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

Как быстро суммировать ячейки столбца, игнорируя ошибки # N / A в Excel?

Как быстро суммировать ячейки столбца, игнорируя ошибки # N / A в Excel?

Для заданного критерия функция СУММЕСЛИ суммирует значения в пределах диапазона. Рассмотрим, например, что вы хотите суммировать только те числа, которые больше 5. Это можно сделать с помощью следующей формулы: =СУММЕСЛИ ЕСЛИ(B2:B25;»> 5″)

Браузер не поддерживает видео.

Это видео является частью курса «Добавление чисел в Excel.

При необходимости условия могут быть применены к одному диапазону, а соответствующие значения из другого диапазона могут быть просуммированы. Формула =SUMMEDI(B2:B5; «Иван»; C2:C5) учитывает только те значения из диапазона C2:C5, для которых соответствующие значения из диапазона B2:B5 равны «Иван».

Используйте функцию SUMMESLIMN для суммирования ячеек на основании ряда условий.

Использование функции СУММЕСЛИ ЕСЛИ для сопоставления строк длиной более 255 символов или строк #VALUE вернет неверные результаты!

Синтаксис

СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])

Аргументы функции СУММЕСЛИ описаны ниже

Диапазон является обязательным аргументом. Диапазон клеток, оцениваемых для условия. Ячейки в каждом диапазоне должны содержать числа, имена, таблицы или ссылки на числа. Пустые значения и текстовые значения игнорируются. Выбранный диапазон может содержать даты в стандартном формате Excel (см. примеры ниже).

Условие .Обязательный аргумент. Условие в виде числа, выражения, ссылки на ячейку, текста или функции, которое определяет, какие ячейки должны быть суммированы. Вы можете включить вопросительный знак (? ), соответствующий одному символу, и звездочку (*) для последовательности символов. Если вопросительный знак (или звездочка) должен быть найден напрямую, ему должна предшествовать тильда (

Можно выразить условия как 32, «>32», B5, «3? «, «яблоки*», «*

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

Читайте так же:
Как быстро восстановить размер ячейки по умолчанию в Excel?

Область подведения итогов .Необязательный аргумент. Ячейки, значения которых суммируются, если они отличаются от ячеек, указанных в качестве диапазона. Если аргумент range_summary опущен, Excel суммирует ячейки, указанные в аргументе range (те же ячейки, на которые влияет условие).

Sum_range должен быть того же размера и формы, что и диапазон. Из-за этой проблемы производительность находится под угрозой, и формула суммирует диапазон ячеек, который начинается с первой ячейки в sum_range, но имеет те же размеры, что и диапазон. Например:

Игнорировать строку, если все 4 ячейки равны 0; получить имя, если любая из 4 ячеек больше 0

У меня есть список имен (столбец A), а числа в столбцах B-F являются результатом формулы. Я пытаюсь создать код FOR LOOP, который проверяет столбцы с B по F. Если все ячейки с B по F равны нулю, код должен игнорировать текущую строку и переходить к следующей строке; если любая из ячеек в столбцах с B по F больше 0, код должен получить соответствующее имя из столбца A.

Пример:Если какая-либо из ячеек в B2, C2, D2 и E2 больше 1, тогда я должен получить имя / значение A2. если все клетки в B2, C2. D2 и E2 — все нули, затем перейдите к проверке следующей строки и сделайте то же самое.

Я пытался получить имена, чьи значения в ячейке больше 1 были из любого из 4 столбцов значений

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

4 ответа

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

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

Если у вас O365, вы можете использовать вспомогательную колонку и формулу.

Добавьте дополнительный столбец ячеек SUM в каждую строку (при желании этот столбец можно скрыть).

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

В качестве альтернативы можно использовать:

Укажите длину стола.

enter image description here

Кто-нибудь может сказать мне, где я ошибаюсь?

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

Но, безусловно, Exit For является логической ошибкой, поскольку он фактически завершает цикл, как только встречает пять нулей.

Для устранения этой логической ошибки необходимо изменить данный фрагмент кода следующим образом:

Где я использовал WorksheetFunction.CountIf() , которая позволяет работать с разными условиями, потому что ваша формулировка непонятна и для этого элемента («больше 0», «для всех ячеек … … равно нулю», «больше 1»).

Это не решение, но оно может сократить ваш исходный код.

Почему бы не создать скрытый столбец * который = SUM всей строки.

Затем получите значение из этого

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

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

Для тех, кто не знаком с формулами массивов, прочитайте «Введение в формулы массивов». Также рекомендую часть IV книги Джона Уокенбаха «Формулы в Microsoft Excel 2010». В главе 14, посвященной массивам, вы узнаете, как с ними познакомиться, а в главе 15: Формулы массивов.

Часто функция AML не работает при использовании диапазона, содержащего ошибки, например, #N/D, #ZNACH! или #DEL/0!

Скачайте записку в Word, примеры в Excel

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

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

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

Рисунок 2: формулы AMPS в ячейках N24:Q24 выдают ошибку

Наша заметка не будет касаться обновления формул БПФ в ячейках N3:Q23, поэтому мы опустим значения ошибок из этих формул в ячейках N24:Q24.

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

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

Проверим формулу массива:

ERROR(N3:N23) возвращает значение ошибки для соответствующих ячеек диапазона N3:N23.

  • 0, если ERROR(N3:N23) вернул ошибку
  • Значение из диапазона N3:N23, если ERROR(N3:N23) не вернул ошибку.

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

Конечно, эту задачу можно решить и без использования матричных формул. Например, добавив столбцы, в которых были выполнены промежуточные вычисления по формуле IF(ERROR(N3:N23);0;N3:N23). Кроме того, до сих пор я не нашел задачи, которую нельзя решить без формул массива, но можно решить с помощью формул массива. Основная идея заключается в другом: табличные формулы упрощают обработку данных, они делают формулы проще и понятнее (хотя для некоторых… :))

Тот же подход можно использовать для функций, аналогичных AMPS, например, LARGE, MAX, MIN. Если вместо пустого значения использовать ноль, лишние нули не будут искажать результат:

Дополнение от 30 июля 2013 года.

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

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

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

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

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

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