Formula generik
=SUM(firstcell:INDEX(data,rows,cols))
Ringkasan
Untuk menentukan julat berdasarkan nilai di sel lain, anda boleh menggunakan fungsi INDEX. Dalam contoh yang ditunjukkan, formula di J7 adalah:
=SUM(C5:INDEX(data,J5,J6))
di mana "data" adalah julat bernama B5: G9.
Penjelasan
Formula ini bergantung pada tingkah laku tertentu INDEX - walaupun nampaknya INDEX mengembalikan nilai pada lokasi tertentu, ia sebenarnya mengembalikan rujukan ke lokasi. Dalam kebanyakan formula, anda tidak akan melihat perbezaannya - Excel hanya menilai rujukan dan mengembalikan nilainya. Rumus ini menggunakan ciri ini untuk membina julat dinamik berdasarkan input lembaran kerja.
Di dalam fungsi jumlah, rujukan pertama hanyalah sel pertama dalam julat yang merangkumi semua sel yang mungkin:
=SUM(C5:
Untuk mendapatkan sel terakhir, kami menggunakan INDEX. Di sini, kami memberikan INDEX julat bernama "data", yang merupakan julat nilai maksimum yang mungkin, dan juga nilai dari J5 (baris) dan J6 (lajur). INDEX tidak mengembalikan julat, ia hanya mengembalikan satu sel di lokasi itu, E9 dalam contoh:
INDEX(data,J5,J6) // returns E9
Formula asal dikurangkan kepada:
=SUM(C5:E9)
yang mengembalikan 300, jumlah semua nilai dalam C5: E9.
Rumus di J8 hampir sama, tetapi menggunakan RATA-RATA dan bukannya SUM untuk mengira purata. Apabila pengguna mengubah nilai dalam J5 atau J6, julat dikemas kini, dan hasil baru dikembalikan.
Alternatif dengan OFFSET
Anda boleh membina formula yang serupa dengan fungsi OFFSET, seperti di bawah:
=SUM(OFFSET(C5,0,0,J5,J6)) // sum =AVERAGE(OFFSET(C5,0,0,J5,J6)) // average
OFFSET direka untuk mengembalikan julat, jadi formula mungkin lebih mudah difahami. Walau bagaimanapun, OFFSET adalah fungsi yang tidak menentu, dan boleh menyebabkan masalah prestasi apabila digunakan dalam lembaran kerja yang lebih besar dan lebih kompleks.
