Как сохранить фильтр в Excel используя представления данных

Допустим каждый месяц нам нужно создать отчет о результатах в продажах нашего отдела. Для подготовки отчета используем статистические показатели по всех транзакциях в журнале истории взаиморасчетов с клиентами, выполненных за прошедший период. Чтобы экспонировать данные по конкретному клиенту, например, для сравнения, необходимо каждый раз фильтровать таблицу истории взаиморасчетов под разными критериями. Нет необходимости тратить время и усилия на многоразовое фильтрование тех самых данных. Можно существенно облегчить решение данной задачи сгруппировав данные и сохранив их как нестандартный вид представления. Благодаря этому одним кликом мышки можно детально экспонировать необходимые данные на данный момент.

Как быстро пользоваться фильтром в Excel

Допустим наш журнал истории взаиморасчетов с клиентами отображается также как на рисунке.

История продаж и взаиморасчетов по клиентам:

История взаиморасчетов.

Нам необходимо отобразить разные варианты данных с разделением на группы: название фирм, городов и сумм. К последней группе еще хотим добавить итоговый показатель.

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

Допустим первая группа данных должна включать в себя данные касающиеся транзакций сумой выше 20 000 рублей реализованной фирмой ADEX Comp. Для этого:

  1. Выделите заголовок таблицы «Сумма» D1 и выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Фильтр». Для исходной таблицы включиться режим автофильтра и в ее заголовках появятся кнопки выпадающего меню.
    2. Автофильтр.
  2. Откройте выпадающее меню ячейки D1 и выберите опцию «Числовые фильтры»-«Настраиваемый фильтр». В результате появиться диалоговое окно «Пользовательский автофильтр».
    3. Настраиваемый фильтр.
  3. В первом выпадающем списке данного окна укажите на значение «больше», а в поле ввода напротив введите значение 20000. И нажмите ОК.
    4. Пользовательский автофильтр.
  4. Теперь раскройте выпадающее меню в ячейке A1 («Название фирмы») и выберите только фирму ADEX Comp.
Название фирмы.

Применено второе условие фильтрования данных.



Как посчитать сумму по фильтру в Excel

Отфильтрованная группа готова, осталось только добавить итоговое значение для столбца «Сумма». Для этого выберите первую пустую ячейку под этим столбцом и выберите инструмент: «ГЛАВНАЯ»-«Редактирование»-«Сумма» или нажмите комбинацию клавиш CTR+=. После нажатия на клавишу Enter суммируются только видимые значения в столбце. По завершению присвойте для этой ячейки денежный формат.

ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

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

  1. – среднее значение, альтернатива функции СРЕДЗНАЧ.
  2. – подсчет количества значений, альтернатива для СЧЁТ.
  3. – подсчет только непустых ячеек в диапазоне, как СЧЁТЗ.
  4. – возвращает максимальное значение (МАКС).
  5. – возвращает минимальное значение (МИН).
  6. – возвращает произведение аргументов (ПРОИЗВЕД).
  7. – функция стандартного отклонения как СТАНДОТКЛОН.
  8. – стандартное отклонение по генеральной совокупности (логические и текстовые значения – игнорируются) как в СТАНДОТКЛОНП.
  9. – суммирует значение как СУММ.
  10. – дисперсия по выборке как ДИСП.
  11. – дисперсия для генеральной совокупности ДИСПР.

Примечание. Номера констант могут быть с включением и с исключением значений в скрытых строках инструментом: «ГЛАВНАЯ»-«Ячейки»-«Формат»-«Скрыть или отобразить». Отличие заключаться в коде константы:

  • 1-11 – с включением;
  • 101-111 – с исключением.

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

для другой фирмы.

Сохранение фильтра в представление данных

Когда первый набор значений для группы готовый, можно его сохранить как один из видов таблицы журнала истории взаиморасчетов по клиентам. Благодаря этому мы получим молниеносный доступ к организации новой структуры данных в таблице. Для этого:

  1. Отфильтруйте таблицу по выше описанным критериям и выберите инструмент: «ВИД»-«Режимы просмотра книги»-«Представления».
    2. Представления.
  2. В появившемся диалоговом окне «Представления» нажмите на кнопку «Добавить». Появиться новое окно «Добавление представления».
    3. Добавить.
  3. В поле «Имя:» введите название «ADEX Comp >20 тыс.руб.» для этого представления данных на рабочем листе и нажмите ОК.
    4. Добавление представления.
  4. Создайте новое представление для нового отображения таблицы с другими критериями фильтрования и придумайте им новые названия.

Теперь достаточно выбрать любой из списка видов представления данных на листе, используя выше указанный инструмент: «ВИД»-«Режимы просмотра книги»-«Представления». При выборе любого из преставлений данные на листе будут автоматически преобразовываться и группироваться в ранее сохраненный их вид. Теперь нет необходимости каждый раз заново создавать сложные фильтры по множеству критериев для их определения. Достаточно переключиться между видами представлений. Указать на представление в списке и нажать на кнопку «Применить».

Пример.

Чтобы удалить представление снова вызовите диалоговое окно: «ВИД»-«Режимы просмотра книги»-«Представления». Потом выделите в списке ненужный вид и нажмите на кнопку «Удалить».


en ru