Alamat Relatif, Alamat Absolut, dan Formula Sederhana

1. Alamat Relatif

Yang dimaksud “alamat” di sini adalah posisi sel yang direpresentasikan dengan headings (pasangan kolom dan baris), seperti A1, B12, dan D13.

Alamat ini dapat dijadikan referensi (rujukan) oleh sel lain. Misalkan, kita punya sel B3 yang berisi teks “Saya”, kemudian B4 kita isi “=B3”. Ini artinya B3 diacu oleh sel B4, sehingga B4 isinya menjadi sama dengan yang di B3. Konsekuensinya adalah jika isi B4 akan selalu sama dengan B3. Jadi, jika isi B3 diubah menjadi “Mereka” maka B4 akan menampilkan juga kata “Mereka”.

Pengacuan isi sel oleh sel lain dengan cara menuliskan “sama dengan” tidak hanya bisa menuliskan suatu alamat, tapi bisa suatu operasi tertentu. Misalnya, operasi matematis (pengolahan angka) atau operasi teks (pengolahan karakter). Ini yang disebut sebagai formula. Suatu formula bisa sederhana, bisa rumit, dan bisa terdiri atas fungsi-fungsi yang disediakan oleh MSExcel (fungsi intrinsik).

Selain operasi matematis dan operasi teks, suatu sel bisa juga berisi operasi gabungan antara teks dan angka, operasi antarkelompok sel, dan operasi yang rumit.

Berikut adalah contoh operasi matematis dan teks.

=B6*C6	mengalikan isi sel B6 dengan isi sel C6
=SUM(D3:D8)	menjumlahkan isi sel pada range/selang sel D3 sampai dengan D8.
=left(G45;10)	mengambil 10 karakter dari arah kiri dari sel G45. Jika G45 berisi kata "kombinasi kunci" maka sel yang berisi formula left tersebut akan terisi "kombinasi ".
=CONCATENATE("Jumlah";B2;"=";SUM(D3:D8);"jiwa")	menggabungkan teks "Jumlah ", isi sel B2, "=", hasil penjumlahan range D3 sampai D8 dan teks " jiwa". Jika B2 ada teks "penduduk" dan misal hasil jumlah D3:D8 adalah 215, maka isi sel dengan formula concatenate terisi "Jumlah penduduk=215 jiwa"
=IF(LEN(T4)=2;CONCATENATE("0";T4;"-";U4);CONCATENATE(T4;"-";U4))	Contoh formula dengan pengecekan kondisi. Ini adalah formula untuk menuliskan kembali nomor telepon dengan kode SLJJ.
=IF(AND(DAY(B5)>=5,DAY(B5<=15), IF(F5>=20000,20%*F5,IF(F5>=10000,10%*F5,0)),0)	Contoh penggunan IF bersarang (nested) untuk menghitung diskon.

Pembahasan alamat memang akan membicarakan penggunaannya pada formula, baik yang sederhana maupun kompleks (seperti contoh di atas). Kamu dapat melihat semua formula di atas menggunakan alamat.

Untuk memahami alamat relatif, simak contoh berikut ini.

Bayangkan kamu ingin mengalikan 2 isi sel seperti contoh di atas (=B6*C6), lalu kita simpan hasil perkalian di sel D6.

Misalkan, kamu mempunyai beberapa sel lagi di bawah B6 dan C6 yang juga akan dikalikan dan disimpan di baris masing-masing (D7, D8, D9, dan seterusnya) maka kita tidak perlu membuat formula pada setiap baris, namun cukup menyalin rumusnya dari sel D6 ke sel-sel di bawahnya.

Perhatikan ilustrasinya berikut ini.

Contoh pemakaian formula.

Tampak pada penampilan di atas, formula pada D7=B7*C7, demikian juga untuk D8=B8*C8, dan seterusnya.

Alamat seperti B8, D7, A31, S40, C9, dan seterusnya merupakan alamat relatif. Disebut “relatif” karena jika dicopy seperti pada contoh di atas, alamat ini akan berubah sesuai dengan posisi sel sekarang (current cell) bukan lagi alamat asal (alamat sel semula yang di-copy).

Contoh di atas (B7*C7, B8*C8, B9*C9) menunjukkan alamat baris yang berubah karena formula di-copy ke bawah (atau bisa juga ke atas). Jadi, dapat dikatakan alamat ini relatif terhadap baris (alamat kolom tetap).

Jika kamu meng-copy isi formula ke kiri atau kanan, isi sel hasil peng-copy-an akan berubah relatif terhadap kolom (alamat baris tetap).

Jika kamu meng-copy isi formula ke kiri atau kanan dan juga ke atas atau bawah maka isi sel hasil peng-copyian akan berubah relatif terhadap kolom dan baris (isi formulanya akan tetap).

2. Alamat Absolut

Coba kamu perhatikan kasus berikut. Asumsikan kamu memiliki komponen komputer yang harganya menggunakan US dollar. Kemudian, kamu ingin mengetahui harganya dalam rupiah.

Harga komponen komputer.

Sekarang, isi sel yang berisi nilai tukar (kurs) Rupiah terhadap Dollar (misal, di C12). Kemudian, hitung nilai rupiahnya untuk barang yang pertama (di D15).

Selanjutnya, formula ini kamu copy-kan ke tiga sel di bawahnya, hasilnya adalah sebagai berikut.

Harga komponen komputer dalam Dollar dan Rupiah.

Isi D16 kosong karena (berisi formula =C16*C13) dan C13 kosong. Isi D17 (berisi formula =C17*C14) terjadi kesalahan/error karena C14 berisi teks yang tidak bisa dikalikan dengan 98. Isi D18 (berisi formula =C18*C15) ada hasil hitungan, tapi salah karena harga dollarnya (48) dikalikan 128 (harga motherboard).

Dengan demikian, peng-copy-an dan hitungan contoh di atas salah, karena formula hasil peng-copy-an salah disebabkan menggunakan alamat relatif.

Jadi, seharusnya sel D16, D17, D18 masing-masing tetap dikalikan dengan sel C12.

Dalam kasus ini, dapat dikatakan, C12 harus diacu sebagai alamat absolut, dengan perkataan lain, alamatnya tidak boleh berubah walau di-copy-kan kemana pun. Cara penulisan alamat absolut adalah menggunakan notasi $.

Dalam kasus tersebut karena kita mengkopikan alamat C12 ke bawah (jadi nilai relatifnya adalah BARIS), sehingga alamat barislah yang harus disetel sebagai absolut. Jadi rumus pada:

D15 ditulisnya: =C15*C$12

Dengan demikian saat di-copy ke bawah, diperoleh hasil berikut.

D16 berisi =C16*C$12
D17 berisi =C17*C$12
D18 berisi =C18*C$12

Untuk kasus di mana ada sel yang akan diacu secara absolut oleh sel-sel yang tersebar pada kolom-kolom, dengan perkataan lain: formulanya dikopi ke kiri dan kanan, maka yang diberi notasi $ adalah kolomnya contoh: =$F5+J2 dan =$F6+J3.

Dalam kasus yang lebih luas lagi, di mana ada sel yang diacu secara absolut oleh sel-sel yang tersebar pada kolomkolom dan baris-baris (formula di-copy ke kiri atau kanan dan atas atau bawah) maka yang diberi notas $ adalah kolom dan baris, misalnya =$H$5.

Perlu diketahui bahwa MSExcel menyediakan tombol shortcut untuk pemberian alamat absolut ini, yaitu F4.

Cara menggunakannya adalah tekan F4 saat kamu sedang membuat formula atau saat kamu dalam mode pengeditan sel yang berisi formula (notasi $ akan diberikan pada alamat yang sedang disorot).

Penekanan F4 ini bersifat switching (seperti tombol pengatur layar monitor).

Penekanan F4 pertamakali akan memberikan notasi $ pada keduanya (baris dan kolom), penekanan F4 yang kedua akan memberikan pada baris, penekanan ketiga pada kolom, penekanan F4 berikutnya akan menghilangkan $, dan seterusnya.

3. Fungsi-Fungsi yang Pertama

Pada pembicaraan yang lalu, sudah diketahui bahwa suatu sel dapat berisi sel yang mengacu isi dari sel lain, misalnya isi sel B4 berisi =A2, sehingga isi B4 akan sama dengan A2.

Pada pembahasan alamat relatif maupun alamat absolut, diberi contoh bukan hanya sekadar mengacu ke sel lain, tetapi sudah berisi “operasi perhitungan”. Inilah yang disebut sebagai rumusan atau formula.

Adapun yang disebut fungsi (function) adalah suatu kata kunci tertentu (keyword) yang membutuhkan argumen (parameter) tertentu. Fungsi dapat digunakan untuk kebutuhan yang tertentu juga. Function terdiri atas beberapa kategori, yaitu seperti berikut ini.

a) Fungsi lojikal;
b) Fungsi matematik dan trigonometri;
c) Fungsi pengolahan karakter (teks);
d) Fungsi statistik dan database;
e) Fungsi finansial dan akuntansi;
f) Fungsi tanggal dan waktu;
g) dan beberapa fungsi khusus.

Fungsi-fungsi tersebut disebut juga fungsi intrinsik, yaitu fungsi-fungsi yang disediakan oleh MSExcel. “Disediakan oleh MSExcel” memiliki pengertian dapat kamu membuat fungsi sendiri sesuai kebutuhanmu. Artinya fungsi adalah formula juga, yaitu formula khusus yang sudah tersedia (intrinsik).

Sebagai contoh misalkan, untuk menghitung jumlah suatu kelompok sel (misal sel A1, A2, A3), kamu bisa menggunakan cara: =A1+A2+A3. Kemudian, menghitung rata-ratanya: =(A1+A2+A3)/3. Namun, karena MSExcel sudah memiliki fungsi yang disediakan untuk kebutuhan seperti ini, kamu dapat langsung menggunakannya, Misalkan, untuk mengitung jumlah dengan =SUM(A1:A3), rata-rata dengan =AVERAGE(A1:A3) atau =SUM(A1:A3)/3 atau =SUM(A1:A3)/COUNT(A1:A3).

a. Fungsi Statistik & Matematik

Misalkan, kamu memiliki data seperti berikut ini. Kemudian, kamu akan menghitung rata-rata nilai Fisika, Kimia, dan Matematika. Selain itu, juga akan dihitung nilai maksimal dan minimal setiap pelajarannya.

Data nilai pelajaran.

Caranya adalah tempatkan kursor pada sel F3 (misalnya untuk siswa dengan nama “Bambang”). Ketikkan “=average”. Sejenak akan muncul beberapa varian dari fungsi average.

Fungsi average

Begitu kamu mengetikkan kurung buka, akan muncul sintaks atau cara penulisan fungsi average ini. Tampak parameter (argumen) yang dibutuhkan. Argumen ini dapat berupa angka misalkan “=average(12;15;6;20;..dst)” atau berupa kelompok (range) sel.

Dalam hal ini akan dimasukkan range C3 sampai E3, namun sebaiknya tidak diketikkan, tetapi cukup menggerakkan kursor melingkupi range ini (kamu bisa menggunakan tombol panah kiri-kanan atau drag dengan mouse).

Fungsi average.

Setelah memberikan range sel, kamu tidak perlu menutupnya dengan kurung tutup. Kamu cukup menekan tombol Enter untuk mengakhiri/mengeksekusinya (MSExcel akan melengkapinya secara otomatis). Hasilnya adalah seperti berikut.

Hasil eksekusi MSExcel.

Cara menggunakan formula atau fungsi ini dapat dilakukan juga melalui ikon pada grup Function Library pada tab Formula. Khusus untuk fungsi penjumlahan (SUM), dengan pertimbangan karena sering digunakan, terdapat juga pada grup Editing dari tab Home.

Grup Editing.

Misalnya, kamu menerapkan fungsi SUM (ikon AutoSum) pada salah satu ikon di atas untuk menjumlahkan nilai matematika. Tampilannya seperti berikut.

Penjumlahan nilai matematika.

Untuk menghitung rata-ratanya selain dengan average, kamu dapat pula menggunakan rumus umum, yaitu jumlahnya dibagi banyaknya item.

Misalkan, kamu tempatkan nilai rata-rata ini pada sel E11 seperti berikut.

Nilai rata-rata.

Nilai item yang dijumlahkan pada rumus di atas (7) dapat digantikan dengan fungsi yang sudah disediakan MSExcel, yaitu COUNT.

=E10/COUNT(E3:E9)

Fungsi count akan menghitung keberadaan angka pada range yang didefinisikan. Jika diinginkan menghitung keberadaan angka maupun teks (huruf) maka fungsinya COUNTA(range).

b. Fungsi Pengolah Teks

Beberapa fungsi pengolah teks ,yaitu RIGHT (mengambil sekian karakter dari sebelah kanan), LEFT (mengambil sekian karakter dari sebelah kiri), CONCATENATE (menggabungkan teks), UPPER (mengkapitalkan), LOWER (mengubah menjadi karakter kecil).

Misalkan, kamu berikan formula =left(C2;2) maka C2 akan diambil 2 karakter dari kiri (hasilnya "Fi"). Argumen suatu fungsi dapat berupa fungsi lagi, misalkan seperti =upper(left(C2;2)). Dengan demikian jika diterapkan pada alamat G2, hasil upper("Fi") adalah "FI".

Pengolahan teks.

Berikut adalah contoh fungsi “menambahkan” karakter atau teks. Dalam konteks numerik, “menambahkan” berarti “penjumlahan”. Adapun dalam konteks karakter/teks “menambahkan” lebih tepat “penggabungan”.

Jika menggunakan rumus umum, penggabungan teks dapat menggunakan ampersand (&). Misalkan, ketika kamu menggabungkan beberapa teks dengan beberapa isi sel suatu alamat tertentu pada sel B1, seperti berikut.

Jika kamu menggunakan fungsi yang disediakan MSExcel (yaitu concatenate) seperti =concatenate("Daftar Nilai ";C2;", ";D2;", dan ";E2), hasilnya seperti pada ilustrasi berikut:

Penggunaan fungsi concentrate.

c. Fungsi Tanggal & Jam

Sebagai contoh kasus, akan diberikan keterangan di bawah daftar nilai contoh di atas berupa informasi kepada siswa, yaitu informasi mengenai hal-hal berikut.

1. Status pengumuman daftar nilai ini per tanggal berapa. diberikan di sel C14 yang dilakukan dengan cara dientri “11-12-2008”.
2. Informasi jamnya pada E14 dengan cara dientri “9:15”.
3. Keterangan tentang durasi waktu yang diberikan jika ada siswa yang mau protes (komplain), yaitu 3 hari, dientri di D15 angka “3”.
4. Informasi berupa penegasan batas waktu terakhir, yaitu dengan menjumlahkan per tanggal dengan durasi hari, jam, dan menitnya, yaitu pada sel D16 berisi penjumlahan sel C14 dan D15. Kemudian, jam dan menit dientri masing-masing di F16 dan H16 menggunakan fungsi HOUR (mengambil jamnya saja) dan MINUTE (mengambil menitnya saja).

Tampilannya kira-kira seperti berikut.

Penggunaan fungsi waktu.