Jumlahkan sel yang dipilih di excel. Unggul. Hitung dan jumlahkan sel yang memenuhi kriteria pemformatan bersyarat
Sering terjadi bahwa Anda perlu menjumlahkan setiap sel detik, ketiga, keempat, dll dalam spreadsheet. Sekarang, berkat trik selanjutnya, itu bisa dilakukan.
Excel tidak menyediakan fungsi standar yang dapat menjumlahkan masing-masing sel ke-n atau string. Namun, Anda dapat menyelesaikan tugas ini dengan beberapa cara yang berbeda... Semua pendekatan ini didasarkan pada fungsi ROW dan MOD.
Fungsi ROW mengembalikan nomor baris untuk referensi sel tertentu: ROW (referensi), dalam versi Rusia dari Excel ROW (referensi).
Fungsi OSTAT (MOD) mengembalikan sisa pembagian bilangan dengan pembagi: MOD (bilangan; pembagi), dalam versi Rusia dari Excel OSTAT (bilangan; pembagi).
Letakkan fungsi ROW dalam fungsi MOD (untuk meneruskan argumen numerik), bagi dengan 2 (untuk menjumlahkan setiap sel lainnya) dan periksa apakah hasilnya bukan nol. Jika demikian, sel tersebut ditambahkan. Fungsi-fungsi ini dapat digunakan dalam berbagai cara - beberapa akan memberikan hasil yang lebih baik daripada yang lain. Misalnya, rumus array untuk menjumlahkan setiap sel kedua dalam kisaran $ A $ 1: $ A $ 100 mungkin terlihat seperti ini: \u003d SUM (IF (MOD (ROW ($ A $ 1: $ A $ 500); 2) \u003d 0; $ A $ 1: $ A $ 500; 0)), dalam versi Rusia Excel \u003d SUM (IF (OSTAT (LINE ($ A $ 1: $ A $ 500); 2) \u003d 0; $ A $ 1: $ A $ 500; 0)).
Karena ini adalah rumus array, Anda harus memasukkannya dengan menekan Ctrl + Shift + Enter, Excel akan menambahkan tanda kurung kurawal sehingga terlihat seperti ini: (\u003d SUM (IF (MOD (ROW ($ A $ 1: $ A $ 500)) ), 2) \u003d 0; $ A $ 1: $ A $ 500; 0))), dalam versi Rusia Excel: (\u003d SUM (IF (Sisa (ROW ($ A $ 1: $ A $ 500)); 2) \u003d 0; $ A $ 1: $ A $ 500; 0))) Anda membutuhkan Excel untuk menambahkan tanda kurung kurawal dengan sendirinya; jika Anda menambahkannya sendiri, rumusnya tidak akan berfungsi.
Meskipun tujuannya tercapai, metode ini berdampak negatif pada desain. spreadsheet... Ini adalah aplikasi rumus array yang tidak perlu. Lebih buruk lagi, rumus panjang ini menyertakan fungsi ROW yang dapat dihitung ulang yang mengubah rumus besar menjadi fungsi yang dapat dihitung ulang juga. Artinya, ini akan terus dihitung ulang, apa pun yang Anda lakukan di buku kerja. Ini cara yang sangat buruk!
Ini rumus lain yang sedikit pilihan terbaik: \u003d SUMPRODUCT ((MOD (ROW ($ A $ 1: $ A $ 500); 2) \u003d 0) * ($ A $ 1: $ A $ 500)), dalam versi Rusia Excel \u003d SUMPRODUCT ((REST (BARIS ($ A $ 1: $ A $ 500); 2) \u003d 0) * ($ A $ 1: $ A $ 500)).
Ingat, bagaimanapun, bahwa rumus ini akan mengembalikan #VALUE! (#VALUE!) Jika ada sel dalam rentang yang berisi teks, bukan angka. Rumus ini, meskipun sebenarnya bukan rumus array, juga melambat pekerjaan Exceljika Anda menggunakannya terlalu banyak, atau jika mengacu pada rentang yang besar setiap kali.
Untungnya ada jalan terbaik, yang tidak hanya lebih efisien, tetapi juga jauh lebih fleksibel. Ini membutuhkan penggunaan fungsi BDSUM (DSUM). Dalam contoh ini, kami menggunakan rentang A1: A500 sebagai rentang di mana setiap sel ke-n perlu ditambahkan.
Masukkan kata Kriteria di sel E1. Masukkan rumus berikut di sel E2: \u003d MOD (ROW (A2) - $ C $ 2-1; $ C $ 2) \u003d 0, dalam versi Rusia Excel \u003d OSTAT (ROW (A2) - $ C $ 2- 1; $ C $ 2) \u003d 0. Pilih sel C2 dan pilih perintah Data → Validasi.
Di kolom Allow, pilih List, dan di kolom Source, masukkan 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Pastikan kotak centang dicentang. (In-Cell), dan klik tombol OK. Di sel C1, masukkan teks SUM setiap…. Di sel mana pun selain baris 1, masukkan rumus berikut: \u003d DSUM ($ A: $ A; 1; $ E $ 1: $ E $ 2), dalam versi Rusia Excel \u003d BDSUMM ($ A: $ A; 1; $ E $ 1: $ E $ 2).
Di sel tepat di atas sel tempat Anda memasukkan fungsi DSUM, masukkan teks \u003d "Summing Every" & $ C $ 2 & CHOOSE ($ C $ 2; "st"; "nd"; "rd"; "th" ; "th"; "th"; "th"; "th"; "th"; "th") & "Cell". Sekarang yang tersisa hanyalah memilih nomor yang diinginkan di sel C2, dan fungsi DSUM akan melakukan sisanya.
Menggunakan fungsi BDSUM (DSUM), Anda dapat menjumlahkan sel pada interval yang Anda tentukan. Fungsi DSUM jauh lebih efisien daripada rumus array atau fungsi SUMPRODUCT. Meskipun membutuhkan waktu sedikit lebih lama untuk menyiapkan, ini adalah kasus ketika sulit dipelajari, mudah untuk bertarung.
Sebelumnya saya telah menjelaskan cara mencari file. Sayangnya, fungsi ini tidak berfungsi jika sel diwarnai dengan pemformatan bersyarat... Saya berjanji untuk "memperbaiki" fungsinya. Tetapi dalam dua tahun yang telah berlalu sejak penerbitan catatan itu, saya belum dapat menulis kode yang dapat dicerna baik sendiri atau menggunakan informasi dari Internet ... ( Update per 29 Maret 2017 Setelah lima tahun berikutnya, saya masih bisa menulis kode; lihat bagian terakhir dari catatan). Dan baru-baru ini saya menemukan ide yang terkandung dalam buku oleh D. Hawley, R. Hawley "Excel 2007. Trik", yang memungkinkan Anda melakukannya tanpa kode.
Biarkan ada daftar nomor dari 1 hingga 100, terletak di kisaran A1: A100 (Gbr. 1; lihat juga lembar "SUMIF" dari file Excel). Rentang telah diformat secara bersyarat untuk menandai sel yang berisi angka lebih besar dari 10 dan kurang dari atau sama dengan 20.
Angka: 1. Rentang angka; pemformatan bersyarat yang disorot sel yang berisi nilai dari 10 hingga 20
Unduh catatan dalam format, contoh dalam format
Sekarang Anda perlu menambahkan nilai di sel yang memenuhi kriteria yang baru saja ditetapkan. Tidak masalah pemformatan apa yang diterapkan pada sel ini, tetapi Anda perlu mengetahui kriteria yang digunakan untuk menyorot sel.
Untuk menambahkan rentang sel yang cocok satu kriteria, Anda dapat menggunakan fungsi SUMIF (Gbr. 2).
Angka: 2. Penjumlahan sel yang memenuhi satu kondisi
Jika Anda memiliki beberapa kondisi, Anda dapat menggunakan fungsi SUMIFS (Gbr. 3).
Angka: 3. Menjumlahkan sel yang memenuhi beberapa kondisi
Anda dapat menggunakan fungsi COUNTIF untuk menghitung jumlah sel yang memenuhi satu kriteria.
Anda dapat menggunakan fungsi COUNTIF untuk menghitung jumlah sel yang memenuhi beberapa kriteria.
Excel menyediakan fungsi lain yang memungkinkan Anda menentukan beberapa kondisi. Fungsi ini termasuk dalam himpunan fungsi basis data Excel dan disebut BDSUMM. Untuk memeriksanya, gunakan set angka yang sama dalam rentang A2: A100 (Gbr. 4; lihat juga sheet "BDSUMM" dari file Excel).
Angka: 4. Menggunakan fungsi database
Pilih sel C1: D2 dan beri nama kriteria rentang ini dengan mengetiknya di kotak nama di sebelah kiri bilah rumus. Sekarang pilih sel C1 dan masukkan \u003d $ A $ 1, yang merupakan referensi ke sel pertama pada lembar yang berisi nama database. Masukkan \u003d $ A $ 1 di sel D1 dan Anda akan mendapatkan dua salinan dari judul kolom A. Salinan ini akan digunakan sebagai tajuk untuk kondisi BDSUMM (C1: D2), yang Anda beri nama Kriteria. Di sel C2, masukkan\u003e 10. Di sel D2, masukkan<=20. В ячейке, где должен быть результат, введите следующую формулу:
BDSUMM ($ A $ 1: $ A $ 101,1, Kriteria)
Anda dapat menggunakan fungsi COUNT untuk menghitung jumlah sel yang memenuhi beberapa kriteria.
Membaca buku oleh John Walkenbach, saya mengetahui bahwa mulai dengan Excel 2010, VBA memiliki properti DisplayFormat baru (lihat, misalnya, Properti Range.DisplayFormat). Artinya, VBA dapat membaca format yang ditampilkan di layar. Tidak peduli bagaimana itu diterima oleh pengaturan pengguna langsung, atau menggunakan pemformatan bersyarat. Sayangnya, MS telah membuatnya sehingga properti DisplayFormat hanya berfungsi dalam prosedur yang dipanggil dari VBA, dan UDF berdasarkan properti ini melontarkan #VALUE! Namun, Anda bisa mendapatkan jumlah nilai dalam rentang dengan sel warna tertentu menggunakan prosedur (makro, tapi bukan fungsi). Terbuka (berisi kode VBA). Buka menu Melihat -> Makro -> Makro; di jendela Makro, sorot garis tersebut SumColorUsl, dan tekan Menjalankan... Jalankan makro, pilih rentang penjumlahan dan kriteria. Jawabannya akan muncul di jendela.
Kode prosedur
Aplikasi Sub SumColorConv. \u003d "Range Selection", _ Default: \u003d ActiveCell.Address, _ Type: \u003d 8) "Criterion Request Set CriterionRange \u003d Application.InputBox (_ Prompt: \u003d" Select kriteria penjumlahan", _ Title: \u003d" Criterion selection ", _ Default: \u003d ActiveCell.Address, _ Type: \u003d 8)" Jumlah sel "yang benar" Untuk Setiap i Dalam UserRange Jika i.DisplayFormat.Interior.Color \u003d _ CriterionRange.DisplayFormat Interior.Color Lalu SumColor \u003d SumColor + i End Jika Berikutnya MsgBox SumColor End Sub
Sub SumColorUl () Aplikasi. Volatile True Dim SumWarna Sebagai Ganda Dim i As Range Redupkan UserRange As Range Redupkan CriterionRange As Range SumColor \u003d 0 "Kueri rentang Set UserRange \u003d Application.InputBox (_ Prompt: \u003d "Pilih rentang penjumlahan", _ Judul: \u003d "Pilihan rentang", _ Default: \u003d ActiveCell.Address, _ Jenis: \u003d 8) "Proscriptor Set CriterionRange \u003d Aplikasi. InputBox (_ Perintah: \u003d "Pilih kriteria penjumlahan", _ Judul: \u003d "Pemilihan kriteria", _ Default: \u003d ActiveCell. Alamat, _ |
Misalkan Anda memiliki laporan penjualan seperti ini:
Dari situ Anda perlu mencari tahu berapa banyak pensil dijual oleh perwakilan penjualan Ivanov di januari.
MASALAH: Bagaimana meringkas data dengan beberapa kriteria ??
KEPUTUSAN: Metode 1:
BDSUMM (A1: G16; F1; I1: K2)
Dalam versi bahasa Inggris:
DSUM (A1: G16, F1, I1: K2)
BAGAIMANA ITU BEKERJA:
Dari database yang kami tentukan A1: G16 fungsi BDSUMM mengambil dan meringkas data kolom jumlah (argumen " Bidang" = F1) sesuai dengan yang diberikan dalam sel I1: K2 (Penjual \u003d Ivanov; Produk \u003d Pensil; Bulan \u003d Januari) kriteria.
CONS: Daftar kriteria harus ada di lembar.
CATATAN: Jumlah kriteria penjumlahan dibatasi oleh RAM.
AREA APLIKASI: Versi Excel apa pun
Metode 2:
SUMPRODUKSI ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2) * F2: F16)
Dalam versi bahasa Inggris:
SUMPRODUKSI ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2) * F2: F16)
BAGAIMANA ITU BEKERJA:
Fungsi SUMPRODUCT membentuk array nilai TRUE dan FALSE, menurut kriteria yang dipilih, dalam memori Excel.
Jika kalkulasi dilakukan di sel lembar (untuk kejelasan, saya akan mendemonstrasikan seluruh pekerjaan rumus seolah-olah kalkulasi dilakukan di lembar, dan bukan di memori), maka susunannya akan terlihat seperti ini:
Jelas bahwa jika, misalnya, D2 \u003d Pensil, maka nilainya akan BENAR, dan jika D3 \u003d Folder, lalu FALSE (karena kriteria untuk memilih produk dalam contoh kita adalah nilainya Pensil).
Mengetahui bahwa nilai TRUE selalu sama dengan 1, dan FALSE selalu sama dengan 0, kami terus bekerja dengan array seperti pada angka 0 dan 1.
Mengalikan nilai yang diperoleh dari array satu sama lain secara berurutan, kita mendapatkan SATU array nol dan satu. Dimana ketiga kriteria seleksi terpenuhi, ( IVANOV, PENCILS, JANUARI) yaitu semua kondisi mengambil nilai BENAR, kami mendapatkan 1 (1 * 1 * 1 \u003d 1), tetapi jika setidaknya satu kondisi tidak terpenuhi, kami mendapatkan 0 (1 * 1 * 0 \u003d 0; 1 * 0 * 1 \u003d 0; 0 * 1 * 1 \u003d 0).
Sekarang yang tersisa hanyalah mengalikan larik yang dihasilkan dengan larik yang berisi data yang perlu kita rangkum sebagai hasil (range F2: F16) dan, nyatanya, jumlahkan apa yang tidak dikalikan dengan 0.
Sekarang bandingkan array yang diperoleh dengan menggunakan rumus dan selama perhitungan langkah demi langkah pada lembar (disorot dengan warna merah).
Saya pikir semuanya jelas :)
MINUS: SUMPRODUCT - rumus larik "berat". Saat menghitung pada rentang data yang besar, waktu penghitungan ulang meningkat secara nyata.
CATATAN
AREA APLIKASI: Versi Excel apa pun
Metode 3: Rumus Array
SUM (JIKA ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2); F2: F16))
Dalam versi bahasa Inggris:
SUM (IF ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2), F2: F16))
BAGAIMANA ITU BEKERJA: Dengan cara yang sama seperti Metode # 2. Hanya ada dua perbedaan - rumus ini dimasukkan dengan menekan Ctrl + Shift + Enterbukan hanya menekan Memasukkan dan larik 0-th dan 1-q tidak dikalikan dengan rentang penjumlahan, tetapi dipilih menggunakan fungsi IF.
MINUS: Rumus larik saat menghitung rentang data yang besar secara nyata meningkatkan waktu penghitungan ulang.
CATATAN: Jumlah array yang diproses dibatasi hingga 255.
AREA APLIKASI: Versi Excel apa pun
Metode 4:
SUMIFN (F2: F16; B2: B16; I2; D2: D16; J2; A2: A16; K2)