000818.ru

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

Как отдельно ранжировать положительные и отрицательные числа в Excel?

Как создать диаграмму с интерактивными подписями данных в Excel

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

Как создать диаграмму с интерактивными подписями данных в Excel

  • Какова общая сумма ряда?
  • Какова доля каждого сегмента в ряду?
  • Каково процентное изменение по сравнению с предыдущим периодом?

Динамические подписи данных на гистограмме

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

Как создать график с динамическими подписями данных

  • Функция ТЕКСТ
  • Функция ВЫБОР
  • Сводная таблица
  • Разрез
  • Гистограмма

Скачайте файл в качестве примера, чтобы следовать моим объяснениям. Примечание: этот файл корректно работает в Excel 2013 или 2016.

Шаг 1: Создайте гистограмму с итоговыми данными

Как создать диаграмму с интерактивными подписями данных в Excel

Шаг 2: Расчет значений для подписей данных

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

Как создать диаграмму с интерактивными подписями данных в Excel

Я создал раздел на листе для каждого измерения: Сумма, % от общего, и % изменения.
Это довольно легко, и я не буду вдаваться в детали каждого расчета.

Шаг 3: Используйте формулу ТЕКСТ для формирования подписей

Как правило, диаграмма представляет операции над данными, основанные на основном источнике диаграммы.

В Excel 2013 появилась новая функция под названием «Значения из ячеек». С помощью этой функции вы можете указать диапазон, который вы хотите использовать для подписи данных.

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

Учитывая, что наши подписи данных меняются между финансовым ($) и процентным (%) форматами, нам также необходимо иметь возможность менять способ отображения чисел на графике. Если этого не сделать, вместо десятичного числа будет отображаться процент.

К счастью, мы можем использовать для этого функцию TEXT. Функция ТЕКСТ позволяет взять число и указать формат, в котором оно должно отображаться.

Как создать диаграмму с интерактивными подписями данных в Excel

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

Шаг 4: Использование функции ВЫБОР

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

Каждый раздел содержит различные метрики. Далее мы создадим еще один раздел, состоящий из информации о подписях данных на гистограмме.

Нам потребуется использовать функцию поиска, чтобы вернуть правильную метрику выбора отсечения. Для этого отлично подходит функция SELECT.

Мы можем указать индекс (1,2,3) с помощью функции SELECT. и она вернет значение, соответствующее индексу.

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

Как создать диаграмму с интерактивными подписями данных в Excel

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

Шаг 5: Настройка подписей данных

Следующим шагом является изменение подписей данных, чтобы они отображали значения из ячеек, сформированных нами при помощи формулы ВЫБОР.
Как я говорил ранее, мы будем использовать функцию «Значения из ячеек», которая есть в Excel 2013 или 2016, что бы сделать это проще.
Вам нужно выбрать на графике ряд подписей, затем нажать на кнопку «Выбрать диапазон» в меню Параметры подписи.

Как создать диаграмму с интерактивными подписями данных в Excel

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

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

Как создать диаграмму с интерактивными подписями данных в Excel

Повторите этот шаг для каждой строки гистограммы.

Шаг 6: Настройка сводной таблицы и среза

И последнее, но не менее важное: мы сделаем наши подписи данных интерактивными. Мы сделаем это с помощью сводной таблицы и среза. Исходными данными для сводной таблицы является таблица на рисунке ниже.

Как создать диаграмму с интерактивными подписями данных в Excel

Эта таблица содержит три варианта для различных подписей данных.
Она также включает в себя номер индекса, на который будет ссылаться формула ВЫБОР (см. шаг 4).
Создайте сводную таблицу. Добавьте Name, Index и Symbol в строки сводной таблицы.

Как создать диаграмму с интерактивными подписями данных в Excel

Поместите срез в поле Символ. Для этого щелкните по перекрестной таблице, перейдите в меню Конструктор, а затем выберите Вставить срез.

Как создать диаграмму с интерактивными подписями данных в Excel

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

Как создать диаграмму с интерактивными подписями данных в Excel

Последним шагом будет ссылка на значение индекса из перекрестной таблицы в ячейке C38, чтобы формула SELECT работала правильно. В ячейке C38 будет отображаться индекс выбранного элемента сводной таблицы, когда пользователь нажмет кнопку «Вырезать».

Для каждого среза формулы SELECT автоматически отображают подписи данных.

Эта интерактивная гистограмма — результат нескольких шагов. Есть пример, который вы можете загрузить и изменить самостоятельно.

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

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

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

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

Например:[зеленый][>200]_-$###0,00;[красный][<0]-$# ##0,00

Excel будет отображать суммы больше 200 зеленым цветом, а отрицательные числа — красным.

Другие варианты получения абсолютного значения

Помимо ABS, существуют и другие способы получения модуля числа в Excel с помощью альтернативных функций.

Функция ЗНАК

Он основан на том, что отрицательные числа умножаются на -1, а положительные — на 1. Выглядит это следующим образом:

На рисунке действие выполнено в ячейке A4.

КОРЕНЬ

Функция КОРЕНЬ

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

Функция ЕСЛИ

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

Язык VBA

Во многих языках программирования этот модуль обозначается через ABS. В VBA команда будет выглядеть следующим образом: A=Abs(-7). Здесь Abs — команда для получения абсолютного значения. В этом случае объекту A будет присвоен номер 7.

ABS и альтернативные варианты просты в применении. Знание того, как их применять, облегчит вашу работу и сэкономит время.

Показывать отрицательные числа красным цветом — использование условного форматирования

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

Предположим, что у вас есть набор данных, подобный приведенному ниже:

Как видите, нужно приложить определенные усилия, чтобы перебрать числа и определить отрицательные.

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

  • Выберите ячейки, в которых вы хотите выделить отрицательные числа
  • Выберите в меню опцию Формат.
  • Щелкните Условное форматирование. Откроется панель «Правила условного форматирования» справа.
  • Щелкните раскрывающееся меню «Форматировать ячейки, если».
  • Нажмите на опцию «Меньше чем» (возможно, вам придется немного прокрутить вниз, чтобы увидеть это).
  • В поле «Значение или формула» введите 0.
  • Установите цвет, которым вы хотите выделить ячейку
  • Нажмите Готово
Читайте так же:
Как объединить столбцы, но не объединить строки в Excel?

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

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

В качестве альтернативы изменению цвета всей ячейки, вы можете просто сделать шрифт значения ячейки (которое имеет отрицательное число) красным. Изменив только цвет текста в шаге 7, вы сможете добиться этого.

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

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

Использование изображений в столбчатых диаграммах

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

Щелкните столбец в столбчатой диаграмме.

Нажмите Заполнить в разделе Формат серии данных.

Раздел «Вставить изображение из» должен содержать имя файла или, при необходимости, буфер обмена, если изображение было скопировано раньше.

Нажмите на столбец гистограммы.

В наборе данных Формат нажмите кнопку Заполнить.

В разделе «Вставить изображение из» введите имя файла или, при необходимости, буфер обмена, если вы ранее уже копировали изображение.

Читайте так же:
Как объединить две или более таблиц в одну на основе ключевых столбцов?

Использование картинок

Выбранное изображение отображается вместо полей на графике.

Фотографии мест

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