طریقه تبدیل چند ردیف یا چند ستون به یک ستون در اکسل

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

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

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

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

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

در واقع این جابجایی را بوسیله توابع 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)

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

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

=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 استفاده کنید، مشابه این سایت.

——————————

روش ۲) با استفاده از ماکرو نویسی در اکسلبرای ساخت ماکرو کلیدهای Alt + F11 را فشار دهید یا از تب Developer قسمت code گزینه Visual Basic را انتخاب کنید. پنجره Microsoft Visual Basic ظاهر می‌شود. در این پنجره از تب Insert گزینه Module را انتخاب نمائید.

در پنجره جدید باز شده کدهای زیر را کپی کنید.

Sub ConvertRangeToColumn()
'pctarfand.ir & tarfandha.blog.ir'
Dim Range1 As Range, Range2 As Range, Rng As Range
Dim rowIndex As Integer
xTitleId = "KutoolsforExcel"
Set Range1 = Application.Selection
Set Range1 = Application.InputBox("Source Ranges:", xTitleId, Range1.Address, Type:=8)
Set Range2 = Application.InputBox("Convert to (single cell):", xTitleId, Type:=8)
rowIndex = 0
Application.ScreenUpdating = False
For Each Rng In Range1.Rows
    Rng.Copy
    Range2.Offset(rowIndex, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
    rowIndex = rowIndex + Rng.Columns.Count
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

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

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

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

———————————————

روش ۲) با استفاده از ماکرو نویسی در اکسلمراحل ساخت ماکرو مشابه حالت قبل است با این تفاوت که بجای کدهای قبل از کدهای زیر استفاده کنید:

Sub TransformOneColumn()
'pctarfand.ir & tarfandha.blog.ir'
Dim InputRng As Range, OutRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Ranges to be transform :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Paste to (single cell):", xTitleId, Type:=8)
Application.ScreenUpdating = False
xRows = InputRng.Rows.Count
xCols = InputRng.Columns.Count
For i = 1 To xCols
    InputRng.Columns(i).Copy OutRng
    Set OutRng = OutRng.Offset(xRows, 0)
Next
Application.ScreenUpdating = True
End Sub

سایر مراحل هم مشابه حالت قبل است.