Урок 1Використання макросів VBA для простої автоматизації: запис макросів, редагування модулів, безпечне виконання, події робочої книги (Workbook_Open, Worksheet_Change)Автоматизуйте повторювані завдання за допомогою макросів VBA, від запису до редагування коду в модулях. Дізнайтеся про практики безпечного виконання, використовуйте події робочої книги, такі як Workbook_Open та Worksheet_Change, і створюйте надійну автоматизацію для оновлення та форматування завдань.
Запис та запуск базових макросівРедагування макросів у стандартних модуляхВикористання Workbook_Open для стартових завданьВикористання Worksheet_Change для логіки введенняБезпека макросів та безпечне виконанняПризначення макросів кнопкам та фігурамУрок 2Автоматизація оновлення: оновлення Power Query та PivotTables за допомогою VBA або кнопокАвтоматизуйте оновлення Power Query та PivotTables за допомогою VBA та елементів керування інтерфейсу. Створіть кнопки та прості макроси для послідовного оновлення кількох об'єктів, обробки помилок та забезпечення того, щоб користувачі завжди бачили актуальні дані.
Ручні проти автоматизованих варіантів оновленняVBA для одночасного оновлення всіх запитівОновлення PivotTables за допомогою макросівКнопки оновлення на панелях приладівОбробка помилок оновлення та ведення журналуУрок 3Функції тексту та дати: TEXT, DATE, EOMONTH, MONTH, YEAR, FORMAT для звітних періодівВикористовуйте функції тексту та дати для створення надійних звітних періодів. Працюйте з TEXT, DATE, EOMONTH, MONTH, YEAR та форматуванням для створення міток місяців, фіскальних періодів та динамічних підсумків на основі дат для панелей приладів та повторюваних звітів.
Створення дат з компонентів за допомогою DATEРозрахунки місяця, року та EOMONTHTEXT для власних міток періодівКотокові місячні та річні діапазониОбробка фіскальних проти календарних періодівУрок 4Найкращі практики версіонування, резервного копіювання та документування джерел даних у робочій книзіЗапровадьте найкращі практики версіонування робочих книг, резервного копіювання та документування. Відстежуйте зміни джерел даних, ведіть журнал змін та вбудовуйте чіткі примітки, щоб майбутні користувачі розуміли кроки оновлення та залежності.
Конвенції іменування файлів та версіонуванняСтратегії резервного копіювання та місця зберіганняВедення журналу змін робочої книгиДокументування зовнішніх з'єднань данихАнотування запитів та ключових формулУрок 5Просунуті формули: XLOOKUP/VLOOKUP, INDEX/MATCH, SUMIFS, COUNTIFS, IF/IFS, вкладені логічні виразиЗастосовуйте просунуті формули, такі як XLOOKUP, VLOOKUP, INDEX/MATCH, SUMIFS, COUNTIFS, IF та IFS. Створюйте вкладені логічні вирази, граційно обробляйте помилки та проектуйте надійні ланцюжки пошуку для складних моделей звітності.
Порівняння XLOOKUP проти VLOOKUPINDEX/MATCH для гнучких пошуківSUMIFS та COUNTIFS з критеріямиIFS та вкладені логічні структуриОбробка помилок за допомогою IFERROR або IFNAУрок 6Проектування структури робочої книги: окремі аркуші для Продажів, Кадрів, Фінансів, Панелі приладів, Словника данихСпроектуйте чітку структуру робочої книги з виділеними аркушами для Продажів, Кадрів, Фінансів, панелей приладів та словника даних. Встановіть стандарти іменування, засоби навігації та розділення шарів сирих даних, підготовки та презентації.
Розділення даних, підготовки та звітівВиділені аркуші для Продажів, Кадрів, ФінансівРозташування панелі приладів та засоби навігаціїЦентральний аркуш словника данихІменування аркушів та схеми кольорів вкладокУрок 7Основи Power Query: імпорт, очищення, об'єднання кількох файлів відділівОволодійте Power Query для імпорту, очищення та об'єднання файлів відділів. Налаштуйте з'єднання, застосуйте кроки трансформації, об'єднайте та додайте таблиці, завантажте результати в моделі Excel, зберігаючи запити оновлюваними та добре документованими.
З'єднання з папками та робочими книгамиОчищення та формування сирих таблицьОб'єднання та додавання файлів відділівКерування кроками запитів та помилкамиЗавантаження запитів у таблиці або модель данихДокументування логіки запитів та джерелУрок 8Валідація даних та контрольоване введення: випадаючі списки, списки, попередження про помилки для щомісячного вставленняКонтролюйте введення користувачів за допомогою правил валідації даних, списків та попереджень про помилки. Створіть випадаючі списки для шаблонів щомісячного вставлення, обмежте некоректні записи та спроектуйте дружні повідомлення, що спрямовують користувачів, захищаючи формули та структури.
Створення випадаючих керувань на основі списківВласні формули валідації для правилПовідомлення про введення та попередження про помилкиВалідація шаблонів щомісячного вставленняБлокування структури при дозволі введенняУрок 9Розраховані стовпці та міри: стовпці структурованих таблиць проти мір PivotРозрізніть розраховані стовпці в таблицях від мір у PivotTables та Моделі даних. Дізнайтеся, коли використовувати кожне, як вони впливають на продуктивність, та як спроектувати повторно використовувані розрахунки для послідовної звітності в поворотних таблицях.
Створення розрахованих стовпців у таблицяхВизначення мір у Моделі данихОснови контексту рядка проти контексту фільтраВибір між стовпцем та міроюПовторне використання мір у PivotTablesУрок 10Використання таблиць Excel та іменованих діапазонів для динамічних діапазонів та структурованих посиланьВикористовуйте таблиці Excel та іменовані діапазони для створення динамічних діапазонів та структурованих посилань. Перетворюйте діапазони на таблиці, використовуйте імена таблиць у формулах та визначайте іменовані діапазони, що автоматично оновлюються з ростом даних.
Перетворення діапазонів у таблиці ExcelСтруктуровані посилання у формулахЗагальні рядки та підсумки на основі таблицьДинамічні іменовані діапазони з формуламиТаблиці, що живлять PivotTables та діаграмиУрок 11Умовна логіка для прапорців та категорій: IF з пошуком, SWITCH, CHOOSEВикористовуйте умовну логіку для створення прапорців та категорій, що керують аналізом. Комбінуйте IF з функціями пошуку та застосовуйте SWITCH та CHOOSE для спрощення вкладеної логіки, роблячи моделі легшими для аудиту та коригування з часом.
Огляд IF та вкладених шаблонів IFIF з прапорцями XLOOKUP або VLOOKUPВикористання SWITCH для логіки з кількома умовамиВикористання CHOOSE для вибору сценаріївАудит та тестування логічних формулУрок 12Робота з великими діапазонами ефективно: масивні формули, поведінка розливу, функція LETДізнайтеся, як обробляти великі діапазони з динамічними масивами, діапазонами розливу та функцією LET. Зрозумійте міркування продуктивності, обробку помилок та як замінити застарілі масивні формули сучасними, підтримуваними шаблонами розрахунків.
Застарілі CSE масивні формули проти динамічних масивівРозуміння та керування діапазонами розливуВикористання LET для спрощення складних формулКомбінування LET з FILTER та SORTПоради щодо продуктивності для великих масивних діапазонів
