Как Начать Использовать СЧЕТЕСЛИ, СУММЕСЛИ и СРЗНАЧЕСЛИ в Excel

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

В этом уроке, вы узнаете, как можно использовать три весьма полезных формулы в Excel: СУММЕСЛИ, СЧЕТЕСЛИ  и СРЗНАЧЕСЛИ.

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

С правой стороны, есть ячейка: Dining Out Expense в которой используются эти три формулы, что бы отследить мои затраты:

• СЧЕТЕСЛИ - Используется для того чтобы подсчитать количество раз, когда в списке появляется слово "Restaurant".
• СУММЕСЛИ - Рассчитывает сумму всех затрат со словом "Restaurant".
  
• СРЗНАЧЕСЛИ - Рассчитывает среднее значения для расходов со словом "Restaurant".
  

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

• СУММЕСЛИ - суммирует значения, удовлетворяющие заданным условиям, как например, суммирует все затраты из одной категории.
• СЧЕТЕСЛИ - Подсчитывает количество ячеек в диапазоне, удовлетворяющих заданному условию, например, сколько раз какое-то название повторяется в списке.
• СРЗНАЧЕСЛИ - Рассчитывает условное среднее значение; вы можете рассчитать среднюю оценку только для экзаменов.

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

СЧЁТЕСЛИ, СУММЕСЛИ и СРЗНАЧЕСЛИ в Excel (Короткий ВидеоУрок)

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

Please accept preferences cookies to load this content.

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

Как Использовать СУММЕСЛИ в Excel

Используйте для этой части урока, вкладку SUMIF (лист с таким названием) в закачанном вами файле примеров.

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

Вот как работает формула СУММЕСЛИ:

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

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

Я хочу знать две вещи:

1. 1.Сколько всего денег я потратил в ресторанах в этом месяце.
2. Все затраты в этом месяце, из любой категории, которые превысили значение 50$.

Вместо того, чтобы вручную складывать данные, мы можем добавить пару формул СУММЕСЛИ, чтобы автоматизировать весь процесс. Я помещу результаты в таблицу с зеленой шапкой Restaurant Expense, которая находится справа. Посмотрите как я это делаю.

Сумма Затрат на Ресторан

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

Вот формула, которую я использую в этом примере:

=СУММЕСЛИ(B2:B17;"Restaurant";C2:C17)

Обратите внимание, что элементы разделены точкой с запятой (в онлайн версии разделителем служит запятая) . Эта формула выполняет три вещи:

• Смотрит, что находится в ячейках с В2 по В17 в категориях затрат
• Использует слово «Restaurant» в качестве критерия для выбора того, что суммировать
• Использует значения в ячейках С2-С17, что бы суммировать

Когда я жму ввод, Excel вычисляет сумму моих расходов на ресторан. Используя СУММЕСЛИ, легко делать легкие статистические расчеты, которые помогут вам отслеживать данные определенного типа.

Затраты Выше 50$

Мы посмотрели как делать проверку условия, по конкретной категории, а теперь давайте выполним суммирование все величин, значения которых больше чем какое-то значение, в не зависимости от категории. В этом случае, я хочу найти все затраты, которые превысили 50$.

Давайте напишем простую формулу, что бы найти сумму всех затрат выше 50$:

=СУММЕСЛИ(C2:C17;">50")

В этом случае, формула чуть проще: так как мы суммируем те же величины, что мы и проверяем на условие (С2-С17), мы просто должны указать эти ячейки. Затем мы должны добавить точку с запятой и потом ">50", что бы суммировать только те значения, которые больше 50$.

В этом примере используется знак "больше", но в качестве дополнительной тренировки: попробуйте суммировать все маленькие расходы, например все расходы, которые меньше 20 долларов или меньше.

Как Использовать СЧЕТЕСЛИ в Excel

Используйте для этой части урока, вкладку COUNTIF (лист с таким названием) в закачанном вами файле примеров.

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

Вот общий формат для формулы СЧЕТЕСЛИ:

= СЧЕТЕСЛИ(ячейки которые надо подсчитывать, критерий по которым ячейку принимать в расчет)

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

• Сколько раз в течение месяца я покупал одежду
• Количество затрат, значение которых равно или больше 100 долларов

Число Случаев Покупки Одежды

Мой первый СЧЕТЕСЛИ будет смотреть на тип расходов и подсчитывать количество покупок с категорией «Clothing» среди моих транзакций.

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

=СЧЕТЕСЛИ(B2:B17;"Clothing")

Эта формула смотрит в столбец с названием "Expense Type", подсчитывает количество раз, сколько ей встретилось слово "clothing", и суммирует их. В результате получается 2.

Количество Затрат на Сумму 100$+

Теперь давайте рассмотрим количество транзакций в моем списке значение которых от 100 долларов и выше.

Вот формула, которую я буду использовать:

=СЧЕТЕСЛИ(C2:C17;">100")

Это простая формула, состоящая из двух частей: она просто указывает Excel список данных которые нужно считать, и дает правило для подсчета. В этом случае, мы просматриваем ячейки С2-С17, на предмет значений, которые выше 100 долларов.

Как Использовать СРЗНАЧЕСЛИ в Excel

Используйте для этой части урока, вкладку СРЗНАЧЕСЛИ (лист с таким названием) в закачанном вами файле примеров.

И последнее, давайте посмотрим как использовать формулу СРЗНАЧЕСЛИ. Я думаю, к настоящему моменту, уже понятно, что СРЗНАЧЕСЛИ, можно  использовать для расчета среднего определенных величин, которые выбираются на основании условий, которые мы зададим для Excel.

Формат формулы СРЗНАЧЕСЛИ следующий:

=СРЗНАЧЕСЛИ(ячейки которые нужно проверить на условие; само условие; для каких ячеек рассчитывать среднее при удовлетворении условию)

Формат формулы СРЗНАЧЕСЛИ, такой же как у формулы СУММЕСЛИ.

Давайте используем СРЗНАЧЕСЛИ формулу, для расчета двух статистических величин для моих затрат:

1. Средние затраты на рестораны..
2. Среднее для всех затрат, которые меньше 25 долларов.

Затраты на Рестораны в Среднем

Чтобы подсчитать  сколько в среднем я потратил на рестораны, я написал формулу СРЗНАЧЕСЛИ, что бы рассчитать среднее значение на основании категории.

=СРЗНАЧЕСЛИ(B2:B17;"Restaurant",C2:C17)

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

• В2:В17 задает диапазон ячеек для которых проверяется выполнение условия. Так как категория затрат записана в этом столбце.
• Слово "Restaurant" задает значение, которое нужно искать.
• И наконец С2-С17 - из которого берутся значения для расчета среднего.

И наконец, Excel усредняет все мои затраты на рестораны в путешествии. По формуле, которую я ему дал.

Вы можете так же попробовать как работает эта формула заменив категорию "Restaurant" другой категорией, например "Clothing."

Среднее для Затрат Меньше чем 25$

Если я слежу за своими незначительными покупками, и хочу знать сколько я потратил в среднем, я могу написать СРЗНАЧЕСЛИ для затрат, значение которых меньше некой величины.

Вот формула, которую я использую, для того, чтобы сделать это:

=СРЗНАЧЕСЛИ(C2:C17;"<25")

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

Резюмируем и Продолжаем Обучение

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

Ключевой момент, для всех формул "...ЕСЛИ", которые мы рассмотрели в этом уроке, то, что вы можете применять условия для ваших расчетов в Excel.

Чем больше знаешь, тем больше хочется узнать. Я предлагаю вам еще три урока по Excel, с которыми вы можете продолжить свое обучение:

• Кроме СЧЁТЕСЛИ, СУММЕСЛИ и СРЗНАЧЕСЛИ, есть также просто условие ЕСЛИ, которое может быть использовано для других задач. Посмотрите наш урок Как Использовать Условие ЕСЛИ.
• Узнайте как использовать Дату и Время в Excel совместно с этими формулами, что бы обрабатывать данные на основе даты.
• Функция ВПР в Excel может использоваться для сопоставления значений из нескольких списков. Узнайте больше о том, Как Использовать Функцию ВПР в Excel из этого урока.

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