Conteggio basato su criteri in una colonna filtrata (Microsoft Excel)

Marty ha un grande foglio di lavoro che elenca tutti i dipendenti (passati e presenti) nella sua azienda. Il foglio di lavoro tiene traccia di varie informazioni su ogni dipendente, come nome, indirizzo, dipartimento, sesso, stato, ecc.

Marty filtra spesso i dati per soddisfare le sue esigenze. Vorrebbe un modo per determinare la percentuale di dipendenti che sono uomini e la percentuale che sono donne, in base a quali righe vengono visualizzate dopo il filtraggio. La funzione SUBTOTALE può lavorare su una colonna filtrata per fornire vari conteggi, ma non gli consentirà di determinare i conteggi in base ai contenuti (“M” o “F”) della colonna filtrata.

Un approccio consiste nell’usare una tabella pivot per determinare le percentuali.

Le tabelle pivot sono relativamente facili da usare, in particolare per rispondere a una singola domanda come questa. Non sono particolarmente bravi, tuttavia, nel consentirti di visualizzare informazioni dettagliate sui tuoi dipendenti: puoi vedere la risposta aggregata alla domanda uomo / donna, ma allo stesso tempo non puoi vedere i dettagli su quei dipendenti. Quindi, voglio concentrarmi sull’uso di formule semplici nella risposta al problema di Marty.

Creare una formula per ottenere le percentuali desiderate è più difficile di quanto sembri a prima vista. È facile, ad esempio, determinare i conteggi quando tutti i record dei dipendenti sono visibili. Ad esempio, potresti semplicemente usare qualcosa del genere, supponendo che il sesso fosse nella colonna C, per determinare quale percentuale dei record è per i dipendenti di sesso maschile:

Il problema è se filtri i record in base a una colonna diversa dalla colonna C. Ad esempio, potresti filtrarli in base a ciò che si trova nella colonna di stato. La formula sopra ti darà comunque una percentuale basata su tutti i record nella colonna sesso, non solo quelli che sono attualmente visibili a causa del filtro.

Potresti, a questo punto, pensare che la funzione SUBTOTALE potrebbe funzionare, ma Marty ha scoperto che non sarebbe così. Di nuovo, se il sesso è nella colonna C, potresti mettere quanto segue in fondo alla colonna C:

Tuttavia, questo non funzionerà per le esigenze desiderate. Il problema principale è che la funzione SUBTOTALE ti dà un conteggio di quali record sono visibili, ma non c’è discernimento se quei record contengono “M” o “F” nella colonna C. Il problema secondario è che COUNTA conta tutti i record, non solo quelli visualizzati. Quindi, la formula non ti dà la percentuale dei record visualizzati che contengono “M” o “F”

ma, invece, una percentuale del numero di record visualizzati rispetto al numero totale di record.

Potresti provare a utilizzare una colonna helper se lo desideri. Basta aggiungere una colonna per rappresentare lo stato “M” o “F” di ogni dipendente come 1 o 0.

Questo potrebbe essere fatto con una formula semplice, come questa:

Questa formula assume, ancora una volta, che la colonna del sesso sia C. Supponendo che questa formula sia collocata nella colonna X (la tua colonna helper), potresti quindi utilizzare due funzioni SUBTOTALE, in questo modo:

Il primo utilizzo di SUBTOTALE ti dà il numero di righe che contengono “M”

e il secondo utilizzo di SUBTOTALE fornisce il numero totale di righe visibili. Il risultato della formula è una percentuale di lavoratori maschi i cui record sono visibili. Puoi determinare la percentuale di lavoratrici sottraendo il risultato maschile da 1.

Se non puoi usare una colonna helper (o preferisci non usare una colonna helper), è un po ‘più complicato aggirare le carenze degli approcci discussi in precedenza; ci vuole una formula un po ‘più complicata. Quella che segue è una formula che funzionerà, ma ho suddiviso la singola formula su quattro righe per renderla un po ‘più facile da spiegare.

Entrerò nei dettagli su come funziona questa formula, quindi abbi pazienza per un po ‘, ci vorrà un po’. Diamo prima un’occhiata a questa parte della formula, che abbraccia la fine della prima riga e la totalità della seconda riga:

L’utilizzo della funzione OFFSET qui si basa su 3 parametri. Il primo corrisponde ad un punto di partenza per il calcolo del riferimento che verrà restituito (in questo caso il punto di partenza è C2: C9999). Il secondo parametro è il numero di righe da sfalsare dall’inizio di quell’intervallo definito nel primo parametro. In questo caso, le righe da sfalsare vengono definite sottraendo il numero di riga più basso nell’intervallo (che restituirà sempre il valore 2) dalla riga effettiva analizzata. Quindi, ad esempio, se la riga analizzata è la riga 10, sottraendo 2 (la riga iniziale) da questa si ottiene un offset di 8 righe dall’inizio dell’intervallo specificato nel primo parametro.

Il terzo parametro è vuoto, quindi il valore predefinito è 0. Questo è il numero di colonne da scostare dalla prima colonna nell’intervallo specificato nel primo parametro. Infine, il quarto parametro è il numero 1, che indica che si desidera che OFFSET restituisca un intervallo alto solo 1 cella.

La linea di fondo è che l’intera porzione della formula è inclusa in modo da restituire un riferimento a una singola cella nella colonna analizzata. Per motivi di spiegazione in questa formula, chiamiamo ciò che viene restituito “SingleCell”. Inserendo questo nella formula originale, otteniamo questo:

La prima funzione SUBTOTALE restituisce quindi il risultato COUNTA (indicato dal valore 3 utilizzato per il primo parametro) per la singola cella. L’effetto è che SUBTOTALE restituisce 0 o 1, a seconda che la cella sia visibile o meno. (Se la cella viene esclusa dai risultati, viene restituito 0. Se non viene filtrata, è visibile, viene restituito 1.)

La parte successiva della formula si basa sulle funzioni ISNUMBER e SEARCH. Questa parte del codice restituisce uno 0 o 1 a seconda che la cella contenga la lettera “M” o meno. Quello che si ottiene, quindi, è qualcosa che si riduce a questo:

Nel caso di questa singola riga, SUMPRODUCT restituirebbe 0, il che significa che la riga “non conta” nel conteggio complessivo. Poiché SUMPRODUCT è una funzione basata su array, calcola un prodotto in base alla moltiplicazione di ogni riga nell’intervallo originale, individualmente. Pertanto, determina un conteggio di tutte le righe che soddisfano le due condizioni: la riga è visibile e la riga contiene la lettera “M.”

Questo viene infine diviso per il risultato della funzione SUBTOTALE finale, che è il risultato COUNTA del numero di righe visibili. Il risultato finale è la percentuale di quante righe visibili hanno il carattere “M” visibile nella colonna C, il risultato esatto voluto da Marty.

Per ottenere la percentuale di donne nelle righe visibili, tutto ciò che devi fare è cambiare ciò che stai cercando: cambia “M” in “F” nella formula, e dovresti stare bene.

C’è un avvertimento che dovrebbe essere menzionato, però. La funzione RICERCA non distingue tra maiuscolo e minuscolo. Pertanto, se utilizzi “Maschio” invece di “M” e “Femmina” invece di “F” nella colonna del sesso (colonna C), la ricerca di “Uomo” nella formula corrisponderà a ogni singola riga perché le celle che contengono ” Female “conterrà i caratteri” male “. Pertanto, è meglio attenersi a “M” e “F” oppure, se è necessario utilizzare “Maschio” e “Femmina”, utilizzare semplicemente “Femmina” nella formula e calcolare la percentuale di record maschili uguale a 1 meno la percentuale femminile.

_Nota: _

Se desideri sapere come utilizzare le macro descritte in questa pagina (o in qualsiasi altra pagina dei siti WordTips), ho preparato una pagina speciale che include informazioni utili.

Fare clic qui per aprire quella pagina speciale in una nuova scheda del browser.

ExcelTips è la tua fonte di formazione economica su Microsoft Excel.

Questo suggerimento (13550) si applica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 e Excel in Office 365.