تمرین ۲۱: توابع IF, IFERROR, MATCH, AND برای بررسی موارد تکراری

دیدگاه‌ خود را بنویسید / از /
بازگشت به نتایج جستجو
ویژه⭐ محبوب✨ , , ,

🔍 مقدمه

در تحلیل داده‌های اکسل، یکی از چالش‌های رایج، شناسایی موارد تکراری در ستون‌ها یا ردیف‌هاست. ترکیب توابع شرطی و منطقی مانند IF، IFERROR، MATCH و AND به کاربران این امکان را می‌دهد تا به‌صورت هوشمند، داده‌های تکراری را تشخیص داده و واکنش مناسب نشان دهند. این روش به‌ویژه در مدیریت اطلاعات مشتریان، کدهای ملی، شماره تماس یا شناسه‌های یکتا بسیار کاربردی است.

🏷️ کلمات کلیدی

شناسایی داده‌های تکراری، تابع IF در اکسل، تابع MATCH، تابع IFERROR، تابع AND، بررسی تکرار در اکسل، فرمول شرطی، آموزش اکسل، توابع منطقی، تحلیل داده

تابع IF در اکسل برای اجرای شرط‌ها استفاده می‌شود. وقتی بخواهیم بررسی کنیم که آیا یک مقدار در محدوده‌ای وجود دارد یا نه، از MATCH کمک می‌گیریم. اگر مقدار یافت شود، MATCH موقعیت آن را برمی‌گرداند؛ در غیر این صورت خطای #N/A تولید می‌شود. برای کنترل این خطا، از IFERROR استفاده می‌کنیم تا خروجی دلخواهی مثل “No” یا صفر را جایگزین کنیم. در نهایت، اگر بخواهیم بررسی کنیم که مقدار مورد نظر در چند ستون به‌طور همزمان وجود دارد، از AND برای ترکیب شرط‌ها بهره می‌گیریم.

ساختار ترکیبی فرمول:

=IFERROR(IF(AND(MATCH(D3,$B$3:$B$8,0),MATCH(D3,$C$3:$C$8,0)),”Yes”,”No”),”No”)

توضیح اجزای فرمول:

  • MATCH(D3,$B$3:$B$8,0): بررسی وجود مقدار سلول D3 در ستون اول
  • MATCH(D3,$C$3:$C$8,0): بررسی وجود همان مقدار در ستون دوم
  • AND(…): بررسی همزمان وجود در هر دو ستون
  • IF(…): اگر هر دو شرط برقرار باشد، “Yes” برگردان؛ در غیر این صورت “No”
  • IFERROR(…): اگر MATCH خطا دهد، خروجی “No” بده

این ترکیب به‌ویژه در شناسایی مواردی که در چند منبع یا لیست تکرار شده‌اند، بسیار مؤثر است.

🎯 سناریوی کاربردی

فرض کنید لیستی از کدهای ملی مشتریان در دو ستون دارید: ستون اول مربوط به ثبت‌نام اولیه و ستون دوم مربوط به ثبت‌نام نهایی. حال می‌خواهید بررسی کنید که آیا یک کد ملی خاص در هر دو ستون وجود دارد یا نه.

فرمول پیشنهادی:

=IFERROR(IF(AND(MATCH(D3,$B$3:$B$8,0),MATCH(D3,$C$3:$C$8,0)),”تکراری”,”غیرتکراری”),”غیرتکراری”)

با این فرمول، اگر مقدار D3 در هر دو ستون وجود داشته باشد، خروجی “تکراری” خواهد بود؛ در غیر این صورت “غیرتکراری”.

حل تمرین:

در این تمرین جدولی داریم که دارای موارد تکراری در سایر ستون‌ها هستند، می‌خواهیم موارد تکرار شده در ستون‌ها را شناسایی کنیم و به ازای موارد تکراری پیدا شده مقدار YES و غیر تکراری مقدار NO را برگرداند.

بیایم این فرمولی که باید طراحی بشه رو با توجه به جدول کدملی‌هایی که داریم، دقیق و مرحله‌به‌مرحله تحلیل کنیم:

کدملی (1) کدملی (2) کدملی جدید
480495469 111436591 583598475
583598475 893797626 111436591
111436591 87689251 893797626
893797626 644370858 82329252
87689251 625373859 694010125
158214803 480495469 250695483

🧠 ساختار فرمول:

=IFERROR(IF(AND(MATCH(D3,$B$3:$B$8,0),MATCH(D3,$C$3:$C$8,0)),"Yes","No"),"No")

🔍 اجزای فرمول:

1. MATCH(D3,$B$3:$B$8,0)

  • بررسی می‌کنه آیا مقدار سلول D3 در محدوده ستون کدملی (1) وجود داره یا نه.
  • اگر پیدا بشه، موقعیت نسبی اون رو برمی‌گردونه (مثلاً 2 یعنی دومین سلول).
  • اگر پیدا نشه، خطای #N/A تولید می‌کنه.

2. MATCH(D3,$C$3:$C$8,0)

  • مشابه بالا، ولی برای ستون کدملی (2).

3. AND(...)

  • بررسی می‌کنه که هر دو MATCH موفق باشن.
  • یعنی مقدار D3 باید هم در ستون B و هم در ستون C وجود داشته باشه.

4. IF(...)

  • اگر شرط AND برقرار باشه → خروجی "Yes"
  • اگر برقرار نباشه → خروجی "No"

5. IFERROR(...)

  • اگر هر کدام از MATCHها خطا بدن (یعنی مقدار پیدا نشه)، خروجی "No" خواهد بود.
  • این باعث می‌شه فرمول از خطای #N/A جلوگیری کنه و به‌جای اون مقدار "No" نمایش بده.

📊 بررسی با جدول داده‌شده:

کدملی (1) کدملی (2) کدملی جدید
480495469 111436591 583598475
583598475 893797626 111436591
111436591 87689251 893797626
893797626 644370858 82329252
87689251 625373859 694010125
158214803 480495469 250695483

فرض کن مقدار سلول D3 برابر باشه با 111436591:

  • آیا در ستون B هست؟ ✅ بله (ردیف 1 و 3)
  • آیا در ستون C هست؟ ✅ بله (ردیف 1) → نتیجه: "Yes"

حالا اگر مقدار D3 برابر باشه با 158214803:

  • در ستون B هست؟ ✅ بله (ردیف 6)
  • در ستون C هست؟ ❌ نه → نتیجه: "No"

🎯 کاربرد عملی:

این فرمول بررسی می‌کنه که آیا یک کدملی خاص در هر دو ستون کدملی (1) و کدملی (2) وجود داره. این می‌تونه برای شناسایی کدهایی باشه که در هر دو نقش ظاهر شدن (مثلاً هم به عنوان فرستنده و هم گیرنده، یا هم والد و هم فرزند در یک ساختار وابستگی).

اگه بخوایم این تحلیل رو برای کل لیست انجام بدیم و خروجی “Yes/No” رو در کنار هر کد جدید ببینیم، می‌تونیم یه ستون کمکی بسازیم.

حالا با هم تمرین حل شده رو در محیط واقعی اکسل تجربه می‌کنیم.

سوالات متداول:

مشخصات و دانلود
دانلود فایل:

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

این سایت از اکیسمت برای کاهش جفنگ استفاده می‌کند. درباره چگونگی پردازش داده‌های دیدگاه خود بیشتر بدانید.

سبد خرید
پیمایش به بالا