Расчеты с использованием встроенных функций

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

 

Категории встроенных функций

 

ABS(< число> ) – абсолютное значение числа.

Пример: ABS(-7) =7.

ПРОИЗВ(< число1>; < число2>; …) – возвращает произведение чисел, входящих в список аргументов. Если в множестве аргументов встречаются нечисловые элементы, они в произведении не участвуют (пропускаются).

Примеры: ПРОИЗВ(3; 2; 4) = 3*2*4 = 24,

ПРОИЗВ(А1; 2; А2: С3) = А1*2*А2*В2*С2*А3*В3*С3.

СУММ(< число1>; < число2>; …) – возвращает сумму чисел, входящих в список аргументов. Список может содержать до 30 элементов.

Примеры: СУММ(3; 2) = 3 + 2 = 5,

СУММ(А1: А3) - суммирует три первых числа в столбце А,

КОРЕНЬ(< число> ) – извлекает квадратный корень из положительного числа.

СТЕПЕНЬ(< число>; < степень> ) – возводит число в заданную степень.

EXP(< степень> ) – возвращает результат возведения в степень основания натурального логарифма (е = 2, 71878…).

Пример: EXP(2) = e² =7, 389.

LOG10(< число> ) – вычисляет десятичный логарифм числа.

LOG(< число>; < основание> ) – вычисляет логарифм числа по заданному основанию.

LN(< число> ) – вычисляет натуральный логарифм числа.

ПИ() – возвращает значение константы p = 3, 14159….

ГРАДУСЫ(< угол в радианах> ) – преобразует радианную меру угла в градусную.

РАДИАНЫ(< угол в градусах> ) - преобразует градусную меру угла в радианную.

SIN (< угол в радианах> ) – синус угла.

COS (< угол в радианах> ) – косинус угла.

TAN (< угол в радианах> ) – тангенс угла.

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

Пример: Показать большее из чисел А1 и В5

= ЕСЛИ(A1> B5; A1; B5).

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

ГПР (< искомое значение>; < область поиска>; < номер строки извлечения>; < тип поиска> ) – осуществляет поиск искомого значения в самой верхней строке области поиска и возвращает значение в том же столбце из заданной строки таблицы. Функция ГПР() используется, когда искомые значения расположены в верхней строке таблицы данных, а возвращаемые значения расположены на несколько срок ниже. Буква «Г» в ГПР() означает «горизонтальный».

ВПР (< искомое значение>; < область поиска>; < номер столбца извлечения>; < тип поиска> ) - осуществляет поиск искомого значения в самой левом столбце области поиска. Результат извлекается из столбца с заданным номером относительно левого (ключевого) столбца области поиска. Функция ВПР() является полным вертикальным аналогом функции ГПР().

Пример: Задача с использованием функции ВПР().

Положим, нужно обслужить фирмы-заказчики, предварительно внесшие некоторые денежные взносы (область А2: В5). Таблица должна быть устроена таким образом, чтобы оператор, вводя название фирмы (например, в А8), должен сразу (в В8) видеть эту сумму, с тем, чтобы не допустить отпуск товара свыше нее. В списке фирм заказчик может и отсутствовать. В этом случае в столбце Взнос должен быть выведен 0. Для розыска значения взноса в ячейку B8 достаточно ввести формулу = ВПР(А8; А$2: В$5; 2; 0).

 

	A	B	C	D
	Фирма	Взнос
	Весна
	Сервис
	Флора
	Восток
	Заказчик	Взнос	Оплата	Остаток
	Флора
	Запад

Если введено имя фирмы, не сделавшей взноса, в клетке будет сообщение об ошибке вида #Н/Д (нет данных). Для формирования правильной реакции системы следует усложнить выражение:

= ЕСЛИ(ЕНД(ВПР(А8; А$2: В$5; 2; 0)); 0; ВПР(А8; А$2: В$5; 2; 0)).

Здесь использована функция ЕНД() распознавания соответствующей ошибки. Тогда Остаток в ячейкеD8 вычисляется по формуле = B8 – C8.

СУММЕСЛИ(< область просмотра>; < критерий поиска>; < область суммирования> ) – функция ищет в заданной области просмотра данные, отвечающие критерию поиска и суммирует значения соответствующих ячеек из области суммирования. Если область суммирования не задана, суммирование производится из области просмотра. Критерий поиска может включать только одно условие.

СЧЁТ(< область просмотра> ) – подсчет в области просмотра количества числовых ячеек. Пустые ячейки, логические значения и тексты пропускаются.

СЧЁТЕСЛИ(< область просмотра>; < критерий поиска> ) – производится подсчет числа ячеек в области просмотра, отвечающих критерию поиска.

	А	В
	ФИО	Рабочих дней
	Петр
	Иван
	Олег
	Иван

Примеры: Имеются данные о числе отработанных рабочими дней (таблица):

=СУММЕСЛИ(А2: А5; ”Иван”; В2: В5) = 34 – число дней, отработанных Иваном.

=СЧЁТЕСЛИ(В2: В5; ”> 20”) = 2 – число человек, работавших больше 20 дней.

=СЧЁТЕСЛИ(А2: А5; ”Иван”) = 2 – сколько раз в документе встретилось имя Иван.

И(< условие1>; < условие2>; …) – возвращает значение ИСТИНА, если истинны все аргументы. Функция еще называется функцией логического умножения.

ИЛИ(< условие1>; < условие2>; …) – возвращает значение ИСТИНА, если истинен хотя бы один из аргументов. Функция еще называется функцией логического сложения.

МАКС(< число1>; < число2>; …) – возвращает максимальное из чисел, входящих в список аргументов.

Пример: МАКС(В2: В5) = 23.

МИН(< число1>; < число2>; …) – возвращает минимальное из чисел, входящих в список аргументов.

Пример: МИНВ2: В5) = 12..

СРЕДЗНАЧ(< число1>; < число2>; …) – возвращает среднее арифметическое своих аргументов.

Пример: СРЕДЗНАЧ(В2: В5) = (23+12+20+22)/4.

Примеры: Выяснить, правда ли, что значение в ячейке А1 больше значений в ячейках А2 и А3.

= ЕСЛИ(И(А1> А2; А1> А3); ”А1 самое большое”; “ А1 не самое большое ”).

Выяснить, правда ли, что среди значений в ячейках А1, А2, А3 есть отрицательные.

= ЕСЛИ(ИЛИ(А1< 0; А2< 0; А3< 0); ”Правда”; “Неправда”).

 

 

Варианты заданий

Вариант 1

Вычислить величину квартплаты. Она состоит из оплаты за содержание жилья и отопления (4руб., 10руб. за квадратный метр площади); за пользования горячей, холодной водой и за водоотведение (90 руб., 20 руб. и 40 руб. за каждого проживающего в квартире). Некоторым категориям жильцов положены льготы при оплате коммунальных услуг. Участники войны платят на 50%, а ветераны труда - на 25% меньше. Эти лица отмечены в колонке < Льготы> буквами " у", " в" соответственно. Фактическая оплата меньше полной для жильцов, имеющих льготы.

Содержание жилья	Отопление	Горячая вода	Холодная вода	Водоотведение
РАСЧЕТ КВАРТПЛАТЫ
Номер квартиры	Площадь	Человек	Льготы (у/в)	Квартплата
полная	фактическая
№1
№2
№3
№4
№5
№6
№7
№8
Всего

Построить график: Площадь – Фактическая квартплата

 

Вариант 2

Выполнить расчет сдельной (т.е. пропорциональной количеству произведенной продукции) зарплаты, а также налога и суммы на руки. Зарплата сотрудника определяется числом обработанных деталей. Если работник допустил брак и испортил деталь, ее стоимость вычитается из заработка. Полагаем, что налог исчисляется в зависимости от зарплаты: если она выше 10000 - налог составляет 20%, если нет -13%.

 

Налог до: 10000р	13%	Налог от: 10000р	20%
Стоимость работы
Стоимость детали
РАСЧЕТ ЗАРПЛАТЫ
ФИО	Обработано деталей	Деталей брака	Зарплата	Сумма налога	Сумма на руки
ХХХ	ХХХ	ХХХ	??? р	??? р	??? р
ХХХ	ХХХ	ХХХ	??? р	??? р	??? р
ХХХ	ХХХ	ХХХ	??? р	??? р	??? р
ХХХ	ХХХ	ХХХ	??? р	??? р	??? р
ХХХ	ХХХ	ХХХ	??? р	??? р	??? р
ХХХ	ХХХ	ХХХ	??? р	??? р	??? р
ХХХ	ХХХ	ХХХ	??? р	??? р	??? р
….
Всего	???	???	??? р	??? р	??? р

Построить график: Фамилия – Сумма на руки

Вариант 3

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

 

Тарифная сетка
Разряд:
Тариф:
Премия:
ЗАРПЛАТА
ФИО	Дней	Разряд	Зарплата	Премия	Начислено
ХХХ	ХХ	Х	??? р	??? р	??? р
ХХХ	ХХ	Х	??? р	??? р	??? р
ХХХ	ХХ	Х	??? р	??? р	??? р
ХХХ	ХХ	Х	??? р	??? р	??? р
ХХХ	ХХ	Х	??? р	??? р	??? р
ХХХ	ХХ	Х	??? р	??? р	???
…
Всего	???		??? р	??? р	??? р

Для поиска разрядного тарифа и начисления премии должна быть использована функция ГПР.

 

Построить график: Фамилия – Начислено

 

 

Вариант 4

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

Однако поставщик, заинтересованный в быстром обороте средств, предлагает дополнительно еще и довольно заметную скидку с оптовой цены, если товар будет взят не на реализацию, а с предоплатой, но уже без возможности возврата. Для торговца с одной стороны получается выгода, - товар обходится дешевле. Хотя с другой стороны это риск: вдруг данная книга совсем не найдет спроса. В конце концов, принимается компромиссное решение. Товар в принципе берется на реализацию, но некоторая его часть идет с предоплатой.

 

⇐ Предыдущая1234567Следующая ⇒

Поделиться:

Популярное:

1. I) Получение передаточных функций разомкнутой и замкнутой системы, по возмущению относительно выходной величины, по задающему воздействию относительно рассогласования .
2. Анализ базовых функций гражданского общества. Демократические функции гражданского общества.
3. Аппроксимация в виде системы линейно независимых функций
4. Блок III. Обследование неречевых функций ребенка
5. Верстка страницы с использованием таблицы.
6. ВОЗРАСТНАЯ ДИНАМИКА ПСИХОФИЗИОЛОГИЧЕСКИХ ФУНКЦИЙ
7. Возрастные изменения функций зубочелюстной системы.
8. Вопрос: Какие из данных функций являются непрерывными?
9. ВопросПризнаки функций права: Править
10. Восстановление двигательных функций
11. Восстановление неречевых функций
12. Врезка 5.5. Анализ функций полезности двух респондентов.