Nilai Teratas dan Terbawah dengan LARGE dan SMALL

MAX memberikan satu nilai terbesar, sedangkan MIN memberikan satu nilai terkecil. Namun, bagaimana jika Anda menginginkan nilai tertinggi ke-2 atau nilai terendah ke-3?

Itulah yang dilakukan oleh LARGE dan SMALL. Keduanya memungkinkan Anda mengambil nilai terbesar ke-n atau terkecil ke-n dari suatu rentang, yang menjadi dasar laporan N-teratas dan N-terbawah.

LARGE(range, k) mengembalikan nilai terbesar ke-k. Argumen kedua, k, menentukan posisi: 1 adalah nilai terbesar, 2 adalah nilai terbesar kedua, dan seterusnya.

Jadi, LARGE(A2:A20,1) sama dengan MAX(A2:A20), sedangkan LARGE(A2:A20,2) memberikan nilai terbesar kedua.

SMALL(range, k) bekerja seperti LARGE, tetapi dimulai dari nilai terkecil. SMALL(A2:A20,1) sama dengan MIN(A2:A20), dan SMALL(A2:A20,3) mengembalikan nilai terkecil ketiga.

Gunakan SMALL untuk menampilkan kinerja terburuk, pilihan termurah, atau tanggal paling awal dalam suatu kumpulan data.

Untuk mencantumkan tiga nilai teratas, susun tiga rumus LARGE dengan k = 1, 2, 3. Cara yang lebih rapi adalah merujuk pada angka pembantu agar rumus dapat diisi ke bawah.

Jika sel D2 berisi 1, D3 berisi 2, dan D4 berisi 3, satu rumus ini jika diisi ke bawah akan menghasilkan seluruh daftar tiga teratas.

Anda dapat menghasilkan k secara otomatis dengan fungsi ROW, sehingga tidak memerlukan kolom pembantu. ROW()-1 menghasilkan 1 pada baris pertama, 2 pada baris berikutnya, dan seterusnya.

Jika diisi ke bawah, rumus ini membuat daftar berperingkat tanpa penyiapan tambahan. Sesuaikan offset dengan posisi awal daftar Anda.

Di Excel dan Google Sheets versi modern, Anda dapat menampilkan seluruh lima nilai teratas dengan satu rumus dengan memberikan SEQUENCE sebagai argumen k. SEQUENCE(5) menghasilkan 1, 2, 3, 4, 5.

LARGE kemudian mengembalikan lima nilai sekaligus dan menampilkannya ke bawah secara otomatis. Satu rumus, satu daftar berperingkat lengkap.

Pertanyaan bisnis yang umum: berapa kontribusi tiga pelanggan teratas saya? Bungkus LARGE dengan SUM menggunakan SEQUENCE (atau konstanta larik) untuk menjumlahkan nilai-nilai teratas.

Rumus di bawah ini menjumlahkan tiga nilai terbesar dalam rentang tersebut, sehingga memberikan total kontribusi tiga teratas dalam satu sel.

Jika k kurang dari 1 atau lebih besar daripada jumlah angka dalam rentang, LARGE dan SMALL mengembalikan #NUM!.

Misalnya, permintaan nilai terbesar ke-30 dalam daftar yang hanya memiliki 19 angka akan gagal. Cegah hal ini dalam laporan dengan membungkus pemanggilan tersebut dalam IFERROR.

Mengetahui suatu nilai saja kurang berguna dibandingkan mengetahui siapa yang mendapatkannya. Gabungkan LARGE dengan INDEX dan MATCH untuk mengambil nama yang terkait dengan nilai teratas.

Rumus ini menemukan angka penjualan terbesar, mencari barisnya, lalu mengembalikan nama yang sesuai dari kolom A, sehingga laporan Anda berbunyi "Penjual teratas: Maria."

Seperti fungsi statistik lainnya, LARGE dan SMALL hanya menghitung angka. Teks dan sel kosong dalam rentang akan dilewati, sehingga tidak memengaruhi nilai mana yang menjadi nilai terbesar ke-n.

Artinya, Anda dapat dengan aman mengarahkan keduanya ke kolom yang mencakup baris judul. Namun, tetap perhatikan angka yang tidak sengaja disimpan sebagai teks, karena angka tersebut akan diabaikan dan dapat mengubah nilai yang dikembalikan LARGE atau SMALL.

Anda memiliki data penjualan bulanan di B2:B20. Manajemen menginginkan tiga bulan dengan hasil terbaik dan terburuk.

• Tiga terbaik: =LARGE($B$2:$B$20,{1,2,3})
• Tiga terburuk: =SMALL($B$2:$B$20,{1,2,3})

Letakkan masing-masing rumus di sel yang dapat menampilkan hasil secara meluber, dan Anda langsung memiliki ringkasan terbaik dan terburuk untuk dasbor.

Uji pemahaman Anda tentang LARGE dan SMALL.

Sekarang Anda dapat melampaui MAX dan MIN:

• LARGE(range,k) mengembalikan nilai terbesar ke-k; SMALL(range,k) mengembalikan nilai terkecil ke-k.
• k = 1 sama dengan MAX / MIN; tingkatkan k untuk mendapatkan nilai terbesar atau terkecil berikutnya.
• Masukkan SEQUENCE atau larik seperti {1,2,3} untuk membuat atau menjumlahkan daftar N-teratas dalam satu rumus.
• Pasangkan dengan INDEX-MATCH untuk memberi label pada nilai, lalu bungkus dengan IFERROR untuk menangani k di luar rentang.