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

به خاطر دارم که در آزمایشگاه، نرمافزاری که دادههای دما و فشار سیستم را طول زمان ذخیره میکرد یک نقص بزرگ داشت و آن این بود که تمام اطلاعات یعنی زمان، دما و فشار را پشت سرهم در یک فایل نوتپد ذخیره میکرد. زمانیکه دانشجویان اطلاعات را به یک فایل اکسل منتقل میکردند مجبور بودند بصورت دستی دادهها را جابجا کنند تا بتوانند نمودار تغییرات دما و فشار را در طول زمان رسم کنند. همان موقع با دیدن این مشکل در اینترنت جستجو کردم روشی پیدا کردم که با استفاده از آن دیگر نیاز نبود بصورت دستی دادهها را جابجا کرد بلکه اکسل این جابجایی را انجام میدهد. اکنون که این مطلب را مینویسم متوجه شدم که چندین روش برای انجام این کار وجود دارد. در ادامه مطلب با پیسیترفند همراه باشید تا با این روشها آشنا شوید.
تبدیل یک ستون به چند ردیف یا چند ستون چندین حالت دارد. برای حالتی نظیر تصویر فوق، در ادامه، ۴ روش پیشنهاد شده است.
پس از باز کردن فایل اکسل، دادههای خود را در ستون A در فایل باز شده کپی کنید.
مطابق راهنمای درون فایل، روی دکمه اجرای ماکروی ۱ کلیک کنید تا کد اجرا شود. پس از اجرای کد، کادر زیر ظاهرا میشود. بطور مثال اگر میخواهید دادههای شما به ۳ ستون تبدیل شوند در این کادر عدد ۳ را وارد کنید.
پس از کلیک روی دکمه ok کادر زیر ظاهرا میشود که در آن باید تعداد دادهها را وارد کنید. بطور مثال ۹.
پس کلیک روی دکمه ok، عملیات جابجایی انجام می شود.
روش ۲) فرمول نویسی:
بوسیله توابع 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 برای تعیین باقیمانده تقسیم استفاده میشوند. در واقع این تابع دو ورودی را دریافت میکند و خروجی آن برابر است با میزان باقیمانده ناشی از تقسیم ورودی اول بر ورودی دوم. برای مثال اگر در سلول A1 عبارت زیر را وارد کنید، خروجی آن برابر با ۱ میشود، زیرا باقیمانده تقسیم ۴ بر ۳ برابر است با ۱.
روش ۴) با استفاده از افزونههای اکسل
افزونه ASAP Utilities:
پس از دانلود و نصب این Add-in، تب جدیدی با عنوان ASAP Utilities به اکسل اضافه میشود.
نحوه استفاده از این افزونه در این سایت توضیح داده شده است.
افزونه Kutools for Excel:
افزونه Kutools for Excel نیز مشابه افزونه قبلی قابلیت تبدیل یک ستون به چند ردیف را دارد. پس از دانلود و نصب این Add-in، تب جدیدی به اکسل با عنوان Kutools اضافه میشود. نحوه استفاده از این افزونه در این سایت توضیح داده شده است.
حالت خاص:
در حالتی که بین دادههای ستون سلول خالی وجود دارد (مطابق دادههای ستون A در شکل زیر).
در این حالت هدف آن است که پس از پر شدن سلولهای یک ردیف، هنگام رسیدن به سلول خالی، سلولها از ردیف بعد پر شوند.
برای این حالت ابتدا دادههای خود را در ستون A در فایل اکسلی که در بالا دانلود کردید، کپی کنید.
سپس روی دکمه اجرای ماکروی ۲ کلیک کنید. پس از اینکار، عملیات جابجایی مطابق شکل بالا انجام میشود.
منبع: mrexcel.com
برای مطلع شدن از جدیدترین مطالب سایت، کانالهای پیسیترفند در تلگرام و سروش را با آیدی 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 را انتخاب کنید و علامت ; را تایپ کنید.
موفق باشید.
-
ارسال نظر
قبل از ارسال نظر به نکات زیر توجه کنید:
۱- با توجه با اینکه نظرات خصوصی شما امکان نمایش در سایت را ندارد، بنابراین هنگام ارسال نظر، گزینه "" را انتخاب نکنید. چون تنها راه پاسخگویی به آن از طریق ایمیل شما است که با توجه به مشغله کاری فرصت ارسال پاسخ از طریق ایمیل وجود ندارد.
۲- قبل از مطرح کردن سوالتان، مطلب فوق را با دقت مطالعه کنید. اگر پاسخ سوالتان را پیدا نکردید در سایت جستجو کنید، ممکن است در مطلب دیگر پاسخ سوالتان را پیدا کنید.
۳- تنها به سوالاتی پاسخ داده میشود که از نحوه حل آنها اطلاع داشته باشم.
۴- نظراتی با مضمون زیر، صرفا تایید و نمایش داده میشود و به آنها پاسخی داده نمیشود.
الف) در مورد پاسخ سوال شما اطلاعی نداشته باشم،
ب) پاسخ سوال شما در مطلب فوق وجود داشته باشد.
طبقه بندی موضوعی
- آفیس و پیدیاف (۱۳۶)
- ورد (۵۵)
- اکسل (۷۵)
- پاور پوینت (۳)
- پیدیاف (۱۰)
- اینترنت (۴۰)
- فایرفاکس (۲۴)
- اینترنت/سایر (۱۸)
- کد نویسی (۴۵)
- دستورات داس (۳)
- ماکرونویسی (۳۹)
- نرم افزار ویدئو (۹)
- نرم افزار عکس (۳)
- بازی (۱)
- رپورتاژ آگهی (۱)
- سایر موارد (۵۰)
- موبایل (۳)
بایگانی
- بهمن ۱۳۹۷ (۱)
- آذر ۱۳۹۷ (۱)
- آبان ۱۳۹۷ (۱)
- مهر ۱۳۹۷ (۱)
- شهریور ۱۳۹۷ (۱)
- مرداد ۱۳۹۷ (۲)
- اسفند ۱۳۹۶ (۱)
- بهمن ۱۳۹۶ (۱)
- آبان ۱۳۹۶ (۱)
- مهر ۱۳۹۶ (۱)
- شهریور ۱۳۹۶ (۱)
- مرداد ۱۳۹۶ (۲)
- تیر ۱۳۹۶ (۳)
- خرداد ۱۳۹۶ (۳)
- ارديبهشت ۱۳۹۶ (۱)
- فروردين ۱۳۹۶ (۱)
- اسفند ۱۳۹۵ (۱)
- بهمن ۱۳۹۵ (۱)
- دی ۱۳۹۵ (۱)
- آذر ۱۳۹۵ (۱)
- آبان ۱۳۹۵ (۱)
- مهر ۱۳۹۵ (۱)
- شهریور ۱۳۹۵ (۱)
- مرداد ۱۳۹۵ (۲)
- تیر ۱۳۹۵ (۲)
- خرداد ۱۳۹۵ (۲)
- ارديبهشت ۱۳۹۵ (۲)
- فروردين ۱۳۹۵ (۳)
- اسفند ۱۳۹۴ (۳)
- بهمن ۱۳۹۴ (۴)
- دی ۱۳۹۴ (۴)
- آذر ۱۳۹۴ (۶)
- آبان ۱۳۹۴ (۶)
- مهر ۱۳۹۴ (۴)
- شهریور ۱۳۹۴ (۴)
- مرداد ۱۳۹۴ (۳)
- تیر ۱۳۹۴ (۳)
- خرداد ۱۳۹۴ (۴)
- ارديبهشت ۱۳۹۴ (۳)
- فروردين ۱۳۹۴ (۲)
- اسفند ۱۳۹۳ (۲)
- بهمن ۱۳۹۳ (۲)
- دی ۱۳۹۳ (۴)
- آذر ۱۳۹۳ (۳)
- آبان ۱۳۹۳ (۳)
- مهر ۱۳۹۳ (۲)
- شهریور ۱۳۹۳ (۳)
- مرداد ۱۳۹۳ (۱)
- تیر ۱۳۹۳ (۴)
- خرداد ۱۳۹۳ (۶)
- ارديبهشت ۱۳۹۳ (۶)
- فروردين ۱۳۹۳ (۵)
- اسفند ۱۳۹۲ (۳)
- بهمن ۱۳۹۲ (۲)
- دی ۱۳۹۲ (۱)
- آذر ۱۳۹۲ (۳)
- آبان ۱۳۹۲ (۱)
- مهر ۱۳۹۲ (۲)
- شهریور ۱۳۹۲ (۳)
- مرداد ۱۳۹۲ (۴)
- تیر ۱۳۹۲ (۴)
- خرداد ۱۳۹۲ (۱)
- ارديبهشت ۱۳۹۲ (۷)
- فروردين ۱۳۹۲ (۲)
- بهمن ۱۳۹۱ (۲)
- دی ۱۳۹۱ (۱۱)
- آذر ۱۳۹۱ (۱۴)
- آبان ۱۳۹۱ (۲۴)
- مهر ۱۳۹۱ (۹)
کلمات کلیدی
آخرين مطالب
-
انتخاب یا حذف یک یا چند در میان ردیفها در اکسل - ماکرو
سه شنبه ۹ بهمن ۱۳۹۷ -
نگه داشتن فرمت و رنگ سلولها پس از ترکیب آنها در اکسل
سه شنبه ۲۷ آذر ۱۳۹۷ -
پیدا کردن نقاط تقاطع دو نمودار در اکسل - ماکرو
دوشنبه ۱۴ آبان ۱۳۹۷ -
ذخیره و انتقال ماکرو در ورد
يكشنبه ۱ مهر ۱۳۹۷ -
آموزش ایجاد و استفاده از ماکرو در نرمافزار ورد
پنجشنبه ۲۲ شهریور ۱۳۹۷ -
عدم نمایش قسمتی از یک تیتر یا زیرنویس شکل و جدول در فهرست ورد
چهارشنبه ۲۴ مرداد ۱۳۹۷ -
ایجاد ردیفهای خالی بر اساس عدد موجود در سلول در اکسل - ماکرو
پنجشنبه ۱۸ مرداد ۱۳۹۷ -
درج پاورقی در صفحهای با دو نوع ستون بندی در ورد
دوشنبه ۲۸ اسفند ۱۳۹۶ -
شمارش تعداد یک کاراکتر خاص در یک سلول اکسل
پنجشنبه ۵ بهمن ۱۳۹۶ -
استخراج سلولهای قطری یک ماتریس در اکسل
سه شنبه ۳۰ آبان ۱۳۹۶