Как фильтровать данные, исключая строку итогов снизу в Excel?
В повседневной обработке данных в Excel часто рассчитывают итоговую строку в нижней части набора данных для подведения ключевых показателей, таких как суммы, средние значения, подсчеты и другие агрегации. Однако, когда вы используете функцию фильтрации Excel, строка итогов часто подвергается той же фильтрации, что и остальные данные, то есть она может быть скрыта при применении фильтров. Во многих случаях — особенно при составлении сводных отчетов, финансовых отчетов или текущем анализе данных — вам может потребоваться, чтобы строка итогов всегда оставалась видимой внизу ваших данных, независимо от фильтров, применяемых к остальной части листа, как показано на скриншоте ниже.
В этой статье мы рассмотрим несколько практических методов, которые помогут убедиться, что строка итогов снизу всегда остается видимой при фильтрации. Каждый метод подходит для различных сценариев использования и предпочтений пользователей. В статье рассматриваются не только встроенные функции Excel, но и подходы с использованием формул, а также программные решения с помощью VBA, предоставляя широкий выбор вариантов в зависимости от ваших потребностей.
➤ Фильтрация данных без учета строки итогов снизу путем создания таблицы
➤ Фильтрация данных без учета строки итогов снизу через вставку формулы
➤ Фильтрация данных без учета строки итогов снизу с помощью кода VBA
Фильтрация данных без учета строки итогов снизу путем создания таблицы
Самый простой способ сохранить видимость строки итогов при фильтрации — преобразовать ваши данные в официальную таблицу Excel. Таблицы Excel имеют встроенную функцию Строка итогов, которая разработана таким образом, чтобы оставаться видимой внизу ваших данных даже при фильтрации других строк. Ниже приведены подробные шаги, типичные сценарии и примечания по этому методу:
1. Если ваши данные уже содержат вручную рассчитанную строку итогов (например, строку с формулами итогов, не интегрированными в таблицу), временно удалите эту строку, чтобы избежать дублирования.
2. Выберите диапазон ваших фактических данных (исключая созданную вручную строку итогов). Затем перейдите в меню Вставка > Таблица. В диалоговом окне Создание таблицы убедитесь, что отмечен пункт Моей таблице есть заголовки, если ваши данные включают заголовки столбцов. Проверьте диапазон данных, чтобы убедиться, что все строки включены.
3. После нажатия кнопки ОК ваши данные будут отформатированы в виде таблицы Excel. При выбранной таблице перейдите на вкладку Конструктор (или Конструктор таблиц в некоторых версиях). В группе Параметры стиля таблицы отметьте пункт Строка итогов. Excel автоматически добавит новую строку итогов внизу.
4. Теперь вы можете использовать стрелки фильтрации в любом столбце для фильтрации данных по мере необходимости. Строка итогов всегда будет отображаться как последняя строка в таблице, независимо от применяемых фильтров.
Применимые сценарии: Этот метод идеально подходит для наборов данных, которые часто обновляются, передаются между командами или используются в повторяющихся отчетах, особенно когда вы получаете выгоду от встроенных функций таблиц, таких как полосатые строки, структурированные ссылки и автоматическое форматирование.
Преимущества:
- Беспроблемная интеграция со встроенными функциями Excel
- Нет необходимости обновлять формулы при расширении или сокращении данных
- Строка итогов остается динамической и автоматически обновляемой
Ограничения:
- Пользовательские формулы в строке итогов необходимо переустановить после преобразования в таблицу
- Большие наборы данных могут испытывать незначительное замедление производительности из-за реального времени форматирования
- Убедитесь, что вы удалили любую созданную вручную строку итогов перед преобразованием в таблицу, иначе вы можете увидеть дублирующиеся итоги.
- Очистите фильтры перед добавлением или удалением данных, чтобы сохранить структуру таблицы согласованной.
Раскройте магию Excel с Kutools AI
- Умное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё это посредством простых команд.
- Пользовательские формулы: Создавайте индивидуальные формулы для оптимизации ваших рабочих процессов.
- Кодирование VBA: Пишите и внедряйте код VBA без особых усилий.
- Интерпретация формул: Легко разбирайтесь в сложных формулах.
- Перевод текста: Преодолейте языковые барьеры в ваших таблицах.
Фильтрация данных без учета строки итогов снизу через вставку формулы
Если вы не хотите использовать таблицу Excel, вы можете сохранить ручную строку итогов внизу ваших данных, применяя формулу ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ специально разработана для работы динамически с фильтрами Excel — ее расчет будет учитывать только видимые (включенные в фильтр) строки, но сама формула не будет скрыта или исключена при фильтрации. Этот подход является гибким и хорошо работает с классическими диапазонами данных.
1. В первой ячейке вашей намеченной строки итогов, прямо под таблицей данных, введите следующую формулу (например, если ваши значения находятся в столбце B с 2 по 13 строки):
=SUBTOTAL(9, B2:B13)Замените B2:B13 на диапазон, соответствующий фактическому столбцу и строкам ваших данных. Число 9 в формуле указывает функцию СУММ; вы можете использовать различные номера функций по мере необходимости (например, 1 для СРЕДНЕГО, 2 для СЧЁТА и т.д.).
2. Нажмите Enter для подтверждения. Строка итогов немедленно покажет рассчитанную сумму на основе видимых (нефильтрованных) строк. Когда вы применяете фильтры с помощью встроенных кнопок фильтрации Excel, строка итогов остается видимой внизу и всегда показывает текущие промежуточные итоги только для видимых строк.
ПРОМЕЖУТОЧНЫЕ.ИТОГИ, если ваши данные увеличиваются или уменьшаются. Эта формула не будет автоматически расширяться, если вы не используете динамический диапазон, например OFFSET или INDEX.Резюме: Формула ПРОМЕЖУТОЧНЫЕ.ИТОГИ поддерживает множество агрегированных функций (например, СРЕДНЕЕ, СЧЁТ и т.д.), делая её гибкой для строк итогов, используемых в финансовых, складских и отчетных рабочих процессах.
Фильтрация данных без учета строки итогов снизу с помощью кода VBA
Для пользователей, работающих с большими наборами данных или тех, кто ищет автоматизированный подход к обеспечению постоянного отображения строки итогов снизу (и никогда не скрывается, независимо от настроек фильтра), вы можете использовать VBA (Visual Basic for Applications) для программного управления фильтрацией. Это решение особенно полезно в книгах, где диапазон данных или расположение строки итогов меняется динамически, или когда вы хотите обеспечить согласованность для всех пользователей.
Этот пример VBA проверяет последнюю строку в вашем диапазоне данных и заставляет её оставаться видимой, даже после применения фильтров. Он гарантирует, что строка итогов всегда отображается и может быть повторно использована путем назначения макроса кнопке.
Шаги для использования этого решения VBA:
1. В Excel перейдите на вкладку Разработчик. Если вы не видите эту вкладку, вам может потребоваться включить её в параметрах Excel, отметив «Разработчик» в разделе Настройка ленты.
2. Нажмите Visual Basic, чтобы открыть редактор VBA. В новом окне нажмите Вставить > Модуль и вставьте следующий код в новый модуль:
Sub AlwaysShowTotalRow()
Dim ws As Worksheet
Dim lastRow As Long
On Error Resume Next
Set ws = ActiveSheet
' Get last used row in column A
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
' Ensure total row is visible before applying filter
ws.Rows(lastRow).Hidden = False
' If filtering is active, reapply filter
If ws.FilterMode Then
ws.ShowAllData
End If
' Make sure total row remains visible
ws.Rows(lastRow).Hidden = False
End Sub3. После ввода кода закройте редактор VBA. В Excel нажмите Alt + F8, выберите AlwaysShowTotalRow и нажмите Выполнить. Макрос обеспечит, чтобы последняя строка (строка итогов) была видимой даже после применения фильтров.
• Макрос предполагает, что строка итогов является последней используемой строкой в столбце A. Вы можете изменить логику, если ваши данные включают пустые строки или несколько итогов.
• Рекомендуется назначить этот макрос кнопке для удобного повторного использования, если ваши данные часто меняются.
• Если ваша книга содержит несколько разделов, используйте именованный диапазон или конкретный маркер столбца, чтобы более надежно найти правильную строку итогов.
Преимущества: Автоматизирует видимость строки итогов независимо от действий пользователя или фильтрации. Идеально подходит для информационных панелей, отчетов или общих книг.
Ограничения: Требуется книга с поддержкой макросов и правильные разрешения для макросов. Может потребоваться настройка для более сложных макетов листов.
Резюме: Этот подход с использованием VBA рекомендуется для автоматизированных сред, где ручное управление строками итогов ненадежно или неудобно. Он помогает поддерживать видимость сводных данных во всех сценариях использования.
Лучшие инструменты для повышения продуктивности в 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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек