Як в Excel 2010 синхронізувати дві зведені таблиці за допомогою одного списку

Звіт, показаний на рис. 11.15, містить дві зведені таблиці. Кожна зведена таблиця має поле сторінок, що дозволяє вибирати ринок збуту. Проблема в тому, що кожен раз при виборі ринку в поле сторінок однієї зведеної таблиці вам доводиться вибирати той же ринок в поле сторінок інший зведеної таблиці.
Синхронізація даних двох таблиць на етапі аналізу даних не є великою проблемою, але існує ймовірність, що ви або ваші клієнти все ж забудуть її виконати.

Рис 11.15. Дві зведені таблиці містять поля сторінок, які виконують фільтрацію даних по ринках. Для аналізу даних окремого ринку потрібно синхронізувати обидві зведені таблиці

Один із способів синхронізації цих зведених таблиць полягає в використанні списку. Ідея полягає в запису макросу, який вибирає потрібний ринок з поля Ринок збуту в обох таблицях. Потім потрібно створити спливаюче меню і заповнити його назвами ринків з двох зведених таблиць. І нарешті, записаний макрос необхідно змінити для фільтрації обох зведених таблиць, використовуючи значення із списку.

Для вирішення цього завдання потрібно виконати наступні дії.

  1. Створіть новий макрос і надайте йому ім’я SynchMarkets. Коли почнеться запис, виберіть у полі Ринок збуту обох зведених таблиць ринок збуту Каліфорнія і зупиніть запис.
  2. Перейдіть на екрані палітру елементів управління форми і додайте на робочий лист розкривається меню.
  3. Створіть жорстко певний список всіх ринків зведеної таблиці. Зауважте, що першим елементом списку вказується значення (Всі). Ви повинні включити цей елемент, якщо хочете мати можливість вибирати в спадному меню всі ринки. На цьому етапі звіт зведеної таблиці повинен виглядати так, як показано на рис. 11.16.
  4. Рис 11.16. У вашому розпорядженні є всі необхідні засоби: макрос, що змінює поле Ринок збуту обох зведених таблиць, спливаюче меню і список всіх ринків збуту, що містяться в зведеній таблиці

  5. Клацніть правою кнопкою миші на списку і в контекстному меню виберіть команду Формат об’єкта (Format Control), щоб виконати початкову настройку елемента управління. Спочатку задайте вихідний діапазон значень списку, використовуваного для заповнення меню, що розкривається, як показано на рис. 11.17. В даному випадку мова йде про список ринків, створеному вами в п.3. Потім вкажіть клітинку, яка буде показувати порядковий номер обраного елемента (в даному прикладі такою є осередок Н1). Клацніть на кнопці ОК.

    Мал. 11.17. Налаштування меню, що розкривається повинні вказувати на список ринків як на вихідний діапазон значень, а в якості точки зв’язування – визначати осередок Н1

    Тепер у вас з’явилася можливість вибирати в спадному меню ринок, а також визначати пов’язаний з ним порядковий номер в осередку H1 (рис. 11.18). Виникає питання: навіщо замість реального імені ринку використовується його індексне значення? Тому що розкривається меню повертає не ім’я, а номер. Наприклад, при виборі в спадному меню імені Шарлотта воно повертає в осередок H1 значення 5. Це означає, що Шарлотта є п’ятим елементом списку.

    Мал. 11.18. Спливаюче меню тепер заповнюється назвами ринків з висновком порядкового номера обраного ринку в осередку H1

    Щоб використовувати порядковий номер замість імені ринку, вам слід передати його з допомогою функції ІНДЕКС (INDEX). Функція індекс перетворює порядковий номер в распознаваемое значення. Введіть функцію ІНДЕКС, яка перетворює порядковий номер з осередку H1 в значення.

  6. Функція ІНДЕКС вимагає для нормальної роботи два аргументи. Перший аргумент являє діапазон значень списку. У більшості випадків ви будете використовувати таку саму частоту, яким заповнюється спливаюче меню. Другий аргумент – це порядковий номер. Якщо порядковий номер вводиться в осередку (наприклад, в осередку H1, як на рис. 11.19), то можете просто послатися на цей осередок.
  7. Мал. 11.19. Функція індекс в осередку 11 перетворює порядковий номер в осередку H1 в значення. В кінцевому рахунку ви будете використовувати значення в осередку 11 для зміни макросу

  8. Відредагуйте макрос SynchMarkets, використовуючи значення в осередку і замість жорстко заданого значення. Перейдіть на вкладку Розробник і клацніть на кнопці Макроси (Macros). На екрані з’явиться діалогове вікно, показане на рис. 11.20. Виберіть в ньому макрос SynchMarkets і клацніть на кнопці Змінити (Edit).

    Мал. 11.20. Щоб отримати доступ до VBA-коду макросу, виберіть макрос SynchMarkets і клацніть на кнопці Змінити

    При записи макросу ви вибрали в обох зведених таблицях ринок збуту Каліфорнія з поля Market (Ринок збуту). Як видно з рис. 11.21, ринок Каліфорнія тепер жорстко заданий в VBA-коді макросу. Замініть значення «Каліфорнія» виразом ActiveSheet.Range ( «I1»). Value, яке посилається на значення в комірці I1.

    Мал. 11.21. Ринок збуту Каліфорнія жорстко заданий в VBA-коді записаного макросу

    На цьому етапі код макросу повинен виглядати так, як показано на рис. 11.22. Після зміни макросу закрийте редактор Visual Basic і поверніться до електронної таблиці.

  9. Мал. 11.22. Замініть значення «Каліфорнія» виразом Activesheet.Range ( «I1»). Value і закрийте редактор Visual Basic

  10. Залишилося тільки забезпечити виконання макросу при виборі ринку збуту в спадному меню. Клацніть правою кнопкою миші на спадному меню і виберіть параметр Призначити макрос. Виберіть макрос SynchMarket і клацніть на кнопці ОК.
  11. Сховайте рядки і стовпці з полями сторінок в зведених таблицях, а також створений вами список ринків і формули індексу.

На рис. 11.23 показаний остаточний результат. Ви отримали призначений для користувача інтерфейс, що дозволяє клієнтам вибирати ринок збуту в обох зведених таблицях за допомогою єдиного списку.

Мал. 11.23. Звіт зведеної таблиці, готовий до використання

При виборі в списку нового елемента розміри стовпців автоматично змінюються, щоб вмістити все відображаються в них дані. Подібна поведінка програми порядком набридає при форматуванні шаблону робочого листа. Можна запобігти його, якщо клацнути на зведеній таблиці правою кнопкою миші і вибрати команду Параметри зведеної таблиці (PivotTable Options). На екрані з’явиться діалогове вікно, в якому необхідно скинути прапорець Автоматично змінювати ширину стовпців при оновленні (Autofit Column Widths on Update).

Ссылка на основную публикацию