Недавно я написал статью о как использовать сводные функции в Excel, чтобы упростить обобщение больших объемов данных, но в этой статье были учтены все данные на листе. Что, если вы хотите просмотреть только часть данных и обобщить эту часть данных?
В Excel вы можете создавать фильтры для столбцов, которые будут скрывать строки, не соответствующие вашему фильтру. Кроме того, вы также можете использовать специальные функции Excel для суммирования данных, используя только отфильтрованные данные.
В этой статье я расскажу вам, как создавать фильтры в Excel, а также использовать встроенные функции для суммирования отфильтрованных данных.
Создание простых фильтров в Excel
В Excel вы можете создавать простые и сложные фильтры. Начнем с простых фильтров. При работе с фильтрами у вас всегда должна быть одна строка вверху, которая используется для меток. Наличие этой строки не является обязательным, но это немного упрощает работу с фильтрами.
Выше у меня есть поддельные данные, и я хочу создать фильтр для столбца Город . В Excel это сделать очень просто. Откройте вкладку Данные на ленте, а затем нажмите кнопку Фильтр . Вам также не нужно выделять данные на листе или нажимать на первую строку.
Когда вы нажимаете «Фильтр», в каждом столбце в первой строке автоматически добавляется небольшая кнопка раскрывающегося списка в самом правом углу.
Теперь щелкните стрелку раскрывающегося списка в столбце «Город». Вы увидите несколько разных вариантов, о которых я расскажу ниже.
Вверху вы можете быстро отсортировать все строки по значениям в столбце «Город». Обратите внимание: при сортировке данных перемещается вся строка, а не только значения в столбце «Город». Это гарантирует, что ваши данные останутся нетронутыми, как и раньше.
Кроме того, рекомендуется добавить столбец в самом начале с именем ID и пронумеровать его от одной до любого количества строк, которые есть на вашем листе. Таким образом, вы всегда можете отсортировать данные по столбцу идентификаторов и вернуть данные в том же порядке, в котором они были изначально, если это для вас важно.
Как видите, все данные в электронной таблице теперь отсортированы на основе значений в столбце «Город». Пока ни одна строка не скрыта. Теперь давайте посмотрим на флажки в нижней части диалогового окна фильтра. В моем примере в столбце «Город» есть только три уникальных значения, и эти три отображаются в списке..
Я снял галочку с двух городов и оставил один отмеченным. Теперь у меня отображается только 8 строк данных, а остальные скрыты. Вы можете легко определить, что просматриваете отфильтрованные данные, если проверите номера строк в крайнем левом углу. В зависимости от того, сколько строк скрыто, вы увидите несколько дополнительных горизонтальных линий, а цвет цифр станет синим.
Теперь допустим, я хочу отфильтровать второй столбец, чтобы еще больше сократить количество результатов. В столбце C указано общее количество членов в каждой семье, и я хочу видеть результаты только для семей, в которых более двух членов.
Нажмите на стрелку раскрывающегося списка в столбце C, и вы увидите одинаковые флажки для каждого уникального значения в столбце. Однако в данном случае мы хотим нажать Числовые фильтры , а затем нажать Больше . Как видите, есть и масса других вариантов.
Появится новое диалоговое окно, в котором вы сможете ввести значение фильтра. Вы также можете добавить более одного критерия с помощью функции И или ИЛИ. Вы можете сказать, что вам нужны строки, в которых значение больше 2, а не равно 5, например.
Теперь у меня осталось всего 5 строк данных: семьи только из Нового Орлеана и с тремя и более членами. Достаточно легко? Обратите внимание: вы можете легко очистить фильтр для столбца, щелкнув раскрывающийся список, а затем нажав ссылку Очистить фильтр из «Имени столбца» .
Вот и все о простых фильтрах в Excel. Они очень просты в использовании, а результаты довольно просты. Теперь давайте рассмотрим сложные фильтры с помощью диалогового окна Расширенные фильтры.
Создание расширенных фильтров в Excel
Если вы хотите создать более сложные фильтры, вам необходимо использовать диалоговое окно Расширенный фильтр. Например, предположим, что я хотел просмотреть все семьи, живущие в Новом Орлеане, в которых более 2 членов в семье ИЛИ все семьи в Кларксвилле, в которых более 3 членов в семье И strong>только те, у кого адрес электронной почты заканчивается .EDU . Теперь это невозможно сделать с помощью простого фильтра.
Для этого нам нужно настроить лист Excel немного по-другому. Вставьте пару строк над набором данных и скопируйте метки заголовков точно в первую строку, как показано ниже..
А теперь расскажем, как работают расширенные фильтры. Сначала вам необходимо ввести критерии в столбцы вверху, а затем нажать кнопку Дополнительно в разделе Сортировка и фильтр на вкладке Данные .
Итак, что именно мы можем ввести в эти ячейки? Хорошо, давайте начнем с нашего примера. Нам нужны только данные из Нового Орлеана или Кларксвилля, поэтому давайте введем их в ячейки E2 и E3.
Когда вы вводите значения в разные строки, это означает ИЛИ. Теперь мы хотим, чтобы семьи Нового Орлеана имели более двух членов, а семьи Кларксвилля - более 3 человек. Для этого введите >2 в C2 и >3 в C3.
Поскольку >2 и Новый Орлеан находятся в одной строке, это будет оператор AND. То же самое относится и к строке 3 выше. Наконец, нам нужны только семьи с конечным адресом электронной почты .EDU. Для этого просто введите *.edu в D2 и D3. Символ * означает любое количество символов.
Как только вы это сделаете, щелкните в любом месте набора данных, а затем нажмите кнопку Дополнительно . Поле Ранг списка автоматически определит ваш набор данных, поскольку вы щелкнули его перед нажатием кнопки «Дополнительно». Теперь нажмите маленькую кнопку справа от кнопки Диапазон критериев .
Выберите все от A1 до E3, а затем еще раз нажмите ту же кнопку, чтобы вернуться в диалоговое окно «Расширенный фильтр». Нажмите «ОК», и ваши данные теперь будут отфильтрованы!
Как видите, теперь у меня есть только 3 результата, соответствующих всем этим критериям. Обратите внимание: чтобы это работало, метки диапазона критериев должны точно совпадать с метками набора данных.
Очевидно, что с помощью этого метода вы можете создавать гораздо более сложные запросы, поэтому поэкспериментируйте с ним, чтобы получить желаемые результаты. Наконец, давайте поговорим о применении функций суммирования к отфильтрованным данным.
Обобщение отфильтрованных данных
Теперь предположим, что я хочу суммировать количество членов семьи в моих отфильтрованных данных. Как мне это сделать? Что ж, давайте очистим наш фильтр, нажав кнопку Очистить на ленте. Не волнуйтесь, очень легко снова применить расширенный фильтр, просто нажав кнопку «Дополнительно» и еще раз нажав «ОК»..
В нижней части нашего набора данных добавим ячейку под названием Итого , а затем добавим функцию суммы для суммирования общего числа членов семьи. В моем примере я просто набрал =SUM(C7:C31) .
Итак, если я посмотрю на все семьи, всего у меня будет 78 членов. Теперь давайте повторно применим наш расширенный фильтр и посмотрим, что произойдет.
Упс! Вместо правильного числа 11 я все равно вижу, что сумма равна 78! Почему это? Что ж, функция СУММ не игнорирует скрытые строки, поэтому она по-прежнему выполняет вычисления, используя все строки. К счастью, есть несколько функций, которые можно использовать, чтобы игнорировать скрытые строки.
Первый — СУБТИТОГ . Прежде чем мы будем использовать любую из этих специальных функций, вам необходимо очистить фильтр и затем ввести функцию.
После очистки фильтра введите =SUBTOTAL( , и вы увидите раскрывающийся список с множеством опций. Используя эту функцию, вы сначала выбираете тип функции суммирования. вы хотите использовать номер.
В нашем примере я хочу использовать SUM , поэтому я должен ввести число 9 или просто нажать на него в раскрывающемся списке. Затем введите запятую и выберите диапазон ячеек.
Когда вы нажмете Enter, вы увидите, что значение 78 такое же, как и раньше. Однако если теперь применить фильтр еще раз, мы увидим 11!
Отлично! Это именно то, чего мы хотим. Теперь вы можете настроить фильтры, и значение всегда будет отражать только те строки, которые отображаются в данный момент.
Вторая функция, которая работает практически так же, как функция ПРОМЕЖУТОЧНЫЙ ИТОГО, — это АГРЕГАТ . Единственное отличие состоит в том, что в функции AGGREGATE есть еще один параметр, в котором вам нужно указать, что вы хотите игнорировать скрытые строки.
Первый параметр — это функция суммирования, которую вы хотите использовать. Как и в случае с ПРОМЕЖУТОЧНЫМ ИТОГОМ, 9 представляет функцию СУММ. Второй вариант — вам нужно ввести 5, чтобы игнорировать скрытые строки. Последний параметр тот же и представляет собой диапазон ячеек.
Вы также можете прочитать мою статью о суммарных функциях, чтобы более подробно узнать, как используйте функцию АГРЕГАТ и другие функции, такие как РЕЖИМ, МЕДИАНА, СРЗНАЧ и т. д.
Надеемся, эта статья станет хорошей отправной точкой для создания и использования фильтров в Excel. Если у вас есть какие-либо вопросы, не стесняйтесь оставлять комментарии. Наслаждайтесь!.