Текстови функции и формули в excel. Работа с низове в Excel

• знак за по-малко или равенство<=»;

• знак "не е равно".<>».

Оператор за конкатенация на текст

За тази цел се използва специалният знак "&" (амперсанд). С него можете да свържете различни фрагменти в едно цяло - същия принцип като при функцията "CONNECT". Ето няколко примера:

1. Ако искате да комбинирате текст в клетки, тогава трябва да използвате следния код.

1. За да вмъкнете някакъв знак или буква между тях, трябва да използвате следната конструкция.

1. Можете да комбинирате не само клетки, но и обикновени знаци.

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

Моля, обърнете внимание, че кавичките се използват точно както на екранната снимка.

Можете да използвате следните оператори, за да дефинирате връзки:

• за да създадете проста връзка към желания диапазон от клетки, достатъчно е да посочите първата и последната клетка от тази област и символа ":" между тях;
• за комбиниране на връзки използвайте знака ";";
• ако е необходимо да се определят клетките, които са в пресечната точка на няколко диапазона, тогава между връзките се поставя „интервал“. В този случай ще се покаже стойността на клетката "C7".

Тъй като само той попада в определението за "пресечна точка на множества". Това е името на този оператор (интервал).

Използване на връзки

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

Прости връзки A1

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

• колони - от A до XFD (не повече от 16384);
• линии - от 1 до 1048576.

Ето няколко примера:

• клетка в пресечната точка на ред 5 и колона B - "B5";
• диапазон от клетки в колона B, започвайки от ред 5 до ред 25 - "B5:B25";
• диапазонът от клетки в ред 5, започвайки от колона B до F - "B5:F5";
• всички клетки в ред 10 - "10:10";
• всички клетки в редове от 10 до 15 - "10:15";
• всички клетки в колона B - "B:B";
• всички клетки в колони от B до K - "B:K";
• диапазонът от клетки от B2 до F5 е "B2-F5".

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

Вторият лист съдържа следните данни.

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

Абсолютни и относителни връзки

Редакторът на Excel работи с три типа връзки:

• абсолютен;
• роднина;
• смесен.

Нека ги разгледаме по-внимателно.

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

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

1. Въведете формулата за изчисляване на сумата от първата колона.

1. Натиснете бързите клавиши Ctrl +C. За да прехвърлите формулата в следващата клетка, трябва да отидете там и да натиснете Ctrl + V .

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

1. Сега вижте новите формули. Промяната в индекса на колоната се случи автоматично.

Ако искате всички връзки да се запазят при прехвърляне на формули (тоест, за да не се променят автоматично), трябва да използвате абсолютни адреси. Те са посочени като "$B$2".

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

Този тип адрес се използва, когато е необходимо да се коригира само колона или ред, а не всички едновременно. Можете да използвате следните конструкции:

• $D1, $F5, $G3 - за фиксиране на колони;
• D$1, F$5, G$3 - за коригиране на редове.

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

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

1. Нека използваме следния израз като пример.

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

3D връзки

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

В този случай резултатът ще съответства на сумата от всички клетки "A5" на всички листове, започвайки от 1 до 4. При съставянето на такива изрази трябва да се спазват следните условия:

• масивите не могат да използват такива препратки;
• забранено е използването на триизмерни изрази там, където има пресичане на клетки (например оператор "space");
• Когато създавате формули с 3D адреси, можете да използвате следните функции: AVERAGE, STDEV, STDEV.V, AVERAGE, STDEV, STDEV.Y, SUM, COUNT, COUNT, MIN, MAX, MIN, MAX, VARR, PRODUCT, VARV, VAR.V и DISPA.

Ако нарушите тези правила, тогава ще видите някакъв вид грешка.

Връзки във формат R1C1

Този тип връзки се различава от "A1" по това, че номерът се дава не само на редове, но и на колони. Разработчиците решиха да заменят нормалния изглед с тази опция за удобство в макросите, но те могат да се използват навсякъде. Ето няколко примера за такива адреси:

• R10C10 е абсолютна връзка към клетката, която се намира на десетия ред на десетата колона;
• R - абсолютна връзка към текущата (в която е посочена формулата) връзка;
• R[-2] – относителна връзка към линията, която се намира две позиции над тази;
• R[-3]C – относителна препратка към клетката, която се намира три позиции по-нагоре в текущата колона (където сте решили да напишете формулата);
• RC е относителна връзка към клетката, която е пет клетки вдясно и пет реда под текущата.

Използване на име

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

Можете да използвате имената за умножение, деление, събиране, изваждане, изчисляване на лихви, съотношения, отклонения, закръгляния, ДДС, ипотеки, заеми, прогнози, разписания, различни формуляри, отстъпки, заплати, стаж, анюитетни плащания, работа с VLOOKUP формули , "VVD", "МЕЖДИННИ РЕЗУЛТАТИ" и т.н. Тоест, можете да правите каквото искате.

Само едно нещо може да се нарече основно условие - трябва да определите това име предварително. В противен случай Excel няма да знае нищо за него. Това става по следния начин.

1. Изберете колона.
2. Извикайте контекстното меню.
3. Изберете „Дайте име“.

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

1. Щракнете върху бутона "OK", за да запазите.

По същия начин можете да зададете име на всяка клетка, текст или номер.

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

И ако се опитате да вмъкнете името ни вместо адреса "D4: D9", ще видите подсказка. Достатъчно е да напишете няколко знака и ще видите какво пасва (от базата на имената) най-много.

В нашия случай всичко е просто - „column_3“. И си представете, че ще имате голям брой такива имена. Няма да можете да запомните всичко.

Използване на функции

Има няколко начина за вмъкване на функция в редактора на Excel:

• ръчно;
• използване на лентата с инструменти;
• с помощта на прозореца Вмъкване на функция.

Нека разгледаме по-отблизо всеки метод.

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

Ако нямате опит в тази област, тогава е по-добре да използвате по-леки методи в началото.

В този случай е необходимо:

1. Отидете в раздела "Формули".
2. Кликнете върху произволна библиотека.
3. Изберете желаната функция.

1. Веднага след това ще се появи прозорецът "Аргументи и функции" с вече избраната функция. Просто трябва да запишете аргументите и да запазите формулата с помощта на бутона "OK".

Съветник за заместване

Можете да го приложите така:

1. Направете всяка клетка активна.
2. Кликнете върху иконата "Fx" или използвайте клавишната комбинация SHIFT + F3.

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

Достатъчно е да маркирате дума, която може да опише това, което искате да направите, и редакторът ще се опита да покаже всички подходящи опции.

1. Изберете която и да е функция от предложения списък.
2. За да продължите, трябва да кликнете върху бутона "OK".

1. След това ще бъдете помолени за „Аргументи и функции“. Можете да направите това ръчно или просто да изберете желания диапазон от клетки.
2. За да приложите всички настройки, трябва да кликнете върху бутона "OK".

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

Използване на вложени функции

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

След това следвайте инструкциите по-долу:

1. Кликнете върху първата клетка. Извикайте прозореца "Вмъкване на функция". Изберете функцията "Ако". Щракнете върху „OK“, за да поставите.

1. След това ще трябва да съставите някакъв вид логически израз. Трябва да се изпише в първото поле. Например, можете да добавите стойностите на три клетки в един ред и да проверите дали сумата е по-голяма от 10. В случай на „истина“, посочете текста „Повече от 10“. За фалшив резултат - "По-малко от 10". След това щракнете върху OK, за да се върнете към работното пространство.

1. В резултат на това виждаме следното - редакторът издаде, че сборът на клетките в третия ред е по-малък от 10. И това е правилно. Така че нашият код работи.

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

1. В резултат на това редакторът преизчислява нашия израз за всеки ред.

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

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

Единственото, което можете да направите, е да увеличите полето за въвеждане. За да направите това, просто щракнете върху посочената икона или натиснете клавишната комбинация Ctrl + Shift + U.

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

В случай на използване на функции, всичко става много по-просто. За да редактирате, следвайте инструкциите по-долу:

1. Активирайте клетката с формулата. Кликнете върху иконата "Fx".

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

1. За да запазите направените промени, използвайте бутона "OK".

За да премахнете израз, просто направете следното:

1. Кликнете върху произволна клетка.

1. Щракнете върху бутона Изтриване или Backspace. В резултат на това клетката ще бъде празна.

Можете да постигнете абсолютно същия резултат с помощта на инструмента Изчисти всичко.

Възможни грешки при компилиране на формули в редактора на Excel

Следните са най-честите грешки, които потребителите правят:

• Изразът използва огромен брой вложени елементи. Не трябва да има повече от 64;
• пътищата към външни работни книги са посочени във формули без пълния път;
• Отварящите и затварящите скоби са неправилно поставени. Ето защо в редактора в лентата с формули всички скоби са маркирани с различен цвят;

• имената на книгите и листовете не се поставят в кавички;
• числата се използват в грешен формат. Например, ако трябва да посочите $2000, просто трябва да въведете 2000 и да изберете подходящия формат на клетката, тъй като символът $ се използва от програмата за абсолютни препратки;

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

• Диапазоните на клетките са неправилни. За да направите това, трябва да използвате оператора ":" (двоеточие).

Кодове за грешки при работа с формули

Когато работите с формула, може да видите следните опции за грешка:

• #СТОЙНОСТ! - Тази грешка показва, че използвате грешен тип данни. Например, опитвате се да използвате текст вместо числова стойност. Разбира се, Excel не може да изчисли сумата между две фрази;
• #ИМЕ? - подобна грешка означава, че сте направили печатна грешка в изписването на името на функцията. Или се опитвате да въведете нещо, което не съществува. Не можеш да направиш това. Освен това проблемът може да е в друго. Ако сте сигурни за името на функцията, опитайте се да разгледате формулата по-отблизо. Може би сте забравили скоба. Освен това трябва да вземете предвид, че текстовите фрагменти са посочени в кавички. Ако всичко друго се провали, опитайте да съставите израза отново;
• #БРОЙ! - показването на такова съобщение означава, че имате някакъв проблем с аргументите или с резултата от формулата. Например числото се оказа твърде голямо или обратното - малко;
• #DIV/0! - Тази грешка означава, че се опитвате да напишете израз, който дели на нула. Excel не може да отмени математически правила. Следователно подобни действия също са забранени тук;
• #N/A! - Редакторът може да покаже това съобщение, ако някаква стойност не е налична. Например, ако използвате функциите SEARCH, SEARCH, MATCH и Excel не намери фрагмента, който търсите. Или изобщо няма данни и формулата няма с какво да работи;
• Ако се опитвате да изчислите нещо и Excel напише думата #REF!, тогава в аргумента на функцията се използва грешен диапазон от клетки;
• #ПРАЗЕН! - тази грешка се появява, ако имате непоследователна формула с припокриващи се диапазони. По-точно, ако в действителност няма такива клетки (които са в пресечната точка на два диапазона). Доста често тази грешка възниква случайно. Достатъчно е да оставите едно място в аргумента и редакторът ще го възприеме като специален оператор (говорихме за него по-рано).

Когато редактирате формулата (клетките са маркирани) ще видите, че те всъщност не се пресичат.

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

За да видите стойността, съдържаща се там, е достатъчно да промените размера на колоната.

Освен това можете да използвате форматиране на клетки. За да направите това, трябва да следвате няколко прости стъпки:

1. Извикайте контекстното меню. Изберете Форматиране на клетки.

1. Посочете типа "Общи". Използвайте бутона "OK", за да продължите.

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

Примери за използване на формули

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

За да оцените математическите възможности на Excel, трябва да изпълните следните стъпки.

1. Създайте таблица с някои условни данни.

1. За да изчислите сумата, въведете следната формула. Ако искате да добавите само една стойност, можете да използвате оператора за добавяне ("+").

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

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

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

1. Excel може да добави, като вземе предвид няколко условия наведнъж. Можете да изчислите сумата от клетките на първата колона, чиято стойност е по-голяма от 2 и по-малка от 6. И същата формула може да бъде зададена за втората колона.

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

1. Можете също така да преброите броя на елементите, които отговарят на дадено условие. Например, нека Excel изчисли колко числа имаме, по-големи от 3.

=COUNTIF(B3:B9,">3") =COUNTIF(C3:C9,">3")

1. Резултатът от всички формули ще бъде както следва.

Математически функции и графики

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

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

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

Както казахме по-рано, растежът на експонентата е много по-бърз от този на обичайното кубично уравнение.

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

Всичко описано по-горе е подходящо за модерни програми от 2007, 2010, 2013 и 2016 г. Старият редактор на Excel е значително по-нисък по отношение на функции, брой функции и инструменти. Ако отворите официалната помощ от Microsoft, ще видите, че те допълнително посочват в коя версия на програмата се е появила тази функция.

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

1. Посочете някои данни за изчисление. Кликнете върху произволна клетка. Кликнете върху иконата "Fx".

1. Изберете категорията "Математика". Намерете функцията "SUM" и щракнете върху "OK".
1. Можете да опитате да преизчислите във всеки друг редактор. Процесът ще бъде абсолютно същият.

Заключение

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

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

Освен това е важно да запомните, че формулите трябва да започват със символа "=" (равно на). Много начинаещи потребители забравят за това.

Примерен файл

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

Видео инструкция

Ако нашето описание не ви е помогнало, опитайте да гледате прикачения видеоклип по-долу, който разказва по-подробно основните точки. Може би правите всичко както трябва, но пропускате нещо. С помощта на това видео трябва да се справите с всички проблеми. Надяваме се, че тези уроци са ви помогнали. Проверявайте ни по-често.

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

СВЪРЗВАНЕ

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

ДОЛНА ЛИНИЯ

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

КАПИТАЛ

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

ПРАВИЛНО

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

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

DLSTR

Ляво и дясно

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

PSTR

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

ТОЧНО

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

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

ТРИМ

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

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

Чрез прилагане на функцията ТРИМкъм стойността на клетка A1, ще премахнем всички допълнителни интервали от нея и ще получим правилния резултат:

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

ПОВТОРЕНИЕ

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

НАМИРАМ

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

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

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

ТЪРСЕНЕ

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

ЗАМЕСТВАНЕ

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

Следната формула замества всички срещания на думата "Excel" с "Word":

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

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

ЗАМЕНИТЕ

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

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

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

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

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

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

Метод 1: Използване на амперсанд

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

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

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

Когато пишем текст преди формула, се придържаме към следния синтаксис. Веднага след знака "=" отворете кавичките и напишете текста. След това затворете кавичките. Поставяме знака амперсанд. След това, ако трябва да въведете интервал, отворете кавичките, поставете интервал и затворете кавичките. Щракваме върху ключа Въведете.

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

Метод 2: Използване на функцията CONCATENATE

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

CONCATENATE(текст1, текст2, ...)

Общо този оператор може да има от 1 преди 255 аргументи. Всеки от тях представлява или текст (включително числа и всякакви други знаци), или връзки към клетки, които го съдържат.

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

1. Изберете празна клетка от колона "Крайна цена". Кликнете върху иконата "Вмъкване на функция"разположен вляво от лентата с формули.



2. Активирането е в ход Помощници за функции. Преминаване към категория "Текст". След това изберете името "СВЪРЗВАНЕ"и щракнете върху бутона Добре.



3. Стартира се прозорецът с аргументи на оператора СВЪРЗВАНЕ. Този прозорец се състои от полета с имена "Текст". Броят им достига 255 , но за нашия пример се нуждаем само от три полета. В първия ще поставим текста, във втория ще поставим връзка към клетката, съдържаща формулата, а в третия ще поставим отново текста.

   Поставете курсора в полето "Текст1". Поставете думата "Обща сума". Можете да пишете текстови изрази без кавички, тъй като програмата сама ще ги постави.

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

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

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



4. Резултатът се показва в предварително избрана клетка, но, както виждаме, както в предишния метод, всички стойности се записват заедно без интервали.



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

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

   CONCATENATE("Общо";" ";D2;" ";"рубли")

   Щракваме върху ключа ENTER. Сега нашите стойности са разделени с интервали.



6. Можете да скриете първата колона, ако искате. "Крайна цена"с оригиналната формула, за да не заема допълнително място на листа. Просто премахването му няма да работи, тъй като това ще наруши функцията СВЪРЗВАНЕ, но е напълно възможно да премахнете елемента. Щракнете с левия бутон на мишката върху сектора на координатната лента на колоната, която трябва да бъде скрита. След това се маркира цялата колона. Кликнете върху селекцията с десния бутон на мишката. Стартира се контекстното меню. Изберете елемент в него. "Крия".



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

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

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

Първо, нека вземем пример за 3 текстови функции на Excel:

1. UPPER - Тази текстова функция променя всички букви в една дума на главни, главни букви.
2. LOWER - Тази функция преобразува всички текстови знаци в малки, малки букви.
3. PROPER - функцията променя само първата буква във всяка дума в главна, голяма.

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

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

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

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

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

Лявата страна на формулата използва допълнителната функция LEFT:

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

1. Текст - връзка към клетка с оригиналния текст.
2. Брой_символи - броят върнати знаци от лявата страна (от началото) на изходния текст.

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

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

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

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

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