#035 Заключване на клетки

Беше ми зададена следната задача. В таблица след като се пипне някоя клетка да се заключи. Т.е. нещо като правилото в шаха "пипнато-местено":) Разбира се при необходимост да може да се сваля защитата, но за обикновения потребител попълващ таблицата да не е възможно да се връща след като въвел нещо. Ето как се реализира това:

1. Избират се клетките в които по начало е разрешено въвеждането и се "отключват". С командата Format (Лента Home)/Lock Cell (да се махне "катинарчето"). Или "класическия начин" чрез Format Cells (Ctrl+1)/ Protection и махане на Lock отметката.

"Отключване" на клетките за въвеждане

2. Активиране на защитата. Използва се командата Format/Protect Sheet. NB! Добре е да се разреши само избора на разрешените за въвеждане клетки. Не поставяйте парола! (Това ще коментираме малко по-късно!)

Включване на защитата на листа

3. Поставяне на макроса.
3.1. Активизиране на кода на листа. Заставате долу на името на листа/десен бутон/View Code.

Активиране на редактора

3.2 Присвояване на код на събитието на работния лист "Change". За целта в левия списък над редактора се избира "Worksheet" а в десния "Change".

Създаване на подпрограма за обработка на събитие

3.3 Поставяне на кода в създаденото събитието. В отворената процедура напишете следния код:

If Me.ProtectContents Then
  Me.Unprotect
  Target.Locked = True
  Me.Protect
End If

Код към събитието

Готово:)

Таблицата трябва да я запишете като XLSM или като стария съвместим със старите версии XLS формат. Припомням, че XLSX формата не поддържа макроси!

Няколко думи за кода. Събитието Change настъпва когато променяте съдържането на клетка. Кодът проверява дали е активираната защитата на листа. Това позволява при деактивирана защита да не се извършва заключване при промяна! Изключва защитата, защитава променената клетка (параметър Target се предава на събитието и съдържа адресът на променяната клетка) и отново активира защитата. Просто:)

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

If Me.ProtectContents Then
   Me.Unprotect ("123456")
   Target.Locked = True
   Me.Protect ("123456")
End If

Където 123456 е използваната парола! (NB! не разчитайте много на тази парола:) Има прекалено много инструменти за нейното премахване). При промяна на паролата се налага и промяна на кода!

#034 Таблици за търсене

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

Таблица оригинал:

до 80%	2
от 81 до 99%	4
от 100 % до 119 %	6
120% и повече	8

Вариант 1: Първо ще реализираме цялата таблица с помощта само на IF. =IF(A1<=80%,2,IF(A1<100%,4,IF(A1<120%,6,8)))    * ако данните са в А1

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

Вариант 2: Таблица за търсене

Таблица за търсене

Въвеждаме данните в произволни клетки (може и на друг лист). Избираме клетките и им задаваме име (в случая  TablicaP).  NB! Не забравяйте да натискате Enter след като именувате областта!.  Обърнете внимание как е изградена таблицата спрямо основните данни! Така изградена таблицата позволява търсенето на всякакви стойности по-големи или равни на нула. 

Формулата която търси в тази таблица е :  =VLOOKUP(A1,tablicaP,2,TRUE) . Приблизително търсене чрез VLookup връщайки стойността от втора колонка (за повече  вижте помощната информация в Excel). Както казах тази формула ще работи само с неотрицателни числа! При отрицателни стойности или текст ще се издъни. Най лесно е да се "пакетира" в IFError (виж темата за тази функция). =IFERROR(VLOOKUP(A1,tablicaP,2,TRUE),0)

Вариант 3: Понеже имах цяла тема как да не се използва Vlookup ето и варианта без нея. Но в случая става доста оплетено и за предпочитане е Vlookup. Все пак ето формулата и нейната "дисекция":

=IFERROR(INDEX(tablicaP,MATCH(A1,INDEX(tablicaP,0,1),1),2),0)

INDEX(tablicaP,0,1) - Това връща цялата първа колонка от таблицата за търсене

MATCH(A1,INDEX(tablicaP,0,1),1) - Приблизително търсене на стойността от А1 в първата колонка на таблицата за търсене. Резултатът представлява номерът на намерения ред. (NB! Единицата означава приблизително търсене в подредени във възходящ ред стойности!)

INDEX(tablicaP,MATCH(A1,INDEX(tablicaP,0,1),1),2) - Връща от таблицата за търсене стойностна от намерения ред и втората колонка.

Ако сте разбрали и трите варианта нямате проблеми с търсенето (става дума за търсене в Excel а не търсене на сериозно гадже):):)

#033 R1C1 адреси и преобразуване на имена на колони в номера и обратно...

За по-лесното боравене с адреси по подразбиране Excel борави с така наречената A1 нотация... Т.е. "име на колоната и номер на реда". Съществува и втори вид наречен R1C1... При не го се посочва номер на реда(първо е ред!) и НОМЕР на колоната. Преди числата се постановя R и C. Например R1C1 е клетката $A$1 (първа колона първи ред). R3C2 е $B$3 (ВТОРА колона и ТРЕТИ ред!). Обърнете внимание и на още една особеност. Не случайно дадох отговорите като абсолютни адреси ($A$1 и $B$3)!  Задаването на относителни адреси е по-особено. Например ако адресът е R[-1]C означава "клетката с адрес един ред нагоре спрямо текущата клетка и същата колона"! Например ако сме в клетка G5 става въпрос за клетката G4! Адресът R[+1]C[-1] ако сме B3 е еквивалентен на A4 (ред надолу и колона на ляво)... И т.н... За повече информация  прочетете тук:

http://office.microsoft.com/bg-bg/excel-help/HP005198323.aspx където има информация и как да си включите/изключите този режим на показване на работните листи (не че има някакъв смисъл де):) Използването на R1C1 адреси е доста често използван в макросите за това и "подгрявам" публиката с тази тема:)

Вчера ми се наложи да правя преобразуване на номер на колона в име и преобразуване на име на колона в номер... Покрай тези примери ще разгледам и  две "екзотични" функции:)

Пример 1. Да се преобразува име на колона в число... Името се намира клетка A1 (например XZ).

=COLUMN(INDIRECT(A1&":"&A1)) Тук няма никаква магия. Създава се валиден адрес и се взема неговата колона. Адресът се получава като се долепят името на колоната сама до себе си (получава се  "XZ:XZ"), чрез Indirect се преобразува в зона и чрез Column се преобразува в число. Със същия успех може да се "сглоби" адрес като се долепи към името на колонката някакво число за номер на ред. Например A1&1 което е и по-кратко!

=COLUMN(INDIRECT(A1&1))

Пример 2. Да се преобразува номер на колона в нейно име... Номера се намира в клетка А1 (например 156).
=MID(ADDRESS(1,A1),2,FIND("$",ADDRESS(1,A1),2)-2)
Тук "магията" е във функцията ADDRESS. Пълното описание на български може да намерите тук: http://office.microsoft.com/bg-bg/excel-help/HP010062407.aspx

ADDRESS(1,A1) За разлика от Indirect, която преобразува текст в зона, функцията Addrеss преобразува числа (ред и колона) в адрес. В нашия случай получавам адресът на клетка с ред едно (използваното число няма значение) и НОМЕРА на колоната посочен в клетка A1. Например за числото 156 функцията ще върне $EZ$1

(по подразбиране връща абсолютен адрес) след което извличаме с Mid знаците от втория (първия е $!) до следващия (обърнете внимание на Find) намерен знак $ (ако имате проблеми с Mid/Find комбинирането пишете да за да спретна някоя темичка):)

Пример 3. Да се намери адресът на средната клетка в листа. Да не се влияе от версията на Excel (2003 и 2007 имат различен брой редове и колони!)

=ADDRESS(SUMPRODUCT(MAX(ROW(A:A))) /2, SUMPRODUCT(MAX(COLUMN(1:1))) / 2) или CSE {=ADDRESS(MAX(ROW(A:A)) / 2, MAX(COLUMN(1:1)) / 2)}
"Пакетират" се функциите Max със Sumproduct за да се намери най-големия ред (колона) на дадена зона... Ако премахнем Sumproduct трябва да въведем формулата като CSE! Номерата и колоните се делят на две (търсим средата) и се дават като параметри на Address....

----
Забележка под линия:):) Има различни варианти за правенето на нещо... Често и аз бъркам и не винаги ви давам най-добрия вариант забравяйки някоя функция на Excel:):) Когато за първи път правих примерът със средата бях изключил за Address... И го направих по много екзотичен начин:)
=CELL("address",INDIRECT("R" & SUMPRODUCT(MAX(ROW(A:A))) / 2 & "C" & SUMPRODUCT(MAX(COLUMN(1:1))) / 2,FALSE))
Използвах два "трика". Първия трик е възможността Indirect да работи с R1C1 зони (обърнете  внимание на параметърът False. (просто "слепвам" числа и буквите R и C!). Зоната получена от Indirect я давам като параметър на функцията Cell за да превърне зона в адрес. Функцията Cell е много екзотична и много рядко се използва:) http://office.microsoft.com/bg-bg/excel-help/HP010062392.aspx . Както споменах посоченото решение е много тромаво и объркано и ме е леееко срам от него, но нали в крайна сметка работи:):):)
Но както казват хората ... Many (not All) Roads Lead to Rome  :):)

Бързата кучка слепи ги ражда:) Вместо да се правя на умен вместо MAX(ROW(A:A) просто може да се напише Rows(A:A) и съответно Columns(1:1)...

#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:)

#031 Таблици (имена и формули)

Както споменах при използването на таблици се появява още една "екстра", която е свързана с автоматично дефинирани имена за създадената таблица. Ще използвам създадената в #030 таблица. 

Примерна таблица

Както вече се видя в #030, че се използваха имена от вида [Цена] и [Стойност]. Тези имена се създават автоматично от заглавията на таблицата. Различното на тези имена е че те са видими САМО вътре в самата таблица. За тяхното разграничаване се използва [] за ограждане.

Малко анализ на формулата в колонка Стойност. Тя беше =[Цена]*[Количество] . Тук трябва да се има предвид, че става дума за цяла зона клетки, но при тяхното умножение се връща резултатът за съответния ред (цената от съответния ред умножена по количеството от съответния ред). Звучи объркващо, но Excel си знае работата:) За да ви объркам още повече да ви кажа че същата формула може да се запише и като =[@Цена]*[@Количество] :) Знакът @ означава текущия ред. Т.е. изрично показваме, че искаме да умножим данните от текущия ред. Както посочих Excel в зависимост от контекста избира и правилната формула. 

Все пак да видим разликите. Ако в даден ред в таблицата (NB! имената са видими само в таблицата!) напишем формулата =max([Цена]) в този случай excel ще върне максималната от ВСИЧКИ цени! Ако напишем =max([@Цена]) ще се върне максималната цена от ТЕКУЩИЯ ред (примера е безсмислен, но искам да усетите разликата между двете формули! Виждал съм доста примери в които се слага @ да се подчертае фактът, че става дума за текущия ред. Както се казва "не пречи". И като се замисли човек е по-ясно да се запомни, че [Стойност] е зона а [@Стойност] е единична клетка, а не да разчита на Excel да се сеща:)

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

#all - цялата таблица

#data - Само областта за данни (без заглавие и обобщаващия ред)

#headers - само заглавията

#totals - само обобщаващия ред

Ето примери и обяснения. Посочените формули се въвеждат ИЗВЪН таблицата!

• =MAX(Фактура[Цена])  - връща най-голямата цена
• =SUM(Фактура[Стойност]) - Сумата на колонка Стойност
• =Фактура[[#Totals],[Стойност]] - Връща стойността на клетката ПОД колонката Стойност. Прилича на предходния пример (ще върне същия резултат), но има различен смисъл и зависи от състоянието на таблицата.  В нашия случай това наистина е сумата на всички елементи, но при филтриране или смяна на функцията ще се върне друг резултат. Обърнете внимание на синтаксисът. Когато имаме няколко елемента те също се заграждат в [] !
• =Фактура[[#Headers],[Количество]] - Заглавието на колонка Количество (не много мъдър пример):)
• =INDEX(Фактура[#Headers],2) - Името на втората колона! Както се вижда може да използваме Index и другите функции както ги прилагаме върху имена! (NB! Двойката е параметър на Index!)
• =INDEX(Фактура[Цена],3) - Връща третата цена. Обърнете внимание на една приятна възможност на Index сравнявайки този и предходния пример. Когато блока е единичен ред или колона числото Excel сам преценява, че става дума за отместване надясно или отместване надолу!
• =INDEX(Фактура[Цена],MATCH("Име 3",Фактура[Наименование],0))  - цената на стока с наименование "Име 3"
• От мен да мине един Vlookup пример (скоро пак участвах в едно меле в което оплюхме здраво Vlookup;) =VLOOKUP(10,Фактура[#Data],4,False)   връща количеството (стойността на четвъртата колонка) за стока с номер 10. 
• =SUM(Фактура[[Цена]:[Стойност]]) - Сумата на всички колонки в зоната Цена-Стойност. (Цена, Количество, Мярка, Стойност)

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

Успех:)