Інформаційні панелі та звіти Excel: приклад списку

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

На щастя, все це робиться без коду VBA, лише кількох формул і списку.

Щоб створити цей приклад, почніть з трьох необроблених наборів даних — як показано на наступному малюнку — які містять три категорії даних: доходи, % чистого доходу та валову маржу. Кожен набір даних містить окремий рядок для кожного регіону, включаючи рядок для всіх регіонів.

Потім ви додаєте поле зі списком, яке виводить номер індексу вибраного елемента в клітинку P2, як показано на наступному малюнку.

Далі створіть проміжну таблицю, яка складається з усіх формул. У цій проміжній таблиці ви використовуєте функцію ВИБІР Excel, щоб вибрати правильне значення з таблиць необроблених даних на основі вибраного регіону.

У Excel функція ВИБІР повертає значення із зазначеного списку значень на основі вказаного номера позиції. Наприклад, формула CHOOSE(3,”Red”, “Yellow”, “Green”, “Blue”) повертає зелений, оскільки зелений є третім пунктом у списку значень. Формула CHOOSE(1, «Червоний», «Жовтий», «Зелений», «Синій») повертає червоний.

Як ви можете бачити на наступному малюнку, формула ВИБІР отримує номер цільової позиції з комірки P2 (комірки, де поле зі списком виводить номер індексу вибраного елемента), а потім узгоджує цей номер позиції зі списком наданих посилань на клітинку. Посилання на клітинки надходять безпосередньо з таблиці необроблених даних.

У прикладі, показаному на малюнку вище, дані, які будуть повернуті за допомогою цієї формули CHOOSE, становлять 41767. Чому? Оскільки клітинка P2 містить число 3, а третє посилання на клітинку у формулі CHOOSE — це клітинка B9.

Ви вводите формулу ВИБІР такого ж типу в стовпець Jan, а потім копіюєте її, як показано на наступному малюнку.

Щоб перевірити, чи працюють ваші формули, змініть значення комірки P2 вручну, ввівши 1, 2, 3, 4 або 5. Коли формули запрацюють, залишається лише створити діаграми за допомогою проміжної таблиці.

Якщо такі функції Excel, як ВИБІР або ІНДЕКС, вас трохи лякають, не хвилюйтеся. Існують буквально сотні способів використовувати різні комбінації елементів керування формою та функцій Excel для створення інтерактивних звітів. Немає встановлених правил щодо того, які елементи керування формою чи функції Excel потрібно використовувати у моделі.

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