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

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

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

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

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

Вж. също

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

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

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

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

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

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

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

Разширете уменията си в Office
Преглед на обучението
Получавайте първи новите функции
Присъединете се към участниците в Office Insider

Беше ли полезна тази информация?

Благодарим ви за обратната връзка!

Благодарим ви за вашата обратна връзка. Изглежда, че ще бъде полезно да ви свържем с един от нашите агенти по поддръжката на Office.

×