Урок 1Использование макросов VBA для простой автоматизации: запись макросов, редактирование модуля, безопасное выполнение, события книги (Workbook_Open, Worksheet_Change)Автоматизируйте повторяющиеся задачи с помощью макросов VBA, от записи до редактирования кода в модулях. Изучите практики безопасного выполнения, используйте события книги, такие как Workbook_Open и Worksheet_Change, и создайте надежную автоматизацию для обновления и форматирования задач.
Recording and running basic macrosEditing macros in standard modulesUsing Workbook_Open for startup tasksUsing Worksheet_Change for input logicMacro security and safe executionAssigning macros to buttons and shapesУрок 2Автоматизация обновления: обновление Power Query и сводных таблиц с помощью VBA или кнопокАвтоматизируйте обновление Power Query и сводных таблиц с помощью VBA и элементов управления интерфейса. Создайте кнопки и простые макросы для последовательного обновления нескольких объектов, обработки ошибок и обеспечения того, чтобы пользователи всегда видели актуальные данные.
Manual vs automated refresh optionsVBA to refresh all queries at onceRefreshing PivotTables with macrosRefresh buttons on dashboardsHandling refresh errors and loggingУрок 3Функции текста и дат: TEXT, DATE, EOMONTH, MONTH, YEAR, FORMAT для отчетных периодовИспользуйте функции текста и дат для создания надежных отчетных периодов. Работайте с TEXT, DATE, EOMONTH, MONTH, YEAR и форматированием для создания меток месяцев, фискальных периодов и динамических сводок на основе дат для панелей и повторяющихся отчетов.
Building dates from components with DATEMonth, year, and EOMONTH calculationsTEXT for custom period labelsRolling monthly and year-to-date rangesHandling fiscal vs calendar periodsУрок 4Лучшие практики версионирования, резервного копирования и документирования источников данных в книгеВнедрите лучшие практики версионирования книг, резервного копирования и документирования источников данных внутри книги. Отслеживайте изменения источников данных, ведите журнал изменений и встраивайте четкие заметки, чтобы будущие пользователи понимали шаги обновления и зависимости.
File naming and versioning conventionsBackup strategies and storage locationsMaintaining a workbook change logDocumenting external data connectionsAnnotating queries and key formulasУрок 5Продвинутые формулы: XLOOKUP/VLOOKUP, INDEX/MATCH, SUMIFS, COUNTIFS, IF/IFS, вложенная логикаПрименяйте продвинутые формулы, такие как XLOOKUP, VLOOKUP, INDEX/MATCH, SUMIFS, COUNTIFS, IF и IFS. Создавайте вложенные логические выражения, корректно обрабатывайте ошибки и проектируйте надежные цепочки поиска для сложных моделей отчетности.
XLOOKUP vs VLOOKUP comparisonINDEX/MATCH for flexible lookupsSUMIFS and COUNTIFS with criteriaIFS and nested logical structuresError handling with IFERROR or IFNAУрок 6Проектирование структуры книги: отдельные листы для Продаж, Кадров, Финансов, Панели, Словаря данныхСпроектируйте четкую структуру книги с выделенными листами для Продаж, Кадров, Финансов, Панели и Словаря данных. Установите стандарты именования, средства навигации и разделение слоев сырых данных, подготовки и представления.
Separating data, staging, and reportsDedicated sheets for Sales, HR, FinanceDashboard layout and navigation aidsCentral data dictionary worksheetSheet naming and tab color schemesУрок 7Основы Power Query: импорт, очистка, объединение нескольких файлов отделовОсвойте Power Query для импорта, очистки и объединения файлов отделов. Настройте подключения, примените шаги трансформации, объедините и добавьте таблицы, загрузите результаты в модели Excel, сохраняя запросы обновляемыми и хорошо документированными.
Connecting to folders and workbooksCleaning and shaping raw tablesMerging and appending departmental filesManaging query steps and errorsLoading queries to tables or data modelDocumenting query logic and sourcesУрок 8Проверка данных и контролируемый ввод: выпадающие списки, списки, предупреждения об ошибках для ежемесячной вставкиКонтролируйте ввод пользователя с помощью правил проверки данных, списков и предупреждений об ошибках. Создайте выпадающие списки для шаблонов ежемесячного вставки, ограничьте недопустимые записи и спроектируйте дружелюбные сообщения, которые направляют пользователей, защищая формулы и структуры.
Creating list-based dropdown controlsCustom validation formulas for rulesInput messages and error alertsValidating monthly paste-in templatesLocking structure while allowing inputУрок 9Вычисляемые столбцы и меры: столбцы структурированных таблиц против мер сводных таблицРазличайте вычисляемые столбцы в таблицах от мер в сводных таблицах и модели данных. Изучите, когда использовать каждое, как они влияют на производительность, и как проектировать повторно используемые вычисления для последовательной отчетности в сводных таблицах.
Creating calculated columns in tablesDefining measures in the Data ModelRow context vs filter context basicsChoosing between column and measureReusing measures across PivotTablesУрок 10Использование таблиц Excel и именованных диапазонов для динамических диапазонов и структурированных ссылокИспользуйте таблицы Excel и именованные диапазоны для создания динамических диапазонов и структурированных ссылок. Преобразуйте диапазоны в таблицы, используйте имена таблиц в формулах и определяйте именованные диапазоны, которые обновляются автоматически при росте данных.
Converting ranges into Excel TablesStructured references in formulasTotal rows and table-based summariesDynamic named ranges with formulasTables feeding PivotTables and chartsУрок 11Условная логика для флагов и категорий: IF с поиском, SWITCH, CHOOSEИспользуйте условную логику для создания флагов и категорий, которые управляют анализом. Комбинируйте IF с функциями поиска и применяйте SWITCH и CHOOSE для упрощения вложенной логики, делая модели проще для аудита и корректировки со временем.
Reviewing IF and nested IF patternsIF with XLOOKUP or VLOOKUP flagsUsing SWITCH for multi-condition logicUsing CHOOSE for scenario selectionAuditing and testing logical formulasУрок 12Работа с большими диапазонами эффективно: формулы массивов, поведение разлива, функция LETИзучите обработку больших диапазонов с динамическими массивами, областями разлива и функцией LET. Поймите соображения производительности, обработку ошибок и как заменить устаревшие формулы массивов современными, поддерживаемыми шаблонами вычислений.
Legacy CSE array formulas vs dynamic arraysUnderstanding and controlling spill rangesUsing LET to simplify complex formulasCombining LET with FILTER and SORTPerformance tips for large array ranges