როგორ გადავიტანოთ რიგები სვეტებად Excel-ში

ეს სახელმძღვანელო განმარტავს, თუ რა უნდა გავაკეთოთ Excel-ში რიგების სვეტებად გადაქცევისთვის. Excel-ის გამოყენებისას ხშირად გიწევთ ცხრილის რიგების და სვეტების შეცვლა. მაგალითად, ხდება, რომ ადამიანმა უზარმაზარი მაგიდა გააკეთა და შემდეგ მიხვდა, რომ მისი წაკითხვა ბევრად უფრო ადვილია, თუ გადაატრიალებთ. 

Эта детальная инструкция расскажет о нескольких способах транспонирования Excel-таблицы, а также о часто встречаемых ошибках, со которыми може да столкнуться пользователь. ყველა შეიძლება გამოიყენოს ლუბოი ვერსიები Excel, როგორც ოчень старой, так и მხოლოდ ახალი.

Paste Special ფუნქციის გამოყენებით

ვთქვათ, თქვენ გაქვთ ორი მონაცემთა ნაკრები. მეორე არის ის, რითაც გინდა დასრულდეს. პირველი არის ცხრილი, რომელიც უნდა გადაიტანოს. პირველ ვერსიაში ქვეყნების სახელები წარმოდგენილია სხვადასხვა სვეტში და ძალიან მოუხერხებელია წაკითხვა და მით უმეტეს, სახელმწიფოთა მახასიათებლების ერთმანეთთან შედარება. ამიტომ, ბევრად უკეთესია ცხრილის ორგანიზება ისე, რომ ქვეყნების სახელები გამოჩნდეს სხვადასხვა სვეტებში.

რიგებისა და სვეტების შესაცვლელად, გააკეთეთ შემდეგი:

1. აირჩიეთ ორიგინალური ცხრილი. თუ გსურთ მთლიანი ცხრილის ერთდროულად ნახვა (თუ ის ძალიან დიდია), უნდა დააჭიროთ კლავიშთა კომბინაციას Ctrl + Home და ამის შემდეგ – Ctrl + Shift + End.
2. შემდეგ ხდება უჯრედების კოპირება. ეს შეიძლება გაკეთდეს კონტექსტური მენიუს მეშვეობით ან Ctrl+C კლავიშების კომბინაციის დაჭერით. მიზანშეწონილია, რომ დაუყოვნებლივ მიეჩვიოთ ბოლო ვარიანტს, რადგან თუ ისწავლით ცხელი კლავიშებს, შეგიძლიათ შეასრულოთ მრავალი დავალება სიტყვასიტყვით ერთ წამში.
3. აირჩიეთ პირველი უჯრედი სამიზნე დიაპაზონში. При этом он должен находиться за пределами таблицы. Также необходимо подоброть ячейку таким образом, чтобы таблица после транспонирования не пересекалась со другими данными. მაგალითად, თუ საწყის ცხრილს აქვს 4 სვეტი და 10 სტრიქონი, მაშინ ყველა ამ ოპერაციის შესრულების შემდეგ ის გაავრცელებს 10 უჯრედს ქვემოთ და 4 უჯრედს გვერდზე. ამიტომ, ამ დიაპაზონში (სამიზნე უჯრედიდან დათვლა) არაფერი არ უნდა იყოს.
4. სამიზნე უჯრედზე, თქვენ უნდა დააწკაპუნოთ მაუსის მარჯვენა ღილაკით და აირჩიეთ "Paste Special" და შემდეგ მონიშნეთ ველი წარწერის გვერდით "Transpose".

მნიშვნელოვანია: თუ წყაროს ცხრილში არის ფორმულები, მნიშვნელოვანია იმის უზრუნველყოფა, რომ აბსოლუტური მითითებები გამოიყენება თითოეულ უჯრედში. ეს უნდა გაკეთდეს ისე, რომ ყველა ბმული ავტომატურად განახლდეს.

Огромное преимущество ფუნქციები «სპეციალისტური შესყიდვა» და დაბლოკვის შესაძლებლობათა ტრანსპონირებით თაблицу всего лишь за несколько секунда. И при этом полностью сохраняется форматирование, что также позволяет сэкономить кучу времени.

არასასურველი პლიუს, ეს არის და სერიული ცალსახა, რაც არღვევს ამ მეთოდს უნივერსალური:

1. ცუდია მისი გამოყენება სრულფასოვანი ცხრილების გადასატანად, რომლებიც არ არის დაყვანილი მნიშვნელობების ბანალურ დიაპაზონში. ამ შემთხვევაში, "ტრანსპოზის" ფუნქცია გამორთული იქნება. ამ პრობლემის გადასაჭრელად, თქვენ უნდა გადაიყვანოთ ცხრილი დიაპაზონში.
2. ეს მეთოდი კარგად შეეფერება ერთჯერად ტრანსპოზს, რადგან ის არ აკავშირებს ახალ ცხრილს თავდაპირველ მონაცემებთან. მარტივი სიტყვებით რომ ვთქვათ, როდესაც თქვენ შეცვლით ერთ-ერთ ცხრილს, ინფორმაცია ავტომატურად არ განახლდება მეორეზე. ამიტომ ტრანსპოზიცია უნდა განმეორდეს. 

როგორ შევცვალოთ რიგები და სვეტები და დავაკავშიროთ ისინი მთავარ ცხრილთან?

მაშ, რა შეიძლება გაკეთდეს იმისათვის, რომ "Paste Special" ოფციამ შეძლოს ცხრილის დაკავშირება ძირითად მონაცემებთან და ტრანსპოზიციის შედეგად წარმოქმნილ მონაცემებთან? ყოველივე ამის შემდეგ, ყველას სურს, რომ მონაცემები ავტომატურად განახლდეს.

1. Скопировать таблицу, для которой требуется транспонирование.
2. შეარჩიეთ უჯრედი მონაცემთა გარეშე ცხრილის ცარიელ ადგილას.
3. გაუშვით Paste Special მენიუ, წინა მაგალითის მსგავსად. ამის შემდეგ, თქვენ უნდა დააჭიროთ ღილაკს "ჩასმა ბმული", რომელიც შეგიძლიათ იხილოთ ქვედა მარცხენა მხარეს.
4. შედეგი იქნება შემდეგი.
5. კლავიშ Ctrl + H.
6. Заменить все знаки ввода формулы (=) ххх (или любую другую комбинацию знаков, которой нет в оригинальной таблице).
7. შედეგად, რაღაც საშინელება გამოვა, მაგრამ ეს არის სავალდებულო შუალედური პირობა სასურველი შედეგის მისაღწევად. მაშინ ყველაფერი ლამაზად გამოიყურება.
8. დააკოპირეთ მიღებული ცხრილი და შემდეგ გამოიყენეთ Paste Special გადასატანად.

ამის შემდეგ, თქვენ უბრალოდ ხელახლა უნდა გახსნათ დიალოგი „იპოვეთ და ჩაანაცვლეთ“ და გამოიყენეთ იგი უჯრედებში „xxx“-ზე „=“-ზე შესაცვლელად, რათა ყველა უჯრედი იყოს ასოცირებული თავდაპირველ ინფორმაციას.რა თქმა უნდა, ეს უფრო რთული და გრძელია, მაგრამ ეს მეთოდი საშუალებას გაძლევთ თავიდან აიცილოთ ორიგინალური ცხრილის ბმულის ნაკლებობა. მაგრამ ამ მიდგომას ასევე აქვს ნაკლი. ეს გამოიხატება ფორმატირების დამოუკიდებლად განახლების აუცილებლობაში.

ფორმულების გამოყენება

არსებობს ორი ფუნქცია, რომელიც საშუალებას გაძლევთ მოქნილად შეცვალოთ რიგები და სვეტები: ТРАНСП и ДВССЫЛ. Здесь также есть возможность сохранить связь со первоначальной таблицей, но механика работы несколько иная.

ფუნქცია TRANSP

სინამდვილეში, ეს ფორმულა პირდაპირ გადააქვს ცხრილს. სინტაქსი შემდეგია:

=TRANSP(მასივი)

ახლა ჩვენ შევეცდებით გამოვიყენოთ ის ცხრილში, რომელიც შეიცავს ინფორმაციას ცალკეული სახელმწიფოების მოსახლეობის შესახებ.

1. დათვალეთ ცხრილის სვეტების და სტრიქონების რაოდენობა და იპოვეთ ცარიელი ადგილი ფურცელზე, რომელსაც აქვს იგივე ზომები.
2. დაიწყეთ რედაქტირების რეჟიმი F2 ღილაკის დაჭერით.
3. დაწერეთ ფუნქცია ტრანსპ მონაცემთა დიაპაზონით ფრჩხილებში. ამ ფუნქციის გამოყენებისას მნიშვნელოვანია ყოველთვის გამოიყენოთ აბსოლუტური მითითებები.
4. დააჭირეთ კლავიშთა კომბინაციას Ctrl+Shift+Enter. მნიშვნელოვანია ზუსტად დააჭიროთ კლავიშთა კომბინაციას, წინააღმდეგ შემთხვევაში ფორმულა უარს იტყვის მუშაობაზე.

ესე იგი, ახლა შედეგი ასე გამოიყურება!

ამ ფორმულის უპირატესობა იგივეა, რაც წინა მეთოდის („ძებნა და ჩანაცვლება“ ფუნქციის გამოყენებით) არის ის, რომ როდესაც ძირითადი ინფორმაცია იცვლება, ტრანსპონირებული ცხრილი ავტომატურად განახლდება.

მაგრამ ჯერ კიდევ არ არის დასახელებული:

1. ფორმატირება, როგორც Find and Replace მეთოდის შემთხვევაში, არ შეინახება.
2. თავდაპირველი ცხრილი უნდა შეიცავდეს გარკვეულ მონაცემებს, წინააღმდეგ შემთხვევაში ზოგიერთ უჯრედში იქნება ნულები.
3. Излишняя зависимость от источника данных. ეს არის, ეს მეთოდი არ არის შეუძლებელი – შეუძლებელია შეცვალოს ტრანსპონირება. Если попытаться это сделать, программа скажет, что შეუძლებელია редактировать часть массива.

ასევე, ეს ფუნქცია ყოველთვის არ შეიძლება გამოყენებულ იქნას მოქნილად სხვადასხვა სიტუაციებში, ასე რომ თქვენ უნდა იცოდეთ ამის შესახებ, მაგრამ გამოიყენოთ უფრო ეფექტური ტექნიკა.

INDIRECT ფორმულის გამოყენება

ამ მეთოდის მექანიკა ძალიან ჰგავს ფორმულის გამოყენებას ტრანსპ, მაგრამ არ არის გამორიცხული, რომ ეს პრობლემა არ არის გამოქვეყნებული.

Но одной формулы არაპირდაპირი საკმარისი არ არის: თქვენ ჯერ კიდევ გჭირდებათ ფუნქციის გამოყენება ADDRESS. ამ მაგალითში დიდი ცხრილი არ იქნება, რათა არ გადატვირთოთ ბევრი ზედმეტი ინფორმაცია.

ასე რომ, ვთქვათ, თქვენ გაქვთ ასეთი ცხრილი, რომელიც შედგება 4 სვეტისა და 5 მწკრივისაგან.

შემდეგი მოქმედებები უნდა განხორციელდეს:

1. შეიყვანეთ ეს ფორმულა: =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1))) ახალი ცხრილის ზედა მარცხენა უჯრედში (ჩვენს მაგალითში ეს არის A7) და დააჭირეთ enter ღილაკს. თუ ინფორმაცია არ იწყება პირველ რიგში ან პირველ სვეტში, მაშინ მოგიწევთ უფრო რთული ფორმულის გამოყენება: =ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1)-СТОЛБЕЦ($A$1)+СТРОКА($A$1);СТРОКА(A1)-СТРОКА($A$1)+СТОЛБЕЦ($A$1))). ამ ფორმულაში A1 ნიშნავს ცხრილის ზედა უჯრედს, რომლის საფუძველზეც ჩამოყალიბდება ტრანსპოზიციური უჯრედი.
2. გააფართოვეთ ფორმულა მთელ ტერიტორიაზე, სადაც განთავსდება ცხრილის ახალი ვერსია. ამისათვის გადაიტანეთ მარკერი პირველი უჯრედის ქვედა მარჯვენა კუთხეში მომავალი ცხრილის საპირისპირო ბოლოს.
3. ყველა! ცხრილი წარმატებით იქნა გადატანილი და თქვენ კვლავ შეგიძლიათ მისი რედაქტირება. რა თქმა უნდა, მისი გარეგნობა სასურველს ტოვებს, მაგრამ მისი გამოსწორება არ არის რთული. სწორი ფორმატირების აღსადგენად, თქვენ უნდა დააკოპიროთ ცხრილი, რომელიც ჩვენ გადავიტანეთ (ანუ ორიგინალი), შემდეგ აირჩიეთ ახლად შექმნილი ცხრილი. შემდეგი, დააწკაპუნეთ მაუსის მარჯვენა ღილაკით არჩეულ დიაპაზონზე და შემდეგ დააწკაპუნეთ "ფორმატირება" პასტის ვარიანტებში.

ასე რომ, ფუნქციის გამოყენებით არაპირდაპირი საშუალებას გაძლევთ შეცვალოთ აბსოლუტურად ნებისმიერი მნიშვნელობა საბოლოო ცხრილში და მონაცემები ყოველთვის განახლდება, როგორც კი რაიმე ცვლილება მოხდება წყაროს ცხრილში.

რა თქმა უნდა, ეს მეთოდი უფრო რთულია, მაგრამ თუ პრაქტიკაში გამოიმუშავებთ, მაშინ ყველაფერი არც ისე საშინელია.

ეს მეთოდი ერთ-ერთი ყველაზე კარგია, თუ როგორ არ არის ის, რაც არ არის შენახული ფორმატირებით, რომელიც შეიძლება იყოს ძალიან კარგი.

როგორ მუშაობს INDIRECT და ADDRESS ფორმულების კომბინაცია?

ბოლოს, როგორც ამას განვმარტავდი, როგორ გამოიყენებდი საკუთრებას ამ ფორმულას, ვამ, შეძლებისდაგვარად, ვერ შეძლებისდაგვარად, ვერ შეძლებისდაგვარად, ვერ ასწავლის პრინციპს მუშაობის ამ მეთოდს.

ფუნქცია არაპირდაპირი ჩვენს ფორმულაში გამოიყენება უჯრედის არაპირდაპირი მითითების შესაქმნელად. მაგალითად, თუ A8 უჯრედში უნდა მიუთითოთ იგივე მნიშვნელობა, რაც B1-ში, მაშინ შეგიძლიათ დაწეროთ ფორმულა

=INDIRECT(„B1“)

Казалось бы, зачем это делать? Ведь можно просто написать ссылку на ячейку в другой ячейке. Но преимущество этой функции в том, что в ссылку, შეიძლება გადააქციოთ აბსოლютно любую строку, и даже ту, которая создается со использованием других ფორმул. Собственно, это мы и делаем в формуле.

ფუნქციის მიღმა ADDRESS ასევე გამოიყენება ფორმულაში სვეტი и LINE. პირველი აბრუნებს უჯრედის მისამართს უკვე ცნობილი მწკრივისა და სვეტის ნომრებზე დაყრდნობით. აქ მნიშვნელოვანია დაიცვას სწორი თანმიმდევრობა. ჯერ მითითებულია მწკრივი, შემდეგ კი მხოლოდ სვეტი. მაგალითად, ფუნქცია ADDRESS(4;2) დააბრუნებს მისამართს $B$2.

Следующая используемая выше функция – ეს სვეტი. აქ აუცილებელია ისე, რომ ფორმულამ მიიღოს სვეტის ნომერი კონკრეტული მითითებიდან. მაგალითად, თუ იყენებთ B2 პარამეტრს ამ ფუნქციის ფრჩხილებში, მაშინ ის დააბრუნებს რიცხვს 2, რადგან მეორე სვეტი არის სვეტი B.

ცხადია, ROW ფუნქცია მუშაობს იმავე გზით, ის უბრალოდ აბრუნებს რიგის ნომერს. 

და ახლა ჩვენ გამოვიყენებთ არა აბსტრაქტულ მაგალითებს, არამედ ძალიან სპეციფიკურ ფორმულას, რომელიც ზემოთ იყო გამოყენებული:

=INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))

აქ შეგიძლიათ ნახოთ ეს ჯერ ფუნქციაში ADDRESS მითითებულია სვეტი და მხოლოდ ამის შემდეგ ხაზი. და სწორედ აქ იმალება ამ ფორმულის შრომისუნარიანობის საიდუმლო. ჩვენ გვახსოვს, რომ ეს ფუნქცია მუშაობს სარკისებურად და მასში პირველი არგუმენტი არის ხაზის ნომერი. და გამოდის, რომ როდესაც ჩვენ ვწერთ სვეტის მისამართს იქ, ის იქცევა ხაზის ნომერში და პირიქით.

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

1. ჩვენ ვიღებთ სვეტებისა და მწკრივების ნომრებს შესაბამისი ფუნქციების გამოყენებით.
2. ფუნქციის გამოყენებით ADDRESS რიგები ხდება სვეტები და პირიქით.
3. ფუნქცია არაპირდაპირი ეხმარება საკანში ასახული მონაცემების ჩვენებას.

აი რა მარტივი გამოდის ყველაფერი!

მაკროს გამოყენება გადასატანად

მაკრო არის პატარა პროგრამა. მისი გამოყენება შესაძლებელია პროცესის ავტომატიზაციისთვის. მნიშვნელოვანია გავითვალისწინოთ, რომ მაკროს აქვს გარკვეული შეზღუდვები. მაქსიმალური ტრანსპოზის მეთოდი საშუალებას გაძლევთ იმუშაოთ 65536 ელემენტთან. თუ ეს ზღვარი გადააჭარბებს, ეს გამოიწვევს მონაცემთა დაკარგვას.

ყველა ცალმხრივი, ეს ეფექტური მეთოდი ავტომატიზირებულია, რაც შეიძლება მნიშვნელოვნად შემსუბუქდეს.

მაგალითად, შეგიძლიათ დაწეროთ такой კოდი, რომელიც შეგიძლიათ შეცვალოთ ადგილების სტრიქონები და კოლონიები.

Sub TransposeColumnsRows()

    Dim SourceRange როგორც დიაპაზონი

    Dim DestRange როგორც Range

    დააყენეთ SourceRange = Application.InputBox(Prompt:=»გთხოვთ, აირჩიოთ დიაპაზონი გადასატანად», სათაური:=»სტრიქონების გადატანა სვეტებად», ტიპი:=8)

    დააყენეთ DestRange = Application.InputBox(Prompt:=»აირჩიეთ დანიშნულების დიაპაზონის ზედა მარცხენა უჯრედი», სათაური:=»სტრიქონების გადატანა სვეტებზე», ტიპი:=8)

    SourceRange.ასლი

    DestRange.აირჩიეთ

    Selection.PasteSpecial Paste:=xlPasteAll, ოპერაცია:=xlNone, SkipBlanks:=False, Transpose:=True

    Application.CutCopyMode = False

ბოლო Sub

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