Как построить дендрограмму в excel
Гистограмма в Excel – это способ построения наглядной диаграммы, отражающей изменение нескольких видов данных за какой-то период времени.
С помощью гистограммы удобно иллюстрировать различные параметры и сравнивать их. Рассмотрим самые популярные виды гистограмм и научимся их строить.
Как построить обновляемую гистограмму?
Имеем данные по продажам разных видов молочной продукции по каждому месяцу за 2015 год.
Построим обновляемую гистограмму, которая будет реагировать на вносимые в таблицу изменения. Выделим весь массив вместе с шапкой и кликнем на вкладку ВСТАВКА. Найдем так ДИАГРАММЫ – ГИСТОГРАММА и выберем самый первый тип. Он называется ГИСТОГРАММА С ГРУППИРОВКОЙ.
Получили гистограмму, размер поля которой можно менять. На такой диаграмме наглядно видно, например, что самые большие продажи были по молоку в ноябре, а самые маленькие – по сливкам в июне.
Если мы будем вносить в таблицу изменения, внешний вид гистограммы тоже будет меняться. Для примера вместо 1400 в январе по кефиру поставим 4000. Видим, как зеленый столбец полетел вверх.
Гистограмма с накоплением
Теперь рассмотрим, как построить гистограмму с накоплением в Excel. Еще один тип гистограмм, который позволяет отразить данные в процентном соотношении. Строится она точно так же, но выбирается другой тип.
Получаем диаграмму, на которой можно видеть, что, например, в январе больше продано молока, чем кефира или сливок. А в августе, по сравнению с другими молочными продуктами, молока было продано мало. И т.п.
Гистограммы в Excel можно изменять. Так, если мы кликнем правой кнопкой мыши в пустом месте диаграммы и выберем ИЗМЕНИТЬ ТИП, то сможем несколько ее видоизменить. Поменяем тип нашей гистограммы с накоплением на нормированную. Результатом будет та же самая диаграмма, но по оси Y будут отражены соотношения в процентном эквиваленте.
Аналогично можно производить и другие изменения гистограммы, что мы и сделали:
- поменяли шрифта на Arial и изменили его цвет на фиолетовый;
- сделали подчеркивание пунктирной линией;
- переместили легенду немного выше;
- добавили подписи к столбцам.
Как объединить гистограмму и график в Excel?
Некоторые массивы данных подразумевают построение более сложных диаграмм, которые совмещают несколько их видов. К примеру, гистограмма и график.
Рассмотрим пример. Для начала добавим к таблице с данными еще одну строку, где прописана выручка за каждый месяц. Она указана в рублях.
Теперь изменим существующую диаграмму. Кликнем в пустом месте правой кнопкой и выберем ВЫБРАТЬ ДАННЫЕ. Появится такое поле, на котором будет предложено выбрать другой интервал. Выделяем всю таблицу снова, но уже охватывая и строку с выручкой.
Excel автоматически расширил область значений по оси Y, поэтому данные по количеству продаж остались в самом низу в виде незаметных столбиков.
Но такая гистограмма неверна, потому что на одной диаграмме у нас значатся числа в рублевом и количественном эквиваленте (рублей и литров). Поэтому нужно произвести изменения. Перенесем данные по выручке на правую сторону. Кликнем по фиолетовым столбикам правой кнопкой, выберем ФОРМАТ РЯДА ДАННЫХ и отметим ПО ВСПОМОГАТЕЛЬНОЙ ОСИ.
Видим, что график сразу изменился. Теперь фиолетовый столбик с выручкой имеет свою область значения (справа).
Но это все равно не очень удобно, потому что столбики почти сливаются. Поэтому произведем еще одно дополнительное действие: кликнем правой кнопкой по фиолетовым столбцам и выберем ИЗМЕНИТЬ ТИП ДИАГРАММЫ ДЛЯ РЯДА. Появится окно, в котором выбираем график, самый первый тип.
Получаем вполне наглядную диаграмму, представляющую собой объединение гистограммы и графика. Видим, что максимальная выручка была в январе и ноябре, а минимальная – в августе.
окно, содержащее дендрограмму, построенную по результатам кластерного анализа.
Полученный график можно редактировать и распечатать непосредственно из Excel или
перенести, воспользовавшись буфером обмена, в какой-либо графический редактор,
например, в CorelDraw. Векторный формат изображения удобен для редактирования при
подготовке иллюстрационной графики. Основным преимуществом данного подхода является
возможность избежать утомительной процедуры экспорта данных из Excel в программу,
выполняющую статистические вычисления, что существенно экономит время.
Нам важно ваше мнение! Был ли полезен опубликованный материал? Да | Нет
Кластерный анализ
Назначение . С помощью онлайн-калькулятора можно проводить классификацию объектов алгоритмами «ближайшего соседа» и «дальнего соседа» с построением дендрограммы.
- Шаг №1
- Шаг №2
- Видеоинструкция
- Оформление Word
Выбор конкретного метода кластерного анализа зависит от цели классификации.
Обычной формой представления исходных данных в задачах кластерного анализа служит матрица:
Пример . Провести классификацию шести объектов, каждый из которых характеризуется двумя признаками (табл.9). В качестве расстояния между объектами принять , расстояние между кластерами исчислить по принципам: 1) “ближайшего соседа” и 2) “дальнего соседа”.
| № п/п | 1 | 2 | 3 | 4 | 5 | 6 |
| x1 | 2 | 4 | 5 | 12 | 14 | 15 |
| x2 | 8 | 10 | 7 | 6 | 6 | 4 |
2. Полученные данные помещаем в таблицу (матрицу расстояний).
| № п/п | 1 | 2 | 3 | 4 | 5 | 6 |
| 1 | 0 | 2.83 | 3.16 | 10.2 | 12.17 | 13.6 |
| 2 | 2.83 | 0 | 3.16 | 8.94 | 10.77 | 12.53 |
| 3 | 3.16 | 3.16 | 0 | 7.07 | 9.06 | 10.44 |
| 4 | 10.2 | 8.94 | 7.07 | 0 | 2 | 3.61 |
| 5 | 12.17 | 10.77 | 9.06 | 2 | 0 | 2.24 |
| 6 | 13.6 | 12.53 | 10.44 | 3.61 | 2.24 | 0 |
3. Поиск наименьшего расстояния.
Из матрицы расстояний следует, что объекты 4 и 5 наиболее близки P4;5 = 2 и поэтому объединяются в один кластер.
| № п/п | 1 | 2 | 3 | [4] | [5] | 6 |
| 1 | 0 | 2.83 | 3.16 | 10.2 | 12.17 | 13.6 |
| 2 | 2.83 | 0 | 3.16 | 8.94 | 10.77 | 12.53 |
| 3 | 3.16 | 3.16 | 0 | 7.07 | 9.06 | 10.44 |
| [4] | 10.2 | 8.94 | 7.07 | 0 | 2 | 3.61 |
| [5] | 12.17 | 10.77 | 9.06 | 2 | 0 | 2.24 |
| 6 | 13.6 | 12.53 | 10.44 | 3.61 | 2.24 | 0 |
При формировании новой матрицы расстояний, выбираем наименьшее значение из значений объектов №4 и №5.
В результате имеем 5 кластера: S(1), S(2), S(3), S(4,5), S(6)
Из матрицы расстояний следует, что объекты 4,5 и 6 наиболее близки P4,5;6 = 2.24 и поэтому объединяются в один кластер.
| № п/п | 1 | 2 | 3 | [4,5] | [6] |
| 1 | 0 | 2.83 | 3.16 | 10.2 | 13.6 |
| 2 | 2.83 | 0 | 3.16 | 8.94 | 12.53 |
| 3 | 3.16 | 3.16 | 0 | 7.07 | 10.44 |
| [4,5] | 10.2 | 8.94 | 7.07 | 0 | 2.24 |
| [6] | 13.6 | 12.53 | 10.44 | 2.24 | 0 |
При формировании новой матрицы расстояний, выбираем наименьшее значение из значений объектов №4,5 и №6.
В результате имеем 4 кластера: S(1), S(2), S(3), S(4,5,6)
Из матрицы расстояний следует, что объекты 1 и 2 наиболее близки P1;2 = 2.83 и поэтому объединяются в один кластер.
| № п/п | [1] | [2] | 3 | 4,5,6 |
| [1] | 0 | 2.83 | 3.16 | 10.2 |
| [2] | 2.83 | 0 | 3.16 | 8.94 |
| 3 | 3.16 | 3.16 | 0 | 7.07 |
| 4,5,6 | 10.2 | 8.94 | 7.07 | 0 |
При формировании новой матрицы расстояний, выбираем наименьшее значение из значений объектов №1 и №2.
В результате имеем 3 кластера: S(1,2), S(3), S(4,5,6)
Из матрицы расстояний следует, что объекты 1,2 и 3 наиболее близки P1,2;3 = 3.16 и поэтому объединяются в один кластер.
| № п/п | [1,2] | [3] | 4,5,6 |
| [1,2] | 0 | 3.16 | 8.94 |
| [3] | 3.16 | 0 | 7.07 |
| 4,5,6 | 8.94 | 7.07 | 0 |
При формировании новой матрицы расстояний, выбираем наименьшее значение из значений объектов №1,2 и №3.
В результате имеем 2 кластера: S(1,2,3), S(4,5,6)
| № п/п | 1,2,3 | 4,5,6 |
| 1,2,3 | 0 | 7.07 |
| 4,5,6 | 7.07 | 0 |
Таким образом, при проведении кластерного анализа по принципу “ближнего соседа” получили два кластера, расстояние между которыми равно P=7.07
Результаты иерархической классификации объектов представлены на рис. в виде дендрограммы.
Любую информацию легче воспринимать, если она представлена наглядно. Это особенно актуально, когда мы имеем дело с числовыми данными. Их необходимо сопоставить, сравнить. Оптимальный вариант представления – диаграммы. Будем работать в программе Excel.
Так же мы научимся создавать динамические диаграммы и графики, которые автоматически обновляют свои показатели в зависимости от изменения данных. По ссылке в конце статьи можно скачать шаблон-образец в качестве примера.
Как построить диаграмму по таблице в Excel?
- Создаем таблицу с данными.
- Выделяем область значений A1:B5, которые необходимо презентовать в виде диаграммы. На вкладке «Вставка» выбираем тип диаграммы.
- Нажимаем «Гистограмма» (для примера, может быть и другой тип). Выбираем из предложенных вариантов гистограмм.
- После выбора определенного вида гистограммы автоматически получаем результат.
- Такой вариант нас не совсем устраивает – внесем изменения. Дважды щелкаем по названию гистограммы – вводим «Итоговые суммы».
- Сделаем подпись для вертикальной оси. Вкладка «Макет» - «Подписи» - «Названия осей». Выбираем вертикальную ось и вид названия для нее.
- Вводим «Сумма».
- Конкретизируем суммы, подписав столбики показателей. На вкладке «Макет» выбираем «Подписи данных» и место их размещения.
- Уберем легенду (запись справа). Для нашего примера она не нужна, т.к. мало данных. Выделяем ее и жмем клавишу DELETE.
- Изменим цвет и стиль.
Выберем другой стиль диаграммы (вкладка «Конструктор» - «Стили диаграмм»).
Как добавить данные в диаграмму в Excel?
- Добавляем в таблицу новые значения - План.
- Выделяем диапазон новых данных вместе с названием. Копируем его в буфер обмена (одновременное нажатие Ctrl+C). Выделяем существующую диаграмму и вставляем скопированный фрагмент (одновременное нажатие Ctrl+V).
- Так как не совсем понятно происхождение цифр в нашей гистограмме, оформим легенду. Вкладка «Макет» - «Легенда» - «Добавить легенду справа» (внизу, слева и т.д.). Получаем:
Есть более сложный путь добавления новых данных в существующую диаграмму – с помощью меню «Выбор источника данных» (открывается правой кнопкой мыши – «Выбрать данные»).
Когда нажмете «Добавить» (элементы легенды), откроется строка для выбора диапазона данных.
Как поменять местами оси в диаграмме Excel?
- Щелкаем по диаграмме правой кнопкой мыши – «Выбрать данные».
- В открывшемся меню нажимаем кнопку «Строка/столбец».
- Значения для рядов и категорий поменяются местами автоматически.
Как закрепить элементы управления на диаграмме Excel?
Если очень часто приходится добавлять в гистограмму новые данные, каждый раз менять диапазон неудобно. Оптимальный вариант – сделать динамическую диаграмму, которая будет обновляться автоматически. А чтобы закрепить элементы управления, область данных преобразуем в «умную таблицу».
- Выделяем диапазон значений A1:C5 и на «Главной» нажимаем «Форматировать как таблицу».
- В открывшемся меню выбираем любой стиль. Программа предлагает выбрать диапазон для таблицы – соглашаемся с его вариантом. Получаем следующий вид значений для диаграммы:
- Как только мы начнем вводить новую информацию в таблицу, будет меняться и диаграмма. Она стала динамической:
Мы рассмотрели, как создать «умную таблицу» на основе имеющихся данных. Если перед нами чистый лист, то значения сразу заносим в таблицу: «Вставка» - «Таблица».
Как сделать диаграмму в процентах в Excel?
Представлять информацию в процентах лучше всего с помощью круговых диаграмм.
Исходные данные для примера:
- Выделяем данные A1:B8. «Вставка» - «Круговая» - «Объемная круговая».
- Вкладка «Конструктор» - «Макеты диаграммы». Среди предлагаемых вариантов есть стили с процентами.
- Выбираем подходящий.
- Очень плохо просматриваются сектора с маленькими процентами. Чтобы их выделить, создадим вторичную диаграмму. Выделяем диаграмму. На вкладке «Конструктор» - «Изменить тип диаграммы». Выбираем круговую с вторичной.
- Автоматически созданный вариант не решает нашу задачу. Щелкаем правой кнопкой мыши по любому сектору. Должны появиться точки-границы. Меню «Формат ряда данных».
- Задаем следующие параметры ряда:
- Получаем нужный вариант:
Диаграмма Ганта в Excel
Диаграмма Ганта – это способ представления информации в виде столбиков для иллюстрации многоэтапного мероприятия. Красивый и несложный прием.
- У нас есть таблица (учебная) со сроками сдачи отчетов.
- Для диаграммы вставляем столбец, где будет указано количество дней. Заполняем его с помощью формул Excel.
- Выделяем диапазон, где будет находиться диаграмма Ганта. То есть ячейки будут залиты определенным цветом между датами начала и конца установленных сроков.
- Открываем меню «Условное форматирование» (на «Главной»). Выбираем задачу «Создать правило» - «Использовать формулу для определения форматируемых ячеек».
- Вводим формулу вида: =И(E$2>=$B3;E$2 Готовые примеры графиков и диаграмм в Excel скачать:
Пример создания динамического синхронного еженедельного графика вместе с ежедневным. Синхронное отображение двух таймфреймов на одном графике.
В программе Excel специально разрабатывались диаграммы и графики для реализации визуализации данных.
Одним из инструментов для решения экономических задач является кластерный анализ. С его помощью кластеры и другие объекты массива данных классифицируются по группам. Данную методику можно применять в программе Excel. Посмотрим, как это делается на практике.
Использование кластерного анализа
С помощью кластерного анализа можно проводить выборку по признаку, который исследуется. Его основная задача – разбиение многомерного массива на однородные группы. В качестве критерия группировки применяется парный коэффициент корреляции или эвклидово расстояние между объектами по заданному параметру. Наиболее близкие друг к другу значения группируются вместе.
Хотя чаще всего данный вид анализа применяют в экономике, его также можно использовать в биологии (для классификации животных), психологии, медицине и во многих других сферах деятельности человека. Кластерный анализ можно применять, используя для этих целей стандартный набор инструментов Эксель.
Пример использования
Имеем пять объектов, которые характеризуются по двум изучаемым параметрам – x и y.
-
Применяем к данным значениям формулу эвклидового расстояния, которое вычисляется по шаблону:
На этом завершается процедура разбиения совокупности на группы.
Как видим, хотя в целом кластерный анализ и может показаться сложной процедурой, но на самом деле разобраться в нюансах данного метода не так уж тяжело. Главное понять основную закономерность объединения в группы.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Читайте также: