Cara Menggunakan Fungsi OFFSET di Excel


Fungsi OFFSET memungkinkan Anda untuk secara tidak langsung merujuk ke sel atau rentang sel di Excel. Dengan OFFSET, Anda dapat memilih titik referensi awal, lalu memasukkan alamat ke sel target daripada merujuk langsung ke mereka.


Ini membuat fungsi OFFSET sangat berguna dalam spreadsheet Excel dinamis. Anda dapat menggunakan fungsi OFFSET sendiri, tetapi potensi sebenarnya hanya muncul saat Anda menumpuknya di dalam fungsi lain di Excel. Baca terus untuk mempelajari semua tentang OFFSET di Excel dengan tiga contoh.


Apa Fungsi OFFSET di Excel?

Fungsi OFFSET mengembalikan referensi ke sel atau rentang sel. OFFSET mengambil lima parameter, dan sintaksnya adalah sebagai berikut:

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

OFFSET akan mengambil posisi referensi sel, dan kemudian pindah dari sana dengan jumlah baris dan colslalu kembalikan referensi sel tujuan.

jika tinggi dan lebar parameter diatur ke lebih dari satu, maka rentang sel di area itu akan dikembalikan. Tinggi dan lebar adalah dua parameter opsional. Jika Anda membiarkannya kosong, OFFSET akan membacanya sebagai 1 dan mengembalikan referensi sel tunggal.

Cara Menggunakan Fungsi OFFSET di Excel

Untuk menggunakan OFFSET di Excel, Anda perlu memasukkan tiga parameter yang diperlukan. Setelah Anda mengatur sel referensi, OFFSET akan berpindah dari sel itu ke sel di bawah ini dengan jumlah baris parameter. Ini akan bergerak ke kanan dengan jumlah cols parameter.

Dari sana, OFFSET akan mengembalikan referensi ke sel tujuan. Jika Anda mengatur parameter untuk tinggi dan lebar juga, OFFSET akan mengembalikan referensi ke rentang sel, di mana sel tujuan awal akan menjadi sel kiri atas.

Ini mungkin terdengar rumit di atas kertas, tetapi jauh lebih mudah untuk dipahami dalam tindakan. Mari kita lihat bagaimana fungsi OFFSET bekerja dengan beberapa contoh.

1. Contoh Fungsi OFFSET: Mengacu pada Sel Tunggal

Untuk memulai, mari gunakan fungsi OFFSET sendiri untuk memahami cara menavigasi spreadsheet. Dalam contoh ini, kami memiliki banyak angka dalam spreadsheet Excel. Kita akan menggunakan fungsi OFFSET untuk merujuk ke C4.

Ketika fungsi OFFSET digunakan dengan jelas, itu akan menampilkan konten sel tujuan. Jadi kita harus mendapatkan isi sel C4, yang merupakan nomor lima, setelah kita memasukkan rumusnya. Mari kita mulai.

  1. Pilih sel untuk memasukkan rumus. Kita akan menggunakan sel G1.
  2. Di bilah rumus, masukkan rumus di bawah ini:
    =OFFSET(A1, 3, 2)

    Rumus ini memanggil MENGIMBANGI berfungsi untuk mengambil posisi sel A1lalu turun dengan 3 sel, dan kemudian bergerak ke kanan dengan 2 sel untuk mencapai sel C4.

  3. Tekan Memasuki.

Setelah Anda menekan Memasuki dan rumusnya berlaku, sel akan kembali 5. Ini adalah nomor dari sel C4, jadi rumus Anda berhasil! Anda juga dapat menggunakan fitur evaluasi untuk lebih memahami rumus Excel, seperti OFFSET.

2. Contoh Fungsi OFFSET: Mengacu pada Rentang Sel

Menggunakan dua parameter opsional, tinggi dan lebar, Anda juga dapat mengembalikan rentang sel dengan fungsi OFFSET di Excel. Mari kita lanjutkan di mana kita tinggalkan dari contoh terakhir.

Kali ini, kita akan mengembalikan sel C4 ke D9 menggunakan OFFSET di Excel. Sama seperti sebelumnya, kita akan menggunakan A1 sebagai titik referensi awal kita.

  1. Pilih sel tempat Anda ingin memasukkan rumus. Kita akan menggunakan sel G1 lagi.
  2. Di bilah rumus, masukkan rumus di bawah ini:
    =OFFSET(A1, 3, 2, 6, 2)

    Rumus ini bekerja seperti yang sebelumnya, kecuali bahwa setelah mencapai sel tujuan (C4), ia akan mengambil 6 baris dan 2 kolom daripada sel target saja. C4 akan menjadi sel pertama dalam rentang baru.

  3. Tekan Memasuki.

Sekarang rentang sel C4 hingga D9 akan muncul di tempat Anda memasukkan rumus. Outputnya akan berupa array.

3. Contoh Fungsi OFFSET: Rumus Majemuk

Dua contoh sebelumnya adalah pemanasan untuk melihat bagaimana OFFSET bekerja di Excel. OFFSET biasanya bersarang dengan fungsi Excel lainnya dalam skenario praktis. Mari kita tunjukkan ini dengan contoh baru.

Dalam contoh ini, kami memiliki pendapatan dari proyek sampingan sepanjang tahun 2017 hingga 2021. Di sisi atas, kami memiliki sel yang seharusnya menunjukkan total pendapatan pada tahun-tahun tertentu mulai dari 2021.

Tujuannya di sini adalah untuk membuat spreadsheet dinamis menggunakan OFFSET di Excel. Saat Anda mengubah jumlah tahun untuk total, rumus yang menghitung nilai ini juga harus diperbarui.

Untuk mencapai tujuan ini, kita akan menggunakan fungsi OFFSET bersama dengan fungsi SUM. Daripada merujuk ke sel secara langsung, kita akan menggunakan OFFSET. Dengan cara ini, kita bisa secara dinamis mendapatkan rentang sel yang akan dimasukkan ke dalam SUM.

Dalam contoh ini, tahun 2021 ada di sel H5, dan jumlah tahun untuk total ada di sel C1. Kami telah menggunakan pemformatan khusus di sel yang berisi tahun. Nilai sebenarnya adalah jumlah tahun, dan teks Tahun adalah akhiran. Mari kita mulai dengan membuat rumus untuk menghitung total. Kita akan menggunakan fungsi SUM di Excel.

  1. Pilih sel tempat Anda ingin menghitung total.
  2. Di bilah rumus, masukkan rumus di bawah ini:
    =SUM(OFFSET(H5,0, 0, 1, -C1))

    Rumus ini terdiri dari MENGIMBANGI dan JUMLAH. Pada langkah pertama, OFFSET akan masuk ke sel H5. Dari sana, itu akan bergerak 0 baris dan 0 kolom, dan kemudian akan mengembalikan rentang sel mulai dari H5. Seleksi ini akan 1 tinggi sel, dan -C1 sel dengan lebar. Ini berarti OFFSET akan mengembalikan rentang mulai dari H5 dan bergerak ke kiri dengan C1 sel.

  3. Pada langkah terakhir, fungsi SUM akan menambahkan angka-angka dalam rentang bersama-sama dan menampilkannya.
  4. Tekan Memasuki.

Jumlah yang Anda dapatkan sebagai total sekarang tergantung pada jumlah tahun yang Anda masukkan. Jika jumlah tahun diatur ke 1, maka Anda akan mendapatkan total untuk tahun 2021 saja.

Perhatikan bahwa total pembaruan seketika setelah Anda mengubah jumlah tahun. Jika Anda mengubah jumlah tahun menjadi 7, fungsi SUM akan menghitung total dari 2015 hingga 2021.

Spreadsheet Dinamis Dengan OFFSET

Fungsi OFFSET di Excel memiliki banyak parameter, dan itu dapat menyebabkan kebingungan sendiri, tetapi begitu Anda menggunakannya, Anda akan mempelajari bahwa, seperti banyak fungsi Excel lainnya, OFFSET adalah fungsi yang ramah.

Meskipun tidak banyak gunanya OFFSET sendiri, ketika digabungkan dengan fungsi Excel lainnya, OFFSET memberi Anda kekuatan untuk membuat spreadsheet yang dinamis dan canggih.


https://www.makeuseof.com/use-offset-function-excel/

Leave a Comment