Excel vba dapatkan nilai sel dari beberapa lembar

Question

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?

Table of Contents Show

Alat Produktivitas Kantor Terbaik
Bagaimana cara menarik data dari beberapa lembar kerja di Excel menggunakan VBA?
Bagaimana cara mendapatkan nilai sel dari lembar lain di VBA?
Bagaimana cara menarik data dari beberapa lembar kerja di Excel?
Bagaimana cara menarik data dari beberapa lembar kerja di Excel berdasarkan kriteria?

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 Sub

4. 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