Article #34365

Любой пользователь, работающий в этой программе, вероятно, знает, как вычислить сумму значений в ячейках электронной таблицы Excel. В этом поможет функция СУММ, занявшая видное место в последних версиях программы, поскольку, возможно, она используется гораздо чаще других. Но иногда перед пользователем может стоять несколько иная задача: узнать количество значений с заданными параметрами в заданном столбце. Не их сумма, а простой ответ на вопрос: сколько раз N-е значение встречается в выбранном диапазоне? В Excel вы можете решить эту проблему одновременно несколькими методами.

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

Метод 1: отображение количества значений в строке состояния

Это, наверное, самый простой способ работы с текстовыми и числовыми данными. Но он не может работать с условиями.

использовать этот метод предельно просто: выбрать интересующий массив данных (любым удобным способом). Результат сразу появится в строке состояния (Количество). В расчете участвуют все ячейки, кроме пустых.

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

Еще одним недостатком этого метода является то, что результат сохраняется только до тех пор, пока мы не отменим выбор ячеек. Те вам придется запомнить или записать где-нибудь отдельно.

Иногда бывает, что индикатор «Количество» по умолчанию не включается в строку состояния, но это легко исправить:

1. Щелкните правой кнопкой мыши строку состояния.
2. В открывшемся списке обратите внимание на строку «Количество». Если рядом с ним нет галочки, он не отображается в строке состояния. Щелкните строку, чтобы добавить ее.
3. Все готово, с этого момента этот индикатор будет добавлен в строку состояния программы.

Метод 2: применение функции СЧЕТЗ

Эта функция позволяет делать то же самое, но с одним существенным отличием: результат будет записан. Остальные недостатки предыдущего метода сохранены, т.е. ACCOUNT3 не работает с условиями.

Функция COUNT3 выполняет задачу подсчета всех заполненных ячеек в заданном диапазоне (пустые не учитываются). Формула функции может выглядеть иначе:

• = СЧЁТ (ячейка1; ячейка2; . ячейкаN)
• = СЧЁТ (ячейка1: ячейкаN)

В первом случае функция подсчитает все перечисленные ячейки. Во втором случае он определит количество непустых ячеек в диапазоне от ячейки 1 до ячейки N. Обратите внимание, что количество аргументов функции ограничено 255.

Попробуем использовать функцию COUNT3 на примере:

1. Выберите ячейку, в которой будет отображаться общий результат расчета.
2. Перейдите на вкладку «Формулы» и нажмите кнопку «Вставить функцию”.Кроме того, вы можете щелкнуть значок «Вставить функцию» рядом со строкой формул.
3. В открывшемся меню (Мастер функций) выберите категорию «Статистика», затем найдите в списке нужную функцию ACCOUNT3, выберите ее и нажмите ОК, чтобы начать ее настройку.
4. В окне «Аргументы функции» установите необходимые ячейки (перечислив их или задав диапазон) и нажмите кнопку ОК. Вы можете указать диапазон с заголовком или без него.
5. Результат расчета отобразится в выбранной нами изначально запрошенной ячейке. Учитываются все ячейки с любыми данными (кроме пустых).

Функция СЧЁТ подходит, если вы работаете только с числами. Ячейки, заполненные текстовыми значениями, не будут учитываться этой функцией. Остаток от COUNT почти идентичен COUNT метода, описанного ранее.

Это формула для функции СЧЁТ:

• = СЧЁТ (ячейка1; ячейка2; . ячейкаN)
• = СЧЁТ (ячейка1: ячейкаN)

Алгоритм действий также аналогичен тому, что мы обсуждали выше:

1. Выберите ячейку, в которой будет сохранен и отображен результат подсчета значений.
2. Заходим в функцию мастера любым удобным способом, выбираем нужную строку АККАУНТ в категории «Статистика» и нажимаем ОК.
3. В «Аргументах функции» мы устанавливаем диапазон ячеек или перечисляем их. Затем нажмите ОК.
4. Результат отобразится в выбранной ячейке. Функция COUNT игнорирует любые ячейки с пустым содержимым или текстовыми значениями. Следовательно, будут учитываться только ячейки, содержащие числовые данные.

Метод 4: оператор СЧЕТЕСЛИ

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

Синтаксис COUNTIF типичен для всех условных операторов:

= СЧЁТЕСЛИ (диапазон; критерий)

Диапазон — это область ячеек, в которой вам нужно найти количество совпадений для данного условия.

Критерий — это конкретное условие, которому функция пытается удовлетворить. Условие указывается в кавычках, оно может быть указано как точное совпадение с введенным числом или текстом или как математическое сравнение, обозначенное знаками «не равно» («»), «больше чем» (» > «) и» минус «(» /=

Давайте посмотрим на использование функции СЧЁТЕСЛИ:

1. Например, давайте определим, сколько раз слово «бег» встречается в столбце «Спорт». Перейдите в ячейку, где вы хотите увидеть окончательный результат.
2. Доступ к мастеру можно получить одним из двух описанных выше способов. В списке статистических функций выберите СЧЁТЕСЛИ и нажмите ОК.
3. Окно аргументов немного отличается от того, которое мы видели при работе с COUNT и COUNT. Введите аргументы и нажмите ОК.
   • В поле «Диапазон» укажите область таблицы, которая будет участвовать в расчете.
   • В поле «Политика» укажите условие. Нам нужно определить частоту появления ячеек, содержащих значение «run», поэтому мы пишем это слово в кавычках. Щелкните ОК.
   • 
4. Функция СЧЁТЕСЛИ подсчитает и отобразит количество совпадений с указанным словом в выбранной ячейке. В нашем случае их 16.

Чтобы лучше понять, как использовать функцию СЧЁТЕСЛИ, попробуем изменить условие:

1. Теперь давайте определим, сколько раз в одном столбце встречаются другие значения, кроме слова «бегать».
2. Выберите ячейку, перейдите к мастеру, найдите оператор СЧЁТЕСЛИ, нажмите ОК.
3. В поле «Диапазон» введите координаты того же столбца, что и в примере выше. В поле «Критерий» добавьте знак неравенства («») перед словом «выполнить».
4. После нажатия кнопки «ОК» мы получаем число, которое сообщает нам, сколько ячеек в выбранном диапазоне (столбце) не содержат слова «выполнить». На этот раз число 17.

Наконец, вы можете проанализировать задание с числовыми условиями, содержащими знаки «больше» («>») или «минус» («

1. Выполним обычные шаги, чтобы вставить функцию СЧЁТЕСЛИ в желаемую результирующую ячейку.
2. В поле диапазона укажите требуемый интервал между ячейками столбца. Задайте условие «> 350» в поле «Критерий» и нажмите ОК.
3. В заранее выбранной ячейке получаем итог: 10 ячеек содержат значения больше 350.

Метод 5: использование оператора СЧЕТЕСЛИМН

СЧЁТЕСЛИ очень похож на СЧЁТЕСЛИ, но позволяет вводить несколько диапазонов с одинаковыми или разными условиями.

Например, нам нужно посчитать количество проданных товаров более 300 штук, а также товаров, стоимость которых превышает 6000 руб.

Давайте посмотрим, как это сделать с помощью функции WHATIFS:

1. В мастере функций мы знакомо находим оператор СЧЁТЕСЛИМН, который находится в той же категории «Статические», и вставляем его в ячейку, чтобы просмотреть результат, нажав кнопку ОК.
2. Окно настроек функции выглядит так же, как СЧЁТЕСЛИ, но как только мы введем данные для первого условия, появятся поля для ввода второго.
   • В поле «Диапазон 1» введите координаты столбца, содержащего данные о продажах в единицах. В поле «Условие 1» согласно нашей задаче напишите «> 300”.
   • В «Диапазон 2» мы указываем координаты столбца, который содержит данные о цене. Как «Условие 2», соответственно, мы указываем «> 6000”.
3. Нажимаем ОК, и мы получаем в последней ячейке число, которое говорит нам, сколько раз в выбранных диапазонах были ячейки с указанными нами параметрами. В нашем примере это 14.

Метод 6: функция СЧИТАТЬПУСТОТЫ

В некоторых случаях мы можем столкнуться с задачей подсчета только пустых ячеек в массиве данных. Тогда будет чрезвычайно полезна функция СЧИТАТЬПУСТОТЫ, которая игнорирует все ячейки, кроме пустых.

Синтаксис очень простой функции:

= СЧИТАТЬПУСТОТЫ (диапазон)

Порядок действий практически такой же, как и перечисленные выше:

1. Выберите ячейку, в которой мы хотим отобразить окончательный результат, подсчитав количество пустых ячеек.
2. Откройте мастер, выберите «СЧИТАТЬПУСТОТЫ» среди статистических операторов и нажмите «ОК.
3. В окне «Аргументы функции» укажите желаемый диапазон ячеек и нажмите кнопку ОК.
4. Результат отобразится в ранее выбранной ячейке. Учитываются только пустые ячейки, все остальные игнорируются.

Заключение

Microsoft Excel — чрезвычайно мощный инструмент, который может решить множество задач любого уровня сложности. Даже в простейшей задаче подсчета количества значений программа может значительно облегчить жизнь пользователя, причем разными способами.