Excel OFFSET fungsi

Excel Fungsi OFFSET mengembalikan referensi ke sel atau rentang sel yang diimbangi dari sel tertentu dengan sejumlah baris dan kolom.

Sintaksis

=OFFSET (reference, rows, cols, [height], [width])

kasus

Nilai kembali

Fungsi OFFSET akan mengembalikan offset referensi sel dari titik awal tertentu.

Catatan fungsi

1. #VALUE! nilai kesalahan akan kembali ketika referensi merujuk ke rentang sel yang tidak bersinambungan.
2. #REF! nilai kesalahan akan kembali ketika baris dan kolom mengimbangi referensi di tepi lembar kerja.

contoh

Contoh 1: Penggunaan dasar untuk fungsi OFFSET

Kembalikan referensi ke sel dengan rumus di bawah ini:
=OFFSET(B2,3,1)

Dalam hal ini, B2 adalah titik awal, angka 3 dan 1 berarti memindahkan 3 baris ke bawah dan 1 kolom ke kanan dari sel B2, dan akhirnya mengembalikan nilai di C5. Lihat tangkapan layar:

Kembalikan referensi ke rentang sel dengan rumus di bawah ini:
=OFFSET(B2,3,1,2,2)

Dalam hal ini, Anda akan mendapatkan hasil rentang 2 x 2 yaitu 3 baris di bawah dan 1 kolom di kanan sel B2.

Note: #VALUE! Kesalahan akan terjadi ketika Anda hanya memilih satu sel untuk menerapkan fungsi OFFSET untuk mengembalikan rentang sel. Anda perlu memilih rentang 2 x 2 (mengatakan 4 sel kosong), masukkan rumus dan tekan Ctrl + perubahan + Enter kunci untuk mendapatkan hasil.

Contoh 2: Gunakan fungsi OFFSET untuk menjumlahkan rentang nilai

Seperti yang kami sebutkan di atas, jika Anda mencoba menggunakan fungsi OFFSET seperti =OFFSET(B2,3,1,2,2) sendiri dalam satu sel, ini akan mengembalikan #VALUE! Kesalahan. Namun, jika Anda menggabungkan fungsi SUM dan OFFSET seperti gambar di bawah ini, ini akan mengembalikan jumlah nilai dalam rentang C5: D6 secara langsung.

1. Pilih sel kosong, salin rumus di bawah ini ke dalamnya dan tekan Enter kunci untuk mendapatkan hasil.
=SUM(OFFSET(B2,3,1,2,2)))

Contoh 3: Jumlahkan kolom berdasarkan kriteria tertentu

Seperti gambar di bawah ini, bagaimana cara mendapatkan total penjualan Mango dari Sun to Tue? Silakan coba seperti di bawah ini.

1. Pilih sel kosong, salin rumus di bawah ini ke dalamnya dan tekan Enter kunci untuk mendapatkan hasil.
=SUM(OFFSET(C2:E2,MATCH(G4,B3:B8,),))

Catatan:

1. Dalam rumus di atas, MATCH(G4,B3:B8) sedang mencari Mangga dan mengembalikan posisinya dalam jangkauan B3: B8. Mari kita lihat, Mango menempatkan 5 baris di bawah sel B2, sehingga mengembalikan angka 5;
2. Karena hasil MATCH adalah 5, dan koma terakhir di sini mewakili 0, fungsi OFFSET sekarang ditampilkan sebagai OFFSET(C2:E2,5,0), artinya offset dimulai dari range C2: E2, turunkan 5 baris ke bawah dan 0 kolom ke kanan untuk mendapatkan referensi ke range C7: E7;
3. Kemudian fungsi SUM ditampilkan sebagai SUM(C7:E7), dan akhirnya mengembalikan jumlah nilai dalam rentang C7: E7.

Lebih banyak contoh

Bagaimana cara menjumlahkan setiap n baris di Excel?

Bagaimana cara membuat rentang nama dinamis di Excel?

Bagaimana mengembalikan nilai sel setiap lima atau n baris di Excel?