Нахождение крайних значений

Для нахождения крайних (наибольшего или наименьшего) значений в множестве данных используют функции МАКС() и МИН().

Синтаксис функции: МАКС(А),

где A – список от 1 до 255 элементов, среди которых требуется найти наибольшее значение. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются.

Задание 6. Создание формулы с использованием функции МАКС().

Ваши действия:

1. Введите в ячейки А28:Е28 числа 10, 7, 9, 27, 2, как показано на рис. 7.9.

2. В ячейки А29 и А30 ведите следующие формулы:

=МИН(А28:Е28)

=МИН(А28:Е28; 0)

Результат вычислений показан на рис. 7.9.

Рис. 7.9. Примеры функции МИН()

Функция МИН() – находит наименьшее значение в диапазоне ячеек. Функция МИН() имеет такой же синтаксис, что и функция МАКС(). Функции МАКС() и МИН() только определяют крайние значения, но не показывают, в какой ячейке эти значения находятся.

3. В ячейки А32 и А33 введите следующие формулы:

=МАКС(А28:Е28)

=МАКС(А28:Е28;30)

Результат вычислений показан на рис. 7.10.

Рис. 7.10. Примеры функции МАКС()

В тех случаях, когда требуется найти не самое большое (самое маленькое) значение, а значение, занимающее определенное положение в множестве данных (например, второе или третье по величине), следует использовать функции НАИБОЛЬШИЙ() или НАИМЕНЬШИЙ().

Синтаксис функции: НАИБОЛЬШИЙ(А;В),

где A – список от 1 до 30 элементов, среди которых требуется найти значение. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются;

В – позиция (начиная с наибольшей) в множестве данных. Если требуется найти второе значение по величине, то указывается позиция 2, если третье, то позиция 3 и т. д.

4. В ячейки А36, В36 и С36 введите следующие формулы:

=НАИБОЛЬШИЙ(А28:Е28;2)

=НАИБОЛЬШИЙ(А28:Е28;3)

=НАИБОЛЬШИЙ(А28:Е28;4)

Результаты вычислений показаны на рис. 7.11

Рис. 7.11. Примеры функции НАИБОЛЬШИЙ()

Функция НАИМЕНЬШИЙ() имеет такой же синтаксис, что и функция НАИБОЛЬШИЙ().

5. В ячейки А39, В39 и С39 введите следующие формулы:

=НАИМЕНЬШИЙ(А28:Е28;2)

=НАИМЕНЬШИЙ(А28:Е28;3)

=НАИМЕНЬШИЙ(А28:Е28;4)

Результаты вычислений показаны на рис. 7.12.

Рис. 7.12. Примеры функции НАИМЕНЬШИЙ()

Расчет средних значений

В самом простом случае для расчета среднего арифметического значения используют функцию СРЗНАЧ().

Синтаксис функции: СРЗНАЧ(А),

где A – список от 1 до 255 аргументов, среднее значение которых требуется найти. Аргумент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются.

Если в диапазон, для которого рассчитывают среднее значение, попадают данные, существенно отличающиеся от остальных, расчет простого среднего арифметического может привести к неправильным выводам. В этом случае следует использовать функцию УРЕЗСРЕДНЕЕ. Эта функция вычисляет среднее, отбрасывая заданный процент данных с экстремальными значениями.

Задание 7. Создание формул с использованием функции СРЗНАЧ().

Ваши действия:

1. В ячейки А41:С41 введите числа – 4,18 и 7.

2. В ячейки А42 и А43 введите следующие формулы:

=СРЗНАЧ(А28:Е28)

=СРЗНАЧ(А28:Е28; 5)

Результаты вычислений показаны на рис. 7.13.

Рис. 7.13. Примеры функции СРЗНАЧ()

3. Присвойте диапазону ячеек А28:Е28 имя Баллы

4. В ячейку А44 введите следующую формулу:

=СРЗНАЧ(Баллы)

5. Присвойте диапазону ячеек А41:С41 имя ДругиеБаллы и в ячейку А45 введите формулу:

=СРЗНАЧ(Баллы;ДругиеБаллы)

Результаты вычислений показаны на рис. 7.13.

Расчет количества ячеек

Функция СЧЕТ()служитдля определения количества ячеек, содержащих числовые значения.

Синтаксис функции: СЧЕТ(А),

где A – список от 1 до 255 элементов, среди которых требуется определить количество ячеек, содержащих числовые значения. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются.

Задание 8. Создание формул с использованием функции СЧЕТ().

Ваши действия:

1. Введите в ячейку А47 слово «Продажи», в ячейку А48 «10.04.2010», А49 – пустая, А50 введите целое число – 19, а А51 дробное число – 22,24 (рис. 7.14).

Рис. 7.14. Примеры функции СЧЕТ()

2. В ячейки В47 и В48 введите следующие формулы:

=СЧЕТ(А47:А51)

=СЧЕТ(А49:А51)

Если требуется определить количество ячеек, содержащих любые значения (числовые, текстовые, логические), то следует использовать функцию СЧЕТЗ().

Синтаксис функции: СЧЕТЗ(А),

где A – список от 1 до 255 элементов, среди которых требуется определить количество ячеек, содержащих любые значения. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки игнорируются.

Рис.7.15. Примеры функции СЧЕТЗ()

3. В ячейки А53 и А54 введите следующие формулы:

=СЧЕТЗ(А47:А51)

=СЧЕТЗ(А49:А51)

Результат вычислений показан на рис. 7.15.

Наоборот, если требуется определить количество пустых ячеек, следует использовать функцию СЧИТАТЬПУСТОТЫ().

Синтаксис функции: СЧИТАТЬПУСТОТЫ(А),

где А – список от 1 до 255 элементов, среди которых требуется определить количество пустых ячеек. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на ячейки с нулевыми значениями игнорируются.

4. В ячейки А56 введите следующую формулу:

= СЧИТАТЬПУСТОТЫ(А47:А51)

Рис. 7.16. Пример функции СЧИТАТЬПУСТОТЫ()

Результат вычислений показан на рис. 7.16.

Можно также определять количество ячеек, отвечающих заданным условиям. Для этого используют функцию СЧЕТЕСЛИ().

Синтаксис функции: СЧЕТЕСЛИ(А;В),

где А – диапазон проверяемых ячеек;

В – критерий в форме числа, выражения или текста, определяющего суммируемые ячейки.

5. В ячейки А56 и В56 введите следующие формулы:

=СЧЕТЕСЛИ(В12:В14;500)

=СЧЕТЕСЛИ(С12:С14;900)

Рис. 7.17. Примеры функции СЧЕТЕСЛИ()

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

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

Вместо функций ЛОЖЬ и ИСТИНА можно непосредственно ввести слово с клавиатуры в ячейку или в формулу.

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

Таблица 7.1

Операторы сравнения

Оператор	Значение
= (знак равенства)	Равно
> (знак больше)	Больше
< (знак меньше)	Меньше
>= (знак больше и равно)	Больше или равно
<= (знак меньше и равно)	Меньше или равно
<> (знак меньше и больше)	Не равно

 

Рассмотрим несколько логических функций: ЕСЛИ(), И(), ИЛИ(), НЕ().

Функция ЕСЛИ()

Функция ЕСЛИ() – изменяется в зависимости от того, истинным или ложным является значение при вычислении условия, и в соответствии с этим возвращает различные значения.

Синтаксис функции: ЕСЛИ(А;В;С),

где A – логическое выражение, правильность которого следует проверить,

В – значение, если логическое выражение истинно,

C – значение, если логическое выражение ложно.

Другими словами, функция ЕСЛИ() оценивает логическое выражение А, и если оно истинно, возвращается первое значение – В, если ложно, то возвращает второе значение – С.

Задание 10. Создание формул с использованием функции   ЕСЛИ().

Ваши действия:

1. Откройте файл Упражнения.xlsx.

2. Перейдите на лист Упр.8.

3. Введите в ячейки F3: H6 исходные данные, как показано на рис. 7.19.

Рис. 7.19. Пример 1 функции ЕСЛИ()

4. В ячейку I4 введите формулу:

=ЕСЛИ(G4>H4;"Превышение бюджета";"Все хорошо!")

5. Введите в ячейки F7: G12 исходные данные, показанные на рис. 7.20.

6. В ячейку H8 введите формулу:

=ЕСЛИ(G8>1000;G8*20%;G8*10%)

7. В ячейку I8 введите формулу:

=ЕСЛИ(G8>=1000;G8*20%;G8*10%)

Рис. 7.20. Пример 2 функции ЕСЛИ()

8. В ячейку F14 (рис. 7.21) введите величину вклада, например, 5000р.

Рис. 7.21. Пример 3 функции ЕСЛИ()

9. В ячейку G14 введите формулу расчета величины вклада, учитывая ниже приведенные значения процентов: если сумма вклада будет меньше 10000р., то банк начислит 10%, если размер вклада находится в пределах от 10000р. до 20000р. – 12%, от 20000р. до 30000р. – 14%, от 30000р. до 40000р. – 17%, и если больше 40000р. – 20%.

=ЕСЛИ(F14<=10000;F14+F14*10%;ЕСЛИ(F14<=20000;F14+F14*12%;ЕСЛИ(F14<=30000;F14+F14*14%;ЕСЛИ(F14<=40000;F14+F14*17%;ЕСЛИ(F14>40000;F14+F14*20%)))))

Функция НЕ()

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

Таблица 7.2

Таблица истинности для функции НЕ()

Значение аргумента	Значение функции
х	НЕ(х)
ЛОЖЬ	ИСТИНА
ИСТИНА	ЛОЖЬ

Таблица 7.3

Таблица истинности для функций И() и ИЛИ()

Значение аргумента		Значение функции
x	y	И(х,у)	ИЛИ(х,у)
ЛОЖЬ	ЛОЖЬ	ЛОЖЬ	ЛОЖЬ
ЛОЖЬ	ИСТИНА	ЛОЖЬ	ИСТИНА
ИСТИНА	ЛОЖЬ	ЛОЖЬ	ИСТИНА
ИСТИНА	ИСТИНА	ИСТИНА	ИСТИНА

 

Функция НЕ() меняет на противоположное логическое значение своего аргумента. Функция еще называется функцией отрицания или инверсии, поскольку ее значение всегда противоположно значению аргумента. Функция НЕ() используется в тех случаях, когда необходимо быть уверенным в том, что значение не равно некоторой конкретной величине:

НЕ(1+1=2) равняется ЛОЖЬ

Функция И()

Функция И() называется функцией логического умножения и имеет следующий синтаксис:

И(условие1;условие2;…)

Функция И() возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ (см. табл. 8.2).

Задание 11. Создание формул с использованием функции И().

Ваши действия:

1. В ячейку F16 введите следующую формулу:

=И(2+2=4;2+3=5)

2. В результате в ячейке появится слово ИСТИНА (рис. 8.4), потому что оба выражения являются правильными.

3. Скопируйте формулу из ячейки F16 в ячейку F17.

4. Измените в ячейке F17 значение любого условия в формуле, чтобы в этой ячейке появилось слово ЛОЖЬ (см. рис. 8.4).

5. Введите в ячейку G16 число находящееся в диапазоне между 1 и 100, например, 56.

6. В ячейку H16 введите формулу:

=И(G16>1;G16<100)

7. В результате в ячейке H16 появится слово ИСТИНА (см. рис. 8.4).

8. Скопируйте формулу из ячейки H16 в ячейку H17, затем в ячейку G17 введите число 109. В результате в ячейке H17 появится слово ЛОЖЬ (см. рис. 8.4).

9. В ячейку I16 введите формулу:

=ЕСЛИ(И(G16>1;G16<100);G16;"Значение вне интервала")

10. Скопируйте формулу из ячейки I 16 в ячейку I 20. В результате в ячейках I 16 и I 17 будут разные значения (см. рис. 7.22).

Рис. 7.22. Примеры функции И()

11. В ячейки F18: H28 (рис. 7.23) введите исходные данные для выявления факта пригодности призывников к военной службе (Годен/Не годен). Например, отклоняются кандидатуры ростом менее 150 см или более 200 см, а также весом менее 55 кг или свыше 100 кг.

Рис. 7.23. Пример функции ЕСЛИ() и И()

12. В ячейку I19 введите следующую формулу:

=ЕСЛИ(И(G22>=150;G22<=200;H22>=55;H22<=100);"Годен";"Не годен")

13.  В ячейке F29 введите число 3 (рис. 7.24).

Рис. 7.24. Примеры функций И(), ИЛИ() и НЕ()

14. Присвойте этой ячейке имя z.

15. Необходимо выяснить, принадлежит ли z отрезку [2,5].

16. В ячейку G29 введите формулу:

=И(z>=2;z<=5)

17. В результате в ячейку G29 возвратится значение ИСТИНА.

Функция ИЛИ()

Функция ИЛИ() называется функцией логического сложения и имеет следующий синтаксис:

ИЛИ(условие1;условие2;…)

Функция возвращает значение ИСТИНА, если хотя бы одно из условий истинно (см. табл. 8.2). Функции И() и ИЛИ() часто применяются совместно с функцией ЕСЛИ() в качестве вложенных функций.

Задание 12. Необходимо выяснить, принадлежит ли z одному из лучей на числовой оси (–¥,2) или (5,+¥).

1. В ячейку H29 введите следующую функцию:

=ИЛИ(z<2;z>5)

2. Формула возвращает в ячейку H29 слово ЛОЖЬ (см. рис. 8.5).

3. Пример можно решить иначе с учетом того обстоятельства, что на рабочем столе есть формула проверки принадлежности числа z отрезку [2,5]. Упомянутые два луча составляют на числовой оси дополнение к этому отрезку. Введите в ячейку I29 формулу:

 =НЕ(G29)

4. Убедитесь, вводя в ячейку F29 различные числа, что формулы в ячейках H29и I29 дают одинаковые результаты.

5. Введите в ячейки G30, H30 и I30 числовые значения, затем присвойте этим ячейкам имена u, v, w. Необходимо выяснить, есть ли среди этих трех чисел отрицательные.

6. В ячейку F30 введите формулу:

=ЕСЛИ(ИЛИ(u<0;w<0;v<0);"Есть отрицательные числа";"Отрицательных чисел нет")

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

Задание 13. Самостоятельно решите следующие примеры.

Пример 1. Введите в ячейку F 31 формулу, возвращающую значение ИСТИНА, если z принадлежит одному из следующих отрезков: (–2,4] или [7,12) или [20,+¥), и ЛОЖЬ – в противном случае.

Примечание: [ – квадратная скобка обозначает равенство указанному значению.

Пример 2. Введите в ячейки F 31, G 31, H 31 и I 31 логические формулы, которые возвращают значение ИСТИНА, если:

1. Каждое из чисел u, v, w является положительным;

2. Хотя бы одно из чисел u, v, w является положительным;

3. Только одно из чисел u, v, w является положительным;

4. Ни одно из чисел u, v, w не является положительным;

Пример 3. Составить формулу, которая бы выдавала приветствия на вводимое время следующим образом (рис. 7.25):

- от 0 до 7 часов – «Доброй ночи»;

- от 7 до 11 часов – «Доброе утро»;

- от 11 до 18 часов – «Добрый день»;

- от 18 до 24 часов – «Добрый вечер».

Предусмотреть защиту от неправильного ввода: время суток не может выражаться отрицательными числами и числами больше 24.

Приветствие
Который час?	9
Тогда	Доброе утро

Рис. 7.25. Вид таблицы для примера 3

Пример 4. Построить таблицу для определения годности или негодности двух заготовок по результатам контрольных замеров их параметров (рис. 7.26). На вес заготовки установлен допуск от 20 до 30 кг, на размер заготовки установлен допуск от 150 до 250 см.

Заготовки	Вес	Размер	Решение
Заготовка 1	25	260	Брак
Заготовка 2	28	175	Заготовка годна

Рис. 7.26. Вид таблицы для примера 4

В качестве вложенных в функцию ЕСЛИ() логических функций для заготовки 1 используйте функцию И(), для заготовки 2 – функцию ИЛИ().