Диаграммы в excel cat

Обновлено: 21.11.2024

Интеллектуальный анализ данных, моделирование, симуляция и визуализация

Название блога

"Любой, кто не может говорить ясно и просто, должен ничего не говорить и продолжать работать, пока не сможет это сделать".

Публикации в центре внимания

Искусственный интеллект в Microsoft Excel: наблюдайте за работой нейронной сети при решении задачи коммивояжера

Более практичный вариант использования динамических раскадровок в Excel: поддержка анимации двумерных данных путем отображения годов до и после текущего года на раскадровке

Обман некоторых удивительных интерактивных функций Tableau в Microsoft Excel: реплика Excel всех пиков Альп, визуализированная с помощью Tableau

Как визуализировать поездки туда и обратно на карте маршрутов в Excel, включая указание количества перевозимых между городами

Лицензия

Отказ от ответственности

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

Понедельник, 15 августа 2011 г.

Гистограммы с цветовой кодировкой в Microsoft Excel

Различные методы цветового кодирования точек данных гистограммы на основе второго ряда данных в Microsoft Excel

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

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

С Tableau Software цветовое кодирование ваших диаграмм очень просто. Просто перетащите размер или меру на полку цвета, и все готово. Microsoft Excel не имеет сопоставимых встроенных функций. Однако это не означает, что вы не можете использовать цветовую кодировку в своих диаграммах Excel. Конечно можно.

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

Основная идея

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

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

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

Техника 1 — несколько рядов данных

Я уверен, что вы уже знаете эту технику. Если вы хотите закодировать категории цветом (как в примере, упомянутом выше), вы добавляете столько рядов данных в гистограмму, сколько вам нужно (например, четыре региона продаж в нашем примере выше) и форматируете их, используя разные цвета заливки.

Очень простая формула IF гарантирует, что каждый ряд данных содержит значения KPI только в том случае, если строка (например, агент по продажам) принадлежит соответствующей категории (например, региону продаж) и NV() иначе.

Результат может выглядеть следующим образом:

Вот пример книги для бесплатного скачивания:

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

Возможно, вместо разных цветов вы использовали бы разные оттенки одного и того же цвета, например:

Вот соответствующая рабочая тетрадь для бесплатного скачивания:

Прием 2. Изменение цвета заливки с помощью VBA

Вы не возражаете против использования некоторых простых процедур VBA в своей рабочей тетради? Хорошо. Очень маленькая подпрограмма VBA может автоматически изменять цвет заливки полос. Таким образом, вам нужен только один ряд данных на гистограмме. Настройка очень похожа на технику 1 (цветовое кодирование меры):

Определите диапазоны значений (сколько хотите)
Определите цветовую шкалу, используя цвет заливки ячеек.
Используйте простую формулу ПОИСКПОЗ, чтобы назначить каждому значению правильный диапазон значений.
Остальное делается с помощью очень простой процедуры VBA, которая перебирает все точки данных в операторе For Next и устанавливает цвет заливки в соответствии с цветом заливки диапазона, к которому принадлежит это значение.

Посмотрите на рабочий лист [расчет] и небольшую процедуру VBA этой рабочей книги:

Подпрограмма VBA «ColorBarChart» назначается счетчику на панели инструментов, т. е. цвет меняется каждый раз, когда вы переключаетесь на другой набор данных. Конечно, вы также можете вызвать эту подпрограмму из Worksheet_Calculate или любой другой процедуры, управляемой событиями.

Прием 3. Изменение прозрачности с помощью VBA

Этот метод ограничен Excel 2007 или более поздней версии. Как и в методе 2, он использует VBA. Однако он меняет не цвет заливки полос, а прозрачность. Настройка рабочей книги проще, чем с двумя другими методами. Как и в методе 2, вам нужен только один ряд данных на гистограмме. Вам не нужно определять значения диапазона, все, что вам нужно сделать, это создать вспомогательный столбец, вычисляющий значения прозрачности с помощью довольно простой формулы.

Вот пример того, как это будет выглядеть:

Процедура VBA очень похожа на процедуру, используемую для метода 2. Вместо изменения свойства .Interior.ColorIndex она изменяет .Fill.Format.Transparency, используя процентные значения во вспомогательном столбце (см. выше).< /p>

Вот и все. Один вспомогательный столбец и 8 строк кода VBA, и все готово.

Вот учебник для бесплатного скачивания:

Пожалуйста, используйте это с осторожностью

Цветовое кодирование — это, несомненно, очень интересный способ визуализации данных. Тем не менее, вы всегда должны подумать дважды, прежде чем использовать его. С моей точки зрения, цветовое кодирование регионов продаж агентов (как показано в примере для метода 1) добавляет много очень полезной информации. Представьте, что гистограмма будет включать более 10 агентов. Вы могли легко видеть по цветам бара, что, например. 5 из 10 лучших исполнителей приходятся на один регион продаж.

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

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

Практический пример — диаграмма торнадо с цветовой кодировкой

Эта рабочая тетрадь Tableau включала множество таблиц и визуализаций, а в одной из них использовались гистограммы с цветовой кодировкой: Dashboard 2 содержала диаграмму, показывающую цели каждой команды в любой момент времени: голы за, голы против и разница мячей.< /p>

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

А как насчет разницы мячей? Конечно, столбцы, наклоненные вправо, имеют лучшую разницу мячей, и наоборот. Тем не менее, взгляните на пример, показанный выше. Представьте, что у вас не было бы цветового кодирования, смогли бы вы сразу увидеть, что разница мячей у «Астон Виллы» хуже, чем у «Блэкберн Роверс», или что разница мячей у «Ливерпуля» отрицательна? Я так не думаю.

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

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

Благодарности

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

Что дальше?

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

Если вас больше интересуют статьи о Microsoft Excel, ждите. Я также работаю над последующей публикацией сводной таблицы с текстами в области значений и над статьей о том, как выполнять детализацию в панели управления Excel.

Сегодня я получил электронное письмо от своей жены, которая очень любит кошек. Она говорит, что YouTube предложил ей посмотреть видео "Cat Icon Array Chart".А так как часто возникает необходимость показать долю значения в целом, используя диаграмму массива иконок, я решил написать «рецепт», который научит вас представлять данные именно так, без использования диаграмм.

В ячейке A1 введите число, представляющее отображаемый процент. Нам нужно изменить цвет шрифта в этой ячейке на красный. Затем создадим матрицу размером 10×10. В столбец слева вводим числа от 0 до 9, а в строку выше числа от 1 до 10. Заполняем матрицу символом кота, который получается с помощью шрифта Webdings и соответствующего код (используйте программу Widows Character Map, чтобы найти код).

Затем необходимо закрасить фон рабочего листа и фон матрицы для лучшего визуального вида. Числа в столбце слева и строке выше должны быть окрашены в тот же цвет, что и фон рабочего листа, чтобы их не было видно.

Наконец, нам еще нужно выбрать все ячейки в матрице и создать соответствующее правило условного форматирования. На вкладке Главная откройте меню Условное форматирование и выберите Новое правило. Когда откроется диалоговое окно, выберите параметр для ввода формулы и используйте следующее выражение:

Затем установите, что при выполнении условия значения в ячейке окрашиваются в темно-красный цвет.

Если вы все сделали правильно, все «котята», число которых меньше или равно числу, указанному в ячейке A1, будут окрашены в темно-красный цвет. Например, если мы ввели число 55, у нас будет 55 темно-красных кошек в матрице. Изменение значения ячейки A1 также обновит матрицу, раскрасив более или менее «котят».

Microsoft Excel не имеет функции, позволяющей построить диаграмму на основе одного набора данных с несколькими шкалами на оси. Обычно для этого используется диаграмма температуры со шкалами как по Цельсию, так и по Фаренгейту (как показано на пример ниже). Существует обходной метод для создания такого типа диаграмм со шкалами Цельсия и Фаренгейта, и в этом руководстве показано, как создать диаграмму в Excel (2003, 2007 и 2010). Этот метод также можно использовать для построения диаграмм с использованием температурной шкалы Кельвина.

Процесс создания диаграммы Excel по Цельсию и Фаренгейту можно обобщить следующим образом:

Создание основной диаграммы с исходными данными (в градусах Цельсия, Фаренгейта или Кельвина).
Добавьте на диаграмму второй набор данных, выходящий далеко за пределы требуемого диапазона температур.
Разделите диаграмму на две оси (основную для основных данных и дополнительную для нерелевантных данных).
Настройте основную ось так, чтобы она охватывала основной диапазон температур.
Выровняйте вторичную ось с вторичным диапазоном температур, это также скроет ненужные данные из второго этапа.

Учебное пособие. Таблица градусов Цельсия и Фаренгейта в Excel

В этом примере будет создана диаграмма температуры на основе приведенных ниже данных, диаграмма будет иметь две отдельные шкалы осей; первичная ось по Цельсию, вторичная по Фаренгейту. Окончательная диаграмма будет похожа на диаграмму Excel во введении.

На первом этапе строится первичная диаграмма на основе температуры и данных по оси X (в приведенном выше примере время в минутах). Для тех, кто умеет строить диаграммы, перейдите на страницу 2.

Этап 1 – первичный температурный график

На вкладке "Вставка" нажмите значок разброса, чтобы вставить пустую диаграмму.

Нажмите на пустую диаграмму и нажмите "Выбрать данные", чтобы открыть окно "Выбрать источник данных".

Нажмите кнопку "Добавить", чтобы добавить новый ряд в пустую диаграмму.

Нажмите значок "Значения серии X" (как показано выше), чтобы выбрать данные для оси x (горизонтальной).

Используйте окно "Редактировать серию", чтобы выделить нужные данные. В нашем примере выделен столбец "Время". нажмите правую кнопку в поле, чтобы вернуться в окно редактирования серии.

Повторите процесс для данных y (по вертикали), нажав кнопку "Значения серии Y".

В нашем примере температура указана в градусах Цельсия (C), но это могут быть градусы Фаренгейта (F) или Кельвины (K). После того, как данные выбраны, нажмите правую кнопку, а затем нажмите кнопку ОК в окне выбора источника данных. Будет построена очень простая температурная диаграмма только с основной температурной шкалой.

Если у вас много данных и вы хотите отобразить их графически, в Excel есть ограничение, заключающееся в том, что он не позволяет отображать более 255 рядов или данных. Количество данных в наборе не имеет значения, но вы можете отобразить только 255 рядов. Таким образом, если у вас более 255 наборов данных, появится сообщение "Максимальное количество рядов данных на диаграмме – 255".

То же самое, если вы хотите разделить один столбец на несколько меньших. Перейдите по этой ссылке.

Итак, здесь мы собираемся показать вам, как обойти это :-)

Теперь предположим, что у вас есть 300 наборов данных, и вы хотите отобразить их таким образом.

И вы хотите иметь это.

Вот как разделить эту очень длинную таблицу из 7 столбцов с более чем 300 наборами данных на то, что вы затем сможете отобразить.

Если вы попытаетесь создать таблицу для 300 наборов данных, вы получите следующую ошибку: "Максимальное количество рядов данных на диаграмме – 255".

Итак, мы покажем вам, как отобразить эти 300 наборов. Конечно, как и у любого трюка или ухищрения, есть побочные эффекты.

ШАГ 1:

Переименуйте (если можете, если не можете изменить формулу в соответствии с названием листа, на котором находятся ваши исходные данные) лист необработанных данных "rawdata"

Убедитесь, что вы начали с данных в строке 6, а заголовки — в строке 5.

Переименуйте заголовки 1,2,3,4,5,6,7, . (это одно из текущих ограничений, вам нужно пронумеровать заголовки).

Это должно выглядеть как предыдущее изображение.

ШАГ 2:

Создайте новый лист. Имя неважно. Этот лист будет для диаграммы или графика/графика, который вы будете делать.

Введите заголовок в строке 4.

В ячейке B1 введите количество столбцов плюс 1 (если у вас семь данных в наборе, введите 8)

В строке 4 введите названия верхних строк (B, C, . A1, если у вас так много данных в наборах). Здесь у нас есть только 7 данных на набор.

В строке 5 переименуйте заголовки данных так же, как на листе RAWDATA. Вы также можете скопировать и вставить его оттуда.

Это должно выглядеть так, как показано ниже.

ШАГ 3:

Скопируйте следующую формулу в ячейку B6.

Это должна быть первая ячейка ваших данных также на листе RAWDATA.

После нажатия клавиши ВВОД должны появиться данные ячейки B6 вашего листа RAWDATA

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

ШАГ 4:

Теперь потяните ячейку вправо.

ШАГ 5:

Теперь выберите диапазон от B4 до H6. Нажмите Ctrl-C или Скопируйте ячейки.

Вставьте ячейки в ячейку J4 (или 2 ячейки после последних данных вашего набора). Это должно выглядеть так.

А теперь уменьшите диапазон от A6 до P6, пока не достигнете набора данных 250.

Если вы видите 0 (нули) в правых столбцах, это означает, что все ваши данные теперь преобразованы в два столбца (у вас было менее 500 наборов данных). Вы можете перейти к шагу 6.

Если у вас более 500 наборов данных, вам придется создать третий (3-й) набор столбцов в соответствии с шагом 4.

ШАГ 6. Создайте диаграмму

Выберите диапазон ячеек от B5 до P255.

Вставьте точечную диаграмму с маркерами или без, и вы получите что-то вроде этого.

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

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