Лаба Логотипы в excel. Задача добавить на диаграмму логотипы компаний в качестве подписей. Сами логотипы уже скопированы и вставлены в книгу как картинки
 Скачать 1.01 Mb.
|
|
Лабораторная про Логотипы + Посвечивание Имеем в качестве исходных данных простую таблицу и обычную гистограмму, построенную по этим данным:  Задача: добавить на диаграмму логотипы компаний в качестве подписей. Сами логотипы уже скопированы и вставлены в книгу как картинки. Шаг 1. Вспомогательный ряд Добавьте в таблицу столбец Лого и в каждой его ячейке введите одно и то же отрицательное число – оно будет определять расстояние от логотипов до оси Х. Затем выделите созданный столбец, скопируйте его и вставьте в диаграмму, чтобы добавить к ней новый ряд данных:  Шаг 2. Только маркеры Щёлкаем по добавленному ряду оранжевых столбцов правой кнопкой мыши и выбираем команду Изменить тип диаграммы для ряда (Change series chart type). В открывшемся окне меняем тип на График с маркерами (Line with markers):  Затем выключаем линии, щёлкнув по ним правой кнопкой мыши - команда Формат ряда данных (Format data series), чтобы остались видны только маркеры:  Шаг 3. Добавляем логотипы Теперь скучная, но главная часть: выделяем по очереди каждый логотип, копируем его (Ctrl+C) и вставляем (Ctrl+V) на место соответствующего маркера (предварительно его выделив). Получаем вот такую красоту:  Шаг 4. Убираем лишнее Для пущей наглядности можно скрыть отрицательные значения на вертикальной оси Y. Для этого в параметрах оси выберем раздел Число (Number) и введём код формата, который не будет отображать значения меньше нуля: #;;0  Если захочется скрыть и вспомогательный столбец Лого из таблицы, то придется дополнительно щёлкнуть по диаграмме правой кнопкой мыши и выбрать команды Выбрать данные - Скрытые и пустые ячейки (Select data - Hidden and empty cells), чтобы разрешить отображение данных из скрытых столбцов:  Вот и все премудрости. Но красиво же, да? :) Предположим, что мы построили простую гистограмму для наглядного отображения значений по нескольким категориям - например прибыли по городам-филиалам:  Задача: выделить цветом нужный столбец(ы) по заданному условию, например город с максимальными продажами или город выбранный в выпадающем списке и т.д. Причем сделать это не вручную поменяв цвет заливки столбца, а "на автопилоте", т.к. завтра данные поменяются и подсветка должна также обновиться. Другими словами, нужно реализовать что-то типа условного форматирования, но не для ячеек, а для столбцов в диаграмме. Суть способа Идея, на самом деле, простая. Добавим к нашей таблице с данными еще один вспомогательный столбец:  Обратите внимание, что: Напротив города, который мы хотим подсветить (в примере - Королев), стоит его же значение. Во всех остальных строчках мы намеренно создаем ошибку #Н/Д! с помощью функции НД() без аргументов (в английской версии - NA()) Если теперь построить гистограмму по такой таблице или добавить вспомогательный столбец как новый ряд в имеющуюся диаграмму, то мы увидим прежнюю картинку, но нужный нам столбец будет задублирован в новом ряду данных:  Теперь главное: щелкаем правой кнопкой мыши по любому столбцу, выбираем команду Формат ряда данных (Format Data Series) и в появившемся окне двигаем ползунок Перекрытие рядов (Overlap) на 100%:   Дубликат столбца наедет на исходный, полностью его перекрывая, но, по факту, выглядеть это будет как подсветка заданной колонки. А ячейки, в которых есть любые ошибки на диаграммах не отображаются. Вместо НД также можно выводить пустую строку "" - эффект примерно тот же. Ну, а если использовать в дополнительном столбце формулы, то можно реализовать подсветку по любым критериям - простор для фантазии тут большой. Давайте разберем несколько примеров для затравки. Подсветка столбца в диаграмме с выбором из выпадающего списка Предположим, что мы сделали выпадающий список в ячейке G2 для выбора нужного города с помощью классической команды Данные - Проверка данных (Data - Data Validation) и указали в качестве источника наш список городов A2:A15:  Теперь можно во вспомогательном столбце подсветки ввести простую формулу, которая будет подсвечивать именно выбранный город:  Как легко сообразить, функция ЕСЛИ (IF) проверяет тот ли это город, который нам нужен, и выводит его значение либо генерирует ошибку #Н/Д. Подсветка самого большого или маленького столбца Очень похоже на предыдущий способ, но функцией ЕСЛИ по очереди проверяем- равно ли значение продаж для каждого города максимальному по столбцу:  Само-собой, можно функцию МАКС заменить на МИН. Подсветка столбцов выше/ниже среднего А если заменить в предыдущей формуле знак равно на больше или меньше, а функцию МАКС на СРЗНАЧ (AVERAGE), то можно подсвечивать все города, где продажи были выше или ниже среднего по стране:  Подсветка Top-3 Если вместо функции МАКС, которая находит только одно самое большое значение, использовать функцию НАИБОЛЬШИЙ (LARGE), то можно легко подсветить Top-3:  Ну, и так далее - думаю, идею вы уже уловили. Линейчатая с распределение (частотное сравнение)  В зарубежных источниках такой тип диаграммы назвают tornado-chart. От наших соотечественников я слышал варианты "диаграмма-горшок" и "диаграмма-бабочка" - у кого на что больше похоже :) Такой тип диаграмм удобно использовать для наглядного сравнение двух наборов данных, например: мы и конкуренты по основным направлениям (товарам, услугам) количество мужчин и женщин разного возраста среди покупателей или зрителей этот год и прошлый год по основным KPI и т.д. Строить такую штуку не очень сложно, но пара небольших засад на пути поджидает - давайте с ними разберемся. За основу возьмем обычную линейчатую гистограмму с накоплением (Stacked Bar Chart):  Чтобы столбцы расходились в разные стороны, а не плюсовались друг на друга, используем простой трюк - добавим перед числами одного из наборов знак минус. Тогда отрицательные и положительные значения пойдут в противоположных направлениях от оси Y. Проще всего это сделать с помощью специальной вставки, а именно: ввести минус 1 в любую пустую ячейку и скопировать ее выделить числа в данных по одному из наборов (например, население по городу в диапазоне B5:B19) нажать Ctrl+Alt+V, чтобы вызвать окно специальной вставки выбрать опции Значения (Values) и Умножить (Multiply) и нажать ОК. Теперь можно выделить всю нашу таблицу с данными (A4:C19) и строить линейчатую диаграмму с накоплением на вкладке Вставка (Insert). Результат будет уже похож на то, что хотелось:  Для полноты картины лучше перенести вертикальную ось Y вправо до упора, чтобы она не мешалась посреди диаграммы. Для этого нужно щелкнуть правой кнопкой мыши по горизонтальной оси, выбрать команду Формат оси (Format Axis) и затем в группе Вертикальная ось пересекает вариант Максимальное значение оси (Maximum Value). Также может возникнуть желание спрятать на диаграмме минусы перед числами. Проще всего это сделать, если применить к ячейкам с исходными данными хитрый пользовательский формат, который не отображает знак "минус". Для этого нужно выделить исходные ячейки (B5:B19), открыть окно Формат ячейки и, выбрав вариант Все форматы (Custom), ввести в поле Тип следующую маску:  Минусы исчезнут в ячейках и, как следствие, на диаграмме. |