Excel автоматизирует создание динамического количества строк из другого набора данных
Я пытаюсь автоматизировать создание динамического количества строк из справочных данных.
Итак, есть 2 листа — Лист1 и Лист2. Я хочу, чтобы, ссылаясь на Лист1 (столбец E, т.е. ROLE_ID), а затем под Листом2 (столбец ROLE_ID), он должен динамически создавать такое количество строк (как есть в Листе1 - Столбец E, т.е. ROLE_ID)
Таким образом, сначала будет создано 8 строк для ГРУППЫ A, затем 5 строк для ГРУППЫ B и затем 8 строк для ГРУППЫ C.
Ниже показано, как выглядит мой Лист1 (это своего рода справочные данные);
а ниже — мой Лист2 (куда я хочу добавить динамические строки из Листа1);
Не уверен, что это можно автоматизировать с помощью формул Excel.
ОБНОВЛЕНО
Ниже мой ожидаемый результат (в Sheet2)
=ФИЛЬТР(J4:J186, ЕСЛИ(ПУСТО(J4:J186), ИСТИНА, ЕСЛИ(НОМЕР(J4:J186), ИСТИНА, ЕСЛИ(ISNA(J4:J186), ЛОЖЬ, ИСТИНА))))
=ПУСК( а, Лист1!К4:К186, б, ВЗЯТЬ(а,,-1), в, СКАН(0,b,LAMBDA(x,y,IF(y<>0,x,x+1)))+1, г, Лист2!C7:F36, IF(b="","",HSTACK(b, CHOOSEROWS(d, XMATCH(c, SEQUENCE(ROWS(d)))))))
=ПУСК( а, Лист1!К4:К186, б, ВОЗЬМИТЕ(а,,-1), в, СКАН(0,b,LAMBDA(x,y,IF(y<>0,x,x+1)))+1, г, Лист2!C7:F36, IF(b=0, "", IF(b="", "", HSTACK(b, CHOOSEROWS(d, XMATCH(c, SEQUENCE(ROWS(d))))))) )
=ЕСЛИ(НЕ(ПУСТО(G4)), G4, ЕСЛИ(НЕ(ПУСТО(E4)), E4, ЕСЛИ(НЕ(ПУСТО(C4)), C4, "")))
=ЕСЛИ(НЕ(ПУСТО(G4:G196)), G4:G196, ЕСЛИ(НЕ(ПУСТО(E4:E196)), E4:E196, ЕСЛИ(НЕ(ПУСТО(C4:C196)), C4:C196, " ")))
=ВПР(H4, Роли!$B$30:Роли!$C$54, 2, ЛОЖЬ)
=IF(H4<>"", VLOOKUP(H4, Roles!$B$30:Roles!$C$54, 2, FALSE), "")
=IF(H4:H196<>"", FILTER(Роли!$C$30:$C$54, Роли!$B$30:$B$54=H4:H196), "")
=IF(H4<>"", VLOOKUP(H4, Roles!$B$30:$C$54, 2, FALSE), "")
=IF(C7:C202=0,"","INSERT INTO SOME_TABLE (COL1,COL2) значений('"&C7:C202&"','"&D7:D202&"');")
=IF(OR(C7:C202=0, ISBLANK(C7:C202)), "", "INSERT INTO SOME_TABLE (COL1,COL2) значений('"&C7:C202&"','"&D7:D202&"'); ")
=IF(OR(ISNUMBER(FIND(CHAR(10), A1))), ISNUMBER(FIND(CHAR(13), A1))), "Содержит CR или LF", "Не содержит CR или LF")
=ЕСЛИ(C7:C202=0,"","ВСТАВКА") =IF(C31="","пустая ячейка",IF(C31=0,"Нулевая ячейка","Ячейка > Ноль"))
=ЕСЛИ(C7:C202="", "", ЕСЛИ(C7:C202=0, "", "ВСТАВКА"))
=ПУСК( диапазон1, Лист1!A1:A20, диапазон2, Лист2!B5:B25, диапазон3, Лист3!C10:C30, комбинированный, VSTACK(диапазон1, диапазон2, диапазон3), ИНДЕКС(комбинированный, ПОСЛЕДОВАТЕЛЬНОСТЬ(СТРОКИ(комбинированный))) )
@MayukhBhattacharya - Спасибо... Я обновил ожидаемый результат. На данный момент мы можем просто сосредоточиться на столбце ROLE_ID (поскольку я в основном хочу сгенерировать такое количество динамических строк в Sheet2).
Попробуйте использовать следующее: =IF(Sheet1!E6:E28=0,"",Sheet1!E6:E28) Вы просто ссылаетесь на такое количество строк с первого листа на лист 2, просто используйте ту, которую я упомянул, IF() не требуется, он используется только для того, чтобы скрыть 0, чтобы он отображался как "" пустым!
Да, верно... в основном на Листе 1 будут пустые строки, указывающие на то, что конкретная группа закончилась, а затем начнется новая группа... Я в порядке, если на Листе 2 будет такая же пустая строка.
Пожалуйста, используйте тот, который я прокомментировал!
Спасибо... это действительно здорово... У меня только что появился последний вопрос по теме.
Да, что это такое?
Я только что обновил исходный вопрос... речь идет об использовании Sheet3 для динамического заполнения столбцов ROLE_NAME и ROLE_GROUP в Sheet2.
Кроме того, я могу вручную добавить и использовать какой-либо символ в столбце ROLE_ID для определения разделения групп (вместо пустой строки).
@MayukhBhattacharya - Кроме того, было бы очень здорово, если бы вы добавили в качестве ответа, и я могу принять то же самое. еще раз спасибо
Связанные вопросы — это новые вопросы, которые получают отдельную новую тему. Если на текущий вопрос дан ответ, отметьте эту тему как таковую, пожалуйста.
@JvdV Сэр, это моя вина, я должен был спросить ОП, но я думал, что это связано с частью IF(), я не был уверен, что они собираются изменить вопрос.
Ответы 2
Вот один из способов сделать то же самое:
=LET(
a, Sheet1!D6:E28,
b, TAKE(a,,-1),
c, SCAN(0,b,LAMBDA(x,y,IF(y<>"",x,x+1)))+1,
d, Sheet3!D4:E6,
IF(b = "","",HSTACK(b, CHOOSEROWS(d, XMATCH(c, SEQUENCE(ROWS(d)))))))
Пояснения:
- Использование функции
LET()помогает определить переменные и сократить избыточные вычисления. - Переменная
aопределяет данные изSheet1-->Sheet1!D6:E28 - Переменная
bопределяет диапазон данных второго столбца, взятых изaс помощью функцииTAKE(), поэтому использованиеTAKE(a,,-1)возвращает конкретный диапазон. - Затем переменная
cиспользует вспомогательную функциюLAMBDA()под названиемSCAN(), которая помогает возвращать массив с помощью специальной формулы, примененной кLAMBDA(), которая перебирает каждое значение в массиве и возвращает массив выходных данных для каждого промежуточного элемента в массиве. Итак, он проверяетb, не пуст ли диапазон, и заполняет значение сверху, как мы обычно делаем, используя значения заполнения сверху таким же образом, но используя динамически! - Далее переменная
dотносится к данным изSheet3 - Наконец, используя простой
XMATCH(), чтобы сопоставить количество строк в данных листа 3 с переменнойcи используяCHOOSEROWS()для заполнения соответствующих данных и, наконец, для сохранения и поддержания форматирования пробелов, которые мы используемIF()для их анализа.
Еще раз спасибо... если бы вы могли дать очень краткое объяснение того, что делает формула, это было бы очень полезно... всего несколько слов также помогли бы понять
По какой-то причине я получаю сообщение #N/A, когда использую приведенную выше формулу.
@copenndthagen не знаю, как вы их получили #N/A, потому что с моей стороны это работает, у вас тот же набор, что показан в ОП, могут быть различия в контексте данных, но макет похож на тот же, верно?
Да... Другая формула, которую вы дали, работает отлично, т.е. =IF(Sheet1!E6:E28=0,"",Sheet1!E6:E28). Но когда я пробую =LET(.......), он печатает #Н/Д
Не могу сказать, с моей стороны это работает!
Я предполагаю, что мне просто нужно вставить эту формулу =LET(.......) туда, где я хочу, чтобы моя первая строка/столбец была напечатана, а последующие строки/столбцы печатались динамически.
@copenndthagen, возможно, вам придется соответствовать диапазонам, но логика здесь остается той же! Это Excel, который вы можете проверить!
@copenndthagen, тебе больше не нужно использовать другую формулу, просто используй нынешнюю.
Рассматриваете ли вы Лист1 – Столбец D (ГРУППА) для чего-нибудь... Я могу игнорировать этот столбец.
@copenndthagen да, чтобы сохранить все и сделать его динамичным, я все включил, нажмите Excel, я добавил, чтобы увидеть, это поможет!
Первая ячейка под «ROLE_ID» на листе Sheet2:
=LET(
comment_1, "Range from first row to the possible last row (5000)",
role_id_col, Sheet1!$E$1:$E$5000,
comment_2, "IFS non-empty = row number, #NA otherwise; TOCOL to remove #NA",
row_num, TOCOL(IFS(LEN(TRIM(role_id_col)), ROW(role_id_col)), 2),
comment_3, "Assuming header (ROLE_ID) is the first non-empty cell, set the range",
role_id_data, INDEX(role_id_col, MIN(row_num) + 1):INDEX(role_id_col, MAX(row_num)),
comment_4, "Replace empty cells with empty string (these will be 0 otherwise)",
IF(LEN(TRIM(role_id_data)), role_id_data, "")
)
Не могли бы вы показать ожидаемый результат, пожалуйста, и можете ли вы использовать функцию
FILTER(), например=LET(a, FILTER(Sheet1!D6:E28,Sheet1!E6:E28<>""), IF(a=0,"",a)), и откуда беретсяROLE_NAME, объясните, пожалуйста! А если вы хотите заполнить пустые строкиGROUP, тогда=LET(a, FILTER(Sheet1!D6:E28,Sheet1!E6:E28<>""), HSTACK(TAKE(a,,-1),SCAN(0,TAKE(a,,1),LAMBDA(r,c,IF(c=0,r,c)))))