Cara menggunakan fungsi SUBTOTAL Excel -

Ringkasan

Fungsi SUBTOTAL Excel mengembalikan hasil agregat untuk nilai yang diberikan. SUBTOTAL dapat mengembalikan SUM, AVERAGE, COUNT, MAX, dan lainnya (lihat tabel di bawah), dan fungsi SUBTOTAL dapat menyertakan atau mengecualikan nilai dalam baris tersembunyi.

Tujuan

Dapatkan subtotal dalam daftar atau database

Nilai kembali

Angka yang mewakili jenis subtotal tertentu

Sintaksis

= SUBTOTAL (function_num, ref1, (ref2),…)

Argumen

  • function_num - Angka yang menentukan fungsi mana yang akan digunakan dalam menghitung subtotal dalam daftar. Lihat tabel di bawah untuk daftar lengkapnya.
  • ref1 - Rentang bernama atau referensi ke subtotal.
  • ref2 - (opsional) Rentang bernama atau referensi ke subtotal.

Versi: kapan

Excel 2003

Catatan penggunaan

Gunakan fungsi SUBTOTAL untuk mendapatkan subtotal dalam daftar atau database. Terlepas dari namanya, SUBTOTAL memiliki kemampuan untuk melakukan berbagai fungsi matematika, termasuk AVERAGE, COUNT, MAX, dan banyak lainnya (lihat tabel di bawah untuk daftar lengkapnya). Secara default, SUBTOTAL mengecualikan nilai dalam baris yang disembunyikan oleh filter, yang membuat SUBTOTAL sangat berguna di Tabel Excel.

Fungsi SUBTOTAL secara otomatis mengabaikan rumus SUBTOTAL lain yang ada dalam referensi untuk mencegah penghitungan ganda.

Contoh

Berikut adalah beberapa contoh SUBTOTAL yang dikonfigurasi ke SUM, COUNT, dan AVERAGE nilai dalam suatu rentang. Perhatikan satu-satunya perbedaan adalah nilai yang digunakan untuk argumen function_num :

=SUBTOTAL(109,range) // SUM =SUBTOTAL(103,range) // COUNT =SUBTOTAL(101,range) // AVERAGE

Perhitungan yang tersedia

Perilaku SUBTOTAL dikontrol oleh argumen function_num , yang disediakan sebagai nilai numerik. Ada 11 fungsi yang tersedia, masing-masing dengan dua opsi, seperti yang terlihat pada tabel di bawah. Perhatikan nilai-nilai tersebut "berpasangan" (misalnya 1-101, 2-102, 3-103, dan seterusnya). Ini terkait dengan cara SUBTOTAL menangani baris yang disembunyikan secara manual. Saat function_num antara 1-11, SUBTOTAL menyertakan sel yang telah disembunyikan secara manual. Jika function_num antara 101-111, SUBTOTAL mengecualikan nilai dalam baris yang telah disembunyikan secara manual.

Fungsi Sertakan tersembunyi Abaikan yang 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
VARP 11 111

Catatan: SUBTOTAL selalu mengabaikan nilai dalam sel yang disembunyikan dengan filter. Nilai dalam baris yang telah "difilter" tidak pernah disertakan, terlepas dari function_num .

SUBTOTAL di Tabel Excel

Fungsi SUBTOTAL digunakan saat Anda menampilkan baris Total dalam Tabel Excel. Excel menyisipkan fungsi SUBTOTAL secara otomatis, dan Anda dapat menggunakan menu drop-down untuk beralih perilaku dan menampilkan max, min, average, dll. Alasan Excel menggunakan SUBTOTAL untuk kalkulasi di baris Total Tabel Excel adalah karena SUBTOTAL secara otomatis mengecualikan baris disembunyikan oleh kontrol filter di bagian atas tabel. Artinya, saat Anda memfilter baris dalam tabel dengan baris Total, Anda akan melihat penghitungan diperbarui secara otomatis untuk mengikuti filter.

SUBTOTAL dengan garis besar

Excel memiliki fitur Subtotal yang secara otomatis memasukkan rumus SUBTOTAL dalam data yang diurutkan. Anda dapat menemukan fitur ini di Data> Outline> Subtotal. Rumus SUBTOTAL yang disisipkan dengan cara ini menggunakan nomor fungsi standar 1-11. Hal ini memungkinkan hasil subtotal tetap terlihat meskipun baris disembunyikan dan ditampilkan saat kerangka diciutkan dan diperluas.

Catatan: meskipun fitur Garis adalah cara "mudah" untuk menyisipkan subtotal dalam sekumpulan data, Tabel Pivot adalah cara yang lebih baik dan lebih fleksibel untuk menganalisis data. Selain itu, Tabel Pivot akan memisahkan data dari penyajian data, yang merupakan praktik terbaik.

Catatan

  • Ketika function_num antara 1-11, SUBTOTAL menyertakan nilai yang disembunyikan
  • Ketika function_num antara 101-111, SUBTOTAL mengecualikan nilai yang disembunyikan
  • Dalam daftar yang difilter, SUBTOTAL selalu mengabaikan nilai di baris tersembunyi, terlepas dari function_num .
  • SUBTOTAL mengabaikan rumus SUBTOTAL lain yang ada di referensi untuk mencegah penghitungan ganda.
  • SUBTOTAL dirancang untuk bekerja dengan nilai data vertikal yang disusun secara vertikal. Dalam rentang horizontal, nilai di kolom tersembunyi selalu disertakan.

Video Terkait

Bagaimana menambahkan baris total ke Tabel Dalam video ini, kita akan melihat bagaimana menambahkan dan mengkonfigurasi Baris Total ke Tabel Excel. Baris Total bisa secara otomatis memperlihatkan penghitungan ringkasan di bawah tabel. Cara menghitung item dalam daftar yang difilter Dalam video ini, kami menunjukkan kepada Anda cara menambahkan pesan di bagian atas daftar yang difilter yang menampilkan item total dan terlihat dalam daftar. Rumus untuk membuat kueri tabel Karena tabel mendukung referensi terstruktur, Anda bisa belajar banyak tentang tabel dengan rumus sederhana. Dalam video ini, kita akan melihat beberapa rumus yang dapat Anda gunakan untuk membuat kueri tabel.

Artikel yang menarik...