جستجو

ترفندهای کامپیوتری

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

خلاصه آمار

تبليغات

پربحث ترين ها

۳

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

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

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

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

برای تبدیل محدوده‌ی داده‌ها به یک ستون دو حالت وجود دارد: 

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

داده‌های زیر را به عنوان نمونه در نظر بگیرید. هدف این است که این دو ردیف داده را به یک ستون تبدیل کنیم. نحوه جابجا شدن سلول‌ها در شکل زیر نشان داده شده است.

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

در واقع این جابجایی را بوسیله توابع Row و Index که در این مطلب معرفی شده‌اند و همچنین با استفاده از توابع MOD و INT که در ادامه با آن‌ها آشنا می‌شوید می‌توان انجام داد.

تابع MOD:
تابع MOD برای تعیین باقیمانده تقسیم استفاده می‌شوند. در واقع این تابع دو ورودی را دریافت می‌کند و خروجی آن برابر است با میزان باقی‌مانده ناشی از تقسیم ورودی اول بر ورودی دوم. به طور مثال خروجی تابع زیر برابر با ۱ می‌شود، زیرا باقیمانده تقسیم ۴ بر ۳ برابر است با ۱.
=MOD(4,3)=1
 
تابع INT:
INT مخفف کلمه Integer یا همان عدد صحیح است. در واقع کاری که این تابع انجام می‌دهد حذف تمامی ارقام اعشاری و ایجاد یک عدد صحیح است. تابع INT همیشه اعداد اعشاری را حذف و عدد را به سمت پایین گرد می‌کند. به عنوان مثال، دو عدد با مقدار 7.998 و 7.111 داریم؛ خروجی تابع INT برای هر دوی این اعداد برابر 7 است. پس فرقی نمی‌کند اعداد اعشاری رو به بالا باشد یا رو به پایین، خروجی هر دو یکسان است.
=Int(7.998)=7
=Int(7.111)=7

در این حالت قبل از استفاده از فرمول زیر، باید تعداد ستون‌های داده‌های اولیه (n) را مشخص کرد.

=INDEX(M,INT((ROW(A1)-1)/n)+1,MOD(ROW(A1)1,n)+1)

M بیانگر محدوده مورد نظر است که برای مثال بالا، A1:C2 و n=3 است، بنابراین فرمول بصورت زیر اصلاح می‌شود. البته دقت کنید که آدرس محدوده باید مطلق باشد تا با جابجایی تغییر نکند. در اولین سلول خالی کنار داده‌ها فرمول زیر را پیست می‌کنیم.

=INDEX($A$1:$C$2,INT((ROW(A1)-1)/3)+1,MOD(ROW(A1)-1,3)+1)

پس از اعمال فرمول بالا به سلول‌های پایین‌تر، نتیجه مورد نظر که در شکل بالا نمایش داده شده است ظاهر می‌شود.

مرجع: stackoverflow.com

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

=IF(ISBLANK(INDEX($A$1:$C$2,INT((ROW(A1)-1)/3)+1,MOD(ROW(A1)-1,3)+1)),"",INDEX($A$1:$C$2,INT((ROW(A1)-1)/3)+1,MOD(ROW(A1)-1,3)+1))

یا

=IF(INDEX($A$1:$C$2,INT((ROW(A1)-1)/3)+1,MOD(ROW(A1)-1,3)+1)=0,"",INDEX($A$1:$C$2,INT((ROW(A1)-1)/3)+1,MOD(ROW(A1)-1,3)+1))

توجه: بجای تایپ دستی تعداد ستون‌های محدوده مورد نظر می‌توانید از تابع Columns استفاده کنید که در این مطلب معرفی شده است، مشابه این سایت.

------------------------------

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

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

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

Sub ConvertRangeToColumn()
'pctarfand.ir & tarfandha.blog.ir'
Dim Range1 As Range, Range2 As Range, Rng As Range
Dim rowIndex As Integer
xTitleId = "KutoolsforExcel"
Set Range1 = Application.Selection
Set Range1 = Application.InputBox("Source Ranges:", xTitleId, Range1.Address, Type:=8)
Set Range2 = Application.InputBox("Convert to (single cell):", xTitleId, Type:=8)
rowIndex = 0
Application.ScreenUpdating = False
For Each Rng In Range1.Rows
    Rng.Copy
    Range2.Offset(rowIndex, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
    rowIndex = rowIndex + Rng.Columns.Count
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

سپس دکمه  یا کلید F5 را فشار دهید تا پنجره‌ی زیر ظاهر شود. ناحیه سلول‌های مدنظرتان (مثلا A1:C3) را انتخاب کنید.

سپس روی دکمه Ok کلیک کنید. در پنجره جدید باز شده سلولی که می‌خواهید نتایج در آن درج شود انتخاب کنید مثلا F1.

در پایان روی دکمه Ok کلیک کنید تا نتیجه زیر ظاهر شود.

منبع: extendoffice.com

--------------------------------------

روش ۳) با استفاده از افزونه‌ Kutools for Excel:

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


حالت دوم) تبدیل چند ستون به یک ستون

داده‌های زیر را به عنوان نمونه در نظر بگیرید. هدف این است که این سه ستون داده را به یک ستون تبدیل کنیم. نحوه جابجا شدن سلول‌ها در شکل زیر نشان داده شده است.

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

فرمول استفاده شده در این حالت مشابه حالت قبل است با کمی تفاوت. فقط کافیست دو تابع INT و MOD را با هم جابجا کنید. البته در این حالت بجای تعیین تعداد ستون‌ها باید تعداد سطرهای داده‌های اولیه (n) را مشخص کرد.

=INDEX(M,MOD(ROW(A1)1,n)+1,INT((ROW(A1)-1)/n)+1)

M بیانگر محدوده مورد نظر است که برای مثال بالا، A1:C2 و n=2 است، بنابراین فرمول بصورت زیر اصلاح می‌شود. البته دقت کنید که آدرس محدوده باید مطلق باشد تا با جابجایی تغییر نکند. در اولین سلول خالی کنار داده‌ها فرمول زیر را پیست می‌کنیم.

=INDEX($A$1:$C$2,MOD(ROW(A1)-1,2)+1,INT((ROW(A1)-1)/2)+1)

پس از اعمال فرمول بالا به سلول‌های پایین‌تر، نتیجه مورد نظر که در شکل بالا نمایش داده شده است ظاهر می‌شود.

توجه: برای استفاده از دو فرمول دیگر که در حالت قبل گفته شد نیز این تغییرات را اعمال کنید.

توجه: بجای تایپ دستی تعداد سطرهای محدوده مورد نظر می‌توانید از تابع Rows استفاده کنید که در این مطلب معرفی شده است.

---------------------------------------------

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

مراحل ساخت ماکرو مشابه حالت قبل است با این تفاوت که بجای کدهای قبل از کدهای زیر استفاده کنید:

Sub TransformOneColumn()
'pctarfand.ir & tarfandha.blog.ir'
Dim InputRng As Range, OutRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Ranges to be transform :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Paste to (single cell):", xTitleId, Type:=8)
Application.ScreenUpdating = False
xRows = InputRng.Rows.Count
xCols = InputRng.Columns.Count
For i = 1 To xCols
    InputRng.Columns(i).Copy OutRng
    Set OutRng = OutRng.Offset(xRows, 0)
Next
Application.ScreenUpdating = True
End Sub

سایر مراحل هم مشابه حالت قبل است.

منبع با تغییر: extendoffice.com

 

توجه: اگر بخواهید خروجی کار بجای یک ستون یک ردیف باشد با استفاده از فرمول و همچنین کد ماکرویی که در این سایت ارائه شده است می‌توانید اینکار را انجام دهید.

 

عضویت در خبرنامه:

نظرات (۳)

  1. سلام دوست من
    من می خواهم تعدا اجناس را درج کنم که به مجموع تعداد اضافه بشه.
    یا بالعکس در بخش فروش که کسر بشه.
    در یک سل یا خانه که همیشه تعداد اجناس جدید رسیده به گدام را درج می کردم و در سل بعدی، ارقام قبلی و حال را با هم جمع میکرد به شکلی که اگر اعدا وارد شده را پاک هم می کردم، باز هم بخش مجموع، ارقامی که جمع کرده بود را از دست نمی داد و ثبت می شد.
    سال 91 خودم ساخته بودم اما حالا اصلا فکر یاری یم نمی کنه.
    دوست دارم حالا شما یارم باشید و اگر امکان دارد، پاسخ را به ایمیل من بفرستید.
    سپاس.
    • پاسخ:

      سلام
      متوجه سوال شما نشدم.
  2. سلام
    خسته نباشید
    من میخوام اطلاعات دو ستون A و B در ستون C زیر هم و یکی در میان قرار بگیرند یعنی B1زیرA1 و...
    وهمزمان دوتا در میان یک ردیف خالی بین آنها باشد یعنی A1وB1 پشت سرهم و بدون فاصله باشند.
    بعد یک ردیف خالی
    و بعد A2وB2 پشت سر هم و بدون فاصله تا آخر.
    شکل فعلی : A1        B1
    شکل نهایی:
    A1
    B1

    A2
    B2

    A3
    B3
     
    .
    .
    .
    تعداد سلول ها خیلی زیاد هست و واقعا ضروریه. منتظر پاسخ گرمتون هستم.
    ممنون
    • پاسخ:

      سلام
      در حالت اول مطلب بالا خواسته شما مطرح شده است. تنها کافیست برای ایجاد سلول خالی، ستون C را نیز انتخاب کنید تا سلول‌های خالی آن بین دو سلول دیگر قرار گیرد.
      موفق باشید.
    • پاسخ:

      سلام
      باز نشر مطالب سایت تنها با ذکر منبع (www.pctarfand.ir) مجاز است.
      موفق باشید.

ارسال نظر

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

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

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

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

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

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

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

ارسال نظر آزاد است، اما اگر قبلا در بیان ثبت نام کرده اید می توانید ابتدا وارد شوید.
شما میتوانید از این تگهای html استفاده کنید:
<b> یا <strong>، <em> یا <i>، <u>، <strike> یا <s>، <sup>، <sub>، <blockquote>، <code>، <pre>، <hr>، <br>، <p>، <a href="" title="">، <span style="">، <div align="">
تجدید کد امنیتی
X بستن