تبدیل جدول افقی به عمودی و بالعکس در اکسل با تابع TRANSPOSE

خروجی خام بسیاری از نرم افزارهای حقوق و دستمزد، فیش هر کارمند را در یک سطر افقی می دهد؛ نام و هر قلم حقوقی (حقوق پایه، حق مسکن، اضافه کار، کارانه، کسورات و …) در یک ستون جداگانه. برای تهیه فیش چاپی تک برگی یا وارد کردن همین اطلاعات در قالب یک فرم عمودی، باید این سطر افقی به یک لیست عمودی تبدیل شود؛ و گاهی برعکس، یک لیست عمودی باید به شکل افقی برای پیوست به یک گزارش پهن ماهانه دربیاید. تابع TRANSPOSE دقیقا همین جابجایی سطر و ستون را با یک فرمول انجام می دهد.

داده نمونه؛ فیش حقوق افقی

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

هدف این است که همین اطلاعات، بدون تایپ مجدد، به یک لیست عمودی دو ستونی (عنوان و مقدار) تبدیل شود؛ فرمتی که برای چاپ فیش تک برگی یا پیوست به ایمیل مناسب تر است.

فرمول TRANSPOSE برای تبدیل افقی به عمودی

تابع TRANSPOSE با این ساختار کار می کند: TRANSPOSE(array). اگر ورودی آن یک محدوده دو سطری و ده ستونی باشد (مثل سطر عنوان و سطر مقدار فیش حقوق)، خروجی آن یک محدوده ده سطری و دو ستونی خواهد بود؛ یعنی همان لیست عمودی عنوان و مقدار.

مراحل ساخت فرمول:

1. در برگه ای جدید یا کنار جدول افقی، سلول مقصد بالا سمت راست لیست عمودی را انتخاب کنید (مثلا A2).
2. فرمول زیر را بنویسید: =TRANSPOSE('فیش افقی'!A1:J2)؛ که A1:J2 محدوده دو سطری (عنوان و مقدار) در برگه منبع است.
3. در اکسل ۳۶۵ فقط Enter بزنید؛ نتیجه به طور خودکار در ۹ سلول پایینی و ستون کناری سرریز (Spill) می شود.
4. در نسخه های قدیمی تر اکسل (بدون آرایه پویا)، پیش از نوشتن فرمول باید کل محدوده مقصد (این جا A2:B11) را از قبل انتخاب کنید و فرمول را با کلید ترکیبی Ctrl+Shift+Enter وارد کنید.

اگر بعدا عددی در سطر افقی منبع تغییر کند، چون فرمول زنده است، لیست عمودی هم خودش را به روز می کند؛ برخلاف کپی کردن دستی که فقط یک عکس لحظه ای از داده است.

بازگرداندن جدول عمودی به افقی؛ همان تابع در جهت برعکس

TRANSPOSE جهت خاصی ندارد؛ دقیقا همان فرمول برای برگرداندن یک لیست عمودی به افقی هم کار می کند. مثلا اگر یک گزارش کارانه یک بخش را ستونی (بخش، سپس مبلغ هر ماه زیر هم) وارد کرده باشید و بخواهید آن را برای پیوست به یک گزارش پهن ماهانه دوباره افقی کنید، کافی است همان محدوده ستونی را TRANSPOSE کنید:

چرا فرمول TRANSPOSE و نه Paste Special نوع Transpose؟

• روش قدیمی تر، کپی کردن محدوده و سپس Paste Special با گزینه Transpose است؛ این کار هم جواب می دهد اما نتیجه فقط یک عکس لحظه ای از داده هاست و هیچ ارتباطی با منبع اصلی ندارد؛ با تغییر داده منبع، باید دوباره کپی و Paste Special انجام شود.
• فرمول TRANSPOSE، برخلاف Paste Special، زنده و پویا است؛ با هر تغییر در جدول منبع، لیست تبدیل شده هم خودکار به روز می شود.
• Paste Special همچنان برای یک تبدیل یک باره و ساده (وقتی نیازی به ارتباط زنده نیست) سریع تر است؛ به خصوص در فایل هایی که قرار نیست دوباره داده منبع تغییر کند.

تکنیک تکمیلی؛ TRANSPOSE برای برچسب محور نمودار

یک کاربرد رایج دیگر TRANSPOSE، تبدیل سرستون های افقی یک گزارش پهن (مثلا نام ماه های فروردین تا شهریور در یک ردیف) به یک لیست عمودی برای استفاده به عنوان برچسب محور یک نمودار یا ستون کمکی یک Pivot Table است؛ فرمول دقیقا به همان شکل، فقط روی محدوده یک سطری اجرا می شود: =TRANSPOSE(A1:F1).

نکات کاربردی

• چون نتیجه TRANSPOSE یک آرایه سرریزشونده است، نمی توان تک تک سلول های داخل آن را جداگانه ویرایش کرد؛ برای تغییر باید کل فرمول در سلول اصلی اصلاح شود یا با Paste Special > Values نتیجه را به مقدار ثابت تبدیل کرد.
• TRANSPOSE فقط مقدار سلول ها را جابجا می کند؛ قالب بندی (رنگ، فرمت عدد، مرز جدول) منتقل نمی شود و باید دوباره روی محدوده جدید اعمال شود.
• اگر محدوده منبع شامل سلول خالی باشد، در نتیجه TRANSPOSE آن سلول به صورت عدد صفر نمایش داده می شود؛ برای جلوگیری از این حالت می توان فرمول را داخل یک IF قرار داد.
• در نسخه های اکسل بدون آرایه پویا (Excel 2019 و قدیمی تر)، اگر محدوده مقصد را دقیقا به اندازه نتیجه (تعداد سطر و ستون درست) از قبل انتخاب نکنید و Ctrl+Shift+Enter نزنید، فرمول یا خطا می دهد یا فقط یک سلول را پر می کند.

📄 دانلود فایل اکسل نمونه «تبدیل فیش حقوق افقی به عمودی با TRANSPOSE» — شامل فیش افقی، فرمول زنده TRANSPOSE برای تبدیل به فیش عمودی، نمونه بازگشت جدول عمودی به افقی، و برچسب سازی عمودی سرستون ماه ها برای نمودار.

منبع : سایت حسابداری بیمارستان
کانال تلگرام @hospitalaccounting