تفاوت داده 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 هست. برای اینکار به روش زیر عمل می کنیم:
- ستونی که می خواهیم داده های منحصر به فرد رو در اون مشخص کنیم، انتخاب می کنیم؛
- از تب Home و از زیر گروه Styles روی Conditional Formatting کلیک می کنیم و مطابق شکل زیر، از زیر مجموعه Highlight Cells Rules گزینه Duplicate Values… رو انتخاب می کنیم؛
شکل ۵- تعیین مقادیر منحصر به فرد با ابزار Conditional Formatting
- در پنجره Duplicate Values مطابق شکل زیر در باکس سمت چپ گزینه Unique و در باکس سمت راست فرمت دلخواه رو انتخاب می کنیم و دکمه OK رو می زنیم؛
شکل ۶- تنظیمات پنجره 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 رو از یک ستون به کل ستون های جدول تغییر بدیم. برای اینکار روی یکی از سلول های ستون A (که قبلا در اون فرمت تعریف شده) رو انتخاب می کنیم و بعد از قسمت Conditional Formatting روی گزینه Manage Rules … کلیک می کنیم که پنجره ای به شکل زیر نمایش داده میشه:
شکل ۹- نمایش محدوده اعمال فرمت شرطی در پنجره Conditional Formatting Rules Manager
حالا کافیه در قسمت Applies to محدوده رو تغییر بدیم برای اینکار روی علامت پیکان مشکی رنگ کلیک می کنیم و محدوده A2:C12 رو انتخاب می کنیم و روی دکمه OK می زنیم. نتیجه کار در شکل زیر نمایش داده شده:
شکل ۱۰- تغییر محدوده اعمال 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
همان طور که قبلا هم گفتیم مهم ترین نکته در این فرمول ها توجه به نحوه درست آدرس دهی در تابع COUNTIFS هست که با توجه به تعریفی که از مقادیر غیر تکراری و متمایز کردیم متفاوت هستن. اگر با این مفهوم آشنا نیستید مقاله اصول فرمول نویسی رو مطالعه کنید یا ویدئوی رایگان اصول فرمول نویسی رو از اینجا دانلود کنید.
در این مقاله در ابتدا مفهوم مقادیر منحصر به فرد و متمایز رو توضیح دادیم و در ادامه با استفاده از ترکیب تابع IF با توابع COUNIF و COUNTIFS در محدوده های مختلف (سطر و ستون) مقادیر منحصر به فرد و متمایز رو تعیین کردیم. همچنین با نحوه فرمول نویسی در ابزار Conditional Formatting برای شناسایی مقادیر غیر تکراری و متمایز آشنا شدیم.
عالی بود ممنونم
بسیار عالی و کاربردی ممنون از انتشار مطالب مفیدتون