Произволно сортиране на списък от ЧИСЛОВИ стойности в MS EXCEL

Сортирайте оригиналния масив от ЧИСЛА в произволен ред.

Нека е даден диапазон от клетки, съдържащи числа (A2:A8 ).

стойности

Нека сортираме числата от диапазонаA2:A8 в произволен ред. Новият списък ще бъде поставен в колонаE.

За да сортираме списъка в произволен ред, ще процедираме по следния начин (вижте примерния файл):

  • към всяка стойност от списъка свързваме произволно число (за целта използваме функцията RAND()), поставяйки го в колонатаC ;
  • за всяко произволно число от колонаC по формулата =RANK(C7;$C$7:$C$13) определяме ранга - стойността спрямо другите стойности в списъка. Ако списъкът е сортиран, тогава рангът на числото ще бъде неговата позиция. Рангът ще бъде поставен в колонаD ;
  • защото масивът от рангове е масив от поредни номера 1, 2, 3, 4, ..., тогава ще ги интерпретираме като позиции на стойността в масива.
  • използвайки формулата =INDEX($A$7:$A$13;D7) получаваме случайно сортиран списък (колонаE ).

числови

Ако има повторения в масива от произволни числа, функцията RANK() ще върне една и съща стойност на ранг за тези стойности, което ще доведе до грешка при сортиране. За щастие, тази вероятност е изключително малка: за първоначален масив от 100 елемента, вероятността за повторение ще бъде от порядъка на 1.0E-13 (10 на минус 13 степен). За визуален контрол на повторенията можете да използвате условно форматиране (изберете необходимия диапазон, създайте правило за условно форматиране Главно / Стилове / Условно форматиране / Правила за избор на клетки / Повтарящи се стойности. ).

Алтернатива без използване на функцията RANK().

За да сортирате списъка (диапазонA25:A31 ) в произволен ред, можете да продължите малко по-различно:

  • на всяка стойност от списъка да съпоставим произволно число, поставяйки го в колонаС ;
  • сортирайте списъка със случайни променливи, например във възходящ ред (в колонаD );
  • сравнявайки произволното число със стойността на оригиналния списък, сортирайте списъка в произволен ред (колонаE ).

Единствената трудност е възможното съвпадение на случайни променливи. Например, ако зададете диапазона за генериране на произволни стойности, например от 1 до 10, и трябва да сортирате списък с повече от 100 стойности, тогава ще получим гарантирани съвпадения, което няма да ни позволи да възстановим оригиналния масив.

За да получите набор от гарантирани несъвпадащи случайни стойности, можете да използвате следната формула:

Можете да сортирате списъка с произволни стойности, като използвате формулата (колонаD ): =ГОЛЯМ($C$25:$C$31;ROW()-ROW($D$24))

И накрая, следната формула =ИНДЕКС($A$25:$A$31,MATCH(D25,$C$25:$C$31,0))

търси стойност от сортирания списък и връща номера на позицията (функцията MATCH(), след което стойността от оригиналния списък се показва чрез номера на позицията (функцията INDEX().