Determinar o valor max

[Hugo C.]

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

 

[Arvi Laanemets]

Hi

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

 

[Toppers]

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

Enter with Ctrl+Shift+Enter

 

[Hugo C.]

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:

 

[Toppers]

Arvi's suggestion of a pivot table?

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:

 

[Hugo C.]

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

 

[Arvi Laanemets]

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.