Untuk artikel ini, kami mengambil skenario pemodelan yang sudah dikenal dan memperluasnya (lihat file Excel yang dapat diunduh untuk contoh model)
Bayangkan Anda memiliki kumpulan data yang mirip dengan yang ditampilkan di tangkapan layar "Contoh Kumpulan Data", di bawah
Contoh kumpulan data
Tugas Anda sederhana. Untuk bulan tertentu dan item tertentu, kembalikan nilai yang sesuai (yang disebut pencarian dua arah). Saya akan mengabaikan XLOOKUP, karena tidak ada di semua versi Excel saat ini, jadi saya akan menggunakan fungsi INDEX dan MATCH sebagai gantinya. Sebagai pengingat
INDEKS
Intinya, INDEX(array, row_number, [column_number]) mengembalikan nilai atau referensi ke nilai dari dalam tabel atau rentang (daftar)
Misalnya, INDEX({7,8,9,10,11,12},3) mengembalikan item ketiga dalam daftar {7,8,9,10,11,12},yaitu, 9. Ini bisa menjadi kisaran. INDEKS(A1. A10,5) memberikan nilai di sel A5, dll
INDEX juga dapat bekerja dalam dua dimensi (maka referensi column_number) — lihat tangkapan layar “Table Array,” di bawah
Susunan tabel
INDEKS(F11. L21,4,5) mengembalikan nilai di baris keempat, kolom kelima dari larik tabel F11. L21 (jelas 26 di tangkapan layar "Table Array")
COCOK
MATCH(lookup_value, lookup_vector, [match_type]) mengembalikan posisi relatif item dalam vektor baris atau kolom yang (kurang-lebih) cocok dengan nilai yang ditentukan. Ini tidak peka huruf besar-kecil
Argumen ketiga, match_type, tidak harus dimasukkan, tetapi untuk banyak situasi, saya sangat menyarankan agar itu ditentukan. Ini memungkinkan satu dari tiga nilai
• match_type 1 [default jika dihilangkan]. Menemukan nilai terbesar kurang dari atau sama dengan lookup_value — tetapi lookup_vector harus dalam urutan menaik yang ketat, sehingga membatasi fleksibilitas
• jenis_cocokan 0. Mungkin pengaturan yang paling berguna, MATCH akan menemukan posisi nilai pertama yang sama persis dengan lookup_value. lookup_vector dapat memiliki data dalam urutan apa pun dan bahkan memungkinkan duplikat
• jenis pencocokan -1. Menemukan nilai terkecil yang lebih besar dari atau sama dengan lookup_value — tetapi lookup_vector harus dalam urutan menurun yang ketat, lagi-lagi membatasi fleksibilitas. Saat menggunakan MATCH, jika tidak ada kecocokan (perkiraan), #N/A dikembalikan (ini juga dapat terjadi jika data tidak diurutkan dengan benar bergantung pada jenis_pencocokan)
MATCH cukup mudah digunakan
Pada tangkapan layar “Contoh Fungsi MATCH,” di bawah, MATCH(“d”,F12. F22,0) memberikan nilai enam [6], menjadi posisi relatif dari "d" pertama dalam rentang. Perhatikan bahwa memiliki match_type 0 di sini penting. Data berisi duplikat dan tidak diurutkan secara alfanumerik. Akibatnya, match_types 1 dan -1 akan memberikan jawaban yang salah. 7 dan #N/A, masing-masing
Contoh fungsi MATCH
PERTANDINGAN INDEKS
Sementara fungsi yang berguna dalam dirinya sendiri, gabungan INDEX dan MATCH membentuk kemitraan yang sangat serbaguna. Pertimbangkan situasi umum yang ditunjukkan pada tangkapan layar “Ringkasan Neraca,” di bawah ini
Ringkasan neraca
COCOK(1,$J$18. $S$18,0) sama dengan lima [5]; . Tapi kita bisa melakukan lebih baik dari itu
INDEKS($J$12. $S$12,5) sama dengan 2020, jadi gabungkan kedua fungsi tersebut
INDEKS($J$12. $S$12,COCOK(1,$J$18. $S$18,0))
sama dengan 2020 dalam satu langkah. Perhatikan betapa fleksibelnya kombinasi ini sebenarnya. Kami tidak perlu menentukan urutan untuk rentang pencarian;
Namun, pendekatan ini hanya mempertimbangkan satu kriteria (dalam contoh di atas, memastikan kapan ketidakseimbangan pertama terjadi). Apa yang terjadi jika ada lebih dari satu kriteria?
Pertimbangkan data yang diputar; . Di sini, pada dasarnya, keluarannya serupa dengan hasil yang dihasilkan oleh PivotTable. Misalnya, perhatikan ilustrasi di tangkapan layar “Ilustrasi Nilai Pivot,” di bawah
Ilustrasi nilai pivot
Dalam contoh ini, saya telah membuat rumus untuk menentukan biaya iGrapple, sebuah perusahaan fiktif baru. Rumus di sini menggunakan sintaks INDEX(MATCH, MATCH), karena mengidentifikasi baris dan kolom yang relevan dari tabel untuk dikembalikan
Formula
=INDEKS($G$13. $I$19,COCOK($G$24,$F$13. $F$19,0), COCOK($G$25,$G$12. $I$12,0))
mempertimbangkan kisaran $G$13. $I$19 dan pilih baris berdasarkan hasil MATCH($G$24,$F$13. $F$19,0), yang mengidentifikasi baris iGrapple mana yang berada dalam kisaran $F$13. $F$19. Selanjutnya, argumen terakhir memilih kolom berdasarkan MATCH($G$25,$G$12. $I$12,0); . $I$12
Persimpangan baris dan kolom yang dipilih mengembalikan nilai yang diputar
Kembali ke skenario kita
Oleh karena itu, dalam situasi kami (lihat tangkapan layar "Contoh Dataset," di bawah) untuk menentukan nilai, kami cukup menggunakan rumus umum
Contoh kumpulan data
=INDEX(Data_Tabel, MATCH(Item, Daftar_Item, 0), MATCH(Bulan, Daftar_Bulan, 0))
Tetapi bagaimana jika jumlah baris dan kolom ditambah? . Bukan hanya rentang yang perlu diperluas;
Setiap kali kita memiliki rentang yang dapat diperpanjang, kita harus menggunakan Tabel. Saya menyorot tabel dan membuka Sisipkan -> Tabel (CTRL+T) — lihat tangkapan layar “Sisipkan Tabel”, di bawah
Sisipkan Tabel
Ini memanggil dialog Buat Tabel (lihat tangkapan layar, “Kotak Dialog Buat Tabel”, di bawah)
Kotak dialog Buat Tabel
Pastikan Anda telah mencentang "Tabel saya memiliki tajuk", tabel kami diubah menjadi Tabel
Tabel terlihat sedikit berbeda (lihat screenshot “Set Data Contoh yang Diubah”, di bawah)
Dataset contoh yang diubah
Empat hal telah berubah (hanya dua yang terlihat)
1. Dropdown filter telah ditambahkan ke baris pertama. Kami tidak memerlukan ini, jadi ini dapat dihapus dengan menyorot tabel dan mengeklik tombol Filter di bagian Urutkan & Filter dari tab Data pada Pita (ALT+A+T)
2. Sel kiri atas telah ditambahkan teks, yang defaultnya adalah "Kolom1" yang sangat imajinatif. Ini karena semua kolom (bidang) dalam tabel harus diberi nama dan berisi teks, bukan rumus. Ini tidak boleh dihapus, tetapi tetap tidak terlihat dalam contoh saya karena pemformatan sel
3. Baris alternatif diarsir berbeda. Sekali lagi, ini tidak terlihat, karena saya telah menyertakan pemformatan saya sendiri, yang menimpa pemformatan ini. Jika pemformatan saya akan dihapus (mis., ubah gaya sel menjadi "Normal", yaitu, Beranda -> Gaya -> Normal), bayangan ini akan terlihat jelas
4. Di pojok kanan bawah, terlihat segi enam tidak beraturan berwarna hijau, yang menyoroti fakta bahwa tabel dapat direntangkan ke kanan dan ke bawah;
Saya sekarang menamai tabel ini "Data" (cukup beri nama di Nama Tabel. bagian grup Properti di tab Desain Tabel di Pita). Kemudian, pada lembar terpisah saya akan memanggil "Data Pencarian", saya telah membuat dua rumus (lihat tangkapan layar "Data Pencarian," di bawah)
Data pencarian
Sebentar. Saya telah menyatakan bahwa saya tidak akan menggunakan XLOOKUP karena tidak ada di semua versi Excel, dan kemudian saya dengan senang hati menggunakan array dinamis, yang bahkan kurang lazim di Excel
Eh, ya
Anda lihat, apa yang tidak saya lakukan di sini tidak penting, dan array dinamis tidak akan digunakan untuk menghasilkan solusi rumus. Namun, membuat daftar ini menunjukkan konsep kunci yang akan saya gunakan untuk menyusun rumus saya. Izinkan saya untuk menjelaskan
Untuk menghasilkan "Daftar Barang" (kolom F di tangkapan layar "Data Pencarian"), saya cukup menggunakan rumus
=Data[Kolom1]
Ini hanyalah isi kolom Column1 di tabel Data kami. Saya membuat perhitungan hanya dengan menyorot isinya (misalnya, Item 001 hingga 012 dalam contoh kami). Membuat daftar kolom itu sederhana;
=TRANSPOSE(OFFSET(Data[[#Headers],[Column1]],,1,, COUNTA('Contoh Pencarian Dinamis'. 24. 24)-1))
Anda harus mencatat bahwa OFFSET(Data[[#Headers] muncul secara otomatis ketika Anda mengklik header di kolom pertama di Tabel “Data”. Ini hanyalah sintaks untuk merujuk ke sel dalam Tabel, yang dikenal sebagai referensi terstruktur
TRANSPOSE dan COUNTA cukup sederhana untuk dijelaskan
• TRANSPOSE melakukan apa yang tertulis di kaleng. Itu menukar baris dan kolom sehingga baris menjadi kolom dan sebaliknya
• COUNTA menghitung jumlah sel yang tidak kosong dalam suatu rentang
Karena itu,
COUNTA('Contoh Pencarian Dinamis'. 24. 24)-1
menghitung jumlah sel kosong di baris 24 (yang merupakan baris yang berisi judul tabel dalam contoh saya) dan mengurangi satu [1] sehingga efek teks yang diperlukan di kolom pertama tabel (Kolom1) diabaikan. Ini mengandaikan tidak ada teks, nilai, atau rumus lain di baris ini
Fungsi ketiga, OFFSET, mungkin perlu sedikit penjelasan lagi
pengingat OFFSET
OFFSET menggunakan sintaks berikut
OFFSET(Referensi, Baris, Kolom, [Tinggi], [Lebar])
Argumen dalam tanda kurung siku (Tinggi dan Lebar) dapat dihilangkan dari rumus — tetapi akan terbukti bermanfaat dalam artikel ini
Paling umum, OFFSET(Referensi, Baris, Kolom) digunakan untuk memilih referensi Baris baris ke bawah (-Rows akan menjadi Baris baris ke atas) dan kolom Kolom di sebelah kanan (-Kolom akan menjadi kolom Kolom di sebelah kiri) dari Referensi. Sebagai ilustrasi, perhatikan screenshot yang dapat didownload, "Contoh Kumpulan Data 2"
OFFSET(A1,2,3) akan membawa kita dua baris ke bawah dan tiga kolom ke sel D3. Oleh karena itu, OFFSET(A1,2,3) = 16 (lihat tangkapan layar yang dapat diunduh. “Contoh Fungsi OFFSET 1”)
OFFSET(D4,-1,-2) akan membawa kita satu baris ke atas dan dua baris ke kiri ke sel B3. Oleh karena itu, OFFSET(D4,-1,-2) = 14 (lihat screenshot yang dapat didownload "Contoh Fungsi OFFSET 2")
Sekarang mari kita perpanjang rumusnya menjadi OFFSET(D4,-1,-2,-2,3). Itu akan kembali membawa kita ke sel B3, tetapi kemudian kita akan memilih rentang berdasarkan parameter Tinggi dan Lebar. Tingginya adalah dua baris yang naik dari lembaran, dengan baris 3 sebagai alasnya (yaitu, baris 2 dan 3), dan Lebarnya adalah tiga kolom dari kiri ke kanan, dengan kolom B sebagai alasnya (yaitu, kolom B
Oleh karena itu OFFSET(D4,-1,-2,-2,3) akan memilih rentang B2. D3 (lihat screenshot yang dapat didownload "Contoh Fungsi OFFSET 3")
Perhatikan bahwa OFFSET(D4,-1,-2,-2,3) = #VALUE. di beberapa versi Excel yang tidak mendukung array dinamis, karena di versi ini Excel tidak dapat menampilkan matriks dalam satu sel, tetapi masih mengenalinya. Hal ini dapat dilihat sebagai berikut
• JUMLAH(OFFSET(D4,-1,-2,-2,3)) = 72 (yaitu, JUMLAH(B2. D3))
• RATA-RATA(OFFSET(D4,-1,-2,-2,3)) = 12 (yaitu, RATA-RATA(B2. D3))
Kembali ke skenario kita (lagi)
Sekarang setelah fungsi kita dipahami, rumus kedua lebih mudah diikuti (lihat tangkapan layar “Data Pencarian,” di bawah)
Data pencarian
=TRANSPOSE(OFFSET(Data[[#Headers],[Column1]],,1,, COUNTA('Contoh Pencarian Dinamis'. 24. 24)-1))
Elemen,
OFFSET(Data[[#Header],[Kolom1]],,1)
mengembalikan sel satu kolom di sebelah kanan header Column1 (yaitu, Jan). Saya telah menggunakan ungkapan ini sebagai kolom pertama, yang akan selalu diidentifikasi secara konsisten sebagai Kolom1, tetapi semua tajuk lainnya dapat diganti namanya
Perluasan rumus ini
OFFSET(Data[[#Headers],[Column1]],,1,,COUNTA('Contoh Pencarian Dinamis'. 24. 24)-1)
membuat rentang yang dimulai dengan tajuk kolom kedua (Jan) dan memperluasnya menjadi COUNTA('Contoh Pencarian Dinamis'. 24. 24)-1 kolom melintang;
Ini kemudian dibungkus dengan TRANSPOSE
=TRANSPOSE(OFFSET(Data[[#Headers],[Column1]],,1,, COUNTA('Contoh Pencarian Dinamis'. 24. 24)-1))
Karena rumus OFFSET berisi rentang baris, hasilnya akan dinyatakan di seluruh baris;
Kedua rentang ini adalah larik dinamis, sehingga untuk versi Excel yang mendukung larik dinamis, rentang ini dapat dirujuk menggunakan rumus masing-masing =F15# dan =H15# (karena # adalah operator tumpahan dalam Excel dinamis). Dan referensi ini dapat digunakan untuk membuat daftar validasi data, jika Anda menginginkannya
Namun, jika Anda tidak memiliki array dinamis, teruslah membaca. Ini adalah "menyenangkan untuk dimiliki" - bukan elemen penting dari solusi
Rumus pencarian dinamis "mudah" dari sini (lihat tangkapan layar "Asumsi Pencarian," di bawah)
Asumsi pencarian
=OFFSET(Data[[#Headers],[Column1]], MATCH(G12,Data[Column1],0), MATCH(G13,OFFSET(Data[[#Headers],[Column1]],,1,, COUNTA . 24. 24)-1),0))
Daripada menggunakan pendekatan INDEX(MATCH, MATCH) yang dirinci sebelumnya, saya menggunakan OFFSET(MATCH, MATCH), dengan sel dasar menjadi header kolom pertama, Data[[#Headers],[Column1]], yang merupakan struktur sederhana . Dua perhitungan MATCH hanya menggunakan dua daftar yang dihasilkan sebelumnya untuk menemukan perpindahan baris dan kolom yang benar
Kata bijak
Ini adalah masalah umum lainnya di Excel. Terlalu sering, pemodel lupa meletakkan tabel referensi di Tabel Excel. Bagi mereka yang mengelola ini, banyak yang tidak yakin bagaimana mereferensikan sebuah baris secara dinamis. Pendekatan OFFSET(COUNTA) telah tersedia selama bertahun-tahun, tetapi hanya sedikit yang pernah menggunakan kombinasi fungsi ini
Cobalah
Liam Bastick, FCMA, CGMA, FCA, adalah direktur SumProduct, sebuah konsultan global yang berspesialisasi dalam pelatihan Excel. Dia juga seorang MVP Excel (sebagaimana ditunjuk oleh Microsoft) dan penulis Pengantar Pemodelan Keuangan dan Pemodelan Keuangan Berkelanjutan. Kirimkan ide untuk artikel mendatang terkait Excel kepadanya di liam. bastick@sumproduct. com. Untuk mengomentari artikel ini atau menyarankan ide untuk artikel lain, hubungi Oliver Rowe di Oliver. Rowe@aicpa-cima. com