Използване на критерии в Microsoft Excel

Microsoft Excel не е само редактор на електронни таблици, но и мощно приложение за различни изчисления. Не на последно място, тази функция се дължи на вградените функции. С помощта на определени функции (оператори) е възможно да се определят дори и условията за изчисление, които се наричат ​​критерии. Нека научим по-подробно как да ги използваме, когато работите в Excel.

Прилагане на критериите

Критериите са условията, при които програмата изпълнява определени действия. Те се използват в редица вградени функции. В тяхното име най-често присъства изразът "IF" . За тази група оператори, на първо място, е необходимо да се включат СТРАНИ , СМЕТКИ , SUM , SUMMES . В допълнение към вградените оператори критериите в Excel се използват и за условно форматиране. Нека разгледаме тяхното приложение при работа с различни инструменти на даден процесор за таблици в повече подробности.

COUNTIF

Основната задача на оператора COUNTIF , която се отнася до статистическа група, е броят на клетките, заети от различни стойности, които отговарят на определено предварително определено условие. Синтаксисът му е както следва:

=СЧЁТЕСЛИ(диапазон;критерий)

Както можете да видите, този оператор има два аргумента. "Обхват" е адресът на масив от елементи в листа, в който да се брои.

"Критерий" е аргумент, който определя условието, че трябва да съдържа клетките на определената област, които да бъдат включени в броя. Като параметър можете да използвате цифров израз, текст или позоваване на клетката, в която се съдържа критерият. В този случай можете да използвате следните знаци, за да посочите критерия: "<" ( "less" ), ">" ( "more" ), "=" ( "equal" ), "<>" . Например, ако посочите израза "<50" , при изчисляването ще бъдат взети предвид само елементите, определени от аргумента "Обхват" , в който цифровите стойности са по-малки от 50. Използването на тези знаци за уточняване на параметрите ще бъде от значение за всички други опции, които ще бъдат обсъдени в този урок по-долу.

И сега нека да разгледаме конкретен пример как този оператор работи на практика.

Така че има таблица, която показва приходите от пет магазина на седмица. Трябва да знаем броя на дните за този период, в които в Shop 2 приходите от продажби надвишават 15 000 рубли.

1. Изберете елемента на листа, в който операторът ще отпечата резултата от изчислението. След това кликнете върху иконата "Вмъкване на функция" .
2. Стартира съветникът за функции . Преминаваме към "статистическия" блок. Там намираме и различаваме името "COUNTRIES" . След това кликнете върху бутона "OK" .
3. Показва се прозорецът с аргументи на горепосочения оператор. В полето "Обхват" посочете областта на клетките, между които ще бъде извършено изчислението. В нашия случай трябва да изберете съдържанието на реда "Магазин 2" , в който се намират приходите на ден. Поставете курсора в определеното поле и, като задържите левия бутон на мишката, изберете съответния масив в таблицата. Адресът на избрания масив ще се покаже в прозореца.

   В следващото поле "Критерий" просто трябва да зададете параметър за директен избор. В нашия случай трябва да броим само тези таблични елементи, в които стойността надвишава 15 000. Затова използваме клавиатурата, за да въведем в посоченото поле израз "> 15000" .

   След като направите всички описани по-горе манипулации, кликнете върху бутона "OK" .

4. Програмата изчислява и показва резултата в елемент от листа, който е бил осветен преди активирането на съветника за функции . Както можете да видите, в този случай резултатът е равен на числото 5. Това означава, че в избран масив в пет клетки има стойности над 15 000. Това означава, че можем да заключим, че в магазин 2 за пет дни от анализираните седем, приходите надхвърлили 15 000 рубли.

Урокът: Съветник за функциите в програмата Excel

SCHOTESLIMN

Следващата функция, която работи с критериите, е COUNTRY . Той също така се позовава на статистическата група от оператори. Задачата на COUNTERMAN е преброяването на клетките в посочения масив, които отговарят на определен набор от условия. Това е фактът, че не можете да посочите нито един, а няколко параметъра, и да различава този оператор от предишния. Синтаксисът е:

=СЧЁТЕСЛИМН(диапазон_условия1;условие1;диапазон_условия2;условие2;…)

"Обхватът на условия" е идентичен на първия аргумент от предходното изявление. Това означава, че става дума за зоната, в която клетките, които отговарят на определените условия, ще бъдат преброени. Този оператор ви позволява да посочите няколко такива зони наведнъж.

"Условие" е критерий, който определя кои елементи от съответния масив от данни ще се включат в изчислението и кои от тях няма. Всяка дадена област за данни трябва да бъде определена отделно, дори и да съответства. Необходимо е всички маси, използвани като условни зони, да имат еднакъв брой редове и колони.

За да определите няколко параметъра на една и съща зона за данни, например, за да преброите броя на клетките, в които стойностите са по-големи от определен брой, но по-малки от друг номер, трябва да посочите същия масив няколко пъти, както аргумента "Обхват на условието" , Въпреки това, трябва да се използват различни критерии като съответните аргументи "Състояние" .

На пример за една и съща маса с седмични приходи от магазини ще видим как работи. Необходимо е да знаем броя на дните от седмицата, когато доходите във всички посочени обекти достигнаха установения за тях процент. Тарифите на приходите са, както следва:

• Магазин 1 - 14000 рубли;
• Магазин 2 - 15 000 рубли;
• Магазин 3 - 24000 рубли;
• Магазин 4 - 11 000 рубли;
• Магазин 5 - 32000 рубли.

1. За да изпълните горната задача, маркирайте курсора с елемента на работния лист, към който ще се изведе резултата от обработката на данните за COUNTRY . Кликваме върху иконата "Вмъкване на функция" .
2. Преминавайки към помощника за функциите , върнете се към "статистическия" блок. В списъка е необходимо да се намери името на COUNTRY FORM и да се направи изборът. След извършване на това действие, трябва да кликнете върху бутона "OK" .
3. След изпълнението на горепосочения алгоритъм от действия се отваря прозорецът на параметрите COUNTLINES .

   В полето "Обхват на условие1" въведете адреса на реда, в който се намират данните за приходите на магазин 1 на седмица. За да направите това, поставете курсора в полето и изберете съответния ред в таблицата. Координатите се показват в прозореца.

   Като се има предвид, че за дневния размер на Store 1 е 14 000 рубли, тогава в полето "Условие 1" въвеждаме израза "> 14000" .

   В полето "Обхват на условията2 (3,4,5)" трябва да въведете координатите на линиите със седмични постъпления в съответствие със Съхранение 2, Съхранение 3, Съхранение 4 и Съхранение 5. Действието се изпълнява по същия алгоритъм като първия аргумент от тази група.

   В полетата "Състояние2" , "Състояние3" , "Състояние4" и "Състояние5" влизаме съответно стойностите "> 15000 ", "> 24000" , "> 11000" и "> 32000" . Както може да предположите, тези стойности съответстват на интервала на приходите над нормата за съответния магазин.

   След въвеждане на всички необходими данни (общо 10 полета) кликнете върху бутона "OK" .

4. Програмата изчислява и показва резултата на екрана. Както можете да видите, то е равно на числото 3. Това означава, че в три дни от анализираната седмица приходите във всички пазари надвишават установения за тях процент.

Сега нека да променим проблема донякъде. Трябва да изчислим броя дни, в които магазин 1 получи приходи над 14 000 рубли, но по-малко от 17 000 рубли.

1. Поставяме курсора в елемента, в който ще се извежда резултатът от изчисленията. Кликваме върху иконата "Вмъкване на функция" над работната площ на листа.
2. Тъй като наскоро приложихме формулата COUNCILS , сега не е необходимо да отидете в групата на "статистическите" магьосници на функциите . Името на този оператор може да бъде намерено в категорията "10 наскоро използвана" . Изберете го и кликнете върху бутона "OK" .
3. Отваря прозореца на аргументите на оператора СЧиТЕСЛИМН, който вече ни е познат. Поставяме курсора в полето "Обхват на състояние1" и след като сте натиснали левия бутон на мишката, изберете всички клетки, които съдържат приходите от дните на Store 1. Те ​​се намират в реда, който се нарича "Магазин 1" . След това координатите на посочената област ще бъдат отразени в прозореца.

   След това поставете курсора в полето "Състояние1" . Тук трябва да посочим долната граница на стойностите в клетките, които ще участват в изчислението. Посочете израза "> 14000" .

   В полето "Обхват на условията2" въвеждаме същия адрес по същия начин, както е въведен в полето "Обхват на състояние1" , т.е. отново въвеждаме координатите на клетките с стойностите на приходите в първата точка на продажба.

   В полето "Условие2" посочваме горната граница на селекцията: "<17000" .

   След като направите всички гореописани действия, кликнете върху бутона "OK" .

4. Програмата извежда резултата от изчислението. Както можете да видите, крайната стойност е равна на 5. Това означава, че за 5 дни от анализираните седем, приходите в първия магазин са в диапазона от 14 000 до 17 000 рубли.

SUMIF

Друг оператор, който използва критериите, е ЛЕТЕН . За разлика от предишните функции, той се отнася до математическия блок на операторите. Неговата задача е да обобщи данните в клетките, които съответстват на определено състояние. Синтаксисът е:

=СУММЕСЛИ(диапазон;критерий;[диапазон_суммирования])

Аргументът "Обхват" показва областта на клетките, която ще бъде проверена за съответствие с условието. Всъщност тя се определя от същия принцип, както и от същия аргумент на функцията на Съвета .

"Критерий" е задължителен аргумент, който указва параметъра за избор на клетка от определената област за данни, който трябва да бъде сумиран. Принципите на индикация са същите като тези на аналогичните аргументи на предишните оператори, които разгледахме по-горе.

"Обхват на сумиране" е незадължителен аргумент. Показва конкретната област на масива, в която ще се извърши сумирането. Ако го пропуснете и не го посочите, по подразбиране се счита, че той е равен на стойността на необходимия аргумент "Обхват" .

Сега, както винаги, помислете за приложението на този оператор на практика. Въз основа на същата таблица, ние сме изправени пред задачата за изчисляване на размера на приходите в Магазин 1 за периода, започващ от 11.03.2017 г.

1. Изберете клетката, в която ще се изведе резултатът. Кликваме върху иконата "Вмъкване на функция" .
2. Отидете в съветника за функции в "Математически" блок и намерете името "ЛЕТНИ" . Кликнете върху бутона "OK" .
3. Отваря се прозорецът с аргументи на функцията SUMIFER . Има три полета, съответстващи на аргументите на конкретния оператор.

   В полето "Обхват" въведете областта на таблицата, в която ще се намират стойностите, които ще се проверят за съответствие с условията. В нашия случай това ще бъде поредица от дати. Поставяме курсора в това поле и избираме всички клетки, съдържащи датите.

   Тъй като трябва да добавим само приходите от 11 март, в полето "Критерий" въведете стойността "> 10.03.2017" .

   В полето "Обхват на сумиране" трябва да посочите област, чиито стойности, съответстващи на зададените критерии, ще бъдат сумирани. В нашия случай това са стойностите на приходите на ред "Shop1" . Изберете подходящия масив от елементи на листа.

   След като въведете всички зададени данни, кликнете върху бутона "OK" .

4. След това резултатът от обработката на данните чрез функцията SUMMER ще се покаже в предварително зададения елемент на работния лист. В нашия случай тя е 47921.53. Това означава, че от 11.03.2017 г. до края на анализирания период общите приходи за магазин 1 са 47 921.53 рубли.

SUMIFS

Ние завършихме проучването на операторите, които използват критериите, спирайки на функцията на РЕЗЮМЕ . Задачата на тази математическа функция е сумирането на стойностите на посочените области на таблицата, избрани чрез няколко параметъра. Синтаксисът на конкретния оператор е:

=СУММЕСЛИМН(диапазон_суммирования;диапазон_условия1;условие1;диапазон_условия2;условие2;…)

"Обхват на сумиране" е аргумент, който е адреса на този масив, клетките, в които, съответстващи на определен критерий, ще бъдат добавени.

"Обхват на състояние" е аргумент, представляващ масив от данни, проверявани за съответствие със състоянието;

"Условие" е аргумент, който е критерий за подбор.

Тази функция включва операции с няколко групи подобни оператори.

Нека видим как този оператор е приложим за решаване на проблеми в контекста на нашата таблица с приходите от продажби в търговските обекти. Ще трябва да изчислим дохода, който Shop 1 донесе за периода от 09 до 13 март 2017 г. В същото време при сумирането на приходите трябва да се вземат предвид само тези дни с приходи над 14 000 рубли.

1. Отново изберете клетката, за да видите общата сума и кликнете върху иконата "Вмъкване на функция" .
2. В помощника за функции първо се преместваме в "Математически" блок и там избираме елемент, наречен "SUMMESLIMN" . Кликнете върху бутона "OK" .
3. Прозорецът на аргументите на оператора, чието име е споменато по-горе, се стартира.

   Задайте курсора в полето "Обхват на сумиране" . За разлика от последващите аргументи, този по рода си и насочва към масива от стойности, където ще бъде направено сумиране на данните, отговарящи на определените критерии. След това изберете района на линията "Shop1" , който публикува стойностите на приходите за съответния изход.

   След като адресът се покаже в прозореца, отворете полето "Обхват на състояние1" . Тук ще трябва да покажем координатите на реда с датите. Захващаме левия бутон на мишката и избираме всички дати в таблицата.

   Поставяме курсора в полето "Състояние1" . Първото условие е, че ще обобщим данните не по-рано от 9 март. Затова въвеждаме стойността "> 08.03.2017" .

   Преминаваме към аргумента "Обхват на състояние2" . Тук трябва да въведете същите координати, които са били записани в полето "Обхват на състояние1" . Правим това по същия начин, т.е. като подчертаваме линията с датите.

   Задайте курсора в полето "Състояние2" . Второто условие е, че дните, за които ще бъдат добавени приходите, трябва да бъдат не по-късно от 13 март. Затова написваме следния израз: «<14.03.2017» .

   Отидете в полето "Обхват на условията2" . В този случай трябва да изберете същия масив, чийто адрес е въведен като масив на сумиране.

   След като адреса на посочения масив се покаже в прозореца, отидете в полето "Условие3" . Като се има предвид, че в сумирането ще бъдат включени само стойности, надхвърлящи 14 000 рубли, отбелязваме следния знак: "> 14000" .

   След последното действие кликнете върху бутона "OK" .

4. Програмата извежда резултата на лист. Тя е равна на 62491.38. Това означава, че за периода от 09 до 13 март 2017 г. размерът на приходите, когато беше добавен в дните, когато надхвърли 14 000 рубли, е 62491,38 рубли.

Условно форматиране

Последният инструмент, описан от нас, при работа с кои критерии се използва, е условното форматиране. Извършва определено форматиране на клетките, които отговарят на определените условия. Нека разгледаме пример за работа с условно форматиране.

Изберете клетките в таблицата в синьо, където стойностите за деня надвишават 14 000 рубли.

1. Избираме целия набор от елементи в таблицата, който посочва приходите на търговските обекти по дни.
2. Преминаваме към раздела "Дом" . Кликваме върху иконата "Условно форматиране" , намираща се в блока "Стилове" на касетата. Извежда се списък с действия. Кликваме върху него на позицията "Създаване на правило ..." .
3. Прозорецът за генериране на правило за форматиране е активиран. В областта на избиране на типа правило изберете името "Форматирайте само клетките, които съдържат" . В първото поле на условния блок от списъка с възможни опции изберете "Клетъчна стойност" . В следващото поле изберете позицията "Още" . В последната ние посочваме самата стойност, повече от които трябва да форматираме елементите на таблицата. Имаме 14000. За да изберете типа форматиране, кликваме върху бутона "Формат ..." .
4. Прозорецът за форматиране е активиран. Преместване в раздела "Попълване" . Из предложенных вариантов цветов заливки выбираем синий, щелкая по нему левой кнопкой мыши. После того, как выбранный цвет отобразился в области «Образец» , клацаем по кнопке «OK» .
5. Автоматически происходит возврат к окну генерации правила форматирования. В нём также в области «Образец» отображается синий цвет. Тут нам нужно произвести одно единственное действие: клацнуть по кнопке «OK» .
6. После выполнения последнего действия, все ячейки выделенного массива, где содержится число большее, чем 14000, будут залиты синим цветом.

Более подробно о возможностях условного форматирования рассказывается в отдельной статье.

Урокът: Условное форматирование в программе Эксель

Как видим, с помощью инструментов, использующих при своей работе критерии, в Экселе можно решать довольно разноплановые задачи. Это может быть, как подсчет сумм и значений, так и форматирование, а также выполнение многих других задач. Основными инструментами, работающими в данной программе с критериями, то есть, с определенными условиями, при выполнении которых активируется указанное действие, является набор встроенных функций, а также условное форматирование.

• ← Изграждане на графика на зависимост в Microsoft Excel
• Създаване на график на мрежата в Microsoft Excel →