Laman

Minggu, 23 Juni 2013

Dasar-dasar Microsoft Excel 2003

tabs_excel2003 - computer1001.blogspot.com
    Mengubah Nama Tab Lembar Kerja
    Ada beberapa cara untuk mengubah nama tab lembar kerja, antara lain:
  1. Klik ganda tab lembar kerja yang ingin Anda ubah namanya. Kemudian tuliskan nama baru untuk lembar kerja tersebut dan tekan Enter.
  2. Klik kanan tab lembar kerja pada bagian bawah dan pilih Rename.

    Memberi Warna Tab Lembar Kerja
  1. Klik kanan tab lembar kerja yang ingin Anda beri warna dan pilih Tab Color untuk memunculkan kotak Format Tab Color.
  2. Klik warna yang Anda inginkan dan kemudian klik OK.

    Menambah Lembar Kerja
    Ada beberapa cara untuk menambah lembar kerja, antara lain:
  1. Dari menu Insert klik perintah Worksheet. Excel akan menyisipkan lembar kerja yang baru.
  2. Klik kanan salah satu lembar kerja untuk memunculkan menu Shortcut dan pilih Insert.

    Menghapus Lembar Kerja
    Ada beberapa cara untuk menghapus lembar kerja, antara lain:
  1. Pilih lembar kerja yang ingin dihapus. Dari menu Edit klik perintah Delete Sheet.
  2. Pilih lembar kerja yang ingin dihapus. Klik kanan untuk memunculkan menu Shortcut dan pilih Delete.

    Memindahkan dan Menyalin Lembar Kerja
  1. Pilih lembar kerja yang ingin Anda pindahkan atau salin. Jika Anda ingin memindahkan lembar kerja dari satu buku kerja ke buku kerja lainnya maka jangan lupa untuk membuka buku kerja tujuan.
  2. Klik kanan tab lembar kerja yang ingin Anda salin atau pindahkan dan pilih Move or Copy Sheet untuk menampilkan kotak dialog Move or Copy.
  3. Untuk memindahkan lembar kerja ke sebuah buku kerja berbeda, pilih nama buku kerja tersebut dari anak panah ke bawah daftar To Book.
  4. Jika Anda ingin memindahkan atau menyalin lembar kerja ke sebuah buku baru pilih new book. MS Excel akan membuat buku kerja baru dan menyalin atau memindahkan lembar kerja ke dalam buku kerja tersebut.
  5. Dalam kotak daftar Before Sheet, pilih lembar kerja yang urutannya akan terletak sesudah lembar kerja pindahan.
  6. Untuk menyalin lembar kerja, aktifkan pilihan Create a Copy.
  7. Klik OK. Lembar kerja yang sudah dipilih akan disalin atau dipindahkan sesuai dengan keinginan Anda.
  8. Tip: Anda juga dapat menyalin atau memindahkan lembar kerja dalam sebuah buku kerja dengan menggunakan fasilitas drag dan drop. Tempatkan pointer mouse pada lembar kerja yang sudah dipilih, lalu klik dan geser tab lembar kerja tersebut ke tempat yang diinginkan. Untuk menyalin lembar kerja, tekan tombol Ctrl sambil menggeser. Lepaskan mouse, lembar kerja akan pindah atau disalin.

    Mengganti Jumlah Default Lembar Kerja
  1. Buka menu Tools dan pilih Options.
  2. Pilih tab General dan masukkan jumlah lembar kerja yang diinginkan pada pilihan Sheets in New Workbook. Kemudian klik OK.

    Memasukkan Data
  • Tekan tombol TAB pada keyboard untuk memindahkan insertion point (kursor) satu sel ke sebelah kanan di baris yang sama.
  • Tekan ENTER untuk memindahkan kursor satu sel ke bawah di kolom yang sama.
  • Memasukkan tanggal: gunakan slash (/) atau atau tanda penghubung (-) untuk memisahkan bagian dari tanggal. Misalnya: 7/3/2008. Untuk memasukkan tanggal hari ini, tekan CTRL + ; (titik koma).
  • Memasukkan jam: tekan spasi dan ketik "a" (untuk AM) atau "p" (untuk PM) setelah Anda mengetikkan jam. Misalnya: 9:00 p. Jika tidak, Excel akan memasukkan jam sebagai AM. Untuk memasukkan jam sekarang tekan CTRL + SHIFT + ; (titik koma).
  • Memasukkan angka:
    • Angka yang dimasukkan dengan tanda kurung (100) akan ditampilkan angka minus: -100. Anda dapat mengatur tampilannya dengan menekan CTRL + 1 untuk menampilkan kotak dialog Format Cells dan pilih tab Number. Pada Category pilih Number dan tentukan tampilan angka negatif yang Anda inginkan, klik OK bila sudah selesai.
    • Untuk memasukkan pecahan, beri satu spasi antara angka dengan pecahan. Contoh: 1 1/5.
    • Untuk memasukkan pecahan saja, masukkan angka nol terlebih dahulu. Contoh: 0 1/8.
Rumus / Formula Pada Excel 2007

Pendahuluan

Oleh : Jaten

Untuk menguasai Excel, tentunya tidak mungkin jika kita tidak memahami dan menguasai kumpulan formula atau rumus yang disertakan.

Rumus sendiri adalah suatu gabungan dari beberapa elemen yang akan menghasilkan suatu nilai kembalian. Elemen-elemen tersebut adalah :

  • nilai konstan berupa angka atau teks
  • referensi cell
  • operator 
  • fungsi

Contoh rumus dan hubungan dari tiap elemen tersebut di atas ditunjukkan pada ilustrasi berikut.

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
    • + (tanda plus) : digunakan untuk menjumlahkan dua nilai.
    • - (tanda minus)  : digunakan untuk mengurangi dua nilai, atau digunakan sebagai tanda negatif pada suatu angka konstanta.
    • * (tanda bintang)  : digunakan untuk mengalikan dua nilai.
    • / (tanda garis miring)  : digunakan untuk membagi suatu nilai dengan nilai lainnya.
    • % (tanda persen) : digunakan untuk merepresentasikan nilai persentase.
    • ^ (tanda sisipan) : digunakan sebagai tanda pangkat.

Gambar 1 : Contoh Penggunaan Operator Aritmatika
  • Operator Perbandingan Nilai
    • = (tanda sama dengan)  : menunjukkan apakah nilai yang satu sama dengan nilai lainnya.
    • > (tanda lebih besar)  : menunjukkan apakah nilai yang satu lebih besar dengan nilai lainnya.
    • < (tanda lebih kecil)  : menunjukkan apakah nilai yang satu lebih kecil dengan nilai lainnya.
    • >= (tanda lebih besar sama dengan)  : menunjukkan apakah nilai yang satu lebih besar atau sama dengan nilai lainnya.
    • <= (tanda lebih kecil sama dengan)  : menunjukkan apakah nilai yang satu lebih kecil atau sama dengan nilai lainnya.
    • <> (tanda lebih kecil dan lebih besar) : menunjukkan ketidaksamaan antara dua nilai.

Gambar 2. Contoh Penggunaan  Operator Perbandingan Nilai
  • Operator Penggabungan Teks
    • & (tanda dan / ampersand) : digunakan untuk menggabungkan dua atau lebih teks.
Gambar 3. Contoh Penggunaan Operator Penggabungan Teks
  • Operator Referensi
    • : (tanda titik dua) : operator range (rentang nilai) yang mengambil dua referensi sel sebagai awal dan akhir rentang nilai.
    • , (tanda koma) : operator union yang digunakan untuk menggabungkan dua atau lebih referensi menjadi satu referensi sel.
    •   (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)

Daftar Artikel Contoh Penggunaan Rumus

Berikut adalah beberapa artikel BelajarExcel.info yang bisa membantu pemahaman Anda lebih lanjut mengenai penggunaan rumus di Excel.

  • IF, suatu fungsi kondisional yang menghitung nilai dengan kriteria tertentu
  • INDEX dan MATCH, mengambil nilai dan referensi berdasarkan pencarian
  • INDIRECT, mengambil nilai berdasarkan teks referensi alamat
  • MID, mengambil bagian dari teks (substring)
  • AVERAGE, menghitung nilai rata-rata dari suatu range nilai
  • SUMIF, melakukan penjumlahan dari suatu range dengan kriteria tertentu
  • VLOOKUP, melakukan lookup dari suatu referensi range dengan suatu nilai, dan mengambil kembalian kolom lain dari range tersebut
  • ARRAY FORMULA, penggunaan rumus array untuk tingkat lanjut
  • ADDRESS, mengambil teks alamat berdasarkan posisi kolom dan baris cell

Penggunaan Fungsi IF pada Excel

Pada Excel 2007 dan versi sebelumnya, fungsi IF merupakan fungsi dasar yang digunakan untuk mendapatkan nilai berdasarkan kondisi tertentu.

Fungsi ini sering disebut sebagai fungsi keputusan, dimana kita menetapkan rule / aturan yang akan mengambil keputusan pengembalian nilai yang berbeda dari input yang masuk.

Syntax dasar dari fungsi IF adalah sebagai berikut

IF(logical_test_value, value_if_true, value_if_false)

Contoh Penggunaan

Misalkan kita memiliki contoh data penjualan sederhaan berikut ini. Total Bayar pada kolom “Total Bayar” (G) akan diisi dengan potongan diskon 10% dari “Total” jika pada kolom “Diskon” terdapat nilai “Y“.

Pemecahannya untuk cell G2 adalah sebagai berikut :

=F2*IF(C2=”Y”,0.9,1)

Penjelasan :

  • Nilai G2 (Total Bayar) didapat dari hasil perkalian F2 (Total) dengan 0.9 (diskon 10%) jika nilai C2 (Diskon) adalah “Y“.
  • Jika nilai C2 (Diskon) bukan “Y” maka tidak ada diskon, dan nilai G2 sama dengan nilai F2.

Contoh file Excel ini dapat Anda download disini.

Penggunaan Fungsi Index dan Match pada Excel 2007

Apa itu Fungsi Index dan Match ?

Dalam keseharian pengolahan data dalam Excel hampir dipastikan Anda akan terlibat dalam dua kondisi berikut :

  • Melakukan pencarian dari suatu nilai terhadap range data tertentu (referensi).
  • Pencarian terhadap referensi tersebut harus cukup dinamis, ini dalam arti dapat mencari dan mengambil data dari kolom ataupun baris manapun yang kita tentukan.

Untuk keperluan hal tersebut, kita dapat melakukannya dengan mudah dari penggunaan dua fungsi Excel, yaitu INDEX dan MATCH.

Fungsi INDEX

Fungsi INDEX adalah fungsi yang cukup sederhana, digunakan untuk mendapatkan nilai dari suatu cell berdasarkan pencarian pada suatu definisi table / data range worksheet kita.

Pencarian digunakan berdasarkan informasi posisi kolom dan baris, dengan acuan berupa kolom dan baris pertama table / data range tersebut.

Syntax fungsi INDEX adalah sebagai berikut :

INDEX(array, row_num, [column_num])

Keterangan :

  • array : adalah table / range data yang terdiri dari satu atau beberapa kolom dan baris.
  • row_num : adalah angka yang menunjukkan posisi baris dengan acuan dari cell pertama ( kolom / baris ujung kiri atas ) dari array.
  • column_num :  adalah angka yang menunjukkan posisi kolom dengan acuan dari kolom / baris pertama dari array. Argumen ini bersifat opsional (boleh digunakan atau tidak).

Penjelasan mengenai fungsi INDEX ini dapat diilustrasikan pada Gambar 1 di bawah ini.

Gambar 1. Ilustrasi Penggunaan Fungsi Index

Dengan formula “=INDEX(B3:B8, 4, 2)“, kita mengambil jarak data dari posisi acuan B3 sebesar 2 kolom ke kanan dan 4 baris ke bawah. Kenapa kita mengambil B3 sebagai acuan ? Karena B3 adalah  cell pertama dari array / data range B3:D4.

Hasil dari “navigasi cell” pada fungsi ini adalah nilai dari cell C6, yaitu angka 20.

Gambar berikutnya (gambar 2) menunjukkan hasil penggunaan fungsi INDEX pada worksheet Excel.

Gambar 2. Contoh Penggunaan Fungsi Index (klik untuk memperbesar)

Fungsi MATCH

Fungsi MATCH adalah fungsi yang digunakan untuk mencari suatu nilai dari suatu range yang terdapat pada suatu kolom atau baris, tapi tidak kedua-duanya.

Syntax fungsi MATCH adalah sebagai berikut :

MATCH(lookup_value, lookup_array, [match_type])

Keterangan :

  • lookup_value : adalah nilai yang ingin dicari pada lookup_array.
  • lookup_array : adalah range data dari suatu kolom ataupun baris.
  • match_type :  adalah angka yang menunjukkan tipe pencocokan sebagai berikut :
    • 1 : jenis pencocokan dimana lookup_array dalam keadaan terurut secara ascending (kecil ke besar). Pencocokan dilakukan dengan mengambil nilai terbesar dari range data yang lebih kecil atau sama dari lookup_value.
    • 0 :  jenis pencocokan dimana pada lookup_array dicari data yang sama persis dengan lookup_value. Urutan data tidak menjadi masalah. Jika diketemukan lebih dari satu data yang sama, maka akan diambil data yang pertama kali diketemukan secara sekuensial.
    • -1 :  jenis pencocokan dimana lookup_array dalam keadaan terurut secara descending (besar ke kecil).  Pencocokan dilakukan dengan mengambil nilai terkecil dari range data yang lebih besar atau sama dari lookup_value.

Untuk kejelasan apa yang dimaksud dengan  match_type ini, perhatikan ilustrasi pada Gambar 3 di bawah ini.

Pada contoh tersebut nilai lookup adalah 3, yang kemudian dicari pada data array dengan tiga kelompok susunan data seperti tampak pada gambar.

Gambar 3. Ilustrasi Contoh Penggunaan Match Type – Skema Pertama

Terlihat dengan pilhan match_type mulai dengan nilai  -1, 0 dan 1 didapatkan hasil posisi yang berbeda dari fungsi match, masing-masing yaitu 5, 2, dan 4.

Contoh lainnya terlihat pada Gambar 4 di bawah ini. Pada kasus ini nilai lookupnya adalah 4 yang dicari pada data array dengan nilai 1, 2, 3, 3, 5, 5 dan 6 (sama dengan contoh sebelumnya). Dengan pilhan tiap  tipe mulai dari -1, 0 dan 1 didapatkan posisi dari fungsi match masing-masing adalah 3, NA (Not Available / Tidak Ditemukan), dan 4.

Gambar 4. Ilustrasi Contoh Penggunaan Match Type – Skema Kedua

Screenshot berikut menunjukkan beberapa contoh lebih lanjut penggunaan match pada Excel 2007 (klik pada gambar untuk memperbesar).

Gambar 5. Contoh Penggunaan Match pada Excel (1)

Gambar 6. Contoh Penggunaan Match pada Excel 2007 (2)

Gambar 7. Contoh Penggunaan Match pada Excel 2007 (3)

Penggunaan dari Gabungan Fungsi INDEX dan MATCH

Seperti dijelaskan sebelumnya, penggabungan fungsi INDEX dan MATCH akan menghasilkan solusi pencarian data yang cukup powerful dimana kita dapat mencari dari referensi berdasarkan kolom / baris yang kita inginkan.

Syntax dari penggabungan fungsi ini tampak seperti berikut :

INDEX(array, MATCH(lookup_value, lookup_array, [match_type]), column_num)

jika yang dicari adalah data range baris pada suatu kolom, atau…

INDEX(array, row_num, MATCH(lookup_value, lookup_array, [match_type]))

jika yang dicari adalah data range kolom pada suatu baris.

Sekilas solusi ini mirip dengan fungsi VLOOKUP yang telah kita bahas sebelumnya. Namun dengan fungsi VLOOKUP kita terbatas pada pencarian pada kolom pertama pada data range referensi dan harus terurut, sedangkan dengan penggabungan fungsi ini kita bisa mencari dari kolom manapun dan tidak perlu dalam keadaan terurut (sesuai match_type tentunya).

Berikut adalah dua gambar contoh penggunaan dari gabungan kedua fungsi INDEX dan MATCH.

Gambar 8. Contoh Penggunaan Index dan Match (1)

Gambar 9. Contoh Penggunaan Index dan Match (2)

Kesimpulan

Fungsi MATCH dan INDEX masing-masing merupakan fungsi untuk melakukan pencarian dan navigasi dari suatu table / data range. Bedanya fungsi MATCH mengembalikan nilai posisi sedangkan fungsi INDEX mengembalikan nilai dari suatu posisi cell.

Penggabungan kedua fungsi tersebut menjadi solusi yang sangat baik sebagai alternatif dari fungsi VLOOKUP yang telah dikenali sebagai fungsi untuk mencari / lookup suatu nilai referensi.

Update !

Contoh file Excel yang dibahas disini dapat didownload di URL berikut : http://goo.gl/MzkpW

Indirect : Mengambil Nilai berdasarkan Teks Alamat

Pendahuluan

Indirect adalah fungsi pada Excel yang digunakan untuk mengambil nilai dari sel dengan menggunakan teks dari alamat dari suatu sel.

Syntax dari indirect adalah sebagai berikut :

INDIRECT(alamat_cell)

Contoh format alamat_cell adalah sebagai berikut :

  • “A2″
  • “H122″
  • dan lain-lain

Contoh Penggunaan

Berikut adalah screenshot contoh penggunaan indirect. Dokumen Excel tersebut dapat juga Anda download pada bagian akhir dari artikel ini.

Contoh Penggunaan Indirect

Artikel Terkait

Sumber Referensi

Mengambil Bagian dari Teks (Substring)

Fungsi SUBSTRING terdapat di beberapa bahasa pemrograman yang digunakan untuk mengambil sebagian dari string / teks. Bagaimana dengan di Excel ?

Untuk ini kita menggunakan fungsi MID, syntaxnya sebagai berikut :

MID(text, start_num, num_of_chars)

dimana :

  • text : adalah teks / string yang akan diolah
  • start_num : posisi indeks / karakter awal
  • num_of_chars : jumlah karakter yang akan diambil

Contoh Penggunaan dan Hasil Output :

  • MID(“belajarexcel.info”, 1, 7) => belajar

Menghitung Rata-rata dengan Average

Pendahuluan

Function AVERAGE adalah fungsi yang bisa kita gunakan di Excel untuk menghitung rata-rata dari suatu range nilai.

Syntax dari AVERAGE adalah sebagai berikut :

AVERAGE(range1, [range2], …)

Keterangan

  • range1 : adalah nilai tunggal atau range dari cell-cell yang ingin kita hitung nilai rata-rata.
  • range2 : adalah nilai tunggal atau range kedua dari cell-cell yang ingin kita hitung nilai rata-rata (opsional).

Objektif

Berikut adalah gambaran contoh perhitungan yang ingin kita lakukan.

Contoh Penggunaan Average
(klik pada gambar untuk memperbesar)

Langkah Penggunaan AVERAGE

  • Buatlah satu file workbook baru pada Excel 2007.
  • Pada sheet1, masukkan data dengan layout seperti berikut.
  • Data tersebut adalah data fiktif penjualan 4 kuartal dari suatu minimart. Kita akan menghitung rata-rata penjualan per kuartal. Contoh file dapat Anda download disini.
  • Tempatkan cursor pada alamat B9. Masukkan rumus berikut dan tekan Enter :=AVERAGE(C5:C8)

     

  • Hasil perhitungan nilai rata-rata adalah 11,050 seperti terlihat pada gambar berikut.
  • Selesai.

Contoh Penggunaan Fungsi SumIF

Pendahuluan

SUMIF adalah fungsi penjumlahan nilai-nilai yang terdapat pada suatu range data, namun nilai yang diambil adalah berdasarkan pada satu pengkondisian atau filter yang ditentukan oleh kita.

Syntax dari fungsi SUMIF adalah sebagai berikut :

SUMIF(range, criteria, [sum_range])

Keterangan

  • range : adalah range dari cell-cell yang ingin kita evaluasi berdasarkan kriteria yang akan kita tentukan.
  • criteria : kondisi atau aturan filter yang ingin kita gunakan.
  • sum_range : jika ini disebutkan maka range ini yang akan jadi acuan untuk mengambil nilai, tetapi posisinya sesuai dengan kolom – baris dari range yang terfilter.

Contoh Penggunaan

  1. Sebagai contoh, kita memiliki data range seperti terlihat di bawah ini  (A2:D10). Yang ingin kita lakukan adalah mengambil total dari Kacang saja kemudian ditempatkan hasilnya pada D11.
  2. Tempatkan cursor pada cell D11 dan ketikkan formula berikut :=SUMIF(D3:D10,”=Kacang”,C3:C10)

    ini artinya dari range kriteria dari D3:D10, kita mengambil yang nilainya Kacang saja. Setelah itu ambil range nilai yang akan dijumlah yaitu C1:C10.

  3. Hasilnya akan terlihat sebagai berikut. Nilai 15 didapatkan sebagai total jumlah untuk Kacang saja.
  4. Selesai

Menggunakan Fungsi VLookup Pada Excel 2007

Apa itu VLOOKUP ?

VLOOKUP adalah salah satu fungsi pada Microsoft Excel yang sangat populer digunakan sejak versi awal sampai sekarang.

Fungsi lookup value pada Excel ada dua, yaitu :

  • VLOOKUP (Vertical Lookup).
  • HLOOKUP (Horizontal Lookup).

Sesuai namanya, VLOOKUP digunakan untuk mencari data pada baris demi baris (vertikal) pada tabel referensi. Sebaliknya, HLOOKUP digunakan untuk mencari data dengan referensi data kolom demi kolom (horisontal).

Untuk memperjelas cara kerja dari VLOOKUP, perhatikan gambar ilustrasi berikut ini.

Ilustrasi Proses VLOOKUP (Vertical Lookup)

Ilustrasi tersebut menggambarkan mekanisme pencarian VLOOKUP sebagai berikut :

  1. suatu cell data dengan nilai “kode 3”  (lookup value) akan digunakan sebagai kunci pencarian ke table referensi (table_array) .
  2. data pada kolom pertama (kode 1, kode 2, kode 3, dan kode 4) pada table_array berfungsi sebagai kunci referensi.
  3. dari proses ini, kita akan ketemu data yang dicari pada posisi di baris ke 3 table referensi.
  4. tetap pada baris ke 3 tersebut, kita akan mengambil nilai dari kolom yang kita inginkan (col_index_num). Misalkan kita mengambil kolom ke 2, maka kita akan mendapatkan nilai 4 sebagai datanya.
  5. selesai.

Syntax VLookup

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Keterangan :

  • lookup_value  : nilai yang akan dicari ke table_array
  • table_array   : range nilai dimana terdapat nilai yang dicari
  • col_index_num : indeks kolom yang dicari
  • range_lookup  : berisi nilai 0 atau 1. Nilai 1 artinya jika tidak ada nilai yang cocok, maka akan dicari pendekatannya.

Contoh Penggunaan

Kita akan mencoba menggunakan vlookup untuk mencari referensi nama produk dari kode produk dalam suatu daftar transaksi.

  • Download file contoh dari link berikut :
    http://contoh-spreadsheet.googlecode.com/files/data_vlookup.xlsx
  • Jalankan aplikasi MS Excel 2007 dan buka file data_vlookup.xlsx tersebut
  • File ini berisi dua sheet :
    • transaksi_penjualan : berisi contoh data transaksi penjualan
    • master_produk : berisi data lengkap referensi produk
  • Buka sheet transaksi_penjualan. Pada sheet ini tidak ada informasi nama produk, yang ada adalah data kode produk. Dan melalui nilai kode produk ini kita akan cari data nama produk yang terdapat pada sheet master_produk.
  • Sisipkan 1 kolom kosong diantara kolom E (kode_produk) dan F (jumlah_pembelian) sehingga F bergeser ke G. Pada kolom F1 isikan nilai nama produk.
Insert kolom “nama produk”
(klik pada gambar untuk memperbesar)
  • Cell-cell yang berada di bawah kolom F (nama produk) ini akan dicari dari sheet master_produk berdasarkan nilai cell pada kolom E (kode_produk) dengan penggunaan fungsi VLookup.Sebelumnya pastikan bahwa table data pada sheet master_produk sudah terurut pada kolom B (kode_produk).
  • Pada kolom F2 masukkan fungsi vlookup sebagai berikut :=VLOOKUP(E2,master_produk!$B$2:$F$42,3)

    dimana E2 menunjukkan nilai dari kode produk, yang akan dicari ke tabel data dengan range B2 s/d F42 di sheet master_produk.

  • Dari range $B$2:$F$42 tersebut pastikan kolom pertama adalah kolom B (kode produk) sebagai referensi. Setelah itu kita ambil kolom ketiga dari range yang merupakan nama produk.
  • Anda seharusnya mendapatkan nama produk “salak 1 kg” pada cell F2.
  • Copy cell F2 ke seluruh baris di bawah. Anda seharusnya mendapatkan hasil sebagai berikut di bawah ini.
  • Selesai

Apa itu Array Formula pada Excel ?

Pendahuluan

Array Formula adalah formula yang melibatkan formula untuk data range atau multi cell yang pada kondisi normal hanya untuk cell-cell tunggal.

Hampir setiap operasi dasar dapat digunakan oleh array formula ini, namun notasi dan entrinya agak sedikit berbeda antara satu formula dengan formula lainnya.

Pada beberapa literatur, array formula ini sering disebut dengan CSE Formula (Control+ Shift + Enter Formula)  karena mengharuskan penggunaan kombinasi tiga tombol keyboard tersebut.

Jika Anda berasal dari dunia programming, maka konsep dan operasi penggunaan array sudah tidak asing lagi. Bahkan cenderung intensif digunakan pada beberapa bahasa pemrograman.

Namun operasi Array Formula untuk Excel agak sedikit berbeda sehingga Anda perlu tahu konsep di balik formula ini dengan lebih baik.

Pada kesempatan kali ini akan ditunjukkan ilustrasi konsep dan contoh penggunaan array formula sehingga Anda mendapatkan gambaran lengkap dan komprehensif akan manfaatnya dalam pekerjaan sehari-hari.

Untuk memulai, marilah kita lihat satu contoh penggunaan berikut ini.

Contoh Awal

  1. Jalankan Excel dan buat satu workbook baru.
  2. Isilah worksheet dengan data seperti terlihat pada gambar berikut.
  3. Dengan data tersebut, kita akan mengisi cell-cell pada kolom SubTotal – berdasarkan perkalian dari kolom Jumlah dan Harga.
  4. Buatlah selection pada range alamat E2:E5 yang berada di bawah kolom SubTotal.
  5. Dengan posisi tersebut di atas, ketik formula =C2:C5*D2:D5, jangan tekan tombol apapun setelah Anda melengkapi formula tersebut.
  6. Sekarang eksekusi formula tersebut sebagai Array Formula dengan menekan tombol keyboard CONTROL + SHIFT + ENTER (CSE).Posisi tombol -tombol tersebut ditunjukkan pada highlight warna kuning dari gambar layout keyboard berikut ini.
  7. Hasil eksekusi tombol CSE tersebut akan terlihat seperti gambar berikut.Perhatikan bahwa formula yang kita masukkan tadi telah diapit oleh pasangan kurung kurawal { … }. Ini menandakan bahwa range cell tersebut telah dianggap sebagai Array Formula.
  8. Arahkan cursor ke setiap cell dari E2:E5, kita akan mendapatkan formula yang sama.
  9. Sekarang cobalah pilih salah satu cell dari E2:E5 dan coba hapus dengan menekan tombol Delete. Akan terjadi error dengan pesan seperti berikut – “You cannot change part of an array“.Ini artinya range dari Array Formula adalah satu kesatuan dan tidak dapat dihapus secara individual.
  10. Selesai.

Array pada Excel

Pengertian array pada Excel sangat sederhana, yaitu kumpulan dari beberapa cell yang tersebar pada suatu kolom, suatu baris atau pada beberapa baris dan kolom (multi columns and rows). Ilustrasinya dapat Anda lihat pada gambar berikut di bawah ini.

Single-Cell dan Multi-Cell

Pada contoh penggunaan array formula di atas, terlihat bahwa hasil atau output eksekusi formula berupa array atau terdiri dari beberapa cell - sebagai satu kesatuan. Untuk hasil demikian, kita namakan formula tersebut Multi-Cell Formula.

Namun array formula tidak harus selalu menghasilkan multi-cell tetapi juga dapat menghasilkan satu cell saja, dan dengan demikian disebut Single-Cell Formula.

Kedua jenis output tersebut tetap memiliki karakteristik yang sama, yaitu pada bagian formulanya terdapat array yang dijadikan sebagai parameter atau input bagi operator ataupun fungsi yang mengolahnya.

Contoh Lanjut : Single Cell

  1. Melanjutkan contoh sebelumnya, tempatkan cursor pada cell E6.
  2. Masukkan formula =SUM(C2:C5*D2:D5) pada cell tersebut dan tekan tombol Control + Shift + Enter.
  3. Hasilnya tampak seperti pada gambar berikut. Terlihat bahwa formula diapit oleh kurung kurawal sebagai tanda itu adalah array formula. Perhatikan bahwa formula ini langsung menghitung berdasarkan hasil perkalian array C2:C5 dan D2:D5.
  4. Selesai.

Contoh Lanjut : Conditional Average

  1. Kembali kita lanjutkan contoh sebelumnya, lakukan perubahan pada worksheet sebelumnya sehingga tampak seperti pada gambar berikut.
(klik untuk memperbesar)
  1. Pada worksheet ini kita akan menghitung rata-rata nilai subtotal untuk produk non telur.
  2. Pada cell F7 masukkan formula berikut dan tekan tombol Control + Shift + Enter : =AVERAGE(IF(C2:C5=”Tidak”,D2:D5*E2:E5)).Formula ini akan menyaring kategori yang bukan telur (nilai “Tidak” pada kolom C), dan kemudian menghitung rata-rata dari sub total yang dihitung dari perkalian Jumlah dan Harga (kolom D dan E).
  3. Hasilnya akan terlihat seperti pada gambar berikut.
  4. Selesai.

Kesimpulan

Array pada Excel adalah rangkaian cell dari 1 kolom, 1 baris, maupun beberapa baris dan kolom. Array formula adalah formula yang beroperasi pada array tersebut, dan menghasilkan output 1 cell (Single-Cell) maupun beberapa cell (Multi-Cell).

Dari contoh yang diberikan, beberapa keuntungan dari penggunaan array formula adalah sebagai berikut :

  1. Konsistensi : Setiap cell dari hasil Multi-Cell memiliki formula yang sama.
  2. Keamanan : Setiap cell dari hasil Multi-Cell tidak dapat dihapus atau diedit sebagian. Dengan demikian kita dapat melakukan proteksi terhadap formula yang diberikan.
  3. Efisiensi ukuran file : Dari kedua contoh terakhir, kita dapat melihat bahwa untuk menghitung nilai total maupun rata-rata sebenarnya tidak diperlukan field SubTotal (intermediate field).Dengan demikian tidak ada penyimpanan tambahan yang diperlukan, dan akan membuat ukuran file menjadi lebih kecil.

ADDRESS: Mengambil Teks Alamat Cell berdasarkan Kolom dan Baris

Fungsi ADDRESS

Address adalah function Excel yang digunakan untuk mengambil representasi teks dari referensi alamat cell berdasarkan posisi kolom dan baris. Tipe data dari nilai kolom dan baris adalah angka / integer.

Syntax dari fungsi Address adalah sebagai berikut :

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

dimana :

  • row_num : adalah posisi baris (harus diisi)
  • column_num : adalah posisi kolom (harus diisi)
  • abs_num : adalah isian yang menentukan tipe pengembalian alamat – nilai-nilainya adalah sebagai berikut :
    • 1 : alamat dalam format absolut (sama dengan jika nilai abs_num tidak diisi)
    • 2 : alamat dalam format absolut untuk baris / row, sedangkan kolom / column tetap relatif
    • 3 : alamat dalam format absolut untuk kolom / column, sedangkan baris / row tetap relatif
    • 4 : alamat dalam format relatif
  • a1 : jika TRUE maka akan mengembalikan format kolom berdasarkan alfabet (A, B, C… AA, …) dan baris berdasarkan angka (1,2,3,…). Sedangkan jika FALSE maka akan mengembalikan berdasarkan angka namun dengan prefix R untuk baris dan prefix C untuk kolom.
  • sheet_text : penambahan teks di depan alamat

Contoh Penggunaan

Berikut adalah screenshot contoh penggunaan dari fungsi Address. Dokumen Excel contoh tersebut dapat didownload pada bagian akhir artikel ini.

Contoh Formula menggunakan Fungsi Address

stilah-istilah dalam Excel
1. Cell : merupakan bagian terkecil dari worksheet yng dapat diisi dengan jumlah karakter (max. 255 karakter) isi cell dapat berupa value, formula atau text. Contoh : cell A3, cell D5

2. Worksheet (lembar Kerja) : merupakan kumpulan dari 256 kolom dan 65536 baris.

3. Workbook (buku kerja) : merupakan kumpulan dari 256 worksheet (berlabel sheet1 sampai sheet 256)

4. Range : merupakan sekelompok cell yang akan mendapataksi sama sesuai perintah yang anda jalankan. Pemberian alamat/ address dilakukan mulai dari cell sudut kiri atas sampai cell sudut kanan bawah. Contoh : A46 → range mulai dari cell A4 sampai cell D6

5. Alamat Relatif : merupakan alamat yang jika dituliskan kedalam bentuk rumus atau fungsi akan berubah jika dicopy ke cell lain.
Contoh : cell berisi formula A5*6 ,B3 dicopy ke C5 formula pada C5 berubah menjadi B8*6

6. Alamat Semi Absolut : merupakan alamat yang dituliskan dengan tanda $ didepan baris atau kolomsehingga nilai tidak akan berubah.
Contoh : Cell B1 berisi formula $A1*7,B1 dicopy kan ke D5 formula pada D5 menjadi $A5*7

7. Alamat Absolut : merupakan alamat yang dituliskan dengan tanda $ didepan baris dan kolom.tekan tombol F4 untuk menghasilkan alamat absolut pada formula bar. Contoh : cell B1 berisi formula $A$1&5,B1 dicopy kan ke C3 formula pada C3 menjadi $A$1*5

8. Name Box : menunjukkan pada cell/ range yang aktif saat itu. Anda dapat juga membuat nama range melalui kotak nama disebelah kiri formula bar.
Contoh : Holla nama lain range A5:G7

Menulis Rumus
1.Operasi Logika
Dibawah ini terdapat operasi logika anda dapat menggunakan operasi resali atau perbandingan dengan lambang yang digunakan pada Excel dan fungsinya sebagai berikut :
a. = : sama dengan
b. > : lebih besar dari
c. < : lebih kecil dari d. >= : lebih besar atau sama dengan
e. <= : lebih kecil atau sama dengan f. <> : tidak sama dengan

2. Menggunakan Fungsi
Fungsi sebenarnya adalah rumus yang sudah ada disediakan oleh Excel 2003, yang akan membantu dalam proses perhitungan. Kita tinggal memanfaatkan sesuai dengan kebutuhan. Umumnya penulisan Fungsi harus dilengkapi dengan argumen, baik erupa angka, label, rumus, alamat sel atau range. Argumen ini harus ditulis dengan diapit tanda kurung ().
A.Fungsi Logika
Fungsi logika adalah fungsi yang digunakan untuk menyelesaikan perhitungan-perhitungan yang berhubungan dengan pengambilan keputusan fungsi logika yang digunakan adalah :
1. Fungsi If :
Bentuk penulisannya : =If(kondisi,nilai jika benar,nilai jika salah)
Contoh : =If(A5<17,”anak-anak”,”dewasa”>30,”panas”,if(A5>0,”hangat”,”dingin”)
2. Fungsi String
Fungsi string berfungsi untuk mengubah isi text numeric menjadi bilangan
1. Fungsi VALUE :
digunakan untuk merubah nilai value menjadi nilai text,
penulisannya : =VALUE(text)
2. Fungsi FIND :
digunakan untuk menghasilkan posisi substring dari sebuah string atau suatu nomor yang dicari,penulisannya : =FIND(cari text,pada text,mulai nomor)
3. Fungsi MID
digunakan untu mengambil karakter tertentu dari sederet karakter, penulisannya : =MID(text,posisi awal,jumlah karakter)
4. Fungsi LEFT atau RIGHT :
digunakan untuk mengambil substring sebelah kiri atau kanan string, penulisannya =LEFT atau =RIGHT(text,jumlah karakter)
5. Fungsi REPLACE :
digunakan untuk menggantikan substring dengan substring lain dalam sebuah string (sederetan karakter atau karakter),
penulisannya : =REPLACE(text lama,nomor awal,jumlah karakter,text baru)
6. Fungsi CONCATENATE :
digunakan untuk menggabungkan string menjadi satu kalimat maksimal 30 string,
penulisannya : =CONCATENATE(text1,text2,…
B. Fungsi Tabel :
Fungsi HLOOKUP dan VLOOKUP digunakan untuk membaca tabel secara vertikal (VLOOKUP) atau secara horizontal (HLOOKUP),
penulisanya : =HLOOKUP(lookup_value,table_array,row_index_num,…
=VLOOKUP(lookup_value,table_array,row_index_num,…

Fungsi yang sering digunakan
1. fungsi Sum :
Digunakan untuk menjumlahkan sekumpulan data pada satu range, penulisannya : =SUM(number1,number2,..)

2. Fungsi Average :
Digunakan untuk mencari nilai rata-rata,
penulisannya : =average(number1,number2,…
3. Fungsi Max :
Digunakan untuk mencari nilai tertinggi dari sekumpulan data, penulisannya : =max(number1,number2,…
4. Fungsi Min:
Digunakan untuk mencari nilai terendah dari sekumpulan data, penulisannya : =max(number1,number2,…
5. fungsi Count :
Digunakan untuk menghitung jumlah data dari range yang kita pilih
6. Fungsi Stedev :
Digunakan untuk menentukan standart devisiasi dari suatu range, penulisannya : =stedev(number1,number2,…
7. Fungsi Var :
Digunakan untuk menentukan nilai varience dari suatu range, penulisannya : =var(number1,number2,…

sumber:

http://aguskurnia.blogdetik.com/2009/06/10/materi-microsoft-excel-2003/

Rumus Microsoft Excel Paling Umum Digunakan – Excel merupakan aplikasi yang paling umum digunakan untuk membuat laporan keuangan, rincian anggaran sampai hal-hal yang rumit seperti analisa kelayakan usaha dan sebagainya. Dalam penggunakan microsoft excel ini tentu tidak terlepas dari penggunaan rumus excel untuk memudahkan kita dalam menghitung angka-angka secara cepat dan Akurat. Kali ini Autada hanya akan memaparkan sedikit tentang rumus excel yang paling umum digunakan baik oleh mahasisawa, karyawan perusahaan atau bahkan pemilik perusahaan (wiraswasta). Rumus Excel dapat berfungsi jika terlebih dahulu diawali dengan tanda “=” (sama dengan), misalnya jika kita ingin menjumlahkan 5 + 5 maka didalam cell excel tersebut kita ketikkan “=5+5″ lalu tekan Enter maka otomatis cell tersebut menunjukkan hasil penjumlahan tersebut yaitu “10″ (Sepuluh). Begitu juga untuk rumus excel yang lainnya seperti perkalian dengan tanda “*” (bintang) Pengurangan dengan tanda “-” (minus), pembagian dengan tanda “/” (garis miring), penjumlahan (sum), pengecualian (if) dan lain sebagainya harus diawali dengan tanda “=” (sama dengan). Penjumlahan Excel Gambar 1. Penjumlahan dengan + Namun untuk rumus excel yang menggunakan huruf seperti sum, if, sumif, average, lookup dsb selain harus diawali dengan tanda “=” juga ditambahkan tanda buka kurung “(” setelah kata rumus excel, dan tutup kurung “)” untuk mengakhiri sumber data. Misalnya =SUM(A1:A5) Penjumlahan Excel rumus SUM Gambar 2. Penjumlahan keseluruhan dari awal sampai akhir Baiklah untuk mempersingkat waktu mari kita simak Rumus Microsoft Exel yang Paling Umum Digunakan: 1. Rumus Excel SUM (Penjumlahan) Penjumlahan dengan menggunakan excel selain bisa kita gunakan dengan + (plus) bisa juga kita gunakan rumus excel sum untuk menjumlahkan seluruh data dengan cepat. Dengan menggunakan sum maka Anda bisa menjumlahkan data dengan cepat mulai dari satu data hingga ribuan data, cukup masukkan urutan pertama dan terakhir saja misalnya spt gambar diatas, data mulai A1 sampai A5 maka yang dimasukkan hanya A1:A5 yang dipisahkan dengan tanda “:” (titik dua), namun jika Anda ingin memilih dengan hanya beberapa cell saja yang ingin dijumlahkan maka pisahkan dengan tanda “;” (titik koma), seperti gambar dibawah ini. Penjumlahan Excel rumus SUM Gambar 3. Penjumlahan dengan sum namun terlipilih TIPS: Cara mudah memilih cell yang ingin dijumlahkan adalah dengan tetap menekan Ctrl pada keyboard sambil meng-klik cell yang ingin dipilih, jika sudah selesai memilih cell kemudian lepas Ctrl pada keywoard. 2. Rumus Excel IF (Pengecualian) Rumus excel IF dipakai untuk menampilkan beberapa kemungkinan nilai atau hasil sesuai dengan kondisi tertentu. Bentuk Umum dari fungsi IF adalah: =IF(logical_test;[value if true];[value if false]) Adapun Cara menggunakan perhitungan dengan menggunakan rumus IF adalah: Tentukan kategori/ batasan untuk suatu kondisi dengan beberapa alternative kondisi. Contoh jika mahasiswa mempunyai nilai lebih besar dari 90 mendapat nilai A, jika nilai lebih besar dari 80 mendapat nilai B, jika nilai lebih besar dari 70 maka mendapat nilai C begitu seterusnya. penggunaan rumus excel IF Gambar 4. penggunaan rumus excel IF Dengan melihat gambar 4 diatas maka ketikkan di cell C2 seperti contoh dibawah ini: =IF(B2>=90;”A”;IF(B2>=80;”B”;IF(B2>=70;”C”;”"))) Rumus excel IF juga umum digunakan untuk menghilangkan pesan error pada cell yang kosong atau belum ada data sumbernya, misalnya jika kita membuat rumus =A1/B1 pada cell C1 sementara pada cell A1 atau B1 belum ada datanya maka biasanya cell C1 akan terlihat error seperti #DIV/0!, #N/A , #NAME dan lainnya. rumus excel error Gambar 5. rumus excel error Jadi untuk menghilangkan pesan error pada cell C1 tersebut maka kombinasikan rumus IF dengan rumus inti yang dimaksud diatas, jadi rumusnya menjadi: =IF(OR(A1>0;B>0);A1/B1;”") Keterangan: Rumus excel IF dapat juga digabungkan dengan beberapa rumus logika lainnya seperti OR, AND, NOT, dan sebagainya. contoh diatas kita menggunakan rumus OR. karena rumus IF merupakan rumus logika maka jika kita logikakan dengan kalimat lebih kurang begini: “jika A1 atau B1 lebih besar dari 0; maka berlaku rumus A1/B1; atau cell C1 tetap kosong tanda petik merupakan pembatas text pada rumus excel, jika ada dua tanda petik (“”) maka itu berarti teks didalamnya kosong, jika hendak membuat kalimat bergabung dengan rumus excel maka gunakan tanda petik dua (“”). misalnya menggunakan rumus diatas kita masukkan kata “kosong” maka =IF(OR(A1>0;B>0);A1/B1;”kosong” Tambahan rumus excel OR berguna untuk memilih dua atau lebih cell yang kita gunakan jadi jika cell A1 atau B1 ada yang kosong maka rumus inti tidak akan berfungsi Cara menggunakan rumus excel IF dalam keseharian Tujuan kita mempelajari rumus excel untuk memudahkan kita dalam kegiatan kita sehari-hari sehingga perlu kita terapkan rumus excel ini langsung untuk kebutuhan keseharian kita, seperti Laporan belanja harian, laporan keuangan usaha, rencana anggaran, maupun proposal keuangan, berikut kami berikan contoh rincian anggaran harian: rumus excel IF dalam keseharian Gambar 6. rumus excel IF dalam keseharian Contoh dengan melihat gambar diatas maka rumus yang digunakan pada cell D4 adalah: =IF(OR(B4>0; C4>0);(D2+SUM(B4:B4))-SUM(C4:C4);”") Sebenarnya ada beberapa rumus excel yang paling umum digunakan yang belum sempat kami jelaskan disini seperti SUMIF, COUNTIF, LOOKUP, dan lainnya. Namun dengan menguasai beberapa rumus dan logika diatas sudah cukup untuk diaplikasikan dalam kehidupan sehari-hari.

1 komentar:

Gemini mengatakan...

tutorial yang bagus gan, cek video serupa disini ya. cara menghilangkan hasil penjumlahan yang error
https://www.youtube.com/watch?v=K9MiiXSiBLs