Як пераўтварыць радкі ў слупкі ў Excel

Гэта кіраўніцтва тлумачыць, што рабіць, каб пераўтварыць радкі ў слупкі ў Excel. Пры выкарыстанні Excel часта даводзіцца мяняць месцамі радкі і слупкі ў табліцы. Напрыклад, бывае, што чалавек склаў велізарную табліцу, а потым зразумеў, што чытаць яе нашмат лягчэй, калі яе перавярнуць. 

У гэтай падрабязнай інструкцыі распавядаецца аб некалькіх спосабах транспаніравання Excel-табліц, а таксама аб часта сустракаемых памылках, з якімі можа столкнуться карыстальнік. Усе яны могуць падтрымліваць любую версію Excel, як вельмі старую, так і самую новую.

Выкарыстанне спецыяльнай функцыі ўстаўкі

Дапусцім, у вас ёсць два наборы даных. Другое - тое, што вы хочаце атрымаць у выніку. Першы - гэта табліца, якую трэба транспанаваць. У першым варыянце назвы краін прадстаўлены ў розных слупках, і чытаць, а тым больш параўноўваць характарыстыкі дзяржаў паміж сабой вельмі нязручна. Такім чынам, значна лепш арганізаваць табліцу так, каб назвы краін з'яўляліся ў розных слупках.Як пераўтварыць радкі ў слупкі ў Excel

Каб памяняць месцамі радкі і слупкі, зрабіце наступнае:

  1. Выберыце зыходны стол. Калі вы хочаце ўбачыць усю табліцу адразу (калі яна вельмі вялікая), трэба націснуць камбінацыю клавіш Ctrl + Home, а пасля гэтага – Ctrl + Shift + End.
  2. Затым клеткі капіююцца. Гэта можна зрабіць праз кантэкстнае меню або націснуўшы камбінацыю клавіш Ctrl+C. Да апошняга варыянту рэкамендуецца прызвычаіць сябе адразу, так як калі вы вывучыце гарачыя клавішы, то зможаце выконваць мноства задач літаральна за секунду.
  3. Выберыце першую ячэйку ў мэтавым дыяпазоне. Пры гэтым ён павінен знаходзіцца за межамі табліцы. Таксама неабходна падобраць ячэйку такім чынам, каб табліца пасля транспанавання не перасякалася з іншымі дадзенымі. Напрыклад, калі першапачатковая табліца мае 4 слупка і 10 радкоў, то пасля выканання ўсіх гэтых дзеянняў яна разыйдзецца на 10 вочак уніз і на 4 вочкі ў бок. Такім чынам, у гэтым дыяпазоне (адлічваючы ад ячэйкі-мішэні) не павінна быць нічога.
  4. На мэтавай вочку трэба клікнуць правай кнопкай мышы і выбраць «Спецыяльная ўстаўка», а затым паставіць галачку насупраць надпісы «Транспанаваць».Як пераўтварыць радкі ў слупкі ў Excel

Важна: калі ў зыходнай табліцы ёсць формулы, важна пераканацца, што ў кожнай ячэйцы выкарыстоўваюцца абсалютныя спасылкі. Гэта неабходна зрабіць для таго, каб усе спасылкі абнаўляліся аўтаматычна.

Велізарная перавага опцыі «Спецыяльная ўстаўка» заключаецца ў магчымасці транспанаваць табліцу ўсяго за некалькі секунд. І пры гэтым цалкам захоўваецца фарматаванне, што таксама дазваляе зэканоміць кучу часу.

Нягледзячы на ​​​​гэтыя яўныя плюсы, ёсць і шэраг сур'ёзных недахопаў, якія мяшаюць гэты метад называцца універсальным:

  1. Выкарыстоўваць яго для транспазіцыі паўнавартасных табліц, не зведзеных да банальнага дыяпазону значэнняў, дрэнна. У гэтым выпадку функцыя «Транспанаванне» будзе адключана. Каб абыйсці гэтую праблему, вы павінны пераўтварыць табліцу ў дыяпазон.
  2. Гэты метад добра падыходзіць для аднаразовага транспанавання, таму што ён не звязвае новую табліцу з зыходнымі дадзенымі. Прасцей кажучы, пры змене адной з табліц інфармацыя не будзе аўтаматычна абнаўляцца ў другой. Таму транспазіцыю прыйдзецца паўтарыць. 

Як памяняць месцамі радкі і слупкі і звязаць іх з асноўнай табліцай?

Такім чынам, што можна зрабіць, каб опцыя «Спецыяльная ўстаўка» змагла звязаць табліцу з асноўнымі дадзенымі і дадзенымі, атрыманымі ў выніку транспазіцыі? Бо кожны хацеў бы, каб дадзеныя абнаўляліся аўтаматычна.

  1. Скапіруйце табліцу, для якой патрабуецца транспанаванне.
  2. Выберыце вочка без дадзеных у пустой вобласці табліцы.
  3. Запусціце меню Спецыяльная ўстаўка, аналагічна папярэдняму прыкладу. Пасля гэтага трэба націснуць на кнопку «Уставіць спасылку», якая знаходзіцца ўнізе злева.Як пераўтварыць радкі ў слупкі ў Excel
  4. Вынік будзе наступны.Як пераўтварыць радкі ў слупкі ў Excel
  5. Выберыце новую табліцу і запусціце акно «Найсці і замяніць», націснуўшы камбінацыю клавіш Ctrl + H.
  6. Замяніць усе знакі ўводу формулы (=) на ххх (або любую іншую камбінацыю знакаў, якой няма ў арыгінальнай табліцы).Як пераўтварыць радкі ў слупкі ў Excel
  7. У выніку атрымаецца нешта жудаснае, але гэта абавязковая прамежкавая ўмова для дасягнення жаданага выніку. Тады ўсё будзе выглядаць прыгожа.Як пераўтварыць радкі ў слупкі ў Excel
  8. Скапіруйце атрыманую табліцу, а затым выкарыстоўвайце Спецыяльную ўстаўку, каб транспанаваць яе.

Пасля гэтага вам проста трэба зноў адкрыць дыялогавае акно «Знайсці і замяніць» і выкарыстоўваць яго, каб змяніць «xxx» у ячэйках на «=», каб усе ячэйкі былі звязаны з зыходнай інфармацыяй.Як пераўтварыць радкі ў слупкі ў ExcelВядома, гэта больш складана і даўжэй, але гэты спосаб дазваляе абыйсці адсутнасць спасылкі на зыходную табліцу. Але ў такога падыходу ёсць і недахоп. Выяўляецца яна ў неабходнасці самастойнага аднаўлення фарматавання.

Прымяненне формул

Ёсць дзве функцыі, якія дазваляюць гнутка змяняць радкі і слупкі: ТРАНСП і ДВССЫЛ. Тут таксама ёсць магчымасць захаваць сувязь з першапачатковай табліцай, але механіка працы некалькі іная.

функцыя ТРАНСП

Фактычна, гэтая формула непасрэдна транспануе электронную табліцу. Сінтаксіс наступны:

=TRANSP(масіў)

Зараз мы паспрабуем прымяніць гэта да табліцы, якая змяшчае інфармацыю аб колькасці насельніцтва асобных дзяржаў.Як пераўтварыць радкі ў слупкі ў Excel

  1. Падлічыце колькасць слупкоў і радкоў у табліцы і знайдзіце пустое месца на аркушы, якое мае такія ж памеры.
  2. Запусціце рэжым рэдагавання, націснуўшы клавішу F2.
  3. Напісаць функцыю ТРАНСП з дыяпазонам даных у дужках. Пры выкарыстанні гэтай функцыі важна заўсёды выкарыстоўваць абсалютныя спасылкі.Як пераўтварыць радкі ў слупкі ў Excel
  4. Націсніце камбінацыю клавіш Ctrl+Shift+Enter. Важна націскаць менавіта камбінацыю клавіш, інакш формула адмовіцца працаваць.

Вось і ўсё, цяпер вынік выглядае так!Як пераўтварыць радкі ў слупкі ў Excel

Перавага гэтай формулы такая ж, як і папярэдняга метаду (з выкарыстаннем функцыі «знайсці і замяніць») у тым, што пры змене асноўнай інфармацыі транспанаваная табліца аўтаматычна абнаўляецца.

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

  1. Фарматаванне, як і ў выпадку з метадам «Знайсці і замяніць», не захаваецца.
  2. Зыходная табліца павінна ўтрымліваць некаторыя дадзеныя, інакш у некаторых вочках будуць нулі.
  3. Излишняя зависимость от источника данных. Зрэшты, гэты метад мае супрацьстаянне па недахопу – немагчымасць змяніць транспанаваную табліцу. Калі паспрабаваць гэта зрабіць, праграма скажа, што немагчыма адрэдагаваць частку масіва.

Акрамя таго, гэтую функцыю не заўсёды можна гнутка ўжываць у розных сітуацыях, таму пра яе трэба ведаць, але выкарыстоўваць больш эфектыўныя прыёмы.

Выкарыстанне ўскоснай формулы

Механіка гэтага метаду вельмі падобная на выкарыстанне формулы ТРАНСП, але пры гэтым яго выкарыстанне вырашае праблему немагчымасці рэдагавання транспанаванай табліцы без сувязі з асноўнай інфармацыяй.

Но адной формулы ускосна недастаткова: вам усё яшчэ трэба выкарыстоўваць функцыю АДРАС. Вялікай табліцы ў гэтым прыкладзе не будзе, каб не перагружаць вас вялікай колькасцю непатрэбнай інфармацыі.

Такім чынам, дапусцім, у вас ёсць такая табліца, якая складаецца з 4 слупкоў і 5 радкоў.Як пераўтварыць радкі ў слупкі ў Excel

Неабходна выканаць наступныя дзеянні:

  1. Увядзіце гэтую формулу: =УСКОСНЫ(АДРАС(СТОЛПЦ(A1);РАДОК(A1))) у левую верхнюю ячэйку новай табліцы (у нашым прыкладзе гэта A7) і націсніце клавішу ўводу. Калі інфармацыя пачынаецца не ў першым радку або першым слупку, то вам прыйдзецца выкарыстоўваць больш складаную формулу: =ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1)-СТОЛБЕЦ($A$1)+СТРОКА($A$1);СТРОКА(A1)-СТРОКА($A$1)+СТОЛБЕЦ($A$1))). У дадзенай формуле пад А1 маецца на ўвазе верхняя вочка табліцы, на аснове якой будзе фармавацца транспонированная вочка.
  2. Пашырыце формулу на ўсю вобласць, дзе будзе размешчана новая версія табліцы. Для гэтага перацягніце маркер ў правым ніжнім куце першай ячэйкі ў супрацьлеглы канец будучай табліцы.Як пераўтварыць радкі ў слупкі ў Excel
  3. Усе! Табліца аказалася ўдала транспанаванай, і вы ўсё яшчэ можаце яе рэдагаваць. Вядома, яе знешні выгляд пакідае жадаць лепшага, але выправіць гэта не складзе працы.Як пераўтварыць радкі ў слупкі ў Excel Каб аднавіць правільнае фарматаванне, вам трэба скапіяваць табліцу, якую мы транспанавалі (г.зн. зыходную), а затым выбраць толькі што створаную табліцу. Затым пстрыкніце правай кнопкай мышы на абраным дыяпазоне, а затым націсніце «Фарматаванне» ў параметрах ўстаўкі.Як пераўтварыць радкі ў слупкі ў Excel

Такім чынам, выкарыстоўваючы функцыю ускосна дазваляе рэдагаваць абсалютна любое значэнне ў выніковай табліцы, і дадзеныя заўсёды будуць абнаўляцца, як толькі ўносяцца змены ў зыходную табліцу.

Вядома, гэты спосаб больш складаны, але калі адпрацаваць яго на практыцы, то ўсё не так страшна.

Гэты метад адзін з самых лепшых, таму што нягледзячы на ​​​​тое, што не захоўваецца фарматаванне ў навасозданай табліцы, яго можна лёгка аднавіць.

Як працуе спалучэнне формул УСКОСНЫЯ і АДРАС?

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

функцыя ускосна у нашай формуле выкарыстоўваецца для стварэння ўскоснай спасылкі на вочка. Напрыклад, калі вам трэба ўказаць тое ж значэнне ў вочку A8, што і ў B1, то вы можаце напісаць формулу

=УСКОСНЫ(“B1”)Як пераўтварыць радкі ў слупкі ў Excel

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

Па-за функцыяй АДРАС таксама выкарыстоўваецца ў формуле калонка и ЛІНІЯ. Першы вяртае адрас ячэйкі на аснове ўжо вядомых нумароў радкоў і слупкоў. Тут важна выконваць правільную паслядоўнасць. Спачатку задаецца радок, а потым толькі слупок. Напрыклад, функцыя АДРАС(4;2) верне адрас $B$2.

Наступная выкарыстоўваная вышэй функцыя – гэта калонка. Тут гэта неабходна для таго, каб формула атрымала нумар слупка з канкрэтнай спасылкі. Напрыклад, калі вы выкарыстоўваеце параметр B2 у дужках гэтай функцыі, яна верне лік 2, паколькі другі слупок - гэта слупок B.

Відавочна, што функцыя ROW працуе такім жа чынам, яна проста вяртае нумар радка. 

І цяпер мы будзем выкарыстоўваць не абстрактныя прыклады, а цалкам канкрэтную формулу, якая была выкарыстаная вышэй:

=УСКОСНЫ(АДРАС(СТОЛПЦ(A1);РАДОК(A1)))

Тут вы можаце ўбачыць, што спачатку ў функцыі АДРАС указваецца слупок, і толькі потым радок. І менавіта тут хаваецца сакрэт працаздольнасці гэтай формулы. Памятаем, што гэтая функцыя працуе люстрана, і першым аргументам у ёй з'яўляецца нумар радка. І атрымліваецца, што калі мы пішам туды адрас слупка, ён ператвараецца ў нумар радка і наадварот.

То ёсць, калі падытожыць:

  1. Мы атрымліваем нумары слупкоў і радкоў з дапамогай адпаведных функцый.
  2. Выкарыстанне функцыі АДРАС радкі становяцца слупкамі і наадварот.
  3. функцыя ускосна дапамагае адлюстраваць люстраныя дадзеныя ў вочку.

Вось як усё проста аказваецца!

Выкарыстанне макраса для транспанавання

Макрас - гэта невялікая праграма. Яго можна выкарыстоўваць для аўтаматызацыі працэсу. Важна ўлічваць, што макрас мае некаторыя абмежаванні. Максімальны метад Transpose дазваляе працаваць з 65536 элементамі. Калі гэты ліміт будзе перавышаны, гэта прывядзе да страты даных.

Ва ўсім астатнім, гэта эфектыўны метад аўтаматызацыі, які зможа значна аблегчыць жыццё.

Напрыклад, можна напісаць такі код, які будзе змяняць месцамі радкі і калонкі.

Sub TransposeColumnsRows()

    Dim SourceRange As Range

    Dim DestRange As Range

    Set SourceRange = Application.InputBox(Prompt:=»Please 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

Але калі ведаць у праграміраванні іншага няма, нічога страшнага. Можна скарыстацца апісанымі вышэй спосабамі. А потым вучыцца новаму па вымярэнні старога.

Пакінуць каментар