Evolcom.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Добавление микрографиков в дашборд

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

Решение

дашборд с микрографиками

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

Реализация

  • Вам необходимо подготовить рабочую книгу к новым возможностям, добавив пять дополнительных столбцов в каждую колонку KPI, две строки на лист данных для установки целевых значений и новые столбцы на лист расчетов.
  • Добавьте пять графиков с обычным форматированием на лист приборной панели. Подробнее о технике создания графиков обычного формата вы можете прочитать в статье. Используйте графики, созданные на листе «Расчеты», в качестве источников данных, а целевое значение — в качестве границы, определяющей цвет заливки столбцов (зеленый — выше цели, красный — ниже цели).
  • Определите среднее значение в рабочем листе и добавьте линию со средним значением на графики с помощью точечной диаграммы. Расчеты, необходимые для шагов 2 и 3, вы найдете в таблицах в столбцах P:AP электронной таблицы.
  • Форматируйте графики так, чтобы были видны только столбцы и линия со средним значением, никаких осей, никаких линий сетки, никаких имен, никаких подписей, в общем, как говорил Эйнштейн: «Делайте все как можно проще, но не проще».
  • Настройте графики в соответствии с диапазоном ячеек. Совет: при изменении размера графика удерживайте нажатой клавишу ALT; это позволит вам установить размер ячейки под графиком в качестве шага изменения размера. Это упрощает процесс позиционирования.
Читайте так же:
Как переименовать все изображения в папке в соответствии со списком ячеек в Excel?

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

  • Обновление легенд данных таблицы для пояснения значения цветов строк и столбцов

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

В следующей статье мы добавим возможность сравнения двух параметров с помощью элементов управления формой.

Вам также могут быть интересны следующие статьи

9 комментариев

Ренат, вы большой молодец! Пишите еще и почаще. У вас очень много всего полезного и интересного.
Единственный вопрос: в файле из данной статьи при прокрутке таблицы столбцы диаграмм не изменяются (они прикреплены только к первым десяти значениям). Нужно как-то усложнить формулу с использованием функции смещ в привязке к позиции ползунка.

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

Полезная информация, спасибо, только я не понял: как задать формат, чтобы график в таблице содержал только зеленый и красный столбцы, у меня в каждой ячейке отражался маленький график со средним значением? !

Если я правильно понял вас, Вячеслав, вам необходимо изменить вид графика со средним значением. Для этого выбираете правым щелчком мыши ряд данных со средним значением -> из выпадающего меню выбираете Изменить тип диаграммы для ряда -> в появившемся меню выбираете тип — график

Читайте так же:
Как показать или скрыть формулы в ячейках указанного диапазона / активного листа / всех листов в Excel?

Динамический выбор серии на диаграмме Excel

У меня есть схема, по которой я могу построить множество серий. По какой-то причине таких серий слишком много, поэтому я хочу описать лишь некоторые из них. Я могу сделать это вручную «статическим» способом, дублируя таблицу и удаляя ненужные строки, но я бы предпочел что-то более динамичное, поскольку новые серии появляются постоянно.

Обычно я хочу рисовать только те серии, которые достигают определенного порога или ранга. Я могу проверить условие и вернуть результат, например, 0 или 1.

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

4 ответа

Другой метод предполагает использование таблицы и ее фильтров. Вот исходная таблица, названия серий в первом столбце, категории в первой строке, последний столбец содержит формулу, которая проверяет значения и возвращает true или false.

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

Unfiltered table and chart

Щелкните раскрывающийся список Автофильтр в заголовке тестовой колонки и в разделе Числовые фильтры выберите Равно и введите в поле Истина.

Filtered table and chart

Ваша тестовая формула не обязательно должна возвращать true или false, она может просто вычислять значение функции или что-то еще. Просто выберите соответствующее условие фильтрации, чтобы скрыть то, что вы не хотите показывать на диаграмме.

Использование динамических диапазонов очень хорошо работает с диаграммами. Существует два типичных варианта применения

Использование динамических диапазонов, когда диапазон известен, но длина набора данных для диапазона переменна. FunChrt1.zip Стивена Буллена является отличным примером этой техники. VBA не требуется

Необходимо знать некоторые основы VBA при переменном количестве серий, поскольку, когда динамический диапазон назначается в качестве диапазона исходных данных, он становится жестко закодированным (вместо конкретного имени для серии графиков, которое остается «живым»). Это похоже на ваш подход к запросу. Джон Пелтье подробно останавливается на этом в диаграммах динамических исходных данных

Читайте так же:
Как подсчитать ячейки с определенным текстом и цветом заливки / шрифта в Excel?

Этот метод использует формулы, требующие немного больше данных, однако он не требует VBA.

Предположим, что в диапазоне A находятся ваши данные. Поместите в строку над (или под) этим диапазоном формулу, которая определяет, соответствует ли диапазон критериям.

Создайте диапазон B того же размера, что и A, с формулами типа: = IF (A1, A2, na ()), где A1 — тестовая ячейка для данного столбца данных (т.е. для данного столбца графика). Если A1 истинно, A2 появляется в этой ячейке, если нет, # N / A.

Используйте диапазон B для построения диаграммы. Серии с # N/A не будут построены.

Чтобы избежать путаницы с легендой, создайте график с областью B, но временно установите для всех тестовых ячеек значение true, чтобы отображались все серии. Затем добавьте метку данных в последнюю точку каждой серии (см. Обновление наложения метки последней точки) и выключите легенду. Теперь напишите соответствующие тестовые шаблоны.

Еще один выбор, следуя по стопам последнего сообщения здесь, потому что у меня была такая же проблема.

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

Область «Визуализация» в правой части графика. Начало ячейки определяется на одну ячейку выше столбца соответствующей серии в диапазоне A. Диапазон A имеет динамический диапазон для определения дат, которые идут вниз слева от диапазона A, используя формулы DMIN и DMAX.

Чтобы избежать проблемы макросортировки, у меня есть второй диапазон, диапазон B является динамическим, потому что первый столбец берет первый «TRUE» из диапазона A — определенный с помощью соответствующего флажка. Все соответствующие данные первого TRUE, включая название серии, отображаются под ним. Диапазон дат просто связан с диапазоном A.

Читайте так же:
Как предотвратить ввод строчных букв в выделении Excel?

Теперь с помощью формулы SHIFT для диапазона диаграммы, начиная с диапазона B, у меня есть динамическое количество серий, с диапазоном диаграммы, заданным диапазоном A, проходящих через диапазон B к диаграмме

При использовании 61 серии я могу запутаться, когда пользователь выбирает «Все» и видит еженедельные даты за 3 года, но на обновление всего этого уходит не более 5 секунд, что ниже моего пользовательского порога в 6 секунд.

Хитрость заключалась в том, чтобы найти и отобразить серии с меткой TRUE по порядку. Поскольку мои серии фактически сгруппированы, я хотел бы сохранить их порядок. Формулы ARRAY замедлили мой расчет до 9 секунд, но вложенный IF, пропускающий ряд Range A TRUE, оказался быстрее, до 5 секунд. Есть причины медленного вычисления, которые здесь не применимы, но вложенный IF был быстрее, чем ARRAY.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector