Логические задачи в эксель с примерами и решениями
Обращаем Ваше внимание, что в соответствии с Федеральным законом N 273-ФЗ «Об образовании в Российской Федерации» в организациях, осуществляющих образовательную деятельность, организовывается обучение и воспитание обучающихся с ОВЗ как совместно с другими обучающимися, так и в отдельных классах или группах.
Рабочие листы и материалы для учителей и воспитателей
Более 2 500 дидактических материалов для школьного и домашнего обучения
Столичный центр образовательных технологий г. Москва
Получите квалификацию учитель математики за 2 месяца
от 3 170 руб. 1900 руб.
Количество часов 300 ч. / 600 ч.
Успеть записаться со скидкой
Форма обучения дистанционная
- Онлайн
формат - Диплом
гособразца - Помощь в трудоустройстве
Видеолекции для
профессионалов
- Свидетельства для портфолио
- Вечный доступ за 120 рублей
- 311 видеолекции для каждого
Антяскина Ольга
РЕШЕНИЕ ЗАДАЧ НА НАХОЖДЕНИЕ ЛОГИЧЕСКИХ ВЫРАЖЕНИЙ В EXCEL
1.1 Логические выражения в алгебре
Логическое выражение - это символическая запись высказывания, состоящая из логических величин (констант или переменных), объединенных логическими операциями (связками).
В математической логике не рассматривается конкретное содержание высказывания, важно только, истинно оно или ложно. Поэтому высказывание можно представить некоторой переменной величиной, значением которой может быть только 0 или 1. Если высказывание истинно, то его значение равно 1, если ложно - 0.
Связки "НЕ", "И", "ИЛИ" заменяются логическими операциями инверсия, конъюнкция, дизъюнкция. Это основные логические операции, при помощи которых можно записать любое логическое выражение.
Конъюнкция - логическое умножение (соответствует союзу «И», в алгебре высказываний обозначается «&»). Конъюнкция - это логическая операция, ставящая в соответствие каждым двум простым (или исходным) высказываниям составное высказывание, являющееся истинным тогда и только тогда, когда оба исходных высказывания истинны. Если хотя бы одно из составляющих высказываний ложно, то и полученное из них с помощью союза «И» сложное высказывание также считается ложным.
Дизъюнкция - логическое сложение (соответствует союзу «ИЛИ», в алгебре высказываний обозначается «V»).Дизъюнкция - это логическая операция, которая каждым двум простым (или исходным) высказываниям ставит в соответствие составное высказывание, являющееся ложным тогда и только тогда, когда оба исходных высказывания ложны и истинным, когда хотя бы одно из двух образующих его высказываний истинно. Если два высказывания соединены союзом "ИЛИ", то полученное сложное высказывание истинно, когда истинно, хотя бы одно из составляющих высказываний.
Инверсия - отрицание (обозначается в естественном языке словами «неверно, что. » и частице «не», в алгебре высказываний обозначается «¬»)
Отрицание - логическая операция, которая с помощью связки «не» каждому исходному высказыванию ставит в соответствие составное высказывание, заключающееся в том, что исходное высказывание отрицается.
Логическое следование (импликация): высказывание, составленное из двух высказываний при помощи связки «если . то . », называется логическим следованием, импликацией A => B.
Эквивалентность (логическое тождество): высказывание, составленное из двух высказываний при помощи связки «тогда и только тогда, когда», называется эквивалентностью A B . Новое высказывание, полученное с использованием эквивалентности, является истинным тогда и только тогда, когда оба исходных высказывания одновременно истинны или одновременно ложны.
В курсе математики решение задач, связанных с логическими выражениями достаточно трудоемкая работа, однако она значительно упрощается с использованием электронных таблиц, наибольшую популярность среди которых приобрел MS Excel.
1.2 Использование логических функций в Microsoft Excel 2010
В Microsoft Excel 2010 имеется возможность работы с различными типами функций, среди которых: математические, логические, статистические, инженерные, аналитические, финансовые и т.д. С учетом специфики нашего исследования более подробно остановимся на логических функциях.
Для начала работы с ними необходимо:
1) использовать мастер «Вставить функцию» (кнопка f x слева от строки формул) Категория Логические.
2) другой способ – Вкладка Формулы à Логические.
Категория Логические содержит семь функций, в том числе функции ЕСЛИ и ЕСЛИОШИБКА. Использование логических функций делает формулы более гибкими, а использование функции ЕСЛИ наделяет формулу способностью «принимать решения». Благодаря этому функция ЕСЛИ стала самой используемой логической функцией. Функция ЕСЛИОШИБКА имеется в библиотеке встроенных функций только в Excel 2010 (2007).
Существуют особенности записи логических операций в электронных таблицах: сначала записывается имя логической операции (И, ИЛИ, НЕ), а затем в круглых скобках перечисляются логические операнды.
Функция ЕСЛИ используется при проверке условий для значений и формул. ЕСЛИ (лог_выражение,значение_если_истина,значение_если_ложь) лог_выражение - любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ;
значение_если_истина - значение, которое возвращается, если аргумент лог_выражение - имеет значение ИСТИНА; если аргумент лог_выражение имеет значение ИСТИНА и аргумент значение_если_истина опущен, то возвращается значение ИСТИНА;
значение_если_ложь - значение, которое возвращается, если аргумент лог_выражение имеет значение ЛОЖЬ. Если аргумент лог_выражение имеет значение ЛОЖЬ и аргумент значение_если_ложь опущен, то возвращается значение ЛОЖЬ.
Пример: 1)введем в ячейку Е3 формулу = ЕСЛИ(Е1= 5; «Правильный ответ»; «Ошибка»).В строке лог_выражение вводим Е1=5; значение_если_истина вводим «Правильный ответ»; значение_если_ложь вводим «Ошибка».
2. Вводим в ячейку Е1 число 5. В ячейке Е3 появилась надпись «Правильный ответ». Такая конструкция позволяет создавать достаточно сложные тестовые программы, например, такие, в которых следует выбрать номер правильного варианта из многих.
3. Вводим в ячейку Е1 число ячейке Е3 появилось слово «Ошибка».
Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.
Возвращает значение ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает значение ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.
Аргументы: логическое_значение1,логическое_значение2. - от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.
Заменяет логическое значение аргумента на противоположное. Функция НЕ используется в тех случаях, когда необходимо иметь уверенность в том, что значение не равно некоторой конкретной величине.
Аргументы: логическое_значение - значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ. Если аргумент логическое_значение - имеет значение ЛОЖЬ, то функция НЕ возвращает значение ИСТИНА; если аргумент логическое_значение имеет значение ИСТИНА, то функция НЕ возвращает значение ЛОЖЬ.
Функции ИСТИНА и ЛОЖЬ предоставляют альтернативный способ записи логических значений ИСТИНА и ЛОЖЬ. Эти функции не имеют аргументов и выглядят следующим образом: =ИСТИНА(), =ЛОЖЬ().
Данная функция возвращает указанное значение, если вычисление по формуле вызывает ошибку; в противном случае функция возвращает результат формулы. Функция ЕСЛИОШИБКА позволяет перехватывать и обрабатывать ошибки в формулах.
Как правильно использовать логические формулы, а также решать конкретные задачи с их использованием, покажем на следующих примерах.
1.3 Примеры задач, связанные с нахождением решений логических выражений, средствами Excel
Отличительной особенностью задач, связанных с нахождением логических выражений, является то, что ответом к ним является не целочисленное значение, которое обычно мы привыкли указывать, а установление истинности или ложности некоторой словесной формулировки, отраженной в условии задачи. В этой связи, решая задачу средствами Excel, мы можем ответить не только на этот вопрос, но и представить соответствующий результат в виде графика, диаграммы и таблицы.
Рассмотрим некоторые примеры задач с использованием логической операции ЕСЛИ.
Задача1. Используя логическую функцию ЕСЛИ, определить кто из абитуриентов будет зачислен, по результатам ЕГЭ, в университет. Абитуриент будет зачислен, если сумма баллов за три экзамена составляет 270 и выше, и его балл по математике должен быть не менее 80 баллов.
Решение: функция ЕСЛИ выполняет проверку условия на истинность, и если оно верно - возвращает в ячейку с формулой заданное нами значение для "ИСТИНА", иначе - другое значение для "ЛОЖЬ".
Шаг 1. Используя логическую функцию ЕСЛИ, выявим у кого из абитуриентов, сумма баллов превышает порог для зачисления.
В столбце F будем вводить формулу о зачислении абитуриентов. В ячейку F 3 введем формулу, для этого делаем её активной. Затем на ленте выбираем вкладку Формулы à Логические à Если. Заполняем поля аргументов, следующим образом:
Лог_выражение - условие в форме логического выражения, которое в дальнейшем может принять два варианта - Истина или Ложь. В этой строке мы зададим условие E3>=$B$1. Ячейку B1 делаем абсолютной ссылкой - поскольку далее будем копировать формулу.
Значение_если_истина - значение, которое выведет программа, если логическое выражение будет верно. В этой строке мы пишем - "Да". Иначе говоря, если балл выше или равен проходному, то в столбце "Зачисление" выведется слово Да.
Значение_если_ложь - значение, которое выведет программа, если логическое выражение будет НЕ верно. В этой строке мы пишет - "Нет". Если балл будет ниже проходного, то получим Нет.
После того как поля аргументов заполнены , нажимаем ОК. В ячейки F 3 получилось Да. Для копирования формулы остальных ячеек, наведем указатель мыши на маркер, который находится в нижнем правом углу рамки выделения. При этом он примет вид черного плюсика. Щелкнем и, удерживая нажатием кнопку мыши, протянем рамку до ячейки F 8.
Шаг 2. Выясним кто, из представленных условий задачи, будет студентом университета.
Для добавлений новых условий используем логическую функцию И, которая позволяет добавлять новые условия истинности выражения и теперь выражение считается истинным, только если оба условия выполняются.
Чтобы вести второе условия, мы должны, щелкнуть ячейку F 3, затем в строке формул навести курсор на лог_выражение.
На ленте выбрать вкладку Формулы à Логические à И.Заполним поля аргументов следующим образом:
В строке лог_значение 1 вводим формулу: E3>=$B$1.
В строке лог_значение 2 вводим формулу: В3>=80
После того как поля аргументов заполнили наживаем ОК. В ячейки F 3 получилось Да. Используем маркер автозаполнения для распространения формулы на остальных абитуриентов. Получаем:
Ответ: из условий задачи, зачисленными будут считаться абитуриенты Антонов, Синичкин, Соколова.
Задача 2. Построить график функции:
Решение: для того чтобы построить данный график будем использовать функцию ЕСЛИ.
З ададим в ячейках А2-А22 произвольную область определений X ∊ [-2;2].Ячейку А2 введем число -2, а в ячейку А3 -1,8. Далее выделяются эти две ячейки и курсор мыши переводится в нижний правый угол выделенного диапазона до появления черного крестика. Нажимается и, удерживая кнопку мыши, переводится курсор на ячейку A22. Как видим, ячейки заполняются автоматически. Это одно из преимуществ MS Excel.
В ячейках В2-В22 введем саму фунцию зависищую от Х.В ячейку В2 введем формулу:
Заданная функция зависит от переменной Х.Если Х принимает отрицательное значение то график будет построен по уравнению 1+х, если x ≥1 то x ^2 в остальных случаях e ^ x .Получили в ячейки В2 число -1. Для распространения формулы на остальные ячейки используем маркер автозаполнения.
Теперь строим график, для этого выделяются ячейки В2-В22. Выберем на ленте вкладку Вставка à Диаграммы à Вставка диаграммы à График à Ок.
Ответ: с помощью функции ЕСЛИ мы построили график функции.
Задача 3. Составить таблицу истинности с помощью Excel .
Аня, Вика и Сергей решили пойти в кино. Учитель, хорошо знавший
ребят, высказал предложения:
1. Аня пойдет в кино только тогда, когда пойдут Вика и Сергей;
2. Аня и Сергей пойдут в кино вместе или же оба останутся дома;
3. Чтобы Сергей пошел в кино, необходимо, чтобы пошла Вика.
Когда ребята пошли в кино, оказалось, что учитель немного ошибся: из трех его утверждений истинными оказались только два. Кто из ребят пошел в кино?
Решение : обозначим простые высказывания:
А - Аня пойдет в кино;
В - Вика пойдет в кино;
С - Сергей пойдет в кино.
Каждое составное высказывание можно выразить в виде формулы - логического выражения:
2. ( A & C ) A & не C )
Составим таблицу истинности в Excel. Вводим функций в ячейках в виде ИСТИНА или ЛОЖЬ. Например для заполнения ячейки А2 выберем на ленте вкладку Формулы Логические Истина Ок. Для заполнения ячейки С2 выбираем на ленте вкладку Формулы → Логические →Ложь→Ок и т.д.
Для нахождений логического выражения нам потребуется вывести значения неА и неС. В ячейках D 2- D 8 найдем неА, а в ячейках Е2- Е8, неС. Чтобы получить неА в ячейке D 2, выбираем вкладку Формулы Логические Не А2 ОК. Получим функцию Ложь. Для распространения формулы на остальные ячейки используем маркер автозаполнения. Чтобы получить неС ячейке, Е2 выберем на ленте вкладку Формулы Логические Не С2 ОК. Получим функцию Истина. Для распространения формулы на остальные ячейки используем маркер автозаполнения. Получим:
Чтобы вывести формулу A ( B & C ) :
1) получим функцию в скобках. Для ячейки F 2 выберем на ленте вкладку Формулы Логические И лог_значение1 выберем ячейку B 2 лог_значение2 выберем ячейку С2 Ок. Получим Ложь. Автозаполнением заполним остальные ячейки до ячейки F 8.
2) теперь получим фунцию всей формулы в ячейках G 2- G 8. В ячейку G 2 вводим =А2= F 2. Затем автозаполнением заполняем остальные ячейки. Получим:
Чтобы вывести формулу ( A & C ) A & не C ) нужно:
1) получим функцию ( A & C )в ячейках Н2-Н8. Для ячейки H 2 выберем на ленте вкладку Формулы Логические И лог_значение1 выберем ячейку A 2 лог_значение2 выберем ячейку С2 Ок. Получим Ложь. Автозаполнением заполним остальные ячейки.
2) получим функцию (не A & не C )в ячейках I 2- I 8. Для ячейки I 2 выберем на ленте вкладку Формулы Логические И лог_значение1 выберем ячейку D 2 лог_значение2 выберем ячейку E 2 Ок. Получим Ложь. Автозаполнением заполним остальные ячейки.
3) теперь получим функцию всей формулы в ячейках J 2- J 8 . Для ячейки J 2 выберем на ленте вкладку Формулы Логические ИЛИ лог_значение1 выберем ячейку H 2 лог_значение2 выберем ячейку I 2 Ок. Получим Ложь. Автозаполнением заполним остальные ячейки.
Таблица примет вид:
Чтобы получить формулу C B . Вводим в ячейку К2 =С2= В2 и нажимаем ENTER .Затем автозаполнением заполним остальные ячейки. Таблица примет вид:
Ответ: т.к. два высказывания должны быть «истина», а одно «ложь», то правильный ответ следующий: Аня и Сергей не пойдут в кино, а пойдет Вика.
Пользователи Excel давно и успешно применяют программу для решения различных типов задач в разных областях.
Excel – это самая популярная программа в каждом офисе во всем мире. Ее возможности позволяют быстро находить эффективные решения в самых разных сферах деятельности. Программа способна решать различного рода задачи: финансовые, экономические, математические, логические, оптимизационные и многие другие. Для наглядности мы каждое из выше описанных решение задач в Excel и примеры его выполнения.
Решение задач оптимизации в Excel
Оптимизационные модели применяются в экономической и технической сфере. Их цель – подобрать сбалансированное решение, оптимальное в конкретных условиях (количество продаж для получения определенной выручки, лучшее меню, число рейсов и т.п.).
В Excel для решения задач оптимизации используются следующие команды:
- Подбор параметров («Данные» - «Работа с данными» - «Анализ «что-если»» - «Подбор параметра») – находит значения, которые обеспечат нужный результат.
- Поиск решения (надстройка Microsoft Excel; «Данные» - «Анализ») – рассчитывает оптимальную величину, учитывая переменные и ограничения. Перейдите по ссылке и узнайте как подключить настройку «Поиск решения».
- Диспетчер сценариев («Данные» - «Работа с данными» - «Анализ «что-если»» - «Диспетчер сценариев») – анализирует несколько вариантов исходных значений, создает и оценивает наборы сценариев.
Для решения простейших задач применяется команда «Подбор параметра». Самых сложных – «Диспетчер сценариев». Рассмотрим пример решения оптимизационной задачи с помощью надстройки «Поиск решения».
Условие. Фирма производит несколько сортов йогурта. Условно – «1», «2» и «3». Реализовав 100 баночек йогурта «1», предприятие получает 200 рублей. «2» - 250 рублей. «3» - 300 рублей. Сбыт, налажен, но количество имеющегося сырья ограничено. Нужно найти, какой йогурт и в каком объеме необходимо делать, чтобы получить максимальный доход от продаж.
Известные данные (в т.ч. нормы расхода сырья) занесем в таблицу:
На основании этих данных составим рабочую таблицу:
- Количество изделий нам пока неизвестно. Это переменные.
- В столбец «Прибыль» внесены формулы: =200*B11, =250*В12, =300*В13.
- Расход сырья ограничен (это ограничения). В ячейки внесены формулы: =16*B11+13*B12+10*B13 («молоко»); =3*B11+3*B12+3*B13 («закваска»); =0*B11+5*B12+3*B13 («амортизатор») и =0*B11+8*B12+6*B13 («сахар»). То есть мы норму расхода умножили на количество.
- Цель – найти максимально возможную прибыль. Это ячейка С14.
Активизируем команду «Поиск решения» и вносим параметры.
После нажатия кнопки «Выполнить» программа выдает свое решение.
Оптимальный вариант – сконцентрироваться на выпуске йогурта «3» и «1». Йогурт «2» производить не стоит.
Решение финансовых задач в Excel
Чаще всего для этой цели применяются финансовые функции. Рассмотрим пример.
Условие. Рассчитать, какую сумму положить на вклад, чтобы через четыре года образовалось 400 000 рублей. Процентная ставка – 20% годовых. Проценты начисляются ежеквартально.
Оформим исходные данные в виде таблицы:
Так как процентная ставка не меняется в течение всего периода, используем функцию ПС (СТАВКА, КПЕР, ПЛТ, БС, ТИП).
- Ставка – 20%/4, т.к. проценты начисляются ежеквартально.
- Кпер – 4*4 (общий срок вклада * число периодов начисления в год).
- Плт – 0. Ничего не пишем, т.к. депозит пополняться не будет.
- Тип – 0.
- БС – сумма, которую мы хотим получить в конце срока вклада.
Вкладчику необходимо вложить эти деньги, поэтому результат отрицательный.
Для проверки правильности решения воспользуемся формулой: ПС = БС / (1 + ставка) кпер . Подставим значения: ПС = 400 000 / (1 + 0,05) 16 = 183245.
Решение эконометрики в Excel
Для установления количественных и качественных взаимосвязей применяются математические и статистические методы и модели.
Дано 2 диапазона значений:
Значения Х будут играть роль факторного признака, Y – результативного. Задача – найти коэффициент корреляции.
Для решения этой задачи предусмотрена функция КОРРЕЛ (массив 1; массив 2).
Решение логических задач в Excel
В табличном процессоре есть встроенные логические функции. Любая из них должна содержать хотя бы один оператор сравнения, который определит отношение между элементами (=, >, =, Пример задачи. Ученики сдавали зачет. Каждый из них получил отметку. Если больше 4 баллов – зачет сдан. Менее – не сдан.
- Ставим курсор в ячейку С1. Нажимаем значок функций. Выбираем «ЕСЛИ».
- Заполняем аргументы. Логическое выражение – B1>=4. Это условие, при котором логическое значение – ИСТИНА.
- Если ИСТИНА – «Зачет сдал». ЛОЖЬ – «Зачет не сдал».
Решение математических задач в Excel
Средствами программы можно решать как простейшие математические задачки, так и более сложные (операции с функциями, матрицами, линейными уравнениями и т.п.).
Условие учебной задачи. Найти обратную матрицу В для матрицы А.
- Делаем таблицу со значениями матрицы А.
- Выделяем на этом же листе область для обратной матрицы.
- Нажимаем кнопку «Вставить функцию». Категория – «Математические». Тип – «МОБР».
- В поле аргумента «Массив» вписываем диапазон матрицы А.
- Нажимаем одновременно Shift+Ctrl+Enter - это обязательное условие для ввода массивов.
Возможности Excel не безграничны. Но множество задач программе «под силу». Тем более здесь не описаны возможности которые можно расширить с помощью макросов и пользовательских настроек.
Логические функции предназначены для проверки одного либо нескольких условий, и выполняют действия, предписанные для каждого из двух возможных результатов. Таковыми результатами могут являться только логические ИСТИНА или ЛОЖЬ.
В Excel содержится несколько логических функций, таких как ЕСЛИ, ЕСЛИОШИБКА, СУММЕСЛИ, И, ИЛИ и другие. Две последние на практике, как правило, самостоятельно не используют, поскольку результатом их вычислений может являться один из только двух возможных вариантов (ИСТИНА, ЛОЖЬ). При совместном использовании с функцией ЕСЛИ, они способны значительно расширить ее функционал.
Примеры использования формул с функциями ЕСЛИ, И, ИЛИ в Excel
Пример 1. При расчете стоимости количества потребленных кВт электроэнергии для абонентов учитывают следующие условия:
- Если в квартире проживают менее 3 человек или за месяц было потреблено менее 100 кВт электроэнергии, ставка за 1 кВт составляет 4,35 рубля.
- В остальных случаях ставка за 1кВт составляет 5,25 рубля.
Рассчитать сумму к оплате за месяц для нескольких абонентов.
Вид исходной таблицы данных:
Выполним расчет по формуле:
Растянем формулу для остальных ячеек с помощью функции автозаполнения. Результат расчета для каждого абонента:
Используя в формуле функцию И в первом аргументе в функции ЕСЛИ, мы проверяем соответствие значений сразу по двум условиям.
Формула с функциями ЕСЛИ и СРЗНАЧ для отбора значений при условии
Пример 2. Абитуриенты, поступающие в университет на специальность «инженер-механик», обязаны сдать 3 экзамена по предметам математика, физика и русский язык. Максимальный балл за каждый экзамен – 100. Средний проходной балл за 3 экзамена составляет 75, при этом минимальная оценка по физике должна составить не менее 70 баллов, а по математике – 80. Определить абитуриентов, которые успешно сдали экзамены.
Вид исходной таблицы:
Для определения зачисленных студентов используем формулу:
- И(B4>=80;C4>=70;СРЗНАЧ(B4:D4)>=75) – проверяемые логические выражения согласно условию задачи;
- "Зачисл." – результат, если функция И вернула значение ИСТИНА (все выражения, представленные в виде ее аргументов, в результате вычислений вернули значение ИСТИНА);
- "Не зач." – результат, если И вернула ЛОЖЬ.
Используя функцию автозаполнения (сделав двойной щелчок по маркеру курсора в нижнем правом углу), получим остальные результаты:
Формула с логическими функциями И ЕСЛИ ИЛИ в Excel
Пример 3. Субсидии в размере 30% начисляются семьям со средним уровнем дохода ниже 8000 рублей, которые являются многодетными или отсутствует основной кормилец. Если число детей свыше 5, размер субсидии – 50%. Определить, кому полагаются субсидии, а кому – нет.
Вид исходной таблицы:
Для проверки критериев согласно условию задачи запишем формулу:
Выполним расчет для первой семьи и растянем формулу на остальные ячейки, используя функцию автозаполнения. Полученные результаты:
Особенности использования логических функций ЕСЛИ, И, ИЛИ в Excel
Функция ЕСЛИ имеет следующую синтаксическую запись:
=ЕСЛИ( лог_выражение ;[значение_если_истина];[значение_если_ложь])
Как видно, по умолчанию можно выполнить проверку только одного условия, например, больше ли e3 числа 20? С использованием функции ЕСЛИ такую проверку можно выполнить так:
В результате будет возвращена текстовая строка «больше». Если нам потребуется узнать, принадлежит ли какое-либо значение указанному интервалу, потребуется сравнить это значение с верхней и нижней границей интервалов соответственно. Например, находится ли результат вычисления e3 в интервале от 20 до 25? При использовании одной лишь функции ЕСЛИ придется ввести следующую запись:
Имеем вложенную функцию ЕСЛИ в качестве одного из возможных результатов выполнения основной функции ЕСЛИ, в связи с чем синтаксис выглядит несколько громоздким. Если потребуется также узнать, например, равен ли корень квадратный e3 числовому значению из диапазона дробных чисел от 4 до 5, итоговая формула будет выглядеть громоздкой и неудобочитаемой.
Гораздо проще использовать в качестве условия сложное выражение, которое может быть записано с использованием функций И и ИЛИ. Например, приведенная выше функция может быть переписана следующим образом:
Иногда требуется узнать, оказалось ли хотя бы одно предположение верным. В этом случае удобно использовать функцию ИЛИ, которая выполняет проверку одного или нескольких логических выражений и возвращает логическое ИСТИНА, если результат вычислений хотя бы одного из них является логическим ИСТИНА. Например, требуется узнать, является ли e3 целым числом или числом, которое меньше 100? Для проверки такого условия можно использовать следующую формулу:
Запись «<>» означает неравенство, то есть, больше либо меньше некоторого значения. В данном случае оба выражения возвращают значение ИСТИНА, и результатом выполнения функции ЕСЛИ будет текстовая строка «верно». Однако, если бы выполнялась проверка ИЛИ(ОСТАТ(EXP(3);1)<>0;EXP(3)0 возвращает ИСТИНА.
На практике часто используют связки ЕСЛИ+И, ЕСЛИ+ИЛИ или сразу все три функции. Рассмотрим примеры подобного использования этих функций.
=ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь) - принимает одно из двух указанных значений, в зависимости от выполнения условия.
Если логическое выражение верно, то функция принимает первое значение. Если логическое выражение не верно, то функция принимает второе значение.
Пример : Студенты сдали экзамен. Требуется сделать заключение: если оценка 5, 4 или 3, то экзамен сдан, иначе – переэкзаменовка.
Запустить Excel и создать таблицу по образцу.
В ячейку D2 ввести формулу =ЕСЛИ(С2>=3;”Экзамен сдан”;”Переэкзаменовка”)
Скопировать эту формулу в нижележащие ячейки. Показать результаты преподавателю, сохранить файл с именем Лог-ф-1.
2. Логическая функция И
И(логическое_значение1; логическое_значение2;. ) - принимает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; принимает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.
Скопировать таблицу на Лист2 и удалить формулы в ячейках D 2: D 11. Изменить оценки и двум студентам вместо оценок ввести «неявка».
Скопировать эту формулу в нижележащие ячейки. Показать результаты преподавателю, сохранить файл с именем Лог-ф-2.
3. Логическая функция ИЛИ
ИЛИ(логическое_значение1; логическое_значение2; . ) - принимает значение ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; принимает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.
Скопировать таблицу на Лист3 и удалить формулы в ячейках D 2: D 11.
В ячейку D2 ввести формулу =ЕСЛИ(ИЛИ(С2=3;С2=4;С2=5);”Оценка положительная”;”Очень плохо”)
Скопировать эту формулу в нижележащие ячейки. Показать результаты преподавателю, сохранить файл с именем Лог-ф-3.
4. Статистическая функция СЧЕТЕСЛИ
=СЧЕТЕСЛИ( диапазон; критерий)
- Диапазон – блок ячеек, содержащий проверяемые значения;
- Критерий – константа или условие проверки;
Подсчитывает в указанном диапазоне количество значений, совпавших с критерием.
Пример : Необходимо подсчитать количество различных оценок по результатам экзамена.
Вставить Лист4, переместить его на место (за Лист3). Скопировать на этот лист диапазон ячеек А1:С11 с листа 1. Оформить ячейки столбца D по образцу.
В ячейку Е2 ввести формулу =СЧЕТЕСЛИ(С2:С11;5). В ячейку Е3 ввести формулу, используя мастер функций.
По аналогии ввести формулы в ячейки Е4 и Е5. При вводе формулы в ячейку Е6 в поле Критерий ввести неявка.
Показать результаты преподавателю, сохранить файл с именем Стат-ф-4.
5. Математическая функция СУММЕСЛИ
=СУММЕСЛИ( Диапазон; Критерий [; Диапазон суммирования ] )
- Диапазон – диапазон проверяемых значений;
- Критерий – константа или условие проверки;
- Диапазон суммирования – обрабатываемый диапазон. Если он отсутствует, то 1-й аргумент функции становится и проверяемым и обрабатываемым диапазоном;
- [ ] – указание на то, что данный аргумент функции может отсутствовать.
Вначале рассмотрим пример использования этой функции с двумя аргументами.
Вставить Лист5. Разместить листы по порядку. Оформить таблицу по образцу.
В ячейку А10 ввести текст Сумма, если >4000. В ячейку С10 ввести формулу =СУММЕСЛИ(С2:С8;">4000").
Из примера видно, что суммироваться будут значения только тех ячеек диапазона С2:С8, которые отвечают условию «>4000». В данном случае диапазон C 2: C 8 и проверяется и обрабатывается.
Рассмотрим использование функции с тремя аргументами.
В ячейку А11 ввести текст Сумма для НР. В ячейку С11 ввести формулу =СУММЕСЛИ(А2:А8; "НР";С2:С8).
В данном случае производится суммирование ячеек диапазона С2:С8 только в тех случаях, если в соседней ячейке диапазона А2:А8 находится запись «НР». Здесь диапазон А2:А8 проверяется, а диапазон С2:С8 обрабатываются.
Показать результаты преподавателю, сохранить файл с именем Мат-ф-5.
6. Вложенная функция ЕСЛИ
Вставить Лист6. Разместить листы по порядку. Оформить таблицу по образцу.
Предположим, что премия начисляется при следующих условиях:
- если средний балл меньше 3, то премия равна 0,
- если средний балл больше 3, но меньше 4.5, то премия равна 500р,
- если средний балл больше 4.5, то премия равна 800р.
Поместить курсор в ячейку D 2 и вызвать Мастера функций.
На первом шаге в левом окне выбрать категорию Логические. В правом окне выбрать функцию ЕСЛИ.
Щелкнуть по кнопке ОК. Появится окно функции ЕСЛИ.
На втором шаге Мастера функций в поле Логическое_выражение надо ввести условие, во втором поле - значение в случае Истина, а в третьем поле - значение в случае Ложь.
Значение среднего балла находится в ячейке С2. Поэтому надо щелкнуть мышью по кнопке с красной стрелочкой в верхнем поле справа. Диалоговое окно свернется в строку.
В ячейке D 2 будет отображено начало формулы. Надо щелкнуть по ячейке С2, а затем по кнопке с красной стрелочкой для возврата в окно функции ЕСЛИ. В формулу будет помещен адрес ячейки С2. Если щелкнуть нечаянно по другой ячейке, то адрес этой ячейки также будет помещен в формулу. Повнимательнее!
Во второе поле ввести значение для случая, когда C 2
Нажать клавишу Tab или щелкнуть мышкой в третьем поле.
В это поле надо поместить вложенную функцию ЕСЛИ. В строке формул слева имеется кнопка вызова функций.
В данном случае это функция ЕСЛИ. Надо щелкнуть по этой кнопке, чтобы в третье поле поместить вложенную функцию ЕСЛИ. Появится новое окно для вложенной функции ЕСЛИ.
Щелкнуть по кнопке ОК.
В ячейку D2 будет помещен результат расчета, а в строке формул отображена расчетная формула с вложенной функцией ЕСЛИ. Скопировать эту формулу в ячейки D 3: D 5 и посмотреть результаты расчета.
Показать преподавателю и сохранить файл с именем Если-влож.
Определить премию в зависимости от среднего балла при следующих условиях:
если >4, то премия равна 300.
Получить решение, показать преподавателю и сохранить файл с именем Если-сам.
Закрыть табличный процессор, убедиться, что строка активных задач пустая, закончить сеанс.
Обращаем Ваше внимание, что в соответствии с Федеральным законом N 273-ФЗ «Об образовании в Российской Федерации» в организациях, осуществляющих образовательную деятельность, организовывается обучение и воспитание обучающихся с ОВЗ как совместно с другими обучающимися, так и в отдельных классах или группах.
Рабочие листы и материалы для учителей и воспитателей
Более 2 500 дидактических материалов для школьного и домашнего обучения
Столичный центр образовательных технологий г. Москва
Получите квалификацию учитель математики за 2 месяца
от 3 170 руб. 1900 руб.
Количество часов 300 ч. / 600 ч.
Успеть записаться со скидкой
Форма обучения дистанционная
- Онлайн
формат - Диплом
гособразца - Помощь в трудоустройстве
Видеолекции для
профессионалов
- Свидетельства для портфолио
- Вечный доступ за 120 рублей
- 311 видеолекции для каждого
Тема Табличный процессор EXCEL -2003
Логические функции
1. Логическая функция ЕСЛИ
=ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь) - принимает одно из двух указанных значений, в зависимости от выполнения условия.
Если логическое выражение верно, то функция принимает первое значение. Если логическое выражение не верно, то функция принимает второе значение.
Пример : Студенты сдали экзамен. Требуется сделать заключение: если оценка 5, 4 или 3, то экзамен сдан, иначе – переэкзаменовка.
Запустить Excel и создать таблицу по образцу.
В ячейку D2 ввести формулу =ЕСЛИ(С2>=3;”Экзамен сдан”;”Переэкзаменовка”)
Скопировать эту формулу в нижележащие ячейки. Показать результаты преподавателю, сохранить файл с именем Лог-ф-1.
2. Логическая функция И
И(логическое_значение1; логическое_значение2;. ) - принимает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; принимает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.
Скопировать таблицу на Лист2 и удалить формулы в ячейках D 2: D 11. Изменить оценки и двум студентам вместо оценок ввести «неявка».
Скопировать эту формулу в нижележащие ячейки. Показать результаты преподавателю, сохранить файл с именем Лог-ф-2.
3. Логическая функция ИЛИ
ИЛИ(логическое_значение1; логическое_значение2; . ) - принимает значение ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; принимает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.
Скопировать таблицу на Лист3 и удалить формулы в ячейках D 2: D 11.
В ячейку D2 ввести формулу =ЕСЛИ(ИЛИ(С2=3;С2=4;С2=5);”Оценка положительная”;”Очень плохо”)
Скопировать эту формулу в нижележащие ячейки. Показать результаты преподавателю, сохранить файл с именем Лог-ф-3.
4. Статистическая функция СЧЕТЕСЛИ
=СЧЕТЕСЛИ( диапазон; критерий)
- Диапазон – блок ячеек, содержащий проверяемые значения;
- Критерий – константа или условие проверки;
Подсчитывает в указанном диапазоне количество значений, совпавших с критерием.
Пример : Необходимо подсчитать количество различных оценок по результатам экзамена.
Вставить Лист4, переместить его на место (за Лист3). Скопировать на этот лист диапазон ячеек А1:С11 с листа 1. Оформить ячейки столбца D по образцу.
В ячейку Е2 ввести формулу =СЧЕТЕСЛИ(С2:С11;5). В ячейку Е3 ввести формулу, используя мастер функций.
По аналогии ввести формулы в ячейки Е4 и Е5. При вводе формулы в ячейку Е6 в поле Критерий ввести неявка.
Показать результаты преподавателю, сохранить файл с именем Стат-ф-4.
5. Математическая функция СУММЕСЛИ
=СУММЕСЛИ( Диапазон; Критерий [; Диапазон суммирования ] )
- Диапазон – диапазон проверяемых значений;
- Критерий – константа или условие проверки;
- Диапазон суммирования – обрабатываемый диапазон. Если он отсутствует, то 1-й аргумент функции становится и проверяемым и обрабатываемым диапазоном;
- [ ] – указание на то, что данный аргумент функции может отсутствовать.
Вначале рассмотрим пример использования этой функции с двумя аргументами.
Вставить Лист5. Разместить листы по порядку. Оформить таблицу по образцу.
В ячейку А10 ввести текст Сумма, если >4000. В ячейку С10 ввести формулу =СУММЕСЛИ(С2:С8;">4000").
Из примера видно, что суммироваться будут значения только тех ячеек диапазона С2:С8, которые отвечают условию «>4000». В данном случае диапазон C 2: C 8 и проверяется и обрабатывается.
Рассмотрим использование функции с тремя аргументами.
В ячейку А11 ввести текст Сумма для НР. В ячейку С11 ввести формулу =СУММЕСЛИ(А2:А8; "НР";С2:С8).
В данном случае производится суммирование ячеек диапазона С2:С8 только в тех случаях, если в соседней ячейке диапазона А2:А8 находится запись «НР». Здесь диапазон А2:А8 проверяется, а диапазон С2:С8 обрабатываются.
Показать результаты преподавателю, сохранить файл с именем Мат-ф-5.
6. Вложенная функция ЕСЛИ
Вставить Лист6. Разместить листы по порядку. Оформить таблицу по образцу.
Предположим, что премия начисляется при следующих условиях:
- если средний балл меньше 3, то премия равна 0,
- если средний балл больше 3, но меньше 4.5, то премия равна 500р,
- если средний балл больше 4.5, то премия равна 800р.
Поместить курсор в ячейку D 2 и вызвать Мастера функций.
На первом шаге в левом окне выбрать категорию Логические. В правом окне выбрать функцию ЕСЛИ.
Щелкнуть по кнопке ОК. Появится окно функции ЕСЛИ.
На втором шаге Мастера функций в поле Логическое_выражение надо ввести условие, во втором поле - значение в случае Истина, а в третьем поле - значение в случае Ложь.
Значение среднего балла находится в ячейке С2. Поэтому надо щелкнуть мышью по кнопке с красной стрелочкой в верхнем поле справа. Диалоговое окно свернется в строку.
В ячейке D 2 будет отображено начало формулы. Надо щелкнуть по ячейке С2, а затем по кнопке с красной стрелочкой для возврата в окно функции ЕСЛИ. В формулу будет помещен адрес ячейки С2. Если щелкнуть нечаянно по другой ячейке, то адрес этой ячейки также будет помещен в формулу. Повнимательнее!
Во второе поле ввести значение для случая, когда C 2
Нажать клавишу Tab или щелкнуть мышкой в третьем поле.
В это поле надо поместить вложенную функцию ЕСЛИ. В строке формул слева имеется кнопка вызова функций.
В данном случае это функция ЕСЛИ. Надо щелкнуть по этой кнопке, чтобы в третье поле поместить вложенную функцию ЕСЛИ. Появится новое окно для вложенной функции ЕСЛИ.
Щелкнуть по кнопке ОК.
В ячейку D2 будет помещен результат расчета, а в строке формул отображена расчетная формула с вложенной функцией ЕСЛИ. Скопировать эту формулу в ячейки D 3: D 5 и посмотреть результаты расчета.
Показать преподавателю и сохранить файл с именем Если-влож.
Определить премию в зависимости от среднего балла при следующих условиях:
если >4, то премия равна 300.
Получить решение, показать преподавателю и сохранить файл с именем Если-сам.
Закрыть табличный процессор, убедиться, что строка активных задач пустая, закончить сеанс.
Читайте также: