Изявление на проблема

Нека да разгледаме един красив решение за много често срещани ситуации, които рано или късно се сблъсква по-голямата част от потребителите на Excel трябва бързо и автоматично събиране на данни от голям брой файлове в обобщена таблица.

Да предположим, че имаме такова папка, която съдържа няколко файлове с данни от клоновете си градове:

Броят на файловете не играе ролята, и може да се промени в бъдеще. Всеки файл има списък с име-продажба. където има таблица с данни:

Броят на редовете (разпореждания) в таблиците, самостоятелно един, различни, но всички стандартен набор от колони.

Цел: Да се ​​съберат данни от всички файлове в една книга и след това да се актуализира автоматично, когато добавяте, изтривате дублиращи се файлове, градове или редове в таблици. По консолидираната финалната маса след това може да се изгради всички отчети, електронни таблици филтрират, сортират данни и т.н. Основното нещо - да бъде в състояние да събере.

Вземете оръжия

Стъпка 1: импортирате един като модел

Първо, нека да импортирате данни от една книга като пример за Excel "се е ускорил тази идея." За да направите това, създайте нова празна работна книга, и.

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

• филтриране на ненужни данни, празни редове, за грешките
• сортиране на данни от една или повече колони
• отърве от повторения
• Споделете аглутинира текстови колони (за сепаратори, броят на знаците, и т.н.)
• предизвика текстът, с цел (за отстраняване на излишни интервали, правилно регистър и т.н.)
• по всеки възможен начин за преобразуване на типове данни (числа като текст, за да превръщат в нормални номера или обратното)
• транспониране (завърта) двумерен маса и разширяване на напречното таблицата в плосък
• за добавяне на допълнителни колони в таблицата и да ги използвате, за да формули и функции, вградени в Ел Query Language М.
• .

Например, нека да добавите колона, за да ни маса с текстово име на месеца, така че по-късно, че е по-лесно да се изгради отчети с обобщени таблици. За да направите това, щракнете с десния бутон върху заглавната колона Дата и изберете Duplicate колона (Дублиране на колоната). и след това щракнете с десния бутон върху заглавието на колона, която се появява дублира и натиснете Convert - Месец - Най месец.

Трябва да се образува нова колона с текст име на месеца за всяка линия. Двукратно щракване върху заглавката на съответната графа, можете да го преименувате от датата на копие е по-удобно месец. например.

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

Изключване на линия грешки или празен низ, както и ненужни мениджъри или клиенти може да бъде обикновен филтър:

И всичко това прави преобразуването се записват в десния панел, където те винаги може да се навива обратно (X) или да промените настройките си (зъбни):

Лесна и елегантна, нали?

Стъпка 2. Трансформиране на функцията в нашето проучване

Впоследствие да се повтаря всички направени преобразуване на данните за всяка внесена книга, трябва да конвертирате ни изработени искане до функцията, която ще се използва, от своя страна, на всички наши файлове. За да направите това, в действителност, много проста.

Сега внимателно ние правим няколко редакции:

Тяхното значение е проста: на първия ред (filepath) => Оказва процедурата ни функция с аргумент filepath. и по-долу, ние се промени фиксираната пътя на стойността на тази променлива.

All. Натиснете Finish, и трябва да видите това:

Не се притеснявайте, че липсващите данни - всъщност, всичко е наред, всичко е както трябва да изглежда така :) Ние успешно създадохме нашата потребителски дефинирана функция, която си спомни целия алгоритъм и вносни данни, без да бъде обвързана с конкретен файл. Остава да му се даде по-смислено име (напр getData) в десния панел, в полето Име и можете да натиснете Начало - Затвори и товар (Начало - Затвори и Load). Имайте предвид, че в кода твърдо кодирана пътя към файла, който ние, внесени например. Можете да се върнете към основния прозорец на Microsoft Excel, но трябва да се появи в десния прозорец, за да създадете връзка към нашата функция:

Стъпка 3: Съберете всички файлове

Сега трябва да добавите към нашата маса друга колона с нашите създават функция, която "дръпне" данни от всеки файл. За да направите това, отидете на раздела Добавяне на колона - по поръчка колона (Добавяне на колона - Добавяне на персонализирана графа), и в прозореца, който се появява, влезте в нашата функция getData. посочвайки го като аргумент пълния път до всеки файл:

След като щракнете върху OK, за да създадете колона, за да добавите към нашата маса в дясно.

Сега премахнете всички ненужни колони (както за Excel, с десния бутон на мишката - Изтриване), оставяйки само добавяне на колона и колона с името на файла, като е името (или по-скоро - в града), ще бъде полезно да има окончателни данни за всеки ред.

И сега "уау момент" - кликнете върху иконата с присвояването на стрелките в горния десен ъгъл на колоната добавен към нашия функция:

премахнете отметката Използвайте оригиналното име на колоната като префикс (Използвайте оригиналното име на колона като префикс) и кликнете върху OK. И нашата функция за зареждане и процес на данни от всеки файл, като следвате алгоритъма записва и събра всички в цялата таблица:

За пълната красота все още могат да се отстранят .xlsx разширение от първата колона с имената на файловете - стандартът променено на "нищо" (щракнете с десния бутон върху заглавието на колоната - Сменете) и преименуване на колоната в града. А също и правилен формат на данните в колоната с датата.

Всичко! Кликнете върху основните - Затвори и товар (Начало - Затвори Load). Всички данни, събрани чрез задаване на всички градове, ще бъдат разтоварени в Excel лист в сегашния формат "интелигентна маса":

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

В бъдеще, всяка промяна в папката (добавяне, изтриване градове) или във файлове (промяна на броя на редовете), ще бъде достатъчно, за да щракнете с десния бутон директно върху масата или заявка в десния панел и изберете Refresh (Обновяване) - Мощност Заявка "наново вашия" всички данни отново след няколко секунди.

Сродни връзки

Засилено "Комбинирайте Binaries" опит при импортиране от някоя папка

Преди тази версия, потребителите могат да комбинират текст или CSV файлове само. Комбайна няма да работи за други поддържани файлови формати (като Excel работни книги, JSON файлове и т.н.), и това не би представлявало трансформации, необходими на всеки файл, преди те да бъдат обединени в една обща маса (като отстраняване на първия ред с колекторни стойности).

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

Въпреки това (в моята работа), когато качите файловете на 1C в .xlsx формат, Мощност Query все още не може да ги погълне, докато просто не запазете файла в Excel (отворен файл, освен, в близост).

Вашият разделение! Просто сложете актуализацията и се опита: всички файлове заредени в пъти директно без функция - в две кликвания. За да получите. Те правят актуализации по-бързо, отколкото аз пиша статии

Благодаря ви, Базил! Вие ми ден!