#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