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

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

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

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

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

برای اینکار طبق مراحل زیر عمل کنید:

۱) در کنار داده‌ها به یک ستون خالی نیاز داریم. بطور مثال برای داد‌های بالا از ستون 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)

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

اگر برخلاف حالت ۱ مانند شکل زیر هدف این باشد که هر ردیف را به تعداد مشخص و متفاوت با سایر ردیف‌ها تکرار کنیم نمی‌توان از روش قبل استفاده کرد. تعداد تکرارهای ردیف‌های ستون 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 کلیک کنید تا نتیجه نهایی مشابه روش قبل ظاهر شود.

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

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

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

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

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

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