Какое логическое выражение является простым. Работа в группах на компьютере. Ролевая игра. Задача «подсчет стоимости телефонных переговоров»

1. Условное форматирование

Самая простая логика. Если содержимое ячейки больше (меньше, равно, не равно и т.д.) определенного значения, то - срабатывает определенное форматирование для этой ячейки (заливка нужным цветом, цвет и начертание шрифта, границы и т.д.)

Выделите ячейки, которые должны автоматически менять свой цвет и выберите в меню Формат - Условное форматирование (Format - Conditional formatting) .

В открывшемся окне можно задать условия и, нажав затем кнопку Формат , параметры форматирования ячейки, если условие выполняется:

2. Условное форматирование с формулами

Можно усложнить критерии проверки условного форматирования, если проверять не значение, а формулу. В этом случае Вы можете проверять одни ячейки, а форматировать - другие. Вот так, например, можно выделить цветом все ячейки со значениями больше среднего:

3. Функция ЕСЛИ (IF)

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

Рассмотрим жизненный пример. Имеем следующую таблицу:

Решение - использовать для расчета функцию ЕСЛИ со следующими параметрами:

То бишь, если количество превышает 5, то человек платит не полную стоимость (B2*C2), а только 90% от нее (B2*C2*0,9).

4. Вложенные ЕСЛИ

Сама по себе одна функция ЕСЛИ может проверить только одно условие. Поэтому, в случае, когда необходимо проверить сразу несколько условий, приходится вкладывать одну функцию ЕСЛИ в другую. Выглядит это примерно следующим образом:

В данном примере проверяется скорость движения автомобиля. Если она больше 110, то выводится предупреждение "Слишком быстро!". В противном случае проверяется - не слишком ли медленно едет водитель, и если нет, то выводится сообщение "Все правильно!"

Excel разрешает вкладывать функции ЕСЛИ друг в друга до 7 раз включительно. Хотя вид такой формулы скорее всего будет вызывать легкую икоту.

5. ЕСЛИ + И + ИЛИ (IF,AND,OR)

Функции И и ИЛИ из категории Логические способны заметно улучшить наглядность и понятность сложных логических проверок. Предыдущий пример с проверкой скорости можно было бы гораздо компактнее и красивее реализовать, например, вот так:

6. Функции СЧЁТЕСЛИ и СУММЕСЛИ (COUNTIF, SUMIF)

Эти функции надо искать не в категории Логические , а в категориях Статистические и Математические , соответственно (или в полном алфавитном перечне).

СЧЁТЕСЛИ - подсчитывает количество ячеек в диапазоне, удовлетворяющих заданному условию, а СУММЕСЛИ - суммирует их значения:

Логические выражения

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

Ниже представлен список операторов сравнения Excel:

Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0).

Функция ЕСЛИ

Функция ЕСЛИ – это функция, которая позволяет вывести в ячейку одно значение, если заданное пользователем условие выполняется и другое – если условие не выполняется.

Синтаксис:

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

Переместить курсор в ячейку D 2 . С помощью мастера функций выбрать из категорииЛогические функциюЕСЛИ (рис. 27), а затем щелкнуть на кнопке ОК.

Диалоговое окно Аргументы функции (рис. 28) содержит три поля ввода. В полеЛог _ выражение необходимо ввести условие, которое определяет, превышает ли проданное кол-во товара 5 шт., следовательно введем в это полеС2>5 . В полеЗначение_если_истина необходимо ввести формулу, которая вычисляет стоимость товара с учетом скидки, тогда введем в это полеB2*C2-B2*C2*0,1 . В полеЗначение_если_ложь необходимо ввести формулу, которая вычисляет стоимость товара без учета скидки (условиеС2>5 -Ложь), в тогда введем в это полеB2*C2 (см. рис. 29). Теперь щелкнем на кнопкеOK.

Скопировать полученную формулу в смежные ячейки. Результаты вычислений формул приведены на рис. 30.

Функции ЕСЛИ могут быть вложены друг в друга в качестве значений аргументов значение_если_истина и значение_если_ложь. С помощью таких вложенных функций Если можно конструировать более сложные проверки. Рассмотрим на примерах, как необходимо использовать вложенной функцией ЕСЛИ .

На рис. 31 приведена таблица с данными. Необходимо:

Определить, на какую сумму продано товаров каждого вида.

Определить, на какую сумму продано товаров каждого вида с учетом скидки (сумма с учетом скидки = сумма - сумма * скидка). Скидка начисляется по следующему принципу: если продано товара на сумму более 2500 грн., то скидка составит 5%, если продано товара на сумму менее 1100 грн., то скидка составит 0%, в остальных случаях скидка составит 2%.

Для того чтобы выполнить первый пункт задания необходимо в ячейку D2 ввести формулу=C2*B2 .

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

Переместим курсор в ячейку E2 и с помощью мастера функций введем следующую формулу (рис. 33 – 34). Результаты решения приведены на рис. 35.

Щелкнуть тут

Рис. 35

Функции ИЛИ, И

Функции И и функция ИЛИ из категории Логические способны заметно улучшить наглядность и понятность сложных логических проверок. Функция ИЛИ и функция И позволяют задавать в формуле одновременно несколько условий, т.е. дают возможность создавать сложные логические выражения. Эти функции работают в сочетании с простыми операторами сравнения. Функции И и ИЛИ могут иметь до 30 логических аргументов и имеют синтаксис:

ИЛИ(логическое_значение1;логическое_значение2; ...)

И(логическое_значение1; логическое_значение2; ...)

Аргументы функций И, ИЛИ могут быть логическими выражениями, массивами или ссылками на ячейки, содержащие логические значения.

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

Предположим, что нужно вывести сообщение «Светофор», если содержимое ячейки B4 или «красный», или «зеленый», или «желтый». Если же она содержит любую другую информацию, то необходимо вывести сообщение «Это не светофор!!!».

ЕСЛИ(ИЛИ(B4= "зеленый" ;B4= "красный" ;B4= "желтый" );"Светофор"; "Это не светофор!!!")

Предположим, что нужно вывести на экран содержимое ячейки B4 , если она содержит число строго между 1 и 100 (1<=B4<=100), и сообщение "Значение вне интервала" в противном случае.

ЕСЛИ(И(B4>=1; B4<=100); B4; «Значение вне интервала»)

Есть список дат, необходимо определить, является ли выходным днем введенная дата. Решения задачи на рис. 36

Формулу в ячейке В2 можно вводить с клавиатуры (необходимо помнить синтаксис используемых вложенных функций) или с помощью мастера функции. Рассмотрим, как вводить формулу с помощью мастера функций:

Переместим курсор в ячейку В2, щелкнем на кнопке вставка функции и в категории логические выберем функцию Если .

Щелкнем в поле Лог _ выражение функции Если, а затем щелкнем на стрелке вниз в строке формул и выберем Другие функции (см. рис. 37). Теперь в категории Логические можно выбрать функцию ИЛИ .

Щелкнем в поле Логическое_выражение_1 функции ИЛИ , а затем щелкнем на стрелке вниз в строке формул и выберем Другие функции. В категории Дата и время выберем функцию ДЕНЬНЕД и в поле Дата_в_числом_формате введем А2 , а в поле Тип введем 2 .

ИЛИ ИЛИ , для того чтобы закончить ввод условия в первое поле этой функции (набираем = 6).

Щелкнем в поле Логическое_выражение_2 функции ИЛИ и повторим действия пункта 3 и 4, чтобы ввести второе условие ДЕНЬНЕД(А2;2)=7.

В строке формул щелкнем мышкой внутри слова ЕСЛИ и таким образом, сможем вернуться в диалоговое окно функции ЕСЛИ , для того чтобы закончить ввод формулы (в поле Значение_если_истина вводим Выходной день , в поле Значение_если_ложь вводим пробел).

Информация о результатах первого модульного контроля и посещаемости студентами занятий выведена в таблице см. рис. 38. Необходимо вывести следующую информацию: Если средний бал студента меньше или равен 3,5 и он пропустил более 49 часов занятий по неуважительной причине, то необходимо вызвать родителей в деканат; если средний бал студента больше или 4,5 и он пропустил не более 10 часов занятий по неуважительной причине, то необходимо отправить родителям письмо благодарность. Решение задачи представлено на рис. 39.

Функции СЧЁТЕСЛИ и СУММЕСЛИ

Эти функции надо искать не в категории Логические , а в категориях Статистические и Математические , соответственно (или в полном алфавитном перечне).

СЧЁТЕСЛИ - подсчитывает количество ячеек в диапазоне, удовлетворяющих заданному условию, а СУММЕСЛИ - суммирует значения ячеек, удовлетворяющих заданному условию. Функция СУММЕСЛИ используется в тех случаях, когда необходимо суммировать не весь диапазон, а только ячейки, отвечающие некоторым условиям (критериям).

Синтаксис:

СЧЁТЕСЛИ (диапазон; критерий)

диапазон - диапазон, в котором нужно подсчитать ячейки.

критерий - критерий (условие) в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать.

Функция СЧЁТЕСЛИ работает следующим образом: вычисляется количество ячеек диапазона, значение которых удовлетворяют условию (критерию) .

В задаче, рассмотренной в примере 10 необходимо определить количество студентов, у которых средний балл >=4.5.

Тогда в ячейку С15 необходимо ввести формулу: =СЧЁТЕСЛИ(B11:B13;">=4,5"). Результат приведен на рис. 40

Синтаксис:

СУММЕСЛИ (диапазон; критерий; диапазон_суммирования)

диапазон - диапазон ячеек проверяемых на критерий (условие).

критерий - критерий (условие) в форме числа, выражения или текста, определяющего суммируемые ячейки.

диапазон_суммирования - фактические ячейки для суммирования.

Функция СУММЕСЛИ работает следующим образом: ячейки из «диапазон_суммирования » суммируются, только если соответствующие им ячейки в аргументе «диапазон » удовлетворяют условию (критерию) . В тех случаях, когда диапазон вычисляемых ячеек (где проверяется условие) и диапазон фактических ячеек для суммирования совпадают, аргумент «диапазон_суммирования» можно не указывать.

В таблице на рис. 41 приведены объемы партий товара, полученные магазином. Необходимо просуммировать только объемы тех партий, значения которых превышают 20. Решение на рис. 42

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

1) вычисление частного и остатка. Функция ЦЕЛОЕ – округляет число до ближайшего меньшего целого. Например ЦЕЛОЕ (5,7) – результат 5; ЦЕЛОЕ (-5,7) – результат 6. Функция ОСТАТ (число, делитель) – вычисляет остаток от деления нацело.

2) функции округления: ОКРУГЛ (число, число_разрядов)

А) если число_разрядов больше 0, то число округляется до указанного количества десятичных разрядов справа от десятичного разделителя;

Б) если число_разрядов равно 0, то число округляется до ближайшего целого;

В) если число_разрядов меньше 0, то число округляется до указанного количества десятичных разрядов слева от десятичного разделителя.

Например: Пусть в ячейке А4 записано число 143,3184, тогда формула ОКРУГЛ (А4,2) вернет число 143,32. Формула ОКРУГЛ (А4,0) вернет число 143. Формула ОКРУГЛ (А4,-1) вернет число 140.

3) Несколько иные задачи решают функции ОКРУГЛВНИЗ (число, число_разрядов) и ОКРУГЛВВЕРХ (число, число_разрядов). В соотвествии с их названиями они работают как функция ОКРУГЛ, но округляют всегда в большую или меньшую сторону.

4) Функция ОТБР (число, число_разрядов) отбрасывает дробную часть числа, если опустить второй аргумент. Если его указать, то функция работает как ОКРУГЛВНИЗ.

5) Функция СЛЧИС.

Возвращает равномерно распределенное случайное число, большее либо равное 0 и меньшее 1. Новое случайное число возвращается при каждом вычислении рабочего листа.

Синтаксис СЛЧИС ()

Примечание:

Чтобы получить случайное вещественное число между a и b, можно использовать следующую формулу:

СЛЧИС()*(b-a)+a

Если требуется использовать функцию СЛЧИС для генерации случайного числа, но изменение этого числа при каждом вычислении значения ячейки нежелательно, можно ввести в строку формул =СЛЧИС(), а затем нажать клавишу F9, чтобы заменить формулу на случайное число.

Логические функции:

Некоторые простые факты из математической логики.

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

Таблица И и ИЛИ

Таблица НЕ

Функция НЕ может иметь только один аргумент, а функция И и ИЛИ могут иметь два и более аргументов.

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

ЕСЛИ (лог_выражение, значение_если _истина, значение_если_ложь)

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

Равно

> Больше

< Меньше

>= Больше или равно

<= Меньше или равно

<> Не равно

Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0).

Функция ЕСЛИ

Функция ЕСЛИ имеет следующий синтаксис:

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

Формула =ЕСЛИ(А1>3;10;20) возвращает значение 10, если значение в ячейке А1 больше 3, а в противном случае - 20:

В качестве аргументов функции ЕСЛИ можно использовать другие функции. В функции ЕСЛИ можно использовать текстовые аргументы. Например: =ЕСЛИ(А1>=4;"Зачет сдал";"Зачет не сдал") .

Можно использовать текстовые аргументы в функции ЕСЛИ, чтобы при невыполнении условия она возвращала пустую строку вместо 0. Например: =ЕСЛИ(СУММ(А1:А3)=30;А10;"") .

Аргумент логическое_выражение функции ЕСЛИ может содержать текстовое значение. Например: =ЕСЛИ(А1="Динамо";10;290) . Эта формула возвращает значение 10, если ячейка А1 содержит строку "Динамо", и 290, если в ней находится любое другое значение. Совпадение между сравниваемыми текстовыми значениями должно быть точным, но без учета регистра.

Функции И, ИЛИ, НЕ

Функции И (AND), ИЛИ (OR), НЕ (NOT) - позволяют создавать сложные логические выражения. Эти функции работают в сочетании с простыми операторами сравнения. Функции И и ИЛИ могут иметь до 30 логических аргументов и имеют синтаксис:

И(логическое_значение1;логическое_значение2...)
=ИЛИ(логическое_значение1;логическое_значение2...)

Функция НЕ имеет только один аргумент и следующий синтаксис:

НЕ(логическое_значение)

Аргументы функций И, ИЛИ, НЕ могут быть логическими выражениями, массивами или ссылками на ячейки, содержащие логические значения.

Приведем пример. Пусть Excel возвращает текст "Прошел", если студент имеет средний балл более 4 (ячейка А2), и пропуск занятий меньше 3 (ячейка А3). Формула примет вид: =ЕСЛИ(И(А2>4;А3<3);"Прошел";"Не прошел")

Несмотря на то, что функция ИЛИ имеет те же аргументы, что и И, результаты получаются совершенно различными. Так, если в предыдущей формуле заменить функцию И на ИЛИ, то ученик будет проходить, если выполняется хотя бы одно из условий (средний балл более 4 или пропуски занятий менее 3). Таким образом, функция ИЛИ возвращает логическое значение ИСТИНА, если хотя бы одно из логических выражений истинно, а функция И возвращает логическое значение ИСТИНА, только если все логические выражения истинны.

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

Предположим, что студент сдал сессию в случае, если средний экзаменационный балл больше 3 (ячейка A 2) и число пропусков занятий меньше 10% (ячейка B 2). В противном случае студент сессию не сдал. Функция ЕСЛИ, реализующая этот алгоритм, будет выглядеть следующим образом: ЕСЛИ(И(А2>3;В2<=10%);”СДАЛ”;”НЕ СДАЛ”).

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

Вложенные функции ЕСЛИ

Иногда бывает очень трудно решить логическую задачу только с помощью операторов сравнения и функций И, ИЛИ, НЕ. В этих случаях можно использовать вложенные функции ЕСЛИ.

Предположим, что в ячейку А1 могут быть занесены три текстовые константы: «красный», «желтый», «зеленый». В зависимости от значения текстовой константы необходимо реализовать функцию ЕСЛИ, выдающую соответствующие рекомендации: «стойте», «ждите», «идите». Функция будет выглядеть следующим образом:
ЕСЛИ(А1=”красный”;”стойте”;ЕСЛИ(А1=”желтый”;”ждите”;”идите”))

Например, в следующей формуле используются три функции ЕСЛИ: =ЕСЛИ(А1=100;"Всегда";ЕСЛИ(И(А1>=80;А1<100);"Обычно";

ЕСЛИ(И(А1>=60;А1<80);"Иногда";"Никогда")))

Если значение в ячейке А1 является целым числом, формула читается следующим образом: "Если значение в ячейке А1 равно 100, возвратить строку "Всегда". В противном случае, если значение в ячейке А1 находится между 80 и 100, возвратить "Обычно". В противном случае, если значение в ячейке А1 находится между 60 и 80, возвратить строку "Иногда". И, если ни одно из этих условий не выполняется, возвратить строку "Никогда". Всего допускается до 7 уровней вложения функций ЕСЛИ.

Функции ИСТИНА и ЛОЖЬ

Функции ИСТИНА (TRUE) и ЛОЖЬ (FALSE) предоставляют альтернативный способ записи логических значений ИСТИНА и ЛОЖЬ. Эти функции не имеют аргументов и выглядят следующим образом:

ИСТИНА()
=ЛОЖЬ()

Например, ячейка А1 содержит логическое выражение. Тогда следующая функция возвратит значение "Проходите", если выражение в ячейке А1 имеет значение ИСТИНА:

ЕСЛИ(А1=ИСТИНА();"Проходите";"Стоп")

В противном случае формула возвратит "Стоп".

Вложенные функции

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

Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.

Допустимые типы вычисляемых значений Вложенная функция, используемая в качестве аргумента, должна вычислять соответствующий этому аргументу тип данных. Например, если аргумент должен быть логическим, т.е. иметь значение ИСТИНА либо ЛОЖЬ, то вложенная функция в результате вычислений тоже должна возвращать логическое значение либо ИСТИНА, либо ЛОЖЬ. В противном случае Microsoft Excel выдаст ошибку «#ЗНАЧ!».

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

В одну функцию можно вставить другую функцию. Допускается до 7-ми уровней вложения функций (в Office 2007 -- до 64). Конечно, функцию можно записать вручную (писать название вложенной функции, открывать скобки, ставить точки с запятой). Однако это противоречит самой идеологии мастера функций, который должен облегчать написание формул, защищать пользователя от ошибок и свести к минимуму ручную работу. Существует более удобный способ вложить функцию -- специальная кнопка на панели "Строка формул ":

После выбора нужной функции из выпадающего списка Excel вставит называние фукнции и круглые скобки в указанное место в формуле (в активное текстовое поле аргумента). После этого окно мастера функций для предыдущей функции (в этом примере "СУММ") сменится на окно для вставляемой функции ("СТЕПЕНЬ"), и ее название в формуле сделается жирным:

Переключение на другую функцию в формуле

Чтобы опять вернуться к окну для функции "СУММ", достаточно просто щелкнуть в строке формул на ее названии, и окно для степени сменится на окно для "СУММ". После этого функция "СУММ" в названии станет жирной, показывая, что в данный момент окно открыто именно для нее .

Использование имен

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

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

имя может начинаться с буквы, обратного слеша (\) или символа подчеркивания;
- в имени могут использоваться только буквы, цифры, обратный слеш и символ подчеркивания;
- нельзя использовать имена, которые трактуются как ссылки на ячейки и одиночные буквы R и С;
- для определения имени необходимо выделить ячейку или диапазон и использовать команду меню Вставка  Имя  Присвоить , а затем в поле «Имя» ввести выбранное пользователем имя;
- для вставки имени в формулу используется команда меню Вставка  Имя  Вставить и затем из списка выбирается соответствующее имя.

Присваивание имен

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

Выделите ячейку или диапазон ячеек.
В группе Определенные имена вкладки Формулы нажмите кнопку Присвоить имя .
В окне Создание имени в поле Имя введите имя ячейки или диапазона (рис. 6.18 ).

Рис. 6.18. Присвоение имени ячейке

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

Для удобства работы рекомендуется создавать имена короткие и хорошо запоминающиеся. Первый знак в имени должен быть буквой или знаком подчеркивания. Остальные знаки имени могут быть буквами, числами, точками и знаками подчеркивания. Пробелы не допускаются. Также не допускаются имена, которые имеют такой же вид, как и ссылки на ячейки, например Z$100 или R1C1 . В имени может быть больше одного слова. В качестве разделителей слов могут быть использованы знаки подчеркивания и точки, например: Год_2007 или Год.2007 . Имя может содержать до 255 знаков. Имя может состоять из строчных и прописных букв, но Excel их не различает.

При работе в Excel можно ссылаться на другие листы той же рабочей книги. Например, чтобы ввести в ячейку A 3 листа Лист1 ссылку на ячейку A 1 листа Лист2, следует выполнить следующую последовательность действий:

выделить ячейку A 3 на листе Лист1 и ввести знак равенства;
щелкнуть на ярлыке Лист2 внизу окна книги;
щелкнуть на ячейке A 1 и нажать клавишу < Enter > . После этого снова будет активизирован лист Лист1 и в ячейке A 3 появится формула =Лист2! A 1.

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

При каждом переходе на другой лист, его имя автоматически добавляется к ссылке на ячейку. Имя листа и адрес ячейки разделены служебным символом ! (восклицательный знак).

Например, в формуле в ячейке D2 в таблице на рис. 6.13 использована ячейка А4 листа Курс текущей книги.

Аналогичным образом можно ссылаться на ячейки, находящиеся в другой книге. Например, ссылка на ячейку A 1 листа Лист1 книги Книга2 имеет следующий вид: =[Книга2]Лист1!$ A $1. По умолчанию при создании ссылки на ячейки, принадлежащие другой рабочей книге, E x cel вставляет абсолютную ссылку.

2.12. Условное форматирование

Excel 2007 предоставляет еще более мощные и удобные инструменты условного форматирования.

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

Для применения условного форматирования служит кнопка "Условное форматирование" на панели "Стили" ленты "Главная" .

Чтобы лучше понять, как работает условное форматирование, выделите группу ячеек с уже введенными данными, нажмите кнопку "Условное форматирование" и посмотрите различные варианты форматирования.

По умолчанию программа автоматически определяет минимальное и максимальное значение в выделенном диапазоне и затем форматирует в равных процентных соотношениях.

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

Для удаления условного форматирования необходимо выделить нужный диапазон ячеек, нажать кнопку "Условное форматирование" и выбрать пункт "Удалить правила" .

Открытый урок информатики в 9 классе
Логические функции в электронных таблицах MS EXCEL

Цель урока: изучить логические функции MS Excel используемые при решении задач.

Образовательные задачи урока:

изучить функции И, ИЛИ, ЕСЛИ из категории Логические ;

научиться использовать их при решении задач.

Планируемые результаты:

Предметные: знать и применять функции И, ИЛИ, ЕСЛИ из категории Логические при решении задач.

Метапредметные:

Регулятивные: самостоятельно формулируют цели, задачи, проблему урока, планируют деятельность.

Познавательные: анализировать, устанавливать причинно-следственные связи, проводить комплексный поиск информации в источниках разного типа для решения задач, сравнивать.

Коммуникативные: осуществлять сотрудничество с педагогом, одноклассниками, высказывают собственную точку зрения; строят понятные речевые высказывания.

Личностные: прививать интерес к предмету, теме, применять полученные знания в различных областях учебной деятельности.

Научатся: пользоваться встроенными функциями, применять логические функции.

Тип урока : комбинированный

Оборудование: мультимедиа-проектор, ноутбуки; программное обеспечение: MS Excel .

Формы работы учащихся: фронтальная, групповая, индивидуальная

Используемые технологии:

проблемное обучение;

развитие критического мышления;

системно-деятельностный и технологический подход к обучению;

информационные технологии

модель «Перевернутый класс»

ХОД УРОКА

1. Организационный момент – менее 1 мин. Приветствует учащихся. Проводит проверку готовности к уроку.

Здравствуйте, очень рада вас видеть снова!

2. Актуализация знаний – 10 мин. (проверка д/з – модель «перевернутый класс»).

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

Если довольны (справились), удовлетворены (частично), разочарованы (не поняли, не смогли) тем, как прошел наш урок, то отметьте ваше отношение к элементам урока в соответствующей клетке лист (Приложение 1 ).

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

Фронтальный опрос с целью актуализации технологических знаний в среде http://learningapps.org/(LearningApps.org является приложением Web 2.0 для поддержки обучения и процесса преподавания с помощью интерактивных модулей ) .

1. Вам предлагается задание в среде learningapps.org: (индивидуальная работа за компьютерами). Приступайте!

На это задание – 1 минута.

http://learningapps.org/watch?v=poc2tgjqa16

А теперь давайте проверим задание вместе (вызывается ученик к доске)

Функцией и Назначением .

Математические или Статистические) (на листках распечатать или на интерактивной доске). Приложение 2 .

	Назначение

	Вычисление корня квадратного

СУММ, КОРЕНЬ, SIN - Математические

МИН, СРЗНЧ, - Статистические

Сравним ваши ответы с правильным результатом (на экране ). Приложение 2. Ответы

3. А теперь посмотрите, пожалуйста, на экран. Перед Вами представлена задача ( Демоверсия 2013 год, задание № 19 ).

Обсуждение со всем классом.

1. Как вы думаете, как выполнить решение задачи для данных в 1000 строк таблицы? (автокопирование формулы)

2. А давайте подумаем, какую формулу можно задать, если необходимо вычислить суммарное расстояние перевозки с 1 по 3 октября ? Ответ необходимо записать в ячейку H2 таблицы.

=СУММ(D2:D118)

=СРЗНАЧЕСЛИ(B2:B371;"Липки";F2:F371)

Молодцы! Справились!

Слайд 1. Таблица

3. Мотивация (постановка проблемы) – 10-12 мин.

Дома вы просмотрели видеоролик (можно презентацию с подробными комментариями, что заменяет объяснение учителя в видеоролике ).

Исходя из данного видеоролика, сформулируйте тему урока ( ), озвучьте цель нашего урока (знать и в MS Excel ).

Слайд 2. Таблица

ЧТО МЫ УЖЕ ЗНАЕМ	ЧТО УЗНАЕМ СЕГОДНЯ НА УРОКЕ?


	Применять логические функции при решении задач
	Логические функции

Приложение 3. Проблема: Перед Вами на партах лежат карточка с заданием. Сможем мы решить данную проблему?

Работа в группах.

1 группа: Решить задачу на применение функций И, ИЛИ (на листках).

__________________________________________________________________________________________________________________________________________________________

Excel Excel → Задание_группа 1).

После обсуждения приходим к выводу, что в ячейку D4 следует ввести формулу=И(B4-C4=C4*0,07) . Иными словами, мы использовали логическую функцию с одним условием - обеспечение продаж больше плана на 7%.

Скажите, в данной задаче, можно ли использовать с той же целью функцию ИЛИ?

Можно, с одним условием, чтобы получит ответы “истина” или “ложь” в ячейке.

2 группа: Решить задачу на применение функции ЕСЛИ (на листках).

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

Поле электронной таблицы выглядит так:

2.Сформулируйте вывод (анализ используемых логических функций):

______________________________________________________________________________________________________________________________________________________________________

3.Проверьте данную задачу, решив ее в Excel .(Файл находиться на Рабочем столе → папка Excel → Задание_группа 2).

Решение: “Школа танцев” введем формулу в ячейку D6: =ЕСЛИ(И(И(B6=168;B6

Готовые решения задач показать (высветить на экран). Обсуждение задач. Удалось решить проблему, поставленную перед Вами?

Молодцы! А теперь давайт е проверим данную задачу, решив ее в Excel .

4. Первичное закрепление материала (практическая часть) – 15 -17 мин.

Работа в группах на компьютере. Ролевая игра.

Учащиеся делятся на три группы по 5-6 человек. Каждая группа будет представлять одну из организаций: «Связьтелеком », «СуперБух », «Шанс ».

Группам раздаются таблички с названиями организаций, карточки с заданиями (запросами клиента), выделяется по одному компьютеру (Приложения № 4 ).

Организация вводят информацию в подготовленные таблицы и подготавливают результат (отчет руководителю; отчет должен быть красиво и эффективно оформлен; в роли руководителя выступает учитель).

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

Если работа над задачей вызывает затруднения, группы могут воспользоваться помощью учителя.

Ролевая игра проводится в течение 10 мин. Затем в течение 5 мин группы рассказывают о своей работе – представляют свою электронную таблицу, описывают трудности и успехи в работе, осуществляют самооценку.

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

Задача « Подсчет стоимости телефонных переговоров»

Пять абонентов звонят из города А в город Б. Если телефонный междугородний звонок был произведен в выходные дни (суббота, воскресенье), или в праздничные дни, или в будние дни с 20 часов вечера до 8 часов утра, то он рассчитывается по льготному тарифу со скидкой 50%, в другое время льгот нет. Подсчитать, сколько каждый из пяти абонентов должен заплатить за переговоры.

Решение

Используем заранее подготовленную таблицу стоимости телефонных переговоров.

Если звонок производится по льготному тарифу, то должно выполняться условие: День недели = “суббота” ИЛИ День недели = “воскресенье” ИЛИ Праздник “Да” ИЛИ время начала переговоров =20 ИЛИ Время начала переговоров G3 заносим формулу: ЕСЛИ (ИЛИ (С3= “Суббота”; С3= “Воскресенье”; D3= “Да”; Е3=20; Е3

Организация «СуперБух » помогает магазину скидки

Задача “Скидка”. скидками скидки: покупатель, стоимость покупки, скидка, стоимость покупки с учётом скидки скидкой, стоимость которых превышает k рублей.

Решение:

Готовим таблицу в виде:

В ячейки A1:B7 заносим соответствующие данные.

В ячейку C2 заносим формулу =B2*0,1 (так как скидка на покупку 10%, то изначальную стоимость покупки необходимо умножить на 0,1).

В ячейку D2 заносим формулу =B2-C2 (так как стоимость мы вычисляем покупки с учётом скидки )

В ячейку E2 заносим формулу =ЕСЛИ(B2=$B$9;D2;B2). В этой формуле необходимо обратить внимание на абсолютную ссылку на ячейку B9.

При занесении формулы в ячейку E2 мы встретились с ситуацией, когда при вставке встроенной функции ЕСЛИ требуется в качестве значения логического выражения сделать ссылку на ячейку.

Организация «Шанс » помогает компаниям

Задача «Задолженность»

1. Найти работников, у которых одновременно имеются задолженности по потребительскому кредиту и кредиту на жилищное строительство, и удержать от начисленной им суммы 20%.

Решение: Для нашего примера логическая функция будет иметь следующий вид:

=ЕСЛИ(И (C30;D30); ВЗ*0,2; "-")

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

2. Найти работников, у которых имеются одновременно задолженности по обоим видам кредита, и удержать от начисленной им суммы 20% в счет погашения кредитов. С остальных работников, имеющих задолженность по какому-либо одному виду кредита, удержать 10% от начисленной им суммы. Работникам, не имеющим задолженность по кредиту, проставить в графе "Удержано" - "б/к".

Решение:

В нашем примере логическая функция будет иметь следующий вид:

=ЕСЛИ (И(С30;D30);В3*0,2;ЕСЛИ (И (С3=0;D3=0);"б/к"; ВЗ*0,1))

Данная логическая функция означает следующее: если одновременно задолженности по потребительскому кредиту и кредиту на жилищное строительство больше нуля, то необходимо удержать 20% с начисленной суммы, если обе задолженности одновременно

равны нулю, то необходимо вывести «б/к», в противном случае необходимо удержать 10% от начисленной суммы.

Завершение практической работы.

5. Физкультминутка (1 мин) для глаз и спины. Проводит учащийся! (Можно сделать как опрос по теме: прием ТРИЗ «Да-НЕТКА», если согласны – ДА встали (НЕТ – сели) или любое другое движение, можно движения менять на каждом вопросе)

Не вставая из-за рабочего стола, примите удобное положение – спина прямая, глаза открыты, взгляд устремлен прямо. Выполнять упражнения необходимо легко без напряжения.

1. Изменение фокусного расстояния: смотреть на кончик носа, затем вдаль. Смотреть на кончик пальца или карандаша, удерживаемого на расстоянии 30 см от глаз, затем вдаль.

Повторить несколько раз.

2. Сжать веки, затем моргнуть несколько раз.

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

4. Круговые движения глаз – от 1 до 10 кругов влево и вправо. Сначала быстро, потом как можно медленнее.

5. Закончить гимнастику необходимо массажем век, мягко поглаживая их указательным и средним пальцем в направлении от носа к вискам, а затем потереть ладони, легко. Без усилий, прикрыть ими закрытые глаза, чтобы полностью загородить их от света (на 1 минуту). Представить погружение в полную темноту.

Спасибо!

6. Проверка усвоения нового материала с помощью MyTest (индивидуально) – 2 мин.

Вам предлагается ответить на вопросы теста, система проанализирует ответы и выдаст результат. По окончании вы занесете результат в Лист самооценки.

Тест находиться на Рабочем столе → Тестирование→ Логические функции в MS Excel .

а) В школу танцев принимаются девушки и юноши, имеющие рост не ниже 168 см и не выше 178 см.

Варианты ответов:

1. ИЛИ (Рост168; Рост

2. И (Рост168)

3. И (Рост =168)

б) В школу танцев принимаются девушки и юноши, имеющие рост не ниже 168 см и не выше 178 см. Вес поступающих в школу танцев должен соотноситься с ростом по формуле: значение веса

Варианты ответов:

1. И(ИЛИ (Рост168; РостРост - 115))

2. И(И (Рост=168; Рост Рост - 115))

3. И(ИЛИ (Рост=168; РостРост - 115))

в) В диапазоне от А2 до А32 находятся значения дневных температур за декабрь месяц. Считается аномалией, если среднемесячная температура выходит за рамки от -30 до +10. Является ли температура аномальной.

Варианты ответов:

1. И (СРЗНАЧ(А2:А32)=10; СРЗНАЧ(А2:А32)

2. И (СРЗНАЧ(А2:А32)=-30)

3. ИЛИ (СРЗНАЧ(А2:А32)=10; СРЗНАЧ(А2:А32)

г) Какое логическое выражение, соответствует двойному неравенству 0

1. И(А10; A1

д) Какое логическое выражение, соответствует условию “дети до 12 лет или старики от 70 лет”.

1. ИЛИ(дети=70)

2. ЕСЛИ (ИЛИ(дети=70)

3. И(дети=70)

е) Для заданного значения X нужно вычислить значение Y по одной из формул: если x5, то y:=x-8, иначе y:=x+3. Как будет выглядеть формула:

Варианты ответов:

1. =ЕСЛИ(В15; B 1-8; B 1+3)

2. =ЕСЛИ(И(В15;B 1-8;B 1+3))

3. =ЕСЛИ(ИЛИ(В15;B 1-8;B 1+3))

7. Подведение итогов урока – 5 мин.

Какие цели в начале урока мы перед собой поставили?

Что нового узнали?

Чему научились?

После обсуждения н а экране:

ЧТО МЫ УЖЕ ЗНАЕМ	ЧТО УЗНАЕМ СЕГОДНЯ НА УРОКЕ?	ЧТО УМЕЕМ, ЧЕМУ НАУЧИЛИСЬ
Содержимым ячейки ЭТ может быть текст, число и формула	Какие логические функции есть в электронных таблицах (MSExcel )?	Логические функции И, ИЛИ реализуют соответствующие логические операции.Логическая функция ЕСЛИ позволяет сделать выбор действия в зависимости от условия (логического выражения).
	Правила ввода логических функций в электронных таблицах (MSExcel )?
	Применять логические функции при решении задач
Математические, статистические функции	Логические функции

8. Домашнее задание 1 мин .:

Наш урок подошел к концу. Вы хорошо поработали в группах, успешно выполнили задания индивидуально, я довольна вами. Спасибо!

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

На партах у вас лежат листы Приложение №5 - это домашнее задание, пользуясь опорным конспектом, знаниями, полученными на уроке, решите задачу, используя логические функции.

Приложение №5

Задача 1.

Задача 2.

ЧТО МЫ УЖЕ ЗНАЕМ	ЧТО УЗНАЕМ СЕГОДНЯ НА УРОКЕ?	ЧТО УМЕЕМ, ЧЕМУ НАУЧИЛИСЬ
Содержимым ячейки ЭТ может быть текст, число и формула	Какие логические функции есть в электронных таблицах (MSExcel )?
	Правила ввода логических функций в электронных таблицах (MSExcel )?	Правила ввода логических функций в ЭТ
	Применять логические функции при решении задач	Использовать логические функции MSExcel при решении задач
Математические, статистические функции	Логические функции

И завершить урок мне бы хотелось французского ученого Густава Гийома: Эпиграф на слайде презентации: «Дорогу осилит идущий, а информатику – мыслящий».

До свидания!

«Приложение 1. Лист самооценки»

Лист самооценки ученика(цы) 9 класса ФИ__________________________________

1. Критерии

Тематический контроль знаний.

БАЛЛЫ

2. Критерии.

Определение темы, цели, проблемы урока

БАЛЛЫ

3.Критерии

Работа в группах.

Работа с опорным конспектом, (смысловой просмотр видеоролика, поиск информации)

БАЛЛЫ

4. Критерии

Первичное усвоение предметных знаний.

Работа в группах на компьютере Ролевая игра.

БАЛЛЫ

Все (4 части)

Частично (3 части)

Не смог (2 части)

Сам

помощью учителя

Не смог

Все сам

Частично

Не смог

Все

Частично

Не смог

1. Задание: свести правильные части так, чтобы они "склеились" скотчем

1. Я определил тему урока.

1.Я вписал формулу

1.Я правильно выполнил задание (практическую работу на компьютере)

2. Установил(а) соответствие, вписал(а) категорию функции

Все (5)

Частично(4-3)

Не смог (2-0)

2. Я сформулировал цель урока

2.Я сформулировал вывод

Все сам

Частично

Не смог

2. Удалось справиться с назначенной ролью

Да

Частично

Нет

3. Решил задачу

Все (2 задания)

Частично (1 задание)

Не смог (ни одного задания)

3. Я понял задачу (проблему), поставленную передо мной

3. Я проверил задачу, решив ее в MS Excel

Да, полностью

Частично

Не смог

3. Описать трудности и успехи в работе

Сам

Со всеми

Не смог

5.Критерии

Проверка усвоения нового материала с помощью MyTest (индивидуально)

БАЛЛЫ

Моя сумма баллов: ________ Отметка: ________

Критерии оценивания:

«5» - 26 – 20 баллов

«4»- 19– 15 баллов

«3» - 14– 10 баллов

Менее 10 баллов. Не расстраивайтесь! Надо еще позаниматься.

Да, полностью

Частично

Не смог

1.Я правильно решил тест (удалось справиться)

Просмотр содержимого документа
«Приложение 2. Задание на соответствие»

Приложение № 2

1.Установите соответствие между Функцией и Назначением .

Функция	Назначение
	Вычисление суммы значений в заданном диапазоне
	Вычисление корня квадратного
	Вычисление тригонометрической функции
	Вычисление минимального значения в заданном диапазоне
	Вычисляет среднее арифметическое значение диапазона аргументов

2. Впишите категорию функции (Математические или Статистические ).

Просмотр содержимого документа
«Приложение 3.Проблема.Задание на листках для групп»

Приложение №3

1 группа:

РЕШИТЬ ЗАДАЧУ НА ПРИМЕНЕНИЕ ФУНКЦИЙ И, ИЛИ

Перед Вами стоит задача о выплате премий за увеличение числа продаж сотрудникам фирмы более чем на 7%.

Поле электронной таблицы выглядит так:

1.Впишите формулу, чтобы в столбце D было выведено ИСТИНА напротив сотрудника, которому следует начислить премию, и ЛОЖЬ напротив сотрудника, которому не следует начислить премию?

2.Сформулируйте вывод (анализ используемых логических функций):

____________________________________________________________________________________________________________________________________________________________________________________

3.Проверьте данную задачу, решив ее в Excel .

(Файл находиться на Рабочем столе → папка Excel → Задание_ группа 1).

2 группа:

РЕШИТЬ ЗАДАЧУ НА ПРИМЕНЕНИЕ ФУНКЦИИ ЕСЛИ

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

Поле электронной таблицы выглядит так:

Сформулируйте вывод (анализ используемых логических функций):

_______________________________________________________________________________________________________________________________________________________________________________________

Проверьте данную задачу, решив ее в Excel .

(Файл находиться на Рабочем столе → папка Excel → Задание_ группа 2).

Просмотр содержимого документа
«Приложение 4.Ролевая игра»

Приложение №4

Организация «СВЯЗЬТЕЛЕКОМ » помогает абоненту рассчитать,

сколько каждый должен заплатить

за телефонные переговоры.

Задача «ПОДСЧЕТ СТОИМОСТИ ТЕЛЕФОННЫХ ПЕРЕГОВОРОВ»

Пять абонентов звонят из города А в город Б. Если телефонный междугородний звонок был произведен в выходные дни (суббота, воскресенье), или в праздничные дни, или в будние дни с 20 часов вечера до 8 часов утра, то он рассчитывается по льготному тарифу со скидкой 50%, в другое время льгот нет. Подсчитать, сколько каждый из пяти абонентов должен заплатить за переговоры.

__________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________

Организация «СуперБух »

помогает магазину составить ведомость, учитывающую скидки: покупатель, стоимость покупки, скидка, стоимость покупки с учётом скидки . И показать, кто из покупателей сделал покупки со скидкой, стоимость которых превышает k рублей.

Задача “СКИДКА”

Покупатели магазина пользуются 10% скидками , если стоимость покупки превышает k рублей. Составить ведомость, учитывающую скидки: покупатель, стоимость покупки, скидка, стоимость покупки с учётом скидки . Составить таблицу и показать, кто из покупателей сделал покупки со скидкой, стоимость которых превышает k рублей.

Организация вводит информацию в подготовленные таблицы и подготавливают результат (отчет руководителю; отчет должен быть красиво и эффективно оформлен (либо в MS Excel , либо в MS Word ); в роли руководителя выступает учитель).

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

Время выполнения - 10 мин. Затем в течение 5 мин группы рассказывают о своей работе – представляют свою электронную таблицу, описывают трудности и успехи в работе.

ОПИСАНИЕ ТРУДНОСТЕЙ И УСПЕХОВ В РАБОТЕ

Организация «ШАНС » помогает компаниям найти работников, у которых имеются задолженности по потребительскому кредиту и (или) кредиту на жилищное строительство, удержать % от начисленной им суммы.

Задача «ЗАДОЛЖЕННОСТЬ»

2. Найти работников, у которых имеются одновременно задолженности по обоим видам кредита, и удержать от начисленной им суммы 20% в счет погашения кредитов (рис. 3). С остальных работников, имеющих задолженность по какому-либо одному виду кредита, удержать 10% от начисленной им суммы. Работникам, не имеющим задолженность по кредиту, проставить в графе "Удержано" - "б/к ".

ОПИСАНИЕ ТРУДНОСТЕЙ И УСПЕХОВ В РАБОТЕ

Просмотр содержимого документа
«Приложение 5. Домашнее задание»

Приложение №5

ЗАДАЧА 1.

Компания по снабжению электроэнергией взимает плату с клиентов по тарифу: k рублей за 1 КВт/ч и m рублей за каждый КВт/ч сверх нормы, которая составляет 50 Квт/ч. Услугами компании пользуются 10 клиентов. Подсчитать плату для каждого клиента.

ЗАДАЧА 2.

10 спортсменов-многоборцев принимают участие в соревнованиях по 5 видам спорта. По каждому виду спорта спортсмен набирает определенное количество очков. Спортсмену присваивается звание мастер, если он набрал в сумме не менее k очков. Сколько спортсменов получило звание мастера?

«Логические функции в Excel»

ЛОГИЧЕСКИЕ ФУНКЦИИ В EXCEL

ПРИМЕРЫ ИХ ПРИМЕНЕНИЯ

Простая функция ЕСЛИ

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

Общий формат функции:

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

Логическое выражение – это условие.

Если условие выполняется, то в силу вступает средняя часть выражения, то есть « значение_если_истина ».

Если условие не выполняется, то в силу вступает « значение_если_ложь ».

Простая функция ЕСЛИ

Работа логической функции « ЕСЛИ » иллюстрируется следующей

блок-схемой:

ЕСЛИ (условие; Оператор1; Оператор2)

2;”Зачет”;”Нет зачета”) В2 – адрес ячейки, где находится оценка первого ученика" width="640"

Задание № 1:

Составим электронную таблицу в следующем виде:

В столбец « Примечание 1 » следует ввести формулу так, чтобы выводилось сообщение « Зачет », если оценка больше «2» и сообщение « Нет зачета » в противном случае.

Ответ:

Если (B22;”Зачет”;”Нет зачета”)

Сложные условия

При решении многих задач в качестве логического выражения могут использоваться сложные условия.

Сложные условия состоят из простых и соединяются между собой логической функцией « И » или логической функцией « ИЛИ ».

Логическая функция ИЛИ

Общий формат функции:

ИЛИ (Логическое_значение_1;логическое_значение_2;......)

истина », если выполняется хотя бы одно логическое значение.

Задание № 2:

В столбец « Примечание 2 Зачет », если оценка «3», «4» или «5» и сообщение « Нет зачета » в противном случае.

Ответ:

Если (ИЛИ(B2=3;В2=4;В2=5);”Зачет”;”Нет зачета”)

В2 – адрес ячейки, где находится оценка первого ученика

Логическая функция И

Общий формат функции:

И (Логическое_значение_1;логическое_значение_2;......)

Это логическое выражение (условие) принимает значение « истина », если все заданные логические значения выполняются одновременно. Если не выполняется хотя бы одно условие, то выдается значение « ложь ».

2;В2 В2 – адрес ячейки, где находится оценка первого ученика" width="640"

Задание № 3:

В столбец « Примечание 3 » ввести формулу так, чтобы выводилось сообщение « Зачет », если оценка больше «2» но меньше или равна «5», и сообщение « Нет зачета » в противном случае.

Ответ:

Если(И(B22;В2

В2 – адрес ячейки, где находится оценка первого ученика

Вложенные функции «ЕСЛИ»

Условные операторы могут иметь вложенную конструкцию, когда в ветви « Да » (смотри блок-схему рис.1) вместо оператора 1 или в ветви « Нет » вместо оператора 2 должно проверяться ещё одно условие. В этом случае используются вложенные операторы .

Рассмотрим случай, когда вместо оператора 1 необходимо поставить еще одно условие. При этом блок-схема имеет вид:

Логическая функция имеет вид:

ЕСЛИ(условие1; Если (условие2; оператор1; оператор2);оператор3)

Рассмотрим случай, когда вместо оператора 2 необходимо поставить еще одно условие. При этом блок-схема имеет вид:

Логическая функция имеет вид:

ЕСЛИ(условие1; оператор1; ЕСЛИ (условие2; оператор2; оператор3))

2;В2" width="640"

Задание № 4:

В столбец « Примечание 4 » ввести формулу так, чтобы выдавалось сообщение « Зачет », если оценка «3», «4» или «5», сообщение « Нет зачета Ошибка » в противном случае.

Ответ:

Если(ИЛИ(B2=3;В2=4;В2=5);”Зачет”;Если(ИЛИ(В2=1;В2=2);”Нет зачета”;”Ошибка”))

В2 – адрес ячейки, где находится оценка первого ученика

Возможен другой вариант решения:

Ответ:

Если(И(B22;В2

Задание № 5:

В столбец « Примечание 5 » ввести формулу так, чтобы выдавалось сообщение « Отлично », если оценка «5», сообщение « Хорошо », если оценка «4», сообщение « Удовлетворительно », если оценка 3, сообщение « Неудовлетворительно » или « Плохо », если оценка «2» или «1», и сообщение « Ошибка » в противном случае.

Ответ:

Если(B2=3;”Удовлетворительно”;Если(В2=4;”Хорошо”;Если(В2=5;”Отлично”;Если(ИЛИ(В2=1;В2=2);”Неудовлетворительно”;”Ошибка”))))

В2 – адрес ячейки, где находится оценка первого ученика

Просмотр содержимого презентации
«Таблица»

MS EXCEL

ЧТО МЫ УЖЕ ЗНАЕМ

Содержимым ячейки ЭТ может быть текст, число и формула

Математические, статистические функции

MS EXCEL

ЧТО МЫ УЖЕ ЗНАЕМ

ЧТО УЗНАЕМ СЕГОДНЯ НА УРОКЕ?

Содержимым ячейки ЭТ может быть текст, число и формула

Применять логические функции при решении задач

Математические, статистические функции

Логические функции

MS EXCEL

Содержимым ячейки ЭТ может быть текст, число и формула

ЧТО МЫ УЖЕ ЗНАЕМ

ЧТО УЗНАЕМ СЕГОДНЯ НА УРОКЕ?

Какие логические функции есть в электронных таблицах (MS Excel)?

ЧТО УМЕЕМ, ЧЕМУ НАУЧИЛИСЬ

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

Правила ввода логических функций в электронных таблицах (MS Excel)?

Правила ввода логических функций в ЭТ

Применять логические функции при решении задач

Математические, статистические функции

Логические функции