Sudahkah Anda menggunakan fungsi VLOOKUP untuk memindahkan data kolom dari satu tabel ke tabel lainnya? Karena Excel sekarang memiliki model data bawaan, fungsi VLOOKUP tidak digunakan lagi. Anda dapat membuat hubungan antara dua tabel berdasarkan data yang cocok di dalamnya. Anda kemudian dapat membuat lembar Power View atau PivotTable dan laporan lainnya dengan bidang dari setiap tabel, meskipun berasal dari sumber yang berbeda. Misalnya, jika Anda memiliki data penjualan pelanggan, Anda mungkin ingin mengimpor dan menautkan untuk menganalisis tren penjualan menurut tahun dan bulan.

Semua tabel dalam buku kerja dicantumkan dalam daftar bidang PivotTable dan Power View.

Saat mengimpor tabel terkait dari database relasional data excel sering dapat membuat hubungan ini dalam model data yang dihasilkan di Latar Belakang. Dalam kasus lain, Anda perlu membuat asosiasi secara manual.

Jika tim Hubungan tidak tersedia, maka buku kerja hanya berisi satu tabel.

Di jendela Manajemen Hubungan tekan tombolnya Membuat.

Di jendela Buat koneksi klik panah di sebelah bidang Meja dan pilih tabel dari daftar dropdown. Dalam hubungan satu-ke-banyak, tabel ini harus menjadi bagian multi-anggota. Dalam contoh customer and time intelligence, Anda harus terlebih dahulu memilih tabel customer sales karena kemungkinan akan ada banyak penjualan setiap harinya.

Untuk elemen Kolom (asing) pilih kolom yang berisi data yang terkait dengan elemen. Misalnya, jika ada kolom tanggal di kedua tabel, Anda harus memilih kolom itu sekarang.

di lapangan Tabel terkait pilih tabel yang berisi setidaknya satu kolom data yang terkait dengan tabel yang dipilih di bidang Meja.

di lapangan Kolom terkait (kunci utama) pilih kolom yang berisi nilai unik yang cocok dengan nilai di kolom yang dipilih di lapangan Kolom.

Klik tombol Oke.

Pelajari selengkapnya tentang hubungan antar tabel di Excel

Catatan Hubungan

Contoh. Mengaitkan data intelijen waktu dengan data penerbangan maskapai

Anda dapat mempelajari tentang hubungan antara kedua tabel dan kecerdasan waktu dengan data gratis di Microsoft Azure Marketplace. Beberapa dari kumpulan data ini sangat besar dan membutuhkan koneksi cepat ke Internet.

Anda sekarang dapat membagi penundaan kedatangan berdasarkan tahun dan bulan, serta nilai-nilai lain di kalender.

Kiat: Secara default, bulan terdaftar di Sesuai abjad. Dengan add-in Power Pivot, Anda bisa mengubah urutan agar muncul dalam urutan kronologis.

Meja BasicCalendarUS harus terbuka di jendela Power Pivot.

Di tabel utama, klik Urutkan menurut kolom.

Di bidang "Urutkan", pilih BulanDalamKalender.

Di bidang "Kepada", pilih BulanTahun.

PivotTable sekarang mengurutkan setiap kombinasi "bulan dan tahun" (Oktober 2011, November 2011) menurut jumlah bulan dalam setahun (10, 11). Mengubah urutan pengurutan itu mudah karena salurannya aliran tanggal menyediakan semua kolom yang diperlukan agar skrip ini berfungsi. Jika Anda menggunakan tabel kecerdasan waktu yang berbeda, langkah Anda akan berbeda.

"Hubungan antar tabel mungkin diperlukan"

Saat bidang ditambahkan ke PivotTable, Anda akan diberi tahu bahwa hubungan antar tabel diperlukan untuk menangani bidang yang dipilih dalam PivotTable.

Meskipun Excel dapat memberi tahu Anda kapan tautan diperlukan, Excel tidak dapat memberi tahu Anda tabel dan kolom mana yang akan digunakan, atau apakah tautan dimungkinkan di antara tabel. Untuk mendapatkan jawaban atas pertanyaan Anda, cobalah yang berikut ini.

Langkah 1. Tentukan tabel mana yang akan disertakan dalam relasi

Jika model Anda hanya berisi beberapa tabel, jelas mana yang akan digunakan. Tapi untuk model besar Anda mungkin membutuhkan bantuan. Salah satu caranya adalah dengan menggunakan Tampilan Bagan di add-in Power Pivot. Tampilan Bagan menyediakan visualisasi semua tabel dalam model data. Dengannya, Anda dapat dengan cepat menentukan tabel mana yang terpisah dari model lainnya.

Catatan: Anda bisa membuat hubungan ambigu yang tidak valid saat digunakan dalam laporan PivotTable atau Power View. Semua tabel Anda mungkin terkait dengan beberapa cara ke tabel lain dalam model, tetapi ketika Anda mencoba menggabungkan bidang dari tabel yang berbeda, Anda akan mendapatkan pesan "Hubungan antar tabel mungkin diperlukan." Alasan yang paling mungkin adalah Anda mengalami hubungan banyak-ke-banyak. Jika Anda mengikuti rantai hubungan antar tabel yang terhubung ke tabel yang Anda butuhkan, maka kemungkinan besar Anda akan menemukan bahwa ada dua atau lebih hubungan satu ke banyak antar tabel. Tidak ada solusi mudah yang akan berhasil dalam setiap situasi, tetapi Anda dapat mencoba membuat kolom terhitung untuk menggabungkan kolom yang ingin Anda gunakan dalam satu tabel.

Langkah 2: Temukan kolom yang dapat digunakan untuk membuat jalur dari satu tabel ke tabel lainnya

Setelah Anda menentukan tabel mana yang tidak terkait dengan model lainnya, tinjau kolom dalam tabel tersebut untuk menentukan apakah kolom lain di tempat lain dalam model berisi nilai yang cocok.

Misalkan Anda memiliki model yang berisi penjualan produk menurut wilayah, dan selanjutnya Anda mengimpor data demografi untuk melihat apakah ada korelasi antara penjualan dan tren demografi di setiap wilayah. Karena data demografis berasal dari berbagai sumber, tabel mereka pada awalnya diisolasi dari model lainnya. Untuk mengintegrasikan data demografis dengan model lainnya, Anda perlu menemukan kolom di salah satu tabel demografis yang cocok dengan yang sudah Anda gunakan. Misalnya, jika data demografis diatur menurut wilayah dan data penjualan Anda menentukan area penjualan, maka Anda dapat menghubungkan kedua kumpulan data tersebut dengan menemukan kolom umum seperti negara bagian, Kode Pos atau wilayah untuk menyediakan pencarian.

Selain nilai yang cocok, ada beberapa persyaratan tambahan untuk membuat asosiasi.

Saat melakukan tugas tertentu di Excel, terkadang Anda harus berurusan dengan beberapa tabel, yang juga terkait satu sama lain. Artinya, data dari satu tabel ditarik ke tabel lain, dan ketika berubah, nilai di semua rentang tabel terkait dihitung ulang.

Tabel tertaut sangat berguna untuk memproses informasi dalam jumlah besar. Sangat tidak nyaman untuk memiliki semua informasi dalam satu tabel, apalagi jika tidak homogen. Sulit untuk bekerja dengan objek seperti itu dan mencarinya. Masalah yang ditentukan hanya dirancang untuk menghilangkan tabel terkait, informasi di antaranya didistribusikan, tetapi pada saat yang sama saling berhubungan. Rentang tabel terkait dapat ditempatkan tidak hanya dalam satu lembar atau satu buku, tetapi juga terletak di buku (file) terpisah. Dua opsi terakhir paling sering digunakan dalam praktik, karena tujuan teknologi ini hanya untuk menghindari akumulasi data, dan menumpuknya di satu halaman tidak menyelesaikan masalah secara mendasar. Mari pelajari cara membuat dan bekerja dengan jenis pengelolaan data ini.

Buat tabel tertaut

Pertama-tama, mari kita membahas pertanyaan tentang cara apa yang memungkinkan untuk membuat hubungan antara rentang tabel yang berbeda.

Metode 1: menghubungkan tabel langsung dengan rumus

Cara termudah untuk menautkan data adalah dengan menggunakan rumus yang merujuk ke rentang tabel lainnya. Ini disebut pengikatan langsung. Metode ini intuitif karena melakukan penautan dengan cara yang sama seperti penautan ke data dalam satu larik tabel.

Mari kita lihat bagaimana, dengan menggunakan contoh, kita dapat membentuk tautan dengan tautan langsung. Kami memiliki dua meja di atas dua lembar. Pada satu tabel, upah dihitung menggunakan rumus dengan mengalikan tingkat karyawan dengan koefisien tunggal untuk semua.

Pada lembar kedua terdapat tabel range yang didalamnya terdapat daftar karyawan beserta gajinya. Daftar karyawan dalam kedua kasus disajikan dalam urutan yang sama.

Penting untuk memastikan bahwa data tentang tarif dari lembar kedua ditarik ke dalam sel yang sesuai dari yang pertama.

1. Pada lembar pertama, pilih sel pertama dari kolom "Nilai". Kami menempatkan tanda "=" di dalamnya. Selanjutnya, klik tab "Sheet 2", yang terletak di sisi kiri antarmuka Excel di atas bilah status.
2. Pindah ke area kedua dokumen. Klik pada sel pertama di kolom "Nilai". Kemudian kita klik tombol Enter pada keyboard untuk memasukkan data ke dalam sel di mana tanda "sama" telah ditetapkan sebelumnya.
3. Lalu ada transisi otomatis ke lembar pertama. Seperti yang Anda lihat, nilai tarif karyawan pertama dari tabel kedua ditarik ke sel yang sesuai. Dengan menempatkan kursor pada sel yang berisi nilai, kita melihat bahwa rumus yang biasa digunakan untuk menampilkan data di layar. Tetapi sebelum koordinat sel tempat data ditampilkan, ada ekspresi "Sheet2!", Yang menunjukkan nama area dokumen tempat mereka berada. Rumus umum dalam kasus kami terlihat seperti ini:
4. Sekarang Anda perlu mentransfer data tentang tarif semua karyawan perusahaan lainnya. Tentu saja, ini dapat dilakukan dengan cara yang sama seperti kita menyelesaikan tugas untuk karyawan pertama, tetapi mengingat kedua daftar karyawan berada dalam urutan yang sama, tugas dapat disederhanakan dan dipercepat secara signifikan. Ini dapat dilakukan hanya dengan menyalin rumus ke kisaran di bawah ini. Karena fakta bahwa tautan di Excel relatif secara default, ketika disalin, nilainya bergeser, itulah yang kita butuhkan. Prosedur penyalinan itu sendiri dapat dilakukan dengan menggunakan penanda isian.

   Jadi, letakkan kursor di area kanan bawah elemen dengan rumus. Kursor kemudian harus berubah menjadi pegangan isian silang hitam. Kami melakukan penjepitan tombol kiri mouse dan menyeret kursor ke bagian paling bawah kolom.

5. Semua data dari kolom serupa pada Lembar 2 ditarik ke dalam tabel pada Lembar 1. Saat mengubah data pada Lembar 2, mereka akan secara otomatis berubah pada yang pertama.

Metode 2: menggunakan sekelompok operator INDEX - MATCH

Tetapi bagaimana jika daftar karyawan dalam susunan tabel tidak dalam urutan yang sama? Dalam hal ini, seperti yang disebutkan sebelumnya, satu opsi adalah menautkan secara manual setiap sel yang akan ditautkan. Tapi ini hanya cocok untuk meja kecil. Untuk rentang yang sangat besar, opsi seperti itu, paling-paling, akan membutuhkan waktu yang sangat lama untuk diterapkan, dan paling buruk, dalam praktiknya tidak akan layak sama sekali. Tapi putuskan masalah ini Hal ini dimungkinkan dengan bantuan sekelompok operator INDEX - SEARCH. Mari kita lihat bagaimana hal ini dapat dilakukan dengan menautkan data dalam rentang tabel yang dibahas dalam metode sebelumnya.

1. Pilih elemen pertama dari kolom "Tarif". Buka Wizard Fungsi dengan mengklik ikon "Sisipkan Fungsi".
2. Di Wizard Fungsi, di grup "Referensi dan Array", temukan dan pilih nama "INDEX".
3. Operator ini memiliki dua bentuk: formulir untuk bekerja dengan array dan formulir referensi. Dalam kasus kami, opsi pertama diperlukan, jadi di jendela pemilihan formulir berikutnya yang terbuka, pilih dan klik tombol "OK".
4. Jendela argumen operator INDEX telah diluncurkan. Sebuah tugas fungsi yang ditentukan- menampilkan nilai yang ada dalam rentang yang dipilih sesuai dengan nomor yang ditentukan. Rumus umum untuk operator INDEX adalah:

   INDEX(array, nomor_baris;)

   "Array" - argumen yang berisi alamat rentang dari mana kami akan mengekstrak informasi dengan nomor baris yang ditentukan.

   "Nomor baris" adalah argumen yang merupakan nomor baris ini. Penting untuk diketahui bahwa nomor baris tidak boleh ditentukan relatif terhadap seluruh dokumen, tetapi hanya relatif terhadap larik yang dipilih.

   "Nomor kolom" adalah argumen opsional. Untuk memecahkan masalah khusus kami, kami tidak akan menggunakannya, dan oleh karena itu tidak perlu menjelaskan esensinya secara terpisah.

   Letakkan kursor di bidang "Array". Setelah itu, buka Lembar 2 dan, tahan tombol kiri mouse, pilih semua isi kolom "Rate".

5. Setelah koordinat ditampilkan di jendela operator, letakkan kursor di bidang "Nomor baris". Kami akan menampilkan argumen ini menggunakan operator MATCH. Karena itu, kami mengklik segitiga, yang terletak di sebelah kiri garis fungsi. Daftar operator yang baru saja digunakan akan terbuka. Jika Anda menemukan nama "SEARCH" di antara mereka, Anda dapat mengkliknya. Jika tidak, klik item terakhir dalam daftar - "Fungsi lainnya ...".
6. Jendela standar Wizard Fungsi diluncurkan. Kami meneruskannya ke grup yang sama "Tautan dan larik". Kali ini dalam daftar, pilih item "SEARCH". Kami melakukan klik pada tombol "OK".
7. Jendela argumen operator MATCH diaktifkan. Fungsi yang ditentukan dimaksudkan untuk menampilkan jumlah nilai dalam array tertentu dengan namanya. Berkat fitur ini, kami akan menghitung nomor baris dari nilai tertentu untuk fungsi INDEX. Sintaks MATCH adalah sebagai berikut:

   MATCH(nilai_pencarian, array_pencarian;)

   "Mencari nilai" - argumen yang berisi nama atau alamat sel rentang pihak ketiga tempat ia berada. Posisi nama ini dalam kisaran target yang harus dihitung. Dalam kasus kami, argumen pertama adalah tautan ke sel pada Lembar 1, di mana nama-nama karyawan berada.

   "Array pencarian" - argumen yang merupakan referensi ke larik di mana nilai yang ditentukan dicari untuk menentukan posisinya. Dalam kasus kami, peran ini akan dimainkan oleh alamat kolom "Nama" pada Lembar 2.

   "Jenis pencocokan" adalah argumen opsional, tetapi, tidak seperti operator sebelumnya, kita memerlukan argumen opsional ini. Ini menunjukkan bagaimana operator akan mencocokkan nilai yang dicari dengan array. Argumen ini dapat memiliki salah satu dari tiga nilai: -1; 0; 1. Untuk array yang tidak berurutan, pilih "0". Opsi ini cocok untuk kasus kami.

   Jadi, mari kita mulai mengisi bidang jendela argumen. Kami meletakkan kursor di bidang "Mencari nilai", klik pada sel pertama kolom "Nama" pada Lembar 1.

8. Setelah koordinat ditampilkan, atur kursor di bidang "Array yang dilihat" dan buka label "Lembar 2", yang terletak di bagian bawah jendela Excel di atas bilah status. Tahan tombol kiri mouse dan pilih semua sel di kolom "Nama" dengan kursor.
9. Setelah koordinat mereka ditampilkan di bidang "Array yang dilihat", buka bidang "Jenis pencocokan" dan gunakan keyboard untuk mengatur angka "0" di sana. Setelah itu, kita kembali lagi ke kolom "Viewed array". Faktanya adalah kita akan menyalin rumus, seperti yang kita lakukan pada metode sebelumnya. Akan ada pergeseran alamat, tetapi kita perlu memperbaiki koordinat array yang sedang dilihat. Dia tidak harus bergerak. Pilih koordinat dengan kursor dan tekan tombol fungsi F4. Seperti yang Anda lihat, tanda dolar muncul di depan koordinat, yang berarti bahwa tautan telah berubah dari relatif menjadi absolut. Kemudian klik tombol "OK".
10. Hasilnya ditampilkan di sel pertama kolom "Nilai". Namun sebelum menyalin, kita perlu memperbaiki satu area lagi, yaitu argumen pertama dari fungsi INDEX. Untuk melakukannya, pilih elemen kolom yang berisi rumus dan pindah ke bilah rumus. Pilih argumen pertama dari operator INDEX (B2:B7) dan klik tombol F4. Seperti yang Anda lihat, tanda dolar muncul di dekat koordinat yang dipilih. Kami mengklik tombol Enter. Secara umum, rumus mengambil bentuk berikut:

    INDEX(Lembar2!$B$2:$B$7;MATCH(Lembar1!A4;Lembar2!$A$2:$A$7;0))

11. Sekarang Anda dapat menyalin menggunakan gagang isian. Kami menyebutnya dengan cara yang sama seperti yang kami bicarakan sebelumnya, dan memperluasnya ke akhir rentang tabel.
12. Seperti yang Anda lihat, terlepas dari kenyataan bahwa urutan baris dalam dua tabel terkait tidak cocok, namun, semua nilai ditarik sesuai dengan nama karyawan. Hal ini dicapai melalui penggunaan kombinasi operator INDEX-MATCH.

Baca juga:

Fungsi INDEX di Excel MATCH fungsi di Excel

Metode 3: Lakukan operasi matematika pada data terkait

Pengikatan data langsung juga bagus karena memungkinkan Anda tidak hanya untuk menampilkan di salah satu tabel nilai yang ditampilkan dalam rentang tabel lain, tetapi juga untuk melakukan berbagai operasi matematika dengannya (penjumlahan, pembagian, pengurangan, perkalian, dll. .).

Mari kita lihat bagaimana ini dilakukan dalam praktik. Mari kita buat agar Lembar 3 akan menampilkan data upah umum untuk perusahaan tanpa perincian oleh karyawan. Untuk melakukan ini, tarif karyawan akan ditarik dari Lembar 2, dijumlahkan (menggunakan fungsi SUM) dan dikalikan dengan koefisien menggunakan rumus.

1. Kami memilih sel tempat hasil perhitungan penggajian pada Lembar 3. Kami mengklik tombol "Sisipkan fungsi".
2. Jendela Fungsi Wizard harus diluncurkan. Buka grup "Matematika" dan pilih nama "SUM" di sana. Selanjutnya, klik tombol "OK".
3. Pindah ke jendela argumen fungsi SUM, yang dirancang untuk menghitung jumlah angka yang dipilih. Ini memiliki sintaks berikut:

   SUM(angka1, angka2,…)

   Bidang di jendela sesuai dengan argumen dari fungsi yang ditentukan. Meski jumlahnya bisa mencapai 255 buah, tapi untuk keperluan kita hanya satu saja yang cukup. Kami meletakkan kursor di bidang "Nomor1". Klik label "Sheet 2" di atas bilah status.

4. Setelah kami pindah ke bagian yang diinginkan buku, pilih kolom yang akan dijumlahkan. Kami melakukan ini dengan kursor dengan menahan tombol kiri mouse. Seperti yang Anda lihat, koordinat area yang dipilih segera ditampilkan di bidang jendela argumen. Kemudian klik tombol "OK".
5. Setelah itu, kami secara otomatis pindah ke Lembar 1. Seperti yang Anda lihat, jumlah total tarif karyawan sudah ditampilkan di elemen yang sesuai.
6. Tapi itu tidak semua. Seperti yang kita ingat, gaji dihitung dengan mengalikan jumlah taruhan dengan koefisien. Oleh karena itu, kami kembali memilih sel tempat nilai penjumlahan berada. Setelah itu, buka bilah rumus. Kami menambahkan tanda perkalian (*) ke rumus di dalamnya, lalu klik elemen di mana indikator koefisien berada. Untuk melakukan perhitungan, klik tombol Enter pada keyboard. Seperti yang Anda lihat, program menghitung total upah untuk perusahaan.
7. Kami kembali ke Lembar 2 dan mengubah ukuran tarif setiap karyawan.
8. Setelah itu, kita kembali pindah ke halaman dengan jumlah total. Seperti yang Anda lihat, karena perubahan pada tabel tertaut, hasil dari total gaji dihitung ulang secara otomatis.

Metode 4: pasta khusus

Anda juga dapat menautkan array tabel di Excel menggunakan Tempel Spesial.

1. Kami memilih nilai yang perlu "ditarik" ke tabel lain. Dalam kasus kami, ini adalah kisaran kolom "Nilai" pada Lembar 2. Klik kanan pada fragmen yang dipilih. Pilih "Salin" dari daftar yang muncul. Kombinasi alternatif adalah pintasan keyboard Ctrl+C. Setelah itu, kita pindah ke Lembar 1.
2. Setelah pindah ke area buku yang kami butuhkan, kami memilih sel tempat kami perlu menarik nilainya. Dalam kasus kami, ini adalah kolom Rate. Kami mengklik fragmen yang dipilih dengan tombol kanan mouse. PADA menu konteks Di kotak alat "Sisipkan Opsi", klik ikon "Sisipkan Tautan".

   Ada juga alternatif. Omong-omong, ini adalah satu-satunya untuk versi Excel yang lebih lama. Di menu konteks, arahkan kursor ke item " Sisipan khusus". Di menu tambahan yang terbuka, pilih item dengan nama yang sama.

3. Ini akan membuka jendela Tempel Spesial. Klik tombol "Sisipkan Tautan" di sudut kiri bawah sel.
4. Opsi mana pun yang Anda pilih, nilai dari satu larik tabel akan dimasukkan ke yang lain. Ketika data dalam kode sumber berubah, mereka juga akan secara otomatis berubah dalam rentang yang dimasukkan.

Pelajaran: Tempel Spesial di Excel

Metode 5: Menautkan tabel di beberapa buku kerja

Anda juga dapat menautkan antar tablespace di buku kerja yang berbeda. Ini menggunakan alat Tempel Khusus. Tindakannya akan sama persis dengan yang kami pertimbangkan dalam metode sebelumnya, kecuali bahwa Anda harus menavigasi saat memasukkan rumus bukan di antara area buku yang sama, tetapi di antara file. Secara alami, semua buku terkait harus dibuka pada saat yang bersamaan.

1. Kami memilih rentang data yang perlu ditransfer ke buku kerja lain. Klik kanan padanya dan pilih "Salin" dari menu yang terbuka.
2. Kemudian kita pindah ke buku di mana data ini perlu dimasukkan. Pilih rentang yang diinginkan. Kami mengklik dengan tombol kanan mouse. Dari menu konteks, di grup Opsi Tempel, pilih Tempel Tautan.
3. Setelah itu, nilai-nilai akan dimasukkan. Saat data dalam buku kerja sumber berubah, larik tabel dari buku kerja akan menariknya ke atas secara otomatis. Selain itu, sama sekali tidak perlu bahwa kedua buku itu terbuka untuk ini. Cukup membuka satu buku kerja saja, dan secara otomatis akan menarik data dari yang tertutup. dokumen terkait jika telah dimodifikasi sebelumnya.

Tetapi perlu dicatat bahwa dalam hal ini penyisipan akan dibuat sebagai array yang tidak dapat diubah. Saat Anda mencoba mengubah sel apa pun dengan data yang dimasukkan, sebuah pesan akan muncul yang memberi tahu Anda bahwa tidak mungkin melakukannya.

Perubahan pada larik seperti itu yang ditautkan ke buku kerja lain hanya bisa dilakukan dengan memutus tautan.

Memutus hubungan antar tabel

Terkadang Anda ingin memutuskan hubungan antara rentang tabel. Alasan untuk ini bisa karena kedua kasus yang dijelaskan di atas, ketika Anda ingin mengubah array yang disisipkan dari buku kerja lain, atau hanya keengganan pengguna untuk memiliki data dalam satu tabel yang diperbarui secara otomatis dari tabel lain.

Metode 1: Membatalkan Tautan Buku Kerja

Anda dapat memutuskan hubungan antara buku di semua sel dengan benar-benar melakukan satu operasi. Dalam hal ini, data dalam sel akan tetap ada, tetapi mereka sudah menjadi nilai statis yang tidak dapat diperbarui yang tidak bergantung pada dokumen lain dengan cara apa pun.

1. Di buku tempat nilai diambil dari file lain, buka tab "Data". Kami mengklik ikon "Edit tautan", yang terletak di pita di blok alat "Koneksi". Perlu dicatat bahwa jika buku saat ini tidak berisi tautan ke file lain, maka tombol ini tidak aktif.
2. Jendela Edit Tautan diluncurkan. Kami memilih dari daftar buku yang ditautkan (jika ada beberapa) file yang ingin kami putuskan tautannya. Klik tombol "Batalkan Tautan".
3. Jendela informasi terbuka dengan peringatan tentang konsekuensinya tindakan lebih lanjut. Jika Anda yakin apa yang akan Anda lakukan, klik tombol "Break Ties".
4. 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 harus benar-benar memutuskan semua ikatan antara kedua buku. Apa yang harus dilakukan jika Anda ingin memutuskan tautan tabel terkait yang berada dalam file yang sama? Anda dapat melakukan ini dengan menyalin data lalu menempelkannya di tempat yang sama dengan nilainya. Omong-omong, dengan cara yang sama, Anda dapat memutuskan koneksi antara rentang data individual dari buku yang berbeda tanpa putus koneksi umum antar file. Mari kita lihat bagaimana metode ini bekerja dalam praktik.

1. Kami memilih rentang di mana kami ingin menghapus tautan dengan tabel lain. Kami mengkliknya dengan tombol kanan mouse. Pilih "Salin" dari menu tarik-turun. Alih-alih tindakan ini, Anda dapat mengetikkan kombinasi alternatif tombol pintas Ctrl + C.
2. Selanjutnya, tanpa membatalkan pilihan fragmen yang sama, klik kanan lagi. Kali ini, dalam daftar tindakan, klik ikon "Nilai", yang terletak di grup alat "Opsi Tempel".
3. Setelah itu, semua tautan dalam rentang yang dipilih akan diganti dengan nilai statis.

Seperti yang Anda lihat, Excel memiliki cara dan alat untuk menautkan beberapa tabel menjadi satu. Pada saat yang sama, data tabel dapat ditempatkan di lembar lain dan bahkan di buku yang berbeda. Jika perlu, koneksi ini dapat dengan mudah diputus.

Kami senang dapat membantu Anda mengatasi masalah tersebut.

Ajukan pertanyaan Anda di komentar, jelaskan secara rinci esensi masalahnya. Pakar kami akan mencoba menjawab secepat mungkin.

Apakah artikel ini membantumu?

Hubungan lembar bentang Excel adalah rumus yang mengembalikan data dari sel di buku kerja lain. Saat Anda membuka buku kerja yang berisi link, Excel membaca informasi terbaru dari buku kerja sumber (refresh link)

Hubungan antar tabel di Excel digunakan untuk mengambil data baik dari lembar buku kerja lain maupun dari buku kerja Excel lainnya. Misalnya, Anda memiliki tabel dengan perhitungan jumlah total penjualan. Perhitungannya menggunakan harga produk dan volume penjualan. Dalam hal ini, masuk akal untuk membuat tabel terpisah dengan data harga, yang akan ditarik menggunakan tautan tabel pertama.

Saat Anda membuat tautan antar tabel, Excel membuat rumus yang menyertakan nama buku kerja sumber yang diapit tanda kurung , nama lembar dengan tanda seru di bagian akhir, dan referensi sel.

Buat tautan antar buku kerja

1. Buka kedua buku kerja di Excel
2. Di buku sumber, pilih sel yang akan ditautkan dan salin (pintasan Ctrl + C)
3. Pergi ke buku target, klik kanan pada sel tempat kita ingin meletakkan tautan. Dari menu tarik-turun pilih Sisipan khusus
4. Sisipan khusus memilih Sisipkan tautan.

Ada opsi lain yang lebih sederhana untuk membuat hubungan antar tabel. Di sel tempat kita ingin menyisipkan tautan, kita beri tanda sama dengan (seperti rumus biasa), buka buku kerja sumber, pilih sel yang ingin kita tautkan, klik Enter.

Anda dapat menggunakan alat salin dan pelengkapan otomatis untuk rumus tautan dengan cara yang sama seperti yang Anda lakukan untuk rumus normal.

Sebelum membuat hubungan antar tabel

Sebelum Anda mulai menyebarkan berita tentang ide-ide besar Anda, berikut adalah beberapa tip untuk bekerja dengan hubungan di Excel:

Buat koneksi yang mudah diikuti. Tidak ada pencarian otomatis semua sel yang berisi tautan. Oleh karena itu, gunakan format khusus untuk mengidentifikasi tautan ke tabel lain dengan cepat, jika tidak, dokumen yang ditautkan dapat berkembang menjadi sulit untuk dipelihara.

Perhitungan otomatis. Buku kerja sumber harus bekerja dalam mode penghitungan otomatis (diatur secara default). Untuk mengganti parameter perhitungan, klik tab Rumus ke grup Perhitungan. Pilih Opsi perhitungan -> Otomatis.

Memperbarui Tautan

Untuk pembaruan manual tautan antar tabel, buka tab Data ke grup Koneksi. Klik tombol Ubah tautan.

Pada kotak dialog yang muncul Mengubah koneksi, Memperbarui.

Putuskan tautan di buku kerja Excel

Memutus tautan ke sumber akan menggantikan rumus tautan yang ada dengan nilai yang dikembalikannya. Misalnya, tautan =Harga!$B$4 akan diganti dengan 16. Anda tidak dapat membatalkan tautan, jadi saya sarankan Anda menyimpan buku kerja sebelum melakukan operasi.

Arahkan ke tab Data ke grup Koneksi. Klik tombol Ubah tautan. Pada kotak dialog yang muncul Mengubah koneksi, pilih koneksi yang Anda minati dan klik tombol Putuskan tautannya.

Jika Anda belum terbiasa dengan tabel pivot, mulailah dengan artikel ini.

Masalah

Kebetulan data yang dianalisis datang kepada kami dalam bentuk tabel terpisah, yang, bagaimanapun, perlu ditautkan. MS Access dapat dengan mudah melakukan ini, tetapi di Excel, Anda selalu harus menggunakan rumus seperti VLOOKUP (VLOOKUP). Namun, dimulai dengan Excel 2013, kami memiliki kesempatan untuk menggunakan beberapa tabel sebagai sumber saat membuat tabel pivot, yang terkait satu sama lain dengan bidang kunci.

Contoh

Dalam contoh kami, kami memiliki 4 tabel: Pesanan, Baris pesanan, Produk, Pelanggan.

Meja pesanan:

Tabel Baris Pesanan:

Tabel Produk

Tabel Klien

Tabel sumber dirancang sebagai tabel cerdas: Pesanan, Baris Pesanan, Barang, dan Klien.

Sangat jelas bahwa tabel Pesanan dan BarisPesanan dapat ditautkan oleh bidang ID_Pesanan, tabel Pesanan dan Klien - dengan bidang ID_klien, tabel BarisPesanan dan Barang - dengan bidang ID_produk.

Unduh contoh

Membuat model data

Mari buat tabel pivot berdasarkan tabel mana pun yang ada.

Pilih PivotTable dari menu Sisipkan. Di kotak dialog tersebut, kami melihat opsi untuk Tambahkan data ini ke model data. Kita bisa memilihnya, tapi saya merekomendasikan cara lain yang lebih nyaman. Cukup klik OK.

Di panel Bidang Tabel Pivot yang muncul, Anda melihat tulisan TABEL LAINNYA ...

Mari kita tekan. Akan muncul pertanyaan berikut:

Jika Anda mulai memilih bidang, maka setelah beberapa saat tombol BUAT akan muncul di daftar bidang ...

Mari klik dan buat tautan di antara tabel kita. Ini menciptakan hubungan antara tabel Pesanan dan BarisPesanan. Perhatikan bahwa Excel bisa membuat hubungan satu-ke-satu atau satu-ke-banyak. Dan yang pertama harus menentukan tabel di mana ada "banyak", jika tidak Excel bersumpah dan menyarankan untuk menukarnya.

Demikian pula, kami membuat koneksi lain.

Kotak dialog Kelola Hubungan dapat diakses melalui pita ANALISIS perintah Hubungan

Untuk melihat lebih banyak bidang pada panel Bidang PivotTable, Anda dapat memilih tampilan ini melalui tombol Alat (dalam bentuk roda gigi):

Hasilnya akan seperti ini:

Akibatnya, semua tabel kami sekarang terhubung dan Anda dapat membuat, misalnya, laporan seperti itu.

Lembar buku kerja

Sejauh ini, kami hanya bekerja dengan satu lembar buku kerja. Seringkali berguna untuk menggunakan beberapa lembar kerja.

Tab Lembar terlihat di bagian bawah layar. Jika Anda mengklik tab dengan tombol kiri mouse, lembar yang ditentukan menjadi aktif dan bergerak ke atas. Klik kanan pada tab akan memunculkan menu untuk tindakan lembar seperti memindahkan, menghapus, mengganti nama, dan sebagainya.

Di sudut kiri bawah jendela buku kerja terdapat tombol gulir yang dapat digunakan untuk berpindah dari satu lembar kerja ke lembar kerja lainnya.

Dengan mengklik kanan pada tombol gulir tab, Anda dapat membuka menu sensitif konteks untuk memilih lembar kerja yang diinginkan.

Lokasi buku kerja

Misalkan Anda ingin melihat semua buku kerja yang terbuka di layar sekaligus, Excel akan dengan mudah memenuhi keinginan Anda, dan Anda dapat dengan mudah menavigasi di buku kerja Anda. Menggunakan perintah Excel Jendela Atur Anda dapat mengatur buku kerja yang terbuka di layar dalam empat cara.

* berdampingan - buku kerja terbuka di jendela kecil di mana seluruh layar dibagi dengan cara "ubin";

* dari atas ke bawah - buku kerja terbuka ditampilkan di jendela yang terlihat seperti garis horizontal,

* dari kiri ke kanan -- buku kerja yang terbuka ditampilkan di jendela yang terlihat seperti garis-garis vertikal;

* kaskade - buku kerja (masing-masing di jendelanya sendiri) "diletakkan" di layar berlapis-lapis.

Transisi antar buku kerja

Apakah Anda memutuskan untuk mengatur semua buku kerja terbuka Anda di layar, atau hanya "menumpuk" mereka di atas satu sama lain dalam urutan pembukaan, Anda dapat dengan mudah berpindah dari satu buku kerja ke buku kerja lainnya. Excel menyediakan beberapa cara cepat untuk menavigasi ke buku kerja yang Anda butuhkan. Anda dapat menggunakan mouse, pintasan keyboard, atau menu untuk melakukan ini. Jendela Excel. Berikut cara-caranya:

* klik pada bagian yang terlihat dari jendela buku kerja;

* tekan tombol untuk berpindah dari jendela satu buku ke jendela lain.

* buka menu Jendela Excel. Bagian bawah berisi daftar buku kerja yang terbuka. Untuk menuju ke buku yang diinginkan, cukup klik pada namanya.

Menyalin data dari satu buku kerja ke yang lain

Dengan perintah Salin Excel, Anda dapat menyalin data dari satu buku kerja ke buku kerja lainnya. Misalnya, Anda telah membuka dua buku kerja, satu berisi anggaran triwulanan dan yang lainnya berisi anggaran tahunan. Untuk menghemat waktu, sebaiknya salin data untuk kuartal pertama dari buku kerja pertama ke buku kerja kedua. Pada saat yang sama, salinan data ini akan muncul di buku kerja asli tanpa mengubahnya.

Untuk menyalin data dari satu buku kerja ke buku kerja lainnya, buka kedua buku kerja tersebut. Pilih data di buku kerja pertama dan klik tombol Salin pada toolbar Standar untuk beralih ke buku kerja lain, gunakan salah satu metode di atas. Misalnya, menurut salah satunya, pilih nama buku kerja kedua dari menu Window. Buka lembar kerja yang diinginkan dan pilih sel tempat Anda ingin memasukkan data. Klik tombol Sisipkan pada toolbar Standar. Excel akan langsung menyalin data ke buku kerja kedua.

Mentransfer data antar buku kerja

Prosedur ini mirip dengan menyalin data. Perbedaannya terletak pada penggunaan tombol yang berbeda. Pilih data di buku kerja pertama dan klik tombol Potong pada toolbar Standar untuk mengekstrak data. Saat Anda beralih ke buku kerja lain, pilih sel yang diinginkan dan klik tombol Sisipkan pada toolbar Standar. Akibatnya, Excel akan menghapus data dari buku kerja pertama dan memasukkannya ke dalam yang kedua.

Ada cara cepat mentransfer data lembar kerja antar buku kerja. Ini terdiri dari penggunaan metode "seret dan lepas". Pertama, buka buku kerja yang terlibat dalam operasi transfer data. Pilih Window/Arrange dari menu Excel. Di kotak dialog Pengaturan Jendela yang terbuka, pilih opsi di sebelahnya dan klik tombol OK. Anda harus dapat melihat setidaknya sebagian kecil dari setiap jendela buku kerja. Pilih tab untuk lembar yang ingin Anda salin. Tempatkan penunjuk tetikus di atas tab lembar yang dipilih, klik dan, tanpa melepaskan tombol tetikus, seret tab ke jendela buku kerja lain. Saat Anda melepaskan tombol mouse, lembar akan "terdaftar" di buku kerja baru (untuk itu).

Buat tautan antara lembar kerja dan buku kerja.

Excel memungkinkan Anda untuk menggunakan data dari lembar lain dan dari tabel lain dalam sebuah tabel.

Menautkan adalah proses memperbarui data secara dinamis dalam lembar kerja berdasarkan data dari sumber lain (lembar kerja atau buku kerja). Data tertaut mencerminkan setiap perubahan yang Anda buat pada data asli.

Penautan dilakukan dengan menggunakan formula khusus yang mengandung apa yang disebut tautan eksternal. Referensi eksternal bisa merujuk ke sel di lembar kerja lain di buku kerja yang sama, atau ke sel di lembar kerja lain di buku kerja lain mana pun. Misalnya, tautan antara dua lembar dicapai dengan memasukkan rumus tautan dalam satu lembar dengan referensi ke sel di lembar lain.

Excel memungkinkan Anda membuat tiga jenis tautan ke lembar kerja lain dan buku kerja lainnya:

Saat bekerja dengan jumlah besar data dan membuat beberapa lembar kerja untuk menyimpan data itu, ada situasi di mana rumus pada satu lembar kerja menggunakan data dari lembar kerja lain. Rumus ini sangat berguna karena mereka menyelamatkan Anda dari keharusan menyimpan data yang berlebihan di banyak lembar kerja.

Untuk merujuk ke sel di lembar kerja lain, letakkan Tanda seru antara nama sheet dan nama sel. Sintaks untuk jenis rumus ini adalah sebagai berikut: =SHEET!Cell. Jika lembar Anda memiliki nama, gunakan nama lembar itu alih-alih lembar. Misalnya, Laporkan!B5.

Jika nama mengandung spasi (misalnya, Anggaran 99), maka saat membuat tautan ke lembar lain, nama ini harus diapit oleh tanda kutip tunggal.

Menghubungkan Beberapa Lembar Kerja

Seringkali ada situasi ketika rumus harus merujuk ke rentang sel yang menyertakan dua atau lebih lembar kerja dalam buku kerja. Ini biasanya terjadi saat Anda membuat lembar kerja yang identik untuk alokasi anggaran untuk periode waktu yang berbeda, untuk pekerjaan tim yang berbeda, atau untuk penjualan di wilayah yang berbeda. Anda juga dapat memiliki lembar kerja yang berbeda, tetapi totalnya secara khusus terkandung dalam sel dengan alamat yang sama. Dan semua total ini kemudian dapat disatukan untuk mendapatkan total keseluruhan dalam satu formula yang berisi referensi ke semua lembar dan alamat sel ini.

Dalam kasus seperti itu, Excel mengacu pada rentang sel menggunakan referensi 3D. Referensi 3D dibuat dengan memasukkan rentang lembar (menentukan lembar awal dan lembar akhir) dan rentang sel yang sesuai. Misalnya, rumus yang menggunakan referensi 3D yang menyertakan sheet Sheet1 hingga Sheet5 dan sel A4:A8 mungkin terlihat seperti ini: =SUM(SHEET1:SHEET5!A4:A8).

Referensi 3D dapat dimasukkan dalam formula dengan cara lain. Untuk melakukan ini, cukup klik pada lembar kerja yang ingin Anda sertakan dalam rumus. Tapi pertama-tama mulailah rumus di sel tempat Anda menginginkan hasilnya. Saat saatnya menggunakan tautan 3D, klik tab untuk lembar pertama yang akan dimasukkan ke dalam tautan, lalu tekan (dan tahan) tombol dan klik pada tab lembar terakhir untuk dimasukkan ke dalam tautan. Setelah itu, pilih sel yang diinginkan. Setelah Anda selesai membuat rumus, tekan tombol .

Menghubungkan Buku Kerja

Ada sejumlah istilah yang digunakan saat menautkan buku kerja yang harus Anda ketahui. Buku kerja yang berisi rumus penautan disebut buku kerja dependen, dan buku kerja yang berisi data tertaut disebut buku kerja sumber.

Menghubungkan antara dua file dicapai dengan memperkenalkan formula tautan dalam satu file dengan referensi ke sel di file lain, file yang menerima data dari yang lain disebut file tujuan, dan file yang menyediakan data disebut file sumber .

Setelah koneksi dibuat. Excel menyalin nilai dari sel di file sumber ke sel di file tujuan. Nilai di sel tujuan diperbarui secara otomatis.

Saat merujuk ke sel yang terdapat dalam buku kerja lain, sintaks berikut digunakan: [Book]Sheet!Cell. Saat memasukkan rumus penautan untuk merujuk ke tautan dari buku kerja lain, gunakan nama buku kerja yang terlampir di tanda kurung siku, diikuti tanpa spasi dengan nama lembar kerja, diikuti dengan tanda seru (!), diikuti dengan alamat sel. Misalnya "C:Petrov[Journal1.хls]Literature"!L3.

Memperbarui Tautan

Saat bekerja dengan beberapa buku kerja dan menautkan rumus, Anda perlu mengetahui bagaimana tautan ini diperbarui. Apakah hasil rumus akan diperbarui secara otomatis jika Anda mengubah data dalam sel yang direferensikan hanya jika kedua buku kerja terbuka.

Jika data dalam buku kerja sumber berubah saat buku kerja dependen (yang berisi rumus penautan) ditutup, data terkait tidak segera diperbarui. Saat berikutnya Anda membuka buku kerja dependen, Excel akan meminta Anda untuk mengonfirmasi bahwa Anda ingin memperbarui data. Untuk memperbarui semua data terkait dalam buku kerja, pilih Ya. Jika Anda memiliki tautan yang diperbarui secara manual, atau jika Anda ingin memperbarui sendiri tautan tersebut, pilih Tidak.

Untuk menghapus lembar, pilih perintah Hapus dari menu peka konteks untuk tab. Untuk menghapus beberapa lembar kerja sekaligus, pertama-tama pilih mereka sambil menahan tombol Ctrl.

Memeriksa:

Apakah Anda tahu apa itu: bekerja buku kerja excel; lembar kerja; aturan untuk menulis formula untuk menghubungkan lembar kerja;

Apakah Anda tahu cara: menyisipkan lembar kerja; menghapus; ganti nama; bergerak; salinan; buka jendela; menutup; memesan; menghubungkan antara lembar buku kerja yang sama dan berbeda.

Pekerjaan laboratorium pada Microsoft Excel.

Pertama dan terpenting, menautkan tabel dengan fitur umum bukanlah fungsi khas untuk MS Excel. Untuk tugas seperti itu, ada program khusus yang disebut database. Meskipun lebih tepat untuk menyebutnya "sistem manajemen basis data", atau DBMS. Ini termasuk, misalnya, MS Access, MS SQL Server, Oracle dan banyak lainnya. Dalam sistem ini, menggabungkan tabel dengan bidang kunci umum mungkin merupakan operasi yang paling umum. Dan untuk solusinya ada yang kuat dan alat yang berguna. Mereka memungkinkan Anda untuk mengatur berbagai jenis hubungan antar tabel, mengontrol integritas data, melakukan penghapusan catatan berjenjang, dll. Namun, untuk penggunaan penuh DBMS, Anda memerlukan beberapa pelatihan, pengetahuan tentang bahasa kueri, dll. Oleh karena itu, dalam praktik seorang akuntan, DBMS dalam bentuk murni jarang digunakan, dan semua pengolahan data biasanya dilakukan di program excel. Adapun MS Excel, di sini kemungkinan menghubungkan tabel terbatas. Pada dasarnya koneksi ini adalah untuk menulis rumus yang mengacu pada lembaran yang berbeda atau buku kerja. Anda dapat membuat laporan tercetak atau tabel pivot yang beroperasi pada beberapa database. Tetapi alat khusus untuk organisasi penuh hubungan antara kumpulan data di Excel, tidak ada, dan tidak boleh.

Namun, tidak semuanya begitu buruk ... Faktanya adalah bahwa di pekerjaan nyata semua pilihan untuk menghubungkan tabel ke akuntan biasanya tidak diperlukan. Ya, dan tanpa penghapusan catatan secara kaskade, itu juga akan berhasil. Tetapi menghubungkan satu meja besar dengan buku referensi adalah tugas yang cukup relevan dan dapat diterapkan secara praktis. Dan, yang paling penting (!), Fungsi seperti itu cukup dapat direalisasikan diMS.Excel. Dan satu-satunya hal yang diperlukan untuk ini adalah beberapa trik sederhana dan sistem hyperlink.

Sebagai contoh, saya akan menggunakan dua tabel, yang bentuknya ditunjukkan pada Gambar. 1 dan 2. Tabel pertama (terletak di lembar " Pesanan ”) terdiri dari empat bidang: “ Pesan”, “Tanggal”, “Pelanggan”, “Jumlah ” dan saat kolom kosong “ Komentar. ". Ini adalah versi sederhana dari database pesanan. Di lapangan" Komentar. » kami akhirnya akan menempatkan tautan ke elemen direktori. Jelas bahwa dalam database Pesanan » Satu rekanan dapat muncul berkali-kali - itu tergantung pada jumlah pesanan yang dibuat oleh klien ini.

Tabel kedua disebut " klien ". Saya hanya meninggalkan lima kolom di dalamnya: “ Nama ” (nama perusahaan), diikuti dengan nama direktur, alamat resmi, nomor telepon dan email.

Penting! Di direktori "Klien » satu baris berisi data tentang satu perusahaan. Pengulangan tidak diperbolehkan di sini, setiap klien hadir dalam database hanya sekali.

Jadi, kita akan mengatur hubungan antara tabel " Pesanan" dan "Pelanggan ". Untuk melakukan ini, di lapangan Komentar. » tabel « Pesanan » kita akan menempatkan hyperlink yang akan menunjuk ke baris tabel tertentu « klien ". Algoritma hyperlink akan menjadi sebagai berikut. Pertama, itu harus mengurai isi bidang " NaimPredpr " di baris saat ini meja " Pesanan ". Dengan kata lain, ingat Nama Perusahaan yang memesan tertentu. Tautan kemudian harus melompat ke lembar " klien ". Pada lembar ini, temukan garis yang menggambarkan parameter perusahaan yang membuat pesanan. Dan, akhirnya, atur penunjuk sel saat ini ke baris yang sesuai di " klien ". Ini adalah tampilannya secara umum.

Omong-omong, dalam terminologi basis data, hubungan seperti itu disebut " satu ke banyak " (bila dilihat dari sisi meja " klien "). Artinya, satu elemen dari direktori pelanggan mengacu pada beberapa catatan dalam database pesanan. Di MS Excel, tidak ada alat khusus untuk membuat tautan seperti itu. Kita harus membangunnya sendiri. Untuk melakukan ini, berdasarkan algoritme, kita memerlukan: fungsi bawaan untuk membuat hyperlink (fungsi seperti itu disebut " HIPERLINK() ”) dan fungsi untuk mencari data dalam sebuah tabel. Saya sarankan untuk menggunakan fungsi " COCOK() ". Tapi itu tidak semua. Hyperlinknya harus alamat yang tepat untuk transisi: nama file, nama lembar kerja dan sel yang ingin Anda aktifkan. Untuk mendapatkan nama file, sheet dan cell, kita akan menggunakan fungsi " SEL() ". Selain itu, untuk membentuk garis dengan alamat lompatan, kita harus beralih ke fungsi bawaan untuk bekerja dengan teks: “ MID() "dan" PENCARIAN() ". Itu sebenarnya semua alat utama. Anda dapat melanjutkan ke implementasi praktis.

Mari kita lakukan beberapa langkah awal terlebih dahulu. Mari kita mulai dengan fungsi " HIPERLINK() ", yang mana yang akan kunci ketika mengatur hubungan antar tabel. Dia memiliki dua pilihan. Yang pertama adalah alamat, yaitu nama file dan lembar kerja yang ditunjuk oleh tautan. Parameter kedua adalah alamat sel tertentu pada lembar ini di mana hyperlink akan menunjuk. Kami akan memiliki banyak hyperlink. Dan setiap kali membuatnya, Anda harus menentukan nama file dan nama sheet dalam parameter fungsi. Ini panjang dan tidak nyaman. Oleh karena itu, lebih baik membuat variabel sekali dengan nama sheet dan file, lalu tentukan variabelnya di fungsi " HIPERLINK() " sesuai kebutuhan. Berkenaan dengan MS Excel, akan lebih mudah untuk menggunakan variabel seperti itu rentang sel bernama. Di sinilah kita akan mulai.

1. Kami memanggil Excel, memuat dokumen dan masuk ke menu " Rumus ". Rekaman itu akan terlihat seperti pada Gambar. 3.

2. Di grup" Nama-nama tertentu» klik ikon « Manajer Nama ". Jendela yang ditunjukkan pada Gambar. empat.

3. Di jendela ini, tekan tombol " Membuat ". Jendela " Membuat nama ”, ditunjukkan pada Gambar. 5.

4. Di jendela ini, di bidang " Nama:" masukkan teks "Daftar_Saya". Di bidang Rentang: » cetak rumus «=MID(SEL("nama file" ;Pelanggan!$A$1);SEARCH(" [ " ;CELL(" nama file " ;Pelanggan!$A$1));256)& " ! " » (Gbr. 3).

5. Di jendela "Buat nama", klik "OK".

6. Di jendela Manajer Nama, klik OK.

Sekarang mari kita lihat apa yang sebenarnya kita lakukan? Buku kerja memiliki rentang bernama baru " Daftarku".

Penting! Dalam kasus kami, nama rentang bernama harus TANPA spasi. Jika tidak, menangani rumus tidak akan bekerja dengan benar.

Seperti rentang bernama apa pun, ini menunjuk ke sel atau grup sel dalam buku kerja Excel. Tapi dalam kasus kami, alamat grup ini tidak permanen, tapi dinamis. Itu dibentuk oleh rumus yang kami masukkan di bidang " Jangkauan: ". Secara singkat tentang kerja formula ini. Mari kita mulai dari dalam - dengan fungsi " SEL()".

Ekspresi "CELL(" filename " ;Pelanggan! $A$1) "mengacu pada sel" A1" pada lembar "Pelanggan ” dan kembali untuk sel ini Alamat lengkap, yaitu jalur ke file, nama file, dan nama lembar tempat sel ini berada. Misalnya, dokumen saya dengan tabel disebut " DeanReferencesExcel_.xls ". Itu disimpan di disk D:" di folder "!Faktor ". Maka hasil rumusnya adalah:D:!Faktor[DeanReferencesExcel_.xls]Klien».

Dari baris ini, kita hanya perlu mengambil nama file dan lembaran sebuah buku kerja - huruf drive dan nama folder tidak akan diperlukan saat membuat hyperlink. Untuk mengatasi masalah ini, kami menggunakan fungsi standarExcel untuk bekerja dengan teks.

Anda dapat memotong bagian dari string dengan fungsi " PERTENGAHAN() ". Tapi dia perlu menentukan: teks sumber(kami memiliki jalur lengkap ke sel), posisi awal dan jumlah tanda Yang akan dilucuti dari string asli.

Posisi awal mudah ditentukan. Untuk melakukan ini, gunakan fungsi CARI() ” kita menemukan kemunculan pertama dari kurung buka persegi (“[ ”) ke dalam teks tempat jalur ke sel disimpan. Fragmen rumus yang melakukan operasi ini terlihat seperti ini: SEARCH(" [ " ;CELL(" nama file " ; Pelanggan!$A$1)) ”. Untuk garis" D:!Faktor [DeanReferencesExcel_.xls]Klien» rumus ini akan kembali « 12 ". Jumlah karakter yang perlu dipotong dari teks sumber, saya memilih maksimum - " 256".

Tetap mengganti nilai yang diperoleh ke dalam fungsi " PERTENGAHAN() ". Parameter pertamanya adalah teks dengan alamat ke sel. Parameter kedua adalah jumlah posisi awal untuk memotong bagian dari string. Parameter terakhir- jumlah karakter maksimum yang diperbolehkan dalam satu baris. Rumus untuk operasi ini adalah:=MID(SEL("nama file" ;Pelanggan!$A$1);SEARCH

(" [ " ;CELL(" nama file " ;Pelanggan!$A$1));256) ". Dalam contoh kami, rumus seperti itu akan mengembalikan hasil dalam bentuk "[DeanReferenceExcel_.xls]Pelanggan". Sebenarnya, ini adalah tautan ke lembar " klien » buku kerja « DeanReferencesExcel_.xls ". Berikutnya mungkin alamat sel. Tetapi untuk saat ini, satu elemen hilang - harus ada tanda seru antara nama sheet dan alamat sel (“! "). Kita dapat menambahkan simbol ini ke rumus menggunakan operasi "& ", dan di versi final ekspresinya akan terlihat seperti ini:"=MID(SEL("nama file" ;Pelanggan!$A$1);SEARCH(" [ " ;SEL(" nama file " ; Klien!$A$1)); 256)&" ! " ". Dan hasil pekerjaannya adalah:».

Nasihat Salin fragmen rumus dengan hati-hati ke dalam sel lembar kerja yang terpisah, dan kemudian pekerjaan setiap bagiannya akan ada di ujung jari Anda.

Omong-omong, pengoperasian rumus di dalam rentang bernama mudah diperiksa. Masukkan ekspresi "=Lembar_Saya ” dan tekan tombol “ Memasuki ". Hasilnya akan muncul di sel:[DeanLinkExcel_.xls]Pelanggan!».

Penting! Perhatikan bahwa nama lembar tidak boleh berisi spasi agar rumus berfungsi dengan benar.

1. Pergi ke lembar " Pesanan ", masuk ke sel" E2".

2. Masukkan rumus "=HYPERLINK(Lembar_Saya&ADDRESS(SEARCH(C2;Pelanggan!$A:$A;0); 1);" > " ) ».

3. Salin rumus ini ke seluruh tinggi tabel. Hasil pekerjaan kami ditunjukkan pada Gambar. 6.

Mari kita periksa apa yang kita punya. Di meja " Pesanan »klik kiri, misalnya pada sel « E6 ". Baris ini berisi informasi tentang pesanan dengan nomor " 5 "dari perusahaan" PE "Kolo"". Setelah klik Excel akan beralih ke " klien ", dan penunjuknya Sel aktif akan berada di alamat A6 ". Di posisi direktori inilah informasi tentang " PE "Kolo"" (Gbr. 7).

Mari kita pertimbangkan secara singkat algoritma rumus. ekspresi "=MATCH(C2;Pelanggan! $A:$A;0)» menemukan sel di kolom « A" pada lembar "Pelanggan ”, yang berisi nama counterparty dari sel “ C2" lembar "Pesanan ". Parameter terakhir dari fungsi " MATCH() "sama dengan" 0 ". Artinya akan mencari nilai sesuai prinsip tepat pertandingan. Mari kita kembali ke contoh kita. Misalkan kita bekerja dengan string " 6 » tabel « Pesanan » (Gbr. 6). Baris ini berisi nomor urut " 5 "dari perusahaan" PE "Kolo" ". Setelah menyalin hyperlink dari sel " E2 » di bawah kolom « E" di sel "E6 "Rumus dengan fungsi pencarian akan menjadi seperti ini:"MATCH(C6;Pelanggan!$A:$A;0)". Dan hasil dari ekspresi ini akan sama dengan " 6 ". Ini berarti bahwa dalam tabel klien " Deskripsi Perusahaan " PE "Kolo" " terletak di keenam garis lembar kerja.

Pindah. Kami akan mengganti hasil ekspresi ini sebagai parameter pertama dari alamat fungsi. Parameter kedua adalah " 1 ". Kemudian ungkapan " ALAMAT(CARI (C2;Pelanggan!$A:$A;0);1)" (baris kedua dari tabel " Pesanan "") pada dasarnya berarti " ALAMAT(2;1) ". Fungsi ini akan mengembalikan sebagai teks alamat sel yang ada di baris kedua dan kolom pertama lembar kerja. Untuk pesanan" 1 ' (baris kedua) ini akan menjadi alamat '$A$2 ". Untuk pesanan di baris keenam tabel (perusahaan " PE "Kolo" ") ekspresi akan mengembalikan string "$A$6", dll.

Dengan pengalamatan sel sudah jelas. Sekarang Anda perlu menggunakan hasilnya dalam fungsi " HIPERLINK() ".

Inilah situasinya. Biarkan saya mengingatkan Anda bahwa fungsi " HIPERLINK() » dua parameter. Yang pertama adalah alamat tempat hyperlink menunjuk. Ini termasuk nama file, lembar, dan alamat sel untuk melompat. Kami sudah memiliki nama file dan sheet, informasi ini disimpan dalam variabel " Daftarku ". Kami menerima alamat sel untuk ditautkan ke rekanan untuk pesanan tertentu. Tetap menggabungkan dua bagian ini dengan operasi "& ". Dan kemudian ekspresi terakhir untuk alamat lompatan adalah:Daftar_Saya&ADDRESS(SEARCH(C2; Pelanggan!$A:$A;0);1)».

Mari kita lihat apa yang kita dapatkan. Untuk sel " C2 » rumus ini akan mengembalikan hasil «[DeanReferenceExcel_.xls]Pelanggan!$A$2" - yaitu referensi ke sel " A2 » di direktori « klien ". Itu benar: untuk nomor pesanan " 1 » tautan menunjuk ke rekanan « TOV "Smit" ". Jika Anda merujuk ke sel C6 " (jumlah order " 5" dari "PE"Kolo" ”), lalu di baris tabel ini “ Pesanan "Ekspresi untuk alamat lompatan akan terlihat seperti ini:"My_Sheet&ADDRESS(SEARCH(C6;Pelanggan!$A:$A;0);1)". Dan hasil rumusnya adalah:[DeanReferenceExcel_.xls]Pelanggan!$A$6».

Kami meneruskan ke parameter kedua dari fungsi " HIPERLINK() ". Ini harus menjadi teks yangExcel akan menampilkan hyperlink di tempatnya. Dengan kata lain, ini adalah nama hyperlink itu sendiri. Saya memilih simbol ">" (tanda tangani "lebih "). Dan kemudian rumus terakhir untuk membuat hyperlink adalah:=HYPERLINK(Daftar_Saya&ADDRESS(SEARCH(C2; Pelanggan!$A:$A; 0);1);" > " ) ».

Itu semua tentang menciptakan hubungan antar tabelUnggul. Kami mengatasi tugas itu, semua tautan berfungsi dengan benar. Satu-satunya hal yang saya ingin menambahkan beberapa kata pada desain meja. Sekarang di kolom

Dalam artikel ini, Anda akan mempelajari cara cepat menggabungkan data dari dua tabel Excel saat tidak ada kecocokan persis di kolom kunci. Misalnya, ketika pengidentifikasi unik dari tabel pertama adalah lima karakter pertama dari pengidentifikasi dari tabel kedua. Semua solusi yang disarankan dalam artikel ini telah saya uji di Excel 2013, 2010 dan 2007.

Jadi ada dua lembar excel yang perlu digabungkan untuk analisis lebih lanjut data. Misalkan satu tabel berisi harga (kolom Harga) dan deskripsi barang (kolom Bir) yang Anda jual, dan tabel kedua berisi data ketersediaan barang dalam stok (kolom In stock). Jika Anda atau kolega Anda menyusun kedua tabel katalog, maka keduanya harus berisi setidaknya satu kolom kunci dengan pengidentifikasi produk unik. Deskripsi atau harga produk dapat berubah, tetapi pengidentifikasi unik selalu tetap sama.

Kesulitan dimulai ketika Anda menerima beberapa tabel dari pabrikan atau dari departemen lain perusahaan. Masalahnya bisa menjadi lebih rumit jika tiba-tiba diperkenalkan format baru pengenal unik atau hanya sedikit perubahan pada stock nomenclature designations (SKU). Dan Anda dihadapkan dengan tugas untuk bersatu Excel baru dan tabel lama dengan data. Dengan satu atau lain cara, situasi muncul ketika hanya ada kecocokan sebagian catatan di kolom kunci, misalnya, “ 12345 " dan " 12345-new_suffix“. Anda mengerti bahwa ini adalah SKU yang sama, tetapi komputer tidak begitu cerdas! Ini bukan kecocokan yang tepat, sehingga tidak mungkin menggunakan konvensional rumus excel untuk menggabungkan data dari dua tabel.

Dan yang benar-benar buruk adalah korespondensinya bisa sangat kabur, dan “ Beberapa perusahaan” dalam satu tabel dapat berubah menjadi “ CJSC "Beberapa Perusahaan"” di tabel lain, dan “ Perusahaan baru(mantan Beberapa Perusahaan)" dan " Perusahaan Lama” juga akan menjadi rekor tentang perusahaan yang sama. Anda tahu ini, tapi bagaimana Anda menjelaskannya ke Excel?

Komentar: Solusi yang dijelaskan dalam artikel ini bersifat universal. Anda dapat mengadaptasinya untuk digunakan lebih lanjut dengan formula standar apa pun seperti VLOOKUP(VLOOKUP), COCOK(COCOK) , GPR(HLOOKUP) dan seterusnya.

Pilih contoh yang tepat untuk melompat langsung ke solusi yang tepat:

Kolom kunci di salah satu tabel berisi karakter tambahan

Pertimbangkan dua tabel. Kolom tabel pertama berisi nomor barang (SKU), nama bir (Bir) dan harganya (Harga). Tabel kedua berisi SKU dan jumlah botol yang tersedia (In stock). Alih-alih bir, bisa ada produk apa pun, dan jumlah kolom dalam kehidupan nyata bisa jauh lebih besar.

Di tabel dengan simbol tambahan, kami membuat kolom bantu. Anda dapat menambahkannya ke akhir tabel, tetapi yang terbaik adalah menyisipkannya di sebelah kanan setelah kolom kunci sehingga terlihat.

Kunci dalam tabel dalam contoh kita adalah kolom SEBUAH dengan data SKU, dan Anda perlu mengekstrak 5 karakter pertama darinya. Mari tambahkan kolom bantu dan beri nama pembantu SKU:

Siap! Kami sekarang memiliki kolom kunci dengan nilai pencocokan tepat - kolom pembantu SKU di tabel dan kolom utama SKU dalam tabel yang akan dicari.

Sekarang menggunakan fungsi VLOOKUP(VLOOKUP) kami mendapatkan hasil yang diinginkan:

rumus lainnya

• Ekstrak dulu X karakter di sebelah kanan: misalnya, 6 karakter di sebelah kanan dari entri "DSFH-164900". Rumusnya akan terlihat seperti ini:

  KANAN(A2;6)
  =KANAN(A2,6)

• Lewati dulu X karakter, ekstrak berikut ini kamu karakter: misalnya, Anda perlu mengekstrak "0123" dari entri "PREFIX_0123_SUFF". Di sini kita perlu melewati 8 karakter pertama dan mengekstrak 4 karakter berikutnya. Rumusnya akan terlihat seperti ini:

  TENGAH(A2;8;4)
  =MID(A2,8,4)

• Ekstrak semua karakter hingga pemisah, panjang urutan yang dihasilkan mungkin berbeda. Misalnya, Anda ingin mengekstrak "123456" dan "0123" dari entri "123456-suffix" dan "0123-suffix", masing-masing. Rumusnya akan terlihat seperti ini:

  KIRI(A2,TEMUKAN("-",A2)-1)
  =KIRI(A2,TEMUKAN("-",A2)-1)

Singkatnya, Anda dapat menggunakan fungsi excel, bagaimana KIRI(KIRI) BAIK(BAIK), PSTR(PERTENGAHAN), TEMUKAN(FIND) untuk mengambil bagian mana pun dari indeks komposit. Jika Anda memiliki kesulitan dengan ini, kami akan melakukan yang terbaik untuk membantu Anda.

Data dari kolom kunci di tabel pertama dibagi menjadi dua atau lebih kolom di tabel kedua

Katakanlah tabel yang dicari berisi kolom dengan pengidentifikasi. Sel-sel kolom ini berisi catatan formulir XXXX-YYYY, di mana XXXX adalah kode sebutan untuk sekelompok barang ( Handphone, televisi, camcorder, kamera), dan YYYY adalah kode produk dalam grup. Tabel utama terdiri dari dua kolom: satu berisi kode kelompok komoditas (Group), yang kedua berisi kode barang (ID). Kami tidak bisa begitu saja membuang kode grup produk, karena kode produk yang sama dapat diulang dalam grup yang berbeda.

Di dalam sel C2 kami menulis rumus berikut:

CONCATENATE(A2;"-";B2)
=CONCATENATE(A2,"-",B2)

Di Sini A2 adalah alamat sel yang berisi kode grup; simbol " – ” adalah pembatas; B2 adalah alamat sel yang berisi kode produk. Salin rumus ke sisa baris.

Sekarang tidak akan sulit untuk menggabungkan data dari dua tabel kami. Kami akan mencocokkan kolom ID lengkap tabel pertama dengan kolom PENGENAL meja kedua. Ketika kecocokan ditemukan, entri dari kolom Keterangan dan harga tabel kedua akan ditambahkan ke tabel pertama.

Data di kolom kunci tidak cocok

Berikut ini contohnya: Anda adalah pemilik toko kecil, Anda menerima barang dari satu atau lebih pemasok. Masing-masing dari mereka memiliki nomenklatur sendiri, yang berbeda dari Anda. Akibatnya, situasi muncul ketika entri "Case-Ip4S-01" Anda cocok dengan entri "SPK-A1403" di berkas Excel diterima dari pemasok. Perbedaan tersebut terjadi secara acak dan tidak ada peraturan umum untuk secara otomatis mengonversi “SPK-A1403” menjadi “Case-Ip4S-01”.

Kabar buruk: Data yang terkandung dalam keduanya tabel excel, harus diproses secara manual sehingga di masa mendatang dimungkinkan untuk menggabungkannya.

Kabar baik: Ini hanya perlu dilakukan sekali, dan tabel bantu yang dihasilkan dapat disimpan untuk digunakan di masa mendatang. Kemudian Anda dapat menggabungkan tabel ini secara otomatis dan menghemat banyak waktu dengan cara ini

1. Buat tabel bantu untuk pencarian.

Membuat daun baru Excel dan sebut saja Konverter SKU. Menyalin seluruh kolom SKU kami dari selembar Toko ke lembar baru, hapus duplikat dan tinggalkan hanya nilai unik di dalamnya.

Tambahkan kolom di sebelahnya Supp.SKU dan secara manual mencari kecocokan antara nilai kolom SKU kami dan Supp.SKU(deskripsi dari kolom akan membantu kami dalam hal ini Keterangan). Ini adalah pekerjaan yang membosankan, biarkan pikiran bahwa Anda harus melakukannya hanya sekali :-) menyenangkan Anda.

Hasilnya, kami memiliki tabel berikut:

2. Perbarui tabel utama dengan data dari tabel pencarian.

Masukkan kolom baru ke tabel utama (Store sheet) Supp.SKU.

Selanjutnya, menggunakan fungsi VLOOKUP(VLOOKUP) membandingkan lembar Toko dan Konverter SKU SKU kami, dan untuk data yang diperbarui - kolom Supp.SKU.

Kolom Supp.SKU diisi dengan kode pabrikan asli.

Komentar: Jika dalam kolom Supp.SKU sel kosong muncul, maka Anda harus mengambil semua kode SKU sesuai dengan sel kosong ini, tambahkan ke tabel Konverter SKU dan temukan kode yang sesuai dari tabel vendor. Setelah itu, ulangi langkah 2.

3. Transfer data dari tabel pencarian ke tabel utama

Tabel utama kami memiliki kolom kunci yang sama persis dengan elemen tabel pencarian, jadi tugas ini sekarang sangat mudah.

Menggunakan fungsi VLOOKUP(VLOOKUP) menggabungkan data lembar Toko dengan data lembar Pemasok Grosir 1, menggunakan kolom untuk mencari kecocokan Supp.SKU.

Berikut adalah contoh data yang diperbarui dalam kolom harga grosir:

Ini sederhana, bukan? Ajukan pertanyaan Anda di komentar artikel, saya akan mencoba menjawab sesegera mungkin.