1) выделяем интервал клеток E11:L17 и устанавливаем следующие настройки:
Формат, Ячейки, Число, Числовой, Число десятичных знаков: 2, Выравнивание, По горизонтали: по левому краю, По вертикали: по верхнему краю, Отображение: переносить по словам, Шрифт: Arial Cyr, Начертание: обычный, Размер: 10, Граница, Линия, Тип линии: ___, Все: внешние, внутренние, Ok.
MS Excel позволяет в формулах указывать переменную несколькими способами:
- указав координаты ее размещения на рабочем листе MS Excel (абсолютный или относительный адрес клетки);
- указав её идентификатор (обозначение), которое может предварительно быть присвоено переменной с помощью команды основного меню Вставка, Имя.
В этой работе рассматривается использование второго варианта.
Создадим таблицу обозначений переменных в нотации MS Excel:
1) Выделяем клетку L3 и выполняем команду Вставка, Имя, Присвоить: имя: kp1, Ok.
Правильность выполнения операции контролируем по содержанию строки Формула, в которой отображается адрес клетки или интервала клеток, которым присваивается соответствующее имя: ="Расчетная таблица"!$L$3.
Аналогично присваиваем имена коэффициентам kр2, kр3 и kр4.
2) Выделяем интервал клеток С11:C16 и присваиваем ему обозначение M командой Вставка, Имя, Присвоить, имя: M, Ok.
Аналогично присваиваем интервалам клеток D11:D16 имя N; E11:E16 имя S и т.д. (см. таблицу 2.2).
3) Вводим, используя нотацию MS Excel, в клетки строки E11:L11 формулы вычисления показателей в следующей последовательности:
Вводим в клетку F11 формулу =N*S, используя обозначения в созданной таблице имен (табл. 2).
Аналогичным образом вводим в клетку G11 формулу =kр1*P; в клетку H11 формулу =kр2*P; в клетку I11 формулу = kр3*P; в клетку J11 формулу =P-U-I-NR; в клетку K11 формулу =kр4*FOT; и в клетку L11 формулу
=FOT - nalog.
Копируем формулы, размещенные в клетках F11:L11, в клетки строк интервала F12:L16, в следующей последовательности:
1) выделяем мышкой интервал клеток F11:L16;
2) выполняем команду Правка, Заполнить, Вниз.
В результате этих действий, формулы из клеток F11:L11, расположенных в первой строке выделенного интервала F11:L16, будут скопированы построчно в клетки интервала F12:L16.
Обратите внимание на то, что клетки, откуда копируются формулы (или данные любого другого вида), обязательно должны находиться в начале интервала, выделенного для выполнения операции копирования.
Вводим в клетки в интервале D13:L13 формулы для вычисления суммарных значений показателей:
1) вводим в клетку D17 формулу =СУММ(S).
2) копируем эту формулу в клетки интервала E17:L17. Для этого выделяем интервал клеток D17:L17 и выполняем команду Правка, Заполнить, Вправо.
В результате формула, занесенная ранее в клетку D17, будет скопирована во все другие клетки этого интервала D17:L17. При этом координаты колонок изменятся в соответствии с координатами столбцов, где они будут размещены.
Примечание: Стандартную функцию СУММ() из библиотеки MS Excel можно вставлять в формулы несколькими способами.
1-й способ: в виде обычного текста непосредственно с клавиатуры вводим название функции и в круглых скобках список её параметров, например СУММ(D11:D16). Эта функция обеспечит вычисление суммы всех чисел, расположенных в клетках с D11 по D16;
2-й способ: Задействуем "Мастер функций"командой Вставка, Функция или нажав кнопку fx на инструментальной линейке "Стандартная". В появившемся диалоговом окне "Мастера функций" выбираем Категория: Математические, Функция: СУММ, Ok. Ввод параметров функции в поле Число1 в окне функции СУММ() можно осуществить, напечатав их непосредственно с клавиатуры, или же, что более профессионально, отодвинув в сторону или свернув на время диалоговое окно функции СУММ() красной кнопочкой справа от поля Число1, выделить мышкой клетки D11:D16 непосредственно в расчетной таблице.
3-й способ: Выделяем мышкой клетки D11:D16 и нажимаем на линейке инструментов "Стандартные" кнопку å (Автосумма).
Завершаем разработку расчетной таблицы:
1) выделяем мышкой интервал клеток C17:L17.
2) выполняем дополнительные настройки
Формат, Ячейки, Граница, Линия, тип линии: ___, Все: внешние, внутренние, Цвет: в появившемся меню цветов выбираем цвет окраски суммарных значений финансовых показателей, например, красный цвет, Ok.
2.2. ПОСТРОЕНИЕ ДИАГРАММ И ГРАФИКОВ
Построим круговую диаграмму, показывающую соотношение общей прибыли школы по месяцам (рис. 2.2):
1) выполняем команду:
Вставка, Диаграмма, (шаг 1 из 4), Стандартные: Круговая, Вид: Круговая диаграмма, Далее, (шаг 2 из 4): Диапазон данных, Диапазон: =M;P, Ряды в: столбцах, Ряд: Ряд1, Имя: Расчетная таблица! $F$9, Значения: Расчетная таблица! $F$11:$F$16, Подписи категорий, Расчетная таблица! $С$11:$С$16, Далее, (шаг 3 из 4): Заголовки, Название диаграммы: Общая прибыль, Легенда, Добавить легенду, Размещение: Справа, Подписи данных, Включить в подписи: доли, Ключ легенды, Линии выноски, Далее, (шаг 4 из 4): Поместить диаграмму на листе: имеющемся: Расчетная таблица, Готово.
На листе Расчетная таблица появляется изображение круговой диаграммы.
Корректируем отображение данных в диаграмме.
1) Устанавливаем курсор мышки в области созданной диаграммы и, нажав левую клавишу мышки, перемещаем диаграмму в удобное для рассмотрения место на листе.
2) Зацепив курсором мышки за жирные точки на рамке вокруг диаграммы, изменяем размеры диаграммы. Таким же приемом, выделив левой клавишей мышки любой элемент диаграммы и вызвав правой клавишей контекстное меню, соответствующее этому элементу, можно произвести дополнительную корректировку каждого из них. Например, щелчком левой клавишей мышки выделяем заголовок диаграммы "Общая прибыль", затем щелчком правой клавиши мышки вызываем контекстное меню и в нем выбираем пункт "Формат заголовка диаграммы". Далее производим корректировку:
Вид, Рамка: невидимая, Заливка: прозрачная, Шрифт, Шрифт: Arial Cyr, Начертание: полужирный, Размер: 12, Выравнивание, по горизонтали: по центру, по вертикали: по центру, Ok.
Аналогично корректируем изображение сегментов диаграммы. Первым щелчком левой клавиши мышки выделяем всю область круговой диаграммы, затем вторым щелчком левой клавиши мышки выделяем какой - либо из сегментов диаграммы. Далее, вызвав правой клавишей мышки контекстное меню, выбираем в нем пункт "Формат точки данных". Далее выполняем настройки в появившемся диалоговом окне этой команды:
Формат элемента данных, Вид, Граница: обычная, Способы заливки, Узор: выбираем узор, соответствующий узору, показанному на рис.2, Штриховка: выбираем цвет штриховки, Фон: выбираем цвет фона для сегмента, Ok, Формат элемента данных, Ok.
Захватив левой клавишей мышки выделенный сегмент, выдвигаем его, в случае необходимости, из области диаграммы.
Внимание: Для закрепления полученных навыков создайте самостоятельно расчетную таблицу и диаграммы, показанные на рисунке 6. Формулы вычисления показателей расчетной таблицы в математическом виде и в нотации MS Excel приведены на этом же рисунке.
2.3. СОХРАНЕНИЕ СОЗДАННОЙ ЭЛЕКТРОННОЙ ТАБЛИЦЫ
Промежуточные и окончательные результаты работы по созданию электронной таблицы, графиков и диаграмм необходимо сохранять на диске в виде файла, в предварительно созданной папке, например в каталоге MS EXCEL по адресу D:\PSN\CИДОРОВ\
Для этого необходимо выполнить команду:
Файл, Сохранить как, Папка.
На экране появляется диалоговое окно "Сохранение документа". Щелкнем мышкой кнопку-треугольник справа поля "Папка" и в появившемся дереве дисков и папок последовательно открываем - корневой каталог диска D:, затем папку PSN, затемпапку СИДОРОВ, затем папку MS EXCEL. Таким образом, устанавливается адрес, по которому должны быть записаны на диск созданные таблицы и диаграммы. Затем заносим в поле "Имя файла " название документа: Астрология и нажимаем графическую кнопку Сохранить.
Если папка для размещения документа не была создана заранее, то её можно создать непосредственно перед записью документа, воспользовавшись кнопкой "Создать папку" на линейке инструментов открытой папки.
Предварительно, перед записью документа на диск рекомендуется создать для него карточку-ярлычок с краткими сведениями о содержании документа и его авторе. Для этого необходимо воспользоваться командой Файл, Свойства, Документ. Если каждый сохраняемый на диске файл будет снабжаться таким ярлычком, то это позволит в последующем получать краткую информацию о содержимом файла, не открывая его. Для этого достаточно будет подвести курсор мышки к иконке этого файла на рабочем столе MS Windows или щелкнуть правой клавишей мышки на этой иконке и в появившемся контекстном меню выбрать пункт "Свойства". В случае необходимости, сведения, занесённые в карточку "Свойства", можно будет здесь же подкорректировать.
2.4. ИСПОЛЬЗОВАНИЕ MS EXCEL ДЛЯ АНАЛИЗА ЗАВИСИМОСТЕЙ МЕЖДУ ПЕРЕМЕННЫМИ
Круговые диаграммы удобны для простого сопоставления значений одного или нескольких показателей между собой.
В том случае, когда требуется исследовать динамику изменения значений показателя во времени или зависимость его значений от значений другого показателя, используются гистограммы и графики.
В MS Excel имеются встроенные функции, которые позволяют достаточно просто аппроксимировать представленные в виде графиков табличные значения показателей непрерывными функциями (линиями тренда), исследовать с помощью этих функций влияние одних показателей на другие или делать прогнозы значений анализируемых показателей на различные временные периоды.
Исследование первое. Используя данные таблицы финансовых показателей школы "Астрология" (табл. 2.1) исследуем тенденции в изменении общей прибыли и зарплаты в 1-м полугодии.
Построим гистограммы, изображенные на рисунке 2.3:
1) выполняем команду
Вставка, Диаграмма, (шаг 1 из 4), Стандартные: Гистограмма, Вид: Обычная гистограмма, Далее, (шаг 2 из 4): Диапазон данных, Диапазон: =Расчетная таблица! M;P;З, Ряды в: столбцах, Ряд, Ряд1, Имя: Расчетная таблица! $F$9, Значение: Расчетная таблица! $F$11: $F$16, Ряд2: Имя: Расчетная таблица! $L$9, Значение: Расчетная таблица! $L$11: $L$16, Подписи оси Х: Расчетная таблица! $C$11: $C$16, Далее, (шаг 3 из 4): Заголовки, Название диаграммы: Общая прибыль и зарплата, Ось Х (категорий): месяцы, Ось Y(значений): Показатели, Подписи данных, Включить в подписи: значения, Легенда: Добавить легенду, Оси, По основной оси: ось Х(категорий), автоматическая, ось Y(значений), Далее, (шаг 4 из 4): Размещение диаграммы, Поместить диаграмму на листе: Имеющемся, Расчетная таблица, Готово.
На листе Расчетная таблица появляется изображение гистограмм общей прибыли и зарплаты сотрудников.
2) аппроксимируем гистограмму общей прибыли полиномом 3-го порядка. Для этого щелчком правой клавиши мышки на гистограмме выполняем команду контекстного меню:
Добавить линию тренда, Тип: Полиномиальная, Степень: 3, Параметры: Показывать уравнение на диаграмме, Поместить на диаграмму величину достоверности аппроксимации (R^2), Прогноз на 1 периодов, Ok
Близость к единице величины достоверности аппроксимации показывает, что использованное для аппроксимации уравнение достаточно хорошо описывает данные таблицы и может быть использовано для прогноза прибыли на интересующие периоды времени.
Выделяя правой клавишей мышки отдельные элементы гистограмм и окна с аппроксимирующей функцией и, используя опции появляющихся при этом контекстных меню, можно диаграммы привести к виду, изображенному на рисунке 3.
Исследование второе. Используя данные таблицы 1, исследуем зависимость уровня оплаты труда от общей прибыли в 1-м полугодии 2000 г.
Построим гистограмму, изображенную на рисунке 2.4.
1) выполняем команду:
Вставка, Диаграмма, (шаг 1 из 4), Стандартные: Гистограмма, Вид: Обычная гистограмма, Далее, (шаг 2 из 4): Диапазон данных, Диапазон: =Расчетная таблица! P;З, Ряды в: столбцах, Ряд, Ряд1, Имя: Расчетная таблица! $L$9, Значение: Расчетная таблица! $L$11: $L$16, Подписи оси Х: Расчетная таблица! $F$11: $F$16, Далее, (шаг 3 из 4): Заголовки, Название диаграммы: Общая прибыль и зарплата, Ось Х (категорий): месяцы, Ось Y(значений): показатели, Подписи данных, Включить в подписи: значения, Легенда: Добавить легенду, Оси, По основной оси: ось Х(категорий), автоматическая, ось Y(значений), Линии сетки: Ось Y (значений): Основные линии, Далее, (шаг 4 из 4): Размещение диаграммы: Поместить диаграмму на листе: Имеющемся: Анализ графиков, Готово.
На листе Анализ графиков, появляется изображение гистограммы (рис. 4)
2) аппроксимируем гистограмму зарплаты полиномом 3-го порядка. Для этого щелчком правой клавиши мышки на рисунке с гистограммой выполняем команду контекстного меню:
Добавить линию тренда, Тип: Полиномиальная, Степень: 3, Параметры: Показывать уравнение на диаграмме, Поместить на диаграмму величину достоверности аппроксимации (R^2), Прогноз на 1 периодов, Ok
Величина достоверности аппроксимации R^2 близкая к единице показывает, что между прибылью предприятия и зарплатой наблюдается зависимость близкая к функциональной и использованное для аппроксимации уравнение может с достаточной степенью достоверности быть использовано для анализа взаимовлияния этих показателей. Для того, чтобы определить насколько близка эта зависимость к функциональной нужно вычислить коэффициент корреляции, воспользовавшись библиотекой встроенных функций MS Excel (см. следующий раздел 5).
Выделяя правой клавишей мышки отдельные элементы гистограммы и используя пункты появляющихся при этом контекстных меню можно диаграмму привести к виду, изображенному на рисунке 4.
2.5. ИСПОЛЬЗОВАНИЕ БИБЛИОТЕКИ ВСТРОЕННЫХ ФУНКЦИЙ
Рассмотрим работу с библиотекой встроенных функций MS Excel на примере использования её для вычисления статистик в таблице 2.3.
1. Вычислим среднеарифметическую величину прибыли школы "Астрология" за полугодие (табл.2.1) и занесем результат в клетку F29 таблицы 3 на листе Анализ графиков. Для этого нужно выполнить следующую последовательность действий:
1) выделим клетку F29 таблицы 2.3 на листе Анализ графиков, в
которую должен быть размещен результат вычисления;
2) выполним команду
Вставка, Функция (fx), Статистические, СРЗНАЧ, Ok, Аргументы функции: Число1: Р, Ok
2. Вычислим коэффициент корреляции между показателями прибыли предприятия и зарплатой сотрудников:
Выделим клетку G31 таблицы 2.3 на листе Анализ графиков в которую должно быть размещено вычисленное значение коэффициента корреляции;
Далее выполним команду
Вставка, Функция (fx), Статистические, КОРРЕЛ, Ok, Аргументы функции: Массив1: Р; Массив2: З, Ok.
Коэффициент корреляции получился равным 1, что указывает на наличие функциональной зависимости между этими показателями. Оно и понятно. Зарплата в этом примере вычисляется по заданной формуле, как функция от прибыли предприятия.
Аналогично можно вычислить и занести в таблицу 2.3 и другие статистические величины показателей школы "Астрология".
Используя инструменты оформления можно таблицу 2.3 привести к виду показанному на листе Анализ графиков.
2.6. РИСУНКИ
Рисунок 2.1 – Расчетная таблица
Рисунок 2.2 – Соотношение показателей общей прибыли по месяцам (в долях)
Рисунок 2.3 – Динамика изменения общей прибыли и зарплаты по месяцам
Таблица 2.2 Таблица обозначений переменных
Список литературы
1. Информатика. Базовый курс. Учебник для вузов. Под редакцией С.В. Симоновича. - СПб: Питер, 2007
2. Умергалин Т.Г., Мухамадеев И.Г., Писаренко Э.В. Задания для практических и лабораторных занятий по информатике. Методические указания.– УГНТУ, Уфа.– 2002.– 47 с.