ВИРАШЕННЯ ПРОБЛЕМИ, ЩО ПОВ’ЯЗАНІ З ФАЙЛАМИ
Нещодавно клієнт задав наступне запитання: «У контексті, коли декілька відділів працюють над бюджетом, ми розділили нашу модель на кілька різних файлів ... З труднощами, які це означає, тобто більш складні формули та зв’язки між файлами, які слід підтримувати. . Інші працюють інакше? " Звичайно, проблеми з файлами Excel здебільшого виникають у компаніях, які мають децентралізований бюджетний процес.
Галерея пов'язаних файлів Excel
Хоча Excel завжди може дати відповідь на будь-яку проблему (завдяки надзвичайно гнучкому характеру), концепція управління зв’язаними файлами, безсумнівно, є її ахілесовою п’яткою. Більше того, саме ця слабкість спонукає багатьох звернутися до "систем" бюджетування/прогнозування. Вони пропонують спільні інтерфейси та усувають проблеми незалежних файлів. Але чи справді ці часто дорогі системи є найкращим рішенням будь-якої проблеми, пов’язаної з файлами? Ця стаття має на меті надати прості та дуже низькі бюджетні альтернативи, які користувачі Excel часто не помічають.
Коли користувачі Excel створюють пов'язані файли?
Теоретично, НІКОЛИ не слід розділяти шаблон Excel на кілька файлів, якщо:
- Ви більше ніж один користувач, який повинен використовувати модель одночасно тапараметр спільної роботи, включений в Excel недостатньо
- Різні частини шаблону слід надсилати різним одержувачам
- Ваша модель завелика
Чому піддаються творці пов’язаних файлів?
Керування зв’язаними файлами в Excel піддає користувачам кілька потенційних проблем:
- Для перегляду актуальних даних у файлі, який подається в інший файл, обидва файли повинні бути відкритими
- Коли користувач змінює вихідний файл, зокрема, коли він видаляє або модифікує діапазони даних, не відкриваючи модель (файл Excel, який в неї подається), користувач моделі часто опиняється або з формулами, які вже не є повністю правильними (посилання до поганих діапазонів даних) або які повертають відверті повідомлення про помилки #REF! (посилання на діапазони даних, які були видалені)
- Якщо імена аркушів та файлів найменші, це значно збільшить формули і, отже, вагу моделі, збільшуючи тим самим можливість зіткнутися з проблемами продуктивності.
Працюйте належним чином із зовнішніми файлами Excel
Коли різні файли були розділені, щоб дозволити різним користувачам оновлювати різні розділи, а параметр спільної роботи, включений у Excel, не зберігався, ось альтернативи для реалізації. Серед них останні два, безумовно, найцікавіші, особливо найостанніші.
1. Використовуйте тривимірні формули для усунення пов’язаних файлів
Кожен менеджер бюджету міг скласти свій власний бюджет в окремому стандартному файлі Excel, який був би однаковим для кожного менеджера. Потім ми могли б просто скопіювати вміст цих сторінок між початковою та кінцевою точками із файлу консолідації, зокрема 3D формули, що, отже, стане автоматизованим із додаванням цих сторінок. Звичайно, це не буде «динамічним» варіантом. Тому потрібно було б компенсувати це шляхом створення сторінки документації, зберігаючи в пам'яті дати кожної останньої версії цих копійованих таким чином сторінок. Для того, щоб цей процес був ефективним, необхідний певний контроль. Якщо ви можете усунути цю “ручну” роботу, це завжди буде кращим. Тоді слід було б вибрати інший із запропонованих нижче варіантів.
2. Використовуйте іменовані поля у вихідних файлах
Використовувати "іменовані поля”Для полів у вихідному файлі, які використовуються в моделі. Цей варіант, хоч і простий у налаштуванні, має переваги та недоліки. При правильному використанні він добре збереже зв’язані формули, але якщо користувачі почнуть копіювати інформацію з аркуша Excel із зазначеними полями в інші файли Excel, вони можуть почати створювати зв’язки між файлами, які не слід пов’язувати. Тому ми повинні бути надто пильними з цим варіантом. Якщо ви можете визначити пріоритетом будь-який інший із наведених нижче варіантів, завжди буде краще це зробити.
3. Використовуйте “дзеркальні” аркуші у зв’язаних файлах
Створити аркуш “Імпорт даних”У моделі та аркуші“Експорт даних”У вихідному файлі. Кожне з цих листочків має бути дзеркалом іншого. Таким чином, замість того, щоб створити формулу в моделі, яка посилається на комірки із зовнішнього файлу, ми замість цього створимо формули, зв’язані з комірками аркуша імпорту того самого файлу (отже, внутрішнє посилання). Тільки аркуш імпорту матиме посилання на вихідний файл. Таким чином, у комірці F10 аркуша 1 робочої книги 1 буде формула, яка буде посилатися на вміст комірки F10 аркуша 1 робочої книги 2. Це значно полегшить управління та оновлення таких файлів.
Приклад створення дзеркальних аркушів
Наприклад, тут ми маємо дані з вихідного файлу (робоча книга 2):
І ось у нас є наш аркуш імпорту нашої моделі (робочий зошит 1). Зверніть увагу, що в комірці C8 ми просто маємо формулу =, яка стосується точно тієї самої комірки, тобто C8, але з другої книги.
Щоб заповнити вкладку моделі, яка використовується для розрахунку продажів менеджера A, нам слід уникати створення формули, яка безпосередньо посилається на вихідний файл, з усіх вищезазначених причин. Тут зауважте, що чим довша назва книги з вихідними даними та назва вкладок цієї книги, тим важчою буде формула як з точки зору ваги, так і розуміння.
Щоб заповнити цей розділ, скоріше просто зверніться до розрахунку на вкладці Імпорт.
На додаток до полегшення формул, якщо людина коли-небудь видаляла або змінювала комірки в Workbook 2, не відкриваючи Workbook 1, вона, ймовірно, зможе побачити помилки, створені на вкладці Експорт, і виправити їх перед цим. Збережіть файл. Таким чином, при відкритті робочої книги 1 формули продовжуватимуть повідомляти правильні значення.
4. Використовуйте програмування VBA для заміни пов'язаних файлів
Також можна було б запрограмувати макрос VBA, який дозволяв би кожного разу, коли користувач натискав кнопку, імпортувати дані з вихідних файлів у модель, і це у значеннях (отже, жодної проблеми з продуктивністю пов'язані занадто жадібні формули). Вам просто потрібно знайти у вашій компанії когось, хто знає, як програмувати на VBA! Але якщо це можливо, це досить ефективне рішення!
5. Використовуйте Power Query (наш улюблений варіант ... безумовно!) Для заміни пов’язаних файлів
Для мене залишається остаточний варіант - використання Power Query, яке дозволяє всім користувачам навіть найменше в Excel імпортувати дані із зовнішніх файлів за допомогою запитів, які потрібно буде лише оновити, щоб отримати найновіші значення. На відміну від мови VBA, такі запити досить легко писати, оскільки, навіть якщо Power Query базується на мові програмування, мові M, вона була розроблена корпорацією Майкрософт з метою вирішення проблеми з низьким кодом. Іншими словами, користувачі можуть створювати запити без необхідності вчитися кодувати. Вони можуть просто натискати різні кнопки та меню, щоб створювати свої запити, і це автоматично генерує код для них. Це не чудово?
Приклад використання Power Query
Імпортуйте дані з файлу, а не посилання на нього
Наприклад, тут спочатку буде імпортувати дані із книги 2 із книги 1.
Потім вам доведеться вибрати вкладку книги 2, яку потрібно імпортувати до книги 1.
Створіть запит на перетворення
Нарешті, натиснувши кілька кнопок і функціональних можливостей, ми виявимо, що перетворюємо дані у добре структуровану таблицю, і ці кроки трансформації будуть записані через застосовані кроки (права панель). Це означає, що під час оновлення запит пройде всі етапи трансформації, і тому, якщо в книзі 2 є нові дані або модифіковані дані, все буде легко інтегровано до книги 1.
Отримати імпортовані дані на вкладці
Тоді можна буде отримати дані в таблиці Excel, створити зведену таблицю або зведену діаграму на основі цих даних або надіслати ці дані в Power Pivot для створення віртуального куба, пов’язавши цю таблицю з іншими таблицями.
Створіть альтернативний запит, щоб досягти того самого результату, що і раніше
Зазвичай за допомогою Power Query ми намагаємось відновити бази даних з "необчислюваними" даними. Натомість для обчислення ми використовуємо вимірювання DAX, Power Pivot або зведені таблиці. Але за потреби в Power Query також можна отримати таблицю з продажами, а не таблицю з цінами та підрахунками одиниць. Це насправді поверне нас до бажаного результату в нашому прикладі.
Зрозумійте перевагу імпортованих даних над даними, отриманими за посиланням
Тут головне зрозуміти це користувач Power Query більше не працює зі зв’язаними даними, а скоріше з ІМПОРТОВАНИМИ даними, які оновлюються на вимогу. Роблячи це, ми повністю усуваємо проблеми пов'язаних файлів. Так само значно покращується розмір файлу, який більше не вимагає складних формул і який стискає дані.
Більше немає причин не використовувати Power Query
Power Query тепер доступний у переважній більшості версій Excel безкоштовно. До версій 2010 та 2013 років необхідно додати додаток, але він доступний у всіх інших пізніших версіях. Power Query повністю змінив спосіб роботи з Excel, оптимізуючи час, необхідний для виконання ручних завдань, а також вирішивши давні проблеми, такі як проблема пов'язаних файлів.!
VIP-супровідний файл для завантаження
Щоб завантажити файл, використаний у цьому посібнику, стати VIP-членом фінансового директора в масках.
Подальшу освіту
Для поглиблення ваших знань з Excel, зокрема завдань, пов’язаних з імпортом, консолідацією та перетворенням даних, ми рекомендуємо наш тренінг Excel - Вступ до Power Query та мови М..
Ось декілька коментарів учнів, які пройшли цей тренінг:
Це справді можливість навчитися та розширити свої знання. Софі Маршан - чудовий тренер, і поєднання відео та вправ допомагає зміцнити навчання. Дякую за все!
Дуже хороший тренінг, застосовний до моєї реальності. Одне з рідкісних утворень, куди ми виїжджаємо з набором інструментів.
Відмінно ! Я вже стежив за Софі за допомогою Twitter або безпосередньо на веб-сайті фінансового директора (у форумі та підказці), і коли дійшло до визначення, яку навчальну організацію взяти, я, природно, подумав про фінансового директора в масках. Я абсолютно не шкодую про свій вибір !
Нарешті я мав досвід нового учня, і, як я очікував, це було дуже позитивно. Поняття добре пояснені та проілюстровані на конкретних прикладах. Дуже корисно мати однакові таблиці даних, щоб мати можливість відтворити приклади. Надані документи у форматі PDF чіткі та добре зроблені.