Ketika melakukan tugas-tugas tertentu di Excel terkadang harus berurusan dengan beberapa tabel yang juga terkait satu sama lain. Artinya, data dari satu tabel diperketat ke yang lain dan nilai-nilai di semua tabel terkait dihitung ulang ketika mereka diubah.
Tabel terkait sangat nyaman digunakan untuk menangani sejumlah besar informasi. Tempatkan semua informasi dalam satu meja, selain itu, jika tidak homogen, tidak terlalu nyaman. Sulit untuk bekerja dengan benda-benda seperti itu dan untuk mencari mereka. Masalah yang ditentukan hanya dirancang untuk menghilangkan tabel terkait, informasi antara yang didistribusikan, tetapi pada saat yang sama saling berhubungan. Tabel terkait mungkin tidak hanya dalam satu lembar atau satu buku, tetapi juga untuk ditempatkan di buku-buku terpisah (file). Dua opsi terakhir dalam praktiknya paling sering digunakan, karena tujuan teknologi ini hanya untuk menjauh dari akumulasi data, dan mengangguknya pada satu halaman tidak menyelesaikan secara mendasar. Mari kita pelajari cara membuat dan bagaimana cara bekerja dengan jenis manajemen data seperti itu.
Membuat tabel terkait
Pertama-tama, mari kita fokus pada pertanyaan, dengan cara itu memungkinkan untuk membuat koneksi antara berbagai tabel.Metode 1: Formula Tabel Binding Langsung
Cara termudah untuk mengikat data adalah penggunaan formula di mana ada referensi ke tabel lain. Ini disebut pengikatan langsung. Metode ini intuitif, karena ketika mengikatnya hampir sama seperti membuat referensi ke data dalam satu array tabel.
Mari kita lihat bagaimana Anda dapat membentuk komunikasi dengan mengikat langsung. Kami memiliki dua meja di dua lembar. Pada tabel yang sama, gajinya dihitung dengan menggunakan rumus dengan mengalikan tarif karyawan untuk satu koefisien.
Di lembar kedua ada kisaran meja di mana ada daftar karyawan dengan gaji mereka. Daftar karyawan dalam kedua kasus disajikan dalam satu urutan.
Perlu membuat data pada taruhan dari lembar kedua kencangkan ke dalam sel yang sesuai yang pertama.
- Pada lembar pertama, kami mengalokasikan sel pertama kolom "BET". Kami memasukkannya ke dalam tanda "=". Selanjutnya, klik label "Sheet 2", yang ditempatkan di bagian kiri antarmuka Excel melalui bilah status.
- Ada gerakan di area kedua dokumen. Klik pada sel pertama di kolom "BET". Kemudian klik pada tombol Enter pada keyboard untuk memasukkan data ke dalam sel di mana tanda "sama" sebelumnya diinstal.
- Kemudian ada transisi otomatis ke lembar pertama. Seperti yang bisa kita lihat, nilai karyawan pertama dari tabel kedua ditarik ke dalam sel yang sesuai. Dengan memasang kursor pada sel yang berisi taruhan, kami melihat bahwa formula biasa digunakan untuk menampilkan data di layar. Tetapi di depan koordinat sel, dari mana data output, ada ungkapan "List2!", Yang menunjukkan nama area dokumen di mana mereka berada. Formula umum dalam kasus kami terlihat seperti ini:
= LIST2! B2
- Sekarang Anda perlu mentransfer data tentang tingkat semua karyawan lain dari perusahaan. Tentu saja, ini dapat dilakukan dengan cara yang sama bahwa kami memenuhi tugas untuk karyawan pertama, tetapi mengingat bahwa kedua daftar karyawan terletak dalam urutan yang sama, tugas tersebut dapat disederhanakan secara signifikan dan dipercepat oleh keputusannya. Ini dapat dilakukan hanya dengan menyalin rumus ke kisaran di bawah ini. Karena fakta bahwa referensi unggulan relatif, ketika menyalin nilai-nilai mereka, nilai shift bergeser yang kita butuhkan. Prosedur salinan itu sendiri dapat dibuat menggunakan penanda pengisi.
Jadi, kami menempatkan kursor ke area kanan bawah elemen dengan rumus. Setelah itu, kursor harus dikonversi ke penanda pengisian dalam bentuk salib hitam. Kami melakukan penjepit tombol kiri mouse dan menarik kursor ke jumlah kolom.
- Semua data dari kolom serupa pada lembar 2 ditarik ke tabel pada lembar 1. Ketika data berubah pada lembar 2, mereka akan secara otomatis berubah pada yang pertama.
Metode 2: Menggunakan Blundering of Operator Index - Cari
Tetapi apa yang harus dilakukan jika daftar karyawan dalam array tabel tidak terletak dalam urutan yang sama? Dalam hal ini, seperti yang dinyatakan sebelumnya, salah satu opsi adalah untuk menginstal hubungan antara masing-masing sel-sel yang harus dikaitkan secara manual. Tapi itu cocok kecuali untuk meja kecil. Untuk rentang besar-besaran, opsi ini akan paling membutuhkan banyak waktu pada implementasi, dan paling buruk - dalam praktiknya umumnya akan tidak realistis. Tetapi masalah ini dapat diselesaikan dengan menggunakan sekelompok indeks operator - pencarian. Mari kita lihat bagaimana hal itu dapat dilakukan dengan menggarap data dalam tabel tentang mana percakapan itu dalam metode sebelumnya.
- Kami menyorot elemen pertama dari kolom "BET". Pergi ke fungsi wizard dengan mengklik ikon "Sisipkan Fungsi".
- Dalam wizard fungsi dalam grup "Tautan dan array" Kami menemukan dan mengalokasikan nama "Index".
- Operator ini memiliki dua bentuk: formulir untuk bekerja dengan array dan referensi. Dalam kasus kami, diperlukan opsi pertama, jadi di jendela pemilihan formulir berikutnya yang terbuka, pilih dan klik tombol "OK".
- Indeks argumen operator mulai berjalan. Tugas dari fungsi yang ditentukan adalah output dari nilai yang terletak di rentang yang dipilih di baris dengan angka yang ditentukan. Indeks Operator Formula Umum seperti:
= Indeks (array; number_name; [Number_Stolbits])
"Array" adalah argumen yang berisi kisaran kisaran dari mana kami akan mengekstrak informasi dengan jumlah baris yang ditentukan.
"Nomor baris" adalah argumen yang merupakan jumlah baris ini. Penting untuk mengetahui bahwa nomor baris harus ditentukan tidak relatif terhadap seluruh dokumen, tetapi hanya relatif terhadap array yang dialokasikan.
"Jumlah kolom" adalah argumen yang opsional. Untuk memecahkan spesifik tugas kami, kami tidak akan menggunakannya, dan oleh karena itu tidak perlu menggambarkannya secara terpisah.
Kami meletakkan kursor di bidang "array". Setelah itu, pergi ke lembar 2 dan, memegang tombol kiri mouse, pilih seluruh isi kolom "lease".
- Setelah koordinat ditampilkan di jendela operator, kami meletakkan kursor di bidang "Nomor Baris". Kami akan menarik argumen ini menggunakan operator pencarian. Oleh karena itu, klik segitiga yang terletak di sebelah kiri string fungsi. Daftar operator yang baru digunakan dibuka. Jika Anda menemukan nama "Cari perusahaan" di antara mereka, Anda dapat mengkliknya. Dalam kasus yang berlawanan, klik pada daftar terbaru - "Fungsi lain ...".
- Jendela Wizard Window Standar dimulai. Pergi ke sana dalam kelompok yang sama "tautan dan array". Kali ini dalam daftar, pilih item "Cari perusahaan". Lakukan klik pada tombol "OK".
- Aktivasi argumen argumen dari operator pencarian dilakukan. Fungsi yang ditentukan dirancang untuk menampilkan nomor nilai dalam array tertentu dengan namanya. Ini berkat fitur ini yang kami hitung jumlah string nilai tertentu untuk fungsi fungsi. Sintaks dari board pencarian disajikan:
= Board pencarian (search_name; melihat__nasif; [type_station])
"Yang diinginkan" adalah argumen yang berisi nama atau alamat sel kisaran pihak ketiga di mana ia berada. Ini adalah posisi nama ini dalam kisaran target dan harus dihitung. Dalam kasus kami, peran argumen pertama akan direferensikan ke sel pada lembar 1, di mana karyawan berada.
"Array Lildful" adalah argumen, yang merupakan referensi ke array, yang melakukan pencarian untuk nilai yang ditentukan untuk menentukan posisinya. Kami akan memiliki peran ini untuk melaksanakan alamat kolom "Nama" pada lembar 2.
"Jenis perbandingan" - argumen yang opsional, tetapi, tidak seperti operator sebelumnya, argumen opsional ini akan dibutuhkan. Ini menunjukkan cara mencocokkan operator adalah nilai yang diinginkan dengan array. Argumen ini dapat memiliki satu dari tiga nilai: -1; 0; 1. Untuk array yang tidak teratur, pilih opsi "0". Opsi ini cocok untuk kasus kami.
Jadi, lanjutkan untuk mengisi bidang jendela argumen. Kami meletakkan kursor di bidang "Nilai Fogular", klik pada kolom "nama" sel pertama pada lembar 1.
- Setelah koordinat ditampilkan, atur kursor di bidang "Listing Mative" dan buka label "Sheet 2", yang terletak di bagian bawah jendela Excel di atas bilah status. Clement tombol kiri mouse dan sorot kursor semua sel kolom "nama".
- Setelah koordinat mereka ditampilkan di bidang "Listing Mative", buka bidang "Type Type" dan atur angka "0" dari keyboard. Setelah itu, kami kembali kembali ke lapangan "melihat melalui array". Faktanya adalah bahwa kita akan melakukan menyalin rumus, seperti yang kita lakukan dalam metode sebelumnya. Akan ada pergeseran alamat, tetapi di sini koordinat array dilihat kita perlu mengamankan. Dia seharusnya tidak bergeser. Kami menyorot koordinat dengan kursor dan klik pada tombol fungsi F4. Seperti yang Anda lihat, tanda dolar muncul sebelum koordinat, yang berarti referensi dari relatif berubah menjadi absolut. Kemudian klik tombol "OK".
- Hasilnya ditampilkan di sel pertama kolom "Taruhan". Tetapi sebelum menyalin, kita perlu memperbaiki area lain, yaitu indeks fungsi argumen pertama. Untuk melakukan ini, pilih elemen kolom, yang berisi rumus, dan pindah ke string rumus. Alokasikan argumen pertama dari Indeks Operator (B2: B7) dan klik tombol F4. Seperti yang Anda lihat, tanda dolar muncul di dekat koordinat yang dipilih. Klik tombol Enter. Secara umum, formula mengambil bentuk berikut:
= Indeks (lembar2! $ B $ 2: $ B $ 7; board pencarian (Sheet1! A4; LIST2! $ A $ 2: 0))
- Sekarang Anda dapat menyalin menggunakan penanda pengisi. Kami menyebutnya dengan cara yang sama seperti yang telah kami lobot sebelumnya, dan regangkan ke ujung kisaran tabel.
- Seperti yang Anda lihat, terlepas dari kenyataan bahwa urutan string dalam dua tabel terkait tidak bertepatan, bagaimanapun, semua nilai diperketat sesuai dengan nama-nama pekerja. Ini dicapai berkat penggunaan kombinasi pencarian indeks operator.
Metode 4: Sisipkan Khusus
Array tabel dasi di Excel juga dapat menggunakan penyisipan khusus.
- Pilih nilai yang ingin Anda "kencangkan" ke tabel lain. Dalam kasus kami, ini adalah kisaran kolom "taruhan" pada lembar 2. Klik pada fragmen khusus dengan tombol kanan mouse. Dalam daftar yang terbuka, pilih item "Salin". Kombinasi alternatif adalah kombinasi tombol CTRL + C. Setelah itu, kami pindah ke lembar 1.
- Pindah ke area buku yang Anda butuhkan, alokasikan sel di mana nilai-nilai perlu diperketat. Dalam kasus kami, ini adalah kolom "Bid". Klik pada fragmen khusus dengan tombol kanan mouse. Dalam menu konteks di bilah alat "Sisipkan Parameter", klik ikon "Sisipkan Komunikasi".
Ada juga alternatif. By the way, adalah satu-satunya untuk versi Excel yang lebih lama. Dalam menu konteks, kami membawa kursor ke item "Sisipan Khusus". Di menu tambahan yang terbuka, pilih posisi dengan nama yang sama.
- Setelah itu, jendela insert khusus terbuka. Klik tombol "Sisipkan Komunikasi" di sudut kiri bawah sel.
- Pilihan apa pun yang Anda pilih, nilai dari satu array tabel akan dimasukkan ke yang lain. Saat mengubah data dalam sumber, mereka juga akan secara otomatis berubah dalam rentang yang dimasukkan.
Pelajaran: Sisipan Khusus di Excel
Metode 5: Komunikasi Antara Tabel di Beberapa Buku
Selain itu, Anda dapat mengatur tautan antara area tabel di berbagai buku. Ini menggunakan alat insert khusus. Tindakan akan sangat mirip dengan yang kami pertimbangkan dalam metode sebelumnya, kecuali bahwa navigasi selama formula tidak akan memiliki antara area satu buku, tetapi antara file. Secara alami, semua buku terkait harus dibuka.
- Pilih rentang data yang akan ditransfer ke buku lain. Klik tombol kanan mouse dan pilih posisi "Salin" di menu yang dibuka.
- Kemudian kami pindah ke buku di mana data ini harus dimasukkan. Pilih kisaran yang diinginkan. Klik tombol kanan mouse. Dalam menu konteks di grup "Sisipkan Pengaturan", pilih item "Sisipkan Komunikasi".
- Setelah itu, nilai-nilai akan dimasukkan. Saat mengubah data dalam buku sumber, array tabel dari buku kerja akan mengencangkannya secara otomatis. Dan sama sekali tidak diperlukan untuk memastikan bahwa kedua buku terbuka. Cukup untuk membuka satu buku kerja, dan itu akan secara otomatis menyesuaikan data dari dokumen terkait tertutup jika ada perubahan sebelumnya di dalamnya.
Tetapi harus dicatat bahwa dalam hal ini inset akan diproduksi dalam bentuk array yang tidak berubah. Ketika mencoba mengubah sel apa pun dengan data yang dimasukkan, sebuah pesan akan diisi informasi tentang ketidakmampuan untuk melakukan ini.
Perubahan dalam array yang terkait dengan buku lain hanya dapat merusak koneksi.
Judul istirahat di antara tabel
Kadang-kadang diperlukan untuk memecahkan koneksi antara tabel. Alasan untuk ini bisa jadi di atas dijelaskan ketika Anda ingin mengubah array dimasukkan dari buku lain dan hanya keengganan pengguna sehingga data dalam tabel yang sama secara otomatis diperbarui dari yang lain.Metode 1: Komunikasi istirahat di antara buku
Untuk menghancurkan koneksi antara buku-buku di semua sel, dengan melakukan sebenarnya satu operasi. Dalam hal ini, data dalam sel akan tetap ada, tetapi mereka sudah statis bukan nilai yang diperbarui yang tidak tergantung pada dokumen lain.
- Dalam buku di mana nilai dari file lain diperketat, buka tab Data. Klik ikon "Ubah Tautan", yang terletak pada tape di bilah alat "Koneksi". Perlu dicatat bahwa jika buku saat ini tidak mengandung koneksi dengan file lain, maka tombol ini tidak aktif.
- Jendela perubahan tautan diluncurkan. Pilih dari daftar buku terkait (jika ada beberapa dari mereka) file yang ingin kami hancurkan koneksi. Klik pada tombol "Break the Connection".
- Jendela informasi terbuka, yang memberikan peringatan tentang konsekuensi dari tindakan lebih lanjut. Jika Anda yakin yang akan Anda lakukan, klik tombol "Break Communication".
- Setelah itu, semua referensi ke file yang ditentukan dalam dokumen saat ini akan diganti dengan nilai statis.
Metode 2: Memasukkan Nilai
Tetapi metode di atas hanya cocok jika Anda perlu mematahkan semua tautan di antara kedua buku. Bagaimana jika Anda perlu memutuskan tabel terkait dalam file yang sama? Anda dapat melakukan ini dengan menyalin data, dan kemudian memasukkan tempat yang sama dengan nilai. By the way, metode ini dapat dipecahkan antara rentang data individu dari berbagai buku tanpa merusak hubungan bersama antar file. Mari kita lihat bagaimana metode ini bekerja dalam praktik.
- Kami menyoroti jangkauan di mana kami ingin menghapus komunikasi dengan meja lain. Klik tombol kanan mouse itu. Di menu Buka, pilih item "Salin". Alih-alih tindakan yang ditentukan, Anda dapat memanggil kombinasi alternatif tombol panas Ctrl + C.
- Selanjutnya, tanpa menghapus pemilihan dari fragmen yang sama, lagi klik dengan tombol kanan mouse. Kali ini dalam daftar tindakan, klik ikon "Nilai", yang diposting di grup Parameter Sisipkan.
- Setelah itu, semua referensi dalam rentang khusus akan diganti dengan nilai statis.
Seperti yang Anda lihat, Excel memiliki cara dan alat untuk mengasosiasikan beberapa tabel di antara mereka sendiri. Pada saat yang sama, data tabular dapat berada di lembar lain dan bahkan dalam buku yang berbeda. Jika perlu, koneksi ini dapat dengan mudah rusak.