Teksta funkcijas Excel formulās: lielie, mazie un lielie burti. Teksta funkciju piemēri programmā Excel Jebkura teksta vērtība programmā Excel

Vārdi, bankas karšu numuri, klientu vai darbinieku adreses, komentāri un daudz kas cits – tās visas ir virknes, ar kurām saskaras daudzi cilvēki, strādājot ar programmu Excel. Tāpēc ir lietderīgi prast apstrādāt šāda veida informāciju. Šajā rakstā tiks aplūkotas teksta funkcijas programmā Excel, bet ne visas, bet tās, kuras, pēc vietnes domām, ir visnoderīgākās un interesantākās:

Visu teksta funkciju sarakstu varat atrast cilnē "Formulas" => "Teksts" nolaižamajā sarakstā:

LEFT funkcija

Atgriež apakšvirkni no teksta no kreisās puses uz labo norādītajā rakstzīmju skaitā.

Sintakse:=LEFT(teksts, [rakstzīmju_skaits])

Argumentu definīcijas:

• teksts - virkne vai atsauce uz šūnu, kurā ir teksts, no kura vēlaties atgriezt apakšvirkni;
• rakstzīmju_skaits ir neobligāts arguments. Vesels skaitlis, kas norāda, cik rakstzīmju jāatgriež no teksta. Noklusējuma vērtība ir 1.

Lietošanas piemērs:

Formula: =LEFTCHAR("Pielāgots teksts";8) - atgrieztā vērtība "Pielāgots".

PAREIZĀ funkcija

Šī funkcija ir līdzīga funkcijai LEFT, izņemot to, ka rakstzīmes tiek atgrieztas no virknes beigām.

Lietošanas piemērs:

Formula: =RIGHT("patvaļīgs teksts";5) – atgrieztā vērtība ir "teksts".

DLSTR funkcija

To izmanto, lai noteiktu virknes garumu. Rezultāts ir vesels skaitlis, kas norāda rakstzīmju skaitu tekstā.

Sintakse:=DLSTR(teksts)

Lietošanas piemērs:

FIND funkcija

Atgriež skaitli, kas ir meklētās apakšvirknes pirmās rakstzīmes sastopamība. Ja teksts netiek atrasts, tiek atgriezta kļūda “#VALUE!”.

Sintakse:=ATRAST(meklēšanas_teksts, meklēšanas_teksts, [sākuma_pozīcija])

Argumentu definīcijas:

• search_text - virkne, kas jāatrod;
• search_text – teksts, kurā tiek veikta pirmā argumenta meklēšana;
• start_position ir neobligāts elements. Pieņem veselu skaitli, kas norāda, no kuras meklēšanas_teksta rakstzīmes jāsāk pārlūkošana. Noklusējuma vērtība ir 1.

Lietošanas piemērs:

No izcilā dzejnieka S. A. Jeseņina dzejoļa fragmenta mēs atrodam rindas "putns" pirmā varoņa rašanos. Meklēšana tiek veikta no virknes sākuma. Ja iepriekš minētajā piemērā meklēšana tiktu veikta no 40. rakstzīmes, tad funkcija atgrieztu kļūdu, jo ieejas pozīcija netika atrasta.

REPLACE funkcija

Šī funkcija aizstāj daļu no virknes norādītajā rakstzīmju skaitā, sākot ar norādīto rakstzīmju skaitu, ar jaunu tekstu.

Sintakse: REPLACE(vecais_teksts, sākuma_pozīcija, rakstzīmju_skaits, jaunais_teksts)

Argumentu definīcijas:

• old_text - virkne vai atsauce uz šūnu, kurā ir teksts;
• sākuma_pozīcija - rakstzīmes sērijas numurs no kreisās puses uz labo, no kuras vēlaties aizstāt;
• rakstzīmju_skaits – zīmju skaits, sākot no sākuma_pozīcijas ieskaitot, kuras jāaizstāj ar jaunu tekstu;
• jaunais_teksts ir virkne, kas aizstāj daļu no vecā teksta, ko nosaka argumenti sākuma_pozīcija un rakstzīmju_skaits.

Lietošanas piemērs:

Šeit šūnā A1 ietvertajā virknē vārds "vecais", kas sākas ar 19. rakstzīmi un ir 6 rakstzīmes garš, tiek aizstāts ar vārdu "jauns".

Ja tiek apstrādāts neliels šūnu skaits, šī metode ir diezgan pieņemama. Bet, ja jums ir jāapstrādā vairāki tūkstoši dažādu rindu, tad formulu sastādīšanas process kļūs darbietilpīgs. Tāpēc mēs atkārtosim aplūkoto piemēru, izmantojot zināšanas par tām funkcijām, kas jau ir aprakstītas raksta sākumā, proti:

• Arguments "sākotnējā_pozīcija" tiks aizstāts ar funkciju "FIND";
• Argumenta "rakstzīmju_skaits" vietā ievietojam funkciju "DLSTR".

Rezultātā mēs iegūstam formulu: =REPLACE(A1; ATRAST("vecais",A1);DLSTR("vecais");"jauns")

Apskatiet funkciju loga printscreen. Atgrieztie argumentu rezultāti nav mainījušies, taču tagad tos var dinamiski noteikt, vienreiz sastādot formulu.

SUBSTITUTE funkcija

Šī funkcija aizstāj norādītās apakšvirknes gadījumus tekstā ar jaunu tekstu, kas ir līdzīgs funkcijai REPLACE, taču starp tām ir būtiska atšķirība. Ja funkcija "REPLACE" manuāli maina norādīto tekstu pēc rakstzīmes, tad funkcija "SUBSTITUTE" automātiski atrod norādītās virknes atkārtojumus un tos maina.

Sintakse: SUBSTITUTE(teksts; vecais_teksts; jauns_teksts; [ieraksta_numurs])

Argumentu definīcijas:

• old_text ir apakšvirkne no pirmā argumenta, kas jāaizstāj;
• new_text - virkne, lai aizstātu veco tekstu;
• ieraksta_skaitlis ir neobligāts arguments. Pieņem veselu skaitli, kas norāda aizvietojamā gadījuma old_text kārtu, visi pārējie gadījumi netiks ietekmēti. Atstājot argumentu tukšu, tiks aizstāti visi gadījumi.

Lietošanas piemērs:

Šūnas A1 virknē ir teksts, kurā ir 2 apakšvirknes "veca". Mums ir jāaizstāj virkne "jauns" pirmās gadījuma vietā. Rezultātā daļa teksta "... vecs-vecs ..." tiek aizstāts ar "... jauns-vecs ...".

Ja pēdējais arguments tiktu izlaists, rezultāts būtu "virkne, kas satur jaunu-jaunu tekstu".

MID funkcija

PSTR atgriež teksta daļu noteiktā rakstzīmju skaitā no norādītās virknes, sākot no norādītās rakstzīmes.

Sintakse: MID(teksts; sākuma_pozīcija; rakstzīmju_skaits)

Argumentu definīcijas:

• teksts - virkne vai atsauce uz šūnu, kurā ir teksts;
• sākuma_pozīcija - rakstzīmes kārtas numurs, no kuras vēlaties atgriezt virkni;
• num_chars ir dabisks vesels skaitlis, kas norāda atgriežamo rakstzīmju skaitu, sākot no pozīcijas start_position.

Lietošanas piemērs:

No teksta šūnā A1 ir jāatgriež pēdējie 2 vārdi, kuru kopējais garums ir 12 rakstzīmes. Atgrieztās frāzes pirmajai rakstzīmei ir kārtas numurs 12.

Arguments number_of_chars var pārsniegt atļauto atgriezto rakstzīmju garumu. Tie. ja aplūkotajā piemērā rakstzīmju_skaits = 12 vietā būtu norādīta vērtība 15, tad rezultāts nav mainījies, un funkcija arī atgrieza virkni “MID function”.

Šīs funkcijas izmantošanas ērtībai tās argumentus var aizstāt ar FIND un DLSTR funkcijām, kā tas tika darīts piemērā ar funkciju REPLACE.

TRIM funkcija

Šī funkcija noņem visas papildu atstarpes: atstarpes ap malām un dubultatstarpes starp vārdiem. Pēc virkņu apstrādes ar funkciju starp vārdiem paliek tikai atsevišķas atstarpes.

Sintakse:=TRIM(teksts)

Lietošanas piemērs:

TRIM( "Teksts ar papildu atstarpēm starp vārdiem un malās" )

Funkcijas izpildes rezultāts būs virkne: "Teksts ar papildu atstarpēm starp vārdiem un ap malām" .

Funkcija CONCATENATE

Izmantojot funkciju "CONCATENATE", varat apvienot vairākas rindas. Maksimālais sapludināmo rindu skaits ir 255.

Sintakse:=Savienot(teksts1, [teksts2], ...)

Funkcijā ir jābūt vismaz vienam argumentam

Šeit ir visbiežāk izmantotās Excel teksta funkcijas (ātrā uzziņa). Papildinformāciju par funkcijām var atrast dialoglodziņā Function Wizard, kā arī Excel palīdzības sistēma.

Teksta funkcijas pārvērš skaitliskās teksta vērtības par skaitļiem un skaitliskās vērtības rakstzīmju virknēs (teksta virknēs), kā arī ļauj veikt dažādas darbības ar rakstzīmju virknēm.

TEXT funkcija

Funkcija TEXT konvertē skaitli teksta virknē ar norādīto formātu. Sintakse:

=TEKSTS(vērtība, formāts)

Vērtības arguments var būt jebkurš skaitlis, formula vai šūnas atsauce. Formāta arguments nosaka, kā tiek parādīta atgrieztā virkne. Lai norādītu vēlamo formātu, varat izmantot jebkuru no formatēšanas rakstzīmēm, izņemot zvaigznīti. Vispārīgā formāta izmantošana nav atļauta. Piemēram, šī formula atgriež teksta virkni 25.25:

=TEKSTS(101/4"0,00")

RUBLE funkcija

Funkcija RUBLE (DOLĀRS) pārvērš skaitli virknē. Tomēr RUBLE atgriež valūtas virkni ar norādīto zīmju skaitu aiz komata. Sintakse:

=RUBLIS(skaitlis,_ciparu_skaits)

Šajā gadījumā programma Excel noapaļo skaitli, ja nepieciešams. Ja arguments num_chars ir izlaists, programmā Excel tiek izmantotas divas zīmes aiz komata, un, ja šī argumenta vērtība ir negatīva, atgriešanas vērtība tiek noapaļota pa kreisi no decimāldaļas.

DLSTR funkcija

Funkcija LEN (LEN) atgriež rakstzīmju skaitu teksta virknē, un tai ir šāda sintakse:

=DLSTR(teksts)

Teksta argumentam ir jābūt rakstzīmju virknei, kas ievietota dubultpēdiņās, vai šūnas atsaucei. Piemēram, šī formula atgriež vērtību 6:

=DLSTR("galva")

Funkcija DLSTR atgriež parādītā teksta vai vērtības garumu, nevis šūnas saglabāto vērtību. Tas arī ignorē sākuma nulles.

CHAR un CODE CHAR funkcija

Jebkurš dators izmanto ciparu kodus, lai attēlotu rakstzīmes. Visizplatītākā rakstzīmju kodēšanas sistēma ir ASCII. Šajā sistēmā ciparus, burtus un citus simbolus apzīmē ar cipariem no 0 līdz 127 (255). Funkcijas CHAR un CODE nodarbojas ar ASCII kodiem. Funkcija CHAR atgriež rakstzīmi, kas atbilst norādītajam ASCII ciparu kodam, un funkcija CODE atgriež ASCII kodu sava argumenta pirmajai rakstzīmei. Funkciju sintakse:

=CHAR(skaitlis)
=KODS(teksts)

Ja rakstzīmi ievadāt kā teksta argumentu, noteikti ievietojiet to pēdiņās, pretējā gadījumā programma Excel atgriezīs kļūdainu vērtību.

TRIM un CLEAN funkcijas

Bieži vien sākuma un beigu atstarpes neļauj pareizi sakārtot vērtības darblapā vai datubāzē. Ja izmantojat teksta funkcijas, lai strādātu ar darblapu tekstiem, papildu atstarpes var kavēt formulu pareizu darbību. Funkcija TRIM noņem virknes sākuma un beigu atstarpes, atstājot tikai vienu atstarpi starp vārdiem. Sintakse:

=TRIM(teksts)

Funkcija CLEAN ir līdzīga funkcijai TRIM, izņemot to, ka tā noņem visas nedrukājamas rakstzīmes. Funkcija PRINT ir īpaši noderīga, importējot datus no citām programmām, jo ​​dažas importētās vērtības var saturēt nedrukājamas rakstzīmes. Šīs rakstzīmes var parādīties darblapās kā mazi kvadrāti vai vertikālas līnijas. Funkcija CLEAN ļauj no šādiem datiem noņemt nedrukājamas rakstzīmes. Sintakse:

=DRUKĀT(teksts)

precīza funkcija

Funkcija EXACT salīdzina divas teksta rindiņas, lai iegūtu pilnīgu identitāti, reģistrjutīga. Formatēšanas atšķirība tiek ignorēta. Sintakse:

=PRECĪZI(teksts1, teksts2)

Ja argumenti teksts1 Un teksts2 identiska attiecībā uz lielo burtu, funkcija atgriež TRUE, pretējā gadījumā FALSE. Argumenti teksts1 Un teksts2 jābūt rakstzīmju virknēm, kas ievietotas dubultpēdiņās, vai šūnu atsaucēm, kas satur tekstu.

UPPER, LOWER un PROPER funkcijas

Programmai Excel ir trīs funkcijas, kas ļauj mainīt burtu reģistrus teksta virknēs: AUGŠĀ, APAKS un PAreizā. Funkcija UPPER pārvērš visus burtus teksta virknē par lielajiem burtiem un LOWER par mazajiem burtiem. Funkcija PROPER katra vārda pirmo burtu raksta ar lielo burtu un visus burtus, kas seko burtiem, kas nav burti; visi pārējie burti tiek pārveidoti par mazajiem burtiem. Šīm funkcijām ir šāda sintakse:

=AUGŠĒJĀ(teksts)
=APAKS (teksts)
=ĪPAŠS(teksts)

Strādājot ar jau esošiem datiem, bieži rodas situācija, kad pašiem ir jāmaina sākotnējās vērtības, kurām tiek piemērotas teksta funkcijas. Varat ievadīt funkciju tajās pašās šūnās, kur atrodas šīs vērtības, jo ievadītās formulas tās aizstās. Bet jūs varat izveidot pagaidu formulas ar teksta funkciju brīvās šūnās tajā pašā rindā un kopēt rezultātu starpliktuvē. Lai aizstātu sākotnējās vērtības ar modificētajām vērtībām, atlasiet sākotnējās teksta šūnas, izvēlnē Rediģēt izvēlieties Īpašā ielīmēšana, atlasiet radio pogu Vērtības un pēc tam noklikšķiniet uz Labi. Pēc tam varat dzēst pagaidu formulas.

Excel ir nepieciešams gadījumos, kad nepieciešams sakārtot, apstrādāt un saglabāt daudz informācijas. Tas palīdzēs automatizēt aprēķinus, padarīs tos vieglākus un uzticamākus. Formulas programmā Excel ļauj veikt patvaļīgi sarežģītus aprēķinus un uzreiz iegūt rezultātus.

Kā uzrakstīt formulu programmā Excel

Pirms to iemācīties, jums vajadzētu saprast dažus pamatprincipus.

1. Katrs sākas ar zīmi "=".
2. Šūnu un funkciju vērtības var piedalīties aprēķinos.
3. Operatori tiek izmantoti kā mums pazīstamas operāciju matemātiskās zīmes.
4. Ievietojot ierakstu, noklusējuma šūna atspoguļo aprēķina rezultātu.
5. Dizainu var redzēt rindā virs tabulas.

Katra Excel šūna ir nedalāma vienība ar savu identifikatoru (adresi), kas tiek apzīmēta ar burtu (kolonnas numurs) un skaitli (rindas numurs). Adrese tiek parādīta laukā virs tabulas.

Tātad, kā izveidot un ievietot formulu programmā Excel? Rīkojieties saskaņā ar šādu algoritmu:

Apzīmējuma nozīme

Papildinājums
- Atņemšana
/ Divīzija
* Reizināšana

Ja jums ir jānorāda numurs, nevis šūnas adrese, ievadiet to no tastatūras. Lai Excel formulā ievadītu negatīvo zīmi, nospiediet "-".

Kā ievadīt un kopēt formulas programmā Excel

Tie vienmēr tiek ievadīti pēc "="" nospiešanas. Bet ko darīt, ja ir daudz līdzīgu aprēķinu? Šajā gadījumā varat norādīt vienu un pēc tam to vienkārši kopēt. Lai to izdarītu, ievadiet formulu un pēc tam "izstiepiet" to pareizajā virzienā, lai reizinātu.
Novietojiet rādītāju uz kopēto šūnu un pārvietojiet peles rādītāju uz apakšējo labo stūri (kvadrātiņā). Tam vajadzētu būt vienkārša krusta formā ar vienādām malām.


Nospiediet kreiso pogu un velciet.


Atlaidiet, kad vēlaties pārtraukt kopēšanu. Šajā brīdī parādīsies aprēķinu rezultāti.


Varat arī izstiepties pa labi.
Pārvietojiet rādītāju uz nākamo šūnu. Jūs redzēsit to pašu ierakstu, bet ar dažādām adresēm.


Kopējot šādā veidā, rindu numuri palielinās, ja nobīde ir uz leju, vai kolonnu numuri palielinās, ja pa labi. To sauc par relatīvo adresāciju.
Ievadīsim tabulā PVN vērtību un aprēķināsim cenu ar nodokli.


Cena ar PVN tiek aprēķināta kā cena*(1+PVN). Pirmajā šūnā ievadiet secību.


Mēģināsim nokopēt ierakstu.


Rezultāts dīvains.


Pārbaudīsim saturu otrajā šūnā.


Kā redzams, kopējot nobīdījās ne tikai cena, bet arī PVN. Un mums ir nepieciešams, lai šī šūna paliktu fiksēta. Labojiet to ar absolūtu saiti. Lai to izdarītu, pārvietojiet rādītāju uz pirmo šūnu un formulas joslā noklikšķiniet uz adreses B2.


Nospiediet F4. Adrese tiks atšķaidīta ar "$" zīmi. Šī ir absolūti šūnas zīme.


Tagad pēc kopēšanas adrese B2 paliks nemainīga.
Ja nejauši ievadījāt datus nepareizajā šūnā, vienkārši pārsūtiet tos. Lai to izdarītu, pārvietojiet peles rādītāju virs jebkuras apmales, pagaidiet, līdz pele izskatās kā krusts ar bultiņām, nospiediet kreiso pogu un velciet. Pareizajā vietā vienkārši atlaidiet manipulatoru.

Funkciju izmantošana aprēķiniem

Excel piedāvā lielu skaitu funkciju, kas ir iedalītas kategorijās. Pilnu sarakstu var apskatīt, noklikšķinot uz pogas Fx blakus formulu joslai vai atverot rīkjoslas sadaļu "Formulas".


Parunāsim par dažām funkcijām.

Kā programmā Excel iestatīt "Ja" formulas

Šī funkcija ļauj iestatīt nosacījumu un veikt aprēķinu atkarībā no tā, vai tas ir patiess vai nepatiess. Piemēram, ja pārdotais daudzums pārsniedz 4 iepakojumus, jāiegādājas vairāk.
Lai ievietotu rezultātu atkarībā no nosacījuma, pievienosim tabulai vēl vienu kolonnu.


Pirmajā šūnā zem šīs kolonnas virsraksta iestatiet rādītāju un rīkjoslā noklikšķiniet uz vienuma "Loģiski". Atlasīsim funkciju "Ja".


Tāpat kā jebkuras funkcijas ievietošanas gadījumā, tiks atvērts logs, kurā tiks aizpildīti argumenti.


Norādīsim nosacījumu. Lai to izdarītu, noklikšķiniet uz pirmās rindas un atlasiet pirmo šūnu "Pārdots". Pēc tam ielieciet zīmi ">" un norādiet skaitli 4.


Otrajā rindā rakstīsim "Pirkums". Šis uzraksts parādīsies tiem produktiem, kas ir izpārdoti. Pēdējo rindiņu var atstāt tukšu, jo mums nav nekādas darbības, ja nosacījums ir nepatiess.


Noklikšķiniet uz Labi un kopējiet ierakstu visai kolonnai.


Lai šūnā netiktu parādīts "FALSE", vēlreiz atveriet funkciju un izlabojiet to. Novietojiet rādītāju pirmajā šūnā un nospiediet Fx blakus formulas joslai. Ievietojiet kursoru trešajā rindā un ievietojiet atstarpi starp pēdiņām.


Pēc tam Labi un kopējiet vēlreiz.


Tagad mēs redzam, kurš produkts ir jāiegādājas.

formulas teksts programmā Excel

Šis līdzeklis ļauj lietot formātu šūnas saturam. Šajā gadījumā jebkurš datu tips tiek pārveidots par tekstu, un tāpēc to nevar izmantot turpmākiem aprēķiniem. Pievienosim kolonnu, lai formatētu kopējo summu.


Pirmajā šūnā ievadiet funkciju (poga "Teksts" sadaļā "Formulas").


Argumentu logā norādiet saiti uz kopējās summas šūnu un iestatiet formātu uz "#RUB".


Noklikšķiniet uz Labi un kopējiet.


Ja mēģināsim izmantot šo summu aprēķinos, mēs saņemsim kļūdas ziņojumu.

"VĒRTĪBA" nozīmē, ka aprēķinus nevar veikt.
Ekrānuzņēmumā varat redzēt formātu piemērus.

Datuma formula programmā Excel

Programmā Excel ir pieejamas daudzas iespējas darbam ar datumiem. Viens no tiem, DATE, ļauj izveidot datumu no trim skaitļiem. Tas ir noderīgi, ja jums ir trīs dažādas kolonnas — diena, mēnesis, gads.

Novietojiet rādītāju ceturtās kolonnas pirmajā šūnā un sarakstā "Datums un laiks" atlasiet funkciju.

Attiecīgi sakārtojiet šūnu adreses un noklikšķiniet uz Labi.


Kopējiet ierakstu.

Automātiskā summa programmā Excel

Ja nepieciešams pievienot lielu datu apjomu, programma Excel nodrošina SUM funkciju. Piemēram, aprēķināsim summu par pārdotajām precēm.
Novietojiet rādītāju šūnā F12. Tas aprēķinās kopējo summu.


Dodieties uz paneli Formulas un noklikšķiniet uz AutoSum.


Programma Excel automātiski iezīmēs tuvāko ciparu diapazonu.


Varat izvēlēties citu diapazonu. Šajā piemērā Excel visu izdarīja pareizi. Noklikšķiniet uz Labi. Pievērsiet uzmanību šūnas saturam. Funkcija SUM tika aizstāta automātiski.


Ievietojot diapazonu, norādiet pirmās šūnas adresi, kolu un pēdējās šūnas adresi. ":" nozīmē "Paņemt visas šūnas starp pirmo un pēdējo. Ja nepieciešams uzskaitīt vairākas šūnas, atdaliet to adreses ar semikolu:
SUM (F5;F8;F11)

Darbs ar formulām programmā Excel: piemērs

Mēs teicām, kā programmā Excel izveidot formulu. Šīs ir zināšanas, kas var noderēt pat ikdienā. Jūs varat pārvaldīt savu personīgo budžetu un kontrolēt izdevumus.


Ekrānuzņēmumā ir redzamas formulas, kas tiek ievadītas, lai aprēķinātu ienākumu un izdevumu summas, kā arī mēneša beigu atlikuma aprēķinu. Pievienojiet katra mēneša darbgrāmatai lapas, ja nevēlaties, lai visas tabulas būtu uz viena galda. Lai to izdarītu, vienkārši noklikšķiniet uz "+" loga apakšā.

Lai pārdēvētu lapu, veiciet dubultklikšķi uz tās un ievadiet nosaukumu.

Tabulu var padarīt vēl detalizētāku.
Excel ir ļoti noderīga programma, un aprēķini tajā sniedz gandrīz neierobežotas iespējas.

Lai jums lieliska diena!

Bieži vien programmā Excel vienā vai otrā veidā ir jāapstrādā teksta virknes. Šādas darbības ir ļoti grūti veikt manuāli, ja rindu skaits pārsniedz simtu. Ērtības labad programmā Excel ir labs funkciju kopums darbam ar virknes datu kopu. Šajā rakstā es īsi aprakstīšu nepieciešamās funkcijas darbam ar kategorijas "Teksts" virknēm un aplūkošu dažas no tām ar piemēriem.

Funkcijas kategorijā "Teksts"

Tātad, apsveriet galvenās un noderīgās kategorijas "Teksts" funkcijas, jūs varat iepazīties ar pārējo.

• BATTEXT(Vērtība) - funkcija, kas pārvērš skaitli teksta veidā;
• DLSTR(Vērtība) ir palīgfunkcija, ļoti noderīga, strādājot ar virknēm. Atgriež virknes garumu, t.i. rindā ietverto rakstzīmju skaits;
• AIZVIETOT(Vecais teksts, Sākuma pozīcija, rakstzīmju skaits, jauns teksts) - aizstāj norādīto rakstzīmju skaitu no noteiktas pozīcijas vecajā tekstā uz jaunu;
• NOZĪMĪGI(Teksts) - pārvērš tekstu par skaitli;
• PA kreisi(String, Number of characters) - ļoti noderīga funkcija, atgriež norādīto rakstzīmju skaitu, sākot no pirmās rakstzīmes;
• PA LABI(Virka, Rakstzīmju skaits) - funkcijas analogs PA kreisi, ar vienīgo atšķirību, ka rakstzīmju atgriešana no virknes pēdējās rakstzīmes;
• ATRAST(meklējamais teksts, teksts, kurā meklējam, sākuma pozīcija) - funkcija atgriež pozīciju, no kuras sākas meklētā teksta rašanās. Rakstzīmes ir reģistrjutīgas. Ja jums ir nepieciešams ignorēt rakstzīmju reģistru, izmantojiet funkciju MEKLĒT. Tiek atgriezta tikai pirmā gadījuma pozīcija virknē!
• AIZSTĀTĀJS(teksts, vecs teksts, jauns teksts, pozīcija) - interesanta funkcija, no pirmā acu uzmetiena izskatās pēc funkcijas AIZVIETOT, bet funkcija AIZSTĀTĀJS spēj aizstāt visus gadījumus virknē ar jaunu apakšvirkni, ja tiek izlaists arguments "pozīcija";
• PSTR(Teksts, Sākuma pozīcija, Rakstzīmju skaits) - funkcija ir līdzīga PA kreisi, bet spēj atgriezt rakstzīmes no norādītās pozīcijas:
• SAVIENOTIES(Teksts1, Teksts 2 .... Teksts 30) – funkcija ļauj savienot līdz 30 līnijām. Varat arī izmantot " & ”, tas izskatīsies šādi "="Teksts1" & "Teksts2" & "Teksts3"";

Šīs ir galvenokārt bieži izmantotās funkcijas, strādājot ar virknēm. Tagad apskatīsim pāris piemērus, kas demonstrēs dažu funkciju darbību.

Piemērs 1
Dota rindu kopa:

No šīm rindām ir jāizņem datumi, rēķinu numuri, kā arī jāpievieno mēneša lauks, lai rindas filtrētu pēc mēneša.

Izvelciet rēķina numurus kolonnā B. Lai to izdarītu, mēs atrodam tā saukto atslēgas simbolu vai vārdu. Mūsu piemērā varat redzēt, ka pirms katra rēķina numura ir “#”, un rēķina numura garums ir 6 rakstzīmes. Izmantosim FIND un MID funkcijas. Šūnā B2 ierakstām šādu formulu:

= PSTR(A2; ATRAST("№";A2)+1;6)

Apskatīsim formulu. No rindas A2 no pozīcijas, kas atrodas aiz atrastās zīmes "Nē", izvelkam 6 skaitļa rakstzīmes.

Tagad izvilksim datumu. Šeit viss ir vienkārši. Datums atrodas rindas beigās, un tajā ir 8 rakstzīmes. C2 formula ir šāda:

= PA LABI(A2;8)

bet izvilktais datums būs virkne, lai to pārvērstu par datumu, ir nepieciešams pēc izvilkšanas, pārvērst tekstu par skaitli:

= NOZĪMĪGI(PA LABI(A2;8))

un pēc tam šūnā iestatiet displeja formātu, kā aprakstīts rakstā "".

Un visbeidzot rindu tālākas filtrēšanas ērtībai mēs ievadīsim mēneša kolonnu, kuru iegūsim no datuma. Tikai, lai izveidotu mēnesi, mums ir jāatmet diena un jāaizstāj ar "01". Formula D2:

= NOZĪMĪGI(SAVIENOTIES("01"; PA LABI(A2;6))) vai = NOZĪMĪGI("01"& PA LABI(A2;6))

Iestatiet šūnas formātu uz " MMMM GGGG". Rezultāts:

2. piemērs
Rindā " Piemērs darbam ar virknēm programmā Excel" nepieciešams aizstāt visas atstarpes ar zīmi "_", vienkārši pievienojiet "MS" pirms vārda "Excel".

Formula būs šāda:

=AIZSTĀTĀJS(AIZVIETOT(A1; MEKLĒT("excel";A1);0;"MS ");" ";"_")

Lai saprastu šo formulu, sadaliet to trīs kolonnās. Sāciet ar SEARCH, pēdējais būs AIZSTĀT.

Visi. Ja jums ir kādi jautājumi, droši jautājiet

Programma Excel saviem lietotājiem piedāvā pat 3 funkcijas darbam ar lielajiem un mazajiem burtiem tekstā (lielajiem un mazajiem burtiem). Šīs teksta funkcijas padara burtus lielus un mazus vai maina tikai vārda pirmo burtu uz lielo.

Formulas ar Excel teksta funkcijām

Vispirms ņemsim 3 Excel teksta funkciju piemēru:

1. AUPER — šī teksta funkcija maina visus vārda burtus uz lielajiem un lielajiem burtiem.
2. LOWER — šī funkcija pārvērš visas teksta rakstzīmes par mazajiem burtiem.
3. PROPER - funkcija maina tikai pirmo burtu katrā vārdā uz lielo, lielo.

Kā redzams piemērā attēlā, šīs funkcijas savos argumentos neprasa neko citu kā oriģinālos teksta datus, kas būtu jāpārveido atbilstoši lietotāja prasībām.

Neskatoties uz tik plašu funkciju klāstu, programmai Excel joprojām ir nepieciešama funkcija, kas var rakstīt pirmo burtu tikai teikuma pirmajam vārdam, nevis katrā vārdā. Tomēr, lai atrisinātu šo problēmu, varat izveidot savu pielāgoto formulu, izmantojot to pašu un citas Excel teksta funkcijas:

Lai atrisinātu šo populāro problēmu, formulā ir jāizmanto papildu Excel teksta funkcijas: LEFT, RIGHT un LONG.



Pirmā burta aizstāšanas formulas princips teikumā

Ja uzmanīgi aplūkojat iepriekš minētās formulas sintaksi, to ir viegli nomainīt, jo tā sastāv no divām daļām, kuras savieno operators &.

Formulas kreisajā pusē tiek izmantota papildu funkcija LEFT:

Šīs formulas daļas uzdevums ir mainīt pirmo burtu uz lielo šūnu A1 sākotnējā teksta virknē. Pateicoties funkcijai LEFT, jūs varat iegūt noteiktu skaitu rakstzīmju, sākot no teksta kreisās puses. Funkcijai ir jāaizpilda 2 argumenti:

1. Teksts - saite uz šūnu ar oriģinālo tekstu.
2. Number of_characters - atgriezto rakstzīmju skaits no avota teksta kreisās puses (no sākuma).

Šajā piemērā jums ir jāiegūst tikai pirmā 1 rakstzīme no sākotnējās teksta virknes šūnā A1. Pēc tam iegūtā rakstzīme tiek pārveidota par lielo lielo burtu.

Formulas labā puse pēc & operatora principā ir ļoti līdzīga kreisajai pusei, tikai tā atrisina citu problēmu. Tās uzdevums ir pārvērst visas teksta rakstzīmes mazos burtos. Bet jums tas jādara, lai nemainītu pirmo lielo burtu, par kuru ir atbildīga formulas kreisā puse. Funkcijas LEFT vietā formulas labajā pusē tiek izmantota funkcija RIGHT:

Funkcija RIGHT teksta darbojas apgriezti ar funkciju LEFT. Tas prasa arī notīrīt divus argumentus: avota tekstu un rakstzīmju skaitu. Bet tas atgriež noteiktu skaitu burtu, kas saņemti no avota teksta labās puses. Tomēr šajā gadījumā mēs nevaram norādīt fiksētu vērtību kā otro argumentu. Galu galā mēs iepriekš nezinām rakstzīmju skaitu avota tekstā. Turklāt dažādu avota teksta virkņu garums var atšķirties. Tāpēc mums ir iepriekš jāaprēķina teksta rindiņas garums un no iegūtās skaitliskās vērtības jāatņem -1, lai rindā netiktu mainīts pirmais lielais burts. Galu galā pirmais burts tiek apstrādāts formulas kreisajā pusē un jau ir pārveidots atbilstoši lietotāja prasībām. Tāpēc to nedrīkst ietekmēt neviena funkcija no formulas labās puses.

Lai automātiski aprēķinātu avota teksta garumu, tiek izmantota Excel teksta funkcija - DLSTR (atšifrēta kā virknes garums). Šīs funkcijas aizpildīšanai nepieciešams tikai viens arguments - saite uz avota tekstu. Aprēķina rezultātā tas atgriež skaitlisku vērtību, aiz funkcijas =LSTR(A1) atņemam -1. Kas dod mums iespēju ar formulas labo pusi neietekmēt pirmo lielo burtu. Rezultātā funkcija RIGHT atgriež teksta virkni bez vienas pirmās rakstzīmes funkcijai LOWER, kas visas teksta rakstzīmes maina uz maziem mazajiem burtiem.

Apvienojot abas formulas daļas ar operatoru &, mēs iegūstam skaistu teksta teikumu, kas saskaņā ar noteikumiem sākas ar pirmo lielo burtu. Un visi pārējie burti ir mazi līdz pat teikuma beigām. Neatkarīgi no teksta garuma, izmantojot to pašu formulu, mēs iegūstam pareizo rezultātu.