Знамо да функција ЦОУНТИФС у Екцелу може рачунати на више критеријума. Аргументе узима као неколико распона критеријума и критеријума. Критеријуме можемо динамички мењати давањем референце ћелије, али не можемо динамички да мењамо колону критеријума. Па, не директно, али можемо. То је оно што унапред учимо у екцел формулама. Радите ствари у Екцелу које се не могу урадити нормално. Да видимо како.
Учимо на примеру.
Контекст
Овде сам припремио податке о продаји коју су наши продајни консултанти обавили у различитим месецима у години. Они продају различите моделе нашег производа, опћенито назване модел1, модел2 итд. Оно што треба да урадимо је да динамички бројимо продају различитих модела у различитим месецима.
У ћелији И2 ћемо изабрати месец. У ћелији И2 ћемо изабрати модел. Ове вредности се могу променити. И број би такође требало да се промени. Функција ЦОУНТИФС треба да тражи колону за месец која ће бити опсег критеријума. Затим ће тражити модел у тој колони месеци.
Дакле, овде су критеријуми и опсег_ критеријума променљиви. Па како да направимо променљиву колону у ЦОУНТИФС? Ево како?
Коришћење именованог опсега за променљиву колону или опсег критеријума
Општа формула
= ЦОУНТИФС (ИНДИРЕЦТ (намед_ранге)), критеријуми)
Прво именујте сваку колону према њиховом наслову. Да бисте то урадили, изаберите табелу и притисните ЦТРЛ+СХИФТ+Ф3 и именујте колоне према горњем реду. Прочитајте о томе овде.
Дакле, распон Б3: Б11, Ц3: Ц11, Д3: Д11 и Е3: Е11 се зову Јан, Феб, Мар и Апр.
Напишите ову формулу у И4.
= ЦОУНТИФС (ИНДИРЕКТНО (И2), И3)
Сада, ако промените месец у И4, одговарајући број месеци Модела 4 ће бити приказан у И4.
Како то ради?
Формула је једноставна. Почнимо изнутра.
ИНДИРЕЦТ (И2): Као што знамо ИНДИРЕЦТ функција претвара текст реф у стварну референцу. Пружили смо га И2. И2 садржи април. Пошто имамо опсег Е3: Е11 назив Апр, ИНДИРЕЦТ (И2) се преводи у Е3: Е11.
Формула је поједностављена на = ЦОУНТИФС (Е3: Е11, И3). ЦОУНТИФС броји све што је у И3 у опсегу Е3: Е11.
Када промените месец, колона ће се динамички мењати. Ово се зове ЦОУНТИФС са променљивим колонама. У гифу сам користио неко условно обликовање засновано на другој ћелији.
Формула може радити и са функцијом Цоунти. Али ако желите имати више услова, користите функцију ЦОУНТИФС.
Графикон колоне огласа који садржи укупне износе
Направите графикон преклапања у програму Екцел 2016
Извршите Парето графикон и анализу у програму Екцел
Извршите графикон водопада у Екцелу
Екцел Спарклинес: Ситни графикони у ћелији
Табела брзиномера (мерача) у Екцелу 2016