Tabelul legat. - Acesta este un tabel creat în Excel și asociat cu o masă din fereastra PowerPivot. Avantajul creării și întreținerii datelor în Excel înainte de a le importa este că este posibil să continuați să modificați datele de pe foaia Excel și să le utilizați în același timp pentru a analiza în PowerPivot.

Numele noului tabel apare în fereastra PowerPivot coincide întotdeauna cu numele tabelului Excel. Prin urmare, înainte de a crea o masă conexă, este necesar să oferiți un tabel Excel cu un nume clar. În mod implicit, Excel generează automat nume de tabele (Tabelul, Tabelul2, etc.), dar le puteți redenumi cu ușurință utilizând interfața Excel.

Crearea unei mese în Excel

La fereastră Excel Selectați datele pe care doriți să le trimiteți sub forma unui tabel.

apasa butonul Formatul ca tabel În fila Filder Root și selectați stilul de masă. Asigurați-vă că selectați Tabel cu titluri.

Dați tabelul numele descris în următoarea procedură.

Redenumirea mesei în Excel

La fereastră Excel pe fila Lucrul cu tabele În grupul de proprietăți de pe tabară Proiecta Introduceți un nume pentru masă.

Crearea unei mese conexe în Excel

Selectați tabelul creat din fereastra Excel.

În fila PowerPivot din grupul Excel, faceți clic pe Creați o masă tricotată.

Noul tabel apare în fereastra PowerPivot. Acest tabel Detaliile sunt asociate cu tabelul Excel selectat și se pot schimba în Excel și nu în fereastra PowerPivot.

Dacă redenumiți tabelul Excel după crearea unui tabel asociat, conexiunea dintre tabele va fi întreruptă. Când încercați să actualizați datele, apare o casetă de dialog. Bugs în tabele conexe. Pentru a selecta o opțiune de corecție a erorilor, faceți clic pe Opțiuni. Numele numelui din fereastra PowerPivot nu afectează conexiunea.

La redenumirea anteturilor de coloane în tabelul Excel, se utilizează automat modificările în tabelul gemeni asociat. Cu toate acestea, dacă redenumiți antetul coloanei în Excel, care este utilizat în Powerpivot, atunci această conexiune PowerPivot va trebui să fie re-creată. Pentru mai multe informații despre link-uri, consultați secțiunea.

În mod implicit, tabelele PowerPivot asociate cu tabelele Excel sunt actualizate automat când se modifică datele. În plus, ele pot fi actualizate manual în fereastra PowerPivot sau în fereastra Excel.

Modul de actualizare a sarcinii în PowerPivot

Lucrul cu tabele Faceți clic pe fila. Tabelul legat. și în grup Mese înrudite apasa butonul Modul de actualizareȘi apoi apăsați butonul manual sau automat.

Actualizarea manuală a tabelelor asociate în PowerPivot

În fereastra PowerPivot din secțiune Lucrul cu tabele Faceți clic pe fila. Tabelul legat. și în grup Mese înrudite Faceți clic pe Actualizare toate sau Actualizare dedicată.

Actualizarea manuală a tabelelor asociate în Excel

La fereastră Excel Faceți clic pe fila PowerPivot și selectați Actualizați toate în grupul de date Excel.

Dacă trebuie să datele automate dintr-un tabel Excel au fost transferate la altulTrebuie să conectați aceste tabele. Există mai multe modalități de asociere a datelor din două mese în Excel. Într-un fel - Cu ajutorul formulei, consultați articolul "Cum să legați tabelele din Excel".
Aici ia în considerare cum să legați două mese în diferite cărți Excel, Utilizând setarea de comunicare între tabele. Când configurați totul, este suficient să deschideți o masă, astfel încât datele să fie mutate în acest tabel dintr-o altă masă. Nu este nevoie să deschideți toate cărțile pentru a comunica. Ea va lucra cu o carte deschisă.
Deci, avem două cărți Excel - Book1 și Book2. În cartea1 există o astfel de masă, deja umplută. Tabelul 1. Atenţie!
Dacă există celule combinate în tabel, atunci în ele conexiunea nu va funcționa sau va fi bătut înapoi.
În cartea2, facem aceeași masă, dar nu o umplem.

Acum trebuie să o facem astfel încât datele din tabelul1 să se mute automat în tabelul2. În cartea1, apăsați mouse-ul drept peste celula A1. În meniul contextual care apare, faceți clic pe funcția "Copiere".
Du-te la Table2. Subliniem celula A2. Faceți clic pe fila "Acasă", butonul "Inserare" și faceți clic pe butonul "Inserare comunicare".

Puteți apela în continuare funcția "Inserare comunicare". De asemenea, copiați, dar în meniul contextual, faceți clic pe funcția "Inserare specială". În caseta de dialog care apare, faceți clic pe butonul "Introduceți comunicarea". Atenţie!
Dacă faceți clic pe tasta Enter, este posibil ca conexiunea să nu fie instalată. Prin urmare, la stabilirea comunicării, faceți clic pe o celulă goală. Un cadru pulsatoriu la celula A1 din tabelul1 pentru a elimina cheia "Esc".
Când stabiliți o conexiune în celulă, calea va fi scrisă în șirul de formulare.
În tabelul celular A22, a apărut numele coloanei, ca în Tabelul1.
Acum, pentru a copia conexiunea pe tot parcursul coloanei, în calea de adresă din celula A1 eliminați semnul $ - înlocuiți linkul absolut relativ. Pentru detalii despre link-uri, consultați articolul "Link-uri relative și absolute cu Excel".
Copiați formula cu o legătură din celula A1 în jos în coloană. De asemenea, configurează și, apoi, copiem, comunicații în alte celule. Sa dovedit un astfel de tabel2.

Schimbați numerele din tabelul1.

Tabelul 2 sa dovedit a fi așa.

Salvați ambele cărți, aproape. Acum, dacă deschidem o carte1, veți schimba numerele din el, vom lucra și vom închide. Dar când deschidem o carte2, atunci toate aceleași date din tabelul2 Când deschideți o carte2 se va schimba la cele pe care le-am schimbat în Tabelul1.
Pentru alte caracteristici ale unei inserții speciale, consultați articolul "Inserare specială Excel".
Cum de a lega tabelele pe diferite computere, consultați articolul "Partajarea fișierului Excel".
În Excel, puteți configura transferul automat de date de la tabelul Excel în cuvânt și invers. Uită-te în articolul "

Dacă datele din altă foaie sunt utilizate pe o singură foaie de lucru, aceste foi sunt considerate conectate. Folosind legarea, puteți muta celulele celulelor din mai multe mese diferite pe o foaie de lucru.

Schimbarea conținutului celulei pe o singură coală sau o masă ( sursă) Cartea de lucru conduce la o schimbare a celulelor sale asociate în foi sau mese ( receptoare). Acest principiu distinge legarea foilor de la copierea simplă a conținutului celulelor de la o foaie la alta.

În funcție de tehnica de execuție, legarea se întâmplă " direct"Și prin echipă" Inserție specială ".

Legare directă Foile sunt utilizate direct la introducerea formulei în celulă, când se utilizează o referință la celula unei alte foi, ca unul dintre elementele de formulare. De exemplu, dacă există o formulă care utilizează o referire la A4 a unei alte foi de lucru (de exemplu, foaia 1) și ambele foi sunt încărcate, atunci o astfel de legare a foilor se numește "direct".

Termenul de legare "dreaptă" indică faptul că utilizatorul în sine direct la introducerea formulei indică numele foii și adresa absolută a celulei separate printr-o marcă de exclamare "!".

Exemple de formule: \u003d list C5 *! A4.

Leaf3! B2 * 100%

Leaf1! A1-list2! A1.

Notă.Pentru a specifica legăturile către celule și foi situate în registrele de lucru descărcate (nedeschise), în formula trebuie să setați calea completă a locației fișierului fără spații. Calea este setată în ghilimele unice, unde numele discului, directorului (dosarelor), numele cărții de lucru (numele fișierului) în paranteze pătrate și numele foii la care este blocat link-ul.

De exemplu:

\u003d "D: \\ Documente Student [Calculul venitului .xls] Achiziționarea de produse"! H19

Legarea prin comandă " Inserare specială " se efectuează dacă orice celulă de masă de pe o foaie de lucru trebuie să conțină o valoare celulară dintr-o altă foaie de lucru.

Pentru a reflecta în celula C4 pe o foaie Prețul produsuluivaloarea celulei N4 pe foaia sursă Achiziționarea de produsetrebuie să plasați cursorul pe celula N4 a foii sursă și să executați comanda. Editați / copiați.Pe foaie Prețul produsuluipuneți cursorul pe celula C4, care trebuie să fie asociat cu originalul și să execute comanda Editați / Inserare specială Inserare / Inserare Comunicare(Vezi figura 6). Apoi pe foaie Prețul implementăriiva apărea o indicație a celulei din foaia sursă. Achiziționarea de produsede exemplu: \u003d Achiziționarea de produse! $ H $ 4

Cu această legare, Excel utilizează automat adresa absolută a celulei, deoarece Adresa de apel relativă poate duce la erori, mai ales dacă accesați fișiere descărcate (cărți de lucru).

Sarcina 7.Celulele C4, C5, C6, C7, C8 în tabelul de cumpărare a cheltuielilor pe o foaie Prețul implementăriicu celule adecvate pe o foaie Achiziționarea de produseutilizând diverse metode de legare a celulelor (figura 28).

Smochin. 28. Legarea celulelor de diferite foi de lucru

Din păcate, dacă cartea sursă a fost eliminată / mutată sau redenumită, conexiunea va fi ofensată. De asemenea, conexiunea se va pierde dacă mutați fișierul de destinație (care conține link-ul). Dacă treceți numai fișierul final prin poștă, destinatarul nu va putea, de asemenea, să actualizeze comunicarea.

Cum să spargeți link-ul

O modalitate de a rezolva această problemă este o ruptură de comunicare. Dacă o singură conexiune este în fișier, atunci acest lucru este destul de simplu:

1. Faceți clic pe fila. Date.
2. Alege echipa Schimbați conexiunile În capitolul. Conexiuni.
3. Clic Sparge comunicarea.

IMPORTANT! La ruperea comunicării, toate formulele care se referă la cartea sursă vor fi convertite în valori! Anularea acestei operațiuni este imposibilă!

Cum de a rupe conexiunea cu toate cărțile

Pentru comoditate, puteți utiliza un macro care sparge comunicarea cu toate cărțile. Macro face parte din add-in-ul Vba-Excel. Să le folosească nevoia:

Cod pe vba.

Codul macro de eliminare a tuturor conexiunilor cu cartea este prezentat mai jos. Puteți copia acest lucru la proiectul dvs.

Subslockworkbooks () Dim WBLINKS Dim I AS SELECT LONG CASE MSGBox ("Toate link-urile către alte cărți vor fi șterse din acest fișier, iar formulele care se leagă de alte cărți vor fi înlocuite cu valorile." & VbclLF & "Sunteți sigur Doriți să continuați? ", 36," RIP Connection? ") Cazul 7" Fără ieșire Sub-end Selectați WBLINKS \u003d ActiveWorkBook.Linksources (tip: \u003d xllinktypeExceliks) Dacă nu ISEmpty (WBLINKS) Apoi pentru i \u003d 1 la ubound (wblinks) ActiveWorkBook.BreakLink Numele: \u003d WBLINKS (I), Tipul: \u003d XLLINKTYPEEXCelleks Următorul altceva Msgbox "Nu există linkuri către alte cărți din acest fișier.", 64, "Comunicarea cu alte cărți" Sfârșitul dacă se încheie

Cum de a sparge link-ul numai în intervalul dedicat

Uneori există multe conexiuni în carte și există temeri că atunci când ștergeți comunicarea, puteți elimina extra. Pentru a evita acest lucru utilizând add-on, puteți șterge conexiunile numai în intervalul dedicat. Pentru aceasta.

Foi de lucru

Până acum, a lucrat doar cu o singură coală a cărții de lucru. Este adesea utilă utilizarea mai multor foi de lucru.

În partea de jos a ecranului, etichetele din tablă sunt vizibile. Dacă faceți clic pe etichetă cu butonul stâng al mouse-ului, foaia specificată devine activă și sa mutat în partea de sus. Faceți clic dreapta pe etichetă va cauza un meniu pentru astfel de acțiuni cu o foaie, ca în mișcare, ștergere, redenumire etc.

În colțul din stânga sus al ferestrei cărții de lucru, butoanele de defilare cu care vă puteți deplasa de la o foaie de lucru la alta.

Faceți clic dreapta pe butoanele de defilare, puteți deschide un meniu dependent de spațiu pentru a selecta desktopul dorit.

Localizarea cărților lucrătorilor

Să presupunem că doriți să vedeți toate cărțile deschise de pe ecran, Excel vă va îndeplini cu ușurință dorința dvs. și puteți naviga cu ușurință în cărțile dvs. Utilizarea comenzii Excel Fereastră Puteți aranja registre deschise pe ecran patru moduri.

* Cărțile de lucru din apropiere deschise în ferestre mici, pentru care întregul ecran este împărțit cu un mod "gresie";

* De la partea de sus în jos - cartea de lucru deschisă sunt afișate în Windows având o vedere a benzilor orizontale,

* De la stânga la dreapta - Registrele de lucru deschise sunt afișate în Windows având un tip de bandă verticală;

* Cascade - Registrele de lucru (fiecare în fereastra sa) "LID" pe straturile de ecran.

Tranziții între locuri de muncă

Indiferent dacă ați decis să localizați toate registrele de lucru deschise pe ecran sau pur și simplu "puneți" unul pe celălalt în ordinea de deschidere, puteți să vă deplasați cu ușurință de la o carte la alta. Excel oferă mai multe moduri rapide de a merge la cartea dorită. Pentru a face acest lucru, puteți utiliza mouse-ul, tastele de urgență sau fereastra meniului Excel. Acestea sunt aceste moduri:

* Faceți clic pe partea vizibilă a ferestrei cărții de lucru;

* Apăsați tastele. Pentru a merge de la o fereastră de carte pe altă fereastră.

* Deschideți fereastra meniului Excel. Partea inferioară conține o listă de registre deschise. Pentru a merge la cartea dorită, faceți clic pe nume.

Copierea datelor dintr-o carte de lucru la alta

Folosind comanda Excel, puteți copia datele dintr-o carte de lucru la alta. De exemplu, ați deschis două cărți de lucru, dintre care una conțin un buget trimestrial, iar cealaltă este anuală. Pentru a economisi timp, ar fi plăcut să copiați datele din primul trimestru de la prima carte de lucru la al doilea. În acest caz, datele inițiale din primul registru de lucru nu schimbă copia acestor date.

Pentru a copia datele dintr-o carte de lucru la altul, deschideți ambele cărți de lucru. Selectați datele din prima carte și faceți clic pe panourile standard pentru bara de instrumente Copiere pentru a comuta la o altă carte, utilizați oricare dintre metodele de mai sus. De exemplu, conform uneia dintre ele, selectați numele celei de-a doua carte de lucru din meniu. Accesați foaia de lucru dorită și selectați celula în care intenționați să introduceți date. Faceți clic pe butonul Standard Introducere Bara de instrumente. Excel copiază instantaneu datele în a doua carte de lucru.

Transferul de date între locurile de muncă

Această procedură este similară cu datele de copiere. Diferența este utilizarea unui alt buton. Evidențiați datele din primul registru de lucru și faceți clic pe butonul Standard Bara de instrumente CUT pentru a extrage datele. Mergând la un alt registru de lucru, evidențiați celula dorită și faceți clic pe butonul Standard Bara de instrumente Paste. Ca rezultat, Excel va șterge datele din prima carte de lucru și le va insera în al doilea.

Există o modalitate rapidă de a transfera datele din foaia de lucru între lucrări. Se compune în utilizarea metodei "Drag și Omite". Primele cărți deschise implicate în operarea transferului de date. Selectați din comanda / localizați comanda Excel. În fereastră, selectați caseta de dialog a locației ferestrei, selectați opțiunea Următorul și faceți clic pe butonul OK. Trebuie să vedeți cel puțin o mică parte a ferestrei fiecărui registru de lucru. Selectați eticheta de coli (foile) pe care doriți să o copiați. Așezați indicatorul mouse-ului peste eticheta selectată, faceți clic și, fără a elibera butonul mouse-ului, trageți eticheta pe fereastra unei alte cărți de lucru. Când eliberați butonul mouse-ului, foaia va fi "înregistrată" în noul registru (pentru el).

Crearea de conexiuni între fișele de lucru și registrele de lucru.

Excel vă permite să utilizați date din alte coloane din tabel din alte tabele.

Legarea este un proces de actualizare a datelor dinamice în foaia de lucru bazată pe alte date sursă (foaie de lucru sau carte de lucru). Datele conexe reflectă modificările aduse datelor sursă.

Legarea se efectuează prin formule speciale care conțin așa-numitele link-uri externe. Legătura externă se poate referi la o celulă dintr-o altă foaie de lucru a aceleiași cărți de lucru sau a celulei oricărei alte fișă de lucru a oricărei alte cărții de lucru. De exemplu, legăturile dintre cele două foi se realizează datorită introducerii unei formule la o foaie cu o legătură cu o celulă într-o altă foaie.

Excel vă permite să creați conexiuni cu alte foi de lucru și alte registre de trei tipuri:

Când lucrați cu un număr mare de date și creând numeroase foi alimentare pentru a stoca aceste date, există situații în care formula de pe o singură foaie de lucru utilizează date dintr-o altă foaie de lucru. Astfel de formule sunt foarte utile deoarece vă salvează de nevoia de a stoca date redundante pe multe foi de lucru.

Pentru a se referi la celulă într-o altă foaie de lucru, puneți o marcă de exclamare între numele foii și numele celulei. Sintaxa pentru acest tip de formule este după cum urmează: \u003d foaie! Cell. Dacă foaia dvs. are un nume, apoi în loc de foaie de desemnare, utilizați numele acestei foi. De exemplu, raportul! B5.

Dacă numele conține spații (de exemplu, Bugetul 99.), atunci când creați legături către alte foi, acest nume trebuie să fie închis în citate unice.

Legarea mai multor foi de lucru

Există adesea situații în care formula ar trebui să se refere la gama de celule, incluzând două sau chiar mai multe foi în registrul de lucru. Acest lucru se întâmplă de obicei atunci când sunt create fișele de lucru identice pentru distribuția bugetară pentru diferite perioade de timp, pentru informații despre activitatea diferitelor echipe sau pentru informații despre vânzări în diferite regiuni. De asemenea, puteți avea diferite foi de lucru, dar valorile lor totale sunt conținute în mod specific în celule cu adrese identice. Și toate aceste valori finale pot fi apoi minimizate pentru a obține un rezultat total într-o singură formulă care conține o referință la toate aceste foi și adrese de celule.

În astfel de cazuri, Excel se referă la intervale de celule folosind referințe tridimensionale. Referința tridimensională este stabilită prin pornirea domeniului de coli (indicând foaia inițială și finală) și intervalul corespunzător de celule. De exemplu, o formulă care utilizează o referință tridimensională, care include foi de foi1 la foaie5 și celula A4: A8, poate avea următoarea formă: \u003d suma (foaia1: list5! A4: A8).

Referințele tridimensionale pot fi incluse în formula și într-un alt mod. Pentru a face acest lucru, faceți clic pe foaia de lucru care urmează să fie inclusă în formula. Dar mai întâi, începeți formula din celula în care doriți să obțineți rezultatul. Când vine vorba de o întoarcere înainte de a utiliza o legătură tridimensională, faceți clic pe prima comandă rapidă pentru a fi pornită, apoi apăsați (și nu lăsați Go) și faceți clic pe eticheta ultimei foi care trebuie inclusă în link. După aceea, alocați celulele necesare. După finalizarea clădirii formulei, apăsați tasta. .

Legarea cărților lucrătorilor

Atunci când sunt utilizate locuri de muncă, un număr de termeni sunt utilizați pe care ar trebui să-l cunoașteți. Cartea de lucru care conține formula de legare se numește cartea de lucru dependentă și cartea de lucru care conține datele asociate cu cartea inițială de lucru.

Conexiunea dintre cele două fișiere este realizată prin introducerea unei formule de comunicare la un singur fișier cu o legătură într-o celulă într-un alt fișier, fișierul care primește date din celălalt este numit un fișier de destinație, iar fișierul care oferă date este un fișier sursă .

Odată ce conexiunea este stabilită. Excel copiază valoarea din celula din fișierul sursă din celula de destinație. Valoarea din celula de destinație este actualizată automat.

Când se referă la celula conținută într-un alt registru de lucru, se utilizează următoarea sintaxă: [carte] foaie! Celulă. Introducerea formulei de legare pentru referire la legătura dintr-o altă carte de lucru, utilizați numele acestei cărți, închise în paranteze pătrate, urmată de numele foii de lucru fără spații, apoi semnul de exclamare (!), Și după ce este Adresa de celule (celule). De exemplu, "C: Petrov [Magazine1.hls] Literatură"! L3.

Actualizați conexiunile

Lucrul cu mai multe locuri de muncă și formulele de legare, trebuie să știți cum sunt actualizate aceste conexiuni. Formele vor fi actualizate automat, dacă modificați datele din celule la care există linkuri numai dacă sunt deschise ambele cărți de lucru.

Dacă datele din registrul de lucru original se schimbă în momentul în care cartea dependentă (care conține formula de legare) este închisă, atunci datele asociate nu sunt actualizate imediat. Deschiderea casei de lucru dependente Data viitoare, Excel vă va cere să confirmați actualizarea datelor. Pentru a actualiza toate datele aferente din registrul de lucru, selectați răspunsul da. Dacă aveți conexiuni care sunt actualizate manual sau doriți să actualizați singuri conexiunile, selectați răspunsul.

Pentru a șterge o foaie, selectați comanda Ștergere din meniul dependent de context pentru etichete. Pentru a elimina simultan mai multe foi de lucru, pre-selectați-le când este apăsată tasta CTRL.

Verifica:

· Știi ce: Registrul de lucru Excel; frunze de lucru; Reguli pentru înregistrarea formulelor pentru comunicarea foilor de lucru;

· Știți cum: Introduceți foaia de lucru; șterge; redenumiți; mișcare; copie; deschideți ferestrele; închide; Ordin; Comunicați între foile de câte una și diferite cărți de lucru.

Lucrări de laborator pe Microsoft Excel.