Legați foile în Excel. Cum să vă conectați datele din diferite registre de lucru în Excel

Din păcate, dacă cartea sursă a fost ștearsă/mutată sau redenumită, conexiunea va fi întreruptă. De asemenea, conexiunea se va pierde dacă mutați fișierul țintă (care conține linkul). Dacă trimiteți fișierul final doar prin poștă, nici destinatarul nu va putea actualiza linkurile.

Cum se întrerupe conexiunea

O modalitate de a rezolva această problemă este întreruperea conexiunii. Dacă există un singur link în fișier, atunci acesta este destul de simplu:

Accesați fila Date.
Selectați o echipă Schimbați conexiunileÎn capitolul Conexiuni.
Clic Rupe conexiunea.

IMPORTANT! Când conexiunea este întreruptă, toate formulele care se referă la cartea sursă vor fi convertite în valori! Anularea acestei operațiuni este imposibilă!

Cum să deconectați toate cărțile

Pentru comoditate, puteți utiliza o macrocomandă care va întrerupe conexiunile cu toate cărțile. Macrocomanda face parte din programul de completare VBA-Excel. Pentru a-l folosi aveți nevoie de:

Cod VBA

Codul pentru o macrocomandă care șterge toate linkurile către o carte este prezentat mai jos. Îl poți copia în proiectul tău.

Sub UnlinkWorkBooks() Dim WbLinks Dim i As Long Select Case MsgBox(„Toate referințele la alte registre de lucru vor fi eliminate din acest fișier, iar formulele care fac referire la alte registre de lucru vor fi înlocuite cu valori.” & vbCrLf & „Sunteți sigur că doriți să continuați ? ", 36, "Întrerupe legătura?") Cazul 7 " Fără ieșire Sub End Selectați WbLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks) If Not IsEmpty(WbLinks) Then For i = 1 To UBound(WbLinks.B ActiveWorks) Name: =WbLinks(i), Type:=xlLinkTypeExcelLinks Next Else MsgBox „Acest fișier nu conține linkuri către alte registre de lucru.”, 64, „Legături către alte registre de lucru” End If End Sub

Cum se întrerupe conexiunile numai în intervalul selectat

Uneori există multe link-uri într-o carte și există îngrijorarea că eliminarea unui link ar putea elimina pe cea suplimentară. Pentru a evita acest lucru, puteți utiliza programul de completare pentru a șterge conexiunile numai în intervalul selectat. Pentru aceasta.

Uneori, datele pe care trebuie să le găsiți pot fi localizate în tabele diferite. Adesea trebuie să selectați date din mai multe tabele Excel. Utilizatorul ar trebui să poată determina din ce tabel să facă o selecție pe baza criteriilor condițiilor pentru un anumit tip de date. Puteți crea o formulă specială pentru aceasta.

Cum să legați două tabele cu o singură formulă pentru a eșantiona o CĂUTARE V după condiție

Figura de mai jos prezintă un tabel pentru calcularea sumei impozitului. Utilizatorul are capacitatea de a determina starea civilă a angajatului (căsătorit sau necăsătorit). Dacă utilizatorul selectează condiția „Single”, selecția trebuie făcută conform tabelului „Angajații singuri”. Dacă se selectează criteriul „Căsătorit”, eșantionul se va face conform tabelului „Angajați căsătoriți”. Formula de calcul a impozitelor presupunând un angajat căsătorit sau necăsătorit al companiei:

Pentru a crea o comutare între tabele, puteți utiliza numele intervalului de celule și funcția INDIRECT. Apoi trebuie să creați o formulă. Mai întâi trebuie să creați două intervale denumite:

Căsătorit – pentru tabelul „Angajați căsătoriți”.
Necăsătorit – pentru tabelul „Angajați necăsătoriți”.

Pentru a atribui nume separate pentru fiecare dintre intervalele acestor două tabele, procedați în felul următor:

Pentru acuratețea și comoditatea introducerii valorilor de intrare într-o celulă... este folosită o listă derulantă creată de instrument: „DATE” - „Lucrul cu date” - „Verificarea datelor” - „Tipul de date:” - „ Listă".

Lista derulantă constă doar din două valori: „Căsătorit” „Celucăr”. Exact la fel ca numele numelor intervalelor de celule create mai devreme. Valoarea celulei E12 va fi folosită pentru a comuta între tabele atunci când se caută după condiție. Prin urmare, valorile și numele intervalelor trebuie să fie identice.

Această formulă se bazează pe funcția CĂUTARE V. Al doilea argument al său, care specifică tabelul sursă, conține funcția INDIRECT. Această funcție are un prim argument, Cell Reference, care convertește textul primit într-o referință de celulă sau interval. În prima imagine, celula E12 conține valoarea „Single”. Funcția INDIRECT încearcă să convertească acest text într-o referință de celulă sau un nume de interval. Dacă textul nu este convertit într-o referință de celulă (ca în acest exemplu), atunci funcția INDIRECT verifică dacă există nume de intervale de celule cu același nume în registrul de lucru. Dacă astfel de nume de interval nu ar fi fost create, atunci funcția ar returna o eroare cu codul #REF!

Sintaxa funcției INDIRECT are un al doilea argument opțional numit „A1”. Valoarea TRUE din acest argument înseamnă că referința de celulă este scrisă în format A1, iar valoarea FALSE înseamnă că referința de celulă este scrisă în format R1C1. În cazul numelor de intervale de celule numite, funcția INDIRECT va returna rezultatul corect, indiferent dacă al doilea argument opțional „A1” este TRUE sau FALSE.

Funcția INDIRECT poate returna și referințe externe către alte foi și chiar alte registre de lucru Excel. Dar cu condiția ca registrul de lucru la care face referire funcție să fie deschis. În caz contrar, va fi returnată o eroare cu codul #LINK!

Un tabel legat este o colecție de date care poate fi gestionată ca o singură unitate.

Utilizați butonul pentru a crea un tabel legat „Format ca tabel” pe panou "Stiluri" benzi "Acasă".

Selectați stilul dorit pentru viitorul tabel și specificați intervalul de celule pe baza căruia va fi creat tabelul aferent.

După setarea tuturor parametrilor, vom obține tabelul asociat. Lângă titlurile coloanelor vor apărea butoane cu săgeți (similar cu filtrarea), iar în fereastra programului va apărea un instrument contextual „Lucrul cu mesele” care contine banda "Constructor".

Fiecare tabel conectat primește un nume unic. Valoarea implicită este „Număr_tabel”. Puteți schimba numele tabelului în panou „Proprietăți”.

Pe panou „Stiluri de masă” Dacă este necesar, puteți modifica sau personaliza stilul tabelului legat.

Puteți transforma un tabel conectat înapoi într-un interval obișnuit de celule. Există un buton pentru asta „Conversie în interval” pe panou "Serviciu" feed contextual "Constructor".

După cum am menționat mai devreme, lângă antetele coloanei există butoane de sortare și filtrare care vă permit să sortați datele și să aplicați filtre.

Într-un tabel conectat, puteți calcula totalurile coloanelor. Pentru a face acest lucru, bifați caseta „Rând total” pe panou „Opțiuni de stil de masă”.

Folosind lista derulantă, puteți selecta valoarea dorită a funcției finale.

Puteți adăuga/elimina rânduri și coloane într-un tabel legat.

Acest lucru se poate face în mai multe moduri.

1. Folosiți butonul „Redimensionați tabelul” pe panou „Proprietăți”.

2. Plasați cursorul în celula tabelului legat, lângă care trebuie să adăugați o nouă coloană (rând) și în panou "Celule" benzi "Acasă" utilizați butonul "Introduce".

În acest articol, veți învăța cum să îmbinați rapid datele din două tabele Excel atunci când coloanele cheie nu se potrivesc exact. De exemplu, când identificatorul unic din primul tabel sunt primele cinci caractere ale identificatorului din al doilea tabel. Toate soluțiile propuse în acest articol au fost testate de mine în Excel 2013, 2010 și 2007.

Deci, există două foi Excel care trebuie combinate pentru analiza ulterioară a datelor. Să presupunem că un tabel conține prețuri (coloana Preț) și descrieri ale mărfurilor (coloana Bere) pe care le vindeți, iar al doilea tabel afișează date despre disponibilitatea mărfurilor în stoc (coloana În stoc). Dacă dvs. sau colegii dvs. ați compilat ambele tabele pentru catalog, atunci ambele trebuie să conțină cel puțin o coloană cheie cu identificatori unici de produs. Descrierea produsului sau prețul se poate modifica, dar identificatorul unic rămâne întotdeauna același.

Dificultățile încep atunci când primești niște tabele de la producător sau de la alte departamente ale companiei. Lucrurile pot deveni și mai complicate dacă un nou format pentru identificatori unici este introdus brusc sau dacă unitățile de păstrare a stocurilor în depozit (SKU) se modifică chiar și ușor. Și vă confruntați cu sarcina de a combina tabele noi și vechi cu datele din Excel. Într-un fel sau altul, apare o situație când există doar o potrivire parțială a înregistrărilor în coloanele cheie, de exemplu, „ 12345 " Și " 12345-sufix_nou„. Înțelegi că acesta este același SKU, dar computerul nu este atât de inteligent! Această potrivire non-exactă face imposibilă utilizarea formulelor Excel obișnuite pentru a combina datele din două tabele.

Și ceea ce este cu adevărat rău este că corespondențele pot fi complet neclare și „ O companie„ într-un singur tabel se poate transforma în „ CJSC „O companie”„în alt tabel și „ Companie nouă (fosta Some Company)" Și " Compania veche” se va dovedi a fi, de asemenea, un record al aceleiași firme. Știi asta, dar cum poate Excel să explice asta?

Cometariu: Soluțiile descrise în acest articol sunt universale. Le puteți adapta pentru utilizare ulterioară cu orice formule standard, cum ar fi CĂUTARE V(CĂUTARE V) CĂUTARE(MECI) GPR(Căutare) și așa mai departe.

Selectați exemplul potrivit pentru a trece direct la soluția de care aveți nevoie:

O coloană cheie dintr-unul dintre tabele conține caractere suplimentare

Să ne uităm la două tabele. Coloanele primului tabel conțin numărul produsului (SKU), numele berii (Bere) și prețul acesteia (Preț). Al doilea tabel înregistrează SKU și numărul de sticle în stoc (În stoc). În loc de bere poate exista orice produs, iar numărul de coloane în viața reală poate fi mult mai mare.

Într-un tabel cu simboluri suplimentare, creați o coloană auxiliară. Puteți să-l adăugați la sfârșitul tabelului, dar cel mai bine este să îl introduceți în dreptul coloanei cheie, astfel încât să fie vizibil.

Coloana cheie din tabelul din exemplul nostru este A cu date SKU și trebuie să extrageți primele 5 caractere din acesta. Să adăugăm o coloană auxiliară și să o denumim Ajutor SKU:

Gata! Acum avem coloane cheie cu potriviri exacte de valoare - coloană Ajutor SKUîn tabelul și coloana principală SKUîn tabelul în care se va efectua căutarea.

Acum folosind funcția CĂUTARE V(CĂUTARE V) obținem rezultatul dorit:

Alte formule

Extrage mai întâi X caractere la dreapta: de exemplu, 6 caractere la dreapta de la intrarea „DSFH-164900”. Formula va arăta astfel:
DREAPTA(A2,6)
=DREAPTA(A2,6)
Sari mai întâi X caractere, extrageți următoarele Y caractere: de exemplu, trebuie să extrageți „0123” din intrarea „PREFIX_0123_SUFF”. Aici trebuie să sărim peste primele 8 caractere și să extragem următoarele 4 caractere. Formula va arăta astfel:
PSTR(A2;8;4)
=MID(A2;8;4)
Extrageți toate caracterele până la delimitator; lungimea secvenței rezultate poate varia. De exemplu, trebuie să extrageți „123456” și „0123” din înregistrările „123456-suffix” și, respectiv, „0123-suffix”. Formula va arăta astfel:
LEFTSIMB(A2;FIND("-",A2)-1)
=STÂNGA(A2,GĂSĂ("-",A2)-1)

Pe scurt, puteți utiliza funcții Excel precum LEVSIMV(STÂNGA), DREAPTA(DREAPTA), PSTR(MID), GĂSI(FIND) pentru a prelua orice părți ale unui index compus. Dacă aveți dificultăți în acest sens, vom face tot posibilul pentru a vă ajuta.

Datele dintr-o coloană cheie din primul tabel sunt împărțite în două sau mai multe coloane din al doilea tabel

Să presupunem că tabelul pe care îl căutați conține o coloană cu identificatori. Celulele din această coloană conțin intrări precum XXXX-AAAA, Unde XXXX– aceasta este denumirea de cod a unui grup de bunuri (telefoane mobile, televizoare, camere video, camere) și YYYY– acesta este codul produsului din cadrul grupului. Tabelul principal este format din două coloane: una conține coduri de grup de produse (Grup), a doua conține coduri de produs (ID). Nu putem elimina pur și simplu codurile de grup de produse, deoarece același cod de produs poate fi repetat în grupuri diferite.

Într-o celulă C2 Să scriem următoarea formulă:

CONCATENATE(A2;"-";B2)
=CONCATENATE(A2,"-",B2)

Aici A2– aceasta este adresa celulei care conține codul grupului; simbolul " – ” este un separator; B2– aceasta este adresa celulei care conține codul produsului. Să copiem formula în liniile rămase.

Acum, combinarea datelor din cele două tabele noastre nu va fi dificilă. Vom potrivi coloana ID complet primul tabel cu coloană ID a doua masă. Dacă se găsește o potrivire, intrările din coloane DescriereȘi Preț al doilea tabel va fi adăugat la primul tabel.

Datele din coloanele cheie nu se potrivesc

Iată un exemplu: sunteți proprietarul unui mic magazin, primiți mărfuri de la unul sau mai mulți furnizori. Fiecare dintre ele are propria nomenclatură, diferită de a ta. Ca urmare, apar situații în care intrarea dvs. „Case-Ip4S-01” se potrivește cu intrarea „SPK-A1403” din fișierul Excel primit de la furnizor. Astfel de discrepanțe apar aleatoriu și nu există o regulă generală pentru a converti automat „SPK-A1403” în „Case-Ip4S-01”.

Vesti proaste: Datele cuprinse în aceste două tabele Excel vor trebui prelucrate manual pentru a le putea combina ulterior.

Vești bune: Acest lucru trebuie făcut o singură dată, iar tabelul auxiliar rezultat poate fi salvat pentru utilizare ulterioară. În continuare, puteți îmbina automat aceste tabele și astfel economisiți mult timp

1. Creați un tabel auxiliar pentru căutare.

Creați o nouă foaie Excel și denumiți-o Convertor SKU. Copierea întregii coloane SKU-ul nostru dintr-o foaie Magazin la o foaie nouă, ștergeți duplicatele și lăsați numai valori unice în ea.

Adăugați o coloană lângă ea Supp.SKUși căutați manual corespondența între valorile coloanei SKU-ul nostruȘi Supp.SKU(descrierile din coloană ne vor ajuta în acest sens Descriere). Aceasta este o treabă plictisitoare, lasă-te să fii fericit cu gândul că trebuie să o faci o singură dată :-).

Ca urmare, avem următorul tabel:

2. Actualizați tabelul principal folosind datele din tabelul de căutare.

Introduceți o nouă coloană în tabelul principal (foaia Store) Supp.SKU.

Apoi, folosind funcția CĂUTARE V(CĂUTARE V) comparați foile MagazinȘi Convertor SKU SKU-ul nostru, iar pentru datele actualizate – o coloană Supp.SKU.

Coloană Supp.SKU completate cu codurile originale ale producătorului.

Cometariu: Dacă în coloană Supp.SKU apar celule goale, apoi trebuie să luați toate codurile SKU corespunzătoare acestor celule goale, adăugați-le în tabel Convertor SKUși găsiți codul corespunzător din tabelul furnizorilor. După aceasta, repetați pasul 2.

3. Transferați datele din tabelul de căutare în tabelul principal

Tabelul nostru principal are o coloană cheie cu o potrivire exactă pentru intrările din tabelul de căutare, așa că această sarcină este acum ușoară

Folosind funcția CĂUTARE V(CĂUTARE V) fuzionați datele din fișa Magazin cu datele fișei Furnizor cu ridicata 1, folosind coloana pentru a găsi potriviri Supp.SKU.

Iată un exemplu de date actualizate într-o coloană Pret cu ridicata:

E simplu, nu-i așa? Pune-ți întrebările în comentariile articolului, voi încerca să răspund cât mai curând posibil.

Uneori trebuie să creați o legătură imuabilă între o celulă și o valoare dintr-o altă celulă, fie introdusă în ea manual, fie obținută dintr-o formulă.

În fereastra deschisă a foii de lucru, selectați celula cu valoarea inițială.
Utilizați orice metodă de copiere cunoscută (butonul „Copiere” din fila „Acasă”, Ctrl+C etc.).
Selectați celula care va fi legată de cea originală.
Accesați fila „Acasă” și în grupul „Clipboard”, extindeți meniul butonului „Lipire”.
În lista de comenzi, selectați „Insert Link” (Fig. 4.4).
Salvați rezultatul făcând clic pe tasta Esc.

Notă

Modificările ulterioare ale valorilor din celula sursă vor fi reflectate automat în celula conectată.

Orez. 4.4. Fila Acasă. Inserați meniul butonului. Elementul „Inserați conexiunea”

Un link de tabel Excel este o formulă care returnează date dintr-o celulă dintr-un alt registru de lucru. Când deschideți un registru de lucru care conține linkuri, Excel citește cele mai recente informații din registrul de lucru sursă (linkuri de actualizare)

Relațiile între tabele în Excel sunt utilizate pentru a prelua date atât din alte foi dintr-un registru de lucru, cât și din alte registre de lucru Excel. De exemplu, aveți un tabel care calculează valoarea totală a vânzărilor. Calculul utilizează prețurile produselor și volumul vânzărilor. În acest caz, este logic să creați un tabel separat cu date despre prețuri, care va fi extras folosind conexiunile primului tabel.

Când creați o relație între tabele, Excel creează o formulă care include numele registrului de lucru sursă cuprins între paranteze, numele foii de lucru urmat de un semn de exclamare și o referință de celulă.

Crearea de legături între registrele de lucru

Deschideți ambele registre de lucru în Excel
În registrul de lucru sursă, selectați celula care trebuie conectată și copiați-o (comandă rapidă de la tastatură Ctrl+C)
Accesați registrul de lucru țintă, faceți clic dreapta pe celula în care vrem să plasăm conexiunea. Selectați din meniul derulant Inserție specială
Inserție specială alege Introduceți conexiunea.

Există o altă opțiune, mai simplă, pentru a crea o relație între tabele. În celula în care dorim să inserăm un link, puneți un semn egal (la fel ca pentru o formulă obișnuită), mergeți la registrul de lucru sursă, selectați celula pe care vrem să o legăm și faceți clic pe Enter.

Puteți utiliza instrumentele de copiere și completare automată pentru formulele de relație la fel ca și pentru formulele obișnuite.

Înainte de a crea relații între tabele

Înainte de a începe să răspândiți cunoștințele despre ideile dvs. mari, iată câteva sfaturi pentru a lucra cu relații în Excel:

Faceți conexiuni ușor de urmărit. Nu există o căutare automată pentru toate celulele care conțin linkuri. Prin urmare, utilizați un format specific pentru a identifica rapid legăturile către alte tabele, altfel documentul care conține legăturile poate crește într-o asemenea măsură încât este dificil de întreținut.

Calcule automate. Registrul de lucru sursă trebuie să funcționeze în modul de calcul automat (setat implicit). Pentru a schimba parametrul de calcul, faceți clic pe fila Formule la grup Calcul. Selectați Opțiuni de calcul –> Automat.

Actualizarea conexiunilor

Pentru a actualiza manual relația dintre tabele, accesați fila Date la grup Conexiuni. Faceți clic pe butonul Schimbați conexiunile.

În caseta de dialog care apare Schimbarea conexiunilor Actualizați.

Întrerupeți linkurile în registrele de lucru Excel

Deconectarea unei surse va face ca formulele de link existente să fie înlocuite cu valorile pe care le returnează. De exemplu, linkul =Prețuri!$B$4 va fi înlocuit cu 16. Ruperea linkului nu poate fi anulată, așa că înainte de a efectua operația, recomand să salvați cartea.

Accesați fila Date la grup Conexiuni. Faceți clic pe butonul Schimbați conexiunile.În caseta de dialog care apare Schimbarea conexiunilor selectați conexiunea care vă interesează și faceți clic pe butonul Rupe conexiunea.

Când efectuați anumite sarcini în Excel, uneori trebuie să aveți de-a face cu mai multe tabele, care sunt, de asemenea, interconectate. Adică, datele dintr-un tabel sunt atrase în altele și, atunci când se modifică, valorile din toate intervalele asociate de tabel sunt recalculate.

Tabelele legate sunt foarte convenabile de utilizat pentru procesarea unor cantități mari de informații. A avea toate informațiile într-un singur tabel, în plus, dacă nu este omogen, nu este foarte convenabil. Este dificil să lucrezi cu astfel de obiecte și să le cauți. Tabelele legate între ele, informațiile între care sunt distribuite dar în același timp interconectate, sunt concepute pentru a elimina această problemă. Intervalele de tabel înrudite pot fi localizate nu numai într-o foaie sau într-un registru de lucru, ci și în registre de lucru (fișiere) separate. Ultimele două opțiuni sunt folosite cel mai des în practică, deoarece scopul acestei tehnologii este tocmai acela de a scăpa de acumularea de date, iar strângerea lor pe o singură pagină nu rezolvă în mod fundamental problema. Să învățăm cum să creăm și să lucrăm cu acest tip de management al datelor.

Crearea tabelelor legate

În primul rând, să ne uităm la întrebarea cum este posibil să se creeze o relație între diferitele intervale de tabel.

Metoda 1: Conectarea directă a tabelelor cu o formulă

Cel mai simplu mod de a lega datele este de a folosi formule care fac referire la alte intervale de tabel. Se numește legare directă. Această metodă este intuitivă, deoarece realizează legătura aproape în același mod ca și crearea de legături către date dintr-o singură matrice de tabel.

Să vedem cum, folosind un exemplu, se poate forma o legătură prin legătură directă. Avem două mese pe două foi. Pe un tabel, salariile sunt calculate folosind o formulă prin înmulțirea ratei lucrătorilor cu un coeficient comun tuturor.

A doua fișă conține un interval de tabel care conține o listă de angajați cu salariile lor. Lista angajaților în ambele cazuri este prezentată în aceeași ordine.

Este necesar să vă asigurați că datele privind tarifele din a doua foaie sunt trase în celulele corespunzătoare primei.

Pe prima foaie, selectați prima celulă a coloanei „Pariu”. Am pus semnul „=” în el. Apoi, faceți clic pe fila „Foaie 2”, care se află în partea stângă a interfeței Excel, deasupra barei de stare.
Se trece la a doua zonă a documentului. Faceți clic pe prima celulă din coloana „Pariu”. Apoi faceți clic pe butonul Enter de pe tastatură pentru a introduce date în celula în care ați setat anterior semnul egal.
Apoi există o tranziție automată la prima foaie. După cum puteți vedea, rata primului angajat din cel de-al doilea tabel este trasă în celula corespunzătoare. Prin plasarea cursorului pe celula care conține pariul, vedem că formula obișnuită este folosită pentru afișarea datelor pe ecran. Dar înainte de coordonatele celulei din care sunt scoase datele, există expresia „Sheet2!”, care indică numele zonei documentului în care se află acestea. Formula generală în cazul nostru arată astfel:
Acum trebuie să transferați date despre tarifele tuturor celorlalți angajați ai întreprinderii. Desigur, acest lucru se poate face în același mod în care am finalizat sarcina pentru primul angajat, dar având în vedere că ambele liste de angajați sunt situate în aceeași ordine, sarcina poate fi simplificată semnificativ și soluționarea acesteia poate fi accelerată. Acest lucru se poate face prin simpla copiere a formulei în intervalul de mai jos. Datorită faptului că linkurile din Excel sunt relative în mod implicit, atunci când sunt copiate, valorile se schimbă, ceea ce avem nevoie. Procedura de copiere în sine poate fi efectuată folosind marcatorul de umplere.
Deci, plasați cursorul în zona din dreapta jos a elementului cu formula. După aceasta, cursorul ar trebui să se transforme într-un marker de umplere sub forma unei cruci negre. Țineți apăsat butonul stâng al mouse-ului și trageți cursorul în partea de jos a coloanei.
Toate datele dintr-o coloană similară de pe foaia 2 au fost introduse în tabelul de pe foaia 1. Când datele de pe foaia 2 se schimbă, se vor schimba automat pe prima.

Metoda 2: utilizarea unei combinații de operatori INDEX - CĂUTARE

Dar ce să faci dacă lista de angajați din tablourile nu este în aceeași ordine? În acest caz, așa cum sa discutat mai devreme, o opțiune este stabilirea manuală a unei legături între fiecare dintre acele celule care trebuie conectate. Dar acest lucru este potrivit doar pentru mese mici. Pentru gamele masive, o astfel de opțiune, în cel mai bun caz, va dura mult timp pentru a fi implementată și, în cel mai rău caz, în practică nu va fi deloc fezabilă. Dar această problemă poate fi rezolvată folosind o combinație de operatori INDEX - CĂUTARE. Să vedem cum se poate face acest lucru prin legarea datelor în intervale tabelare, care au fost discutate în metoda anterioară.

Selectați primul element al coloanei „Pariu”. Accesați Expertul pentru funcții făcând clic pe pictograma „Inserare funcție”.
În Function Wizard, în grupul „Links and Arrays”, găsiți și selectați numele „INDEX”.
Acest operator are două forme: un formular pentru lucrul cu matrice și un formular de referință. În cazul nostru, prima opțiune este necesară, așa că în următoarea fereastră de selecție a formularului care se deschide, selectați-o și faceți clic pe butonul „OK”.
Fereastra argumentelor operatorului INDEX a fost lansată. Sarcina funcției specificate este de a afișa valoarea situată în intervalul selectat în linia cu numărul specificat. Formula generală pentru operatorul INDEX este:
INDEX(matrice;număr_linie;)

„Matrice” este un argument care conține adresa intervalului din care vom extrage informații după numărul liniei specificate.

„Numărul liniei” este un argument care este numărul acestei linii. Este important de știut că numărul liniei nu trebuie specificat în raport cu întregul document, ci doar în raport cu matricea selectată.

„Numărul coloanei” este un argument opțional. Nu îl vom folosi pentru a rezolva problema noastră specifică și, prin urmare, nu este nevoie să-i descriem separat esența.

Plasați cursorul în câmpul „Matrice”. După aceasta, mergeți la Foaia 2 și, ținând apăsat butonul din stânga al mouse-ului, selectați întregul conținut al coloanei „Pariu”.
După ce coordonatele sunt afișate în fereastra operatorului, plasați cursorul în câmpul „Număr de linie”. Vom scoate acest argument folosind operatorul MATCH. Prin urmare, facem clic pe triunghi, care este situat în stânga liniei de funcție. Se deschide o listă de operatori utilizați recent. Dacă găsiți numele „CĂUTARE” printre ele, puteți da clic pe el. În caz contrar, faceți clic pe ultimul element din listă – „Alte funcții...”.
Se deschide fereastra standard Function Wizard. În el mergem la același grup „Legături și matrice”. De data aceasta, selectați elementul „CĂUTARE” din listă. Faceți clic pe butonul „OK”.
Fereastra de argumente ale operatorului MATCH este activată. Această funcție este concepută pentru a afișa numărul unei valori dintr-o anumită matrice după numele său. Datorită acestei caracteristici, vom calcula numărul de rând al unei anumite valori pentru funcția INDEX. Sintaxa MATCH este prezentată după cum urmează:
MATCH(valoare_căutare, matrice_căutare;)

„Valoare de căutare” este un argument care conține numele sau adresa celulei din intervalul terță parte în care se află. Este poziția acestui articol în intervalul țintă care trebuie calculată. În cazul nostru, primul argument va fi link-uri către celulele din Foaia 1 în care se află numele angajaților.

„Lookup Array” este un argument care este o referință la o matrice în care valoarea specificată este căutată pentru a-i determina poziția. Pentru noi, acest rol va fi jucat de adresa coloanei „Nume” din Foaia 2.

„Tipul de potrivire” este un argument opțional, dar spre deosebire de declarația anterioară, vom avea nevoie de acest argument opțional. Specifică modul în care operatorul va potrivi valoarea de căutare cu matricea. Acest argument poate avea una dintre cele trei valori: -1; 0; 1. Pentru matrice neordonate, selectați opțiunea „0”. Această opțiune este potrivită pentru cazul nostru.

Deci, să începem să completăm câmpurile ferestrei de argumente. Plasați cursorul în câmpul „Search value”, faceți clic pe prima celulă a coloanei „Nume” din foaia 1.
După ce sunt afișate coordonatele, plasați cursorul în câmpul „Matrice vizibilă” și accesați comanda rapidă „Foaie 2”, care se află în partea de jos a ferestrei Excel, deasupra barei de stare. Țineți apăsat butonul stâng al mouse-ului și selectați cu cursorul toate celulele din coloana „Nume”.
După ce coordonatele lor sunt afișate în câmpul „Matrice vizualizată”, accesați câmpul „Tip de potrivire” și utilizați tastatura pentru a seta numărul „0” acolo. După aceasta, revenim din nou la câmpul „Matrice vizualizată”. Cert este că vom copia formula, așa cum am făcut în metoda anterioară. Va exista o schimbare a adreselor, dar trebuie să reparăm coordonatele matricei care este vizualizată. Nu ar trebui să se miște. Selectați coordonatele cu cursorul și apăsați tasta funcțională F4. După cum puteți vedea, în fața coordonatelor a apărut un semn dolar, ceea ce înseamnă că referința a trecut de la relativ la absolut. Apoi faceți clic pe butonul „OK”.
Rezultatul este afișat pe ecran în prima celulă a coloanei „Pariu”. Dar înainte de a copia, trebuie să securizăm încă o zonă, și anume primul argument al funcției INDEX. Pentru a face acest lucru, selectați elementul de coloană care conține formula și treceți la bara de formule. Selectați primul argument al operatorului INDEX (B2:B7) și faceți clic pe butonul F4. După cum puteți vedea, un semn dolar apare lângă coordonatele selectate. Faceți clic pe tasta Enter. În general, formula a luat următoarea formă:
INDEX(Foaia2!$B$2:$B$7,PORITARE(Foaia1!A4,Foaia2!$A$2:$A$7,0))
Acum puteți copia folosind mânerul de umplere. Îl numim în același mod despre care am vorbit mai devreme și îl întindem până la sfârșitul intervalului de masă.
După cum putem vedea, în ciuda faptului că ordinea rândurilor celor două tabele legate nu se potrivește, cu toate acestea, toate valorile sunt ajustate în funcție de numele de familie ale angajaților. Acest lucru a fost realizat prin utilizarea unei combinații de operatori INDEX-MATCH.

Citeste si:

Funcția INDEX în Excel Funcția MATCH în Excel

Metoda 3: Efectuați operații matematice pe date legate

Conectarea directă a datelor este, de asemenea, bună, deoarece vă permite nu numai să afișați valorile care sunt afișate în alte intervale de tabel într-unul dintre tabele, ci și să efectuați diverse operații matematice cu acestea (adunare, împărțire, scădere, înmulțire etc. ).

Să vedem cum se face acest lucru în practică. Să facem astfel încât Fișa 3 să afișeze date generale de salariu pentru întreprindere fără a le defalca pe angajat. Pentru a face acest lucru, tarifele angajaților vor fi extrase din Foaia 2, însumate (folosind funcția SUM) și înmulțite cu un coeficient folosind o formulă.

Selectați celula în care vor fi afișate rezultatele calculului de salarizare pe Foaia 3. Faceți clic pe butonul „Inserare funcție”.
Ar trebui să se lanseze fereastra Function Wizard. Accesați grupul „Matematice” și selectați acolo numele „SUMĂ”. Apoi, faceți clic pe butonul „OK”.
Funcția SUM, care este concepută pentru a calcula suma numerelor selectate, este mutată în fereastra de argumente. Are următoarea sintaxă:
SUM(număr1;număr2;…)

Câmpurile din fereastră corespund argumentelor funcției specificate. Deși numărul lor poate ajunge la 255 de bucăți, pentru scopul nostru doar una va fi suficientă. Plasați cursorul în câmpul „Număr1”. Faceți clic pe comanda rapidă „Foaie 2” de deasupra barei de stare.
După ce ne-am mutat la secțiunea dorită a cărții, selectăm coloana care trebuie însumată. Facem acest lucru cu cursorul în timp ce ținem apăsat butonul stâng al mouse-ului. După cum puteți vedea, coordonatele zonei selectate sunt afișate imediat în câmpul ferestrei de argumente. Apoi faceți clic pe butonul „OK”.
După aceasta, suntem mutați automat la Foaia 1. După cum puteți vedea, valoarea totală a tarifelor angajaților este deja afișată în elementul corespunzător.
Dar asta nu este tot. După cum ne amintim, salariul se calculează înmulțind pariul cu coeficientul. Prin urmare, selectăm din nou celula în care se află valoarea însumată. După aceea, accesați bara de formule. Adăugăm un semn de înmulțire (*) la formula din ea, apoi facem clic pe elementul în care se află indicatorul de coeficient. Pentru a efectua calculul, faceți clic pe tasta Enter de pe tastatură. După cum puteți vedea, programul a calculat salariul total pentru întreprindere.
Revenim la Foaia 2 și modificăm tariful oricărui angajat.
După aceasta, trecem din nou la pagina cu suma totală. După cum puteți vedea, din cauza modificărilor din tabelul legat, rezultatul salarial total a fost recalculat automat.

Metoda 4: insert special

De asemenea, puteți lega matrice de tabel în Excel folosind paste special.

Selectăm valorile care vor trebui „trase” într-un alt tabel. În cazul nostru, acesta este intervalul coloanei „Rate” din Foaia 2. Faceți clic dreapta pe fragmentul selectat. În lista care se deschide, selectați „Copiere”. O comandă rapidă alternativă este Ctrl+C. După aceea, trecem la Foaia 1.
După ce ne-am mutat în zona cărții de care avem nevoie, selectăm celulele în care va trebui să ridicăm valorile. În cazul nostru, aceasta este coloana „Rate”. Faceți clic dreapta pe fragmentul selectat. În meniul contextual din blocul instrument „Insert Options”, faceți clic pe pictograma „Insert Link”.
Există și o variantă alternativă. Apropo, este singurul pentru versiunile mai vechi de Excel. În meniul contextual, mutați cursorul la elementul „Lipire specială”. În meniul suplimentar care se deschide, selectați poziția cu același nume.
Aceasta va deschide o fereastră Paste Special. Faceți clic pe butonul „Insert Link” din colțul din stânga jos al celulei.
Indiferent de opțiunea pe care o alegeți, valorile dintr-o matrice de tabel vor fi inserate în cealaltă. Când datele din sursă se modifică, se vor schimba automat și în intervalul inserat.

Lecție: Lipire specială în Excel

Metoda 5: Conectarea tabelelor din mai multe registre de lucru

În plus, puteți organiza relații între zonele tabelului în diferite registre de lucru. Aceasta folosește Instrumentul special Lipire. Acțiunile vor fi absolut similare cu cele pe care le-am considerat în metoda anterioară, cu excepția faptului că la introducerea formulelor va trebui să navigați nu între zonele aceleiași cărți, ci între fișiere. Desigur, toate cărțile conectate trebuie să fie deschise.

Selectați intervalul de date care trebuie transferat într-un alt registru de lucru. Faceți clic dreapta pe el și selectați „Copiere” din meniul care se deschide.
Apoi trecem la cartea în care vor trebui introduse aceste date. Selectați intervalul dorit. Click dreapta. În meniul contextual, în grupul „Insert Options”, selectați „Insert Link”.
După aceasta, valorile vor fi introduse. Când datele din registrul de lucru sursă se modifică, matricea tabelară din registrul de lucru o va extrage automat. Mai mult, nu este deloc necesar ca ambele cărți să fie deschise pentru asta. Este suficient să deschideți un singur registru de lucru și va extrage automat date dintr-un document închis în legătură, dacă s-au făcut anterior modificări.

Dar trebuie remarcat faptul că în acest caz inserarea se va efectua sub forma unui tablou imuabil. Când încercați să schimbați orice celulă cu date inserate, va apărea un mesaj care vă va informa că este imposibil să faceți acest lucru.

Modificările la o astfel de matrice legată de o altă carte pot fi făcute numai prin întreruperea legăturii.

Ruperea relației dintre tabele

Uneori trebuie să rupeți relația dintre intervalele de tabel. Motivul pentru aceasta poate fi fie cazul descris mai sus, când trebuie să modificați o matrice inserată dintr-un alt registru de lucru, fie pur și simplu reticența utilizatorului de a avea datele dintr-un tabel actualizate automat din altul.

Metoda 1: ruperea conexiunii dintre cărți

Puteți întrerupe legătura dintre cărțile din toate celulele efectuând în esență o singură operație. În acest caz, datele din celule vor rămâne, dar vor fi deja valori statice, neactualizate, care nu depind în niciun fel de alte documente.

Într-un registru de lucru în care sunt extrase valorile din alte fișiere, accesați fila „Date”. Faceți clic pe pictograma „Editați conexiunile”, care se află pe panglica din blocul de instrumente „Conexiuni”. Trebuie remarcat faptul că, dacă cartea curentă nu conține legături către alte fișiere, atunci acest buton este inactiv.
Se deschide fereastra pentru schimbarea conexiunilor. Selectăm din lista de cărți legate (dacă sunt mai multe) fișierul cu care dorim să întrerupem legătura. Faceți clic pe butonul „Întrerupe conexiunea”.
Se deschide o fereastră de informații care conține un avertisment despre consecințele acțiunilor ulterioare. Dacă sunteți sigur de ceea ce veți face, atunci faceți clic pe butonul „Rupe legăturile”.
După aceasta, toate referințele la fișierul specificat în documentul curent vor fi înlocuite cu valori statice.

Metoda 2: Inserarea valorilor

Dar metoda de mai sus este potrivită numai dacă trebuie să rupeți complet toate conexiunile dintre două cărți. Ce ar trebui să faceți dacă trebuie să separați tabelele asociate aflate în același fișier? Puteți face acest lucru prin copierea datelor și apoi lipirea lor în același loc cu valorile. Apropo, în același mod, puteți întrerupe conexiunea dintre intervalele de date individuale ale diferitelor cărți fără a întrerupe conexiunea generală dintre fișiere. Să vedem cum funcționează această metodă în practică.

Selectăm intervalul în care dorim să ștergem relația cu un alt tabel. Facem clic dreapta pe el. În meniul care se deschide, selectați „Copiere”. În loc de aceste acțiuni, puteți introduce o combinație alternativă de taste rapide Ctrl+C.
Apoi, fără a deselecta același fragment, faceți din nou clic dreapta pe el. De data aceasta, în lista de acțiuni, faceți clic pe pictograma „Valori”, care se află în grupul de instrumente „Insert Options”.
După aceasta, toate referințele din intervalul selectat vor fi înlocuite cu valori statice.

După cum puteți vedea, Excel are modalități și instrumente de a lega mai multe tabele împreună. În același timp, datele tabelare pot fi localizate pe alte foi și chiar în diferite cărți. Dacă este necesar, această conexiune poate fi întreruptă cu ușurință.

Ne bucurăm că am putut să vă ajutăm să rezolvați problema.

Pune-ți întrebarea în comentarii, descriind esența problemei în detaliu. Specialistii nostri vor incerca sa raspunda cat mai repede posibil.