As u sekere take in Excel uitvoer, moet u soms verskillende tabelle hanteer, wat ook verband hou. Dit wil sê dat data van een tabel na 'n ander getrek word en wanneer dit verander word, word die waardes herbereken in alle verwante tabelreekse.
Gekoppelde tabelle is baie handig om te gebruik vir die verwerking van 'n groot hoeveelheid inligting. Om al die inligting in een tabel te plaas, is dit ook nie so gerieflik as dit nie homogeen is nie. Dit is moeilik om met sulke voorwerpe te werk en daarna te soek. Die aangeduide probleem is ontwerp om uitgeskakel te word deur gekoppelde tabelle, waarvan die inligting versprei word, maar terselfdertyd met mekaar verbind word. Gekoppelde tabelreekse kan nie net binne 'n enkele vel of in 'n enkele boek geleë wees nie, maar kan ook in aparte boeke (lêers) geleë wees. Laasgenoemde twee opsies word meestal in die praktyk gebruik, aangesien die doel van hierdie tegnologie is om weg te kom van die opeenhoping van data, en om dit op een bladsy op te stel, los nie die probleem fundamenteel op nie. Kom ons leer hoe om hierdie tipe databestuur te skep en te werk.
Skep gekoppelde tabelle
Laat ons eerstens nadink oor watter metodes daar 'n geleentheid bestaan om 'n verwantskap tussen verskillende tabelreekse te skep.
Metode 1: skakel tabelle direk met 'n formule
Die maklikste manier om data te bind, is om formules te gebruik wat verwys na ander tabelreekse. Dit word direkte binding genoem. Hierdie metode is intuïtief, aangesien die skakel hiermee op dieselfde manier uitgevoer word as om skakels na data in een tabelreeks te skep.
Kom ons kyk hoe 'n band 'n voorbeeld kan vorm deur direkte binding. Ons het twee tafels op twee velle. Op een tabel word die betaalstaat bereken deur die formule te gebruik deur die werknemer se koers met 'n enkele koëffisiënt vir almal te vermenigvuldig.
Op die tweede blad is 'n tabelreeks met 'n lys van werknemers met hul salarisse. Die lys werknemers word in albei gevalle in dieselfde volgorde aangebied.
Dit is nodig om seker te maak dat die gegewens oor die tariewe vanaf die tweede vel in die ooreenstemmende selle van die eerste getrek word.
- Kies die eerste sel in die kolom op die eerste bladsy "Bet". Ons plaas 'n teken daarin "=". Klik op die kortpad "Blad 2", wat aan die linkerkant van die Excel-koppelvlak bo die statusbalk geleë is.
- Gaan na die tweede gedeelte van die dokument. Ons 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 bladsy. Soos u kan sien, word die koers van die eerste werknemer van die tweede tafel na die ooreenstemmende sel getrek. Deur die wyser op die sel te plaas wat die weddenskap bevat, sien ons dat die gewone formule gebruik word om data op die skerm te vertoon. Maar voor die koördinate van die sel waarvandaan die data uitgevoer word, is daar 'n uitdrukking "Sheet2!", wat die naam aandui van die dokumentgebied waar dit geleë is. Die algemene formule in ons geval lyk so:
= Blad2! B2 - Nou moet u die data oordra oor die tariewe van alle ander werknemers van die onderneming. Natuurlik kan dit gedoen word op dieselfde manier as wat ons die taak vir die eerste werknemer voltooi het, maar aangesien beide werknemerslyste in dieselfde volgorde gerangskik is, kan die taak aansienlik vereenvoudig word en die oplossing daarvan vinniger gemaak word. Dit kan gedoen word deur die formule eenvoudig na die onderstaande reeks te kopieer. As gevolg van die feit dat die skakels in Excel standaard relatief is, word die waardes verskuif wanneer dit gekopieër word, en dit is wat ons nodig het. Die kopiëringsprosedure self kan met die vulmerker gedoen word.
Sit die muis in die onderste regterkant van die element met die formule. Daarna moet die wyser omgeskakel word na 'n vulteken in die vorm van 'n swart kruis. Klem die linkermuisknop vas en sleep die wyser heel onder in die kolom.
- Alle data uit 'n soortgelyke kolom op Blad 2 is in 'n tafel ingetrek Blad 1. Wanneer u data verander na Blad 2 hulle sal outomaties verander op die eerste.
Metode 2: gebruik 'n klomp INDEX-operators - SOEK
Maar wat as die lys werknemers in die tabelreeks nie in dieselfde volgorde is nie? In hierdie geval, soos vroeër genoem, is een van die opsies om 'n verband te bewerkstellig tussen elk van die selle wat met die hand gekoppel moet word. Maar dit is slegs geskik vir klein tafels. Vir massiewe reekse sal so 'n opsie op die beste tyd baie tyd neem om te implementeer, en in die ergste geval is dit in die praktyk nie haalbaar nie. Maar hierdie probleem kan met behulp van 'n klomp bestuurders opgelos word INDEKS - MATCH. Kom ons kyk hoe dit gedoen kan word deur die data in die tabelreeks wat in die vorige metode bespreek is, te koppel.
- Kies die eerste kolomelement "Bet". Gaan na Funksie-towenaardeur op die ikoon te klik "Voeg funksie in".
- die Funksie-towenaar in die groep Verwysings en skikkings vind en merk die naam "Index".
- Hierdie operateur het twee vorms: 'n vorm om met skikkings te werk en 'n verwysingsvorm. In ons geval is die eerste opsie nodig, daarom kies u dit in die volgende venster vir die keuse van die vorm wat oopmaak, en klik op die knoppie. "OK".
- Operateur-argumente-venster van stapel gestuur INDEKS. Die taak van hierdie funksie is om 'n waarde uit te voer wat binne die gekose reeks in die lyn met die gespesifiseerde nommer is. Algemene operateurformule INDEKS so is:
= INDEX (skikking; rynummer; [kolomnummer])"Array" - 'n argument met die adres van die reeks waaruit ons inligting met die nommer van die gespesifiseerde reël sal onttrek.
Lyn nommer - die argument, wat die nommer van hierdie einste lyn is. Dit is belangrik om te weet dat die lynnommer nie moet spesifiseer in verhouding tot die hele dokument nie, maar slegs relatief tot die geselekteerde skikking.
Kolomnommer - 'n opsionele argument. Ons sal dit nie gebruik om ons spesifieke probleem op te los nie, en dit is dus nie nodig om die wese daarvan afsonderlik te beskryf nie.
Sit die wyser in die veld "Array". Gaan daarna na Blad 2 en hou die linkermuisknop ingedruk, kies al die inhoud van die kolom "Bet".
- Plaas die muis in die veld nadat die koördinate in die venster van die operateur vertoon is Lyn nommer. Ons voer hierdie argument aan met behulp van die operateur MATCH. Daarom klik ons op die driehoek, links van die funksielyn. 'N Lys met die operateur wat onlangs gebruik is, word oopgemaak. As u 'n naam onder hulle vind "MATCH"dan kan u daarop klik. Andersins, klik op die laaste item op die lys - "Ander funksies ...".
- Die standaardvenster begin Funksie towenaars. Ons gee dit aan dieselfde groep Verwysings en skikkings. Kies hierdie keer die item in die lys "MATCH". Klik op die knoppie. "OK".
- Operateur-argumente-venster is geaktiveer MATCH. Die gespesifiseerde funksie is bedoel om die nommer van 'n waarde in 'n spesifieke skikking met die naam daarvan te vertoon. Danksy hierdie funksie bereken ons die lynnommer van 'n sekere waarde vir die funksie INDEKS. sintaksis MATCH word soos volg voorgestel:
= SOEK (search_value; lookup_array; [match_type])"Soek waarde" - 'n argument met die naam of adres van die sel van die derdeparty-reeks waarin dit geleë is. Dit is die posisie van hierdie naam in die teikengebied wat bereken moet word. In ons geval is die eerste argument verwysings na selle op Blad 1waar die name van werknemers geleë is.
Array gesien - 'n argument wat 'n verwysing na 'n skikking voorstel waarin die gespesifiseerde waarde gesoek word om die posisie daarvan te bepaal. Die kolomadres "sal hierdie rol hier speel."Voornaam op Blad 2.
Passingsoort - 'n argument wat opsioneel is, maar anders as die vorige stelling, benodig ons hierdie opsionele argument. Dit dui aan hoe die operateur die soekwaarde met die skikking sal ooreenstem. Hierdie argument kan een van drie waardes hê: -1; 0; 1. Kies vir ongeordende skikkings "0". Hierdie opsie is geskik vir ons geval.
Laat ons dus die velde van die argumente-venster invul. Sit die wyser in die veld "Soek waarde"Klik op die eerste sel van die kolom "Naam" op Blad 1.
- Stel die wyser in die veld nadat die koördinate vertoon is Array gesien en klik op die kortpad "Blad 2", wat aan die onderkant van die Excel-venster bokant die statusbalk geleë is. Hou die linkermuisknop ingedruk en selekteer alle selle in die kolom met die muis "Naam".
- Nadat hul koördinate in die veld vertoon is Array gesiengaan veld toe Passingsoort en stel die nommer daar vanaf die sleutelbord "0". Daarna keer ons weer terug veld toe Array gesien. Die feit is dat ons die formule sal kopieer, soos in die vorige metode. Adresverskuiwing sal plaasvind, maar hier moet ons die koördinate van die skikking wat gekyk word, regstel. Dit moet nie verplaas word nie. Kies die koördinate met die muis en druk op die funksie-toets F4. Soos u kan sien, het die dollarteken voor die koördinate verskyn, wat beteken dat die skakel van relatief na absoluut verander het. Klik dan op die knoppie "OK".
- Die resultaat word in die eerste sel van die kolom vertoon. "Bet". Maar voordat ons kopieër, moet ons 'n ander gebied oplos, naamlik die eerste argument van die funksie INDEKS. Om dit te doen, kies die kolomelement wat die formule bevat, en beweeg na die lyn met formules. Ons kies die eerste argument van die operateur INDEKS (B2: B7) en klik op die knoppie F4. Soos u kan sien, het die dollarteken naby die geselekteerde koördinate verskyn. Klik op die knoppie Tik. In die algemeen het die formule die volgende vorm aangeneem:
= INDEX (Sheet2! $ B $ 2: $ B $ 7; SOEK (Sheet1! A4; Sheet2! $ A $ 2: $ A $ 7; 0)) - Nou kan u met die vulmerker kopieer. Ons noem dit op dieselfde manier as wat ons vroeër gepraat het, en strek dit tot aan die einde van die tafelreeks.
- Soos u kan sien, alhoewel die ryorde van die twee verwante tabelle nie saamval nie, word alle waardes volgens die name van werknemers opgetrek. Dit is bereik deur die gebruik van 'n kombinasie van operateurs INDEKS-MATCH.
Lees ook:
EXEX funksie in Excel
EXCEL-funksie in Excel
Metode 3: voer wiskundige bewerkings met verwante data uit
Direkte databinding is ook goed, want dit stel u in staat om nie net waardes wat in ander tabelreekse in een van die tabelle vertoon word, te vertoon nie, maar ook verskillende wiskundige bewerkings daarmee uit te voer (optelling, deling, aftrek, vermenigvuldiging, ens.).
Kom ons kyk hoe dit in die praktyk geïmplementeer word. Kom ons maak dit aan Blad 3 algemene salarisdata vir die onderneming sal sonder 'n verdeling deur werknemers vertoon word. Om dit te doen, sal werknemers se tariewe verkry word Blad 2, opgesom (met behulp van die funksie som) en vermenigvuldig met 'n koëffisiënt deur die formule te gebruik.
- Kies die sel waar die resultaat van die berekening van die betaalstaat vertoon word. Blad 3. Klik op die knoppie. "Voeg funksie in".
- Venster moet begin Funksie towenaars. Gaan na die groep "Wiskundige" en kies die naam daar "Som". Klik nou op die knoppie "OK".
- Die funksie-argumente word na die venster geskuif som, wat ontwerp is om die som van die geselekteerde getalle te bereken. Dit het die volgende sintaksis:
= SUM (getal1; getal2; ...)Die velde in die venster stem ooreen met die argumente van die gespesifiseerde funksie. Alhoewel hulle getal 255 kan bereik, sal net een genoeg wees vir ons doel. Sit die wyser in die veld "Nommer 1". Klik op die kortpad "Blad 2" bo die statusbalk.
- Nadat ons na die gewenste gedeelte van die boek oorgegaan het, kies die kolom wat opgesom moet word. Ons doen dit met die wyser terwyl u die linkermuisknop ingedruk hou. Soos u kan sien, word die koördinate van die geselekteerde gebied onmiddellik in die veld van die argumente-venster vertoon. Klik dan op die knoppie "OK".
- Daarna skuif ons outomaties na Blad 1. Soos u kan sien, word die totale hoeveelheid werknemersbotte reeds in die ooreenstemmende element vertoon.
- Maar dit is nie al nie. Soos ons onthou, word salaris bereken deur die waarde van die koers met 'n faktor te vermenigvuldig. Daarom kies ons weer die sel waarin die opgesomde waarde geleë is. Daarna gaan ons na die lyn met formules. Voeg 'n vermenigvuldigingsteken by die formule daarin (*), en klik dan op die element waarin die koëffisiënt-aanwyser geleë is. Klik op die knoppie om die berekening uit te voer Tik op die sleutelbord. Soos u kan sien, het die program die totale loon vir die onderneming bereken.
- Terug na Blad 2 en die koers van enige werknemer te verander.
- Daarna skuif ons weer na die bladsy met die totale bedrag. As gevolg van die veranderinge in die gekoppelde tabel is die resultaat van die totale loon outomaties herbereken.
Metode 4: pasgemaakte invoegsel
U kan ook tabelarray's in Excel met behulp van 'n spesiale invoegsel koppel.
- Ons kies die waardes wat in 'n ander tabel "getrek" moet word. In ons geval is dit die omvang van die kolom "Bet" op Blad 2. Ons klik op die geselekteerde fragment met die regter muisknop. Kies in die lys wat oopmaak "Copy". 'N Alternatiewe kortpad is Ctrl + C. Daarna verhuis ons na Blad 1.
- Nadat ons na die gebied van die boek wat ons benodig, beweeg het, kies ons die selle waarin ons die waardes moet optrek. In ons geval is dit 'n kolom "Bet". Ons klik op die geselekteerde fragment met die regter muisknop. In die konteksmenu in die instrumentblok Voeg opsies in Klik op die ikoon Plak skakel.
Daar is ook 'n alternatief. Terloops, dit is die enigste vir ouer weergawes van Excel. Hou die knoppie oor in die konteksmenu "Spesiale insetsel". Kies die posisie met dieselfde naam in die addisionele menu wat oopmaak.
- Daarna word die spesiale invoegvenster oop. Klik op die knoppie Plak skakel in die linker onderste hoek van die sel.
- Watter opsie u ook al kies, waardes uit een tabelreeks word in 'n ander ingevoeg. As u data in die bron verander, sal hulle ook outomaties verander in die ingevoegde reeks.
Les: Spesiale invoeging in Excel
Metode 5: skakel tussen tabelle in verskeie boeke
Daarbenewens kan u kommunikasie tussen tafelareas in verskillende boeke organiseer. 'N Spesiale invoeginstrument word gebruik. Die aksies sal presies dieselfde wees as dié wat ons in die vorige metode oorweeg het, behalwe dat u moet navigeer terwyl u nie formules maak tussen gebiede van dieselfde boek nie, maar tussen lêers. Natuurlik moet alle verwante boeke oop wees.
- Kies die reeks data wat u na 'n ander boek wil oordra. Klik met die rechtermuisknop daarop en kies die posisie in die menu wat oopmaak. "Copy".
- Dan gaan ons na die boek waarin hierdie gegewens ingevoeg moet word. Kies die gewenste reeks. Regsklik. In die konteksmenu in die groep Voeg opsies in kies item Plak skakel.
- Daarna word die waardes ingevoeg. Wanneer data in die bronwerkboek verander, sal die tabelreeks uit die werkboek dit outomaties optrek. Dit is ook nie nodig dat albei boeke hiervoor oop is nie. Dit is genoeg om slegs een werkboek oop te maak, en dit sal outomaties data uit 'n geslote gekoppelde dokument trek as daar al voorheen veranderinge daaraan aangebring is.
Maar daar moet op gelet word dat die insetsel in hierdie geval as 'n onveranderlike skikking gemaak word. As u probeer om enige sel met die ingevoegde data te verander, verskyn daar 'n boodskap wat u inlig dat dit onmoontlik is om dit te doen.
Veranderings in so 'n skikking wat met 'n ander boek verband hou, kan slegs aangebring word deur die skakel te verbreek.
Gaping tussen tafels
Soms moet u die verband tussen tabelreekse verbreek. Die rede hiervoor kan óf die bogenoemde geval wees, wanneer u 'n skikking wat in 'n ander boek ingevoeg is, moet verander, óf bloot die onwilligheid van die gebruiker dat die data in een tabel outomaties van 'n ander opgedateer word.
Metode 1: verbinding tussen boeke verbreek
U kan die verband tussen boeke in alle selle verbreek deur feitlik een bewerking uit te voer. Terselfdertyd sal die data in die selle bly, maar dit sal reeds statiese waardes wees wat nie opdateer kan word nie, wat op geen manier van ander dokumente afhanklik is nie.
- Gaan na die oortjie in die boek waarin waardes van ander lêers getrek word "Data". Klik op die ikoon "Verander kommunikasie"op die lint in die gereedskapskas geleë "Connections". Daar moet op gelet word dat as die huidige boek nie skakels na ander lêers bevat nie, hierdie knoppie nie aktief is nie.
- Die skakel verander venster begin. Ons kies die lêer waarmee ons die verbinding wil verbreek uit die lys met verwante boeke (as daar verskeie is). Klik op die knoppie Breek die skakel.
- 'N Inligtingsvenster word oopgemaak waarin 'n waarskuwing bestaan oor die gevolge van verdere aksies. As u seker is wat u gaan doen, klik dan op die knoppie "Bande breek".
- Daarna sal alle skakels na die gespesifiseerde lêer in die huidige dokument vervang word met statiese waardes.
Metode 2: Voeg waardes in
Maar bogenoemde metode is slegs geskik as u die skakels tussen die twee boeke heeltemal moet verbreek. Wat moet u doen as u die verwante tabelle in dieselfde lêer moet skei? U kan dit doen deur die data te kopieer en dan op dieselfde plek as die waardes te plak. Terloops, op dieselfde manier kan u die verbinding tussen die individuele datareeks van verskillende boeke verbreek sonder om die algemene verband tussen die lêers te verbreek. Kom ons kyk hoe hierdie metode prakties werk.
- Kies die reeks waarin ons die skakel na 'n ander tabel wil verwyder. Ons klik daarop met die regter muisknop. Kies in die menu wat oopmaak "Copy". In plaas van hierdie aksies, kan u 'n alternatiewe kombinasie van snelkieslyne tik Ctrl + C.
- Regs-klik daarna weer sonder om die seleksie uit dieselfde fragment te verwyder. Klik hierdie keer in die lys van aksies op die ikoon "Waardes"wat in die gereedskapgroep geleë is Voeg opsies in.
- Daarna sal alle skakels in die gekose reeks vervang word met statiese waardes.
Soos u kan sien, is daar in Excel maniere en instrumente om verskillende tabelle aan mekaar te koppel. Terselfdertyd kan gegewens op ander velle en selfs in verskillende boeke verskyn. Indien nodig, kan hierdie verbinding maklik verbreek word.
