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 tādā pašā secībā, bet 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 tika izveidoti ar Excel 2016. Lai gan skats var atšķirties, ja izmantojat citu programmas Excel, 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.

  1. 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.

  2. Š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.

  3. Noklikšķiniet uz>datu konsolidēšana(grupā Datu rīki).

    Cilnes Dati grupa Datu rīki

  4. 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:

    Dialoglodziņš Datu konsolidēšana

  5. Atlasiet datus.

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

    Datu konsolidēšanas sakļaušanas dialogs

    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 atrastsun Excel labi, faila ceļš tiks ievadīts lodziņā Atsauce un pievienot 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:

    Dialoglodziņš Datu konsolidēšana

  6. Uznirstošajā logā Konsolidēšanas noklikšķiniet uz Pievienot. Atkārtojiet šo darbību, lai pievienotu visus konsolidētos diapazonus.

  7. Automātiskie un manuālie atjauninājumi: Ja vēlaties Excel konsolidācijas tabulu automātiski, 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āmaina 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.

  8. Noklikšķiniet uzLabi Excel tiks ģenerēta konsolidācija. 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 lapas formulas atsauce
 

Padoms.: Lai ievadītu šūnas atsauci, piemēram, Pārdošana! B4 formulā bez rakstīšanas rakstiet formulu līdz punktam, kur vēlaties izmantot atsauci, 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

Vai nepieciešama papildu palīdzība?

Vienmēr varat pajautāt speciālistiem Excel Tech kopienā, saņemt atbalstu Answers kopienā vai ieteikt jaunu līdzekli vai uzlabojumu vietnē Excel User Voice.

Skatiet arī

Pārskats par formulām programmā Excel

kā nepieļaut kļūdainas formulas

kļūdu atrašana un izlabošana formulās

Excel īsinājumtaustiņi un funkciju taustiņi

Excel funkcijas (alfabēta secībā)

Excel funkciju saraksts (pēc kategorijas)

Vai nepieciešama papildu palīdzība?

Paplašiniet savas Office prasmes
Iepazīties ar apmācību
Esiet pirmais, kas saņem jaunās iespējas
Pievienoties Office Insider programmai

Vai šī informācija bija noderīga?

Paldies par jūsu atsauksmēm!

Paldies par atsauksmēm! Šķiet, ka jums varētu būt noderīgi sazināties ar kādu no mūsu Office atbalsta speciālistiem.

×