книги / Теория вероятностей и математическая статистика. Прикладная статистика с использованием MS EXCEL

–выделить ячейку, меню «Правка» → «Копировать» (или комбинация клавиш <Ctrl>+<C>); выделить целевую ячейку (ячейки), то есть область, куда надо поместить копируемые данные; меню «Правка» → «Вставить» (или <Ctrl>+<V>);

–быстрое копирование: выделить ячейку, содержащую формулу, и перетащить границу выделенной ячейки в новое место, удерживая нажатой клавишу <Ctrl>.

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

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

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

151

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

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

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

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

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

иих изображение.

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

152

В зависимости от места расположения и особенностей построения и редактирования различают два вида диаграмм:

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

–диаграммы в формате полного экрана на новом рабочем

листе.

Оба типа диаграмм связаны с данными рабочего листа

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

лений в Excel является «Мастер диаграмм», который вызывается специальной кнопкой на панели «Стандартная» или из меню «Вставка» → «Диаграмма» (рис. П1.9).

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

153

Рис. П1.10. Мастер диаграмм, шаг 4 – построение внедренной диаграммы

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

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

154

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

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

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

5. Средства структуризации и первичной обработки данных

Ранее уже отмечалось, что к принципиальным преимуществам электронных таблиц как формы организации данных относится гибкое сочетание возможностей, которые дает привязка информации к ячейкам таблицы, со свободой принятия решений о том, какую именно ячейку выбрать для размещения информации. Однако данная «свобода» имеет и обратную сторону: зачастую за нее приходится платить на дальнейших этапах работы, когда возникают проблемы с выполнением тех или иных операций по автоматизированной обработке данных. Несекрет, что логика таких операций предполагает наличие жестко формализованной структуры у исходной информации. Одним из способов решения этой проблемы в Excel являются списки.

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

155

соответствующего типа, используется термин «поле». Поля списка могут иметь имена, которые должны располагаться

впервой строке таблицы.

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

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

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

–выполнить команду меню «Данные» → «Сортировка»;

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

Рис. П1.12. Задание параметров сортировки данных

156

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

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

1.Выделить всю область списка (включая заголовок).

2.Выполнить команду меню «Данные» → «Фильтр» →

→«Автофильтр», после чего в строке заголовков появятся кнопки с раскрывающимися списками значений.

3.С помощью появившихся элементов управления кнопок могут быть заданы стандартные критерии отбора («Все», «Первые 10...», «всестрокисконкретнымзначением»), рис. П1.13.

Рис. П1.13. Задание фильтра

157

Контрольные вопросы

1.Перечислите известные вам способы редактирования

иформатирования данных в ячейках рабочей книги.

2. Какие особенности имеет процесс ввода формул

вячейки Excel (по сравнению с вводом данных других типов)?

3.В каких случаях следует использовать абсолютную,

ав каких – относительную адресацию?

4.Опишите процесс ввода встроенных функций.

5.Опишите процесс построения диаграммы в Excel.

6.Перечислите известные вам инструменты статистического анализа данных в Excel.

158

ПРИЛОЖЕНИЕ 2

СТАТИСТИЧЕСКИЕ ФУНКЦИИ EXCEL

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

FPACП (x; степени_свободы1; степени_свободы2)

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

FРАСПОБР (вероятностъ; степени_свободы1; степени_свободы2)

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

ZTECT (массив; х; сигма)

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

БЕТАОБР (вероятность; альфа; бета; А; В)

Возвращает обратную функцию к интегральной функции плотности бета-вероятности. Интегральное бета-распреде-

159

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

БЕТАРАСП (х; альфа; бета; А; В)

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

БИНОМРАСП(число_успехов; число_испытаний; вероятность_успеха; интегральная)

Возвращает отдельное значение биномиального распределения. Функция БИНОМРАСП используется в задачах с фиксированным числом тестов или испытаний, когда результатом любого испытания может быть только успех или неудача, испытания независимы, и вероятность успеха постоянна на протяжении всего эксперимента. Например, БИНОМРАСП может вычислить вероятность того, что двое из трех следующих новорожденных будут мальчиками.

ВЕЙБУЛЛ (х; альфа; бета; интегральная)

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

ВЕРОЯТНОСТЬ (х_интервал; интервал_вероятностей; нижний_предел; верхний_предел)

Возвращает вероятность того, что значение из интервала находится внутри заданных пределов. Если верхний_предел не задан, то возвращается вероятность того, что значения в аргументе х_интервал равняются значению аргумента нижний_предел.

160