Формулы Excel — подсчитайте количество вхождений на нескольких листах в книге

Подсчитать количество вхождений в несколько листов в книге

Синтаксис используемых функций

 INDIRECT (ref_text, [a1]) COUNTIF (диапазон_критерия, критерии) SUMPRODUCT (array1, [array2], [array3]  , ...) 

Функция ДВССЫЛ возвращает ссылку, указанную в текстовой строке. Ссылки немедленно оцениваются для отображения их содержания.
Функция СЧЁТЕСЛИ используется для подсчета количества ячеек, отвечающих определенным критериям.
Функция СУММПРОИЗВ используется для умножения соответствующих компонентов в заданных массивах и возвращает сумму этих продуктов.

Что делать?

Чтобы подсчитать количество вхождений на нескольких рабочих листах книги, функцию СУММПРОИЗВ можно использовать вместе с Функция СЧЁТЕСЛИ и КОСВЕННО.

Формула

  = СУММПРОИЗВ (СЧЁТЕСЛИ (ДВССЫЛ ("'" & Таблицы & "  '! B1: E12 "), B6))  

Как работает эта формула

В выражении INDIRECT («‘» & Sheets & «‘! B1: E12»), «Sheets» — это имя диапазона B9: B11, который содержит ‘Sheet1’, ‘Sheet2’, ‘Sheet3’. Когда это выражение оценивается, массив создается как —

  {"'Sheet1'! B1: E12"; "'Sheet2'! B1: E12"; "'Sheet3'! B1: E12";}  

В этой книге три листа, каждый из которых содержит таблицу в диапазоне B1: E12. Листы ниже:

Массив имеет три значения, каждое из которых является текстом. значение и содержит имя листа, соединенное посредством сокатенации с диапазоном B1: E12. Следовательно, функция ДВССЫЛ преобразует каждое текстовое значение в правильную ссылку и передает в диапазон критериев функционала СЧЁТЕСЛИ вместе со значением критерия В6.

СЧЁТЕСЛИ затем возвращает три результата из трех предоставленных диапазонов и создает массив типа

  {1; 1; 1;}  

Наконец, функция СУММПРОИЗВ произвела сумму массива и вернула результат.

Оцените статью
nanomode.ru
Добавить комментарий