Oct 06 2021 04:16 PM
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.
Oct 06 2021 09:52 PM
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})
Oct 06 2021 11:26 PM
Oct 07 2021 08:31 AM
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
Oct 07 2021 10:54 AM
Oct 07 2021 11:50 AM
Oct 07 2021 03:06 PM
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)))
Oct 08 2021 02:18 AM - edited Oct 08 2021 02:19 AM
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.
Oct 12 2021 03:19 AM
SolutionOct 12 2021 09:15 AM
Qual fórmula você escolheu e agora precisa entender como funciona?
Oct 12 2021 03:19 AM
Solution