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

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

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

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

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

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

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

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

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

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

Стъпки за консолидиране

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

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

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

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

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

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

  3. В раздела Данни, в групата Инструменти за данни щракнете върху Консолидиране.

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

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

    По-долу е даден пример с три избрани диапазона от работен лист.

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

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

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

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

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

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

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

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

    Забележки : 

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

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

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

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

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

Използване на формула за консолидиране на данни

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

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

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

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

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

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

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

Знаете ли, че...?

Ако нямате абонамент за Office 365 или най-новата версия на Office, можете сега да изпробвате безплатно:

Изпробвайте Office 365 или най-новата версия на Excel

Имате ли въпрос за конкретна функция?

Публикувайте въпрос във форума на общността на Excel

Помогнете ни да подобрим Excel

Имате ли предложения как можем да подобрим следващата версия на Excel? Ако е така, вижте темите в Гласът на потребителите на Excel

Вж. също

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

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

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

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

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

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

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

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

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

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

×