2.4.1 Формирование статистических и сводных таблиц в мs Ехсеl
В программе МS Ехсеl имеется пакет анализа данных «Сводные таблицы», который позволяет проводить сводку данных, вычислять производные величины и другие статистические характеристики, необходимые для формирования и анализа статистических таблиц.
В пакете «Сводные таблицы» МS Ехсеl предусмотрен весьма большой набор функций и программных возможностей, однако в данном случае нами не ставится задача исчерпывающего представления этих возможностей. Здесь будут рассмотрены только некоторые приемы работы с указанным пакетом, позволяющие проводить минимально необходимый объем операций для решения задач сводки и статистического анализа табличных данных. Знакомство с этими возможностям и значительно облегчит более углубленное самостоятельное освоение указанного пакета программ.
Рисунок 5. Исходная база данных для формирования сводной
таблицы.
Исходную информацию для формирования таблиц МЗ Ехсеl целесообразно располагать в виде так называемых баз данных. Информация в любой базе данных всегда располагается строго в определенном порядке
В первой строке таблицы МS Ехсеl обязательно указываются наименования столбцов, куда будут заноситься значения соответствующих учетных признаков. Отдельная клетка этой строки, которая является заглавием конкретного столбца, называется Поле базы данных. В данном примере (Рисунок 5), используются следующие поля: Возраст, Пол Профессия, ЖЕЛ Факт (фактическая жизненная емкость легких), ЖЕЛ Долж. (должная жизненная емкость) и Ф/Д (отношение фактической к должной жизненной емкости легких).
Все учетные признаки по каждой отдельной единице наблюдения записываются в отдельную строку. Содержимое этой строки называется Записью. В данном примере представлено 16 записей, расположенных начиная со 2 по 17 строку. Т.е. во второй строке записаны данные Иванова, в третьей - Петрова и т.д.
Такая база данных может постоянно пополняться или модифицироваться. Например: в качестве исходной информации не >6язательно вводить заранее вычисленное значение Ф/Д. Можно было ввести только исходные поля ЖЕЛ Факт и ЖЕЛ Долж., а потом вычислить их соотношение. Для этого необходимо:
- в клетке F1 обозначить поле Ф/Д (если оно не было обозначено ранее);
-в клетку F2 ввести формулу =D2/Е2*100;
-скопировать содержимое клетки F2 в блок ячеек FЗ:F17.
После того, как исходная информация размещена на первом листе (в нижней части листа установлен переключатель Лист1) в cоответствиии с образцом (Рисунок 5), можно приступить к формированию макета сводной таблицы.
Для размещения сводной таблицы лучше всего использовать новый лист, Для этого переключите указатель в нижней чисти листа МS Ехсеl, на котором Вы находитесь, в положение Лист2. Затем, установите курсор в верхний левый угол листа (позиция А1). После этого через команду Вид и Панели инструментов нужно вызвать пакет Сводные таблицы. На экране появится окно инструментов (окно мастера) сводных таблиц (Рисунок 6).
Рисунок 6. Окно мастера свободных таблиц
Окно мастера сводных таблиц может располагаться непосредственно на листе, с левого или правого края, с низу или вверху листа. Для того, чтобы переместить его в наиболее удобное для работы место, нужно установить указатель мыши на поверхность окна мастера (Рисунок 6) и нажав левую клавишу мыши перетащить его в нужное место.
Затем последовательно выполните следующие действия:
1.Установите
указатель мыши на значок мастера таблиц
и
щелчком левой кнопки запустите мастер;
2.В открывшемся окне мастера сводных таблиц (Шаг 1 из 4) укажите источник информации, откуда будут поступать исходные данные. В данном примере необходимо указать: в списке или базе данных Мicrosoft Ехсеl, после чего нажмите кнопку Далее>;
Рисунок 7. Окно указателя диапазона исходных данных мастера таблиц, (шаг 2 из 4).
3. В открывшемся окне шаг 2 из 4 укажите диапазон исходных данных. В этот диапазон обязательно должна попасть первая строка с именами полей базы данных (Рисунок 7). После этого нажмите кнопку Далее>;
4. На экране появится следующее окно мастера таблиц. В этом окне, справа, указан перечень учетных признаков, которые можно включать в таблицу. Установите указатель мыши на нужном вам признаке, нажмите левую клавишу мыши и не отпуская её перетащите признак в нужное место. Расположите информацию так, как указано на рисунке (Рисунок 8). Признак Пол нужно расположить в области столбцов, признак Возраст - в области строк, признак Профессия - в области страниц, признак Ф/Д располагается в центральной области данных. Обратите внимание: для признака Ф/Д предусматривается вывод информации в виде количества значений (К-во значений признака). Если у Вас установлен вывод другой информации (среднее, сумма и т.д.), тогда установите указатель мыши на сообщение Ф/Д, дважды быстро щелкните правой клавишей и в открывшемся окне Вычисление поля сводной таблицы выберете операцию Кол-во значений и нажмите клавишу [ОК]
Рисунок 8. Мастер свободных таблиц (шаг 3 из 4)
После подготовки макета таблицы нажмите клавишу Готово. На экране появится групповая таблица распределения в абсолютных числах (Рисунок 9). Групповой эта таблица считается потому, что в ней приводится распределение по двум учетным признакам: пол и возраст. Если использовать переключатель Профессия, который установлен выше полученной таблицы, то можно рассматривать результаты распределения с учетом той или иной профессии. Таким образом распределение будет уже учитывать три группировочных признака. Таблица в этом случае становится комбинационной.
Рисунок 9. Таблица распределения в абсолютных числах
Для того чтобы выбрать с помощью переключателя третий группировочный признак, установите указатель мыши на значок t и щелкните правой клавишей. Затем выберите из появившегося перечня нужный вам признак.
Полученные результаты основаны на распределении показателей Ф/Д. Поскольку эти данные изначально были у каждой единицы наблюдения, то по ним можно судить о распределении всей анализируемой совокупности.
Для того, что бы получить распределение в любой другой форме производных величин нужно воспользоваться функцией Вычисление поля сводной таблицы.
Обратите внимание! Добиваться каких либо изменений в таблице можно только тогда, когда Вами выделена хотя бы одна клетка этой таблицы.
В окне мастера таблицы функцию вычисления поля таблицы можно вызвать значком. В открывшемся вторичном окне Вычисление поля сводной таблицы выберите операцию Среднее и нажмите клавишу [ОК]. В таблице после этой операции будет представлено распределение средних показателей Ф/Д (Рисунок 10). Для того, чтобы изменить числовой формат полученных результатов, нужно в окне Вычисление поля сводной таблицы выбрать операцию Формат... и задать необходимую размерность числового формата.
Аналогичным образом можно использовать для представления данных и другие производные величины (дисперсии, среднеквадратические отклонения и т.п.
Вычисление некоторых производных величины можно устанавливать через опцию Дополнительно» в окне Вычисление поля сводной таблицы. Для того чтобы получить, например, процентное распределение (распределение в экстенсивных показателях), необходимо предварительно указать поле, т.е. группировочный признак относительно которого будет производится расчет указанных величин.
Одной из самых употребительных функций мастера сводных таблиц является функция дополнительной группировки анализируемых признаков. Например, данные представленные в полученных ранее таблицах (Рисунок 9 и Рисунок 10), показывают распределение по достаточно мелким возрастным интервалам, т.е. практически по каждому отдельно взятому возрасту. Для объединения всех возрастных интервалов в 2 группы (от 40 до 49 лет и от 50 до 59 лет) необходимо выполнить следующие операции:
Установите указатель мыши в ту клетку столбца Возраст, в которой указан возраст 40. Затем нажав правую клавишу мыши и не отпуская её выделите диапазоны 40 42 43 44 47 49;
Вызовите из окна мастера сводных таблиц нажатием на клавишу =› команду Сгруппировать В таблице появится еще один столбик Возраст2 и в нем Группа 1. Аналогичным образом проведите группировку остальной части возрастных интервалов.
После того, как эта группировка выполнена уберите из таблицы столбик Возраст. Для этого установите указатель мыши на подпись столбца Возраст и не отпуская клавишу перетащите надпись на любое свободное место листа. После этого в таблице будет оставлен только столбик сгруппированных данных. Названия Группа 1 и Группа 2 можно изменить. Для этого выделите клетку таблицы с на нажмите на клавишу (клавиатуры компьютера) Р2 и введите нужное вам название, удалив все не нужное.
Изменения в группировках будут автоматически сохранены, и ими можно пользоваться при дальнейшей работе, вызывая их в необходимых случаях в окне мастера сводных таблиц (шаг 3 из 4).