Общие сведения о логических выражениях
Создайте в рабочей книге Excel новый лист «Логика».
Введите в ячейку А1 формулу =7>5. Она вернет значение Истина. Теперь введите в ячейку А2 формулу =3>5. Эта формула вернет значение Ложь. Правые части обеих формул представляют собой высказывания, т.е. утверждение, относительно которых можно заключить, верны они или нет. Арифметические формулы, которые были рассмотрены выше, например формула =A1*B1, высказываниями не являются. Арифметические формулы предписывают, как по исходным данным вычислить значение и вопрос об их истинности или ложности не имеет смысла.
Рассмотрим другой пример. Введите в ячейку А4 число 2, а в ячейку B4 формулу =А4>3. Формула возвращает значение ЛОЖЬ. Теперь исправьте содержимое ячейки А4 – введите число 6. Формула возвращает значение ИСТИНА. В ячейке В4 записан предикат, т.е. высказывание с переменными (в данном случае переменная одна). В зависимости от значения переменных предикат может принимать значения Истина или ЛОЖЬ. В этом примере формула как бы дает ответ на вопрос: «Число (или результат вычислений по формуле), хранящийся в ячейке А4, превышает число 3?» В зависимости от значения А4 ответ будет либо ДА (ИСТИНА) либо НЕТ (ЛОЖЬ).
Сравнение двух арифметических выражений, содержащих переменные, дает предикат. В формуле =А4>3 ее составные части (А4 и 3) можно считать арифметическими выражениями, только очень простыми. Более сложный пример: =3*(A4^2-1)>(2*A4+1)/5.
Таблица 2
Операции сравнения
| > | >= | < | <= | = | <> |
| больше | больше или равно | меньше | меньше или равно | равно | не равно |
Обратите внимание, что символ отношения «больше или равно» изображается двумя знаками. Причина в том, что на клавиатуре отсутствует знак ≥.
Высказывание и предикат имеют общее название – логическое выражение. Имеются логические операции, которые позволяют строить сложные логические выражения. Эти операции реализованы в Excel как функции, перечень которых приведен в таблице 3 в порядке убывания приоритета.
Таблица 3
Логические операции
| Название | Обозначение | Функция Excel |
| Отрицание | ⌐ | НЕ |
| Конъюнкция | & | И |
| Дизъюнкция | V | ИЛИ |
На самом деле в Excel приоритет логических операций не имеет значения, так как они реализованы виде функций.
У логических функций аргументы могут принимать только два значения: ИСТИНА или ЛОЖЬ. Поэтому логические функции можно задавать таблицей, где перечислены все возможные значения аргументов и соответствующие им значения функций. Такие таблицы называются таблицами истинности (таблица 4 и таблица 5).
Функция НЕ может иметь только один аргумент, а функции И и ИЛИ два и более аргументов.
Таблица 4
Таблица истинности для функции НЕ
| х | НЕ(х) |
| ЛОЖЬ | ИСТИНА |
| ИСТИНА | ЛОЖЬ |
Таблица 5
Таблица истинности для функций И и ИЛИ
| х | у | И(х,у) | ИЛИ(х,у) |
| ЛОЖЬ | ЛОЖЬ | ЛОЖЬ | ЛОЖЬ |
| ЛОЖЬ | ИСТИНА | ЛОЖЬ | ИСТИНА |
| ИСТИНА | ЛОЖЬ | ЛОЖЬ | ИСТИНА |
| ИСТИНА | ИСТИНА | ИСТИНА | ИСТИНА |
На практике в числом виде логические выражения как правило не используются. Логическое выражение служит первым аргументом функции ЕСЛИ:
ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь).
В языках программирования высокого уровня этой функции соответствует оператор
если лог_выражение то действие1 иначе действие2
IF лог_выражение THEN действие1 ELSE действие2
Лог_выражение – это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100 – это логическое выражение; если значение в ячейке A10 равно 100, то выражение принимает значение ИСТИНА. В противном случае – ЛОЖЬ.
Например, сведения о предполагаемых и фактических расходах сведены в таблице в интервале A1:C4.
| Предполагаемые расходы | Фактические расходы | Бюджет |
Проверьте сведения на превышение бюджета. Для этого введите в ячейку С2 следующую формулу, которую затем скопируйте в ячейки С3 и С4 =ЕСЛИ(A2<B2;"Превышение бюджета";"Бюджет не превышен")
Частичные операции
В Excel есть термин – частичная сумма, которым обозначается операция суммирования чисел из заданного диапазона, но «не всех подряд», а только тех, которые удовлетворяют определенному условию. Например, функции СУММЕСЛИ и СЧЕТЕСЛИ.
Рассмотрим частичную операцию суммирования на примере подсчета суммы комиссионных для стоимости имущества более 250 000 руб.
Рисунок 17 – Частичная операция суммирования
В ячейку В6 введена функция СУММЕСЛИ, аргументы которой показаны в диалоговом окне на рисунке
Рисунок 18 – Диалоговое окно СУММЕСЛИ
В поле Диапазон задан диапазон А2:А5 в котором содержатся данные о стоимости имущества. Функция СУММЕСЛИ сравнивает каждое значение из диапазона вычисляемых ячеек А2:А6 с критерием отбора >250000, заданным в поле Критерий. Ячейки в поле Диапазон_суммирования суммируются, только если соответствующие им ячейки в аргументе «диапазон» удовлетворяют критерию. Если Диапазон_суммирования опущен, то суммируются ячейки в аргументе «диапазон».
В нашем случае в поле Диапазон_суммирования присутствуют фактические ячейки для суммирования – значения комиссионных. Функция СУММЕСЛИ просуммировала только те значения комиссионных (21 000р. + 28 000р.), для которых стоимость имущества превышает 250 000р.
Microsoft Excel предлагает дополнительные функции, которые можно применять для анализа данных с использованием условий. Например, для подсчета числа появлений текстовой строки или числа в пределах диапазона ячеек, можно воспользоваться функцией СЧЁТЕСЛИ.
Для получения формулы, возвращающей в зависимости от выполнения условия одно из двух значений, например вознаграждение по указанному объему продаж, можно воспользоваться функцией ЕСЛИ. Расширим понятие частичная сумма и введем термин частичная операция, которым будем обозначать любую из следующих операций – суммирование, подсчет количества чисел, поиск минимального или максимального числа, которые выполняются над числами, принадлежащими заданному диапазону и удовлетворяющие заданному пользователем критерию.
Будем считать, что компонента из диапазона будет включена в частичную операцию, если для нее выполняются заданные условия. Необходимо подчеркнуть, что условие включения в частичную операцию проверяется для каждой компоненты диапазона.
Формулы массива
Для поиска частичного максимума ли минимума, а также частичные подсчет и суммирование сложных условий могут быть выполнены с помощью формул массива. Такие формулы состоят из внешней функции СУММ, МАКС или МИН (собственно выполняющих их необходимую частичную операцию) и одной или нескольких вложенных функций ЕСЛИ, создающих виртуальный массив, над которыми эти операции выполняются.
Формула массива является мощным вычислительным элементом Excel. В отличие от обычных (скалярных) формул, вычисляющих значение только для одной ячейки, формулы массива вычисляют значения как для одной ячейки, так и для нескольких ячеек одновременно. Формулы массива заключены в фигурные скобки {}. Завершение ввода формулы массива осуществляется не простым нажатием клавиши Enter, а нажатием комбинации клавиш Ctrl+Shift+Enter, после чего формула автоматически «обрамляется» фигурными скобками.
Допустим, требуется вычислить сумму абсолютных значений чисел, содержащихся в диапазоне А1:А5. Если не использовать формулу массива, то единственный способ решения этой задачи состоит в том, чтобы в соседнем диапазоне, например, В1:В5, с помощью обычной функции ABS создать абсолютные значения и потом просуммировать их, например, в ячейке С1 с помощью функции СУММ.
Для того, чтобы ускорить процесс вычислений с помощью единственной формулы воспользуемся формулой массива {=СУММ(ABS(A1:A5))}.
Специально подчеркнем, что хотя функция ABS в этой формуле массива и возвращает пять значений, но ни в какие ячейки они не записываются, а просто используются в качестве слагаемых функцией СУММ. В этом случае можно говорить, что в формуле массива функция ABS возвращает виртуальный массив значений.
С учетом вышеизложенного, формулы массива, реализующие рассматриваемые задачи частичных операций могут быть записаны в следующем виде:
| подсчет | {=СУММ(ЕСЛИ(Усл; 1; 0))} | суммирует единицы |
| суммирование | {=СУММ(ЕСЛИ(Усл; А; 0))} | суммирует отобранные элементы |
| максимальный | {=МАКС(ЕСЛИ(Усл; А))} | находит наибольшее среди отобранных |
| минимальный | {=МИН(ЕСЛИ(Усл; А))} | находит наименьшее среди отобранных |
Рассмотрим использование формул массива на примере поиска минимальной выручки торговой точки, превышающую заданную величину, например 1000 р.
Исходные данные для решения задачи содержатся в диапазоне A1:B10 рабочего листа Excel. Размер выручки записан в ячейках диапазона В2:В10. Если для нахождения величины минимальной выручки ввести в ячейку С2 функцию =МИН(B2:B10) – вы найдете самую минимальную выручку 850 р., которая не удовлетворяет заданному условию – меньше 1000р. Для того, чтобы «отсечь» лишние значения и сформировать виртуальный диапазон, в котором отсутствуют компоненты, неудовлетворяющие заданному условию включим в функцию МИН функцию ЕСЛИ, задающую условия отбора.
{=МИН(ЕСЛИ(В2:В10>1000; В2:В10))}
Функция ЕСЛИ в части условия будет поэлементно сравнивать значения в диапазоне В2:В10 c заданным критерием отбора >1000 и формировать виртуальный массив из которого будут исключены элементы неудовлетворяющие заданному критерию, т.е элементы 900р. и 850р..
Значение критерия отбора можно задавать в отдельной ячейке для того, чтобы меняя его, получать новое требуемое значение.
Лекция №3. Моделирование функций И и ИЛИ в формулах массива. Извлечение данных из массива значений. Задание критериев для извлечения данных из массива