Как посчитать сумму значений столбца в эксель

Поиск нужного столбца и расчет его суммы.

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

Для поиска и выбора конкретного товара используем весьма популярную комбинацию функций ИНДЕКС+ПОИСКПОЗ.

Расчет суммы в G3 выполним так:

Итак, комбинация ИНДЕКС+ПОИСКПОЗ должны возвратить для дальнейших расчетов набор чисел в виде вертикального массива, который и будет потом просуммирован.

Опишем это подробнее.

ПОИСКПОЗ находит в шапке наименований таблицы B1:D1 нужный продукт (бананы) и возвращает его порядковый номер (иначе говоря, 2).

Затем ИНДЕКС выбирает из массива значений B2:D21 соответствующий номер столбца (второй). Будет возвращен весь столбик данных с соответствующим номером, поскольку номер строки (первый параметр функции) указан равным 0. На нашем рисунке это будет С2:С21. Остается только подсчитать все значения в этой колонке.

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

Как суммировать весь столбец либо строку.

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

Если поставить формулу суммы в G2

то получим общую выручку по первому отделу.

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

Все сказанное выше в полной мере относится и к работе со строками.

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

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

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

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

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

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

И самое интересное — если указать на нее мышкой и нажать Delete (или вкладка Главная — Редактирование — Очистить содержимое) — то она становится действительно пустой, и с ней начинают работать формулы и другие функции Excel без всяких ошибок.

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

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

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

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

В итоге, если в Е1 записано что угодно, отличное от 1, программа вернет строку нулевой длины. И если впоследствии формулу заменять значением (Специальная вставка – Значения), то получим нашу псевдо-пустую позицию.

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

Суммировать только положительные числа

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

Ниже приведена формула, которая сделает это:

=SUMIF(B2:B8,">0",B2:B8)

Приведенная выше формула СУММЕСЛИ  (SUMIF) принимает три аргумента:

• Первый аргумент — это диапазон критериев. Здесь мы проверим критерии и добавим только те значения, которые соответствуют критериям. В нашем примере это B2: B8.
• Второй аргумент — это сами критерии. Поскольку мы хотим суммировать только положительные числа, это «> 0». Помните, что когда вы используете оператор, он должен быть заключен в двойные кавычки.
• Третий аргумент имеет фактический диапазон, который вы хотите суммировать.

Поскольку в этом случае у меня есть только один критерий, я использовал формулу SUMIF. Если у вас есть несколько критериев для проверки, вы можете использовать эту формулу SUMIFS в Excel.

СУММИРОВАТЬ все положительные числа больше 100

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

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

Все, что мне нужно сделать, это настроить критерии так, чтобы вместо добавления всех положительных чисел он складывал все числа, превышающие 100.

Ниже приведена формула, которая сделает это:

=SUMIF(B2:B8,">100",B2:B8)

Как видите, все, что я сделал, это скорректировал второй аргумент, который является аргументом критерия.

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

В приведенных выше примерах мы использовали формулу SUMIF, в которой «диапазон критериев» и «диапазон сумм» совпадают.

Но, как я уже упоминал, вы также можете иметь их как отдельные диапазоны.

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

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

Ниже приведена формула, которая добавит только те ячейки в столбце B, где соответствующая ячейка в столбце C имеет положительное значение:

=SUMIF(C2:C6,">0",B2:B6)

Обратите внимание, что это аналогичная формула, которую мы использовали выше (с одним изменением). Вместо того, чтобы иметь одинаковые критерии и диапазон суммы, в этой формуле все по-другому

Совет от профессионала : когда вы указали диапазон критериев, вам не нужно указывать весь диапазон сумм. Например, следующая формула будет работать нормально — = SUMIF (C2: C6, ”> 0 ″, B2). Поскольку C2: C6 уже указан, вам не нужно указывать B2: B6. Подойдет только первая ячейка. Формула автоматически примет тот же диапазон размеров, начиная с B2.

Различные текстовые значения с условием.

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

Чтобы решить эту проблему, вам может помочь этот вариант:

Введите это в пустую ячейку, куда вы хотите поместить результат, F2, например. А затем нажмите Shift + Ctrl + Enter вместе, чтобы получить правильный результат.

Поясним: здесь A2:A18 это список покупателей, с учётом которого вы ограничиваете область расчётов, B2: B18 — перечень товаров, в котором вы хотите посчитать уникальные значения, Е2 содержит критерий, на основании которого подсчет ограничивается только конкретным покупателем.

Второй способ.

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

Применительно к нашему примеру:

С учетом ограничений ЕСЛИ() функция ПОИСКПОЗ определяет порядковый номер только для строк, которые соответствуют критериям.

Если какая-либо из ячеек в диапазоне критериев пустая, вам необходимо скорректировать расчёт, добавив дополнительно ЕСЛИ для обработки пустых ячеек.  Иначе они будут переданы в функцию ПОИСКПОЗ, которая в ответ сгенерирует сообщение об ошибке.

Вот что получилось после корректировки:

То есть все действия и расчёты мы производим, если в столбце B нам встретилась непустая ячейка: ЕСЛИ(B2:B10 <> «»….

Если у вас есть два критерия, вы можете расширить логику формулы путем добавления другого вложенного ЕСЛИ.

Поясним. Определим, сколько наименований товара находилось в первой партии первого покупателя.

Критерии запишем в G2 и G3.

В общем виде это выглядит так:

Подставляем сюда реальные данные  и получаем результат:

В первой партии 2 наименования товара, хотя и 3 позиции.

Если достаточно выполнения хотя бы одного условия (логика ИЛИ).

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

Но что если вы хотите подсчитать значения, для которых хотя бы одно из указанных условий имеет значение ИСТИНА , то есть использовать логику ИЛИ? В принципе, есть два способа сделать это — 1) сложив несколько формул СЧЕТЕСЛИ или 2) использовать комбинацию СУММ+СЧЕТЕСЛИМН с константой массива.

Способ 1. Две или более формулы СЧЕТЕСЛИ или СЧЕТЕСЛИМН.

Подсчитаем заказы со статусами «Отменено» и «Ожидание». Чтобы сделать это, вы можете просто написать 2 обычные формулы СЧЕТЕСЛИ и затем сложить результаты:

В случае, если нужно оценить более одного параметра отбора, используйте СЧЕТЕСЛИМН.

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

Способ 2. СУММ+СЧЁТЕСЛИМН с константой массива.

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

Вставьте СЧЕТЕСЛИМН в функцию СУММ, вот так:

СУММ(СЧЁТЕСЛИМН(диапазон;{«условие1″;»условие2″;»условие3»;…}))

В нашей таблице с примерами для подсчета заказов со статусом «Отменено» или «Ожидание» расчет будет выглядеть следующим образом:

=СУММ(СЧЁТЕСЛИМН(E2:E11;{«Отменено»;»Ожидание»}))

Массив означает, что в начале ищем все отмененные заказы, потом ожидающие. Получается массив из двух цифр итогов. А затем функция СУММ просто их складывает.

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

Как найти процент между числами из двух строк?

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

Предположим, у нас есть данные о продажах шоколада за 12 месяцев. Нужно проследить, как изменялась реализация от месяца к месяцу. Цифры в столбце С показывают, на сколько процентов в большую или меньшую сторону изменялись продажи в текущем месяце по сравнению с предшествующим.

Обратите внимание, что первую ячейку С2 оставляем пустой, поскольку январь просто не с чем сравнивать. В С3 записываем формулу:  

В С3 записываем формулу:  

Можно также использовать и другой вариант:

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

Если нам нужно сравнивать продажи каждого месяца не с предшествующим, а с каким-то базисным периодом (например, с январём текущего года), то немного изменим нашу формулу, использовав абсолютную ссылку на цифру продаж января:

Абсолютная ссылка на $B$2 останется неизменной при копировании формулы в C4 и ниже:

А ссылка на B3 будет изменяться на B4, B5 и т.д.

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

Десятичное число автоматически отображается в процентах, поэтому вам не нужно умножать его на 100.

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

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

Вид исходной таблицы:

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

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

Для определения количества просмотренных страниц пользователями default и user_1 запишем формулу:

В результате расчета получим:

Способ 1: счетчик на строке состояния

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

Но, хотя по умолчанию этот счетчик включен, и только ждет того, когда пользователь выделит определенные элементы, в некоторых случаях он может быть отключен вручную. Тогда актуальным становится вопрос о его включении. Для этого нужно кликнуть правой кнопкой мыши по строке состояния и в открывшемся списке установить галочку напротив пункта «Количество». После этого счетчик опять будет отображаться.

Способ 2: функция СЧЁТЗ

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

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

Открывается окно Мастера функций. Ищем в представленном списке элемент «СЧЁТЗ». После того, как это наименование выделено, жмем на кнопку «OK».

Запускается окно аргументов. Аргументами этой функции являются ссылки на ячейки. Ссылку на диапазон можно прописать вручную, но лучше установить курсор в поле «Значение1», куда нужно ввести данные, и выделить соответствующую область на листе. Если нужно произвести подсчет заполненных ячеек в нескольких удаленных друг от друга диапазонах, то координаты второго, третьего и последующего диапазона нужно вводить в поля под названием «Значение2», «Значение3» и т.д. Когда все данные введены. Жмем на кнопку «OK».

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

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

Способ 3: функция СЧЁТ

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

1. Как и в предыдущем случае, выделяем ячейку, куда будут выводиться данные и таким же способом запускаем Мастер функций. В нём выбираем оператора с наименованием «СЧЁТ». Жмем на кнопку «OK».

Запускается окно аргументов. Аргументы те же самые, что и при использовании предыдущего способа. В их роли выступают ссылки на ячейки. Вставляем координаты диапазонов на листе, в которых нужно подсчитать количество заполненных ячеек числовыми данными. Жмем кнопку «OK».

Для ручного введения формулы придерживаемся следующего синтаксиса:

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

3-D сумма, или работаем с несколькими листами рабочей книги Excel.

Чтобы подсчитать цифры из одинаковой формы диапазона на нескольких листах, вы можете записывать координаты данных специальным синтаксисом, называемым «3d-ссылка».

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

Посмотрите на этом небольшом видео, как применяются 3-D формулы.

Как видите, у нас имеется 4 одинаковых таблицы. Стандартный метод нахождения выручки за месяц –

В качестве аргументов перечисляем диапазоны данных.

А вот 3D-метод:

Мы указываем программе, что нужно произвести вычисления с ячейками B2:B8, находящимися на листах неделя1, неделя2, неделя3, неделя4. Здесь номер листа последовательно увеличивается на 1.

Важное замечание. Вы можете пользоваться 3D-ссылками и в других случаях

К примеру, выражение

найдет среднее значение однодневной выручки за месяц.

Как можно использовать сводную таблицу.

Вот обычная задача, которую все пользователи Excel должны время от времени выполнять. У вас есть список данных (к примеру, названий товаров), и нужно узнать количество уникальных позиций в этом списке. Как это сделать? Проще, чем вы думаете 🙂

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

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

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

1. Когда откроется сводная таблица, расположите области строк, столбцов и значений так, как вам нужно. Если у вас нет большого опыта работы со сводными таблицами Excel, могут оказаться полезными следующие подробные рекомендации: Создание сводной таблицы в Excel.
2. Переместите поле, количество уникальных элементов которого вы хотите вычислить ( поле « Товар» в этом примере), в область « Значения» , щелкните его и выберите «Параметры значения поля…» из раскрывающегося меню.
3. Откроется диалоговое окно , прокрутите вниз до операции «Число разных элементов» , которая является самым последним пунктом в списке, выберите ее и нажмите OK .

Вы также можете дать собственное имя своему счетчику, если хотите.

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

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

Благодарю вас за чтение и надеюсь увидеть вас снова. Пожалуйста, не переключайтесь!

Суммирование произведений двух столбцов

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

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

1. Сделайте активной нужную ячейку.
2. Кликните на строку ввода формулы.

1. Напишите следующую команду.

=СУММ(

1. Выделите вручную значения в одной из колонок. Например, в нашем случае выделим столбец «Количество».

1. Затем сдвигаемся в формуле правее и ставим символ «*» (знак умножения). Если вы работаете на ноутбуке, то нужно будет нажать на сочетание клавиш Shift+8.

1. После этого пишем еще раз следующий код.

=СУММ(

1. На этот раз выделяем другой столбец.

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

Для завершения нужно нажать на клавишу Enter. В результате этого вы увидите сумму произведения двух столбцов.

Часто задаваемые вопросы и проблемы.

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

1. Возможен ли подсчет в несмежном диапазоне клеток?

Вопрос: Как я могу использовать СЧЕТЕСЛИ для несмежного диапазона или ячеек?

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

Неправильно: =СЧЕТЕСЛИ(A2;B3;C4;»>0″)

Правильно: = СЧЕТЕСЛИ (A2;»>0″) + СЧЕТЕСЛИ (B3;»>0″) + СЧЕТЕСЛИ (C4;»>0″)

Альтернативный способ — использовать функцию ДВССЫЛ (INDIRECT) для создания массива из несмежных клеток. Например, оба приведенных ниже варианта дают одинаковый результат, который вы видите на картинке:

=СУММ(СЧЁТЕСЛИ(ДВССЫЛ({«B2:B11″;»D2:D11″});»=0»))

Или же

=СЧЕТЕСЛИ($B2:$B11;0) + СЧЕТЕСЛИ($D2:$D11;0)

1. Амперсанд и кавычки в формулах СЧЕТЕСЛИ

Вопрос: когда мне нужно использовать амперсанд?

Ответ: Это, пожалуй, самая сложная часть функции СЧЕТЕСЛИ, что лично меня тоже смущает. Хотя, если вы подумаете об этом, вы увидите — амперсанд и кавычки необходимы для построения текстовой строки для аргумента.

Итак, вы можете придерживаться этих правил:

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

= СЧЕТЕСЛИ(A1:A10;10) или = СЧЕТЕСЛИ(A1:A10;C1)

Если ваши условия содержат текст, подстановочный знак или логический оператор с числом, заключите его в кавычки. Например:

= СЧЕТЕСЛИ(A2:A10;»яблоко») или = СЧЕТЕСЛИ(A2:A10;»*») или = СЧЕТЕСЛИ(A2:A10;»>5″)

Если ваши критерии — это выражение со ссылкой или же какая-то другая функция Excel, вы должны использовать кавычки («») для начала текстовой строки и амперсанд (&) для конкатенации (объединения) и завершения строки. Например:

= СЧЕТЕСЛИ(A2:A10;»>»&D2) или = СЧЕТЕСЛИ(A2:A10;»<=»&СЕГОДНЯ())

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

Например, = СЧЕТЕСЛИ(C2: C8;»<=5″) и = СЧЕТЕСЛИ(C2: C8;»<=»&5) работают одинаково хорошо.

1. Как сосчитать ячейки по цвету?

Вопрос: Как подсчитать клетки по цвету заливки или шрифта, а не по значениям?

Ответ: К сожалению, синтаксис функции не позволяет использовать форматы в качестве условия. Единственный возможный способ суммирования ячеек на основе их цвета — использование макроса или, точнее, пользовательской функции Excel VBA.

1. Ошибка #ИМЯ?

Проблема: все время получаю ошибку #ИМЯ? Как я могу это исправить?

Ответ: Скорее всего, вы указали неверный диапазон. Пожалуйста, проверьте пункт 1 выше.

1. Формула не работает

Проблема: моя формула не работает! Что я сделал не так?

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

Будьте очень осторожны с использованием пробелов. При создании одной из формул для этой статьи я был уже готов рвать волосы, потому что правильная конструкция (я точно знал, что это правильно!) не срабатывала. Как оказалось, проблема была на самом виду… Например, посмотрите на это: =СЧЁТЕСЛИ(A4:A13;» Лимонад»). На первый взгляд, нет ничего плохого, кроме дополнительного пробела после открывающей кавычки. Программа отлично проглотит всё без сообщения об ошибке, предупреждения или каких-либо других указаний. Но если вы действительно хотите посчитать товары, содержащие слово «Лимонад» и начальный пробел, то будете очень разочарованы….

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

И это все на сегодня. В следующей статье мы рассмотрим несколько способов подсчитывания ячеек в Excel с несколькими условиями.

Ещё примеры расчета суммы:

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

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

1. Сначала добавим новую строку.

1. Выделяем нужную ячейку и переходим в строку ввода функций.

1. На этот раз вводим другую формулу.

=СУММЕСЛИМН(

У этой функции аргументы расположены в ином порядке (в отличие от СУММЕСЛИ).

1. Здесь мы сразу выделяем тот столбец, в котором указано количество товаров. В прошлый раз мы это делали в последнюю очередь – здесь всё наоборот.

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

1. Указываем диапазон первого условия. В нашем случае это графа «Стоимость».

1. Для того чтобы не было ошибок, дописываем символ «;». Затем в двойных кавычках пишем условие сортировки. Например, в нашем случае это «>20». Также закрываем точкой с запятой. Если вы часто работаете в Excel или планируете там работать – нужно привыкать все аргументы закрывать этим символом.

1. Далее мы видим подсказку, что нужно проделать те же самые действия, но теперь для второго условия. Их может быть огромное количество. Просто каждый раз необходимо выделять нужный диапазон и затем указывать условие. Выделяем столбец «Статус».

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

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

В нашем случае мы видим, что дорогих товаров у нас 18, а продано из них всего 10.

Способ 2. Функция «Excel Sum»

Функция «SUM ()» является гораздо более эффективным способом сложения ячеек. Ее можно использовать для суммирования чисел отдельных ячеек. Тем не менее, она также позволяет вам добавить диапазон, просто указав первую и последнюю ячейки, которые будут суммированы. Это особенно удобно, потому что ее можно использовать для написания простой формулы, содержащей сотни или тысячи ячеек.

Считаем сумму с помощью функции «Excel Sum»

Синтаксис функции выглядит следующим образом — «= SUM (Number1, Number2, … Number255)», где:

1. Number1 — (обязательно) первое значение, которое должно быть суммировано. Этот аргумент может содержать фактические данные, конкретное число, или ссылку на ячейку с данными для суммирования.
2. Number2, Number3, … Number255 — (необязательно) дополнительные значения, суммируемые до 255.

Шаг 1. Выберите пустую ячейку в таблице или за ее пределами. Теперь нужно вставить формулу. Для этого нажмите на символ формул в панели инструментов и в появившемся окне выберите «СУММ».

В выбранную ячейку ставим знак «=», нажимаем на панели управления на символ формул, выбираем «СУММ»

Шаг 2. Теперь нужно настроить диапазон ячеек, которые будут суммироваться нашей формулой.

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

Шаг 3. Нажмите на кнопку «ОК». Теперь в выбранной вами ячейке отразится сумма.

Нажимаем «ОК»

Результат, полученный функцией «СУММ»

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

Итак, выбрав свободную ячейку, вписываем в нее следующее: «=Сумм(R29C4:R33C5)», где первый набор символов до двоеточия, это левая верхняя ячейка диапазона, а второй — правая нижняя. Вписав формулу, нажимаем на кнопку «Enter».

В выбранную ячейку ставим знак «=», вводим формулу вручную, нажимаем «Enter»

Помимо ввода вручную вы также можете воспользоваться сочетанием клавиш, которое при одновременном нажатии автоматически пропишет эту функцию в выбранную ячейку. Комбинация клавиш для входа в функцию «СУММ»: Alt + = (знак равенства). Пример:

1. Нажмите на ячейку, где должна находиться функция «SUM».

2. Нажмите и удерживайте нажатой клавишу «Alt» на клавиатуре.
3. Нажмите и отпустите знак равенства (=) на клавиатуре, не отпуская клавишу «Alt».

4. Отпустите клавишу «Alt». Функция «SUM» должна быть введена в активную ячейку.

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

Способы подсчета итогов в рабочей таблице

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

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

1. Выделить диапазон клеток, итог сложения которых нужно получить.
2. На вкладке “Главная” в правой стороне найти значок автосуммы, нажать на него.

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

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

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

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

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

1. После этого должно открыться окно настройки “Мастер функций”. Из открывшегося списка необходимо выбрать требуемую функцию “СУММ”.
2. Для выхода из окна “Мастер функций” нажать кнопку “ОК”.

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

От: admin

Эта тема закрыта для публикации ответов.