График изменения данных в соответствии с выбранной ячейкой
Если вы хотите создать панель мониторинга с диаграммой, которая изменяет свои данные в соответствии с выбранными параметрами, вы можете использовать события в VBA. Да, это может быть сделано. Нам не понадобится ни раскрывающийся список, ни слайсер, ни поле со списком. Мы сделаем ячейки кликабельными и изменим данные для создания диаграммы из выбранной ячейки.
Выполните следующие шаги, чтобы создать динамические диаграммы в Excel, которые изменяются в соответствии с выбором ячейки.
Шаг 1: Подготовьте данные на листе в качестве источника для диаграммы.
Здесь у меня есть образцы данных из разных регионов на листе. Я назвал это исходными данными.
Шаг 2: Получите данные по одному региону за один раз на другом листе.
-
Теперь вставьте новый лист. Назовите это соответствующим образом. Я назвал его «Панель управления».
-
Скопируйте все месяцы в один столбец. Напишите название одного региона рядом с месяцем.
-
Теперь мы хотим получить данные о регионе в ячейке D1. Мы хотим, чтобы данные менялись при изменении региона в D1. Для этого мы можем использовать
link: / lookup-formulas-vlookup-with-dynamic-col-index [Two Way Lookup].
Поскольку мои исходные данные находятся в A2: D8 в таблице исходных данных. Я использую приведенную ниже формулу.
|
= Data’!$A$2:$D$8, Data’!$A$1:$D$1,0)) |
Здесь мы используем динамическую индексацию столбцов для VLOOKUP. Вы можете прочитать об этом link: / lookup-formulas-vlookup-with-dynamic-col-index [здесь].
-
Вставьте диаграмму, используя эти данные на листе Dashboard. Я использую простой линейный график. Скройте источник диаграммы, если не хотите его отображать.
Теперь, когда вы измените название региона в D1, диаграмма изменится соответствующим образом. Следующим шагом является изменение имени региона в D1 при выборе параметра в указанной ячейке.
Шаг 3. Измените регион при выборе имени региона в указанном диапазоне. * Пишите названия всех регионов в диапазоне, я пишу их в диапазоне A2: A4.
-
Щелкните правой кнопкой мыши имя листа панели инструментов и выберите «Просмотреть код»
возможность войти непосредственно в модуль рабочего листа в VBE, чтобы мы могли использовать ссылку: / events-in-vba-the-worksheet-events-in-excel-vba [событие рабочего листа] `.
-
Теперь напишите ниже код в редакторе VB.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A2:A4")) Is Nothing Then
Range("A2:A4").Interior.ColorIndex = xlColorIndexNone
Dim region As Variant
region = Target.value
On Error GoTo err:
Select Case region
Case Is = "Central"
Range("D1").value = region
Case Is = "East"
Range("D1").value = region
Case Is = "West"
Range("D1").value = region
Case Else
MsgBox "Invalid Option"
End Select
Target.Interior.ColorIndex = 8
End If
err:
End Sub
И это сделано. Теперь, когда вы выбираете ячейку в диапазоне A2: A4, ее значение будет присвоено D1, и данные диаграммы будут соответственно изменяться.
Я объяснил, как работает этот код ниже. Вы можете понять это и внести изменения в соответствии с вашими требованиями. Я предоставил ссылки на разделы справки, которые я использовал здесь в этом примере. Так что проверьте их.
Как работает код?
Здесь я использовал ссылку: / events-in-vba-the-events-in-excel-vba [Событие Excel] `. Я использовал ссылку `: / events-in-vba-the-worksheet-events-in-excel-vba [событие рабочего листа]« SelectionChange »для запуска событий.
If Not Intersect(Target, Range("A2:A4")) Is Nothing Then
Эта строка устанавливает фокус на диапазон A2: A4, так что событие SelectionChange срабатывает только тогда, когда выделение находится в диапазоне A2: A4. Код между If и End будет выполняться, только если выбор находится в диапазоне A2: A4. Теперь вы можете настроить его в соответствии с вашими требованиями, чтобы сделать диаграмму динамической.
Range("A2:A4").Interior.ColorIndex = xlColorIndexNone
Эта строка устанавливает нулевой цвет диапазона A2: A4.
region = Target.value On Error GoTo err:
В двух приведенных выше строках мы получаем значение выбранных ячеек в переменной области и игнорируем любые возникающие ошибки. не используйте строку «On Error GoTo err:», пока не убедитесь, что хотите игнорировать любую возникающую ошибку. Я использовал его, чтобы избежать ошибки при выборе нескольких ячеек.
Select Case region
Case Is = "Central"
Range("D1").value = region
Case Is = "East"
Range("D1").value = region
Case Is = "West"
Range("D1").value = region
Case Else
MsgBox "Invalid Option"
End Select
В приведенных выше строках мы используем ссылку на excels: / general-themes-in-vba-vba-select-case-alternate-of-nested-if-else-if-applications [Select Case Statement] `для установки значения диапазона D1.
Target.Interior.ColorIndex = 8 End If err: End Sub
Перед оператором End If мы меняем цвет выбранной опции, чтобы она была выделена. Затем оператор If завершается и начинается тег err :.
Оператор On Error перейдет к этому тегу, если во время выполнения оператора select возникнет какая-либо ошибка.
Загрузите рабочий файл ниже.
Статьи по теме:
link: / events-in-vba-chart-object-events-using-vba-in-microsoft-excel [События встроенной диаграммы с использованием VBA в Microsoft Excel] * | События встроенных диаграмм могут сделать вашу диаграмму более интерактивной, динамичной и полезной, чем обычные диаграммы. Для включения событий на графиках мы …
link: / events-in-vba-the-events-in-excel-vba [События в Excel VBA] | * В Excel существует семь типов событий. Каждое событие имеет разный характер. Событие приложения работает на уровне книги. Рабочая тетрадь на уровне листов. Событие рабочего листа на уровне диапазона.
link: / events-in-vba-the-worksheet-events-in-excel-vba [События рабочего листа в Excel VBA] * | Событие рабочего листа действительно полезно, когда вы хотите, чтобы ваши макросы запускались, когда указанное событие происходит в Лист.
link: / events-in-vba-workbook-events-using-vba-in-microsoft-excel [События книги с использованием VBA в Microsoft Excel] | События книги работают со всей книгой. Поскольку все листы являются частью рабочей книги, эти события работают и с ними.
link: / events-in-vba-prevent-that-an-automacroeventmacro-executes-using-vba-in-microsoft-excel [Предотвратить выполнение automacro / eventmacro с использованием VBA в Microsoft Excel] * | Чтобы предотвратить запуск макроса auto_open, используйте клавишу Shift.
link: / events-in-vba-chart-object-events-using-vba-in-microsoft-excel [События объекта диаграммы с использованием VBA в Microsoft Excel] * | Диаграммы — это сложные объекты, к которым вы прикрепляете несколько компонентов. Для создания событий диаграммы мы используем модуль Class.
Популярные статьи:
link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-повышения-продуктивность [50 сочетаний клавиш Excel для повышения производительности] | Выполняйте свою задачу быстрее. Эти 50 ярлыков сделают вашу работу в Excel еще быстрее.
link: / формулы-и-функции-введение-функции-vlookup [Функция ВПР в Excel] | Это одна из наиболее используемых и популярных функций Excel, которая используется для поиска значений из разных диапазонов и листов.
link: / tips-countif-in-microsoft-excel [COUNTIF в Excel 2016] | Подсчитайте значения с условиями, используя эту удивительную функцию. Вам не нужно фильтровать данные для подсчета определенного значения.
Функция Countif важна для подготовки вашей приборной панели.
link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel] | Это еще одна важная функция приборной панели. Это поможет вам суммировать значения для конкретных условий.