Банальна, на перший погляд, завдання, періодично зустрічається в роботі майже будь-якого користувача Microsoft Excel - розташувати елементи списку в зворотному порядку. При всій простоті, тут є свої "фішки" - давайте розберемо кілька варіантів її вирішення.
Спосіб 1. Ручне сортування по доп.столбцу
Це звичайно перше, що спадає на думку. Додаємо поруч з нашим списком ще один стовпець з порядковими номерами і сортуємо по цьому стовпцю по спадаючій:
Очевидний плюс такого підходу в простоті. Очевидний ж мінус в тому, що потрібно руками виконати енну кількість операцій. Якщо це разова завдання - ОК, але якщо дані змінюються щодня, то сортувати список постійно вручну вже напружує. Виходом може стати використання формул.
Спосіб 2. Зворотний порядок формулою
Оскільки формули в Excel перераховуються автоматично (якщо включений ручний режим перерахунку), то і сортування, реалізована формулами, буде відбуватися "на льоту", без будь-якого участі користувача.
Потрібна нам формула, що розміщує елементи списку в зворотному порядку може виглядати так:
Недолік цієї формули в тому, що в ній повинні жорстко ставити початок і кінець списку (осередку A2 і A9 в нашому випадку). Якщо заздалегідь точно не відомо, скільки саме елементів буде в списку, то краще використовувати інший підхід:
У цій формулі номер останньої використаної клітинки підраховується за допомогою функції СЧЁТЗ (COUNTA). тобто кількість елементів у вихідному списку може згодом змінюватися.
Мінус цього варіанту - в вихідному списку не повинно бути порожніх клітинок, тому що функція СЧЁТЗ тоді неправильно визначить номер рядка останнього елемента. Виходом може стати використання динамічного іменованого діапазону з автопідстроюванням розмірів або хитрою формули масиву:
Як легко помітити, це варіація першого способу, де діапазон узятий «із запасом» відразу до сотої рядки і номер рядка останньої заповненої комірки задається не жорстко, а обчислюється за допомогою фрагмента МАКС (($ A $ 2: $ A $ 100<>"") * СТРОКА ($ A $ 2: $ A $ 100))
Кожна клітинка в діапазоні A2: A100 перевіряється на заповненість за допомогою виразу ($ A $ 2: $ A $ 100<>""), Що дасть на виході масив значень ІСТИНА і НЕПРАВДА. Потім цей масив поелементно множиться на масив номерів рядків, одержаний за допомогою функції СТРОКА ($ A $ 2: $ A $ 100). Оскільки логічний ИСТИНУ Excel інтерпретує як 1, а брехня - як 0, то після множення ми отримаємо масив номерів заповнених осередків. А вже з нього функція МАКС (MAX) вибирає найбільше число, тобто номер останнього заповненого рядка.
І, само-собою, не забудьте після введення цієї формули натиснути не звичайний Enter. а поєднання Ctrl + Shift + Enter. щоб ввести її як формулу масиву.
Спосіб 3. Макрос
Якщо хочеться реалізувати перекладання значень осередків в зворотному порядку без додаткового стовпчика з формулами, тобто прямо в початкових осередках, то не обійтися без простого макросу.
Натисніть Alt + F11 або кнопку Visual Basic на вкладці Розробник (Developer). Вставте новий порожній модуль через меню Insert - Module і скопіюйте туди текст макросу:
Тепер, якщо виділити стовпець-список з даними і запустити наш макрос за допомогою поєднання Alt + F8 або команди Розробник - Макроси (Developer - Macros). то список розгорнеться в зворотному порядку прямо в тих же осередках, тобто на місці.