Як сортувати в Excel (за рядками, стовпцями, кольорами, датами та числами)

Сортування даних в Excel стало досить простим за допомогою всіх вбудованих опцій.

Ви можете легко сортувати дані в алфавітному порядку, виходячи зі значення у комірках, або за кольором комірки та шрифту.

Ви також можете виконувати багаторівневу сортування стовпців (тобто сортування за стовпцем А, а потім за стовпцем В), а також сортування рядків (зліва направо).

І якщо цього було недостатньо, Excel також дозволяє вам створювати власні власні списки та сортувати на основі цього (як це круто). Таким чином, ви можете сортувати дані на основі розмірів сорочки (XL, L, M, S) або відповідей (повністю згоден, згоден, не згоден) або інтенсивності (висока, середня, низька)

Підсумок - надто багато варіантів сортування, які є у вашому розпорядженні під час роботи з Excel.

І в цьому величезному поглибленому посібнику я покажу вам усі ці варіанти сортування та кілька цікавих прикладів, де вони можуть бути корисними.

Оскільки це величезний підручник з багатьма темами, я пропоную зміст нижче. Ви можете натиснути будь -яку тему, і вона миттєво перенесе вас туди.

Доступ до параметрів сортування в Excel

Оскільки сортування - це така поширена річ, яка потрібна під час роботи з даними, Excel надає вам кілька способів доступу до параметрів сортування.

Кнопки сортування на стрічці

Найшвидший спосіб отримати доступ до параметрів сортування за допомогою кнопок сортування на стрічках.

Натиснувши вкладку Дані на стрічці, ви побачите опції "Сортувати та фільтрувати". Три кнопки ліворуч у цій групі призначені для сортування даних.

Дві маленькі кнопки дозволяють сортувати дані, як тільки ви натискаєте на них.

Наприклад, якщо у вас є набір даних з іменами, ви можете просто вибрати весь набір даних і натиснути будь -яку з двох кнопок, щоб відсортувати дані. Кнопка від A до Z сортує дані в алфавітному порядку від найнижчого до найвищого, а кнопка від Z до A сортує дані за найвищим до найнижчого в алфавітному порядку.

Ці кнопки також працюють з цифрами, датами або часом.

Особисто я ніколи не використовую ці кнопки, оскільки вони обмежені в їх здатності, і існує більша ймовірність помилок при їх використанні. Але якщо вам потрібна швидка сортування (якщо в наборі даних немає порожніх клітинок), це може бути швидким способом.

Діалогове вікно сортування

На вкладці Дані на стрічці в групі сортування є ще один значок кнопки Сортування.

Коли ви натискаєте цей значок кнопки Сортування, він відкриває діалогове вікно сортування (щось, як показано нижче).

Діалогове вікно сортування є найбільш повним рішенням для сортування в Excel. За допомогою цього діалогового вікна можна отримати доступ до всіх параметрів, пов’язаних із сортуванням.

Усі інші методи використання параметрів сортування обмежені і не пропонують повного функціоналу.

Ось чому я завжди вважаю за краще використовувати діалогове вікно, коли мені доводиться сортувати в Excel.

Основною причиною моїх уподобань є те, що при використанні діалогового вікна дуже мало шансів помилитися. Все добре структуровано і позначено (на відміну від кнопок на стрічці, де ви можете заплутатися, яку з них використовувати).

У цьому посібнику ви знайдете мене переважно за допомогою діалогового вікна для сортування даних. Це також тому, що деякі речі, які я висвітлюю в певних розділах (наприклад, багаторівневу сортування або сортування зліва направо), можна зробити лише за допомогою діалогового вікна.Комбінація клавіш - Якщо вам потрібно часто сортувати дані в Excel, я рекомендую вам вивчити комбінацію клавіш, щоб відкрити діалогове вікно сортування. Його ALT + A + S + S

Параметри сортування в меню фільтра

Якщо ви застосували фільтри до свого набору даних, ви також можете знайти параметри сортування разом із параметрами фільтра. Фільтр можна застосувати, вибравши будь -яку клітинку в наборі даних, натиснувши вкладку Дані та натиснувши піктограму Фільтр.

Припустимо, у вас є набір даних, як показано нижче, і до вас застосовано фільтр.

Коли ви натискаєте на значок фільтра для будь -якого стовпця (це маленький значок трикутника, спрямований вниз праворуч від комірки заголовка стовпця), ви також побачите деякі параметри сортування.

Зауважте, що ці параметри сортування змінюються на основі даних у стовпці. Отже, якщо у вас є текст, він покаже вам можливість сортування від A до Z або Z до A, але якщо у вас є цифри, він покаже вам варіанти сортування від найбільшого до найменшого або найменшого до найбільшого.

Параметри клацніть правою кнопкою миші

Окрім використання діалогового вікна, інший метод, який я іноді використовую,-це натискання правої кнопки миші (це також надзвичайно швидко, оскільки займає всього два кліки).

Якщо у вас є набір даних, який потрібно відсортувати, клацніть правою кнопкою миші будь-яку клітинку, і вона покаже вам параметри сортування.

Зауважте, що ви бачите деякі параметри, яких ви не бачите на стрічці або у параметрах фільтра. Хоча існує звичайна сортування за значенням та спеціальна опція сортування (яка відкриває діалогове вікно Сортування), ви також можете побачити такі параметри, як Покласти вибраний колір комірки/Колір шрифту/Піктограма форматування у верхній частині.

Я вважаю цей параметр досить корисним, оскільки він дозволяє мені швидко зібрати всі кольорові клітинки (або клітинки з іншим кольором шрифту) у верхній частині. Я часто маю дані про щомісячні витрати, які я проходжу, і виділяю деякі клітинки вручну. Потім я можу скористатися цією опцією, щоб швидко зібрати всі ці клітинки у верхній частині.

Тепер, коли я висвітлив усі способи доступу до параметрів сортування в Excel, давайте подивимося, як використовувати їх для сортування даних у різних сценаріях.

Сортування даних у Excel (текст, числа, дати)

Застереження: У більшості випадків сортування спрацьовує навіть тоді, коли в наборі даних вибрано одну клітинку. Але в деяких випадках у вас можуть виникнути проблеми (коли у вашому наборі даних є порожні клітинки/рядки/стовпці). Під час сортування даних найкраще вибрати весь набір даних, а потім відсортувати їх - лише для того, щоб уникнути можливих проблем.

Залежно від типу даних, які у вас є, ви можете використовувати параметри сортування в Excel.

Сортування за текстом

Припустимо, у вас є набір даних, як показано нижче, і ви хочете відсортувати всі ці записи на основі імені учня в алфавітному порядку.

Нижче наведено кроки для сортування цих текстових даних в алфавітному порядку:

1. Виберіть весь набір даних
2. Перейдіть на вкладку Дані
3. Натисніть на значок Сортування. Відкриється діалогове вікно Сортування.
4. У діалоговому вікні Сортування переконайтеся, що вибрано заголовки моїх даних. Якщо у ваших даних немає заголовків, ви можете зняти цей прапорець.
5. У спадному меню "Сортувати за" виберіть "Назва"
6. У спадному меню "Сортувати ввімкнено" переконайтеся, що вибрано "Значення клітинки"
7. У спадному меню Замовлення виберіть A-Z
8. Натисніть OK.

Наведені вище кроки відсортують весь набір даних і дадуть результат, як показано нижче.

Чому б просто не скористатися кнопками на стрічці?

Наведений вище спосіб сортування даних у Excel може виглядати як багато кроків у порівнянні з простою клацанням на значку сортування на стрічці.

І це правда.

Вищеописаний спосіб довший, але є немає ймовірності будь -якої помилки.

Коли ви використовуєте кнопки сортування на стрічці, є деякі речі, які можуть піти не так (і це може бути важко помітити, коли у вас великий набір даних.

Далі я обговорюватиму недоліки використання кнопок у цьому підручнику, але дозвольте мені швидко показати вам, що може піти не так.

У наведеному нижче прикладі, оскільки Excel не може розпізнати наявність рядка заголовка, він сортує весь набір даних, включаючи заголовок.

Цю проблему можна уникнути за допомогою діалогового вікна сортування, оскільки вона явно дає вам можливість вказати, чи мають ваші дані заголовки чи ні.

Оскільки діалогове вікно Сортування виключає ймовірність помилок, я рекомендую використовувати його замість усіх інших методів сортування в Excel

Сортування за номерами

Тепер я припускаю, що ви вже зрозуміли, як працює сортування тексту (розглянуто вище цього розділу).

Інші типи сортування (наприклад, на основі чисел, дат чи кольору) будуть використовувати майже ті ж самі кроки з незначними відхиленнями.

Припустимо, у вас є набір даних, як показано нижче, і ви хочете відсортувати ці дані на основі оцінок, які набрав кожен учень.

Нижче наведено кроки для сортування цих даних за цифрами:

1. Виберіть весь набір даних
2. Перейдіть на вкладку Дані
3. Натисніть на значок Сортування. Відкриється діалогове вікно Сортування.
4. У діалоговому вікні Сортування переконайтеся, що вибрано заголовки моїх даних. Якщо у ваших даних немає заголовків, ви можете зняти цей прапорець.
5. У спадному меню "Сортувати за" виберіть "Назва"
6. У спадному меню "Сортувати ввімкнено" переконайтеся, що вибрано "Значення клітинки"
7. У спадному меню "Порядок" виберіть "Від найбільшого до найменшого"
8. Натисніть OK.

Наведені вище кроки відсортують весь набір даних і дадуть результат, як показано нижче.

Сортування за датою/часом

Хоча дата та час можуть виглядати інакше, вони є не чим іншим, як цифрами.

Наприклад, в Excel число 44196 буде значенням дати 31 грудня 2021-2022 років. Ви можете відформатувати це число так, щоб воно виглядало як дата, але в бекенді в Excel воно все ще залишається числом.

Це також дозволяє розглядати дати як числа. Таким чином, ви можете додати 10 до клітинки з датою, і це дасть вам номер дати через 10 днів.

Те саме стосується часу в Excel.

Наприклад, число 44196.125 означає 3:00 31 грудня 2021-2022 років. У той час як ціла частина числа являє собою повний день, десяткова частина дасть вам час.

А оскільки дата і час - це числа, їх можна сортувати як числа.

Припустимо, у вас є набір даних, як показано нижче, і ви хочете відсортувати ці дані на основі дати подання проекту.

Нижче наведено кроки для сортування цих даних за датами:

1. Виберіть весь набір даних
2. Перейдіть на вкладку Дані
3. Натисніть на значок Сортування. Відкриється діалогове вікно Сортування.
4. У діалоговому вікні Сортування переконайтеся, що вибрано заголовки моїх даних. Якщо у ваших даних немає заголовків, ви можете зняти цей прапорець.
5. У спадному списку "Сортувати за" виберіть "Дата подання"
6. У спадному меню "Сортувати ввімкнено" переконайтеся, що вибрано "Значення клітинки"
7. У спадному меню "Порядок" виберіть "Найстаріший до найновішого"
8. Натисніть OK.

Наведені вище кроки відсортують весь набір даних і дадуть результат, як показано нижче.

Зауважте, що хоча дата та час є цифрами, Excel все одно визнає, що вони відрізняються за способом відображення. Тож, коли ви сортуєте за датою, він показує критерії сортування "Найдавніший до найновішого" та "Від найновішого до найстарішого", але коли ви використовуєте цифри, він показує "Від найбільшого до найменшого" або "Від найменшого до найбільшого". Такі дрібниці, що роблять Excel чудовим інструментом для роботи з електронними таблицями (PS: Google Таблиці не показують стільки деталей, просто м'яке сортування за A-Z чи Z-A)

Сортування за кольором комірки / кольором шрифту

Цей варіант дивовижний, і я ним користуюся постійно (можливо, трохи забагато).

У мене часто є набори даних, які я аналізую вручну та виділяю клітинки під час цього. Наприклад, я переглядав список статей у цьому блозі (який у мене є на аркуші Excel), і я виділив ті, які мені потрібно було покращити.

І як тільки я закінчу, я можу швидко відсортувати ці дані за кольорами комірок. Це допомагає мені зібрати всі ці виділені клітинки/рядки у верхній частині.

А щоб додати чудовості, ви можете сортувати за кількома кольорами. Тому, якщо я виділяю клітинки з назвами статей, які потребують негайної уваги, червоним кольором, а деякі, які пізніше можна розглядати жовтим, я можу сортувати дані, щоб показати всі червоні рядки, а потім жовтий.

Якщо вам цікаво дізнатися більше, я нещодавно написав цю статтю про сортування за кількома кольорами. У цьому розділі я швидко покажу вам, як сортувати лише за одним кольором

Припустимо, у вас є набір даних, як показано нижче, і ви хочете відсортувати за кольором і отримати всі еритроцити вгорі.

Нижче наведено кроки для сортування за кольором:

1. Виберіть весь набір даних
2. Перейдіть на вкладку Дані
3. Натисніть на значок Сортування. Відкриється діалогове вікно Сортування.
4. У діалоговому вікні Сортування переконайтеся, що вибрано заголовки моїх даних. Якщо у ваших даних немає заголовків, ви можете зняти цей прапорець.
5. У спадному списку "Сортувати за" виберіть "Дата подання" (або будь-який стовпець, у якому є кольорові клітинки). Оскільки в цьому прикладі у всіх стовпцях є кольорові клітинки, ви можете вибрати будь-яку.
6. У спадному меню "Сортувати ввімкнено" виберіть "Колір комірки".
7. У спадному меню «Порядок» виберіть колір, за яким потрібно відсортувати. Якщо у наборі даних є декілька кольорів клітинок, він покаже вам усі ці кольори тут
8. У останньому спадному меню виберіть "Зверху". Тут ви вказуєте, чи хочете ви, щоб кольорові клітинки знаходилися у верхній частині набору даних або внизу.
9. Натисніть OK.

Наведені вище кроки відсортують ваш набір даних за кольором, і ви отримаєте результат, як показано нижче.

Так само, як ми відсортували ці дані за кольором комірки, ви також можете це зробити на основі кольору шрифту та значка умовного форматування.

Багаторівнева сортування даних

Насправді набори даних рідко бувають такими простими, як той, який я використовую у цьому посібнику.

Ваш може поширюватися на тисячі рядків і сотні стовпців.

І коли у вас набори даних настільки великі, також виникає необхідність у збільшенні часточки даних і кісток. Багаторівнева сортування даних - одна з речей, які можуть вам знадобитися, якщо у вас великий набір даних.

Сортування даних на декількох рівнях означає, що ви можете сортувати набір даних за значеннями в одному стовпці, а потім знову сортувати його за значеннями в інших стовпцях.

Наприклад, припустимо, що у вас є набір даних, як показано нижче, і ви хочете відсортувати ці дані за двома критеріями:

1. Регіон
2. Продажі

Результат цього сортування на основі двох вищезазначених критеріїв дасть вам набір даних, як показано нижче.

У наведеному вище прикладі ми маємо дані, спочатку відсортовані за регіонами, а потім усередині кожного регіону, дані далі сортуються за вартістю продажів.

Це швидко дозволяє нам побачити, які торгові представники добре працюють у кожному регіоні, а які погано.

Нижче наведено кроки для сортування даних за кількома стовпцями:

1. Виберіть весь набір даних, який потрібно відсортувати.
2. Перейдіть на вкладку Дані.
3. Натисніть на піктограму Сортування (показану нижче). Відкриється діалогове вікно Сортування.
4. У діалоговому вікні Сортування переконайтеся, що вибрано заголовки моїх даних. Якщо у ваших даних немає заголовків, ви можете зняти цей прапорець.
5. У діалоговому вікні Сортування зробіть наступне
   • Сортувати за (Стовпець): Регіон (це перший рівень сортування)
   • Сортувати по: значення клітинок
   • Порядок: від А до Я
6. Натисніть Додати рівень (це додасть ще один рівень параметрів сортування).
7. На другому рівні сортування зробіть наступний вибір:
   • Потім за (Стовпець): Продажі
   • Сортувати по: Значення
   • Порядок: від найбільшого до найменшого
8. Натисніть OK

Професійна порада: У діалоговому вікні "Сортування" є функція "Рівень копіювання". Це швидко копіює вибраний рівень сортування, а потім ви можете легко змінити його. Корисно знати про цю функцію, і це може заощадити ваш час, якщо вам доведеться сортувати на основі кількох стовпців.

Нижче наведено відео, де я показую, як зробити багаторівневу сортування в Excel:

Сортування на основі користувацького списку

Хоча Excel вже має деякі загальні критерії сортування (наприклад, сортування за алфавітом за текстом, від найменшого до найбільшого або від найбільшого до найменшого з номерами, від найдавнішого до нового або від найновішого до найстарішого з датами), цього може бути недостатньо.

Щоб навести вам приклад, припустимо, у мене є наступний набір даних:

Тепер, якщо я сортую їх відповідно до регіону в алфавітному порядку, у мене є два варіанти - від A до Z або від Z до A. Нижче наведено те, що я отримую, коли сортую ці дані в алфавітному порядку від A до Z, використовуючи стовпець регіону.

Але що, якщо я хочу, щоб цей порядок сортування був на Схід, Захід, Північ, Південь?

Ви, звичайно, можете змінити дані після сортування, але це не ефективний спосіб це зробити.

Правильним способом цього буде використання користувацьких списків.

Спеціальний список - це список, який Excel дозволяє створювати, а потім використовувати так само, як і вбудовані списки (наприклад, назви місяців або назви днів тижня). Після створення власного списку ви можете використовувати його в таких функціях, як сортування даних або маркер заповнення.

Деякі приклади, де користувацькі списки можуть бути корисними, включають:

• Сортування даних за назвою регіону/міста
• Сортування за розмірами футболок - Маленька, Середня, Велика, Дуже велика
• Сортування на основі відповідей опитування - повністю згоден, згоден, нейтральний, не згоден
• Сортування за ймовірністю - висока, середня, низька

Перший крок у спробі сортування на основі користувацьких критеріїв - створити власний список.

Кроки для створення власного списку в Excel:

1. Перейдіть на вкладку Файл
2. Натисніть Параметри
3. У діалоговому вікні "Параметри Excel" виберіть "Додатково" зі списку на лівій панелі.
4. У розділі "Додатково" прокрутіть униз і виберіть "Редагувати користувацький список".
5. У діалоговому вікні "Спеціальні списки" введіть свої критерії у вікні "Записи списку". Введіть критерії, розділені комами (Схід, Захід, Північ, Південь) [Ви також можете імпортувати свої критерії, якщо вони є у списку].
6. Натисніть Додати
7. Натисніть OK

Після того, як ви виконаєте наведені вище кроки, Excel створить та збереже власний список, який можна використовувати для сортування даних.

Зауважте, що порядок елементів у користувацькому списку визначає спосіб сортування вашого списку.

Коли ви створюєте власний список в одній книзі Excel, він стає доступним для всіх книг у вашій системі. Тож вам потрібно створити його лише один раз і використовувати повторно у всіх робочих книгах.

Етапи сортування за допомогою користувацького списку

Припустимо, у вас є набір даних, як показано нижче, і ви хочете відсортувати його за регіонами (порядок сортування - Схід, Захід, Північ та Південь)

Оскільки ми вже створили власний список, ми можемо використовувати його для сортування даних.

Ось кроки для сортування набору даних за допомогою користувацького списку:

1. Виберіть весь набір даних
2. Перейдіть на вкладку Дані
3. Натисніть на значок Сортування. Відкриється діалогове вікно Сортування.
4. У діалоговому вікні Сортування переконайтеся, що вибрано заголовки моїх даних. Якщо у ваших даних немає заголовків, ви можете зняти цей прапорець.
5. У спадному меню "Сортувати за" виберіть "Регіон" (або будь-який стовпець, у якому є кольорові клітинки)
6. У спадному меню "Сортувати ввімкнено" виберіть "Значення клітинки".
7. У спадному меню "Порядок" виберіть "Спеціальний список". Як тільки ви натиснете на нього, він відкриє діалогове вікно Спеціальні списки.
8. У діалоговому вікні Спеціальні списки виберіть власний список, який ви вже створили, на лівій панелі.
9. Натисніть OK. Після цього ви побачите спеціальні критерії сортування у спадному полі порядку сортування
10. Натисніть OK.

Наведені вище кроки відсортують ваш набір даних на основі спеціальних критеріїв сортування.

Примітка: Вам не потрібно заздалегідь створювати власний список, щоб сортувати дані на його основі. Ви також можете створити його, перебуваючи у діалоговому вікні Сортування. Коли ви натискаєте Спеціальний список (на кроці 7 вище), він відкриває діалогове вікно користувацького списку. Ви також можете створити власний список.

Спеціальні списки не враховують регістр. Якщо ви хочете виконати сортування з урахуванням регістру, зверніться до цього прикладу.

Сортування зліва направо

Хоча в більшості випадків ви, ймовірно, сортуватимете за значеннями стовпців, іноді у вас також може виникнути необхідність сортувати на основі значень рядків.

Наприклад, у наведеному нижче наборі даних я хочу відсортувати його на основі значень у рядку Регіон.

Хоча цей тип структури даних не такий поширений, як колоночні дані, я все ще бачив багато людей, які працюють з такою конструкцією.

Excel має вбудовану функціональність, яка дозволяє сортувати зліва направо.

Нижче наведено кроки для сортування цих даних зліва направо:

1. Виберіть весь набір даних (крім заголовків)
2. Перейдіть на вкладку Дані
3. Натисніть на значок Сортування. Відкриється діалогове вікно Сортування.
4. У діалоговому вікні Сортування натисніть Параметри.
5. У діалоговому вікні Параметри сортування виберіть "Сортувати зліва направо"
6. Натисніть OK.
7. У спадному списку "Сортувати за" виберіть Ряд 1. Роблячи це, ми вказуємо, що сортування потрібно виконати на основі значень у рядку 1
8. У спадному меню "Сортувати ввімкнено" виберіть "Значення клітинки".
9. У спадному меню Замовлення виберіть A-Z (за бажанням також можна скористатися спеціальним списком сортування)
10. Натисніть OK.

Наведені вище кроки сортують дані зліва направо на основі значень 1 -го рядка.

Excel не розпізнає (або навіть дозволяє вказати) заголовки при сортуванні зліва направо. Тому вам потрібно переконатися, що клітинки заголовка не вибрані під час сортування даних. Якщо ви також вибрали клітинки заголовків, вони будуть відсортовані відповідно до їх значення.

Примітка: Іншим способом сортування даних справа наліво може бути транспонування даних та отримання їх у стовпчастому вигляді. Після того, як ви його отримаєте, ви можете скористатися будь -яким із методів сортування, описаних до цього часу. Після сортування можна скопіювати отримані дані та вставити їх як транспоновані дані.

Нижче наведено відео, де я показую, як сортувати дані зліва направо в Excel

Сортування з урахуванням регістру в Excel

Досі у всіх наведених вище прикладах сортування не залежало від регістру.

Але що робити, якщо ви хочете зробити сортування чутливим до регістру.

На щастя, Excel дозволяє вказати, чи потрібно сортувати з урахуванням регістру чи ні.

Примітка. Хоча в більшості випадків вам не потрібно турбуватися про сортування з урахуванням регістру, це може бути корисним, коли ви отримуєте свої дані з бази даних, наприклад Salesforce, або коли ви вручну збираєте дані та змушуєте різних людей вводити один і той самий текст у різні випадки. Сортування з урахуванням регістру може допомогти вам вести всі записи однієї особи/бази даних разом.

Припустимо, у вас є набір даних, як показано нижче, і ви хочете відсортувати ці дані на основі стовпця регіону:

Нижче наведено кроки для сортування даних за алфавітом, а також для того, щоб зробити їх чутливими до регістру:

1. Виберіть весь набір даних
2. Перейдіть на вкладку Дані
3. Натисніть на значок Сортування. Відкриється діалогове вікно Сортування.
4. У діалоговому вікні Сортування переконайтеся, що вибрано заголовки моїх даних. Якщо у ваших даних немає заголовків, ви можете зняти цей прапорець.
5. Натисніть кнопку Параметри
6. У діалоговому вікні Параметри сортування встановіть прапорець "Чутливий регістр"
7. Натисніть OK.
8. У спадному списку "Сортувати за" виберіть "Регіон"
9. У спадному меню "Сортувати ввімкнено" виберіть "Значення клітинки".
10. У спадному списку порядку виберіть А-Я
11. Натисніть OK.

Вищезазначені кроки не тільки сортуватимуть дані за алфавітом за регіонами, але й зробили б їх чутливими до регістру.

Ви отримаєте отримані дані, як показано нижче:

При сортуванні від A до Z нижній регістр розміщується над текстом верхнього регістру.

Отримання оригінального порядку сортування

Часто під час сортування даних у Excel вам може знадобитися повернутися до попереднього або початкового порядку сортування та розпочати заново,

Хоча ви можете використовувати функцію скасування в Excel (за допомогою Control Z), щоб повертатися за кроком за раз, це може викликати збентеження, якщо ви вже зробили кілька дій після сортування даних.

Крім того, скасування працює лише доки книга не відкрита, але коли ви збережете та закриєте її та відкриєте пізніше, ви не зможете повернутися до початкового порядку сортування.

Ось два простих способи переконатися, що ви не втратите вихідний порядок сортування та повернете його навіть після сортування даних:

1. Зробіть копію вихідного набору даних. Це те, що я рекомендую вам зробити, навіть якщо вам не потрібен оригінальний порядок сортування. Ви можете мати книгу з усіма даними, а потім просто створити копію книги та попрацювати над копією. Коли я працюю над критичними наборами даних, я щодня роблю копію (з датою або номером версії як частиною назви книги).
2. Додайте стовпець із серією чисел. Ця серія чисел переплутається під час сортування даних, але якщо ви хочете повернутися до вихідних даних, ви можете відсортувати їх за цією серією чисел.

У цьому розділі дозвольте мені швидко показати вам, що означає додавання ряду та використання його для повернення оригінального порядку сортування.

Припустимо, у вас є набір даних, як показано нижче:

Щоб переконатися, що у вас є спосіб повернути ці дані після їх сортування, додайте допоміжний стовпець і додайте до нього ряд чисел (як показано нижче).

Ось підручник з різними способами швидкого додавання стовпця з числами

Коли у вас є стовпець -помічник, переконайтеся, що ви включили його під час сортування цього набору даних.

Припустимо, я сортую ці дані на основі регіону і в кінцевому підсумку отримую дані, як показано нижче:

Тепер, якщо я хочу повернутися до вихідного набору даних, я можу просто знову відсортувати ці дані, але на основі допоміжного стовпця (від низького до високого).

Просто … чи не так?

Якщо ви не хочете, щоб допоміжний стовпець відображався, ви можете приховати його або створити резервну копію, а потім видалити її.

Деякі поширені проблеми під час сортування даних у Excel

На початку цієї статті я показав вам різні способи сортування даних у Excel (включаючи кнопки сортування на стрічці, параметри правої кнопки миші, параметр фільтра та діалогове вікно сортування).

І ще раз повторю: використання діалогового вікна сортування зводить до мінімуму ймовірність виникнення проблем або помилок.

Тепер дозвольте мені показати вам, що може піти не так, коли ви використовуєте кнопку сортування зі стрічки (ті, що показані нижче)

Не ідентифікуються заголовки стовпців

Припустимо, у вас є набір даних, як показано нижче:

Це виглядає як пристойно відформатований набір даних із заголовками, чітко відформатованими кольором комірки та жирним шрифтом.

Тому, коли ви сортуєте ці дані за іменами (за допомогою кнопок сортування на стрічці), ви очікуєте, що заголовок залишиться у верхній частині, а решта даних буде відсортовано.

Але що відбувається, коли ви це робите - заголовок також вважається звичайними даними і сортується (як показано нижче).

Хоча Excel досить розумний, щоб розпізнавати заголовки, у цьому прикладі він не зміг цього зробити. Під час використання кнопок значка сортування на стрічці не можна було вручну вказати, що ці дані мають заголовки.

Примітка: Ця проблема виникає, коли у мене є набір даних, і я додаю та форматую заголовки та сортую його. Зазвичай Excel був би достатньо розумним, щоб визначити, що в наборі даних є заголовки (особливо коли тип даних заголовка та дані у стовпці відрізняються). Але в цьому випадку цього не вдалося зробити, коли я додав заголовок і одразу відсортував. Якщо я збережу цю книгу, закрию її та відкрию знову, Excel так чи інакше впізнає перший рядок і заголовок.

Хоча в більшості випадків це може не бути проблемою, все ж є ймовірність, що під час використання значків сортування на стрічці, використання діалогового вікна усуває цю проблему, оскільки ви можете вказати, що у вас є заголовки.

Не визначаються порожні рядки/стовпці

Це питання сортування є дещо складнішим - і набагато більш поширеним, ніж ви могли собі уявити.

Припустимо, у вас є набір даних, як показано нижче. Зверніть увагу, що рядок 6 прихований (і це порожній рядок).

Тепер, якщо я виділю будь -яку клітинку в перших чотирьох рядках даних (ті над прихованим порожнім рядком) і відсортую ці дані за допомогою кнопок сортування, це буде сортувати лише перші чотири записи в наборі даних (рядок 2 до 5). Подібним чином, якщо я виділю будь -яку клітинку в наборі даних під прихованим порожнім рядком, вона відсортує лише останні сім рядків.

І якщо ви не шукаєте його конкретно, ви, ймовірно, пропустите цю жахливу помилку.

Як переконатися, що ви не зробите цю помилку сортування?

Щоб переконатися, що ви не стали жертвою цієї проблеми, вам потрібно перевірити свій набір даних перед сортуванням.

Виберіть весь набір даних перед сортуванням даних.

Ви можете зробити це, вибравши будь -яку клітинку в наборі даних, а потім скориставшись комбінацією клавіш - Control + A.

Якщо у вашому наборі даних є порожні рядки/стовпці, весь набір даних не буде вибрано. Ви можете це визначити, швидко відсканувавши контур виділення.

Якщо ви бачите, що за межами виділення залишилися деякі дані, ви можете вибрати їх вручну.

Щоб уникнути цієї проблеми, найкраще спочатку показати всі приховані рядки та стовпці, а потім перейти до сортування даних, вибравши весь набір даних або спочатку видаливши порожні рядки.

Часткове сортування (на основі прізвища)

Іноді у вас може бути набір даних, який потрібно відсортувати на основі частини тексту.

Наприклад, припустимо, що у мене є набір даних, як показано нижче, і я хочу відсортувати ці дані за прізвищем.

Для цього мені потрібно розділити ці дані так, щоб у мене були лише прізвища в одному стовпці. Коли я його маю, я можу використовувати цей стовпець для сортування даних.

Нижче наведена формула, яка може дати мені прізвище:

= ПРАВО (B2, LEN (B2) -FIND ("", B2))

Це дасть вам результат, як показано нижче.

Тепер ви можете використовувати стовпець Прізвище для сортування цих даних.

Після цього ви можете видалити стовпець з прізвищем або приховати його.

Зауважте, що вищенаведена формула працює, якщо у вас є імена, які мають лише ім’я та прізвище, і між якими немає подвійних пробілів. Якщо у вас є інші дані про імена, вам доведеться скорегувати формулу. Ви також можете використовувати текст до стовпця, щоб швидко розділити імена на основі символу пробілу як роздільника.

Це лише один із прикладів сортування на основі часткових даних. Інші приклади можуть включати сортування за містами за адресою або ідентифікатором працівника на основі кодів відділів.

Крім того, якщо текст, на основі якого потрібно сортувати, знаходиться на початку тексту, ви можете використовувати лише звичайну функцію сортування.

Інші приклади сортування (за допомогою формули та VBA)

Поки що в цьому підручнику я розглянув приклади, коли ми використовували вбудовану функцію сортування в Excel.

Автоматична сортування за формулою

Коли ви використовуєте вбудовану функцію сортування, а потім вносите будь-які зміни в набір даних, вам потрібно відсортувати її ще раз. Функціональність сортування не є динамічною.

Якщо ви хочете отримати набір даних, який автоматично сортує щоразу, коли відбуваються будь -які зміни, ви можете скористатися формулами для цього.

Зауважте, що для цього вам потрібно зберігати вихідний набір даних та відсортований набір даних окремо (як показано нижче)

У мене є детальний підручник, де я розповідаю про сортування за алфавітом за формулами. Він показує два методи для цього - використання допоміжних стовпців та використання формули масиву.

Excel також представив формулу динамічного масиву SORT, яка може легко це зробити (без допоміжного стовпця або складної формули масиву). Але оскільки це зовсім нове, можливо, ви не матимете доступу до нього у своїй версії Excel.

Сортування за допомогою VBA

І, нарешті, якщо ви хочете обійти все діалогове вікно сортування або інші параметри сортування, ви можете використовувати VBA для сортування даних.

У наведеному нижче прикладі у мене є набір даних, який сортує дані, коли я двічі натискаю на заголовок стовпця. Це полегшує сортування і може бути використано на інформаційних панелях, щоб зробити його більш зручним для користувачів.

Ось детальний підручник, де я розповідаю про те, як сортувати дані за допомогою VBA та створювати щось, як показано вище.

Я намагався навести багато прикладів, щоб показати вам різні способи сортування даних у Excel та все, що вам потрібно мати на увазі під час цього.

Сподіваюся, цей підручник був вам корисним.