Как в эксель посчитать критериальную функцию

Вычисление вложенной формулы по шагам

Иногда трудно понять, как вложенная формула вычисляет конечный результат, поскольку в ней выполняется несколько промежуточных вычислений и логических проверок. Но с помощью диалогового окна Вычисление формулы вы можете увидеть, как разные части вложенной формулы вычисляются в заданном порядке. Например, формулу =IF(AVERAGE(F2:F5)>50,SUM(G2:G5),0) проще понять, если вы увидите следующие промежуточные результаты:

Шаги, показанные в диалоговом окне

Сначала выводится вложенная формула. Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.

=ЕСЛИ(40>50;СУММ(G2:G5);0)

Диапазон ячеек F2:F5 содержит значения 55, 35, 45 и 25, поэтому функция СРЗНАЧ(F2:F5) возвращает результат 40.

=ЕСЛИ(Ложь;СУММ(G2:G5);0)

Значение 40 не больше 50, поэтому выражение в первом аргументе функции IF (аргумент logical_test) имеет значение false.

Функция ЕСЛИ возвращает значение третьего аргумента (аргумент значение_если_ложь). Функция СУММ не вычисляется, так как она является вторым аргументом функции IF (value_if_true аргумент) и возвращается только в том случае, если выражение имеет значение true.

1. Выделите ячейку, которую нужно вычислить. За один раз можно вычислить только одну ячейку.
2. На вкладке Формулы в группе Зависимости формул нажмите кнопку Вычислить формулу.

Примечание: Кнопка Шаг с заходом недоступна для ссылки, если ссылка используется в формуле во второй раз или если формула ссылается на ячейку в отдельной книге.

• Некоторые части формул, использующие функции IF и CHOOSE, не вычисляются, а #N/A отображается в поле Оценка .
• Если ссылка пуста, в поле Вычисление отображается нулевое значение (0).
• Формулы с циклическими ссылками могут не оцениваться должным образом. Если нужны циклические ссылки, можно включить итеративное вычисление.
• Следующие функции пересчитываются при каждом изменении листа и могут привести к тому, что средство «Оценка формулы » дает результаты, отличные от результатов, отображаемых в ячейке: RAND, OFFSET, CELL, INDIRECT, NOW, TODAY, RANDBETWEEN, INFO и SUMIF (в некоторых сценариях).

СЧЁТЕСЛИ (функция СЧЁТЕСЛИ)

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

Самая простая функция СЧЁТЕСЛИ означает следующее:

• =СЧЁТЕСЛИ(где нужно искать;что нужно найти)

• =СЧЁТЕСЛИ(A2:A5;»Лондон»)
• =СЧЁТЕСЛИ(A2:A5;A4)

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

Имя аргумента

диапазон (обязательный)

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

критерий (обязательный)

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

Например, критерий может быть выражен как 32, «>32», В4, «яблоки» или «32».

В функции СЧЁТЕСЛИ используется только один критерий. Чтобы провести подсчет по нескольким условиям, воспользуйтесь функцией СЧЁТЕСЛИМН.

Примеры

Чтобы использовать эти примеры в Excel, скопируйте данные из приведенной ниже таблицы и вставьте их на новый лист в ячейку A1.

Количество ячеек, содержащих текст «яблоки» в ячейках А2–А5. Результат — 2.

Количество ячеек, содержащих текст «персики» (значение ячейки A4) в ячейках А2–А5. Результат — 1.

Количество ячеек, содержащих текст «яблоки» (значение ячейки A2) и «апельсины» (значение ячейки A3) в ячейках А2–А5. Результат — 3. В этой формуле для указания нескольких критериев, по одному критерию на выражение, функция СЧЁТЕСЛИ используется дважды. Также можно использовать функцию СЧЁТЕСЛИМН.

Количество ячеек со значением больше 55 в ячейках В2–В5. Результат — 2.

Количество ячеек со значением, большим или равным 32 и меньшим или равным 85, в ячейках В2–В5. Результат — 1.

Количество ячеек, содержащих любой текст, в ячейках А2–А5. Подстановочный знак «*» обозначает любое количество любых символов. Результат — 4.

Количество ячеек, строка в которых содержит ровно 7 знаков и заканчивается буквами «ки», в диапазоне A2–A5. Подставочный знак «?» обозначает отдельный символ. Результат — 2.

Распространенные неполадки

Возможная причина

Для длинных строк возвращается неправильное значение.

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

Для работы с такими строками используйте функцию СЦЕПИТЬ или оператор сцепления &. Пример: =СЧЁТЕСЛИ(A2:A5;»длинная строка»&»еще одна длинная строка»).

Функция должна вернуть значение, но ничего не возвращает.

Аргумент критерий должен быть заключен в кавычки.

Формула СЧЁТЕСЛИ получает #VALUE! ошибка при ссылке на другой лист.

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

Рекомендации

Помните о том, что функция СЧЁТЕСЛИ не учитывает регистр символов в текстовых строках.

Критерий не чувствителен к регистру. Например, строкам «яблоки» и «ЯБЛОКИ» будут соответствовать одни и те же ячейки.

Использование подстановочных знаков

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

Например, =СЧЁТЕСЛИ(A2:A5;»яблок?») возвращает все вхождения слова «яблок» с любой буквой в конце.

Убедитесь, что данные не содержат ошибочных символов.

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

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

ФУНКЦИЯ СЧЁТЕСЛИ поддерживает именованные диапазоны в формуле (например, =COUNTIF(fruit;»>=32″)-COUNTIF(fruit;»>85″). Именованный диапазон может располагаться на текущем листе, другом листе этой же книги или листе другой книги. Чтобы одна книга могла ссылаться на другую, они обе должны быть открыты.

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

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Excel: Функции СЧЕТЕСЛИ и СЧЕТЕСЛИМН

В случаях, когда при работе в Excel требуется подсчитать количество ячеек, удовлетворяющих заданному условию, можно использовать функцию Счётесли .

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

Счётесли (диапазон; критерий)

Диапазон – группа ячеек, для которых нужно выполнить подсчет.

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

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

Критерий проверки необходимо заключать в кавычки.

Критерий не чувствителен к регистру. К примеру, функция не увидит разницы между словами «налог» и «НАЛОГ».

Примеры использования функции Счётесли.

1. Подсчет количества ячеек, содержащих отрицательные значения Счётесли(А1:С2;» <0") Диапазон - А1:С2 , критерий - "<0"

1. Подсчет количества ячеек, значение которых больше содержимого ячейки А4: Счётесли(А1:С2;»>»&A4) Диапазон — А1:С2 , критерий — «>»&A4

1. Подсчет количества ячеек со словом «текст» (регистр не имеет значения). Счётесли(А1:С2;»текст») Диапазон — А1:С2 , критерий — «текст»

1. Для текстовых значений в критерии можно использовать подстановочные символы * и ? . Вопросительный знак соответствует одному любому символу,
   звездочка— любому количеству произвольных символов. Если требуется найти непосредственно вопросительный знак (или звездочку), необходимо поставить перед ним знак ~ . Например, чтобы подсчитать количество ячеек, содержащих текст, который начинается с буквы Т (без учета регистра), можно воспользоваться следующей формулой: Счётесли(А1:С2;»Т * «) Диапазон — А1:С2 , критерий — «Т * «

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

Счётесли(А1:С2;». «) Диапазон — А1:С2 , критерий — «. «

В функции Счётесли используется только один критерий.

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

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

Счётеслимн (диапазон1; условие1; [диапазон2]; [условие2]; …).

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

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

Пример использования:

1. Подсчет количества ячеек, в которых находятся даты из определенного периода (например, после 15 января и до 1 марта 2015г.).

Диапазон один — C1:C8 , условия — «>15.01.2015″ и »

Сумма в Еxcel: способы вычисления

Как посчитать? Сумма в Еxcel может вычисляться разными способами. Основные из них – сложение через знак «плюс», использование особой кнопки во кладке «Главная» и применение специальной функции для соответствующих расчетов.

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

В статье рассказывается:

1. Способы суммирования в Excel
2. Порядок расчета с помощью арифметической формулы
3. Порядок расчета с помощью кнопки «Автосумма»
4. Порядок расчета с помощью функции СУММ
5. Суммирование в соответствии с заданным условием
6. Суммирование в Excel по несколькими условиями
7. Часто задаваемые вопросы касательно вычисления суммы в Excel

Пройди тест и узнай, какая сфера тебе подходит:
айти, дизайн или маркетинг.
Бесплатно от Geekbrains

Способы суммирования в Excel

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

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

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

Для простого суммирования чисел в конкретном диапазоне (группе ячеек) обычно используют простую формулу сложения со знаком плюс. Однако при работе с несколькими числами удобнее использовать функцию СУММ. Ее применение менее подвержено ошибкам. При этом автофункция во многом еще проще.

Порядок расчета с помощью арифметической формулы

Для начала разберемся, как использовать арифметические формулы для вычисления суммы строк в Excel. Рассмотрим конкретный пример.

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

Узнай, какие ИТ — профессии
входят в ТОП-30 с доходом
от 210 000 ₽/мес
Павел Симонов
Исполнительный директор Geekbrains

Команда GeekBrains совместно с международными специалистами по развитию карьеры подготовили материалы, которые помогут вам начать путь к профессии мечты.

Подборка содержит только самые востребованные и высокооплачиваемые специальности и направления в IT-сфере. 86% наших учеников с помощью данных материалов определились с карьерной целью на ближайшее будущее!

Скачивайте и используйте уже сегодня:

Павел Симонов
Исполнительный директор Geekbrains

Топ-30 самых востребованных и высокооплачиваемых профессий 2023

Поможет разобраться в актуальной ситуации на рынке труда

Подборка 50+ бесплатных нейросетей для упрощения работы и увеличения заработка

Только проверенные нейросети с доступом из России и свободным использованием

ТОП-100 площадок для поиска работы от GeekBrains

Список проверенных ресурсов реальных вакансий с доходом от 210 000 ₽

Получить подборку бесплатно
Уже скачали 26918

Вставьте сюда символ «=». Щелкните левой кнопкой мыши на первую ячейку ряда, содержащую числовое значение. Адрес сразу же отобразится в элементе, дающем сумму. Поставьте символ «+». Затем щелкните на следующей ячейке в строке. Таким образом, поочередно отображаются символ «+» и адрес ячейки, относящейся к первому магазину в ряду.

Результирующая формула в данном случае: =B3+C3+D3+E3+F3+G3+H3.

Разумеется, если используются другие таблицы, то они будут выглядеть иначе.

Нажмите Enter на клавиатуре, чтобы вывести на экран общую выручку первой торговой точки. Результат отобразится в ячейке с формулой.

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

Порядок расчета с помощью кнопки «Автосумма»

Почти каждый пользователь Excel знает о кнопке Автосумма. Эта команда является настолько популярной, что доступна сразу в двух местах на ленте: в группе «Главная» → «Редактирование» и группе «Формулы» → «Библиотека функций».

В первой строке выделите все ячейки, содержащие числовые значения. Сделайте это, нажав левую кнопку мыши. Перейдите на вкладку «Главная» и на ленте панели инструментов «Редактирование» щелкните на значке «Автосумма».

Другой способ вызова функции Autosum – перейти на вкладку «Формулы», где на панели инструментов «Библиотека функций» нужно нажать кнопку «Автосумма».

Если переходить на другую вкладку вы не хотите, то после выделения строки введите комбинацию горячих клавиш Alt+=.

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

Для вас подарок! В свободном доступе до 17.03 —>
Скачайте ТОП-10 нейросетей, которые помогут облегчить
вашу работу
Чтобы получить подарок, заполните информацию в открывшемся окне

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

Порядок расчета с помощью функции СУММ

СУММ (сумма) – это математическая функция, используемая в Microsoft Excel для суммирования значений, содержащихся в диапазоне ячеек. Это могут быть любые числа, ссылки на ячейки, диапазоны, массивы и константы. С помощью данной функции можно обрабатывать порядка 255 аргументов.

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

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

Число 1 является обязательным аргументом, а число 2 – нет.

Здесь функция ожидает наличия хотя бы одного числа или диапазона, содержащего данные (число 1). Число 2 заключено в квадратные скобки, поскольку является необязательным аргументом. Для того чтобы понять, как пишется функция СУММ, воспользуемся конкретным примером: = СУММ (C20:C31; F4:F17).

Сначала идет символ «=», который указывает на начало ввода функции. Затем идет имя команды СУММ, далее левая скобка, означающая, что пора вводить аргументы, первый из которых C20:C31. Это диапазон со значениями, которые нужно просуммировать.

Следующий аргумент – F4:F17. Значения в нем также подлежат сложению. В конце ставим правую скобку в знак окончания перечисления и в завершении ввода функции нажимаем Enter.

Предположим, вам необходимо вычислить сумму продаж за первый год работы салона. Введите символ «=» в поле «Всего» и имя функции СУММ. Затем по вышеуказанной схеме идет перечисление аргументов. Для этого выделите левой кнопкой мыши нужный диапазон (например, 12 месяцев – 12 строк в таблице) и нажмите Enter. В результате сумма аргументов отобразится в колонке «Всего». Как было сказано выше, каждый аргумент может быть представлен в качестве диапазона, числа или ссылки на отдельные ячейки.

Суммирование в соответствии с заданным условием

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

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

Если необходимо просуммировать данные с учетом одного критерия, то лучше воспользоваться функцией СУММЕСЛИ. Синтаксис ее прост:

=СУММЕСЛИ (диапазон ячеек, удовлетворяющих условию; критерий отбора данных для суммирования; диапазон суммирования).

Дарим скидку от 60%
на курсы от GeekBrains до 17 марта
Уже через 9 месяцев сможете устроиться на работу с доходом от 150 000 рублей

Допустим, необходимо определить общее количество молотков на складе. Названия задаются в диапазоне B3:B70, а соответствующие количества – в диапазоне C3:C70. В результате получается следующее функциональное выражение:

=СУММЕСЛИ(В3:В70; «молоток»;С3:С70). Обратите внимание, что слово «молоток» является текстом, поэтому оно выделяется кавычками. Формула читается так: «Значения всех ячеек в диапазоне C3:C70 должны быть суммированы на позициях ячеек в диапазоне B3:B70, содержащей слово МОЛОТОК». Еще один вариант прочтения: «Если ячейка в диапазоне B3:B70 содержит слово молоток, то соответствующие этой позиции ячейки в диапазоне C3:C70 должны быть сложены».

Если нажать Enter и запустить эту формулу, то Excel начнет проверять все ячейки в диапазоне B3:B70. Если следующая проверяемая ячейка содержит слово «Молоток», то соответствующая ячейка в диапазоне C3:C70 суммируется. В противном случае соответствующая ячейка в C3:C70 пропускается.

Суммирование в Excel по несколькими условиями

Когда при суммировании нужно учесть более одного критерия, то ситуация усложняется. Существует несколько способов произвести вычисления, самый оптимальный из них – это использование функции СУММЕСЛИМН. Она осуществляет выборочное суммирование по различным заданным условиям. Общее количество правил, которые можно задать, ограничено 127. Чтобы воспользоваться данной функцией, нужно указать не менее двух условий. Можно оставить и одно, но тогда проще будет делать суммирование через СУММЕСЛИ.

Синтаксис СУММЕСЛИМН следующий:

=СУММЕСЛИМН(общий диапазон; диапазон для проверки на соответствие первому критерию (т. е. первое условие); первый критерий (ему должны удовлетворять ячейки в первом контрольном диапазоне критерия); диапазон для проверки на соответствие второму критерию (второе условие); второй критерий (второе условие)… и так далее до 127 контрольных диапазонов критериев и самих критериев).

Рассмотрим пример работы СУММЕСЛИМН. Предположим следующее. Названия товаров заданы в диапазоне B3:B80, количество упаковок для каждого товара в ячейках C3:C80, а цена, соответствующая товару, в диапазоне D3:D80. Необходимо найти общее количество упаковок рубашек, цена которых меньше 3000. Задача будет выглядеть следующим образом:

«Найти сумму интервалов C3:C80. При этом диапазон B3:B80 должен содержать слово «рубашка», а значение диапазона D3:D80 должно быть меньше 3000». Итоговая формула выглядит так:

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

Часто задаваемые вопросы касательно вычисления суммы в Excel

Почему вдруг возникают проблемы с суммированием?

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

Как изменить количество знаков после запятой, уже введенных в Excel, для корректного суммирования?

Количество отображаемых десятичных знаков для уже введенных на рабочем листе чисел можно изменить. Это делается с помощью кнопок на панели инструментов. Выделите ячейки, которые необходимо отформатировать. Перейдите на вкладку «Главная» на ленте. Чтобы увеличить или уменьшить количество знаков после запятой, нажмите соответствующие кнопки. С каждым щелчком количество символов будет меняться.

Только до 14.03
Скачай подборку материалов, чтобы гарантированно найти работу в IT за 14 дней
Список документов:

ТОП-100 площадок для поиска работы от GeekBrains

20 профессий 2023 года, с доходом от 150 000 рублей

Чек-лист «Как успешно пройти собеседование»

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

Как просуммировать видимые ячейки?

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

Как складывать и вычитать временные значения?

Существуют определенные правила вычисления суммы времени в Excel. Например, формула = («12:00»-«8:00»)*24 может быть использована для нахождения разницы между 8:00 и 12:00 для расчета заработной платы. Важный момент: значения времени Excel рассчитывает как часть дня. Для нахождения общего количества часов результат необходимо умножить на 24. Этот пункт не нужно выполнять, если требуется только добавить часы и минуты.

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