Смотри лист из EXCEL! Группировка_промежуточные_ итоги
Министерство культуры российской федерации
Федеральное образовательное БЮДЖЕТНОЕ учреждение высшего профессионального образования
«Санкт-Петербургский государственный университет кино и телевиедния»
кАФЕДРА МАТЕМАТИЧЕСКОГО МОДЕЛИРОВАНИЯ
Математическое моделирование процесса статистической сводки и группировки в среде MS Excel
методическое пособие
по выполнению лабораторных работ
по дисциплине
«Основы статистики и бухгалтерского учета»
для студентов дневного и заочного отделения
факультета массовых коммуникаций
специальности «Связи с общественностью»
и бакалавриата «Реклама и связи с общественностью»
Санкт - Петербург
Год
ВВЕДЕНИЕ
Номер варианта контрольной работы, соответствует двум последним цифрам номера зачетки (шифра). Для шифров конечные цифры которых 70 и более, вариант определяется по сумме, двух последних цифр.
Например, для шифра оканчивающегося цифрами 65 № таблицы 10, факторный (группировочный) признак столбец №6, а Результативный признак столбец №13.
Например, для шифра оканчивающегося цифрами 70, вариант 7 № таблицы 2, факторный (группировочный) признак столбец №4, а Результативный признак столбец №13.
Содержание лабораторных работ:
Лабораторная работа № 1.
1. Лист 1. Исходные данные. Таблица 1.
2. Исходные данные, отсортированные по основному группировочному признаку. Лист 2 Сортировка. Таблица 2.
Лабораторная работа № 2.
3. Построение структурной группировки (по образцу таблиц 3, 4 и 5). Лист 3 Расчет для группировки.
4. Лист 4. Группировка с промежуточными итогами. (по образцу таблиц 6.7 и 8).
Лабораторная работа № 3.
5. Лист 5. Полигон. Таблицы 9 и 10. Рисунок 1. Полигон.
6. Лист 6. Гистограмма. Таблица 11 и Рисунок 2.
7. Лист 7. Эмпирическая функция.
Таблица 12 и 13. Рисунок 3. Эмпирическая функция.
Таблица 14 и Рисунок 4. Огива и кумулята.
Лабораторная работа № 4.
8. Лист 8. Группировка_проценты_к_итогу. Таблица 15 и 16.
9. Лист 9. Аналитическая_группировка. Таблица 17.
10. Лист 10. Группировка_2_признака. Таблица 18, 19 и 20.
11. Лист 11. Перегруппировка_и_вторичная_группировка Таблица 21 и 22.
Порядок выполнения контрольной работы:
Лабораторная работа № 1 «Сортировка»
1. На листе 1 сформировать таблицу 1 с исходными данными в соответствии со своим вариантом. Название листа Исходные данные.
2. Для дальнейших расчетов можно выбрать столбцы под № 1, 2, 3, а также столбцы с факторным и результативным признаками, в соответствии с вариантом.
| Таблица 1. | ||||||
| Основные показатели деятельности банков одной из областей РФ | ||||||
| № п/п | Сумма активов | Собственный капитал | Привлеченные ресурсы | Балансовая прибыль | Объем вложений в гос. ц./б | Ссудная задолженность |
| 645,6 | 27,1 | 8,1 | 3,5 | 30,8 | ||
| 636,9 | 70,4 | 56,3 | 9,5 | 12,6 | 25,7 | |
| 95,7 | 38,4 | 13,3 | 26,7 | |||
| 619,6 | 120,8 | 44,8 | 38,4 | 4,4 | 25,3 | |
| 616,4 | 49,4 | 109,7 | 13,4 | 20,9 | ||
| 614,4 | 50,3 | 108,1 | 30,1 | 19,1 | 47,3 | |
| 608,6 | 76,1 | 37,8 | 19,2 | 43,7 | ||
| 601,1 | 52,4 | 26,3 | 41,1 | 3,7 | 29,1 | |
| 600,2 | 9,3 | 5,2 | 56,1 | |||
| 27,3 | 24,4 | 39,3 | 13,1 | 24,9 | ||
| 592,9 | 65,5 | 8,6 | 16,7 | 39,6 | ||
| 591,7 | 22,4 | 40,5 | 7,5 | 59,6 | ||
| 585,5 | 29,3 | 106,9 | 45,3 | 6,7 | 44,9 | |
| 578,6 | 89,5 | 8,4 | 11,2 | 32,2 | ||
| 577,5 | 22,9 | 12,8 | 19,3 | 45,1 | ||
| 553,7 | 119,3 | 89,4 | 44,7 | 19,4 | 24,5 | |
| 543,6 | 49,6 | 93,8 | 8,8 | 5,7 | 31,1 | |
| 88,6 | 26,7 | 32,2 | 7,8 | 37,1 | ||
| 43,7 | 108,1 | 20,3 | 8,3 | 23,1 | ||
| 516,7 | 90,5 | 25,2 | 12,2 | 9,7 | 15,8 | |
| Итого | 1143,9 | 1379,6 | 499,2 | 221,4 | 683,5 |
3. На листе 2 сформировать таблицу для сортировки исходных данных. Название листа Сортировка.
4. Выполнить сортировку по основному группировочному признаку
Сортировку можно выполнить следующим образом: 1. Выделяем всю таблицу с исходными данными;
2. Меню – Данные - Сортировка;
3. Задаем ключ сортировки: сортировать в первую очередь по столбцу "Собственный капитал" – факторный признак, затем можно задать второй ключ, например, "Балансовая прибыль" – результативный признак.
В результате получим таблицу 2.
| Таблица 2. Сортировка исходных данных по величине собственного капитала | ||||||
| № п/п | Сумма активов | Собственный капитал | Привлеченные ресурсы | Балансовая прибыль | Объем вложений в гос. ц./б | Ссудная задолженность |
| 645,6 | 27,1 | 8,1 | 3,5 | 30,8 | ||
| 591,7 | 22,4 | 40,5 | 7,5 | 59,6 | ||
| 577,5 | 22,9 | 12,8 | 19,3 | 45,1 | ||
| 27,3 | 24,4 | 39,3 | 13,1 | 24,9 | ||
| 585,5 | 29,3 | 106,9 | 45,3 | 6,7 | 44,9 | |
| 95,7 | 38,4 | 13,3 | 26,7 | |||
| 600,2 | 9,3 | 5,2 | 56,1 | |||
| 43,7 | 108,1 | 20,3 | 8,3 | 23,1 | ||
| 616,4 | 49,4 | 109,7 | 13,4 | 20,9 | ||
| 543,6 | 49,6 | 93,8 | 8,8 | 5,7 | 31,1 | |
| 614,4 | 50,3 | 108,1 | 30,1 | 19,1 | 47,3 | |
| 601,1 | 52,4 | 26,3 | 41,1 | 3,7 | 29,1 | |
| 578,6 | 89,5 | 8,4 | 11,2 | 32,2 | ||
| 608,6 | 76,1 | 37,8 | 19,2 | 43,7 | ||
| 636,9 | 70,4 | 56,3 | 9,5 | 12,6 | 25,7 | |
| 592,9 | 65,5 | 8,6 | 16,7 | 39,6 | ||
| 88,6 | 26,7 | 32,2 | 7,8 | 37,1 | ||
| 516,7 | 90,5 | 25,2 | 12,2 | 9,7 | 15,8 | |
| 553,7 | 119,3 | 89,4 | 44,7 | 19,4 | 24,5 | |
| 619,6 | 120,8 | 44,8 | 38,4 | 4,4 | 25,3 | |
Лабораторная работа №2. «СТРУКТУРНАЯ ГРУППИРОВКА»
5. Для последующих этапов необходимы предварительные расчеты.
| Перед расчетами необходимо преобразовать формулу Стерджесса: LOG2N = LOG210* LOG10N |
Причем LOG210 = 3.322 – это стандартная величина. Далее выполнить расчеты в соответствии с таблицей 3.
6. На лист 3 скопировать таблицу 2 с отсортированными данными и перенумеровать таблицу на таблица 3. Название листа Расчет для группировки.
7. Выполнить расчеты для определения количества единиц банков в каждой группе, сохраняя диапазон условий для всех групп.
| Расчеты для группировки | Таблица 3 | ||||
| Название | Обозначение | Значения | Формула в Excel: | ||
| Максимальное значение признака в группировке | Хmax= | 120,8 | МАКС | ||
| Минимальное значение признака в группировке | Xmin= | МИН | |||
| Общее число банков | N= | ||||
| Логарифм числа банков | lgN= | 1,30103 | LOG10 | ||
| Количество групп | k=1+3,322*lgN= | 5,322022 | |||
| Величина интервала группировки (шаг) | h=(Xmax-Xmin)/k= | 21,76 | |||
| Для получения границ интервалов будем использовать значение шага интервала h. Начинаем с минимального значения признака в совокупности. Нижняя граница предыдущего интервала соответствует верхней границе последующего. Значение шага h можно взять из табл.3 и использовать его в формуле в виде ссылки с абсолютным адресом. Преобразование в абсолютный адрес получается при нажатии клавиши F4. Формулы для расчета представлены в таблице 4 ниже: Т аблица 4 Расчеты границ интервалов | |||||
| Граница | Номер группы | ||||
| Xmin | X1 = Xmin + h | ||||
| X1 | X2 = X1 + h | ||||
| X2 | X3 = X2 + h | ||||
| X3 | X4 = X3 + h | ||||
| X4 | X5 = X4 + h |
Итак, распределим банки по группам и подсчитаем число банков в каждой из них. Для этого можно использовать стандартные функции работы с базами данных в Excel.
В качестве примера найдем количество банков, входящих в 1-ую группу.
1) Сначала сформируем диапазон условий - отдельную таблицу, в которой покажем по какому условию нужно отбирать банки. Причем условие И указывается в одной строке диапазона условий, а условие ИЛИ в разных. Заголовки столбцов, по которым происходит расчет, указывать обязательно!
| Диапазон условий: | ||
| Собственный капитал | Собственный капитал | |
| >=12 | <=33,76 |
2) Затем ставим курсор в ячейку для размещения результата и вызываем мастер функций кнопкой fх
3) Выбираем категорию функций: Работа с базой данных.
4) Выбираем функцию: БСЧЁТ и заполняем поля ввода:
| База_данных | A3:G23 | |
| Поле | C3 | |
| Критерий | B40:C41 |
Замечания:
1) При заполнении параметра "База_данных" нужно выделить всю исходную таблицу вместе с заголовками столбцов;
2) Параметр "Поле" для этой функции является необязательным, можно указать имя столбца, по которому происходит расчет.
3) При заполнении параметра "Критерий" указывается диапазон условий вместе с заголовками столбцов.
Таблица 5.
| Обозначим численные границы групп: | ||||
| Граница | Группа | Число банков | ||
| 33,76 | ||||
| 33,76 | 55,52 | |||
| 55,52 | 77,28 | |||
| 77,28 | 99,04 | |||
| 99,04 | 120,8 | |||
| N= |
Результат появиться в 1-ой строке таблицы 5 в последнем столбце, то есть число банков для группы 1 (для нашего примера это цифра 5). Аналогично найдем число банков для остальных групп. Каждый раз будем заново формировать диапазон условий.
8. На лист 4 скопировать таблицу 3 с листа 3, получим таблицу 6. Добавим в таблицу 6 столбец «Группа». Название листа Группировка с промежуточными итогами.
| Таблица 6. | ||||||||||||
| Группировка банков по величине собственного капитала с расчетом промежуточных итогов по каждой группе | ||||||||||||
| № п/п | Группа | Сумма активов | Собственный капитал | Привлеченные ресурсы | Балансовая прибыль | Объем вложе-ний в гос. ц/б | Ссудная задолжен-ность | |||||
| 645,6 | 27,1 | 8,1 | 3,5 | 30,8 | ||||||||
| 591,7 | 22,4 | 40,5 | 7,5 | 59,6 | ||||||||
| 577,5 | 22,9 | 12,8 | 19,3 | 45,1 | ||||||||
| 27,3 | 24,4 | 39,3 | 13,1 | 24,9 | ||||||||
| 585,5 | 29,3 | 106,9 | 45,3 | 6,7 | 44,9 | |||||||
| 95,7 | 38,4 | 13,3 | 26,7 | |||||||||
| 600,2 | 9,3 | 5,2 | 56,1 | |||||||||
| 43,7 | 108,1 | 20,3 | 8,3 | 23,1 | ||||||||
| 616,4 | 49,4 | 109,7 | 13,4 | 20,9 | ||||||||
| 543,6 | 49,6 | 93,8 | 8,8 | 5,7 | 31,1 | |||||||
| 614,4 | 50,3 | 108,1 | 30,1 | 19,1 | 47,3 | |||||||
| 601,1 | 52,4 | 26,3 | 41,1 | 3,7 | 29,1 | |||||||
| 578,6 | 89,5 | 8,4 | 11,2 | 32,2 | ||||||||
| 608,6 | 76,1 | 37,8 | 19,2 | 43,7 | ||||||||
| 636,9 | 70,4 | 56,3 | 9,5 | 12,6 | 25,7 | |||||||
| 592,9 | 65,5 | 8,6 | 16,7 | 39,6 | ||||||||
| 88,6 | 26,7 | 32,2 | 7,8 | 37,1 | ||||||||
| 516,7 | 90,5 | 25,2 | 12,2 | 9,7 | 15,8 | |||||||
| 553,7 | 119,3 | 89,4 | 44,7 | 19,4 | 24,5 | |||||||
| 619,6 | 120,8 | 44,8 | 38,4 | 4,4 | 25,3 | |||||||
9. Далее выполнить расчет промежуточных итогов по каждой группе. Для расчета структурной группировки можно использовать возможности Excel по определению промежуточных итогов в списках. Для этого в таблицу и нужно добавить столбец "Группа", позволяющий отбирать данные каждой группы в отдельности.
10. Затем нужно задать параметры для вычисления промежуточных итогов:
1) Помещаем курсор в список или выделяем всю таблицу вместе с заголовком столбцов.
2) Выбираем Меню - Данные - Итоги. 3) В появившемся окне "Итоги" задаем:
| Промежуточные итоги | |
| При каждом изменении в | |
| Группа | Ñ |
| Операция | |
| Количество | Ñ |
| Добавить итоги по | |
| Группа | Ñ |
Замечания: 1. При заполнении параметра "При каждом изменении в…" выбирается тот столбец, при изменении значения которого и следует вычислять итоги. 2. Второй параметр для заполнения "Операция" дает возможность выбрать из списка нужную операцию для расчета итогов. Для нашего примера сначала рекомендуется рассчитать количество записей для столбца группа (операция "Количество"), затем рассчитать сумму для всех остальных столбцов (операция "Сумма"). 3. Последний параметр "Добавить итоги по..." позволяет выбрать из списка всех столбцов в таблице, те по которым необходимо рассчитывать промежуточные итоги (поставив флажок напротив выбранного названия столбца), в нашем примере можно выбрать все столбцы кроме "№ п/п".
В результате получим таблицу 7.
Смотри лист из EXCEL! Группировка_промежуточные_ итоги
Теперь можно скопировать все промежуточные итоги и мы получим таблицу с готовой структурной группировкой. При этом при вставке значений в новую таблицу нужно использовать следующие параметры вставки:
1. Нажать правую клавишу мыши. 2. В контекстном меню выбрать: Специальная вставка. 3. Затем выбрать: Вставить Значения, указав соответствующий флажок.
В результате получим таблицу 8.
| Таблица 8. | |||||||||||||||
| Структурная группировка банков по величине собственного капитала | |||||||||||||||
| № группы | Число банков ед. | Сумма активов | Собствен-ный капитал | Привлечен-ные ресурсы | Балансовая прибыль | Объем вложений в гос. ц./б | Ссудная задолжен-ность | ||||||||
| 3000,3 | 113,9 | 318,4 | 50,1 | 205,3 | |||||||||||
| 4121,7 | 328,4 | 587,7 | 161,4 | 70,3 | 234,3 | ||||||||||
| 282,4 | 287,4 | 64,3 | 59,7 | 141,2 | |||||||||||
| 1058,7 | 179,1 | 51,9 | 44,4 | 17,5 | 52,9 | ||||||||||
| 1173,3 | 240,1 | 134,2 | 83,1 | 23,8 | 49,8 | ||||||||||
| ИТОГО | 1143,9 | 1379,6 | 499,2 | 221,4 | 683,5 | ||||||||||
Лабораторная работа № 3. «Построение статистических графиков»
3.1. «Полигон»
Полигон в статистике график – это ломанная линия, соединяющая точки с координатами по оси абсцисс - значение варианты признака в середине интервала (X) и по оси ординат - значение частоты n.
Для построения используем данные таблицы 7.
На лист 5 скопируем таблицу 7 название листа Полигон. По данным таблицы 7 построим дополнительную таблицу 9.
Таблица 9.
| Интервал (группы банков по величине собственного капитала), [a,b] |   Середина интервала | Частота, n; (число банков в группе) | ||
| 33,76 | 22,88 | |||
| 33,76 | 55,52 | 44,64 | ||
| 55,52 | 77,28 | 66,4 | ||
| 77,28 | 99,04 | 88,16 | ||
| 99,04 | 120,8 | 109,92 |
Для построения полигона необходимо добавить в исходные данные еще две точки, а именно:
1) точку начала первого интервала X0 = 2,5, этой точке соответствует ордината - частота ni = 0.
2) точку конца последнего интервала Хmax = 12,5; этой точке также соответствует частота ni = 0.
В результате будем строить полигон по дополненной таблице 10.
Таблица 10.
| Частота, n; (число банков в группе) | ||||
| 22,88 | |||||
| 44,64 | |||||
| 66,4 | |||||
| 88,16 | |||||
| 109,92 | |||||
| 120,8 |
Середина интервалаПорядок построения полигона:
1. Выделяем в таблице 9 данные необходимые для построения полигона столбцы 1 и 2 без заголовков столбцов.
2. Вызываем мастер построения диаграмм в Excel - кнопка в виде гистограммы на панели инструментов.
3. Задаем тип диаграммы - Точечная и вид диаграммы - точечная, на которой значения соединены отрезками. Переход к следующему шагу построения - кнопка Далее.
В результате получим рисунок 1.
Рис. 1. Полигон частот.
3.2. «Гистограмма»
Для построения гистограммы в Excel нужно уметь строить гистограмму вручную.
Гистограмма в статистике - это фигура в виде соединенных столбцов, основанием столбца служат интервалы, а высотой – частоты.
Гистограмма в Excel - это отдельно стоящие столбцы, заданной в программе Excel ширины, а высота может быть и частотой, как нам необходимо.
Для правильного построения гистограммы для статистического исследования используем следующий порядок действий:
1. Добавляем лист для построения гистограммы – название листа Гистограмма. Заполняем дополнительную таблицу для построения гистограммы - таблицу 11. В этой таблице расписываем все четыре точки для построения одного столбца. Например, для построения первого столбца в таблице 11 есть 4 точки с № 1,2,3,4. Для построения второго и последующего столбцов уже можно использовать всего 3 точки, так как конец предыдущего интервала совпадает с началом последующего. Данные копируем из таблицы 9. В результате получаем:
| Таблица 11. | |||
| Построение гистограммы для статистического исследования | |||
| № п/п | Величина собственного капитала | частота, n, число банков | |
| 33,76 | |||
| 33,76 | |||
| 33,76 | |||
| 55,52 | |||
| 55,52 | |||
| 55,52 | |||
| 77,28 | |||
| 77,28 | |||
| 77,28 | |||
| 99,04 | |||
| 99,04 | |||
| 99,04 | |||
| 120,8 | |||
| 120,8 |
2. Выделяем в таблице 11 данные необходимые для построения гистограммы столбцы 2 и 3 вместе с заголовками столбцов.
3. Вызываем мастер построения диаграмм в Excel - кнопка в виде гистограммы на панели инструментов.
4. Задаем тип диаграммы - Точечная и вид диаграммы - Точечная на которой значения соединены отрезками. Переход к следующему шагу построения - кнопка Далее.
5. На следующем шаге можно задать диапазон данных, расположение рядов - в столбцах, а также название диаграммы, название осей и др.
6. Затем выбираем место размещения диаграммы на имеющимся листе - Гистограмма или на отдельном. Завершение построения кнопка – Готово.
7. После построения можно отредактировать диаграмму, используя контекстное меню правой кнопки мыши.
В результате получим рисунок 2.
Рисунок 2. Гистограмма частот.
3.3. «Эмпирическая функция распределения, огива и кумулята.»
Добавим лист для построения эмпирической функции – название листа Эмпирическая функция. Построение эмпирической функции распределения вручную предполагает дополнительные расчеты. Для начала рассчитаем относительные частоты, используя данные таблицы 9.
Получим таблицу 12.
| Таблица 12. | ||||||
| Расчет относительных и накопленных частот | ||||||
| Интервал (группы банков по величине собственного капитала), [a,b] | Частота, ni, число банков в интервале | Относительная частота | Накопленная частота | |||
| a | b | ni |
|  | ||
| 33,76 | 0,25 | 0,25 | ||||
| 33,76 | 55,52 | 0,35 | 0,25+0,35=0,6 | |||
| 55,52 | 77,28 | 0,2 | 0,25+0,35+0,2=0,8 | |||
| 77,28 | 99,04 | 0,1 | 0,8+0,1=0,9 | |||
| 99,04 | 120,8 | | 0,1 | 0,9+0,1=1,0 | ||
| Итого |  |  | ||||
| Сумма частот |
Также необходимо построение таблицы накопленных относительных частот для двух концов каждого интервала. Результаты показаны в таблице 13.
| Таблица 13. | ||
| Номер отрезка и ряда для построения эмпирической функции | Значение признака Х по концам интервала | Значение эмпирической функции распределения (сумма накопленных частот для всех предыдущих интервалов) |
| 0,25 | ||
| 33,76 | 0,25 | |
| 33,76 | 0,6 | |
| 55,52 | 0,6 | |
| 55,52 | 0,8 | |
| 77,28 | 0,8 | |
| 77,28 | 0,9 | |
| 99,04 | 0,9 | |
| 99,04 | ||
| 120,8 |
Для построения эмпирической функции распределения в Excel по указанной таблице 13 выполним следующие действия:
1) Выделяем в таблице 13 данные необходимые для построения эмпирической функции вместе с заголовками столбцов.
2) Вызываем мастер построения диаграмм в Excel - кнопка в виде гистограммы на панели инструментов.
3) Задаем тип диаграммы - Точечная и вид диаграммы - Точечная на которой значения соединены отрезками. Переход к следующему шагу построения - кнопка Далее.
4) Каждый из 6 рядов будем задавать отдельно, используя возможности вкладки Ряд. Нажимая каждый раз кнопку Добавить и задавая значения Х и У для каждого ряда.
5) В результате мы получим 6 отрезков на разной высоте, соответствующей накопленной частоте для каждого интервала. Результаты представлен на рис.3.
Рис.3. Построение эмпирической функции распределения.
Для построения огивы и кумуляты необходимо по-разному соединить точки указанных выше отрезков.
Если соединить начальные точки каждого отрезка, то мы получим огиву, если соединить конечные точки – кумуляту.
Для построения огивы и кумуляты в Excel нужно построить дополнительную таблицу для получения кривых.
| Таблица 14. | ||||
| Дополнительная таблица для построения кривых | ||||
| Построение огивы | Построение кумуляты | |||
| Х | У | Х | У | |
| 0,25 | ||||
| 33,76 | 0,6 | 33,76 | 0,25 | |
| 55,52 | 0,8 | 55,52 | 0,6 | |
| 77,28 | 0,9 | 77,28 | 0,8 | |
| 99,04 | 99,04 | 0,9 | ||
| 120,8 | 120,8 |
1. Для построения огивы мы выделяем первые два столбца дополнительной таблицы, для построения кумуляты столбцы 3 и 4.
2. Для добавления рядов Огива и Кумулята на один график с эмпирической функцией нужно выделить всю диаграмму (она будет выделена 8 маркерами выделения), затем нажать правую кнопку мыши и выбрать в контекстном меню: Исходные данные.
3. В появившемся окне Исходные данные выбрать вкладку Ряд и нажать кнопку Добавить.
4. Затем задать Имя (огива), значения Х (цифры из столбца 1) и значения У (цифры из столбца 2) для огивы .
5. Задать Имя, значения Х, значения У для кумулляты.
6. В результате график будет выглядеть как на рисунке 4.
Рис.4. Эмпирическая функция распределения, огива и кумулята.
Лабораторная работа № 4
«Исследование структуры совокупности. Построение структурной группировки в процентах к итоговым значениям»
Добавим лист с названием Группировка_проценты_к_итогу. Заменим абсолютные показатели структуры на относительные. Для построения структурной группировки в процентах к итогу добавим в таблицу 8 столбцы с границами интервалов по величине собственного капитала и получим таблицу 15.
Для получения таблицы 16 будем использовать данные табл.15. Используем следующую формулу для расчета доли в процентах по каждому показателю: (Адрес ячейки с показателем)*100/(Абсолютный адрес ячейки с итоговым показателем). Например, для столбца "Собственный капитал" результат в ячейке F21 рассчитан по формуле: F4*100/$F$9.
| Таблица 15. | |||||||||
| Структурная группировка банков по величине собственного капитала | |||||||||
| № группы | Группы банков (границы интервала) | Число банков, ед. | Сумма активов | Собственный капитал | Привлеченные ресурсы | Балансовая прибыль | Объем вложений в гос. ц./б | Ссудная задолженность | |
| 33,76 | 3000,3 | 113,9 | 318,4 | 50,1 | 205,3 | ||||
| 33,76 | 55,52 | 4121,7 | 328,4 | 587,7 | 161,4 | 70,3 | 234,3 | ||
| 55,52 | 77,28 | 282,4 | 287,4 | 64,3 | 59,7 | 141,2 | |||
| 77,28 | 99,04 | 1058,7 | 179,1 | 51,9 | 44,4 | 17,5 | 52,9 | ||
| 99,04 | 120,8 | 1173,3 | 240,1 | 134,2 | 83,1 | 23,8 | 49,8 | ||
| ИТОГО | 1143,9 | 1379,6 | 499,2 | 221,4 | 683,5 |
| Таблица 16. | |||||||||
| Структурная группировка банков по величине собственного капитала в процентах к итоговым значениям | |||||||||
| № группы | Группы банков (границы интервала) | Число банков, % к итогу | Сумма активов, % к итогу | Собственный капитал, % к итогу | Привлеченные ресурсы, % к итогу | Балансовая прибыль, % к итогу | Объем вложений в гос. ц/б, % к итогу | Ссудная задолженность, % к итогу | |
| 33,76 | 25,49 | 9,96 | 23,08 | 29,25 | 22,63 | 30,04 | |||
| 33,76 | 55,52 | 35,02 | 28,71 | 42,60 | 32,33 | 31,75 | 34,28 | ||
| 55,52 | 77,28 | 20,53 | 24,69 | 20,83 | 12,88 | 26,96 | 20,66 | ||
| 77,28 | 99,04 | 8,99 | 15,66 | 3,76 | 8,89 | 7,90 | 7,74 | ||
| 99,04 | 120,8 | 9,97 | 20,99 | 9,73 | 16,65 | 10,75 | 7,29 | ||
| ИТОГО в % | 100,00 | 100,00 | 100,00 | 100,00 | 100,00 | 100,00 |
Лабораторная работа № 5.
«Исследование зависимости факторного и результативного признаков.
Построение аналитической группировки»
Аналитическая группировка — это исследование взаимосвязей варьирующих признаков в пределах однородной совокупности. При ее построении можно установить взаимосвязи между двумя и более признаками. При этом один признак будет результативным, а другой (другие) – факторным.
Факторными называются признаки, оказывающие влияние на изменение результативных. Результативными называются признаки, которые изменяются под влиянием факторных.
Для получения таблицы 17 с аналитической группировкой оставим для расчета только два признака факторный - "собственный капитал" и результативный - "балансовая прибыль".
Добавим лист с названием Аналитическая_группировка. Рассчитаем значение обоих признаков в среднем на один банк. Для этого используем формулу: (Адрес ячейки со значением "всего")/(адрес ячейки с числом банков в этой группе). Например, для ячейки E5 формула записывается следующим образом: E5/D5.
В результате получаем таблицу 17.
| Таблица 17. | |||||||
| Зависимость балансовой прибыли от величины собственного капитала для совокупности банков | |||||||
| № группы | Группы банков (границы интервала) | Число банков, ед. | Собственный капитал | Балансовая прибыль | |||
| всего | в среднем на один банк | всего | в среднем на один банк | ||||
| 33,76 | 113,9 | 22,78 | 29,20 | ||||
| 33,76 | 55,52 | 328,4 | 46,91 | 161,4 | 23,06 | ||
| 55,52 | 77,28 | 282,4 | 70,60 | 64,3 | 16,08 | ||
| 77,28 | 99,04 | 179,1 | 89,55 | 44,4 | 22,20 | ||
| 99,04 | 120,8 | 240,1 | 120,05 | 83,1 | 41,55 | ||
| ИТОГО | 1143,9 | 499,2 | |||||
| в среднем на один банк по всей совокупности | 57,20 | 24,96 |
Лабораторная работа № 6.
«Исследование зависимости построения группировки по двум признакам»
Проведем группировку данных коммерческих банков по двум признакам величине собственного капитала и балансовой прибыли. Каждую группу и подгруппу охарактеризуем следующими показателями: число коммерческих банков, собственный капитал и балансовая прибыль.
1. Добавим лист с названием Группировка_2_признака.
2. Затемскопируем табл. 6 с листа Группировка с промежуточными итогами в табл.18, оставим для дальнейших расчетов столбцы: № п/п, Группа, Собственный капитал и Балансовая прибыль, затем проведем сортировку по величине балансовой прибыли в каждой группе отдельно. Для этого добавим заголовки столбцов перед значениями каждой группы.
| Таблица 18. Расчеты для построения группировки по двум признакам | |||
| № п/п | Группа | Собственный капитал | Балансовая прибыль |
| 8,1 | |||
| 22,4 | 40,5 | ||
| 22,9 | 12,8 | ||
| 27,3 | 39,3 | ||
| 29,3 | 45,3 | ||
| № п/п | Группа | Собственный капитал | Балансовая прибыль |
| 38,4 | |||
| 9,3 | |||
| 43,7 | 20,3 | ||
| 49,4 | 13,4 | ||
| 49,6 | 8,8 | ||
| 50,3 | 30,1 | ||
| 52,4 | 41,1 | ||
| № п/п | Группа | Собственный капитал | Балансовая прибыль |
| 8,4 | |||
| 37,8 | |||
| 70,4 | 9,5 | ||
| 8,6 | |||
| № п/п | Группа | Собственный капитал | Балансовая прибыль |
| 88,6 | 32,2 | ||
| 90,5 | 12,2 | ||
| № п/п | Группа | Собственный капитал | Балансовая прибыль |
| 119,3 | 44,7 | ||
| 120,8 | 38,4 |
Далее выполним сортировку: Меню-Данные-Сортировка (ключ сортировки - Балансовая прибыль, затем – Группа).
Затем, для определения количества подгрупп по балансовой прибыли, внутри каждой группы по собственному капиталу проведем операции аналогичные тем, которые проводили для структурной группировки по величине собственного капитала (см. лист "Сортировка"), а именно: находим максимальное, минимальное значение балансовой прибыли, рассчитываем количество банков в каждой подгруппе с помощью функции СЧЕТ по столбцу Группа. Указанная функция позволяет рассчитывать количество чисел в списке аргументов.
Для расчета количества подгрупп k используем функцию ОКРУГЛ, которая округляет число до указанного количества десятичных разрядов, при указании число разрядов 0, округление происходит до ближайшего целого. У функции 2 аргумента: число и количество разрядов. В качестве примера рассмотрим расчеты для группы 1 (см. таблица 19).
| Таблица 19. | ||||||
| Расчеты для группировки банков по величине собственного капитала с сортировкой по балансовой прибыли | ||||||
| № п/п | Группа | Собственный капитал | Балансовая прибыль | Обозначение | значение | Формула в Excel: |
| 8,1 | Хmax= | 45,30 | МАКС | |||
| 22,9 | 12,8 | Xmin= | 8,10 | МИН | ||
| 27,3 | 39,3 | количество банков в группе N= | 5,00 | СЧЕТ | ||
| 22,4 | 40,5 | lgN= | 0,70 | LOG10 | ||
| 29,3 | 45,3 | k=1+3,322*lgN= | 3,00 | ОКРУГЛ(1+3,322*F18;0) | ||
| h=(Xmax-Xmin)/k= | 12,40 |
Затем аналогично расчетам в листе "Расчеты для группировки" проводим разделение на подгруппы в каждой группе. Результаты заносим в табл. 20.
| Таблица 20 | ||||||||
| Зависимость балансовой прибыли от величины собственного капитала для совокупности банков с группировкой по балансовой прибыли | ||||||||
| № группы | Группы банков (границы интервала) по величине собственного капитала | В том числе подгруппы по величине балансовой прибыли | Число банков, ед. | Собственный капитал | Балансовая прибыль | |||
| верхняя | нижняя | верхняя граница | нижняя граница | |||||
| 33,76 | 8,10 | 20,50 | 34,9 | 20,9 | ||||
| 20,50 | 32,90 | |||||||
| 32,9 | 45,30 | 125,1 | ||||||
| итого по группе | 113,9 | |||||||
| 33,76 | 55,52 | 328,4 | 161,4 | |||||
| 55,52 | 77,28 | 282,4 | 64,3 | |||||
| 77,28 | 99,04 | 179,1 | 44,4 | |||||
| 99,04 | 120,8 | 240,1 | 83,1 | |||||
| ИТОГО | 1143,9 | 499,2 |
Далее рассчитаем столбец "Число банков". Для этого используем функцию из категории "Работа с базами данных" БСЧЕТ, аналогично расчету для структурной группировки в листе "Расчет для группировки". При этом в качестве базы данных используется диапазон только одной группы с применеием абсолютной адресации, по которой проходит разделение на подгруппы. Выделяется диапазон вместе с названием столбцов. В качестве поля выбирается название столбца "Балансовая прибыль", адрес также делают абсолютным. Диапазон условий для критерия каждый раз строится заново с использованием нижней и верхней границ для каждой подгруппы.
В качестве примера рассмотрено выделение подгрупп в группе №1. Здесь для получения величины балансовой прибыли и собственного капитала необходимы дополнительные расчеты. Если нужно получить величину только балансовой прибыли, то можно использовать функцию категории "Работа с базой данных", БДСУММ. Аргументы вводят аналогично рассчитанному выше столбцу "число банков"
Если необходимо получить итоговые значения в каждой подгруппе по всем показателям, то нужно использовать возможность Расширенного фильтра. Для применения Расширенного фильтра нужно выполнить следующие действия:
1. Выделить диапазон только для одной группы, для которой выделяем подгруппы.
2. Выбрать Меню - Данные - Фильтр - Расширенный фильтр.
3. В появившемся окошке Расширенного фильтра задать аргументы для фильтрации.
| Исходный диапазон | Диапазон для группы № 1 |
| Диапазон условий | Соответствующий диапазон для границ каждой подгруппы |
| Поместить результат в диапазон | Указать диапазон размещения результата отличный от исходного диапазона |
Обязательно установить флажок: Скопировать результат в другое место
Затем в таблицу с результатом фильтрации добавить итоги по выбранной подгруппе по всем показателям. Количество необходимых фильтраций соответствует количеству подгрупп в группе.
Для примера рассмотрим диапазон условий и полученный результат при применении расширенного фильтра для группы 1 подгруппы 1 по балансовой прибыли.
Диапазон условий
| Балансовая прибыль | Балансовая прибыль | |
| >=8,1 | <=20,5 |
Полученный результат:
| № п/п | Группа | Собственный капитал | Балансовая прибыль |
| 8,1 | |||
| 22,9 | 12,8 |
Затем подсчитаем итоговые цифры для собственного капитала и балансовой прибыли для группы 1 подгруппы 1 по балансовой прибыли.
| № п/п | Группа | Собственный капитал | Балансовая прибыль |
| 8,1 | |||
| 22,9 | 12,8 | ||
| ИТОГО | 34,9 | 20,9 |
Результаты заносим в таблицу 20. Аналогичные действия проводим для всех подгрупп группы 1, затем для всех подгрупп группы 2 и т.д. Таким образом, заполняем таблицу 20 до конца.
Лабораторная работа № 7
«Перегруппировка и построение вторичной группировки»
Группировки, построенные за один и тот же период времени, но для разных регионов или, наоборот, для одного региона, но за два разных периода времени, могут оказаться несопоставимыми из-за различного числа выделенных групп или неодинаковости границ интервалов.
В таком случае необходима перегруппировка данных с помощью вторичной группировки.
Вторичная группировка — операция по образованию новых групп на основе ранее осуществленной группировки.
Для получения вторичной группировки добавим лист с названием Перегруппировка.
Для построения вторичной группировки нужно провести перегруппировку данных табл. 2 Структурная группировка. Уменьшим количество групп в группировке до 3 и определим новое значение шага.
| Хmax= | 120,8 | |
| Xmin= | ||
| k= | ||
| h=(Xmax-Xmin)/k= | 36,26666667 |
Далее определим границы новых интервалов, используя новое значение шага. Результаты оформить в виде таблицы по аналогии с проведением структурной группировки в лабораторной работе № 2.
Для подсчета числа банков в каждом из новых интервалов используем функции Excel. Выбираем категорию функций: Работа с базой данных.
Выбираем функцию: БСЧЁТ и заполняем поля ввода
| База_данных | A22:G42 | Диапазон ячеек размещения всей таблицы вместе с заголовками |
| Поле | C22 | Адрес ячейки с названием столбца, по которому проходит перегруппировка «Собственный капитал» |
| Критерий | F7:G8 | Диапазон условий для нового интервала |
Например, диапазон условий для получения числоа банков в новой группе 1 выглядит следующим образом:
| Диапазон условий: | ||
| Собственный капитал | Собственный капитал | |
| >=12 | <=48,27 |