Как скрывать панель пуск windows 10

Динамически загружается в PowerQuery через язык M

Видеокурсы по Excel

В сотрудничестве с SEDUO я создал несколько видеокурсов:

• Как работать с Excel – научитесь эффективно использовать Excel – самый популярный ( рейтинг 97 %, более 13 000 студентов, + 750 положительных отзывов ) – Получить курс
• MaxiCourse Excel – получите лучшую работу, больше денег, станьте незаменимым ( более 6 часов просмотра, 160 уроков ) – Получить курс
• G-таблицы. — если не нравится Excel, воспользуйтесь курсом на Google-таблицах

Нужно ли вам динамически изменять загруженные листы в PowerQuery? Из этой статьи вы узнаете, как элегантно использовать язык M и динамические ссылки.

Я предполагаю немного продвинутое знание Excel. когда пара таблиц была обработана (открыта) в PowerQuery.

Ъпо воде

У меня есть таблица с 12 листами, названными по месяцам. В Excel мне нужно иметь измененную таблицу в соответствии с выбранным месяцем. Чтобы мне не приходилось заходить в PowerQuery и менять данные в М-коде (М-коде) вручную ( или загружать и редактировать 12x ). Или как элегантно получить переменную из листа Excel прямо в PowerQuery ( который реагирует на изменение ).

В данной статье для наглядности статья разбита на отдельные главы.

Примечание: На самом деле, это не просто вопрос загрузки таблицы, но ряд корректировок будет сделан в данном месяце, или также может быть возможно выбрать год и, например, подразделение, имена сотрудников и внезапно динамические ссылки приобретают более значимый и практический смысл. В этой статье показано, как вставить одну ссылку в М-код. Точно так же вставьте еще один .

в Power Query.

Исходные данные

У меня есть исходная таблица (внешняя рабочая книга), содержащая 12 месяцев, где каждый месяц находится на отдельном листе. мне нужен первыйтолько бетон.

Таблица + Вспомогательная константа

Я создам пустой файл, в который буду загружать данные из исходной таблицы (внешний файл). Этот файл также будет содержать выбор месяца, который я хочу загрузить. Так что остальное редактирование может выполнить PowerQuery и динамически найти только выбранный месяц.

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

Фон может выглядеть, например, так (важны ячейки B6 и B7):

Загружены исходные данные из внешнего файла

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

На ленте Данные > Первые данные > Из файла > Из блокнота

Найдите рабочую книгу (которую вы хотите импортировать и отредактировать) с исходными данными и нажмите Импорт

.

В появившемся окне Навигатор выберите месяц (например, январь) и нажмите Начать > Загрузить в. , так как я хочу загружать данные в тот же лист, пока не увижу изменения (если вам нужен новый сайт Загрузить > Загрузить ).

Поскольку диалоговое окно предлагает мне, где он будет загружен, я выбираю Таблица (она должна быть предустановлена) и проверяю Существующий лист (и не забываю указать, какая ячейка ) и остается только подтвердить ОК .

И данные загружаются ( хотя с первого взгляда видно, загружается ли и работает ):

Подробнее о загрузке данных из файла я написал в отдельной статье.

Загрузка и создание константы для М-кода

Таблица готова, но вам точно не захочется повторять эти несколько шагов для каждого месяца (если неесли вы каким-то образом модифицируете загруженную таблицу в PowerQuery, удалите и трансформируете столбцы и т.д.). Поэтому подготовим динамическую константу, которую будем загружать в PowerQuery (материалы у вас уже есть, это тот месяц).

В моем случае я помечаю ячейки B6:B7 ( имя константы и ее значение ).

Изменить через меню Данные > Из таблицы или области в диалоговом окне я просто проверяю, содержит ли таблица заголовок, и нажимаю ОК .

Откроется окно PowerQuery и нас будет интересовать правое меню Запросы.

Мы создадим ссылку из таблицы. Щелкните правой кнопкой мыши над именем и выберите Ссылка :

На первый взгляд может показаться, что есть дублирование, но на второй видно различие в ряду формул (показан):

Давайте изменим этот образец

Синтаксис константы прост:

Таблица [Jmono columns]

Давайте просто добавим константу, чтобы она реагировала на данные из таблицы

Написав, я остановился, чтобы подтвердить Enter, но это понятно. Результатом будет название выбранного месяца (и смена значка в вопросах):

Вы по-прежнему можете переименовать константу в: ConstantMonth

Применение динамической константы в М-коде

У вас есть константа, теперь осталось только вставить ее в М-код. В правом окне для таблицы месяцев вы выбираете шаг, который решает, что данный месяц будет прочитан:

Осталось только заменить эту константу (ее имя ConstantMonth ):

from = Source<[Item=»January»,Kind=»Sheet»]>[Data] переключиться на: = Source<[Item=Constant Month,Kind=»Sheet»]>[Data]

Примечание

Аналогичным образом, если вы добавите другие константы, вы сможете использовать их для фильтрации и т. д.

Готово проверено

Просто закройте и загрузите ( выбранная константа загружается в новый лист — затем вы можете скрыть этот лист ).

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

Если нет? Правильно ли названы листы, отображаются ли правильные константы? Где-то ошибка? Зазор наружу? Произошло ли обновление?

Обновить, все можно обновить на вкладке Данные или в разделе «Вопросы и связи», нажав на значок обновления.

Конечно, вы можете иметь больше состояний и т. д., но я оставлю это на ваше усмотрение.

Похожие статьи

Если вы хотите узнать больше о Power Query, я рекомендую статьи по теме:

Для скачивания

Бесплатная загрузка находится в стадии подготовки. Вы можете поддержать публикацию, поддержав на Patreon.

Всего

Сталкивались ли вы с какими-либо проблемами, есть ли у вас совет, как улучшить, вы можете изменить свое мнение в комментариях.

Статья обновлена: 19.09.2020 11:02

Если

Помог ли этот веб-сайт сэкономить вам время и деньги? Помог ли он решить проблему? Готовы ли вы предоставить символическое вознаграждение за дальнейшее развитие? Выберите форму вознаграждения, которая вам подходит.

Павел Ласбк — автор сайта

Microsoft Office (Word, Excel, Google Sheets , PowerPoint) Я использую с 2000 года (с 2004 года в этом домене) — В 2017 году я получил престижную награду MVP от Microsoft (пока я единственный из IR в категории Excel). Я также делюсь своими знаниями и опытом онлайн в видеороликах для SEDUO . В компаниях преподаю и консультирую , работаю в МУНИ. Я создаю этот сайт более 15 лет. Существует более 1000 бесплатных инструкций, советов и рекомендаций, включая более 250 различных шаблонов и рабочих книг.

Пока нет комментариев

Вы можете стать первым, кто оставит осмысленный комментарий.