Di 'che vuoi essere in grado di contare elementi unici da un elenco, ma con una svolta. E dì che stai lavorando con questo foglio di lavoro:
La colonna D conta il numero di righe in ciascuna delle sezioni dalla colonna B e la colonna C conta il numero di sezioni uniche in base ai primi cinque caratteri della colonna A per quella sezione. Le celle B2: B11 contengono ARG e puoi contare otto elementi univoci nei primi cinque caratteri di A2: A11 perché A7: A9 contengono ciascuna 11158, quindi i due duplicati non vengono conteggiati. Allo stesso modo, il 5 in D12 indica che ci sono cinque righe per BRD, ma all'interno delle righe 12:16 ci sono tre elementi univoci dei primi cinque caratteri, poiché 11145 viene ripetuto e 11173 viene ripetuto.
Ma come si fa a dire a Excel di farlo? E quale formula potresti usare in C2 che potrebbe essere copiata in C12 e C17?
La semplice formula di conteggio in D2,, =COUNTIF(B:B,B2)conta il numero di volte in cui B2 (ARG) esiste nella colonna B.
Si utilizza una colonna helper per isolare i primi cinque caratteri della colonna A, come in questa figura:
Successivamente, devi in qualche modo indicare che per ARG, sei interessato solo alle celle F2: F11 per trovare il numero di elementi univoci. In generale, troverai questo valore utilizzando la formula di matrice mostrata in questa figura:
Si utilizza temporaneamente la cella C3 solo per mostrare la formula; puoi vedere che non è presente in C3 nelle figure precedenti. (Imparerai a breve come funziona questa formula.)
Allora qual è la formula in C2, C12 e C17? La risposta sorprendente (e interessante) è mostrata in questa figura:
Whoa! Come funziona?
Dai un'occhiata a Risposta nei nomi definiti in questa figura:
È la stessa formula di una figura precedente, ma invece di utilizzare l'intervallo F2: F11, utilizza un intervallo denominato Rg. Inoltre, la formula era una formula di matrice, ma le formule con nome vengono trattate come se fossero formule di matrice! Cioè, =Answernon viene inserito con Ctrl + Maiusc + Invio ma viene semplicemente inserito come al solito.
Allora come viene definito Rg? Se è selezionata la cella C1 (che è un passaggio importante per comprendere questo trucco), viene definita come in questa figura:
Ecco =OFFSET(Loan_Details!$F$1,MATCH(Loan_Details!$B1,Loan_Details!$B:$B,0)-1,0,COUNTIF(Loan_Details!$B:$B,Loan_Details!$B1),1).
Loan_Details è il nome del foglio, ma puoi guardare questa formula senza il nome del foglio lungo. Un modo semplice per farlo è assegnare temporaneamente al foglio un nome semplice, come x, e poi guardare di nuovo il nome definito:
Questa formula è più facile da leggere!
Puoi vedere che questa formula corrisponde a $ B1 (nota il riferimento relativo alla riga corrente) rispetto a tutta la colonna B e sottrae 1. Sottrai 1 perché stai usando OFFSET da F1. Ora che conosci la formula per C, dai un'occhiata a quella per C2:
La MATCH($B2,$B:$B,0)parte della formula è 2, quindi la formula (senza il riferimento al nome del foglio) è:
=OFFSET($F$1,2-1,0,COUNTIF($B:$B,$B2),1)
o:
=OFFSET($F$1,1,0,COUNTIF($B:$B,$B2),1)
o:
=OFFSET($F$1,1,0,10,1)
Perché COUNTIF($B:$B,$B2)è 10, ci sono 10 ARG. Questo è l'intervallo F2: F11. In effetti, se la cella C2 è selezionata e premi F5 per andare su Rg, vedrai questo:
Se la cella di partenza fosse C12, premendo F5 per andare a Rg si ottiene questo:
Quindi ora, con la risposta definita come =SUM(1/COUNTIF(rg,rg)), hai finito!
Diamo un'occhiata più da vicino a come funziona questa formula, usando un esempio molto più semplice. Normalmente, la sintassi per COUNTIF è =COUNTIF(range,criteria), come =COUNTIF(C1:C10, "b")in questa figura:
Questo darebbe 2 come numero di b nell'intervallo. Ma passando l'intervallo stesso come criteri utilizza ogni elemento nell'intervallo come criterio. Se evidenzi questa parte della formula:
e premi F9, vedi:
Ogni elemento nell'intervallo viene valutato e questa serie di numeri significa che ce n'è una a e ci sono due b, tre c e quattro d. Questi numeri sono divisi in 1, dando 1, ½, ½, ⅓, ⅓, ⅓, ¼, ¼, ¼, ¼, come puoi vedere qui:
Quindi hai 2 metà, 3 terzi, 4 quarti e 1 intero, e sommandoli ottieni 4. Se un elemento fosse ripetuto 7 volte, avresti 7 settimi e così via. Abbastanza bello! (Tanto di cappello a David Hager per aver scoperto / inventato questa formula.)
Ma aspetta un minuto. Allo stato attuale, devi inserire questa formula solo in C2, C12 e C17. Non sarebbe meglio se potessi inserirlo in C2 e riempirlo e mostrarlo solo nelle celle corrette? In effetti, puoi farlo. Puoi modificare la formula in C2 in modo che sia =IF(B1B2,Answer,""), e quando la riempi, fa il lavoro:
Ma perché fermarsi qui? Perché non trasformare la formula in una formula con nome, come mostrato qui:
Perché funzioni, la cella C2 deve essere la cella attiva (altrimenti la formula dovrebbe essere diversa). Ora puoi sostituire le formule della colonna C con =Answer2:
Puoi vedere che C3 ha =Answer2, come tutte le celle nella colonna C. Perché non continuare questo nella colonna D? La formula in D2, dopo aver applicato anche il confronto a B1 e B2, è mostrata qui:
Quindi, se mantieni selezionata la cella D2 e definisci un'altra formula, dì Risposta3:
quindi puoi entrare =Answer3nella cella D2 e riempire:
Ecco la parte superiore del foglio di lavoro, con le formule visualizzate, seguita dalla stessa schermata con i valori che mostrano:
Quando altre persone cercano di capirlo, all'inizio potrebbero grattarsi la testa!
Questo articolo ospite è tratto da Bob Umlas, MVP di Excel. È tratto dal libro More Excel Outside the Box. Per vedere gli altri argomenti del libro, fare clic qui.
