Panduan dan contoh rumus array

Latihan ini menunjukkan cara menggunakan rumus array multisel dan sel tunggal untuk menghitung serangkaian angka penjualan. Rangkaian langkah pertama menggunakan rumus multisel untuk menghitung rangkaian subtotal. Rangkaian kedua menggunakan rumus sel tunggal untuk menghitung jumlah total.

• Rumus array multisel

  

• Di sini kami menghitung Total Penjualan coupe dan sedan untuk setiap staf penjualan dengan memasukkan =F10:F19*G10:G19 di sel H10.

  Saat menekan Enter, Anda akan melihat hasil meluap ke bawah ke sel H10:H19. Perhatikan bahwa rentang luapan disorot dengan batas saat Anda memilih sel mana pun dalam rentang luapan. Anda mungkin juga melihat rumus dalam sel H10:H19 tampak keabu-abuan. Keberadaan rumus tersebut hanya untuk referensi, jadi jika ingin menyesuaikan rumus, Anda perlu memilih sel H10, tempat rumus utama berada.

• Rumus array sel tunggal

  

  Di sel H20 buku kerja contoh, ketik atau salin dan tempel =SUM(F10:F19*G10:G19), lalu tekan Enter.

  Dalam kasus ini, Excel mengalikan nilai dalam array (rentang sel F10 sampai G19), lalu menggunakan fungsi SUM untuk menjumlahkan totalnya. Hasilnya adalah jumlah total penjualan $1.590.000,00.

  Contoh ini menunjukkan betapa canggihnya tipe rumus ini. Sebagai contoh, misalnya Anda memiliki 1.000 baris data. Anda dapat menjumlahkan sebagian atau semua data itu dengan membuat rumus array di sel tunggal sebagai ganti menyeret rumus menuruni 1.000 baris. Perhatikan juga bahwa rumus sel tunggal di sel H20 independen sepenuhnya terhadap rumus multisel (rumus di sel H10 sampai H19). Ini adalah keunggulan lain penggunaan rumus array — fleksibilitas. Anda dapat mengubah rumus lain di kolom H tanpa memengaruhi rumus di H20. Hal ini juga merupakan praktik yang baik untuk memiliki total yang independen seperti ini, karena membantu memvalidasi keakuratan hasil.

• Rumus array dinamis juga menawarkan keunggulan berikut:

  • Konsistensi    Jika Anda mengeklik salah satu sel dari H10 ke bawah, Anda akan melihat rumus yang sama. Konsistensi ini dapat membantu memastikan akurasi yang lebih tinggi.

  • Keamanan    Anda tidak dapat menimpa komponen rumus array multisel. Misalnya, klik sel H11, lalu tekan Delete. Excel tidak akan mengubah output array. Untuk mengubahnya, Anda harus memilih sel kiri atas dalam array, atau sel H10.

  • Ukuran file lebih kecil    Anda bisa sering menggunakan rumus array tunggal daripada beberapa rumus menengah. Misalnya, contoh penjualan mobil menggunakan satu rumus array untuk menghitung hasil di kolom E. Jika Anda telah menggunakan rumus standar seperti =F10*G10, F11*G11, F12*G12, dll., Anda akan menggunakan 11 rumus yang berbeda untuk menghitung nilai yang sama. Itu bukan masalah besar, tetapi bagaimana jika Anda memiliki ribuan baris? Maka itu dapat membuat perbedaan besar.

  • Efisiensi    Fungsi array dapat menjadi cara yang efisien untuk membangun rumus yang kompleks. Rumus array =SUM(F10:F19*G10:G19) sama dengan ini: =SUM(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).

  • Peluapan    Rumus array dinamis akan secara otomatis meluap ke rentang output. Jika data sumber ada dalam tabel Excel, rumus array dinamis akan secara otomatis mengubah ukurannya saat Anda menambahkan atau menghapus data.

  • Kesalahan #SPILL!    Aray dinamis memperkenalkan kesalahan #SPILL!, yang menunjukkan bahwa rentang luapan yang dimaksud terhalang karena alasan tertentu. Saat Anda mengatasi penghalang, rumus akan meluap secara otomatis.

Barisan tetap adalah komponen dari rumus array. Anda membuat barisan tetap dengan memasukkan daftar item, lalu menutup daftar itu secara manual dengan tanda kurung kurawal ({ }), seperti ini:

={1\2\3\4\5} atau ={"January"\"February"\"March"}

Jika memisahkan item dengan koma, Anda membuat array horizontal (baris). Jika memisahkan item dengan titik koma, Anda membuat array vertikal (kolom). Untuk membuat array dua dimensi, batasi item di setiap baris dengan koma, dan batasi setiap baris dengan titik koma.

Prosedur berikut akan memberi Anda beberapa latihan dalam membuat konstanta horizontal, vertikal, dan dua dimensi. Kami akan menunjukkan contoh penggunaan fungsi SEQUENCE untuk membuat barisan tetap secara otomatis, serta memasukkan barisan tetap secara manual.

• Membuat konstanta horizontal

  Gunakan buku kerja dari contoh sebelumnya, atau buat buku kerja baru. Pilih sel kosong, lalu masukkan =SEQUENCE(1,5). Fungsi SEQUENCE membuat array 1 baris kali 5 kolom yang sama seperti ={1\2\3\4\5}. Hasil berikut akan ditampilkan:

  

• Membuat konstanta vertikal

  Pilih sel kosong dengan ruang di bawahnya, lalu masukkan =SEQUENCE(5), atau ={1;2;3;4;5}. Hasil berikut akan ditampilkan:

  

• Membuat konstanta dua dimensi

  Pilih sel kosong dengan ruang di sebelah kanan dan di bawahnya, lalu masukkan =SEQUENCE(3,4). Anda akan melihat hasil berikut:

  

  Anda juga dapat memasukkan: atau ={1\2\3\4;5\6\7\8;9\10\11\12}, tetapi Anda perlu memperhatikan tempat meletakkan titik koma versus koma.

  Seperti yang Anda lihat, opsi SEQUENCE menawarkan keuntungan yang signifikan dibandingkan memasukkan nilai barisan tetap secara manual. Terutama, menghemat waktu Anda, tetapi juga dapat membantu mengurangi kesalahan dari entri manual. Ini juga lebih mudah dibaca, terutama karena titik koma sulit dibedakan dari pemisah koma.

Berikut adalah contoh yang menggunakan barisan tetap sebagai bagian dari rumus yang lebih besar. Dalam buku kerja contoh, buka lembar kerja Konstanta dalam rumus, atau buat lembar kerja baru.

Di sel D9, kami memasukkan =SEQUENCE(1,5,3,1), tetapi Anda juga dapat memasukkan 3, 4, 5, 6, dan 7 di sel A9:H9. Tidak ada hal khusus dari pemilihan nomor tersebut, kami hanya memilih nomor selain 1-5 agar berbeda.

Di sel E11, masukkan =SUM(D9:H9*SEQUENCE(1,5)), atau =SUM(D9:H9*{1\2\3\4\5}). Rumus menghasilkan 85.

Fungsi SEQUENCE menyusun ekuivalensi barisan tetap {1\2\3\4\5}. Karena Excel mengoperasikan ekspresi yang diapit tanda kurung terlebih dahulu, dua elemen berikutnya yang dioperasikan adalah nilai sel di D9:H9, dan operator perkalian (*). Pada titik ini, rumus mengalikan nilai dalam array yang tersimpan dengan nilai yang terkait dalam konstanta. Ini ekuivalen dengan:

=SUM(D9*1,E9*2,F9*3,G9*4,H9*5), atau =SUM(3*1,4*2,5*3,6*4,7*5)

Terakhir, fungsi SUM menambahkan nilai, dan menghasilkan 85.

Untuk menghindari penggunaan array tersimpan dan mempertahankan seluruh operasi dalam memori, Anda dapat menggantinya dengan barisan tetap lain:

=SUM(SEQUENCE(1,5,3,1)*SEQUENCE(1,5)), atau =SUM({3\4\5\6\7}*{1\2\3\4\5})

Elemen yang dapat Anda gunakan dalam barisan tetap

• Barisan tetap dapat berisi angka, teks, nilai logis (seperti TRUE dan FALSE), dan nilai kesalahan seperti #N/A. Anda dapat menggunakan angka dalam format bilangan bulat, desimal, dan notasi ilmiah. Jika menyertakan teks, Anda harus menutupnya dengan tanda kutip ("teks”).

• Konstanta array tidak boleh berisi array, rumus, atau fungsi tambahan. Dengan kata lain, konstanta array hanya boleh berisi teks atau angka yang dipisahkan dengan koma atau titik koma. Excel menampilkan pesan peringatan apabila Anda memasukkan rumus seperti {1\2\A1:D4} atau {1\2\SUM(Q2:Z8)}. Nilai numerik juga tidak boleh berisi tanda persen, tanda dolar, koma, atau kurung.

Salah satu cara terbaik untuk menggunakan barisan tetap adalah dengan memberinya nama. Konstanta bernama lebih mudah digunakan, dan dapat menyembunyikan kerumitan rumus array Anda dari orang lain. Untuk memberi nama konstanta array dan menggunakannya dalam rumus, lakukan hal berikut:

Buka Rumus > Nama yang Ditentukan > Tentukan Nama. Di kotak Nama, ketik Quarter1. Di kotak Merujuk ke, masukkan konstanta berikut (ingat untuk mengetik tanda kurung kurawal secara manual):

={"January"\"February"\"March"}

Kotak dialog kini akan terlihat seperti ini:

Klik OK, lalu pilih baris apa pun dengan tiga sel kosong, lalu masukkan =Quarter1.

Hasil berikut akan ditampilkan:

Jika ingin hasilnya meluap secara vertikal, dan bukan horizontal, Anda dapat menggunakan =TRANSPOSE(Quarter1).

Jika ingin menampilkan daftar 12 bulan, seperti yang mungkin Anda gunakan saat membuat laporan keuangan, Anda dapat mendasarkannya pada tahun ini dengan fungsi SEQUENCE. Hal yang menarik dari fungsi ini adalah meskipun hanya bulan yang ditampilkan, ada tanggal valid di belakangnya yang dapat Anda gunakan dalam perhitungan lain. Anda akan menemukan contoh ini pada Konstanta array bernama dan lembar kerja Himpunan data sampel cepat di buku kerja contoh.

=TEXT(DATE(YEAR(TODAY()),SEQUENCE(1,12),1),"mmm")

Ini menggunakan fungsi DATE untuk membuat tanggal berdasarkan tahun ini, SEQUENCE membuat barisan tetap dari 1 sampai 12 untuk Januari sampai Desember, lalu fungsi TEXT mengubah format tampilan menjadi "mmm" (Jan, Feb, Mar, dll.). Jika ingin menampilkan nama lengkap bulan, seperti Januari, Anda perlu menggunakan "mmmm".

Jika menggunakan konstanta bernama sebagai rumus array, ingatlah untuk memasukkan tanda sama dengan, seperti =Quarter1, bukan hanya Quarter1. Jika tidak, Excel akan menafsirkan array tersebut sebagai string teks dan rumus Anda tidak akan bekerja seperti yang diharapkan. Terakhir, ingatlah bahwa Anda dapat menggunakan kombinasi fungsi, teks dan angka. Semua tergantung pada seberapa kreatif yang Anda inginkan.

Contoh berikut menggambarkan beberapa cara penempatan konstanta array untuk digunakan dalam rumus array. Beberapa contoh menggunakan fungsi TRANSPOSE untuk mengubah baris menjadi kolom dan sebaliknya.

• Mengalikan setiap item dalam array

  Masukkan =SEQUENCE(1,12)*2, atau ={1\2\3\4;5\6\7\8;9\10\11\12}*2

  Anda juga dapat membagi dengan (/), menambah dengan (+), dan mengurangi dengan (-).

• Memangkatkan item dalam array

  Masukkan =SEQUENCE(1,12)^2, atau ={1\2\3\4;5\6\7\8;9\10\11\12}^2

• Menemukan akar kuadrat dari item kuadrat dalam array

  Masukkan =SQRT(SEQUENCE(1,12)^2), atau =SQRT({1\2\3\4;5\6\7\8;9\10\11\12}^2)

• Transpose baris satu dimensi

  Masukkan =TRANSPOSE(SEQUENCE(1,5)), atau =TRANSPOSE({1\2\3\4\5})

  Meskipun Anda memasukkan barisan tetap horizontal, fungsi TRANSPOSE akan mengubah barisan tetap tersebut menjadi kolom.

• Transpose kolom satu dimensi

  Masukkan =TRANSPOSE(SEQUENCE(5,1)), atau =TRANSPOSE({1;2;3;4;5})

  Meskipun Anda memasukkan barisan tetap vertikal, fungsi TRANSPOSE akan mengubah barisan tetap tersebut menjadi baris.

• Transpose konstanta dua dimensi

  Masukkan =TRANSPOSE(SEQUENCE(3,4)), atau =TRANSPOSE({1\2\3\4;5\6\7\8;9\10\11\12})

  Fungsi TRANSPOSE mengubah setiap baris menjadi serangkaian kolom.

Bagian ini menyediakan contoh rumus array dasar.

• Membuat array dari nilai yang ada

  Contoh berikut menjelaskan cara menggunakan rumus array untuk membuat array baru dari array yang sudah ada.

  Masukkan =SEQUENCE(3,6,10,10), atau ={10\20\30\40\50\60;70\80\90\100\110\120;130\140\150\160\170\180}

  Pastikan untuk mengetik { (tanda kurung kurawal buka) sebelum mengetik 10, dan } (tanda kurung kurawal tutup) setelah mengetik 180, karena Anda sedang membuat array angka.

  Berikutnya, masukkan =D9#, atau =D9:I11 di sel kosong. Arrat 3 x 6 dari sel muncul dengan niilai yang sama yang Anda lihat di D9:D11. Tanda # disebut operator rentang luapan, dan ini merupakan cara Excel dalam mereferensikan seluruh rentang array daripada harus mengetiknya.

  

• Membuat barisan tetap dari nilai yang ada

  Anda dapat mengambil hasil rumus array yang meluap dan mengubahnya menjadi bagian-bagian komponennya. Pilih sel D9, lalu tekan F2 untuk beralih ke mode edit. Selanjutnya, tekan F9 untuk mengubah referensi sel menjadi nilai, yang kemudian diubah Excel menjadi barisan tetap. Saat Anda menekan Enter, rumus, =D9#, kini seharusnya menjadi ={10\20\30;40\50\60;70\80\90}.

• Menghitung karakter dalam rentang sel

  Contoh berikut menunjukkan cara menghitung jumlah karakter dalam rentang sel. Ini termasuk spasi.

  

  =SUM(LEN(C9:C13))

  Dalam kasus ini, fungsi LEN menghasilkan panjang setiap string teks di setiap sel dalam rentang tersebut. Fungsi SUM kemudian menjumlahkan semua nilai tersebut dan menampilkan hasilnya (66). Jika ingin mendapatkan jumlah karakter rata-rata, Anda dapat menggunakan:

  =AVERAGE(LEN(C9:C13))

• Isi sel terpanjang dalam rentang C9:C13

  =INDEX(C9:C13,MATCH(MAX(LEN(C9:C13)),LEN(C9:C13),0),1)

  Rumus ini hanya berfungsi jika rentang data hanya berisi satu kolom sel.

  Mari periksa lebih dekat rumus ini, mulai dari elemen dalam ke arah luar. Fungsi LEN menghasilkan panjang setiap item dalam rentang sel D2:D6. Fungsi MAX menghitung nilai terbesar di antara item tersebut, yang sesuai dengan string teks terpanjang, yang ada di sel D3.

  Di sini akan mulai terlihat sedikit kompleks. Fungsi MATCH menghitung offset (posisi relatif) sel yang berisi string teks terpanjang. Untuk melakukannya, diperlukan tiga argumen: nilai pencarian, array pencarian, dan tipe yang cocok. Fungsi MATCH mencari array pencarian untuk nilai pencarian yang ditetapkan. Dalam kasus ini, nilai pencarian adalah string teks terpanjang:

  MAX(LEN(C9:C13)

  dan bahwa string itu terdapat dalam array ini:

  LEN(C9:C13)

  Argumen tipe yang cocok dalam kasus ini adalah 0. Tipe yang cocok dapat berupa nilai 1, 0, atau -1.

  • 1 - menghasilkan nilai terbesar yang kurang dari atau sama dengan val pencarian

  • 0 - menghasilkan nilai pertama yang sama persis dengan nilai pencarian

  • -1 - menghasilkan nilai terkecil yang lebih besar atau sama dengan nilai pencarian yang ditentukan

  • Jika Anda menghilangkan tipe yang cocok, Excel mengasumsikan 1.

  Terakhir, fungsi INDEX memperhitungkan argumen ini: array, serta nomor baris dan kolom dalam array tersebut. Rentang sel C9:C13 memberikan array, fungsi MATCH memberikan alamat sel, dan argumen akhir (1) menentukan bahwa nilai berasal dari kolom pertama dalam array.

  Jika ingin mendapatkan isi string teks terkecil, Anda perlu mengubah MAX pada contoh di atas dengan MIN.

• Menemukan n nilai terkecil dalam rentang

  Contoh ini menampilkan cara menemukan tiga nilai terkecil dalam rentang sel, dengan array data sampel di sel B9:B18 telah dibuat dengan: =INT(RANDARRAY(10,1)*100). Perhatikan bahwa RANDARRAY adalah fungsi yang mudah berubah, jadi Anda akan mendapatkan kumpulan angka acak baru setiap kali Excel menghitung.

  

  Masukkan =SMALL(B9#,SEQUENCE(D9), =SMALL(B9:B18,{1;2;3})

  Rumus ini menggunakan barisan tetap untuk mengevaluasi fungsi SMALL tiga kali dan menghasilkan 3 anggota terkecil dalam array yang terdapat di sel B9:B18, dengan 3 adalah nilai variabel di sel D9. Untuk menemukan lebih banyak nilai, Anda dapat menambah nilai dalam fungsi SEQUENCE, atau menambahkan lebih banyak argumen ke konstanta. Anda juga dapat menggunakan fungsi tambahan dengan rumus ini, seperti SUM atau AVERAGE. Misalnya:

  =SUM(SMALL(B9#,SEQUENCE(D9))

  =AVERAGE(SMALL(B9#,SEQUENCE(D9))

• Menemukan n nilai terbesar dalam satu rentang

  Untuk mencari nilai terbesar dalam satu rentang, Anda dapat mengganti fungsi SMALL dengan fungsi LARGE. Selain itu, contoh berikut menggunakan fungsi ROW dan INDIRECT.

  Masukkan =LARGE(B9#,ROW(INDIRECT("1:3"))), atau =LARGE(B9:B18,ROW(INDIRECT("1:3")))

  Pada tahap ini, mengetahui sedikit tentang fungsi ROW dan INDIRECT mungkin akan berguna untuk Anda. Anda dapat menggunakan fungsi ROW untuk membuat array bilangan bulat berurutan. Misalnya, pilih kolom kosong, lalu masukkan:

  =ROW(1:10)

  Rumus ini membuat kolom 10 bilangan bulat berurutan. Untuk melihat kemungkinan masalah, sisipkan satu baris di atas rentang yang memuat rumus array (yaitu, di atas baris 1). Excel menyesuaikan referensi baris, dan kini rumus menghasilkan bilangan bulat dari 2 sampai 11. Untuk mengatasinya, tambahkan fungsi INDIRECT pada rumus:

  =ROW(INDIRECT("1:10"))

  Fungsi INDIRECT menggunakan string teks sebagai argumennya (itulah sebabnya rentang 1:10 ditutup dengan tanda kutip). Excel tidak menyesuaikan nilai teks apabila Anda menyisipkan baris atau memindahkan rumus array. Akibatnya, fungsi ROW selalu menghasilkan array bilangan bulat yang Anda inginkan. Anda dapat dengan mudah menggunakan SEQUENCE:

  =SEQUENCE(10)

  Mari periksa rumus yang Anda gunakan sebelumnya — =LARGE(B9#,ROW(INDIRECT("1:3"))) — dimulai dari tanda kurung dalam ke arah luar: Fungsi INDIRECT menghasilkan sekumpulan nilai teks, dalam kasus ini nilai 1 sampai 3. Fungsi ROW kemudian menghasilkan array kolom tiga sel. Fungsi LARGE menggunakan nilai dalam rentang sel B9:B18, dan dievaluasi tiga kali, satu kali untuk setiap referensi yang dihasilkan oleh fungsi ROW. Jika ingin mencari nilai lainnya, Anda dapat menambahkan rentang sel yang lebih besar ke fungsi INDIRECT. Terakhir, seperti contoh SMALL, Anda dapat menggunakan rumus ini dengan fungsi lain, seperti SUM dan AVERAGE.

• Menjumlahkan rentang yang berisi nilai kesalahan

  Fungsi SUM di Excel tidak berfungsi apabila Anda mencoba menjumlahkan rentang yang berisi nilai kesalahan, misalnya #VALUE! atau #N/A. Contoh ini menunjukkan cara menjumlahkan nilai dalam rentang bernama Data yang berisi kesalahan:

  

• =SUM(IF(ISERROR(Data),"",Data))

  Rumus ini membuat array baru yang berisi nilai aslinya dikurangi setiap nilai kesalahan. Dimulai dari fungsi dalam ke arah luar, fungsi ISERROR mencari rentang sel (Data) untuk kesalahan. Fungsi IF menghasilkan nilai tertentu jika kondisi yang Anda tetapkan dievaluasi TRUE dan nilai lain jika dievaluasi FALSE. Dalam kasus ini, fungsi akan mengembalikan string kosong ("") untuk semua nilai kesalahan karena dievaluasi ke TRUE, dan mengembalikan nilai yang tersisa dari rentang (Data) karena dievaluasi ke FALSE, yang berarti tidak berisi nilai kesalahan. Fungsi SUM kemudian menghitung jumlah total untuk array yang difilter.

• Menghitung jumlah nilai kesalahan dalam rentang

  Contoh ini serupa dengan rumus sebelumnya, tetapi menghasilkan jumlah nilai kesalahan dalam rentang bernama Data dan bukan memfilternya:

  =SUM(IF(ISERROR(Data),1,0))

  Rumus ini membuat array yang berisi nilai 1 untuk sel yang berisi kesalahan dan nilai 0 untuk sel yang tidak berisi kesalahan. Anda dapat menyederhanakan rumus dan mendapatkan hasil yang sama dengan menghapus argumen ketiga untuk fungsi IF, seperti ini:

  =SUM(IF(ISERROR(Data),1))

  Jika Anda tidak menentukan argumen, fungsi IF menghasilkan FALSE jika sel tidak berisi nilai kesalahan. Anda dapat menyederhanakan lagi rumus tersebut:

  =SUM(IF(ISERROR(Data)*1))

  Versi ini bekerja karena TRUE*1=1 dan FALSE*1=0.

Anda mungkin perlu menjumlahkan nilai berdasarkan kondisi.

Misalnya, rumus array ini menjumlahkan bilangan bulat positif saja dalam rentang yang bernama Sales, yang mewakili sel E9: E24 pada contoh di atas:

=SUM(IF(Sales>0,Sales))

Fungsi IF membuat array nilai positif dan salah. Fungsi SUM pada dasarnya mengabaikan nilai salah karena 0+0=0. Rentang sel yang Anda gunakan dalam rumus ini dapat terdiri dari jumlah baris dan kolom berapa pun.

Anda juga dapat menjumlahkan nilai yang memenuhi lebih dari satu kondisi. Misalnya, rumus array ini menghitung nilai yang lebih besar dari 0 DAN kurang dari 2500:

=SUM((Sales>0)*(Sales<2500)*(Sales))

Perhatikan bahwa rumus ini mengembalikan kesalahan jika rentang berisi satu atau beberapa sel nonnumerik.

Anda juga dapat membuat rumus array yang menggunakan tipe atau kondisi OR. Misalnya, Anda dapat menjumlahkan nilai yang lebih besar dari 0 ATAU kurang dari 2500:

=SUM(IF((Sales>0)+(Sales<2500),Sales))

Anda tidak dapat menggunakan fungsi AND dan OR langsung dalam rumus array karena fungsi itu memberi hasil tunggal, baik TRUE maupun FALSE, dan fungsi array memerlukan array hasil. Anda dapat mengatasinya dengan menggunakan logika yang ditampilkan dalam rumus sebelumnya. Dengan kata lain, Anda menjalankan operasi matematika, seperti penambahan atau perkalian pada nilai yang memenuhi kondisi OR dan AND.

Contoh ini menunjukkan cara mengeluarkan nol dari rentang apabila Anda perlu menghitung nilai rata-rata dalam rentang tersebut. Rumus ini menggunakan rentang data bernama Sales:

=AVERAGE(IF(Sales<>0,Sales))

Fungsi IF membuat array nilai yang tidak sama dengan 0, lalu meneruskan nilai tersebut ke fungsi AVERAGE.

Rumus array ini membandingkan nilai dalam dua rentang sel bernama MyData dan YourData dan mengembalikan jumlah perbedaan antara keduanya. Jika konten kedua rentang itu identik, rumus ini akan mengembalikan 0. Untuk menggunakan rumus ini, rentang sel harus memiliki ukuran dan dimensi yang sama. Misalnya, jika MyData adalah rentang 3 baris kali 5 kolom, YourData juga harus 3 baris kali 5 kolom:

=SUM(IF(MyData=YourData,0,1))

Rumus ini membuat array baru dengan ukuran yang sama sebagai rentang yang dibandingkan. Fungsi IF mengisi array dengan nilai 0 dan nilai 1 (0 bila tidak cocok dan 1 untuk sel yang identik). Fungsi SUM kemudian menghasilkan jumlah nilai dalam array tersebut.

Anda dapat menyederhanakan rumus seperti ini:

=SUM(1*(MyData<>YourData))

Seperti rumus yang menghitung nilai kesalahan dalam rentang, rumus ini berfungsi karena TRUE*1=1, dan FALSE*1=0.

Rumus array ini mengembalikan nomor baris nilai maksimum dalam rentang kolom tunggal yang bernama Data:

=MIN(IF(Data=MAX(Data),ROW(Data),""))

Fungsi IF membuat array baru yang terkait dengan rentang bernama Data. Jika sel yang terkait berisi nilai maksimum dalam rentang, array akan berisi nomor baris. Jika tidak, array berisi string kosong (""). Fungsi MIN menggunakan array baru sebagai argumen kedua dan mengembalikan nilai terkecil, yang terkait dengan nomor baris dari nilai maksimum dalam Data. Jika rentang yang bernama Data berisi nilai maksimum yang identik, rumus akan mengembalikan baris nilai pertama.

Jika Anda ingin mengembalikan alamat sel sebenarnya dari nilai maksimum, gunakan rumus ini:

=ADDRESS(MIN(IF(Data=MAX(Data),ROW(Data),"")),COLUMN(Data))

Anda akan menemukan contoh serupa di buku kerja contoh pada lembar kerja Perbedaan antara himpunan data.

Latihan ini menunjukkan cara menggunakan rumus array multisel dan sel tunggal untuk menghitung serangkaian angka penjualan. Rangkaian langkah pertama menggunakan rumus multisel untuk menghitung rangkaian subtotal. Rangkaian kedua menggunakan rumus sel tunggal untuk menghitung jumlah total.

• Rumus array multisel

Salin seluruh tabel di bawah ini, lalu tempel ke sel A1 di lembar kerja kosong.

Staf  Penjualan	Jenis  Mobil	Jumlah  Terjual	Harga  Unit	Total  Penjualan
Barnhill	Sedan	5	33000
	Coupe	4	37000
Ingle	Sedan	6	24000
	Coupe	8	21000
Jordan	Sedan	3	29000
	Coupe	1	31000
Pica	Sedan	9	24000
	Coupe	5	37000
Sanchez	Sedan	6	33000
	Coupe	8	31000
Rumus (Jumlah Total)			Jumlah Total
'=SUM(C2:C11*D2:D11)			=SUM(C2:C11*D2:D11)

1. Untuk melihat Penjualan Total dari coupe dan sedan untuk tiap tenaga penjualan, pilih sel E2:E11, masukkan rumus =C2:C11*D2:D11, lalu tekan Ctrl+Shift+Enter.

2. Untuk melihat Total Keseluruhan dari semua penjualan, pilih sel F11, masukkan rumus =SUM(C2:C11*D2:D11), lalu tekan Ctrl+Shift+Enter.

Saat menekan Ctrl+Shift+Enter, Excel mengapit rumus dengan tanda kurung kurawal ({ }), lalu menyisipkan contoh rumus pada setiap sel dari rentang yang dipilih. Hal ini terjadi sangat cepat, sehingga yang Anda lihat di kolom E adalah jumlah total penjualan untuk setiap jenis mobil untuk setiap tenaga penjualan. Jika Anda memilih E2, lalu E3, E4, dan seterusnya, Anda akan melihat bahwa rumus yang sama diperlihatkan: {=C2:C11*D2:D11}. 

• Membuat rumus array sel tunggal

Di sel D13 buku kerja, ketik rumus berikut, lalu tekan Ctrl+Shift+Enter:

=SUM(C2:C11*D2:D11)

Dalam kasus ini, Excel mengalikan nilai dalam array (rentang sel C2 sampai D11), lalu menggunakan fungsi SUM untuk menjumlahkan totalnya. Hasilnya adalah jumlah total penjualan $1.590.000,00. Contoh ini menunjukkan betapa canggihnya tipe rumus ini. Sebagai contoh, misalnya Anda memiliki 1.000 baris data. Anda dapat menjumlahkan sebagian atau semua data itu dengan membuat rumus array di sel tunggal sebagai ganti menyeret rumus menuruni 1.000 baris.

Perhatikan juga bahwa rumus sel tunggal di sel D13 independen sepenuhnya terhadap rumus multisel (rumus di sel E2 sampai E11). Ini adalah keunggulan lain menggunakan rumus array — fleksibilitas. Anda bisa mengubah rumus di kolom E atau menghapus kolom itu sama sekali, tanpa memengaruhi rumus di D13.

Rumus array juga menawarkan keunggulan berikut:

• Konsistensi    Jika Anda mengklik salah satu sel dari E2 ke bawah, Anda melihat rumus yang sama. Konsistensi ini dapat membantu memastikan akurasi yang lebih tinggi.

• Keamanan    Anda tidak bisa menimpa komponen rumus array multi sel. Misalnya, klik sel E3, lalu tekan Delete. Anda harus memilih seluruh rentang sel (E2 sampai E11) dan mengubah rumus untuk seluruh array, atau membiarkan array seperti itu. Sebagai tindakan keamanan tambahan, Anda harus menekan Ctrl+Shift+Enter untuk mengonfirmasi perubahan apa pun pada rumus.

• Ukuran file lebih kecil    Anda bisa sering menggunakan rumus array tunggal daripada beberapa rumus menengah. Sebagai contoh, buku kerja ini menggunakan satu rumus array untuk menghitung hasil di kolom E. Jika menggunakan rumus standar (misalnya =C2*D2, C3*D3, C4*D4…), Anda akan harus menggunakan 11 rumus yang berbeda untuk mendapatkan hasil yang sama.

Secara umum, rumus array menggunakan sintaks rumus standar. Semuanya dimulai dengan tanda sama dengan (=), dan Anda dapat menggunakan sebagian besar fungsi bawaan Excel dalam rumus array Anda. Perbedaan utamanya adalah, bila menggunakan rumus array, Anda menekan Ctrl+Shift+Enter untuk memasukkan rumus. Apabila Anda melakukannya, Excel akan menutup rumus array Anda dengan kurung kurawal — jika Anda mengetikkan kurung kurawal secara manual, rumus Anda akan dikonversi menjadi string teks, dan tidak akan bekerja.

Fungsi array dapat menjadi cara yang efisien untuk membangun rumus yang kompleks. Rumus array =SUM(C2:C11*D2:D11) sama dengan: =SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).

Setiap kali Anda bekerja dengan rumus multisel, perhatikan juga:

• Pilih rentang sel untuk menampung hasil sebelum memasukkan rumus. Anda melakukannya apabila membuat rumus array multisel dengan memilih sel E2 sampai E11.

• Anda tidak dapat mengubah isi setiap sel dalam satu rumus array. Untuk melakukannya, pilih sel E3 dalam buku kerja dan tekan Delete. Excel akan menampilkan pesan bahwa Anda tidak dapat mengubah sebagian array.

• Anda dapat memindahkan atau menghapus seluruh rumus array, tetapi Anda tidak dapat memindahkan atau menghapus sebagian. Dengan kata lain, untuk menyusutkan rumus array, Anda harus menghapus rumus yang ada lalu memulai ulang.

• Untuk menghapus rumus array, pilih seluruh rentang rumus (misalnya, E2:E11), lalu tekan Delete.

• Anda tidak dapat menyisipkan sel kosong, atau menghapus sel dari rumus array multisel.

Ada kalanya, Anda mungkin perlu memperluas rumus array. Pilih sel pertama dalam rentang array yang sudah ada, dan lanjutkan hingga Anda telah memilih seluruh rentang yang ingin diperpanjang rumusnya. Tekan F2 untuk mengedit rumus,  kemudian tekan CTRL+SHIFT+ENTER untuk mengonfirmasi rumus setelah Anda menyesuaikan rentang rumus. Kuncinya adalah memilih seluruh rentang, dimulai dari sel kiri atas dalam array. Sel kiri atas adalah sel yang akan diedit.

Rumus array memang hebat, tetapi ada kelemahannya:

• Anda sesekali mungkin lupa menekan Ctrl+Shift+Enter. Hal ini bisa terjadi bahkan pada pengguna Excel yang paling berpengalaman. Ingatlah untuk menekan kombinasi tombol ini setiap kali memasukkan atau mengedit rumus array.

• Pengguna lain dari buku kerja Anda mungkin tidak memahami rumus Anda. Dalam praktiknya, rumus array umumnya tidak dijelaskan dalam lembar kerja. Oleh karena itu, jika orang lain perlu memodifikasi buku kerja Anda, Anda harus menghindari rumus array atau memastikan orang tersebut mengetahui tentang rumus array apa pun dan memahami cara mengubahnya, jika perlu.

• Tergantung kecepatan pemrosesan dan memori komputer Anda, rumus array yang besar dapat memperlambat perhitungan.

Konstanta array adalah komponen dari rumus array. Anda membuat konstanta array dengan memasukkan daftar item lalu menutup daftar itu secara manual dengan kurung kurawal ({ }), seperti ini:

={1\2\3\4\5}

Sekarang, Anda sudah tahu bahwa Anda perlu menekan Ctrl+Shift+Enter setiap kali membuat rumus array. Karena barisan tetap adalah komponen rumus array, apit konstanta dengan tanda kurung kurawal dengan mengetiknya secara manual. Anda kemudian menggunakan Ctrl+Shift+Enter untuk memasukkan seluruh rumus.

Jika memisahkan item dengan koma, Anda membuat array horizontal (baris). Jika memisahkan item dengan titik koma, Anda membuat array vertikal (kolom). Untuk membuat array dua dimensi, batasi item di setiap baris dengan koma, dan batasi setiap baris dengan titik koma.

Ini adalah array dalam satu baris: {1\2\3\4}. Ini adalah array di satu kolom: {1\2\3\4}. Dan ini adalah array dua baris dan empat kolom: {1\2\3\4;5\6\7\8}. Pada array dua baris, baris pertama adalah 1, 2, 3, dan 4, dan baris kedua adalah 5, 6, 7, dan 8. Satu titik koma memisahkan kedua baris, antara 4 dan 5.

Seperti halnya rumus array, Anda dapat menggunakan konstanta array dengan sebagian besar fungsi bawaan Excel. Bagian berikut menjelaskan cara membuat setiap konstanta dan cara menggunakannya dengan fungsi di Excel.

Prosedur berikut akan memberi Anda latihan membuat konstanta horizontal, vertikal, dan dua dimensi.

Membuat konstanta horizontal

1. Pada lembar kerja kosong, pilih sel A1 hingga E1.

2. Pada bilah rumus, masukkan rumus berikut, lalu tekan Ctrl+Shift+Enter:

   ={1\2\3\4\5}

   Dalam kasus ini, Anda harus mengetik tanda kurung kurawal buka dan tutup ({ }), dan Excel akan menambahkan kumpulan kedua untuk Anda.

   Hasil berikut akan ditampilkan.

   

Membuat konstanta vertikal

1. Dalam buku kerja Anda, pilih satu kolom lima sel.

2. Pada bilah rumus, masukkan rumus berikut, lalu tekan Ctrl+Shift+Enter:

   ={1;2;3;4;5}

   Hasil berikut akan ditampilkan.

   

Membuat konstanta dua dimensi

1. Dalam buku kerja Anda, pilih satu blok sel lebar tiga kolom kali tinggi tiga baris.

2. Pada bilah rumus, masukkan rumus berikut, lalu tekan Ctrl+Shift+Enter:

   ={1\2\3\4;5\6\7\8;9\10\11\12}

   Anda akan melihat hasil berikut:

   

Menggunakan konstanta dalam rumus

Ini adalah contoh sederhana yang menggunakan konstanta:

1. Dalam buku kerja contoh, buat lembar kerja baru.

2. Di sel A1, ketikkan 3, lalu ketikkan 4 di B1, 5 di C1, 6 di D1, dan 7 di E1.

3. Di sel A3, ketik rumus berikut, lalu tekan Ctrl+Shift+Enter:

   =SUM(A1:E1*{1\2\3\4\5})

   Perhatikan bahwa Excel menutup konstanta dengan sepasang kurung kurawal, karena Anda memasukkannya sebagai rumus array.

   

   Nilai 85 muncul di sel A3.

Bagian berikutnya menjelaskan cara kerja rumus.

Rumus yang baru saja Anda gunakan terdiri dari beberapa bagian.

1. Fungsi

2. Array tersimpan

3. Operator

4. Konstanta array

Elemen terakhir dalam tanda kurung adalah konstanta array: {1\2\3\4\5}. Ingatlah bahwa Excel tidak menutup konstanta array dengan kurung kurawal; Anda yang mengetikkannya. Ingat juga bahwa setelah Anda menambahkan konstanta ke rumus array, tekan Ctrl+Shift+Enter untuk memasukkan rumus.

Karena Excel menjalankan operasi dalam tanda kurung terlebih dahulu, kedua elemen berikutnya adalah nilai yang tersimpan di buku kerja (A1:E1) dan operator. Pada titik ini, rumus mengalikan nilai dalam array yang tersimpan dengan nilai yang terkait dalam konstanta. Ini ekuivalen dengan:

=SUM(A1*1,B1*2,C1*3,D1*4,E1*5)

Terakhir, fungsi SUM menambahkan nilai-nilai, dan jumlah 85 ditampilkan di sel A3.

Untuk menghindari menggunakan array tersimpan dan menjaga operasi seluruhnya di memori, ganti array tersimpan dengan konstanta array yang lain:

=SUM({3\4\5\6\7}*{1\2\3\4\5})

Untuk mencobanya, salin fungsi ini, pilih sel kosong di buku kerja Anda, tempel rumus ke bilah rumus, lalu tekan Ctrl+Shift+Enter. Anda akan melihat hasil yang sama seperti dalam latihan sebelumnya yang menggunakan rumus array:

=SUM(A1:E1*{1\2\3\4\5})

Konstanta array dapat berisi angka, teks, nilai logika (seperti TRUE dan FALSE), dan nilai kesalahan (seperti #N/A). Anda dapat menggunakan angka dalam format bilangan bulat, desimal, dan ilmiah. Jika menyertakan teks, Anda perlu menutup teks dengan tanda kutip (").

Konstanta array tidak boleh berisi array, rumus, atau fungsi tambahan. Dengan kata lain, konstanta array hanya boleh berisi teks atau angka yang dipisahkan dengan koma atau titik koma. Excel menampilkan pesan peringatan apabila Anda memasukkan rumus seperti {1\2\A1:D4} atau {1\2\SUM(Q2:Z8)}. Nilai numerik juga tidak boleh berisi tanda persen, tanda dolar, koma, atau kurung.

Salah satu cara terbaik untuk menggunakan barisan tetap adalah dengan memberinya nama. Konstanta bernama lebih mudah digunakan, dan dapat menyembunyikan kerumitan rumus array Anda dari orang lain. Untuk memberi nama konstanta array dan menggunakannya dalam rumus, lakukan hal berikut:

1. Pada tab Rumus, dalam grup Nama yang Ditentukan, klik Tentukan Nama.
   Kotak dialog Tentukan Nama akan muncul.

2. Di kotak Nama, ketikkan Quarter1.

3. Di kotak Merujuk ke, masukkan konstanta berikut (ingatlah untuk mengetikkan tanda kurung kurawal secara manual):

   ={"January"\"February"\"March"}

   Konten kotak dialog sekarang terlihat seperti ini:

   

4. Klik OK, lalu pilih baris yang berisi tiga sel kosong.

5. Ketik rumus berikut, lalu tekan Ctrl+Shift+Enter.

   =Quarter1

   Hasil berikut akan ditampilkan.

   

Ketika Anda menggunakan konstanta bernama seperti rumus array, ingatlah untuk memasukkan tanda sama dengan. Jika tidak, Excel akan menafsirkan array itu sebagai string teks dan rumus Anda tidak akan bekerja seperti yang diharapkan. Terakhir, ingatlah bahwa Anda dapat menggunakan kombinasi teks dan angka.

Perhatikan masalah berikut apabila konstanta array Anda tidak bekerja:

• Beberapa elemen mungkin tidak dipisahkan dengan karakter yang benar. Jika Anda menghilangkan satu koma atau titik koma, atau meletakkannya di tempat yang salah, barisan tetap mungkin tidak dibuat dengan benar atau Anda mungkin melihat pesan peringatan.

• Anda mungkin memilih rentang sel yang tidak cocok dengan jumlah elemen dalam konstanta Anda. Misalnya, jika Anda memilih satu kolom enam sel untuk digunakan dengan konstanta lima sel, nilai kesalahan #N/A akan ditampilkan di sel yang kosong. Sebaliknya, jika Anda memilih terlalu sedikit sel, Excel akan menghilangkan nilai yang tidak memiliki sel yang terkait.

Contoh berikut menggambarkan beberapa cara penempatan konstanta array untuk digunakan dalam rumus array. Beberapa contoh menggunakan fungsi TRANSPOSE untuk mengubah baris menjadi kolom dan sebaliknya.

Mengalikan setiap item dalam satu array

1. Buat lembar kerja baru, lalu pilih satu blok sel kosong lebar empat kolom kali tinggi tiga baris.

2. Ketik rumus berikut, lalu tekan Ctrl+Shift+Enter:

   ={1\2\3\4;5\6\7\8;9\10\11\12}*2

Memangkatkan item dalam satu array

1. Pilih satu blok sel kosong lebar empat kolom kali tinggi tiga baris.

2. Ketik rumus array berikut, lalu tekan Ctrl+Shift+Enter:

   ={1\2\3\4;5\6\7\8;9\10\11\12}*{1\2\3\4;5\6\7\8;9\10\11\12}

   Atau alternatifnya, masukkan rumus array ini, yang menggunakan operator caret (^):

   ={1\2\3\4;5\6\7\8;9\10\11\12}^2

Mengubah urutan baris satu dimensi

1. Pilih satu kolom lima sel kosong.

2. Ketik rumus berikut, lalu tekan Ctrl+Shift+Enter:

   =TRANSPOSE({1\2\3\4\5})

   Meskipun Anda memasukkan konstanta array horizontal, fungsi TRANSPOSE akan mengonversi konstanta array tersebut menjadi kolom.

Mengubah urutan kolom satu dimensi

1. Pilih satu kolom lima sel kosong.

2. Masukkan rumus berikut, lalu tekan Ctrl+Shift+Enter:

   =TRANSPOSE({1;2;3;4;5})

Meskipun Anda memasukkan konstanta array vertikal, fungsi TRANSPOSE akan mengonversi konstanta array tersebut menjadi baris.

Mengubah urutan konstanta dua dimensi

1. Pilih satu blok sel lebar tiga kolom kali tinggi empat baris.

2. Masukkan konstanta berikut, lalu tekan Ctrl+Shift+Enter:

   =TRANSPOSE({1\2\3\4;5\6\7\8;9\10\11\12})

   Fungsi TRANSPOSE mengonversi setiap baris menjadi serangkaian kolom.

Bagian ini menyediakan contoh rumus array dasar.

Membuat array dan konstanta array dari nilai yang ada

Contoh berikut ini menjelaskan cara menggunakan rumus array untuk membuat link antar rentang sel di lembar kerja yang berbeda. Contoh ini juga menunjukkan cara membuat konstanta array dari rangkaian nilai yang sama.

Membuat array dari nilai yang ada

1. Di lembar kerja di Excel, pilih sel C8:E10, dan masukkan rumus ini:

   ={10\20\30;40\50\60;70\80\90}

   Pastikan mengetikkan { (kurung kurawal buka) sebelum Anda mengetikkan 10, dan } (kurung kurawal tutup) setelah Anda mengetikkan 90, karena Anda sedang membuat array angka.

2. Tekan Ctrl+Shift+Enter, yang memasukkan array angka ini dalam rentang sel C8:E10 dengan menggunakan rumus array. Di lembar kerja Anda, C8 hingga E10 akan terlihat seperti ini:

   10	20	30
   40	50	60
   70	80	90

3. Pilih rentang sel C1 hingga E3.

4. Masukkan rumus berikut pada bilah rumus, lalu tekan Ctrl+Shift+Enter:

   =C8:E10

   Array sel 3x3 muncul di sel C1 hingga E3 dengan nilai yang sama Anda lihat di C8 hingga E10.

Membuat barisan tetap dari nilai yang ada

1. Dengan sel C1:C3 dipilih, tekan F2 untuk beralih ke mode edit. 

2. Tekan F9 untuk mengubah referensi sel menjadi nilai. Excel mengubah nilai tersebut menjadi barisan tetap. Rumusnya kini harus ={10\20\30;40\50\60;70\80\90}.

3. Tekan Ctrl+Shift+Enter untuk memasukkan barisan tetap tersebut sebagai rumus array.

Menghitung karakter dalam satu rentang sel

Contoh berikut ini menunjukkan cara menghitung jumlah karakter, termasuk spasi, dalam satu rentang sel.

1. Salin seluruh tabel ini dan tempelkan ke lembar kerja di sel A1.

   Data
   This is a
   bunch of cells that
   come together
   to form a
   single sentence.
   Karakter total di A2:A6
   =SUM(LEN(A2:A6))
   Konten sel terpanjang (A3)
   =INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)

2. Pilih sel A8, lalu tekan Ctrl+Shift+Enter untuk melihat jumlah total karakter di sel A2:A6 (66).

3. Pilih sel A10, lalu tekan Ctrl+Shift+Enter untuk melihat isi terpanjang dari sel A2:A6 (sel A3).

Rumus berikut yang digunakan di sel A8 menghitung jumlah total karakter (66) dalam sel A2 sampai A6.

=SUM(LEN(A2:A6))

Dalam kasus ini, fungsi LEN mengembalikan panjang setiap string teks di setiap sel dalam rentang tersebut. Fungsi SUM kemudian menjumlahkan semua nilai tersebut dan menampilkan hasilnya (66).

Menemukan nilai terkecil n dalam satu rentang

Contoh ini menunjukkan cara menemukan tiga nilai terkecil dalam satu rentang sel.

1. Masukkan beberapa angka acak di sel A1:A11.

2. Pilih sel C1 hingga C3. Kumpulan sel ini akan menampung hasil yang dihasilkan oleh rumus array.

3. Masukkan rumus berikut, lalu tekan Ctrl+Shift+Enter:

   =SMALL(A1:A11,{1;2;3})

Rumus ini menggunakan barisan tetap untuk mengevaluasi fungsi SMALL tiga kali dan menghasilkan anggota terkecil (1), terkecil kedua (2), dan terkecil ketiga (3) dalam array yang terdapat di sel A1:A10. Untuk menemukan nilai lainnya, tambahkan argumen lain ke konstanta. Anda juga dapat menggunakan fungsi tambahan dengan rumus ini, seperti SUM atau AVERAGE. Misalnya:

=SUM(SMALL(A1:A10,{1\2\3})

=AVERAGE(SMALL(A1:A10,{1\2\3})

Menemukan nilai terbesar n dalam satu rentang

Untuk mencari nilai terbesar dalam satu rentang, Anda dapat mengganti fungsi SMALL dengan fungsi LARGE. Selain itu, contoh berikut ini menggunakan fungsi ROW dan INDIRECT.

1. Pilih sel D1 hingga D3.

2. Pada bilah rumus, masukkan rumus berikut, lalu tekan Ctrl+Shift+Enter:

   =LARGE(A1:A10,ROW(INDIRECT("1:3")))

Pada titik ini, mungkin membantu untuk mengetahui sedikit tentang fungsi ROW dan INDIRECT. Anda bisa menggunakan fungsi ROW untuk membuat array bilangan bulat berurutan. Misalnya, pilih kolom kosong 10 sel di buku kerja latihan Anda, masukkan rumus array ini, lalu tekan Ctrl+Shift+Enter:

=ROW(1:10)

Rumus ini membuat kolom 10 bilangan bulat berurutan. Untuk melihat kemungkinan masalah, sisipkan satu baris di atas rentang yang memuat rumus array (yaitu, di atas baris 1). Excel menyesuaikan referensi baris, dan rumus mengembalikan bilangan bulat dari 2 sampai 11. Untuk mengatasinya, tambahkan fungsi INDIRECT pada rumus:

=ROW(INDIRECT("1:10"))

Fungsi INDIRECT menggunakan string teks sebagai argumennya (itulah sebabnya rentang 1:10 ditutup dengan tanda kutip ganda). Excel tidak menyesuaikan nilai teks apabila Anda menyisipkan baris atau memindahkan rumus array. Hasilnya, fungsi ROW akan selalu mengembalikan array bilangan bulat yang Anda inginkan.

Mari periksa rumus yang Anda gunakan sebelumnya — =LARGE(A5:A14,ROW(INDIRECT("1:3"))) — dimulai dari tanda kurung dalam ke arah luar: Fungsi INDIRECT menghasilkan sekumpulan niilai teks, dalam kasus ini nilai 1 sampai 3. Fungsi ROW kemudian mengembalikan array kolom tiga sel. Fungsi LARGE menggunakan nilai dalam rentang sel A5:A14, dan dievaluasi tiga kali, sekali untuk setiap referensi yang dikembalikan oleh fungsi ROW. Nilai 3200, 2700, dan 2000 dikembalikan ke array kolom tiga sel. Jika ingin mencari nilai lainnya, Anda dapat menambahkan rentang sel yang lebih besar ke fungsi INDIRECT.

Seperti contoh sebelumnya, Anda dapat menggunakan rumus ini dengan fungsi lain, seperti SUM dan AVERAGE.

Menemukan string teks terpanjang dalam rentang sel

Kembali ke contoh string teks sebelumnya, masukkan rumus berikut di sel kosong, lalu tekan Ctrl+Shift+Enter:

=INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)

Teks "bunch of cells that" akan muncul.

Mari periksa lebih dekat rumus ini, mulai dari elemen dalam ke arah luar. Fungsi LEN menghasilkan panjang setiap item dalam rentang sel A2:A6. Fungsi MAX menghitung nilai terbesar di antara item tersebut, yang sesuai dengan string teks terpanjang, yang ada di sel A3.

Di sini akan mulai terlihat sedikit kompleks. Fungsi MATCH menghitung offset (posisi relatif) sel yang berisi string teks terpanjang. Untuk melakukannya, diperlukan tiga argumen: nilai pencarian, array pencarian, dan tipe yang cocok. Fungsi MATCH mencari array pencarian untuk nilai pencarian yang ditetapkan. Dalam kasus ini, nilai pencarian adalah string teks terpanjang:

(MAX(LEN(A2:A6))

dan bahwa string itu terdapat dalam array ini:

LEN(A2:A6)

Argumen tipe yang cocok adalah 0. Tipe yang cocok dapat berupa nilai 1, 0, atau -1. Jika Anda menetapkan 1, MATCH mengembalikan nilai terbesar yang kurang dari atau sama dengan nilai pencarian. Jika Anda menetapkan 0, MATCH mengembalikan nilai pertama yang tepat sama dengan nilai pencarian. Jika Anda menetapkan -1, MATCH menemukan nilai terkecil yang lebih besar dari atau sama dengan nilai pencarian yang ditetapkan. Jika Anda menghilangkan tipe yang cocok, Excel mengasumsikan 1.

Terakhir, fungsi INDEX memperhitungkan argumen ini: array, serta nomor baris dan kolom dalam array tersebut. Rentang sel A2:A6 memberikan array, fungsi MATCH memberikan alamat sel, dan argumen akhir (1) menentukan bahwa nilai berasal dari kolom pertama dalam array.

Bagian ini menyediakan contoh rumus array tingkat lanjut.

Menjumlahkan rentang yang berisi nilai kesalahan

Fungsi SUM di Excel tidak bekerja apabila Anda mencoba menjumlahkan rentang yang berisi nilai kesalahan, misalnya #N/A. Contoh ini menunjukkan cara menjumlahkan nilai dalam rentang bernama Data yang berisi kesalahan.

=SUM(IF(ISERROR(Data),"",Data))

Rumus ini membuat array baru yang berisi nilai aslinya dikurangi setiap nilai kesalahan. Dimulai dari fungsi dalam ke arah luar, fungsi ISERROR mencari rentang sel (Data) untuk kesalahan. Fungsi IF mengembalikan nilai tertentu jika kondisi yang Anda tetapkan dievaluasi TRUE dan nilai lain jika dievaluasi FALSE. Dalam kasus ini, fungsi akan mengembalikan string kosong ("") untuk semua nilai kesalahan karena dievaluasi ke TRUE, dan mengembalikan nilai yang tersisa dari rentang (Data) karena dievaluasi ke FALSE, yang berarti tidak berisi nilai kesalahan. Fungsi SUM kemudian menghitung jumlah total untuk array yang difilter.

Menghitung jumlah nilai kesalahan dalam satu rentang

Contoh ini serupa dengan rumus sebelumnya, tetapi mengembalikan jumlah nilai kesalahan dalam rentang yang bernama Data sebagai ganti memfilternya:

=SUM(IF(ISERROR(Data),1,0))

Rumus ini membuat array yang berisi nilai 1 untuk sel yang berisi kesalahan dan nilai 0 untuk sel yang tidak berisi kesalahan. Anda dapat menyederhanakan rumus dan mendapatkan hasil yang sama dengan menghapus argumen ketiga untuk fungsi IF, seperti ini:

=SUM(IF(ISERROR(Data),1))

Jika Anda tidak menetapkan argumen, fungsi IF mengembalikan FALSE jika sel tidak berisi nilai kesalahan. Anda dapat menyederhanakan lagi rumus tersebut:

=SUM(IF(ISERROR(Data)*1))

Versi ini bekerja karena TRUE*1=1 dan FALSE*1=0.

Menjumlahkan nilai berdasarkan kondisi

Anda mungkin perlu menjumlahkan nilai berdasarkan kondisi. Sebagai contoh, rumus array ini menjumlahkan hanya bilangan bulat positif dalam rentang yang bernama Sales:

=SUM(IF(Sales>0,Sales))

Fungsi IF membuat array nilai positif dan nilai salah. Fungsi SUM pada dasarnya mengabaikan nilai salah karena 0+0=0. Rentang sel yang Anda gunakan dalam rumus ini dapat terdiri dari jumlah baris dan kolom berapa pun.

Anda juga dapat menjumlahkan nilai yang memenuhi lebih dari satu kondisi. Misalnya, rumus array ini menghitung nilai yang lebih besar dari 0 dan kurang dari atau sama dengan 5:

=SUM((Sales>0)*(Sales<=5)*(Sales))

Perhatikan bahwa rumus ini mengembalikan kesalahan jika rentang berisi satu atau beberapa sel nonnumerik.

Anda juga dapat membuat rumus array yang menggunakan tipe atau kondisi OR. Misalnya, Anda dapat menjumlahkan nilai yang kurang dari 5 dan lebih besar dari 15:

=SUM(IF((Sales<5)+(Sales>15),Sales))

Fungsi IF menemukan semua nilai yang lebih kecil dari 5 dan lebih besar dari 15 lalu meneruskan nilai itu ke fungsi SUM.

Anda tidak dapat menggunakan fungsi AND dan OR langsung dalam rumus array karena fungsi itu memberi hasil tunggal, baik TRUE maupun FALSE, dan fungsi array memerlukan array hasil. Anda dapat mengatasinya dengan menggunakan logika yang ditampilkan dalam rumus sebelumnya. Dengan kata lain, Anda menjalankan operasi matematika, seperti penambahan atau perkalian, pada nilai yang memenuhi kondisi OR dan AND.

Menghitung rata-rata tanpa nol

Contoh ini menunjukkan cara mengeluarkan nol dari rentang apabila Anda perlu menghitung nilai rata-rata dalam rentang tersebut. Rumus ini menggunakan rentang data bernama Sales:

=AVERAGE(IF(Sales<>0,Sales))

Fungsi IF membuat array nilai yang tidak sama dengan 0 lalu meneruskan nilai itu ke fungsi AVERAGE.

Menghitung jumlah selisih antara dua rentang sel

Rumus array ini membandingkan nilai dalam dua rentang sel bernama MyData dan YourData dan mengembalikan jumlah perbedaan antara keduanya. Jika konten kedua rentang itu identik, rumus ini akan mengembalikan 0. Untuk menggunakan rumus ini, rentang sel harus berukuran sama dengan dimensi yang sama (misalnya, jika MyData adalah rentang 3 baris kali 5 kolom, YourData juga harus 3 baris kali 5 kolom):

=SUM(IF(MyData=YourData,0,1))

Rumus ini membuat array baru dengan ukuran yang sama sebagai rentang yang dibandingkan. Fungsi IF mengisi array dengan nilai 0 dan nilai 1 (0 bila tidak cocok dan 1 untuk sel yang identik). Fungsi SUM kemudian mengembalikan jumlah nilai dalam array tersebut.

Anda dapat menyederhanakan rumus seperti ini:

=SUM(1*(MyData<>YourData))

Seperti rumus yang menghitung nilai kesalahan dalam satu rentang, rumus ini bekerja karena TRUE*1=1, dan FALSE*1=0.

Menemukan lokasi nilai maksimum dalam satu rentang

Rumus array ini mengembalikan nomor baris nilai maksimum dalam rentang kolom tunggal yang bernama Data:

=MIN(IF(Data=MAX(Data),ROW(Data),""))

Fungsi IF menciptakan array baru yang terkait dengan rentang yang bernama Data. Jika sel yang terkait berisi nilai maksimum dalam rentang, array akan berisi nomor baris. Jika tidak, array berisi string kosong (""). Fungsi MIN menggunakan array baru sebagai argumen kedua dan mengembalikan nilai terkecil, yang terkait dengan nomor baris dari nilai maksimum dalam Data. Jika rentang yang bernama Data berisi nilai maksimum yang identik, rumus akan mengembalikan baris nilai pertama.

Jika Anda ingin mengembalikan alamat sel sebenarnya dari nilai maksimum, gunakan rumus ini:

=ADDRESS(MIN(IF(Data=MAX(Data),ROW(Data),"")),COLUMN(Data))