Fungsi OFFSET di Excel

Fungsi OFFSET di excel mengembalikan nilai sel atau rentang (sel yang berdekatan) yang merupakan jumlah baris dan kolom tertentu dari titik referensi. Titik referensi ini adalah sel awal yang disediakan sebagai argumen untuk fungsi tersebut. Dimulai dari titik referensi, fungsi tersebut menghasilkan titik akhir (sel akhir atau rentang) yang nilainya dikembalikan.

Misalnya, dataset diberikan sebagai berikut:

• Kolom A terdiri dari 2, 4, dan 3 dalam rentang A1:A3.
• Kolom B terdiri dari 1, 5, dan 6 dalam rentang B1:B3.

Rumus ‘=OFFSET(A1,2,1)’ mengembalikan 6. Fungsi excel OFFSET mulai menghitung referensi sel dari sel A1 (titik awal). Selanjutnya, memindahkan dua baris ke bawah (ke sel A3) dan 1 kolom ke kanan (ke sel B3).

Titik akhirnya adalah sel B3. Jadi, fungsi OFFSET mengembalikan nilai sel B3, yaitu 6.

Tujuan penggunaan fungsi OFFSET adalah untuk mencari nilai sel yang titik awalnya diketahui. Fungsi ini sangat membantu dalam kasus di mana kumpulan data diperbarui secara berkala.

Fungsi OFFSET adalah bagian dari fungsi Pencarian dan Referensi Excel.

Sintaks Fungsi OFFSET Excel

Sintaks fungsi ditunjukkan pada gambar berikut:

Fungsi menerima argumen wajib berikut:

• Referensi: Ini adalah titik awal dari mana perhitungan sel akhir (atau rentang) alamat dimulai. Itu dapat dimasukkan sebagai sel atau rentang sel yang berdekatan.
• Baris: Ini adalah jumlah baris yang perlu dipindahkan oleh fungsi. Gerakannya bisa di atas atau di bawah titik referensi (titik awal). Jika argumen ini positif, fungsi bergerak di bawah titik referensi. Jika argumen ini negatif, fungsi bergerak di atas titik referensi.
• Cols: Ini adalah jumlah kolom yang perlu dipindahkan oleh fungsi. Gerakannya bisa ke kanan atau ke kiri dari titik referensi (titik awal). Jika argumen ini positif, fungsinya berpindah ke kanan titik referensi. Jika argumen ini negatif, fungsinya berpindah ke kiri titik referensi.

Fungsi menerima argumen opsional berikut:

• Tinggi: Ini adalah tinggi (jumlah baris) rentang yang akan dikembalikan.
• Lebar: Ini adalah lebar (jumlah kolom) rentang yang akan dikembalikan.

Argumen ‘tinggi’ dan ‘lebar’ harus selalu bilangan positif. Jika salah satu dari argumen ini dihilangkan, tinggi dan lebar referensi (titik awal) diasumsikan.

Catatan 1: Argumen ‘baris’ dan ‘kolom’ ditentukan menggunakan titik referensi sebagai basis.

Catatan 2: Jika argumen ‘height’ dan ‘width’ adalah 1, fungsi excel OFFSET mengembalikan nilai sel. Ini karena rentang 1 baris kali 1 kolom adalah dimensi (ukuran) sel tunggal.

Catatan 3: Fungsi OFFSET tidak memindahkan sel atau rentang apa pun. Itu hanya mengembalikan nilai alamat sel (atau rentang) yang dihitungnya.

Bagaimana cara menggunakan Fungsi OFFSET di Excel?

Fungsi OFFSET adalah fungsi bawaan Excel. Mari kita pahami cara kerjanya dengan bantuan beberapa contoh.

Setiap contoh mencakup kasus yang berbeda dari fungsi excel OFFSET, termasuk penjelasannya. Dalam dua contoh terakhir (contoh #4 dan #5), fungsi OFFSET telah digabungkan dengan fungsi aritmatika.

Contoh #1–Titik Akhir adalah Sel Data

Tabel berikut menunjukkan nama lima pembalap dan peringkat yang sesuai yang diperoleh mereka. Kami ingin mencari nama pembalap ketiga dengan bantuan fungsi OFFSET.

Langkah 1: Masukkan rumus OFFSET berikut di sel F4.

‘=OFFSET(B3,2,1)’

Langkah 2: Tekan tombol ‘Enter’. Output muncul di sel F4. Makanya, ‘Nadal’ menjadi pebalap yang menduduki peringkat ketiga dalam balapan tersebut.

Penjelasan: Argumen pertama dari fungsi excel OFFSET adalah sel B3 yang merupakan titik referensi (titik awal).

Argumen ‘baris’ adalah 2. Jadi, fungsi bergerak dua baris di bawah sel B3, yaitu sel B5. Argumen ‘cols’ adalah 1. Jadi, fungsi memindahkan satu kolom ke kanan sel B3, yaitu sel C5.

Nilai sel yang dihasilkan (titik akhir) C5 adalah ‘Nadal.’ Oleh karena itu, keluaran di sel F4 adalah ‘Nadal.’ Titik akhir ditunjukkan pada gambar berikut.

Contoh #2–Titik Akhir adalah Sel Kosong

Bekerja pada data contoh #1, kami ingin fungsi OFFSET mengembalikan nilai sel kosong D5.

Langkah 1: Masukkan rumus berikut di sel F5.

‘=OFFSET(B3,2,2)’

Langkah 2: Tekan tombol ‘Enter’. Output 0 muncul di sel F5. Karenanya, fungsi OFFSET mengembalikan nilai sel kosong sebagai nol.

Penjelasan: Pada rumus excel OFFSET yang diberikan, titik referensi (titik awal) adalah sel B3.

Karena argumen ‘baris’ adalah 2, fungsi memindahkan dua baris ke bawah ke sel B5. Argumen ‘cols’ juga 2. Jadi, fungsi memindahkan dua kolom ke kanan sel B3, yaitu sel D5.

Sel yang dihasilkan (titik akhir) adalah D5. Karena sel ini kosong, keluaran yang dikembalikan oleh fungsi OFFSET adalah 0. Titik akhir ditunjukkan pada gambar berikut.

Contoh #3–Titik Akhir adalah Sel yang Tidak Ada

Bekerja pada data contoh #1, kami ingin fungsi OFFSET mengembalikan nilai sel (di sebelah kiri sel A1) yang tidak ada.

Langkah 1: Masukkan rumus excel OFFSET berikut di sel F6.

‘=OFFSET(B3,-2,-2)’

Langkah 2: Tekan tombol ‘Enter’. Outputnya muncul di sel F6. Oleh karena itu, nilai sel yang tidak ada adalah ‘#REF!’ kesalahan.

Penjelasan: Dalam contoh ini, sel B3 adalah titik referensi.

Argumen ‘baris’ adalah -2. Jadi, fungsinya bergerak dua baris di atas sel B3, yaitu sel B1. Argumen ‘cols’ juga -2. Jadi, fungsinya memindahkan dua kolom ke kiri sel B3. Namun, tidak ada kolom di sebelah kiri kolom A.

Oleh karena itu, tidak ada sel yang dihasilkan (titik akhir). Jadi, hasilnya adalah ‘#REF!’ kesalahan, seperti yang ditunjukkan pada gambar berikut. Ikon berwarna kuning menunjukkan ‘kesalahan referensi sel tidak valid’.

Catatan: Jika titik akhir fungsi OFFSET adalah sel yang berada di luar jangkauan atau tidak ada, ini mengembalikan kesalahan ‘#REF’.

Contoh #4–Jumlah Nilai Alamat Rentang yang Dikembalikan

Tabel berikut menunjukkan lima saham dengan pengembalian maksimum dan minimum (dalam $). Kami ingin menjumlahkan pengembalian maksimum dari saham-saham ini. Gunakan fungsi excel OFFSET dikombinasikan dengan fungsi SUM Fungsi SUM Fungsi SUM di excel menambahkan nilai numerik dalam rentang sel. Dikategorikan di bawah fungsi Matematika dan Trigonometri, dimasukkan dengan mengetikkan ‘=SUM’ diikuti dengan nilai yang akan dijumlahkan. Nilai yang diberikan ke fungsi dapat berupa angka, referensi sel, atau rentang.Baca lebih lanjut.

Langkah 1: Masukkan rumus berikut di sel F3.

‘=JUMLAH(OFFSET(C2,0,0,5,1))’

Langkah 2: Tekan tombol ‘Enter’. Output muncul di sel F3. Oleh karena itu, jumlah pengembalian maksimum dari saham yang diberikan adalah $449.

Penjelasan: Sel C2 adalah titik referensi.

Baik argumen ‘baris’ dan ‘kolom’ adalah nol. Ini menyiratkan bahwa fungsi OFFSET tidak akan bergerak ke atas dan ke bawah atau ke kiri dan ke kanan.

Argumen ‘height’ adalah 5, menyiratkan bahwa fungsi OFFSET akan menjumlahkan lima baris mulai dari sel C2. Ini adalah kisaran C2:C6.

Argumen ‘lebar’ adalah 1, menyiratkan bahwa fungsi OFFSET akan menjumlahkan nilai dari satu kolom (kolom C).

Dengan argumen ‘tinggi’ dan ‘lebar’ yang diberikan, rumus sebelumnya (dimasukkan pada langkah 1) mengembalikan jumlah nilai dari rentang 5 baris kali 1 kolom. Ini menjumlahkan nilai rentang yang dikembalikan (5-baris dengan 1-kolom) karena OFFSET diapit dalam fungsi SUM.

Oleh karena itu, jumlah semua nilai kolom C dihitung sebagai 98+92+89+88+82=449.

Contoh #5–Rata-rata Nilai dari Alamat Rentang yang Dikembalikan

Bekerja pada data dari contoh #4, kami ingin menghitung pengembalian rata-rata saham ‘S1.’ Gunakan fungsi pengecualian OFFSET yang digabungkan dengan fungsi AVERAGEFungsi AVERAGEFungsi AVERAGE di Excel memberikan rata-rata aritmatika dari kumpulan nilai numerik yang disediakan. Rumus ini dikategorikan sebagai Fungsi Statistik. Rumus rata-ratanya adalah =AVERAGE(baca selengkapnya.

Langkah 1: Masukkan rumus berikut di sel F5.

‘=RATA-RATA(OFFSET(C2,0,0,1,2))’

Langkah 2: Tekan tombol ‘Enter’. Outputnya muncul di sel F5. Oleh karena itu, pengembalian rata-rata saham ‘S1’ adalah $74.

Penjelasan: Sel C2 adalah titik referensi.

Baik argumen ‘baris’ dan ‘kolom’ adalah nol. Ini menyiratkan bahwa fungsi OFFSET tidak akan bergerak ke segala arah.

Argumen ‘tinggi’ adalah 1. Ini berarti bahwa fungsi OFFSET hanya akan mempertimbangkan satu baris (baris 2) untuk menghitung rata-rata.

Argumen ‘lebar’ adalah 2. Ini menyiratkan bahwa fungsi excel OFFSET akan menghitung rata-rata dua kolom (kolom C dan D).

Dengan argumen ‘tinggi’ dan ‘lebar’ yang diberikan, rumus sebelumnya (dimasukkan pada langkah 1) mengembalikan rata-rata rentang 1 baris kali 2 kolom.

Dalam rumus sebelumnya, OFFSET disertakan dalam fungsi AVERAGE. Jadi, nilai rentang yang dikembalikan (1 baris kali 2 kolom) digunakan untuk menghitung rata-rata. Oleh karena itu, rata-rata nilai dalam sel C2 dan D2 dihitung sebagai (98+50)/2=74.

Rentang Dikembalikan oleh Fungsi OFFSET Excel

Fungsi OFFSET juga dapat mengembalikan nilai rentang sel yang berdekatan. Untuk itu, langkah-langkah yang harus dilakukan adalah sebagai berikut:

• Pilih rentang kosong dengan ukuran yang sama seperti yang ditentukan oleh argumen ‘height’ dan ‘width’ dari fungsi OFFSET di excel.
• Masukkan rumus OFFSET di sel paling kiri paling atas dari rentang kosong.
• Tekan ‘Ctrl+Shift+Enter.’ Tanda kurung kurawal muncul di awal dan akhir rumus.

Nilai rentang sel diperoleh sebagai output dari fungsi excel OFFSET.

Catatan: Untuk mendapatkan nilai rentang sel, penting untuk menyediakan argumen ‘tinggi’ (jumlah baris) dan ‘lebar’ (jumlah kolom).

Pertanyaan yang Sering Diajukan

1. Tentukan fungsi OFFSET Excel.

Fungsi OFFSET di excel mengembalikan nilai sel tunggal atau rentang sel yang berdekatan. Alamat sel ini (atau rentang) dihitung dari titik referensi (sel awal) yang diberikan sebagai argumen. Titik referensi ini diambil sebagai dasar untuk menentukan argumen ‘baris’ dan ‘kolom’.

Sintaks fungsi excel OFFSET dinyatakan sebagai berikut:

‘=OFFSET(referensi,baris,kolom,[tinggi],[lebar])’

Fungsi OFFSET menerima argumen berikut:

Referensi: Ini adalah titik awal atau basis dari di mana perhitungan alamat sel (atau rentang) dimulai. Itu bisa berupa sel tunggal atau rentang sel yang berdekatan.

Baris: Ini adalah jumlah baris yang perlu dipindahkan oleh fungsi. Itu dihitung dari titik awal. Angka positif menyiratkan pergerakan ke bawah, sedangkan angka negatif menyiratkan pergerakan ke atas.

Cols: Ini adalah jumlah kolom yang perlu dipindahkan oleh fungsi. Ini juga dihitung dari titik awal. Angka positif menyiratkan gerakan ke kanan, sedangkan angka negatif menyiratkan gerakan ke kiri.

Tinggi: Ini adalah tinggi (jumlah baris) rentang yang akan dikembalikan.

Lebar: Ini adalah lebar (jumlah kolom) rentang yang akan dikembalikan.

Tiga argumen pertama bersifat wajib, sedangkan dua argumen terakhir bersifat opsional.

Catatan: Untuk mendapatkan nilai rentang sel, pilih rentang kosong dengan tinggi dan lebar yang sama seperti yang ditentukan dalam argumen. Selanjutnya, masukkan rumus OFFSET dan tekan ‘Ctrl+Shift+Enter.’

2. Apa rumus SUM OFFSET di Excel?

Dalam rumus SUM OFFSET, OFFSET diapit dalam fungsi SUM dengan cara berikut:

‘=SUM(sel pertama:(OFFSET(sel berisi total,-1,0)))’

‘Sel pertama’ adalah yang pertama alamat sel yang nilainya akan ditambahkan. ‘Sel berisi total’ adalah sel keluaran (berisi total) di akhir sel numerik. Angka -1 dan 0 masing-masing adalah argumen ‘baris’ dan ‘kolom’.

Rumus yang diberikan membantu menemukan jumlah rentang sel yang berdekatan. Fungsi OFFSET menghitung alamat rentang. Fungsi SUM menjumlahkan nilai rentang.

Sebagai contoh, mari kita jumlahkan nilai kolom B yang terdiri dari nilai 20, 35, 42, dan 11 dalam rentang B1:B4. Langkah-langkahnya adalah sebagai berikut:

• Masukkan rumus berikut di sel B5. ‘=SUM(B1:(OFFSET(B5,-1,0)))’

• Tekan tombol ‘Enter’.

Keluaran di sel B5 adalah 108.

3. Mengapa fungsi OFFSET harus digunakan dan kapan harus digunakan di Excel?

Alasan penggunaan fungsi OFFSET tercantum sebagai berikut:

• Fungsi ini membantu menemukan nilai sel (atau rentang) yang alamatnya tidak diketahui, tetapi titik awalnya diketahui. • Bekerja dengan data yang baris atau kolomnya ditambahkan secara teratur. • Membantu membuat rentang dinamis untuk PivotTable dan PivotChart.
• Dapat dikombinasikan dengan fungsi lain yang membutuhkan alamat sel untuk bekerja.

Fungsi excel OFFSET digunakan dalam situasi berikut:

• Untuk memasukkan nilai sel yang baru disisipkan ke dalam to tal suatu rentang, OFFSET digunakan dengan fungsi SUM.
• Untuk mencari rata-rata, maksimum, atau minimum dengan memasukkan penambahan atau penghapusan baris, digunakan OFFSET dengan fungsi AVERAGE, MAX, dan MIN. • Untuk membuat daftar drop-down dinamis (pembaruan dengan perubahan pada daftar sumber), OFFSET digunakan dengan fungsi COUNTA.
• Untuk mengatasi keterbatasan VLOOKUP (tidak bisa melihat ke kiri) dan HLOOKUP (tidak bisa melihat ke atas), OFFSET digunakan dengan fungsi MATCH.

Artikel yang Direkomendasikan

Ini telah menjadi panduan untuk fungsi OFFSET di Excel. Berikut kita bahas cara menggunakan rumus excel OFFSET beserta contoh step by stepnya. Anda juga dapat melihat fungsi Excel yang berguna ini–

• OFFSET di VBAOFFSET Pada VBAVBA fungsi OFFSET memudahkan pengguna untuk berpindah ke bawah atau kembali ke sel tertentu pada jarak tertentu, yaitu pada jarak sejumlah baris dan kolom tertentu dari sel saat ini.baca lebih lanjut
• Fungsi FIND di Excel Fungsi FIND Di ExcelFungsi Find di excel menemukan lokasi karakter atau substring dalam string teks. Dengan kata lain ia menemukan kemunculan suatu teks dalam teks lain, karena memberi kita posisi, keluaran yang dikembalikan oleh fungsi ini adalah bilangan bulat.baca lebih lanjut
• Bagan Gelembung di ExcelBagan Gelembung Di ExcelDi Excel, bagan gelembung adalah tipe plot sebar yang menggunakan gelembung untuk menampilkan nilai dan perbandingan. Seperti plot sebar, bagan gelembung membandingkan data pada sumbu horizontal dan vertikal.baca lebih lanjut
• Tally Chart di ExcelTally Chart Di ExcelTally chart adalah salah satu teknik untuk mengumpulkan data berharga melalui tanda penghitungan dari kumpulan data. Tanda penghitungan adalah angka, kejadian, atau frekuensi total yang diukur untuk suatu kategori dari pengamatan yang dikelompokkan.baca lebih lanjut