Funcții text și formule în excel. Lucrul cu șiruri în Excel

• semn mai mic sau egal<=»;

• semnul „nu este egal”.<>».

Operator de concatenare a textului

Caracterul special „&” (ampersand) este folosit în acest scop. Cu acesta, puteți conecta diferite fragmente într-un singur întreg - același principiu ca și cu funcția „CONNECT”. Aici sunt cateva exemple:

1. Dacă doriți să combinați textul în celule, atunci trebuie să utilizați următorul cod.

1. Pentru a introduce un caracter sau o literă între ele, trebuie să utilizați următoarea construcție.

1. Puteți combina nu numai celule, ci și caractere obișnuite.

Orice text, altul decât link-urile, trebuie să fie cuprins între ghilimele. În caz contrar, formula va genera o eroare.

Vă rugăm să rețineți că ghilimele sunt folosite exact ca în captura de ecran.

Puteți utiliza următorii operatori pentru a defini legături:

• pentru a crea o legătură simplă către intervalul dorit de celule, este suficient să specificați prima și ultima celulă din această zonă și simbolul „:” între ele;
• pentru a combina link-uri, utilizați semnul „;”;
• dacă este necesar să se determine celulele care se află la intersecția mai multor intervale, atunci este plasat un „spațiu” între legături. În acest caz, va fi afișată valoarea celulei „C7”.

Deoarece numai ea se încadrează în definiția „intersecției mulțimilor”. Acesta este numele acestui operator (spațiu).

Utilizarea link-urilor

În timp ce lucrați în editorul Excel, puteți utiliza link-uri de diferite tipuri. Cu toate acestea, majoritatea utilizatorilor începători știu să-l folosească doar pe cel mai simplu dintre ei. Vă vom învăța cum să introduceți corect link-uri de toate formatele.

Legături simple A1

De regulă, acest tip este folosit cel mai des, deoarece este mult mai convenabil să le compuneți decât restul.

• coloane - de la A la XFD (nu mai mult de 16384);
• linii - de la 1 la 1048576.

Aici sunt cateva exemple:

• celulă la intersecția rândului 5 și coloanei B - „B5”;
• interval de celule din coloana B începând de la rândul 5 până la rândul 25 - „B5:B25”;
• intervalul de celule din rândul 5 începând de la coloana B la F - "B5:F5";
• toate celulele din rândul 10 - „10:10”;
• toate celulele din rândurile de la 10 la 15 - „10:15”;
• toate celulele din coloana B - "B:B";
• toate celulele din coloanele de la B la K - „B:K”;
• intervalul de celule de la B2 la F5 este „B2-F5”.

Uneori formulele folosesc informații din alte foi. Funcționează după cum urmează.

A doua fișă conține următoarele date.

Dacă există un spațiu în numele foii, atunci în formulă trebuie indicat între ghilimele simple (apostrofe).

Legături absolute și relative

Editorul Excel funcționează cu trei tipuri de link-uri:

• absolut;
• relativ;
• amestecat.

Să le luăm în considerare cu mai multă atenție.

Toate exemplele menționate anterior aparțin adreselor de celule relative. Acest tip este cel mai popular. Principalul avantaj practic este că editorul va schimba referințele la o valoare diferită în timpul migrării. În conformitate cu locul exact în care ați copiat această formulă. Pentru calcul se va lua în considerare numărul de celule dintre poziția veche și cea nouă.

Imaginați-vă că trebuie să extindeți această formulă la întreaga coloană sau rând. Nu veți schimba manual literele și numerele din adresele celulelor. Funcționează după cum urmează.

1. Introduceți formula de calcul a sumei primei coloane.

1. Apăsați tastele rapide Ctrl +C. Pentru a transfera formula în celula următoare, trebuie să mergeți acolo și să apăsați Ctrl + V .

Dacă tabelul este foarte mare, este mai bine să faceți clic pe colțul din dreapta jos și, fără a elibera degetul, să întindeți indicatorul până la capăt. Dacă există puține date, atunci copierea folosind tastele rapide este mult mai rapidă.

1. Acum uitați-vă la noile formule. Modificarea indexului coloanei a avut loc automat.

Dacă doriți ca toate legăturile să fie păstrate la transferul formulelor (adică, astfel încât acestea să nu se schimbe automat), trebuie să utilizați adrese absolute. Acestea sunt specificate ca „$B$2”.

Ca urmare, vedem că nu s-au produs modificări. Toate coloanele afișează același număr.

Acest tip de adresă este utilizat atunci când este necesar să se repare doar o coloană sau un rând, și nu toate în același timp. Puteți folosi următoarele construcții:

• $D1, $F5, $G3 - pentru fixarea coloanelor;
• D$1, F$5, G$3 - pentru a remedia rândurile.

Lucrați cu astfel de formule numai atunci când este necesar. De exemplu, dacă trebuie să lucrați cu un rând constant de date, dar să schimbați doar coloanele. Și cel mai important - dacă urmează să calculați rezultatul în celule diferite care nu sunt situate de-a lungul aceleiași linii.

Faptul este că atunci când copiați formula pe o altă linie, numerele din linkuri se vor schimba automat cu numărul de celule din valoarea inițială. Dacă utilizați adrese mixte, atunci totul va rămâne pe loc. Acest lucru se face în felul următor.

1. Să folosim următoarea expresie ca exemplu.

1. Să mutăm această formulă într-o altă celulă. De preferat nu pe următoarea linie și pe altă linie. Acum puteți vedea că noua expresie conține același șir (4), dar o literă diferită, deoarece era singura care era relativă.

Legături 3D

Conceptul de „tridimensional” include acele adrese în care este indicată o serie de foi. Un exemplu de formulă arată astfel.

În acest caz, rezultatul va corespunde sumei tuturor celulelor „A5” de pe toate foile, începând de la 1 la 4. La compilarea unor astfel de expresii, trebuie respectate următoarele condiții:

• tablourile nu pot folosi astfel de referințe;
• expresiile tridimensionale sunt interzise a fi folosite acolo unde există o intersecție de celule (de exemplu, operatorul „spațiu”);
• Când creați formule cu adrese 3D, puteți utiliza următoarele funcții: AVERAGE, STDEV, STDEV.V, AVERAGE, STDEV, STDEV.Y, SUM, COUNT, COUNT, MIN, MAX, MIN, MAX, VARR, PRODUCT, VARV, VAR.V si DISPA.

Dacă încalci aceste reguli, atunci vei vedea un fel de eroare.

Link-uri în format R1C1

Acest tip de link-uri diferă de „A1” prin faptul că numărul este dat nu numai rândurilor, ci și coloanelor. Dezvoltatorii au decis să înlocuiască vizualizarea normală cu această opțiune pentru comoditate în macrocomenzi, dar acestea pot fi folosite oriunde. Iată câteva exemple de astfel de adrese:

• R10C10 este o legătură absolută către celulă, care este situată pe a zecea linie a celei de-a zecea coloane;
• R - legătură absolută cu legătura curentă (în care este indicată formula);
• R[-2] – legătură relativă cu linia, care se află la două poziții deasupra acesteia;
• R[-3]C – referință relativă la celulă, care se află cu trei poziții mai sus în coloana curentă (unde ați decis să scrieți formula);
• RC este o legătură relativă către celula care se află cinci celule în dreapta și cinci linii sub cea actuală.

Utilizarea numelui

Programul Excel pentru denumirea intervalelor de celule, celule unice, tabele (obișnuite și rezumative), constante și expresii vă permite să vă creați propriile nume unice. În același timp, nu există nicio diferență pentru editor atunci când lucrează cu formule - el înțelege totul.

Puteți folosi numele pentru înmulțire, împărțire, adunare, scădere, calcul al dobânzii, rapoarte, abateri, rotunjire, TVA, credite ipotecare, împrumuturi, estimări, pontaj, diverse forme, reduceri, salarii, vechime, plăți de anuitate, lucrul cu formule VLOOKUP , „VVD”, „REZULTATE INTERIM.” și așa mai departe. Adică poți face ce vrei.

Un singur lucru poate fi numit condiția principală - trebuie să determinați acest nume în prealabil. În caz contrar, Excel nu va ști nimic despre asta. Acest lucru se face în felul următor.

1. Selectați o coloană.
2. Apelați meniul contextual.
3. Selectați „Dați nume”.

1. Specificați numele dorit pentru acest obiect. În acest caz, trebuie să respectați următoarele reguli.

1. Faceți clic pe butonul „OK” pentru a salva.

În același mod, puteți atribui un nume oricărei celule, text sau număr.

Puteți folosi informațiile din tabel atât cu ajutorul numelor, cât și cu ajutorul unor link-uri obișnuite. Așa arată standardul.

Și dacă încercați să introduceți numele nostru în loc de adresa „D4: D9”, veți vedea un indiciu. Este suficient să scrieți câteva caractere și veți vedea ce se potrivește (din baza de nume) cel mai mult.

În cazul nostru, totul este simplu - „coloana_3”. Și imaginați-vă că veți avea un număr mare de astfel de nume. Nu vei putea să memorezi totul.

Utilizarea funcțiilor

Există mai multe moduri de a insera o funcție în editorul Excel:

• manual;
• utilizarea barei de instrumente;
• folosind fereastra Insert Function.

Să aruncăm o privire mai atentă la fiecare metodă.

În acest caz, totul este simplu - vă folosiți mâinile, propriile cunoștințe și abilități pentru a introduce formule într-o linie specială sau direct într-o celulă.

Dacă nu aveți experiență de lucru în acest domeniu, atunci este mai bine să utilizați mai întâi metode mai ușoare.

În acest caz este necesar:

1. Accesați fila „Formule”.
2. Faceți clic pe orice bibliotecă.
3. Selectați funcția dorită.

1. Imediat după aceea, va apărea fereastra „Argumente și funcții” cu funcția deja selectată. Trebuie doar să puneți argumentele și să salvați formula folosind butonul „OK”.

Vrăjitorul de înlocuire

Îl poți aplica astfel:

1. Faceți orice celulă activă.
2. Faceți clic pe pictograma „Fx” sau utilizați comanda rapidă de la tastatură SHIFT + F3.

1. Imediat după aceea, se va deschide fereastra „Inserare funcție”.
2. Aici veți vedea o listă mare de diferite caracteristici sortate pe categorii. În plus, puteți utiliza căutarea dacă nu găsiți articolul dorit.

Este suficient să punctați un cuvânt care poate descrie ceea ce doriți să faceți, iar editorul va încerca să afișeze toate opțiunile potrivite.

1. Selectați orice funcție din lista propusă.
2. Pentru a continua, trebuie să faceți clic pe butonul „OK”.

1. Apoi vi se va cere „Argumente și funcții”. Puteți face acest lucru manual sau pur și simplu selectați intervalul dorit de celule.
2. Pentru a aplica toate setările, trebuie să faceți clic pe butonul „OK”.

1. Ca urmare, vom vedea numărul 6, deși acest lucru era deja de înțeles, deoarece fereastra Argumente și funcții afișează un rezultat preliminar. Datele sunt recalculate instantaneu atunci când oricare dintre argumente se schimbă.

Folosind funcții imbricate

Ca exemplu, vom folosi formule cu condiții logice. Pentru a face acest lucru, va trebui să adăugăm un fel de tabel.

Apoi urmați instrucțiunile de mai jos:

1. Faceți clic pe prima celulă. Apelați fereastra „Inserare funcție”. Selectați funcția „Dacă”. Faceți clic pe „OK” pentru a lipi.

1. Apoi va trebui să compuneți un fel de expresie logică. Trebuie scris în primul câmp. De exemplu, puteți adăuga valorile a trei celule într-un rând și puteți verifica dacă suma este mai mare decât 10. În cazul „adevărat”, specificați textul „Mai mult de 10”. Pentru un rezultat fals - „Mai puțin de 10”. Apoi faceți clic pe OK pentru a reveni la spațiul de lucru.

1. Ca rezultat, vedem următoarele - editorul a spus că suma celulelor din al treilea rând este mai mică de 10. Și acest lucru este corect. Deci codul nostru funcționează.

1. Acum trebuie să configurați următoarele celule. În acest caz, formula noastră pur și simplu se extinde mai departe. Pentru a face acest lucru, mai întâi trebuie să mutați cursorul în colțul din dreapta jos al celulei. După ce cursorul se schimbă, trebuie să faceți un clic stânga și să îl copiați în partea de jos.

1. În consecință, editorul recalculează expresia noastră pentru fiecare linie.

După cum puteți vedea, copia a avut un mare succes deoarece am folosit link-urile relative despre care am vorbit mai devreme. Dacă trebuie să remediați adrese în argumentele funcției, atunci utilizați valori absolute.

Există mai multe moduri de a face acest lucru: utilizați bara de formule sau un expert special. În primul caz, totul este simplu - faceți clic într-un câmp special și introduceți manual modificările necesare. Dar să scrii acolo nu este foarte convenabil.

Singurul lucru pe care îl puteți face este să măriți câmpul de intrare. Pentru a face acest lucru, faceți clic pe pictograma indicată sau apăsați combinația de taste Ctrl + Shift + U.

Este demn de remarcat faptul că aceasta este singura modalitate dacă nu utilizați funcții în formulă.

În cazul utilizării funcțiilor, totul devine mult mai simplu. Pentru a edita, urmați instrucțiunile de mai jos:

1. Faceți activă celula cu formula. Faceți clic pe pictograma „Fx”.

1. După aceea, va apărea o fereastră în care puteți schimba argumentele funcției de care aveți nevoie într-un mod foarte convenabil. În plus, aici puteți afla exact care va fi rezultatul recalculării noii expresii.

1. Pentru a salva modificările efectuate, utilizați butonul „OK”.

Pentru a elimina o expresie, faceți următoarele:

1. Faceți clic pe orice celulă.

1. Faceți clic pe butonul Ștergere sau Backspace. Ca rezultat, celula va fi goală.

Puteți obține exact același rezultat folosind instrumentul Clear All.

Posibile erori la compilarea formulelor în editorul Excel

Următoarele sunt cele mai frecvente greșeli pe care le fac utilizatorii:

• Expresia folosește un număr mare de cuibări. Nu ar trebui să fie mai mult de 64;
• căile către registrele de lucru externe sunt specificate în formule fără calea completă;
• Parantezele de deschidere și de închidere sunt deplasate. De aceea, în editorul din bara de formule toate parantezele sunt evidențiate într-o culoare diferită;

• numele cărților și foilor nu sunt luate între ghilimele;
• numerele sunt folosite în format greșit. De exemplu, dacă trebuie să specificați $2000, trebuie doar să introduceți 2000 și să selectați formatul de celulă adecvat, deoarece simbolul $ este folosit de program pentru referințe absolute;

• argumentele funcției necesare nu sunt specificate. Rețineți că argumentele opționale sunt incluse între paranteze drepte. Totul fără ele este necesar pentru funcționarea completă a formulei;

• Intervalele de celule sunt incorecte. Pentru a face acest lucru, trebuie să utilizați operatorul „:” (coloană).

Coduri de eroare atunci când lucrați cu formule

Când lucrați cu o formulă, este posibil să vedeți următoarele opțiuni de eroare:

• #VALOARE! - Această eroare indică faptul că utilizați tipul de date greșit. De exemplu, încercați să utilizați text în loc de o valoare numerică. Desigur, Excel nu poate calcula suma dintre două fraze;
• #NUME? - o eroare similară înseamnă că ați greșit la ortografie numele funcției. Sau încerci să introduci ceva care nu există. Nu poți face asta. În plus, problema poate fi în alta. Dacă sunteți sigur de numele funcției, atunci încercați să priviți formula mai îndeaproape. Poate ai uitat o paranteză. În plus, trebuie să țineți cont de faptul că fragmentele de text sunt indicate între ghilimele. Dacă toate celelalte nu reușesc, încercați să compuneți din nou expresia;
• #NUMĂR! - afișarea unui astfel de mesaj înseamnă că aveți un fel de problemă cu argumentele sau cu rezultatul formulei. De exemplu, numărul s-a dovedit a fi prea mare sau invers - mic;
• #DIV/0! - Această eroare înseamnă că încercați să scrieți o expresie care se împarte la zero. Excel nu poate anula regulile matematice. Prin urmare, asemenea acțiuni sunt interzise și aici;
• #N / A! - Editorul poate afișa acest mesaj dacă o anumită valoare nu este disponibilă. De exemplu, dacă utilizați funcțiile SEARCH, SEARCH, MATCH și Excel nu ați găsit fragmentul pe care îl căutați. Sau nu există date deloc și formula nu are cu ce să lucreze;
• Dacă încercați să calculați ceva și Excel scrie cuvântul #REF!, atunci se folosește un interval greșit de celule în argumentul funcției;
• #GOL! - această eroare apare dacă aveți o formulă inconsecventă cu intervale suprapuse. Mai exact, dacă în realitate nu există astfel de celule (care se află la intersecția a două intervale). Destul de des, această eroare apare accidental. Este suficient să lăsați un spațiu în argument, iar editorul îl va percepe ca pe un operator special (am vorbit despre asta mai devreme).

Când editați formula (celulele sunt evidențiate) veți vedea că nu se intersectează de fapt.

Uneori puteți vedea o mulțime de # caractere care umplu complet lățimea celulei. De fapt, aici nu există nicio eroare. Aceasta înseamnă că lucrați cu numere care nu se potrivesc în această celulă.

Pentru a vedea valoarea conținută acolo este suficient să redimensionați coloana.

În plus, puteți utiliza formatarea celulelor. Pentru a face acest lucru, trebuie să urmați câțiva pași simpli:

1. Apelați meniul contextual. Selectați Formatare celule.

1. Specificați tipul „General”. Folosiți butonul „OK” pentru a continua.

Datorită acestui lucru, editorul Excel va putea traduce acest număr într-un alt format care se încadrează în această coloană.

Exemple de utilizare a formulelor

Editorul Microsoft Excel vă permite să procesați informații în orice mod convenabil pentru dvs. Există toate condițiile și oportunitățile necesare pentru aceasta. Să ne uităm la câteva exemple de formule pe categorii. Acest lucru vă va face mai ușor să vă dați seama.

Pentru a evalua capacitățile matematice ale Excel, trebuie să efectuați următorii pași.

1. Creați un tabel cu câteva date condiționate.

1. Pentru a calcula suma, introduceți următoarea formulă. Dacă doriți să adăugați o singură valoare, puteți utiliza operatorul de adăugare ("+").

1. Destul de ciudat, în editorul Excel nu puteți elimina folosind funcții. Pentru deducere, se folosește operatorul obișnuit „-”. În acest caz, codul va fi următorul.

1. Pentru a determina cât de mult este primul număr de al doilea ca procent, trebuie să utilizați această construcție simplă. Dacă doriți să scădeți mai multe valori, va trebui să scrieți un „minus” pentru fiecare celulă.

Rețineți că simbolul procentului este plasat la sfârșit, nu la început. În plus, atunci când lucrați cu procente, nu trebuie să înmulțiți suplimentar cu 100. Acest lucru se întâmplă automat.

1. Excel poate adăuga, ținând cont de mai multe condiții simultan. Puteți calcula suma celulelor primei coloane, a căror valoare este mai mare de 2 și mai mică de 6. Și aceeași formulă poate fi setată pentru a doua coloană.

=SUMIFS(B3:B9,B3:B9,">2",B3:B9,"<6") =SUMIFS(C3:C9;C3:C9;">2",C3:C9,"<6")

1. De asemenea, puteți număra numărul de elemente care îndeplinesc o anumită condiție. De exemplu, lasă Excel să calculeze câte numere avem mai mari decât 3.

=NUMĂRĂDAC(B3:B9,">3") =NUMĂRĂDAC(C3:C9,">3")

1. Rezultatul tuturor formulelor va fi după cum urmează.

Funcții matematice și grafice

Folosind Excel, puteți calcula diverse funcții și puteți construi grafice pe ele, apoi puteți efectua analize grafice. De regulă, astfel de tehnici sunt folosite în prezentări.

De exemplu, să încercăm să construim grafice pentru exponent și pentru o ecuație. Instrucțiunea va fi după cum urmează:

1. Să creăm un tabel. În prima coloană vom avea numărul original „X”, în a doua - funcția „EXP”, în a treia - raportul specificat. Ar fi posibil să se facă o expresie pătratică, dar atunci valoarea rezultată pe fundalul exponentului de pe grafic ar dispărea practic.

După cum am spus mai devreme, creșterea exponentului este mult mai rapidă decât cea a ecuației cubice obișnuite.

În mod similar, orice funcție sau expresie matematică poate fi reprezentată grafic.

Tot ceea ce este descris mai sus este potrivit pentru programele moderne din 2007, 2010, 2013 și 2016. Vechiul editor Excel este semnificativ inferior în ceea ce privește caracteristicile, numărul de funcții și instrumente. Dacă deschideți ajutorul oficial de la Microsoft, veți vedea că acestea indică suplimentar în ce versiune a programului a apărut această funcție.

În toate celelalte privințe, totul arată aproape exact la fel. De exemplu, să calculăm suma mai multor celule. Pentru asta ai nevoie de:

1. Specificați câteva date pentru calcul. Faceți clic pe orice celulă. Faceți clic pe pictograma „Fx”.

1. Selectați categoria „Matematică”. Găsiți funcția „SUM” și faceți clic pe „OK”.
1. Puteți încerca să recalculați în orice alt editor. Procesul va fi exact același.

Concluzie

În acest tutorial, am vorbit despre tot ce ține de formule în editorul Excel, de la cele mai simple la cele foarte complexe. Fiecare secțiune a fost însoțită de exemple și explicații detaliate. Acest lucru se face astfel încât informațiile să fie accesibile chiar și pentru manechini completi.

Dacă ceva nu merge pentru tine, atunci faci o greșeală undeva. Poate că aveți greșeli de scriere în expresii sau referințe de celule incorecte. Principalul lucru de înțeles este că totul trebuie condus cu mare atenție și atenție. În plus, toate funcțiile nu sunt în engleză, ci în rusă.

În plus, este important să rețineți că formulele trebuie să înceapă cu simbolul „=" (egal cu). Mulți utilizatori începători uită de asta.

Exemplu de fișier

Pentru a vă face mai ușor să vă ocupați de formulele descrise mai devreme, am pregătit un fișier demonstrativ special în care au fost compilate toate exemplele de mai sus. O poți face de pe site-ul nostru absolut gratuit. Dacă în timpul antrenamentului folosești un tabel gata făcut cu formule bazate pe datele pe care le-ai completat, vei obține rezultate mult mai rapid.

Instrucțiuni video

Dacă descrierea noastră nu v-a ajutat, încercați să vizionați videoclipul atașat mai jos, care spune punctele principale mai detaliat. Poate că faci totul bine, dar îți lipsește ceva. Cu ajutorul acestui videoclip, ar trebui să rezolvați toate problemele. Sperăm că aceste tutoriale v-au ajutat. Verificați-ne mai des.

Excel oferă un număr mare de funcții cu care puteți procesa text. Domeniul de aplicare al funcțiilor de text nu se limitează doar la text, ele pot fi utilizate și cu celulele care conțin numere. Ca parte a acestei lecții, vom analiza exemple ale celor mai comune 15 funcții Excel din categorie Text.

CONECTAȚI

Pentru a concatena conținutul celulelor în Excel, împreună cu operatorul de concatenare, puteți utiliza funcția text CONECTAȚI. Concatenează secvenţial valorile celulelor specificate într-un rând.

LOWERLINE

Dacă Excel trebuie să facă toate literele cu minuscule, de ex. convertiți-le în litere mici, o funcție de text va veni în ajutor LOWERLINE. Nu înlocuiește caracterele care nu sunt litere.

CAPITAL

Funcția text CAPITAL face toate literele cu majuscule, adică le transformă în majuscule. Precum și LOWERLINE, nu înlocuiește caractere care nu sunt litere.

CORECT

Funcția text CORECT scrie cu majuscule prima literă a fiecărui cuvânt și transformă toate celelalte în litere mici.

Fiecare primă literă care urmează unui caracter fără literă este, de asemenea, convertită în majuscule.

DLSTR

Stânga și dreapta

Funcții text STÂNGAși DREAPTA returnează numărul de caractere dat, începând de la începutul sau de la sfârșitul șirului. Un spațiu contează ca un caracter.

PSTR

Funcția text PSTR returnează numărul specificat de caractere, începând de la poziția specificată. Un spațiu contează ca un caracter.

CORECT

Funcţie CORECT vă permite să comparați două șiruri de text în Excel. Dacă se potrivesc exact, atunci este returnat TRUE; în caz contrar, FALSE. Această funcție de text ține cont de majuscule, dar ignoră diferențele de formatare.

Dacă cazul nu joacă un rol important pentru dvs. (se întâmplă în cele mai multe cazuri), atunci puteți aplica o formulă care pur și simplu verifică egalitatea a două celule.

TUNDE

Elimină toate spațiile suplimentare din text, cu excepția spațiilor unice dintre cuvinte.

În cazurile în care prezența unui spațiu suplimentar la sfârșitul sau începutul unei linii este dificil de urmărit, această funcție devine pur și simplu indispensabilă. Figura de mai jos arată că conținutul celulelor A1 și B1 este exact același, dar nu este așa. În celula A1, punem în mod deliberat un spațiu suplimentar la sfârșitul cuvântului excela. Ca urmare, funcția CORECT a returnat valoarea FALSE.

Prin aplicarea funcției TUNDE la valoarea celulei A1, vom elimina toate spațiile suplimentare din ea și vom obține rezultatul corect:

Funcţie TUNDE util de aplicat datelor care sunt importate în foile de lucru Excel din surse externe. Asemenea date conțin foarte adesea spații suplimentare și diverse caractere care nu pot fi imprimate. Pentru a elimina toate caracterele care nu pot fi imprimate din text, trebuie să utilizați funcția IMPRIMARE.

REPETA

Funcţie REPETA repetă un șir de text de numărul specificat de ori. Șirul este dat ca prim argument al funcției, iar numărul de repetări ca al doilea.

GĂSI

Funcția text GĂSI găsește apariția unui șir în altul și returnează poziția primului caracter al frazei de căutare relativ la începutul textului.

Această funcție ține cont de majuscule și minuscule...

... și poate începe vizualizarea textului din poziția specificată. În figura de mai jos, formula începe scanarea de la al patrulea caracter, adică. litera c „ r„. Dar chiar și în acest caz, poziția personajului este considerată relativ la începutul textului care este vizualizat.

CĂUTARE

Funcția text CĂUTARE foarte asemănătoare cu funcția GĂSI, principala lor diferență este că CĂUTARE insensibil la majuscule.

SUBSTITUI

Înlocuiește textul sau caracterul specificat cu valoarea dorită. În funcția text Excel SUBSTITUI sunt utilizate atunci când se știe dinainte ce text trebuie înlocuit, și nu locația acestuia.

Următoarea formulă înlocuiește toate aparițiile cuvântului „Excel” cu „Word”:

Înlocuiește doar prima apariție a cuvântului „Excel”:

Elimină toate spațiile dintr-un șir de text:

A INLOCUI

Înlocuiește caracterele situate într-o locație cunoscută dintr-un șir cu valoarea dorită. În funcția text Excel A INLOCUI sunt folosite atunci când se știe unde se află textul, în timp ce el însuși nu este important.

Formula din exemplul de mai jos înlocuiește 4 caractere începând cu a șaptea poziție cu valoarea „2013”. Pentru exemplul nostru, formula va înlocui „2010” cu „2013”.

Înlocuiește primele cinci caractere ale unui șir de text, adică cuvântul „Excel” la „Word”.

Asta e tot! Ne-am familiarizat cu 15 funcții de text ale Microsoft Excel și ne-am uitat la acțiunea lor pe exemple simple. Sper că această lecție ți-a fost de folos și că ai obținut măcar puține informații utile din ea. Mult succes si succes in invatarea Excel!

Destul de des, atunci când lucrați în Excel, este necesar să introduceți text explicativ lângă rezultatul calculării unei formule, ceea ce face mai ușor de înțeles aceste date. Desigur, puteți selecta o coloană separată pentru explicații, dar nu în toate cazurile, adăugarea de elemente suplimentare este rațională. Cu toate acestea, în Excel există modalități de a pune împreună formula și textul în aceeași celulă. Să vedem cum se poate face acest lucru folosind diferite opțiuni.

Dacă încercați doar să inserați text într-o celulă cu o funcție, atunci cu o astfel de încercare, Excel va afișa un mesaj de eroare în formulă și nu vă va permite să faceți o astfel de inserare. Dar există două moduri de a introduce text lângă expresia formulei. Primul este să folosești ampersand, iar al doilea este să folosești funcția CONECTAȚI.

Metoda 1: Folosirea unui ampersand

Cel mai simplu mod de a rezolva această problemă este să folosiți simbolul și ( & ). Acest semn produce o separare logică a datelor pe care le conține formula de expresia textului. Să vedem cum se poate aplica această metodă în practică.

Avem un mic tabel în care costurile fixe și variabile ale întreprinderii sunt indicate în două coloane. A treia coloană conține o formulă simplă de adunare care le însumează și generează un total general. Trebuie să adăugăm un cuvânt explicativ după formula în aceeași celulă în care este afișat costul total "ruble".

Desigur, nu este necesar să faceți toți acești pași. Tocmai am arătat că, cu o introducere normală fără un al doilea ampersand și ghilimele cu un spațiu, formula și datele text se vor îmbina. Puteți seta spațiul corect chiar și atunci când efectuați al doilea paragraf al acestui ghid.

Când scriem text înaintea unei formule, respectăm următoarea sintaxă. Imediat după semnul „=", deschideți ghilimele și scrieți textul. După aceea, închideți ghilimele. Punem semnul ampersand. Apoi, dacă trebuie să introduceți un spațiu, deschideți ghilimele, puneți un spațiu și închideți ghilimele. Facem clic pe cheie introduce.

Pentru a scrie text împreună cu o funcție, și nu cu o formulă obișnuită, toți pașii sunt exact la fel ca cei descriși mai sus.

Metoda 2: Utilizarea funcției CONCATENATE

De asemenea, puteți utiliza funcția pentru a insera text împreună cu rezultatul calculării formulei CONECTAȚI. Acest operator este conceput pentru a combina într-o singură celulă valorile afișate în mai multe elemente ale foii. Aparține categoriei de funcții text. Sintaxa sa este următoarea:

CONCATENATE(text1, text2, ...)

În total, acest operator poate avea de la 1 inainte de 255 argumente. Fiecare reprezintă fie text (inclusiv numere și orice alte caractere) sau link-uri către celulele care îl conțin.

Să vedem cum funcționează această funcție în practică. De exemplu, să luăm același tabel, doar să mai adăugăm o coloană la el "Cost total" cu o celulă goală.

1. Selectați o celulă de coloană goală "Cost total". Faceți clic pe pictogramă „Inserare funcție” situat în stânga barei de formule.



2. Activare în curs Vrăjitorii de funcții. Trecerea la categorie "Text". Apoi, selectați numele "CONECTAȚI"și faceți clic pe butonul O.K.



3. Se lansează fereastra Argumente ale operatorului CONECTAȚI. Această fereastră constă din câmpuri numite "Text". Numărul lor ajunge 255 , dar pentru exemplul nostru, avem nevoie doar de trei câmpuri. În prima vom plasa textul, în a doua vom plasa un link către celula care conține formula, iar în a treia vom plasa din nou textul.

   Setați cursorul în câmp „Text1”. Pune în cuvânt "Total". Puteți scrie expresii text fără ghilimele, deoarece programul le va pune singur.

   Apoi mergem la câmp „Text2”. Setați cursorul acolo. Trebuie să indicăm aici valoarea pe care o afișează formula, ceea ce înseamnă că ar trebui să dăm un link către celula care o conține. Acest lucru se poate face prin simpla introducere manuală a adresei, dar este mai bine să plasați cursorul în câmp și să faceți clic pe celula care conține formula de pe foaie. Adresa va fi afișată automat în caseta de argumente.

   În câmp „Text3” introduceți cuvântul „ruble”.

   După aceea, faceți clic pe butonul O.K.



4. Rezultatul este afișat într-o celulă preselectată, dar, după cum putem vedea, ca și în metoda anterioară, toate valorile sunt scrise împreună fără spații.



5. Pentru a rezolva această problemă, selectați din nou celula care conține operatorul CONECTAȚIși accesați bara de formule. Acolo, după fiecare argument, adică după fiecare punct și virgulă, adăugați următoarea expresie:

   Trebuie să existe un spațiu între ghilimele. În general, următoarea expresie ar trebui să apară în linia funcției:

   CONCATENATE("Total";" ";D2;" ";"ruble")

   Facem clic pe cheie INTRODUCE. Acum valorile noastre sunt separate prin spații.



6. Puteți ascunde prima coloană dacă doriți. "Cost total" cu formula originală astfel încât să nu ocupe spațiu suplimentar pe foaie. Doar eliminarea acestuia nu va funcționa, deoarece aceasta va rupe funcția CONECTAȚI, dar este foarte posibil să eliminați elementul. Faceți clic pe butonul stâng al mouse-ului pe sectorul barei de coordonate a coloanei care ar trebui să fie ascunsă. După aceea, întreaga coloană este evidențiată. Faceți clic pe selecție cu butonul din dreapta al mouse-ului. Este lansat meniul contextual. Selectați un element din el. "Ascunde".



7. După aceea, după cum vedem, coloana de care nu avem nevoie este ascunsă, ci datele din celula în care se află funcția CONECTAȚI afisat corect.

Excel oferă utilizatorilor săi până la 3 funcții pentru a lucra cu litere mari și mici în text (majuscule și minuscule). Aceste funcții de text fac litere mari și mici sau schimbă doar prima literă dintr-un cuvânt în majuscule.

Formule cu funcții de text Excel

Mai întâi, să luăm un exemplu de 3 funcții text Excel:

1. UPPER - Această funcție de text schimbă toate literele dintr-un cuvânt în majuscule, majuscule.
2. LOWER - Această funcție convertește toate caracterele textului în litere mici, mici.
3. PROPER - funcția schimbă doar prima literă din fiecare cuvânt în majusculă, mare.

După cum puteți vedea în exemplul din figură, aceste funcții în argumentele lor nu necesită altceva decât datele text originale, care ar trebui convertite în conformitate cu cerințele utilizatorului.

În ciuda unei game atât de largi de funcții, Excel are totuși nevoie de o funcție care poate scrie prima literă cu majuscule doar pentru primul cuvânt dintr-o propoziție și nu în fiecare cuvânt. Cu toate acestea, pentru a rezolva această problemă, vă puteți crea propria formulă personalizată folosind aceleași și alte funcții de text Excel:

Pentru a rezolva această problemă populară, trebuie să utilizați funcții suplimentare de text Excel în formula: STÂNGA, DREAPTA și LUNG.

Principiul formulei de înlocuire a primei litere dintr-o propoziție

Dacă te uiți îndeaproape la sintaxa formulei de mai sus, este ușor de înlocuit faptul că aceasta constă din două părți conectate prin operatorul &.

Partea stângă a formulei folosește funcția suplimentară LEFT:

Sarcina acestei părți a formulei este de a schimba prima literă cu majuscule din șirul de text original al celulei A1. Datorită funcției LEFT, puteți obține un anumit număr de caractere începând din partea stângă a textului. Funcția necesită completarea a 2 argumente:

1. Text - un link către o celulă cu textul original.
2. Number of_characters - numărul de caractere returnate din partea stângă (de la început) a textului sursă.

În acest exemplu, trebuie să obțineți doar primul caracter din șirul de text original din celula A1. Caracterul rezultat este apoi convertit într-o literă majusculă.

Partea dreaptă a formulei după operatorul & este foarte asemănătoare în principiu cu partea stângă, doar că rezolvă o problemă diferită. Sarcina sa este de a converti toate caracterele textului în litere mici. Dar trebuie să faceți acest lucru pentru a nu schimba prima literă majusculă, pentru care partea stângă a formulei este responsabilă. În loc de funcția STÂNGA din partea dreaptă a formulei, este utilizată funcția DREAPTA:

Funcția text RIGHT funcționează invers cu funcția LEFT. De asemenea, necesită ștergerea în praf a două argumente: textul sursă și numărul de caractere. Dar returnează un anumit număr de litere primite din partea dreaptă a textului sursă. Cu toate acestea, în acest caz, nu putem specifica o valoare fixă ​​ca al doilea argument. La urma urmei, nu știm dinainte numărul de caractere din textul sursă. În plus, lungimea diferitelor șiruri de text sursă poate diferi. Prin urmare, trebuie să precalculăm lungimea liniei de text și să scădem -1 din valoarea numerică rezultată, pentru a nu schimba prima literă majusculă din linie. La urma urmei, prima literă este procesată de partea stângă a formulei și a fost deja convertită la cerințele utilizatorului. Prin urmare, nu ar trebui să fie afectat de nicio funcție din partea dreaptă a formulei.

Pentru a calcula automat lungimea textului sursă, se folosește funcția de text Excel - DLSTR (descifrată ca lungime a șirului). Această funcție necesită un singur argument pentru a completa - un link către textul sursă. Ca rezultat al calculului, returnează o valoare numerică, după funcția =LSTR(A1) scadem -1. Ceea ce ne oferă posibilitatea de a nu afecta prima literă majusculă cu partea dreaptă a formulei. Ca rezultat, funcția DREAPTA returnează un șir de text fără un prim caracter pentru funcția LOWER, care schimbă toate caracterele text în litere mici mici.

Ca urmare a combinării ambelor părți ale formulei cu operatorul &, obținem o propoziție text frumoasă, care, conform regulilor, începe cu prima literă majusculă. Și toate celelalte litere sunt mici până la sfârșitul propoziției. Indiferent de lungimea textului, folosind aceeași formulă, obținem rezultatul corect.