Excel не суммирует скрытые ячейки в Excel

Обновлено: 21.11.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 чисел» с помощью простой формулы, состоящей из СУММПРОИЗВ.

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

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

Возможно, нужно сгруппировать некоторые данные по определенному атрибуту. Допустим, вы хотите просмотреть данные о продажах в регионах B и E из набора данных, включающего данные из регионов от A до G. Или вам может понадобиться увидеть объемы продаж на сумму более 200 долларов США. Вы можете отфильтровать данные или скрыть некоторые строки или столбцы.В зависимости от макета вашего набора данных у вас может быть только один вариант, но в любом случае вы останетесь с отфильтрованными или видимыми ячейками.

Следующий шаг — получить общее количество этих видимых цифр.

Подведем итоги!

Оглавление

Проблемы с функцией SUM

Рассчитать итог? Легкий. Alt + H + U + S, и вы готовы с функцией СУММ, но здесь у нас возникают небольшие проблемы. Проблема с функцией СУММ заключается в том, что она включает ячейки, исключенные путем скрытия или фильтрации, что делает всю работу с сокрытием/фильтрацией довольно бесполезной. Давайте продемонстрируем.

В приведенной ниже таблице столбец H был отфильтрован, чтобы показать итоговые суммы после уплаты налогов, превышающие 200 долларов США. Если мы выберем отфильтрованные ячейки, строка состояния покажет нам сумму 903,56:

Однако, когда мы применяем функцию СУММ к отфильтрованным ячейкам, мы получаем в сумме 1842,72:

Что здесь произошло? Вместо того, чтобы брать только отфильтрованные ячейки, функция СУММ берет все ячейки между первой и последней отфильтрованными ячейками. Формула суммировала диапазон H6:H17, который составляет 12 значений вместо 4 отфильтрованных значений.

То же самое касается скрытых ячеек; поскольку функция СУММ принимает последовательный диапазон (если только не вводится вручную с отдельными ячейками), скрытые ячейки также будут включены функцией СУММ при подсчете суммы.

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

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

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

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

Выберите 109 из вариантов, чтобы ПРОМЕЖУТОЧНЫЙ ИТОГ суммировал значения отфильтрованных ячеек.

Для второго аргумента вы можете начать ссылаться на ячейки для суммирования. Мы выбрали наши видимые ячейки, которые автоматически становятся H6:H17, но благодаря функции ПРОМЕЖУТОЧНЫЕ ИТОГ будут добавлены только отображаемые ячейки.

У нас есть результат:

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

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

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

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

Давайте посмотрим, как работает функция АГРЕГАТ для возврата суммы видимых ячеек. Во-первых, формула, которую мы использовали:

Как и в случае с функцией ПРОМЕЖУТОЧНЫЕ ИТОГО, первым идет номер функции. Здесь у нас есть число 9 для суммирования.

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

Теперь о массиве. Мы ввели H6:H17.

Функция АГРЕГАТ проигнорировала скрытые строки для суммирования значений, видимых в диапазоне H6:H17:

Обратите внимание, что в параметрах формулы упоминаются только скрытые строки, а не столбцы. Почему? Функция АГРЕГАТ также не работает для скрытых столбцов. Время исправить это. Войдите в функцию VBA.

Использование пользовательской функции VBA

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

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

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

Нажмите клавиши Alt + F11, чтобы запустить VB. Или, если у вас добавлена вкладка Разработчик, вы можете перейти на вкладку Код > и нажмите кнопку Visual Basic.
Открыв редактор VB, щелкните вкладку Вставка и выберите из списка Модуль. Вы будете перенаправлены в окно Module.

В окне Модуль скопируйте и вставьте приведенный ниже код, чтобы добавить функцию:

Будет создана функция с именем SumVisible для вычисления суммы выбранных ячеек, видимых в наборе данных, без учета значений в скрытых строках и столбцах.

Закройте VB
В ячейке, где вы хотите получить итоговую сумму, введите следующую формулу:

Вам нужно только ввести имя созданной функции и диапазон. Функция суммирует значения в диапазоне и возвращает итог:

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

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

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

Подпишитесь и станьте частью нашей семьи, насчитывающей более 15 000 человек!

Теперь подпишитесь на Excel Trick и получите бесплатную копию нашей электронной книги "200+ ярлыков Excel" (формат для печати), которая резко повысит вашу продуктивность.

Функция СУММ в 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. Здесь мы рассмотрели четыре простых метода. Надеюсь, эти методы помогут вам найти решение вашей проблемы. Наконец, если у вас есть какие-либо вопросы или предложения, не стесняйтесь комментировать ниже.

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