Счет если в эксель
Обновлено: 07.07.2024
Ниже приведены варианты функции СУММЕСЛИ Excel. Описания, пошаговый алгоритм использования в зависимости от варианта функции, с примерами использования и иллюстрациями.
СУММЕСЛИ следует использовать в тех случаях, когда Вам необходимо суммировать значения в диапазоне, которые соответствует указанному Вами критерию.
Возможные варианты данной функции:
- сумма, если больше;
- сумма, если меньше;
- сумма, если равно;
- сумма, если не равно;
- сумма если больше или равно;
- сумма если меньше или равно.
Отдельная статья описывает функцию СУММЕСЛИ Excel. Точное совпадение. Частичное совпадение. С текстовыми критерий. Содержит описание, примеры и иллюстрации. Статья находиться по этой ссылке: Функция СУММЕСЛИ в MS Excel. Точное совпадение. Частичное совпадение. Инструкция и примеры.
Самый простой и понятный способ вызвать данную функцию, это нажать в правом верхнем углу, рядом с кнопкой ∑, стрелку вниз (Рис. 1).
Появиться список функций. Как правило, это наиболее часто используемые функции. В этом списке уже может быть нужная Вам функция СУММЕСЛИ. В нашем случае ее там нет. Тогда выбираем пункт Другие функции. Он находиться внизу списка (Рис. 2).
Появляется диалоговое окно – Вставка функции. Самое верхнее, это поле для поиска нужной функции. Вводим название функции, выбираем ее в результатах поиска и нажимаем ОК (Рис. 3).
В нем есть три ключевых поля для заполнения:
Что бы выбрать нужный диапазон нажимаете на стрелочку в конце строки диапазона и выбираете нужные Вам ячейки с помощью мыши. Можно сразу выбрать нужный диапазон, не нажимая на стрелочку, но сначала нужно поставив курсор на нужную строку в диалоговом окне. Можно прописать диапазон вручную. Результат от этого не зависит. После того как все готово, нажимает ОК (Рис. 5).
Далее рассмотрим варианты использования данной функции на конкретных примерах, что бы понять, как они работают.
Напоминаю, что перед тем, как вызывать функцию, нужно выбрать ячейку, в которой должно появиться искомое значение, результат применения функции.
Сумма, если больше, Сумма, если меньше. СУММЕСЛИ.
Знаки, которые используются: >, <.
У нас есть 25 строк с названием Пример и номер примера, в таблице. Напротив каждого примера стоит значение (Рис. 6).
Сумма, если больше.
Формула в ячейке, как пример, выглядит вот так:
Напротив каждого примера стоит число от 100 до 1300. Нам нужно просуммировать, только значения, которые больше числа 500.
Вызываем функцию, как описано выше и выбираем нужный нам диапазон в строке – Диапазон (Е3;Е27). Это столбец со значениями чисел от 100 до 1300.
Вводим критерий. В строке Критерий, пишем: >500, вручную. Можно скопировать значение критерия с самой таблицы и добавить в ручную только знак >. Кавычки появятся автоматически (Рис. 7).
Нажимаем ОК. Напротив ячейки с словом ИТОГО, появилась сумма чисел больше 500 (Рис. 8)
Сумма, если меньше.
Формула в ячейке, как пример, выглядит вот так:
Допустим, нам нужно найти сумму в диапазоне значений (E3:E27) меньше числа 500.
Алгоритм точно такой же. В критерии указывает знак меньше <, и необходимое значение. В нашем примере 500.
Сумма, если равно, Сумма, если не равно. СУММЕСЛИ.
Знаки, которые используются: =, <>.
Сумма, если равно.
Формулы в ячейках, как пример, выглядят вот так:
В столбце D, есть четыре ячейки с Пример 1 (выделенные красным шрифтом). Наша задача суммировать значения, которые стоят только напротив Пример 1. Вызываем функцию СУММЕСЛИ. В Диапазоне выбираем нужный диапазон (столбец D) (D3:D27).
В Критерий, вручную пишем Пример 1 или копируем с таблицы. По этому критерию будет осуществляться поиск нужных ячеек в диапазоне (D3:D27).
В Диапазон_суммирования выбираем диапазон ячеек с значениями (столбец Е), которые будут суммироваться (E3:E27). Суммироваться будут те ячейки, которые будут отвечать нашему критерию и находиться напротив ячеек с значением Пример 1. Нажимаем ОК (Рис. 9).
Так же можно использовать данный вариант функции, когда из списка значений (цифр), нужно суммировать только цифры равные определенному значению. Например, в диапазоне значений (E3:E27), нам нужно суммировать только значения равные числу 100 (выделенные красным шрифтом). В строке Критерий указываем необходимое значений, после знака =. В нашем случае это цифра 100. Диапазон_суммирования не заполняем. (Рис. 10).
Получаем искомую сумму, в нашем случае это число 400.
Сумма, если не равно.
Формулы в ячейках, как пример, выглядят вот так:
Рассмотрим на примере, когда нам нужно суммировать все значений, кроме тех, которые находятся напротив ячейки с Пример 1.
Выбираем в Диапазон ячейки, содержащие наш критерий (D3:D27) – Пример 1
В Критерий прописываем: <> Пример 1. Получаем сумму всех значений (диапазон E3:E27), кроме тех, которые стоят напротив Пример 1 (диапазон (D3:D27)).
В Диапазон_суммирования выбираем ячейки, значения которых нужно суммировать (E3:E27). Нажимаем ОК. Получаем нужную нам сумму. (Рис. 11).
Или нам нужно суммировать только значение не равные числу 100.
Тогда в строке Диапазон указываем нужный нам диапазон для суммирования (E3:E27).
В Критерий пишем <>100.
Диапазон_суммирования не заполняем.
Нажимаем ОК. Получаем сумму всех ячеек, кроме тех, где значение равно числу 100. (Рис. 12).
Сумма если больше или равно, Сумма если меньше или равно. СУММЕСЛИ.
Знаки, которые используются: >=, <=.
Сумма если больше или равно.
Формула в ячейке, как пример, выглядит вот так:
Рассмотрим на примере. Нам нужно посчитать сумму значений в столбце (E3:E27). Но суммировать нужно только те числа, которые равны или больше значения числа 500. По стандартному алгоритму вызываем функцию СУММЕСЛИ.
В строке Диапазон выбираем диапазон ячеек, которые нужно просуммировать (E3:E27).
В Критерий пишем:>=500.
Диапазон_суммирования не заполняем.
Нажимаем ОК. Получаем сумму значений равною числу 500, плюс все, что больше этой цифры в заданном диапазоне (E3:E27) (Рис. 13).
Сумма если меньше или равно
Формула в ячейке, как пример, выглядит вот так:
Алгоритм точно такой же, как и в предыдущем примере. Только вместо знак больше, используем знак меньше и равно <=. Получаем сумму всех чисел равную или меньше числу 500, которые находятся в заданном диапазоне (E3:E27).
Обращаю Ваше внимание, что выше приведенные примеры, являются вариантами использования данной функции. Способ использования данной функции должен определяться задачей, которую необходимо решить.
Количества чисел в таблице Excel можно быстро выяснить, используя функцию «Счёт». Игнорируя текстовый формат, она учитывает только числовые значения.
Где используется и как работает функция СЧЁТ?
Часто, для анализа табличных данных не нужно производить никаких математических операций с числами. Достаточно уточнить количество показателей. Именно для такого случая пригодится эта простая функция СЧЁТ.
Аргументом функции может быть:
- любые значения, скопированные и поставленные на место аргумента;
- одна ячейка или целая область таблицы;
- формулы и функции.
Синтаксис функции выглядит следующим образом:
Все версии Excel, начиная с 2007, могут учитывать до 255 аргументов при расчёте результата этой формулы. Обязательным является только первый из них. При этом есть разница в том, как учитывается значение, если оно хранится в ячейке или введено в числе аргументов.
- Числа, даты и время всегда учитываются функцией СЧЁТ.
- Текст и ошибки никогда не учитываются.
- Логические значения и текстовое представление числа не учитывается, если оно хранится в ячейке. Если эти же элементы ввести в формулу в качестве аргумента, они будут учтены.
Для понимания этой особенности нужно рассмотреть наглядно на конкретных примерах.
Пример использования функции СЧЁТ в Excel
Пример 1. В таблицу введены некоторые значения. Среди них есть текст, числовые выражения и число, заключённое в кавычки «22». Именно его и считает программа Excel текстовым значением в ячейке.
Пропишем формулу, она будет учитывать содержимое массива ячеек, и будет выглядеть таким образом:
После введения формулы нажатием клавиши Enter, появится число 4. Именно столько чисел введено в ячейки при помощи цифр, которые не заключены в кавычки.
Теперь введём в ячейку ниже D2, другую функцию. В ней все табличные элементы будут прописаны в виде аргументов. Она будет выглядеть так:
=СЧЁТ(12; мост; кг; крыло; 33; 45678; «22»; сила; ампер; 16.02.1999; 14.07.1975; стена; потолок; 21)
Введя формулу, мы получим значение 5.
Разные результаты функции объясняются тем, что одно число («22») воспринимается программой, как текст, если оно находится в ячейке, и как число, если оно прописано в числе аргументов функции.
Пример использования функций СЧЁТЗ и СЧИТАТЬПУСТОТЫ в Excel
Вы сможете быстро определить количество заполненных или пустых клеток в заданной области таблицы. Для выполнения этих операций используются функции с именами «СЧЁТЗ» и «СЧИТАТЬПУСТОТЫ». Для наглядности этой возможности необходимо рассмотреть пример.
Пример 2. В таблицу введены разные значения. Среди них нет никаких закономерностей. Есть пустые и заполненные ячейки.
В любом месте таблицы можно ввести функцию. Для определения заполненных ячеек в диапазоне А1:С5 нужно прописать такую формулу:
Введение её через Enter даст результат 8. Именно столько заполненных ячеек есть в обозначенной области.
Чтобы узнать количество пустых клеток, нужно ввести функцию СЧИТАТЬПУСТОТЫ:
Введение формулы покажет результат 7. Это количество пустых ячеек в таблице.
Пример использования функции СЧЁТЕСЛИ с условием
Очень часто используется такая разновидность функции «СЧЁТ». С помощью заданной формулы можно узнать количество ячеек с заданными параметрами. Функция имеет имя «СЧЁТЕСЛИ». В ней могут учитываться такие аргументы.
- Диапазон. Табличная область, в которой будут искаться определённые элементы.
- Критерий. Признак, который разыскивается в заданной области.
Синтаксис выглядит так:
Функция может показать количество ячеек с заданным текстом. Для этого аргумент заключается в кавычки. При этом не учитывается текстовый регистр. В синтаксисе формулы не может быть пробелов.
Оба аргумента являются обязательными для указания. Для наглядности стоит рассмотреть следующий пример.
Пример 3. Есть ведомость с фамилиями студентов и оценками за экзамен. В таблице 2 столбца и 10 ячеек. Нужно определить, какое количество студентов получили отличную оценку 5 (по пятибалльной системе оценивания), а какое 4, потом 3 и 2.
Для определения количества отличников нужно провести анализ содержимого ячеек второго столбика. В отдельной табличке нужно использовать простую функцию подсчета количества числовых значений с условием СЧЁТЕСЛИ:
После нажатия на клавиатуре Enter будет получен результат:
Так, всего за несколько секунд, можно получить данные по обширным и сложным таблицам.
В данной статье рассмотрим варианты использования функции СЧЁТЕСЛИ с двумя (несколькими) критериями поиска. Более подробная информация с описанием функции СЧЁТЕСЛИ, ее возможностями и примерами использования, находиться по ссылке: Функция СЧЁТЕСЛИ в MS Excel. Описание и примеры
Предположим, что у нас есть простая таблица, с текстовыми данными и числами.
СЧЁТЕСЛИ с двумя критериями. Числовое значение.
Найдем количество ячеек в столбце Числа, числовые значения в которых, больше числа 50 и меньше числа 50. Сначала, используя функцию СЧЁТЕСЛИ, мы находим, количество ячеек, значения в которых больше числа 50. Можно сначала найти количество ячеек, с значением меньше числа 50. Это не имеет значение. Как это сделать описано в статье: Функция СЧЁТЕСЛИ в MS Excel. Описание и примеры. Теперь в строке формулы добавляем плюс «+», и еще раз используем функцию СЧЁТЕСЛИ. Диапазон тот же самый, но в критерии поиска задаем поиск значений меньше числа 50. Получаем общие количество ячеек, значение в которых больше и меньше числа 50.
Можно использовать и другие варианты: больше или равно, меньше или ровно, равно, не равно. В зависимости от задачи.
Так же, вместо суммирования результатов поиска по нескольким критериям, можно провести вычитание этих результатов. Или любое другое действие.
СЧЁТЕСЛИ с двумя критериями. Ссылка на ячейку.
В качестве критерия поиска, можем использовать значения, содержащиеся в ячейке, указывая ссылку на эту ячейку в поле Критерии, диалогового окна Аргументы функции. Вернемся к нашей таблице и найдем количество ячеек в столбце Числа, числовые значения в которых, больше числа 50 и меньше числа 50. При этом используем в качестве критериев поиска ссылки на ячейки. Используем ячейку С7, как одну из ячеек, в которой находиться число 50.
Получили результат 12 ячеек.
Обращаю внимание, что знаки «<» и «>» (или другие знаки), в таком варианте использования функции, необходимо заключать в кавычки. И ставить между знаками и ссылкой на ячейку символ «&».
СЧЁТЕСЛИ с двумя критериями. Текстовые критерии.
В качестве критерия поиска, в данном варианте, можно использовать текстовое значение (слово) или часть слова. Как работает функции при использование таких критериев поиска, можно узнать по ссылке: Функция СЧЁТЕСЛИ в MS Excel. Описание и примеры. Объединение нескольких критериев поиска в данном варианте происходит по такому же принципу, как и в вариантах, описанных выше. Возьмём для примера столбец, в котором содержаться название мебели. Найдем количество ячеек, в которых содержаться слово Стол и слово Шкаф. При этом, поиск будем осуществлять используя буквы находящиеся в этих словах. «Ст» и «ф» соответственно. К этим буквам необходимо добавить символ «*». В случае с «Ст», после, так как это первые буквы в слове. В случае с «ф», перед, так как это конец слова. Кавычки появятся автоматически.
Количество критериев не ограниченно двумя. Может быть больше, в зависимости от задачи.
Функция СЧЁТЕСЛИ в MS Excel, позволяет посчитать количество ячеек, которые отвечают заданному критерию поиска, в указанном диапазоне. Рассмотрим, как это работает на примерах.
Как вызвать функцию СЧЁТЕСЛИ СЧЁТЕСЛИ в MS Excel.
Нажимаем на кнопу Вставить функцию и в открывшемся диалоговом окне Вставка функции, в поле поиск, пишем СЧЁТЕСЛИ. Нажимаем найти. Обращаю внимание, что в поле поиска, писать СЧЁТЕЛСИ, необходимо именно с буквой Ё. По-другому поиск не найдет интересующую нас функцию. По результатам поиска, выбираем нужную нам функцию.
Аргументы функции СЧЁТЕСЛИ в MS Excel.
После того, как была выбрана функция СЧЁТЕСЛИ, появляется диалоговое окно Аргументы функции. В данном диалоговом окне есть два поля для заполнения:
- Диапазон, здесь необходимо указать диапазон ячеек, в которых будет осуществляться поиск ячеек с значениями, которые отвечают критериям поиска. Выбрать необходимый диапазон можно сначала поставив курсор на поле Диапазон, а потом выделив необходимые ячейки.
- Критерий, здесь необходимо указать условие (критерий поиска), согласно которому функция будет искать и подсчитывать количество ячеек в заданном Диапазоне.
Какие варианты критериев поиска существуют.
В данном пункте, на примерах рассмотрим, какие варианты критериев для поиска можно использовать в функции СЧЁТЕСЛИ. Предположим, что у нас есть простая таблица, с текстовыми данными и числами.
Больше (>), меньше(<), больше или равно (>=), меньше или равно (<=), равно (=), не равно (<>).
Больше.
Найдем, сколько ячеек в столбце Числа, содержат в себе значение больше числа 50. Вызываем функцию СЧЁТЕСЛИ в ячейку Е2. В диалоговом окне Аргументы функции, в поле Диапазон, указываем диапазон С3:С17. Это ячейки столбца Числа, в которых мы будем осуществлять поиск. В поле Критерий, пишем знак больше «>», и число 50.
Формула функции будет выглядеть вот так:
Кавычки появятся автоматически, после того, как нажать ОК.
По такому же принципу работаю остальные варианты. Если продолжать рассматривать на нашем примере, Диапазон для всех остальных случаев остаётся без изменения. Меняется Критерий.
Меньше.
Используем знак меньше «<».
В поле Критерий, диалогового окна Аргументы функции, пишем: <50.
Формула функции будет выглядеть вот так:
В результате получим количество ячеек, числа в которых меньше 50.
Больше или равно.
В поле Критерий, диалогового окна Аргументы функции, пишем: >=50.
Формула функции будет выглядеть вот так:
В результате получим количество ячеек, числа в которых больше или равны 50.
Меньше или равно.
В поле Критерий, диалогового окна Аргументы функции, пишем: <=50.
Формула функции будет выглядеть вот так:
В результате получим количество ячеек, числа в которых меньше или равны 50.
Равно.
В поле Критерий, диалогового окна Аргументы функции, пишем: =50.
Формула функции будет выглядеть вот так:
В результате получим количество ячеек, числа в которых равны 50.
Не равно.
В поле Критерий, диалогового окна Аргументы функции, пишем: <>50.
Формула функции будет выглядеть вот так:
В результате получим количество ячеек, числа в которых не равны 50.
Ссылка на ячейку в качестве критерия поиска функции СЧЁТЕСЛИ в MS Excel.
Возможно использовать ссылку на ячейку в качестве критерия поиска. В нашем примере найдем сколько ячеек в столбце Числа содержат в себе число 50. Значения в поле Диапазон, диалогового окна Аргументы функции, остаётся без изменений. В поле Критерий указываем любую ячейку из указанного диапазона, которая отвечает нашему критерию. В нашем пример выберем ячейку С11.
Формула функции будет выглядеть вот так:
Кавычки в таком варианте не нужны.
Нажимаем ОК. Получаем результат. Количество ячеек, которые содержат то же значение, что и ячейка С11. Три ячейки. Они залиты желтым цветом для наглядности.
При использование в качестве критерия поиска ссылку на ячейку, использовать знаки: <, >, =, <=, >=, <>, необходимо с знаком амперсанда (&), между этими знаками с самой ссылкой на ячейку.
Важный момент, в данном случае, кавычки возле знака больше (>) ставить нужно вручную.
Для примера, формулы с знаком больше будет выглядеть вот так:
Текстовые значения в качестве критерия поиска функции СЧЁТЕСЛИ в MS Excel.
В качестве критерия поиска в поле Критерий, в диалоговом окне Аргументы функции, можно использовать текстовое значения. Например, можно найти в столбце Значения, количество ячеек, которые не содержат в себе Значение 1. Меняем Диапазон поиска.
Формула функции будет выглядеть вот так:
Кавычки возле знака не равно (<>) ставить вручную.
Если использовать в качестве критерия не ссылку на ячейку, в которой есть текст, а сам текст, его необходимо заключить в кавычки вручную.
Формула функции будет выглядеть вот так:
Возможно осуществлять поиск указав в критерии только часть слова или одну букву. Например, у нас есть столбец, в котором указано название мебели.
Найдем количество ячеек, со словом Стол, указав в критерии поиска часть букв из этого слова, которые стоят в начале: Ст
Формула функции будет выглядеть вот так:
Теперь укажем в критерии поиска конец слова. Например, букву ф, из слова шкаф.
Формула функции будет выглядеть вот так:
Кавычки возле Ст* и *ф, ставятся автоматически.
Варианты использования функции СЧЁТЕСЛИ с двумя (несколькими) критериями поиска описаны в статье: Функция СЧЁТЕСЛИ с использованием двух (нескольких) критериев поиска. Описание и примеры.
Читайте также: