Vai esat izmantojis funkciju VLOOKUP kolonnas pārnešanai no vienas tabulas otrā? Programmā Excel ir iekļauts arī iebūvēts datu modelis, kas ļauj izveidot relācijas starp tabulām, kas var būt alternatīva uzmeklēšanas funkciju, piemēram, VLOOKUP, izmantošana. Jūs varat izveidot relāciju starp divām datu tabulām, pamatojoties uz atbilstošiem datiem katrā tabulā. Pēc tam varat izveidot rakurstabulas un citas atskaites ar laukiem no katras tabulas pat tad, ja tabulas ir no dažādiem avotiem. Piemēram, ja jums ir klientu pārdošanas dati, varat importēt laika informācijas datus un izveidot relāciju ar tiem, lai analizētu pārdošanas tendences pa gadiem un mēnešiem.
Visas darbgrāmatas tabulas ir uzskaitītas rakurstabulas lauku sarakstā.
Relācijas visbiežāk tiek izmantotas, veidojot rakurstabulas no vairākām datu modeļa tabulām. Tas ļauj analizēt saistītus datus, apvienojot tos vienā tabulā.
Piezīme.: Ja darbgrāmatā ir iekļauts datu modelis, varat pārvaldīt tabulu relācijas cilnē Dati.
Importējot saistītas tabulas no relāciju datu bāzes, programma Excel bieži šīs relācijas var izveidot datu modelī, kas tiek veidots aizkulisēs. Citos gadījumos relācijas vajadzēs izveidot manuāli.
-
Pārliecinieties, vai darbgrāmatā ir vismaz divas tabulas un vai katrā tabulā ir kolonna, kuru var kartēt ar citas tabulas kolonnu.
-
Veiciet vienu no šīm darbībām: Formatējiet datus kā tabulu vai importējiet ārējos datus kā tabulu jaunā darblapā.
-
Katrai tabulai piešķirt jēgpilnu nosaukumu. Lai ievadītu nosaukumu, sadaļā Tabulas rīki noklikšķiniet uz Noformējums > Tabulas nosaukums > ievadiet nosaukumu.
-
Pārliecinieties, ka vienas tabulas kolonnā ir unikālas datu vērtības bez dublikātiem. Programma Excel var izveidot relāciju tikai tad, ja viena kolonna satur unikālas vērtības.
Piemēram, lai klienta datus saista ar laika informāciju, abās tabulās datumiem jābūt vienādā formātā (piemēram, 1/1/2026) un vismaz vienā tabulā (laika informācijas tabulā) katrs datums kolonnā ir tikai vienreiz norādīts.
-
Atlasiet Dati > relācijas.
Ja opcija Relācijas ir pelēkota, darbgrāmatā ir tikai viena tabula.
-
Lodziņā relāciju pārvaldība atlasiet Jauns.
-
Lodziņā Izveidot relāciju noklikšķiniet uz bultiņas pie opcijas Tabula un sarakstā atlasiet tabulu. Veidojot relāciju Viens pret daudziem, šai tabulai ir jābūt relācijas kopā Daudzi. Minētajā klientu un laika informācijas piemērā vispirms ir jāizvēlas klientu pārdošanas datu tabula, jo daudzi pārdošanas darījumi var notikt jebkurā dienā.
-
Laukā Kolonna (ārējā) atlasiet kolonnu, kurā ir iekļauti ar laukā Saistītā kolonna (primārā) norādīto kolonnu saistītie dati. Piemēram, ja abās tabulās ir datumu kolonna, ir jāizvēlas šī kolonna.
-
Laukā Saistītā tabula atlasiet tabulu, kurā ir vismaz viena kolonna, kuras dati ir saistīti ar laukā Tabula tikko atlasīto tabulu.
-
Laukā Saistīta kolonna (primārā) atlasiet kolonnu, kurā ir unikālas vērtības, kas atbilst laukā Kolonna atlasītās kolonnas vērtībām.
-
Atlasiet Labi.
Papildinformācija par tabulu relācijām programmā Excel
Piezīmes par relācijām
-
Velkot dažādu tabulu laukus uz rakurstabulas lauku sarakstu, būs zināms, vai relācijas pastāv. Ja netiek piedāvāts izveidot relāciju, programmā Excel jau ir nepieciešama relācijas informācija, lai veidotu datu relāciju.
-
Relāciju izveide līdzinās funkcijas VLOOKUP lietošanai: ir jāizmanto kolonnas ar savstarpēji atbilstošiem datiem, lai programmā Excel varētu izveidot vienas tabulas rindu iekšējo atsauci uz otras tabulas rindām. Laika informācijas piemērā tabulā Klienti ir jābūt datumu vērtībām, kas ir iekļautas arī laika informācijas tabulā.
-
Excel datu modelī relācijas parasti ir viens pret vienu vai viens pret daudziem. Relācijām daudzi pret daudziem ir nepieciešama papildu modelēšana (piemēram, uzmeklēšanas tabulas izmantošana). Relācijas Daudzi pret daudziem izraisa riņķveida atkarības kļūdas, piemēram, "Tika konstatēta riņķveida atkarība". Šī kļūda rodas, ja tiek izveidots tiešs savienojums starp divām tabulām, kas ir daudzi pret daudziem vai netieši savienojumi (tabulu relāciju ķēde, kas katrā relācijā ir viens pret daudziem, bet daudzi pret daudziem, skatot beigās). Lasiet vairāk par relācijām starp tabulām datu modelī.
-
-
Atšķirībā no uzmeklēšanas formulām, relācijās netiek dublēti dati. Tās saista tabulas, lai laukus no katras tabulas varētu izmantot kopā rakurstabulā.
-
Datu tipiem abās kolonnās jābūt saderīgiem. Papildinformāciju skatiet sadaļā Datu tipi programmas Excel datu modeļos.
-
Ir pieejami arī citi relāciju izveides veidi, kas, iespējams, ir intuitīvāki — jo īpaši, ja neesat pārliecināts, kuras tabulas izmantot. Skatiet sadaļu Relācijas izveide pievienojumprogrammas diagrammas skatā Power Pivot.
"Tabulu relācijas var būt nepieciešamas"
Kad pievienosit laukus rakurstabulai, jūs tiksit informēts, ja būs vajadzīga tabulas relācija, lai rakurstabulā atlasītajiem laukiem būtu jēga.
Lai gan programma Excel var norādīt uz relācijas nepieciešamajiem kritērijiem, tā nevar noteikt, kuras tabulas un kolonnas izmantot un vai tabulas relācija vispār ir iespējama. Lai saņemtu vajadzīgās atbildes, izmēģiniet tālāk aprakstītās darbības.
1. — nosakiet, kuras tabulas jānorāda relācijā
Ja jūsu modelī ir tikai dažas tabulas, varbūt uzreiz būs skaidrs, kuras no tām jāizmanto. Taču attiecībā uz lielākiem modeļiem, iespējams, būs vajadzīga palīdzība. Viena pieeja ir izmantot pievienojumprogrammas Power Pivot diagrammas skatu. Diagrammas skatā ir vizuāli attēlotas visas tabula datu modelī. Izmantojot diagrammas skatu, varat ātri noteikt, kuras tabulas ir savrupas no pārējā modeļa.
Piezīme.: Ir iespējams izveidot neviennozīmīgas relācijas, kas ir nederīgas, ja tās tiek izmantotas rakurstabulā. Pieņemsim, ka visas jūsu tabulas kaut kādā veidā ir saistītas ar citām tabulām modelī, bet, mēģinot kombinēt laukus no dažādām tabulām, tiek parādīts ziņojums "Iespējams, starp tabulām ir nepieciešamas relācijas". Ticamākais iemesls ir relācija daudzi pret daudziem. Ja sekosit to tabulu relāciju ķēdei, kas savieno tabulas, kuras vēlaties izmantot, iespējams, atklāsit, ka jums ir divas vai vairākas tabulu relācijas viens pret daudziem. Nav tāda viegla risinājuma, kas derētu visām situācijām, taču varat mēģināt izveidot aprēķinātas kolonnas, lai konsolidētu kolonnas, ko vēlaties izmantot vienā tabulā.
2. — atrodiet kolonnas, ko var izmantot, lai izveidotu ceļu no vienas tabulas uz nākamo
Kad būsit identificējis, kura tabula ir atvienota no pārējā modeļa, pārskatiet tabulas kolonnas, lai noteiktu, vai cita kolonna citur modelī satur atbilstošas vērtības.
Piemēram, pieņemsim, ka jums ir modelis, kurā ir produktu pārdošanas dati pēc teritorijas, un ka pēc tam importējat demogrāfiskos datus, lai uzzinātu, vai pastāv attiecība starp pārdošanas apjomu un demogrāfiskajām tendencēm katrā teritorijā. Tā kā demogrāfiskie dati tiek iegūti no cita datu avota, tā tabulas sākotnēji ir izolētas no pārējā modeļa. Lai demogrāfiskos datus integrētu pārējā modelī, vienā no demogrāfiskajām tabulām ir jāatrod kolonna, kas atbilst jau izmantotai kolonnai. Piemēram, ja demogrāfiskie dati ir organizēti pēc reģiona un ja jūsu pārdošanas datos ir norādīts, kurā reģionā darījums noticis, varat saistīt šīs divas datu kopas, atrodot kopīgu kolonnu, piemēram, Novads, Pasta indekss vai Reģions, lai veiktu uzmeklēšanu.
Papildus vērtību atbilstībai ir vēl dažas citas prasības, lai izveidotu relāciju:
-
Uzmeklēšanas kolonnas datu vērtībām ir jābūt unikālām. Citiem vārdiem sakot, šajā kolonnā nedrīkst būt dublikātu. Datu modelī nulles un tukšas virknes ir vienādas ar tukšumiem, kas ir noteikta datu vērtība. Tas nozīmē, ka uzmeklēšanas kolonnā nedrīkst būt vairākas nulles.
-
Datu tipiem avota kolonnā ir jābūt saderīgiem ar datu tipiem uzmeklēšanas kolonnā. Papildinformācija par datu tipiem atrodama rakstā Datu tipi datu modeļos.
Papildinformāciju par tabulu relācijām skatiet sadaļā Datu modeļa tabulu relācijas.
