#59 UnPivot или оправяне на "сбъркана" таблица (Част 2 - PowerQuery).... За Нинджи по обработка на данните - For Data Ninjas:)

      Това е втора част на сагата с "обръщането" на данни:) Първата част четете тук:
#58 UnPivot или оправяне на "сбъркана" таблица (Част 1 - Чрез формули).... За Нинджи по обработка на данните - For Data Ninjas:)

     Тук ще използвам "джокер" наречен Power Query. Това безплатен инструмент от Microsoft предназначен за създаване на запитвания и трансформация на данни. Инструмента позволява и да се извличат данни от самата таблица на Excel. Изтеглете си този инструмент от тук:Microsoft Power Query for Excel (MS Download Center). Как се добавя към вашата лента и някой основни начални стъпки може да намерите на следния адрес:Getting Started with Microsoft Power Query for Excel. Ако всичко е ОК трябва да ви се появи нова секция в лентата на Excel. 

Секция на Power Query

1. Стартиране на създаването на заявка към данни на Excel. 

     Създайте от "сурoвите" данни както е посочено в стъпка 2 от предходната тема. Изберете клетка от таблицата и стартирайте помощника от секцията на Power Query (From Table Excel Table).

Начало на импортирането

     Отваря се прозореца за изграждане на запитването. Power Query автоматично определя данните за импортиране. 

Данни за импортиране

2. Избират се колонките които ще се "нормализират"

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

Избор на колоните

 3. Стартиране на процеса

От раздела "Transform" се избира "Unpivot Columns".

Стартиране на процеса

В резултат на обръщането, колонките се заместват с нови колонки. Attribute и Value.

Резултатна таблица

4. Смяна на името на двете колонки

        В нашия случай "Attribute" е "Мярка", а "Value" е "Количество". Смяната на имената става чрез двойно щракане върху заглавието на колонката или Rename от контекстното меню (десен бутон на мишката).

Променени имена на колонките

5. Допълнителна обработка

 
   Power Query ви позволява множество видове обработки на данните. Например да заложите филтриране на ненулевите стойности. Този филтър се запазва в самото запитване!

6. Експортиране обратно в Excel

        От лентата Home се натиска бутона Close & Load. Автоматично се създава нов лист в таблицата.

Връщане на данните в Excel

7. Опресняване

     Когато се намирате върху резултатната таблица, се появяват два нови раздела за манипулация с данните (редактиране, опресняване и т.н.) и за тяхното оформяне. За опресняване се използва бутона Refresh от раздела Query.

Опресняване

Ми това е:)) Power Query е много мощен инструмент.... Всяка Data Ninja трябва да го познава, наред с Power Pivot.

Успех;)

#58 UnPivot или оправяне на "сбъркана" таблица (Част 1 - Формули).... За Нинджи по обработка на данните - For Data Ninjas:)

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

·         първият ред да описва данните в съответната колона;

·         заглавията (етикетите) на колоните да се разполагат САМО на най-горния (първия) ред на списъка и да са уникални, т.е. да не се дублират;

·         имената на колоните не трябва да дублират стойност от самата колона;

·         всяка колона трябва да представлява уникална категория от данни;

·         всеки ред да описва уникален обект;

·         списъкът не трябва да съдържа празни редове или колони;

·         не е желателно разполагането на редове за агрегиране (суми, средни и т.н.) на данните;

·         колоните, базирани на данните от други колони, да се заместят с подходящи формули (в някои видове анализи наличието на производни колони е ненужно).

Ето един пример:

Първични данни

      Последните шест колонки представляват една и съща категория - "Количество". Названията на колонките са различни мерки. Тук може да има дискусия дали не може просто да сменил колонките и да стане "Количество XS", "Количество S"......

     Съществува друг вариант на тази таблица:

"Обърната" таблица

      Тук проблемът е, че има повторения на номера, имена и цени на стоки, но ако "мярката" е съществен атрибут този вариант на таблицата също има своето място. Аз предпочитам този вид на таблицата, защото от нея много лесно може да се получи горния вариант чрез използването на PivotTable функционалността.... Процесът на трансформиране на първоначалния вариант във втория е малко по-сложен (не много). Ще се опитам да ви покажа два подхода за неговото решаване. Освен тях има и трети свързан с писането на макроси, но на него няма да се спирам (за сега не смятам да ви мятам в дебрите на програмирането, преди да съм се убедил, че не сте станали факири на функциите:). 

Примерната таблица може да си я изтеглите от тук:UnPivot-Blog.xlsx.

В тази тема ще разгледаме решението чрез използване на формули (моят любим вариант)

1. Дефиниране на променливи

     На страницата с първоначалните данни съм добавил две клетки съдържащи номера на началната колона която ще "въртим" (в нашия случай това е колонка "D" (номер четири))  и броя на колоните които ще "въртим" (шест). След което съм именувал клетките, които съдържат данните.

Именуване на клетки

2. Създаване на таблица от първоначалните данни

    Много хора ме гледат странно, когато има кажа да направят от таблицата си таблица!:) Не могат да разберат, че рисувайки рамки около клетките те "РИСУВАТ" таблицата. Excel дава много по-голяма гъвкавост ако данните са организирани в таблица върху работния лист. Ако сте пропуснали, запознайте се със следните две теми от блога ми: #030 Таблици (създаване) и #031 Таблици (имена и формули). В тези теми е обяснено как се създават таблици и как се борави с табличните имена във формулите. За по-лесно разбиране на формулите, от първичните данни съм създал нова таблица с име "Danni".

Добавяне на таблица

3. Помощни колонки

     За да не ви стресирам с дълги формули съм направил три помощни колонки.

3.1 Повторения

      За реализиране на формулата, ни трябва колонка която трябва да осъществи повторение на елементите колкото са колонките които въртим (в нашия случай шест пъти). Т.е. трябват ни шест единици, шест двойки и т.н. Този трик съм го дискутирал в темата #40 Месец в тримесечие или логика срещу математика. В нашият случай формулата има следния вид:

=ROUNDUP(ROW(A1)/PivotCount;0)

      Функцията Row() връща номера на реда. Т.е. ROW(А1) за първата клетка ще върне едно, за втората две и т.н. Така си осигуряваме брояч. Делим на бройката на колонките и закръгляме нагоре:) Фасулска работа:):)

3.2 Отместване

      За определяне на отместването от началната колонка ще ни трябва брояч, който да брои от нула до броя на колонките минус едно (в нашия случай от 0 до 5). И после пак. Тук се използва остатък от делението.

=MOD(ROW(A1)-1;PivotCount)

3.3 Номер на реалната колонка

     Спокойно можех да изпусна предходната колонка и да я интегрирам в тази. Но ми се искаше да ви покажа как се прави нулево базирано отместване. Номера на реалната колонка се получава по формулата:

=H2+PivotStart

H2 е помощната клетка за отместването!

Помощни колони

4. Основни колони

При основните колони се използва брояча за повторение. Формулата (таблична формула!) има следния вид:

=INDEX(Danni[Номер];UnPivot!G2)

=INDEX(Danni[Номер];UnPivot!G2)

.... 

Извличането от съответната колонка става с помощта на Index. За повече прочетете: #010 Функция Offset и функция Index !

5. Данни за колонките за въртене

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

=INDEX(Danni;UnPivot!G2;UnPivot!I2)

6. Имената на атрибута

    Тук използваме само номера на колонките. Движим се не вертикално ами хоризонтално по имената на колонките в основната таблица. Тук първия параметър (номера на реда) е постоянна величина!

=INDEX(Danni[#Headers];1;UnPivot!I2)

7. Всичко в едно

На отделен лист съм направил формулите без да използвам помощни клетки. Нищо сложно:)

   

=INDEX(Danni[Номер];ROUNDUP(ROW(A1)/PivotCount;0))

.....

=INDEX(Danni;ROUNDUP(ROW(A1)/PivotCount;0);MOD(ROW(A1)-1;PivotCount)+PivotStart)

=INDEX(Danni[#Headers];1;MOD(ROW(A1)-1;PivotCount)+PivotStart)

Ми това е:):) Успех

П.П. Четете втората част;)

#57 И пак за закръглянето (или как банкерите цепят стотинката):)

И пак за закръглянето... Аман:):):)

     Първо малко теория свързана със закръглянето. Както са ни учили в училище, ако цифрата след знака до който закръгляме е по-голяма или равна на петица я увеличаваме с единица. Т.е. 1.474 закръглено до втория знак е 1.47, а 1.476 е 1.48. В това правило има нещо "нечестно". Цифрите при които НЕ се закръгля са 1,2,3,4, а цифрите при които се закръгля са 5,6,7,8,9. Оказва се, че имаме повече случаи при които се закръгля! Търсят се различни начини за оправяне на тази грешка. Един от тези начини е така нареченото "банкерско закръгляне" (bankers' rounding).  Това е едно от названията на метода: round half to even, unbiased rounding, convergent rounding, statistician's rounding, dutch rounding, gaussian rounding, odd-even rounding, broken rounding. Ето алгоритъма на закръгляне:

1. Ако следващата цифра след позицията за закръгляне е 0,1,2,3,4, не се извършва промяна на на последния знак: 1.444 си е 1.44. (така са ни учили в училище)
2. Ако следващата цифра след позицията за закръгляне е 6,7,8,9,  последният знак се увеличава с единица: 1.446 си е 1.45 (така са ни учили в училище).
3. Ако следващата цифра след позицията за закръгляне е пет и има още цифри след нея, последният знак се увеличава с единица: 1.4451 става 1.45, и 1.4351 става 1.44 (така са ни учили в училище).
4. Ако следващата цифра след позицията за закръгляне е пет и НЯМА други цифри след нея:
1. Ако последната цифра е НЕЧЕТНА се извършва увеличение на последния знак. т.е. 1.435 става 1.44 (така са ни учили в училище)
2. Ако последната цифра е ЧЕТНА,  НЕ СЕ ИЗВЪРШВА увеличение на последния знак. т.е. 1.445 си остава 1.44 (така НЕ СА ни учили в училище:)!!!

    Т.е. да резюмирам разликата между това което сте учили в училището и "банкерското" училище е, ЧЕ АКО СЛЕД ЗНАКА ЗА ЗАКРЪГЛЯНЕ ИМАМЕ ПЕТИЦА (САМО ПЕТИЦА!) И ПОСЛЕДНИЯ ЗНАК Е ЧЕТНО ЧИСЛО НЕ СЕ ИЗВЪРШВА УВЕЛИЧАВАНЕ!

       За повече информация четете тук: Rounding (статия във Wikipedia).

      Защо ви пълня главите с глупости?!:) Искам да ви предпазя от един подводен камък. По-скоро тези от вас, които в даден момент ще започнат да пишат макроси на Visual Basic for Application (VB) . Проблемът се състои в следното: функцията Round в Excel работна книга си работи "нормално". Т.е. тя си закръгля както са ни учили. 1.445 си го закръгля на 1.45! Функцията Round във VBA работи по банкерски!!! За илюстрация съм направил проста потребителска функция (UDF):

Function vbaRound(r As Range, digits As Byte) As Double
   vbaRound = Round(r.Value, digits)
End Function

След което сравних резултата от двете функции:

Сравняване на WorkSheet Round и Round във VBA

      Както се вижда разликата е при четна цифра (4,6...) и ПЕТИЦА без нищо след нея! Обърнете внимание, че ако след петицата има нещо друго, то двете функции си действат по един и същи начин!

     Така, че внимавайте със закръглянията във VBA! Те са различни от закръглянията в работен лист!

    П.П. Ако искате да получите "нормално" закръгляне и във VBA, може да използвате следния запис:Application.WorksheetFunction.Round(......

П.П Ето къде MS са смотали информацията: PRB: Round Function different in VBA 6 and Excel Spreadsheet

#56 Отново за закръглянето (или как Excel си пази тайните):) (За нинджи!)

     Как от една тема се появи втора! :) Ако няма кой да ни усложни живота, сами си го усложняваме! :) Всичко тръгна безобидно от писането на #055 Начини за закръгляне :) В една от таблиците първата колонка съдържа 0.2,0.4..... От тук може да си изтеглите примера: демонстрация за закръгляне. (таблицата съдържа потребителска функция и за това ще ви поиска разрешаването на макросите!)

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

    Реших да го направя "научно" (пореден случай, когато практиката прегазва науката):):) Записах в клетката А2 формулата =А1+0.2 и дръпнах. И готовоооооооо.... Но не би:( Всичко изглеждаше читаво, докато не въведох формулите  =ODD(A2) и =EVEN(A2) в колонките "C" и "E".  Реших да "проверя" Excel. И ми се стори много подозрително, че "ODD" за "1" резултата е "1", а за "3" резултата е "5"!?! Също така EVEN, за "2" дава "2", а за "4" дава "6"?! Въведох директно в клетка =Even(4) и резултатът беше "4"!

   След първоначалните проверки дали не съм дръпнал неправилно, реших да опитам с допълнителна колона. В колонка "B" стойностите ги въведох с възможностите на Excel за запълване на клетки с поредица от стойности (Series). След като въведох функциите ODD и EVEN за колонка "B",  се получиха резултатите в колонките "D" и "F". Направих условно форматиране и лъсна неприятната истина. Имаше две различия!

     Какво се е случило? Нищо изненадващо. При добавянето на 0.2 се е натрупала грешка и 3.00 не е 3.00 и 4.00 не е 4.00! Понеже са малко отгоре, функциите ги закръглят към следващото четно/нечетно число! Оказва се, че ODD и EVEN са много чувствителни и реагират на тези минимални разлики.

За тези които искат да задълбочат,  препоръчвам да изчетат статиите  Numeric precision in Microsoft Excel (и връзките под нея) и Floating-point arithmetic may give inaccurate results in Excel.

     Решенията на проблема са описани в  #016 Закръгляне или кошмарът на счетоводителя и в злополучната #055 Начини за закръгляне. Например формулата в A2 може да се видоизмени като стане =round(A1+0.2;2)! Така, това което ще виждате е точно това което ще се използва при изчисленията. Другият вариант е да смените начина на изчисление както е описано в #16.

   След като намерих решение, ме зачовърка въпроса, как да покажа истинската стойност на клетката или с колко тя се различава от показаното. И така убих още няколко часа от времето, което така и така го нямам:( Добавих колона в която да получа разликата между колонка "А" и колонка "B". И получих.... "геврек"... Т.е. нула. Даже много нули след опита ми да променя формата и да увелича точността:):) Excel упорито твърдеше, че разлика между двете колонки няма! Реших по-брутално да му докажа, че стойностите в двете колонки са различни. За целта просто ги сравних. И .... Нищо! Пак си твърдеше, че двете стойности са си равни и получих навсякъде True! Лошото (и хубавото на Excel) е че не иска да мъти главите на потребителите с глупости. Т.е. е максимално User Friendly. Но точно в такива ситуации това хич не е добре и няма как да обясня на някой как при две еднакви колонки резултата е различен?!? Странно е, че ODD и EVEN са толкова нетипично за EXCEL чувствителни. Явно някой математически маниак ги е правил!:):)

Разлика и сравнение

    След дискусия по форуми, в които се опитваха да ми обяснят проблема, а аз да им обясня, че проблема ми е ясен, но не ми е ясно как да изтръгна от Excel истината:):) След борба се оказа че май единствения начин е да се мине през правене на потребителска (UDF - User Defined Function) функция на VBA. Оказва се, че VBA е по-приказлив и всичко си казва!:)

Кодът на функцията е:

Function Diff(x As Range, dig As Byte) As Double

    Diff = x.Value - Round(x, dig) 

End Function

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

  =Diff(A2;2). 

Разлика

  

      И истината лъсна. Както се вижда, разликите от стойността която виждаме и стойността която служи за изчисление са много много малки, но са достатъчни да променят резултатите.И това може да е фатално!

За това ЗАКРЪГЛЯЙТЕ!!!

П.П. И се намесиха нинджите! :):) И аз научих нещо ново! В дискусията след експерименти изскочи една тайна на Excel. Оказва се, че при определени ситуации Excel не се държи User Friendly, а изчислява и показва точно както си трябва.

     Оказва се, че формулата =(A2-B2) дава различен резултат от =A2-B2!!!! Връща точния резултат! Математически няма разлика, но в Excel има разлика! Точен резултат дава и формулата =A2-B2-0 !!! И то без скоби! Що се отнася до сравнението се оказва, че правилното сравнение е =A2-B2=0 ! И тук може без скоби! Excel-ска му работа. Сума ти математици, може би току що, го намразиха;) Та и аз понаучих нещо недокументирано и реших да го споделя с вас.

Правилна разлика и сравнение

#055 Начини за закръгляне

      Няма да открия топлата вода с тази тема, но се надавам, че информацията от нея ще ви бъде полезна. Вече дискутирах в друга тема (#016 Закръгляне или кошмарът на счетоводителя) нуждата от закръгляне чрез функция! Съветвам ви да прочетете внимателно тази тема. В нея обсъдих само една от функциите за закръгляне. За да бъда коректен сега ще покажа всички начини, които са ДЕСЕТ(!) на брой!

Можем да ги класифицираме на три групи:

1. Методи за "отрязване" на цялата част от числото.

=Trunc - "реже" цялата част или или цялата част и определен брой знаци от десетичната част. Посочват се броя знаци до които се реже.
=Int - връща цяло число по-малко от даденото. Няма втори параметър.

   Разликата между тези две функции и при работа с отрицателни числа! В помощната информация на Excel е казано, че ако искаме да получим дробната част на едно число формулата е X-INT(X), което не е вярно (всъщност изречението е "Връща дробната част от положително реално число...", но хората не вникват в детайлите и по инерция смятат, че се отнася за ВСИЧКИ числа)!! Вярната формула е X-Trunc(X), защото тя отчита и отрицателните числа! NB! Ако искате да имате дробната част в положителен вид използвайте формулата =ABS(X-TRUNC(X;0)) !

Разлика между Trunc и Int

2. Методи за закръгляне до определен брой знаци след десетичната точка. Като параметър се посочва БРОЙ ЗНАЦИ.

=Round - закръгля според математическите правила
=RoundDown - винаги закръгля към по-малкото число
=RoundUp - винаги закръгля към по-голямото число

3. Методи за закръгляне към стойност която се дели на дадения множител без остатък. В този случай се посочва като параметър МНОЖИТЕЛ (не брой знаци!!). Например при множител 2 става дума за ЧЕТНО число!

=Mround - Закръгля към по-голямото или към по-малкото число, което се дели без остатък на посочения множител.
=Ceiling - Закръгля към следващото число което се дели без остатък на посочения множител (нещо като MRoundUP).
=Floor - Закръгля към предишното число което се дели без остатък на посочения множител (нещо като MRoundDown).
=Odd - Връща следващото нечетно число. Функцията няма втори параметър.
=Even - Връща следващото четно число. Функцията няма втори параметър.

Методи за закръгляне (Щракнете върху таблицата за да я видите в оригинален размер!)

      Едно от хитрите приложения на Ceiling е да закръгляте суми. Например ако искате да на боравите със жълти стотинки можете да закръгляте с множител 0.10, а ако искате да не работите с монети от 2 или 1 стотинка може да използвате множител 0.05! (NB! Разбира се може да използвате и Floor, но кой иска да губи!:):):)

Закръгляне на суми

Успех със закръглянето!:):)