Tabele înrudite în Excel: Instrucțiuni detaliate

Atunci când efectuați anumite sarcini în Excel, uneori trebuie să se ocupe de mai multe mese care sunt, de asemenea, legate între ele. Adică, datele de la un tabel sunt strânse față de ceilalți, iar valorile din toate tabelele conexe sunt recalculate atunci când sunt schimbate.

Tabelele asociate sunt foarte convenabile pentru a gestiona o cantitate mare de informații. Plasați toate informațiile într-o singură masă, în afară de aceasta, dacă nu este omogenă, nu foarte convenabilă. Este dificil să lucrați cu astfel de obiecte și să le căutați. Problema specificată este concepută doar pentru a elimina tabelele asociate, informațiile dintre care sunt distribuite, dar în același timp sunt interconectate. Tabelele asociate pot fi nu numai într-o singură coală sau o carte, ci și să fie localizate în cărți separate (fișiere). Ultimele două opțiuni în practică sunt utilizate cel mai adesea, deoarece scopul acestei tehnologii este doar pentru a scăpa de acumularea de date, iar capul lor pe o singură pagină nu rezolvă o fundamental. Să învățăm cum să creăm și cum să lucrăm cu un astfel de tip de gestionare a datelor.

Crearea tabelelor asociate

În primul rând, să ne concentrăm asupra întrebării, în care este posibilă crearea unei conexiuni între diverse tabele.

Metoda 1: Formulă de mese de legare directă

Cea mai ușoară modalitate de a lega datele este utilizarea formulelor în care există referințe la alte tabele. Se numește legare directă. Această metodă este intuitivă, deoarece atunci când se leagă aproape doar ca crearea de referințe la date într-o matrice de masă.

Să vedem cum exemplul pe care îl puteți forma prin legare directă. Avem două mese pe două foi. În același tabel, salariul se calculează utilizând formula prin înmulțirea ratelor angajatului pentru un singur coeficient.

Pe a doua foaie există o gamă de masă în care există o listă de angajați cu salariul lor. Lista angajaților din ambele cazuri este prezentată într-o singură comandă.

Este necesar să se facă ca datele de pe pariurile din a doua foaie să se conformeze celulelor corespunzătoare ale primului.

1. Pe prima foaie, alocăm prima celulă a coloanei "Bet". Am pus-o în semnul "=". Apoi, faceți clic pe eticheta "Fișa 2", care este plasată în partea stângă a interfeței Excel pe bara de stare.



2. Există o mișcare în a doua zonă a documentului. Faceți clic pe prima celulă din coloana "Bet". Apoi faceți clic pe butonul ENTER de pe tastatură pentru a introduce datele în celula în care a fost instalat anterior semnul "egal".



3. Apoi, există o tranziție automată la prima foaie. După cum putem vedea, valoarea primului angajat de la al doilea tabel este tras în celula corespunzătoare. Prin instalarea cursorului pe o celulă care conține un pariu, vedem că formula obișnuită este utilizată pentru a afișa date pe ecran. Dar, în fața coordonatelor celulei, de unde se prezintă datele, există o expresie "listă2!", Ceea ce indică numele zonei de documente unde se află. Formula generală din cazul nostru arată astfel:

   = List2! B2



4. 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 îndeplinit sarcina primului angajat, dar având în vedere că ambele liste de angajați sunt situate în aceeași ordine, sarcina poate fi semnificativ simplificată și accelerată prin decizia sa. Acest lucru se poate face prin copierea formulei la intervalul de mai jos. Datorită faptului că referințele la Excel sunt relative, la copierea valorilor lor, valorile schimbătoarele sunt schimbate de noi. Procedura de copiere în sine poate fi făcută utilizând un marker de umplere.

   Deci, am pus cursorul în zona inferioară dreaptă a elementului cu formula. După aceea, cursorul trebuie să se convertească la marcatorul de umplere sub forma unei cruci negre. Realizăm clema butonului stâng al mouse-ului și trageți cursorul la numărul coloanei.



5. Toate datele dintr-o coloană similară de pe o foaie 2 au fost trase într-o masă pe o foaie 1. Când datele se schimbă pe o foaie 2, acestea se vor schimba automat la prima.

Metoda 2: Utilizarea gaurării Indexului Operatori - Căutare

Dar ce să faceți dacă lista angajaților din matricele de masă nu este localizată în aceeași ordine? În acest caz, după cum sa menționat mai devreme, una dintre opțiuni este de a instala relația dintre fiecare dintre acele celule care ar trebui să fie asociate manual. Dar este potrivit, cu excepția meselor mici. Pentru intervale masive, această opțiune va dura mai bine mult timp la implementare, iar în cele mai grave - în practică va fi, în general, nerealistă. Dar această problemă poate fi rezolvată utilizând o grămadă de indice de operatori - căutare. Să vedem cum se poate face prin platarea datelor din tabele despre care conversația a fost în metoda anterioară.

1. Subliniem primul element al coloanei "Bet". Mergeți la Wizard Funcții făcând clic pe pictograma "Inserare Funcție".



2. În vrăjitorul funcțiilor din grupul "Link-uri și matrice" găsim și alocăm numele "Index".



3. Acest operator are două forme: un formular de lucru cu matrice și referință. În cazul nostru, este necesară prima opțiune, deci în fereastra de selecție a formularului care se deschide, selectați-o și faceți clic pe butonul "OK".



4. Indicele argumentelor operatorului începe să funcționeze. Sarcina funcției specificate este ieșirea valorii situate în intervalul selectat în linie cu numărul specificat. Indicele operatorului general al formulei:

   = Index (matrice, nume_name; [număr_stolbit])

   "Array" este un argument care conține intervalul de la care vom extrage informații de numărul de rânduri specificate.

   "Numărul rândului" este un argument care este numărul acestei linii. Este important să știți că numărul liniei trebuie specificat nu în raport cu întregul document, ci numai față de matricea alocată.

   "Numărul coloanei" este un argument opțional. Pentru a rezolva în mod specific sarcina noastră, nu o vom folosi și, prin urmare, nu este necesar să o descriem separat.

   Am pus cursorul în câmpul "Array". După aceea, mergeți la foaia 2 și, ținând butonul stâng al mouse-ului, selectați întregul conținut al coloanei "Rate".



5. După ce coordonatele sunt afișate în fereastra operatorului, am pus cursorul în câmpul "Numărul rândului". Vom retrage acest argument utilizând operatorul de căutare. Prin urmare, faceți clic pe un triunghi care este situat în partea stângă a șirului de funcții. Se deschide o listă de operatori nou utilizați. Dacă găsiți numele "firma de căutare" printre ei, puteți face clic pe el. În cazul opus, faceți clic pe cel mai recent punct al listei - "Alte funcții ...".



6. Fereastra Standard Window Wizard începe. Du-te la el în același grup "Link-uri și matrice". De data aceasta în listă, alegeți elementul "Caută companie". Efectuați un clic pe butonul "OK".



7. Activarea argumentelor argumentelor operatorului de căutare se efectuează. Funcția specificată este concepută pentru a scoate numărul de valoare într-o matrice specifică prin numele său. Este datorită acestei funcții pe care le calculam numărul unui șir de valoare specifică pentru funcția funcției. Sintaxa plăcii de căutare este prezentată:

   = Bord de căutare (căutare_name, vizualizare__nassiv; [Type_station])

   "Dimensiunea dorită" este un argument care conține numele sau adresa celulei din gama terță parte în care se află. Este poziția acestui nume în intervalul țintă și trebuie calculată. În cazul nostru, rolul primului argument va fi menționat la celule pe o foaie 1, în care sunt localizați angajații.

   "Array Listful" este un argument, care este o referință la o matrice, care efectuează căutarea valorii specificate pentru a determina poziția acesteia. Vom avea acest rol pentru a executa adresa coloanei "Nume" pe o foaie 2.

   "Tipul de comparație" - un argument opțional, dar, spre deosebire de operatorul anterior, va fi necesar acest argument opțional. Indică modul de potrivire a operatorului este valoarea dorită cu o matrice. Acest argument poate avea una din cele trei valori: -1; 0; 1. Pentru matrice dezordonate, selectați opțiunea "0". Această opțiune este potrivită pentru cazul nostru.

   Deci, procedați la completarea câmpurilor de fereastră a argumentului. Am pus cursorul în câmpul "Value Fogular", faceți clic pe prima coloană "Nume nume" de pe o foaie 1.



8. După afișarea coordonatelor, setați cursorul în câmpul "Lista masiv" și accesați eticheta "Fișa 2", care este situată în partea de jos a ferestrei Excel deasupra bara de stare. Clement butonul stâng al mouse-ului și evidențiați cursorul toate celulele coloanei "Nume".



9. După ce coordonatele lor sunt afișate în câmpul "Listing Massive", mergeți la câmpul "Mapping Type" și setați numărul "0" de la tastatură. După aceea, revenim din nou la câmp "Privind prin matrice". Faptul este că vom efectua copierea formulei, așa cum am făcut în metoda anterioară. Va exista o schimbare de adrese, dar aici coordonatele matricei au vedere că trebuie să ne asigurăm. El nu ar trebui să se schimbe. Evidențiam coordonatele cu cursorul și faceți clic pe tasta funcțională F4. După cum puteți vedea, semnul dolarului a apărut înaintea coordonatelor, ceea ce înseamnă că referința de la relativ sa transformat în absolut. Apoi faceți clic pe butonul "OK".



10. Rezultatul este afișat în prima celulă a coloanei "Bet". Dar înainte de a copia, trebuie să rezolvăm o altă zonă, și anume primul indice al funcției argumentului. Pentru aceasta, selectați elementul de coloană, care conține o formulă și se deplasează la șirul de formule. Alocați primul argument al indicelui operatorului (B2: B7) și faceți clic pe butonul F4. După cum puteți vedea, semnul dolarului a apărut în apropierea coordonatelor selectate. Faceți clic pe tasta Enter. În general, formula a efectuat următoarea formă:

    = Index (Sheet2! $ B $ 2: $ B $ 7; Board de căutare (foaie1! A4; listul2! $ 2: $ 7; 0))



11. Acum puteți copia utilizând un marker de umplere. Noi o numim în același mod în care am vorbit mai devreme și ne-am întins la sfârșitul intervalului tabular.



12. După cum puteți vedea, în ciuda faptului că ordinea corzilor din două mese legate nu coincide, toate valorile sunt strânse în funcție de numele lucrătorilor. Acest lucru a fost realizat datorită utilizării combinației de căutare a indexului operatorilor.

Metoda 4: Inserție specială

Aranjamentele de masă din Excel pot folosi, de asemenea, o inserție specială.

1. Selectați valorile pe care doriți să le "strângeți" la o altă masă. În cazul nostru, acesta este intervalul de coloană "BET" pe ​​o foaie 2. Faceți clic pe fragmentul dedicat cu butonul din dreapta al mouse-ului. În lista care se deschide, selectați elementul "Copiere". O combinație alternativă este combinația de taste CTRL + C. După aceea, ne mutăm la foaia 1.



2. Mutarea în zona cărții de care aveți nevoie, alocați celulele în care vor trebui să fie strânse valorile. În cazul nostru, aceasta este coloana "BID". Faceți clic pe fragmentul dedicat cu butonul drept al mouse-ului. În meniul contextual din bara de instrumente "Introduceți parametrii", faceți clic pe pictograma "Inserare comunicare".

   

   Există, de asemenea, o alternativă. El, apropo, este singurul pentru versiunile mai vechi ale Excelului. În meniul contextual, aducem cursorul la elementul "Insert special". În meniul suplimentar care se deschide, selectați poziția cu același nume.



3. După aceea, se deschide o fereastră specială de introducere. Faceți clic pe butonul "Introduceți comunicarea" din colțul din stânga jos al celulei.



4. Orice opțiune alegeți, valorile dintr-o matrice de masă vor fi introduse în alta. La schimbarea datelor din sursă, acestea se vor schimba automat în intervalul inserat.

Lecția: Inserție specială în Excel

Metoda 5: Comunicarea între tabele în mai multe cărți

În plus, puteți organiza o legătură între zonele de masă din diferite cărți. Aceasta utilizează un instrument de introducere specială. Acțiunile vor fi absolut similare cu cele pe care le-am luat în considerare în metoda anterioară, cu excepția faptului că navigația pe parcursul formulelor nu va avea între zonele unei cărți, ci între fișiere. Bineînțeles, toate cărțile conexe ar trebui deschise.

1. Selectați intervalul de date care urmează să fie transferat într-o altă carte. Faceți clic pe butonul din dreapta al mouse-ului și selectați poziția "Copiere" în meniul deschis.



2. Apoi ne mutăm în cartea în care trebuie introduse aceste date. Selectați intervalul dorit. Faceți clic pe butonul din dreapta al mouse-ului. În meniul contextual din grupul "Inserare setări", selectați elementul "Inserare comunicare".



3. După aceea, vor fi introduse valorile. La schimbarea datelor din cartea sursă, o matrice de masă din cartea de lucru se va strânge automat. Și nu este deloc necesar să se asigure că ambele cărți sunt deschise. Este suficient să deschideți un singur registru de lucru și va fi automat de datorie de la un document închis în cazul în care au existat schimbări anterioare în ea.

Dar trebuie remarcat faptul că în acest caz, insetarea va fi produsă sub forma unei matrice neschimbate. Când încercați să schimbați orice celulă cu datele introduse, va fi populat un mesaj informând despre incapacitatea de a face acest lucru.

Modificările unei astfel de mastere asociate cu o altă carte poate rupe conexiunea.

Pauză de titlu între tabele

Uneori este necesar să întrerupeți legătura dintre tabele. Motivul pentru aceasta poate fi cele descrise mai sus atunci când doriți să modificați o matrice introdusă dintr-o altă carte și pur și simplu reticența utilizatorului, astfel încât datele din același tabel să fie actualizate automat de la cealaltă.

Metoda 1: Pauză de comunicare între cărți

Pentru a sparge legătura dintre cărți din toate celulele, efectuând de fapt o operațiune. În acest caz, datele din celule vor rămâne, dar vor fi deja valori statice care nu sunt actualizate care nu depind de alte documente.

1. În cartea în care sunt strânse valorile din alte fișiere, accesați fila Date. Faceți clic pe pictograma "Schimbați legăturile", care este situată pe banda din bara de instrumente "Conectare". Trebuie remarcat faptul că dacă cartea curentă nu conține conexiuni cu alte fișiere, atunci acest buton este inactiv.



2. Fereastra de schimbare a link-ului este lansată. Selectați din lista cărților conexe (dacă există mai multe) fișierul cu care dorim să rupem conexiunea. Faceți clic pe butonul "Întrerupeți conexiunea".



3. O fereastră de informații se deschide, care oferă un avertisment cu privire la consecințele acțiunilor viitoare. Dacă sunteți sigur că veți face, faceți clic pe butonul "Comunicare".



4. După aceasta, toate referințele la fișierul specificat în documentul curent vor fi înlocuite cu valori statice.

Metoda 2: Introducerea valorilor

Dar metoda de mai sus este potrivită numai dacă trebuie să întrerupeți complet toate legăturile dintre cele două cărți. Dacă trebuie să deconectați tabelele asociate din același fișier? Puteți face acest lucru prin copierea datelor și apoi introduceți același loc ca valori. Apropo, această metodă poate fi ruptă între intervalele individuale de date ale diferitelor cărți fără a sparge o relație comună între fișiere. Să vedem cum funcționează această metodă în practică.

1. Evidențiam gama în care dorim să ștergem comunicarea cu o altă masă. Faceți clic pe butonul din dreapta al mouse-ului. În meniul Deschidere, selectați elementul "Copiere". În loc de acțiunile specificate, puteți apela o combinație alternativă de chei Hot Ctrl + C.



2. Apoi, fără a scoate selecția de la același fragment, faceți clic din nou pe acesta cu butonul din dreapta al mouse-ului. De data aceasta în lista de acțiune, faceți clic pe pictograma "Valoare", care este afișată în grupul de parametri Insert.



3. După aceasta, toate referințele din gama dedicată vor fi înlocuite cu valori statice.

După cum puteți vedea, Excel are modalități și instrumente pentru a asocia mai multe mese între ele. În același timp, datele tabulare pot fi pe alte foi și chiar în cărți diferite. Dacă este necesar, această conexiune poate fi ușor întreruptă.