شماره گذاری ردیف ها در اکسل به صورت خودکار (اتوماتیک)
هنگام کار با اکسل، فرآیندهای کوچکی وجود دارد که باید اغلب انجام شود. دانستن “راه درست” می‌تواند تا حد زیادی در زمان شما صرفه جویی کند. یکی از این وظایف ساده (اما اغلب مورد نیاز) شماره گذاری ردیف‌های یک مجموعه داده در اکسل است (که شماره سریال در مجموعه داده نیز نامیده می‌شود).
حالا اگر فکر می‌کنید که یکی از راه‌ها این است که به سادگی این شماره سریال را به صورت دستی وارد کنید، خوب – حق با شماست!
اما این بهترین راه برای انجام آن نیست.
تصور کنید که صدها یا هزاران ردیف دارید که برای آنها باید شماره ردیف را وارد کنید. خسته کننده و کاملا غیر ضروری خواهد بود.
راه‌های زیادی برای شماره گذاری ردیف‌ها در اکسل وجود دارد و در این آموزش قصد دارم برخی از روش هایی را که توصیه می‌کنم و اغلب استفاده می‌کنم به اشتراک بگذارم.
این آموزش شامل:
نحوه شماره گذاری ردیف‌ها در اکسل
بهترین راه برای شماره گذاری ردیف‌ها در اکسل به نوع مجموعه داده‌ای که دارید بستگی دارد.
به عنوان مثال، ممکن است یک مجموعه داده پیوسته داشته باشید که از ردیف 1 شروع می‌شود، یا مجموعه داده‌ای که از یک ردیف دیگر شروع می‌شود. یا ممکن است مجموعه داده‌ای داشته باشید که چند ردیف خالی در آن وجود دارد و فقط می‌خواهید ردیف هایی را که پر شده‌اند شماره گذاری کنید.
شما می‌توانید یکی از روش‌هایی را که بر اساس مجموعه داده شما کار می‌کند، انتخاب کنید.
1. با استفاده از Fill Handle
Fill Handle یک الگو را از چند سلول پر شده شناسایی می‌کند و به راحتی می‌تواند برای پر کردن سریع کل ستون استفاده شود.
در اینجا مراحل شماره گذاری سریع ردیف‌ها با استفاده از دستگیره پر آمده است:
- در سلول A2 عدد 1 و در سلول A3 عدد 2 را وارد کنید.
- هر دو سلول (A2 و A3) را انتخاب کنید.
- توجه داشته باشید که یک مربع کوچک در پایین سمت راست انتخاب وجود دارد.
- مکان نما را روی این مربع نگه دارید، متوجه خواهید شد که مکان نما به نماد مثبت تبدیل می‌شود.
- بر روی مربع Fill Handle دوبار کلیک کنید (در حالی که مکان نما در فرم نماد بعلاوه است) و به طور خودکار تمام سلول‌ها را تا پایان مجموعه داده پر می‌کند.
توجه داشته باشید که Fill Handle به طور خودکار الگو را شناسایی می‌کند و سلول‌های باقی مانده را با آن الگو پر می‌کند. در این مورد، الگوی این بود که اعداد 1 افزایش می‌یافتند.
در صورتی که یک ردیف خالی در مجموعه داده دارید، دستگیره پرکردن (Fill Handle) فقط تا آخرین ردیف غیر خالی به هم پیوسته کار می‌کند.
همچنین توجه داشته باشید که در صورت نداشتن داده در ستون مجاور، دوبار کلیک کردن روی Fill Handle کار نخواهد کرد. با این حال، می‌توانید مکان نما را روی Fill Handle قرار دهید، کلید سمت راست ماوس را نگه دارید و به پایین بکشید. سلول‌های تحت پوشش با کشیدن مکان نما را پر می‌کند.
2. با استفاده از Fill Series
در حالی که Fill Handle راهی سریع برای شماره گذاری ردیف‌ها در اکسل است، سری Fill به شما کنترل بسیار بیشتری بر نحوه وارد کردن اعداد می‌دهد.
فرض کنید شما یک مجموعه داده مانند شکل زیر دارید:
در اینجا مراحل استفاده از Fill Series برای شماره گذاری ردیف‌ها در اکسل آمده است:
- 1 را در سلول A2 وارد کنید.
- به تب Home بروید.
- در Editing Group، روی کادر کشویی Fill کلیک کنید.
- از منوی کشویی، “Series…” را انتخاب کنید.
- در کادر محاوره‌ای «Series»، «Columns» را در گزینه‌های «Series in» انتخاب کنید.
- مقدار Stop را مشخص کنید. در این صورت، چون ما 26 رکورد داریم، می‌توانیم 26 را وارد کنیم. اگر مقداری وارد نکنید، Fill Series کار نخواهد کرد.
- روی OK کلیک کنید.
با این کار بلافاصله ردیف‌ها از 1 تا 26 شماره گذاری می‌شود.
هنگامی که با وارد کردن شماره ردیف‌ها شروع می‌کنید، استفاده از “سری پر کردن” می‌تواند مفید باشد. برخلاف Fill Handle، نیازی به پر کردن ستون‌های مجاور از قبل ندارد.
حتی اگر چیزی در برگه ندارید، سری Fill همچنان کار می‌کند.
توجه: در صورتی که ردیف‌های خالی در وسط مجموعه داده داشته باشید، Fill Series همچنان عدد آن ردیف را پر می‌کند.
3. با استفاده از تابع ROW
همچنین می‌توانید از توابع اکسل برای شماره گذاری ردیف‌ها در اکسل استفاده کنید.
در روش‌های Fill Handle و Fill Series در بالا، شماره ردیف درج شده یک مقدار ثابت است. این بدان معناست که اگر سطر را جابه‌جا کنید (یا آن را در جای دیگری از مجموعه داده برش دهید و بچسبانید)، شماره‌گذاری ردیف بر این اساس تغییر نخواهد کرد.
این نقص را می‌توان با استفاده از فرمول‌های موجود در اکسل برطرف کرد.
می توانید از تابع ROW برای شماره گذاری ردیف در اکسل استفاده کنید.
برای به دست آوردن شماره گذاری ردیف با استفاده از تابع ROW، فرمول زیر را در خانه اول وارد کنید و برای تمام سلول‌های دیگر کپی کنید:
=ROW()-1
تابع () ROW شماره ردیف سطر فعلی را می‌دهد. بنابراین من از ردیف دوم به بعد 1 را از آن کم کردم. اگر داده‌های شما از ردیف 5 شروع می‌شود، باید از فرمول ROW()-4 استفاده کنید.
بهترین بخش در مورد استفاده از تابع ROW این است که اگر یک ردیف از مجموعه داده خود را حذف کنید، شماره گذاری‌ها را خراب نمی‌کند.
از آنجایی که تابع ROW به هیچ سلولی اشاره نمی‌کند، به طور خودکار (یا باید بگویم AutoMagically) تنظیم می‌شود تا شماره ردیف صحیح را به شما بدهد. چیزی که در زیر نشان داده شده است:
توجه داشته باشید که به محض حذف یک ردیف، شماره ردیف‌ها به طور خودکار به روز می‌شوند.
باز هم، این هیچ رکورد خالی در مجموعه داده را در نظر نمی‌گیرد. اگر ردیف‌های خالی داشته باشید، همچنان شماره ردیف را نشان می‌دهد.
می‌توانید از فرمول زیر برای مخفی کردن شماره ردیف برای ردیف‌های خالی استفاده کنید، اما باز هم شماره ردیف‌ها را تنظیم نمی‌کند (به این ترتیب که شماره ردیف بعدی به ردیف پر شده بعدی اختصاص می‌یابد).
IF(ISBLANK(B2)،"""ROW()-1)
4. با استفاده از تابع COUNTA
اگر می‌خواهید ردیف‌ها را طوری شماره‌گذاری کنید که فقط ردیف‌هایی که پر شده‌اند شماره سریال بگیرند، این روش بهترین راه است.
از تابع COUNTA استفاده می‌کند که تعداد سلول‌های یک محدوده خالی را می‌شمارد.
فرض کنید شما یک مجموعه داده مانند شکل زیر دارید:
توجه داشته باشید که ردیف‌های خالی در مجموعه داده نشان داده شده در بالا وجود دارد.
در اینجا فرمولی وجود دارد که ردیف‌ها را بدون شماره گذاری ردیف‌های خالی شماره گذاری می‌کند.
=IF(ISBLANK(B2)،"""،COUNTA($B$2:B2))
تابع IF بررسی می‌کند که آیا سلول مجاور در ستون B خالی است یا خیر. اگر خالی باشد، یک خالی برمی‌گرداند، اما اگر اینطور نباشد، تعداد تمام سلول‌های پر شده را تا آن سلول برمی‌گرداند.
5. استفاده از SUBTOTAL برای داده‌های فیلتر شده
گاهی اوقات، ممکن است یک مجموعه داده عظیم داشته باشید، جایی که بخواهید داده‌ها را فیلتر کنید و سپس داده‌های فیلتر شده را در یک صفحه جداگانه کپی و جایگذاری کنید.
اگر از هر یک از روش‌های نشان داده شده در بالا تا کنون استفاده کنید، متوجه خواهید شد که شماره ردیف‌ها ثابت می‌مانند. این بدان معنی است که وقتی داده‌های فیلتر شده را کپی می‌کنید، باید شماره گذاری ردیف را به روز کنید.
در چنین مواردی، تابع SUBTOTAL می‌تواند به طور خودکار شماره ردیف‌ها را به روز کند. حتی وقتی مجموعه داده را فیلتر می‌کنید، شماره ردیف‌ها دست نخورده باقی می‌مانند.
بگذارید دقیقاً با یک مثال به شما نشان دهم که چگونه کار می‌کند.
فرض کنید شما یک مجموعه داده مانند شکل زیر دارید:
اگر این داده‌ها را بر اساس فروش محصول A فیلتر کنم، چیزی مانند شکل زیر دریافت خواهید کرد:
توجه داشته باشید که شماره ردیف در ستون A نیز فیلتر شده است. بنابراین اکنون، شما فقط اعداد ردیف‌هایی را می‌بینید که قابل مشاهده هستند.
در حالی که این رفتار مورد انتظار است، در صورتی که می‌خواهید یک شماره ردیف سریال دریافت کنید – به طوری که به سادگی بتوانید این داده‌ها را در جای دیگری کپی و جایگذاری کنید – می‌توانید از تابع SUBTOTAL استفاده کنید.
در ادامه تابع SUBTOTAL آمده است که مطمئن می‌شود حتی داده‌های فیلتر شده دارای شماره گذاری ردیف مداوم هستند.
=SUBTOTAL(3,$B$2:B2)
عدد 3 در تابع SUBTOTAL با استفاده از تابع COUNTA مشخص می‌شود. آرگومان دوم محدوده‌ای است که تابع COUNTA روی آن اعمال می‌شود.
مزیت تابع SUBTOTAL این است که هنگام فیلتر کردن داده‌ها به صورت پویا به روز می‌شود (مانند شکل زیر):
توجه داشته باشید که حتی زمانی که داده‌ها فیلتر می‌شوند، شماره گذاری ردیف به روز می‌شود و پیوسته باقی می‌ماند.
6. ایجاد یک جدول اکسل
Excel Table یک ابزار عالی است که هنگام کار با داده‌های جدولی باید از آن استفاده کنید. مدیریت و استفاده از داده‌ها را بسیار آسان تر می‌کند.
این نیز روش مورد علاقه من در بین تمام تکنیک‌های نشان داده شده در این آموزش است.
اجازه دهید ابتدا روش صحیح شماره گذاری ردیف‌ها را با استفاده از جدول اکسل به شما نشان دهم:
- کل مجموعه داده را انتخاب کنید.
- به تب Insert بروید.
- روی نماد Table کلیک کنید (همچنین می‌توانید از میانبر صفحه کلید Control + T استفاده کنید).
- در کادر محاوره‌ای ایجاد جدول، مطمئن شوید که محدوده صحیح است.
- روی OK کلیک کنید. این داده‌های جدولی شما را به یک جدول اکسل تبدیل می‌کند.
- در سلول A2 فرمول زیر را وارد کنید. توجه داشته باشید که به محض وارد کردن فرمول، به طور خودکار آن را در تمام سلول‌های آن ستون پر می‌کند (اطلاعات بیشتر در مورد ستون‌های محاسبه شده را می‌توانید در اینجا بخوانید ).
=ROW()-ROW(Table2[#Headers])
توجه داشته باشید که در فرمول بالا، من از Table2 استفاده کرده‌ام، زیرا این نام جدول اکسل من است. شما می‌توانید جدول 2 را با نام جدولی که دارید جایگزین کنید.
استفاده از جدول اکسل در هنگام شماره گذاری ردیف‌ها در اکسل مزایای دیگری نیز دارد:
- از آنجایی که جدول اکسل به طور خودکار فرمول را در کل ستون وارد می‌کند، زمانی که یک ردیف جدید را در جدول وارد می‌کنید، کار می‌کند. این بدان معناست که وقتی ردیف‌هایی را در جدول اکسل وارد می‌کنید/حذف می‌کنید، شماره‌گذاری ردیف‌ها به‌طور خودکار به‌روزرسانی می‌شود (مانند شکل زیر).
- اگر ردیف‌های بیشتری را به داده‌ها اضافه کنید، جدول اکسل به طور خودکار گسترش می‌یابد تا این داده‌ها را به عنوان بخشی از جدول شامل شود. و از آنجایی که فرمول‌ها به طور خودکار در ستون‌های محاسبه شده به روز می‌شوند، شماره ردیف را برای ردیف تازه درج شده درج می‌کند (مانند شکل زیر).
7. اضافه کردن 1 به شماره ردیف قبلی
این یک روش ساده است که کار می‌کند.
ایده این است که 1 را به شماره ردیف قبلی (عدد در سلول بالا) اضافه کنید. این کار اطمینان حاصل می‌کند که ردیف‌های بعدی عددی را دریافت می‌کنند که 1 افزایش می‌یابد.
فرض کنید شما یک مجموعه داده مانند شکل زیر دارید:
در اینجا مراحل وارد کردن شماره ردیف با استفاده از این روش آمده است:
- در سلول ردیف اول 1 را به صورت دستی وارد کنید. در این مورد، در سلول A2 است.
- در سلول A3 فرمول =A2+1 را وارد کنید
- فرمول تمام سلول‌های ستون را کپی و جایگذاری کنید.
مراحل بالا شماره ردیف را در تمام سلول‌های ستون وارد می‌کند. در صورت وجود هر ردیف خالی، همچنان شماره ردیف را برای آن درج می‌کند.
همچنین توجه داشته باشید که در صورت وارد کردن یک ردیف جدید، شماره ردیف به روز نمی‌شود. در صورت حذف یک ردیف، تمام سلول‌های زیر ردیف حذف شده یک خطای مرجع را نشان می‌دهند.
اگر شما از روش دیگری استفاده می‌کنید، آن را در قسمت نظرات با من به اشتراک بگذارید.