Создайте индекс риска, который меняет свой цвет

Шаг 1: веса в одном ряду

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

Rules!B7:E7   0.30   0.30   0.20   0.20

Теперь совокупный балл в каждой строке представляет собой одну формулу, указывающую на эту строку:

=ROUND(SUMPRODUCT(E6:H6, Rules!$B$7:$E$7), 1)

SUMPRODUCT умножает каждую подоценку на ее вес и складывает результаты. Знаки доллара блокируют строку весов, поэтому вы можете заполнить эту формулу двадцать строк, и все они по-прежнему будут иметь одинаковые веса.

Есть искушение написать =E6*0.3+F6*0.3+G6*0.2+H6*0.2 вместо. Не. Это скрывает веса в двадцати формулах, поэтому их изменение означает двадцать изменений, а это означает, что этого никогда не произойдет, что означает, что веса в файле перестают соответствовать весам в политике.

Шаг 2. Отсечки в одной таблице

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

Перекрытие — это ошибка, которая ранее вызвала проблему 65. Если Высокий начинается с 65, а Повышенный заканчивается на 65, диапазон зависит от того, какое правило было оценено первым, и это не политика, которую кто-то записал.

Откуда взялись эти пять цифр — это отдельный вопрос, и более важный, чем электронная таблица. Если вы выбрали их потому, что они круглые, прочтите как выбрать пороговые значения из данных прежде чем защищать их перед кем-либо.

Шаг 3: найдите группу, никогда не вводите ее

Столбец полосы представляет собой формулу. Он считывает счет и находит последнюю точку, после которой счет очищается.

=INDEX(Rules!$B$12:$B$16, MATCH(I6, Rules!$C$12:$C$16, 1))

Прочитайте справа налево. MATCH с третьим аргументом 1 означает «найти наибольшее значение, которое меньше или равно моему, и сообщить мне его положение». Оценка 61,4 очищает 0, 25 и 45, но не 65, поэтому она останавливается на 45, третьей строке. INDEX затем возвращает третье имя группы, Повышенный.

Единственное правило, от которого это зависит: нижние границы должны быть отсортированы по возрастанию. MATCH с 1 ходит по списку, принимая порядок. Вне порядка он уверенно и без ошибки возвращает неправильный ответ, что является худшим видом ошибки.

Выигрыш больше, чем экономия на наборе текста. Когда полоса представляет собой текст в столбце, вы можете сортировать по ней, подсчитывать, фильтровать и поворачивать ее. Это стало данными.

Шаг 4: позвольте цвету следовать за полосой

Выделите всю область данных, столбцы и все остальное, затем добавьте по одному правилу условного форматирования для каждой полосы, используя правило формулы:

=$J6="Severe"

Один знак доллара – это весь механизм. $J блокирует столбец, поэтому в каждой ячейке строки задается один и тот же вопрос. Голый 6 остается относительным, поэтому в строке 7 задается вопрос о $J7, ряд 8 примерно $J8, и так далее вниз. Одно правило окрашивает целую строку, а двадцать строк не требуют дополнительной обработки.

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

Привяжите правило к строке, с которой начинаются ваши данные. Напишите это, сидя в строке 6, и ссылка будет $J6. Напишите это, сидя где-нибудь в другом месте, и каждая строка будет отличаться из-за разницы, которая выглядит так, как будто цвета сдвинулись на один.

Шаг 5: две проверки, которые улавливают остальное

Две формулы на листе правил, на виду, проверяют, о чем никто не помнит.

=IF(ROUND(SUM(B7:E7),4)=1, "Weights balance", "Fix the weights, they must total 100%")

Веса, составляющие в сумме 96%, по-прежнему дают оценку. Он просто незаметно занижает каждую строку, и ничего не выглядит сломанным.

=IF(F17=COUNTA('Risk Index'!$A$6:$A$25), "Every site lands in one band", "Banding gap, check the cut-offs")

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

Оба чека читаются как предложение, а не число. Читатель, который никогда не открывал этот файл, может сказать, исправен ли он, не зная, как он работает.

Шаг 6: журнал изменений

Одна вкладка, три колонки: что нашел, что изменил, почему. Каждое исправление получает строку.

Это вкладка, которая превращает перестройку в нечто, что клиент может одобрить. Без него вы возвращаете файл, который выглядит по-другому, и им придется поверить вам на слово. С его помощью они могут не согласиться с каким-либо отдельным решением, не открывая их все заново.

Один тип записи имеет большее значение, чем другие. Если два исходных файла расходятся во мнениях, вы говорите об этом в журнале изменений, а не тихо выбираете победителя. «Этот сайт появляется на двух картах с двумя оценками, помеченными на ваше решение» — это лучшая фраза, чем любое молчаливое исправление, потому что какой источник побеждает, это вызов политики, а не таблицы.