Note: The other languages of the website are Google-translated. Back to English

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 for Excel Memecahkan Sebagian Besar Masalah Anda, dan Meningkatkan Produktivitas Anda hingga 80%

  • Reuse: Masukkan dengan cepat rumus, bagan yang kompleks dan apa pun yang pernah Anda gunakan sebelumnya; Enkripsi Sel dengan kata sandi; Buat Milis dan mengirim email ...
  • Bilah Formula Super (dengan mudah mengedit beberapa baris teks dan rumus); Membaca Tata Letak (membaca dan mengedit sel dalam jumlah besar dengan mudah); Tempel ke Rentang yang Difilter...
  • Gabungkan Sel / Baris / Kolom tanpa kehilangan Data; Pisahkan Konten Sel; Gabungkan Baris / Kolom Duplikat... Mencegah Sel Duplikat; Bandingkan Rentang...
  • Pilih Duplikat atau Unik Baris; Pilih Baris Kosong (semua sel kosong); Temukan Super dan Temukan Fuzzy di Banyak Buku Kerja; Pilih Acak ...
  • Salinan Tepat Beberapa Sel tanpa mengubah referensi rumus; Buat Referensi Otomatis ke Beberapa Lembar; Sisipkan Poin, Kotak Centang, dan lainnya ...
  • Ekstrak Teks, Tambahkan Teks, Hapus berdasarkan Posisi, Hapus Space; Membuat dan Mencetak Subtotal Paging; Konversi Konten Antar Sel dan Komentar...
  • Filter Super (simpan dan terapkan skema filter ke sheet lain); Penyortiran Lanjutan menurut bulan / minggu / hari, frekuensi dan lainnya; Filter Khusus dengan huruf tebal, miring ...
  • Gabungkan Workbooks dan WorkSheets; Gabungkan Tabel berdasarkan kolom kunci; Pisahkan Data menjadi Beberapa Lembar; Konversi Batch xls, xlsx dan 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.
tab kte 201905

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!
officetab bawah
Urutkan komentar berdasarkan
komentar (13)
Belum ada peringkat. Jadilah yang pertama memberi peringkat!
Komentar ini diminimalkan oleh moderator di situs
makasih banyak gan sangat bermanfaat :roll:
Komentar ini diminimalkan oleh moderator di situs
Dan bagaimana jika saya ingin menentukan sel yang tidak mengkliknya tetapi menulisnya? bagaimana kodenya akan berubah? terima kasih!
Komentar ini diminimalkan oleh moderator di situs
Anda melewatkan tanda kutip tunggal pada baris 16 dari kode VBA. Kode ini benar: Sub AutoFillSheetNames() 'Update 20131202 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 Untuk Setiap Ws Dalam Application.Worksheets Jika Ws.Name ActWsName Kemudian ActRng.Offset(xIndex, 0).Value = "='" & Ws.Name & "'!" & ActAddress xIndex = xIndex + 1 End If Next Application.ScreenUpdating = True End Sub
Komentar ini diminimalkan oleh moderator di situs
Bagaimana saya mereferensikan sel tertentu yang bertentangan dengan sel yang telah saya pilih di buku kerja lain?
Komentar ini diminimalkan oleh moderator di situs
hai Terima kasih atas pengkodeannya yang sangat bagus dan sangat membantu kami. Dalam kode VBA di atas ini, bagaimana saya bisa mengecualikan lembar kerja tersembunyi? Karena saya memiliki pelanggan berbeda yang saya tagih setiap bulan menggunakan format. 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 sehingga tidak memilih nilai sel dari lembar kerja tersembunyi. Pekerjaan Anda sangat dihargai dan terima kasih atas dukungan Anda yang luar biasa! Salam Abhishek
Komentar ini diminimalkan oleh moderator di situs
Hai, saya baru saja mencoba kode vba Anda dan masuk akal bagaimana cara menggunakannya, namun pada master spreadsheet saya agar ini berfungsi, ini sedikit lebih rumit, pertama saya ingin ketika saya menjalankan kode agar hasilnya terisi secara horizontal, kedua id sel gunakan sebagai referensi seperti B6 digunakan tidak sesuai dengan spreadsheet lain - jadi untuk menjelaskan nilai yang ingin ditemukan id muncul di sel yang sama pada setiap lembar tetapi ini tidak sesuai dengan master spreadsheet harap ini masuk akal tolong bantu !!
Komentar ini diminimalkan oleh moderator di situs
Apakah ada versi MACbook dari alat ini?
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 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
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 berfungsi dengan baik dengan saya, namun saya butuh bantuan Anda tentang cara saya mendapatkan kode ini mengambil nilai di lembar master horizontal, bukan vertikal. Salam Hormat, :-)
Komentar ini diminimalkan oleh moderator di situs
Hai, terima kasih ini berfungsi dengan buku kerja di pekerjaan saya. Terima kasih Salam hormat Luzardo
Komentar ini diminimalkan oleh moderator di situs
Saya memiliki spreadsheet dengan semua bulan. Saya ingin beberapa sel terbawa dari bulan pertama ke bulan berikutnya (yaitu, sel B12 di bulan Januari dapat dihubungkan ke bulan Februari - Desember sel B12). Bagaimana saya melakukan ini?
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 untuk mengisi semua siswa yang menghadiri sekolah X dari lembar kerja yang tersedia. Bagaimana saya melakukan ini? Terima kasih sebelumnya!
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
Belum ada komentar yang diposting di sini
Tinggalkan komentar anda
Posting sebagai Tamu
×
Beri peringkat pos ini:
0   Karakter
Lokasi yang Disarankan

Ikuti kami

Hak Cipta © 2009 - www.extendoffice.com. | Seluruh hak cipta. Dipersembahkan oleh ExtendOffice. | Peta Situs
Microsoft dan logo Office adalah merek dagang atau merek dagang terdaftar dari Microsoft Corporation di Amerika Serikat dan / atau negara lain.
Dilindungi oleh Sectigo SSL