Редактирование и форматирование данных

Редактирование данных возможно как в процессе ввода в ячейку, так и после ввода. Если во время ввода данных в ячейку допущена ошибка, то она может быть исправлена стиранием неверных символов при помощи клавиши Backspace и набором заново (клавиша Esc – отмена ввода данных).

Введенные данные можно редактировать двумя способами:

• выделить ячейку и, щелкнув в строке формул, отредактировать данные;

• для редактирования непосредственно в ячейке нужно дважды щелкнуть на ней или нажать клавишу F2.

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

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

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

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

При использовании меню необходимо выделить строку или столбец и выполнить команду Формат – Столбец – Ширина (или Формат – Строка – Высота).

Символы любой ячейки или блока можно оформить разными шрифтами. Для этого необходимо выделить ячейку или блок, а затем воспользоваться уже знакомыми по программе Microsoft Word кнопками на панели Форматирование или командой меню Формат – Ячейки – Шрифт.

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

Технология интервального прогнозирования

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

При анализе экспериментальных и статистических данных часто используются трендовые модели (тренды).

Трендовая модель (тренд) – математическая функция вида y = f(x), график которой отражает общую тенденцию ряда данных.

Математическая модель – приближенное описание какого-либо класса явлений внешнего мира, выраженное с помощью математической символики; система математических уравнений, описывающих наиболее важные свойства объекта исследования.

В Excel тренд может быть создан на рабочем листе (используется средство Автозаполнение или одна из статистический функций, например РОСТ или ТЕНДЕНЦИЯ), а также на диаграмме. Создание тренда на диаграмме яснее демонстрирует модель и прослеживает тенденцию изменения данных. Excel способен автоматизировать создание тренда и построение его графика (линии тренда) в гистограммах, линейчатых диаграммах, диаграммах с областями, графиках и точечных диаграммах.

Рассмотрим последовательность создания тренда по данным таблицы, представленной на рис. 41.

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

Рис. 41. Интервальное прогнозирование

Порядок добавления линии тренда к рядам данных:

1. выделить ряд данных, к которому нужно добавить линию (щелкнуть правой кнопкой мыши на любой точке графика);

2. выбрать команду контекстного меню Добавить линию тренда;

3. в появившемся окне выбрать нужный тип регрессионной линии тренда (желательно использовать монотонные функции, в данном случае – тип Линейная). Также можно указать название тренда, задать при необходимости интервал прогнозирования (продолжить линию тренда вперед или назад за пределы, в которых данные известны), задать точку пересечения тренда с осью Y, а также поместить на диаграмму уравнение тренда и величину достоверности аппроксимации. В нашем случае зададим прогноз вперед на 3 периода (интервал прогнозирования не должен превышать 1/3 интервала наблюдения) и отметим пункты Показывать уравнение на диаграмме и Поместить на диаграмму величину достоверности аппроксимации.

В результате на диаграмме появится линия тренда, его уравнение y = 13,867х + 287,78 и квадрат коэффициента корреляции R² = 0,881.

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

Для расчета числовых данных в ячейку В3 нужно записать уравнение тренда =13,867*B1 + 287,78 (т. к. х – номер месяца) и затем скопировать эту формулу в ячейки С3…М3. Получим прогнозные значения: на октябрь – 426, ноябрь – 440, на декабрь – 454.

Рис. 42. Параметры тренда

Насколько обоснованны эти цифры?

Прогнозирование на основе тренда (как и любой другой статистический метод прогнозирования) применимо лишь при выполнении условий:

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

• данные о прогнозируемом объекте представительны и достоверны.

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

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

где  – среднее квадратическое отклонение ошибок аппроксимации:

n – количество интервалов наблюдения (9 месяцев);

р – число параметров (коэффициентов) тренда (для линейного р = 2);

y_i – данные ряда (количество преступлений за i-й месяц);

y_Pi – расчетные данные;

t_i – номер интервала, считая от середины интервала наблюдения (середина интервала наблюдения соответствует 5-му месяцу, т.е. t₁ = –4, t₂ = –3 … t₅ = 0, t₆ = 1 … t₉ = 4);

t_k – номер интервала прогноза, считая от середины интервала наблюдения (т. е. t₁₀ = 5, t₁₁ = 6, t₁₂ = 7).

Для расчета среднего квадратического отклонения ошибок аппроксимации  в ячейку В16 запишем формулу

=КОРЕНЬ(СУММКВРАЗН(B2:J2;B3:J3)/7).

Средняя ошибка прогноза линейного тренда зависит от номера интервала прогнозирования, поэтому для 10-го месяца в ячейку В17 запишем формулу =$B$16*КОРЕНЬ(1/9+12*5^2/(9^3-9)), затем скопируем ее в С17:D17 и заменим в полученных формулах номер интервала прогнозирования 5 соответственно на 6 и 7.

Для получения достаточно надежных границ прогноза, скажем, с вероятностью 0,9 (или 90 %) того, что ошибка не будет больше указанной, следует среднюю ошибку умножить на величину t-критерия Стьюдента при указанной вероятности (1–0,9 = 0,1) и при числе степеней свободы, равном

n – p= 9 – 2 = 7.

Кроме того, ошибка прогноза  y должна учитывать и ошибку аппроксимации:

.

Чтобы определить ошибку прогноза для 10-го месяца, в ячейку В18 запишем формулу =КОРЕНЬ($B$16^2+(СТЬЮДРАСПОБР(0,1;7)*B17)^2), затем скопируем ее в С18:D18.

Получим значения ошибки прогноза  y для октября – 25,3; ноября – 28 и декабря – 31.

Интервал прогноза определяется по формуле y_pi   y (расчетное значение  ошибка прогноза). Для определения интервала прогноза на октябрь в ячейку I16 запишем формулу =К3-В18, в ячейку К16 запишем сумму расчетного значения и ошибки прогноза =К3+В18. Аналогичные формулы для ноября и декабря можно записать в ячейки I17:К18:

ноябрь =L3-C18 =L3+C18

декабрь =M3-D18 =M3+D18. Окончательный вид таблицы представлен на рис. 41.

Получается, что при сохранении существующей тенденции с вероятностью 90 % число наблюдаемых в декабре явлений окажется в пределах 454  31 или в интервале от 423 до 485.

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