Mēs zinām, ka funkcija COUNTIFS programmā Excel var paļauties uz vairākiem kritērijiem. Argumenti tiek ņemti par pāris kritēriju diapazonu un kritērijiem. Mēs varam dinamiski mainīt kritērijus, norādot šūnas atsauci, bet mēs nevaram dinamiski mainīt kritēriju sleju. Nu ne tieši, bet mēs varam. Tas ir tas, ko mēs iepriekš iemācāmies Excel formulās. Dariet lietas Excel, ko nevar izdarīt normāli. Redzēsim, kā.
Mācīsimies ar piemēru.
Konteksts
Šeit es esmu sagatavojis datus par pārdošanas apjomiem, ko mūsu pārdošanas konsultanti veikuši dažādos gada mēnešos. Viņi pārdod dažādus mūsu produkta modeļus, vispārīgi nosauktus par modeli1, modeli2 un tā tālāk. Kas mums jādara, ir dinamiski uzskaitīt dažādu modeļu pārdošanas apjomus dažādos mēnešos.
Šūnā I2 mēs izvēlēsimies mēnesi. Šūnā I2 mēs izvēlēsimies modeli. Šīs vērtības var mainīties. Un arī skaitam vajadzētu mainīties. Funkcijai COUNTIFS jāmeklē mēneša sleja, kas būs kritēriju diapazons. Tad tā meklēs modeli šo mēnešu slejā.
Tātad šeit abi kritēriji un kritēriju_diapazons ir mainīgi. Tātad, kā mēs varam izveidot kolonnu mainīgo COUNTIFS? Lūk, kā?
Nosaukta diapazona izmantošana mainīgo kolonnu vai kritēriju diapazonam
Vispārīgā formula
= COUNTIFS (netiešs (nosaukts_diapazons), kritēriji)
Vispirms nosauciet katru kolonnu atbilstoši tās virsrakstiem. Lai to izdarītu, atlasiet tabulu un nospiediet taustiņu kombināciju CTRL+SHIFT+F3 un nosauciet slejas atbilstoši augšējai rindai. Par to lasiet šeit.
Tātad diapazons B3: B11, C3: C11, D3: D11 un E3: E11 ir attiecīgi nosaukti par Janu, Feb, Mar un Apr.
Uzrakstiet šo formulu I4.
= COUNTIFS (NETIEŠS (I2), I3)
Tagad, ja maināt mēnesi I4, modeļa 4 atbilstošo mēnešu skaits tiks parādīts I4.
Kā tas strādā?
Formula ir vienkārša. Sāksim no iekšpuses.
Netiešs (I2): Kā mēs zinām, funkcija INDIRECT pārvērš teksta atsauci par faktisko atsauci. Mēs to esam nodrošinājuši I2. I2 satur apr. Tā kā mums ir diapazons E3: E11 nosaukums Apr, netiešais (I2) tiek tulkots kā E3: E11.
Formula vienkāršota līdz = COUNTIFS (E3: E11, I3). COUNTIFS skaita visu, kas ir I3 diapazonā E3: E11.
Mainot mēnesi, kolonna mainīsies dinamiski. To sauc par COUNTIFS ar mainīgajām kolonnām. Gifā esmu izmantojis kādu nosacītu formatējumu, pamatojoties uz citu šūnu.
Formula var darboties arī ar skaitīšanas funkciju. Bet, ja vēlaties, lai būtu vairāki nosacījumi, izmantojiet funkciju COUNTIFS.
Radošo kolonnu diagramma, kas ietver kopsummas
Izveidojiet pārklājuma diagrammu programmā Excel 2016
Veiciet Pareto diagrammu un analīzi programmā Excel
Veiciet ūdenskrituma diagrammu programmā Excel
Excel Sparklines: sīkās diagrammas šūnā
Spidometra (mērinstrumenta) diagramma programmā Excel 2016