У даному прикладірозглянемо найшвидші та ефективні методисортування даних в Excel за різними критеріями: за зростанням або зменшенням чисел, за датами від старих конових і від нових до старих, за алфавітом від А до Я, а також у зворотному напрямку. алфавітному порядку.

Сортування рядків по стовпцю в Excel

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

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

Перейдіть курсором клавіатури на будь-яку комірку в стовпці G (можна навіть на заголовок стовпця G1). А потім виберіть інструмент: «ГОЛОВНА»-«Редагування»-«Сортування та фільтр»-«Сортування за спаданням».

В результаті дані впорядкувалися та організувалися у певну структуру:

Як виявилось, найбільшу премію отримав співробітник «Євген Пруткий».

Увага! Список, який буде сортований, не повинен містити об'єднаних осередків.

Примітка. Інструмент для базового сортування діапазонів доступний також на закладці: «ДАНІ»-«Сортування за зростанням/зменшенням».

І вбудований в меню заголовків таблиць в режимі автофільтра або розумної таблиці:

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

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

Якщо таблицю необхідно буде привести до початкового вигляду, тоді перед кожним сортуванням створюйте стовпець нумерації рядків. У цьому прикладі – це стовпець А (№п/п). Часто необхідно привести таблицю до початкового вигляду після виконання з нею численних та складних сортувань даних з різним стовпцям, критеріям чи умовам. Тоді достатньо виконати сортування по стовпцю з номерами рядків, щоб таблиця набула свого первісного вигляду.

Як у Excel зробити сортування у стовпці

Іноді потрібно виконати незалежне сортування лише всередині одного стовпця таблиці без прив'язки до інших стовпців або їх зміни. Наприклад, стовпець B «Ім'я». Таке завдання вирішується так само просто:

1. Виділіть цілий стовпець B, клацнувши лівою мишкою по заголовку стовпця листа.
2. Виберіть інструмент: «ГОЛОВНА»-«Редагування»-«Сортування та фільтр»-«Сортування за спаданням».
3. У діалоговому вікні «Виявлені дані в невказаному діапазоні», виберіть опцію: «сортувати в межах зазначеного виділення». І натисніть кнопку «Сортування».

Значення осередків в інших стовпцях залишилися без змін:

Такий спосіб необхідно застосовувати лише як допоміжний.



Сортування за кількома стовпцями Excel

Як настроїти сортування в Excel? Допустимо ми хочемо сортувати співробітників за датою народження (від старих до нових) відповідно до кожного міста (місця народження). Для цього:

Таблиця набула такої структури:

Спочатку її значення відсортовано щодо назв міст за абеткою. Далі щодо кожного міста розподілені дати народження від старих до нових.

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

Тоді в діалоговому вікні інструмента «Сортування, що налаштовується» слід відключити опцію «Мої дані мають заголовки стовпців». В результаті чого як критерії для кожного першого випадаючого списку секції «Стовпець» у кожному рівні, будуть використані заголовки стовпців робітника листа Excel(Стовпець А, Стовпець B, C і т.д.). А при застосуванні всіх параметрів у сортування будуть включені всі рядки та осередки вихідного діапазону (фрагменту таблиці), що є правильним результатом у цьому випадку.

Примітка. Кнопка виклику інструменту для настроюваного сортування також доступна на закладці «ДАНІ»-«Сортування».

Таблиці даних, які ми отримуємо від різних співробітників, у більшості випадків не впорядковані і працювати з ними без організації – складно. Виникає необхідність відсортувати: текстові значення за абеткою, числові – за зростанням, а дати від старих до нових. Сортувати вручну всі стовпці окремо – це не найкраще рішеннядля цього завдання. Особливо якщо стовпців понад тисячу. Тому краще скористатися спеціальним вбудованим інструментом Excel - сортуванням, що налаштовується. Цей інструментавтоматичне сортує дані одночасно за кількома стовпцями. Він настільки еластичний, що його можна застосувати практично для будь-якого сортування з будь-яким рівнем складності.

Доброго дня, дорогі читачі! Сьогодні поділюся тим, як сортувати за алфавітом в excel. Почати варто з даного типу, а потім будуть описані й інші варіанти. Уперед!

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

• Потім, на вкладці під назвою «Головна», у розділі «Редагування» клацаємо по кнопці «Сортування та фільтр». А в списку вибираємо пункт з назвою «Сортування від А до Я».
• Варто звернути увагу, що випадає віконце, в якому потрібно вибрати: по всій таблиці або в межах виділення.
• Якщо необхідно здійснити лише в одному із стовпчиків, слід проставити галочку на пункт «Сортувати в рамках виділення».

Чи легко? Неймовірно!

Залежно від зростання та спадання

Даний метод сортування здійснюється так само, як за абеткою. Відмінність буде лише у найменуванні функцій: «По зростанню», і навіть, «За спаданням».

По кількох полях файлу

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

Клацніть по вкладці «головна», після – «Сортування та фільтр», потім – «Сортування, що настроюється».

Шапка вікна містить такі кнопки:

1. Додавання рівня;
2. Видалення рівня;
3. Копіювання рівня;
4. Клавіші вгору та вниз.

Говорячи про те, яким саме чином буде здійснено подібне сортування, варто зауважити, що Ексель піде за списком зверху. Перший рівень є найпріоритетнішим. Наприклад, ми вибрали «сортувати людей залежно від імені від А до Я – виконано.

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

Отже, насамперед, було відсортовано рядки залежно від імен. Після цього слід звернути увагу на ім'я Дар'я. Їх 4, проте розташовуються вони у тій послідовності, як і були в таблиці спочатку.

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

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

Представляю вашій увазі просту у використанні надбудову (макрос) для Excelяка дозволяє відсортувати листи робочої книги Excel як за зростанням, так і за спаданням . Якщо хтось стикався з не цілком коректним сортуванням, коли відразу слідом за Лист1 йде Лист11, а не Лист2, може не хвилюватися. У цій надбудові така проблема вирішена, вона однаково якісно відсортує листи, назви яких складаються з літер, цифр і поєднання букв, цифр, пробілів і символів. Причому зробить це навіть зі прихованими листами. Надбудова правильно і швидко сортує аркуші з іменами, що складаються з одних лише цифр, таких як 1, 2, 3, 4, 5, а також аркуші з іменами виду 1-1-1, 1-1-2, 1-1 -3.

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

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

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

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

Примітки:

Сортування текстових значень

Примітки: Можливі проблеми

Сортування чисел

Примітки:

Сортування значень дати та часу

Примітки: Можливі проблеми

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

Можливо, ви захочете виконати сортування за двома або декількома стовпцями або рядками, щоб згрупувати дані з однаковими значеннямив одному стовпці або рядку, а потім відсортувати ці групи з однаковими значеннями по іншому стовпцю або рядку. Наприклад, якщо є стовпці "Відділ" та "Співробітник", можна спочатку виконати сортування за стовпцем "Відділ" (для угруповання всіх співробітників по відділах), а потім - на ім'я (для розташування імен співробітників кожного відділу в алфавітному порядку). Можна одночасно виконувати сортування за 64 стовпцями.

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

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

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

Сортування за списками, що налаштовуються

Для сортування у порядку, заданим користувачем, можна застосовувати списки, що настроюються. Наприклад, стовпець може містити значення, за якими ви хочете сортувати, такі як "Високий", "Середній" і "Низький". Як налаштувати сортування, щоб спочатку відображалися значення "Високий", потім - "Середній", а в кінці - "Низький"? Якщо відсортувати їх у алфавітному порядку (від А до Я), то значення "Високий" відображатимуться вгорі, але за ними виявляться значення "Низький", а не "Середній". А при сортуванні від Я до А значення "Середній" виявляться в самому верху. Насправді значення "Середній" завжди, незалежно від порядку сортування, повинні відображатися в середині. Ви можете вирішити цю проблему, створивши список, що настроюється.

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

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

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

Примітка:Таблиці не підтримують можливість сортування зліва направо. Спочатку перетворіть таблицю в діапазон: виділіть у ній будь-яку комірку та виберіть пункти Робота з таблицями > Перетворити на діапазон.

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

Сортування за значенням у стовпці

Щоб виконати сортування щодо значення в стовпці, наприклад частини коду (789- WDG-34), прізвища (Регіна Покровська) або імені (Покровська Регіна), спочатку необхідно розбити стовпець на дві або кілька частин таким чином, щоб значення, за яким потрібно виконати сортування, виявилося у власному стовпці. Щоб розбити значення в осередку на частини, можна скористатися текстовими функціямичи майстром текстів. додаткові відомостіта приклади див. у статтях Розбивка тексту по різних осередках та Розбивка тексту по різних стовпцях за допомогою функцій.

Сортування меншого діапазону в межах більшого

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

На щастя, Excel видає попередження, якщо виявляє подібну спробу:

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

Якщо результат не відповідає бажаному, натисніть кнопку Скасувати .

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

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

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

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

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

Перевірте поточний параметр мовного стандарту Порядок сортування залежить від вибраної мови. Переконайтеся, що на панелі керуванняв розділі Регіональні параметриабо Мова та регіональні стандартизаданий правильний мовний стандарт. Щоб дізнатися, як змінити мовний стандарт, див. довідку Microsoft Windows.

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

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

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

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

Примітка:У Excel Online можна сортувати таблиці та діапазони за одним або декількома стовпцями (як за зростанням, так і за спаданням), але не можна сортувати за рядками (сортування зліва направо).

Сортування даних у таблиці

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

Сортування даних у діапазоні

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

Наприклад, у вас є таблиця зі стовпцями "Відділ" та "Співробітник". Можна спочатку виконати сортування по стовпцю "Відділ" для угруповання всіх співробітників відділів, а потім - на ім'я для розташування імен співробітників кожного відділу в алфавітному порядку.

Щоб виконати настроювання сортування в Excel Online, дані потрібно відформатувати як таблицю Excel:

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

Ви не знаєте, який порядок сортування вибрано?

Перевірте напрямок стрілки.

Сортування за зростаннямпозначена стрілкою, спрямованою нагору.

Сортування за зменшеннямпозначена стрілкою, спрямованою донизу.

Порада:Додавши до таблиці нові дати, виділіть стовпець і повторно застосуйте порядок сортування.

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

Якщо ви хочете відсортувати на прізвище список імен, які починаються з імені (наприклад, "Регіна Покровська"), вам потрібно змінити їх таким чином, щоб вони починалися з прізвища (наприклад, "Покровська, Регіна"). Для цього ми будемо використовувати декілька формул.

Тут показано кілька формул у осередках B2, C2 і D2, які ми об'їмо в осередку E2.

Ось опис наведених вище формул.

		Опис	Результат
	ЗНАЙТИ(" ",A2)	Знаходить позицію першого пропуску в рядку "Регіна Покровська"
	ПСТР("A2,B2+1,30)	Знаходить останні 30 символів у рядку "Регіна Покровська", починаючи з першої позиції після пробілу (8) (це "П" у слові Покровська). Ви можете запросити набагато більше символівчим вам потрібно.	Покровська
	ЛЕВСІМВ(A2,B2-1)	Знаходить ім'я у рядку "Регіна Покровська", повертаючи крайні ліві символи, що знаходяться перед першим пробілом (на позиції 7). Таким чином, повертаються перші 6 знаків (позиція 7 мінус 1) – "Регіна".
		Поєднує "Покровська", кому з пробілом (", ") і "Регіна", щоб повернути рядок "Покровська, Регіна"	Покровська, Регіна

У Е2 ми перетворили рядок "Регіна Покровська" на "Покровська, Регіна". Тепер перетягніть формулу в осередку E2 вниз через осередки, що знаходяться нижче, щоб скопіювати формулу і перетворити решту імен зі стовпця A.

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

Виділіть комірки, починаючи з E2, і натисніть клавіші CTRL+C, щоб скопіювати їх.

На вкладці Головнаклацніть стрілку під командою Вставитита виберіть пункт Вставити значення.

Нарешті, щоб відсортувати імена, виділіть комірки та натисніть За зростаннямабо За зменшенням.

Видалення або скасування сортування у стовпці

В Excel Online, щоб скасувати будь-яке сортування, яке ви застосували до списку або таблиці, можна використовувати команду Скасувати на вкладці Головна. У цьому прикладі зображені дані у списку, який спочатку був упорядкований містами в порядку зростання, а потім відсортований за чисельністю населення в порядку зменшення. Скасування дії поверне початковий порядок даних (у містах).

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

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

Збереження та повторне використанняпорядку сортування

В Excel Online можна сортувати таблиці та діапазони, але ви не зможете зберегти або повторно застосувати порядок сортування до іншого діапазону або іншої таблиці даних. У класичному додатку Excel також неможливо.

додаткові відомості

Ви завжди можете поставити запитання фахівцеві Excel Tech Community, попросити допомоги у спільноті

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

Типи даних, що сортуються, і порядок сортування

Сортування числових значень у Excel

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

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

Сортування текстових значень у Excel

"Сортування від А до Я" - сортування даних щодо зростання;

"Сортування від Я до А" - сортування даних щодо спадання.

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

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

Можна відсортувати текстові дані з урахуванням регістру. Для цього необхідно в параметрах сортування встановити прапорець у полі "Враховувати регістр".

Зазвичай літери верхнього регіструмають менші номери, ніж літери нижнього регістру.

Сортування значень дати та часу

"Сортування від старих до нових" - це сортування значень дати і часу від раннього значення до найпізнішого.

"Сортування від нових до старих" - це сортування значень дати і часу від найпізнішого значення до раннього.

Сортування форматів

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

Сортування за списком, що налаштовується

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

Параметри сортування

Сортування по стовпцю

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

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

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

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

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

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

Крім усього іншого при сортуванні можна враховувати або не враховувати регістр.

Надбудова для сортування даних у Excel

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