Взвешивание выборки в excel

Обновлено: 07.07.2024

Коррекция выборки (взвешивание) по нескольким переменным

Вопросы взвешивания выборочных данных с целью "подтянуть" выборочные статистики к параметрам генеральной совокупности (ГС) всегда актуальны в исследованиях, претендующих на репрезентативность. Из наиболее недавних публикаций по этому вопросу - перепечатки давних, но не потерявших своей значимости статей А.О. Крыштановского по ремонту и перевзвешиванию выборки в приложении к учебнику [1].

В количественных социологических исследованиях объектами наблюдения часто являются отдельные индивиды, либо, скажем, домохозяйства. Иногда в таких случаях контрольные цифры соответствия выборки генеральной совокупности может дать государственная статистика. Такие параметры как численность и соотношение мужчин и женщин, людей с разным образованием, показатели расселения людей и проч. время от времени фиксируются в рамках переписей населения и периодически корректируются посредством специальных прогнозных моделей и микропереписей. Допустим, нас интересует процент людей, собирающихся голосовать за определённую партию. Опыт подсказывает, что заметное влияние на политические предпочтения оказывает уровень образования индивидов. Поэтому, чтобы получить обоснованные прогнозы голосования, надо постараться обеспечить соответствие распределения образования в выборке распределению в генеральной совокупности. Иначе в выборке, скажем, может оказаться избыточное по сравнению с генеральной совокупностью количество людей с низким уровнем образования, что может негативно повлиять на качество прогноза (прогноз процента голосов за партию, ориентированную на малообразованную часть населения будет необоснованно завышен). Если распределение образования в генеральной совокупности на изучаемой территории известно (для этого можно заглянуть в статистический сборник), данные можно перевзвесить весьма просто. Достаточно приписать голосу каждого респондента в выборке вес w_i, где i - код определённого уровня образования, рассчитываемый как

P_i - доля людей с i-м уровнем образования в ГС (данные Росстата), а

p_i - доля людей с i-м уровнем образования в имеющейся выборке.

Если есть потребность в приведении объема выборки к объёму ГС (т.е. о построении модели ГС масштаба 1:1), веса могут быть рассчитаны ещё более просто:

N_i - численность i-й группы в ГС, а

n_i - численность i-й группы в выборке*.

* Заметим, что при такой "инфляции" объёма выборки нельзя проводить статистические проверки, т.к. объём выборки оказывается завышенным и, следовательно, заниженными оказываются все стандартные ошибки. Например, незначимые различия приобретут большую значимость, т.к. будут "расцениваться" как проявившиеся на выборке большого объёма (объёма, равного объёму ГС). Тем не менее, масштабирующими весами иногда бывает удобно пользоваться. Далее будем полагать, что работаем именно с ними: это избавит нас от необходимости подсчёта долей и процентов.

Мы рассмотрели классический пример. Жизнь, однако же, устроена сложнее. Так, политические предпочтения могут определяться не только образованием, но и полом, возрастом, статусом занятости. Выходит, желательно контролировать пропорции в выборке не только по одной переменной, но по некоторому их набору. При этом взвешивание по одному параметру может привести к ещё большим несоответствиям выборки и ГС по другим параметрам. Чтобы избежать таких искажений, желательно использовать информацию о совместных распределениях переменных в ГС. Это значит, требуется знать, сколько, например, мужчин с высшим образованием возраста 25-30 лет, занятых полный рабочий день проживает на исследуемой территории. Зная численность такой сложной составной категории можно соответствующим образом взвесить ответы таких респондентов в выборке. Обычно в статистических сборниках приводятся разбиения по наиболее важным показателям, но такой детализации может и не найтись. Зато почти наверняка имеются данные распределения отдельно по мужчинам и женщинам, людям с разным образованием и т.д. Иными словами, имеются итоговые ячейки (маргиналы) многомерной таблицы сопряжённости между переменными в ГС, а наполнение таблицы неизвестно.

*Возможно, в отечественной науке имеется более подходящий, традиционный термин для обозначения этого метода. Буду признателен, если кто-то из подписчиков просветит меня в этом. Есть сведения, что в английской терминологии такой метод взвешивания иногда называют также "rim weighting". Термин Iterative Proportional Fitting взят из [2]. Внимание! В уравнениях (1, 2) записи метода в этой работе у меня в Adobe Acrobat не отображаются операторы суммирования. Не исключено, что эта проблема может проявиться и на других машинах.

Алгоритм IPF. Взвешивание проводится по k переменным, каждая из которых имеет конечное число категорий. Численность объектов каждой категории для каждой переменной в ГС задана. В начале все наблюдения имеют вес, равный 1. Веса корректируются по очереди для каждой переменной. Каждая итерация включает в себя k обновлений весов. Рассматривается первая переменная. Считается сумма весов в выборке по каждой её категории. Затем вес каждого наблюдения (имеющего, например, категорию s) умножается на отношение численности категории s первой переменной в ГС к сумме весов всех наблюдений категории s в выборке. Переходят ко второй переменной. Считают сумму весов (с учётом их изменений для первой переменной) для каждой категории второй переменной. Обновлённые при разборе первой переменной веса для каждого наблюдения умножаются на отношение численности соответствующей категории второй переменной в ГС к сумме весов всех наблюдений этой категории в выборке. И так далее, до последней, k-й, переменной. После завершения очередной итерации можно проверить, не сошёлся ли алгоритм, т.е. не оказалась ли приведена выборка в соответствие заданным маргиналам в ГС. Я говорю "можно" проверить, потому что в разбираемом синтаксисе такая проверка не реализована. Считается, что алгоритм должен сойтись за относительно небольшое число итераций. В синтаксисе реализована лишь одна итерация и пользователю предлагается самостоятельно инициировать запуск следующей итерации, если результаты подгонки пропорций после предыдущей его не устраивают.

Данные примера. Перед тем как продолжить, сгенерируем пример данных с двумя переменными и разберём, как бы мы производили взвешивание, точно зная совместное распределение этих переменных в ГС, а затем - применим к данным процедуру итеративного взвешивания. Инструкция SET SEED обеспечивает сброс генератора случайных чисел к исходному значению 20072006, чтобы у вас был точно такой же пример. Мы генерируем 3000 наблюдений вектора (var1, var2), и это символизирует генеральную совокупность. По условию генерации, переменная var1 имеет 2 категории и принимает их равновероятно. Используется функция генерации значений из равномерного распределения на заданном участке - RV.UNIFORM() и функция округления RND. Переменная var2 некоторым образом зависит от первой переменной и имеет 4 разных категории.
Вторая пара переменных (svar1, svar2) имеет только 100 значений (они генерируются, если системная переменная $casenum - номер наблюдения - имеет значение, не превосходящее 100) и символизирует выборку. Обратите внимание, генерирующие уравнения несколько отличаются от тех, что были использованы для генеральной совокупности. Это обеспечит нам искомые "искажения", несоответствие выборки исследуемой ГС.

SET SEED 20072006.

- COMPUTE var1 = 1+RND(rv.uniform(0,1)).

- COMPUTE var2 = var1 + 1+ RND (rv.uniform(-1,1)).

- IF ($casenum<=100) svar1=1+RND(rv.uniform(0.1,1.1)).

- IF ($casenum<=100) svar2 = svar1 + 1 + RND(rv.UNIFORM(-0.9,1.2)).

END INPUT PROGRAM.

Убедимся, что маргиналы таблиц сопряжённости, соответствующих ГС (var1 * var2) и выборке (svar1 * svar2), существенно различаются. Так, например, к первой категории переменной var2 относятся лишь 6% наблюдений в выборке, но 12% наблюдений в ГС. Требуется корректировка.

CROSSTABS TABLES=var1 BY var2 /CELL COUNT ROW.

CROSSTABS TABLES=svar1 BY svar2 /CELL COUNT ROW.

Взвешивание с известными совместными распределениями. Предположим, мы знаем совместное распределение этих переменных в ГС. Действительно, в данный момент у нас перед глазами таблица сопряжённости var1 * var2. Воспользуемся этим случаем для того, чтобы взвесить выборку наиболее точно. Для каждой комбинации значений svar1 и svar2 зададим свой вес как показано выше: N_i/n_i.

IF ($casenum<=100 AND svar1 = 1 AND svar2 = 1) W= 359 / 6.

IF ($casenum<=100 AND svar1 = 1 AND svar2 = 2) W= 756 / 20.

IF ($casenum<=100 AND svar1 = 1 AND svar2 = 3) W= 405 / 16.

IF ($casenum<=100 AND svar1 = 2 AND svar2 = 2) W= 373 / 11.

IF ($casenum<=100 AND svar1 = 2 AND svar2 = 3) W= 758 / 28.

IF ($casenum<=100 AND svar1 = 2 AND svar2 = 4) W= 349 / 19.

Построим частотное распределение:

CROSSTABS TABLES=svar1 BY svar2 /CELL COUNT ROW.

Теперь наша выборка - модель ГС в масштабе 1:1. Остаётся надеяться, что статистика по тем переменным, которые не участвовали во взвешивании, также будет близка к параметрам ГС. Теоретически, эти статистики должны теперь больше соответствовать действительности, чем до взвешивания.

Взвешивание с использованием IPF. Теперь представим, что совместное распределение переменных (var1, var2) в ГС нам неизвестно. Известны лишь их маргиналы - итоговые частоты по каждой категории. Запишем эти итоговые частоты для каждого значения переменных svar1 и svar2 в переменные univ1 и univ2, соответственно.

IF svar1=1 univ1=1520.

IF svar1=2 univ1=1480.

IF svar2=1 univ2=359.

IF svar2=2 univ2=1129.

IF svar2=3 univ2=1163.

IF svar2=4 univ2=349.

Все 3000 значений "генеральной совокупности" нам больше не нужны. Оставим лишь 100 выборочных значений:

Пусть начальное значение веса для каждого наблюдения будет равно единице.

Теперь настало время разобрать макрос, производящий итерацию взвешивания.

В качестве параметров ему передаются пары имён переменных. Первая переменная - та, чьи пропорции корректируются, вторая - переменная со значением маргинальных частот категорий первой переменной. Пар может передаваться много. В данном случае будет 2 пары, так как взвешивание проводим по 2 переменным. В данном случае автор макроса посчитал удобным заключить параметры макроса в квадратные скобки (ключевое слово !ENCLOSE).

Надо понять, что сама операция взвешивания, повторяющаяся в одной итерации k = 2 раза (по числу переменных взвешивания) очень проста. Это лишь одна инструкция "COMPUTE W = W * (!valp / Wt)". Всё остальное содержимое макроса - подготовка к ней.

Переданные переменные "перебираются" макросом в цикле !DO . !IN . !DOEND. Имя каждой переменной последовательно передаётся макропеременной !var. Некоторая сложность связана с тем, что нам нужно отличать переменную с категориями (svar) от переменной с частотой генеральной совокупности (univ). Первая - всегда на первом месте в каждой паре, вторая - на втором. Поэтому удобно определить пару дополнительных макропеременных !nnn и !nnn2. Если присмотреться, мы оперируем с ними как с триггерами, перекидывая их из состояния 1 в 2 и обратно, в зависимости от того, какая переменная из пары (svar, univ) рассматривается в цикле сейчас. Если рассматривается первая переменная (svar), то мы просто запоминаем её имя в дополнительной макропеременной !varp. Нам это пригодится когда мы перейдём к следующей переменной с частотой ГС. Если !nnn = 2 (т.е. рассматривается одна из переменных univ), нам, согласно алгоритму итеративного взвешивания, следует сначала подсчитать сумму весов по каждой категории текущей переменной !varp (помните, это одна из переменных svar?). Для этого мы, во-первых, сортируем файл по этой переменной и записываем во временную переменную Wt текущий вес каждого наблюдения. Во-вторых, считаем (с помощью функции LAG) накопленный итог - сумму весов, затем сортируем файл так, чтобы по каждой категории переменной !varp накопленный итог был первым значением наблюдением в группе (сортировка в порядке убывания по Wt). И аналогичным манёвром (с использованием функции LAG) делаем так, чтобы для каждого наблюдения группы переменная Wt содержала суммарный вес наблюдений данной группы. Теперь осталось только провести очередную итерацию (см. инструкцию COMPUTE). В данном случае !valp = !var = значение одной из переменных univ.

DEFINE !pondx (!POSITIONAL !ENCLOSE('[',']'))

- !IF (!nnn=1) !then !LET !varp=!var !IFEND.

- SORT CASES BY !varp.

- IF !varp=LAG(!varp) Wt = Wt + LAG(Wt).

- SORT CASES BY !varp (A) Wt (D).

- IF !varp=LAG(!varp) Wt=LAG(Wt).

- COMPUTE W = W * (!valp / Wt).

- !IF (!nnn2=1) !THEN !LET !nnn=2 !IFEND.

- !IF (!nnn2=2) !THEN !LET !nnn=1 !IFEND.

Запустим макрос первый раз (проведём первую итерацию). После её завершения в файле останутся все ранее определённые переменные плюс переменная Wt, которую можно удалить или оставить - без разницы. Если будете выполнять ещё итерации, она всё равно добавится.

!pondx [svar1 univ1 svar2 univ2].

После итерации можно попробовать применить получившийся итоговый вес и построить таблицу сопряжённости. Если маргиналы этой таблицы не совпадают со значениями ГС, итерации можно повторить какое-то число раз. При желании вы можете расширить предложенный синтаксис так, чтобы он автоматически определял, достигнут ли требуемый результат взвешивания (а также - позволял вводить ограничения на минимальные и максимальные веса, удалял лишние переменные и т.д.).
Для данного примера (с принятой отправной точкой генератора случайных чисел) выборка "сходится" с генеральной совокупностью, кажется, за 6 итераций:

!pondx [svar1 univ1 svar2 univ2].

CROSSTABS TABLES=svar1 BY svar2 /CELL COUNT ROW.

Теперь можно начинать работать с выборкой - что там требовалось оценить? Сравните, кстати, кросстабуляции, полученные ранее для ГС и выборочную кросстабуляцию после взвешивания. Приятно отметить, что расхождения очень незначительны.

Сформулируем несколько заключительных замечаний.

1. Если не требуется задание "реальных" весов (расширяющих выборку до размеров ГС), можно задавать веса пропорциональные. Тогда веса univ должны задаваться в долях от размера ГС, умноженных на размер выборки. Размер выборки после взвешивания останется постоянным.

2. Алгоритм взвешивания сойдётся не всегда. За более подробными инструкциями следует обратиться к математическим работам, в которых изучаются его свойства. В упомянутой работе [2] даётся исчерпывающая библиография.

3. Макрос позволяет задать весьма большое число пар переменных, то есть, зная маргиналы нужных вам переменных в ГС, можно взвесить выборку по этим переменным достаточно легко.

4. После окончания взвешивания все переменные univ, а также Wt могут быть удалены.

Литература

1. Крыштановский А.О. Анализ социологических данных с помощью пакета SPSS. - М.: Изд. дом ГУ ВШЭ, 2006

Среди множества показателей, которые применяются в статистике, нужно выделить расчет дисперсии. Следует отметить, что выполнение вручную данного вычисления – довольно утомительное занятие. К счастью, в приложении Excel имеются функции, позволяющие автоматизировать процедуру расчета. Выясним алгоритм работы с этими инструментами.

Вычисление дисперсии

Дисперсия – это показатель вариации, который представляет собой средний квадрат отклонений от математического ожидания. Таким образом, он выражает разброс чисел относительно среднего значения. Вычисление дисперсии может проводиться как по генеральной совокупности, так и по выборочной.

Способ 1: расчет по генеральной совокупности

Для расчета данного показателя в Excel по генеральной совокупности применяется функция ДИСП.Г. Синтаксис этого выражения имеет следующий вид:

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

Посмотрим, как вычислить это значение для диапазона с числовыми данными.

Производим выделение ячейки на листе, в которую будут выводиться итоги вычисления дисперсии. Щелкаем по кнопке «Вставить функцию», размещенную слева от строки формул.

Способ 2: расчет по выборке

В отличие от вычисления значения по генеральной совокупности, в расчете по выборке в знаменателе указывается не общее количество чисел, а на одно меньше. Это делается в целях коррекции погрешности. Эксель учитывает данный нюанс в специальной функции, которая предназначена для данного вида вычисления – ДИСП.В. Её синтаксис представлен следующей формулой:

Количество аргументов, как и в предыдущей функции, тоже может колебаться от 1 до 255.

Мастер функций

Как видим, программа Эксель способна в значительной мере облегчить расчет дисперсии. Эта статистическая величина может быть рассчитана приложением, как по генеральной совокупности, так и по выборке. При этом все действия пользователя фактически сводятся только к указанию диапазона обрабатываемых чисел, а основную работу Excel делает сам. Безусловно, это сэкономит значительное количество времени пользователей.

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

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

Этот инструмент называется «Описательная статистика». С его помощью можно в очень короткие сроки, использовав ресурсы программы, обработать массив данных и получить о нем информацию по целому ряду статистических критериев. Давайте взглянем, как работает данный инструмент, и остановимся на некоторых нюансах работы с ним.

Использование описательной статистики

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

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

Медиана;
Мода;
Дисперсия;
Среднее;
Стандартное отклонение;
Стандартная ошибка;
Асимметричность и др.

Рассмотрим, как работает данный инструмент на примере Excel 2010, хотя данный алгоритм применим также в Excel 2007 и в более поздних версиях данной программы.

Как уже было сказано выше, инструмент «Описательная статистика» входит в более широкий набор функций, который принято называть Пакет анализа. Но дело в том, что по умолчанию данная надстройка в Экселе отключена. Поэтому, если вы до сих пор её не включили, то для использования возможностей описательной статистики, придется это сделать.

Переходим во вкладку «Файл». Далее производим перемещение в пункт «Параметры».

После вышеуказанных действий надстройка Пакет анализа будет активирована и станет доступной во вкладке «Данные» Эксель. Теперь мы сможем использовать на практике инструменты описательной статистики.

Применение инструмента «Описательная статистика»

Теперь посмотрим, как инструмент описательная статистика можно применить на практике. Для этих целей используем готовую таблицу.

«Данные»

«Анализ данных

«Анализ»

Выше мы говорили исключительно о входных данных. Теперь переходим к разбору настроек параметров вывода, которые расположены в этом же окне формирования описательной статистики. Прежде всего, нам нужно определиться, куда именно будут выводиться обработанные данные:

Выходной интервал;
Новый рабочий лист;
Новая рабочая книга.

В первом случае нужно указать конкретный диапазон на текущем листе или его верхнюю левую ячейку, куда будет выводиться обработанная информация. Во втором случае следует указать название конкретного листа данной книги, где будет отображаться результат обработки. Если листа с таким наименованием в данный момент нет, то он будет создан автоматически после того, как вы нажмете на кнопку «OK». В третьем случае никаких дополнительных параметров указывать не нужно, так как данные будут выводиться в отдельном файле Excel (книге). Мы выбираем вывод результатов на новом рабочем листе под названием «Итоги».

Далее, если вы хотите чтобы выводилась также итоговая статистика, то нужно установить флажок около соответствующего пункта. Также можно установить уровень надежности, поставив галочку около соответствующего значения. По умолчанию он будет равен 95%, но его можно изменить, внеся другие числа в поле справа.

Кроме этого, можно установить галочки в пунктах «K-ый наименьший» и «K-ый наибольший», установив значения в соответствующих полях. Но в нашем случае этот параметр так же, как и предыдущий, не является обязательным, поэтому флажки мы не ставим.

Асимметричность;
Интервал;
Минимум;
Стандартное отклонение;
Дисперсия выборки;
Максимум;
Сумма;
Эксцесс;
Среднее;
Стандартная ошибка;
Медиана;
Мода;
Счет.

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

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Читайте также: