FUNGSI FORMAT CELL PADA MICROSOFT EXCEL

Tampilan Format Cells :

Sekarang kita akan belajar bagaimana memformat cell untuk data berupa angka. Pertama, pilih menu Number>Number.

Selanjutnya teman-teman bisa memilih apakah akan menggunakan angka decimal? berapa angka decimal yang ingin ditampilkan? Atau ingin menggunakan separator (.) perseribu? Lihat contoh gambar di bawah ini :

Pada gambar di atas, Cell A1 menggunakan format number tanpa decimal dan tanpa separator. Cell A2 dengan format 2 decimal tanpa separator, sedangkan Cell A3 menggunakan format tanpa decimal namun dengan separator (.) perseribu.

Terkadang kita juga membutuhkan tampilan data angka nol (0) didepan angka lainnya, seperti untuk membuat data nomor handphone/telepon seperti ini ”0815711xxxx”. Kalau kita menggunakan format number, maka secara otomatis angka nol di awal akan dihilangkan. Untuk menampilkannya, pertama-tama ubahlah terlebih dahulu cell yang akan diisi dengan data tersebut menjadi teks (Text). Atau bisa langsung menambahkan karakter ( ’ ) di awal data, contoh : ’0815711xxxx.

Number

Fungsi Number digunakan untuk mengatur format angka dari cell. Dalam menu Number terdapat 12 pilihan kategori, yaitu:

Kategori

Format

General

Umum/Normal tanpa format (contoh : 50)

Number

Angka (contoh : 50, 15.5)

Currency

Mata uang (contoh : Rp.50.000)

Accounting

Akuntansi/Keuangan (contoh : Rp. 50.000)

Date

Tanggal (contoh : 29/10/2013)

Time

Waktu (contoh : 11:00:00

Percentage

Persentase (contoh : 50%)

Fraction

Pecahan (contoh : ¾ , ½ )

Scientific

Scientific (contoh : 3,E+01)

Text

Teks (angka akan dianggap sebagai teks)

Special

Format khusus

Custom

Format dengan pengaturan pribadi/manual

Pada tab number kita bisa mengatur format pada cell

Sample, menunjukkan isi cell yang anda pilih untuk diformatDecimal places, menentukan banyaknya angka di belakang koma yang maksimal sampai 30 angka di belakang koma.Kotak ini hanya untuk kategori Number, Currency, Accounting, Percentage, dan Scientific.Use 1000 Seperator, untuk mengisi pemisah ribuan pada angka. Kotak ini hanya berlaku untuk kategori Number.Negative numbers, menentukan bentuk penulisan bilangan-bilangan negatif. Kotak ini hanya berlaku pada kategori Number dan Currency.Symbol, memilih simbol mata uang. Kotak ini hanya berlaku pada kategori Currency dan Accounting.Type, memilih tampilan yang anda ingin gunakan pada angka. Kotak ini hanya berlaku pada kategori Date, Fraction, Special, dan Custom.Locale (location), memilih bahasa yang digunakan dalam menampilkan angka. Pilihan ini hanya berlaku pada kategori Date, Time, dan Special.

Alignment

Fungsi Alignment adalah untuk mengatur perataan dari cell. Dalam Alignment terdapat 4 pengaturan, yaitu:

1. Text alignment

Text alignmen digunakan untuk mengatur perataan teks,terdapat 2 opsi pengaturan yaitu:

Horizontal : Perataan secara horizontalVertical : Perataan secara vertical

2. Text control3. Right-to-left4. Orientation

Wrap text : Menjadikan teks mengikuti ukuran lebar cellShrink to fit : Menjadikan teks mengecil mengikuti lebar cellMerge cells : Menggabungkan cell

Text Direction : Mengatur arah penulisan teks

Orientation digunakan untuk mengatur derajat kemiringan dari teks.

Font

Fungsi Font adalah untuk mengatur karakter huruf dari cell. Dalam Font terdapat 6 opsi pengaturan, yaitu:

1. Font

Font digunakan untuk memilih jenis huruf yang diinginkan. Seperti jenis font Arial, Times New Roman dan lain-lain.

2. Font Style

Font Style digunakan untuk mengatur gaya penulisan, yaitu:

RegularItalic (cetak miring) = Ctrl+IBold (cetak tebal) = Ctrl+BBold + Italic (cetak tebal dan miring) = Ctrl+I tambah Ctril+B

3. Size

Untuk mengubah ukuran huruf.

4. Underline

Underline digunakan untuk membuat garis bawah huruf (Ctrl+U)

5. Color

Color digunakan untuk mengganti warna huruf dengan warna yang diinginkan.

6. Effects

Effects digunakan untuk memberi efek pada huruf, efek tersebut yaitu:

Strikethrough : efek coretan,Superscript : huruf berukuran kecil diatas karakter umum (seperti pangkat),Subscript : huruf berukuran kecil dibawah karakter umum,

Border

Fungsi Border adalah untuk mengatur bingkai/garis luar dari cell. Dalam Border terdapat 4 opsi pengaturan, yaitu

1. Line

Line digunakan untuk mengatur jenis garis.

2. Color

Color digunakan untuk mengatur warna garis.

3. Presets4. Border

None : tanpa bingkaiOutline : memberi garis luar cell/rangeInside : memberi garis dalam cell/range

Border digunakan untuk memberikan garis sesuai arah yang diinginkan.

Fill

Fill digunakan untuk memberikan warna pada cell. Pada Fill terdapat 3 opsi pengaturan, yaitu:

1. Background Color

Background Color digunakan untuk memilih warna background belang cell

2. Pattern Color

Pattern Color digunakan untuk memilih warna arsidari dari cell

3. Pattern Style

Pattern Style digunakan untuk memilih jenis arsiran untuk cell

Protection

Protection berfungsi untuk melindungi isi cell, dengan cara mengunci sekaligus menyembunyikan rumus di dalam cell.

FUNGSI LOGIKA PADA EXCEL

Fungsi Logika (IF) Pada Excel

Dalam pelajaran TIK, Jika Nilai TIK kalian kurang dari 75 maka harus remidi dan jika mendapatkan nilai 75, 76,77 ke atas berarti kalian lulus.

PERINTAH LOGIKA

“JIKA NILAI TIK < 75 MAKA REMIDI”

“JIKA NILAI TIK >= 75 MAKA LULUS”

Dalam Excel perintah logika dikenal dengan fungsi logika IF, yaitu suatu fungsi yang digunakan untuk menyelesaikan suatu masalah yang dihadapkan dengan suatu kondisi tertentu. Kondisi tersebut membentuk suatu pernyataan yang baku dan menuntut jawaban "Benar" dan "Salah".

Rumusnya,

Perlu diingat tentang operasi logika / operator pembanding, digunakan sebagai syarat dari kondisi.

Untuk setiap nilai benar dan salah yang diberikan, jika bertype Karakter, maka harus diapit tanda petik dua ( " " ) sedangkan yang bertype Numerik / Angka maka diketikkan biasa saja.

IF Tunggal

Fungsi logika IF yang hanya memiliki satu kondisi / syarat, sehingga dipastikan memiliki dua hasil yang akan ditampilkan yaitu hasil yang sesuai syarat (kondisi) atau hasil yang tidak sesuai syarat (kondisi) akibat dari satu kondisi / syarat tersebut, sehingga hanya membutuhkan satu IF (Tunggal)

Rumus seperti di atas ....

Contoh :

Dalam pelajaran TIK, Jika Nilai TIK kalian kurang dari 75 maka harus remidi dan jika mendapatkan nilai 75, 76 ke atas berarti kalian lulus.

Berarti, jika isi sel B3 (nilai) < 75 (kondisi) maka Remidi (Hasil sesuai kondisi) dan jika isi sel B3 (nilai) >= 75 (selain < 75) maka Lulus (Hasil tidak sesuai kondisi).

Jawab :

Rumus IF dapat juga dimasukkan melalui menu dengan langkah – langkah sebagai berikut :

Klik FormulasKlik LogicalKlik / pilih IFKotak Function Arguments aktif, isi kondisi (syarat) dan nilai (hasil) yang ditentukan.Klik OK

IF Majemuk

Fungsi logika IF yang memiliki lebih dari satu kondisi / syarat, sehingga dipastikan memiliki lebih dari dua hasil yang akan ditampilkan yaitu hasil yang sesuai syarat (kondisi) pertama, kedua dan seterusnya dan yang terakhir hasil yang tidak sesuai syarat (kondisi) semuanya, akibat dari satu kondisi / syarat tersebut, sehingga membutuhkan lebih dari satu IF (Majemuk).

Yang perlu diingat dalam IF majemuk adalah Jumlah IF = Hasil – 1, tutup kurung yang terakhir sebanyak jumlah IF, susunan IF yang terakhir seperti IF tunggal.

Rumus

Ditentukan nilai TIK,

< 50 adalah Gagal, >= 50 sampai < 75 adalah Remidi, >= 75 adalah Lulus

Artinya, nilai / hasil ada tiga yaitu, gagal, remidi dan lulus (jumlah IF ada dua, tanda kurung tutup terakhir ada dua). Kondisi / syaratnya cukup dua yaitu < 50 (hasilnya gagal) dan >= 75 (hasilnya lulus), selain dua kondisi / syarat tersebut (tidak memenuhi syarat semuanya) hasilnya ditempatkan pada nilai salah (remidi).

Menggabungkan Fungsi Logika IF dan Fungsi String

Fungsi logika IF dimana pada bagian kondisi / syarat digunakan juga fungsi string untuk mengambil beberapa teks yang sesuai dengan ketentuan.

Misal,

Ketentuan

TUJUAN diperoleh dari NOMOR PENGIRIMAN, jika :

MD = Madiun,

SB = Surabaya,

ML = Malang

JENIS PAKET diperoleh dari NOMOR PENGIRIMAN, jika :

K = Kilat,

B = Biasa

Rumus yang digunakan adalah,

Tujuan (sel B3)

Rumus yang digunakan adalah,

Jenis Paket (sel C3)

Hasil setelah rumus dimasukkan,

Operator Logika

Kadangkala kita diperhadapkan pada dua atau lebih kondisi/kriteria yang diperiksa, maka untuk menyelesaikan hal ini digunakanlah operator logika. Macam-macam operator logika yang terdapat dalam Excel adalah :

=AND(kriteria 1,kriteria 2, ...)

Akan bernilai benar jika semua kriteria benar. Misalnya :

AND(TRUE,TRUE) hasilnya adalah TRUE

AND(TRUE,FALSE) hasilnya adalah FALSE

AND(FALSE,TRUE) hasilnya adalah FALSE

AND(FALSE,FALSE) hasilnya adalah FALSE

=OR(kriteria 1,kriteria 2, ...)

Akan bernilai benar jika salah satu kriteria benar. Misalnya :

OR(TRUE,TRUE) hasilnya adalah TRUE

OR(TRUE,FALSE) hasilnya adalah TRUE

OR(FALSE,TRUE) hasilnya adalah TRUE

OR(FALSE,FALSE) hasilnya adalah FALSE

=NOT(kriteria atau kondisi)

Hasilnya adalah kebalikan dari kondisi. Misalnya :

NOT(TRUE) hasilnya adalah FALSE

NOT(FALSE) hasilnya adalah TRUE

Contoh Kasus

FUNGSI STATISTIKA PADA EXCEL 2007

Fungsi Statistika adalah termasuk rumus fungsi yang digunakan dalam proses untuk menghitung suatu data dalam excel.

Fungsi statistika antara lain : COUNT, SUM, AVERAGE, MAX, MIN.

CONTOH :

Dapat dilihat bahwa untuk menghitung angka atau nilai – nilai tersebut adalah dengan menggunakan rumus fungsi, kegunaannya adalah untuk menghemat waktu karena akan mempercepat penyeleseian dalam pekerjaan.

Menghitung jumlah => =SUM(numbe1r;number2,…)

Ketik =SUM(Lalu drag angka – angka yang akan dijumlahkan/ditotalkanKemudian ENTER

Menghitung rata- rata => =AVERAGE(number1,number,…)

Ketik =AVRAGE(Lalu klik pada angka salah satu dari jumlah dataKemudian dibagi dengan banyaknya nilai dalam mata kuliahLalu ENTER

Menghitung COUNT => =COUNT(number,number,…)

Ketik = COUNT(Kemudian blok/drag data yang akan dijumlah atau total dari data.Klik atau tekan enter.

Menghitung nilai MAX => =MAX(number1,number2,…)

Ketik =MAX(Lalu blog/drag angka dari data yang akan ditentukan nilai maksimalnya,Tekan enter

Menghitung nilai MIN => =MIN(number1,number2,…)

Ketik =MIN(Blok/drag angka dari data yang akan ditentukan nilai maksimalnya,Tekan enter

Pada gambar diatas, kolom yang diisikan secara manual adalah kolom No, Nama, Status dan Lama Kerja sedangkan untuk kolom Pokok dan Tunjangan didapatkan dari rumus dengan ketentuan sebagai berikut :

Pokok :

Jika Lama Kerja>15 -> 2.000.000

Jika Lama Kerja>10 -> 1.500.000

Jika Lama Kerja>5 -> 1.000.000

Selain itu 750.000

Tunjangan :

Jika berstatus Nikah (N) maka mendapatkan Tunjangan 15% dari Pokok;

Jika Belum Nikah (B) dan Lama Kerja lebih dari 10 thn maka mendapatkanTunjangan 7,5%
dari Pokok;

Jika berstatus Belum Nikah (B) dan Lama Kerja lebih dari 3 thn maka mendapatkan
Tunjangan 3% dari Pokok;

Selain itu tidak mendapatkan Tunjangan.

Penyelesaian :

Pokok :

Untuk mencari Pokok, karena terdapat 4 buah kriteria/syarat maka kita menggunakan 4 buah fungsi IF di dalamnya. Rumusnya adalah sebagai berikut :

=IF(D2>=15,2000000,IF(D2>=10,1500000,IF(D2>=5,1000000,750000)))

Tunjangan :

Untuk mencari Tunjangan, terdapat 4 buah kriteria sehingga fungsi IF yang digunakan ada 3 buah serta terdapat operator logika "dan" didalamnya maka rumusnya adalah sebagai berikut :

=IF(C2="N",15%*E2,IF(AND(C2="B",D2>10),7,5%*E2,IF(AND(C2="B",D2>3),3%*E2,0)))

Fungsi Pembacaan Tabel

Fungsi berikut ini digunakan untuk membaca tabel data/informasi dengan membandingkan nilai kunciyang terletak di kolom pertama tabel data secara vertikal.
=VLOOKUP(Nilai Kunci, Range Tabel, Offset Kolom)

Sedangkan bila pembandingannya dilakukan secara horizontal (per baris) maka fungsinya adalah sebagai berikut :

=HLOOKUP(Nilai Kunci, Range Tabel, Offset Baris)

Catatan :

Nilai Kunci, adalah data yang berupa angka, kode tertentu atau teks yang digunakan sebagai pembanding data

Range Tabel, Adalah alamat tabel yang berisi data atau informasi lengkap sebagai acuan atau referensi untuk mengisi tabel soal.

Syarat Pembacaan Tabel :

Data pembanding pada tabel data harus terletak di kolom pertama

Range Tabel dan harus urut naik (Ascending)

Data pembanding pada kolom pertama bisa berupa teks, angka atau data logika, huruf besar atau kecil tidak dibedakan.

Offset Kolom atau Offset baris

Adalah nomor kolomatau nomor baris data yang akan dibaca. Misalnya diketahui lembar kerja dengan tabel berikut ini :

Untuk mengisi bagian kosong di kolom NAMA BRG, dipergunakan fungsi sebagai berikut :
=VLOOKUP(B2. $E$3:$f$5, 2)

Nilai Kunci ada di kolom B2 karena sama dengan kolom pertama tabel data.
Range Tabel beralamat tetap (absolut) karena itu diberi awalan '$s' dan ditentukan mulai data yang ada dibawah judul kolom sampai dengan akhir label.
Offset kolomnya 2, karena data yang diinginkan adalah NAMA BRG yang terletak dikolom ke-2 dari range tabel data

FUNGSI TEKS PADA EXCEL 2007

Data berupa teks dapat dinyatakan sebagai sebuah argument. Salah satu fungsi teks yang sering digunakan adalah fungsi pemotongan teks untuk mendapatkan atau menggunakan informasi dari sebagian atau seluruh teks yang ada. Beberapa fungsi teks yang sering digunakan adalah :

LEFT

digunakan untuk memotong bagian kiri suatu teks sebanyak karakter tententu.
=LEFT(text,num_chars)

Contoh :
cell A2 = Tutorial Ilmu Komputer

=LEFT(C2,8)
hasil = Tutorial

RIGHT

digunakan untuk memotong bagian kanan suatu teks sebanyak karakter tententu.
=RIGHT(text,num_chars)

Contoh :
cell A1 = Tutorial Ilmu Komputer

=RIGHT(A1 ,8)

hasil = Komputer

MID

digunakan untuk memotong teks pada kedudukan tententu.

=MID(text,start_num,num_chars)
text adalah teks yang ingin dipotong

start_num adalah posisi mulai memotong dihitung dari kiri

num_chars adalah banyaknya karakter yang dipotong

Contoh :

cell A1 = Tutorial Ilmu Komputer

=RIGHT(A1 ,10,4)

hasil = Ilmu

LEN

digunakan untuk meghitung panjang suatu teks atau banyaknya karakter pada suatu teks.
=LEN(text)

Contoh :
cell A1 = Tutorial Ilmu Komputer

=LEN(A1)
hasil = 22

VALUE

digunakan untuk mengubah data karakter menjadi angka =VALUE(text)

TEXT

digunakan untuk mengubah data angka menjadi karakter =TEXT(text)

FIND

digunakan untuk mencari kedudukan karakter tertentu dari suatu teks dan nilai yang dihasilkan berupa angka yang menunjukkan posisi karakter yang dicari.

FIND(find_text,within_text,start_num)
find_text adalah teks atau karakter yang ingin dicari

within_text adalah teks yang berisi karakter atau teks yang ingin dicari

start_num dicari dari karakter yang keberapa.

CONCATENATE

digunakan untuk menggabungkan data di cell-cell yang berbeda.

=CONCATENATE (text1,text2,...)

banyaknya cell yang bisa digabungkan adalah 2 sampai 255

FUNGSI REFERENSI (LOOK UP) PADA MS. EXCEL

Fungsi VLOOKUP dan HLOOKUP mempunyai fungsi dan prinsip yang sama. Perbedaanya terletak pada pembacaan arah tabel. Dimana Fungsi VLOOKUP digunakan untuk membaca nilai tabel dalam arah vertical (dalam satu kolom), sedangkan Fungsi HLOOKUP digunakan untuk membaca nilai tabel dalam arah horizontal (dalam satu baris).

Rumus umum untuk kedua fungsi ini adalah sebagai berikut :

=VLOOKUP(lookup_value;table_array;col_index_num;lookup_range)

=HLOOKUP(lookup_value;table_array;col_index_num;lookup_range)

Keterangan:

lookup_value adalah nilai yang akan menjadi acuan pencarian di table yang digunakan. Ia merupakan kunci berupa alamat sel yang digunakan sebagai kunci pembacaan table.table_array adalah range (rentang nilai) dimana tabel tempat nilai-nilai yang ingin dibaca disimpan. Pada range ini biasanya ada beberapa kolom. Kolom pertama berisi nilai pembanding yang akan dibandingkan dengan nilai kunci.col_index_num adalah nomor baris atau kolom di tabel acuan.lookup_range adalah nilai logika yang menentukan apakah fungsi mencari sebuah nilai yang benar-benar sesuai atau menggunakan nilai pendekatan. Jika diabaikan, maka akan menggunakan nilai TRUE yang dengan pendekatan. Sebaliknya, jika menggunakan FALSE, fungsi akan menggunakan pendekatan nilai yang benar-benar sesuai.

Catatan :

Range table data yang akan dibaca dengan fungsi VLOOKUP harus dalam keadaan urut secara Ascending (secara menaik)

Pembuatan Tabel Referensi Dan Penggunaannya

Sebelum menggunakan rumus VLOOKUP dan HLOOKUP kita harus memahami pembuatan tabel acuan dan tabel pencariannya terlebih dahulu. Berikut langkah-langkah yang harus diperhatikan.

Referensi Cell pada Microsoft Excel

Referensi cell mempunyai maksud untuk merujuk atau terhubung ke cell atau range cell tertentu untuk mencari data atau value yang ada di cell atau range cell tertentu itu untuk selanjutnya kita gunakan dalam membuat formula. Sebagai contoh sederhana kita lihat pada gambar 1.1 di bawag ini

Gambar 1.1

Misalnya pada cell A1 kita tuliskan angka 6. Maka ketika kita ingin memunculkan angka 6 pada cell A1 untuk kita gunakan pada cell A3, maka kita klik cell A3, awali dengan =(sama dengan) terus klik cell A1 tekan enter. Atau kita awali dengan tanda (=) trus kita tuliskan A1. Maka angka 6 yang ada di cell A1 akan muncul di cell A3.

Penerapan Referensi Cell Pada Formula

Gambar 1.2

Dari Gambar 1.2 diatas, misalkan kita akan menuliskan formula pada cell A3 yaitu pengurangan antara angka di cell A1 dikurangi cell B1, maka dapat kita tuliskan pada cell A3 =A1-B1 terus tekan enter. Maka hasilnya adalah pengurangan antara cell A1 dikurangi B1 yaitu 10.

Gambar 1.3

Dari Gambar 1.3 di atas, referensinya berupa range cell atau tidak hanya satu cell saja. Misalnya kita akan menjumlahkan dari cell B1 sampai dengan B3, maka dengan fungsi SUM, formula dapat kita tuliskan =SUM(B1:B3). Tanda titik dua (:) sebagai penghubung untuk referensi yang berupa range cell atau lebih dari satu cell.

Formula

Merujuk pada

Keterangan / Hasil

=A1

cell A1

Data(value) yang ada di cell A1

A1:A3

cell A1 sampai A3

Data(value) yang ada di cell A1 sampai A3,dalam penerapannya pada formula tidak bisa berdiri sendiri, musti dipadukan dengan fungsi-fungsi yang ada

=Sheet2!A1

cell A1 pada Sheet2

Data(value) yang ada di cell A1 pada Sheet2,dimanfaatkan untuk referensi pada cell yang berbeda