Loncat ke daftar isi utama

Bagaimana cara mencari / menemukan nilai di buku kerja lain?

Artikel ini membahas tentang cara mencari nilai dan mengembalikan data dari buku kerja lain, dan cara menemukan / mencari nilai dari buku kerja lain. Di sini saya akan memperkenalkan tiga solusi secara rinci.


Vlookup data dan mengembalikan nilai dari buku kerja lain di Excel

Misalnya Anda membuat tabel Pembelian Buah di Excel, dan sekarang Anda perlu mencari buah dari buku kerja lain dan mengembalikan harga yang sesuai seperti gambar di bawah ini. Di sini saya akan memandu Anda menyelesaikannya dengan fungsi VLOOKKUP di Excel.

1. Buka kedua workbook yang akan Anda vlookup nilainya dan kembalikan nilainya.

2. Pilih sel kosong yang akan Anda kembalikan harganya, ketikkan rumusnya = VLOOKUP (B2, [Price.xlsx] Sheet1! $ A $ 1: $ B $ 24,2, FALSE) ke dalamnya, lalu seret Fill Handle untuk menerapkan rumus ini ke kisaran yang Anda butuhkan.

Catatan:
(1) Dalam rumus di atas, B2 adalah buah yang akan Anda cari dari buku kerja lain, Price.xlsx menunjukkan nama file buku kerja yang akan Anda cari, Sheet1 berarti nama lembar yang akan Anda cari, dan A $ 1 : $ B $ 24 adalah kisaran yang akan Anda cari. Anda dapat mengubahnya sesuai kebutuhan.
(2) Setelah menutup buku kerja yang Anda cari, rumus akan diperbarui secara otomatis = VLOOKUP (B2, 'W: \ test \ [Price.xlsx] Sheet1'! $ A $ 1: $ B $ 24,2, SALAH), W: \ test \ adalah jalur penyimpanan buku kerja yang telah Anda cari.

Sejauh ini, semua harga telah kembali dengan benar seperti screenshot kiri yang ditampilkan. Dan harga ini akan diperbarui secara otomatis jika buku kerja asli yang Anda cari dari perubahan.

perhatikan pita Formula terlalu rumit untuk diingat? Simpan rumus sebagai entri Teks Otomatis untuk digunakan kembali hanya dengan satu klik di masa mendatang!
Baca lebih banyak…     Free trial

Vlookup data dan mengembalikan nilai dari buku kerja tertutup lainnya dengan VBA

Mungkin sampah bingung untuk mengkonfigurasi jalur penyimpanan, nama file, dan lembar kerja di fungsi VLOOKUP. Metode ini akan memperkenalkan VBA untuk menyelesaikannya dengan mudah.

1. tekan lain + F11 kunci untuk membuka jendela Microsoft Visual Basic for Applications.

2. Klik Menyisipkan > Modul, lalu tempelkan kode VBA di bawah ini ke jendela modul pembuka.

VBA: Vlookup data dan mengembalikan nilai dari buku kerja tertutup lainnya

Private Function GetColumn(Num As Integer) As String
If Num <= 26 Then
GetColumn = Chr(Num + 64)
Else
GetColumn = Chr((Num - 1) \ 26 + 64) & Chr((Num - 1) Mod 26 + 65)
End If
End Function
Sub FindValue()
Dim xAddress As String
Dim xString As String
Dim xFileName As Variant
Dim xUserRange As Range
Dim xRg As Range
Dim xFCell As Range
Dim xSourceSh As Worksheet
Dim xSourceWb As Workbook
On Error Resume Next
xAddress = Application.ActiveWindow.RangeSelection.Address
Set xUserRange = Application.InputBox("Lookup values :", "Kutools for Excel", xAddress, Type:=8)
If Err <> 0 Then Exit Sub
Set xUserRange = Application.Intersect(xUserRange, Application.ActiveSheet.UsedRange)
xFileName = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx", 1, "Select a Workbook")
If xFileName = False Then Exit Sub
Application.ScreenUpdating = False
Set xSourceWb = Workbooks.Open(xFileName)
Set xSourceSh = xSourceWb.Worksheets.Item(1)
xString = "='" & xSourceWb.Path & Application.PathSeparator & _
"[" & xSourceWb.Name & "]" & xSourceSh.Name & "'!$"
For Each xRg In xUserRange
Set xFCell = xSourceSh.Cells.Find(xRg.Value, , xlValues, xlWhole, , , False)
If Not (xFCell Is Nothing) Then
xRg.Offset(0, 2).Formula = xString & GetColumn(xFCell.Column + 1) & "$" & xFCell.Row
End If
Next
xSourceWb.Close False
Application.ScreenUpdating = True
End Sub
Note: VBA ini akan mengembalikan nilai dalam kolom yang berada 2 kolom di belakang kolom yang dipilih. Misalnya saya memilih kolom B saat menerapkan VBA ini, nilai akan kembali di Kolom D. Jika Anda perlu mengubah kolom tujuan, silakan cari tahu kodenya xRg.Offset (0, 2) .Formula = xString & GetColumn (xFCell.Column + 1) & "$" & xFCell.Row , dan ganti 2 ke nomor lain yang Anda butuhkan.

3. tekan F5 atau klik Run tombol untuk menjalankan VBA ini.

4. Pada kotak dialog pembuka, harap tentukan kisaran data yang akan Anda cari, dan klik OK .

5. Sekarang pilih workbook di mana Anda akan mencari nilai-nilai di kotak dialog Select a Workbook, dan klik Open .

Sekarang semua nilai yang dipilih dicari di buku kerja tertutup yang ditentukan, dan nilai yang sesuai dikembalikan pada kolom yang ditentukan. Lihat tangkapan layar:

Alat Produktivitas Kantor Terbaik

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

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!
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi this works great thanks! Would it be at all possible to show me how i would change the code if i have the workbook open that i would like to lookup the data in?
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations