Как сделать фильтр по дате в сводной таблице excel
Работая с большими сводными таблицами, часто приходится их принудительно упрощать, фильтруя часть информации, чтобы не утонуть в цифрах. Самый простой способ для этого - поместить некоторые поля в область фильтра (в версиях до 2007 она называлась область страниц) и выбирать из выпадающих списков только нужные значения:
Неудобства такого способа очевидны:
- При выборе нескольких элементов - их не видно, а видно текст "(несколько элементов)". Не юзерфрендли ни разу.
- Один фильтр отчета жестко привязан к одной сводной таблице. Если у нас несколько сводных таблиц (а одной обычно дело не ограничивается), то для каждой(!) придется создавать свой фильтр и для каждой придется его раскрывать, отмечать галочками нужные элементы и жать ОК. Жутко неудобно, видел даже энтузиастов, которые писали специальные макросы для этих целей.
Если у вас Excel 2010, то все это можно делать более изящно - с помощью срезов (slicers) . Срезы - это удобное кнопочное графическое представление интерактивных фильтров отчета для сводной таблицы или диаграммы:
Срез выглядит как отдельный графический объект (как диаграмма или картинка), не связан с ячейками и отображается над листом, что позволяет легко его перемещать. Чтобы создать срез для текущей сводной таблицы нужно перейти на вкладку Параметры (Options) и в группе Сортировка и фильтр (Sort and filter) нажать кнопку Вставить срез (Insert slicer) :
Теперь при выделении или снятии выделения с элементов среза (можно использовать клавиши Ctrl и Shift, а также протягивание с нажатой левой кнопкой мыши для выделения оптом) в сводной таблице будут отображаться только отфильтрованные данные по отобранным элементам. Дополнительный приятный нюанс в том, что срез разными цветами отображает не только выделенные, но еще и пустые элементы, для которых нет ни одного значения в исходной таблице:
Если вы используете несколько срезов, то это позволит быстро и наглядно отобразить взаимосвязи между элементами данных:
Один и тот же срез можно легко привязать к нескольким сводным таблицам и диаграммам, используя с вкладки Параметры (Options) кнопку Подключения к сводной таблице (Pivot table connections) , которая открывает соответствующее диалоговое окно:
Тогда выделение элементов на одном срезе будет влиять сразу на несколько таблиц и диаграмм, возможно даже, на разных листах.
Не осталась забыта и дизайнерская составляющая. Для форматирования срезов на вкладке Конструктор (Design) есть несколько встроенных стилей:
. и возможность создавать свои варианты оформления:
А уж в связке "сводная таблица - сводная диаграмма - срез" все это выглядит совсем замечательно:
Как отфильтровать даты между двумя конкретными датами в Excel?
Иногда вам может потребоваться фильтровать данные или записи только между двумя конкретными датами в Excel. Например, вы хотите отобразить записи о продажах с 9 по 1 вместе в Excel, скрывая другие записи. В этой статье рассматриваются способы простой фильтрации дат между двумя конкретными датами в Excel.
Combine multiple worksheets or workbooks into one single worksheet or workbook may be a huge task in your daily work. But, if you have Kutools for Excel, its powerful utility – Combine can help you quickly combine multiple worksheets, workbooks into one worksheet or workbook.
Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 60 days. Read More Download the free trial now
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools : Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools : Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color ; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment.
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Отфильтруйте даты между двумя конкретными датами с помощью команды фильтра
Предположим, у вас есть следующий отчет, и теперь вы хотите отфильтровать элементы между 9 и 1, чтобы вы могли быстро обобщить некоторую информацию. Смотрите скриншоты:
![]() | ![]() |
Microsoft Excel Фильтр Команда поддерживает фильтрацию всех дат между двумя датами с помощью следующих шагов:
Шаг 1: Выберите столбец даты, столбец C в случае. И нажмите Данные > Фильтр, см. снимок экрана:
Шаг 3: Во всплывающем окне Пользовательский автофильтр В диалоговом окне укажите две даты, по которым вы будете выполнять фильтрацию. См. Следующие шаги:
Шаг 4: Нажмите OK. Теперь он фильтрует столбец Date между двумя конкретными датами и скрывает другие записи, как показано на следующем снимке экрана:
Фильтровать даты между двумя конкретными датами с кодом VBA
Следующий короткий код VBA также может помочь вам отфильтровать даты между двумя конкретными датами, пожалуйста, сделайте следующее:
Шаг 1: Введите две конкретные даты в пустые ячейки. В этом случае я ввожу дату начала 9 в ячейку E1 и дату окончания 2012 в ячейку E1.
Шаг 2: Затем удерживайте ALT + F11 ключи, и он открывает Microsoft Visual Basic для приложений окно.
Шаг 3: Нажмите Вставить > Модулии вставьте следующий код в окно модуля.
Public Sub MyFilter() Dim lngStart As Long, lngEnd As Long lngStart = Range("E1").Value 'assume this is the start date lngEnd = Range("E2").Value 'assume this is the end date Range("C1:C13").AutoFilter field:=1, _ Criteria1:=">=" & lngStart, _ Operator:=xlAnd, _ Criteria2:="< gt-block">Примечание:
- В приведенном выше коде lngStart = Диапазон ("E1") , E1 это дата начала на вашем листе, и lngEnd = Диапазон ("E2") , E2 - указанная вами дата окончания.
- Диапазон ("C1: C13") , диапазон C1: C13 столбец даты, который вы хотите отфильтровать.
- Все приведенные выше коды являются переменными, вы можете изменить их по своему усмотрению.
Шаг 4: Затем нажмите F5 ключ для запуска этого кода, и записи между 9 и 1 были отфильтрованы.
Выберите все даты между двумя конкретными датами с помощью Kutools for Excel
В этом разделе мы рекомендуем вам Выбрать определенные ячейки полезности Kutools for Excel. С помощью этой утилиты вы можете легко выбрать все строки между двумя конкретными датами в определенном диапазоне, а затем переместить или скопировать эти строки в другое место в книге.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1: Выберите диапазон, который вы будете фильтровать по двум датам., а затем нажмите кнопку Кутулс > Выберите > Выбрать определенные ячейки.
2: В Выбрать определенные ячейки диалоговом окне укажите настройки, как показано ниже
- 1). Выбрать Весь ряд вариант в Тип выбора .
- 2). в Конкретный тип раздел, последовательно выберите Больше или равно и Меньше или равно в двух раскрывающихся списках. Затем введите дату начала и дату окончания в следующие текстовые поля.
- 3). Щелкните значок OK кнопка. Смотрите скриншот:
Теперь все строки, соответствующие критерию, выбраны. А затем вы можете скопировать и вставить выбранные строки в необходимый диапазон по мере необходимости.
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Как выполнить фильтрацию по месяцам в сводной диаграмме в Excel?
Например, вы хотите сравнить объем продаж всех фруктов в данном месяце на сводной диаграмме, как вы можете отфильтровать объем продаж по этому месяцу? Здесь я покажу вам два способа простой фильтрации по месяцам в сводной диаграмме в Excel.
Kutools for Excel предоставляет мощный Суперфильтр утилита, помогающая пользователям Excel быстро сортировать даты только по дням / неделям / месяцам / кварталам / годам, фильтровать текстовые строки по длине текста, фильтровать текстовые строки по прописным или строчным буквам и т. д. Полнофункциональная бесплатная 30-дневная пробная версия!
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу .
- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Фильтр по месяцам в сводной диаграмме, если метки / легенды осей являются датами
Предположим, вы создали сводную диаграмму, а метки оси - это даты, как показано на снимке экрана ниже. Вы можете легко отфильтровать сводную диаграмму по месяцам следующим образом:
Шаг 1: нажмите Время подано на сводной диаграмме, а затем выберите Фильтры даты > Все даты в периоде а затем выберите месяц, например январь.
Внимание: В Excel 2007 полевые кнопки не отображаются в сводной диаграмме, но есть панель фильтров сводной диаграммы для фильтрации. Щелкните сводную диаграмму, перейдите к панели фильтров сводной диаграммы. Поля осей (категории) раздел (или Легендарные поля (серия) раздел), щелкните стрелка Кроме того, Время, А затем нажмите Фильтры даты > Все даты периода > январь или другой месяц.
Теперь отфильтровываются только точки данных, полученные в выбранном месяце. См. Снимок экрана ниже:
Фильтрация по месяцам в сводной диаграмме, если метки / легенды осей не являются датами
В некоторых случаях вы можете создать сводную диаграмму, а метки или легенды осей не являются датами, как показано на снимке экрана ниже. Теперь, если вам нужно отфильтровать сводную диаграмму по месяцам, вы можете сделать следующее:
Шаг 1. На панели полей сводной диаграммы перетащите Время подано из Выберите поля для добавления в отчет раздела с Ось (Категории) раздел или Легенда (Серия) .
Внимание: Вы также можете щелкнуть правой кнопкой мыши Время подано в Выберите поля для добавления в отчет раздел и выберите Добавить в поля оси (категории) or Добавить в поля легенды (серии) из контекстного меню.
Тогда Время в сводную диаграмму была добавлена кнопка filed, и вы можете следить за тем же способ фильтрации сводной диаграммы по месяцам.
После фильтрации по месяцам сводная диаграмма может отображаться, как показано ниже:
В обычной таблице или диапазоне можно легко отфильтровать данные, нажав Данные > Фильтр, но знаете ли вы, как фильтровать в сводной таблице? В этой статье мы подробно расскажем о методах.
Фильтровать диапазон дат в сводной таблице с добавлением поля даты в качестве метки строки
Перетащив поле даты в качестве метки первой строки, вы можете легко отфильтровать диапазон дат в сводной таблице. Пожалуйста, сделайте следующее:
1. Выберите исходные данные и нажмите Вставить > PivotTable.
2. В диалоговом окне «Создание сводной таблицы» укажите целевой диапазон для размещения сводной таблицы и щелкните значок OK кнопка. Смотрите скриншот:
3. На панели полей сводной таблицы перетащите Время поле в качестве метки первой строки, затем перетащите другие поля в разные разделы по мере необходимости. Смотрите скриншот:
4. Перейдите к сводной таблице, нажмите стрелку рядом с заголовки строк, снимите отметки с дат, которые вы будете скрывать, и нажмите OK кнопка. Смотрите скриншот:
Теперь вы отфильтровали диапазон дат в сводной таблице.
Ноты:
(1) Если вам нужно отфильтровать указанный диапазон дат в сводной таблице, щелкните стрелку рядом с надписью строки, а затем щелкните Фильтры даты > До/После/Между в раскрывающемся списке по мере необходимости.
В моем случае я выбираю Фильтры даты > Между. Теперь в диалоговом окне «Фильтр даты (Дата)» укажите определенный диапазон дат и щелкните значок OK кнопка. Смотрите скриншот:
Теперь вы отфильтровали указанный диапазон дат в сводной таблице.
(2) Если вы хотите отфильтровать динамический диапазон дат в сводной таблице, нажмите стрелку рядом с заголовки строк, а затем в раскрывающемся списке щелкните Фильтры даты > На этой неделе, Этот месяц, Следующий квартали т.д. по мере необходимости.
Отфильтровать диапазон дат в сводной таблице с добавлением данных в виде фильтра отчета
Вы также можете перетащить поле даты в раздел «Фильтр» на панели «Поля сводной таблицы», чтобы отфильтровать диапазон дат в сводной таблице.
1. Пожалуйста, следуйте Шаг 1-2 вышеуказанного метода для создания сводной таблицы.
2. На панели полей сводной таблицы перетащите Время поле к Фильтр раздел, а затем перетащите другие поля в другие разделы по мере необходимости.
Перейдите к сводной таблице, вы увидите, что поле «Дата» добавлено в качестве фильтра отчета над сводной таблицей.
3. Пожалуйста, щелкните стрелку рядом с (Все), проверить Выбрать несколько элементов в раскрывающемся списке, даты следующей проверки, которые вы отфильтруете, и, наконец, щелкните OK кнопка. Смотрите скриншот:
Теперь вы отфильтровали диапазон дат в сводной таблице.
Легко объединяйте несколько листов / книг / файлов CSV в один рабочий лист / книгу
Объединение десятков листов из разных книг в один может оказаться утомительным занятием. Но с Kutools for Excel's Объединить (рабочие листы и рабочие тетради) утилиту, вы можете сделать это всего за несколько кликов! Полнофункциональная бесплатная 30-дневная пробная версия!
Во многих компаниях принято отмечать дни рождения сотрудников или поздравлять с ДР клиентов, предлагая скидки именинникам. И тут же возникает проблема: если в компании ощутимо большое количество сотрудников/клиентов, то сортировка их списка по дате рождения дает не совсем желательный результат:
Поскольку Microsoft Excel воспринимает любую дату как числовой код (количество дней с начала века до текущей даты), то сортировка идет, на самом деле, по этому коду. Таким образом мы получаем на выходе список по порядку "старые-молодые", но из него совсем не видно у кого в каком месяце день рождения.
Способ 1. Функция ТЕКСТ и дополнительный столбец
Для решения задачи нам потребуется еще один вспомогательный столбец с функцией ТЕКСТ (TEXT) , которая умеет представлять числа и даты в заданном формате:
В нашем случае формат "ММ ДД" означает, что нужно отобразить из всей даты только двузначные номер месяца и день (без года).
Теперь простая сортировка по вспомогательному столбцу (вкладка Данные - Сортировка) как раз и даст нужный результат:
Для полноты ощущений можно добавить к отсортированному списку еще автоматическое отчеркивание месяцев друг от друга горизонтальной линией. Для этого выделите весь список (кроме шапки) и выберите на вкладке Главная команду Условное форматирование - Создать правило (Home - Conditional formatting - Create Rule) . В открывшемся окне выберите нижний тип правила Использовать формулу для определения форматируемых ячеек и введите следующую формулу:
После нажатия на ОК к нашей таблице добавятся симпатичные разделительные линии по месяцам:
Способ 2. Сводная таблица с группировкой
Этот способ вместо дополнительных столбцов и функций задействует супермощный инструмент Excel - сводные таблицы. Выделите ваш список и на вкладке Вставка (Insert) нажмите кнопку Сводная таблица (Pivot Table) , а затем ОК в появившемся окне. Перетащите поле с датой в область строк - Excel выведет на листе список всех дат в первом столбце:
Щелкните правой кнопкой мыши по любой дате и выберите команду Группировать (Group) . В следующем окне убедитесь, что выбран шаг группировки Месяцы и нажмите ОК. Получим список всех месяцев, которые есть в исходной таблице:
Теперь, чтобы увидеть у кого именно из сотрудников день рождения приходятся на конкретный месяц, перетащите поле с именем сотрудника и бросьте его под поле с датой в область строк:
Задача решена, и не нужно возиться с формулами. Единственный минус этого варианта в том, что Excel не умеет группировать столбцы с пустыми ячейками, т.е. вы должны иметь полностью заполненный датами столбец в исходной таблице.
Ну, вот - можно идти собирать деньги с коллег на очередной тортик или закупать подарки для любимых клиентов :)
Читайте также: