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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Забележки: 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вж. също

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

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

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

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

×