Seperti di Excele untuk merangkum sel individu. Unggul. Menghitung dan penjumlahan sel yang memenuhi kriteria pemformatan bersyarat
Sering terjadi bahwa Anda perlu merangkum setiap detik, ketiga, keempat, dll. Sel dalam spreadsheet. Sekarang, berkat trik berikutnya, itu bisa dilakukan.
Excel tidak memberikan fungsi standar yang dapat merangkum masing-masing n-per sel atau string. Namun, Anda dapat melakukan tugas ini dalam beberapa cara yang berbeda. Semua pendekatan ini didasarkan pada fungsi string (baris) dan tetap (mod).
Fungsi baris (baris) Mengembalikan nomor baris untuk tautan yang ditentukan ke sel: baris (referensi), di versi Rusia dari baris Excel (tautan).
Fungsi Kiri (Mod) Mengembalikan saldo nomor ke pembagi: mod (nomor; pembagi), di versi Rusia Excel tetap (angka; pembagi).
Tempatkan fungsi garis (baris) ke fungsi tetap (mod) (untuk mentransfer argumen numerik), bagi 2 (untuk merangkum setiap sel kedua) dan periksa apakah hasilnya nol. Jika demikian, sel dijumlahkan. Fungsi-fungsi ini dapat digunakan dalam berbagai cara - beberapa akan memastikan hasil terbaik daripada yang lain. Misalnya, formula array untuk penjumlahan setiap sel kedua dalam kisaran $ 1 1: $ dan $ 100 bisa terlihat seperti ini: \u003d jumlah (jika (mod (ROW ($ 1: $ A $ 500); 2) \u003d 0; $ A $ 1: $ A $ 500; 0)), dalam versi Rusia dari Excel \u003d jumlah (jika (tetap (string ($ 1: $ 1: $ A $ 500); 2) \u003d 0; $ A $ 1: $ dan $ 500; 0)).
Karena ini adalah rumus array, Anda harus memasukkannya dengan menekan Ctrl + Shift + Enter kombinasi tombol, Excel akan menambahkan kawat gigi agar terlihat seperti ini: (\u003d jumlah (jika (mod ($ 1: $ 1: $ 5), 2) \u003d 0; $ A $ 1: $ A $ 500; 0))), dalam versi Rusia dari Excel: (\u003d jumlah (JIKA (SEMUA (string ($ 1 1: $ A $ 500); 2) \u003d 0; $ 1: $ A $ 500; 0))) Diperlukan bahwa Excel sendiri menambahkan tanda kurung angka; Jika Anda menambahkannya sendiri, formula tidak akan berfungsi.
Meskipun tujuannya tercapai, metode ini berdampak buruk pada desain. spreadsheet.. Ini adalah penggunaan formula array yang tidak perlu. Untuk memburuknya, fungsi hitalisasi string (baris) diinvestasikan dalam rumus panjang ini, yang mengubah rumus besar juga menjadi yang dihitung ulang. Ini berarti bahwa itu akan diterjemahkan secara konstan, apa pun yang Anda lakukan di buku kerja. Ini cara yang sangat buruk!
Ini adalah formula lain, yang sedikit pilihan terbaik: \u003d SUMPRODUCT ((mod (baris ($ 1: $ 1: $ A $ 500); 2) \u003d 0) * ($ A $ 1: $ A $ 500)), dalam versi Rusia dari Excel \u003d Sumpacy ((((((( String ($ A $ 1: $ A $ 500); 2) \u003d 0) * ($ A $ 1: $ A $ 500)).
Namun perlu, ingat bahwa rumus ini akan mengembalikan kesalahan # rata-rata! (#Value!) Jika ada rentang rentang tidak mengandung angka, tetapi teks. Formula ini, meskipun sebenarnya bukan rumus array, juga melambat excel bekerjaJika Anda menggunakannya terlalu sering atau jika setiap kali mengacu pada rentang besar.
Untungnya, ada jalan terbaik, yang tidak hanya lebih efisien, tetapi juga keputusan yang jauh lebih fleksibel. Ini membutuhkan penggunaan fungsi BDSMM (DSUM). Dalam contoh ini, kami menggunakan kisaran A1: A500 sebagai kisaran di mana perlu untuk merangkum setiap n-m ke sel.
Di sel E1 masukkan kriteria kata. Di sel E2, masukkan rumus berikut: \u003d mod (baris (A2) - $ c $ 2-1; $ c $ 2) \u003d 0, dalam versi Rusia Excel \u003d Sisa (A2) - $ c $ 2-1; $ С $ 2) \u003d 0. Pilih sel C2 dan pilih Data → Periksa (data → validasi).
Dalam bidang tipe data (izinkan), pilih Daftar (Daftar), dan di bidang Sumber (Sumber), masukkan 1, 2, 3, 4, 5, 6, 8, 9, 10. Pastikan kotak centang Daftar diperiksa. nilai yang diizinkan (Dalam sel), dan klik tombol OK. Di sel C1, masukkan teks penjumlahan setiap .... Dalam sel apa pun, kecuali baris 1, masukkan rumus berikut: \u003d DSUM ($ A; 1; $ E $ 1: $ E $ 2), dalam versi Rusia Excel \u003d BDSMM ($ A: $ A; 1; $ E $ 1: $ E $ 2).
Di sel langsung di atas satu tempat Anda memasukkan fungsi BDSMM (DSUM), masukkan teks \u003d "Summing setiap" & $ dengan $ 2 & pilih ($ C $ 2; "St"; "ND"; "RD"; " "th"; "th"; "th"; "th"; "th"; "th"; "th") & "sel". Sekarang tetap hanya untuk memilih nomor yang diinginkan di sel C2, dan sisanya akan membuat fungsi BDSMM (DSUM).
Menggunakan fungsi BDSMM (DSUM), Anda dapat merangkum sel melalui interval yang Anda tentukan. Fungsi BDSMM (DSUM) jauh lebih efisien daripada rumus array atau fungsi product. Meskipun pengaturannya membutuhkan waktu lebih lama, inilah masalahnya ketika sulit dipelajari, mudah bertempur.
Sebelumnya, saya menggambarkan cara menemukan fungsi pengguna. Sayangnya, fitur ini tidak berfungsi jika sel-sel dicat menggunakan pemformatan bersyarat. Saya berjanji untuk "menyelesaikan" fungsinya. Tetapi dalam dua tahun, yang berlalu dari publikasi catatan, saya tidak bisa secara independen maupun dengan bantuan informasi dari Internet untuk menulis kode resetan ... ( Pembaruan pada 29 Maret 2017 Lima tahun kemudian, saya masih berhasil menulis kode; Lihat daftar final Catatan). Dan baru-baru ini saya menemukan ide yang terkandung dalam kitab D. Choli, R. Holi "Excel 2007. Tricks", yang memungkinkan Anda melakukannya tanpa kode.
Misalkan ada daftar angka dari 1 hingga 100 ditempatkan di kisaran A1: A100 (Gbr. 1; lihat juga lembar "SMERE" file Excel). Rentang memformat kondisional yang diterapkan, menandai sel yang mengandung angka lebih dari 10 dan kurang atau sama dengan 20.
Ara. 1. Rentang angka; Pemformatan bersyarat sel-sel yang disorot berisi nilai dari 10 hingga 20
Unduh Catatan dalam format, contoh dalam format
Sekarang perlu untuk melipat nilai-nilai dalam sel yang memenuhi kriteria yang baru diinstal. Tidak masalah apa pemformatan yang diterapkan pada sel-sel ini, tetapi perlu untuk mengetahui kriteria yang sesuai dengan sel-sel yang dialokasikan.
Untuk melipat rentang sel yang menjawab satu Kriteria, Anda dapat menggunakan fungsi hening (Gbr. 2).
Ara. 2. Penjumlahan sel yang memenuhi satu syarat
Jika Anda memiliki beberapa Kondisi, Anda dapat menggunakan fungsi summelimn (Gbr. 3).
Ara. 3. Penjumlahan sel yang memenuhi beberapa kondisi
Untuk menghitung jumlah sel yang memenuhi satu kriteria, Anda dapat menggunakan fungsi meter.
Untuk menghitung jumlah sel yang memenuhi beberapa kriteria, Anda dapat menggunakan fitur Meckestimn.
Excel menyediakan fitur lain yang memungkinkan Anda menentukan beberapa kondisi. Fitur ini termasuk dalam set fitur basis data. data Excel. Dan disebut bdsumm. Untuk memeriksanya, gunakan set angka yang sama dalam kisaran A2: A100 (Gbr. 4; Lihat juga daftar BDSMM Excel File).
Ara. 4. Menggunakan fungsi basis data
Pilih C1: D2 sel dan tetapkan nama kriteria ke rentang ini dengan memasukkannya ke bidang nama kiri dari string rumus. Sekarang pilih sel C1 dan enter \u003d $ A $ 1, yaitu, referensi ke sel pertama pada lembar yang berisi nama database. Masukkan \u003d $ A $ 1 ke dalam sel D1 dan Anda akan menerima dua salinan header kolom. Salinan ini akan digunakan sebagai tajuk utama untuk kondisi BDSMM (C1: D2), yang Anda sebut kriteria. Di sel C2, enter\u003e 10. Di sel D2 Enter<=20. В ячейке, где должен быть результат, введите следующую формулу:
BDSMM ($ A $ 1: $ dan $ 101,1, kriteria)
Untuk menghitung jumlah sel yang memenuhi beberapa kriteria, Anda dapat menggunakan fungsi BSC.
Membaca buku John Waolenbach saya belajar bahwa, dimulai dengan versi Excel 2010, properti displayformat baru muncul di VBA (lihat, misalnya, range.displayformat properti). Itu., VBA dapat membaca format yang ditampilkan di layar. Tidak masalah bagaimana itu diperoleh, pengaturan pengguna langsung, atau dengan pemformatan bersyarat. Sayangnya, MS Developers telah melakukannya agar properti displayformat hanya berfungsi dalam prosedur yang disebut dari VBA, dan fungsi pengguna berdasarkan properti ini memberikan kesalahan #! Namun, dimungkinkan untuk mendapatkan jumlah nilai dalam kisaran pada sel-sel warna tertentu menggunakan prosedur (makro, tetapi tidak berfungsi). Buka (berisi kode VBA). Pergi melalui menu Melihat -> MACROS. -> MACROS.; di jendela Makro, sorot garis Sumyzvetus., dan tekan Melakukan. Jalankan makro, pilih rentang penjumlahan dan kriteria. Jawabannya akan muncul di jendela.
Kode Prosedur
Subsyzvetusl () application : \u003d ActiveCell.Address, _ Type: \u003d 8) "Kriterine Permintaan Set KriteriaRange \u003d Application.Inppbox (_ Prompt: \u003d" kriteria penjumlahan", _ Judul: \u003d" Seleksi Critter ", _ default: \u003d ActiveCell.Address, _ Type: \u003d 8)" Penjumlahan sel-sel "kanan" untuk setiap i dalam userrange jika i.color \u003d _ kriteriarange.displayformat. Interior.color kemudian Sumcolor \u003d Sumcolor + I berakhir jika MSGBox Selanjutnya SUMCOLOR END SUB
Sub sumyzvetus () Aplikasi. Volatile benar. Dim Sumcolor sebagai ganda Redup saya sebagai kisaran Dim Userrange sebagai kisaran Kritero redup sebagai rentang SUMCOLOR \u003d 0. "Rentang permintaan Set Userrange \u003d Application.Inputbox (_ Prompt: \u003d "Pilih rentang penjumlahan", _ Judul: \u003d "Seleksi Rentang", _ Default: \u003d ActiveCell.Address, _ Jenis: \u003d 8) "Permintaan Atur kriteria \u003d aplikasi. Inputbox (_ Prompt: \u003d. "Pilih kriteria penjumlahan", _ Judul: \u003d "Pilihan Kriteria", _ Default: \u003d ActiveCell. Alamat, _ |
Misalkan Anda memiliki laporan penjualan perwakilan penjualan ini:
Dari itu Anda perlu mencari tahu berapa banyak pensil. Kata perwakilan penjualan Ivanov di januari.
MASALAH: Cara merangkum data pada beberapa kriteria ??
KEPUTUSAN: Metode 1:
Bdsmm (A1: G16; F1; i1: K2)
Dalam versi bahasa Inggris:
DSUM (A1: G16, F1, I1: K2)
BAGAIMANA ITU BEKERJA:
Dari database yang ditentukan oleh kami A1: G16. fungsi Bdsumm. Menghapus dan merangkum data kolom jumlah (argumen " Bidang" = F1.) Menurut sel yang ditentukan dalam sel I1: K2. (Penjual \u003d Ivanov; Produk \u003d pensil.; Bulan \u003d Januari) Kriteria.
Cons: Daftar kriteria harus pada lembar.
Catatan: Jumlah kriteria penjumlahan terbatas pada RAM.
Area aplikasi: Semua versi Excel
Metode 2:
SUMMAT ((B2: B16 \u003d i2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2) * F2: F16)
Dalam versi bahasa Inggris:
SUMPRODUKS ((B2: B16 \u003d i2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2) * F2: F16)
BAGAIMANA ITU BEKERJA:
Fungsi bah menghasilkan array dari nilai kebenaran dan kebohongan, sesuai dengan kriteria yang dipilih, dalam memori Excel.
Jika perhitungan dilakukan pada sel-sel lembaran (untuk kejelasan, saya akan menunjukkan semua pekerjaan formula, seolah-olah perhitungan terjadi pada lembar, dan bukan dalam memori), maka array akan terlihat seperti ini:
Jelas, jika misalnya, D2 \u003d pensil.maka nilainya akan sama dengan kebenaran, dan jika D3 \u003d folder, kemudian salah (sebagai kriteria untuk pemilihan barang dalam contoh kami adalah nilainya Pensil.).
Mengetahui bahwa nilai kebenaran selalu sama dengan 1, dan kebohongan selalu sama dengan 0 kita terus bekerja dengan array seperti angka 0 dan 1.
Bergantian nilai-nilai array yang dihasilkan di antara diri mereka secara konsisten, kami mendapatkan satu array nonol dan unit. Di mana ketiga kriteria seleksi dilakukan ( Ivanov, pensil, Januari) I.E. Semua kondisi mengambil nilai-nilai kebenaran untuk mendapatkan 1 (1 * 1 * 1 \u003d 1), tetapi jika hanya satu syarat tidak puas - kita memperoleh 0 (1 * 1 * 0 \u003d 0; 1 * 0 * 1 \u003d 0; 0 * 1 * 1 \u003d 0).
Sekarang tetap hanya untuk melipatgandakan array yang dihasilkan ke array yang berisi data yang perlu kita rangkum (kisaran F2: F16.) Dan pada kenyataannya, untuk meringkas bahwa pada 0 tidak berkembang biak.
Sekarang bandingkan formula yang dihasilkan dan dengan perhitungan langkah demi langkah pada daun array (disorot dengan warna merah).
Saya pikir semuanya jelas :)
Minus.: SUMMIPAL. - Formula "berat" dari array. Ketika menghitung pada rentang data besar, waktu perhitungan ulang meningkat secara signifikan.
Catatan
Area aplikasi: Semua versi Excel
Metode 3: Formula besar-besaran
Jumlah (if (((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), F16))
BAGAIMANA ITU BEKERJA: Sama seperti metode nomor 2. Hanya ada dua perbedaan - rumus ini dimasukkan dengan menekan Ctrl + Shift + EnterBukan hanya menekan MEMASUKKAN Dan array 0 dan 1-C tidak dikalikan dengan rentang penjumlahan, dan dipilih menggunakan fungsi jika.
Minus.: Rumus array saat menghitung pada rentang data besar secara signifikan meningkatkan waktu perhitungan ulang.
Catatan: Jumlah array array dibatasi hingga 255.
Area aplikasi: Semua versi Excel
Metode 4:
Smerevevylmn (F2: F16; B2: B16; i2; D2: D16; J2; A2: A16; K2)