Cara menggunakan fungsi Excel OFFSET -

Ringkasan

Fungsi OFFSET Excel mengembalikan referensi ke rentang yang dibuat dengan lima input: (1) titik awal, (2) offset baris, (3) offset kolom, (4) tinggi baris, (5) lebar baris kolom. OFFSET berguna dalam rumus yang membutuhkan rentang dinamis.

Tujuan

Buat offset referensi dari titik awal yang diberikan

Nilai kembali

Referensi sel.

Sintaksis

= OFFSET (referensi, baris, kolom, (tinggi), (lebar))

Argumen

  • referensi - Titik awal, disediakan sebagai referensi atau rentang sel.
  • baris - Jumlah baris yang akan diimbangi di bawah referensi awal.
  • cols - Jumlah kolom yang akan diimbangi di sebelah kanan referensi awal.
  • height - (opsional) Tinggi dalam baris referensi yang dikembalikan.
  • width - (opsional) Lebar dalam kolom dari referensi yang dikembalikan.

Versi: kapan

Excel 2003

Catatan penggunaan

Fungsi Excel OFFSET mengembalikan rentang dinamis yang dibuat dengan lima input: (1) titik awal, (2) offset baris, (3) offset kolom, (4) tinggi dalam baris, (5) lebar kolom.

Titik awal ( argumen referensi ) bisa satu sel atau satu rentang sel. The baris dan cols argumen adalah jumlah sel untuk "mengimbangi" dari titik awal. The tinggi dan lebar argumen adalah opsional dan menentukan ukuran rentang yang dibuat. Ketika tinggi dan lebar dihilangkan, standarnya adalah tinggi dan lebar referensi .

Misalnya, untuk referensi C5 mulai A1, referensi adalah A1, baris adalah 4 dan cols adalah 2:

=OFFSET(A1,4,2) // returns reference to C5

Untuk mereferensikan C1: C5 dari A1, referensi A1, baris 0, kolom 2, tinggi 5, dan lebarnya 1:

=OFFSET(A1,0,2,5,1) // returns reference to C1:C5

Catatan: lebar dapat dihilangkan, karena defaultnya adalah 1.

Adalah umum untuk melihat OFFSET dibungkus dalam fungsi lain yang mengharapkan suatu rentang. Misalnya, untuk SUM C1: C5, dimulai dari A1:

=SUM(OFFSET(A1,0,2,5,1)) // SUM C1:C5

Tujuan utama OFFSET adalah untuk memungkinkan rumus menyesuaikan secara dinamis dengan data yang tersedia atau masukan pengguna. Fungsi OFFSET dapat digunakan untuk membangun rentang bernama dinamis untuk bagan atau tabel pivot, untuk memastikan bahwa data sumber selalu mutakhir.

Catatan: Dokumentasi Excel menyatakan tinggi dan lebar tidak boleh negatif, tetapi nilai negatif tampaknya berfungsi dengan baik sejak awal 1990-an. Fungsi OFFSET di Google Sheets tidak akan mengizinkan nilai negatif untuk argumen tinggi atau lebar.

Contoh

Contoh di bawah ini menunjukkan bagaimana OFFSET dapat dikonfigurasi untuk mengembalikan jenis rentang yang berbeda. Layar ini diambil dengan Excel 365, jadi OFFSET mengembalikan larik dinamis saat hasilnya lebih dari satu sel. Di versi Excel yang lebih lama, Anda dapat menggunakan tombol F9 untuk memeriksa hasil yang dikembalikan dari OFFSET.

Contoh 1

Pada layar di bawah ini, kami menggunakan OFFSET untuk mengembalikan nilai ketiga (Maret) di kolom kedua (Barat). Rumus di H4 adalah:

=OFFSET(B3,3,2) // returns D6

Contoh # 2

Pada layar di bawah ini, kami menggunakan OFFSET untuk mengembalikan nilai terakhir (Juni) di kolom ketiga (Utara). Rumus di H4 adalah:

=OFFSET(B3,6,3) // returns E9

Contoh # 3

Di bawah ini, kami menggunakan OFFSET untuk mengembalikan semua nilai di kolom ketiga (Utara). Rumus di H4 adalah:

=OFFSET(B3,1,3,6) // returns E4:E9

Contoh # 4

Di bawah ini, kami menggunakan OFFSET untuk mengembalikan semua nilai untuk Mei (baris kelima). Rumus di H4 adalah:

=OFFSET(B3,5,1,1,4) // returns C8:F8

Contoh # 5

Di bawah, kami menggunakan OFFSET untuk mengembalikan nilai April, Mei, dan Juni untuk wilayah Barat. Rumus di H4 adalah:

=OFFSET(B3,4,2,3,1) // returns D7:D9

Contoh # 6

Di bawah, kami menggunakan OFFSET untuk mengembalikan nilai April, Mei, dan Juni untuk Barat dan Utara. Rumus di H4 adalah:

=OFFSET(B3,4,2,3,2) // returns D7:E9

Catatan

  • OFFSET hanya mengembalikan referensi, tidak ada sel yang dipindahkan.
  • Kedua baris dan kolom dapat diberikan sebagai angka negatif untuk membalikkan arah offset normalnya - kolom negatif offset ke kiri, dan baris negatif offset di atas.
  • OFFSET adalah "fungsi volatil" - ini akan dihitung ulang dengan setiap perubahan lembar kerja. Fungsi yang mudah menguap bisa membuat buku kerja yang lebih besar dan lebih kompleks berjalan lambat.
  • OFFSET akan menampilkan #REF! nilai kesalahan jika offset berada di luar tepi lembar kerja.
  • Ketika tinggi atau lebar dihilangkan, tinggi dan lebar acuan digunakan.
  • OFFSET dapat digunakan dengan fungsi lain yang mengharapkan untuk menerima referensi.
  • Dokumentasi Excel mengatakan tinggi dan lebar tidak boleh negatif, tetapi nilai negatif berfungsi.

Video Terkait

Cara membuat rentang nama dinamis dengan OFFSET Dalam video ini, kita akan melihat cara membuat rentang nama dinamis menggunakan fungsi OFFSET, cara paling umum untuk membuat rentang nama dinamis dengan rumus.

Artikel yang menarik...