Як отримати П. І. Б. з повних прізвища, імені та по батькові
Вітаю всіх працівників «Б & K»! Я — ваш постійний читач. Передплачую видання уже більше двох років. У будь-якому розділі знаходжу для себе багато корисної інформації. Дуже допомогли опанувати комп’ютер матеріали щодо «1С», Excel, «живі» рубрики типу «запитання-відповідь». Завдяки «Б & K» я став постійним мешканцем Інтернету. Але зараз у мене суто практичне запитання. Є велика база в Excel. В одній колонці цієї бази знаходяться прізвища, імена та по батькові працівників (повністю). Наприклад, «Іванов Іван Іванович». Мені потрібно отримати їх прізвища та ініціали, тобто замість «Іванов Іван Іванович» написати «Іванов І. І.». Підкажіть, як швидко впоратися з таким завданням. Коригувати кілька тисяч прізвищ не дуже хочеться! Наперед завдячую.
В. Волков, м. Харків
Відповідає Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства
Це завдання має, як мінімум, два рішення. Можна написати невелику програму мовою VBA (Visual Basic for Application), але цей шлях не найкращий. Для роботи з текстами, для запису результатів до комірок доведеться оперувати об’єктами, властивостями, методами. Для програміста така робота простіше простого. А от для бухгалтера вона незвична. Ми підемо іншим шляхом. Вирішимо завдання за допомогою формул. Вони вийдуть довгими. Але якщо використовуватися правильну техніку і будувати формули частинами, то ніякі труднощі нам не загрожують. Докладно про роботу з формулами можна прочитати в наших публікаціях щодо Excel: «Формули — душа таблиці» (див. «Б & К», 2006, № 1) та «Excel 2007: формули без проблем» (див. «Б & К», 2009, № 9). Однак я спробую описати процес створення формул настільки докладно, наскільки дозволить обсяг цієї статті. Приступимо.
Створюємо формулу
На рис. 1 показано робочий лист нашого прикладу. У комірці «
A1» цього листа записано текст «Иванов Вадим Петрович». На першому етапі ми в окремих колонках робочого листа послідовно отримаємо формули для визначення прізвища «Иванов», а також ініціалів «В.» і «П.», а потім об’єднаємо отримані результати до однієї довгої формули.
Нам знадобляться вбудовані функції для роботи з текстом. Їх багато, я ж зупинив свій вибір на таких.
Функція «
Найти(Текст1;Текст2;НачПоз) » знаходить заданий рядок у тексті та повертає положення шуканого рядка щодо крайнього лівого символу в цьому тексті. Синтаксис функції такий: «Найти(Текст1;Текст2;НачПоз)», де «Текст1» — це те, що ми шукаємо, «Текст2» — це рядок, в якому ми шукаємо. Параметр «НачПоз» — це номер символу в «Текст2», з якого починається пошук. За умовчанням параметр «НачПоз» дорівнює «1». Приклад: формула «=Найти("о";"Иванов";1)» поверне значення «5». Це означає, що в слові «Иванов» символ «о» знаходиться на п’ятій позиції.Важливо!
Для пошуку входжень одного рядка до іншого можна використовувати й функцію «Поиск». Між цими функціями є відмінності. Так, функція «Найти» враховує регістр символів та не допускає використання підстановних знаків (або так званих шаблонів). Функція «Поиск» не враховує регістр, зате в ній можна користуватися підстановними знаками. Їх уводять замість рядка «Текст1». Наприклад, знак питання («?») в шаблоні означає, що на цьому місці може стояти будь-який символ. Знак зірочки («*») відповідає будь-якій послідовності символів. Якщо потрібно знайти один із підстановних знаків, перед ними в шаблоні потрібно поставити знак тильди («~»).Для наших цілей достатньо функції «
Найти()». При роботі з текстом нам доведеться вирізати з нього окремі частки, щоб отримати з них ініціали. Для таких дій я пропоную використовувати функцію «ПСтр()». У неї три параметри: «Текст», «НачПоз» і «КолЗн». Функція повертає зазначену кількість знаків «КолЗн» із рядка «Текст» починаючи з позиції «НачПоз». Наприклад, формула «=ПСтр("Иванов";2;4)» поверне результат «вано». Комбінуючи ці дві функції, ми вирішимо завдання. Приступимо:1) до комірки «
B1» запишіть формулу «=Найти(" ";A1;1)». Першим параметром у формулі записано пробіл ! Уручну друкувати нічого не потрібно. Для створення формули використовуйте Майстра функцій. Другий параметр — повне ім’я (прізвище, ім’я, по батькові). Формула знаходить у повному імені (у нашому випадку це рядок «Иванов Вадим Петрович») перший пробіл — між словами «Иванов» і «Вадим». Позиція цього пробілу у вихідному тексті дорівнює «7». Щоб виділити з тексту прізвище «Иванов», потрібно вирізати з вихідного рядка символи з першого до сьомого, тобто до позиції, яку визначила формула «=Найти(" ";A1;1)». Зробимо це;2) до комірки «
С1» записуємо формулу «=Пстр(A1;1;Найти(" ";A1;1))». Результат роботи цієї формули — текст «Иванов » (рис. 1). Завершальний пробіл у цьому слові я залишив спеціально. Тепер потрібно вирізати з вихідного рядка ім’я та по батькові, тобто з повного імені потрібно взяти частину, починаючи з позиції «8» і до кінця рядка. Позиція «8» — це не що інше, як місце розташування першого пробілу плюс зсув на один символ праворуч. Отже, формула для визначення першого символу імені буде такою: «=Найти(" ";A1;1)+1». Довжину рядка нам допоможе визначити функція «ДлСтр()». Усі складові формули зрозумілі, можна продовжувати;3) до комірки «
D1» записуємо «=Пстр(A1; Найти(" ";A1;1)+1;Длстр(A1))». Результат її роботи — текст «Вадим Петрович». З цього тексту потрібно вирізати перший символ і додати до цього символу знак «.» (крапку). Для цього виконуємо такі дії:4) до комірки «
E1» уводимо формулу «=Пстр(D1;1;1)&"."». У ній функція «Пстр» вирізає перший символ із тексту «Вадим Петрович». Це не що інше, як літера «В». Операція «&» зчіплює символ «В» і крапку. У результаті виходить «В.»;5) тепер розберемося з по батькові. Послідовність дій буде схожа на попередню. Спочатку виділяємо з тексту «
Вадим Петрович» слово «Петрович». Ознака початку слова «Петрович» очевидна: це пробіл. Тому нам достатньо визначити місце розташування цього пробілу та потім вирізати відповідну частину рядка. Щоб визначити початок слова «Петрович», до комірки «F1» вводимо формулу: «=Найти(" ";D1;1)». Результатом її роботи буде число «6». Усе правильно. У рядку «Вадим Петрович» слово «Петрович» починається із сьомої позиції (тобто з позиції «6+1»);6) до комірки «
G1» уводимо формулу «=Пстр(D1;F1+1;Длстр(D1))». Вона поверне по батькові — «Петрович»;7) до комірки «
H1» уводимо формулу «=Пстр(G1;1;1)&"."». Вона вирізає зі слова «Петрович» перший символ і додає до нього крапку. Результат роботи формули — текст «П.»;8) у комірці «
I1» збираємо результат в одне ціле. Для цього до «I1» записуємо: «=C1&E1&H1». Результат роботи цієї формули — текст «Иванов В.П.». Що й потрібно було зробити.А тепер переходимо до найцікавішої частини. Спробуємо об’єднати наші зусилля в одній-однісінькій формулі. Вона вийде великою. Але отримати її неважко. Потрібно тільки робити все уважно. Нагадаю, що формула — це звичайний текст. І оперувати з нею можна як з текстовим рядком: виконувати заміну символів, копіювати, переносити і видаляти фрагменти тощо. Усі складові для узагальнюючої формули в нас є. Тільки зараз їх розташовано в різних комірках. Але, задіявши буфер обміну, ми об’єднаємо їх за лічені хвилини.
Отже, наша мета. Беремо за основу кінцеву формулу «
=C1&E1&H1». До неї послідовно підставляємо вирази з комірок «C1», «E1», «H1». У результаті отримаємо певний проміжний варіант, в якому з’являться посилання на комірки «D1», «G1». Замість цих посилань знову підставимо тексти формул з відповідних комірок. І так продовжуємо доти, доки у формулі не залишаться тільки посилання на комірку « A1» (повне ім’я працівника). Приступимо:1) переходимо до комірки «
C1». Натискуємо на «F2» (режим редагування вмісту). Стане доступним віконце з умістом комірки;2) виділяємо
текст «Пстр(A1;1; Найти(" ";A1;1))». Символ «=» виділяти не потрібно!3) за допомогою «
Ctrl+C» копіюємо текст до буфера обміну;4) натискуємо на «
Esc», щоб повернутися на робочий лист;5) переходимо до «
J1», натискуємо на «F2». У «J1» ми побудуємо остаточний варіант формули;6) у формулі «
=C1&E1&H1» видаляємо текст «С1» і вставляємо замість нього вміст буфера обміну. У результаті отримуємо « Пстр(A1;1;Найти(" ";A1;1))&E1&H1». Зміни у вихідній формулі показано напівжирним шрифтом з підкресленням;7) переходимо до комірки «
E1». Натискуємо на «F2»;8) виділяємо текст «
ПСТР(D1;1;1)&"."». Символ «=» виділяти не потрібно;9) за допомогою «
Ctrl+C» копіюємо текст до буфера обміну;10) натискуємо «
Esc», щоб повернутися на робочий лист;11) переходимо до «
J1». Натискуємо на «F2»;12) у формулі «
Пстр(A1;1;Найти(" ";A1;1))&E1&H1» видаляємо текст «E1» з буфера і вставляємо замість нього вміст буфера обміну. У результаті отримуємо «=Пстр(A1;1;Найти(" ";A1;1))& Пстр(D1;1;1)&"."&H1»;13) переходимо до комірки «
H1». Натискуємо на «F2»;14) виділяємо текст «
Пстр(G1;1;1)&"."». Символ «=» у формулі виділяти не потрібно!15) за допомогою «
Ctrl+C» копіюємо текст до буфера обміну;16) натискуємо на «
Esc», щоб повернутися на робочий лист;17) переходимо до «
J1». Натискуємо на «F2»;18) у формулі «
=Пстр(A1;1;Найти(" ";A1;1))&Пстр(D1;1;1)&"."&H1 » видаляємо текст «H1» і вставляємо вміст із буфера обміну. Отримуємо «=Пстр(A1;1;Найти(" ";A1;1)) & Пстр(D1;1;1) & "."&Пстр(G1;1;1)&"." ». Залишилося видалити в цій формулі посилання на «D1», «G1»;19) переходимо до комірки «
D1». Натискуємо на «F2»;20) виділяємо з формули текст «
Пстр(A1; Найти(" ";A1;1)+1;Длстр(A1))» (без символу «=»);21) за допомогою «
Ctrl+C» копіюємо його до буфера обміну;22) натискуємо на «
Esc», повертаємося на робочий лист;23) переходимо до «
J1». Натискуємо на «F2»;24) у формулі «
=Пстр(A1;1;Найти(" "; A1;1))&Пстр(D1;1;1)&"."&Пстр(G1;1;1)&"."» видаляємо текст «D1» і вставляємо вміст із буфера обміну. Отримуємо: «=Пстр(A1;1;Найти(" ";A1;1)) & Пстр (Пстр(A1;Найти(" ";A1;1)+1;Длстр(A1));1;1)&"."& Пстр(G1;1;1)&"."»;25) переходимо до комірки «
G1». Натискуємо на «F2»;26) виділяємо текст «
Пстр(D1;F1+ 1;Длстр(D1))»;27) за допомогою «
Ctrl+C» копіюємо текст до буфера обміну;28) натискуємо на «
Esc»;29) переходимо до «
J1». Натискуємо на «F2»;30) у формулі «
=Пстр(A1;1;Найти (" ";A1;1)) & Пстр(Пстр(A1;Найти (" ";A1;1)+1; Длстр(A1));1;1) & "."& Пстр(G1;1;1)&"."» видаляємо текст «G1» і вставляємо замість нього вміст із буфера обміну. Отримуємо формулу: «=Пстр(A1;1;Найти(" ";A1;1)) & Пстр (Пстр(A1;Найти(" ";A1;1)+1;Длстр(A1));1;1) & "." & Пстр(Пстр(D1;F1+1; Длстр(D1));1;1)&"."». У формулі знову з’явилися посилання на «D1» і «F1». Замінимо їх; 31) стаємо на «D1», натискуємо «F2»;32) копіюємо до буфера текст «
Пстр(A1; Найти(" ";A1;1)+1;Длстр(A1))»;33) у формулі «
=Пстр(A1;1;Найти (" ";A1;1)) & Пстр(Пстр(A1;Найти (" ";A1;1)+1; Длстр(A1));1;1) & "." & Пстр (Пстр(D1;F1+1;Длстр(D1));1;1)&"."» послідовно виділяємо два посилання на «D1» (показано напівжирним) і кожного разу замінюємо їх умістом із буфера обміну. У результаті отримаємо формулу: «=Пстр(A1;1;Найти (" ";A1;1))&Пстр(Пстр(A1;Найти(" "; A1;1)+1;Длстр(A1));1;1)&"."&Пстр(Пстр (Пстр(A1;Найти(" ";A1;1)+1;Длстр(A1));F1+1; Длстр(Пстр(A1;Найти(" ";A1;1)+1;Длстр(A1))));1;1)&"."»;34) стаємо на комірку «
F1», натискуємо на «F2»;35) копіюємо до буфера обміну текст «
Найти(" ";D1;1)»;36) переходимо до «
J1». Натискуємо на «F2»;37) у формулі «
=Пстр (A1;1;Найти (" ";A1;1)) & Пстр(Пстр (A1;Найти (" ";A1;1)+1;Длстр(A1));1;1) & "." & Пстр(Пстр(Пстр(A1;Найти(" ";A1;1)+1; Длстр(A1)); F1+1; Длстр(Пстр(A1;Найти (" ";A1;1)+1;Длстр(A1))));1;1)&"."» замість посилання «F1» вставляємо вміст із буфера обміну. Отримуємо формулу: «=Пстр(A1;1;Найти (" ";A1;1))&Пстр(Пстр(A1;Найти(" "; A1;1)+1;Длстр(A1));1;1)&"."&Пстр(Пстр(Пстр(A1;Найти(" ";A1;1)+ 1;Длстр(A1));Найти(" ";D1;1)+1;Длстр (Пстр(A1;Найти(" ";A1;1)+1;Длстр(A1))));1;1)&"."». Нам залишилося прибрати єдине посилання на «D1»;38) стаємо на комірку «
D1», натискуємо на «F2»;39) копіюємо до буфера обміну текст «
Пстр (A1;Найти(" ";A1;1)+1;Длстр(A1))»;40) переходимо до «
J1». Натискуємо на «F2»;41) у формулі «
=Пстр(A1;1;Найти(" ";A1; 1))&Пстр(Пстр(A1;Найти(" ";A1;1)+1; Длстр (A1));1;1)&"."&Пстр(Пстр(Пстр(A1;Найти (" ";A1;1)+1;Длстр(A1));Найти(" ";D1;1)+1; Длстр(Пстр(A1;Найти(" ";A1;1)+1; Длстр(A1))));1;1)&"."» замінюємо посилання «D1» на вміст буфера обміну.Остаточний варіант формули має такий вигляд: «
=Пстр(A1;1;Найти(" ";A1;1)) & Пстр (Пстр(A1;Найти(" ";A1;1)+1;Длстр (A1));1;1)&"."&Пстр(Пстр(Пстр(A1; Найти(" ";A1;1)+1; Длстр(A1));Найти (" ";Пстр(A1;Найти(" ";A1;1)+1;Длстр (A1));1)+1;Длстр(Пстр(A1;Найти(" "; A1; 1)+1;Длстр(A1))));1;1)&"."».Порада
Вигляд формули може здатися вам страхітливим. Мушу запевнити, що при описаній техніці (робота з Майстром функцій та буфером обміну) на створення такої формули витрачається не більше п’яти хвилин. А для тих, хто не дуже впевнено працює з формулами в Excel, процес її створення буде чудовою вправою для вдосконалення своїх навичок.
Покращуємо формулу
Процес удосконалення нескінченний. І це — беззаперечний факт. Тому формулу в принципі можна залишити як є. Працюватиме вона нормально і здебільшого успішно впорається з поставленим завданням. Але певні недоліки все-таки є. Наприклад, якщо повне ім’я починається з пробілу, результат роботи формули буде неправильним. Якщо між прізвищем, ім’ям чи по батькові знаходяться не один, а кілька пробілів, формула теж помилиться (адже функцією «
Найти()» ми завжди шукаємо один пробіл!). Також виникне проблема, якщо обробити формулою порожню комірку. Усі ці питання вирішуються елементарно. Головне — визначити алгоритм наших дій, а саме:— щоб формула впевнено впоралася із зайвими пробілами, потрібно скрізь замість посилання «
A1» поставити вираз «Сжпробелы(A1)». Ця функція прибере все зайве і залишить тільки одиночні пробіли між словами;— у ситуації, коли замість повного імені стоїть порожній рядок, виручить функція «
Если()». Виглядатиме вона так: «Если(A1="";"";формула)», де замість «формула» ми поставимо отриманий нами вираз. Виконуємо такі дії:1) спочатку перетворимо файл. Зараз наша формула знаходиться в комірці «
J1». Її складові записано у проміжних комірках. Оскільки в «J1» залишилися посилання тільки на «A1», усе зайве можна видалити. Тому виділяємо колонки з «B» по «I» та видаляємо їх. У нашій таблиці залишилися тільки дві комірки, як показано на рис. 2. В комірці «A1» записано повне ім’я. У «B1» знаходиться формула. Приступимо до її коригування;
2) виділяємо комірку з формулою. Можна виділити колонку «
B»;3) натискуємо на «
Ctrl+H» (меню «Правка → Найти и заменить»;4) у полі «
Найти:» записуємо «A1», до поля «Заменить на:» вводимо «Сжпробелы(A1)»;5) натискуємо на «
Заменить все». Формулу відкориговано. Ось її новий вигляд: «=Пстр(Сжпробелы(A1);1;Найти(" "; Сжпробелы(A1);1))&Пстр(Пстр(Сжпробелы (A1);Найти(" ";Сжпробелы(A1);1)+1; Длср(Сжпробелы(A1)));1;1)&"."&Пстр (Пстр(Пстр(Сжпробелы(A1);Найти(" "; Сжпробелы(A1);1)+1;Длстр(Сжпробелы(A1)));Найти(" ";Пстр(Сжпробелы(A1);Найти(" "; Сжпробелы(A1);1)+1;Длстр(Сжпробелы (A1)));1)+1;Длстр(Пстр(Сжпробелы(A1); Найти(" ";Сжпробелы(A1);1)+1;Длстр (Сжпробелы(A1)))));1;1)&"."»;Важливо!
Запам’ятайте цей прийом. Ми ним ще скористаємося.6) у комірці «
С1» Майстром функцій будуємо функцію «Если(A1="";"";1)». У цій формулі «1» — фіктивний параметр. Він потрібен лише для того, щоб Майстер функцій закрив формулу;7) у формулі «
Если(A1="";"";1)» замість «A1» пишемо «Сжпробелы(A1)»;8) стаємо в комірку «
B1». Натискуємо «F2»;9) копіюємо текст функції без символу «
=» до буфера обміну;10) переходимо на комірку «
С1». Натискуємо «F2». У тексті формули стираємо «1» і вставляємо вміст із буфера обміну. Отримаємо такий результат: « =Если(Сжпробелы(A1)="";"";Пстр (Сжпробелы(A1);1;Найти(" "; Сжпробелы(A1);1))&Пстр(Пстр(Сжпробелы (A1);Найти(" ";Сжпробелы(A1);1)+1; Длср(Сжпробелы(A1)));1;1)&"."&Пстр (Пстр(Пстр(Сжпробелы(A1);Найти(" "; Сжпробелы(A1);1)+1;Длстр(Сжпробелы (A1)));Найти(" ";Пстр(Сжпробелы(A1); Найти (" ";Сжпробелы(A1);1)+1;Длстр (Сжпробелы(A1)));1)+1;Длстр(Пстр (Сжпробелы(A1);Найти(" ";Сжпробелы(A1); 1)+1;Длстр(Сжпробелы(A1)))));1;1)&".") »;11) остаточний варіант формули знаходиться в комірці «
С1». Видаляємо колонку «B».Кілька конфліктних ситуацій ми обробили. Вважаю, далі покращувати формулу немає сенсу, результат буде незначний. Краще зупинимося на такому питанні, як робота з формулою.
Як користуватися формулою
Отже, у нас є таблиця, як показано на рис. 2. Результуючу формулу створено в комірці «
B1». Усі її частини посилаються на комірку «A1». Припустимо, я хочу застосувати цю формулу для заповнення бази, як показано на рис. 3. Єдина проблема може бути в тому, що доведеться коригувати адресу комірки, яка посилається на вихідні дані. У принципі, це можна зробити вручну, але посилань у формулі багато. Нагадаю ще раз, формула — це звичайний текст. А це означає, коригувати його можна будь-якими доступними засобами, у тому числі й засобом пошуку та заміни. І це — найшвидший спосіб вирішення проблеми. Виконуємо такі дії:
1) відкриваємо файл із прикладом. Клавішею «
F2» виділяємо текст формули та копіюємо його до буфера обміну;2) переходимо до документа з базою даних. Вставляємо до потрібної комірки вміст буфера. Для прикладу на рис. 3 буде «
B2». Не дивуйтеся, що замість результату в комірці з’явилося значення «#ЗНАЧ!». Це — наслідок неправильної адресації. Зараз ми це виправимо;3) виділяємо комірку «
B2» або всю колонку «B»;4) натискуємо на «
Ctrl+H» (меню «Правка → Найти и заменить»;5) у полі «
Найти:» записуємо «A1», у полі «Заменить на:» уводимо адресу комірки з повним ім’ям працівника. У прикладі це «(A2)»;6) натискуємо на «
Заменить все». Готово.Отже, ви можете зберігати книгу з формулою в папці зі зразками файлів Excel. У будь-який момент відкриваєте файл. Копіюєте формулу. Вставляєте в інший документ. Засобами заміни виправляєте адресацію. Причому буквально одним рухом миші! І залишається тільки скопіювати формулу на всю висоту бази даних. Ну а ті, хто не бажає створювати формулу самостійно, можуть зНайти її на форумі редакції:
http://bk.factor.ua/forum/viewtopic.php?p=2503#2503 або скачати прикріплений до статті файл на сайті www.bk.factor.ua.
Чекаю ваших запитань, зауважень та пропозицій на
bk@id.factor.ua, nictomkar@rambler.ru або на форумі редакції www.bk.factor.ua/forum . Успішної роботи!
