В этом уроке я покажу Вам самые популярные макросы в VBA Excel, которые вы сможете использовать для оптимизации своей работы. VBA — это язык программирования, который может использоваться для расширения возможностей MS Excel и других приложений MS Office. Это чрезвычайно полезно для пользователей MS Excel, поскольку VBA может использоваться для автоматизации вашей работы и значительно увеличить Вашу эффективность. В этой статье Вы познакомитесь с VBA и я вам покажу некоторые из наиболее полезных, готовых к использованию примеров VBA. Вы сможете использовать эти примеры для создания собственных скриптов, соответствующих Вашим потребностям.

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

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

Table of Contents

Как включить макросы в Excel

В Excel нажмите комбинацию клавиш alt + F11. Это приведет вас к редактору VBA в MS Excel. Затем щелкните правой кнопкой мыши папку Microsoft Excel Objects слева и выберите Insert => Module. Это место, где сохраняются макросы. Чтобы использовать макрос, вам нужно сохранить документ Excel как макрос. Из табуляции File => Save as, выберите Save as macro-enabled Workbok (расширение .xlsm) Теперь пришло время написать свой первый макрос!

1. Копирование данных из одного файла в другой.

Очень полезный макрос, поскольку он показывает, как скопировать ряд данных изнутри vba и как создать и назвать новую книгу. Вы можете изменить этот макрос в соответствии с вашими собственными требованиями:

2. Отображение скрытых строк

Иногда большие файлы Excel можно содержать скрытые строки для большей ясности И для лучшего удобства пользователей. Вот один макрос, который отобразит все строки из активной рабочей таблицы:

3. Удаление пустых строк и столбов

Пустые строки в Excel — может быть проблемой для обработки данных. Вот как избавиться от них:

4. Нахождение пустых ячеек

13. Создание сводной таблицы

14. Отправка активного файла по электронной почте

Мой любимый код VBA. Он позволяет вам прикреплять и отправлять файл, с которым вы работаете, с предопределенным адресом электронной почты, заголовком сообщения и телом сообщения! Сначала Вам нужно сделать референцию в Excel на Microsoft Outlook (в редакторе Excel VBA, нажмите tools => references и выберите Microsoft Outlook).

15. Вставка всех графиков Excel в презентацию PowerPoint

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

16. Вставка таблицы Excel в MS Word

Таблицы Excel обычно помещаются внутри текстовых документов. Вот один автоматический способ экспорта таблицы Excel в MS Word:

17. Извлечение слов из текста

Мы можем использовать формулы, если хотим извлечь определенное количество символов. Но что, если мы хотим извлечь только одно слово из предложения или диапазон слов в ячейке? Для этого мы можем сами создать функцию Excel с помощью VBA. Это одна из самых удобных функций VBA, поскольку она позволяет создавать собственные формулы, которые отсутствуют в MS Excel. Давайте продолжим и создадим две функции: findword() и findwordrev():

Отлично, мы уже создали две новые функции в Excel! Теперь попробуйте использовать их в Excel. Функция = FindWordRev (A1,1) берет последнее слово из ячейки A1. Функция = FindWord (A1,3) берет третье слово из ячейки A1 и т. Д.

18. Защита данных в MS Excel

Иногда мы хотим защитить данных нашего файла, чтобы только мы могли его изменять. Вот как это сделать с VBA:

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

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

При записи макроса записываются все действия, описанные в Visual Basic для приложений (VBA) коде. Эти действия могут включать ввод текста или чисел, нажатие ячеек или команд на ленте или в меню, форматирование ячеек, строк или столбцов, а также импорт данных из внешнего источника, например Microsoft Access. Приложение Visual Basic (VBA) — это подмножество мощного языка программирования Visual Basic, которое входит в большинство приложений Office. Несмотря на то, что VBA обеспечивает возможность автоматизации процессов между приложениями Office, вам не нужно знать код VBA или программное программирование, если это нужно.

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

Макросы и средства VBA находятся на вкладке Разработчик, которая по умолчанию скрыта, поэтому сначала нужно включить ее. Дополнительные сведения см. в статье Отображение вкладки "Разработчик".

Запись макроса

Перед записью макросов полезно знать следующее:

Макрос, записанный для работы с диапазоном Excel, будет выполняться только для ячеек этого диапазона. Поэтому если вы добавите в диапазон новую строку, макрос не будет применяться к ней.

Если вам нужно записать длинную последовательность задач, советуем вместо этого использовать несколько более мелких макросов.

В макросе могут содержаться и задачи, не относящиеся к Excel. Процесс макроса может охватывать прочие приложения Office и другие программы, которые поддерживают Visual Basic для приложений (VBA). Например, вы можете записать макрос, который сначала обновляет таблицу в Excel, а затем открывает Outlook для ее отправки по электронной почте.

Чтобы записать макрос, следуйте инструкциям ниже.

На вкладке Разработчик в группе Код нажмите кнопку Запись макроса.

В поле Имя макроса укажите имя макроса. Сделайте имя понятным, чтобы можно было быстро найти нужный макрос.

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

Чтобы назначить сочетание клавиш для запуска макроса, в поле Сочетание клавиш введите любую строчную или прописную букву. Рекомендуется использовать сочетания клавиш с CTRL+ SHIFT, так как они будут заменять собой совпадающие с ними стандартные сочетания клавиш в Excel, пока открыта книга, содержащая макрос. Например, если назначить сочетание клавиш CTRL+Z (Отменить), вы не сможете использовать его для функции "Отменить" в данном экземпляре Excel.

В списке Сохранить в выберите книгу, в которой вы хотите сохранить макрос.

Как правило, макрос сохраняется в указанном расположении книги , но если вы хотите, чтобы макрос был доступен при использовании Excel, выберите пункт Личная книга макросов . Если выбрать команду Личная книга макросов, Excel создаст скрытую личную книгу макросов (личное. xlsb), если она еще не существует, и сохранит макрос в этой книге.

В поле Описание при необходимости введите краткое описание действий макроса.

Хотя поле "Описание" является необязательным, рекомендуется его заполнить. Кроме того, желательно ввести понятное описание, которое будет полезно вам и всем, кто запускает макрос. Если у вас много макросов, описания помогут быстро определить, для чего они нужны.

Чтобы начать запись макроса, нажмите кнопку ОК.

Выполните действия, которые нужно записать.

На вкладке разработчик в группе код нажмите кнопку остановить запись .

Работа с макросами, записанными в Excel

На вкладке Разработчик щелкните Макросы, чтобы просмотреть макросы, связанные с книгой. Кроме того, можно нажать клавиши ALT+F8. При этом откроется диалоговое окно Макрос.

Внимание: Макросы нельзя отменить. Прежде чем впервые запускать записанный макрос, сохраните книгу или создайте ее копию, чтобы предотвратить внесение нежелательных изменений. Если вас не устраивают результаты выполнения макроса, вы можете закрыть книгу, не сохраняя ее.

Ниже приведены дополнительные сведения о работе с макросами в Excel.

Сведения о параметрах безопасности макросов и их значении.

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

С помощью редактора Visual Basic можно изменять макросы, присоединенные к книге.

Если книга содержит макрос VBA, который нужно использовать где-либо еще, этот модуль можно скопировать в другую книгу с помощью редактора Microsoft Visual Basic.

Назначение макроса объекту, фигуре или графическому элементу

На листе щелкните правой кнопкой мыши объект, рисунок, фигуру или элемент, которому нужно назначить существующий макрос, а затем выберите команду назначить макрос.

В поле Назначить макроса выберите макрос, который вы хотите назначить.

Вы можете назначить макрос значку и добавить его на панель быстрого доступа или ленту.

Вы можете назначать макросы формам и элементам ActiveX на листе.

Узнайте, как включать и отключать макросы в файлах Office.

Открытие редактора Visual Basic

Нажмите клавиши ALT+F11.

Узнайте, как найти справку по элементам Visual Basic.

Работа с записанным кодом в редакторе Visual Basic (VBE)

С помощью редактора Visual Basic (VBE) вы можете добавлять в записанный код собственные переменные, управляющие структуры и другие элементы, которые не поддерживает средство записи макросов. Так как средство записи макросов фиксирует почти каждый шаг, выполняемый во время записи, может также потребоваться удалить ненужный код. Просмотр записанного кода — отличный способ научиться программировать на VBA или отточить свои навыки.

Пример изменения записанного кода можно найти в статье Начало работы с VBA в Excel.

Запись макроса

Перед записью макросов полезно знать следующее:

Макрос, записанный для работы с диапазоном Excel, будет выполняться только для ячеек этого диапазона. Поэтому если вы добавите в диапазон новую строку, макрос не будет применяться к ней.

Если вам нужно записать длинную последовательность задач, советуем вместо этого использовать несколько более мелких макросов.

В макросе могут содержаться и задачи, не относящиеся к Excel. Процесс макроса может охватывать прочие приложения Office и другие программы, которые поддерживают Visual Basic для приложений (VBA). Например, вы можете записать макрос, который сначала обновляет таблицу в Excel, а затем открывает Outlook для ее отправки по электронной почте.

Макросы и средства VBA находятся на вкладке Разработчик, которая по умолчанию скрыта, поэтому сначала нужно включить ее.

Перейдите в раздел настройки > Excel . Панель инструментов & > ленты.

В категории Настроить ленту в списке Основные вкладки установите флажок Разработчик, а затем нажмите кнопку Сохранить.

Чтобы записать макрос, следуйте инструкциям ниже.

На вкладке Разработчик нажмите кнопку Запись макроса.

В поле Имя макроса введите название макроса. Сделайте имя понятным, чтобы можно было быстро найти нужный макрос.

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

В списке Сохранить в выберите книгу, в которой вы хотите сохранить макрос.

Как правило, макрос сохраняется в указанном расположении книги , но если вы хотите, чтобы макрос был доступен при использовании Excel, выберите пункт Личная книга макросов. Если выбрать команду Личная книга макросов, в Excel будет создана скрытая личная книга макросов (личное. XLSB), если он еще не существует, и сохранение макроса в этой книге. Книги в этой папке открываются автоматически при запуске Excel, а код, хранящийся в личной книге макросов, будет указан в диалоговом окне Макрос, которое описано в следующем разделе.

Чтобы назначить сочетание клавиш для запуска макроса, в поле Сочетание клавиш введите любую строчную или прописную букву. Рекомендуется использовать сочетания клавиш, которые еще не назначены другим командам, так как они будут переопределять совпадающие с ними стандартные сочетания клавиш в Excel, пока открыта книга, содержащая макрос.

В поле Описание при необходимости введите краткое описание действий макроса.

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

Чтобы начать запись макроса, нажмите кнопку ОК.

Выполните действия, которые нужно записать.

На вкладке Разработчик щелкните Остановить запись.

Работа с макросами, записанными в Excel

На вкладке Разработчик щелкните Макросы, чтобы просмотреть макросы, связанные с книгой. При этом откроется диалоговое окно Макрос.

Примечание: Макросы нельзя отменить. Прежде чем впервые запускать записанный макрос, сохраните книгу или создайте ее копию, чтобы предотвратить внесение нежелательных изменений. Если вас не устраивают результаты выполнения макроса, вы можете закрыть книгу, не сохраняя ее.

Ниже приведены дополнительные сведения о работе с макросами в Excel.

Узнайте, как включать и отключать макросы в Excel для Mac.

Если книга содержит макрос VBA, который нужно использовать где-либо еще, этот модуль можно скопировать в другую книгу с помощью редактора Microsoft Visual Basic.

Назначение макроса объекту, фигуре или графическому элементу

На листе щелкните правой кнопкой мыши объект, рисунок, фигуру или элемент, которому нужно назначить существующий макрос, а затем выберите команду назначить макрос.

В поле Назначить макроса выберите макрос, который вы хотите назначить.

Вы можете назначить макрос значку и добавить его на панель быстрого доступа или ленту.

Вы можете назначать макросы формам и элементам ActiveX на листе.

Открытие редактора Visual Basic

На вкладке Разработчик щелкните Visual Basic или выберите Сервис > Макрос > Редактор Visual Basic.

Узнайте, как найти справку по элементам Visual Basic.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Get expert help now

Don’t have time to figure this out? Our expert partners at Excelchat can do it for you, 24/7.

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

1. Скрыть промежуточные итоги сводной таблицы

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

Для начала вы выделите любую ячейку из сводной таблицы, а затем запустите этот макрос.

2. Обновить все сводные таблицы

Супер быстрый способ, чтобы обновить все сводные таблицы.

Просто запустите этот макрос, и все сводные таблицы в вашей книге будут обновлены в один клик.

3. Создание сводной таблицы

Выполните этот шаг за шагом руководство для создания сводной таблицы с помощью VBA.

4 . Автоматическое обновление диапазона сводной таблицы

Если таблицы Excel не используются, этот макрос можно использовать для обновления диапазона сводных таблиц.

5. Отключить / Включить получение сводных данных

Чтобы отключить / включить функцию GetPivotData вам нужно использовать опцию Excel, с помощью этого макроса вы можете сделать это в один клик.

6. Создание обратной совместимости сводной таблицы

Если вы все еще используете Excel 2003, вы можете с несовместимостью PivotTables между Excel 2003 и более поздними версиями. Например, в более поздних версиях Excel в сводных таблицах может иметь более 16384 полей столбцов и более 1000000 уникальных элементов данных. Excel 2003 может иметь только 256 полей столбцов и 32500 уникальных элементов данных. Для решения проблем с совместимостью, Microsoft инициировала концепцию режима совместимости.

Если вы не в режиме совместимости (то есть вы работаете с XLSX или XLSM файлами), то при создании сводной таблицы, объекты открытии файлов в Excel 2003 уничтожаются.

Чтобы избежать этого фиаско вручную, Excel 2007 и 2010 пользователи должны пройти через эти шаги:

2. Сохраните файл как файл XLS.

3. Закройте файл.

4. Откройте его снова.

5. Начало создания сводной таблицы.

В качестве альтернативы можно использовать макрос, который автоматически запускает сводную таблицу в таблице в версии Excel 2003 — даже если вы не в режиме совместимости!

Если записать макрос при создании сводной таблицы в Excel 2007 или Excel 2010, записи макросов генерируют код, который имеет несколько аргументов. Одним из аргументов является свойство Version. Как следует из названия, свойство Version определяет версию Excel сводной таблицы, в которой она создана. Дело в том, что вы можете изменить версию в коде, чтобы заставить Excel создать сводную таблицу, которая будет работать с Excel 2003.

Вот список различных версий:

xlPivotTableVersion2000 — Excel 2000

xlPivotTableVersion10 — Excel 2002

xlPivotTableVersion11 — Excel 2003

xlPivotTableVersion12 — Excel 2007

xlPivotTableVersion14 — Excel 2010

Вот пример макроса, который создает сводную таблицу с помощью Range ("A3: N86") на Лист1 в качестве исходных данных.

Обратите внимание, что мы изменили свойства Version и DefaultVersion к xlPivotTable Version11. Это гарантирует, что PivotTable будет работать в Excel 2003.

7. Обновление всех сводных таблиц книги

Это не редкость, чтобы иметь несколько сводных таблиц в одной книге. Много раз, эти сводные таблицы ссылаются на данные, что требует их обновления. Этот макрос, обновит все сводные таблицы по первому требованию.

Этот макрос — перебирает рабочие листы, а затем перебирает PivotTables. На каждом цикле, макрос обновляет сводную таблицу.

8. Создание «описи» всех сводной таблицы книги

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

При создании сводной таблицы переменной, вы выставляете все свойства сводных таблиц—наименование, место нахождения, индекс кэша, и так далее. В этом макросе цикл по каждой сводной таблице извлекает определенные свойства на новый лист.

9. Создаем все сводные таблицы, используя тот же кэш данных

Если вы работаете со сводными таблицами, у вас есть необходимость анализировать один набор данных несколькими способами. В большинстве случаев этот процесс требует создания отдельных сводных таблиц из одного источника данных.

Проблема заключается в том, что каждый раз, когда вы создаете сводную таблицу, вы храните снимок источника данных в кэш повороте. Каждый кэш поворот, который создается увеличивает использование памяти и размер файла. Побочный эффект такого поведения является то, что ваша электронная таблица раздувается, для этого создаём PivotTables.

Начиная с Excel 2007, Microsoft построен в автоматическом сводном кэше алгоритме обмена, который распознает, когда вы создаете сводную таблицу из того же источника, что и существующая сводная таблица. Это снижает случаи создания избыточных кэшев.

В дополнение к уменьшению размера файла, есть и другие преимущества для обмена кэш поворота:

• Вы можете обновить одну сводную таблицу и все другие, которые используют общий кэш поворота.

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

• При добавлении вычисляемого элемента в одну сводную таблицу, он проявляется в других.

• Любая группировка или разгруппировка выполнения влияет на все PivotTables разделяющих один и тот же кэш.

В этом примере, все сводные таблицы в кэш поворота используют PivotTable1 на всех листах

10. Скрываем все промежуточные итоги в сводной таблицe

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

Вы можете вручную скрыть все промежуточные итоги сразу перейдя в ленте и выбрав

Работа со Сводными ТаблицамиДизайнМакетПромежуточныеНе Показывать Промежуточные Итоги. Но опять же, если вы строите автоматизированный процесс, который постоянно манипулирует сводных таблиц без ручного вмешательства, вы можете предпочесть вариант макроса. Как это работает

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

Это верно, Excel передает массив с 12 ложными параметрами. Есть 12 экземпляров false, потому что есть двенадцать видов промежуточных итогов — сумма, среднее, количество, минимум и максимум и т.д. Итак, когда вы выключите промежуточные итоги во время записи макроса в Excel наборы всех возможных видов значение false.

Альтернативный способ отключить "итоги" — это первый комплект из 12 промежуточных итогов в True. Это автоматически заставляет других 11 видов в false. Мы после этого устанавливаем так же Итого в ложь, скрывая все промежуточные итоги. В этот кусок кода, мы создаем первый промежуточный итог True, а затем установить его в false. Это удаляет промежуточный итог для области.

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

11. Изменяем названия данных всех полей сводной

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

В идеале, название каждого элемента данных соответствует имени поля из источника данных. К сожалению, сводные таблицы не позволят вам получить точное название имени полей данных, как в поле источника данных. Чтобы решить это, нужно добавить пробел в конце имени поля. Программа Excel рассматривает имя поля (с пробелом), чтобы отличаться от имени поля источника данных, так что это позволяет. Косметически читателям таблиц не заметить пробел после имени.

Этот макрос использует этот способ, чтобы переименовать поля данных. Он перебирает каждое поле данных в сводной таблице, а затем сбрасывает каждый Заголовок, чтобы соответствовать его полю в источнике данных, плюс пробел.

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

При создании сводной таблицы Excel, по умолчанию, суммирует данные для подсчета или суммирования элементов. Если все ячейки в столбце содержат числовые данные, Excel выбирает сумму. Если поле, которые вы добавляете содержат пробел или текст, Excel выбирает Count.

Хотя это кажется, имеет смысл, во многих случаях, поля, которые должны быть суммированы на законных основаниях содержат пробелы. В этих случаях мы вынуждены вручную изменять тип вычислений.

Макрос в этом разделе направлен на оказание помощи путем автоматической установки типа расчета каждого элемента данных для Sum.

Этот макрос выполняет цикл по каждому полю данных в сводной таблице и изменяет свойство функции к xlSum. Вы можете изменить этот макрос, чтобы использовать один из вариантов расчета: xlCount, xlAverage, xlMin, xlMax, и так далее. Когда вы идете в окно кода и введите pf.Function =, вы видите выпадающий список показывает вам все ваши варианты.

13. Применить числовой формат для всех элементов данных

Макрос изложенные в данном разделе просматривает форматирование чисел в исходных данных сводной таблицы и автоматически применяет соответствующее форматирование к каждому полю.

Перед выполнением этого кода, нужно убедиться, что

— Исходные данные для сводной таблицы доступны. Макрос должен увидеть их, чтобы захватить правильное форматирование чисел.

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

Этот макрос использует свойство PivotTable SourceData, чтобы найти расположение источника данных. Затем он перебирает каждый столбец в источнике, захватив имя заголовка и числовой формат первого значения по каждой колонке. После того, как у него есть эта информация, макрос определяет, будут ли поля данных соответствовать оценочному столбцу. Если он находит совпадение, форматирование применяется к этому полю данных.

14. Сортировка полей сводной в алфавитном порядке

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

Этот макрос сбрасывает сортировку на все поля, гарантируя, тем самым, что новые данные встанут на место. Идея заключается в том, чтобы запускать макрос каждый раз при обновлении сводной таблицы. В коде, мы проходим через каждое поле данных в сводной таблице и сортируем каждое из них

15. Применить пользовательскую сортировку к элементам данных

В некоторых случаях, требуется применить пользовательскую сортировку элементов данных в сводной таблице.

Вы можете автоматизировать пользовательскую сортировку ваших полей с помощью свойства положения объекта PivotItems. С помощью свойства Position, можно присвоить номер позиции, который определяет порядок, в котором вы хотели бы видеть каждый элемент поворота.

В этом примере кода мы сначала указываем на поле сводной области в Pvt1 сводной таблице. Затем мы перечисляем каждый элемент вместе с номером позиции, указывающей порядок сортировки, который нам нужен.

16. Ставим защиту на сводную таблицу

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

PivotTable объект предоставляет несколько свойств, которые позволяют ограничить различные функции и компоненты сводной таблицы:

EnableWizard: Установка этого свойства в значение False отключает PivotTable Tools контекстное меню, которое обычно активизируется при щелчке внутри сводной таблицы. В Excel 2003, этот параметр отключает мастер диаграмм и сводных таблиц Pivot.

EnableDrilldown: Установка этого свойства в ЛОЖЬ пользователей предотвращает от получать подробные данные, дважды щелкнув поле данных.

EnableFieldList: Установка этого свойства в ЛОЖЬ пользователей препятствует активации списка полей или перемещения поворотных полей вокруг.

EnableFieldDialog: Установка этого свойства в значение False отключает способность пользователей изменять поле поворота с помощью диалогового окна Параметры Значение поля.

PivotCache.EnableRefresh: Установка этого свойства в значение False отключает возможность обновления сводной таблицы.

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

17. Применять ограничения сводного поля

Макрос, описанный в этом разделе, демонстрирует некоторые параметры защиты, доступных через VBA.

Объект сводных полей предоставляет несколько свойств, которые позволят ограничить различные функции и компоненты сводной таблицы.

DragToPage: Установка этого свойства в значение false освобождает пользователей от перетаскивания любое поле сводной таблицы в область фильтра отчета в сводной таблице.

DragToRow: Установка этого свойства в значение false освобождает пользователей от перетаскивания любое поле сводной таблицы в области строк сводной таблицы.

DragToColumn: Установка этого свойства в значение false освобождает пользователей от перетаскивания любое поле сводной таблицы в область столбцов сводной таблицы.

DragToData: Установка этого свойства в значение false освобождает пользователей от перетаскивания любое поле сводной таблицы в область данных сводной таблицы.

DragToHide: Установка этого свойства в значение false освобождает пользователей от перетаскивания полей сводной от сводной таблицы. Оно также предотвращает использование контекстного меню, чтобы скрыть или удалить поля сводной.

EnableItemSelection: Установка этого свойства в значение false отключает раскрывающиеся списки на каждом поле сводной таблицы.

Вы можете установить любой или все из этих свойств самостоятельно либо True, либо false. В этот макросе, мы применяем все ограничения к сводной таблице.

18. Автоматическое удаление листов с детализацией сводной

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

Это особенно проблема, когда вы распространяете отчеты сводных таблиц для пользователей, которые часто смотрят детали. Нет никакой гарантии, что они будут помнить, что нужно очистить листы. Хотя эти листы, вероятно, не будут вызывать проблемы, они могут загромождать рабочую книгу.

Этот макрос поможет автоматически удалять листы с деталицией сводной.

Основная предпосылка этого макроса на самом деле очень простая. Когда пользователь нажимает кнопку для деталей, макрос просто переименовывает выходной лист так, чтобы первые десять символов являлись PivotDrill. Затем рабочая книга закрывается, макрос находит любой лист, который начинается с PivotDrill и удаляет его.

Реализация действительно немного сложнее, потому что вы по существу должны иметь две части кода. Одна часть идет в случае Worksheet_BeforeDoubleClick, в то время как другая часть переходит в событие Workbook_BeforeClose.

19. Печать сводной таблицы для каждого элемента фильтра

Сводные таблицы обеспечивают отличный механизм для анализа больших наборов данных в файлы для печати. Вы можете создать отчет сводной таблицы, в комплекте скопления и анализа, и затем поместить поле (например, регион) в фильтр отчета. С фильтром отчета, вы можете выбрать каждый элемент данных один раз, а затем распечатать отчет сводной таблицы.

Макрос в этом разделе показывает, как автоматически перебрать все значения в фильтр отчета и вывести на печать.

В объектной модели Excel, в раскрывающемся списке Фильтр отчета известен как PageField. Для печати сводной таблицы каждого элемента данных в фильтре отчета, мы должны перебрать коллекцию PivotItems объекта PageField. Когда мы делаем цикл, то динамически меняем выбор в фильтре отчета, а затем используем метод ActiveSheet.PrintOut для печати целевого диапазона.

20. Создание нового файла для каждого элемента фильтра

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

Макрос в этом разделе показывает, как автоматически перебирать все значения в отчете фильтра и экспортирует в новую книгу.

В объектной модели Excel, в раскрывающемся списке Фильтр отчета известен как PageField. Для печати сводной таблицы для каждого элемента данных в фильтре отчета, потребности макросов для обхода коллекции PivotItems объекта PageField. По мере того как Макрос циклы, она должна динамически изменять выбор в фильтре отчета, а затем экспортировать отчет сводной таблицы в новую книгу.

21. Готовим диапазон данных для сводной таблицы

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

В идеале, эти данные будут отформатированы в более табличном формате.

Есть бесчисленное множество методов, которые можно использовать, чтобы транспонировать весь диапазон данных. Макрос в этом разделе обеспечивает простой способ автоматизировать эту задачу.

Несколько диапазонов консолидации может выводить только три базовые поля: строка, столбец и стоимость. Поле Row всегда состоит из первого столбца в источнике данных. Поле Колонна состоит из всех заголовков столбцов после первого столбца в источнике данных. Поле Значение состоит из значений в источнике данных.

Из-за этого, вы можете иметь только один столбец измерения. Чтобы понять это, взгляните на рисунке 6-6. Обратите внимание, что первый столбец, по существу, каскадный столбец, состоящий из двух измерений данных: Market и категории. Это потому, что сводная таблица диапазона консолидации может обрабатывать только одно измерение поля.

Вы можете транспонировать набор данных со сводной таблицей множественного диапазона консолидации. Ручные шаги, чтобы сделать это,

1. Нажмите Alt + D + P для вызова мастера Excel 2003 сводной таблицы.

2. Выберите опцию для нескольких диапазонов консолидации и нажмите кнопку Далее.

3. Выберите опцию Поля страниц, а затем нажмите кнопку Далее.

4. Определить диапазон, в котором вы работаете, и нажмите кнопку Готово, чтобы создать сводную таблицу.