۳ روش چرخاندن داده ها در اکسل (با تابع و با ماکرو)

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

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

تابع TRANSPOSE در اکسل

تابع TRANSPOSE در اکسل می تواند جهت محدوده داده ها را از افقی به عمودی و یا بالعکس تبدیل کند. در واقع ابعاد داده ها را تغییر می دهد و همراه با آن آدرس های داده نیز تغییر خواهد کرد. بنابراین اگر می خواهید داده های خود را بچرخانید، آن را با احتیاط انجام دهید.

این تابع فقط یک آرگومان دارد:

=TRANSPOSE(array)

array محدوده سلول ها برای چرخش است. تابع محدوده array را به این ترتیب می چرخاند: ابتدا ردیف اول آرایه اصلی به ستون اول آرایه جدید تبدیل می شود سپس ردیف دوم به ستون دوم تبدیل می شود و به همین صورت تا ردیف آخر ادامه پیدا می کند.

نکته: برای اینکه تابع TRANSPOSE کار کند باید با فشار دادن کلیدهای Ctrl + Shift + Enter آن را به یک فرمول آرایه ای تبدیل کنید.

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

۱- تعداد ستون ها و ردیف های جدول اصلی را بشمارید.

باید بدانید که جدول اولیه چند ستون و ردیف دارد. در مرحله بعدی به این اعداد نیاز خواهید داشت.

در این مثال، جدول اولیه ۴ ستون و ۵ ردیف دارد.

۲- تعدادی از سلول های خالی را به همان اندازه انتخاب کنید اما جهت را تغییر دهید.

تعداد سلول ها در جدول جدید به اندازه جدول اولیه خواهد بود اما جهت افقی به عمودی یا برعکس تغییر می کند. یک محدوده سلول خالی را انتخاب کنید به صورتی که تعداد ستون های آن برابر با تعداد ردیف های جدول اصلی و تعداد ردیف های آن به تعداد ستون های جدول اصلی باشد.

در اینجا، محدوده ای با ۵ ستون و ۴ ردیف انتخاب می کنیم:

۳- فرمول TRANSPOSE را وارد کنید.

با انتخاب محدوده سلول های خالی، فرمول Transpose را تایپ کنید:

=TRANSPOSE(A1:D5)

برای این کار:

مانند فرمول زیر، ابتدا علامت تساوی، نام تابع و پرانتز باز را تایپ می کنید:

=TRANSPOSE(

سپس محدوده اصلی و اولیه را با استفاده از ماوس انتخاب کنید یا آن را به صورت دستی تایپ کنید:

در نهایت، پرانتز بسته را تایپ کنید اما کلید Enter را نزنید! در این مرحله، فرمول Transpose باید شبیه شکل زیر باشد:

۴- فرمول TRANSPOSE را کامل کنید.

کلیدهای Ctrl + Shift + Enter را فشار دهید تا فرمول آرایه ای را به درستی کامل کنید.

چرا باید این کار را انجام دهید؟ چون فرمول به بیشتر از یک سلول اعمال می شود و این دقیقاً همان چیزی است که فرمول های آرایه برای آن در نظر گرفته شده اند.

وقتی Ctrl + Shift + Enter را فشار دهید، اکسل فرمول Transpose را در براکت ها{}محصور می کند. به هیچ وجه نباید براکت ها را به صورت دستی تایپ کنید چونکه فرمول کار نخواهد کرد.

تصویر زیر نشان می دهد که جدول اصلی با موفقیت چرخانده شده و ۴ ستون به ۴ ردیف تبدیل شده است:

چرخاندن داده ها برای سلول های خالی

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

اگر می خواهید تابع به جای سلول های صفر، سلول های خالی را برگرداند، تابع IF را در فرمول TRANSPOSE وارد کنید تا بررسی کند آیا سلول خالی است یا خیر. اگر سلول خالی باشد، IF یک رشته خالی را برمی گرداند (“”) در غیر اینصورت مقدار را وارد کند:

=TRANSPOSE(IF(A1:D5=””,””,A1:D5))

فرمول را وارد کنید و کلیدهای Ctrl + Shift + Enter را فشار دهید تا فرمول آرایه ای به درستی تکمیل شود. نتیجه مشابه زیر خواهد بود:

لیست همه دروس کاربردی اکسل ما

نکات استفاده از تابع TRANSPOSE در اکسل

ممکن است تابع TRANSPOSE کاربران بی تجربه را گیج کند. نکات زیر به شما کمک می کند تا از اشتباهات اجتناب کنید.

• ویرایش فرمول TRANSPOSE: تابع TRANSPOSE به عنوان یک تابع آرایه ای، امکان تغییر بخشی از آرایه بازگشتی را نمی دهد. برای ویرایش فرمول Transpose، کل محدوده ای که فرمول به آن اشاره می کند، انتخاب کرده و تغییر مورد نظر را اعمال کنید سپس Ctrl + Shift + Enter را فشار دهید تا فرمول به روز شده ذخیره شود.

• حذف فرمول TRANSPOSE: برای حذف فرمول Transpose از صفحه خود، کل محدوده در فرمول را انتخاب کنید و کلید Delete را فشار دهید.

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

اگر می خواهید ارتباط بین دو جدول را قطع کنید، فرمول را با مقادیر محاسبه شده جایگزین کنید. برای انجام این کار، تمام مقادیر بازگشتی از فرمول را انتخاب کرده، کلیدهای Ctrl + C را فشار دهید تا کپی شوند سپس راست کلیک کرده و از Paste Special گزینه Values ​​را انتخاب کنید.

سایر روش های چرخاندن داده ها در اکسل

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

• استفاده از ماکرو

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

Sub TransposeColumnsRows()

Dim SourceRange As Range

Dim DestRange As Range

Set SourceRange = Application.InputBox(Prompt:=”Please select the range to transpose”, Title:=”Transpose Rows to Columns”, Type:=8)

Set DestRange = Application.InputBox(Prompt:=”Select the upper left cell of the destination range”, Title:=”Transpose Rows to Columns”, Type:=8)

SourceRange.Copy

DestRange.Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

Application.CutCopyMode = False

End Sub

• استفاده از Paste Special

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

مراحل زیر را دنبال کنید:

• محدوده داده مورد نظر را انتخاب کنید.
• داده ها را کپی کنید. می توانید از میانبر صفحه کلید Ctrl + C استفاده کنید یا کلیک راست کرده و روی Copy کلیک کنید.
• روی سلولی که می خواهید داده ها را در آن انتقال دهید، راست کلیک کنید.
• گزینه Paste special را انتخاب کنید.
• روی Transpose کلیک کنید.

• اگر نمی خواهید از فرمول های آرایه ای در کاربرگ خود استفاده کنید، می توانید توابع INDIRECT و ADDRESS را با هم ترکیب کرده و یک محدوده را با فرمول غیر آرایه ای معمولی بچرخانید.

دانلود فیلم های آموزش صفر تا صد اکسل +جزوه