Belajar Microsoft Excel

Menjumlahkan Angka Dengan Kriteria Warna

2013-12-07T05:58:00.001+07:00

Seperti halnya pada pembahasan sebelumnya yaitu cara Menghitung Jumlah Sel Dengan Kriteria Warna, maka kali ini kita akan belajar cara Menjumlahkan Angka Dengan Kriteria Warna. Pada rumus dasar Excel terdapat fungsi penjumlahan diantaranya SUM yang berfungsi untuk menjumlahkan angka pada beberapa sel, SUMIF yang berfungsi untuk menjumlahkan angka pada beberapa sel dengan satu kriteria tertentu, SUMIFS yang berfungsi untuk menjumlahkan angka pada beberapa sel dengan kriteria lebih dari satu.

Bagaimana kalau kita hanya ingin menjumlahkan angka dalam suatu range data tetapi hanya warna tertentu yang terjumlahkan?

Lihat contoh tabel berikut:

Pada tabel di atas yang terhitung hanya angka dalam sel dengan background warna biru, karena kriteria yang dipakai adalah warna biru. Pada range C3:C12 ada 4 sel dengan background warna biru, jadi yang terhitung adalah 7 + 5 + 8 + 6 = 26.

Untuk melakukan perhitungan tersebut dibutuhkan kode VBA yang nantinya digunakan sebagai function.

Cara menambahkan kode VBA:

Aktifkan jendela Visual Basic Editor (tekan Alt+F11)
Klik menu Insert > Module
Masukkan kode berikut

Function HitungWarna(Data As Range, Warna As Range) As Double
Dim Wrn As Range, Jml As Double, ColorIndex As Integer

ColorIndex = Warna.Cells(1, 1).Interior.ColorIndex
Jml = 0
On Error Resume Next
For Each Wrn In Data.Cells
If Wrn.Interior.ColorIndex = ColorIndex Then
Jml = Jml + Wrn.Value
End If
Next Wrn
On Error GoTo 0
Set Wrn = Nothing
HitungWarna = Jml
End Function

Cara Menggunakan Fungsi:

Format penulisannya adalah

=hitungwarna(data;warna)

Contoh:

=hitungwarna(C3:C12;C15)

Range C3:C12 adalah kumpulan sel yang ingin dijumlahkan

Range C15 adalah kriteria yang berisi warna tertentu sebagai acuan penjumlahan

Anda juga bisa DOWNLOAD contoh perhitungan tersebut di sini

Selain cara di atas kita juga bisa menambahkan Excel Add-Ins untuk menambahkan fungsi menjumlahkan angka dengan kriteria warna. Caranya sebagai berikut:

1. Download file Add-Ins HitungWarna di sini.

2. Buka Microsoft Excel, klik tombol Office button

3. Klik Excel Option

4. Tekan tombol Go..., maka akan muncul jendela Add-Ins, kemudian pilih Browse dan cari file hitungwarna.xlam yang sudah anda download tadi, terakhir tekan OK.

Jika muncul jendel Macro Security pilih Enable Macro supaya macro bisa dijalankan.

LINK DOWNLOAD:

Contoh Fungsi Excel HitungWarna

HitungWarna Add-Ins

Menghitung Jumlah Sel Dengan Kriteria Warna

2013-11-16T23:52:00.003+07:00

Pada dasarnya Microsoft Excel bisa untuk menghitung jumlah sel yang berisi text menggunakan rumus COUNTA, atau jika ingin menghitung jumlah sel yang berisi angka menggunakan rumus COUNT. Sedangkan untuk menghitung sel dengan kriteria tertentu kita bisa menggunakan rumus COUNTIF.

Sekarang pertanyaannya jika yang ingin kita hitung hanya sel yang ada warnanya (ada background) tetapi tetapi menggunakan kriteria tertentu, baik angka maupun text.

Lihat contoh tabel berikut:

Pada tabel di atas yang terhitung hanya jumlah sel dalam range yang berwarna (ada background). Sebagai ambil contoh kita pakai kriteria angka "5", pada range tersebut total angka 5 sebanyak 4 sel tetapi dari hasil perhitungan hanya terhitung 2 karena hanya ada 2 angka 5 pada sel berwarna.

Untuk melakukan perhitungan tersebut dibutuhkan kode VBA yang nantinya digunakan sebagai function.

Cara menambahkan kode VBA:

Aktifkan jendela Visual Basic Editor (tekan Alt+F11)
Klik menu Insert > Module
Masukkan kode berikut

Function CountColour(rng As Range, srchfor As String) As Long
Application.Volatile
Dim c As Range
For Each c In rng
If c.Interior.ColorIndex <> xlNone And UCase(c.Value) = UCase(srchfor) Then
CountColour = CountColour + 1
End If
Next
End Function

Cara Menggunakan Fungsi:

Format penulisannya adalah

=countcolour(range;criteria)

Contoh:

=countcolour(C3:C12;C15)

Range C3:C12 adalah kumpulan sel yang ingin dijumlahkan

Range C15 adalah kriteria tertentu sebagai acuan penjumlahan

Anda juga bisa DOWNLOAD contoh perhitungan tersebut di sini

Selain cara di atas kita juga bisa menambahkan Excel Add-Ins untuk menambahkan fungsi menghitung jumlah sel dengan kriteria warna. Caranya sebagai berikut:

1. Download file Add-Ins CountColour di sini.

2. Buka Microsoft Excel, klik tombol Office button

3. Klik Excel Option

4. Tekan tombol Go..., maka akan muncul jendela Add-Ins, kemudian pilih Browse dan cari file countcolour yang sudah anda download tadi, terakhir tekan OK.

Jika muncul jendel Macro Security pilih Enable Macro supaya macro bisa dijalankan.

LINK DOWNLOAD:

Contoh Fungsi Excel CountColour

CountColour Add-Ins

Fungsi Countif Pada Excel

2013-07-01T08:00:00.000+07:00

Seperti halnya fungsi COUNT dan COUNTA yang dipakai untuk menghitung jumlah sel dalam sebuah range data, fungsi COUNTIF di pakai untuk menghitung jumlah sel dalam sebuah range yang memenuhi kriteria tunggal yang telah kita tentukan.

Syntaks fungsi countif sebagai berikut:

=COUNTIF(range;criteria;...)

range : (diperlukan), berisi sekumpulan sel yang akan dihitung
criteria : (diperlukan), berisi kriteria yang menetapkan sel yang akan dihitung.

Berikut contoh penggunaan countif:

Contoh 1:
Pada tabel di bawah ini ketikkan pada sel C14 dengan rumus =COUNTIF(C3:C12;">40"). Setelah di Enter maka akan menghasilkan nilai "6", karena kriteria yang diberikan >40 maka akan menghitung jumlah sel yang berisi angka lebih besar dari 40. Untuk menghitung nilai lebih kecil ataupun sama dengan tinggal merubah pada kriterianya.

	A	B	C	D
1
2		No.	Nilai
3		1	25
4		2	27
5		3	35
6		4	51
7		5	46
8		6	82
9		7	56
10		8	73
11		9	19
12		10	66
13
14		Jml lebih besar dari 40	6
15

Contoh 2:
Ketikkan pada sel C14 dengan rumus =COUNTIF(C3:C12;"apel"). Hasil yang didapat adalah nilai "3" diperoleh dari perhitungan jumlah sel dengan content "apel" berjumlah 3 sel.

	A	B	C	D
1
2		No.	Nama Buah
3		1	apel
4		2	jeruk
5		3	nanas
6		4	apel
7		5	mangga
8		6	durian
9		7	durian
10		8	apel
11		9	nanas
12		10	semangka
13
14		Jml "apel"	3
15

Contoh 3:

Anda juga bisa menggunakan tanda tanya (?) dan tanda bintang (*) di dalam kriteria. Tanda tanya menggantikan karakter tunggal apapun, sedangkan tanda bintang untuk urutan beberapa karakter apapun. Pada sel C14 coba tuliskan rumus =COUNTIF(C3:C12;"buku*") untuk mencari jumlah sel yang berisi dengan awalan kata buku. Pada contoh tabel di bawah diperoleh jumlah sel dengan awalan kata buku berjumlah 3.

	A	B	C	D
1
2		No.	Nama Barang
3		1	buku tulis
4		2	buku gambar
5		3	ballpen
6		4	spidol
7		5	penggaris
8		6	kertas kuarto
9		7	kertas folio
10		8	buku note
11		9	pensil
12		10	penghapus
13
14			3
15

Sedangkan untuk penggunaan tanda tanya (?) hanya mewakili satu karakter, jadi jika ingin mewakili 2 karakter tinggal menambahkan tanda tanya satu lagi (??) dan seterusnya.

Untuk menghitung jumlah sel dengan menggunakan beberapa kriteria menggunakan fungsi COUNTIFS, untuk menghitung jumlah sel yang hanya berisi angka menggunakan fungsi COUNT, sedangkan untuk menghitung jumlah sel yang berisi text menggunakan fungsi COUNTA.

Fungsi Counta Pada Excel

2013-06-28T08:00:00.000+07:00

Selain fungsi COUNT yang digunakan untuk menghitung jumlah sel yang berisi angka, ada juga fungsi COUNTA yang berfungsi untuk menghitung jumlah sel yang tidak kosong dalam sebuah range data. Fungsi COUNTA menghitung setiap sel yang berisi berbagai jenis format data baik berupa angka, text, text kosong yang dihasilkan rumus (""), maupun nilai kesalahan yang dihasilkan sebuah rumus seperti #DIV/0!, #N/A, #REF!, dll. Fungsi COUNTA tidak akan menghitung sel yang kosong.

Syntaks fungsi COUNTA sebagai berikut:

=COUNTA(value1;value2;...)

value1 (Diperlukan). Argumen pertama yang mewakili nilai yang akan dihitung.
value2;... (Opsional). Argumen tambahan yang menyatakan nilai - nilai yang akan dihitung, hingga maksimal 255 argumen tambahan.

Sebagai contoh penggunaan fungsi COUNTA bisa dilihat pada tabel di bawah ini. Pada sel C3:C12 berisi beberapa jenis tipe data yaitu angka, text dan kode error sebuah rumus.

	A	B	C	D
1
2		No.	Nilai
3		1	25
4		2	TRUE
5		3	FALSE
6		4	51
7		5	TRUE
8		6	#DIV/0!
9		7
10		8	#N/A
11		9	19
12		10	#REF!
13
14		Jumlah baris	=COUNTA(C3:C12)
15

Pada sel C14 tuliskan rumus "=COUNTA(C3:C12)" lalu tekan Enter sehingga akan menghasilkan nilai seperti tabel di bawah ini:

	A	B	C	D
1
2		No.	Nilai
3		1	25
4		2	TRUE
5		3	FALSE
6		4	51
7		5	TRUE
8		6	#DIV/0!
9		7
10		8	#N/A
11		9	19
12		10	#REF!
13
14		Jumlah baris	9
15

Nilai 9 didapat dari perhitungan pada range yang dipilih memiliki 9 sel yang terisi / tidak kosong. Pada range tersebut sel C9 tidak terhitung karena pada sel tersebut merupakan sel kosong. Untuk menghitung jumlah sel yang hanya berisi angka bisa menggunakan fungsi COUNT, sedangkan untuk menghitung jumlah sel yang berisi angka dengan kriteria tertentu gunakan fungsi COUNTIF atau fungsi COUNTIFS.

Fungsi Count Pada Microsoft Excel

2013-06-24T08:00:00.000+07:00

Selain fungsi MAX dan fungsi MIN yang digunakan untuk mencari nilai terbesar dan terkecil, ada juga fungsi COUNT. Fungsi COUNT tersebut digunakan untuk menghitung jumlah sel yang berisi angka dari suatu range yang dipilih. Argumen bisa merujuk ke tipe data yang berbeda, tetapi hanya sel yang berisi angka yang dihitungnya.

Syntaks fungsi COUNT sebagai berikut:

=COUNT(value1;value2;...)

value1 diperlukan. Berisi angka atau range yang akan di hitung angkanya.
value2;... opsional. Hingga 255 item tambahan, referensi sel, atau rentang yang ingin dihitung angkanya.

Pada tabel di bawah ini adalah contoh penggunaan fungsi Count yang akan menghitung jumlah sel yang berisi angka pada range C3:C12

	A	B	C	D
1
2		No.	Nilai
3		1	25
4		2	27
5		3	35
6		4	51
7		5	46
8		6	82
9		7	56
10		8	73
11		9	19
12		10	66
13
14		Jumlah baris	=COUNT(C3:C12)
15

Kemudian pada sel C14 masukkan rumus "=COUNT(C3:C12)" lalu tekan Enter sehingga hasilnya akan tampak seperti tabel di bawah ini:

	A	B	C	D
1
2		No.	Nilai
3		1	25
4		2	27
5		3	35
6		4	51
7		5	46
8		6	82
9		7	56
10		8	73
11		9	19
12		10	66
13
14		Jumlah baris	10
15

Nilai 10 didapat dari perhitungan jumlah sel pada range yang dipilih yaitu berjumlah 10 sel. Pada range tersebut semua sel terhitung karena semua sel berisi format angka, jika selain format angka maka tidak akan ikut terhitung. Untuk menghitung jumlah sel yang berisi logika, teks atau nilai kesalahan bisa menggunakan fungsi COUNTA, sedangkan untuk menghitung jumlah sel yang berisi angka dengan kriteria tertentu gunakan fungsi COUNTIF atau fungsi COUNTIFS.

Mencari Nilai Minimal Pada Range Excel

2013-06-21T08:00:00.000+07:00

Setelah sebelumnya sudah dicontohkan penggunaan fungsi MAX untuk mencari nilai terbesar, pada postingan kali ini kebalikan dari fungsi MAX yaitu penggunaan fungsi MIN. Fungsi MIN dipakai untuk mengembalikan nilai terkecil dalam sekumpulan nilai.

Syntaks fungsi MIN sebagai berikut:

=MIN(number1;number2;...)

number1;number2;... = number1 diperlukan, angka selanjutnya adalah opsional. Bilangan 1 sampai 255 yang ingin di cari nilai minimalnya.

Penggunaan fungsi MIN seperti contoh data di bawah ini:

	A	B	C	D
1
2		No.	Nilai
3		1	25
4		2	27
5		3	35
6		4	51
7		5	46
8		6	82
9		7	56
10		8	73
11		9	19
12		10	66
13
14		Nilai Minimal	=MIN(C3:C12)
15

Kemudian masukkan rumus "=MIN(C3:C12)" pada sel C14 maka hasilnya akan tampak seperti tabel di bawah ini:

	A	B	C	D
1
2		No.	Nilai
3		1	25
4		2	27
5		3	35
6		4	51
7		5	46
8		6	82
9		7	56
10		8	73
11		9	19
12		10	66
13
14		Nilai Minimal	19
15

Mencari Nilai Max Dalam Sebuah Range

2013-06-20T08:00:00.000+07:00

Untuk mencari nilai maksimal sebuah range data pada Ms Excel bisa kita gunakan fungsi MAX. Fungsi MAX dipakai untuk mengembalikan nilai terbesar dalam sekumpulan nilai.

Syntaks fungsi MAX sebagai berikut:

=MAX(number1;number2;...)

number1;number2;... = number1 diperlukan, angka selanjutnya adalah opsional. Bilangan 1 sampai 255 yang ingin di cari nilai maksimalnya.

Penggunaan fungsi MAX dengan contoh data seperti di bawah ini:

	A	B	C	D
1
2		No.	Nilai
3		1	25
4		2	27
5		3	35
6		4	51
7		5	46
8		6	82
9		7	56
10		8	73
11		9	19
12		10	66
13
14		Nilai Maksimal	=MAX(C3:C12)
15

Masukkan rumus "=MAX(C3:C12)" pada sel C14 maka hasilnya akan tampak seperti tabel di bawah ini:

	A	B	C	D
1
2		No.	Nilai
3		1	25
4		2	27
5		3	35
6		4	51
7		5	46
8		6	82
9		7	56
10		8	73
11		9	19
12		10	66
13
14		Nilai Maksimal	82
15

Hasil dari rumus tersebut menghasilkan angka "82" dikarenakan angka tersebut merupakan nilai tertinggi dalam range yang dipilih. Untuk mencari nilai terendah gunakan fungsi MIN.
Selamat mencoba...

Perkalian Pada Beberapa Sel Atau Range

2013-06-13T22:31:00.000+07:00

Pada Ms Excel ada banyak fungsi perhitungan, selain fungsi penjumlahan juga terdapat fungsi yang dipakai untuk melakukan perkalian angka yang terdapat pada beberapa sel atau range, yaitu menggunakan fungsi PRODUCT.

Sintaks dari fungsi Product sebagai berikut:

=PRODUCT(number1;number2;...)

Misalnya pada sel A1 dan A2 terdapat angka yang hendak dikalikan, maka penggunaan rumusnya =PRODUCT(A1;A2). Anda juga bisa menggunakan rumus operasi matematika perkalian (*) yaitu =A1*A2, akan tetapi kalau sel yang hendak dikalikan dalam jumlah banyak akan lebih efisien jika menggunakan fungsi PRODUCT, seperti contoh di bawah ini:

	A	B
1	1	4
2	2	5
3	3	6
4
5		=PRODUCT(A1:B3)

Pada sel B7 anda masukkan rumus =PRODUCT(A1:B3) lalu tekan Enter, maka hasilnya adalah 720 seperti tabel di bawah ini. Jika menggunakan rumus operasi matematika rumusnya adalah =A1*A2*A3*B1*B2*B3 hasilnya juga sama 720.

	A	B
1	1	4
2	2	5
3	3	6
4
5		720

Fungsi Sum Pada Ms Excel

2013-06-11T22:45:00.000+07:00

Fungsi Sum pada Microsoft Excel merupakan fungsi yang sering digunakan dalam pengolahan data Excel, karena fungsi Sum ini dipakai untuk penjumlahan. Fungsi penjumlahan ini bisa digunakan untuk menjumlahkan data dalam satu sheet, antar sheet dalam satu file, bahkan bisa juga digunakan untuk antar file.

Syntax fungsi Sum adalah sebagai berikut:

=SUM(number1;number2;...)

number1,number2,dst. harus berisi format angka supaya bisa dijumlahkan.

Contoh penggunaan fungsi Sum:

1. Buka Ms. Excel kemudian isikan sel B1:B5 dengan angka-angka seperti gambar di bawah ini.

	A	B
1		10
2		15
3		20
4		5
5		30
6	Total	=SUM(B1:B5)
7

2. Letakkan kursor pada sel B6 kemudian tuliskan rumus berikut:

=SUM(B1:B5) lalu tekan "Enter"

3. Maka hasilnya pada sel B6 adalah "80". Angka 80 didapat dari penjumlahan dari sel B1 sampai dengan B5.

	A	B
1		10
2		15
3		20
4		5
5		30
6	Total	80
7

Contoh Penggunaan Fungsi SumIF

2013-06-09T23:23:00.001+07:00

Salah satu fungsi logika yang digunakan pada Microsoft Excel yaitu fungsi Sumif. Fungsi Sumif merupakan gabungan fungsi Sum (penjumlahan) dan fungsi If (logika). Jadi SUMIF adalah fungsi yang dipakai untuk menjumlahkan nilai-nilai yang terdapat pada suatu range data, hanya saja nilai yang diambil berdasarkan pada satu kondisi atau kriteria tertentu.

Syntax dari fungsi SUMIF adalah sebagai berikut :

SUMIF(range;criteria;sum_range)

Keterangan :

range : range dari cell-cell yang ingin kita evaluasi.

criteria : kondisi atau persyaratan yang ingin kita tentukan.

sum_range : range yang akan dijumlahkan.

Cara penggunaan fungsi Sumif silahkan buka Ms Excel kemudian ikuti langkah-langkah berikut:

1. Buatlah tabel seperti terlihat di bawah ini (A1:C11). Kita akan mencoba menghitung total dari Spidol saja kemudian hasilnya akan kita tempatkana pada C12.

2. Tempatkan cursor pada sel C12 dan ketikkan formula berikut :

=SUMIF(B2:B11;"Spidol";C2:C11)

Ini artinya kita memberi perintah kepada excel untuk memeriksa range B2:B11, syarat yang kita tentukan adalah "Spidol". Jadi apabila pada range B2:B11 terdapat sel yang berisi "Spidol", maka baris yang sama pada range C2:C11 akan dijumlahkan.

3. Hasilnya akan terlihat seperti gambar di bawah ini. Nilai 10 diperoleh dari penjumlahan untuk kriteria Spidol saja.

Demikianlah contoh sederhana tentang penggunaan fungsi Sumif pada Microsoft Excel, Klik disini untuk mendownload contoh worksheet penggunaan Sumif seperti contoh di atas.

Menggunakan Fungsi IFERROR

2013-05-13T09:30:00.000+07:00

Dalam membuat rumus pada Microsoft Excel terkadang nilai yang di hasilkan error seperti #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, atau #NULL!. Hal tersebut disebabkan karena data yang dicari tidak ditemukan atau tidak sesuai. Dengan munculnya hasil error tersebut dapat mengganggu kita dalam pengolahan data, misalnya dari hasil rumus itu kita totalkan lagi maka hasilnya juga ikut error.

Untuk mengatasi hal tersebut pada Microsoft Excel 2007 ke atas kita bisa memanfaatkan fungsi IFERROR dengan sintaks:

IFERROR(value;value_if_error)

Keterangan:

value adalah expresi / rumus yang akan diuji.

value_if_error adalah nilai yang dihasilkan jika rumus tadi error.

Sebagai contoh saya akan menggunakan fungsi vlookup seperti pada gambar berikut:

Buat table seperti contoh di atas masukkan rumus sebagai berikut:

1. Pada sel B2 masukkan rumus =VLOOKUP(A2;$H$3:$I$12;2;0) dan copy kan ke bawah hingga sel B10.

2. Pada sel D2 masukkan rumus =VLOOKUP(A2;$H$3:$J$12;3;0) dan copy kan ke bawah hingga sel D10.

3. Pada sel E2 masukkan rumus =C2*D2 dan copy kan ke bawah hingga sel E10.

4. Pada E11 masukkan rumus =SUM(E2:E10).

Contoh di atas pada baris 9 dan 10 keluar hasil #N/A sehingga pada bagian total juga ikut error (#N/A).

Masalah tersebut di atas dapat diatasi dengan menambahkan fungsi IFERROR dengan cara:

1. Pada sel B2 ubah rumus menjadi =IFERROR((VLOOKUP(A2;$H$3:$I$12;2;0));"") kemudian copy kan ke bawah hingga sel B10.
2. Pada sel D2 ubah rumus menjadi =IFERROR((VLOOKUP(A2;$H$3:$J$12;3;0));0) copy kan ke bawah hingga sel D10.
Dan hasilnya akan seperti gambar di bawah ini:

Sehingga hasilnya tidak keluar error dan tampak lebih rapi. Untuk melihat contoh file dalam bentuk excel silahkan download di sini.

Menghitung Jumlah Karakter Dalam Satu Sel

2013-04-29T21:43:00.000+07:00

Kali ini saya ingin membagikan fungsi untuk menghitung jumlah karakter dalam satu sel pada Microsoft Excel. Ternyata jumlah karakter pada Excel bisa dihitung dengan fasilitas yang sudah disediakan oleh Microsoft Excel.

Sebagai contoh kata "KARAKTER" jika dihitung jumlah karakternya ada 8 karakter. Untuk mengetahui caranya ikuti langkah-langkah berikut ini:

1. Tuliskan kata "KARAKTER" pada sel A1.

2. Pada sel C1 tuliskan formula =LEN(A1) lalu tekan Enter.

3. Maka hasilnya akan muncul angka 8 di sel B1.

Nah sekarang sudah tahu kan caranya menghitung jumlah karakter dalam satu sel pada Microsoft Excel. Pada contoh di atas yang dihitung adalah semua karakter yang ada di sebuah sel, lalu bagaimana jika kita ingin menghitung hanya karakter tertentu saja. Contohnya kita ingin manhitung jumlah huruf "A" yang ada di sel A1 dan kita akan menghitungnya di sel D1, caranya tuliskan pada sel D1 dengan formula =LEN(A1)-LEN(SUBSTITUTE(A1;"A";"")) lalu tekan Enter.

Maka akan menghasilkan angka 2 pada sel D1 karena huruf A pada tulisan KARAKTER jumlahnya ada 2.

Memisahkan Isi Sel Ke Kolom Yang Berbeda

2013-04-27T23:12:00.000+07:00

Terkadang data pada Microsoft Excel yang telah kita buat sebelumnya ada beberapa hal yang perlu kita rubah, seperti pembahasan kali ini yaitu memisahkan isi sel ke kolom yang berbeda. Sebagai contoh pada data nama karyawan yang sebelumnya kita tuliskan dengan nama lengkap, selanjutnya kita di suruh memisahkan antara nama depan, nama tengah dan nama belakang. Apabila data yang sudah kita input sedikit mungkin tidak begitu masalah bagi kita untuk menuliskannya kembali, lalu bagaimana kalau data tersebut sampai ratusan bahkan ribuan ?? tentu saja jika kita tulis ulang lagi akan memakan waktu yang cukup lama dan tentunya melelahkan.

Kali ini saya akan menjelaskan bagaimana cara memisahkan isi sel ke kolom yang berbeda. Sebagai contoh perhatikan isi sel yang akan dipisahkan ke kolom yang berbeda berikut ini:

Untuk memisahkan nama-nama tersebut ikuti langkah-langkah di bawah ini:

1. Seleksi range yang berisi data nama tersebut (A2:A6).

2. Klik tab Data, kemudian pada grup Data Tools, pilih Text to Columns.

3. Selanjutnya akan muncul kotak dialog Convert Text to Columns Wizard - Step 1 of 3. Pada langkah ini pastikan opsi Delimited sudah terpilih, lalu klik Next.

4. Kemudian pada kotak dialog Convert Text to Columns Wizard - Step 2 of 3, tepatnya pada bagian Delimiters pilih opsi Space, jika sudah klik Next.

5. Langkah berikutnya pada kotak dialog Convert Text to Columns Wizard - Step 3 of 3, tentukan dimana data akan diletakkan dengan mengetikkan nama sel pada kolom Destination, misalnya C2.

6. Yang terakhir tekan Finish, sehingga hasilnya akan tampak seperti berikut.

Demikianlah sedikit penjelasannya, semoga bermanfaat dan selamat mencoba.

Fungsi Perhitungan Pada Microsoft Excel

2013-04-22T22:40:00.000+07:00

Ada banyak sekali fungsi pada Microsoft Excel yang bisa kita gunakan untuk perhitungan di dalam menyelesaikan berbagai pekerjaan kita. Di antara sekian banyak fungsi pada Microsoft Excel ada beberapa yang sering di gunakan yaitu sebagai berikut:

1. Operator Matematika

Operator matematika yang digunakan adalah :

- Penjumlahan (+)

Contoh: =325+150

- Pengurangan (-)

Contoh: =200-50

- Perkalian (*)

Contoh: =5*3

- Pembagian (/)

Contoh: =45/3

- Perpangkatan (^)

Contoh: =8^3

- Persentase (%)

Contoh: 10%

2. Fungsi Sum

Fungsi Sum digunakan untuk melakukan penjumlahan sekumpulan data pada suatu range.

Bentuk penulisannya : =SUM(number1;number2;…)

Contoh : =SUM(4;8;9)

3. Fungsi Product

Fungsi Product digunakan untuk melakukan perkalian sekumpulan data pada suatu range.

Bentuk penulisannya : =PRODUCT(number1;number2;…)

Contoh : =PRODUCT(4;5;2)

4. Fungsi Max

Fungsi Max digunakan untuk mencari nilai tertinggi dari sekumpulan data (range).

Bentuk penulisannya adalah : =MAX(number1;number2;…)

Contoh : =MAX(14;25;21)

5. Fungsi Min

Fungsi Min digunakan untuk mencari nilai terendah dari sekumpulan data (range).

Bentuk penulisannya adalah : =MIN(number1;number2;…)

Contoh : =MIN(40;5;2)

6. Fungsi Count

Fungsi Count digunakan untuk menghitung jumlah data dari suatu range yang dipilih.

Bentuk penulisannya adalah : =COUNT(value1;value2;…;)

Contoh : =COUNT(2;2;2;2;2;2;2)

7. Fungsi Round

Fungsi Round digunakan untuk membulatkan bilangan ke digit tertentu.

Bentuk penulisannya adalah : =ROUND(number;num_digits)

Contoh : =ROUND(123.4567;1)

8. Fungsi Sqrt

Fungsi Sqrt digunakan untuk menghasilkan suatu nilai akar kwadrat dari suatu bilangan.

Bentuk penulisannya adalah : =SQRT(number)

Contoh : =SQRT(9)

9. Fungsi Power

Fungsi Power digunakan untuk menghasilkan suatu bilangan yang dipangkatkan.

Bentuk penulisannya adalah : = Power(number;power)

Contoh : =POWER(6;2)

10. Fungsi Sumsq

Fungsi Sumsq digunakan untuk mempangkatduakan angka dalam argument dan memberikan jumlah dari pemangkatan.

Bentuk penulisannya adalah : =SUMSQ(number1;number2;…)

Contoh : =SUMSQ(3;4) 32 + 42 = 9 + 16 = 25

11. Fungsi Average

Fungsi Average digunakan untuk menghitung nilai-nilai rata-rata.

Bentuk penulisannya adalah : =AVERAGE(number1;number2;…)

Contoh : =AVERAGE(10;5)

12. Fungsi Sumif
Fungsi Sumif digunakan untuk menghitung nilai pada suatu range dengan kriteria atau kondisi yang telah kita tentukan.
Bentuk penulisannya adalah : =SUMIF(range;criteria;sum_range)
Contoh : =SUMIF(A1:A10;"CONTOH";B1:B10)

Keterangan: setiap penulisan rumus harus diawali dengan tanda sama dengan (=) dan setelah siap memasukkan rumusnya harus menekan tombol Enter.

Mengonversi Text pada Microsoft Excel

2013-04-17T23:40:00.000+07:00

Di dalam Microsoft Word, kita bisa mengubah huruf kecil menjadi huruf kapital atau sebaliknya dengan sangat mudah, karena memang di sana ada fitur untuk melakukan hal tersebut. Sayang, Excel sendiri tidak menyediakan fitur seperti itu, sehingga banyak pengguna yang kerepotan saat akan mengubah huruf. Nah, cara yang akan dijelaskan berikut ini setidaknya akan membantu Anda dalam mengatasi masalah tersebut.

Untuk mengonversi huruf kapital menjadi huruf kecil caranya sebagai berikut:
1. Sebagai contoh ketikkan teks "MICROSOFT OFFICE EXCEL" pada sel A2.
2. Pada sel A4 ketikkan formula =LOWER(A2).
3. Tekan Enter dan hasilnya akan menjadi "microsoft office excel".

Sementara, jika ingin mengonversi huruf kecil ke huruf kapital formulanya =UPPER().

Selain pengubahan teks dari huruf kapital menjadi huruf kecil atau sebaliknya secara keseluruhan (semua huruf dalam teks), pada Microsoft Excel juga bisa mengubah hanya huruf pertama di stiap awal kata menjadi huruf kapital.

Cara mengonversinya adalah sebagai berikut:
1. Ketikkan teks "dr. citra lestari" dengan huruf kecil pada sel C2.
2. Lalu aktifkan sel C4, pada Formula bar ketikkan =PROPER(C2).
3. Tekan Enter, maka hasilnya akan seperti ini "Dr. Citra Lestari".

Cara ini bisa juga digunakan pada teks yang dari awal ditulis dengan huruf kapital.

Fungsi VLookup pada Microsoft Excel 2007

2013-04-03T22:31:00.000+07:00

Kali ini saya ingin berbagi tentang penggunaan Fungsi Vlookup pada Microsoft Excel 2007. Jika Anda sudah terbiasa bekerja dengan jumlah data yang cukup besar di Excel, maka biasanya data-data tersebut akan dibagi ke dalam bebarapa sheet terpisah. Salah satu tujuannya agar data tetap dapat terorganisasi dengan baik, sehingga akan mudah dalam pencarian data kembali.

Nah, biasanya data-data antar sheet tersebut saling berelasi, misalkan ada dua sheet yaitu sheet "master_barang" dan "transaksi_penjualan". Bagaimana Anda menghubungkan data dari berbagai sheet tersebut jika diperlukan?

Kita dapat melakukannya dengan fungsi lookup value - yaitu fungsi yang melakukan pencarian nilai padanan dari suatu cell ke cell yang terdapat pada table lain, baik sheet yang sama maupun terpisah.

Fungsi lookup pada Excel ada dua, yaitu sebagai berikut:

VLOOKUP (Vertical Lookup)
HLOOKUP (Horizontal Lookup)

Dan pada artikel ini akan ditunjukkan contoh penggunaan fungsi VLOOKUP yang lebih umum digunakan.

Apa itu VLookup ?

VLOOKUP adalah fungsi yang melakukan pencarian dari suatu nilai pada kolom pertama dari suatu table array dan jika ditemukan akan mengembalikan nilai lainnya pada baris yang sama dari hasil pencarian.

Berikut adalah syntax dan keterangan dari fungsi 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.

Terkait syntax vlookup, gambar di atas mengilustrasikan bagaimana mekanisme lookup ini bekerja:

Data sumber (lookup value) dar cell bernilai "kode 3" akan dicari ke suatu table referensi (table_array).
Kolom pertama (kode 1, kode 2, kode 3 dan kode 4) dari table_array berfungsi sebagai kunci (key).
Dari proses lookup kita akan dapatkan posisi di baris ke 3.
Setelah itu kita akan mengambil kolom yang kita inginkan (col_index_num) dari baris tersebut.

Contoh Penggunaan

Pada contoh berikut kita akan menggunakan vlookup untuk mencari referensi nama barang dengan menggunakan kode barang yang terdapat dalam suatu daftar transaksi.

Buka aplikasi MS Excel 2007, buat tabel seperti contoh di bawah ini kemudian ganti nama sheet menjadi master_barang. Atau bisa langsung download file contohnya di sini.

2. Buat nama range sel A2 sampai D6 dengan nama "master_barang".
3. Buka sheet 2 dan buatlah tabel seperti berikut:

Pada sel C2 masukkan fungsi vlookup sebagai berikut:

=VLOOKUP(B2;master_barang;2;0)

copy sel C2 ke seluruh baris di bawahnya.

Pada sel D2 masukkan fungsi vlookup sebagai berikut:

=VLOOKUP(B2;master_barang;3;0)

copy sel D2 ke seluruh baris di bawahnya.

Pada sel F2 masukkan fungsi vlookup dan perkalian sebagai berikut:

=VLOOKUP(B2;master_barang;4;0)*E2

copy sel D2 ke seluruh baris di bawahnya dan hasilnya akan seperti berikut ini:

Semoga penjelasan di atas dapat bermanfaat bagi para pembaca.

Mendefinisikan Nama Sel dan Range Excel

2013-03-28T23:30:00.000+07:00

Sel atau Range pada Microsoft Excel dapat diberi nama, sehingga pada waktu Anda menggunakan formula, argumen di dalam formula tersebut dapat dimengerti dengan mudah. Cara ini membuat Anda lebih mudah dalam membaca nama sel maupun range daripada yang secara default berupa gabungan huruf dan angka.

Ada beberapa cara untuk memberikan nama sel atau range yaitu:

Cara Pertama:

1. Seleksi sel atau range yang akan diberi nama, contoh kita pilih sel B3.
2. Pada tab Formulas, tepatnya pada grup Defined Names, pilih Define Name.

3. Pada kotak dialog New Name, tuliskan nama sel pada kolom Name, misalnya Kode_Material. Jika nama lebih dari dua kata gunakan tanda underscore ( _ ). Untuk memudahkan anda dalam mengingat nama sel atau range gunakan kata-kata yang pendek dan simpel.

Jika sudah maka nama sel atau range yang sudah Anda buat akan muncul pada kolom Name Box di sebelah kiri Formula Bar, seperti gambar berikut ini.

Cara Kedua:

Cara kedua yang bisa Anda lakukan untuk memberikan nama sel atau range yaitu dengan menyeleksi menggunakan mouse. Cara ini hanya bisa dilakukan terhadap sel atau range yang memiliki keterangan yang diletakkan di bagian atas, bawah, kiri atau kanan.

Untuk lebih jelasnya ikuti langkah-langkah berikut ini:

1. Seleksi rangkaian sel B2:D3.

2. Pada tab Formulas, tepatnya pada grup Defined Names, pilih Create from Selection.

3. Di kotak dialog yang muncul, tentukan dimana lokasi keterangan sel:

Top row, di baris atas.
Left column, di kolom kiri.
Bottom row, di baris bawah.
Right column, di kolom kanan.

4. Karena keterangan dalam contoh ini terletak di baris atas, maka kita pilih Top row, kemudian tekan OK.

Gambar berikut ini adalah contoh pemberian nama sel yang sudah selesai dilakukan.

Cara Ketiga:

Selain kedua cara yang sudah dijelaskan di atas, ada satu lagi alternatif untuk memberi nama sel atau range yaitu dengan cara menulis di kotak nama sel/range (Name Box). Cara ketiga ini sangat simpel karena tidak perlu membuka banyak menu, langkah-langkahnya adalah sebagai berikut:

1. Seleksi sel atau range yang akan diberi nama.

2. Pada kotak nama sel/range yang berada di sebelah kiri Formula Bar, tuliskan nama yang diinginkan, lalu tekan tombol Enter.

Demikian sedikit penjelasannya semoga bisa bermanfaat.

Membuka Worksheet yang Terproteksi Password

2013-03-27T05:25:00.000+07:00

Pada postingan sebelumnya sudah dijelaskan tentang bagaimana Memproteksi Worksheet dengan Password. Untuk yang belum tahu cara memproteksi worksheet dengan password silahkan klik di sini.

Kali ini saya akan menjelaskan cara membuka worksheet yang terproteksi password. Untuk membuka worksheet yang sebelumnya diberi password, caranya cukup mudah, yakni sebagai berikut:

1. Klik kanan mouse pada tab Sheet yang diberi password --> Unprotect Sheet...

atau bisa juga melalui tab Review --> klik Unprotect Sheet pada group Changes.

2. Pada kotak dialog yang muncul, masukkan password yang sama ketika Anda memberi password lalu klik OK.

Setelah worksheet yang terproteksi password sudah terbuka maka Anda bisa merubah/edit isi worksheet tersebut.

Memproteksi Worksheet dengan Password

2013-03-24T00:50:00.000+07:00

Sebagai bentuk antisipasi terhadap hal-hal yang tidak Anda inginkan terhadap data di dalam worksheet yang telah Anda buat, Anda bisa memberinya password untuk melindunginya. Sehingga jika ada orang lain yang ingin melakukan sesuatu terhadap data Anda, mereka diharuskan untuk memasukkan password terlebih dahulu.

Cara memproteksi worksheet dengan password adalah sebagai berikut:
1. Bukalah worksheet yang akan diberi password.
2. Klik kanan mouse di atas tab Sheet --> Protect Sheet...

Di kotak dialog yang muncul, Anda bisa memberikan ijin kepada semua pengguna untuk melakukan terhadap worksheet dengan memilih opsi yang Anda inginkan pada bagian Allow all users of this worksheet to. Secara default, opsi yang terpilih adalah Select locked cells (semua pengguna dapat menyeleksi sel yang terkunci) dan Select unlocked cells (semua pengguna dapat menyeleksi sel yang tidak terkunci).
3. Masukkan password pada kolom Password to unprotect sheet, klik OK.

4. Setelah itu akan muncul lagi kotak dialog konfirmasi password. Masukkan sekali lagi password yang sama di kolom ini, klik OK.

Terakhir simpan workbook Anda. Mulai sekarang, setiap kali akan merubah atau memodifikasi data di dalam worksheet tersebut, maka harus memasukkan password terlebih dahulu.

Akan tetapi jika ada beberapa sel yang diperbolehkan untuk diubah, sebelum menerapkan cara di atas Anda bisa memilih dengan menyorot salah satu atau beberapa sel yang nantinya bisa bisa diubah oleh siapapun setelah worksheet kita proteksi.

Cara membuat unlocked cells adalah sebagai berikut:
1. Pilih sel yang nantinya tidak Anda inginkan ikut terproteksi (unlocked cells).
2. Kemudian pilih tab Home --> Format (pada group Cells) --> pilih menu Format Cells. Atau dengan cara lain yaitu dengan cara klik kanan pada sel yang Anda pilih kemudian pilih menu Format Cells.
3. Pada kotak dialog Custom List, Anda pilih tab Protection.
4. Hilangkan tanda checklist pada kotak di sebelah kiri tulisan Locked lalu klik OK.

Dengan demikian setelah anda memproteksi dengan password, pada sel tersebut masih bisa di ubah ataupun diinput datanya.

Catatan:
- Cara ini hanya untuk memproteksi pada satu worksheet saja alias tidak diterapkan pada semua worksheet.
- Password sebenarnya hanya pilihan opsional, artinya boleh diisi atau tidak.
- Fitur Protect Sheet ini juga bisa Anda akses melalui tab Review, tepatnya pada grup Changes.

Demikian sedikit penjelasannya semoga bisa bermanfaat untuk Anda.

Mengubah Nama Kolom Excel Menjadi Angka

2013-03-21T05:41:00.000+07:00

Kali ini saya akan menjelaskan tentang cara Mengubah Nama Kolom Excel Menjadi Angka. Pada dasarnya tampilan worksheet Microsoft Excel terdiri dari kolom dan baris, untuk nama kalom yaitu kolom A,B,C,D,dst... sedangkan untuk nama baris yaitu baris 1,2,2,4,dst... Jadi untuk nama sel di kolom A baris 1 ialah sel A1. Ada kalanya kita melihat sebuah worksheet Excel nama kolom menjadi seperti nama baris (angka) yaitu kolom 1,2,3,4,dst, jadi untuk nama selnya menjadi R1C1 (artinya sel tersebut terletak di row 1 colomn 1).

Jika suatu saat Anda menjumpai hal tersebut dalam aktivitas bekerja, khususnya dalam penggunaan aplikasi Microsoft Excel tidak usah khawatir karena cara ini sangat sederhana sekali dan Anda semua pasti bisa melakukannya.

Berikut cara mengubah nama kolom Excel 2007 menjadi angka:

1. Buka workbook Microsoft Excel

2. Klik logo "Office Button" yang berada di pojok kiri atas --> pilih "Excel Options"

3. Kemudian pilih menu Formulas

4. Di bawah submenu Working with formulas beri check list kotak kecil di sebelah kiri tulisan "R1C1 reference style"

Sedangkan untuk mengembalikannya menjadi huruf caranya sama seperti di atas, pada item no. 4 anda buang check list pada kotak kecil di sebelah kiri tulisan "R1C1 reference style".

Demikianlah cara mengubah nama kolom Excel 2007 menjadi angka dan lihat hasilnya, semoga bisa bermanfaat untuk anda.

Cara Memindahkan dan Menyalin Sheet Pada Microsoft Excel

2013-03-15T00:02:00.002+07:00

Pada postingan kali ini saya ingin menjelaskan suatu hal yang sangat sederhana yaitu bagaimana cara memindahkan dan menyalin sheet atau juga disebut Move or Copy pada Microsoft Excel. Meskipun cara ini sangat sederhana tetapi bebarapa hari yang lalu diantara teman kerja saya ada yang bertanya "bagaimana sih caranya copy sheet secara cepat supaya isi dan formatnya sama ?". Sehingga saya postingkan tulisan ini, siapa tahu bisa bermanfaat bagi para pembaca.

Oke langsung saja ke pokok pembahasan yaitu bagaimana cara memindahkan dan menyalin sheet pada Microsoft Excel, caranya adalah sebagai berikut:
1. Buka file Excel dimana terdapat sheet yang hendak Anda pindahkan ataupun anda salin.
2. Klik kanan pada sheet yang hendak di pindahkan, lalu pilih Move or Copy…
3. Untuk memindahkan ke dalam file yang sama Anda tinggal menentukan letak sheet tersebut di bawah tulisan Before sheet:, sedangkan untuk memindahkan ke file baru Anda pilih di bawah tulisan To book:, pilih (new book) kemudian tentukan letak sheet yang Anda inginkan lalu tekan “OK”.
4.Sedangkan untuk manyalin (copy) sheet caranya sama dengan yang di atas hanya saja sebelum tekan OK, Anda beri tanda centang pada kotak kecil di sebelah tulisan Create a copy lalu tekan OK.

Demikianlah sedikit penjelasan mengenai cara memindahkan dan menyalin sheet pada Microsoft Excel, semoga bisa bermanfaat bagi Anda.

Mengganti Jenis Font Secara Default

2013-03-15T00:01:00.000+07:00

Pada Microsoft Excel 2007 secara default menggunakan jenis font Calibri dengan ukuran 11. Apabila jenis dan ukuran font tersebut ternyata tidak pernah dipakai dalam menyelesaikan pekerjaan, bisa diganti secara default dengan jenis font yang biasa digunakan dalam pekerjaan Anda. Supaya kerja Anda menjadi lebih efisien karena tidak perlu lagi mengubah jenis font yang sesuai dengan keinginan Anda.

Pada Microsoft Excel 2007, caranya adalah sebagai berikut:

1. Klik logo Office pada bagian kiri atas, kemudian pilih Excel Options.

2. Pada menu Popular, ubah jenis dan ukuran font pada opsi Use this font dan font size.

3. Tekan OK, untuk menutup kotak dialog.

4. Berikutnya akan muncul kotak dialog yang meminta kita untuk me-restart Excel. Tekan OK, lalu tutup dan buka kembali Excel untuk melihat perubahannya.

Mengganti Warna Tampilan Microsoft Excel

2013-03-14T23:58:00.000+07:00

Postingan sebelumnya sudah dijelaskan sedikit tentang Pengertian Microsoft Excel. Pada kesempatan ini saya akan menjelaskan tentang cara mengganti warna tampilan Microsoft Excel 2007. Ada tiga warna yang dapat Anda pilih untuk mengubah tampilan Excel, yaitu: Blue, Silver dan Black.

Untuk mengganti warna tampilan Microsoft Excel 2007, caranya adalah sebagai berikut:

1. Klik logo Office yang berada pada kiri atas → Excel Options

1. 2. Pada menu Popular, ubah color scheme sesuai warna yang Anda inginkan.

1. 3. Setelah Anda pilih warna yang Anda sukai lalu klik OK untuk melihat perubahannya.

Sekarang warna tampilan Microsoft Excel sudah berubah sesuai pilihan Anda. Selamat mencoba dan semoga bermanfaat.

Pengertian Microsoft Excel

2013-03-10T12:26:00.000+07:00

Sebagian besar orang mungkin sudah mengenal yang namanya Microsoft Excel, tapi bagi yang baru belajar tidak ada salahnya untuk mengetahui pengertian dan dasar-dasar pengoperasian Ms. Excel.

Ms. Excel adalah program aplikasi atau semacam software untuk mengolah suatu angka-angka statistic. Ms. Excel ini berkemampuan untuk mengolah data dengan rumus, sedang rumus yang dipakai adalah rumus operator matematika dan rumus fungsi.

Rumus operator merupakan suatu rumus yang bertujuan untuk perhitungan matematik. Misal : penambahan, pengurangan, perkalian, pembagian dan lainnya. Sedang rumus fungsi adalah suatu rumus yang bertujuan untuk menghitung dalam bentuk matematika maupun non matematika.

Ms Excel ini terbagi menjadi 3 serial, yakni serial 2003, 2007, dan 2010.Karena ada tiga serial, maka cara penggunaannya pun berbeda. Namun fungsinya tetap sama kok. Tidak ada salahnya kalian mengenal terlebih dahulu tentang dasar-dasar MS. Excel. Dalam Ms. Excel, terdapat workbook dan worksheet. Workbook terdiri dari beberapa worksheet. Sedangkan Worksheet adalah lembar kerja yang nantinya digunakan untuk mengolah dan mengorganisasikan data.Worksheet terdiri dari beberapa baris (row) dan kolom (column). Dan pertemuan baris dan kolom disebut juga dengan sel (cell). Perlu diketahui juga bahwa gabungan dari beberapa sel dinamakan Range.

Itu sedikit penjelasan tentang apa itu Ms. Excel. Semoga bermanfaat bagi kalian semua.