 |
Задание 2. Логические функции.
|
|
|
|
Добавьте лист Логические функции.
Пример1.
Вычислить значение функции у=f(x) по формуле:
у= 
Введите любое число в ячейку А1, а в В1 - следующую формулу: =ЕСЛИ(А1<0;А1;А1^2).
У функции ЕСЛИ - 3 аргумента. Первый - это логическое выражение, которое может принимать значение ИСТИНА или ЛОЖЬ. Второй и третий - выражения, которые вычисляются в активной ячейке в том случае, если 1-й аргумент принимает соответственно истинное или ложное значение.
Проверьте работу функции, задавая в А1 несколько разных значений.
Пример2.
Вычислить значение функции у=f(x) по формуле:
у= 
Введите в А1 любое число, в В1 формулу: =ЕСЛИ(И(А1>= -5;А1<= 5);А1*А1-1;А1).
Количество аргументов функции И - не фиксировано. Функция возвращает значение ИСТИНА, если все аргументы принимают значение ИСТИНА, и ЛОЖЬ, если хотя бы одно условие ложно.
Пример3.
Вычислить значение функции у=f(x) по формуле:
у= 
Задачу решает такая формула:
=ЕСЛИ(И(А1>= -5;A1<=5);А1^3;ЕСЛИ(ИЛИ(И(А1>= -10;A1< -5);И(А1>5;A1<= 10));A1;A1*A1)).
Функция ИЛИ подобна функции И. Разница в том, что ИЛИ возвращает значение ИСТИНА, если хотя бы один из её аргументов истинный. Если все аргументы ложны, - значение ЛОЖЬ.
Задание: Рассчитать значения 
на отрезке [a;b] c шагом h. Результат оформить в виде таблицы.
Варианты заданий выбираются из таблицы. Номер варианта выбирается по последней цифре номера зачётной книжки. Например, зачётная книжка № 931416 ® Вариант № 6
| 
| h | 
| |
| – 4 | 0,4 | 
| ||
| – 0.9 | 0.9 | 0,15 | 
| |
| – 1 | 0,2 | 
| ||
| 4,2 | 5,85 | 0,3 | 
| |
| 
| 
| 
| |
| 0,4 | 
| |||
| 
| 
| 
| |
| – 3 | 
| |||
| – 2 | 0,5 | 
| ||
| 
| 
|
Задание 3 (одинаковое для всех вариантов). Статистические функции.
Добавьте лист Статистические функции1. Введите данные столбцов А, В, С.
|
|
|
| А | В | С | D | |
| Студент | Оценка | Оценка | Количество | |
| Викторов | ||||
| Валерьев | ||||
| Валерьева | ||||
| Ильичёв | ||||
| Антонов | ||||
| Евгеньев | ||||
| Михайлов | ||||
| Дмитриев | ||||
| Алексанров | ||||
| Надеждина | ||||
| Татьянова | ||||
| Петрова | ||||
| Иванов | ||||
| Павлова | ||||
| Степанов | ||||
| Егоров | ||||
Для того чтобы подсчитать, какое количество студентов получили ту или иную оценку, воспользуемся функцией СЧЁТЕСЛИ. Сначала выделите диапазон с оценками, нажмите Вставка ® Имя ® Присвоить и назовите этот диапазон: Оценки. Теперь в ячейке D2 вызовите функцию =СЧЁТЕСЛИ(Оценки;C2). После этого сделайте автозаполнение.
Задание 4 (одинаковое для всех вариантов). Использование комбинированных формул.
Добавьте лист Комбинированные формулы1.
Введите следующие данные (кроме столбца G и диапазона А13:А17).
| A | B | C | D | E | F | G | |
| Фирма | Сумма оплаты | Оплачено | Назначенная дата оплаты | Реальная дата оплаты | Долг | Задержка оплаты | |
| Рекорд | 105 100р. | 100 000р. | 15.02.02 | 13.02.02 | 5 100р. | -2 | |
| Рубин | 207 000р. | 220 000р. | 16.02.02 | 16.02.02 | -13 000р. | ||
| Горизонт | 153 000р. | 139 000р. | 20.02.02 | 25.02.02 | 14 000р. | ||
| Сокол | 192 000р. | 185 000р. | 23.02.02 | 23.02.02 | 7 000р. | ||
| Витязь | 220 000р. | 220 000р. | 23.02.02 | 20.02.02 | 0р. | -3 | |
| Sony | 735 000р. | 750 000р. | 25.02.02 | 25.02.02 | -15 000р. | ||
| Panasonic | 652 000р. | 628 000р. | 26.02.02 | 24.02.02 | 24 000р. | -2 | |
| Samsung | 546 000р. | 590 000р. | 26.02.02 | 22.02.02 | -44 000р. | -4 | |
| 3N | 465 000р. | 400 000р. | 27.02.02 | 27.02.02 | 65 000р. | ||
| Philips | 357 000р. | 340 000р. | 27.02.02 | 28.02.02 | 17 000р. | ||
| Всего | 3 632 100р. | 3 572 000р. | 60 100р. | ||||
| -4900 |
Дату вводите так: 15-2 для 15.фев…Затем выберите формат ячейки Дата и установите образец 14.03.99 Присвойте соответствующим диапазонам названия: Фирма, С_оплаты, Оплачено, Наз_Дата, Реал_Дата, Долг, З_оплаты.
|
|
|
В ячейку G2 введите знак «=», мышью обозначьте диапазон Реал_дата, поставьте «-», обозначьте Наз_дата, нажмите Ctrl+Shift+Enter. Выделите диапазон З_оплаты, нажмите F2, Ctrl+Shift+Enter.
Предположим, вы поставляете указанным фирмам детали на суммы, фигурирующие в столбце Сумма оплаты.
Работа с одним критерием.
Суммирование значений одного знака.
Как видите, имеются должники, а также фирмы, заплатившие вперёд. Простым автосуммированием мы узнаем общую сумму долга, но этой информации может быть недостаточно. Чтобы найти сумму для фактических должников, в ячейку А13 введите: =СУММЕСЛИ(Долг;”>0”).
Суммируемые и проверяемые значения принадлежат разным диапазонам.
Предположим, вы хотите узнать, на какую сумму были проведены поставки только по отношению к должникам. Тогда в ячейку А14 введите: =СУММЕСЛИ(Долг;">0";С_оплаты).
Суммирование на основе сравнения дат.
Если вы хотите узнать, какая общая сумма была выплачена на 25 февраля, - воспользуйтесь формулой =СУММЕСЛИ(Реал_дата;"<25.фев";Оплачено) в ячейке А15.
Допустим, мы хотим найти сумму, которой мы рисковали, исходя из того, что имеются фирмы, проведшие оплату с опозданием. В столбце G положительное число говорит, что оплата - с задержкой, отрицательное - что досрочная. Поэтому в ячейку А16 нужно ввести: =СУММЕСЛИ(З_оплаты;">0";С_оплаты).
Суммирование на основе сравнения текста.
Из таблицы видно, что наш основной должник - фирма 3N. Проверим, как выглядит долг без учёта данных по этой фирме. Для этого введём в ячейку А17: =СУММЕСЛИ(Фирма;"<>3N";Долг).
Работа на основе нескольких критериев.
Суммирование при выполнении всех критериев.
Предположим, вы хотите подсчитать сумму, на которую были поставлены детали фирмам-должникам, которые кроме того и оплату провели с задержкой. Тогда воспользуйтесь формулой массива: {=СУММ((Долг>0)*(З_оплаты>0)*(С_оплаты))}. (Не СУММЕСЛИ)
|
|
|
Эта формула использует 3 массива из 10 элементов: массив С_оплаты и 2 виртуальных массива, которые существуют только в оперативной памяти компьютера - логический массив, где значения ИСТИНА будут соответствовать положительным значениям диапазона Долг, и ЛОЖЬ в противоположном случае; логический массив, где значения ИСТИНА будут соответствовать положительным значениям диапазона З_оплаты, и ЛОЖЬ в противоположном случае.
Логические значения умножаются по правилам:
ИСТИНА*ИСТИНА=1*ИСТИНА=1
ИСТИНА*ЛОЖЬ=ЛОЖЬ*ЛОЖЬ=ЛОЖЬ*1=ЛОЖЬ*0=0
Поэтому значения из диапазона С_оплаты умножаются на 1 только тогда, когда оба соответствующих значения в виртуальных массивах равны ИСТИНА. Иначе они умножаются на 0.
Выясните, какая сумма была получена в период от 20 до 25 февраля от фирм, проведших эту оплату вовремя.
Решение таково:
{=СУММ((ДАТА(ГОД(Реал_дата);МЕСЯЦ(Реал_дата);ДЕНЬ(Реал_дата))>ДАТА(2002;2;19))*(ДАТА(ГОД(Реал_дата);МЕСЯЦ(Реал_дата);ДЕНЬ(Реал_дата))<ДАТА(2002;2;26))*(З_оплаты<=0)*С_оплаты)}.
Обратите внимание, что все условия берутся в скобки.
В этой формуле использованы функции категории Дата и время. Сравнивать даты можно только тогда, когда они представлены в числовом формате. Эту функцию и выполняет ДАТА().
В любой ячейке введите =ДАТА(2002;2;19). Чтобы теперь отобразить результат в числовом формате, - нажмите Формат ® Ячейки ® Числовой ® Без дробных знаков. Получим 37 306. Как видите, даты нумеруются по порядку и на этом основании сравниваются. Очевидно, для работы с датами в числовом формате нет необходимости отображать их в этом формате.
Ниже мы рассмотрим ещё несколько функций этой категории.
Суммирование при выполнении хотя бы одного критерия.
Предположим, вы хотите подсчитать сумму, на которую были поставлены детали фирмам, которые провели оплату либо в срок, либо без долга или кредита. Тогда введите формулу:
{=СУММ(Если((Долг=0)+(З_оплаты=0);1;0)*(С_оплаты))}.
Здесь знак «+» выполняет роль функции ИЛИ. Комбинирование «+» и «*» без функции ЕСЛИ приведёт к ошибке, т.к. ИСТИНА+ИСТИНА=2, а не 1.
|
|
|
Соединение критериев.
Допустим, нужно выяснить, какая сумма была получена в период от 20 до 25 февраля от тех из фирм Рубин, Рекорд, Sony, которые провели оплату вовремя. Тогда нужно воспользоваться формулой, соединяющей действие функций И и ИЛИ:
{=СУММ((ДАТА(ГОД(Реал_дата);МЕСЯЦ(Реал_дата);ДЕНЬ(Реал_дата))>ДАТА(2002;2;19))*(ДАТА(ГОД(Реал_дата);МЕСЯЦ(Реал_дата);ДЕНЬ(Реал_дата))<ДАТА(2002;2;26))*(З_оплаты<=0)*ЕСЛИ ((Фирма=”Рекорд”)+(Фирма=”Рубин”)+(Фирма=”Sony”);1;0) *(С_оплаты)}.
Дополнительное задание.
Аналогично, учитывая различные критерии, можно работать с функцией СЧЁТЕСЛИ. Эта функция подсчитывает количество ячеек в диапазоне, удовлетворяющих определённому критерию. Обратите внимание на отличие этой функции от функции СЧЁТ (помимо условия).
Ответьте на следующие вопросы.
1. Каково количество должников? =СЧЁТЕСЛИ(Долг;”>0”)
2. Какие фирмы провели оплату точно в срок? =СЧЁТЕСЛИ(З_оплаты;0)
3. С какими из фирм заключены более крупные договора, чем с фирмой Philips? =СЧЁТЕСЛИ(С_оплаты;”>”&B11).
Для подсчёта с учётом нескольких критериев вспомним, что логические значения умножаются и складываются по правилам:
ИСТИНА*ИСТИНА=1*ИСТИНА=1
ИСТИНА*ЛОЖЬ=ЛОЖЬ*ЛОЖЬ=ЛОЖЬ*1=ЛОЖЬ*0=0
ИСТИНА+ИСТИНА=2
4. Есть ли такие фирмы, договор с которыми не превышает 250 000 р. и которые провели оплату лишь в марте? {=СУММ((С_оплаты<250000)*(МЕСЯЦ(Реал_дата)=3))}
5. Есть ли такие фирмы, договор с которыми не превышает 250 000 р. и которые либо имеют долг, либо провели оплату с задержкой?
{=СУММ((С_оплаты<250000)*ЕСЛИ((Долг>0)+(З_оплаты>0);1;0))}
|
|
|
