Как суммировать только видимые ячейки в Excel

Обновлено: 05.07.2024

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

Прежде всего, нам нужно найти правильную функцию, которая может суммировать только видимые ячейки в отфильтрованном списке и игнорировать отфильтрованные данные. На самом деле, встроенная в excel функция ПРОМЕЖУТОЧНЫЕ ИТОГОВ может выполнять вычисления, такие как сумма данных, количество, среднее значение как для видимых, так и для невидимых ячеек или только для видимых ячеек. Поскольку мы только хотим показать вам формулу для выполнения суммирования видимых ячеек в списке фильтров, поэтому в этой статье мы познакомим вас только с основным использованием функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ для суммированных данных. После прочтения этой статьи я думаю, что вы должны хорошо понимать функцию ПРОМЕЖУТОЧНЫЕ ИТОГО, чтобы вы могли использовать ее в своей повседневной работе.

ПРИМЕР:

Если мы хотим суммировать общую сумму отфильтрованных фруктов, можем ли мы использовать здесь функцию СУММ напрямую?

Введите =СУММ(B2:B10) в ячейке B12. Смотрите скриншот ниже, здесь мы получаем 64, возвращаемое значение явно неверно. Он равен сумме всего списка B2:B10, скрытые строки также учитываются.

ФОРМУЛА:

Давайте применим в этом случае функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ, чтобы снова попытаться рассчитать общую сумму для «Apple».

Шаг 1. Введите =ПРОМЕЖУТОЧНЫЙ ИТОГ(9;B2:B10).

Шаг 2. Нажмите Enter, чтобы загрузить возвращаемое значение.

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

КАК РАБОТАЕТ ЭТА ФОРМУЛА:

Эта формула содержит только одну функцию: функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

Синтаксис:

=ПРОМЕЖУТОЧНЫЙ ИТОГ (номер_функции, ссылка1, [ссылка2], …)

У него есть несколько аргументов, таких как номер_функции, ссылка1, ссылка2. Для function_num это число, оно показывает функцию, используемую ПРОМЕЖУТОЧНЫЙ ИТОГ, предоставляя разные числа, функция ПРОМЕЖУТОЧНЫЙ ИТОГ может выполнять разные вычисления. Как мы упоминали выше, функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ может выполнять большинство основных вычислений, таких как СУММ, СЧЕТ, СРЕДНЕЕ и т. д., поэтому нам нужно знать некоторые полезные номера функций, прежде чем объяснять функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

Пожалуйста, посмотрите на скриншоты ниже, они показывают соответствие между номером и функцией.

Номер функции от 1 до 101.

Номер функции от 101 до 111.

См. приведенный выше список номеров функций, мы видим, что функция СУММ имеет два номера функций: 9-СУММ и 109-СУММ. На самом деле, если мы введем 9, функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ будет суммировать только видимые данные и игнорировать отфильтрованные данные; и если мы введем 109, функция ПРОМЕЖУТОЧНЫЕИТОГИ будет суммировать только видимые данные и игнорировать данные, скрытые вручную. Видите, разница в следующем:

9: игнорировать данные, скрытые списком фильтров;

109: игнорировать данные, скрытые вручную.

В данном случае в формуле =ПРОМЕЖУТОЧНЫЙ ИТОГ(9;B2:B10). Есть два аргумента. Для первого аргумента номер_функции, поскольку данные скрыты списком фильтров, мы вводим здесь 9; для второго аргумента, ссылки, мы можем ввести весь список B2:B10, или вы также можете удерживать мышь и выбирать видимые ячейки напрямую.

РЕЗУЛЬТАТ:

Мы уже получили нужную сумму для «Apple», на этот раз давайте выберем Banana, чтобы проверить, правильно ли работает наша формула после изменения критериев фильтра.

Он работает правильно. Функция ПРОМЕЖУТОЧНЫЕ ИТОГО может хорошо работать с «суммой видимых ячеек».

Если мы изменим номер_функции с 9 на другие, функция ПРОМЕЖУТОЧНЫЙ ИТОГ соответствующим образом изменит расчет на основе номера функции.Например, в данном случае измените 9 на 2 (2 — COUNT).

Мы получим 2, применив формулу.

Связанные функции

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

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

Если вы хотите проверить значения, чтобы увидеть, начинаются ли они с определенных символов, таких как «x», «y» или «z», вы можете создать формулу с функциями COUNTIF и SUM для возврата результатов. ПРИМЕР Вы можете увидеть «ИСТИНА» или .

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

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

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

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

Иногда нам может понадобиться просуммировать N самых больших чисел или первых N чисел в диапазоне. В этой статье мы покажем вам метод «СУММ самых больших N чисел» с помощью простой формулы, состоящей из СУММПРОИЗВ.

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

Мы можем суммировать только ВИДИМЫЕ строки (СТРОКИ НЕ ОТФИЛЬТРИРОВАНЫ) в отфильтрованном списке с ФУНКЦИЕЙ ПРОМЕЖУТОЧНЫХ ИТОГОВ с номером функции 9 или 109 . Эта функция автоматически игнорирует скрытые строки в отфильтрованном списке или таблице. Шаги, описанные ниже, помогут пройти весь процесс.

Рисунок 1. Как суммировать видимые строки в отфильтрованном списке

Общая формула

=ПРОМЕЖУТОЧНЫЙ ИТОГ(9,ДИАПАЗОН)

Формула

=ПРОМЕЖУТОЧНЫЙ ИТОГ(9;C4:C13)

Настройка данных

Мы настроим данные, введя товары, проданные из ячейки B4, в ячейку B13.
Сумма продаж указана в ячейках C4 – C13.
Наш результат будет возвращен функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ в ячейке C14.

Рисунок 2. Настройка данных

Создание отфильтрованного списка

Предполагая, что мы хотим удалить продажи на сумму менее 500 долларов США, мы можем создать ОТФИЛЬТРОВАННЫЙ СПИСОК следующим образом:

Нажмите на ячейку C3, щелкните правой кнопкой мыши и выберите фильтр. Мы нажмем «фильтровать по значению ячейки». Результат показан на рис. 3.

Рисунок 3. Создание отфильтрованного списка

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

< бр />

Рисунок 4. Отфильтрованный список

Мы заметим, что ячейка C6 с $280 была автоматически скрыта

Использование функции ПРОМЕЖУТОЧНЫЙ ИТОГ

Мы нажмем на ячейку C14.
Мы введем приведенную ниже формулу
=ПРОМЕЖУТОЧНЫЙ ИТОГ(9,C4:C13)
Мы нажмем клавишу ввода.

Рисунок 5: Результат функции ПРОМЕЖУТОЧНЫЕ ИТОГО

Примечание

Если вы хотите ВРУЧНУЮ СКРЫТЬ СТРОКИ (щелчок правой кнопкой мыши, СКРЫТЬ), используйте приведенную ниже формулу:
=ПРОМЕЖУТОЧНЫЙ ИТОГ(109,C4:C13)
Изменив номер функции, функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ может выполнять множество других вычислений.

Мгновенное подключение к эксперту через нашу службу Excelchat

В большинстве случаев проблема, которую вам нужно решить, будет более сложной, чем простое применение формулы или функции. Если вы хотите сэкономить часы исследований и разочарований, попробуйте наш онлайн-сервис Excelchat! Наши эксперты по Excel доступны круглосуточно и без выходных, чтобы ответить на любой вопрос, который у вас может возникнуть. Мы гарантируем подключение в течение 30 секунд и индивидуальное решение в течение 20 минут.

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

Синтаксис функций Excel

Аргументы

номер1	Обязательно. Константа, ссылка на ячейку или массив, содержащий числовые значения, которые вы хотите добавить.
[number2]	Необязательно. До 254 дополнительных аргументов.

Функция СУММ позволяет добавлять значения в диапазоны ячеек, массивы и константы. У вас может быть до 255 различных аргументов.

Содержание

1. Как сложить числа в столбец (функция СУММ)?

< бр />

Функция СУММ позволяет добавлять значения в диапазон ячеек, например = СУММ(B3:B7), вместо добавления значений в формулу с помощью знака плюс, например =B3+B4+B5+B6+B7.

Функция СУММ позволяет ввести один или несколько диапазонов ячеек, в этом примере в качестве аргумента вводится только диапазон ячеек B3:B7. См. рисунок выше.

2. Как сложить числа в массив (функция СУММ)?

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

Выберите ячейку и введите =СУММ(B3:B9)

< бр />

Нажмите левую кнопку мыши в строке формул и выберите B3:B9.

Нажмите F9, и диапазон ячеек будет преобразован в массив, например: =СУММ()

Функция СУММ складывает значения в массиве 5+3+6+4+2 = 20. Когда вы конвертируете диапазон ячеек в значения, которые вы жестко кодируете или создаете константы в своей формуле, это означает, что они никогда не изменятся, пока вы не измените значения в формуле.

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

Я рекомендую прочитать этот пост: Изучите основы работы с массивами Excel , если вы хотите узнать больше о формулах массивов.

3. Как суммировать отдельные ячейки?

Функция СУММ позволяет добавлять значения из выбранных ячеек. Хитрость заключается в том, чтобы нажать и удерживать клавишу CTRL при выборе определенных ячеек для суммирования. Вот шаги более подробно:

Дважды нажмите левой кнопкой мыши на ячейку, появится подсказка.
Введите =СУММ(
Нажмите и удерживайте клавишу CTRL.
Удерживая левую кнопку мыши, нажмите и удерживайте левую кнопку мыши на ячейках, которые хотите суммировать.
Отпустите клавишу CTRL.
Добавить закрывающую скобку)
Нажмите Enter.

4. Как суммировать числа в нескольких диапазонах ячеек?

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

Дважды нажмите левой кнопкой мыши на ячейку, появится подсказка.
Введите =СУММ(
Нажмите и удерживайте клавишу CTRL.
Нажмите и удерживайте левую кнопку мыши.
Перетащите мышью, чтобы выбрать диапазон ячеек.
Отпустите левую кнопку мыши.
Возвращайтесь к шагу 4, пока не будут выбраны все диапазоны ячеек.
Отпустите клавишу CTRL.
Добавить закрывающую скобку)
Нажмите Enter.

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

Формула в ячейке B8 суммирует значения в диапазоне ячеек B3:B7. 5 + AA + 6 + 4 +2 = 17. Функция СУММ игнорирует текстовые строки, в данном случае AA.

Функция СУММ игнорирует текстовые и логические значения, но не значения ошибок.

Обратите внимание, что функция СУММ игнорирует числа, сохраненные в виде текста. На изображении ниже показана функция СУММ в ячейке B8. Только число 4 включено в общее количество ячеек в диапазоне ячеек B3:B7.

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

6. Как суммировать логические значения?

Диапазон ячеек B3:B7 содержит логические значения, ИСТИНА или ЛОЖЬ, однако функция СУММ не может добавлять логические значения, пока они не будут преобразованы в числовые эквиваленты.

Есть несколько решений этой проблемы, вот некоторые из них:

Формула в ячейке B8:

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

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

Инструкции по вводу формулы массива.

Дважды нажмите левой кнопкой мыши на ячейке B8.
Введите =СУММ(B3:B7*1)
Одновременно нажмите и удерживайте клавиши CTRL + SHIFT.
Нажмите Enter один раз.
Отпустить все клавиши.

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

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

Формула возвращает 2, поскольку ИСТИНА равна 1, а ЛОЖЬ равна 0. 1+0+1+0+0 = 2.

Обратите внимание: вы можете использовать функцию СУММПРОИЗВ, если не хотите использовать формулы массива.

Обычная формула в ячейке B8:

7. Как создать промежуточный итог?

На изображении выше показаны числа в столбце B.

Введите эту формулу в ячейку C3:

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

Выберите ячейку C4 и посмотрите, как изменилась формула в строке формул. Часть ссылки на ячейку без знаков доллара изменена с B3 на B4.

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

Формула в ячейке C4 складывает числа из ячеек B3 и B4. Формула еще больше растет в ячейке C5 и продолжает расти в ячейках ниже.

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

8. Как суммировать с условием [формула массива]

< бр />

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

Формула в ячейке F3 позволяет складывать числа в столбце C, если их соседнее значение равно значению в ячейке F2:

Эта формула вводится как формула массива, если вы не используете Excel 365. Я рекомендую функцию СУММЕСЛИ, созданную именно для этого, без ввода формулы как формулы массива.

Пояснение формулы в ячейке F3

Шаг 1. Логическое выражение

Знак равенства в B3:B10=F2 позволяет сравнивать значения в диапазоне ячеек B3:B10 со значением в ячейке F2. Знак равенства — это логический оператор, часто используемый в функциях ЕСЛИ.

Этот логический тест возвращает массив логических значений:

Шаг 2. Умножение массива на диапазон ячеек

Скобки (B3:B10=F2) гарантируют, что эта часть формулы вычисляется первой перед умножением на числа в диапазоне ячеек C3:C10.

FALSE равно 0 (нулю), а TRUE равно 1.

Шаг 3. Суммирование чисел

Затем функция СУММ добавляет число в массив:

и возвращает 19 в ячейке F3. 1+10+8 = 19

9. Сумма с несколькими условиями [формула массива]

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

Эта формула вводится как формула массива, если вы не используете Excel 365. Я рекомендую функцию СУММЕСЛИМН, созданную именно для этого без необходимости в формуле массива.

Объяснение формулы в ячейке G4

Шаг 1. Первое условие

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

Шаг 2. Второе условие

Шаг 3. Умножение массивов

Шаг 4. Суммируйте значения в массиве

Функция СУММПРОИЗВ позволяет выполнять те же вычисления без необходимости ввода формулы в виде формулы массива.

10. Как суммировать только видимые ячейки?

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ позволяет суммировать значения в диапазоне ячеек, в котором некоторые строки скрыты или отфильтрованы. На рисунке выше показан диапазон ячеек, в котором скрыты строки 4 и 9. Обычная функция СУММ в этом случае не сработает, вам понадобится функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ:

Первый аргумент позволяет выбрать номер функции, определяющий поведение функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ. В этом случае 109 суммирует все видимые ячейки в диапазоне ячеек.

Чтобы скрыть значение, просто нажмите правой кнопкой мыши на номер строки, а затем нажмите левой кнопкой мыши на "Скрыть", чтобы скрыть всю строку. Выберите строки вокруг скрытой строки, а затем нажмите на них правой кнопкой мыши, чтобы открыть меню, затем нажмите левой кнопкой мыши на «Показать», чтобы снова отобразить значение.

На рисунке выше показаны отфильтрованные значения в столбце C. Excel сообщает вам, что диапазон ячеек отфильтрован по цвету номеров строк и значку рядом с «Числами» в ячейке C2.

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

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

В ячейке C13 на рисунке выше отображается общее количество отфильтрованных ячеек. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ работает так же хорошо, если вы предпочитаете использовать функцию Excel с таблицей, определенной в Excel, как показано в ячейке C15.

Как скрыть/отобразить значения?

Обратите внимание: следуйте этим инструкциям, чтобы скрыть и показать определенные строки:

Нажмите правую кнопку мыши на номере строки.
Появится всплывающее меню, см. изображение выше.
Нажмите мышью, чтобы скрыть или показать.

Совет! Нажмите и удерживайте клавишу CTRL при выборе строк, чтобы скрыть/отобразить несколько значений одновременно.

11. Как суммировать отфильтрованный столбец?

На изображении выше показаны числа в диапазоне ячеек C3:C7, однако применен фильтр, и строки 4 и 6 скрыты. Функция СУММ в ячейке C9 не может игнорировать отфильтрованные значения, вам нужна функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ для суммирования отфильтрованных чисел.

Формула в ячейке C10:

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

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

Формула в ячейке E2:

Ссылка на ячейку — B:B, что означает, что все числовые значения в столбце B включены в итог.

13. Как суммировать строку?

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

Формула в ячейке C4:

Ссылка на ячейку — 2:2, что означает, что все числовые значения в строке 2 включены в итог.

14. Как суммировать по листам?

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

Формула в ячейке C3:

Вот шаги, которые я сделал, чтобы создать приведенную выше формулу:

Дважды нажмите левой кнопкой мыши на ячейку C3, появится подсказка.
Введите =СУММ(
Перейти к первому рабочему листу.
Нажмите мышью на ячейку, содержащую значение, которое вы хотите добавить.
Нажмите и удерживайте клавишу SHIFT.
Выберите последний рабочий лист, который вы хотите включить в итог.
Отпустите клавишу SHIFT.
Введите закрывающую скобку )
Нажмите Enter.

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

15. Как суммировать по цвету?

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

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

Это то, что показано на изображении выше. Я решил выделить строки синим цветом, если соответствующая ячейка в столбце B равна элементу «B». Вот как я это сделал:

Выделите ячейки, которые нужно выделить, в приведенном выше примере диапазон ячеек B3:C10.
Перейдите на вкладку "Главная" на ленте.
Нажмите левой кнопкой мыши кнопку условного форматирования.
Нажмите левой кнопкой мыши на "Новое правило". Появится диалоговое окно.
Нажмите мышью на "Использовать формулу для определения форматируемых ячеек".
Нажмите мышью на поле под надписью "Форматировать значения, где эта формула верна:".
Введите =$B3=$F$2
Нажмите левой кнопкой мыши на кнопку «Формат». Появится новое диалоговое окно.
Нажмите левой кнопкой мыши на вкладку "Заполнить" в верхнем меню.
Выберите цвет.
Нажмите левой кнопкой мыши на OK. Диалоговое окно закрывается.
Нажмите левой кнопкой мыши на OK еще раз.

16. Как суммировать абсолютные числа?

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

Формула в ячейке C8:

Пояснение формулы в ячейке C8

Шаг 1. Удаление знака

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

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

Загрузить практическую рабочую тетрадь

Скачайте эту книгу и попрактикуйтесь.

4 способа суммирования только видимых ячеек в Excel

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

1. Суммировать только видимые ячейки с таблицей в Excel

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

ШАГИ:

Во-вторых, перейдите на ленту ВСТАВИТЬ и выберите Таблица.

ПРИМЕЧАНИЕ. Это преобразует набор данных в таблицу. Вы также можете сделать это с помощью клавиатуры. Просто нажмите Ctrl + T, и набор данных будет преобразован в таблицу.

В-третьих, перейдите на ленту ДИЗАЙН и выберите Строка итогов.

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

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

Здесь мы скрыли 7-ю, 9-ю и 10-ю строки, а сумма для видимых ячеек появилась в последней строке.

2. Автофильтр для суммирования только видимых ячеек в Excel

Мы используем функцию фильтра Excel для суммирования только видимых ячеек. Здесь мы можем использовать функцию ПРОМЕЖУТОЧНЫЙ ИТОГ и функцию АГРЕГАТ в этом методе. Мы также покажем здесь использование AutoSum.

Мы собираемся снова использовать предыдущий набор данных.

2.1 Использование функции ПРОМЕЖУТОЧНЫЙ ИТОГ

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

ШАГИ:

Сначала выберите диапазон ячеек в наборе данных.

Теперь выберите ячейку E13 и введите формулу.

Теперь нажмите Enter, чтобы увидеть результат.

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

2.2 Использование функции АГРЕГАТ

Это очень похоже на метод функции ПРОМЕЖУТОЧНЫЕ ИТОГО. Мы используем функцию АГРЕГАТ вместо ПРОМЕЖУТОЧНЫХ ИТОГОВ.

ШАГИ:

Сначала выберите ячейку E13.
Теперь введите формулу:

Теперь нажмите Enter и посмотрите результат.

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

2.3 Использование автосуммы

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

ШАГИ:

Во-вторых, перейдите на ленту ФОРМУЛЫ и выберите Автосумма.

Наконец, столбец "Зарплата" суммируется и отображается в ячейке.

3. Найти сумму только для видимых ячеек с пользовательской функцией

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

Здесь мы будем использовать следующие шаги.

ШАГИ:

Сначала перейдите на ленту РАЗРАБОТЧИК и выберите Visual Basics.

Во-вторых, появится окно Microsoft Visual Basic. Выберите метод и введите код там.

Здесь наша функция ONLYVISIBLE.

В-третьих, выберите ячейку E13 и введите формулу.

Теперь нажмите Enter и посмотрите результат.

Наконец, если мы скроем 7-ю, 9-ю и 10-ю строку, будет показан желаемый результат.

4. Функция Excel SUMIF для добавления видимых ячеек

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

Здесь мы добавим два новых столбца в предыдущий набор данных для выполнения этой операции. Для этого метода мы будем использовать столбец «Да/Нет» и вспомогательный столбец. Здесь мы воспользуемся помощью функции АГРЕГАТ.

ШАГИ:

Сначала выберите ячейку F5 и введите формулу.

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

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

Затем поместите формулу в ячейку E13.

Он будет искать критерии в диапазоне от D5 до D12 и суммировать, если условие удовлетворяет.

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

ПРИМЕЧАНИЕ. Мы можем использовать этот метод, только если нам нужно применить определенные условия.

Заключение

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

Читайте также: