Тема: ячейки 2
Простые диаграммы в Excel — дополнительные возможности
В прошлый раз я рассказывал о том, как строить микро-диаграммы в ячейках Excel. На самом деле это ещё не всё, что можно вытворять. Подключаем фантазию, и..
…Чего ооочень не хватало в той диаграмме — так это подписей. Добавим их следующим образом:
=ПОВТОР(«|»,D2)&» «&D2
=REPT(«|»,D2)&» «&D2
Диаграмма в ячейке с подписями
…Превращаем время в диаграмму Гантта. Наверное, самое полезно-ценное применение. В Excel просто так Ганнта не построить, MS Project вряд ли у кого-то есть, а привязать нормально к строкам с данными — ещё сложнее. Итак: полоски показывают промежутки времени, отведённые на определённую задачу.
=ПОВТОР(» «,(D2-МИН($D$2:$D$10)))&ПОВТОР(«|»,1.5*(E2-D2+1))
=REPT(» «,(D2-MIN($D$2:$D$10)))&REPT(«|»,1.5*(E2-D2+1)),
где D2 — ячейка с начальной датой, E2 — ячейка с конечной датой, а колонка D2:D10 есть колонка всех начальных дат
Умножение на 1.5 необходимо для того, чтобы соотнести ширины: в шрифте Arial пробел равен примерно полторы символа «I»
Гантт в ячейке Excel
..Добавляем к графикам маркеры-окончания, а сами графики делаем пунктирами. Остаётся по-прежнему читабельно, а график принимает совсем иной вид.
Диаграмма с маркером-окончанием в ячейке Excel
Кто не верит — смотрите файл со всеми этими графиками.
Давайте ещё что-нибудь эдакое придумаем?
Простые диаграммы внутри ячеек в Excel
Есть один интересный приемчик в работе с эксель-данными. Раньше о нём упоминала Ира, но кратко и давно — а поэтому освежим архивы истории.
Допустим, у вас есть таблица. Много строк. И есть колонка, по которому все эти строки сравниваются. Например: продажи по каждому магазину торговой сети, выручка по товарным категориям, тиражи изданий, или что-либо неважно. Главное — что график строить вроде как незачем, а сравнить значения в таблице хочется. Например, хочется нам посмотреть, какие из моделей Volvo на сайте auto.ru выставляются активнее всего (вдруг вам это действительно понадобилось?).
Исходные данные, перетащенные в Excel:
Исходные данные для графика внутри ячейки
Что делать? Строить диаграмму прямо в ячейке. Эксель 2010 умеет делать микро-чарты (спарклайны), но мы сейчас не об этом. Нам нужно не динамику в ячейке построить, а сравнить между собой строки. В работу берём формулу REPT («Повтор» в русской версии).
Итак, пишем в строке формулы для ячейки А3 магический код:
= REPT ( «I» ; A2), где А2 — это ячейка с количеством машин
Результат: ячейка заполнилась символами I, в количестве равном цифре каждой ячейки.
Результат выдачи - количество в каждой ячейке
Останется лишь поставить такую ширину столбца, который позволит увидеть всю длину.
Зачем все это? Только лишь для того, чтобы оставить таблицу чистой и аккуратной, не городить графиков, и при этом сделать таблицу читабельнее.
Как заметили самые смекалистые — такие диаграммы как будто бы не работают на больших числах. А вот нет — делаем дополнительную промежуточную колонку, в которой тысячые и миллионные разряды убираем делением, и приводим все так чтобы значения не превышали 50-100.
Итоговый график в ячейке
Это еще не все. Идем дальше: визуализируем таким же способом положительные и отрицательные значения. Отрицательные строятся в одной колонке, положительные в другой. Формула контролирует, что именно отображать.
Пример: статистика изменений в предложениях всё того же Volvo (это уже выдумано, пример-то надо продолжать). Результат:
Выигрыши - проигрыши диаграмма в ячейке Excel
Как?
1. Строим модуль значения каждой строки, так как REPT ничего не знает про отрицательные числа
2. Строим в двух колонках REPTы так, что в одной колонке только положительные, в другой только отрицательные
3. Форматируем, и приводим в порядок. Works!
Кстати, все подобные экзерсисы — отличный тренажер для логики. Love it!