За буквите и хората

Безплатни 20 урока
регистрирай се и научи

Ако сте вече потребител - Регистриран съм
Видео

Допълнение:

ФУНКЦИИТЕ LEFT и RIGHT

=LEFT(текст; [брой_знаци])              =LEFT(text, [num_chars])           - връща зададения брой първи знаци от избрания текст.

=RIGHT(текст; [брой_знаци])            =RIGHT(text, [num_chars])         - връща зададения брой последни знаци от избрания текст.

  • Текст / Text - Текстът от които искаме да извлечем определен брой знаци;
  • Брой_знаци / Num_chars -  Броя знаци, които искаме функцията да извлече.

ФУНКЦИЯТА MID

=MID(текст; начален_ном; брой_знаци)   =MID(text, start_num, num_chars)   

  • Текст / Text - Текстът от които искаме да извлечем определен брой знаци;
  • Начален_ном / Start_num - Позицията на първия знак, който искаме да извлечем от текст;
  • Брой_знаци / Num_chars - Броя знаци, които искаме функцията да извлече.

ФУНКЦИИТЕ SEARCH И FIND

=SEARCH(намери_текст;в_текст;[начален_ном])         =SEARCH(find_text,within_text,start_num)

=FIND(намери_текст;в_текст; [начален_ном])                     =FIND(find_text, within_text, [start_num])

  • Намери_текст / Find_text     -   Текстът, който искаме да намерим;
  • В_текст / Within_text             -   Текстът, в който търсим стойността на аргумента намери_текст;
  • Начален_ном / Start_num     -    Номерът на знака в аргумента в_текст, от който искате да започне търсенето.

    Основната разлика между Search и Find е, Search не отчита големи и малки букви, доката Find отчита.

 

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

Тест за преминаване към следващия урок

 
С какво формулата Find се различава от Search?
Search търси текст независимо от главни/малки, а Find ги взима предвид
Search разрешава да се търси след първата буква, а Find - не
Find търси текст независимо от главни/малки, а Search ги игнорира при търсенe
 
Коя от следните формули ни позволява да намерим точно позицията на дадена буква в определена клетка?
Len
Mid
Search

Въпроси и отговори

  • А как да променя голям списък с имена на кирилица със съответстащите им на латиница?

    Daniela:

    В урок 3 "за буквите и хората" от модул 1има списък с имената на кирилица и в друга колона на латиница. Може би не му е точно тук мястото да питам, но не знам къде точно.

    Единия начин, който знам е чрез find and replace. Но трябва буква по буква. Много бавно и трудоемко.

    Много начини пробвах в ексела, но не се получи.

    Порових се в нета, а там имало някакви макроси, не ми е ясно какви точно.

    Ако се напишат отстрани в две колони българската азбука и съответстващите им знаци на латиница. И ако има такава формула да ги замести?

    И благодаря предварително за отделеното време за отговор.

    Daniela:

    Току що се сетих и за варианта - File/ options / proofing/ AutoCorrect Options /AutoCorrect /Replace: .... With: ........

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

    Витали Бурла:

    Здравейте Даниела,

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

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

    Ето как става:

    Нужни са ви:

    • търпение :)
    • мапинг на буквите по ваше желание (т.е. дали българско В ще е на латиница W или V
    • доста помощни колони (в зависимост от дължината на имената)
    • формулите Concatenate, Proper, и Vlookup, Mid, Column

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

    Това ще ви отнеме около 2 мин, но е малко досадно :) Нека зададем име на този масив/диапазон, да кажем Letters

    Нека сега в клетка а2 сложим Иван Петров и да имаме свободни колоните вдясно За да стане Ivan Petrov, правим следното Стъпка 1) разбиваме името на букви: в клетка b2 пишем: =MID($A$2, COLUMN()-1,1) Резултатът ще бъде просто буквата "И". Изтеглете формулата колкото е нужно надясно. Специално с това име - до колона L. Стъпка 2) "превод" на латиница: започваме от клетка b3, където пишем: =IFERROR(VLOOKUP(B2,letters,2,0), " ") Стъпка 3) събиране на буквите: в клетка а3 пишем =PROPER(CONCATENATE(B3,C3,D3,E3,F3,G3,H3,I3,J3,K3,L3)) Proper слага главна буква в началото на думата и след всеки интервал.

    И така, вече имате Ivan Petrov в а3 :)

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

    Peter:

    Даниела, намерих едно място, можеш да опиташ тук - http://forum.abv.bg/abv/lat2cyr.php Пробвах с 3-4 имена, прави някакви транслации :)

    Peter:

    Всъщност, това по-горе е латиница - кирилица, а тук - обратно - http://translator-bg.com/content/view/68/63/lang,bg/

    Daniela:

    Благодаря за отговорите на Витали и Peter. Като си пусна компютъра, ще си поиграя.

    Daniela:

    С моя скапан късмет, играта стана много голяма. В момента разполагам с ексел 2003. Чудо!! В началото всичко тръгна по реда си. Но стигнах до формулата IFERROR и опаааа. Нема такова животно. Ама не се отказвам. Щом я няма, няма. Използвах само VLOOKUP. Тръгна добре, ама има и интервал. Там ми даде грешка #N/A.
    И с нея се справих. Вмъкнах празен интервал в Letters. И се получи. Добре че след това , всичко го имаше в тоя престарял ексел.

    Благодаря на Витали за по-голямата игра. Беше ми забавно.

    Daniela:

    Peter, благодаря ти за сайтовете. Разгледах ги, но предпочитам в ексела да ги обработвам данните. Пък и някои имена ги преобразува малко смешно. Когато сам си направиш настройките, както искаш е друго. Това, което ми хрумна е просто идея. Просто видях някой да преобразува име по име.

    Peter:

    Моето е workaroud - метод, т.е. ако си на зор и бързаш да свършиш работа - временно решение, ... което понякога може да стане постоянно :)

    Daniela:

    Да, понякога можеш да попаднеш на точно това, което ти трябва. Колкото повече варианти - толкова по-голям избор.

    Лора Тонева:

    За мен варианта с макрос е най-лек. Освен това веднъж направен, макросът може да се ползва постоянно, ако се налага такава операция. Не мога да кажа, че съм особено добре запозната с макросите, но пробвах и записах макрос с Find/Replace -> Replace All за първите 2-3 букви. После с копиране може да се допълни и за останалите букви, за по-бързо и лесно. Поизчистих това, което мисля, че е излишно от кода и макроса си работи. Предимство е, че може да си заложиш на коя буква от кирилицата, коя (кои, ако са повече) от латиница искаш да съответства. На мен за буквите до "ж", например, макросът ми изглежда така: Sub Macro1() ' ' Macro1 Macro '

    ' Cells.Replace What:="а", Replacement:="a", LookAt:=xlPart, SearchOrder _ :=xlByRows Cells.Replace What:="б", Replacement:="b", LookAt:=xlPart, SearchOrder _ :=xlByRows Cells.Replace What:="в", Replacement:="v", LookAt:=xlPart, SearchOrder _ :=xlByRows Cells.Replace What:="г", Replacement:="g", LookAt:=xlPart, SearchOrder _ :=xlByRows Cells.Replace What:="д", Replacement:="d", LookAt:=xlPart, SearchOrder _ :=xlByRows Cells.Replace What:="е", Replacement:="e", LookAt:=xlPart, SearchOrder _ :=xlByRows Cells.Replace What:="ж", Replacement:="j", LookAt:=xlPart, SearchOrder _ :=xlByRows

    End Sub

    Лора Тонева:

    Още веднъж за по-четливо , че нещо ми се разместиха редовете, когато изпратих отговора:

    Sub Macro1() ' ' Macro1 Macro

    ' Cells.Replace What:="а", Replacement:="a", LookAt:=xlPart, SearchOrder _ :=xlByRows Cells.Replace What:="б", Replacement:="b", LookAt:=xlPart, SearchOrder _ :=xlByRows Cells.Replace What:="в", Replacement:="v", LookAt:=xlPart, SearchOrder _ :=xlByRows Cells.Replace What:="г", Replacement:="g", LookAt:=xlPart, SearchOrder _ :=xlByRows Cells.Replace What:="д", Replacement:="d", LookAt:=xlPart, SearchOrder _ :=xlByRows Cells.Replace What:="е", Replacement:="e", LookAt:=xlPart, SearchOrder _ :=xlByRows Cells.Replace What:="ж", Replacement:="j", LookAt:=xlPart, SearchOrder _ :=xlByRows

    End Sub

    Daniela:

    Благодаря на Лора за отговора . Е, всъщност се връщаме на варианта find and replace,само че с макроси. Засега не съм толкова на ти с макросите, но мога що годе да се справям. Мога да си запиша действията и да си приложа бутонче за съответния макрос. Не е проблем и да се коригира нещо. Инак ще ми е малко досадно да изписвам ред по ред. Ако казуса се повтаря е логично да се приложи макрос или другата формула на Витали. Например за служителите в една фирма. А това е един масив от данни, който може да се променя постоянно.

  • Защо Left, Mid, Right имат предимство пред Data>Text to columns?

    Ваня Драганова:

    Тези команди са определено много интересни. При това въвеждане на данни обаче Text to columns с разграничител space би решило проблема по-бързо. Тъй като досега не съм ползвала показаните команди, бих искала да помоля за пример. който да демонстрира кога употребата им е за предпочитане пред Text to columns.

    Лора Тонева:

    Здравейте Ваня, на мен в практиката ми се е налагало да използвам тези функции доста. Ето един реален пример: Имаме склад с артикули, чиито номенклатурни номера са от вида А2452201429. В номенклатурния номер няма интервали и в списъка, който се експортира в Ексел от складовата програма, номерата се експортират в този вид. Артикулите са около 5000 и са подредени в склада на следния принцип - първо по групи и след това по възходящ ред в рамките на групата, а групата са втората тройка цифри от номенклатурния номер ( в случая с този номер - 220). За да се ревизира склада, трябва да сортираме по подходящия начин списъка. За целта с mid отделям 3 символа, започвайки от петия, за да извадя групата. После сортирам по колоната с група като първи признак и по целия номер като втори. Сигурно друг би предложил и нещо по-добро, но тъй като аз не съм сетила за друго, съм го правила по този начин многократно и това ми е спестило доста работа.

    Витали Бурла:

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

    Пример: имате списък от хора с ЕГН-та и трябва да изчислите бройката хора по генерация (до 1960, до 1980 итн). ако в клетка B2 имате ЕГН, то в C2 с Left(b2, 2) взимате годината на раждане. За да стане цифрова стойност умножавате по 1 т..е =1*Left(b2,2). Сега имате двуцифрено число и можете да приложите формулата навсякъде в списъка, дори ако прибавяте нови имена и ЕГН-та към него.

    Имайте предвид, че с Text-to-Columns, вие предполагате хармонизирани, кохерентно организирани данни. Само че, отдел Х може да не обичат Excel и да ви пратят данни така (всичко в една колона)

    Иван - София -5 Мария: Пловдив 4 Георги; Лом; 3 Ваня (!) - Русе: 7 Петър -ловеч :8

    ..а на вас ви трябват само градовете + числата.. Този списък разбира се няма да е 6 реда, а примерно 200, за да по-готино :) Тук T-t-C просто няма да проработи, каквото и да ви трябва да извадите. Затова можете да позлвате тези формули (Left, Right, Mid) в комбинация със , Search, Find, Trim.

    Поздрави,

    Витали

    Daniela:

    Витали, това с умножаването по 1 ми хареса.

    Александър Вецов:

    Витали здравей! Прави ми впечатление, че разделяш различните аргументи във формулите със запетея " , ", а аз го правя с точка и запетя " ; ". Това от настройките на Ексел ли зависи или от версията, която ти използваш?

    Дарина Иванова:

    От настройките на Ексел е.

    Краси Кръстев:

    Здравейте, и от мен

    Да добавя една тема по отношение на настройките:

    Ето какво да правите, ако Excel ви обръща числата в дата

    мисля че ще е полезна в дискусията.

  • Как да премахване на оцветяването?

    Десислава Петрова Дженкова:

    Здравейте, със някаква клавишна комбинация ли премахнахте червеното оцветяване, след като изтрихте find?

    Краси Кръстев:

    Здравей, Десислава.

    Това което лектора прави във видеото за да изтрие оцветяването на клетката е да отиде в празна клетка, след което да я копира с Ctrl + C , след това директно с Ctrl + V поставя копираното форматиране въвху клетката с червен цвят и готово.... в червената клетка вече е формата на неоцветената клетка, т.е. от червена клетка става без цвят.

  • Как да разделим текста без да се виждат колоните за интервалите?

    Ана Йонкова:

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

    Дарина Иванова:

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

    Ана Йонкова:

    Искам да кажа, че колони М и О ,от урока, не е нужно да се виждат, ако таблицата е много голяма.