Tabelle correlate in Excel: istruzioni dettagliate

Quando si eseguono determinati compiti in Excel a volte devono affrontare diverse tabelle che sono anche legate all'altra. Cioè, i dati di una tabella sono serrati ad altri e i valori in tutte le tabelle correlate vengono ricalcolati quando vengono modificati.

Le tabelle correlate sono molto comode da usare per gestire una grande quantità di informazioni. Metti tutte le informazioni in un solo tavolo, inoltre, se non è omogenea, non molto conveniente. È difficile lavorare con tali oggetti e per cercarli. Il problema specificato è solo progettato per eliminare le tabelle correlate, le informazioni tra cui sono distribuite, ma allo stesso tempo sono interconnesse. Le tabelle correlate potrebbero non solo all'interno di un foglio o un libro, ma anche per essere posizionato in libri separati (file). Le ultime due opzioni in pratica sono utilizzate più spesso, dal momento che lo scopo di questa tecnologia è solo per allontanarsi dall'accumulo di dati, e il loro annuente su una pagina non risolve fondamentalmente. Scopriamo come creare e come lavorare con un tipo di gestione dei dati.

Creando tabelle correlate

Prima di tutto, concentriamo sulla domanda, in che modo è possibile creare una connessione tra varie tabelle.

Metodo 1: Formula Direct Binding Tables

Il modo più semplice per legare i dati è l'uso di formule in cui ci sono riferimenti ad altre tabelle. Si chiama rilegatura diretta. Questo metodo è intuitivo, dal momento che quando gliga è quasi proprio come creare riferimenti ai dati in un unico array di tabella.

Vediamo come l'esempio è possibile formare la comunicazione tramite binding diretto. Abbiamo due tavoli su due fogli. Sullo stesso tavolo, lo stipendio viene calcolato utilizzando la formula moltiplicando i tassi dei dipendenti per un singolo coefficiente.

Sul secondo foglio c'è una gamma di tavoli in cui c'è un elenco di dipendenti con il loro stipendio. L'elenco dei dipendenti in entrambi i casi è presentato in un unico ordine.

È necessario rendere i dati sulle scommesse dal secondo foglio serrare nelle celle corrispondenti del primo.

1. Sul primo foglio, assegniamo la prima cella della colonna "scommessa". Abbiamo messo dentro il segno "=". Quindi, fare clic sull'etichetta "foglio 2", che viene posizionato sulla parte sinistra dell'interfaccia Excel sulla barra di stato.



2. C'è un movimento nella seconda area del documento. Clicca sulla prima cella nella colonna "Scommessa". Quindi fare clic sul pulsante ENTER sulla tastiera per inserire i dati nella cella in cui il segno "uguale" è stato precedentemente installato.



3. Poi c'è una transizione automatica al primo foglio. Come possiamo vedere, il valore del primo impiegato della seconda tabella viene tirato nella cella corrispondente. Installando il cursore su una cella contenente una scommessa, vediamo che la formula abituale viene utilizzata per visualizzare i dati sullo schermo. Ma davanti alle coordinate della cella, da dove vengono emessi i dati, c'è un'espressione "List2!", Che indica il nome dell'area del documento in cui si trovano. La formula generale nel nostro caso sembra questo:

   = Lista2! B2



4. Ora è necessario trasferire i dati sulle aliquote di tutti gli altri dipendenti dell'impresa. Naturalmente, questo può essere fatto nello stesso modo in cui abbiamo soddisfatto il compito per il primo dipendente, ma considerando che entrambe le liste dei dipendenti si trovano nello stesso ordine, il compito può essere significativamente semplificato e accelerato dalla sua decisione. Questo può essere fatto semplicemente copiando la formula alla gamma di seguito. A causa del fatto che i riferimenti ad Excel sono relativi, quando si copiano i loro valori, lo spostamento dei valori viene spostato di cui abbiamo bisogno. La procedura di copia stessa può essere effettuata utilizzando un marker di riempimento.

   Quindi, mettiamo il cursore nell'area in basso a destra dell'elemento con la formula. Successivamente, il cursore deve convertire nel marker di riempimento sotto forma di croce nera. Eseculendo il morsetto del tasto sinistro del mouse e tiremo il cursore sul numero della colonna.



5. Tutti i dati da una colonna simile su un foglio 2 sono stati tirati in una tabella su un foglio 1. Quando i dati cambiano su un foglio 2, cambieranno automaticamente il primo.

Metodo 2: Utilizzo dell'indice degli operatori degli operatori - Ricerca

Ma cosa fare se l'elenco dei dipendenti negli array di tabelle non si trova nello stesso ordine? In questo caso, come indicato in precedenza, una delle opzioni è installare la relazione tra ciascuna di quelle celle che dovrebbero essere associate manualmente. Ma è adatto ad eccezione dei tavolini. Per grandi intervalli, questa opzione richiederà al meglio molto tempo sull'attuazione, e nel peggiore dei peggiori - in pratica - in pratica sarà generalmente irrealistico. Ma questo problema può essere risolto utilizzando un grappolo di operatori indice - ricerca. Vediamo come può essere fatto colpendo i dati nelle tabelle su cui la conversazione era nel metodo precedente.

1. Evidenzia il primo elemento della colonna "Scommessa". Vai alla procedura guidata Funzioni facendo clic sull'icona "Inserisci funzione".



2. Nel mago delle funzioni nel gruppo "Collegamenti e array" che troviamo e allociamo il nome "indice".



3. Questo operatore ha due forme: una forma per lavorare con array e riferimento. Nel nostro caso, è richiesta la prima opzione, quindi nella finestra di selezione del modulo successivo a cui si apre, selezionarla e fai clic sul pulsante "OK".



4. L'indice degli argomenti dell'operatore inizia a funzionare. Il compito della funzione specificato è l'uscita del valore situato nell'intervallo selezionato nella riga con il numero specificato. Indice generale dell'operatore di formula tale:

   = Indice (array; Number_name; [Number_stolbits])

   Il "array" è un argomento contenente la gamma dell'intervallo da cui estrarremo le informazioni entro il numero della riga specificata.

   "Numero di riga" è un argomento che è il numero di questa linea. È importante sapere che il numero di linea dovrebbe essere specificato non relativo all'intero documento, ma solo relativo all'array assegnata.

   Il "numero della colonna" è un argomento opzionale. Per risolvere lo specifico del nostro compito, non lo useremo, e quindi non è necessario descriverlo separatamente.

   Mettiamo il cursore nel campo "Array". Dopodiché, vai al foglio 2 e, tenendo il pulsante sinistro del mouse, selezionare l'intero contenuto della colonna "Vota".



5. Dopo che le coordinate vengono visualizzate nella finestra dell'operatore, mettiamo il cursore nel campo "Numero di riga". Ritireremo questo argomento utilizzando l'operatore di ricerca. Pertanto, fare clic su un triangolo che si trova sulla sinistra della stringa di funzione. Si apre un elenco di operatori appena usati. Se trovi il nome "Cerca società" tra loro, puoi cliccarlo su di esso. Nel caso contrario, fare clic sull'ultimo punto dell'elenco - "Altre funzioni ...".



6. Inizia la finestra Standard Window Window Wizard. Vai ad esso nello stesso gruppo "Link e array". Questa volta nella lista, scegli la voce "Società di ricerca". Eseguire un clic sul pulsante "OK".



7. L'attivazione degli argomenti degli argomenti dell'operatore di ricerca viene eseguita. La funzione specificata è progettata per emettere il numero del valore in un array specifico con il suo nome. È grazie a questa funzione che calcoliamo il numero di una stringa di un valore specifico per la funzione funzione. La sintassi della scheda di ricerca è presentata:

   = Scheda di ricerca (search_name; visualizzazione__nassive; [type_station])

   "Il desiderato" è un argomento contenente il nome o l'indirizzo della cella della gamma di terze parti in cui si trova. È la posizione di questo nome nell'intervallo di destinazione e dovrebbe essere calcolato. Nel nostro caso, il ruolo del primo argomento sarà referenziale alle cellule su un foglio 1, in cui si trovano i dipendenti.

   "Array listful" è un argomento, che è un riferimento a un array, che esegue la ricerca del valore specificato per determinare la sua posizione. Avremo questo ruolo da eseguire l'indirizzo della colonna "Nome" su un foglio 2.

   "Tipo di confronto" - Un argomento che è facoltativo, ma, a differenza del precedente operatore, sarà necessario questo argomento opzionale. Indica come corrispondere all'operatore è il valore desiderato con un array. Questo argomento può avere uno dei tre valori: -1; 0; 1. Per gli array disordinati, selezionare l'opzione "0". Questa opzione è adatta per il nostro caso.

   Quindi, procedere alla compilazione dei campi della finestra degli argomenti. Mettiamo il cursore nel campo "Valore fogulare", fai clic sulla colonna "Nome" della cella su un foglio 1.



8. Dopo aver visualizzato le coordinate, impostare il cursore nel campo "Elenco massiccio" e andare all'etichetta "foglio 2", che si trova nella parte inferiore della finestra Excel sopra la barra di stato. Clement il tasto sinistro del mouse ed evidenzia il cursore tutte le celle della colonna "Nome".



9. Dopo che le loro coordinate vengono visualizzate nel campo "Elenco massiccio", vai al campo "Tipo di mappatura" e impostare il numero "0" dalla tastiera. Dopodiché, torniamo di nuovo nel campo "Guardando attraverso l'array". Il fatto è che eseguiremo la copia della formula, come abbiamo fatto nel metodo precedente. Ci sarà uno spostamento degli indirizzi, ma qui le coordinate dell'array hanno visto dobbiamo garantire. Non dovrebbe cambiare. Evidenziamo le coordinate con il cursore e fai clic sul tasto funzione F4. Come puoi vedere, il segno del dollaro è apparso davanti alle coordinate, il che significa che il riferimento dal parente si è trasformato in assoluto. Quindi fare clic sul pulsante "OK".



10. Il risultato viene visualizzato nella prima cella della colonna "Scommessa". Ma prima di copiare, dobbiamo riparare un'altra area, vale a dire il primo indice della funzione Argomento. Per fare ciò, selezionare l'elemento della colonna, che contiene una formula e passare alla stringa di formula. Assegnare il primo argomento dell'Indice dell'operatore (B2: B7) e fare clic sul pulsante F4. Come puoi vedere, il segno del dollaro è apparso vicino alle coordinate selezionate. Fai clic sul tasto Invio. In generale, la formula ha preso la seguente forma:

    = Indice (foglio2! $ B $ 2: $ B $ 7; scheda di ricerca (foglio1! A4; lista2! $ A $ 2: $ A $ 7; 0))



11. Ora puoi copiare usando un indicatore di riempimento. Lo chiamiamo nello stesso modo in cui abbiamo parlato prima e allunghiamo fino alla fine della gamma tabellare.



12. Come puoi vedere, nonostante il fatto che l'ordine di stringhe in due tavoli correlato non coincida, tuttavia, tutti i valori sono serrati secondo i nomi dei lavoratori. Ciò è stato raggiunto grazie all'utilizzo della combinazione della ricerca dell'indice degli operatori.

Metodo 4: inserto speciale

Anche gli array della tabella di cravatta in Excel possono utilizzare un inserimento speciale.

1. Seleziona i valori che si desidera "stringere" a un'altra tabella. Nel nostro caso, questa è la portata di colonna "scommessa" su un foglio 2. Fare clic sul frammento dedicato con il tasto destro del mouse. Nell'elenco che si apre, seleziona la voce "Copia". Una combinazione alternativa è la combinazione di tasti CTRL + C. Dopodiché, passiamo al foglio 1.



2. Trasferirsi nell'area del libro di cui hai bisogno, allocare le celle in cui i valori dovranno essere serrati. Nel nostro caso, questa è la colonna "Bid". Fare clic sul frammento dedicato con il tasto destro del mouse. Nel menu di scelta rapida nella barra degli strumenti "Inserisci parametri", fare clic sull'icona "Inserisci comunicazione".

   

   C'è anche un'alternativa. Lui, a proposito, è l'unico per le versioni precedenti di Excel. Nel menu di scelta rapida, portiamo il cursore all'elemento "Inserto speciale". Nel menu aggiuntivo a cui si apre, selezionare la posizione con lo stesso nome.



3. Dopodiché si apre una finestra di inserimento speciale. Fare clic sul pulsante "Inserisci comunicazione" nell'angolo in basso a sinistra della cella.



4. Qualunque opzione scelta, i valori da un array di un tavolo saranno inseriti in un altro. Quando si modificano i dati nella sorgente, cambieranno automaticamente nell'intervallo inserito.

Lezione: inserto speciale in Excel

Metodo 5: Comunicazione tra le tabelle in diversi libri

Inoltre, è possibile organizzare un collegamento tra le aree della tabella in diversi libri. Questo utilizza uno strumento di inserimento speciale. Le azioni saranno assolutamente simili a quelle che abbiamo considerato nel metodo precedente, tranne che la navigazione durante le formule non avrà tra le aree di un libro, ma tra i file. Naturalmente, tutti i libri correlati dovrebbero essere aperti.

1. Selezionare l'intervallo di dati da trasferire in un altro libro. Fai clic sul pulsante con destro del mouse destro del mouse e seleziona la posizione "Copia" nel menu Aperto.



2. Quindi passiamo al libro in cui questi dati dovrebbero essere inseriti. Seleziona l'intervallo desiderato. Fare clic con il tasto destro del mouse. Nel menu di scelta rapida nel gruppo "Inserisci impostazioni", selezionare la voce "Inserisci comunicazione".



3. Successivamente, i valori saranno inseriti. Quando si modificano i dati nel libro sorgente, un array di tabella dal libro di lavoro li serterà automaticamente. E non è affatto necessario garantire che entrambi i libri siano aperti. È sufficiente aprire una sola cartella di lavoro, e verrà automaticamente eseguito i dati su un documento correlato chiuso se ci fossero precedenti cambiamenti in esso.

Ma va notato che in questo caso l'inserto sarà prodotto sotto forma di un array invariato. Quando si tenta di modificare qualsiasi cella con i dati inseriti, un messaggio verrà compilato informando sull'incapacità di farlo.

Le modifiche in tale array associate a un altro libro possono solo rompere la connessione.

Break Titolo tra le tabelle

A volte è necessario rompere la connessione tra le tabelle. Il motivo per questo può essere quanto sopra descritto quando si desidera modificare un array inserito da un altro libro e semplicemente la riluttanza dell'utente in modo che i dati nella stessa tabella siano automaticamente aggiornati dall'altro.

Metodo 1: interruzioni di comunicazione tra i libri

Per rompere la connessione tra libri in tutte le celle, eseguendo effettivamente un'operazione. In questo caso, i dati nelle celle rimarranno, ma saranno già valori statici non aggiornati che non dipendono da altri documenti.

1. Nel libro in cui i valori di altri file vengono serrati, vai alla scheda Dati. Fare clic sull'icona "Modifica collegamenti", che si trova sul nastro nella barra degli strumenti "Connection". Va notato che se il libro corrente non contiene connessioni con altri file, questo pulsante è inattivo.



2. Viene lanciata la finestra di cambio collegamento. Seleziona dall'elenco dei libri correlati (se ce ne sono molti) il file con cui vogliamo interrompere la connessione. Fare clic sul pulsante "Rompere la connessione".



3. Si apre una finestra di informazione, che fornisce un avvertimento sulle conseguenze di ulteriori azioni. Se sei sicuro di fare, fai clic sul pulsante "Break Communication".



4. Successivamente, tutti i riferimenti al file specificato nel documento corrente saranno sostituiti con valori statici.

Metodo 2: inserimento di valori

Ma il metodo sopra è adatto solo se è necessario rompere completamente tutti i collegamenti tra i due libri. Cosa succede se è necessario scollegare le tabelle associate all'interno dello stesso file? Puoi farlo copiando i dati, quindi inserendo lo stesso posto come valori. A proposito, questo metodo può essere rotto tra le singole intervalli di dati di vari libri senza rompere una relazione comune tra i file. Vediamo come funziona questo metodo in pratica.

1. Evidenziamo la gamma in cui desideriamo eliminare la comunicazione con un altro tavolo. Fai clic sul pulsante con destro del mouse destro del mouse. Nel menu Apri, seleziona la voce "Copia". Invece delle azioni specificate, è possibile comporre una combinazione alternativa di tasti hot ctrl + C.



2. Successivamente, senza rimuovere la selezione dallo stesso frammento, fare nuovamente clic su di esso con il tasto destro del mouse. Questa volta nell'elenco delle azioni, fare clic sull'icona "Valore", che è pubblicata nel gruppo di parametri Inserisci.



3. Successivamente, tutti i riferimenti nella gamma dedicata saranno sostituiti con valori statici.

Come puoi vedere, Excel ha modi e strumenti per associare diverse tabelle tra loro. Allo stesso tempo, i dati tabulari possono essere su altri fogli e anche in diversi libri. Se necessario, questa connessione può essere facilmente rotta.