Тема 9. Логические функции

Примеры.

1. Найти минимальное и максимальное значения из чисел, находящихся в блоке ячеек А 2: D 2 как показано на рисунках 262 – режим показа формул, и 263 – режим вычислений.

Рисунок 262

Рисунок 263

2. Вычислить средние арифметические значения содержимого блока ячеек А 1: А 5 и блока ячеек В 1: В 5 как показано на рисунках 264 – режим формул, и 265 – режим решения.

Рисунок 264

Рисунок 265

СРОТКЛ (число 1; число 2; …) – возвращает среднее абсолютных значений отклонений чисел от их среднего значения.

Среднее отклонение вычисляется по формуле:

где – среднее арифметическое, n – количество чисел.

Например,

СРОТКЛ(3;2;1;4;2;3;3) равняется 0,77551, т.е.

(3 + 2 + 1 + 4 + 2 + 3 + 3) / 7 = 18 / 7 = 2,571429;

(| (3 – 2,57) | + | (2 – 2,57) | + | (1 – 2,57) | + | (4 – 2,57) | + | (2 – 2,57) | + | (3 – 2,57) | + | (3 – 2,57) |) = 0,77551.

3. Вычислить среднее отклонение чисел 3, 2, 1, 4, 2, 3, 3 как показано на рисунках 266 – режим формул, и 267 – режим решения.

Рисунок 266

Рисунок 267

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

Функции проверки условия.

Функция ЕСЛИ (логическое выражение; значение да; значение нет). Эта функция выполняет проверку условия, задаваемого первым аргументом. Если условие выполняется (ИСТИНА), то значением функции будет значение да, если не выполняется - то значением функции будет значение нет.

Рассмотрим примеры:

=ЕСЛИ(А6<0;15;25)

Если значение содержимого ячейки А 6 будет меньше 0, то результат вычисления равен 15, в противном случае (т.е. если значение содержимого ячейки А 6>=0) результат равен 25.

В аргументах функции можно использовать другие функции, например,

=ЕСЛИ(СУММ(D1:D6)>0; СУММ(D1:D6);0)

Результатом выполнения этой функции будет сумма значений содержимого ячеек D 1: D 6, если эта сумма положительная, и нулю в противном случае, то есть если эта сумма отрицательная или равна нулю как показано на рисунках 268, 269.

Рисунок 268

Рисунок 269

Аргументами функции, а также результатом выполнения функции могут быть текстовые константы.

Например,

=ЕСЛИ(В5>100;“Принять”;“Отказать”)

Если содержимое ячейки больше 100, то результатом выполнения функции будет значение “ Принять ”, в противном случае – “ Отказать ” как показано на рисунках 270, 271.

Рисунок 270

Рисунок 271

Текстовые значения могут быть и в логическом выражении:

=ЕСЛИ(В10=”отлично”;“100%”;“70%”)

Функции И, ИЛИ, НЕ.

= И (логическое выражение 1; логическое выражение 2; …)

= ИЛИ (логическое выражение 1; логическое выражение 2; …)

= НЕ (логическое выражение)

Эти функции дают возможность строить сложные логические выражения. Функции И () и ИЛИ () могут иметь в качестве аргументов до 30 логических выражений, функция НЕ () – только один аргумент. Аргументами этих функций могут быть логические значения, массивы, адреса ячеек.

Результаты, получаемые с помощью функций И () и ИЛИ () существенно отличаются.

Рассмотрим примеры:

=ЕСЛИ(И(А3>0;D3>0);“Решение есть”;“Решения нет”))

=ЕСЛИ(ИЛИ(А3<0; D3<0);“Решения нет”;“Решение есть”))

В первом случае: если и содержимое ячейки А3>0, и содержимое ячейки D3>0, результатом будет “ Решение есть ”, если содержимое хотя бы одной из ячеек (А3 или В3) <=0, результатом будет - “ Решения нет ” как показано на рисунках 272, 273, 274.

Рисунок 272

Рисунок 273

Рисунок 274

Примечание. Формулу можно сделать короче, если вместо текстовых констант ввести ссылки на них рисунок 275.

Рисунок 275

Во втором случае: если или содержимое ячейки А3<0, или содержимое ячейки D3<0, результатом будет “ Решения нет ”, в противном случае “ Решение есть ” как показано на рисунках 276, 277, 278.

Рисунок 276

Рисунок 277

Рисунок 278

Часто вместо функций И, ИЛИ нагляднее использовать операторы умножения и сложения. Например, рассматриваемые формулы можно записать:

=ЕСЛИ(А3>0*D3>0;“Решение есть”;“Решения нет”)) рисунок 279

Рисунок 279

=ЕСЛИ((А3<0)+(D3<0);“Решения нет”;“Решение есть”)) рисунок 280.

Рисунок 280

Функция НЕ () выдает логическое значение противоположное логическому значению своего аргумента:

=НЕ(А1=А2) эквивалентна =А1<>A2

=НЕ(А1>А2) эквивалентна =А1<=A2

Например

=НЕ(2*2=4) вернет ЛОЖЬ, так как условие 2*2=4 истинно рисунок 281.

Рисунок 281

=НЕ(2*2=5) вернет ИСТИНА, так как условие 2*2=5 ложно рисунок 282.

Рисунок 282

Вложение функций ЕСЛИ ().

Функции ЕСЛИ () могут вкладываться друг в друга. Можно вложить друг в друга в качестве второго и третьего аргументов до 7 функций ЕСЛИ ().

=ЕСЛИ(В10=25;“Отлично”;ЕСЛИ(И(В10<25;В10>22);“Хорошо”;ЕСЛИ(И(В10<=22;B10>19);“Удовлетворительно”;“Неудовлетворительно”))).

Выполняется функция следующим образом: если число, находящееся в ячейке В 10, равно 25, то значением функции будет “ Отлично ”; иначе – если число, находящееся в ячейке В 10 меньше 25, но больше 22, то функция примет значение “ Хорошо ”, иначе – если В 10 меньше или равно 22 и больше 19, функция примет значение “ Удовлетворительно ”, иначе “ Неудовлетворительно ” как показано на рисунках 283, 284, 285.

Рисунок 283

Рисунок 284

Рисунок 285

Следует иметь в виду, что вложенных функций ЕСЛИ () должно быть на единицу меньше, чем возможных вариантов принимаемых значений. Иногда аргументы логических функций становятся громоздкими, если приходится проверять выполнение одного и того же условия элементами блоков ячеек, например:

=ЕСЛИ(И(А4>=B2;B4>=B2;C4>=B2;D4>=B2;E4>=B2;F4>=B2); “Да”; “Нет”) рисунок 286.

Рисунок 286

Условие можно записать короче, если в аргументе функции использовать блок ячеек:

=ЕСЛИ(И(А4:F4>=B2);“Да”;“Нет”) рисунок 287.

Рисунок 287

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

В Excel имеются функции даты и времени. Рассмотрим некоторые из них.

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

Например,

=СЕГОДНЯ()

результатом выполнения этой функции 24 октября 2000 года будет

24.10.00

Функция ТДАТА () – дает возможность получить текущую (системную) дату и время. Аргументы у этой функции отсутствуют. Результатом выполнения данной функции является значение текущей даты и времени.

Например,

= ТДАТА()

результатом выполнения этой функции 24 октября 2000 года в 12 часов 43 минуты будет

24.10.00 12:43

ДАТА (год; месяц; день) – возвращает дату из отдельных ее компонент, полученных, возможно, в результате вычислений.

Например,

=ДАТА(2003;03;01)

возвращает 01.03.2003.

ДАТАЗНАЧ (текст) – преобразует текстовую форму даты в числовую.

Например,

=ДАТАЗНАЧ(“01.03.2003”)

возвращает 01.03.2003.

ДЕНЬНЕД (дата; тип) - используется для вычисления порядкового номера дня (от 1 до 7) недели, соответствующей заданной дате.

Аргумент дата может быть датой в числовом формате, ссылкой на ячейку, содержащую дату в числовом или текстовом формате, функцию даты. Необязательный аргумент тип дает возможность выбрать желаемый порядок нумерации дней недели. Если этот аргумент равен 1 или отсутствует, то первым днем недели является воскресенье, а последним – суббота. Если тип равен 2, то первым днем считается понедельник, а последним - воскресенье. Если тип равен 3, то нумерация дней недели начинается с нуля, причем 0 соответствует понедельнику, а 6 – воскресенью.

Например, пусть в ячейке С 5 записана дата 27.01.00, тогда при выполнении оператора

=ДЕНЬНЕД(С5;1)

появится значение 5, что соответствует четвергу.

ВРЕМЯ (часы; минуты; секунды) – возвращает дату в числовом формате для заданного времени: десятичную дробь в интервале от 0 до 0,99999999, представляющую время от 0:00:00 (12:00:00 ночи до23:59:59 (11:59:59 вечера).

Время (часы; минуты; секунды). Аргумент секунды необязателен, но точку с запятой после аргумента минуты опускать нельзя. При обращении к функции ВРЕМЯ надо придерживаться 24-часового формата, т.е. время 3 часа 15 минут дня (2:15 РМ) следует задавать в виде

=ВРЕМЯ(14;15;)

Значения аргументов могут вычисляться непосредственно функции. Например, если потребуется найти, время, которое было 35 секундами раньше времени17:0:14, то можно воспользоваться функцией

=ВРЕМЯ(17; 0; 14-35)

и функция вернет результат 16:59:39.

Дату и время можно использовать в формулах и функциях так же, как обычные числа. Предположим, нужно найти дату через 48 дней после 5 июля 2003 года. Пусть начальная дата находится в ячейке А 1, тогда по формуле

=А1+48

будет найдена новая дата, которая составит 37855 (в ОБЩЕМ формате). Если выбрать формат ДАТА, можно получить, например, 22.08.03 рисунок 288.

Рисунок 288

Пусть требуется определить количество дней между датами 05.07.03 и 22.08.03. Запишем в ячейку В 6 формулу

=В3-А2,

получим 48 (в общем формате) рисунок 289.

Рисунок 289

Дата добавления: 2014-10-17