Какие типы таблиц возвращает функция excel currentworkbook

Создание формул Power Query в Excel

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

Примечание: Надстройка Power Query предлагается в Excel 2016 в виде группы команд Скачать и преобразовать. Информация в этой статье относится и к Power Query, и к новой группе. Дополнительные сведения см. в статье Функция «Скачать и преобразовать» в Excel 2016.

Для создания формул Power Query в Excel можно использовать строку формул редактора запросов или расширенный редактор. Редактор запросов — это средство, включенное в Power Query, в котором можно создавать запросы к данным и формулы. Для создания этих формул используется язык формул Power Query. Существует множество формул Power Query, с помощью которых можно обнаруживать, объединять и уточнять данные. Подробные сведения обо всех видах формул Power Query см. в статье Категории формул Power Query.

Давайте создадим сначала простую формулу, а затем сложную.

• Создание простой формулы
• Создание сложной формулы

Создание простой формулы

В этом примере мы с помощью формулы Text.Proper() преобразуем текстовое значение, сделав первые буквы слов прописными.

На вкладке ленты POWER QUERY нажмите кнопку Из других источников и выберите пункт Пустой запрос.

В строке формул редактора запросов введите = Text.Proper(«text value») , а затем нажмите клавишу ВВОД или щелкните значок «Ввод».

Результат будет выглядеть на листе вот так:

В редакторе запросов можно также создавать сложные формулы.

Создание сложной формулы

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

Примечание: В этой статье приведены базовые сведения о сложных формулах Power Query. Подробные сведения о формулах Power Query см. в статье Формулы Power Query.

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

Исходная таблица выглядит так:

Результирующая таблица должна иметь такой вид:

Рассмотрим шаги формулы запроса, с помощью которых можно изменить исходную таблицу и сделать первые буквы слов в столбце ProductName прописными.

Пример составления сложного запроса с помощью расширенного редактора

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

• Создайте последовательность шагов формулы запроса, которая начинается с оператора let. Обратите внимание: в языке формул Power Query учитывается регистр.
• Каждый шаг формулы запроса строится на основе одного из предыдущих шагов (ссылается на него по имени).
• Результат шага формулы запроса выводится с помощью оператора in. Обычно последний шаг запроса используется как окончательный результат оператора in для набора данных.

Шаг 1. Открытие расширенного редактора

1. На вкладке ленты POWER QUERY нажмите кнопку Из других источников и выберите пункт Пустой запрос.
2. В редакторе запросов выберите команду Расширенный редактор.

Откроется окно Расширенный редактор.

Шаг 2. Определение исходного источника

В расширенном редакторе сделайте следующее:

1. Используйте оператор let, определяющий формулу Источник = Excel.CurrentWorkbook(). В результате источником данных станет таблица Excel. Дополнительные сведения о формуле Excel.CurrentWorkbook() см. в статье Excel.CurrentWorkbook.
2. Назначьте имя шага Источник результату оператора in.

let Source = Excel.CurrentWorkbook()[Content] in Source

Сложный запрос будет выглядеть в расширенном редакторе следующим образом:

1. Нажмите кнопку Готово.
2. На ленте редактора запросов нажмите кнопку Закрыть и загрузить.

На листе результат будет выглядеть вот так:

Шаг 3. Преобразование первой строки в заголовки

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

1. Добавьте к шагам формулы запроса формулу #»Использовать первую строку в качестве заголовков» = Table.PromoteHeaders() и сошлитесь на «Источник» как на источник данных. Дополнительные сведения о формуле Table.PromoteHeaders() см. в статье Table.PromoteHeaders.
2. Назначьте имя шага #»Использовать первую строку в качестве заголовков» результату оператора in.

let Source = Excel.CurrentWorkbook()[Content], #"First Row as Header" = Table.PromoteHeaders(Source) in #"First Row as Header"

На листе результат будет выглядеть вот так:

Шаг 4. Изменение каждого значения в столбце (замена первых букв в словах прописными)

Чтобы преобразовать каждое значение столбца ProductName, сделав первые буквы слов прописными, воспользуйтесь формулой Table.TransformColumns() и сошлитесь на шаг формулы запроса «Использовать первую строку в качестве заголовков». Для этого воспользуйтесь расширенным редактором.

1. Добавьте к шагам формулы запроса формулу #»Выполнена капитализация каждого слова» = Table.TransformColumns() и сошлитесь на шаг #»Использовать первую строку в качестве заголовков» как на источник данных. Дополнительные сведения о формуле Table.TransformColumns() см. в статье Table.TransformColumns.
2. Назначьте имя шага #»Выполнена капитализация каждого слова» результату оператора in.

let Source = Excel.CurrentWorkbook()[Content], #"First Row as Header" = Table.PromoteHeaders(Source), #"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",>) in #"Capitalized Each Word"

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

С помощью языка формул Power Query можно создавать простые и сложные запросы для обнаружения, объединения и уточнения данных. Дополнительные сведения о Power Query см. в статье Справка по Microsoft Power Query для Excel.

Доступ к функциям данных

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

Имя	Описание
AccessControlEntry.ConditionToIdentities	Возвращает список удостоверений, которые будет принимать условие.
Access.Database	Возвращает структурное представление базы данных Microsoft Access.
ActiveDirectory.Domains	Возвращает список доменов Active Directory в том же лесу, что и указанный домен или домен текущего компьютера, если он не указан.
AdobeAnalytics.Cubes	Возвращает наборы отчетов в Adobe Analytics.
AdoDotNet.DataSource	Возвращает коллекцию схем для источника данных ADO.NET.
AdoDotNet.Query	Возвращает коллекцию схем для источника данных ADO.NET.
AnalysisServices.Database	Возвращает таблицу многомерных кубов или табличных моделей из базы данных Analysis Services.
AnalysisServices.Database	Возвращает базы данных служб Analysis Services на определенном узле.
Azure служба хранилища. BlobContents	Возвращает содержимое указанного большого двоичного объекта из хранилища Azure.
Azure служба хранилища. Большие двоичные объекты	Возвращает таблицу навигации, содержащую все контейнеры, найденные в учетной записи служба хранилища Azure. Каждая строка имеет имя контейнера и ссылку на большие двоичные объекты контейнера.
Azure служба хранилища. DataLake	Возвращает таблицу навигации, содержащую документы, найденные в указанном контейнере, и ее вложенные папки из Azure Data Lake служба хранилища.
Azure служба хранилища. DataLakeContents	Возвращает содержимое указанного файла из файловой системы Azure Data Lake служба хранилища.
Azure служба хранилища. Таблицы	Возвращает таблицу навигации, содержащую строку для каждой таблицы, найденной по URL-адресу учетной записи из хранилища хранилища Azure. Каждая строка содержит ссылку на таблицу Azure.
Cdm.Contents	Эта функция недоступна, так как для нее требуется .NET 4.5.
Csv.Document	Возвращает содержимое CSV-документа в виде таблицы с помощью указанной кодировки.
Cube.AddAndExpandDimensionColumn	Объединяет указанную таблицу измерения dimensionSelector с кубом cube в отфильтрованном контексте и изменяет степень детализации измерения, расширяя указанный набор атрибутов измерения attributeNames.
Cube.AddMeasureColumn	Добавляет столбец с столбцом имени в куб, содержащий результаты меры мерSelector, примененного в контексте строки каждой строки.
Cube.ApplyParameter	Возвращает куб после применения параметра с аргументами к кубу.
Cube.AttributeMemberId	Возвращает уникальный идентификатор члена из значения свойства элемента.
Cube.AttributeMemberProperTy	Возвращает свойство propertyName атрибута attribute измерения.
Cube.CollapseAndRemoveColumns	Изменяет степень детализации контекста фильтра для куба путем сворачивания атрибутов, сопоставленных с указанными столбцами columnNames.
Cube.Dimensions	Возвращает таблицу, содержащую набор доступных измерений в кубе.
Cube.DisplayFolders	Возвращает составное дерево таблиц, представляющих отображаемую иерархию папок объектов (например, измерений и мер).
Cube.MeasureProperties	Возвращает таблицу, содержащую набор доступных свойств для мер, развернутых в кубе.
Cube.MeasureProperty	Возвращает свойство меры.
Cube.Measures	Возвращает таблицу, содержащую набор доступных мер в кубе.
Cube.Parameters	Возвращает таблицу, содержащую набор параметров, которые можно применить к кубу.
Cube.Properties	Возвращает таблицу, содержащую набор доступных свойств для измерений, развернутых в кубе.
Cube.PropertyKey	Возвращает ключ свойства property .
Cube.ReplaceDimensions	Заменяет набор измерений, возвращенных с помощью Cube.Dimensions.
Cube.Transform	Применяет функции куба списка, преобразования в кубе.
DB2. Базы данных	Возвращает таблицу таблиц и представлений SQL, доступных в базе данных Db2.
DeltaLake.Table	Возвращает содержимое таблицы Delta Lake.
Essbase.Cubes	Возвращает кубы в экземпляре Essbase, сгруппированных по серверу Essbase.
Excel.CurrentWorkbook	Возвращает таблицы в текущей книге Excel.
Книга Excel.Workbook	Возвращает таблицу, представляющую листы в данной книге Excel.
Exchange.Contents	Возвращает оглавление из учетной записи Microsoft Exchange.
File.Contents	Возвращает двоичное содержимое файла, расположенного по пути.
Folder.Contents	Возвращает таблицу, содержащую свойства и содержимое файлов и папок, найденных в указанной папке.
Folder.Files	Возвращает таблицу, содержащую строку для каждого файла, найденного по пути к папке, и вложенных папок. Каждая строка содержит свойства папки или файла и ссылку на его содержимое.
GoogleAnalytics.Accounts	Возвращает учетные записи Google Analytics для текущих учетных данных.
Hdfs.Contents	Возвращает таблицу, содержащую строку для каждой папки и файла, найденной по URL-адресу папки, из файловой системы Hadoop. Каждая строка содержит свойства папки или файла и ссылку на его содержимое.
Hdfs.Files	Возвращает таблицу, содержащую строку для каждого файла, найденного по URL-адресу папки, и вложенные папки из файловой системы Hadoop. Каждая строка содержит свойства файла и ссылку на его содержимое.
HdInsight.Containers	Возвращает таблицу навигации, содержащую все контейнеры, найденные в учетной записи HDInsight. Каждая строка имеет имя контейнера и таблицу, содержащую свои файлы.
HdInsight.Contents	Возвращает таблицу навигации, содержащую все контейнеры, найденные в учетной записи HDInsight. Каждая строка имеет имя контейнера и таблицу, содержащую свои файлы.
HdInsight.Files	Возвращает таблицу, содержащую строку для каждой папки и файла, найденной по URL-адресу контейнера, и вложенных папок из учетной записи HDInsight. Каждая строка содержит свойства файла или папки и ссылку на его содержимое.
Html.Table	Возвращает таблицу, содержащую результаты выполнения указанных селекторов CSS для предоставленного HTML-кода.
Identity.From	Создает удостоверение.
Identity.IsMemberOf	Определяет, является ли удостоверение членом коллекции удостоверений.
IdentityProvider.Default	Поставщик удостоверений по умолчанию для текущего узла.
Informix.Database	Возвращает таблицу таблиц и представлений SQL, доступных в базе данных Informix на сервере server в именованном database экземпляре базы данных.
Json.Document	Возвращает содержимое документа JSON. Содержимое может быть непосредственно передано функции в виде текста или может быть двоичным значением, возвращаемым функцией, например File.Contents.
Json.FromValue	Создает представление JSON заданного значения с кодировкой текста, заданной кодировкой.
MySQL.Database	Возвращает таблицу с данными, связанными с таблицами в указанном my База данных SQL.
OData.Feed	Возвращает таблицу веб-каналов OData, предлагаемых службой OData.
Odbc.DataSource	Возвращает таблицу таблиц SQL и представлений из источника данных ODBC, указанного строка подключения connectionString .
Odbc.InferOptions	Возвращает результат попытки определить возможности SQL для драйвера ODBC.
Odbc.Query	Подключение универсальному поставщику с заданным строка подключения и возвращает результат оценки запроса.
OleDb.DataSource	Возвращает таблицу таблиц и представлений SQL из источника данных OLE DB, указанного строка подключения.
OleDb.Query	Возвращает результат выполнения собственного запроса в источнике данных OLE DB.
Oracle.Database	Возвращает таблицу с данными, связанными с таблицами в указанной базе данных Oracle.
Pdf.Tables	Возвращает все таблицы, найденные в PDF-файле.
PostgreSQL.Database	Возвращает таблицу с данными, связанными с таблицами в указанном Postgre База данных SQL.
RData.FromBinary	Возвращает запись кадров данных из файла RData.
Salesforce.Data	Подключение в API объектов Salesforce и возвращает набор доступных объектов (т. е. учетные записи).
Salesforce.Reports	Подключение в API отчетов Salesforce и возвращает набор доступных отчетов.
SapBusinessWarehouse.Cubes	Возвращает infoCubes и запросы в системе SAP Business Warehouse, сгруппированных по InfoArea.
SapHana.Database	Возвращает пакеты в базе данных SAP HANA.
SharePoint.Contents	Возвращает таблицу, содержащую строку для каждой папки и документа, найденной по URL-адресу сайта SharePoint. Каждая строка содержит свойства папки или файла и ссылку на его содержимое.
SharePoint.Files	Возвращает таблицу, содержащую строку для каждого документа, найденного по URL-адресу сайта SharePoint и вложенным папкам. Каждая строка содержит свойства папки или файла и ссылку на его содержимое.
SharePoint.Tables	Возвращает таблицу, содержащую результат списка SharePoint в виде веб-канала OData.
Soda.Feed	Возвращает полученную таблицу CSV-файла, доступ к которому можно получить с помощью API SODA 2.0. URL-адрес должен указывать на допустимый источник, совместимый с SODA, который заканчивается расширением CSV.
База данных Sql.Database	Возвращает таблицу, содержащую таблицы SQL, расположенные в базе данных экземпляра SQL Server.
Базы данных Sql.Database	Возвращает таблицу со ссылками на базы данных, расположенные на экземпляре SQL Server. Возвращает таблицу навигации.
Sybase.Database	Возвращает таблицу с данными, связанными с таблицами в указанной базе данных Sybase.
Teradata.Database	Возвращает таблицу с данными, связанными с таблицами в указанной базе данных Teradata.
WebAction.Request	Создает действие, которое при выполнении возвращает результаты выполнения запроса метода по URL-адресу с использованием HTTP в качестве двоичного значения.
Web.BrowserContents	Возвращает HTML-код для указанного URL-адреса, как показано в веб-браузере.
Web.Contents	Возвращает содержимое, скачаемое с веб-URL-адреса в виде двоичного значения.
Web.Headers	Возвращает заголовки HTTP, скачанные из URL-адреса в качестве значения записи.
Web.Page	Возвращает содержимое HTML-веб-страницы в виде таблицы.
Xml.Document	Возвращает содержимое XML-документа в виде иерархической таблицы (список записей).
Xml.Tables	Возвращает содержимое XML-документа в виде вложенной коллекции плоских таблиц.

Обратная связь

Были ли сведения на этой странице полезными?

Какие типы таблиц возвращает функция excel currentworkbook

Крис Вебб (Chris Webb) — независимый эксперт, консультант по технологиям Analysis Services, MDX, Power Pivot, DAX, Power Query и Power BI. Его блог — это кладезь информации на тему перечисленных технологий. Вот уже более 10 лет он пишет про BI-решения от Microsoft. Количество его статей перевалило за 1000! Также Крис выступает на большом количестве различных конференций вроде SQLBits, PASS Summit, PASS BA Conference, SQL Saturdays и участвует в различных сообществах.
Крис любезно разрешил нам переводить его статьи на русский язык. И мы представляем первую статью.

Данная статья относится к надстройке Power Query в Excel 2010/2013, к группе Скачать и преобразовать вкладки Данные в Excel 2016, и к экрану Get Data в Power BI Desktop. Термин «Power Query» используется в том же контексте, что и в предыдущих статьях.

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

Ссылка на значение ячейки в Редакторе запросов

Предположим, что источник данных – таблица Excel, подобная такой:

Импортируем её в Power Query. Чтобы получить данные из ячейки второго столбца второй строки щелкаем по ней ПКМ и выбираем пункт Детализация углублением:

Готово, мы получили 5 в ответе:

Обратите внимание, что это значение 5 не то же самое, что и в ячейке таблицы. Запрос Power Query может вернуть любой тип данных. В данном случае будет возвращено значение целочисленного типа, а не значение типа таблица. Если вывести результаты этого запроса на лист Excel, то мы увидим отформатированную таблицу. Но если использовать результаты этого запроса в качестве входных данных для другого (например, как фильтр в SQL-запросе), то иметь данные целочисленного типа удобнее, чем таблицу из одной строки и столбца.

Ссылка на значение ячейки в Редакторе кода

Вот код для действий на скриншотах выше. Вероятно, вы догадались как он работает.

let Источник = Excel.CurrentWorkbook()[Content], "Измененный тип" = Table.TransformColumnTypes( Источник, , , > ), poleB = #"Измененный тип"[poleB] in poleB

Рассмотрим эти три шага: – Источник – получаем данные из таблицы Excel
– «Измененный тип» – устанавливаем тип данных для трех столбцов в целочисленный
– poleB – возвращает значение ячейки из второй строки столбца В (строки начинаются с 0).

Код позволяет ссылаться на отдельные ячейки таблицы, используя систему координат из имени столбца и номера строки, еще раз повторим, что нумерация строк начинается с нуля. Поэтому выражение:

«Измененный тип»[poleB]

вернёт значение ячейки из второй строки столбца poleB., т.е 5. Аналогично, выражение

«Измененный тип»[poleC]

вернёт значение 3, соответствующее первой строке столбца poleC.

Отметим, что ссылки на столбец и строку могут идти в любом порядке, и выражение #»Измененный тип»[poleB] вернёт то же самое, что и

«Измененный тип»[poleB]

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

Ссылка на отсутствующие строку или столбец

Что произойдёт если использовать ссылку на отсутствующий столбец и/или строку? Конечно, мы получим сообщение об ошибке. Вернёмся к нашему примеру и запишем:

Оба выражения вернут ошибку, т.к. в таблице нет 5-ой строки и столбца poleD.

Однако вместо ошибки можно получить значение NULL, используя оператор «?» после ссылки. Например, выражение

«Измененный тип»[poleD]?

вернёт null вместо сообщения об ошибке:

Но будьте осторожны! Выражение

«Измененный тип»?[poleB]

по-прежнему возвращает ошибку, но не потому что отсутствует пятая строка, а потому что ссылка на пятую строку вернёт null, а у него нет столбца poleB.

Решением может быть изменение порядка ссылок:

«Измененный тип»[poleB]?

или применение оператора «?» для обеих ссылок:

«Измененный тип»?[poleB]?

К сожалению, применение оператора «?» не позволит избежать ошибок, если использовать отрицательные значения в ссылках строк.

Эффект первичного ключа

Знаете ли вы, что таблицы Power Query могут содержать первичный ключ (т. е. один или несколько столбцов, значения которых уникально идентифицируют каждую строку), определяемый самой надстройкой? Нет? Неудивительно, это вовсе не очевидно из пользовательского интерфейса. Однако, существует несколько ситуаций, когда Power Query определяет первичный ключ для таблицы, в том числе:

1. Когда импортируются данные из таблицы реляционной базы данных, подобной SQL Server, и таблица уже имеет первичный ключ.
2. Когда используется кнопка Удалить повторения чтобы убрать повторяющиеся значения из столбца или столбцов, скрытно вызывается функция Table.Distinct()
3. Когда к таблице применяется функция Table.AddKey()

Рассмотрим следующую таблицу Excel, в основном такую же, что приводилась выше, но с новым столбцом, который однозначно идентифицирует каждую строку.

Если вы загрузите таблицу в Power Query, щелкните ПКМ по заголовку столбца poleKey и выберете пункт Удалить повторения, то установите этот столбец первичным ключом.

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

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

let Источник = Excel.CurrentWorkbook()[Content], "Измененный тип" = Table.TransformColumnTypes( Источник, , , >), "Удаленные дубликаты" = Table.Distinct( "Измененный тип", ), "Строка 2" = "Удаленные дубликаты"<[poleKey="Строка 2"]>[poleB] in "Строка 2

Обратите внимание на последний шаг, это важно! Вместо ссылки по номеру строки идёт ссылка по первичному ключу.

Можно продолжать использовать нотацию на основе номера строки, но если таблица имеет столбец с первичными ключами, то можно использовать нотацию с первичным ключом.

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

Возможность ссылок на отдельные значения невероятно полезна в определенных типах запросов и расчётов. Однако стоит помнить, что зачастую существует несколько способов решения задачи, и не все они одинаково хороши.
Одно очевидное применение техники описанной в статье – запись предыдущих вычислений там, где необходимы ссылки на значения предыдущей строки таблицы. Но по опыту известно, что запись расчетов, использующих ссылки на строку/столбец не даёт осуществлять Query Folding («квэри фолдинг» — термин, означающий передачу тяжелых операций по обработке запросов на сторону сервера при работе с совместимой базой данных, на текущий момент это MS SQl, прим. пер.), и ведет к снижению производительности.
Возможно, альтернативные подходы (некоторые описаны в статьях Implementing Common Calculations In Power Query и Join Conditions in Power Query, Part 2: Events-In-Progress, Performance and Query Folding) будут лучшим выходом.

Нет каких-то общих правил, которые можно посоветовать, вы должны сами попробовать разные способы.

Параметризация путей к данным в Power Query

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

Постановка задачи

Предположим, что у нас в папке E:\Отчеты по продажам лежит файл Топ-100 товаров.xls, представляющий собой выгрузку из нашей корпоративной базы данных или ERP-системы (1С, SAP и т.п.) Этот файл содержит информацию о наиболее популярных товарных позициях и выглядит внутри примерно так:
С ходу, наверное, понятно, что работать с ним в Excel в таком виде практически невозможно: будут мешать пустые строки через одну с данными, объединенные ячейки, лишние столбцы, многоуровневая шапка и т.д.
Поэтому рядом с этим файлом в той же папке мы создаём ещё один новый файл Обработчик.xlsx, в котором создадим запрос Power Query, который будет загружать страшненькие данные из исходного файла-выгрузки Топ-100 товаров.xls, и приводить их в порядок:

Создаем запрос к внешнему файлу

Открыв файл Обработчик.xlsx, выберем на вкладке Данные команду Получить данные — Из файла — Из книги Excel (Data — Get Data — From file — From Excel) , затем укажем местоположение исходного файла и нужный нам лист. Выбранные данные загрузятся в редактор Power Query: Приведём их в нормальный вид:

1. Удалим пустые строки через Главная — Удалить строки — Удалить пустые строки (Home — Remove Rows — Remove Empty Rows) .
2. Удалим ненужные 4 верхних строки через Главная — Удалить строки — Удалить верхние строки (Home — Remove Rows — Remove Top Rows) .
3. Поднимем первую строку в шапку таблицы кнопкой Использовать первую строку в качестве заголовков на вкладке Главная (Home — Use first row as header) .
4. Отделим пятизначный артикул от названия товара во втором столбце, используя команду Разделить столбец на вкладке Преобразование (Transform — Split Column) .
5. Удалим ненужные столбцы и переименуем заголовки оставшихся для лучшей наглядности.

В итоге у нас должна получиться следующая, гораздо более приятная, картина:

Осталось эту облагороженную таблицу выгрузить обратно на лист в наш файл Обработчик.xlsx командой Закрыть и загрузить (Home — Close&Load) на вкладке Главная:

Находим путь к файлу в запросе

Теперь давайте посмотрим как выглядит наш запрос «под капотом», на встроенном в Power Query внутреннем языке с лаконичным названием «М». Для этого вернемся в наш запрос двойным щелчком по нему в правой панели Запросы и подключения и на вкладке Просмотр выберем Расширенный редактор (View — Advanced Editor) :

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

Добавляем умную таблицу с путём к файлу

Закроем пока Power Query и вернёмся в наш файл Обработчик.xlsx. Добавим новый пустой лист и сделаем на нём маленькую «умную» таблицу, в единственной ячейке которой будет записан полный путь к нашему файлу исходных данных:

Для создания «умной» таблицы из обычного диапазона можно использовать сочетание клавиш Ctrl + T или кнопку Форматировать как таблицу на вкладке Главная (Home — Format as Table) . Заголовок столбца (ячейка А1) может быть совершенно любым. Также обратите внимание, что для понятности я дал таблице имя Параметры на вкладке Конструктор (Design) .

Скопировать из Проводника путь или даже ввести его вручную не представляет, конечно, особой сложности, но лучше всего минимизировать человеческий фактор и определять путь, по возможности, автоматически. Это можно реализовать с помощью стандартной функции рабочего листа Excel ЯЧЕЙКА (CELL) , которая умеет выдавать кучу полезной информации об указанной в качестве аргумента ячейке — в том числе и путь к текущему файлу:

Если предположить, что файл с исходными данными всегда лежит в той же папке, что и наш Обработчик, то путь, который нам нужен можно сформировать следующей формулой:

или в английской версии:

. где функция ЛЕВСИМВ (LEFT) берёт из полной ссылки кусок текста до открывающей квадратной скобки (т.е. путь к текущей папке), а затем к нему приклеивается имя и расширение нашего исходного файла с данными.

Параметризуем путь в запросе

Остался последний и самый главный штрих — прописать в запросе путь к исходному файлу Топ-100 товаров.xls, сославшись на ячейку А2 нашей созданной «умной» таблицы Параметры.

Для этого вернемся в запрос Power Query и ещё раз откроем Расширенный редактор на вкладке Просмотр (View — Advanced Editor) . Вместо текстовой строки-пути в кавычках «E:\Отчеты по продажам\Топ-100 товаров.xlsx» введём туда вот такую конструкцию:

Excel.CurrentWorkbook() [Content] [Путь к исходным данным]

Давайте разберемся из чего она состоит:

• Excel.CurrentWorkbook() — это функция языка М для обращения к содержимому текущего файла
• [Content] — это уточняющий параметр к предыдущей функции, указывающий, что мы хотим получить содержимое «умной» таблицы Параметры
• [Путь к исходным данным] — это имя столбца в таблице Параметры, к которому мы обращаемся
• — это номер строки в таблице Параметры, из которой мы хотим взять данные. Шапка — не в счет и нумерация начинается от нуля, а не от единицы.

Вот и всё, собственно.

Осталось нажать на Готово и проверить как работает наш запрос. Теперь при пересылке всей папки с обоими файлами внутри на другой ПК запрос будет сохранять работоспособность и определять путь к данным автоматически.

Ссылки по теме

• Что такое Power Query и зачем он нужен при работе в Microsoft Excel
• Как импортировать в Power Query плавающий фрагмент текста
• Редизайн двумерной кросс-таблицы в плоскую с помощью Power Query

Похожие публикации:

1. Как объединить ячейки в html таблице по вертикали
2. Как пользоваться фотоаппаратом canon eos 60d
3. Как удалить календарь в айфоне
4. Что делать если телевизор bbk не включается