Știm că funcția COUNTIFS în Excel poate conta pe mai multe criterii. Este nevoie de argumente ca două criterii de gamă și criterii. Putem modifica criteriile dinamic dând referința celulei, dar nu putem schimba dinamic coloana criteriilor. Ei bine, nu direct, dar putem. Asta învățăm în avans formulele Excel. Făcând lucruri în Excel, care nu se pot face normal. Să vedem cum.
Să învățăm prin exemplu.
Context
Aici am pregătit date despre vânzările efectuate în diferite luni ale anului de către consultanții noștri de vânzări. Vânză diferite modele ale produsului nostru, denumite generic model1, model2 și așa mai departe. Ceea ce trebuie să facem este să numărăm dinamic vânzările diferitelor modele în diferite luni.
În celula I2 vom alege luna. În celula I2 vom alege modelul. Aceste valori pot fi se vor schimba. Și contorul ar trebui să se schimbe și el. Funcția COUNTIFS ar trebui să caute coloana lunii care va fi gama de criterii. Apoi va căuta modelul în acea coloană de luni.
Deci, aici criteriile și intervalul de criterii sunt variabile. Deci, cum facem variabilă coloană în COUNTIFS? Iată cum?
Utilizarea intervalului denumit pentru coloana variabilă sau intervalul de criterii
Formula generică
= COUNTIFS (INDIRECT (named_range), criterii)
Mai întâi, denumiți fiecare coloană conform titlurilor lor. Pentru aceasta, selectați tabelul și apăsați CTRL + SHIFT + F3 și denumiți coloanele conform rândului superior. Citiți despre asta aici.
Deci, Gama B3: B11, C3: C11, D3: D11 și E3: E11 sunt denumite respectiv Jan, Feb, Mar și Apr.
Scrieți această formulă în I4.
= COUNTIFI (INDIRECȚI (I2), I3)
Acum, dacă schimbați luna în I4, numărul de luni respectiv al modelului 4 va fi afișat în I4.
Cum functioneaza?
Formula este simplă. Să începem din interior.
INDIRECT (I2): După cum știm, funcția INDIRECT convertește textul de referință în referință reală. I-am furnizat I2. I2 conține aprilie Deoarece avem gama E3: E11 denumire apr, INDIRECT (I2) se traduce prin E3: E11.
Formula simplificată la = COUNTIFS (E3: E11, I3). COUNTIFS contează orice este în I3 în intervalul E3: E11.
Când schimbați luna, coloana se va schimba dinamic. Aceasta se numește COUNTIFS cu coloane variabile. În gif am folosit o anumită formatare condițională bazată pe o altă celulă.
Formula poate funcționa și cu funcția countif. Dar dacă doriți să aveți mai multe condiții, utilizați funcția COUNTIFS.
Diagramă coloană creativă care include totaluri
Creați diagramă de suprapunere în Excel 2016
Efectuați diagrama și analiza Pareto în Excel
Efectuați diagrama cascadă în Excel
Excel Sparklines: Diagramele minuscule din celulă
Diagrama vitezometrului (ecartament) în Excel 2016