1.5. Использование библиотеки встроенных функций
Рассмотрим работу с библиотекой встроенных функций MS Excel на примере использования её для вычисления статистик в таблице 1.2.
1. Вычислим арифметическую среднюю величину прибыли школы "Астрология" за полугодие и занесем результат в клетку Е53 таблицы 2. Для этого нужно выполнить следующую последовательность действий:
1) выделим мышкой клетку E53, в которую должен быть занесен результат вычисления средней величины прибыли;
2) далее выполним команду:
Формулы, Вставить функцию (fx), Категория: Статистические, СРЗНАЧ, Ok, Аргументы функции: Число1: Расчет. табл. 1!F7:F:12, Ok.
2. Вычислим коэффициент корреляции между показателями прибыли предприятия и зарплатой сотрудников:
1) выделим клетку F55, в которую должно быть занесено значение коэффициента корреляции;
2) далее выполним команду
Формулы, Вставить функцию (fx), Категория: Статистические, КОРРЕЛ, Ok, Аргументы функции: Массив 1: Расчет. табл. 1!F7:F12; Массив 2: Расчет. табл. 1!L7:L12, Ok.
Коэффициент корреляции получился равным 1, что указывает на наличие прямой функциональной зависимости между этими показателями. Оно и понятно. Зарплата в этом примере итак вычисляется по заданной формуле, как функция от прибыли предприятия.
Аналогично вычисляются и заносятся в таблицу 1.2 и другие статистические величины показателей школы "Астрология".
Рисунки
Рисунок 1.1 – Расчетная таблица
Рисунок 1.2 – Соотношение показателей общей прибыли по месяцам
Рисунок 1.3 – Динамика изменения общей прибыли по месяцам
Рисунок 1.4 – График зависимости оплаты труда от общей прибыли в 1-м полугодии
Таблица 1.2 – Результаты статистических расчетов
Рисунок 1.5 – Задание на самостоятельную разработку электронных таблиц и диаграмм
Лабораторная работа 2
В этой работе рассматривается вариант создания электронных таблиц, где в формулах переменные задаются их именами (идентификаторами, обозначениями).
Порядок выполнения всех действий здесь также описывается с самого начала (также как и в работе 1), с тем, чтобы обучающийся мог, минуя выполнение лабораторной работы 1, сразу же приступить к освоению рассматриваемой ниже технологии решения прикладной задачи.
2.1. Настройка форматов клеток создаваемой расчетной таблицы и ввод в них данных
Устанавливаем ширину колонок рабочего окна MS Excel приблизительно равной ширине колонок создаваемой таблицы (рис. 2.1, табл. 2.1):
1) устанавливаем курсор в строке обозначений колонок листа MS Excel на линии, разделяющей названия двух соседних столбцов, например, С и D, так, чтобы возникла двунаправленная стрелка, и, не отпуская клавишу мышки, изменяем ширину колонки на необходимую величину (табл. 2.1).
Таким же образом настраиваем ширину остальных колонок рабочего окна, которые будут использоваться при создании электронной таблицы.
Настраиваем интервал клеток C7:L7 для размещения в нем названия расчетной таблицы:
1) выделяем мышкой интервал клеток С7:L7;
2) выполняем команду правой клавиши мышки
Формат ячеек, Число: Текстовый, Выравнивание, по горизонтали: по центру, по вертикали: по верхнему краю, Отображение: переносить по словам, объединение ячеек, Шрифт: Arial, Начертание: полужирный, Размер: 12, Ok.
Вводим с клавиатуры в интервал клеток С7:L7 название таблицы: Финансовые показатели работы школы "АСТРОЛОГИЯ".
Аналогично настраиваем интервал клеток C8:L8 для размещения в нем подзаголовка Таблица 1 - Показатели 1 - го полугодия 2011 года:
1) выделяем мышкой интервал клеток С8:L8;
2) выполняем команду правой клавиши мышки
Формат ячеек, Число: Текстовый, Выравнивание, по горизонтали: по центру, по вертикали: по верхнему краю, Отображение: переносить по словам, объединение ячеек, Шрифт: Arial, Начертание: полужирный, Размер: 10, Ok.
Вводим с клавиатуры в интервал клеток С8:L8 подзаголовок создаваемой таблицы.
Настраиваем интервал клеток H3:H6 для размещения в них названий различных статей отчислений с прибыли:
1) выделяем мышкой интервал клеток H3:H6;
2) выполняем команду
Формат ячеек, Число, Текстовый, Выравнивание, по горизонтали: по левому краю, по вертикали: по верхнему краю, Отображение: переносить по словам, Шрифт: Arial, Начертание: обычный, Цвет: черный, Размер: 10, Ok.
Вводим с клавиатуры в клетки H3:H6 названия статей отчислений.
Настраиваем интервал клеток K3:K6 для размещения в них обозначений (идентификаторов) коэффициентов статей отчислений, которые будут использоваться в электронных формулах:
1) выделяем мышкой интервал клеток K3:K6;
2) выполняем команду
Формат ячеек, Число, Текстовый, Выравнивание, по горизонтали: по правому краю, по вертикали: по верхнему краю, Отображение: переносить по словам, Шрифт: Arial, Начертание: полужирный, Цвет: черный, Размер: 12, Ok.
Вводим с клавиатуры в выделенные клетки H3:H6, обозначения коэффициентов статей отчислений kk1, kk2, kk3, kk4.
Настраиваем интервал клеток L3:L6 для размещения в них значений коэффициентов статей отчислений, в процентах:
1) выделяем мышкой интервал клеток L3:L6;
2) выполняем команду
Формат ячеек, Число, Процентный, Число десятичных знаков: 2, Выравнивание, по горизонтали: по правому краю, по вертикали: по верхнему краю, Отображение: переносить по словам, Шрифт: Arial Cyr, Начертание: полужирный, Цвет: красный, Размер: 10, Ok.
Вводим с клавиатуры в выделенные клетки L3:L6, проценты отчислений, принятые в организации.
Создаем шапку электронной таблицы:
1) выделяем интервал клеток C9:L9 и устанавливаем в них одинаковые настройки:
Ф ормат ячеек, Число: Текстовый, Выравнивание, по горизонтали: по центру, по вертикали: по верхнему краю, Отображение: переносить по словам, Шрифт: Arial, Начертание: полужирный, Размер: 10, Граница, Линия, Тип линии: ___, Все: внешние, внутренние, Ok;
В клетки шапки таблицы C9:L9 вводим названия показателей: Месяц, Количество курсантов и т.д.
2) Выделяем интервал клеток C10:L10 и устанавливаем в них одинаковые настройки:
Формат ячеек, Число: Текстовый, Выравнивание, по горизонтали: по центру, по вертикали: по верхнему краю, Отображение: переносить по словам, Шрифт: Arial, Начертание: полужирный, Размер: 10, Граница, Линия, Тип линии: ___, Все: внешние, внутренние, Ok.
Вводим в клетки интервала C10:L10 идентификаторы показателей, которые будут использоваться в формулах: M, N, S, P и т.д.
Настраиваем клетки колонок таблицы для ввода в них исходных данных для расчета и расчетных формул:
1) выделяем интервал клеток C11:С17 и устанавливаем следующие настройки:
Формат ячеек, Число, Текстовый, Выравнивание, по горизонтали: по левому краю, по вертикали: по верхнему краю, Отображение: переносить по словам, Шрифт: Arial, Начертание: обычный, Размер: 10, Граница, Линия, Тип линии: ___, Все: внешние, внутренние, Ok.
Вводим в клетки C11:C16 названия месяцев 1-го полугодия:
- вводим в клетку С11 слово Январь;
- выделяем клетки С11:С16 и выполняем команду
Главная, Заполнить, Прогрессия: Расположение: по столбцам, Тип: даты, Единицы: месяц, Шаг: 1, Ok.
2) Выделяем интервал клеток D11:D17 и устанавливаем следующие настройки:
Формат ячеек, Число, Числовой, Число десятичных знаков: 0, Выравнивание, по горизонтали: по левому краю, по вертикали: по верхнему краю, Отображение: переносить по словам, Шрифт: Arial, Начертание: обычный, Размер: 10, Граница, Линия, Тип линии: ___, Все: внешние, внутренние, Ok.
Вводим в клетки D11:D16 данные по количеству слушателей в каждом месяце (табл. 2.1).
3) выделяем интервал клеток E11:L16 и устанавливаем следующие настройки:
Формат ячеек, Число, Числовой, Число десятичных знаков: 2, Выравнивание, по горизонтали: по левому краю, По вертикали: по верхнему краю, Отображение: переносить по словам, Шрифт: Arial, Начертание: обычный, Размер: 10, Граница, Линия, Тип линии: ___, Все: внешние, внутренние, Ok.
Вводим в клетки E7:E12 данные по стоимости обучения (табл. 2.1).