Сортування у Excel

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

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

А ще можна створювати власні сортувальні списки та сортувати за ними. Але про це згодом.

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

Тож почнемо!

Функція “Сортування”

Отже, є кілька варіантів запуску сортування.

Кнопка функції у розділі «Сортування та фільтр»

Це найзручніший, на мій погляд, спосіб запуску сортування. Клацніть на «Дані», щоб побачити значок нашої функції.

2 значки поруч дозволяють швидко відсортувати дані від А до Я та навпаки.

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

Теж саме і з чисельними значеннями.

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

Вікно функції «Сортування»

Давайте запустимо саму функцію.

Відкрилося таке вікно:

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

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

Сортування даних разом із функцією «Фільтр»

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

Наприклад, наша табличка виглядає так:

Ми можемо натиснути на стрілочку, що йде вниз, відкриється вікно функції «Фільтр», але також можна робити сортування.

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

Сортування за допомогою миші

Ще можна сортувати дані, використовуючи мишу.

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

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

Проте, найефективнішим способом буде використання самої функції «Сортування».

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

Сортування загальний розділ

Отже, запам'ятайте назавжди – щоб сортувати дані вам, завжди потрібно їх ВИДІЛЯТИ.

Від А до Я і навпаки

Допустимо, у нас є така табличка:

Давайте відсортуємо дані в ній на ім'я продавців:

• Виділяємо табличку та відкриваємо функцію «Сортування»;

• Не забувайте ставити галочку “Мої дані містять заголовки” якщо у вашій табличці вони є;

• У першому параметрі вибираємо, за яким стовпчиком вашу табличку сортуватимемо;

• У другому параметрі вибираємо «Значення»;

• У третій потрібний порядок. У нашому випадку, від А до Я;

• Підтверджуємо.

Отже, результат:

Для таких ситуацій достатньо використовувати швидкі кнопки сортування від А до Я

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

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

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

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

Все-таки я рекомендував би вам вчитися сортувати дані саме за допомогою самої функції.

За числами

У принципі, нічого кардинально різного у прикладі з числами нічого очікувати. Кроки ті самі.

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

Як це зробити?

Покрокова інструкція:

• Відкриємо функцію “Сортування”;

• У першому параметрі вибираємо стовпчик «Продажі»

• У другому – “Значення”;

• І в третьому – “за зростанням”;

• Підтверджуємо.

Результат ви можете бачити на малюнку нижче:

По даті

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

Допустимо, у нас та ж табличка:

Тепер відсортуємо все за датами.

Як це зробити?

Покрокова інструкція:

• Відкриваємо функцію “Сортування”;

• У першому параметрі – “Дата”;

• У другому – “Значення”;

• У третьому – “за зростанням”;

• Підтверджуємо.

Готово!

За кольором чи шрифтом

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

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

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

Допустимо у нас така табличка:

Давайте відсортуємо дані по оранжевому кольору:

Як це зробити?

Покрокова інструкція:

• Відкриваємо функцію “Сортування”;

• У першому параметрі вибираємо будь-який стовпець;

• У другому – “Колір осередку”;

• А в третьому оберіть колір, який буде відмінним елементом;

• У четвертому параметрі вкажіть, де буде цей колір після сортування;

• Підтвердіть.

Отже, результат на зображенні нижче:

Так само можна сортувати і за шрифтом.

Сортування за двома та більше критеріями

Ми з вами розглянули лише базові приклади. Відсортувати такі дані дуже легко. Але якщо вам потрібно виконати сортування за двома і більше критеріями?

А на додаток до цього у вас табличка на 1000+ рядків та 50+ стовпців?

У такому випадку вам потрібно використовувати вікно функції сортування та задавати кожен параметр сортування окремо та в потрібному порядку.

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

• Регіон

• Продажі

Отже, ви маєте зрозуміти. Спочатку сортування відбуватиметься по регіону, а потім із продажу. Ми просто поставимо 2 правила сортування, які виконуватимуться саме в такому порядку.

Отже, як це зробити?

Покрокова інструкція:

• Відкрийте функцію “Сортування”;

• Спочатку створимо перше правило:

• Перший параметр – “Регіон”;

• Другий – “Значення”;

• Третій – “Від А до Я”;

• Тепер натисніть «Додати рівень»;

• Тут уже створюємо друге правило:

• Перший параметр – “Продажі”;

• Другий – “Значення”;

• Третій – «По спаданням»;

• Підтвердіть.

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

Створюємо свій список сортування

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

Наприклад, у нас та сама табличка:

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

Я покажу вам, як створити свій список сортування.

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

Як це зробити:

• Клацніть “Файл”;

• “Параметри”;

• Вкладка “Додатково”;

• Промотайте вниз і знайдіть розділ “Загальні” -> “Змінити списки …”;

• А тепер створіть свій список, просто введіть порядок, наприклад, регіонів чи міст. Введіть його так, як він повинен сортуватись.

• Натисніть “Import”;

• Підтвердіть.

Отже, ви створили власний список сортування. Тепер ви можете взагалі всі файли та таблички Excel сортувати за ним.

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

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

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

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

Як це зробити?

Покрокова інструкція:

• Відкрийте функцію “Сортування”;

• У першому параметрі – “Регіон”;

• У другому – “Значення”;

• У третьому — «Список, що налаштовується…»;

• У вікні виберіть ваш нещодавно створений;

• Підтвердьте;

• Підтвердіть.

Готово, ось результат у нашому випадку:

Список необов'язково створювати, як ми робили в минулому випадку, можна зробити це прямо у вікні «список, що налаштовується…»;

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

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

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

Допустимо, ми маємо такий набір даних:

І так, дійсно, мало хто у 2021 році створює такі звіти. Але все ж таки буває таке, важливо вміти працювати з багатьма типами таблиць.

Відсортуємо “Регіон” зліва направо!

Як це зробити?

Покрокова інструкція:

• Відкриємо функцію “Сортування”;
• Клацніть на “Параметри …”;

• Вкажемо опцію «стовпці діапазони»;

• Підтвердьте;

• У першому параметрі – “Region”;

• У другому – “Значення”;

• У третьому «Від А до Я»;

• Застосовуємо.

Готово, результат на зображенні нижче:

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

Як враховувати регістр при сортуванні

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

Найчастіше вам це не потрібно, але бувають різні ситуації. Швидше за все, і такі знання колись знадобляться.

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

Як це зробити?

Покрокова інструкція:

• Відкриваємо функцію “Сортування”;

• Натисніть “Параметри …”;

• Активуйте «Враховувати регістр»;

• Ось, власне, і все, далі просто створюємо правило сортування.

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

Результат:

Як скасувати сортування

Бувають такі ситуації, коли сортування перестало бути потрібним з якихось причин. У такому разі вам потрібно знати — як видалити її.

Якщо ви зробили її недавно, ви можете просто натиснути на комбінацію гарячих клавіш CTRL + Z і вона скасується. Але якщо ви зробили її досить давно?

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

2 варіанти «відкотитися» до початкового сортування даних:

• Робимо копію файлу без сортування;

• Додаємо окремий стовпчик із порядковими номерами при початковому сортуванні.

Я вам покажу, як це робити.

Допустимо, у нас є така табличка:

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

Тепер відсортуємо дані:

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

Проблеми при сортуванні

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

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

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

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

Як я вже казав, напевно, головна проблема при сортуванні за допомогою «швидких кнопок» – це те, що Excel не враховує заголовки.

Ви можете бачити цю проблему на зображенні нижче:

Тому краще використовувати саму функцію «Сортування».

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

Excel не враховує порожні рядки (або стовпці) під час сортування

Проблема є досить масштабною.

Допустимо, у нас є така табличка:

Ви можете помітити, що рядок із порядковим номером 6 прихований.

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

Це можна побачити на зображенні нижче:

Цю проблему легко не помітити.

Як не припуститися такої помилки?

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

Перевірити набір даних можна просто виділивши їх (CTRL + A) та подивившись, чи все виділено чи щось відсутнє.

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

Використання формули для розподілу рядка або сортування на прізвище

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

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

Ось формула розподілу:

=ПРАВСИМБ(B2;ДЛСТР(B2)-ЗНАЙТИ(” “;B2))

Отже, тепер ви маєте окремий стовпець із прізвищами продавців та можете відсортувати дані щодо них.

Дуже важливо знати, яким саме способом розділено ім'я та прізвище. Якщо там тільки 1 пробіл – проблем не буде, але якщо в якомусь осередку буде подвійна пробіл або кома, або будь-який інший символ, буде помилка в розподілі, а відповідно і в сортуванні. Зважайте на це.

Якби прізвище розташовувалося на початку осередку (у нашому випадку на початку осередку ім'я), ми могли б просто використати звичайне сортування.