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