Я работаю над таблицей иерархии Excel, и мне нужна помощь. В столбцах A-F изображена иерархия организации. Мне нужна функция, которая предоставляет значения для столбца «Ожидаемое количество».
Здесь я вручную заполнил, какие значения я хотел бы получить в столбце H:
Резюмирую логику, как рассчитываются числа в столбце H:
Теперь давайте посмотрим на последнее организационное подразделение в каждой строке:
Если данная ячейка не имеет значения в ячейке справа (практически у организационного подразделения нет подчиненных подразделений), тогда «Ожидаемое количество» = «Численность команды»-1. [например. C4 не имеет значения в ячейке справа (D5 пусто), практически это означает, что «Подорган A организации 1». не имеет подчиненных подразделений. Н4 = G4-1 = 8
Если в данной ячейке есть значения в ячейках справа (практически у подразделения есть подчиненное подразделение или подразделения), то «Ожидаемое количество» = «Численность команды»-1 + количество различных подчиненных подразделений x 2. [например. C5 имеет 4 разных значения в ячейках справа), практически это означает, что «Подорган B организации 1». имеет 4 различных подчиненных подразделения. H5 = G5-1+4x2= 16
Если значение в столбце G равно 0, то «Ожидаемое количество» = количество различных подчиненных единиц x 2 [например. Н2 = 1x2 =2]
Функция должна работать и на нижних уровнях. Это лишь образец иерархии. Там будут тысячи юнитов и, возможно, 8 уровней.
В столбцах A–F в каждой ячейке (которая имеет значение) указана организационная единица. В таблице показаны иерархически структурированные подразделения компании. Численность команды указывает на количество сотрудников, работающих в подразделениях. Мне нужны эти цифры в другой разбивке для программы.
При использовании предоставленной логики я получаю результаты, отличные от того, что вы опубликовали на изображении, но следуя вашей логике:
=G2+(G2=0)-1+2*COUNTA(A2:F2)*(COUNTA(A2:F2)<COUNTA(A3:F3))
или проливаемый (Office 365 Beta):
=LET(data,A2:G17,
lvl,DROP(data,,-1),
headc,TAKE(data,,-1),
curr,BYROW(--(lvl>""),SUM),
headc+(headc=0)-1+2*curr*(IFERROR(curr<DROP(curr,1),0)))
Офис 365:
=LET(data,B3:H18,
lvl,DROP(data,,-1),
headc,TAKE(data,,-1),
curr,BYROW(--(lvl>""),LAMBDA(b,SUM(b))),
headc+(headc=0)-1+2*curr*(IFERROR(curr<DROP(curr,1),0)))
включая уценку данных:
Эта формула возвращает желаемый результат на моем листе. Формула заполняется из ячейки J2.
=IFERROR(IF(G2=0,
ROWS(UNIQUE(FILTER(CHOOSECOLS($A$2:$F$17,
XMATCH(TRUE,NOT(ISBLANK(A2:F2)),,-1)+1),(CHOOSECOLS($A$2:$F$17,
XMATCH(TRUE,NOT(ISBLANK(A2:F2)),,-1))=
XLOOKUP(TRUE,NOT(ISBLANK(A2:F2)),A2:F2,,,-1))*(CHOOSECOLS($A$2:$F$17,
XMATCH(TRUE,NOT(ISBLANK(A2:F2)),,-1)+1)<>""))))*2,G2-
1+ROWS(UNIQUE(FILTER(CHOOSECOLS($A$2:$F$17,
XMATCH(TRUE,NOT(ISBLANK(A2:F2)),,-1)+1),(CHOOSECOLS($A$2:$F$17,
XMATCH(TRUE,NOT(ISBLANK(A2:F2)),,-1))=
XLOOKUP(TRUE,NOT(ISBLANK(A2:F2)),A2:F2,,,-1))*(CHOOSECOLS($A$2:$F$17,
XMATCH(TRUE,NOT(ISBLANK(A2:F2)),,-1)+1)<>""))))*2),G2-1)
Не компактный, как у P.b. :)
С Microsoft 365,
leaf_col
.has_no_sub_org
в основном ISBLANK(INDEX(hierarchy, row_num + 1, leaf_col + 1))
sub_org_count
= СТРОКИ (непустые ячейки в следующем столбце для этой организации)=LET(
hierarchy, A2:F17,
team_head_count, LAMBDA(row_num, INDEX(G2:G17, row_num)),
row_indices, SEQUENCE(ROWS(hierarchy)),
expected_counts, BYROW(
row_indices,
LAMBDA(row_num,
IF(
team_head_count(row_num) = 0,
2,
LET(
row_, CHOOSEROWS(hierarchy, row_num),
leaf_col, COUNTA(row_),
has_no_sub_org, IF(
row_num = MAX(row_indices),
TRUE,
ISBLANK(INDEX(hierarchy, row_num + 1, leaf_col + 1))
),
sub_org_count, IF(
has_no_sub_org,
0,
ROWS(
UNIQUE(
FILTER(
CHOOSECOLS(hierarchy, leaf_col + 1),
NOT(ISBLANK(CHOOSECOLS(hierarchy, leaf_col + 1))) *
(
CHOOSECOLS(hierarchy, leaf_col) =
INDEX(hierarchy, row_num, leaf_col)
)
)
)
)
),
team_head_count(row_num) - 1 + sub_org_count * 2
)
)
)
),
expected_counts
)
Просто обычный подход индекса/соответствия. Единственное, что стоит упомянуть:
• Добавлены дополнительная строка и столбец, служащие индикаторами.
• В диапазон подсчитываемого столбца всегда будет одна пустая ячейка, поэтому я вычитаю одну из уникального счетчика.
• c-ROWS(xdata) всегда будет отрицательным, поэтому значение будет приниматься с конца столбца.
=LET(
data,A2:F17,
xdata,EXPAND(data,ROWS(data)+1,COLUMNS(data)+1,""),
headCt,G2:G17,
MAP(SEQUENCE(ROWS(data)),
LAMBDA(c,
LET(colIndex,XMATCH(TRUE,INDEX(xdata,c,0) = ""),
restCol,TAKE(INDEX(xdata,0,colIndex),c-ROWS(xdata)),
nextBlank,XMATCH(TRUE,restCol = ""),
u,ROWS(UNIQUE(TAKE(restCol,nextBlank)))-1,
MAX(INDEX(headCt,c)-1,0)+2*u))))
Что такое
org unit
? Для вас это может быть логично, но для меня это не так.