Como usar a função ByCol em Excel

Em Excel, podemos usar a função ByCol para iterar sobre as colunas de um intervalo ou a função ByRow para iterar sobre as linhas de um intervalo, que com a ajuda de uma função Lambda, um tipo de função criado pelo utilizador, permite-nos executar cálculos reutilizando partes de uma expressão, e aplicar a mesma fórmula a todas as colunas ou linhas de um intervalo!

Neste artigo vou mostrar-te um exemplo de como podes utilizar a função ByCol para determinar numa tabela de dupla entrada, o maior valor e o item para uma matriz.

 O desafio é, no intervalo da imagem, que é um intervalo com 2 entradas (colaborador e meses do ano) determinar o maior valor de venda e qual o colaborador que representa esse valor, para cada mês. 

Claro que é possível realizar o cenário recorrendo a funções clássicas, mas aplicando uma função Lambda, podemos usar uma variável que irá correr todas as colunas da tabela e executar a operação, desta forma, utilizamos a função ByCol com uma expressão Lambda!

Parte I – Detetar o maior valor

 O maior valor pode ser detetado com a função MÁXIMO [MAX] ou com a função MAIOR [LARGE]. No exemplo vamos utilizar a função LARGE, que de uma forma dinâmica permite identificar o maior valor, mas também outras posições, como o 2º maior, ou 3º maior valor.

=MAIOR(B2:B10;$A$13)        

No cenário estamos a assumir o ranking com a célula A13 para torná-lo dinâmico.

Parte II – Encontrar o colaborador com o maior valor

 No próximo passo vamos detetar o colaborador com o maior valor, para o cenário podemos usar várias funções entre elas a função ÍNDICE [INDEX] com ajuda da função CORRESP [MATCH] mas no exemplo podemos também usar a função FILTRAR [FILTER].

=FILTRAR($A$2:$A$10;B2:B10=MAIOR(B2:B10;$A$13))        

Parte III – Juntar toda a lógica com a função ByCol

Com a função BYCOL podemos executar o mesmo raciocínio em que a função vai iterar cada uma das colunas do intervalo, aplicando uma expressão a cada coluna, a expressão neste caso é gerada pela função LAMBDA.

 Desta forma conseguimos determinar uma fórmula mais eficaz. A função ByCol requer um intervalo a ser iterado (neste caso sobre as colunas). Usando a função ByRow, a lógica é a mesma, mas itera o conjunto de linhas de um intervalo.

 De seguida a função necessita de uma expressão a ser calculada para cada coluna da tabela. A expressão é definida através de uma Lambda, que pode armazenar uma ou mais variáveis, que depois são utilizadas no cálculo da expressão. No exemplo e para a função Lambda, a variável é apenas uma e representa a coluna a ser iterada. A expressão será a expressão usada para detetar o colaborador, neste caso a função: =FILTRAR($A$2:$A$10;B2:B10=MAIOR(B2:B10;$A$13)).

 Contudo, no exemplo, podes verificar que existe um conjunto de valores, o intervalo B2:B10 que é repetido várias vezes na expressão. Não só o intervalo é repetido como a expressão é recalculada todas as vezes ao longo do intervalo, neste caso, 12 vezes, mas dependendo do intervalo pode ser muito mais! É por esta razão que se torna mais eficaz usar uma expressão Lambda.

 =BYCOL(B2:M10;LAMBDA(coluna;FILTRAR(A2:A10;coluna=MAIOR(coluna;$A$13))))        

Explicando a lógica da expressão:

• FUNÇÃO BYCOL: Requer uma Matriz e uma expressão. A matriz é o intervalo de valores a ser iterado coluna a coluna. A Expressão é a fórmula calculada em cada coluna do intervalo.
• FUNÇÃO LAMBDA: Requer um ou mais parâmetros e um cálculo a ser executado. O parâmetro funciona como uma variável. O cálculo é definido pelo utilizador, podemos chamar-lhe uma função personalizada, que utiliza os parâmetros no cálculo definido.

A matriz, na função BYCOL é todo o intervalo que contem as vendas.

 Para a função LAMBDA, definimos o parâmetro “coluna” que é a variável utilizada na expressão.

 O cálculo é a mesma função -> FILTRAR utilizada para detetar o colaborador. Na expressão deves reparar que o intervalo repetido é substituído pela variável “coluna”.

Parte IV – Destacar as vendas do colaborador

 Podemos aproveitar e destacar as vendas do colaborador com formatação condicional. Para aplicar a formatação condicional podemos utilizar a fórmula no passo 1.