Сумиране на избраните клетки в Excel. Excel. Преброяване и сумиране на клетки, които отговарят на критерии за условно форматиране
Често се случва да трябва да сумирате всяка втора, трета, четвърта и т.н. клетка в електронна таблица. Сега, благодарение на следващия трик, може да се направи.
Excel не предоставя стандартна функция, която може да сумира всяка n-та клетка или низ. Можете обаче да изпълните тази задача с няколко различни начини... Всички тези подходи се основават на функциите ROW и MOD.
ROW функция връща номера на реда за дадената препратка към клетка: ROW (справка), в руската версия на Excel ROW (препратка).
Функция OSTAT (MOD) връща остатъка от разделяне на число на делител: MOD (число; делител), в руската версия на Excel OSTAT (число; делител).
Поставете функцията ROW във функцията MOD (за да предадете числов аргумент), разделете на 2 (за да сумирате всяка друга клетка) и проверете дали резултатът не е нула. Ако е така, клетката се добавя. Тези функции могат да се използват по най-различни начини - някои ще осигурят по-добри резултати от други. Например формула за масив, която да сумира всяка втора клетка в диапазона $ A $ 1: $ A $ 100 може да изглежда така: \u003d SUM (IF (MOD (ROW ($ A $ 1: $ A $ 500); 2) \u003d 0; $ A $ 1: $ A $ 500; 0)), в руската версия на Excel \u003d SUM (IF (OSTAT (LINE ($ A $ 1: $ A $ 500); 2) \u003d 0; $ A $ 1: $ A $ 500; 0)).
Тъй като това е формула на масив, трябва да я въведете, като натиснете Ctrl + Shift + Enter, Excel ще добави фигурни скоби, така че да изглежда така: (\u003d SUM (IF (MOD (ROW ($ A $ 1: $ A $ 500) ), 2) \u003d 0; $ A $ 1: $ A $ 500; 0))), в руската версия на Excel: (\u003d SUM (IF (Оставащо (LINE ($ A $ 1: $ A $ 500); 2) \u003d 0; $ A $ 1: $ A $ 500; 0))) Нуждаете се от Excel, за да добавите къдрави скоби от само себе си; ако ги добавите сами, формулата няма да работи.
Въпреки че целта е постигната, този метод влияе отрицателно върху дизайна. електронна таблица... Това е ненужно приложение на формула на масив. За да бъде нещата още по-лоши, тази дълга формула включва функция за преизчисляване ROW, която също превръща голяма формула във функция за преизчисляване. Това означава, че ще бъде преизчисляван постоянно, независимо какво правите в работната книга. Това е много лош начин!
Ето още една формула, която е леко най-добрият избор: \u003d SUMPRODUCT ((MOD (ROW ($ A $ 1: $ A $ 500); 2) \u003d 0) * ($ A $ 1: $ A $ 500)), в руската версия на Excel \u003d SUMPRODUCT ((ОСТАНАЛО (РЯД ($ A $ 1: $ A $ 500); 2) \u003d 0) * ($ A $ 1: $ A $ 500)).
Не забравяйте обаче, че тази формула ще върне #VALUE! (#VALUE!) Ако някои клетки в диапазона съдържат текст вместо числа. Тази формула, макар всъщност да не е формула на масив, също се забавя работа в Excelако го използвате твърде много пъти или ако се отнася за голям обхват всеки път.
За щастие има по най-добрия начин, което е не само по-ефективно, но и много по-гъвкаво. Това изисква използването на функцията DSUM. В този пример използвахме диапазона A1: A500 като диапазон, над който трябва да се добави всяка n-та клетка.
Въведете думата Критерии в клетка E1. Въведете следната формула в клетка E2: \u003d MOD (ROW (A2) - $ C $ 2-1; $ C $ 2) \u003d 0, в руската версия на Excel \u003d OSTAT (ROW (A2) - $ C $ 2- 1; $ C $ 2) \u003d 0. Изберете клетка C2 и изберете командата Data → Validation.
В полето Разрешаване изберете Списък и в полето Източник въведете 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Уверете се, че квадратчето е отметнато. (В клетката) и щракнете върху бутона OK. В клетка C1 въведете текста SUM на всеки .... Във всяка клетка, различна от ред 1, въведете следната формула: \u003d DSUM ($ A: $ A; 1; $ E $ 1: $ E $ 2), в руската версия на Excel \u003d BDSUMM ($ A: $ A; 1; $ E $ 1: $ E $ 2).
В клетката директно над клетката, където сте въвели функцията DSUM, въведете text \u003d "Summing Every" & $ C $ 2 & CHOOSE ($ C $ 2; "st"; "nd"; "rd"; "th" ; "th"; "th"; "th"; "th"; "th"; "th") & "Cell". Сега остава само да изберете желаното число в клетка C2, а функцията DSUM ще свърши останалото.
Използвайки функцията BDSUM (DSUM), можете да сумирате клетки в указания от вас интервал. Функцията DSUM е много по-ефективна от формула на масив или функцията SUMPRODUCT. Въпреки че отнема малко повече време за настройка, това е случаят, когато е труден за учене, лесен за борба.
По-рано описах как да намеря. За съжаление тази функция не работи, ако клетките са оцветени с условно форматиране... Обещах да "усъвършенствам" функцията. Но през изминалите две години от публикуването на тази бележка не успях да напиша смилаем код нито самостоятелно, нито използвайки информация от Интернет ... ( Актуализация на 29 март 2017 г. След още пет години все пак успях да напиша кода; вижте последната част на бележката). И съвсем наскоро попаднах на идея, съдържаща се в книгата на Д. Хоули, Р. Хоули „Excel 2007. Трикове“, която ви позволява да правите без код.
Нека има списък с числа от 1 до 100, разположени в диапазона A1: A100 (фиг. 1; вижте също листа "SUMIF" на файла Excel). Диапазонът е форматиран условно, за да маркира клетки, които съдържат числа, по-големи от 10 и по-малки или равни на 20.
Фигура: 1. Обхват от числа; условно форматиране маркирани клетки, съдържащи стойности от 10 до 20
Изтеглете бележка във формат, примери във формат
Сега трябва да добавите стойностите в клетките, които отговарят на току-що зададените критерии. Няма значение какъв вид форматиране се прилага към тези клетки, но трябва да знаете критериите, по които са маркирани клетките.
За да добавите набор от клетки, които съвпадат един критерий, можете да използвате функцията SUMIF (фиг. 2).
Фигура: 2. Сумиране на клетки, които отговарят на едно условие
Ако имате няколко условия, можете да използвате функцията SUMIFS (фиг. 3).
Фигура: 3. Сумиране на клетки, които отговарят на няколко условия
Можете да използвате функцията COUNTIF, за да преброите броя на клетките, които отговарят на един критерий.
Можете да използвате функцията COUNTIF, за да преброите броя на клетките, които отговарят на множество критерии.
Excel предоставя друга функция, която ви позволява да зададете множество условия. Тази функция е включена в набора от функции на бази данни в Excel и се нарича BDSUMM. За да го проверите, използвайте същия набор от числа в диапазона A2: A100 (фиг. 4; вижте също листа „BDSUMM“ на файла на Excel).
Фигура: 4. Използване на функции на база данни
Изберете клетки C1: D2 и дайте име на този критерий за диапазон, като го напишете в полето за име отляво на лентата с формули. Сега изберете клетка C1 и въведете \u003d $ A $ 1, което е препратка към първата клетка на листа, съдържаща името на базата данни. Въведете \u003d $ A $ 1 в клетка D1 и ще получите две копия на заглавието на колоната А. Тези копия ще се използват като заглавки за условията BDSUMM (C1: D2), които сте нарекли Критерий. В клетка C2 въведете\u003e 10. В клетка D2 въведете<=20. В ячейке, где должен быть результат, введите следующую формулу:
BDSUMM ($ A $ 1: $ A $ 101,1, критерий)
Можете да използвате функцията COUNT, за да преброите броя на клетките, които отговарят на множество критерии.
Четейки книгата на Джон Уокенбах, научих, че като се започне с Excel 2010, VBA има ново свойство DisplayFormat (вижте например свойството Range.DisplayFormat). Тоест VBA може да чете формата, показан на екрана. Няма значение как е получено чрез директни потребителски настройки или чрез условно форматиране. За съжаление, MS направи така, че свойството DisplayFormat да работи само в процедури, извикани от VBA, а UDF, базирани на това свойство, дават #VALUE! Можете обаче да получите сумата от стойности в диапазон от клетки с определен цвят, като използвате процедура (макрос, но не и функция). Отворен (съдържа VBA код). Преминете през менюто Изглед -> Макроси -> Макроси; в прозореца Макро, маркирайте линията SumColorUslи натиснете Изпълни... Изпълнете макроса, изберете обхвата на сумиране и критерия. Отговорът ще се появи в прозореца.
Код на процедурата
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" Изберете обхват на сумиране, _ Заглавие: \u003d "Избор на диапазон", _ По подразбиране: \u003d ActiveCell.Address, _ Тип: \u003d 8) "Задаване на критерий за заявка CriterionRange \u003d Application.InputBox (_ Подкана: \u003d" Избор критерий за сумиране", _ Заглавие: \u003d" Избор на критерий ", _ По подразбиране: \u003d ActiveCell.Address, _ Тип: \u003d 8)" Сума от "правилни" клетки за всеки i в UserRange If i.DisplayFormat.Interior.Color \u003d _ CriterionRange.DisplayFormat Вътрешност.Цвет Тогава SumColor \u003d SumColor + i End Ако следващ MsgBox SumColor End Sub
Sub SumColorUl () Приложение. Летливи Вярно Dim SumColor As Double Dim i As Range Затъмнете обхвата на потребителя като обхват Dim CriterionRange As Range SumColor \u003d 0 "Заявка за обхват Задайте UserRange \u003d Application.InputBox (_ Подкана: \u003d "Изберете обхват на сумиране", _ Заглавие: \u003d "Избор на диапазон", _ По подразбиране: \u003d ActiveCell.Address, _ Тип: \u003d 8) "Проскриптор Задайте CriterionRange \u003d Приложение. InputBox (_ Подкана: \u003d „Изберете критерий за сумиране“, _ Заглавие: \u003d "Избор на критерий", _ По подразбиране: \u003d ActiveCell. Адрес, _ |
Да предположим, че имате отчет за продажбите по следния начин:
От него трябва да разберете колко моливи продава се от търговски представител Иванов в януари.
ПРОБЛЕМ: Как да обобщим данните по няколко критерия ??
РЕШЕНИЕ: Метод 1:
BDSUMM (A1: G16; F1; I1: K2)
В английската версия:
DSUM (A1: G16, F1, I1: K2)
КАК РАБОТИ:
От базата данни, която посочихме A1: G16 функция BDSUMM извлича и обобщава данни за колони номер (аргумент " Поле" = F1) според даденото в клетки I1: K2 (Продавач \u003d Иванов; Продукти \u003d Моливи; Месец \u003d януари) критерии.
ПРОТИВИ: Списъкът с критерии трябва да бъде на листа.
ЗАБЕЛЕЖКИ: Броят на критериите за сумиране е ограничен от RAM.
ОБЛАСТ НА ПРИЛОЖЕНИЕ: Всяка версия на Excel
Метод 2:
ПРОДУКТ ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2) * F2: F16)
В английската версия:
ПРОДУКТ ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2) * F2: F16)
КАК РАБОТИ:
Функцията SUMPRODUCT формира масиви от TRUE и FALSE стойности, в съответствие с избраните критерии, в паметта на Excel.
Ако изчисленията бяха извършени в клетките на листа (за по-голяма яснота ще покажа цялата работа на формулата, сякаш изчисленията се извършват върху листа, а не в паметта), тогава масивите ще изглеждат така:
Очевидно е, че ако например D2 \u003d моливи, тогава стойността ще бъде TRUE и ако D3 \u003d Папки, след това FALSE (тъй като критерият за избор на продукт в нашия пример е стойността Моливите).
Знаейки, че TRUE винаги е равно на 1, а FALSE винаги е равно на 0, ние продължаваме да работим с масиви както с числа 0 и 1.
Умножавайки последователно получените стойности на масивите, получаваме ЕДИН масив от нули и единици. Когато и трите критерия за подбор са изпълнени, ( ИВАНОВ, МАСИЛКИ, ЯНУАРИ) т.е. всички условия взеха стойностите TRUE, получаваме 1 (1 * 1 * 1 \u003d 1), но ако поне едно условие не е изпълнено, получаваме 0 (1 * 1 * 0 \u003d 0; 1 * 0 * 1 \u003d 0; 0 * 1 * 1 \u003d 0).
Сега остава само да умножим получения масив по масив, съдържащ данни, които трябва да обобщим като резултат (диапазон F2: F16) и всъщност сумираме това, което не се умножава по 0.
Сега сравнете масивите, получени с помощта на формулата и по време на поетапното изчисляване на листа (маркирано в червено).
Мисля, че всичко е ясно :)
МИНУСИ: ПРОДУКТ - „тежка“ формула на масива. Когато се изчисляват големи интервали от данни, времето за преизчисляване значително се увеличава.
ЗАБЕЛЕЖКИ
ОБЛАСТ НА ПРИЛОЖЕНИЕ: Всяка версия на Excel
Метод 3: Формула на масив
SUM (IF ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2); F2: F16))
В английската версия:
SUM (IF ((B2: B16 \u003d I2) * (D2: D16 \u003d J2) * (A2: A16 \u003d K2), F2: F16))
КАК РАБОТИ: По същия начин като Метод №2. Има само две разлики - тази формула се въвежда чрез натискане Ctrl + Shift + Enterа не просто натискане Въведете и масивът 0-ти и 1-q не се умножава по обхвата на сумиране, а се избира с помощта на функцията IF.
МИНУСИ: Формулите на масиви при изчисляване на големи диапазони от данни значително увеличават времето за преизчисляване.
ЗАБЕЛЕЖКИ: Броят на обработените масиви е ограничен до 255.
ОБЛАСТ НА ПРИЛОЖЕНИЕ: Всяка версия на Excel
Метод 4:
SUMIFN (F2: F16; B2: B16; I2; D2: D16; J2; A2: A16; K2)