19.05.2020 07:02
ANadezhdina 9 4500 Источник
Не жди поездку в Гагры!
Давид Маркович, сериал «Ликвидация»

Всем привет!

За время карантина все, наверное, не по разу прочитали о том, как плодотворно А.С. Пушкин провел свой период «заточения», названный впоследствии «Болдинской осенью», поэтому не буду объяснять, что именно меня сподвигло написать следующую статью об инфографике средствами MS Office.

К тому же, мне есть что вам еще рассказать, ведь я более 10 лет преподаю и руковожу группой разработки учебных материалов в учебном центре «Сетевая Академия ЛАНИТ», а также являюсь автором курса по инфографике.

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


Я — легенда


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


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


Начнем с первого. Он наиболее простой.


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

Настроим вертикальную ось. В контекстном меню вертикальной оси выберем команду Формат оси… 

На вкладке Параметры оси установим минимальное значение 1500000, а максимальное значение 2000000 — для границ. Также установим главные единицы измерения 100000 и выделим диаграмму, чтобы увидеть, как применились наши параметры.

Настроим подписи горизонтальной оси. Для этого в контекстном меню диаграммы выберем команду Выбрать данные…

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

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

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

Выделив выбранный значок, скопируем его любым способом: комбинацией клавиш, контекстным меню или командами на ленте.Затем выделим на диаграмме ряд данных и вставим значок комбинацией клавиш CTRL+V.

В области Формат ряда данных перейдем на вкладку Заливка и границы и установим серый цвет для линии. 

С первым вариантом мы закончили. Предлагаю сразу приступить ко второму.


 Для построения следующего варианта нам надо в имеющейся таблице добавить ячейки со значениями 100% для каждого из двух рядов.Затем выделим диапазон B1:C3  и построим линейчатую гистограмму с группировкой.

Используя кнопку Элементы диаграммы, отключим оси, название диаграммы, сетку, легенду, но добавим подписи данных. Затем выделим подписи ряда 100% и удалим их клавишей Delete.

Для любого ряда с данными в контекстном меню выберем команду Формат ряда данных…

В области Формат ряда данных на вкладке Параметры ряда для перекрытия рядов установим значение 100%, а для бокового зазора 50%.

В контекстном меню ряда данных выберем команду Выбрать данные…

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

Выделим подписи данных и назначим тип шрифта и размер. В данном варианте это Impact 24 пт.

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

Скопируем оба символа еще раз и для одной пары назначим изменение цвета рисунка на серый, выбрав подходящий вариант в списке кнопки Цвет на вкладке Формат/Работа с рисунками. 

Выделим серый значок с изображением мужчины и скопируем его любым способом: комбинацией клавиш, контекстным меню или командами на ленте. Затем выделим точку данных Мужчины, 100% и вставим значок комбинацией клавиш CTRL+V.

В контекстном меню точки данных выберем команду Формат точки данных…

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

Выделим синий значок с изображением мужчины и скопируем его любым способом: комбинацией клавиш, контекстным меню или командами на ленте. Затем выделим точку данных Мужчины, 72% и вставим значок комбинацией клавиш CTRL+V.

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

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

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

Итак, теперь в вашем архиве три разных варианта использования в диаграммах значков вместо легенды. 

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


Данные можете придумать и свои, а символ с изображением стопки книжек из шрифта Webdings вам в помощь! 

Властелин колец


Моя прелесть…


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

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

Первый — для визуализации одного итогового значения, а второй — для нескольких.


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


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

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

В контекстном меню ряда данных выберем команду Формат ряда данных… 

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

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

На вкладке Заливка и границы установим серый цвет заливки.

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

В области Формат точки данных на вкладке Заливка и границы установим оранжевый цвет заливки.

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

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

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

Настроим выравнивание текста в надписи по горизонтали и вертикали, используя команды вкладки Главная. Ну, и последний штрих, назначим шрифт Impact и размер 28 пт.

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

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

А теперь к следующему примеру.


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

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

В контекстном меню любого ряда данных выберем команду Формат ряда данных… 

В области Формат ряда данных на вкладке Параметры ряда установим значение 50% для диаметра отверстия и угол поворота первого сектора 180 градусов.Чтобы увидеть, как применяются эти параметры, выделим саму диаграмму.

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

В области Формат точки данных на вкладке Заливка и границы установим белый цвет заливки.

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

Используя команду Фигуры на вкладке Вставка, добавим на лист прямоугольник, с длинной стороны 3 см, а высоту первоначально предлагаю сделать 1 см.Цвет прямоугольника и его границы сделаем таким же синим цветом, как и сектор диаграммы.

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


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

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

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

Отменим заливку и контур для надписи командами на вкладке Средства рисования/Формат.Настроим выравнивание текста в надписи по правому краю, используя команды вкладки Главная, назначим размер шрифта 12 пт и полужирное начертание.

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

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

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

С помощью команд на вкладке Средства рисования/Формат назначим серую заливку и эффект для нашей фигуры Заготовка 1. 

Выделим диаграммы и все дополнительные объекты и сгруппируем их, выбрав команду Группировать/Сгруппировать в контекстном меню или на вкладке Работа с рисунками/Формат.

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


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

Миссия невыполнима


Выполнима или невыполнима, не нам решать, но наблюдать за процессом очень интересно…


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

Вот вам пара примеров. Такие диаграммы еще называют диаграммами прогресса, а первый вариант очень часто называют термометром.


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


 Данные для построения содержатся в ячейке С1, а в диапазон A3:D5 введем дополнительные данные и вычисления. Значения 60%, 30% и 10% нужны для цветовых разделений шкалы по уровням. В ячейку С3 введем ссылку на ячейку С1, значение 2% введем в ячейку С4, это будет размер красной метки.В ячейку С5 введем формулу =100%-С3-С4

Выделим диапазон B3:D5 и построимнормированнуюгистограмму с накоплением. Отключим оси, название диаграммы, сетку и легенду.

Назначим цвета для рядов, используя мини-панель форматирования. Для самого верхнего назначим серый цвет 25%. 

Для среднего ряда цвет назначим немного темнее, а для нижнего еще темнее среднего.

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

В контекстном меню любого ряда выберем команду Формат ряда данных…

В области Формат ряда данных установим боковой зазор 0%.

Уменьшим ширину диаграммы.

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

Выделим подпись данных и назначим полужирное начертание и белый цвет шрифта.

А второй пример диаграммы будем строить с небольшой хитростью. Тут нам понадобится функционал PowerPoint.


 В ячейке С2 находятся данные, которые мы будем отображать в нашей диаграмме, а в ячейке D2 вычислим разницу между 100% и значением в ячейке С2.

Выделим диапазон C2:D2 и построим нормированную гистограмму с накоплением. Изменим отображения данных по осям, нажав кнопку Строка\столбец на вкладке Работа с диаграммами\Конструктор. 

Используя кнопку Элементы диаграммы, отключим название диаграммы, оси и сетку. Также уменьшим ширину области диаграммы.

В контекстном меню любого ряда выберем команду Формат ряда данных…

В области Формат ряда данных установим боковой зазор 0%.

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

Теперь откроем PowerPoint и создадим изображение прозрачного денежного мешка из символа шрифта Webding. На слайде нарисуем белый прямоугольник, примерно 8 на 10 см. Создадим надпись и вставим в нее символ с изображением мешка денег. Размер шрифта назначим 300 пт. Размер надписи лучше увеличить, чтобы весь символ в нее попадал.

Наложим область надписи поверх прямоугольника и выделим все комбинацией клавиш CTRL+A, затем на вкладке Средства рисования/Формат в списке кнопки Объединить фигуры выберем команду Вычитание.

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

Выполним обрезку рисунка, используя команду Обрезка на вкладке Работа с рисунками/Формат.

Введем в ячейку С2 значение 100%. Это нам позволит подогнать размер нашего мешка по полной высоте диаграммы.Наложим изображение мешка на диаграмму и изменим размеры рисунка так, чтобы высота диаграммы четко совпадала с размерами мешка по высоте, а ширина мешка была меньше или равна ширине диаграммы. Обратите внимание, это можно отслеживать только в сам момент изменения размеров рисунка.

Еще немного подрежем рисунок, но так, чтобы кроме рамки нашей диаграммы ничего лишнего больше не отображалось.

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

Выделим диаграмму, рисунок и надпись и сгруппируем их с помощью команды Группировать\Сгруппировать в контекстном меню.

Вы уже ждете пример для самостоятельной реализации? Вот он.


Иллюзия обмана


Чем больше вам кажется, что вы видите, тем легче вас обмануть…


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

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

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

Образно говоря, получается некое окно с одного листа на другой.

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

Вот пример простого размещения снимков диаграмм на одном листе.  


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


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

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


 На вкладке Файл перейдем в раздел Параметры.

В окне Параметры Excel перейдем на вкладку Настроить ленту.В списке поля Выбрать команды: выберем вариант Все команды.

Найдем в списке команду Камера и выделим ее.Обратите внимание, команды расположены по алфавиту, и это поможет быстрее ее найти.

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

Убедимся, что у нас в одном поле выделена команда Камера, а в другом — Новая группа (настраиваемая), и нажмем кнопку Добавить. Команда Камера добавится в новую группу.

При желании можно переименовать группу, а для завершения нажать кнопку ОК.

Теперь на вкладке Главная есть кнопка


Самое время разобраться, как она работает.

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


Приступим.


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

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

Нажмем кнопку Камера на вкладке Главная. Именно на эту вкладку мы добавили кнопку.

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

Выполним обрезку для нашего рисунка, выбрав команду Обрезка в мини-панели форматирования.

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

Отменим также и границы для выделенного рисунка, выбрав на вкладке Работа с рисунками/Формат в списке команды  Граница рисунка вариант Нет контура.

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

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

На листе с диаграммой введем в ячейку B1 значение 75% и сразу увидим, что диаграмма перерисовалась. Ну тут ничего особенного, к этому мы уже привыкли.


Теперь перейдем на лист со снимком…


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

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

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

А напоследок для самых внимательных и терпеливых читателей приятный сюрприз — скидка 50% на онлайн-курс по инфографике по купону INFOHABR. Срок действия купона — до 31 августа 2020 года, а доступ открывается на 3 месяца. Только тсссс!

До встречи в сети или на наших курсах!