Сборка таблиц из разных файлов Excel с помощью Power Query

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

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

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

Файлы для сборки

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

Данные в книгах

Количество строк (заказов) в таблицах, само-собой, разное, но набор столбцов везде стандартный.

Задача: собрать данные из всех файлов в одну книгу с последующим автоматическим обновлением при добавлении-удалении файлов-городов или строк в таблицах. По итоговой консолидированной таблице затем можно будет строить любые отчеты, сводные таблицы, фильтровать-сортировать данные и т.д. Главное - суметь собрать.

Подбираем оружие

Для решения нам потребуется последняя версия Excel 2016 (в нее нужный функционал уже встроен по умолчанию) или предыдущие версии Excel 2010-2013 с установленной бесплатной надстройкой Power Query от Microsoft (скачать ее можно здесь). Power Query - это супергибкий и супермощный инструмент для загрузки в Excel данных из внешнего мира с последующей их зачисткой и обработкой. Power Query поддерживает практически все существующие источники данных - от текстовых файлов до SQL и даже Facebook :)

Если у вас нет Excel 2013 или 2016, то дальше можно не читать (шучу). В более древних версиях Excel подобную задачу можно реализовать только программированием макроса на Visual Basic (что весьма непросто для начинающих) или монотонным ручным копированием (что долго и порождает ошибки).

Шаг 1. Импортируем один файл как образец

Для начала давайте импортируем данные из одной книги в качестве примера, чтобы Excel "подхватил идею". Для этого создайте новую пустую книгу и...

  • если у вас Excel 2016, то откройте вкладку Данные и выберите Создать запрос - Из файла - Из книги (Data - New Query- From file - From Excel)
  • если у вас Excel 2010-2013 с установленной надстройкой Power Query, то откройте вкладку Power Query и выберите на ней Из файла - Из книги (From file - From Excel)
Затем в открывшемся окне переходим в нашу папку с отчетами и выбираем любой из файлов-городов (не играет роли какой именно, т.к. они все типовые). Через пару секунд должно появиться окно Навигатор, где нужно в левой части выбрать требуемый нам лист (Продажи), а в правой отобразится его содержимое:

Навигатор Power Query

Если нажать в правом нижнем углу этого окна кнопку Загрузить (Load), то таблица будет сразу импортирована на лист в исходном виде. Для одиночного файла - это хорошо, но нам нужно загрузить много таких файлов, поэтому мы пойдем немного другим путем и жмем кнопку Правка (Edit). После этого должен в отдельном окне отобразиться редактор запросов Power Query с нашими данными из книги:

Редактор запросов Power Query

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

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

Для примера, давайте добавим к нашей таблице столбец с текстовым названием месяца, чтобы потом проще было строить отчеты сводных таблиц. Для этого щелкните правой кнопкой мыши по заголовку столбца Дата и выберите команду Дублировать столбец (Duplicate Column), а затем щелкните правой кнопкой мыши по заголовку появившегося столбца-дубликата и выберите команды Преобразование - Месяц - Название месяца:

Добавление столбца с месяцем

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

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

Преобразование форматов данных в столбцах

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

Фильтрация данных в Power Query

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

Шаги преобразований

Легко и изящно, не правда ли?

Шаг 2. Преобразуем наш запрос в функцию

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

В редакторе запросов перейдите на вкладку Просмотр и нажмите кнопку Расширенный редактор (View - Advanced Editor). Должно открыться окно, где все наши предыдущие действия будут записаны в виде кода на языке М. Обратите внимание, что в коде жестко прописан путь к файлу, который мы импортировали для примера:

Исходный код

Теперь аккуратно вносим пару правок:

Измененный код

Смысл их прост: первая строка (filepath)=> превращает нашу процедуру в функцию с аргументом filepath, а ниже мы меняем фиксированный путь на значение этой переменной. 

Все. Жмем на Готово и должны увидеть вот это:

Созданная функция

Не пугайтесь, что пропали данные - на самом деле все ОК, все так и должно выглядеть :) Мы успешно создали нашу пользовательскую функцию, где запомнился весь алгоритм импорта и обработки данных без привязки к конкретному файлу. Осталось дать ей более понятное имя (например getData) на панели справа в поле Имя и можно жать Главная - Закрыть и загрузить (Home - Close and Load). Обратите внимание, что в коде жестко прописан путь к файлу, который мы импортировали для примера.. Вы вернетесь в основное окно Microsoft Excel, но справа должна появиться панель с созданным подключением к нашей функции:

Подключение к функции

Шаг 3. Собираем все файлы

Все самое сложное - позади, осталась приятная и легкая часть. Идем на вкладку Данные - Создать запрос - Из файла - Из папки (Data - New Query - From file - From folder) или, если у вас Excel 2010-2013, аналогично на вкладку Power Query. В появившемся окне указываем папку, где лежат все наши исходные файлы-города и жмем ОК. Следующим шагом должно открыться окно, где будут перечислены все найденные в этой папке (и ее подпапках) файлы Excel и детализация по каждому из них:

Импорт всех файлов Excel из заданной папки

Жмем Изменить (Edit) и опять попадаем в знакомое окно редактора запросов.

Теперь нужно добавить к нашей таблице еще один столбец с нашей созданной функцией, которая "вытянет" данные из каждого файла. Для этого идем на вкладку Добавить столбец - Пользовательский столбец (Add Column - Add Custom Column) и в появившемся окне вводим нашу функцию getData, указав для ее в качестве аргумента полный путь к каждому файлу:

Дополнительный столбец

После нажатия на ОК созданный столбец должен добавиться к нашей таблице справа.

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

А теперь "вау-момент" - щелкнем мышью по значку со своенным стрелками в правом верхнем углу добавленного столбца с нашей функцией:

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

... снимаем флажок Использовать исходное имя столбца как префикс (Use original column name as prefix)и жмем ОК. И наша функция подгрузит и обработает данные из каждого файла, следуя записанному алгоритму и собрав все в общую таблицу:

Собранные данные

Для полной красоты можно еще убрать расширения .xlsx из первого столбца с именами файлов - стандартной заменой на "ничего" (правой кнопкой мыши по заголовку столбца - Заменить) и переименовать этот столбец в Город. А также подправить формат данных в столбце с датой.

Все! Жмем на Главной - Закрыть и загрузить (Home - Close & Load). Все собранные запросом данные по всем городам будут выгружены на текущий лист Excel в формате "умной таблицы":

Собранные данные

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

В будущем, при любых изменениях в папке (добавлении-удалении городов) или в файлах (изменение количества строк) достаточно будет щелкнуть правой кнопкой мыши прямо по таблице или по запросу в правой панели и выбрать команду Обновить (Refresh) - Power Query "пересоберет" все данные заново за несколько секунд.

P.S.

Поправка. После январских обновлений 2017 года Power Query научился собирать Excel'евские книги сам, т.е. не нужно больше делать отдельную функцию - это происходит автоматически. Таким образом второй шаг из этой статьи уже не нужен и весь процесс становится заметно проще:

  1. Выбрать Создать запрос - Из файла - Из папки - Выбрать папку - ОК
  2. После появления списка файлов нажать Изменить
  3. В окне редактора запросов развернуть двойной стрелкой столбец Binary и выбрать имя листа, который нужно взять из каждого файла

И все! Песня!

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




Страницы: 1  2  
06.02.2017 07:42:48
Очень полезная статья, большое спасибо.
06.02.2017 08:28:36
Николай, отлично, что вы добрались до чудесного PQ! :)
Важное замечание - для 2010 эта надстройка тоже работает! Установочный файл тот жеще,  что и для 2013
06.02.2017 09:09:35
Полез уточнить - точно, с 2010 уже можно ставить. Спасибо, Максим - сейчас поправлю в статье :)
06.02.2017 22:55:59
За исключением Office 2010, установленного в Windows XP :(
06.02.2017 08:29:29
Спасибо. Power query есть для Excel начиная с 2010 года.
06.02.2017 09:09:57
Спасибо, сейчас подправлю. Владельцы старых версий обрадуются ;)
07.02.2017 04:24:49
Очень удобная вещь! Спасибо, Николай!
Подскажите пожалуйста, возможно ли подобным образом собирать данные не только из отдельных файлов, но и из папок. Объясню. Коллеги разносят файлы по папкам 01 Январь, 02 Февраль, и т.д. При этом важно чтобы данные обновлялись
Спасибо.
07.02.2017 10:05:33
Сергей, все совершенно так же. Указываете для сбора верхнюю папку в иерархии и Power Query соберет все файлы из всех подпапок автоматически.
08.02.2017 07:10:08
Николай, спасибо большое за оперативный ответ! Действительно все работает. В процессе применения данной волшебной функции возник вопрос: при сборе данных возможно ли каким-нибудь образом установить исключение повторов и еще -  из одинаковых строк выбрать строку с максимальным значением определенного критерия?
Спасибо.
07.02.2017 11:30:41
Надстройки Power (Query / Pivot / View) для Excel это хорошие инструменты уровня Self-Service BI, которые продвинутый аналитик может эффективно использовать в своей работе.8)

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

Если интересно, в качестве альтернативы данную задачу можно решить без функции, на эту тему рекомендую посмотреть видео от коллеги по вашему цеху: https://www.youtube.com/watch?v=tODq6yRVZl8

Еще, как правило, есть проблема в Power Query, что путь к файлам/папкам фиксируется в запросе, но это тоже решается – можно создать динамический путь примерно так: = Excel.CurrentWorkbook(){[Name="tPath"]}[Content]{0}[Path]

Если нужно будет кому-то, вот одно из видео на эту тему: https://www.youtube.com/watch?v=kRB6O2qxsIc

Вообще возможности ExcelЯ c надстройкой Power Query вдохновляют!

Например: долгожданные SQL JOIN-ы таблиц теперь можно делать с помощью Power Query. В ближайшее время планирую сделать статью и видео на эту тему и выложить себе в блог: http://biweb.ru/blog

Спасибо!
07.02.2017 17:18:02
Я работаю в Excel  13 у меня все получилось за исключением наз Месяцев данная вкладка не вылазиет нет её.
А так все очень круто запросы действиетльно очень полезные.
08.02.2017 09:00:49
Здравствуйте,
подскажите, пожалуйста, а как в шапке отобразить значки типа данных ('ABC', календарик, 'ABC 123') ?
08.02.2017 09:02:23
А у вас какая версия Excel?
08.02.2017 09:12:17
08.02.2017 18:39:22
Здравствуйте!
1. Николай, у вас в тексте написано: "первая строка (filepath)=> превращает нашу процедуру в функцию с аргументом filepath..", а на видео  вместо filepath указано filename.
2. Exel 2016 и тоже не показывает как у вас в шапке значки типа данных ('ABC', календарик, 'ABC 123')
3. а если такую функцию внедрить в Plex, на подобие плюшки в Plex "Сборка листов"?;)
09.02.2017 08:58:42
Николай, помогите пожалуйста. выпадает вот такое сообщение
[DataFormat.Eror] Не удалось выполнить синтаксический анализ входных данных, представленных в качестве значения даты.
подскажите пожалуйста как устранить данную проблему.
09.02.2017 21:47:57
Николай, а где книга Мастер формул?
18.02.2017 08:44:26
Выложил в раздел КНИГИ, можно брать ;)
18.02.2017 11:22:33
Спасибо за ответ. Купил уже, дня три как. Я такой радый!)
10.02.2017 10:31:14
Ура! Теперь надо обязательно ставить обновление от Января 2017. Вот что пишут:

Enhanced “Combine Binaries” experience when importing from any folder

Before this release, users could combine Text or CSV files only. The combine would not work for any other supported file formats (such as Excel Workbooks, JSON files, etc.), and it would not account for transformations required on each file before combining them into a single table (such as removing the first row with header values).

With this release, we enhanced the “Combine Binaries” experience when importing from any folder so that:
  • Excel analyzes the input files from the Folder query and detects the right file format to use (i.e., Text or Excel Workbook).
  • Users can select a specific object from the list (such as a spreadsheet name) to use for data combine.
  • Excel automatically creates the following entities
https://blogs.office.com/2017/02/08/january-2017-updates-for-get-transform-in-excel-2016-and-the-power-query-add-in/

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

Однако (на моей работе) при выгрузке файлов из 1С в формате .xlsx, Power Query всё равно не может их проглотить, пока просто не пересохранишь файл в самом Excel (открыть файл, сохранить, закрыть).
10.02.2017 12:12:36
Твою дивизию! Только что поставил обновление и попробовал: все файлы загрузились на раз напрямую без функции - в два щелчка. Офигеть. Они делают апдейты быстрее, чем я пишу статьи :)

Спасибо, Василий! You made my day! :)
17.02.2017 21:18:26
Простите за вопрос. А что принципиально поменялось (улучшилось) после этого обновления?
как теперь лучше решать эту задачу?
18.02.2017 08:43:20
Не нужно корректировать запрос на языке М, чтобы превратить его в функцию. Т.е. Шаги 1 и 2 не нужны, можно сразу с шага 3 начинать - и все ОК.
22.02.2017 17:25:24
Огромное спасибо за статью, как раз тему осваиваю.
А можно в студию версию сборки Excel 2016 после установки январского обновления?
23.02.2017 00:15:22
В смысле? Перезаписать видео?
27.02.2017 10:22:59
Нет, просто, написать в сообщении какая у вас теперь версия Excel стала после установки январского обновления в разделе "О программе". У меня сейчас установлена версия офиса 16.0.4266.1001. Мне это нужно для описания АйТишникам на работе, до какой версии мне надо офис обновить.
10.02.2017 15:54:16
Я это делаю с помощью надстройки Plex:
1.Сборка листов
2.Собрать на итоговый лист
Остается только через фильтр удалить заголовки...
12.02.2017 10:14:11
Это да :) Но PLEX:
  • будет собирать дольше
  • не умеет обновлять собранные данные (нужно будет пересобирать)
  • не умеет запоминать цепочку действий по причесыванию данных
Так что Power Query в этом вопросе однозначно круче.

Зато PLEX умеет разбирать одну таблицу по разным листам, а PQ - пока нет :)
08.03.2017 11:43:08
Николай, добрый день

Подскажите а как быть когда при добавлении в папку нового файла с данными, при обновлении запроса Query задваиват данные
12.02.2017 01:44:42
Николай, доброго времени суток. Прошу уточнить: на видео при внесении поправок в расширенном редакторе Вы вписываете filename, а в описании данного примера -filepath. Это не ошибка? Спасибо
12.02.2017 10:11:45
Олег, это просто имя переменной - оно может быть абсолютно любым, хоть ABCD. Главное, чтобы в первой строке и в аргументе File.Contents они совпадали.
14.02.2017 17:04:28
Николай, добрый день. Не подскажете - имеется ли где информация/инструкция как подключаться к Базам данных. Например если 1с базируется на SQL Servere. Я тестово пробовал с IT специалистом провести подключение, однако инструментарий PQ почему то не принимал учётные данные, пробовали обойти - выдаёт ошибку подключения.
19.02.2017 10:58:58
Николай, можно сделать гораздо проще, без запроса.;)
Таблица
19.02.2017 12:24:33
Так стало возможно только после январского-2017 обновления Power Query - почитайте комменты выше, уже писали про это :)
19.02.2017 15:18:02
этот прием я нашел на видеоот 04.06.2016 (с 30:47), при чем тут январское обновление :o
19.02.2017 21:12:44
Кнопка "Развернуть" на столбце с Binary раньше работала только для импорта CSV файлов, не XLSX. Разворачивать-подставлять содержимое Excel-книг с выбором конкретного листа стало возможным только после январских обновлений Power Query - гляньте их официальный блог.
26.02.2017 20:29:02
Николай, добрый день.
Случайно не подскажете, как с помощью vba кода потом удалить все запросы из полученной книги?
Макрорекордер не чирикает...
26.02.2017 21:08:53
Уже нашел, спасибо.
Вот такой код:

Private Sub DeleteQuery()
Dim wSh As Worksheet
Dim lo As ListObject

    On Error Resume Next
    ActiveWorkbook.Queries("Test").Delete
    For Each wSh In ActiveWorkbook.Worksheets
        For Each lo In wSh.ListObjects
            If lo.Name = "Test" Then
                Application.DisplayAlerts = False
                wSh.Delete
                'wSh.ListObjects("Test").Delete
            End If
        Next lo
    Next wSh
    On Error GoTo 0
    
End Sub
 
Имя запроса можно легко посмотреть и вставить в код.
01.03.2017 14:44:50
 Подскажите пожалуйста, сделала все по инструкции -  однако не загружаются на самом последнем этапе все данные и выходит вот такая ошибка: [Dataformat Error] Внешняя таблица не имеет предполагаемый формат

Во всех файлах дали одинаковое наименование листу для выгрузки. Что-то все ранво не так.

заранее огромное спасибо за помощь
09.03.2017 08:50:34
Не видя файлов, сказать невозможно. Совет один - проверяйте файлы. Может быть столбцы по-разному называются или перставлены, возможно есть скрытые столбцы или строки.
07.03.2017 21:56:35
Я правильно понимаю,  листы должны иметь одно имя? А что делать если в папке таблицы одинаковые, а листы с разными именами?
09.03.2017 08:49:10
А как Power Query поймет какой именно лист вам нужен? Телепатически? :)
14.09.2018 19:23:29
Вопрос актуален :-)
Есть какой-то способ обращения к листу не по его названию, а по номеру? Как в VBA.
17.09.2018 23:10:06
Решение найдено.
Имя листа можно передавать вторым параметром
(filepath, shname) =>
Предварительно в этой же таблице с путями, отдельной колонкой можно завести имена листов.
08.03.2017 17:37:08
Кстати, заметил такое, что в Power Query не выходит заранее задать взаимосвязи между кучей таблиц и надергать в один запрос данные из десятка взаимосвязанных источников, как я привык это делать в Access. Учитывая что я только начал, может я куда-то не туда смотрю и такой функционал имеется?
23.03.2017 10:09:47
Все ваши таблицы добавляете через PQ в модель данных и в power Pivot  через представление данных в виде диаграммы связываете таблицы добавленные через PQ  как надо. Потом можно построить сводную таблицу. Конкретнее смотрите тут и тут. Удачи.
27.03.2017 16:10:12
1. в самом начале правки, где добавляем столбец с месяцем, можно ли прописать формулу в новом столбце для обработки данных в другом? не могу найти такого функционала. есть вызов настраиваемой функции, но форма не дает мне выбрать функцию
2. можно ли настроить автоматическое обновление сборной таблицы? или каждый раз при добавлении файлов нужно обновлять вручную?
27.03.2017 17:19:21
1. Можно добавить вычисляемый столбец с формулой
2. Насколько я знаю, да - надо руками обновлять.
27.03.2017 17:45:35
у меня только
-пользовательский столбец
-вызвать настаиваемую функцию
-условный столбец
-столбец индекса

мне нужна функция поиска, нигде из этих столбцов я не могу ее описать
вычисляемого столбца не нашел
29.03.2017 10:19:40
Николай, добрый день! Подскажите пожалуйста как добавить столбец и внести во все его строки значение из единственной ячейки в заголовке таблицы (своего рода абсолютная ссылка)?
Спасибо.
03.04.2017 09:02:41
Добрый день! Скажите, пожалуйста, есть ли возможность сослаться на несколько листов одного файла одновременно?
Спасибо!
09.04.2017 17:35:58
Павел, доброе время суток!!! Одни ваши видео уроки мне позволили подняться на несколько голов выше тех, кто получает деньги за работу с Excel,  при этом не умеют работать с ним и вообще не представляют, что Excel  это не тетрадка в клетку и без полей, а нечто большее!!!
Спасибо большое!!!
У меня Excel 2016
Я работаю со штрихкодами (12цифр), для удобства я ставлю формат Дополнительный(табельный номер), отчёты мне приходят ежедневно и хочу собрать их в один файл с помощью Power Query.
И возникает проблема.
Первый  столбец – вводятся данные с помощью сканера штрихкодов и они переносятся корректно.  
Второй Столбец образуется из первого по формуле «ВПР»  и «Еслиошибка» (появляется значение «продан»).
Вот с ним и возникает проблема, данные переносятся не корректно, а именно округляются :
122040897832 - 122040897830
118060007467 - 118060007470
155050192073 - 155050192070

И так далее…

И вопрос №2,
Power Query – делает свою «умную таблицу», а я хотел бы что бы он переносил данные в мою «умную таблицу» где уже прописаны формулы в дополнительных столбцах  и идёт сразу же обработка этих данных.

Помогите пожалуйста!!!:)
11.04.2017 21:49:04
Сделал в этих же файлах, но с маленьким объёмом данных - получилось всё чётко!!!:D где-то лажа с большими файлами, завтра буду снова их полдня "мурыжить" !!! :)))) думаю всё получится!!! :)))) :D
22.04.2017 10:27:05
1. Попробуйте текстовый формат
2. Или писать ваши формулы рядом с умной таблицей PQ или вытаскивать данные из таблицы-результата в вашу функциями типа ВПР (VLOOKUP).
18.04.2017 06:23:46
Доброго времени суток! Скажите, пожалуйста, можно ли использовать несколько источников данных для одной книги? Например, "связать" несколько листов одной книги с различными базами данных.
22.04.2017 10:24:28
Да, конечно. Сделайте несколько запросов в одной книге - без проблем.
19.04.2017 19:26:47
Добрый день! Работает ли PQ, если некоторые столбцы в каком-нибудь из файлов скрыты? сгруппированы?
22.04.2017 10:23:52
Скрытие не помешает, а вот группировка - это проблема. Особенно - в шапке таблицы.
28.04.2017 09:56:56
Подскажите пожалуйста. У меня Excel 2010. Установил надстройку PQ. При выполнении шага 1. в момент импорта первого файла в качестве образца все зависает и висит пока не убьешь процесс...  в чем может быть дело?
сразу оговорюсь, что таблица сложная много сгруппированных строк и столбцов в шапке, также есть формулы сумм в некоторых столбцах. И количество строк порядка 1500 шт.
Николай, добрый день!
Если есть время помогите или направьте в нужном направлении. Есть файл банковских операций до 01.05. и файлы обновлений. Слияние на один лист, к примеру "Альфа", исходного файла и обновлений из папки я настроил. Теперь пробую сделать так чтобы информация на листе "Альфа" сохранялась даже при удалении выписок, т.е. чтобы конечный лист помнил свое текущее состояние и обновлялся только в случая поступления новых выписок. Power Query такое конечно позволяет делать результат мне выгружает в новый лист, так как выгрузка в текущую таблицу данных запрещена.
Спасибо!
25.05.2017 15:10:49
Николай, добрый день.
Подскажите, как можно сделать слияние, если во всех файлах отличается имя листа (каждый раз генерится новое имя) а структура данных абсолютно одинакова. Я так понимаю нужно делать 2 запроса и превращать их в функцию: 1-й для файла, 2-й для имени книги и подставлять их в итоговый большой запрос?
13.11.2017 09:30:37
В двух словах не объяснишь.
Но идея в том, что надо сначала собрать сами файлы в Power Query, а потом применить к ним функцию =Excel.Workbook(), чтобы вытащить из каждого весь список листов (независимо от их имен).
А потом уже разворачиваем содержимое листов и работает с данными.
Как-то так, если на пальцах.
Страницы: 1  2  
Наверх