#62 Броене на знаци или как прецаквам усилията на колеги

Реших да постна нещо за да не съм капо тази година:) А и да покажа, че съм жив и здрав и поддържам блога. Но и вие сте си виновни, че не ме тормозите и то със задачки, които биха били полезни и за останалите читатели:)

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

Благодаря предварително!!!" 

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

Задача 1. Да се преброят срещанията на даден символ в текст.

За това имам цяла тема. #017 Търсене и броене със Substitute ! Добре е да си я опресните, но ако на някой не му се чете ето и краткия вариант.

Идеята е следната. Изтрива се търсения символ. Изтриването се извършва чрез заместване (функция Substitute) на символа с "" (празени стринг). След което се намира разликата между дължината на оригиналния текст и новия текст, който е без търсения символ. Разликата показва броя срещания на символа.

Пример: Да се намеря броя появявания на символа "." в текст.

=LEN(A1)-LEN(SUBSTITUTE(A1;".";""))

Задача 2. Да се преброят срещанията на два символа в текст

Вариант 1: По пътя на логиката може да го направим като дублираме формулата.

Пример: Да се намери броя на срещанията на символите "." и "*" в текст.

=LEN(A1)-LEN(SUBSTITUTE(A1;".";""))+LEN(A1)-LEN(SUBSTITUTE(A1;"*";""))

Вариант 1b: Не се изискват две висши да се сетим да обединим двете дължини:

=2*LEN(A1)-LEN(SUBSTITUTE(A1;".";""))-LEN(SUBSTITUTE(A1;"*";""))

Вариант 2: Сега остава да се сетим как да обединим двете резултатни дължини.

=2*LEN(A1)-SUM(LEN(SUBSTITUTE(A1;{".","*"};"")))

Изпращаме МАСИВ, който съдържа двата символа. Грешка е, ако се изпратят като един стринг ".*", защото ще подмени само там, където двата символа са един до друг! Чрез функцията Sum намираме сумата на двата резултата (двете дължини).

Задача 3. Да се преброят нечетните цифри в цяло число (задачата на юнака:)

Би трябвало да се сетили как става "магията":) Просто символите не са 2 ами 5!

=5*LEN(A2)-SUM(LEN(SUBSTITUTE(A2;{1,3,5,7,9};"")))

Excel e достатъчно "умен" и не изисква да се преобразува числото в текст, но ако някой го дразни, че разчитаме на "интелекта" на програма то тогава може да преобразува явно:

=5*LEN(TEXT(A2;"0"))-SUM(LEN(SUBSTITUTE(TEXT(A2;"0");{1,3,5,7,9};"")))

Също така няма смисъл да заграждаме в кавички цифрите, но може и така:

=5*LEN(TEXT(A2;"0"))-SUM(LEN(SUBSTITUTE(TEXT(A2;"0");{"1","3","5","7","9"};"")))

Ми това е:) Решихме задачата на младежа и на една камара бъдещи "потърпевши" от това задание:):) И искрени извинения към колегата (учител или преподавател), който дава това задание. В интерес на истината му завиждам, че има такива обучаеми на които може да даде такава задача, която хич не е лесна.

Успех:)

П.П. Обещавам да не решавам задачите на други хитреци! Не търсете лесния вариант. Няма как чрез StackOverflow и други помощници да се генерира БВП! До време е. Трябват хора, които сами решават проблемите!

#60 Интервали и интервал без разделяне

Една бърза тема за тези, които използват Copy/Paste от уеб страници. Попаднаха ми данни имащи следния вид:

Данни

 

 

 

 

 

 

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

Преобразуване на данни

 

 

 

 

 В долната клетка нещата изглеждат подозрително. Използвах функции и "трикове" за преобразуване на текст в число. Неуспешно за клетката A3!:( Вижда се, че Excel позволява използването на стойността в математически израз (колонка F), въпреки че не е число (проверката в колонка B)!

Преобразуване на текст в число

За това се заех по-обстойно с втората клетка. Реших да проверя кой е третия символ в клетката. Стори ми се подозрителен:) Това извърших чрез функцията:
 =CODE(MID(A3;3;1)), която върна отговор 160! "Нормалния" интервал има код 32.

Къде е проблема?! Кодът 160 е на символа "non-breakable space". Това е интервал, който се разглежда като символ и се разглежда като част от думата (например 100 км/ч.). В HTML това е символа &nbsp,  а в MS Word се въвежда чрез Ctrl+Shift+Space. Явно в текста който е копиран в Excel е използван този символ. Оказва се, че в много от сайтовете използват този символ за подредба на данните.

Решения:

1. Чрез търсене и замяна

Търсене и замяна

Забележка: Задържа се клавиша Alt и се набира от ЦИФРОВАТА клавиатура (не от основния блок) 0160.

2. Чрез функция

=VALUE(SUBSTITUTE(A3;CHAR(160);""))

Забележка: Между кавичките няма нищо!

Ми това е:)
Успех и умната с интервалите (и с Copy/Paste):):)

#019 Контролни списъци които зависят един от друг (Adv)

Както вече споменах в тази тема ще разгледам по-сложен контрол с помощта на списъци които зависят един от друг. Ако не сте прочели #018 (и свързаните с нея теми) го направете сега. Методът използван в #018 има два недостатъка. 

Първият недостатък в използвания в #018 е във фактът, че ако в първия контролен списък има текст с интервали не е възможно да създадем именувана област съдържаща интервали. Този проблем се решава лесно. Ако в първия списък имаме стойност например "малко дете", създаваме именувана област с име "малкодете" (без интервали!). След което видоизменяме контролиращия списък (в примера #018 клетка B2) като =indirect(substitute(B1," ",""). Просто използваме вече дискутирана функция Substitute за премахване на интервалите. Елементарно;)

Вторият проблем е по-сложен и изисква малко по-сложни действия. Проблемът е, че първия списък е статичен. Потребителя не може да въвежда стойности в него. Когато направим и първия контролен списък динамичен се оказва, че няма как да използваме Indirect по простата причина, че при разработката на приложението ние не знаем какво ще реши да въведе потребителя в първия списък (и респективно да създадем съответната именувана област) :( 

Пример: Да се оценяват студенти в зависимост от различни методи за оценки.

Оценките да зависят от избрания метод (държава)

Оценките на студентите (жълтите клетки) да са съобразени избрания метод за оценяване в клетката B1. Потребителя да може да добавя и редактира начините за оценяване в отделна област.

Да предположим, че областта за видовете оценки започва от клетка F1. (Hint! По-добре е самата област да бъде на друг лист!)

За за въвеждане на видовете оценявания на начало клетка F1

В посочената зона потребителя ще въвежда в първия ред с думи типът на оценяването и вертикално разрешените стойности. Дал съм примери. За повече информация може да надзърнете на адрес http://en.wikipedia.org/wiki/Grade_%28education%29 (Типът оценяване от колонка L няма да го намерите там):):)  Както се вижда различните начини на оценяване имат различен брой разрешени стойности.

1. Създаване на динамична именувана област за видовете оценявания. Тук се използва техниката от #001. За име на областта задайте "GradeType" а за Refers to: =OFFSET(Sheet1!$F$1,0,0,1,COUNTA(Sheet1!$1:$1)-2). Областта е хоризонтална. (NB! това "магическо" оцветено в червено -2 е заради фактът че на първи ред имаме две пълни клетки в повече (А1 и В1)!! Ето за това и препоръчвам зоната да бъде някъде отделно където няма да има такива "смущения"!)

Команда Formulas/Define name

2. Създаване на именувана област за оценяване. Тази област трябва да е динамична вертикално в зависимост от броя на оценките и зависи от стойността на B1.  Задава се име на областта "Grade" и сочи към формулата :

=OFFSET(Sheet1!$F$1,1,MATCH(Sheet1!$B$1,GradeType,0)-1, COUNTA(OFFSET(Sheet1!$F$1,1,MATCH(Sheet1!$B$1,GradeType,0)-1,100,1)))

Не се плашете много:) Вече говорихме за Offset. Сигурно скоро ще има тема и за Match. В случая Match намира колонката на съответния тип оценяване в зависимост от съдържанието на B1.

MATCH(Sheet1!$B$1,GradeType,0)-1 (NB! -1 е защото Offset Брои от НУЛА!).

Часта : COUNTA(OFFSET(Sheet1!$F$1,1,MATCH(Sheet1!$B$1,GradeType,0)-1,100,1)))

намира колко пълни клетки има вертикално.   Тъй като трябва да броим в някаква област предварително задаваме област от 100 (множко е) реда и с CountA намираме реалния брой редове. 

3. Контрол на клетката с видовете оценявания. За клетката B1 се изпълнява Data Validation с контрол по списък =Gradetype след което се избират жълтите клетки и се задава DataValidation по списъка Grade.

Това е:) "Само" три стъпки:)

#017 Търсене и броене със Substitute

Функцията Substitute има за цел да подменя един текст с друг... Синтаксисът  и е =Substitute( текст в който се подменя, стария текст, новия текст, [коя появя на текстът да се подмени, ако се пропусне се подменят всички намерени текстове] )

Ето два примера:

• =SUBSTITUTE("0000001","0","*")  резултат: ******1
• =SUBSTITUTE("003592300838","00","+",1) Резултат: +3592300838 (замества със знака "+" само първите намерени две нули! (този пример е само за илюстрация и ако работите с мобилни номера ще се наложи по-сложни трикове!)

Функцията обаче се използва и в по-сложни формули по малко по-специален (и често труден за разбиране) начин.

Да се преброи колко пъти даден текст (или знак) го има в друг текст. Ето израза:
=Len(текстът в който търсим)-Len(Substitute(текстът в които търсим, текстът (знакът) който искаме да преброим, "") )
Трикът е, че се премахва търсения текст (подменя се с "") и се намира разликата между дължините на оригиналния и променения текст.

Пример 1: Да се преброят тиретата в даден текст.
Отговор: =LEN("123-345-678-987")-LEN(SUBSTITUTE("123-345-678-987","-",""))

Пример 2: Да се преброят интервалите в дадена клетка
Отговор: =LEN(A7)-LEN(SUBSTITUTE(A7," ","")) (Между първите кавички има интервал, а между вторите няма!)

Второ приложение е да се намери позицията на N-тото появяване на даден текст/знак.
=Find(char(7),Substitute(текст в който търсим, текст който търсим, Char(7), появяването което търсим)

Тук се прилага следния трик. Подменя се този текст/символ с нещо което знаем че го няма в текстът. Например  с Char(7) (камбанка):) Припомням за последния параметър на Substitute който позволява избирателна промяна. След което се намира позицията на този символ със Find.

Ето примери:
Пример 3: Да се намери позицията на ВТОРОТО тире в текст:
Отговор: =FIND(CHAR(7),SUBSTITUTE("123-345-678-987","-",CHAR(7),2))

Пример 4: Да се намери позицията на ТРЕТИЯ интервал в дадена клетка
Отговор: =FIND(CHAR(7),SUBSTITUTE(A7," ",CHAR(7),3))


И като десерт два сложни примера за тези които искат да си размърдат мозъците:)))

Пример 5: Да се намери броят на цифрите в дадена клетка. Не се броят точки и други символи. Само цифрите от нула до девет!
Отговор: =SUMPRODUCT(LEN(A8)-LEN(SUBSTITUTE(A8,ROW(INDIRECT("1:10"))-1,"")))

Тук се прави вътрешен цикъл за да се използва трикът с броенето. Единия вариант е в да се сложи масив {0,1,2,3...9} . Другия вариант е с Row (беше дискутиран вече в по-стари теми). Поради фактът че нулев ред няма (т.е. Row(0) ще даде грешка!) се брои от едно до десет и се вади единица и по този начин се получава броене от нула до девет! Задал съм в червено този "цикъл".

Пример 6. Да се извлече ПОСЛЕДНАТА дума от клетка. Думите са разделени с интервал. Не знаем колко думи има! Думите са повече от една (ако не сме сигурни, трябва да се направи проверка, която в отговора съм спестил!).

Отговор:
=MID(A11,FIND(CHAR(7),SUBSTITUTE(A11," ",CHAR(7), LEN(A11)-LEN(SUBSTITUTE(A11," ",""))))+1,32767)

Тук се използва функцията MID за извличане. За намирането на началната позиция се използва комбинация от двата метода за търсене и броене. Чрез броене се намира броя (т.е. колко интервала има) и след това се намира позицията на последния (=броя) интервал. Вместо да се прави сложна гимнастика за определяне броя на символите за извличане просто се посочва 32767 (текстът в една клетка не може да бъде повече от 32767!) и функцията Mid взема всичко до края. (За лимитите ето ви справка http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010342495.aspx )

Това е:)