Общие сведения о логических выражениях
Создайте в рабочей книге Excel новый лист «Логика».
Введите в ячейку А1 формулу =7>5. Она вернет значение Истина. Теперь введите в ячейку А2 формулу =3>5. Эта формула вернет значение Ложь. Правые части обеих формул представляют собой высказывания, т.е. утверждения, относительно которых можно заключить, верны они или нет. Арифметические формулы, которые мы изучали на предыдущих занятиях, например формула =$A2*B$1, высказываниями не являются. Арифметические формулы предписывают, как по исходным данным вычислить значение, и вопрос об их истинности или ложности не имеет смысла.
Рассмотрим другой пример. Введите в ячейку А4 число 2, а в ячейку И4 формулу =А4>3. Формула возвращает значение ЛОЖЬ. Теперь исправьте содержимое ячейки А4 – введите число 6. Формула возвращает значение ИСТИНА. В ячейке В4 записан предикат, т.е. высказывание с переменными (в данном случае переменная одна). В зависимости от значения переменных предикат может принимать значения Истина или ЛОЖЬ. В этом примере формула как бы дает ответ на вопрос: «Число (или результат вычислений по формуле), хранящийся в ячейке А4, превышает число 3?» В зависимости от значения А4 ответ будет либо ДА (ИСТИНА), либо НЕТ (ЛОЖЬ).
Сравнение двух арифметических выражений, содержащих переменные, дает предикат. В формуле =А4>3 ее составные части (А4 и 3) можно считать арифметическими выражениями, только очень простыми. Более сложный пример: =3*(A4^2-1)>(2*A4+1)/5.
Сведем операции сравнения в таблицу.
| > | >= | < | <= | = | <> |
| больше | больше или равно | меньше | меньше или равно | равно | не равно |
Обратите внимание, что символ отношения «больше или равно» изображается двумя знаками. Причина в том, что на клавиатуре отсутствует знак ≥.
Высказывание и предикат имеют общее название – логическое выражение. Имеются логические операции, которые позволяют строить сложные логические выражения. Эти операции реализованы в Excel как функции, перечень которых приведен в таблице 2 в порядке убывания приоритета.
| Название | Обозначение | Функция Excel |
| Отрицание | ⌐ | НЕ |
| Конъюнкция | & | И |
| Дизъюнкция | V | ИЛИ |
На самом деле в Excel приоритет логических операций не имеет значения, так как они реализованы виде функций.
У логических функций аргументы могут принимать только два значения: ИСТИНА или ЛОЖЬ. Поэтому логические функции можно задавать таблицей, где перечислены все возможные значения аргументов и соответствующие им значения функций. Такие таблицы называются таблицами истинности.
Таблица для функции НЕ.
| х | НЕ(х) |
| ЛОЖЬ | ИСТИНА |
| ИСТИНА | ЛОЖЬ |
Таблица для функций И и ИЛИ
| х | у | И(х,у) | ИЛИ(х,у) |
| ЛОЖЬ | ЛОЖЬ | ЛОЖЬ | ЛОЖЬ |
| ЛОЖЬ | ИСТИНА | ЛОЖЬ | ИСТИНА |
| ИСТИНА | ЛОЖЬ | ЛОЖЬ | ИСТИНА |
| ИСТИНА | ИСТИНА | ИСТИНА | ИСТИНА |
Функция НЕ может иметь только один аргумент, а функции И и ИЛИ два и более аргументов.
На практике в числом виде логические выражения как правило не используются. Логическое выражение служит первым аргументом функции ЕСЛИ:
ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь).
В языках программирования высокого уровня этой функции соответствует оператор
если лог_выражение то действие1 иначе действие2
Лог_выражение – это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100 — это логическое выражение; если значение в ячейке A10 равно 100, то выражение принимает значение ИСТИНА. В противном случае — ЛОЖЬ.
Например, сведения о предполагаемых и фактических расходах сведены в таблице в интервале A1:C4.
| Предполагаемые расходы | Фактические расходы | Бюджет |
Проверьте сведения на превышение бюджета. Для этого введите в ячейку С2 следующую формулу, которую затем скопируйте в ячейки С3 и С4
=ЕСЛИ(A2>B2; «Превышение бюджета»; «Бюджет не превышен»)
4.2 Задания для самостоятельной работы
В рабочей книге каждый лист отвести для решения одной задачи. Каждому листу дать название задание 1, задание 2, …, задание 13.
Отчет по выполненной работе должен содержать распечатку каждой отформатированной расчетной таблицы и таблицы с включенными формулами.
Задание 1
В таблице представлены результаты 5 игр по футболу между двумя студенческими командами. В столбце В приведены число голов, забитых командой Авангард, в столбце С – командой Сатурн. Необходимо в столбце D получить название команды-победительницы каждой игры либо слово ничья.
| Игра | Авангард | Сатурн | Кто выиграл? |
| 15.10.2000 | |||
| 10.10.2001 | |||
| 12.09.2002 | |||
| 9.10.2003 | |||
| 14.09.2004 |
Задание 2
На основе записей в главном журнале бухгалтерии, приведенном в таблице Главный журнал, автоматически выведите баланс (разницу между дебетом и кредитом) по любому счету с начала текущего месяца. Номер счета вводится в отдельную ячейку.
Главный журнал
| Дата | Счет | Дебет | Кредит |
| 1.мар | |||
| 2.мар | |||
| 3.мар | |||
| 4.мар | |||
| 5.мар | |||
| 7.мар | |||
| 8.мар | |||
| 10.мар | |||
| 11.мар | |||
| 12.мар |
Балансовый отчет
| Счет | |
| Дебет | |
| Кредит | |
| Баланс |
Задание 3
Торговый агент получает процент от суммы совершенной сделки по следующим условиям:
- если объем сделки до 3000, то 5 %;
- если объем до 10000, то 2 %;
- если выше 10000, то 1,5 %.
| Торговый агент | Сумма сделки | Вознаграждение |
| Иванов И.И. | ||
| Петров П.П. | ||
| Сидоров О.Б. | ||
| Жукин Л.М. |
Задание 4
К юбилею предприятия планируется начислить премию сотрудникам исходя из их стажа работы:
- если стаж работы от 10 до 20 лет, то 2 оклада;
- свыше 20 лет, то 3 оклада.
| Сотрудник | Дата устройства на работу | Оклад | Премия |
| Иванов И.И. | 12.08.1983 | ||
| Петров П.П. | 12.05.2001 | ||
| Сидоров О.Б. | 23.05.1992 | ||
| Жукин Л.М. | 16.12.1977 |
Стаж рассчитать, используя функции работы с датами.
Задание 5
По результатам сданной сессии начислите студентам стипендию, если:
- все отличные оценки – максимальная стипендия;
- сессия сдана на 4 и 5 – средняя стипендия;
- все оценки хорошо – минимальная стипендия.
| Студент | Высш. матем. | Физика | История | Стипендия |
| Иванов И.И. | ||||
| Петров П.П. | ||||
| Сидоров О.Б. | ||||
| Жукин Л.М. |
Задание 6
Используя функции И и ИЛИ создайте форму, в которой автоматически будет выводиться информация о студентах, которые не сдали зачет по праву или одному из иностранных языков и поэтому не могут быть допущены к экзамену по Информатике.
| Студент | Право | Английский | Немецкий | Информатика |
| Иванов И.И. | зачтено | зачтено | ||
| Петров П.П. | зачтено | |||
| Сидоров О.Б. | зачтено | зачтено | ||
| Жукин Л.М. | зачтено | |||
| Пяткин Г.Л. | зачтено | |||
| Мишина П.Д. | зачтено | зачтено | ||
| Люкина Р.Д. | зачтено |
Задание 7
Провайдер Интернет-услуг установил следующую систему оплаты: при работе с 2 до 10 часов – 0,5$ в час; с 10 до 20 часов - 1$ в час; в остальное время суток – 0,75 $. Подготовить лист для определения стоимости 1 часа работы в Интернете, если известно, что время работы совпало с началом очередного часа и задано в формате время и что время работы находилось в одном из указанных интервалов суток. Предусмотреть пересчет стоимости работы по курсу в рублях, для чего в отдельной ячейке задать курс.
| Курс $ к рублю. | 28 600 | ||
| Клиент | Начало работы | Стоимость в $ | Стоимость в руб. |
| Петров П.П. | 19:00 | ||
| Сидоров О.Б. | 14:00 | ||
| Жукин Л.М. | 8:00 | ||
| Пяткин Г.Л. | 23:00 |
Задание 8
В библиотеке последний четверг каждого месяца – санитарный день. Для заданного года получить расписания санитарных дней на все месяцы. При решении задачи воспользоваться функциями работы с датами КОНМЕСЯЦА() и ДЕНЬНЕД(). Функция КОНМЕСЯЦА будет доступна, если выполнить следующие действия: Сервис/Надстройки/Пакет анализа. Теперь список функций пополнится. Синтаксис функции: КОНМЕСЯЦА(начальная_дата; число_месяцев). Число_месяцев – это число месяцев до или после указанной даты. Положительное значение аргумента означает будущую дату, отрицательное – прошедшую. Функция возвращает последнюю дату месяца, который отстоит на заданное число месяцев от текущей даты.
Условное форматирование
Цель: практические навыки применения условного форматирования в таблицах.
Краткие сведения
В Excel имеется средство, с помощью которого легко наложить формат (шрифт, границы, цвет) на ячейку или блок. В зависимости от значения, полученного в ячейке, ее формат будет меняться. Например, текст в ячейке можно окрасить в зеленый цвет, если объем продаж превосходит прогноз, и красным — если продажи упали.
Значения выделенных ячеек можно сравнивать с константой или с результатами вычислений по формуле. Чтобы определить условие форматирования при оценке данных в ячейках, не входящих в выделенный диапазон, или при проверке нескольких критериев, можно использовать логическую формулу. Если был выбран параметр формула, то указанная формула должна принимать значения ИСТИНА (1) или ЛОЖЬ (0). Формула должна начинаться со знака равенства (=). Вычисления по данной формуле могут производиться только на активном листе. Чтобы оценить данные на другом листе или в другой книге, на активном листе необходимо указать имя для данных на другом листе или в другой книге либо ввести ссылку на эти данные в ячейке активного листа, а затем сослаться на указанную ячейку или имя в формуле.
Например, чтобы оценить данные, содержащиеся в ячейке A5 на Листе1 книги Налоги.xls, в ячейку активного листа введите следующую ссылку, включая знак равенства (=): =[Налоги.xls]ЛИСТ1!$A$5.
При помощи формулы можно также оценивать критерии, не основанные на данных листа. Например, формула =ДЕНЬНЕД("12.5.99")=1 принимает значение ИСТИНА, если дата 12 мая 1999 г. является воскресеньем. Значение ячейки никак не влияет на истинность условия только в том случае, если в формуле нет специальных ссылок на форматируемые выделенные ячейки. Если же формула ссылается на выделенные ячейки, то в нее необходимо ввести ссылки на ячейки.
Ссылки на ячейки можно ввести в формулу, выделив ячейки непосредственно на листе. Выделение ячеек на листе вставляет абсолютные ссылки. Если необходимо, чтобы ссылки для каждой ячейки в выделенном диапазоне были согласованы, используйте относительные ссылки.
Даты и время рассматриваются как числа. Например, при сравнении содержимого ячейки с датой 7 января 2001 дата будет представлена в виде числа 36898.
В приведенном ниже примере условные форматы, примененные к ячейкам B3:E4, используются для анализа вклада ежеквартальных вложений в общий годовой итог. Если вложения, сделанные в течение квартала, составили 30 и более процентов от общего годового итога, то результаты отображаются полужирным шрифтом на сером фоне. Если же вложения, сделанные в течение квартала, составили 20 и менее процентов, то результаты отображаются полужирным белым шрифтом на черном фоне.
Выполните команду Формат / Условное форматирование… Заполните поля диалогового окна, как на рисунке 24. Для добавления второго условия нажмите кнопку А также >>.
Рис. 24. Условное форматирование
В формуле определяется относительная часть (номер строки) ссылки на ячейку $F3, чтобы каждая ячейка из диапазона B3:E4 сравнивалась с соответствующим значением итога в столбце F. Результат форматирования представлен на рисунке 25.
Рис. 25. Результаты форматирования
Теперь рассмотрим пример задания условного форматирования с помощью формулы. Для предыдущего примера выделите полужирным шрифтом на сером фоне все числа, кратные двум. На рисунке 26 приведена формула, которая должна оценивать все ячейки в диапазоне.
Рис. 26. Задание условного форматирования с помощью формулы
При вводе такой формулы в диалоговом окне Условное форматирование следует вводить ссылку только на активную ячейку в выделенном диапазоне. Microsoft Excel устанавливает ссылки для других ячеек относительно активной. Результат форматирования приведен на рисунке 27.
Рис. 27. Результат форматирования
Усложним задачу. Теперь нужно выделить полужирным шрифтом на сером фоне числа, кратные двум, но не кратные трем. На рисунке 28 показан пример заполнения диалогового окна.
Рис. 28. Логическая формула в условном форматирование
Введенная логическая формула И должна возвращать значение ИСТИНА или ЛОЖЬ. В ней фигурирует относительный адрес активной ячейки, входящей в блок. Результат форматирования представлен на рисунке 29.
Рис. 29. Результат форматирования
5.2 Задания для самостоятельной работы
В рабочей книге каждый лист отвести для решения одной задачи. Каждому листу дать название задание 1, задание 2, …, задание 7.
Отчет по выполненной работе должен содержать распечатку каждой отформатированной расчетной таблицы и таблицы с включенными формулами.
Задание 1
В таблице приведены данные о количестве легковых автомобилей, выпущенных отечественными автомобильными заводами в первом полугодии 2001 года (задание 1 практической работы 2). Выделить полужирным курсивом выпуск автомобилей, превышающий среднее значение за первый квартал. Выделить тройку лучших месяцев среднего выпуска автомобилей с помощью разной степени заливки ячеек серым цветом.
Задание 2
Заработная плата каждого из 12 работников фирмы за каждый месяц первого квартала записана в таблице задания 2 практической работы 2. Выделить полужирным белым шрифтом на черном фоне максимальную и минимальную зарплату. Выделить полужирным курсивом заработную плату, не превышающую среднего значения.
Задание 3
Известны данные о количестве осадков, выпавших за каждый день июля (задание 10 второй практической работы). Выделить красным полужирным курсивом величину осадков, превышающих среднее значение.
Задание 4
Известны оценки по Информатике каждого из 15 студентов (задание 8 второй практической работы). Выделить другим цветом ячейки с фамилиями студентов, имеющих оценку 5. Для решения задачи следует выделить диапазон ячеек, содержащий фамилии, и задать условный формат формулой, ссылающейся на ячейку, принимающую конкретное значение.
Задание 5
Известен возраст 30 человек (задание 9 второй практической работы). Выделить серым цветом строки (Ф.И.О. и возраст), содержащие информацию о претендентах для приема на работу в фирму, если по условиям возраст претендентов не превышает 35 лет. Здесь следует задавать условия отдельно для каждого столбца.
Задание 6
Известна сумма баллов, набранная студентами при сдаче контрольной точки (задание 10 второй практической работы). Выделить серым цветом строки с информацией о студентах, не сдавших контрольную точку, и полученных ими, если минимальное количество баллов равно 60.
Задание 7
По результатам сданной сессии студентам начисляется стипендия (задание 5 четвертой практической работы). Выделить строки различными шрифтами и видами заливки для студентов, получающих максимальную, среднюю и минимальную стипендии.
Ссылки и массивы
Цель: освоение средств извлечения информации из диапазонов данных.
Краткие сведения
Очень часто в практической работе встречаются задачи, для решения которых требуется использовать справочную таблицу. Например, по заданному номеру телефона найти фамилию или по названию государства - его столицу. Для решения подобных задач следует составить справочную таблицу, в первом столбце которой расположены поисковые значения (все номера телефонов), а во втором – фамилии абонентов. Специальная функция ВПР осуществит поиск в справочной таблице значений, соответствующих введенному вами аргументу и автоматически подставит найденное значение в той же строке из указанного второго столбца таблицы.
Синтаксис функции ВПР (вертикальный поисковый ряд).
ВПР(искомое значение; таблица; номер столбца; интервальный просмотр):
- Искомое значение – это значение, которое должно быть найдено в первом столбце массива. Оно может быть значением, ссылкой или текстовой строкой.
- Таблица – таблица с информацией, в которой ищутся данные. Можно использовать ссылку на интервал или имя интервала.
- Номер столбца – это номер столбца в массиве «таблица», в котором должно быть найдено соответствующее значение.
- Интервальный просмотр – в это окно вводится 0(ЛОЖЬ), если необходимо точное соответствие или 1(ИСТИНА), если соответствие приближенное. По умолчанию устанавливается приближенное соответствие, при котором, если точное значение не найдено, возвращается максимальное из значений, меньших, чем искомое. Если введена ИСТИНА, то значения в первом столбце массива должны быть отсортированы в возрастающем порядке.
Например, методист учебного отдела может работать с документами, используя справочную таблицу, в первой колонке которой указано наименование дисциплины, а во второй – количество учебных часов. Как только Методист введет в основную таблицу название дисциплины, компьютер автоматически подставит количество часов по этому предмету.
Итак, составьте на отдельном рабочем листе (в нашем примере на первом рабочем листе) таблицу следующего вида, как показано на рисунке 30. Затем перейдите на другой рабочий лист (например, второй лист) и составьте выписку из ведомости на учащегося, как показано на рисунке 31.
Для того чтобы получить в колонке Количество часов число часов, из справочной таблицы вызовите Мастер функции и категории Ссылки и массивы, выберите функцию ВПР. Заполните аргументы функции ВПР, как показано на рисунке 33, и нажмите ОК.
Скопируйте функцию из ячейки В3 в В4.
По аналогии с функцией ВПР работает функция ГПР (горизонтальный поисковый ряд), но в отличие от нее ищет значение в верхней строке массива и выводит его в заданном столбце.
Функция ПОИСКПОЗ очень похожа на функцию ВПР, но она возвращает позицию искомого значения в массиве, а не само значение. Синтаксис: ПОИСКПОЗ(искомое_значение; массив; тип_сопоставления). Искомое_значение — значение, используемое при поиске значения в таблице. Просматриваемый_массив — блок, состоящий из одного столбца или одной строки.. Тип_сопоставления — число -1, 0 или 1. Функция ПОИСКПОЗ находит первое значение, которое в точности равно аргументу искомое_значение в неупорядоченном массиве.
Например, дополним таблицу Выписка успеваемость колонкой «Номер в учебном плане», для чего введем это название в ячейку D3, а в ячейку D3 формулу.
= ПОИСКПОЗ(А3;ЛИСТ1!$А$2:$А$5;0).
Формула возвращает значение 3, так как содержимое ячейки А3 Физика – третий элемент в массиве ЛИСТ1!$А$2:$А$5 (Математика, Информатика, Физика, Философия).
 |  |
| Рис. 30. Справочная таблица на первом листе | Рис. 31. Основная таблица, в которой присутствуют справочные данные |
Рис. 32. Аргументы функции ВПР
6.2 Задания для самостоятельной работы
В рабочей книге каждый лист отвести для решения одной задачи. Каждому листу дать название задание 1, задание 2, …, задание 13.
Отчет по выполненной работе должен содержать распечатку каждой отформатированной расчетной таблицы и таблицы с включенными формулами.