Laba datu bāze vienmēr ir strukturēta. Tas nozīmē, ka dažādām entītijām ir dažādas tabulas. Lai gan Excel nav datu bāzes rīks, bet to bieži izmanto, lai uzturētu nelielus datu gabalus.
Daudzas reizes mums rodas vajadzība apvienot tabulas, lai arī redzētu attiecības un atklātu noderīgu informāciju.
Datu bāzēs, piemēram, SQL, Oracle, Microsoft Access utt., Ir viegli savienot tabulas, izmantojot vienkāršus vaicājumus. Bet programmā Excel mums nav JOIN, bet mēs joprojām varam pievienoties tabulām programmā Excel. Mēs izmantojam Excel funkcijas, lai apvienotu un savienotu datu tabulas. Varbūt tā ir vairāk pielāgojama apvienošana nekā SQL. Apskatīsim Excel tabulu apvienošanas paņēmienus.
Apvienojiet datus programmā Excel, izmantojot funkciju VLOOKUP
Lai apvienotu datus programmā Excel, abās tabulās jābūt vismaz vienam kopīgam faktoram/id, lai mēs varētu tos izmantot kā relāciju un apvienot šīs tabulas.
Apsveriet šīs divas tabulas zemāk. Kā mēs varam apvienot šīs datu tabulas programmā Excel?
Pasūtījumu tabulā ir informācija par pasūtījumu, un tabulā Klients ir informācija par klientu. Mums ir jāsagatavo tabula, kurā norādīts, kurš pasūtījums pieder kādam klienta vārdam, klienta punktiem, mājas numuram un viņa pievienošanās datumam.
Kopējais ID abās tabulās ir Cust.ID ko var izmantot tabulu apvienošanai programmā Excel.
Ir trīs tabulu apvienošanas metodes, izmantojot funkciju VLOOKUP.
Iegūstiet katru kolonnu ar kolonnu indeksu
Šajā metodē mēs izmantosim vienkāršu VLOOKUP, lai pievienotu šīs tabulas vienā tabulā. Tātad, lai iegūtu vārdu, uzrakstiet šo formulu.
[Klienti ir lapa1! $ I $ 3: $ M $ 15.]
= MEKLĒŠANA (B3,Klienti,2,0)
Lai apvienotu tabulas punktus, uzrakstiet šo formulu.
= MEKLĒŠANA (B3,Klienti,3,0)
Lai apvienotu māju Nr tabulā, uzrakstiet šo formulu.
= MEKLĒŠANA (B3,Klienti,4,0)
Šeit mēs apvienojām divas tabulas programmā Excel, katru kolonnu pa vienam tabulā. Tas ir noderīgi, ja ir jāapvieno tikai dažas kolonnas. Bet, ja jums ir jāapvieno vairākas kolonnas, tas var būt drudžains uzdevums. Tātad, lai apvienotu vairākas tabulas, mums ir dažādas pieejas.
Apvienot tabulas, izmantojot VLOOKUP un SLEJAS funkcija.
Ja vēlaties izgūt vairākas blakus esošas kolonnas, izmantojiet šo formulu.
=MEKLĒŠANA(lookup_value,table_array,SLEJA()-n, 0)
Šeit funkcija SLEJA vienkārši atgriež kolonnu numurus, kuros tiek rakstīta formula.
n ir jebkurš skaitlis, kas pielāgo kolonnu numuru tabulas masīvā,
Mūsu piemērā tabulas apvienošanas formula būs šāda:
=MEKLĒŠANA(B3 USD,Klienti,SLEJA()-2,0)
Kad esat uzrakstījis šo formulu, jums vairs nebūs jāraksta formula citām kolonnām. Vienkārši nokopējiet to visās pārējās šūnās un kolonnās.
Kā tas strādā
Labi! Pirmajā slejā mums ir nepieciešams nosaukums, kas ir otrā sleja klientu tabulā.
Šeit galvenais faktors ir SLEJA()-2. Funkcija SLEJA atgriež pašreizējās šūnas kolonnas numuru. Mēs rakstām formulu D3, tāpēc no COLUMN () iegūsim 4. Tad mēs atņemam 2, kas veido 2. Tādējādi beidzot mūsu formula vienkāršo līdz =MEKLĒŠANA(B3 USD,Klienti,2,0).
Kopējot šo formulu E kolonnās, mēs iegūsim formulu kā =MEKLĒŠANA(B3 USD,Klienti,3, 0). Kas izgūst trešo kolonnu no klientu tabulas.
Apvienojiet tabulas, izmantojot funkciju VLOOKUP-MATCH
Šis ir mans iecienītākais veids, kā apvienot tabulas programmā Excel, izmantojot funkciju VLOOKUP. Iepriekš minētajā piemērā mēs izgājām kolonnu sērijveidā. Bet ko darīt, ja mums vajadzētu izgūt izlases slejas. Šādā gadījumā iepriekš minētās metodes būs bezjēdzīgas. VLOOKUP-MATCH tehnikā šūnu apvienošanai tiek izmantoti kolonnu virsraksti. To sauc arī par VLOOKUP ar Dynamic Col Index.
Mūsu piemērā šeit formula būs šāda.
= MEKLĒŠANA ($ B3,Klienti, MATCH (2 USD,$ 2 $: $ 2 $,0),0)
Šeit mēs vienkārši izmantojam funkciju MATCH, lai iegūtu atbilstošo kolonnas numuru. To sauca par Dynamic VLOOKUP.
INDEX-MATCH izmantošana tabulu apvienošanai programmā Excel
INDEX-MATCH ir ļoti spēcīgs uzmeklēšanas rīks, un daudzkārt to sauc par labāku VLOOKUP funkciju. To var izmantot, lai apvienotu divas vai vairākas tabulas. Tas arī ļaus apvienot kolonnas no tabulas kreisās puses.
Šī bija ātra apmācība par tabulu apvienošanu un pievienošanu programmā Excel. Mēs izpētījām vairākus veidus, kā Excel apvienot divas vai vairākas tabulas. Jūtieties brīvi uzdot jautājumu par šo rakstu vai jebkuru citu vaicājumu par Excel 2019, 2016, 2013 vai 2010 komentāru sadaļā.
Programmā Excel darbojas IF, ISNA un VLOOKUP
Funkcijas IFERROR un VLOOKUP programmā Excel
Kā programmā Excel izgūt visu atbilstošās vērtības rindu
Populāri raksti:
50 Excel saīsne produktivitātes paaugstināšanai : Ātrāk izpildiet savu uzdevumu. Šie 50 īsceļi ļaus jums strādāt vēl ātrāk programmā Excel.
Kā lietot tVLOOKUP funkcija programmā Excel : Šī ir viena no visbiežāk izmantotajām un populārākajām Excel funkcijām, kas tiek izmantota, lai meklētu vērtību no dažādiem diapazoniem un lapām.
Kā programmā Excel izmantot funkciju COUNTIF : Saskaitiet vērtības ar nosacījumiem, izmantojot šo apbrīnojamo funkciju. Jums nav jāfiltrē dati, lai saskaitītu noteiktas vērtības. Skaitītāja funkcija ir būtiska, lai sagatavotu informācijas paneli.
Kā lietot funkciju SUMIF programmā Excel : Šī ir vēl viena būtiska informācijas paneļa funkcija. Tas palīdz apkopot vērtības noteiktos apstākļos.