Cum se stabilesc relații între tabele în Excel. Caietul de lucru Excel. Comunicarea între foile de lucru. Partajarea datelor. Lucrări de laborator pe Microsoft Excel

Informații într-o foaie de calcul Microsoft Excel poate fi combinat cu datele extrase din desen.

Prin crearea unui link către o foaie de calcul Excel, puteți include informații din întreaga foaie de lucru sau doar o parte a acesteia, de ex. interval numit. În caseta de dialog External Data Link, puteți face următoarele:

• Setați o legătură de date selectând fișierul XLS la care doriți să legați datele extrase din desen.
• Setați maparea dintre datele desenului extrase și datele din foaia de calcul.
• Selectați coloanele din fișierul XLS pe ​​care doriți să le îmbinați cu datele de desen extrase.

Pagina de corecție a datelor oferă acces la căsuță de dialog„Legătură către date externe”, unde puteți lega datele extrase la întreaga foaie de calcul Excel sau la o serie de rânduri și coloane.

Pentru a crea un link către o foaie de calcul, trebuie să aveți aplicație Microsoft Excela. Dacă nu este instalat programul Microsoft Excel sau fișier XLS nu este salvat local pe computerul pe care sunt preluate datele, datele nu pot fi legate.

După ce modificați o foaie de calcul conectată, cum ar fi adăugarea unui rând sau a unei coloane, puteți actualiza tabelul din desen pentru a reflecta acea modificare folosind comanda LINK UPDATE. De asemenea, dacă modificați un tabel dintr-un desen, foaia de calcul asociată poate fi actualizată folosind aceeași comandă.

Notă: Pentru a elimina o legătură de date, utilizați comanda LINK. Nu puteți elimina legăturile de date din panoul Legături externe.

Despre legarea și potrivirea datelor

Conectarea datelor implică selectarea unei foi de calcul Microsoft Excel pentru a combina întregul tabel sau o parte din tabel cu datele desenului extrase. Pentru mai multe informații despre legarea de date, consultați Crearea unui link de tabel către date externe.

Procesul de mapare a datelor creează o relație între două seturi de date: datele din coloana tabelului de date extrase ale desenului și datele din coloana foii de calcul Excel.

Următorul exemplu examinează o specificație simplă. Trei coloane de date sunt preluate din desen. Aceste coloane conțin următoarele date: Partea nr., EditorialȘi Cantitate. Puteți include două coloane suplimentare de date dintr-o foaie de calcul în specificația dvs. Aceste coloane conțin următoarele date PrețȘi Furnizor.

ÎN în acest exemplu foaia de calcul conține o coloană de numere de piesă care are un antet Număr de catalogși corespunde coloanei Număr de detaliuîn tabelul de date extrase. În caseta de dialog External Data Link, selectați „Număr detaliu” ca coloană pentru desenarea datelor și „Număr de catalog” ca o coloană pentru date externe. Deoarece există cel puțin o celulă de date corespunzătoare atât în ​​coloana de date desen, cât și în coloana de date externe, funcția de extragere a datelor știe cum sunt legate cele două surse.

Puteți verifica consistența datelor făcând clic pe butonul Verificare conformitate din caseta de dialog External Data Link. Dacă potrivirea este incorectă, este afișat un mesaj de avertizare indicând motivul. În unele cazuri, este posibil să nu puteți crea corect o legătură între datele desenului și datele foii de calcul.

Validarea datelor potrivite

Un eșec de potrivire a datelor înseamnă că nu poate fi găsită o potrivire validă între datele desenului și datele externe (în foaia de calcul). Sunt afișate următoarele mesaje de eroare.

Nu s-au găsit potriviri între valorile din datele desenului și datele externe.

• Dacă problema nu poate fi rezolvată, fuziunea datelor externe nu va avea loc. Comparați datele coloanei foii de calcul cu datele desenului și încercați să găsiți valorile corespunzătoare. De exemplu, datele de desen și datele externe pot avea valori de precizie diferite.

Coloana de date din datele externe nu conține valori unice.

• O coloană de date externă conține una sau mai multe celule cu aceleași date. În această situație, este imposibil să se definească fără ambiguitate corespondența dintre datele desenului și datele externe.

În acest articol veți învăța cum să combinați rapid datele din două tabele Excel când coloane cheie fara potriviri exacte. 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ă dintr-o dată nou format identificatori unici sau codurile de stoc din depozit (SKU) se vor schimba doar puțin. Ș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 obișnuită formule Excel 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 din stoc (În stoc). În loc de bere, poate exista orice produs și numărul de coloane în viata reala ar putea fi mult mai multe.

Î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 datele 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)

Într-un cuvânt, puteți folosi așa ceva Funcții Excel, Cum 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 este o denumire de cod a unui grup de bunuri ( Celulare, 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. Asemenea discrepanțe apar aleatoriu și nu există regula generala 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 unul nou Foaie Excelși sună-l Convertor SKU. Copierea întregii coloane SKU-ul nostru dintr-o foaie Magazin la o foaie nouă, eliminați duplicatele și lăsați numai valori unice în ea.

Adăugați o coloană lângă ea Sup.SKUși căutați manual potriviri între valorile coloanei SKU-ul nostruȘi Sup.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) Sup.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ă Sup.SKU.

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

Cometariu: Dacă în coloană Sup.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 Sup.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.

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 conţine 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ă anteturile coloanelor există butoane de sortare și filtrare care vă permit să sortați datele și să aplicați filtre.

Într-un tabel legat, 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".

Facand anumite sarciniîn Excel, uneori ai de-a face cu mai multe tabele, care sunt și ele interconectate. Adică, datele dintr-un tabel sunt atrase în altele și, atunci când se schimbă, 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 singură 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.

Î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ă a angajaților 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.

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 grămadă 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ă.

1. Selectați primul element al coloanei "Ofertă". Să mergem la Expertul de funcții făcând clic pe pictogramă „Inserare funcție”.



2. ÎN Expertul de funcții in grup „Legături și matrice” găsiți și evidențiați numele "INDEX".



3. 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 "BINE".



4. Fereastra de argumente operator este lansată INDEX. Sarcina funcției specificate este de a afișa valoarea situată în intervalul selectat în linia cu numărul specificat. Formula generală a operatorului INDEX este aceasta:

   INDEX(matrice, număr_rând, [număr_coloană])

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

   "Numărul de linie"- 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”- un argument optional. 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âmp „Matrice”. După aceasta mergem la Foaia 2și, ținând butonul din stanga mouse-ul, selectați întregul conținut al coloanei "Ofertă".



5. După ce coordonatele sunt afișate în fereastra operatorului, plasați cursorul în câmp "Numărul de linie". Vom scoate acest argument folosind operatorul CĂUTARE. 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ăsești un nume printre ei "CĂUTARE", apoi puteți da clic pe el. În caz contrar, faceți clic pe ultimul element din listă - „Alte funcții...”.



6. Se deschide o fereastră standard Vrăjitorii de funcții. Să mergem la același grup „Legături și matrice”. De data aceasta selectăm elementul din listă "CĂUTARE". Faceți clic pe butonul "BINE".



7. Fereastra de argumente operator este activată CĂUTARE. 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 linie al unei anumite valori pentru funcție INDEX. Sintaxă CĂUTARE prezentat astfel:

   MATCH(valoare_căutare, matrice_căutare, [tip_potrivire])

   „Valoare de căutare”— 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 referințele la celule Foaia 1, în care se află numele angajaților.

   „Matrice de navigare”- un argument care este o referință la o matrice în care se caută valoarea specificată pentru a-i determina poziția. Pentru noi, acest rol va fi jucat de adresa rubricii „ Nume" pe Foaia 2.

   „Tip de potrivire” este un argument opțional, dar spre deosebire de afirmația anterioară, aceasta argument opțional vom avea nevoie. Specifică modul în care operatorul va potrivi valoarea de căutare cu matricea. Acest argument poate avea unul dintre cele trei semnificații: -1 ; 0 ; 1 . Pentru matrice neordonate ar trebui să 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âmp „Valoare de căutare”, faceți clic pe prima celulă a coloanei "Nume" pe Foaia 1.



8. După ce sunt afișate coordonatele, plasați cursorul în câmp „Matrice de navigare”și accesați comanda rapidă "Foaia 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 toate celulele coloanei cu cursorul "Nume".



9. După ce coordonatele lor sunt afișate în câmp „Matrice de navigare”, du-te pe teren „Tip de potrivire”și folosiți tastatura pentru a seta numărul acolo «0» . După aceasta ne întoarcem din nou pe teren „Matrice de navigare”. 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 faceți clic pe cheie functionala 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 "BINE".



10. Rezultatul este afișat în prima celulă a coloanei "Ofertă". 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. Selectarea primului 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 cheie introduce. În general, formula a luat următoarea formă:

    INDEX(Foaia2!$B$2:$B$7,PORITARE(Foaia1!A4,Foaia2!$A$2:$A$7,0))



11. 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ă.



12. 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—CĂUTARE.

Dacă avem nevoie datele dintr-un tabel Excel au fost transferate automat în altul, trebuie să legați aceste tabele. Există mai multe moduri de a lega datele din două tabele în Excel. Sens unic - folosind o formulă, consultați articolul „Cum să legați tabelele în Excel”.
Aici vom lua în considerare, cum să legați două tabele în diferite cărți excela, prin stabilirea unei relații între tabele. Odată ce totul este configurat, este suficient să deschideți un tabel, astfel încât datele să fie transferate în acest tabel dintr-un alt tabel. Nu trebuie să deschideți toate cărțile pentru ca conexiunea să funcționeze. Va funcționa cu un singur registru de lucru deschis.
Deci, avem două registre de lucru Excel - Book1 și Book2. În Cartea 1 există un astfel de tabel, deja completat. Tabelul 1.Atenţie!
Dacă tabelul are celule îmbinate, atunci conexiunea dintre ele nu va funcționa sau se va pierde.
În Cartea 2 facem același tabel, dar nu îl completăm.

De asemenea, puteți apela funcția „Insert Link” astfel. Copiem și noi, dar în meniul contextual faceți clic pe funcția „ Inserție specială" În caseta de dialog care apare, faceți clic pe butonul „Insert Link”.