У меня есть книга Excel, в которой 100 листов, все листы идентичны, кроме A10 на каждом листе.
В A10 листа 1 есть значение 101, которое я набрал вручную, а A10 всех других листов пустое. Теперь я хочу, чтобы, когда я набираю 101 в A10 листа 1, я хочу иметь 102 в A10 листа 2, 103 в A10 листа 3 и так далее до листа 100; автоматически.
Как это сделать?
Этот макрос зависит от листа и должен быть помещен в модуль листа VBA.
Щелкните правой кнопкой мыши на имени листа в нижней части Excel и выберите вид кода.
Вставьте этот код туда.
закройте модуль vba и замените A10 на первом листе.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
For Count = 1 To ActiveWorkbook.Sheets.Count
Sheets(Count).Range("A10").Value = Sheets(1).Range("A10").Value + Count - 1
Next
Application.EnableEvents = True
End Sub
Я предполагаю, что A10 - это переменная, в противном случае вы могли бы просто жестко закодировать ее, и каждый последующий лист увеличивается на 1, независимо от значения в листе 1. В A10 на листе 2 вы могли бы поместить это:
=IF(ISBLANK(SHEET1!A10),"",SHEET1!A10+1)
На каждом последующем листе измените ссылку на лист, чтобы отразить номер предыдущего листа. Функция ISBLANK проверяет, является ли A10 на предыдущем листе пустым, и отображает пустое значение (пустые двойные кавычки). В противном случае отображается номер 1 выше, чем на предыдущем листе.
Как отмечает Прасанна в комментарии, это может потребовать ручного ввода формулы на 100 листах. Тем не менее, есть способ достичь результата по существу за один шаг. Он сочетает в себе две вещи. Во-первых, можно ввести одну и ту же формулу на каждый лист сразу. Щелкните правой кнопкой мыши одну из вкладок листа внизу и выберите «Выбрать все вкладки». Если вы затем вставите формулу в A10 текущего листа, каждый лист получит одну и ту же формулу с этим одним действием. Это будет включать Sheet1, поэтому вам придется вернуться к листу 1 и заменить A10 вашим значением.
Спросите, как вы используете одну и ту же формулу на каждом листе? Рад, что вы задали этот вопрос. В Excel есть функция CELL, которая возвращает метаинформацию о ячейке. Одним из параметров, который вы можете запросить, является FILENAME, который возвращает полный путь, имя файла и имя листа. Это может быть проанализировано для извлечения имени текущего листа, и вы можете использовать арифметику для изменения ссылки на лист, затем функцию INDIRECT для фактической ссылки на ячейку на другом листе, используя результирующий текст. Чтобы функция CELL работала, вы должны сначала сохранить электронную таблицу, чтобы она действительно получила имя файла.
Например, для ссылки на A10 на предыдущем листе вы замените жестко закодированное выражение SHEETn!A10 (где n - номер предыдущего листа), примерно так:
INDIRECT("sheet"&VALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+6,3))-1&"!a10")
Я полагаю, что вы можете ссылаться на любую ячейку на листе в функции CELL (я не думаю, что она должна содержать что-либо), поэтому я использовал ячейку A1, чтобы избежать циклической ссылки. Это извлекает номер листа путем нахождения позиции закрывающей скобки (которая завершает имя файла) и добавления счетчика символов для слова "лист", чтобы определить начальную позицию номера листа. Я использовал длину 3, потому что самый большой номер листа будет содержать 3 цифры (если его меньше, функция MID просто берет то, что есть).
Я явно преобразовал результат в число (функция VALUE), чтобы выполнить вычитание, чтобы получить предыдущий номер листа. Excel на самом деле прощает это и будет выполнять математику без преобразования VALUE, но для тех, кто использует другую электронную таблицу, это может потребоваться.
Полное имя листа и ссылка на ячейку затем строятся путем объединения текста вокруг номера листа с помощью &. Функция INDIRECT превращает эту текстовую строку в фактическую ссылку на ячейку.
Поэтому, если вы замените это выражение в исходной формуле, каждый лист получит одну и ту же формулу (нет необходимости редактировать ее на каком-либо листе), и он вычислит требуемую ссылку на лист на каждом листе.