Transponējiet funkciju programmā Excel

Laiku pa laikam Excel lietotājam var rasties uzdevums horizontālas struktūras datu diapazonu pārvērst vertikālā. Šo procesu sauc par transponēšanu. Šis vārds lielākajai daļai cilvēku ir jauns, jo parastā darbā ar datoru šī darbība nav jāizmanto. Tomēr tiem, kam jāstrādā ar lielu datu apjomu, ir jāzina, kā to izdarīt. Šodien mēs sīkāk runāsim par to, kā to veikt, ar kādu funkciju, kā arī sīkāk aplūkosim dažas citas metodes.

Funkcija TRANSPOSE – transponē šūnu diapazonus programmā Excel

Viena no interesantākajām un funkcionālākajām tabulas transponēšanas metodēm programmā Excel ir funkcija TRANSPS. Ar tās palīdzību jūs varat pārvērst horizontālu datu diapazonu vertikālā vai veikt apgriezto darbību. Apskatīsim, kā ar to strādāt.

Funkciju sintakse

Šīs funkcijas sintakse ir neticami vienkārša: TRANSPOZE(masīvs). Tas nozīmē, ka mums ir jāizmanto tikai viens arguments, kas ir datu kopa, kas ir jāpārvērš horizontālā vai vertikālā skatījumā atkarībā no tā, kāds tas bija sākotnēji.

Šūnu vertikālo diapazonu (kolonnu) transponēšana

Pieņemsim, ka mums ir kolonna ar diapazonu B2:B6. Tajos var būt gan gatavas vērtības, gan formulas, kas atgriež rezultātus šajās šūnās. Mums tas nav tik svarīgi, transponēšana ir iespējama abos gadījumos. Pēc šīs funkcijas izmantošanas rindas garums būs tāds pats kā sākotnējā diapazona kolonnas garums.

Šīs formulas izmantošanas darbību secība ir šāda:

1. Izvēlieties līniju. Mūsu gadījumā tā garums ir piecas šūnas.
2. Pēc tam pārvietojiet kursoru uz formulas joslu un ievadiet tur formulu =TRANSP(B2:B6).
3. Nospiediet taustiņu kombināciju Ctrl + Shift + Enter.

Protams, jūsu gadījumā jums ir jānorāda diapazons, kas ir raksturīgs jūsu tabulai.

Horizontālo šūnu diapazonu (rindu) transponēšana

Principā darbības mehānisms ir gandrīz tāds pats kā iepriekšējā punktā. Pieņemsim, ka mums ir virkne ar sākuma un beigu koordinātām B10:F10. Tajā var būt arī gan tiešas vērtības, gan formulas. Izveidosim no tā kolonnu, kuras izmēri būs tādi paši kā oriģinālajai rindai. Darbību secība ir šāda:

1. Atlasiet šo kolonnu ar peli. Varat arī izmantot tastatūras taustiņus Ctrl un lejupvērsto bultiņu, noklikšķinot uz šīs kolonnas augšējās šūnas.
2. Pēc tam mēs rakstām formulu =TRANSP(B10:F10) formulas joslā.
3. Mēs to rakstām kā masīva formulu, izmantojot taustiņu kombināciju Ctrl + Shift + Enter.

Transponēšana ar Paste Special

Vēl viena iespējamā transponēšanas iespēja ir izmantot funkciju Paste Special. Šis vairs nav operators, ko izmantot formulās, bet tā ir arī viena no populārākajām metodēm kolonnu pārvēršanai rindās un otrādi.

Šī opcija atrodas cilnē Sākums. Lai tai piekļūtu, jums jāatrod grupa “Starpliktuve” un tur jāatrod poga “Ielīmēt”. Pēc tam atveriet izvēlni, kas atrodas zem šīs opcijas, un atlasiet vienumu “Transponēt”. Pirms tam jums ir jāatlasa diapazons, kuru vēlaties atlasīt. Rezultātā mēs iegūsim to pašu diapazonu, tikai tiek atspoguļots pretējais.

3 veidi, kā transponēt tabulu programmā Excel

Bet patiesībā ir daudz vairāk veidu, kā pārvērst kolonnas rindās un otrādi. Aprakstīsim 3 metodes, ar kurām mēs varam transponēt tabulu programmā Excel. Mēs apspriedām divus no tiem iepriekš, bet mēs sniegsim vēl dažus piemērus, lai jūs iegūtu labāku priekšstatu par uXNUMXbuXNUMXbkā veikt šo procedūru.

1. metode: Īpašā ielīmēšana

Šī metode ir visvienkāršākā. Pietiek nospiest pāris pogas, un lietotājs saņem transponētu tabulas versiju. Lielākai skaidrībai sniegsim nelielu piemēru. Pieņemsim, ka mums ir tabula, kurā ir informācija par to, cik daudz produktu pašlaik ir noliktavā, kā arī par to kopējo cenu. Pati tabula izskatās šādi.

Mēs redzam, ka mums ir galvene un kolonna ar produktu numuriem. Mūsu piemērā galvenē ir informācija par to, kāds produkts, cik tas maksā, cik tas ir noliktavā un kādas ir visu noliktavā esošo ar šo preci saistīto produktu kopējās izmaksas. Izmaksas iegūstam pēc formulas, kur izmaksas reizina ar daudzumu. Lai piemērs būtu vizuālāks, padarīsim galveni zaļu.

Mūsu uzdevums ir pārliecināties, ka tabulā esošā informācija atrodas horizontāli. Tas ir, lai kolonnas kļūtu par rindām. Darbību secība mūsu gadījumā būs šāda:

1. Atlasiet datu diapazonu, kas mums jāpagriež. Pēc tam mēs kopējam šos datus.
2. Novietojiet kursoru jebkurā lapas vietā. Pēc tam noklikšķiniet uz peles labās pogas un atveriet konteksta izvēlni.
3. Pēc tam noklikšķiniet uz pogas Īpašā ielīmēšana.

Pēc šo darbību veikšanas jums jānoklikšķina uz pogas “Transponēt”. Drīzāk atzīmējiet izvēles rūtiņu blakus šim vienumam. Mēs nemainām citus iestatījumus, un pēc tam noklikšķiniet uz pogas “OK”.

Pēc šo darbību veikšanas mums paliek viena un tā pati tabula, tikai tās rindas un kolonnas ir sakārtotas atšķirīgi. Ņemiet vērā arī to, ka šūnas, kurās ir tāda pati informācija, ir iezīmētas zaļā krāsā. Jautājums: kas notika ar formulām, kas bija sākotnējā diapazonā? Viņu atrašanās vieta ir mainījusies, bet viņi paši ir palikuši. Šūnu adreses vienkārši mainījās uz tām, kas tika izveidotas pēc transponēšanas.

Gandrīz tādas pašas darbības ir jāveic, lai transponētu vērtības, nevis formulas. Šajā gadījumā ir jāizmanto arī izvēlne Īpašā ielīmēšana, bet pirms tam atlasiet datu diapazonu, kurā ir vērtības. Mēs redzam, ka īpašā ielīmēšanas logu var izsaukt divos veidos: izmantojot īpašu lentes izvēlni vai konteksta izvēlni.

2. metode. TRANSP funkcija programmā Excel

Faktiski šī metode vairs netiek izmantota tik aktīvi, kā tas bija šīs izklājlapu programmas parādīšanās sākumā. Tas ir tāpēc, ka šī metode ir daudz sarežģītāka nekā izmantojot Paste Special. Tomēr tas tiek izmantots tabulu transponēšanas automatizēšanai.

Turklāt šī funkcija ir programmā Excel, tāpēc par to noteikti jāzina, lai gan tā vairs netiek gandrīz izmantota. Iepriekš mēs apsvērām procedūru, kā ar to strādāt. Tagad mēs šīs zināšanas papildināsim ar papildu piemēru.

1. Pirmkārt, mums ir jāizvēlas datu diapazons, kas tiks izmantots tabulas transponēšanai. Jums vienkārši jāizvēlas apgabals, gluži pretēji. Piemēram, šajā piemērā mums ir 4 kolonnas un 6 rindas. Tāpēc ir jāizvēlas apgabals ar pretējiem raksturlielumiem: 6 kolonnas un 4 rindas. Bildē tas ļoti labi redzams.
2. Pēc tam mēs nekavējoties sākam aizpildīt šo šūnu. Ir svarīgi nejauši nenoņemt atlasi. Tāpēc formula jānorāda tieši formulas joslā.
3. Pēc tam nospiediet taustiņu kombināciju Ctrl + Shift + Enter. Atcerieties, ka šī ir masīva formula, jo mēs vienlaikus strādājam ar lielu datu kopu, kas tiks pārsūtīta uz citu lielu šūnu kopu.

Pēc datu ievadīšanas mēs nospiežam taustiņu Enter, pēc kura mēs iegūstam šādu rezultātu.

Mēs redzam, ka formula netika pārnesta uz jauno tabulu. Tika zaudēts arī formatējums. Poeto

Tas viss būs jādara manuāli. Turklāt atcerieties, ka šī tabula ir saistīta ar sākotnējo. Tāpēc, tiklīdz kāda informācija tiek mainīta sākotnējā diapazonā, šīs korekcijas tiek automātiski veiktas transponētajā tabulā.

Tāpēc šī metode ir labi piemērota gadījumos, kad jāpārliecinās, ka transponētā tabula ir saistīta ar sākotnējo. Ja izmantosiet īpašu ieliktni, šī iespēja vairs nebūs.

kopsavilkuma tabula

Šī ir principiāli jauna metode, kas ļauj ne tikai transponēt tabulu, bet arī veikt milzīgu skaitu darbību. Tiesa, transponēšanas mehānisms būs nedaudz atšķirīgs salīdzinājumā ar iepriekšējām metodēm. Darbību secība ir šāda:

1. Izveidosim rakurstabulu. Lai to izdarītu, jums ir jāatlasa tabula, kas mums ir jātransponē. Pēc tam dodieties uz vienumu “Ievietot” un atrodiet tur “Rakurstabulu”. Parādīsies dialoglodziņš, piemēram, šajā ekrānuzņēmumā.
2. Šeit varat atkārtoti piešķirt diapazonu, no kura tas tiks izveidots, kā arī veikt vairākus citus iestatījumus. Mūs tagad galvenokārt interesē šarnīra tabulas vieta – uz jaunas lapas.
3. Pēc tam tiks automātiski izveidots rakurstabulas izkārtojums. Tajā ir jāatzīmē tie priekšmeti, kurus mēs izmantosim, un tad tie ir jāpārvieto uz pareizo vietu. Mūsu gadījumā vienums “Produkts” ir jāpārvieto uz “Sleju nosaukumi” un “Cena par gabalu” uz “Vērtības”.
4. Pēc tam beidzot tiks izveidota rakurstabula. Papildu bonuss ir automātisks galīgās vērtības aprēķins.
5. Varat mainīt arī citus iestatījumus. Piemēram, noņemiet atzīmi no vienuma “Cena par gabalu” un atzīmējiet vienumu “Kopējās izmaksas”. Rezultātā mums būs tabula, kurā būs informācija par to, cik produkti maksā. Šī transponēšanas metode ir daudz funkcionālāka nekā citas. Aprakstīsim dažas rakurstabulu priekšrocības:

1. Automatizācija. Ar rakurstabulu palīdzību var automātiski apkopot datus, kā arī patvaļīgi mainīt kolonnu un kolonnu novietojumu. Lai to izdarītu, jums nav jāveic nekādas papildu darbības.
2. Interaktivitāte. Lietotājs var mainīt informācijas struktūru tik reižu, cik nepieciešams savu uzdevumu veikšanai. Piemēram, varat mainīt kolonnu secību, kā arī grupēt datus patvaļīgā veidā. To var izdarīt tik reižu, cik lietotājam nepieciešams. Un tas burtiski aizņem mazāk nekā minūti.
3. Viegli formatēt datus. Ir ļoti vienkārši sakārtot šarnīra tabulu tā, kā cilvēks vēlas. Lai to izdarītu, vienkārši veiciet dažus peles klikšķus.
4. Vērtību iegūšana. Lielais skaits formulu, kas tiek izmantotas, lai izveidotu pārskatus, atrodas personas tiešā pieejamības zonā, un tās ir viegli integrēt rakurstabulā. Tie ir tādi dati kā summēšana, vidējā aritmētiskā iegūšana, šūnu skaita noteikšana, reizināšana, lielāko un mazāko vērtību atrašana norādītajā paraugā.
5. Spēja izveidot kopsavilkuma diagrammas. Ja rakurstabulas tiek pārrēķinātas, ar tām saistītās diagrammas tiek automātiski atjauninātas. Ir iespējams izveidot tik daudz diagrammu, cik nepieciešams. Tos visus var mainīt konkrētam uzdevumam, un tie nebūs savstarpēji saistīti.
6. Iespēja filtrēt datus.
7. Ir iespējams izveidot rakurstabulu, pamatojoties uz vairāk nekā vienu avota informācijas kopu. Tāpēc to funkcionalitāte kļūs vēl lielāka.

Tiesa, izmantojot rakurstabulas, jāņem vērā šādi ierobežojumi:

1. Ne visu informāciju var izmantot rakurstabulu ģenerēšanai. Pirms tos var izmantot šim nolūkam, šūnas ir jānormalizē. Vienkāršiem vārdiem sakot, saprotiet to pareizi. Obligātās prasības: galvenes rindas klātbūtne, visu rindu pilnība, datu formātu vienlīdzība.
2. Dati tiek atjaunināti pusautomātiski. Lai rakurstabulā iegūtu jaunu informāciju, jums jānoklikšķina uz īpašas pogas.
3. Rakurstabulas aizņem daudz vietas. Tas var izraisīt zināmus datora darbības traucējumus. Arī šī iemesla dēļ failu būs grūti nosūtīt pa e-pastu.

Tāpat pēc rakurstabulas izveides lietotājam nav iespējas pievienot jaunu informāciju.