Смотри лист из EXCEL!Группировка_промежуточные_ итоги

Далее ⇒

Министерство культуры российской федерации

Федеральное образовательное БЮДЖЕТНОЕ учреждение высшего профессионального образования

«Санкт-Петербургский государственный университет кино и телевиедния»

кАФЕДРА МАТЕМАТИЧЕСКОГО МОДЕЛИРОВАНИЯ

Математическое моделирование процесса статистической сводки и группировки в среде MS Excel

учебно-методическое пособие

по выполнению контрольной работы

по дисциплине

«Основы статистики и бухгалтерского учета»

для студентов дневного и заочного отделения

факультета массовых коммуникаций

специальности «Связи с общественностью»

и бакалавриата «Реклама и связи с общественностью»

Санкт - Петербург

Год

ВВЕДЕНИЕ

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

Например для шифра оканчивающегося цифрами 65 № таблицы 10, факторный (группировочный) признак столбец №6, а Результативный признак столбец №13.

Например, для шифра оканчивающегося цифрами 70, вариант 7 № таблицы 2, факторный (группировочный) признак столбец №4, а Результативный признак столбец №13.

Содержание контрольной работы:

1. Исходные данные.

2. Исходные данные, отсортированные по основному группировочному признаку (табл. 1).

3. Построение структурной группировки (по образцу таблиц 2,3 и 4).

4. Построение аналитической группировки по двум признакам факторному и результативному (табл. 5 и 6).

5. Построение графиков распределение (табл.10, 11).

5.1. Полигон частот (рис.3)

5.2. Гистограмма частот (рис.4).

5.3. Эмпирическая функция распределения, огива и кумулята (рис.5).

6. Вторичная группировка (табл.12 и 13).

7. Выводы.

8. Список использованных источников.

Порядок выполнения контрольной работы:

Часть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

Рис. 1. Вид листа Исходные данные в EXCEL.

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. Для последующих этапов необходимы предварительные расчеты.

Перед расчетами необходимо преобразовать формулу Стерджесса: LOG₂N = LOG₂10* LOG₁₀N

Причем LOG₂10 = 3.322 – это стандартная величина. Далее выполнить расчеты в соответствии с таблицей 3.

Расчеты для группировки	Таблица 3
Название	Обозначение	Значения	Формула в Excel:
Максимальное значение признака в группировке	Хmax=	120,8	МАКС
Минимальное значение признака в группировке	Xmin=		МИН
Общее число банков	N=
Логарифм числа банков	lgN=	1,30103	LOG10
Количество групп	k=1+3,322*lgN=	5,322022
Величина интервала группировки (шаг)	h=(Xmax-Xmin)/k=	21,76

6. На лист 3 скопировать таблицу 2 с отсортированными данными и перенумеровать таблицу на таблица 3. Название листа Расчет для группировки.

7. Выполнить расчеты для определения количества единиц банков в каждой группе, сохраняя диапазон условий для всех групп.

Для получения границ интервалов будем использовать значение шага интервала h. Начинаем с минимального значения признака в совокупности. Нижняя граница предыдущего интервала соответствует верхней границе последующего. Значение шага h можно взять из табл.3 и использовать его в формуле в виде ссылки с абсолютным адресом. Преобразование в абсолютный адрес получается при нажатии клавиши F4. Формулы для расчета представлены в таблице 4 ниже: Т аблица 4 Расчеты границ интервалов




	Граница	Номер группы
	Xmin	X₁=Xmin + h
	X₁	X₂=X₁ + h
	X₂	X₃=X₂ + h
	X₃	X₄=X₃ + h
	X₄	X₅=X₄ + 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) точку начала первого интервала X₀ = 2,5, этой точке соответствует ордината - частота n_i = 0.

2) точку конца последнего интервала Х_max = 12,5; этой точке также соответствует частота n_i = 0.

В результате будем строить полигон по дополненной таблице 10.

Таблица 10.

Середина интервала

Частота, n; (число банков в группе)

22,88

44,64

66,4

88,16

109,92

120,8

Порядок построения полигона:

1. Выделяем в таблице 9 данные необходимые для построения полигона столбцы 1 и 2 без заголовков столбцов.

2. Вызываем мастер построения диаграмм в Excel - кнопка в виде гистограммы на панели инструментов.

3. Задаем тип диаграммы - Точечная и вид диаграммы - точечная, на которой значения соединены отрезками. Переход к следующему шагу построения - кнопка Далее.

В результате получим рисунок 2.

Рис. 2. Полигон частот.

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. После построения можно отредактировать диаграмму, используя контекстное меню правой кнопки мыши.

В результате получим рисунок 3.

Рисунок 3. Гистограмма частот.

3.3. Эмпирическая функция распределения, огива и кумулята.

Добавим лист для построения эмпирической функции – название листа Эмпирическая функция. Построение эмпирической функции распределения вручную предполагает дополнительные расчеты. Для начала рассчитаем относительные частоты, используя данные таблицы 9.

Получим таблицу 12.

Таблица 12.

Расчет относительных и накопленных частот

Интервал (группы банков по величине собственного капитала), [a,b]

Частота, n_i, число банков в интервале

Относительная частота

Накопленная частота

n_i

33,76

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) Выделяем в таблице 6 данные необходимые для построения эмпирической функции вместе с заголовками столбцов.

2) Вызываем мастер построения диаграмм в Excel - кнопка в виде гистограммы на панели инструментов.

3) Задаем тип диаграммы - Точечная и вид диаграммы - Точечная на которой значения соединены отрезками. Переход к следующему шагу построения - кнопка Далее.

4) Каждый из 6 рядов будем задавать отдельно, используя возможности вкладки Ряд. Нажимая каждый раз кнопку Добавить и задавая значения Х и У для каждого ряда.

5) В результате мы получим 6 отрезков на разной высоте, соответствующей накопленной частоте для каждого интервала. Результаты представлен на рис.4.

Рис.4. Построение эмпирической функции распределения.

Для построения огивы и кумуляты необходимо по-разному соединить точки указанных выше отрезков.

Если соединить начальные точки каждого отрезка, то мы получим огиву, если соединить конечные точки – кумуляту.

Для построения огивы и кумуляты в 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. В результате график будет выглядеть как на рисунке 5.

Рис.5. Эмпирическая функция распределения, огива и кумулята.

ЧАСТЬ 4. ДОПОЛНИТЕЛЬНОЕ ЗАДАНИЕ

4.1. Построение структурной группировки в процентах к итоговым значениям

Добавим лист с названием Группировка_проценты_к_итогу. Для построения структурной группировки в процентах к итогу добавим в таблицу 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

4.2. Построение аналитической группировки.

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

Факторными называются признаки, оказывающие влияние на изменение результативных. Результативными называются признаки, которые изменяются под влиянием факторных.

Для получения таблицы 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

4.3. Построение вторичной группировки

Группировки, построенные за один и тот же период времени, но для разных регионов или, наоборот, для одного региона, но за два разных периода времени, могут оказаться несопоставимыми из-за различного числа выделенных групп или неодинаковости границ интервалов.

В таком случае необходима перегруппировка данных с помощью вторичной группировки.

Вторичная группировка — операция по образованию новых групп на основе ранее осуществленной группировки.

Для получения вторичной группировки добавим лист с названием Перегруппировка. Затемскопируем табл. 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 аргумента: число и количество разрядов.

						Таблица 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 до конца.

Далее ⇒