Subtotal google sheets berdasarkan kategori

Saat bekerja di Google Sheets, Anda mungkin ingin memeriksa subtotal kumpulan data Anda secara berkala atau menyertakan hasilnya dalam dokumentasi akhir

Fungsi SUBTOTAL adalah cara termudah untuk melakukannya. Selain menghitung subtotal untuk daftar data, fungsi subtotal juga

• Menghitung metrik dengan baris data tersembunyi atau terfilter
• Secara dinamis memilih fungsi

Ini adalah fungsi yang kurang dimanfaatkan karena sifatnya yang rumit. Ini tidak terlalu ramah bagi pemula karena harus menggunakan kontrol fungsi untuk beroperasi. Namun, jika Anda mengikuti panduan ini, Anda akan lancar bekerja dengan fungsi SUBTOTAL dalam waktu singkat

Artikel ini Meliputi

Apa itu Kontrol Fungsi?

Kontrol fungsi memberi tahu fungsi tertentu di Google Spreadsheet cara berperilaku. Dalam kasus fungsi SUBTOTAL, Anda mungkin ingin mencari SUM, AVERAGE, MAX, dll

Menggunakan kode kontrol fungsi dalam sintaks memberitahu fungsi SUBTOTAL perhitungan mana yang harus digunakan

Ini mungkin terdengar sedikit membingungkan, tetapi lembar contekan di bawah ini akan membantu Anda. Anda dapat memeriksanya kembali saat mengerjakan spreadsheet. Tidak perlu langsung menghafal kontrol fungsi

Anda bahkan dapat membuat  agar tidak perlu menghafal setiap kode

Kontrol Fungsi SUBTOTAL

Formula yang digunakan

Kode (termasuk nilai tersembunyi)

Kode (tidak termasuk nilai tersembunyi)

Rata-rata

1

101

Menghitung

2

102

Hitungan

3

103

Maks

4

104

Min

5

105

Produk

6

106

Deviasi Standar

7

107

Populasi Standar Deviasi

8

108

Jumlah

9

109

Perbedaan

10

110

Populasi Varians

11

111

Seperti yang Anda lihat di tabel, ada sebelas kontrol fungsi untuk fungsi SUBTOTAL di Google Sheets. Setiap kontrol fungsi ini dapat menyertakan atau mengecualikan nilai tersembunyi

Kontrol fungsi satu digit atau dua digit menyertakan nilai tersembunyi. Tiga digit dalam argumen kontrol fungsi berarti nilai tersembunyi dikecualikan

Mari kita lihat bagaimana kontrol fungsi sesuai dengan sintaks

Sintaks SUBTOTAL

=SUBTOTAL(function_code, range1, [range2, ...])

Anda memerlukan minimal dua argumen agar fungsi SUBTOTAL berfungsi. Yang pertama adalah salah satu kontrol fungsi yang tercantum di atas dan yang kedua adalah rentang data

Namun, Anda dapat menambahkan rentang data sebanyak yang Anda inginkan ke fungsi ini

Mari gunakan argumen berikut dalam sintaksis sebagai contoh

=SUBTOTAL(9, B2:B7)

Karena argumen pertama dalam fungsi SUBTOTAL adalah sebuah kontrol, argumen ini memberitahu fungsi untuk menerapkan agregasi yang diinginkan. Dalam contoh ini, 9 adalah kontrol fungsi yang dipilih

Jika Anda melihat tabel di atas, Anda dapat melihat bahwa menggunakan 9 dalam sintaks akan menerapkan fungsi SUM ke rentang B2 yang diberikan. B7, termasuk nilai tersembunyi apa pun

Untuk mengecualikan nilai tersembunyi apa pun, Anda harus menggunakan kontrol fungsi tiga digit yang setara dengan 109

Di tangkapan layar berikutnya, Anda dapat melihat kami menambahkan kumpulan data kedua ke subtotal

Ini akan mencakup kumpulan data dari A2. A7 dalam perhitungan juga

Perhatikan bahwa tanda kurung siku “[“ yang diberikan dalam contoh sintaks tidak diperlukan dan akan menyebabkan kesalahan jika dimasukkan ke dalam rumus. Mereka hanya menandakan awal dan akhir rentang yang bisa Anda masukkan dalam sintaksis dan sebenarnya bukan bagian dari perhitungan

Mengapa Anda Harus Menggunakan Fungsi SUBTOTAL

Tampaknya lebih mudah untuk menggunakan fungsi bawaan standar untuk setiap penghitungan subtotal. Lagi pula, dalam contoh pertama kami, Anda dapat dengan mudah menggunakan sintaks =SUM, (B2. B7) dan masih menerima jawaban yang benar

Meskipun ini benar, spreadsheet yang lebih komplekslah yang akan mendapat manfaat dari penggunaan fungsi SUBTOTAL

Dalam contoh berikut, fungsi SUM telah digunakan untuk menghitung subtotal dan total keseluruhan pada spreadsheet

Anda dapat melihat bahwa subtotalnya benar menggunakan fungsi SUM, tetapi total keseluruhannya salah. Ini karena menghitung angka subtotal serta angka lain di kolom yang membuat subtotal

Anda dapat menghindari ini dengan menggunakan sintaks untuk fungsi SUM =SUM(C9+C18+C27) untuk mendapatkan jawaban yang benar. Namun, ini jauh lebih lambat dan membosankan daripada menggunakan fungsi SUBTOTAL. Juga jauh lebih mudah untuk membuat kesalahan dengan mengklik atau mengetik setiap bidang secara manual

Berikut tampilannya menggunakan fungsi SUBTOTAL untuk kumpulan data yang sama

Anda dapat melihat bahwa total keseluruhan sekarang memiliki jawaban yang benar yaitu 70. Ini karena fungsi SUBTOTAL mengabaikan data duplikat sedangkan fungsi SUM tidak. Itu dilakukan dengan mengabaikan setiap fungsi SUBTOTAL lainnya dalam rentang yang diberikan

Konsep ini berlaku untuk setiap kontrol fungsi sebanding yang digunakan oleh fungsi SUBTOTAL, bukan hanya SUM

Interaksi Fungsi SUBTOTAL dengan Data yang Difilter dan Tersembunyi

Perhatikan kumpulan data berikut ini

Di dalamnya, kami telah menggunakan rumus berikut di setiap sel ini

• C15 = JUMLAH(C2. C13)
• C16 = SUBTOTAL(9,C2. C13)
• C17 = SUBTOTAL(109,C2. C13)

Seperti yang Anda lihat, masing-masing rumus ini memberikan jawaban benar yang sama untuk jumlah total penjualan. Namun, saat Anda mulai memfilter atau menyembunyikan data, setiap formula akan berinteraksi secara berbeda dengan spreadsheet

Data yang Difilter

Dalam contoh berikutnya, kami telah memfilter menurut merek untuk hanya menampilkan satu penjualan

Anda dapat melihat bahwa kedua baris SUBTOTAL telah menghapus data yang difilter dari penghitungannya, tetapi fungsi SUM tidak. Konsep yang sama ini akan berlaku jika kita memfilter kolom tahun. Ini akan memberikan subtotal berdasarkan tanggal sebagai gantinya

Jadi, pastikan Anda memilih fungsi yang benar, apakah Anda ingin menyertakan data yang difilter atau tidak dalam total spreadsheet Anda

Data Tersembunyi

Untuk contoh terakhir, kami juga menambahkan baris tersembunyi ke kumpulan data. Perhatikan bahwa angka di sel C17 berubah. Ini karena kami menggunakan kontrol fungsi 109, yang mengabaikan baris tersembunyi. Hal yang sama berlaku untuk kolom tersembunyi

Pastikan Anda memilih kontrol fungsi dan subtotal yang tepat dengan parameter kondisi untuk diterapkan ke sasaran akhir spreadsheet Anda

Kesimpulan

Sepintas, fungsi SUBTOTAL bisa sedikit mengintimidasi. Namun, begitu Anda memahami kontrol fungsi, Anda akan menghemat banyak waktu saat mengerjakan spreadsheet

Jika Anda merasa tutorial ini tentang cara menghitung subtotal unik di Google Sheets bermanfaat, Anda mungkin juga tertarik mempelajari tentang menjalankan total

Christopher Daniel

+ postingan

Chris adalah ahli spreadsheet dan penulis konten untuk Productivity Spot dan Spreadsheet Point. Dia memiliki gelar Sarjana Pengajaran ganda dan telah bekerja di industri pendidikan selama lebih dari 11 tahun. Pengalamannya membuatnya mahir mengurai topik-topik rumit agar semua orang mampu memahaminya

Bagaimana Anda menghitung berdasarkan kategori di Google Sheets?

Bagaimana Anda subtotal berdasarkan kategori?

Apakah ada fungsi subtotal jika di Google Sheets?