Как Поставить Фильтр в Сводной Таблице в Столбце Excel • Что это за функция описание
Создание сводной таблицы в Excel
Классический способ составления сводной таблицы
В качестве примера рассмотрим алгоритм действий для составления сводных таблиц в Microsoft Excel 2019. Эта же логика может применяться и в более ранних версиях программы.
В качестве исходной (базовой) таблицы возьмем данные по продажам по продажам. В таблице присутствует информация о наименовании товара, поле, виде спорта, а также, основных экономических показателях, таких как количество проданных единиц, цена и сумма в руб. При этом, если по одному и тому же наименованию товара было несколько продаж, то и строчек будет несколько.
Для корректного формирования сводной таблицы всегда очень важно представлять себе, как должен выглядеть конечный результат. Другими словами, нужно понимать, какую информацию мы хотим видеть в обработанном виде.
Как только мы сделаем нашу базовую таблицу “умной” (или динамической, то есть способной автоматически изменяться), при добавлении в нее новых строк с данными, они в автоматическом режиме будут отображаться и в сводной таблице.
Примечание: Преобразование простой таблицы в “умную” не является обязательным требованием, и этот шаг, при желании, можно пропустить. В этом случае, после каждой корректировки исходной таблицы сводную придется формировать заново либо обновлять, пользуясь методом, который описан в конце статьи.
Шаг 3. Применяем фильтры и другие настройки
Теперь вернемся к нашей задаче. Нам нужно отфильтровать данные по полу (женский) и оставить только строки, которые относятся к виду спорта “теннис”.
В рассмотренном примере был показан только один вариант построения сводной таблицы.
Чтобы сконструировать отличную от предыдущей форму, снова воспользуемся окном списка полей (справа). Если вы специально или случайно закрыли это окно, чтобы вернуть его, кликаем правой кнопкой мыши по любой ячейке внутри таблицы и в открывшемся контекстном меню выбираем пункт “Показать список полей”.
Давайте теперь попробуем перетащить в пустую область “Столбцы” поле “Цена, руб.”, предварительно убрав ранее заданные фильтры, чтобы увидеть, как именно преобразится таблица в ее начальном виде.
Благодаря нашим стараниям таблица изменила свой вид. Теперь есть возможность увидеть разбивку суммы по ценам за единицу товара. При желании и необходимости снова можно задействовать фильтры по полу или виду спорта.
Также можно попробовать добавить в таблицу еще больше информации. Для этого снова открываем список полей и перетаскиваем поле “Продано, шт.” в область “Строки”.
Данное действие позволит выявить, встречались ли в базовой таблице продажи под одинаковому наименованию товара в разных строках. Как видим, для беговых кроссовок 35 размера так и есть.
Помимо этого, в программе есть возможность представить числовые данные в виде гистограммы. Отмечаем необходимую ячейку, переключаемся в главную вкладку, нажимаем на функцию “Условное форматирование”, далее в открывшемся перечне кликаем по кнопке “Гистограммы” и щелкаем по варианту, который понравился больше всего.
Гистограмма настроена для выделенной ячейки. Осталось только нажать на кнопку справа от ячейки и выбирать одну из опций “Применить правило форматирования ко всем ячейкам…”.
Видоизмененная таблица стала более наглядной и привлекательной.
Использование Мастера сводных таблиц
Сводная таблица может быть создана с помощью инструмента под названием “Мастер сводных таблиц”. Но предварительно нужно вынести значок Мастера на Панель быстрого доступа. Для этого выполняем следующую цепочку действий:
Важные моменты, которые нужно учитывать при формировании сводных таблиц
Заключение
Итак, сводную таблицу в Excel можно создать, пользуясь двумя методами: классическим и с помощью Мастера сводных таблиц. Последний охватывает более широкий спектр альтернатив на этапе выбора источников данных, однако, в большинстве случаев первый способ более чем достаточен для для конструирования сводной таблицы.
Блеск и нищета сводных таблиц. Часть 13 | КомпьютерПресс
После проделанных шагов в основной таблице останутся только записи по заданному разграничивающему значению. Чтобы отменить последнее действие (фильтрацию), нужно нажать на кнопку “Очистить”, которая находится в разделе “Данные”.
Финансы в Excel
Главная Статьи Сводные таблицы Редактирование сводной таблицы
Редактирование сводной таблицы
Подключение макросов
Для работы примера требуется подключение макросов VBA. В Excel 2002-2003 может потребоваться предварительно изменить безопасность макросов до среднего или низкого уровня (Сервис \ Макросы \ Безопасность). В Excel 2007 щелкните на строку сообщения под лентой, а затем подтвердите операцию.
Без подключенных макросов пример будет работать в стандартном режиме отображения деталей (drill-down) при двойном клике в области данных сводной таблицы.
Функциональность
Программный код перехватывает двойной клик в области данных сводной таблицы и отображает исходные данные в виде отфильтрованных строк в отдельном окне. После редактирования и возврата в окно сводной таблицы, вторичное окно исходных данных автоматически закрывается, а данные в отчете обновляются.
В примере исходные данные размещены на листе «Data», а сводная таблица на листе «Отчет». В сводной таблице имеются 2 поля данных: «Объем», «Выручка» и вычисляемое поле «Средняя цена». Двойной клик доступен на обычных полях, при выборе вычисляемого поля никаких действий не производится, так как в этом случае, очевидно, нечего редактировать.
В новом окне редактирования выделяется ячейки столбца по выбранным в сводной таблице данным:
Обратите внимание, что в двух окнах находится одна и та же рабочая книга. Это видно по ярлыкам листов. Там же при первом открытии примера вы заметите появление нового листа «Filter». Этот лист используется для организации фильтра на листе исходных данных.
После редактирования, вернитесь на начальное окно со сводной таблицей. При этом служебное окно с исходными данными должно закрыться автоматически:
Программный код примера работает с любыми сводными таблицами, основанными на простых данных Excel. Для практического применения на реальном примере сводной таблицы несложно организовать редактирование в диалоговом окне вместо клонирования окна Excel.
Общий принцип поиска ключевой информации сводной таблицы может помочь создать более сложный интерфейс для выборки и редактирования информации из базы данных.
Алгоритм работы
- Устанавливается свойство сводной таблицы, отменяющее стандартное поведение на двойной клик.
- На уровне листа, на котором располагается сводная таблица, перехватывается событие двойного клика в области данных.
- Проверяется, не является ли поле вычисляемым. Создается пустой лист для фильтрации исходных данных.
- Формируется значения фильтра через проверку диапазонов областей строк, столбцов и страниц сводной таблицы. Эти значения записываются на служебный лист.
- С помощью операции «Расширенный фильтр» фильтруется исходный диапазон данных.
- Создается новое окно, в которое выводится отфильтрованный диапазон исходных данных.
- Включается событие на активизацию окна Excel. При возврате в окно со сводной таблицей, второе окно с исходными данными закрывается.
Программный код
Небольшие комментарии к наиболее интересным частям программного кода VBA.
События рабочей книги (ThisWorkbook):
Автоматическое закрытие второго окна рабочей книги происходит при условии, что имя текущего окна заканчивается на «:2» (Right(Wn.Caption, 2) = «:2»). Этот суффикс (двоеточие плюс номер) Excel присваивает новому окну автоматически.
Процедура поиска и вывода данных для редактирования:
Исправление ошибки работы с датами в Excel 2007:
Комментарии
Добрый день.
Подскажите пож: «как убрать фильтр чтобы на листе Data отразилась вся база с изменениями»
Заране благодарен
Доброго времени суток у меня возникает ошибка У меня не закрывается второе «новое» окно после возврата к сводной таблице. Данные в сводной таблице обновляются только при нажатие обновить данные и второе окно приходится вручную закрывать после изменение данных
Прошу прощения, не очень понял как использовать функциональный шаблон для какой-либо текущей задачи. Если не затруднит, поясните плз.
Спасибо!
Хотел сам писать что-то подобное, а тут раз и есть решение.
Отмечу одно — если даты сгруппировать, например по месяцам работать перестает. Почему — понятно, переписывать долго, точнее некогда. А сам макрос — здоровский. С именованными диапазонами работает легко, если имя присваивать листу, на не книге.
Всё верно. Если источник «умная таблица», то работает. Не работает если источником Сводной таблицы служит именованный диапазон, т.е. если источник сводной таблицы указан не ‘Лист1’!$A:$B, а Таблица1 (которая является диапазоном ‘Лист1’!$A:$B, но с присвоенным именем Таблица1. Но это мелочи, имхо.
Марат, простите. Обманул. И с именами массивов работает. Ошибка была моя в том, что лист Filter надо удалять, если в макросе меняешь название листа с отчётом и с данными. В общем, макрос — конфетка.
Прошу подсказать, как прописать, что б было автоматическое определение листа «отчета» и листа «данных», а то в случае если в одной книге несколько сводных таблиц с разных источников данных. надо прописывать ручками.
Николас, спасибо за проявленный интерес. Вообще, это в чистом виде демонстрационный пример. Для практического применения я бы формы VBA использовал. Уж больно ненадежно это управление окнами рабочей книги.
«Умная» — .это имеется в виду то, что через ленту «Вставка\Таблица» добавляется? По идее нет разницы в источнике. Попробуйте.
Всё верно. Если источник «умная таблица», то работает. Не работает если источником Сводной таблицы служит именованный диапазон, т.е. если источник сводной таблицы указан не ‘Лист1’!$A:$B, а Таблица1 (которая является диапазоном ‘Лист1’!$A:$B, но с присвоенным именем Таблица1. Но это мелочи, имхо.
Прошу подсказать, как прописать, что б было автоматическое определение листа «отчета» и листа «данных», а то в случае если в одной книге несколько сводных таблиц с разных источников данных. надо прописывать ручками.
Николас, спасибо за проявленный интерес. Вообще, это в чистом виде демонстрационный пример. Для практического применения я бы формы VBA использовал. Уж больно ненадежно это управление окнами рабочей книги.
«Умная» — .это имеется в виду то, что через ленту «Вставка\Таблица» добавляется? По идее нет разницы в источнике. Попробуйте.
Николас, спасибо за замечание.
Как оказалось, в 2010м очередное чудо. почему-то перестало отрабатывать свойство PivotItem.Visible. Исправил файл.
- Щелкните на имени поля таблицы (1), содержащего слова “Сумма по полю”, за которыми следует имя поля. Перейдите на вкладку Анализ (2) набора контекстных вкладок Работа со сводными таблицами, и щелкните на кнопке Параметры поля (3).
Сводные таблицы в Excel: Создание, Фильтрация, Форматирование
- Устанавливается свойство сводной таблицы, отменяющее стандартное поведение на двойной клик.
- На уровне листа, на котором располагается сводная таблица, перехватывается событие двойного клика в области данных.
- Проверяется, не является ли поле вычисляемым. Создается пустой лист для фильтрации исходных данных.
- Формируется значения фильтра через проверку диапазонов областей строк, столбцов и страниц сводной таблицы. Эти значения записываются на служебный лист.
- С помощью операции «Расширенный фильтр» фильтруется исходный диапазон данных.
- Создается новое окно, в которое выводится отфильтрованный диапазон исходных данных.
- Включается событие на активизацию окна Excel. При возврате в окно со сводной таблицей, второе окно с исходными данными закрывается.
На экране появится диалоговое окно Рекомендуемые сводные таблицы, показанное на скриншоте ниже. В левой его части приведены примеры сводных таблиц, которые Excel может создавать на основе данных, выбранных в списке.
Сводная таблица Excel: создание, работа с данными, удаление
Для чего в Excel нужны сводные таблицы
В оригинале этот термин звучит как Pivot Table, а перевод фразы несколько туманен: «таблица, которую можно менять, крутить, демонстрировать в разных проекциях». Что это означает? Интерпретация табличных данных – задача непростая. Обычно это делается с помощью формул, получая итоговые результаты в конце таблицы.
Большинство регулярных пользователей Excel даже не догадываются о наличии такого мощного инструмента. Для наглядности приведём небольшой и показательный пример использования сводных таблиц Excel «для чайников».
Допустим, вы менеджер компании и отвечаете за ведение документации по продажам. Все сделки заносятся в таблицу с указанием всех деталей (продавец, покупатель, количество, сумма, дата продажи, адрес покупателя и т. д.). Руководитель просит отчёт о продажах по регионам.
Между тем любой пользователь Excel знает, насколько сложно выполнять подобные манипуляции в рамках исходной таблицы.
Создание и работа со сводными таблицами в Excel
Второй, основной способ создания сводных таблиц – «с нуля», для чего жмём первую кнопку.
Откроется новое окно, в котором необходимо задать два параметра: исходный диапазон ячейки и место расположения СТ. Поскольку мы перед созданием сводной таблицы выделили ячейку, диапазон определится автоматически, значением по умолчания для второго параметра является новый лист. Так что ничего не меняем и жмём Ок. В результате откроется новый лист с незаполненным макетом.
В ней внизу указаны четыре области, на основании которых будет сформирована сводная таблица. Рассмотрим их более подробно:
- область значений является главной частью СТ, поскольку именно здесь указывается, каким образом обрабатывать столбцы и строки. Обычно это суммирование – этот способ агрегирования будет установлен по умолчанию при условии, что все ячейки диапазона принимают числовые значения. При наличии хотя бы одной пустой ячейки или с данными в формате, отличном от числового, вместо суммы значений ячеек будет подсчитано их количество, так что этот факт нужно обязательно учитывать. Для нашего примера суммирование будет производиться по каждому товару в соответствующем регионе. Кроме суммирования, можно задать любой другой способ вычислений, которых имеется около двух десятков (вычисление среднего значения, доли и пр.). Проще всего это сделать непосредственно в сводной таблице, кликнув ПКМ по любой ячейке и выбрав необходимый способ вычислений;
- область строк – состоит из уникальных значений ячеек, расположенных в левом столбце. Если вставить сюда несколько полей, сводная таблица будет уже не плоской, а многоуровневой;
- область столбцов также содержит значения полей, но уже относительно верхней строки исходной таблицы;
- область фильтра позволяет накладывать ограничения на условия вычислений значений ячеек. Обычно сюда помещают поле, по которому должна фильтроваться выборка, заданная в трёх других областях.
Благодаря наличию этих областей можно настроить выборку из исходной таблицы практически любой сложности, потратив на это считанные минуты.
Чтобы было яснее, поясним вышесказанное на примере, описанном в предыдущем разделе.
Итак, пошаговая инструкция, как сделать сводную таблицу в Excel:
- из перечня полей в сводной таблице перетаскиваем поле «Выручка» в область значений;
- поле «Область» (регион нахождения покупателя) перетаскиваем в область строк;
- поле «Товар» помещаем в область столбцов.
Всего три действия – и сводная таблица, пригодная для отправки начальству, готова! На её составление у нас ушло порядка 10 секунд.
Чтобы вместо выручки просуммировать прибыль, достаточно в область значений вместо выручки поместить соответствующее поле, соответствующие вычисления в сводной таблице будут произведены автоматически.
Вторая задача тоже выполняется за секунды – чтобы поменять местами регионы и товары, просто перетащите их из одной области в другую (поменяйте местами).
Чтобы получить результаты по каждому менеджеру, достаточно в область фильтра поместить поле «Менеджер», тогда вверху появится селектор, в котором нужно указать нужную фамилию для получения отчёта. Имеется возможность объединять отчёт по нескольким продавцам.
Разумеется, мы привели не самый сложный, но весьма показательный пример, позволяющий понять, как в Excel создавать сводные таблицы. Способ вычислений значений в ячейках может быть более замысловатым, возможно, придётся использовать вычисляемые поля, а также применять условное форматирование – возможности в этом плане у Excel внушительные.
Об источнике данных
Мы уже упоминали, что к исходным данным, используемым для правильного создания сводных таблиц в программе Excel, предъявляются определённые требования. Перечислим их:
- главное ограничение связано с обязательным наличием названий над столбцами, участвующими в вычислениях. Такие идентификаторы необходимы для формирования результирующих отчётов – при добавлении в исходную таблицу новых записей (строк) формат СТ менять не нужно, а результаты будут пересчитаны автоматически;
- убедитесь, что в ячейках строк и столбцов, участвующих в выборке, введены числовые параметры. Если они будут пустыми или содержать текстовые значения, эти строки выпадут из расчётов, что исказит результаты вычислений;
- следите за соответствием форматов строк и содержимым ячеек. Если она определена как дата, то все значения в столбце должны иметь такой же формат, иначе фильтрация и просчеты будут неправильными.
Если придерживаться этих правил, то с построением сводной таблицы проблем возникнуть не должно.
Обновление данных в сводной таблице Excel
Хотя мы говорили об автоматическом пересчёте сводных таблиц при внесении корректировок в исходные данные или в структуру самой СТ, на самом деле из соображений экономии ресурсов компьютера для пересчёта нужно выполнить определённые действия.
Обновить данные в нашей сводной таблице Excel можно двумя способами:
Кэширование таблиц особенно оправдано, если они большие по размерам, в этом случае скорость работы программы существенно увеличится, особенно если вы вводите новые строки регулярно и с высокой частотой.
Добавление в СТ Excel столбца или строки
Изменение порядка следования столбцов также добиться несложно, просто перенеся мышкой заголовок столбца на нужную позицию.
Формирование сводной таблицы из нескольких файлов (таблиц) Excel
Это более сложная, но вполне выполнимая задача. Используем мастер СТ, который нужно добавить на панель быстрого доступа.
Итак, слева, возле пункта «Файл», жмём на стрелочку, направленную вниз, выбираем пункт «Другие команды». Откроется новое окно, в средней панели щёлкаем на опции «Все команды». Находим в открывшемся перечне «Мастер сводных таблиц», выбираем его и кликаем на кнопе «Добавить». Соответствующий значок появится в верхней панели.
Запускаем мастер. Допустим, нам нужно объединить данные из двух листов, майского и за июнь. Главное условие – они должны совпадать по структуре. Если это так, то в первом окне мастера выбираем опции «Сводная таблица» и следующий параметр – «Несколько диапазонов консолидации».
После нажатия кнопки «Готово» откроется новое окно, в котором указываем «Новый лист» и снова жмём кнопку «Готово».
ВНИМАНИЕ. Получение многомерных таблиц неудобно тем, что в ней сложно ориентироваться при большом количестве полей, да и нужные расчёты приходится проводить, каждый раз указывая нужные значения из верхней области таблицы.
Как удалить СТ
Самый простой случай – когда вы создали сводную таблицу, отослали результаты шефу, и она вам больше не нужна. Если вы в этом уверены, просто выбираем таблицу и жмём клавишу Delete. Просто и эффективно.
Но вдруг структура таблицы может вам понадобиться в будущем? В Excel имеется возможность удалить только результаты, или данные ячеек. Рассмотрим, как это делается.
Для удаления результатов вычислений выполняем следующие шаги:
Но как поступить, если вы хотите сохранить результаты, но сами данные вам не нужны, то есть вы хотите освободить стол? Такая ситуация часто возникает, если руководству нужны только итоги. Алгоритм действий:
- снова выбираем любую ячейку, кликаем на вкладке «Анализ»;
- выбираем пункт меню «Действия», кликаем на «Выбрать», отмечаем мышкой всю сводную таблицу;
- щёлкаем ПКМ внутри выделенной области;
- из контекстного меню выбираем пункт «Скопировать»;
- переходим к вкладке «Главная», снова щёлкаем ПКМ и выбираем «Вставить»;
- выбираем вкладку «Вставить значение», в ней отмечаем параметр «Вставить как значение».
В итоге сводная таблица будет стёрта с сохранением результатов.
СОВЕТ. Ускорить процедуру можно посредством использования комбинации клавиш. Для выделения таблицы применяйте Ctrl + A, для копирования – Ctrl + C. Затем жмём ALT + E, ALT + S, ALT + V и завершаем процедуру нажатием Enter.
Для удаления сводных таблиц в Excel 2007/2010 нужно использовать другой алгоритм:
Если ваш начальник любит визуализацию данных, очевидно, что вам придётся использовать сводные диаграммы. Поскольку они занимают много места в таблице, после использования их обычно удаляют.
В старых версиях программы для этого нужно выделить диаграмму, щёлкнуть на вкладке «Анализ», выбрать группу данных и нажать последовательно «Очистить» и «Очистить всё».
При этом, если диаграмма связана с самой сводной таблицей, после её удаления вы потеряете все настройки таблицы, её поля и форматирование.
Для версий старше Excel 2010 нужно выбрать диаграмму, на вкладке «Анализ» выбрать пункт «Действия» и нажать «Очистить» и «Очистить всё». Результат будет аналогичным.
Надеемся, что наши уроки по сводным таблицам позволят вам открыть для себя этот достаточно мощный функционал. Если у вас остались вопросы, задавайте их в комментариях, мы постараемся на них ответить.
Редактирование сводной таблицы
- из перечня полей в сводной таблице перетаскиваем поле «Выручка» в область значений;
- поле «Область» (регион нахождения покупателя) перетаскиваем в область строк;
- поле «Товар» помещаем в область столбцов.
Наверное, все пользователи, которые постоянно работают с Microsoft Excel, знают о такой полезной функции этой программы как фильтрация данных. Но не каждый в курсе, что существуют также и расширенные возможности у этого инструмента. Давайте рассмотрим, что умеет делать расширенный фильтр Microsoft Excel и как им пользоваться.
Публикуя свою персональную информацию в открытом доступе на нашем сайте вы, даете согласие на обработку персональных данных и самостоятельно несете ответственность за содержание высказываний, мнений и предоставляемых данных. Мы никак не используем, не продаем и не передаем ваши данные третьим лицам.