Cara menggunakan fungsi Excel OFFSET -

Ringkasan

Fungsi Excel OFFSET mengembalikan rujukan ke rentang yang dibina dengan lima input: (1) titik permulaan, (2) offset baris, (3) pengimbasan lajur, (4) ketinggian dalam baris, (5) lebar di lajur. OFFSET berguna dalam formula yang memerlukan julat dinamik.

Tujuan

Buat rujukan mengimbangi dari titik permulaan yang diberikan

Nilai pulangan

Rujukan sel.

Sintaks

= OFFSET (rujukan, baris, col, (tinggi), (lebar))

Hujah

  • rujukan - Titik permulaan, disediakan sebagai rujukan sel atau julat.
  • baris - Bilangan baris untuk diimbangi di bawah rujukan permulaan.
  • cols - Bilangan lajur yang akan diimbangi di sebelah kanan rujukan permulaan.
  • tinggi - (pilihan) Ketinggian dalam baris rujukan yang dikembalikan.
  • lebar - (pilihan) Lebar dalam lajur rujukan yang dikembalikan.

Versi

Excel 2003

Catatan penggunaan

Fungsi Excel OFFSET mengembalikan julat dinamik yang dibina dengan lima input: (1) titik permulaan, (2) offset baris, (3) offset lajur, (4) ketinggian dalam baris, (5) lebar dalam lajur.

Titik permulaan ( argumen rujukan ) boleh menjadi satu sel atau rangkaian sel. The baris dan cols argumen ialah bilangan sel-sel untuk "mengimbangi" dari titik permulaan. The ketinggian dan lebar hujah-hujah adalah pilihan dan menentukan saiz julat yang dicipta. Apabila ketinggian dan lebar dihilangkan, mereka akan menetapkan ketinggian dan lebar rujukan .

Sebagai contoh, untuk merujuk C5 bermula dari A1, rujukan adalah A1, baris adalah 4 dan col adalah 2:

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

Untuk merujuk C1: C5 dari A1, rujukan adalah A1, baris adalah 0, cols adalah 2, tinggi adalah 5, dan lebar adalah 1:

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

Catatan: lebar boleh dihilangkan, kerana lalai menjadi 1.

Adalah biasa untuk melihat OFFSET dibungkus dengan fungsi lain yang mengharapkan julat. Contohnya, hingga SUM C1: C5, bermula pada A1:

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

Tujuan utama OFFSET adalah untuk membolehkan formula menyesuaikan secara dinamik dengan data yang ada atau input pengguna. Fungsi OFFSET dapat digunakan untuk membangun julat dinamis untuk carta atau jadual pangsi, untuk memastikan bahawa data sumber selalu terkini.

Catatan: Dokumentasi Excel menyatakan ketinggian dan lebar tidak boleh negatif, tetapi nilai negatif nampaknya berfungsi dengan baik sejak awal tahun 1990-an. Fungsi OFFSET dalam Helaian Google tidak akan membenarkan nilai negatif untuk argumen tinggi atau lebar.

Contoh

Contoh di bawah menunjukkan bagaimana OFFSET dapat dikonfigurasi untuk mengembalikan pelbagai jenis julat. Skrin ini diambil dengan Excel 365, jadi OFFSET mengembalikan array dinamik apabila hasilnya lebih dari satu sel. Dalam versi Excel yang lebih lama, anda boleh menggunakan kekunci F9 untuk memeriksa hasil yang dikembalikan dari OFFSET.

Contoh # 1

Pada skrin di bawah, kami menggunakan OFFSET untuk mengembalikan nilai ketiga (Mac) di lajur kedua (Barat). Formula dalam H4 adalah:

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

Contoh # 2

Pada skrin di bawah, kami menggunakan OFFSET untuk mengembalikan nilai terakhir (Jun) di lajur ketiga (Utara). Formula dalam H4 adalah:

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

Contoh # 3

Di bawah ini, kami menggunakan OFFSET untuk mengembalikan semua nilai pada lajur ketiga (Utara). Formula dalam H4 adalah:

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

Contoh # 4

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

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

Contoh # 5

Di bawah ini, kami menggunakan OFFSET untuk mengembalikan nilai April, Mei, dan Jun untuk wilayah Barat. Formula dalam H4 adalah:

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

Contoh # 6

Di bawah ini, kami menggunakan OFFSET untuk mengembalikan nilai April, Mei, dan Jun untuk Barat dan Utara. Formula dalam H4 adalah:

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

Catatan

  • OFFSET hanya mengembalikan rujukan, tidak ada sel yang dipindahkan.
  • Kedua-dua baris dan col dapat diberikan sebagai nombor negatif untuk membalikkan arah ofset normalnya - col negatif diimbangi ke kiri, dan baris negatif diimbangi di atas.
  • OFFSET adalah "fungsi tidak menentu" - ia akan dikira semula dengan setiap perubahan lembaran kerja. Fungsi tidak menentu dapat menjadikan buku kerja yang lebih besar dan kompleks berjalan dengan perlahan.
  • OFFSET akan memaparkan #REF! nilai ralat jika ofset berada di luar tepi lembaran kerja.
  • Apabila ketinggian atau lebar dihilangkan, tinggi dan lebar rujukan digunakan.
  • OFFSET boleh digunakan dengan fungsi lain yang diharapkan dapat menerima rujukan.
  • Dokumentasi Excel mengatakan ketinggian dan lebar tidak boleh negatif, tetapi nilai negatif berfungsi.

Video-video yang berkaitan

Cara membuat julat dinamik dengan OFFSET Dalam video ini, kita akan melihat bagaimana membuat julat dinamik dengan menggunakan fungsi OFFSET, cara yang paling biasa untuk membuat julat nama dinamik dengan formula.

Artikel menarik...