Menjumlahkan nilai teks berdasarkan kriteria sebagian teks dengan SUMIF dan COUNTIF

Postingan ini merupakan kelanjutan dari postingan sebelumnya tentang fungsi teks di excel. Dalam postingan sebelumnya dibahas tentang cara memilih sebagian teks yang terdapat dalam sebuah sel. Tulisan ini masih berkaitan erat dengan posting sebelumnya. Dalam pembahasan kali ini akan dibahas cara menghitung nilai dan jumlah teks yang proses perhitungan berdasarka kriteria sebagian teks yang terdapat dalam sel. Beberapa penggunaan SUMIF dan COUNTIF pernah saya bahas disini belajar excel dan belajar excel 2007

Untuk lebih mudahnya bisa lihat contoh di bawah ini:
Diketahui :
Dalam sebuah perpustakaan kawasan tertinggal terdapat beragam buku. Buku tulis ada 5 buah, Buku gambar pinokio ada 8 buah, Buku harian ada 3 buah, Novel remaja ada 2 buah, Novel sejarah 6 buah. (Tentunya di perpustakaan tersebut masih banyak buku lain , silahkan ditambah sendiri untuk pengembangan formula excel, dalam contoh ini hanya tediri dari beberapa buku di atas)

Ditanyakan:
Berapa jumlah buku
Berapa jumlah novel
Berapa banyak jenis buku
Berapa banyak jenis novel

Jika datanya hanya beberapa baris mungkin masih mudah untuk menghitung secara manual, namun bagaimana jika datanya bertambah menjadi ratusan hingga ribuan baris, tentunya perhitungan dengan cara manual masih bisa dilakukan namun lebih efektif jika menggunakan software excel.

Untuk menyelesaikan kasus di atas buat tabel seperti di bawah ini

1. Tabel data

2. Lakukan perhitungan jumlah buku
a. Untuk menghitung jumlah buku, di D11
=SUMIF($A$3:$A$7,"*"&C11&"*",B3:B7)
Formula di atas berarti  jumlahkan semua nilai pada range B3:B7 jika sel pada range A3:A7 memenuhi kriteria  *C11* atau *Buku*
b. Untuk menghitung jumlah novel di D12
=SUMIF($A$3:$A$7,CHAR(42)&C12&CHAR(42),$B$3:$B$7)
Rumus di atas sama pada rumus 2a, namun menggunakan ASCI Code  dengan rumus lakukan penjumlahan nilai pada range B3:B7 jika kriterianya CHAR(42)&C12&CHAR(42) atau sederhananya *Novel*   dimana tanda bintang (*) adalah karakter ke 42 dalam code ASCII

3. Lakukan perhitungan jenis buku
a. Di sel D15 ketik rumus
=COUNTIF($A$3:$A$7,"*"&C11&"*")
b. Di D16 ketik formula
=COUNTIF($A$3:$A$7,"*"&C12&"*")
Rmus pada 3a dan 3b  hampir sama pada rumus 2a dan 2b, namun pada COUNTIF hanya melakukan pencacahan sedangkan pada SUMIF  melakukan penjumlahan berdasarkan kriteria, jika tidak sesuai kriteria akan dibaikan.

Cara menggabungkan fungsi if dan vlookup di excel 2007

Beberapa fungsi logika yang tersedia di excel 2007 dan sering digunakan adalah fungsi AND, FALSE,IF,IFERROR,NOT,OR,TRUE. Dalam postingan kali ini akan dibahas tentang salah satu dari fungsi logika tersebut yaitu penggunaan fungsi IF. Atau lebih detailnya Fungsi IF digabungkan dengan fungsi VLOOKUP.

Salahsatu keunggulan excel 2007 adalah kemudahan yang ditawarkan untuk menggabungkan fungsi-fungsi yang tersedia tanpa perlu khawatir dengan compatibiltas fungsi tersebut.

Jika dalam contoh sebelumnya saya menggunakan fungsi IF dengan satu buah tabel referensi bisa dilihat disini Fungsi IF VLOOKUP , Dalam contoh ini akan digunakan dua buah tabel referensi .


Untuk menggabung fungsi IF dan VLOOKUP bisa ikuti prosedur berikut :

1. Buat tabel kerja dan dua buah tabel referensi, sederhananya seperti gambar di bawah ini :
 Kosongkan kolom D dan E karena nantinya akan diisi dengan formula

2. Dalam sel D4 akan berisi formula yang menggunakan fungsi VLOOKUP , fungsi VLOOKUP ini akan mengecek nilai pada sel C4 selanjutnya akan mencocokkan dengan nilai pada range G4:I7, jika menemukan nilai yang sama maka sel D4 akan diisi dengan nilai referensi yang ada di kolom H (kolom kedua dalam range G4:I7)
Di sel D4 ketik formula berikut :
=VLOOKUP(C4,$G$4:$I$7,2)

Di sel E4 ketik penggabungan formula IF dan VLOOKUP berikut
=IF(B4=2010,(VLOOKUP(C4,$G$4:$I$7,3,0)),(VLOOKUP(C4,$G$10:$I$13,3,0)))
Hasilnya akan tampak seperti di bawah ini

Formula pada sel E4 jika diterjemahkan ke dalam bahasa manusia kira-kira bunyi perintahnya seperti di bawah ini:
Jika sel B4 bernilai 2010 maka gunakan fungsi vlookup pada sel E2 dengan tabel referensi pada range G4:I7 (G$4:$I$7, tanda $ menandakan alamat absolut), selanjutnya cocokkan nilai P001 pada sel C4 dengan nilai yang ada pada range G4:I7, jika ditemukan nilai yang sama maka ambil nilai yang ada pada kolom ke 3 pada range G4:I7 kemudian masukkan ke dalam sel E4
Jika sel B4 tidak bernilai 2010 maka terapkan fungsi VLOOKUP pada range $G$10:$I$13 dengan prosedur seperti saat B4 bernilai true namun tabel referensi pada range $G$10:$I$13

Kira-kira seperti cerita pendek di atas excel menerjemahkan perintah tentang penggabungan fungsi IF dan VLOOKUP. Silahkan dikembangkan dengan menggunakan logika dan kasus yang lebih kompleks misalnya tabel referensi lebih dari 2 , variable di kolom B lebih dari 3 , atau tabel referensi berada pada sheet yang berbeda dengan tabel kerja.

Cara mengedit tampilan persamaan kurva /grafik di excel

Terkadang tampilan equation pada grafik /kurva yang sudah dibuat di excel belum sesuai dengan format equation yang diinginkan, misalnya : persamaan yang tampil y = x² + 2E-14 , sedangkan persamaan kuadrat yang umum dikenal menggunakan format y = x² ,Untuk mengatur tampilan tersebut bisa saja anda mengatur secara manual dengan melakukan double klik pada persamaan yang ada di plot area tersebut, namun bisa juga dengan melakukan pengaturan "Format Trendline Label".

Catatan: Postingan ini adalah kelanjutan dari postingan sebelumnya tentang cara membuat grafik di excel. Sebelum membaca lebih lanjut postingan di bawah ini sebaiknya baca terlebih dahulu postingan awalnya disini cara membuat grafik pada excel

Contoh persamaan grafik yang ingin diubah bisa dilihat di bawah ini

Selanjutnya, untuk lebih mudahnya silahkan ikuti prosedur di bawah ini,
1. Klik kanan pada box equation y = x² + 2E-14
pilih Format Trendline Label

2. Saat window Format Trendline Label tampil , pada sisi kiri window pilih Number, Dalam Number category pilih Number
Pilih Close

3. Hasilnya akan terlihat seperti di bawah ini

Artikel Terkait:

Menampilkan bingkai grafik di excel 2007

Disaat membuat grafik di excel, biasanya frame border tidak tampil pada sisi kanan plot area. Bingkai grafik tersebut dapat dibuat dengan menggunakan border color dan solid line pada format plot area. Dalam gambar di bawah ini bingkai grafik sisi kanan tidak tampak.

Gambar grafik berikut adalah gambar grafik yang dibuat dengan menggunakan type scatter graph, yang pembuatannya pernah saya bahas disini excel grafik.

Untuk menampilkan bingkai grafik tersebut silahkan ikuti prosedur berikut:

1. Klik pada plot area, hingg di pojok tiap border muncul bulatan kecil .
Selanjutnya klik kanan pada plot area , pada popup menu pilih Format Plot Area

2.Pada bagian Boder Color, pilih Solid line
Klik Close

3. Hasilnya akan tampak seperti gambar di bawah, bingkai grafik di sisi sebelah kanan sudah tampak.

Cara membuat kurva parabola di excel

Dalam ilmu matematika dikenal berbagai macam grafik/kurva diantaranya kurva linier (garis lurus), kurva lingkaran, kurva ellips , parabola, hiperbola, logaritma, eksponensial , kurva sinusoidal, cosinus, tangensial dan berbagai kurva lainnya. Pada dasarnya semua kurva tersebut dapat dibuat dengan menggunakan microsoft excel. Dalam postingan kali ini khusus dibahas tentang cara membuat kurva parabola di excel 2007.

Untuk membuat kurva parabolic di ms excel, silahkan ikuti prosedur berikut:

1. Buat tabel data seperti di bawah ini
a. Kolom A berisi data yang diketik secara manual
Data Kolom B bisa diketiik manual, atau menggunakan rumus berikut
=A3^2
Catatan : Data di kolom B merupakan hasil kuadrat dari data pada kolom A

b. Blok sel pada range A3:B13
Pilih Insert - Scatter - pilih type scatter yang tidak bergaris (lihat panah merah)

2. Saat grafik berikut tampil, klik pada titik data selanjutnya klik kanan kemudian pilih Add trendline

3. Akan muncul kotak dialog Format Trendline
Pilih Trendline Options
Pada bagian Trend Regression Type pilih Polinomial
Centang "Display equation on chart"

4. Hasilnya akan terlihat seperti di bawah ini

5. Dengan menggunakan persamaan berikut pada sel C3
=(A3^2)+(2*A3)+4
Selanjutnya ulangi proses /langkah 1 sampai 3 di atas maka akan diperoleh grafik seperti di bawah ini

Contoh file excelnya bisa didownload disini parabola graph

Untuk mengatur grafik agar lebih rapi bisa dilihat beberapa pengaturan pada grafik berikut :

fungsi CountIf

ungsi CountIF di excel 2007

Fungsi countif digunakan untuk menghitung /mencacah sebuah sel/range berdasarkan kiteria tertentu
Untuk lebih mudahnya bisa lihat contoh di bawah ini. Kita akan menguhitung banyaknya siswa yang memperoleh nilai A, B,C, D dan E

1. Buat tabel berikut

2. Di sel  ketik formula

Di sel C14 ketik formula :  =COUNTIF(C4:C12,"A")
Di sel C15 ketik formula :  =COUNTIF(C4:C12,"B")
Di sel C16 ketik formula :  =COUNTIF(C4:C12,"C")
Di sel C17 ketik formula :  =COUNTIF(C4:C12,"D")
Di sel C18 ketik formula :  =COUNTIF(C4:C12,"E")

Dari tabel terlihat banyaknya soswa yang mendapat nilia A =3 orang , B 3 orang, C D dan E masing-masing 1 orang. Fungsi countif tentunya kan bermanfaat jika datanya terdiri dari ratusan record, jika hanya belasan saja mungkin lebih baik dihitung manual.

File excelnya bisa didownload disini Fungsi CountIF di excel 2007 (9 KB)

Fungsi Hlookup sederhana

Fungsi Hlookup hampir sama dengan vlooukup, tetapi tabel referensinya disusun secara horisontal.

Berikut contoh penggunaan hlookup

1. Buat tabel kerja dan tabel referensi

2. Masukkan formula berikut:
a. Pada sel B5 ketik:
=HLOOKUP(A5,$F$4:$I$6,2,0)
b. Pada sel C5 ketik:
=HLOOKUP(A5,$F$4:$I$6,3,0)
c. Copy formula dari baris 5 ke 16
3. Hasilnya seperti gambar di bawah




Buat teman-teman pengguna microsoft excel 2007. Penggunaan fungsi hlookup di bisa dilihat panduannya disini fungsi hlookup excel 2007

Fungsi IF Nested dan Text untuk menghitung gaji pegawai

Dalam bahasan kali ini kita akan mencoba mempelajari tentang penggunaan fungsi if bersarang (if nested) yang digabungkan dengan fungsi text untuk menghitung gaji pegawai.
Adapun data dan persyaratan yang akan digunakan dapat anda lihat pada bagian A sampai E di bawah ini.

A. GOLONGAN
jika kode pegawai = A1 golongan 1
jika kode pegawai = B2 golongan 2
jika kode pegawai = C3 golongan 3
jika kode pegawai = D4 golongan 4

B. JABATAN
jika kode pegawai=DIR maka JABATAN=DIREKTUR, mendapat GAPOK 2500000 dan TUNJANGAN 1000000
jika kode pegawai=STA maka JABATAN=STAF, mendapat GAPOK 2000000 dan TUNJANGAN 800000
jika kode pegawai=KAR maka JABATAN=KARYAWAN, mendapat GAPOK 1500000 dan TUNJANGAN 600000
jika kode pegawai=CAR maka JABATAN=CARAKA, mendapat GAPOK 1000000 dan TUNJANGAN 500000


C. STATUS ADALAH
jika kode pegawai = L maka STATUS = laki-laki
jika kode pegawai = P maka STATUS = wanita

D. PAJAK ADALAH
jika GOL 1 dikenakan PAJAK 15% dari GAPOK
jika GOL 2 dikenakan PAJAK 10% dari GAPOK
jika GOL 3 dikenakan PAJAK 5% dari GAPOK
jika GOL 4 tidak dikenakan PAJAK

E. KODE PEGAWAI
A1DIRDP
B2STASL
C3KARKL
D4PERPL
B2STASP
C3KARKP

Untuk menyelesaikan permasalahan di atas, maka dapat disederhanakan dengan dengan membuat tabel berikut (data acuan adalah data Kode Pegawai), anda bisa mengembangkan dengan menggunakan data acuan lain.

1. Buat tabel data berikut:
Isi sel A35 dengan kode pegawai
( bagian tabel dengan background abu-abu akan dihitung dengan menggunakan formula)


2. Buat formula berikut
a.Pada sel B35 isi formula:
=IF(MID(A35,3,3)="DIR","DIREKTUR",(IF(MID(A35,3,3)="STA","STAF",(IF(MID(A35,3,3)="KAR","KARYAWAN","CARAKA")))))
b.Pada sel C35 isi formula:
=IF(LEFT(A35,2)="A1",1,(IF(LEFT(A35,2)="B2",2,(IF(LEFT(A35,2)="C3",3,4)))))
c.Pada sel D35 isi formula:
=IF(RIGHT(A35,1)="P","WANITA","LAKI-LAKI")
d.Pada sel E35 isi formula:
=IF(B35="DIREKTUR",2500000,(IF(B35="STAF",2000000,(IF(B35="KARYAWAN",1500000,1000000)))))
e.Pada sel F35 isi formula:
=IF(B35="DIREKTUR",1000000,(IF(B35="STAF",800000,(IF(B35="KARYAWAN",600000,500000)))))
f.Pada sel G35 isi formula:
=IF(C35=1,E35*0.15,(IF(C35=2,E35*0.1,(IF(C35=3,E35*0.05,0)))))
g. Gaji Bersih
=E35+F35-G35

3. Copy formula ke sell yang ada di bawahnya (B36 hingga H40)

4. hasilnya bisa dilihat seperti di bawah ini



5. Jika anda ingin mendownload source file excelnya bisa download disini

Mencari data ganda (duplikat) dalam satu kolom di excel 2003

Dalam bahasa excel kali ini, kita akan membahas fungsi dasar excel buat adik-adik sd,smp dan sma (kalo adik2 mahasiswa pasti sudah terbiasa menggunakan fungsi ini) yang mungkin belum tahu mencari data ganda (duplikat data) dalam sebuah kolom,bisa menggunakan cara berikut.

1. Buat data dalam seperti berikut:
a. Pada kolom A ketik nama teman-teman anda (usahakan ada yang anda ketik dengan ganda)
b. Pada kolom B biarkan kosong (nanti diisi dengan formula)



2. Pada sel B2 ketik formula :
=IF(MAX(COUNTIF(A2:A11,A2:A11))>1,"Duplikat","Unik")
Kopi formula hingga sel B11
dari hasil terlihat bahwa:
andi dan ghalib mempunyai duplikat.



Jika ingin mendownload file excelnya bisa gunakan link berikut

Mencari data ganda (duplikat) pada sheet yang berbeda di excel 2003

Berikut ini adalah penggunaan fungsi excel untuk mencari data ganda (duplikat). Dalam kasus ini di sheet1 berisi daftar nama yang sudah dikoreksi menggunakan cara berikut. kemudian pada sheet2 dibuat daftar nama yang berbeda dengan daftar nama pada sheet1. Dengan harapan pada sheet2 tidak ada data nama yang sama dengan data nama pada sheet1, maka perlu dilakukan pengujian.

Berikut prosedur yang anda bisa lakukan:
1. Pada sheet1 buat tabel berikut:
isi dengan daftar nama




2. Pada sheet2 buat tabel data daftar nama berikut
kolom c dibiarkan kosong, karena akan diisi formula


3. Pada sel C2, ketik formula berikut
=NOT(ISERROR(VLOOKUP(B2,sheet1!B2:B9,1,0)))
Copy formula C2 hingga ke sel C13

Catatan:
Jika status pada kolom C = True, berarti data tersebut telah ada pada sheet1, berarti mengalami duplikat.


Jika anda ingin mendownload file excelnya, bisa gunakan link berikut

Menghitung biaya sewa kamar menggunakan fungsi vlookup di excel 2003

Berikut ini adalah contoh sederhana penggunaan fungsi vlookup untuk menghitung biaya sewa kamar. Dalam contoh ini saya membuat tabel kerja dan tabel referensi dibuat beda sheet.

Data baku yang saya peroleh dari teman adalah seperti di bawah ini



1. Untuk memudahkan menyelesaikan masalah di atas, maka pada sheet1 dibuat tabel kerja sebagai berikut:
(Sel dengan background hijau adalah data input No urut, ID, dan nama pemesan)
(Sel dengan background coklat diisi dengan formula)


2. pada sheet2 buat tabel referensi seperti di bawah ini


3. Aktifkan sheet1. Buat formula berikut:
pada sel D4 ketik :
=VLOOKUP(B4,Sheet2!$A$4:$D$12,2,0)
pada sel E4 ketik:
=VLOOKUP(B4,Sheet2!$A$4:$D$12,3,0)
pada sel F5 ketik:
=VLOOKUP(B4,Sheet2!$A$4:$D$12,4,0)
pada sel H5 ketik:
=F4-G4

Hasilnya seperti gambar di bawah ini


Jika ingin mendownload file excel bisa gunakan link berikut