Apa itu referensi dinamis di excel?

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

Bagaimana cara membuat referensi dinamis di Excel?

Bagaimana Anda mereferensikan rentang dinamis di Excel?

Apa itu lembar kerja Excel dinamis?

Apa rumus tanggal dinamis di Excel?