Задаване на тире в Microsoft Excel

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

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

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

БАТТЕКСТ (Стойност) - функция, която преобразува число в текстов тип;
DLSTR (Стойност) е помощна функция, много полезна при работа със низове. Връща дължината на низа, т.е. брой символи, съдържащи се в низа;
ЗАМЕНЕТЕ (Стар текст, начална позиция, брой знаци, нов текст) - замества посочения брой знаци от определена позиция в стария текст с нов;
ЗНАЧИТЕЛНО (Текст) - преобразува текста в число;
ЛЕВСИМВ (String, Number of characters) - много полезна функция, тя връща посочения брой символи, започвайки от първия символ;
НАДЯСНО (String, Брой символи) - аналог на функцията ЛЕВСИМВ, с единствената разлика, че символите се връщат от последния символ на низа;
ДА НАМЕРЯ (текст за търсене, текст, в който търсим, изходна позиция) - функцията връща позицията, от която започва въвеждането на търсения текст. Взема се предвид случаят с символи. Ако трябва да не вземате предвид малки и малки букви, използвайте функцията ТЪРСЕНЕ... Връща се само позицията на първата поява в низа!
ЗАМЕСТИТЕЛ (текст, стар текст, нов текст, позиция) - интересна функция, на пръв поглед изглежда като функция ЗАМЕНЕТЕно функцията ЗАМЕСТИТЕЛ може да замени всички появявания в низа с нов подниз, ако аргументът "position" е пропуснат;
PSTR (Текст, начална позиция, брой символи) - функцията е подобна на ЛЕВСИМВ, но е в състояние да върне символи от посочената позиция:
СВЪРЗВАНЕ (Text1, Text 2 .... Text 30) - функцията ви позволява да свържете до 30 реда. Също така можете да използвате символа " & ”, Ще изглежда така„ \u003d ”Text1” & ”Text2” & ”Text3” ”;

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

Пример 1
Като се има предвид куп редове:

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

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

= PSTR(А2; ДА НАМЕРЯ("No."; A2) +1; 6)

Нека анализираме формулата. От ред А2 от позицията, следваща след намерения знак "Не", извличаме 6 знака от числото.

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

= НАДЯСНО(A2; 8)

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

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

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

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

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

Форматирайте клетката " ММММ ГГГГ". Резултат:

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

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

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

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

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

Следните функции намират и връщат части от текстови низове или правят големи низове от малки: НАМЕРИ, ТЪРСИ, НАДЯСНО, НАЛЯВО, СРЕДЕН, ЗАМЕНЯТ, РЕПТИРАЙ, ЗАМЕНИ, КОНКАТЕНИРАЙ.

Функции FIND и SEARCH

Функциите FIND и SEARCH се използват за определяне на позицията на един текстов низ в друг. И двете функции връщат номера на символа, от който започва първото появяване на низа за търсене. Тези две функции работят по един и същи начин, с изключение на това, че FIND е чувствителен към малки и големи букви и SEARCH приема заместващи символи. Функциите имат следния синтаксис:

\u003d НАМЕРЕТЕ (lookup_text; lookup_text; start_position)
\u003d ТЪРСЕНЕ (lookup_text; lookup_text; start_position)

Аргумент текст за търсене указва текстовия низ за намиране и аргумента прегледан_текст - текстът, в който се извършва търсенето. Всеки от тези аргументи може да бъде символен низ, затворен в двойни кавички или препратка към клетка. Незадължителен аргумент начална_позиция указва позицията в търсения текст, от която започва търсенето. Аргумент начална_позиция трябва да се използва, когато lookup_text съдържа множество повторения на текста за търсене. Ако този аргумент е пропуснат, Excel връща позицията на първата поява.

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

Например, за да определите позицията на буквата "g" в реда "Гаражни врати", трябва да използвате формулата:

FIND ("f"; "Гаражни врати")

Тази формула връща 5.

Ако не знаете точната последователност от символи на текста, който търсите, можете да използвате функцията SEARCH и да включите в низа текст за търсене заместващи знаци: въпросителен знак (?) и звездичка (*). Въпросният знак съвпада с един произволно набран знак и звездичка замества всяка последователност от символи в определената позиция. Например, за да намерите позицията на имената Анатолий, Алексей, Акаки в текста, намиращ се в клетка А1, трябва да използвате формулата:

ТЪРСЕНЕ ("A * d"; A1)

Функции НАДЯСНО и НАЛЯВО

Функцията НАДЯСНО връща най-десните символи на аргументарния низ, докато функцията НАЛЯВО връща първите (вляво) символи. Синтаксис:

\u003d НАДЯСНО (текст, брой_символи)
\u003d НАЛЯВО (текст, брой символи)

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

Аргумент брой знаци трябва да е по-голямо или равно на нула. Ако този аргумент е пропуснат, Excel смята, че е 1. Ако герои повече знаци в аргумента текст , тогава се връща целият аргумент.

PSTR функция

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

\u003d MID (текст, начална_позиция, брой знаци)

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

Функции ЗАМЯНА и ЗАМЕНЯВАНЕ

Тези две функции заместват символите в текста. Функцията REPLACE заменя част от текстов низ с друг текстов низ и има синтаксис:

\u003d ЗАМЕНИТЕ (стар_текст; начална_позиция; брой знаци; нов_текст)

Аргумент old_text е текстов низ за замяна на символи с. Следващите два аргумента указват символите, които трябва да бъдат заменени (спрямо началото на реда). Аргумент нов_текст указва текстовия низ за вмъкване.

Например клетка А2 съдържа текста „Вася Иванов“. За да поставите същия текст в клетка A3, замествайки името, трябва да вмъкнете следната функция в клетка A3:

ЗАМЕНЕТЕ (A2; 1; 5; "Петя")

Във функцията SUBSTITUTE началната позиция и броят на символите, които трябва да бъдат заменени, не са посочени, но текстът за заместване е изрично посочен. Функцията SUBSTITUTE има следния синтаксис:

\u003d ЗАМЕСТИТЕЛ (текст; стар_текст; нов_текст; брой_настъпване)

Аргумент номер на запис е по избор. Той инструктира Excel да замени само дадената поява на низа old_text .

Например клетка А1 съдържа текста „Нула по-малка от осем“. Необходимо е думата "нула" да се замени с "нула".

ЗАМЕСТИТЕЛ (A1; "o"; "y"; 1)

Числото 1 в тази формула показва, че трябва да се промени само първото „о“ в реда на клетка А1. Ако аргументът номер на запис пропуснат, Excel замества всички случаи на низа old_text на ред нов_текст .

Функция REPEAT

Функцията REPT ви позволява да запълните клетка с низ от символи, повторени определен брой пъти. Синтаксис:

\u003d REPEAT (текст, повторения)

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

Функция CONCATENATE

Функцията CONCATENATE е еквивалент на текстов израз & и се използва за обединяване на низове. Синтаксис:

\u003d КОНКАТЕНАТ (текст1, текст2, ...)

Можете да използвате до 30 аргумента във функция.

Например клетка A5 съдържа текста "първата половина на годината", следната формула връща текста "Общо за първата половина на годината":

КОНКАТЕНАТ ("Общо за"; A5)

Много потребители на Excel изпитват значителни затруднения, когато се опитват да поставят тире на работен лист. Факт е, че програмата разбира тирето като знак минус и незабавно преобразува стойностите в клетката във формула. Следователно този въпрос е доста спешен. Нека да видим как да поставите тире в Excel.

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

Метод 1: форматиране на диапазона

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

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

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

Метод 2: Натискане на бутона Enter

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

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

Метод 3: поставете символ

Друг начин да напишете тире в Excel е да вмъкнете символ.

След това тирето ще бъде отразено в избраната клетка.

Има и друг вариант за този метод. Да си на прозореца "Символ", отидете в раздела "Специални символи"... В списъка, който се отваря, изберете елемента Ем тире... Щракнете върху бутона „Поставяне“... Резултатът ще бъде същият като в предишната версия.

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

Метод 4: добавете допълнителен знак

Освен това има и друг начин да поставите тире. Вярно е, че визуално тази опция няма да бъде приемлива за всички потребители, тъй като приема присъствието в клетката, в допълнение към самия знак "-", още един символ.

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