Решение логических задач в Excel

Аннотация. Статья посвящена комплексному исследованию логических операций в табличном редакторе Excel. Выделяются и описываются характерные особенности использования логических функций. А также представлены сравнения различных операций. Для более четкого понимания приведены соответсвующие примеры. 

Ключевые слова: Excel, таблица, логические функции, функционал оператора, аргумент функции.

Excel – табличный редактор, инструмент, упрощающий работу с данными. Программа имеет большое количество различных эффективных инструментов, среди которых особое внимание стоит уделить логическим операторам. В основе логических операторов лежит установление истинности или ложности заданного выражения. Данная функция встроена в инструментарий формул и позволяет производить вычисления и заполнять ячейки на основе логических выражений. В этой статье описаны основные функции Excel, а также рассмотрен практический пример использования логических операторов.

Приведем наиболее часто используемые логические операторы:

• ИСТИНА;

• ЛОЖЬ;

• ЕСЛИОШИБКА;

• ЕОШИБКА;

• ЕСЛИ;

• ИЛИ;

• И;

• НЕ;

• ЕПУСТО.

Операторы «ИСТИНА» и «ЛОЖЬ»

Функция «ИСТИНА» принимает одно конкретное значение, так как не имеет собственных аргументов.

Функция «ЛОЖЬ» в отличии он функции «ИСТИНА» может принимать любые значения, являющиеся ложными.

Обе функции практически всегда используются, как дополнение для других сложных выражений.

Операторы «И» и «ИЛИ»

Запись оператора «И» выглядит следующим образом:

=И (лог_значение1; лог_значение2; …), возможное количество используемых аргументов – от 1 до 255.

Оператор «И» является связкой для нескольких условий логического выражения. Необходимо, чтобы аргументы оператора имели значение ИСТИНА, иначе вся логическая цепочка будет возвращать значение «ЛОЖЬ».

Запись оператора «ИЛИ» выглядит следующим образом:

=ИЛИ (лог_значение1; лог_значение2; …), возможное количество используемых аргументов – от 1 до 255.

В отличие от И, функция ИЛИ возвращает значение ИСТИНА даже если только один из используемых аргументов ему соответствует, а остальные – ложные.

Оператор «НЕ»

Запись оператора «НЕ» выглядит следующим образом:

=НЕ (лог_значение).

В операторе «НЕ» используется только один аргумент. Следовательно, результат функции (ИСТИНА/ЛОЖЬ) полностью зависит от значения аргумента.

Операторы «ЕСЛИ» и «ЕСЛИОШИБКА»

Запись оператора «ЕСЛИ» выглядит следующим образом:

=ЕСЛИ(логическое_выражение;значение_если_истина;значение_если-ложь).

Оператор «ЕСЛИ» позволяет построить сложные развернутые логические цепочки. Суть функции заключается в проверке логического выражения условия.  Если условие является истиной, то в результате возвращается одно выражение, а если ложью  - другое.

Запись оператора «ЕСЛИОШИБКА» выглядит следующим образом:

=ЕСЛИОШИБКА(значение;значение_если_ошибка).

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

Операторы «ЕОШИБКА» и «ЕПУСТО»

Запись оператора «ЕОШИБКА» выглядит следующим образом:

=ЕОШИБКА (значение)

Функция проверяет корректность заполненных ячеек (одной или диапазона), и, если ячейка неправильно заполнена, то возвращается результат ИСТИНА, иначе – ЛОЖЬ.

Примеры значений в неправильно заполненных ячейках:

• #ИМЯ?;

• #Н/Д;

• #ДЕЛ/0!;

• #ЧИСЛО!;

• #ЗНАЧ;

• #ПУСТО!;

• #ССЫЛКА!.

Запись оператора «ЕПУСТО» выглядит следующим образом:

=ЕПУСТО (значение)

Оператор осуществляет проверяет ячейку (диапазон ячеек) и возвращает ИСТИНА, если в ячейке (диапазоне ячеек) отсутствуют данные.  ЛОЖЬ - если в ячейке (диапазоне ячеек) присутствуют данные.  Аргумент функции – адрес конкретной ячейки или ссылка на диапазон ячеек.

Приведем примеры:

Рисунок 1. Решение логических задач (Лист 1)

В этом примере логических функций создадим отчёт о результатах выпускных экзаменов, которые должны сформировать пропускной бал при поступлении в гимназию. Экзаменов будет всего три: математика, русский язык и история, проходной бал для зачисления в гимназию будет равняться 12. Дополнительным условием можно добавить, что бы по экзамену с русского языка оценка должна быть не менее 4.

Рисунок 2. Решение логических задач (Лист 2)

В данном примере производится переоценка складских запасов с добавлением статистической функции СРЗНАЧ, которая будет определять товар, цена которого ниже среднего значения цены по прайсу и рекомендовать его к списанию. Применив условное форматирование можно выделить такие позиции.

Рисунок 3. Решение логических задач (Лист 3)

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

Рисунок 4. Решение логических задач (Лист 4)

Очень часто планируя затраты для получения прибыли, специалисты должны отслеживать платежи что бы суммы не выходили за границы лимита и можно было получить запланированную прибыль. По этому важно знать где и когда превышен лимит затрат, что бы в дальнейшем выправить ситуацию и получить положительный результат.

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

Современные табличные процессоры, в частности Microsoft Excel, представляют собой чрезвычайно мощное средство по решению широкого диапазона задач: от проведения простейших расчетов до создания средств автоматизации вычислений.

Изучение возможностей таких программ может принести студентам несомненную пользу при самостоятельной проработке учебного материала.