نحوه تبدیل ردیف به ستون در اکسل

این راهنما توضیح می دهد که برای تبدیل ردیف ها به ستون در اکسل چه باید کرد. هنگام استفاده از اکسل، اغلب باید سطرها و ستون‌ها را در جدول عوض کنید. به عنوان مثال، این اتفاق می افتد که شخصی یک جدول بزرگ درست کرده است، و سپس متوجه می شود که اگر آن را برگردانید، خواندن آن بسیار آسان تر است. 

Эта детальная инструкция расскажет о нескольких способах транспонирования Excel-taблицы, а также о часто встречаемых ошибках, с которыми може да столкнуться пользователь. همه‌ی آن‌ها می‌توانند از نسخه‌های اکسل استفاده کنند، چه به‌صورت قدیمی، تاک و فقط جدید.

با استفاده از قابلیت Paste Special

فرض کنید شما دو مجموعه داده دارید. دوم آن چیزی است که می خواهید به آن برسید. اولین مورد جدولی است که باید جابجا شود. در نسخه اول، نام کشورها در ستون های مختلف ارائه شده است و خواندن و حتی بیشتر از آن، مقایسه ویژگی های کشورها با یکدیگر بسیار ناخوشایند است. بنابراین، بسیار بهتر است که جدول را طوری سازماندهی کنید که نام کشورها در ستون های مختلف ظاهر شود.

برای جابجایی سطرها و ستون ها، موارد زیر را انجام دهید:

1. جدول اصلی را انتخاب کنید. اگر می خواهید کل جدول را یکجا ببینید (اگر خیلی بزرگ است)، باید کلید ترکیبی Ctrl + Home را فشار دهید و بعد از آن - Ctrl + Shift + End را فشار دهید.
2. سپس سلول ها کپی می شوند. این کار را می توان از طریق منوی زمینه یا با فشار دادن کلیدهای ترکیبی Ctrl+C انجام داد. توصیه می شود بلافاصله خود را به آخرین گزینه عادت دهید، زیرا اگر کلیدهای داغ را یاد بگیرید، می توانید بسیاری از کارها را به معنای واقعی کلمه در یک ثانیه انجام دهید.
3. اولین سلول در محدوده هدف را انتخاب کنید. При этом он должен находиться за пределами таблицы. Также необходимо изборть ячейку таким образом, чтобы таблица после транспонирования не пересекалась со другими данными. به عنوان مثال، اگر جدول اولیه دارای 4 ستون و 10 ردیف باشد، پس از انجام تمام این عملیات، 10 خانه را به پایین و 4 خانه را به طرفین پخش می کند. بنابراین، در این محدوده (شمارش از سلول هدف) نباید چیزی وجود داشته باشد.
4. در سلول مورد نظر، باید کلیک راست کرده و "Paste Special" را انتخاب کنید و سپس کادر کنار کتیبه "Transpose" را علامت بزنید.

مهم: اگر فرمول هایی در جدول منبع وجود دارد، مهم است که اطمینان حاصل شود که از مراجع مطلق در هر سلول استفاده می شود. این کار باید انجام شود تا همه لینک ها به طور خودکار به روز شوند.

Ogromnoe преимущество امکانات «Specially instatka» و zaklyuchaetsya in mogut transponirovatь таблицу всего лишь за несколько ثانیه. И при этом полностью сохраняется форматирование, что также позволяет сэкономить кучу времени.

ﺩﺭ ﺩﺭ ﺍﺯ ﺩﺭ ﺍﻳﻦ ﺩﺭ ﺩﺳﺘﻪ ﺩﺭ ﺍﺳﺖ ﺩﺭ ﺍﺳﺖ ﻭ و

1. استفاده از آن برای جابجایی جداول تمام عیار که به محدوده ای از مقادیر پیش پا افتاده کاهش نمی یابد، بد است. در این حالت، عملکرد "Transpose" غیرفعال می شود. برای حل این مشکل، باید جدول را به یک محدوده تبدیل کنید.
2. این روش برای انتقال یکباره مناسب است زیرا جدول جدید را با داده های اصلی مرتبط نمی کند. به عبارت ساده، هنگامی که یکی از جداول را تغییر می دهید، اطلاعات به طور خودکار در جدول دوم به روز نمی شوند. بنابراین، انتقال باید تکرار شود. 

چگونه می توان ردیف ها و ستون ها را تعویض کرد و آنها را به جدول اصلی پیوند داد؟

بنابراین، چه کاری می‌توان انجام داد تا گزینه Paste Special بتواند جدول را با داده‌های اصلی و داده‌های حاصل از انتقال پیوند دهد؟ پس از همه، همه دوست دارند داده ها به طور خودکار به روز شوند.

1. Скопировать таблицу, для которой требуется транспонирование.
2. سلولی بدون داده را در قسمت خالی جدول انتخاب کنید.
3. منوی Paste Special را مانند مثال قبلی اجرا کنید. پس از آن، باید روی دکمه "درج پیوند" کلیک کنید، که در پایین سمت چپ یافت می شود.
4. نتیجه به شرح زیر خواهد بود.
5. انتخاب کنید جدید تابلیцу و запустить okno «Найти و заменить» با ترکیب ترکیبی Ctrl + H.
6. فرمول‌های همه نشانه‌های ввода (=) در هخ (یا любую другую комбинацию знаков, которой нет در оригинальной таблице).
7. در نتیجه، چیز وحشتناکی رخ خواهد داد، اما این یک شرط واجب واجب برای دستیابی به نتیجه مطلوب است. سپس همه چیز زیبا به نظر می رسد.
8. جدول به دست آمده را کپی کنید و سپس از Paste Special برای جابجایی آن استفاده کنید.

پس از آن، فقط باید کادر گفتگوی «یافتن و جایگزینی» را دوباره باز کنید و از آن برای تغییر «xxx» در سلول‌ها به «=» استفاده کنید تا همه سلول‌ها با اطلاعات اصلی مرتبط شوند.البته، این پیچیده تر و طولانی تر است، اما این روش به شما امکان می دهد از عدم وجود پیوند به جدول اصلی جلوگیری کنید. اما این رویکرد یک اشکال نیز دارد. این در نیاز به از سرگیری مستقل قالب بندی بیان شده است.

کاربرد فرمول ها

دو تابع وجود دارد که به شما امکان می دهد ردیف ها و ستون ها را به طور انعطاف پذیر تغییر دهید: ТРАНСП и ДВССЫЛ. Здесь также есть возможность сохранить связь с первоначальной таблицей, но механика работы несколько иная.

تابع TRANSP

در واقع، این فرمول مستقیماً صفحه گسترده را جابجا می کند. نحو به شرح زیر است:

=TRANSP(آرایه)

اکنون سعی خواهیم کرد آن را در جدولی اعمال کنیم که حاوی اطلاعاتی در مورد جمعیت ایالت های جداگانه است.

1. تعداد ستون ها و ردیف های جدول را بشمارید و یک فضای خالی در برگه پیدا کنید که ابعاد یکسانی دارد.
2. با فشار دادن کلید F2 حالت ویرایش را شروع کنید.
3. یک تابع بنویسید TRANSP با محدوده داده در پرانتز هنگام استفاده از این تابع مهم است که همیشه از ارجاعات مطلق استفاده کنید.
4. کلیدهای ترکیبی Ctrl+Shift+Enter را فشار دهید. مهم است که دقیقاً ترکیب کلید را فشار دهید، در غیر این صورت فرمول از کار کردن خودداری می کند.

همین، حالا نتیجه به نظر می رسد!

مزیت این فرمول مانند روش قبلی (با استفاده از تابع "یافتن و جایگزینی") این است که وقتی اطلاعات زیربنایی تغییر می کند، جدول انتقال داده شده به طور خودکار به روز می شود.

اما نارسا در این مورد نیست:

1. قالب بندی، مانند روش Find and Replace، ذخیره نمی شود.
2. جدول اصلی باید حاوی داده هایی باشد، در غیر این صورت در برخی سلول ها صفر وجود خواهد داشت.
3. Излишняя зависимость от источника данных. این روش ناممکن است - غیر ممکن است. Если попытаться это сделать, программа скажет, что نمی تواند تنظیم شود часть массива.

همچنین، این تابع را نمی توان همیشه به صورت انعطاف پذیر در موقعیت های مختلف اعمال کرد، بنابراین باید در مورد آن بدانید، اما از تکنیک های کارآمدتر استفاده کنید.

با استفاده از فرمول INDIRECT

مکانیک این روش بسیار شبیه به استفاده از فرمول است TRANSP, اما در این مورد است که مشکل را حل نمی کند.

Но одной формулы غیر مستقیم کافی نیست: هنوز باید از عملکرد استفاده کنید 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. همه! مشخص شد که جدول با موفقیت جابجا شده است، و شما همچنان می توانید آن را ویرایش کنید. البته ظاهر او چیزهای زیادی برای دلخواه باقی می گذارد، اما رفع آن دشوار نیست. برای بازیابی قالب بندی صحیح، باید جدولی را که جابجا کردیم (یعنی اصلی) کپی کنید، سپس جدول جدید ایجاد شده را انتخاب کنید. در مرحله بعد، روی محدوده انتخاب شده کلیک راست کرده و سپس روی "Formatting" در گزینه های چسباندن کلیک کنید.

بنابراین با استفاده از تابع غیر مستقیم به شما امکان می دهد مطلقاً هر مقداری را در جدول نهایی ویرایش کنید و به محض ایجاد هرگونه تغییر در جدول منبع، داده ها همیشه به روز می شوند.

البته، این روش دشوارتر است، اما اگر آن را در عمل انجام دهید، همه چیز چندان ترسناک نیست.

Этот روش один из самых лучших, потому что несмотря на то, что не حفظ форматирование во новосозданной таблице, ممکن است اینگونه باشد.

ترکیب فرمول های INDIRECT و ADDRESS چگونه کار می کند؟

پس از آن، چه در این مورد، چه در این مورد، چه در مورد این روش استفاده شود.

عملکرد غیر مستقیم در فرمول ما برای ایجاد یک مرجع سلول غیر مستقیم استفاده می شود. به عنوان مثال، اگر باید همان مقدار را در سلول A8 در B1 مشخص کنید، می توانید فرمول را بنویسید.

=INDIRECT("B1")

Казалось бы, зачем это делать? Ведь можно просто написать ссылку на ячейку در دیگری ячейке. Но преимущество этой функции в том، что в ссылку ممکن است ابсолютно любую stroku را برگرداند، و даже ту، которая создается со использованием других формул. Собственно, это мы и делаем во فرمуле.

فراتر از عملکرد ADDRESS همچنین در فرمول استفاده شده است ستون и LINE. اولی آدرس سلول را بر اساس اعداد سطر و ستون از قبل شناخته شده برمی گرداند. مهم است که دنباله صحیح را در اینجا دنبال کنید. ابتدا سطر مشخص می شود و سپس فقط ستون. به عنوان مثال، تابع ADDRESS(4;2) آدرس $B$2 را برمی گرداند.

Следующая используемая выше функция – это ستون. در اینجا لازم است تا فرمول شماره ستون را از یک مرجع خاص دریافت کند. به عنوان مثال، اگر از پارامتر B2 در براکت های این تابع استفاده کنید، آنگاه عدد 2 را برمی گرداند، زیرا ستون دوم ستون B است.

بدیهی است که تابع ROW به همین صورت عمل می کند، فقط شماره ردیف را برمی گرداند. 

و اکنون از نمونه های انتزاعی استفاده نمی کنیم، بلکه از یک فرمول بسیار خاص که در بالا استفاده شده است استفاده خواهیم کرد:

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

در اینجا می توانید آن را ابتدا در تابع مشاهده کنید ADDRESS ستون مشخص شده است و تنها پس از آن خط. و اینجاست که راز ظرفیت کاری این فرمول پنهان می شود. به یاد داریم که این تابع به صورت آینه ای کار می کند و اولین آرگومان موجود در آن شماره خط است. و معلوم می شود که وقتی آدرس یک ستون را در آنجا می نویسیم به شماره خط تبدیل می شود و بالعکس.

تو есть، если подытожить:

1. با استفاده از توابع مربوطه، اعداد ستون و ردیف را بدست می آوریم.
2. با استفاده از تابع ADDRESS سطرها تبدیل به ستون می شوند و بالعکس.
3. عملکرد غیر مستقیم به نمایش داده های آینه شده در سلول کمک می کند.

به همین سادگی همه چیز معلوم می شود!

استفاده از ماکرو برای جابجایی

ماکرو یک برنامه کوچک است. می توان از آن برای خودکارسازی فرآیند استفاده کرد. توجه به این نکته مهم است که ماکرو دارای محدودیت هایی است. روش حداکثر Transpose به شما امکان می دهد با 65536 عنصر کار کنید. اگر از این حد تجاوز شود، باعث از دست رفتن اطلاعات می شود.

در همه ostalьnom, это эffektivnый روش خودکار خودکاری، که به شما امکان می دهد به طور کامل راحت شوید.

به عنوان مثال، می توانید تاکوی کد را بنویسید.

Sub TransposeColumnsRows()

    Dim SourceRange به عنوان محدوده

    Dim DestRange به عنوان Range

    SourceRange = Application.InputBox را تنظیم کنید(Prompt:=»لطفاً محدوده ای را برای جابجایی انتخاب کنید، عنوان:=»انتقال ردیف ها به ستون، نوع:=8)

    DestRange = Application.InputBox را تنظیم کنید(Prompt:=»سلول سمت چپ بالای محدوده مقصد را انتخاب کنید، عنوان:=»انتقال سطرها به ستون، نوع:=8)

    SourceRange.Copy

    DestRange.انتخاب کنید

    Selection.PasteSpecial Paste:=xlPasteAll، عملیات:=xlNone، SkipBlanks:=False، Transpose:=True

    Application.CutCopyMode = نادرست

پایان دادن به زیر

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