Лабораторная работа №4
Категорийные данные
Категорийные данные представляют собой набор нечисловых значений, который показывает, к какой категории относится каждый из рассматриваемых объектов. Например, пол задается двумя категориями (мужской и женский), преподавательская должность имеет несколько категорий (ассистент, доцент, профессор), автомобильные марки характеризуются достаточно большим числом категорий (Нисан, Форд, Тойота и др.). Категорийные данные могут быть описаны с помощью частот (или процентов), определяющих число значений данных, попавших в каждую из категорий. Во многих случаях, если известно точное количество категорий, можно каждой категории поставить в соответствие число и затем обрабатывать полученные данные как количественные. Например, если имеется в точности две категории, то их можно обозначить цифрами 1 и 0. Категорийные данные разделяются на два типа: порядковые и номинальные.
Порядковые категорийные данные состоят из категорий, для которых существует порядок, имеющий содержательный смысл. Например, можно вести речь об объекте как о первом («лучшем»), втором («хорошем»), третьем («посредственном») и т.д. Порядковые данные можно ранжировать и использовать это ранжирование для анализа. Для порядковых данных в качестве статистических показателей широко используются медиана и мода.
Номинальные категорийные данные определяются категориями, которые нельзя содержательно упорядочить. Для таких категорий нет чисел, которые можно было бы поставить в соответствие с содержательным смыслом. Следовательно, с номинальными данными нельзя проводить вычисления, и нет основы для ранжирования. Все, что можно сделать – это подсчитать частоту (процент), попадающих в каждую из категорий наблюдений и использовать в качестве обобщающего показателя моду. Примерами номинальных данных являются наименования городов или главный продукт производственных предприятий (электроника, пластмасса, древесина).
Использование Сводной таблицы для одномерного набора данных
В случае одномерного набора категорийных данных (одной переменной) Сводная таблица Excel служит для подсчета частот всевозможных значений. Результат может отображаться либо в абсолютных числах, либо в процентах. Для создания Сводной таблицы в Excel используется Мастер сводных таблиц и диаграмм.
| Задание 1. | Создать таблицу распределения частот для оценки ресторанного бизнеса. |
Для оценки ресторанного бизнеса введена шкала значений от 1 (Очень неблагоприятная) до 6 (Очень благоприятная). В таблице 1 приведены восемнадцать наблюдений, выбранных из обзора ресторанов 1980 г.
Таблица 1. Оценки ресторанного бизнеса
| Ресторан | Оценка | Ресторан | Оценка | Ресторан | Оценка |
Несмотря на то, что оценки выражены числами, мы имеем дело с порядковыми категорийными данными, поскольку предложенная шкала оценок носит субъективный характер. Непонятно, например, можно ли считать, что разница между оценками 5 и 4 такая же, как и между оценками 2 и 1, или, что оценка 2 в два раза лучше оценки 1. Опишем данные с помощью таблицы распределения частот, в которой подсчитывается количество ресторанов, имеющих соответствующую оценку.
1. Откройте программу Excel. Щелкните на кнопке Сохранить на панели инструментов Стандартная. В появившемся диалоговом окне откройте папку Статистика и задайте имя файлу Категорийные данные.xls.
2. На Листе1 в ячейке A1 задайте метку Ресторан, а в ячейке B1 задайте метку Оценка.
3. В ячейку A2 введите значение 1. Выделите диапазон A2:A19 и заполните его числами от 1 до 18, выполнив команду: Правка®Заполнить®Прогрессия…
4. В диапазон B2:B19 введите оценки из Таблицы 1.
Замечание. Ряды строк с данными, такие как отображены на Листе1, в Excel называются списками или базой данных. Строки называются записями, а столбцы – полями.
5. Выделите любую ячейку в базе данных (диапазон A1:B19) и выберите в меню команду:
Данные®Сводная таблица…
Открывается диалоговое окно Мастер сводных таблиц и диаграмм – шаг 1 из 3. В диалоговом окне включите опции как показано ниже на рисунке. Щелкните на кнопке Далее>.
6. В диалоговом окне Мастер сводных таблиц и диаграмм – шаг 2 из 3 убедитесь, что Excel правильно определил промежуток данных. Щелкните на кнопке Далее>.
7. В диалоговом окне Мастер сводных таблиц и диаграмм – шаг 3 из 3 включите опцию существующий лист, а затем выделите ячейку E1, которая определяет левый верхний угол области вывода сводной таблицы. В текстовом поле диалогового окна Excel выведет имя листа и абсолютную ссылку на ячейку (см. рис. ниже). Щелкните на кнопке Макет…
8. В диалоговом окне Мастер сводных таблиц и диаграмм – макет перетащите кнопку Оценка, которая находится справа, в область слева под названием Строка. Кнопку Ресторан перетащите в область Данные (см. рисунок ниже).
Замечание. Когда вы перетащите кнопку Ресторан, ее название может измениться на Сумма по полю Ресторан. В этом случае щелкните дважды по названию и в появившемся диалоговом окне Вычисление поля сводной таблицы из списка в области Операция: выберите элемент Количество (см. рисунок ниже). Щелкните на кнопке ОК.
9. Закройте диалоговое окно макета, щелкнув на кнопке ОК. В диалоговом окне Мастер сводных таблиц и диаграмм – шаг 3 из 3 щелкните на кнопке Готово. Таблица распределения частот, созданная Мастером сводных таблиц и диаграмм разместится в диапазоне E1:F9.
Замечание. Если появляются дополнительные окна, то их надо закрыть.
10. Скопируйте полученную таблицу в диапазон H1:I9. Теперь заменим во второй таблице абсолютные числа процентами.
11. Выделите любую ячейку в столбце I (например, I4) и щелкните правой кнопкой мыши на этой ячейке. В контекстном меню выберите команду Параметры поля… Открывается диалоговое окно Вычисление поля сводной таблицы. Щелкните на кнопке Дополнительно>>. Появляется область Дополнительные вычисления:, в которой откройте список и выберите элемент Доля от общей суммы (см. рисунок ниже). Щелкните на кнопке ОК.
12. Получили таблицу, в которой количественные показатели выражены в процентах. Для облегчения чтения, выделите диапазон процентных значений I3:I9 и уменьшите разрядность до целых чисел.
13. Сравните результаты и ответьте на следующие вопросы.
Ü Какую оценку имеет наибольшее количество ресторанов?
Ü Какой процент ресторанов имеет самую низкую оценку, а какой процент ресторанов имеет самую высокую оценку?
Использование Сводной таблицы с двумя входами
Сводные таблицы с двумя входами часто называют перекрестными таблицами. Такие таблицы используют для установления зависимости между двумя категорийными переменными. Они показывают, сколько раз встречается каждая комбинация из двух категорий.
| Задание 1. | Определить зависимость перспектив (оценок) ресторанного бизнеса от вида собственности. |
Имеется восемнадцать наблюдений, выбранных из обзора ресторанов 1980 г. В таблице 2 представлены оценки ресторанов и вид собственности. Отсутствующие значения в таблице 2 отмечены звездочками (*).
Таблица 2. Данные обзора ресторанов
| Ресторан | Оценка | Владелец | Ресторан | Оценка | Владелец |
| * | * | ||||
1. Откройте файл Категорийные данные.xls.
2. С Листа1 скопируйте данные (диапазон A1:B19) на Лист2 в тот же диапазон A1:B19.
3. В ячейке C1 задайте метку Владелец, а в диапазон C2:C19 введите данные собственности, включая отсутствующие значения (*).
4. Выделите любую ячейку в базе данных (диапазон A1:C19) и выберите в меню команду:
Данные®Сводная таблица…
5. В диалоговом окне Мастер сводных таблиц и диаграмм – шаг 1 из 3 включите опции в списке или базе данных Microsoft Excel и сводная таблица. Щелкните на кнопке Далее>.
6. На втором шаге Мастера сводных таблиц и диаграмм убедитесь, что Excel правильно определил промежуток данных (A1:C19). Щелкните на кнопке Далее>.
7. На третьем шаге Мастера сводных таблиц и диаграмм щелкните на кнопке Макет…
8. В диалоговом окне Мастер сводных таблиц и диаграмм – макет перетащите кнопку Владелец в область под названием Строка, кнопку Оценка перетащите в область Столбец, кнопку Ресторан перетащите в область Данные (см. рисунок ниже). Если при перетаскивании кнопки Ресторан появится название Сумма по полю Ресторан, щелкните дважды по названию и в появившемся диалоговом окне Вычисление поля сводной таблицы из списка в области Операция: выберите элемент Количество. Щелкните на кнопке ОК. Результат действий изображен ниже на рисунке.
9. Закройте диалоговое окно макета, щелкнув на кнопке ОК. В диалоговом окне Мастер сводных таблиц и диаграмм – шаг 3 из 3 включите опцию существующий лист а затем выделите ячейку E1 и щелкните на кнопке Готово. Таблица распределения частот с двумя входами разместится в диапазоне E1:L7.
Замечание. Если появляются дополнительные окна, то их надо закрыть.
10. Чтобы спрятать отсутствующие значения, откройте список поля Владелец, уберите галочку со значка звездочки (*) и щелкните на кнопке ОК.
11. С целью облегчения сравнения перспектив бизнеса в зависимости от вида собственности количественные показатели лучше выразить в процентах. Для этого скопируйте полученную таблицу в диапазон E10:L15.
12. Выделите любую ячейку с числом в диапазоне F12:L14 и щелкните правой кнопкой мыши на этой ячейке. В контекстном меню выберите команду Параметры поля… Открывается диалоговое окно Вычисление поля сводной таблицы. Щелкните на кнопке Дополнительно>>. В области Дополнительные вычисления: откройте список и выберите элемент Доля от суммы по строке. Щелкните на кнопке ОК.
13. Таблица будет лучше выглядеть, если убрать Общий итог по столбцам. Выделите любую ячейку с числом в диапазоне F12:L14 и щелкните правой кнопкой мыши на этой ячейке. В контекстном меню выберите команду Параметры таблицы… В диалоговом окне снимите флажок с опции общая сумма по столбцам и щелкните на кнопке ОК.
14. Выделите ячейку E10 и введите в нее название Доля по строкам. Уменьшите ширину столбца E в соответствии с этим названием.
15. Шесть категорий перспектив (оценок) бизнеса могут быть избыточными. Оставим только две категории оценок: Неблагоприятная и Благоприятная. Сгруппируйте данные, содержащие оценки 1, 2 и 3. Для этого выделите ячейки с заголовками этих оценок (F11:H11) и щелкните правой кнопкой мыши. В контекстном меню выберите команду: Группа и структура®Группировать… Теперь щелкните правой кнопкой мыши на ячейке с названием Группа1 и в контекстном меню выберите команду: Группа и структура®Скрыть детали.
16. Сгруппируйте данные, содержащие оценки 4, 5 и 6. Для этого выделите ячейки с заголовками этих оценок (G12:I12) и щелкните правой кнопкой мыши. В контекстном меню выберите команду: Группа и структура®Группировать… Теперь щелкните правой кнопкой мыши на ячейке с названием Группа2 и в контекстном меню выберите команду:
Группа и структура®Скрыть детали.
17. Чтобы можно было форматировать таблицу, предварительно скопируйте ее в другую область. Для этого выделите таблицу (диапазон E10:H15) и щелкните на кнопке Копировать на панели инструментов Стандартная. Теперь щелкните на ячейке E18 и выполните команду:
Правка®Специальная вставка… В диалоговом окне включите опцию значения и форматы чисел. Щелкните на кнопке ОК.
18. Отформатируйте таблицу как показано ниже.
| Доля по строкам | |||
| Оценка | Общий итог | ||
| Собственность | Неблагоприятная | Благоприятная | |
| Частная | 71% | 29% | 100% |
| Товарищество | 0% | 100% | 100% |
| Корпоративная | 43% | 57% | 100% |
19. Отобразим строчные проценты в линейчатой диаграмме. Выделите в таблице диапазон ячеек E20:G23.
20. Откройте Мастер диаграмм и на шаге 1 выберите тип диаграммы Нормированная линейчатая. Щелкните на кнопке Далее>.
21. На шаге 2 проверьте, что выбран нужный диапазон и включена опция Ряды в: столбцах. Щелкните на кнопке Далее>.
22. На шаге 3 на вкладке Заголовки введите название диаграммы Оценки бизнеса в зависимости от вида собственности, а на вкладке Линии сетки уберите все отметки. Щелкните на кнопке Далее>.
23. На шаге 4 включите опцию имеющемся: и щелкните на кнопке Готово. Разместите диаграмму в диапазоне E26:J40.
24. Для отображения категорий собственности на диаграмме в том же порядке, что и в таблице, выделите вертикальную Ось категорий, щелкните правой кнопкой мыши и в контекстном меню выберите команду: Формат оси… В диалоговом окне на вкладке Шкала установите флажки на опциях обратный порядок категорий и пересечение с осью Y (значений) в максимальной категории. Щелкните на кнопке ОК.
25. Дважды щелкните на горизонтальной Оси значений. В диалоговом окне Формат оси на вкладке Шкала в области Авто снимите флажок с опции цена основных делений: и в текстовом поле введите 20.
26. Проанализируйте результаты и ответьте на следующие вопросы.
Ü Какой вид собственности имеет самую высокую оценку, а какой – самую низкую?
Ü Какой вид собственности можно считать перспективным?
Контрольные вопросы
1. Что представляют собой категорийные данные? В чем отличие между количественными и категорийными данными
2. В чем заключается разница между порядковыми и номинальными категорийными данными?
3. Какие обобщающие показатели используются при анализе порядковых данных, а какие – при анализе номинальных данных?
4. Какие операции можно применять к категорийным данным? Какой инструмент Excel используется при анализе категорийных данных?
Контрольные задания
На Листе3 выполните задание с использованием в зависимости от варианта либо базы данных служащих, либо базы данных студентов (файлы База данных служащих.xls и База данных студентов.xls находятся в папке Мои документы).
| Вариант | Задание |
| Рассматривая пол служащих, используя Мастер сводных таблиц и диаграмм, вычислите процент мужчин и женщин. | |
| Рассматривая уровень подготовки служащих, используя Мастер сводных таблиц и диаграмм, вычислите процент каждого уровня подготовки. | |
| Используя Мастер сводных таблиц и диаграмм, определите зависимость (в процентах) уровня подготовки служащих от пола (мужчина или женщина). Отформатируйте таблицу, заменив обозначения уровней подготовки следующими названиями: А–Низкий, В–Средний, С–Высокий. Постройте нормированную линейчатую диаграмму. Отобразите категории пола на диаграмме в том же порядке, что и в таблице. Проанализируйте результаты. | |
| Используя Мастер сводных таблиц и диаграмм, определите зависимость (в процентах) пола (мужчина или женщина) от уровня подготовки служащих. Отформатируйте таблицу, заменив обозначения уровней подготовки следующими названиями: А–Низкая квалификация, В–Средняя квалификация, С–Высокая квалификация. Постройте нормированную линейчатую диаграмму. Отобразите категории уровня подготовки на диаграмме в том же порядке, что и в таблице. Проанализируйте результаты. | |
| Используя Мастер сводных таблиц и диаграмм, вычислите процент юношей и девушек, участвующих в опросе студентов. | |
| Используя Мастер сводных таблиц и диаграмм, вычислите процент ответов студентов на каждый вопрос. | |
| Используя Мастер сводных таблиц и диаграмм, создайте таблицу из двух переменных (Пол и Применение). Вычислите доли по строкам. Отформатируйте таблицу, заменив обозначения ответов соответствующими названиями. Постройте нормированную линейчатую диаграмму. Отобразите категории пола на диаграмме в том же порядке, что и в таблице. Проанализируйте результаты. | |
| Используя Мастер сводных таблиц и диаграмм, создайте таблицу из двух переменных (Пол и Обеспокоенность). Вычислите доли по строкам. Отформатируйте таблицу, заменив обозначения ответов соответствующими названиями. Постройте нормированную линейчатую диаграмму. Отобразите категории пола на диаграмме в том же порядке, что и в таблице. Проанализируйте результаты. | |
| Используя Мастер сводных таблиц и диаграмм, создайте таблицу из двух переменных (Пол и Скучность). Вычислите доли по строкам. Отформатируйте таблицу, заменив обозначения ответов соответствующими названиями. Постройте нормированную линейчатую диаграмму. Отобразите категории пола на диаграмме в том же порядке, что и в таблице. Проанализируйте результаты. | |
| Используя Мастер сводных таблиц и диаграмм, создайте таблицу из двух переменных (Пол и Индивидуальный подход). Вычислите доли по строкам. Отформатируйте таблицу, заменив обозначения ответов соответствующими названиями. Постройте нормированную линейчатую диаграмму. Отобразите категории пола на диаграмме в том же порядке, что и в таблице. Проанализируйте результаты. | |
| Используя Мастер сводных таблиц и диаграмм, создайте таблицу из двух переменных (Применение и Обеспокоенность). Вычислите доли по строкам. Сгруппируйте ответы в две категории: ответы 1, 2, 3 объедините в категорию с названием Может быть, а ответы 4, 5 – в категорию Нет. Отформатируйте таблицу, включив соответствующие названия. Постройте нормированную линейчатую диаграмму. Отобразите категории на диаграмме в том же порядке, что и в таблице. Проанализируйте результаты. | |
| Используя Мастер сводных таблиц и диаграмм, создайте таблицу из двух переменных (Применение и Индивидуальный подход). Вычислите доли по строкам. Сгруппируйте ответы в две категории: ответы 1, 2, 3 объедините в категорию с названием Может быть, а ответы 4, 5 – в категорию Нет. Отформатируйте таблицу, включив соответствующие названия. Постройте нормированную линейчатую диаграмму. Отобразите категории на диаграмме в том же порядке, что и в таблице. Проанализируйте результаты. | |
| Используя Мастер сводных таблиц и диаграмм, создайте таблицу из двух переменных (Применение и Скучность). Вычислите доли по строкам. Сгруппируйте ответы в две категории: ответы 1, 2, 3 объедините в категорию с названием Может быть, а ответы 4, 5 – в категорию Нет. Отформатируйте таблицу, включив соответствующие названия. Постройте нормированную линейчатую диаграмму. Отобразите категории на диаграмме в том же порядке, что и в таблице. Проанализируйте результаты. | |
| Используя Мастер сводных таблиц и диаграмм, создайте таблицу из двух переменных (Скучность и Обеспокоенность). Вычислите доли по строкам. Сгруппируйте ответы в две категории: ответы 1, 2, 3 объедините в категорию с названием Может быть, а ответы 4, 5 – в категорию Нет. Отформатируйте таблицу, включив соответствующие названия. Постройте нормированную линейчатую диаграмму. Отобразите категории на диаграмме в том же порядке, что и в таблице. Проанализируйте результаты. |