змест
Учора на марафоне 30 функцый Excel за 30 дзён мы падлічылі колькасць слупкоў у дыяпазоне з дапамогай функцыі Калоны (NUMBERCOLUMN), а цяпер прыйшоў час чагосьці больш запатрабаванага.
13-ы дзень марафону мы прысвяцім вывучэнню функцыі Транспанаванне (ТРАНСП). З дапамогай гэтай функцыі вы можаце паварочваць дадзеныя, ператвараючы вертыкальныя вобласці ў гарызантальныя і наадварот. Ці ёсць у вас такая патрэба? Ці можна зрабіць гэта з дапамогай адмысловай устаўкі? Ці могуць гэта зрабіць іншыя функцыі?
Такім чынам, давайце звернемся да інфармацыі і прыкладаў па функцыі Транспанаванне (ТРАНСП). Калі ў вас ёсць дадатковая інфармацыя або прыклады, падзяліцеся імі ў каментарах.
Функцыя 13: ТРАНСПАНІЗАЦЫЯ
функцыя Транспанаванне (TRANSPOSE) вяртае гарызантальны дыяпазон ячэек як вертыкальны дыяпазон, ці наадварот.
Як можна выкарыстоўваць функцыю TRANSPOSE?
функцыя Транспанаванне (TRANSP) можа змяняць арыентацыю дадзеных, а таксама працаваць у спалучэнні з іншымі функцыямі:
- Зменіце гарызантальнае размяшчэнне даных на вертыкальнае.
- Паказаць лепшыя агульныя заробкі за апошнія гады.
Каб змяніць арыентацыю даных без стварэння спасылак на зыходныя даныя:
- Выкарыстоўваць паста Спец (Спецыяльная паста) > транспонировать (Транспанаваць).
Сінтаксіс TRANSPOSE (TRANSP)
функцыя Транспанаванне (TRANSPOSE) мае наступны сінтаксіс:
TRANSPOSE(array)
ТРАНСП(массив)
- масіў (масіў) - гэта масіў або дыяпазон ячэек, якія трэба транспанаваць.
Пасткі TRANSPOSE (ТРАНСПАНІЗАЦЫЯ)
- функцыя Транспанаванне (TRANSPOSE) неабходна ўвесці як формулу масіва, націснуўшы Ctrl + Shift + Enter.
- Дыяпазон, які атрымаецца ў выніку пераўтварэння з дапамогай функцыі Транспанаванне (TRANSPOSE) павінна мець такую ж колькасць радкоў і слупкоў, колькі зыходны дыяпазон мае слупкі і радкі адпаведна.
Прыклад 1: ператварэнне гарызантальных даных у вертыкальныя
Калі даныя гарызантальныя ў аркушы Excel, вы можаце ўжыць функцыю Транспанаванне (TRANSPOSE), каб перавесці іх у вертыкальнае становішча, але ў іншым месцы на аркушы. Напрыклад, у выніковай табліцы бенчмаркаў зручней было б вертыкальнае размяшчэнне. Выкарыстанне функцыі Транспанаванне (TRANSPOSE), вы можаце спасылацца на зыходныя гарызантальныя даныя, не змяняючы іх размяшчэнне.
Каб транспанаваць гарызантальны дыяпазон 2 × 4 у вертыкальны дыяпазон 4 × 2:
- Выберыце 8 вочак, куды вы хочаце размясціць атрыманы вертыкальны дыяпазон. У нашым прыкладзе гэта будуць вочкі B4:C7.
- Увядзіце наступную формулу і ператварыце яе ў формулу масіва, націснуўшы Ctrl + Shift + Enter.
=TRANSPOSE(B1:E2)
=ТРАНСП(B1:E2)
Фігурныя дужкі будуць аўтаматычна дададзены ў пачатку і ў канцы формулы, каб паказаць, што была ўведзена формула масіву.
Замест Транспанаванне (TRANSPOSE), Вы можаце выкарыстоўваць іншую функцыю для пераўтварэння даных, напрыклад, індэкс (ІНДЭКС). Вам не патрабуецца ўводзіць формулу масіва, і вам не трэба выбіраць усе вочкі ў мэтавай вобласці пры стварэнні формулы.
=INDEX($B$2:$E$2,,ROW()-ROW(C$4)+1)
=ИНДЕКС($B$2:$E$2;;СТРОКА()-СТРОКА(C$4)+1)
Прыклад 2: Змяніць арыентацыю без спасылак
Калі вы проста хочаце змяніць арыентацыю вашых даных, не захоўваючы спасылкі на зыходныя даныя, вы можаце выкарыстоўваць Спецыяльную ўстаўку:
- Выберыце зыходныя дадзеныя і скапіруйце іх.
- Выберыце верхнюю левую ячэйку вобласці, дзе вы хочаце змясціць вынік.
- На ўкладцы Дадаткова Галоўная (Дадому) пстрыкніце выпадальнае меню каманд ўстаўляць (Уставіць).
- выберыце транспонировать (Транспанаваць).
- Выдаліць зыходныя дадзеныя (неабавязкова).
Прыклад 3: лепшы агульны заробак за апошнія гады
функцыя Транспанаванне (TRANSP) можна выкарыстоўваць у спалучэнні з іншымі функцыямі, напрыклад, у гэтай цудоўнай формуле. Гэта было апублікавана Харланам Гроўвам у блоку навін Excel падчас дыскусіі аб разліку найлепшага агульнага заробку за апошнія 5 гадоў (запар!).
=MAX(MMULT(A8:J8, --(ABS(TRANSPOSE(COLUMN(A8:J8))-COLUMN(OFFSET(A8:J8,0,0,1,COLUMNS(A8:J8)-Number+1))-(Number-1)/2)
=МАКС(МУМНОЖ(A8:J8; --(ABS(ТРАНСП(СТОЛБЕЦ(A8:J8))-СТОЛБЕЦ(СМЕЩ(A8:J8;0;0;1;ЧИСЛСТОЛБ(A8:J8)-Number+1))-(Number-1)/2)
Як можна паняць па фігурным скобкам у радку формулы – гэта формула масіва. Ячэйка A5 названа Нумар і ў гэтым прыкладзе лік 4 уведзены, як значэнне для колькасці гадоў.
Формула правярае дыяпазоны, каб убачыць дастаткова ці ў іх паслядоўных столбцов. Вынікі праверкі (1 або 0) памнажаюцца на значэнні ячэек, каб атрымаць суммарны аб'ём працоўнай платы.
Для праверкі вынікаў на малюнку ніжэй у радку пад значэннямі зарплаты паказаны суммарныя значэнні для кожнай стартавай ячэйкі, пры гэтым максімальнае значэнне вылучана жёлтым. Гэта больш доўгі шлях да таго ж выніку, што папярэдняя формула масіва атрымлівае ў адной ячэйцы!
