Визуализация статусов проектов

Визуализация (от лат. visualis, «зрительный») — способ представления чисел в удобном для анализа виде

---

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

Под статусом, или стадией проекта имеется в виду следующее. Примеры: «Новый» (только начали), «В тестировании» (сделали, проверяем), «Отстаём!» (нарушили срок сдачи) «Сдано» (заказчик подписал акты и произвёл оплату) и т.д.

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

---

Как это работает?

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

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

---

Итак, начнём.

1. Заполним таблицу статусами

Добавим новый лист DATA и заполним на нём таблицу со статусами и соответствующими числовыми значениями.

---

2. Настроим проверку данных

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

В поле Правила выберем на листе DATA диапазон ячеек в столбце B (там у нас список «Новый», «Выполняется» и т.д.)

Сохранить.

---

3. Настроим выборку числовых значений

В соседнюю ячейку в столбце C вводим формулу:

=ЕСЛИОШИБКА(ИНДЕКС(statusValue;ПОИСКПОЗ($B6;DATA!$B:$B;0)))

Здесь используются 3 функции: ЕСЛИОШИБКА, ИНДЕКС и ПОИСКПОЗ.

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

Если щёлкнуть внизу этого справочного окна по ссылке "Подробнее о функции ИНДЕКС…", откроется новое окно с более подробным описанием функции и примерами её использования.

---

3.1. Функция ИНДЕКС

Функция

ИНДЕКС(ссылка; [строка]; [столбец])

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

[ссылка] — это массив (диапазон ячеек), в котором ищем.
[строка] и [столбец] — номера строки и столбца, на пересечении которых находится искомое значение.

Например, функция ИНДЕКС(DATA!D2:D8;2;1) в нашем примере возвратит значение 0,07. Т.к. формат ячеек в столбце C процентный, то на экране будет 7%.

---

3.2. Функция ПОИСКПОЗ

Номер строки заранее неизвестен, его надо найти. Ищем с помощью функции ПОИСКПОЗ(запрос; диапазон; [метод_поиска]). Она возвращает относительное положение значения в диапазоне.

[запрос] — то, что мы ищем (значение в ячейке B6 — текст «Новый»).
[диапазон] — где ищем (диапазон ячеек B:B на листе DATA, там у нас статусы).
[метод_поиска] — 0, 1 или -1.
У нас 0, поэтому функция находит первое значение, равное значению аргумента [запрос].

Например, если в ячейке B6 выбран статус «Новый», то функция

ПОИСКПОЗ($B6;DATA!$B:$B;0)

возвращает значение 3.

---

3.3. Функция ЕСЛИОШИБКА()

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

---

3.4. Именованные диапазоны

Посмотрим ещё раз на формулу в ячейке C6:

=ЕСЛИОШИБКА(ИНДЕКС(statusValue;ПОИСКПОЗ($B6;DATA!$B:$B;0)))

В [ссылка] вместо диапазона ячеек DATA!D:D мы видим statusValue.
Это название диапазона DATA!D:D.

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

Именовать диапазон ячеек просто.

Выделите на листе DATA столбец D

Меню Данные > Настроить именованные диапазоны

Введите имя «statusValue» и нажмите Готово

---

3.5. Функция SPARKLINE()

Хорошо, теперь займёмся столбцом E.

В ячейку E6 введём формулу, которая создаст внутри ячейки миниатюрную диаграмму:

=ЕСЛИОШИБКА(SPARKLINE(C6; {«charttype»\»bar»;»max»\1;»color1″\»#69F0AE»;»color2″\»#ffffff»}))

Её синтаксис:

SPARKLINE(данные; [параметры])

[данные] — диапазон ячеек, содержащий данные для построения диаграммы. В нашем примере данные берём из столбца C (там цифровые значения статусов).

[параметры] — диапазон ячеек, содержащий дополнительные параметры и их значения. С их помощью настраивается вид диаграммы. «charttype»\»bar» означает, что вид диаграммы — гистограмма.
Параметр «max»\1 означает, что максимальное значение по горизонтальной оси — 1.
«color1″\»#69F0AE» задает светло-зелёный цвет столбцов.

---

3.6. Условное форматирование

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

Выделим нужные ячейки в столбце E

Меню Формат > Условное форматирование

В поле Форматировать ячейки, если… выберем Ваша формула

В поле Значение или формула введём формулу:
=$C6=0,01

В поле Формат выберем красный цвет заливки, и Готово.

---

Вот и всё, задача выполнена. Статусы проектов наглядно отображаются. Визуализация статусов проектов быстро оценить ситуацию.

Используйте эти приёмы в своих проектах.

---

Здесь можно посмотреть видео, в котором воспроизводится вышеописанное: Визуализация статусов проектов в таблицах Google

Узнать больше о таблицах и формах Гугл вы можете на онлайн-курсе Система управленческого учёта в Таблицах и Формах Google.