#39 Функците Small и Large без дублирани стойности

Описанието на функцията Small е дадено в помощната информация на Excel (може да използвате този адрес http://office.microsoft.com/bg-bg/excel-help/HP010062539.aspx).
За съжаление в информацията не е посочен един малък подводен камък, който  може да ви доведе до главоболие в определени случаи. Става дума за това, че функцията брои стойностите не отчитайки дублиращите стойности за всяка позиция. Ето пример:

Данни за Small

Ако въведем формулата: =SMALL(A1:A10;1) ще видим стойността на най-малката стойност (в случая 1).  Ако въведем формулата =SMALL(A1:A10;2), с тайната надежда да видим ВТОРАТА по големина стойност, ни очаква разочарование. Функцията ще върне пак 1!! Това произтича от факта, че в списъка има две единици. Чак когато зададем =SMALL(A1:A10;3) ще получим това което искаме :(

Този "проблем" се решава със следната помощна табличка:

Намиране на N-тата по големина стойност

Стъпка 1: В колонка C се намират числа от 1 до N.Може с AutoFill или формулата =Row(A1) или =Row()-1 и т.н. ( всеки си има стил и различен подход, и както и да ги въведете (дори и на ръка) никой няма да ви разстреля):):):)

Стъпка 2: В клетката D2 въведете  =SMALL($A$1:$A$10;1). Намира първата най-малка стойност. Тук няма интрига;)  

Стъпка 3: В клетка D3 въведете =SMALL($A$1:$A$10;COUNTIF($A$1:$A$10;"<="&D2)+1)

Размножете формулата като я "дръпнете" надолу. И готовото:) Ако искате да не дава грешка #Num просто я "пакетирайте" с IFError за което си има отделна тема;) 

Няколко думи за "магията". За да се намери N-тото по големина число, трябва да е намери колко е броя на N-1 по големина числа преди него! Примерно за да се намери второто по големина число трябва да се намери колко са преди него (в случая колко единици имаме)! След което към намерената бройка да се добави единица и да се намери чрез Small съответната стойност (в нашия случай 3-тото по големина число). За намирането на третото по големина числа броим колко са преди него като бройка (в нашия случай колко са числата по-малки от 2) В нашия случай намираме броя  на единиците и двойките!. Понеже имаме само една двойка сумата е 3! Т.е. със Small трябва да намерим четвъртата(!) по големина стойност.... И т.н. (опитайте се да проиграете и за останалите числа).

За по-лесното използване на тази таблица, я именуваме (примерно SmallBD).След което може да използваме "прост" Vlookup:):)

Именуване на таблицата

Пример: Да се намери петата по големина стойност: =VLOOKUP(5;SmallBD;2;FALSE). Сменяте само червената цифричка и сте готови:):)

Забележка: По някога може създаването на междинна таблица да не е възможно (въпреки че в случая го препоръчвам горещо) и се налага да се сглоби цялата формула в една клетка. В този случай формулата става доста дълга (и доста объркваща за начинаещи)! Аз ще дам само как се намира втората по големина стойност:
=SMALL($A$1:$A$10;COUNTIF($A$1:$A$10;"<=" & SMALL($A$1:$A$10;1))+1).
За по-големи стойности се въоръжете с търпение и здрави нерви и "сглобявайте":):) Но както споменах това го препоръчвам за майсторите които са разбрали предишните обяснения!

Успех:)

П.П. Всичко казано за функцията Small важи и за функцията Large!

#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! не разчитайте много на тази парола:) Има прекалено много инструменти за нейното премахване). При промяна на паролата се налага и промяна на кода!