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

Например, если кто-то вводит 200 в одну из этих ячеек, фактически вводится 240.

Есть множество способов подойти к этой задаче с помощью макросов.

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

В следующем примере изменяется введенное значение, если оно было внесено в любую из трех ячеек: A1, C3 или B8.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rInput As Range     Dim rInt As Range     Dim rCell As Range

'change the input cell range as desired     Set rInput = Range("A1, C3, B8")



Set rInt = Intersect(Target, rInput)

If Not rInt Is Nothing Then         For Each rCell In rInt             If IsNumeric(rCell) Then                 With Application                     .EnableEvents = False                     rCell = rCell * 1.2                     .EnableEvents = True                 End With             End If         Next     End If End Sub

Помните, что это обработчик событий, что означает, что он запускается (в данном случае) всякий раз, когда что-то изменяется на листе. Чтобы использовать этот макрос, щелкните правой кнопкой мыши вкладку рабочего листа и выберите «Просмотреть код» в появившемся контекстном меню. Excel отображает редактор VB, и вы можете добавить код Worksheet_Change.

Обратите внимание, что ключ к выяснению того, было ли внесено изменение в одну из трех определенных входных ячеек, — это функция Intersect. Он проверяет, есть ли пересечение между целевым диапазоном (измененные ячейки, которые запустили обработчик Worksheet_Change) и диапазоном rInput (ваши входные ячейки). Если есть, то rInt будет содержать пересекающиеся ячейки.

Затем макрос проходит по этим ячейкам и, если ячейки содержат числовые значения, умножает эти ячейки на 120%. (Умножение на 120% равносильно увеличению значения на 20%.) Обратите внимание, что для свойства .EnableEvents установлено значение False, когда умножение выполнено; если бы эта защита не была принята, то каждое умножение снова запускало бы этот обработчик событий, и вы бы многократно (и навсегда) умножали бы значение ячейки на 120%.

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

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

Чтобы использовать этот подход, предположим, что ваш диапазон входных ячеек — B7: B19. Выделите эти ячейки и затем, используя поле имени в верхнем левом углу области ячеек, введите имя «plus20pct». Это действие присваивает диапазону имя. Затем вы можете использовать это имя в макросе.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rInt As Range     Dim rCell As Range

'change the input cell range as desired

Set rInt = Intersect(Target, Range("plus20pct"))

If Not rInt Is Nothing Then         For Each rCell In rInt             If IsNumeric(rCell) Then                 With Application                     .EnableEvents = False                     rCell = rCell * 1.2                     .EnableEvents = True                 End With             End If         Next     End If End Sub

Обратите внимание, что единственное изменение заключается в способе определения пересечения ячеек — функция Intersect использует диапазон «plus20pct» в качестве параметра. В остальном все работает как раньше.

Теперь, когда вы увидели, как это сделать с помощью макросов, остается вопрос, действительно ли вам следует делать это с помощью макросов. Во-первых, есть ограничения на то, что могут делать эти макросы. Например, что, если ваш пользователь вводит дату или время в одну из ячеек ввода? На внутреннем уровне Excel обрабатывает дату и время как числа, а это значит, что они тоже будут увеличены на 20%.

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

Макросы используют либо абсолютные ссылки на ячейки (A1, C3 и B8), либо именованный диапазон (plus20pct). Хотя именованный диапазон может быть скорректирован путем добавления или удаления строки или столбца, абсолютные ссылки на ячейки не изменятся. Таким образом, вы можете завершить проверку макроса (и настройку)

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

В-третьих, предположим, что кто-то вводит значение (200) в одну из ваших входных ячеек. Оно автоматически увеличивается на 20% и становится 240. Человек видит это изменение и задается вопросом, что произошло, поэтому он выбирает ячейку и нажимает F2, чтобы начать редактирование ячейки. Перед тем, как внести изменения, они помнят, что «А, да, предполагается, что автоматически увеличится на 20%». Поэтому они просто нажимают Enter, чтобы принять значение 240.

Однако Excel считает это изменением и увеличивает 240 на 20%, в результате чего получается 288 — не то, что вы или пользователь планировали.

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

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

_Примечание: _

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

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (12684) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.