Как в excel подсчитать количество ячеек со значением

Самый быстрый способ

Самый простой, но в то же время поверхностный способ — подсчитать количество элементов в строке состояния. Их количество отображается в нижней части открытого окна.

Если вы хотите установить несколько простых параметров для расчетов, откройте настройки строки состояния. Это можно сделать, щелкнув по нему правой кнопкой мыши. В появившемся окне обратите внимание на ту часть, где написано «Среднее», «Количество», «Количество чисел», «Минимум», «Максимум» и «Сумма».

Выберите нужный вариант и узнайте больше о том, что содержит ваша таблица.

Как посчитать сумму с разных листов в Excel?

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

Практически каждый из нас, работая в Excel хотя бы раз, вероятно, использовал операцию сложения с использованием оператора плюса «+» и различных функций (СУММ, СУММЕСЛИ, СУММЕСЛИ и т.д.).

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

К решению этой проблемы можно подойти несколькими способами, мы рассмотрим несколько вариантов, как это сделать: от медленного и ручного до быстрого и полуавтоматического.

Как сложить листы в Excel?

Вариант 1. Ручной ввод

Начнем с одной стороны с медленного варианта, а с другой — с более понятного и наглядного способа.

Например, возьмем простую задачу и добавим данные в ячейку A1 из трех листов: Sheet1, Sheet2 и Sheet3. Для этого отметим поочередно ссылки на ячейку с разных листов и знаки плюса «+» в формуле «+»:

Также прочтите: diskpart обнаружил ошибку отказа в доступе

= Лист1! A1 + Sheet2! A1 + Sheet3! A1

Это очень простой вариант. Другой вариант ведения журнала может быть реализован аналогичным образом с помощью функции СУММ:

= СУММ (Лист1! A1; Лист2! A1; Лист3! A1)

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

Как это часто бывает, если вам нужно рассчитать сумму на два-три листа, описанный выше способ подойдет идеально. Но если усложнить задачу и представить аналогичный пример не для 3, а, например, для 30 листов, процесс немного затянется.

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

Вариант 2. Полуавтоматический ввод

Мы будем повторно использовать функцию СУММ, но на этот раз отметим ссылку на листы, разделенную двоеточиями:

= СУММ (Лист1: Лист3! A1)

В этом случае нам уже не так важно общее количество суммируемых листов, важно знать только начальный и конечный.

Следовательно, запись для добавления 10 или 20 листов будет более или менее одинаковой — Имя первого листа: Диапазон имени и фамилии.

Остановимся на логике формирования формулы чуть подробнее.

Напомню, что оператор двоеточия в Excel используется для объединения диапазона, который образует ссылку на все ячейки между первой и последней (включая сами ячейки).

Например, диапазон A1: A10 указывает на двумерную ссылку, которая содержит все ячейки между первой (A1) и последней (A10).

Когда мы добавляем еще один размер в виде листов, мы получаем так называемую трехмерную ссылку. Например, диапазон Sheet1: Sheet3! A1: A10 содержит все ячейки от Sheet1! A1: A10 до Sheet3! A1: A10.

Следовательно, в нашем примере суммируются все ячейки A1 от Sheet1 до Sheet3. Но при использовании этого метода суммирования есть некоторые тонкости, о которых стоит помнить, чтобы не наделать случайных ошибок, поговорим об этом подробнее.

Особенности трехмерных ссылок

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

• Добавление / копирование листа. Если вы добавите новый лист или скопируете существующий и вставите его между первым и последним (используется в формуле), то он автоматически будет участвовать в вычислении. Сама эта формула не изменится, и значение может измениться, если новый лист содержит данные в тех же ячейках;
• Удаление листа. Случай, аналогичный приведенному выше примеру. При удалении листа формула не изменится, а расчет изменится на основе удаленных данных (при удалении начальных или конечных листов формула изменится, промежуточная — формула останется без изменений);
• Переместите лист. При перемещении листа в другое место в книге формула не изменится, но набор листов между ними изменится (будут добавлены новые или будут удалены текущие).

Если описание кажется немного сложным, в этом случае попробуйте поэкспериментировать с формулой и переставить листы на конкретном примере и понять, как это работает.

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

Как осуществлять расчеты в программе Excel

По сравнению с Word Excel предлагает более полезные функции обработки табличной информации, которые можно использовать не только в офисах, но и на домашних компьютерах для решения повседневных задач.

В этой небольшой заметке мы поговорим об одном из аспектов использования электронных таблиц Excel: создании форм математических вычислений.

В Excel вы можете установить отношения между ячейками таблицы и определить для них правила. По этой причине изменение значения одной ячейки повлияет на значение другой (или других) ячеек. В качестве примера

предлагаю решить следующую задачу в Excel.

Три человека (Иван, Петр и Василий) осуществляют совместную коммерческую деятельность, которая заключается в оптовой закупке овощей в некоторых регионах страны, их транспортировке в регионы с повышенным спросом и продаже товаров по более высокой цене. Иван занимается покупкой, Василий — продажей, и они оба получают по 35% чистой прибыли.

Петр — возит товары, его доля 30%. По этой схеме партнеры работают постоянно и ежемесячно (а то и чаще) должны подсчитывать, сколько денег кому причитается.

При этом необходимо каждый раз производить расчеты с учетом покупной стоимости и отпускной цены товара, его количества, стоимости топлива и дальности транспортировки, а также некоторых других факторов.

А если создать таблицу расчета в Excel, Иван, Петр и Василий смогут очень быстро разделить заработанные деньги, просто введя необходимую информацию в соответствующие ячейки. Программа сделает за них все расчеты за несколько минут.

• Перед началом работы в Excel
  хочу обратить ваше внимание на то, что
  каждая ячейка в таблице Excel имеет свои координаты
  состоит из буквы в вертикальном столбце и номера горизонтальной строки.
• Работайте в Excel. Создание таблицы расчета:
• Открываем таблицу и подписываем ячейки, в которые будем вводить исходные данные (закупочная цена товара, его количество, отпускная цена товара, расстояние транспортировки, стоимость топлива).
• Затем мы подписываем некоторые другие промежуточные поля, которые будут использоваться таблицей для отображения результатов промежуточного расчета (расходы на покупку товаров, выручка от продажи товаров, стоимость фрахта, прибыль).

Подсчет Уникальных ТЕКСТовых значений в MS EXCEL

Например, рассмотрим следующий планшет:

 

Цель: подсчитать количество уникальных текстовых маркеров в A7: A15. Для этого мы используем формулу: = СУММПРОИЗВ ((A7: A15 <> «») / СЧЁТЕСЛИ (A7: A15; A7: A15)).

Если в указанных координатах есть числовые маркеры, они также будут рассчитаны. Чтобы не считать числа, вам нужно использовать: = СУММПРОИЗВ (ETEXT (A7: A15) / COUNTIF (A7: A15; A7: A15)).

Что такое процент?

Percentage (латинское процент — процент) — сотая часть. А. Обозначает долю чего-либо в целом. Например, 25% от 500 рублей — это 25 частей по 5 рублей каждая, то есть 125 рублей.

Базовая формула расчета процента от суммы выглядит так:

(Часть / Целое) * 100 = Процент (%)

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

Подсчитать количество повторяющихся значений в столбце

Чтобы рассчитать необходимое количество дубликатов в столбце, нужно сделать следующее:

1. Мы будем использовать ту же пластину, что и в примере выше. Выделяем ячейки столбца.
2. Переходим к окну форматирования.
3. В поле «Выберите тип правила:» щелкните запись «Использовать формулу для определения форматированных ячеек».
4. В строке «Форматировать формулу для определения форматированных ячеек» вбиваем = СЧЁТЕСЛИ ($ A: $ A; A5)> 1.

1. Готовый! Мы реализовали расчет одних и тех же показателей в столбце, а также выделили повторяющуюся информацию другим цветом.

Как найти повторяющиеся значения в Excel.

Например, у нас есть такой знак:

 

Чтобы подсчитать повторяющиеся значения, введите следующую формулу в G5: = ЕСЛИ (СЧЁТЕСЛИ (A $ 5: A $ 10; A5)> 1; СЧЁТЕСЛИ (A $ 5: A5; A5); 1). Затем вам нужно скопировать эту формулу на весь столбец.

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

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

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

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

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

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

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

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

   

3. Окно аргументов немного отличается от того, что мы видели при работе с COUNT и COUNT. Введите аргументы и нажмите ОК.
   В поле «Диапазон» укажите область таблицы, которая будет участвовать в расчете.
4. В поле «Политика» укажите условие. Нам нужно определить частоту появления ячеек, содержащих значение «run», поэтому мы пишем это слово в кавычках. Щелкните ОК.

5. 

6. Функция СЧЁТЕСЛИ подсчитает и отобразит количество совпадений с данным словом в выбранной ячейке. В нашем случае их 16.

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

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

   

4. После нажатия кнопки ОК мы получаем число, которое говорит нам, сколько ячеек в выбранном диапазоне (столбце) не содержат слова «выполнить». На этот раз число 17.

   

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

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

   

3. В заранее выбранной ячейке получаем итог: 10 ячеек содержат значения больше 350.

   

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

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

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

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

1. В мастере знакомым образом находим оператор СЧЁТЕСЛИМН, который находится в той же категории «Статическая», и вставляем его в ячейку, чтобы просмотреть результат, нажав кнопку ОК.

   

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

   

4. Нажмите ОК, и мы получим число в последней ячейке, которое говорит нам, сколько раз в выбранных диапазонах были ячейки с указанными нами параметрами. В нашем примере это 14.

   

С определенным текстом или значением

Функция СЧЁТЕСЛИ — позволяет подсчитать количество блоков, соответствующих определенному критерию. Диапазон записывается как аргумент — B2: B13 и с «;» указан критерий — «> 5».

Например, есть таблица, в которой указано, сколько килограммов того или иного продукта было продано за день. Подсчитываем, сколько было продано товаров весом более 5 килограмм. Для этого нужно посчитать, сколько блоков в столбце «Вес», где значение больше пяти. Функция будет выглядеть так: = СЧЁТЕСЛИ (B2: B13; «> 5»). Он рассчитает количество блоков с более чем пятью содержимым.

Как посчитать количество пустых и непустых ячеек?

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

Не пустой.

В некоторых уроках COUNTIF вы можете встретить советы по подсчету непустых ячеек, например:

СЧЁТЕСЛИ (диапазон;»*»)

Но дело в том, что приведенное выше выражение учитывает только ячейки, содержащие текстовые значения. Это означает, что те, которые включают даты и числа, будут рассматриваться как пустые (игнорироваться) и не будут включены в общую сумму!

Если вам нужно универсальное решение для подсчета всех непустых ячеек в указанном диапазоне, введите:

СЧЁТЕСЛИ (диапазон;»<>» & «»)

Он правильно работает со всеми типами значений — текстом, датами и числами — как вы можете видеть на изображении ниже.

Также можно подсчитать непустые ячейки в диапазоне:

= СЧЁТ (MI2: MI22).

Пустой.

Если вы хотите подсчитать пустые позиции в определенном диапазоне, вы должны следовать тому же подходу: использовать подстановочные знаки для текстовых значений в условиях и параметр «» для подсчета всех пустых ячеек.

Подсчитаем ячейки, в которых нет текста:

СЧЁТЕСЛИ (диапазон; «<>» & «*»)

Поскольку звездочка (*) соответствует любой последовательности текстовых символов, учитываются ячейки, которые не равны *, т. Е. Не содержат текста в указанном диапазоне.

Чтобы подсчитать пустые ячейки (все типы значений):

= СЧЁТЕСЛИ (E2: E22;»»)

Конечно, для этих случаев есть специальная функция

= СЧИТАТЬПУСТОТЫ (E2: E22)

Но не все знают о его существовании. Но теперь вы знаете …

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

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

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

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

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

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

1. Выберите ячейку, в которой будет отображаться общий результат расчета.
2. Перейдите на вкладку «Формулы» и нажмите кнопку «Вставить функцию”.

   
   Кроме того, вы можете щелкнуть значок «Вставить функцию» рядом со строкой формул.

   

3. В открывшемся меню (Мастер функций) выберите категорию «Статистика», затем найдите в списке нужную функцию ACCOUNT3, выберите ее и нажмите OK, чтобы начать ее настройку.

   

4. В окне «Аргументы функции» укажите необходимые ячейки (перечислив их или указав диапазон) и нажмите кнопку ОК. Вы можете указать диапазон с заголовком или без него.

   

5. Результат расчета отобразится в выбранной нами изначально запрошенной ячейке. Учитываются все ячейки с любыми данными (кроме пустых).

   

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

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

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

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

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

   

3. В «Аргументах функции» задайте диапазон ячеек или пронумеруйте их. Затем нажмите ОК.

   

4. Результат отобразится в выбранной ячейке. Функция COUNT игнорирует любые ячейки с пустым содержимым или текстовыми значениями. Следовательно, будут учитываться только ячейки, содержащие числовые данные.

   

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

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

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

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

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

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

   

3. В окне «Аргументы функции» укажите желаемый диапазон ячеек и нажмите кнопку ОК.

   

4. Результат отобразится в ранее выбранной ячейке. Учитываются только пустые ячейки, все остальные игнорируются.

   

СЧЕТЕСЛИ с несколькими условиями.

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

Количество чисел в диапазоне

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

Например, вы можете использовать для вычисления ячеек в диапазоне B2: B9, где значение больше 5 и меньше или равно 15:

= СЧЁТЕСЛИ (B2: B11; «> 5») — СЧЁТЕСЛИ (B2: B11; «> 15»)

Количество ячеек с несколькими условиями ИЛИ.

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

Давай сделаем это:

= СЧЁТЕСЛИ (A4: A13; «Лимонад») + СЧЁТЕСЛИ (A2: A11; «* сок»)

Обратите внимание, что мы включили подстановочный знак (*) во второй критерий. Он используется для подсчета количества всех видов сока в списке.

Как вы понимаете, здесь можно добавить больше условий.

Подсчет количества определенного значения ячейки в Excel при условии

Пример 2. По результатам сдачи экзаменов необходимо составить таблицу, содержащую данные о количестве студентов, сдавших предмет соответственно на 5, 4, 3 балла, а также не сдавших передать тему.

Предварительно выделите ячейки E2: E5, введите следующую формулу:

= СЧЁТЕСЛИ (B3: B19; D2: D5)

Описание аргументов:

• B3: B19 — диапазон ячеек для оценок за экзамен;
• D2: D5 — это диапазон ячеек, содержащий критерии для подсчета количества результатов.

Нулевые строки.

Также обратите внимание, что COUNTIF и COUNTBLANK подсчитывают ячейки с пустыми строками, которые кажутся пустыми только на первый взгляд.

Что это за пустые строки? Также они часто появляются при импорте данных из других программ (например, 1С). Внешне в них ничего нет, но на самом деле их нет. Если попытаться найти такие «пустышки» (F5 -Выбрать — Пустые ячейки) — они не обнаруживаются. Но фильтр данных видит их как пустые и фильтрует как пустые.

Дело в том, что существует такая вещь, как «строка нулевой длины» (или «нулевая строка»). Пустая строка появляется, когда программе нужно вставить значение, но вставлять нечего.

Проблемы начинаются, когда вы пытаетесь выполнить с ним математику (вычитание, деление, умножение и т.д.). Вы получаете сообщение об ошибке #VALUE! В этом случае функции СУММ и СЧЁТ игнорируют их, как если бы это был какой-то текст. А внешне нет.

И самое интересное, что если навести на него мышкой и нажать Delete (или вкладку Home — Edit — Delete content), то он станет действительно пустым, и формулы Excel и другие функции начнут работать с ним без ошибок.

Если вы не хотите считать их пустыми, используйте следующее выражение для подсчета действительно пустых ячеек:

= СТРОКИ (E2: E22) * КОЛОНЧИК (E2: E22) -СЧЕТЕСЛИ (E2: E22;»<>»&»»)

Откуда берутся пустые строки в ячейках? Здесь может быть несколько вариантов:

1. Изначально он есть, потому что так настроена загрузка и создание файлов в сторонней программе (например, 1С). В некоторых случаях такие выгрузки настраиваются таким образом, что нет пустых ячеек как таковых — они просто заполняются строкой нулевой длины.
2. была создана формула, производящая текст нулевой длины. Самый простой случай:

= ЕСЛИ (MI1 = 1; 10;»»)

Следовательно, если в E1 записано что-либо, кроме 1, программа вернет строку нулевой длины. А если позже формулу заменить на значение (Специальная вставка — Значения), то мы получим нашу псевдопустую позицию.

Если вы проверяете какие-то условия с помощью функции ЕСЛИ и планируете выполнять математические операции с результатами в будущем, то вместо «» ставьте 0. Так что проблем не будет. Нули всегда можно заменить или скрыть: Файл — Параметры — Дополнительно — Показывать нули в позициях, содержащих нулевые значения.

Статистический анализ посещаемости с помощью функции СЧЁТЕСЛИ в Excel

Пример 3. В таблице Excel хранятся данные о просмотрах страниц сайта пользователями за день. Определяет количество пользователей сайта в день и сколько раз в день пользователи с логинами по умолчанию и user_1 заходили на сайт.

Поскольку у каждого пользователя есть свой уникальный идентификатор в базе данных (Id), мы рассчитываем количество пользователей сайта в день, используя следующую матричную формулу, и нажимаем комбинацию клавиш Ctrl + Shift + Enter для его вычисления:

Выражение 1 / COUNTIF (A3: A20; A3: A20) возвращает массив дробных чисел 1 / number_of_meets, например, для пользователя с ником sam это значение равно 0,25 (4 раза). Общая сумма этих значений, вычисленная функцией СУММ, соответствует количеству уникальных вхождений, то есть количеству пользователей на сайте.

Чтобы определить количество просматриваемых страниц по умолчанию и пользователей user_1, напишите формулу:

Примеры подсчёта с единственным условием

Рассмотрим, как работает функция, на конкретном примере. Предположим, у нас есть список сотрудников гипотетического учреждения, состоящий из трех столбцов, в которых, помимо фамилий, указаны зарплата и пол сотрудников. Список может быть сколь угодно длинным, но примера из десяти сотрудников достаточно, чтобы понять, как работает описанная функция.

Для работы с описанной функцией необходимо выполнить следующую последовательность действий.

1. Выберите любую свободную ячейку для результата (например, H10), затем щелкните fx в строке формул Excel, чтобы ввести описанную функцию.

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

1. В раскрывающемся списке «Категория» выберите один из перечисленных списков и нажмите ОК.

1. В новом списке выберите нашу функцию и нажмите ОК.

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

1. Чтобы получить доступ к диапазону, щелкните красную наклонную стрелку справа от поля главной темы.

1. В новом компактном окне выделите интересующий диапазон (полный столбец B) и щелкните красную стрелку вниз, чтобы вернуться в предыдущее окно.

Теперь нам нужно задать условие расчета. Допустим, нам нужно узнать, сколько человек в данном учреждении получают зарплату в 50 тысяч рублей.

1. В поле «Условие 1» введите соответствующее значение в том формате, в котором оно отображается в таблице (обычное число), и нажмите «ОК». (Обратите внимание, что в окне аргументов появилась третья строка для второго условия. Она нам пока не нужна, поэтому мы не будем обращать на нее внимание.)

После ввода правильного условия найденное значение (2) сразу появляется в том же окне темы. После нажатия на ОК вы вернетесь в главное рабочее окно (в ячейке H10).

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

Следующий пример — критерий неравенства. Реализовано это так, как показано на скриншоте. Обратите внимание, что в этом случае для получения правильного результата в диапазоне условий название диапазона не выделяется — слово «Зарплата» в ячейке. К такому выбору приводит простое логическое рассуждение: ведь когда текст выделен, он тоже будет включен в расчет, искажая тем самым единственные нужные нам числовые результаты.

Использование СЧЕТЕСЛИ для подсчета дубликатов.

Еще одно возможное использование функции СЧЁТЕСЛИ в Excel — поиск дубликатов в столбце, между двумя столбцами или в строке.

1. Ищем дубликаты в одном столбце

Это простое выражение COUNTIF ($ A $ 2: $ A $ 24; A2)> 1 найдет все те же записи в A2: A24.

А другая формула СЧЁТЕСЛИ (B2: B24; ИСТИНА) сообщит вам, сколько существует дубликатов:

Для более наглядного представления найденных совпадений я использовал условное форматирование значения ИСТИНА.

2. Сколько совпадений между двумя столбцами?

Сравните list2 со list1. В столбце E мы последовательно берем каждое значение из list2 и подсчитываем, сколько раз оно встречается в list1. Если совпадений нет, это уникальное значение. На рисунке они выделены цветом с использованием условного форматирования.

Выражение = СЧЁТЕСЛИ ($ A $ 2: $ A $ 24, C2) копировать столбец E.

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

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

= СУММПРОИЗВ ((СЧЁТЕСЛИ (A2: A24; C2: C24)> 0) * (C2: C24<>»»))

Подсчитаем количество уникальных значений в list2:

= СУММПРОИЗВ ((СЧЁТЕСЛИ (A2: A24; C2: C24) = 0) * (C2: C24<>»»))

Получаем 7 уникальных записей и 16 дубликатов, как видно на рисунке.

Полезный. Если вы хотите выделить повторяющиеся позиции или целые строки, содержащие повторяющиеся записи, вы можете создать правила условного форматирования на основе формул COUNTIF, как показано в этом руководстве — Правила условного форматирования Excel.

3. Сколько дубликатов и уникальных значений в строке?

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

Подсчитаем количество дубликатов:

= СУММПРОИЗВ ((СЧЁТЕСЛИ (A2: K2; A2: K2)> 1) * (A2: K2<>»»))

Мы видим, что 13 сняли 2 раза.

Подсчитайте уникальные значения:

= СУММПРОИЗВ ((СЧЁТЕСЛИ (A2: K2; A2: K2) = 1) * (A2: K2<>»»))

Подсчет ячеек в строках и столбцах

Узнать количество секций можно двумя способами. Первый позволяет посчитать их построчно в выбранном диапазоне. Для этого нужно в соответствующее поле ввести формулу = ROWS (массив). В этом случае будут засчитаны все ячейки, а не только те, которые содержат числа или текст.

Второй вариант — = NUMBERCOLUMN (массив) — работает по аналогии с предыдущим, но считает сумму разделов в столбце.

Считаем числа и значения

Расскажу о трех полезных вещах, которые помогут вам в работе с программой.

1. Количество чисел в массиве можно рассчитать по формуле COUNT (значение1; значение2;…). Учитываются только те элементы, которые включают числа, то есть, если некоторые из них содержат текст, они будут пропущены, а дата и время будут приняты во внимание. В этой ситуации нет необходимости устанавливать параметры по порядку: вы можете написать, например, = COUNT (A1: C3; B4: C7;…).
2. Другая статистическая функция, COUNT, будет подсчитывать для вас непустые ячейки в диапазоне, то есть те, которые содержат буквы, числа, даты, время и даже логические значения FALSE и TRUE.
3. Обратное действие выполняет формула, показывающая количество пустых секций — СЧИТАТЬПУСТОТЫ (массив). Применимо только к непрерывному выбору.

Ставим экселю условия

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

Массив — это диапазон элементов, между которыми хранится запись. Это может быть только непрерывный прямоугольный набор смежных ячеек. Критерием считается только то условие, при котором производится выбор. Если он содержит текст или числа со знаками сравнения, мы заключаем его в кавычки. Если условие равно только числу, кавычки не нужны.

Разбираемся в критериях

Примеры критериев:

• «> 0» — засчитываются ячейки с номерами от нуля и выше;
• «Товар» — подсчитываются разделы, содержащие это слово;
• 15 — получить сумму элементов с заданной цифрой.

Для наглядности приведу подробный пример.

Чтобы подсчитать ячейки зоны от A1 до C2, значение которых больше указанного в A5, в строке формул необходимо написать = COUNTIF (A1: C2; «>» & A5).

Задачи на логику

Вы хотите установить логические параметры в Excel? Используйте подстановочные знаки * и?. Первый будет обозначать любое количество произвольных символов, а второй — только один.

Например, вам нужно знать, сколько Т-ячеек без учета регистра в электронной таблице. Мы устанавливаем комбинацию = СЧЁТЕСЛИ (A1: D6; «T *»). Другой пример: вы хотите узнать количество ячеек, содержащих только 3 (любых) символа в одном диапазоне. Итак, мы пишем = СЧЁТЕСЛИ (A1: D6;«???»).

Функция Счётеслимн

Countslimn (диапазон1; условие1; диапазон2; условие2; …).

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

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