ثبت نام دوره آنلاین VBA به زبان ساده
سبد خرید
0

سبد خرید شما خالی است.

جستجو
Generic filters

تفاوت داده Distinct و Unique چیست و چطور میشه مشخص کرد؟

مقادیر متمایز در اکسل
نظر شما در مورد این آموزش

تعیین مقادیر متمایز (Distinct) و منحصر به فرد (Unique) در یک محدوده

پیدا کردن و مشخص کردن داده های منحصربه فرد یا یونیک و پیدا کردن داده هایی که حداقل یکبار تکرار شدن، یکی از مسائلی هست که خیلی کاربرد داره و مورد سوال کاربارن اکسل هست. فرض کنید لیستی از اسامی مشتریانی داریم که از یک فروشگاه خرید کردن، می خواهیم اسامی مشتریانی که فقط یکبار خرید کردن (داده های منحصر به فرد- Unique Values) و یا اینکه اسامی تمام مشتریانی که حداقل یکبار خریدن کردن (داده های متمایز- Distinct Values) رو مشخص کنیم. در واقع قصد داریم مقادیر منحصر به فرد (غیر تکراری) و متمایز در یک محدوده ( به نوعی مقایسه لیست رو میخوایم انجام بدیم) رو تعیین کنیم. برای اینکه با روش کار آشنا بشین مطالعه ی این مقاله رو پیشنهاد می کنیم.

در این مقاله قصد داریم نحوه شناسایی و فرمت دهی (با ابزار Conditional Formatting)  داده های غیر تکراری و متمایز رو آموزش بدیم. اما در مرحله اول بهتر هست که با مفهوم مقادیر منحصر به فرد و متمایز در اکسل آشنا بشیم.

تعریف مقادیر منحصر به فرد (Unique): مقادیری هستند که فقط یک بار در یک مجموعه داده ظاهر می شن.

تعریف مقادیر متمایز (Distinct) : همه مقادیر مختلف در یک لیست که شامل مقادیر منحصر به فرد و مقادیر تکراری که برای اولین بار نمایش داده میشن. (از هر داده (چه تکراری چه غیرتکراری) یکی)

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

شناسایی مقادیر Distinct و Unique

راحت ترین روش برای شناسایی مقادیر متمایز و غیر تکراری در اکسل، استفاده از ترکیب توابع IF و COUNTIF هست. فرمولی که از ترکیب این دو تابع نوشته می شه بر اساس نوع داده ای که قصد جستجوی اون رو داریم ، متفاوت هست که در مثال های زیر به بررسی این تفاوت ها می پردازیم:

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

برای شناسایی مقادیر متمایز و غیر تکراری در یک ستون از فرمول های زیر استفاده می کنیم که در این فرمول ها A3 اولین سلول و A13 آخرین سلول حاوی داده در ستون A هستن.

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

=IF (COUNTIF ($A$3: $A$13, $A3) =1, “Unique”, “”)

فرمول شناسایی مقادیر متمایز در ستون A

=IF (COUNTIF ($A$3: $A3, $A3) =1, “Distinct”, “”)

 

به محدوده بررسی شرط (criteria_range) در دو فرمول بالا دقت کنید. نحوه قرار گرفتن علامت $ در آدرس دهی محدوده ها در این فرمول خیلی مهمه. در فرمول مربوط به شناسایی مقادیر منحصر به فرد محدوده بررسی شرط ($A$3:$A$13) با درگ کردن فرمول تغییری نمی کنه و ثابت هست. این فرمول مقدار هر سلول رو در کل محدوده بررسی می کنه و در صورتی که تعداد شمارش شده برابر یک باشه اون مقدار رو منحصر به فرد در نظر می گیره.

اما در فرمول دوم محدوده بررسی شرط در هر سلول متفاوت هست و با درگ کردن فرمول محدوده بررسی شرط هم بزرگتر میشه به طوری که در سلول A13 محدوده مورد نظر به $A$3:$A13 تغییر پیدا می کنه. دلیل این موضوع هم اینه که داریم به دنبال مقادیر متمایز می گردیم. یعنی میخوایم به محض اینکه تعداد آیتم مورد نظر در محدوده متحرک، برابر با یک شد، مشخص بشه. در اینصورت اگر از آیتم مورد نظر در ادامه محدوده وجود داشته باشه و مثلا بشه ۲ تا، دیگه جزو داده های Distinct به حساب نمیاد و فقط همون اولی در نظر گرفته میشه. یعنی:

در شکل زیر نتیجه استفاده از فرمول های بالا در دو ستون مجزا نمایش داده شده. همان طور که در شکل هم مشخص هست تمامی مقادیر که به عنوان “منحصر به فرد” شناسایی شدن در گروه مقادیر “متمایز” هم قرار گرفتن و در مورد مقادیر تکراری مثال اسم “سارا” فقط بار اولی که این اسم در لیست نمایش داده شده به عنوان مقدار متمایز شناسایی شده.

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

شکل ۱- شناسایی مقادیر غیر تکراری و متمایز در یک ستون

شناسایی مقادیر Unique و Distinct در سطر ها

فرض کنید جدولی حاوی دو ستون “نام” و “نام خانوادگی” داریم. می خواهیم نام و نام خانوادگی های غیر تکراری و متمایز رو مشخص کنیم برای اینکار از ترکیب دو تابع IF و COUNTIFS به شکل زیر استفاده می کنیم.

فرمول تعیین سطر حاوی نام و نام خانوادگی غیر تکراری

=IF (COUNTIFS ($A$3: $A$10, $A3, $B$3: $B$10, $B3) =1, “Unique row”, “”)

فرمول تعیین سطر حاوی نام و نام خانوادگی متمایز

=IF (COUNTIFS ($A$3: $A3, $A3, $B$3: $B3, $B3) =1, “Distinct row”, “”)

نتیجه استفاده از دو فرمول بالا در شکل زیر نمایش داده شده. همان طور که در شکل زیر مشخص هست چون در این حالات باید هم نام و هم نام خانوادگی رو چک کنیم که آیا تکراری هست یا نه (دو شرط باید چک بشه) در نتیجه از تابع COUNTIFS استفاده کردیم. در این حالت هم تفاوت دو فرمول در محدوده انتخابی تابع COUNTIFS (criteria_range) هست.

شناسایی مقادیر منحصر به فرد و متمایز در سطر ها

شکل ۲- شناسایی مقادیر غیر تکراری و متمایز در سطر ها

شناسایی مقادیر Unique و Distinct حساس به اندازه حروف (بزرگ و کوچک)

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

فرمول تعیین مقادیر منحصر به فرد حساس به اندازه حروف

=IF (SUM ((–EXACT ($A$3: $A$10, A3))) =1,”Unique”,””)

فرمول تعیین مقادیر متمایز حساس به اندازه حروف

=IF (SUM ((–EXACT ($A$3: $A3, $A3))) =1,”Distinct”,””)

شناسایی مقادیر حساس به اندازه حروف

شکل ۳- شناسایی مقادیر منحصر به فرد و متمایز حساس به اندازه حروف

در این مثال چون می خواهیم دقیقا عین مقادیر رو کنترل کنیم و اندازه حروف برامون اهمیت داره از تابع EXACT استفاده کردیم. این تابع دو عبارت رو با هم چک میکنه و اگر با هم برابر باشند TRUE و اگر برابر نباشند FALSE نشون میده. و برای اینکه این عبارت های منطقی (True/False) رو تبدیل به عدد کنیم از دو علامت “- -” قبل از تابع EXACT استفاده کردیم که این دو علامت عبارت های TRUE و FALSE رو به اعداد یک و صفر تبدیل می کنه و بعد از نوشتن فرمول هم از کلیدهای ترکیبی Ctrl + Shift + Enter برای فعال کردن فرمول آرایه ای استفاده می کنیم. نحوه عملکرد این فرمول رو می تونیم به صورت مرحله به مرحله با استفاده از Evaluate Formula که در تب Formula قرار داره بررسی کنیم که در ویدئو زیر نمایش داده شده:

ارزیابی فرمول

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

برای فیلتر کردن مقادیر منحصر به فرد و متمایز کافیه با استفاده از فرمول های بالا مقادیر رو مشخص کنیم و بعد با اعمال فیلتر روی داده هامون مقادیر دلخواه رو فیلتر کنیم مطابق شکل زیر:

فیلتر کردن مقادیر

شکل ۴- فیلتر کردن مقادیر منحصر به فرد و متمایز

فرمت دهی مقادیر منحصر به فرد و متمایز با استفاده از ابزار Conditional Formatting

حالا دو روش با استفاده از ابزار Conditional Formatting برای فرمت دهی مقادیر منحیر به فرد در یک ستون رو ببینید:

فرمت دهی مقادیر منحصر به فرد در یک ستون با استفاده از قوانین پیش فرض در Conditional Formatting

سریعترین راه برای شناسایی مقادیر منحصر به فرد در اکسل استفاده از قوانین پیش فرض در ابزار Conditional Formatting هست. برای اینکار به روش زیر عمل می کنیم:

  1. ستونی که می خواهیم داده های منحصر به فرد رو در اون مشخص کنیم، انتخاب می کنیم؛
  2. از تب Home و از زیر گروه Styles روی Conditional Formatting کلیک می کنیم و مطابق شکل زیر، از زیر مجموعه Highlight Cells Rules گزینه Duplicate Values… رو انتخاب می کنیم؛

تعیین مقادیر منحصر به فرد با ابزار Conditional Formatting

شکل ۵- تعیین مقادیر منحصر به فرد با ابزار Conditional Formatting

  1. در پنجره Duplicate Values مطابق شکل زیر در باکس سمت چپ گزینه Unique و در باکس سمت راست فرمت دلخواه رو انتخاب می کنیم و دکمه OK رو می زنیم؛

تنظیمات پنجره Duplicate Values برای تعیین مقادیر منحصر بفرد

شکل ۶- تنظیمات پنجره Duplicate Values برای تعیین مقادیر غیر تکراری

در صورتی که فرمت های پیش فرض راضی کننده نباشن می تونیم با انتخاب گزینه Custom Format… در شکل ۶، فرمت دلخواه رو ایجاد بکنیم.

همان طور که گفتیم تعیین مقادیر غیر تکراری با ابزار Conditional Formatting خیلی سریع و راحت هست اما برای تعیین مقادیر متمایز چون قانون پیش فرضی در Conditional Formatting وجود نداره لذا باید خودمون دست به کار بشیم و با فرمول نویسی در Conditional Formatting این قانون رو ایجاد کنیم.

فرمت دهی مقادیر غیر تکراری و متمایز با استفاده از تعریف قوانین سفارشی در Conditional Formatting

برای انجام اینکار ابتدا ستونی که داده ها در اون قرار دارن رو انتخاب می کنیم و پس از انتخاب Conditional Formatting در مسیر زیر، فرمول رو وارد می کنیم.

Conditional Formatting > New rule > Use a formula to determine which cells to format

فرمول تعیین مقادیر غیر تکراری

=COUNTIF ($A$2: $A$12, $A2) =1

نکته:
با استفاده از این فرمول، میتونیم داده هایی که دقیقا ۲، ۳ یا N بار تکرار شدن رو هم مشخص کنیم. یعنی کافیه بجای عدد ۱، مقدار مورد نظر رو قرار بدیم.( این قاعده هم جزو قواعد پیشفرض این ابزار نیست)

 

فرمول تعیین مقادیر متمایز

=COUNTIF ($A$2: $A2, $A2) =1

در فرمول های بالا A2 اولین سلول حاوی داده در محدوده انتخاب هست. نتیجه به کارگیری فرمول های بالا در شکل های ۷ و ۸ نمایش داده شده.

فرمول نویسی در Conditional Formatting برای تعیین مقادیر منحصر بفرد

شکل ۷- فرمول نویسی در Conditional Formatting برای تعیین مقادیر منحصر به فرد

فرمول نویسی در Conditional Formatting

شکل ۸- فرمول نویسی در Conditional Formatting برای تعیین مقادیر متمایز

نکته کاربردی:

در مثال بالا اگر بخواهیم درکل سطر فرمت تعریف شده اعمال بشه (کل سطر به رنگ زرد یا قرمز در بیاد) کافیه که محدوده اعمال Conditional Formatting رو از یک ستون به کل ستون های جدول تغییر بدیم. برای اینکار روی یکی از سلول های ستون A (که قبلا در اون فرمت تعریف شده) رو انتخاب می کنیم و بعد از قسمت Conditional Formatting روی گزینه Manage Rules … کلیک می کنیم که پنجره ای به شکل زیر نمایش داده میشه:

نمایش محدوده اعمال فرمت شرطی در پنجره Conditional Formatting Rules Manager

شکل ۹- نمایش محدوده اعمال فرمت شرطی در پنجره Conditional Formatting Rules Manager

حالا کافیه در قسمت Applies to محدوده رو تغییر بدیم برای اینکار روی علامت پیکان مشکی رنگ کلیک می کنیم و محدوده A2:C12 رو انتخاب می کنیم و روی دکمه OK می زنیم. نتیجه کار در شکل زیر نمایش داده شده:

تغییر محدوده اعمال Conditional Formatting

شکل ۱۰- تغییر محدوده اعمال Conditional Formatting

فرمت دهی سطرهای حاوی مقادیر منحصر به فرد یا متمایز در اکسل

برای فرمت دهی سطرها براساس مقادیر موجود در دو ستون یا بیشتر از تابع COUNTIFS استفاده می کنیم چون باید چند شرط رو بررسی کنیم. فرمول های مورد استفاده به شکل زیر هستن:

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

=COUNTIFS ($A$2: $A$10, $A2, $B$2: $B$10, $B2) =1

فرمول مورد استفاده برای شناسایی سطر حاوی مقادیر متمایز

=COUNTIFS ($A$2: $A2, $A2, $B$2: $B2, $B2) =1

در فرمول های بالا ستون های A و B رو برای شناسایی مقادیر متمایز و منحصر به فرد کنترل می کنیم و بر اساس مقادیر این دو ستون سطر مربوطه رو فرمت دهی می کنیم.

برای روشن شدن بهتر فرمول ها، جدول زیر رو در نظر بگیرید. می خواهیم هر سطری که دارای نام و نام خانوادگی منحصر به فرد و متمایز هست رو فرمت دهی کنیم. برای این کار اول از همه کل ستون های جدول (به جز سر ستون ها) رو انتخاب می کنیم و فرمول های بالا رو در Conditional Formatting می نویسیم. نتیجه استفاده از هر کدوم از فرمول ها در شکل های ۱۱ و ۱۲ نمایش داده شده.

فرمت دهی سطرهای حاوی مقادیر منحصر به فرد در ستون های A و B

شکل ۱۱- فرمت دهی سطرهای حاوی مقادیر منحصر به فرد در ستون های A و B

فرمت دهی سطرهای حاوی مقادیر متمایز در ستون های A و B

شکل ۱۲- فرمت دهی سطرهای حاوی مقادیر متمایز در ستون های A و B

همان طور که قبلا هم گفتیم مهم ترین نکته در این فرمول ها توجه به نحوه درست آدرس دهی در تابع COUNTIFS هست که با توجه به تعریفی که از مقادیر غیر تکراری و متمایز کردیم متفاوت هستن. اگر با این مفهوم آشنا نیستید مقاله اصول فرمول نویسی رو مطالعه کنید یا ویدئوی رایگان اصول فرمول نویسی رو از اینجا دانلود کنید.

در این مقاله در ابتدا مفهوم مقادیر منحصر به فرد و متمایز رو توضیح دادیم و در ادامه با استفاده از ترکیب تابع IF با توابع COUNIF و COUNTIFS در محدوده های مختلف (سطر و ستون) مقادیر منحصر به فرد و متمایز رو تعیین کردیم. همچنین با نحوه فرمول نویسی در ابزار Conditional Formatting برای شناسایی مقادیر غیر تکراری و متمایز آشنا شدیم.

من سامان چراغی هستم. دانش آموخته مقطع فوق لیسانس دانشگاه تربیت مدرس در رشته مهندسی صنایع. از سال 1388 اکسل و برنامه نویسی VBA رو به صورت حرفه ای شروع کردم.

ارسال دیدگاه

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

توسط
تومان