#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 , но има по-големи възможности и не се пипат настройките за калкулиране. Както споменах, ще направя няколко теми да разясня нещата в този код в детайли. Успех и внимателно със датите:)

#021 Kак да оформим сума на интервали

Това е "бонус" допълнение към #020. Става дума за сумиране на интервали:

Ето таблицата от #020 допълнена със сума:

Суми

В клетка C2 е формулата =(B2-A2+(B2<A2)) клетката е форматирана като Time.  В клетка  E2 формулата е =(B2-A2+(B2<A2))*24 и клетката е Comma style.
Както се вижда, че при сумиране на колонката в която продължителността е Time Excel не позволява сумата да надвиши 24.
Този проблем се решава просто с промяна на формата на клетка C5. Използва се Custom Format [h]:mm.

Custom number format

И всичко е ОК. Появява се сумата 33:30 (33 часа и 30 МИНУТИ). В дясната колонка сумата е 33.50 (33 часа и ПОЛОВИНА). Това повтаряне като мантра от моя страна е да запомните разликите:) Умишлено давам и двата варианта,а  изборът си остава за вас.

:)

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

#013 Петък 13 или локализация на датите:)

Когато писах предишния пример за датите се сетих за петък 13-ти,който беше скоро, и за един много познат проблем. Как да се изобрази с думи деня от седмицата. Разбира се всеки петокласник би трябвало да се сети за функцията Text :) =Text(дата, "dddd"). По-сложно става ако искаме ВИНАГИ текстът да е български (или друг конкретен език). Посочения пример има един дефект, че се влияе от текущата локализация на Excel.

Като лирично отклонение с цел да прочетете и за други функции може да предложа едно сложно решение.... =CHOOSE(WEEKDAY(A1,2),"понеделник","вторник", "сряда", "четвъртък", "петък", "събота","неделя")

Всъщност този пример го давам на студентите когато обяснявам Choose и Weekday... Но да си призная си е и за запълване на повече време в часа ми докато студентите търсят проклинайки ме  (мен и Кирил и Методий) буквите по клавиатурата:)) 

Но да се върнем към нашия проблем с локализацията. Всъщност пред форматиращия стринг може да се постави указател за локализация. Например =TEXT(NOW(),"[$- локален_код]dddd"). Трикът е в числото в скобите. Списък на стойностите може да вземете от адрес http://msdn.microsoft.com/en-us/goglobal/bb964664.aspx. Стойността вземате от втората колонка в посочената таблица.

Ми това е... Да проверим: =TEXT("13/8/2010","[$-402]dddd").  Може и на македонски ;) : =TEXT("13/8/2010","[$- 042f]dddd")

Enjoy :)