У Джона есть рабочий лист, содержащий записи, используемые в системе отслеживания затрат. Номера записей вводятся в столбец A, местоположения — в столбец B, а затраты — в столбец C. Не все записи имеют значение стоимости, введенное в столбец C. Джон хочет определить количество записей «с местоположением X и стоимостью <> 0».

Вашим первым импульсом может быть использование одной из функций рабочего листа, предназначенных для подсчета, например CountIf. Единственная проблема состоит в том, что CountIf не позволяет проверять два условия при вычислении решения. Однако есть несколько решений, которые вы можете использовать без необходимости добавления дополнительных столбцов или промежуточных вычислений.

Первое (и, возможно, самое простое) решение — использовать функцию рабочего листа СУММПРОИЗВ. Эта функция позволяет подсчитывать или суммировать данные из столбца, строки или массива с любым количеством критериев. Основной синтаксис следующий:

=SUMPRODUCT( (CONDITION1)  (CONDITION2)  (CONDITION3) * (DATACELLS) )

В данном конкретном случае вы можете составить формулу следующим образом:

=SUMPRODUCT((B2:B101="X")*(C2:C101>0))

При этом проверяются два разных условия.

Сначала проверяются ячейки в столбце B, чтобы узнать, равны ли они «X», затем соответствующие ячейки в столбце C проверяются, чтобы узнать, равны ли они 0. Оба условия возвращают либо True (1), либо False (0). Затем эти результаты умножаются друг на друга, в результате чего получается 1 или 0. Затем функция СУММПРОИЗВ складывает их вместе, в результате получается совокупный счет.

Другое решение — создать формулу массива, которая будет выполнять вычисления за вас. Формулы массива отличаются от обычных формул тем, что они работают с несколькими ячейками, выполняя итерацию по ним для получения результата. Рассмотрим следующую формулу:

=(B2="X")*(C2>0)

Это возвращает одно значение — 1 или 0. В формуле используется та же базовая логика, которая описана в предыдущем объяснении решения СУММПРОИЗВ. Два логических сравнения возвращают 1 или 0, которые умножаются друг на друга, что дает 1 или 0 в качестве ответа. Теперь рассмотрим следующую формулу:

=SUM((B2:B101="X")*(C2:C101>0))

Теперь это очень похоже на предыдущую формулу СУММПРОИЗВ, но она не будет работать должным образом как прямая формула. Это связано с тем, что SUM не предназначен для итеративной работы с диапазоном ячеек. Если вы введете эту формулу как формулу массива (нажмите Shift + Ctrl + Enter, чтобы ввести ее), тогда Excel поймет, что вы хотите работать с каждым из диапазонов, в свою очередь, чтобы вычислить окончательную сумму, которая представляет собой количество записей, которые соответствуют заявленным критериям.

Различные способы использования формул массива — довольно обширная тема.

Для получения дополнительной информации о том, как работают формулы массива, см. Другие выпуски _WordTips, _ или посетите следующий веб-сайт:

http://www.cpearson.com/excel/ArrayFormulas.aspx

Третий вариант — использовать функции рабочего листа базы данных для возврата счетчика. Используя их, вы настраиваете «таблицу критериев» на своем листе, а затем функция использует критерии для анализа записей. Следующие шаги предполагают, что метками столбцов для трех столбцов являются RecNum, Location и Cost:

  1. Найдите несколько пустых ячеек либо на том же листе, что и ваши записи, либо на другом листе. (Я предполагаю, что для этого примера вы используете столбцы J и K.)

  2. В ячейке J1 введите слово Location.

  3. В ячейке K1 введите слово Cost.

  4. В ячейке J2 введите X.

  5. В ячейке K2 введите> 0. Теперь вы ввели свою таблицу критериев в ячейки J1: K2.

  6. Выберите ячейки J1: K2.

  7. Выберите «Имя» в меню «Вставка», затем выберите «Определить». Excel отображает диалоговое окно «Определить имя». (См. Рис. 1.)

  8. Введите имя Criteria, затем нажмите OK.

  9. В ячейке, в которой вы хотите подсчитать количество записей, соответствующих вашим критериям, введите следующее:

=DCOUNT(B1:C101,2,Criteria)

Обратите внимание, что первый аргумент, используемый с DCOUNT, — это второй и третий столбцы вашего списка записей. Этот аргумент также включает метки столбцов, которые необходимы, чтобы DCOUNT мог найти правильные совпадения критериев из таблицы критериев (третий аргумент).

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (2815) применим к Microsoft Excel 97, 2000, 2002 и 2003.