Ви можете використовувати Excel, щоб розбити інформацію на менші частини. Знайти потрібні дані та маніпулювати ними є важливою метою для багатьох користувачів Excel.
Якщо у вас є повне ім’я людини, вам може знадобитися вказати лише її ім’я чи прізвище. Наприклад, якщо ви надсилаєте своїм клієнтам дружній автоматизований електронний лист, вам потрібно використовувати їхні імена, щоб не звучати безособово. Якщо ви переглядаєте список респондентів опитування, можливо, важливо просто використовувати їхні прізвища або приховати їхні прізвища, щоб зберегти анонімність.
Excel робить цей процес простим, і ви можете застосувати багато різних підходів. Ось підручник, який допоможе вам створити окремі стовпці імені та прізвища за допомогою формул. Ми також розглядаємо питання по батькові.
Формули Excel для поділу імен на частини
З чого почати?
Розділення імен
Це загальна формула:
=ВЛІВО(клітина,ЗНАЙТИ(” “,клітина,1)-1)
Щоб виконати його, замініть клітинка з покажчиком клітинки, який містить перше повне ім’я, яке потрібно розділити. У цьому прикладі ви хочете вибрати B2 і ввести формулу:
=ВЛІВО(A2,ЗНАЙТИ(” “,A2,1)-1)
Однак важливо зазначити, що на деяких пристроях ця формула використовує крапку з комою замість коми. Отже, якщо наведена вище формула не працює для вас, можливо, вам знадобиться використовувати таку версію:
=ВЛІВО(клітина;ЗНАЙТИ(” “;клітинка;1)-1)
У прикладі ви використовуєте:
=ВЛІВО(A2;ЗНАЙТИ(” “;A2;1)-1)
Тепер ви можете просто перетягнути маркер заповнення вниз до кінця стовпця Ім’я.
Функція LEFT дозволяє відокремлювати рядок, починаючи з лівого кінця тексту. Частина FIND цієї формули знаходить перший пробіл у повному імені, тому ви отримуєте частину вашого повного імені, яка стоїть перед порожнім пробілом.
Отже, імена через дефіс залишаються разом, а також імена, що містять спеціальні символи. Але ваш стовпець Повне ім’я не міститиме по батькові чи середніх ініціалів.
Кома чи крапка з комою?
Чому формула не однакова для всіх?
Для багатьох користувачів Excel функції Excel використовують кому для розділення вхідних даних. Але на деяких пристроях регіональні налаштування відрізняються.
Щоб дізнатися, який символ використовується у вашому Excel, просто почніть вводити формулу. Коли ви починаєте, введіть =ВЛІВО(, ви побачите текст при наведенні курсора, який запропонує правильне форматування.
Розділення прізвищ
Так само підходьте до розділення прізвищ. Цього разу ви повинні використовувати ПРАВИЛЬНУ формулу, яка розділяє рядки, починаючи з правого боку.
Потрібна формула:
=ВПРАВО(комірка, LEN(комірка) – ПОШУК(“#”, ЗАМІНИТИ(комірка,” “, “#”, LEN(комірка) – LEN(ЗАМІСТИТИ(комірка, ” “, “”)))))
У наведеному вище прикладі ви використовували б таку формулу в клітинці C2:
=ВПРАВО(A2, LEN(A2) – ПОШУК(“#”, ЗАМІНИТИ(A2,” “, “#”, LEN(A2) – LEN(ЗАМІСТИТИ(A2, ” “, “”)))))
Знову ж таки, вам може знадобитися переключитися з коми на крапку з комою, що означає, що вам може знадобитися використовувати:
=ВПРАВО(A2; LEN(A2) – ПОШУК(“#”; ЗАМІНИТИ(A2;” “; “#”; LEN(A2) – LEN(ЗАМІНИТИ(A2; ” “; “”)))))
Прізвища через дефіс та прізвища зі спеціальними символами залишаються неушкодженими.
Чому ця формула складніша, ніж формула для імен? Складніше відокремити по батькові та середні ініціали від прізвищ.
Якщо ви хочете, щоб імена та ініціали були перераховані разом з прізвищами, ви можете використовувати формулу:
=ВПРАВО(комірка, LEN(комірка) – ПОШУК(” “, клітинка))
або:
=ВПРАВО(A2, LEN(A2) – ПОШУК(” “, A2))
або:
=ВПРАВО(A2; LEN(A2) – ПОШУК(” “; A2))
Але що, якщо ви хочете відокремити по батькові? Це рідше, але це може бути корисно знати.
Відокремлення по батькові
Формула для по батькові така:
=MID(клітина, SEARCH(” “, клітинка) + 1, SEARCH(” “, клітинка, SEARCH(” “, клітинка)+1) – SEARCH(” “, клітинка)-1)
У наведеному вище прикладі ви отримуєте:
=MID(A2, SEARCH(” “, A2) + 1, SEARCH(” “, A2, SEARCH(” “, A2)+1) – SEARCH(” “, A2)-1)
Якщо ваш Excel використовує крапку з комою, формула виглядає так:
=MID(A2; SEARCH(” “; A2) + 1; SEARCH(” “; A2; SEARCH(” “; A2)+1) – ПОШУК(” “; A2)-1)
Після введення формули перетягніть маркер заповнення вниз. Ось стовпець по батькові, доданий до прикладу вище:
Якщо повне ім’я не містить по батькові чи ініціали, у цьому стовпці ви отримаєте нульові значення, які можуть відображатися як #VALUE!. Щоб отримати порожні клітинки замість #ЗНАЧЕННЯ!, можна скористатися функцією IFERROR.
Тоді ваша формула стає:
=ЯКЩО ПОМИЛКА(СЕРЕДИНА(комірка, ПОШУК(” “, клітинка) + 1, ПОШУК(” “, клітинка, ПОШУК(” “, клітинка)+1) – ПОШУК(” “, клітинка)-1),0)
або:
= ЯКЩО(СЕРЕДИНА(A2, SEARCH(” “, A2) + 1, SEARCH(” “, A2, SEARCH(” “, A2)+1) – ПОШУК(” “, A2)-1),0)
або:
= ЯКЩО(СЕРЕДИНА(A2; ПОШУК(” “; A2) + 1; ПОШУК(” “; A2; ПОШУК(” “; A2)+1) – ПОШУК(” “; A2)-1);0)
Один підхід до розділення кількох імен
Що станеться, якщо хтось із вашого списку має кілька по батькові? Використовуючи наведену вище формулу, буде отримано лише їхнє по батькові.
Щоб вирішити цю проблему, ви можете спробувати інший підхід до розділення по батькові. Якщо у вас створені стовпці імені та прізвища, ви можете просто обрізати їх. Все, що залишиться, вважатиметься по батькові.
Ця формула:
=TRIM(MID(клітина1,LEN(cell2)+1,LEN(cell1)-LEN(клітина2&cell3)))
Тут клітинка1 посилається на покажчик комірки в стовпці Повне ім’я, клітинка2 посилається на покажчик комірки в стовпці Ім’я, а клітинка3 посилається на покажчик комірки в стовпці Прізвище. У наведеному вище прикладі ми отримуємо:
=ОБРИМ(СЕРЕДНІЙ(A2,LEN(B2)+1,LEN(A2)-LEN(B2&D2))))
або:
=ОБРИМ(СЕРЕДНІЙ(A2;LEN(B2)+1;LEN(A2)-LEN(B2&D2))))
Якщо ви використовуєте цю формулу, вам не доведеться турбуватися про нульові значення.
Швидкий підсумок
Ось формули, які можна використовувати для поділу повних імен на частини:
Ім'я: =ВЛІВО(клітина,ЗНАЙТИ(” “,клітина,1)-1)
Прізвища: =ВПРАВО(комірка, LEN(комірка) – ПОШУК(“#”, ЗАМІНИТИ(комірка,” “, “#”, LEN(комірка) – LEN(ЗАМІСТИТИ(комірка, ” “, “”)))))
Середні імена: =ЯКЩО ПОМИЛКА(СЕРЕДИНА(комірка, ПОШУК(” “, клітинка) + 1, ПОШУК(” “, клітинка, ПОШУК(” “, клітинка)+1) – ПОШУК(” “, клітинка)-1),0)
Альтернативна формула для по батькові: =TRIM(MID(клітина1,LEN(cell2)+1,LEN(cell1)-LEN(клітина2&cell3)))
Розділення імені та прізвища без використання формул
Якщо вам не хочеться вводити купу формул, які можуть бути введені неправильно, скористайтеся перевагами вбудованого в Excel майстра перетворення тексту в стовпці.
- Переконайтеся, що Дані виберіть вкладку з меню у верхній частині та виділіть стовпець, який потрібно перетворити.
- Потім натисніть на Текст до стовпців.
- Далі переконайтеся Розмежований вибрано та натисніть Далі
. - Тепер виберіть Космос з параметрів і натисніть Далі.
- Потім змініть Пункт призначення до «$B$2” і натисніть Закінчити.
Кінцевий результат повинен виглядати так. 
.
Кінцевий результат повинен виглядати так. 
Заключне слово
Є багато інших способів вирішення цієї проблеми в Excel. Якщо жоден з доступних варіантів не відповідає вашим потребам, виконайте додаткові дослідження.
Використання формул відносно просте і не залежить від версії Excel, яку ви використовуєте. Але, на жаль, ви все одно можете зіткнутися з помилками.
Наприклад, якщо повне ім’я людини починається з прізвища, воно буде розділятися неправильно. У формул також виникають проблеми з прізвищами, які містять префікси або суфікси, наприклад, Ле Карре або Ван Гог. Якщо чиєсь ім’я закінчується на Jr., це буде зазначено як його прізвище.
Однак є зміни, які ви можете додати, щоб вирішити ці проблеми по мірі їх появи. Робота з формулами дає вам гнучкість, необхідну для вирішення цих складнощів.
