Loncat ke daftar isi utama

Bagaimana menjumlahkan hanya sel yang disaring atau terlihat di Excel?

Mungkin mudah bagi Anda untuk menjumlahkan kolom angka di Excel, tetapi terkadang Anda perlu memfilter atau menyembunyikan beberapa data untuk memenuhi kriteria Anda. Setelah bersembunyi atau memfilter, dan sekarang Anda hanya ingin menambahkan hanya nilai yang difilter atau terlihat. Jika Anda menerapkan fungsi Sum di Excel, semua nilai termasuk data tersembunyi akan ditambahkan, dalam hal ini, bagaimana Anda bisa menjumlahkan hanya nilai sel yang difilter atau terlihat di Excel?


Jumlahkan hanya nilai sel yang difilter atau terlihat dengan rumus

Dengan ini SUBTOTAL fungsi yang mengabaikan baris yang telah dikecualikan oleh filter, Anda dapat dengan mudah menambahkan hanya sel yang terlihat. Anda dapat melakukan seperti ini:

Misalkan Anda memiliki berbagai data, dan mereka telah difilter sesuai kebutuhan, lihat tangkapan layar:

1. Di sel kosong, C13 misalnya, masukkan rumus ini: = Subtotal (109, C2: C12) (109 menunjukkan saat Anda menjumlahkan angkanya, nilai tersembunyi akan diabaikan; C2: C12 adalah kisaran yang akan Anda jumlahkan dengan mengabaikan baris yang difilter.), dan tekan Enter kunci.

Catatan: Rumus ini juga bisa membantu Anda menjumlahkan hanya sel yang terlihat jika ada baris tersembunyi di lembar kerja Anda. Namun, rumus ini tidak bisa dijumlahkan dengan mengabaikan sel di kolom tersembunyi.

Jumlah / Hitung / Rata-rata sel yang terlihat hanya dalam kisaran tertentu dengan mengabaikan sel / baris / kolom yang disembunyikan atau difilter

Fungsi SUM / Count / Average biasanya akan menghitung semua sel dalam kisaran yang ditentukan pada sel materi disembunyikan / disaring atau tidak. Sedangkan fungsi Subtotal hanya dapat menjumlahkan / menghitung / rata-rata dengan mengabaikan baris tersembunyi. Namun, Kutools for Excel RINGKASAN / COUNTVISABLE / RATA-RATA fungsi akan dengan mudah menghitung rentang yang ditentukan dengan mengabaikan sel, baris, atau kolom tersembunyi.


jumlah iklan rata-rata hanya sel yang terlihat

Jumlahkan hanya nilai sel yang disaring atau terlihat dengan User Defined Function

Jika Anda tertarik dengan kode berikut, ini juga dapat membantu Anda untuk menjumlahkan hanya sel yang terlihat.

1. Tahan ALT + F11 kunci, dan itu membuka Microsoft Visual Basic untuk Aplikasi jendela.

2. Klik Menyisipkan > Modul, dan tempel kode berikut di jendela modul.

Function SumVisible(WorkRng As Range) As Double
'Update 20130907
Dim rng As Range
Dim total As Double
For Each rng In WorkRng
    If rng.Rows.Hidden = False And rng.Columns.Hidden = False Then
        total = total + rng.Value
    End If
Next
SumVisible = total
End Function

3. Simpan kode ini dan masukkan rumusnya = SumVisible (C2: C12) ke dalam sel kosong. Lihat tangkapan layar:

4. Lalu tekan Enter kunci dan Anda akan mendapatkan hasil yang Anda inginkan.


Jumlah / Hitung / Rata-rata hanya sel yang disaring atau terlihat dengan Kutools for Excel

Jika Anda memasang Kutools for Excel, Anda dapat dengan cepat menghitung jumlah / hitungan / rata-rata hanya sel yang terlihat atau disaring di Excel dengan mudah.

Kutools untuk Excel - Tingkatkan Excel dengan lebih dari 300 alat penting. Nikmati uji coba GRATIS 30 hari berfitur lengkap tanpa memerlukan kartu kredit! Get It Now

Misalnya Anda ingin menjumlahkan hanya sel yang terlihat saja, pilih sel yang akan Anda tempatkan hasil penjumlahannya, ketikkan rumusnya = RINGKASAN (C3: C12) (C3: C13 adalah rentang di mana Anda hanya akan menjumlahkan sel yang terlihat) ke dalamnya, dan tekan Enter kunci.

Dan kemudian hasil penjumlahan dihitung dengan mengabaikan semua sel tersembunyi. Lihat tangkapan layar:

Untuk menghitung hanya sel yang terlihat, harap terapkan rumus ini = COUNTVISIBLE (C3: C12); Untuk rata-rata hanya sel yang terlihat, terapkan rumus ini = AVERAGEVISIBLE (C3: C12).

Catatan: Jika Anda tidak dapat mengingat rumus dengan tepat, Anda dapat mengikuti langkah-langkah di bawah ini untuk menjumlahkan / menghitung / rata-rata hanya sel yang terlihat dengan mudah:

1. Pilih sel tempat Anda akan memasukkan hasil penjumlahan, dan klik Kutools > Fungsi > Statistik & Matematika > RINGKASAN (Atau RATA-RATA, COUNTVISABLE sesuai kebutuhan). Lihat tangkapan layar:

2. Pada kotak dialog Function Arguments pembuka, tentukan kisaran di mana Anda akan menjumlahkan sel tersembunyi yang diabaikan, dan klik OK tombol. Lihat tangkapan layar:

Kutools untuk Excel - Tingkatkan Excel dengan lebih dari 300 alat penting. Nikmati uji coba GRATIS 30 hari berfitur lengkap tanpa memerlukan kartu kredit! Get It Now

Dan kemudian hasil penjumlahan dihitung dengan mengabaikan semua sel tersembunyi.


Demo: Jumlah / Hitung / Rata-rata hanya sel yang disaring atau terlihat


Kutools untuk Excel: Lebih dari 300 alat praktis di ujung jari Anda! Mulai uji coba gratis 30 hari Anda tanpa batasan fitur hari ini. Download Sekarang!

Mudah menjumlahkan / menghitung hanya sel yang disaring / terlihat dengan menghapus baris tersembunyi di Excel

Saat menjumlahkan / menghitung sel yang difilter di Excel, fungsi SUM atau fungsi Hitung tidak akan mengabaikan sel tersembunyi. Jika baris tersembunyi / tersaring dihapus, kami hanya dapat menjumlahkan atau menghitung sel yang terlihat dengan mudah. Anda bisa mencoba Kutools for Excel's Hapus Baris & Kolom Tersembunyi (Terlihat) utilitas untuk mengatasinya.


iklan hapus kolom baris tersembunyi 3

Artikel terkait:

Alat Produktivitas Kantor Terbaik

馃 Kutools AI Ajudan: Merevolusi analisis data berdasarkan: Eksekusi Cerdas   |  Hasilkan Kode  |  Buat Rumus Khusus  |  Analisis Data dan Hasilkan Grafik  |  Aktifkan Fungsi Kutools...
Fitur Populer: Temukan, Sorot, atau Identifikasi Duplikat   |  Hapus Baris Kosong   |  Gabungkan Kolom atau Sel tanpa Kehilangan Data   |   Putaran tanpa Formula ...
Pencarian Super: VLookup Beberapa Kriteria    VLookup Nilai Berganda  |   VLookup di Beberapa Lembar   |   Pencarian Fuzzy ....
Daftar Drop-down Lanjutan: Buat Daftar Drop Down dengan Cepat   |  Daftar Drop Down yang Bergantung   |  Multi-pilih Drop Down List ....
Manajer Kolom: Tambahkan Jumlah Kolom Tertentu  |  Pindahkan Kolom  |  Alihkan Status Visibilitas Kolom Tersembunyi  |  Bandingkan Rentang & Kolom ...
Fitur Unggulan: Fokus Kisi   |  Tampilan Desain   |   Bar Formula Besar    Manajer Buku Kerja & Lembar   |  Perpustakaan Sumberdaya (Teks otomatis)   |  Pemetik tanggal   |  Gabungkan Lembar Kerja   |  Enkripsi/Dekripsi Sel    Kirim Email berdasarkan Daftar   |  Filter Super   |   Filter Khusus (filter tebal/miring/coret...) ...
15 Perangkat Teratas12 Teks Tools (Tambahkan Teks, Hapus Karakter, ...)   |   50 + Grafik jenis (Gantt Chart, ...)   |   40+ Praktis Rumus (Hitung usia berdasarkan ulang tahun, ...)   |   19 Insersi Tools (Masukkan Kode QR, Sisipkan Gambar dari Jalur, ...)   |   12 Konversi Tools (Angka ke Kata, Konversi Mata Uang, ...)   |   7 Gabungkan & Pisahkan Tools (Lanjutan Gabungkan Baris, Pisahkan Sel, ...)   |   ... dan banyak lagi

Tingkatkan Keterampilan Excel Anda dengan Kutools for Excel, dan Rasakan Efisiensi yang Belum Pernah Ada Sebelumnya. Kutools for Excel Menawarkan Lebih dari 300 Fitur Lanjutan untuk Meningkatkan Produktivitas dan Menghemat Waktu.  Klik Di Sini untuk Mendapatkan Fitur yang Paling Anda Butuhkan...

Deskripsi Produk


Tab Office Membawa antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Jauh Lebih Mudah

  • Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
  • Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
  • Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!
Comments (26)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Bonjour,
Moi j'ai un probl猫me comparable mais diff茅rent, en gros quand je filtre mes colonnes et que je veux copier la m锚me valeur dans mes lignes visibles, toute les lignes qui sont entre mes lignes visibles sont elles aussi modifi茅es.
Comment faire pour que seule mes lignes visibles soient modifi茅s.
This comment was minimized by the moderator on the site
Hello, Loux

Do you mean to copy and paste cell vaues to visible cells only? If so, maybe the below articles can help you:
https://www.extendoffice.com/documents/excel/2331-excel-paste-data-into-filtered-list.html
https://www.extendoffice.com/documents/excel/2617-excel-paste-to-visible-filtered-cells.html

Please try it, thank you!
This comment was minimized by the moderator on the site
Hola,

No me resulta sumar el rango de una fila considerando s贸lo las columnas visibles.

Alguna formula o alguna macro?

Gracias!!!
This comment was minimized by the moderator on the site
Hello, Cristobal,
Sorry, I can't understand your problem clearly, could you explain your problem in English?
Or you can insert a screenshot or a file to describe your problem.
Thank you!
This comment was minimized by the moderator on the site
Hola,

Quiero sumar dentro de un rango de fila que al ocultar columnas sume s贸lo las visibles.
No he logrado hacerlo, alguna f贸rmula? o tendr铆a que hacerse con una marcro?

Muchas gracias!!!
This comment was minimized by the moderator on the site
how to skip hidden cells from excel formulae in filtered sheet....

yellow coloured cells are involving hidden cells due to filter....
This comment was minimized by the moderator on the site
Hi,
What kinds of calculation do you need to do? Kutools for Excel supports three functions to count/sum/average ignoring all hidden cells.
This comment was minimized by the moderator on the site
or like this



column a column d

100 10

90 10

80 10



where 90=100-10, 80=90-10, and so on.........

dragging the formula includes hidden cells in formula
This comment was minimized by the moderator on the site
When i am applying say G3-K2 Formula in filtered excel sheet & dragging the formula, it includes hidden cells

for example



Validity Lifting Qty
27054.59 123.41
26931.18 330.98
26600.20 493.66
26106.54 476.38
25630.16 480.64
25149.52 577.06


now value in validity comes after get subtracted by lifted qty (like 2nd value 26931.18=27054.59-123.41)

3rd value in validity=2nd value in validity-2nd value in lifted qty. and so on.....

now this sheet is filtered & when i drag the formulae in validity column, it includes hidden cells, due to filter.

which gives me wrong result
This comment was minimized by the moderator on the site
Validity Lifting Qty
27054.59 123.41
26931.18 330.98
26600.20 (26600.2=26931.18-330.98) 493.66
26106.54 476.38
25630.16 480.64
25149.52 577.06
This comment was minimized by the moderator on the site
When i am applying say G3-K2 Formula in filtered excel sheet & dragging the formula, it includes hidden cells

for example



Validity Lifting Qty
27054.59 123.41
26931.18 330.98
26600.20 493.66
26106.54 476.38
25630.16 480.64
25149.52 577.06


now value in validity comes after get subtracted by lifted qty (like 2nd value 26931.18=27054.59-123.41)

3rd value in validity=2nd value in validity-2nd value in lifted qty. and so on.....

now this sheet is filtered & when i drag the formulae in validity column, it includes hidden cells, due to filter.

which gives me wrong result
This comment was minimized by the moderator on the site
Hi,
The normal =G3-K2 will not ignore any hidden cells/rows/column by dragging to copy. I am sorry I can not figure out a proper formula for your. All formulas or methods talked in this article is just about sum/count/average ignoring hidden cells.
This comment was minimized by the moderator on the site
how i can skip hidden cells from excel formulae in filtered sheet
This comment was minimized by the moderator on the site
Im trying to use the AVERAGEVISIBLE function to average the 12 largest values in a column, this works in the normal AVERAGE function as =AVERAGE(LARGE(E971:E1540,{1,2,3,4,5,6,7,8,9,10,11,12})) however when i try to use the average visible function it returns #VALUE!, any ideas?
This comment was minimized by the moderator on the site
This is awesome! Thanks so much.
This comment was minimized by the moderator on the site
Sorry I messed up the posts security code at bottom and seems like my question may have been deleted as it asked me to try another one. I can get the VBA code for SUMVISIBLE to work well if my data is vertical and I hide rows. However it doesn't if my data runs horizontal and I want to hide columns. Is there a way to program this? Thanks!
This comment was minimized by the moderator on the site
I am able to get your VBA for =SUMVISIBLE above to work good. However just if my data runs vertically and I am hiding rows. Is there a way to program it so you can have your data run horizontally and it still work when you hide columns? THANKS!
This comment was minimized by the moderator on the site
The solution worked for me. :D
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
Rate this post:
0   Characters
Suggested Locations