Лабораторная работа № 6 СТАНДАРТНЫЕ ФУНКЦИИ
Стандартные функции
Программа Excel позволяет производить простые вычисления с применением простейших операций (рассмотрены в лабораторной работе № 2, стр. 22) и сложные вычисления с использованием большого числа встроенных стандартных функций.
Функция - это заранее определенное выражение, которое имеет один или несколько аргументов.
Каждая функция вызывается по имени и производит расчет по заранее заданному алгоритму расчета, например СУММ (аргумент1, аргумент2 и т. д.).
Существует несколько категорий (групп) стандартных функций:
Категория
| Применение
| Количество функций
| Финансовые
| Расчет процентов, увеличение и уменьшение суммы капитала и т. д.
|
| Дата и время
| Вычисления с датами и временем
|
| Математические
| Математические и тригонометрические вычисления, которые выполняются на научных калькуляторах
|
| Статистические
| Вычисления средних значений, сумм, максимума и т. д.
|
| Ссылки
и массивы
| Вычисления с таблицами данных
|
| Работа
с базой данных
| Работа со списками
|
| Текстовые
| Сравнение, преобразование и форматирование текста в ячейках
|
| Логические
| Вычисления, результат которых может быть ИСТИНА или ЛОЖЬ
|
| Проверка свойств и значений
| Определение ошибок, происходящих при вычислениях
|
|
Для вставки стандартной функции на рабочий лист электронной таблицы необходимо выполнить следующие шаги:
1 шаг Курсор установить на ячейку, где будет помещен результат вычисления функции.
2 шаг «Вставка» → «Функция» или нажать пиктограмму на панели инструментов «Стандартная».
3 шаг Курсором в поле «Категория» выбрать группу функций → в поле «Функция» выбрать функцию → нажать «ОК».
4 шаг В зависимости от выбранной функции ввести значения аргументов.
В функциях используются операторы сравнения и операторы связи
Оператор
| Значение
| =
| Равно
| <
| Меньше
| >
| Больше
| <=
| Меньше или равно
| >=
| Больше или равно
| <>
| Не равно
| :
| Диапазон, например = СУММ (A1:C10)
| ;
| Объединение, например =СУММ (A10; A2:C7)
|
У нас нет возможности рассмотреть все функции, и в этом нет необходимости: стандартные функции применяются по мере решаемых задач.
Рассмотрим применение функции на примере решения задачи «Расчет с клиентами отеля».
Постановка задачи:
1) Постоянные исходные данные.
Тарифы проживания (стоимость номера за 1 день проживания):
Класс
| Люкс
|
|
|
| Стоимость
| $50
| $30
| $20
| $15
|
2) Расчетные данные:
№ п/п
| Наименование
| Описание
|
| Признак доплаты
| Устанавливается:
«Да» - было забронировано;
«Нет» - не было забронировано
|
| Сумма доплаты
| Сумма доплаты = 1 дню проживания в номере соответствующего класса, если номер был предварительно забронирован
|
| Признак скидки
| Устанавливается:
«Да» - если клиент проживал более 10 дней;
«Нет» - если клиент проживал 10 дней или менее 10 дней
|
| Сумма скидки
| Сумма скидки =15% за дни проживания, если клиент проживает в номере более 10 дней
|
| Сумма оплаты
без скидок
и доплат
| Сумма оплаты без скидок и доплат = количество дней проживания * стоимость тарифа соответствующего класса
|
| Общая сумма оплаты
| Общая сумма оплаты = Сумма оплаты без скидок и доплат + Сумма доплаты - Сумма скидки
|
3) Вводимые исходные данные:
№ п/п
| Наименование
| Описание
|
| Клиент
| Фамилия, имя, отчество клиента
|
| Бронирование
| Знак плюс (+) указывает, что номер был забронирован
|
| Проживание
| Дата заезда и дата выезда клиента
|
| Класс номера
| Класс номера проживания клиента
| Для решения задачи «Расчет с клиентами отеля» будем использовать функции различных категорий.
Задание №1. Использование логических функций
Функция
| Действие
| ЕСЛИ (лог_выражение; значение_если_истина; значение_если_ложь)
| Лог_выражение - вводится логическое выражение, например А1>10. Значение_если_истина - вводится значение, если логическое выражение истинно, например Н5*10. Значение_если ложь - вводится выражение, если логическое выражение ложно, например Н5-К5
| И (<Условие 1>;
<Условие2>; ...)
| Возвращает значение ИСТИНА, если истинны все аргументы
| ИЛИ (<Условие1>; <Условие2>; ...)
| Возвращает значение ИСТИНА, если истинным является хотя бы один из аргументов
| Рассмотрим применение логических функций на примере электронной таблицы «Расчет с клиентами отеля»[*].
O Загрузите программу Excel.
|
O Создайте электронную таблицу вида:
|
ê Установите «Признак доплаты»: «Да» - номер был забронирован; «Нет» - номер не был забронирован для всех клиентов:
| Ü Курсор установите на ячейку G9.
Ü «Вставка» → «Функция» или нажмите пиктограмму на панели инструментов «Стандартная».
Ü В поле «Категория» выберите «Логические» → в поле «Функция» установите «ЕСЛИ» → нажмите «OK».
Ü В поле «Лог_выражение» введите: C9=”+” (спрашиваем «Было бронирование?»).
Ü В поле «Значение_если_истина» введите: Да.
Ü В поле «Значение_если_ложь» введите: Нет.
Ü Нажмите клавишу <Enter>.
Ü Произведите автозаполнение для всех клиентов.
После выполненных действий электронная таблица должна принять вид:
ê Установите «Признак скидки» для клиентов: «Да» - если клиент проживал более 10 дней; «Нет» - если клиент проживал 10 дней или менее:
| Ü Курсор установите на ячейку I9.
Ü «Вставка» → «Функция» или нажмите пиктограмму на панели инструментов «Стандартная».
Ü В поле «Категория» выберите «Логические» → в поле «Функция» установите «ЕСЛИ» → нажмите «OK».
Ü В поле «Лог_выражение» введите: (E9-D9)>10.
Ü В поле «Значение_если_истина» введите: Да.
Ü В поле «Значение_если_ложь» введите: Нет.
Ü Нажмите клавишу <Enter>.
Ü Произведите автозаполнение для всех клиентов.
После выполненных действий электронная таблица должна принять вид:
Задание №2. Использование функций ссылок и массивов
Для удобства расчета оплаты за проживание используем функции ссылок и массивов:
Функция
| Действия
| ГПР(искомое_значение;таблица;номер_строки;диапазон_просмотра)
| Искомое_значение - это значение, которое требуется найти в первой строке таблицы. Искомое_значение может быть значением, ссылкой или текстовой строкой.
Таблица - это таблица с информацией, в которой ищутся данные. Можно использовать ссылку на интервал или имя интервала.
Номер_строки - это номер строки в массиве таблицы, из которой будет выбрано значение, сопоставляемое искомому значению.
Интервальный_просмотр - это логическое значение, которое определяет, нужно ли, чтобы функция ГПР искала точное или приближенное соответствие: если Истина -точный поиск, если Ложь - приблизительный поиск
|
ê Произведите расчет «Сумма оплаты без скидок и доплат» для первого клиента::
| Ü Курсор установите на ячейку К9.
Ü Введите: =(Е9-D9)*
Ü «Вставка» → «Функция» или нажмите пиктограмму на панели инструментов «Стандартная».
Ü В поле «Категория» выберите «Ссылки и массивы» → в поле «Функция» установите ГПР → нажмите «ОК».
Ü В поле «Искомое_значение» введите: F9 (установили класс номера клиента).
Ü В поле «Таблица» введите: I3:L4 (установили диапазон таблицы, где указаны тарифы стоимости классов проживания)
Ü В поле «Номер_строки» введите: 2 (установили строку стоимости, откуда будет выбираться стоимость класса).
Ü В поле «Диапозон_просмотра» введите: 1 (установили точный поиск в таблице тарифов).
Ü Нажмите клавишу <Enter>.
ê Произведите расчет «Сумма оплаты без скидок и доплат» для всех клиентов:
| Ü Установите абсолютную адресацию для таблицы тарифов: курсор установите на ячейку К9 и введите абсолютную адресацию =(Е9-D9)*ГПР(F9;$I$3:$L$4;2;1).
Ü Произведите автозаполнение для всех клиентов/
После выполнения действий электронная таблица должна иметь вид:
ê Произведите расчет «Сумма доплат» для первого клиента:
| Ü Курсор установите на ячейку H9.
Ü «Вставка» → «Функция» или нажмите пиктограмму на панели инструментов «Стандартная».
Ü В поле «Категория» выберите «Логические» → в поле «Функция» установите «ЕСЛИ» → нажмите «ОК».
Ü В поле «Лог_выражение» введите: G9=’Нет’.
Ü В поле «Значение_если_истина» введите: 0.
Ü В поле «Значение_если_ложь» установите функцию ГПР:
· Нажмите в сроке формул:
· Выберите функцию ГПР.
· В поле «Искомое_значение» введите: F9.
· В поле «Таблица» введите: I3:L4.
· В поле «Номер_строки» введите: 2.
· В поле «Диапазон_просмотра» введите: 1.
· Нажмите клавишу <Enter>.
ê Произведите расчет «Сумма доплат» для всех клиентов:
| Ü Установите абсолютную адресацию для таблицы тарифов: курсор установите на ячейку H9 и введите абсолютную адресацию =ЕСЛИ(G9="Нет";0;ГПР(F9;$I$3:$L$4;2;1))
Ü Произведите автозаполнение для всех клиентов.
ê Произведите расчет «Сумма скидок» для первого клиента:
|
Ü Курсор установите на ячейку J9.
Ü «Вставка» → «Функция» или нажмите пиктограмму на панели инструментов «Стандартная».
Ü В поле «Категория» выберите «Логические» → в поле «Функция» установите «ЕСЛИ» → нажмите «ОК».
Ü В поле «Лог_выражение» введите: I9=”Нет”.
Ü В поле «Значение_если_истина» введите: 0.
Ü В поле «Значение_если_ложь» введите: (E9-D9-10)*0,15*.
Ü Вставьте функцию ГПР:
· Нажмите в сроке формул:
· Выберите функцию ГПР.
· В поле «Искомое_значение» введите: F9.
· В поле «Таблица» введите: I3:L4.
· В поле «Номер_строки» введите: 2.
· В поле «Диапазон_просмотра» введите: 1.
· Нажмите клавишу <Enter>.
ê Произведите расчет «Сумма скидок» для всех клиентов:
| Ü Установите абсолютную адресацию для таблицы тарифов: курсор установите на ячейку H9 и введите абсолютную адресацию =ЕСЛИ(G9="Нет";0;ГПР(F9;$I$3:$L$4;2;1))
Ü Произведите автозаполнение для всех клиентов.
После выполнения действий электронная таблица должна иметь вид:
O Произведите расчет «Общая сумма оплаты» (см. постановку задачи «Расчет с клиентами», стр. 60-61 )
|
После выполнения действий электронная таблица должна принять вид:
Произведем краткий анализ заселения в отеле.
O Создайте фрагмент электронной таблицы
|
Задание №3. Использование математических функций
Функция
| Действие
| СУММ (число1, число2 …)
| Суммируется значение указанных ячеек
| СУММЕСЛИ (диапазон, условие, диапазон_суммирования)
| Суммируется значение ячеек из диапазона суммирования, если проверяемое значение с условием дает значение Истина
|
ê Произведите расчет «Общая сумма оплат»:
| Ü Курсор установите на ячейку L26.
Ü «Вставка» → «Функция» или нажмите пиктограмму на панели инструментов «Стандартная».
Ü В поле «Категория» выберите «Математические» → в поле «Функция» - функцию «СУММ».
Ü В поле «Число 1» установите L9:L20.
Ü Нажмите клавишу <Enter>.
O Произведите расчет «Сумма скидок» и «Сумма доплат».
|
ê Произведите расчет «Итоговая сумма по номерам Люкс»:
| Ü Курсор установите на ячейку L32.
Ü «Вставка» → «Функция» или нажмите пиктограмму на панели инструментов «Стандартная».
Ü В поле «Категория» выберите «Математические» → в поле «Функция» - функцию «СУММЕСЛИ».
Ü В поле «Диапазон» установите F9:F20.
Ü В поле «Условие» установите Люкс.
Ü В поле «Диапазон_суммирования» установите L9:L20.
Ü Нажмите клавишу «OK» или <Enter>.
O Произведите расчет «Итоговая сумма по 1 классу», «Итоговая сумма по 2 классу» и «Итоговая сумма по 1 классу».
|
После выполненного задания электронная таблица должна принять вид:
Задание №4. Использование статистических функций
Функция
| Действие
| СРЗНАЧ (число 1, число2 …)
| Вычисляет среднее арифметическое значение указанных чисел
| МАКС (число 1, число2 …)
| Определяет максимальное значение из указанных чисел
| МИН (число 1, число2 …)
| Определяет минимальное значение из указанных чисел
| СЧЕТ (значение 1, значение 2 …)
| Подсчитывает количество чисел, заданных в списке значений (количество ячеек, имеющих числовые значения)
| СЧЕТЗ (значение 1, значение 2 …)
| Подсчитывает количество чисел, заданных в списке значений (количество ячеек, имеющих непустые значения)
| РАНГ (число, ссылка, порядок)
| Вычисляет не только наибольшее или наименьшее значение и располагает в порядке возрастания или убывания, но применяет функцию ранжирования.
| СЧЕТЕСЛИ (диапазон, условие)
| Вычисляет количество ячеек, значение которых удовлетворяют условию.
| СЧИТАТЬПУСТОТЫ (диапазон)
| Вычисляется количество пустых ячеек.
|
ê Произведите расчет «Общее количество клиентов»:
| Ü Курсор установите на ячейку G25.
Ü «Вставка» → «Функция» или нажмите пиктограмму на панели инструментов «Стандартная».
Ü В поле «Категория» выберите «Статистические» → в поле «Функция» установите функцию «СЧЕТЗ».
Ü В поле «Значение1» установите B9:B20.
Ü Нажмите клавишу «OK» или <Enter>.
ê Произведите расчет «Количество клиентов по бронированию»:
| Ü Курсор установите на ячейку G26.
Ü «Вставка» → «Функция» или нажмите пиктограмму на панели инструментов «Стандартная».
Ü В поле «Категория» выберите «Статистические» → в поле «Функция» установите функцию «СЧЕТЕСЛИ».
Ü В поле «Диапазон» установите C9:C20.
Ü В поле «Условие» введите +.»
Ü Нажмите клавишу «OK» или <Enter>.
ê Произведите расчет «Количество клиентов без бронирования», «Количество клиентов, заселенных в номера Люкс», «Количество клиентов, заселенных в номера 1 класса», «Количество клиентов, заселенных в номера 2 класса», «Количество клиентов, заселенных в номера 3 класса».
|
После выполненного задания электронная таблица должна принять вид:
Рассмотрим применение статистических функций на примере решения задачи «Статистический анализ обращений в туристическую фирму “Аист” за ____ 2003 г.»
Постановка задачи:
1) Постоянные исходные данные: текстовые наименования, дни недели.
2) Вводимые исходные данные: числовые значения по количеству обращений и покупок путевок по дням месяца.
3) Расчетные данные:
Наименование
| Описание
| Количество обращений по всем категориям по каждому дню
| = «Количество обращений студентов» + «Количество обращений семейных» + «Количество обращений одиноких»
| Количество проданных путевок по всем категориям по каждому дню
| = «Количество путевок, проданных студентам» + «Количество путевок, проданных семейным» + «Количество путевок, проданных, одиноким»
|
O Произведите ввод исходных данных в соответствии с приведенной таблицей:
|
ê Произведите расчет количества обращений 1 числа месяца по всем категориям:
| Ü Курсор установите на ячейку B5.
Ü «Вставка» → «Функция» или нажмите пиктограмму на панели инструментов «Стандартная».
Ü В поле «Категория» установите «Математические» → в поле «Функция» - функцию «СУММ».
Ü В поле «Число» введите: B6:B8.
O Произведите расчет: «Количество обращений для каждого числа месяца», «Количество купленных путевок по каждому дню месяца».
|
После выполнения задания таблица должна принять вид:
ê Произведите статистический анализ проданных путевок:
| 1) Рассчитайте Количество обращений за путевками за месяц:
Используя функцию СУММ, выполните следующие действия:
Ü Курсор установите на ячейку A15.
Ü Введите: Количество в месяц.
Ü Курсор установите на ячейку A16.
Ü Введите обращений.
Ü Курсор установите на ячейку E16.
Ü Вызовите функцию СУММ.
Ü В поле «Число1» введите: B5:AF5.
O Произведите расчет «Количество путевок, проданных за месяц» и результат разместите в ячейку E17.
|
2) Рассчитайте Количество дней в месяце, когда Студенты не обращались за путевками.
Используя функцию СЧИТАТЬПУСТОТЫ, выполните следующие действия:
Ü Курсор установите на ячейку A23.
Ü Введите: Количество дней в месяце.
Ü Курсор установите на ячейку A24.
Ü Введите не обращались Студенты.
Ü Курсор установите на ячейку E24.
Ü Вызовите функцию СЧИТАТЬПУСТОТЫ.
Ü В поле «Диапазон» введите: B6:AF6.
O Произведите расчет «Количество дней в месяце, когда не было обращений Семейных» и «Количество дней в месяце, когда не было обращений Одиноких». Результаты разместите в ячейках E25, E26.
|
3) Рассчитайте Среднее количество обращений за день.
Используя функцию СРЗНАЧА, выполните следующие действия:
Ü Курсор установите на ячейку I15.
Ü Введите: Среднее количество в день.
Ü Курсор установите на ячейку I16.
Ü Введите обращений.
Ü Курсор установите на ячейку S16.
Ü Вызовите функцию СРЗНАЧА.
Ü В поле «Значение1» введите: B5:AF5.
O Произведите расчет «Среднеезначение количествапроданных путевок в месяц».
|
4) Рассчитайте Максимальное количество обращений в день.
Используя функцию МАКС, выполните следующие действия:
Ü Курсор установите на ячейку H19.
Ü Введите: Максимальное количество в день.
Ü Курсор установите на ячейку Q20.
Ü Введите обращений.
Ü Курсор установите на ячейку S20.
Ü Вызовите функцию МАКС.
Ü В поле «Число1» введите: B5:AF5.
O Произведите расчет «Максимальное значение количествапроданных путевок в день».
|
5) Рассчитайте Минимальное количество обращений в день.
Используя функцию МИН, выполните следующие действия:
Ü Курсор установите на ячейку U19.
Ü Введите: Минимальное количество в день.
Ü Курсор установите на ячейку AC20.
Ü Введите обращений.
Ü Курсор установите на ячейку AE20.
Ü Вызовите функцию МИН.
Ü В поле «Число1» введите: B5:AF5.
O Произведите расчет «Минимальное значение количествапроданных путевок в день».
|
6) Определите, какое место занимают Студенты по количеству обращений.
Используя функцию РАНГ, выполните следующие действия:
Ü Рассчитайте количество обращений Студентов, Семейных, Одиноких за месяц и разместите их в ячейки AG6, AG7, AG8.
Ü Курсор установите на ячейку Q23
Ü Введите: Место по количеству обращений.
Ü Курсор установите на ячейкуQ24.
Ü Введите обращений
Ü Курсор установите на ячейку S24
Ü Вызовите функцию РАНГ.
Ü В поле «Число» введите: AG6.
Ü В поле «Ссылка» введите: AG6:AG8.
ÜВ поле «Порядок» введите: 0 (указывает, что расположение по возрастанию, 1 – по убыванию).
O Определите, какие места занимают Семейные, Одинокие, по количеству обращений.
|
O Отформатируйте обработанную таблицу в соответствии с видом:
|
6.2 Режимы расчета в таблицах
Задание №5. Режимы расчета в таблицах
В программе Excel существуют два режима расчета в таблице:
1) Автоматический – если изменить исходные данные, то перерасчет таблицы производится автоматически.
2) Ручной - если изменить исходные данные, то для перерасчета таблицы необходимо нажать клавишу <F9>.
Для установки режима расчета необходимо выполнить следующие шаги:
1 шаг «Сервис» → «Параметры».
2 шаг Установить панель «Вычисления» → установить признак расчета таблицы.
Задания для самостоятельной работы:
1. Создайте электронную таблицу «Расчет стоимости семейной путевки маршрута “Злата Прага”».
а) Произведите расчет в электронной таблице:
Наименование параметров
| Описание
| Скидки
| 10% на проживание, если более 4 взрослых и только для отеля 5 звезд (*****)
| Цена проживания
| Один взрослый турист – в соответствии с тарифами, один ребенок на двоих взрослых – бесплатно, для других детей – 50% от тарифа
| Цена перелета
| Один взрослый – в соответствии с тарифами, каждый ребенок – 50% от тарифа
| Базовая цена путевки
| Цена проживания с учетом скидки + Цена перелета
| Оформление визы
| $60 за взрослого и $40 за каждого ребенка
| Трансферт
| $100 на путевку
| Экскурсии
| $80 за взрослого и $40 за каждого ребенка
| Общая цена путевки
| Базовая цена путевки + Оформление визы + Трансферт + Экскурсии
|
б) Произведите краткий анализ по следующим параметрам:
· Количество проданных путевок с 5-, 4-, 3-звездными классами проживания.
· Количество проданных путевок с 1, 2, 3-м классами перелета.
· Последовательность распределения покупок путевок по классам проживания и по классам перелета.
· Общая сумма стоимости путевок:
с 5-звездным классов проживания;
с 4-звездным классов проживания;
с 3-звездным классов проживания;
· Количество путевок со скидкой на проживание, и на какую сумму.
· Количество путевок с экскурсиями.
в) Постройте графики:
· Клиенты – класс проживания.
· Клиенты – класс перелета.
При расчете таблицы рекомендуется использовать функции: СУММ, ГПР, СЧЕТЕСЛИ, СУММЕСЛИ, ЕСЛИ, И, ИЛИ, СЧЕТЗН, РАНГ.
2. Создайте таблицу «Расчет стипендии»:
а) Произведите расчет в электронной таблице:
Наименование
| Описание
| Средний балл
| (Оценка по математике + оценка по информатике + оценка по русскому) / 3, если студент сдал все экзамены и зачеты
| 0, если студент не сдал какой-либо экзамен или зачет
| Стипендия
| Коэффициент (в зависимости от среднего балла) * минимальную оплату
| Средний балл по предмету
| Сумма всех оценок студентов по предмету / на количество студентов
| Сдано
| Подсчитать количество сдавших студентов по каждому предмету
| Сумма стипендий на группу
| Сумма стипендий всех студентов
| На группу №100
| Сумма стипендий всех студентов только группы №100
| На группу №101
| Сумма стипендий всех студентов только группы №101
|
При расчете таблицы рекомендуется использовать функции: ЕСЛИ, ИЛИ, СУММ, СРЗНАЧ, СЧЕТЕСЛИ, Оператор &, ВПР.
Не нашли, что искали? Воспользуйтесь поиском по сайту:
©2015 - 2024 stydopedia.ru Все материалы защищены законодательством РФ.
|