Formula umum
=SUM(firstcell:INDEX(data,rows,cols))
Ringkasan
Untuk menentukan rentang berdasarkan nilai di sel lain, Anda bisa menggunakan fungsi INDEX. Pada contoh yang ditampilkan, rumus di J7 adalah:
=SUM(C5:INDEX(data,J5,J6))
di mana "data" adalah rentang bernama B5: G9.
Penjelasan
Rumus ini bergantung pada perilaku tertentu dari INDEX - meskipun tampaknya INDEX mengembalikan nilai di lokasi tertentu, sebenarnya ini mengembalikan referensi ke lokasi tersebut. Di sebagian besar rumus, Anda tidak akan melihat perbedaannya - Excel hanya mengevaluasi referensi dan mengembalikan nilainya. Rumus ini menggunakan fitur ini untuk membuat rentang dinamis berdasarkan input lembar kerja.
Di dalam fungsi penjumlahan, referensi pertama hanyalah sel pertama dalam rentang yang mencakup semua sel yang memungkinkan:
=SUM(C5:
Untuk mendapatkan sel terakhir, kami menggunakan INDEX. Di sini, kami memberi INDEX rentang bernama "data", yang merupakan rentang nilai maksimum yang mungkin, dan juga nilai dari J5 (baris) dan J6 (kolom). INDEX tidak mengembalikan rentang, ini hanya mengembalikan satu sel di lokasi itu, E9 dalam contoh:
INDEX(data,J5,J6) // returns E9
Rumus aslinya direduksi menjadi:
=SUM(C5:E9)
yang mengembalikan 300, jumlah dari semua nilai di C5: E9.
Rumus di J8 hampir sama, tetapi menggunakan RATA-RATA, bukan SUM untuk menghitung rata-rata. Ketika pengguna mengubah nilai di J5 atau J6 kisaran diperbarui, dan hasil baru dikembalikan.
Alternatif dengan OFFSET
Anda dapat membuat rumus yang mirip dengan fungsi OFFSET, seperti di bawah ini:
=SUM(OFFSET(C5,0,0,J5,J6)) // sum =AVERAGE(OFFSET(C5,0,0,J5,J6)) // average
OFFSET dirancang untuk mengembalikan rentang, jadi rumusnya mungkin lebih sederhana untuk dipahami. Namun, OFFSET adalah fungsi yang mudah menguap, dan dapat menyebabkan masalah kinerja saat digunakan dalam lembar kerja yang lebih besar dan lebih kompleks.
