Завершаем оформление расчетной таблицы.




1) выделяем мышкой интервал клеток C13:L13;

2) выполняем дополнительные настройки

Формат, Ячейки, Граница, Линия, тип линии: ___, Все: внешние, внутренние, Цвет: в появившемся меню цветов выбираем цвет окраски суммарных значений финансовых показателей, например, красный цвет, Ok.

 

1.2. ПОСТРОЕНИЕ ДИАГРАММ И ГРАФИКОВ

 

Построим круговую диаграмму, показывающую доли общей прибыли школы по месяцам:

1) выделяем курсором мышки интервал клеток C5:C12 и затем, придерживая нажатой клавишу Ctrl, выделяем дополнительно интервал клеток F5:F12;

2) включаем "Мастер диаграмм":

Вставка, Диаграмма, (шаг 1 из 4), Стандартные: Круговая, Вид: Круговая диаграмма, Далее, (шаг 2 из 4): Диапазон данных, Диапазон: =Расчетная таблица!$C$5:$C$12; Расчетная таблица!$!$F$5: $F$12, Ряды в: столбцах, Далее, (шаг 3 из 4): Заголовки, Название диаграммы: Общая прибыль, Легенда, Добавить легенду, Размещение: Справа, Подписи данных, Подписи значений: доля, Ключ легенды, Линии выноски, Далее, (шаг 4 из 4): Поместить диаграмму на листе: имеющемся: Расчетная таблица!$, Готово.

На листе Расчетная таблица появляется изображение круговой диаграммы (рис. 1.1).

 

Корректируем отображение данных в диаграмме:

1) устанавливаем курсор мышки в области созданной диаграммы и, нажав левую клавишу мышки, перемещаем диаграмму в удобное для рассмотрения место на листе (рис 1.1);

2) зацепив курсором мышки за жирные точки на рамке вокруг диаграммы, изменяем размеры диаграммы.

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

3) далее производим корректировку:

Вид, Рамка: невидимая, Заливка: прозрачная, Шрифт, Шрифт: Arial Cyr, Начертание: полужирный, Размер: 12, Выравнивание, по горизонтали: по центру, по вертикали: по центру, Ok.

Аналогично корректируем изображение сегментов диаграммы:

Первым щелчком левой клавиши мышки выделяем всю область круговой диаграммы, затем вторым щелчком левой клавиши мышки выделяем какой - либо из сегментов диаграммы. Далее, вызвав правой клавишей мышки контекстное меню, выбираем в нем пункт "Формат точки данных".

Далее выполняем настройки в появившемся диалоговом окне этой команды:

Формат элемента данных, Вид, Граница: обычная, Способы заливки, Узор: выбираем узор, соответствующий узору, показанному на рис.1.1, Штриховка: выбираем цвет штриховки, Фон: выбираем цвет фона для сегмента, Ok, Формат элемента данных, Ok.

Захватив левой клавишей мышки выделенный сегмент, выдвигаем его, в случае необходимости, из области диаграммы.

 

Внимание: Для закрепления полученных навыков создайте самостоятельно таблицы и гистограммы, представленные на рисунке 1.5. Формулы, по которым рассчитываются экономические показатели в обычной математической нотации и в нотации MS Excel, указаны в нижнем левом углу рисунка 1.5.

 

1.3. СОХРАНЕНИЕ СОЗДАННОЙ ЭЛЕКТРОННОЙ ТАБЛИЦЫ

 

Промежуточные и окончательные результаты работы по созданию электронной таблицы, графиков и диаграмм необходимо сохранять на диске в виде файла, в предварительно созданной папке, например в папке MS EXCEL по адресу D:\PSN\ФАРИТОВА\

Для этого необходимо выполнить команду:

Файл, Сохранить как, Папка.

На экране появляется диалоговое окно "Сохранение документа". Щелкнем мышкой кнопку-треугольник справа поля "Папка" и в появившемся дереве дисков и папок последовательно открываем - корневой каталог диска D:, затем папку PSN, затемпапку ФАРИТОВА, затем папку MS EXCEL. Таким образом, устанавливается адрес, по которому должны быть записан на диск созданный документ. Затем заносим в поле "Имя файла " название документа: Астрология и нажимаем графическую кнопку Сохранить.

Если папка для размещения документа не была создана заранее, то её можно создать непосредственно перед записью файла на диск, воспользовавшись кнопкой "Создать папку" на линейке инструментов в диалоговом окне команды Сохранить.

Предварительно, перед записью документа на диск, рекомендуется создать для него карточку-ярлычок с краткими сведениями о содержании документа и его авторе. Для этого необходимо воспользоваться командой Файл, Свойства, Документ.

Если каждый сохраняемый Вами на диске файл будет снабжаться таким ярлычком, то это позволит в последующем получать краткую информацию о содержимом файла, не открывая его. Для этого достаточно подвести курсор мышки к иконке файла на рабочем столе MS Windows или щелкнуть правой клавишей мышки на этой иконке и в появившемся контекстном меню выбрать пункт "Свойства". В случае необходимости, сведения, занесённые в карточку "Свойства", можно будет подкорректировать.

 

 

1.4. ИСПОЛЬЗОВАНИЕ MS EXCEL ДЛЯ АНАЛИЗА ЗАВИСИМОСТЕЙ МЕЖДУ ПЕРЕМЕННЫМИ

 

Круговые диаграммы удобны для простого сопоставления значений одного или нескольких показателей между собой.

В том случае, когда требуется исследовать динамику изменения значений показателя во времени или зависимость его значений от другого показателя, используются гистограммы и линейные графики.

В MS Excel имеются встроенные процедуры, которые позволяют достаточно просто аппроксимировать, представленные на диаграммах в виде графиков дискретные значения переменных, непрерывными функциями (линиями тренда), исследовать наличие взаимосвязи между ними, прогнозировать их значения на различные временные периоды.

 

Исследование первое. Используя данные таблицы финансовых показателей школы "Астрология" (табл. 1.1) исследуем тенденции в изменении общей прибыли и зарплаты сотрудников в 1-м полугодии.

Построим гистограмму, изображенную на рисунке 1.2.

 

1) выделяем интервал клеток C5:C12 и, придерживая нажатой клавишу Ctrl, выделяем дополнительно интервал клеток F5:F12;

2) включаем "Мастер диаграмм" и выполняем команду:

Вставка, Диаграмма, (шаг 1 из 4), Стандартные: Гистограмма, Вид: Обычная гистограмма, Далее, (шаг 2 из 4): Диапазон данных, Диапазон: =Расчетная таблица!$C$5:$C$12; Расчетная таблица!$F$5: $F$12, Ряды в: столбцах, Ряд, Ряд: Общая прибыль, Имя: Расчетная таблица!$F$5, Значение: Расчетная таблица!$F$7: $F$12, Подписи оси Х: Расчетная таблица!$C$7: $C$12, Далее, (шаг 3 из 4): Заголовки, Название диаграммы: Динамика изменения общей прибыли, Ось Х(категорий): месяцы, Ось Y(значений): общая прибыль, Подписи данных, Включить в подписи: значения, Легенда, Добавить легенда (убрать флажок), Оси, По основной оси: ось Х(категорий), категории, ось Y(значений), Далее, (шаг 4 из 4): Размещение диаграммы, Поместить диаграмму на листе: имеющемся, лист 2, Готово.

На листе 2 появляется изображение гистограммы (рис. 1.2).

Переименуем ярлычок листа 2 с гистограммой на: анализ графиков.

3) Аппроксимируем гистограмму общей прибыли полиномом 3-го порядка. Для этого щелчком правой клавиши мышки на рисунке с гистограммой выполняем команду контекстного меню:

Добавить линию тренда, Тип: Полиномиальная, Степень: 3, Параметры: Показывать уравнение на диаграмме, Поместить на диаграмму величину достоверности аппроксимации R^2, Прогноз на 1 периодов, Ok

Величина достоверности аппроксимации близкая к единице показывает, что использованное для аппроксимации уравнение достаточно хорошо описывает данные таблицы и может быть использовано для прогноза прибыли на интересующие периоды времени.

Выделяя правой клавишей мышки отдельные элементы гистограммы и используя пункты появляющихся при этом контекстных меню можно диаграмму привести к виду на рисунке 1.3.

 

Исследование второе. Используя данные таблицы финансовых показателей школы "Астрология" (табл. 1.1) исследуем влияние общей прибыли на уровень оплаты труда в 1-м полугодии.

Построим гистограмму, изображенную на рисунке 1.3.

1) выделяем курсором мышки интервал клеток F5:F12 и, придерживая нажатой клавишу Ctrl, выделяем дополнительно интервал клеток L5: L12;

2) включаем "Мастер диаграмм":

Вставка, Диаграмма, (шаг 1 из 4), Стандартные: Гистограмма, Вид: Обычная гистограмма, Далее, (шаг 2 из 4): Диапазон данных, Диапазон: =Расчетная таблица!$F$5: $F$12, Расчетная таблица!$L$5: $L$12, Ряды в: столбцах, Ряд, Ряд: Общая прибыль, Удалить, Ряд: Зарплата, Имя: Расчетная таблица!$L$5, Значение: Расчетная таблица!$L$7: $L$12, Подписи оси Х: Расчетная таблица!$F$7: $F$12, Далее, (шаг 3 из 4): Заголовки, Название диаграммы: Зависимость зарплаты от прибыли, Ось Х(категорий): прибыль, Ось Y(значений): зарплата, Подписи данных, Включить в подписи: значения, Легенда, Добавить легенду (убрать флажок), Оси, По основной оси: ось Х(категорий), категории, ось Y(значений), Далее, (шаг 4 из 4): Поместить диаграмму на листе: имеющемся: Анализ графиков, Готово.

На листе Анализ графиков, появляется гистограмма (рис. 1.3).

Аппроксимируем гистограмму зарплаты полиномом 3-го порядка. Для этого щелчком правой клавиши мышки на рисунке с гистограммой выполняем команду контекстного меню:

Добавить линию тренда, Тип: Полиномиальная, Степень: 3, Параметры: Показывать уравнение на диаграмме, Поместить на диаграмму величину достоверности аппроксимации R^2, Прогноз на 1 периодов, Ok

Величина достоверности аппроксимации близкая к единице показывает, что между прибылью предприятия и зарплатой наблюдается зависимость близкая к функциональной и использованное для аппроксимации уравнение может с достаточной степенью достоверности быть использовано для анализа взаимовлияния этих показателей. Для того, чтобы определить насколько близка эта зависимость к функциональной, нужно вычислить коэффициент корреляции, воспользовавшись для этого библиотекой встроенных функций MS Excel (см. следующий раздел 1.5).

Выделяя правой клавишей мышки отдельные элементы гистограммы и используя пункты появляющихся при этом контекстных меню можно диаграмму привести к виду, изображенному на рисунке 1.4.

 

1.5. ИСПОЛЬЗОВАНИЕ БИБЛИОТЕКИ ВСТРОЕННЫХ ФУНКЦИЙ

 

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

1. Вычислим арифметическую среднюю величину прибыли школы "Астрология" за полугодие и занесем результат в клетку Е53 таблицы 2. Для этого нужно выполнить следующую последовательность действий:

1) выделим мышкой клетку E53, в которую будет занесен результат;

2) далее выполним команду:

Вставка, Функция (fx), Статистические, СРЗНАЧ, Ok, Аргументы функции: Число1: Расчетная таблица!F7:F:12, Ok.

2. Вычислим коэффициент корреляции между показателями прибыли предприятия и зарплатой сотрудников:

1) выделим клетку F55, в которую должно быть занесено значение коэффициента корреляции;

2) далее выполним команду

Вставка, Функция (fx), Статистические, КОРРЕЛ, Ok, Аргументы функции: Массив 1: Расчетная таблица!F7:F12; Массив 2: Расчетная таблица!L7:L12, Ok.

 

Коэффициент корреляции получился равным 1, что указывает на наличие прямой функциональной зависимости между этими показателями. Оно и понятно. Зарплата в этом примере итак вычисляется по заданной формуле, как функция от прибыли предприятия.

Аналогично вычисляются и заносятся в таблицу 1.2 и другие статистические величины показателей школы "Астрология".

 

1.6. РИСУНКИ

 

 

Рисунок 1.1 – Расчетная таблица

 

Рисунок 1.2 – Соотношение показателей общей прибыли по месяцам

 

 

 

Рисунок 1.3 – Динамика изменения общей прибыли

 

 

Рисунок 1.4 – График зависимости оплаты труда от общей прибыли в 1-м полугодии

 

 

 

 

Таблица 1.2 – Результаты статистических расчетов

 

Рисунок 1.5 – Задание на самостоятельную разработку электронных таблиц и диаграмм

 

Лабораторная работа 2

 

Рассмотрим порядок выполнения 2-го варианта создания расчетной таблицы.

Порядок выполнения всех действий здесь также описывается с самого начала (также как и в работе 1), с тем, чтобы студент мог сразу же приступить к выполнению рассматриваемого ниже порядка работы, если он владеет приёмами создания электронных таблиц, рассмотренными в лабораторной работе 1.

 

2.1. НАСТРОЙКА ФОРМАТОВ КЛЕТОК СОЗДАВАЕМОЙ РАСЧЕТНОЙ ТАБЛИЦЫИ ВВОД В НИХ ДАННЫХ

 

Устанавливаем ширину колонок рабочего окна MS Excel приблизительно равной ширине колонок создаваемой таблицы в следующей последовательности (рис. 2.1, табл. 2.1):

1) устанавливаем курсор в строке обозначений колонок листа MS Excel на линии, разделяющей названия двух соседних столбцов, например, С и D, так, чтобы возникла двунаправленная стрелка, и, не отпуская клавишу мышки, изменяем ширину колонки на необходимую величину.

Таким же образом настраиваем ширину остальных колонок рабочего окна, которые будут использоваться при создании электронной таблицы.

 

Настраиваем интервал клеток C7:L7 для размещения в нем названия расчетной таблицы:

1) выделяем мышкой интервал клеток С7:L7;

2) выполняем команду

Формат, Ячейки, Число, Текстовый, Выравнивание, по горизонтали: по центру, по вертикали: по верхнему краю, Отображение: переносить по словам, объединение ячеек, Шрифт: Arial Cyr, Начертание: полужирный, Размер: 12, Ok.

Вводим с клавиатуры в интервал клеток С7:L7 название таблицы: Финансовые показатели работы школы "АСТРОЛОГИЯ".

 

Аналогично настраиваем интервал клеток C8:L8 для размещения в нем подзаголовка Таблица 1 - Показатели 1 - го полугодия 2000 года:

1) выделяем мышкой интервал клеток С8:L8;

2) выполняем команду

Формат, Ячейки, Число, Текстовый, Выравнивание, по горизонтали: по центру, по вертикали: по верхнему краю, Отображение: переносить по словам, объединение ячеек, Шрифт: Arial Cyr, Начертание: полужирный, Размер: 10, Ok.

Вводим с клавиатуры в интервал клеток С8:L8 подзаголовок создаваемой таблицы.

 

Настраиваем интервал клеток H3:H6 для размещения в них названий различных статей отчислений с прибыли:

1) выделяем мышкой интервал клеток H3:H6;

2) выполняем команду

Формат, Ячейки, Число, Текстовый, Выравнивание, По горизонтали: по левому краю, По вертикали: по верхнему краю, Отображение: переносить по словам, Шрифт: Arial Cyr, Начертание: обычный, Цвет: Красный, Размер: 10, Ok.

Вводим с клавиатуры в клетки H3:H6 названия статей отчислений.

 

Настраиваем интервал клеток K3:K6 для размещения в них обозначений (идентификаторов) коэффициентов статей отчислений, которые будут использоваться в расчетных формулах:

1) выделяем мышкой интервал клеток K3:K6;

2) выполняем команду

Формат, Ячейки, Число, Текстовый, Выравнивание, По горизонтали: по правому краю, По вертикали: по верхнему краю, Отображение: переносить по словам, Шрифт: Arial Cyr, Начертание: полужирный, Цвет: черный, Размер: 12, Ok.

Вводим с клавиатуры в выделенные клетки H3:H6, обозначения коэффициентов статей отчислений - kp1, kp2, kp3, kp4.

 

Настраиваем интервал клеток L3:L6 для размещения в них значений коэффициентов статей отчислений, в процентах:

1) выделяем мышкой интервал клеток L3:L6;

2) выполняем команду

Формат, Ячейки, Число, Процентный, Выравнивание, По горизонтали: по правому краю, По вертикали: по верхнему краю, Отображение: переносить по словам, Шрифт: Arial Cyr, Начертание: полужирный, Цвет: красный, Размер: 10, Ok.

Вводим с клавиатуры в выделенные клетки L3:L6, установленные в организации, значения процентов отчислений.

 

Создаем шапку расчетной таблицы:

1) выделяем интервал клеток C9:L9 и устанавливаем в них одинаковые настройки:

Формат, Ячейки, Число, Текстовый, Выравнивание, По горизонтали: по центру, По вертикали: по верхнему краю, Отображение: переносить по словам, Шрифт: Arial Cyr, Начертание: полужирный, Размер: 10, Граница, Линия, Тип линии: ___, Все: внешние, внутренние, Ok.

В клетки шапки таблицы C9:L9 вводим названия показателей: Месяц, Количество слушателей и т.д.

2) Выделяем интервал клеток C10:L10 и устанавливаем в них одинаковые настройки:

Формат, Ячейки, Число, Текстовый, Выравнивание, По горизонтали: по центру, По вертикали: по верхнему краю, Отображение: переносить по словам, Шрифт: Arial Cyr, Начертание: полужирный, Размер: 10, Граница, Линия, Тип линии: ___, Все: внешние, внутренние, Ok.

Вводим в клетки интервала C10:L10 обозначения показателей, которые будут использоваться в расчетных формулах: M, N, S, P и т.д.

 

Настраиваем клетки колонок таблицы для ввода в них исходных для расчета данных:

1)выделяем интервал клеток C11:С17 и устанавливаем следующие настройки:

Формат, Ячейки, Число, Текстовый, Выравнивание, По горизонтали: по левому краю, По вертикали: по верхнему краю, Отображение: переносить по словам, Шрифт: Arial Cyr, Начертание: обычный, Размер: 10, Граница, Линия, Тип линии: ___, Все: внешние, внутренние, Ok.

Вводим в клетки C11:C16 названия месяцев 1-го полугодия.

 

2) Выделяем интервал клеток D11:D17 и устанавливаем следующие настройки:

Формат, Ячейки, Число, Числовой, Число десятичных знаков: 0, Выравнивание, По горизонтали: по левому краю, По вертикали: по верхнему краю, Отображение: переносить по словам, Шрифт: Arial Cyr, Начертание: обычный, Размер: 10, Граница, Линия, Тип линии: ___, Все: внешние, внутренние, Ok.

Вводим в клетки D11:D16 данные по количеству слушателей по месяцам.

 

3) Выделяем интервал клеток E11:E17 и устанавливаем следующие настройки:

Формат, Ячейки, Число, Числовой, Число десятичных знаков: 2, Выравнивание, По горизонтали: по левому краю, По вертикали: по верхнему краю, Отображение: переносить по словам, Шрифт: Arial Cyr, Начертание: обычный, Размер: 10, Граница, Линия, Тип линии: ___, Все: внешние, внутренние, Ok.

Вводим в клетки E11:E16 данные по стоимости обучения слушателей по месяцам.

 



Поделиться:




Поиск по сайту

©2015-2024 poisk-ru.ru
Все права принадлежать их авторам. Данный сайт не претендует на авторства, а предоставляет бесплатное использование.
Дата создания страницы: 2018-01-30 Нарушение авторских прав и Нарушение персональных данных


Поиск по сайту: