2015-05-10
1285
| Функция | Действие |
| СРЗНАЧ (число 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 |
При расчете таблицы рекомендуется использовать функции: ЕСЛИ, ИЛИ, СУММ, СРЗНАЧ, СЧЕТЕСЛИ, Оператор &, ВПР.
