Лабораторная работа № 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 Откройте новую Книгу.

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

При расчете таблицы рекомендуется использовать функции: ЕСЛИ, ИЛИ, СУММ, СРЗНАЧ, СЧЕТЕСЛИ, Оператор &, ВПР.

Предыдущая 1 2 3 4 567 8 9 10 Следующая

Не нашли, что искали? Воспользуйтесь поиском по сайту:

©2015 - 2024 stydopedia.ru Все материалы защищены законодательством РФ.