Ini adalah trik sederhana namun menarik yang memungkinkan Anda untuk mengganti data dari tabel kecil tanpa menggunakan sel sama sekali. Esensinya adalah Anda dapat "menjahit" array nilai substitusi langsung ke dalam rumus. Mari kita lihat beberapa cara untuk melakukan ini.
fungsi PILIH
Jika Anda perlu mengganti data dari array satu dimensi dengan angka, maka Anda dapat menggunakan fungsi INDEX atau yang lebih sederhana dan lebih cocok, dalam hal ini analog – fungsi SELEKSI (MEMILIH). Ini menampilkan elemen array dengan nomor urutnya. Jadi, misalnya, jika kita perlu menampilkan nama hari dalam seminggu dengan nomornya, maka kita dapat menggunakan konstruksi ini
Ini adalah contoh sederhana untuk memulai untuk mendapatkan gagasan bahwa tabel pencarian dapat ditransfer langsung ke dalam rumus. Sekarang mari kita lihat contoh yang lebih rumit, tetapi lebih cantik.
Array konstanta dalam rumus
Misalkan kita memiliki daftar kota, di mana menggunakan fungsi VPR (VLOOKUP) nilai koefisien gaji dari kolom kedua tabel kuning di sebelah kanan diganti:
Triknya adalah Anda dapat mengganti referensi ke rentang dengan tabel $E$3:$F$5 array konstanta langsung dalam rumus, dan tabel yang tepat tidak lagi diperlukan. Agar tidak memasukkan data secara manual, Anda bisa melakukan sedikit trik.
Pilih sel kosong mana saja. Masukkan tanda sama dengan dari keyboard dan pilih rentang dengan tabel – alamatnya harus ditampilkan di bilah rumus:
Pilih dengan mouse tautan E3:F5 di bilah rumus dan tekan tombol F9 – tautan akan berubah menjadi larik konstanta:
Tetap menyalin array yang dihasilkan dan menempelkannya ke rumus kami dengan VLOOKUP, dan menghapus tabel itu sendiri karena tidak perlu:
Bernama array konstanta
Mengembangkan ide metode sebelumnya, Anda dapat mencoba opsi lain – melakukan bernama array konstanta dalam RAM, yang kemudian digunakan dalam rumus. Untuk melakukan ini, klik pada tab rumus (Rumus) tombol Manajer nama (NamaManajer)… Kemudian tekan tombol membuat, pikirkan dan masukkan nama (biarkan saja, misalnya, kota) dan di lapangan Jarak (Referensi) rekatkan array konstanta yang disalin dalam metode sebelumnya:
Tekan OK dan tutup Manajer nama. Sekarang nama yang ditambahkan dapat digunakan dengan aman di setiap lembar buku dalam rumus apa pun – misalnya, dalam fungsi VLOOKUP kami:
Ringkas, cantik, dan, dalam arti tertentu, bahkan melindungi dari tangan non-profesional yang main-main
- Cara menggunakan fungsi VLOOKUP untuk mencari data dari satu tabel ke tabel lainnya
- Cara menggunakan pencarian perkiraan dengan fungsi VLOOKUP
- Perhitungan tanpa rumus