Forum Discussion

Guilherme_Garibaldi

Copper Contributor

Oct 06, 2021

Solved

office 365 formula classificar

Boa noite a todos, alguém sabe como fazer com que a formula classificar apenas traga os 3 ou 4 primeiros informações como se fosse um ranking. Quando faz a formula classificar em uma tabela de 400 li...

Guilherme_Garibaldi
Oct 12, 2021
Obrigado pela ajuda, funcionou, apenas preciso me entender com na fórmula, mas funciona.

Guilherme_Garibaldi

Copper Contributor

Oct 07, 2021

Estou usando a formula classificarpor.

Brass Contributor

Oct 07, 2021

Here is the same approach using SORTBY. Note the use of curly braces surrounding the array constant {1;2;3;4}

=ÍNDEX(SORTBY(A2:A400,B2:B400),{1;2;3;4}) USA Excel

=ÍNDICE(SORTBY(A2:A400;B2:B400);{1;2;3;4}) Portuguese Excel

Guilherme_Garibaldi
Copper Contributor
Oct 07, 2021
entendi, pra mim a formula até funcionou, mas ela so tras a primeira coluna nao esta puxando o resto das informações.
- byundt
  Brass Contributor
  Oct 07, 2021
  Excel formulas do not work on "arrays of arrays" at the moment.
  
  But you could copy across a formula that returns the top four in each column like:
  =ÍNDEX(SORTBY(A2:A400,$B2:$B400),{1;2;3;4}) USA Excel
  
  =ÍNDICE(SORTBY(A2:A400;$B2:$B400);{1;2;3;4}) Portuguese Excel
  - byundt
    Brass Contributor
    Oct 07, 2021
    Microsoft added some new functions very recently to the Beta Channel of Microsoft 365. One of these functions is called MAKEARRAY, and can be used to return your results with a single formula in a single cell. It works with another recently added function LAMBDA, which lets you plug values into a formula.
    
    As shown below, the formula returns a three column array of results for the top four rows returned by SORTBY function. I realize that you likely do not have the newly added functions, but am posting the suggestion because it was fun to figure out, and might be useful to somebody else in the future.
    =MAKEARRAY(4,3,LAMBDA(r,c,INDEX(SORTBY(A2:C400,B2:B400),r,c)))

Resources