Како да конвертирате редови во колони во Excel

Овој водич објаснува што да направите за да ги конвертирате редовите во колони во Excel. Кога користите Excel, честопати треба да менувате редови и колони во табела. На пример, се случува човек да направи огромна маса, а потоа да сфати дека е многу полесно да ја прочита ако ја превртиш. 

Эта детальная инструкция расскажет о нескольких способах транспонирования Excel-таблицы, а также о часто встречаемых ошибках, со которыми може да столкнуться пользователь. Сите тие можат да се користат на любой верзии на Excel, како очень старой, так и само новой.

Користење на функцијата Paste Special

Да речеме дека имате две групи на податоци. Вториот е она со што сакате да завршите. Првата е табелата што треба да се транспонира. Во првата верзија, имињата на земјите се претставени во различни колони, и многу е незгодно да се читаат, а уште повеќе да се споредуваат карактеристиките на државите меѓу себе. Затоа, многу е подобро да се организира табелата така што имињата на земјите да се појавуваат во различни колони.

За да ги замените редовите и колоните, сторете го следново:

1. Изберете ја оригиналната табела. Ако сакате да ја видите целата табела одеднаш (ако е многу голема), треба да ја притиснете комбинацијата на копчиња Ctrl + Home, а потоа - Ctrl + Shift + End.
2. Потоа ќелиите се копираат. Ова може да се направи или преку контекстното мени или со притискање на комбинацијата на копчиња Ctrl+C. Препорачливо е веднаш да се навикнете на последната опција, бидејќи ако научите топли копчиња, можете да извршите многу задачи буквално во една секунда.
3. Изберете ја првата ќелија во целниот опсег. При этом он должен находиться за пределами таблицы. Также необходимо подоброть ячейку таким образом, чтобы таблица после транспонирования не се пересекалась со другими данными. На пример, ако почетната табела има 4 колони и 10 редови, тогаш по извршувањето на сите овие операции, таа ќе шири 10 ќелии надолу и 4 ќелии на страна. Затоа, во овој опсег (сметајќи од целната ќелија) не треба да има ништо.
4. На целната ќелија, мора да кликнете со десното копче и да изберете „Вметни специјално“, а потоа штиклирајте го полето до натписот „Транспонира“.

Важно: ако има формули во изворната табела, важно е да се осигурате дека се користат апсолутни референци во секоја ќелија. Ова мора да се направи така што сите врски автоматски се ажурираат.

Огромное преимущество опции «Специјальная вставка» и заключается в можности транспонировать таблицу всего лишь за несколько секунд. И при этом полностью сохраняется форматирование, што также позволяет сэкономить кучу времени.

Несмотря на эти явные плюсы, есть и ряд серьезных недостатков, которые го мешаат овој метод называться универзальным:

1. Лошо е да се користи за транспонирање на полноправни табели кои не се сведени на банален опсег на вредности. Во овој случај, функцијата „Транспонирање“ ќе биде оневозможена. За да го решите овој проблем, мора да ја претворите табелата во опсег.
2. Овој метод е добро прилагоден за еднократно транспонирање бидејќи не ја поврзува новата табела со оригиналните податоци. Со едноставни зборови, кога менувате една од табелите, информациите нема автоматски да се ажурираат на втората. Затоа, транспонирањето ќе мора да се повтори. 

Како да ги замените редовите и колоните и да ги поврзете со главната табела?

Значи, што може да се направи за да може опцијата „Вметни специјално“ да ја поврзе табелата со главните податоци и оние што произлегуваат од транспонирањето? На крајот на краиштата, секој би сакал податоците автоматски да се ажурираат.

1. Скопировать таблицу, для которой требуется транспонирование.
2. Изберете ќелија без податоци во празна област на табелата.
3. Стартувајте го менито Paste Special, слично на претходниот пример. После тоа, треба да кликнете на копчето „Вметни врска“, кое може да се најде долу лево.
4. Резултатот ќе биде следниот.
5. Выбрать новую таблицу и запустить окно «Најти и заменить» путем нажатия комбинации клавиш Ctrl + H.
6. Заменить все знаки ввода формулы (=) на ххх (или любую другую комбинацию знаков, которой нет во оригинальной таблице).
7. Како резултат на тоа, ќе испадне нешто страшно, но ова е задолжителен среден услов за постигнување на посакуваниот резултат. Тогаш сè ќе изгледа убаво.
8. Копирајте ја добиената табела и потоа користете Paste Special за да ја транспонирате.

После тоа, само треба повторно да го отворите дијалогот „Најди и замени“ и да го користиш за да го промениш „xxx“ во ќелиите во „=“, така што сите ќелии се поврзани со оригиналните информации.Се разбира, ова е покомплицирано и подолго, но овој метод ви овозможува да го заобиколите недостатокот на врска до оригиналната табела. Но, овој пристап има и недостаток. Се изразува во потребата самостојно да се продолжи со форматирањето.

Примена на формули

Постојат две функции кои ви дозволуваат флексибилно да менувате редови и колони: ТРАНСП и ДВССУЛ. Здесь также есть возможность сохранить связь со первоначальной таблицей, но механика работы несколько иная.

функција ТРАНСП

Всушност, оваа формула директно ја транспонира табелата. Синтаксата е следна:

=TRANSP(низа)

Сега ќе се обидеме да го примениме на табела која содржи информации за населението на одделни држави.

1. Пребројте го бројот на колони и редови во табелата и пронајдете празно место во листот што има исти димензии.
2. Започнете го режимот за уредување со притискање на копчето F2.
3. Напишете функција ТРАНСП со опсегот на податоци во загради. Важно е секогаш да користите апсолутни референци кога ја користите оваа функција.
4. Притиснете ја комбинацијата на копчиња Ctrl+Shift+Enter. Важно е точно да ја притиснете комбинацијата на копчиња, инаку формулата ќе одбие да работи.

Тоа е тоа, сега резултатот изгледа вака!

Предноста на оваа формула е иста како и претходниот метод (со користење на функцијата „најди и замени“) е тоа што кога се менуваат основните информации, транспонираната табела автоматски ќе се ажурира.

Но недостати у не е тоа имеются:

1. Форматирањето, како и во случајот со методот Find and Replace, нема да се зачува.
2. Оригиналната табела мора да содржи некои податоци, инаку ќе има нули во некои ќелии.
3. Излишняя зависимость од источника данных. То есть, этот метод имеет противоположный по недостаток – невозможность изменять транспонированную таблицу. Если попытаться это сделать, программа скажет, што не може да се редактировать часть массива.

Исто така, оваа функција не може секогаш да се применува флексибилно во различни ситуации, па затоа треба да знаете за неа, но да користите поефикасни техники.

Користење на формулата INDIRECT

Механиката на овој метод е многу слична со користењето на формулата ТРАНСП, но при этом его использование решает проблем не може да се редактировать транспонированную таблицу без потери связи со основните информации.

Но одной формулы ИНДИРЕКТЕН не е доволно: сепак треба да ја користите функцијата АДРЕСА. Во овој пример нема да има голема табела, за да не ве преоптовариме со многу непотребни информации.

Значи, да речеме дека имате таква табела, која се состои од 4 колони и 5 редови.

Треба да се преземат следниве активности:

1. Внесете ја оваа формула: =ИНДИРЕКТНА(АДРЕСА(КОЛУНА(A1),РЕД(A1))) во горната лева ќелија на новата табела (во нашиот пример тоа е A7) и притиснете го копчето enter. Ако информациите не започнуваат во првиот ред или во првата колона, тогаш ќе мора да користите посложена формула: =ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1)-СТОЛБЕЦ($A$1)+СТРОКА($A$1);СТРОКА(A1)-СТРОКА($A$1)+СТОЛБЕЦ($A$1))). Во оваа формула, А1 значи горната ќелија на табелата, врз основа на која ќе се формира транспонираната ќелија.
2. Проширете ја формулата на целата област каде што ќе биде поставена новата верзија на табелата. За да го направите ова, повлечете го маркерот во долниот десен агол на првата ќелија до спротивниот крај на идната табела.
3. Сите! Табелата се покажа како успешно транспонирана и сè уште можете да ја уредувате. Се разбира, нејзиниот изглед остава многу да се посакува, но неговото поправање не е тешко. За да го вратите правилното форматирање, треба да ја копирате табелата што ја транспониравме (т.е. оригиналната), а потоа изберете ја новосоздадената табела. Следно, кликнете со десното копче на избраниот опсег, а потоа кликнете на „Форматирање“ во опциите за залепување.

Значи користејќи ја функцијата ИНДИРЕКТЕН ви овозможува да уредувате апсолутно која било вредност во конечната табела, а податоците секогаш ќе се ажурираат штом ќе се направат какви било промени во изворната табела.

Се разбира, овој метод е потежок, но ако го решите во пракса, тогаш сè не е толку страшно.

Этот метод один из самых лучших, потому што несмотря на тоа, што не сочува форматирование во новосозданной таблице, може его восстановить.

Како функционира комбинацијата на формулите INDIRECT и ADDRESS?

После тоа, како да се разобрались во том, како да се использовать совокупность этих формаул для транспонирования таблицы, вам, може, захочется более глубоко го изучи принципот на работа на овој метод.

функција ИНДИРЕКТЕН во нашата формула се користи за создавање индиректна референца на ќелијата. На пример, ако треба да ја наведете истата вредност во ќелијата A8 како и во B1, тогаш можете да ја напишете формулата

=INDIRECT(„B1“)

Казалось бы, зачем ето делать? Ведь можно е да напишете ссылку на ячейку во другой ячейке. Но преимущество этой функции во том, што во ссылку може да се преврати апсолютно любую строку, и даже ту, которая создается со использованием других формаул. Собственно, это мы и делаем во форма.

Надвор од функцијата АДРЕСА се користи и во формулата кОЛОНА и ЛИНИЈА. Првиот ја враќа адресата на ќелијата врз основа на веќе познатите броеви на редови и колони. Важно е да се следи правилната низа овде. Прво се одредува редот, а потоа само колоната. На пример, функцијата АДРЕСА(4;2) ќе ја врати адресата $B$2.

Следующая используемая выше функција – ето кОЛОНА. Тука е неопходно, така што формулата го добива бројот на колоната од одредена референца. На пример, ако го користите параметарот B2 во заградите на оваа функција, тогаш тој ќе го врати бројот 2, бидејќи втората колона е колона Б.

Очигледно, функцијата ROW работи на ист начин, само го враќа бројот на редот. 

И сега нема да користиме апстрактни примери, туку многу специфична формула што беше користена погоре:

=ИНДИРЕКТНА(АДРЕСА(КОЛУНА(A1),РЕД(A1)))

Овде можете да го видите тоа прво во функцијата АДРЕСА колоната е наведена, а само тогаш линијата. И токму тука се крие тајната на работниот капацитет на оваа формула. Се сеќаваме дека оваа функција работи на огледален начин, а првиот аргумент во неа е бројот на линијата. И излегува дека кога ќе ја запишеме адресата на колоната таму, таа се претвора во број на линија и обратно.

То есть, если подытожить:

1. Ги добиваме броевите на колоните и редовите користејќи ги соодветните функции.
2. Користење на функцијата АДРЕСА редовите стануваат колони и обратно.
3. функција ИНДИРЕКТЕН помага да се прикажат огледуваните податоци во ќелијата.

Тоа е колку едноставно сето тоа излегува!

Користење на макро за транспонирање

Макро е мала програма. Може да се користи за автоматизирање на процесот. Важно е да се земе предвид дека макрото има некои ограничувања. Максималниот метод на транспонирање ви овозможува да работите со 65536 елементи. Ако оваа граница се надмине, тоа ќе резултира со губење на податоци.

Во сем остальном, тоа е ефективни метод автоматизација, кој може да значительно лесно да се живее.

На пример, можете да напишете такой код, который будет менять местами строки и колонки.

Sub TransposeColumnsRows()

    Затемнет изворен опсег како опсег

    Затемнување на опсегот како опсег

    Поставете SourceRange = Application.InputBox(Prompt:=»Ве молиме изберете го опсегот за транспонирање», Наслов:=»Пренеси ги редовите во колони», Тип:=8)

    Поставете DestRange = Application.InputBox(Prompt:=»Изберете ја горната лева ќелија од опсегот на одредиштето», Наслов:=»Пренеси ги редовите во колони», Тип:=8)

    SourceRange.Копирај

    DestRange.Изберете

    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

    Application.CutCopyMode = Неточно

Под крајот

Но если знаний в программировании особо нет, ничего страшного. Можно воспользоваться описанными повеќе способами. А потом учиться новому по мере освоения старого.