Обработка статистических данных средствами электронных таблиц Excel

Рис. 1.3.1 Управление форматом ячеек

Интервал (блок, диапазон) ячеек - прямоугольная область  смежных или несмежных ячеек. Он задается указанием адреса верхней  левой и правой нижней ячеек, разделенных  символом двоеточие. Например: A2:G4. 
ПРИМЕЧАНИЕ 
Выделение несмежного диапазона ячеек производится с помощью мыши при нажатой клавише Ctrl. 

Ячейки имеют  заданный формат, который можно установить из меню Формат командой Ячейки. Эта  команда имеет несколько вкладок, из вкладки Число можно выбрать  категорию значений в ячейке и  задать код формата, в том числе  можно создать пользовательский формат. Чтобы ввести данные в ячейку, следует:

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

2. Ввести  данные прямо с клавиатуры  в режиме редактирования ячейки (курсор ввода находится внутри  ячейки).

Или: 
1. Дважды щелкнуть левой клавишей мыши.

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

3. Завершить  ввод одним из трех способов:

- нажатием  клавиши Enter;

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

- изменить  данные.

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

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

     В Excel существует несколько способов, которые позволяют упростить и ускорить процесс ввода данных:

- повторный  ввод (копирование) уже существующих  данных; 
- автозаполнение;

- ввод прогрессий. 

Для повтора (копирования) существующих данных следует: 
1) выделить ячейку;

2) подвести  указатель мыши к маркеру автозаполнения и перетащить рамку выделения по всем ячейкам.

Аналогичного  результата можно добиться, если воспользоваться  меню Правка > Заполнить и указать направление копирования.

Автозаполнение - заполняет выделенные ячейки выбранными (либо

озданными дополнительно) последовательностями.

 

                       1.4. Формулы, функции, мастер функций.

      Формула - это выражение, состоящее из операндов, которое используется для выполнения вычислений над имеющимися данными. Каждая формула начинается с символа равно (=). В качестве операндов могут быть: числа, логические значения, ссылки на адреса ячеек (диапазон ячеек), встроенные функции, которые соединяются с помощью символов операций - сложения, вычитания, умножения, деления, возведения в степень. Сама формула -- это тоже значение (результат вычислений), которое хранится в ячейке рабочего листа.

Рис. 1.4.1. Диапазон, заполненный геометрической прогрессией

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

2. Умножение  и деление.

3. Сложение  и вычитание.

     Технические возможности Excel позволяют создавать достаточно сложные формулы. Однако подлинная мощь Excel как программного средства реализуется через широкий набор встроенных функций, предназначенных для выполнения самых различных вычислительных и логических процедур. 
    Несмотря на их разнообразие, все функции имеют одинаковый стандартный формат: имя функции и находящийся в круглых скобках перечень аргументов, разделенных точками с запятой. Например: 
=ОКРВВЕРХ(ВЗ;2). 
Существенную помощь пользователю в процессе конструирования выражений, содержащих формулы, может оказать Мастер функций. Вызвать его можно одним их следующих способов:

- меню Вставка  > Функция;

- кнопка  Вставка функции на панели  Стандартная. 

     В Excel редактирование формул производится так же, как и редактирование любых введенных данных:

- выделить  ячейку, содержащую редактируемую  формулу, нажать клавишу F2, внести  изменения, нажать клавишу Enter;

- быстрая  правка - двойной щелчок по ячейке, нажать клавишу F2, внести изменения,  нажать Enter.

Копируются  формулы так же, как и другие данные: 
- выделить ячейку, меню Правка > Копировать (или комбинация клавиш Ctrl+C); выделить целевую ячейку (ячейки), то есть область, куда надо поместить копируемые данные; меню Правка > Вставить (или Ctrl+V); 
- быстрое копирование: выделить ячейку, содержащую формулу, и перетащить границу выделенной ячейки в новое место, удерживая нажатой клавишу Ctrl.

Рис. 1.4.2. Ввод функции

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

           Различают: 
- полную абсолютную ссылку (знак $ ставится и перед именем столбца, и перед номером строки, например $А$8). В этом случае при копировании и перемещении адрес ячейки не меняется;

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

                              1.5. Графические возможности Excel.

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

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

    С помощью Excel можно строить диаграммы разных типов. Некоторые из них могут быть "объемными" (они выглядят очень эффектно и помогают подчеркнуть различия между разными наборами данных). 
В зависимости от места расположения и особенностей построения и редактирования различают два вида диаграмм:

- внедренные  диаграммы - помещается на том  же рабочем листе, где и данные, по которым они построены;

- диаграммы  в формате полного экрана на  новом рабочем листе. 
    Оба типа диаграмм связаны с данными рабочего листа и автоматически обновляются при изменении данных

   Удобным средством для создания графических представлений в Excel является Мастер диаграмм, который вызывается специальной кнопкой на панели Стандартная или из меню Вставка > Диаграмма (рис. 1.5.1).

Рис.1.5.1. Окно мастера диаграмм

При выборе нужного параметра (рис. 1.5.2) определяется, будет ли создан специальный диаграммный лист или внедренная диаграмма.

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

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

Рис. 1.5.3. Представление данных в виде графика

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

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

 

 

 

 

 

Глава 2. Пакеты программ для статистического анализа данных мониторинга.

 

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

                             2.1. Пакеты базовой статистики.

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

            2.2.Пакеты углубленного статистического анализа.

Пакеты  углубленного статистического анализа, ориентированные на специалистов по математической статистике. Такие пакеты /Systat, SAS, Statgraphics и другие/ содержат мощное математическое обеспечение, включающее методы разведочного анализа, факторный и регрессионный анализ, анализ временных рядов и многое другое. Как правило, такие пакеты имеют собственный язык, позволяющий вызывать имеющиеся в пакете статистические процедуры и даже программировать новые. Например, в пакете  Statgraphics реализованы следующие алгоритмы статистического анализа:

ПАРАМЕТРИЧЕСКИЕ ТЕСТЫ - критерии Стьюдента, Фишера и  т.п.

НЕПАРАМЕТРИЧЕСКИЕ ТЕСТЫ - Хи-квадрат, знаков, Колмогорова-Смирнова и т.п.

РЕГРЕССИОННЫЙ АНАЛИЗ - простая регрессия, множественная  линейная регрессия, нелинейная регрессия  и т. п.

АНАЛИЗ ВРЕМЕННЫХ  РЯДОВ - сглаживание, автокорреляция и  т. п.

МНОГОМЕРНЫЕ МЕТОДЫ - кластерный анализ, корреляция и многое другое.

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