Светлана

Добавлено 07.01.18, 10:51 в УРОКИ EXCEL

Сборка таблиц из разных файлов Excel Как быстро и с автоматическим обновлением собрать данные из нескольких файлов Excel в один с помощью Power Query. Файл-пример можно скачать тут http://www.pl...  Подробнее »

 

Оставили «Спасибо»

Сохранили к себе

и ещё 1 человек
Текст из видео:
  • 00:00: здравствуйте меня зовут николай павлов я тренер и эксперт по продуктам microsoft office и по excel в частности а также автор сайта планет excel и двух книг по microsoft excel microsoft excel готовое решение бери и пользуйся и microsoft excel мастер формул где вы можете найти массу полезной информации по работе в этой программе для пользователей любого уровня в этом видео уроке я хотел бы наглядно показать как можно быстро и красиво собрать информацию из любого количества файлов excel в единое
  • 00:32: целое используя функционал надстройки power клэри парк вере это бесплатная отстройка от компании microsoft которую вы можете в любой момент скачать и установить на 2010 или 2013 версию microsoft excel если у вас excel 2016 последней версии то ничего скачивать и устанавливать не нужно в последнюю версию весь функционал этой настройки встроен по умолчанию итак предположим что у нас
  • 01:00: есть большое количество файлов какое конкретно в принципе роли не играет расположенных где-то в одной из папок у меня это папка отчеты на диске d в принципе внутри эти файлы могли бы быть разложены еще и по подпапкам например по месяцам или кварталам это тоже роли не играет сбор всех файлов из всех подпапок будет произведен полностью автоматически внутри файл и представляют из себя вот такие вот
  • 01:31: таблицы расположенные на листах продажи количество строк в каждом файле естественно может меняться но количество столбцов и общая структура таблицы везде идентичны наша задача собрать все файлы в одну большую общую таблицу с тем чтобы потом по ней можно было строить отчеты сводных таблиц считать какую-то аналитику прогнозировать продажи и так далее создадим новую пустую книгу в excel весь
  • 02:04: процесс сбора данных условно можно поделить на три этапа сначала мы импортируем один файл в качестве примера затем преобразуем запрос функцию и применим ее при импорте всех файлов из нашей папке я буду показывать импорт на последние 2000 16 версии microsoft excel если у вас более ранние версии то после установки надстройки power клэри должна появиться вкладка парк вере и все дальнейшие
  • 02:30: действия будут абсолютно идентичными в 2016 excel весь функционал по ур клэри находится на вкладке данные и так откроем новую пустую книгу в excel и на вкладке данные развернем выпадающий список создать запрос здесь много разных вариантов и источников данных выберем вариант из файла и из книги excel в открывшемся окне откроем папку где лежат наши отчеты
  • 03:02: выберем любой файл для импорта в качестве образца в появившемся окне навигатора выберем лист с которого нужно взять данные из каждого файла паркуре отобразит нам предварительный просмотр данных если сейчас нажать на кнопку загрузить то данные будут сразу загружены на лист мы же хотим как-то причесать или преобразовать их в процессе импорта и потом превратить эту
  • 03:30: функцию поэтому жмем на кнопку правка открывается окно редактора запросов parkway основной инструмент в котором можно проделывать огромное количество операций над данными склеивайте расклеивать столбцы и причесывать текст убирать дубликаты отфильтровывать строчки с ошибками с пустыми значениями преобразовывать типы данных и так далее огромное количество инструментов в этом окне для примера давайте добавим допустим столбец в
  • 04:00: котором будет название месяца это очень полезная колонка если мы планируем в будущем анализировать эти данные сводные таблицы для этого щелкнув правой кнопкой мышки по столбцу дата выберу команду создать дубликат столбца получаются копия колонки и кстати можно спокойно смотрите перетаскивать куда нам нужно и затем правой кнопкой мышки выбираю еще раз команду преобразования месяц
  • 04:34: название месяца вот так легко двойным щелчком по заголовку любой столбец можно переименовать если данный в исходных таблицах не очень правильные как часто бывает the power кори может не всегда распознать корректно тип данных поэтому в левом верхнем смотреть углу каждого столбца есть вот
  • 05:00: такой значок которая позволяет выбрать правильный формат дата деньги процент и время и так далее плюс ко всему если в наших исходных данных есть ошибки или пустые строки которые потом тоже помешают при анализе данных то совершенно спокойно можно их здесь отфильтровать смотрите привычный язык селе фильтра можно снять галки и исключить например некоторые компании из импорта или удалить пустые или удалить ошибки то
  • 05:31: есть это делается очень очень легко ну хорошо предположим это все что мы хотели сделать данными таблицы корректно и правильно и подобным же образом мы хотим импортировать потом данные из всех остальных файлов вот чтобы это можно было сделать наш запрос который как видите вот здесь справа описывается в виде набора шагов нужно превратить функцию от вязов вот эту цепочку действий от конкретного файл для
  • 06:01: этого идем на вкладку просмотр и открываем расширенный редактор запросов дело в том что внутри парк вере работает свой встроенный внутренний язык программирования это не visual basic это язык который называется м и все наши действия по импорту и преобразованию данных как раз и записываются на этом языке то есть это что-то вроде макро рекордер своего рода чтобы отвязать процедуру импорта от нашего конкретного
  • 06:30: файла барнаул и сделать универсальные функции добавляем две небольшие правки в начало дописываем вот такую строку по сути эта команда превращает наш запрос функцию с аргументом file names переменной файлы и затем меняем конкретный путь к файлу вот здесь
  • 07:01: скобках на имя нашей переменной все это все что нужно сделать для преобразования нашего запроса функцию нажимаем готово не пугайтесь того что увидите на этом шаге тут визуализация достаточно скромная ну и я бы может быть еще поменял все таки имя нашей функции на
  • 07:30: какой-то более понятно и но давайте назовем скажем ее to get by это все возвращаемся на главную вкладку жмем кнопку закрыть ее загрузить и возвращаемся в excel справа должна быть видна наша созданная функция теперь можно переходить к последнему этапу и импортировать все файлы из папки для этого также идем на вкладку данные или вкладку паркуре если у вас установлена деле настройка еще
  • 08:00: раз выбираем создать запрос но на этот раз уже не из конкретного одного файла а из папки то есть просим паркуре импортировать данные из всех файлов заданной папке открывшемся окне выбираем путь к папке у нас напоминаю это был диск d и папка отчеты нажимаем ok парк вере перебирает все вложенные папки и файлы и выводит их
  • 08:31: список нажимаем кнопку изменить и опять попадаем в привычное но редактора запросов обратите внимание что сейчас мы видим не содержим и импортируемых файлов просто их список с именами датами создания изменений и так далее причем обратите внимание на два столбца на столбец name и на столбец folder pads если сцепить эти два столбца то получится
  • 09:01: полный путь к файлу чтобы извлечь данные из каждого файла по такому полному пути нам как раз и потребуется наша созданная на предыдущем шаге функция get это добавим еще один пользовательский столбец и в качестве формулы как раз и укажем нашу функцию get дейта а в скобках в качестве аргументов для этой функции укажем значение двух столбцов не задаются в квадратных скобках folder of путь к файлу и в
  • 09:36: квадратных скобках name значение этих двух столбцов склеиваются при помощи стандартного для excel символа амперсанд нажимаем ok и получаем еще один столбец паркуре услужливо подсказывает нам что тип данных для каждой ячейки это ты был . 3 каждой ячейке находится таблица с
  • 10:00: исходными данными из наших файлов то есть то что как раз и требовалось собственно все не нужные столбцы можно спокойно удалить правой кнопкой мышки и наверное я вот оставлю еще столбец с именем для того чтобы потом было понятно из какого файла какие строки попали в итоговый отчет чтобы избавиться от расширения . xlsx
  • 10:31: можно использовать классический трюк с заменой правой кнопкой мышки щелкаю по заголовку столбца выбираю команду замена значений и этот стандартный трюк из excel которые я думаю многие из вас использовали заменить что-то на ничего заменить . x или секс на пустоту нажимаю ok и в итоге получаю очищенный от расширение столбец ну а теперь главное магическое движение щелкаем вот по этой двойной стрелки в правом верхнем углу
  • 11:00: нашего добавленного столбца эта команда которая разворачивает содержимое каждой ячейки и в данном случае снимаем здесь внизу галочку использовать исходный местам со как префикс нам это не нужно нажимаем окей тогда и получаем сюда собраны все данные из всех файлов обработанные в соответствии с той функций который мы перед этим дали как образец на практике естественно имеет смысл проверить то что получилось хотя бы навскидку не
  • 11:30: следить ошибок и возможно добавить какие-то мелкие преобразования я вот дом случае вижу что столбец с датами как вере не воспринял формате даты обратите внимание что в углу стоит значок текста поэтому имеет смысл наверное щелкнуть и поменять формат на дату в остальном по моему все хорошо возвращаемся на вкладку главное и нажимаем закрыть и загрузить excel загружает в одну общую таблицу все
  • 12:01: данные и из всех наших файлов если в будущем в папке или внутри файлов будут какие-то изменения то есть до песчаные или удалены строки или добавлены новые файлы то не надо естественно будет повторять весь этот процесс достаточно будет щелкнуть или по таблице правой кнопкой мышки выбрать команду обновить или вот здесь вот справа в панели запросов также щелкнуть правой и выбрать эту команду при щелчке xl смотрите
  • 12:30: автоматически пей собирает данные из всех файлов причем это происходит очень и очень быстро вот так совершенно шикарный инструмент о том как это делать вручную не хочется даже думать правда когда знаешь все просто приходите наши тренинги которые мы проводим по microsoft excel и другим программы пакета office для компаний и частных лиц подробности на сайте планета excel в разделе тренинги ну и подписывайтесь на наши новости на этом канале youtube в
  • 13:01: твиттере и на фейсбуке или читаете наши новинки по rss