تابع TRANSPOSE در اکسل – از صفر تا صد + مثال

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

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

فرمول تابع TRANSPOSE بسیار ساده است و فقط  یک آرگومان دارد.

=TRANSPOSE(array)

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

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

مراحل فرمول‌نویسی با تابع TRANSPOSE در اکسل

مراحل فرمول‌نویسی با تابع TRANSPOSE برای جابجایی سطر و ستون بسته به نسخه اکسل تفاوت دارد. در نسخه‌های قدیمی‌تر از آفیس ۲۰۲۱ مراحل کمی بیشتر است.

فیلم نمایش عمودی و افقی داده ها در اکسل با تابع TRANSPOSE + مثال کاربردی (رایگان) در فرادرس

کلیک کنید

فرمول‌نویسی در نسخه‌های قدیمی اکسل

برای فرمول‌نویسی با تابع TRANSPOSE در نسخه‌های ۲۰۱۹ به قبل اکسل مراحل زیر را انجام می‌دهیم.

1. شمارش سطر و ستون‌ها در جدول اصلی
2. انتخاب محدوه خالی دلخواه با چیدمان برعکس
3. نوشتن فرمول تابع TRANSPOSE
4. فشردن دکمه CTRL+SHIFT+ENTER

فرمول‌نویسی در نسخه‌های جدید اکسل

اما در نسخه آفیس ۲۰۲۱ و مایکروسافت ۳۶۵ فقط کافی است، با نوشتن فرمول تابع و انتخاب محدوده سلول‌ها فرایند تبدیل سطر به ستون (و برعکس) را انجام دهیم. مراحل انجام کار به شرح زیر است.

1. انتخاب یک سلول خالی دلخواه در جدول
2. نوشتن فرمول تابع TRANSPOSE

برای درک بهتر موضوع، در ادامه بحث هر یک از این موارد را همراه مثال توضیح می‌دهیم.

مثال فرمول‌نویسی تابع TRANSPOSE

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

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel + گواهینامه در فرادرس

کلیک کنید

مثال فرمول‌نویسی در نسخه‌های قدیمی اکسل

مثال زیر، نمونه ساده‌ای از فرمول‌نویسی صحیح در نسخه‌ ۲۰۱۹ و قدیمی‌تر اکسل است که در چهار مرحله زیر انجام می‌گیرد.

مرحله ۱. شمارش تعداد سطر و ستون‌ها در جدول اصلی

فرض می‌کنیم میزان صادرات میوه چند کشور مختلف را مانند تصویر زیر در اختیار داریم. همان‌طور که مشخص است این جدول چهار ستون و پنج ردیف دارد.

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

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

مرحله ۳. نوشتن فرمول تابع TRANSPOSE

در اولین سلول از جدول خالی جدید، فرمول تابع TRANSPOSE را می‌نویسیم. برای راحتی کار، ابتدا عبارت =TRANSPOSE( را می‌نویسیم.

سپس محدوده جدول اصلی را انتخاب می‌کنیم. به این شکل همانند تصویر زیر محدوده مورد نظر برای تغییر جهت در فرمول قرار می‌گیرد.

البته می‌توانیم به‌صورت دستی نام محدوده را در فرمول تایپ کنیم.

مرحله ۴. فشردن دکمه Ctrl+Shift+Enter

بعد از بستن پرانتز، برای اجرای صحیح فرمول هم‌زمان، دکمه‌های Ctrl+Shift+Enter را فشار می‌دهیم. با این کار در جدول جدید، سطر و ستون‌ها جابجا می‌شوند. در دو طرف فرمول اصلی نیز علامت آکولاد{} قرار می‌گیرد که نشانه اجرای فرمول آرایه‌ای است.

مثال فرمول‌نویسی در نسخه‌های جدید اکسل

برای تغییر جهت سطر و ستون‌های مثال قبلی در نسخه‌های جدید اکسل ابتدا یک سلول خالی دلخواه را در جدول انتخاب می‌کنیم. سپس با نوشتن فرمول تابع TRANSPOSE در آن دکمه ENTER را می‌زنیم.

در نسخه‌های جدید، برای فرمول‌نویسی تابع TRANSPOSE در اکسل نیازی به انتخاب سلول‌های خالی یا فشردن دکمه‌های ترکیبی CTRL+SHIFT+ENTER نداریم.

رفع مشکل سلول‌های خالی با تابع TRANSPOSE

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

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel + گواهینامه در فرادرس

کلیک کنید

مثال

در جدول فرضی زیر دو سلول خالی B3 و B5 وجود دارد.

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

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

در این فرمول اگر تابع IF سلول را خالی تشخیص بدهد، بعد از معکوس کردن سطر و ستون، سلول‌های خالی را بدون تغییر باقی می‌گذارد.

یادگیری کاربردهای پیشرفته تابع TRANSPOSE همراه فرادرس

تا این بخش، ساده‌ترین کاربرد تابع TRANSPOSE در اکسل را یاد گرفتیم. اما از این تابع نیز مانند سایر توابع می‌توانیم در فرمول‌نویسی‌های پیشرفته استفاده کنیم. به‌عنوان مثال در ترکیب با تابع SUM یا تابع AVERAGE به‌ترتیب مجموع یا میانگین سطر و ستون‌های جابجا شده را حساب کنیم. همچنین می‌توانیم تابع پرکاربرد XLOOKUP یا تابع VLOOKUP را در ترکیب با تابع TRANSPOSE برای جستجوی عبارت یا عدد دلخواه در نتیجه فرمول به‌کار ببریم.

همه این موارد که نیاز به دانش پیشرفته کار با اکسل دارند، نیازمند یادگیری فرمول‌نویسی حرفه‌ای در اکسل و آشنایی کامل با سایر توابع است. برای این منظور مجموعه فیلم‌های آموزشی تهیه شده در فرادرس راهنمای کاملی هستند که با توجه به نیاز می‌توانیم از درس‌های متنوع آن‌ها استفاده کنیم.

برای حرکت در این مسیر پیشنهاد مرحله اول یادگیری، مشاهده فیلم‌های منتخب آموزشی زیر است.

• فیلم آموزش استفاده از توابع و فرمول در اکسل همراه گواهینامه در فرادرس
• فیلم رایگان آموزش فرمول‌های پیچیده با ترکیب توابع مختلف در اکسل در فرادرس
• فیلم آموزش ترفندهای کاربردی اکسل همراه گواهینامه در فرادرس
• فیلم آموزش ابزارهای کاربردی اکسل همراه گواهینامه در فرادرس
• فیلم آموزش اکسل ۲۰۲۱ همراه گواهینامه در فرادرس

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

• مجموعه فیلم آموزش توابع و فرمول‌ در اکسل در فرادرس
• مجموعه فیلم آموزش مقدماتی تا پیشرفته اکسل در فرادرس

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

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

فیلم آموزش ترفندهای کاربردی در اکسل Excel + گواهینامه در فرادرس

کلیک کنید

۱. کمتر کردن داده‌ها

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

۲. اجرای درست فرمول

اگر هنگام اجرای فرمول در نسخه‌های قدیمی اکسل از کلید ترکیبی Ctrl+Shift+Enter استفاده نکنیم، تابع TRANSPOSE درست عمل نمی‌کند. بنابراین لازم است به این موضوع توجه کنیم.

۳. حذف سلول‌های ادغام شده

قبل از نوشتن فرمول، لازم است «سلول‌های ادغام شده» (Merged Cell) را حذف کنیم. زیرا وجود این سلول‌ها باعث ایجاد خطا هنگام اجرای تابع TRANSPOSE یا به‌هم ریختگی ساختار داده‌ها می‌شود.

در مطلب زیر از مجله فرادرس در مورد ادغام سلول‌ها توضیح داده شده است:

۴. یکسان بودن تعداد سلول‌ها

در نسخه‌های قدیمی اکسل که جدول جدیدی از سلول‌های خالی را پایین جدول اصلی انتخاب می‌کنیم، یکسان بودن محدوده سلول‌ها بسیار اهمیت دارد. در صورت رعایت نکردن این موضوع، با خطای #VALUE! روبرو می‌شویم.

کاربرد تابع TRANSPOSE در اکسل

تابع TRANSPOSE به‌خصوص در علم داده و ساختارسازی داده‌های هوش مصنوعی در موارد زیر مورد استفاده قرار می‌گیرد.

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel + گواهینامه در فرادرس

کلیک کنید

۱. مرتب کردن مجدد داده‌ها

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

۲. آماده‌سازی داده‌ها

بعضی از الگوریتم‌های یادگیری ماشین فقط داده‌های با فرمت خاص (به‌عنوان مثال قرارگیری در یک سطر یا ستون) را قبول می‌کنند. با استفاده از این تابع می‌توانیم داده‌ها را به فرم مورد نظر الگوریتم درآوریم.

۳. گزارش‌های پویا

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

سایر ابزارهای اکسل برای جابجایی سطر و ستون

تابع TRANSPOSE روشی برای جابجایی سطر و ستون‌ها در اکسل با فرمول است. اما در این نرم‌افزار دو ابزار کاربردی دیگر نیز وجود دارند که بدون فرمول‌نویسی می‌توانیم سطر را به ستون (و برعکس) تبدیل کنیم.

فیلم آموزش اکسل – ابزارهای کاربردی Excel + گواهینامه در فرادرس

کلیک کنید

ابزار Paste Special

این ابزار روشی بسیار ساده و سریع برای جابجایی سطر و ستون‌ها است. در این حالت بعد از کپی داده‌ها و راست کلیک، گزینه «Paste Special» را می‌زنیم. سپس در پنجره باز شده، بخش «Transpose» را انتخاب می‌کنیم. استفاده از از این روش به‌خصوص زمانی که تعداد سطرها بسیار زیاد و در نقطه مقابل تعداد ستون‌ها کم است، بسیار کاربرد دارد. اما از آنجا که داده‌های تغییر یافته ایستا هستند، در صورت تغییر داده‌های جدول اصلی، داده‌های جابجا شده به‌صورت خودکار به‌روز نمی‌شوند. بنابراین لازم است بار دیگر عملیات را تکرار کنیم.

برای یادگیری کامل نحوه استفاده از این ابزار، نکات تکمیلی را همراه مثال در مطلب زیر توضیح داده‌ایم.

ابزار Power Query

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

مراحل تغییر سطر و ستون با این ابزار به شرح زیر است.

۱. بعد از انتخاب جدول داده‌ها، از زبانه «Data» در بخش «Get & Transform Data» روی گزینه «From Table/Range» کلیک می‌کنیم.

۲. با کلیک روی دکمه «Ok» پنجره باز شده وارد ادیتور ابزار می‌شویم. در این بخش بعد از کلیک روی زبانه «Transform» از بخش « Use First Row as Headers» گزینه «Use Headers as First Row» را انتخاب می‌کنیم. به این شکل همه هدرهای جدول در ردیف اول قرار می‌گیرند.

۳. روی دکمه «Transpose» کلیک می‌کنیم.

۴. با ورود به زبانه «File» و انتخاب دکمه «Close & Load» به محیط اکسل برمی‌گردیم. نتیجه نهایی در این محیط قابل مشاهده است.

جمع‌بندی پایانی

فرمول‌نویسی با تابع TRANSPOSE در اکسل روشی ساده برای تبدیل سطر به ستون و برعکس است. در این مطلب از مجله فرادرس روش کار با این تابع، نکات مهم و کاربردهای آن را همراه مثال یاد گرفتیم. همچنین با سایر ابزارهای مشابه اکسل مانند «Paste Special» و «Power Query» نیز آشنا شدیم. با ترکیب تابع TRANSPOSE و سایر توابع اکسل می‌توانیم فرمول‌نویسی‌های حرفه‌ای دیگری را نیز انجام دهیم که برای یادگیری کامل آن‌ها مجموعه فیلم آموزش توابع و فرمول در اکسل در فرادرس مرجع مناسبی است.