جستجو

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

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

خلاصه آمار

تبليغات

پربحث ترين ها

۱

تکرار ردیف‌های یک ستون به تعداد دلخواه در اکسل

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

تکرار ردیف‌های یک ستون به تعداد دلخواه در اکسل

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

حالت ۱) تکرار یکسان برای تمام ردیف‌ها

مثال زیر را در نظر بگیرد. قصد داریم تمام ردیف‌ها را ۳ مرتبه تکرار کنیم البته همانطور که اشاره شد تکرار‌های هر ردیف زیر همان ردیف باشد و ترتیب ردیف‌ها هم تغییر نکند.

روش ۱- با استفاده از ابزار مرتب کردن

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

۱) در کنار داده‌ها به یک ستون خالی نیاز داریم. بطور مثال برای داد‌های بالا از ستون B استفاده می‌کنیم.

۲) در سلول B2 عدد ۱ و در سلول B3 عدد ۲ را درج می‌کنیم.

۳) اعداد ۱ و ۲ را انتخاب می‌کنیم و روی علامت Fill handle دو بار کلیک می‌کنیم. 

اکسل بطور خودکار سلول‌های ستون B را پر می‌کند. بطور مثال از ۱ تا ۵.

۴) سپس از مجموعه سلول‌ها (از A2 تا B6) کپی می‌گیریم و به تعداد یکی کمتر از عددی که می‌خواهیم ردیف‌ها تکرار شوند پیست می‌کنیم. چون هدف سه بار تکرار است پس ۲ بار این اطلاعات را در پایین اطلاعات اصلی پیست می‌کنیم.

۵) اکنون سلول‌های ستون B (از B2 تا B16) را انتخاب می‌کنیم و از تب Data روی دکمه Sort A to Z کلیک می‌کنیم.

۶) پس از کلیک پیغام زیر ظاهر می‌شود گزینه Expand the selection را تیک می‌زنیم.

۷) در پایان روی دکمه Sort کلیک کنیم. نتیجه زیر حاصل می‌شود.

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

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

در این روش از تابع Index که قبلا در این مطلب معرفی شده است استفاده می‌کنیم.

=INDEX(محدوده,INT((ROW(A1)-1)/n)+1)

در فرمول فوق محدوده مورد نظر A2:A6 است و n نیز تعداد تکرار را نشان می‌دهد. پس از جایگزینی محدوده و n در فرمول زیر، آنرا را در سلول B2 تایپ کنید. چون تعداد سلول‌ها ۵ عدد است و باید ۳ بار تکرار شوند پس مجموعا ۱۵ عدد می‌شوند بنابراین فرمول را از سلول B2 تا B16 اعمال کنید.

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

منبع: stackoverflow.com


حالت ۲) تکرار متفاوت برای هر ردیف‌

اگر برخلاف حالت ۱ مانند شکل زیر هدف این باشد که هر ردیف را به تعداد مشخص و متفاوت با سایر ردیف‌ها تکرار کنیم نمی‌توان از روش قبل استفاده کرد. تعداد تکرارهای ردیف‌های ستون A در ستون B مشخص شده است.

برای این حالت کار کمی پیچیده‌تر است. ۲ روش برای حالت ۲ وجود دارد:

روش ۱- فرمول نویسی

مراحل زیر را انجام دهید:

۱- سمت چپ ستون A یک ستون جدید ایجاد کنید و در سلول A2 عدد ۱ را تایپ کنید.

۲- سپس در سلول A3 فرمول زیر را تایپ کنید و با استفاده از کشیدن Fill handle این فرمول را تا سلول A7 اعمال کنید.

=A2 + C2

۳- در ادامه در سلول D2 عدد ۱ را وارد کنید و با کشیدن از Fill handle تا عدد ۱۸ پیش بروید. عدد ۱۸ مجموع تکرارهای ردیف‌ها هست که در ستون C درج شده است.

۴- سپس در سلول E2 فرمول زیر را تایپ کنید و با کشیدن Fill handle در سلول‌های پایین‌تر اعمال کنید تا نتیجه زیر حاصل شود:

=VLOOKUP(D2,$A$1:$B$7,2)

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

روش ۲- ماکرونویسی

  

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

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

Sub CopyData()
'pctarfand.ir & tarfandha.blog.ir'
Dim Rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
Set OutRng = OutRng.Range("A1")
For Each Rng In InputRng.Rows
    xValue = Rng.Range("A1").Value
    xNum = Rng.Range("B1").Value
    OutRng.Resize(xNum, 1).Value = xValue
    Set OutRng = OutRng.Offset(xNum, 0)
Next
End Sub

۲) روی دکمه  کلیک کنید یا کلید F5 را فشار دهید. با انجام اینکار پنجره زیر ظاهر می‌شود. سپس محدوده داد‌ه‌ها و همچنین تعداد تکرارهای مدنظرتان را انتخاب کنید.

۳) روی ok کلیک کنید تا پنجره جدیدی ظاهر شود. سلولی که می‌خواهید نتیجه نهایی آنجا درج شود انتخاب کنید مثلا سلول F1.

۴) در پایان روی ok کلیک کنید تا نتیجه نهایی مشابه روش قبل ظاهر شود.

 

منبع: www.extendoffice.com

از کد ماکروی ارائه شده در این سایت نیز می‌توانید استفاده کنید.


حالت ۳) پر کردن سلول‌های خالی با مقادیر بالای آن

برخی از اوقات ممکن است تعداد تکرار سلول‌ها بر اساس تعداد سلول‌های خالی زیر هر سلول باشد.

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

۱- ابتدا محدوده‌ای که قصد دارید سلول‌های خالی آن پر شوند را انتخاب کنید.
۲- اکنون کلید F5 را فشار دهید تا پنجره Go To نمایان شود. سپس بر روی دکمه Special کلیک کنید. البته می‌توانید از تب Home روی دکمه Find & Select کلید کرده و روی گزینه
Go To Special کلیک کنید.

۳- در پنجره باز شده گزینه Blanks را انتخاب کرده و بر روی دکمه OK کلیک کنید.

خواهید دید که با این کار تمامی سلو‌ل‌های خالی به حالت انتخاب درمی‌آیند.

۴- اکنون بدون هیچ کلیک اضافه‌ای عبارت زیر را تایپ کنید. بصورت خودکار این عبارت در اولین سلول خالی یعنی A3 تایپ می‌شود.

=A2

۵- در پایان کلیدهای Ctrl+Enter را همزمان فشار دهید تا نتیجه زیر حاصل شود.

۶- برای اینکه نتایج با جابجایی تغییر نکنند ستون مربوطه را انتخاب و کپی کنید سپس در مکان دیگر بجای پیست معمولی از گزینه Paste Values استفاده کنید.

 

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

نظرات (۱)

ارسال نظر

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

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

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

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

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

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

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

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