Отношения «родитель-потомок» в Excel и уровень детали
Любая помощь приветствуется по этому вопросу. Изначально я пришел сюда, чтобы получить помощь по этому вопросу, и смог найти решение, которое работало до сих пор.
Для ссылки на исходный пост вот ссылка: Формула Excel для присвоения родительского номера дочернему элементу
Вот ссылка на таблицу Excel с используемыми в настоящее время кодами: https://1drv.ms/x/s!Au53oWRKuRfD9kanfyQ62uW30r1V
Еще раз повторю, что до сих пор это работало, и мне нужно найти решение, чтобы устранить проблему. Итак, позвольте мне объяснить, что происходит:
Мы начинаем с ввода информации о спецификациях деталей (BOM) на первой вкладке («Инженерный выпуск»).
Столбец A (номер позиции) используется для назначения родительской детали дочерней детали и определения уровня детали в сборке. Например: Сборка = 1 — это сборка и ее уровень — 0, Компонент 1 = 1,1 — это составная часть до 1 (сборка) и ее уровень равен 1, Субкомпонент 1 = 1.1.1 является частью подкомпонента 1.1 (компонент 1) и его уровень равен 2, Компонент 2 = 1,2 – это составная часть до 1 (сборка) и ее уровень равен 1, Субкомпонент 2 = 1.2.1 является частью подкомпонента 1.2 (компонент 2) и его уровень равен 2, Sub-Sub-Component = 1.2.1.1 является частью суб-субкомпонента 1.2.1 (субкомпонент 2) и его уровень равен 3, и т. д....
На второй вкладке («BOMUploadRelease») у меня все, начиная с первого листа, автоматизировано с помощью кода для заполнения шаблонного листа, который загружается в нашу систему ERP.
На этом листе находятся коды. Номеру детали присваивается буква «.(буква)», добавляемая в конец номера детали на первой вкладке на основе выбора столбца «Dispo» на первой вкладке. Столбец «Уровень» на вкладке 2 («BOMUploadRelease») назначается на основе столбца A (номер позиции), а столбец «Родительский» на вкладке 2 («BOMUploadRelease») назначается на основе столбца A (номер позиции), но вводится родительская часть. номер из столбца (Номер детали) на вкладке 2 («BOMUploadRelease»).
Первая проблема — это присваиваемые уровни. Текущий код работал нормально, пока мы не увеличили спецификацию и не использовали двузначные цифры, например 1.5.11, а из-за двузначного числа «11» после точки он дает ему уровень 3, хотя он все еще должен быть 2. По сути, для каждый "." в строке из столбца A (номер позиции) на первой вкладке («Инженерный выпуск») будет уровень, поэтому 0 «.» будет уровнем 0, 1 «." будет уровень 1, 2 "." будет уровень 2, 3 "." будет уровень 3 и т. д.
Код, используемый для решения проблемы уровней деталей:
=IF('Engineering Release'!A6<>"",LEN('Engineering Release'!A6)-LEN(SUBSTITUTE('Engineering Release'!A6,".","")),"")
Вторая проблема заключается в том, что родительский номер назначается неправильно, когда в спецификации имеется один и тот же номер детали субкомпонента, но есть два разных номера родительского компонента, которые используют один и тот же номер детали субкомпонента. Он всегда будет извлекать первый родительский номер детали для всех подкомпонентов, а не назначать его правильному.
Как это выглядит сейчас:
Что должно быть с исправленными ошибками красным текстом:
Код, используемый для присвоения номера родительской детали:
=LET(x, XLOOKUP(TEXTBEFORE(A2,".",-1),
'Engineering Release'!$B$6:$B$62,
'Engineering Release'!$A$6:$A$62),
y, 'Engineering Release'!$K$6:$K$62,IFERROR( XLOOKUP(TEXTBEFORE(x,".",-1),
TEXT('Engineering Release'!$A$6:$A$62,"@"),
'Engineering Release'!$B$6:$B$62 &"." &IFS (y = "Ref.","REF", y = "Repair","R", y = "Reuse","U", y = "Modify","M", y = "New","N", y = "Outsource","O")),""))
Любая помощь ценится и нужна. Пожалуйста, обратитесь к электронной таблице по ссылке, изображениям, показывающим, как это не так и как должно быть, а также используемым кодам.
Мне помогали с этой формулой с самого первого моего приезда сюда, потому что я застрял. До сих пор это работало с простыми спецификациями, но с более сложными спецификациями — не так хорошо. Хотя я понимаю, почему это проблема.
Ответы 2
Если у вас есть Microsoft 365 (в противном случае сообщите мне), пожалуйста, введите это в первую ячейку BOMUploadRelease:
- Используя Менеджер имен, определите
BOMUpRelс помощью приведенного ниже кода. - Эта пользовательская функция принимает диапазон данных и результат, необходимые в качестве входных данных.
- Используя col_num в качестве параметра, вы можете разместить столбец в необходимых позициях (как показано в примере).
=LAMBDA(eng_data, col_num,
LET(
header, TAKE(eng_data, 1),
data, DROP(eng_data, 1),
item_no, INDEX(data, , 1),
part_no, INDEX(data, , 2) &
SWITCH(
INDEX(data, , XMATCH("Dispo.", header)),
"Repair", "R",
"Reuse", "U",
"Modify", "M",
"Ref", "F",
"New", "N"
),
level, LEN(item_no) - LEN(SUBSTITUTE(item_no, ".", "")),
parent_item_no, TEXTBEFORE(item_no, ".", level),
parent_part_no, IF(
level,
XLOOKUP(parent_item_no, item_no & "", part_no, "not found"),
""
),
result, HSTACK(part_no, INDEX(data, , 3), level, parent_part_no),
CHOOSECOLS(result, col_num)
)
)
- Очистите существующие значения в
BOMUploadRelease - Введите
=BOMUpRel('Engineering Release'!A5:K45, col_num)в первую ячейку каждого столбца. - Поскольку параметр
col_numиспользуется сCHOOSECOLS, чтобы получить несколько столбцов, введите 1–4 в виде массива внутри{}; например ``=BOMUpRel('Engineering Release'!A5:K45,{1,2,3,4})` вернет все столбцы.
Результат:
Я использовал описательные имена для переменных. Если необходимо подробное объяснение, сообщите мне.
Да, мы используем 365. Я использую настольные приложения для всего, кроме нескольких документов SharePoint. Эта книга Excel создается через настольное приложение. Я попробую код и свяжусь с вами, так как сейчас я в дороге. Если я смогу остановиться и войти в систему удаленно, я попробую. Большое спасибо за помощь.
Это хорошо. LAMBDA и т. д. тогда сработает.
Вы можете использовать следующую формулу для Level в O2:
=LET(a,DROP(TOCOL('Engineering Release'!A:A,1),4),LEN(a)-LEN(SUBSTITUTE(a,".",)))
Это увеличивает количество деталей, используемых в инженерной версии, поэтому обязательно удалите все значения (формулы) из ячеек ниже.
Тогда для Parent в S2 вы можете использовать:
=XLOOKUP(O2-1,O$1:O1,A$1:A1,"",,-1)
Немного не по теме:
Вы можете значительно упростить формулу
BomUploadRelease!A2:старая формула:
=IF('Engineering Release'!K6:K6 = "Repair",CONCATENATE('Engineering Release'!B6:B6,".R"),IF('Engineering Release'!K6:K6 = "Reuse",CONCATENATE('Engineering Release'!B6:B6,".U"),IF('Engineering Release'!K6:K6 = "Modify",CONCATENATE('Engineering Release'!B6:B6,".M"),IF('Engineering Release'!K6:K6 = "Ref.",CONCATENATE('Engineering Release'!B6:B6,".F"),IF('Engineering Release'!K6:K6 = "New",CONCATENATE('Engineering Release'!B6:B6,".N"), "")))))новая формула:
=DROP(TOCOL('Engineering Release'!B:B,1),1)&"."&SWITCH(DROP(TOCOL('Engineering Release'!K:K,1),1),"Repair","R","Reuse","U","Modify","M","Ref.","F","New","N")Или бета:
=DROP('Engineering Release'!B:.B,5)&"."&SWITCH(DROP('Engineering Release'!K:.K,5),"Repair","R","Reuse","U","Modify","M","Ref.","F","New","N")И ваша формула в
BomUploadRelease!C2:старая формула:
=IF(ISBLANK('Engineering Release'!C6:F6), "", 'Engineering Release'!C6:F6)новый (я не понимаю, почему вы ссылаетесь на столбец F, поскольку это объединены и поэтому не будут содержать данные):
=TOCOL(DROP('Engineering Release'!C:C,5),1)или бета:
=DROP('Engineering Release'!C:.C,5)
еще раз по теме: если вы реализовали вышеуказанные изменения, вы можете разлить своего родителя, используя:
=MAP(A2#,O2#,LAMBDA(PartNo,Level,XLOOKUP(Level-1,O2:Level,A2:PartNo,"",,-1)))
Хороший П.Б. Могу ли я спросить, что такое синтаксис «бета» C:.C? Спасибо.
Спасибо. . означает до последней использованной ячейки. C.:.C будет от первого до последнего использования. К сожалению, вы не можете писать C6:.C (пока?). Если вы участвуете в инсайдерской программе и используете бета-канал, это новая активированная функция, которая в конечном итоге станет доступна и обычным пользователям после успешного тестирования.
@nkalvi Подробнее здесь techcommunity.microsoft.com/t5/excel-blog/….
Пока недоступно на Mac Beta (все еще версия 2408). Еще раз спасибо.
Вау... Спасибо. На этих выходных я буду в дороге, но попробую все это, когда вернусь в офис. Я никогда не имел дела с разливами и никогда не видел этого, поэтому мне очень интересно его использовать. Я дам тебе знать, как только смогу
Разлив @JoshuaJohnson открывает двери для создания динамичных решений. Они обновляются в реальном времени при изменении/добавлении данных.
Это приятно знать. Тогда мне нужно этому научиться. Я пытаюсь автоматизировать как можно большую часть ручного ввода данных в таблицы Excel и больше использовать систему ERP, но некоторые процессы, подобные этому, по-прежнему требуют, чтобы исходная таблица Excel выполняла работу по преобразованию всего для загрузки в файл. ERP-система с минимальным вводом данных.
@JoshuaJohnson, если вас беспокоит порядок данных технических релизов, это может быть легко исправить: =LET(a,SORT(""&DROP(TOCOL('Engineering Release'!A:A,1),4)),LEN(a)-LEN(SUBSTITUTE(a,".",)))
Обратите внимание, что ваша проблема 1 вызвана ошибкой в вашей формуле. в настоящее время там написано:
=IF('Engineering Release'!A6<>"",LEN(SUBSTITUTE('Engineering Release'!A6,".",""))-1,"")в то время как в вашем вопросе вы написали формулу, которая будет правильно рассчитывать:=IF('Engineering Release'!A6<>"",LEN('Engineering Release'!A6)-LEN(SUBSTITUTE('Engineering Release'!A6,".","")),"")