مطالب برتر
حضرت علی (ع): «زَکوةُ العِلمِ نَشرُهُ؛ زکات علم نشر آن است».
وبلاگ "ترفندها" (سایت پی‌سی‌ترفند) برای عمل به این حدیث شریف و با هدف ارتقاء دانش و مهارت‌های مرتبط با رایانه در ۳۰ مهرماه ۱۳۹۱ ایجاد شده است. به امید آنکه مفید واقع شود.
🔵 کانال‌های پی‌سی‌ترفند در تلگرام و سروش:
telegram.me/pctarfand_ir
sapp.ir/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 برای تعیین باقیمانده تقسیم استفاده می‌شوند. در واقع این تابع دو ورودی را دریافت می‌کند و خروجی آن برابر است با میزان باقیمانده ناشی از تقسیم ورودی اول بر ورودی دوم. برای مثال اگر در سلول 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@ دنبال کنید:
|
سوالات خود در زمینه ورد و اکسل و ... را میتوانید از طریق ایمیل و پیام‌رسان سروش با ما مطرح کنید. آدرس ایمیل: tarfandha.blog@gmail.com آی‌دی سروش: pctarfand_info@ گروه سروش: |
نظرات (۲۰)
-
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
ممنون میشم راهنماییم کنید. -
-
اسماعیل
با سلام . من یک ستون با 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 را انتخاب کنید و علامت ; را تایپ کنید.
موفق باشید.
-
تبليغات
