Excel – автоматическое увеличение ячейки столбца строки идентификатора при вставке
Можно ли реализовать этот сценарий в Excel, чтобы я получал автоматическое увеличение столбца идентификатора каждый раз, когда вставляется новая строка? (неважно в каком месте - в начале, в середине или в конце листа):
- В Excel есть ячейка (назовем ее
max_value), которая содержит целочисленное значение; - Существует столбец (
column A), содержащий идентификаторы (все эти идентификаторы будут <=max_value); - После того, как пользователь вставляет новую строку, независимо от того, в каком месте,
max_valueсначала увеличивается на 1, а затемcolumn Aв этой новой строке заполняетсяmax_value.
Хотите ли вы, чтобы это выполнялось автоматически после вставки строки/строк? А как насчет случая вставки более чем строки? Увеличивать каждую строку, начиная с указанной выше? Как действовать в случае удаления строк? Следует ли запретить удаление строки?
@FoxfireAndBurnsAndBurns, прежде чем я даже попытаюсь, я хочу знать, возможно ли это, и если да, я постараюсь предоставить некоторый код. Хотелось бы остаться с Excel, поскольку убедить других использовать другой инструмент может быть болезненно, а Excel знают все =)
@FaneDuru да автоматически, я могу запретить пользователям добавлять по одной строке за раз, чтобы упростить задачу. Удаление строк должно быть разрешено, max_value в любом случае будет храниться в отдельной ячейке для новых строк.
Microsoft не предоставляет никаких событий, которые могли бы инициироваться удалением строк. Но событие рабочего листа Change, я думаю, можно использовать для этого. Затем, когда я спросил об удалении строки, я не имел в виду удаление строки, содержащей max_value... Я имел в виду, разве вам не нужна какая-либо корреляция между существующими строками (с данными) и этим max_value числом? Я имею в виду, что если всего 10 строк и удаление всех из них, максимум будет выглядеть странно. Что вы имеете в виду под ограничением вставки только одной строки? Инструктировать их устно или создавать код, запрещающий это?
На всякий случай (проблема обнаружения вставки/удаления строки) кажется полезным сделать две кнопки для вставки и удаления и использовать позицию активной ячейки в качестве ссылки на вставку.
@FaneDuru нет необходимости в корреляции, мне просто нужно иметь уникальные числовые значения, могут быть пробелы и т. д., в основном все, что мне нужно, описано в вопросе. Что касается запрета пользователям вставлять несколько строк — устно, поэтому это не входит в мои требования к решению. Не нужно выполнять никаких действий при удалении строки, только при вставке строки.
Если идентификатор всегда увеличивается на 1, то: Используйте таблицу (вставка > таблица) с формулой в столбце A =ROW()-ROW(Table1[#Headers]). Формула максимального значения: =MAX(Table1[ID])
Ответы 1
Чтобы сделать то, что вы хотите, необходимо поместить строку marker в столбец A:A в строке, которая должна находиться за пределами диапазона, в который будут вставлены строки. Поэтому напишите «Маркер» в соответствующей ячейке.
На другом листе («Лист2») в следующем примере напишите начальную букву max_value. Хорошо бы этот лист скрыть, чтобы случайно не изменить эталонное значение.
Пожалуйста, скопируйте следующий код на обрабатываемом листе (увеличить в столбце A:A) модуль кода:
Option Explicit
Private markerRowNo As Long 'the Marker row when a whole row is selected
Private rngMark As Range 'the range where the Marker is (to be found)
Private Const shMaxName As String = "Sheet2" 'the sheet name where the max row is kept (in "A1")
Private max_value As Long 'the reference value to be incremented
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = Me.Columns.Count Then
If Target.Rows.Count > 1 Then 'if inserted more than a row:
Application.EnableEvents = False
markerRowNo = 0: Application.Undo 'reverse the rows insertion
Application.EnableEvents = True
MsgBox "Not allowed to insert more than a row at a time!", vbInformation, "No more than one row": Exit Sub
End If
Set rngMark = Me.Columns(1).Find(what: = "Marker", After:=Me.Range("A1"), LookIn:=xlValues, LookAt:=xlWhole)
If rngMark.Row > markerRowNo Then 'if the row where the maker exists is bigger then the previous => insertion
Application.EnableEvents = False 'to avoid triggering the event continuously
max_value = Worksheets(shMaxName).Range("A1").Value
Target.Cells(1).Value = max_value + 1 'place the necessary value in A:A
Worksheets(shMaxName).Range("A1").Value = max_value + 1 'adapt the maximum row reference
Application.EnableEvents = True
End If
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Columns.Count = Me.Columns.Count Then 'if a whole row has been selected
'find the marker cell:
Set rngMark = Me.Columns(1).Find(what: = "Marker", After:=Me.Range("A1"), LookIn:=xlValues, LookAt:=xlWhole)
markerRowNo = rngMark.Row 'update the markerRowNo variable
End If
End Sub
Первым инициируемым событием является SelectionChange, которое в случае выбора всей строки запоминает номер строки «Маркер».
Второе инициируемое событие — Change, которое определяет, была ли вставлена целая строка, записывает в A:A увеличенный номер строки, а также увеличивает ссылочную ячейку.
Это последнее событие отвечает за вставку нескольких строк. В таком случае это UnDo вставка и отправка соответствующего сообщения...
Пожалуйста, оставьте отзыв после тестирования. Я пробовал комментировать все строки кода, но если что-то еще не совсем понятно, не стесняйтесь спрашивать разъяснения.
@sjanisz Пожалуйста, загрузите форму рабочей тетради тестирования здесь и поиграйте с ней. Я использовал его для проверки приведенного выше кода. Мне никогда не требовались такие комбинации событий, но я попробовал это сейчас...
Я занимаюсь этим, в случае вставки это работает так, как предполагалось, но я хочу удалить функцию, которая при удалении строки затем строка под идентификатором заменяется новым значением, я хочу, чтобы это оставалось таким, как было раньше
@sjanisz Упс... Это ошибка, и я не проверил ее должным образом после того, как попробовал использовать существующую переменную max_value. По ошибке я также скопировал это в событие SelectionChange. Его необходимо заменить на markerRowNo. Я сделаю это немедленно. Сделал это! Без этого код не сможет отличить вставку или удаление... И так оно и было, когда я его проектировал и тестировал.
@sjanisz Вы можете проверить это в обновленной книге тестирования, которую можно скачать здесь...
Вы можете добиться этого с помощью VBA. Пожалуйста, опубликуйте код, который вы пробовали, где вы застряли и какие ошибки вы получаете. В любом случае, если вам действительно нужно обрабатывать автоматическое увеличение идентификатора, Excel явно не лучший вариант для этого. Рассмотрите возможность использования других вариантов, таких как Access или SQL, и объедините их с Excel.