У меня есть лист с тремя вкладками. Клиент сталкивается с «Формой», «Списком» для вставки в CRM извлеченной информации и «Данными» для размещения любых необходимых промежуточных данных.
Соответствующая информация, извлеченная из CRM, представляет собой два столбца: столбец A — «Регионы» и столбец C — «Имена элементов».
Я хочу использовать код VBA, чтобы взять информацию из «Списка» и отобразить ее в «Форме» с именем региона, а затем в том же столбце перечислить элементы для этого региона. Оставьте место и продолжайте.
Пример:
Airport
Toy Robot
Uno Card Game
Commercial
Dinosaur Figures
Glass Marbles
Kids Makeup Kit
Lego Bricks
Nerf Gun
PlayDoh Can
PlayDoh Toolkit
Residential
Deck Of Cards
Dino Egg
Dinosaur Figures
Glass Marbles
Kids Makeup Kit
Lego Bricks
Mini Ping Pong
Monopoly
Downtown
Lego Bricks
Nerf Gun
PlayDoh Can
Я использовал код VBA, чтобы определить, сколько элементов указано для каждого региона, и сохранить их на вкладке «Данные». В столбце A указан регион, а в столбце B указано количество товаров, перечисленных для этого региона.
Для образца: Аэропорт 2 Коммерческий 7 Жилой 8 Центр города 3
Далее у меня есть код, который настраивает имена регионов, оставляя место для элементов.
Аэропорт
Коммерческий
Жилой
Центр города
Теперь я пытаюсь выяснить, как добавить сопровождающие имена элементов в каждом регионе на вкладке «Форма».
Я пробовал использовать Index Match с циклом, но он не выполняет цикл нужное количество раз и не принимает во внимание пустые строки.
(Disclaimer: depending on your version of Excel, not all of these functions may be available.)
Итак, для первого шага мы можем использовать UNIQUE
и FILTER
:
=UNIQUE(FILTER(List!$A:$A, --(List!$A:$A<>"Region")*--(List!$A:$A<>"")))
(Обратите внимание, что FILTER
предназначен для удаления заголовка и пустых ячеек. Вы можете изменить "Region"
на List!$A$1
, если хотите, чтобы все было более «динамично»).
Теперь нам нужно будет действовать с каждой записью в этом массиве/списке, поэтому давайте воспользуемся REDUCE
и LAMBDA
:
=REDUCE("", UNIQUE(FILTER(List!$A:$A, --(List!$A:$A<>"Region")*--(List!$A:$A<>""))), LAMBDA(arr, val, [SOME FUNCTION]))
Это возьмет значение arr
(которое начинается с ""
) и применит к нему <SOME FUNCTION>
для каждого значения (val
) в массиве. Итак, анализируя наши шаги: «Во-первых, вам нужен уникальный список регионов. Затем для каждого региона вы хотите получить список элементов. Наконец, вы хотите сложить все это вместе». До сих пор мы все сделали жирным шрифтом: давайте «получим список элементов» для val
в качестве нашего следующего шага:
UNIQUE(FILTER(List!$C:$C, List!$A:$A=val))
Наконец, мы хотим сложить все вертикально, с пустой строкой между ними. Для этого мы можем использовать VSTACK
:
VSTACK(arr, val, [LIST OF ITEMS], "")
Разберем это: мы хотим взять наш arr
на данный момент, добавить val
(Регион) в качестве заголовка, затем наш Список элементов, который мы разработали мгновение назад, и закончить пустой ячейкой (""
). Итак, сложим все это воедино:
=REDUCE("",UNIQUE(FILTER(List!$A:$A, --(List!$A:$A<>"Region")*--(List!$A:$A<>""))), LAMBDA(arr, val, VSTACK(arr, val, UNIQUE(FILTER(List!$C:$C, List!$A:$A=val)), "")))
Теперь, если вы запустите это, вы можете заметить, что оно включает пустую строку вверху (потому что arr
начинается как ""
) и пустую строку внизу (потому что мы добавляем пустую строку после каждого списка элементов). Если вы хотите избавиться от них, вы можете использовать функцию DROP
: положительное число удаляет указанное количество строк/столбцов из верхнего/левого края массива, а отрицательное число удаляет их из нижнего/справа. Мы хотим удалить по одному, поэтому нам нужно DROP
дважды:
=DROP(DROP(REDUCE("",UNIQUE(FILTER(List!$A:$A, --(List!$A:$A<>"Region")*--(List!$A:$A<>""))), LAMBDA(arr,val, VSTACK(arr, val, UNIQUE(FILTER(List!$C:$C, List!$A:$A=val)), ""))), 1), -1)
Этот код работает в моем примере файла, если я запускаю его на листе «Форма». Ввод данных в лист «Форма» начинается с ячейки A10, и при запуске макроса на листе «Форма» данные отсутствуют. Как и в вашем примере, данные сортируются на листе «Список». Код также работает, если для региона имеется только одна запись.
Sub regions()
Dim i, j, l, m As Long
Range("A:A").Clear
j = 10
For i = 2 To Worksheets("List").Range("A" & Rows.Count).End(xlUp).Row
l = Application.WorksheetFunction.CountIf _
(Worksheets("List").Range("A:A"), Worksheets("List").Cells(i, 1))
If l = 1 Then
Cells(j, 1).Value = Worksheets("List").Cells(i, 1).Value
Cells(j + 1, 1).Value = Worksheets("List").Cells(i, 3).Value
j = j + l + 2
Else
If Worksheets("List").Cells(i, 1).Value = _
Worksheets("List").Cells(i + 1, 1).Value And _
Application.WorksheetFunction.CountIf(Range("A:A"), _
Worksheets("List").Cells(i, 1)) = 0 Then
Cells(j, 1).Value = Worksheets("List").Cells(i, 1).Value
For m = 1 To l
Cells(j + m, 1).Value = Worksheets("List").Cells(i + m - 1, 3).Value
Next m
j = j + l + 2
Else
End If
End If
Next i
End Sub