Логические функции EXCEL

РАСШИРЕНИЕ ФУНКЦИОНАЛА С ПОМОЩЬЮ ОПЕРАТОРОВ « И » и « ИЛИ »

Когда нужно проверить несколько истинных условий, используется функция И .

Суть такова : ЕСЛИ а = 1 И а = 2 ТОГДА значение X ИНАЧЕ значение Y.

Функция ИЛИ проверяет условие 1 или условие 2.

Как только хотя бы одно условие истинно, то результат будет истинным.

Суть такова : ЕСЛИ а = 1 ИЛИ а = 2 ТОГДА значение X ИНАЧЕ значение Y.

Функции И и ИЛИ могут проверить до 30 условий.

Аргументы функций И, ИЛИ, НЕ могут быть логическими выражениями, массивами или ссылками на ячейки, содержащие логические значения.

Приведем пример.

Функции ИСТИНА и ЛОЖЬ

Функции ИСТИНА (TRUE) и ЛОЖЬ (FALSE) предоставляют альтернативный способ записи логических значений ИСТИНА и ЛОЖЬ. Эти функции не имеют аргументов и выглядят следующим образом:

=ИСТИНА() =ЛОЖЬ()

Например, ячейка А1 содержит логическое выражение. Тогда следующая функция возвратить значение "Проходите", если выражение в ячейке А1 имеет значение ИСТИНА:

=ЕСЛИ(А1=ИСТИНА();"Проходите";"Стоп")

В противном случае формула возвратит "Стоп".

Функция ЕПУСТО

Если нужно определить, является ли ячейка пустой, можно использовать функцию ЕПУСТО (ISBLANK), которая имеет следующий синтаксис:

=ЕПУСТО(значение)

Аргумент значение может быть ссылкой на ячейку или диапазон. Если значение ссылается на пустую ячейку или диапазон, функция возвращает логическое значение ИСТИНА , в противном случае ЛОЖЬ.

В элек­трон­ную таб­ли­цу за­нес­ли дан­ные о те­сти­ро­ва­нии учеников. Ниже при­ве­де­ны пер­вые пять строк таблицы:

1

A

B

округ

2

C

C

3

фамилия

4

предмет

Ученик 1

D

В

Ю

5

Ученик 2

балл

обществознание

немецкий язык

СВ

Ученик 3

246

русский язык

530

Ученик 4

576

обществознание

304

В столб­це А за­пи­сан округ, в ко­то­ром учит­ся ученик; в столб­це В — фамилия; в столб­це С — любимый предмет; в столб­це D — тестовый балл. Всего в элек­трон­ную таб­ли­цу были за­не­се­ны дан­ные по 1000 ученикам.

  Выполните задание.

• Сколько уче­ни­ков в Во­сточ­ном окру­ге (В) вы­бра­ли в ка­че­стве лю­би­мо­го пред­ме­та информатику ?

2. Каков сред­ний те­сто­вый балл у уче­ни­ков Се­вер­но­го окру­га (С)?

Пояснение.

• За­пи­шем в ячей­ку E2 сле­ду­ю­щую фор­му­лу  =ЕСЛИ(A2="В";C2;0)  и ско­пи­ру­ем ее в диа­па­зон E3:E1001.
• В ячей­ку столб­ца E будет за­пи­сы­вать­ся на­зва­ние предмета, если уче­ник из Во­сточ­но­го окру­га и «0», если это не так.
• При­ме­нив опе­ра­цию  =ЕСЛИ(E2="информатика";1;0), по­лу­чим стол­бец (F) : с еди­ни­ца­ми и нулями.
• Далее, ис­поль­зу­ем опе­ра­цию  =СУММ(F2:F1001). По­лу­чим ко­ли­че­ство учеников, ко­то­рые счи­та­ют своим лю­би­мым пред­ме­том информатику.

• Для от­ве­та на вто­рой во­прос ис­поль­зу­ем опе­ра­цию «ЕСЛИ».
• За­пи­шем в ячей­ку G2 сле­ду­ю­щее выражение:  =ЕСЛИ(A2="С";D2;0), в ре­зуль­та­те при­ме­не­ния дан­ной опе­ра­ции к диа­па­зо­ну ячеек G2:G1001 , по­лу­чим столбец, в ко­то­ром за­пи­са­ны баллы толь­ко уче­ни­ков Се­вер­но­го округа.

• Сло­жив зна­че­ния в ячейках , по­лу­чим сумму бал­лов учеников: 56 737.

• Найдём ко­ли­че­ство уче­ни­ков Се­вер­но­го окру­га с по­мо­щью ко­ман­ды  =СЧЁТЕСЛИ(A2:A1001;"С"), по­лу­чим 105.

• Раз­де­лив сумму бал­лов на ко­ли­че­ство учеников , получим: 540,352 —  ис­ко­мый сред­ний балл.

В элек­трон­ную таб­ли­цу за­нес­ли дан­ные о ка­ло­рий­но­сти продуктов. Ниже при­ве­де­ны пер­вые пять строк таблицы:

A

1

B

Продукт

2

3

C

Жиры, г

Арахис

D

Белки, г

4

45,2

Арахис жареный

52

Горох отварной

5

26,3

Углеводы, г

E

0,8

Горошек зелёный

26

9,9

Калорийность, Ккал

0,2

10,5

13,4

552

5

20,4

626

8,3

130

55

В столб­це А за­пи­сан продукт; в столб­це В — содержание в нём жиров; в столб­це С — содержание белков; в столб­це D — содержание уг­ле­во­дов и в столб­це Е — калорийность этого продукта.

  Выполните задание .

1. Сколь­ко про­дук­тов в таб­ли­це со­дер­жат мень­ше 5 г жиров и мень­ше 5 г белков? За­пи­ши­те число этих про­дук­тов в ячей­ку Н2 таблицы.

2. Ка­ко­ва сред­няя ка­ло­рий­ность про­дук­тов с со­дер­жа­ни­ем жиров 0 г? Ответ на этот во­прос за­пи­ши­те в ячей­ку НЗ таб­ли­цы с точ­но­стью не менее двух зна­ков после запятой.

Пояснение.

  1. За­пи­шем в ячей­ку G2 сле­ду­ю­щую формулу  =ЕСЛИ(И(B2  и ско­пи­ру­ем ее в диа­па­зон G3:G1001. В таком случае, в ячей­ку столб­ца G будет за­пи­сы­вать­ся единица, если про­дукт со­дер­жит мень­ше 5 г жиров и мень­ше 5 г белков.

2. При­ме­нив опе­ра­цию  =СУММ(G2:G1001) , по­лу­чим ответ: 394.

• За­пи­шем в ячей­ку J2 сле­ду­ю­щее выражение:  =СУММЕСЛИ(B2:B1001;0;E2:E1001) , в ре­зуль­та­те по­лу­чим сумму ка­ло­рий с ну­ле­вым со­дер­жа­ни­ем жиров: 10 628.
• При­ме­нив опе­ра­цию  =СЧЁТЕСЛИ(B2:B1001;0 ), по­лу­чим ко­ли­че­ство про­дук­тов с ну­ле­вым со­дер­жа­ни­ем жиров: 113.

• Разделив, по­лу­чим сред­нее зна­че­ние про­дук­тов с со­дер­жа­ни­ем жиров 0 г: 94,05.

№ 19

На основании данных, содержащихся в таблице, ответьте на 2 вопроса:

• Сколько продуктов в таблице содержат меньше 50г углеводов и меньше 50г белков? Запишите число этих продуктов в ячейку Н2 таблицы.

A

1

Продукт

B

2

Арахис

3

Жиры, г

C

Белки, г

Арахис жареный

45,2

4

D

E

52

Горох отварной

Углеводы, г

5

26,3

Горошек зелёный

Калорийность, Ккал

26

9,9

0,8

13,4

552

10,5

0,2

5

626

20,4

130

8,3

55

F2: =ЕСЛИ(И(C2

H2: =СУММ(F2:F1001)

№ 19

2) Какова средняя калорийность продуктов с содержанием жира менее 1г? Ответ запишите в ячейку Н3 с точностью менее менее двух знаков после запятой.

A

1

Продукт

2

B

C

3

Жиры, г

Арахис

Арахис жареный

Белки, г

4

45,2

D

Горох отварной

26,3

5

E

52

Углеводы, г

Горошек зелёный

Калорийность, Ккал

26

0,8

9,9

13,4

10,5

552

0,2

626

5

20,4

130

8,3

55

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

= СЧЁТЕСЛИ (диапазон; критерий) – подсчитывает количество непустых ячеек в диапазоне, удовлетворяющих заданному условию

H3: = СУММЕСЛИ (B2:B1001;”СЧЁТЕСЛИ (В2:В1001;”