Inițialele numelui de familie Excel. Combinând numele și prenumele. Găsiți numele în numele complet

10.04.2024

Adesea, la întocmirea unui acord, factură, propunere comercială sau alt document, lucrăm cu numele, prenumele și patronimul clientului. Uneori este necesar să separați numele complet și să obțineți doar prenumele pentru a vă adresa clientului în textul documentului, uneori trebuie să faceți o coloană cu semnatarul, lăsând numele de familie și inițialele;

Cu siguranță puteți scrie formula, care va extrage datele necesare din numele complet, dar chiar și o persoană cu experiență va avea nevoie de 5-7 minute pentru a face acest lucru. Ce se întâmplă dacă nu știi în avans locația numelui de familie în text? Mult mai usor si mai convenabil utilizați funcții gata făcute din Suplimente VBA-Excel.

Găsiți numele de familie în numele complet

După instalarea suplimentului, va fi disponibilă o nouă caracteristică = NUME (nume complet), Unde

Numele complet- text sau link către o celulă care conține numele complet

Scopul acestei funcții este de a extrage doar numele de familie dintr-o celulă. Mai mult, ordinea nu este importantă pentru ea. Numele de familie poate veni fie la început, la sfârșit sau la mijlocul textului.

Găsiți numele în numele complet

Funcție similară pentru nume =NUME(nume complet). Extrage un nume dintr-un text cu un nume complet, indiferent unde se află.

Găsiți al doilea nume în numele complet

Cea mai recentă caracteristică din această serie este =PATERNIC (NUME COMPLET) pentru a extrage al doilea nume.

Exemple de lucru cu numele complet în Excel

Afișează numele și inițialele

Una dintre sarcinile obișnuite la întocmirea documentelor este conversia textului din formularul Nume Prenume Patronimic în textul Nume I. O. O puteți rezolva folosind următoarea formulă:

NUMELE(A1)&" "&STÂNGA(NUME(A1))&". "&STÂNGA(NUMELE(A1))&"."

Este oarecum lung, dar credeți-mă, ar fi mult mai scurt și mai clar dacă nu am folosi funcții din add-in.

Un alt plus este că locația numelui de familie în text nu este importantă pentru noi, funcția va determina totul automat.

Am luat în considerare anterior posibilitatea folosind exemplul împărțirii unui nume complet în părțile sale componente. Pentru a face acest lucru, am folosit instrumentul Excel Text by Columns.

Fără îndoială, acesta este un instrument foarte important și util în Excel, care poate simplifica foarte mult multe sarcini. Dar această metodă are un mic dezavantaj. Dacă, de exemplu, vi se trimit în mod constant date într-o anumită formă și trebuie în mod constant să le împărțiți, atunci acest lucru durează un anumit timp, în plus, dacă datele v-au fost trimise din nou, atunci va trebui să faceți toate operațiuni din nou.

Dacă ne uităm la exemplul de împărțire a unui nume complet, atunci va fi posibil să divizăm textul folosind formule text Excel, folosind funcția și, despre care am discutat în articolele anterioare. În acest caz, trebuie doar să inserați date într-o anumită coloană, iar formulele vor împărți automat textul după cum aveți nevoie. Să începem să ne uităm la acest exemplu.

Avem o coloană cu o listă de nume complete, sarcina noastră este să plasăm numele de familie, prenumele și patronimul în coloane separate.

Să încercăm să descriem planul de acțiune în detaliu și să împărțim soluția problemei în mai multe etape.

În primul rând, vom adăuga coloane auxiliare pentru calculele intermediare pentru a vă fi mai clar, iar la sfârșit vom combina toate formulele într-una singură.

Deci, să adăugăm coloanele poziționate spațiile 1 și 2. Folosind funcția FIND, așa cum am discutat deja în articolul anterior, vom găsi poziția primului spațiu. Pentru a face acest lucru, în celula „H2” scriem formula

FIND(" ";A2;1)

Acum trebuie să găsim numărul de ordine al celui de-al doilea spațiu. Formula va fi aceeași, dar cu o ușoară diferență. Dacă scriem aceeași formulă, funcția ne va găsi primul spațiu, dar avem nevoie de al doilea spațiu. Aceasta înseamnă că este necesară schimbarea celui de-al treilea argument din funcția FIND - poziția de pornire - adică poziția din care funcția va căuta textul căutat. Vedem că al doilea spațiu este în orice caz după primul spațiu și am găsit deja poziția primului spațiu, ceea ce înseamnă că adăugând 1 la poziția primului spațiu vom spune funcției FIND să caute spațiul. începând cu prima literă după primul spațiu. Funcția va arăta astfel:

Să începem să împărțim prima parte a textului - Nume

Pentru a face acest lucru, vom folosi funcția, permiteți-mi să vă reamintesc de sintaxa acestei funcții:

PSTR(text; poziție_începută; număr_caractere), unde

textul este un nume complet, în exemplul nostru este celula A2;
poziție_începută- in cazul nostru este 1, adica incepand de la prima litera;
number_characters - vedem că numele de familie este format din toate caracterele, începând de la prima literă și până la primul spațiu. Și știm deja poziția primului spațiu. Acesta va fi numărul de caractere minus 1 caracter al spațiului în sine.

Formula va arăta astfel:

PSTR(A2;1;H2-1)

Să începem să împărțim a doua parte a textului - Nume

Din nou folosim funcția =PSTR(text; poziție_începută; număr_caractere), unde

textul este același text cu numele complet, în exemplul nostru este celula A2;
poziție_începută- in cazul nostru, Numele incepe cu prima litera dupa primul spatiu, cunoscand pozitia acestui spatiu obtinem H2+1;
number_characters - numărul de caractere, adică numărul de litere din nume. Vedem că numele nostru se află între două spații ale căror poziții le cunoaștem. Dacă scădem poziția primului spațiu din poziția celui de-al doilea spațiu, obținem diferența, care va fi egală cu numărul de caractere din nume, adică I2-H2

Obtinem formula finala:

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

Să începem să împărțim a treia parte a textului - Patronimic

Și din nou funcția =PSTR(text; poziție_începută; număr_caractere), unde

textul este același text cu numele complet, în exemplul nostru este celula A2;
poziție_începută- Patronimul nostru este situat după al 2-lea spațiu, ceea ce înseamnă că poziția inițială va fi egală cu poziția celui de-al doilea spațiu plus un semn sau I2+1;
number_characters - în cazul nostru nu există semne după patronimic, deci putem lua pur și simplu orice număr, principalul lucru este că este mai mare decât numărul posibil de caractere în patronimic, am luat un număr cu o marjă mare - 50

Obținem funcția

PSTR(A2;I2+1;50)

Apoi, selectați toate cele trei celule și trageți formulele în jos și obțineți rezultatul de care avem nevoie. Puteți termina aici sau puteți scrie calcule intermediare ale poziției spațiilor în formulele de divizare a textului. Este foarte ușor de făcut. Vedem că calculul pentru primul spațiu este în celula H2 - FIND(" ";A2;1) , iar calculul pentru al doilea spațiu este în celula I2 - FIND(" ";A2;H2 +1) . Vedem că în formula celulei I2 există H2, îl schimbăm în formula însăși și obținem o formulă imbricată în celula I2

Ne uităm la prima formulă pentru selectarea Numelui și vedem unde apare H2 sau I2 aici și le schimbăm în formulele din aceste celule, în mod similar cu Prenume și Nume

Nume =PSTR(A2,1,H2 -1) obținem =PSTR(A2,1,FIND(" ",A2,1) -1)
Nume =PSTR(A2;H2 +1;I2 -H2) se obține =PSTR(A2;FIND(" ";A2;1) +1;
FIND(" ";A2;FIND(" ";A2;1)+1)-FIND(" ";A2;1) )
Patronimic =PSTR(A2;I2 +1;50) obținem =PSTR(A2; FIND(" ";A2;FIND(" ";A2;1)+1)+1;50)

Acum, calculele de poziție intermediară cu un spațiu pot fi eliminate în siguranță. Aceasta este una dintre tehnicile în care, pentru simplitate, sunt căutate mai întâi date intermediare, iar apoi funcția este imbricată una în cealaltă. De acord, dacă scrii o formulă atât de mare deodată, este ușor să te confuzi și să faci greșeli.

Sperăm că acest exemplu v-a arătat clar cât de utile sunt funcțiile de text Excel pentru a lucra cu text și cum vă permit să împărțiți textul automat folosind formule de același tip de date. Dacă ți-a plăcut articolul, îți vom fi recunoscători că dai clic pe +1 și îmi place. Abonați-vă și alăturați-vă grupului nostru VKontakte.

Exemplul 2: Cum să împărțiți textul în coloane în Excel folosind o formulă

Să luăm în considerare cel de-al doilea exemplu, care este și foarte des întâlnit în practică. Exemplul este similar cu cel anterior, dar există mult mai multe date care trebuie împărțite. În acest exemplu, voi arăta o tehnică care vă va permite să rezolvați rapid problema și să nu vă confundați.

Să presupunem că avem o listă de numere separate prin virgule, trebuie să împărțim textul astfel încât fiecare număr să fie într-o celulă separată (în loc de virgule, acestea pot fi orice alte caractere, inclusiv spații). Adică trebuie să despărțim textul în cuvinte.

Să vă reamintim că această problemă poate fi rezolvată manual (fără formule) folosind metoda pe care am considerat-o deja. În cazul nostru, trebuie să facem acest lucru folosind formule.

Mai întâi trebuie să găsim o împărțire comună prin care vom împărți textul. În cazul nostru, aceasta este o virgulă, dar, de exemplu, în prima sarcină am împărțit numele complet, iar separatorul era un spațiu. Al doilea exemplu este mai universal (mai convenabil atunci când există o cantitate mare de date), așa că, de exemplu, am putea împărți în mod convenabil nu numai numele complet în celule separate, ci întreaga propoziție - fiecare cuvânt într-o celulă separată. De fapt, această întrebare a apărut în comentarii, așa că s-a decis să se completeze acest articol.

Pentru comoditate, vom indica acest separator în coloana alăturată, astfel încât să nu fie nevoie să-l scriem în formulă, ci pur și simplu să ne referim la celulă. Acest lucru ne va permite, de asemenea, să folosim fișierul pentru a rezolva alte probleme prin simpla schimbare a separatorului din celule.

Acum esența principală a tehnicii.

Pasul 1. În coloana auxiliară găsim poziția primului separator folosind funcția FIND. Nu voi descrie funcția în detaliu, deoarece am analizat-o deja mai devreme. Să scriem formula în D1 și să o extindem în jos la toate liniile

GĂSĂ(B1;A1;1)

Adică căutăm o virgulă în text, începând de la poziția 1

GĂSĂ($B1 ;$A1;D1+1 )

Mai întâi, să reparăm coloana cu valoarea și textul dorit, astfel încât, atunci când trageți formula spre dreapta, referințele de celule să nu se miște. Pentru a face acest lucru, trebuie să scrieți un dolar înaintea coloanei B și A - fie manual, fie selectați A1 și B1, apăsați tasta F4 de trei ori, după care legăturile vor deveni absolute, nu relative.

În al doilea rând: vom calcula al treilea argument - începutul poziției ca poziție a separatorului anterior (am găsit-o mai sus) plus 1, adică D1+1 întrucât știm că al doilea delimitator este exact după primul delimitator și nu trebuie să luăm în considerare.

Să notăm formula și să o tragem în jos.

Pasul 3. Găsim pozițiile tuturor celorlalți separatori. Pentru a face acest lucru, extindeți formula pentru găsirea celui de-al doilea separator (pasul 2) la dreapta cu numărul de celule egal cu numărul total de valori împărțite separat, cu o marjă mică. Să obținem toate pozițiile separatorului. Unde eroarea #Value înseamnă că valorile s-au epuizat și formula nu mai găsește delimitatori. Obținem următoarele

Pasul 4. Separăm primul număr de text folosind funcția PSTR.

PSTR(A1;1;D1-1)

Poziția noastră de pornire este 1, calculăm numărul de caractere ca poziție a primului separator minus 1: D1-1 trageți formula în jos

Pasul 5. Găsim și al doilea cuvânt folosind funcția PSTR din celula P1

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

Poziția de pornire a celui de-al doilea număr începe după prima virgulă. Avem poziția primei virgule în celula D1, adăugăm una și obținem poziția de pornire a celui de-al doilea număr.

Numărul de caractere este diferența dintre poziția celui de-al treilea separator și al doilea și minus un caracter, adică E1-D1-1

Să reparăm coloana A a textului sursă, astfel încât să nu se miște atunci când trageți formula la dreapta.

Pasul 6. Să întindem formula obținută la pasul 5 la dreapta și în jos și obținem text în celule separate.

Pasul 7În principiu, problema noastră a fost deja rezolvată, dar pentru frumusețe, în aceeași celulă P1 vom scrie o formulă care prinde o eroare, înlocuind-o cu o valoare goală. De asemenea, puteți grupa și restrânge coloanele auxiliare, astfel încât acestea să nu stea în cale. Obținem soluția finală a problemei

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

Notă. Prima poziție a separatorului și prima împărțire a cuvântului am făcut diferit față de celelalte și din această cauză am putut doar extinde formula de la valorile secunde. În timp ce scriam problema, am observat că problema ar putea fi simplificată. Pentru a face acest lucru, în coloana C a fost necesar să introduceți 0 pentru valoarea primului separator. După aceasta găsim valoarea primului separator

Găsește ($B1,$A1,C1+1)

iar primul text ca

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

După aceasta, puteți extinde imediat formula la valorile rămase. Las această opțiune ca exemplu pentru descărcare. În principiu, fișierul poate fi folosit ca șablon. Introduceți datele în coloana „A”, specificați un separator în coloana „B”, întindeți formulele la numărul necesar de celule și obțineți rezultatul.

Atenţie! Comentariile au remarcat că, deoarece nu avem un delimitator la sfârșitul textului, nu numărăm numărul de caractere de la ultimul delimitator până la sfârșitul rândului, deci ultimul text delimitat lipsește. Pentru a rezolva problema, puteți fie, în primul pas, să adăugați o coloană auxiliară lângă textul sursă, unde puteți combina acest text cu un separator. Astfel vom avea un separator la sfârșitul textului, ceea ce înseamnă că formulele noastre își vor calcula poziția și totul va funcționa.

Sau a doua soluție este în pasul 3, când compunem o formulă de calcul a pozițiilor separatoarelor și o completăm. Verificați dacă există o eroare, apoi indicați un număr evident mare, de exemplu 1000.

DACA EROARE(FIND($B1,$A1,C1+1),1000)

Funcția primește ca parametru un șir de text sub forma „Nume prenume patronimic”, și decupează prenumele și patronimul, lăsând doar inițialele - în forma „Nume I. O.”

Această funcție poate fi folosit ca UDF (funcții definite de utilizator) în celulele foii Excel.
(vezi exemplul in atasament)

Descrierea caracteristicilor funcției - pe forum: programmersforum.ru/showpost.php?p=757147&postcount=6

Nu există un algoritm formal exact pentru împărțirea unui nume complet în părți.
Este imposibil să separi oficial un nume de familie de un nume fără a cunoaște genealogia și limba vorbitorului nativ. Puteți utiliza doar o varietate de euristici.

În plus, diferite autorități (în momente diferite) au avut opinii diferite cu privire la modul în care ar trebui să arate inițialele într-un caz dat.
Documentele Serviciului Federal de Migrație sunt una, registratorii Băncii Centrale sunt alta, iar pregătirea documentelor comerciale este alta.
Astfel, unele servicii necesită ca 2-3 nume scurte complexe ale Asiei de Sud-Est să nu fie prescurtate. Dar unii nu.

Da, cu întrebarea unde ar trebui să fie inițialele la dreapta sau la stânga - nu există nicio certitudine. Nu, desigur că există reguli ale limbii ruse... Dar prevalează regulile de rulare a afacerii uneia sau alteia structuri (sau pur și simplu așa cum dorește piciorul din spate stâng al funcționarului care acceptă documente)...

Mulți ar putea avea o întrebare: De unde vine toată această grădină zoologică și de ce este nevoie de toate? De regulă, toți acești Don Pedro sunt cetățeni străini care primesc un pașaport rusesc pe baza documentelor eliberate anterior în afara jurisdicției Federației Ruse. Situațiile legale sunt diferite. Și destul de des o transliterație în chirilică din limba maternă este scrisă în noul pașaport rus.
Pașapoartele rusești de la -ogly și -kyzy sunt acum eliberate în Tatarstan. Am cunoscut un client al cărui nume, conform pașaportului său din 2007, era Mustafa Oleg ogly...

Opțiune Comparare text Funcție publică Inițiale(e ca șir , opțional ToLeft ca boolean = fals ) Dim sv ca variantă , sФ ca șir , sИ ca șir , sО ca șir , i ca lungă , k ca lungă Aplicație.Volatil Adevărat dacă InStr(s , "." ) > 0 Sau Len(Trim$(s)) = 0 Apoi inițiale = s „Inițiale specificate în mod explicit sau șir gol Ieșire Funcție Sfârșit Dacă „Normalizează șirul de intrare s = Înlocuire(Application.Trim(s), Chr(30), "-" ) s = Înlocuire(Înlocuire(s, " -" , "-" ), "- " , "-" ) s = Înlocuire(Înlocuire (s, "" " , """ ), " "" , """ ) „O” Henry Alexander; O" Henry Alexander; N" Homo; D" Treville sv = Split(e) sИ = vbNullString: sО = vbNullString: sФ = vbNullString i = UBound (sv) Dacă i< 1 Then Инициалы = s: Exit Function Select Case sv(i) Case "оглы" , "кызы" , "заде" „bey, bek, zade, zul, ibn, kyzy, ogly, ol, pasha, ul, khan, shah, ed, el i = i - 1 sО = UCase(Left$(sv(i), 1)) & "." i = i - 1 Caz "pasha" , "khan" , "shah" , "sheikh" i = i - 1 Caz Altfel Selectați cazul Dreapta$(sv(i), 3) Cazul "vich" , "vna" Dacă i >= 2 Atunci „Sfârșit standard pentru patronimele rusești sО = СropWord(sv(i)) Else „Nume ca Bosan Slavich sИ = СropWord(sv(i)): sФ = sv(0) End If i = i - 1 Case Else k = InStr(sv(i), "-" ) If k > 0 then Select Case Mid$(sv( i), k + 1) Cazul „ogly”, „kyzy”, „zade”, „ugli”, „uuly”, „ool” „Varianta de tip nasab „-ogly” și „-zade” Mahmud-ogly sО = UCase(Left$(sv(i), 1)) & "." i = i - 1 Dacă i = 0 Atunci sИ = sО sО = vbNullString End If End Selectați ElseIf i > 2 Then Select Case sv(i - 1) Case "ibn" , "bin" , "bin" sО = UCase(Left $(sv(i), 1)) și "." „Usertal Alisher bin Suleiman i = i - 2 Final Selectați Altceva " Ben Edward sИ = UCase(Left$(sv(i), 1)) Dacă Len(sv(i)) > 1 Atunci sИ = sИ & "." i = i - 1 End Dacă End Select End Select Select Case sv(0) Case "de" , "del" , "dos" , "sept" , "van" , "fon" , "tsu" Dacă i >= 2 Atunci sФ = sv(0 ) & " " & StrConv(sv(1), vbProperCase) sИ = СropWord(sv(2)) Else "De Nikolai If Len(sИ) > 0 Then 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 „Nu ne-am hotărât încă asupra numelui de familie. 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О Funcție de sfârșit Funcție publică СropWord(s As Variant ) As String If Len(s) = 1 Then СropWord = s Else ss$ = UCase(Left$(s, 1)) & "." : k = InStr(s, "-" ) Dacă k > 0 Atunci ss$ = ss$ & "-" & Mid$(s, k + 1, 1) & "." СropWord = ss$ End If End Function

Atașamentul	mărimea

Lista în Excel poate fi corectat cu formule - înlocuiți prenumele și al doilea nume cu inițiale, combinați cuvintele din celule într-o propoziție, inserați cuvinte într-o listă Excel.
Avem un tabel în care numele de familie, prenumele și patronimul sunt scrise în celule diferite. Trebuie să le plasăm într-o singură celulă. Rescrierea manuală a listei durează mult. Dar, în tabelul Excel, există o funcție specială.Există două opțiuni.
Prima varianta.
Avem această listă.
Trebuie să ne scriem numele complet în celula D1 într-o singură propoziție.Scriem formula în această celulă (D1). Faceți clic pe celulă (faceți-o activă).
Accesați fila „Formule” din secțiunea „Biblioteca de funcții”, selectați „Text” și selectați funcția „CONCATENARE”.În fereastra care apare, indicăm adresele celulelor pe care trebuie să le combinăm într-o singură propoziție. A ieșit așa.

Numele complet este scris fără spații. Pentru a remedia acest lucru, formula trebuie îmbunătățită.Între adresele celulei după punct și virgulă scrieți" " . Rezultatul este următoarea formulă.
=CONCATENATE(A1;" ";B1;" ";C1)
A ieșit așa.
Acum copiați formula în coloană.
A doua varianta.
În loc de CONCATENATE, puteți apăsa pur și simplu butonul ampersand (&).Formula va arăta așa.
=A2&B2&C1
Rezultatul este același ca în prima opțiune. Dacă nu există spații între cuvinte, atunci introduceți un spațiu (" ").
Formula va fi așa.=A2&" "&B2&" "&C2
Puteți combina nu numai cuvinte, ci și numere. Poate sa faceți o propoziție din datele celulei în Excel.
Puteți seta formule în celulele necesare ale formularului.De exemplu, avem o listă de clienți cu adrese.

Trebuie să facem o propunere. Scriem formula în celulă.
=A2&" "&B2&" "&C2&" "&"locuieste la"&" "&"g."&" "&D2&" "&"strada."&" "&E2&" "&"d."&" "&F2& " ."
Aceasta a fost propunerea.

Folosim acest principiu pentru a elabora orice propuneri.
Dacă textul din celule este deja scris, dar avem nevoie introduceți cuvinte suplimentareînaintea celor existente, atunci acest lucru se poate face folosind formula.Avem această listă.
Trebuie să inserăm cuvântul „Chiriaș” înaintea numelor de familie.În celula noii coloane scriem formula.
="Chiriaș"&" "&A8
Copiați această formulă în coloană. Rezultatul este următoarea listă.
Prima coloană poate fi ascunsă sau poate fi copiată valoarea unei coloane noi fără formule, iar prima coloană și a doua cu formule pot fi șterse.
O altă modalitate de a adăuga text, numere, simboluri la textul dintr-o celulă, consultați articolul „ Adăugați text în celulele de text Excel ".
Folosind formule, puteți converti o listă, în care sunt scrise integral prenumele, al doilea nume și numele de familie lista cu numele de familie si initialele. De exemplu, celula spune:
În coloana următoare scriem următoarea formulă.
=CONCATENATE(LEFT(SPACE(A1),FIND(" ",SPACE(A1),1)),PSTR(SPACE(A1),FIND(" ",SPACE(A1),1)+1,1);" .";PSTR(SPATIU(A1);FIND(" ";SPATIU(A1); FIND(" ";SPATIU(A1);1)+1)+1;1);."")
S-a întâmplat.
Dacă există spații suplimentare între cuvinte, le puteți elimina. Citiți mai multe despre asta în articolul „ Cum să eliminați spațiile suplimentare în Excel„. Folosind aceleași metode, puteți elimina spații dintre numere dintr-o formulă, deoarece spațiile suplimentare pot duce la o eroare la calcul sau formula nu va conta.
Puteți muta datele într-un rând din ultimele celule în prima, inversează linia. De exemplu, în celule este scris: în prima celulă este Ivanov, în a doua - Maria. Trebuie să scriem Maria în prima celulă, Ivanova în a doua.Cum să faci acest lucru rapid într-un tabel mare, vezi articolul "".