A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Determinar o valor max



 
 
Thread Tools Display Modes
  #1  
Old July 30th, 2007, 02:02 PM posted to microsoft.public.excel.misc
Hugo C.[_2_]
external usenet poster
 
Posts: 11
Default Determinar o valor max

Estou neste momento a trabalhar com uma base de cerca de 40000 linhas, tenho
várias datas associadas a clientes, eu queria obter o valor max de cada
cliente agrupando os clientes.

exe

Cliente dias
12345 -213
12345 -158
12568 126
12345 10
12489 15
48615 486

para o cliente 12345 o valor max seria 10, como fazer que ele me agrupe por
clientes e me de o valor max ...

gostaria que me ajudassem
  #2  
Old July 30th, 2007, 02:22 PM posted to microsoft.public.excel.misc
Arvi Laanemets
external usenet poster
 
Posts: 397
Default Determinar o valor max

Hi

The solution is pivot table - group by Cliente, and calculate MAX of dias


--
Arvi Laanemets
( My real mail address: arvi.laanemetsattarkon.ee )



"Hugo C." wrote in message
news
Estou neste momento a trabalhar com uma base de cerca de 40000 linhas,
tenho
v?rias datas associadas a clientes, eu queria obter o valor max de cada
cliente agrupando os clientes.

exe

Cliente dias
12345 -213
12345 -158
12568 126
12345 10
12489 15
48615 486

para o cliente 12345 o valor max seria 10, como fazer que ele me agrupe
por
clientes e me de o valor max ...

gostaria que me ajudassem



  #3  
Old July 30th, 2007, 02:26 PM posted to microsoft.public.excel.misc
Toppers
external usenet poster
 
Posts: 3,081
Default Determinar o valor max

=MAX(($A$2:$A$7=12345)*($B$2:$B$7))

Enter with Ctrl+Shift+Enter

"Hugo C." wrote:

Estou neste momento a trabalhar com uma base de cerca de 40000 linhas, tenho
várias datas associadas a clientes, eu queria obter o valor max de cada
cliente agrupando os clientes.

exe

Cliente dias
12345 -213
12345 -158
12568 126
12345 10
12489 15
48615 486

para o cliente 12345 o valor max seria 10, como fazer que ele me agrupe por
clientes e me de o valor max ...

gostaria que me ajudassem

  #4  
Old July 30th, 2007, 03:00 PM posted to microsoft.public.excel.misc
Hugo C.[_2_]
external usenet poster
 
Posts: 11
Default Determinar o valor max

that would be easy if they were only 5 elements, but like i said i´m working
whit 40000 elements so is there a way using condictions to assume all clients
according whit their number and then make me the max value for each case ...

"Toppers" escreveu:

=MAX(($A$2:$A$7=12345)*($B$2:$B$7))

Enter with Ctrl+Shift+Enter

"Hugo C." wrote:

Estou neste momento a trabalhar com uma base de cerca de 40000 linhas, tenho
várias datas associadas a clientes, eu queria obter o valor max de cada
cliente agrupando os clientes.

exe

Cliente dias
12345 -213
12345 -158
12568 126
12345 10
12489 15
48615 486

para o cliente 12345 o valor max seria 10, como fazer que ele me agrupe por
clientes e me de o valor max ...

gostaria que me ajudassem

  #5  
Old July 30th, 2007, 03:22 PM posted to microsoft.public.excel.misc
Toppers
external usenet poster
 
Posts: 3,081
Default Determinar o valor max

Arvi's suggestion of a pivot table?

"Hugo C." wrote:

that would be easy if they were only 5 elements, but like i said i´m working
whit 40000 elements so is there a way using condictions to assume all clients
according whit their number and then make me the max value for each case ...

"Toppers" escreveu:

=MAX(($A$2:$A$7=12345)*($B$2:$B$7))

Enter with Ctrl+Shift+Enter

"Hugo C." wrote:

Estou neste momento a trabalhar com uma base de cerca de 40000 linhas, tenho
várias datas associadas a clientes, eu queria obter o valor max de cada
cliente agrupando os clientes.

exe

Cliente dias
12345 -213
12345 -158
12568 126
12345 10
12489 15
48615 486

para o cliente 12345 o valor max seria 10, como fazer que ele me agrupe por
clientes e me de o valor max ...

gostaria que me ajudassem

  #6  
Old July 30th, 2007, 09:32 PM posted to microsoft.public.excel.misc
Hugo C.[_2_]
external usenet poster
 
Posts: 11
Default Determinar o valor max

it´s a nice ideia but we doesn´t acept... 2 many data 2 output ... humm ... i
solve doing max in acess, but i´m stil not satisfied
  #7  
Old July 31st, 2007, 06:40 AM posted to microsoft.public.excel.misc
Arvi Laanemets
external usenet poster
 
Posts: 397
Default Determinar o valor max

Hi

Let's assume you have your data in columns A:B. In cells A1:B1 you must have
column headers ("Cliente"; "dias").

Select some range in columns A:B, starting from A1;
From Data menu, select Pivot Table and Pivot Chart Report;
Next;
Edit the range to $A:$B. Next;
Click on Layout...;
Drag field Cliente into ROW area;
Drag field dias into Data area - you get there 'Count of dias';
Double-click on 'Count of dias';
Set 'Summarize by' to MAX. OK;
OK;
Finish.

It's easiest way for start. You can add new entries into source table or
edit existing ones, then select any cell in pivot table, and refresh the
pivot table to actualize it. A drawback is, you get Cliente '(blank)' in
pivot table because all of empty rows at bottom of table - but you can
easily ignore it.


--
Arvi Laanemets
( My real mail address: arvi.laanemetsattarkon.ee )



"Hugo C." wrote in message
...
it´s a nice ideia but we doesn´t acept... 2 many data 2 output ... humm
... i
solve doing max in acess, but i´m stil not satisfied



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:35 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.