Как соединить два набора данных в Excel с помощью динамических функций?
Я создаю отчет для продавцов, и мне нужно соединить два набора данных с динамическими функциями. Набор данных 1 является целевым, но у каждого человека может быть несколько записей (например, если у него был другой лидер в течение года. Например, у человека А было 3 лидера. Мне также нужна возможность включать или исключать записи на основе выбора в отчете:
Затем мне нужно связать эти данные с набором данных 2, который показывает объемы по учетным записям для подписанных счетов, а также тех, кто их подписал. Мне также понадобится возможность включать или исключать определенные записи, а некоторые учетные записи можно разделить между двумя людьми (учетная запись y).
Конечным результатом должен быть целевой показатель и объем продаж на одного лидера в зависимости от того, когда у этого лидера был объем продаж, по следующим направлениям:
в качестве примера у меня есть следующее, которое поворачивает данные для целей:
=LET(
names, UNIQUE(B2:B5),
namesWithYes, FILTER(B2:B5, P2:P5 = "yes"),
TargetWithYes, BYROW(FILTER(C2:O5,P2:P5 = "yes"),LAMBDA(α,SUM(CHOOSECOLS(DROP(α,,1),--TEXTSPLIT(INDEX(α,1),","))))),
Targets, MMULT(--(names=TRANSPOSE(namesWithYes) ), TargetWithYes),
IF( {1,0}, names, Targets )
)
извини. опечатка в цели для лидера 1. должно быть 99 вместо 68
в качестве примера у меня есть следующее, которое вращается для цели: =LET(names, UNIQUE(B2:B5),namesWith Yes, FILTER(B2:B5, P2:P5 = "да"), TargetWith Yes, BYROW(FILTER(C2: O5,P2:P5 = "да"),LAMBDA(α,SUM(ВЫБЕРИТЕ ECOLS(DROP(α,,1),--TEXTSPLIT(INDEX(α,1),","))))), Цели, MMULT(--(names=TRANSPOSE(namesWith Yes) ), TargetWith Yes), IF( {1,0}, name, Targets ))
Попробуйте отредактировать в самом вопросе, комментарии со временем будут удалены. (Сейчас я не могу редактировать это для вас, иначе буду рад помочь)
извини. Я новичок во всем этом. Спасибо :-)
Ответы 3
Для первой части (Цель):
=SUM(FILTER($D$2:$O$5 , ($B$2:$B$5 = A10) * ($P$2:$P$5 = "yes") )
Красный — B2:B5, синий — D2:O5, зеленый — P2:P5.
Измените A10 на ссылку на ячейку, в которую вы хотите ввести номер своего лидера.
Перетащите вниз остальных лидеров.
Спасибо. Однако я пытаюсь создать массив, а не перетаскивать формулы. например для целевой стороны у меня есть это: =LET(names, UNIQUE(B2:B5), nameWith Yes, FILTER(B2:B5, P2:P5 = "да"), TargetWith Yes, BYROW(FILTER(C2:O5,P2:P5) = "да"),LAMBDA(α,SUM(CHOOSECOLS(DROP(α,,1),--TEXTSPLIT(INDEX(α,1),","))))), Targets, MMULT(- -(names=TRANSPOSE(namesWithYes)), TargetWith Yes), IF( {1,0}, name, Targets)), но теперь я хотел бы связать это с набором данных 2, чтобы сделать то же самое с объемом. Надеюсь, это имеет смысл
Теперь это имеет больше смысла, хотя я все еще выясняю, как получить номера томов; Но, пожалуйста, включите в свой вопрос формулу, которая у вас уже есть. Так что мы можем опираться на это.
Если вы правильно поняли, вы хотите добиться объемов, если да, то можно использовать следующую формулу:
=LET(
names, UNIQUE(B2:B5),
namesWithYes, FILTER(B2:B5, P2:P5 = "yes"),
TargetWithYes, BYROW(FILTER(C2:O5,P2:P5 = "yes"),LAMBDA(α,SUM(INDEX(α,--TEXTSPLIT(@α,",")+1)))),
Targets, MMULT(--(names=TOROW(namesWithYes) ), TargetWithYes),
Volumes, MAP(names, LAMBDA(α, LET(δ, FILTER(HSTACK(A2:A5,D2:O5),α=B2:B5,""),
SUM(BYROW(δ, LAMBDA(ε, SUM(IF((INDEX(ε,1)=A8:A11)*(DROP(ε,,1)>0)*(O8:O11 = "Yes"),C8:N11,0)))))))),
HSTACK(names, Targets,Volumes))
Если для вас возможно использование таблиц, вы можете попробовать этот вариант (кстати, спасибо за вопрос :)
=LET(
targetWithYes, tblTarget[include] = "yes",
volumeWithYes, tblVolume[include] = "yes",
delim, ",",
row_delim, ";",
include_col, LAMBDA(col_headers, csv_incl,
ISNUMBER(
XMATCH(
col_headers,
TRIM(TEXTSPLIT(csv_incl, ","))
)
)
),
name_months_for_leader, LAMBDA(l,
FILTER(
HSTACK(tblTarget[name], tblTarget[Months]),
tblTarget[Leader] = l
)
),
sum_volume, LAMBDA(name_months,
SUMPRODUCT(
tblVolume[[1]:[12]] * volumeWithYes *
(tblVolume[name] = INDEX(name_months, 1, 1)) *
include_col(
tblVolume[[#Headers],[1]:[12]],
INDEX(name_months, 1, 2)
)
)
),
u_leader, UNIQUE(
FILTER(tblTarget[Leader], targetWithYes)
),
leader_target_volume, TEXTJOIN(
row_delim,
,
BYROW(
u_leader,
LAMBDA(l,
LET(
sum_target, SUMPRODUCT(
tblTarget[[1]:[12]] *
(tblTarget[Leader] = l) *
targetWithYes
),
sum_volume, SUM(
BYROW(
name_months_for_leader(l),
sum_volume
)
),
TEXTJOIN(
delim,
,
l,
sum_target,
sum_volume
)
)
)
)
),
TEXTSPLIT(leader_target_volume, delim, row_delim)
)
Я не уверен, как вы достигли ожидаемого результата, с некоторыми
FILTERЯ могу получить результаты на основе лидера 2, но неясно, как вы достигли своих собственных результатов.