Ինչպես վերափոխել տողերը սյունակների Excel-ում

Այս ուղեցույցը բացատրում է, թե ինչ անել Excel-ում տողերը սյունակների վերածելու համար: Excel-ից օգտվելիս հաճախ պետք է տողերն ու սյունակները փոխանակել աղյուսակում: Օրինակ՝ պատահում է, որ մարդը հսկայական սեղան է պատրաստել, հետո հասկացել է, որ այն կարդալը շատ ավելի հեշտ է, եթե շուռ ես տալիս։ 

Эта детальная инструкция расскажет о нескольких способах транспонирования Excel-taблицы, а также о часто встречаемых ошибках, с которыми може да столкнуться пользователь. Все они може да использоваться на любой версии Excel, ինչպես очень старой, так и самой новой.

Օգտագործելով Paste Special հատկությունը

Ենթադրենք, դուք ունեք երկու տվյալների հավաքածու: Երկրորդն այն է, ինչով ուզում ես վերջանալ: Առաջինը աղյուսակն է, որը պետք է փոխադրվի: Առաջին տարբերակում երկրների անունները ներկայացված են տարբեր սյունակներում, և շատ անհարմար է կարդալ, և առավել եւս՝ համեմատել պետությունների բնութագրերը միմյանց հետ։ Հետեւաբար, շատ ավելի լավ է աղյուսակը կազմակերպել այնպես, որ երկրների անունները հայտնվեն տարբեր սյունակներում:

Տողերն ու սյունակները փոխելու համար կատարեք հետևյալը.

1. Ընտրեք բնօրինակ աղյուսակը: Եթե ​​ցանկանում եք միանգամից տեսնել ամբողջ աղյուսակը (եթե այն շատ մեծ է), պետք է սեղմել Ctrl + Home ստեղնաշարի համադրությունը, իսկ դրանից հետո՝ Ctrl + Shift + End:
2. Այնուհետև բջիջները պատճենվում են: Դա կարելի է անել կամ համատեքստի մենյուի միջոցով կամ սեղմելով Ctrl+C կոճակների համակցությունը: Խորհուրդ է տրվում անմիջապես ընտելանալ վերջին տարբերակին, քանի որ եթե սովորեք տաք ստեղներ, կարող եք շատ առաջադրանքներ կատարել բառացիորեն մեկ վայրկյանում։
3. Ընտրեք թիրախային տիրույթի առաջին բջիջը: При этом он должен находиться за пределами таблицы. Также необходимо подоброть ячейку таким образом, чтобы таблица после транспонирования не пересекалась со другими данными. Օրինակ, եթե սկզբնական աղյուսակը ունի 4 սյունակ և 10 տող, ապա այս բոլոր գործողությունները կատարելուց հետո այն կտարածի 10 բջիջ դեպի ներքև և 4 բջիջ դեպի կողք։ Հետևաբար, այս միջակայքում (թիրախային բջիջից հաշվելով) ոչինչ չպետք է լինի:
4. Թիրախային բջիջի վրա դուք պետք է սեղմեք աջը և ընտրեք «Կպցնել Հատուկ», այնուհետև նշեք «Տրանսպոզի» մակագրության կողքին գտնվող վանդակը:

Կարեւոր է. եթե աղբյուրի աղյուսակում կան բանաձևեր, կարևոր է ապահովել, որ յուրաքանչյուր բջիջում օգտագործվեն բացարձակ հղումներ: Դա պետք է արվի, որպեսզի բոլոր հղումները ավտոմատ կերպով թարմացվեն:

Огромное преимущество «Специјальная вставка» և փակցնելու հնարավորություններ փոխանցելու հնարավորությունը: И при этом полностью сохраняется форматировение, что также позволяет сэкономить кучу времени.

Несмотря на эти явные плюсы, есть и ряд серьезных недостатков, которые мешают этому մեթոդу называться универсальным:

1. Վատ է այն օգտագործել լիարժեք աղյուսակների փոխադրման համար, որոնք չեն կրճատվում մինչև սովորական արժեքների միջակայք: Այս դեպքում «Transpose» գործառույթը կանջատվի: Այս խնդրի շուրջ աշխատելու համար դուք պետք է աղյուսակը փոխարկեք տիրույթի:
2. Այս մեթոդը լավ հարմար է մեկանգամյա փոխադրման համար, քանի որ այն չի կապում նոր աղյուսակը սկզբնական տվյալների հետ: Պարզ բառերով ասած, երբ աղյուսակներից մեկը փոխում եք, երկրորդի վրա ինֆորմացիան ավտոմատ չի թարմացվի։ Հետևաբար, փոխադրումը պետք է կրկնվի։ 

Ինչպե՞ս փոխանակել տողերն ու սյունակները և դրանք կապել հիմնական աղյուսակին:

Այսպիսով, ի՞նչ կարելի է անել, որպեսզի «Paste Special» տարբերակը կարողանա աղյուսակը կապել հիմնական տվյալների և փոխադրման արդյունքում ստացված տվյալների հետ: Ի վերջո, բոլորը կցանկանային, որ տվյալները ավտոմատ կերպով թարմացվեն:

1. Скопировать таблицу, для которой требуется транспонирование.
2. Աղյուսակի դատարկ տարածքում ընտրեք առանց տվյալների բջիջ:
3. Գործարկեք Paste Special ընտրացանկը, որը նման է նախորդ օրինակին: Դրանից հետո անհրաժեշտ է սեղմել «Տեղադրել հղումը» կոճակը, որը կարելի է գտնել ներքևի ձախ մասում:
4. Արդյունքը կլինի հետևյալը.
5. Выбрать новую таблицу и запустить окно «Найти и заменить» путем нажатия կոմբինացիաներ клавиш Ctrl + H.
6. Заменить все знаки ввода формулы (=) на ххх (или любую другую комбинацию знаков, которой нет в оригинальной таблице).
7. Արդյունքում սարսափելի բան կստացվի, բայց սա պարտադիր միջանկյալ պայման է ցանկալի արդյունքի հասնելու համար։ Այդ ժամանակ ամեն ինչ գեղեցիկ տեսք կունենա։
8. Պատճենեք ստացված աղյուսակը և այն տեղափոխելու համար օգտագործեք Paste Special-ը:

Դրանից հետո պարզապես անհրաժեշտ է նորից բացել «Գտնել և փոխարինել» երկխոսությունը և օգտագործել այն բջիջներում «xxx»-ը «=»-ի փոխելու համար, որպեսզի բոլոր բջիջները կապված լինեն բնօրինակ տեղեկատվության հետ:Իհարկե, սա ավելի բարդ է և ավելի երկար, բայց այս մեթոդը թույլ է տալիս շրջանցել սկզբնական աղյուսակի հղման բացակայությունը: Բայց այս մոտեցումն ունի նաև թերություն. Դա արտահայտվում է ֆորմատավորումն ինքնուրույն վերսկսելու անհրաժեշտությամբ։

Բանաձևերի կիրառում

Կան երկու գործառույթ, որոնք թույլ են տալիս ճկուն կերպով փոխել տողերն ու սյունակները. ТРАНСП и ДВССЫЛ. Здесь также есть возможность сохранить связь с первоначальной таблицей, но механика работы несколько иная.

գործառույթը TRANSP

Փաստորեն, այս բանաձևը ուղղակիորեն փոխադրում է աղյուսակը: Շարահյուսությունը հետևյալն է.

=TRANSP (զանգված)

Այժմ մենք կփորձենք կիրառել այն աղյուսակի վրա, որը պարունակում է տեղեկատվություն առանձին նահանգների բնակչության մասին:

1. Հաշվե՛ք աղյուսակի սյունակների և տողերի քանակը և թերթի վրա գտե՛ք նույն չափսերն ունեցող դատարկ տարածք:
2. Սկսեք խմբագրման ռեժիմը՝ սեղմելով F2 ստեղնը:
3. Գրեք ֆունկցիա ՏՐԱՆՍՊ տվյալների տիրույթը փակագծերում: Այս ֆունկցիան օգտագործելիս կարևոր է միշտ օգտագործել բացարձակ հղումներ:
4. Սեղմեք ստեղների համակցությունը Ctrl+Shift+Enter: Կարևոր է սեղմել ստեղների համադրությունը, հակառակ դեպքում բանաձևը կհրաժարվի աշխատելուց:

Վերջ, հիմա արդյունքն այսպիսին է.

Այս բանաձևի առավելությունը նույնն է, ինչ նախորդ մեթոդը (օգտագործելով «գտնել և փոխարինել» գործառույթը) այն է, որ երբ հիմքում ընկած տեղեկատվությունը փոխվում է, փոխադրված աղյուսակը ինքնաբերաբար կթարմացվի:

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

1. Ֆորմատավորումը, ինչպես Find and Replace մեթոդի դեպքում, չի պահպանվի:
2. Բնօրինակ աղյուսակը պետք է պարունակի որոշակի տվյալներ, հակառակ դեպքում որոշ բջիջներում զրոներ կլինեն:
3. Излишняя зависимость от источника данных. То есть, этот метод имеет противоположный по недостаток – անհնար է изменять транспонированную таблицу. Если попытаться это сделать, программа скажет, что անհնար է կարգավորել часть массива.

Բացի այդ, այս գործառույթը միշտ չէ, որ կարող է ճկուն կերպով կիրառվել տարբեր իրավիճակներում, այնպես որ դուք պետք է իմանաք դրա մասին, բայց օգտագործեք ավելի արդյունավետ տեխնիկա:

Օգտագործելով INDIRECT բանաձևը

Այս մեթոդի մեխանիզմը շատ նման է բանաձևի օգտագործմանը ՏՐԱՆՍՊ, но при этом его использование решает խնդիրը չի կարող կարգավորել տրանսպոնիրովանույու տաբլուց առանց потери связи со հիմնականй տեղեկատվական.

Но одной формулы Անկախ բավարար չէ. դուք դեռ պետք է օգտագործեք գործառույթը ՀԱՍՑԵ. Այս օրինակում մեծ աղյուսակ չի լինի, որպեսզի ձեզ չծանրաբեռնեն շատ ավելորդ տեղեկություններով:

Այսպիսով, ենթադրենք, դուք ունեք նման աղյուսակ, որը բաղկացած է 4 սյունակից և 5 տողից:

Անհրաժեշտ է կատարել հետևյալ գործողությունները.

1. Մուտքագրեք այս բանաձևը. =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1))) նոր աղյուսակի վերին ձախ բջիջում (մեր օրինակում դա A7 է) և սեղմեք enter ստեղնը: Եթե ​​տեղեկատվությունը չի սկսվում առաջին շարքում կամ առաջին սյունակում, ապա դուք ստիպված կլինեք օգտագործել ավելի բարդ բանաձև. =ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1)-СТОЛБЕЦ($A$1)+СТРОКА($A$1);СТРОКА(A1)-СТРОКА($A$1)+СТОЛБЕЦ($A$1))). Այս բանաձևում A1-ը նշանակում է աղյուսակի վերին բջիջը, որի հիման վրա կձևավորվի փոխադրված բջիջը:
2. Ընդլայնել բանաձևը ամբողջ տարածքում, որտեղ կտեղադրվի աղյուսակի նոր տարբերակը: Դա անելու համար քաշեք առաջին բջիջի ստորին աջ անկյունում գտնվող նշիչը դեպի ապագա աղյուսակի հակառակ ծայրը:
3. Բոլորը! Աղյուսակը հաջողությամբ փոխադրվեց, և դուք դեռ կարող եք այն խմբագրել: Իհարկե, նրա արտաքին տեսքը շատ ցանկալի բան է թողնում, բայց դա շտկելը դժվար չէ։ Ճիշտ ձևաչափումը վերականգնելու համար հարկավոր է պատճենել աղյուսակը, որը մենք տեղափոխել ենք (այսինքն՝ բնօրինակը), ապա ընտրել նոր ստեղծված աղյուսակը։ Հաջորդը, աջ սեղմեք ընտրված տիրույթի վրա, այնուհետև կտտացրեք «Ֆորմատավորում» մածուկի ընտրանքներում:

Այսպիսով, օգտագործելով գործառույթը Անկախ թույլ է տալիս խմբագրել վերջնական աղյուսակում բացարձակապես ցանկացած արժեք, և տվյալները միշտ կթարմացվեն սկզբնաղբյուրի աղյուսակում փոփոխություններ կատարելուն պես:

Իհարկե, այս մեթոդն ավելի բարդ է, բայց եթե այն գործնականում մշակես, ապա ամեն ինչ այնքան էլ սարսափելի չէ։

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

Ինչպե՞ս է գործում INDIRECT և ADDRESS բանաձևերի համադրությունը:

Վերջին անգամ, ինչպես կարելի է ասել, թե ինչպես կարելի է օգտագործել այս մեթոդը:

ֆունկցիա Անկախ մեր բանաձեւում օգտագործվում է անուղղակի բջջային հղում ստեղծելու համար: Օրինակ, եթե Ձեզ անհրաժեշտ է A8 բջիջում նշել նույն արժեքը, ինչ B1-ում, ապա կարող եք գրել բանաձևը.

=INDIRECT («B1»)

Казалось бы, зачем это делать? Ведь можно просто написать ссылку на ячейку в другой ячейке. Но преимущество этой функции в том, что в ссылку կարող է փոխակերպվել աբսոլютно любую строку, и даже ту, которая создается с использованием других формул. Собственно, это мы и делаем в формуле.

Ֆունկցիայի սահմաններից դուրս ՀԱՍՑԵ օգտագործվում է նաև բանաձևում ՇԱՐԱՍՅՈՒՆԸ и LINE. Առաջինը վերադարձնում է բջիջի հասցեն արդեն հայտնի տողերի և սյունակների համարների հիման վրա: Այստեղ կարևոր է հետևել ճիշտ հաջորդականությանը: Սկզբում նշվում է տողը, իսկ հետո միայն սյունակը: Օրինակ՝ ֆունկցիան ՀԱՍՑԵ(4;2) կվերադարձնի $B$2 հասցեն:

Следующая используемая выше функция – это ՇԱՐԱՍՅՈՒՆԸ. Այստեղ անհրաժեշտ է, որպեսզի բանաձևը ստանա սյունակի համարը կոնկրետ հղումից: Օրինակ, եթե այս ֆունկցիայի փակագծերում օգտագործեք B2 պարամետրը, ապա այն կվերադարձնի 2 թիվը, քանի որ երկրորդ սյունակը B սյունակն է:

Ակնհայտ է, որ ROW ֆունկցիան աշխատում է նույն կերպ, այն պարզապես վերադարձնում է տողի համարը: 

Եվ հիմա մենք կօգտագործենք ոչ թե վերացական օրինակներ, այլ շատ կոնկրետ բանաձև, որն օգտագործվել է վերևում.

=INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))

Այստեղ դուք կարող եք տեսնել դա առաջին հերթին գործառույթում ՀԱՍՑԵ նշված է սյունակը, և միայն դրանից հետո տողը: Եվ հենց այստեղ է թաքնված այս բանաձեւի աշխատունակության գաղտնիքը։ Մենք հիշում ենք, որ այս ֆունկցիան աշխատում է հայելային ձևով, և դրա առաջին արգումենտը տողի համարն է։ Եվ ստացվում է, որ երբ այնտեղ գրում ենք սյունակի հասցեն, այն վերածվում է տողի համարի և հակառակը։

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

1. Մենք ստանում ենք սյունակների և տողերի համարները՝ օգտագործելով համապատասխան գործառույթները։
2. Օգտագործելով գործառույթը ՀԱՍՑԵ տողերը դառնում են սյունակներ և հակառակը:
3. ֆունկցիա Անկախ օգնում է ցուցադրել հայելային տվյալները բջիջում:

Ահա թե որքան պարզ է ամեն ինչ ստացվում:

Օգտագործելով մակրո փոխադրման համար

Մակրոն փոքր ծրագիր է: Այն կարող է օգտագործվել գործընթացը ավտոմատացնելու համար: Կարևոր է հաշվի առնել, որ մակրոն ունի որոշ սահմանափակումներ: Տրանսպոզի առավելագույն մեթոդը թույլ է տալիս աշխատել 65536 տարրերի հետ: Եթե ​​այս սահմանը գերազանցվի, դա կհանգեցնի տվյալների կորստի:

Во всем остальном, это эффективный մեթոդը ավտոմատացման, որը կարող է մեծապես հեշտացնել жизнь.

Օրինակ, հնարավոր է գրել такой код, который будет менять местами строки и колонки.

Sub TransposeColumnsRows()

    Dim SourceRange As Range

    Dim DestRange As Range

    Սահմանեք SourceRange = Application.InputBox (Հուշում.=»Խնդրում ենք ընտրել փոխադրման միջակայքը», վերնագիր.=»Տողերը տեղափոխել սյունակներ», տեսակ՝=8)

    Սահմանեք DestRange = Application.InputBox(Prompt:=»Ընտրեք նպատակակետի տիրույթի վերին ձախ բջիջը», վերնագիր.=»Տողերը տեղափոխել սյունակներ», Type:=8)

    SourceRange.Copy

    DestRange.Ընտրեք

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

    Application.CutCopyMode = Կեղծ է

Վերջ Sub

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