Início / coding / Perguntas / 79582378
Accepted
user30309464
Asked: 2025-04-19 20:15:23 +0800 CST

Como criar números de série agrupados usando Arrayformula?

  • 772

IMAGEM DA PLANILHA DO GOOGLE

Quais seriam as fórmulas de matriz em A1, B1, C1 e D1 (separadamente) para fornecer as colunas de saída desejadas, conforme mostrado.

Link da Planilha Google

OBSERVAÇÃO: A diferença entre "Cabeçalhos Principais" (PARENTES, AMIGOS, AMIGOS DE NEGÓCIOS) e "nomes" é que os "Cabeçalhos Principais" têm 1 letra de espaço no início e no fim.

As colunas A, B e C são todas iguais, a única diferença é que para os "Títulos Principais" a coluna A fornece alfabetos, a coluna B fornece números e a coluna C fornece algarismos romanos.

CONDIÇÃO PARA NUMERAÇÃO SERIAL:
(1) A numeração serial é feita completamente com base nas colunas: NOMES E PAX.
(2) Se PAX for 0, a saída estará em branco.

PARA as colunas A, B, C:
Quando PAX estiver em branco:
-> se for um "Título principal", a saída será um alfabeto OU
-> se for um "nome", a saída estará em branco.

PARA a coluna D:
Quando PAX estiver em branco:
-> se for um "Título principal", a saída estará em branco OU
-> se for um "nome", a saída será uma numeração serial até o próximo "Título principal", depois reiniciará de 1 até o próximo "Título principal" e assim por diante.

EDIT:
As colunas NAMES e PAX são entradas, o restante é a saída necessária.

Dados:

UM B C D NOME PAX
UM 1 EU PARENTES
1 1 1 1 Nitin 2
2 2 2 2 Amit Nawal 1
3 3 3 3 Gulzari 2
4 4 4 4 Niraj 2
Vrindaswasni 0
Vishnuji
Tulsi 0
5 5 5 5 Narnarayan 3
6 6 6 6 Kalpesh 2
B 2 II AMIGOS
7 7 7 1 Manoj 2
8 8 8 2 Prashant 2
9 9 9 3 Alok 1
Sushilji 0
10 10 10 4 Deepak 2
Ankur
Shradhanand 0
11 11 11 5 Faculdade RC 13
C 3 III AMIGOS DE NEGÓCIOS
12 12 12 1 Pawan 2
13 13 13 2 Pinky 2
14 14 14 3 Ashish 2
Bhavya
google-sheets

2 respostas

  1. Best Answer

    Esta fórmula em A2 gera o resultado para todas as colunas.

    
=ARRAYFORMULA(LET(
   names, E2:E, pax, F2:F, i, ROW(names),
   header_count, COUNTIFS(RIGHT(names), " ", i, "<=" & i),
   serial_numbering, COUNTIFS(names, "<>", pax, "<>0", i, "<=" & i),
   result, IFNA(
     IF(RIGHT(names)=" ", 
       {
        CHAR(header_count + 64),
        header_count,
        ROMAN(header_count)
       },
       IF(pax = 0, ,
         {
          serial_numbering + {0, 0, 0},
          serial_numbering - XLOOKUP(header_count, header_count, serial_numbering)
         }
       )
     )
   ), 
   result
 ))

    Como funciona

    Primeiro, fazemos uma contagem contínua dos cabeçalhos e os armazenamos em header_count:

    header_count ← COUNTIFS(RIGHT(names), " ", i, "<=" & i),

    Esta fórmula retorna uma matriz com ROWS(names)linhas, onde cada linha representa a contagem dos cabeçalhos encontrados até o momento. Um cabeçalho é qualquer valor na coluna "nomes" que termina com um espaço " ".

    Em seguida, fazemos a numeração com base nas condições mencionadas no OP (ou seja, nome não vazio e pax não 0) e atribuímos a serial_numbering:

    serial_numbering ← COUNTIFS(names, "<>", pax, "<>0", i, "<=" & i)

    Depois, examinamos cada valor em 'nomes'.

    Se for um cabeçalho, retornamos os cabeçalhos (Alfabeto, Número, Romano) com base no atual header_count:

    IF(RIGHT(names)=" ", 
   {
    CHAR(header_count + 64),
    header_count,
    ROMAN(header_count)
   },

    Se não for um cabeçalho e se pax não for 0 (ou estiver em branco*), retornamos:

    {
 serial_numbering + {0, 0, 0},
 serial_numbering - XLOOKUP(header_count,   header_count, serial_numbering)
}

    onde

    serial_numbering + {0, 0, 0}

    é uma abreviação de

    {serial_numbering, serial_numbering, serial_numbering}

    e

    serial_numbering - XLOOKUP(header_count, header_count, serial_numbering)

    é o número de série atual menos o número de série associado ao cabeçalho atual. A subtração é necessária para redefinir o número de série sempre que encontrarmos um novo cabeçalho.

    * Por causa do tipo de coerção value = 0é equivalente aOR(value = 0, ISBLANK(value))

    • 2

  2. Para colunas A:C, use reduce()e map(), assim:

    =let(
  names, E2:E,
  pax, F2:F,
  isMain_, lambda(name, regexmatch(name, "^ ")),
  seq, sequence(rows(names)),
  ordinals, reduce(tocol(æ, 2), seq, lambda(a, i, let(
    ordinal_, lambda(j, iferror(choosecols(chooserows(a, -1), j))),
    name, index(names, i),
    count, index(pax, i),
    isSeq.1, isMain_(name),
    seq.1, ordinal_(1) + isSeq.1,
    seq.2, ordinal_(2) + not(isSeq.1) * sign(count),
    vstack(a, hstack(seq.1, seq.2))
  ))),
  map(seq, lambda(i, let(
    name, index(names, i),
    count, index(pax, i),
    ordinal, index(ordinals, i, 2 - isMain_(name)),
    if(isMain_(name), 
      hstack(
        char(64 + ordinal),
        ordinal,
        roman(ordinal)
      ),
      if(count, 
        hstack(ordinal, ordinal, ordinal),
        iferror(ø)
      )
    )
  )))
)

    Veja let() , lambda() , map() , vstack() , hstack() , chooserows() , choosecols() , iferror() , sequence() , reduce() e sua planilha de exemplo .

    • 0

relate perguntas