Loncat ke daftar isi utama

Bagaimana cara mereferensikan sel yang sama dari beberapa lembar kerja di Excel?

Misalkan Anda memiliki buku kerja yang berisi beberapa lembar kerja, dan sekarang Anda ingin mengekstrak data dalam sel yang sama di beberapa lembar kerja ke dalam satu lembar kerja master. Misalnya, tarik sel B8 dari Sheet1, Sheet2, Sheet3, Sheet4… ke dalam master sheet seperti gambar berikut yang ditampilkan. Bagaimana Anda bisa menyelesaikan tugas ini dengan cepat dan mudah di Excel?

Referensi sel yang sama dari beberapa lembar kerja ke dalam satu lembar master dengan rumus

Referensi sel yang sama dari beberapa lembar kerja ke dalam satu lembar master dengan kode VBA

Referensi sel yang sama dari beberapa lembar kerja menjadi satu lembar master dengan fitur luar biasa


Referensi sel yang sama dari beberapa lembar kerja ke dalam satu lembar master dengan rumus

Jika nama lembar kerja Anda adalah nama lembar default, seperti Sheet1, Sheet2, Sheet3 ..., Anda dapat menggunakan rumus untuk menangani pekerjaan ini dengan cepat di Excel.

1. Pertama, Anda harus membuat daftar nomor pembantu, 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 data, lalu seret gagang isian ke sel yang ingin Anda gunakan rumus ini, dan semua nilai sel yang sama dari beberapa lembar kerja telah diekstraksi, lihat tangkapan layar:

=INDIRECT("'Sheet" & E2 & "'!$B$8")

Note: Dalam 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 berfungsi dengan baik jika nama sheet adalah Sheet1, Sheet2, Sheet3 ...


Referensi sel yang sama dari beberapa lembar kerja ke dalam satu lembar master

Kutools untuk Excel mendukung fitur yang kuat-Merujuk ke Lembar Kerja secara dinamis yang dapat membantu Anda mereferensikan nilai sel yang sama di beberapa lembar kerja ke dalam lembar master. Silakan lihat demo di bawah ini.    Klik untuk mengunduh Kutools for Excel!


Referensi sel yang sama dari beberapa lembar kerja ke dalam satu lembar master dengan kode VBA

Jika ada lusinan lembar kerja yang nama sheetnya merupakan berbagai konten dan bukan Sheet1, sheet2, dalam hal ini, kode VBA berikut dapat membantu Anda mengisi referensi sel yang sama dari beberapa lembar kerja menjadi 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 ALT + F11 kunci, dan itu membuka Jendela Microsoft Visual Basic for Applications.

3. Klik Menyisipkan > Modul, dan tempel 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 Sub

4. Lalu tekan F5 kunci untuk menjalankan kode ini, dan semua nilai sel B8 dari lembar kerja lain telah ditarik ke dalam 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 yang ditentukan, 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 untuk Excel, Dengan yang Merujuk ke Lembar Kerja secara dinamis utilitas, Anda dapat mengisi referensi sel dari beberapa lembar kerja di Excel dengan mudah.

Tip:Untuk menerapkan ini Merujuk ke Lembar Kerja secara dinamis fitur, pertama, Anda harus mengunduh Kutools untuk Excel, lalu terapkan fitur dengan cepat dan mudah.

Setelah menginstal Kutools untuk Excel, lakukan seperti ini:

1. Klik sel B8 dalam lembar Master yang ingin Anda ekstrak dari lembar kerja lain.

2. Kemudian klik Kutools > More > Merujuk ke Lembar Kerja secara dinamis, lihat tangkapan layar:

3. di Isi Referensi Lembar Kerja kotak dialog, pilih Isi secara vertikal sel demi sel dari Isi pesanan, dan klik kunci kecil di samping kotak teks rumus, dan kunci abu-abu akan menjadi kunci kuning, artinya rumus dan referensi sel telah dikunci, lalu Anda dapat mengklik sel mana saja untuk mengekstrak referensi sel B8 dari lembar kerja lain, di contoh ini saya akan mengklik sel B2. Kemudian, periksa lembar kerja yang ingin Anda ekstrak referensi selnya. Lihat tangkapan layar:

Note: Jika Anda tidak mengunci kunci kecil ini, referensi sel yang Anda klik akan diubah dengan sel yang dipilih.

4. Lalu klik Isi Rentang tombol, dan nilai 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 for Excel dan uji coba gratis Sekarang!


Artikel lainnya:

  • 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 mengatasi 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 di dalam buku kerja? Misalnya, saya memiliki empat lembar kerja yang mencantumkan beberapa nama berisi duplikat di kolom A, dan sekarang, saya ingin mengekstrak semua nama unik dari lembar ini ke dalam daftar baru, bagaimana saya bisa menyelesaikan pekerjaan ini di Excel?
  • Hitung Nilai Spesifik 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, tetapi, pernahkah mencoba memasukkan gambar yang sama ke semua lembar kerja buku kerja Anda? Artikel ini, saya akan memperkenalkan metode yang berguna bagi Anda untuk menyelesaikan pekerjaan ini.

Alat Produktivitas Kantor Terbaik

🤖 Kutools AI Ajudan: Merevolusi analisis data berdasarkan: Eksekusi Cerdas   |  Hasilkan Kode  |  Buat Rumus Khusus  |  Analisis Data dan Hasilkan Grafik  |  Aktifkan Fungsi Kutools...
Fitur Populer: Temukan, Sorot, atau Identifikasi Duplikat   |  Hapus Baris Kosong   |  Gabungkan Kolom atau Sel tanpa Kehilangan Data   |   Putaran tanpa Formula ...
Pencarian Super: VLookup Beberapa Kriteria    VLookup Nilai Berganda  |   VLookup di Beberapa Lembar   |   Pencarian Fuzzy ....
Daftar Drop-down Lanjutan: Buat Daftar Drop Down dengan Cepat   |  Daftar Drop Down yang Bergantung   |  Multi-pilih Drop Down List ....
Manajer Kolom: Tambahkan Jumlah Kolom Tertentu  |  Pindahkan Kolom  |  Alihkan Status Visibilitas Kolom Tersembunyi  |  Bandingkan Rentang & Kolom ...
Fitur Unggulan: Fokus Kisi   |  Tampilan Desain   |   Bar Formula Besar    Manajer Buku Kerja & Lembar   |  Perpustakaan Sumberdaya (Teks otomatis)   |  Pemetik tanggal   |  Gabungkan Lembar Kerja   |  Enkripsi/Dekripsi Sel    Kirim Email berdasarkan Daftar   |  Filter Super   |   Filter Khusus (filter tebal/miring/coret...) ...
15 Perangkat Teratas12 Teks Tools (Tambahkan Teks, Hapus Karakter, ...)   |   50 + Grafik jenis (Gantt Chart, ...)   |   40+ Praktis Rumus (Hitung usia berdasarkan ulang tahun, ...)   |   19 Insersi Tools (Masukkan Kode QR, Sisipkan Gambar dari Jalur, ...)   |   12 Konversi Tools (Angka ke Kata, Konversi Mata Uang, ...)   |   7 Gabungkan & Pisahkan Tools (Lanjutan Gabungkan Baris, Pisahkan Sel, ...)   |   ... dan banyak lagi

Tingkatkan Keterampilan Excel Anda dengan Kutools for Excel, dan Rasakan Efisiensi yang Belum Pernah Ada Sebelumnya. Kutools for Excel Menawarkan Lebih dari 300 Fitur Lanjutan untuk Meningkatkan Produktivitas dan Menghemat Waktu.  Klik Di Sini untuk Mendapatkan Fitur yang Paling Anda Butuhkan...

Deskripsi Produk


Tab Office Membawa antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Jauh Lebih Mudah

  • Aktifkan pengeditan dan pembacaan tab 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 sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!
Comments (13)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I have create a stock sheet and opening stock list is there and i create 500 sheets for different items so how to put opening stock in all sheets at a time
This comment was minimized by the moderator on the site
Hello,


I have a list of students in column A and the schools they attend in G. I want a worksheet to populate all students who attend X school from the available worksheets. How would I do this? Thanks in advance!
This comment was minimized by the moderator on the site
I have a spreadsheet with all the months. I want some of the cells to carry over from the first month to the proceeding other (i.e., cell B12 in January can be linked to February - December cell B12). How would I do this?
This comment was minimized by the moderator on the site
Hi, thanks it works with a workbook at my job. Thanks Best regards Luzardo
This comment was minimized by the moderator on the site
Dears, Kindly note that I used Reference Same Cell From Multiple Worksheets With VBA Code and it works with me properly, however I need your help in how to me this code retrieve the values in the master sheet horizontal instead of vertical. Best Regards, :-)
This comment was minimized by the moderator on the site
Dears Kindly note that I used Reference Same Cell From Multiple Worksheets With VBA Code and it works properly, but I need to modify something in the code and I need your help to make the values return in the master sheet horizontal instead of vertical. Best Regards, Mohamed AbdELhady
This comment was minimized by the moderator on the site
Is there a MAcbook version of this tool?
This comment was minimized by the moderator on the site
Hi I have just tried your vba code and it makes sense how to use it however on my master spreadsheet for this to work it is slightly more complicated firstly i'd want when i run the code for the results to populate horizontally secondly the cell id use as a reference like B6 is used doesnt correpsond on the other spreadsheets - so to explain the value id want to find appears in the same cell on each sheet but this doesnt correspond onto the master spreadsheet hope this makes sense please help!!
This comment was minimized by the moderator on the site
hi Thank you for you coding its really great and so much helpful for us. In this above VBA code how can I exclude the hidden worksheet? As I have different customer whom I billed them monthly using a format. So I have created few worksheet and hide them and from where I extract the different data from this hidden sheet. So please let me know the coding so that it doesn't pick the cell value from hidden worksheet. Your work is highly appreciated and thak you for your wonderful support! Regards Abhishek
This comment was minimized by the moderator on the site
How would I reference a specific cell as opposed to the cell that I have selected in another workbook?
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations