Почему диаграмма в эксель пустая

10 ошибок в использовании средств графиков и диаграмм

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

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

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

1. Вертикальная ось Y начинается не с 0

Гистограммы (столбчатые диаграммы) в основном используются для визуализации относительных размеров. Соответственно если пользователь информации видит, что один столбик на графике в два раза больше другого, он подсознательно делает вывод о двукратной разнице в количественном значении. Но это сравнение будет ошибочным, если ось не будет начинаться с нулевой отметки.

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

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

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

2. Временная ось неправильно отражает целостность периода

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

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

3. Выбор неправильного типа графика

График позволяет выразить идею, которую несут данные, наиболее полно и точно, поэтому очень важно выбрать подходящий тип диаграммы. Гистограммы (столбчатые диаграммы) лучше воспринимаются при сравнении категорий, линейные графики — при демонстрации изменения категории во времени, а круговые диаграммы – для визуализации структуры. Графики рассеивания (scatter charts) лучше не применять для беседы с нетехническими специалистами, они больше подходят для анализа сырых первичных данных.

Первый пример показывает разницу восприятия линейной и столбчатой диаграмм при сравнении категорий (источник данных один и тот же):

А на этом примере сразу видно, какой из типов графиков понятнее и эффективнее передаёт временную динамику показателей:

4. Использование 3D-эффектов

Красота в графике не всегда является синонимом понятности. За счёт добавления дополнительного третьего измерения в диаграммы происходит сильное искажение пропорций по принципу близкий/далёкий объект. Эффект от этого можно увидеть на примере ниже. Как Вы думаете, какую диаграмму легче прочитать?

По этой причине, большинство 3D-визуализаций как минимум не делают информацию понятнее. На 3D круговой диаграмме ниже, какой сектор больше: зелёный или синий, красный или сиреневый? Визуально груш предприятие продало гораздо больше, чем яблок. На самом же деле зелёный и синий сектора абсолютно равны, так же как равны и красный с сиреневым. Это чётко видно на двумерном варианте данной диаграммы справа.

Поэтому если у Вас нет действительно очень, очень веской причины использовать 3D диаграммы – лучше просто забыть об их существовании.

5. Плохое или избыточное оформление

Конечно же, на вкус и цвет все фломастеры разные – но всё-таки хорошим тоном в визуализациях считается минимализм, стремление убрать всё лишнее, всё то, что может отвлекать от сути информации. Ниже приведён пример не слишком удачного стиля оформления визуализации (точка безубыточности какого-то производства).

Можно выделить следующие моменты, которые точно не способствуют лучшему восприятию:

• отсутствие заголовка;
• отсутствие подписей осей;
• шрифт с засечками;
• изобилие чёрных вспомогательных линий;
• вертикальные или диагональные надписи – они гораздо хуже читаются;
• отдельная легенда (само собой с чёрной линией границы), при наличии возможности подписать данные на самой диаграмме.

А теперь посмотрим на ту же информацию, только в несколько другом виде. Комментарии тут, я думаю, излишни.

6. Слишком много категорий

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

Лучшие практики визуализации советуют делить структуру максимум на 4-5 частей. В крайнем случае, всегда можно применить группировку категорий или разделить информацию на несколько диаграмм. Наглядность это ошибки продемонстрирована на примере ниже.

7. Отсутствие акцента

Любая визуализация – это способ представления информации, способ донести до аудитории какую-то мысль, идею. Вы же собираетесь что-то сказать своим графиком – так сделайте это! Если же на диаграмме нет никакого акцента, взгляд ни за что не цепляется – вы рискуете получить несфокусированное внимание потребителя информации. Ниже пример графиков с явным акцентом.

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

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

8. Искажение при группировке в категорию «Другие»

Категорию «Другие» лучше делать только когда в неё группируется лишь малая часть данных. Иначе можно легко исказить восприятие информации. Для примера посмотрите на эти два графика:

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

9. Некорректное использование усреднённых данных

Для наглядной демонстрации данной ошибки посмотрите на небольшую визуализацию слева. Что по ней можно сказать? Мы видим позитивную динамику продаж, у предприятия всё хорошо. Но если развернуть эту же информацию по категориям проданных товаров, то ситуация предстаёт совсем в другом свете. Оказывается, по всем категориям, кроме мандаринов, наблюдается спад продаж. И только за счёт мандаринов предприятие в целом смогло продать в 2016 г. больше, чем в 2015 г. С этой точки зрения явно есть над чем задуматься.

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

10. Круговые диаграммы не отображают целое (100%)

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

В качестве эпилога

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

Автор: Виктор Рыжов

ПРО НАС

Bintels – консалтингова компанія, що спеціалізується на розробці та впровадженні бізнес-аналітики на базі Microsoft Power BI. Проводимо корпоративні тренінги з Power BI

ЗАПИСИ БЛОГА

• Аналитика TED Talks на базе Power BI
• Эверест. Визуализация данных на базе Microsoft Power BI
• Bintels нанесен на первую в Украине карту рынка fintech!

КОНТАКТИ

+38-096-479-2383
info@bintels.com
м. Київ, вул. Жилянська, 148

покупка

Как пропустить пустые ячейки при создании диаграммы в Excel?

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

Отображать пробелы в таблице как ноль

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

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

2. Затем в Выберите источник данных диалоговое окно, нажмите Скрытые и пустые ячейкиИ в Настройки скрытых и пустых ячеек диалог, проверьте 0 вариант. Смотрите скриншот:

3. Нажмите OK > OK , чтобы закрыть диалоговые окна, а пустые ячейки отображаются как нули. Смотрите скриншот:

Пропускать пробелы в таблице

Чтобы пропустить пробелы непосредственно в диаграмме, вам понадобится формула перед созданием диаграммы.

1. Выберите пустую ячейку рядом со значениями, по которым вы хотите создать диаграмму, и введите эту формулу = ЕСЛИ (ISBLANK (B2), # N / A, B2), B2 — это используемая вами ячейка, и перетащите дескриптор автоматического заполнения вниз. к ячейкам нужно применить эту формулу. Смотрите скриншот:

2. Затем, не снимая выделения с ячеек формулы, щелкните Вставить вкладку и вставьте нужную диаграмму в Графики группа. Смотрите скриншот:

Тогда пустые ячейки на диаграмме игнорировались.

Наконечник: Если вы обычно используете сложные диаграммы в Excel, что будет затруднительно, поскольку вы создадите их очень быстро, здесь с Авто текст инструмент Kutools for Excel, вам просто нужно сначала создать диаграммы, затем добавить диаграммы в панель автотекста, а затем вы можете повторно использовать их в любом месте в любое время, вам нужно только изменить ссылки в соответствии с вашими реальными потребностями. Нажмите, чтобы бесплатно скачать сейчас.

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

При создании графика в Microsoft Excel диаграмма может содержать пробелы в строке.

Причина

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

Обходной путь

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

Способ 1

Отображение пустых ячеек в виде интерполяции. Для этого выполните следующие действия:

Excel 2000 – 2003

1. Щелкните график, чтобы запустить его.
2. В меню Сервис выберите пункт Параметры.
3. На вкладке Диаграмма выберите пункт с интерполяцией, а затем нажмите кнопку ОК.

Excel 2007

1. Выделите диаграмму и щелкните правой кнопкой мыши в любом месте диаграммы.
2. Нажмите кнопку выбрать данные , а затем — скрытые и пустые ячейки.
3. Щелкните, чтобы установить соединение точек данных с линией, а затем дважды нажмите кнопку ОК .

Способ 2

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

• В пустой ячейке введите #N/a.
• В строке формул для пустой ячейки введите = НД ().
• Если диапазон ячеек для линейной диаграммы использует формулу для получения значений из другого диапазона ячеек, и если вы не хотите, чтобы в графике была указана 0 (ноль), в строке формул введите следующую формулу: = Если (SUM (диапазон) = 0; НД (); Sum (диапазон))Диапазонзаметок — это диапазон ячеек, который находится за пределами диапазона ячеек для графика.

Отображение пустых ячеек, пустых значений (#Н/Д) и скрытых данных листа на диаграмме

Excel для Microsoft 365 Word для Microsoft 365 Outlook для Microsoft 365 PowerPoint для Microsoft 365 Excel 2021 Word 2021 Outlook 2021 PowerPoint 2021 Excel 2019 Word 2019 Outlook 2019 PowerPoint 2019 Excel 2016 Word 2016 Outlook 2016 PowerPoint 2016 Excel 2013 Word 2013 Outlook 2013 PowerPoint 2013 Excel 2010 Word 2010 Outlook 2010 PowerPoint 2010 Excel 2007 Word 2007 Outlook 2007 PowerPoint 2007 Еще. Меньше

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

На графиках, точечных и лепестковых диаграммах вы также можете изменить способ отображения пустых ячеек и ячеек с ошибкой #Н/Д. Вместо отображения пустых ячеек в виде пробелов можно отобразить пустые ячейки в виде нуля значений (0) или можно поместить пробелы строкой. Значения #Н/Д могут отображаться как пустые ячейки или в виде линий, соединяющих точки данных. В приведенных ниже примерах показаны все варианты обработки этих ситуаций в Excel.

Варианты для отображения пустых ячеек

Варианты для отображения ячеек со значением #Н/Д

Изменение способа отображения пустых ячеек, пустых значений (#Н/Д), скрытых строк и столбцов на диаграмме

1. Щелкните диаграмму, которую вы хотите изменить.
2. На ленте в группе вкладок Работа с диаграммами откройте вкладку Конструктор и в группе Данные нажмите кнопку Выбрать данные.
3. Нажмите кнопку Скрытые и пустые ячейки.
4. В разделе Показывать пустые ячейки как: выберите вариант пустые значения, нулевые значения или линию.

Примечание: На точечной диаграмме, на которой есть только маркеры (без соединительных линий), можно отобразить пустые ячейки только в виде пропусков или нулей: соединить точки данных линией невозможно.

Эта функция доступна только в том случае, если у вас есть подписка на Microsoft 365 и в настоящее время доступна только участникам программы предварительной оценки. Если вы являетесь Microsoft 365подписчиком , убедитесь, что у вас установлена последняя версия Office.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту сообщества Excel Tech Community, попросить помощи в сообществе Answers, а также предложить новую функцию или улучшение. Сведения о том, как поделиться своими мыслями, см. в статье Разделы справки оставить отзыв о Microsoft Office? Поделитесь с нами своими мыслями.