Mes žinome, kad „COUNTIFS“ funkcija „Excel“ gali būti pagrįsta keliais kriterijais. Argumentai yra keli kriterijų diapazonas ir kriterijai. Mes galime dinamiškai keisti kriterijus, nurodydami langelio nuorodą, bet negalime dinamiškai keisti kriterijų stulpelio. Na, ne tiesiogiai, bet mes galime. To mes iš anksto mokomės „Excel“ formulių. Daryti dalykus „Excel“, kurių negalima padaryti normaliai. Pažiūrėkime, kaip.
Mokykimės pavyzdžiu.
Kontekstas
Čia aš paruošiau duomenis apie pardavimus, kuriuos įvairiais metų mėnesiais atliko mūsų pardavimo konsultantai. Jie parduoda skirtingus mūsų gaminio modelius, bendrai pavadintus modeliu1, modeliu2 ir pan. Turime dinamiškai skaičiuoti skirtingų modelių pardavimus skirtingais mėnesiais.
I2 langelyje mes pasirenkame mėnesį. I2 langelyje pasirinksime modelį. Šios vertės gali pasikeisti. Ir skaičius turėtų pasikeisti. Funkcija COUNTIFS turėtų ieškoti mėnesio stulpelio, kuris bus kriterijų diapazonas. Tada to mėnesio stulpelyje jis ieškos modelio.
Taigi čia kriterijai ir kriterijų_diagramos yra kintami. Taigi, kaip padaryti stulpelio kintamąjį COUNTIFS? Štai kaip?
Pavadinto diapazono naudojimas kintamojo stulpeliui ar kriterijų diapazonui
Bendra formulė
= COUNTIFS (NETIESIOGINIS (pavadintas_diapazonas), kriterijai)
Pirmiausia pavadinkite kiekvieną stulpelį pagal jų antraštes. Norėdami tai padaryti, pasirinkite lentelę ir paspauskite CTRL+SHIFT+F3 ir pavadinkite stulpelius pagal viršutinę eilutę. Apie tai skaitykite čia.
Taigi, diapazonas B3: B11, C3: C11, D3: D11 ir E3: E11 yra pavadinti atitinkamai sausio, vasario, kovo ir balandžio mėnesiais.
Parašykite šią formulę I4.
= COUNTIFS (NETIESIOGINIS (I2), I3)
Dabar, jei pakeisite mėnesį I4, atitinkamas „Model4“ mėnesių skaičius bus rodomas I4.
Kaip tai veikia?
Formulė paprasta. Pradėkime nuo vidaus.
NETIESIOGINIS (I2): Kaip žinome, INDIRECT funkcija konvertuoja tekstą į faktinę nuorodą. Mes jį pateikėme I2. I2 yra balandis. Kadangi turime diapazoną E3: E11 pavadinimas balandis, NETIESIOGINIS (I2) reiškia E3: E11.
Formulė supaprastinta iki = COUNTIFS (E3: E11, I3). „COUNTIFS“ skaičiuoja viską, kas yra I3 diapazone E3: E11.
Kai pakeisite mėnesį, stulpelis pasikeis dinamiškai. Tai vadinama COUNTIFS su kintamaisiais stulpeliais. Gif aš naudoju tam tikrą sąlyginį formatavimą, pagrįstą kita ląstele.
Formulė taip pat gali veikti su skaitiklio funkcija. Bet jei norite turėti kelias sąlygas, naudokite funkciją COUNTIFS.
Kūrybinių stulpelių diagrama, apimanti sumas
Sukurkite perdangos diagramą „Excel 2016“
Atlikite „Pareto“ diagramą ir analizę „Excel“
Atlikite krioklio diagramą „Excel“
„Excel Sparklines“: mažos ląstelės diagramos
Spidometro (matuoklio) diagrama „Excel 2016“