Esercizio N6 individua il MAX

Hai un problema con Excel?

Nuova discussione

Esercizio N.6 individua il MAX
di Textomb data: 06/05/2015 11:26:32
tra una riuniuone e l'altra...
La tabella è la stessa dell'Esercizio N.5. Ma stavolta saliamo un pò di livello alla scoperta di formule un pò più complesse.
Allego in ogni caso l'Esempio N. 6 per un'immediata comprensione.
Il nostro dipartimento ci chiede di estrapolare, da quella tabella, la città ed il mese in cui si è verificato il numero "max" di ore di straordinario pagate nel 2014.
Nel nostro caso specifico questo si è verificato nel mese di Agosto e nella città di Padova dove sono state riconosciute ben 350 ore di straordinario.
Ma quali formule dovrei utilizzare per estrapolare quell'informazione?
Sono ammesse SOLO le formule!!! Quindi niente VBA per stavolta.
Liberate la Vostra fantasia!!!
di Textomb data: 06/05/2015 11:29:13
@ ninai.
tu aspetti fino a quando non te lo dico io!!!!
di Luca73 data: 06/05/2015 12:33:11
Ciao
ci provo...assumo che il massimo sia uno ed uno soltanto (ovvero non esistano due celle con il valore max).

per la città
=INDIRETTO("C" & SOMMA(SE(D3:O40=$R$13;RIF.RIGA(D3:O40);0)))
per il mese
=SCARTO(C2;0;MATR.SOMMA.PRODOTTO(SE(D3:O40=MAX(D3:O40);RIF.COLONNA(D3:O40);0))-3)
entrambe matriciali
filosofie parzialmente differenti (a parte indiretto con scarto) ma interscambiabili
di Mister_x (utente non iscritto) data: 06/05/2015 15:50:12
ciao textomb

aspettando le varie funzioni da applicare, mi sono divertito questa volta applicando la formattazione condizionale alla tabella, dove con questa ho rilevato il valore Max() e dopo
intrecciando la riga e la colonna di apprtenenza la citta' e la provincia, con i vari colori
allego il file con inserite le varie formattazioni a riquardo, per verificare le formule adottate a chi interessa come ho impostate queste per fare il tutto

ciao
Primopianizzo
di Mauro data: 06/05/2015 21:53:36
Fatemi sapere quando normalizzare l'altra.

di brontolo (utente non iscritto) data: 07/05/2015 08:02:28
Se il valore massimo è univoco, potremmo provare con:
Due Celle dedicate:
- Località
- Mese

Nel caso in cui ci siano più località con lo stesso numero di Ore straordinarie lavorate, per avere l'elenco di tutte le Località interessate, la Formule andrà necessariamente modificata.
In questo secondo caso, andrà rivista anche la Formula "Mese" anche se non ho ancora eseguito dei Test al riguardo.

Nella Cella CAMPO "Località"

=INDICE($C$3:$C$40;AGGREGA(15;6;RIF.RIGA($C$3:$C$40)-2/($D$3:$O$40=MAX($D$3:$O$40));RIF.RIGA(A1)))


Nella Cella CAMPO "Mese"

{=INDICE($D$2:$O$2;PICCOLO(SE($D$3:$O$40=MAX($D$3:$O$40);RIF.COLONNA($A$1:$L$1));COLONNE($A1:A$1)))}
Matriciale da confermare con Ctrl Maiuscolo Invio

di Textomb data: 07/05/2015 10:14:43
Al fine di non appesantire i ragionamenti..., si dovrà ipotizzare che ci sia solo un valore max nella tabella. Giusto per l'appunto verificatosi ad Agosto e nella città di Padova.
Passate ormai le 24 ore dalla pubblicazione del quesito possiamo dare il via libera a tutti coloro che volessero offrire la propria soluzione.
Alla fine mi incaricherò di pubblicare il file con le soluzioni espresse da tutti.
Infine ringrazio il Ns Amministratore Mauro per il privileggio accordato alla tematica mettendola in evidenza per una migliore visibilità.
di Textomb data: 07/05/2015 10:19:13
oops... privilegio... e non privileggio. Anche se è un forum di excel l'Italiano non può essere trascurato. Quindi sforziamoci di rispettarlo.

di Mister_x (utente non iscritto) data: 07/05/2015 17:15:01
ciao

ipotizzando ,non in questo caso voluto, che ci siano piu di un max valori uguali,quindi da 1 a x
con la formattazione condizionale che avevo proposto ieri, questo era gia' risolto
oggi propongo una funzione per questo scopo, sulla base del DB proposto
allego sempre il file per verifica

ciao

'' in un modulo di classe

Option Explicit
Public Function DatiMax(Straordinari As Range, Citta As Variant, Mesi As Variant) As String
'' Utilizzo  DatiMax(Range dove ore;Numero di colonna delle citta' (3 o "C");Numero di riga dei mesi (2))
Application.Volatile
Dim Valmax As Single
Dim Straord As Variant
Dim MesiCitta As String
MesiCitta = ""
Valmax = Application.Max(Straordinari)
 For Each Straord In Straordinari
   If Straord = Valmax Then
      MesiCitta = MesiCitta & Cells(Straord.Row, Citta) & "-" & Cells(Mesi, Straord.Column) & "   "
   End If
 Next
DatiMax = "Max " & Valmax & " IN " & MesiCitta
End Function

max riga colonna
di canapone data: 07/05/2015 19:05:00
Ciao a tutti,

riprendo il ragionamento di Brontolo: lo saluto subito.

Il mese può essere ottenuto con la stessa formula usata per individuare la città

=INDICE(D2:O2;AGGREGA(15;6;RIF.COLONNA(A:L)/((D3:O40)=MAX(D3:O40));1))

---------------

Per la città sto usando una formula molto simile alla sua

=INDICE(C3:C40;AGGREGA(14;6;RIF.RIGA(1:38)/((D3:O40)=MAX(D3:O40));1))

------------------------------------------------------

Per versioni di Excel precedenti

Città

=INDICE(C1:C40;MAX(INDICE(RIF.RIGA(D3:D40)*((D3:O40)=MAX(D3:O40));)))

Mese

=INDICE(A2:O2;MAX(INDICE(RIF.COLONNA(D3:O40)*((D3:O40)=MAX(D3:O40));)))

Saluti
di ninai (utente non iscritto) data: 07/05/2015 19:51:44
ciao
un saluto a tutti
mi avete anticipato tutte le mie ipotetiche soluzioni (attualmente non ho modo di cimentarmi a cercarne altre ) a questo punto intervengo modificando le formule di Luca73, per inviarle non matriciali e per uniformarle entrambe con INDIRETTO()

per la citta:
=INDIRETTO("C" & SOMMA(INDICE((D3:O40=$R$13)*RIF.RIGA(D3:O40);)))

per il mese:
=INDIRETTO("R2C" & SOMMA(INDICE((D3:O40=$R$13)*RIF.COLONNA(D3:O40);));FALSO)

per il resto, anche io avevo optato per restituzioni multiple, con AGGREGA() ecc. ecc.
di brontolo (utente non iscritto) data: 07/05/2015 19:53:41
Buona sera, Canapone;
in realtà la Formula "Località" che ho proposto doveva concedere l'oppotunità, copiata nelle celle sottostanti, di selezionare più Località con lo stesso valore di Ore straordinarie lavorate.
La Formula ... è Tua, copiata bovinamente e adattata, molto indegnamente, da una precedente discussione.

Poi, non ho sviluppato completamente la Formula "Mese" per il valore massimo di Ore straordinarie lavorate attribuite a più Località, non per pigrizia, ma perchè, per il momento, ... arranco

Non demordo, per mia soddisfazione, vorrei chiudere il cerchio.

di scossa data: 08/05/2015 09:45:56
Ho seguito solo parzialmente, quindi spero di non proporre soluzioni già esposte.

La prima proposta (celle in verdino) usa formule normali e trova, in caso di occorrenze multiple, sempre l'ultima:
per la Città -> =SCARTO(C1;MAX(INDICE(($D$3:$O$40=$R$13)*RIF.RIGA($D$3:$O$40);))-1;0)
per il mese -> =SCARTO(A2;0;MAX(INDICE(($D$3:$O$40=$R$13)*RIF.COLONNA($D$3:$O$40);))-1)

La seconda proposta (celle gialline) usa i "nomi":


nome	riferito a

-----	---------------------------------------------------

dati	=Esercizio!$D$3:$O$40

iCol	=INDICE((dati=Esercizio!$R$13)*RIF.COLONNA(dati);)

iRig	=INDICE((dati=Esercizio!$R$13)*RIF.RIGA(dati);)

iG	=MATR.SOMMA.PRODOTTO(--(dati=Esercizio!$R$13))

per la Città -> =SCARTO(C1;GRANDE(iRig;iG)-1;0)
per il mese -> =SCARTO(A2;0;GRANDE(iCol;iG)-1)

Allego il file Esercizio_N.6_scossa.xlsx

scossa's web site

Se tu hai una mela, ed io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno.
Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)

di scossa data: 08/05/2015 09:51:16
cit. scossa: "La seconda proposta (celle gialline) usa i "nomi" ...."

Dimneticavo di dire che questa soluzione permette di trovare la prima ricorrenza.

scossa's web site

più max per mese e città
di canapone data: 08/05/2015 10:31:32
Ciao a tutti,

ho allegato Esercizio nr.6 canapone.

Uso delle formule da confermare con control+maius+invio per elencare le coordinate città/mese relative ai picchi di valore

Nelle formule indico MAX con R13

In Q15 la prima formula

=SE.ERRORE(INDICE($C$3:$C$40;GRANDE(SE($D$3:$O$40=$R$13;RIF.RIGA($A$3:$A$40)-2);RIF.RIGA(A1)));"")

Accanto R15 la seconda formula.

=SE.ERRORE(PICCOLO(SE(INDICE($D$3:$O$40;CONFRONTA(Q15;$C$3:$C$40;0);0)=$R$13;RIF.COLONNA($D$1:$O$1)-3);CONTA.SE($Q$15:Q15;Q15));"")

Possono sicuramente essere semplificate: i rif.colonna ed i rif.riga per esempio.

Saluti
più max per mese e città
di canapone data: 08/05/2015 10:36:13
Ciao

un ripensamento

in Q15 meglio usare PICCOLO invece di GRANDE

=SE.ERRORE(INDICE($C$3:$C$40;PICCOLO(SE($D$3:$O$40=$R$13;RIF.RIGA($A$3:$A$40)-2);RIF.RIGA(A1)));"")

Saluti

riallego il file
Fuori concorso!
di brontolo (utente non iscritto) data: 08/05/2015 16:41:24
Buona sera, Canapone.
Che dire? Era quello che mi mancava per i Mesi.

Nel File Esercizio N°6 di brontolo.zip mi sono limitato ad evitare di imporre il totale ma calcolarlo all'interno delle formule. Nelle Formule "Città" Excel 2000 e Excel 2013, non ho usato matriciali.
Ma, questi, sono loro dettagli.

Ho in animo di rivedere la Tua Formula "Mese" per le release di Excel antecedenti Excel 2010, ma per questo mi prendo un pò di tempo; ... adesso ho finito le Cibalgine!!!

brontolo
di brontolo (utente non iscritto) data: 08/05/2015 16:47:41
oops... come dice l'Ottimo Textomb:
- Ma, questi, sono solo dettagli.
e non
- Ma, questi, sono loro dettagli.
Cit.:
Anche se è un forum di excel l'Italiano non può essere trascurato. Quindi sforziamoci di rispettarlo.
Aggiungo, specialme te con i Fiorentini!
max riga colonna excel 2000
di canapone data: 08/05/2015 17:21:32
Ciao,

per rendere le formule utilizzabili su Excel in versione precedenti alla 2007

In Q15

=SE(RIF.RIGA($A1)>CONTA.SE($D$3:$O$40;$R$13);"";INDICE($C$3:$C$40;PICCOLO(SE($D$3:$O$40=$R$13;RIF.RIGA($A$3:$A$40)-2);RIF.RIGA(A1))))

in R15

=SE(Q15>"";PICCOLO(SE(INDICE($D$3:$O$40;CONFRONTA(Q15;$C$3:$C$40;0);0)=$R$13;RIF.COLONNA($D$1:$O$1)-3);CONTA.SE($Q$15:Q15;Q15));"")

Mi ripeto: sono da confermare con control+maiusc+invio

Saluti da Firenze
di brontolo (utente non iscritto) data: 09/05/2015 05:44:43
Solo ora mi accorgo che il File Esercizio N°6 di brontolo.zip non è fruibile.
Temo che la causa sia il carattere "°".

Per chi può essere interessato, inserisco il File Esercizio N.6 di brontolo.zip

Il messaggio mi confermava che l'inserimento del File era andato a buon fine; purtroppo non mi sono preoccupato di verificare se effettivamente il File era scaricabile.

Mi scuso per il refuso.

brontolo
di Textomb data: 09/05/2015 12:20:31
allego il file "Esercizio N.6 - Soluzioni" che contempla il complesso delle soluzioni proposte dagli intervenuti.
Risulta un esercizio particolarmente interessante capire l'approccio con cui gli intervenuti hanno inteso affrontare la questione.
Nel rinnovare i miei più sinceri complimenti a TUTTI, Vi auguro un buon week end!!!

Vuoi Approfondire?