Excel фамилия инициали. Комбиниране на име и фамилия. Намерете име в пълното име

10.04.2024

Често, когато изготвяме договор, фактура, търговско предложение или друг документ, ние работим с фамилията, собственото име и бащиното име на клиента. Понякога е необходимо да се отдели пълното име и да се получи само име, за да се обърнете към клиента в текста на документа; понякога трябва да направите колона с подписалото лице, като оставите фамилията и инициалите.

Със сигурност можете да напишете формулата, който ще извлече необходимите данни от пълното име, но дори опитен човек ще се нуждае от 5-7 минути, за да направи това. Ами ако не знаете местоположението на фамилното име в текста предварително? Много по-лесно и удобноизползвайте готови функции от VBA-Excel добавки.

Намерете фамилното име в пълното име

След инсталиране на добавката ще бъде налична нова функция = ФАМИЛНО ИМЕ (пълно име), Където

Пълно име- текст или връзка към клетка, която съдържа пълното име

Целта на тази функция е да извлече само фамилното име от клетка. Освен това редът не е важен за нея. Фамилията може да се появи в началото, в края или в средата на текста.

Намерете име в пълното име

Подобна функция за име =ИМЕ(пълно име). Той извлича име от текст с пълно име, независимо къде се намира.

Намерете бащино име в пълното име

Най-новата функция в тази серия е =ПАТЕРИНАЛ (ПЪЛНО ИМЕ)за извличане на бащиното име.

Примери за работа с пълно име в Excel

Показване на фамилия и инициали

Една от често срещаните задачи при изготвянето на документи е преобразуването на текст от формата Фамилия Име Отчество в текста Фамилия I. O. Можете да го решите, като използвате следната формула:

ФАМИЛНО ИМЕ(A1)&" "&НАЛЯВО(ИМЕ(A1))&". "&НАЛЯВО(ПАТЕРИМЕ(A1))&"."

Донякъде е дълъг, но повярвайте ми, щеше да е много по-кратък и по-ясен, ако не използвахме функции от добавката.

Друг плюс е, че местоположението на фамилията в текста не е важно за нас, функцията ще определи всичко автоматично.

Преди това разгледахме възможността използвайки примера за разделяне на пълно име на съставните му части. За да направим това, използвахме инструмента Excel Text by Columns.

Несъмнено това е много важен и полезен инструмент в Excel, който може значително да опрости много задачи. Но този метод има малък недостатък. Ако например непрекъснато ви се изпращат данни в определена форма и постоянно трябва да ги разделяте, тогава това отнема известно време, освен това, ако данните са ви изпратени отново, тогава ще трябва да направите всички операции отново.

Ако разгледаме примера за разделяне на пълно име, тогава ще бъде възможно да разделим текста с помощта на текстови формули на Excel, използвайки функцията и, която обсъдихме в предишни статии. В този случай просто трябва да вмъкнете данни в конкретна колона и формулите автоматично ще разделят текста според нуждите ви. Нека започнем да разглеждаме този пример.

Имаме колона със списък с пълни имена, нашата задача е да поставим фамилията, собственото име и бащиното име в отделни колони.

Нека се опитаме да опишем плана за действие много подробно и да разделим решението на проблема на няколко етапа.

Първо ще добавим помощни колони за междинни изчисления, за да ви е по-ясно, а накрая ще обединим всички формули в една.

И така, нека добавим колони позиция 1-ви и 2-ри интервали. Използвайки функцията FIND, както вече обсъдихме в предишната статия, ще намерим позицията на първото пространство. За да направите това, в клетка "H2" записваме формулата

FIND(" ";A2;1)

Сега трябва да намерим поредния номер на второто пространство. Формулата ще бъде същата, но с малка разлика. Ако напишем същата формула, функцията ще ни намери първия интервал, но имаме нужда от втория интервал. Това означава, че е необходимо да се промени третият аргумент във функцията FIND - началната позиция - тоест позицията, от която функцията ще търси търсения текст. Виждаме, че вторият интервал във всеки случай е след първия интервал и вече намерихме позицията на първия интервал, което означава, че като добавим 1 към позицията на първия интервал, ще кажем на функцията FIND да търси интервала започвайки от първата буква след първия интервал. Функцията ще изглежда така:

Да започнем да разделяме първата част на текста – Фамилиите

За да направим това, ще използваме функцията, нека ви напомня за синтаксиса на тази функция:

PSTR(текст; начална_позиция; брой_знаци), където

text е пълно име, в нашия пример това е клетка A2;
начална_позиция- в нашия случай е 1, т.е. започвайки от първата буква;
number_characters - виждаме, че фамилното име се състои от всички знаци, като се започне от първата буква и се стигне до 1-ви интервал. И вече знаем позицията на първото пространство. Това ще бъде броят знаци минус 1 знак от самия интервал.

Формулата ще изглежда така:

PSTR(A2 ;1 ;H2-1 )

Нека започнем да разделяме втората част на текста - Име

Отново използваме функцията =PSTR(текст; начална_позиция; брой_знаци), където

text е същото пълно име text, в нашия пример това е клетка A2;
начална_позиция- в нашия случай Името започва с първата буква след първия интервал, знаейки позицията на този интервал получаваме H2+1;
number_characters - броят знаци, т.е. броят на буквите в името. Виждаме, че името ни е между две пространства, чиито позиции знаем. Ако извадим позицията на първия интервал от позицията на втория интервал, получаваме разликата, която ще бъде равна на броя знаци в името, тоест I2-H2

Получаваме крайната формула:

PSTR(A2 ;H2+1 ;I2-H2 )

Нека започнем да разделяме третата част на текста - Патроним

И отново функцията =PSTR(текст; начална_позиция; брой_знаци), където

текстът е същият текст с пълно име, в нашия пример това е клетка A2;
начална_позиция- Нашето бащино име се намира след 2-ро място, което означава, че началната позиция ще бъде равна на позицията на второто пространство плюс един знак или I2+1;
number_characters - в нашия случай няма знаци след бащиното име, така че можем просто да вземем произволно число, основното е, че е по-голямо от възможния брой знаци в бащиното име, взех число с голям марж - 50

Получаваме функцията

PSTR(A2 ;I2+1 ;50 )

След това изберете всичките три клетки и плъзнете формулите надолу и получете резултата, от който се нуждаем. Можете да завършите тук или можете да напишете междинни изчисления на позицията на интервалите в самите формули за разделяне на текст. Много лесно се прави. Виждаме, че изчислението за първото пространство е в клетка H2 - FIND(" ";A2;1) , а изчислението за второто пространство е в клетка I2 - FIND(" ";A2;H2 +1) . Виждаме, че във формулата на клетка I2 има H2, променяме го на самата формула и получаваме вложена формула в клетка I2

Разглеждаме първата формула за избор на фамилното име и виждаме къде тук се появяват H2 или I2 и ги променяме на формулите в тези клетки, подобно на собственото име и фамилията

Фамилия =PSTR(A2,1,H2 -1) получаваме =PSTR(A2,1,FIND(" ",A2,1) -1)
Име =PSTR(A2;H2 +1;I2 -H2) получаваме =PSTR(A2;FIND(" ";A2;1) +1;
FIND(" ";A2;FIND(" ";A2;1)+1)-FIND(" ";A2;1) )
Бащино име =PSTR(A2;I2 +1;50) получаваме =PSTR(A2; FIND(" ";A2;FIND(" ";A2;1)+1)+1;50)

Сега изчисленията на междинни позиции с интервал могат безопасно да бъдат премахнати. Това е една от техниките, когато за простота първо се търсят междинни данни и след това функцията се влага една в друга. Съгласете се, ако напишете толкова голяма формула наведнъж, лесно е да се объркате и да направите грешки.

Надяваме се, че този пример ясно ви показа колко полезни са текстовите функции на Excel за работа с текст и как ви позволяват да разделяте текст автоматично, като използвате формули от същия тип данни. Ако сте харесали статията, ще ви бъдем благодарни, че кликнете върху +1 и ми харесва. Абонирайте се и се присъединете към нашата група VKontakte.

Пример 2: Как да разделя текст на колони в Excel с помощта на формула

Нека разгледаме втория пример, който също много често се среща в практиката. Примерът е подобен на предишния, но има много повече данни, които трябва да бъдат разделени. В този пример ще покажа техника, която ще ви позволи бързо да разрешите проблема и да не се объркате.

Да кажем, че имаме списък с числа, разделени със запетаи, трябва да разделим текста, така че всяко число да е в отделна клетка (вместо запетаи, това могат да бъдат всякакви други знаци, включително интервали). Тоест, трябва да разделим текста на думи.

Нека ви напомним, че този проблем може да бъде решен ръчно (без формули), като се използва методът, който вече разгледахме. В нашия случай трябва да направим това с помощта на формули.

Първо трябва да намерим общо разделение, по което ще разделим текста. В нашия случай това е запетая, но например в първата задача разделихме пълното име и разделителят беше интервал. Нашият втори пример е по-универсален (по-удобен, когато има голямо количество данни), така че например можем удобно да разделим не само пълното име на отделни клетки, но и цялото изречение - всяка дума в отделна клетка. Всъщност този въпрос се появи в коментарите, така че беше решено да се допълни тази статия.

За удобство ще посочим този разделител в съседната колона, така че да не се налага да го записваме във формулата, а просто да препращаме към клетката. Това също ще ни позволи да използваме файла за решаване на други проблеми, като просто променим разделителя в клетките.

Сега основната същност на техниката.

Етап 1. В спомагателната колона намираме позицията на първия разделител с помощта на функцията FIND. Няма да описвам функцията подробно, тъй като вече я разгледахме по-рано. Нека напишем формулата в D1 и да я разширим до всички редове

FIND(B1;A1;1 )

Тоест търсим запетая в текста, започвайки от позиция 1

FIND($B1 ;$A1;D1+1 )

Първо, нека коригираме колоната с желаната стойност и текст, така че при плъзгане на формулата надясно препратките към клетките да не се местят. За да направите това, трябва да напишете долар преди колоната B и A - или ръчно, или изберете A1 и B1, натиснете клавиша F4 три пъти, след което връзките ще станат абсолютни, а не относителни.

Второ: ще изчислим третия аргумент - началото на позицията като позицията на предишния разделител (намерихме го по-горе) плюс 1, т.е. D1+1 тъй като знаем, че вторият разделител е точно след първия и не е нужно да го вземаме предвид.

Нека запишем формулата и я плъзнете надолу.

Стъпка 3.Намираме позициите на всички останали разделители. За да направите това, разширете формулата за намиране на втория разделител (стъпка 2) вдясно с броя на клетките, равен на общия брой отделно разделени стойности с малък марж. Нека вземем всички разделителни позиции. Когато грешката #Value означава, че стойностите са изчерпани и формулата вече не намира разделители. Получаваме следното

Стъпка 4.Отделяме първото число от текста с помощта на функцията PSTR.

PSTR(A1;1;D1-1)

Нашата начална позиция е 1, изчисляваме броя на знаците като позицията на първия разделител минус 1: D1-1 плъзнете формулата надолу

Стъпка 5. Намираме и втората дума с помощта на функцията PSTR в клетка P1

PSTR($A1;D1+1;E1-D1-1)

Началната позиция на второто число започва след първата запетая. Имаме позицията на първата запетая в клетка D1, добавяме едно и получаваме началната позиция на нашето второ число.

Броят на знаците е разликата между позицията на третия разделител и втория и минус един знак, тоест E1-D1-1

Нека поправим колона A от изходния текст, така че да не се мести при плъзгане на формулата надясно.

Стъпка 6. Нека разтегнем формулата, получена в стъпка 5, надясно и надолу и да получим текст в отделни клетки.

Стъпка 7По принцип нашият проблем вече е решен, но за красота в същата клетка P1 ще напишем формула, която улавя грешка, замествайки я с празна стойност. Можете също така да групирате и свивате спомагателни колони, така че да не ви пречат. Получаваме окончателното решение на проблема

IFERROR(PSTR($A1,D1+1,E1-D1-1); "")

Забележка. Направихме първата позиция на разделителя и първото разделяне на думата различно от другите и поради това успяхме да разширим формулата само от вторите стойности. Докато пишех проблема, забелязах, че проблемът може да бъде опростен. За да направите това, в колона C беше необходимо да въведете 0 за стойността на първия разделител. След това намираме стойността на първия разделител

FIND($B1,$A1,C1+1)

и първият текст като

PSTR($A1;C1+1;D1-C1-1)

След това можете веднага да разширите формулата до останалите стойности. Оставям тази опция като пример за изтегляне. По принцип файлът може да се използва като шаблон. Вмъкнете данни в колона „A“, посочете разделител в колона „B“, разтегнете формулите до необходимия брой клетки и получете резултата.

внимание! В коментарите се отбелязва, че тъй като нямаме разделител в края на текста, не броим броя на знаците от последния разделител до края на реда, така че последният разделителен текст липсва. За да разрешите проблема, можете или в първата стъпка да добавите помощна колона до изходния текст, където можете да комбинирате този текст с разделител. По този начин ще имаме разделител в края на текста, което означава, че нашите формули ще изчислят неговата позиция и всичко ще работи.

Или второто решение е в стъпка 3, когато съставяме формула за изчисляване на позициите на разделителите и я допълваме. Проверете, ако има грешка, тогава посочете очевидно голямо число, например 1000.

IFERROR(FIND($B1,$A1,C1+1),1000)

Функцията получава като параметър текстов низ във формата "Фамилия първо име патроним" и отрязва собственото име и бащиното име, оставяйки само инициалите - във формата "Фамилия I. O."

Тази функция може да се използвакато UDF (дефинирани от потребителя функции) в клетки на работен лист на Excel.
(вижте примера в прикачения файл)

Описание на функциите на функцията - във форума: programmersforum.ru/showpost.php?p=757147&postcount=6

Няма точен формален алгоритъм за разделяне на пълно име на части.
Невъзможно е формално да се отдели фамилия от собствено име, без да се познават генеалогията и езикът на носителя на езика. Можете да използвате само различни евристики.

Освен това различните власти (по различно време) са имали различни виждания за това как трябва да изглеждат инициалите в даден случай.
Документите на Федералната миграционна служба са едно, регистраторите на Централната банка са друго, а подготовката на търговски документи е друго.
Така някои услуги изискват 2-3 сложни кратки имена на Югоизточна Азия да не се съкращават. Но някои не го правят.

Да, с въпроса къде трябва да са инициалите отдясно или отляво - няма еднозначност. Не, разбира се, има правила на руския език... Но преобладават правилата за бизнес оборот на една или друга структура (или просто както иска левият заден крак на служителя, приемащ документи)...

Мнозина може да имат въпрос: откъде идва цялата тази зоологическа градина и защо е необходима всичко това? По правило всички тези Дон Педро са чуждестранни граждани, които получават руски паспорт въз основа на документи, издадени преди това извън юрисдикцията на Руската федерация. Правните ситуации са различни. И доста често в новия руски паспорт се записва транслитерация на кирилица от родния език.
Руски паспорти от -ogly и -kyzy вече се издават в Татарстан. Срещнах клиент, чието име според паспорта му от 2007 г. беше Мустафа Олег огли...

Опция Compare Text Public Function Initials(s As String, Optional ToLeft As Boolean = False) Dim sv As Variant, sФ As String, sИ As String, sО As String, i As Long, k As Long Application.Volatile True If InStr(s) , ".") > 0 Или Len(Trim$(s)) = 0 Тогава Initials = s „Изрично посочени инициали или празен низИзход от функцията End If „Нормализиране на входния низ s = Replace(Application.Trim(s), Chr(30), "-" ) s = Replace(Replace(s, " -" , "-") , "- " , "-") s = Replace(Replace (с, "" " , """ ), " "" , """ ) "О" Хенри Александър; O" Хенри Александър; N" Homo; Д „Тревил sv = Split(s) sИ = vbNullString: sО = vbNullString: sФ = vbNullString i = UBound (sv) Ако i< 1 Then Инициалы = s: Exit Function Select Case sv(i) Case "оглы" , "кызы" , "заде" "бей, бек, заде, зул, ибн, къзи, огли, ол, паша, ул, хан, шах, ед, ел i = i - 1 sО = UCase(Left$(sv(i), 1)) & "." i = i - 1 Регистър "pasha", "khan", "shah", "sheikh" i = i - 1 Case Else Изберете Case Right$(sv(i), 3) Case "vich", "vna" Ако i >= 2 Тогава „Стандартно окончание за руски бащини имена sО = СropWord(sv(i)) Друго „Име като Босан Славич sИ = СropWord(sv(i)): sФ = sv(0) End If i = i - 1 Case Else k = InStr(sv(i), "-") If k > 0 Тогава Изберете Case Mid$(sv( i), k + 1) Падеж „ogly“, „kyzy“, „zade“, „ugli“, „uuly“, „ool“ „Вариант на насаб „-огли” и „-заде” тип Махмуд-огли sО = UCase(Left$(sv(i), 1)) & "." i = i - 1 If i = 0 Then sИ = sО sО = vbNullString End If End Select ElseIf i > 2 Then Select Case sv(i - 1) Case "ibn", "bin" , "bin" sО = UCase(Left $(sv(i), 1)) & "." „Усертал Алишер бин Сюлейман i = i - 2 End Select Else " Ben Edward sИ = UCase(Left$(sv(i), 1)) If Len(sv(i)) > 1 Then sИ = sИ & "." i = i - 1 End If End Select End Select Select Case sv(0) Case "de", "del", "dos", "sept", "van", "fon", "tsu" If i >= 2 Тогава sФ = sv(0 ) & " " & StrConv(sv(1), vbProperCase) sИ = СropWord(sv(2)) Else "De Nikolai Ако Len(sИ) > 0 Тогава sФ = sv(0) & " " & StrConv(sv(1) ), vbProperCase) Else sФ = StrConv(sv(0), vbProperCase): sИ = СropWord(sv(1)) End If End If Case Else If Len(sФ) = 0 Then „Все още не сме решили фамилията. sФ = StrConv(sv(0), vbProperCase) If Len(sИ) = 0 Then sИ = СropWord(sv(1)) End If End Select If ToLeft Then Initials = sИ & sО & " " & sФ Else Initials = sФ & " " & sИ & sО Крайна функция Публична функция СropWord(s As Variant ) As String If Len(s) = 1 Then СropWord = s Else ss$ = UCase(Left$(s, 1)) & "." : k = InStr(s, "-") Ако k > 0 Тогава ss$ = ss$ & "-" & Mid$(s, k + 1, 1) & "." СropWord = ss$ End If End функция

Привързаността	Размер

Списък в Excelможе да се коригира с формули - заместване на собствено и бащино име с инициали, комбиниране на думи от клетки в изречение, вмъкване на думи в списък на Excel.
Имаме таблица, в която фамилията, собственото име и бащиното име са написани в различни клетки. Трябва да ги поставим в една клетка. Ръчното пренаписване на списъка отнема много време. Но в таблицата на Excel има специална функция.Има две възможности.
Първи вариант.
Имаме този списък.
Трябва да напишем пълното си име в клетка D1 в едно изречение.Записваме формулата в тази клетка (D1). Кликнете върху клетката (направете я активна).
Отидете в раздела „Формули“ в секцията „Библиотека с функции“, изберете „Текст“ и изберете функцията „CONCATENATE“.В прозореца, който се показва, посочваме адресите на клетките, които трябва да комбинираме в едно изречение. Оказа се така.

Пълното име се изписва без интервали. За да се коригира това, формулата трябва да се подобри.Между адресите на клетките след точка и запетая пишете" " . Резултатът е следната формула.
=CONCATENATE(A1;" ";B1;" ";C1)
Оказа се така.
Сега копирайте формулата надолу в колоната.
Втори вариант.
Вместо функцията CONCATENATE, можете просто да натиснете бутона амперсанд (&).Формулата ще изглежда така.
=A2&B2&C1
Резултатът е същият като при първия вариант. Ако няма интервали между думите, вмъкнете интервал (" ").
Формулата ще бъде такава.=A2&" "&B2&" "&C2
Можете да комбинирате не само думи, но и числа. Мога направете изречение от клетъчни данни в Excel.
Можете да задавате формули в необходимите клетки на формата.Например, имаме списък с клиенти с адреси.

Трябва да направим предложение. Записваме формулата в клетката.
=A2&" "&B2&" "&C2&" "&"живее в"&" "&"g."&" "&D2&" "&"улица."&" "&E2&" "&"d."&" "&F2& " ."
Това беше предложението.

Ние използваме този принцип, за да съставим всякакви предложения.
Ако текстът в клетките вече е написан, но имаме нужда вмъкнете допълнителни думипреди съществуващите, тогава това може да се направи с помощта на формулата.Имаме този списък.
Трябва да вмъкнем думата „Наемател“ преди фамилните имена.В клетката на новата колона записваме формулата.
= "Наемател"&" "&A8
Копирайте тази формула надолу в колоната. Резултатът е списък като този.
Първата колона може да бъде скрита или стойността на нова колона без формули може да бъде копирана, а първата колона и втората с формули могат да бъдат изтрити.
Друг начин за добавяне на текст, числа, символи към текст в клетка вижте статията " Добавете текст към текстови клетки на Excel ".
С помощта на формули можете да конвертирате списък, в който собственото, бащиното и фамилното име са написани изцяло списък с фамилия и инициали. Например клетката казва:
В следващата колона записваме следната формула.
=CONCATENATE(LEFT(SPACE(A1),FIND(" ",SPACE(A1),1)),PSTR(SPACE(A1),FIND(" ",SPACE(A1),1)+1,1);" ";PSTR(ИНТЕРВАЛ(A1);НАМИРАНЕ(" ";ИНТЕРВАЛ(A1); НАМИРАНЕ(" ";ИНТЕРВАЛ(A1);1)+1)+1;1);."")
Се случи.
Ако има допълнителни интервали между думите, можете да ги премахнете. Прочетете повече за това в статията " Как да премахнете допълнителни интервали в Excel". Използвайки същите методи, можете да премахнете интервалите между числата във формула, тъй като допълнителните интервали могат да доведат до грешка при изчисляване или формулата няма да се брои.
Можете да местите данни в ред от последните клетки към първите, обърнете линията. Например в клетките пише: в първата клетка е Иванов, във втората - Мария. В първата клетка трябва да запишем Мария, а във втората Иванова.Как да направите това бързо в голяма таблица, вижте статията "".