#38 Работа с ЕГН

Реших малко да си поиграя и направих една таблица за работа с ЕГН-та... В нея има доста хитрости и различни техники. Няма да описвам детайли. Тези които искат да пишат на посочения е-mail. Успешно човъркане и учене:):)

Връзка към файла (google.docs)

#37 Параметър Target

Както вече обещах ето ви малко детайли за параметъра Target. При събитията свързани с даден работен лист (промяна на данни, избор на клетки, натискане на десен бутон и т.н.) към подпрограмата се изпраща параметър Target който съдържа клетките/клетките които се променят, избират и т.н. Ето някой трикове за работа с този параметър.  N.B! Примерите в тази глава се въвеждат в частта за код на съответния лист (десен бутон върху името на листа и View Code).

1. Проверка за броя на клетките. Най-лесно е просто когато параметъра съдържа повече от една клетка да се излезе от програмата. За целта в началото на подпрограмата се поставя следния код : If target.count>1 then exit sub. Това върши работа в голяма част от случаите,  но когато искаме да работим с повече от една клетка трябва да се направи цикъл от вида:

Dim c As Range
 For Each c In Target
   Код за работа със променливата C
 Next 

Пример 1: Да се направи програма за оцветяване на променяните клетки в зелено и на изтритите клетки в жълто. За целта се програмира събитието за промяна на клетка.

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim c As Range
For Each c In Target 'За всяка клетка в зоната
 If IsEmpty(c.Value) Then 'Ако клетката е празна
   c.Interior.Color = RGB(255, 255, 0) 'жълт цвят = червено + зелено
 Else
   c.Interior.Color = RGB(0, 255, 0) ' Зелен
 End If
 Next

End Sub

2.Действия само върху избрани клетки. Много често се налага дадено действие да се извършва само ако става дума за определени клетки от работния лист. Има различни начини за проверка дали зоната target (или клетките от нея) са подходящи.

Вариант 1: Цвят на клетката. Това е много "бабешки" начин, но не изисква промяната на кода (освен ако не искаме друг цвят). Просто потребителя оцветява нужните клетки в даден цвят в работната книга и само за тези клетки даденото събитие се активира. За целта се използва конструкция:

if Target.Interior.Color = xxx then

end if

Пример 2: При натискане на десен бутон върху жълтите клетки (само върху тях!) в избраната клетка да се въвежда текущата дата. В случая се програмира събитието което настъпва при натискане на десен бутон на мишката:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Target.Count > 1 Then Exit Sub ' Изход ако са повече от една клетка
If Target.Interior.Color = RGB(255, 255, 0) Then ' Ако цветът на клетката е жълт
  Target.Value = Now() ' Записва датата
  Target.NumberFormat = "dd/mm/yyyy" 'Оформя се клетката като дата
  Cancel = True ' Излиза се от подпрограмата без да се показва стандартното меню за десен бутон
End If
End Sub

Вариант 2:  Трикът със цветовете винаги ми е вършил работа, но има и по "научен" начин чрез използване на функцията Intersect. Тази функция връща пресечната точка (сечението) на няколко зони. Форматът и е Intersect(зона1, зона2....). Ако няма нито една пресечна точка функцията връща Nothing. За нас е важно да НЕ е Nothing. Ето конструкцията която се използва.

If NOT (Intersect(target,range(Зона)) is Nothing) then

end if

В зона се посочва или адреси на конкретни клетки (например Range("а1:а99")) или име на зона (например Range("nomera"). Недостатък на метода е че трябва да се пипа кода (или името на зоната) за да се промени обхватът. Но тук предимството че потребителя не е обвързан с определен цвят.

Пример 3: При избор на клетка от зоната B1:B22 да се изведе предупреждение. За целта се програмира събитието за избор на клетки.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not (Intersect(Target, Range("B1:B22")) Is Nothing) Then
   MsgBox ("Данните са важни!")
End If

#36 Статична дата и/или час

Спокойно можех да кръстя темата "Как да не използваме Now()":)) Проблемът се крие в "умните" функции Now() и Today() които се самообновяват при преизчисляване (натискане на F9 или някой от другите комбинации за преизчисляване). Това е доста досадно и съм виждал много некадърно направени таблици в които датите (например дата на фактура) е с помощта на Today() или Now()!  И в резултат на което получавате дата която се мени постоянно:):) Когато искаме датата да не се променя най-бързия начин за въвеждане е с помощта на комбинациите Ctrl+; (точка и запетая) за въвеждане на текущата дата и Ctrl+Shift+; за въвеждане на текущия час. Така въведените стойности няма да се променят!!

По сложен е въпросът ако искаме датата (или часът) да се въвеждат автоматично при промяна или попълване на дадена клетка. 

Задача:

Да се направи таблица в която автоматично да се попълва дата и час при промяна на дадена клетка:

Автоматична дата

В посочения пример се иска когато потребителя въвежда данни в колонка А автоматично да се въведе текущата дата и час в колонка В и то без да се променя при всяко преизчисляване на листа!

Вариант едно: Явно варианта с формула в клетка В2 =IF(A2="","", now()) е примамлив но неприложим по простата причина, че при всяко преизчисляване ще се променя дата и часа:( !

Да   опитаме да се направим на умни. =IF(A2="","",IF(B2="",NOW(),B2)). Тази формула има лееек дефект:):) Проблемът е, че самата тя зависи от себе си (казано на кратко получава се зацикляне (circular reference!) на формулата. Колкото и да се мъчим (например с помощта на други клетки) винаги формулата се самозацикля:(  Но от това също има изход. Малко груб, но работи. Excel позволява самозациклянето на формули в определени ситуации. Това са така наречени итерационни изчисления. Нашия случай не е точно такъв, но ще "излъжем" Excel че искаме той да не обръща внимание на самозациклените формули. Настройките се извършват от Excel/Options.

Включване на итерациите

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

Вариант две: С помощта на макрос. Натискане десен бутон на името на листа и показвате редактора на код чрез View Code. В прозорецът вкарвате следния код (Copy/Paste). В кодът съм сложил доста коментари, но обещавам следваща тема да бъде за параметъра Target и как се използва той.

'Събитие при промяна на клетки
Private Sub Worksheet_Change(ByVal Target As Range)

' Ако се прави групова промяна на клетки се излиза от подпрограмата
' Функцията не работи с промяна/изтриване на повече от една клетка!

If Target.Count > 1 Then Exit Sub

' Ако променяната клетка е в зоната A2:A999

If Not (Intersect(Target.Cells, Range("A2:A999")) Is Nothing) Then
   'Ако НЕ изтриваме клетката
   If Not IsEmpty(Target.Value) Then
   ' Ако съседната клетка все още е празна
   If Target.Offset(0, 1) = "" Then
      ' В съседната и клетка записваме текущата дата
      Target.Offset(0, 1) = Now()
      ' Оформяме съседната клетка като дата и час
      Target.Offset(0, 1).NumberFormat = "dd.mm.yyyy HH:MM"
    End If
    Else ' Ако изтриваме клетката
    ' Премахваме съдържанието и на съседната клетка
      Target.Offset(0, 1) = ""
    End If
End If
End Sub

Както казах втория вариант има недостатък, че трябва да е книга от тип XLSM , но има по-големи възможности и не се пипат настройките за калкулиране. Както споменах, ще направя няколко теми да разясня нещата в този код в детайли. Успех и внимателно със датите:)

#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 а не търсене на сериозно гадже):):)