Innehåll
Den här guiden förklarar vad du ska göra för att konvertera rader till kolumner i Excel. När du använder Excel måste du ofta byta rader och kolumner i en tabell. Till exempel händer det att en person har gjort ett enormt bord, och då insåg han att det är mycket lättare att läsa det om man vänder på det.
Эта детальная инструкция расскажет о нескольких способах транспонирования Excel-таблицы, а также о часто встречаемых ошибках, с которыми может столкнуться пользователь. Все они могут использоваться на любой версии Excel, как очень старой, так och самой новой.
Använda funktionen Klistra in special
Låt oss säga att du har två datamängder. Det andra är vad du vill sluta med. Den första är tabellen som behöver införlivas. I den första versionen presenteras ländernas namn i olika kolumner, och det är mycket obekvämt att läsa, och ännu mer, att jämföra staternas egenskaper med varandra. Därför är det mycket bättre att organisera tabellen så att landsnamnen visas i olika kolumner.
Gör följande för att byta rader och kolumner:
- Välj den ursprungliga tabellen. Om du vill se hela tabellen på en gång (om den är väldigt stor), måste du trycka på tangentkombinationen Ctrl + Hem, och efter det – Ctrl + Skift + End.
- Cellerna kopieras sedan. Detta kan göras antingen via snabbmenyn eller genom att trycka på tangentkombinationen Ctrl+C. Det rekommenderas att du omedelbart vänjer dig vid det sista alternativet, för om du lär dig snabbtangenter kan du utföra många uppgifter bokstavligen på en sekund.
- Välj den första cellen i målområdet. При этом он должен находиться за пределами таблицы. Также необходимо подобрать ячейку таким образом, чтобы таблица после транспонирования не пересеськан. Till exempel, om den initiala tabellen har 4 kolumner och 10 rader, kommer den att sprida 10 celler nedåt och 4 celler åt sidan efter att ha utfört alla dessa operationer. Därför bör det inte finnas något inom detta intervall (räknat från målcellen).
- På målcellen måste du högerklicka och välja "Klistra in special" och sedan markera rutan bredvid inskriptionen "Transponera".
Viktigt: om det finns formler i källtabellen är det viktigt att se till att absoluta referenser används i varje cell. Detta måste göras för att alla länkar ska uppdateras automatiskt.
Огромное преимущество опции «Специальная вставка» och заключается в возможности транспонировать таблиск. И при этом полностью сохраняется форматирование, что также позволяет сэкономить кучу времени.
Несмотря на эти явные плюсы, есть и ряд серьезных недостатков, которые мешают этому методи назывин:
- Det är dåligt att använda det för att transponera fullfjädrade tabeller som inte reduceras till ett banalt värdeintervall. I det här fallet kommer funktionen "Transponera" att inaktiveras. För att kringgå det här problemet måste du konvertera tabellen till ett intervall.
- Denna metod är väl lämpad för en engångstransponering eftersom den inte associerar den nya tabellen med originaldata. Med enkla ord, när du ändrar en av tabellerna kommer informationen inte automatiskt att uppdateras på den andra. Därför måste införlivandet upprepas.
Hur byter man rader och kolumner och länkar dem till huvudtabellen?
Så, vad kan göras så att alternativet "Klistra in special" kan länka tabellen med huvuddata och den som är ett resultat av införlivandet? Alla skulle trots allt vilja att uppgifterna uppdateras automatiskt.
- Скопировать таблицу, för которой требуется транспонирование.
- Välj en cell utan data i ett tomt område av tabellen.
- Starta menyn Klistra in special, liknande det föregående exemplet. Efter det måste du klicka på knappen "Infoga länk", som finns längst ner till vänster.
- Resultatet blir följande.
- Выбрать новую таблицу и запустить окно «Найти и заменить» путем нажатия комбинации клавиш Ctrl + H.
- Заменить все знаки ввода формулы (=) på ххх (eller любую другую комбинацию знаков, которой нетинав оригую).
- Som ett resultat kommer något hemskt att visa sig, men detta är ett obligatoriskt mellanvillkor för att uppnå det önskade resultatet. Då kommer allt att se vackert ut.
- Kopiera den resulterande tabellen och använd sedan Paste Special för att transponera den.
Efter det behöver du bara öppna dialogrutan "Sök och ersätt" igen och använda den för att ändra "xxx" i cellerna till "=" så att alla celler associeras med den ursprungliga informationen.
Naturligtvis är detta mer komplicerat och längre, men den här metoden låter dig komma runt bristen på en länk till den ursprungliga tabellen. Men detta tillvägagångssätt har också en nackdel. Det uttrycks i behovet av att självständigt återuppta formateringen.
Tillämpning av formler
Det finns två funktioner som gör att du flexibelt kan ändra rader och kolumner: ТРАНСП и ДВССЫЛ. Здесь также есть возможность сохранить связь с первоначальной таблицей, но механика работы ненасколько.
funktion TRANSP
Den här formeln transponerar faktiskt kalkylarket direkt. Syntaxen är följande:
=TRANSP(matris)
Nu ska vi försöka tillämpa det på en tabell som innehåller information om befolkningen i enskilda stater.
- Räkna antalet kolumner och rader i tabellen och hitta ett tomt utrymme i arket som har samma dimensioner.
- Starta redigeringsläget genom att trycka på F2-tangenten.
- Skriv en funktion ÖVERFÖRING med dataintervallet inom parentes. Det är viktigt att alltid använda absoluta referenser när du använder denna funktion.
- Tryck på tangentkombinationen Ctrl+Skift+Enter. Det är viktigt att trycka på exakt tangentkombinationen, annars kommer formeln att vägra fungera.
Det var allt, nu ser resultatet ut så här!
Fördelen med denna formel är densamma som den tidigare metoden (med "hitta och ersätt"-funktionen) är att när den underliggande informationen ändras kommer den omvandlade tabellen att uppdateras automatiskt.
Но недостатки у нее тоже имеются:
- Formatering, som i fallet med metoden Sök och ersätt, kommer inte att sparas.
- Den ursprungliga tabellen måste innehålla vissa data, annars kommer det att finnas nollor i vissa celler.
- Излишняя зависимость от источника данных. То есть, этот метод имеет противоположный по недостаток – невозможность изменять транспонированниц транспонированниц. Если попытаться это сделать, программа скажет, что невозможно редактировать часть массива.
Dessutom kan den här funktionen inte alltid tillämpas flexibelt i olika situationer, så du måste känna till det, men använda mer effektiva tekniker.
Använder den INDIREKTA formeln
Mekaniken i denna metod är mycket lik att använda formeln ÖVERFÖRING,
Но одной формулы INDIREKT inte tillräckligt: du måste fortfarande använda funktionen ADRESS. Det kommer inte att finnas någon stor tabell i det här exemplet, för att inte överbelasta dig med en massa onödig information.
Så låt oss säga att du har en sådan tabell, bestående av 4 kolumner och 5 rader.
Följande åtgärder måste vidtas:
- Ange denna formel: =INDIREKT(ADRESS(KOLUMN(A1),RAD(A1))) i den övre vänstra cellen i den nya tabellen (i vårt exempel är det A7) och tryck på enter-tangenten. Om informationen inte börjar i den första raden eller den första kolumnen, måste du använda en mer komplex formel: =ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1)-СТОЛБЕЦ($A$1)+СТРОКА($A$1);СТРОКА(A1)-СТРОКА($A$1)+СТОЛБЕЦ($A$1))). I denna formel betyder A1 den översta cellen i tabellen, på grundval av vilken den transponerade cellen kommer att bildas.
- Utöka formeln till hela området där den nya versionen av tabellen kommer att placeras. För att göra detta, dra markören i det nedre högra hörnet av den första cellen till motsatta änden av den framtida tabellen.
- Allt! Tabellen visade sig vara framgångsrikt omvandlad, och du kan fortfarande redigera den. Naturligtvis lämnar hennes utseende mycket att önska, men att fixa det är inte svårt. För att återställa rätt formatering måste du kopiera tabellen som vi transponerade (det vill säga den ursprungliga) och välj sedan den nyskapade tabellen. Högerklicka sedan på det valda intervallet och klicka sedan på "Formatera" i klistraalternativen.
Så använder funktionen INDIREKT låter dig redigera absolut alla värden i den slutliga tabellen, och data kommer alltid att uppdateras så snart några ändringar görs i källtabellen.
Naturligtvis är den här metoden svårare, men om du tränar det i praktiken är allt inte så skrämmande.
Этот метод один из самых лучших, потому что несмотря на то, что не сохраняется форматирование.
Hur fungerar kombinationen av formlerna INDIREKT och ADRESS?
После того, как вы разобрались в том, как использовать совокупность этих формул för транспонирования танспользовати хочется более глубоко изучить принцип работы этого метода.
Funktion INDIREKT i vår formel används för att skapa en indirekt cellreferens. Om du till exempel behöver ange samma värde i cell A8 som i B1, kan du skriva formeln
=INDIREKT(“B1”)
Казалось бы, зачем это делать? Ведь можно просто написать ссылку на ячейку в другой ячейке. Но преимущество этой функции в том, что в ссылку можно превратить абсолютно любую строку, и даже ту, которая создается с использованием других формул. Собственно, это мы и делаем в FORMуле.
Bortom funktion ADRESS används också i formeln KOLUMN и LINJE. Den första returnerar adressen till cellen baserat på de redan kända rad- och kolumnnumren. Det är viktigt att följa rätt ordningsföljd här. Först anges raden och sedan endast kolumnen. Till exempel funktionen ADRESS(4;2) kommer att returnera adressen $B$2.
Следующая используемая выше функция – это KOLUMN. Det är nödvändigt här så att formeln får kolumnnumret från en specifik referens. Till exempel, om du använder B2-parametern inom parentes av denna funktion, kommer den att returnera siffran 2, eftersom den andra kolumnen är kolumn B.
Uppenbarligen fungerar ROW-funktionen på samma sätt, den returnerar bara radnumret.
Och nu kommer vi inte att använda abstrakta exempel, utan en mycket specifik formel som användes ovan:
=INDIREKT(ADRESS(KOLUMN(A1),RAD(A1)))
Här kan du se det först i funktionen ADRESS kolumnen anges, och först därefter raden. Och det är här som hemligheten bakom denna formels arbetsförmåga är gömd. Vi kommer ihåg att den här funktionen fungerar på ett spegelvänt sätt, och det första argumentet i den är radnumret. Och det visar sig att när vi skriver adressen till en kolumn där, blir den till ett radnummer och vice versa.
То есть, если подытожить:
- Vi får kolumn- och radnumren med hjälp av motsvarande funktioner.
- Använda funktionen ADRESS rader blir kolumner och vice versa.
- Funktion INDIREKT hjälper till att visa speglad data i cellen.
Så enkelt blir det hela!
Använda ett makro för att transponera
Ett makro är ett litet program. Det kan användas för att automatisera processen. Det är viktigt att tänka på att makrot har vissa begränsningar. Den maximala Transpose-metoden låter dig arbeta med 65536 element. Om denna gräns överskrids kommer det att resultera i dataförlust.
Во всем остальном, это эффективный метод автоматизации, который сможет значительно облегчизить жить.
Например, можно написать такой код, который будет менять местами строки и колонки.
Sub TransposeColumnsRows()
Dim SourceRange As Range
Dimma DestRange As Range
Ställ in SourceRange = Application.InputBox(Prompt:=»Välj intervallet att transponera», Titel:=»Transponera rader till kolumner», Typ:=8)
Ange DestRange = Application.InputBox(Prompt:=»Välj den övre vänstra cellen i destinationsintervallet», Titel:=»Transponera rader till kolumner», Typ:=8)
SourceRange.Copy
DestRange.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
End Sub
Но если знаний в программировании особо нет, ничего страшного. Можно воспользоваться описанными выше способами. А потом учиться новому по мере освоения старого.