Як сортувати в Excel і зберігати рядки разом: 4 простих методу

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

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

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

Зміст

• Як сортувати, зберігаючи рядки разом
• 3 способи доступу до команд сортування
  • Вкладка «Домашня сторінка».
  • Вкладка «Дані».
  • Клацніть правою кнопкою миші
• Як сортувати, вибираючи кілька стовпців, щоб зберегти рядки разом
• Як сортувати за допомогою підказки Excel, щоб зберегти рядки разом
• Як використовувати функцію сортування, щоб зберегти рядки разом
  • Сортування та фільтрація разом
• Як використовувати спеціальні списки для сортування та збереження рядків разом
  • Як створити власний список
  • Як використовувати список
• 3 поради щодо обробки типів даних
• 4 поради щодо ефективного сортування
• Заключні думки

Як сортувати, зберігаючи рядки разом

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

Припустімо, у вас є дані інвентаризації одягу, де тип предмета вказано в стовпці A, а колір – у стовпці B. Це дані:

• Футболка, Червона

• Джинси, Сині

• Худі, Зелений

• Футболка, фіолетова

• Худі, чорний

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

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

Ось результати неправильного методу сортування:

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

3 способи доступу до команд сортування

Найпоширенішим способом сортування в Excel є використання команди «Сортувати». Є три способи отримати доступ до цієї функції:

• Домашня вкладка

• Вкладка даних

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

Вкладка «Домашня сторінка».

1. Перейдіть на вкладку «Головна».

2. Розгорніть спадне меню «Сортування та фільтр» у групі «Редагування».

3. Виберіть варіант порядку зростання або спадання.

Excel передбачає, що у вас є рядок заголовка з цією опцією.

Вкладка «Дані».

1. Перейдіть на вкладку Дані.

2. Натисніть кнопку Сортувати в групі Сортувати та фільтрувати.

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

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

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

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

2. Клацніть правою кнопкою миші.

3. У спадному меню (внизу) виберіть «Сортувати».

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

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

Щоб скористатися цим методом, ви виберете всі задіяні дані перед використанням команди «Сортувати». Це означає, що ви не просто вибираєте один стовпець.

У наших прикладах даних вище ви повинні вибрати весь робочий аркуш, певний діапазон даних клітинок від A1 до B6 або два стовпці A і B.

Виконайте такі дії:

1. Виділіть усі пов’язані комірки (або Ctrl-A для всіх).

2. Перейдіть на вкладку «Головна».

3. Розгорніть спадне меню «Сортувати та фільтрувати».

4. Виберіть «Сортувати від А до Я».

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

Якщо у вас є рядок заголовка, його також буде розпізнано.

Як сортувати за допомогою підказки Excel, щоб зберегти рядки разом

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

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

Виконайте такі дії:

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

2. Перейдіть на вкладку «Головна».

3. Розгорніть спадне меню «Сортувати та фільтрувати».

4. Виберіть «Сортувати від А до Я» (або на свій смак).

5. У вікні «Попередження про сортування», що з’явиться, виберіть «Розширити вибір».

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

Розширюючи вибір, зберігаючи пов’язані дані в тому самому рядку без змін.

Як використовувати функцію сортування, щоб зберегти рядки разом

Функція SORT в Excel автоматично зберігає рядки разом під час сортування.

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

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

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

Синтаксис функції SORT такий:

=СОРТ(масив, [індекс_сортування], [порядок_сортування], [стовпець_або_рядок]

• масив: діапазон комірок для сортування.

• sort_index: стовпець для сортування.

• sort_order: TRUE для зростання (за замовчуванням) і FALSE для спадання.

• column_or_row: FALSE для стовпців (за замовчуванням) і TRUE для рядків

Припустімо, у вас є дані в клітинках A1–B6 із заголовком. Ви хочете відсортувати ці дані за значеннями в стовпці A в порядку зростання. Використовуйте цю формулу:

=СОРТ(A2:B6)

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

Сортування та фільтрація разом

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

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

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

1. Відфільтруйте рядки в стовпці B (колір).

2. Відсортуйте відфільтровані рядки за стовпцем А (назва елемента).

Це синтаксис функції FILTER:

=ФІЛЬТР(масив, критерії, [якщо_порожній])

• масив: діапазон комірок для сортування.

• критерії: за чим фільтрувати.

• if_empty: що повернути, якщо немає відповідності.

Головне — обернути функцію SORT навколо відфільтрованих результатів. Для нашого прикладу ви б використали цю формулу:

=SORT(FILTER(A2:B6;B2:B6 = “Зелений”), 1)

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

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

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

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

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

1. Створіть список.

2. Використовуйте список як послідовність порядку.

Як створити власний список

1. Перейдіть на вкладку Файл.

2. Виберіть Параметри.

3. Натисніть Додатково.

4. Прокрутіть униз до розділу Загальні.

5. Натисніть «Редагувати настроюваний список», щоб відкрити діалогове вікно «Настроювані списки».

6. Додайте впорядковані записи як список, розділений комами.

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

Як використовувати список

Тепер, коли ви створили список, ви можете застосувати його до набору даних.

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

2. Перейдіть на вкладку Дані.

3. Натисніть кнопку Сортувати в групі Сортувати та фільтрувати.

4. Виберіть «розширити вибір», щоб переглянути діалогове вікно сортування.

5. Розгорніть спадний список під заголовком «Замовлення».

6. Виберіть «Користувацький список».

7. Виберіть свій список.

8. Натисніть ОК двічі.

Ця інфографіка показує послідовність:

3 поради щодо обробки типів даних

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

1. Щоб відсортувати текст (в алфавітному порядку), виберіть клітинку в стовпці, який потрібно відсортувати, перейдіть на вкладку «Дані» та натисніть «Сортувати від А до Я» (за зростанням) або «Сортувати від Я до А» (за спаданням).

2. Під час сортування за днями чи місяцями переконайтеся, що ваші дати мають узгоджений формат (наприклад, ДД/ММ/РРРР).

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

4 поради щодо ефективного сортування

Ось наші найкращі поради щодо ефективного сортування:

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

2. Під час сортування великих даних корисно використовувати функцію «Закріпити панелі».

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

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

Сортування даних для видалення порожніх рядків є корисною підготовкою, коли ви шукаєте різні значення у своїх даних. Це відео показує завдання в дії:

Заключні думки

Сортування — одне з найпоширеніших завдань під час роботи з електронною таблицею Excel.

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

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