6 روش بدست آوردن مقادیر منحصر به فرد در اکسل

رسول سلیمانی

5 سال پیش

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

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

روش 1 : استفاده از ابزار Remove Duplicates

ابتدا محدوده مورد نظر را انتخاب کنید سپس از منوی DATA روی گزینه Remove Duplicates کلیک کنید تا پنجره مربوط به آن باز شود. همانند تصویر ذیل در صورتی که محدوده انتخابی شامل عنوان هم است، تیک گزینه My data has headers را فعال و OK نمایید.

دقت کنید که در این روش مقادیر تکراری حذف شده و دیگر به آن ها دسترسی ندارید. در صورت نیاز به اصل داده ها از روش های دیگر استفاده کنید

روش 2 : استفاده از ابزار Advanced Filter

از منوی DATA روی گزینه Advanced Filter کلیک کنید تا پنجره مربوط به آن باز شود. همانند تصویر ذیل گزینه Copy to another location را فعال کنید. در قسمت List range محدوده مورد نظر و در قسمت Copy to سلولی که نتایج در آن نمایش داده شود را انتخاب و OK نمایید.

در این روش نیز در صورت انتخاب گزینه Filter the list, in-place مقادیر تکراری حذف شده و دیگر به آن ها دسترسی ندارید. در صورت نیاز به اصل داده ها داده ها گزینه Copy to another location را انتخاب یا از روش های دیگر استفاده کنید.

نکته مهم

دقت کنید که محدوده انتخابی حتما شامل عنوان نیز باشد در غیر این صورت، اولین ردیف بجای عنوان در نظر گرفته شده و در فرآیند فیلتر لحاظ نمی شود

روش 3 : استفاده از Pivot Table

ابتدا محدوده مورد نظر را انتخاب کنید سپس از منوی Insert روی گزینه Pivot Table کلیک کنید تا پنجره مربوط به آن باز شود. محل نمایش Pivot Table را تعیین کرده سپس همانند تصویر ذیل ستون (محدوده) انتخابی را به قسمت Row بکشید.

روش 4 : ترکیب توابع (INDEX, MATCH, COUNTIF)

فرمت کلی :

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

در مثال بالا (تصویر) فرمول استفاده شده در سلول D5 به صورت ذیل است. دقت کنید که محدوده B5:B15 با روش نامگذاری محدوده (Named Range) به اسم List نامگذاری شده است.

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

در ضمن این فرمول به صورت آرایه ای (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} /// D5 سلول

2: {1;0;0;0;0;0;0;0;1;1} /// D6 سلول

3: {1;1;1;0;0;0;1;0;1;1} /// D7 سلول

4: {1;1;1;1;0;1;1;0;1;1} /// D8 سلول

5: {1;1;1;1;1;1;1;0;1;1} /// D9 سلول

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

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

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

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

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

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

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

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

روش 5 : استفاده از VBA

با استفاده از کلید های Alt + F11 وارد محیط VBA شوید. از کادر Project روی نام فایل کلیک راست کرده و از منوی Insert گزینه Module را انتخاب کنید. در ماژول جدید ایجاد شده کدهای ذیل را کپی کنید.

Sheet1.irکپی کردن

Option Explicit 
Function My_UNIQUE(ByVal U_Rng As Range, ByVal Num As Double) 
    Dim Val_Uni(10000), Rng As Variant 
    Dim i, j, R, C As Double 
    '
    R = U_Rng.Row 
    C = U_Rng.Column 
    ' 
    For Each Rng In U_Rng 
        With Application.WorksheetFunction 
            If .CountIf(Range(Cells(R, C), Cells(R + j, C)), Rng) = 1 Then 
                Val_Uni(i) = Rng i = i + 1 
            End If 
        End With 
        ' 
        j = j + 1 
    Next 
    ' 
    On Error GoTo 0 
    My_UNIQUE = Val_Uni(Num - 1) 
End Function

Sheet1.ir

به اکسل باز گردید. این کد یک تابع جدید با نام My_UNIQUE به لیست توابع اکسل اضافه می کند

=My_UNIQUE(U_Rng,Num)

و دارای دو آرگومان اصلی است.

U_Rng /// محدوده مورد نظر جهت جست و جو

Num /// امین مقدار منحصر به فرد n شماره

حال همانند تصویر ذیل در سلول مورد نظر علامت = گذاشته و کلمه My را بنویسید خواهید دید که تابع My_UNIQUE در لیست توابع پیشنهادی نمایش داده می شود. آن را انتخاب کنید و در آرگومان اول محدوده مورد نظر و در آرگومان دوم، تابع ROW(A1) را وارد و Enter کنید. تابع اولین مقدار منحصر به فرد را نمایش می دهد فرمول را به پایین درگ کنید تا دیگر مقادیر نیز نمایش داده شود.

User Define Function - مقادیر منحصر به فرد اکسل

روش 6 : استفاده از تابع UNIQUE

تابع UNIQUE در نسخه 2021 اکسل به لیست توابع اضافه شده است و کاربرد اصلی آن نیز استخراج مقادیر منحصر به فرد است. برای بدست آوردن مقادیر منحصر به فرد کافی است محدوده مورد نظر را داخل این تابع قرار داده و Enter کنید.

دقت کنید که می بایست به اندازه مقادیر منحصر به فرد، سلول خالی در پایین سلول حاوی فرمول وجود داشته با شد در غیر این صورت با خطای #Spill مواجه می شوید.

Advanced Filter COUNTIF Filter IFERROR INDEX MATCH Pivot Table Remove Duplicates UNIQUE User Define Functions

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

رضا
۱۸ دی ۱۳۹۹

نکته جالبی بود..ممنون از توضیحات کاملتون

پاسخ
جلال
۲۱ آبان ۱۴۰۳

سلام
لطفا در خصوص ترکیب توابع index و math و countif با مثالهای متعدد بیشتر توضیح بدین
سپاس فراوان

پاسخ

دیدگاهتان را بنویسید لغو پاسخ

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

این قسمت نباید خالی باشد

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

ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی می‌نویسم.

شما برای ادامه باید با شرایط موافقت کنید

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