ترفندهای کامپیوتری

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

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

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

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

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

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

در این حالت قبل از استفاده از فرمول زیر، باید تعداد ستون‌های داده‌های اولیه (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)),"",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,"",INDEX($A$1:$C$2,INT((ROW(A1)-1)/3)+1,MOD(ROW(A1)-1,3)+1))

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

------------------------------

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

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

فرمول استفاده شده در این حالت مشابه حالت قبل است با کمی تفاوت. فقط کافیست دو تابع 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

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