Цей посібник покаже вам, як розмістити в Excel алфавітом за допомогою функції сортування та фільтрування, а також формули для впорядкування та форматування даних у порядку.
Microsoft Excel є ефективним програмним забезпеченням для організації важливих даних, таких як фінанси, дані про продажі, інформація про клієнтів тощо. Алфавітне розміщення даних у робочих аркушах є одним із найкращих способів швидкого впорядкування, доступу до цих даних і посилання на них. Розміщення діапазону клітинок за алфавітом означає сортування діапазону в алфавітному порядку в Excel. Дані можуть бути відсортовані обома способами, або в порядку зростання, або в порядку спадання.
Наприклад, припустимо, що у вас є великий набір даних про замовлення клієнта, і ви хочете знайти історію замовлень конкретного клієнта. Прочесати весь список стане справою справою. Замість того, щоб витрачати час на прокручування, ви можете розставити стовпці за алфавітом, щоб швидко знайти свої дані.
Залежно від типу набору даних, з яким ви працюєте, ви можете використовувати різні способи для алфавіту даних у Excel. Excel дозволяє сортувати один стовпець або рядок, вибраний діапазон або весь аркуш, а також параметри групування кількох стовпців або рядків під час їх сортування. Давайте дізнаємося, як розставити дані в алфавітному порядку в електронних таблицях Excel за рядками або стовпцями.
Навіщо складати дані в алфавіті в Excel
Алфавітність даних матиме ряд переваг:
- Це робить дані більш розумними та полегшує їх читання.
- Це полегшує користувачеві пошук певного значення або імені клієнта в таблиці даних Excel.
- Це допомагає користувачеві візуально ідентифікувати повторювані записи, щоб ми могли запобігти помилкам при введенні даних.
- Це дозволяє користувачам легко групувати стовпці або списки разом, щоб користувачі могли дивитися на них поруч.
Алфавітний порядок (сортування за алфавітом) — це простий і поширений спосіб швидкого сортування інформації. Це особливо корисно для дуже великих наборів даних. Всього в Excel є 4 способи алфавіту даних: кнопка A-Z або Z-A, функція сортування, функція фільтра та формули.
Алфавітність стовпця в Excel
Найшвидший спосіб сортування в алфавітному порядку в Excel – це використання вбудованої функції сортування.
Спочатку відкрийте електронну таблицю, яка містить набір даних. Виберіть клітинки або стовпець, які потрібно відсортувати, і переконайтеся, що не виділено жодних порожніх клітинок. Потім перейдіть на вкладку «Дані» та натисніть «A-Z», щоб сортувати за зростанням, або «Z-A», щоб сортувати за спаданням у групі «Сортування та фільтр».
Якщо між ними є порожні клітинки, Excel припускає, що всі дані відсортовано, і зупиняється над чорною коміркою. Якщо ви хочете відсортувати весь стовпець, просто виберіть будь-яку клітинку в стовпці та скористайтеся наведеною вище опцією.
Ці ж параметри також можна отримати в інструменті «Сортування та фільтрування» у розділі «Редагування» вкладки «Головна».
Після того, як ви натиснете кнопку, Excel негайно розмістіть свій список за алфавітом.
Як сортувати та зберігати рядки разом у Excel
Ви можете легко відсортувати стовпець, якщо у вашому аркуші є лише один стовпець, але це трохи ускладнюється, коли ваш аркуш містить кілька стовпців поруч із стовпцем, який ви хочете відсортувати. Крім того, іноді ми працюємо з електронними таблицями, де після сортування стовпця нам потрібно зберегти відповідні рядки неушкодженими.
Наприклад, якщо у нас є електронна таблиця зі списком оцінок учнів. Коли ми сортуємо стовпець з іменами студентів в алфавітному порядку, ми також хочемо, щоб позначки, введені в рядках поруч із ними, також переміщалися відповідно.
У таких випадках ви можете використовувати кнопку «A-Z» або «Z-A», щоб відсортувати один із стовпців в алфавітному порядку, і він автоматично розташує дані в інших стовпцях, зберігаючи рядки разом. Давайте подивимося, як це працює на іншому прикладі.
Спочатку виберіть перший стовпець, за яким потрібно відсортувати інші стовпці. Потім виберіть «A-Z» або «Z-A» на вкладці «Дані» або «Домашня сторінка», щоб відсортувати дані в одному стовпці, і Excel автоматично змінить порядок інших стовпців відповідно.
Після натискання кнопки сортування з’явиться діалогове вікно Попередження про сортування. Виберіть перемикач «Розширити вибір» і натисніть кнопку «Сортувати».
Це дозволить утримувати рядки разом під час сортування стовпців, не збігаючи жодних даних (як показано нижче).
Якщо вибрати параметр «Продовжити з поточним виділенням», Excel відсортує лише вибраний стовпець.
Як сортувати в алфавітному порядку за кількома стовпцями
Якщо ви хочете відсортувати набір даних в алфавітному порядку більш ніж за одним стовпцем, ви можете зробити це за допомогою функції сортування Excel.
Наприклад, ми хочемо розташувати наступну таблицю в алфавітному порядку спочатку за країною, а потім за ім’ям:
Спочатку виберіть всю таблицю, яку ви хочете відсортувати за алфавітом, потім перейдіть на вкладку «Дані» і в групі «Сортування та фільтрування» натисніть «Сортувати».
Або натисніть значок «Сортування та фільтр» у розділі «Редагування» на вкладці «Головна» та виберіть «Сортування на замовлення», щоб отримати доступ до тієї ж функції.
З’явиться діалогове вікно «Сортування», яке покаже перший стовпець (параметр), у якому буде відсортовано таблицю. У спадному списку «Сортувати за» виберіть основний стовпець, за яким ви хочете спочатку розмістити дані в алфавіті, у нашому випадку «Країну», а в спадному меню «Порядок» виберіть «А до Я» або «Від Я до А». .
Потім натисніть кнопку «Додати рівень», щоб додати другий рівень сортування. Виберіть другий стовпець (у нашому випадку ім’я), за яким ви хочете розставити дані в алфавітному порядку, у полі «Сортувати за», і виберіть «А до Я» або «Я до А». Якщо ваша таблиця має заголовки зверху, поставте прапорець «Мої дані мають заголовки», щоб вона могла пропускати заголовки під час їх сортування. За потреби додайте більше рівнів сортування та натисніть «ОК».
Таблиця впорядкована в алфавітному порядку: спочатку за країною, а потім за прізвищем, як показано нижче.
Як відсортувати рядки за алфавітом в Excel
Іноді вам може знадобитися розмістити рядки в Excel за алфавітом, а не стовпці. Це можна зробити за допомогою функції сортування Excel. Наприклад, скажімо, у вас є електронна таблиця, де в першому рядку стовпців B–T містяться назви міст, де кількість магазинів, доступних у різних типах роздрібної торгівлі, а стовпець A містить тип роздрібного продавця. Комірки використовуються для відстеження кількості магазинів у кожному місті в різних категоріях роздрібних продавців.
Спочатку виділіть усі рядки, які потрібно розмістити в алфавіті; Якщо ви не хочете переміщувати мітки рядків, виключіть їх із вибору. Тепер перейдіть на вкладку «Дані» та натисніть «Сортувати» в групі «Сортування та фільтрування».
У діалоговому вікні «Сортування» натисніть кнопку «Параметри».
З’явиться ще одне невелике діалогове вікно, у якому можна вибрати «Сортувати зліва направо» або «Сортувати зверху вниз». Виберіть опцію «Сортувати зліва направо», щоб розмістити в алфавіті за рядком, і натисніть «ОК», щоб повернутися до діалогового вікна «Сортування».
Тепер виберіть рядок, який потрібно розмістити в алфавіті, у спадному списку «Сортувати за» (у цьому прикладі рядок 1). В іншому полі залиште значення клітинок у полі «Сортувати на», а в полі «Порядок» виберіть «А до Я» (у порядку зростання). Потім натисніть «ОК», щоб закрити діалогове вікно.
Тепер перший рядок у нашій таблиці відсортовано в алфавітному (зростаючому) порядку, а решта рядків упорядковано відповідно, як показано нижче.
Як відсортувати стовпець за алфавітом за допомогою фільтра
Ще один швидкий спосіб сортування в алфавітному порядку в Excel – використання функції фільтра. Після застосування фільтрів до стовпців доступ до параметрів сортування для всіх стовпців можна отримати лише одним клацанням миші.
Щоб додати фільтр до стовпців, виберіть один або кілька стовпців і натисніть опцію «Фільтр» у групі «Сортування та фільтрування» на вкладці «Дані». Якщо ви хочете застосувати фільтр до всієї таблиці, виберіть будь-яку клітинку в таблиці та натисніть опцію «Фільтр».
Ви також можете отримати доступ до параметра «Фільтр», натиснувши інструмент «Сортування та фільтр» у групі «Редагування» на вкладці «Головна» та вибравши «Фільтр».
У кожному з заголовків стовпців з’явиться маленька стрілка спадного меню. Натисніть стрілку будь-якого стовпця, який потрібно відсортувати за алфавітом, і виберіть «Сортувати від А до Я» або «Сортувати від Я до А».
Це розташує потрібний стовпець у вибраному вами алфавітному порядку, і якщо ви виберете «Сортувати від А до Я», на кнопці фільтра з’явиться маленька стрілка вгору, яка вказує порядок сортування (за зростанням):
Розширене сортування в спеціальному порядку
Сортування за алфавітом не ідеально підходить для всіх алфавітних даних. Іноді дані можна розмістити в алфавіті, але це не найефективніший спосіб їх сортування.
Давайте уявімо ситуацію, коли у вас може бути список, що містить назви місяців або днів тижня, сортування за алфавітом у цій ситуації не корисно. Доречніше сортувати цей список у хронологічному порядку. Якщо відсортувати від А до Я, це розташує місяці в алфавітному порядку, першим буде квітень, потім серпень, лютий, червень і так далі. Але це не те, чого ти хочеш. На щастя, дуже легко впорядкувати в хронологічному порядку в Excel за допомогою параметра Advanced Custom Sort.
Почніть з вибору таблиці, яку потрібно відсортувати. Потім виберіть «Сортування на замовлення» в розділі «Сортування та фільтр» у розділі «Редагування» на вкладці «Головна».
У діалоговому вікні «Сортування» виберіть стовпець, який містить місяці року, у розділі стовпців, відсортуйте, як зазвичай, «Значення», а в розділі «Порядок» виберіть «Спеціальний список».
У діалоговому вікні Custom Lists ви можете створити власний список. Існують деякі спеціальні списки за замовчуванням, включаючи дні тижня, скорочені місяці, місяці року тощо. Виберіть відповідний варіант сортування відповідно до ваших потреб (у нашому випадку січень, лютий, ..) і натисніть «ОК». Потім знову натисніть «ОК», щоб відсортувати список у хронологічному порядку.
Ви помітите, що ви успішно відсортували список відповідно до порядку місяця.
Як сортувати за алфавітом у Excel за допомогою формул Excel
Якщо ви шанувальник формул, то можете використовувати формули Excel для алфавіту списку. Дві формули, які можна використовувати для алфавіту даних: COUNTIF і VLOOKUP.
Наприклад, у нас є список імен, які ми хочемо розмістити в алфавіті за допомогою формули.
Щоб відсортувати це, ми додамо тимчасовий стовпець під назвою «Порядок сортування» до існуючої таблиці.
У клітинці (A2) поруч із даними скористайтеся такою формулою COUNTIF:
=COUNTIF($B$2:$B$20,"<="&B2) Наведена вище формула порівнює текстове значення в клітинці B2 з усіма іншими текстовими значеннями (B3:B20) і повертає його відносний ранг. Наприклад, у клітинці B2 він повертає 11, оскільки є 11 текстових значень, які нижчі або дорівнюють тексту «Ненсі» в алфавітному порядку. Результатом є порядок сортування імені співробітника в клітинці B2.
Потім за допомогою маркера заповнення перетягніть цю формулу, щоб заповнити її у всьому діапазоні. Це надає порядок сортування кожного з імен у списку.
Тепер ми повинні впорядкувати дані, показані на знімку екрана вище, на основі номера порядку сортування, і для цього ми збираємося використовувати функцію VLOOKUP.
Синтаксис:
=VLOOKUP(,A:B,2,0)Тут «номер сортування» представляє числа в порядку зростання від 1 до 20. У порядку спадання числа мають бути від 20 до 1.
Тут у нас є стовпець D для відсортованих імен. Для комірки D2 введіть таку формулу VLOOKUP:
=VLOOKUP(1,A:B,2,0)Аналогічно, для другої та третьої клітинок ви повинні використовувати формулу як:
=VLOOKUP(2,A:B,2,0) і =VLOOKUP(3,A:B,2,0) і так далі…
Після введення цієї формули VLOOKUP у кожну клітинку поруч із даними, список розбивається в алфавіті.
Замість того, щоб вручну вводити вищезгадану формулу (1-20) до кожної клітинки, ви також можете використовувати функцію рядка, щоб полегшити вашу роботу. Функція Row () повертає номер рядка поточної комірки. Отже, за допомогою функції рядка формула буде мати вигляд:
=VLOOKUP(ROW()-1,A:B,2,0)Ця формула дасть вам той самий результат, що й формула вище.
А потім за допомогою маркера заповнення перетягніть цю формулу, щоб заповнити її у всьому діапазоні.
Як розставити записи в алфавіті за прізвищем
Іноді нам часто доводиться сортувати таблиці даних за алфавітом з прізвищами. У таких випадках, навіть якщо імена починаються з імен, вам потрібно розставити їх за алфавітом за прізвищем. Ви можете зробити це за допомогою текстових формул Excel.
Для цього спочатку потрібно витягти ім’я та прізвище з повних імен у дві різні колонки. Потім поверніть назви, відсортуйте їх, а потім поверніть їх до початкової форми.
Маючи повне ім’я в A2, введіть такі формули в дві різні клітинки (B2 і C2), а потім скопіюйте формули вниз по стовпцях (за допомогою маркера заповнення) до останньої клітинки з даними:
Щоб витягти ім’я, введіть цю формулу в клітинку C2:
=ВЛІВО(A2,ШУКАТИ(” “,A2)-1)
Щоб витягти прізвище, введіть цю формулу в клітинку D2:
=ВПРАВО(A2,LEN(A2)-ПОШУК(" ",A2,1))
А потім, у клітинці E2, об’єднали ім’я та прізвище у зворотному порядку, розділивши комою:
=D2&", "&C2
Ми розділили ім’я та прізвище та поміняли їх місцями. Тепер ми повинні розмістити їх в алфавіті та повернути назад. Як ви можете бачити вище, коли ми витягуємо імена за допомогою формули, стовпці C, D і E насправді містять формули, але вони відформатовані так, щоб вони виглядали як значення. Отже, нам потрібно перетворити формулу на значення.
Для цього спочатку виділіть усі клітинки формули (E1:E31) і натисніть Ctrl + C скопіювати їх. Клацніть правою кнопкою миші вибрані клітинки, клацніть піктограму «Значення» під «Параметри вставки» та натисніть клавішу «Enter».
Потім виберіть будь-яку клітинку в отриманому стовпці та натисніть опцію «А до Я» або «Я до А» на вкладці «Дані» або «Головна» відповідно до ваших потреб.
Після натискання кнопки з’явиться діалогове вікно попередження про сортування. Виберіть опцію «Розгорнути вибір» і натисніть кнопку «Сортувати».
Як бачимо, вся колонка в алфавіті за прізвищем.
Тепер, якщо ви хочете повернути його назад до початкового формату «Ім’я Прізвище», вам просто потрібно ввести ті самі формули, але з різними посиланнями на клітинки.
Вам потрібно знову розділити ім’я (E2) на дві частини, використовуючи наведені нижче формули:
У G2 витягніть ім’я:
=ВПРАВО(E2,LEN(E2)-ПОШУК(" ",E2))
У G2 витягніть прізвище:
=ВЛІВО(E2,ПОШУК(" ",E2)-2)
І з’єднайте дві частини разом, щоб отримати оригінальну повну назву:
=G2&" "&H2
Кожен раз, коли ви вводите формулу в першу клітинку, не забудьте скопіювати формулу вниз по стовпцю (за допомогою маркера заповнення) до останньої клітинки з даними.
Тепер все, що вам потрібно зробити, це знову перетворити формули на перетворення значень, як ми зробили вище, і все готово. це зроблено і.