Расчет ожидаемого количества на основе иерархической структуры

Я работаю над таблицей иерархии Excel, и мне нужна помощь. В столбцах A-F изображена иерархия организации. Мне нужна функция, которая предоставляет значения для столбца «Ожидаемое количество».

Здесь я вручную заполнил, какие значения я хотел бы получить в столбце H:

Резюмирую логику, как рассчитываются числа в столбце H:

Теперь давайте посмотрим на последнее организационное подразделение в каждой строке:

  1. Если данная ячейка не имеет значения в ячейке справа (практически у организационного подразделения нет подчиненных подразделений), тогда «Ожидаемое количество» = «Численность команды»-1. [например. C4 не имеет значения в ячейке справа (D5 пусто), практически это означает, что «Подорган A организации 1». не имеет подчиненных подразделений. Н4 = G4-1 = 8

  2. Если в данной ячейке есть значения в ячейках справа (практически у подразделения есть подчиненное подразделение или подразделения), то «Ожидаемое количество» = «Численность команды»-1 + количество различных подчиненных подразделений x 2. [например. C5 имеет 4 разных значения в ячейках справа), практически это означает, что «Подорган B организации 1». имеет 4 различных подчиненных подразделения. H5 = G5-1+4x2= 16

  3. Если значение в столбце G равно 0, то «Ожидаемое количество» = количество различных подчиненных единиц x 2 [например. Н2 = 1x2 =2]

Функция должна работать и на нижних уровнях. Это лишь образец иерархии. Там будут тысячи юнитов и, возможно, 8 уровней.

Что такое org unit? Для вас это может быть логично, но для меня это не так.

P.b 20.07.2024 00:30

В столбцах A–F в каждой ячейке (которая имеет значение) указана организационная единица. В таблице показаны иерархически структурированные подразделения компании. Численность команды указывает на количество сотрудников, работающих в подразделениях. Мне нужны эти цифры в другой разбивке для программы.

keniko 20.07.2024 07:55
Преобразование HTML-таблицы в профессиональный документ Excel
Преобразование HTML-таблицы в профессиональный документ Excel
Это самый простой способ создания Excel из HTML-таблицы.
Импорт excel в laravel в базу данных
Импорт excel в laravel в базу данных
Здравствуйте, дорогой читатель, в этой статье я расскажу практическим и быстрым способом, как импортировать файл Excel в вашу базу данных с помощью...
0
2
105
4
Перейти к ответу Данный вопрос помечен как решенный

Ответы 4

При использовании предоставленной логики я получаю результаты, отличные от того, что вы опубликовали на изображении, но следуя вашей логике:

=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)))

включая уценку данных:

А Б С Д Э Ф г ЧАС 1 1-й уровень Уровень 2 Уровень 3 Уровень 4 Уровень 5 Уровень 6 Численность команды Ожидаемое количество 2 Страна1 0 2 3 Страна1 Организация 1 страны 1 1 4 4 Страна1 Организация 1 страны 1 Суборг А организации 1. 9 8 5 Страна1 Организация 1 страны 1 Суборг Б организации 1. 9 14 6 Страна1 Организация 1 страны 1 Суборг Б организации 1. Регион 1 Суборга Б 2 9 7 Страна1 Организация 1 страны 1 Суборг Б организации 1. Регион 1 Суборга Б Область 1 региона 1 17 16 8 Страна1 Организация 1 страны 1 Суборг Б организации 1. Регион 1 Суборга Б Зона2 Региона1 11 10 9 Страна1 Организация 1 страны 1 Суборг Б организации 1. Регион 2 Суборга Б 3 10 10 Страна1 Организация 1 страны 1 Суборг Б организации 1. Регион 2 Суборга Б Площадь1 Региона2 17 16 11 Страна1 Организация 1 страны 1 Суборг Б организации 1. Регион 2 Суборга Б Площадь2 Региона2 9 8 12 Страна1 Организация 1 страны 1 Суборг Б организации 1. Регион 3 Суборга Б 3 10 13 Страна1 Организация 1 страны 1 Суборг Б организации 1. Регион 3 Суборга Б Площадь1 Региона3 4 3 14 Страна1 Организация 1 страны 1 Суборг Б организации 1. Регион 3 Суборга Б Площадь2 Региона3 9 8 15 Страна1 Организация 1 страны 1 Суборг Б организации 1. Регион 3 Суборга Б Площадь3 Региона3 19 18 16 Страна1 Организация 1 страны 1 Суборг Б организации 1. Регион 3 Суборга Б Район4 Региона3 9 8 17 Страна1 Организация 1 страны 1 Суборг Б организации 1. Регион 4 Суборга Б 8 7
Ответ принят как подходящий

Эта формула возвращает желаемый результат на моем листе. Формула заполняется из ячейки 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))))

Другие вопросы по теме