сортування за алфавітом у Microsoft Excel

Зміст

• Спосіб 1: кнопки швидкого сортування
• Спосіб 2: спеціальне сортування
• Спосіб 3: формула сортування
  • Крок 1: Створення допоміжної формули
  • Крок 2: Створення формули сортування
  • Крок 3: модернізації формули для повторних назв
• Питання та відповіді

Спосіб 1: кнопки швидкого сортування

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

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

2. На вкладці &171; Головна & 187; відкрийте спадне меню &171; редагування&187; .

3. У ньому використовуйте блок &171; сортування та фільтрація&187; , вибравши порядок, в якому ви хочете встановити значення.

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

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

6. Другий варіант &8212; &171; Сортувати в межах зазначеного виділення&187; .

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

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

Спосіб 2: спеціальне сортування

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

1. Рекомендуємо відразу виділити всю таблицю, якщо крім сортування за алфавітом ви бажаєте додати ще кілька рівнів.

2. Потім у тому ж розділі & 171; редагування&187; вибирайте пункт & 171; спеціальне сортування&187; .

3. У випадаючому меню & 171; Сортувати за&187; вкажіть стовпець, який впливає на сортування.

4. В якості режиму сортування вибирається тип &171;значення комірок&187; .

5. Залишилося вказати тільки порядок &171; від А до Я & 187; або & 171; від Я до А&187; .

6. Якщо треба сортувати і інші стовпці, додайте їх в якості рівнів і виконайте таку ж настройку.

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

Спосіб 3: формула сортування

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

Крок 1: Створення допоміжної формули

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

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



2. Тепер діапазон виділених комірок має власне ім'я, відповідальне за його вміст, &8212; у нашому випадку це фрукти. Якщо вводите кілька слів в назві, не ставте пробіл, А використовуйте замість нього нижнє підкреслення: &171; (приклад_тексту)&187; .

3. У новій клітці створимо формулу COUNTIF , яка підраховує клітини, що задовольняють умову. В якості діапазону вказуйте створену тільки що групу, потім першу клітинку для порівняння. В результаті первісний вигляд формули такий: =COUNTIF (фрукти; A1) .

4. Зараз результатом цієї формули буде &171;1&187; , оскільки її запис не зовсім правильний для майбутніх розрахунків, тому додайте вираз "<="&038; перед номером першої комірки.

5. Розтягніть формулу, затиснувши край комірки, до кінця майбутнього списку для сортування.

6. Перейменуйте діапазон з числами в групу - це знадобиться при складанні наступної формули.

Крок 2: Створення формули сортування

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

1. У новій комірці почніть вводити =пошук (рядок(A1) . Ця формула відповідає за пошук позиції рядка, через що і слід вказати аргумент "A1" .



2. Далі для простоти додавання іменних діапазонів перейдіть до "Формули" , розгорніть меню "певні імена" і виберіть "використовувати у формулі" .

3. Додайте діапазон із допоміжною формулою та вкажіть для нього тип відображення "точна відповідність" у випадаючому списку, який з'явиться після додавання ";" .

4. Завершіть створення формули, обернувши її у функцію Індекс , яка буде працювати з масивом назв.

5. Перевірте результат, а потім розтягніть формулу так, як це вже було показано вище.

6. Тепер Ви отримаєте коректно працюючий динамічний список, відсортований за алфавітом.

Для спрощення розуміння окремо надаємо повну формулу:

=(Індекс(фрукти; ПОИСКПОЗ(рядок(A1); номер_слова; 0))) , вам же залишиться тільки відредагувати її під свої цілі і розтягнути на необхідний діапазон осередків.

Крок 3: модернізації формули для повторних назв

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

1. Відкрийте допоміжну формулу та зніміть знак "<" , щоб COUNTIF працював лише за умови рівності.

2. Додайте другу частину - + COUNTIF ($A$1: A1; A1) , що дозволяє нормально записувати однакові слова в послідовному порядку.

3. Знову розтягніть формулу, щоб вона змінилася на всіх клітинках.

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