Fungsi SUBTOTAL

Fungsi ini menghasilkan subtotal dalam daftar atau database, fungsi ini mempunyai argumen yang harus diisi yang dapat menghitung dari data yang tersembunyi (hidden) ataupun tidak. Akan tetapi fungsi SUBTOTAL mengabaikan semua baris yang tidak disertakan di hasil filter, tidak peduli nilai function_num yang Anda gunakan.

Sintaks :

SUBTOTAL(function_num,ref1,[ref2],...)

Function_num atau argumen ini diperlukan. Angka 1 sampai 11 (menyertakan nilai tersembunyi) atau 101 sampai 111 (mengabaikan nilai tersembunyi) menentukan fungsi apa yang digunakan untuk menghitung subtotal dalam daftar.
 

Function_num (menyertakan nilai tersembunyi)	Function_num (mengabaikan nilai tersembunyi)	Fungsi
1	101	AVERAGE
2	102	COUNT
3	103	COUNTA
4	104	MAX
5	105	MIN
6	106	PRODUCT
7	107	STDEV
8	108	STDEVP
9	109	SUM
10	110	VAR
11	111	VARP

Contoh Penerapan :

Cobalah ketik pada Cell B15 sesuai contoh tabel dibawah ini  =SUBTOTAL(      dan akan muncul jendela argumen seperti dibawah ini :

Selanjutnya kita coba saja menggunakan Fungsi ini dengan argumen No.2 yaitu COUNT. Dan dengan kondisi Row No 9-10 kita Hide (Disembunyikan).

Coba anda perhatikan, rumus ini tetap menyertakan Row 9-10 yang kita sembunyikan untuk dihitung. Hasilnya adalah 12.

Argumen 2 sekarang kita ganti dengan 102 (mengabaikan Hidden Row), menjadi :

=SUBTOTAL(102;B2:B13)

  Jelaskan sekarang? Perbedaan argumen 2 dan 102?

Cobalah berlatih Fungsi SUBTOTAL ini dengan menggunakan argumen2x yang disediakan.

















Penerapan dengan Filter :

Misalkan saya hanya mengambil nama Abidin saja, fungsi ini tetap hanya menghitung sesuai kriteria filter yaitu "Abidin" yang record berjumlah 2 buah. Fungsi ini akan mengabaikan data yang disembunyikan oleh Filter.

Fungsi AVERAGE dan variannya

Kasus 1: Menghitung Rata-rata 5 Nilai (Skor) Tertinggi dan Terendah

Kasus 2: Menghitung Rata-rata dengan 1 Kriteria

Kasus 3: Menghitung Rata-rata dengan 1 Kriteria Wildcard

 
Kasus 4: Menghitung Rata-rata dengan Operator "<" "<" atau "="

 
Kasus 5: Menghitung Rata-rata dengan 1 atau lebih Kriteria

 
Kasus 6: FUNGSI AVERAGE() DAN AVERAGEA()

Perbedaan antara 2 fungsi ini adalah Fungsi AVERAGE() akan menghitung cell yang berisi value (numerik ataupun teks), sedangkan Fungsi AVERAGEA() akan mengabaikan cell kosong.


Silahkan Download File Latihan

Tes TPA dan TOEFL

Kasus 1 (Update Otomatis Data Skor TPA) :

Seorang staf admisi di Program S2 Magister kebingungan karena memproses banyak data Tes TPA (Tes Potensi Akademik) sebagai syarat masuk sebagai mahasiswa. Dia mempunyai Tabel Data seperti ini.

Skor minimal yang dapat diterima adalah 500. Terlihat ada 3 Tabel, yang pertama adalah Tabel Tes Penerimaan, Tabel ke-2 adalah Tabel Tes Perbaikan 1 dan ketiga adalah Tabel Tes Perbaikan 2.
Petugas admisi tersebut menginginkan update otomatis data skor TPA bagi yang belum memenuhi syarat, setelah calon mahasiswa yang belum lulus mengulang tes tersebut, dan tes perbaikan diberikan maksimal 2 kali.

Setelah data skor TPA di-input menjadi seperti ini :

Pada tabel skor TPA diatas, terlihat ada 4 calon mahasiswa yang mengulang sampai 2 kali karena skornya dibawah 500. Yaitu :
1. Sule
2. Bondan
3. Siti
4. Sumi

Ada juga yang mengulang 1 kali saja, yaitu :
1. Randi

Dan pada Tabel Update terlihat data Skor TPA terkini yang otomatis ter-update mengikuti input data pada ketiga Tabel Skor TPA.

Inilah formulanya (Cell B18 - B25) :

=IF(AND(COUNTIF($D$7:$D$11;A25)=0;COUNTIF($G$7:$G$10;$A25)=0);INDEX($B$7:$B$14;MATCH($A25;$A$7:$A$14;0));IF(COUNTIF($G$7:$G$10;$A25)=0;INDEX($E$7:$E$11;MATCH($A25;$D$7:$D$11;0));INDEX($H$7:$H$10;MATCH($A25;$G$7:$G$10;0))))

Anda bisa menambah Fungsi Error Trapping pada rumus ini:

=IFERROR(IF(AND(COUNTIF($D$7:$D$14;A25)=0;COUNTIF($G$7:$G$14;$A25)=0);INDEX($B$7:$B$14;MATCH($A25;$A$7:$A$14;0));IF(COUNTIF($G$7:$G$14;$A25)=0;INDEX($E$7:$E$14;MATCH($A25;$D$7:$D$14;0));INDEX($H$7:$H$14;MATCH($A25;$G$7:$G$14;0))));"")


Silahkan anda gunakan rumus ini untuk kasus-kasus yang serupa. Be Creative....


Kasus 2 (Mengambil Data Calon Mahasiswa Yang Lulus Seleksi) :

Petugas Admisi ingin mengambil data calon mahasiswa yang lulus dengan memenuhi skor minimal TPA (500) dan TOEFL (500).

Dan kriteria yang diterapkan adalah :

Formula yang diterapkan kali ini :

Formula yang pertama adalah menghitung jumlah calon mahasiswa yang lulus, berjumlah 3 orang. Formula ini berkaitan dengan formula kedua, yang akan menampilkan sesuai data yang ditampilkan oleh formula pertama.













Untuk mengambil data digunakan Array Formula (CTRL-SHIFT-ENTER) :

Penjelasan :
1. Bagian =IF(ROWS(E$10:E10)<=$G$4 akan mengecek apakah current row sekarang lebih kecil atau  sama dengan jumlah calon mahasiswa yang lulus (3 orang). Jika "Ya", teruskan eksekusi yang nomor 2     dibawah ini.

2. Bagian INDEX(A$3:A$9;SMALL(IF($B$3:$B$9>=$E$4;IF($C$3:$C$9>=$F$4;ROW($B$3:$B$9)-ROW($B$3)+1));ROWS(E$10:E10))) akan mengambil data baris demi baris pada Kolom "Nama", "TPA" dan TOEFL".

3. Bagian =IF(ROWS(E$10:E10)<=$G$4 jika bernilai salah maka akan menampilkan Cell kosong (;"")   


Kasus 3 (Mengambil Data Mahasiswa Yang Memenuhi Syarat Wisuda) :

Petugas Alumni S2 ingin mengambil data dari mahasiswa yang memenuhi syarat wisuda. Kriteria yang harus dipenuhi sebanyak 3 macam, yaitu Ujian Tesis, Dokumen Wisuda Lengkap dan SPP Lunas.

Sebenarnya kasus ini sama dengan Kasus sebelumnya, hanya saja kali ini kriterianya menjadi 3 macam.

Solusi rumusnya menjadi.....

Download File Latihan

Mengambil Data Dengan Rentang Angka

Problem :
Seumpama saya punya Toko Buku, dan punya data seperti ini :

Dan kriteria yang ingin saya dapatkan adalah seperti ini :

Saya ingin data "Jenis Buku" bisa muncul secara otomatis berdasarkan angka No Buku yang saya input, dengan syarat No buku tersebut masih di dalam Range No Buku pada Tabel Database Buku.

Try this Array Formula in Cell C17 :

=IFERROR(INDEX($C$3:$C$10;MATCH(1;(C14>=$A$3:$A$10)*(C14<=$B$3:$B$10);0));"") 

and Search Results :

Atau anda bisa juga menggunakan Rumus Non Array (diinput di Cell C17) :

=IFERROR(VLOOKUP(LOOKUP($C$14;SMALL($A$3:$A$10;ROW(INDIRECT("1:"&ROWS($A$3:$A$10)))));$A$3:$C$10;3;0);"")


Download File Latihan

Mencari Produk Dengan Kriteria "Price Range" dan "Date Range"

Kasus 1 :

Tabel Data

Kriteria yang ingin kita cari, seperti ini :

Dimana di saat kita input Range Harga, misalnya dari Rp.10.000  sampai Rp. 50.000 , di Tabel Results akan muncul data produk, tgl order, qty dan harga sesuai range harga yang kita input.

Hasilnya akan muncul data yang sesuai denga price range yang di-input. Formulanya seperti ini :

Kolom "Produk"
=IFERROR(INDEX($A$2:$D$9;SMALL(IF(($D$2:$D$9<=$B$14)*$D$2:$D$9>=$B$13);ROW($D$2:$D$9)-MIN(ROW($D$2:$D$9))+1);ROW(1:1));COLUMN(A1:A1));"")

Kolom "Tgl Order"
=IFERROR(INDEX($A$2:$D$9;SMALL(IF(($D$2:$D$9<=$B$14)*$D$2:$D$9>=$B$13);ROW($D$2:$D$9)-MIN(ROW($D$2:$D$9))+1);ROW(1:1));COLUMN(B1:B1));"")

Kolom "Qty"
=IFERROR(INDEX($A$2:$D$9;SMALL(IF(($D$2:$D$9<=$B$14)*$D$2:$D$9>=$B$13);ROW($D$2:$D$9)-MIN(ROW($D$2:$D$9))+1);ROW(1:1));COLUMN(C1:C1));"")

Kolom "Harga"
=IFERROR(INDEX($A$2:$D$9;SMALL(IF(($D$2:$D$9<=$B$14)*$D$2:$D$9>=$B$13);ROW($D$2:$D$9)-MIN(ROW($D$2:$D$9))+1);ROW(1:1));COLUMN(D1:D1));"")

Dan jangan lupa untuk menekan CTRL+SHIFT+ENTER yaa........ (Array Formula)


Kasus 2 :

Pada kesempatan ini saya akan menambahkan kriteria yang dicari, masih dengan Tabel data yang sama, seperti contoh di bawah ini.

Pada contoh di atas, kita menambahkan kriteria "Date Range", selain "Price Range" yang sudah dibahas pada kasus 1.

Hasilnya seperti ini

Rumusnya menjadi semakin kompleks dan ini bisa diselesaikan dengan array Formula.

Kolom "Produk"

=IFERROR(INDEX($A$2:$A$9;SMALL(IF(($B$2:$B$9>=$B$13)*($B$2:$B$9<=$B$14)*($D$2:$D$9>=$B$17)*($D$2:$D$9<=$B$18);ROW($A$2:$A$9)-MIN(ROW($A$2:$A$9))+1);ROW(1:1)));"")

Kolom "Tgl Order"

=IFERROR(INDEX($B$2:$B$9;SMALL(IF(($B$2:$B$9>=$B$13)*($B$2:$B$9<=$B$14)*($D$2:$D$9>=$B$17)*($D$2:$D$9<=$B$18);ROW($B$2:$B$9)-MIN(ROW($B$2:$B$9))+1);ROW(1:1)));"")

Kolom "Qty"

=IFERROR(INDEX($C$2:$C$9;SMALL(IF(($B$2:$B$9>=$B$13)*($B$2:$B$9<=$B$14)*($D$2:$D$9>=$B$17)*($D$2:$D$9<=$B$18);ROW($C$2:$C$9)-MIN(ROW($C$2:$C$9))+1);ROW(1:1)));"")

Kolom "Harga"

=IFERROR(INDEX($D$2:$D$9;SMALL(IF(($B$2:$B$9>=$B$13)*($B$2:$B$9<=$B$14)*($D$2:$D$9>=$B$17)*($D$2:$D$9<=$B$18);ROW($D$2:$D$9)-MIN(ROW($D$2:$D$9))+1);ROW(1:1)));"")

Download File Latihan

Menghitung Kata dalam String Kalimat

Good day guys, iseng-iseng saya kepengen menghitung berapa kata sih di dalam suatu string kalimat tertentu,    yah kita masih banyak berkutat dengan Teks Function.....wkwkwkwk.....

Rumusnya adalah :

=IF(LEN(TRIM(A120))=0;0;LEN(TRIM(A120))-LEN(SUBSTITUTE(A120;" ";""))+1)


Atau anda ingin menghitung kata denga rumus Array? Bisa....bisa.....

Rumusnya adalah:

=SUM(IF(LEN(TRIM(A125:D128))=0;0;LEN(TRIM(A125:D128))-LEN(SUBSTITUTE(A125:D128;" ";""))+1))

Don't forget to CTRL-SHIFT-ENTER cause this is array formula....