شماره زن اتوماتیک در اکسل — آموزش شماره زنی خودکار در اکسل + فیلم

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

ممکن است بعضی از موضوعاتی که در این متن به آن اشاره خواهیم کرد، در مطالب دیگر مجله فرادرس مانند دنباله در اکسل — از صفر تا صد و اکسل چیست ؟ — کاربرد، مزایا، معایب و نحوه یادگیری به کار رفته باشد، بنابراین اگر با اکسل آشنایی ندارید، بهتر است به عنوان مقدمه آن‌ها را مطالعه کنید. همچنین خواندن نوشتارهای توابع جدید اکسل ۲۰۱۹ — راهنمای کاربردی و مجموعه آموزش‌ های قابلیت های جدید آفیس ۲۰۱۹ نیز خالی از لطف نیست.

شماره زن اتوماتیک در اکسل

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

فیلم مجموعه آموزش اکسل – مقدماتی تا پیشرفته در فرادرس

کلیک کنید

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

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

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

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

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

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

کلیک کنید

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

شماره زن اتوماتیک با تابع IF

به طور معمول زمانی که نیاز است مقدار یک سلول را براساس شرط تعیین کنیم، از تابع IF کمک می‌گیریم. در اینجا هم اولین تکنیک فرمولی نویسی برای شماره زن اتوماتیک استفاده از تابع IF است. یکی از محدودیت‌هایی که دراین روش داریم، آن است که باید حتما برای اولین قلم، شماره ردیف را درج کنیم. به این منظور در سلول A2، مقدار 1 را نوشته‌ایم. پس باید فرمول را از سلول بعدی آغاز کنیم. فرمول نوشته شده در سلول A3 به صورت زیر است.

=IF(B3<>"",A2+1,"")

با کپی کردن این فرمول برای سطرهای بعدی از ستون A، (مثلا تا سطر ۱۱ام کاربرگ) شماره گذاری خودکار در اکسل صورت می‌گیرد. از طرفی اگر لیست اقلام را در سلول‌های ستون B نیز ادامه دهیم، شماره‌دار شده و یک لیست ترتیبی حاصل می‌شود.

نکته: منظور از ""، عبارت نال (NULL) یا خالی است. به این معنی که تابع IF هیچ مقداری را در صورت ناصحیح بودن شرط، برنمی‌گرداند.

به تصویر زیر توجه کنید. شماره‌گذاری در ستون A از ردیف سوم به بعد، خودکار صورت گرفته.

نکته: هر چند این روش ساده به نظر می‌رسد، ولی حذف کردن یک سطر در بین لیست، فرمول را دچار خطا کرده و به علت از بین رفتن آدرس پیش‌نیاز، در سلول مربوط به ستون A، پیغام خطای !REF# ظاهر می‌شود. از طرفی اضافه کردن یک سطر در بین لیست، باید به همراه کپی کردن فرمول در آن سلول هم باشد تا شماره‌گذاری به درستی صورت گیرد.

شماره زن اتوماتیک با تابع COUNTA

احتمالا با تابع «شمارش» (COUNT) در اکسل آشنایی دارید. تابع COUNTA نیز به مانند همین تابع، عمل شمارش را انجام می‌دهد و در یک ناحیه، تعداد سلول‌های عددی و متنی را شمارش می‌کند، در حالیکه تابع COUNT فقط سلول‌هایی را می‌شمارد که عددی باشند.

از طرفی آدرس مرجع نسبی و مطلق را هم باید بشناسید تا نحوه ایجاد شماره زن اتوماتیک را بهتر درک کنید. به یاد دارید که مثلا به کار بردن آدرس $$\text{\$B\$2}$$ در یک فرمول یا تابع (سلول وابسته) نشانگر آن است که با کپی شدن سلول وابسته (فرمول)، این آدرس تغییری نخواهد کرد و در محل جدید نیز به سلول B2 اشاره خواهد داشت.

با این مقدمه، فرمول زیر را در سلول A2 می‌نویسیم.

=COUNTA($B$2:B2)

فرمول گفته شده را در سلول‌های A3 تا مثلا A11 کپی می‌کنیم. نتیجه مطابق با تصویر زیر خواهد بود.

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

بهتر است برای رفع این مشکل از یک تابع IF استفاده کنیم و در صورتی که سلول مجاور در ستون B، مقداری داشت، فرمول به کار رفته و در غیر اینصورت، مقدار خالی جایگزین شود. در نتیجه فرمول جایگزین به صورت زیر خواهد بود.

=IF(B2<>"",COUNTA($B$2:B2),"")

واضح است که علامت <> نشانگر عملگر مقایسه‌ای «مخالف» است. به این ترتیب اگر سلول B2 و سلول‌های متناظر با آن، خالی باشند، شماره زن متوقف شده و مقدار خالی (NULL) برای شماره سطر مورد نظر، نمایان می‌شود. ولی در صورت وجود یک عبارت، شماره گذاری صورت گرفته و براساس تعداد سطرهای قبلی و جاری، شماره زن خودکار عمل می‌کند.

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

شماره زن اتوماتیک با تابع ISBLANK و COUNTA

این روش هم به مانند تکنیک قبلی است و همان مزایای را دارد، فقط به جای استفاده از تابع IF، از تابع ISBLANK و NOT استفاده کرده‌ایم. فرمولی که در سلول A2 نوشته می‌شود، به صورت زیر است.

=NOT(ISBLANK(B2))*COUNTA($B$2:B2)

نکته: تابع NOT یا نقیض، یک تابع منطقی است که ارزش هر گزاره منطقی را به صورت نقیض آن در می‌آورد. از طرفی تابع ISBLANK، خالی بودن یک آدرس را مورد بررسی قرار می‌دهد. در صورتی که پارامتر آن مقداری نداشته باشد، مقدار TRUE و در غیر اینصورت مقدار FALSE را برمی‌گرداند. به این موضوع نیز توجه داشته باشید که هنگام ضرب یک عدد در مقادیر منطقی TRUE یا FALSE باعث می‌شود که مقدار منطقی، صفر یا یک در نظر گرفته شوند.

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

شماره زن اتوماتیک با تابع ROW و IF

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

=IF(B2="","",ROW()-1)

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

نکته: چون می‌خواهیم سطر دوم کاربرگ، شماره ردیف ۱ داشته باشد، مقدار ۱ را از تابع ROW کم کرده‌ایم تا سطر دوم، با شماره ۱ مشخص شود. نتیجه را در تصویر زیر خواهید دید.

نکته: در این روش نیز در صورت حذف یک سطر، به طور خودکار شماره‌ها به روز می‌شوند ولی اگر سطر جدیدی اضافه کنیم، باید خودمان فرمول‌ را کپی کرده تا شماره ردیف ایجاد شود.

شماره زن اتوماتیک در اکسل با VBA

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

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

کلیک کنید

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

1' Action in worksheet change
2Private Sub Worksheet_Change(ByVal Target As Range)
3    Dim cell As Range
4    ' Do nothing when change in other area except Column B
5    If Not Intersect(Range("B:B"), Target) Is Nothing Then
6        ' Increasing numbering when something in B
7        For Each cell In Intersect(Range("B:B"), Target).Cells
8            If cell.Value <> "" And Range("A" & cell.Row).Value = "" Then
9                Range("A" & cell.Row).Value = Application.Max(Range("A:A")) + 1
10            End If
11        Next
12    End If
13End Sub
14' Save it as a code, Right Click on tab (sheet1) and choose, view code, then copy and paste above code and return to sheet
15

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

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

Range("A" & cell.Row).Value = Application.Max(Range("A:A")) + 1

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

بهتر است به جای تابع Max از تابع زیر (NextNumber) استفاده کنید تا این مشکل برطرف شود. به این ترتیب یک شماره زن اتوماتیک در اکسل ایجاد خواهید کرد.

1Public Function NextNumber(SequenceName As String)
2Dim n As Name, v
3On Error Resume Next
4Set n = ThisWorkbook.Names(SequenceName)
5On Error GoTo 0
6
7If n Is Nothing Then
8'create the name if it doesn't exist
9ThisWorkbook.Names.Add SequenceName, RefersTo:=2
10v = 1
11Else
12'increment the current value
13v = Replace(n.RefersTo, "=", "")
14n.RefersTo = v + 1
15End If
16NextNumber = v
17End Function

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

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

کلیک کنید

به منظور دسترسی به کد و توابع مورد استفاده در این متن، فایل کارپوشه کاری با نام excel autonumbering.xlsm در اختیارتان قرار می‌گیرد. فقط توجه داشته باشید که هنگام باز کردن این فایل، گزینه Enable Macro را فعال کنید تا کدهای VBA قابل اجرا باشند. برای دریافت این فایل اینجا کلیک کنید، سپس فایل را از حالت فشرده خارج و در اکسل بارگذاری کنید.

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

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