Level Basic - Intermediate MODUL EXCEL FUNDAMENTAL PADEPOKAN IT COURSE IT TRAINING CENTER & IT SOLUTION www.padepokanit.com
MODUL EXCEL FUNDAMENTAL BASIC - INTERMEDIATE DAFTAR ISI DAFTAR ISI ...................................................................................................................................................... 1 RUANG LINGKUP MATERI................................................................................................................................ 3 TUJUAN INSTRUKSIONAL ................................................................................................................................ 3 PETA MATERI .................................................................................................................................................. 3 I. BASIC MICROSOFT EXCEL ............................................................................................................................. 4 PENGENALAN INTERFACE MS EXCEL...................................................................................................................4 BEKERJA DENGAN TABEL, AUTO FIILL, FORMAT CELLS, MERGE CELLS & WRAP TEXT ..................................................6 Cara Membuat Tabel Pada Microsoft Excel ..........................................................................................6 Membuat Auto Fill atau isian otomatis .................................................................................................7 Merge Cells & Wrap Text .......................................................................................................................8 Format Cells Untuk Memformat Border Tabel dan Mata Uang Rupiah (Rp) ........................................9 Latihan Tabel, Auto Fill, Merge Cell, dan Wrap Text ...........................................................................11 MANAJEMEN VIEW, PRINT TITLE, LAYOUT DAN PRINTING ....................................................................................12 Mengatur skala layar Pencetakan Melalui Menu Page Layout...........................................................12 Fungsi Print Tile ...................................................................................................................................12 Page Break Area. .................................................................................................................................14 Memberi No Halaman .........................................................................................................................16 Print Dokumen Ke Printer ....................................................................................................................17 II. BEKERJA DENGAN RUMUS DAN FUNGSI DASAR ....................................................................................... 17 MENGATUR REGIONAL SETTING.......................................................................................................................17 PENGENALAN FORMULA DI EXCEL....................................................................................................................20 FUNGSI DASAR EXCEL YANG SERING DIGUNAKAN.................................................................................................23 Fungsi Matematika & Statistika Dasar................................................................................................23 Soal Latihan Fungsi Matetika & Statistika...........................................................................................26 Bekerja dengan Fungsi Count, Counta, CountBlank ............................................................................26 FUNGSI COUNTIF DAN COUNTIFS .....................................................................................................................29 FUNGSI ABSOLUTE ........................................................................................................................................30 FUNGSI RANKING..........................................................................................................................................32 FUNGSI WAKTU DAN TANGGAL DI EXCEL ...........................................................................................................33 III. BEKERJA DENGAN CONDITIONAL FORMATTING....................................................................................... 34 PENGENALAN CONDITIONAL FORMATTING.........................................................................................................34 TIPE CONDITIONAL FORMATTING PADA EXCEL ....................................................................................................35 CARA PENGGUNAAN CONDITIONAL FORMATTING PADA EXCEL...............................................................................35 VI. BEKERJA DENGAN RUMUS LANJUTAN...................................................................................................... 37 FUNGSI SUMIF DAN SUMIFS...........................................................................................................................37 Fungsi SumIF........................................................................................................................................37 Fungsi SUMIFS .......................................................................................................................................38 Latihan Fungsi Sumif dan Sumifs .........................................................................................................40 FUNGSI LOGIKA (LOGIKA IF)...........................................................................................................................41 Logika IF Tunggal.................................................................................................................................41 Logika IF Bertingkat.............................................................................................................................43 Latihan Penggunaan Rumus IF Bertingkat di Excel.............................................................................44 Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 1 of 74
FUNGSI VLOOKUP DAN HLOOKUP.....................................................................................................................46 Pengenalan Fugsi Vlookup dan Hlookup .............................................................................................46 Contoh Soal Penggunaan Fungsi Vlookup dan Hlookup Internal Sheet..............................................46 Penggunaan Fungsi Hlookup ...............................................................................................................48 Penggunaan Fungsi Vlookup dengan Nama Range.............................................................................49 V. BEKERJA DENGAN DATA VALIDATION ...................................................................................................... 50 VI. BEKERJA DENGAN FUNGSI GRAFIK ........................................................................................................... 52 MENGENAL GRAFIK.......................................................................................................................................52 JENIS-JENIS GRAFIK .......................................................................................................................................53 MEMFORMAT GRAFIK....................................................................................................................................54 LATIHAN MEMBUAT GRAFIK ...........................................................................................................................55 VII. LATIHAN PRAKTIKUM & TUGAS MANDIRI .............................................................................................. 57 PRAKTEK 1 ................................................................................................................................................57 Latihan Membuat Tabel & Format Cells ..............................................................................................57 Menggunakan Rumus Rumus Excel.....................................................................................................59 PRAKTEK 2...................................................................................................................................................60 Latihan Fungsi Matematika.................................................................................................................60 Latihan Fungsi Absolut ........................................................................................................................60 Latihan Fungsi Logika Sederhana (Logika IF Tunggal).........................................................................61 Latihan Conditional Formatting...........................................................................................................62 VIII. STUDI KASUS ........................................................................................................................................ 64 STUDI KASUS 1 MEMBUAT REKAPITULASI DAFTAR HADIR ABSESNSI .......................................................................64 STUDI KASUS 2 MEMBUAT SLIP GAJI DENGAN PENGGABUNGAN FUNGSI .................................................................68 STUDI KASUS 3 MEMBUAT APLIKASI PENJUALAN ................................................................................................72 Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 2 of 74
Ruang Lingkup Materi 1. Basic Excel Pengenalan Lingkungan Ms Excel & Interface 2. Bekerja Dengan Tabel & Format Cells 3. Layout & Pencetakan di Ms Excel 4. Rumus & Fungsi Dasar (Fungsi Matematika, Statistika, Count, Rankng dan Fungsi Absolute) 5. Fungsi Countif dan Countifs 6. Bekerja Dengan Conditional Formatting 7. Rumus Lanjutan (Sumif, Sumifs, Logika IF, dan Fungsi Vlookup-Hlookup) 8. Bekerja dengan Data Validation 9. Latihan & Studi Kasus Tujuan Instruksional 1. Mengetahui dan mampu Menjelaskan Basic Excel dan menggunakan Perintah Menu 2. Membuat & Memformat Tabel menggunakan Fasilitas Format Cell, Merge Cells, dan Wrap Text, dan Fitur lainnya. 3. Mampu Mengatur layout Tabel, Margin, Penomoran Halaman, dan Pencetakan 4. Mampu Bekerja dengan Rumus dan Fungsi Dasar serta Lanjutan di Ms Excel 5. Dapat Menggunakan Fungsi Conditional Formatting dan Data Validation 6. Dapat Membuat Grafik di Excel 7. Menyelesaikan latihan-latihan dan Studi Kasus PETA MATERI Review Basic Excel (About, Environment, & Feature Excel) Pemahaman Bekerja Dengan Tabel, Format Cells Praktek & Latihan Dasar Teori dan Conditional Formatting Bekerja Dengan Rumus & Fungsi Dasar dan Lanjutan di Ms Excel Mampu Menyelesaikan Latihan Praktikum, Studi Kasus dan Tugas Mandiri Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 3 of 74
I. Basic Microsoft Excel Pengenalan Interface Ms Excel Microsoft Excel atau Microsoft Office Excel adalah sebuah program aplikasi lembar kerja spreadsheet yang dibuat dan didistribusikan oleh Microsoft Corporation untuk sistem operasi Microsoft Windows dan Mac OS. Aplikasi ini memiliki fitur kalkulasi dan pembuatan grafik yang, dengan menggunakan strategi marketing Microsoft yang agresif, menjadikan Microsoft Excel sebagai salah satu program komputer yang populer digunakan di dalam komputer mikro hingga saat ini. Bahkan, saat ini program ini merupakan program spreadsheet paling banyak digunakan oleh banyak pihak, baik di platform PC berbasis Windows maupun platform Macintosh berbasis Mac OS, semenjak versi 5.0 diterbitkan pada tahun 1993. Aplikasi ini merupakan bagian dari Microsoft Office System, dan versi terakhir adalah versi Microsoft Office Excel 2016 yang diintegrasikan di dalam paket Microsoft Office System 2016 Sebelum mulai memasuki pembahasan Microsoft Excel, ada baiknya kita mengenal lebih dulu bagaimana tampilan Microsoft Excel itu, beserta beberapa istilah-istilah umum yang akan digunakan. Function Bar Ribbon Cell Range Column Heading Row Heading Worksheet Pada Microsoft excel kita bekerja dengan system workbook, sedangkan di dalam workbook terdapat worksheet atau lembar kerja dan biasanya cukup kita sebut sheet saja. Pada worksheet ini kita bekerja dengan menggunakan kolom dan baris yang membentuk kotakan kecil-kecil berupa sel-sel (cells) tempat kita memasukkan data. Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 4 of 74
Pengenalan Menu Quick Acces Toolbar : Memuat Buttons atau Tombol-tombol umum dan sering kita gunakan. Tombol-tombol ini bisa kita sesuaikan dengan kebutuhan. Ribbon : Berisi kumpulan tombol perintah excel yang dikelompokkan dalam bentuk Tab berdasarkan fungsi-fungsinya mulai Home, Insert, Page Layout, Formula, Data, Review, View. dan Developper (Secara Default disembunyikan). Pada masing-masing Tab dikelompokkan berdasarkan seri fungsinya. Misal : Clipboard, Font, Alignment, dan seterusnya. Formula Bar : Menunjukkan alamat cell yang aktif beserta isinya. Formula bar juga merupakan tempat dimana nanti kita akan menuliskan rumus-rumus atau formula excel. Worksheet Area : Berisi kumpulan cell yang diidentifikasi berdasarkan kolom (Column) dengan simbol huruf A, B, C dst. dan baris (Row) dengan simbol angka 1, 2, 3 dst. Didalam cell inilah kita menyusun data-data sesuai kebutuhan. Status Bar : Menunjukkan informasi status program Excel yang sedang berjalan. Didalam Ribbon terdiri Dari 1. Tabs Menu : Berisi kumpulan tombol yang dikelompokkan sesuai fungsinya. Secara default terdapat Tab Home, Insert, Page Layout, Formulas, Data, Review dan View. Sedangkan Tab Developper disembunyikan. untuk menampilaknnya kita perlu melakukan pengaturan terlebih dahulu. 2. Group Menu: Didalam masing-masing Tab tombol dikelompokkan lagi dalam beberapa group. Maisng-masing group dipisahkan dengan Garis vertikal. Pada Tab Page Layout diatas terdapat Group Themes, Pade setup, Scale to fit, Sheet Options dan Arrange. 3. Command Buttons : Didalam group terdapat beberapa Command Button atau tombol perintah sesuai dengan fungsi masing-masing. 4. Dialog Box Launcher : Pada beberapa sudut Menu Group terdapat Dialog Box Launcher untuk menampilkan pengaturan lebih lanjut pada masing-masing group yang secra default pengaturan ini tidak ditampilkan. Dalam Lembar Kerja Microsoft Excel terdapat 4 komponen utama yaitu : 1. Row Heading Row Heading (Kepala garis), adalah penunjuk lokasi baris pada lembar kerja yang aktif. Row Heading juga berfungsi sebagai salah satu bagian dari penunjuk sel (akan dibahas setelah ini). Jumlah baris yang disediakan oleh Microsoft Excel adalah 65.536 baris. 2. Column Heading Column Heading (Kepala kolom), adalah penunjuk lokasi kolom pada lembar kerja yang aktif. Sama halnya dengan Row Heading, Column Heading juga berfungsi sebagai salah satu bagian dari penunjuk sel (akan dibahas setelah ini). Kolom di simbol dengan abjad A – Z dan gabungannya. Setelah kolom Z, Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 5 of 74
kita akan menjumpai kolom AA, AB s/d AZ lalu kolom BA, BB s/d BZ begitu seterus sampai kolom terakhir yaitu IV (berjumlah 256 kolom) dan jumlah baris 1048576 baris dengan lebih dari 6000 kolom. 3. Cell Pointer Cell Pointer (penunjuk sel), adalah penunjuk sel yang aktif. Sel adalah perpotongan antara kolom dengan baris. Sel diberi nama menurut posisi kolom dan baris. Contoh. Sel A1 berarti perpotongan antara kolom A dengan baris 1. 4. Formula Bar Formula Bar, adalah tempat kita untuk mengetikkan rumus-rumus yang akan kita gunakan nantinya. Dalam Microsoft Excel pengetikkan rumus harus diawali dengan tanda ‘=’ . Misalnya kita ingin menjumlahkan nilai yang terdapat pada sel A1 dengan B1, maka pada formula bar dapat diketikkan =A1+B1 . Bekerja Dengan Tabel, Auto Fiill, Format Cells, Merge Cells & Wrap Text Ketika mengolah sebuah data pada microsoft Excel, penting untuk menjadikan data-data tersebut sebagai sebuah tabel excel. Hal ini akan mempermudah kita dalam mengolah dan menganalisa sebuah data seperti melakukan pengurutan, memfilter, mempercantik tampilan serta mempermudah kita dalam penulisan rumus-rumus excel. Tabel adalah kumpulan baris dan kolom yang terbentuk menjadi satu kesatuan dimana judul kolom disebut field, dan baris disebut data tabel. Pada sub modul ini kita akan belajar tentang cara membuat Tabel, Merubah atau mengganti nama tabel serta menghapus Tabel pada microsoft Office. Cara Membuat Tabel Pada Microsoft Excel Untuk menjadikan sebuah rentang atau range data sebagai Tabel ada dua cara : 1. Seleksi atau pilih rentang data yang ingin kita jadikan tabel. 2. Selanjutnya klik Menu Home dan Pilih Menu Tabel selanjutnya klik All Border seperti terlihat pada gambar dibawah ini Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 6 of 74
Membuat Auto Fill atau isian otomatis Daripada memasukkan data secara manual di lembar kerja, kita bisa menggunakan fitur Auto Fill atau Isian Otomatis untuk mengisi sel dengan data yang mengikuti pola atau berdasarkan pada data di sel lainnya. Fitur ini sangat berguna saat kita membuat sebuah rumus excel. Auto Fill merupakan salah satu fitur pada aplikasi Excel yang digunakan untuk mengisi data pada sel berdasarkan pola tertentu dari data pada sel lain. Fitur ini kita gunakan misalnya saja untuk membuat penomoran yang berurutan. Cara pengunaan fitur ini relatif sangat mudah, Anda bisa menggunakan tombol Auto Fill dalam group Editing pada tab Home atau menggunakan drag dengan mouse. Namun saya sendiri lebih menyukai Auto Fill dengan drag mouse karena merupakan cara yang paling mudah untuk dilakukan. Ada dua cara membuat Auto Fill (Isian Otomatis) Cara Pertama 1. Tulis angka 1 di Cell A1 2. Selanjutnya arahkan pointer ke cell angka 1 Tekan Tombol CTRL 3. Tekan tombol Control di Keyborad selanjutnya Drag Ke 4. Selanjutnya Drag kebawah Bawah Cara Kedua 1. Tulis angka 1 di Cell A2 2. Tulis Angka 2 di Cell A3 3. Blok Cell A1 dan A2 4. Arahkan Pointer ke Cell A3 5. Setelah Tanda + Muncul drag ke bawah Hasil Pembuatan Auto Fill Dalam kaitannya dengan penggunaan Formula Excel, Auto Fill ini bisa Anda gunakan untuk menyalin formula dari satu sel ke sel-sel lain yang berdekatan secara cepat. Prinsipnya sama dengan contoh Auto Fill diatas, yaitu selalu mengikuti pola yang sudah terbentuk pada sel yang menjadi acuan. Untuk lebih jelas, perhatikan contoh berikut ini. Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 7 of 74
Agar lebih cepat sekaligus mempermudah pekerjaan kita maka kita bisa menggunakan fitur Auto Fill yang telah disediakan Excel. Caranya sama seperti contoh Auto Fill sebelumnya. Anda tinggal memilih sel C2 yang telah berisikan formula perhitungan, lalu mendekatkan pointer mouse ke sudut kanan bawah dan kemudian klik, tahan, lalu drag ke arah bawah (Lihat Gambar Auto Fill Formula 2). Merge Cells & Wrap Text Berikut Penjelasan Tentang Wrap Text dan Merge Cells • Wrap text dan merge cells sangat penting jika anda bekerja menggunakan microsoft excel, karena fungsi tool tersebut saling melengkapi satu sama lain. Wrap text berfungsi untuk mengatur posisi text agar posisi text menyesuaikan sesuai dengan lebar kolom, sedangkan Merge cells berfungsi untuk menggabungkan cells pada tabel dimicrosoft excel. • Merge Cells adalah teknik untuk menggabungkan baris ata kolom menjadi satu misal Cell B6 digabung sampai D6. Caranya Blok Cell B6 sampai D6 kemudian pilih Merge & Center Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 8 of 74
Hasilnya Tampak Seperti Pada Gambar Berikut MERGE CELLS WRAP TEXT Format Cells Untuk Memformat Border Tabel dan Mata Uang Rupiah (Rp) Format cells Terdiri dari fungsi Number, Alignment, Font, Border, Fill dan Protection. Pada bagian menu tersebut terdapat beberapa kategori dan fungsi lainnya. Fungsi tersebut memiliki pengaturan tersendiri yang bisa kita atur sesuai keinginan kita. Penggunaan Format Cells - Border 1. Blok cell yang akan di format 2. Selanjutnya klik kanan dan pilih format cells 3. Pilih Jenis Format Cells Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 9 of 74
4. Pilih Menu Border, Selanjutnya Format Garis Border gunakan Style untuk memilih ketebalan dan jenis Garis 5. Gunakan Outline untuk membuat Garis Tepi, dan Inside untuk membuat garis didalam tepi 6. Dan None untuk Menghilangkan Border. Langkah Memformat Angka Menjadi Rupiah 1. Untuk memformat Mata Uang menjadi Rp caranya Blok Data selanjutnya pilih Symbol Rp di menu Format Cells – Pilih Accounting 2. Atau bisa juga di Menu Home jika Settingan Komputer menggunakan Tanggal Bahasa Indonesia 3. Untuk lebih jelas Lihat Gambar dibawah ini Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 10 of 74
Latihan Tabel, Auto Fill, Merge Cell, dan Wrap Text Untuk Melatih penggunaan Excel langkah awal mencoba membuat table sederhana dan penggunaan format cells serta Merge Cell. Langkah Membuat Tabel di Excel dan Memformatnya : 1. Buat Judul Tabel 2. Buat Judul / Field Kolom 3. Selanjutnya Atur lebar kolom 4. Gunakan Merge Cells untuk Memerge Baris & kolom 5. Buat Auto fill untuk memberi No Otomatis 6. Selanjutnya Buat Border Tabel Gunakan Format Cells 7. Untuk Membuat Wraptext Tambahkan satu kolom setelah Perusahaan dan isi dengan Alamat selanjutnya Format Menggunakan Wrap Text. Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 11 of 74
Manajemen View, Print Title, Layout Dan Printing Pada Sub bab ini akan dibahas Managemen Tampilan pada Ms Excel meliputi Mengatur Skala Layar, Memberi No Halaman, Print Title, Set Print Area, Page Break Preview, dan Cetak Dokumen Mengatur skala layar Pencetakan Melalui Menu Page Layout Dalam Menu Page layout ada beberapa fungsi Penting diantaranya scale, set Print Area, dan Prin Title. Berikut penjelasannya: • Scale Berfungsi untuk mengatur halaman seperi skala layar, set print area, print title, menentukan ukuran dan jenis kertas dan yang lainnya • Set Print Are, Berfungsi Untuk mencetak area tertentu saja atau mencetak area sel yang diinginkan, Kita dapat menggunakan fasilitas Print Area. Dengan menggunakan fitur Print area, maka hanya area sel yang terpilih saja yang akan dicetak. • Printe Title digunakan untuk mencetak juduk kolom atau baris secara berulang. Fungsi Print Tile Bila kita memiliki Tabel dengan jumlah baris yang sangat banyak dan panjang sudah pasti pada saat mencetaknya di printer kita akan menggunakan fasilitas set print area untuk memotong bagian-bagian tertentu yang akan di cetak dan harus mencetak ulang kepala baris dan kolom (Header Rows and coloum) dikertas agar semua baris dan kolom (Judul kolom) bisa tercetak. Sebagai contoh lihat table yang akan dicetak dibawah ini Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 12 of 74
Pada Tabel diatas Kita akan mencetak data Customer mulai dari cell A6 sampai E12 Berikut langkahnya: 1.. Klik Menu Ribbon Page Layout klik Page Setup 2. Selanjutnya klik Print Title , akan muncul tampilan berikut Klik Disini Untuk Menentukan Range yang akan di cetak dan Judul kolom yang akan ditampilkan 4. Pada Print Area Isi dengan data yang akan ditampilkan yaitu range A6 sampai E12 Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 13 of 74
Keterangan 1. Pada Rows to repeat at top ketikan / tentukan No baris yang akan di ulang dari Judul Kolom, dalam contoh ini adalah baris ke 2 2. Pada Coloum to repeat a left ketikan / tentukan range kolom yang akan ditampilkan yaitu kolom Customer No, Nama, Adress, Contact, dan Phone atau kolom A sampai E, seperti terlihat pada gambar diatas 3. selanjutnya klik OK dan klik Print Preview maka Hasilnya akan tampil seperti berikut. Hasil Setelah di set dan ditampilkan di print preview Keterangan Data yang di cetak adalah data yang telah di set di print area yaityu mulai dari customer No BDG00000 atau cell A6 sampai Cell E12 Rows to repeat at top : Nama baris yang berisikan judul table Columns to repeat at left : Nama kolom yang berisikan judul tabel Penulisan masing-masing dengan diawali tanda \"$\". Semisal barisnya adalah dari baris 2 maka ditulis : $2:$2 Page Break Area Page Break digunakan untuk membuat halaman secara terpisah pada suatu sheet. Jadi walaupun jarak antara range satu dengan range lainnya ataupun cell satu dengan cell lainnya begitu dekat, ia dapat dipisah halamannya saat di print preview/print. Marilah kita belajar membuat page break di dalam Microsoft Excel. Cara Membuat Page Break Area Di Excel Langkahnya Sbb : 1. Buka File Excel yang akan di set Page Break 2. Klik Menu View dan pilih Page Break 3. Untuk Mengatur halaman caranya tinggal tarik border garis warna biru ke bawah atau keatas sesuai halaman yang akan dibuat Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 14 of 74
Hasil Keterangan Page Break Preview digunakan untuk membagi halaman excel menjadi beberapa halaman Page Break Preview bisa di Tarik berdasarkan baris atau kolom Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 15 of 74
Memberi No Halaman Untuk Memberi No Halaman pada Dokumen Excel caranya 1. Buka Dokumen yang akan di beri no halaman 2. Selanjutnya klik menu File – Print 3. Kemudian Pilih Page setup, klik tab Header dan Footer dan Pilih Custom Footer 4. Pada Menu Custom Footer selanjutnya Insert No Halaman (Insert Page Number) Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 16 of 74
Print Dokumen Ke Printer Langkahnhya: • Buka Dokumen Excel • Sebelum Anda mencetak lembar kerja yang berisi data dalam jumlah besar, Anda dapat menyesuaikan lembar kerja dengan cepat dalam tampilan Tata Letak Halaman. Anda dapat melihat dan mengedit elemen seperti margin, orientasi halaman serta header dan footer • Selanjutnya pada Menu Pilih Print, akan muncul Tampilan Tombol Print dan Jumlah Halaman Yg akan di Print. Serta Pilihan Printer Keterangan Dalam Mencetak dokumen bisa diatur Mulai dari Jenis Printer Di Menu Setting Terdapat Bagian-bagian yang sering digunakan diantaranya Pages yaitu untuk menentukan Halaman yang akan di cetak II. Bekerja Dengan Rumus dan Fungsi Dasar Mengatur Regional Setting Dalam mengolah data menggunakan Microsoft Office Excel khususnya bekerja dengan rumus dan Fungsi Excel kita perlu memperhatikan setting bahasa atau regional setting komputer yang kita pakai. Karena hal ini akan mempengaruhi cara kita dalam menuliskan rumus-rumus excel maupun fungsi- fungsi dalam Microsoft Excel. Selain itu regional setting ini juga akan mempengaruhi bagaimana format tampilan angka, mata uang dan tanggal dalam data yang kita olah. Secara default (biasanya) bahasa komputer memakai setiing regional English versi UK maupun US. Sedangkan dalam tutorial-tutorial belajar excel blog ini saya memakai setting bahasa Indonesia agar lebih sesuai dengan keadaan kita sehari-hari. Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 17 of 74
Cara Merubah Regional Setting Windows 7 Untuk merubah regional setting atau pengaturan bahasa ini pada sistem operasi windows 7 ikuti langkah-langkah sebagai berikut: 1. Cari menu Control Panel. klik START ---> Control Panel 2. Pada jendela Control Panel Pilih bagian Clock Language and Region --> Change display language Jika view by Category Atau pilih Region and Language jika view by small icons dan view by large icons 3. Pada tab Format, cari dan pilih Indonesian (Indonesia) 4. Klik OK Untuk lebih jelasnya perhatikan gambar berikut : (Cara Mengatur Regional Setting Komputer 1) Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 18 of 74
(Cara Mengatur Regional Setting Komputer 2) (Cara Mengatur Regional Setting Komputer 3) (Cara Mengatur Regional Setting Komputer 4) Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 19 of 74
Jika anda memakai sistem operasi selain Windows 7 silahkan menyesuaikan. Perbedaan Regional Setting English (US) dan Bahasa Indonesia Perbedaan apa saja yang dipengaruhi oleh regional setting ini dalam program Microsoft Excel? Setidaknya akan ada 7 (Tujuh) perbedaan yang dipengaruhi oleh regional setting ini. 1. Tanda pemisah ribuan: English US memakai koma (,) contoh: 1,000 sedangkan bhs. Indonesia memakai titik (.) contoh: 1.000. 2. Tanda pemisah angka desimal: English US memakai titik (.) contoh: 0.25 sedangkan bhs. Indonesia memakai koma(,) contoh: 0,25. 3. Penamaan Hari: English US memakai Bahasa Inggris,contoh: Sunday, sedangkan bhs. Indonesia memakai bahasa Indonesia, contoh: Minggu. 4. Penamaan Bulan : English US memakai Bahasa Inggris,contoh: January-December, sedangkan bhs. Indonesia memakai bahasa Indonesia, contoh: Januari-Desember. 5. Format default tanggal : English US memakai format Bulan/Tanggal/Tahun, sedangkan bhs. Indonesia memakai format Tanggal/Bulan/Tahun 6. Tanda Mata uang : English US memakai Dollar ($), sedangkan bhs. Indonesia memakai Rupiah (Rp.) 7. Pemisah Argumen : Dalam penulisan rumus excel English US memakai koma (,) sedangkan bahsa Indonesia memkai pemisah titik koma(;) Pengenalan Formula Di Excel Formula atau rumus adalah suatu gabungan dari beberapa elemen yang akan menghasilkan suatu nilai kembalian. Elemen-elemen tersebut adalah : fungsi operator referensi cell nilai konstan berupa angka atau teks Contoh rumus dan hubungan dari tiap elemen tersebut di atas ditunjukkan pada ilustrasi berikut. Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 20 of 74
Penulisan Rumus pada Excel selalu dimulai dengan tanda sama dengan ( = ) Daftar Operator yang Dapat Digunakan Selain fungsi, banyak juga yang menanyakan bagaimana penggunaan operator seperti tanda +, -, *, /, dan lain-lain. Berikut adalah kategori dan daftar operator yang ada pada Microsoft Excel 2007 dan keterangan untuk membantu menjelaskan penggunaannya : Operator Aritmatika o + (tanda plus) : digunakan untuk menjumlahkan dua nilai. o - (tanda minus) : digunakan untuk mengurangi dua nilai, atau digunakan sebagai tanda negasi pada suatu angka konstanta. o * (tanda bintang) : digunakan untuk mengalikan dua nilai. o / (tanda garis miring) : digunakan untuk membagi suatu nilai dengan nilai lainnya. o % (tanda persen) : digunakan untuk merepresentasikan nilai persentase. o ^ (tanda sisipan) : digunakan sebagai tanda pangkat. Gambar 1 : Contoh Penggunaan Operator Aritmatika Operator Perbandingan Nilai o = (tanda sama dengan) : menunjukkan apakah nilai yang satu sama dengan nilai lainnya. o > (tanda lebih besar) : menunjukkan apakah nilai yang satu lebih besar dengan nilai lainnya. Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 21 of 74
o < (tanda lebih kecil) : menunjukkan apakah nilai yang satu lebih kecil dengan nilai lainnya. o >= (tanda lebih besar sama dengan) : menunjukkan apakah nilai yang satu lebih besar atau sama dengan nilai lainnya. o <= (tanda lebih kecil sama dengan) : menunjukkan apakah nilai yang satu lebih kecil atau sama dengan nilai lainnya. o <> (tanda lebih kecil dan lebih besar) : menunjukkan ketidaksamaan antara dua nilai. Gambar 2. Contoh Penggunaan Operator Perbandingan Nilai Operator Penggabungan Teks o & (tanda dan / ampersand) : digunakan untuk menggabungkan dua atau lebih teks. Gambar 3. Contoh Penggunaan Operator Penggabungan Teks Operator Referensi o : (tanda titik dua) : operator range (rentang nilai) yang mengambil dua referensi sel sebagai awal dan akhir rentang nilai. o , (tanda koma) : operator union yang digunakan untuk menggabungkan dua atau lebih referensi menjadi satu referensi sel. o (tanda spasi) : operator irisan yang digunakan untuk menghasilkan perpotongan dari dua referensi atau lebih menjadi satu referensi sel. Gambar 4. Sumber Data Contoh 5. Contoh Penggunaan Operator Referensi (dibantu dengan fungsi SUM) Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 22 of 74
Fungsi Dasar excel Yang sering digunakan Rumus dan fungsi memiliki pengertian berupa instruksi matematika pada sel atau range yang digunakan untuk proses perhitungan. Rumus yang digunakan di excel merupakan formula siap pakai yang melakukan operasi berkelanjutan terhadap suatu nilai dalam range khusus. Beberapa fungsi yang terdapat dalam Microsoft excel diantaranya, fungsi matematika, fungsi statistika, fungsi logika, fungsi lookup (vlookup & hlookup). Sebagai contoh untuk menentukan jumlah angka dalam sel A1 sampai H1, kita dapat memasukan fungsi = Sum(A1:H1) daripada memasukan harus =A1+B1+C1 dan seterusnya Fungsi Matematika & Statistika Dasar Kita dapat memasukkan rumus yang berupa instruksi matematika dan statistika dasar ke dalam suatusel pada lembar kerja. Operator hitung yang dapat digunakan diantaranya adalah + (penjumlahan), －(pengurangan), * (perkalian), dan ^ (perpangkatan).Rumus atau Formula dasar Microsoft Excel yang harus diketahui adalah Fungsi SUM, Average, Max, Min, dan Count, berikut ini penjelasan dan contoh kasus dari fungsi yang saya sebutkan tadi. 1. Fungsi SUM, digunakan untuk menjumlahkan nilai yang ada pada range tertentu. Ketikkan di lembar kerja anda seperti di bawah ini : Kemudian di sel A7 masukan fungsi =SUM(A2:A7) kemudian enter. Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 23 of 74
Hasilnya Ket : Menjumlahkan semua data yang ada dalam range yang dimaksud, yaitu dari A2 hingga A6. 2. Fungsi Average, digunakan untuk mencari nilai rata-rata dari suatu range. Dari data sebelumnya kita akan belajar membuat fungsi average (). Ketikkan di E2, =AVERAGE(A2:D2) kemudian enter. Hasilnya Ket : Hasil di sel E2 adalah 78.5, yang merupakan nilai rata-rata dari A2 hingga D2. Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 24 of 74
3. Fungsi Max, digunakan untuk mencari nilai tertinggi dari suatu range. Sebagai contoh ketikkan dilembar kerja seperti gambar dibawah ini Di sel B9, ketikkan rumus =MAX(B2:B8) Ket : Hasil dari sel B9 adalah 800000 Nilai tertinggi dari kolom harga. 4. Fungsi Min, digunakan untuk mencari nilai terendah dari suatu range. Di sel B9, ketikkan rumus =MIN(B2:B8) Ket : Hasil dari sel B9 adalah 110000 Nilai terendah dari kolom harga. 5. Fungsi Count, digunakan untuk mencari banyaknya data dari suatu range. Di sel B9, ketikkan rumus =COUNT(B2:B8) Ket : Hasil dari sel B9 adalah 7 (Banyaknya jumlah barang) Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 25 of 74
Sampa disini kita sudah selesai mempelajari Fungsi Dasar Formula Pada Microsoft Excel : SUM, Average, Max, Min, Dan Count sebelum menutup artikel ini saya akan memberikan soal latihan buat teman-teman saya harap bisa dikerja agar terbiasa dengan fungsi dasar formula di atas. Soal Latihan Fungsi Matetika & Statistika Petunjuk Pengerjaan 1. Isikan nilai total nilai dan rata-rata setiap siswa 2. Lalu isikan di bagian bawah dengan total nilai, nilai rata-rata, nilai terbesar, nilai terendah dan jumlah siswa. Gunakan fungsi sum(), average(), max(), min(), count(). Bekerja dengan Fungsi Count, Counta, CountBlank 1. COUNT RUMUS : =COUNT(RANGE) Adalah Rumus untuk menghitung banyak angka ( hanya angka ) bisa juga huruf atau simbol dalam sebuah barisan cell. Misalnya anda ingin menghitung berapa jumlah data dari table dibawah ini. Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 26 of 74
Formulanya: =COUNT(D3:D6) lalu ENTER. Anda akan mendapatkan jumlah dari datanya adalah 4 2.CountA Adalah Rumus untuk menghitung banyaknya data( hanya data) pada sebuah barisan cell. Hampir sama dengan Count tetapi bedanya di angka dan data saja. Misalnya, anda ingin menghitung banyaknya data dari table dibawah ini. Rumusnya: =COUNTA(B3:B6) Lalu, ENTER dan anda akan dapatkan hasilnya adalah 12 Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 27 of 74
CountBlank CountBlank adalah fungsi yang digunakan untuk menghitung jumlah atau banyaknya sel yang kosong yang terdapat dalam suatu range data. Format: =COUNTBLANK(range) range: berupa referensi range yang akan dihitung jumlah sel kosong. Bagaimanakah cara menghitung jumlah cell yang kosong di dalam suatu range data?Suatu saat anda mempunyai table data pada file excel dimana anda memerlukan informasi jumlah cell yang kosong (tidak ada nilainya alias blank).Anda bisa saja menghitungnya secara manual, tetapi jika table data berupa range yang besar misalnya lebih dari 1000 baris dan lebih dari 100 kolom tentu akan merepotkan anda.Untuk keperluan ini anda dapat memanfaatkan fungsi COUNTBLANK dari pada menghitung jumlah cell yang kosong secara manual. Contoh: Hasilnya adalah 2. Artinya jumlah data yang kosong dari sel C1 hingga C7 adalah 2(teks yang berisi tidak dihitung Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 28 of 74
Fungsi Countif dan Countifs Menghitung banyaknya data dengan kriteria tertentu,misalnya banyak data dalam sebuah range ada berapa kata/sel yang sama, bisa dia berupa Nama yang sama, angka yang sama dan lain sebagai nya. maka kita dapat menentukann ada berapa banyak sel tersebut. Contohnya, kita ingin menghitung ada berapa data dengan jumlah 1. Maka rumusnya =countif(D3:D6;1) tekan ENTER,maka anda akan dapatkan hasilnya adalah 2. Artinya, ada 2 data dengan jumlah 1. Perhatian : Pada Countif Untuk Kriteria Ada 2 Jenis Yang Pertama Kriteria Berupa Angka Dan Kriteria Berupa Huruf, Contoh Diatas Kriteria Berupa Angka Dan Jika Kriteria Berupa Huruf Maka Anda Harus Menggunakan Tanda Petik Dua Seedangkan untuk Fungsi Countifs berfungsi untuk menyeleski data berdasarakan banyak kriteria, contohnya bisa dilihat pada soal latihan berikut: Penggunaan Fungsi Countifs dengan 2 kriteria Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 29 of 74
Fungsi Absolute Salah satu rumus yang paling penting akan tetapi jarang dimengerti adalah fungsi absolut pada Ms. Excel. Fungsi absolut adalah sebuah fungsi dimana ia membuat alamat cell selalu sama meskipun kemudian ia dicopykan ke kolom yang lain artinya Sel absolut adalah lembar kerja atau alamat sel yang tidak mengalami perubahan dimana pada saat menjumlahkan, mengurangi, membagi maupun menggabungkan terdapat satu sel yang dikunci dan tidak bisa berubah. Dalam penulisannya di Ms. Excel, fungsi absolut ditulis dengan menggunakan simbol $. Fungsi absolut ini memiliki dua jenis, yakni; Absolut Mutlak. Yang dimaksud dengan fungsi absolut mutlak adalah fungsi dimana kuncian kolom dan barisnya sama sekali tidak berubah. Contohnya : $C$9 Dari contoh di atas, maka penguncian itu dilakukan pada kolom C dan baris ke-9. Meskipun hendak di kopi dimanapun, di kolom lain di Ms. Excel, ia tidak akan berubah. Semi Absolut. Semi absolut disini maksudnya adalah penguncian yang hanya berlaku pada kolomnya atau barisnya saja, tidak pada keduanya. Contoh: $C9 atau C$9 Jika lambang $ terletak di belakang C secara otomatis yang dikunci hanyalah kolom C saja dan jika $ berada di belakang 9 maka yang dikunci hanya baris 9. Contoh Soal Perhatikan soal berikut ini : Dalam penyelesaian soal tersebut kita cukup menggunakan rumus 1 kali kemudian di fill handle / Auto Fill Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 30 of 74
Fungsi Absolute dalam pemahamannya dapat dibagi menjadi 4 bagian 1. F4 - 1 Kali : Diperuntukan untuk data yang berada pada Kolom dan baris tetap ( data hanya 1. example : $A$1 2. F4 - 2 Kali : Diperuntukan untuk data yang berada pada BARIS tetap ( Pergerakan data secara horisontal atau posisi data horisontal ) Example : A$1 Contoh dalam soal harga per lembar ( 75 - 100 - 150 - 200 )berada pada ( B5 - C5 - E5 - F5 ) 3. F4 - 3 Kali : Diperuntukan untuk data yang berada pada KOLOM tetap ( Pergerakan data secara Vertikal atau posisi data Vertikal) Example : $A1 Contoh dalam soal Jummlah lembar ( 1 - 5 -10 - 15 - 20 .... 100 ) berada pada ( A1- A2- A3- A4 ... A16) 4. F4 - 4 Kali : Untuk Menghilangkan fungsi absolute ( Kembali ke bantuk semula ) example : A1 Penyelesaian Soal Logika penyelesaian adalan Jumlah Lembar * Harga jika kita tidak menggunakan fungsi absolute maka penyelesaian rumus harus dijalankan 1 per satu Dengan memperhatikan posisi datanya maka o Jumlah lembar berada pada kolom tetap A1 : A16 maka untuk penyesaiannya Jumlah lembar diabsolutekan sebanyak 3 kali o Harga berada pada baris tetap B5: E5 maka untuk penyesaiannya Jumlah lembar diabsolutekan sebanyak 2 kali jadi secara lengkap fungsi menjadi =$A6*B$5 Penggunaan AutoFill berada di ujung kanan bawah ( Plus Hitam ) Perhatikan bagan di bawah ini : Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 31 of 74
Fungsi Ranking Fungsi RANK adalah merupakan salah satu fungsi dari bagian Statistik dalam Microsoft Excel yang bertujuan atau menghasilkan nomor urut suatu nilai dalam suatu daftar nilai. Penggunaan fungsi RANK digunakan dalam memberikan peringkat (Ranking). Bentuk umum dari Fungsi RANK adalah =RANK(number,ref,order) Keterangan: Number: Nilai (alamat cel) yang akan dicari nomor peringkatnya, Ref: Semua daftar nilai yang akan diurutkan dan berfungsi sebagai bahan pembanding, Order: Nilai yang menentukan bagaimana bentuk urutan, ada dua pilihan dalam order, yaitu: 0-Ascending: Excel akan mengurutkan nilai tertinggi pada urutan 1, dst. Dan 1-Ascending: maka excel akan mengurutkan nilai terkecil sampai ke tertinggi Latihan Penggunaan Fungsi Ranking Fungsi Ranking Number: C2 Ref: C2:C16 Order: 0 Sebagai catatan, pada bagian Ref: C2:C16 adalah daftar nilai tetap dan pembanding maka pada cell ini harus dikunci dengan cara menekan F4, sehingga C2:C16 menjadi $C$2:$C$16, sehingga secara lengkap fungsinya berubah menjadi =RANK(C2;$C$2:$C$16;0) Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 32 of 74
Fungsi Waktu dan Tanggal di Excel kita berlanjut ke rumus excel untuk date and time. Rumus ini bisa kita gunakan untuk mengolah data- data yang berhubungan dengan waktu baik tahun, bulan, tanggal, jam, menit dan detik.Jika Anda menggunakan Excel secara berkala, Saya yakin Anda mendapati tanggal dan waktu di sel-sel Excel Anda. Data seringkali memiliki catatan kapan dibuat atau diperbarui, jadi sangat penting mengetahui bagaimana cara bekerja dengan data ini. Berikut adalah tiga skill utama yang akan kamu pelajari dalam tutorial ini: Bagaimana memformat tanggal dalam Excel sehingga tampil sesuai yang Anda inginkan Rumus untuk menghitung jumlah hari, bulan, dan tahun antara dua tanggal. Rumus tanggal Excel untuk memasukkan tanggal hari ini, dan shortcut keyboard untuk menambahkan waktu saat ini. Dalam mengisi cell pada Microsoft Excel berupa tanggal, biasanya kita terkendala dengan format tanggal yang tidak sesuai. Tak jarang format tersebut berubah setelah kita tuliskan. Umumnya, format tanggal pada Microsoft Excel mengikuti format penulisan bahasa Inggris dimana dimulai dengan bulan, kemudian tanggal, dan tahun. Pada dasarnya Microsoft Excel bisa melakukan apapun dengan data, jika Anda tahu bagaimana caranya. Tutorial ini adalah langkah kunci lainnya untuk menambah keahlian Excel Anda BEBERAPA RUMUS DATE DAN TIME EXCEL Now Now rumus ini bisa digunakan untuk menuliskan waktu tanggal dan jam saat itu secara otomatis, sesuai yang tertera disistem komputer sytanks penulisannya : =NOW () contoh hasilnya 17/11/2014 19:58 Today Today rumus ini untuk menuliskan tanggal saat itu secara otomatis, sesuai yang tertera disistem komputer, perbedaanya dengan now, kalau rumus today hanya menampilkan tanggal tidak disertai jamnya. sytanks penulisannya : =TODAY() contoh hasilnya 17/11/2014 Time Time digunakan untuk menuliskan format waktu dari urutan angka sytanks penulisannya : =TIME(hour;minute;second) contohnya =TIME(12;10;50) hasilnya 12: 10 PM Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 33 of 74
Dan Untuk Memformat Tanggal dan Waktu bias masuk ke menu Format Cell kemudian pilih Date and time, kemudian tentukan format tanggalnya. III. Bekerja Dengan Conditional Formatting Pengenalan Conditional Formatting Microsoft Excel mempunyai banyak sekali fitur yang dapat memudahkan penggunanya, salah satunya adalah conditional formatting. Fasilitas ini mirip Format Cell pada excel akan tetapi mempunyai fungsi yang berbeda. Conditional formatting pada excel adalah fitur yang berfungsi untuk memberikan format pewarnaan pada cell sesuai dengan kondisi/kriteria tertentu. Conditional formatting dapat memformat tipe data dalam bentuk number ataupun text semuanya dapat dengan mudah kita format sesuai kebutuhan. sebagian besar user excel, mewarnai suatu cell secara manual (data difilter kemudian diberi warna sendiri secara manual). jika datanya hanya beberapa cell saja mungkin masih bisa manual tapi kalau datanya sudah ribuan akan sangat merepotkan bukan? Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 34 of 74
Bagian Pemformattan cell meliputi Font (Type, size, dan color) Shading (Warna background) Border (garis pembatas) Tipe Conditional Formatting pada Excel Ada 5 type conditional formatting pada excel yang dapat kita gunakan 1. Highlight Cell Rules, Memformat data dengan syarat lebih besar dari, kurang dari, sama dengan, atau antara. 2. Top/Bottom Rules, Memformat data dengan syarat 10 tertinggi atau terendah. 3. Data Bars, Memformat data dengan warna bar dengan syarat dari nilai terendah sampai tertinggi secara otomatis. 4. Color Scales, Memformat data berdasarkan skala warna yang berbeda dengan syarat dari nilai terendah sampai tertinggi. 5. Icon Sets, Memformat data dengan icon/gambar berdasarkan dengan kondisi tertentu. Cara Penggunaan Conditional Formatting pada excel Contoh Kasus : Berikan pewarnaan pada tabel dibawah ini untuk kolom gaji dengan kondisi Berikan Warna hijau untuk gaji lebih besar atau sama dengan 4.000.000 Berikan Warna kuning untuk gaji lebih besar atau sama dengan 2.000.000 berikan Warna merah untuk gaji dibawa 2.000.000 Penyelesaian 1. Blok range kolom gaji (Nilainya saja) 2. Pada tab Home pilih Conditional formatting. 3. Pemberian warna cell dapat gunakan highlight cell rules, pilih greather than. 4. Pada dialog box ketikan angka 4000000 dengan tipe format yang dapat anda pilih sesuai kebutuhan Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 35 of 74
default format pilihan format default hanya ada beberapa saja, anda dapat gunakan custom format pada pilihan paling bawah. 5. Pada custom format cell, klik tab fill pilih bacground warna hijau kemudian tekan OK. custom format Untuk mewarnai warna kuning dan merah caranya sama dengan step diatas, dalam keadaan kolom gaji yang masih terseleksi ulangi langkah 2-5. dengan hasil pewarnaanya adalah berikut ini. Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 36 of 74
VI. Bekerja Dengan Rumus Lanjutan Pada Bab ini akan dibahas rumus-rumus lanjutan di Excel diantaranya Fungsi Sumif dan Sumifs, Logika IF, dan Fungsi Lookup (Vlookup dan Hlookup) Fungsi SumIF dan SumIFS Fungsi SumIF Fungsi Sumif dan sumifs merupakan fungsi yang sering sekali digunakan dalam pembuatan data dengan menggunakan aplikasi Microsoft Excel. Fungsi SUMIF dan SUMIFS sebenarnya adalah gabungan dari 2 fungsi Excel yakni SUM yang berguna untuk melakukan penjumlahan data dan IF yang biasa digunakan untuk menentukan kondisi TRUE atau FALSE. jadi disini maksudnya adalah fungsi ini digunakan untuk melakukan penjumlahan data dengan criteria tertentu. Perbedaan dasar dari kedua fungsi ini yaitu fungsi SUMIF hanya dapat menjumlahkan sebuah nilai data dalam suatu range dengan satu syarat tertentu. Sehingga penulisan kriteria untuk penjumlahan tersebut hanya satu saja. Sedangkan pada rumus SUMIFS dapat menjumlahkan suatu nilai dalam range tertentu dengan syarat yang lebih dari satu. Sehingga pengguna yang ingin menjumlahkan sebuah nilai data dengan banyak syarat bisa menggunakan fungsi SUMIFS. Dan sebagai catatan dalam fungsi SUMIFS akan ada perubahan kode penulisan yang memisahkan kriteria satu dengan kriteria lainnya yang disisipkan. Penulisan Syntax fungsi SUMIF adalah sebagai berikut. =SUMIF(range; criteria; [sum_range]) Contoh Penggunaan Rumus SumIF Tentukan Total pembelian masing masing barang dari LCD Monitor, CPU, Keyboard dan Motherboard. Menggunakan fungsi SumIF, dimana Untuk menentukan hasil penjumlahan berarti kita harus mencari penjumlahan dari Range D3 sampai ke D19 dengan syarat bahwa nilai C adalah salah satu dari LCD Monitor, CPU, Keyboard dan Motherboard. Demikian penggunaan dari Fungsi SUMiF pada Ms Excel. Penulisan rumusnya dan Tabel Latihan Seperti Berikut Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 37 of 74
Rumus : 1. Menghitung LCD Monitor : =SUMIF(C3:C19,\"LCD MONITOR\",D3:D19) 2. Menghitung CPU : =SUMIF(C3:C19,\"CPU\",D3:D19) 3. Menghitung KeyBoard : =SUMIF(C3:C19,\"KeyBoard\",D3:D19) 4. Menghitung Motherboard : =SUMIF(C3:C19,\"MotherBoard\",D3:D19) Fungsi SUMIFS Rumus SUMIFS fungsinya hampir sama dengan fungsi SUMIF yaitu melakukan penjumlahan data dengan kriteria tertentu, bedanya fungsi SUMIF hanya untuk satu kriteria sedangkan fungsi SUMIFS untuk yang lebih dari satu kriteria atau kriteria banyak. Penulisan syntaxnya adalah : =SUMIF (sum_range; criteria_range1; criteria1; criteria_range2; criteria2;...) Perbedaan dengan Fungsi SUMIF adalah SUMIFS mempunyai criteria lebih dari satu criteria. Kalau SUMIF hanya mempunyai satu criteria dan SUMIFS lebih. Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 38 of 74
Soal & Skenario Latihan Fungsi SumIfs Carilah 1. Jumlah total Harga dari Motherboard dengan kondisi baik. 2. Total Pembelian Keyboard Cacat 3. Total Pembelian LCD Monitor Baik Penyelesaian 1. Untuk soal Pembelian Motherboard yang baik gunakan rumus fungsi SUMIFS seperti dibawah ini =SUMIFS(E3:E19,C3:C19,\"MOTHERBOARD\",D3:D19,\"Baik\") 2. Untuk Soal Pembelian Keyboard Cacat gunakan RUmus SUMIFS seperti pada dibawah ini. =SUMIFS(E3:E19,C3:C19,\"KEYBOARD\",D3:D19,\"CACAT\") 3. Untuk SOal Pembelian LCD monitor Baik gunakan Rumus SUMIFS seperti ini =SUMIFS(E3:E19,C3:C19,\"LCD MONITOR\",D3:D19,\"Baik\") Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 39 of 74
Latihan Fungsi Sumif dan Sumifs Untuk lebih memahami tentang fungsi SUMIF dalam Microsoft Excel, kerjakanlah soal dibawah ini. BERIKUT INI ADALAH PENULISAN RUMUS UNTUK COLOM C 12, C13,C14,C15 Total Pembelian CPU gunakan rumus =SUMIF(B2:B11,\"CPU\",C2:C11) Total pembelian LCD gunakan Rumus =SUMIF(B2:B11,\"LCD\",C2:C11) Total Pembelian Keyboard gunakan rums =SUMIF(B2:B11,\"Keyboard\",C2:C11) Total Pembelian Laptop gunakan rumus =SUMIF(B2:B11,\"Laptop\",C2:C11) Latihan Soal Sumifs Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 40 of 74
Rumus Yang digunakan =SUMIFS(E68:E77;C68:C77;”L”;D68:D77;”M”) Keterangan Sum_range adalah E68:E77, area yang akan dihitung jumlahnya. Criteria_range 1 adalah C68:C77, area yang didalamnya terdapat nilai kunci. Criteria 1 adalah “L”, merupakan nilai kunci. Anda juga bisa mengganti “L” dengan C68 (memakai alamat sel). Criteria_range 2 adalah D68:D77, area yang didalamnya terdapat nilai kunci. Criteria 2 adalah “M”, merupakan nilai kunci. Anda juga bisa mengganti “M” dengan D68 (memakai alamat sel). Fungsi Logika (Logika IF) Fungsi logika digunakan untuk menyeleksi suatu kondisi, bila kondisi yang diseleksi terpenuhi, maka statemen yang mengikuti akan diproses. Sebaliknya bila kondisi tidak terpenuhi maka yang akan diproses adalah statemen berikutnya. Artinya Fungsi Logika memerlukan adanya pernyataan penyangkalan test logika antara benar atau salah. Fungsi Logika Terbagi menjadi dua Fungsi Logika IF Tunggal dan Logika IF Bertingkat. Logika IF Tunggal Logika IF Tunggal terdiri dari dua Pernyaan Benar dan Salah (True or False). Bentuk umum adalah sebagai berikut : =IF(logical_test;value_if_true;value_if_false) =IF(A, B, C) A = Persyaratan yang akan di uji B = Apabila kondisi uji benar C = Apabila kondisi uji salah OPERATOR ARTI CONTOH EKSPRESI LOGIKA LOGIKA ….. Lebih kecil/kurang dari …… B5<90 < ….. Lebih besar/lebih dari ….. B5>90 > ….. Sama dengan ….. B5=”LUNAS” = ….. Kurang dari atau sama dengan …. B5<=90 <= ….. Lebih dari atau sama dengan …. B5>=90 >= ….. Tidak sama dengan ….. B5<>90 <> ATAU (Ekspresi 1; Ekspresi 2; …) OR(B5=3;D5=8) OR DAN (Ekspresi 1; Ekspresi 2; …) AND(B5=3;D5=8) AND Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 41 of 74
Untuk lebih jelasnya penggunaan fungsi logika IF kerjakan latihan dan buat tabel seperti pada gambar dibawah ini : Ketentuan : Kolom Jumlah Nilai merupakan total dari nilai hasil test Nilai rata-rata adalah nilai yang akan dijadikan acuan keterangan Kolom keterangan diisi dengan ketentuan sebagai berikut : Dinyatakan DITERIMA jika nilai rata-ratanya diatas 76, tetapi jika nilai rata-rata yang diperolehnya lebih kecil dari 76 maka dinyatakan GAGAL Langkah Pengerjaan Kolom Tanggal 1. Klik Sel D5 2. Tekan Tombol CTRL dan titik koma (;) Kolom Waktu 1. Ketik sel H5 2. Tekan tombol CTRL bersama-sama SHIFT dan tombol titik koma (;) Kolom Jumlah Nilai 1. Klik sel H9 2. Ketik tombol = 3. ketik SUM( 4. Blok sel E9 sampai sel G9 5. Tekan ENTER Kolom Rata-Rata Padepokan IT Course Page 42 of 74 1. Klik sel I9 2. Ketik tombol = 3. Ketik AVERAGE( 4. Blok sel E9 sampai G9 5. Tekan ENTER Modul Excel Fundamental Basic - Intermediate
Tips : Untuk membulatkan nilai rata-rata klik sel I9 sampai I13 kemudian klik Format Cell pilih number dan pada decimal places isi 0 Kolom Keterangan 1. klik sel J9 2. ketik tombol = 3. Ketik IF 4. Klik sel I9 5. Ketik >76,”DITERIMA”,”GAGAL”) 6. Tekan Enter Tips : Jika menuliskan tanda koma (,) tidak bisa atau ada kesalahan ganti dengan tanda tirik koma (;) Logika IF Bertingkat Rumus Excel IF Bertingkat terdiri dari lebih satu atau banyak IF yang bisa kita sesuaikan sesuai dengan kebutuhan tergantung dari berapa jumlah kondisi yang akan kita tetapkan.Rumus ini Digunakan jika kondisi yang dibandingkan melebihi dari satu kondisi =IF(logical_test;value_if_true;IF(logical_test;value_if_true;IF(... \"value_if_false dst)) Fungsi ini sering disebut juga sebagai fungsi untuk pengambilan keputusan, dimana kita menetapkan rule atau aturan dalam menentukan nilai yang berbeda, sehingga nilai tersebut akan dikelompokkan sesuai dengan batasan yang telah ditentukan. Dimana if Bertingkat atau bercabang terdiri dari banyak Kondisi IF Syarat: o Penulisan rumus hampir sama dengan fungsi IF Tunggal. o Hanya saja setiap pernyataan harus memiliki rentang nilai (interval) dan diikuti nilai berupa TEKS yang berbeda-beda. o Nilai setiap pernyataan hanya menggunakan nilai batas bawah dari sebuah interval. o Pernyataan terakhir ditulis hanya dengan nilai TEKS tanpa ada pernyataan seperti pernyataan sebelumnya. o Saat mengakhiri rumus fungsi tersebut, maka ditutup dengan KURUNG TUTUP yang terakhir sesuai dengan jumlah KURUNG BUKA yang telah digunakan. Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 43 of 74
Latihan Penggunaan Rumus IF Bertingkat di Excel Berikut ini beberapa contoh simulasi dasar penggunaan fungsi if bercabang yang dapat sobat kembangkan sesuai dengan kebutuhan dan kondisi yang ada. 1. Rumus IF dengan 3 kondisi Rumus if dengan 3 kondisi merupakan penggunaan rumus if pada suatu keadaan dengan hasil yang akan dikembalikan berjumlah 3 keadaan dengan 2 nilai logika yang diberikan. Sebagai contoh penggunaan rumus if dengan 3 kondisi ini adalah diilustrasikan sebagai berikut : 1. Jika memenuhi logika pertama maka akan dikembalikan nilai pertama. (contoh jika kode adalah JKT maka kembalikan hasil pada nama kota adalah jakarta ) 2. Jika tidak memenuhi logika pertama maka cek dengan logika kedua. Jika pada logika kedua terpenuhi maka kembalikan nilai kedua. (contoh jika kode adalah SBY maka kembalikan hasil pada nama kota adalah surabaya) 3. Jika pada logika pertama dan logika kedua tidak terpenuhi, maka kembalikan nilai kota belum terdaftar (jadi apabila pada test logika kode bukan merupakan JKT atau SBY maka akan dikembalikan dengan kondisi ketiga yaitu kota belum terdaftar) Untuk contoh penerapannya sobat excel bisa perhatikan pada contoh gambar if dengan 3 kondisi 1 dibawah ini, rumus formula if bertingkat dengan 3 kondisi ditempatkan pada kolom F : Dari formula diatas dapat dijabarkan penggunaan fungsi if sebagai berikut Pertama akan dilakukan pengecekan dengan (E2=\"JKT\") artinya Jika sel E2 sama dengan (=) JKT maka kembalikan nilai \"Jakarta\". Apabila pada sel E2 bukan merupakan JKT maka akan dicek dengan (E2=\"SBY\") artinya Jika sel E2 sama dengan (=) SBY maka kembalikan nilai \"Surabaya\". Kemudian apabila sel E2 bukan JKT dan juga bukan SBY maka kembalikan nilai \"Kota Belum Terdaftar\". Dengan rumus yang sama pada penggunaan fungsi if diatas, pengecekan dengan string teks bisa diganti dengan pengecekan yang mengacu kepada lokasi suatu sel misalnya “JKT” diganti dengan sel A2 dan SBY diganti dengan sel A3. Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 44 of 74
Contoh Rumus IF Bertingkat Lainnya Menyeleksi Agama Berdasarkan Kode =IF(E6=1;\"ISLAM\";IF(E6=2;\"KRISTEN\";IF(E6=3;\"HINDU\";IF(E6=4;\"BUDHA\";\"ANIMISME\")))) OUTPUT Latihan Logika IF Bertingkat untuk Menghitung Grade Nilai Membuat Grade Nilai di Micorosoft Excel| Sederhana tapi kadang menyulitkan, begitulah ketika kita hendak membuat grade nilai. Sederhan karena kita hanya membuat grade dari rentang nilai yang ditentukan menjadi bentuk huruf A, B, C atau D misalnya. Ouputnya Seperti terlihat Pada Gambar di Bawah ini Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 45 of 74
Fungsi Vlookup dan Hlookup Pengenalan Fugsi Vlookup dan Hlookup Fungsi Lookup terbagi menjadi fungsi VLOOKUP dan HLOOKUP dalam Microsoft Excel berguna untuk membaca suatu tabel, lalu mengambil nilai yang diinginkan pada tabel tersebut berdasarkan kunci tertentu. Kunci ini berupa sel referensi Fungsi Vlookup digunakan untuk membaca suatu tabel yang disusun secara vertikal, sedangkan Fungsi Hlookup digunakan untuk membaca tabel yang disusun secara horizontal. CARA PENULISAN RUMUS =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) =HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) Dimana: lookup_value: nilai atau sel referensi yang dijadikan kunci dalam pencarian data. table_array: tabel atau range yang menyimpan data yang ingin dicari. Range untuk contoh tabel di atas adalah: A2:C4 (tabel pertama - VLOOKUP) dan B1:D3 (tabel dua - HLOOKUP). col_index_num: nomor kolom yang ingin diambil nilainya untuk fungsi VLOOKUP. Untuk tabel pertama (VLOOKUP): nomor kolom adalah 2, bila ingin mengambil nilai pada kolom Name. Nomor kolom adalah 3, bila ingin mengambil nilai pada kolom Price. row_index_num: nomor baris yang ingin diambil nilainya untuk fungsi HLOOKUP. Untuk tabel dua (HLOOKUP): nomor baris adalah 2, bila ingin mengambil nilai sel pada baris Name. Nomor baris adalah 3, bila ingin mengambil nilai sel pada baris Price. range_lookup: merupakan Nilai Opsional yaitu Nilai logika TRUE atau FALSE, dimana Anda ingin fungsi VLOOKUP atau HLOOKUP mengembalikan nilai dengan metode kira-kira (TRUE) atau mengembalikan nilai secara tepat (FALSE). Contoh Soal Penggunaan Fungsi Vlookup dan Hlookup Internal Sheet Diketahui Data Tabel pegawai sebagai berikut NIP, NAMA, GOLONGAN, DAN GAJI POKOK , Cari gaji pokok sesuai golongan ? Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 46 of 74
Langkah Pengerjaan Untuk Menyelesaikan Soal diatas kita bisa menggunakan dua cara Cara 1 1. Klik sel D3 2. Ketik =vlookup( 3. Klik Sel C3 4. Ketik koma (;) 5. Blok sel A11 sampai B19 6. Tekan Tombol F4 7. Ketik Titik koma (;) 8. Ketik angka 2 9. Ketik ; dan angka 0 10. ) 11. Tekan Enter Cara 2 1. Blok Cell A11 sampai B19 2. Selanjutnya pada Name Box Beri Nama Gapok 3. Kemudian pada Cell D3 Isikan Rumus Seperti Berikut =VLOOKUP(C3;GAPOK;2;0) 4. Hasilnya Lihat Tabel Dibawah ini Pada NameBox Isi Gapok, Sebagai Nama Range yang ditentukan Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 47 of 74
Penjelasan Cara 1 Keterangan Rumus Rumus mengambil nilai Tabel Nilai Kunci Data Pada Tabel golongan =Vlookup Tabel Nilai Gaji Pokok Sesuai Golongan Sel C3 Kolom Nilai Yang Diambil Dari Tabel Nilai A11 : B19 Mengabsolutkan Nilai 2 F4 Penjelasan Cara 2 Keterangan Rumus Rumus mengambil nilai Tabel Nilai Kunci Data Pada Tabel golongan =Vlookup Nama Range Tabel Vlookup Sel C3 Kolom Nilai Yang Diambil Dari Tabel Vlookup Kolom Gapok Ke 2 2 Catatan & Tips Dalam Penggunaan Fungsi Vlookup dan Hlookup, Harus dibuat dua tabel yaitu tabel transaksi, dan tabel refrensi Kesalahan dalam membuat rumus Vlookup yaitu Range tabel tidak dibuat absolut atau mutlak (tidak pakai simbol $) sehingga data bergerak pada saat dicopy. Misalnya A1:E20, seharusnya $A$1:$E$20. Format nilai yang digunakan untuk melookup tidak sama. Misalnya, salah satu nilai memiliki format number sedangkan di tabel menggunakan format text. Penggunaan Fungsi Hlookup Selanjutnya Tabel Refrensi buat dengan menggunakan Fungsi Hlookup caranya sama hanya bentuk tabel nilainya yang dirubah seperti gambar berikut : Langkah pengerjaannya adalah sebagai berikut : Padepokan IT Course Page 48 of 74 Langkah Pengerjaan 1. Klik sel D3 2. Ketik =hlookup( 3. Klik Sel C3 4. Ketik koma (,) 5. Blok sel Tabel Nilai Vlookup 6. Tekan Tombol F4 7. Ketik koma (;) 8. Ketik angka 2 9. Tekan Enter Modul Excel Fundamental Basic - Intermediate
Penggunaan Fungsi Vlookup dengan Nama Range Dalam Penggunaan Rumus Vlookup kita bisa menggunakan nama range melalui Name Box, sehingga penulisan rumus menjadi lebih singkat. Rumus seperti terlihat dibawah ini =VLOOKUP(D4;gaji;2;0) Cara Membuat Rumus Vlookup dengan Nama Range Langkahnya 1. Buka Tabel Master yang akan dijadikan Acuan Misal Tabel Gaji 2. Selanjunya Blok Cell dari A3:B7 kemudian pada NameBox isi dengan gajiseperti terlihat pada gambar dibawah ini 3. Selanjutnya Pada Tabel Transaksi atau dimana rumus akan dibuat ketik rumus seperti berikut 4. hasilnya Modul Excel Fundamental Basic - Intermediate Padepokan IT Course Page 49 of 74
Search
