Приложението на функцията BPR в Microsoft Excel

В някои случаи потребителят е изправен пред задачата да върне определен брой знаци към целевата клетка от друга клетка, започвайки със знака, указан в акаунта вляво. Тази функция се управлява перфектно от функцията за PCR . Още повече увеличава функционалността си, ако в комбинация с нея използва други оператори, например SEARCH или FIND . Нека да разгледаме по-отблизо какви са възможностите на функцията на ОМТ и да видим как работи тя по конкретни примери.

Използване на CCRT

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

=ПСТР(текст;начальная_позиция;количество_знаков)

Както можете да видите, тази формула се състои от три аргумента. Всички те са задължителни.

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

Аргументът "Начална позиция" е представен като число, което показва кой знак от профила, като се започне отляво, трябва да извлечете. Първият знак се счита за "1" , вторият за "2" и т.н. При изчислението са взети предвид дори пространства.

Аргументът "Брой знаци" съдържа числен индекс на броя знаци, като се започне от началната позиция, която трябва да извлечете към целевата клетка. Когато се брои, точно като предишния аргумент, пространствата се вземат под внимание.

Пример 1: единична екстракция

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

Така че, имаме маса от служители на компанията. Първата колона съдържа имената, фамилното име и патронимиката на служителите. Трябва да използваме оператора на CCTS, за да извлечем в посочената клетка само името на първия човек от списъка на Петър Иванович Николаев.

1. Изберете елемента от листа, който искате да извлечете. Кликваме върху бутона "Вмъкване на функция" , който се намира близо до формулата.
2. Отваря се прозорецът на съветника за опции. Отворете категорията "Текст" . Избираме името "POS" там и кликваме върху бутона "OK" .
3. Изпълнява прозореца с аргументи на оператора "PTPR" . Както можете да видите, в този прозорец броят полета съответства на броя аргументи на тази функция.

   В полето "Текст" въвеждаме координатите на клетката, която съдържа имената на служителите. За да не управлявате адреса ръчно, просто поставете курсора в полето и кликнете с левия бутон на мишката върху елемента на листа, който съдържа данните, от които се нуждаем.

   В полето "Начална позиция" трябва да посочите номера на символа, който се брои отляво, от който започва фамилното име на служителя. Когато броим, вземаме предвид и пропуските. Писмото "N" , с което започва името на служителя Николаев, е петнадесетият символ. Ето защо в полето поставяме числото "15" .

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

   След като въведете всички данни, кликнете върху бутона "OK" .

4. Както можете да видите, след това действие фамилията на служителя е изведена в клетката, посочена в първата стъпка от Пример 1 .

Урокът: Съветник за функции в Excel

Пример 2: групова екстракция

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

Имаме списък със смартфони. Преди името на всеки модел е думата "Смартфон" . Трябва да поставим в отделна колона само имената на моделите без тази дума.

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

   В полето "Текст" посочваме адреса на първия елемент на колоната с оригиналните данни.

   В полето "Начална позиция" трябва да посочим номера на символа, от който ще бъдат извлечени данните. В нашия случай във всяка клетка преди името на модела е думата "смартфон" и интервал. По този начин фразата, която трябва да бъде изведена в отделна клетка, започва навсякъде от десетия символ. Задайте номера "10" в това поле.

   В полето "Брой знаци" трябва да зададете броя знаци, които съдържат изписаната фраза. Както можете да видите, името на всеки модел има различен брой знаци. Но фактът, че след името на модела, текстът в клетките свършва, спестява ситуацията. Следователно, в това поле може да зададем число, което е равно на или по-голямо от броя знаци с най-дългото име в този списък. Задаваме произволен брой знаци "50" . Името на който и да е изброени смартфони не надвишава 50 знака, така че тази опция ни подхожда.

   След като въведете данните, кликнете върху бутона "OK" .

2. След това името на първия модел на смартфона се показва в предварително зададената клетка на таблицата.
3. За да не въведем поотделно формулата във всяка клетка на колоната, копираме я с помощта на маркера за попълване. За целта поставете курсора в долния десен ъгъл на клетката с формулата. Курсорът се преобразува в дръжка за пълнене под формата на малък кръст. Задръжте левия бутон на мишката и го плъзнете до самия край на колоната.
4. Както можете да видите, цялата колона ще бъде пълна с данните, от които се нуждаем. Тайната е, че аргументът "Текст" е относителна референция и също се променя, когато позицията на целевите клетки се променя.
5. Но проблемът е, че ако решим да променим или изтрием внезапно колоната с оригиналните данни, данните в целевата колона ще бъдат показани неправилно, тъй като те са свързани един с друг чрез формула.

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

   Като алтернативно действие можете да натиснете Ctrl + C след избора.

6. След това, без да премахвате селекцията, кликнете върху колоната с десния бутон на мишката. Отваря контекстното меню. В блока "Параметри за вмъкване" кликнете върху иконата "Стойности" .
7. След това, вместо формулите, стойностите ще бъдат въведени в избраната колона. Сега можете безопасно да редактирате или изтриете оригиналната колона. Резултатът не е засегнат по никакъв начин.

Пример 3: Използване на комбинация от оператори

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

Текстовите оператори SEARCH и FIND връщат позицията на посочения знак в разглеждания текст.

Синтаксисът на функцията за търсене е както следва:

=ПОИСК(искомый_текст;текст_для_поиска;начальная_позиция)

Синтаксисът на оператора FIND изглежда така:

=НАЙТИ(искомый_текст;просматриваемый_текст;нач_позиция)

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

Да видим как да използваме оператора SEARCH заедно с функцията CCR . Имаме таблица, в която се въвеждат имената на различни модели компютърни технологии с общо име. Както и в последния момент, трябва да извлечем имената на моделите без общо име. Трудността е, че ако в предишния пример генеричното име за всички елементи е същото ("смартфон"), то в този списък е различно ("компютър", "монитор", "колони" и т.н.) с различен брой знаци. За да решим този проблем, се нуждаем и от оператора SEARCH , който ще инвестираме във функцията CCT.

1. Избираме първата клетка на колоната, където ще бъдат изведените данни, и по обичайния начин наричаме прозореца на аргументите на функцията на CCT .

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

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

   За да отворите прозореца с аргументи на оператора SEARCH , задайте курсора в полето "Initial Position" . След това кликнете върху иконата под формата на триъгълник, насочен надолу. Тази икона се намира на същото хоризонтално ниво на прозореца, където се намира бутонът "Вмъкване на функция" и лентата с формули, но отляво от тях. Извежда се списък с най-скоро използваните оператори. Тъй като между тях няма име "SEARCH" , кликнете върху елемента "Други функции ..." .

3. Прозорецът Магьосници се отваря. В категорията "Text" изберете името "SEARCH" и кликнете върху бутона "OK" .
4. Отваря се полето за търсене на оператора. Тъй като търсим пространство, поставяме интервал в полето "Търсене на текст", като настроите курсора там и натискаме съответния клавиш на клавиатурата.

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

   Архивът на полето "Начална позиция" е по избор. В нашия случай не е необходимо да го попълвате или можете да зададете номер "1" . За всяка от тези опции търсенето ще се извърши от началото на текста.

   След като въведете данните, не бързайте да натискате бутона "OK" , тъй като функцията SEARCH е вложена. Просто кликнете върху името на PCR в лентата за формули.

5. След като се извърши последното определено действие, автоматично се връщаме в прозореца с аргументи на оператора на BTS . Както можете да видите, полето "Начална позиция" вече е попълнено с формулата за търсене . Но тази формула показва пространство и имаме нужда от следващия знак след мястото, от което започва името на модела. Затова към съществуващите данни в полето "Начална позиция" добавяме израз "+1" без кавички.

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

   След извършване на всички посочени манипулации, кликнете върху бутона "OK" в долната част на прозореца.

6. Както можете да видите, след това името на модела на устройството бе показано в отделна клетка.
7. Сега, като използвате съветника за попълване, както в предишния метод, копирайте формулата до клетките, които са разположени по-ниски в тази графа.
8. Имената на всички модели устройства се показват в целевите клетки. Сега, ако е необходимо, е възможно да се прекъсне връзката в тези елементи с колоната на оригиналните данни, както и в предишното време, като се приложи копирането и поставянето на стойностите последователно. Това действие обаче не винаги е задължително.

Функцията FIND се използва заедно с формулата EPR по същия принцип като оператора SEARCH .

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

• ← Изчисляване на анюитетно плащане в Microsoft Excel
• Отпечатване на документ в Microsoft Excel →