Якщо вам потрібно відсортувати список, то до ваших послуг є маса способів, найпростіший з яких - кнопки сортування на вкладці або в меню дані (Дані — Сортування). Однак бувають ситуації, коли сортування списку потрібно виконувати автоматично, тобто формулами. Це може знадобитися, наприклад, при створенні даних для розкривного списку, під час обчислення даних для діаграм тощо. Як сортувати список за допомогою формули на льоту?
Спосіб 1. Числові дані
Якщо список містить лише числову інформацію, то його можна легко відсортувати за допомогою функцій ОСТАННЯ (МАЛИЙ) и ЛІНІЯ (РЯДОК):
функція ОСТАННЯ (МАЛИЙ) витягує з масиву (стовпець A) n-й найменший елемент у рядку. Ті. SMALL(A:A;1) — найменше число в стовпці, SMALL(A:A;2) — друге найменше і так далі.
функція ЛІНІЯ (РЯДОК) повертає номер рядка для вказаної комірки, тобто ROW(A1)=1, ROW(A2)=2 тощо. У цьому випадку він використовується просто як генератор послідовності чисел n=1,2,3… для наш сортований список. З таким же успіхом можна було зробити додатковий стовпець, заповнити його вручну числовою послідовністю 1,2,3 … і посилатися на нього замість функції ROW.
Спосіб 2. Текстовий список і регулярні формули
Якщо список містить не цифри, а текст, то функція SMALL більше не працюватиме, тому доведеться йти іншим, трохи довшим, шляхом.
По-перше, давайте додамо службовий стовпець із формулою, де порядковий номер кожного імені в майбутньому відсортованому списку буде обчислюватися за допомогою функції COUNTIF (COUNTIF):
В англійській версії це буде:
=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
Перший член — це функція для підрахунку кількості комірок, менших за поточну. Другий — це захист на випадок, якщо будь-яке ім’я зустрічається більше одного разу. Тоді їх буде не однакова, а послідовно зростаюча кількість.
Тепер отримані числа потрібно розташувати послідовно в порядку зростання. Для цього можна скористатися функцією ОСТАННЯ (МАЛИЙ) з першого способу:
Ну і, нарешті, залишилося просто витягнути імена зі списку за номерами. Для цього можна скористатися такою формулою:
функція БІЛЬШ ЕКСПОЗИЦІЙНИЙ (МАТЧ) шукає в стовпчику B потрібний порядковий номер (1, 2, 3 і т. д.) і фактично повертає номер рядка, де цей номер знаходиться. функція ІНДЕКС (ІНДЕКС) витягує зі стовпця A ім’я під цим номером рядка.
Спосіб 3: формула масиву
Цей метод, по суті, є тим самим алгоритмом розміщення, що й у Методі-2, але реалізований за допомогою формули масиву. Для спрощення формули діапазон клітинок C1:C10 отримав назву список (виділити комірки, натиснути Ctrl + F3 і кнопку Створювати):
У клітинку E1 скопіюйте нашу формулу:
=INDEX(Список; MATCH(SMALL(COUNTIF(Список; “<"&Список); ROW(1:1)); COUNTIF(Список; "<"&Список); 0))
Або в англійській версії:
=INDEX(Список, MATCH(SMALL(COUNTIF(Список, «<"&Список), ROW(1:1)), COUNTIF(Список, "<"&Список), 0))
і штовхати Ctrl + Shift + Enterщоб ввести його як формулу масиву. Потім отриману формулу можна скопіювати по всій довжині списку.
Якщо ви хочете, щоб формула враховувала не фіксований діапазон, а мала можливість коригуватися при додаванні нових елементів до списку, то вам доведеться трохи змінити стратегію.
По-перше, діапазон списку потрібно встановити динамічно. Для цього при створенні потрібно вказати не фіксований діапазон C3:C10, а спеціальну формулу, яка буде посилатися на всі доступні значення, незалежно від їх кількості. Натисніть Alt + F3 або відкрити вкладку Формули – менеджер імен (Формули — Менеджер імен), створіть нове ім’я та в полі посилання (Довідка) введіть таку формулу (я припускаю, що діапазон даних для сортування починається з клітинки C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=ЗМІЩЕННЯ(C1,0,0;1;1000;SCHÖTZ(C1:CXNUMX);XNUMX)
По-друге, наведену вище формулу масиву потрібно буде розтягнути з запасом – з очікуванням додаткових даних, введених у майбутньому. У цьому випадку формула масиву почне видавати помилку #ЧИСЛО в клітинках, які ще не заповнені. Щоб його перехопити, можна скористатися функцією ПОМИЛКА, який потрібно додати «навколо» нашої формули масиву:
=IFERROR(INDEX(Список; MATCH(SMALL(COUNTIF(Список; “<"&Список); ROW(1:1)); COUNTIF(Список; "<"&Список); 0));»»)
=IFERROR(NDEX(Список, MATCH(SMALL(COUNTIF(Список, «<"&Список), ROW(1:1)), COUNTIF(Список, "<"&Список), 0));"")
Він виявляє помилку #NUMBER і натомість виводить пусте значення (порожні лапки).
:
- Сортувати діапазон за кольором
- Що таке формули масивів і для чого вони потрібні
- Сортування й динамічні масиви SORT у новому Office 365