KutoolsforOffice — Одно решение — пять мощных инструментов.Меньше усилий — больше результата.

Как автоматически изменять размер фигуры в зависимости от значения указанной ячейки в Excel?

АвторSiluviaДата изменения

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

Автоматическое изменение размера фигуры в зависимости от значения указанной ячейки с помощью кода VBA

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

Перемещайтесь по документам с помощью Office Tab

Office Tab

Вкладки для навигации в Word, Excel, PowerPoint и других приложениях — как в веб-браузере: плавное переключение между документами в одном окне.

Бесплатно скачатьПодробнее

Выполните следующие шаги:

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

2. В окне Microsoft Visual Basic for Applications скопируйте и вставьте приведённый ниже код VBA в окно кода. Убедитесь, что вы вставляете его именно на страницу кода того рабочего листа, где расположена ваша фигура.

Код VBA: Автоматическое изменение размера фигуры в зависимости от значения указанной ячейки в Excel

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Row = 2 And Target.Column = 1 Then
        Call SizeCircle("Oval 2", Val(Target.Value))
    End If
End Sub
Sub SizeCircle(Name As String, Diameter)
    Dim xCenterX As Single
    Dim xCenterY As Single
    Dim xCircle As Shape
    Dim xDiameter As Single
    On Error GoTo ExitSub
    xDiameter = Diameter
    If xDiameter > 10 Then xDiameter = 10
    If xDiameter < 1 Then xDiameter = 1
    Set xCircle = ActiveSheet.Shapes(Name)
    With xCircle
        xCenterX = .Left + (.Width / 2)
        xCenterY = .Top + (.Height / 2)
        .Width = Application.CentimetersToPoints(xDiameter)
        .Height = Application.CentimetersToPoints(xDiameter)
        .Left = xCenterX - (.Width / 2)
        .Top = xCenterY - (.Height / 2)
    End With
ExitSub:
End Sub

Пояснение и меры предосторожности:

  • В этом коде «Oval 2»обозначает имя фигуры, размер которой вы хотите изменить. Убедитесь, что оно точно совпадает с именем, отображаемым на панели выбора (Главная > Найти и выделить > Панель выбора).
  • Код срабатывает только при изменении конкретной ячейки A2 (строка 2, столбец 1). Если вы хотите использовать другую ячейку — просто обновите эти значения соответствующим образом.
  • Размер фигуры определяется значением ячейки, которое задаёт диаметр в сантиметрах. В коде установлены пределы размера: верхний — 10 см, и нижний — 1 см, чтобы избежать чрезмерного изменения размеров.
  • Если вы укажете значение вне диапазона (меньше 1 или больше 10), фигура автоматически примет ближайший допустимый размер.
  • Если указанная фигура не найдена или значение не является числом, код проигнорирует действие, не выводя сообщения об ошибке.

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

Код VBA: Автоматическое изменение размера нескольких фигур в зависимости от значений разных указанных ячеек в Excel

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xAddress As String
    On Error Resume Next
    If Target.CountLarge = 1 Then
        xAddress = Target.Address(0, 0)
        If xAddress = "A1" Then
            Call SizeCircle("Oval 1", Val(Target.Value))
        ElseIf xAddress = "A2" Then
            Call SizeCircle("Smiley Face 3", Val(Target.Value))
        ElseIf xAddress = "A3" Then
            Call SizeCircle("Heart 2", Val(Target.Value))
        End If
    End If
End Sub

Sub SizeCircle(Name As String, Diameter)
    Dim xCenterX As Single
    Dim xCenterY As Single
    Dim xCircle As Shape
    Dim xDiameter As Single
    On Error GoTo ExitSub
    xDiameter = Diameter
    If xDiameter > 10 Then xDiameter = 10
    If xDiameter < 1 Then xDiameter = 1
    Set xCircle = ActiveSheet.Shapes(Name)
    With xCircle
        xCenterX = .Left + (.Width / 2)
        xCenterY = .Top + (.Height / 2)
        .Width = Application.CentimetersToPoints(xDiameter)
        .Height = Application.CentimetersToPoints(xDiameter)
        .Left = xCenterX - (.Width / 2)
        .Top = xCenterY - (.Height / 2)
    End With
ExitSub:
End Sub

Важные примечания и советы по использованию:

1) В этом коде используются имена фигур «Oval 1», «Smiley Face 3» и «Heart 2». Они обозначают фигуры, размер которых требуется изменить. Убедитесь, что имена точно совпадают с теми, что отображаются в вашем Рабочая книга Excel.
2) Ячейки A1,A2и A3управляют размерами соответствующих фигур. Вы можете изменить эти ссылки, если хотите использовать другие пары «ячейка–фигура». Чтобы добавить новые фигуры и ячейки, просто добавьте аналогичные строки в раздел If...ElseIfперед оператором End If. Укажите правильный адрес ячейки и имя фигуры по мере необходимости.

3. После внесения изменений и сохранения кода нажмите Alt+Q, чтобы закрыть окно Microsoft Visual Basic for Applications и вернуться к рабочему листу.

Теперь при каждом изменении значения в ячейке A2 фигура «Oval2» будет автоматически изменять свой размер в режиме реального времени. См. скриншот:

измените значение в определённой ячейке, и размер фигуры автоматически изменится

Аналогично, при обновлении значений в ячейках A1, A2 или A3 фигуры «Oval 1», «Smiley Face 3» и «Heart 2» мгновенно изменят свои размеры в соответствии со значениями связанных ячеек. См. пример ниже:

измените значения в определённых ячейках, и размеры фигур автоматически изменятся

Дополнительные напоминания:
– Изменение размера фигуры происходит только при изменении значений в указанных ячейках. Если вы вручную переместите фигуру или измените её так, что будет нарушен её опорный пункт, может потребоваться сбросить положение фигуры.
– Работать будут только числовые значения. Пустые ячейки или текстовые данные могут привести к уменьшению фигуры до минимального размера или вызвать непредсказуемое поведение.
– Убедитесь, что макросы включены в вашей среде Excel: решения на основе VBA требуют разрешения на выполнение макросов.

Примечание: как только значение ячейки превышает 10, увеличение размера фигуры прекращается (максимум установлен на 10 см). Аналогично, значения ниже 1 автоматически устанавливаются на минимальный размер (1 см).

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

Рекомендация по итогам:Метод на основе VBA обеспечивает динамическое изменение размера в режиме реального времени и идеально подходит для интерактивных панелей мониторинга и отчётов, управляемых данными. Однако он требует от пользователя понимания принципов именования фигур, работы со средой VBA и сохранения книги в формате, поддерживающем макросы ().xlsm), чтобы код остался рабочим. Этот метод наиболее подходит для ситуаций, где необходима автоматизация и допустимо использование макросов.

Ручное изменение размера фигуры с использованием эталонного значения

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

Щёлкните фигуру, размер которой нужно изменить, затем в области Формат фигуры (щёлкните правой кнопкой мыши по фигуре → Размер и свойства) вручную задайте нужные значения ширины и высоты. В качестве ориентира можно использовать размеры ячейки. Хотя этот способ не автоматический, он прост и эффективен для разовых корректировок.

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

Список и экспорт всех фигур в текущем Рабочая книга Excel:

Утилита Экспорт диаграммы из Kutools для Excel позволяет быстро получить список всех фигур в текущей книге и экспортировать их все сразу в указанную папку, как показано на скриншоте ниже. Скачайте и попробуйте прямо сейчас! (30-дневная бесплатная пробная версия)

экспортируйте все фигуры в текущем файле Excel с помощью Kutools

Связанные статьи:

Лучшие инструменты повышения продуктивности в Office

🤖KUTOOLS AI Помощник: Преобразуйте Анализ данных с помощью:Интеллектуального выполнения   |  Генерации кода|  Создания пользовательские формулы  |  Анализа данных и построения диаграмм|  Вызова Расширенные функции…
Популярные функции:Поиск, выделение или Отметить дубликаты   |  Удалить пустые строки   |  Объединить столбцы или ячеек без потери данных   |  Округление без использования формул…
Супер ПОИСК:VLookup по нескольким критериям  |  VLookup по нескольким значениям  |   VLookup по нескольким листам   |   Распознавание нечетких соответствий…
Расширенный раскрывающийся список:Быстрое создание выпадающего списка   |  Зависимый выпадающий список   |  Выпадающий список с множественным выбором…
Управление столбцами:Добавление заданного количества столбцов|Перемещение столбцов|Переключение видимости скрытых столбцов|Сравнение диапазонов и столбцов…
Избранные функции:Сетка фокусировки   |  Просмотр дизайна   |Улучшенная строка формулы   | Управление рабочими книгами и листами   |  Библиотека ресурсов(автотекст)|  Выбор даты   |  Объединить листы  |  Шифрование/Расшифровать ячейки   | Отправка писем по списку   |  Супер фильтр   |   Специальный фильтр(Фильтр ячеек с жирным шрифтом/курсив/зачёркивание…) …
Лучшие наборы инструментов 15:12 Текстовыеинструменты(Добавить текст,Удалить определенные символы, …)|   50+Типыдиаграмм(Диаграмма Ганта, …)|   40+ Практические формулы(Рассчитать возраст на основе даты рождения, …)|   19 Инструментывставки(Вставить QR-код,Вставка изображения по пути, …)|   12 Инструментыпреобразования(Преобразовать в слова,Конвертация валют, …)|   7 Объединить и разделитьИнструменты(Расширенное объединение строк,Разделить ячейки, …)|… и многое другое
Используйте Kutools на предпочитаемом языке — поддержка английского, испанского, немецкого, французского, китайского и ещё 40+ языков!

Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…

Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу

  • Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
  • Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
  • Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!

Все надстройки Kutools — один установщик

Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.

ExcelWordOutlookTabsPowerPoint
  • Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
  • Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
  • Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
  • 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
  • Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек