В прошлом посте мы рассмотрели . А сегодня мы узнаем, как в Excel посчитать сумму значений. Эта задача может поставить в тупик, если структура таблицы сложная, или нужно сделать выборочное суммирование. Дочитайте этот до конца, вас ждёт описание новой уникальной функции с отличным функционалом.
Как считают сумму значений новички? , где через «+» перечисляют все слагаемые и остаются довольными результатом. Но это работает, если слагаемых 5-10. А как быть, если их больше?
Суммесли и ее синтаксис
Матричные формулы - очень мощный инструмент. Начнем с простой задачи. Независимо от того, находится ли значение в этом диапазоне, легко проверить. Возвращает 1, если условие выполнено, и 0, если это не так. Если эти два значения истинности теперь умножаются, то снова получается либо 1, либо.
Если теперь сформировать дополнительный столбец. Кроме того, здесь не нужно умножаться, поскольку функция уже является частью функции. Добавляет все ячейки, значение которых превышает 5% от общей суммы. И если вы все еще хотите знать, сколько позиций это.
Сумма в строке состояния
Такой способ хорош, если нужно узнать сумму, без использования в других формулах и таблицах. А если результат сложения нужно отобразить на листе – можно пользоваться функциями суммирования.
Функции суммирования в Эксель
Простейшая функция суммирования выглядит так: =СУММ(аргумент1 ; аргумент2;… ) . Её можно использовать, когда есть перечень ячеек, массив, несколько массивов для сложения. Аргументами могут быть числа, ссылки на ячейки с числами, диапазоны. В следующем примере я просуммировал оклады работников отделов Экономики и Продаж в некой организации:
Функции базы данных работают с диапазоном критериев. Часто для определенных столбцов данных формируются формулы результата, которые затем должны учитывать только отфильтрованные записи данных. В отфильтрованной области данных это учитывает только те строки, которые сохраняются после фильтрации.
Текущий список можно найти в тексте справки. В этой статье мы представляем 9 полезных и интересных формул для использования в программе, легко запоминающихся и готовых к применению в ваших электронных таблицах. Похвалив за его широкий спектр функций, он может оказаться немного сложным для всех тех, кто использует его в первый раз. Как будто это было мало, нет, это приложение стало еще более мощным. Это позволяет суммировать несколько числовых данных. Для этого есть две силы.
Функция СУММ
Если нужно посчитать сумму произведений неких чисел, пользуйтесь функцией =СУММПРОИЗВ(массив1; [массив2];… ) . Функция перемножит соответствующие элементы массивов, а результаты умножения просуммирует. Понятно, что все массивы, заданные в функции, должны иметь одинаковое количество строк и столбцов.
Первая сумма двух ячеек. Вариант: Эта формула также суммирует все числа, содержащиеся в определенной прямоугольной области, считая, что первая ячейка расположена в верхней левой вершине и, как последняя, та, которая находится в нижней правой вершине.
Добавить в соответствии с заданным критерием
В следующих примерах «первая ячейка: последняя ячейка» всегда будет обозначаться термином «вариация». Второй способ позволит добавлять отдельные ячейки. Таблица может отображать разные данные, даже если вы не хотите работать со всеми из них одновременно. Если список длинный, может быть разумным сделать выбор каждого из идентичных элементов, чтобы добавить их.
В примере ниже есть оклады работников в у.е. и курсы валюты. Нужно посчитать суммарный оклад всех работников в валюте их страны. Т.е. формула будет иметь вид: =СУММПРОИЗВ(Оклады; Курсы ) . Функция умножит каждый оклад на курс и сложит все произведения друг с другом:
«Критерий» - это условие поиска в таблице. Вариантом является изменение ячеек, содержащих критерий, подлежащий оценке. Изменение суммы - это диапазон ячеек, в которых найдены суммы, подлежащие суммированию. В этом примере нашим критерием является категория программы. Чтобы рассчитать только количество загрузок в категории, введите следующую формулу.
Добавить по различным критериям
Таблица может предложить широкий спектр информации. Критерий 1 является первым условием поиска в таблице и т.д. Чтобы узнать общие очки, полученные мужчинами желтой команды, напишите в ячейке по вашему выбору следующую формулу. Вариант: необязательно выбирать первую и вторую ячейки столбца или очереди; вы также можете выбрать весь столбец.
Сумма произведений в Эксель
Суммирование с условием в Excel
Часто нужно просуммировать только те значения, которые отвечают какому-то условию. Для этого используем функцию =СУММЕСЛИ(Диапазон, Критерий, [Диапазон суммирования] ) . Как видим, для нее можно задать 3 аргумента:
- Диапазон – массив, в котором происходит проверка условия. Это обязательный аргумент;
- Критерий
– условие для отбора значений (обязательный аргумент). Ячейки из «Диапазона» будут проверены на соответствие этому критерию. Если вы ищете равенство какому-то числу – просто запишите это число в качестве аргумента. Остальные критерии заключаются в кавычки. Например:
- 12 – значение равно двенадцати
- «>12» — значение больше двенадцати
- «Обувь» — значение равно «Обувь»
- Диапазон суммирования – массив значений, которые будут просуммированы. Он должен иметь столько же строк и столбцов, сколько в «Диапазоне». Это необязательный аргумент. Если он не задан, просуммированы будут значения из массива «Диапазон».
В этом примере я сложил оклады всех сотрудников отдела логистики:
Что касается статистики, вы можете узнать, сколько раз информация повторяется над столбцом. В этом случае мы применяем следующую формулу. Критерием является условие поиска в таблице. В предыдущей таблице мы хотели узнать, сколько женщин участвовало в игре. Поэтому мы пишем в ячейке следующую формулу.
Добавить клетки, соответствующие различным условиям
Эта формула дает вам точную статистику для ответа на этот вопрос: сколько раз повторяется ряд конкретных условий в вашей таблице? Эта формула имеет решение. Если мы используем эту функцию в приведенной выше таблице, мы сможем определить, например, сколько мужчин являются частью желтой команды. Нам просто нужно написать следующую формулу в ячейке.
Сумма с условием
А что, если нужно задать несколько условий? Эксель прекрасно справится с такой задачей, используем функцию:
СУММЕСЛИМН(диапазон суммирования; диапазон условия1; условие1; диапазон условия2 ; условие2 ; … ) .
Конечно, диапазоны суммирования и условий должны иметь одинаковое количество столбцов и строк, иначе формула выдаст ошибку. В примере я просуммировал зарплату только работников отдела экономики с окладом выше 100 у.е.:
Сумма событий между двумя конкретными значениями
Он позволяет узнать, сколько чисел находится между двумя конкретными значениями по следующей формуле. Минимальные и максимальные значения - это значения, между которыми должны быть указаны числа, которые мы ищем. Поэтому мы должны написать следующую формулу в ячейке.
Было бы пустой тратой времени, чтобы повторить определение среднего. В основном потому, что очень просто вычислить его по следующей формуле. Чтобы вычислить среднее значение всех этих чисел, введите следующую формулу в ячейке. Как и в предыдущих случаях, он также работает с рядом чисел в нескольких столбцах или разделен запятыми.
Сумма с несколькими условиями
Мощная и гибкая функция АГРЕГАТ
А теперь представляю вам обещанную новинку. Функция АГРЕГАТ впервые появилась в Excel 2010 и сразу решила многие мои проблемы. Что если нужно посчитать сумму только видимых ячеек? Функция СУММ сложит все ячейки диапазона, даже если они скрыты. Что делать?
Максимум и минимум
Нахождение наибольшего или наименьшего значения среди большого количества данных может быть реальным сопротивлением. Он очень прост в использовании и написан следующим образом. Вариант. Вы можете применить поиск к нескольким изменениям в ячейке. В этом случае формулами будут.
Вы можете создавать формулы или функции в ячейках, которые автоматически выполняют вычисления с использованием данных во всех выбранных ячейках. Например, вы можете сравнить значения в двух ячейках, рассчитать сумму или продукт ячейки и т.д. результат формулы или функции отображается в ячейке, в которую вы ее ввели.
А если массив содержит , которые нужно игнорировать? Эти и многие другие задачи решает функция: =АГРЕГАТ(номер функции; параметр; массив;… ) . Функция имеет аргументы:
- Номер функции – укажите что именно нужно подсчитать. Например, количество значений, сумму, максимум и т.д. При вводе формулы появится подсказка:
Просмотр мгновенных вычислений для диапазона ячеек
Более 250 функций доступны для различных областей, включая статистику, технику и финансы; некоторые могут извлекать удаленные данные через Интернет. Подробная информация о каждой функции отображается в справочных формулах и онлайновых функциях и функциях браузера, которые отображаются при вставке знака равенства в ячейку.
Быстро сделайте сумму, среднее значение или количество диапазонов ячеек
Вы можете быстро найти сумму, средний, минимальный, максимальный, счетчик или продукт диапазона ячеек.- Параметр – даёт возможность выбрать данные, которые функция будет игнорировать. Например, скрытые ячейки, ошибки и др. При вводе так же будет подсказка:
Функция Агрегат, вторая подсказка
- Массив – та группа ячеек, для которой производится расчет
- Прочие параметры – в зависимости от значения первого аргумента, функция может попросить у вас дополнительные аргументы
Описывать функцию агрегат можно долго, но главное – вовремя узнать о её существовании, и начать свои эксперименты. Она вас не раз удивит и сэкономит множество времени, пользуйтесь!
Если формула имеет ошибку, она появляется в ячейке результата. Нажмите, чтобы просмотреть сообщение об ошибке. Если сообщение указывает, что причиной ошибки является другая ячейка, вы можете щелкнуть ссылку на ячейку, чтобы выбрать ячейку, содержащую ошибку.
Суммирование, вычитание, умножение или деление значений
Вы можете создавать простые или сложные арифметические формулы для выполнения вычислений значений в таблицах. 
Вы можете использовать операторы сравнения, чтобы узнать, равны ли значения двух ячеек или если значение больше или равно другому. Результат оператора сравнения выражается как «истина» или «ложь».
Прямо сейчас оставлю вас наедине со всеми описанными функциями. Попрактикуйтесь немного, чтобы они остались в вашей памяти.
А следующие посты снова очень важные – это . Если вы читаете этот пост – обязательно прочтите и следующий, это будет очень полезно!
Не забыли подписаться на блог? Сделайте это прямо сейчас, чтобы всегда первыми читать новые статьи!
Ячейки в формулах
Если формула имеет ошибку, появляется ячейка результата.
В формулах вы можете включать ссылки на ячейки, диапазоны ячеек или целые столбцы или строки данных, а также ячейки в других таблицах или других листах. Числа используют значения в контрольных ячейках для вычисления результата формулы.
Приведенные ниже примеры иллюстрируют использование ссылок на ячейки в формулах. Если опорный интервал состоит из более чем одной ячейки, начальная и конечная ячейки разделяются одним знаком с двумя точками. Имя таблицы и ссылка на ячейку разделяются двойной точкой. ячейка в «другой таблице для формулы», автоматически указывается имя таблицы. Если ссылка ссылается на ячейку таблицы на другом листе, имя листа также должно быть включено. Когда вы нажимаете ячейку на другом листе при создании формулы, имя листа и имя таблицы автоматически включаются в формулу. Чтобы обратиться к столбцу, вы можете использовать буквенное обозначение столбца. Следующая формула вычисляет общее количество ячеек в третьем столбце: для ссылки на строку вы можете использовать номер строки. Следующая формула вычисляет общее количество ячеек в первой строке: Чтобы ссылаться на заголовок строки или столбца, вы можете использовать имя заголовка. Формула ниже вычисляет общее количество всех ячеек в строке с помощью Заголовок «Доход». Функции браузера включают подробную информацию о каждой из этих функций, чтобы помочь вам выбрать тот, который соответствует вашим потребностям.
Суммировать в программе Excel умеет, наверное, каждый. Но с усовершенствованной версией команды СУММ, которая называется СУММЕСЛИ, существенно расширяются возможности данной операции.
По названию команды можно понять, что она не просто считает сумму, но еще и подчиняется каким-либо логическим условиям.
СУММЕСЛИ и ее синтаксис
Функция СУММЕСЛИ позволяет суммировать ячейки, которые удовлетворяют определенному критерию (заданному условию). Аргументы команды следующие:
В приведенном ниже примере добавляются количества для Ирландии. В следующем параграфе вы можете найти подробное описание параметров. Если мы предпочитаем отозвать функцию из списка функций, мы найдем функцию в Математической категории. Первые два параметра, обязательные, служат для определения линий, которые удовлетворяют условию, которое необходимо идентифицировать.
Почему третий необязательный параметр?
Третий параметр, необязательный, позволяет указать ячейки, на которые вы хотите применить сумму. Если этот параметр не указан, то сумма выполняется по тому, что было определено в первом интервале параметров. Когда имеет смысл оставить третий параметр пустым? Когда диапазон является числовым, и вам нужно добавить значения переменной, определенные в диапазоне.
- Диапазон – ячейки, которые следует оценить на основании критерия (заданного условия).
- Критерий – определяет, какие ячейки из диапазона будут выбраны (записывается в кавычках).
- Диапазон суммирования – фактические ячейки, которые необходимо просуммировать, если они удовлетворяют критерию.
Получается, что у функции всего 3 аргумента. Но иногда последний может быть исключен, и тогда команда будет работать только по диапазону и критерию.
На следующем изображении вы хотите сделать сумму всех сумм выше. Обратите внимание, что критерий всегда указывается в кавычках, даже если он является числовым. Их использование делает функцию более гибкой. В этом случае результат не изменится в отношении указания «Ирландии».
По сути, эти два разъяснения говорят нам, что. Почему все это важно? Потому что, не зная этих двух деталей, вы рискуете получить неправильные результаты! Вот некоторые практические примеры различных ситуаций, с которыми вы можете столкнуться. Пример №1 - Правильное использование.
Как работает функция СУММЕСЛИ в Excel?
Рассмотрим простейший пример, который наглядно продемонстрирует, как использовать функцию СУММЕСЛИ и насколько удобной она может оказаться при решении определенных задач.
Имеем таблицу, в которой указаны фамилии сотрудников, их пол и зарплата, начисленная за январь-месяц. Если нам нужно просто посчитать общее количество денег, которые требуется выдать работникам, мы используем функцию СУММ, указав диапазоном все заработные платы.
Прописываем аргументы.
- Диапазоном в данном случае будет являться список всех должностей сотрудников, потому что нам нужно будет определить сумму заработных плат. Поэтому проставляем E2:E14.
- Критерий выбора в нашем случае – продавец. Заключаем слово в кавычки и ставим вторым аргументом.
- Диапазон суммирования – это заработные платы, потому что нам нужно узнать сумму зарплат всех продавцов. Поэтому F2:F14.
Получилось 92900. Т.е. функция автоматически проработала список должностей, выбрала из них только продавцов и просуммировала их зарплаты.
Аналогично можно подсчитать зарплаты всех менеджеров, продавцов-кассиров и охранников. Когда табличка небольшая, кажется, что все можно сосчитать и вручную, но при работе со списками, в которых по несколько сотен позиций, целесообразно использовать СУММЕСЛИ.
Функция СУММЕСЛИ в Excel с несколькими условиями
Если к стандартной записи команды СУММЕСЛИ в конце добавляются еще две буквы – МН (СУММЕСЛИМН), значит, подразумевается функция с несколькими условиями. Она применяется в случае, когда нужно задать не один критерий.
Синтаксис с использованием функции по нескольким критериям
Аргументов у СУММЕСЛИМН может быть сколько угодно, но минимум – это 5.
- Диапазон суммирования. Если в СУММЕСЛИ он был в конце, то здесь он стоит на первом месте. Он также означает ячейки, которые необходимо просуммировать.
- Диапазон условия 1 – ячейки, которые нужно оценить на основании первого критерия.
- Условие 1 – определяет ячейки, которые функция выделит из первого диапазона условия.
- Диапазон условия 2 – ячейки, которые следует оценить на основании второго критерия.
- Условие 2 – определяет ячейки, которые функция выделит из второго диапазона условия.
Пример использования
- продавцом;
- женщиной.
Значит, будем применять команду СУММЕСЛИМН.
Прописываем аргументы.
- диапазон суммирования – ячейки с зарплатой;
- диапазон условия 1 – ячейки с указанием должности сотрудника;
- условия 1 – продавец;
- диапазон условия 2 – ячейки с указанием пола сотрудника;
- условие 2 – женский (ж).
Итог: все продавцы-женщины в январе получили в сумме 51100 рублей.
СУММЕСЛИ в Excel с динамическим условием
Функции СУММЕСЛИ и СУММЕСЛИМН хороши тем, что они автоматически подстраиваются под изменение условий. Т.е. мы можем изменить данные в ячейках, и суммы будут изменяться вместе с ними. Например, при подсчете заработных плат оказалось, что мы забыли учесть одну сотрудницу, которая работает продавцом. Мы можем добавить еще одну строчку через правую кнопку мыши и команду ВСТАВИТЬ.
У нас появилась дополнительная строчка. Сразу обращаем внимание, что диапазон условий и суммирования автоматически расширился до 15 строки.
Копируем данные сотрудника и вставляем их в общий перечень. Суммы в итоговых ячейках изменились. Функции среагировали на появление в диапазоне еще одного продавца-женщины.
Аналогично можно не только добавлять, но и удалять какие-либо строки (например, при увольнении сотрудника), изменять значения (заменить «январь» на «февраль» и подставить новые заработные платы) и т.п.
