Как исключить определённые ячейки в столбце из суммы в Excel?
При работе с данными в Excel вам часто нужно вычислить сумму диапазона, намеренно исключая определённые ячейки — например, содержащие выбросы, ошибки, нерелевантную информацию или конкретные значения, которые не должны учитываться в итоговом расчёте. Как показано на скриншоте ниже, представьте, что у вас есть столбец чисел, но вы хотите просуммировать все значения, кроме тех, что находятся в ячейках A3 и A4. В этой статье подробно описаны различные практические методы суммирования диапазона в Excel с исключением одной или нескольких заданных ячеек — чтобы вы всегда получали именно те результаты, которые требуются для финансового анализа, отчётности или управления запасами, где важны точечные вычисления.
- Исключение ячеек в столбце из суммы с помощью формулы
- Код VBA – программное суммирование диапазона с пропуском/исключением указанных ячеек
- Формула Excel – используйте СУММЕСЛИ/СУММЕСЛИМН, чтобы включать только значения, не соответствующие критериям исключения
- Формула Excel – используйте функцию ФИЛЬТР в новых версиях Excel для фильтрации исключаемых ячеек перед суммированием
Исключение ячеек в столбце из суммы с помощью формулы
Используя простую арифметику внутри формулы СУММ, вы можете напрямую исключить нежелательные ячейки из расчета. Этот подход подходит для быстрых вычислений, когда нужно обработать небольшое количество исключений. Выполните следующие шаги:
1. Выберите пустую ячейку для отображения результата суммирования, введите следующую формулу в строку формул и нажмите Enter, чтобы вычислить сумму, исключив конкретные ячейки. Например:
=SUM(A2:A7)–SUM(A3:A4)
Пояснение и советы:
- Функция SUM(A2:A7) вычисляет сумму всего диапазона, тогда как SUM(A3:A4) вычитает значения исключаемых ячеек. Этот метод лучше всего подходит, когда исключаемые ячейки идут подряд.
- Если исключаемые ячейки несмежные, вы можете легко комбинировать и вычитать несколько ячеек. Например, чтобы исключить A3 и A6 из диапазона, скорректируйте формулу следующим образом:
=SUM(A2:A7)–A3–A6
- Если исключаемые ячейки разбросаны по таблице или их много, ручной перечень каждой из них может привести к громоздким и трудноуправляемым формулам.
- Обращайте внимание на ссылки на ячейки: если ваши данные или диапазон изменятся, обязательно обновите формулу, чтобы избежать ошибок.
Код VBA – программное суммирование диапазона с пропуском/исключением указанных ячеек
Для сценариев с большим количеством исключений или при необходимости часто повторять процесс использование макроса VBA обеспечивает гибкость и автоматизацию. С помощью VBA вы можете суммировать Ограниченный диапазон и исключать любое количество ячеек — как смежных, так и несмежных — программно определяя их. Этот метод подходит пользователям, знакомым со средой VBA и стремящимся оптимизировать более сложную логику исключения.
Меры предосторожности: Макросы VBA могут изменять вашу книгу. Всегда сохраняйте файл перед запуском нового кода. Чтобы выполнить приведённый выше код, необходимо включить макросы.
1. Перейдите в Средства разработчика > Visual Basic, чтобы открыть редактор VBA. В окне проекта щелкните правой кнопкой мыши по своей книге, выберите Вставить > Модуль и вставьте следующий код в модуль:
Sub SumWithExclusions()
Dim sumRange As Range
Dim excludeCells As Range
Dim cell As Range
Dim result As Double
Dim xTitleId
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set sumRange = Application.InputBox("Select the range to sum", xTitleId, Type:=8)
Set excludeCells = Application.InputBox("Select cells to exclude (use Ctrl+Click to select multiple)", xTitleId, Type:=8)
result = 0
If Not sumRange Is Nothing Then
For Each cell In sumRange
If Not Application.Intersect(cell, excludeCells) Is Nothing Then
' Skip excluded cells
Else
result = result + cell.Value
End If
Next
MsgBox "The sum excluding specified cells is: " & result, vbInformation
Else
MsgBox "No range selected.", vbExclamation
End If
End Sub2. Нажмите 
«Выполнить» в окне VBA или клавишу F5, чтобы запустить макрос. После этого появится диалоговое окно с запросом на выбор полного диапазона для суммирования. Затем выберите ячейки, которые нужно исключить (удерживайте Ctrl, чтобы выбрать несколько). Результат макрос отобразит во всплывающем окне.
- Если вы случайно выбрали не те ячейки, просто запустите макрос ещё раз — процесс полностью интерактивный и гибкий.
- Макросы лучше всего подходят для регулярных задач или сложных исключений на основе критериев.
Формула Excel – используйте СУММЕСЛИ или СУММЕСЛИМН, чтобы включать только значения, не соответствующие критериям исключения
Для более сложных или логических исключений используйте функции СУММЕСЛИ или СУММЕСЛИМН. Эти формулы отлично справляются с задачами, где исключения зависят от определённых значений, условий или когда у вас есть готовый список элементов для исключения.
Пример – исключение по конкретному значению
1.Если вы хотите просуммировать A2:A7, но исключить значение «16», введите следующую формулу в целевую ячейку (например, в ячейку B1):
=SUMIF(A2:A7,"<>16")Эта формула суммирует все значения в диапазоне A2:A7, кроме тех, которые равны 16.
2. После ввода формулы нажмите Enter. При необходимости вы можете скопировать или скорректировать ссылки на диапазон/ячейки.
Пример – исключение всех ячеек, соответствующих значению в другой ячейке
Предположим, что ячейка C1 содержит значение, которое вы хотите исключить из суммы:
=SUMIF(A2:A7,"<>"&,A3)Обновляйте ячейку C1 по мере необходимости — и формула автоматически исключит все совпадающие значения.
- Для нескольких критериев исключения или более сложных правил рекомендуем использовать СУММЕСЛИМН в сочетании со вспомогательными столбцами или массивами. Однако функции СУММЕСЛИ и СУММЕСЛИМН демонстрируют наилучшие результаты, когда исключения основаны на чётких и согласованных критериях, а не на произвольных позициях ячеек.
- Если ваш диапазон содержит текст или пустые ячейки, СУММЕСЛИ автоматически игнорирует их; убедитесь, что это соответствует вашим намерениям.
Формула Excel – используйте функцию ФИЛЬТР (новые версии Excel) для фильтрации исключаемых ячеек перед суммированием
Если вы используете Excel для Microsoft 365 или Excel 2021 и новее, функция ФИЛЬТР позволяет динамично и гибко исключать ячейки перед применением СУММ. Это особенно полезно при работе с большими наборами данных или когда критерии исключения часто меняются.
Пример – исключение конкретных значений (например, 16 и 13)
1.Введите следующую формулу в целевую ячейку (например, B1):
=SUM(FILTER(A2:A7,(A2:A7<,>,16)*(A2:A7<,>,13)))Эта формула суммирует все значения в A2:A7, кроме тех, которые равны 16 и 13. Функция ФИЛЬТР создает массив, включающий только ячейки, не равные этим значениям, а затем СУММ складывает их.
2. Нажмите Enter. Расчёт будет автоматически обновляться при изменении исключений или исходных данных.
- Чтобы динамически исключать значения на основе списка (например, список исключений находится в C2:C4):
=SUM(FILTER(A2:A7,ISNA(MATCH(A2:A7,C2:C4,0))))Эта формула автоматически исключает из диапазона A2:A7 все значения, совпадающие со значениями в C2:C4. Просто обновите список исключений в столбце C — и результат формулы тут же обновится сам.
- Подход с использованием функции ФИЛЬТР рекомендуется пользователям, работающим с последними версиями Excel и стремящимся к динамичной и масштабируемой логике исключения.
- Если вы получаете ошибку #ВЫЧИСЛ!, проверьте, остается ли хотя бы одно значение в диапазоне после всех исключений; в противном случае ФИЛЬТР возвращает ошибку.
В заключение, Excel предлагает несколько практических решений для суммирования диапазона с исключением конкретных ячеек или значений. Простые формулы подходят для быстрых и небольших исключений, тогда как СУММЕСЛИ/СУММЕСЛИМН и ФИЛЬТР поддерживают более гибкие сценарии, основанные на условиях. VBA идеален, когда исключений много, они разнообразны или требуют автоматизации. Всегда дважды проверяйте ссылки на ячейки и корректировки формул при изменении ваших Исходные данные. Если возникают ошибки, проверьте диапазоны или списки исключений и попробуйте повторно применить формулы или перезапустить макрос.
См. также:
- Как исключить определённую ячейку или диапазон из печати в Excel?
- Как исключить значения из одного списка по сравнению с другим в Excel?
- Как найти Минимальное значение в диапазоне, исключая нулевые значения, в Excel?
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек