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 veidot relācijas starp tabulām, kas var būt alternatīva uzmeklēšanas funkcijām, piemēram, VLOOKUP. 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 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 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ītos datus, neapvienojot tos vienā tabulā.
Piezīme
Ja darbgrāmatā ir datu modelis, tabulu relācijas varat pārvaldīt cilnē Dati.
Importējot saistītas tabulas no relāciju datu bāzes, programmā Excel bieži šīs relācijas var tikt izveidotas datu modelī, kas tiek veidots fonā. 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. 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 saistītu klientu datus ar laika informāciju, abās tabulās datumiem jābūt vienādā formātā (piemēram, 1/1/2026) un vismaz vienas tabulas (laika informācijas tabulas) kolonnā katram datumam jābūt norādītam tikai vienreiz. - Atlasiet Datu>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 redzams, vai relācijas pastāv. Ja nav redzama uzvedne ar aicinājumu izveidot relāciju, programmā Excel jau ir nepieciešamā 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, izmantojot uzmeklēšanas tabulu). Relācijas daudzi pret daudziem rada riņķveida atkarības kļūdas, piemēram, "Tika atklāta riņķveida atkarība." Šāda kļūda radīsies, ja veidosit tiešu savienojumu starp divām tabulām, kurās ir daudzi pret daudziem vai netieši savienojumi (tabulas relāciju virkne, kas ir viens pret daudziem katrā relācijā, taču, daudzi pret daudziem, ja skata kopumā). Lasiet vairāk par tabulu relācijām datu modelī.
Atšķirībā no uzmeklēšanas formulām, relācijas nedublē datus. Tā vietā tie saista tabulas, lai rakurstabulā varētu izmantot kopā laukus no katras tabulas.
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 vajadzīgas"
Pievienojot 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šamību, tā nevar noteikt, kuras tabulas un kolonnas jāizmanto 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 izmanto rakurstabulā. Pieņemsim, ka visas jūsu tabulas kaut kādā veidā ir saistītas ar citām tabulā, taču, 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 šāda ziņojuma 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 konstatētu, 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ī, jums vienā no demogrāfiskajām tabulām 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. Tas nozīmē, ka š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.