Задание 11. Расположение исходных данных в таблице

В таблице, по которой были построены диаграммы в предыдущих заданиях, данные для разных кварталов (в терминологии Excel – это «категории») расположены «по строкам». Это совершенно необязательно – таблица может и быть такой:

В ней те же данные расположены «по столбцам».

В большинстве случаев Excel «правильно поймёт», где расположены данные. Воспроизведите эту таблицу на рабочем листе. Для этого можно выделить «старую» таблицу, дать команду Копировать, выбрать ячейку на рабочем листе и дать команду Специальная вставка, в окне которой надо установить флажок Транспонировать.

Постройте по данным этой «новой» таблицы диаграмму типа Гистограмма с группировкой

и вы увидите, что она выглядит точно так же (как на рис.1).

Но если вам необходимо использовать в качестве Категорий (подписей по оси абсцисс) слова Объём выпуска и Прибыль, а обозначения кварталов использовать в качестве параметра (в Легенде диаграммы), то построенную диаграмму можно отредактировать. Для этого надо щёлкнуть в Области диаграммы правой кнопкой мыши, а в контекстном меню выбрать команду Выбрать данные… Появится показанное на рис.7 окно Выбор источника данных.

Рис.7

Нажмите показанную на этом рисунке кнопку Строка/столбец (а далее Ok) и диаграмма изменится (показано на рис.8).

Рис.8

На этой диаграмме «поменялись местами» категории и параметры (отображаемые в легенде).

Задание 12. Редактирование диаграмм (1)

Создайте новый рабочий лист с именем Задание. Воспроизведите на нём показанную ниже таблицу (Год – Объём производства). Эту таблицу вы можете не создавать в своей рабочей книге, а скопировать из файла Данные_Для_Практикума_Excel2007.xlsx (рабочий лист).

Постройте по данным этой таблицы диаграмму типа Гистограмма с группировкой.

Выполнив описанные в Задании 1 три простых действия для построения диаграммы, вы получите «нечто», показанное на рис.9. Формально это «диаграмма», но абсолютно бесполезная и почти бессмысленная, так как не выполняет свою задачу – наглядно показать, как изменялся объём производства в зависимости от года.

Рис.9

В данном случае Excel «не понял», что на гистограмме должен быть отображён только один ряд данных (Объём производства), а данные из столбца Год должны быть подписями по оси абсцисс (категориями). Для того чтобы диаграмма выполняла своё предназначение, её надо отредактировать.

Первое, что надо сделать – удалить ряд Год с диаграммы (выделить, а далее клавиша

Delete на клавиатуре).

Второе – вызвать показанное на рис.7 окно Выбор источника данных (см. предыдущее задание). В правой части этого окна (в поле под слова Подписи горизонтальной оси (категории)) нажать кнопку Изменить. Появится показанное на рис.10 окно Подписи оси.

Рис.10

В ссылочное поле Диапазон подписей оси: введите (мышью!) диапазон ячеек рабочего листа, содержащий значения Года (начиная с ячейки, где первое число: 1990). Нажмите Ok для закрытия окон и вы получите показанную на рис.11 диаграмму.

Рис.11

Построенная диаграмма уже наглядно показывает, как изменялся объём производства в зависимости от года. (Разумеется, что вы можете её отформатировать).

Примечание. Почему при построении первой диаграммы (рис.1) Excel правильно определил категории и ряды данных, а в последнем примере неправильно? Дело в том, что в первом примере для обозначения кварталов использованы буквы (тип данных – «текст»), а в

последнем примере тип данных в столбце Год – «число». Excel «по умолчанию» использует в качестве категорий данные типа «текст», а данные типа «число» использует в качестве значений рядов.

Задание 13. Редактирование диаграмм (2)

По данным таблицы из предыдущего задания на том же рабочем листе постройте диаграмму типа График с маркерами. Отредактируйте построенную «по умолчанию» диаграмму так, чтобы она стала «полезной».

Вы построили две таблицы, отображающие одни и те же данные. Какая из них «лучше» - решать вам. Поэтому при использовании в ваших отчётах диаграмм полезно попробовать разные типы диаграмм, тем более что Excel позволяет сделать это достаточно просто.

Задание 14. Редактирование диаграмм (3)

Создайте новый рабочий лист с именем Задание 14. Воспроизведите на нём показанную ниже таблицу Выработка по кварталам. Эту таблицу вы можете не создавать в своей рабочей книге, а скопировать из файла Данные_Для_Практикума_Excel2007.xlsx (рабочий лист).

Выработка по кварталам

Постройте по данным этой таблицы диаграмму типа Гистограмма с группировкой.

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

В этом примере и названия цехов и обозначения кварталов могут быть категориями

(подписями по оси абсцисс), поэтому постройте оба варианта диаграмм.

Задание 15. Построение и форматирование точечной диаграммы (1)

Создайте новый рабочий лист с именем Задание 15. Воспроизведите на нём таблицу Данные переписей населения в России. Эту таблицу надо скопировать из файла Данные_Для_Практикума_Excel2007.xlsx (рабочий лист).

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

б) Постройте по данным таблицы диаграмму типа Точечная с прямыми отрезками и маркерами, отображающую процентный состав по группам населения в зависимости от года переписи.

Такую диаграмму можно построить двумя способами.

Первый способ. Выделите таблицу с данными. При выделении будьте внимательны: строка таблицы, в которой слова Население (тыс. чел.): не относится к структуре таблицы, поэтому её не надо включать в выделение. Далее на вкладке Вставка в группе Диаграммы выберите соответствующую команду. На построенной «по умолчанию» диаграмме надо удалить три «лишних» ряда – это ряды, соответствующие столбцам, в которых приведены данные не в процентах, а в «тыс. чел.».

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

Построенные диаграммы должны быть одинаковы по структуре (они могут отличаться только форматированием рядов данных).

в) Отформатируйте одну из построенных диаграмм так, чтобы она была похожа на приведённую ниже:

Задание 16. Построение и форматирование точечной диаграммы (2)

Создайте новый рабочий лист с именем Задание. Воспроизведите на нём показанную ниже таблицу. Эту таблицу вы можете не создавать в своей рабочей книге, а скопировать из файла Данные_Для_Практикума_Excel2007.xlsx (рабочий лист).

Постройте по этим данным диаграмму типа Точечная с маркерами. На рис. 12 показан

примерный вид построенной диаграммы.

Рис. 12

Недостатком этой построенной «по умолчанию» диаграммы может оказаться

неразличимость абсцисс точек диаграммы, у которых x =0, 01, 0, 02 и 0, 1 (эти точки

«сливаются»). Заметьте, такая ситуация появляется тогда, когда значения каких-то данных различаются во много раз (в этом примере крайние значения x различаются в 1600 раз!). Для устранения этого недостатка можно отформатировать ось этой величины, применив логарифмический масштаб.

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

Там же установите минимальное значение по этой оси равным 0, 01, а максимальное значение равным 30.

Там же в разделе Вертикальная ось пересекает: установите в поле Значение оси: число

0, 01 (в этом случае ось ординат будет расположена слева).

Отформатируйте построенную диаграмму так, чтобы она была похожа на приведённую ниже:

Рекомендации:

1. На диаграмме нанесены так называемые «линии сетки», которые в некоторых случаях облегчают восприятие данных на диаграмме. Для оси ординат построены только Основные линии сетки (это горизонтальные линии; для построения выделите ось, а далее в контекстном меню команда Добавить основные линии сетки).

Для оси абсцисс построены и основные и промежуточные линии сетки (это вертикальные линии; строятся аналогично).

2. На обеих осях вы видите короткие толстые штрихи (там, где линии сетки пересекаются с осями). Эти штрихи расположены на оси там, где значение величины имеет определённое значение (на оси ординат это: 0, 5, 10 и т.д., а на оси абсцисс это: 0, 01, 0, 02, … 0, 1, 0, 2…). Для добавления этих штрихов: выделить ось ординат и вызвать окно Формат оси, и, выбрав в левой части Параметры оси, в правой части окна выбрать в раскрывающемся списке Основные: значение «внутрь» – будут установлены штрихи в местах пересечения оси с основными линиями сетки.

Аналогично установите основные штрихи для оси абсцисс. Там же находится раскрывающийся список Промежуточные – для оси абсцисс выберите в нём значение

«внутрь».

3. Логарифмическая шкала неравномерная (посмотрите, как расположены деления и штрихи на оси абсцисс), поэтому она и позволяет отображать данные, различающиеся во много раз. Значения для промежуточных линий сетки не отображаются на диаграмме, поэтому полезно запомнить, что для логарифмической шкалы эти значения обычно такие, какие показаны на расположенном ниже рисунке:

Закономерность: каждый промежуточный штрих соответствует значению расположенного слева от него основного штриха, умноженного на 2, на 3, на 4 … и так до 9 включительно.

Задание 17. Построение линии тренда (1)

Создайте новый рабочий лист с именем Задание. Скопируйте на него таблицу с данными о планетах и некоторых астероидах Солнечной системы из файла Данные_Для_Практикума_Excel2007.xlsx (рабочий лист).

Постройте зависимость периода обращения от расстояния от Солнца (диаграмму типа

Точечная с маркерами).

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

Кроме этого видно, что точки, отображающие на диаграмме данные для малых r (для близких к Солнцу планет), «сливаются» и из-за этого вывод о существовании закономерности может оказаться ошибочным. Минимальное и максимальное расстояния от Солнца различаются в 100 раз, а минимальный и максимальный периоды обращения – в тысячу, поэтому рационально применить логарифмический масштаб и к оси абсцисс и к оси ординат.

Примените для построенной диаграммы логарифмический масштаб по обеим осям. Отформатируйте элементы диаграммы, чтобы она выглядела примерно так, как показано на рис. 13.

Рис. 13

Очевидный порядок в расположении точек на диаграмме позволяет надёжно утверждать, что между r и T существует связь. Действительно, эта связь была обнаружена ещё в 1619 году Иоганном Кеплером и позже названа в его честь «третьим законом Кеплера».

В используемых обозначениях третий закон Кеплера может быть записан так:

T=D*r ^3/2 (1)

где: B – постоянная, зависящая от того, в каких единицах измеряются T и r.

С помощью построенной диаграммы вы можете легко проверить справедливость ф-лы (3.1). Для этого надо добавить на диаграмму «линию тренда».

Линией тренда называют какую-то аналитическую (выраженную формулой) зависимость между двумя величинами, хотя бы приближенно представляющую зависимость, заданную в численном (табличном) виде. До недавнего времени чаще использовались русскоязычные эквиваленты: «аппроксимирующая кривая», «аппроксимирующая формула».

Зависимость, выраженная ф-лой (1) – частный случай «степеннОй зависимости»:

T=Bra

(2)

где: B и a – постоянные, T и r – зависимая и независимая переменные соответственно.

Для добавления лини тренда, а линия тренда всегда добавляется к конкретному ряду данных, надо выделить на диаграмме этот ряд данных и в контекстном меню выбрать команду Добавить линию тренда… Появится показанное на рис. 14 окно «Формат линии тренда».

Рис. 14

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

В нижней части окна установите флажки: показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации (R^2). При этих установках Excel не только «нарисует» на диаграмме линию, которая расположена «ближе других» к точкам ряда (линию тренда или аппроксимирующую линию), но и покажет на диаграмме формулу, по которой построена линия тренда (формула почему-то названа уравнением линии тренда).

На диаграмме будет показана величина R 2, называемая в эконометрике коэффициентом детерминации, характеризующая то, насколько близко к точкам ряда расположена линия тренда. Величина R 2 может принимать значения из интервала от 0 до 1. Чем ближе к точкам ряда лежит линия тренда, тем ближе величина R 2 к 1.

На рис. 15 показана построенная диаграмма с добавленной линией тренда, а кроме этого на диаграмме отображаются и формула, по которой построена линия тренда, и величина R 2.

Рис. 15

Excel рассчитал два числа (B и a) в ф-ле (2) исходя из условия, что степенная линия тренда должна проходить «как можно ближе» к точкам ряда. Рассчитанная величина показателя степени a = 1, 499363 получилась очень близкой к величине показателя степени,

предсказываемой третьим законом Кеплера (см. ф-лу (1), в ней показатель равен

), что

может служить подтверждением справедливости закона Кеплера. Величина R 2= 0, 999991 также свидетельствует о том, что полученная степенная зависимость хорошо аппроксимирует данные

наблюдений.

Обратите внимание: вы получили не только наглядное представление данных

(диаграмму), но и проанализировали данные, что позволило сделать полезные выводы.

Задание 18. Построение линии тренда (2)

Создайте новый рабочий лист с именем Задание. Скопируйте на него таблицу Данные переписей населения в США из файла Данные_Для_Практикума_Excel2007.xlsx (рабочий лист).

а) Постройте по данным таблицы диаграмму типа Точечная с маркерами.

б) На диаграмме видно, что в расположении точек ряда есть порядок, а это означает, что есть какая-то закономерность в изменении численности населения в зависимости от времени.

Очевидно, что если построить для этого ряда какую-то «хорошую» линию тренда, то вполне возможно, что появится возможность предсказать численность населения в будущем. Дело в том, что построение линии тренда даёт формулу, а в формулу можно подставить любое значение независимой переменной (год можно подставить и 1971 и 1972 и т.д., т.е. за пределами данных, использованных для построения линии тренда).

Вопрос только один: какую линию тренда выбрать? (Какую функциональную зависимость выбрать?). В предыдущем задании выбор вида линии тренда был вполне однозначным, так как для зависимости T (r) есть модель (третий закон Кеплера), которая даёт степенную зависимость. Когда модели нет, как в рассматриваемом случае, выбор вида линии тренда в значительной степени произволен и при выборе руководствуются различными критериями, а чаще – метод проб и ошибок.

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

В появившемся окне Формат линии тренда (показано выше на рис. 14) надо выбрать Линейная, в текстовом поле вперёд на: установить 30 (при такой установке линия тренда будет проведена на 30 лет дальше, чем год последней точки ряда (1970) т.е. до 2000 года), и установить флажки показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации (R^2).

На рис. 16 показан примерный вид диаграммы с построенной линией тренда.

Рис. 16

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

Отформатируйте построенную диаграмму примерно так, как показано на рис. 16.

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

Кроме этого, используя уравнение линии тренда можно попытаться спрогнозировать численность населения в будущем. Используя уравнение линии тренда, рассчитайте (в какой- то произвольной ячейке рабочего листа) прогнозируемую линейным трендом численность населения в 2000 году. В изображённом на диаграмме уравнении линии тренда x – независимая переменная (год), а y – численность населения.

Сравните полученную величину с данными переписи 2000 года – 281 млн. Для этого в какой-то произвольной ячейке рабочего листа вычислите (в процентах) относительную ошибку прогнозирования линейным трендом:

Относительная ошибка =

Реальная численность

Прогноз

(3)

Реальная численность

г) Вполне возможно, что какая-то кривая будет лучше аппроксимировать данные. Наиболее простая кривая – парабола 2-й степени. Не трогая построенную линию линейного тренда, на построенную диаграмму добавьте линию тренда Полиномиальная Степень 2 (в окне Формат линии тренда), сделайте так, чтобы парабола продолжалась до 2000 года, а на диаграмме отображалось её уравнение и величина R 2.

д) Используя уравнение параболической линии тренда, рассчитайте (в какой-то произвольной ячейке рабочего листа) прогнозируемую параболическим трендом численность населения в 2000 году. Внимание! Точность коэффициентов в уравнении линии параболического тренда, представленных на диаграмме «по умолчанию», может оказаться недостаточной для этого расчёта. Поэтому перед расчётом увеличьте число значащих цифр в

коэффициентах уравнения линии тренда. Для этого выделите на диаграмме уравнение линии параболического тренда, а затем в контекстном меню выполните команду Формат подписи линии тренда. В появившемся окне Формат подписи линии тренда установите в списке Числовые форматы: значение Числовой, а Число десятичных знаков равным 5.

Сравните полученную величину с данными переписи 2000 года – 281 млн. Для этого в какой-то произвольной ячейке рабочего листа вычислите (в процентах) относительную ошибку по ф-ле (3).

е) Сравните относительные ошибки прогнозирования линейным и параболическим трендами. Ошибка прогнозирования параболическим трендом должна получиться меньше (по абсолютной величине), так как парабола в среднем расположена «ближе» к точкам ряда, чем прямая. Об этом свидетельствует и величина R 2 – для параболы она ближе к 1.

Примечания.

1. Анализируя данные с помощью линии тренда можно прогнозировать развитие какого-то процесса. Например, если в данном случае вы убедились, что параболический тренд позволяет с достаточной точностью прогнозировать численность населения на 30 лет вперед, то построив линию тренда с использованием реальных данных по 2010 год, вы с достаточной степенью уверенности можете прогнозировать численность населения до 2040 года (до 30 лет вперёд).

2. Уравнение линии тренда позволяет хранить приближенные значения представленных в таблице данных более компактно (в виде формулы).