محصول به سبد خرید شما اضافه شد.
پنل کاربری

پیدا کردن شماره های حذف شده از یک لیست در اکسل

رسول سلیمانی
1,622
2 دیدگاه
پیدا کردن شماره های حذف شده از لیست در اکسل

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

این سوال یکی از دنبال کننده های صفحه اینستاگرام تالانت (talent.ir@) بود که توسط آقای یاسر طاهرخانی در یکی از پست ها به خوبی به آن پاسخ داده شده است. اما در اینجا می خواهیم یک فرمول کلی برای چنین مواردی یعنی پیدا کردن شماره های حذف شده از یک لیست، ارائه دهیم.

فرمول کلی:

=SMALL(IF(ISERROR(MATCH(All_List,List,0)),All_List,””),ROW(A1))

List :  =$B$5:$B$14

All_List : =ROW(INDIRECT(“A“&MIN(List)&”:A“&MAX(List),TRUE))

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

در این فرمول List همان لیست شماره فاکتورها است که می خواهیم شماره حذف شده در آن را پیدا کنیم . در این مثال (تصویر بالا) شماره فاکتورها در محدوده B5:B14 قرار داشته و با روش نام گذاری محدوده Named Range به اسم List نامگذاری شده است. همچنین برای جلوگیری از کوتاه شدن فرمول و درک راحت آن بخشی از فرمول با استفاده از ابزار Named Range با عبارت All_List نام گذاری شده است. توجه داشته باشید که این فرمول به صورت آرایه ای بوده و برای استفاده از آن باید از کلیدهای ترکیبی Ctrl + Shift + Enter استفاده کرد.

نحوه عملکرد فرمول :

1- در این فرمول ابتدا مقدار All_List محاسبه می شود. هدف از محاسبه All_List بدست آوردن لیست تمامی شماره فاکتورهای ممکن، شامل فاکتورهای موجود و ابطال شده می باشد.

All_List : =ROW(INDIRECT(“A“&MIN(List)&”:A“&MAX(List),TRUE))

در فرمول بالا  ابتدا کوچکترین و بزرگترین شماره فاکتور موجود با استفاده از توابع MIN و MAX محاسبه می شود

1: =ROW(INDIRECT(“A“&1&”:A“&14,TRUE))

2: =ROW(INDIRECT(“A1:A14“,TRUE))

سپس تابع INDIRECT عبارت بدست آمده (“A1:A14”) را به محدوده قابل فهم برای اکسل تبدیل می کند.

=ROW(A1:A14)

در ادامه چون از فرمول به صورت آرایه ای استفاده می کنیم تابع ROW آرایه ای از شماره ردیف محدوده بدست آمده، که همان لیست کامل شماره فاکتورها است را نمایش می دهد.

All_List : ={1;2;3;4;5;6;7;8;9;10;11;12;13;14}

2-در گام بعد تابع MATCH لیست کامل شماره فاکتورها را با لیستی که در اختیار داریم (یعنی List) مقایسه می کند و مواردی را که وجود ندارند (یعنی همان شماره فاکتورهای ابطال شده) را بصورت خطای #N/A  نمایش می دهد

MATCH({1;2;3;4;5;6;7;8;9;10;11;12;13;14},List,0)

= {1;2;#N/A;#N/A;3;4;5;#N/A;6;7;8;#N/A;9;10}

3- در ادامه تابع ISERROR برای موارد خطا مقدار TRUE و برای دیگر مقادیر، مقدار FALSE را ارائه می کند.

ISERROR({1;2;#N/A;#N/A;3;4;5;#N/A;6;7;8;#N/A;9;10})

= {FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}

4- در گام بعد تابع IF با استفاده از خروجی تابع ISERROR مقادیر متناظر TRUE را از لیست کامل شماره فاکتورها نمایش می دهد.

IF({FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE},{1;2;3;4;5;6;7;8;9;10;11;12;13;14},””)

= {“”;””;3;4;””;””;””;8;””;””;””;12;””;””}

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

در این مطلب با نحوه پیدا کردن شماره های حذف شده از یک لیست آشنا شدیم. فرض کنید در این مثال مدیر فروش از احمد می خواهد که لیستی از مشتریان مربوط به این فاکتور ها را در اختیارش قرار دهد. از انجا که ممکن است مشتریان تکرای زیادی در لیست 6000 هزارتایی وجود داشته باشد. تهیه  چنین لیستی مشکل به نظر می رسد. برای آشنایی با نحوه انجام این کار مطلب استخراج مقادیر واحد از یک محدوده را ببینید.

IFISERRORMATCHMAXMINNamed RangeROWSMALL

2 دیدگاه. پیغام بگذارید

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

این قسمت نباید خالی باشد
این قسمت نباید خالی باشد
لطفاً یک نشانی ایمیل معتبر بنویسید.
شما برای ادامه باید با شرایط موافقت کنید

حداکثر حجم مجاز فایل : 5 مگابایت فرمت های مجاز : jpeg - jpg - png - pdf- xls - xlsx - xlsb - xlsm - txt - csv - zip - rar فایل ها را اینجا بکشید

آخرین نظرات

keyboard_arrow_up