За да се развива блога, ще съм ви благодарен да ми поставяте реални проблеми. По такъв начин ми идват и идеите. Тази тема е породена от конкретен проблем, за което благодаря на задалия въпроса. Става въпрос за так нареченото "нормализиране" на таблица. "Нормалната" таблица трябва да отговаря на следните условия:
·
първият ред да описва данните в съответната
колона;
·
заглавията (етикетите) на колоните
да се разполагат САМО на най-горния (първия) ред на списъка и да са уникални,
т.е. да не се дублират;
·
имената на колоните не трябва да
дублират стойност от самата колона;
·
всяка колона трябва да представлява
уникална категория от данни;
·
всеки ред да описва уникален
обект;
·
списъкът не трябва да съдържа
празни редове или колони;
·
не е желателно разполагането на
редове за агрегиране (суми, средни и т.н.) на данните;
·
колоните, базирани на данните от
други колони, да се заместят с подходящи формули (в някои видове анализи
наличието на производни колони е ненужно).
Ето един пример:
 |
| Първични данни |
Последните шест колонки представляват една и съща категория - "Количество". Названията на колонките са различни мерки. Тук може да има дискусия дали не може просто да сменил колонките и да стане "Количество 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)
Ми това е:):) Успех
П.П. Четете втората част;)
Няма коментари:
Публикуване на коментар