Darbe.ru

Быт техника Дарби
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel. Суммирование по диапазону, содержащему ошибку, с помощью формулы массива

Excel. Суммирование по диапазону, содержащему ошибку, с помощью формулы массива

Кто не знаком с формулами массива, предлагаю сначала почитать Введение в формулы массива. Рекомендую также Джон Уокенбах «Формулы в Microsoft Excel 2010», а именно часть IV. Формулы массивов (глава 14. Знакомство с массивами; глава 15. Магия формул массива).

Наверное, вы сталкивались с тем, что функция СУММ не работает, если ее применить к диапазону, в котором содержатся ошибки, например, #Н/Д, #ЗНАЧ! или #ДЕЛ/0!

Скачать заметку в формате Word, примеры в формате Excel

Допустим, данные организованы, как на рис. 1 (не самым удачным образом :)). Здесь в таблице «Продажи за период» данные из таблиц по месяцам собираются формулой ВПР, и, если продаж артикула в течение месяца не было, то формула ВПР возвращает значение ошибки #Н/Д.

Рис. 1. Объемы продаж по месяцам и за период с января по апрель

Суммирование по диапазону, часть ячеек в котором, содержат значение ошибки, также возвращает ошибку (рис. 2).

Рис. 2. Формулы СУММ в ячейках N24:Q24 дают ошибку

Мы могли бы исправить ситуацию, модернизировав формулы ВПР в ячейках N3:Q23, но тема нашей заметки иная, поэтому мы изменим формулы в ячейках N24:Q24, чтобы они суммировали числовые значения, игнорируя значения ошибок.

Для этого применим формулу массива (рис. 3)

Рис. 3. СУММ на основе формулы массива «справляется» со значениями ошибки в отдельных ячейках

Разберем, как работает формула массива:

ЕОШИБКА(N3:N23) возвращает значение ошибка для соответствующих ячеек из диапазона N3:N23.

  • 0, если ЕОШИБКА(N3:N23) вернула ошибку
  • значение из диапазона N3:N23, если ЕОШИБКА(N3:N23) не вернула ошибку

Формула массива <=СУММ(ЕСЛИ(ЕОШИБКА(N3:N23);0;N3:N23))>суммирует все значения, полученные на предыдущем шаге, то есть все значения из диапазона N3:N23, при этом, заменяя значения ошибок на нули.

Конечно же, эту задачу можно было решить и без применения формул массива. Например, добавлением колонок, в которых делались промежуточные вычисления по формуле ЕСЛИ(ЕОШИБКА(N3:N23);0;N3:N23). Более того, пока я не нашел ни одной задачи, которую без формул массива нельзя было бы решить, а с формулами массива – можно. Основная идея заключается в другом: формулы массива упрощают обработку данных, делают формулы проще и нагляднее (хотя кому, как… :))

Аналогичный подход можно применить для функций схожих с СУММ, например, СРЗНАЧ, МАКС, МИН. Используйте в этом случае, в формуле вместо нуля пустое значение, чтобы лишние нули не искажали результат:

Добавление от 30 июля 2013 г.

Начиная с версии Excel2010 гораздо проще использовать функцию АГРЕГАТ. Эта функция специально предназначена для выполнения операций с массивом (или ссылками), содержащим ошибочные и/или скрытые значения (рис. 4). Функция АГРЕГАТ (для массива) имеет три обязательных параметра и один необязательный:

  • тип исполняемой функции (всего их 16); среди них: суммирование, среднее значение, максимум, медиана, квартиль и др.;
  • метод обработки массива — какие ячейки пропускать, например, только содержащие ошибку или только скрытые ячейки, или и те и другие (всего 8 методов);
  • массив (он же диапазон ячеек)
  • необязательный параметр используется для некоторых типов функций, например, НАИБОЛЬШИЙ; в этом случае этот параметр указывает, какое наибольшее по счету значение взять (например, 2).

Рис. 4. Пример использования функции АГРЕГАТ; тип исполняемой функции — 9 — СУММ, параметр — 6 — пропускать значения ошибок.

6 комментариев для “Excel. Суммирование по диапазону, содержащему ошибку, с помощью формулы массива”

Здравствуйте,
Спасибо за классное описание функций. Я пробовал применить функцию СУММ(ЕСЛИ(ЕОШИБКА(N3:N23);0;N3:N23)) и функцию АГРЕГАТ в Google Docs Exel но они почему то там не работают. Мне надо посчитать суму в диапазоне ячеек, но в некоторых есть ошибка REF! И формула там не работает. Вы проверяли работает ли функция в Google Docs?
Спасибо за ответ

Читайте так же:
Макрос удаления строк по условию в excel

Что делать, если Эксель не считает или неверно считает сумму

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

Основные причины неисправности

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

Изменяем формат ячеек

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

Выбор формата

Чтобы проверить, действительно ли дело в формате, следует перейти во вкладку «Главная». Предварительно, необходимо выбрать непроверенную ячейку. В этой вкладке находится информация о формате.

Выбор из списка

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

Формат ячеек

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

Список форматов

В открытом окне находится полный список форматов с описанием и настройками. Достаточно выбрать нужный и нажать на «ОК».

Отключаем режим «Показать формулы»

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

Показать формулу

Для отключения функции «Показать формулы», следует перейти в соответствующий раздел «Формулы». Здесь находится окно «Зависимости». Именно в нем расположена требуемая команда. Чтобы отобразить список всех зависимостей, следует кликнуть на стрелочке. Из перечня необходимо выбрать «Показать» и отключить данный режим, если он активен.

Ошибки в синтаксисе

Ошибка синтаксиса

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

В таком случае, следует проверить правильное написание каждой ячейки, которая выдает неверный результат. Можно переписать все значения, воспользовавшись встроенным мастером.

Включаем пересчет формулы

запуск параметров

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

параметры вычислений

В открытом окне необходимо перейти во вкладку «Формулы». Здесь находятся параметры вычислений. Достаточно установить флажок на пункте «Автоматически» и сохранить изменения, чтобы система начала проводить перерасчет.

Ошибка в формуле

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

Читайте так же:
Как в ворде сделать сетку клетки

Ошибка в формуле

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

Откроется окно, которое отображает саму формулу. Здесь, следует нажать на «Вычислить», чтобы провести проверку ошибки.

Другие ошибки

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

Формула не растягивается

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

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

Неверно считается сумма ячеек

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

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

Формула не считается автоматически

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

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

Функция «суммеслимн» в Excel: примеры использования

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

Математические функции в Excel

В данной категории содержится порядка 80 функций. Здесь можно найти операторы, способные просчитать значения электронной таблицы любого формата. В Excel «суммесли» часто встречается, а набор тригонометрических функций подходит для определенного круга пользователей. В чем же выражается суть арифметического оператора, у которого есть аналог в последних версиях программного продукта «суммеслимн»? Его задача суммировать значения, которые попадают под определенные критерии.

В большей степени математические функции призваны автоматизировать работу пользователя. Если возникают вопросы по применению оператора, вызывают справку. Сделать это можно в окне «Аргументы функции» нажатием на ссылку «Краткая справка» или через F1.

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

Описание функции

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

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

Синтаксис функции выглядит следующим образом: =суммеслимн(диапазон_суммирования; [диапазон_условия1; условие1]; [диапазон_условия2; условие2]; …)

функция суммеслимн в excel примеры

Под диапазоном суммирования понимается массив, ячейки которого будут складываться, если они удовлетворяют последующим условиям. Другой блок аргументов в синтаксисе – диапазон_условия1; условие1. Они позволяют выбрать в конкретном массиве по первому фактору нужные ячейки, которые в дальнейшем суммируются в пределах первоначального интервала. Дополнительными критериями выступают последующие диапазон_условия10; условие10.

Особенности функции

  • Если сравнивать «суммесли» и «суммеслимн», то расположение аргументов в синтаксисе указанных операторов различается. У первой функции диапазон суммирования находится на третьей позиции, у второй – на первой.
  • Если неправильно ввести данные для «суммеслимн» в Excel, примеры выявляют ошибку. Дело в том, что размерность всех строк и столбцов диапазонов одинакова для данного оператора. «Суммесли» допускает различное количество ячеек в интервале суммирования и условия.
  • В случае перебора первого аргумента обнаруживаются пустые или текстовые ячейки, они игнорируются.
  • При прописывании критерия используют знак «*». Он обозначает, что искать нужно содержащийся фрагмент и не стопроцентное совпадение с условием.
  • Функция «суммеслимн» в Excel (примеры это подтверждают) допускают длину строки до 255 символов.
  • Ячейки, указанные в диапазоне суммирования, лишь тогда складываются, когда удовлетворяют всем поставленным условиям. Иначе говоря, выполняется еще логическая функция «И».
Читайте так же:
Можно ли вордовский документ перевести в эксель

Пример 1

Для закрепления материала пользователю нужно самому решать подобные задачи по «суммеслимн» в Excel. Примеры использования представлены ниже.

Даны 5 столбцов, где указаны:

  • дата;
  • цвет;
  • штат;
  • количество;
  • стоимость.

функция суммеслимн в excel примеры

Выполняется поиск по следующим критериям:

  • диапазон суммирования: F5:F11;
  • интервал первого условия: поиск по цвету;
  • начальный критерий отбора: содержится слово red;
  • диапазон второго условия: поиск по штату;
  • другой критерий: содержится аббревиатура TX.

Пример 2

Даны 4 столбца, в которых указаны:

  • А – категория товара;
  • В – конкретные продукты;
  • С – российский город;
  • D – объем продаж.

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

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

суммеслимн в excel примеры использования

Во втором операторе цифрами помечены аргументы «суммеслимн»:

  • 1 – диапазон суммирования (объем продаж);
  • 2 – интервал первого условия (поиск по категории);
  • 3 – критерий 1 (поиск слова «фрукты»);
  • 4 – диапазон второго условия (нахождение по городу);
  • 5 – критерий 2 (поиск слова «Казань»).

Пример 3

Даны 2 столбца. В первом указаны имена сотрудников, во втором – объем продаж для каждого из них. Необходимо создать 3 функции «суммеслимн» в Excel. Примеры, как это сделать, показаны на изображении.

excel суммесли

  1. Для первого оператора указывается диапазон суммирования (для всех трех функций он одинаков): В2:В5; интервал первого и второго условий совпадает с предыдущим, первый критерий отбирает строки, где объем продаж не менее 100, второй же – где не более 500.
  2. Для второго оператора указывается предыдущий диапазон суммирования. По первому столбцу идет отбор условий: первое говорит о поиске имен, где содержится буква «а», второе – о нахождении продавца, имя которого начинается на «И». Найденные значения суммируются.
  3. Для третьего оператора также указывается предыдущий диапазон суммирования. Для обоих условий поиск идет в первом столбце. Начальный критерий отбора: имя продавца «<> Иван». Второе условие: объем продаж более 200.

Пример 4

Даны 3 столбца, в которых указана дата, тип расходов, сумма на конкретный вид траты. Необходимо сделать выборку по двум условиям:

  1. Критерий не включает слово «Прочие».
  2. Условие содержит слово «Расходы*». Знак «*» говорит о том, что после введенной информации идет продолжение в ячейке столбца В.

формула суммеслимн в excel примеры

В качестве самостоятельного задания пользователь может ввести условие по дате за конкретный период: за месяц или неделю. Чтобы это сделать, необходимо в кавычках указать период, к примеру, «>01.01.2017». Вторым условием выступает: «<31.01.2017».

Функции Excel всегда подробно описаны в справке Microsoft. Если возникают вопросы, даже опытный пользователь не брезгует применить встроенную помощь программы.

Функция СЧЁТЕСЛИМН в Excel

Функция Excel СЧЁТЕСЛИМН возвращает количество ячеек, соответствующих одному или нескольким критериям. СЧЁТЕСЛИМН может использоваться с критериями, основанными на датах, числах, тексте и других условиях. СЧЁТЕСЛИМН поддерживает логические операторы (>, <,, =) и подстановочные знаки (*,?) Для частичного сопоставления.

Читайте так же:
Как в фотошопе добавить эффекты на фото

Возвращаемое значение

Синтаксис

Аргументы

  • диапазон1 — первый диапазон для оценки.
  • критерий1 — критерий для использования в диапазоне1.
  • диапазон2 — (необязательно) Второй диапазон для оценки.
  • критерий2 — (необязательно) критерий для использования в диапазоне2.

Версия

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

Функция СЧЁТЕСЛИМН в Excel подсчитывает количество ячеек в диапазоне, который соответствует одному заданному критерию. В отличие от более старой функции СЧЁТЕСЛИ, СЧЁТЕСЛИ может применять более одного условия одновременно. Условия поставляются с парами диапазон / критерий, и требуется только первая пара. Для каждого дополнительного условия необходимо указать другую пару «диапазон / критерий». Допускается до 127 пар диапазон / критерий.

Критерии могут включать логические операторы (>, <,, =) и подстановочные знаки (*,?) Для частичного соответствия. Критерии также могут быть основаны на значении из другой ячейки, как описано ниже.

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

Базовый пример

В показанном примере можно использовать СЧЁТЕСЛИМН для подсчета записей с использованием 2 следующих критериев:

Обратите внимание, что функция СЧЁТЕСЛИМН не чувствительна к регистру.

Двойные кавычки («») в критериях

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

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

Значение из другой ячейки

При использовании значения из другой ячейки в условии ссылка на ячейку должна быть объединена с оператором при использовании. В приведенном ниже примере СЧЁТЕСЛИМН будет подсчитывать значения в ячейке A1: A10, которые меньше значения в ячейке B1. Обратите внимание, что оператор «меньше» (текст) заключен в кавычки, а ссылка на ячейку — нет:

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

Не равно

Чтобы создать критерий «не равно», используйте оператор «», заключенный в двойные кавычки («»). Например, приведенная ниже формула будет подсчитывать ячейки, не равные «красным» в диапазоне A1: A10:

Пустые ячейки

COUNTIFS can count cells that are blank or not blank. The formulas below count blank and not blank cells in the range A1:A10:

Dates

The easiest way to use COUNTIFS with dates is to refer to a valid date in another cell with a cell reference. For example, to count cells in A1:A10 that contain a date greater than a date in B1, you can use a formula like this:

Notice we concatenate the «>» operator to the date in B1, but and are no quotes around the cell reference.

The safest way hardcode a date into COUNTIFS is with the DATE function. This guarantees Excel will understand the date. To count cells in A1:A10 that contain a date less than September 1, 2020, you can use:

Wildcards

The wildcard characters question mark (?), asterisk(*), or tilde (

) can be used in criteria. A question mark (?) matches any one character, and an asterisk (*) matches zero or more characters of any kind. For example, to count cells in a A1:A5 that contain the text «apple» anywhere, you can use a formula like this:

Читайте так же:
Макет ценника в excel

) is an escape character to allow you to find literal wildcards. For example, to count a literal question mark (?), asterisk(*), or tilde (

), add a tilde in front of the wildcard (i.e.

Notes

  • Multiple conditions are applied with AND logic, i.e. condition 1 AND condition 2, etc.
  • Each additional range must have the same number of rows and columns as range1, but ranges do not need to be adjacent. If you supply ranges that don’t match, you’ll get a #VALUE error.
  • Non-numeric criteria needs to be enclosed in double quotes but numeric criteria does not. For example: 100, «100», «>32», «jim», or A1 (where A1 contains a number).
  • The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches any sequence of characters.
  • To find a literal question mark or asterisk, use a tilde (

) in front question mark or asterisk (i.e.

Related videos

Как создать простую сводную таблицу Сводные таблицы — фантастические инструменты для суммирования данных, но вы также можете использовать формулы для создания собственных сводок с помощью таких функций, как СЧЁТЕСЛИ и СУММЕСЛИ. Посмотрите, как это делается в этом 3-х минутном видео Как использовать функцию СЧЁТЕСЛИМН В этом видео мы рассмотрим, как использовать функцию СЧЁТЕСЛИМН для подсчета ячеек, которые соответствуют нескольким критериям в наборе данных.

Диапазон суммирования в excel

Как посчитать сумму в столбце Excel

Многие пользователи знают о табличном процессоре Excel. Кто-то устанавливал его просто для открытия таблиц, а кто-то – для их составления. Часто во время внесения в таблицу данных может понадобиться калькулятор, чтобы посчитать немаленькие числа. Так что для экономии нашего времени функция суммирования (вычитания, умножения и вычитания) была встроена в Эксель. Однако не все знают, как применять её. Именно об этом и будет идти речь в статье.

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

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

Таблица для примера

Способ 1: Ввод ячеек

  1. Итак, в конце необходимого столбца выделяем ячейку, где нужно вывести сумму чисел. В ней вводим символ «=» («Равно», без кавычек). После него пишем любую ячейку данного столбца, значок «+», ячейку, «+», ячейку и так далее. Сначала пишется заглавная буква ячейки, а потом цифра. Если всё правильно, то цвет надписей будет меняться, а ячейки – выделяться.

Ввод ячеек

Результат ввода ячеек

Способ 2: Выделение столбца

Этот способ напоминает предыдущий. Но этот будет намного проще, так как вводиться формула будет сама.

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

Выделяем столбец

Приписываем Сумм и находим сумму

Способ 3: Отдел «Автосумма»

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

    На панели инструментов находим отдел «Формулы» и переключаемся на него. Там заходим в меню «Автосумма». В нём кликаем по кнопке «Сумма».

Отдел Автосумма

Результат использования отдела -Автосумма

Способ 4: Функция «СУММЕСЛИ»

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

  1. Заходим в отдел «Формулы» и нажимаем на «Автосумма». Теперь мы выбираем кнопку «Другие функции…».

Находим СУММЕСЛИ

Выбор СУММЕСЛИ

Вводим условия СУММЕСЛИ

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector