Atliekant tam tikras užduotis "Excel", kartais turi susidoroti su keliomis lentelėmis, kurios taip pat yra susijusios su viena su kita. Tai reiškia, kad iš vienos lentelės duomenys yra sugriežtinti kitiems ir vertes visose susijusiose lentelėse yra perskaičiuojami, kai jie keičiami.
Susijusios lentelės yra labai patogios naudoti dideliam informacijos kiekiui. Įdėkite visą informaciją vienoje lentelėje, be to, jei tai nėra vienalytė, o ne labai patogu. Sunku dirbti su tokiais objektais ir ieškoti jų. Nurodyta problema yra tiesiog skirta pašalinti susijusias lenteles, informacija, kuri yra paskirstyta, bet tuo pačiu metu yra tarpusavyje sujungta. Susijusios lentelės gali būti ne tik viename lape arba vienoje knygoje, bet ir esančioje atskirose knygose (failuose). Paskutinės dvi praktikos parinktys dažniausiai naudojamos, nes šios technologijos tikslas yra tiesiog išeiti nuo duomenų kaupimo, o jų nykimas viename puslapyje neišsprendžia iš esmės. Sužinokite, kaip sukurti ir kaip dirbti su tokiu duomenų valdymo tipu.
Sukuriant susijusias lenteles
Visų pirma, sutelkime dėmesį į klausimą, kokiu būdu galima sukurti ryšį tarp įvairių lentelių.1 metodas: tiesioginės surišimo lentelės formulė
Lengviausias būdas susieti duomenis yra formulių naudojimas, kuriame yra nuorodos į kitas lenteles. Jis vadinamas tiesioginiu privalomu. Šis metodas yra intuityvus, nes kai jis jungia jį beveik kaip ir nuorodas į duomenis vienu lentelės masyvu.
Pažiūrėkime, kaip pavyzdys galite suformuoti tiesioginį privalomą. Mes turime dvi lenteles dviem lapais. Toje pačioje lentelėje atlyginimas apskaičiuojamas naudojant formulę, padauginus darbuotojų normas vienam koeficientui.
Antrajame lape yra lentelės diapazonas, kuriame yra darbuotojų sąrašas su savo atlyginimu. Abiem atvejais darbuotojų sąrašas pateikiamas viename užsakyme.
Būtina padaryti, kad duomenys apie statymus iš antrojo lapo priveržkite į atitinkamas pirmojo ląstelių ląsteles.
- Pirmajame lape, mes skiriame pirmąją "Bet" stulpelio ląstelę. Mes įdėjome į ženklą "=". Be to, spustelėkite ant "lapo 2" etiketė, kuri yra dedama ant kairėje Excel sąsajos per būsenos juostą.
- Antrajame dokumento srityje yra judėjimas. Spustelėkite pirmąją ląstelę "Bet" stulpelyje. Tada spustelėkite mygtuką Enter mygtuką ant klaviatūros įvesti duomenis į ląstelę, kurioje anksčiau buvo įdiegta "lygi" ženklas.
- Tada yra automatinis perėjimas prie pirmojo lapo. Kaip matome, pirmojo darbuotojo vertė iš antrosios lentelės yra ištraukta į atitinkamą ląstelę. Įdiegus žymeklį ant ląstelės, kuriame yra statymo, matome, kad įprasta formulė naudojama ekrane rodomi duomenys. Bet priešais ląstelių koordinates, iš kur yra duomenys, yra išraiška "List2!", Kuris nurodo dokumento srities pavadinimą, kuriame jie yra. Bendra mūsų bylos formulė atrodo taip:
= Sąrašas2! B2
- Dabar jums reikia perduoti duomenis apie visų kitų įmonės darbuotojų tarifus. Žinoma, tai galima padaryti taip pat, kaip mes įvykdėme pirmojo darbuotojo užduotį, tačiau manydamas, kad abu darbuotojų sąrašai yra tokiu pačiu būdu, užduotis gali būti žymiai supaprastinta ir paspartinta jos sprendimu. Tai galima padaryti tiesiog kopijuojant formulę į žemiau esantį diapazoną. Dėl to, kad nuorodos į "Excel" yra santykinis, kai kopijuojant savo vertes, vertės perėjimas yra perkeliamas į tai, kad mums reikia. Pati kopijavimo procedūra gali būti atliekama naudojant užpildymo žymeklį.
Taigi, mes įdėjome žymeklį į apatiniame dešiniajame elemento sritimi formulėje. Po to žymeklis turi konvertuoti į pripildymo žymeklį į juodos kryžiaus forma. Atliekame kairįjį pelės mygtuką ir ištraukiame žymeklį į stulpelio numerį.
- Visi duomenys iš panašaus stulpelio ant 2 lapo buvo ištrauktas į lentelę ant lapo 1. Kai duomenys pasikeičia ant lapo, jie bus automatiškai pakeisti pirmojo.
2 metodas: Naudojant operatorių sprogimą - paiešką
Bet ką daryti, jei darbuotojų sąrašą lentelės masyvai nėra į tą pačią tvarka? Šiuo atveju, kaip nurodyta anksčiau, viena iš galimybių yra įdiegti ryšį tarp kiekvienos iš šių ląstelių, kurios turėtų būti susijęs rankiniu būdu. Bet tai tinka, išskyrus mažas lenteles. Dėl masinių intervalų ši galimybė geriausiai užtruks daug laiko įgyvendinant, ir blogiausiu atveju - praktiškai tai bus nerealu. Tačiau ši problema gali būti išspręsta naudojant operatorių indeksą - paiešką. Pažiūrėkime, kaip tai galima padaryti atliekant duomenis lentelėse, kuriose pokalbis buvo ankstesniame metode.
- Pažymėjome pirmąjį "statymo" kolonėlės elementą. Eikite į funkcijų vedlį spustelėję piktogramą "Įterpti funkciją".
- Funkcijų "Nuorodos ir matricos" funkcijų vedlyje randame ir paskirsime pavadinimą "Index".
- Šis operatorius turi dvi formas: formą dirbti su masyvais ir nuorodomis. Mūsų atveju reikia pirmosios parinktis, todėl atveriate kito formų pasirinkimo lange, pasirinkite jį ir spustelėkite mygtuką "OK".
- Operatoriaus argumentai Indeksas pradeda veikti. Nurodytos funkcijos užduotis yra vertės, esančios pasirinktoje diapazone, produkcija linijoje su nurodytu numeriu. Bendras formulės operatoriaus indeksas:
= Indeksas (masyvas; NUMBS_NAME; [NUMBS_STOLBITES])
"Array" yra argumentas, kuriame yra diapazono diapazonas, iš kurio išsamiai išgausime nurodytos eilutės numerį.
"Eilės numeris" yra argumentas, kuris yra šios eilutės numeris. Svarbu žinoti, kad linijos numeris turėtų būti nurodytas ne visam dokumentui, bet tik palyginti su paskirstytu masyvu.
"Stulpelio numeris" yra argumentas, kuris yra neprivalomas. Norėdami išspręsti konkrečiai mūsų užduotį, mes to nenaudosime, todėl nebūtina jį apibūdinti atskirai.
Mes įdėjome žymeklį į "masyvo" lauke. Po to, eikite į 2 lapą ir laikydami kairįjį pelės mygtuką, pasirinkite visą "greičio" stulpelio turinį.
- Po koordinatės rodomos operatoriaus lange, laukiame žymeklį į "eilės numerį". Mes atšauksime šį argumentą naudodami paieškos operatorių. Todėl spustelėkite trikampį, kuris yra kairėje nuo funkcijos eilutės. Atsidaro naujai naudojamų operatorių sąrašas. Jei radote pavadinimą "Ieškoti kompanija" tarp jų, galite spustelėti ant jo. Priešingu atveju spustelėkite naujausią sąrašo tašką - "Kitos funkcijos ...".
- Paleidžiama standartinis langų vedlio langas. Eikite į tą pačią grupę "Nuorodos ir matricos". Šį kartą sąraše pasirinkite elementą "Ieškoti kompanija". Atlikite mygtuką "Gerai".
- Atliekamas paieškos argumentų argumentų aktyvinimas. Nurodyta funkcija yra sukurta siekiant išvesti vertės numerį konkrečioje masyvo pagal jo pavadinimą. Tai yra dėl šios funkcijos, kad apskaičiuojame funkcijos funkcijos specifinės vertės eilutės skaičių. Pateikiama paieškos valdybos sintaksė:
= Paieškos taryba (Search_name; žiūrėti__nassing; [Type_Station])
"Norima" yra argumentas, kuriame nurodomas trečiosios šalies intervalo ląstelės pavadinimas arba adresas, kuriame jis yra. Tai yra šio pavadinimo pozicija tikslinėje srityje ir turėtų būti apskaičiuojama. Mūsų atveju pirmojo argumento vaidmuo bus nurodytas 1 lapo lape, kuriame yra darbuotojai.
"Sultuminis masyvas" yra argumentas, kuris yra nuoroda į masyvą, kuris atlieka nurodytos vertės paiešką, kad nustatytumėte savo poziciją. Mes turėsime šį vaidmenį vykdant "pavadinimo" stulpelio adresą 2 lape.
"Palyginimo tipas" - argumentas, kuris yra neprivalomas, tačiau, skirtingai nei ankstesnis operatorius, šis neprivalomas argumentas bus reikalingas. Tai rodo, kaip suderinti operatorių yra norima vertė su masyvu. Šis argumentas gali turėti vieną iš trijų verčių: -1; 0; 1. Sudarytoms masyvams pasirinkite parinktį "0". Ši parinktis tinka mūsų atveju.
Taigi, toliau užpildykite argumentų langų laukus. Mes įdėjome žymeklį į lauką "rūkas vertė", spustelėkite pirmąjį ląstelės "Vardas" stulpelį lape 1.
- Po koordinatės rodomas, nustatykite žymeklį į "sąrašą masyvi" lauke ir eikite į "lapo 2" etiketę, kuri yra esančio "Excel" lango apačioje virš būsenos juostos apačioje. Užsukite kairįjį pelės mygtuką ir paryškinkite žymeklį visus "pavadinimo" stulpelio ląsteles.
- Po jų koordinatės rodomos lauke "Sąrašas masyvi" lauke, eikite į lauko "žemėlapių tipo" ir nustatykite numerį "0" nuo klaviatūros. Po to mes vėl grįšime į lauką "Žvelgiant per masyvą". Faktas yra tai, kad atliksime formulę, kaip atlikome ankstesniame metode. Bus skirta adresų perėjimas, bet čia matomo masyvo koordinatės turime užtikrinti. Jis neturėtų pereiti. Pažymėkite koordinates su žymekliu ir spustelėkite F4 funkcijų klavišą. Kaip matote, dolerio ženklas pasirodė prieš koordinates, o tai reiškia, kad nuoroda iš giminės tapo absoliučiu. Tada spustelėkite mygtuką "Gerai".
- Rezultatas rodomas pirmojoje "Bet" stulpelio ląstelėje. Tačiau prieš kopijavimą turime nustatyti kitą sritį, ty pirmąjį argumento funkcijos indeksą. Norėdami tai padaryti, pasirinkite stulpelio elementą, kuriame yra formulė ir pereikite prie formulės eilutės. Paskirti pirmąjį operatoriaus indekso argumentą (B2: B7) ir spustelėkite mygtuką F4. Kaip matote, dolerio ženklas pasirodė šalia pasirinktų koordinačių. Spustelėkite įvesties klavišą. Apskritai, formulė buvo tokia forma:
= Indeksas (lapas2! $ B $ 2: $ b $ 7; Paieškos lenta (lapas1! A4, sąrašas2! $ 2: $ 7, 0))
- Dabar galite kopijuoti naudojant užpildymo žymeklį. Tai vadiname tuo pačiu būdu, kaip mes kalbėjome anksčiau ir ištempėme į lentelės ribų.
- Kaip matote, nepaisant to, kad styginių eilės tvarka dviem susijusiose lentelėse nesutampa, vis dėlto visos vertės yra sugriežtintos pagal darbuotojų vardus. Tai buvo pasiekta dėl operatorių paieškos derinio naudojimo.
4 metodas: specialus įdėklas
"Tie" lentelės matricos "Excel" taip pat gali naudoti specialų įterpimą.
- Pasirinkite reikšmes, kurias norite "priveržti" į kitą lentelę. Mūsų atveju tai yra "statymo" stulpelio diapazonas ant lapo 2. Spustelėkite specialų fragmentą su dešiniuoju pelės mygtuku. Atidarant sąrašą pasirinkite elementą "Kopijuoti". Alternatyvus derinys yra CTRL + C klavišų derinys. Po to mes pereiname prie lapo 1.
- Persikėlimas į reikalingo knygos sritį, paskirsto ląsteles, kuriose reikės sugriežtinti vertybes. Mūsų atveju tai yra "pasiūlymo" stulpelis. Paspauskite ant specialaus fragmento su dešiniuoju pelės mygtuku. "Įterpti parametrų" įrankių juostoje esančiame kontekste spustelėkite piktogramą "Įterpti ryšį".
Taip pat yra alternatyva. Jis, beje, yra vienintelė senesnių versijų "Excel". Konteksinio meniu mes atnešime žymeklį į "specialų įdėklą". Papildomame meniu, kuris atsidaro, pasirinkite tą patį pavadinimą.
- Po to atsidaro specialus įdėklas. Spustelėkite mygtuką "Įterpti ryšį" apatiniame kairiajame langelio kampe.
- Nepriklausomai nuo pasirinkimo pasirinkimo vertės iš vienos lentelės masyvo bus įterptas į kitą. Keičiant duomenis šaltinyje, jie taip pat automatiškai pakeis į įterptą diapazoną.
Pamoka: specialus įdėklas "Excel"
5 metodas: ryšiai tarp lentelių keliose knygose
Be to, galite organizuoti ryšį tarp stalo sričių skirtingose knygose. Tai naudoja specialų įdėklo įrankį. Veiksmai bus visiškai panašūs į tuos, kuriuos mes apsvarstėme ankstesniame metodo, išskyrus tai, kad formulių metu naršymas nebus tarp vienos knygos sričių, bet tarp failų. Žinoma, turėtų būti atidarytos visos susijusios knygos.
- Pasirinkite duomenų diapazoną, kuris bus perkeltas į kitą knygą. Spustelėkite jį dešiniuoju pelės mygtuku ir atidarytu meniu pasirinkite "Copy" poziciją.
- Tada mes pereiname prie knygos, kurioje turėtų būti įterpti šie duomenys. Pasirinkite norimą diapazoną. Spustelėkite dešinį pelės mygtuką. Sąryšio meniu "Įterpti nustatymus" grupę pasirinkite elementą "Įterpti ryšį".
- Po to bus įterptos vertės. Keičiant duomenis į šaltinio knygoje, stalo masyvo iš darbo knygos bus priveržtos juos automatiškai. Ir tai nėra būtina užtikrinti, kad abi knygos būtų atviros. Pakanka atidaryti vienintelę darbo knygą ir automatiškai pritaikys duomenis iš uždarojo dokumento, jei jame buvo ankstesni pakeitimai.
Tačiau reikėtų pažymėti, kad šiuo atveju įdėklas bus gaminamas nepakitusios masyvo forma. Bandant keisti bet kokį langelį su įterptais duomenimis, pranešimas bus apgyvendintas informavimas apie nesugebėjimą tai padaryti.
Pokyčiai tokio masyvo, susijusio su kita knyga, gali nutraukti ryšį.
Pavadinimas tarp lentelių
Kartais reikia nutraukti ryšį tarp lentelių. To priežastis gali būti pirmiau aprašyta, kai norite pakeisti masyvą, įterptą iš kitos knygos ir tiesiog vartotojo nenoras, kad tos pačios lentelės duomenys automatiškai atnaujinami iš kito.1 metodas: komunikacijos pertraukos tarp knygų
Norėdami nutraukti ryšį tarp knygų visose ląstelėse, atliekant faktiškai vieną operaciją. Šiuo atveju ląstelių duomenys išliks, tačiau jie jau bus statiniai, kurie nėra atnaujintos vertės, kurios nepriklauso nuo kitų dokumentų.
- Knygoje, kurioje yra sugriežtinti kitų failų vertės, eikite į skirtuką Duomenys. Spustelėkite piktogramą "Keisti nuorodos", esančią "Connection" įrankių juostoje esančioje juostoje. Pažymėtina, kad jei dabartinėje knygoje nėra ryšių su kitais failais, šis mygtukas yra neaktyvus.
- Paleidžiamas nuorodos keitimo langas. Pasirinkite iš susijusių knygų sąrašo (jei yra keletas iš jų) failą, su kuriuo mes norime nutraukti ryšį. Spustelėkite mygtuką "Sulaužykite ryšį".
- Atsidaro informacijos langas, kuris suteikia įspėjimą apie tolesnių veiksmų pasekmes. Jei esate tikri, kad ketinate daryti, spustelėkite mygtuką "Break Communication".
- Po to visos nuorodos į nurodytą failą dabartiniame dokumente bus pakeista statinėmis vertėmis.
2 metodas: verčių įterpimas
Tačiau pirmiau minėtas metodas tinka tik tada, kai reikia visiškai nutraukti visas dviejų knygų ryšius. Ką daryti, jei reikia atjungti su asocijuotomis lentelėmis tame pačiame faile? Tai galite padaryti kopijuojant duomenis, tada įdėkite tą pačią vietą kaip vertes. Beje, šis metodas gali būti plyšęs tarp atskirų duomenų diapazonų įvairių knygų nesilaikant bendro ryšio tarp failų. Pažiūrėkime, kaip šis metodas veikia praktikoje.
- Pažymėjome diapazoną, kuriame norime ištrinti bendravimą su kita lentele. Spustelėkite jį dešiniuoju pelės mygtuku. Atidaryti meniu pasirinkite "Kopijuoti" elementą. Vietoj konkrečių veiksmų galite surinkti alternatyvų karštų klavišų Ctrl + C derinį.
- Toliau, nepašalinant pasirinkimo iš to paties fragmento, vėl spustelėkite jį su dešiniuoju pelės mygtuku. Šį kartą į veiksmų sąrašą spustelėkite "Vertės" piktogramą, kuri yra paskelbta įterpti parametrų grupėje.
- Po to visos nuorodos į specialų diapazoną bus pakeista statinėmis reikšmėmis.
Kaip matote, "Excel" turi būdus ir priemones, skirtas susieti keletą stalų tarpusavyje. Tuo pačiu metu lentelės duomenys gali būti ant kitų lapų ir net skirtingose knygose. Jei reikia, šis ryšys gali būti lengvai pažeistas.