جستجو

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

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

خلاصه آمار

تبليغات

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

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

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

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

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

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

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

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

  • افزونه ASAP Utilities:

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

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

  • افزونه Kutools for Excel:

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


حالت خاص:

در حالتی که بین داده‌های ستون، سلول خالی وجود دارد (مشابه داده‌های ستون A در شکل زیر). 

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

برای این حالت ابتدا داده‌های خود را در ستون A در فایل اکسلی که در بالا دانلود کردید، کپی کنید. 

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

منبع: mrexcel.com

برای مطلع شدن از جدیدترین مطالب سایت، کانال‌ پی‌سی‌ترفند در تلگرام را با آی‌دی pctarfandir@ دنبال کنید:

          

سوالات خود در زمینه ورد و اکسل و ... را می‌توانید از طریق ایمیل و تلگرام با ما مطرح کنید.

آدرس ایمیل: tarfandha.blog@gmail.com

آی‌دی تلگرام: pctarfand_admin@

نظرات (۳۰)

  1. محمدرضا اسدی نیا

    با سلام
    در شیت list با دستور index از شیت i.c نامهای ستون A بصورت چند ردیف لیست شدن
    • پاسخ:

      متاسفانه راهی برای مشکل شما پیدا نکردم.
  2. محمدرضا اسدی نیا

    با سلام

    میخواستم در شیت i.c فقط نامهایی که ردیف های موجودی مساوی و بزرگتر از 1 هستن در شیت i.c list فراخوان بشه .
    باتشکر
    https://www.uplooder.net/files/1d2525535a6e599c855ede2871b6dc85/kala.xls.html
    • پاسخ:

      سلام
      در حال حاضر شیت list دارای چه اطلاعاتی است؟
  3. محمدرضا اسدی نیا

    منظورم از لیستی فایل اکسل هست که چند شیت مختلف داره واطلاعات در یک شیت اصلی وارد میشه و در شیت های دیگه گزارشهای مختلف گرفته میشه
    • پاسخ:

      در صورت امکان یک نمونه فایل برای بررسی بیشتر برایم بفرستید.
  4. محمدرضا اسدی نیا

    با سلام و وقت بخیر
    من لیستی دارم که در ستون A نام وارد شده و حدود 3000 عدد هست و در ستون b مقادیر صفر و یک (موجود و عدم موجود)برای هر ردیف از طریق محاسبه فرمول نمایش داده میشه .
    با فرمول index یا offset میشه نام هایی که فقط مقادیر یک دارن یا فقط مقادیر صفر دارن بصورت لیستی نمایش داده بشه؟
    اگر بله ممنون میشم راهنمایی کنید
    • پاسخ:

      سلام
      منظورتان از لیستی چیه؟ کاملتر توضیح دهید.
  5. عصمت تیموریان

    سلام گرامی

    من می خواستم 36000 عدد که در یک ستون هست رو به 500 تایی 500 تایی تبدیل کنم ماکرو خریداری کردم ولی عمل نمی کنه
    هی مدام 9 تایی میشه
    • پاسخ:

      سلام
      3600 داده را می توان به 7 ردیف 500 تایی تبدیل کرد. چطور به 500 تا 500 تایی می خواهید تبدیل کنید؟!
      مطابق راهنما داده ها را در ستون A کپی کنید و پی از اجرای ماکروی 1، در کادر اول که باز می شود عدد 500 و در کادر دوم عدد 3600 را وارد کنید و تایید کنید.
      برای 1000 تا داده ماکرو تست شد. پس از اجرا به دو ردیف 500 تایی تبدیل شد.
  6. سلام ببخشید میشه جامع تر توضیح بدین.فرمول کجا باید نوشته بشه؟
    • پاسخ:

      سلام
      نحوه استفاده از فرمول ها در تصاویر به طور واضح نمایش داده شده است!
  7. سلام
    کد
    =INDEX($A$1:$A$9,3*(ROW(A1)-1)+COLUMN(A1))
    را وقتی ویزارم و میکشم پایین ارور میده سز را درست میکنه اما برای سطر های بعدی خطا میده
    میشه برای یک ستون حدود 10 هزار خطی که دارم برای اینکه 10 تا 10 تا ستونشون کنم کمکم کنی
    • پاسخ:

      سلام
      راهنمای استفاده از کد را مطالعه کنید. باید بجای 3 از 10 استفاده کنید و بجای محدوده $A$1:$A$9 محدوده مدنظرتان را درج کنید.
      موفق باشید.
  8. سلام اقای مهندس
    من فایل را خدمتتون ارسال کردم ، لطف می کنید راهنمایی کنید.
    ممنونم
    • پاسخ:

      سلام
      باید در فرمول بجای ۱۱ از ۲۳ استفاده کنید.
      موفق باشید.
  9. سلام و خسته نباشید
    من به اکسل خیلی اشنا نیستم. اما با این توضیحات بالا فقط سطر یک را درست میتونم در بیارم. بقیه سطرها به هم میریزه.نمیدونم مشکل از کجاست
    فایل:
    http://s9.picofile.com/file/8328748926/Book1.xlsx.html
  10. سلام. مشکل قبلی که خدمتتون فرستادم با یه سیستم دیگه حل شد . اما هر کاری میکنم باز هم یه جای کار میلنگه
    این ستون 253 تایی باید به 11 سطر 23 تایی که سلول اول کلمه "nomre" هست تبدیل بشه اما ملاحظه کنید چه طور میشه:

    http://s9.picofile.com/file/8328635500/Untitled.png
    • پاسخ:

      سلام
      مطمئن هستید فرمول ها را درست استفاده می کنید؟ اگر پس از بررسی مجدد مشکلتان حل نشد فایل خود ر ا برای من ارسال کنید.
  11. سلام و عرض احترام
    میخوام یک ستون 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
  12. سلام مدیر گرامی
    چگونه می توان یک ستون شامل نام و نام خانوادگی (با تعداد زیاد ) را به صورت دو ستون جداگانه تبدیل کرد
    • پاسخ:

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

      سلام
      مطلب بالا همین کار را آموزش داده است.
      موفق باشید.
  14. سلام
    من هشت هزار داده داشتم برای جابه جایی. با راهنمایی شما عرض دو ساعت انجام شد. خیلی خوب توضیح داده بودید. بی نهایت ممنونم. امیدوارم راهتونو به همین خوبی ادامه بدید.
  15. سلام، اگه بخوایم عکس این کار رو انجام بدم باید چیکار کنم، ینی جدولی باشه بخوام همه ی داده هارو بیارو تووی یک ستون ؟
    • پاسخ:

      سلام
      به این مطلب مراجعه کنید.
  16. خیلی خیلی عالی و کاربردی بود
    خدا خیر دنیا و آخرت رو بهتون بده
  17. سلام
    من میخوام زمانی که در یک سلول عدد 2 یا هر عدد دیگری وارد میکنم دو ستون اضافه گردد
    مثلا زمانی که در سلول b2 عدد 3 را وارد کردم بعد از ستون H سه ستون دیگر اضافه گردد
    باید چه فرمولی بنویسم؟
    جایی دیدم که اینکار شدنیه
    ممنون
    • پاسخ:

      سلام
      متاسفانه راهی سراغ ندارم.
  18. با سلام. ببخشید من یه سری داده توی اکسل دارم که فرم کلیشون به این شکله:
    pstS, phoS,b3728,JW3706 که من می خوام از محل "," علامت از هم جدا بشن و در چند ردیف قرار بگیرن. میشه خیلی ساده بگین چیکار کنم؟
    با تشکر
    • پاسخ:

      سلام
      با استفاده از ابزار Text to column می‌توانید آن‌ها را از هم جدا کنید (در این قسمت بجای تیک زدن گزینه Space از گزینه comma استفاده کنید).
      موفق باشید.
  19. سلام
    اطلاعات هر سلول من به صورت
    181,10-Mar-2016,08:03 می باشد و من میخوام اطلاعاتیکه با ,  جدا شدن رو به سلول های جدا از هم تبدیل کنم
    181
    10-Mar-2016 08:03
    ممنون میشم راهنماییم کنید.
    • پاسخ:

      سلام
      به دو روش می‌توان این کار را انجام داد:
      روش ۱- این قسمت (بجای فاصله " " از ویرگول "،" استفاده کنید) و
      روش ۲- این قسمت (بجای تیک زدن گزینه Space از گزینه comma استفاده کنید)
      موفق باشید.
  20. با سلام . من یک ستون با 1000 شماره دارم که برای چاپ کردن میخام تو چند ردیف پشت سر که توی یه برگ آچار فیکس بشن چاپ کنم. منتها هر کاری میکنم یه سطر میشن .چکار باید کرد ؟ با تشکر 
    • پاسخ:

      سلام
      یعنی یک ستون با ۱۰۰۰ ردیف دارید؟ منظورتان از اینکه یک سطر می‌شوند را متوجه نشدم!
  21. به فایل تکست که داده  باید از چپ به راست تو هر ردیف به ترتیب برداشته بشه و همه تو یک  ستون نوشته بشه چه کار باید بکنم مرسی.

    مثال :                                        1  2  3  4  5

                                                   8  12  9  0  6

    5

    4

    3

    2

    1

    6

    0

    9

    12

    8

    • پاسخ:

      سلام
      این داده‌ها را باید به اکسل منتقل کنید. سپس با مراجعه به این سایت می‌توانید از روش‌های گفته شده برای تبدیل دو ردیف به یک ستون در اکسل استفاده کنید.
      موفق باشید.
  22. چطوری باید 2 یا سه سر ستون را در فایل اکسل ثابت کنیم. از قسمت freez فقط یک سطر یا ستون میشود.
    • پاسخ:

      سلام
      برای اینکار زیر سطر دوم یعنی روی سطر سوم (سلول A3 یا هر سلولی در سطر سوم) کلیک کرده، سپس روی دکمه Freeze panes کلیک کنید.
      موفق باشید.
  23. سلام
    ممنونم از بابت مطالب فوق العاده تون
    من یه سوال داشتم
    اگه من یه سری از داده ها رو وارد یه ستون از اکسل بکنم بعد بخام یکی در میون سل خالی بشه چکار باید بکنم البته منظورم اینه که مثلا داده سل 1 باشه سل 2 خالی بشه و داده سل 2 بره داخل سل 3 به همین صورت تا آخرین داده
    • پاسخ:

      سلام
      به این مطلب مراجعه کنید.
      موفق باشید.
  24. سلام
    چطور میشه 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

      موفق باشید.

  25. سلام
    واقعا مرسی بابت وبلاگ ات .
    یه مشکلی که من هم دارم دستگاه داده های ازمایشمن و به این صورت در اکسل داده .چون داده ها زیاد اند نمیتونم با دست جداشون کنم .
    251.0000;0.0397
    255.0000;0.2425
    به نظر تون برای جدا کردن داده هاقبل و بعد از ; و نوشتن در یک ستون دیگر چی کار کنم.؟
    با تشکر
    • پاسخ:

      سلام
      از روش تجزیه استفاده کنید. در اینجا توضیح داده شده است. کافیست در مرحله ۴ بجای انتخاب گزینه space، گزینه other را انتخاب کنید و علامت ; را تایپ کنید.
      موفق باشید.

ارسال نظر

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

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

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

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

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

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

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

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