7. Работа со статистической функцией.

Цель выполнения работы: изучить возможности использования статистической функции ЧАСТОТА для обработки массива данных, которые должны быть сгруппированы в заданных диапазонах, что позволяет ответить на вопрос – как часто встречаются в выделенном диапазоне сведения об объекте, по результатам научиться строить диаграмму.

Очень часто исследователь располагает данными, которые не упорядочены в таблице, а если это сделать традиционными способами, например, применить фильтры для отсева данных или провести сортировку данных по определенному параметру, или выполнить операцию подведения промежуточных итогов, будет затрачено много времени, а главное становится трудно анализировать другие данные. Применение стандартных статистических функций, которые заложены в Мастере функций Excel, во многом упрощает работу исследователя. Рассмотрим пример использования статистической функции ЧАСТОТА для анализа данных. Предположим, у исследователя есть список жителей района, фрагмент которого представлен на листе Excel в ячейках A2:A11; B2:B11 (рис. 1).

Рис. 1. Пример последовательности выполнения работы со статистической функцией ЧАСТОТА для получения конечного результата

Задание 1: провести анализ массива данных в таблице Excel о распределении по возрастному цензу жителей района, отобразить результаты в виде диаграммы, в которой показаны частоты (количество) по заданным интервалам возрастных групп жителей района. Для освоения правил применения функции ЧКАСТОТА можно воспользоваться данными, представленных на рис 1.

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

· На отдельном листе Excel создать таблицу, состоящую из двух списков (Фамилия, Возраст).

· Введите наименования интервалов, которые будут отражены на диаграмме для построения распределений данных по возрасту (в таблице это столбец С – ячейки C2:C5, данные в столбце с наименованием «Интервал» понадобятся для обозначения наименований столбцов на оси гистограммы). Обратите внимание на то, что интервалы задаются неравномерные, например, так задано исследователем на рис. 1.

· Создайте заголовок с наименованием «Границы», например, в столбец D, и введите условия, по которым должны формироваться возрастные группы для функции ЧАСТОТА. Обратите внимание, что для интервала «Младше 35 лет» следует установить только правую границу. Для последующих интервалов ставят также только правую границу, поэтому для интервала (последнего) старше 50 лет не надо ставить ограничений (все данные о лицах, возраст которых превышает 50 лет, попадут в этот интервал), что отображено на рис. 1 в ячейках D2:D4.

· Выделить область на листе, в которой будет отображаться результат группировки данных по заданным критериям (интервалам), начиная от ячейки E2 до ячейки E5 (выделяется столько ячеек, сколько задумано интервалов). В рассматриваемом примере создан заголовок в ячейке E1 с наименованием «Частота», а затем выделено столько ячеек, сколько интервалов задумал исследователь (их всего четыре).

· Установить курсор в первую ячейку, где будут отображаться частоты (в данном примере это ячейка E2).

· Открыть Мастер функций, найти в статистических функциях «Частота». Заполнить диалоговое окно Аргументы функций (рис. 2).

· В окне «Массив_данных» должен находиться массив с возрастом жителей, выбранный из ячеек B2:B11, если вместо возраста используется год рождения, то в окне придётся вставить формулу для преобразования данных в число лет.

· В окне «Массив_ интервалов» установить ссылки на границы интервалов, которые находятся в ячейках D2:D4, напоминаем, что указывают на один интервал меньше.

· Чтобы отобразить результаты в виде массива данных на листе Excel - нажать сочетание клавиш CTRL+SHIFT+ENTER, после чего появится результат в ячейках E2:E5, которые ранее были выделены.

Рис. 2. Пример заполнения окон аргументами функции ЧАСТОТА

Для отображения результатов в графическом виде необходимо построить диаграмму, пример которой показан на рис. 1. Рекомендации по созданию диаграмм дано в разделе 3.

Контрольное задание. Воспользуйтесь данными, которые отражены в таблице (рис. 17), вычислите значения в столбце «Итого», без использования функции СУММПРОИЗВ. Для примера работы с функцией ЧАСТОТА, проведите изменение содержания списка (добавить фамилии, исправить возраст), и проанализируйте, как будет изменяться гистограмма. Введите новые диапазоны интервалов возрастных групп жителей района, проведите новые расчеты, какие изменения будут на листе Excel.

Вопросы для самоконтроля

1. Какие способы существуют для открытия Мастера построения функций?

2. Почему функции разделены по категориям?

3. Когда появляется диалоговое окно для ввода аргументов функции, какими способами его можно заполнить?

4. Можно ли с помощью статистических функций обрабатывать данные в текстовом формате?

Отображение результатов вычислений в виде диаграмм

Диаграмма – это способ наглядного представления информации, заданной в виде таблиц чисел (диаграмму следует рассматривать как объект, создаваемый Excel, который связывается с данными рабочего листа). В графическом процессоре Excel диаграммы делятся на типы (наиболее часто встречаются такие типы, как: гистограммы, графики и точечные диаграммы). На рис. 3 приведён пример построения круговой диаграммы по данным, которые взяты из таблицы – Ведомость оплаты (Тема 5, задание 1).

Рис. 3. Пример круговой диаграммы для демонстрации доли оклада сотрудника от общего фонда оплаты труда

Диаграмма в виде гистограммы, показанная на рис. 1 (Частота распределения населения по возрастным группам), имеет две оси, по горизонтали ось содержит текстовые данные, обозначающие величины интервалов, по вертикали ось показывает количественные величины, каждый столбец соответствует числу лиц, имеющих возраст который заключён в диапазоне. На рис. 3 показаны справа от кольцевой диаграммы фамилии сотрудников с обозначением цвета на диаграмме (как видите, оси на диаграмме отсутствуют). Вынос в отдельное поле обозначение внешнего вида данных с пояснением, называется легендой данных. В Excel 2007/2010 построение диаграммы осуществляется автоматически, если правильно выбрать данные, а затем на вкладке «Вставка» выбрать тип диаграммы. К сожалению, не всегда получается то, что хочет увидеть исследователь, поэтому, построим диаграмму с «нуля», за основу возьмём данные из примера работы со статистической функцией ЧАСТОТА.

Задание 2: построить диаграмму, отражающую распределение жителей района по заданным возрастным диапазонам.

· Откройте выполненное задание, в котором использовалась функция ЧАСТОТА, на вкладке «Вставка» выберите значок , раскройте его и щёлкните по пиктограмме . В результате на листе появится чистая прямоугольная.

· Щёлкните правой кнопкой мыши по прямоугольной области, в появившемся меню, выберите строку с командой . Эта команда открывает окно «Выбор источника данных».

· В окне «Выбор источника данных» в разделе «Элементы легенды» нажмите на кнопку , что приведёт к появлению окна «Изменение ряда» (обратите внимание на то, что это окно можно всегда открыть для изменения диапазона данных или для ввода нового имени ряда). Пиктограмма служит для открытия диалогового окна, в которое заносятся данные с листа Excel (можно сразу данные вводить в окно). На рис. 4 показано окно «Изменение ряда» с заполненными данными.

Рис. 4. Заполненные окна с указанными диапазонами ячеек

Обратите внимание, в диалоговых окнах: «Имя ряда» и «Значения» всегда должен присутствовать знак равенства. После знака равенства указывается в одиночных кавычках имя листа (в рассматриваемом примере, ярлык листа с данными о жителях района называется – Анализ данных), а после него установлен восклицательный знак (признак отделения названия от диапазона данных), после которого вводятся адреса ячеек с данными (адреса ячеек обязательно должны быть фиксированными). На рис. 5 показано окно «Выбор источника данных» с данными для построения диаграммы.

Рис. 5. Окно с выбранными данными для построения гистограммы

· Нажмите на кнопку ОК, мастер построения диаграмм отобразит гистограмму, которая представлена на рис. 6, после чего потребуется участие пользователя, чтобы на диаграмме правильно разместить обозначение осей, наименования данных и заголовка.

Рис. 6. Начальное отображение гистограммы

Чтобы изменить подписи горизонтальной оси, необходимо в окне «Выбор источника данных» в соответствующем разделе нажать на кнопку , в окне «Подписи оси» указать диапазон интервалов, в которых группируются жители района по возрасту, как показано на рис. 7, в примере этим диапазоном являются ячейки: $С$2:$С$5.

Рис. 7. Указание диапазона ячеек, в которых находятся подписи для столбцов гистограммы

· Для того чтобы провести изменения в заголовке диаграммы или осуществить переименовании осей и легенды, на вкладке «Работа с диаграммами» - «Макет», в Excel 2007/2010 предлагается воспользоваться группой «Подписи», которая содержит различные варианты команд, что показано на рис. 8.

Рис. 8. Группа команд для организации подписей на диаграмме

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

Вопросы для самоконтроля

1. Что такое диаграмма, с позиции разработчиков Excel?

2. Как пользоваться окном «Выбор источников данных»?

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

4. Какие дополнительные возможности даёт вкладка «Работа с диаграммами»?

5. Как построить несколько диаграмм на листе Excel?