Rumus array dinamis dan perilaku array yang terbagi

Rumus Excel yang menghasilkan sekumpulan nilai, juga dikenal sebagai array, menghasilkan nilai ini ke sel sebelahnya. Perilaku ini disebut peluapan.

Rumus yang dapat menghasilkan array dengan ukuran yang bervariasi disebut rumus array dinamis. Rumus yang saat ini menghasilkan array yang berhasil diluapkan dapat disebut sebagai rumus array luapan.

Berikut adalah beberapa catatan untuk membantu Anda memahami dan menggunakan rumus dengan tipe ini.

Apa artinya luapan?

Catatan: Rumus array yang lebih lama, yang dikenal sebagai rumus array warisan, selalu menghasilkan hasil dengan ukuran tetap - rumus tersebut selalu meluap ke dalam sel dengan jumlah yang sama. Perilaku peluapan yang dijelaskan dalam topik ini tidak berlaku untuk rumus array warisan.

Luapan berarti rumus telah menghasilkan beberapa nilai, dan nilai tersebut telah diletakkan di sel yang bersebelahan. Misalnya, =SORT(D2:D11,1,-1), yang mengurutkan array dalam urutan menurun, akan menghasilkan array terkait dengan tinggi 10 baris. Namun, Anda hanya perlu memasukkan rumus di sel kiri atas, atau F2 dalam kasus ini, dan rumus akan secara otomatis diluapkan ke sel F11.

Urutkan nilai dalam sel D2:D11 dengan =SORT(D2:D11,1,-1)

Titik kunci

Saat Anda menekan Enter untuk mengonfirmasi rumus, Excel akan mengubah ukuran rentang output secara dinamis untuk Anda, dan meletakkan hasil ke dalam setiap sel dalam rentang tersebut.
Jika Anda menulis rumus array dinamis untuk bertindak pada daftar data, akan berguna jika meletakkannya dalam tabel Excel, lalu menggunakan referensi terstruktur untuk merujuk pada data. Hal ini karena referensi terstruktur disesuaikan secara otomatis saat menambahkan atau menghapus baris dari tabel.
Rumus array luapan tidak didukung dalam tabel Excel itu sendiri, jadi Anda harus meletakkannya di kisi di luar Tabel. Tabel paling cocok untuk menyimpan baris dan kolom dengan data independen.
Setelah memasukkan rumus array luapan, ketika Anda memilih sel apa pun dalam area luapan, Excel akan menempatkan batas yang disorot di sekitar rentang tersebut. Batas akan menghilang saat Anda memilih sel di luar area tersebut.
Hanya sel pertama di area luapan yang dapat diedit. Jika Anda memilih sel lain di area luapan, rumus akan terlihat di bilah rumus, tetapi teksnya "berbayang", dan tidak dapat diubah. Jika perlu memperbarui rumus, Anda harus memilih sel kiri atas dalam rentang array, mengubahnya sesuai kebutuhan, lalu Excel akan memperbarui area luapan lainnya secara otomatis saat Anda menekan Enter.
Tumpang tindih rumus - Rumus array tidak dapat dimasukkan jika ada sesuatu yang memblokir rentang output. dan jika hal ini terjadi, Excel akan menghasilkan kesalahan #LUAPAN! yang menunjukkan bahwa ada hambatan. Jika Anda menghapus hambatan, rumus akan meluap seperti seharusnya. Dalam contoh di bawah ini, rentang output rumus tumpang tindih dengan rentang lain yang memiliki data, dan ditampilkan dengan batas titik-titik yang tumpang tindih dengan sel yang berisi nilai yang menunjukkan bahwa luapan tidak dapat dilakukan. Hapus data yang memblokir, atau salin ke area lain, lalu rumus akan meluap seperti seharusnya.
Rumus array warisan yang dimasukkan melalui CTRL+SHIFT+ENTER (CSE) masih didukung untuk alasan kompatibilitas mundur, tetapi tidak boleh digunakan lagi. Jika ingin, Anda dapat mengonversi rumus array warisan ke rumus array dinamis dengan mencari sel pertama dalam rentang array, menyalin teks rumus, menghapus seluruh rentang array warisan, lalu memasukkan ulang rumus di sel kiri atas. Sebelum meningkatkan rumus array warisan ke rumus array dinamis, Anda harus mengetahui beberapa perbedaan penghitungan di antara keduanya.
Excel memiliki dukungan yang terbatas untuk larik dinamis antar buku kerja, dan skenario ini hanya didukung ketika kedua buku kerja terbuka. Jika Anda menutup buku kerja sumber, setiap rumus array dinamis yang tertaut akan mengembalikan kesalahan #REF! ketika direfresh.