تبدیل چند ردیف یا چند ستون به یک ستون در اکسل + ماکرو

برای تبدیل محدوده‌ی داده‌ها به یک ستون دو حالت وجود دارد:

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

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

روش ۱) فرمول نویسی:

در واقع این جابجایی را بوسیله توابع Row و Index که در این مطلب معرفی شده‌اند و همچنین با استفاده از توابع MOD و INT که در ادامه با آن‌ها آشنا می‌شوید می‌توان انجام داد.

تابع MOD:

تابع MOD برای تعیین باقیمانده تقسیم استفاده می‌شوند. در واقع این تابع دو ورودی را دریافت می‌کند و خروجی آن برابر است با میزان باقی‌مانده ناشی از تقسیم ورودی اول بر ورودی دوم.
به طور مثال خروجی تابع زیر برابر با ۱ می‌شود، زیرا باقیمانده تقسیم ۴ بر ۳ برابر است با ۱.

=MOD(4,3)=1

تابع INT:

INT مخفف کلمه Integer یا همان عدد صحیح است. در واقع کاری که این تابع انجام می‌دهد حذف تمامی ارقام اعشاری و ایجاد یک عدد صحیح است. تابع INT همیشه اعداد اعشاری را حذف و عدد را به سمت پایین گرد می‌کند. به عنوان مثال، دو عدد با مقدار 7.998 و 7.111 داریم؛ خروجی تابع INT برای هر دوی این اعداد برابر 7 است. پس فرقی نمی‌کند اعداد اعشاری رو به بالا باشد یا رو به پایین، خروجی هر دو یکسان است.

=Int(7.998)=7
=Int(7.111)=7

در این حالت قبل از استفاده از فرمول زیر، باید تعداد ستون‌های داده‌های اولیه (n) را مشخص کرد.

=INDEX(M,INT((ROW(A1)-1)/n)+1,MOD(ROW(A1)1,n)+1)

M بیانگر محدوده مورد نظر است که برای مثال بالا، A1:C2 و n=3 است، بنابراین فرمول بصورت زیر اصلاح می‌شود. البته دقت کنید که آدرس محدوده باید مطلق باشد تا با جابجایی تغییر نکند. در اولین سلول خالی کنار داده‌ها فرمول زیر را پیست می‌کنیم.

=INDEX($A$1:$C$2,INT((ROW(A1)-1)/3)+1,MOD(ROW(A1)-1,3)+1)

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

مرجع: stackoverflow.com

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

=IF(ISBLANK(INDEX($A$1:$C$2,INT((ROW(A1)-1)/3)+1,MOD(ROW(A1)-1,3)+1)),&quot&quot,INDEX($A$1:$C$2,INT((ROW(A1)-1)/3)+1,MOD(ROW(A1)-1,3)+1))

یا

=IF(INDEX($A$1:$C$2,INT((ROW(A1)-1)/3)+1,MOD(ROW(A1)-1,3)+1)=0,&quot&quot,INDEX($A$1:$C$2,INT((ROW(A1)-1)/3)+1,MOD(ROW(A1)-1,3)+1))

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

روش ۲) با استفاده از ماکرو نویسی در اکسل

خرید و دانلود از این لینک

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

مطابق راهنمای درون فایل، روی دکمه اجرای ماکروی ۱ کلیک کنید تا پنجره‌ زیر ظاهر شود. ناحیه سلول‌های مدنظرتان (مثلا A1:C3) را انتخاب کنید.

سپس روی دکمه Ok کلیک کنید. در پنجره جدید باز شده سلولی که می‌خواهید نتایج در آن درج شود انتخاب کنید مثلا F1.

در پایان روی دکمه Ok کلیک کنید تا نتیجه زیر ظاهر شود.

منبع: extendoffice.com

روش ۳) با استفاده از افزونه‌ Kutools for Excel:

افزونه Kutools for Excel نیز مشابه افزونه قبلی قابلیت تبدیل یک ستون به چند ردیف را دارد. پس از دانلود و نصب این Add-in، تب جدیدی به اکسل با عنوان Kutools اضافه می‌شود. نحوه استفاده از این افزونه در این سایت توضیح داده شده است.

________________________________________________________________________

حالت دوم) تبدیل چند ستون به یک ستون

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

روش ۱) فرمول نویسی:

فرمول استفاده شده در این حالت مشابه حالت قبل است با کمی تفاوت. فقط کافیست دو تابع INT و MOD را با هم جابجا کنید. البته در این حالت بجای تعیین تعداد ستون‌ها باید تعداد سطرهای داده‌های اولیه (n) را مشخص کرد.

=INDEX(M,MOD(ROW(A1)1,n)+1,INT((ROW(A1)-1)/n)+1)

M بیانگر محدوده مورد نظر است که برای مثال بالا، A1:C2 و n=2 است، بنابراین فرمول بصورت زیر اصلاح می‌شود. البته دقت کنید که آدرس محدوده باید مطلق باشد تا با جابجایی تغییر نکند. در اولین سلول خالی کنار داده‌ها فرمول زیر را پیست می‌کنیم.

=INDEX($A$1:$C$2,MOD(ROW(A1)-1,2)+1,INT((ROW(A1)-1)/2)+1)

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

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

توجه: بجای تایپ دستی تعداد سطرهای محدوده مورد نظر می‌توانید از تابع Rows استفاده کنید که در این مطلب معرفی شده است.

روش ۲) با استفاده از ماکرو نویسی در اکسل

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

مطابق راهنمای درون فایل، روی دکمه اجرای ماکروی ۲ کلیک کنید. سایر مراحل هم مشابه حالت قبل است.

منبع با تغییر: extendoffice.com

توجه: اگر بخواهید خروجی کار بجای یک ستون یک ردیف باشد با استفاده از فرمول و همچنین کد ماکرویی که در این سایت ارائه شده است می‌توانید اینکار را انجام دهید.

بازنشر شده از www.pctarfand.ir