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

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

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

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

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

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

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

Винаги можете да попитате експерт в техническата общност на Excel, да получите поддръжка в общността за отговори или да предложите нова функция или подобрение на User Voice за Excel.

Вж. също

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

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

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

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

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

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

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

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

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

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

×