دنباله در اکسل — از صفر تا صد

دنباله‌ها (Sequence) نقش مهمی در محاسبات ریاضی و مهندسی دارند. ولی در این نوشتار قرار است با نوع دیگری از دنباله‌ها آشنا شویم که معمولا الگوی ریاضیاتی ساده‌ای داشته یا به صورت یک دنباله از اسامی ظاهر می‌شوند که دارای نظم خاصی نیز هستند. هنگامی که با جدول‌ها و لیست‌ها سر و کار داریم، وجود نظم در بین مقادیر متنی و حتی عددی، دارای اهمیت زیادی است. از طرفی، یکی از ابزارهای محبوب و مورد پسند برای کار روی لیست و جدول‌ها، اکسل (MS-Excel) است. به همین علت در این نوشتار با دنباله در اکسل و نحوه ایجاد سری‌های عددی، متنی و حتی دنباله‌های سفارشی خواهیم پرداخت به کمک مثال‌هایی شیوه ایجاد آن‌ها را مورد بررسی قرار خواهیم داد.

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

دنباله در اکسل

همانطور که در ابتدای متن مشاهده کردید، قرار است یک دنباله را در جدول اکسل، ایجاد و نمایش دهیم. البته به مانند اغلب فعالیت‌ها، این کار ممکن است به شکل و شیوه‌ها مختلفی اجرا شود. در نوشتار Flash Fill در اکسل --- راهنمای کاربردی، بعضی از دنباله یا سری‌ها را به توجه به رابطه‌ای که بین نقاط یک دنباله وجود داشت، مورد بررسی قرار دادیم.

فیلم آموزش اکسل – محاسبات ریاضی و ماتریسی با Microsoft Excel 2016 در فرادرس

کلیک کنید

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

دنباله متنی در اکسل

می‌دانید که معمولا کلمات و اسامی دارای ترتیب نیستند. ولی معمولا با اضافه کردن یک عدد در کنار یک اسم، می‌توان یک دنباله متنی (یا به شکل صحیح‌تر دنباله متنی-عددی) ایجاد کرد. برای ایجاد این دنباله در اکسل کافی است که یک متن نوشته و بلافاصله بعد از آن، عدد آغازین برای دنباله را وارد کنیم.

برای مثال اگر بخواهید دنباله‌ای از اسامی «کالای۱»، «کالای۲» و ... در ستون A ایجاد شود، کلمه «کالا» را نوشته و بعد از آن عدد ۱ را تایپ می‌کنیم. با عمل «کشیدن و رها کردن» (Drag and Drop) دکمه «پر کردن خودکار» (Fill Handle)، دنباله متنی ایجاد شده و سلول‌های بعدی را پر می‌کند. به این ترتیب یک دنباله متنی خواهید داشت. در تصویر 1، یک کاربرگ را مشاهده می‌کنید که دنباله متنی با مقادیر کالای۱ تا کالای20 در آن دیده می‌شود.

دنباله متنی با لیست سفارشی (Custom List) در اکسل

ولی گاهی اسامی دارای ترتیب خاصی هستند. برای مثال، اسامی ماه‌ها دارای نظم و ترتیب مشخص بوده که با دانستن نام هر ماه، می‌توانیم ماه قبل یا بعد از آن بشناسیم. به منظور ایجاد لیست یا دنباله‌ای از اسامی ماه‌ها (به کمک ترتیب مورد نظر) از «لیست سفارشی» (Custom List) استفاده می‌کنیم. در تصویر ۲، گام‌های مربوط به ایجاد دنباله‌ای از اسامی ماه‌ها که در  لیست سفارشی اضافه شده است را مشاهده می‌کنید.

فیلم آموزش رایگان اکسل Excel – محاسبات ریاضی و ماتریسی – ویژه ناشنوایان در فرادرس

کلیک کنید

در گام ۱، گزینه NEW LIST را از بخش Custom Lists انتخاب کرده و در گام دوم، مقادیر متنی را به ترتیب (از کوچک به بزرگ یا بزرگ به کوچک) در کادر List Entries، وارد کنید. با وارد کردن هر کلمه، کلید enter را بزنید تا به سطر بعدی وارد شوید. در گام سوم نیز پس از ثبت همه موارد با انتخاب دکمه Add، لیست سفارشی به لیست‌های موجود در اکسل اضافه خواهد شد.

نکته: برای دسترسی به پنجره لیست سفارشی (Custom List) در اکسل از فهرست File‌ گزینه Options را انتخاب کرده و از کادر سمت چپ با انتخاب گزینه Advanced، در بخش سمت راست پنجره، دکمه Custom List را پیدا کرده و کلیک کنید.

همانطور که مشاهده می‌کنید این لیست هم برای ایجاد دنباله و هم مرتب‌سازی در اکسل به کار می‌رود.

پس از معرفی لیست سفارشی، می‌توانید برای دنباله متنی از آن استفاده کنید. کافی است یکی از قلم‌های لیست را در یک سلول وارد کنید و به وسیله دستگیره پرکردن (Fill Handle)، سلول‌های مجاور را با مقادیر دیگر لیست پر کنید. البته به یاد دارید که عبارت‌های تعریف شده در لیست دارای ترتیب هستند در نتیجه از هر قلمی در لیست آغاز کنید، عبارت‌های بعدی آن در ادامه ظاهر خواهند شد.

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

با توجه به اینکه اسامی ماه‌های شمسی را وارد کرده‌ایم، با نوشتن مقدار «تیر» در سلول A1، اسامی ماه‌های دیگر سال را طبق ترتیب آن‌ها، به صورت یک دنباله در سلول‌های A2 تا A10 مطابق با تصویر ۴، ایجاد کرده‌ایم. به این موضوع نیز توجه داشته باشید که باید ماه در سلول اول به درستی و مطابق با لیست سفارشی وارد شود. در غیر اینصورت امکان نمایش مقادیر بعدی لیست وجود نخواهد داشت.

به این ترتیب بدون فرمول نویسی، توانستیم اقلام یک لیست متنی را ایجاد کنیم.

ایجاد دنباله در اکسل با فرمول نویسی

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

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

فرض کنید روزهای هفته را در یک ستون از کاربرگ درج کرده‌ایم. می‌خواهیم با نوشتن نام یک روز در سلول A1، به صورت متنی، دنباله در اکسل ایجاد کنیم. در اینجا از تابع Match و INDEX‌ استفاده خواهیم کرد. به منظور آشنایی بیشتر با نحوه کار این دو تابع بهتر است نوشتار دیگری از مجله فرادرس با عنوان توابع INDEX و MATCH در اکسل برای جست‌وجوی سریعتر را نیز مطالعه کنید، هر چند به طور مقدماتی در اینجا هم این دو تابع را معرفی خواهیم کرد.

تابع Match در اکسل

این تابع، مقداری که به عنوان پارامتر به آن داده شده را در یک لیست جستجو کرده و مکان آن (مثلا ردیف در لیست) را نمایش می‌دهد. شکل دستوری این تابع به صورت زیر است.

MATCH(lookup_value, lookup_array, [match_type])

همانطور که مشخص است، پارامترهای اول و دوم این تابع اجباری بوده و پارامتر سوم آن اختیاری است زیرا در علامت [ ] نوشته شده. در جدول زیر نحوه عملکرد هر یک از این پارامترها مشخص و معرفی شده است.

جدول ۱: پارامترهای تابع Match برای ایجاد دنباله در اکسل

نکته: اگر مقدار مورد جستجو (Lookup_value) در لیست مورد نظر (Lookup_array) وجود نداشته باشد، اکسل فرض می‌کند که لیست شما به صورت صعودی مرتب شده و مکان نزدیک‌ترین گزینه را به عنوان پاسخ نشان می‌دهد اگر مقدار ۰ را به عنوان پارامتر سوم وارد کنید، فقط مطابقت با مورد جستجو انجام شده و در صورت ناموجود بودن آن در لیست پیغام خطای !N/A# ظاهر خواهد شد. اگر لیست به صورت نزولی مرتب شده باشد، با وارد کردن مقدار ۱- برای پارامتر سوم، نزدیک‌ترین مقدار (کوچکترین مقداری که از Lookup_value بزرگتر است) براساس مورد جستجو، نتیجه اجرای این تابع خواهد بود.

کاربرگ زیر را در نظر بگیرید. اسامی روز‌های هفته به ترتیب در ستون C‌،‌ نوشته شده‌اند. می‌خواهیم در سلول B1، مکان یا موقعیت مقداری که در سلول A1 نوشته شده را در ستون C، مشخص کنیم.

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

تابع Index در اکسل

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

INDEX(array, row_num, [column_num])

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

جدول ۲: پارامترهای تابع Index برای ایجاد دنباله در اکسل

حال با ترکیب دو تابع Match و Index، مقداری که در سلول A1 نوشته شده را جستجو کرده و موقعیتش را محاسبه می‌کنیم (بخش تابع Match)، سپس مقداری از لیست که در سطر بعدی آن موقعیت وجود دارد را نمایش خواهیم داد (بخش Index). فرمول به صورت زیر نوشته خواهد شد. توجه داشته باشید که بعضی از پارامترها به صورت آدرس مطلق (Absolute Reference) نوشته شده‌اند.

=INDEX($C$1:$C$7,MATCH(A1,$C$1:$C$7,0)+1,1)

همانطور که مشاهده می‌کنید، مکان مورد جستجو به صورت آدرس یا مرجع مطلق است. موقعیت مقدار جستجو شده (Lookup_value) نیز با عدد ۱ جمع شده تا سطر بعدی از لیست را مشخص کند.

به کاربرگی که در تصویر ۶ قرار گرفته است، دقت کنید. همانطور که مشاهده می‌کنید، با توجه به قرارگیری مقدار «یکشنبه» در سلول A1، با کپی کردن فرمول بالا در سلول‌های بعدی، اسامی روزهای دیگر هفته به ترتیب ظاهر خواهد شد.

نکته: اگر بخواهید فقط روزهای فرد ایام هفته در لیست ظاهر شود، کافی است موقعیت روز یکشنبه در لیست را با عدد ۲ جمع کنید. همین کار را با ذکر مقدار «شنبه» به عنوان اولین روز هفته و ایجاد دنباله روزهای زوج نیز می‌توانید انجام دهید. کاربرگی که در تصویر ۸ قرار گرفته برای نمایش زورهای زوج ایجاد شده است. این امر یکی از مزیت‌های فرمول نویسی برای ایجاد دنباله در اکسل محسوب می‌شود.

توجه دارید که با تمام شدن قلم‌های موجود در لیست، با پیغام خطای !REF# مواجه خواهید شد. البته به کمک تابع IF یا ISERROR می‌توان از نمایش این خطا جلوگیری کرد.

دنباله عددی در اکسل

در بسیاری از موارد برای ایجاد شماره ردیف از دنباله عددی استفاده می‌کنیم. برای مثال می‌خواهیم اعداد ۱ تا ۱۰۰ را در ستون A قرار داده و در ستون دیگر اسامی کالاها یا اسناد حسابداری را ثبت کنیم. از آنجایی که نوشتن مقدار ۱ تا ۱۰۰ در یک ستون زمان زیاد برده و ممکن است مقداری هم از قلم بیافتد، استفاده از ثبت دنباله‌ها در اکسل می‌تواند راه‌کار مطمئنی باشد. ابتدا بدون فرمول نویسی ایجاد دنباله‌های عددی را معرفی کرده، سپس با استفاده از توابع و فرمول‌هایی که در اکسل وجود دارد، این دنباله در اکسل را خواهیم ساخت.

فیلم آموزش رسم نمودار در اکسل – طراحی انواع نمودار و تنظیمات در فرادرس

کلیک کنید

دنباله عددی در اکسل بدون فرمول نویسی

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

• گام اول: مقدار ۱ و ۲ را به ترتیب در سلول‌های A1 و A2 وارد کنید.
• گام دوم: هر دو سلول A1 و A2 را انتخاب کنید.
• گام سوم: با استفاده از دکمه پر کردن خودکار (Fill Handle) که با انتخاب هر دو سلول ظاهر شده است، سلول‌های پایینی را تا سلول A10 پر کنید.

با طی کردن مراحل بالا، شماره ردیف‌ها آماده خواهند شد.

نکته: اگر می‌خواهید شماره‌ها به صورت اعداد زوج باشند، باید در سلول A1 و A2 به ترتیب ۲ و ۴ را وارد کنید، سپس گام‌های بعدی را بردارید.

شیوه دیگر برای ایجاد لیست عددی، بخصوص زمانی که تعداد مقادیر لیست زیاد باشند، استفاده از دستور series از برگه Home و بخش Editing و گزینه Fill است.

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

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

جدول 3: پارامترهای مورد استفاده برای ایجاد دنباله در اکسل در پنجره Series

برای مثال اگر بخواهیم تصاعد هندسی با قدر نسبت برابر با ۲ ایجاد کرده و تا مقدار ۱۰۰0 دنباله ادامه یابد، تنظیمات را مطابق با تصویر 10 انجام می‌دهیم. همچنین در این تصویر خروجی را مشاهده می‌کنید.

فرمول نویسی دنباله عددی در اکسل

یکی از راه‌های دیگر برای ایجاد دنباله در اکسل به صورت عددی، فرمول نویسی است. البته این کار بسیار ساده است. کافی است مقدار اولیه دنباله را در یک سلول نوشته و ضابطه ریاضیاتی دنباله را در سلول بعدی وارد کنید، بطوریکه به صورت یک سلول وابسته بر حسب پیش‌نیاز سلول اولیه نوشته شود. برای مثال اگر بخواهیم شماره ردیف از ۱ تا ۱۰ را بنویسیم، کافی است مقدار ۱ را در سلول A1 و فرمول زیر را در سلول A2 وارد کرده و فرمول را تا سلول A10 کپی کنیم.

$$\large A2: =A1+1$$

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

ROW([reference])

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

به فرمول نوشته شده در سلول A3 که در نوار فرمول دیده می‌شود، دقت کنید. همانطور که می‌بینید از شماره ردیف سلول A1 در این فرمول استفاده شده است، زیرا این سلول دارای ردیف ۱ است و چون می‌خواهیم در «جدول اقلام فروشی» نیز شماره ردیف از ۱ آغاز شود، از آدرس A1 در فرمول استفاده کرده و فرمول را در سطرهای بعدی کپی کرده‌ایم.

ایجاد دنباله در اکسل با تابع Sequence

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

=SEQUENCE(rows,[columns],[start],[step])

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

نکته: توجه داشته باشید که این تابع به صورت برداری عمل می‌کند و باید به شکل تابع برداری آن را ثبت کنید. یعنی هنگام ثبت از کلیدهای ترکیبی Ctrl+Shift+Enter باید استفاده کرد. برای اطلاعات بیشتر در این زمینه بهتر است از نحوه نوشتن توابع برداری در اکسل مطلع شوید.

جدول 4: پارامترهای تابع sequence برای ایجاد دنباله در اکسل

نکته: متاسفانه این تابع فقط در نسخه اکسل ۳۶۵ (Office 365) که از طریق اینترنت در دسترس قرار می‌گیرد، قابل استفاده است و در نسخه‌های جدید اکسل حتی اکسل ۲۰۱۹ نیز وجود ندارد.

دنباله سفارشی در اکسل

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

فیلم آموزش اکسل Microsoft Excel 2019 – مقدماتی در فرادرس

کلیک کنید

برای مثل فرض کنید که می‌خواهیم مقادیر ۱،۲،۳،۴ را ۱۰ بار پشت سر هم تکرار کنیم. یا شاید لازم باشد که مقادیر از ۱ تا ۱۰ را سه بار به صورت تکراری در ستون یک کاربرگ نمایش دهیم. در ادامه این کار را البته به کمک فرمول نویسی انجام می‌دهیم ولی در این بین از یک تابع ریاضیاتی در اکسل کمک می‌گیریم که شاید با دنباله‌ها ارتباطی نداشته باشد.

کاربرگ زیر را در نظر بگیرید. همانطور که مشاهده می‌کنید، مقادیر ۱ تا ۴ به صورت تکراری در این ستون از کاربرگ قرار گرفته‌اند.

می‌خواهیم به کمک یک فرمول و تعیین پارامترهای آن لیستی به صورت تکراری از یک دنباله در اکسل ایجاد کنیم که به شکل عددی باشد. فرض کنید که مقدار اولیه در سلول A1 نوشته شده و مقدار انتهایی نیز در دنباله در سلول B1 قرار گرفته است. در اینجا از تابع MOD استفاده می‌کنیم که باقی‌مانده یک تقسیم را نمایش می‌دهد. برای مثال $$MOD(10,3) = 1$$ زیرا خارج قسمت صحیح حاصل تقسیم ۱۰ بر ۳ برابر با ۳ و باقی‌مانده ۱ است. شکل این تابع و پارامترهای آن به صورت زیر است.

MOD(number, divisor)

پارامتر number همان مقسوم و divisor، نیز مقسوم علیه هستند. این دو پارامتر اجباری بوده و برای محاسبه باقی‌مانده تقسیم ضروری هستند.

از آنجایی که می‌خواهیم دنباله از ۱ شروع شود، بهتر است هر بار یک واحد به سلول اول (مثلا A1) که مقدار ۱ در آن نوشته شده اضافه شود تا اینکه عدد حاصل شده مضربی از چهار (یعنی مقداری که در سلول B1 نوشته شده) باشد، آنگاه باقی‌مانده صفر شده و دنباله از اول تکرار می‌شود. فرمول زیر را برای سلول A2 در نظر بگیرید.

$$ \large A2 = MOD(A1,$B$1) + 1 $$

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

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

خلاصه و جمع‌بندی

در این نوشتار با روش‌های مختلف برای ایجاد دنباله در اکسل به صورت متنی یا عددی آشنا شدیم. همچنین ایجاد یک دنباله عدد به صورت سفارشی به همراه فرمول‌های مورد نظر نیز ارائه شد که شاید بتوان آن را یک مزیت برای این متن نسبت به متون آموزشی دیگر محسوب کرد. به این ترتیب کار ورود داده‌ها در اکسل برای کاربران ساده‌تر شده و می‌توانند جدول‌هایی که مربوط به سری‌ها هستند را با سرعت و دقت زیاد در اکسل ثبت و ذخیره کنند. متاسفانه شرکت مایکروسافت بعضی از توابع اکسل (مانند تابع Sequence) که برای ایجاد دنباله‌ها به کار می‌روند را فقط در نسخه اینترنتی آفیس یعنی آفیس ۳۶۵ (365 office) قرار داده است و حتی در ویرایش ۲۰۱۹ اکسل نیز قابل استفاده و مشاهده نیست. ولی امیدواریم که در نسخه‌های بعدی اکسل، بتوانیم از این گونه توابع در نسخه‌های رومیزی آفیس (Desktop Office) بهره ببریم.