Консолидиране на данни в множество работни листове
Applies ToExcel за Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016

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

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

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

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

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

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

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

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

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

  1. Ако все още не сте го направили, настройте данните във всеки съставен лист, като направите следното:

    • Уверете се, че всеки диапазон от данни е във формат на списък. Всяка колона трябва да има етикет (заглавка) в първия ред и да съдържа подобни данни. Не трябва да има празни редове или колони където и да е в списъка.

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

    • Уверете се, че всеки диапазон има еднакво оформление.

  2. В главния работен лист щракнете върху горната лява клетка на областта, в която искате да се появяват консолидираните данни.

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

  3. Щракнете върху Данни>Консолидиране (в групата Инструменти за данни ).

    Групата "Инструменти за данни" в раздела "Данни"

  4. В полето Функция щракнете върху сумираща функция, която искате Excel да използва за консолидиране на данните. Функцията по подразбиране е SUM.

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

    Диалогов прозорец ''Консолидиране на данни"

  5. Изберете данните си.

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

    Консолидиране на данни Свий диалоговия прозорец

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

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

    Диалогов прозорец ''Консолидиране на данни"

  6. В изскачащия прозорец Консолидиране щракнете върху Добави. Повторете това, за да добавите всички диапазони, които консолидирате.

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

    Забележки: 

    • Не можете да създавате връзки, когато областта източник и целевата област са в един и същ лист.

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

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

    • Всички етикети, които не съвпадат с етикетите в другите области източници, пораждат отделни редове или колони в консолидирането.

    • Уверете се, че всички категории, които не искате да консолидирате, имат уникални етикети, които се показват само в един диапазон източник.

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

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

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

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

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

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

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

Имате нужда от още помощ?

Винаги можете да попитате експерт в техническата общност на Excel или да получите поддръжка в Общността за отговори от.

Вж. също

Общ преглед на формулите в Excel

Начини за избягване на повредени формули

Намиране и коригиране на често срещани грешки във формули

Клавишни комбинации и функционални клавиши за Excel

Функции на Excel (по азбучен ред)

Функции на Excel (по категории)

Нуждаете ли се от още помощ?

Искате ли още опции?

Разгледайте ползите от абонамента, прегледайте курсовете за обучение, научете как да защитите устройството си и още.

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