Сортування Excel: сортування даних за текстом, датою, номером або кольором

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

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

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

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

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

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

Клацніть будь -яку клітинку у стовпці зі значеннями для сортування, а потім клацніть дані вкладка, в Сортувати та фільтрувати розділ, клацніть Сортувати від А до Я or Відсортуйте Z до A кнопку для сортування даних у порядку зростання або спадання в алфавітному порядку. Дивіться скріншот:

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

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

У Сортувати та фільтрувати групи дані вкладка, є ще одна сортувати , див. знімок екрана:

Після натискання цього сортувати кнопку, a сортувати з'явиться діалогове вікно, як показано на скріншоті нижче:

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

Tips : Ви також можете використовувати ярлики, щоб відкрити це сортувати діалогове вікно, будь ласка, натисніть Alt + A + S + S клавіші послідовно на клавіатурі.

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

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

примітки: Ці параметри сортування змінюються на основі даних у стовпці: Якщо у стовпці є текст, він відображатиметься Сортувати від А до Я, Відсортуйте Z до A; Якщо у стовпці є цифри, він відображатиметься Сортувати від найменшого до найбільшого, Сортувати від найбільшого до найменшого; Якщо у стовпці є дати, він відображатиметься Сортувати від найстарішого до нового, Сортувати від найновішого до найстарішого.

1.4 Клацніть правою кнопкою миші параметри сортування

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

Основні звички сортування

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

2.1 Сортувати дані за текстами, номерами або датами

Щоб відсортувати діапазон даних на основі текстів, чисел або дат у порядку зростання або спадання, виконайте такі дії:

1. Виберіть діапазон даних, який потрібно відсортувати, а потім клацніть дані > сортувати, див. скріншот:

2, в сортувати у діалоговому вікні виконайте такі операції:

• У Колонка розділ, виберіть назву стовпця, за якою потрібно відсортувати;
• У Сортувати за розділ, виберіть Значення комірок варіант;
• У замовлення розділ, вкажіть порядок сортування. (Щоб відсортувати текстові рядки, виберіть А до Z or Z до А.; Щоб відсортувати список номерів, виберіть Від найменшого до найбільшого or Від найбільшого до найменшого; Щоб відсортувати клітинки дати, виберіть Від старих до нових or Новi в старому.)
• Tips : Якщо у ваших даних є заголовки, переконайтеся Мої дані мають заголовки перевірено. Якщо ваші дані не мають заголовків, зніміть їх.

3. Потім натисніть кнопку OK кнопку, вибрані дані будуть відсортовані на основі стовпця, який ви вказали одразу.

2.2 Сортування даних за кольором комірки, кольором шрифту, піктограмою клітинки

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

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

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

2, в сортувати діалогове вікно, вкажіть такі операції:

2.1) Встановіть перший колір комірки зверху так:

• У Колонка розділ, виберіть ІМ'Я або інші стовпці, у яких є кольорові клітинки. У цьому прикладі я маю кольорові клітинки у всіх стовпцях, ви можете вибрати будь -яку назву стовпця;
• Відповідно до Сортувати за розділ, будь ласка, виберіть Колір клітини варіант;
• У замовлення розділ, виберіть один колір комірки, який потрібно нанести зверху або знизу;

2.2) Потім натисніть кнопку Додати рівень , щоб додати другий та інші рівні правил, повторіть наведені вище кроки для встановлення другого та інших кольорів комірок.

• Tips : Якщо у ваших даних є заголовки, переконайтеся Мої дані мають заголовки перевірено. Якщо ваші дані не мають заголовків, зніміть їх.

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

Tips : Відповідно до наведених вище кроків, ви також можете сортувати дані за кольором шрифту або піктограмою клітинки, вибравши Колір шрифту or Значок умовного форматування у діалоговому вікні Сортування.

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

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

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

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

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

Tips : Якщо у ваших даних є заголовки, переконайтеся Мої дані мають заголовки перевірено. Якщо ваші дані не мають заголовків, зніміть їх.

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

4. Потім натисніть кнопку OK, і ви отримаєте результат сортування, як показано на скріншоті нижче:

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

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

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

2, в сортувати у діалоговому вікні виберіть стовпець, який потрібно відсортувати, з Колонка розділ, а потім виберіть Значення комірок від Сортувати за розділ, в замовлення розділ, клацніть Спеціальний список варіант, див. скріншот:

Tips : Якщо у ваших даних є заголовки, переконайтеся Мої дані мають заголовки перевірено. Якщо ваші дані не мають заголовків, зніміть їх.

3. І а Спеціальні списки з'явиться вікно, виконайте такі дії:

• Натисніть НОВИЙ СПИСОК в виготовлений на замовлення поле зі списками;
• Потім введіть текстові рядки в тому порядку, який потрібно відсортувати на основі Список записів коробка; (Під час введення текстів, будь ласка, натисніть Що натомість? Створіть віртуальну версію себе у клавіша для розділення записів.)
• Нарешті клацніть додавати , новий список додається до Користувальницькі списки коробка відразу.

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

5. А потім натисніть OK , щоб закрити діалогове вікно, в результаті діапазон даних був відсортований за визначеним вами користувацьким списком, див. скріншот:

2.5 Сортування даних зліва направо (горизонтальне сортування)

Зазвичай ви завжди сортуєте таблицю даних вертикально зверху вниз, але іноді вам може знадобитися відсортувати дані на основі значень рядків (сортування зліва направо). Наприклад, у наведеному нижче діапазоні даних я хочу відсортувати їх на основі значень у рядку Ім'я.

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

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

2. У вискочив сортувати діалогове вікно натисніть кнопку Опції потім, у Параметри сортування діалогове вікно, виберіть Сортувати зліва направо варіант, див. скріншот:

3. Клацання OK щоб повернутися до діалогового вікна "Сортування" зараз у Рядок розділ, вкажіть номер рядка, за яким потрібно сортувати дані, а потім виберіть Значення комірок в Сортувати за нарешті, виберіть порядок сортування в замовлення потрібний вам розділ, перегляньте скріншот:

4. Потім натисніть кнопку OK, а ваші дані відсортовано так, як показано на скріншоті нижче:

2.6 Сортування даних у порядку з урахуванням регістру

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

Щоб виконати сортування з урахуванням регістру в Excel, виконайте наведені нижче дії.

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

2, в сортувати у діалоговому вікні Колонка у розділі, вкажіть ім’я стовпця, за яким потрібно сортувати дані, а потім виберіть Значення комірок в Сортувати за нарешті, виберіть порядок сортування в замовлення потрібний вам розділ, перегляньте скріншот:

Tips : Якщо у ваших даних є заголовки, переконайтеся Мої дані мають заголовки перевірено. Якщо ваші дані не мають заголовків, зніміть їх.

3. Продовжуйте натискати на Опції у цьому діалоговому вікні, а в Параметри сортування підказка, поставте галочку Чутливий до справи варіант, див. скріншот:

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

Розширені способи сортування

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

3.1 Сортувати дані за частотою

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

 Сортувати дані за частотою за допомогою допоміжного стовпця

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

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

=COUNTIF($A$2:$A$16,A2)

примітки: У наведеній вище формулі, A2: A16 - це список із даними, які потрібно сортувати за частотою, та A2 це перші дані цього списку.

2. Потім продовжуйте виділяти комірки формул, а потім клацніть дані > Сортувати від А до Я or Відсортуйте Z до A як вам потрібно, у спливаючому вікні Попередження про сортування у вікні запиту, виберіть Розгорніть вибір, див. скріншоти:

3. А потім натисніть сортувати кнопка, тепер вихідний стовпець відсортовано за частотою, як показано на таких скріншотах:

 Сортуйте дані за частотою за допомогою зручної функції

Якщо у вас є Kutools для Excel, З його Розширене сортування Функція дозволяє швидко та легко сортувати дані за частотою зустрічі без стовпців -помічників.

після установки Kutools для Excel, будь ласка, зробіть так:

1. Виберіть список даних, який потрібно відсортувати, а потім клацніть Kutools Plus > сортувати > Розширене сортування, див. скріншот:

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

Tips : Якщо у ваших даних є заголовки, переконайтеся Мої дані мають заголовки перевірено. Якщо ваші дані не мають заголовків, зніміть їх.

3. Потім натисніть кнопку OK кнопку, список даних був одразу відсортований за кількістю випадків, див. скріншот:

3.2 Сортувати дані за довжиною символу

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

 Сортувати дані на основі довжини символу за допомогою допоміжного стовпця

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

1. Введіть цю формулу = LEN (A2) у порожню клітинку, що прилягає до вихідних даних, наприклад, В2, а потім перетягніть маркер заповнення до клітинок для застосування цієї формули, див. скріншот:

2. Потім продовжуйте виділяти комірки формул, а потім клацніть дані > Сортувати від А до Я or Відсортуйте Z до A як вам потрібно, у спливаючому вікні Попередження про сортування у вікні запиту, виберіть Розгорніть вибір, див. скріншот:

3. Потім натисніть кнопку сортувати , рядки були відсортовані за довжиною символу. Ви можете видалити допоміжну колонку В, як вам потрібно. Дивіться скріншот:

 Сортуйте дані на основі довжини символів за допомогою простого варіанту

Щоб швидко та легко сортувати дані за кількістю символів, тут я рекомендую зручний інструмент - Kutools для Excel, З його Розширене сортування Функція дозволяє легко впоратися з цим завданням.

після установки Kutools для Excel, будь ласка, зробіть так:

1. Виберіть список даних, який потрібно відсортувати, а потім клацніть Kutools Plus > сортувати > Розширене сортування.

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

Tips : Якщо у ваших даних є заголовки, переконайтеся Мої дані мають заголовки iперевірено. Якщо ваші дані не мають заголовків, зніміть їх.

3. Потім натисніть кнопку OK , текстові рядки у списку відсортовані за довжиною символу, як показано на скріншоті нижче:

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

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

 Сортуйте повні імена за прізвищами за допомогою допоміжного стовпця

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

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

=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

2. Перейдіть до вибору комірок формул, а потім клацніть дані > Сортувати від А до Я or Відсортуйте Z до A як вам потрібно, у спливаючому вікні Попередження про сортування у вікні запиту, виберіть Розгорніть вибір, див. скріншот:

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

 Сортуйте повні імена за прізвищами швидким методом

Якщо ви не знайомі з формулою, відсортуйте повні імена за прізвищами без будь -якої формули, Розширене сортування особливість Kutools для Excel може зробити вам користь.

після установки Kutools для Excel, будь ласка, зробіть так:

1. Виберіть клітинки повного імені, які потрібно відсортувати, і натисніть Kutools Plus > сортувати > Розширене сортування.

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

Tips : Якщо у ваших даних є заголовки, переконайтеся Мої дані мають заголовки перевірено. Якщо ваші дані не мають заголовків, зніміть їх.

3. Потім натисніть кнопку OK кнопка, стовпець повного імені відсортовано відразу за прізвищами.

3.4 Сортування електронних адрес за доменом

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

 Сортувати адреси електронної пошти за доменом за допомогою стовпця -помічника

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

1. Введіть наведену нижче формулу у порожню клітинку біля вашої адреси електронної пошти. У цьому прикладі я введу цю формулу в клітинку С2, а потім перетягнуть маркер заповнення до діапазону, до якого потрібно застосувати цю формулу. Потім усі адреси електронної пошти будуть вилучені з адрес, див. Скріншот:

=RIGHT(B2,LEN(B2)-FIND("@",B2))

2. Утримуйте клітинки формул вибраними, а потім клацніть дані > Сортувати від А до Я or Відсортуйте Z до A як вам потрібно, в Попередження про сортування діалогове вікно, поставте галочку Розгорніть вибір, див. скріншот:

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

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

 Сортуйте адреси електронної пошти за доменом за допомогою декількох клацань миші

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

після установки Kutools для Excel, будь ласка, зробіть так:

1. Виберіть весь діапазон даних, який потрібно відсортувати, а потім клацніть Kutools Plus > сортувати > Розширене сортування.

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

Tips : Якщо у ваших даних є заголовки, переконайтеся Мої дані мають заголовки перевірено. Якщо ваші дані не мають заголовків, зніміть їх.

3. Клацання OK, а діапазон даних відсортовано за доменами електронної пошти, як показано на скріншоті нижче:

3.5 Сортувати один стовпець відповідно до іншого

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

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

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

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

=MATCH(B2,$A$2:$A$10,0)

2. Потім виберіть стовпець B і новий стовпець -помічник і натисніть дані > сортувати йти до сортувати у діалоговому вікні сортувати у діалоговому вікні виконайте такі операції:

• Виберіть стовпець -помічник, за яким потрібно сортувати дані, з Колонка розділ;
• Потім виберіть Значення комірок в Сортувати за розділ;
• Нарешті, виберіть Від найменшого до найбільшого опція в замовлення .

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

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

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

1. Вставте новий порожній стовпець між двома стовпцями.

2. Введіть формулу нижче в клітинку B2, а потім перетягніть маркер заповнення, щоб заповнити цю формулу до потрібних клітинок. Тепер ви можете побачити, що дані у стовпці С відсортовані відповідно до даних у стовпці А.

=IF(ISNA(MATCH(A2,$C$2:$C$8,0)),"",INDEX($C$2:$C$8,MATCH(A2,$C$2:$C$8,0)))

3.6 Сортування та вилучення унікальних значень зі списку даних

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

1. Будь ласка, введіть формулу нижче у порожню клітинку - C2, а потім натисніть Ctrl + Shift + Enter ключі, щоб отримати перший результат, дивіться скріншот:

=IFERROR(INDEX($A$2:$A$12,MATCH(0,COUNTIF($A$2:$A$12,"<"&$A$2:$A$12)-SUM(COUNTIF($A$2:$A$12,"="&C$1:C1)),0)),"")

примітки: У формулі, A2: A12 - це список даних, з якого потрібно витягти унікальні значення, C1 - це клітинка над формулою, яку ви вводите. Будь ласка, змініть їх відповідно до ваших потреб.

2. Потім перетягніть маркер заповнення, щоб витягти значення, поки не з’являться порожні клітинки, усі унікальні значення будуть вилучені та відсортовані у порядку зростання, див. Скріншот:

3.7 Сортуйте відразу кілька рядків або стовпців незалежно

Нам легко сортувати діапазон даних на основі одного рядка або стовпця, але іноді вам може знадобитися окремо алфавітувати кожен рядок або стовпець у діапазоні незалежно, як би ви могли досягти цього завдання в Excel?

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

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

Сортуйте кілька рядків незалежно одночасно за допомогою формули

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

2. Потім скопіюйте або введіть наведену нижче формулу в порожню клітинку - H2, а потім натисніть Ctrl + Shift + Enter клавіші разом, щоб отримати перший результат, див. скріншот:

=INDEX($B2:$E2, MATCH(COLUMNS($B2:B2), COUNTIF($B2:$E2, "<="&$B2:$E2), 0))

3. Потім виберіть комірку формули H2 і перетягніть маркер заповнення вправо, щоб скопіювати формулу до інших клітинок першого рядка, у цьому прикладі перетягніть до комірки K2, див. Скріншот:

4. Продовжуйте виділяти комірки формул у першому рядку (H2: K2) і перетягніть маркер заповнення вниз, щоб скопіювати формулу до інших рядків. Тепер ви можете побачити, що значення в кожному рядку відсортовані окремо в порядку зростання.

Відсортуйте кілька рядків незалежно відразу за допомогою коду VBA

Наступний код VBA також може допомогти вам з легкістю сортувати дані в кожному рядку в алфавітному порядку. Виконайте такі дії:

1. Виберіть дані, які потрібно відсортувати в кожному рядку.

2. Утримуйте клавішу ALT + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.

3. Потім натисніть кнопку Insert > Модуліта вставте наступний код у Модулі Вікно

Код VBA: Сортуйте кілька рядків незалежно одночасно

Sub SortIndividualR()
'Updateby Extendoffice
    Dim xRg As Range, yRg As Range
    If TypeName(Selection) <> "Range" Then Exit Sub
    Set xRg = Selection
    If xRg.Count = 1 Then
        MsgBox "Select multiple cells!", vbExclamation, "Kutools for Excel"
        Exit Sub
    End If
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    Application.ScreenUpdating = False
    For Each yRg In xRg.Rows
        yRg.Sort Key1:=yRg.Cells(1, 1), _
        Order1:=xlAscending, _
        Header:=xlNo, _
        Orientation:=xlSortRows
    Next yRg
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
    Application.ScreenUpdating = True
End Sub

4. Потім натисніть F5 ключ для запуску цього коду, дані в кожному рядку негайно сортуються у порядку зростання, див. скріншот:

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

Щоб відсортувати дані в кожному стовпці окремо, такі два методи можуть вам допомогти.

Сортуйте кілька стовпців незалежно одночасно за допомогою формули

1. Скопіюйте мітки стовпців в інше місце, де потрібно отримати відсортований результат.

2. Потім введіть формулу нижче у порожню клітинку - F3 і натисніть Ctrl + Shift + Enter клавіші разом, щоб отримати перший результат, а потім перетягніть маркер заповнення вниз, щоб скопіювати цю формулу в інші рядки, див. скріншот:

=INDEX(A$3:A$6,MATCH(ROWS(A$3:A3),COUNTIF(A$3:A$6,"<="&A$3:A$6),0))

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

Сортуйте кілька стовпців одночасно незалежно за допомогою коду VBA

Щоб самостійно сортувати дані у кількох стовпцях, наступний код VBA також може вам допомогти, зробіть це так:

1. Утримуйте клавішу ALT + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.

2. Потім натисніть кнопку Insert > Модуліта вставте наступний код у Модулі Вікно

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

Sub SortIndividualJR()
'Updateby Extendoffice
    Dim xRg As Range
    Dim yRg As Range
    Dim ws As Worksheet
    Set ws = ActiveSheet
    On Error Resume Next
    Set xRg = Application.InputBox(Prompt:="Range Selection:", _
                                    Title:="Kutools for excel", Type:=8)
    Application.ScreenUpdating = False
    For Each yRg In xRg
        With ws.Sort
            .SortFields.Clear
            .SortFields.Add Key:=yRg, Order:=xlAscending
            .SetRange ws.Range(yRg, yRg.End(xlDown))
            .Header = xlNo
            .MatchCase = False
            .Apply
        End With
    Next yRg
    Application.ScreenUpdating = True
End Sub

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

4. А потім натисніть OK, кожен стовпець швидко сортувався окремо.

3.8 Сортуйте дані випадковим чином у Excel

Для нас звичайно сортувати дані в алфавітному порядку за зростанням або спаданням, але ви коли -небудь пробували сортувати дані у вибраному діапазоні випадковим чином? У цьому розділі я розповім про те, як перемішати діапазон клітинок у випадковому порядку.

 Сортуйте стовпець даних випадковим чином за допомогою допоміжного стовпця

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

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

2. Продовжуйте виділяти комірки формул, а потім клацніть Dата > Сортувати від А до Я or Відсортуйте Z до A як вам потрібно, у спливаючому вікні Попередження про сортування у вікні запиту, виберіть Розгорніть вибір, див. скріншот:

3. А потім натисніть сортувати кнопку, список даних перемішано відразу, див. скріншот:

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

Kutools для Excel підтримує потужну функцію - Сортувати діапазон випадковим чином, за допомогою цієї функції ви можете рандомізувати дані в діапазоні клітинок, у кожному стовпці / рядку виділеного об’єкта або рандомізувати цілі рядки або стовпці одночасно.

після установки Kutools для Excel, будь ласка, зробіть так:

1. Виберіть діапазон даних, який потрібно відсортувати випадковим чином, а потім клацніть Kutools Plus > сортувати > Сортувати діапазон випадковим чином. Дивіться знімок екрана:

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

• Цілі ряди: Випадкове перемішування всіх рядків у вибраному діапазоні.
• Цілі стовпці: Випадкове перемішування всіх стовпців у вибраному діапазоні.
• Клітини в кожному ряду: Перемішати клітинки в кожному рядку окремо.
• Клітини в кожному стовпці: Перемішати клітинки в кожному стовпці окремо.
• Усі комірки в діапазоні: Випадкове розташування всіх клітинок у вибраному діапазоні.

3. Потім натисніть кнопку Ok кнопку, дані негайно рандомізуються.

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

Під час сортування дат у Excel функція Сортування сортуватиме список дат за роком, місяцем та днем ​​за замовчуванням, але в певному випадку вам може знадобитися впорядкувати дати за місяцем чи днем ​​або місяцем та днем, ігноруючи рік. Як вирішити цю проблему в Excel?

 Сортуйте список дат за місяцем або днем ​​за допомогою стовпця -помічника

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

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

=MONTH(B2)        (extract month number)
= ДЕНЬ (B2)              (номер дня витягу)

2. Після повернення номерів місяця чи дня продовжуйте вибирати комірки формул, а потім клацніть дані > Сортувати від А до Я or Відсортуйте Z до A як вам потрібно, у спливаючому вікні Попередження про сортування у вікні запиту, виберіть Розгорніть вибір, див. скріншот:

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

 Сортуйте список дат за місяцем або днем ​​за допомогою декількох клацань миші

Якщо у вас є Kutools для Excel, З його Розширене сортування Функція дозволяє сортувати список дат лише за місяцем або днем ​​за допомогою кількох клацань миші.

після установки Kutools для Excel, виконайте такі дії:

1. Виберіть діапазон даних, який потрібно відсортувати, а потім клацніть Kutools Plus > сортувати > Розширене сортування.

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

Tips : Якщо у ваших даних є заголовки, переконайтеся Мої дані мають заголовки перевірено. Якщо ваші дані не мають заголовків, зніміть їх.

3. Потім натисніть кнопку OK кнопку, дані були відсортовані за місяцем або днем, як ви вказали, ігноруючи рік, див. скріншот:

 Сортуйте список дат за місяцями та днями за допомогою стовпця -помічника

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

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

=TEXT(B2,"MMDD")

2. Утримуйте клітинки формул, а потім клацніть дані > Сортувати від А до Я or Відсортуйте Z до A як вам потрібно, у спливаючому вікні Попередження про сортування у вікні запиту, виберіть Розгорніть вибір, див. скріншот:

3. Потім натисніть кнопку сортувати і тепер ваші дані сортуються лише за місяцями та днями.

3.10 Сортувати список дат за днями тижня

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

 Сортуйте список дат за днями тижня за допомогою стовпця -помічника

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

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

=WEEKDAY(B2)           (Start from Sunday (1) to Saturday(7))
= ВИХОДНИЙ (В2,2)       (Початок з понеділка (1) до неділі (7))

2. Утримуйте клітинки формул, а потім клацніть дані > Сортувати від А до Я or Відсортуйте Z до A як вам потрібно, у спливаючому вікні Попередження про сортування у вікні запиту, виберіть Розгорніть вибір, див. скріншот:

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

 Сортуйте список дат за днями тижня з легким варіантом

За допомогою Kutools для ExcelАвтора Розширене сортування Функція дозволяє вирішити цю проблему якомога швидше без будь -якої допоміжної формули.

після установки Kutools для Excel, будь ласка, зробіть так:

1. Виберіть діапазон даних, який потрібно відсортувати, а потім клацніть Kutools Plus > сортувати > Розширене сортування.

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

Tips : Якщо у ваших даних є заголовки, переконайтеся Мої дані мають заголовки перевірено. Якщо ваші дані не мають заголовків, зніміть їх.

3. Потім натисніть кнопку OK кнопку, дані відсортовані за днями тижня так, як вам потрібно. Дивіться скріншот:

3.11 Сортувати список дат за кварталом

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

 Сортуйте список дат за кварталами за допомогою допоміжного стовпця

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

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

=ROUNDUP(MONTH(B2)/3,0)

2. Продовжуйте виділяти комірки формул, а потім клацніть дані > Сортувати від А до Я or Відсортуйте Z до A як вам потрібно, у спливаючому вікні Попередження про сортування у вікні запиту, виберіть Розгорніть вибір, див. скріншот:

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

 Сортуйте список дат за кварталами за допомогою зручної функції

Якщо у вас є Kutools для Excel встановлений, Розширене сортування Ця функція може допомогти вам вирішити це завдання кількома клацаннями миші.

1. Виберіть діапазон даних, який потрібно відсортувати, а потім клацніть Kutools Plus > сортувати > Розширене сортування.

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

Tips : Якщо у ваших даних є заголовки, переконайтеся Мої дані мають заголовки перевірено. Якщо ваші дані не мають заголовків, зніміть їх.

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

3.12 Сортувати дані на основі назв місяців або назв буднів

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

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

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

Tips : Якщо у ваших даних є заголовки, переконайтеся Мої дані мають заголовки перевірено. Якщо ваші дані не мають заголовків, зніміть їх.

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

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

Tips : Щоб відсортувати за назвами будні, виберіть повні імена (неділя, понеділок, вівторок, ...) або короткі назви (нд, пн, вт ...) у Спеціальні списки діалогове вікно, як вам потрібно.

3.13 Сортування даних на основі непарних чи парних чисел

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

 Сортувати дані за непарними або парними числами за допомогою допоміжного стовпця

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

1. Біля списку чисел введіть цю формулу = ISODD (A2) у порожній клітинці, а потім перетягніть, щоб скопіювати формулу до інших клітинок, тепер ви можете побачити, що відображаються значення ІСТИНА та НЕВІРНІСТЬ, ІСТИНА вказує на непарні числа, а ЛОЖЬ - на парні.

2. Продовжуйте вибирати клітинки формули, а потім клацніть дані > Сортувати від А до Я or Відсортуйте Z до A як вам потрібно, у спливаючому вікні Попередження про сортування у вікні запиту, виберіть Розгорніть вибір, див. скріншот:

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

 Сортуйте дані за непарними або парними числами за допомогою корисної функції

За допомогою Kutools для ExcelАвтора Розширене сортування Функція дозволяє швидко сортувати числа від непарних до парних чи навпаки.

після установки Kutools для Excel, будь ласка, зробіть так:

1. Виберіть діапазон даних, який потрібно відсортувати, а потім клацніть Kutools Plus > сортувати > Розширене сортування.

2, в Розширене сортування у діалоговому вікні клацніть стовпець, під яким потрібно відсортувати Колонка розділ, а потім виберіть Непарне і парне число від Сортувати за розділі, потім вкажіть порядок сортування (А до Z сортувати від парних чисел до і Z до А. сортувати від непарних до парних чисел), подивіться скріншот:

Tips : Якщо у ваших даних є заголовки, переконайтеся Мої дані мають заголовки перевірено. Якщо ваші дані не мають заголовків, зніміть їх.

3. А потім клацніть OK , ви отримаєте такі результати:

3.14 Сортування даних на основі абсолютних значень

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

 Сортувати дані за абсолютними значеннями за допомогою допоміжного стовпця

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

1. В сусідню порожню комірку, наприклад В2, введіть цю формулу = ABS (A2), а потім перетягніть маркер заповнення, щоб скопіювати цю формулу в інші клітинки. Тепер ви можете побачити, що всі значення були перетворені в абсолютні значення:

2. Утримуйте клітинки формул, а потім клацніть дані > Сортувати від А до Я or Відсортуйте Z до A як вам потрібно, у спливаючому вікні Попередження про сортування у вікні запиту, виберіть Розгорніть вибір, див. скріншот:

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

 Сортувати дані за абсолютними значеннями за допомогою зручної опції

Якщо ви втомилися створювати допоміжну колонку, тут я рекомендую Kutools для Excel, З його Розширене сортування Функція дозволяє сортувати числа за абсолютними значеннями прямо і просто.

після установки Kutools для Excel, виконайте такі дії:

1. Виберіть діапазон даних, який потрібно відсортувати, а потім клацніть Kutools Plus > сортувати > Розширене сортування.

2, в Розширене сортування у діалоговому вікні клацніть стовпець, під яким потрібно відсортувати Колонка розділ, а потім виберіть абсолют значення від Сортувати за розділі, потім вкажіть порядок сортування, подивіться скріншот:

Tips : Якщо у ваших даних є заголовки, переконайтеся, що у моїх даних є заголовки. Якщо ваші дані не мають заголовків, зніміть їх.

3. Потім натисніть кнопку OK, всі числа будуть відсортовані від найменшого до найбільшого або найбільшого до найменшого значення, ігноруючи від’ємний знак.

Автоматично сортувати дані при введенні або зміні даних

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

4.1 Автоматичне сортування числових значень у стовпці з формулами

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

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

=IFERROR(SMALL($A$2:$A$100,ROWS(B$2:B2)),"")

примітки: У цій формулі, A2: A100 - це список номерів, який потрібно сортувати автоматично, включаючи деякі порожні клітинки для нових записів, B2 - це клітинка, у якій ви вводите формулу.

2. Тепер при зміні вихідних даних або введенні нових даних відсортований список буде автоматично оновлюватися, як показано нижче на демонстраційній сторінці:

4.2 Автоматичне сортування текстових значень у стовпці з формулами

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

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

=IFERROR(INDEX($A$2:$A$100,MATCH(ROWS($A$2:A2),COUNTIF($A$2:$A$100,">="&$A$2:$A$100),0)),"")

2. Відтепер при введенні значення або зміні вихідних даних у стовпці А текстові рядки у стовпці В будуть автоматично сортуватись у порядку зростання, див. Нижче демонстрацію:

4.3 Автоматичне сортування суміші числових і текстових значень у стовпці з кодом VBA

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

1. Клацніть правою кнопкою миші вкладку аркуша, де потрібно автоматично сортувати дані, а потім виберіть Переглянути код у контекстному меню у спливаючому вікні Microsoft Visual Basic для додатків вікно, скопіюйте та вставте наступний код у порожнє Модулі вікно, див. скріншот:

Код VBA: автоматичне сортування при введенні або зміні даних у порядку зростання:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
    On Error Resume Next
    If Application.Intersect(Target, Application.Columns(1)) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Range("A1").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
                                        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

примітки: У наведеному вище коді введені дані будуть автоматично відсортовані у стовпці А. A1 - це заголовок, і A2 є першою клітинкою списку даних.

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

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
    On Error Resume Next
    If Application.Intersect(Target, Application.Columns(1)) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Range("A1").Sort Key1:=Range("A2"), Order1:=xlDescending, Header:=xlYes, _
                                        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Інші випадки сортування

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

5.1 Сортування даних усередині комірки

Для сортування текстового рядка в межах однієї комірки в алфавітному порядку, наприклад, для сортування тексту “HDAW” як “ADHW”; Або відсортувати кілька слів, розділених комами, у клітинці, наприклад, відсортувати «слово, перспектива, Excel, доступ» як «доступ, Excel, Outlook, слово». У цьому розділі мова піде про те, як вирішити цей тип сортування в Excel.

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

Сортуючи значення рядка всередині комірки в алфавітному порядку, слід створити призначену користувачем функцію. Будь ласка, зробіть так:

1. Постривай ALT + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.

2. Клацання Insert > Модуліта вставте наступний код у Модулі Вікно

Код VBA: Сортування текстового значення всередині комірки

Function SortCellContents(xRange As Range)
'Updateby Extendoffice
Dim xArr
Dim xF1, xF2 As Integer
Dim xStrValue As String
Dim xStrT As String
If xRange.Count <> 1 Then
    Exit Function
End If
xStrValue = xRange.Value
ReDim xArr(1 To Len(xStrValue))
For xF1 = 1 To UBound(xArr)
    xArr(xF1) = Mid(xStrValue, xF1, 1)
Next
For xF1 = 1 To UBound(xArr)
    For xF2 = xF1 To UBound(xArr)
        If Asc(xArr(xF2)) < Asc(xArr(xF1)) Then
            xStrT = xArr(xF2)
            xArr(xF2) = xArr(xF1)
            xArr(xF1) = xStrT
        End If
    Next xF2
Next xF1
SortCellContents = Join(xArr, "")
End Function

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

 Сортувати текстові рядки, розділені роздільником усередині комірки в алфавітному порядку

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

1. Постривай ALT + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.

2. Клацання Insert > Модуліта вставте наступний код у Модулі Вікно

Код VBA: Сортування текстових рядків, розділених комами всередині комірки

Function SortCellWithSeparator(CellAddress As Range, DelimiterChar As String, IncludeSpaces As Boolean) As String
'Updateby Extendoffice
Dim xRg As Range
Dim xString As String
Dim xF1, xF2 As Integer
Dim xSArr
Dim xStrT As String
Dim xStrValue As String
Set xRg = CellAddress
xStrValue = WorksheetFunction.Substitute(xRg.Value, " ", "")
xSArr = Split(xStrValue, DelimiterChar)
    For xF1 = 0 To UBound(xSArr)
        For xF2 = xF1 + 1 To UBound(xSArr)
                If xSArr(xF2) < xSArr(xF1) Then
                    xStrT = xSArr(xF2)
                    xSArr(xF2) = xSArr(xF1)
                    xSArr(xF1) = xStrT
                End If
        Next xF2
    Next xF1
xStrValue = ""
For xF1 = 0 To UBound(xSArr)
    xStrValue = xStrValue & xSArr(xF1) & DelimiterChar
Next xF1
SortCellWithSeparator = xStrValue
SortCellWithSeparator = Left(SortCellWithSeparator, Len(SortCellWithSeparator) - 1)
If IncludeSpaces = True Then SortCellWithSeparator = WorksheetFunction.Substitute(SortCellWithSeparator, ",", ", ")
End Function

3. Потім збережіть і закрийте цей код, поверніться до свого робочого аркуша, введіть цю формулу = SortCellWithSeparator (A2, ",", TRUE) у порожню клітинку, де потрібно повернути результат, а потім перетягніть маркер заповнення вниз, щоб скопіювати формулу до інших комірок, усі текстові рядки в осередках будуть відсортовані в алфавітному порядку, див. скріншот:

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

5.2 Змінити / перевернути порядок даних у стовпці або рядку

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

 Змінити / перевернути порядок даних у стовпці або рядку з формулами

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

Переверніть порядок даних у стовпці

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

=OFFSET($A$10,-(ROW(A1)-1),0)

примітки: У наведеній вище формулі, A1 є першою клітиною і A10 - остання клітинка в стовпці.

Переверніть порядок даних підряд

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

=OFFSET($A$1,,COUNTA(1:1)-COLUMN(A1),)

примітки: У наведеній вище формулі, A1 - перша комірка в рядку, і 1:1 - це номер рядка, у якому знаходяться ваші дані. Якщо дані в рядку 10, ви повинні змінити їх на 10:10.

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

 Змінити / перевернути порядок даних у кількох стовпцях або рядках з кодом VBA

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

Переверніть порядок даних у діапазоні клітинок по вертикалі

1. Спочатку слід створити резервну копію вихідних даних, а потім утримувати ALT + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.

2. Клацання Insert > Модуліта вставте наступний код у Модулі Вікно

Код VBA: переверніть діапазон комірок у зворотному порядку по вертикалі

Sub Flipvertically()
'updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
Dim Arr As Variant
Dim i As Integer, j As Integer, k As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Arr = WorkRng.Formula
For j = 1 To UBound(Arr, 2)
    k = UBound(Arr, 1)
    For i = 1 To UBound(Arr, 1) / 2
        xTemp = Arr(i, j)
        Arr(i, j) = Arr(k, j)
        Arr(k, j) = xTemp
        k = k - 1
    Next
Next
WorkRng.Formula = Arr
End Sub

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

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

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

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

Код VBA: переверніть діапазон комірок у зворотному порядку по вертикалі

Sub Fliphorizontally()
'updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
Dim Arr As Variant
Dim i As Integer, j As Integer, k As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Arr = WorkRng.Formula
For i = 1 To UBound(Arr, 1)
    k = UBound(Arr, 2)
    For j = 1 To UBound(Arr, 2) / 2
        xTemp = Arr(i, j)
        Arr(i, j) = Arr(i, k)
        Arr(i, k) = xTemp
        k = k - 1
    Next
Next
WorkRng.Formula = Arr
End Sub

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

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

Якщо у вас є Kutools для Excel, З його Перевернути вертикальний діапазон та Перевернути горизонтальний діапазон функції, ви можете змінити діапазон комірок вертикально та горизонтально лише одним клацанням миші.

після установки Kutools для Excel, будь ласка, зробіть так:

Переверніть порядок даних у діапазоні клітинок по вертикалі

1. Виберіть діапазон даних, який потрібно змінити, а потім клацніть Кутулс > Діапазон > Перевернути вертикальний діапазон > ВСІ / Тільки перевернути значення, див. скріншот:

Tips : Якщо ви виберете ВСІ параметр, все форматування комірки буде скасовано одночасно; Якщо ви обираєте Тільки перевернути значення, буде змінено лише значення клітинок.

2. І тоді діапазон даних буде одразу змінено по вертикалі.

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

1. Виберіть діапазон даних, а потім клацніть Кутулс > Діапазон > Перевернути горизонтальний діапазон > Усі / тільки значення перевертання, див. скріншот:

Tips : Якщо ви виберете ВСІ параметр, все форматування комірки буде скасовано одночасно; Якщо ви обираєте Тільки перевернути значення, буде змінено лише значення клітинок.

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

5.3 Сортування вкладок робочого аркуша за алфавітом або кольором в Excel

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

 Сортувати вкладки робочого аркуша в алфавітному порядку з кодом VBA

Наступний код VBA може допомогти сортувати вкладки аркушів за алфавітом у порядку зростання або спадання, будь ласка, зробіть так:

1. Постривай ALT + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.

2. Клацання Insert > Модулі, а потім вставте наступний макрос у Модулі Вікно

VBA: Сортуйте всі аркуші в алфавітному порядку

Sub SortWorkBook()
'Updateby Extendoffice
Dim xResult As VbMsgBoxResult
xTitleId = "KutoolsforExcel"
xResult = MsgBox("Click Yes to sort sheets in ascending order;" & Chr(10) & "Click No will sort in descending order", vbYesNoCancel + vbQuestion + vbDefaultButton1, xTitleId)
For i = 1 To Application.Sheets.Count
    For j = 1 To Application.Sheets.Count - 1
        If xResult = vbYes Then
            If UCase$(Application.Sheets(j).Name) > UCase$(Application.Sheets(j + 1).Name) Then
                Sheets(j).Move after:=Sheets(j + 1)
            End If
            ElseIf xResult = vbNo Then
                If UCase$(Application.Sheets(j).Name) < UCase$(Application.Sheets(j + 1).Name) Then
                    Application.Sheets(j).Move after:=Application.Sheets(j + 1)
            End If
        End If
    Next
Next
End Sub

3. Потім натисніть F5 ключ, щоб запустити цей макрос, у наступному вікні запиту натисніть Так, усі аркуші будуть відсортовані в порядку зростання за алфавітом; Натисніть Немає, усі робочі листи будуть відсортовані в порядку зменшення за алфавітом, як вам потрібно.

 Сортуйте вкладки робочого аркуша в алфавітно -цифровому порядку за допомогою потужної функції

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

після установки Kutools для Excel, виконайте такі дії:

1. Відкрийте книгу, у якій потрібно відсортувати вкладки аркуша, а потім клацніть Kutools Plus > Робочий аркуш > Сортувати аркуші, див. скріншот:

2, в Сортувати аркуші діалоговому вікні, виберіть потрібний тип сортування на правій панелі, наприклад Альфа-сорт, Буквено-цифрове сортування. Дивіться знімок екрана:

3. Потім натисніть кнопку Ok кнопка, усі аркуші будуть відсортовані відповідно до вказаного вами типу сортування. Дивіться скріншоти:

 Сортувати вкладки робочого аркуша за кольором вкладки з кодом VBA

Для нас звичайно сортувати вкладки робочого аркуша в алфавітному порядку в Excel, але ви коли -небудь пробували сортувати вкладки аркушів за кольором вкладки? Нижче наведений код VBA може допомогти вирішити цю проблему, будь ласка, зробіть так:

1. Постривай ALT + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.

2. Клацання Insert > Модулі, а потім вставте наступний макрос у Модулі Вікно

VBA: Сортуйте всі аркуші за кольором вкладки

Sub SortWorkBookByColor()
'Updateby20140624
Dim xArray1() As Long
Dim xArray2() As String
Dim n As Integer
Application.ScreenUpdating = False
If Val(Application.Version) >= 10 Then
    For i = 1 To Application.ActiveWorkbook.Worksheets.Count
        If Application.ActiveWorkbook.Worksheets(i).Visible = -1 Then
            n = n + 1
            ReDim Preserve xArray1(1 To n)
            ReDim Preserve xArray2(1 To n)
            xArray1(n) = Application.ActiveWorkbook.Worksheets(i).Tab.Color
            xArray2(n) = Application.ActiveWorkbook.Worksheets(i).Name
        End If
    Next
    For i = 1 To n
        For j = i To n
            If xArray1(j) < xArray1(i) Then
                temp = xArray2(i)
                xArray2(i) = xArray2(j)
                xArray2(j) = temp
                temp = xArray1(i)
                xArray1(i) = xArray1(j)
                xArray1(j) = temp
            End If
        Next
    Next
    For i = n To 1 Step -1
        Application.ActiveWorkbook.Worksheets(CStr(xArray2(i))).Move after:=Application.ActiveWorkbook.Worksheets(Application.ActiveWorkbook.Worksheets.Count)
    Next
End If
Application.ScreenUpdating = True
End Sub

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

 Сортуйте вкладки аркуша за кольором вкладки з дивовижною функцією

з Kutools для ExcelАвтора Сортувати аркуші функцію, ви також можете швидко і легко сортувати вкладки аркуша за кольором.

після установки Kutools для Excel, будь ласка, зробіть так:

1. Натисніть Kutools плюс > Робочий аркуш > Сортувати аркуші піти Сортувати аркуші у діалоговому вікні Сортувати аркуші діалогове вікно натисніть кнопку Сортування кольорів на правій панелі, перегляньте знімок екрана:

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

---

Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

• Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
• Об’єднати клітинки / рядки / стовпці та Ведення даних; Вміст розділених комірок; Поєднуйте повторювані рядки та суму / середнє... Запобігання дублюючим клітинам; Порівняйте діапазони...
• Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
• Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
• Улюблені та швидко вставлені формули, Діапазони, діаграми та зображення; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
• Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
• Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
• Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
• Групування зведеної таблиці за номер тижня, день тижня та багато іншого ... Показати розблоковані, заблоковані клітини за різними кольорами; Виділіть клітини, які мають формулу / назву...

Детальніше ... Повні можливості 30-денної безкоштовної пробної версії ... Придбати ... 

---

Вкладка Office - надає інтерфейс із вкладками в Office і значно полегшує вашу роботу

• Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
• Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
• Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!

Детальніше ... Повні можливості 30-денної безкоштовної пробної версії ... Придбати ...