به خاطر دارم که در آزمایشگاه، نرم‌افزاری که داده‌های دما و فشار سیستم را طول زمان ذخیره می‌کرد یک نقص بزرگ داشت و آن این بود که تمام اطلاعات یعنی زمان، دما و فشار را پشت سرهم مثل شکل روبرو در یک فایل 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))

مرجع: www.excelforum.com

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

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

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

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

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

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

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

تابع MOD:
تابع MOD برای تعیین باقیمانده تقسیم استفاده می‌شوند. در واقع این تابع دو ورودی را دریافت می‌کند و خروجی آن برابر است با میزان باقی‌مانده ناشی از تقسیم ورودی اول بر ورودی دوم. برای مثال اگر در سلول A1 عبارت زیر را وارد کنید، خروجی آن برابر با ۱ می‌شود، زیرا باقیمانده تقسیم ۴ بر ۳ برابر است با ۱.
=MOD(4,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

دقت کنید که در کد فوق باید بجای n و m با توجه به داده‌های خود عدد بگذارید. n تعداد ستون‌ها و m تعداد کل داده‌ها است که در مثال ما n=3 و m=9.

سپس دکمه  یا کلید F5 را فشار دهید تا عملیات جابجایی انجام شود.

از کد زیر نیز می‌توانید استفاده کنید (منبع):

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

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

  • افزونه ASAP Utilities:

پس از دانلود و نصب این Add-in، تب جدیدی با عنوان ASAP Utilities به اکسل اضافه می‌شود.

نحوه استفاده از این افزونه در این سایت توضیح داده شده است.

  • افزونه Kutools for Excel:

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

حالت خاص: سوال یکی از کاربران:

جواب سوال: دو روش برای مشکل فوق پیشنهاد می شود:

۱- نصب افزونه Kutools for Excel که در بالا به آن اشاره شد + راهنمای استفاده

۲- ماکرو نویسی: با استفاده از ماکرو زیر می‌توانید عملیات فوق را انجام دهید. نحوه استفاده از ماکرو در بالا در قسمت روش ۳ توضیح داده شده است. فقط باید دقت کنید که عامل توقف حلقه وجود سلول خالی است نه Hello، بنابراین وجود سلول خالی بین مجموعه‌ها الزامی است. البته باید به جای عدد ۴، تعداد ردیف‌هایی که می‌خواهید ایجاد شود را قرار دهید. که در مثال فوق ۴ است.

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

 



برگرفته شده از pctarfand.ir