Cara menulis rumus Excel yang akan mencari nilai di lembar berbeda berdasarkan produk mana yang dipilih. Cara menarik data dari lembar kerja yang berbeda untuk setiap produk.
Menonton video
- Rhonda dari Cincinnati: Bagaimana cara mencari baris dan lembar kerja?
- Gunakan kolom Tanggal untuk mencari tahu lembar mana yang akan digunakan
- Langkah 1: Buat VLOOKUP biasa dan gunakan FORMULATEXT untuk melihat seperti apa tampilan referensi itu
- Langkah 2: Gunakan fungsi Concatenation dan TEXT untuk membuat referensi yang terlihat seperti referensi larik tabel dalam rumus
- Langkah 3: Bangun VLOOKUP Anda, tetapi untuk larik tabel, gunakan TIDAK LANGSUNG (hasil dari langkah 2)
- Langkah 4: Salin rumus dari Langkah 2 (tanpa tanda sama dengan) dan tempelkan ke rumus dari langkah 3
Transkrip Video
Belajar Excel dari Podcast, Episode 2173: Cari Lembar dan Baris.
Hai, selamat datang kembali di netcast, saya Bill Jelen. Saya berada di Cincinnati minggu lalu, dan Rhonda di Cincinnati memiliki pertanyaan besar ini. Rhonda perlu mencari produk ini tetapi tabel Pencarian berbeda, tergantung pada bulan apa. Lihat, kami memiliki tabel Cari yang berbeda di sini untuk Januari hingga April, dan mungkin, untuk bulan-bulan lainnya juga. Baik.
Jadi saya akan menggunakan INDIRECT untuk menyelesaikan ini, tetapi sebelum saya melakukan INDIRECT, saya selalu merasa lebih mudah hanya dengan melakukan VLOOKUP langsung. Jadi, kita bisa melihat seperti apa bentuknya nanti. Jadi kami mencari A1 di tabel ini pada bulan Januari, dan kami ingin kolom ketujuh, koma FALSE, semua VLOOKUP diakhiri dengan FALSE. (= VLOOKUP (A2, 'Jan 2018'! A1: G13,7, FALSE)). Baik.
Dan, itu jawaban yang benar. Yang sangat menarik bagi saya adalah mendapatkan teks rumusnya. Jadi ini menunjukkan kepada saya seperti apa rumus itu nantinya. Dan seluruh trik di sini adalah, saya mencoba membuat kolom Pembantu yang akan terlihat persis seperti Referensi ini, bukan? Jadi bagian ini-- hanya bagian itu saja. Baik. Jadi kolom Pembantu ini harus terlihat seperti itu. Dan hal pertama yang ingin saya lakukan, adalah kita akan menggunakan fungsi TEXT dari Date-- fungsi TEXT dari tanggal-- untuk mendapatkan mmm, spasi, yyyy-- jadi, "mmm yyyy" seperti itu- - yang seharusnya kembali, untuk setiap sel, bulan apa yang kita cari. Sekarang, saya perlu membungkusnya dengan apostrof. Jika tidak ada nama spasi di sana, saya tidak membutuhkan apostrof, tetapi saya membutuhkannya. Jadi kita akan melakukan Concactenate di depan,apostrof, jadi itulah kutipan-- apostrof, kutipan-- ampersand, dan kemudian di sini, kutipan lain, apostrof, dan tanda seru, A1: G13, kutipan penutup, ampersand di sana.
Baik. Jadi sekarang, apa yang telah berhasil kami lakukan di sini di kolom Helper, adalah kami membuat sesuatu yang terlihat persis seperti larik tabel di VLOOKUP. Baik. Jadi jawaban kita, maka, itu akan menjadi = VLOOKUP dari sel ini, A2, koma, dan kemudian ketika kita sampai ke larik tabel, kita akan menggunakan INDIRECT. TIDAK LANGSUNG adalah fungsi keren ini yang mengatakan, "Hei, ini sel yang terlihat seperti referensi sel, dan saya ingin Anda pergi ke F2, ambil benda yang terlihat seperti referensi sel, lalu gunakan apa pun yang ada di referensi sel itu sebagai jawabannya, "koma, 7, koma, FALSE," seperti itu. (= VLOOKUP (A2, 'Jan 2018'! A1: G13,7, FALSE)) Oke, jadi sekarang, dengan cepat, kami memilih berbeda Cari tabel dan mengembalikan nilai bergantung pada apakah bulan April atau apa.
Baik. Jadi mari kita ambil 4/24 ini, saya akan mengubahnya menjadi 2/17/2018, seperti itu, dan kita akan melihat bahwa 403 berubah menjadi 203-- sempurna. Ini bekerja. Baik. Sekarang, kita tidak memerlukan dua kolom ini di sini, tentu saja, dan sungguh, jika Anda memikirkannya, kita tidak membutuhkan keseluruhan kolom ini. Kita bisa mengambil semuanya, kecuali tanda sama dengan, Ctrl + C untuk menyalinnya, dan kemudian di mana kita memiliki D2, tempel saja, seperti itu. Sempurna. Klik dua kali untuk menembak jatuh dan menyingkirkan ini. Ada jawaban kami. Baiklah, kita akan menggunakan teks rumus kita di sini, hanya untuk melihat jawaban akhir itu.
Saya harus memberi tahu Anda, jika saya harus membuat formula itu dari awal, saya tidak akan melakukannya. Saya tidak akan bisa melakukannya. Saya pasti akan mengacaukannya. Itulah mengapa saya selalu membangunnya dalam langkah-langkah-- Saya mencari tahu seperti apa rumusnya dan kemudian Concactenate the Helper column yang akan digunakan di dalam INDIRECT, dan akhirnya, mungkin di sini pada akhirnya, kembalikan semuanya.
Hai, banyak tip seperti tip ini di buku, Power Excel dengan. Ini adalah Edisi 2017 dengan misteri 617 Excel terpecahkan. Klik "Saya" di pojok kanan atas untuk informasi lebih lanjut.
Baiklah, penutup dari Episode ini: Rhonda dari Cincinnati-- bagaimana mencari baris dan lembar kerja. Saya menggunakan kolom Tanggal untuk mencari tahu lembar mana yang akan digunakan; jadi saya membuat VLOOKUP biasa dan menggunakan teks rumus untuk melihat seperti apa referensi itu; lalu buat sesuatu yang terlihat seperti referensi itu menggunakan fungsi teks untuk mengonversi Tanggal menjadi Bulan dan Tahun; gunakan Concactenation untuk membangun sesuatu yang terlihat seperti referensi; dan kemudian saat Anda membangun VLOOKUP untuk argumen kedua, larik tabel, gunakan INDIRECT; dan kemudian tunjukkan hasil dari langkah 2; lalu langkah keempat opsional di sana, salin rumus dari langkah 2, tanpa tanda sama dengan, dan tempelkan ke rumus dari Langkah 3, sehingga Anda mendapatkan satu rumus.
Saya ingin berterima kasih kepada Rhonda karena telah hadir di seminar saya di Cincinnati, dan saya ingin berterima kasih karena Anda telah mampir. Sampai jumpa di lain waktu untuk netcast lain dari.
Unduh berkas
Unduh file contoh di sini: Podcast2173.xlsm
