Эксель диаграмма: 10 Простых советов как сделать диаграммы Excel превосходными

Вафельная диаграмма в Excel

Вафельная диаграмма (Waffle Chart) — один из типов диаграмм, которые обычно используют для визуализации прогресса. Логика тут предельно простая и очевидная — чем больше залитых квадратиков, тем ближе к цели:

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

• прогресс по проекту
• различные KPI в любом бизнесе
• заполненяемость площадей или объемов (склады, строительство…)
• … и т.д.

Одна проблема: в Microsoft Excel нет такого типа диаграммы среди встроенных возможностей. Тем не менее, обойти это ограничение можно достаточно легко и даже не одним способом.

Способ 1. Вафельная диаграмма условным форматированием

Размечаем обрамлением табличку 10х10 квадратных ячеек и заполняем её (копированием, а не вручную, само-собой) снизу вверх возрастающими значениями от 1% до 100%

Затем выделяем весь размеченный диапазон (D3:M12) и выбираем Главная — Условное форматирование — Создать правило (Home — Conditional Formatting — Create Rule).

В открывшемся окне выбираем тип правила Форматировать только ячейки, которые содержат (Format only cells than contains), чуть ниже в выпадающем списке выбираем вариант Меньше или равно (Less or equal) и указываем рядом ссылку на ячейку с исходным значением ($B$2). Задаём цвет заливки, нажав на кнопку Формат (Format):

После нажатия на ОК получаем почти готовую диаграмму:

Для пущей красоты можно скрыть значения процентов в ячейках таблицы, задав для неё в окне Формат ячеек (Format cells) на вкладке Число (Number) пользовательский формат, состоящий из трёх подряд точек с запятой:

Затем на вкладке Вставка (Insert) жмём кнопку WordArt и, выбрав приглянувшийся дизайн надписи и вставив её поверх нашей таблицы, вводим в строку формул знак «равно» и делаем ссылку на ячейку с исходным значением:

Получаем надпись, текст которой автоматически обновляется из ячейки B2, отображая поверх нашей «вафли» текущее значение нашего параметра.

Способ 2. Вафельная диаграмма из линейчатой

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

Начинаем с создания процентного ряда от 0% до 100% с шагом 10% (диапазон A4:A13). Затем добавляем к нему столбец с вычислением разности между значениями ряда и нашим исходным значением, которое нужно визуализировать из ячейки B2:

Затем добавляем столбец с вложенными друг в друга функциями ЕСЛИ (IF), чтобы реализовать следующую логику:

• отрицательные значения заменяем на 0
• значения больше 10% на 10
• оставшиеся выводим как есть, но добавляем умножение на 100 (т.к. проценты в Excel представляют из себя числовые значения от 0 до 1, а нам нужно на выходе получить числа от 0 до 10)

Выделяем последний вычисленный столбец (диапазон C4:C13) и строим по нему линейчатую диаграмму на вкладке Вставка (Insert):

… и получаем вот такую картину:

Осталось сделать эту диаграмму более похожей на «вафлю». Для этого:

1. Щёлкаем правой кнопкой мыши по синим столбцам, выбираем опцию Формат ряда данных (Format data series) и убираем Боковой зазор (Gap width) до нуля. Столбики становятся максимально широкими и сливаются в единое целое:

2. Там же на вкладке форматирования задаём для синих столбиков полупрозрачную заливку:

3. Щелкаем по горизонтальной оси правой кнопкой мыши, выбираем команду Формат оси (Format axis) и задаем пределы от 0 до 10 и шаг основных делений равный 1:

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

   
   Дополнительно и при желании, можно настроить ещё цвет линий сетки, сделав их более яркими.

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

Вот и всё — вафля готова :)

Нюансы

Пара нюансов и лайфхаков вдогон:

• Если после создания диаграммы захочется скрыть вспомогательную таблицу A4:C13, оставив только ячейку B2 с исходным значением, то лучше щёлкнуть по диаграмме правой, затем команда Выбрать данные — Скрытые и пустые ячейки (Select data source — Hidden and Empty cells) и включить флажок Показывать данные в скрытых строках и столбцах (Show data from hidden rows and columns). Иначе после скрытия исходных данных пропадет и диаграмма.
• Если такой вид диаграммы вам придётся делать ещё неоднократно, то имеет смысл сохранить созданную диаграмму как шаблон, щёлкнув по ней правой кнопкой мыши и выбрав команду Сохранить как шаблон (Save as Template):

  
  После этого вафельную диаграмму (по подготовленной таблице!) можно будет создать через выбор в стандартном окне типов диаграмм Excel, найдя её в разделе Шаблоны (Templates):

Вот и всё. Теперь вы сможете делать вафли не только на кухне, но и в Excel :)

Ссылки по теме

Визуализация диаграммы с помощью фигур и рисунков в Excel — TutorExcel.Ru

Рассмотрим интересный способ как разнообразить стандартные графики в виде гистограммы или линейчатой диаграммы с помощью использования фигур и рисунков в Excel.

Приветствую всех, дорогие читатели блога TutorExcel.Ru!

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

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

К примеру, сравните 2 варианта исполнения такого графика и скажите какой вариант вам больше нравится?

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

Начнем.

Модификация графика с помощью добавления фигур

Пример 1. Произвольная фигура

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

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

Далее на основе этой таблицы построим гистограмму с накоплением, выделяем диапазон с данными A4:F6 в панели вкладок идем во Вставка -> Диаграммы -> Гистограмма с накоплением:

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

Теперь уберем с диаграммы ненужные на текущий момент детали (линии сетки) и установим небольшой боковой зазор, чтобы столбцы были визуально шире (щелкаем правой кнопкой мыши по любому ряду с данными, выбираем Формат ряда данных -> Параметры ряда и меняем Боковой зазор), а также покрасим оба ряда в более нейтральные цвета:

Теперь же и начинается самое интересное.

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

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

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

Для вставки фигуры на график нам достаточно ее скопировать (Ctrl + C) и, выделив верхний ряд на диаграмме, вставить (Ctrl + V):

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

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

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

Нам осталось немного поработать над внешним видом. Давайте добавим подписи данных для столбцов.

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

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

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

При этом мы можем не ограничиваться только таким выбранным типом фигуры, например, вот как будет выглядеть наша диаграмма со скругленными краями или стрелкой на вершине:

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

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

С этим примером закончили, давайте перейдем к следующему.

Пример 2. Картинки и рисунки

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

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

Так же как и в примере выше добавим несколько вспомогательных рядов, к 2 стандартным рядам с частями для построения столбцов добавим ряд для подписи данных:

Начало построения точно такое же как и в примере выше, поэтому выделяем диапазон с данными A8:F11 и строим гистограмму с накоплением:

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

Щелкаем по нему правой кнопкой мыши, выбираем Изменить тип диаграммы для ряда и устанавливаем для него тип График:

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

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

Мы подошли к самому важному — добавление рисунка на график.

Наши действия и тут точно такие же, копируем нужный флаг (Ctrl + C), выделяем конкретный столбец (т.е. не сразу весь ряд, а нужный столбец из ряда) и вставляем (Ctrl + V) рисунок с флагом, а затем повторяем эту процедуру для каждого столбца:

Все готово.

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

При использовании рисунков обратите внимание на несколько важных моментов:

• При вставке рисунка в столбец он как бы заполняет все пространство столбца, поэтому пропорции картинки на графике могут поменяться;
  Поэтому чтобы картинка выглядела гармонично можно «поиграться» со значением в дополнительном ряду (которое как раз отвечает за высоту столбца) и найти подходящий вариант.
• Некоторые рисунки не имеют формальных границ (например, белая полоса на флаге России сливается с белым фоном на графике).
  Если у вас именно такой случай, то можно задать линию границы для рисунка, чтобы она отделяла сам рисунок от фона графика.

На этом на сегодня все!

Скачать файл с примером.

Спасибо за внимание!
Если у вас есть вопросы или мысли по теме статьи — обязательно пишите в комментариях.

Удачи вам и до скорых встреч на страницах блога TutorExcel.Ru!

Поделиться с друзьями:

Поиск по сайту:

как решать нестандартные задачи на примере

Диаграмму «Водопад» используют, когда нужно суммировать данные, удовлетворяющие сразу нескольким условиям. Я впервые столкнулся с ней, когда мне нужно было выполнить весьма специфичную задачу: подсчитать еженедельную выручку от продаж зерненого творога в течение года. Проблема заключалась в том, что среди выгруженных данных оказались не только обычные продажи, но и промоакции, которые не нужно было учитывать. Рабочий файл состоял из нескольких тысяч строк, поэтому отсортировать вручную было невозможно. Именно тогда диаграмма Waterfall показала мне, как можно сэкономить много времени и сил.

Тимофей Миненков — студент МГУ им. М.В. Ломоносова и выпускник онлайн-курса Changellenge >> ToolKit 2018.

Я использовал функцию СУММЕСЛИМН, чтобы задать условия для каждой ячейки и посчитать сумму. Например, для первой: найти суммарную выручку по всем видам зерненого творога за последнюю неделю 2015 года без учета промоакций. Так выглядел синтаксис этой функции:

= СУММЕСЛИМН (Диапазон для суммирования; Диапазон 1 для критерия; Критерий для диапазона 1; Диапазон 2 для критерия; Критерий для диапазона 2; …)

На первый взгляд, не очень дружелюбно. Однако суть формулы проста: диапазон для суммирования в нашем случае — выручка; первым диапазоном для критерия будет дата — выделяем диапазон $H$3:$H$3370; далее идет сам критерий, т. е. дата K3; теперь выделяем диапазон для второго критерия (нам нужно исключить акции) — в нашем случае это будет диапазон $C$3:$C$3370; и сам критерий Продажа.

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

Диаграмма Waterfall с помощью надстройки Think-Cell

Think-Cell — это надстройка для PowerPoint в формате Excel. С помощью Think-Cell можно быстро делать диаграммы разного уровня сложности. Они получаются более профессиональными, по сравнению со стандартной визуализацией в Excel. Ниже несколько функций надстройки Think-Cell:

1. Добавить на диаграмму стрелки, которые отражают изменение величин в %.
2. Выбрать, в какую сторону повернут график.
3. Обрезать базовые значения на диаграмме, оставив только верхние части, чтобы отразить маленькие изменения.

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

Например, на gif-анимации ниже я показываю, как за одну минуту сделать каскадную диаграмму. Она визуализирует денежный эффект от предложенных инициатив для страховой компании в чемпионате Oliver Wyman Impact. Дополнительно прокомментировать тут нужно только три момента:

• английская буква «e» суммирует все значения, которые написаны до нее;
• чтобы выделить необходимые столбцы для стрелки, которая показывает изменение в процентах, используйте клавишу Ctrl ;
• иногда клавиша вызова окна Excel не работает, в такой ситуации лучше сохранить файл, закрыть и снова открыть его.

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

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

Прокачаться в Excel >>

Теги

Как создать шаблон временной шкалы (диаграммы Ганта) в Excel

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

Создание временной шкалы в Excel 

Шаг 1. Создание таблицы

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

Шаг 2. Добавление дат начала

Щелкните пустую линейчатую диаграмму правой кнопкой мыши и выберите пункт «Выбор данных» в контекстном меню. В окне «Выбор источника данных» нажмите кнопку «Добавить» в разделе «Элементы легенды (ряды)». Появится диалоговое окно «Изменение ряда». Щелкните пустое поле «Имя ряда», а затем выберите ячейку «Дата начала» в таблице, созданной на этапе 1. Наконец, щелкните значок таблицы справа от поля «Значения», чтобы открыть диалоговое окно «Изменение ряда». Щелкните первую дату начала в таблице и, не отпуская кнопку мыши, перетащите указатель до последней даты начала, чтобы охватить весь диапазон дат начала задач. Щелкните значок таблицы рядом с полем «Значения». Появится предыдущее окно. Нажмите кнопку «ОК».

Шаг 3. Добавление сведений о продолжительности

Выполните те же действия в столбце «Продолжительность». Нажмите кнопку «Добавить» в разделе «Элементы легенды (ряды)». Выберите пустое поле «Имя ряда» и щелкните в таблице первую ячейку «Продолжительность». Щелкните значок таблицы справа от поля «Значения», чтобы открыть диалоговое окно «Изменение ряда». В таблице щелкните первую ячейку с данными о продолжительности и, не отпуская кнопку мыши, перетащите указатель до последней такой ячейки, чтобы охватить весь диапазон значений продолжительности задач. Щелкните значок таблицы рядом с полем «Значения» и нажмите кнопку ОК, чтобы добавить сведения о продолжительности на временную шкалу в приложении Excel.

Шаг 4. Добавление имен задач

Щелкните правой кнопкой мыши любую линейку диаграммы и выберите пункт «Выбрать данные» в контекстном меню. Щелкните значок таблицы справа от пункта «Подписи горизонтальной оси (категории)», а затем с помощью мыши выделите диапазон имен задач в таблице. Выбранный диапазон должен включать только сами имена задач, а не название столбца «Задача». Нажмите кнопку ОК в двух последующих окнах.

Шаг 5. Форматирование диаграммы Ганта

Итак, вы создали линейчатую диаграмму с накоплением, на которой показаны даты начала и продолжительность задач, расположенных в обратном порядке. Щелкните правой кнопкой мыши список задач и выберите пункт «Формат оси». Установите флажок «Обратный порядок категорий».

Чтобы скрыть синие участки всех линеек, щелкните любой из таких участков правой кнопкой мыши и выберите пункт «Формат ряда данных». В разделе «Заливка» выберите вариант «Нет заливки». В разделе «Цвет границы» выберите «Нет линий», а в разделе «Тень» снимите флажок «Тень». Затем удалите ненужную легенду линейчатой диаграммы, чтобы высвободить место.

Чтобы убрать пустое место в начале диаграммы Ганта, щелкните правой кнопкой мыши на первой ячейке «Дата начала» и выберите пункт «Формат ячеек» в контекстном меню. Запишите число, указанное в категории «Общий». Нажмите кнопку «Отмена».

Вернитесь к диаграмме Ганта, щелкните правой кнопкой мыши любую дату над линейками и выберите пункт «Формат оси» в контекстном меню. В поле «Минимальное значение» укажите записанное ранее число и закройте диалоговое окно.

Чтобы убрать пустое место между линейками диаграммы, щелкните правой кнопкой мыши в верхней части красной линейки и выберите пункт «Формат ряда данных» в контекстном меню. Установите ползунок «Перекрытие рядов» в значение 100%, а ползунок «Боковой зазор» — в значение 10%.

Поздравляем, вы успешно создали временную шкалу проекта в приложении Excel.

Загрузка бесплатного шаблона временной шкалы для Excel

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

Удобный способ создания временной шкалы проекта

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

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

Попробуйте бесплатную версию Wrike и ознакомьтесь с ее возможностями без загрузки и установки приложения.

Excel. Диаграмма, изменяющаяся при добавлении данных

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

В качестве пример возьмем курс доллара (рис. 1). Для начала создадим обычную диаграмму (тип «График с маркерами»).

Рис. 1. График с маркерами

Скачать заметку в формате Word, примеры в формате Excel

Далее создадим два именованных динамических диапазона: один для меток категорий (Даты), второй – для точек данных (Курс $). Для создания именованного диапазона пройдите по меню Формулы → Диспетчер имен (рис. 2).

Рис. 2. Диспетчер имен

В открывшемся окне «Диспетчер имен» нажмите кнопку создать, и в окне «Создание имени» введите имя диапазона – «Даты» и формулу для ссылки на диапазон: =СМЕЩ(Лист1!$A$1;1;0;СЧЁТЗ(Лист1!$A$1:$A$100)-1;1)

Рис. 3. Присвоение имени динамическому диапазону

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

Обратите внимание, что сразу же за аргументом функции СЧЁТЗ стоит «–1». Благодаря этому заголовок ряда не будет включен в именованный диапазон. Заметьте также, что в качестве аргумента функции СЧЁТЗ указан не весь столбец А, а лишь первые 100 ячеек. Если вы используете большой массив данных, укажите соответствующее число, например, 1000 или 10 000. В ранних версиях Excel такое ограничение весьма желательно, дабы не перегружать вычисления. Указывая колонку полностью, вы заставляете Excel просматривать тысячи ненужных ячеек. Некоторые функции Excel достаточно умны, чтобы определить, какие ячейки содержат данные, некоторые сделать этого не могут. В новых версиях Excel не обязательно строго ограничивать диапазон, так как обработка больших диапазонов в них улучшена.

Затем создайте второй именованный диапазон для данных столбца В (рис. 4)

Рис. 4. Динамический диапазон «Курс»

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

Рис. 5. Выбрать данные

В открывшемся окне «Выбор источника данных» выделяем ряд и жмем «Изменить» (рис. 6).

Рис. 6. Изменить ряд

В открывшемся окне «Изменение ряда» заменяем ссылки на ячейки на имя ряда «Курс» (рис. 7). Обратите внимание, что имя листа Excel следует оставить в неизменном виде «=Лист1!»

Рис. 7. Замена ссылок на имя диапазона

Аналогично заменяем подписи горизонтальной оси (категории): жмем другую кнопку «Изменить» в правой части окна «Выбор источника данных» (см. рис. 6) и вводим имя «Даты» вместо ссылок на ячейки (рис. 8).

Рис. 8. Замена подписей оси (категорий)

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

Рис. 9. Новые данные, добавленные в таблицу (выделены желтым) автоматически отражаются на диаграмме

В своей работе менеджера мне приходится контролировать довольно много параметров, так что подобные хитрости я использую давно, и они значительно облегчают мне работу. А вот недавно в книге Д.Холи, Р. Холи «Excel 2007. Трюки» я прочитал о еще одной возможности, основанной на том же свойстве.

Добавление от 19 июня 2018 г. Эту же проблему гораздо проще решить, если встать на любую ячейку диапазона, и нажать Ctrl+T (англ.). Диапазон превратится в Таблицу. Создайте на ее основе диаграмму. При добавлении строк в Таблицу, диаграмма будет отражать их автоматически.

Построение диаграммы для фиксированного числа последних данных

Еще один тип именованных диапазонов, который можно использовать с диаграммами, – это диапазоны, выбирающие только последние N значений (можно указать любое число).

См. пример на Лист2 в Excel-файле. Для данных в столбце А создайте динамический именованный диапазон с именем Даты30 (последние 30 дней), который ссылается на следующие данные: =СМЕЩ($A$1;СЧЁТЗ($A$1:$A$100)-30;0;30;1). Для данных в столбце В создайте динамический именованный диапазон с именем Курс30, который ссылается на следующие данные: =СМЕЩ($B$1;СЧЁТЗ($B$1:$B$100)-30;0;30;1). Замените в диаграмме ссылки на диапазоны данных именами динамических диапазонов. Получится диаграмма, отражающая последние 30 значений (рис. 10).

Рис. 10. На диаграмме отражаются 30 последних значений

При добавлении данных в таблицу область отражения на диаграмме сместится (рис 11).

Рис. 11. При добавлении данных диаграмма по-прежнему отражает 30 последних значений

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

Построение диаграмм в Excel. — it-black.ru

C помощью этой статьи Вы научитесь строить различные диаграммы в программе Microsoft Excel 2013. Но сначала давайте рассмотрим понятие “диаграмма”:

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

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

Диаграммы в Excel способны графически представлять данные, что позволяет легче воспринимать большие объемы информации и оценивать её. Excel предлагает самые различные типы диаграмм.

Какие бывают типы диаграмм?

1. КРУГОВЫЕ ДИАГРАММЫ

Круговые диаграммы подходят для демонстрации пропорций, т.е. части чего-то относительно целого. Каждое значение представлено в виде доли (сектора) от суммы всех значений (круга). Круговая диаграмма строится для одного ряда данных. Такой подход очень полезен, когда нужно сравнить данные друг с другом.

2. ГИСТОГРАММА

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

3. ГРАФИК

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

4. ДИАГРАММЫ С ОБЛАСТЯМИ

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

5. ПОВЕРХНОСТНЫЕ ДИАГРАММЫ

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

6. ЛИНЕЙЧАТАЯ ДИАГРАММА

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

ЭЛЕМЕНТЫ ДИАГРАММ

1. Заголовок диаграммы.

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

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

4. Легенда указывает принадлежность каждого ряда к кому-либо или чему-либо. В текущем примере легенда содержит 3 цвета с соответствующими продавцами. Видя легенду достаточно легко определить к какому продавцу относится каждый из столбцов.

5. Горизонтальная ось (также известная как ось X) является горизонтальной частью диаграммы. Горизонтальная ось представляет категории.

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

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

Можно также нажать клавиши ALT+F1, и Excel автоматически создаст для вас простую гистограмму. На этом этапе вам будут доступны различные возможности для редактирования диаграммы в соответствии с потребностями.

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

Как показано на рисунке в группе “Диаграммы” щелкаем мышью на стрелке в правой части кнопки “Гистограмма” и в открывшемся при этом подменю выбираем один из вариантов раздела “Объемная гистограмма”.

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

P.S. Если Вы не уверены, какой тип диаграммы использовать, команда “Рекомендуемые диаграммы” предложит различные варианты на базе исходных данных.

Связывание и внедрение таблиц и диаграмм MS Excel в документы MS Word — Интеграция Excel с другими приложениями — Эффективная работа в Excel — Статьи об Excel

2 способ. Создание нового объекта

Для этого на вкладке Вставка в группе Текст нажимаем кнопку Объект,

 для версий старше Word 2007 меню Вставка — Объект

 и в раскрывшемся диалоговом окне на вкладке Создание ищем в списке строчку Лист Microsoft Excel и нажимаем ОК 

После этого на странице документа мы видим фрагмент листа Excel, а также ленту с вкладками (или меню) Excel.

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

В версиях до Word 2003 включительно можно использовать кнопку Добавить таблицу Excel на Стандартной панели инструментов.

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

Если мы хотим создать внедрённую диаграмму, то в диалоговом окне Вставка объекта на вкладке Создание выбираем строчку Диаграмма Microsoft Excel. В этом случае будет создан не один лист Excel, а два: на первом будет пример диаграммы, а на втором — исходные данные для неё, которые следует заменить своими исходными данными.

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

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

3 способ. Создание объекта из файла

Для этого на вкладке Вставка в группе Текст нажимаем кнопку Объект и в раскрывшемся диалоговом окне переходим на вкладку Создание из файла. С помощью кнопки Обзор находим в Проводнике нужный файл Excel и нажимаем ОК.

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

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

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

Было

Стало

Если же Вы ту же самую операцию делаете, не входя в режим редактирования, то Вы просто растягиваете/сжимаете рисунок

Было

Стало

Создать внедрённый объект из файла можно также, перетащив мышкой значок файла Excel на страницу документа Word

Совет. Если Вы планируете внедрять диаграмму в документ, то лучше расположить её на отдельном листе.

Примечание. Иногда после редактирования таблицы и возвращения в Word, мы видим, что таблица отображается не полностью, она как бы «уехала» вверх или влево.

В этом случае следует вернуться в режим редактирования и обратить внимание, какая ячейка осталась активной. 

Рекомендуется оставлять активной ячейку в левом верхнем углу Вашей таблички.

СВЯЗЫВАНИЕ

1 способ. Через буфер обмена с помощью Специальной вставки

Выделяем на листе Excel диапазон ячеек или диаграмму, выполняем команду Копировать, переходим в документ Word и даём команду Специальная вставка. Размещение этой команды зависит от версии Word.

В версиях до Word 2003 включительно она находится в меню Правка. Начиная с Word 2007 эту команду можно найти в раскрывающемся списке кнопки Вставить на вкладке Главная. Кроме того, начиная с Word 2010 в контекстном меню присутствует команда Параметры вставки, с помощью которой можно выбрать варианты связывания.

2 способ. Создание объекта из файла.

Для установления связи с файлом источником при создании объекта из файла достаточно в диалоговом окне Вставка объекта поставить флажок Связь с файлом

При этом в документ вставляется уже не сама таблица, а ссылка на неё. 

При последующем открытии такого файла появляется окно

Если вы не уверены в надёжности источника связанных файлов, нажмите Нет.

Если выбран вариант Да, то данные в таблице документа обновятся в соответствии с последними изменениями в книге Excel. 

Если во время изменения данных в электронной таблице документ Word открыт, то связанная таблица обновляется автоматически (по умолчанию).

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

Как видим, в этом диалоговом окне есть возможность Сменить источник, а также Разорвать связь, если таковая больше не требуется. Кроме того, здесь же можно выбрать способ обновления связи: автоматически или по запросу.

В версии до Word 2007 это диалоговое окно также открывается при выполнении команды меню Правка — Связи. 

В Word 2007 его можно открыть, нажав кнопку Office и выбрав команду Подготовить — Изменить ссылки на файлы. 

10 советов по дизайну для создания красивых диаграмм и графиков Excel в 2021 году

Очень заманчиво позволить ужасным графикам Excel проникнуть в ваш маркетинг. Вашему начальнику наплевать на мелочи вроде того, как выглядят графики, верно? И все, что Excel предлагает по умолчанию, вероятно, нормально … не так ли?

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

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

Примечание. Я использую Excel для Mac 2011.Если вы используете другую версию или операционную систему, реализация следующих советов может выглядеть иначе.

Советы по дизайну Excel для украшения некрасивых диаграмм и графиков в Microsoft Excel

1) Выберите правильный график.

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

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

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

Кредит изображения: Викимедиа

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

Чтобы превратить данные в одну из этих диаграмм в Excel, выделите данные, которые вы хотите преобразовать в диаграмму, затем выберите «Диаграммы» в верхней панели навигации (или выберите «Вставить»> «Диаграммы», если у вас другая версия). . Затем выберите диаграмму, наиболее подходящую для ваших данных.

2) Сортировка данных гистограммы, чтобы они были интуитивно понятными.

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

Изображение предоставлено: Search Engine Land

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

Изображение предоставлено: Search Engine Land

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

Чтобы упорядочить графики в Excel, вам необходимо отсортировать данные от наибольшего к наименьшему. Нажмите «Данные», выберите «Сортировка» и выберите способ сортировки всего.

3) Сократите метки оси Y.

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

Чтобы сократить их, щелкните правой кнопкой мыши одну из меток на оси Y и выберите «Ось формата» в появившемся меню.Выберите «Номер» слева, а затем «Пользовательский» в списке категорий. (Снимите флажок «Связано с источником», если он выбран, иначе вы не сможете щелкнуть «Пользовательский».)

Введите код произвольного формата \ $ 0 ,, \ m (как показано ниже) и нажмите «ОК», чтобы закрыть.

Полученный график будет выглядеть намного чище:

4) Убрать фоновые линии.

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

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

Чтобы удалить фоновые линии, выберите «Макет диаграммы»> «Линии сетки», затем выберите «Без линий сетки» в параметрах «Горизонтальный» и «Вертикальный».

5) Удалите поля линии по умолчанию.

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

Изображение предоставлено: Эндрю Робертс

Чтобы удалить эти поля, дважды щелкните по оси даты, и откроется окно «Ось формата». В разделе «Параметры оси» измените «Ось положения» на «По отметкам».

Кредит изображения: Эндрю Робертс

6) Убрать лишнюю укладку.

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

Чтобы исправить стиль в Excel, щелкните правой кнопкой мыши график и выберите «Форматировать область диаграммы». Удалите все лишнее и ненужное влияние на ваши данные:

7) Держитесь подальше от 3D-эффектов.

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

Чтобы данные выглядели дополнительно , люди часто делают гистограммы, линейные и круговые диаграммы в трехмерном виде, но на самом деле это только затрудняет чтение данных.Из-за того, как данные наклонены, это дает читателю искаженное представление о том, что на самом деле означают данные. Поскольку вы используете данные, чтобы рассказать общую историю, вы не хотите ослаблять свои аргументы из-за плохого дизайна. Посмотрите, как по-разному выглядит круговая диаграмма в 2D и в 3D?

Кредит изображения: visual.ly

И если вы действительно посмотрите на область, занимаемую каждым разделом на экране, вы поймете, почему легко неверно интерпретировать трехмерные графики:

Изображение предоставлено: visual.ly

Чтобы удалить 3D-стиль с ваших графиков, дважды щелкните столбцы, линии или секторы круговой диаграммы, которые вы хотите изменить, выберите «3D-формат» и установите для «Сверху» и «Снизу» значение «Нет».

8) Удалите легенду, если она не нужна.

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

Чтобы удалить легенду в Excel, вы можете просто дважды щелкнуть текстовое поле легенды, а затем нажать «Удалить» на клавиатуре:

9) Используйте фирменные цвета.

Цвета, предустановленные в Excel, довольно тусклые. Один из быстрых способов приукрасить их — сделать их цветами вашего бренда. Это небольшая деталь, которая сделает ваши диаграммы гладкими и чистыми.

Чтобы всегда использовать правильные фирменные цвета, вам необходимо получить цветовой код HEX цветов вашего бренда.Затем вставьте этот код в этот преобразователь цвета HEX -> RBG. В Excel дважды щелкните те части графика, цвет которых вы хотите изменить. В разделе «Заливка» выберите «Цвет»> «Другие цвета».

Во всплывающем окне выберите второй значок слева с помощью ползунков. Видите это маленькое раскрывающееся меню? Выберите «Ползунки RGB», затем введите числа RGB, которые вы нашли в начале. Вуаля! Идеально фирменные цвета и шикарная графика.

10) Добавьте затененную область на линейный график.

Вы когда-нибудь видели линейный график с заштрихованной областью под линией? Они могут выделить вашу линейную диаграмму.

Кредит изображения: Эндрю Робертс

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

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

Кредит изображения: Эндрю Робертс

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

В появившемся меню выберите первый тип в категории «Площадь».

Кредит изображения: Эндрю Робертс

Ваш новый линейный график будет выглядеть примерно так:

Кредит изображения: Эндрю Робертс

Затем дважды щелкните затененную область на диаграмме (в данном случае красную область), и появится меню «Форматировать ряд данных». Щелкните «Заливка» слева и выберите «Сплошная заливка». В разделе «Цвет заливки» выберите тот же цвет, что и линия на диаграмме. Вы можете изменить прозрачность на все, что захотите — прозрачность 66% выглядит хорошо.

Затем в разделе «Цвет границы» выберите «Сплошная заливка». В разделе «Цвет заливки» выберите тот же цвет, что и линия на диаграмме. Измените прозрачность на ту же прозрачность, что и прозрачность цвета границы.

Хотите больше советов по Excel? Ознакомьтесь с этими ресурсами для изучения Excel в Интернете.

Примечание редактора: этот пост был первоначально опубликован в 2014 году и с тех пор обновлен для обеспечения точности и полноты.

Как создавать интерактивные диаграммы Excel

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

Показать серию диаграмм с флажками

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

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

Создать повторяющийся диапазон данных

Вместо использования исходного диапазона данных для создания диаграммы вы
создать повторяющийся диапазон, связанный с исходными данными.В дубликате
диапазон, формулы будут отображать данные ряда, только если флажок
проверено. В противном случае будет показано # N / A, и серия будет опущена.
из диаграммы, поскольку значения # N / A не отображаются в диаграмме Excel.

В этом примере исходные данные находятся в ячейках B2: H5, а дубликаты
диапазон, показанный ниже, находится в ячейках B8: h21

Для создания заголовков столбцов «Месяц»:

1. В ячейке C8 введите следующую формулу: = C2
2. Скопируйте формулу в ячейку H8

Для создания заголовков строк «Регион»:

1. В ячейке B9 введите следующую формулу: = C3
2. Скопируйте формулу в ячейку B11

Для создания данных серии:

1. В ячейке C9 введите следующую формулу: = IF ($ A9 = TRUE, C3, NA ())
2. Скопируйте формулу в ячейку H9, а затем скопируйте в строку
   11.

Добавьте флажки

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

Чтобы добавить флажок:

1. На ленте щелкните вкладку Разработчик. (Если вкладка не отображается,
   следуйте инструкциям здесь, чтобы добавить
   вкладка Разработчик)
2. Щелкните команду «Вставить» и в разделе «Элементы управления формы» щелкните
   флажок

3. На листе щелкните ячейку A3, чтобы добавить флажок.
4. Удалите текст из заголовка флажка и установите флажок
   коробка более узкая, чтобы поместиться в столбце A.

5. Скопируйте флажок и вставьте копии в ячейки A4 и A5.

Свяжите флажки с ячейками рабочего листа

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

1. Щелкните правой кнопкой мыши флажок в ячейке A3 и выберите Управление форматом
2. Щелкните поле Ссылка на ячейку, а затем на листе щелкните
   ячейка A9, чтобы связать флажок с этой ячейкой.

3. Щелкните OK, чтобы закрыть окно управления форматом.
4. Свяжите оставшиеся флажки с соответствующими ячейками в
   повторяющийся диапазон данных.
5. Щелкните каждый из флажков, чтобы добавить флажок, и чтобы добавить
   ИСТИНА для связанной ячейки.

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

Чтобы создать линейную диаграмму из повторяющегося диапазона данных:

1. Выберите данные и заголовки в повторяющемся диапазоне данных — ячейки
   B8: h21
2. На ленте щелкните вкладку Вставка
3. Щелкните команду «Вставить линейную диаграмму», затем щелкните «Двухмерная линия».

4. При необходимости переместите диаграмму и измените ее размер по размеру рабочего листа.
5. СОВЕТ. Расположите диаграмму над диапазоном повторяющихся данных, чтобы скрыть
   Это.

Форматирование линейной диаграммы

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

Чтобы удалить легенду диаграммы:

1. Щелкните легенду диаграммы, чтобы выбрать ее.
2. На клавиатуре нажмите клавишу Delete

Чтобы связать заголовок диаграммы с ячейкой:

1. Щелкните заголовок диаграммы, чтобы выбрать его.
2. Щелкните строку формул и введите знак равенства (=)
3. Щелкните ячейку B1, которая содержит заголовок рабочего листа
4. Нажмите Enter, чтобы заполнить формулу

Для добавления меток данных:

1. Установите флажки, чтобы показать все серии.
2. Щелкните первую серию, чтобы выбрать ее.
3. Щелкните последнюю точку в выбранной серии, чтобы выбрать данные.
   точка.
4. Щелкните правой кнопкой мыши выбранную точку данных и выберите Добавить метку данных,
   затем нажмите Добавить метку данных

5. Дважды щелкните метку данных — при первом щелчке выбираются все
   метки данных для серии, а второй щелчок выбирает только
   одна метка данных.
6. Щелкните правой кнопкой мыши выбранную метку данных и выберите «Форматировать данные».
   Этикетка
7. На панели «Форматирование метки данных» в разделе «Параметры метки» добавьте флажок
   отметьте название серии и снимите отметку с поля «Значения».

8. Повторите шаги 2-7 для оставшейся серии данных.
9. СОВЕТ: Если метки данных расположены слишком близко к линиям, щелкните
   График области диаграммы и сузьте ее.

Проверьте интерактивную линейную диаграмму

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

Значение в связанной ячейке изменится на ЛОЖЬ, а данные для
эта серия исчезнет с графика.

Показать серию диаграмм с отметками X

Вместо использования флажков вы можете отметить серию знаком «X»,
чтобы он появился на графике.Уберите «X», чтобы удалить
серия из диаграммы.

Следуйте инструкциям по установке для интерактивного примера флажка.
выше, но не устанавливайте флажки в ячейках A3: A5.

Затем добавьте формулу в столбец A повторяющегося диапазона данных, чтобы увидеть
если есть отметка в основных данных. Если есть отметка, то результат
ИСТИНА, а в противном случае — ЛОЖЬ.

1. В ячейке A9 введите следующую формулу: = A3 <> «»
2. Скопируйте формулу в ячейку A11.

Протестируйте интерактивную онлайн-диаграмму

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

Флажки недоступны в интерактивном интерактивном представлении, поэтому
в этом примере в столбце A используются отметки «X».

• Введите X в столбце A, чтобы отобразить регион.
• Очистите ячейку в столбце A, чтобы скрыть область.

Загрузить рабочую тетрадь

Чтобы увидеть обе версии настройки диаграммы, вы можете скачать образец
файл.Файл заархивирован и в формате xlsx без
макросы. Образец интерактивного
Файл диаграммы

Дополнительные ресурсы для диаграмм

Интерактивная диаграмма — Всего

Круговая диаграмма

Диаграмма Ганта в Excel

Диаграммы, кластерный стек
Утилита

Диаграммы, линия-столбец 2 оси

Диаграммы, Панель

Карты, Водопад

Код VBA, скопировать в книгу

Лучшее руководство по Excel — Диаграммы 101

В этом уроке вы можете узнать основную информацию о диаграммах в Excel.

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

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

Важно правильно выбрать тип диаграммы.

• Столбчатая диаграмма — самый популярный тип диаграмм.Чем больше значение, тем выше столбец.

• Линия — используйте этот график, чтобы показать, как данные меняются со временем. Используйте его для отображения 2-7 серий данных. Больше выглядит не очень хорошо.

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

• Панель — хороший выбор для отображения более одной категории данных или одной категории с подкатегориями.

• Область — аналогична линейной диаграмме

• Точечная диаграмма — для отображения изменения значения данных, когда вы теперь значения в виде точек.

• Акции — только для инвесторов

• Поверхность — при отображении двух наборов данных

• Пончик — аналогично круговой диаграмме, но может включать несколько рядов данных

53

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

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

Это основные диаграммы, которые вы можете вставить в Excel. Типов графиков гораздо больше. На этом веб-сайте вы можете найти много уроков о диаграммах в Excel. Не только основы.

 Дополнительная литература:
Диаграмма разброса
Диаграмма Doghnout 

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

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

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

Вот те же данные для той же диаграммы, но какой тип диаграммы Excel вы бы выбрали или какую диаграмму вы предпочитаете?

Мне нравится тот, у которого есть флажки для моей панели управления Excel. А ты?

Хорошо, а как вы можете сделать это в следующей информационной панели компании Excel? Вот процесс. Кроме того, в конце этого поста есть видеоурок по диаграммам Excel.

Разбивка

1) Создайте диаграмму Excel с точечной диаграммой (XY).

2) Создайте свои собственные маркеры

3) Замените точки данных своими пользовательскими маркерами

4) Удалить легенду диаграммы

5) Снимите горизонтальную ось

Пошаговое руководство

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

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

2) Затем вы должны создать свои собственные маркеры.Как правило, вы можете создавать свои собственные маркеры для диаграммы Excel двумя способами.

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

или

b) В меню «Вставка» в группе «Иллюстрации» выполните одно из следующих действий:

i) Вставить изображение из файла:

ii) Вставить картинки: iii) Вставить фигуры:

3) Теперь, когда у вас есть собственные маркеры Excel, вы можете вставить их в диаграмму Excel.Чтобы добавить свои собственные маркеры на диаграмму, просто выполните следующие шаги простого копирования и вставки:

a) Копия на первом рисунке, клипе или фигуре

б) Выберите диаграмму

c) Выберите точку данных, щелкнув точку данных «дважды» (2 раза), чтобы убедиться, что вы выбрали точку данных. Если вы не выберете одну точку данных, щелкнув ее 2 раза, вы в конечном итоге измените все точки данных, поскольку у нас есть только одна серия. Итак, чтобы повторить, щелкните точку данных 2 раза , чтобы изменить только один маркер.

г) Вставьте маркер

e) Повторите эти шаги для каждого маркера. Вот как будет выглядеть диаграмма с флагом страны, заменяющим 2 из трех точек данных:

Вы заметите, что при замене стандартного маркера на настраиваемый маркер также заменяется маркер легенды на настраиваемый маркер:

Вот еще один образец того же графика с красной или зеленой стрелкой вверх / вниз. Я добавил метки данных, чтобы вы

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

Видеоурок по Excel

Производитель

Стив = Истинный (поклонник Excel)

Призыв к действию

Если вам нравятся эти сообщения в блоге Excel и обучающие видео по Excel, не забудьте подписаться на мой RSS-канал и YouTube! Канал.

Самые быстрые способы создания новой диаграммы в электронной таблице Excel

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

1) Сначала создайте диапазон данных диаграммы на листе Excel, а затем выделите этот диапазон:

2) Затем вам нужно решить, хотите ли вы встроенную диаграмму Excel (см. B ниже) или хотите, чтобы итоговая диаграмма отображалась на отдельном листе диаграммы (см. A ниже)?

A) Чтобы создать диаграмму Excel на отдельном листе Excel, известном как лист диаграммы, после того, как вы выделили диапазон диаграммы, просто нажмите клавишу «F11», и ваша диаграмма будет создана на новом листе в книге Excel.

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

B) Чтобы создать диаграмму Excel, встроенную в существующий рабочий лист Excel, после того, как вы выделили диапазон диаграммы, просто нажмите клавишу «Alt + F1», и ваша диаграмма будет создана на текущем листе Excel.

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

Вот короткое видео, демонстрирующее эти методы:

Знаете ли вы, как быстрее создать диаграмму? Если да, дайте мне знать, если будут комментарии.

Спасибо, что посетили мой блог. Убедитесь, что вы зарегистрируетесь как зарегистрированный пользователь, чтобы получить следующую публикацию с шаблоном панели инструментов Excel.

Стив = Правда

диаграмм Excel — Ablebits.com

Ultimate Suite

Более 60 профессиональных инструментов для Microsoft Excel

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

Работает для: Windows

Узнать больше об Ultimate Suite

Общие шаблоны электронной почты

Пользовательские шаблоны писем для команд и отдельных лиц

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

• Быстро отвечать на электронные письма с помощью шаблонов
• Персонализируйте свои ответы с помощью макросов
• Автоматически прикреплять файлы и изображения
• Использовать черновики Outlook в качестве шаблонов

• Создавайте красивый дизайн писем
• Защитите свою конфиденциальность с помощью шифрования
• Использование на устройствах с сенсорным экраном
• Запуск на ПК, Mac, планшете с Windows

Работает для: Windows • macOS • Outlook Online

Узнать больше об общих шаблонах электронной почты

Проверка ссылок для Microsoft Word

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

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

• Искать все типы ссылок
• Определить состояния ссылок

• Одновременное редактирование нескольких гиперссылок
• Сортировка и фильтрация ссылок по разным критериям

Работает для: Windows

Узнать больше о Link Checker

Text Toolkit для Excel

35+ удобных опций для идеального оформления текстовых ячеек

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

• Обрезать лишнее
• Преобразование текста в числа
• Подсчет символов и слов
• Изменить регистр букв
• Извлечь текст или числа
• Удалить текст на его месте

• Добавить тот же текст в выделенные ячейки
• Разделить ячейки по символу, строке или маске
• Заменить ненужные символы
• Удалить ведущие нули
• Чистые непечатаемые символы
• Поменять местами текст в ячейках

Работает для: macOS • Excel Online

Узнать больше о Text Toolkit

Дополнения для Google Таблиц

примеров диаграмм Excel | Exceljet

Перейти к основному содержанию

• Логин
• Тележка

Быстро, чисто и по делу

• Обучение
• Видео
• Функции
• Формулы
• Ярлыки
• Блог

Типы диаграмм |
Примеры диаграмм |
Обучение

Линейная диаграмма с множеством точек данных

Динамическая диаграмма игнорирует пустые значения

Световой день от восхода до заката

Плавающая гистограмма с полосами вверх и вниз

Достижение цели проекта

Ассортимент продукции с течением времени

Популярные дипломы о высшем образовании для женщин

Топ-15 стран по продолжительности жизни

Результаты опроса любимый вкус мороженого

Квартальные продажи по регионам кластера

Квартальные продажи по регионам накопления

Годовые данные отчета о прибылях и убытках

Продукты в этом году по сравнению с прошлым годом

Ассортимент продукции в этом году по сравнению с прошлым годом

График актуален с прогнозом

Гистограмма с целевой линией

Расчет пи

Смертность от передозировки героином в США

Утверждение Трампа первые 100 дней

© 2012-2021 Exceljet.