Функцыя транспанавання ў Excel

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

Функцыя TRANSPOSE – транспанаваць дыяпазоны вочак у Excel

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

Сінтаксіс функцыі

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

Транспанаванне вертыкальных дыяпазонаў ячэек (слупкоў)

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

Паслядоўнасць дзеянняў для выкарыстання гэтай формулы наступная:

1. Выберыце радок. У нашым выпадку ён мае даўжыню пяць вочак.
2. Пасля гэтага перамесціце курсор на радок формул і ўвядзіце туды формулу =TRANSP(B2:B6).
3. Націсніце камбінацыю клавіш Ctrl + Shift + Enter.

Натуральна, што ў вашым выпадку вам трэба пазначыць дыяпазон, характэрны для вашай табліцы.

Транспанаванне гарызантальных дыяпазонаў ячэек (радкоў)

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

1. Вылучыце гэты слупок мышкай. Вы таксама можаце выкарыстоўваць клавішы на клавіятуры Ctrl і стрэлку ўніз пасля націску на самую верхнюю ячэйку гэтага слупка.
2. Пасля гэтага пішам формулу =TRANSP(B10:F10) у радку формул.
3. Запісваем яго ў выглядзе формулы масіва з дапамогай камбінацыі клавіш Ctrl + Shift + Enter.

Транспанаванне з дапамогай Paste Special

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

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

3 спосабу транспанавання табліцы ў Excel

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

Спосаб 1: Спецыяльная ўстаўка

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

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

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

1. Выберыце дыяпазон дадзеных, якія нам трэба павярнуць. Пасля гэтага капіруем гэтыя дадзеныя.
2. Размесціце курсор у любым месцы аркуша. Затым пстрыкніце правай кнопкай мышы і адкрыйце кантэкстнае меню.
3. Затым націсніце на кнопку «Спецыяльная ўстаўка».

Пасля выканання гэтых крокаў вам трэба націснуць на кнопку «Транспанаваць». Хутчэй пастаўце галачку насупраць гэтага пункта. Іншыя налады не мяняем, а затым націскаем на кнопку «ОК».

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

Прыкладна такія ж дзеянні трэба выканаць для пераносу значэнняў, а не формул. У гэтым выпадку вам таксама трэба скарыстацца меню «Спецыяльная ўстаўка», але перад гэтым абярыце дыяпазон даных, якія змяшчаюць значэнні. Мы бачым, што акно Paste Special можна выклікаць двума спосабамі: праз спецыяльнае меню на стужцы або кантэкстнае меню.

Спосаб 2. Функцыя TRANSP ў Excel

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

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

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

Пасля таго, як мы ўвялі дадзеныя, націскаем клавішу Enter, пасля чаго атрымліваем наступны вынік.

Мы бачым, што формула не была перанесена ў новую табліцу. Фарматаванне таксама было страчана. Паэта

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

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

зводная табліца

Гэта прынцыпова новы метад, які дазваляе не толькі транспанаваць табліцу, але і выконваць велізарная колькасць дзеянняў. Праўда, механізм транспазіцыі будзе некалькі адрознівацца ад папярэдніх спосабаў. Паслядоўнасць дзеянняў такая:

1. Давайце зробім зводную табліцу. Для гэтага трэба выбраць табліцу, якую нам неабходна транспанаваць. Пасля гэтага перайдзіце ў пункт «Устаўка» і знайдзіце там «Зводную табліцу». З'явіцца дыялогавае акно, як на гэтым скрыншоце.
2. Тут можна пераназначыць дыяпазон, з якога ён будзе вырабляцца, а таксама зрабіць шэраг іншых налад. Цяпер нас цікавіць у першую чаргу месца зводнай табліцы – на новым аркушы.
3. Пасля гэтага макет зводнай табліцы будзе створаны аўтаматычна. Неабходна пазначыць у ім тыя прадметы, якія мы будзем выкарыстоўваць, а затым іх неабходна перанесці ў патрэбнае месца. У нашым выпадку нам трэба перанесці пункт «Прадукт» у «Назвы слупкоў», а «Кошт за штуку» у «Значэнні».
4. Пасля гэтага зводная табліца будзе канчаткова створана. Дадатковы бонус - аўтаматычны разлік канчатковага кошту.
5. Вы таксама можаце змяніць іншыя параметры. Напрыклад, зніміце галачку з пункта «Цана за штуку» і адзначце пункт «Агульны кошт». У выніку мы атрымаем табліцу з інфармацыяй аб тым, колькі каштуюць прадукты. Гэты метад транспазіцыі значна больш функцыянальны, чым іншыя. Давайце апішам некаторыя перавагі зводных табліц:

1. Аўтаматызацыя. З дапамогай зводных табліц можна аўтаматычна сумаваць дадзеныя, а таксама адвольна змяняць становішча слупкоў і слупкоў. Для гэтага не трэба выконваць ніякіх дадатковых дзеянняў.
2. Інтэрактыўнасць. Карыстальнік можа змяняць структуру інфармацыі столькі разоў, колькі яму неабходна для выканання пастаўленых задач. Напрыклад, вы можаце змяніць парадак слупкоў, а таксама згрупаваць дадзеныя адвольным чынам. Гэта можна рабіць столькі разоў, колькі спатрэбіцца карыстальніку. І займае гэта літаральна менш за хвіліну.
3. Лёгкае фарматаванне даных. Аформіць зводную табліцу так, як хочацца чалавеку, вельмі проста. Для гэтага дастаткова зрабіць некалькі клікаў мышкай.
4. Атрыманне значэнняў. Пераважная колькасць формул, якія выкарыстоўваюцца для стварэння справаздач, знаходзяцца ў непасрэдным доступе чалавека і лёгка інтэгруюцца ў зводную табліцу. Гэта такія дадзеныя, як сумаванне, атрыманне сярэдняга арыфметычнага, вызначэнне колькасці вочак, множанне, знаходжанне найбольшага і найменшага значэнняў у зададзенай выбарцы.
5. Магчымасць стварэння зводных дыяграм. Калі зводныя табліцы пералічваюцца, звязаныя з імі дыяграмы аўтаматычна абнаўляюцца. Можна стварыць колькі заўгодна дыяграм. Усе яны могуць быць зменены для канкрэтнай задачы, і яны не будуць звязаны паміж сабой.
6. Магчымасць фільтрацыі даных.
7. Можна пабудаваць зводную табліцу на аснове больш чым аднаго набору зыходнай інфармацыі. Такім чынам, іх функцыянальнасць стане яшчэ больш.

Праўда, пры выкарыстанні зводных табліц неабходна ўлічваць наступныя абмежаванні:

1. Не ўсю інфармацыю можна выкарыстоўваць для стварэння зводных табліц. Перш чым іх можна будзе выкарыстоўваць для гэтай мэты, клеткі павінны быць нармалізаваны. Простымі словамі, зразумейце гэта правільна. Абавязковыя патрабаванні: наяўнасць радка загалоўка, запоўненасць усіх радкоў, роўнасць фарматаў даных.
2. Дадзеныя абнаўляюцца паўаўтаматычна. Для атрымання новай інфармацыі ў зводнай табліцы неабходна націснуць на адмысловую кнопку.
3. Зводныя табліцы займаюць шмат месца. Гэта можа прывесці да некаторых збояў у працы кампутара. Акрамя таго, з-за гэтага файл будзе цяжка адправіць па электроннай пошце.

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