Vairāku darblapu datu konsolidēšana

Lai apkopotu un izveidotu pārskatu par rezultātiem no atsevišķām darblapām, varat konsolidēt datus no katras lapas šablona darblapā. Lapas var būt tajā pašā darbgrāmatā, kur atrodas šablona darblapa, vai citās darbgrāmatās. Kad konsolidējat datus, tie tiek apvienoti, lai nepieciešamības gadījumā varētu vieglāk atjaunināt un apkopot.

Piemēram, ja izmantojat izdevumu darblapu katram reģionālajam birojam, varat izmantot konsolidāciju, lai šos datus ievietotu uzņēmuma izdevumu šablona darblapā. Šajā šablona darblapā var būt arī pārdošanas kopsummas un vidējās vērtības, pašreizējā inventāra saraksts un visvairāk pārdotie produkti visā uzņēmumā.

Padoms.: Ja bieži konsolidējat datus, ieteicams izveidot jaunas darblapas no darblapu veidnes, kas izmanto konsekventu izkārtojumu. Papildinformāciju par veidnēm skatiet rakstā Veidnes izveide. Šis ir arī ideāls laiks, lai iestatītu veidni ar Excel tabulām.

Pastāv divi datu konsolidēšana pēc novietojuma vai kategorijas.

Konsolidācija pēc pozīcijas: avota apgabalu datiem ir tāda pati secība, un tie izmanto tās pašas etiķetes. Izmantojiet šo metodi, lai konsolidētu datus no vairākas darblapas, piemēram, nodaļas budžeta darblapas, kas izveidotas, izmantojot vienu veidni.

Konsolidācija pēc kategorijas: ja avota apgabalu dati nav sakārtoti vienā un tajā pašā secībā, taču izmanto tās pašas etiķetes. Izmantojiet šo metodi, lai konsolidētu datus no darblapu sērijas, kurās ir atšķirīgi izkārtojumi, bet kurām ir tādas pašas datu etiķetes.

Datu konsolidēšana pēc kategorijas ir līdzīga rakurstabulas konsolidēšanai. Taču, izmantojot rakurstabulu, varat viegli pārkārtot kategorijas. Apsveriet iespēju izveidot rakurstabulu, ja jums ir nepieciešama elastīgāka konsolidācija pēc kategorijas.

Piezīme.: Šajā rakstā minētie piemēri ir izveidoti programmā Excel 2016. Lai gan skats var atšķirties, ja izmantojat citu Excel versiju, veicamās darbības ir vienādas.

Lai vairākas darblapas konsolidētu šablona darblapā, veiciet tālāk norādītās darbības.

Ja vēl neesat to darīja, iestatiet datus katrā komponentu lapā, rīkojoties šādi:
- Pārliecinieties, vai visi datu diapazoni ir saraksta formātā. Katrā kolonnā jābūt etiķetei (galvenei) pirmajā rindā, un tajā ir jābūt līdzīgiem datiem. Sarakstā nedrīkst būt tukšu rindu vai kolonnu.
- Novietojiet katru diapazonu atsevišķā darblapā, bet neievietojiet tos šablona darblapā, kur plānojat konsolidēt datus. Excel to izdarīs jūsu gadījumā.
- Pārliecinieties, vai visiem diapazoniem ir vienāds izkārtojums.
Šablona darblapā noklikšķiniet konsolidēto datu parādīšanas apgabala kreisajā augšējā šūnā.

Piezīme.: Lai izvairītos no esošo datu pārrakstīšanas šablona darblapā, pārliecinieties, vai pa labi un zem šīs šūnas ir pietiekami daudz šūnu konsolidēto datu formatēšanai.
Noklikšķiniet uz>datu konsolidēšana ( grupā Datu rīki).
Lodziņā Funkcija noklikšķiniet uz summārā funkcija, kas programmai Excel jāizmanto datu konsolidēšanai. Noklusējuma funkcija ir SUM.

Lūk, piemērs, kurā ir izvēlēti trīs darblapu diapazoni:
Atlasiet datus.

Pēc tam lodziņā Atsauce noklikšķiniet uz pogas Sakļaut, lai samazinātu paneli un atlasītu datus darblapā.

Noklikšķiniet uz darblapas, kurā ir konsolidējamie dati, atlasiet tos un pēc tam pa labi noklikšķiniet uz pogas Izvērst dialogu, lai atgrieztos dialoglodziņā Konsolidēšana.Ja darblapa, kurā ir konsolidējamie dati, ir citā darbgrāmatā, noklikšķiniet uz Pārlūkot , lai atrastu šo darbgrāmatu. Pēc tam, kad būs atrasts un noklikšķināts uz Labi, programma Excel ievadīs faila ceļu lodziņā Atsauce un pievienos izsaukuma zīmi ceļā. Pēc tam varat turpināt atlasīt citus datus.

Lūk, piemērs, kurā atlasīti trīs darblapu diapazoni:
Uznirstošajā logā Konsolidēšanas noklikšķiniet uz Pievienot. Atkārtojiet šo darbību, lai pievienotu visus konsolidētos diapazonus.
Automātiskie un manuālie atjauninājumi: Ja vēlaties, lai Excel automātiski atjaunināt konsolidācijas tabulu, mainot avota datus, vienkārši atzīmējiet izvēles rūtiņu Izveidot saites uz avota datiem. Ja šī rūtiņa nav atzīmēta, konsolidēšanu var atjaunināt manuāli.
Piezīmes.:
- Ja avota un mērķa apgabali atrodas vienā lapā, saites nevar izveidot.
- Ja ir jāmaina diapazona pakāpe vai jāaizstāj diapazons, noklikšķiniet uz diapazona uznirstošajā lodziņā Konsolidējot un atjauniniet to, veicot iepriekš norādītās darbības. Tiek izveidota jauna diapazona atsauce, tāpēc pirms atkal konsolidēšanas ir jāizdzēš iepriekšējā atsauce. Vienkārši izvēlieties veco atsauci un nospiediet taustiņu Delete.
Noklikšķiniet uz Labi, un excel ģenerēs konsolidāciju. Varat arī lietot formatējumu. Konsolidēšana ir nepieciešama tikai vienreiz, ja vien netiek atkārtota konsolidācija.
- Cita avota apgabala etiķetēm neatbilstošās etiķetes konsolidācijā veido atsevišķas rindas vai kolonnas.
- Pārliecinieties, vai visām kategorijām, kuras nav jākonsolidē, ir unikālas etiķetes, kas redzamas tikai vienā avota diapazonā.

Ja konsolidējamie dati ir dažādās darblapās dažādās šūnās:

Katrai darblapai ievadiet formulu ar šūnu atsaucēm uz citām darblapām. Piemēram, lai konsolidētu datus no darblapas Apgrozījums (šūnā B4), Stundas (šūnā F5) un Mārketings (šūnā B9) šablona darblapas šūnā A2, ievadiet šādu formulu:

Excel vairāku lapu formulu atsauce

Padoms.: Lai ievadītu šūnas atsauci, piemēram, Pārdošana! B4 formulā bez rakstīšanas rakstiet formulu līdz punktam, kur nepieciešama atsauce, pēc tam noklikšķiniet uz darblapas cilnes un pēc tam noklikšķiniet uz šūnas. Excel aizpildīs lapas nosaukumu un šūnas adresi. PIEZĪME. Formulas šādos gadījumos var būt kļūdainas, jo ir ļoti viegli nejauši atlasīt nepareizu šūnu. Pēc sarežģītas formulas ievadīšanas var būt grūti pamanīt kļūdu.

Ja konsolidējamie dati dažādās darblapās ir vienās un tajā pašās šūnās:

Ievadiet formulu, kurā iekļautas telpiskās atsauces uz vairāku darblapu nosaukumu diapazonu. Piemēram, lai konsolidētu datus šūnās A2, sākot ar darblapu Apgrozījums līdz darblapai Mārketings, šablona darblapas šūnā E5 ievadiet šādu formulu:

Excel 3D lapas atsauces formula