استخراج مقادیر واحد از یک محدوده در اکسل

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

فرمت کلی فرمول استخراج مقادیر واحد از یک محدوده:

{=IFERROR(INDEX(List,MATCH(0,COUNTIF(Uniques,List),0)),””)}

در مثال بالا (تصویر) فرمول استفاده شده در سلول D5 به صورت ذیل است:

{=IFERROR(INDEX(List,MATCH(0,COUNTIF(D$4:D4,List),0)),””)}

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

فرمول اصلی مورد استفاده در اینجا تابع INDEX  است:

INDEX(List;row)

در این تابع با مشخص کردن محدوده لیست اسامی (List) و شماره ردیف نام فرد موردنظر استخراج می شود. لذا کار اصلی و مشکل در اینجا پیدا کردن شماره ردیف است که با ترکیب توابع MATCH وCOUNTIF انجام می گیرد:

COUNTIF($D$4:D4;List)

در اینجا تابع COUNTIF بررسی می کند که چه تعداد از اسامی موجود در لیست اصلی در لیست اسامی تکرار شده است. دقت کنید در این تابع از محدوده قابل گسترش (expanding reference) استفاده شده است

$D$4:D4

محدوده قابل گسترش محدوده ای است که یک طرف آن به صورت مطلق و طرف دیگر آن نسبی آدرس دهی شده است لذا با کپی کردن فرمول به سلول های پایین تر محدوده تابع COUNTIF ردیف های بیشتری در لیست اصلی را شامل می شود. دقت کنید که محدوده تابع COUNTIF از یک ردیف بالاتر از لیست اصلی آغاز می شود(سلول D4) زیرا که ما نمی توانیم به سلولی ارجاع دهیم که در آن فرمول می نویسیم.

نکته مهم: دقت کنید که عنوان لیست اصلی (در اینجا سلول D4) در لیست اسامی وجود نداشته باشد.

برای آرگومان شرط (criteria) در تابع COUNTIF از خود محدوده لیست اسامی (List) استفاده شده است. لذا با در نظر گرفتن شروط چندگانه تابع COUNTIF نتایج را به صورت آرایه ایی همانند ذیل ارائه می دهد:

1: {0;0;0;0;0;0;0;0;0;0}   /// ردیف 5

2: {1;0;0;0;0;0;0;0;1;1}   /// ردیف 6

3: {1;1;1;0;0;0;1;0;1;1}   /// ردیف 7

4: {1;1;1;1;0;1;1;0;1;1}    /// ردیف 8

5: {1;1;1;1;1;1;1;0;1;1}   /// ردیف 9

حال محدوده مورد بررسی (لیست اصلی) را در اختیار داریم و هم اکنون نیاز داریم که موقعیت اسامی (شماره ردیف) را در لیست اصلی به دست آوریم. برای این کار از تابع MATCH برای پیدا کردن مقادیر 0 صفر استفاده می شود. با قرار دادن محدوده به دست آمده از تابع COUNTIF در تابع MATCH نتیجه به صورت ذیل خواهد بود:

1: MATCH(0,{0;0;0;0;0;0;0;0;0;0},0)    /// ردیف 5 => A

2: MATCH(0,{1;0;0;0;0;0;0;0;1;1},0)     /// ردیف 6 => B

3: MATCH(0,{1;1;1;0;0;0;1;0;1;1},0)     /// ردیف 7 => G

4: MATCH(0,{1;1;1;1;0;1;1;0;1;1},0)    /// ردیف 8 => H

5: MATCH(0,{1;1;1;1;1;1;1;0;1;1},0)    /// ردیف 9 => E

تابع MATCH موقعیت اسامی (شماره ردیف) را با جست و جوی مقادیر 0 صفر مشخص می کند (به عبارت دیگر موقعیت اسامی را مشخص می کند که در لیست اصلی وجود ندارند). در نهایت موقعیت اسامی (شماره ردیف) به دست آمده در تابع INDEX قرار گرفته و این تابع اسم فرد را نمایش می دهد. در پایان اینکه برای کنترل خطاها کل فرمول در تابع IFERROR قرار داده شده است.

منبع : با کمی تغییرات Exceljet.net

پاسخی بگذارید

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