Znamo da funkcija COUNTIFS u Excelu može računati na više kriterija. Argumente uzima kao nekoliko raspona kriterija i kriterija. Kriterije možemo dinamički mijenjati davanjem reference ćelije, ali ne možemo dinamički mijenjati stupac kriterija. Pa, ne izravno, ali možemo. To je ono što unaprijed učimo excelovim formulama. Raditi stvari u Excelu koje se ne mogu raditi normalno. Pogledajmo kako.
Učimo na primjeru.
Kontekst
Ovdje sam pripremio podatke o prodaji koju su naši prodajni konzultanti obavili u različitim mjesecima u godini. Prodaju različite modele našeg proizvoda, općenito nazvane model1, model2 itd. Ono što moramo učiniti je dinamički brojati prodaju različitih modela u različitim mjesecima.
U ćeliji I2 odabrat ćemo mjesec. U ćeliji I2 odabrat ćemo model. Ove se vrijednosti mogu promijeniti. I broj bi se trebao promijeniti. Funkcija COUNTIFS trebala bi tražiti stupac mjeseca koji će biti raspon kriterija. Tada će tražiti model u toj koloni mjeseci.
Dakle, ovdje su kriteriji i raspon_kriterija promjenjivi. Dakle, kako napraviti promjenjivu kolonu u COUNTIFS? Evo kako?
Korištenje imenovanog raspona za promjenjivi stupac ili raspon kriterija
Opća formula
= COUNTIFS (INDIRECT (named_range)), kriteriji)
Prvo imenujte svaku kolonu prema njihovim naslovima. Da biste to učinili, odaberite tablicu i pritisnite CTRL+SHIFT+F3 i imenujte stupce prema gornjem retku. Pročitajte o tome ovdje.
Dakle, raspon B3: B11, C3: C11, D3: D11 i E3: E11 nazvani su siječanj, veljača, ožujak i travanj.
Napišite ovu formulu u I4.
= COUNTIFS (INDIREKTNO (I2), I3)
Sada, ako promijenite mjesec u I4, odgovarajući broj mjeseci Modela 4 bit će prikazan u I4.
Kako radi?
Formula je jednostavna. Počnimo iznutra.
INDIRECT (I2): Kao što znamo INDIRECT funkcija pretvara tekst ref u stvarnu referencu. Pružili smo ga I2. I2 sadrži travanj. Budući da imamo raspon E3: E11 naziv Apr, INDIRECT (I2) prevodi u E3: E11.
Formula je pojednostavljena na = COUNTIFS (E3: E11, I3). COUNTIFS broji sve što je u I3 u rasponu E3: E11.
Kad promijenite mjesec, stupac će se dinamički mijenjati. To se naziva COUNTIFS sa stupcima varijable. U gifu sam koristio uvjetno oblikovanje na temelju druge ćelije.
Formula može raditi i s funkcijom Counti. No, ako želite imati više uvjeta, upotrijebite funkciju COUNTIFS.
Grafikon stupca oglasa koji uključuje ukupne iznose
Izradite preklapajući grafikon u Excelu 2016
Izvršite Pareto grafikon i analizu u Excelu
Izvršite grafikon vodopada u Excelu
Excel Sparklines: Sitni grafikoni u ćeliji
Tablica brzinomera (mjerača) u Excelu 2016