Ringkasan pengiraan dalam keadaan penapisan Excel

Artikel ini membawakan anda pengetahuan yang berkaitan tentang excel terutamanya cara menambah nombor siri selepas penapisan, darab selepas penapisan, dan mengira mengikut syarat selepas penapisan harap dapat membantu semua.

Cadangan pembelajaran yang berkaitan: tutorial cemerlang

1. Tambahkan nombor siri selepas menapis

seperti yang ditunjukkan dalam rajah di bawah, Untuk mengekalkan nombor siri berterusan dalam keadaan ditapis, kita boleh membatalkan penapis dahulu, masukkan formula berikut dalam sel D2, dan kemudian tarik ke bawah:

=SUBTOTAL(3,E$1:E2)- 1

Fungsi SUBTOTAL hanya mengira kandungan sel yang boleh dilihat.

Parameter pertama menggunakan 3, yang menunjukkan peraturan pengiraan untuk melaksanakan fungsi COUNTA, iaitu mengira bilangan sel yang boleh dilihat untuk parameter kedua.

Parameter kedua menggunakan julat yang dikembangkan secara dinamik E$1:E2 Apabila formula ditarik ke bawah, julat ini akan menjadi E$1:E3, E$1:E4, E$1:E5,...<.>

Formula sentiasa mengira bilangan sel tidak kosong yang kelihatan dalam kawasan dari baris pertama lajur E hingga baris di mana formula terletak. Tolak 1 daripada hasil, dan hasil yang dikira akan sama dengan nombor siri, dan ia akan kekal berterusan selepas penapisan.

Perhatian, sila ambil perhatian bahawa jika formula ini ditukar kepada =SUBTOTAL(3,E$2:E2), iaitu, bermula dari baris di mana formula terletak, hasil nombor siri akan baik, tetapi baris terakhir akan ditapis oleh Excel Sentiasa dipaparkan sebagai baris ringkasan.

2. Darab selepas menapis

Seperti yang ditunjukkan dalam rajah di bawah, selepas menapis lajur E, anda perlu mengira jumlah jumlah didarab dengan harga unit.

Formula dalam sel E2 ialah:

=SUMPRODUCT(SUBJUMLAH(3,OFFSET(E3,ROW(1:13),))*F4:F16*G4:G16)

Untuk mengira produk yang ditapis, kunci kepada masalah adalah untuk menentukan sama ada data boleh dilihat.

Bagaimana untuk menentukan status yang boleh dilihat ini?

Perlu menggabungkan fungsi OFFSET dan SUBTOTAL.

Mula-mula gunakan fungsi OFFSET, mengambil sel E3 sebagai titik asas, dan mengimbangi baris 1 hingga 13 ke bawah untuk mendapatkan rujukan berbilang dimensi. Rujukan berbilang dimensi ini mengandungi 13 kawasan rujukan dengan satu baris dan satu lajur, iaitu sel individu dari E4 hingga E16 dirujuk masing-masing.

Seterusnya gunakan fungsi SUBTOTAL Gunakan 3 sebagai parameter pertama, iaitu, kira bilangan sel yang kelihatan dalam setiap sel dari E4 hingga E16 keputusan ialah 1, jika tidak, keputusan statistik ialah 0. Dapatkan tatasusunan memori dengan kesan berikut:

{1;0;1;1;1;1;0;0;1;1;0;1;0}

Kemudian gunakan di atas Hasilnya didarab dengan kuantiti dalam lajur F dan harga unit dalam lajur G. Jika sel berada dalam keadaan paparan, ia bersamaan dengan 1*kuantiti*harga unit, jika tidak ia bersamaan dengan 0*kuantiti*unit harga.

Akhir sekali, gunakan fungsi SUMPRODUCT untuk menjumlahkan produk.

3. Kira mengikut syarat selepas tapisan

Seperti gambar di bawah, selepas menapis jabatan di lajur E, kira bilangan orang yang berkhidmat melebihi 3 tahun.

Formula dalam sel E2 ialah:

=SUMPRODUCT(SUBJUMLAH(3,OFFSET(E3,ROW(1:13),))*(G4:G16>3))

Separuh pertama prinsip pengiraan adalah sama seperti contoh sebelumnya dan intinya adalah untuk menentukan sama ada sel itu boleh dilihat.

Syarat statistik dalam separuh kedua formula (G4:G16>3) didarab dengan keputusan penghakiman pada separuh pertama, menunjukkan bahawa kedua-dua syarat dipenuhi pada masa yang sama, iaitu bilangan daripada item dalam keadaan kelihatan dan lajur G adalah lebih besar daripada 3 .

4 Secara automatik betulkan tajuk selepas menapis

Seperti yang ditunjukkan dalam rajah di bawah, selepas menapis nama jabatan dalam lajur E, anda mahu tajuk sel D1 bertukar secara automatik kepada jabatan yang sepadan nama. Formulanya ialah:

=LOOKUP(1,0/SUBJUMLAH(3,OFFSET(D1,ROW(1:15)-1,)),E:E)&”Jadual Statistik”

Gabungan fungsi SUBTOTAL dan OFFSET, tujuannya masih untuk menentukan sama ada sel dalam lajur D kelihatan. Dapatkan tatasusunan memori yang terdiri daripada 0 dan 1:

{0;1;0;0;0;0;1;1;1;1;0;1;0;1;0}

Gunakan tatasusunan memori 0/ untuk mendapatkan tatasusunan memori baharu yang terdiri daripada 0 dan nilai ralat:

{#DIV/0!;0;#DIV/0!…;0;0;0 ;0;#DIV/0!;0;#DIV/0!;0;#DIV/0!}

Fungsi LOOKUP menggunakan 1 sebagai nilai pertanyaan untuk mencari kedudukan 0 terakhir dalam di atas tatasusunan memori, dan mengembalikan kandungan lajur E pada kedudukan yang sepadan.

Matlamat utama adalah untuk mengekstrak kandungan sel terakhir yang dipaparkan selepas penapisan.

Sambungkan kandungan yang diekstrak dengan &"Jadual Statistik" untuk mengubahnya menjadi tajuk jadual yang dikemas kini secara automatik.

Cadangan pembelajaran berkaitan:

tutorial cemerlang

Atas ialah kandungan terperinci Ringkasan pengiraan dalam keadaan penapisan Excel. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!