Tutorial ini menerangi fungsi offset Excel yang sering-memudaratkan.

Fungsi offset menghasilkan rujukan kepada julat yang diimbangi dari sel permulaan dengan bilangan baris dan lajur tertentu. Mari kita meneroka fungsinya melalui penjelasan ringkas diikuti oleh aplikasi praktikal.

• Aplikasi praktikal mengimbangi
  • Diimbangi dengan jumlah
  • Diimbangi dengan purata/maksimum/min
  • Membuat julat dinamik dengan mengimbangi
  • Mengimbangi dan vookup
• Batasan dan alternatif untuk mengimbangi

Fungsi Offset Excel: Sintaks dan Penggunaan Asas

Offset mengembalikan sel atau jarak yang diimbangi dari sel atau julat tertentu.

Sintaksinya adalah:

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

Tiga hujah pertama adalah wajib; Dua yang terakhir adalah pilihan. Semua argumen boleh menjadi rujukan sel atau keputusan formula.

• Argumen yang diperlukan:

  • reference : sel permulaan atau julat.
  • rows : Bilangan baris untuk mengimbangi (positif: ke bawah, negatif: naik).
  • cols : Bilangan lajur untuk mengimbangi (positif: kanan, negatif: kiri).

• Argumen Pilihan:

  • height : Bilangan baris dalam julat yang dikembalikan (mesti positif).
  • width : Bilangan lajur dalam julat yang dikembalikan (mesti positif).

Jika height atau width ditinggalkan, mereka lalai ke ketinggian atau lebar reference . Nota: Offset adalah fungsi yang tidak menentu, berpotensi memberi kesan kepada prestasi lembaran kerja.

Contoh mengimbangi mudah:

=OFFSET(A1,3,1)

Ini bergerak dari A1 tiga baris ke bawah dan satu lajur kanan, mengembalikan nilai dalam B4.

Pertimbangan utama:

• Offset tidak memindahkan sel; ia mengembalikan rujukan.
• Untuk pulangan pelbagai, rows dan cols merujuk kepada sel kiri atas.
• reference tidak sah (sel-sel tidak bersebelahan) menghasilkan #Value! ralat.
• Mengimbangi di luar kelebihan spreadsheet menghasilkan #REF! ralat.
• Mengimbangi berfungsi dalam fungsi yang menerima rujukan sel/julat (contohnya, SUM(OFFSET(...)) ).

Sebagai contoh, SUM(OFFSET(A1,3,1,1,3)) jumlah B4: D4.

Untuk mengimbangi sel semasa, menggabungkan INDIRECT , ADDRESS , ROW() , dan COLUMN() :

=SUM(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),3,1,1,3))

Mengapa menggunakan Offset?

Offset cemerlang dalam mewujudkan julat dinamik, menyesuaikan diri dengan perubahan data, dan merujuk julat berdasarkan sel permulaan tanpa mengetahui alamat yang tepat.

Aplikasi praktikal mengimbangi

Diimbangi dengan jumlah:

• Jumlah dinamik: untuk jumlah julat yang secara automatik termasuk baris baru yang ditambah di atas formula:

=SUM(B2:OFFSET(B9,-1,0))

• Merumuskan baris N yang terakhir:

=SUM(OFFSET(B1,COUNT(B:B)-E1 1,0,E1,1)) (atau menggunakan COUNTA untuk sel-sel yang tidak kosong)

Diimbangi dengan purata, max, min: menggantikan SUM dengan AVERAGE , MAX , atau MIN dalam contoh di atas untuk pengiraan dinamik yang serupa.

Membuat julat dinamik dengan mengimbangi:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

Ini mewujudkan julat dinamik berdasarkan bilangan sel bukan kosong dalam lajur A Sheet1. Laraskan tajuk seperti yang diperlukan.

Offset dan VLOOKUP: Offset membolehkan carian kiri atau ke atas, tidak mungkin dengan standard VLOOKUP atau HLOOKUP. (Formula kompleks yang ditinggalkan untuk keringkasan, lihat asal untuk butiran.)

Batasan dan alternatif untuk mengimbangi

Volatiliti Offset boleh memberi kesan kepada prestasi, dan formula kompleks sukar untuk debug. Pertimbangkan alternatif ini:

1. Jadual Excel: Jadual membuat julat dinamik yang secara automatik menyesuaikan diri dengan perubahan data.
2. Fungsi Indeks: Alternatif yang tidak menentu untuk rujukan pelbagai dinamik.
3. Fungsi tidak langsung: mewujudkan rujukan dinamik dari pelbagai sumber.

Buku Kerja Amalan yang boleh dimuat turun (pautan dalam asal) menyediakan pengalaman tangan.

Atas ialah kandungan terperinci Excel Offset Fungsi - Contoh dan Kegunaan Formula. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!