Подивіться відео - Як відсортувати за прізвищем в Excel
Якщо ви працюєте з наборами даних імен, їх сортування є одним із поширених завдань, які вам доводиться виконувати часто.
Сортувати дані в алфавітному порядку на основі повного імені досить просто, де Excel використовує перший символ імені для сортування.
Але що, якщо ти хочеш сортувати дані за прізвищем в Excel?
Хоча це не так просто, це все ж можна зробити (багато що також залежить від способу структури даних імен).
Який би метод ви не використовували, вам доведеться якимось чином витягнути прізвище з повного імені та поставити його в окрему колонку. Потім ви можете використовувати цей стовпець для сортування даних за прізвищем в алфавітному порядку.
У цьому підручнику Excel я покажу вам, як сортувати стовпець з іменами на основі прізвища.
Тож почнемо!
Витяг та сортування за прізвищем за допомогою функції «Знайти та замінити»
Першим кроком до сортування за прізвищем є отримання прізвища в окремому стовпці.
Ви можете зробити це, замінивши все перед прізвищем пустим, щоб у вас залишилося лише прізвище.
Припустимо, у вас є набір даних, як показано нижче, і ви хочете відсортувати ці дані в алфавітному порядку за прізвищем.
Нижче наведено кроки для сортування за прізвищем:
- Виберіть набір даних, включаючи заголовок (у цьому прикладі це буде A1: A10)
- Скопіюйте його в сусідній стовпець (якщо сусідній стовпець не порожній, вставте новий стовпець, а потім скопіюйте ці назви)
- Перейменуйте скопійований заголовок стовпця. У цьому прикладі я буду називати "Прізвище"
- Виберіть усі скопійовані імена (не вибирайте заголовок)
- Утримуйте клавішу Control, а потім натисніть клавішу H. Відкриється діалогове вікно «Знайти та замінити».
- У полі Знайти що введіть * (символ зірочки, після якого пробіл)
- Залиште поле «Замінити на» порожнім
- Натисніть Замінити все. Це миттєво замінить все ім’я, і у вас залишиться лише прізвище.
Вищезазначені кроки збережуть прізвище та видалять все перед ним. Це добре працює, навіть якщо у вас є по батькові або префікси (наприклад, Mr. або Ms).
Після того, як у сусідньому стовпці є прізвища, ви можете легко сортувати набір даних (включаючи повні імена) в алфавітному порядку на основі прізвища.
Нижче наведено кроки для сортування за прізвищем:
- Виберіть весь набір даних із заголовками (включаючи повні імена та вилучені прізвища). Ви також можете включити інші стовпці, які потрібно відсортувати разом з іменами
- Перейдіть на вкладку Дані
- Натисніть Сортувати
- У діалоговому вікні Сортування переконайтеся, що вибрано «Мої дані мають заголовки».
- У опції "Сортувати за" виберіть назву стовпця, у якому є лише прізвище
- У розділі "Сортувати ввімкнено" виберіть "Значення клітинки"
- У опції "Замовити" виберіть "від А до Я"
- Натисніть OK
Наведені вище кроки відсортують весь вибраний набір даних на основі прізвища.
Після цього ви можете видалити стовпець із прізвищем.
Професійна порада: У будь-який момент часу, якщо ви вважаєте, що вам можуть знадобитися повернути вихідні дані, вам потрібно мати спосіб відсортувати цей набір даних. Для цього у сусідньому стовпці (ліворуч або праворуч) поставте порядкові номери перед сортуванням. Тепер, якщо вам потрібні вихідні дані, ви отримаєте їх шляхом сортування на основі цифр.Витяг та алфавіт за прізвищем за допомогою формули
Хоча вищеописаний метод (за допомогою функції «Знайти та замінити») - це те, що я вважаю за краще отримати всі прізвища та сортувати на їх основі, одне його обмеження полягає в тому, що отримані дані у статичному вигляді.
Це означає, що якщо я додаю до свого списку більше імен, мені доведеться повторити той самий процес, щоб отримати прізвища.
Якщо це те, чого ви не хочете, ви можете використовувати метод формули для сортування даних за прізвищами.
Припустимо, у вас є набір даних, як показано нижче.
Нижче наведена формула, яка витягне прізвище з повного імені:
= ПРАВО (A2, LEN (A2) -FIND ("", A2))
Наведена вище формула спирається на шаблон із повним іменем (у цьому прикладі міститься лише ім’я та прізвище). Шаблон полягає в тому, що між ім'ям та прізвищем буде пробіл.
Функція FIND використовується для визначення позиції пробілу. Потім це значення віднімається від загальної довжини імені, щоб отримати загальну кількість символів у прізвищі.
Потім це значення використовується у функції ПРАВО для отримання прізвища.
Після того, як у вас є стовпець з прізвищем, ви можете сортувати ці дані (це детально описано у першому методі).
Наведена вище формула буде працювати, якщо у вас є лише ім’я та прізвище.
Але що, якщо у вас також є по батькові. Або може бути привітання перед іменем (наприклад, пан чи пані)
У такому випадку вам потрібно скористатися формулою нижче:
= ПРАВО (A2, LEN (A2) -FIND ("@", SUBSTITUTE (A2, "", "@", LEN (A2) -LEN (SUBSTITUTE (A2, "", ""))))
Наведена вище формула знаходить позицію останнього символу пробілу, а потім використовує його для вилучення прізвища.
Я рекомендую вам використовувати другу формулу у всіх випадках, і вона є більш надійною і може обробляти всі випадки (якщо прізвище знаходиться в кінці імені).
Примітка: Ці дві формули спираються на умову, що між кожним елементом імені є лише один символ пробілу. У разі наявності подвійних пробілів, або провідних/кінцевих пробілів, ця формула дасть неправильні результати. У такому випадку найкраще скористатися функцією TRIM, щоб спочатку позбутися будь -яких початкових, кінцевих і подвійних пробілів, а потім скористатися наведеною вище формулою.
Хоча це може здатися складним методом, перевага використання формули полягає в тому, що вона робить результати динамічними. Якщо ви додаєте до свого списку більше імен, вам залишається лише скопіювати формулу, і вона дасть вам прізвище.
Використання тексту в стовпцях
Текст у стовпці - це знову ж таки простий і легкий спосіб розділити клітинки в Excel.
Ви можете вказати роздільник (наприклад, кому або пробіл) і використовувати його для розділення вмісту комірки. Коли у вас є розділені елементи в окремих стовпцях, ви можете використовувати стовпець з прізвищем для алфавітування даних.
Припустимо, у вас є набір даних, як показано нижче:
Нижче наведено кроки для використання тексту в стовпці для сортування за прізвищем:
- Виберіть стовпець з назвою (за винятком заголовка)
- Перейдіть на вкладку Дані
- У групі "Інструменти даних" натисніть опцію Текст у стовпці. Відкриється майстер перетворення тексту в стовпці
- На кроці 1 "Майстра перетворення тексту в стовпці" виберіть "Розділений" і натисніть Далі
- На кроці 2 виберіть "Пробіл" як роздільник (і зніміть прапорець біля будь -якого іншого, якщо вибрано), а потім натисніть кнопку Далі.
- На кроці 3 виберіть перший стовпець імені у вікні попереднього перегляду даних, а потім виберіть опцію «Не імпортувати стовпці (пропустити)». Це гарантує, що ім’я не є частиною результату, і ви отримаєте лише прізвище.
- Також на кроці 3 змініть осередок призначення на той, що прилягає до вихідних даних. Це переконається, що ви отримаєте прізвище окремо, а дані оригінальних імен недоторкані.
- Натисніть кнопку Готово
Отримавши результат, можна сортувати за прізвищем.
Ви також можете надсилати текст у стовпці, щоб відокремити ім’я та прізвище, якщо у вас роздільником є кома.
Використання Flash Fill
Ще один швидкий і швидкий спосіб отримати прізвища - це використання функції заповнення Flash.
Flash Fill був представлений в Excel 2013 і допомагає маніпулювати даними шляхом визначення шаблонів. Щоб це спрацювало, вам потрібно кілька разів показати результат Flash Fill, який ви очікуєте.
Після того, як він ідентифікує візерунок, він швидко виконає решту роботи за вас.
Припустимо, у вас є набір даних імен нижче.
Нижче наведено кроки для використання Flash Fill для отримання прізвища, а потім для його сортування:
- У клітинку В2 введіть текст «Морі». Це результат, якого ви очікуєте в клітині.
- Перейдіть до наступної клітинки та введіть прізвище імені в сусідній клітинці (Елліот у цьому прикладі).
- Виділіть обидві клітинки
- Наведіть курсор на нижню праву частину виділення. Ви помітите, що курсор зміниться на значок плюса.
- Двічі клацніть по ньому (або клацніть і перетягніть його вниз). Це дасть вам певний результат у клітинках (навряд чи це буде той результат, якого ви хочете)
- Натисніть на значок «Параметри автозаповнення».
- Натисніть на Flash Fill
Це дасть вам результат, який, ймовірно, буде прізвищем у всіх клітинках.
Я кажу швидше за все, оскільки Flash Fill у деяких випадках може не працювати. Оскільки це залежить від ідентифікації шаблону, це може бути не завжди можливим. Або іноді шаблон, який він розшифровує, може бути неправильним.
У таких випадках слід ввести очікуваний результат ще в одну або дві клітинки, а потім виконати кроки 4-7.
Після того, як усі прізвища будуть у стовпці, можна сортувати дані на основі цих прізвищ.
Отже, це чотири різні способи, за допомогою яких можна сортувати дані за прізвищем. Найкращим методом було б використати техніку «Знайти та замінити», але якщо ви хочете зробити результати динамічними, метод формули - це шлях.
Сподіваюся, цей підручник був вам корисним.
