Excel Consolidation (Съдържание)
- Въведение в консолидацията в Excel
- Как да консолидирам данните в няколко работни листа?
Консолидиране на данни в Excel
Консолидирането като цяло означава събиране или събиране на някои общи неща на едно място. Това може да бъде всякакъв вид данни или нещо като информация или номер. Консолидирането в Excel се отнася до функция, която комбинира информация или данни от различни работни книги / работни листове в един работен лист / работна книга. С други думи, тази функция взема данни от различни работни листове, за да ги комбинира в един работен лист, който може лесно да се актуализира.
Например, ако имате четири работни листа. Всеки лист има разходи за една четвърт. Може да ви е необходим един лист с разходите за четирите тримесечия. Това е, когато функцията за консолидация влиза в игра. Когато данните в работни листове са в една и съща позиция, което означава под едни и същи етикети и в същия ред. Използваме „Консолидация по позиция“. И когато данните в работни листове не са под едни и същи етикети и не в същия ред. След това използваме „Консолидация по категория“. Това е точно като създаване на Pivot таблица.
Как да консолидирам данните в няколко работни листа?
Нека разберем как да консолидираме данните в няколко работни листа с няколко примера.
Пример №1 - Консолидиране на данни в същата работна книга
Да речем, че имаме работен лист, в който имаме данни за продажбите с четири работни листа, маркирани според региона им като Север, Юг, Изток и Запад. Сега бихме искали да обединим данните на едно място, а не в лист в рамките на една и съща работна книга. Има пети лист, наречен консолидиран файл.
Този пример ще покаже консолидираните продажби за всички региони. Ето примерните файлове с данни. Тук можете да видите различните имена на работния лист и последния консолидиран файл, върху който трябва да работим.
Това е нашият шаблон в лист „консолидиран файл“ и сега ще започнем да консолидираме данни от работните листове.
Сега ще кликнем върху клетка B3.
Когато искаме функцията „Консолидация“ да вмъква данните от други листове. Както можем да видим по-горе, клетка B3 е избрана и сега ще преминем към раздела Данни в лентата на Excel и ще преминем към Consolidate.
След като щракнем върху Консолидиране, по-долу ще се появи прозорецът:
Първото нещо, което разглеждаме, е падащото меню „Функция“, което показва много аритметични функции като сума, броене, макс, средна стойност и т.н. Тъй като искаме сума от продажби, ще изберете „Сума“ в падащото меню. Сега ще преминем към раздела за справка, за да добавим препратка към нашите данни от различни работни листове. След това ще преминем към първия ни лист, който съдържа данните за продажбите на North. Остава само да подбираме данни за продажбите, а не заглавията и редовете. Това е показано по-долу.
Референцията е показана в полето „Справка“ по този начин.
Щракнете върху „Добавяне“ и референцията ще бъде добавена в полето „Всички справки“.
По същия начин сега ще добавим препратка от всички други листове като Север, Юг, Изток и Запад. След като приключим с добавянето на препратки, щракнете върху „OK“.
Сега ние консолидирахме данни за продажбите на ръководителите месечно, на национално ниво.
Пример №2 - Консолидиране на годишните продажби на продукта
В това отношение ние имаме четвърти разумни продажби на продукти A, B, C и D и искаме консолидирани годишни продажби на продукти.
Тук имаме данни в четири листа, първи лист с данни за продажбите за Q1, след това има данни за Q2, последвани от данни за Q3 и Q4. Сега ще отидем в раздела за справка, за да добавим препратки към нашите данни от различни работни листове. Ще преминем към първия ни лист, който съдържа данните за продажбите за Q1. Ще изберем данните, както са показани по-долу.
Сега ще отидем в раздела Данни и щракнете върху Консолидиране и по-долу ще се появи прозорецът.
Ще щракнете върху „Добавяне“ и референцията ще бъде добавена в полето „Всички справки“.
Ще щракнете върху „Добавяне“ и референцията ще бъде добавена в полето „Всички справки“.
Ще щракнете върху „Добавяне“ и референцията ще бъде добавена в полето „Всички справки“.
По-долу е нашия шаблон за консолидирания информационен лист. Сега ще изберем клетка B2, за да получим общите данни за продажбите от други листове.
Ще изберем функцията „Сума“ от падащото меню. По същия начин ще добавим препратки от листове Q2, Q3 и Q4. Ще изглежда така. Всички референции от всички листове вече са добавени. Кликнете върху „OK“
Вече разполагаме с нашите консолидирани годишни данни за продажбите с общата сума за всеки продукт.
В случай, ако искаме да вмъкнем консолидираната таблица в празен работен лист, вместо да правим таблица с шаблони. Можем да го направим и ние. Като вземем предишните си примерни данни, ще направим консолидацията по-долу.
Нямаме шаблон за таблица за консолидация и искаме да получим консолидирани данни в празен работен лист с етикети на редове и колони. Ще добавим нов работен лист, в нашия случай това е „Консолидиран файл“.
Сега като преди ще отидем в раздела Данни Сега ще изберем референцията от нашите таблици с данни. Ще започнем това с лист „Север“ и след това ще продължим с листове „Юг“, „Изток“ и „Запад“. Ще изберем данните, както са показани по-долу, които включват етикети на редове и колони. След това ще добавим референцията в полето "Всички препратки": Сега добавете всички препратки по същия начин от всички таблици с данни. Сега ще проверим и „горния ред”, и „лявата колона” и след това натискаме OK. Сега виждаме консолидирани данни за продажбите с етикети на редове и колони. Ако искате да актуализирате консолидираните данни, когато отделният лист се актуализира, щракнете върху полето „Създаване на връзки за създаване на данни“. Ако искате да актуализирате данните ръчно, не поставяйте отметка в квадратчето и щракнете върху OK. Клетките в консолидираната таблица сега съдържа формулата на сумата, както е показано по-долу. Това го прави динамичен по природа. Това е таблица за динамична консолидация. Ако имаме данни в различни работни книжки и искаме да ги консолидираме в една работна книга. Ние също можем да направим това. Това ни дава голяма гъвкавост и лекота. Имаме тримесечни данни за продажбите на различни продукти, като например A, B, C и D в различни работни книги, както е показано по-долу. След като отворим всички работни книги, ще преминем към нова работна книга и щракнете върху клетка B3. Добавете референциите от всички работни листове по-долу. Ние консолидирахме данните от всички работни книги в нова работна книга. Освен това всички промени в данните във всички работни книги ще се актуализират и в новата работна книга за консолидирани данни. Така че научихме как да използваме функцията за консолидация в Excel с помощта на примери. Това е много полезна функция за обединяване или събиране на данни в един лист от различни работни листове или работни книги. Това е ръководство за консолидация в Excel. Тук обсъждаме Как да консолидираме данните в множество работни листове, заедно с практически примери и шаблон за Excel за изтегляне. Можете да разгледате и другите ни свързани статии -
Пример №3 - Консолидиране на данни от различни работни книги
Неща, които трябва да запомните за консолидацията в Excel
Препоръчителни статии