Как изменить запрос в excel - Word и Excel - помощь в работе с программами

Создание, загрузка и изменение запроса в Excel (Power Query)

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Еще…Меньше

Power Query предлагает несколько способов создания и загрузки power querys в книгу. Вы также можете настроить параметры загрузки запроса по умолчанию в окне Параметры запроса.

Совет Чтобы упросить, сформированы ли данные на этом плане с помощью Power Query, выберите ячейку с данными, а если появится вкладка Лента контекстного запроса, данные загружаются из Power Query.

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

Знакомые Excel, лента и сетка	Лента редактора Power Query и предварительный просмотр данных

Например, работа с данными на Excel значительно отличается от Power Query. Кроме того, подключенные данные, которые вы видите на Excel, могут не работать с Power Query для их обработки. Это происходит только при загрузке данных на таблицу или в модель данных из Power Query.

Переименование я вкладок на таблицах Ямы лучше переименовывать по своему смыслу, особенно если их много. Особенно важно пояснить разницу между данными и данными, загруженными из редактора Power Query. Даже если у вас всего два листа: с таблицей Excel «Лист1»и запросом, созданным путем импорта таблицы Excel Таблица1,ее легко запутать. Всегда имеет смысл изменить названия ярлыков по умолчанию на более понятное. Например, переименуйте Лист1 в Таблицу данных и Таблицу1 в Таблицу запросов. Теперь понятно, какая вкладка с данными, а какая вкладка с запросом.

Вы можете создать запрос из импортируемых данных или пустой запрос.

Создание запроса из импортируемых данных

Это самый распространенный способ создания запроса.

Импорт некоторых данных. Дополнительные сведения см. в том, как импортировать данные из внешних источников.
Выберем ячейку в данных и выберите запрос> изменить.

Создание пустого запроса

Вы можете начать с нуля. Это можно сделать двумя способами.

Выберите Данные> Получить данные >из других источников > пустой запрос.
Выберите Данные> получить данные >запуск редактора Power Query.

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

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

Выберите Новый источник, чтобы добавить источник данных. Эта команда выглядит так же, как> «Получить данные» на Excel ленте.
Выберите Последние источники, чтобы выбрать источник данных, с помощью который вы работали. Эта команда выглядит так же, как> последние источники на Excel ленте.
Чтобы ввести данные вручную, выберите Ввести данные. Вы можете выбрать эту команду, чтобы попробовать редактор Power Query независимо от внешнего источника данных.

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

Загрузка запроса из редактора Power Query

В редакторе Power Query сделайте следующее:

Чтобы загрузить на таблицу, выберите Главная> Закрыть & Загрузить > Закрыть & Загрузить.
Чтобы загрузить данные в модель данных, выберите Главная> Закрыть & Загрузить > Закрыть & Загрузка.

В диалоговом окне Импорт данных выберите добавить эти данные в модельданных.

Совет Иногда команда «Загрузить в» неатривна или отключена. Это может произойти при первом создании запроса в книге. В этом случае нажмите кнопку Закрыть & Загрузить, на новом > запросы данных & Connections > Запросы, щелкните запрос правой кнопкой мыши и выберите загрузить в . Кроме того, на ленте редактора Power Query выберите запрос> Загрузить в.

Загрузка запроса из области «Запросы и подключения»

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

В Excel выберите Запросы> запросы & Connections, а затем выберите вкладку Запросы.
В списке запросов найдите запрос, щелкните его правой кнопкой мыши и выберите загрузить в. Появится диалоговое окно Импорт данных.
Выберите, как вы хотите импортировать данные, а затем выберите ОК. Чтобы получить дополнительные сведения об использовании этого диалогового окна, выберите знак вопроса (?).

Существует несколько способов изменить запрос, загруженный на таблицу.

Изменение запроса на Excel данных

Чтобы изменить запрос, найдите ранее загруженную из редактора Power Query, выйдите ячейку в данных и выберите запрос > Изменить.

Изменение запроса в области «Запросы & подключения»

Возможно, вы & области Запросы и подключения удобнее использовать, если в одной книге много запросов и вы хотите быстро найти его.

В Excel выберите Запросы> запросы & Connections, а затем выберите вкладку Запросы.
В списке запросов найдите запрос, щелкните его правой кнопкой мыши и выберите изменить.

Изменение запроса в диалоговом окне «Свойства запроса»

В Excel выберите Data> Data & Connections > Запросы, щелкните запрос правой кнопкой мыши и выберите Свойства ,выберите вкладку Определение в диалоговом окне Свойства и нажмите кнопку Изменить запрос.

Совет Если вы работаете с запросом на > данных, в диалоговом окнеСвойства выберите вкладку Определение, а затем — Изменить запрос.

Модель данных обычно содержит несколько таблиц, расположенных в связи. Запрос загружается в модель данных с помощью команды Загрузить, чтобы отобразить диалоговое окно Импорт данных, а затем в поле Добавить эти данные в режим данныхl. Дополнительные сведения о моделях данных см. в дополнительных сведениях о том, какие источники данных используются в модели данных книги,Создание модели данных в Excelи Создание таблиц с помощью нескольких таблиц.

Чтобы открыть модель данных, выберите Power Pivot > Управление.
В нижней части окна Power Pivot выберите вкладку нужной таблицы.

Подтвердим, что отображается правильная таблица. Модель данных может иметь много таблиц.
Обратите внимание на имя таблицы.
Чтобы закрыть окно Power Pivot, выберите файл> Закрыть. Чтобы освободить память, может потребоваться несколько секунд.
Выберите > подключения & свойства >Запросы, щелкните запрос правой кнопкой мыши и выберите изменить.
Завершив внесение изменений в редакторе Power Query, выберите файл > Закрыть & загрузить.

Результат

Запрос на этом и в таблице в модели данных обновляются.

Если вы заметили, что загрузка запроса в модель данных занимает намного больше времени, чем загрузка на лист, проверьте действия Power Query, чтобы узнать, фильтруется ли текстовый столбец или структурированный столбец списка с помощью оператора Contains. Это действие приводит Excel повторно прогонять весь набор данных для каждой строки. Более того, Excel не могут эффективно использовать многопрочитанные выполнения. В качестве обходного решения попробуйте использовать другой оператор, например Равно или Начинает с.

Корпорация Майкрософт знает об этой проблеме и находится в стадии исследования.

Вы можете загрузить Power Query:

На один из них. В редакторе Power Query выберите Home> Close & Load > Close & Load.
В модель данных. В редакторе Power Query выберите Home> Close & Load > Close & Load To.

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

Кроме того, динамически переопределять стандартные параметры запроса можно в диалоговом окне Импорт, которое отображается после выбора параметра & Загрузитьв.

Глобальные параметры, которые применяются во всех книгах

В редакторе Power Query выберите Файл > параметры и параметры >параметры запроса.
В диалоговом окне Параметры запроса в левой части в разделе GLOBAL выберите Загрузка данных.
В разделе Загрузка Параметры запроса по умолчанию сделайте следующее:
- Выберите Использовать стандартные параметры загрузки.
- Выберите Указать настраиваемые параметры загрузкипо умолчанию, а затем выберите или отобирать параметр Загрузить на таблицу или Загрузить в модель данных.

Совет В нижней части диалогового окна можно выбрать восстановить параметры по умолчанию, чтобы вернуться к настройкам по умолчанию.

Параметры книги, которые применяются только к текущей книге

В диалоговом окне Параметры запроса в левой части в разделе ТЕКУЩАЯ КНИГА выберите Загрузка данных.
Выполните одно или несколько из указанных ниже действий.
- В области Обнаружение типоввыберите или сберем для обнаружения типов столбцов и их заглавных колонок для неструктурированных источников.
  
  По умолчанию они обнаруживаются. Если вы предпочитаете формировать данные самостоятельно, отоставьте этот параметр.
- В области Связивыберите или отоберем создать связи между таблицами при первом добавлении в модель данных.
  
  По умолчанию перед загрузкой в модель данных можно найти существующие связи между таблицами, например внешние ключи в реляционной базе данных, и импортировать их вместе с данными. Если вы предпочитаете делать это самостоятельно, сделайте это самостоятельно.
- В области Связивыберите или отоберете update relationships when refreshing queries loaded to the Data Model (Связи) при обновлении запросов, загруженных в модель данных.
  
  По умолчанию отношения не обновляются. При обновлении запросов, уже загруженных в модель данных, Power Query находит существующие связи между таблицами, например внешние ключи, в реляционной базе данных и обновляет их. Это может привести к удалению связей, созданных вручную после импорта данных, или к новым связям. Тем не менее, если вы хотите сделать это, выберите этот параметр.
- В области Фоновыеданные выберите или отпустите разрешение предварительного просмотра данных для скачивания в фоновом режиме.
  
  По умолчанию предварительный просмотр данных загружается в фоновом режиме. Если вы хотите сразу же видеть все данные, отобираем этот параметр.

См. также

Справка по Power Query для Excel

Управление запросами в Excel

Нужна дополнительная помощь?

Источник

Список курсов/Материалы темы «Управление взаимодействием»

Видео материала «Редактирование запроса в MS Excel»

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

Содержание:

Редактирование запроса в MS Excel
Добавление столбца в запрос MS Excel
Сортировка полей запроса в MS Excel

Редактирование запроса в MS Excel

Редактировать запрос к данным MS Project Online возможно при условии что в текущем файле MS Excel уже есть подключение к данным. Как подключится к данными MS Project Online Вы можете познакомится в разделе «Подключение к данным MS Project Online». Представлены в этом материале инструкция относятся к MS Excel 2016.

Для редактирования запросов к данным:

Перейдите на пункт меню «Данные«.
На линейке инструментов нажмите на кнопку «Получить данные«.
Открывшемся подменю нажмите на пункт «Запустить редактор запросов«.

Редактор запросов позволяет не только создавать запросы данных но и формировать дополнительные столбцы и представления. В редакторе запросов Вы можете:

Обеднять несколько таблиц данных в одну таблицу, связывая по одной или нескольким столбцам.
Менять типы столбцов и устанавливать автоматическое редактирование данных столбца.
Создавать свои столбцы с расчетными данными (можно использовать функции MS Excel).
Устанавливать фильтры для данных.
Группировать данные по нескольким столбцам.

В левой части окна размещается список таблиц выгруженных с MS Project Online в виде запросов. Вы этом списке Вы можете:

Вставить новый запрос. Станьте на существующем запросе и нажмите правую кнопку мышки и выберите пункт «Копировать«. Нажмите правую кнопку мышки на списке запросов и нажмите кнопку «Вставить«. В перечень запросов будет добавлен копируемый запрос с вопросительным знаком.
Переименовать запрос. Выделите необходимый запрос и нажмите правую кнопку мыши выберите пункт «Переименовать«. После чего название запроса будет выведено в поле редактирования где Вы сможете редактировать имя запроса.
Удалить запрос. Выберите запрос и нажмите на левую кнопку меню и выберите пункт «Удалить«.
Группировать запросы. В контекстном меню выберите пункт меню «Переместить в группу«. В открывшемся подменю выдерите необходимую группу. Если нужно группы нет выберите пункт «Создать группу…«.
Переместить в списке запросов. В контекстном меню выберите пункт «Переместить вверх» или «Переместить вниз«.

В правой части расположен блок «Параметры запроса» в котором Вы можете редактировать имя запроса и применяемые шаги. Все Ваши изменения запроса будут записываться в виде команд в перечень «Применяемые шаги«. Вы можете редактировать уже созданные шаги изменяя запрос.

Добавление столбца в запрос MS Excel

Вы можете добавить в запрос неограниченное количество пользовательских столбцов. Для добавления пользовательского столбца:

Перейдите на закладку «Добавить столбец» верхнего меню.
Нажмите на кнопку «Пользовательский столбец»

3. В открывшемся окне «Добавить пользовательский столбец» укажите имя столбца в поле «Имя пользовательского столбца«

4. В поле «Пользовательская формула столбца:» укажите формулу на основании который будет рассчитываться значение пользовательского столбца. Вы можете использовать формулы MS Excel для преобразования данных. При указания формулы внизу окна будет выведено подтверждение правильности ввода формулы. Для формулы Вы можете использовать поле запроса и нажмите кнопку «<< Вставить«

5. После завершения описания столбца нажмите на кнопку «Ок«.

Сортировка полей запроса в MS Excel

Сортировки данных:

Выберите столбец по информации с которых будет проводится сортировка и нажмите стрелку вниз.
Выберите направление сортировки по возрастанию или по убыванию значений поля.

Для установки фильтра данных Вы можете:

Выберите столбец по информации с которых будет проводится фильтрация и нажмите стрелку вниз.
Для того чтобы убрать пустые значения выберите пункт «Удалить пустые».
Для установки специальных фильтров выберите пункт «Текстовые фильтры» и выберите тип фильтрации из перечня.
Для выбора полей с необходимыми значениями, снимите галочки с ненужных значений поля.

После установки фильтра в правой части «Примененные шаги» будет добавлена строка «Строки с применением фильтра«. Для редактирования фильтра нажмите на значок шестеренки с правой части «Строки с применением фильтра«. В открывшемся окне «Фильтрация строк» Вы можете:

На пункте «Базовый» Вы можете установить фильтр по текущему полю. Фильтровать можно только по двум значениям.
На пункте «Дополнительно» Вы можете установить фильтр по неограниченному количеству столбцов и значений.
В поле «Столбец» выберите столбец запроса.

Вас могут заинтересовать следующие материалы

	Печать проекта в MS Project
	Формирование отчетов по реализации проекта. Настройка всех типов отчетов в MS Project .
	06.12.2022	10564

Печать проекта в MS Project

Формирование отчетов по реализации проекта. Настройка всех типов отчетов в MS Project .

06.12.2022

10564

	Перечень задач в MS SharePoint
	Работа с перечнем задач в MS SharePoint
	18.05.2020	1822

Перечень задач в MS SharePoint

Работа с перечнем задач в MS SharePoint

18.05.2020

1822

	Библиотека документов проектов MS SharePoint
	Работа с документами в библиотеках документов в MS Project Online
	08.12.2019	2320

Библиотека документов проектов MS SharePoint

Работа с документами в библиотеках документов в MS Project Online

08.12.2019

2320

Источник

Хитрости »

12 Январь 2023 912 просмотров

С выходом Power Query в массы все чаще стал появляться вопрос: как обновить запросы один за другим в заданном порядке?

Если еще не работали с этой надстройкой и не знаете что это такое, то для начала лучше ознакомиться со статьей: Power Query — что такое и почему её необходимо использовать в работе?
Описание проблемы

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

«Запрос — Отделы», «Запрос — Сотрудники», «Заказы и Продажи», «Запрос — Бюджет»

. При этом обязательно необходимо, чтобы «Запрос — Бюджет» обновился последним, т.к. он зависит от всех остальных.

Какие есть варианты решения?

Вручную
Кнопка Обновить все(Refresh All)
Написать собственный порядок обновления через Visual Basic for Applications

Что еще может пригодиться в решении задачи:

Вывести список имен имен всех подключений на лист

Обновление вручную

Можно обновить запросы стандартными методами, поочередно вручную обновляя каждый запрос. Но это не всегда удобно, особенно если запросов более 5. Значит этот вариант не разбираем в деталях в принципе — все его плюсы и минусы очевидны.

Данные -Обновить все

Кнопка Данные(Data) —Обновить все(Refresh All) не просто обновляет все без исключения запросы в книге, но и порядок запросов при этом никак не определить, следовательно мы не можем быть уверены в том, что обновление произошло именно в нужном нам порядке. Но все же не станем отбрасывать этот вариант как не рабочий — если проблема лишь в порядке обновления, то решение все же есть.
На самом деле, если мы имеем дело с Power Query, то правильнее всего выстраивать все взаимосвязи уже на стадии создания этих запросов в любом случае. Т.е. создавать их в правильной последовательности и «затягивать» данные в основной запрос в самую последнюю очередь, ориентируясь на результаты вспомогательных запросов. Это оптимальный вариант, т.к. в этом случае достаточно будет просто обновить нужный запрос, а Power Query сама определит какие вспомогательные запросы необходимо обновить для корректного результата. При этом встроенный механизм Power Query достаточно хорошо справляется с кешированием данных и даже если обновление происходит через кнопку Обновить все(Refresh All), то даже если на один и тот же вспомогательный запрос будет завязано несколько других — он будет обновлен лишь один раз, а не при обновлении каждого запроса.
Но здесь у нас опять же возникает проблема — если нам не надо обновлять все запросы в книге — как Power Query узнает, какие из них надо обновлять, а какие нет? Ответ — никак. Поэтому хоть и следует всегда стараться выстраивать цепочки взаимодействий запросов в «правильном» порядке — нашу проблему с обновлением только части запросов это никак не решит. В этом случае можно создать мастер запрос, в который просто через объединение запросов собирать все нужные. И обновление этого запроса должно инициировать обновление всех указанных в нем запросов со всеми их цепочками. Как создать подобный мастер запрос:

Все, запрос готов. Теперь достаточно обновить только его, чтобы все связанные запросы обновились. Правда, есть одна проблема: если данных много, то такой запрос их увеличит вдвое — ведь мы в один запрос собираем все остальные…

Собственный порядок обновления через Visual Basic for Applications

В решении через мастер-запрос есть одна проблема — запросы могут быть не только Power Query, но и более старых версий. Здесь вариантов вообще не остается — только Visual Basic for Applications. Да и в случае, если запросы уже созданы ранее кем-то еще или навыков создать мастер-запрос нет.

'---------------------------------------------------------------------------------------
' Author : Щербаков Дмитрий(The_Prist)
'          Профессиональная разработка приложений для MS Office любой сложности
'          Проведение тренингов по MS Excel
'          https://www.excel-vba.ru
'          info@excel-vba.ru
' Purpose: Код обновляет поочередно указанные запросы в заданном порядке
'---------------------------------------------------------------------------------------
Sub RefreshConnections()
    Dim oc, xQuery, aConnections, xConName
    Dim IsBG_Refresh As Boolean
    'создаем перечисление всех запросов, которые надо обновить
    'располагаем их в том порядке, в котором необходимо обновлять
    aConnections = Array("Запрос — Отделы", "Запрос — Сотрудники", "Заказы и Продажи", "Запрос — Бюджет")
    'перебираем и обновляем все запросы поочередно
 
    For Each xConName In aConnections
        Set oc = ActiveWorkbook.Connections(xConName)
        Set xQuery = Nothing
        Select Case oc.Type
        Case Excel.XlConnectionType.xlConnectionTypeODBC
            Set xQuery = oc.ODBCConnection
        Case Excel.XlConnectionType.xlConnectionTypeOLEDB
            Set xQuery = oc.OLEDBConnection
        Case Else
            'запрос выгружен в таблицу на листе
            If oc.Ranges.Count > 0 Then
                Set xQuery = oc.Ranges(1).QueryTable
            End If
        End Select
        'непосредственно обновление запроса с ожиданием окончания обновления
        If Not xQuery Is Nothing Then
            'запоминаем значение обновления в фоне для запроса
            IsBG_Refresh = xQuery.BackgroundQuery
            'выставляем принудительно ждать завершения запроса
            xQuery.BackgroundQuery = False
            'обновляем запрос
            xQuery.Refresh
            'возвращаем обновление в фоне в первоначальное состояние
            xQuery.BackgroundQuery = IsBG_Refresh
        End If
    Next
End Sub

Как использовать: Для начала надо убедиться, что разрешены макросы и при необходимости включить их: почему не работает макрос. Затем копируем код выше, из Excel переходим в редактор VBA(Alt+F11) —Insert —Module. Вставляем туда скопированный код. Теперь код можно вызывать нажатием клавиш Alt+F8 -выделяем RefreshConnections —Выполнить(Run).
Чтобы использовать приведенный код для своих запросов достаточно в строке:

aConnections = Array("Запрос — Отделы", "Запрос — Сотрудники", "Заказы и Продажи", "Запрос — Бюджет")

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

Вывести список всех запросов в книге на лист

Что делать, если

я не знаю, как правильно называются запросы

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

'---------------------------------------------------------------------------------------
' Author : Щербаков Дмитрий(The_Prist)
'          Профессиональная разработка приложений для MS Office любой сложности
'          Проведение тренингов по MS Excel
'          https://www.excel-vba.ru
'          info@excel-vba.ru
' Purpose: Код выводит на лист все запросы в текущей книге
'---------------------------------------------------------------------------------------
Sub GetAllConnections()
    Dim ws As Worksheet
    Dim lr As Long
    Dim oc, aList()
    'создаем массив для запоминания имен всех запросов
    ReDim aList(1 To ActiveWorkbook.Connections.Count, 1 To 1)
    For Each oc In ActiveWorkbook.Connections
        lr = lr + 1 'увеличиваем счетчик
        aList(lr, 1) = oc.Name
    Next
    'создаем новый лист, в который записываем имена всех запросов
    Set ws = ActiveWorkbook.Worksheets.Add(before:=ActiveWorkbook.Worksheets(1))
    'оформляем заголовок(для эстетики)
    ws.Cells(1, 1).Value = "Имя запроса"
    ws.Cells(1, 1).Font.Bold = True
    'записываем имена всех запросов
    ws.Cells(2, 1).Resize(lr, 1).Value = aList
End Sub

Статья помогла? Поделись ссылкой с друзьями!

Видеоуроки

Поиск по меткам

Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика

Источник

Содержание:

1.
О продукте

Возможности Microsoft Excel

2.
Новые ключевые
возможности Microsoft Excel

3. Дополнительные новые
возможности Microsoft Excel

4.
Специальные
возможности в Microsoft Excel

Технические характеристики и компоненты
Microsoft Excel

5.
Технические характеристики и ограничения Microsoft Excel

6. Компоненты,
устанавливаемые вместе с Microsoft Excel

Настройка Microsoft Excel

7. Настройка элементов
окна программы

8. Изменение
значений по умолчанию и настроек

9. Настройка панелей инструментов и меню

10. Настройка
параметров запуска Microsoft Excel

11. Использование надстроек и дополнительных компонентов

12.

Разрешение вопросов, связанных с
настройкой приложения Microsoft Excel

Управление и печать файлов

13. Создание и
открытие книг

14.
Поиск и предварительный просмотр файлов

15.
Настройка свойств
файлов

16. Печать
файлов

17. Сохранение и закрытие файлов

18.
Преобразование
файлов в формат
Microsoft
Excel и обратно

19. Работа с
шаблонами

20. Разрешение вопросов, связанных с управлением и
печатью файлов

Работа с книгами и листами

21.
Управление
листами

22. Размещение окон и просмотр листов

23.
Сохранение настроек
отображения и печати как представления

24.
Разрешение вопросов
при работе с книгами и листами

Работа с
данными на листах

25.
Ввод данных

26.
Выбор данных

27.
Редактирование данных на
листе

28.
Копирование и перемещение
данных

29.
Проверка орфографии

30.
Использование буфера обмена
Microsoft Office

31.
Форматирование листов

32.
Использование границ и
заливки

33.
Использование условных
форматов

34.
Изменение размера ячеек и
положения текста

35.
Использование числовых
форматов

36.
Использование стилей

37.
Работа с текстом и данными

38.
Отбор

39.
Сортировка

40.
Проверка записей в ячейках

41.
Разрешение вопросов,
связанных с данными на листах

Использование
Microsoft Excel при работе в
Интернете

42.
Публикация данных
Microsoft Excel
в Интернете

43.
Загрузка и анализ данных из
Интернета

44.
Работа с гиперссылками

45.
Работа с веб-папками

46.
Автоматизация содержимого
веб-страниц

47.
Работа с файлами и узлами
FTP

48.
Разрешение вопросов
по использованию
Microsoft Excel при работе в
Интернете

Импорт данных

49.
Импорт данных

50.
Работа с данными
OLAP

51.

Создание и выполнение
запросов

52.
Настройка
Microsoft Query и источников
данных

53.
Работа с внешними данными на
листе

54.
Разрешение вопросов,
связанных с внешними данными

Анализ и
управление данными

55.
Автоматический расчёт
итоговых данных

56.
Структуризация данных

57.
Консолидация данных

58.
Анализ данных с помощью
свободных таблиц и отчётов

59.
Создание и удаление
свободных таблиц и отчётов

60.
Настройка вида и разметки
свободных таблиц

61.
Печать свободных таблиц

62.
Создание свободных таблиц с
помощью групповых операций и формул

63.
Извлечение и обновление
данных

64.
Выполнение анализов
«что-если» для данных на листах

65.
Анализ таблиц данных

66.
Процедура поиска решения

67.
Работа со сценариями

68.
Разрешение вопросов,
связанных с анализом и управлением
данными

Создание и
использование форм

69.
Создание и использование форм

70.
Создание форм

71.
Использование форм

72.
Разрешение вопросов,
связанных с созданием и использованием
форм

Создание и
исправление формул

73.
Создание формул

74.
Использование ссылок

75.
Формулы массивов

76.
Имена и заголовки

77.
Условные формулы

78.
Формулы даты и времени

79.
Математические формулы

80.
Текстовые формулы

81.
Формулы сравнения

82.
Финансовые формулы

83.
Создание связей

84.
Управление расчётами

85.
Исправление формул

86.
Работа с Евро

87.
Разрешение вопросов,
связанных с созданием и исправлением
формул

Работа с
функциями

88.
Справка по функциям

89.
Внешние функции

90.
Инженерные функции

91.
Информационные функции

92.
Логические функции

93.
Математические функции

94.
Статистические функции

95.
Текстовые функции и функции
обработки данных

96.
Финансовые функции

97.
Функции баз данных

98.
Функции даты и времени

99.
Функции просмотра

Работа с
рисунками и диаграммами

100.
Работа с рисунками и
диаграммами

101.
Создание фигур, линий,
полулиний и других графический объектов

102.
Форматирование графических
объектов

103.
Добавление текста и особых
текстовых эффектов

104.
Группировка, выравнивание и
перемещение графических объектов

105.
Работа с импортированными
рисунками и картинками

106.
Работа со схемами и
организационными диаграммами

107.
Разрешение вопросов,
связанных с графическими объектами и
рисунками

Работа с
диаграммами

108.
Создание диаграмм

109.
Отображение диаграмм

110.
Работа с маркерами данных,
подписями значений и текстом

111.
Редактирование данных в
диаграмме

112.
Планки погрешностей и линии
тренда

113.
разрешение вопросов ,
связанных с диаграммами

Система
безопасности

114.
Защита от вирусов

115.
Цифровые подписи и
сертификаты

116.
Защита книг и листов

117.
Обеспечение
конфиденциальности

118.
Разрешение вопросов,
связанных с безопасностью

Совместная
работа

119.
Работа с общими книгами

120.
Отправка данных на рецензию

121.
Отслеживание изменений

122.
Пометка и просмотр изменений

123.
Слияние книг

124.
Работа с примечаниями

125.
Работа с обсуждениями

126.
Проведение собраний по
сети

127.
Взаимодействие
Microsoft Excel
и Lotus Notes

128.
Разрешение вопросов,
связанных с совместной работой

Доступ к данным
совместно с другими программами

129.
Доступ к данным совместно с
другими программами

130.
Обмен данными между
Microsoft Excel,
Microsoft Word и
Microsoft PowerPoint

131.
Обмен данными между
Microsoft Excel
и Microsoft Access

132.
Взаимодействие между
Microsoft Excel
Microsoft outlook

133.
Разрешение вопросов,
связанных с совместным доступом к данным

Рукописный текст
и речь

134.
Распознание рукописного
текста и речи

135.
Обработка рукописного текста

136.
Распознавание рукописного
текста на восточно-азиатских языках

137.
Обработка речи

138.
Разрешение вопросов,
связанных с распознаванием рукописного
текста и речи

Смарт-теги

139.
Использование смарт-тегов

140.
Разрешение вопросов,
связанных со смарт-тегами

Автоматизация
задач

141.
Работа с макросами

142.
Разрешение вопросов,
связанных с автоматизацией задач

Создание
и выполнение запросов

Изменение запроса
внешних данных

Щелкните ячейку в диапазоне
внешних данных, который требуется изменить.
На панели инструментов
Внешние данные нажмите кнопку
Изменить запрос
.

Примечание.
Дополнительные сведения о создании и редактировании запросов в
Microsoft Query содержатся в справочной системе Microsoft Query.

Копирование диапазона
внешних данных и его запроса

Нажмите стрелку рядом с полем
Имя в строке формул и выберите имя
диапазона внешних данных, который требуется скопировать.

Если требуется
включить подписи столбцов или формулы, которые не принадлежат
диапазону внешних данных, выделите содержащие их ячейки. Нажмите
стрелку рядом с полем Имя в строке
формул, а затем выберите имя диапазона внешних данных, который
требуется скопировать.
Нажмите кнопку
Копировать
.
Откройте окно книги, в которую
требуется вставить диапазон внешних данных.
Выделите ячейку в верхнем
левом углу области вставки.

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

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

Настройка запроса с
параметрами

Запросы с параметрами должны
быть созданы в Microsoft Query. Дополнительные сведения о создании
запросов с параметрами см в справке Microsoft Query.

Изменение сообщения запроса с
параметрами в Microsoft Excel

В Microsoft Excel щелкните
любую ячейку диапазона внешних данных, созданного с помощью
запроса с параметрами.
На панели инструментов
Внешние данные нажмите
кнопку Параметры запроса
.
Из списка в левой части
диалогового окна Параметры
выберите параметр, который требуется изменить.
В поле
запрос
на ввод параметра пользователем введите текст сообщения и
нажмите кнопку OK. Сообщение может
содержать до 100 знаков.
Чтобы применить новое
сообщение и обновить данные, выберите
Обновить данные
на
панели инструментов Внешние
данные.

Примечание.
Данная процедура изменяет пользовательский запрос только для
текущего сеанса работы с книгой. Чтобы постоянно выводить на экран
новое сообщение, сохраните книгу или введите новое сообщение для
запроса в Microsoft Query. Более подробные сведения об изменении
запроса см. в справке Microsoft Query.

Использование данных из ячейки в
качестве значения параметра

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

Чтобы обновлять
данные при каждом изменении значения в ячейке установите флажок

Автоматически обновлять при изменении значения ячейки.
Нажмите кнопку
OK.
Чтобы обновить данные,
нажмите кнопку Обновить данные
на
панели инструментов Внешние
данные.

Использование постоянного
значения параметра в запросе

В Microsoft Excel щелкните
любую ячейку диапазона внешних данных, созданного с помощью
запроса с параметрами.
На панели инструментов
Внешние данные нажмите
кнопку Параметры запроса
.
Из списка в левой части
диалогового окна Параметры
выберите параметр, который требуется изменить.
Выберите вариант
используется значение.
Введите значение параметра и
нажмите кнопку OK.
Чтобы обновить данные,
нажмите кнопку Обновить данные
на
панели инструментов Внешние
данные.

Выполнение запроса в
фоновом режиме или с прерыванием работы

Выполнение запроса в
фоновом режиме позволяет не прерывать работу в Microsoft Excel на
время выполнения запроса.

Выделите ячейку в диапазоне
внешних данных, а затем нажмите на панели инструментов
Внешние данные кнопку
Свойства диапазона данных
.
Установите флажок
фоновое обновление
для выполнения запросов в фоновом режиме или снимите его для
приостановки работы на время выполнения запросов.

Примечания

Прерывание запроса.
Если флажок фоновое
обновление снят, запрос прерывается нажатием клавиши ESC.
Прерывание фонового
обновления. Чтобы прервать запрос, выполняемый в фоновом
режиме, дважды щелкните значок
Фоновое обновление
в строке
состояния и в появившемся диалоговом окне
Состояние обновления
внешних данных нажмите кнопку
Остановить обновление.
Запись макроса. Во
время записи макроса, содержащего запрос, запуск запроса в фоновом
режиме невозможен. Чтобы получить возможность запускать записанный
макрос в фоновом режиме, вызовите редактор Visual Basic и измените
способ обновления объекта QueryTable с «BackgroundQuery := False» на
«BackgroundQuery := True». Сведения о написании макросов см. в
справке Visual Basic.

Источник

Файлы к уроку:

Для спонсоров Boosty
Для спонсоров VK

Ссылки:

Страница курса
Плейлист YouTube
Плейлист ВК

Описание

В этом уроке вы узнаете:

Зачем нужен Power Query
История Power Query
Как установить Power Query в Excel
Зачем нужен Power Query
Как создать запрос в Power Query

В этом вводном уроке курса Excel Power Query на 1-2-3 мы узнаем, что такое Power Query, зачем он нужен, как его установить и запустить.

Power Query — это встроенный инструмент Excel для получения и преобразования данных (Get and Transform).

Специалистам по Excel приходится каждый день тратить уйму времени на преобразование данных. Мы работаем с разными источниками и данные далеко не всегда приходят в удобном виде.

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

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

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

В Excel эта задача решается долго и тяжело. Если тесты происходят, например, раз в неделю, то каждую неделю вам придется тратить уйму времени на обработку файлов. Более того, чтобы решить эту задачу стандартными возможностями Excel нужен достаточно высокий уровень владения.

Как вы думаете, сколько времени уйдет на решение этой задачи в Excel Power Query? Всего несколько минут. Более того, вам достаточно решить эту задачу всего 1 раз. А когда придут новые данные достаточно будет всего лишь нажать «Обновить».

Решение

Запуск Power Query

Если у вас Excel от 2016 версии и новее, то Power Query устанавливать не нужно. Он уже встроен в Excel.

Если у вас Excel 2010 или 2013, то Power Query нужно сначала скачать. Перейдите по ссылке https://www.microsoft.com/ru-ru/download/details.aspx?id=39379 и нажмите «Скачать». Потом отметьте галочкой файл для вашей разрядности операционной системы (64 или 32 бит). После скачивания файла запустите установку.

Теперь в главном меню Excel должна появиться еще одна вкладка. Если вкладка не появилась, то перейдите в меню Файл — Параметры — Настройки — Управление — Надстройки СОМ. В открывшемся окне отметьте галочкой пункт Power Query.

Добавить кнопки Power Query на панель быстрого доступа

Я рекомендую добавить кнопки для работы с Power Query на панель быстрого доступа. Это ускоряет работу. Я настоятельно рекомендую добавить 3 команды:

Изменить запрос
Запросы и подключения
Запустить редактор запросов

Если вы не знаете как добавлять кнопки в панель быстрого доступа, то посмотрите видео-версию урока на YouTube.

Нажмите «Настройка панели быстрого доступа». В появившемся окне справа снизу найдите кнопку «Экспорт/импорт». Нажмите ее и укажите путь к скачанному файлу.

Предварительные настройки Power Query

Откройте редактор запросов любым удобным способом:

При помощи созданной кнопки в панели быстрого доступа
Вкладка Данные — Группа Получить и преобразовать данные — Получить данные — Запустить редактор запросов

В окне Редактора запросов нажмите Файл — Параметры и настройки — Параметры запроса. В пункте Загрузка данных сделайте настройки как на картинке:

Теперь перейдите в пункт Редактор Power Query и поставьте галочку «Отобразить редактор запросов».

Далее переходим в пункт Конфиденциальность и выбираем «Всегда игнорировать уровни конфиденциальности».

Жмем ОК. Настройки вступят в силу при следующем запуске редактора запросов.

Создаем первый запрос Power Query

Создадим первый запрос к веб-странице с таблицей состава индекса Dow Jones. Выполняем следующие действия:

Запускаем редактор запросов
Вкладка Главная — Создать источник — Другие источники — Интернет
Вводим адрес https://finance.yahoo.com/quote/%5EDJI/components?ltr=1
Нажимаем Подключение
Слева указываем нужную таблицу и жмем ОК

Перед вам появится следующее окно редактора запросов Power Query:

Сверху мы видим меню в привычном ленточном интерфейсе как и во всех продуктах Microsoft Office. Слева список запросов, справа примененные шаги к запросу. Теперь продолжим работать с нашим запросом.

Удалим шаг Изменить тип. Для этого в списке шагов справа нажмем на крестик слева от названия шага. Укажем тип данных для каждого столбца.

По умолчанию Power Query определил тип данных каждого столбца как текстовый. Об этом говорит пиктограмма с символами ABC слева от названий столбцов.

Попробуйте нажать на пиктограмму ABC столбца Last Price и указать тип данных десятичное число. В столбце во всех строках отобразятся ошибки. Это произошло из-за того, что в нашем регионе целая и дробная части разделяются запятыми, а в таблице с этого сайта точкой. В таком случае нужно указать тип с использованием локали.

Нажмите правой кнопкой мыши на название столбца Last Price — Тип изменения — Используя локаль. Укажите тип данных целое число и языковой стандарт Английский США. Теперь все получилось. То же самое проделайте для других числовых столбцов.

Теперь перейдите на вкладку Главная, щелкните на нижнюю часть кнопки Закрыть и загрузить, в списке выберете Закрыть и загрузить в. Отметьте пункт Таблица и укажите место, куда эту таблицу поместить, потом нажмите ОК. Наш первый запрос готов!

Power Query разное

Номер урока	Урок	Описание
1	Power Query. Знакомство с Power Query	В этом уроке мы познакомимся в Power Query. Зачем нужен Power Query Как установить Power Query Как его Настроить Как изменить запрос
2	Power Query. Подключение XML	В этом уроке мы научимся подключаться к файлам в формате XML и импортировать эти данные в Excel.
3	Power Query. Уникальные значения двух столбцов	В этом уроке мы получим уникальные значения из двух столбцов таблицы.
4	Power Query. Импорт таблиц PDF	Импорт таблиц из файла PDF, импорт таблиц из множества PDF файлов с объединением в один датасет.
5	Power Query. Собрать разбитую строку	В этом практическом уроке мы научимся соединять разбитую строку. Этот пример взят из реальной практики одного из спонсоров канала.
6	Power Query. Пивот со счетом	В этом уроке мы создадим пивот, в котором будут пронумерованы столбцы.
7	Power Query. Минимальное значение в диапазоне	В этом уроке мы найдем минимальное значение в диапазоне строк.
8	Power Query. Нарастающий итог 2	В этом уроке мы изучим еще один способ сделать нарастающий итог в Power Query.
9	Power Query. Нарастающий итог 3	В этом уроке мы разберем еще один способ выполнить нарастающий итог в Power Query.
10	Power Query. Прирост населения Китая	В этом уроке мы сравним прирост населения Китая с приростом населения мира в целом за последние 200 лет.
11	Power Query. Повторяющиеся значения в строке	В этом уроке разберем как определить есть ли в строке повторения.
12	Power Query. Таблица навигации по функциям М	В этом уроке вы узнаете как создать таблицу навигации по всем функциям языка Power Query.
13	Power Query. Удалить запросы и модель данных из книги	Разберем как быстро удалить все запросы и модель данных из текущей книги.
14	Power Query. Открыть еще 1 Excel и еще 3 трюка	В этом видео я покажу как открыть еще 1 файл Excel, если у вас уже запущен Power Query.
15	Power Query. Подключиться к ZIP архиву	Пользовательская функция для подключения к zip файлу. Подключимся к txt файлу, который находится в zip архиве.
16	Power Query. Импорт Word	Импортируем таблицу из документа Word. Для спонсоров разберем импорт таблицы с объединенными ячейками.
17	Power Query. Фильтрация списком	В этом уроке мы хотим отфильтровать таблицу при помощи списка, например, хотим получить продажи определенных товаров.
18	Power Query. Пользовательская функция Switch	В этом уроке мы создадим пользовательскую функцию Switch.
19	Power Query. Информация о формате, Чтение zip	В этом уроке мы узнаем как получить информацию о формате ячеек при помощи Power Query.
20	Power Query. Импорт данных из gz	В этом уроке мы разберем как импортировать файл в формате gz.
21	Power Query. Удалить лишние пробелы, Text.Split	В этом уроке мы научимся удалять лишние пробелы в текстовом столбце таблицы.
22	Power Query. Параметры в SQL-запросе	Вы хотите, чтобы в ваш SQL-запрос подставлялось значение из параметра, источником которого является ячейка с листа Excel.
23	Power Query. Параметры в SQL-запросе 2	Ваш запрос очень большой и количество параметров в нем большое. Как организовать все так, чтобы было удобно работать.
24	Power Query. Добавить столбец в каждую таблицу табличного столбца	В этом уроке вы узнаете как трансформировать табличный столбец, например, вы сможете добавить столбец индекса внутрь каждой таблицы табличного столбца.
25	Power Query. Интервальный просмотр 1 (ВПР 1)	Объединить 2 таблицы с интервальным просмотром = 1.
26	Power Query. Относительный путь к файлу и папке	Если ваш источник находится в той же папке, что и отчет, то вы можете указать относительный путь. В таком случае подключение не будет ломаться, если вы запустите файл на другом компьютере.
27	Power Query. Нарастающий итог в каждой категории	Применим функцию нарастающего итога не ко всей таблице, а к определенному окну.
28	Power Query. ВПР без Merge или Join	Вам нужно подставить данные из столбца другой таблицы. Как это сделать без объединения таблиц.

Источник

См. также

Нужна дополнительная помощь?

Видео материала «Редактирование запроса в MS Excel»

Содержание:

Редактирование запроса в MS Excel

Добавление столбца в запрос MS Excel

Сортировка полей запроса в MS Excel

Вас могут заинтересовать следующие материалы

Поиск по меткам

Изменение запроса внешних данных

Копирование диапазона внешних данных и его запроса

Настройка запроса с параметрами

Выполнение запроса в фоновом режиме или с прерыванием работы

Описание

Решение

Запуск Power Query

Добавить кнопки Power Query на панель быстрого доступа

Предварительные настройки Power Query

Создаем первый запрос Power Query

Power Query разное

А вот еще интересные статьи:

Изменение запроса
внешних данных

Копирование диапазона
внешних данных и его запроса

Настройка запроса с
параметрами

Выполнение запроса в
фоновом режиме или с прерыванием работы