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

Question

Marcya یک لیست طولانی از اعداد مرتب شده در ستون A یک کاربرگ دارد. این اعداد ظاهراً متوالی هستند، اما او نمی داند که آیا این درست است یا خیر. بررسی دستی لیست هم خسته کننده و هم مستعد خطا است، بنابراین مارسیا به این فکر می کند که آیا راهی برای برجسته کردن «اعداد از دست رفته» (اعدادی که با اعداد قبلی متوالی نیستند) وجود دارد یا فهرستی از اعدادی که در آن از قلم افتاده اند را تهیه کند. فهرست

راه‌های مختلفی وجود دارد که می‌توانید برای اینکه بفهمید در کجا اعداد گم شده‌اند، استفاده کنید. اولین موردی است که من اغلب استفاده می کنم: من یک ستون کمکی در کنار ستون A اضافه می کنم. با فرض اینکه اعداد شما از سلول A1 شروع می شوند، این را در سلول B2 قرار می دهم:

=IF(A2<>A1+1,"Error","")

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

=IF(A2=A1,"Duplicate",IF(A2<>A1+1,"Gap",""))

روش دیگر استفاده از قالب‌بندی شرطی در سلول‌های ستون A است. این مراحل را دنبال کنید، دوباره با این فرض که مقادیر شما در سلول A1 شروع می‌شوند:

شکل 1. کادر محاوره ای New Formatting Rule.

1. محدوده A2 تا آخرین مقدار در ستون A را انتخاب کنید.
2. در حالی که تب صفحه اصلی نوار نمایش داده می شود، روی گزینه Conditional Formatting در گروه Styles کلیک کنید. اکسل پالتی از گزینه های مربوط به قالب بندی شرطی را نمایش می دهد.
3. Highlight Cells Rules را انتخاب کنید و سپس بیشتر قوانین را از منوی فرعی انتخاب کنید. اکسل کادر محاوره ای New Formatting Rule را نمایش می دهد. (شکل 1 را ببینید.)
4. در ناحیه Select a Rule Type در بالای کادر محاوره ای، Use a Formula to Determin Your Cells Format را انتخاب کنید.
5. در کادر Format Values ​​Where This Formula Is True، این فرمول را وارد کنید: =A2<>A1+1
6. روی Format کلیک کنید تا کادر محاوره ای Format Cells نمایش داده شود.
7. با استفاده از کنترل‌ها در کادر محاوره‌ای، قالبی را که می‌خواهید برای برجسته کردن سلول‌هایی که پشت سر هم نیستند، مشخص کنید.
8. برای حذف کادر محاوره ای Format Cells روی OK کلیک کنید. قالب بندی که در مرحله 7 مشخص کرده اید اکنون باید در قسمت پیش نمایش قانون ظاهر شود.
9. روی OK کلیک کنید.

در نهایت، اگر می خواهید لیستی از اعداد گمشده در یک سری متوالی را جمع آوری کنید، می توانید از فرمول آرایه استفاده کنید. موارد زیر را در ردیف 1 یک ستون خالی قرار دهید:

=IFERROR(SMALL(IF(COUNTIF($A$1:$A$135,
MIN($A$1:$A$135)+ROW($1:$135)-1)=0, 
MIN($A$1:$A$135)+ROW($1:$135)-1),ROW(A1)),"")

به یاد داشته باشید که این فرمول یک آرایه واحد است، بنابراین باید با استفاده از Ctrl+Shift+Enter آن را به صورت یک خط وارد کنید . سپس می‌توانید فرمول را در تعدادی از سلول‌ها کپی کنید، تا زمانی که مقدار بیشتری برنگرداند. همچنین، فرمول فرض می کند که سری شما در محدوده A1:A135; اگر اینطور نیست، باید فرمول را تغییر دهید تا محدوده واقعی را منعکس کند.