Membuat Rumus menggunakan ACUAN (19)

Anda dapat membuat suatu rumus dengan menggunakan acuan, dimana MS Excel akan mengambil nilai atau data yang terdapat pada alamat sel yang Anda tuju dan menggunakannya dalam proses perhitungan pada rumus tersebut. Anda juga dapat mengambil data yang ada pada alamat sel dan menggunakannya sebagai acuan. Termasuk juga pada beberapa rumus dalam Worksheet.

Pengertian Acuan

Ada tiga bentuk acuan yang dikenal MS Excel, yaitu : relative references  (acuan alamat sel relatif), absolute references (acuan alamat sel absolut) dan mixed references (acuan alamat sel campuran). Dengan penjelasan sebagai berikut :

1)    Relative reference, atau dikenal juga dengan istilah alamat sel relatif. Contoh bentuk penulisannya adalah A1. MS Excel menggunakan alamat sel A1 sebagai acuan untuk penulisan alamat sel lain pada rumus secara berurutan menurut baris dan kolom.

2)    Absolut references, atau acuan alamat sel absolut yang merupakan alamat sel tetap. Contoh penulisannya adalah $A$1. Acuan alamat sel absolut ditandai dengan simbol $ di depan huruf kolom dan baris. Anda dapat menggunakan acuan alamat sel absolut di dalam rumus dan MS Excel akan mengambil nilai yang ada pada alamat sel tersebut secara tetap.

3)    Mixed references, atau acuan alamat sel campuran.  Acuan alamat sel campuran dapat berbentuk alamat sel absolut baris atau absolut kolom. Contoh penulisan alamat sel absolut kolom adalah $A1 dimana posisi kolom absolut sedang alamat sel baris relatif. Untuk alamat sel absolut baris adalah A$1, dimana alamat sel kolom bersifat relatif dan alamat sel baris absolut.

Pengertian Operator Acuan

Dalam MS Excel terdapat tiga tipe operator acuan, yaitu :

1)    Range, Membuat acuan yang isinya semua sel yang berada di antara dua acuan. Di antara dua alamat sel acuan tersebut dibatasi dengan operator titik dua atau colon. Contoh : =SUM(A1:C12).

2)    Union. Membuat satu acuan yang isinya dua atau lebih acuan. Penulisan antara satu acuan dengan acuan lainnya dibatasi operator koma. Contoh: =SUM(A1,A12,I15,D2,E3) atau dengan contoh lain : =SUM(A1:A12,B2,D4,E5).

3)    Intersection.  Alamat sel atau range yang merupakan perpotongan dari dua buah range. Penulisan kedua range tersebut dibatasi oleh spasi.

Fungsi-fungsi (Functions Category dan Functions Name) yang digunakan dalam Latihan Otomatisasi Akuntansi menggunakan MS EXCEL 97 ini sebagai berikut:

FUNGSI: SUM( )

Bentuk	:	SUM (X1,X2,X3…….)
Keterangan	:	Ä Menjumlahkan nilai numerik dengan nilai numerik lain. Ä Argumen X1, X2, X3…. Adalah nilai numerik, alamat sel atau range yang berisi data numerik atau range-range yang mengandung data numerik. Ä Fungsi SUM hanya menjumlahkan nilai-nilai numerik yang terdapat dalam suatu range. Jumlah argumen bisa mencapai 30 argumen.
Contoh	:	Ä = SUM (21,9,1971)                            > 2001 Ä = SUM (A1:A5, C1:C5) (menjumlahkan nilai numerik dalam range A1:A5 dan range C1:C5.)

FUNGSI: SUMIF ( )

Bentuk	:	SUMIF(X,Y,Z)
Keterangan	:	Ä Menjumlahkan data atau nilai numerik yang memenuhi suatu kriteria tertentu. Ä Argumen X yang digunakan untuk menentukan range yang akan diuji. Ä Argumen Y yang digunakan untuk menentukan syarat. Ä Argumen Z adalah range data yang akan dijumlahkan.
Contoh	:

FUNGSI : CONCATENATE ( )

Bentuk	:	CONCATENATE (X1,X2,X3……)
Keterangan	:	Ä Argumen X1,X2,X3…. Merupakan teks yang akan digabungkan kedalam sebuah teks tunggal teks. Argumen tersebut dapat berupa suatu teks, bilangan atau alamat sel. Jumlah argumen yang dapat anda bentuk bisa mencapai 30 argumen. Ä Operator dengan simbol dan (&) dapat menggantikan fungsi CONCATENATE  yaitu untuk menggabungkan beberapa teks sehingga menjadi suatu teks.
Contoh	:	Ä = CONCATENATE (“Total”,”Nilai”)  >“Total”,”Nilai”. Fungsi diatas dapat juga ditulis dengan perintah : =“Total”& ”&”Nilai”. Ä Jika sel C2 berisi teks “LKW”, sel C5 berisi teks “GERJALIBIN” dan C8 berisi nilai 0358773352. =CONCATENATE(C2,”-“,C5,”telp. “,C8) >”LKW-GERJALIBIN telp. 0358773352.”

FUNGSI : REPT ( )

Bentuk	:	REPT (X,Y)
Keterangan	:	Ä Mengulangi penulisan  suatu data teks. Ä Argumen X merupakan data teks yang penulisannya diapit dengan tanda petik ganda(“). Argumen X dapat diwakili alamat sel. Ä Argumen Y merupakan bilangan numerik positif yang menunjukkan banyaknya pengulangan data teks dan nilainya harus lebih besar dari nol (0), jika nilainya nol (0), REPT akan menhasilkan “”(teks kosong). Ä Hasil dari fungsi REPT tidak bisa lebih dari 255 karakter.
Contoh	:	Ä = REPT (“Romy”,3)                >”Romy Romy Romy”

FUNGSI : REPLACE ( )

Bentuk	:	REPLACE (X,Y1,Y2,Z)
Keterangan	:	Mengganti karakter dari suatu data teks pada posisi tertentu. Ä  Argumen X merupakan data teks yang penulisannya diapit dengan tanda petik ganda (“). Argumen X dapat diwakili oleh alamat sel. Ä  Argumen Y1 adalah nilai numerik yang menunjukkan posisi awal teks yang akan diganti. Ä  Argumen Y2 menunjukkan jumlah atau banyaknya karakter yang akan diganti. Ä  Argumen Z adalah teks pengganti.
Contoh	:	Ä  =REPLACE (“November”,4,5,” “)              -> Nov Ä  =REPLACE (“Agustin”,6,2,”us“)                -> Agustus Ä  =REPLACE (“Vitral”,4,3,”al“)                    -> Vital

FUNGSI : AND ( )

Bentuk	:	AND (X1,X2,X3, …..)
Keterangan	:	Ä Menghasilkan TRUE jika semua argumen bernilai benar (true); menghasilkan FALSE jika satu atau lebih argumen bernilai salah (false). Ä Argumen X1, X2, X3, …. Merupakan kondisi yang akan diuji atau alamat sel yang mengandung nilai logika yang menghasilkan nilai benar (true) atau salah (false). Ä Argumen kondisi dapat dibentuk sampai batas maksimal 30 argumen.
Contoh	:	Ä = AND (TRUE, FALSE)                                            >  FALSE = AND (2+2=4,2+3=5)                                       > TRUE Ä jika B1:B2 berisi nilai TRUE, FALSE, dan TRUE : = AND (B1:B2)                                                  > FALSE Ä jika B4 mengandung bilangan antara 1 dan 100: =AND (1<B4,B4<100)                                        > TRUE

FUNGSI: OR ( )

Bentuk	:	OR (X1,X2,X3, ….)
Keterangan	:	Ä Menghasilkan TRUE jika beberapa argumen bernilai benar (true); menghasilkan FALSE jika semua argumen bernilai salah (false). Ä Argumen X1,X2,X3, …. Merupakan kondisi-kondisi yang akan diuji  atau alamat sel yang mengandung nilai-nilai logika yang akan menghasilkan nilai benar (true) atau salah (false). Ä Argumen kondisi dapat dibentuk sampai batas maksimal 30 argumen.
Contoh	:	Ä = OR (TRUE)                         > TRUE = OR (1+1=1,2+2=5)                        > FALSE Ä jika A1: A3 berisi nilai TRUE, FALSE, dan TRUE : = OR(A1:A2)                         >TRUE

FUNGSI : IF ( )

Bentuk	:	IF (X,Y,Z)
Keterangan	:	Ä  Menghasilkan satu nilai jika suatu kondisi yang anda uji bernilai benar (true) dan menghasilkan nilai lain jika kondisi yang akan diuji bernilai salah (false). Ä  Argumen X adalah beberapa nilai atau ekpresi yang dapat diuji untuk menghasilkan nilai TRUE atau FALSE. Ä  Agumen Y adalah suatu nilai yang akan dihasilkan jika logika bernilai TRUE. Jika logika yang diuji bernilai TRUE dan argumen Y dihilangkan nilai TRUE yang akan ditampilkan. Ä  Argumen Z adalah suatu nilai yang akan dihasilkan jika logika bernilai FALSE. Jika logika yang diuji bernilai FALSE dan argumen Z dihilangkan. Nilai FALSE yang akan ditampilkan.
Contoh	:	Ä = IF (A1=100,SUM(B1:B5), MAX (B1:B5)) jika sel A1 berisi nilai 50, fungsi MAX akan dijalankan untuk menghasilkan nilai tertinggi range B1:B5. Jika sel A1 berisi nilai 100, fungsi SUM akan dijalankan untuk menghitung nilai total range B1:B5. Ä = IF (B2=”A”,”Memuaskan”, IF(B2=”B”.Baik”,”Cukup”)) jika sel B2 berisi nilai “A”, fungsi IF menghasilkan teks “Memuaskan”. Jika sel B2 berisi nilai “B”, fungsi IF menghasilkan teks “Baik”, jika sel B2 tidak berisi nilai “A” atau “B”, fungsi IF menghasilkan nilai “Cukup”.

FUNGSI : VLOOKUP ( )

Bentuk	:	VLOOKUP ( kunci, range, offset)
Keterangan	:	Ä Mencari nilai dalam suatu kolom tabel atau digunakan untuk membaca tabel vertikal dengan menggunakan nilai kunci pembanding, serta offset kolom yang menunjukkan posisi data yang dibaca pada kolom tertentu. Ä Argumen kunci adalah berupa alamat sel, yang digunakan sebagai kunci pembacaan tabel. ¨     Microsoft Excel akan menampilkan kode kesalahan #N/A, jika nilai kunci tidak sesuai dengan nilai yang ada pada kolom pertama range tabel. Misalnya kunci berbentuk numerik, sedangkan nilai kolom pertama tabel berbentuk data teks. ¨     Jika kunci memiliki harga lebih besar dari nilai yang ada pada kolom pertama tabel Microsoft Excel akan membaca nilai pembanding paling besar yang ada pada kolom pertama tabel array. Ä Argumen range adalah nama atau range dari tabel yang akan dibaca. Kolom pertama berisi nilai pembanding yang akan dibandingkan dengan nilai kunci. Ä Argumen offset adalah data numerik yang digunakan sebagai pembacaan pada kolom data. ¨     Nilai offset kolom dimulai dengan angka 1 yang berarti membaca data kolom pertama. Angka 2 membaca data pada kolom ke-2 dan seterusnya. ¨     Jika nilai offset lebih besar dari jumlah kolom yang ada pada range tabel, maka MS Excel akan menampilkan kode kesalahan #REF! ¨     Jika nilai offset lebih kecil dari 1, maka MS Excel akan menampilkan kode kesalahan #VALUE! Ä Range Tabel data (Tabel Array) yang akan dibaca dengan fungsi VLOOKUP harus dalam keadaan urut secara ascending (pengurutan data secara menaik).