Съдържание
Това ръководство обяснява какво да направите, за да конвертирате редове в колони в Excel. Когато използвате Excel, често трябва да разменяте редове и колони в таблица. Например, случва се човек да е направил огромна маса и след това осъзнава, че е много по-лесно да я прочетете, ако я обърнете.
Тази подробна инструкция разказва за няколко начина за транспониране на Excel-таблици, както и за често срещаните грешки, с които може да се използва потребител. Всички те могат да използват всяка версия на Excel, както много стара, така и самата нова.
Използване на функцията за специално поставяне
Да приемем, че имате два набора от данни. Второто е това, с което искате да завършите. Първата е таблицата, която трябва да се транспонира. В първата версия имената на държавите са представени в различни колони и е много неудобно да се чете и още повече да се сравняват характеристиките на държавите една с друга. Затова е много по-добре таблицата да се организира така, че имената на държавите да се показват в различни колони.
За да размените редове и колони, направете следното:
- Изберете оригиналната таблица. Ако искате да видите цялата таблица наведнъж (ако е много голяма), трябва да натиснете клавишната комбинация Ctrl + Home, а след това – Ctrl + Shift + End.
- След това клетките се копират. Това може да стане или чрез контекстното меню, или чрез натискане на клавишната комбинация Ctrl+C. Препоръчително е незабавно да свикнете с последната опция, защото ако научите горещите клавиши, можете да изпълнявате много задачи буквално за една секунда.
- Изберете първата клетка в целевия диапазон. При това той трябва да се намира за пределите на таблиците. Също така е необходимо да се подобри ячейка по този начин, за да таблицата след транспониране не се премести с други данни. Например, ако първоначалната таблица има 4 колони и 10 реда, тогава след извършване на всички тези операции, тя ще се разпространи 10 клетки надолу и 4 клетки встрани. Следователно в рамките на този диапазон (броейки от целевата клетка) не трябва да има нищо.
- В целевата клетка трябва да щракнете с десния бутон на мишката и да изберете „Специално поставяне“ и след това да поставите отметка в квадратчето до надписа „Транспониране“.
Важно: ако в изходната таблица има формули, важно е да се гарантира, че във всяка клетка се използват абсолютни препратки. Това трябва да се направи, така че всички връзки да се актуализират автоматично.
Огромното предимство на опцията «Специална вставка» се състои във възможността да транспонирате таблицата само за няколко секунди. И при това напълно се запазва форматирането, което също позволява икономия на кучу време.
Независимо от тези явни плюсове, има и редица сериозни недостатъци, които мешат този метод да се нарича универсален:
- Лошо е да го използвате за транспониране на пълноценни таблици, които не са намалени до банален диапазон от стойности. В този случай функцията „Транспониране“ ще бъде деактивирана. За да заобиколите този проблем, трябва да конвертирате таблицата в диапазон.
- Този метод е много подходящ за еднократно транспониране, защото не свързва новата таблица с оригиналните данни. С прости думи, когато промените една от таблиците, информацията няма да се актуализира автоматично във втората. Следователно транспонирането ще трябва да се повтори.
Как да разменям редове и колони и да ги свързвам към основната таблица?
И така, какво може да се направи, така че опцията „Специално поставяне“ да може да свърже таблицата с основните данни и тези, получени от транспонирането? В крайна сметка всеки би искал данните да се актуализират автоматично.
- Скопирайте таблицата, за която се изисква транспониране.
- Изберете клетка без данни в празна област на таблицата.
- Стартирайте менюто Paste Special, подобно на предишния пример. След това трябва да кликнете върху бутона „Вмъкване на връзка“, който се намира долу вляво.
- Резултатът ще бъде следният.
- Изберете нова таблица и отворете прозореца «Намери и замени» чрез натискане на комбинации от клавиши Ctrl + H.
- Заменете всички знаци на въвеждане на формули (=) на ххх (или всяка друга комбинация от знаци, която няма в оригиналната таблица).
- В резултат на това ще се получи нещо ужасно, но това е задължително междинно условие за постигане на желания резултат. Тогава всичко ще изглежда красиво.
- Копирайте получената таблица и след това използвайте специално поставяне, за да я транспонирате.
След това просто трябва да отворите отново диалоговия прозорец „Намиране и замяна“ и да го използвате, за да промените „xxx“ в клетките на „=“, така че всички клетки да бъдат свързани с оригиналната информация.
Разбира се, това е по-сложно и по-дълго, но този метод ви позволява да заобиколите липсата на връзка към оригиналната таблица. Но този подход има и недостатък. Изразява се в необходимостта от независимо възобновяване на форматирането.
Приложение на формули
Има две функции, които ви позволяват гъвкаво да променяте редове и колони: ТРАНСП и ДВССИЛ. Тук също има възможност да запазите връзката с първоначалната таблица, но механичните работи са няколко други.
функция TRANSP
Всъщност тази формула директно транспонира електронната таблица. Синтаксисът е следният:
=TRANSP(масив)
Сега ще се опитаме да го приложим към таблица, която съдържа информация за населението на отделните щати.
- Пребройте броя на колоните и редовете в таблицата и намерете празно място в листа със същите размери.
- Стартирайте режима за редактиране, като натиснете клавиша F2.
- Напишете функция ТРАНСПОРТ с обхвата на данните в скоби. Важно е винаги да използвате абсолютни препратки, когато използвате тази функция.
- Натиснете клавишната комбинация Ctrl+Shift+Enter. Важно е да натиснете точно клавишната комбинация, в противен случай формулата ще откаже да работи.
Това е, сега резултатът изглежда така!
Предимството на тази формула е същото като предишния метод (с помощта на функцията „намери и замени“) е, че когато основната информация се промени, транспонираната таблица автоматично ще се актуализира.
Но недостатъците у вас също са:
- Форматирането, както в случая с метода Find and Replace, няма да бъде запазено.
- Оригиналната таблица трябва да съдържа някои данни, в противен случай в някои клетки ще има нули.
- Излишняя зависимост от източника на данни. Въпреки това, този метод има противоположност по недостатък – невъзможност да се промени транспонираната таблица. Ако опитате да направите това, програмата скаже, че е невъзможно да редактирате част от масива.
Освен това тази функция не винаги може да се прилага гъвкаво в различни ситуации, така че трябва да знаете за нея, но да използвате по-ефективни техники.
Използване на формулата INDIRECT
Механиката на този метод е много подобна на използването на формулата ТРАНСПОРТ, но при това неговото използване решава проблема с невъзможността за редактиране на транспонираната таблица без връзка с основната информация.
Но една формула НЕПРЯК не е достатъчно: все още трябва да използвате функцията АДРЕС. В този пример няма да има голяма таблица, за да не ви претовари с много ненужна информация.
И така, да кажем, че имате такава таблица, състояща се от 4 колони и 5 реда.
Необходимо е да се предприемат следните действия:
- Въведете тази формула: =ИНДИРЕКТНО(АДРЕС(КОЛОНА(A1),РЕД(A1))) в горната лява клетка на новата таблица (в нашия пример е A7) и натиснете клавиша enter. Ако информацията не започва в първия ред или първата колона, тогава ще трябва да използвате по-сложна формула: =ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1)-СТОЛБЕЦ($A$1)+СТРОКА($A$1);СТРОКА(A1)-СТРОКА($A$1)+СТОЛБЕЦ($A$1))). В тази формула A1 означава горната клетка на таблицата, на базата на която ще се формира транспонираната клетка.
- Разширете формулата до цялата област, където ще бъде поставена новата версия на таблицата. За да направите това, плъзнете маркера в долния десен ъгъл на първата клетка до противоположния край на бъдещата таблица.
- Всичко! Таблицата се оказа успешно транспонирана и все още можете да я редактирате. Разбира се, външният й вид оставя много да се желае, но коригирането му не е трудно. За да възстановите правилното форматиране, трябва да копирате таблицата, която транспонирахме (т.е. оригиналната), след което да изберете новосъздадената таблица. След това щракнете с десния бутон върху избрания диапазон и след това щракнете върху „Форматиране“ в опциите за поставяне.
И така, използвайки функцията НЕПРЯК ви позволява да редактирате абсолютно всяка стойност в крайната таблица и данните винаги ще бъдат актуализирани веднага щом бъдат направени промени в изходната таблица.
Разбира се, този метод е по-труден, но ако го изработите на практика, тогава всичко не е толкова страшно.
Този метод е един от най-добрите, тъй като въпреки това, че не се запазва форматирането в новосъздадената таблица, можете лесно да го възстановите.
Как работи комбинацията от формули INDIRECT и ADDRESS?
След това, както сте разбрали в това, как да използвате совокупността на тази формула за транспониране на таблици, вие, вероятно, искате да разберете по-дълбоко принципа на работа на този метод.
функция НЕПРЯК в нашата формула се използва за създаване на индиректна препратка към клетка. Например, ако трябва да зададете същата стойност в клетка A8 като в B1, тогава можете да напишете формулата
=INDIRECT("B1")
Казалось би, защо това да правим? Ведь можете просто да напишете връзка на ячейку в друга ячейка. Но предимството е тази функция в тази, че в връзката може да се превърне абсолютно всяка линия, и дори тази, която се създава с друга формула за използване. Собствено, това ние и правим във формулата.
Отвъд функцията АДРЕС също се използва във формулата КОЛОНА и LINE. Първият връща адреса на клетката въз основа на вече известните номера на редове и колони. Тук е важно да следвате правилната последователност. Първо се посочва редът, а след това само колоната. Например функцията АДРЕС(4;2) ще върне адреса $B$2.
Следващата използвана по-горе функция – това КОЛОНА. Тук е необходимо формулата да получи номера на колоната от конкретна препратка. Например, ако използвате параметъра B2 в скобите на тази функция, тогава тя ще върне числото 2, тъй като втората колона е колона B.
Очевидно функцията ROW работи по същия начин, тя просто връща номера на реда.
И сега ще използваме не абстрактни примери, а много конкретна формула, която беше използвана по-горе:
=ИНДИРЕКТНО(АДРЕС(КОЛОНА(A1),РЕД(A1)))
Тук можете да видите това първо във функцията АДРЕС посочва се колоната и едва след това линията. И именно тук се крие тайната на работоспособността на тази формула. Спомняме си, че тази функция работи огледално и първият аргумент в нея е номерът на реда. И се оказва, че когато напишем адреса на колона там, той се превръща в номер на ред и обратно.
Това е, ако подытожить:
- Получаваме номерата на колоните и редовете, като използваме съответните функции.
- Използване на функцията АДРЕС редовете стават колони и обратно.
- функция НЕПРЯК помага за показване на огледалните данни в клетката.
Ето колко просто се оказва всичко!
Използване на макрос за транспониране
Макросът е малка програма. Може да се използва за автоматизиране на процеса. Важно е да се има предвид, че макросът има някои ограничения. Максималният метод на транспониране ви позволява да работите с 65536 елемента. Ако това ограничение бъде превишено, това ще доведе до загуба на данни.
Във всичко останало, това е ефективен метод за автоматизация, който може значително да облегчи живота.
Например, можете да напишете такъв код, който ще променя местата на редове и колонки.
Sub TransposeColumnsRows()
Dim SourceRange като диапазон
Dim DestRange като диапазон
Set SourceRange = Application.InputBox(Prompt:=»Please select the range to transpose», Title:=»Transpose Rows to Columns», Type:=8)
Set DestRange = Application.InputBox(Prompt:=»Изберете горната лява клетка на целевия диапазон», Title:=»Transpose Rows to Columns», Type:=8)
SourceRange.Copy
DestRange.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = Невярно
End Sub
Но ако знаят в програмирането друго няма, нищо страшно. Може да се използва описаните по-горе способи. А потом учиться новому по измерванията на старото.