Россия — первая в рейтинге самых курящих стран мира
Ссылка на источник https://t.me/kriptodvizh/120
Вертольот
Сравниваем продажи
Буэнос диас, мучачос!
Предлагаю снова погрузиться в "сладостный, волшебный, поэтический мир сводок, цифр, отчетов, планов и смет". И диаграмм.
Сегодня сравним продажи чего-нибудь, и слепим из них вот такую визуализацию:
Нам понадобится таблица следующего вида:
Есть три вспомогательных столбца F, G, H.
В графе "Подсветка" (F) - формула "=ЕСЛИ(E4-D4>0;E4;0)" - чтобы потом выделить на диаграмме столбцы, превышающие уровень прошлого года. В графе "Ось" (G) тупо нолики, потом поймете, зачем. А в графе "Подпись" (H) повторяем название категорий товаров, чтобы добавить их на диаграмму, причем располагаем их по середине наших наборов данных (конечно, если в данных по месяцам будет четное количество строк, то по середине не получится, и на диаграмме подписи будут чуть смещены)
Выделяем таблицу как показано на скрине:
В меню "Вставка" -> "Гистограммы" -> "Гистограмма с группировкой":
Для названия диаграммы сделаем ссылку на ячейку, в которой содержится заголовок таблицы.
1. выделяем на диаграмме название
2. в строке формул ставим знак =
3. Жмякаем ЛКМ ячейку B1, затем Enter.
Удаляем в легенде наши вспомогательные значения - подстветку и ось. Сначала ЛКМ выделим всю легенду, затем ЛКМ еще раз щелкаем по нужному элементу -> Delete:
Легенду через формат располагаем сверху, а в левый угол перетягиваем мышкой. (Ну или выберите какой вам нравится вариант расположения):
Убираем оси. И получаем вот такую лысую фигню*:
(*на скринах выше видно, что подписи категорий товаров легли кривенько, но если вас такой вариант устраивает, то можно пропустить дальнейший извращенный процесс добавления подписей через танцы с бубном)
Выделяем любой ряд данных и через меню Работа с диаграммими -> Конструктор -> Изменить тип диаграммы, и поменяем:
- для ряда "2021" на "график с маркерами"
- для ряда "Ось" на "точечную с прямыми отрезками"
После этих манипуляций появятся всякие лишние оси - их удаляем:
Пока столбики стоят по отдельности - настраиваем их цвета на свой вкус. Серый ряд ("Подсветка") делаем, например, темнее или ярче:
После того, как с расцветкой определились - делаем перекрытие рядов, и минимальный боковой зазор:
Также, через формат меняем вид графика, который показывает продажи за 2021 год, делаем пунктирную линию, маркер с белой заливкой, и т.д. и т.п.
Теперь, выделяем желтую линию, ПКМ -> добавить подписи данных:
После добавления подписей, нам нужно выделить сами подписи, в нашем случае нолики:
И в формате подписей данных ставим галку на "значения из ячеек" -> "выбрать диапазон" и мышкой выделяем диапазон $H$3:$H$19:
Добавленные таким образом подписи тоже лягут криво, поэтому в формате поставим флажок "Снизу":
Чтобы подписи не наползали на диаграмму, нужно выделить ЛКМ область построения, и потянуть за нижний маркер чуть вверх:
Теперь спрячем наш нулевой ряд, выделив его ЛКМ и поставив флажок "Нет линий":
Ну и последние штрихи, добавляем подписи данных, располагаем их как надо, ну и т.п., чтоб получилось вот так:
А как сделать оформление как на первом скрине - вы знаете из прошлого урока Вторичная круговая диаграмма + полезный фокус
Забирайте https://disk.yandex.ru/d/cEUMv0eAKKZ7hA
Вторичная круговая диаграмма + полезный фокус
Хэллоу, экселеводы и экселеведы!
Сегодня нарисуем круговую диаграмму:
Чтобы построить вторичную круговую диаграмму, нужно пройти 9 кругов ада.
Таблицу данных формируем следующим образом:
*все имена и события вымышлены, любые совпадения с реальными людьми и/или событиями чистая случайность
Графа "Доля" для построения диаграммы не нужна, в таблице она просто так, чтобы было.
В таблице есть некие категории (это могут быть категории товаров, должников, видов услуг и тому подобное). В моем примере данные отсортированы по убыванию - так как для маленького круга мы берем категорию жанров, по которым собирается менее 500 зрителей (либо, у вас это могут быть выделенные по любым иным признакам категории товаров/услуг/чего угодно - главное, чтобы те строки, которые вы хотите выделить в правый круг шли подряд ниже остальных категорий, которые будут располагаться в левом круге)
Затем, изуродуем нашу таблицу, добавив в нее строку, которая будет отделять данные для наших кругов:
*в предыдущих уроках были высказаны претензии по поводу того, что нельзя так обращаться с таблицами, с чем я категорически согласна. Но в этих постах нет цели научить вас культуре обращения с данными, я показываю пример таблиц именно для построения тех или иных диаграмм, а не для какой-то иной работы с ними.
Выделяем таблицу без итоговой строчки и "Доли":
В меню "Вставка" выбираем вторичную круговую диаграмму:
Как всегда, материм эксель за стрёмные цвета и каличный вид диаграммы, смиряемся и пашем дальше. Легенду надо выделить и нажать Delete (либо через "+" в котором скрываются/отображаются элементы диаграммы):
Дальше, мы видим, что какая-то чехарда с рядами данных в кругах - это поправимо. Нужно выделить ряды данных и в правой части экрана, где форматирование диаграмм - поставить необходимое нам количество значений во второй области построения:
Это количество (6) мы взяли, как говорится, не с потолка, а из нашей таблицы - под выделенной подкатегорией "Другие" находится 6 жанров фильмов, зрителей у которых меньше 500 человек.
Добавляем подписи данных - выделив ряды данных, ПКМ -> Добавить подписи данных:
Появится полная фигня, которую нам опять переделывать:
Выделим подписи данных ЛКМ, в форматировании диаграммы для подписей выбираем необходимые параметры:
Те фильмы, которые попали в правый круг по умолчанию, на диаграмме обзываются "Другой". Можно перебить название вручную. Или выделить только эту подпись, и в строке формул поставить "=" и жмякнуть на ту ячейку, где находится название выделенной нами подкатегории (это для того, чтобы каждый раз на диаграмме не перепечатывать руками название, если вдруг мы решили переименовать в таблице):
Тут пришлось изголяться, если мы ссылаемся на ячейку с названием подкатегории, то у нас похерится процентное значение. Поэтому в ячейку с названием я зашила формулу, которая сцепляет текст с суммой процентов.
И еще - проценты в таблице и на диаграмме могут отличаться на 1 п. так как, я х.з вообще по какой логике идет округление на диаграмме((.
Ну и последнее, настраиваем "красивости" - цвета, положение подписей, можно сделать круги разрезанными, и т.д. и т.п. на свой вкус и цвет. Я подробно эту муть расписывать не буду, там уже все просто и понятно.
А теперь фокус
Чтобы мы могли перенести диаграмму на чистый лист (ну допустим, мы хотим сделать отдельный лист с инфографиками, без всяких таблиц и прочей лабуды) на панели быстрого доступа нажмите на малюсенькую стрелочку ("Настроить панель быстрого доступа") -> Другие команды
Выберите "Все команды" и в списке по алфавиту найдите инструмент "Камера" -> Добавить -> ОК
А теперь, располагаем нашу диаграмму таким образом, чтобы быть уверенными, что ни ее размер, ни ее положение мы больше менять не будем. Выделяем ячейки, на которых "лежит" диаграмма и нажимаем значок "Камера" на панели быстрого доступа:
Появится пунктирная рамка, как при копировании:
В любом пустом месте этого же листа сделайте движение зажав ЛКМ, как будто выделяете диапазон (размах не важен), и наша диаграмма волшебным образом появится на экране, но уже в виде рисунка:
Фокус в том, что этот рисунок можно перенести на отдельный лист, а когда мы будем менять формат диаграммы, все изменения отобразятся и на её "фотографии". Почему нельзя диаграмму передвигать, или менять размер - это всё тоже отобразится на рисунке. А ещё, для рисунка можно настроить всякие красивости, например тени или объем, отражение, поворот и много еще чего:
Забирайте на здоровье: https://disk.yandex.ru/d/02Z3ifnWrPZ1SQ
Диаграмма "План-Факт"
Здрасть!
Сегодня мы нарисуем диаграмму "План-факт" вот такого вида:
Для этого нам понадобится таблица:
В графе "Дельта" простая формула "Факт" минус "План". Для наглядности задаём формат, благодаря которому отрицательная разница светится красным цветом, а положительная - синим. Выделяем диапазон данных, ПКМ -> Формат ячеек -> выбираем (все форматы). А в строке Тип: набираем (кого я обманываю - копируем) это: [Синий]+0%;[Красный]-0%
А дальше пойдут танцы с бубном. Первым делом, зажав клавишу Ctrl выделим столбцы "Месяц", "План" и "Дельта". В меню "Вставка" ищем кнопку "Вставить диаграмму с областями" и жмякаем центральную диаграмму в верхнем ряду ("С областями и накоплением"):
Результатом этих действий будет вот такая херобора:
Но нам этого мало. Поэтому выделим теперь столбцы "Факт" и "План", жмякнем ПКМ -> копировать (или Ctrl+C):
А теперь выделим область диаграммы, нажмем Ctrl+V и получим вот такое:
Затем выделим любой ряд данных на диаграмме, жмякнем ПКМ -> изменить тип диаграммы для ряда:
В окне "Изменение типа диаграммы" поменяем последние два ряда на "График с маркерами"
Получится вот так:
Растянем диаграмму вширь, удалим линии сетки, и прочие лишние рамки (линии сетки можно выделить ЛКМ и нажать Delete, только аккуратно, не грохните всю диаграмму):
Убираем линии на горизонтальной оси:
Нижний ряд данных (синий) выделим ЛКМ и сделаем его без заливки:
Название я тоже удаляю (я подписываю диаграммы отдельным заголовком непосредственно в презентации)
Теперь нужно навести порядок в легенде. Нажимаем на нее ЛКМ один раз - и выделяется вся легенда:
А второй раз жмякаем крайний левый "План" - должен быть выделен только этот элемент легенды:
- и нажимаем Delete. Таким же образом поступаем с "Дельта". Затем легенду перенесем наверх:
Вертикальную ось значений выделяем, и меняем начальное значение, например на 0,5 (так как в таблице все значения план-факт больше 50%) - чтобы немного отцентровать график:
Затем вертикальную ось удаляем, она нам больше не нужна. Теперь поочередно выделяем ЛКМ ряды данных и добавляем подписи:
Когда подписи добавлены, мы меняем их положение на "Сверху" или "Снизу" - в зависимости от того, каких значений больше - тех что выше плана или ниже (чтобы меньше перетаскивать вручную), заодно снимаем галку с "линии выноски"
Ряды данных перекрашиваем как нам нравится - не забываем про маркер, у него есть параметры заливка и граница (границу делаем без линий):
Ряд "Дельта" - он посередине между линиями, оранжевый - делаем с узорной заливкой, заштрихованным:
К нему также добавляем подписи данных, и растаскиваем вручную - положительные значения наверх, отрицательные вниз. В формате подписей данных должна по умолчанию стоять галка "Связь с источником" - тогда все наши подписи будут такого же формата, как мы задали в таблице.
Если вы дочитали до конца, то вы супер молодцы, потому что я уже чёт заколебалась, фух.
За это вот вам файл с примером прошлого урока https://disk.yandex.ru/i/1cCD_hA9xC9Ukw
и файл данного урока https://disk.yandex.ru/i/0Qi8vmRVpLNmwA
Инфографик "Рейтинг"
Привет, отчаянные и малочисленные, преданные фанаты Excel!
Сегодня изобразим инфографик, показывающий рейтинг сферических руководителей в вакууме (Название мне придумывать лень):
Для этого нам нужна таблица вот такого вида:
Предположим, норматив установлен один на квартал. Пустая строка между месяцами позволяет нам на гистограмме также визуально отделить их
Итак, для построения гистограммы выделим таблицу (без столбца «Ранг»):
В меню «Вставка» ищем значок «Вставить линейчатую диаграмму». И жмякаем «Другие линейчатые диаграммы»:
В окне «Вставка диаграммы» нужно выбрать «Линейчатая с группировкой» -> ОК:
Результатом этих действий будет вот такое уродливое нечто:
Мы должны безжалостно удалить всё лишнее – линии сетки, ось значений, всякие там рамки и т.п. Для этого, выделим ЛКМ эти элементы по очереди и жмякнем "Delete".
А лишнее обрамление и рамки убираем в конструкторе диаграмм. Например, выделили вертикальную ось категорий -> ставим флажок в разделе «Заливка и границы» «нет линий»:
То же самое проделываем для всей диаграммы, чтобы убрать ее рамку:
Обратите внимание - линейчатая гистограмма по умолчанию переворачивает вертикальную ось категорий (то есть наших несчастных руководителей) с ног на голову. Чтобы было красиво и логично, в настройках диаграммы поставим обратный порядок категорий. Для этого выделяем вертикальную ось категорий и в параметрах оси ставим галку «Обратный порядок категорий»:
Теперь причёсываем ряды данных. Выделяем сначала ряд «Факт» и делаем его темно-синим, или каким угодно, какой вам нравится:
Затем, выделим ряд «Норматив» и делаем без заливки, а линии светло-синими:
Теперь, выделим любой ряд данных и настроим перекрытие рядов = 100 %, а боковой зазор, ну например 20%:
Шрифт тоже красим в темно-синий. А легенду можно переместить наверх, или оставьте как есть, и так сойдет:
И последнее, выделим ряд данных "Факт" и ПКМ добавим подписи данных. Расположить их лучше по центру, и перекрасить в белый: