#043 Как се коригират скали и има ли "Тъп 0" като оценка:)

Преди време получих писмо (и чинно му отговорих):) Но реших, че си заслужава да напиша отговора и в тема за да съм полезен и на други. Ето писмото:

Попаднах случайно на блога ти за трикове в Ексел, докато търсех информация за функцията CHOOSE. Изглежда интересен и полезен, но аз съм още начинаещ. Та в този смисъл в последно време се сблъсквам доста често с Ексел. Озадачи ме следното: =CHOOSE(O4-1;"Слаб";"Среден";"Добър";"Мн.добър";"Отличен").

Защо от О4 изваждаме една единица.

Лесно се намира в помощната информация на Еxcel, че Choose служи за преобразуване на числова стойност. Първия параметър е стойността, а останалите са отговорите в зависимост от нейната стойност. Отговорите са подредени по следния начин "Отговор при стойност на първия параметър ЕДНО", "Отговор при стойност на първия параметър ДВЕ" и т.н. Т.е. скалата на отговрите е 1,2,3,4,5.... Да разгледаме скалата от оценките... Тя е 2,3,4,5,6... Т.е. по някаквъ начин ние трябва да изравним двете скали:

Вариант 1: Корекция на отговорите. Създаваме ШЕСТ отговора. Този вариант е по-скоро за илюстрация и по-добре не го помнете:)

=CHOOSE(А1;"";"Слаб";"Среден";"Добър";"Мн.добър";"Отличен") 

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

Вариант 2: Корекция на скалата с оценките . Тук НАМАЛЯВАМЕ оценката да се преобразуват в числови стойности от ЕДНО до ПЕТ! И задаваме ПЕТ отговора.

=CHOOSE(А1-1;"Слаб";"Среден";"Добър";"Мн.добър";"Отличен") (това е примерът от писмото)

Ако оценката е 2 ще стане едно и ще се върне първия отговор ("Слаб"). При три ще стане две и ще се върне втория отговор и т.н... 

Да продължим със следния казус. Един учител по мое време имаше малко по-различна скала за оценяване. Тя беше следната "Тъп 0", "Лош 1", "Слаб 2", "Среден 3", "Добър 4", "Мн. добър 5", "Отличен 6":):)

Тук скалата на оценките (стойността според която ще връщаме резултат) започва от 0!! Тук помага сама втория вариант на решение от предходния пример:

=CHOOSE(А1+1;"Тъп";"Лош";"Слаб";"Среден";"Добър";"Мн.добър";"Отличен")

Тук изравняването се извършва чрез ДОБАВЯНЕ на единица към оценката за да се преобразува в поредица запозвачваща от едно. При оценка 0 ще върне ПЪРВИЯ отговор ("Тъп") и т.н.

Мисля че сте разбрали, че трябва стойностите на израза да се преобразува в поредица 1,2,3,4....

Да видим сега малко "забавления" за напреднали:) Малко решения на същия пример (с "нормални оценки") решена чрез други функции. Идеята е да имате богат арсенал от оръжия за да се по-гъвкави в различни ситуации.

Вариант 1: Vlookup с "вграден" масив.

=VLOOKUP(A2;{2\"Слаб";3\"Среден";4\"Добър";5\"Мн. Добър";6\"Отличен"};2;FALSE)

(вижте #041!). Разбира се може да го направите и по "бабешкия" начин, като направите отделна табличка с оценките и "словом" на отделен лист. И да търсите с Vlookup в нея.

Вариант 2: Комбинация Index/Match.

=INDEX({"Слаб";"Среден";"Добър";"Мн. Добър";"Отличен"};MATCH(A1;{2\3\4\5\6};0))

 (вижте темите за Index и  Match)

Вариант 3: CSE вариант на предходната формула с автоматично генериран масив

от числа. (въвежда се с Ctrl+Shift+Enter! без {} около формулата!)

{=INDEX({"Слаб";"Среден";"Добър";"Мн.Добър";"Отличен"};MATCH(A2;ROW(INDIRECT("2:6"));0))}

 Вариант 4: Любимия на всеки даскал физкултурник преквалифицирал се като информатик:)

(хич не съм му фен на този вариант, но всеки ученик (и студент) го въртят на шиш докато не го научи да го прави по този начин):):)

=IF(A1=2;"Слаб";IF(A1=3;"Среден";IF(A1=4;"Добър";IF(A1=5;"Мн. Добър";"Отличен")))) 

Та опитайте се да ги разберете тези варианти (мога и още да ви дам):) Всеки си има плюсчета и минусчета:) И така никога няма да  получите "Тъп 0":)

Успех:)

#032 Функция SubTotal и защо е по-добра от Sum

Да погледнем #030 в реда за обобщение. Там намираме не Sum и Average ами функцията SubTotal. Да се замислим защо Excel използва нея а не "класическите" функции. SubTotal не се преподава, но е доста по-мощна и гъвкава от Sum и т.н. Та в общи линии първия параметър е число показващо каква е използваната функция а втория е самата зона. Ето стойностите на първия параметър:

Функция_ном (включва скрити стойности)	Функция_ном (игнорира скрити стойности)	Функция
1	101	AVERAGE
2	102	COUNT
3	103	COUNTA
4	104	MAX
5	105	MIN
6	106	PRODUCT
7	107	STDEV
8	108	STDEVP
9	109	SUM
10	110	VAR
11	111	VARP

Вижда се, че имаме две възможности за всяка функция. СЪС и БЕЗ да се включват скритите стойности! Тук се крие силата на тази функция!  По време на правенето или използването  на една таблица "скриваме" редове или колонки както с помощта да Hide или с помощта на филтрите. Сега е моментът да се замислите че SUM смята ВСИЧКО което му е подадено като параметър без да го е грижа какво се вижда и какво не! Т.е. "това което виждате може да НЕ е това което се сумира"!:) За разлика от SUM/Average и т.н. SubTotal ви дава право на избор!

=SubTotal(9,A1:A100) е пълен аналог на =SUM(A1:A100) сумирайки независимо дали са видими, докато =SubTotal(109,A1:A100) ще зависи кои клетки от зоната са видими! Този "малък" на пръв поглед нюанс може да ви създаде главоболия:) Забелязал съм, че рядко се набляга на този "дефект" на класическите функции и сякаш никога няма да мине през акъла на някой да скрива или филтрира:):)

Както казах обаче при таблиците в реда за обобщаване Excel "мъдро" слага правилните функции (т.е. SubTotal с отчитане на скритите редове). Ако не искате това ще се наложи да подмените предложените от Excel функции с "вашите" любими такива:) Чара на SubTotal е че може да работи и като класическа функция, за това просто сменяте първия параметър и сте ОК:)

NB! Да бъдем коректни не винаги е възможно използването на SubTotal! Тя не възприема така наречените 3D зони (зони които са между няколко работни листа). Например =SUM(Sheet1:Sheet4!A1) няма как да я подмените с SubTotal! За това не бързайте да погребвате Sum, Average и т.н. :):)

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

Табличка за SubTotal

Условие: Когато потребителя пипа C1 и C2 да се вижда правилния резултат.

0. Подготовка. Колонките D, E и F са помощни и спокойно може да са на друг лист да не загрозяват пейзажа:) Или просто ги скрийте когато приключите с настройките и видите, че всичко е ОК

1. Дефиниране на имена. Понеже все забравям да направя тема за именуване сега ще пиша много:( Трябват ни три имена "Функции", "Всички", "Видими" за данните в помощните колонки. Най-бързия начин е да изберете клетките от D1 до F12 (данните с имената!) и да изпълните командата Formulas/Define Names/From Selection и да посочите че използвате и заглавния ред и първата колона. Така ще получим освен трите имена на колонките и имената "Average" за първите две стойности, "Count" за вторите и т.н.

Създаване на имена

Резултат от именуването (проверка чрез Formulas/NameManager)

Тук има един малък проблем. Името "Функции" сочи към данните а ние искаме да сочи към имената на функциите. За това трябва да оправим този проблем като редактираме името от Name Manager.

Редактиране на името "Функции"

2. Контрол на C1 и C2. За да може потребителя да избира стойности за контролиране на C1 използваме списъка "Функции",  а за C2 зоната E1:F1. (Вижте #002).

Контрол на C2

3. Формула в C3. Ще дам два варианта да е по-весело:) И да имате теми за размисъл и четене:)

Вариант 1: =SUBTOTAL(INDEX(INDIRECT(C2),MATCH(C1,Функции,0)),A1:A24)

Тук извличаме номера на функцията чрез Index:

INDIRECT(C2) - "Обръща" съдържанието на клетка C2 в зона. (Всички/Видими). Така получаваме зоната от която ще извличаме числото.

MATCH(C1,Функции,0) -  Тук намираме редът от който извличаме търсейки съдържанието на C1 в зоната Функции (за това и се наложи корекция!)

Вариант 2: =SUBTOTAL(INDIRECT(C1) INDIRECT(C2),A1:A28) 

 

Извличането на стойността е в израза:

INDIRECT(C1) INDIRECT(C2) - Това е странен израз. Две зони разделени с интервал. Тук хитростта е че се намира "сечението" на двете зони. Зоната C1 (това е хоризонталната зона на съответната функция) определя реда, Зоната C2 пък представяла колоната. Например ако сме избрали Max и Видими  се търси сечението между E5:F5 (зона Max) и F2:F12 (зона Видими). Сечението (общото между двете зони) е клетка F5. Която и съдържа това което ни трябва!

Явно и тема за зоните трябва да понапиша:)

Успех със SubTotal:)

#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.

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

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

Отново ще дискутираме темата за DataValidation с помощта на списъци. Този път ще се спра на темата как да направим списък който се влияе от друг списък. Няма да е зле отново да прочетете #001, #002 и #004.

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

Свързани списъци

Да направим списък за контрол, който да се променя в зависимост от избрания пол.

Стъпка 1. Контролираме въвежданите стойности в полето Пол. Понеже стойностите са константни може да използваме Data Validation с твърдо зададени стойности. Избираме B1 активираме Data/Data Validation и въвеждаме стойностите в списъка.

Статични стойности за контрол

Стъпка 2. Създаваме списъците за контрол. Въвеждаме данните за облеклата на мъжа и жената. След което се избират данните за мъжа и се задава име  на областта  "мъж" (точно каквато е стойността за валидиране!). По аналогичен начин се създава и именувана област "жена" с облеклата за жената. NB! Не забравяйте да натискате Enter след като въведете името на избраните клетки!

Област с мъжките дрехи

Област с женските дрехи

Стъпка 3. Валидиране на клетките в зависимост от стойността на пола. Избираме клетките които ще контролираме (в случая само клетка B2). Активираме командата Data Validation. Избора се контрол по списък и в полето Source въвеждаме =Indirect(B1).

Свързан списък

Това е:)  Сега когато се избере пол в клетката B1  видовете дрехи се променят. Този пример има недостък, че зоните "мъж" и "жена" са статични. Т.е. ако добавим нови стойности ще се наложи да правим промяна в името. За да направите примера още по лесен за потребителя, вместо посочения от мен начин за именуване използвайте динамично именуване (описано в #001) при създаване на областта "мъж" и на областта "жена" (разбира се няма да бъде A1 ами D1 (Е1) като начало на зона).  Така потребителя сам лесно може да си дописва дрехи в съответната област и тя сама ще се разширява. Успех:)

Цялата "магия" се крие във функцията Indirect. Тя служи за преобразуване съдържанието на клетката B1 в адрес (в случая именувана зона). Както вече обърнах внимание е важно името на зоните да бъде същото както е името на стойностите в B1! Мисля, че сте се досетили, че ако стойностите в първия списък са повече за всяка от тях трябва да създадете съответната именувана област!

Следващата тема ще бъде малко по-сложен начин за свързани списъци:)

#015 Относителни, смесени, абсолютни и още по-абсолютни адреси:)

Тази тема е по-скоро за начинаещи, но ми се иска все пак да обърна внимание. Моят опит със студенти често показва, че се пропускат детайлите, а както знаем дяволът е именно в детайлите.  Става дума за адресите които използваме във формулите. Всеки минал първо ниво на Excel знае, че има два основни вида. Относителни (A1) адреси, които се променят при преместване на формулата и абсолютни ($A$1)  които не се променят. Основните усилие, които се хвърлят от преподавателите е да втълпят на студентите за наличието на тези два вида и разликите между  тях.

Оказва се обаче, че рядко се илюстрира прилагането на междинния тип адреси или така наречените смесени ($A1, A$1) адреси, при които само единия компонент (реда или колоната) е абсолютен (константа) а другия е относителен. Когато проверявам входното ниво на обучаеми по Excel им проверявам знанията с помощта на тази таблица:

Таблица за умножение

Пример: При въведени данни в колонка  A и първия ред (оранжевите области) да се въведе само ЕДНА формула (в клетка B2) която при размножаване да създава таблицата за умножение (жълтите клетки).

Вариант 1 : Формулата в клетка B2: =$A2*B$1. Използват се смесени адреси.  (NB! припомням само за полезния клавиш F4, който променя типът на адресът).  Размножавате надолу и после надясно и готово. За мое съжаление успеваемостта за този пример е нулева. (Не че и аз не водя курсове за начинаещи и това е камък и в моята градинка):) Тази формула е полезно да се знае за да се вникне в детайлите.

Вариант 2: Чрез имена. Изберете клетките от A2 до A11 и в областта за имена  (горе в ляво) им задайте име "redove". (NB! след въвеждане на името натиснете Enter!)

Именуване на област

 По аналогичен начин задайте име на зоната B1:K1 "koloni". След което формулата в клетка B2 е: =redove*koloni . Чисто и просто (това в случая не значи "къпана блондинка":) Умен Excel:) Красиво е... Но както казах е добре все пак да се знаят типовете адреси. Ще имам отделна тема в която ще покажа различните начини на именуване и използване на имената.

Още малко за абсолютните адреси. Да предположим, че искаме да намерим квадратът на съдържанието на клетката A1. Вариантът на формулата е =$A$1^2 (^ е степенуване). Тази формула може да се мести без да се променя адресът на клетката. НО. Абсолютните адреси имат един "дефект" (зависи от гледната точка и конкретния пример). Въпреки че са абсолютни при вмъкване и изтриване на редове или колони (чрез командите Insert или  Delete) те се променят. Може да си направите експеримент да вмъкнете колонка на мястото на колонка A. В този случай формулата ще се видоизмени на =$B$1^2.  Ако изтриете първи ред ще се получи "гадното" съобщение #Ref във формулата, защото редът липсва.

Оказва се, че абсолютния адрес не е на 100% абсолютен!  Запомнете това! Ако искаме наистина абсолютен адрес се използва функцията Indirect("адрес или зона"). Примерът се видоизменя като =Indirect("A1")^2. Тази формула ВИНАГИ ще връща квадратът на съдържанието на клетка A1!

#014 Колко дена има в даден месец

В Analysis ToolPak add-in-са има функция ЕОMONTH, но съм виждал прекалено много инсталации с не инсталирани (не активирани)  add-in модули за това ще дам един по-универсален пример. =Day(Date(Година,Месец+1,0)). Ако искаме самата дата а не броя на дните просто се маха заграждащия Day. Годината е необходима за месец февруари. В останалите случай може просто да се сложи произволна година. Трикът е че взема нулевия ден на СЛЕДВАЩИЯ месец:)

Пример 1: Да се намери броя на дните (брой не дата!) в текущия месец.
Отговор : =DAY(DATE(YEAR(NOW()),MONTH(NOW())+1,0))

 Пример 2: Да се намери последната ден в месеца (като дата) за дата в клетката A1
Отговор: =DATE(YEAR(A1),MONTH(A1)+1,0)

Пример 3: Колко дена има в 2008 година:)
Отговор: =SUMPRODUCT(DAY(DATE(2008,ROW(INDIRECT("1:12"))+1,0)))
Това е мноооого ма много триково решение с цел да ви припомня как се прави вътрешен цикъл във формула:))) И не ви съветвам да се увличате по тях.
Иначе ... =DATE(2008,12,31)-DATE(2008,1,1)+1 :) Явно съм пропуснал да кажа, че датите могат да се вадят като числа. И да припомня едно правило от първи клас. Брой=Крайната_стойност-началната_стойност+1!! :):) Може и =DATEDIF("1.1.2008","31.12.2008","d")+1 :)

Дано вече усещате мощността на Excel (а още не съм започнал с макросите):)

Enjoy:)

#007 Контролно число на ЕГН с една формула

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

Проблем: Да се изчисли контролната цифра на ЕГН в клетката A1.
Отговор: =IF(MOD(SUMPRODUCT(VALUE(MID(A1,ROW(INDIRECT("1:9")),1))*({2;4;8;5;10;9;7;3;6})),11)=10, 0, MOD(SUMPRODUCT(VALUE(MID(A1, ROW( INDIRECT("1:9")) ,1))*({2;4;8;5;10;9;7;3;6})),11))

Nice А?:):) Ето анализът на формулата:

Контролно число: Последната цифра на ЕГН-то се нарича контролна и се изчислява по следния алгоритъм:
1. Всяка от първите девет цифри се умножава по съответно тегло. Теглата са: 2,4,8,5,10,9,7,3,6
2. Намира се сумата на тези произведения
3. Намира се остатъкът от делението на 11 на намерената в точка две сума
4. Ако остатъкът е по-малък от 10 това е контролното число, ако е равен на 10 контролното число е 0!

1. Извличане на първите девет цифрите от ЕГН-то  една по една: VALUE(MID(A1,ROW(INDIRECT("1:9")),1))

 Тук се симулира цикъл във формула. Няколко думи за неговото осъществяване:

• Ако стойностите не са много може просто да изброят заградени в {} и разделени със "," (хоризонтален масив)! В нашия случай трябваше да се напише {1,2,3,4,5,6,7,8,9}, което като се замисля не изглежда прекалено дълго и зле:)
• Когато стойностите са повече се използва конструкцията Row(начало:край). Например Row(1:9) или Row(1:999).... Тази конструкция има един недостатък. При местене на формулата тя се "настройва" за новото място. Не ни спасява и "трикът" с използването на абсолютни адреси например Row($1:$9). В този случай формулата не се променя  при преместване, но става каша ако изтриваме или вмъкваме редове в района на първи и девети ред. За това използвайте този метод предпазливо.
• Най-стабилния е метода използван в примера ROW(Indirect("начало:край"). В този случай формулата не зависи от "околната среда" и нейното местоположение:) Ще използвам този начин за правене на вътрешен цикъл и  в други примери.

Функцията Value преобразува извлечение символ в цифра. В интерес на истината съм подходил доста "консервативно" към проблемът, но и без това формулата си е сложна да правя други трикове в нея:) Като резултат се получава масив от първите девет цифри.

2. Сума на произведенията на цифрите и теглата:

SUMPRODUCT(VALUE(MID(A1,ROW(INDIRECT("1:9")),1))*({2;4;8;5;10;9;7;3;6}))

Тук трябва да се обърне внимание на масивът с теглата. Той е "вертикален"! Ха сега:) Пак ви намерих занимание за четене:) Ето тук (на англйски): http://office.microsoft.com/en-us/excel-help/more-arrays-introducing-array-constants-in-excel-HA001087291.aspx Тази "подробност" ми коства около час когато правих формулата де;) Т.е. е важно, че разделителят на елементите е ";" а не ","! (тук възниква въпросът как се отбелязват вертикалните и хоризонталните масиви при друг вид езикови настройки (друг десетичен знак и друг знак за разделител на списъци) за което ще пиша в коментар под темата когато го тествам!)

Няма да се спирам защо се използва SumProduct а не Sum (четете по-старите теми)!

3. Намиране остатък от делението на 11.Нищо сложно при използването на функцията Mod.

MOD(SUMPRODUCT(VALUE(MID(A1,ROW(INDIRECT("1:9")),1))*({2;4;8;5;10;9;7;3;6})) ,11)

4. Поставяне в If конструкция =IF(остатък=10,0,остатък).

Ми това е:)