Некоторые типы диаграмм несовместимы друг с другом. Что делать в Excel

Обновлено: 06.07.2024

Если ваша организация перешла на Office 2016 или Office 365, теперь у вас есть доступ к Excel 2016. Что теперь? Есть ли новые функции? Ответ положительный, особенно если вам нужны инструменты бизнес-аналитики и новые способы представления данных.

Не пропустите новые функции Excel 2016. Вот 7 из них, которые меня больше всего волнуют:

1. Поле "Расскажи мне"

Поле «скажи мне» — одно из самых очевидных нововведений в программе Excel, и, честно говоря, очень жаль, что его не было в выпуске 2007 года, когда лента меню изначально была уменьшена. (Помните, как трудно было найти наши любимые инструменты?) Поле «подскажите» — это, по сути, функция поиска, в которой перечислены функции и операции, соответствующие условиям поиска. Это удобный способ добраться туда, куда вы хотите! Существует также интеллектуальный поиск, который будет использовать Интернет для поиска статей, релевантных вашему поисковому запросу.

2. Прогнозирование

В Excel 2016 добавлена возможность статистического прогнозирования одним щелчком мыши для данных, с которыми связан элемент времени. Пока у вас есть приличный объем данных, он сможет прогнозировать за пределами вашей последней точки данных, а также показывать уровни достоверности на каждом конце спектра. Что еще хорошо в функции прогнозирования, так это то, что если Excel может видеть тенденции из-за сезонности, он учитывает их в прогнозе. Довольно умный! После того, как вы сделали свой прогноз, вы сможете отобразить его в виде линейной или гистограммы, но имейте в виду, что данные, используемые для создания выбранной вами диаграммы, не будут связаны с диаграммой. Если вы внесете изменения в данные, они не будут автоматически отражены в диаграмме.

3. Поле поиска (сводные таблицы)

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

4. Группировка по дате (сводные таблицы)

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

5. Новые типы диаграмм

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

– Гистограммы и диаграммы Парето

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

– Диаграммы солнечных лучей

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

– Каскадные диаграммы

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

– 3D-карта данных

Возможно, самая впечатляющая из всех новых визуализаций в Excel 2016 – 3D-карта данных, которая на самом деле была надстройкой для версии 2013 года, но теперь является полностью интегрированной опцией. Эта диаграмма идеально подходит для анализа данных с глобальным охватом (например, отчет о продажах компании, в котором компания работает с глобальной клиентской базой). Очевидно, вам нужны данные о местоположении, чтобы использовать этот тип диаграммы, и затем вы можете добавить другие поля для построения своей картины.Диаграмма предоставит вам карту мира с полосами или столбцами в местах, к которым прикреплены поля. Более того, если вы установите его для определенного периода времени, диаграмма позволит вам записать видео моделирования, показывающее изменение значений за этот период. Он отлично подходит для демонстрации роста продаж совету директоров или вашей команде.

6. PowerPivot

Технически PowerPivot по-прежнему является надстройкой и не новинкой для Excel, но это отличный инструмент для переноса данных в Excel. На самом деле это позволяет вам импортировать большие объемы данных (здесь мы говорим о сотнях миллионов полей). PowerPivot даже имеет собственный язык функций, Data Analysis Expression или DAX, и именно в этом заключаются новые функции. Если вы чувствуете, что исчерпали все функции Excel, попробуйте PowerPivots.

7. Получить и преобразовать

Получить и преобразовать еще одно постоянное приспособление, появившееся в виде надстройки Power Query, помогает импортировать данные из различных источников данных. По-прежнему доступны стандартные варианты импорта из файла с разделителями-запятыми (CSV) или книги Excel, но теперь вы также можете выбрать такие параметры, как Facebook, Salesforce и другие программы и пакеты. Как только вы импортируете свои данные, это даст вам возможность привести данные в удобный для использования формат. Вы также можете применить инструменты "Получить и преобразовать" к данным, уже имеющимся в Excel.

Чтобы повысить эффективность работы с Excel 2016, пройдите практический курс от Global Knowledge. Наши вводные и промежуточные курсы персонализированы по версии, что позволяет вам выбрать ту, которую вы будете использовать. Наши расширенные курсы и курсы по Power BI относятся к версии 2016 и позволяют вам в полной мере использовать новые захватывающие функции.

Об авторе

Денис уже более 10 лет является сертифицированным инструктором Microsoft и ведет такие курсы Office, как Word, Excel, PowerPoint, Access, Outlook и OneNote, а также программирование на Visual Basic. Он дополняет свои навыки подготовки приложений опытом построения команды, разрешения конфликтов, тайм-менеджмента, а также написания предложений и отчетов.

Эта диаграмма содержит 2 ряда данных, линейный ряд и ряд столбцов.
Эти два ряда также нанесены на разные оси.

Создайте диаграмму по умолчанию.

Измените 2-й ряд на линию
Выберите отдельный ряд и выберите (Диаграмма > Тип диаграммы)
Измените тип диаграммы на линию.
Обратите внимание, что другой ряд остается в виде столбец.
Когда два ряда отображаются на разных осях, оси масштабируются независимо
При использовании двух осей необходимо убедиться, что вы указали, какой ряд построен на какой оси

Переместить ряд на другую ось
2D-диаграммы могут фактически иметь две оси.
Если диаграмма содержит более одного ряда, вы можете выбрать, какая ось будет использоваться для каждого ряда.
Двойной щелкните серию и выберите вкладку "Оси".

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

Комбинированные диаграммы

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

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

Как создать комбинированную диаграмму?

Чтобы создать диаграмму с комбинацией типов диаграмм, необходимо сначала создать диаграмму с одним типом диаграммы.
Выберите отдельные ряды данных и выберите (Формат > Тип диаграммы).
Вы можете выбрать отдельную серию и изменить тип диаграммы только для этой конкретной серии.
Вы можете создавать комбинированные диаграммы, содержащие два или более различных типов диаграмм. Например, диаграмма, состоящая из ряда столбцов и ряда линий. Не все типы диаграмм можно использовать в комбинированной диаграмме.
Вы можете изменить тип диаграммы для отдельной серии, выбрав серию вместо области диаграммы.
Чтобы включить в диаграмму более одного типа диаграммы , щелкните ряд данных и измените его тип диаграммы (Диаграмма > Тип диаграммы).
Это позволяет добавлять (и сохранять) собственные типы диаграмм, а также форматировать их??
Легенда обычно отражает любые изменения, внесенные в типы диаграмм.
Есть некоторые типы диаграмм, которые нельзя комбинировать на одной диаграмме

Гистограммы и гистограммы

2D- и 3D-диаграммы

Если вы попытаетесь объединить два несовместимых типа диаграмм, отобразится сообщение об ошибке.

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

Добавление второй оси

Когда значения для двух рядов данных очень разные, невозможно найти шкалу, подходящую для обоих.
Часто бывает полезно добавить на диаграмму вторую ось, когда вы используете комбинацию типов диаграмм.< br />Это может помочь идентифицировать отдельные значения.
Диаграмма, содержащая две оси, является простым примером комбинированной диаграммы.
Чтобы добавить дополнительную ось, выберите ("Параметры диаграммы", вкладка "Оси") и отметьте галочкой соответствующий флажок.

Важно

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

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

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

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

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

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

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

Начало работы

Чтобы проиллюстрировать шаги, которым вы должны следовать, нам нужно начать с некоторых данных. Как вы, возможно, уже догадались, мы собираемся сравнить исторический послужной список франчайзи двух компаний, Orange Ltd и Banana Ltd, в четырех разных штатах — Айове, Миннесоте, Техасе и Юте.

С учетом всего сказанного рассмотрим следующую таблицу:

Давайте рассмотрим каждый столбец подробнее.

Состояние. В этом столбце представлены категории, по которым диаграмма будет разбита на более мелкие диаграммы (панели). В нашем случае каждая линейная мини-диаграмма иллюстрирует динамику производительности для каждого из состояний.
Год — этот столбец определяет масштаб горизонтальной оси. Значения и форматирование должны быть одинаковыми на всех панелях.
Orange Ltd и Banana Ltd – это ваши фактические значения. Мы собираемся использовать только два набора данных для иллюстраций, но с методом, показанным в этом руководстве, предела нет.

В конечном итоге мы хотим, чтобы наши данные выглядели так:

Итак, давайте приступим.

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

Сначала справа от фактических данных (столбец E) настройте вспомогательный столбец под названием "Разделитель". Цель этого столбца — разделить данные на две чередующиеся категории, выраженные значениями 1 и 2, чтобы заложить основу для будущей сводной таблицы.

Введите «1» в каждую соответствующую ячейку разделителя столбцов, которые относятся к первой категории, Айова (E2:E7). Затем введите «2» во все соответствующие ячейки, которые попадают во вторую категорию, Миннесота (E8:E13).

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

Выделите любую ячейку в диапазоне набора данных (A1:E25).
Перейдите на вкладку "Вставка".
Выберите «Сводная таблица».

Когда появится диалоговое окно "Создание сводной таблицы", выберите "Существующий лист", выделите любую пустую ячейку рядом с фактическими данными (G1) и нажмите "ОК".

Сразу после создания сводной таблицы появится панель задач "Поля сводной таблицы". На этой панели задач переместите элементы в списке полей в следующем порядке (порядок важен), чтобы изменить макет новой сводной таблицы:

На этом этапе ваша сводная таблица должна выглядеть примерно так:

Теперь измените тип макета и удалите лишние элементы таблицы.

Выберите любую ячейку в сводной таблице (G4:L26).
Перейдите на вкладку "Дизайн".
Нажмите "Макет отчета".
Выберите «Показать в виде таблицы».

Оставаясь на вкладке "Дизайн", нажмите значок "Общие итоги" и выберите "Отключить для строк и столбцов" в раскрывающемся меню.

Затем нажмите «Промежуточные итоги» и выберите «Не показывать промежуточные итоги».

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

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

Строка заголовка (P3:S3) только что созданной таблицы отвечает за отображение элементов условных обозначений диаграммы, поэтому давайте также быстро добавим их.

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

Наконец, постройте обычную линейную диаграмму и посмотрите, что получится.

Выделите таблицу, содержащую данные, извлеченные из сводной таблицы (N3:S27).
Перейдите на вкладку "Вставка".
Нажмите кнопку "Вставить график или диаграмму с областями".
Выберите «Линия».

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

Нажмите правой кнопкой мыши любой ряд данных и выберите "Форматировать ряд данных".

В области задач "Формат ряда данных" выполните следующие действия:

Перейдите на вкладку "Заливка и линия".
В разделе "Линия" выберите "Сплошная линия".
Нажмите значок "Цвет контура" и выберите нужный цвет из палитры. (Повторите для каждой строки.)
Внимательно проверьте легенду диаграммы, чтобы убедиться, что вы правильно сопоставили цвета.

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

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

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

Настройте отдельную фиктивную таблицу точно так же, как показано на снимке экрана ниже. Оставьте ячейку U4 пустой, чтобы все заработало, и введите "Разделители" в V4.
Введите «0» в V5 и скопируйте его вниз. Количество строк, содержащих значения в таблице, показывает, сколько баров ошибок будет в конечном итоге создано. В этом случае нам нужно три планки погрешностей, поэтому в них будет три ячейки с «0».
Введите «=COUNTA(O4:O9)+0,5» в поле U5.

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

Теперь введите «=U5+COUNTA(O4:O9)» в U6 и скопируйте его в U7.

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

Выделите все значения во вспомогательной таблице, кроме строки заголовка (U4:V7).
Выберите область диаграммы.
Перейдите на вкладку "Главная".
Нажмите "Вставить".
Выберите «Специальная вставка».

В появившемся диалоговом окне выполните несколько простых шагов:

В разделе "Добавить ячейки как" выберите "Новая серия".
В разделе "Значения (Y) в" выберите "Столбцы".
Отметьте флажки "Названия серий в первой строке" и "Категории (метки X) в первом столбце".
Нажмите "ОК".

Щелкните правой кнопкой мыши только что добавленный ряд данных (ряд «Разделители») и выберите «Изменить тип диаграммы ряда».

Для серии "Разделители" измените "Тип диаграммы" на "Разброс с прямыми линиями".

Сначала удалите дополнительную горизонтальную ось. Щелкните правой кнопкой мыши числа в верхней части диаграммы и выберите "Удалить".

Затем щелкните правой кнопкой мыши дополнительную вертикальную ось вдоль правой стороны диаграммы и выберите "Формат оси".

На панели задач "Формат оси" задайте диапазоны масштаба оси:

На той же вкладке прокрутите вниз до раздела «Ярлыки» и измените «Положение метки» на «Нет», чтобы скрыть шкалу оси — если вы просто удалите ее, это испортит диаграмму.

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

Выберите серию "Разделители".
Нажмите значок "Элементы диаграммы".
Нажмите на стрелку рядом с пунктом "Погрешности".
Выберите «Дополнительные параметры».

В области задач "Формат полос ошибок" измените разделители.

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

Сначала разберемся с фиктивной серией. Выберите серию «Разделители», перейдите к области задач «Формат серии данных» и на вкладке «Параметры серии» выберите «Без строки».

Теперь удалите горизонтальные полосы ошибок и все элементы легенды, кроме «Orange Ltd» и «Banana Ltd», выбрав каждый элемент, щелкнув правой кнопкой мыши и выбрав «Удалить». К концу этого шага ваша панельная диаграмма должна выглядеть так:

Измените название диаграммы, и ваша великолепная панельная диаграмма готова к работе!

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

Вот здесь комбинированная диаграмма действительно полезна. Комбинированные диаграммы недоступны на ленте Excel (Excel 2007)

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

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

Диаграмма-пример начиналась как стандартная столбчатая диаграмма, а через несколько щелчков мыши была создана умная и более подходящая комбинированная диаграмма. Устройтесь поудобнее и выпейте кофе - молодец!.

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

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