Знаем, че функцията COUNTIFS в Excel може да разчита на множество критерии. Той приема аргументи като няколко критерии и критерии. Можем да променяме критериите динамично, като даваме препратка към клетката, но не можем да променяме динамично колоната с критерии. Е, не директно, но можем. Това научаваме предварително формулите на Excel. Правете неща в Excel, които не могат да се правят нормално. Да видим как.
Нека се учим с пример.
Контекст
Тук съм подготвил данни за продажбите, извършени през различни месеци от годината от нашите търговски консултанти. Те продават различни модели на нашия продукт, с общо наименование model1, model2 и така нататък. Това, което трябва да направим, е да отчитаме динамично продажбите на различни модели в различни месеци.
В клетка I2 ще изберем месеца. В клетка I2 ще изберем модела. Тези стойности могат да бъдат променени. И броят също трябва да се промени. Функцията COUNTIFS трябва да търси колоната за месец, която ще бъде диапазон на критерии. След това ще търси модел в тази колона за месеци.
Тук критериите и диапазонът на критериите_и двете са променливи. И така, как да направим колоната променлива в COUNTIFS? Ето как?
Използване на именован диапазон за променлива колона или диапазон от критерии
Обща формула
= COUNTIFS (INDIRECT (named_ range), критерии))
Първо, наименувайте всяка колона според заглавията им. За да направите това, изберете таблицата и натиснете CTRL+SHIFT+F3 и наименувайте колоните според горния ред. Прочетете за това тук.
И така, Обхват B3: B11, C3: C11, D3: D11 и E3: E11 са кръстени съответно на Jan, Feb, Mar и Apr.
Напишете тази формула в I4.
= БРОЙ (ИНДИРЕКТЕН (I2), I3)
Сега, ако промените месеца в I4, съответният брой месеци на Model4 ще бъде показан в I4.
Как работи?
Формулата е проста. Нека започнем отвътре.
INDIRECT (I2): Както знаем, функцията INDIRECT преобразува текста ref в действителна справка. Ние сме го предоставили I2. I2 съдържа април. Тъй като имаме диапазон E3: E11 име Apr, INDIRECT (I2) се превежда на E3: E11.
Формулата е опростена до = COUNTIFS (E3: E11, I3). COUNTIFS брои всичко, което е в I3 в диапазон E3: E11.
Когато промените месеца, колоната ще се променя динамично. Това се нарича COUNTIFS с променливи колони. В gif използвах условно форматиране, базирано на друга клетка.
Формулата може да работи и с функция counter. Но ако искате да имате множество условия, използвайте функцията COUNTIFS.
Диаграма на колоната на рекламното послание, която включва суми
Създаване на диаграма с наслагване в Excel 2016
Извършете диаграма и анализ на Pareto в Excel
Изпълнете диаграма на водопада в Excel
Excel Sparklines: Малките диаграми в клетката
Диаграма на скоростомера (габарит) в Excel 2016