Обединяване на работен лист в Excel
Обединяването на множество листове в един работен лист един по един е трудна задача, но за щастие имаме функция, наречена „Consolidate“ в Excel, а също и от Excel 2010 нататък, можем да използваме „Power Query“ като сливане на работен лист. В тази статия ще ви покажем как да обедините работни листове в едно.
Получаването на данните в множество работни листове е доста често, но комбинирането на всички данни на работния лист в един път е работа на човека, който получава данните в различни листове.
Работен лист за сливане с помощта на консолидирана опция
Най-лесният и бърз начин за обединяване на множество данни от работни листове в един е чрез използването на вградената функция на Excel „Консолидиране “. Например, погледнете данните по-долу в Excel листове.
В горното изображение имаме четири работни листа, които се състоят от четири различни регионални продажбени номера за месеци.
От горните четири листа трябва да създадем един единствен лист, който да показва всички обобщени резултати. Следвайте стъпките по-долу, за да консолидирате работни листове.
Стъпка 1: Създайте нов работен лист и го наречете „Консолидиран лист“.
Стъпка 2: Поставете курсора в първата клетка на работния лист, отидете в раздела ДАННИ и кликнете върху опцията „Консолидиране“.
Стъпка 3: Това ще се отвори под прозореца „КОНСОЛИДИРАНЕ“ .
Стъпка 4: Тъй като обединяваме всички 4 регионални данни, изберете опцията „SUM“ под падащия списък с функции в Excel.
Стъпка 5: След това трябва да изберем референтния диапазон от първия лист до последния лист. Поставете курсора си в полето за справка, отидете на листа EAST и изберете данните.
Стъпка 6: Щракнете върху бутона „ДОБАВЯНЕ“, за да добавите първата референтна област.
Сега това се добавя към референтния списък.
Стъпка 7: След това просто трябва да отидете на листа „Юг“ и референтният диапазон би бил избран автоматично.
Стъпка 8: Щракнете отново върху „ДОБАВЯНЕ“ и втората справка за листа се добавя към списъка. По този начин повторете същото за всички листове.
Сега сме добавили всички 4 листа с препратки. Още нещо, което трябва да направим накрая, е докато избираме всеки диапазон от региони, който сме избрали, включително заглавката на реда и заглавката на колоната на таблицата с данни, така че за да внесете същото в консолидирания лист, поставете отметки в квадратчетата „Top Row“ и „ Лява колона “ и поставете отметка в квадратчето „ Създаване на връзки към изходни данни “.
Добре, щракнете върху „Добре“ и ще имаме обобщена таблица като по-долу.
Както можете да видите по-горе, имаме две групирани номера на листа като 1 и 2. Ако кликнете върху 1, той ще покаже цялата консолидирана таблица на региона, а ако щракнете върху 2, ще покаже разбиването на всяка зона.
Изглежда добре, но това не е вид обединяване на работни листове, така че всъщност обединяването комбинира всички работни листове в един без никакви изчисления; за това трябва да използваме опцията Power Query.
Обединете работни листове с помощта на Power Query
Power Query е добавка за версии на Excel 2010 и 2013, а също така е вградена функция за версии на Excel 2016 и по-нови.
Отидете в раздела Данни и изберете „ Получаване на данни“ от това, изберете „От файл“, след това от Работна книга на Excel.
Изберете листа, след което го преобразувайте в редактор на заявки за захранване.
За това трябва да преобразуваме всички таблици с данни в таблици на Excel. Преобразувахме всяка таблица с данни в таблица на Excel и я наименувахме по имената на регионите им като Изток, Юг, Запад и Север.
Първо отидете на някой от листовете, под Power Query, щракнете върху „Добавяне“.
Сега това ще отвори прозореца „Добавяне“ .
Тук трябва да обединим повече от една таблица, така че изберете опцията „Три или повече таблици“ .
Изберете таблицата „Изток“ и кликнете върху бутона „Добавяне >>“ .
Направете същото и за други таблици на региони.
След това просто кликнете върху „Ok“, ще се отвори прозорецът „Power Query Editor“.
Кликнете върху опцията „Затваряне и зареждане“.
Това ще обедини всички листове в един в нов работен лист на същата работна книга.
Неща за запомняне
- Power Query в Excel е наличен за Excel 2010 и 2013 като добавка, а от Excel 2016 нататък това е вграден раздел.
- Consolidate се използва за обединяване на различни работни листове в един, базиран на аритметични изчисления.
- За сливането на Power Query трябва да преобразуваме данните във формат на таблица на Excel.
