Blog Posted by on 2-2-2025 in Взаємодія в роботі

Сортування даних у Excel – інструмент для представлення інформації у зручному для користувача вигляді.

Числові значення можна відсортувати за зростанням та спаданням, текстові – за алфавітом та у зворотному порядку. Доступні варіанти – за кольором та шрифтом, у довільному порядку, за кількома умовами. Сортуються стовпці та рядки.

Порядок сортування в Excel

Існує два способи відкрити меню сортування:

  1. Клацніть правою кнопкою миші по таблиці. Вибрати «Сортування» та спосіб.
  2. Відкрити вкладку «Дані» – діалогове вікно «Сортування».

Методи сортування, що часто використовуються, представлені однією кнопкою на панелі завдань:

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

  1. Щоб програма правильно виконала завдання, виділяємо потрібний стовпець у діапазоні даних.
  2. Далі діємо залежно від поставленого завдання. Якщо потрібно виконати просте сортування за зростанням/зменшенням (алфавітом або назад), то достатньо натиснути відповідну кнопку на панелі завдань. Коли діапазон містить більше одного стовпця, Excel відкриває діалогове вікно виду: Щоб збереглася відповідність значень у рядках, вибираємо дію «автоматично розширити виділений діапазон». Інакше відсортується лише виділений стовпець – структура таблиці порушиться.

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

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

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

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

  1. Виділяємо стовпець – права кнопка миші – "Сортування".
  2. Із запропонованого списку вибираємо «Спочатку комірки з виділеним кольором».
  3. Погоджуємося «автоматично розширити діапазон».

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

У вікні вводимо необхідні параметри:

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

За таким же принципом сортуються дані щодо шрифту.

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

Як встановити порядок вторинного сортування в Excel? Для вирішення цього завдання потрібно задати кілька умов сортування.

  1. Відкриваємо меню «Сортування, що настроюється». Призначаємо перший критерій.
  2. Натискаємо кнопку «Додати рівень».
  3. З'являються вікна для введення даних наступної умови сортування. Заповнюємо їх.

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

Сортування рядків у Excel

За умовчанням сортуються дані про стовпці. Як здійснити сортування за рядками в Excel:

  1. У діалоговому вікні «Сортування, що настроюється» натиснути кнопку «Параметри».
  2. У меню вибрати «Стовпці діапазону».
  3. Натисніть кнопку ОК. У вікні «Сортування» з'являться поля для заповнення умов рядків.

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

Випадкове сортування в Excel

Вбудовані параметри сортування не дозволяють розташувати дані у стовпці випадковим чином. З цим завданням впорається функція СЛЧИС.

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

Ставимо курсор у сусідній осередок (зліва-праворуч, не важливо). У рядок формул вводимо СЛЧИС(). Тиснемо Enter. Копіюємо формулу на весь стовпець – отримуємо набір випадкових чисел.

Тепер відсортуємо отриманий стовпець за зростанням/зменшенням – значення у вихідному діапазоні автоматично розташуються у випадковому порядку.

Динамічний сортування таблиці в MS Excel

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

  1. Є набір простих чисел, які потрібно відсортувати за зростанням.
  2. Ставимо курсор у сусідній осередок і вводимо формулу: = НАЙМЕНШИЙ (A: A; РЯДОК (A1)). Саме так. Як діапазон вказуємо весь стовпець. А як коефіцієнт – функцію РЯДКУ з посиланням на першу комірку.
  3. Змінимо у вихідному діапазоні цифру 7 на 25 – «сортування» за зростанням також зміниться.

Якщо потрібно зробити динамічне сортування за спаданням, використовуємо функцію НАЙБІЛЬШИЙ.

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

  1. Вихідні дані – список деяких назв у довільному порядку. У прикладі – список фруктів.
  2. Виділяємо стовпець і даємо йому ім'я "Фрукти". Для цього в полі імен, що знаходиться біля рядка формул, вводимо потрібне нам ім'я для присвоєння його до виділеного осередку.
  3. У сусідньому осередку (у прикладі – у В5) пишемо формулу: Оскільки перед нами формула масиву, натискаємо поєднання Ctrl+Shift+Enter. Розмножуємо формулу на весь стовпець.
  4. Якщо у вихідний стовпець додаватимуться рядки, то вводимо трохи модифіковану формулу: Додамо в діапазон "фрукти" ще одне значення "помело" і перевіримо:

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

  • Створити таблицю
  • Форматування
  • Функції Excel
  • Формули та діапазони
  • Фільтр та сортування
  • Діаграми та графіки
  • Зведені таблиці
  • Друк документів
  • Бази даних та XML
  • Можливості Excel
  • Налаштування параметри
  • Уроки Excel
  • Макроси VBA
  • Завантажити приклади

Сортування даних в Excel за рядками та стовпцями за допомогою формул - Kozak

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

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

  1. Виділіть комірку в стовпці, який потрібно відсортувати.
  2. На вкладці Головна у групі Сортування та фільтр виконайте одну з наведених нижче дій.
  3. Щоб швидко сортувати за зростанням, виберіть

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

  • Переконайтеся, що всі дані збережені у текстовому вигляді. Якщо стовпець, який підлягає сортуванню, містить числа, збережені у числовому форматі, і числа, збережені у текстовому форматі, необхідно відформатувати всі комірки або числа, або текст. В іншому випадку, числа, збережені у числовому форматі, після сортування виявляться вище чисел, збережених у текстовому форматі. Щоб відформатувати всі вибрані дані у вигляді тексту, натисніть клавіші CTRL+1 , щоб відкрити діалогове вікно Формат осередків , перейдіть на вкладку Число , а потім у розділі Категорія виберіть Загальні, Число або Текст.
  • Видаліть усі початкові прогалини. Іноді дані, що імпортуються з інших програм, можуть доповнюватися початковими пробілами. Перш ніж виконувати сортування, видаліть їх із даних. Це можна зробити вручну або за допомогою функції СЖПРОБЕЛИ.
  1. Виділіть комірку в стовпці, який потрібно відсортувати.
  2. На вкладці Головна у групі Сортування та фільтр виконайте одну з наведених нижче дій.
  3. Щоб відсортувати від низького до високого, виберіть

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

  1. Виділіть комірку в стовпці, який потрібно відсортувати.
  2. На вкладці Головна у групі Сортування та фільтр виконайте одну з наведених нижче дій.
  3. Щоб відсортувати більш ранню дату або час, виберіть

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

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

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

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

  • Щоб виконати сортування за текстом, числом або датою та часом, виберіть Значення осередків.
  • Щоб виконати сортування за форматом, виберіть варіант Колір осередку, Колір шрифту або Значок осередку.
  • Для текстових значень виберіть Від А до Я або Від Я до А.
  • Для числових значень виберіть За зростанням або За спаданням.
  • Для дат та часу виберіть Від старих до нових або Від нових до старих.
  • Щоб виконати сортування за списком, виберіть пункт Настроюваний список.

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

  1. Виділіть комірку в стовпці, який потрібно відсортувати.
  2. На вкладці Дані у групі Фільтр & сортування виберіть Сортувати.
  • Щоб перемістити колір осередку, колір шрифту або піктограму вгору або вліво, виберіть пункт Зверху для сортування по стовпцю та Ліворуч для сортування за рядком.
  • Щоб перемістити колір клітинки, колір шрифту або піктограму вниз або праворуч, виберіть потрібний варіант. Знизу для сортування по стовпцю та Праворуч для сортування за рядком.

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

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

    За необхідності створіть список, що настроюється.

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

    • Список користувачів можна створювати лише на основі значень (текстових, числових або значень дати та часу). Створити список, що настроюється, на основі формату (колір осередку, кольору шрифту або значків) не можна.
    • Максимальна довжина списку користувача становить 255 символів, і перше значення не може починатися з цифри.
    1. На вкладці Дані у групі Фільтр & сортування виберіть Сортувати.

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

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

    1. Виділіть будь-яку комірку в діапазоні, що сортується.
    2. На вкладці Дані у групі Фільтр & сортування виберіть Сортувати.

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

    1. Для текстових значень виберіть варіант Від А до Я або Від Я до А.
    2. Для числових значень виберіть За зростанням або За спаданням.
    3. Для дат та часу виберіть Від старих до нових або Від нових до старих.
    1. У групі Сортування, виберіть варіант Колір осередку, Колір шрифту або Значок осередку.
    2. У списку Порядок виберіть колір клітинки, колір шрифту або піктограму, а потім виберіть опцію Ліворуч або Праворуч.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    1. Якщо дані не містяться в таблиці, потрібно відформатувати їх як таблицю. Це призведе до автоматичного додавання кнопки фільтрації вгорі кожного стовпця таблиці.
    2. Натисніть кнопку фільтра у верхній частині стовпця, за яким ви хочете відсортувати, та виберіть потрібний порядок сортування.
    1. Виберіть комірку в стовпці, за яким буде сортуватися.
    2. Якщо на аркуші є рядок заголовків, виберіть один заголовок, за яким потрібно відсортувати дані, наприклад Населення.
    • За зростанням: сортування від A до Я, від найменшого значення до найбільшого або від ранньої дати до пізнішої.
    • За спаданням: сортування від Я до А, від найбільшого значення до найменшого або від пізнішої дати до ранньої.

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

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

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

    Примітка: Меню Сортування вимкнено, оскільки воно поки що не підтримується. В даний час можна змінити сортування в класичному додатку Excel.

    • За зростанням: сортування від A до Я, від найменшого значення до найбільшого або від ранньої дати до пізнішої.
    • За спаданням: сортування від Я до А, від найбільшого значення до найменшого або від пізнішої дати до ранньої.

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

    1. Виділіть комірку в стовпці, який потрібно відсортувати.
    2. На вкладці Дані виберіть Сортування користувача.
    3. У діалоговому вікні Сортування, що настроюється. у розділі Стовпці Виберіть стовпець, за яким потрібно сортувати.

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

    • Переміщення вгору або вліво: виберіть опцію Зверху для сортування стовпця та Ліворуч для сортування рядка.
    • Переміщення вниз або вправо: виберіть опцію Знизу для сортування стовпця та Праворуч для сортування рядка.
    • Переконайтеся, що стовпець у полі Потім по та вибір у полі Порядок збігаються.
    • Повторіть ці кроки для кожного додаткового кольору клітинки, кольору шрифту або піктограми, які потрібно включити до сортування.
    1. На вкладці Дані виберіть Сортування користувача.
    2. У діалоговому вікні Сортування, що настроюється. виберіть Параметри.

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

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

    1. Виділіть будь-яку комірку в діапазоні, що сортується.
    2. На вкладці Дані виберіть Сортування користувача.
    3. У діалоговому вікні Настроюване сортування виберіть Параметри.
    • Сортування за зростанням: сортування від A до Я, від найменшого значення до найбільшого або від ранньої дати до пізнішої.
    • Сортування за зменшенням: сортування від Я до А, від найбільшого значення до найменшого або від пізнішої дати до ранньої.

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

    Ви завжди можете поставити запитання експерту в Excel Tech Community або отримати підтримку у спільнотах.