0060

Мы знаем, что функция СЧЁТЕСЛИМН в Excel может рассчитывать на несколько критериев.

Он принимает аргументы как пару критериев, диапазон и критерии. Мы можем изменить критерии динамически, указав ссылку на ячейку, но мы не можем динамически изменять столбец критериев. Ну, не напрямую, но можем.

Это то, что мы узнаем заранее о формулах Excel. Делать в Excel то, что невозможно сделать обычным образом. Посмотрим как.

Давайте учиться на примере.

Контекст

Здесь я подготовил данные о продажах, осуществленных нашими консультантами по продажам в разные месяцы года. Они продают разные модели нашего продукта под общим названием model1, model2 и так далее. Нам необходимо динамически подсчитывать продажи разных моделей в разные месяцы.

0061

В ячейке I2 выберем месяц. В ячейке I2 мы выберем модель. Эти значения могут быть изменены. И счет тоже должен измениться.

Http: // COUNTIFS [COUNTIFS Function] должен искать столбец месяца, который будет диапазоном критериев. Затем он будет искать модель в этом столбце месяцев.

Итак, здесь и критерии, и диапазон критериев являются переменными. Так как же сделать переменную столбца в СЧЁТЕСЛИМН? Вот как?

Использование именованного диапазона для столбца переменной или диапазона критериев

Общая формула

=COUNTIFS(INDIRECT(named_range),criteria)

Сначала назовите каждый столбец в соответствии с их заголовками. Для этого выберите таблицу, нажмите CTRL + SHIFT + F3 и назовите столбцы в соответствии с верхней строкой. Об этом читайте здесь.

Итак, диапазоны B3: B11, C3: C11, D3: D11 и E3: E11 называются Jan, Feb, Mar и Apr соответственно.

Запишите эту формулу в I4.

=COUNTIFS(INDIRECT(I2),I3)

0062

Теперь, если вы измените месяц в I4, соответствующее количество месяцев Model4 будет отображаться в I4.

Как это устроено?

Формула проста. Начнем изнутри.

ссылка: / поиск-формулы-excel-косвенная-функция [КОСВЕННЫЙ] (I2):

Как мы знаем, функция ДВССЫЛ преобразует текстовую ссылку в фактическую ссылку. Мы предоставили его I2. I2 содержит Apr. Поскольку у нас есть диапазон E3: E11 name Apr, link: / lookup-formulas-excel-косвенная-функция [INDIRECT] (I2)

переводится как E3: E11.

Формула упрощена до = link: / counting-the-countifs-function-n-excel [COUNTIFS] (E3: E11, I3).

СЧЁТЕСЛИМН считает все, что находится в I3 в диапазоне E3: E11.

При изменении месяца столбец будет меняться динамически. Это называется СЧЁТЕСЛИМН с переменными столбцами. В гифке я использовал некоторую link: / conditional-formatting-conditional-formatting-based-on-another-cell-value [условное форматирование на основе другой ячейки].

Формула также может работать с функцией counttif. Но если вы хотите иметь несколько условий, используйте функцию СЧЁТЕСЛИМН.

Статьи по теме:

link: / counting-countifs-two-criterion-match [Получить COUNTIFS при совпадении двух критериев в Excel]

link: / counting-countifs-with-or-for-множественные критерии [Получить СЧЁТ МНЕНИЙ с функцией ИЛИ для множественных критериев в Excel]

Статья по теме:

link: / excel-chart-creative-column-chart-that-includes-totals [Creative Column Chart that Includes Totals] link: / excel-general-how-to-overlap-multiple-charts [Создать накладываемую диаграмму в Excel 2016] «link: / tips-how-to-create-a-pareto-chart-in-microsoft-excel [Выполнить диаграмму Парето и анализ в Excel]« ссылка: / tips-waterfall-chart [Выполнить диаграмму водопада в Excel] ссылка: / excel-chart-excel-sparklines-the-tiny-charts-in-cell [Спарклайны Excel: крошечные диаграммы в ячейке]

link: / excel-chart-speedometer-gauge-chart-in-excel-2016 [Таблица спидометра (датчика) в Excel 2016]