Как пропустить ошибки в excel при суммировании
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel для iPad Excel Web App Excel для iPhone Excel для планшетов с Android Excel 2010 Excel 2007 Excel для Mac 2011 Excel для телефонов с Android Excel для Windows Phone 10 Excel Mobile Excel Starter 2010 Еще. Меньше
Лучшее решение
Решение:убедитесь, что искомые значения есть в исходных данных, или используйте в формуле обработчик ошибок, например ЕСЛИERROR. Например, =ЕСЛИERROR(ФОРМУЛА();0), которая говорит:
=ЕСЛИ(при вычислении формулы получается ошибка, то показать 0, в противном случае показать результат формулы)
Если вы не знаете, что делать на этом этапе или какого рода помощь вам нужна, поищите похожие вопросы на форуме сообщества Excel или опубликуйте там свой вопрос.
Если вы хотите работать дальше, приведенный ниже контрольный список поможет вам определить возможные причины проблем в формулах.
Искомое значение и исходные данные относятся к разным типам. Например, вы пытаетесь использовать ссылку на функцию ВПР как число, а исходные данные сохранены как текст.
Решение:убедитесь, что типы данных одинаковы. Чтобы проверить формат ячеек, вы можете выбрать ячейку или диапазон ячеек, щелкнуть правой кнопкой мыши и выбрать формат ячеек >число (или нажмите CTRL+1) и при необходимости изменить числовом формате.
Совет: Если вам нужно принудительно изменить формат для целого столбца, сначала примените нужный формат, а затем выберите Данные > Текст по столбцам > Готово.
Начальные и конечные пробелы можно удалить с помощью функции СЖПРОБЕЛЫ. В приведенном ниже примере в функции ВПР используется вложенная функция СЖПРОБЕЛЫ для удаления начальных пробелов из имен в ячейках A2:A7 и возврата названия отдела.
Примечание: 24 сентября 2018 г. - Формулы динамического массива - Если у вас есть текущая версия Microsoft 365 и вы находитесь на канале быстрого выпуска Insiders, вы можете ввести формулу в верхнюю левую ячейку выходного диапазона и нажать клавишу Enter, чтобы подтвердите формулу в виде формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
Если вы используете функцию ПОИСКПОЗ, попробуйте изменить значение аргумента тип_сопоставления, чтобы указать порядок сортировки таблицы. Чтобы найти точное совпадение, задайте для аргумента тип_сопоставления значение 0 (ноль).
Формула массива ссылается на диапазон, не соответствующий по количеству строк или столбцов диапазону, содержащему формулу массива.
В данном примере ячейка E2 содержит ссылку на несовпадающие диапазоны:
Чтобы формула вычислялась правильно, необходимо изменить ее так, чтобы оба диапазона включали строки 2–11.
Примечание: 24 сентября 2018 г. - Формулы динамического массива - Если у вас есть текущая версия Microsoft 365 и вы находитесь на канале быстрого выпуска Insiders, вы можете ввести формулу в верхнюю левую ячейку выходного диапазона и нажать клавишу Enter, чтобы подтвердите формулу в виде формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
В формуле, использующей стандартную или пользовательскую функцию, отсутствует один или несколько обязательных аргументов.
Если вы не знаете точно, какие аргументы использовать, вам поможет мастер функций. Вы выберите ячейку с формулой, оставляемой под вопросом, а затем перейдите на вкладку Формула на ленте и нажмите кнопку Вставить функцию.
Excel автоматически запустит мастер.
Щелкните любой аргумент, и Excel покажет вам сведения о нем.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Суммируйте все числовые ячейки, игнорируя ошибки в Excel
При суммировании диапазона чисел, содержащих некоторые значения ошибок, обычная функция СУММ не будет работать правильно. Чтобы суммировать только числа и пропустить значения ошибок, функция СЛОЖЕНИЕ или СУММ вместе с функциями ЕСЛИОШИБКА могут оказать вам услугу.
- Суммировать числа, игнорируя различные ошибки, с помощью функции АГРЕГАТ
- Суммировать числа без учета различных ошибок с помощью функций СУММ и ЕСЛИОШИБКА
- Суммировать числа без учета конкретной ошибки с функцией СУММЕСЛИ
Суммировать числа, игнорируя различные ошибки, с помощью функции АГРЕГАТ
В Excel функция АГРЕГАТ может помочь суммировать диапазон ячеек и игнорировать все виды ошибок, общий синтаксис:
- 9 : Это аргумент - номер функции в АГРЕГАТЕ, он указывает на функцию суммы;
- 6 : Это еще один аргумент - параметры в АГРЕГАТЕ, он используется для игнорирования значений ошибок;
- range : Диапазон суммируемых ячеек.
Примените приведенную ниже формулу в пустую ячейку и нажмите Enter ключ для суммирования:
Суммировать числа без учета различных ошибок с помощью функций СУММ и ЕСЛИОШИБКА
Вы также можете объединить функции СУММ и ЕСЛИОШИБКА вместе, чтобы создать формулу массива для решения этой задачи, общий синтаксис:
- range : Диапазон ячеек, которые вы хотите суммировать, без учета ошибок.
Введите следующую формулу в пустую ячейку и нажмите Shift + Ctrl + Enter ключи, чтобы получить правильный результат, см. снимок экрана:
Пояснение к формуле:
= СУММ (ЕСЛИОШИБКА (C2: C10,0))
- ЕСЛИ ОШИБКА (C2: C10,0) : Эта функция ЕСЛИОШИБКА вернет 0 при обнаружении значений ошибки. Итак, вы получите следующий список массивов: .
- SUM(IFERROR(C2:C10,0))=SUM() : Функция СУММ суммирует все числа в списке массивов, чтобы получить окончательный результат: 8300.
Суммировать числа без учета конкретной ошибки с функцией СУММЕСЛИ
В пустой ячейке введите приведенную ниже формулу и нажмите Enter ключ для получения нужного вам результата:
Внимание: При применении этой формулы, если в списке данных возникают другие типы ошибок, эта функция СУММЕСЛИ возвращает результат ошибки.
Функция АГРЕГАТ в Excel предназначена для выполнения различных вычислений с использованием встроенных функций (определяются на основе одного из параметров) и возвращает агрегатный результат. Данная функция объединяет наиболее востребованные функции Excel, предназначенных для работы с массивами данных. Синтаксис рассматриваемой функции предусматривает возможность пропуска элементов массива со значениями ошибок или скрытых строк.
Примеры использования функции АГРЕГАТ в Excel
Пример 1. В классе провели контрольную по математике. Необходимо определить среднюю оценку для учеников, которые присутствовали на контрольной. Напротив некоторых фамилий оценка не указана по причине неn допуска к написанию или отсутствия.
Вид таблицы с данными:
Для расчета используем следующую формулу:
- 1 – число, соответствующее функции СРЗНАЧ;
- 3 – число, указывающее на способ расчета (не учитывать скрытые строки и коды ошибок);
- B3:B13 – диапазон ячеек с данными для определения среднего значения.
Как пропустить ошибки в ячейках при суммировании в Excel
Вид таблицы с данными:
Для решения удобно использовать функцию АГРЕГАТ, поскольку она позволяет исключить коды ошибок из расчетов.
Определим показатели первого счетчика по формуле:
- 9 – указатель на функцию СУММ;
- 2 – указатель на способ расчета (игнорирование значений ошибок);
- B3:B13 – ссылка на диапазон со значениями кВт.
Произведем аналогичный расчет для второго счетчика, передав в качестве ссылки ячейки C3:C13. В результате получим следующие значения:
Например, для расчета этих значений с использованием обычной функции СУММ потребуется более сложная и громоздкая запись (формула массива – для выполнения нужно нажать ctrl+shift+enter):
Описание синтаксиса функции АГРЕГАТ в Excel
Рассматриваемая функция имеет две формы синтаксической записи:
=АГРЕГАТ( номер_функции;параметры;ссылка1; [ссылка2];. )
=АГРЕГАТ( номер_функции;параметры;массив; [k])
- номер_функции – обязательный для заполнения, принимает числовые значения от 1 до 19, где каждое значение соответствует определенной функции (например, 1 – СРЗНАЧ, 2 – СЧЁТ и т. д.) Полный перечень номеров и соответствующих им функций указан в справочной информации к функции АГРЕГАТ.
- параметры – обязательный для заполнения, принимает числовые значения из диапазона от 0 до 7, где:
- значения от 0 до 3 – пропуск вложенных функций АГРЕГАТ, промежуточные итоги (для всех случаев), а также скрытых строк (1), значений ошибок (2), скрытых строк и значений ошибок (3);
- число 4 – все значения учитываются;
- 5-7 – пропуск только скрытых строк, значений ошибок и скрытых строк и значений ошибок соответственно.
- ссылка1 – обязательный для заполнения, принимает ссылку на диапазон ячеек с данными, над которыми требуется выполнить вычисления.
- [ссылка2] – необязательный аргумент, принимает ссылку на диапазон ячеек с данными, требуемыми для вычислений некоторыми функциями (например, НАИБОЛЬШИЙ, КВАРТИЛЬ.ВКЛ и прочие).
- массив – обязательный аргумент функции АГРЕГАТ формы массива, принимающий диапазон вычисляемых данных.
- [k] – не обязательный для заполнения аргумент, принимающий числовое значение, указывающее на позицию в массиве для некоторых функций (например, НАИМЕНЬШЕЕ, НАИБОЛЬШЕЕ, ПРОЦЕНТИЛЬ.ВКЛ и прочие).
Примечание: Если в качестве массива или ссылки на диапазон было передано выражение с промежуточными вычислениями (например, «B1:B10*(B1:B10<5)»), вложенные итоги, агрегаты или скрытые строки игнорироваться не будут.
При ошибочных вычислениях, формулы отображают несколько типов ошибок вместо значений. Рассмотрим их на практических примерах в процессе работы формул, которые дали ошибочные результаты вычислений.
Ошибки в формуле Excel отображаемые в ячейках
В других арифметических вычислениях (умножение, суммирование, вычитание) пустая ячейка также является нулевым значением.
Несколько практических примеров:
В ячейке А1 – слишком большое число (10^1000). Excel не может работать с такими большими числами.
В ячейке А3 – квадратный корень не может быть с отрицательного числа, а программа отобразила данный результат этой же ошибкой.
Как убрать НД в Excel
В данном случаи пересечением диапазонов является ячейка C3 и функция отображает ее значение.
В данном примере ошибка возникал при неправильном копировании формулы. У нас есть 3 диапазона ячеек: A1:A3, B1:B4, C1:C2.
Под первым диапазоном в ячейку A4 вводим суммирующую формулу: =СУММ(A1:A3). А дальше копируем эту же формулу под второй диапазон, в ячейку B5. Формула, как и прежде, суммирует только 3 ячейки B2:B4, минуя значение первой B1.
Примечание. В данном случае наиболее удобнее под каждым диапазоном перед началом ввода нажать комбинацию горячих клавиш ALT+=. Тогда вставиться функция суммирования и автоматически определит количество суммирующих ячеек.
Решетки в ячейке Excel
Как суммировать диапазон ячеек без учета ошибок в Excel?
Иногда на вашем листе могут быть значения ошибок, но теперь вы хотите суммировать выбранный диапазон ячеек, игнорируя значения ошибок. Если вы примените функцию Sum для диапазона напрямую, вы получите неверный результат. Здесь я познакомлю вас с некоторыми простыми и эффективными методами.
Kutools for Excel’s Error Condition Wizard can help you quickly find the error values and replace them with blanks or any text or numbers you need.
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
Суммарный диапазон ячеек без учета ошибок с помощью формулы массива
Предположим, у вас есть диапазон значений ячеек, как показано на следующем снимке экрана, вы можете применить эту формулу массива для суммирования диапазона, игнорируя ошибки. Пожалуйста, сделайте так:
1. В пустой ячейке введите эту формулу = СУММ (ЕСЛИ (ЕСТЬ ОШИБКА (A1: C6); ""; A1: C6)) , см. снимок экрана:
Внимание: в приведенной выше формуле, A1: C6 - это диапазон, который вы хотите суммировать, вы можете изменить его по своему усмотрению.
2, Затем нажмите Shift + Ctrl + Enter одновременно, чтобы закончить. И вы получите следующий результат:
Суммарный диапазон ячеек, игнорирующих ошибки с кодом VBA
Следующий код VBA также может помочь вам быстро получить сумму значений ячеек с ошибками.
1. Удерживайте ALT + F11 ключи, и он открывает Окно Microsoft Visual Basic для приложений.
2. Нажмите Вставить > Модулии вставьте следующий код в Окно модуля.
Код VBA: сумма ячеек с игнорированием ошибок
3. Затем нажмите F5 нажмите клавишу для запуска кода, и появится диалоговое окно, напоминающее вам о выборе диапазона, который вы хотите суммировать.
4. Затем нажмите OK, вы получите окно подсказки, чтобы показать результат следующим образом.
Sum / Average / Count range of cells ignoring errors with Kutools for Excel
If you want to get the calculations, such as sum, count, average of a range cells excluding the error values, Kutools for Excel may help you, you can apply the Select cells with Error Value and Select Range Helper utilities to deal with this job.
After installing Kutools for Excel, please do as follows:
1. Select the data range that you want to calculate without the error values.
2. Click Kutools > Select > Select Cells with Error Value, see screenshot:
3. And then all the error values have been selected at once, and go on clicking Kutools > Select > Select Range Helper, see screenshot:
4. In the Select Range Helper dialog box, select Inverse Selection option, and then drag the cursor select the range that you want to select only the numbers, when you release the mouse, the selected error values in former step have been deselected, and only the number cells have been selected, see screenshot:
5. And now, the calculations of the cells ignoring the errors, such as sum, average, count have been displayed at the status bar, see screenshot:
Demo: Sum / Average / Count range of cells ignoring errors with Kutools for Excel
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
Читайте также: