Microsoft Excel

трюкиприёмырешения

Как создать текстовую диаграмму в диапазоне ячеек таблицы Excel

В статье Визуализация данных в Excel при условном форматировании приводится пример гистограммы как настройки условного форматирования. Эта настройка позволяет вам создавать простые гистограммы непосредственно в диапазоне ячеек. Функциональность гистограмм достаточно простая, но если вы работаете с книгой совместно с другими пользователями, у которых установлена версия, предшествующая Excel 2007, они не увидят их.

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

Рис. 133.1. Гистограмма, созданная непосредственно в диапазоне ячеек

Рис. 133.1. Гистограмма, созданная непосредственно в диапазоне ячеек

Столбец D содержит формулы, использующие достаточно редкую функцию ПОВТОР, которая повторяет текстовую строку заданное количество раз. Например, следующая формула отображает пять звездочек:
=ПОВТОР("*":5).

В примере, показанном на рис. 133.1, ячейка D3 содержит эту формулу, которая была скопирована вниз по столбцу:
=ПОВТОР("*";C3/2).

Обратите внимание на то, что формула делит значения в столбце С на 2. Это способ масштабирования графика. Вместо 60 звездочек ячейка отобразит 30. Для повышения точности можно использовать функцию ОКРУГЛ:
=ПОВТОР("*";ОКРУГЛ(C3/2;0)).

Без функции ОКРУГЛ формула урезает результат деления (игнорирует десятичную часть аргумента). Например, значение 67 в столбце С выводит 33 символа в столбце D. Функция ОКРУГЛ округляет результат до 34 символов.

Вы можете использовать этот тип графического отображения вместо гистограммы. Если вам не нужна предельная точность (из-за ошибок округления), этот тип не-диаграммы может отвечать всем требованиям.

На рис. 133.2 приведены другие примеры с использованием различных символов и шрифтов. Диаграмма, показывающая сплошные полосы (начиная со строки 39), применяет символ вертикальной черты шрифта Symbol. Для большинства клавиатур этот символ генерируется при нажатии Shift+ (обратная косая черта). Формула в ячейке D39 будет следующей:
=ПОВТОР("|";С39/2000).

Рис. 133.2. Примеры построения диаграмм в ячейках с помощью функции ПОВТОР

Рис. 133.2. Примеры построения диаграмм в ячейках с помощью функции ПОВТОР

В примере на рис. 133.3 задействованы формулы в столбцах F и Н для графического изображения отклонений от месячного бюджета с помощью последовательности символов. Вы можете легко увидеть, какие реальные показатели превышают значение бюджета или не доходят до него. Эта псевдодиаграмма использует символ n, который выглядит как небольшой квадрат в шрифте Wingdings.

Используются следующие формулы:
F3: =ЕСЛИ(D3<0;ПОВТОР("n";-ОКРУГЛ(D3*100;0));"")
G3: =A3
Н3: =ЕСЛИ(D3>0;ПОВТОР("n";-ОКРУГЛ(D3*-100;0));"")

Рис. 133.3. Отображение отклонений от месячного бюджета с помощью функции ПОВТОР

Рис. 133.3. Отображение отклонений от месячного бюджета с помощью функции ПОВТОР

Для этого примера нужно выполнить следующие шаги, что позволит создать гистограмму после ввода предыдущих формул.

  1. Назначьте шрифт Wingdings ячейкам F3 и Н3.
  2. Скопируйте формулы вниз по столбцам F, G и Н, чтобы согласовать все данные.
  3. Выровняйте по правому краю текст в столбце Е и настройте любые другие параметры форматирования.

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

Top