Fungsi SUBTOTAL di Excel

Apa itu Fungsi SUBTOTAL di Excel?

Fungsi excel SUBTOTAL melakukan operasi aritmatika yang berbeda seperti rata-rata, produk, jumlah, deviasi standar, varian, dll., pada rentang yang ditentukan. Setiap operasi memiliki nomor fungsi unik yang ditetapkan untuknya. Nomor fungsi ini diberikan sebagai argumen ke fungsi SUBTOTAL.

Misalnya, lembar kerja terdiri dari nilai berikut di kolom A:

Sel A1 berisi 22
Sel A2 berisi 45
Sel A3 berisi 37
Sel A4 berisi 12

Rumus ‘=SUBTOTAL(1,A1:A4)’ mengembalikan 29. Keluaran ini adalah rata-rata dari rentang yang diberikan (A1:A4). Angka 1 dalam rumus memberi tahu Excel bahwa rata-rata angka yang tercantum harus dihitung.

Dengan fungsi SUBTOTAL, seseorang dapat memasukkan atau mengecualikan nilai baris tersembunyi. Namun, nilai yang difilter (nilai yang disembunyikan oleh filter) dikecualikan, secara default.

Fungsi SUBTOTAL bekerja dengan rentang vertikal (kolom) dari kumpulan data. Itu dapat melakukan 11 operasi aritmatika yang berbeda pada dataset. Fungsi SUBTOTAL dikategorikan dalam fungsi Matematika dan Trigonometri Excel.

SUBTOTAL adalah fungsi serbaguna yang tersedia di semua versi Excel. Dalam versi Excel modern, fungsi AGGREGATE Fungsi AGREGAT Fungsi AGREGAT di excel mengembalikan agregat dari tabel data atau daftar data yang diberikan. Baca lebih lanjut juga tersedia. Ini dapat melakukan lebih banyak operasi dibandingkan dengan fungsi excel SUBTOTAL.

Sintaks SUBTOTAL di Excel

Sintaks fungsi SUBTOTAL Excel ditunjukkan pada gambar berikut:

Fungsi SUBTOTAL menerima argumen berikut:

Function_num: Ini adalah angka yang menentukan operasi aritmatika (fungsi) mana yang akan dilakukan oleh fungsi SUBTOTAL. Argumen ‘function_num’ dapat mengambil nilai apa pun dari 1 hingga 11 atau 101 hingga 111.
Ref1: Ini adalah rentang sel tempat operasi aritmatika akan dilakukan. Itu dapat diberikan sebagai referensi atau sebagai rentang bernama.
Ref2: Ini adalah rentang sel kedua tempat operasi aritmatika akan dilakukan. Ini juga dapat dimasukkan sebagai referensi atau sebagai rentang bernama.

Argumen ‘function_num’ dan ‘ref1’ bersifat wajib, sedangkan ‘ref2’ bersifat opsional. Fungsi SUBTOTAL mengembalikan output numerik.

Catatan: Argumen ‘function_num’ selalu dimasukkan sebagai nilai numerik.

Operasi yang Dilakukan oleh Fungsi SUBTOTAL

Fungsi SUBTOTAL melakukan operasi aritmatika bergantung pada nilai argumen ‘function_num’. Operasi (fungsi) yang dilakukan oleh fungsi SUBTOTAL dan nomor fungsi yang sesuai dicantumkan sebagai berikut:

Fungsi	function_num Termasuk nilai tersembunyi	function_num Tidak termasuk nilai tersembunyi
RATA-RATA	1	101
MENGHITUNG	2	102
COUNTA	3	103
MAKS	4	104
MIN	5	105
PRODUK	6	106
STDEV	7	107
STDEVP	8	108
JUMLAH	9	109
VAR	10	110
VAR	11	111

Harus diperhatikan bahwa untuk setiap operasi (fungsi), ada dua nilai (seperti 1 dan 101 untuk AVERAGE) dari argumen ‘function_num’. Argumen ‘function_num’ dapat mengambil salah satu dari nilai ini tergantung pada apakah baris tersembunyi disertakan atau dikecualikan dari subtotal.

Nilai argumen ‘function_num’ memiliki implikasi sebagai berikut:

Ketika nilai ‘function_num’ antara 1-11, SUBTOTAL menyertakan baris yang disembunyikan secara manual dalam perhitungan.
Ketika nilai ‘function_num’ adalah antara 101-111, SUBTOTAL mengecualikan baris yang disembunyikan secara manual dari perhitungan.

Nilai baris yang disembunyikan oleh filter selalu dikecualikan terlepas dari argumen ‘function_num’.

Catatan: Pengguna tidak perlu menghafal nomor fungsi. Ini karena begitu seseorang mulai mengetikkan rumus SUBTOTAL di sel, Excel menampilkan daftar yang berisi berbagai operasi dan nomor fungsi yang sesuai. Untuk memilih operasi yang akan dilakukan, klik dua kali namanya yang ditampilkan dalam daftar.

Bagaimana Cara Membuka Fungsi SUBTOTAL di Excel?

Untuk membuka fungsi excel SUBTOTAL, masukkan “=SUBTOTAL” pada sel yang diinginkan, diikuti dengan argumen dari fungsi tersebut. Sebagai alternatif, fungsi SUBTOTAL dapat dibuka dari tab Rumus di Excel. Langkah-langkah untuk hal yang sama tercantum sebagai berikut:

Pilih sel yang akan dimasukkan rumus SUBTOTAL.
Dari tab Rumus, klik tarik-turun “Matematika & Trigonometri”. Pilih “SUBTOTAL”, seperti yang ditunjukkan pada gambar berikut.

Kotak dialog “argumen fungsi” terbuka, seperti yang ditunjukkan pada gambar berikut. Masukkan nilai untuk argumen “function_num” dan “ref1”. Setelah kursor ditempatkan di dalam kotak “ref1”, kotak “ref2” muncul di bawahnya.
Klik “Oke” untuk melanjutkan. Output dari fungsi SUBTOTAL akan ditampilkan di sel yang dipilih pada langkah 1.

Bagaimana cara menggunakan Fungsi SUBTOTAL di Excel?

Mari kita perhatikan beberapa contoh untuk memahami cara kerja fungsi SUBTOTAL Excel.

Gambar berikutnya menunjukkan ID dari empat pesanan (di kolom A) yang diterima oleh sebuah organisasi. Sebelum menyelesaikan pesanan ini, organisasi telah menyusun kumpulan data yang berisi informasi berikut:

Jumlah barang yang akan dipasok tercantum dalam kolom B.
Biaya satuan setiap barang diberikan di kolom C.
Total biaya setiap pesanan ditunjukkan pada kolom D.

Tidak ada baris dan filter tersembunyi dalam kumpulan data yang diberikan. Lakukan tugas-tugas berikut:

Terapkan semua 11 operasi fungsi excel SUBTOTAL ke rentang D2:D5. Tutup satu operasi dalam satu contoh. Jadi, harus ada 11 contoh di samping gambar berikutnya.
Di akhir contoh #11, rumus dan hasil dari semua contoh harus dikonsolidasikan di satu tempat.

Mari kita mulai dengan operasi yang berbeda dari fungsi excel SUBTOTAL yang tercakup dalam contoh berikut.

Catatan: Harap abaikan kotak kecil yang berisi tanda tanya (di kolom C dan D) pada gambar dari kesebelas contoh.

Contoh 1

Mari kita terapkan fungsi AVERAGEFungsi AVERAGEFungsi AVERAGE di Excel memberikan rata-rata aritmatika dari kumpulan nilai numerik yang disediakan. Rumus ini dikategorikan sebagai Fungsi Statistik. Rumus rata-rata adalah =AVERAGE(baca lebih lanjut dengan rumus SUBTOTAL. Rumus ini membantu menghitung rata-rata rentang yang ditentukan. Langkah-langkah untuk hal yang sama tercantum sebagai berikut:

Langkah 1: Masukkan rumus berikut di sel F2.

‘=SUBTOTAL(1,D2:D5)’

Catatan: Untuk memilih fungsi nomor 1, klik dua kali ‘1-AVERAGE’ yang muncul dalam daftar operasi aritmatika yang tersedia. Sebagai alternatif, seseorang dapat mengetik ‘1’ (tanpa tanda kutip ganda) secara manual.

Langkah 2: Tekan tombol ‘Enter’. Outputnya adalah 81,75, yang muncul di sel F2. Hal yang sama ditunjukkan pada gambar berikut.

Penjelasan: Argumen pertama dari rumus excel SUBTOTAL yang diberikan (dimasukkan pada langkah 1) adalah 1, yang menyiratkan bahwa rata-rata perlu dihitung. Argumen kedua adalah rentang D2:D5. Jadi, rata-rata dihitung untuk rentang D2:D5. Oleh karena itu, output dari rumus tersebut adalah 81,75.

Karena tidak ada baris tersembunyi, kami menggunakan 1 sebagai nomor fungsi. Seandainya ada baris tersembunyi, kami akan menerapkan rumus ‘=SUBTOTAL(101,D2:D5).’ Rumus ini akan mengecualikan baris tersembunyi dan melakukan operasi tertentu (rata-rata) pada baris yang terlihat dari rentang D2:D5.

Misalnya, jika baris 3 dan 5 disembunyikan, rumus ‘=SUBTOTAL(101,D2:D5)’ akan menghasilkan 65. Ini adalah rata-rata angka 30 dan 100.

Contoh #2

Mari kita terapkan fungsi COUNT Fungsi COUNT Fungsi COUNT di excel menghitung jumlah sel yang berisi nilai numerik dalam rentang yang diberikan. Ini adalah fungsi statistik dan mengembalikan nilai integer. Sintaks rumus COUNT adalah ‘=COUNT(nilai 1, [nilai 2],…)’ baca selengkapnya dengan rumus excel SUBTOTAL. Ini membantu menghitung nilai numerik dari rentang yang ditentukan. Langkah-langkahnya tercantum sebagai berikut:

Langkah 1: Masukkan rumus berikut di sel F2.

‘=SUBTOTAL(2,D2:D5)’

Catatan: Klik dua kali ‘2-COUNT’ dari daftar operasi. Dengan cara ini, fungsi nomor 2 muncul di rumus SUBTOTAL.

Langkah 2: Tekan tombol ‘Enter’. Keluaran 4 muncul di sel F2, seperti yang ditunjukkan pada gambar berikut.

Penjelasan: ‘Function_num’ adalah 2 dan ‘ref1’ adalah D2:D5. Jadi, fungsi COUNT diterapkan pada rentang D2:D5. Fungsi COUNT menghitung jumlah sel yang berisi nilai numerik dalam rentang D2:D5.

Ada empat sel numerik dalam rentang D2:D5. Sel-sel ini adalah D2, D3, D4, dan D5. Oleh karena itu, output dari fungsi SUBTOTAL adalah 4.

Catatan: Fungsi COUNT menghitung sel rentang yang berisi angka (keduanya positif dan negatif), tanggal, waktu, angka desimal, persentase, dan sebagainya. Itu tidak menghitung sel kosong, nilai kesalahan, nilai logika (nilai Boolean benar dan salah), dan string teks.

Contoh #3

Mari kita terapkan fungsi COUNTA Fungsi COUNTAFungsi COUNTA adalah fungsi excel statistik bawaan yang menghitung jumlah sel yang tidak kosong (tidak kosong) dalam rentang sel atau referensi sel. Misalnya, sel A1 dan A3 berisi nilai, tetapi sel A2 kosong. Rumus ‘=COUNTA(A1,A2,A3)’ menghasilkan 2. baca selengkapnya dengan rumus excel SUBTOTAL. Ini membantu menghitung sel yang tidak kosong dari rentang yang ditentukan. Langkah-langkah untuk hal yang sama tercantum sebagai berikut:

Langkah 1: Masukkan rumus berikut di sel F2.

‘=SUBTOTAL(3,D2:D5)’

Catatan: Untuk memasukkan 3 sebagai ‘function_num’ dalam rumus SUBTOTAL, klik dua kali ‘3-COUNTA’ dari daftar yang ditampilkan di Excel.

Langkah 2: Tekan tombol ‘Enter’. Outputnya adalah 4 di sel F2, seperti yang ditunjukkan pada gambar berikut.

Penjelasan: Argumen ‘function_num’ adalah 3 dan argumen ‘ref1’ adalah D2:D5. Rumus SUBTOTAL yang diberikan (dimasukkan pada langkah 1) menghitung sel yang tidak kosong dalam rentang D2:D5.

Sel yang tidak kosong dalam rentang yang diberikan (D2:D5) adalah D2, D3, D4, dan D5. Oleh karena itu, output dari rumus SUBTOTAL adalah 4.

Catatan: Fungsi COUNTA menghitung sel yang berisi string teks, angka, nilai logika (Boolean), nilai tanggal/waktu, nilai kesalahan, dan seterusnya. Hanya sel yang benar-benar kosong yang dikecualikan dari hitungan.

Contoh #4

Mari kita terapkan fungsi MAXFungsi MAXFormula MAX di Excel digunakan untuk menghitung nilai maksimum dari sekumpulan data/array. Ini menghitung angka tetapi mengabaikan sel kosong, teks, nilai logika BENAR dan SALAH, dan nilai teks. Baca selengkapnya dengan rumus excel SUBTOTAL. Ini membantu menemukan nilai terbesar dari rentang yang ditentukan. Langkah-langkah untuk hal yang sama tercantum sebagai berikut:

Langkah 1: Masukkan rumus berikut di sel F2.

‘=SUBTOTAL(4,D2:D5)’

Catatan: Untuk memilih ‘function_num’ 4, klik dua kali ‘4-MAX’ dari daftar yang ditampilkan di Excel. Sebagai alternatif, seseorang dapat mengetik ‘4’ (tanpa tanda kutip ganda) secara manual.

Langkah 2: Tekan tombol ‘Enter’. Keluaran di sel F2 adalah 120. Ini ditunjukkan pada gambar berikut.

Penjelasan: Fungsi MAX mengembalikan nilai numerik terbesar dari serangkaian angka. Dengan rumus SUBTOTAL yang diberikan (dimasukkan pada langkah 1), nilai terbesar dari rentang D2:D5 adalah 120. Oleh karena itu, keluarannya adalah 120.

Jika kita menyembunyikan baris 3 dan 4, rumus ‘=SUBTOTAL(104,D2:D5)’ akan mengembalikan 77. Ini karena 77 lebih besar dari dua nilai yang terlihat (30 dan 77). Jadi, fungsi nomor 104 mengecualikan nilai baris tersembunyi sambil mencari jumlah maksimum dari suatu rentang.

Catatan: Fungsi MAX mempertimbangkan nilai numerik dari kumpulan data. Jika rentang terdiri dari string teks, nilai logika (benar dan salah) atau string kosong, maka akan diabaikan oleh fungsi MAX.

Contoh #5

Mari kita terapkan fungsi MIN dengan rumus excel SUBTOTAL. Ini membantu menemukan nilai terkecil dari rentang yang ditentukan. Langkah-langkahnya tercantum sebagai berikut:

Langkah 1: Masukkan rumus berikut di sel F2.

‘=SUBTOTAL(5,D2:D5)’

Catatan: Klik dua kali ‘5-MIN’ (dalam daftar fungsi) untuk memasukkan 5 sebagai argumen ‘function_num’.

Langkah 2: Tekan tombol ‘Enter’. Keluaran di sel F2 adalah 30, seperti yang ditunjukkan pada gambar berikut.

Penjelasan: Fungsi MIN mengembalikan nilai terkecil dari daftar angka. Di sini, angka terkecil dari rentang D2:D5 adalah 30.

Oleh karena itu, keluaran dari rumus SUBTOTAL (dimasukkan pada langkah 1) adalah 30.

Catatan: Seperti fungsi MAX, MIN juga berfungsi dengan nilai numerik dari suatu rentang. Fungsi MIN mengabaikan string teks, string kosong, dan nilai logika.

Contoh #6

Mari kita terapkan fungsi PRODUCTFungsi PRODUKFungsi excel produk adalah fungsi matematika bawaan yang digunakan untuk menghitung produk atau perkalian dari angka yang diberikan. Jika Anda memberikan argumen rumus ini sebagai 2 dan 3 sebagai =PRODUCT(2,3) maka hasil yang ditampilkan adalah 6.baca lebih lanjut dengan rumus excel SUBTOTAL. Ini membantu melakukan penggandaan rentang angka yang ditentukan. Langkah-langkah untuk hal yang sama tercantum sebagai berikut:

Langkah 1: Masukkan rumus berikut di sel F2.

‘=SUBTOTAL(6,D2:D5)’

Catatan: Untuk memasukkan 6 sebagai argumen ‘function_num’, klik dua kali ‘6-PRODUCT’ dari daftar fungsi.

Langkah 2: Tekan tombol ‘Enter’. Keluarannya adalah 27720000. Karena angka ini cukup besar, kami mempertahankan notasi ilmiahnya di sel F2. Untuk ini, lakukan tugas-tugas berikut:

Pilih sel F2 dan klik kanan.
Pilih ‘format sel’ dari menu konteks.
Di bawah ‘kategori’, pilih ‘ilmiah’. Di ‘tempat desimal’, masukkan ‘1.’
Klik ‘Oke.’

Notasi ilmiah 2.8E+07 muncul di sel F2, seperti yang ditunjukkan pada gambar berikut. Rumus SUBTOTAL juga dapat dilihat di bilah rumus. Keluaran 27720000 dapat dilihat pada gambar di akhir contoh #11, yang menunjukkan hasil konsolidasi.

Catatan 1: Notasi ilmiah menampilkan angka dalam format eksponensial. Di Excel, notasi ilmiah sering digunakan untuk mempersingkat nilai numerik yang besar. Dengan menggunakan notasi ilmiah, tampilan nilai sel berubah. Namun, nilainya sendiri tidak berubah.

Catatan 2: Pratinjau nomor yang diformat dapat dilihat di bawah ‘sampel’ di kotak dialog ‘format sel’.

Penjelasan: Rumus SUBTOTAL (dimasukkan pada langkah 1) melakukan operasi perkalian pada rentang D2:D5. Ini berfungsi sebagai berikut:

30*120*100*77=27720000

Oleh karena itu, keluarannya (27720000) adalah perkalian angka-angka dalam sel D2, D3, D4, dan D5.

Jika kita menyembunyikan baris 3 dan 4, rumus ‘=SUBTOTAL(106,D2:D5)’ akan menghasilkan 2310. Ini adalah produk dari sel D2 dan D5 yang terlihat.

Contoh #7

Mari kita terapkan fungsi STDEV atau STDEV.SFungsi STDEV.SSimpangan standar menunjukkan variabilitas nilai data dari rata-rata (rata-rata). Di Excel, STDEV dan STDEV.S menghitung simpangan baku sampel sementara STDEVP dan STDEV.P menghitung simpangan baku populasi. STDEV tersedia di Excel 2007 dan versi sebelumnya. Namun, STDEV.P dan STDEV.S hanya tersedia di Excel 2010 dan versi selanjutnya. baca selengkapnya dengan rumus excel SUBTOTAL. Ini membantu menghitung standar deviasi populasi berdasarkan sampel data. Langkah-langkah untuk hal yang sama tercantum sebagai berikut:

Langkah 1: Masukkan rumus berikut di sel F2.

‘=SUBTOTAL(7,D2:D5)’

Catatan: Klik dua kali ‘7-STDEV’ atau ‘7-STDEV.S’ untuk memilih fungsi nomor 7.

Langkah 2: Tekan tombol ‘Enter’. Outputnya adalah 38.7158, seperti yang ditunjukkan pada gambar berikut.

Catatan: Populasi merujuk ke seluruh kumpulan data, sedangkan sampel adalah subkumpulan dari kumpulan data ini. Dengan kata lain, sampel mengandung satu atau lebih elemen populasi.

Penjelasan: STDEV dan STDEV.S menghitung standar deviasi dengan mengasumsikan rentang D2:D5 sebagai sampel populasi. Oleh karena itu, standar deviasi sampel adalah 38,7158. Ini adalah output dari rumus SUBTOTAL yang dimasukkan pada langkah 1.

Saat menghitung deviasi standar sampel, ‘n-1’ diambil sebagai penyebut, di mana ‘n’ adalah jumlah nilai dalam kumpulan data.

Standar deviasi yang tinggi (38,7158) menyiratkan bahwa nilai rentang yang diberikan (D2:D5) berfluktuasi dari rata-rata (rata-rata) untuk sebagian besar.

Catatan 1: Standar deviasi menunjukkan dispersi Di dalam statistik, dispersi (atau penyebaran) adalah cara untuk menggambarkan jangkauan distribusi data di sekitar nilai atau titik pusat. Ini membantu dalam memahami distribusi data. Baca lebih lanjut (penyimpangan) nilai data dari rata-rata (rata-rata). Dengan kata lain, dengan standar deviasi, seseorang dapat mengatakan apakah nilai data mendekati atau menyebar dari mean.

Catatan 2: STDEV dan STDEV.S adalah contoh standar deviasi yang membantu membuat kesimpulan yang relevan untuk populasi. STDEV.S adalah versi perbaikan dari fungsi STDEV. STDEV.S tersedia di Excel 2010 dan versi selanjutnya.

Catatan 3: Baik STDEV maupun STDEV.S mengabaikan string teks dan nilai logika dari sampel data.

Contoh #8

Mari kita terapkan fungsi STDEVP atau STDEV.P dengan rumus excel SUBTOTAL. Ini membantu menghitung standar deviasi dari seluruh populasi. Langkah-langkahnya tercantum sebagai berikut:

Langkah 1: Di sel F2, masukkan rumus berikut.

‘=SUBTOTAL(8,D2:D5)’

Catatan: Klik dua kali ‘8-STDEVP’ atau ‘8-STDEV.P’ untuk memasukkan fungsi nomor 8. Sebagai alternatif, seseorang dapat mengetikkan ‘8’ (tanpa tanda kutip ganda) secara manual.

Langkah 2: Tekan tombol ‘Enter’. Output di sel F2 adalah 33,5289. Hal yang sama ditunjukkan pada gambar berikut.

Penjelasan: Fungsi STDEVP dan STDEV.P berasumsi bahwa data yang diberikan sebagai argumen (rentang D2:D5) mewakili seluruh populasi. Oleh karena itu, rumus SUBTOTAL (dimasukkan pada langkah 1) mengembalikan standar deviasi populasi, yaitu 33,5289.

Saat menghitung deviasi standar populasi, ‘n’ diambil sebagai penyebut, di mana ‘n’ adalah jumlah nilai dalam kumpulan data.

Standar deviasi yang tinggi (33,5289) menunjukkan variabilitas yang tinggi dari nilai yang diberikan (kisaran D2:D5) dari rata-rata (rata-rata).

Catatan: Fungsi STDEVP dan STDEV.P mengabaikan string teks dan nilai logika kumpulan data populasi. STDEV.P adalah versi perbaikan dari STDEVP. Fungsi STDEV.P tersedia di Excel 2010 dan versi yang lebih baru.

Contoh #9

Mari kita terapkan fungsi SUMFungsi SUMFungsi SUM di excel menambahkan nilai numerik dalam rentang sel. Dikategorikan di bawah fungsi Matematika dan Trigonometri, dimasukkan dengan mengetikkan ‘=SUM’ diikuti dengan nilai yang akan dijumlahkan. Nilai yang diberikan ke fungsi dapat berupa angka, referensi sel, atau rentang. Baca selengkapnya dengan rumus excel SUBTOTAL. Ini membantu untuk meringkas nilai numerik dari rentang yang ditentukan. Langkah-langkahnya tercantum sebagai berikut:

Langkah 1: Di sel F2, masukkan rumus berikut.

‘=SUBTOTAL(9,D2:D5)’

Catatan: Untuk memasukkan fungsi nomor 9, klik dua kali ‘9-SUM’ yang ditampilkan di daftar fungsi Excel. Cara lainnya, ketik angka ‘9’ (tanpa tanda kutip ganda) secara manual.

Langkah 2: Tekan tombol ‘Enter’. Keluaran di sel F2 adalah 327. Ini ditunjukkan pada gambar berikut.

Penjelasan: Fungsi SUM menjumlahkan nilai rentang D2:D5. Ia bekerja sebagai berikut:

30+120+100+77=327

Oleh karena itu, keluaran rumus SUBTOTAL (dimasukkan pada langkah 1) adalah 327.

Jika kita menyembunyikan baris 3 dan 4, rumus ‘=SUBTOTAL(109,D2:D5)’ akan menghasilkan 107. Penambahan ini hanya mempertimbangkan nilai sel yang terlihat (D2 dan D5).

Catatan: Fungsi SUM mengabaikan sel kosong dan string teks dari kumpulan data.

Contoh #10

Mari kita terapkan fungsi VAR atau VAR.S dengan fungsi excel SUBTOTAL. Ini menghitung varian populasi berdasarkan sampel data. Langkah-langkah untuk hal yang sama tercantum sebagai berikut:

Langkah 1: Masukkan rumus berikut di sel F2.

‘=SUBTOTAL(10,D2:D5)’

Catatan: Untuk memasukkan 10 dalam rumus SUBTOTAL, klik dua kali ’10-VAR’ atau ’10-VAR.S’ dari daftar fungsi.

Langkah 2: Tekan tombol ‘Enter’. Outputnya adalah 1498.92, seperti yang ditunjukkan pada gambar berikut.

Penjelasan: Fungsi VAR dan VAR.S berasumsi bahwa nilai yang diberikan (kisaran D2:D5) sebagai argumen adalah sampel dari populasi. Oleh karena itu, varian sampel yang dikembalikan oleh rumus SUBTOTAL (dimasukkan pada langkah 1) adalah 1498,92.

Karena output (1498.92) besar, ini menunjukkan varian tinggi atau volatilitas tinggi (risiko). Ketika varian tinggi, berikut ini disimpulkan:

Nilai sampel (kisaran D2:D5) yang diberikan jauh dari mean (rata-rata).
Nilai sampel lebih tersebar satu sama lain.

Catatan 1: Variasi sampel adalah kuadrat dari standar deviasi sampel. Ketika variannya nol (output dari rumus SUBTOTAL adalah 0), tidak ada variabilitas dalam nilai kumpulan data. Ini menyiratkan bahwa semua nilai dataset identik.

Catatan 2: VAR dan VAR.S mengabaikan string teks dan nilai logika dari sampel data. VAR.S adalah versi perbaikan dari fungsi VAR. VAR.S tersedia di Excel 2010 dan semua versi berikutnya.

Contoh #11

Mari kita terapkan fungsi VARP atau VAR.P dengan rumus excel SUBTOTAL. Ini membantu menghitung varians dari seluruh populasi. Langkah-langkah untuk hal yang sama tercantum sebagai berikut:

Langkah 1: Di sel F2, masukkan rumus berikut.

‘=SUBTOTAL(11,D2:D5)’

Catatan: Klik dua kali ’11-VARP’ atau ’11-VAR.P’ untuk memasukkan 11 dalam rumus SUBTOTAL. Sebagai alternatif, seseorang dapat mengetik ’11’ (tanpa tanda kutip ganda) secara manual.

Langkah 2: Tekan tombol ‘Enter’. Output muncul di sel F2. Ini adalah 1124.19, seperti yang ditunjukkan pada gambar berikut.

Penjelasan: Fungsi VARP dan VAR.P mengasumsikan bahwa nilai yang diberikan (kisaran D2:D5) mewakili seluruh populasi. Oleh karena itu, keluaran rumus SUBTOTAL (dimasukkan pada langkah 1) adalah variansi populasi. Angka ini adalah 1124,19.

Output (1124.19) besar, yang menyiratkan bahwa nilai seluruh populasi (kisaran D2:D5) tersebar (menyebar). Varians yang tinggi dikaitkan dengan volatilitas (risiko) yang tinggi.

Catatan: Varians populasi adalah kuadrat dari standar deviasi populasi. VAR.P adalah versi VARP yang disempurnakan. VAR.P tersedia di Excel 2010 dan versi selanjutnya. VARP dan VAR.P sama-sama mengabaikan string teks dan nilai logis dari kumpulan data populasi.

Mari kita gabungkan hasil dari contoh sebelumnya (contoh #1 sampai #11) pada gambar berikutnya.

Rumus dan keluaran masing-masing ditampilkan di kolom ‘rumus’ dan ‘hasil’. Contoh nomor (1-11) ditampilkan di kolom pertama berjudul ‘contoh no.’

Properti

Fitur utama dari fungsi excel SUBTOTAL tercantum sebagai berikut:

Properti 1: Fungsi SUBTOTAL, dengan nomor fungsi antara 101-111, tidak berfungsi dengan rentang horizontal. Misalnya, lembar kerja berisi data berikut:

Sel A1 berisi 2

Sel B1 berisi 3

Sel C1 berisi 4

Sel D1 berisi 5

Rumus ‘=SUBTOTAL(106,A1:D1)’ mengembalikan 120. Jika kolom C disembunyikan, rumus ‘=SUBTOTAL(106,A1:D1)’ mengembalikan 120 lagi. Ini menyiratkan bahwa saat melakukan operasi pada rentang horizontal, menyembunyikan kolom tidak memengaruhi fungsi excel SUBTOTAL.

Namun, menyembunyikan baris, saat melakukan operasi pada rentang vertikal, memengaruhi rumus SUBTOTAL. Ini benar ketika nomor fungsi antara 101-111. Dalam hal ini, rumus SUBTOTAL menghitung keluaran dengan mempertimbangkan hanya baris yang terlihat.

Oleh karena itu, ketika nomor fungsi antara 101-111, fungsi SUBTOTAL berfungsi sebagai berikut:

Ini mencakup nilai kolom tersembunyi saat bekerja pada rentang horizontal.
Ini mengecualikan nilai baris tersembunyi saat bekerja pada rentang vertikal.

Properti 2: Fungsi excel SUBTOTAL mengabaikan rumus SUBTOTAL bersarang saat melakukan operasi aritmatika. Hal ini dilakukan untuk mencegah kesalahan yang mungkin terjadi akibat penghitungan ganda.

Catatan 1: Fungsi bersarang adalah fungsi yang ditempatkan di dalam fungsi lain. Fungsi dalam (bersarang) dihitung terlebih dahulu. Hasil dari fungsi dalam menjadi argumen untuk fungsi luar.

Catatan 2: Penghitungan ganda adalah kesalahan dimana angka yang sama dihitung dua kali.

Kesalahan Dikembalikan oleh Fungsi SUBTOTAL

Fungsi excel SUBTOTAL mengembalikan kesalahan berikut:

‘#NILAI!’ kesalahan: Kesalahan ini terjadi karena salah satu alasan berikut:
- Jika argumen function_num bukan nilai yang diperbolehkan (nilai yang diterima adalah bilangan bulat antara 1-11 atau 101-111)
- Jika alamat rentang yang diberikan adalah referensi 3-D
‘#DIV/0!’ kesalahan: Kesalahan ini terjadi karena salah satu alasan berikut:
- Jika perhitungan melibatkan pembagian dengan nol
- Jika suatu fungsi (operasi aritmatika) hanya bekerja dengan angka, tetapi rentang yang ditentukan tidak berisi nilai numerik apa pun
‘#NAMA?’ kesalahan: Kesalahan ini terjadi jika nama fungsi SUBTOTAL salah dieja.

Catatan: Dalam referensi 3-D, rentang (atau sel) yang sama dirujuk di berbagai lembar kerja. Misalnya, referensi Sheet1:Sheet3!A1:A5 adalah referensi 3-D dari rentang A1:A5. Referensi ini melibatkan lembar kerja, ‘Sheet1,’ ‘Sheet2, dan ‘Sheet3.’

Pertanyaan yang Sering Diajukan

Tentukan fungsi SUBTOTAL Excel.

Fungsi SUBTOTAL melakukan operasi aritmatika tertentu (seperti rata-rata, minimum, maksimum, jumlah, produk, dll.) pada rentang sel yang telah ditentukan sebelumnya. Sebanyak 11 operasi berbeda dapat dilakukan oleh fungsi SUBTOTAL. Selanjutnya, fungsi ini bekerja dengan rentang vertikal dari kumpulan data.

Sintaks fungsi SUBTOTAL dinyatakan sebagai berikut:

“SUBTOTAL(function_num,ref1,[ref2],…)”

“Function_num” digunakan untuk menentukan operasi (fungsi ) yang akan dilakukan oleh fungsi SUBTOTAL. “ref1” dan “ref2” masing-masing adalah rentang sel pertama dan kedua tempat operasi akan dilakukan. “Function_num” dan “ref1” adalah argumen wajib, sedangkan “ref2” adalah

argumen opsional . Fungsi nomor 1-11 menyertakan baris yang disembunyikan secara manual, sedangkan 101-111 tidak menyertakan nilai baris tersembunyi. Nilai baris yang disembunyikan oleh filter selalu dikecualikan.

Saat pengguna mulai memasukkan rumus SUBTOTAL di sel, Excel menampilkan daftar nomor fungsi yang tersedia. Seseorang dapat mengklik dua kali nomor fungsi yang diperlukan untuk memilihnya.

Mengapa fungsi SUBTOTAL digunakan di Excel?

Fungsi SUBTOTAL digunakan untuk alasan berikut:

a. Ini menghasilkan hasil yang dinamis dalam kasus data yang difilter. Setelah filter diterapkan, rumus SUBTOTAL (dengan nomor fungsi 101-111) secara otomatis menghitung ulang untuk menyertakan hanya nilai yang terlihat setelah pemfilteran. Jadi, keluaran rumus SUBTOTAL sesuai dengan filter yang diterapkan.
b. Itu mengabaikan nilai baris yang disembunyikan secara manual. Saat satu atau beberapa baris disembunyikan, rumus SUBTOTAL mengecualikan baris tersebut dari perhitungan. Dengan cara ini, operasi aritmatika hanya dapat dilakukan pada data yang relevan.
c. Itu mengabaikan rumus SUBTOTAL bersarang. Jika pengguna telah menerapkan beberapa rumus SUBTOTAL ke rentang yang sama, hanya subtotal terluar yang akan dihitung. Subtotal dalam (bersarang) dikecualikan dari perhitungan. Dengan cara ini, beberapa operasi dapat dilakukan pada rentang yang sama, tanpa mengkhawatirkan penghitungan ganda.
d. Ini membantu melakukan berbagai operasi aritmatika dengan menggunakan satu fungsi. Seandainya fungsi yang ber