Эксель ранжирование: Функции РАНГ, РАНГ.РВ и РАНГ.СР для ранжирования данных в Excel

Ранжировать наблюдения

159

Преобразование данных

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

В первую очередь применяются перекодировки, описанные в шаблоне. Все остальные значения перекодируются в значения, превышающие последнее значение в шаблоне, а пользовательские пропущенные значения (на основе первой переменной в

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

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

Как перекодировать текстовые и числовые значения в последовательные целые

E Выберите в меню:

Преобразовать > Автоматическая перекодировка…

E Выберите одну или более переменных для перекодировки.

E Для каждой выбранной переменной введите имя новой переменной и нажмите на кнопку

Новое имя.

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

Имена и метки новых переменных создаются автоматически на основе исходных имен переменных и выбранных типов рангов. В сводных таблицах, вывод которых можно задать, перечислены исходные переменные, новые переменные и метки переменных. (Замечание: Автоматически создаваемые имена имена новых переменных ограничены по длине 8 байтами.)

Дополнительно Вы можете:

Ранжировать наблюдения в возрастающем и в убывающем порядке.

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

160

Глава 8

Рисунок 8-13

Диалоговое окно Ранжировать наблюдения

Как ранжировать наблюдения

E Выберите в меню:

Преобразовать > Ранжировать наблюдения…

EВыберите одну или несколько переменных для ранжирования. Ранжировать можно только числовые переменные.

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

Ранжирование наблюдений: Типы

Можно выбрать один из нескольких методов ранжирования. Для каждого метода создается отдельная ранговая переменная. Методы ранжирования включают простые ранги, значения Сэвиджа, дробные ранги и процентили (N разбиений). Также ранжирование может быть выполнено на основе оценок долей и нормальных значений.

Ранг. Простой ранг. Значение новой переменной равно ее рангу.

Баллы Сэвиджа. Новая переменная содержит баллы Севиджа, основанные на экспоненциальном распределении.

Дробный ранг. Значение новой переменной равно рангу, деленному на сумму весов непропущенных наблюдений.

Дробный ранг в %. Каждый ранг делится на число наблюдений с непропущенными значениями и умножается на 100.

Cумма весов наблюдений. Значение новой переменной равно сумме весов наблюдений. Новая переменная постоянна для всех наблюдений одной группы.

161

Преобразование данных

N разбиение:. Ранги, приписываемые наблюдениям, основываются на процентилях групп, где каждая группа содержит приблизительно одно и то же число наблюдений. Например, при четырех рангах процентилей, ранг 1 будет назначен наблюдениям, лежащим ниже 25-го процентиля, ранг 2 — наблюдениям между 25-м и 50-м процентилями, ранг 3 — наблюдениям между 50-м и 75-м процентилями, и ранг 4 — наблюдениям выше 75-го.

Оценки долей. Оценки кумулятивной доли распределения, соответствующей некоторому конкретному рангу.

Нормальные значения. Z-значения, соответствующие оцененной кумулятивной (накопленной) доле.

Формула для оценки долей. Для вычисления оценок долей и нормальных значений можно выбрать формулу оценивания: Блома, Тьюки, Ранкит или Ван дер Вардена.

Блома. Создает новую ранговую переменную, основанную на оценках пропорций, которые используют формулу (r-3/8) / (w+1/4), где w — сумма весов наблюдений, а r — ранг.

Тьюки. Использует формулу (r-1/3) / (w+1/3), где r — ранг, а w — сумма весов наблюдений.

Ранкит. Использует формулу (r-1/2)/w, где w — число наблюдений, а r — ранг наблюдения, меняющийся от 1 до w.

Ван дер Вардена. Преобразование Ван-дер-Вардена, задаваемое формулой r/(w+1), где w — сумма весов наблюдений, а r — ранг, меняющийся от 1 до w.

Рисунок 8-14

Диалоговое окно Ранжировать наблюдения: Типы рангов

Ранжирование наблюдений: Совпадающие наблюдения

Диалоговое окно Ранжировать наблюдения: Совпадающие наблюдения позволяет задавать методы присвоения рангов наблюдениям с одинаковыми значениями исходной переменной.

Создание диаграммы Парето в Excel (шаблон)


Автор Мариям Жук На чтение 7 мин Опубликовано

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

Содержание

  1. Для чего нужен принцип Парето
  2. Использование диаграммы Парето в Microsoft Excel
  3. Построение таблицы для диаграммы Парето
  4. Построение диаграммы Парето в Microsoft Excel
  5. Особенности подбора данных для диаграммы Парето

Для чего нужен принцип Парето

Принцип Парето получил своё название по фамилии известного итальянского учёного и экономиста, Вильфредо Парето (1848-1923). В основе принципа лежит правило: 20 % усилий дают 80 % результата, и наоборот – 80 % усилий дают всего 20 % результата.

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

К примеру, вас назначили главой в команду, имеющую затруднения в работе с новым проектом. В ходе опроса работников об основных трудностях и препятствиях в выполнении работы и изучении отчётной документации составляется список проблем. После того, как список проанализирован, становится возможным составить перечень и провести ранжирование наиболее часто встречающихся затруднений в работе. В процессе изучения становится понятно, что отсутствие взаимопонимания между работниками и акционерами проекта является причиной большей части проблем из списка, а второй по численности проблемой является недостаток технического оснащения (компьютеров, серверов и др.). Другие проблемы играют гораздо меньшее значение. Так, становится понятно, что наладив коммуникацию работников и акционеров проекта, получится устранить большую часть противоречий и проблем, а решив вопрос с техникой – избавиться примерно от 90 % препятствий для успешной реализации проекта. Увидеть возможные способы решения затруднений поможет составление диаграммы Парето.

Использование диаграммы Парето в Microsoft Excel

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

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

Построение таблицы для диаграммы Парето

В начале, следует ввести имеющиеся числовые показатели в таблицу для диаграммы. В нашем примере присутствует 6 категорий:

  • training fees (плата за обучение),
  • hardware (компьютерная аппаратура),
  • office supplies (канцелярские товары),
  • software (программное обеспечение),
  • mileage (затраты на проездные),
  • other (другое).

Точно вводим имеющиеся числа в столбцы A – category (категория) и B – amount (сумма). Для расположения данных в порядке убывания следует выделить столбец B с числами, нажать на правую кнопку мыши, с помощью команды data (данные) отсортировать их в порядке убывания стоимости sort by (сортировать по), largest to smallest (сортировка по убыванию).

Далее, следует использовать функцию =SUM() для получения общей суммы затрат. Требуется добавить ячейки B3:B8 для получения суммы всех расходов. Возможно выбрать ячейку B9 и нажать ALT + “=”, что сделает тоже самое, что и команда SUM. Получаем общую сумму.

Вводим данные и высчитываем сумму затрат

Важно! Ввести численные данные без ошибок, иначе построить график, верно отражающий ситуацию не получится.

Общая сумма затрат

Далее, требуется создать столбец cumulative amount (накопленная сумма). Надо ввести первую сумму в ячейку C3 — 3750 или B3. Каждое последующее число должно складываться с предыдущим значением в столбце C. Следовательно, в ячейке C4 вводим  = C3 + B4, после чего нажимаем «Enter». Для автоматического заполнения оставшихся данных – делаем двойной щелчок на autofill handle (маркер автозаполнения), или, выделив его левой кнопкой мыши, тянем вниз.

Создаем столбец накопленной суммыПрименяем маркер автозаполнения для подсчёта совокупного процента

Далее, создаём столбец cumulative % (совокупный процент). Для данного столбца его можно использовать. Для этого применяются два ранее созданных столбца: B – сумма, C – кумулятивная сумма. В ячейку D3 вводим = C3 / $ B $ 9, после нажимаем «Enter». Знак «$» создает absolute reference (абсолютная ссылка), поэтому общая сумма в ячейке B9 не изменится при перетаскивании формулы вниз.

Высчитываем кумулятивный %

Далее, как в прошлом примере, используем маркер автозаполнения, активировав его двойным нажатием на левую кнопку мыши или перетащив маркер автозаполнения по столбцу D вниз. Теперь у нас готова таблица, чтобы построить диаграмму Парето.

Используем автозаполнение на показатель кумулятивного %

Построение диаграммы Парето в Microsoft Excel

Чтобы построить диаграмму Парето в Excel, действуйте в соответствии с приведенной ниже схемой:

1. Выделяем ячейки от B2 до D8. Сочетание клавиш «Alt» и F1 автоматически создаёт диаграмму из выделенных данных.

Выделяем данные для построения диаграмыАвтоматически созданный из данных график

2. Жмём правой кнопкой мыши по диаграмме и выбираем Select Data (выбрать данные).

Окно выбора данных

3. Выбираем data source (источник данных).

4. Выбираем накопленную сумму и подтверждаем удаление.

5. На самом графике выбираем совокупный %, жмём на него правой кнопкой мыши и меняем Change chart series type (Изменить тип диаграммы).

Линейный график данных

6. Выбираем line graph (линейный график).

Окно выбора типа графикаТак выглядит график с добавленным показателем кумулятивного %

По итогу получается гистограмма с плоским линейным графиком вдоль оси X. Для получения кривой для совокупного % требуется добавить вертикальную ось. Действуем следующим образом:

7. Жмём правой кнопкой мыши на строку c совокупным % и выбираем Format data series (формат ряда данных).

8. Выбираем Secondary Axis (Вторичная ось) в разделе Series options (параметры ряда).

Окно параметров ряда

9. Далее нажимаем Close (закрыть).

Таким образом мы создаём полную диаграмму Парето. На данной диаграмме синими столбцами обозначены затраты на нужды работников от больших к меньшим (то, что было отсортировано на этапе составления таблицы). Зеленая линия отображает накапливаемый процент расходов — видно, что пороговая линия в 80% останавливается на канцелярских товарах.

Так выглядит готовая диаграмма Парето

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

Особенности подбора данных для диаграммы Парето

Перед созданием таблицы для диаграммы Парето, следует обратить внимание на следующие важные факторы:

  • Выбрать категорию, по которой будут группироваться имеющиеся численные показатели. Не всегда наиболее понятным вариантом будет использование финансовых показателей.
  • Определиться с величиной измерения: объем работы, время, частота, количество затраченных денег.
  • Определить временной промежуток, за который будет строиться диаграмма. Данные за разные годы, месяцы и даже недели могут кардинально отличаться. Также, если составить диаграммы за несколько временных промежутков, становится возможным сделать сравнительный анализ показателей, что позволит получить дополнительные сведения.

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

Оцените качество статьи. Нам важно ваше мнение:

Как использовать функцию Excel Rank (примеры + видео)