Kā pārvērst rindas par kolonnām programmā Excel

Šajā rokasgrāmatā ir paskaidrots, kas jādara, lai programmā Excel rindas pārvērstu kolonnās. Izmantojot programmu Excel, tabulā bieži ir jāmaina rindas un kolonnas. Piemēram, gadās, ka cilvēks ir uztaisījis milzīgu galdu, un tad viņš saprata, ka to ir daudz vieglāk izlasīt, ja to apgriež. 

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

Īpašās ielīmēšanas funkcijas izmantošana

Pieņemsim, ka jums ir divas datu kopas. Otrais ir tas, ar ko vēlaties beigties. Pirmā ir tabula, kas ir jātransponē. Pirmajā versijā valstu nosaukumi ir norādīti dažādās kolonnās, un ir ļoti neērti lasīt un vēl jo vairāk salīdzināt valstu īpašības savā starpā. Tāpēc daudz labāk ir sakārtot tabulu tā, lai valstu nosaukumi parādās dažādās kolonnās.Kā pārvērst rindas par kolonnām programmā Excel

Lai apmainītu rindas un kolonnas, rīkojieties šādi:

  1. Izvēlieties sākotnējo tabulu. Ja vēlaties redzēt visu tabulu uzreiz (ja tā ir ļoti liela), ir jānospiež taustiņu kombinācija Ctrl + Home, un pēc tam – Ctrl + Shift + End.
  2. Pēc tam šūnas tiek kopētas. To var izdarīt, izmantojot konteksta izvēlni vai nospiežot taustiņu kombināciju Ctrl+C. Ieteicams nekavējoties pierast pie pēdējās iespējas, jo, apgūstot karstos taustiņus, jūs varat veikt daudzus uzdevumus burtiski vienā sekundē.
  3. Atlasiet pirmo šūnu mērķa diapazonā. При этом он должен находиться за пределами таблицы. Также необходимо подобрать ячейку таким образом, чтобы таблица после транспонирования не переспонирования не переспонирования не перессекалас. Piemēram, ja sākotnējā tabulā ir 4 kolonnas un 10 rindas, tad pēc visu šo darbību veikšanas tā izkliedēs 10 šūnas uz leju un 4 šūnas uz sāniem. Tāpēc šajā diapazonā (skaitot no mērķa šūnas) nevajadzētu būt nekā.
  4. Mērķa šūnā ar peles labo pogu noklikšķiniet un atlasiet “Īpašā ielīmēšana” un pēc tam atzīmējiet izvēles rūtiņu blakus uzrakstam “Transponēt”.Kā pārvērst rindas par kolonnām programmā Excel

Svarīgi: ja avota tabulā ir formulas, ir svarīgi nodrošināt, lai katrā šūnā tiktu izmantotas absolūtās atsauces. Tas jādara, lai visas saites tiktu automātiski atjauninātas.

Огромное преимущество опции «Специальная вставка» и заключается в возможности трансконспонировать таблицу. И при этом полностью сохраняется форматирование, что также позволяет сэкономить кучу времени.

Несмотря на эти явные плюсы, есть и ряд серьезных недостатков, которые мешают этому методу назыальвырмя:

  1. Ir slikti to izmantot, lai transponētu pilnvērtīgas tabulas, kas nav samazinātas līdz banālam vērtību diapazonam. Šādā gadījumā funkcija “Transponēt” tiks atspējota. Lai novērstu šo problēmu, tabula ir jāpārvērš diapazonā.
  2. Šī metode ir labi piemērota vienreizējai transponēšanai, jo tā nesaista jauno tabulu ar sākotnējiem datiem. Vienkāršiem vārdiem sakot, mainot vienu no tabulām, informācija netiks automātiski atjaunināta otrajā. Tāpēc transponēšana būs jāatkārto. 

Kā apmainīt rindas un kolonnas un saistīt tās ar galveno tabulu?

Tātad, ko var darīt, lai opcija “Īpašā ielīmēšana” varētu saistīt tabulu ar galvenajiem datiem un to, kas iegūta transponēšanas rezultātā? Galu galā visi vēlētos, lai dati tiktu automātiski atjaunināti.

  1. Скопировать таблицу, для которой требуется транспонирование.
  2. Tukšā tabulas apgabalā atlasiet šūnu, kurā nav datu.
  3. Palaidiet izvēlni Īpašā ielīmēšana, līdzīgi kā iepriekšējā piemērā. Pēc tam jums jānoklikšķina uz pogas "Ievietot saiti", kas atrodas apakšējā kreisajā stūrī.Kā pārvērst rindas par kolonnām programmā Excel
  4. Rezultāts būs šāds.Kā pārvērst rindas par kolonnām programmā Excel
  5. Выбрать новую таблицу и запустить окно «Найти и заменить», путем нажатия комбинации клавиш Ctrl+H.
  6. Заменить все знаки ввода формулы (=) на ххх (или любую другую комбинацию знаков, котонарой нет в ойцин).Kā pārvērst rindas par kolonnām programmā Excel
  7. Rezultātā izrādīsies kaut kas šausmīgs, taču tas ir obligāts starpnosacījums vēlamā rezultāta sasniegšanai. Tad viss izskatīsies skaisti.Kā pārvērst rindas par kolonnām programmā Excel
  8. Kopējiet iegūto tabulu un pēc tam izmantojiet īpašo ielīmēšanu, lai to transponētu.

Pēc tam jums vienkārši ir atkārtoti jāatver dialoglodziņš “Atrast un aizstāt” un izmantot to, lai šūnās mainītu “xxx” uz “=”, lai visas šūnas būtu saistītas ar sākotnējo informāciju.Kā pārvērst rindas par kolonnām programmā ExcelProtams, tas ir sarežģītāk un ilgāk, taču šī metode ļauj apiet saites uz sākotnējo tabulu trūkumu. Taču šai pieejai ir arī savs trūkums. Tas izpaužas kā nepieciešamība patstāvīgi atsākt formatēšanu.

Formulu pielietojums

Ir divas funkcijas, kas ļauj elastīgi mainīt rindas un kolonnas: ТРАНСП un ДВССЫЛ. Здесь также есть возможность сохранить связь с первоначальной таблицей, но механика работы несколько иная.

funkcija TRANSP

Faktiski šī formula tieši transponē izklājlapu. Sintakse ir šāda:

=TRANSP(masīvs)

Tagad mēs mēģināsim to piemērot tabulai, kurā ir informācija par atsevišķu valstu iedzīvotājiem.Kā pārvērst rindas par kolonnām programmā Excel

  1. Saskaitiet tabulas kolonnu un rindu skaitu un atrodiet tukšu vietu lapā, kurai ir vienādi izmēri.
  2. Sāciet rediģēšanas režīmu, nospiežot taustiņu F2.
  3. Uzrakstiet funkciju TRANSPS ar datu diapazonu iekavās. Lietojot šo funkciju, ir svarīgi vienmēr izmantot absolūtās atsauces.Kā pārvērst rindas par kolonnām programmā Excel
  4. Nospiediet taustiņu kombināciju Ctrl+Shift+Enter. Ir svarīgi nospiest tieši taustiņu kombināciju, pretējā gadījumā formula atteiksies darboties.

Tas ir viss, tagad rezultāts izskatās šādi!Kā pārvērst rindas par kolonnām programmā Excel

Šīs formulas priekšrocība ir tāda pati kā iepriekšējās metodes (izmantojot funkciju "atrast un aizstāt") priekšrocība ir tāda, ka, mainoties pamatinformācijai, transponētā tabula tiks automātiski atjaunināta.

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

  1. Formatēšana, tāpat kā meklēšanas un aizstāšanas metodes gadījumā, netiks saglabāta.
  2. Sākotnējā tabulā ir jābūt dažiem datiem, pretējā gadījumā dažās šūnās būs nulles.
  3. Излишняя зависимость от источника данных. То есть, этот метод имеет противоположный по недостаток – невозможность изменять транспонированную таблиц Если попытаться это сделать, программа скажет, что невозможно редактировать часть массива.

Arī šo funkciju ne vienmēr var elastīgi pielietot dažādās situācijās, tāpēc par to ir jāzina, bet jāizmanto efektīvāki paņēmieni.

Izmantojot NETIEŠO formulu

Šīs metodes mehānika ir ļoti līdzīga formulas izmantošanai TRANSPS, но при этом его использование решает проблему невозможности редактировать транспонированную транспонированную таблицу беиз потинцу потойсой.

Но одной формулы NETIEŠAIS nepietiek: jums joprojām ir jāizmanto funkcija ADDRESS. Šajā piemērā nebūs lielas tabulas, lai nepārslogotu jūs ar daudz nevajadzīgas informācijas.

Tātad, pieņemsim, ka jums ir šāda tabula, kas sastāv no 4 kolonnām un 5 rindām.Kā pārvērst rindas par kolonnām programmā Excel

Ir jāveic šādas darbības:

  1. Ievadiet šo formulu: =NETIEŠA(ADRESE(SLENA(A1),RINDA(A1))) jaunās tabulas augšējā kreisajā šūnā (mūsu piemērā tas ir A7) un nospiediet taustiņu Enter. Ja informācija nesākas pirmajā rindā vai pirmajā kolonnā, jums būs jāizmanto sarežģītāka formula: =ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1)-СТОЛБЕЦ($A$1)+СТРОКА($A$1);СТРОКА(A1)-СТРОКА($A$1)+СТОЛБЕЦ($A$1))). Šajā formulā A1 nozīmē tabulas augšējo šūnu, uz kuras pamata tiks izveidota transponētā šūna.
  2. Paplašiniet formulu uz visu apgabalu, kurā tiks ievietota jaunā tabulas versija. Lai to izdarītu, velciet marķieri pirmās šūnas apakšējā labajā stūrī uz nākamās tabulas pretējo galu.Kā pārvērst rindas par kolonnām programmā Excel
  3. Visi! Tabula izrādījās veiksmīgi transponēta, un jūs joprojām varat to rediģēt. Protams, viņas izskats atstāj daudz ko vēlēties, taču to salabot nav grūti.Kā pārvērst rindas par kolonnām programmā Excel Lai atjaunotu pareizo formatējumu, jums ir jākopē mūsu transponētā tabula (tas ir, sākotnējā), pēc tam atlasiet jaunizveidoto tabulu. Pēc tam ar peles labo pogu noklikšķiniet uz atlasītā diapazona un pēc tam ielīmēšanas opcijās noklikšķiniet uz "Formatēšana".Kā pārvērst rindas par kolonnām programmā Excel

Tātad, izmantojot funkciju NETIEŠAIS ļauj rediģēt absolūti jebkuru vērtību galīgajā tabulā, un dati vienmēr tiks atjaunināti, tiklīdz avota tabulā tiks veiktas izmaiņas.

Protams, šī metode ir grūtāka, bet, ja jūs to strādājat praksē, tad viss nav tik biedējoši.

Этот метод один из самых лучших, потому что несмотря на то, что не сохраняется сохраняется фолгатин форматирование в новорматирование в новорматирование

Kā darbojas NETIEŠĀ un ADRESE formulu kombinācija?

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

funkcija NETIEŠAIS mūsu formulā tiek izmantots, lai izveidotu netiešu šūnu atsauci. Piemēram, ja šūnā A8 ir jānorāda tāda pati vērtība kā B1, varat rakstīt formulu

=NETIEŠAIS(“B1”)Kā pārvērst rindas par kolonnām programmā Excel

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

Ārpus funkcijas ADDRESS izmanto arī formulā KOLONNA и LINE. Pirmais atgriež šūnas adresi, pamatojoties uz jau zināmajiem rindu un kolonnu numuriem. Šeit ir svarīgi ievērot pareizo secību. Vispirms tiek norādīta rinda un pēc tam tikai kolonna. Piemēram, funkcija ADRESE(4;2) atgriezīs adresi $B$2.

Следующая используемая выше функция – это KOLONNA. Šeit tas ir nepieciešams, lai formula saņemtu kolonnas numuru no konkrētas atsauces. Piemēram, ja izmantojat parametru B2 šīs funkcijas iekavās, tas atgriezīs skaitli 2, jo otrā kolonna ir kolonna B.

Acīmredzot funkcija ROW darbojas tāpat, tā tikai atgriež rindas numuru. 

Un tagad mēs izmantosim nevis abstraktus piemērus, bet ļoti specifisku formulu, kas tika izmantota iepriekš:

=NETIEŠA(ADRESE(SLENA(A1),RINDA(A1)))

Šeit jūs to varat redzēt vispirms funkcijā ADDRESS ir norādīta kolonna un tikai pēc tam rinda. Un tieši šeit slēpjas šīs formulas darba spēju noslēpums. Mēs atceramies, ka šī funkcija darbojas spoguļa veidā, un pirmais arguments tajā ir rindas numurs. Un izrādās, ka, ierakstot tur kādas kolonnas adresi, tā pārvēršas par rindas numuru un otrādi.

То есть, если подытожить:

  1. Mēs iegūstam kolonnu un rindu numurus, izmantojot atbilstošās funkcijas.
  2. Izmantojot funkciju ADDRESS rindas kļūst par kolonnām un otrādi.
  3. funkcija NETIEŠAIS palīdz parādīt šūnā atspoguļotos datus.

Tik vienkārši viss izrādās!

Makro izmantošana transponēšanai

Makro ir maza programma. To var izmantot, lai automatizētu procesu. Ir svarīgi ņemt vērā, ka makro ir daži ierobežojumi. Maksimālā Transponēšanas metode ļauj strādāt ar 65536 elementiem. Ja šis ierobežojums tiek pārsniegts, tiks zaudēti dati.

Во всем остальном, это эффективный метод автоматизации, который сможет значительно облегчить жизн.

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

Sub TransposeColumnsRows()

    Dim SourceRange As Range

    Dim DestRange kā diapazons

    Set SourceRange = Application.InputBox(Prompt:=»Lūdzu, atlasiet transponējamo diapazonu», Title:=»Pārvietot rindas uz kolonnām», Tips:=8)

    Set DestRange = Application.InputBox(Prompt:=»Atlasiet mērķa diapazona augšējo kreiso šūnu», Title:=»Pārvietot rindas uz kolonnām», Tips:=8)

    SourceRange.Copy

    DestRange. Select

    Selection.PasteSpecial Paste:=xlPasteAll, Darbība:=xlNav, SkipBlanks:=False, Transpose:=True

    Application.CutCopyMode = Nepatiesa

End Sub

Но если знаний в программировании особо нет, ничего страшного. Можно воспользоваться описанными выше способами. А потом учиться новому по мере освоения старого.

Atstāj atbildi