Како претворити редове у колоне у Екцелу

Овај водич објашњава шта треба да урадите да бисте претворили редове у колоне у програму Екцел. Када користите Екцел, често морате да мењате редове и колоне у табели. На пример, дешава се да је човек направио огроман сто, а онда је схватио да је много лакше прочитати ако га окренете. 

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

Коришћење посебног лепљења

Рецимо да имате два скупа података. Друго је оно са чиме желите да завршите. Прва је табела коју треба транспоновати. У првој верзији, имена земаља су представљена у различитим колонама, и веома је незгодно читати, а још више, упоређивати карактеристике држава једна са другом. Због тога је много боље организовати табелу тако да се називи земаља појављују у различитим колонама.

Да бисте заменили редове и колоне, урадите следеће:

1. Изаберите оригиналну табелу. Ако желите да видите целу табелу одједном (ако је веома велика), потребно је да притиснете комбинацију тастера Цтрл + Хоме, а након тога – Цтрл + Схифт + Енд.
2. Ћелије се затим копирају. Ово се може урадити кроз контекстни мени или притиском на комбинацију тастера Цтрл+Ц. Препоручује се да се одмах навикнете на последњу опцију, јер ако научите вруће тастере, многе задатке можете обавити буквално у једној секунди.
3. Изаберите прву ћелију у циљном опсегу. При етом он должен находитьса за пределами таблици. Также необходимо подобрать ачејку таким образом, чтоби таблицу после транспонированиа не пересекалась с другим цимма. На пример, ако почетна табела има 4 колоне и 10 редова, онда ће након извршења свих ових операција проширити 10 ћелија надоле и 4 ћелије у страну. Према томе, унутар овог опсега (рачунајући од циљне ћелије) не би требало бити ништа.
4. На циљној ћелији морате да кликнете десним тастером миша и изаберете „Специјално лепљење“, а затим означите поље поред натписа „Транспонирај“.

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

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

Неизвестна на ети авние плуси, есть и рад серьезних недостатков, которие мешаут етому методу називатьса универсальним:

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

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

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

1. Скопировать таблицу, дла которој требуетса транспонирование.
2. Изаберите ћелију без података у празном делу табеле.
3. Покрените Пасте Специал мени, слично претходном примеру. Након тога, потребно је да кликнете на дугме „Убаци везу“, које се налази у доњем левом углу.
4. Резултат ће бити следећи.
5. Изаберите нову табелу и запустите окно «Најди и замени» помоћу комбинације тастера Цтрл + Х.
6. Замените все знаке ввода формули (=) на ххх (или лубуу другу комбинацију знакова, которој нет в оригиналној таблици).
7. Као резултат тога, испоставиће се нешто страшно, али ово је обавезан средњи услов за постизање жељеног резултата. Тада ће све изгледати лепо.
8. Копирајте добијену табелу, а затим користите Пасте Специал да је транспонујете.

Након тога, само треба да поново отворите дијалог „Пронађи и замени“ и користите га да промените „ккк“ у ћелијама у „=“ тако да све ћелије буду повезане са оригиналним информацијама.Наравно, ово је компликованије и дуже, али овај метод вам омогућава да заобиђете недостатак везе до оригиналне табеле. Али овај приступ такође има недостатак. Изражава се у потреби да се самостално настави са форматирањем.

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

Постоје две функције које вам омогућавају да флексибилно мењате редове и колоне: ТРАНСП и ДВССИЛ. Такође можете сачувати везу с первоначелној таблици, но механика работи неколико инаа.

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

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

=ТРАНСП(низ)

Сада ћемо покушати да то применимо на табелу која садржи податке о становништву појединих држава.

1. Избројите број колона и редова у табели и пронађите празан простор на листу који има исте димензије.
2. Покрените режим уређивања притиском на тастер Ф2.
3. Напишите функцију ТРАНСП са опсегом података у заградама. Важно је увек користити апсолутне референце када користите ову функцију.
4. Притисните комбинацију тастера Цтрл+Схифт+Ентер. Важно је да притиснете тачно комбинацију тастера, иначе ће формула одбити да ради.

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

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

Но недостатки у нее тоже имеутса:

1. Форматирање, као у случају методе Финд анд Реплаце, неће бити сачувано.
2. Оригинална табела мора да садржи неке податке, иначе ће у неким ћелијама бити нуле.
3. Излишнаа зависимость от источника данних. То есть, етот метод имеет противположенниј по недостатку – немогућность изменать транспонированнуу таблицу. Если попитатьса ето сделать, программа скажет, что невозможно редактировать часть массива.

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

Коришћење ИНДИРЕКТНЕ формуле

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

Но одној формули ИНДИРЕКТАН није довољно: још увек морате да користите функцију АДРЕСА. У овом примеру неће бити велике табеле, како вас не би преоптеретили са гомилом непотребних информација.

Дакле, рецимо да имате такву табелу, која се састоји од 4 колоне и 5 редова.

Потребно је предузети следеће радње:

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

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

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

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

Како функционише комбинација формула ИНДИРЕКТНЕ и АДРЕСЕ?

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

функција ИНДИРЕКТАН у нашој формули се користи за креирање индиректне референце ћелије. На пример, ако треба да наведете исту вредност у ћелији А8 као у ћелији Б1, онда можете написати формулу

=ИНДИРЕКТНО(“Б1”)

Казалось би, зачем ето делать? Ведь можно просто написать ссилку на ачејку в другој ачејке. Но преимусества етој функции в том, что в ссилку можете претворить лубуу строку, и даже ту, котораа создаетса с использованием других формула. Собственно, ето ми и делаем в формули.

Беионд Фунцтион АДРЕСА такође се користи у формули КОЛОНА и ЛИНЕ. Први враћа адресу ћелије на основу већ познатих бројева редова и колона. Овде је важно пратити тачан редослед. Прво се наводи ред, а затим само колона. На пример, функција АДРЕСА(4;2) вратиће адресу $Б$2.

Следуусаа используемаа више функциа – ето КОЛОНА. Овде је неопходно да формула добије број колоне из одређене референце. На пример, ако користите параметар Б2 у заградама ове функције, она ће вратити број 2, пошто је друга колона колона Б.

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

А сада ћемо користити не апстрактне примере, већ врло специфичну формулу која је коришћена изнад:

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

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

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

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

Тако једноставно све испада!

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

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

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

На пример, можете написати одмах код, коториј будет менать местами строки и колонки.

Суб ТранспосеЦолумнсРовс()

    Дим СоурцеРанге Ас Ранге

    Дим ДестРанге Ас Ранге

    Подесите СоурцеРанге = Апплицатион.ИнпутБок(Промпт:=»Молимо изаберите опсег за транспоновање», Титле:=»Транспосе Ровс то Цолумнс», Типе:=8)

    Подесите ДестРанге = Апплицатион.ИнпутБок(Промпт:=»Изаберите горњу леву ћелију одредишног опсега», Титле:=»Транспосе Ровс то Цолумнс», Типе:=8)

    СоурцеРанге.Цопи

    ДестРанге.Селецт

    Селецтион.ПастеСпециал Пасте:=клПастеАлл, Оператион:=клНоне, СкипБланкс:=Фалсе, Транспосе:=Труе

    Апплицатион.ЦутЦопиМоде = Фалсе

Енд Суб

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