Пример выполнения задания по теме
«Логические выражения в таблице»
В среде MS Excel существует возможность не только вычислять и выводить результат того или иного выражения по заданному условию, но и подсчитывать итоговые значения в заданном диапазоне ячеек по определённому условию (критерию). Этим целям служат специальные функции Excel:
| Функция | Назначение |
| СЧЕТЕСЛИ (диапазон; критерий) Категория † Статистические; | подсчитывает количество непустых ячеек в диапазоне, по заданному условию. |
| СУММЕСЛИ (диапазон; критерий; диапазон суммирования) Категория † Математические; | суммирует ячейки, заданные указанным условием. |
Задание. Торговый агент получает вознаграждение в размере некоторой доли от суммы совершенной сделки: если объем сделки до 5000 руб., то в размере 5%, если не ниже – 7%. Определите объем вознаграждения для каждого агента некоторой фирмы. Сколько агентов в фирме? Сколько агентов совершили сделки на сумму более 10000 руб., каково их суммарное вознаграждение?
Методические указания к выполнению задания
1. Объем вознаграждения агента зависит от суммы совершенной сделки, имеем два варианта: сумма сделки больше или меньше 5000р.
2. Алгоритм решения данной задачи - разветвляющийся, в Excel такой алгоритм реализуется с помощью функции ЕСЛИ.
3. Вычислить «Объем вознаграждения» по формуле:
=ЕСЛИ(B2>=5000;B2*5%;B2*7%).
4. Всего агентов в фирме: Е6 =СЧЕТЗ (А2:А5) – Категория † Статистические.
Напоминание. Функция СЧЁТЗ - подсчёт количества значений (непустых ячеек) в указанном диапазоне; значениями могут быть тексты, даты, логические величины.
5. Число агентов, совершивших сделку на сумму более 10000р.:
Е7=СЧЕТЕСЛИ(В2:В5;”>=10000”),
их суммарное вознаграждение: Е8 =СУММЕСЛИ(В2:В5; “>=10000”;С2:С5).
Варианты задания по теме «Логические выражения в таблице»
1.Известны оценки (по пятибалльной шкале), полученные абитуриентами на каждом из трех вступительных экзаменов. Для каждого абитуриента найти сумму баллов и определить, поступил ли он в учебное заведение, если известно, что «проходной» балл (минимально необходимая для поступления сумма баллов) равен 13. Сколько всего абитуриентов? Сколько абитуриентов поступило в учебное заведение? Определить средний суммарный балл поступивших и не поступивших .
2.Известна информация о багаже (количество вещей и общий вес багажа) пассажиров автобуса. Если у пассажира нет багажа, то соответствующие ячейки пустые. Определить для каждого пассажира необходимость оплаты багажа (да/нет), если оплачивается багаж с весом более 10кг. Сколько всего пассажиров? Сколько пассажиров должны оплатить багаж? Определить суммарный вес багажа пассажиров, имеющих одну вещь, более двух вещей.
3.В таблице имеются сведения о результатах игр по баскетболу между двумя командами (дата игры, набранные каждой командой очки). Необходимо в каждой игре определить победителя. Сколько игр выиграла каждая команда? Определить среднее количество набранных очков каждой командой. Ничьи во внимание не принимать.
4.Известны оценки каждого ученика по трем предметам. Для каждого ученика определить средний балл. Определить успеваемость каждого ученика: «да» - если средний балл выше 3,5. Найти общее количество «успевающих». Найти общее количество пятерок в таблице, количество двоек по каждому предмету. Найти средний балл у «успевающих» учеников по одному из предметов.
5.Таблица содержит сведения о лекарствах: название, срок годности, цена, нужен ли рецепт при покупке (да/нет). При покупке любого лекарства пенсионерам предоставляется скидка: 5% - если цена ниже 100 руб., и 7% - в противном случае. Определить стоимость каждого лекарства для пенсионеров. Определить количество лекарств, отпускаемых без рецепта.
6.Таблица содержит сведения о сотрудниках двух отделов: фамилия, имя, отдел, оклад. Начислить премию каждому сотруднику: 15% - работникам склада, 20% - работникам бухгалтерии. Подсчитать сотрудников с именем Иван, определить число сотрудников, у которых оклад не превышает 2000 руб. Подсчитать общий премиальный фонд работников склада.
7.Таблица содержит сведения о количестве осадков, выпавших за каждый день недели, и о температуре воздуха в эти дни. Для каждого дня определить, шёл снег или дождь. Считать, что идет дождь, если температура воздуха выше 00С. Определить, сколько дней выпадал снег и сколько – дождь. Определить суммарное количество осадков в те дни, когда шёл дождь.
8.Таблица содержит сведения о студентах (фамилия, возраст, рост), желающих заниматься в баскетбольной секции. Для каждого определить, принят ли он в команду (да/нет), если принимают тех, чей рост не ниже 175см. Сколько человек принято? Каков средний возраст принятых?
9.Таблица содержит сведения о количестве отработанных часов за каждый день месяца каждым работником предприятия. Пустая ячейка означает, что в этот день он не работал (выходной или день отпуска). Для каждого определить количество рабочих дней в этот месяц, суммарное количество отработанных часов. Начислить премию тем, кто отработал более 120 часов (да/нет). Определить количество работников, отработавших за месяц 20 и более дней. Определить общее количество часов, выработанных сотрудниками, которым премия не начислена.
10.Итоги отборочного тура чемпионата по футболу заданы в виде таблицы. За выигрыш дается 3 очка, за проигрыш – 0, за ничью – 1.
| Команда | В | Н | П | О | |||||
| Спартак | Х | ||||||||
| Динамо | Х | ||||||||
| ЦСКА | Х | ||||||||
| Ротор | Х | ||||||||
| Камаз | Х |
Для каждой команды определить:
ü Число выигрышей (В), ничьих (Н) и проигрышей.
ü Сумму набранных очков
ü Верно ли, что число выигрышей у команды больше числа проигрышей (да/нет), среднее число набранных очков у таких команд.
11.Провайдер интернет-услуг установил следующую систему оплаты: при работе с 2 до 10 часов – 0,5$ в час, в остальное время суток – 0,75$; в час. Таблица содержит сведения о пользователях: ФИО, момент начала работы (задан в виде целого числа, обозначающего час соответствующего момента времени), количество часов работы. Для каждого пользователя определить стоимость работы в интернете, если известно, что все время работы находилось в одном из указанных интервалов времени суток. Определить количество пользователей работавших более трех часов, определить общую стоимость их работы.
12.В таблице содержатся сведения о пациентах клиники: ФИО, возраст, пол, стоимость лечения без скидок. Вычислить стоимость лечения с учетом скидки, если скидка предоставляется пациентам старше 60 лет в размере 10%. Определить количество пациентов, которым предоставляется скидка, общую стоимость их лечения. Сколько всего пациентов в клинике?
13.Таблица содержит сведения о студентах: ФИО, количество сданных лабораторных работ, оценка по контрольной работе. В отдельный столбец каждому студенту записать результат зачёта: «зачтено», если он сдал не менее 5 лабораторных работ и получил по контрольной работе оценку не менее 3 баллов. Сколько студентов аттестовано? Сколько получили зачёт и скольким «не зачтено»?
3. Лабораторная работа по теме: «Адресация»
Цель работы: научиться использовать разные типы адресации (способы указания адресов ячеек в формуле) при решении различных задач в Excel, включающих копирование формул.
Теоретическая справка. При вычислении по формулам приходится ссылаться на данные, находящиеся в других ячейках и имеющих собственные адреса. Ссылка определяет нахождение ячейки или группы ячеек в книге Excel, её адрес или адрес блока ячеек. Адреса в Excel бывают трех типов:
· относительные (например, А1),
· абсолютные ($A$1),
· смешанные ($A1 или A$1).
Относительный адрес устанавливается автоматически (по умолчанию) при вводе формулы. Адреса, находящиеся в формуле, при копировании настраиваются относительно начального положения на новое местоположение в таблице по следующему правилу:
при копировании формулы по строке изменяется имя столбца,
а копирование по столбцу изменяет номер строки.
 |
Формулы при вычислениях в таблице, как правило, вводятся в первую строку таблицы и далее копируются на весь столбец. Если адреса, содержащиеся в формуле, указывают на ячейки, находящиеся внутри таблицы, то такая настройка относительных адресов при копировании вполне уместна.
Рис.10. Пример копирования формулы с относительными адресами.
Если копируемая формула содержит внешние ссылки, т.е. адреса ячеек, находящиеся вне таблицы, то приходится изменять тип адреса внешней ячейки. В этом случае выбирают либо абсолютную адресацию, либо смешанную (рис.11).
Абсолютная адресация фиксирует (делает неизменяемыми) при копировании формулы и номер строки, и имя столбца. Признаком абсолютной адресации является символ $, устанавливаемый с помощью клавиши F4. Абсолютный адрес устанавливается тогда, когда формулу, его содержащую, необходимо копировать и по строке, и по столбцу.
GПримечание. Ячейка, которой присвоено имя, имеет абсолютный адрес.
Смешанная адресация фиксирует только один параметр адреса: либо имя столбца, либо номер строки. Если копирование формулы происходит по столбцу таблицы (вертикально), то фиксируется номер строки (имя столбца не меняется – см. правило выше), например, С$1. Если формула копируется по строке таблицы (горизонтально), фиксируется имя столбца, например, $С1 (рис.11а и 11б).
Рис.11а. Пример копирования формулы с абсолютными и смешанными адресами
( вертикальное копирование)
Рис.11б. Пример копирования формулы с абсолютными и смешанными адресами
(горизонтальное копирование)
G Примечание
Изменить тип адреса ячейки можно так:
· выделить ячейку с формулой;
· установить курсор мыши на адресе ячейки в строке формул;
· выделить его, дважды щелкнув левой клавишей мыши;
· выбрать тип адреса, последовательно
нажимая клавишу F4.