Консолидиране на данни в множество работни листове

За да обобщите и докладвате резултати от различни работни листове, можете да консолидирате данни от всеки лист в главен работен лист. Листовете могат да бъдат в същата работна книга като главния работен лист или в други работни книги. Когато консолидирате данни, вие събирате данни, така че да можете по-лесно да актуализирате и обобщавате, както е необходимо.

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

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

Има два начина за консолидиране на данни по позиция или категория.

Консолидиране по позиция: Данните в областите източници имат еднакъв ред и използват едни и същи етикети. Използвайте този метод, за да консолидирате данни от поредица работни листове, създадени от един и същ шаблон, като например работни листове за бюджет на отдели.

Консолидиране по категория: когато данните в областите източници не са подредени в същия ред, но използват едни и същи етикети. Използвайте този метод, за да консолидирате данни от поредица работни листове, които имат различни оформления, но едни и същи етикети на данните.

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

Забележка: Примерите в тази статия са създадени с Excel 2016. Въпреки че вашият изглед може да се различава, ако използвате друга версия на Excel, стъпките са същите.

Следвайте тези стъпки, за да консолидирате няколко работни листа в главен работен лист:

Ако все още не сте го направили, настройте данните във всеки съставен лист, като направите следното:
- Уверете се, че всеки диапазон от данни е във формат на списък. Всяка колона трябва да има етикет (заглавка) в първия ред и да съдържа подобни данни. Не трябва да има празни редове или колони където и да е в списъка.
- Поставете всеки диапазон в отделен работен лист, но не въвеждайте нищо в главния работен лист, където планирате да консолидирате данните. Excel ще направи това вместо вас.
- Уверете се, че всеки диапазон има еднакво оформление.
В главния работен лист щракнете върху горната лява клетка на областта, в която искате да се появяват консолидираните данни.

Забележка: За да избегнете презаписване на съществуващи данни в главния работен лист, уверете се, че оставяте достатъчно клетки надясно и под тази клетка за консолидираните данни.
Щракнете върху Данни>Консолидиране (в групата Инструменти за данни ).
В полето Функция щракнете върху сумираща функция, която искате Excel да използва за консолидиране на данните. Функцията по подразбиране е SUM.

Ето пример, в който са избрани три диапазона от работни листове:
Изберете данните си.

След това в полето Препратка щракнете върху бутона Свий , за да свиете панела и да изберете данните в работния лист.

Щракнете върху работния лист, съдържащ данните, които искате да консолидирате, изберете данните и след това щракнете върху бутона Разшири диалоговия прозорец отдясно, за да се върнете към диалоговия прозорец Консолидиране.

Ако работен лист, съдържащ данните, които трябва да консолидирате, е в друга работна книга, щракнете върху Преглед , за да намерите тази работна книга. След като местоположението и щракването върху OK, Excel ще въведе пътя до файла в полето Препратка и ще добави удивителен знак към този път. След това можете да продължите да избирате други данни.

Ето пример, в който са избрани три диапазона от работни листове:
В изскачащия прозорец Консолидиране щракнете върху Добави. Повторете това, за да добавите всички диапазони, които консолидирате.
Автоматични или ръчни актуализации: Ако искате Excel да актуализира таблицата за консолидация автоматично, когато данните източник се променят, просто отметнете квадратчето Създай връзки към данните източник . Ако това поле остане без отметка, можете да актуализирате консолидацията ръчно.
Забележки:
- Не можете да създавате връзки, когато областта източник и целевата област са в един и същ лист.
- Ако трябва да промените степента на диапазон – или да заместите диапазон – щракнете върху диапазона в изскачащия прозорец Консолидиране и го актуализирайте, като използвате стъпките по-горе. Това ще създаде нова препратка към диапазон, така че ще трябва да изтриете предишната, преди да повторите консолидирането. Просто изберете старата препратка и натиснете клавиша Delete.
Щракнете върху OK и Excel ще генерира консолидацията вместо вас. Ако желаете, можете да приложите форматиране. Необходимо е да форматирате само веднъж, освен ако не изпълните отново консолидирането.
- Всички етикети, които не съвпадат с етикетите в другите области източници, пораждат отделни редове или колони в консолидирането.
- Уверете се, че всички категории, които не искате да консолидирате, имат уникални етикети, които се показват само в един диапазон източник.

Ако данните за консолидиране са в различни клетки на различни работни листове:

Въведете формула с препратки на клетка към другите работни листове, по една за всеки отделен работен лист. Например за да консолидирате данни от работни листове с имена "Продажби" (в клетка B4), HR (в клетка F5) и "Маркетинг" (в клетка B9) в клетка A2 на главния работен лист, бихте въвели следното:

Препратка към формула на Excel за много листове

Съвет: За да въведете препратка към клетка – например "Продажби"! B4 – във формула, без да въвеждате, въведете формулата до точката, където ви трябва препратката, след което щракнете върху раздела на работния лист и след това щракнете върху клетката. Excel ще попълни името на листа и адреса на клетката вместо вас. ЗАБЕЛЕЖКА: формулите в такива случаи могат да бъдат предсказани от грешки, тъй като е много лесно случайно да изберете грешна клетка. Също така може да е трудно да забележите грешка след въвеждането на сложна формула.

Ако данните за консолидиране са в едни и същи клетки на различни работни листове:

Въведете формула в тримерна препратка, която използва препратка към диапазон от имена на работни листове. Например за да консолидирате данни в клетки A2 от "Продажби" до "Маркетинг" включително, в клетка E5 на главния работен лист бихте въвели следното:

Препратка към формула на Excel за 3D лист