Главная страница 1
скачать файл

Установка фильтра сводной таблицы в Excel на основе данных в ячейке
(с использованием кода VBA)

Если вы используете сводные таблицы в Excel, в которых содержатся тысячи или десятки тысяч значений, то выбор одного из них фильтром отчета наверняка сводил вас с ума… Обращаю ваше внимание, что речь идет не о тысячах значений в исходных данных, а именно в самой сводной таблице!

Предлагается создать макрос и дополнить его несложным кодом VBA, позволяющим вам установить фильтр отчета на основании значения, помещенного в одну из ячеек на листе сводной таблицы.

Для удобства восприятия излагаемого материала предлагается также использовать Excel-файл с примером. Несколько слов о примере. На листе «Исходные данные» приведены заявки на отгрузку по датам с указанием названия артикула и числа отгружаемых штук (коробок). На основе этих исходных данных сформирована «Сводная таблица», содержащая 3740 артикулов, 5592 заявки и 26 дат.

Что вы делаете обычно? При нажатии на фильтр отчета «Артикул» (Все) в ниспадающем меню возникает огромная полоса прокрутки, и выбор одного из значений непрост, так как Excel не предоставляет возможностей сократить число записей в списке фильтра отчета. Поэтому вы «прокручиваете» весь список, пока не найдете нужное значение. Если после этого вам нужно установить фильтр в иное значение, вы должны сначала «отжать» ранее установленное значение и выбрать новое.

Если вас это утомляет, то дальнейшее изложение для вас! 



Заметим при этом, что, при выборе фильтра по названию строк Excel предоставляет возможность сократить число записей с помощью фильтров по подписи или значению. (Вместо фильтра по подписи может присутствовать фильтр по дате, если названия строк – даты.)

Итак, начинаем…


  1. Убедитесь, что среди закладок на ленте Excel, присутствует «Разработчик»:



  1. Если такой закладки вы не видите, щелкните на кнопке Office в левом верхнем углу и затем на кнопке «Параметры Excel»:

В открывшемся окне «Параметры Excel» перейдите на вкладку «Основные» и поставьте галочку в строке «Показывать меню Разработчик на ленте». Нажмите Ok





  1. Подготовьтесь к записи макроса. Допустим, вас интересуют динамика отгрузок (по дням) определенного артикула. Тогда сводная таблица должна принять вид:



  1. Запускаем запись макроса. Простейший метод – щелкнуть на значке «Запись макроса» рядом с кнопкой «Пуск». (Значок «Макрос» действует как переключатель: запустить / остановить запись)



  1. В открывшемся окне «Запись макроса» набираем имя макроса (в нашем примере «Фильтр_отчета») и задаем параметр «Сохранить в:» «Личная книга макросов». Такой выбор позволит воспользоваться макросом из любой книги Excel. Если установить «Эта книга», макрос будет храниться в открытой книге Excel, и запустить его будет возможно только из этой книги. Сочетание клавиш пока не выбираем, так как это опция полезна только для тех макросов, которые вы используете часто. И еще одно замечание: имя макроса должно начинаться с буквы или знака подчеркивания, не должно содержать пробелы, не должно совпадать со встроенным именем Excel:



  1. Заполняем поле «Описание», чтобы вы (или ваши коллеги) могли вспомнить через несколько месяцев, зачем был нужен этот макрос:

Жмём Ok. С этого момента не делайте никаких лишних движений мышкой и нажатий клавиш! Идет запись макроса!



  1. Допустим, нас интересуют отгрузки артикула 28516. «Давим» на фильтр поля «Артикул», находим в списке артикул 28516, щелкаем на нем и на Ok:



  1. Останавливаем запись макроса, щелкая на значке «Остановить запись макроса» рядом с кнопкой «Пуск».

  2. Мы создали макрос, способный выбирать в фильтре отчета артикул 28516. Теперь надо модернизировать код VBA, чтобы макрос выбирал артикул, прописанный в ячейке листа «Сводная таблица».

  3. Посмотрим на код нашего макроса. Для этого на вкладке ленты «Вид» жмем «Макросы» и в выпадающем меню еще раз «Макросы»:



  1. Выбираем наш макрос. Помните, что мы выбрали опцию «Личная книга макросов»? Так вот, наш макрос записался в файл Personal.xlsb:

Жмем «Войти». Открылось окно MS Visual Basic файл Personal.xlsb, Module1





  1. В первой строке указано название макроса – «Фильтр_отчета», далее идут несколько строк примечаний (строки, начинающиеся с апострофа). Собственно макрос состоит из двух инструкций кода:

ActiveSheet.PivotTables("СводнаяТаблица3").PivotFields("Артикул").ClearAllFilters

Этот код дает команду на активном листе Excel сводной таблицы № 3 в поле «Артикул» очистить все фильтры.

ActiveSheet.PivotTables("СводнаяТаблица3").PivotFields("Артикул").CurrentPage = "28516"

Этот код дает команду в том же фильтре выставить значение 28516

Заменяем эту строку на:

ActiveSheet.PivotTables("СводнаяТаблица3").PivotFields("Артикул").CurrentPage = Range("G1").Value

То есть, вместо фиксированного значения артикула (28516), мы дали команду подставлять значение ячейки G1!


  1. Закрываем окно MS Visual Basic. На предупреждение:

жмем Ok.


  1. Теперь осталось небольшое украшательство и проверка работоспособности нашего скорректированного макроса! Перейдите на лист «Сводная таблица» и на вкладке «Разработчик» выберите элемент «Кнопка»:

Курсор примет вид крестика. Выделите ячейку F1, как бы нарисовав прямоугольник. В открывшемся окне «Назначить макрос объекту» выделите наш макрос, чтобы он появился в поле «Имя макроса», нажмите Ok:





  1. Вместо «Кнопка3» напишите «Артикул» (у вас скорее всего будет «Кнопка1»; у меня увеличился номер, так как я несколько раз создавал кнопку пока оформлял текст заметки ). А также укажите в ячейке G1 новое значение фильтра:



  1. Жмем на кнопку «Артикул». Получилось!!! В сводной таблице отражаются значения для артикула 27274:

скачать файл



Смотрите также:
Предлагается создать макрос и дополнить его несложным кодом vba, позволяющим вам установить фильтр отчета на основании значения, помещенного в одну из ячеек на листе сводной таблицы
42.17kb.
Руководство по использованию Триколор sb установка
63.75kb.
Ваш вылет состоится на основании выписанных авиабилетов. Вам необходимо быть в аэропорту за 2 часа до вылета
74.11kb.
В общем, расскажу вам одну историю, что случилась со мной недавно. Может, кому на пользу пойдёт, так сказать, «наглядным примером, да по разгильдяйскому образу жизни»
70.75kb.
В мае 2012 года в Государственной Думе был принят в первом чтении законопроект о введении саморегулирования в сфере управления многоквартирными домами (мкд)
1960.38kb.
План-схема поможет вам совершить прогулку. Распечатайте ее
105.44kb.
Кто-то теряет свой электорат, а кто-то его находит (15. 06. 06)
11.63kb.
1. Установить границу территории объекта культурного наследия регионального значения «Место, где размещалась конспиративная квартира Царицынской организации рсдрп» согласно приложениям №№1 и 2
55.19kb.
Вы задавали значения свойств элементов управления и формы
51.2kb.
Как увеличить, или создать собственную библиотеку звуков
25.2kb.
Модель цикла обращения знаний в системе корпоративной памяти
59.41kb.
Послушайте сообщение важнейшего значения
44kb.