Функция за транспониране в Excel

От време на време потребител на Excel може да има задачата да превърне набор от данни, които имат хоризонтална структура във вертикална. Този процес се нарича транспониране. Тази дума е нова за повечето хора, тъй като при нормална работа с компютър не е необходимо да използвате тази операция. Въпреки това, тези, които трябва да работят с големи количества данни, трябва да знаят как да го направят. Днес ще говорим по-подробно за това как да го изпълним, с каква функция, а също така ще разгледаме подробно някои други методи.

Функция TRANSPOSE – транспониране на диапазони от клетки в Excel

Един от най-интересните и функционални методи за транспониране на таблици в Excel е функцията ТРАНСПОРТ. С негова помощ можете да превърнете хоризонтален диапазон от данни във вертикален или да извършите обратната операция. Нека да видим как да работим с него.

Синтаксис на функцията

Синтаксисът за тази функция е невероятно прост: ТРАНСПОНИРАНЕ (масив). Това означава, че трябва да използваме само един аргумент, който е набор от данни, който трябва да бъде преобразуван в хоризонтален или вертикален изглед, в зависимост от това какъв е бил първоначално.

Транспониране на вертикални диапазони от клетки (колони)

Да предположим, че имаме колона с диапазон B2:B6. Те могат да съдържат както готови стойности, така и формули, които връщат резултати в тези клетки. За нас не е толкова важно, транспонирането е възможно и в двата случая. След прилагане на тази функция дължината на реда ще бъде същата като оригиналната дължина на колоната на диапазона.

Последователността от стъпки за използване на тази формула е следната:

1. Изберете линия. В нашия случай той има дължина от пет клетки.
2. След това преместете курсора в лентата с формули и въведете формулата там =TRANSP(B2:B6).
3. Натиснете клавишната комбинация Ctrl + Shift + Enter.

Естествено, във вашия случай трябва да посочите диапазона, който е типичен за вашата таблица.

Транспониране на хоризонтални диапазони от клетки (редове)

По принцип механизмът на действие е почти същият като в предишния параграф. Да предположим, че имаме низ с начални и крайни координати B10:F10. Може също да съдържа както директно стойности, така и формули. Нека направим колона от него, която ще има същите размери като оригиналния ред. Последователността на действията е следната:

1. Изберете тази колона с мишката. Можете също да използвате клавишите на клавиатурата Ctrl и стрелката надолу, след като щракнете върху най-горната клетка на тази колона.
2. След това записваме формулата =TRANSP(B10:F10) в лентата с формули.
3. Записваме го като формула за масив, като използваме клавишната комбинация Ctrl + Shift + Enter.

Транспониране с Paste Special

Друга възможна опция за транспониране е използването на функцията Paste Special. Това вече не е оператор, който да се използва във формули, но е и един от популярните методи за превръщане на колони в редове и обратно.

Тази опция е в раздела Начало. За да получите достъп до него, трябва да намерите групата „Клипборд“ и там да намерите бутона „Поставяне“. След това отворете менюто, което се намира под тази опция, и изберете елемента „Транспониране“. Преди това трябва да изберете диапазона, който искате да изберете. В резултат на това ще получим същия диапазон, само обратното е огледално.

3 начина за транспониране на таблица в Excel

Но всъщност има много повече начини да превърнете колони в редове и обратно. Нека опишем 3 метода, чрез които можем да транспонираме таблица в Excel. Обсъдихме два от тях по-горе, но ще дадем още няколко примера, за да получите по-добра представа за uXNUMXbuXNUMXbкак да извършите тази процедура.

Метод 1: Специално поставяне

Този метод е най-простият. Достатъчно е да натиснете няколко бутона и потребителят получава транспонирана версия на таблицата. Нека дадем малък пример за по-голяма яснота. Да предположим, че имаме таблица, съдържаща информация за това колко продукти в момента са на склад, както и колко струват общо. Самата маса изглежда така.

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

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

1. Изберете диапазона от данни, които трябва да ротираме. След това копираме тези данни.
2. Поставете курсора където и да е върху листа. След това щракнете с десния бутон на мишката и отворете контекстното меню.
3. След това щракнете върху бутона „Специално поставяне“.

След като изпълните тези стъпки, трябва да кликнете върху бутона „Транспониране“. По-скоро поставете отметка в квадратчето до този елемент. Не променяме други настройки и след това щракнете върху бутона „OK“.

След като изпълним тези стъпки, оставаме със същата таблица, само нейните редове и колони са подредени по различен начин. Също така имайте предвид, че клетките, съдържащи същата информация, са маркирани в зелено. Въпрос: какво се случи с формулите, които бяха в първоначалния диапазон? Местоположението им се е променило, но самите те са останали. Адресите на клетките просто се променят на тези, които са били формирани след транспонирането.

Трябва да се извършат почти същите действия, за да се транспонират стойности, а не формули. В този случай също трябва да използвате менюто Специално поставяне, но преди това изберете диапазона от данни, съдържащи стойностите. Виждаме, че прозорецът Paste Special може да бъде извикан по два начина: чрез специално меню на лентата или контекстното меню.

Метод 2. Функция TRANSP в Excel

Всъщност този метод вече не се използва толкова активно, колкото в самото начало на появата на тази програма за електронни таблици. Това е така, защото този метод е много по-сложен от използването на Paste Special. Въпреки това, той намира своето приложение при автоматизиране на транспонирането на таблици.

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

1. Първо, трябва да изберем диапазона от данни, който ще се използва за транспониране на таблицата. Просто трябва да изберете зоната напротив. Например в този пример имаме 4 колони и 6 реда. Следователно е необходимо да се избере област с противоположни характеристики: 6 колони и 4 реда. На снимката се вижда много добре.
2. След това веднага започваме да попълваме тази клетка. Важно е да не премахнете случайно селекцията. Следователно трябва да посочите формулата директно в лентата за формули.
3. След това натиснете клавишната комбинация Ctrl + Shift + Enter. Не забравяйте, че това е формула за масив, тъй като работим с голям набор от данни наведнъж, които ще бъдат прехвърлени в друг голям набор от клетки.

След като въведем данните, натискаме клавиша Enter, след което получаваме следния резултат.

Виждаме, че формулата не е прехвърлена в новата таблица. Форматирането също беше загубено. Поето

Всичко това ще трябва да се направи ръчно. Освен това не забравяйте, че тази таблица е свързана с оригиналната. Следователно, веднага щом се промени някаква информация в оригиналния диапазон, тези корекции се правят автоматично в транспонираната таблица.

Следователно този метод е много подходящ в случаите, когато трябва да се уверите, че транспонираната таблица е свързана с оригиналната. Ако използвате специална вложка, тази възможност вече няма да съществува.

обобщена таблица

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

1. Нека направим обобщена таблица. За да направите това, трябва да изберете таблицата, която трябва да транспонираме. След това отидете на елемента „Вмъкване“ и потърсете там „Обърна таблица“. Ще се появи диалогов прозорец като този на тази екранна снимка.
2. Тук можете да преназначите диапазона, от който ще се прави, както и да направите редица други настройки. Сега се интересуваме преди всичко от мястото на обобщената таблица - на нов лист.
3. След това автоматично ще се създаде оформлението на обобщената таблица. В него е необходимо да маркирате тези елементи, които ще използваме, след което те трябва да бъдат преместени на правилното място. В нашия случай трябва да преместим елемента „Продукт“ в „Имена на колони“ и „Цена за брой“ в „Стойности“.
4. След това обобщената таблица ще бъде окончателно създадена. Допълнителен бонус е автоматичното изчисляване на крайната стойност.
5. Можете да промените и други настройки. Например махнете отметката от елемента „Цена за брой“ и отметнете елемента „Обща цена“. В резултат на това ще имаме таблица, съдържаща информация за това колко струват продуктите. Този метод на транспониране е много по-функционален от останалите. Нека опишем някои от предимствата на обобщените таблици:

1. Автоматизация. С помощта на обобщени таблици можете автоматично да обобщавате данни, както и да променяте произволно позицията на колони и колони. За да направите това, не е необходимо да извършвате никакви допълнителни стъпки.
2. Интерактивност. Потребителят може да променя структурата на информацията толкова пъти, колкото е необходимо за изпълнение на задачите му. Например, можете да промените реда на колоните, както и да групирате данни по произволен начин. Това може да се направи толкова пъти, колкото е необходимо на потребителя. И отнема буквално по-малко от минута.
3. Лесно форматиране на данни. Много е лесно да подредите обобщена таблица по начина, по който човек иска. За да направите това, просто направете няколко кликвания на мишката.
4. Получаване на стойности. Огромният брой формули, които се използват за създаване на отчети, са разположени в директен достъп на човек и са лесни за интегриране в обобщена таблица. Това са данни като сумиране, получаване на средна аритметична стойност, определяне на броя на клетките, умножение, намиране на най-големите и най-малките стойности в определената извадка.
5. Възможност за създаване на обобщени диаграми. Ако обобщените таблици се преизчислят, свързаните с тях диаграми се актуализират автоматично. Възможно е да създадете толкова графики, колкото са ви необходими. Всички те могат да се променят за конкретна задача и няма да бъдат свързани помежду си.
6. Възможност за филтриране на данни.
7. Възможно е да се изгради обобщена таблица въз основа на повече от един набор от изходна информация. Следователно тяхната функционалност ще стане още по-голяма.

Вярно е, че когато използвате обобщени таблици, трябва да се вземат предвид следните ограничения:

1. Не цялата информация може да се използва за генериране на обобщени таблици. Преди да могат да се използват за тази цел, клетките трябва да бъдат нормализирани. С прости думи, разберете го правилно. Задължителни изисквания: наличие на заглавен ред, пълнота на всички редове, равенство на форматите на данните.
2. Данните се актуализират полуавтоматично. За да получите нова информация в обобщената таблица, трябва да кликнете върху специален бутон.
3. Пивотните таблици заемат много място. Това може да доведе до известно прекъсване на работата на компютъра. Освен това файлът ще бъде труден за изпращане по имейл поради това.

Също така, след създаване на обобщена таблица, потребителят няма възможност да добавя нова информация.