Возможность получения обобщенного представления данных большого объема очень важна. Наиболее часто для сводного представления данных в программе Microsoft Office Excel применяются гистограммы, описательная статистика и сводные таблицы. В данной работе будут обсуждаться гистограммы, используемые для наглядного представления данных.
Картина, часто говорят, стоит тысячи слов. Можно ли в программе Excel создать изображение (именуемое гистограммой), представляющее отдельные значения в виде ряда данных?
Гистограмма— это средство, обычно применяемое для сводного представления данных. По существу гистограмма показывает, сколько наблюдений попадает в различные диапазоны значений. Например, гистограмма, созданная из месячных доходностей акций компании Cisco (файл Stock.xlsx), может показать, сколько компания получила доходов в диапазоне от 0 до 10%, от 11 до 20% и т. д. Диапазоны группировки данных называют интервалами карманов (bin ranges).
Давайте рассмотрим, как создавать и интерпретировать гистограммы, дающие сводное представление месячных доходов от акций компаний Cisco и GM за 1990—2000 гг. Эти данные (и доходность других акций) можно найти в файле Stock.xlsx. На листе Курсы акций показано подмножество данных. За март 1990 г., например, цена акций Cisco выросла на 1.075%.
Создавая гистограммы в программе Excel, вы можете позволить программе определить интервалы карманов или задать их самостоятельно. Если это действие доверить Excel, вы можете в результате получить странно выглядящие интервалы, например от -12.53% до 4.52%. Поэтому предпочтительно определять их самостоятельно.
Хороший способ определения интервалов карманов на гистограмме (считайте, что интервалы карманов — это задаваемые границы) — деление диапазона значений (от минимального до максимального) на 8—15 промежутков равной длины. Все месячная доходность акций Cisco лежит в интервале от -30 до 40%, поэтому выбираем следующие границы карманов: -30%, -20%, -10%, 0% и т. д. до 40%.
Для создания наших интервалов карманов сначала необходимо ввести CSCO, 0.4, 0.3, 0.2,..., -0.2, -0.3 (границы интервалов) в ячейки Н54 :Н62. Далее на вкладке ленты Данные в группе Анализ надо щелкнуть кнопкой мыши команду Анализ данных для вывода на экран одноименного диалогового окна. В нем перечислены процедуры надстройки Пакет анализа (Analysis Pack), содержащего большинство инструментов статистической обработки программы Excel.
Примечание
Если команды Анализданныхна вкладке Данныенет, щелкните мышью кнопку MicrosoftOffice, а затем кнопку ПараметрыExcel. В поле Управлениещелкните кнопкой мыши строку Надстройки Excelи затем кнопку Перейти. В диалоговом окне Надстройкиустановите флажок в строке Пакет анализаи щелкните мышью кнопку ОК. Теперь вы можете обращаться к средствам Пакетаанализа, щелкнув мышью кнопку Анализданныхв группе Анализна вкладке ленты Данные.
Щелкнув кнопкой мыши строку Гистограмма в диалоговом окне Анализ данных, мы откроем диалоговое окно Гистограмма.
Далее описано, как заполнить данное диалоговое окно.
- Выделите Входной интервал (Input Range) (F51:F181). (Для выделения диапазона F51:F181 можно выделить F51 и затем нажать комбинацию клавиш <Ctrl>+<Shift>+<↓>.
В результате вы перейдете в нижнюю ячейку столбца.) Этот диапазон включает все данные, которые мы хотим использовать для построения гистограммы. Необходимо включить в диапазон имя столбца данных csco из ячейки F51, поскольку если не включить название в первую строку, ось х гистограммы очень часто помечается числом, что может сбивать с толку.
- Интервал карманов (Bin Range) (Н54:Н62) содержит границы наших интервалов карманов. Программа Excel создаст карманы от -30 до -20%, от -20 до -10% и т. д. до 30 и 40%.
- Далее устанавливается флажок Метки (Labels), потому что в первых строках нашего входного интервала и интервала карманов содержатся имена.
- Далеевыбирается вариант построения гистограммы на новом рабочем листе (названном Гисто).
- Обязательно установите флажок Вывод графика (Chart Output), иначе программа Excel не создаст гистограмму.
Щелкните мышью кнопку ОК в диалоговом окне Гистограмма.
После создания гистограммы вы увидите зазоры между ее столбцами. Для удаления этих зазоров щелкните правой кнопкой мыши любой столбец на диаграмме и выберите команду Формат рядов данных (Format Data Series). На вкладке Параметры (Series Options) уменьшите величину бокового зазора до 0%. Вы также увидите, что не у всех столбцов диаграммы есть метки на оси. Если нет ни одной метки, вы можете вывести на экран их все. Выделите диаграмму и перетащите для расширения гистограммы с нажатой кнопкой мыши указатель в виде двунаправленной стрелки. Можно также уменьшить размер шрифта, чтобы добиться вывода меток на экран. Для уменьшения размера шрифта щелкните правой кнопкой мыши ось на чертеже и затем выберите в меню команду Шрифт. Измените размер шрифта на 5. Есть возможность изменить заголовок диаграммы — выделите его текст и введите на его место новый. После внесения всех этих изменений на экране появится гистограмма.
Обратите внимание на то, что месячная доходность акций Cisco чаще всего попадает в диапазон от 0 до 10%, а затем высота столбцов падает по мере перемещения в сторону от самых высоких столбцов. При создании гистограммы также формируется сводка частоты попадания значений в интервалы карманов.
Анализируя эти частоты, мы узнаем, например, что в течение двух месяцев доходность акций компании Cisco была больше -30% и не превосходила -20%; в течение 13 месяцев месячная доходность ее акций была больше -20% и не превосходила -10%.
Каковы самые распространенные типы гистограмм?
Гистограммы большинства наборов данных можно отнести к одному из следующих типов:
- симметричная (symmetric);
- скошенная вправо (с положительной асимметрией);
- скошенная влево (с отрицательной асимметрией);
- с несколькими пиками (multiple peaks).
Рассмотрим каждый тип гистограмм более подробно. (См. файл Skewexamples.xlsx.)
□ Симметричное распределение. Гистограмма симметрична, если у нее один пик и она выглядит примерно одинаково слева и справа от пика. Очень часто симметричны результаты тестов (например, тесты определения коэффициента интеллектуального развития (IQ)). Гистограммы тестов IQ (см. ячейку W42) могут выглядеть так, как показано в файле. Обратите внимание на то, что высоты столбцов, отстоящих на один столбец влево и вправо от самого высокого столбца, примерно одинаковы, Высоты столбцов, отстоящих на два столбца от столбца с пиковой высотой, также приблизительно одинаковы и т. д. Столбец с меткой 105 представляет всех людей с коэффициентом IQ, большим 95 и меньшим или равным 105, столбец с меткой 65 представляет всех людей с коэффициентом IQ, не превышающим 65, и т. д. Кстати, месячные доходы Cisco почти симметричны.
□ Скошенная вправо (с положительной асимметрией). Гистограмма скошена вправо (с положительной асимметрией), если у нее один пик и значения набора данных распространяются вправо от пика гораздо дальше, чем влево от пика. Во многих экономических рядах данных (например, семейных или индивидуальных доходах) наблюдается положительный скос. В файле (см. ячейку Т24) представлен пример гистограммы с положительной асимметрией, созданной на основе данных о семейных доходах.
□ Скошенная влево (с отрицательной асимметрией). Гистограмма скошена влево (с отрицательной асимметрией), если у нее один пик и значения набора данных распространяются влево от пика гораздо дальше, чем вправо от пика. Период вынашивания ребенка в днях, показанный в ячейке Q7, может служить примером отрицательного скоса. Высота каждого столбца гистограммы представляет количество женщин, чей период от зачатия до рождения попадает в заданный интервал кармана.
□ С множественными пиками. Если на гистограмме есть несколько пиков, обычно это означает, что одновременно отображаются две генеральные совокупности или больше. Например, предположим, что диаметр лифтовых направляющих, производимых на двух станках, представлен диаграммой, показанной в файле Twinpeaks.xlsx. (см. ячейку Q11).
На этой гистограмме данные разделены на две отдельные группы. По всей вероятности, каждая группа данных соответствует лифтовым направляющим, производимым на одном из станков. Если предположить, что нам нужен диаметр лифтовых направляющих, равный 0.55 дюймам, то мы придем к выводу, что один станок изготавливает слишком короткие направляющие, в то время как другой — слишком длинные. Следуя нашей интерпретации, мы должны создать гистограммы, отображающие лифтовые направляющие, созданные на каждом станке. Данный пример показывает, почему гистограммы считаются мощным средством качественного анализа.
Что я могу узнать, сравнивая гистограммы разных наборов данных?
Нас часто просят сравнить разные наборы данных. Например, нас может интересовать, как отличаются месячные доходности акций компаний Cisco и GM. Для ответа на подобный вопрос вы можете построить гистограмму для компании GM с теми же интервалами карманов, что и у компании Cisco и затем поместить одну гистограмму над другой, как показано на рабочем листе Гистограммы в файле Stock.xlsx.
Сравнив эти две гистограммы, мы можем сделать два важных вывода.
- Как правило, компания Cisco действовала лучше GM. Мы можем утверждать это, потому что самый высокий столбец Cisco расположен на один столбец правее самого высокого столбца GM. Кроме того, столбцы компании Cisco продвигаются дальше вправо по сравнению со столбцами компаний GM.
- У компании Cisco больше изменчивость, или отклонение от среднего, чем у компании GM. Пиковый столбец GM включает 59 месяцев, в то время как пиковый столбец Cisco содержит только 41 месяц. Это показывает, что у компании Cisco больше доходов находится за пределами кармана, представляющего наиболее вероятный доход Cisco. Разброс доходов Cisco больше разброса доходов компании GM.
Задачи
- Используйте данные файла Stock.xlsx для построения гистограмм месячной доходности компаний GE и Intel.
- Воспользуйтесь данными файла Historicalinvest.xlsx для создания гистограмм годовой доходности акций и долгосрочных облигаций (Т. Bonds). Сравните величины годовой доходности этих акций и облигаций.
- Вам даются (в файле Deming.xlsx) измерения диаметра (в дюймах), выполненные производственным мастером, для 500 стержней (rods), изготовленных компанией Rodeo. Стержень считается приемлемым, если его диаметр равен как минимум 1 дюйму. В прошлом диаметр стержней, производимых Rodco, соответствовал симметричной гистограмме.
- Постройте гистограмму на основе этих измерений.
- Прокомментируйте неожиданные характеристики полученной гистограммы.
Можно ли догадаться, чем объясняются неожиданные характеристики гистограммы? (Подсказка: один из 14 принципов специалиста по качеству (гуру качества) Деминга — "Изгоняйте страхи...".)