Пример 2

В ячейке А6 (с именем z) записано число. Выяснить, принадлежит ли оно отрезку [2, 5].

Решение.

Присвоим ячейке А6 имя z. Введем в А6 число 3.Сначала сконструируем логическое выражение, решающее задачу. z Î [2, 5] Û (z ³ 2) ^ (z £ 5). Для того чтобы z принадлежал отрезку [2, 5], нужно, чтобы одновременно были истинны два предиката: z ³ 2 и z £ 5. В ячейке В6 разместим формулу = И (z > = 2, 2 < = 5). В 86 получим значение ИСТИНА. Следует предостеречь от неверного решения: формулы =2< =z< =5. Введите эту формулу в С6 и убедитесь, что она возвращает ЛОЖЬ! Коварство этой, на первый взгляд, такой естественной формулы в том, что Excel ничего не сообщает о ее некорректности.

Пример 3

В ячейке А6 (с именем z) записано число. Выяснить, принадлежит ли оно одному из лучей на числовой оси: (-¥, 2) или(5, ¥).

Решение

Сконструируем логическое выражение, решающее задачу: z Î (-¥, 2) È (5, ¥) Û (z < 2) Ú (z > 5), где значок È обозначает операцию объединения множеств. Для того чтобы z принадлежал хотя бы одному из лучей, нужно, чтобы был истинным хотя бы один из предикатов: z < 2 или z> 5. В ячейке D6 разместим формулу = ИЛИ (z < 2, z > 5). A6 содержит число 3, поэтому формула возвращает ЛОЖЬ.

Задачу можно было решить иначе с учетом того обстоятельства, что на рабочем листе есть формула проверки принадлежности числа z отрезку [2, 5]. Упомянутые два луча составляют на числовой оси дополнение к этому отрезку. Введем в ячейку Еб формулу = НЕ (В6). Убедитесь, вводя в ячейку А6 различные числа, что формулы в ячейках D6 и Е6 дают идентичные результаты.

Функции даты и времени

Если нужно оперировать датами и временем, то и здесь Excel придёт вам на помощь. В ней предусмотрено около двадцати функций, работающих с этим типом значений.

Таблица 6

Функция СЕГОДНЯ

Эта функция не имеет аргумента. Она возвращает текущую дату, точнее, дату, установленную в системе. Например, если вы введете в ячейку следующую формулу 16 июня 2003 г., то формула вернет 16.06.2003:

= СЕГОДНЯ ()

Функция ДАТА

Эта функция выводит на экран дату в соответствии с тремя аргументами: год, месяц и день. Функция ДАТА поможет скомпоновать дату на основе информации, содержащейся в рабочем листе. Например, если ячейка A1 содержит 2003, ячейка Bl — 12, а ячейка Cl— 25, то следующая формула возвращает дату 25.12.2003:

= ДАТА (А1; В1; С1)

Функция ДЕНЬ

Эта функция определяет день месяца в указанной дате. Например, если ячейка A1 содержит дату 25.12.2003, то следующая формула вернет число 25:

= ДЕНЬ (А1)

Рисунок 6.6

Кроме того, в Excel предусмотрены функции ГОД и МЕСЯЦ, которые выделяют из даты соответственно год и месяц.

Функция ВРЕМЯ

Эта функция возвращает время в зависимости от трех аргументов: часы, минуты и секунды. Функция ВРЕМЯ может скомпоновать время на основании информации, содержащейся в рабочем листе. Например, если ячейка A1 содержит 8, ячейка Bl — 15, а ячейка Cl — 0, то следующая формула возвращает 8: 15: 00:

= ВРЕМЯ (А1; В1; С1)

Функция ЧАС

Эта функция выделяет из времени число часов. Например, если ячейка A1 содержит время 8: 15: 00, то следующая формула возвращает 8:

= ЧАС (А1)

Статистические функции

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

Функция СРЗНАЧ

Эта функция возвращает среднее значение (среднее арифметическое) диапазона ячеек. Это эквивалентно сумме содержимого всех ячеек диапазона, деленной на количество ячеек. Следующая формула возвращает среднее значение диапазона A1: А100:

= СРЗНАЧ (А1: A100)

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

Функция СЧЁТЕСЛИ

Эта функция пригодится в том случае, если нужно подсчитать, сколько раз определенное значение встречается в заданном диапазоне. Функция СЧЕТЕСЛИ имеет два аргумента: диапазон, содержащий значения, которые нужно подсчитать, и критерий для определения того, что подсчитывать.

Нахождение наибольших и наименьших значений

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

=" Наименьшее: " & МИН(Данные)& " Наибольшее: " & МАКС (Данные)

Например, если в ячейках диапазона Данные находятся числа от 12 до 156, то эта формула возвращает строку " Наименьшее: 12 Наибольшее: 156".

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

= НАИБОЛЬШИЙ (Данные; 2)

Функция НАИМЕНЬШИЙ работает точно таким же образом, но возвращает n-ое наименьшее значение диапазона.

Практические задания:

Внимание: все операции необходимо производить не вручную, а использую логические формулы и необходимые функции!

1. В ячейке A4 записать число х, в ячейку B число y (см. табл. вариантов). Используя логические функции проверить, принадлежит ли оно отрезку [4, 9]. Если принадлежит, то в ячейке В5 должен выводится текст: «Принадлежит». Если не принадлежит, то в ячейке В5 должен быть текст: «Не принадлежит».

2. Вычислите значение (у-х) в ячейке А7. Если оно отрицательное, в ячейке В7 вычислите x², если положительное – y².

3. Если значения х и у оба положительны, в ячейке С5 вычислите их сумму, если оба отрицательны, в ячейке С5 вычислите модуль их разности, если одно из них отрицательно, в ячейке С5 вычислите сумму их модулей.

4. Если сумма х и у больше 45 или их разность меньше 12, в ячейке D3 вычислите их произведение, в противном случае, в ячейке D3 вычислите сумму их квадратов.

5. Торговый агент получает процент от суммы совершённой сделки. Если объем сделки до 3000, то 5%; если объём до 10000, то 2%; если выше 10000, то 1, 5%. Введите в ячейку А10 текст «Объем сделки», в ячейку А11 – «Размер вознаграждения». В ячейку В10 введите объём сделки, а в В11 – формулу, вычисляющую размер вознаграждения.

6. Покажите на 3-х примерах как работают функции даты и времени (можно использовать любые функции данной категории).

7. Покажите на 3-х примерах как работают текстовые функции (можно использовать любые функции данной категории).

8. Заполните любыми числами блок К4: L12. Посчитайте среднее значение этого диапазона ячеек, используя соответствующую статистическую функцию.

Таблица 6