Pārvaldiet tikšanās, plānus, budžeti — tas ir vienkārši ar Microsoft 365.

Vairāku darblapu datu konsolidēšana

Lai apkopotu un sniegtu atskaišu rezultātus no atsevišķām darblapām, varat apvienot datus no katras lapas šablona darblapā. Lapas var būt tajā pašā darbgrāmatā, kurā atrodas galvenā darblapa vai citās darbgrāmatās. Konsolidējot datus, jūs apkopojat datus, lai jūs varētu vienkāršāk atjaunināt un apkopot pēc vajadzības.

Piemēram, ja jums ir izdevumu darblapa katram no reģionālajiem birojiem, varat izmantot konsolidāciju, lai šos datus tuvinātu galvenajā korporatīvās izdevumu 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 augstākie pārdošanas produkti visā uzņēmumā.

Padoms.: Ja bieži konsolidējat datus, iespējams, tas palīdzēs izveidot jaunas darblapas no darblapas veidnes, kas izmanto konsekventu izkārtojumu. Papildinformāciju par veidnēm skatiet rakstā veidnes izveide. Tas ir arī ideāls laiks, lai iestatītu veidni ar Excel tabulām.

Ir divi veidi, kā konsolidēt datus pēc pozīcijas vai kategorijas.

Konsolidācija pēc pozīcijas: datiem avota apgabalos ir vienāds pasūtījums un tiek izmantotas tās pašas etiķetes. Izmantojiet šo metodi, lai konsolidētu datus no darblapu sērijas, piemēram, struktūrvienību budžeta darblapām, kas izveidotas no vienas veidnes.

Konsolidācija pēc kategorijas: ja dati avota apgabalos 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ām ir atšķirīgi izkārtojumi, bet kurām ir vienādas datu etiķetes.

  • Datu konsolidēšana pēc kategorijas līdzinās rakurstabulas izveidei. Izmantojot rakurstabulu, tomēr varat viegli pārkārtot kategorijas. Apsveriet iespēju izveidot rakurstabulu , ja ir nepieciešams elastīgāks konsolidācija pēc kategorijas.

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

Veiciet šīs darbības, lai vairākas darblapas konsolidētu šablona darblapā:

  1. Ja vēl neesat to izdarījis, iestatiet datus katrā komponentu lapā, rīkojoties šādi:

    • Pārliecinieties, vai visi datu diapazoni ir saraksta formātā. Katrai kolonnai ir jābūt ar etiķeti (galveni) pirmajā rindā, un tajā ir līdzīgi dati. Sarakstā nedrīkst būt tukšu rindu vai kolonnu.

    • Novietojiet katru diapazonu atsevišķā darblapā, bet neievadiet neko no galvenās darblapas, kurā plānojat konsolidēt datus. Programma Excel veiks šo uzdevumu.

    • 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 esat atstājis pietiekami daudz šūnu pa labi un zem šīs šūnas konsolidētajiem datiem.

  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.

    Tālāk ir parādīts piemērs, kurā ir atlasīti trīs darblapu diapazoni:

    Datu konsolidēšanas dialogs

  5. Atlasiet datus.

    Pēc tam lodziņā atsauce noklikšķiniet uz sakļaušanas pogas, lai samazinātu paneli, un darblapā atlasiet datus.

    Datu konsolidēšanas sakļaušanas dialogs

    Noklikšķiniet uz darblapas, kurā ir dati, kas jākonsolidē, atlasiet datus un pēc tam noklikšķiniet uz pogas Izvērst dialogu , kas atrodas pa labi, lai atgrieztos dialogā konsolidēšana .

    Ja darblapa ar datiem, kas ir jākonsolidē, ir citā darbgrāmatā, noklikšķiniet uz Pārlūkot , lai atrastu šo darbgrāmatu. Pēc atrašanās vietas un noklikšķināšanas uz Labi, Excel ievadīs faila ceļu lodziņā atsauce un pievieno izsaukuma zīmi uz šo ceļu. Pēc tam varat turpināt atlasīt citus datus.

    Šeit ir piemērs, kurā ir atlasīti trīs darblapu diapazoni:

    Datu konsolidēšanas dialogs

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

  7. Automātiskie un manuālie atjauninājumi: Ja vēlaties, lai programma Excel atjauninātu konsolidācijas tabulu automātiski, mainoties avota datiem, vienkārši atzīmējiet izvēles rūtiņu izveidot saites uz avota datiem . Ja šis lodziņš paliek neatzīmēts, konsolidāciju var atjaunināt manuāli.

    Piezīmes.: 

    • Nevar izveidot saites, ja avota un mērķa apgabaliem ir vienā lapā.

    • Ja nepieciešams mainīt diapazona apjomu vai aizstāt diapazonu, noklikšķiniet uz diapazona konsolidācijas uznirstošajā sarakstā un atjauniniet to, izmantojot iepriekš norādītās darbības. Tādējādi tiks izveidota jauna diapazona atsauce, tāpēc pirms atkārtotas konsolidēšanas būs jādzēš iepriekšējā. Vienkārši izvēlieties veco uzziņu un nospiediet taustiņu DELETE.

  8. Noklikšķiniet uz Labi, un programma Excel ģenerēs konsolidāciju. Ja vēlaties, varat lietot formatējumu. To ir nepieciešams formatēt tikai vienreiz, ja vien netiek atkārtoti palaista 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, kuras parādās tikai vienā avota diapazonā.

Ja dati konsolidācijai 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, Sales! B4 — formulā bez rakstīšanas ierakstiet formulu līdz vietai, kur ir nepieciešamas atsauces, pēc tam noklikšķiniet uz darblapas cilnes un pēc tam noklikšķiniet uz šūnas. Programma Excel pabeigs lapas nosaukumu un šūnas adresi. Piezīme. šādos gadījumos formulas var būt kļūdas, jo ir ļoti viegli nejauši atlasīt nepareizo šūnu. Var būt arī grūti pamanīt kļūdu pēc sarežģītas formulas ievadīšanas.

Ja dati konsolidēšanai ir vienās un tajās pašās šūnās dažādās darblapā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 pārdošanu (ieskaitot), šablona darblapas šūnā E5 jāievada šādi:

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.

×