msoffice-prowork

Mulți oameni știu că, pentru a combina textul în două celule, este suficient să folosiți funcția CONCATENATE, totuși, ce se întâmplă dacă este necesar să nu combinați, ci mai degrabă să divizați textul într-o celulă? Daca se cunoaste numarul de caractere de separat (nu conteaza la dreapta sau la stanga), atunci poti folosi functiile STANGA sau DREAPTA, in functie de ce parte trebuie sa selectezi un anumit numar de caractere.

Totuși, ce se întâmplă dacă este necesară împărțirea unei celule în care nu se știe numărul de caractere care trebuie separate, ci doar câte părți trebuie obținute în urma operației. cu cel mai mult exemplu simpluÎntr-o astfel de situație, poate fi necesar să selectați din celula în care este introdus numele complet al persoanei, separat numele de familie, prenumele și patronimul. Numele de familie sunt diferite pentru fiecare, așa că nu va funcționa să știi în avans numărul de caractere care trebuie separate.

Pentru revizuirea dvs., vă prezentăm două moduri de a împărți textul. Unul este foarte rapid - pentru cei care trebuie doar să împartă textul înlocuindu-l pe cel existent, iar al doilea folosind formule.

Prima cale este super rapidă.

De fapt, există o capacitate încorporată de a împărți rapid textul într-o celulă dacă există sau există un separator / separatori (de exemplu, un spațiu simplu sau virgulă). Mai mult, pot exista mai multe astfel de separatoare, de ex. textul va fi împărțit dacă linia conține fie un spațiu, fie un punct și virgulă sau virgulă etc.

Pentru a face acest lucru, selectați celulele cu textul pe care doriți să îl împărțiți și utilizați comanda „Text în coloane”.

În versiunea în limba engleză, această comandă sună ca „Text în coloane” din fila „DATE”.

După solicitările simple ale vrăjitorului (de fapt, în exemplul nostru, după alegerea unui separator - un spațiu, puteți apăsa în siguranță „Finish”)

Și iată, de fapt, rezultatul.

A doua modalitate este utilizarea formulelor.

Într-o astfel de situație, veți avea nevoie de o combinație de funcții: SEARCH (SEARCH) și MID (MID). Pentru început, folosind primul, găsim un spațiu între cuvinte (între nume și prenume și prenume și patronimic), apoi îl conectăm pe al doilea pentru a selecta numărul necesar de caractere. În linii mari, prima funcție determină numărul de caractere, iar a doua - o împărtășim deja.

În plus, deoarece nu există un caracter de separare la sfârșitul rândului, numărul de caractere din ultimul cuvânt (în cazul nostru, patronimic) nu poate fi calculat, dar aceasta nu este o problemă, este suficient să specificați un deliberat mai mare. număr de caractere ca argument „număr de caractere” al funcției MID, de exemplu, 100.

Să luăm în considerare cele de mai sus cu un exemplu. În primul rând, pentru o mai bună înțelegere, să împărțim formulele și, astfel, să împărțim întregul proces în două etape.

Pentru a determina numărul de caractere care trebuie alocate într-o linie, este necesar să se determine poziția separatoarelor (în cazul nostru, un spațiu) acestea vor fi cu unul mai puțin decât cuvintele din celulă.

Deoarece au fost obținute informații despre numărul de caractere necesare, următorul pas este utilizarea funcției MID.

Aici, ca argumente, sunt folosite valori intermediare obținute cu ajutorul funcției SEARCH. Pentru ultima coloană, numărul de caractere este necunoscut, așa că a fost luat un număr mai mare de caractere (în cazul nostru, 100).

Acum să încercăm să combinăm calculele intermediare într-o singură formulă.

Dacă textul din celulă trebuie împărțit doar în două părți, atunci este necesar să căutați doar un spațiu (sau un alt separator care se află între cuvinte) și să fie împărțit în 4 sau mai multe părți, formula va trebui să fi complicat căutând al 3-lea, al 4-lea etc. separatoare.

Cumpărați produse

Am luat în considerare anterior posibilitatea pe exemplul împărțirii unui nume complet în părți constitutive. Pentru a face acest lucru, am folosit instrumentul din 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 aceasta metoda există un mic dezavantaj. Dacă, de exemplu, vi se trimit în mod constant date într-o anumită formă și trebuie în mod constant să le partajați, atunci este nevoie de un anumit timp, în plus, dacă datele v-au fost trimise din nou, atunci va trebui să faceți din nou toate operațiunile.

Dacă luăm în considerare exemplul de împărțire a numelui complet, atunci va fi posibil să divizăm textul folosind text formule Excel, folosind funcția și , pe care le-am considerat î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, patronimicul î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 final 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 considerat deja în articolul precedent, vom găsi poziția primelor spații. Pentru a face acest lucru, în celula „H2” scriem formula

FIND(" ";A2;1)

Acum trebuie să găsim numărul ordinal al celui de-al doilea spațiu. Formula va fi aceeași, dar cu o ușoară diferență. Dacă scriem aceeași formulă, atunci funcția va găsi primul spațiu pentru noi și 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 primelor spații, vom spune funcției FIND să caute un spațiu care începe de la prima literă după primul spațiu. Funcția va arăta astfel:

FIND(" ";A2;H2+1)

Se trece la împărțirea primei părți a textului - Nume

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

MID(text ; poziție_începută; număr_de_caractere ), unde

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

Formula va arăta astfel:

MID(A2;1;H2-1)

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

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

1. textul este același text cu numele complet, în exemplul nostru este celula A2;
2. poziție_începută- în cazul nostru, Numele începe cu prima literă după primul spațiu, cunoscând poziția acestui spațiu, obținem H2+1 ;
3. num_characters - numărul de caractere, adică numărul de litere din nume. Vedem că avem un nume î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, atunci vom obține diferența, care va fi egală cu numărul de caractere din nume, adică I2-H2

Obtinem formula finala:

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

Se trece la împărțirea celei de-a treia părți a textului - Patronimic

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

1. textul este același text cu numele complet, în exemplul nostru este celula A2;
2. poziție_începută- Mijlocul nostru este după al 2-lea spațiu, deci poziția inițială va fi egală cu poziția celui de-al doilea spațiu plus un semn sau I2+1 ;
3. number_characters - în cazul nostru, nu există semne după patronimic, deci putem pur și simplu să luăm orice număr, principalul lucru este ca acesta să fie mai mult decât numărul posibil de caractere din patronimic, am luat o cifră cu o marjă mare - 50

Obținem o funcție

MID(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 acest lucru sau puteți scrie calcule intermediare ale poziției spațiilor în formulele de împărțire a textului în sine. Este foarte ușor de făcut. Putem vedea că calculul primului spațiu este în celula H2 - FIND(" ";A2;1) , iar calculul celui de-al doilea spațiu din celula I2 este FIND(" ";A2;H2 +1) . Vedem că H2 apare în formula celulei I2, îl schimbăm în formula însăși și obținem o formulă imbricată în celula I2

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

• Nume \u003d MID (A2; 1; H2 -1) obținem \u003d MID (A2; 1; FIND (" "; A2; 1) -1)
• Nume = MID(A2;H2 +1;I2 -H2 ) obținem =MID(A2;FIND(" ";A2;1) +1;
  FIND(" ";A2;FIND(" ";A2;1)+1)-FIND(" ";A2;1) )
• Al doilea nume \u003d PSTR (A2; I2 +1; 50) obținem \u003d PSTR (A2; FIND(" ";A2;FIND(" ";A2;1)+1)+1;50)

Acum, calculele intermediare ale poziției cu un spațiu pot fi eliminate în siguranță. Acesta este unul dintre trucurile când, 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 o greșeală.

Sperăm că acest exemplu ți-a arătat clar cât de util este textul Funcții Excel pentru lucrul cu textul și modul în care acestea vă permit să divizați automat textul folosind formule de același tip de date. Dacă ți-a plăcut articolul, atunci vom fi recunoscători pentru 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 pe coloane în Excel folosind formula

Luați în considerare al doilea exemplu, care este, de asemenea, foarte comun în practică. Exemplul este similar cu cel precedent, dar sunt mult mai multe date de împărțit. În acest exemplu, voi arăta un truc 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, poate fi orice alte caractere, inclusiv spații). Adică trebuie să despărțim textul în cuvinte.

Amintiți-vă că manual (fără formule) această problemă este foarte simplu rezolvată cu ajutorul căreia am luat în considerare deja. În cazul nostru, trebuie să facem acest lucru folosind formule.

Mai întâi trebuie să găsiți o divizare 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 a fost un spațiu. Al doilea exemplu este mai general (mai convenabil pentru în număr mare date), deci, de exemplu, am putea împărți în mod convenabil nu numai numele complet în celule separate, ci întreaga propoziție - fiecare cuvânt din celulă separată. De fapt, o astfel de întrebare a fost primită în comentarii, așa că s-a decis completarea acestui articol.

Pentru comoditate, vom indica acest separator în coloana următoare pentru a nu-l prescrie în formulă, ci pur și simplu pentru a ne referi la celulă. Acest lucru ne va permite, de asemenea, să folosim fișierul pentru alte sarcini prin simpla schimbare a separatorului din celule.

Acum esența principală a recepției.

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 întindem până 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 )

În primul rând: fixăm coloana cu valoarea și textul dorit, astfel încât atunci când formula este trasă la 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: al treilea argument - vom calcula î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.

Notați formula și trageți-o în jos.

Pasul 3 Găsiți pozițiile tuturor celorlalți separatori. Pentru a face acest lucru, întindem formula pentru găsirea celui de-al doilea separator (pasul 2) la dreapta după numărul de celule, câte valori rupte separat pot fi cu o marjă mică. Obțineți toate pozițiile separatoarelor. Unde eroarea #Value înseamnă că valorile s-au terminat și formula nu mai găsește separatori. Obținem următoarele

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

MID(A1;1;D1-1)

Avem o poziție de pornire de 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 MID din celula P1

MID($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

Fixăm coloana A a textului sursă, astfel încât să nu se miște atunci când formula este trasă la dreapta.

Pasul 6. Să întindem formula obținută la pasul 5 la dreapta și în jos și obținem textul î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 interfereze. Obținem soluția finală a problemei

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

Notă. Am făcut prima poziție a separatorului și prima împărțire a cuvântului diferit față de celelalte și din această cauză am putut întinde formula doar din a doua valori. În timp ce scriam sarcina, am observat că ar fi posibil să simplific sarcina. Pentru a face acest lucru, în coloana C, a fost necesar să scrieți 0 din valoarea primului separator. După aceea, găsim valoarea primului separator

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

iar primul text ca

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

După aceea, 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 separator la sfârșitul textului, nu numărăm numărul de caractere de la ultimul separator până la sfârșitul rândului, deci ultimul text separat lipsește. Pentru a rezolva problema, puteți fie să adăugați o coloană auxiliară lângă textul sursă în primul pas, unde să concatenați acest text cu un separator. Astfel, obținem că va exista 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 decizie este la pasul 3, când alcătuim o formulă de calcul a pozițiilor separatoarelor pentru a o completa. Faceți o verificare, dacă există o eroare, atunci indicăm un număr în mod deliberat mare, de exemplu 1000.

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

Astfel, ultimul text va fi calculat începând de la ultima virgulă până la puțin mai puțin de 1000 de caractere, adică până la sfârșitul rândului, ceea ce ne trebuie.

Ambele versiuni sunt disponibile pentru descărcare.

Exemplu de descărcare: (fixat: câmp suplimentar)
Exemplu de descărcare: (fixat: număr în mod deliberat mare)

Adesea la intocmirea unui contract, factura, oferta comerciala sau alt document lucram cu numele, prenumele si 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 este necesar să faceți o coloană cu semnatarul, lăsând numele de familie și parafa.

Cu siguranță poți scrie o formulă, care va extrage datele necesare din numele complet, dar chiar și o persoană cu experiență va avea nevoie de 5-7 minute pentru aceasta. Dar dacă nu știți dinainte 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

Sarcina acestei funcții este de a extrage doar numele de familie din celulă. Și nu-i pasă de ordine. Numele de familie poate merge atât 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 numele din textul cu numele complet, de asemenea, nu contează unde se află.

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

Ultima funcție din această serie este funcția = PATRONYMID(nume complet) a extrage patronimic.

Exemple de lucru cu numele complet în Excel

Eliminați numele de familie și inițialele

Una dintre sarcinile comune în întocmirea documentelor este transformarea textului formularului Prenume Prenume Patronimic în textul Prenume I. O. O puteți rezolva folosind următoarea formulă.

Program pentru editarea datelor. Informațiile din fișier sunt prezentate sub forma unui tabel, care constă din rânduri și coloane. Cea mai mică unitate de măsură în document Excel- celula. Aceste elemente au un număr condiționat, care este determinat de relația lor cu coloana și rândul, de exemplu, A1 sau D13. Puteți modifica lățimea și înălțimea celulelor pentru a le oferi aspectul dorit, astfel încât forma, dimensiunea și raportul de aspect să se potrivească nevoilor dvs. Puteți, de asemenea, să îmbinați elemente adiacente pe ambele părți sau să despărțiți pentru a ajusta structurile tabelului. Din păcate, deoarece o celulă este cea mai mică unitate a unui fișier din Excel, nu poate fi împărțită.

Foile de calcul Excel sunt foarte populare și sunt adesea folosite pentru a lucra cu date. Uneori, utilizatorii trebuie să împartă o celulă în două sau mai multe părți, dar nu există o astfel de posibilitate în program. Cu toate acestea, există modalități de a ocoli această limitare și de a oferi mesei aspectul dorit.

Necesitatea divizării unei celule poate apărea dacă una dintre coloane trebuie să conțină două sau mai multe elemente de date. De exemplu, două sau mai multe nume ale unui anumit articol, în timp ce altele din coloana „nume” au doar unul. În plus, unii utilizatori trebuie să împartă datele conținute într-o celulă în mai multe. Cel mai comun exemplu este împărțirea numelui complet al unei persoane separat în nume de familie, prenume și patronimic, astfel încât fiecare valoare să ocupe o celulă separată. În ambele cazuri, puteți efectua împărțiri cu caracteristici precum îmbinarea celulelor, Textul pe coloane, Completarea instantanee și o varietate de formule care sunt personalizabile pentru fiecare caz.

Excel are o anumită structură de tabel - acest lucru este necesar pentru a facilita navigarea în date și nu există erori în formule și calcule, probleme cu combinarea și citirea. Fiecare celulă are propriul său număr individual, care este determinat de poziția sa pe axele numerelor și literelor latine. De exemplu, adresa alfanumerice a primului element din tabel este A1. Un rând corespunde unei celule de tabel și invers. Aceasta înseamnă că este un element minimal și nu poate fi împărțit în două sau mai multe independente.

În unele cazuri, utilizatorul Excel trebuie să se asigure că există două sau mai multe valori într-o coloană pentru a se intersecta cu unul dintre rânduri. De exemplu, în cazul în care acest lucru sau acela are mai multe nume sau numere, iar datele celorlalte se încadrează într-o celulă. În mod similar cu rândurile, dacă o coloană include categorii primare (de exemplu, „programatori”, „designeri” și așa mai departe), iar cealaltă - secundară („programatori” - Ivan, Peter). Chiar dacă nu puteți face acest lucru direct în editor, limitarea poate fi ocolită. Pentru asta ai nevoie de:

1. Planificați în avans ce va include numărul maxim de valori într-un rând sau coloană;
2. In pregatire Foaie Excel să lucreze pentru a îmbina acele celule dintr-o coloană sau un rând care vor fi folosite ca celule unice;
3. Astfel, celulele „divizate” vor fi elemente independente, iar celulele „întregii” vor fi conectate, adică rezultatul va fi vizual (dar va îndeplini totuși cerințele Excel).

Exemplu: în coloana A și rândurile 1-5 aveți 5 nume de familie, iar coloana B alăturată conține funcțiile acestor persoane în companie. Dacă una sau mai multe persoane dețin 2 poziții, introduceți a doua în coloana C, iar pentru restul, combinați pur și simplu B1 și C1, B2 și C2 și așa mai departe. În mod similar, în cazurile în care o valoare din prima coloană corespunde cu mai mult de 2 în cele ulterioare. Fiecare celulă va avea propria sa adresă în Excel și va rămâne complet funcțională.

Cum să divizați celulele îmbinate atunci când planificați o structură

Dacă, după acțiuni similare cu cele descrise în paragraful anterior, decideți că întreaga pagină trebuie să revină la starea anterioară și să împărțiți celulele:

1. Deschideți foaia dorită, selectați toate celulele (sau o anumită parte) și accesați fila „Acasă” din panoul de sus al Excel;
2. În zona „Aliniere”, faceți clic pe săgeată și deschideți lista derulantă cu funcțiile „Imbinare și Centrare”, apoi selectați „Unmerge Cells”;
3. Elementele vor fi împărțite în unele unice, dar toate datele vor fi mutate în stânga sus - le puteți distribui în coloane folosind funcția „Text cu coloane”, pe care o vom lua în considerare în continuare.

Cum să împărțiți vizual o celulă într-un tabel Excel, cum să împărțiți un element în diagonală

Dacă este suficient pentru a sparge o celulă vizual fără a-i modifica proprietățile și adresele în Excel, trebuie să:

1. Plasați cursorul pe elementul dorit sau selectați mai multe (sau întreaga foaie).
2. Deschideți fila „Acasă”, în zona „Celule”, faceți clic pe „Format”.
3. Se va deschide un meniu drop-down unde trebuie să selectați „Format Cells”.
4. În fereastra nouă, trebuie să accesați fila „Chenar” - aici puteți desena singur marginile celulelor necesare (linii verticale, orizontale și diagonale, mai multe opțiuni de linii și multe culori).
5. Există o altă opțiune - trebuie să faceți clic dreapta pe celulele selectate pentru a afișa meniul contextual, apoi selectați „Format Cells”, mergeți la fila „Border” și trasați linii în același mod.
6. Una sau mai multe celule selectate vor primi marcajul pe care l-ați specificat.

Pentru a crea o celulă care va include numele rândurilor și coloanelor în același timp, trebuie să faceți:

1. În fereastra „Format celule”, în fila „Chenar”, trageți orice linie diagonală care merge din colțul din stânga sus în colțul din dreapta jos.
2. Aplicați formatarea.
3. Introduceți text în „sus” celulei (este separat doar vizual), care se va potrivi cu șirul, de exemplu „titlu”).
4. Aliniați-l la stânga sau la dreapta, poziționați-l mai precis folosind spații.
5. În timp ce editați un element, apăsați Alt + Enter pentru a accesa linie nouă, apoi introduceți text pentru coloană, cum ar fi „suma”;
6. Dacă textul este poziționat sau nu arată așa cum doriți, trebuie să îl repoziționați cu un spațiu sau să modificați dimensiunea și raportul de aspect al celulelor.

Cum să împărțiți datele celulei pe coloane în foaia de calcul Excel folosind separatorul

Dacă aveți celule care sunt umplute cu unele date și trebuie să le sortați în coloane după categorie, utilizați funcția de împărțire. Este perfect atunci cand elementele contin informatii, de exemplu, despre persoanele invitate - prenume, prenume, numarul biletului, orasul sau tara lor, data sosirii. Dacă toate acestea au fost transferate de la document text, nu va avea nicio formatare. Pentru a face mai convenabil lucrul în Excel, datele trebuie împărțite în coloanele corespunzătoare - „prenume”, „nume” și așa mai departe.

Se face astfel:

1. Creați noi coloane goale dacă nu sunt suficiente în dreapta celei care conține informații (trebuie să fie cel puțin atât cât numărul de categorii de date), în caz contrar informațiile vor fi scrise altora care au fost deja completate . Plasați cursorul mouse-ului după coloana dorită pe linia cu litere latine și faceți clic pe cadrul tabelului cu butonul dreapta al mouse-ului, în meniul contextual selectați Lipire. Dacă trebuie să adăugați mai multe coloane goale, selectați mai întâi aceeași cantitate în dreapta celei pe care o căutați (faceți clic pe celula cu Literă latinăși trageți selecția).
2. Selectați coloana pe care doriți să o împărțiți. Deschideți „Date” - „Lucrul cu date” - „Text pe coloane”.
3. În fereastra nouă (Wizard of Column Text Distribution) selectați formatul de date. Dacă coloana pe care o căutați are informații din diferite categorii separate prin spații sau virgule, selectați „Delimitat” dacă are o cantitate fixă ​​de date - „ lățime fixă” (de exemplu, un ID digital - vom lua în considerare această opțiune mai târziu), faceți clic pe „Următorul”.
4. Apoi specificați delimitatorii care sunt utilizați în matricea de text din coloană. Specificați-le în „Caracterul separator este” (dacă există mai multe, enumerați-le pe toate în câmpul „Altele”). De asemenea, selectați Tratează separatorii consecutivi ca unul dacă datele conțin mai multe tipuri pe rând (de exemplu, două spații pe rând sau un punct care indică o abreviere a unui cuvânt, nu sfârșitul unei propoziții, urmat de o virgulă).
5. Setați terminatorul de linie dacă există propoziții în text care sunt cuprinse între ghilimele și conțin separatori dintr-un alt paragraf, dar nu le puteți întrerupe. Acestea includ propoziții precum „Rusia, Moscova” - adresa în acest caz trebuie să rămână completă. Dacă nu setați un limitator, „Rusia” și „Moscova” vor ajunge în coloane diferite.
6. Selectați un format de date. În mod implicit, este „General”. Dacă informațiile dvs. conțin date sau sume Bani, specificați coloanele corespunzătoare în care vor fi plasate. Aici puteți specifica și unde vor fi plasate anumite date. Faceți clic pe pictograma de selecție a intervalului din dreapta „Place in” și, ca primă coloană, selectați cea mai din stânga dintre cele libere de completat. Din păcate, datele nu pot fi mutate într-un alt registru de lucru Excel și chiar într-o altă foaie, dar le puteți împărți pe cea curentă și apoi pur și simplu le puteți copia la locul potrivit.
7. Faceți clic pe „Finalizare” - toate setările vor fi aplicate. Salvați documentul pentru a nu-l pierde.

Cum să răspândiți datele celulei în coloanele din foile de calcul Excel folosind umplere flash

Începând cu versiunea 2013, Microsoft Office Excel oferă opțiunea de a recicla Flash Fill. Cu această funcție, puteți forța editorul să distribuie automat datele în celulele coloanei de îndată ce observă un model în intrare.

Opțiunea funcționează astfel: Excel începe să analizeze datele pe care le introduceți în celulele foii de lucru și încearcă să afle de unde provin, la ce corespund și dacă există un model în ele. Deci, dacă în coloana A aveți nume de familie și prenume de persoane, iar în B introduceți nume de familie, folosind Instant Fill, utilitarul va calcula acest principiu și va oferi distribuirea automată a tuturor valorilor în coloana B.

Cu acest instrument, trebuie doar să introduceți doar o parte a datelor în noua coloană - având în vedere că funcția funcționează în modul pasiv, este foarte convenabilă. Pentru a-l activa și utiliza:

1. Asigurați-vă că aveți „Instant Fill” activat - se află în fila „Fișier” - elementul „Opțiuni” - „Avansat” - „Efectuați automat umplerea instantanee” (bifați caseta dacă nu este acolo).
2. Începeți să introduceți date de la celălalt într-una dintre coloane, iar editorul se va oferi să distribuie informațiile în bloc. Dacă ceea ce oferă Excel vi se potrivește, apăsați Enter.
3. Dacă funcția este activată, dar nu funcționează într-un anumit șablon, rulați instrumentul manual în „Date” - „Instant Fill” sau apăsați Ctrl + „E”.

Cum să distribuiți datele celulei pe coloane într-o foaie de calcul Excel folosind formule

Excel are formule care fac împărțirea datelor mai ușoară și mai funcțională. De regulă, comenzile LEFT, MID, RIGHT, FIND, SEARCH și LONG sunt de obicei suficiente. Să vedem când sunt necesare și cum să le folosim.

Cum să împărțiți numele și prenumele în 2 coloane

Unul dintre cele mai frecvente cazuri este nevoia de a separa numele și prenumele din coloana A în B și, respectiv, C. Pentru a face acest lucru, trebuie să vă asigurați că editorul însuși găsește un decalaj între valori și rupe totul automat. Folosiți formula „=LEFT(A2;SEARCH(" ";A2;1)-1)". Acesta caută spații în căutare, apoi le ia ca separator și trimite, de exemplu, numele de familie în stânga celor două coloane și prenumele în dreapta. La fel și cu alte valori care sunt separate prin spații. Formula specificată nu este potrivită pentru celule mai complexe, inclusiv nume cu nume de familie și patronimice, sufixe și alte date.

Cum se separă prenume, prenume și al doilea nume în 3 coloane

Dacă trebuie să împărțiți numele complet în coloane din trei valori (în acest caz, oricare dintre ele poate fi doar sub forma unei inițiale a unei litere):

1. Utilizați formula „=LEFT(A2;FIND(" ";A2;1)-1)" pentru a separa numele;
2. Folosiți „=MID(A2;FIND(" ";A2;1)+1;FIND(" ";A2;FIND(" ";A2;1)+1)-(FIND(" ";A2;1)+ 1))" pentru a găsi un patronim (într-o intrare precum „Ivanov Ivan Ivanovici”)
3. Utilizați „=RIGHT(A2;LSTR(A2)-FIND(" ";A2;FIND(" ";A2;1)+1))" pentru a extrage numele de familie.

Aceleași formule pot fi folosite pentru intrări precum „Ivanov Ivan Jr.” (în stilul occidental) sau altele care conțin un sufix.

Cum se distribuie datele dacă sunt separate prin virgulă

Dacă datele din celule sunt scrise sub forma „Black, Bob Mark” (numele complet cu numele de familie în față - în Limba englezăîn acest caz, este necesară o virgulă), le puteți separa în familiarul „Bob Mark White” după cum urmează:

1. Folosiți „=MID(A2;SEARCH(" ";A2;1)+1;FIND(" ";A2;FIND(" ";A2;1)+1)-(FIND(" ";A2;1)+ 1))" pentru a evidenția numele;
2. Folosiți „=RIGHT(A2;LSTR(A2)-FIND(" ";A2;FIND(" ";A2;1)+1))" pentru a extrage al doilea nume;
3. Folosiți „=LEFT(A2;FIND(" ";A2;1)-2)" pentru a extrage numele de familie."

Alte formule

Excel vă permite să lucrați nu numai cu numele și prenumele persoanelor, ci și cu alte tipuri de date. Un alt exemplu comun sunt adresele. Dacă celula conține informații precum „Rusia, Moscova, strada Arbat”, puteți distribui valorile între alte elemente specificând o virgulă, un punct sau un alt caracter arbitrar ca separator. Pentru a împărți o astfel de adresă în 3 părți (țară, oraș, stradă):

1. Folosiți „=LEFT(A2;SEARCH(",";A2)-1)” pentru a separa țara;
2. Folosiți „=MID(A2;SEARCH(",";A2)+2;SEARCH(",";A2;SEARCH(",";A2)+2)-SEARCH(",";A2)-2)" a evidenția un oraș;
3. Folosiți „=DREAPTA(A2;LUNG(A2)-(CĂUTARE(",";A2;CĂUTARE(",";A2)+1)+1))” pentru a separa strada.

Astfel, scopul formulei specificate este de a separa datele în locul în care apare pictograma dată (în acest caz, o virgulă). Pune-l doar între ghilimele.

Rezultat

Microsoft Office Excel oferă o gamă largă de posibilități de lucru atât cu grila tabelului, cât și cu conținutul acesteia. Deși nu există nicio funcție de a împărți o celulă în mai multe părți, puteți obține rezultatul prin planificarea structurii și gruparea elementelor. Dacă formatarea nu vă convine, o puteți anula pe întreaga foaie. Marginile pot fi folosite pentru a împărți un element în diagonală, pentru a vă asigura că numele coloanelor sunt în dreapta sus și rândurile sunt în stânga jos. Dacă doriți să distribuiți o serie de informații în celulele din alte coloane, utilizați formule, Flash Fill sau Text to Columns.

Cum să împărțiți textul în coloane în două, trei sau mai multe celule în Excel 2007. De obicei, de exemplu, folosesc o defalcare a celulelor cu un nume complet, astfel încât datele să fie separate.

Cum să împărțiți textul în coloane în Excel

La serviciu, a trebuit să separ ora din celulele tabelului excel, de exemplu: 0:00-15:00. Iar între valori, separatorul este punct și virgulă 0:00-15:00; 16:00-17:00; 22:00-24:00. Aceste date sunt în tabel pe zi, în celule există un număr diferit de valori între separator. A fost necesar să împărțiți timpul pentru fiecare zi, să puneți timpul într-o coloană sub dată, să împărțiți timpul la început și la sfârșit, să calculați diferența și să adăugați totul împreună ...

Prima etapă. Împărțiți timpul în celule

Selectați o gamă de celule, pe panglică, faceți clic pe Date-Text pe coloane, va apărea Text Wizard.

Nu schimbăm nimic, dacă comutatorul dvs. este în poziția: cu un separator, faceți clic pe următorul.

Debifați caseta de selectare „filă”, bifați caseta de selectare „altul”.

și introduceți punct și virgulă [ ; ]. Apăsăm fie mai departe, deși nu există nimic special de urmărit acolo, fie, mai degrabă, clic gata. Se va pune întrebarea: înlocuiți conținutul celulelor? Raspunsul este da!

Am putut sparge celula câte valori au fost între [ ; ]. Adăugați linii goale sub celula cu data, în numărul de celule din dreapta coloanei C.

Două celule, două rânduri. Selectați celule, copiați,

iar sub prima celulă, faceți clic Click dreapta„Lipire specială” - „Transpunere”.

Datele vor fi transferate de la rând la coloană, să facem același lucru pentru restul celulelor.

S-a întâmplat text împărțitîntr-o celulă, aranjați pe zi, într-o coloană.

Etapa a doua. Cum să împărțiți o celulă după ora de început și ora de sfârșit în Excel

Să obținem diferența de timp recurgând din nou la vrăjitorul text. Hai sa continuăm text împărțit în celule pentru orele de început și de sfârșit. Aici delimitatorul este semnul "-" minus.

Să scoatem coloana din tabelul nostru prin copierea și lipirea celulelor de lângă ea. Lucrăm cu vrăjitorul: Data-Text pe coloane, primul pas nu schimbă nimic, la al doilea pas în cealaltă casetă [-] pune un minus, click gata.

Etapa a treia

Am pus formula: ora de încheiere minus ora de începere,

format de celule Toate formatele [h]:mm,

Să întindem formula până la capăt. Avem timpul în ore. În celula următoare, scrieți: \u003d K4 * 60 Enter. Obțineți timpul în minute.

Adevărat cu secunde... Nu am găsit o modalitate mai bună decât să copiez coloana, să o lipesc în notepad, să apeși CTRL + H (Găsiți și înlocuiți), am scris în găsire: 00

si l-a lasat gol...

Am dat clic pentru a înlocui totul... Am copiat textul fără: 00 din blocnotes și l-am lipit în tabel.

Am făcut-o mai ușor cu numărul de ore, l-am copiat într-un caiet și l-am lipit în tabel. O problemă: 24 de ore contează ca 24:00:00.

Nu este o problemă, din moment ce știm cum să împărțiți o celulă în excel, apoi poți folosi și Copy&Paste, dar cu un truc - în celulă înainte de ora 24:00 pune un apostrof ‘ -ghilimele simple „24:00

și lipiți peste tot. Puteți adăuga doar minute prin autosumă, obținem suma orelor împărțind suma minutelor la 60, iar numărul de zile împărțim numărul de ore rezultat la 24 ....

În exemplul meu, ceva din 31 de zile a lucrat 28 de zile, din 744 de ore - 673 de ore și din 44640 de minute - 40411 minute... Nu contează ce este, principalul lucru este că în acest exemplu ai învățat cum se face în Excel împărțiți textul în coloaneîntr-o celulă, cum puteți utiliza blocnotesul într-un mod nestandard și cum puteți schimba formatul celulei adăugând un apostrof la ele.

Acest videoclip arată în detaliu cum să împărțiți textul în coloane:

Vă recomandăm să vizionați videoclipul modul ecran întreg, în setările de calitate alegeți 1080 HD, nu uita abonați-vă la canalul YouTube, acolo vei găsi multe video interesant care iese destul de des. Vizionare fericită!