Bagaimana cara menggunakan subtotal dengan if di excel?

FORMULA INI ADALAH KEAJAIBAN MURNI.

Jika Anda ingin menulis rumus SUBTOTAL di Excel dengan IF (kondisi), Anda perlu menggunakan beberapa fungsi untuk melakukannya. Namun sebelum kita melakukannya, mari kita pahami data yang kita miliki untuk contoh ini.

Pada contoh di atas, Anda memiliki tiga kolom:

• Nama
• Lempengan usia
• Jenis kelamin

Dan saat Anda memfilter lempengan dari kolom Lempengan Usia, jumlah wanita di sel F1 akan ditampilkan. Artinya kita mempunyai rumus yang menampilkan jumlah nilai yang difilter tetapi dengan syarat.

Rumus yang kami miliki:

 =SUMPRODUCT((C2:C41=E1)*(SUBTOTAL(3,OFFSET(C2,ROW(C2:C41)-MIN(ROW(C2:C41)),0))))

Memahami rumus SUBTOTAL IF

Rumus ini menggunakan lima fungsi: SUMPRODUK, SUBTOTAL, OFFSET, ROW, dan MAX. Oleh karena itu, untuk memahami rumus ini kita perlu membaginya menjadi beberapa bagian.

1.GARIS(C2:C41)-2.0)

Bagian rumus ini menggunakan fungsi MIN dan ROW.

• Di ROW, kami telah mereferensikan kolom “Jenis Kelamin”, dan mengembalikan array nomor baris.
• Setelah itu, MIN mengambil nomor array atau baris tersebut dan mengembalikan nomor baris minimum. Itu sebabnya kita punya 2 di bagian rumus ini.

2.GARIS(C2:C41)

Pada bagian ini, kita hanya memiliki fungsi ROW, yang mengembalikan array nomor baris.

3. OFFSET(C2,GARIS(C2:C41)-MIN(GARIS(C2:C41)),0)

Kami sekarang memiliki fungsi OFFSET. Ini membantu Anda membuat referensi ke suatu rentang menggunakan referensi sel sebagai titik awal. Dalam argumen referensi , kami mengacu pada sel C2, sel pertama tempat rentang gender kami dimulai.

Dalam argumen garis kita memiliki bagian rumus yang dibahas di atas dalam dua bagian pertama. Setelah itu, dalam argumen cols, kami menggunakan 0. Dengan semua ini, OFFSET mengembalikan array dari semua nilai di kolom “Jenis Kelamin”.

4. SUBTOTAL(3,OFFSET(C2,GARIS(C2:C41)-MIN(GARIS(C2:C41)),0))

Kami menggunakan array yang dikembalikan oleh OFFSET di SUBTOTAL. Dan di function_num kita menggunakan 3, yang memberitahu SUBTOTAL untuk menggunakan fungsi COUNTA untuk perhitungan.

Saat Anda menggunakan filter pada kolom “Age Slab”, bagian SUBTOTAL dari rumus ini mengembalikan array yang berisi 0 dan 1.

Dalam tabel ini kita memiliki 1 untuk nilai yang setara dengan nilai yang kita terapkan filternya. Lihat contoh di bawah ini:

5. (C2:C41=E1)

Bagian rumus ini mengembalikan array dengan menguji suatu kondisi. Dalam kondisi ini, kami menguji apakah nilai rentangnya adalah “Female”, dan mengembalikan TRUE dan FALSE dalam array.

Dalam tabel ini, kita memiliki TRUE untuk nilai “Female” dan FALSE untuk nilai lainnya.

7. Bagian terakhir

Pada akhirnya, kita memiliki dua tabel di SUMPRODUK. Dan kami juga memiliki operator tanda bintang di antara array ini.

Saat kita mengalikan dua tabel satu sama lain, kita memiliki satu tabel dengan 0 dan 1. Dalam tabel ini, satu (1) untuk nilai “Perempuan” dalam jenis kelamin dan 21-30 untuk “Umur Slab”.

Pada akhirnya, SUMPRODUK mengembalikan jumlah menggunakan array ini. Dan jumlah ini adalah jumlah sel dengan nilai “Wanita” pada kolom gender saat Anda memfilter 21-30 pada kolom “Rentang Usia”.