Wanneer u sekere take uitvoer in Excel, moet u soms met verskeie tabelle handel, wat ook met mekaar verband hou. Dit beteken dat die data van een tafel na die ander getrek word, en wanneer hulle verander, word die waardes in alle verwante tabelreekse herbereken.
Gekoppelde tabelle is baie nuttig vir die verwerking van groot hoeveelhede inligting. Dit is nie baie gerieflik om al die inligting in een tafel te hê nie, en as dit nie homogeen is nie. Dit is moeilik om met sulke voorwerpe te werk en hulle te soek. Hierdie probleem is bedoel om verwante tafels uit te skakel, waarvan inligting versprei word, maar terselfdertyd ook met mekaar verband hou. Gekoppelde tabelreekse kan nie net binne een vel of een boek geleë wees nie, maar ook in aparte boeke (lêers). In die praktyk word die laaste twee opsies meestal gebruik, aangesien die doel van hierdie tegnologie is om weg te kom van die ophoping van data, en om hulle op dieselfde bladsy op te laai, is die probleem nie fundamenteel op te los nie. Kom ons leer hoe om te skep en hoe om met hierdie soort data bestuur te werk.
Skep van gekoppelde tabelle
Eerstens, laat ons voortgaan met die vraag hoe dit moontlik is om 'n skakel tussen verskillende tabelreekse te skep.
Metode 1: Tabelle met 'n formule direk verbind
Die maklikste manier om data te koppel, is om formules te gebruik wat verband hou met ander tabelreekse. Dit word direk bindend genoem. Hierdie metode is intuïtief, aangesien daarmee die binding op feitlik dieselfde manier plaasvind as om verwysings na data in 'n enkele tabelreeks te skep.
Kom ons kyk hoe 'n voorbeeld 'n verband kan vorm deur direk bindend te wees. Ons het twee tafels op twee velle. In een tabel word die betaalstaat bereken deur 'n formule te gebruik deur die tempo van werkers met 'n enkelkoers vir almal te vermenigvuldig.
Op die tweede blad is daar 'n tabelreeks waarin daar 'n lys van werknemers met hul salarisse is. Die lys van werknemers in albei gevalle word in dieselfde volgorde aangebied.
Dit is nodig om so te maak dat die data op die tariewe van die tweede vel opgeneem word in die ooreenstemmende selle van die eerste.
- Kies die eerste kolom sel op die eerste vel. "Bet". Ons sit in haar punt "=". Klik dan op die etiket "Fiche 2"Wat aan die linkerkant van die Excel-koppelvlak bokant die statusbalk geleë is.
- Gaan na die tweede area van die dokument. Klik op die eerste sel in die kolom. "Bet". Klik dan op die knoppie. Tik op die sleutelbord om data in te voer in die sel waarin die teken voorheen gestel is "Gelykes".
- Dan is daar 'n outomatiese oorgang na die eerste vel. Soos u kan sien, word die koers van die eerste werknemer van die tweede tafel in die toepaslike sel getrek. Nadat ons die wyser op die sel wat die verbintenis het, geplaas word, sien ons dat die gewone formule gebruik word om data op die skerm te vertoon. Maar voor die koördinate van die sel waar die data vertoon word, is daar 'n uitdrukking "Sheet2!"wat die naam van die area van die dokument waar hulle geleë is, aandui. Die algemene formule in ons geval is soos volg:
= Fiche2! B2 - Nou moet jy die data oorplaas op die tariewe van alle ander werknemers van die onderneming. Dit kan natuurlik gedoen word op dieselfde manier as wat ons die taak vir die eerste werknemer bereik het, maar aangesien beide lyste van werknemers in dieselfde volgorde gereël word, kan die taak aansienlik vereenvoudig word en die oplossing bespoedig. Dit kan gedoen word deur eenvoudig die formule na die onderstaande reeks te kopieer. As gevolg van die feit dat skakels in Excel relatief verhoudings is, wanneer hulle gekopieer word, verskuif die waardes, wat ons benodig. Die kopieerprosedure self kan uitgevoer word met behulp van die vul merker.
So, plaas die wyser in die onderste regterkantste gedeelte van die element met die formule. Daarna moet die wyser omskep word in die vorm van 'n swart kruis. Ons voer die klem van die linkermuisknop uit en sleep die wyser heel onder in die kolom.
- Alle data uit dieselfde kolom op Fiche 2 is na die tafel getrek Blad 1. Wanneer data verander na Fiche 2 hulle sal outomaties verander op die eerste.
Metode 2: Gebruik 'n klomp operateurs INDEX - MATCH
Maar wat as die lys van werknemers in tabelraamwerke nie in dieselfde volgorde gereël word nie? In hierdie geval, soos vroeër genoem, is een van die opsies om die verband tussen elk van die selle wat met die hand gekoppel moet word, op te stel. Maar dit is net geskik vir klein tafels. Vir massiewe reekse sal hierdie opsie op sy beste baie tyd neem om te implementeer, en in die ergste geval sal dit glad nie haalbaar wees nie. Maar jy kan hierdie probleem oplos met 'n klomp operateurs INDEKS - MATCH. Kom ons kyk hoe dit gedoen kan word deur data in tabelreekse te koppel, wat in die vorige metode bespreek is.
- Kies die eerste item in die kolom. "Bet". Gaan na Funksie Wizarddeur op die ikoon te klik "Voeg funksie in".
- die Funksiewizard in 'n groep "Skakels en skikkings" vind en kies die naam "Index".
- Hierdie operateur het twee vorms: 'n vorm om met skikkings te werk en 'n verwysing. In ons geval is die eerste opsie nodig, dus in die volgende venster van die vormseleksie wat oop gaan, kies ons dit en klik op die knoppie "OK".
- Die operateur argument venster is uitgevoer. INDEKS. Die taak van die gespesifiseerde funksie is om die waarde wat in die geselekteerde reeks in die lyn met die gespesifiseerde nommer is, te vertoon. Algemene operateur formule INDEKS is dit:
= INDEX (array; line_number; [kolom nommer])"Array" - die argument met die adres van die reeks waaruit ons inligting sal onttrek met die nommer van die gespesifiseerde string.
"Lynnommer" - die argument wat die nommer van hierdie lyn self is. Dit is belangrik om te weet dat die lynnommer nie gespesifiseer moet word relatief tot die hele dokument nie, maar slegs relatief tot die gekose skikking.
"Kolom nommer" - Die argument is opsioneel. Om ons probleem spesifiek op te los, sal ons dit nie gebruik nie, en daarom is dit nie nodig om sy essensie afsonderlik te beskryf nie.
Plaas die wyser in die veld "Array". Daarna gaan na Fiche 2 en hou die linker muisknop in en kies die hele inhoud van die kolom "Bet".
- Nadat die koördinate in die operateurvenster vertoon word, plaas die wyser in die veld "Lynnommer". Ons sal hierdie argument vertoon deur die operateur te gebruik MATCH. Kliek dan op die driehoek wat links van die funksielyn is. 'N Lys van onlangs gebruikte operateurs word geopen. As jy onder hulle die naam kry "MATCH"dan kan jy daarop klik. Andersins, klik op die mees onlangse item in die lys - "Ander kenmerke ...".
- Die standaard venster begin. Funksie Meesters. Gaan dit in dieselfde groep. "Skakels en skikkings". Hierdie keer in die lys, kies die item "MATCH". Voer 'n klik op die knoppie. "OK".
- Aktiveer operateur venster argumente MATCH. Die gespesifiseerde funksie is bedoel om die nommer van 'n waarde in 'n spesifieke skikking met sy naam te vertoon. Danksy hierdie geleentheid sal ons die rynommer van 'n spesifieke waarde vir die funksie bereken. INDEKS. sintaksis MATCH aangebied as:
= MATCH (soekwaarde; opzoeking skikking; [match_type])"Gewaardeerde waarde" - die argument wat die naam of adres van die derdeparty-reeks sel bevat waarin dit geleë is. Dit is die posisie van hierdie naam in die teikenreeks wat bereken moet word. In ons geval sal die eerste argument selverwysings wees Blad 1waarin die name van werknemers geleë is.
"Gewysde skikking" - 'n argument wat 'n skakel na 'n skikking verteenwoordig waarin die gespesifiseerde waarde gesoek word om sy posisie te bepaal. Ons sal hierdie rol speel kolom "Voornaam op Fiche 2.
"Mapping Type" - 'n Argument wat opsioneel is, maar in teenstelling met die vorige stelling, sal ons hierdie opsionele argument nodig hê. Dit dui aan hoe die operateur die verlangde waarde sal ooreenstem met die skikking. Hierdie argument kan een van drie waardes hê: -1; 0; 1. Vir ongeordende skikkings, kies die opsie "0". Hierdie opsie is geskik vir ons saak.
Dus, begin ons die velde van die argumente venster invul. Plaas die wyser in die veld "Gewaardeerde waarde", klik op die eerste sel van die kolom "Naam" op Blad 1.
- Nadat die koördinate vertoon is, stel die wyser in die veld "Gewysde skikking" en gaan op die kortpad "Fiche 2"wat onderaan die Excel-venster bokant die statusbalk geleë is. Hou die linker muisknop vas en merk al die selle in die kolom. "Naam".
- Nadat hul koördinate in die veld vertoon word "Gewysde skikking"Gaan na die veld "Mapping Type" en stel die nommer van die sleutelbord "0". Daarna gaan ons weer terug na die veld. "Gewysde skikking". Die feit is dat ons die formule sal kopieer, soos ons in die vorige metode gedoen het. Daar sal 'n verrekening van adresse wees, maar ons moet die koördinate van die skikking wat aangevra word, regstel. Dit moet nie verskuif nie. Kies die koördinate van die wyser en klik op die funksie sleutel F4. Soos u kan sien, het 'n dollarteken voor die koördinate verskyn, wat beteken dat die skakel van familielid absolute geword het. Klik dan op die knoppie "OK".
- Die resultaat word in die eerste sel van die kolom vertoon. "Bet". Maar voordat ons kopieer, moet ons 'n ander area regmaak, naamlik die eerste argument van die funksie INDEKS. Om dit te doen, kies die element van die kolom wat die formule bevat en skuif na die formulebalk. Kies die eerste argument van die operateur INDEKS (B2: B7) en klik op die knoppie F4. Soos u kan sien, verskyn die dollarteken naby die gekose koördinate. Klik op die knoppie Tik. Oor die algemeen het die formule die volgende vorm geneem:
= INDEX (Sheet2! $ B $ 2: $ B $ 7; MATCH (Sheet1! A4; Sheet2! $ A $ 2: $ A $ 7; 0)) - Nou kan jy met behulp van die vul merker kopieer. Noem dit op dieselfde manier as wat ons vroeër gepraat het, en strek dit aan die einde van die tafelreeks.
- Soos u kan sien, ten spyte van die feit dat die volgorde van die rye van die twee verwante tafels nie ooreenstem nie, word alle waardes egter strenger volgens die name van die werkers. Dit is bereik deur die gebruik van 'n kombinasie van operateurs INDEKS-MATCH.
Sien ook:
Excel funksie INDEX
Die wedstryd funksie in Excel
Metode 3: Voer wiskundige bedrywighede met geassosieerde data uit
Direkte data-binding is ook goed omdat dit nie net waardes wat in ander tabelreekse in een van die tabelle vertoon word, vertoon nie, maar ook om verskeie wiskundige bewerkings met hulle uit te voer (byvoeging, deling, aftrekking, vermenigvuldiging, ens.).
Kom ons kyk hoe dit in die praktyk gedoen word. Kom ons doen dit op Sheet 3 algemene onderneming salaris data sal vertoon word sonder werknemer uiteensetting. Hiervan sal personeeltariewe getrek word Fiche 2, som op (gebruik die funksie som) en vermenigvuldig met die koëffisiënt met die formule.
- Kies die sel waar die totale betaalstaat vertoon sal word Sheet 3. Klik op die knoppie "Voeg funksie in".
- Dit moet die venster oopmaak Funksie Meesters. Gaan na die groep "Wiskundige" en kies die naam daar "Som". Klik dan op die knoppie "OK".
- Skuif na die funksie argument venster somwat ontwerp is om die som van die gekose getalle te bereken. Dit het die volgende sintaksis:
= SUM (nommer1; getal2; ...)Die velde in die venster stem ooreen met die argumente van die gespesifiseerde funksie. Alhoewel hulle nommer 255 stukke kan bereik, sal ons net genoeg wees vir ons doel. Plaas die wyser in die veld "Nommer 1". Klik op die etiket "Fiche 2" bokant die statusbalk.
- Nadat ons na die verlangde gedeelte van die boek verskuif het, kies die kolom wat opgesom moet word. Ons maak dit die wyser, hou die linker muisknop. Soos u kan sien, word die koördinate van die geselekteerde gebied dadelik vertoon in die veld van die argument venster. Klik dan op die knoppie. "OK".
- Daarna beweeg ons outomaties na Blad 1. Soos u kan sien, word die totale bedrag van die loonkoers van werkers reeds in die ooreenstemmende element vertoon.
- Maar dis nie alles nie. Soos ons onthou, word die salaris bereken deur die waarde van die koers met die koëffisiënt te vermenigvuldig. Daarom kies ons weer die sel waarin die opgesomde waarde geleë is. Daarna gaan jy na die formulebalk. Ons voeg 'n vermenigvuldigingsteken by sy formule (*), en klik dan op die element waarin die koëffisiënt geleë is. Om die berekening uit te voer, klik op Tik op die sleutelbord. Soos u kan sien, het die program die totale loon vir die onderneming bereken.
- Gaan terug na Fiche 2 en verander die grootte van die tarief van enige werknemer.
- Daarna gaan jy weer na die bladsy met die totale bedrag. Soos u kan sien, weens veranderinge in die verwante tabel, is die uitslag van die totale loon outomaties herbereken.
Metode 4: spesiale invoeging
U kan ook tabelraamwerke in Excel koppel met 'n spesiale insetsel.
- Kies die waardes wat aan 'n ander tabel moet "strenger" word. In ons geval is dit die kolomreeks. "Bet" op Fiche 2. Klik op die gekose fragment met die regter muisknop. Kies die item in die lys wat oopmaak "Copy". Alternatiewe sleutel kombinasie is Ctrl + C. Daarna beweeg na Blad 1.
- As u na die verlangde area van die boek beweeg, kies ons die selle waarin u die waardes wil trek. In ons geval is dit 'n kolom. "Bet". Klik op die gekose fragment met die regter muisknop. In die konteks kieslys in die nutsbalk "Invoegingsopsies" Klik op die ikoon "Voeg skakel in".
Daar is ook 'n alternatief. Terloops, dit is die enigste een vir ouer weergawes van Excel. In die konteks kieslys, beweeg die wyser na die item "Plak Spesiaal". Kies die item met dieselfde naam in die bykomende kieslys wat oopmaak.
- Daarna word 'n spesiale invoegvenster oop. Ons druk die knoppie "Voeg skakel in" in die onderste linkerhoek van die sel.
- Watter opsie u ook kies, sal waardes van een tabel skikking in 'n ander ingevoeg word. Wanneer u die data in die bron verander, sal hulle ook outomaties verander in die ingevoegde reeks.
Les: Plak Spesiaal in Excel
Metode 5: Verhouding tussen tabelle in veelvuldige boeke
Daarbenewens kan u die verband tussen tafelspasies in verskillende boeke organiseer. Dit gebruik die spesiale invoegtoets. Aksies sal heeltemal soortgelyk wees aan dié wat ons in die vorige metode oorweeg het, behalwe dat navigasie tydens die bekendstelling van die formules nie tussen dele van een boek nie, maar tussen lêers moet plaasvind. Natuurlik moet alle verwante boeke oop wees.
- Kies die reeks data wat u na 'n ander boek wil oordra. Kliek op dit met die regter muisknop en kies die posisie in die kieslys wat oopmaak "Copy".
- Dan beweeg ons na die boek waarin hierdie data ingevoeg moet word. Kies die gewenste reeks. Klik op die regter muisknop. In die konteks kieslys in die groep "Invoegingsopsies" kies 'n item "Voeg skakel in".
- Daarna sal die waardes ingevoeg word. Wanneer u die data in die bronboek verander, sal die tabelreeks uit die werkboek outomaties opgetrek word. En dit is glad nie nodig vir albei boeke om hiervoor oop te wees nie. Dit is genoeg om net een werkboek oop te maak, en dit sal outomaties die data van die geslote gekoppelde dokument insleep, as daar voorheen veranderinge gemaak is.
Maar dit moet op gelet word dat in hierdie geval die invoeging in die vorm van 'n onveranderlike skikking gemaak sal word. As jy probeer om 'n sel met ingevoegde data te verander, sal daar 'n boodskap verskyn waarin jy kan weet dat dit nie moontlik is om dit te doen nie.
Veranderinge in so 'n skikking wat met 'n ander boek geassosieer word, kan slegs gemaak word deur die skakel te verbreek.
Ontkoppeling tussen tabelle
Soms is dit nodig om die verband tussen tabelreekse te verbreek. Die rede hiervoor kan wees, soos hierbo beskryf, wanneer u 'n skikking van 'n ander boek wil plaas, of bloot omdat die gebruiker nie wil hê dat die data in een tabel outomaties van 'n ander opgedateer moet word nie.
Metode 1: ontkoppel tussen boeke
U kan die verband tussen boeke in alle selle verbreek deur feitlik een operasie uit te voer. Terselfdertyd sal die data in die selle bly, maar hulle sal reeds statiese nie-opgedateerde waardes wees wat nie van ander dokumente afhanklik is nie.
- In die boek, waarin waardes van ander lêers getrek word, gaan na die blad "Data". Klik op die ikoon "Wysig skakels"wat op die band in die blok gereedskap geleë is "Connections". Daar moet kennis geneem word dat indien die huidige boek nie skakels na ander lêers bevat nie, hierdie knoppie onaktief is.
- Die venster vir die verandering van skakels word bekendgestel. Kies uit die lys van verwante boeke (indien daar verskeie is) die lêer waarmee ons die verbinding wil verbreek. Klik op die knoppie "Breek die skakel".
- 'N Inligtingsvenster word oop waarin daar 'n waarskuwing is oor die gevolge van verdere aksies. As jy seker is van wat jy gaan doen, klik dan op die knoppie. "Breek bande".
- Daarna word alle verwysings na die gespesifiseerde lêer in die huidige dokument vervang met statiese waardes.
Metode 2: Voeg waardes in
Maar die bogenoemde metode is slegs geskik as u al die skakels tussen die twee boeke heeltemal moet skei. Wat om te doen as jy verwante tafels wat binne dieselfde lêer is, wil ontkoppel? U kan dit doen deur die data te kopieer en dit dan op dieselfde plek as die waardes in te plak.Terloops, kan dieselfde metode gebruik word om die verband tussen verskillende datareekse van verskillende boeke te verbreek sonder om die algemene verband tussen lêers te verbreek. Kom ons kyk hoe hierdie metode in die praktyk werk.
- Kies die reeks waarin ons die skakel na 'n ander tabel wil verwyder. Klik op dit met die regter muisknop. Kies die item in die kieslys wat oopmaak "Copy". In plaas van hierdie aksies kan jy 'n alternatiewe sneltoets kombinasie tik. Ctrl + C.
- Dan, sonder om die seleksie uit dieselfde fragment te verwyder, klik ons weer met die regter muisknop. Hierdie keer in die lys van aksies kliek ons op die ikoon "Waardes"wat in 'n groep gereedskap geplaas word "Invoegingsopsies".
- Daarna word alle skakels in die gekose reeks vervang met statiese waardes.
Soos u kan sien, het Excel metodes en gereedskap om verskeie tabelle saam te koppel. In hierdie geval kan die tabel data op ander blaaie wees en selfs in verskillende boeke. Indien nodig, kan hierdie verbinding maklik gebreek word.
