SOLVED

office 365 formula classificar

Copper Contributor

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 linhas ou mais ela traz todas as informações em ordem que eu indico, mas so quero algumas.

10 Replies

I assume you are using the SORT function to return your results. If so, consider wrapping it with the INDEX function.

 

The following formula returns the first four values returned by SORT in a column. If you want them returned as a row, use commas instead of semicolons in the array constant {1;2;3;4}. Formula 3 using INDICE was produced using the Functions Translator Add-in using my USA formula as input and Portuguese as the output--I hope it has the list separators correct.

=INDEX(SORT(A2:A400,,1),{1;2;3;4})
 
=ÍNDICE(SORT(A2:A400;;1);{1;2;3;4})

 

Interesting add-in. My guess is that 1 will create a column with English settings, whilst 3 will produce a row with Portuguese settings.

Estou usando a formula classificarpor.

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

entendi, pra mim a formula até funcionou, mas ela so tras a primeira coluna nao esta puxando o resto das informações.
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

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

 

@byundt

Something that troubles me about the formula is that, at least from superficial examination, it would appear to be performing the sort over 400 rows 12 times (a good thing it is not 40,000 rows)..

A way of changing the appearance of the formula (for better or worse?) is to wrap it within LET, so allowing the Lambda function to be named. 

Refactoring such formulae is like picking one's path through an alligator swamp; 'nested arrays' and 'array of arrays' seem to lurk as errors just waiting to snap at the unwary.

best response confirmed by Guilherme_Garibaldi (Copper Contributor)
Solution
Obrigado pela ajuda, funcionou, apenas preciso me entender com na fórmula, mas funciona.

Qual fórmula você escolheu e agora precisa entender como funciona?

1 best response

Accepted Solutions
best response confirmed by Guilherme_Garibaldi (Copper Contributor)
Solution
Obrigado pela ajuda, funcionou, apenas preciso me entender com na fórmula, mas funciona.

View solution in original post