ГРАФИК
Диаграмма График во многом аналогична Гистограмме с группировкой: к ней применимы те же идеи по отображению двух рядов со значительно отличающимися значениями, что и к Гистограмме.
После размещения рядов на разных осях получим вот такую диаграмму (линии вертикальных осей выделены цветами, соответствующими цветам рядов).
Примечание . Если провести горизонтальные линии сетки к вспомогательной оси, то они, естественно, могут не совпасть с линиями к основной оси, т.к. масштабы осей (величины основных делений вертикальных осей) могут не совпадать. Это может «перегрузить» диаграмму.
Вообще, к использованию вспомогательных вертикальных осей, а тем более вспомогательных горизонтальных осей для Гистограммы и Графика нужно подходить обдуманно: ведь диаграмма должна «читаться» — быть понятной без дополнительных комментариев.
Вставка и построение
Для примера используем таблицу выручки и затрат за год, на основании которой построим простой график:
| янв.13 | фев.13 | мар.13 | апр.13 | май.13 | июн.13 | июл.13 | авг.13 | сен.13 | окт.13 | ноя.13 | дек.13 | |
| Выручка | 150 598р. | 140 232р. | 158 983р. | 170 339р. | 190 168р. | 210 203р. | 208 902р. | 219 266р. | 225 474р. | 230 926р. | 245 388р. | 260 350р. |
| Затраты | 45 179р. | 46 276р. | 54 054р. | 59 618р. | 68 460р. | 77 775р. | 79 382р. | 85 513р. | 89 062р. | 92 370р. | 110 424р. | 130 175р. |
Вне зависимости от используемого типа, будь это гистограмма, поверхность и т.п., принцип создания в основе не меняется. На вкладке «Вставка» в приложении Excel необходимо выбрать раздел «Диаграммы» и кликнуть по требуемой пиктограмме.
Выделите созданную пустую область, чтобы появились дополнительные вкладки лент. Одна из них называется «Конструктор» и содержит область «Данные», на которой расположен пункт «Выбрать данные». Клик по нему вызовет окно выбора источника:
Обратите внимание на самое первое поле «Диапазон данных для диаграммы:». С его помощью можно быстро создать график, но приложение не всегда может понять, как именно хочет видеть это пользователь
Поэтому рассмотрим простой способ добавления рядов и осей.
На упомянутом выше окне нажмите кнопку «Добавить» в поле «Элементы легенды». Появится форма «Изменение ряда», где нужно задать ссылку на имя ряда (не является обязательным) и значения. Можно указать все показатели вручную.
После занесения требуемой информации и нажатия кнопки «OK», новый ряд отобразиться на диаграмме. Таким же образом добавим еще один элемент легенды из нашей таблицы.
Теперь заменим автоматически добавленные подписи по горизонтальной оси. В окне выбора данных имеется область категорий, а в ней кнопка «Изменить». Кликните по ней и в форме добавьте ссылку на диапазон этих подписей:
Посмотрите, что должно получиться:
Пример использования второй вертикальной оси на графике Excel
Рассмотрим пример использования второй вертикальной оси на графике Excel для наглядной демонстрации различий в температуре на протяжении года и уровне осадков.
1. Откройте Excel и введите данные о температуре и уровне осадков в два столбца. Убедитесь, что данные о температуре и осадках находятся в разных столбцах.
2. Выделите оба столбца с данными, затем выберите вкладку «Вставка» на верхней панели меню.
3. В группе «Графики» выберите тип графика, который хотите использовать, например, «Сглаженная линия».
4. На графике справа щелкните правой кнопкой мыши и выберите «Изменить тип графика».
5. В диалоговом окне «Тип диаграммы» выберите «Комбинированный».
6. В списке доступных типов диаграмм выберите «Сглаженная линия с подписью».
7. На панели «Данные ряда» выберите второй столбец с данными об уровне осадков и нажмите кнопку «Ось», затем выберите «Вторичная ось».
8. Нажмите «ОК», чтобы закрыть диалоговое окно «Тип диаграммы».
9. Теперь у вас на графике появится вторая вертикальная ось, которая позволит вам лучше сравнивать температуру и уровень осадков в течение года.
10. Чтобы настроить имена осей и другие параметры, щелкните на них правой кнопкой мыши и выберите «Формат оси».
Таким образом, используя вторую вертикальную ось на графике Excel, вы можете наглядно представить различные наборы данных с учетом их особенностей и диапазонов. Это очень полезный инструмент для анализа и визуализации информации.
Создаем диаграмму
Решать задачу мы будем разными способами, вы же выберете наиболее подходящий. Итак, приступаем.
Гистограмма
Данный тип подойдет тогда, когда нам нужно просто визуально отобразить значения, или сравнить их с другими.
- Для того чтобы начать создавать диаграмму, изначально следует иметь данные, которые лягут в ее основу. Поэтому, выделяем весь столбик цифр из таблички и жмем комбинацию кнопок Ctrl+C.
- Далее, кликаем по вкладке Вставка и выбираем гистограмму. Она как нельзя лучше отобразит наши данные.
- В результате приведенной последовательности действий в теле нашего документа появится диаграмма. В первую очередь нужно откорректировать ее положение и размер. Для этого тут есть маркеры, которые можно передвигать.
- Мы настроили конечный результат следующим образом:
- Давайте придадим табличке название. В нашем случае это Цены на продукты. Чтобы попасть в режим редактирования, дважды кликните по названию диаграммы.
- Также попасть в режим правки можно кликнув по кнопке, обозначенной цифрой 1 и выбрав функцию Название осей.
- Как видно, надпись появилась и тут.
Так выглядит результат работы. На наш взгляд, вполне неплохо.
Сравнение разных значений
Если у вас есть несколько значений, их тоже можно добавить сюда же, так, мы сможем получить отличный материал для визуального сравнения.
- Копируем цифры второго столбца.
- Теперь выделяем саму диаграмму и жмем Ctrl+V. Эта комбинация вставит данные в объект и заставит упорядочить их, снабдив столбиками разной высоты.
В программе есть сотни других типов графиков, их можно найти в меню Вставка. Путем проб и комбинаций с каждым нужно разбираться по-отдельности.
Процентное соотношение
Для того чтобы более четко понимать роль различных ячеек нашей таблицы и в целом ее значений, можно сопоставить результаты в виде круговой диаграммы. Причем делать это мы будем с выводом процентного соотношения. Приступаем.
- Как и в предыдущих случаях копируем данные нашей таблички. Для этого достаточно выделить их и нажать комбинацию клавиш Ctrl+C. Также можно воспользоваться контекстным меню.
- Снова кликаем по вкладке Вставка и выбираем круговую диаграмму из списка стилей.
- Как только метод вывода будет добавлен, вы увидите следующую картину:
- Далее, нам потребуется сменить стиль и выбрать профиль с отображением процентов. Делается это после выделения готового объекта в списке стилей.
- Ну что, совсем другой результат. Диаграмма смотрится вполне профессионально. Нам удалось сделать поистине отличный наглядный индикатор.
Все параметры, включая цвета, шрифты и их тени гибко настраиваются в Microsoft Excel. Обилие управляющих элементов поистине велико.
ТОЧЕЧНАЯ
Визуально Точечная диаграмма похожа на диаграмму типа График (если конечно у Точечной диаграммы точки соединены линиями).
Различие состоит в том, что для построения Графика в качестве координаты Х всегда используется последовательность 1, 2, 3, … (с подписями – категориями), а для Точечной значения по горизонтальной оси м.б. любыми числами (см. статью График vs Точечная ).
Примечание . Если для построения Точечной диаграммы не указана ссылка на значения Х (или ссылка указывает на текстовые значения), то в качестве координат по Х будет использована та же последовательность 1, 2, 3, …, что и для Графика.
Исходя из вышесказанного, при построении диаграммы Точечная целесообразно указывать числовые значения по Х. В противном случае нужно просто использовать График, т.к. для него можно задавать любые подписи по Х (в том числе и текстовые), что для Точечной диаграммы сделать нельзя (только числа).
Теперь о совмещении разных типов диаграмм с Точечной. Если для Точечной диаграммы не используется координата Х, то на диаграмме она выглядит как График.
Подписи по горизонтальной оси берутся от Графика. В окне Выбор источника данных видно, что для ряда отображаемого Точечной диаграммой подписи горизонтальной оси одинаковы изменить/ удалить нельзя.
Кроме того, График может быть только на основной оси и поменять это нельзя.
Если для Точечной диаграммы используется координата Х и оба ряда отложены по одной (основной) оси, то получается не очень красивая диаграмма.
Это связано с тем, что при совмещении с Точечной диаграммой (на одной оси и когда у Точечной указаны значения по Х), диаграмма типа График становится как бы главной:
- на горизонтальной оси отображаются подписи только для Графика;
- вертикальная сетка не отображается для отрицательных значений Х (т.к. График строится только для Х=1, 2, 3, …);
- у Графика невозможно изменить Ось с Основной на Вспомогательную (у Точечной можно).
Если Точечную построить на вспомогательной оси, то диаграмма изменится.
Подписи для Точечной (значения по Х) теперь отображаются сверху.
Совет . Диаграмма типа Точечная используется для построения окружностей, эллипсов и других замкнутых фигур на плоскости .
Теперь рассмотрим построение 2-х рядов данных, которые используют диаграмму Точечная.
Сначала построим 2 эллипса с различными координатами центра и размерами полуосей без использования вспомогательных осей.
Примечание . Фактически на диаграмме 4 ряда данных: точка центра представляет отдельный ряд.
Дважды кликнем по бордовому эллипсу и выберем построение ряда на вспомогательной оси (сделаем то же и центра эллипса).
Теперь координаты Y для бордового эллипса откладываются по правой вертикальной оси (можно ее для наглядности выделить также бордовым цветом).
Добавим Вспомогательную горизонтальную ось (в меню Оси (вкладка Макет , группа Оси ) выберите Вспомогательная горизонтальная ось и установите ее По умолчанию ).
Наверное, для диаграммы типа Точечная использование вспомогательных осей наиболее оправдано – это позволяет отображать на одной диаграмме различные по масштабу кривые.
Типы диаграмм
График
Идеально подходить для отображения изменения объекта во времени и определения тенденций.Пример отображения динамики затрат и общей выручки компании за год:
Гистограмма
Хорошо подходит для сравнения нескольких объектов и изменения их отношения со временем.Пример сравнения показателя эффективности двух отделов поквартально:
Круговая
Предназначения для сравнения пропорций объектов. Не может отображать динамику.Пример доли продаж каждой категории товаров от общей реализации:
Диаграмма с областями
Подходит для отображения динамики различий между объектами во времени
При использовании данного типа важно соблюдать порядок рядов, т.к. они перекрывают друг друга
Допустим, существует необходимость отобразить загрузку отдела продаж и ее покрытие персоналом. Для этого показатели потенциала сотрудников и загрузки были приведены к общей шкале.
Так как для нас первостепенно видеть именно потенциал, то данный ряд отображается первым. Из ниже приведенной диаграммы видно, что с 11 часов до 16 часов отдел не справляет с потоком клиентов.
Точечная
Представляет собой систему координат, где положение каждой точки задается значениями по горизонтальной (X) и вертикальной (Y) осям. Хорошо подходить, когда значение (Y) объекта зависит от определенного параметра (X).
Пример отображения тригонометрических функций:
Поверхность
Данный тип диаграмм представляет собой трехмерные данные. Ее можно было бы заменить несколькими рядами гистограммы либо графика, если бы ни одна особенность – она не подходит для сравнения значений рядов, она предоставляет возможность сравнения между собой значений в определенном состоянии. Весь диапазон значений разделен на поддиапазоны, каждый из которых имеет свой оттенок.
Биржевая
Из названия понятно, что подобный тип диаграмм идеально подходит для отображения динамики торгов на биржах, но может быть также использован для других целей.
Обычно подобные диаграммы отображают коридор колебания (максимальное и минимальное значение) и конечное значение в определенных период.
Лепестковая
Особенность этого типа диаграмм заключается в том, что горизонтальная ось значений расположена по кругу. Таким образом, она позволяет более наглядно отобразить различия между объектами по нескольких категория.
На ниже приведенной диаграмме представлено сравнение 3-х организаций по 4-ем направлениям: Доступность; Ценовая политика; Качество продукции; Клиентоориентированность. Видно, что компания X лидирует по первому и последнему направлению, компания Y по качеству продукции, а компания Z предоставляет лучшие цены.
Также можно сказать, что компания Х является лидером, т.к. площадь ее фигуры на диаграмме является самой большой.
Как построить график зависимости в Excel?
График зависимости по сути своей и есть график функции; речь может идти лишь о сложности математического выражения, в остальном порядок создания визуальных представлений остаётся тем же. Чтобы показать, как построить график сложной зависимости нескольких параметров от исходных значений, ниже будет приведён ещё один небольшой пример.
Пусть параметр Y зависит от X в виде y = x3 + 3x – 5; Z — в виде z = x/2 + x2; наконец, зависимость R — выражается в виде набора несистематизированных значений.
Тогда, чтобы построить сводный график зависимости, необходимо:
Составить в Excel таблицу с заголовками, отображающими суть каждой зависимости. Пусть для примера это будут просто X, Y, Z и R. В этой таблице сразу можно задать значения оси абсцисс (X) и параметра R, не выражаемого известной функцией.
Ввести в верхней ячейке столбца Y формулу, нажать клавишу Enter и «растянуть» значения на весь диапазон X.
То же проделать для столбца Z. Как можно убедиться, при изменении любого параметра X будут меняться соответствующие значению Y и Z, в то время как R останется неизменным.
Выделить три столбца производных от X и построить, как было рассказано раньше, график — гладкий, с маркерами или в виде точек.
Если одна из функций мешает наблюдать за изменениями остальных, её можно удалить с графика, выделив щелчком мыши и нажав клавишу Delete.
Научившись строить графики в Экселе, пользователь может перейти к следующей важной задаче — попытаться сделать оформление каждой зависимости красивым и рациональным
Работа с Excel версии 2013 года
Нарисовать график в Excel 2013 можно также по алгоритму, прописанному в начале статьи. Из новинок в инструментах программы отметим:
- представление видов модификации рисунка в удобном меню;
- появились маркеры, с помощью которых меняются размеры поля;
- теперь корректировка легенды проводится в одной вкладке.
Чтобы открыть поле форматирования легенды, нужно кликнуть по одному из обозначений. В выпадающем окошке появится строка «Формат легенды».
Возможности построения графиков в Эксель безграничны
Сетевой график в Excel 2013
Это уникальный инструмент, который используется для визуализации выполнения задач проекта. Чтобы построить такой рисунок, нужно зайти во вкладку «Вид» в панели инструментов диаграмм, и выбрать соответствующий пункт меню. С помощью настроек создаете легенду и ее оформление, задаете размеры рамок и выбираете те сведения, которые необходимо отразить. Между разными блоками можно построить график зависимости в Excel, с помощью которого отображаются взаимосвязи объектов.
Чтобы к этому рисунку добавить легенду, придется открыть Печать документа. Здесь в параметрах страницы есть вкладка «Легенда», в которой и нужно внести всю информацию. По окончании работы нажмите «Ок» и отмените печать.
Чтобы изменить границы рисунка, нужно во вкладке «Сетевой график» открыть «Макет», где есть параметры границ. Если нужно просто расположить изображение по центру, нажмите «Фиксированная ширина и высота».
Другие возможности программы
Вы можете построить ступенчатый график в Excel. Он показывает изменения одного показателя за определенное время, как и другие виды визуализации. Однако для его построения используется понятие «Планки погрешностей». Это третий показатель, который используется в создании рисунка.
Для работы на приведенном примере нужно:
- В соседнем от последнего рабочего столбце С (в первой рабочей ячейке) создать формулу B3-B2.
- Копируем формулу во все рабочие ячейки столбца С.
- Делаем точечную диаграмму с прямыми отрезками с использованием столбцов А и В.
- Рядом с ней будет знак плюса, при нажатии на него откроется группа функций.
- Выбираем «Предел погрешности».
- Во вкладке «Макет» выбираем «Текущий фрагмент».
- Открываем «Планки погрешностей» по оси Х и кликаем по формату выделенного.
В открывшемся поле нужно отметить в «Направление» кнопку «Плюс», в конечном стиле «Без точки». А величина погрешности должна быть отмечена как фиксированная – единица. После этого нужно таким же способом открыть «Планки погрешностей по оси У». Здесь первые два параметра такие же, как у оси Х, только величина погрешности отмечается как «Пользовательская». В поле «Отрицательные значения ошибки» вставьте данные из столбца С.
Вы также можете начертить два графика на одной диаграмме Excel. Сначала по первым данным постройте диаграмму выбранного типа. Затем нужно добавить вторую в это же поле. Для этого в конструкторе нужно выбрать легенду, и в ней добавить «Имя ряда», где в качестве значения указать новые данные.
На практике каждый из описанных способов работы осуществляется просто и с минимальными временными затратами.
Как добавить вспомогательную ось к диаграмме Excel на Mac
Шаг 1: Вводим данные в таблицу
Пусть в строке 1 будут находиться подписи оси Х, а в строках 2 и 3 – подписи для двух осей Y.
В данном примере: в первой строке указаны размеры обуви, во второй строке – количество проданной обуви, а в третьей – процент проданной обуви.
Шаг 2: Создаём диаграмму из имеющихся данных
Выделяем данные, которые нужно показать на диаграмме. Затем в меню нажимаем Диаграммы (Charts), выбираем Гистограмма (Column) и кликаем вариант Гистограмма с группировкой (Clustered Column) вверху слева.
Диаграмма появится чуть ниже набора данных.
Шаг 3: Добавляем вспомогательную ось
Теперь построим график по вспомогательной оси для данных из строки Percent of Nike Shoes Sold. Выделяем диаграмму, рядом с меню Диаграммы (Charts) должен появиться сиреневый ярлык – Макет диаграммы (Chart Layout). Жмем на него.
В левом верхнем углу в разделе Текущий фрагмент (Current selection) открываем выпадающий список и выбираем Percent of Nike Shoes Sold или любой другой ряд данных, который нужно отложить по вспомогательной оси.
Когда выбран нужный ряд данных, нажимаем кнопку Формат выделенного (Format Selection) сразу под выпадающим списком. Появится диалоговое окно, в котором можно выбрать вспомогательную ось для построения графика. Если раздел Оси (Axis) не открылся автоматически, кликните по нему в меню слева и выберите вариант Вспомогательная ось (Secondary Axis). После этого нажмите ОК.
Шаг 4: Настраиваем форматирование
Теперь график Percent of Nike Shoes Sold перекрывает график Number of Shoes Sold. Давайте исправим это.
Выделяем график и снова кликаем зелёную вкладку Диаграммы (Charts). Слева вверху нажимаем График > График (Line > Line).
Вуаля! У нас получилась диаграмма, на которой отлично видно количество проданных пар обуви и процент в соответствии с их размерами.
Прием 4. Объединение графиков с помощью команды «Форматирование области»
Если вам нужно объединить несколько графиков в Excel, чтобы сравнить данные в виде графического изображения, используйте команду «Форматирование области». Эта функция позволяет создать совмещенный график, объединив несколько графиков в одном.
Для того чтобы воспользоваться этим приемом, откройте нужный лист Excel и выделите данные для всех графиков, которые вы хотите объединить. Затем нажмите на клавишу Shift и выделите все эти графики. После этого перейдите во вкладку «Форматирование области» и выберите нужный тип совмещенного графика.
Воспользуйтесь опциями, которые дают вам возможность настроить внешний вид вашего объединенного графика: добавьте заголовки, измените цвета, шрифты, размер и т.д. Редактируйте ваш график, пока он не будет полностью соответствовать вашим потребностям.
Таким образом, с помощью команды «Форматирование области» можно быстро и легко объединить несколько графиков в одном, что позволяет лучше визуализировать данные и принимать более обоснованные решения.
Как создать диаграмму из нескольких листов в Excel
Предположим, у вас есть несколько листов с данными о доходах за разные годы, и вы хотите построить диаграмму на основе этих данных, чтобы визуализировать общую тенденцию.
1. Создайте диаграмму на основе вашего первого листа
Откройте свой первый рабочий лист Excel, выберите данные, которые вы хотите отобразить на диаграмме, перейдите к Вставлять вкладка > Графики группу и выберите тип диаграммы, которую вы хотите создать. В этом примере мы будем создавать диаграмму Stack Column:
2. Добавьте второй ряд данных с другого листа
Нажмите на только что созданную диаграмму, чтобы активировать Инструменты диаграммы вкладки на ленте Excel, перейдите к Дизайн вкладка (Дизайн диаграммы в Excel 365) и щелкните значок Выберите данные кнопка.
Или нажмите кнопку Фильтры диаграммы кнопка справа от графика, а затем щелкните значок Выберите данные… ссылка внизу.
в Выберите источник данных окно, нажмите на Добавлять кнопка.
Теперь мы собираемся добавить вторую серию данных на основе данных, расположенных на другом листе. Это ключевой момент, поэтому внимательно следуйте инструкциям.
Нажав на Добавлять кнопка открывает Редактировать серию диалоговое окно, в котором вы нажимаете кнопку Свернуть диалоговое окно рядом с полем Значения серии.
Редактировать серию диалоговое окно уменьшится до узкого окна выбора диапазона. Щелкните вкладку листа, содержащего другие данные, которые вы хотите включить в диаграмму Excel (вкладка Редактировать серию окно останется на экране при перемещении между листами).
На втором листе выберите столбец или строку данных, которые вы хотите добавить к диаграмме Excel, а затем щелкните значок Развернуть диалоговое окно значок, чтобы вернуться к полноразмерному Редактировать серию окно.
А теперь нажмите кнопку Свернуть диалоговое окно справа от поля Название серии и выберите ячейку, содержащую текст, который вы хотите использовать в качестве имени серии. Нажмите на Развернуть диалоговое окно вернуться к исходному Редактировать серию окно.
Убедитесь, что ссылки в Название серии а также Значение серии поля указаны правильно и щелкните ХОРОШО кнопка.
Как вы видите на снимке экрана выше, мы связали имя серии с ячейкой B1, которая является именем столбца. Вместо имени столбца вы можете ввести собственное имя серии в двойных кавычках, например, =”Второй ряд данных“.
Имена серий будут отображаться в легенде вашей диаграммы, поэтому вы можете потратить пару минут на то, чтобы дать несколько осмысленных и описательных имен для ваших рядов данных.
На этом этапе результат должен выглядеть примерно так:
3. Добавьте больше рядов данных (необязательно)
Если вы хотите отобразить на диаграмме данные из нескольких рабочих листов, повторите процесс, описанный в шаге 2, для каждой серии данных, которую вы хотите добавить. Когда закончите, нажмите кнопку ХОРОШО кнопка на Выберите источник данных диалоговое окно.
В этом примере я добавил 3-й ряд данных, вот как теперь выглядит моя диаграмма Excel:
4. Настройте и улучшите диаграмму (необязательно)
При создании диаграмм в Excel 2013 и 2016 обычно элементы диаграммы, такие как заголовок диаграммы и легенда, добавляются Excel автоматически. Для нашей диаграммы, построенной из нескольких рабочих листов, заголовок и легенда не были добавлены по умолчанию, но мы можем быстро исправить это.
Выберите свой график, нажмите кнопку Элементы диаграммы кнопку (зеленый крестик) в правом верхнем углу и выберите нужные параметры:
Дополнительные параметры настройки, такие как добавление меток данных или изменение способа отображения осей на диаграмме, см. в следующем руководстве: Настройка диаграмм Excel.
Составление диаграммы из сводной таблицы
Продемонстрированное выше решение работает только в том случае, если ваши записи отображаются в одном и том же порядке на всех листах, которые вы хотите отобразить на диаграмме. В противном случае ваш график не будет испорчен.
В этом примере порядок записей (Апельсины, яблоки, Лимоны, Виноград) одинакова во всех 3-х листах. Если вы создаете диаграмму из больших рабочих листов и не уверены в порядке расположения всех элементов, имеет смысл сначала создать сводную таблицу, а затем построить диаграмму на основе этой таблицы. Чтобы вывести совпадающие данные в сводную таблицу, вы можете использовать функцию ВПР или мастер объединения таблиц.
Например, если рабочие листы, обсуждаемые в этом примере, имеют другой порядок элементов, мы могли бы составить сводную таблицу, используя следующую формулу:
=ВПР(A3,’2014′!$A$2:$B$5, 2,ЛОЖЬ)
И получил следующий результат:
А затем, просто выберите сводную таблицу, перейдите к Вставлять вкладка > Графики группу и выберите нужный тип диаграммы.
Как выделить столбец на диаграмме?
По одному критерию
Давайте начнем с выделения столбцов.
Рассмотрим простую таблицу с одним показателем в виде продаж для каждого магазина:
Выделим полученный диапазон с данными $A$1:$F$2 и добавим на лист диаграмму, для этого в панели вкладок выбираем Вставка -> Диаграммы -> Гистограмма с группировкой.
Также давайте сразу ее немножко модифицируем и добавим на нее подпись данных (нажимаем правой кнопкой мыши по ряду и выбираем Добавить подпись данных) и уберем лишние детали (уберем сетку и вертикальную ось), чтобы они нам сейчас не мешали:
В результате получаем классическую диаграмму, вот теперь давайте подумаем как можно выделить столбец на диаграмме и в качестве критерия будем искать столбец с максимальным значением.
В данном примере это Магазин 4, т.е. 4 столбец. Какие у нас есть варианты?
Вариант изменять заливку подходящего столбца (вручную самостоятельно перекрашивать или делать с помощью макроса). Этот вариант не выглядит достаточно удобным так как это не даст нам возможности автоматического обновления при изменении данных.
Также есть вариант с добавлением дополнительного ряда на диаграмму, который мы с помощью формул можем прописать, и вот этот вариант нам уже вполне подойдет, так как даст нам возможность автоматизировать выделение столбца.
Поэтому давайте дополним нашу исходную таблицу вспомогательным рядом, в котором пропишем условие на выделение: если значение в исходном столбце удовлетворяет условию, то во вспомогательном ряду ставим значение из исходного ряда, в остальных же случаях ставим ноль. Другими словами, если значение максимально, то дублируем его во вспомогательный ряд, в остальных случаях пишем ноль.
Само условие прописываем с помощью функции ЕСЛИ (в английской версии IF), а поиск максимального значения функцией МАКС (английский вариант MAX):
Затем давайте расширим диапазон для построенной диаграммы и добавим в него новый вспомогательный ряд. Мы это можно сделать либо нажав правой кнопкой мыши по диаграмме, затем щелкнув Выбрать данные и задав новый диапазон ($A$1:$F$3), либо же активировав диаграмму и расширив диапазон с помощью появившейся рамки с диапазоном прямо на листе Excel.
Как мы видим новый ряд отобразился рядом с самым высоким столбцом, мы этого как раз и добивались когда прописывали вспомогательный ряд.
Теперь наша задача состоит в том, чтобы ряды оказались не рядом (как сейчас на картинке), а друг за другом. Для этого щелкаем правой кнопкой мыши по дополнительному ряду с данными и выбираем Формат ряда данных -> Параметры ряда и устанавливаем значение в параметре Перекрытие рядов равным 100%, в результате получаем:
Что произошло?
Наш дополнительный ряд оказался на переднем плане и тем самым как бы заслоняет собой основной ряд (оранжевый столбец находится к нам ближе чем синий), за счет чего и возникает представление, что мы поменяли раскраску столбца, хотя на самом деле просто добавили еще один ряд на график.
Теперь настроим внешний вид графика под свои предпочтения, я, к примеру, поменял стандартную раскраску столбцов и несколько увеличил их ширину с помощью настройки бокового зазора:
Диаграмма готова, и теперь при изменении максимального значения в таблице оно также поменяется и на графике в виде выделенного цветом столбца, вот так это выглядит при изменении значений в исходной таблице:
При этом если в таблице будет сразу несколько максимальных значений, то все они на графике также будут выделяться.
Перейдем к следующему примеру.
По нескольким критериям
Усложним задачу и теперь помимо выделения максимального значения на графике, также выделим и минимальное, таким образом, чтобы мы видели не только лучший результат, но и худший.
Алгоритм действий точно такой же, как и в предыдущем примере — добавляем в таблицу еще один дополнительный ряд, где прописываем условие на соответствие минимальному значению, поиск минимума можно сделать с помощью функции МИН (в английской версии MIN):
Расширяем диапазон (до $A$1:$F$4) для построения диаграммы и получаем еще один «отличительный» столбец на диаграмме, дополнительно также меняем заливку столбца по своим предпочтениям:
В принципе таких критериев выделения столбцов можно прописать сколь угодно много, однако если с этим переборщить, то обычная диаграмма может превратиться в слишком пестрый и непонятный график. Поэтому с критериями лучше не злоупотреблять и ограничиться 1-2 условиями.
Вспомогательная горизонтальная ось
Чтобы добавить вторую горизонтальную ось (категорий), в области построения уже должна отображаться вспомогательная.
- Щелкнуть по графику, который построен по вертикальной вспомогательной оси. Перейти на вкладку «Макет» и открыть группу «Оси».
- Раскрыть пункт «Вспомогательная горизонтальная». По умолчанию при добавлении второй оси значений Excel не показывает вторую ось категорий. Но пункт становится доступным для пользователя. Раскрыв его, выбираем подходящий вариант отображения.
На рисунке это будет выглядеть так:
Удалить дополнительную горизонталь значений можно теми же способами, что и вспомогательную вертикальную.
На любой вспомогательной оси можно показать лишь один ряд данных. Если необходимо отобразить несколько рядов, то для каждого процедуру добавления повторяют сначала.
Для дальнейшего применения построенную диаграмму с двумя осями можно сохранить в виде шаблона. Для этого нужно щелкнуть по области построения. На вкладке «Конструктор» нажать кнопку «Сохранить как шаблон».
Далее программа предложит место сохранения шаблона. Пользователь должен назначить имя и нажать ОК.
При работе в Excel табличных данных часто не достаточно для наглядности информации. Для повышения информативности ваших данных советуем использовать графики и диаграммы в Эксель. В этой статье рассмотрим пример того, как построить график в Excel по данным таблицы.