Какво представляват текстовите функции в excel. Работа с низове в Excel. Текстови функции на Excel. Оператор за конкатенация на текст

Често в Excel трябва да обработвате текстови низове по един или друг начин. Много е трудно да се извършват такива операции ръчно, когато броят на линиите е повече от сто. За удобство Excel е внедрил добър набор от функции за работа с набор от низови данни. В тази статия ще опиша накратко необходимите функции за работа с низове в категорията „Текст“ и ще разгледам някои от тях с примери.

Функции на категорията "Текст".

И така, нека да разгледаме основните и полезни функции на категорията „Текст“, можете да се запознаете с останалите.

BATTEXT(Стойност) – функция, която преобразува число в текстов вид;
DLST(Стойност) е помощна функция, много полезна при работа с низове. Връща дължината на низа, т.е. брой знаци, съдържащи се в реда;
ЗАМЕНИТЕ(Стар текст, Начална позиция, брой знаци, нов текст) – заменя посочения брой знаци от определена позиция в стария текст с нов;
ЗНАЧИТЕЛНО(Текст) – преобразува текста в число;
ЛЕВСИМВ(Низ, Брой знаци) – много полезна функция, връща посочения брой знаци, започвайки от първия знак;
ДЯСНО(Низ, Брой символи) – аналог на функцията ЛЕВСИМВ, с единствената разлика, че връща знаци от последния символ на низа;
НАМИРАМ(текст за търсене, текст, в който търсим, начална позиция) – функцията връща позицията, от която започва появата на търсения текст. Знаците са чувствителни към главни и малки букви. Ако трябва да не различавате главни и малки букви, използвайте функцията ТЪРСЕНЕ. Връща се само позицията на първото срещане в низа!
ЗАМЕСТВАНЕ(текст, стар текст, нов текст, позиция) – интересна функция, на пръв поглед подобна на функцията ЗАМЕНИТЕ, но функцията ЗАМЕСТВАНЕможе да замени всички срещания в низа с нов подниз, ако аргументът „позиция“ е пропуснат;
PSTR(Текст, Начална позиция, Брой знаци) – функцията е подобна на ЛЕВСИМВ, но може да връща знаци от посочената позиция:
СВЪРЗВАНЕ(Текст1, Текст 2 .... Текст 30) – функцията ви позволява да свържете до 30 реда. Можете също да използвате символа " & ", ще изглежда така "="Text1" & "Text2" & "Text3"";

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

Пример 1
Даден е набор от редове:

Необходимо е да се извлекат дати, номера на фактури от тези редове, както и да се добави поле за месец, за да се филтрират редовете по месеци.

Нека извлечем номерата на фактурите в колона B. За да направим това, ще намерим така наречения ключов символ или дума. В нашия пример можете да видите, че всеки номер на фактура е предшестван от „Не“, а дължината на номера на фактурата е 6 знака. Нека използваме функциите FIND и PSTR. Записваме следната формула в клетка B2:

= PSTR(A2; НАМИРАМ("№";A2)+1;6)

Нека да разгледаме формулата. От ред A2 от следващата позиция след намерения знак „Не“ извличаме 6 цифри.

Сега нека извлечем датата. Тук всичко е просто. Датата се намира в края на реда и заема 8 знака. Формулата за C2 е следната:

= ДЯСНО(A2;8)

но извлечената дата ще бъде низ, за да я преобразувате в дата, е необходимо да преобразувате текста в число след извличането:

= ЗНАЧИТЕЛНО(ДЯСНО(A2;8))

и след това задайте формата на показване в клетката, както е описано в статията „“.

И накрая, за удобство на по-нататъшното филтриране на редове, ще въведем колоната месец, която ще получим от датата. Само за да създадем месец, трябва да премахнем деня и да го заменим с „01“. Формула за D2:

= ЗНАЧИТЕЛНО(СВЪРЗВАНЕ("01"; ДЯСНО(A2;6))) или = ЗНАЧИТЕЛНО("01"& ДЯСНО(A2;6))

Задайте формата на клетката " ММММ ГГГГ" Резултат:

Пример 2
В редица " Пример за работа с низове в Excel" Всички интервали трябва да бъдат заменени със знака "_", а преди думата "Excel" добавете "MS".

Формулата ще бъде както следва:

=ЗАМЕСТВАНЕ(ЗАМЕНИТЕ(A1; ТЪРСЕНЕ("excel";A1);0;"MS ");" ";"_")

За да разберете тази формула, разделете я на три колони. Започнете с ТЪРСЕНЕ, последното ще бъде ЗАМЕНЯНЕ.

Всичко. Ако имате въпроси, не се колебайте да попитате

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

За да ги намерите в програмата, отидете в раздела, наречен "Формули", отворете списъка и изберете "Текст".

"КОНТАКТ"

Тази функция позволява на потребителя да свърже няколко реда заедно. Максималният разрешен брой за връзка е 255. Моля, обърнете внимание, че трябва сами да въведете интервали тук. Тялото на „CONCATENATE“ трябва да съдържа поне един аргумент. Нека да разгледаме пример за неговия запис и действие:

=CONCATENATE("Аргумент1","","Аргумент2");
Резултатът ще бъде следният ред: "Аргумент1 Аргумент2".

Алтернатива на тази функция за работа с текст е символът &, който също отговаря за свързването на низове.

"PSTR"

Той отговаря за връщането на част от текста в зададения брой знаци от необходимия ред. Редът започва с маркирания знак. Ключови позиции (текст, начална позиция, брой знаци). Всеки от тях се определя, както следва:

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

Като пример може да се даде следната ситуация. Клетка A1 съдържа малко текст. Трябва да върнем последните две думи; общата им дължина на знака е 12. Знакът, който трябва да бъде върнат в конкретна фраза, е номериран с 12.

"ЗАМЕСТИТЕЛ"

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

Конкретната функция изглежда така: текст; стар текст; нов текст; номер на събитие.

Нека разгледаме всеки от аргументите:

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

"ЗАМЕНЯНЕ"

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

Нека разгледаме всеки от аргументите на тази текстова функция в Excel поотделно:

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

Сега нека да разгледаме пример за използване на текстовата функция в Excel. Клетка A1 съдържа ред с думата „стар“. Започва от 19-ти знак и има продължителност от шест еднакви единици. Замяната става с думата "ново".

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

Ще заменим първоначалната позиция с „НАМИРАНЕ“.
Броят на знаците ще се промени на "LENGTH".

"DLST"

Тази текстова функция в Excel помага на потребителя да определи дължината на низ. Резултатът от работата му ще бъде връщането на цяло число, указващо броя на знаците в текста. Изглежда така: =DLTR(текст).

"НАМИРАМ"

Когато работи с текст, той изпълнява функцията да връща число, което представлява появата на първия символ, намерен в подниз на необходимия текст. Ако данните не са намерени, вместо число се връща грешката "#VALUE!"

Ето формата му в документа. Но без конкретни аргументи: = FIND(текст за търсене; текст за търсене; начална позиция).

Сега нека разгледаме всеки от горните аргументи поотделно:

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

Excel предлага голям брой функции, с които можете да обработвате текст. Обхватът на текстовите функции не се ограничава до текст; те могат да се използват и с клетки, съдържащи числа. В този урок ще използваме примери, за да разгледаме 15-те най-често срещани функции на Excel от категорията Текст.

СВЪРЗВАНЕ

За да комбинирате съдържанието на клетките в Excel, заедно с оператора за конкатенация, можете да използвате текстовата функция СВЪРЗВАНЕ. Той последователно свързва стойностите на посочените клетки в един ред.

НИСЪК

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

КАПИТАЛ

Текстова функция КАПИТАЛправи всички букви главни, т.е. ги преобразува в главни букви. Както и НИСЪК, не замества знаци, които не са букви.

ПРОПНАЧ

Текстова функция ПРОПНАЧправи главна първата буква на всяка дума и преобразува всички останали в малки букви.

Всяка първа буква, която следва знак, който не е буква, също се преобразува в главна буква.

DLST

Ляво и дясно

Текстови функции ЛЕВСИМВИ ДЯСНОвръща определен брой знаци, започвайки от началото или края на низа. Интервалът се брои за знак.

PSTR

Текстова функция PSTRвръща определен брой знаци, започващи от определена позиция. Интервалът се брои за знак.

СЛУЧАЙНОСТ

функция СЛУЧАЙНОСТви позволява да сравните два текстови низа в Excel. Ако съвпадат точно, тогава се връща TRUE, в противен случай FALSE. Тази текстова функция е чувствителна към главни и малки букви, но игнорира разликите във форматирането.

Ако регистърът не играе голяма роля за вас (това се случва в повечето случаи), тогава можете да приложите формула, която просто проверява равенството на две клетки.

ПРОСТРАНСТВО

Премахва всички допълнителни интервали от текста, с изключение на единичните между думите.

В случаите, когато наличието на допълнителен интервал в края или началото на ред е трудно да се проследи, тази функция става просто незаменима. На фигурата по-долу можете да видите, че съдържанието на клетки A1 и B1 е абсолютно същото, но това не е така. В клетка A1 умишлено поставяме допълнителен интервал в края на думата Excel. В резултат на това функцията СЛУЧАЙНОСТни върна стойността FALSE.

Чрез прилагане на функцията ПРОСТРАНСТВОкъм стойността на клетка A1, ще премахнем всички допълнителни интервали от нея и ще получим правилния резултат:

функция ПРОСТРАНСТВОполезен за прилагане към данни, които се импортират в производството Excel листовеот външни източници. Такива данни много често съдържат допълнителни интервали и различни непечатаеми знаци. За да премахнете всички непечатаеми знаци от текста, трябва да използвате функцията ПЕЧСИМВ.

ПОВТОРЕНИЕ

функция ПОВТОРЕНИЕповтаря текстов низ определен брой пъти. Низът се дава като първи аргумент на функцията, а броят на повторенията като втори.

НАМИРАМ

Текстова функция НАМИРАМнамира срещането на един низ в друг и връща позицията на първия знак от фразата за търсене спрямо началото на текста.

Тази функция е чувствителна към главни и малки букви...

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

ТЪРСЕНЕ

Текстова функция ТЪРСЕНЕмного подобен на функцията НАМИРАМ, основната им разлика е, че ТЪРСЕНЕне са чувствителни към малки и големи букви.

ЗАМЕСТВАНЕ

Заменя определен текст или знак с необходимата стойност. Текстова функция на Excel ЗАМЕСТВАНЕизползва се, когато предварително се знае какъв текст трябва да бъде заменен, а не местоположението му.

Формулата по-долу замества всички срещания на думата „Excel“ с „Word“:

Заменя само първото срещане на думата „Excel“:

Премахва всички интервали от текстов низ:

ЗАМЕНИТЕ

Заменя знаци, разположени на предварително известно място в низ с необходимата стойност. Текстова функция на Excel ЗАМЕНИТЕизползва се, когато се знае къде се намира текстът, но самият текст не е важен.

Формулата в примера по-долу замества 4 знака, започвайки от седма позиция, със стойността „2013“. В нашия пример формулата ще замени „2010“ с „2013“.

Заменя първите пет знака от текстов низ, т.е. думата „Excel“ в „Word“.

Това е всичко! Запознахме се с 15 текстови функции Microsoft Excelи разгледа ефекта им върху прости примери. Надявам се, че този урок е дошъл в точното време за вас и сте получили поне малко от него полезна информация. Всичко най-добро и успех в изучаването на Excel!

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

Аргументът е стойност, която предоставя входна информация на функция.

Списък с текстови функции

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

Бързо използване на функциите

"BATTEXT"

Помощта за функцията "BATTEXT" казва, че в контролния панел, в секцията "Език и регионални стандарти", можете да промените формата "bat" на различен стил, но по правило никой не успява да направи това . Поради това,аналог на тази функция на руски език написана във вградена Офис приложенияезикът за програмиране VisualBasicforApplications и се разпространява като добавка.

"ЛЕВСИМВ" и "ПРАВСИМВ"

Функциите LEFT и RIGHT се използват за изрязване на ненужни знаци в стойностите на клетките, както текстови, така и цифрови. В случаите, когато е необходимо изрязаните стойности да бъдат в същите клетки, в които се намира оригиналният текст, е по-бързо и по-удобно да изрежете текста, заобикаляйки етапа на въвеждане на формули, като използвате добавка, която позволява премахване на допълнителни знаци.

"ЗНАЧИТЕЛНО"

Число в Excel може да бъде форматирано като текст. С такива числа не могат да се извършват никакви математически операции, те се възприемат от Excel като обикновен текст. Можете да получите число, форматирано като текст, например като поставите апостроф пред числовата стойност или като зададете формата на клетката на текст и след това я попълните с цифрова стойност. За да преобразувате такива числа в реални числа, се използва функцията „VALUE“. Освен това можете да конвертирате текст в число, без да използвате формула, като използвате добавка, която в даден диапазоннамира числа, форматирани като текст, и ги трансформира в реални числа.

“PECHSIMV”, “UPPERCASE”, “PROPNACH”, “SZHSPACE”, “LOWER”

Някои текстови функции, като „PREPCHIMV“, „UPPER CAPITAL“, „PROPNACH“, „SPACES“, „LOWER“, често са удобни за използване не под формата на формули, въведени в клетките на работния лист, а като инструмент, който ви позволява за да получите крайния резултат от трансформацията веднага в клетки с изходен текст. В такива случаи е удобно да се използва

Excel предлага на своите потребители до 3 функции за работа с големи и малки букви в текст (главни и малки). Тези текстови функции правят буквите главни и малки или променят само първата буква в думата, за да бъде главна.

Формули с текстови функции на Excel

Първо, нека да разгледаме примера с 3 текстови съобщения Функции на Excel:

CAPITAL – тази текстова функция променя всички букви в една дума в главни букви.
LOWER – Тази функция преобразува всички текстови знаци в малки, малки букви.
PROPNACH - функцията променя само първата буква във всяка дума в главна буква.

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

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

За да разрешите този популярен проблем, трябва да използвате допълнителни текстови функции на Excel във формулата: ЛЯВО, ДЯСНО и ДЪЛЖИНА.

Принципът на действие на формулата за замяна на първата буква в изречението

Ако се вгледате внимателно в синтаксиса на горната формула, можете лесно да видите, че тя се състои от две части, свързани една с друга чрез оператора &.

Лявата страна на формулата използва допълнителна функцияЛЕВСИМВ:

Целта на тази част от формулата е да промени първата буква на главна буква в оригиналния текстов низ на клетка A1. Благодарение на функцията LEFT можете да получите определен брой знаци, започвайки от лявата страна на текста. Функцията изисква да бъдат попълнени 2 аргумента:

Текст – връзка към клетка с изходен текст.
Number_characters – броят знаци, върнати от лявата страна (от началото) на изходния текст.

IN в този примертрябва да получите само първия 1 знак от оригиналния текстов низ в клетка A1. След това полученият знак се преобразува в главни букви Главна букваГлавна буква.

Дясната страна на формулата след оператора & е много подобна по принцип на лявата страна, само че решава различен проблем. Неговата задача е да преобразува всички текстови знаци в малки букви. Но това трябва да се направи, за да не се промени първата главна буква, за която е отговорна лявата страна на формулата. Вместо функцията ЛЯВО от дясната страна на формулата се използва функцията ДЯСНО:

Текстовата функция ДЯСНО работи обратно пропорционално на функцията ЛЯВО. Освен това изисква премахване на два аргумента: изходния текст и броя на знаците. Но връща определен брой букви, получени от дясната страна на изходния текст. Въпреки това, в в такъв случайне можем да посочим фиксирана стойност като втори аргумент. В крайна сметка ние не знаем предварително броя на знаците в изходния текст. Освен това дължината на различните изходни текстови низове може да варира. Следователно трябва първо да изчислим дължината на текстовия ред и да извадим -1 от получената цифрова стойност, за да не променим първата главна буква в реда. В крайна сметка първата буква се обработва от лявата страна на формулата и вече е трансформирана, за да отговаря на изискванията на потребителя. Следователно не трябва да се влияе от никоя функция от дясната страна на формулата.

За да изчислите автоматично дължината на изходния текст, използвайте текстовата функция на Excel - DLST (означава дължината на реда). Тази функция изисква да бъде попълнен само един аргумент - връзка към изходния текст. В резултат на изчислението връща числова стойност, така че след функцията =LENGTH(A1) изваждаме -1. Което ни дава възможност да не докосваме първата главна буква от дясната страна на формулата. В резултат на това функцията RIGHT връща текстов низ без един първи знак на функцията LOWER, която преобразува всички знаци от текста в малки малки букви.

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