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

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

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

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

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

بوسیله توابع Row ،Index و Column می‌توان اینکار را انجام داد. نحوه استفاده از این سه تابع را این مطلب مشاهده کنید.

ابتدا پس از کپی داده‌ها درون اکسل، باید مشخص کنید که می‌خواهید داده‌ها به چند ستون تبدیل شوند، تعداد این ستون‌ها را با n نمایش می‌دهیم. مثلا برای شکل بالا چون می‌خواهیم تغییرات دما و فشار در طول زمان نشان داده شود بنابراین n=۳ ستون باید ایجاد شود. ستون زمان، ستون دما و ستون فشار.

در اولین سلول خالی کنار داده‌ها فرمول زیر را پیست می‌کنیم و به سایر سلول‌ها هم انتقال می‌دهیم.

=INDEX(محدوده,n*(ROW(A1)-1)+COLUMN(A1))

چون برای مثال بالا محدوده A1:A9 و n=3 است بنابراین فرمول بصورت زیر اصلاح می‌شود. البته دقت کنید که محدوده را در فرمول بالا با قرار دادن علامت $ در اطراف آن باید ثابت نگه داشت تا تغییر نکند.

=INDEX($A$1:$A$9,3*(ROW(A1)-1)+COLUMN(A1))

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

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

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

البته بجای تایپ حروف می‌توانید فرمول زیر را در اولین سلول کپی کرده (بجای تایپ حرف a) و سپس مشابه شکل بالا در سایر سلول‌ها اعمال کنید و بقیه مراحل را مشابه شکل بالا انجام دهید.

=MOD(ROW(A1)+n-1,n)

که برای مثال بالا بجای n باید عدد ۳ قرار داد.

=MOD(ROW(A1)+2,3)

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

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

Sub OneCol2nCols()
'pctarfand.ir & tarfandha.blog.ir'
n = 3
m = 9
k = 1
For i = 1 To m / n
For j = 1 To n
Cells(k, 1).Select
  Selection.Copy
   Cells(i, j + 2).Select
    ActiveSheet.Paste
    k = k + 1
    Next
Next
End Sub

Sub TrsPose()
'pctarfand.ir & tarfandha.blog.ir'
Dim Area As Range, i As Long
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For Each Area In Range("A1:A" & LR).SpecialCells(xlCellTypeConstants).Areas
    i = i + 1
    Range("B" & i).Resize(, Area.Rows.Count).Value = Application.Transpose(Area)
Next Area
End Sub

Sub OneCol2Cols()
'pctarfand.ir & tarfandha.blog.ir'
k = 1
For i = 1 To 4
j = 1
   Do Until IsEmpty(Cells(k, 1))
   Cells(k, 1).Select
   Selection.Copy
   Cells(i, j + 2).Select
    ActiveSheet.Paste
    k = k + 1
    j = j + 1
    Loop
    k = k + 1
Next
End Sub