Подключиться к умным таблицам

Вызов команды:
MulTEx -группа Специальные -Работа с запросами -Подключиться к умным таблицам

---

Для работы данной команды необходима установленная надстройка Power Query. В версиях 2010 и 2013 она устанавливается отдельно, в версиях начиная с 2016 - уже встроена в Excel. Подробнее: Power Query - что такое и почему её необходимо использовать в работе?

Чаще всего запросы Power Query создаются из так называемых умных таблиц(Вставка(Insert) -Таблица(Table)). Но иногда необходимо создавать подключения ко всем таблицам в файле. Классический пример: 12 таблиц по одной на каждый месяц года и ко всем 12-ти таблицах необходимо создать подключение. А затем объединить все таблицы в единую и создать сводную. Или похожая задача: в одной книге в однотипных таблицах ведется бюджет компании и для каждого филиала/департамента своя таблица. Необходимо подключиться ко всем таблицам и собрать в единую. Упростить подобные задачи поможет команда Подключиться к умным таблицам. Она создает подключение в Power Query ко всем указанным таблицам и может объединить результат подключения в единую таблицу и выгрузить на отдельный лист в умную или сводную таблицу.
Основная форма содержит вкладки:

• Основные параметры
• Дополнительные параметры

---

ОСНОВНЫЕ ПАРАМЕТРЫ
При запуске команды в список сразу заносятся все умные таблицы, расположенные в книге, с указанием имени таблицы, имени листа, на котором расположена таблица и адресом диапазона таблицы:

Можно выбрать все или только некоторые таблицы для создания подключений и загрузки в Power Query.
Подключения создаются только к тем таблицам, которые отмечены флажками. Поэтому перед созданием подключений лучше убедиться, что флажки сняты для таблиц, подключение к которым создавать не требуется.

Выделить все/снять выделение:
устанавливает флажки на все таблицы в списке, либо снимает флажки со всем таблиц.

Использовать в качестве заголовков _ строку:
по умолчанию таблицы загружаются как есть. Однако при таком подключении в результирующем запросе заголовки как правило выглядят как Column1, Column2, Column3 и т.д. При этом реальные заголовки могут располагаться во 2-ой, 3-ей и т.д. строке. При помощи этой настройки можно "сдвинуть" таблицу вверх на заданное количество строк так, чтобы 2-я или иная строка была использована в качестве заголовков. Для примера возьмем такую таблицу:

Таблица на скрине выше начинается со строки 5. Но для таблицы это её первая строка - т.е. заголовок. В результате в Power Query такая таблица загрузится в следующем виде:

Чтобы сделать заголовками 2-ю строку таблицы(название месяцев - т.е. 2-я строка, не считая заголовка) необходимо указать Использовать в качестве заголовков 2 строку. Первая строка таблицы при этом будет удалена и таблица примет более правильный вид:

Особенно важна данная настройка в случае, когда необходимо впоследствии объединять однотипные таблицы в одну.
Если указать 1 - в качестве заголовков полученного запроса используется первая строка
Если указать 2 - в качестве заголовков полученного запроса используется вторая строка, первая строка при этом удаляется
Если указать 3 - в качестве заголовков полученного запроса используется третья строка, первые две строки при этом удаляются
И т.д.
Если заголовки таблиц изначально корректные и не требуется их заменять - выставляется значение 0.

ДОПОЛНИТЕЛЬНЫЕ ПАРАМЕТРЫ

На данной вкладке указываются действия, которые необходимо выполнить после успешного создания подключений к выбранным таблицам.
После успешного подключения к таблице:
Указывается действие, которое необходимо сделать для каждой выбранной таблицы, если подключение к ней было создано

• только создать подключение - будет создан только запрос получения данных, но эти данные не будут никуда выгружены.
• выгрузить в таблицу на лист - результат запроса получения данных будет выгружен на отдельный лист в объект умной таблицы(Вставка(Insert) -Таблица(Table)). Лист создается автоматически. В дальнейшем полученные данные можно будет обновить напрямую из таблицы(правая кнопка мыши на любой ячейке таблицы -Обновить(Refresh)) или кнопкой Данные(Data) -Обновить все(Refresh All)
  Включить фоновое обновление данных - доступен только если выбран пункт выгрузить в таблицу на лист. Если установлен - запрос будет обновляться параллельно с другими запросами и действиями в книге. Если выключен - запрос будет обновляться последовательно и действия с результатами запроса будут доступны только после окончательного обновления.
• создать сводную таблицу - после создания запроса из таблицы, на основании его данных на отдельном листе будет создана сводная таблица.
  Добавить в модель данных(для Excel 2013 и выше) - доступен только если выбран пункт создать сводную таблицу. Если установлен, то одновременно с созданием сводной таблицы запрос будет добавлен в модель данных Power Pivot, что в дальнейшем позволит объединять данные этого запроса с другими запросами непосредственно из сводной таблицы.
  Важно: данная опция доступна только начиная с Excel 2013. В более ранних версиях модель данных недоступна.

Назначить сводной таблице макет (стандартно назначается из вкладки Конструктор(Design) -Макет отчета(Report Layout)):
при создании сводной таблицы можно сразу выбрать один из вариантов структуры макета

• Сжатая форма(Compact form) - макет, используемый по умолчанию самим Excel. В данном макете все данные в области строк располагаются в одном столбце с небольшими отступами для каждой группы, относительно вышестоящей группы.
• Форма структуры(Outline form) - элементы области строк располагаются в разных столбцах в виде "лесенки": каждая новая группа начинается со следующей строки
• Табличная форма(Tabular form) - элементы области строк располагаются в разных столбцах в линейном виде: каждая категория в своем столбце на одном уровне с остальными категориями

Создать дополнительно запрос объединения всех выбранных таблиц в одну и после успешного создания:
Если установить данный флажок, то после создания запросов Power Query ко всем выбранным таблицам будет создан еще один запрос, который объединяет все созданные запросы в единую таблицу. Заголовки столбцов в таблицах могут различаться. Это не вызовет ошибок и обработается на стадии создания общего запроса. При этом:

• данные одинаковых заголовков всех таблиц будут помещены друг под другом
• если столбцы различаются, то несовпадающие(отсутствующие в других таблицах) столбцы будут добавлены в конец таблицы справа.

Настройки выгрузки после успешного создания подключения полностью совпадают с настройками выгрузки каждой из таблиц.

Если выбрано создание запроса объединения, то для остальных таблиц лучше всего в настройках оставлять только подключение, а для объединенного запроса выгрузку в сводную или умную таблицу.