Excelda alohida hujayralarni qanday sarhisob qilish kerak. Excel. Shartli formatlash mezonlariga javob beradigan kataklarni hisoblash va yig'ish
Elektron jadvaldagi har bir ikkinchi, uchinchi, to'rtinchi va hokazolarni yig'ish kerak bo'lgan holatlar ko'p uchraydi. Endi navbatdagi hiyla-nayrang tufayli buni amalga oshirish mumkin.
Excel har birini yig'adigan standart funktsiyani ta'minlamaydi n-hujayra yoki mag'lubiyat. Biroq, siz ushbu vazifani bir nechta bilan bajarishingiz mumkin turli xil yo'llar... Ushbu yondashuvlarning barchasi ROW va MOD funktsiyalariga asoslangan.
ROW funktsiyasi berilgan katakka berilgan satr raqamini qaytaradi: ROW (ma'lumotnoma), rus tilidagi Excel ROW versiyasida (ma'lumotnoma).
OSTAT funktsiyasi (MOD) raqamni bo'linuvchiga bo'lishning qolgan qismini qaytaradi: MOD (son; bo'luvchi), rus tilidagi Excel OSTAT versiyasida (raqam; bo'luvchi).
ROW funktsiyasini MOD funktsiyasiga joylashtiring (raqamli argumentni o'tkazish uchun), ikkiga bo'ling (har bir boshqa hujayrani yig'ish uchun) va natijaning nolga teng emasligini tekshiring. Agar shunday bo'lsa, katak qo'shiladi. Ushbu funktsiyalardan turli xil usullarda foydalanish mumkin - ba'zilari boshqalariga qaraganda yaxshiroq natijalar beradi. Masalan, $ A $ 1: $ A $ 100 oralig'idagi har bir ikkinchi katakni yig'adigan massiv formulasi quyidagicha ko'rinishi mumkin: \u003d SUM (IF (MOD (ROW ($ A $ 1: $ A $ 500); 2) \u003d 0; $ A $ 1: $ A $ 500; 0)), Excelning rus tilidagi versiyasida \u003d SUM (IF (OSTAT (LINE ($ A $ 1: $ A $ 500); 2) \u003d 0; $ A $ 1: $ A $ 500; 0)).
Bu massiv formulasi bo'lgani uchun uni Ctrl + Shift + Enter tugmachalarini bosib kiritishingiz kerak, Excel jingalak qavslarni quyidagicha qo'shadi: (\u003d SUM (IF (MOD (ROW ($ A $ 1: $ A $ 500) ), 2) \u003d 0; $ A $ 1: $ A $ 500; 0)))), Excelning rus tilidagi versiyasida: (\u003d SUM (IF (Qolgan (ROW ($ A $ 1: $ A $ 500)); 2) \u003d 0; $ A $ 1: $ A $ 500; 0))) jingalak qavslarni o'zi qo'shish uchun Excel kerak; agar siz ularni o'zingiz qo'shsangiz, formula ishlamaydi.
Maqsadga erishilgan bo'lsa-da, ushbu usul dizaynga salbiy ta'sir qiladi. elektron jadval... Bu massiv formulasining keraksiz qo'llanilishi. Vaziyatni yanada yomonlashtiradigan bo'lsak, ushbu uzun formulada katta formulani qayta hisoblanadigan funktsiyaga aylantiradigan qayta hisoblanadigan ROW funktsiyasi mavjud. Bu shuni anglatadiki, ish daftarida nima qilsangiz ham, u doimo qayta hisoblab chiqiladi. Bu juda yomon yo'l!
Bu erda yana bir ozgina bo'lgan yana bir formula mavjud eng yaxshi tanlov: \u003d SUMPRODUCT ((MOD (ROW ($ A $ 1: $ A $ 500); 2) \u003d 0) * ($ A $ 1: $ A $ 500)), Excelning rus tilidagi versiyasida \u003d SUMPRODUCT ((REST (ROW ($ A $ 1: $ A $ 500); 2) \u003d 0) * ($ A $ 1: $ A $ 500)).
Shunga qaramay, ushbu formula #VALUE qiymatini qaytarishini unutmang! (#VALUE!) Agar oraliqdagi biron bir katakda raqamlar o'rniga matn bo'lsa. Ushbu formula, aslida massiv formulasi bo'lmasa ham, sekinlashadi excel ishiagar siz uni juda ko'p marta ishlatsangiz yoki u har safar katta diapazonga tegishli bo'lsa.
Yaxshiyamki bor eng yaxshi usul, bu nafaqat samaraliroq, balki juda ham moslashuvchan. Bu DSUM funktsiyasidan foydalanishni talab qiladi. Ushbu misolda biz A1: A500 oralig'ini har bir n-katak qo'shilishi kerak bo'lgan diapazon sifatida ishlatdik.
E1 katakchasiga Mezon so'zini kiriting. E2 katakchaga quyidagi formulani kiriting: \u003d MOD (ROW (A2) - $ C $ 2-1; $ C $ 2) \u003d 0, Excelning rus tilidagi versiyasida \u003d OSTAT (ROW (A2) - $ C $ 2- 1; $ C $ 2) \u003d 0. C2 katakchani tanlang va Ma'lumotlar → Tasdiqlash buyrug'ini tanlang.
Ruxsat berish maydonida Ro'yxat-ni tanlang va Manba maydoniga 1, 2, 3, 4, 5, 6, 7, 8, 9, 10-ni kiriting, katakchani belgilaganligiga ishonch hosil qiling. (Hujrada) va OK tugmasini bosing. C1 katakchaga har… uchun SUM matnini kiriting. 1-qatordan boshqa har qanday katakchaga quyidagi formulani kiriting: \u003d DSUM ($ A: $ A; 1; $ E $ 1: $ E $ 2), Excelning rus tilidagi versiyasida \u003d BDSUMM ($ A: $ A; 1; $ E $ 1: $ E $ 2).
DSUM funktsiyasini kiritgan katakning yuqorisidagi katakchaga \u003d "Summing Every" & $ C $ 2 & CHOOSE ($ C $ 2; "st"; "nd"; "rd"; "th" matnini kiriting. ; "th"; "th"; "th"; "th"; "th"; "th") & "Cell". Endi C2 katakchada kerakli raqamni tanlash qoldi, qolganini DSUM funktsiyasi bajaradi.
BDSUM (DSUM) funktsiyasidan foydalangan holda siz belgilangan oraliqda kataklarni yig'ishingiz mumkin. DSUM funktsiyasi massiv formulasidan yoki SUMPRODUCT funktsiyasidan ancha samarali. O'rnatish biroz ko'proq vaqtni talab qilsa-da, o'rganish qiyin bo'lgan, kurashish oson bo'lgan holat.
Ilgari men qanday topishni tasvirlab berdim. Afsuski, hujayralar ranglangan bo'lsa, bu funktsiya ishlamaydi shartli formatlash... Men funktsiyani "takomillashtirishga" va'da berdim. Ammo ushbu eslatma nashr etilganidan beri o'tgan ikki yil ichida men o'zim yoki Internetdagi ma'lumotlardan foydalanib hazm bo'ladigan kod yozolmadim ... ( 2017 yil 29 martni yangilang Yana besh yildan so'ng, men hali ham kod yozishga muvaffaq bo'ldim; eslatmaning yakuniy qismiga qarang). Va yaqinda men D. Xolining, R. Xoulining "Excel 2007. Fokuslar" kitobida mavjud bo'lgan g'oyani uchratdim, bu sizga kodsiz ishlashga imkon beradi.
A1: A100 oralig'ida joylashgan 1 dan 100 gacha bo'lgan raqamlar ro'yxati bo'lsin (1-rasm; shuningdek, Excel faylining "SUMIF" varag'iga qarang). 10 dan katta va 20 dan kichik yoki unga teng raqamlarni o'z ichiga olgan katakchalarni belgilash uchun oraliq shartli ravishda formatlangan.
Shakl: 1. Sonlar diapazoni; 10 dan 20 gacha bo'lgan qiymatlarni o'z ichiga olgan ajratilgan katakchalarni shartli formatlash
Qaydni formatda, misollarni formatda yuklab oling
Endi siz o'rnatgan mezonlarga mos keladigan katakchalarga qiymatlarni qo'shishingiz kerak. Ushbu katakchalarga qanday formatlash qo'llanilishi muhim emas, lekin katakchalarni ajratib ko'rsatadigan mezonlarni bilishingiz kerak.
Mos keladigan kataklar qatorini qo'shish uchun bitta mezon, SUMIF funktsiyasidan foydalanishingiz mumkin (2-rasm).
Shakl: 2. Bitta shartga javob beradigan hujayralar yig'indisi
Agar sizda bo'lsa biroz SUMIFS funktsiyasidan foydalanishingiz mumkin (3-rasm).
Shakl: 3. Bir nechta shartlarga javob beradigan hujayralarni yig'ish
COUNTIF funktsiyasidan bitta mezonga javob beradigan kataklar sonini hisoblash uchun foydalanishingiz mumkin.
COUNTIF funktsiyasidan bir nechta mezonlarga javob beradigan kataklar sonini hisoblash uchun foydalanishingiz mumkin.
Excel sizga bir nechta shartlarni belgilashga imkon beradigan yana bir funktsiyani taqdim etadi. Ushbu funktsiya bazalar funktsiyalari to'plamiga kiritilgan excel ma'lumotlari va BDSUMM deb nomlanadi. Uni tekshirish uchun A2: A100 oralig'idagi bir xil raqamlar to'plamidan foydalaning (4-rasm; shuningdek, Excel faylining "BDSUMM" varag'iga qarang).
Shakl: 4. Ma'lumotlar bazasi funktsiyalaridan foydalanish
C1: D2 katakchalarni tanlang va ushbu qatorga formulalar satrining chap qismidagi ism maydoniga yozib, mezonini bering. Endi C1 katakchani tanlang va \u003d $ A $ 1 kiriting, bu ma'lumotlar bazasi nomini o'z ichiga olgan varaqdagi birinchi katakka havola. D1 katakchasiga \u003d $ A $ 1 raqamini kiriting va A satrining ikki nusxasini olasiz. Ushbu nusxalar siz Criterion deb nomlagan BDSUMM (C1: D2) shartlari uchun sarlavha sifatida ishlatiladi. C2 katagiga\u003e 10 kiriting. D2 katagiga kiriting<=20. В ячейке, где должен быть результат, введите следующую формулу:
BDSUMM ($ A $ 1: $ A $ 101.1, mezon)
COUNT funktsiyasidan ko'p mezonlarga javob beradigan kataklar sonini hisoblash uchun foydalanishingiz mumkin.
Jon Uolkenbaxning kitobini o'qib, Excel 2010 yildan boshlab VBA yangi DisplayFormat xususiyatiga ega ekanligini bilib oldim (masalan, Range.DisplayFormat xususiyati). Ya'ni, VBA ekranda ko'rsatilgan formatni o'qishi mumkin. Bu to'g'ridan-to'g'ri foydalanuvchi sozlamalari yoki shartli formatlash yordamida qanday qabul qilinganligi muhim emas. Afsuski, MS buni shunday qildi: DisplayFormat xususiyati faqat VBA dan chaqirilgan protseduralarda ishlaydi va ushbu xususiyatga asoslangan UDFlar #VALUE ni tashlaydi! Biroq, protsedura (makro, lekin funktsiya emas) yordamida ma'lum bir rang hujayralari tomonidan diapazondagi qiymatlar yig'indisini olishingiz mumkin. Ochiq (VBA kodini o'z ichiga oladi). Menyudan o'ting Ko'rinish -> Makrolar -> Makrolar; oynada Ibratli, chiziqni ajratib ko'rsatish SumColorUslva tugmasini bosing Ijro eting... Makrosni ishga tushiring, summa diapazoni va mezonini tanlang. Javob oynada paydo bo'ladi.
Jarayon kodi
Sub SumColorConv () Application.Volatile True Dim SumColor as Double Dim i As Range Dim UserRange As Range Dim CriterionRange As Range SumColor \u003d 0 "Range query Set UserRange \u003d Application.InputBox (_ Prompt: \u003d" Summat intervalni tanlang ", _ Sarlavha: \u003d "Range Selection", _ Default: \u003d ActiveCell.Address, _ Type: \u003d 8) "Criterion Request Set CriterionRange \u003d Application.InputBox (_ Prompt: \u003d" Select summa mezoni", _ Sarlavha: \u003d" Criterion selection ", _ Default: \u003d ActiveCell.Address, _ Type: \u003d 8)" UserDange If i.DisplayFormat.Interior.Color \u003d _ CriterionRange.DisplayFormat uchun "to'g'ri" kataklarning yig'indisi Interior.Color Keyin SumColor \u003d SumColor + i End Agar keyingi MsgBox SumColor End Sub bo'lsa
Sub SumColorUl () Ilova. Uchuvchi to'g'ri Dim SumColor as Double Dim i As Range Dim UserRange diapazoni sifatida Xira mezon: diapazon sifatida SumColor \u003d 0 "Oraliq so'rovi UserRange \u003d Application.InputBox-ni o'rnating (_ Prompt: \u003d "Summa oralig'ini tanlang", _ Sarlavha: \u003d "Tanlov oralig'i", _ Odatiy: \u003d ActiveCell.Address, _ Turi: \u003d 8) "Proscriterion CriterionRange \u003d Application-ni o'rnating. InputBox (_ So'rov: \u003d "Summa mezonini tanlang", _ Sarlavha: \u003d "Mezon tanlash", _ Odatiy: \u003d ActiveCell. Manzil, _ |
Sizda shunday savdo hisoboti bor deylik:
Undan siz qancha pul topishingiz kerak qalamlar savdo vakili tomonidan sotiladi Ivanov yilda yanvar.
Muammo: Ma'lumotlarni bir necha mezon bo'yicha qanday umumlashtirish mumkin?
QAROR: 1-usul:
BDSUMM (A1: G16; F1; I1: K2)
Ingliz tilidagi versiyasida:
DSUM (A1: G16, F1, I1: K2)
U QANDAY ISHLAYDI:
Biz ko'rsatgan ma'lumotlar bazasidan A1: G16 funktsiya BDSUMM ustun ma'lumotlarini oladi va yig'adi miqdori (dalil " Maydon" = F1) hujayralarga berilgan I1: K2 (Sotuvchi \u003d Ivanov; Mahsulotlar \u003d Qalamlar; Oy \u003d yanvar) mezonlari.
CONS: mezonlarning ro'yxati varaqda bo'lishi kerak.
Izohlar: Yig'ish mezonlari soni RAM bilan cheklangan.
Ilova maydoni: Excelning har qanday versiyasi
2-usul:
QISM MAHSULOT ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2) * F2: F16)
Ingliz tilidagi versiyasida:
QISM MAHSULOT ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2) * F2: F16)
U QANDAY ISHLAYDI:
SUMPRODUCT funktsiyasi Excel xotirasida tanlangan mezonlarga muvofiq TRUE va FALSE qiymatlari massivlarini shakllantiradi.
Agar hisob-kitoblar varaqning katakchalarida bajarilgan bo'lsa (aniqlik uchun, men formulaning butun ishini hisoblar xotirada emas, balki varaqda sodir bo'lgandek namoyish qilaman), unda massivlar quyidagicha ko'rinadi:
Agar, masalan, D2 \u003d qalamlar, keyin qiymat TRUE bo'ladi, va agar D3 \u003d papkalar, keyin FALSE (chunki bizning misolimizda mahsulotni tanlash mezonlari bu qiymatdir Qalamlar).
Haqiqat har doim 1 ga, FALSE har doim 0 ga teng ekanligini bilib, biz 0 va 1 raqamlaridagi kabi massivlar bilan ishlashni davom ettiramiz.
Massivlarning olingan qiymatlarini bir-biri bilan ketma-ket ko'paytirib, biz BIR qator nol va birlikni olamiz. Uchala tanlov mezonlari bajarilgan joyda, ( IVANOV, QALAMQALAR, YANVAR) ya'ni barcha shartlar TRUE qiymatlarini oldi, biz 1 (1 * 1 * 1 \u003d 1) ni olamiz, ammo kamida bitta shart bajarilmasa, biz 0 (1 * 1 * 0 \u003d 0; 1 * 0 * 1 ni olamiz \u003d 0; 0 * 1 * 1 \u003d 0).
Endi natijaviy qatorni natijalar (intervalli) sifatida sarhisob qilishimiz kerak bo'lgan ma'lumotlarni o'z ichiga olgan massiv bilan ko'paytirishgina qoladi F2: F16) va, aslida 0 ga ko'paytirilmagan narsani jamlang.
Endi formuladan foydalangan holda va varaqdagi bosqichma-bosqich hisoblash paytida olingan massivlarni solishtiring (qizil rang bilan belgilangan).
Menimcha hamma narsa aniq :)
MINUSLAR: SUMPRODUCT - "og'ir" massiv formulasi. Katta hajmdagi intervallarni hisoblashda qayta hisoblash vaqti sezilarli darajada oshadi.
Izohlar
Ilova maydoni: Excelning har qanday versiyasi
3-usul: Array formulasi
SUM (IF ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2); F2: F16))
Ingliz tilidagi versiyasida:
SUM (IF ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2), F2: F16))
U QANDAY ISHLAYDI: 2-usul bilan bir xil tarzda. Faqat ikkita farq bor - bu formula bosish orqali kiritiladi Ctrl + Shift + Entershunchaki bosishdan ko'ra Kirish va 0-chi va 1-q massivlari yig'indilar oralig'iga ko'paytirilmaydi, lekin IF funktsiyasi yordamida tanlanadi.
MINUSLAR: Katta hajmdagi intervallarni hisoblashda massiv formulalari qayta hisoblash vaqtini sezilarli darajada oshiradi.
Izohlar: Qayta ishlangan massivlar soni 255 bilan cheklangan.
Ilova maydoni: Excelning har qanday versiyasi
4-usul:
SUMIFS (F2: F16; B2: B16; I2; D2: D16; J2; A2: A16; K2)