Planilhas Google para o Excel

Question

Eu tenho essa fórmula de matriz.

=ArrayFormula(IF(P3:P="",,IFERROR(VLOOKUP(P3:P,sort({{C:C,L:L,A:A};{D:D,M:M,A:A}},3,0),2,0),UPLUS(V3:V))))

Funciona bem em folhas do google. Mas quando converti minhas planilhas do Google para Excel, a fórmula de classificação é inválida. E noto que as matrizes funcionam um pouco diferentes no excel. Então, como posso converter essa fórmula de google sheets para excel sem perder a função.

    

Answer 1

O erro está na função sort . Antes de começar, vamos ver um exemplo da função sort do Google Sheets que você usou:

Vamos ver que temos uma grade de A1 a C3, conforme abaixo:

          -------------------------------
          |    A    |    B    |    C    |
-----------------------------------------
|    1    |    3    |    11   |    61   |
-----------------------------------------
|    2    |    7    |    13   |    21   |
-----------------------------------------
|    3    |    5    |    12   |   141   |
-----------------------------------------

Então, se você emitir essa fórmula no E7:

=SORT(A1:C3, 2, 0)

Isso criará uma grade ordenada de A1:C3 com sua célula superior e mais à esquerda localizada em E7 e classificada de acordo com a segunda coluna (coluna B ou coluna 2 ). O terceiro argumento chama ascending (1) or descending (0) order da coluna 2 ou coluna B. O resultado é:

          -------------------------------
          |    E    |    F    |    G    |
-----------------------------------------
|    7    |    7    |    13   |    21   |
-----------------------------------------
|    8    |    5    |    12   |   141   |
-----------------------------------------
|    9    |    3    |    11   |    61   |
-----------------------------------------

Seu intervalo de valores é por partes (não contínuo) e {{C:C,L:L,A:A};{D:D,M:M,A:A}} é a entrada que se parece com isso quando colada em suponhamos Q14:

          -------------------------------------------------
          |       Q       |       R       |       S       |
-----------------------------------------------------------
|    14   |entire C column|entire L column|entire A column|
-----------------------------------------------------------
|14+Len(C)|entire D column|entire M column|entire A column|
-----------------------------------------------------------

onde Len (C) é o comprimento da coluna C. Note que todas as colunas devem ter o mesmo comprimento (número de linhas) para usar a função SORT. Agora você está emitindo a seguinte fórmula:

sort({{C:C,L:L,A:A};{D:D,M:M,A:A}},3,0)

Como você deve ter adivinhado, a coluna 3 é agora a coluna S, que é basicamente a fusão da coluna A duas vezes. 0 sugere que ele seja classificado em ordem decrescente.

Você precisa imitar essa funcionalidade no Excel, pois o Excel não possui a mesma função SORT.

Se você ver esta página , explica como ordenar um array usando as funções INDEX, MATCH e ROWS para executar a mesma operação no MS Excel, NO ENTANTO, os arrays do Excel não funcionam da mesma maneira que o Google Planilha. matrizes (sendo esta última muito mais simples com a função sort ). Basta olhar para a enorme fórmula de classificação para o Excel no link. Infelizmente não existe um método mais simples no Excel. Tecnicamente, ao classificar usando fórmulas no Excel, você mesmo está escrevendo o algoritmo de classificação.

Eu diria que neste cenário, você terá que isolar a classificação da fórmula principal. Como em, primeiro execute a classificação para dados dinâmicos (classificação usando fórmula) e, em seguida, aplique o VLOOKUP e outras operações nele.