Misalkan Anda memiliki buku kerja yang berisi banyak lembar kerja, dan sekarang Anda ingin mengekstrak data dalam sel yang sama di beberapa lembar kerja menjadi satu lembar kerja master. Misalnya, tarik sel B8 dari Sheet1, Sheet2, Sheet3, Sheet4… ke dalam master sheet seperti yang ditunjukkan tangkapan layar berikut. Bagaimana Anda bisa menyelesaikan tugas ini dengan cepat dan mudah di Excel?
Referensi sel yang sama dari beberapa lembar kerja menjadi satu lembar master dengan rumus
Jika nama lembar kerja Anda adalah nama lembar default, seperti Lembar1, Lembar2, Lembar3. , Anda dapat menggunakan rumus untuk menangani pekerjaan ini dengan cepat di Excel
1. Pertama, Anda harus membuat daftar nomor pembantu, silakan masukkan 1, 2, 3, 4. nomor urut yang menunjukkan lembar kerja yang perlu Anda rujuk, lihat tangkapan layar
2. Kemudian, masukkan rumus di bawah ini ke dalam sel tempat Anda ingin mengekstrak datanya, lalu seret gagang isian ke sel yang ingin Anda terapkan rumus ini, dan semua nilai sel yang sama dari beberapa lembar kerja telah diekstrak, lihat tangkapan layar
=INDIRECT("'Lembar" & E2 & "'. $B$8")
Catatan. Pada rumus di atas, E2 adalah nomor pembantu yang Anda masukkan pada langkah 1, dan B8 adalah referensi sel yang ingin Anda ekstrak. Rumus ini hanya bekerja dengan baik jika nama sheet adalah Sheet1, Sheet2, Sheet3
Referensi sel yang sama dari beberapa lembar kerja menjadi satu lembar master
Kutools for Excel mendukung fitur yang kuat-Merujuk Secara Dinamis ke Lembar Kerja yang dapat membantu Anda mereferensikan nilai sel yang sama di beberapa lembar kerja menjadi lembar master. Silakan lihat demo di bawah ini. Klik untuk mengunduh Kutools untuk Excel
Referensi sel yang sama dari beberapa lembar kerja menjadi satu lembar master dengan kode VBA
Jika ada puluhan lembar kerja yang nama lembarnya beragam isinya bukan Lembar1, lembar2, dalam hal ini, kode VBA berikut dapat membantu Anda mengisi referensi sel yang sama dari beberapa lembar kerja ke dalam satu lembar kerja sekaligus
1. Di lembar kerja Master, klik sel B8 yang merupakan sel yang ingin Anda ekstrak dari lembar kerja lain
2. Tahan tombol ALT + F11, dan itu membuka jendela Microsoft Visual Basic for Applications
3. Klik Sisipkan > Modul, dan tempelkan kode berikut di Jendela Modul
kode VBA. referensi sel yang sama dari beberapa lembar kerja
Sub AutoFillSheetNames() 'Update by Extendoffice Dim ActRng As Range Dim ActWsName As String Dim ActAddress As String Dim Ws As Worksheet On Error Resume Next xTitleId = "KutoolsforExcel" Set ActRng = Application.ActiveCell ActWsName = Application.ActiveSheet.Name ActAddress = ActRng.Address(False, False) Application.ScreenUpdating = False xIndex = 0 For Each Ws In Application.Worksheets If Ws.Name <> ActWsName Then ActRng.Offset(xIndex, 0).Value = "='" & Ws.Name & "'!" & ActAddress xIndex = xIndex + 1 End If Next Application.ScreenUpdating = True End Sub4. Kemudian tekan tombol F5 untuk menjalankan kode ini, dan semua nilai sel B8 dari lembar kerja lain telah ditarik ke lembar kerja Master. Lihat tangkapan layar
Catatan. Kode VBA ini diterapkan untuk mengisi sel tempat Anda mengklik. Misalnya, jika Anda mengklik sel A1 di lembar kerja tertentu, semua nilai sel A1 dari lembar kerja lain akan diisi ke dalam lembar kerja ini.
Referensi sel yang sama dari beberapa lembar kerja menjadi satu lembar master dengan fitur luar biasa
Jika Anda tidak terbiasa dengan kode VBA, di sini, saya dapat memperkenalkan Anda alat yang berguna - Kutools for Excel, dengan utilitas Dynamically Refer to Worksheets, Anda dapat mengisi referensi sel dari beberapa lembar kerja di Excel dengan mudah
Tips. Untuk menerapkan fitur Dynamically Refer to Worksheets ini, pertama-tama Anda harus mengunduh Kutools for Excel, lalu menerapkan fitur tersebut dengan cepat dan mudah.
Setelah menginstal Kutools for Excel, silakan lakukan seperti ini
1. Klik sel B8 di dalam lembar Master yang ingin Anda ekstrak dari lembar kerja lain
2. Lalu klik Kutools > Lainnya > Rujuk Secara Dinamis ke Lembar Kerja, lihat tangkapan layar
3. Di kotak dialog Fill Worksheets References, pilih Fill vertically cell after cell dari Fill order, dan klik kunci kecil di sebelah kotak teks rumus, dan kunci abu-abu akan menjadi kunci kuning, artinya rumus dan referensi sel telah dikunci, . Kemudian, periksa lembar kerja yang ingin Anda ekstrak referensi selnya. Lihat tangkapan layar
Catatan. Jika Anda tidak mengunci kunci kecil ini, referensi sel yang Anda klik akan diubah dengan sel yang dipilih
4. Kemudian klik tombol Fill Range, dan nilai sel dari sel B8 dari lembar kerja lain telah dicantumkan secara vertikal di lembar kerja Master, lalu tutup kotak dialog ini. Lihat tangkapan layar
Klik untuk Mengunduh Kutools untuk Excel dan uji coba gratis Sekarang
Lebih banyak artikel
- Salin Baris Dari Beberapa Lembar Kerja Berdasarkan Kriteria Ke Lembar Baru
- Misalkan, Anda memiliki buku kerja dengan tiga lembar kerja yang memiliki format yang sama seperti gambar di bawah ini. Sekarang, Anda ingin menyalin semua baris dari lembar kerja ini yang kolom C berisi teks "Selesai" ke dalam lembar kerja baru. Bagaimana Anda bisa menyelesaikan masalah ini dengan cepat dan mudah tanpa menyalin dan menempelkannya satu per satu secara manual?
- Buat Daftar Nilai Unik Dari Beberapa Lembar Kerja
- Apakah ada cara cepat bagi kami untuk membuat daftar nilai unik dari semua lembar kerja dalam buku kerja?
- Countif Nilai Tertentu di Beberapa Lembar Kerja
- Misalkan, saya memiliki beberapa lembar kerja yang berisi data berikut, dan sekarang, saya ingin mendapatkan jumlah kemunculan nilai tertentu "Excel" dari lembar kerja ini. Bagaimana saya bisa menghitung nilai tertentu di beberapa lembar kerja?
- Sisipkan Gambar Yang Sama Ke Beberapa Lembar Kerja
- Biasanya, kita dapat menyisipkan gambar ke dalam lembar kerja dengan cepat menggunakan fungsi Sisipkan di Excel, namun, pernahkah mencoba menyisipkan gambar yang sama ke semua lembar kerja di buku kerja Anda?
- Jalankan Makro Yang Sama Pada Beberapa Lembar Kerja Sekaligus Di Excel
- Biasanya, kita dapat menjalankan makro di lembar kerja, jika ada beberapa lembar yang perlu menerapkan makro yang sama ini, Anda harus memicu kode satu per satu lembar. Jika ada cara cepat lain untuk menjalankan makro yang sama di beberapa lembar kerja sekaligus di Excel?
Alat Produktivitas Kantor Terbaik
Kutools for Excel Memecahkan Sebagian Besar Masalah Anda, dan Meningkatkan Produktivitas Anda hingga 80%
- Gunakan kembali. Masukkan dengan cepat rumus kompleks, bagan dan apa pun yang pernah Anda gunakan sebelumnya; . Encrypt Cells with password; Create Mailing List and send emails...
- Super Formula Bar (mengedit beberapa baris teks dan formula dengan mudah); . Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Menggabungkan Sel/Baris/Kolom tanpa kehilangan Data; . Cegah Sel Duplikat; . Combine Duplicate Rows/Columns.. Prevent Duplicate Cells; Compare Ranges...
- Pilih Baris Duplikat atau Unik ; . Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Beberapa Sel tanpa mengubah referensi rumus; . Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Ekstrak Teks , Tambah Teks, Hapus berdasarkan Posisi, Hapus Spasi ; . Convert Between Cells Content and Comments...
- Filter Super (simpan dan terapkan skema filter ke sheet lain); . Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Menggabungkan Buku Kerja dan Lembar Kerja ; . Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- Lebih dari 300 fitur canggih . Mendukung Office / Excel 2007-2021 dan 365. Mendukung semua bahasa. Penerapan yang mudah di perusahaan atau organisasi Anda. Fitur lengkap Uji coba gratis 30 hari. Jaminan uang kembali 60 hari.
Baca selengkapnya. Download Gratis. Pembelian.
Office Tab Menghadirkan antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Lebih Mudah
- Aktifkan pengeditan dan pembacaan bertab di Word, Excel, PowerPoint , Publisher, Access, Visio, dan Project.
- Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru
- Meningkatkan produktivitas Anda hingga 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari
Baca selengkapnya. Download Gratis. Pembelian.
Terlama Pertama
Urutkan komentar menurut
Terlama Pertama
Pertama Terbaru
Komentar ( 13 )
Belum ada peringkat. Jadilah yang pertama menilai
ihiasat
sekitar 8 tahun yang lalu
Komentar ini diminimalkan oleh moderator di situs
terima kasih banyak sangat bermanfaat. gulungan
Membalas
0
0
fra
sekitar 8 tahun yang lalu
Komentar ini diminimalkan oleh moderator di situs
Dan bagaimana jika saya ingin menentukan sel tidak mengkliknya tetapi menulisnya?
Membalas
0
0
Keith
sekitar 7 tahun yang lalu
Komentar ini diminimalkan oleh moderator di situs
Anda melewatkan tanda kutip tunggal di baris 16 kode VBA. Kode ini benar. Sub IsiOtomatisSheetNames() 'Perbarui 20131202 Dim ActRng Sebagai Kisaran Dim ActWsName Sebagai String Dim ActAddress Sebagai String Dim Ws Sebagai Lembar Kerja Saat Kesalahan Lanjutkan Berikutnya xTitleId = "KutoolsforExcel" Tetapkan ActRng = Aplikasi. ActiveCell ActWsName = Aplikasi. Lembar aktif. Nama AlamatAkt = ActRng. Alamat (Salah, Salah) Aplikasi. ScreenUpdating = False xIndex = 0 Untuk Setiap Ws Di Aplikasi. Lembar Kerja Jika Ws. Beri nama ActWsName Kemudian ActRng. Offset(xIndeks, 0). Nilai = "='" & Ws. Nama & "'. " & ActAddress xIndex = xIndex + 1 End If Next Application. Pembaruan Layar = True End Sub
Membalas
0
0
Yakobus
sekitar 6 tahun yang lalu
Komentar ini diminimalkan oleh moderator di situs
Bagaimana cara mereferensikan sel tertentu dibandingkan dengan sel yang telah saya pilih di buku kerja lain?
Membalas
0
0
Abhisek Mishra
sekitar 6 tahun yang lalu
Komentar ini diminimalkan oleh moderator di situs
hai Terima kasih atas pengkodean Anda, ini sangat bagus dan sangat membantu kami. Dalam kode VBA di atas bagaimana saya bisa mengecualikan lembar kerja yang tersembunyi? . Jadi saya telah membuat beberapa lembar kerja dan menyembunyikannya dan dari mana saya mengekstrak data yang berbeda dari lembar tersembunyi ini. Jadi tolong beri tahu saya pengkodeannya agar tidak mengambil nilai sel dari lembar kerja tersembunyi. Pekerjaan Anda sangat dihargai dan terima kasih atas dukungan Anda yang luar biasa. Salam Abhisek
Membalas
0
0
Jordan Hynes
sekitar 6 tahun yang lalu
Komentar ini diminimalkan oleh moderator di situs
Hai, saya baru saja mencoba kode vba Anda dan masuk akal bagaimana menggunakannya namun pada spreadsheet master saya agar ini berfungsi, ini sedikit lebih rumit pertama saya ingin ketika saya menjalankan kode agar hasilnya terisi secara horizontal kedua sel id
Membalas
0
0
Li
sekitar 5 tahun yang lalu
Komentar ini diminimalkan oleh moderator di situs
Apakah ada versi MACbook dari alat ini?
Membalas
0
0
Mohammad Abd Elhady
sekitar 5 tahun yang lalu
Komentar ini diminimalkan oleh moderator di situs
Dears Mohon diperhatikan bahwa saya menggunakan Referensi Sel Yang Sama Dari Beberapa Lembar Kerja Dengan Kode VBA dan berfungsi dengan baik, tetapi saya perlu memodifikasi sesuatu dalam kode dan saya memerlukan bantuan Anda untuk membuat nilai kembali di lembar master horizontal, bukan vertikal. Salam Hormat, Mohamed AbdELhady
Membalas
0
0
Mohammad Abd Elhady
sekitar 5 tahun yang lalu
Komentar ini diminimalkan oleh moderator di situs
Dears, Mohon dicatat bahwa saya menggunakan Referensi Sel Yang Sama Dari Beberapa Lembar Kerja Dengan Kode VBA dan itu bekerja dengan saya dengan baik, namun saya membutuhkan bantuan Anda dalam cara saya kode ini mengambil nilai-nilai dalam lembar master horizontal bukan vertikal. Salam Hormat,. -)
Membalas
0
0
Luzardo
sekitar 5 tahun yang lalu
Komentar ini diminimalkan oleh moderator di situs
Hai, terima kasih ini berfungsi dengan buku kerja di pekerjaan saya. Terima kasih Salam Luzardo
Membalas
0
0
Becky
sekitar 5 tahun yang lalu
Komentar ini diminimalkan oleh moderator di situs
Saya memiliki spreadsheet dengan semua bulan. Saya ingin beberapa sel dipindahkan dari bulan pertama ke bulan berikutnya (mis. e. , sel B12 di bulan Januari dapat ditautkan ke bulan Februari - Desember sel B12). Bagaimana saya melakukan ini?
Membalas
0
0
Sam
sekitar 5 tahun yang lalu
Komentar ini diminimalkan oleh moderator di situs
Halo,
Saya memiliki daftar siswa di kolom A dan sekolah yang mereka hadiri di G. Saya ingin lembar kerja mengisi semua siswa yang bersekolah di sekolah X dari lembar kerja yang tersedia. Bagaimana saya melakukan ini?
Membalas
0
0
Harshal Patil
sekitar 4 tahun yang lalu
Komentar ini diminimalkan oleh moderator di situs
Saya telah membuat lembar stok dan daftar stok pembuka ada di sana dan saya membuat 500 lembar untuk item yang berbeda jadi bagaimana cara memasukkan stok pembuka di semua lembar sekaligus