kirimpertanyaan

 

Belajar dan konsultasi masalah Excel      

Baca dulu

Hai yang muda bersemangat dan merdeka dalam belajar!

Kali ini saya ulas penjumlahan menggunakan rumus SUM dan OFFSET. Sebagai awal akan dikupas terlebih dahulu mengenai OFFSET. Ohya ... rumus ini ribet, buang -buang waktu anda untuk sebuah perkara yang sudah ada jawabannya. Yaa dengan ini anda diminta menuliskan rumus baru yang padahal, sebenarnya, sesungguhnya sudah Excel sediakan rumus paten SUM itu sendiri sejak dia kanak-kanak.

Ok, jika masih berminat mencoba jurus ini silakan dilanjut.

Syntax untuk rumus ini adalah =OFFSET(MulaiDariSelMana, BerapaBaris, BerapaKolom)

Yang aslinya adalah =OFFSET(reference, rows, cols)

Dibawah adalah uraian mengenai OFFSET

offset 1

Pada contoh pertama OFFSET menghasilkan nilai 9 dengan argumen

Referensi sel = D4

Baris = 1

Kolom =1

rumus untuk baris dan kolom juga dapat ditulis langsung tanpa referensi sel menjadi seperti ini =OFFSET(D4, 1, 1)

 

Contoh kedua OFFSET menghasilkan tulisan red dengan argumen baris dan kolom bernilai negatif

Referensi sel = D4

Baris = -1

Kolom = -2

Nilai positif pada argumen baris untuk sasaran baris di bawah referensi sel dan nilai positif pada argumen kolom untuk sasaran kolom disebelah kanan dari referensi sel.

Kebalikannya, negatif pada argumen baris untuk sasaran baris di atas referensi sel dan nilai negatif pada argumen kolom untuk sasaran kolom disebelah kiri dari referensi selnya.

Untuk contoh diatas rumus untuk baris dan kolom juga dapat ditulis langsung tanpa referensi sel menjadi =OFFSET(D4, -1, -2)

 

Untuk yang terakhir adalah contoh OFFSET dengan argumen baris dan kolom bernilai 0 (nol) sehingga didapatkan hasil sama dengan referensi sel D4 yaitu 5 (lima).

Selain syntax =OFFSET(reference, rows, cols) terdapat syntax kedua yaitu

=OFFSET(reference, rows, cols, [height], [width]) atau kita permudah artinya menjadi

=OFFSET(MulaiDariSelMana, BerapaBaris, BerapaKolom, BanyakBaris, BanyakKolom)

Syntax yang kedua ini akan menghasilkan sebuah range. Dengan keunikan rumus ini anda akan menemukan OFFSET dapat dikombinasi dengan rumus lain. Rumus ini akan menghasilkan ERROR jika tidak digandengkan dengan rumus lain juga akan tetap ERROR jika penggunaannya kurang tepat ... heee :)

Dibawah adalah contoh dari penggunaan syntax OFFSET keduanya yang digabungkan dengan rumus SUM

offset 2

Dapatkan file terkait untuk pemahaman lebih lanjut pada link ini https://goo.gl/7FokzQ

 Uraian contoh pertama adalah:

Menjumlahkan angka dalam range hasil rumus OFFSET, jika kita urai OFFSET(B3,0,1,2,2) yaitu

1. Range dengan referensi sel B3

a. baris bernilai 0 (nol) artinya tetap pada baris yang sama dengan B3

b. kolom bernilai 1 artinya bergerak 1 kolom dari posisi B3

2. kedua hasil a dan b diatas menghasilkan referensi sel C3

c. banyak baris 2

d. banyak kolom 2

3. kedua hasil c dan d menghasilkan referensi sel D4

maka range dihasilkan 2 dan 3 diatas adalah C3:D4

sehingga =SUM(OFFSET(B3,0,1,2,2)) menjadi =SUM(C3:D4)

hasil: 12

 

Uraian kedua,

Menjumlahkan angka dalam range hasil rumus OFFSET, jika kita urai OFFSET(B3,0,1,3,2) yaitu

1. Range dengan referensi sel B3

a. baris bernilai 0 (nol) artinya tetap pada baris yang sama dengan B3

b. kolom bernilai 1 artinya bergerak 1 kolom dari posisi B3

2. kedua hasil a dan b diatas menghasilkan referensi sel C3

c. banyak baris 3

d. banyak kolom 2

3. kedua hasil c dan d menghasilkan referensi sel D5

maka range dihasilkan 2 dan 3 diatas adalah C3:D5

sehingga =SUM(OFFSET(B3,0,1,3,2)) menjadi =SUM(C3:D5)

hasil: 27

 

Uraian ketiga,

Menjumlahkan angka dalam range hasil rumus OFFSET, jika kita urai OFFSET(B3,0,1,3,3) yaitu

1. Range dengan referensi sel B3

a. baris bernilai 0 (nol) artinya tetap pada baris yang sama dengan B3

b. kolom bernilai 1 artinya bergerak 1 kolom dari posisi B3

2. kedua hasil a dan b diatas menghasilkan referensi sel C3

c. banyak baris 3

d. banyak kolom 3

3. kedua hasil c dan d menghasilkan referensi sel E5

maka range dihasilkan 2 dan 3 diatas adalah C3:E5

sehingga =SUM(OFFSET(B3,0,1,3,3)) menjadi =SUM(C3:E5)

hasil: 45

 

Dapatkah OFFSET dikombinasikan dengan VLOOKUP, INDEX, MATCH, SUMIF, COUNTIF?

Jawabannya: BISA BANGET

Bagaimana caranya? ... Ikuti kami di tutorial selanjutnya. Semoga tutorial ini menambah daya khayal sehingga menyehatkan logika excel anda.

 

Sekian uraian mengenai OFFSET semoga bermanfaat.

Salam Excel

RA



Kitab VBA Excel Level Satu

Buat pemula yang serius pengen belajar MACRO VBA Excel, silahkan baca buku berikut:

Kitab VBA Excel Level Satu edisi 2 sEDISI 2 : Rp. 60.000

BELI VERSI CETAK KLIK DI SINI

BELI VERSI DIGITAL KLIK DI SINI

LIHAT PREVIEW KLIK DI SINI

 

Partners

logo baru local1news

Newsletter

Daftarkan email anda untuk mendapatkan update dari Klinik Excel