Условная функция и логические выражения в электронной таблице Excel

Для создания проблемной ситуации предлагаю ученикам следующую задачу.

Ученики сдают зачет, который предусматривает систему оценивания “зачет” и “незачет”. “Зачет” ставится, если из 10 вопросов ученики верно ответили больше чем на половину вопросов, т.е. на 6 и более, а в противном случае ставится “незачет”. Надо автоматизировать процесс выставления зачета.

Давайте подумаем, какую информацию надо занести в электронные таблицы, какие задать формулы?

После обсуждения с учениками останавливаемся на таблице следующего вида:

В ячейки А2:К6 заносим данные (1 — верный ответ, 0 – неверный), а в ячейки L2:М6 заносим формулы. В ячейке L2 будет формула =СУММ (В2:К2). Эту формулу надо скопировать в другие ячейки столбца L (по количеству учеников).

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

2. Изучение нового материала (ведется с использованием презентации)

Условная функция, записанная в ячейку таблицы, выполняется так: если условие истинно, то значение данной ячейки определяется значением , в противном случае – значением .

Логические выражения строятся с помощью операций отношения (, =, ) и логических операций (логическое И, логическое ИЛИ, логическое отрицание НЕ). Результатом вычисления логического выражения являются логические величины ИСТИНА или ЛОЖЬ.

Слайд 3. Особенности логических операций в электронных таблицах.

Сначала записывается имя логической операции (И, ИЛИ, НЕ), а затем в круглых скобках перечисляются операнды. Например, ЕСЛИ (И (А5=18); “ДА”; “НЕТ”)

Слайд 4.Есть ли действительные корни у квадратного уравнения

Даны коэффициенты а, b, c квадратного уравнения (а?0). Определить, имеет ли это уравнение действительные корни

Решение. Исходной информацией являются коэффициенты а, b, c. Готовим таблицу в следующем виде:

В ячейки А2:С2 заносим числовые значения коэффициентов.

В ячейку D2 заносим формулу для вычисления дискриминанта квадратного уравнения =В^2-4*A2*C2. В ячейку Е2 заносим формулу =ЕСЛИ (D2>=0; “ДА”; “НЕТ”). Таким образом, если условие D2>=0 (то есть если дискриминант неотрицателен), то в ячейке Е2 будет отражаться текст “ДА”, противном случае – “НЕТ”.

Приведенный пример (слайд 4) разбирает учитель. Следующий пример (слайд 5) ученики должны объяснить самостоятельно.

Даны коэффициенты k, b уравнения прямой у= kх+ b и даны координаты (х,у) пяти точек на плоскости. Для каждой точки определить, принадлежит ли она данной прямой или нет.

Решение. Если точка принадлежит прямой, то при подстановке координат точки в уравнение этой прямой получается верное равенство. Готовим таблицу в следующем виде:

Следующий, более сложный, пример (слайд 6) учитель разбирает подробно.

Даны координаты вершин прямоугольника (х₁,у₁), (х₁,у₂), (х₂,у₂), (х₂,у₁), расположенного в первом квадранте, со сторонами, параллельными осям координат (0 < х₁ < х₂, 0 < у₁ < у₂), и даны координаты точки (х,у). Определить, лежит ли эта точка внутри прямоугольника (включая его границы) или нет.

Решение. Если точка с координатами (х,у) лежит внутри прямоугольника или на его границе, то должны выполняться следующие условия:

В ячейки А2: F2 заносим соответствующие данные. В ячейку G2 заносим формулу: =ЕСЛИ (И (Е2>=$A$2; E2=$C$2; F2<=$D$2); “Да”; “Нет”).

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

Теперь вернемся к ситуации, поставленной в начале урока.

=ЕСЛИ (L2>=6; “зачет”; “незачет”). Далее надо скопировать эту формулу в ячейки столбца М по количеству учеников.

Далее предлагается задача для самостоятельного решения учащимися (фронтальная работа)

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

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

А	В	С	D	Е	F	G
1	Полный тариф (руб. за мин.)	6	Льготный тариф (руб. за мин.)	3
2	Фамилия	Дата переговоров	День недели	Праздник	Время начала переговоров	Длительность	Оплата
3	Зверева	1/2005	Среда	Да	12	7
4	Сомов	12/2007	Четверг	Нет	21	6
5	Котова	13/2007	Пятница	Нет	4	10
6	Ильин	17/2007	Вторник	Нет	11	20
7	Яшков	14/2007	Суббота	Нет	18	15
8	Львов	15/2007	Воскресенье	Нет	22	3

Если звонок производится по льготному тарифу, то должно выполняться условие: День недели = “суббота” ИЛИ День недели = “воскресенье” ИЛИ Праздник “Да” ИЛИ время начала переговоров >=20 ИЛИ Время начала переговоров G3 заносим формулу: ЕСЛИ (ИЛИ (С3= “Суббота”; С3= “Воскресенье”; D3= “Да”; Е3>=20; Е3<=8);$D$1*F3; $B$1*F3).

Ссылка на ячейки D1 и В1 абсолютная, так как при копировании формул имена этих ячеек не должны меняться.

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

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

А	В	С	D	Е	F	G
1	Полный тариф (руб. за мин.)	6	Льготный тариф (руб. за мин.)	3
2	Фамилия	Дата переговоров	День недели	Праздник	Время начала переговоров	Длительность	Оплата
3	Зверева	1/2005	Среда	Да	12	7	21
4	Сомов	12/2007	Четверг	Нет	21	6	18
5	Котова	13/2007	Пятница	Нет	4	10	30
6	Ильин	17/2007	Вторник	Нет	11	20	120
7	Яшков	14/2007	Суббота	Нет	18	15	45
8	Львов	15/2007	Воскресенье	Нет	22	3	9

Задача 1 (уровень 1). Стоимость абонемента на посещение бассейна в воскресенье на 10 % выше, чем во все остальные дни. Определить стоимость проданных 10 абонементов, если цена абонемента в будний день составляет 50 рублей.

Задача 2 (уровень 2). Покупатель магазина получает скидку 3%, если у него есть дисконтная карта или если общая стоимость его покупки превышает 5 тысяч рублей. Определить, сколько заплатили покупатели за свои покупки с учетом дисконта (если он есть).

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

Задача 1 (уровень 1). Компания по снабжению электроэнергией взимает плату с клиентов по тарифу: 0,6 рублей за 1 кВт/ч за первые 200 кВт; 0,9 рублей за 1 кВт/ч, если потребление свыше 200 кВт. Услугами компании пользуются 10 клиентов. Подсчитать плату для каждого клиента.

Задача 2 (уровень 2). Компания по снабжению электроэнергией взимает плату с клиентов по тарифу: 0,6 рублей за 1 кВт/ч за первые 200 кВт; 0,9 рублей за 1 кВт/ч, если потребление свыше 200 кВт, но не превышает 500 кВт; 1,2 рубля за 1 кВт/ч, если потребление свыше 500 кВт. Услугами компании пользуются 10 клиентов. Подсчитать плату для каждого клиента.

Задача 3 (уровень 3). К задачам предыдущего уровня сложности добавляются следующие задания: подсчитать суммарную плату всех клиентов, определить, сколько клиентов потребляет свыше 500 квт.

У нас есть таблица с наименованиями спортивной обуви. Представим, что скоро у нас будет распродажа, и всей женской обуви нужно проставить скидку 25%. В одном из столбцов в таблице как раз прописан пол для каждого наименования.

Обработка текста в Excel. Регулярные выражения.

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

Ответы к тесту по информатике 524 вопроса (Часть 4)

309. Общая эффективность автоматизированного решения экономических задач находится в прямой зависимости от (укажите все правильные):

+а) снижения затрат на обработку информации (прямая эффективность)

б) снижения затрат промышленных предприятий на производство

+д) достигнутого повышения уровня информационного обслуживания (косвенная эффективность)

310. В снижении трудовых и стоимостных затрат на обработку экономической информации выражается __________ эффективность

311. Качественные изменения, происходящие в результате применения средств вычислительной техники, характеризует ___________ эффективность

312. Для расчета ожидаемой косвенной эффективности рекомендуется применять:

313. В результате сравнения затрат на обработку информации нескольких вариантов проектных решений определяются показатели _________ экономической эффективности

314. Абсолютным показателем экономической эффективности разрабатываемого проекта является , где (укажите все правильные):

+а) — величина снижения затрат на обработку информации

+б) — стоимость обработки информации при базовом варианте

+г) — стоимость обработки информации при проектируемом варианте

315. Относительным показателем экономической эффективности проекта является или , где (укажите все правильные):

+б) — величина снижения затрат на обработку информации

+д) — стоимость обработки информации при базовом варианте

316. Относительным показателем экономической эффективности проекта является , где (укажите все правильные):

+г) — стоимость обработки информации при базовом варианте

+д) — стоимость обработки информации при проектируемом варианте

317. Внедрение проекта предполагает дополнительные капитальные затраты , где (укажите все правильные):

+а) — капитальные затраты проектируемой системы обработки информации

+б) — коэффициент неравномерности нагрузки на предприятиях связи

в) — затраты существующей системы обработки информации

318. Эффективность капитальных затрат определяется , где (укажите все правильные):

+а) — срок окупаемости дополнительных капитальных затрат

+д) — величина снижения затрат на обработку информации

319. Долю окупаемости дополнительных капитальных затрат за год определяют , где (укажите все правильные):

+б) — коэффициент экономической эффективности капитальных затрат

+в) — величина снижения затрат на обработку информации

+д) — срок окупаемости дополнительных капитальных затрат

320. Абсолютным показателем снижения трудовых затрат является разность , где (укажите все правильные):

+а) — годовая трудоемкость проектируемого варианта системы обработки информации

+г) — годовая трудоемкость существующего варианта системы обработки информации

д) — коэффициент неравномерности нагрузки на предприятиях связи

321. Относительными показателями снижения трудовых затрат являются или , где (укажите все правильные):

+б) — разность между годовыми трудовыми затратами базового и проектируемого вариантов решений

в) — коэффициент выбытия основных производственных фондов

+г) — годовая трудоемкость существующего варианта системы обработки информации

д) — коэффициент неравномерности нагрузки на предприятиях связи

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

+б) — годовая трудоемкость проектируемого варианта системы обработки информации

+г) — годовая трудоемкость существующего варианта системы обработки информации

д) — коэффициент неравномерности нагрузки на предприятиях связи

323. Значение абсолютного снижения трудовых затрат , где (укажите все правильные):

+а) — коэффициент, учитывающий возможность полного высвобождения работников

+б) — разность между годовыми трудовыми затратами базового и проектируемого вариантов проектных решений

в) — коэффициент выбытия основных производственных фондов

+г) — годовой фонд времени одного работника, занятого в сфере обработки экономической информации

д) — коэффициент неравномерности нагрузки на предприятиях связи

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

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

327. При копировании в табличном процессоре MS Excel формулы =А3+$В$3 из ячейки С3 в ячейку D4 формула примет вид:

328. Адрес в электронной таблице указывает координату:

329. Данные в электронной таблице могут быть (укажите все правильные):

330. Для настройки стандартных установок новой рабочей книги Excel, необходимо дать команду:

332. В MS Excel значение в ячейке С3 при копировании формулы =А1*В1 из ячейки С1 в ячейку С3 равно:

333. Операция выполняемая в Excel в отсутствии скобок в первую очередь …

334. Выделенный блок ячеек в электронной таблице имеет диапазон:

337. Правильная запись формулы в табличном процессоре Excel:

338. В электронной таблице выделен блок ячеек, его диапазон:

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

б) определения размера ежемесячных выплат для погашения кредита

в) исчисления логарифмов, тригонометрических функций

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

д) вычисления длины строки, преобразования значений букв в строчные

340. Команды редактирования в электронной таблице выполняют функции:

в) выравнивания данных в ячейках, назначения шрифтов, толщины линий

341. Создание формулы в электронной таблице Excel начинается с ввода знака _______.

342. В электронных таблицах выражение -4Е+05 относится к следующему типу данных:

343. В MS Excel ячейка, над содержимым которой производятся какие-то действия и выделенная рамкой, является ____________.

344. В электронной таблице выделен блок ячеек, его диапазон:

345. В электронных таблицах выделена группа ячеек В2:С4. Сколько ячеек входит в эту группу?

346. В MS Excel результатом вычислений в ячейке С1 будет:

347. Дан фрагмент электронной таблицы, содержащий числа и формулы. В ячейке E5 после выполнения расчетов по формуле будет:

348. В MS Excel результатом вычислений в ячейке С1 будет:

349. Правильный вариант записи формулы в табличном процессоре Excel:

351. В MS Excel результатом вычислений в ячейке С1 будет:

353. Какое значение будет в ячейке С3 MS Excel при копировании формулы =А1*$В$1 из ячейки С1 в ячейку С3:

354. Для переноса и копирования данных в электронной таблице используется:

355. К какому типу данных в электронных таблицах относится выражение: $А$4*В5

356. После ввода формулы в ячейку электронной таблицы отображается:

357. При копировании в табличном процессоре MS Excel формулы =$А$3+В3 из ячейки С3 в ячейку D2 формула примет вид:

358. Дан фрагмент электронной таблицы, содержащий числа и формулы. В ячейке H9 после выполнения расчетов по формуле будет:

359. В электронных таблицах указывает на использование абсолютной адресной ссылки знак:

360. Пользователь совершил ряд действий, после которых окно электронной таблицы приобрело вид:. После того, как пользователь нажал клавишу “ENTER” (щёлкнул кнопкой мышки), выполнилась операция…

д) вместо 3-й строки появилась новая строка с пустыми ячейками

г) указанием ссылок на первую ячейку А1 и последнюю ячейку

364. Дан фрагмент электронной таблицы, содержащий числа и формулы. В ячейке D7 после выполнения расчетов по формуле будет:

365. Пункты меню «Формат», «Ячейка» в MS Excel позволяют:

+б) задать форматы чисел, изменить шрифт, задать тип выравнивания

366. Какое значение будет в ячейке С3 при копировании формулы =$А$1*$В$1 из ячейки С1 в ячейку С3:

367. При копировании в табличном процессоре MS Excel формулы =А3+$В$3 из ячейки С3 в ячейку D2 формула примет вид:

368. В электронной таблице выделена группа ячеек А1:С2. Сколько ячеек входит в эту группу?

370. Пользователь совершил ряд действий, после которых окно электронной таблицы приобрело вид:. После того, как пользователь нажал клавишу “ENTER” (щёлкнул кнопкой мышки), выполнилась операция…

г) в выбранном столбце прибавилось количество ячеек

д) вместо столбца С появился новый столбец с пустыми ячейками

371. Чтобы в MS Excel при копировании формулы из одной ячейки в другую адрес ячейки в формуле изменялся автоматически, нужно:

372. Правильный вариант записи формулы в табличном процессоре Excel:

374. Статистические функции табличных процессоров используются для:

б) определения размера ежемесячных выплат для погашения кредита

+в) вычисления среднего значения, стандартного отклонения

г) исчисления логарифмов, тригонометрических функций

д) вычисления длины строки, преобразования значений букв в строчные

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

б) выполнение математических операций. Не содержат алфавитных и специальных символов

+в) выполнение арифметических, логических и других действий, производимых с данными из других ячеек

г) формирование диаграмм и графиков, таблиц, рисунков

376. В электронной таблице выделена группа ячеек А1:В3. Количество ячеек входящих в эту группу:

377. На рис. представлено окно табличного процессора MS Excel с окном табличного документа. На активном листе данного документа содержимым ячейки A95 является…

378. Абсолютный адрес в электронных таблицах Excel – это:

а) расстояние от клетки, содержащей формулу, до клетки, на которую в ней имеется ссылка

+в) адрес, в котором не изменяются номера строк и столбцов

г) полный адрес, указывающий номера строки и столбца клетки

379. В электронных таблицах выражение =$А$4*В5 относится к следующему типу данных:

+б) содержат операторы сравнения, определяют истинность, логичность выражения

г) содержат порядок выполнения действий, принятый в математике

381. Команды форматирования в электронной таблице выполняют функции:

+в) выравнивания данных в ячейках, назначения шрифтов, толщины линий

382. Что из ниже перечисленного является текстом в ячейке Excel?

383. Числовые данные в электронной таблице обеспечивают:

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

+б) выполнение математических операций. Не содержат алфавитных и специальных символов

в) выполнение арифметических, логических и других действий, производимых с данными из других ячеек

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

384. Какое значение будет в ячейке С3 при копировании формулы =$А$1*В1 из ячейки С1 в ячейку С3:

385. Для выделения в электронной таблице Excel несколько блоков ячеек, используют на клавиатуре клавишу:

а) множество ячеек, образующих область произвольной формы

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

д) множество ячеек, образующих область квадратной формы

387. Ячейка электронной таблицы называется текущей, если:

388. Диапазон A5:D8 включает _______ ячеек электронной таблицы

389. В электронной таблице записано арифметическое выражение: 2 / 3 ^ 2 — (13 — 6) / 2 / 4. Математическую запись, соответствующая этому выражению:

390. Арифметическое выражение может быть записано в электронных таблицах в виде:

391. Числовая константа 300000 может быть записана в электронной таблице в виде:

392. Числовая константа12,3Е+4 может быть записана в электронной таблице в виде:

393. Дан фрагмент электронной таблицы: . Определить, какое из утверждений истинно для этого фрагмента таблицы.

394. Дан фрагмент электронной таблицы в режиме отображения формул. Чему равны значения в ячейках В2 и В3 (после выхода из режима отображения формул), если в них скопировано содержимое ячейки В1?

395. Чему равно значение, вычисленное по формуле: =ИЛИ(A115), если в ячейке A1 электронной таблицы находится число 27?

397. В ячейку с адресом B1 занесена формула: =ЕСЛИ(A1>22;»НОЧЬ»;»ДЕНЬ»). Чему равно значение в ячейке B1 электронной таблицы, если в ячейке A1 находится число 13?

398. Учащиеся проходят тестирование. Если сумма баллов больше 16, но меньше 19, то ученик получает оценку 4. Какое условие, проверяет, получит ли тестируемый оценку 4. Сумма баллов хранится в ячейке электронной таблицы с адресом C10.

399. В ячейку с адресом C1 электронной таблицы занесена формула: =ЕСЛИ(A15;0;-1)). Чему равно значение в ячейке C1, если значение в ячейке A1 равно 15?

402. Дан фрагмент электронной таблицы в режиме отображения формул. Чему равны значения в ячейках В2 и В3 (после выхода из режима отображения формул), если в них скопировано содержимое ячейки В1?

403. Дан фрагмент электронной таблицы, содержащий числа и формулы. Чему будет равно значение в ячейке В3 после выполнения расчетов по формулам?

404. Правильная форма записи арифметического выражения в электронных таблицах:

406. Покупатели магазина пользуются скидками в 10%, если покупка состоит более чем из пяти наименований товаров или стоимость покупки превышает 100 рублей. В ячейки электронной таблицы B2 и C2 занесены соответственно количество товаров и стоимость покупки для одного покупателя. Какая формула будет вычислять стоимость покупки с учётом скидки.

407. Дано исходное состояние таблицы:. В ячейку C2 занесена формула: =ЕСЛИ(C1=0;СУММ(A1:A3);ЕСЛИ(C1=1;СУММ(B1:B3);»Данных нет»)). Какое значение находится в ячейке C2, если ячейка C1 пустая?

408. В ячейку B1 электронной таблицы занесена формула: =0,314Е+1*2*A1. Чему равно значение в ячейке B1, если ячейка A1 содержит число 6?

409. Арифметическое выражение может быть записано в ячейке электронной таблицы в виде:

410. Дано исходное состояние таблицы в режиме отображения формул: . Что появится в ячейках A2, B2 и C2 в режиме отображения значений?

411. В MS Exsel при копировании содержимого ячейки A2 в ячейки B2 и A3 в них были занесены формулы $A1+C1 и $A2+B2 соответственно. Что было записано в ячейке A2?

412. Дан фрагмент электронной таблицы: . Определить диапазон(ы) ячеек, по значениям которых была построена диаграмма.

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

Условная функция и логические выражения в электронной таблице Excel

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

А	В	С	D	Е	F	G
1	Полный тариф (руб. за мин.)	6	Льготный тариф (руб. за мин.)	3
2	Фамилия	Дата переговоров	День недели	Праздник	Время начала переговоров	Длительность	Оплата
3	Зверева	1/2005	Среда	Да	12	7
4	Сомов	12/2007	Четверг	Нет	21	6
5	Котова	13/2007	Пятница	Нет	4	10
6	Ильин	17/2007	Вторник	Нет	11	20
7	Яшков	14/2007	Суббота	Нет	18	15
8	Львов	15/2007	Воскресенье	Нет	22	3