Serba Serbi Fungsi RANK

Apakah Anda pernah menggunakan Fungsi Rank() untuk menentukan urutan ranking pada data anda? Jika sudah pasti anda akan menemukan data ranking yang aneh, yaitu sebagai berikut :
1. Jika data mempunyai skor yang sama maka ranking-nya pun sama (dobel)
2. Bahkan ada nomor urutan ranking yang tidak muncul

Misalnya saya mempunyai data yang akan diurutkan menggunakan Fungsi Rank() seperti ini :

Anda pasti akan menemukan keanehan pada angka score yang sama. Score Andi dan Rosi adalah 25, tetapi mempunyai Ranking yang berurutan.
Score Manik, Samsul, Borneo dan Afifa sama-sama mempunyai score 45 dan mempunyai Ranking yang tidak urut. Bahkan ada nomor Urut 2, 7, 8 ,9 dan 12 tidak muncul. Agak kacau bukan?

Fungsi Rank() masih dapat kita gunakan untuk membuat Ranking suatu data dengan sedikit modifikasi.

Perhatikan di Tabel selanjutnya....


Formula 1

Disini Fungsi Rank() ditambah dengan Fungsi COUNTIF() untuk menghitung Score yang sama dalam satu kolom, mulai baris pertama sampai current row kemudian dikurangi 1.
Coba perhatikan, kita berhasil membuat suatu Ranking yang berurutan walaupun di data tersebut ada beberapa angka score yang sama.

Formula 2

Disini kita mencoba dengan Formula yang sedikit berbeda dengan contoh sebelumnya. Fungsi Rank() ditambah dengan Fungsi COUNTIF() untuk menghitung score yang sama dimulai dari baris pertama sampai 1 baris diatas current row, kemudian ditambah 1.

Semoga dapat berguna ;) Happy & Fun with MS Excel.....

Bonus : Rumus dibawah ini juga bisa untuk mencari posisi ranking dengan mengabaikan data yang sama (dobel) :

 Input di Cell C2 :
=COUNTIF($B$2:$B$13; ">"&B2)+SUM(IF(B2=$B$2:B2; 1; 0))+COUNTIF(B$1:B1;B2)
atau
=RANK(B2;$B$2:$B$13;0)+SUMPRODUCT(--($B$2:$B$13=B2);--($A$2:$A$13<A2))
atau
=COUNTIF($B$2:$B$13; ">"&B2)+SUM(IF(B2=$B$2:B2; 1; 0))


CONTOH LAIN :
Tabel 2 Kolom Nilai dengan Ranking :

Contoh diatas manakala data skor tidak ada yang dobel, jika terjadi data dobel maka rumus ranking diatas akan menampilkan ranking yang sama pula. How to solve this problem? Look example down below.........

Tabel 2 Kolom dengan Ranking (Data Skor ada yang sama) :

Ranking dengan Penambahan Akhiran Urutan (Bahasa Inggris) :

Download File Latihan

Fungsi TRANPOSE

Pada artikel kali ini, saya akan membahas Fungsi Transpose(), dimana sangat berguna untuk memindahkan data dengan format Tabel yang berbeda, fungsi ini berguna jika data yang anda pindahkan berisi ratusan data. Fungsi Transpose ini merupakan Formula Array, yang mana di dalam penerapannya dengan menekan Tombol CTRL-SHIFT-ENTER atau saya singkat CSE.

Misalkan anda mempunyai tabel data seperti ini:

Tetapi Bos Anda menginnginkan Format Tabel seperti ini:

Hal ini menjadi problem bagi anda jika data yang anda punya berisi ratusan atau ribuan data, tetapi jangan kuatir, di dalam MS Excel telah menyediakan Fungsi Transpose untuk memindahkan data anda dengan cara yang praktis.

Langkah-langkahnya:

Silahkan Blok dulu Tabel

Setelah di-blok, silahkan anda langsung mengetikkan  =Transpose(

Kemudian anda blok Tabel Data anda, pada contoh disini, saya memisahkan Tabel Data dan Tabel Baru di dalam Lembar Kerja yang terpisah.

Setelah di-blok, anda tekan Tombol CTRL-SHIFT-ENTER secara bersamaan. Dan Voila.... data anda berpindah sesuai Tabel Database anda secara AJAIB...!



Download FIle Latihan

Penggunaan Fungsi SUMIFS

Anda pasti sudah sangat familiar dengan Fungsi VLOOKUP() dari MS Excel yang sangat berguna untuk mengambil data tertentu sesuai kriteria yang kita inginkan (Multiple Criteria). Di dalam MS Excel  2007 - 2010 terdapat fungsi SUMIFS() yang di dalam Contoh yang akan saya berikan berfungsi hampir sama dengan Fungsi VLOOKUP(). Kelemahan Fungsi ini tidak bisa menampilkan data TEKS, hanya NUMERIK saja.

Fungsi SUMIFS ini berguna jika kita mengambil data tertentu dengan 2 kriteria. Pada contoh diatas, Kriteria Pertama adalah SUPPLIER, kemudian Kriteria Kedua adalah ITEM.

Download File Latihan

Nama Depan dan Nama Belakang

Disini saya akan share beberapa formula untuk memisahkan Nama Depan, Nama Tengah dan Nama Belakang, serta formula untuk. Dan formula untuk konversi nama dari pola Nama Depan & Nama Belakang menjadi Nama Belakang & Nama Depan maupun sebaliknya, serta beberapa trik rumus yang lainnya. Satu-persatu kasusnya, yaitu

Kasus A

Rumus Nama Depan:
=LEFT(A2;SEARCH(" ";A2;1)-1)
Rumus Nama Belakang
=TRIM(RIGHT(SUBSTITUTE(A2;" ";REPT(" ";99));99))

Kasus B

Rumus Nama Depan:
=LEFT(A7;SEARCH(" ";A7;1)-1)
Rumus Nama Tengah:
=TRIM(MID(SUBSTITUTE(A7;" ";REPT(" ";99));100;99))
Rumus Nama Belakang:
=TRIM(RIGHT(SUBSTITUTE(A7;" ";REPT(" ";99));99))

Kasus C

Rumus menghilangkan sebutan atau gelar di depan nama:
=TRIM(RIGHT(A12;LEN(A12)-SEARCH(" ";A12)))

Kasus D

Rumus konversi:
=MID(A18;SEARCH(" ";A18)+1;50)&" "&LEFT(A18;SEARCH(" ";A18;1)-2)

Kasus E

Rumus konversi:
=TRIM(RIGHT(SUBSTITUTE(A24;" ";REPT(" ";99));99))&", "&LEFT(A24;(FIND("|";SUBSTITUTE(A24;" ";"|";2)))-1)

Kasus F

Rumus memisahkan teks nama yang tanpa spasi tetapi harus ada huruf kapitalnya pada setiap awal kata karena untuk penanda
=TRIM(MID($A30;(SMALL(FIND(0;SUBSTITUTE($A30;CHAR(ROW(INDIRECT("65:90")));0)&0);COLUMNS($A$1:A$1)));(SMALL(FIND(0;SUBSTITUTE($A30;CHAR(ROW(INDIRECT("65:90")));0)&0);COLUMNS($A$1:A$1)+1))-(SMALL(FIND(0;SUBSTITUTE($A30;CHAR(ROW(INDIRECT("65:90")));0)&0);COLUMNS($A$1:A2)))))

*rumus ini rumus array, you need to be sure press all together CTRL+SHIFT+ENTER button, ENTER alone don't works

Kasus G

Rumus menambahkan spasi pada teks nama yang tidak ada spasinya dan minimal harus ada huruf kapital setiap awal kata.
=TRIM(LEFT(A35;MIN(FIND(CHAR(64+COLUMN($A$1:$W$1));MID(A35;2;255)&(CHAR(64+COLUMN($A$1:$W$1))))))&" "&MID(A35;1+MIN(FIND(CHAR(64+COLUMN($A$1:$W$1));MID(A35;2;255)&(CHAR(64+COLUMN($A$1:$W$1)))));SMALL(FIND(CHAR(64+COLUMN($A$1:$W$1));MID(A35;2;255)&(CHAR(64+COLUMN($A$1:$W$1))));2)-MIN(FIND(CHAR(64+COLUMN($A$1:$W$1));MID(A35;2;255)&(CHAR(64+COLUMN($A$1:$W$1))))))&" "&LEFT(REPLACE(A35;1;SMALL(FIND(CHAR(64+COLUMN($A$1:$W$1));MID(A35;2;255)&(CHAR(64+COLUMN($A$1:$W$1))));2);"");MIN(FIND(CHAR(64+COLUMN($A$1:$W$1));MID(REPLACE(A35;1;SMALL(FIND(CHAR(64+COLUMN($A$1:$W$1));MID(A35;2;255)&(CHAR(64+COLUMN($A$1:$W$1))));2);"");2;255)&(CHAR(64+COLUMN($A$1:$W$1))))))&" "&SUBSTITUTE(SUBSTITUTE(REPLACE(A35;1;SMALL(FIND(CHAR(64+COLUMN($A$1:$W$1));MID(A35;2;255)&(CHAR(64+COLUMN($A$1:$W$1))));2);"");LEFT(REPLACE(A35;1;SMALL(FIND(CHAR(64+COLUMN($A$1:$W$1));MID(A35;2;255)&(CHAR(64+COLUMN($A$1:$W$1))));2);"");MIN(FIND(CHAR(64+COLUMN($A$1:$W$1));MID(REPLACE(A35;1;SMALL(FIND(CHAR(64+COLUMN($A$1:$W$1));MID(A35;2;255)&(CHAR(64+COLUMN($A$1:$W$1))));2);"");2;255)&(CHAR(64+COLUMN($A$1:$W$1))))));"");MID(REPLACE(A35;1;SMALL(FIND(CHAR(64+COLUMN($A$1:$W$1));MID(A35;2;255)&(CHAR(64+COLUMN($A$1:$W$1))));2);"");MIN(FIND(ROW($1:$10)-1;REPLACE(A35;1;SMALL(FIND(CHAR(64+COLUMN($A$1:$W$1));MID(A35;2;255)&(CHAR(64+COLUMN($A$1:$W$1))));2);"")&5^19));255);"")&" "&MID(REPLACE(A35;1;SMALL(FIND(CHAR(64+COLUMN($A$1:$W$1));MID(A35;2;255)&(CHAR(64+COLUMN($A$1:$W$1))));2);"");MIN(FIND(ROW($1:$10)-1;REPLACE(A35;1;SMALL(FIND(CHAR(64+COLUMN($A$1:$W$1));MID(A35;2;255)&(CHAR(64+COLUMN($A$1:$W$1))));2);"")&5^19));255))

rumus terakhir ini juga rumus array, jangan lupa caranya yaaa...
FILE LATUHAN

Memperbaiki Data Text Dengan Fungsi PROPER

Terkadang kita pusing dengan data nama Pelanggan yang yang cara penulisannya berbeda-beda formatnya. Antara huruf kapital dan huruf kecil campur aduk tidak karuan. Jangan kuatir, di dalam MS Excel telah disediakan Fungsi PROPER() untuk memperbaikinya supaya rapi dan enak dibaca.

Menghilangkan Spasi Kosong

Saya lakukan perbaikan dan penambahan materi pada posting ini semoga bermanfaat

1. Menghilangkan Spasi Kosong

Rumus :
=SUBSTITUTE(A2;" ";"")

2. Mengganti Kata Menggunakan Fungsi SUBSTITUTE dan REPLACE

Rumus dengan SUBSTITUTE:
=SUBSTITUTE(A7;"Bapak";"Ibu")

Rumus dengan REPLACE
=REPLACE(A7;5;5;"Ibu")

Lebih simpel menggunakan SUBSTITUTE tetapi ini sifatnya "case sensitive", misalnya Ibu dengan ibu itu berbeda, jadi harus "exact match". Pada contoh rumus menggunakan fungsi REPLACE lebih ribet karena harus mencari posisi kata dari suatu kalimat yang harus diganti, dsilahkan dicermati sintaks dari rumus ini:

REPLACE( old_text, start, number_of_chars, new_text )

3. Membersihkan Spasi Kosong Yang Tertinggal

Mungkin sudah jelas ya lewat gambar diatas, kadang kita menemui data yang dipenuhi oleh spasi kosong yang tertinggal, nah hal ini terkadang yang membuat rumus excel kita jadi error. Anda bisa cek cell yang terdapat teks untuk menguji apakah jumlah karakternya sama dengan yang terpampang di monitor, cobalah memalkai fungsi LEN. Sebetulnya ada cara yang lebih praktis lewat excel untuk membersihkan "trailing spaces" ini, karena blog saya rumus excel ya saya terangkan lewat rumus saja yaaa....

Memisahkan Alamat dengan Kode Pos

Kasus 1

Kita mempunyai data alamat lengkap dengan kode posnya. tetapi kita tidak ingin menampilkan kode pos itu, dan meletakkannya di kolom lain.

Rumus tersebut berlaku hanya dengan format alamat yang tersusun tanpa adanya Koma (",") , jika alamat yang ingin kita pisahkan mengandung Koma yang memisahkan alamat dan kode pos maka diperlukan formula yang sedikit berbeda.

Kasus 2

Download File Latihan

Fungsi VLOOKUP Versus INDEX dan MATCH

Mungkin sudah banyak Rekan-rekan yang mengetahui Fungsi VLOOKUP di dalam Excel yang berfungsi mengambil informasi spesifik dari sebuah Tabel Database, yang mungkin berskala besar.

Contoh Sederhana penggunaan Fungsi VLOOKUP()

Disini kita perhatikan, bahwa Fungsi VLOOKUP() mencari data sesuai ke arah kanan dari Kolom Kunci yang terletak paling kiri sebanyak n kolom. Contoh di atas data yang diambil dari Tabel Data adalah kolom ke-2 (Kolom Harga).

Pertanyaan yang muncul kemudian adalah, Bagaimana penerapan rumus excel jika kita ingin mengambil data dari Tabel Database tetapi data tersebut terletak di sebelah kiri dari data/referensi?

Disini saya akan menunjukkan kegunaan Perpaduan Fungsi Index() dan Match(), yang berguna mengambil data di sebelah kiri dari Kolom Kunci.



Tetapi Fungsi VLOOKUP sebenarnya juga bisa mencari data di sebelah kiri dari Kolom Kunci, tentu saja dengan modifikasi dengan dipadukan dengan fungsi CHOOSE.

Ya ini Fungsi Left Lookup dengan VLOOKUP.......

Download File Latihan

Menampilkan Hari dan Bulan Secara Otomatis

Saya perbaiki dengan penambahan materi pada postingan ini, supaya lebih berkualiatas bobotnya, saya akan memberikan beberapa trik menampilkan hari dan bulan secara otomatis, mudan2xan bermanfaat.

Rumus Mengambil Nama Hari:
1. =CHOOSE(WEEKDAY(A3);"Minggu";"Senin";"Selasa";"Rabu";"Kamis";"Jumat";"Sabtu")
2. =TEXT(A3;"dddd")
3. =TEXT(A3;"[$-21]dddd")
atau berbahasa inggris:
1.=CHOOSE(WEEKDAY(A3);"Sunday";"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday")
2. =TEXT(A3;"[$-9]dddd")

Rumus Mengambil Nama Bulan:
1. =CHOOSE(MONTH(A3);"Januari";"Februari";"Maret";"April";"Mei";"Juni";"Juli";"Agustus";"September";"Oktober";"Desember")
2. =TEXT(A3;"mmmm")
3. =TEXT(A3;"[$-21]mmmm")
atau berbahasa inggris:
1.=CHOOSE(MONTH(A3);"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"December")
2.=TEXT(A3;"[$-9]mmmm")

Bagaimana dengan menggabungkan Hari dengan Tanggalnya?

Rumusnya:
1. =CHOOSE(WEEKDAY(A3);"Minggu";"Senin";"Selasa";"Rabu";"Kamis";"Jumat";"Sabtu")&", "&TEXT(A3;"dd mmm yyy")
2. =TEXT(A3;"[$-9]dddd")&", "&TEXT(A3;"[$-9]dd mmmm yyy")

Rumusnya (yang diwarnai kuning yaaa):
1. Hari =DAY(A29)
2. Bulan =MONTH(A29)
3. Tahun =YEAR(A29)

Nah kali ini membuat list nama2x bulan:
Rumus ini diletakkan pada cell A32 (perhatikan gambar kemudian di-copy kebawah sampai hasilnya blank.
1.=IF(ROWS($A$1:A1)>12;"";CHOOSE(ROWS($A$1:A1);"Januari";"Februari";"Maret";"April";"Mei";"Juni";"Juli";"Agustus";"September";"Oktober";"Nopember";"Desember"))
atau bahasa inggris
=IF(ROWS($A$1:A1)>12;"";CHOOSE(ROWS($A$1:A1);"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December"))

Nama-nama hari juga bisa dibuatkan list-nya secara otomatis tanpa harus diketik manual
1. =IF(ROWS(A1:$A$1)>7;"";CHOOSE(ROWS(A1:$A$1);"Minggu";"Senin";"Selasa";"Rabu";"Kamis";"Jumat";"Sabtu"))
atau bahasa inggris
=IF(ROWS(A1:$A$1)>7;"";CHOOSE(ROWS(A1:$A$1);"Sunday";"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday"))

Sekian dari saya
Wass Wr Wb
Rizky

Menghitung Umur dengan Fungsi DATEDIF

Bagaimanakah menampilkan umur seseorang dengan rumus excel, saya akan memeberikan contoh dengan beberapa kasus.

KASUS A:

Nah kasus A ini ingin menghitung umur dari 2 Tanggal (Hari ini dan Ulang Tahun), tetapi berupa angka2x, mohon diperhatikan di gambar, jadi bukan berupa format tanggal.
Rumus 1:
=IF(OR(A4="";B4="";C4="");"";IF(DATEDIF(DATE(C4;B4;A4);DATE(C7;B7;A7);"y")=0;"";DATEDIF(DATE(C4;B4;A4);DATE(C7;B7;A7);"y")&" tahun ")&IF(DATEDIF(DATE(C4;B4;A4);DATE(C7;B7;A7);"ym")=0;"";DATEDIF(DATE(C4;B4;A4);DATE(C7;B7;A7);"ym")&" bulan ")&IF(DATEDIF(DATE(C4;B4;A4);DATE(C7;B7;A7);"md")=0;"";DATEDIF(DATE(C4;B4;A4);DATE(C7;B7;A7);"md")&" hari"))
Rumus 2:
=IF(OR(A4="";B4="";C4="");"";IF(INT((DATE(C7;B7;A7)-DATE(C4;B4;A4))/365,25)=0;"";INT((DATE(C7;B7;A7)-DATE(C4;B4;A4))/365,25)&" tahun ")&IF(INT(MOD((DATE(C7;B7;A7)-DATE(C4;B4;A4))/365,25;1)*12)=0;"";INT(MOD((DATE(C7;B7;A7)-DATE(C4;B4;A4))/365,25;1)*12)&" bulan ")&IF(INT(MOD((DATE(C7;B7;A7)-DATE(C4;B4;A4))/30,4375;1)*30,4375)=0;"";INT(MOD((DATE(C7;B7;A7)-DATE(C4;B4;A4))/30,4375;1)*30,4375)&" hari"))

KASUS B:

Kasus B ini berbeda sedikit, karena saya akan membandingkannya langsung dengan fungsi TODAY(), jadi yang di-input hanya tanggal ulang tahunnya saja.
Rumus 1:
=IF(OR(A17="";B17="";C17="");"";IF(DATEDIF(DATE(C17;B17;A17);DATE(YEAR(TODAY());MONTH(TODAY());DAY(TODAY()));"y")=0;"";DATEDIF(DATE(C17;B17;A17);DATE(YEAR(TODAY());MONTH(TODAY());DAY(TODAY()));"y")&" tahun ")&IF(DATEDIF(DATE(C17;B17;A17);DATE(YEAR(TODAY());MONTH(TODAY());DAY(TODAY()));"ym")=0;"";DATEDIF(DATE(C17;B17;A17);DATE(YEAR(TODAY());MONTH(TODAY());DAY(TODAY()));"ym")&" bulan ")&IF(DATEDIF(DATE(C17;B17;A17);DATE(YEAR(TODAY());MONTH(TODAY());DAY(TODAY()));"md")=0;"";DATEDIF(DATE(C17;B17;A17);DATE(YEAR(TODAY());MONTH(TODAY());DAY(TODAY()));"md")&" hari"))
Rumus 2:
=IF(OR(A17="";B17="";C17="");"";IF(INT((DATE(YEAR(TODAY());MONTH(TODAY());DAY(TODAY()))-DATE(C17;B17;A17))/365,25)=0;"";INT((DATE(YEAR(TODAY());MONTH(TODAY());DAY(TODAY()))-DATE(C17;B17;A17))/365,25)&" tahun ")&IF(INT(MOD((DATE(YEAR(TODAY());MONTH(TODAY());DAY(TODAY()))-DATE(C17;B17;A17))/365,25;1)*12)=0;"";INT(MOD((DATE(YEAR(TODAY());MONTH(TODAY());DAY(TODAY()))-DATE(C17;B17;A17))/365,25;1)*12)&" bulan ")&IF(INT(MOD((DATE(YEAR(TODAY());MONTH(TODAY());DAY(TODAY()))-DATE(C17;B17;A17))/30,4375;1)*30,4375)=0;"";INT(MOD((DATE(YEAR(TODAY());MONTH(TODAY());DAY(TODAY()))-DATE(C17;B17;A17))/30,4375;1)*30,4375)&" hari"))

KASUS C:

Nah yang ini ber-format tanggal, rumusnya jadi lebih mudah:
Rumus 1:
=IF(OR(A26="";B26="");"";IF(DATEDIF(B26;A26;"y")=0;"";DATEDIF(B26;A26;"y")&" tahun ")&IF(DATEDIF(B26;A26;"ym")=0;"";DATEDIF(B26;A26;"ym")&" bulan ")&IF(DATEDIF(B26;A26;"md")=0;"";DATEDIF(B26;A26;"md")&" hari"))
Rumus 2:
=IF(OR(A26="";B26="");"";IF(INT((A26-B26)/365,25)=0;"";INT((A26-B26)/365,25)&" tahun ")&IF(INT(MOD((A26-B26)/365,25;1)*12)=0;"";INT(MOD((A26-B26)/365,25;1)*12)&" bulan ")&IF(INT(MOD((A26-B26)/30,4375;1)*30,4375)=0;"";INT(MOD((A26-B26)/30,4375;1)*30,4375)&" hari"))

KASUS D:

Sama dengan yang diatas, tetapi yang sekarang dibandingkan langsung dengan fungsi TODAY()
Rumus 1:
=IF(A35="";"";IF(DATEDIF(A35;TODAY();"y")=0;"";DATEDIF(A35;TODAY();"y")&" tahun ")&IF(DATEDIF(A35;TODAY();"ym")=0;"";DATEDIF(A35;TODAY();"ym")&" bulan ")&IF(DATEDIF(A35;TODAY();"md")=0;"";DATEDIF(A35;TODAY();"md")&" hari"))
Rumus 2:
=IF(A35="";"";IF(INT((TODAY()-A35)/365,25)=0;"";INT((TODAY()-A35)/365,25)&" tahun ")&IF(INT(MOD((TODAY()-A35)/365,25;1)*12)=0;"";INT(MOD((TODAY()-A35)/365,25;1)*12)&" bulan ")&IF(INT(MOD((TODAY()-A35)/30,4375;1)*30,4375)=0;"";INT(MOD((TODAY()-A35)/30,4375;1)*30,4375)&" hari"))

FILE LATIHAN
Salam

Penomoran Otomatis

Ini dia tips untuk membuat penomoran secara otomatis pada excel tanpa kita melakukan drag and drop dengan mouse. Hal yang paling memudahkan adalah penomoran ini otomatis akan menyesuaikan jika kita menghapus salah satu list yang ada.

Menghitung Rata2x Kelas, Frekuensi dan Konversi Nilai

Saya melakukan revisi pada tulisan ini dengan penambahan materi tentunya, mudah2xan bermanfaat.

Kasus 1 (Menghitung Nilai Di Atas Rata2x Kelas)

Rumus untuk jumlah murid yang mempunyai nilai di atas rata2x :
=COUNTIF($B$2:$B$15;">" &AVERAGE($B$2:$B$15))

Rumus untuk mengambil nama2 murid yang mempunyai nilai di atas rata2x kelas (array formula):
=IFERROR(INDEX($A$2:$A$15;SMALL(IF($B$2:$B$15>AVERAGE($B$2:$B$15);ROW($A$2:$A$15)-ROW($A$2)+1);ROWS($A$1:A1)));"")

Kasus 2 (Mengambil Nilai Rata2x Dengan Menggunakan Tabel Konversi Nilai)

Rumus:
=SUMPRODUCT(SUMIF($A$2:$A$4;$C$7:$C$10;$B$2:$B$4))/COUNTA($C$7:$C$10)

Kasus 3 (Mencari Jumlah Murid Dengan Kriteria Nilai Rentang Nilai Tertentu)

Saya berikan 3 contoh rumus yang digunakan :
1. =SUM(B2:B11>30;IF(B2:B11<75;1;0);0) --> rumus array
2. =SUMPRODUCT(--(B2:B11>30);--(B2:B11<75))
3. =COUNTIFS(B2:B11;">"&30;B2:B11;"<"&70)

Paling simpel menggunakan COUNTIFS bukan?

Kasus 4 (Mencari Nilai Dengan Menggunakan Tabel Konversi)

Rumus untuk IPK :
=SUMPRODUCT(SUMIF($A$2:$A$10;$E$2:$E$4;$B$2:$B$10))/COUNTA($E$2:$E$4)

Rumus untuk Nilai KOnversi :
=IFERROR(INDEX(A2:A10;MATCH(D7;B2:B10;0));INDEX(A2:A10;MATCH(MIN(IF(IF(B2:B10<=D7;B2:B10)=MAX(IF(B2:B10<=D7;B2:B10));B2:B10));B2:B10;0)))

Sekian Wass.Wr.Wb
Rizky

File Excel Latihan

Menghitung Kelompok Umur

Saya ingin memberikan contoh penerapan rumus excel untuk mengetahui berapa jumlah kelompok umur dari sebuah tabel database umur.

Berikut Tabel Database Umur :

Kriteria yang dicari adalah sebagai berikut:
a. Kurang dari 30 Tahun
b. 30 - 40 Tahun
c. 41 - 50 Tahun
d. Lebih dari 50 Tahun


Saya akan memberikan contoh pemecahan masalah perhitungan ini dengan 5 Solusi dibawah ini :

Solusi 1:

Pada contoh diatas, saya menambahkan Kolom Logic untuk memberikan pemahaman logikanya, seperti berikut :
a. < 30 Tahun --> Jelas
b. 30 - 40 Tahun --> Yang jadi patokan angka 30 saja, jadi >=30
c. 41 - 50 Tahun --> Yang adi patokan angka 41 saja, jadi >=40
d. > 50 Tahun --> Jelas

Perhatikan jika Kolom Logic saya hilangkan :

Solusi 2:

Mudah-mudahan jelas ya..... 

Solusi 3:

Di solusi 3 ini disisipkan Array Formula, coba diperhatikan :

Rumus Array digunakan untuk mencari Range Umur :

30 - 40 Tahun dan 41 - 50 Tahun

Silahkan anda gunakan rumus ini untuk pemecahan masalah yang serupa dengan range umur sesuai kebutuhan.

Solusi 4: COUNTIF-1

Solusi 5: COUNTIF-2

Banyak jalan menuju Roma kan.... Guys... ;)


Solusi 6: dengan COUNTIFS

 Fungsi COUNTIFS() ini tersedia di Excel 2007 dan 2010.

Solusi 7: dengan SUMPRODUCT

Solusi 8: Menggunakan SUM dan IF

Solusi 9:

Solusi 10:



Download File Latihan