Транспонирање на функција во 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

Друга можна опција за транспозиција е да се користи функцијата Paste Special. Ова повеќе не е оператор што треба да се користи во формули, но исто така е еден од популарните методи за претворање на колоните во редови и обратно.

Оваа опција е на табулаторот Home. За да пристапите до неа, треба да ја пронајдете групата „Табла со исечоци“ и таму да го пронајдете копчето „Вметни“. После тоа, отворете го менито што се наоѓа под оваа опција и изберете ја ставката „Пренеси“. Пред тоа, треба да го изберете опсегот што сакате да го изберете. Како резултат на тоа, ќе го добиеме истиот опсег, само спротивното се огледува.

3 начини за транспонирање табела во Excel

Но, всушност, има многу повеќе начини за претворање на колоните во редови и обратно. Ајде да опишеме 3 методи со кои можеме да транспонираме табела во Excel. Разговаравме за два од нив погоре, но ќе дадеме уште неколку примери за да добиете подобра идеја за uXNUMXbuXNUMXbкако да ја спроведете оваа постапка.

Метод 1: Специјално залепете

Овој метод е наједноставен. Доволно е да притиснете неколку копчиња и корисникот добива транспонирана верзија на табелата. Да дадеме мал пример за поголема јасност. Да претпоставиме дека имаме табела која содржи информации за тоа колку производи се моментално на залиха, како и колку чинат вкупно. Самата табела изгледа вака.

Гледаме дека имаме заглавие и колона со броеви на производи. Во нашиот пример, заглавието содржи информации за тоа кој производ, колку чини, колку е на залиха и колкава е вкупната цена на сите производи поврзани со оваа ставка што се на залиха. Трошокот го добиваме според формулата каде трошокот се множи со количината. За да го направиме примерот повизуелен, ајде да го направиме заглавието зелено.

Наша задача е да се осигураме дека информациите содржани во табелата се наоѓаат хоризонтално. Тоа е, така што колоните стануваат редови. Редоследот на дејства во нашиот случај ќе биде како што следува:

1. Изберете го опсегот на податоци што треба да ги ротираме. После тоа, ги копираме овие податоци.
2. Поставете го курсорот каде било на листот. Потоа кликнете на десното копче на глувчето и отворете го контекстното мени.
3. Потоа кликнете на копчето „Вметни специјално“.

Откако ќе ги завршите овие чекори, треба да кликнете на копчето „Транспонирање“. Наместо тоа, штиклирајте го полето до оваа ставка. Ние не ги менуваме другите поставки, а потоа кликнете на копчето „OK“.

По извршувањето на овие чекори, остануваме со истата табела, само нејзините редови и колони се распоредени поинаку. Исто така, имајте предвид дека ќелиите што ги содржат истите информации се означени со зелено. Прашање: што се случи со формулите што беа во оригиналниот опсег? Нивната локација е променета, но тие самите останаа. Адресите на ќелиите едноставно се сменија на оние што беа формирани по транспозицијата.

Речиси истите дејства треба да се извршат за да се транспонираат вредностите, а не формулите. Во овој случај, треба да го користите и менито Paste Special, но пред тоа, изберете го опсегот на податоци што ги содржат вредностите. Гледаме дека прозорецот Paste Special може да се повика на два начина: преку специјално мени на лентата или контекстното мени.

Метод 2. Функција TRANSP во Excel

Всушност, овој метод повеќе не се користи толку активно како што беше на самиот почеток на појавувањето на оваа програма за табеларни пресметки. Тоа е затоа што овој метод е многу покомплициран од користењето на Paste Special. Сепак, тој ја наоѓа својата употреба во автоматизирање на транспонирањето на табелата.

Исто така, оваа функција е во Excel, па затоа е императив да се знае за неа, иако веќе не се користи речиси. Претходно ја разгледавме процедурата, како да работиме со неа. Сега ќе го дополниме ова знаење со дополнителен пример.

1. Прво, треба да го избереме опсегот на податоци што ќе се користи за транспонирање на табелата. Само треба да ја изберете областа напротив. На пример, во овој пример имаме 4 колони и 6 редови. Затоа, неопходно е да се избере област со спротивни карактеристики: 6 колони и 4 редови. Сликата многу добро го покажува.
2. После тоа, веднаш почнуваме да ја пополнуваме оваа ќелија. Важно е случајно да не се отстрани изборот. Затоа, мора да ја наведете формулата директно во лентата со формули.
3. Следно, притиснете ја комбинацијата на копчиња Ctrl + Shift + Enter. Запомнете дека ова е формула за низа, бидејќи работиме со голем сет на податоци одеднаш, кои ќе бидат префрлени во друг голем сет на ќелии.

Откако ќе ги внесеме податоците, го притискаме копчето Enter, по што го добиваме следниот резултат.

Гледаме дека формулата не е пренесена во новата табела. Форматирањето исто така беше изгубено. Поето

Сето ова ќе треба да се направи рачно. Покрај тоа, запомнете дека оваа табела е поврзана со оригиналната. Затоа, штом некои информации се сменат во оригиналниот опсег, овие прилагодувања автоматски се прават во транспонираната табела.

Затоа, овој метод е добро прилагоден во случаи кога треба да се осигурате дека транспонираната табела е поврзана со оригиналната. Ако користите специјална влошка, оваа можност повеќе нема да биде.

сумарна табела

Ова е фундаментално нов метод, кој овозможува не само транспонирање на табелата, туку и извршување на огромен број дејства. Точно, механизмот за транспозиција ќе биде малку поинаков во споредба со претходните методи. Редоследот на дејствата е како што следува:

1. Ајде да направиме стожерна табела. За да го направите ова, треба да ја изберете табелата што треба да ја транспонираме. После тоа, одете во ставката „Вметни“ и побарајте „Стортна табела“ таму. Ќе се појави дијалог-кутија како онаа на оваа слика од екранот.
2. Овде можете да го преназначите опсегот од кој ќе биде направен, како и да направите голем број други поставки. Сега првенствено сме заинтересирани за местото на стожерната табела - на нов лист.
3. После тоа, распоредот на стожерната табела автоматски ќе се креира. Неопходно е да ги означиме во него оние предмети што ќе ги користиме, а потоа тие мора да се преместат на правилното место. Во нашиот случај, треба да ја преместиме ставката „Производ“ во „Имиња на колони“, а „Цена по парче“ во „Вредности“.
4. После тоа, стожерната табела конечно ќе се креира. Дополнителен бонус е автоматското пресметување на конечната вредност.
5. Можете да промените и други поставки. На пример, отштиклирајте ја ставката „Цена по парче“ и штиклирајте ја ставката „Вкупна цена“. Како резултат на тоа, ќе имаме табела која содржи информации за тоа колку чинат производите. Овој метод на транспозиција е многу пофункционален од другите. Ајде да опишеме некои од придобивките од стожерните табели:

1. Автоматизација. Со помош на стожерните табели, можете автоматски да ги сумирате податоците, како и произволно да ја менувате позицијата на колоните и колоните. За да го направите ова, не треба да вршите никакви дополнителни чекори.
2. Интерактивност. Корисникот може да ја промени структурата на информациите онолку пати колку што му е потребно за извршување на своите задачи. На пример, можете да го промените редоследот на колоните, како и да групирате податоци на произволен начин. Ова може да се направи онолку пати колку што му треба на корисникот. И тоа буквално трае помалку од една минута.
3. Лесно се форматираат податоците. Многу е лесно да се организира стожерна табела на начинот на кој човек сака. За да го направите ова, само направете неколку кликнувања со глувчето.
4. Добивање вредности. Огромниот број формули што се користат за креирање извештаи се наоѓаат во директната пристапност на лицето и лесно се интегрираат во стожерна табела. Тоа се податоци како што се собирање, добивање на аритметичка средина, одредување на бројот на ќелии, множење, наоѓање на најголемите и најмалите вредности во наведениот примерок.
5. Способност за креирање резиме на графикони. Ако PivotTables се пресметуваат повторно, нивните поврзани графикони автоматски се ажурираат. Можно е да креирате онолку графикони колку што ви треба. Сите тие можат да се сменат за одредена задача и нема да бидат меѓусебно поврзани.
6. Способност за филтрирање податоци.
7. Можно е да се изгради стожерна табела врз основа на повеќе од еден сет на изворни информации. Затоа, нивната функционалност ќе стане уште поголема.

Точно, кога користите стожерни табели, мора да се земат предвид следните ограничувања:

1. Не може сите информации да се користат за генерирање на стожерни табели. Пред да можат да се користат за оваа намена, клетките мора да се нормализираат. Со едноставни зборови, сфатете го правилно. Задолжителни барања: присуство на линија за заглавие, полнота на сите линии, еднаквост на формати на податоци.
2. Податоците се ажурираат полуавтоматски. За да добиете нови информации во стожерната табела, мора да кликнете на специјално копче.
3. Стожерните табели заземаат многу простор. Ова може да доведе до одредено нарушување на компјутерот. Исто така, датотеката ќе биде тешко да се испрати по е-пошта поради ова.

Исто така, по креирањето на стожерна табела, корисникот нема можност да додава нови информации.