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

حضرت علی (ع): «زَکوةُ العِلمِ نَشرُهُ؛ زکات علم نشر آن است».
وبلاگ "ترفندها" (سایت پی‌سی‌ترفند) برای عمل به این حدیث شریف و با هدف ارتقاء دانش و مهارت‌های مرتبط با رایانه در ۳۰ مهرماه ۱۳۹۱ ایجاد شده است. به امید آنکه مفید واقع شود.
🔵 کانال پی‌سی‌ترفند در تلگرام:
telegram.me/pctarfand_ir

دنبال کنندگان ۵۰ نفر

این وبلاگ را دنبال کنید

۲۶

دی ۹۳

نظرات

۲۰

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

مرادی

| ۲۶ دی ۹۳ ، ۱۵:۳۴

اکسل ماکرونویسی

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

برچسب ها column index row افزونه ASAP Utilities افزونه Kutools for Excel تابع اکسل تبدیل یک ستون به چند ردیف فرمول در اکسل ماکروی اکسل

نظرات (۲۰)

arghavan1

۲۳ خرداد ۹۷ ، ۰۹:۲۸

خیلی لطف کردید
همیشه شاد و پیروز باشید
arghavan1

۲۱ خرداد ۹۷ ، ۱۴:۴۶

سلام اقای مهندس
من فایل را خدمتتون ارسال کردم ، لطف می کنید راهنمایی کنید.
ممنونم
- پاسخ:
  
  ۲۲ خرداد ۹۷، ۱۸:۴۴
  
  سلام
  باید در فرمول بجای ۱۱ از ۲۳ استفاده کنید.
  موفق باشید.
arghavan1

۱۹ خرداد ۹۷ ، ۱۸:۵۲

سلام و خسته نباشید
من به اکسل خیلی اشنا نیستم. اما با این توضیحات بالا فقط سطر یک را درست میتونم در بیارم. بقیه سطرها به هم میریزه.نمیدونم مشکل از کجاست
فایل:
http://s9.picofile.com/file/8328748926/Book1.xlsx.html
arghavan1

۱۸ خرداد ۹۷ ، ۱۴:۳۷

سلام. مشکل قبلی که خدمتتون فرستادم با یه سیستم دیگه حل شد . اما هر کاری میکنم باز هم یه جای کار میلنگه
این ستون 253 تایی باید به 11 سطر 23 تایی که سلول اول کلمه "nomre" هست تبدیل بشه اما ملاحظه کنید چه طور میشه:

http://s9.picofile.com/file/8328635500/Untitled.png
- پاسخ:
  
  ۱۸ خرداد ۹۷، ۱۷:۲۳
  
  سلام
  مطمئن هستید فرمول ها را درست استفاده می کنید؟ اگر پس از بررسی مجدد مشکلتان حل نشد فایل خود ر ا برای من ارسال کنید.
arghavan1

۱۷ خرداد ۹۷ ، ۱۳:۴۷

سلام و عرض احترام
میخوام یک ستون 48 تایی تبدیل به 3 سطر 16 تایی شود اما بعد پیست کردن فرمول با این خطا مواجه میشوم؛ میشه راهنمایی بفرمائید
یک دنیا ممنون
http://s8.picofile.com/file/8328562068/%D8%AA%D8%B5%D9%88%DB%8C%D8%B1.png

http://s8.picofile.com/file/8328562176/%D8%A7%D8%B1%D8%B2%D8%B4%DB%8C%D8%A7%D8%A8%DB%8C_%D8%A8%D8%B1%D9%86%D8%A7%D9%85%D9%87.xlsx.html
فریدون

۰۸ آذر ۹۶ ، ۰۸:۴۸

سلام مدیر گرامی
چگونه می توان یک ستون شامل نام و نام خانوادگی (با تعداد زیاد ) را به صورت دو ستون جداگانه تبدیل کرد
- پاسخ:
  
  ۱۱ آذر ۹۶، ۱۶:۳۱
  
  سلام
  اگر تمام نام ها و نام های خانوادگی یک کلمه ای باشند براحتی می توان با استفاده از این مطلب اینکار را انجام داد.
  موفق باشید.
یوسف

۰۴ آبان ۹۶ ، ۱۵:۰۴

سلام مدیریت محترم
من یک سوالی داشتم ،
من 324 تا داده در یک ستون دارم که میخوام آن را به 27 سطر 12 تایی تبدیل کنم .یعنی هر 12 تا داده را در یک سطر قرار دهد ممنون میشم که سریعتر پاسخ بدید باتشکر
- پاسخ:
  
  ۴ آبان ۹۶، ۲۳:۳۱
  
  سلام
  مطلب بالا همین کار را آموزش داده است.
  موفق باشید.
فرشاد

۰۷ مهر ۹۶ ، ۱۵:۰۷

سلام
من هشت هزار داده داشتم برای جابه جایی. با راهنمایی شما عرض دو ساعت انجام شد. خیلی خوب توضیح داده بودید. بی نهایت ممنونم. امیدوارم راهتونو به همین خوبی ادامه بدید.
محمد

۱۲ اسفند ۹۵ ، ۱۱:۵۸

سلام، اگه بخوایم عکس این کار رو انجام بدم باید چیکار کنم، ینی جدولی باشه بخوام همه ی داده هارو بیارو تووی یک ستون ؟
- پاسخ:
  
  ۱۲ اسفند ۹۵، ۱۲:۳۶
  
  سلام
  به این مطلب مراجعه کنید.
ّفاطمه66

۰۸ مهر ۹۵ ، ۱۴:۱۲

خیلی خیلی عالی و کاربردی بود
خدا خیر دنیا و آخرت رو بهتون بده
حمیدرضا

۱۹ تیر ۹۵ ، ۱۴:۱۱

سلام
من میخوام زمانی که در یک سلول عدد 2 یا هر عدد دیگری وارد میکنم دو ستون اضافه گردد
مثلا زمانی که در سلول b2 عدد 3 را وارد کردم بعد از ستون H سه ستون دیگر اضافه گردد
باید چه فرمولی بنویسم؟
جایی دیدم که اینکار شدنیه
ممنون
- پاسخ:
  
  ۱۹ تیر ۹۵، ۱۹:۰۳
  
  سلام
  متاسفانه راهی سراغ ندارم.
vahideh

۲۹ ارديبهشت ۹۵ ، ۱۱:۲۳

با سلام. ببخشید من یه سری داده توی اکسل دارم که فرم کلیشون به این شکله:
pstS, phoS,b3728,JW3706 که من می خوام از محل "," علامت از هم جدا بشن و در چند ردیف قرار بگیرن. میشه خیلی ساده بگین چیکار کنم؟
با تشکر
- پاسخ:
  
  ۲۹ ارديبهشت ۹۵، ۱۱:۳۳
  
  سلام
  با استفاده از ابزار Text to column می‌توانید آن‌ها را از هم جدا کنید (در این قسمت بجای تیک زدن گزینه Space از گزینه comma استفاده کنید).
  موفق باشید.
علی جلالی

۲۹ فروردين ۹۵ ، ۱۴:۳۴

سلام
اطلاعات هر سلول من به صورت
181,10-Mar-2016,08:03 می باشد و من میخوام اطلاعاتیکه با , جدا شدن رو به سلول های جدا از هم تبدیل کنم
181
10-Mar-2016 08:03
ممنون میشم راهنماییم کنید.
- پاسخ:
  
  ۲۹ فروردين ۹۵، ۱۵:۴۴
  
  سلام
  به دو روش می‌توان این کار را انجام داد:
  روش ۱- این قسمت (بجای فاصله " " از ویرگول "،" استفاده کنید) و
  روش ۲- این قسمت (بجای تیک زدن گزینه Space از گزینه comma استفاده کنید)
  موفق باشید.
اسماعیل

۱۷ فروردين ۹۵ ، ۱۳:۱۰

با سلام . من یک ستون با 1000 شماره دارم که برای چاپ کردن میخام تو چند ردیف پشت سر که توی یه برگ آچار فیکس بشن چاپ کنم. منتها هر کاری میکنم یه سطر میشن .چکار باید کرد ؟ با تشکر
- پاسخ:
  
  ۱۷ فروردين ۹۵، ۱۵:۴۱
  
  سلام
  یعنی یک ستون با ۱۰۰۰ ردیف دارید؟ منظورتان از اینکه یک سطر می‌شوند را متوجه نشدم!
مصطفی

۲۹ آبان ۹۴ ، ۱۸:۴۶

بسیار بسیار ممنون راهنمایی فوق العاده ای بود تشکر
مصطفی

۲۹ آبان ۹۴ ، ۱۷:۱۱

به فایل تکست که داده باید از چپ به راست تو هر ردیف به ترتیب برداشته بشه و همه تو یک ستون نوشته بشه چه کار باید بکنم مرسی.
مثال : 1 2 3 4 5
8 12 9 0 6
5
4
3
2
1
6
0
9
12
8
- پاسخ:
  
  ۲۹ آبان ۹۴، ۱۷:۳۱
  
  سلام
  این داده‌ها را باید به اکسل منتقل کنید. سپس با مراجعه به این سایت می‌توانید از روش‌های گفته شده برای تبدیل دو ردیف به یک ستون در اکسل استفاده کنید.
  موفق باشید.
فرزانه

۲۰ آبان ۹۴ ، ۱۲:۱۶

چطوری باید 2 یا سه سر ستون را در فایل اکسل ثابت کنیم. از قسمت freez فقط یک سطر یا ستون میشود.
- پاسخ:
  
  ۲۰ آبان ۹۴، ۱۳:۳۳
  
  سلام
  برای اینکار زیر سطر دوم یعنی روی سطر سوم (سلول A3 یا هر سلولی در سطر سوم) کلیک کرده، سپس روی دکمه Freeze panes کلیک کنید.
  موفق باشید.
فاطمه

۱۷ آبان ۹۴ ، ۲۱:۱۵

سلام
ممنونم از بابت مطالب فوق العاده تون
من یه سوال داشتم
اگه من یه سری از داده ها رو وارد یه ستون از اکسل بکنم بعد بخام یکی در میون سل خالی بشه چکار باید بکنم البته منظورم اینه که مثلا داده سل 1 باشه سل 2 خالی بشه و داده سل 2 بره داخل سل 3 به همین صورت تا آخرین داده
- پاسخ:
  
  ۱۷ آبان ۹۴، ۲۲:۰۴
  
  سلام
  به این مطلب مراجعه کنید.
  موفق باشید.
legulas

۱۵ تیر ۹۴ ، ۲۳:۰۱

سلام
چطور میشه hello ها رو زیر هم آورد بطوریکه سطرها کمی جلو و عقب بشوند
تشکر
- پاسخ:
  
  ۱۶ تیر ۹۴، ۲۱:۵۷
  
  سلام
  ابتدا توضیحات آخرین مثال مطلب بالا را به دقت مشاهده کنید، سپس:
  شما باید بدانید که بیشترین تعداد ستون‌ها چه مقداری است مثلا در مثال فوق مربوط به ردیف چهارم است که برابر با ۶ است بنابراین با دانستن این مقدار باید از کد زیر استفاده کنید و بجای قسمت قرمز بیشترین تعداد ستون‌ها را قرار دهید.
  
  Sub OneCol2Cols()
  k = 1
  Z = 1
  For i = 1 To 4
     Do Until IsEmpty(Cells(Z, 1))
      Z = Z + 1
     Loop
     j = 6 - Z + k
        Do Until IsEmpty(Cells(k, 1))
     Cells(k, 1).Select
     Selection.Copy
     Cells(i, j + 4).Select
      ActiveSheet.Paste
      k = k + 1
      j = j + 1
      Loop
      k = k + 1
      Z = k
  Next
  End Sub
  
  موفق باشید.
علی

۱۷ بهمن ۹۳ ، ۰۰:۲۶

سلام
واقعا مرسی بابت وبلاگ ات .
یه مشکلی که من هم دارم دستگاه داده های ازمایشمن و به این صورت در اکسل داده .چون داده ها زیاد اند نمیتونم با دست جداشون کنم .
251.0000;0.0397
255.0000;0.2425
به نظر تون برای جدا کردن داده هاقبل و بعد از ; و نوشتن در یک ستون دیگر چی کار کنم.؟
با تشکر
- پاسخ:
  
  ۱۷ بهمن ۹۳، ۰۹:۳۷
  
  سلام
  از روش تجزیه استفاده کنید. در اینجا توضیح داده شده است. کافیست در مرحله ۴ بجای انتخاب گزینه space، گزینه other را انتخاب کنید و علامت ; را تایپ کنید.
  موفق باشید.

قبل از ارسال نظر به نکات زیر توجه کنید:

۱- با توجه با اینکه نظرات خصوصی شما امکان نمایش در سایت را ندارد، بنابراین هنگام ارسال نظر، گزینه "نظر بصورت خصوصی ارسال شود" را انتخاب نکنید. چون تنها راه پاسخگویی به آن از طریق ایمیل شما است که با توجه به مشغله کاری فرصت ارسال پاسخ از طریق ایمیل وجود ندارد.

۲- قبل از مطرح کردن سوالتان، مطلب فوق را با دقت مطالعه کنید. اگر پاسخ سوالتان را پیدا نکردید در سایت جستجو کنید، ممکن است در مطلب دیگر پاسخ سوالتان را پیدا کنید.

۳- تنها به سوالاتی پاسخ داده می‌شود که از نحوه حل آن‌ها اطلاع داشته باشم.

۴- نظراتی با مضمون زیر، صرفا تایید و نمایش داده می‌شود و به آن‌ها پاسخی داده نمی‌شود.

الف) در مورد پاسخ سوال شما اطلاعی نداشته باشم،

ب) پاسخ سوال شما در مطلب فوق وجود داشته باشد.

ارسال نظر آزاد است، اما اگر قبلا در بیان ثبت نام کرده اید می توانید ابتدا وارد شوید.

نام *

پست الکترونيک

سایت یا وبلاگ

پیام *

شما میتوانید از این تگهای html استفاده کنید:

یا ، یا ، ، <strike> یا <s>، ، ، <blockquote>، <code>، <pre>، <hr>، ، ، <a href="" title="">، ، <div align="">

کد امنیتی *