Excel има някои невероятни текстови функции, които могат да ви помогнат при работа с текстовите данни.
В някои случаи може да се наложи да изчислите общия брой знаци в клетка/диапазон или колко пъти се появява конкретен знак в клетка.
Въпреки че има функцията LEN, която може да преброи броя знаци в клетка, можете да направите и останалото с комбинация от формули (както ще видим по -късно в примерите).
В този урок ще разгледам различни примери, при които можете да броите общо или конкретни знаци в клетка/диапазон в Excel.
Пребройте всички знаци в клетка
Ако просто искате да получите общ брой на всички знаци в клетка, можете да използвате функцията LEN.
Функцията LEN приема един аргумент, който може да бъде текстът в двойни кавички или препратка към клетка към клетка, която има текста.
Да предположим например, че имате набора от данни, както е показано по -долу и искате да преброите общия брой знаци във всяка клетка:
По -долу е формулата, която ще направи това:
= LEN (A2)
Сама по себе си функцията LEN може да не изглежда много, но когато я комбинирате с други формули, тя може да направи някои прекрасни неща (като например да получите броя на думите в клетка или да разделите име и фамилия).
Забележка: Функцията LEN ще преброи всички знаци в клетка, било то специален знак, числа, препинателни знаци и интервали (водещи, последни и двойни интервали между думите).
Тъй като функцията LEN отчита всеки знак в клетка, понякога може да получите грешен резултат в случай, че имате допълнителни интервали в клетката.
Например, в случая по -долу, функцията LEN връща 25 за текста в клетка A1, докато трябва да е била 22. Но тъй като брои и допълнителни символи на интервал, получавате грешен резултат.
За да избегнете преброяването на допълнителни интервали, първо можете да използвате функцията TRIM, за да премахнете всички водещи, последни и двойни интервали и след това да използвате функцията LEN, за да получите реалния брой думи.
Формулата по -долу ще направи това:
= LEN (TRIM (A2))
Пребройте всички знаци в диапазон от клетки
Можете също да използвате функцията LEN, за да преброите общия брой знаци в целия диапазон.
Да предположим например, че имаме един и същ набор от данни и този път, вместо да получа броя на знаците във всяка клетка, искам да знам колко има в целия диапазон.
Можете да направите това, като използвате формулата по -долу:
= СУМПРОДУКТ (LEN (A2: A7)))
Позволете ми да обясня как работи тази формула.
В горната формула частта LEN на функцията поема цял диапазон от клетки и брои знаците във всяка клетка.
Резултатът от функцията LEN ще бъде:
{22;21;23;23;23;31}
Всяко от тези числа представлява броя на знаците в клетката.
И когато използвате функцията SUMPRODUCT с нея, тя просто ще добави всички тези числа.
Сега, ако се чудите защо не можете да използвате SUM вместо SUMPRODUCT, причината е, че това е масив и SUMPRODUCT може да обработва масив, но функцията SUM не може.
Ако все пак искате да използвате SUM, можете да използвате формулата по -долу (но не забравяйте, че трябва да използвате Control + Shift + Enter, за да получите резултата вместо обикновен Enter)
= SUM (LEN (A2: A7))
Пребройте специфични знаци в клетка
Както споменах, истинската полезност на функцията LEN е, когато се използва в комбинация с други формули.
И ако искате да броите конкретни знаци в клетка (това може да е буква, цифра, специален знак или интервал), можете да направите това с комбинация от формули.
Да предположим например, че имате набора от данни, както е показано по -долу и искате да преброите общия брой думи във всяка клетка.
Въпреки че няма вградена формула за получаване на броя на думите, можете да преброите знаците за интервал и след това да го използвате, за да знаете общия брой думи в клетката.
По -долу е формулата, която ще ви даде общия брой знаци за интервал в клетка:
= LEN (A2) -LEN (SUBSTITUTE (A2, "", ""))+1
Горната формула брои общия брой знаци за интервал и след това добавя 1 към това число, за да получи броя на думите.
Ето как работи тази формула:
- Функцията SUBSTITUTE се използва за замяна на всички знаци за интервал с празно място. След това функцията LEN се използва за преброяване на общия брой знаци, когато няма интервали.
- Резултатът от LEN (SUBSTITUTE (A2, ”“, ””)) след това се изважда от LEN (A2). Това ни дава общия брой знаци за интервал, които има в клетката.
- 1 се добавя във формулата и общият брой думи ще бъде с един повече от общия брой знаци за интервал (тъй като две думи са разделени с един знак).
Обърнете внимание, че в случай че има водещи, последни или двойни интервали, ще получите грешен резултат. В такъв случай е най -добре да използвате функцията TRIM заедно с функцията LEN.
Можете също да използвате същата логика, за да намерите конкретен знак или дума или фраза в клетка.
Да предположим например, че имам набор от данни, както е показано по -долу, където имам различни партиди, където всяка партида има азбука и номер, който да я представя (като A1, J2 и т.н.)
По -долу е формулата, която ще ви даде общия брой пъти, когато всеки месец се създава партида с азбука А:
= LEN (B2) -LEN (ЗАМЕСТНИК (B2, "A", ""))
Горната формула отново използва същата логика - намерете дължината на текста в клетка със и без знака, който искате да преброите и след това вземете разликата между тези две.
В горната формула съм закодирал твърдо символа, който искам да преброя, но можете също да го поставите в клетка и след това да използвате препратката към клетката. Това го прави по -удобен, тъй като формулата ще се актуализира следващия път, когато промените текста в клетката.
Пребройте специфични знаци, използвайки формула, която не е чувствителна към регистъра
Има един проблем с формулата, използвана за преброяване на конкретни знаци в клетка.
Функцията SUBSTITUTE е чувствителна към регистъра. Това означава, че „А“ не е равно на „а“. Ето защо получавате грешен резултат в клетка С5 (резултатът трябва да е 3).
И така, как можете да получите броя на знаците на конкретен герой, когато във всеки случай може да е бил (долен или горен).
Правите това, като правите формулата безразлична. Въпреки че можете да отидете на сложна формула, аз просто добавих броя на знаците в двата случая (малки и главни A).
= LEN (B2) -LEN (SUBSTITUTE (B2, "A", ""))+LEN (B2) -LEN (SUBSTITUTE (B2, "a", ""))
Преброяване на знаци/цифри преди и след десетичното число
Не знам защо, но това е често срещано запитване, което получавам от читателите си и съм виждал в много форуми - като този
Да предположим, че имате набор от данни, както е показано по -долу и искате да преброите знаците преди десетичната запетая и след десетичната запетая.
По -долу са формулите, които ще направят това.
Пребройте знаците/цифрите преди десетичната запетая:
= LEN (INT (A2))
Пребройте знаците/числата след десетичната запетая:
= LEN (A2) -FIND (".", A2)
Имайте предвид, че тези формули са предназначени само за значими цифри в клетката. В случай, че имате начални или последни нули или сте използвали форматиране на персонализирано число, за да покажете повече/по -малко числа, горните формули все пак ще ви дадат значителни цифри преди и след десетичната запетая.
Това са някои от сценариите, при които можете да използвате формули за преброяване на знаци в клетка или диапазон от клетки в Excel.
Надявам се, че урокът ви е бил полезен!
