Sappiamo che la funzione CONTA.SE in Excel può contare su più criteri. Prende argomenti come coppia di criteri, intervallo e criteri. Possiamo cambiare i criteri dinamicamente dando il riferimento della cella ma non possiamo cambiare dinamicamente la colonna dei criteri. Beh, non direttamente, ma possiamo farlo. Questo è ciò che impariamo in anticipo sulle formule di Excel. Fare cose in Excel che non possono essere fatte normalmente. Vediamo come.
Impariamo con l'esempio.
Contesto
Qui ho preparato i dati delle vendite effettuate nei diversi mesi dell'anno dai nostri consulenti di vendita. Vendono diversi modelli del nostro prodotto, denominati genericamente model1, model2 e così via. Quello che dobbiamo fare è contare le vendite di diversi modelli in diversi mesi in modo dinamico.
Nella cella I2 sceglieremo il mese. Nella cella I2 sceglieremo il modello. Questi valori possono essere cambieranno. E anche il conto dovrebbe cambiare. La funzione CONTA.PI.SE dovrebbe cercare la colonna del mese che sarà l'intervallo di criteri. Quindi cercherà il modello in quella colonna dei mesi.
Quindi qui i criteri e criteri_intervallo sono entrambi variabili. Quindi, come facciamo a rendere variabile la colonna in COUNTIFS? Ecco come?
Utilizzo dell'intervallo denominato per la colonna variabile o l'intervallo di criteri
Formula generica
=CONTA.SE(INDIRETTO(intervallo_denominato),criteri)
Innanzitutto, nomina ogni colonna secondo le loro intestazioni. Per fare ciò, seleziona la tabella e premi CTRL+MAIUSC+F3 e assegna un nome alle colonne come nella riga superiore. Leggi qui.
Quindi, l'intervallo B3: B11, C3: C11, D3: D11 e E3: E11 sono denominati rispettivamente gennaio, febbraio, marzo e aprile.
Scrivi questa formula in I4.
=CONTA.SE(INDIRETTO(I2),I3)
Ora se cambi il mese in I4, il rispettivo conteggio dei mesi di Model4 verrà mostrato in I4.
Come funziona?
La formula è semplice. Cominciamo dall'interno.
INDIRETTO(I2): Come sappiamo, la funzione INDIRETTO converte il riferimento di testo in riferimento effettivo. Lo abbiamo fornito I2. I2 contiene Apr. Poiché abbiamo l'intervallo E3:E11 nome Apr, INDIRETTO(I2) si traduce in E3:E11.
La formula semplificata in =COUNTIFS(E3:E11,I3). COUNTIFS conta tutto ciò che è in I3 nell'intervallo E3:E11.
Quando cambi mese, la colonna cambierà dinamicamente. Questo è chiamato COUNTIFS con colonne variabili. Nella gif ho usato una formattazione condizionale basata su un'altra cella.
La formula può funzionare anche con la funzione countif. Ma se vuoi avere più condizioni, usa la funzione CONTA.PI.SE.
Istogramma creativo che include i totali
Crea grafico sovrapposto in Excel 2016
Esegui il grafico e l'analisi di Pareto in Excel
Esegui grafico a cascata in Excel
Sparkline di Excel: i piccoli grafici nella cella
Grafico del tachimetro (indicatore) in Excel 2016