Логические функции и условное форматирование в электронных таблицах Excel и Calc на примере решения одной задачи

ТАБЛИЧНЫЙ ПРОЦЕССОР МБОУ СОШ № 1 г. Сургута Тема: «Логические функции. Условное форматирование» Д.В. Жданов, учитель информатики Цель: демонстрация возможностей логических функций, входящих в состав инструментария табличных процессоров (электронных таблиц) для решения спектра пользовательских задач и их применение в различных практических сферах деятельности человека. Задачи: Продемонстрировать принцип использования «мастера функций» в различных приложениях.Познакомить с категорией логических функций и изучить формат записи функции ЕСЛИ (IF).Научить применять функцию ЕСЛИ (IF) в зависимости от поставленной цели задачи.Продемонстрировать возможности условного форматирования для визуализации данных и результатов расчетов при решении задач в различных областях деятельности. Методы: 1. Теоретические:а) актуализация знанийб) изучение нового материала;в) анализ;г) синтез;д) сравнение;е) абстрагирование; 2. Практические:а) решение задач;б) компьютерное моделирование; Этапы реализации: Изучение нового материала с одновременной актуализацией прошлых знаний.Демонстрация решения задачи с предварительным анализом условия и обсуждением алгоритма решения.Самостоятельная практическая работа.Заключение (подведение итогов). Выбор приложения Microsoft Office Open Office Microsoft Excel 2003 Общая теория Пример использования логической функции в решении задачи Задание Для решения логических задач в табличном процессоре используются логические функции. Они позволяют выполнять логические операции над условиями (логическими выражениями). Условие представляет собой величины и/или выражения одного типа, связанные одним из знаков отношений: >, <, >=, <= ,<>.Условия могут быть составлены из данных, ссылок и выражений. Любое условие может принимать значение «ИСТИНА» (логическая единица) либо «ЛОЖЬ» (логический нуль).В качестве примера рассмотрим логическую функцию «ЕСЛИ», которая имеет следующий формат записи: =ЕСЛИ (Условие; Выражение 1; Выражение 2).Алгоритм действия этой функции:проверяется истинность записанного условия логического выражения);в ячейку, в которой находится эта функция, заносится значение «Выражение 1», если проверяемое условие истинно, или значение «Выражение 2», если условие ложно.  Далее В начало Выбор приложения Полное и неполное ветвленияИспользование функции «ЕСЛИ» можно сравнить со структурой ветвления с которой Вы знакомились в 6 и 7 классах.Полное ветвление – это ветвление, в котором при выполнении условия (условие истинно) в ячейку будет занесено одно выражение, а при невыполнении (условие ложно) будет занесено другое выражение. Рассмотренная ранее структура является полным ветвлением. Существует возможность опустить последний аргумент (Выражение 2). =ЕСЛИ (Условие; Выражение 1)В этом случае, при выполнении условия, в ячейку будет занесено Выражение 1, а случай, когда условие будет ложно, в ячейке будет отображаться результат проверки условия на истинность, т.е. «ЛОЖЬ». В начало Выбор приложения Назад Действие Условие да нет Действие 1 Условие да нет неполное ветвление полное ветвление Действие 2 В случае выполнении условия (истинности логического выражения) выполняется действие, а при ложности выражения (условия) никаких действий не производится. В случае выполнении условия (истинности логического выражения) выполняется действие 1, а при ложности выражения (условия) - действие 2. Назад Для решения логических задач в табличном процессоре используются логические функции. Они позволяют выполнять логические операции над условиями (логическими выражениями). Условие представляет собой величины и/или выражения одного типа, связанные одним из знаков отношений: >, <, >=, <= ,<>.Условия могут быть составлены из данных, ссылок и выражений. Любое условие может принимать значение «ИСТИНА» (логическая единица) либо «ЛОЖЬ» (логический нуль).В качестве примера рассмотрим логическую функцию «IF», которая имеет следующий формат записи: =IF (Условие; Выражение 1; Выражение 2).Алгоритм действия этой функции:проверяется истинность записанного условия (логического выражения);в ячейку, в которой находится эта функция, заносится значение «Выражение 1», если проверяемое условие истинно, или значение «Выражение 2», если условие ложно.  Далее В начало Выбор приложения Полное и неполное ветвленияИспользование функции «IF» можно сравнить со структурой ветвления с которой Вы знакомились в 6 и 7 классах.Полное ветвление – это ветвление, в котором при выполнении условия (условие истинно) в ячейку будет занесено одно выражение, а при невыполнении (условие ложно) будет занесено другое выражение. Рассмотренная ранее структура является полным ветвлением. Существует возможность опустить последний аргумент (Выражение 2). =IF (Условие; Выражение 1)В этом случае, при выполнении условия, в ячейку будет занесено Выражение 1, а случай, когда условие будет ложно, в ячейке будет отображаться результат проверки условия на истинность, т.е. «ЛОЖЬ». Назад Далее Сформулируем задачу: Требуется определить, достиг ли Ваш товарищ совершеннолетия или нет? В случае положительного ответа напротив соответствующего имени отобразить слово «Совершеннолетний», если нет, посчитать, сколько дней отделяет его от 18-тилетия.Вспомним, что дата в табличном процессоре является числом, поэтому до совершеннолетия человеку требуется прожить 17 лет, 12 месяцев и 31 день, что соответствует 6575 дням (високосные годы учитывать не будем). Поэтому сформулируем условие: если количество прожитых дней со дня рождения до текущей даты больше или равно 6575, то он совершеннолетний, иначе требуется найти разность 6575 и количества прожитых дней. OO Calc MS Excel Назад Далее Примерная форма таблицы: Назад Далее Алгоритм решения задачи: Определим количество дней прожитых с даты рождения по сегодняшнее число. Для этого в ячейку C2 запишем формулу: =B$12-B2. Обратите внимание, что здесь мы использовали как относительную, так и абсолютную ссылки).Скопировать введенную формулу вниз. В диапазоне ячеек Вы увидите значения в формате «Дата» (количество прожитых дней.месяцев.лет) со дня своего рождения.Измените формат на числовой. В ячейку D2 запишем формулу содержащую функцию «ЕСЛИ», согласно сформулированного ранее условия: =ЕСЛИ(C2>=6575;"Совершеннолетний";6575-C2) и скопируем ее вниз. Ввод функции можно реализовать и с помощью «Вставки функции».Произвести условное форматирование по заданным параметрам. Назад В ячейках таблицы могут содержаться числа, текст и формулы. Электронные таблицы могут избавить от рутинных математических расчетов, особенно при решении однотипных задач. Другими словами электронные таблицы помогают автоматизировать расчеты. Реализовать их можно, подставляя данные из определенных ячеек в формулу, которая заносится в конкретную ячейку. Формула может содержать числа, ссылки, функции.Запись формулы начинается со знака «=» Коротко о формулах: Адрес ячейки – это уникальный идентификатор элементарного объекта электронной таблицы, образованного пересечением столбца и строки и представляющий собой обозначение этих элементов.При указании адреса требуется помнить, что сперва записывается обозначение столбца, а затем без пробела следует номер строки!Например: A5, F15.Ссылкой называется адрес объекта (ячейки, группы ячеек), используемый при записи формулы или функции.Относительная ссылка – адрес ячейки, автоматически изменяющийся при копировании формулы.Абсолютная (фиксированная ссылка) - адрес ячейки, не изменяющийся автоматически при копировании формулы. В записи ссылки необходимо поставить знак «$» перед фиксируемым элементом. Коротко об адресах и ссылках: Назад сделайте ячейку с формулой текущей; подведите указатель мыши к правому нижнему углу этой ячейки так, чтобы указатель превратился в черный крестик; нажмите на левую кнопку мыши и удерживая ее переместите указатель вниз вплоть до необходимой ячейки; отпустите кнопку. Копирование данных: Назад Форматирование – процесс, связанный с оформлением данных и объектов электронной таблицы в желаемом виде.Числовой формат – обеспечивает представление чисел с определенным количеством десятичных знаков, которое задается пользователем.В диалоговом окне «Формат ячеек» на вкладке «Число» выберите числовой формат с указанием десятичных знаков «0», т.к. количество прожитых дней – это целое число. Изменение формата представления данных: Назад Использование диалога «Вставка функции»: Выделите ячейку D2.В меню «Вставка» выберите пункт «Функция».В диалоговом окне выберите категорию «Логические» - «ЕСЛИ».Введите аргументы функции: Логическое выражение (C2>=6575), значение для истинности (Совершеннолетний) и для ложности (6575-C2). Обратите внимание, что текстовые данные, выступающие в качестве аргумента должны быть заключены в кавычки. Назад Условное форматирование: Форматирование – процесс, связанный с оформлением данных и объектов электронной таблицы в желаемом виде.Имея представление о технологии применения логической функции «ЕСЛИ» и имея навыки по форматированию табличных документов, можно организовать форматирование ячейки в зависимости от заданного Вами условия. Такое форматирование называется условным. В табличном процессоре Вы имеете возможность задать до трех условий, в зависимости от истинности которых можете определить различные варианты форматирования данных в ячейках.Сформулируем критерии для применения такого форматирования:Выделить красным полужирным курсивом значения дней до совершеннолетия, если до него осталось меньше года (<365 дней). Для этого… Назад Условное форматирование: Выделим (сделаем текущей) ячейку D2.В меню «Формат» выберем пункт «Условное форматирование».В диалоговом окне введем необходимое условие и зададим формат отображения данных, щелкнув по кнопке «Формат». В случае необходимости Вы можете добавить еще 2 условия (А также>>).4. Подтвердив выбор, Вы увидите, что изменения произошли только в ячейке D2. Это связано с тем, что условное форматирование может быть применено только к одной ячейке. Поэтому Вам необходимо еще раз скопировать формулу из ячейки D2 вниз. Форматирование при этом будет наследоваться всеми ячейками. Результат Результат: Как? Все понятно! Задание: Имеются данные по итогам Первенства города по лыжным гонкам (Файл: Гонка в корневом каталоге диска «X»: (при необходимости воспользуйтесь поиском файлов на Вашем компьютере)).Требуется:Осуществить сортировку в порядке возрастания по результату (результат определяется как время финиша - время старта).Добавить столбец «Проигрыш победителю» (определяется как результат участника – результат победителя).Добавить столбец «Коэффициент» (определяется как проигрыш победителю в числовом формате).По коэффициенту присвоить спортивный разряд: до 0,015 числового формата – 3 юношеский разряд (рекомендация: применяйте логическую функцию (к сожалению, другие разряды не могут быть присвоены на данной трассе), поэтому всем остальным участникам ставим «б/р» (без разряда)).В столбце «Коэффициент» красным полужирным курсивом выделяем «3 юр». Не стоит забывать и о «резерве». Выделите синим полужирным курсивом коэффициент участников близкий к этому значению (от 0,015 до 0,017).Расставьте места согласно результатов (сортировка данных). Результат Назад Open Office Calc Общая теория Пример использования логической функции в решении задачи Задание Назад Далее Примерная форма таблицы: Назад Далее Алгоритм решения задачи: Определим количество дней прожитых с даты рождения по сегодняшнее число. Для этого в ячейку C2 запишем формулу: =B$12-B2. Обратите внимание, что здесь мы использовали как относительную, так и абсолютную ссылки).Скопировать введенную формулу вниз. В диапазоне ячеек Вы увидите значения в формате «Дата» (количество прожитых дней.месяцев.лет) со дня своего рождения.Измените формат на числовой. В ячейку D2 запишем формулу содержащую функцию «IF», согласно сформулированного ранее условия: =IF(C2>=6575;"Совершеннолетний";6575-C2) и скопируем ее вниз. Ввод функции можно реализовать и с помощью «Вставки функции».Произвести условное форматирование по заданным параметрам. Назад В ячейках таблицы могут содержаться числа, текст и формулы. Электронные таблицы могут избавить от рутинных математических расчетов, особенно при решении однотипных задач. Другими словами электронные таблицы помогают автоматизировать расчеты. Реализовать их можно, подставляя данные из определенных ячеек в формулу, которая заносится в конкретную ячейку. Формула может содержать числа, ссылки, функции.Запись формулы начинается со знака «=» Коротко о формулах: Адрес ячейки – это уникальный идентификатор элементарного объекта электронной таблицы, образованного пересечением столбца и строки и представляющий собой обозначение этих элементов.При указании адреса требуется помнить, что сперва записывается обозначение столбца, а затем без пробела следует номер строки!Например: A5, F15.Ссылкой называется адрес объекта (ячейки, группы ячеек), используемый при записи формулы или функции.Относительная ссылка – адрес ячейки, автоматически изменяющийся при копировании формулы.Абсолютная (фиксированная ссылка) - адрес ячейки, не изменяющийся автоматически при копировании формулы. В записи ссылки необходимо поставить знак «$» перед фиксируемым элементом. Коротко об адресах и ссылках: Назад сделайте ячейку с формулой текущей; подведите указатель мыши к правому нижнему углу этой ячейки так, чтобы указатель превратился в черный крестик; нажмите на левую кнопку мыши и удерживая ее переместите указатель вниз вплоть до необходимой ячейки; отпустите кнопку. Копирование данных: Назад Форматирование – процесс, связанный с оформлением данных и объектов электронной таблицы в желаемом виде.Числовой формат – обеспечивает представление чисел с определенным количеством десятичных знаков, которое задается пользователем.В диалоговом окне «Формат ячеек» на вкладке «Число» выберите числовой формат с указанием десятичных знаков «0», т.к. количество прожитых дней – это целое число. Изменение формата представления данных: Назад Использование диалога «Вставка функции»: Выделите ячейку D2.В меню «Вставка» выберите пункт «Функция».В диалоговом окне выберите категорию «Логические» - «IF». Назад Условное форматирование: Форматирование – процесс, связанный с оформлением данных и объектов электронной таблицы в желаемом виде.Имея представление о технологии применения логической функции «IF» и имея навыки по форматированию табличных документов, можно организовать форматирование ячейки в зависимости от заданного Вами условия. Такое форматирование называется условным. В табличном процессоре Вы имеете возможность задать до трех условий, в зависимости от истинности которых можете определить различные варианты форматирования данных в ячейках.Сформулируем критерии для применения такого форматирования:Выделить красным полужирным курсивом значения дней до совершеннолетия, если до него осталось меньше года (<365 дней). Для этого… Назад Условное форматирование: Выделим (сделаем текущей) ячейку D2.В меню «Формат» выберем пункт «Условное форматирование».В диалоговом окне введем необходимое условие и зададим формат отображения данных, щелкнув по кнопке «Новый стиль…». В случае необходимости Вы можете добавить еще 2 условия (соответствующий флажок).4. Подтвердив выбор, Вы увидите, что изменения произошли только в ячейке D2. Это связано с тем, что условное форматирование может быть применено только к одной ячейке. Поэтому Вам необходимо еще раз скопировать формулу из ячейки D2 вниз. Форматирование при этом будет наследоваться всеми ячейками. Результат Результат: Как? Все понятно! Задание: Имеются данные по итогам Первенства города по лыжным гонкам (Файл: Гонка в корневом каталоге диска «X»: (при необходимости воспользуйтесь поиском файлов на Вашем компьютере)).Требуется:Осуществить сортировку в порядке возрастания по результату (результат определяется как время финиша - время старта).Добавить столбец «Проигрыш победителю» (определяется как результат участника – результат победителя).Добавить столбец «Коэффициент» (определяется как проигрыш победителю в числовом формате).По коэффициенту присвоить спортивный разряд: до 0,015 числового формата – 3 юношеский разряд (рекомендация: применяйте логическую функцию (к сожалению, другие разряды не могут быть присвоены на данной трассе), поэтому всем остальным участникам ставим «б/р» (без разряда)).В столбце «Коэффициент» красным полужирным курсивом выделяем «3 юр». Не стоит забывать и о «резерве». Выделите синим полужирным курсивом коэффициент участников близкий к этому значению (от 0,015 до 0,017).Расставьте места согласно результатов (сортировка данных). Результат Назад