График-талли в Excel: создать с помощью формул

Быстрые ссылки

• Создать систему талли

• Суммировать группы по пять

• Сосчитать остаточные одиночные отметки

• Создать график-талли формулой

• Скрыть вспомогательные столбцы

График-талли — это таблица с визуальными отметками (талли), показывающая, как часто что-то происходило. В Excel доступно много встроенных типов диаграмм, но опции “tally” нет. К счастью, её можно создать с помощью формул.

Для примера мы визуализируем, сколько голосов получил каждый человек в списке.

Создать систему талли

Обычно талли изображаются как четыре вертикальные черты и диагональная зачёркивающая пятая черта — это удобная визуальная группировка по 5.

В Excel воспроизвести диагональную зачёркнутую пятую черту трудно, поэтому можно использовать текстовую замену: четыре вертикальные линии (символ pipe) и затем дефис. На клавиатуре US/UK символ pipe — это вертикальная черта над обратным слэшем.

Итак, каждая группа из пяти будет представлена как:

||||-

А одиночная отметка (1) будет просто:

|

Впишите эти символы в ячейки D1 и E1 на листе.

Мы будем собирать график-талли с помощью формул, ссылаясь на эти две ячейки, чтобы отображать правильное количество символов.

Суммировать группы по пять

Чтобы посчитать число полных групп по 5, округлим значение голосов вниз до ближайшего кратного 5, затем разделим результат на 5. Для округления используем функцию FLOOR.MATH.

В ячейке D3 введите формулу:

=FLOOR.MATH(C3,5)/5

Эта формула округляет значение в C3 (например, 23) вниз до ближайшего кратного 5 (20), а затем делит на 5 — получается 4 полные группы.

Сосчитать остаточные одиночные отметки

Теперь нужно вычислить остаток после деления на 5 — именно столько одиночных черт нужно отобразить. Для этого используем функцию MOD, которая возвращает остаток от деления.

В ячейке E3 введите формулу:

=MOD(C3,5)

Создать график-талли формулой

Теперь у нас есть количество групп по 5 и количество одиночных отметок. Остаётся составить строку символов. Для этого используем функцию REPT, которая повторяет текст заданное число раз, и соединяем части с помощью амперсанда (&).

В ячейке F3 введите формулу:

=REPT($D$1,D3)&REPT($E$1,E3)

REPT повторяет текст указанное количество раз. Здесь мы повторяем символы талли столько раз, сколько указано в D3 и E3, а затем объединяем полученные фрагменты.

Важно: если значения в C3 отрицательные или нечисловые, формулы могут вернуть ошибку или неожиданный результат. Убедитесь, что столбец голосов содержит целые неотрицательные числа.

Скрыть вспомогательные столбцы

Чтобы окончательно оформить график, спрячьте вспомогательные столбцы D и E.

Выделите столбцы D и E, щёлкните правой кнопкой мыши и выберите “Скрыть”.

Готовый график-талли даёт понятное визуальное представление о количестве голосов у каждого человека.

Когда этот подход пригоден и когда нет

• Подходит, если нужно быстро показать частоту для небольших наборов данных и добавить «теплую» ручную визуализацию в таблицу.
• Не подходит для больших данных (сотни/тысячи строк) — текстовые метки займут много места и снизят читаемость.
• Не подходит, если требуется динамическая диаграмма с масштабированием и интерактивностью — лучше стандартные диаграммы Excel.

Альтернативные подходы

• Использовать стандартную гистограмму или столбчатую диаграмму Excel для больших наборов данных.
• Применить условное форматирование с набором значков для компактного визуального представления.
• Построить пользовательский шрифт или символы Wingdings/Unicode, чтобы визуально приблизиться к перечёркиваемой пятёрке (требует больше подготовки).

Мини-методология (пошагово)

1. Поместите символы групп и одиночек в D1 и E1.
2. В D3 рассчитывайте число полных групп: =FLOOR.MATH(C3,5)/5.
3. В E3 рассчитывайте остаток: =MOD(C3,5).
4. В F3 собирайте итоговую строку: =REPT($D$1,D3)&REPT($E$1,E3).
5. Протяните формулы вниз по ряду и скройте столбцы D и E.

Критерии приёмки

• Для каждой записи в столбце голосов отображается строка талли в столбце F.
• Количество символов соответствует сумме групп по 5 и остатка.
• Формулы корректно работают при целых неотрицательных входных данных.

Короткий словарь

• Tally (талли) — текстовое представление подсчёта в виде групп по 5.
• FLOOR.MATH — округление вниз до кратного числа.
• MOD — остаток от деления.
• REPT — повторение строки заданное число раз.

Чеклист для ролей

• Аналитик: проверить данные на целые неотрицательные значения.
• Автор отчёта: настроить D1 и E1 с нужными символами и стиль шрифта.
• Пользователь: проверить отображение в финальной версии (шрифт моноширинный улучшает выравнивание).

Важно: для лучшей ровности отображения используйте моноширинный шрифт (например, Consolas или Courier New) в столбце F — тогда группы визуально выравниваются по ширине.

Итог

График-талли в Excel просто реализуется через комбинацию FLOOR.MATH, MOD и REPT. Этот приём полезен для компактной «ручной» визуализации частот, когда стандартные диаграммы не нужны или вы хотите стилизованный табличный вывод.

Краткие выводы:

• Подходит для небольших табличных представлений.
• Лёгко настраивается и локализуется.
• Для больших наборов данных лучше использовать стандартные диаграммы.