#054 Мащабиране чрез форматиране или изгубени в превода

Една кратка тема породена от писмо на фен на блога:)

Става въпрос как стойност на клетка да се мащабира в хиляди. Т.е. да се раздели на 1000. Например при 1420 да се види 1, при 1560 да се види 2 и т.н. Не е трудно да се достигне чрез Чичо Гошо (Google)  до тази статия в която се описва специалните форматиращи възможности на Excel:

http://office.microsoft.com/bg-bg/excel-help/HP010342372.aspx

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

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

Показване на разделител за хилядите    За да използвате запетая като разделител за хиляди или за мащабиране на число като кратно на хиляда, включете следния разделител в числовия формат.

, (запетая)

Показва разделителя за хиляди в число. Excel разделя хилядите със запетаи, ако форматът съдържа запетая, заградена от знака за номер (#) или от нули. Запетая след цифров контейнер мащабира числото по 1000. Например ако форматът е #.0, и въведете 12,200,000 в клетката, показва се числото 12,200.0.

За да покажете	Като	Използвайте този код
12000	12,000	#,###
12000	12	#,
12200000	12.2	0.0,,

Целият проблем е, че това е вярно ако разделителя за хилядите е ","! Този който е правил превода  е пропуснал факта, че в стандартните настройки за България този разделител е ИНТЕРВАЛ! Именно за това и читателя се беше заблудил и не се беше справил с проблема си.

Има следните решения:
1. Да смените Thousand Separator със "," в регионалните настройки на Windows. Но това би ви довело до промяна на десетичния знак и т.н.
2. Да смените настройките на самия Excel. Това става чрез командата File/Options/Advanced:

Промяна на настройките

3. Просто на игнорирате текста от статията и използвате интервал:) !

• Избирате клетките
• Активирате форматирането на числа от лентата или чрез CTRL+1

Активиране на форматирането на числа

•    Изберете  Custom format и въвеждате "# " за формат. След знака # има интервал!

  

  Специален формат

Ми това е:)  Направете проверка, като въведете големи числови стойности във форматираните клетки. 
 Дори и създателите на Excel не си изпипват преводите в зависимост от дадения регион:) Така, че не се предоверявайте сляпо.

Успех

#053 Как да използваме старата стойност при промяна

При създаването на макроси които да следят промяната на клетки често се налага да се извършват обработки с участието на данните в клетката преди тя да бъде променена. Проблемът тук е, че събитието Worksheet_change носи само информация за новата стойност! Т.е. нямаме две събития ПРЕДИ и СЛЕД промяната, а само едно събитие. Това налага правенето на различни трикове. Един от тези трикове е в момента на избор на клетка (събитието SelectionChange) да се запомнят данните в променлива или скрит работен лист.

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

Ето едно забавно (и не много смислено;) примерче за по-лесно разбиране на решението:

Условие: Да се направи област, в която при въвеждане да не се изтрива старата стойност ами да се създава списък разделен със запетайка.Потребителя въвежда нова стойност и тя се "долепя" към текущите стойности.

Решение: Заставате върху името на листа. Десен бутон. View Code и копирате в редактора следния код:

'---------------------------------------- Начало

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    Dim oldvalue, newvalue
    ' Зоната която се наблюдава в примера (променете за вашия случай!)
    '------
    Set r = Range("A1:A100")
    '-----
    ' Изход ако се променят повече от една клетка 

    ' или клетката не е в наблюдаваната зона
    If Target.Cells.Count > 1 Or Intersect(r, Target) Is Nothing Then Exit Sub

    'Изход ако се изтрива клетка

    If IsEmpty(Target) Then Exit Sub

    ' Изключване на събитията (така се предпазваме от зацикляне!)
    Application.EnableEvents = False

    'Вземаме текущата стойност
    newvalue = Target.Value

    'Връщаме старата стойност чрез Undo
    Application.Undo
    oldvalue = Target.Value
   
    'Създаване на новата стойност 

    '(променете според вашите изисквания)
    '-----

    'Ако клетката е била празна
    If IsEmpty(oldvalue) Then
      Target.Value = newvalue
    Else ' Ако клетката не е била празна долепяме новата стойност с запетайка
      Target.Value = oldvalue & "," & newvalue
    End If
    '-----
    ' Възстановяване обработката на събития
    Application.EnableEvents = True

End Sub

' ----------------------------- Край

Забележка: Опитайте да въвеждате стойности в зоната A1:A100. Когато въведете стойност в клетка в която вече има въведени данни те се запазват! Както казах примерът е по-скоро илюстративен, но може да ви послужи:) Според вашите нужди променяте  редовете които са заградени с "-".

Успех

#052 Минути в часове или как да правим разлика между дробна част и остатък

Една мини тема породена от въпрос във блога.... "Как да се преобразуват минути в час и минути"?!? Темата е много близка до #020 Как се изваждат часове и #021 Kак да оформим сума на интервали , затова моля отново ги разгледайте!


Условие: Имаме сума на минути и искаме да ги преобразуваме в час и минути:

Варианти за преобразуване на минути в часове

Вариант 1
Всеки първокласник знае че един час се състои от 60 минути и спокойно решаваме да използваме деление и пишем формула =B7/60 (формулата в клетка B8).
Тук сме на 50% прави (може и 100%, но с известни уговорки) :)

Що се отнася до часовете сме получили вярно, но виж с дробната част имаме проблем! От една страна 90 минути са  час и 30 МИНУТИ, но от друга страна си е час и ПОЛОВИНА (0,5!).  Т.е. в нашия случай дробната част е във вид на ОТНОСИТЕЛЕН ДЯЛ  (т.е. 59-те минути възможен остатък се превръщат в проценти спрямо 60!). 15 минути са 25% (1/4), 30 секунди са 50% (1/2) и т.н. Именно поради тази причина е важно как ще бъде прочетен резултата. Абсолютно грешно е да се чете като час и 50 МИНУТИ (както беше казано правилното е да се чете като час и ПОЛОВИНА). По същия начин 1,25 трябва да се чете не Час и 25 МИНУТИ ами Час и ЧЕТВЪРТ! Та както казах резултата е верен, но с известни уговорки!

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

Вариант 2

Как да върнем секундите като секунди а не като десетична част?! Просто е... Знания от първи клас!:) На нас ни трябва ОСТАТЪКА, а не дробната част! Имам наблюдения от студентите си, че голяма част просто не правят разлика  между двете понятия! Минутите са ОСТАТЪК от делението на 60!  В Excel това става чрез функцията MOD!

=mod(B7;60) .

Трябва  да "сглобим" чрез оператора за конкатенация (слепване) на текст (&) часовете (ЦЯЛАТА част на делението на 60) , знакът ":" и ОСТАТЪКА (не дробната част!) от делението на 60! Използваме функцията TEXT за оформление на резултата.

Формулата в B9 е:

=TEXT(FLOOR(B7/60;1);"00") & ":" & TEXT(MOD(B7;60);"00")

NB! Резултатът е ТЕКСТ! Това означава, че може да го обработване с текстови функции, а не директно с функциите за дата и час!

NB2!  Вместо FLOOR(x;1) може да се използва и INT(x)!

  

Вариант 3

Не случайно ви накарах да прочетете #20 и #21 в началото (ако не сте ги прочели още, моля отделете им време!). Следващите два варианта са базиране на работата с типа време!

Правилото което трябва да запомните е: "За преобразуване от число във време ДЕЛИМ на 24 (не на 60 или на 100), а на време в число УМНОЖАВАМЕ по 24!".

Формулата в B10 е:

 =(B7/60)/24

Първото деление да се превърнат минутите в дробно число (час/минути), а второто деление е за преобразуването в тип Date/Time! Груба грешка щеше да е директно минутите да се делят само на 24!

NB! Сложим съм скобите за по-голяма прегледност. Може и без тях!

NB! За оформление използвайте потребителски формат (Custom Format). [hh]:mm! За повече информация вижте #21

Вариант 4

Тук използваме функцията Time чрез която се "сглобява" времето от час/минути/секунди!

Формулата в B11 е:

=TIME(0;B7;0)

NB! За оформление използвайте потребителски формат (Custom Format). [hh]:mm! За повече информация вижте #21

NB! Функцията генерира време само в рамките на 24 часа, независимо от използвания за оформлението формат! Ако във вашият вариант се получават сума на минутите по-голяма от 24 часа, то този вариант е неприложим!!

Умната с десетичния знак и с остатъка от делението! Различни неща са!:):)

Успех:)