آموزش تابع XOR در اکسل

معرفی تابع XOR

تابع XOR یکی از توابع منطقی اکسل است که برای بررسی «یا انحصاری» استفاده می‌شود. یعنی زمانی نتیجه TRUE می‌دهد که تعداد شرط‌های درست، فرد باشد. در حالت دو شرطی، اگر فقط یکی از شرط‌ها درست باشد نتیجه TRUE می‌شود؛ اما اگر هر دو شرط درست یا هر دو شرط نادرست باشند، نتیجه FALSE خواهد بود.

به زبان ساده، XOR برای موقعیت‌هایی مناسب است که می‌خواهیم دقیقاً یکی از چند شرط برقرار باشد؛ نه هیچ‌کدام و نه همه آن‌ها.

مثال ساده: فرض کنید در سلول A2 وضعیت ثبت‌نام یک کاربر و در سلول B2 وضعیت پرداخت او بررسی می‌شود. اگر فقط یکی از این دو مورد انجام شده باشد، می‌خواهیم نتیجه TRUE شود.

=XOR(A2="ثبت نام شده", B2="پرداخت شده")

اگر کاربر ثبت‌نام کرده باشد ولی پرداخت نکرده باشد، نتیجه TRUE می‌شود. اگر هم ثبت‌نام کرده باشد و هم پرداخت کرده باشد، نتیجه FALSE می‌شود؛ چون هر دو شرط درست هستند، نه فقط یکی.

کاربردهای اصلی تابع XOR

• بررسی اینکه فقط یکی از دو شرط برقرار است یا نه.
• کنترل وضعیت‌های متناقض، مانند اینکه یک سفارش نباید همزمان هم «ارسال شده» و هم «لغو شده» باشد.
• ساخت قوانین اعتبارسنجی در فرم‌ها و فایل‌های گزارش‌گیری.
• تشخیص انتخاب‌های انحصاری، مثل انتخاب فقط یک گزینه از بین چند گزینه.
• ترکیب با تابع IF برای نمایش پیام‌های سفارشی.
• کنترل داده‌های واردشده توسط کاربران و جلوگیری از ورود اطلاعات ناسازگار.
• تحلیل داده‌های منطقی در کنار توابعی مانند AND، OR، SUM، COUNTIF و XLOOKUP.

ساختار (Syntax)

ساختار انگلیسی تابع XOR در اکسل به شکل زیر است:

=XOR(argument1, argument2, ...)

نمای فارسی ساختار تابع به صورت مفهومی:

=XOR(شرط۱; شرط۲; ...)

تابع XOR می‌تواند چندین آرگومان منطقی دریافت کند. هر آرگومان می‌تواند یک مقدار TRUE یا FALSE، یک مقایسه، یک سلول، یا نتیجه یک فرمول منطقی باشد.

آرگومان‌ها

argument1 / شرط اول

اولین شرط یا مقدار منطقی است که تابع XOR آن را بررسی می‌کند. این آرگومان الزامی است و می‌تواند یک مقدار TRUE یا FALSE، یک مقایسه مانند بزرگ‌تر بودن یک عدد، یا ارجاع به یک سلول باشد.

=XOR(A2>100)

argument2 / شرط دوم

دومین شرط یا مقدار منطقی است. این آرگومان اختیاری است، اما در عمل تابع XOR معمولاً با حداقل دو شرط کاربردی‌تر می‌شود.

=XOR(A2>100, B2="فعال")

argument3 و سایر آرگومان‌ها / شرط‌های بیشتر

می‌توانید شرط‌های بیشتری به تابع اضافه کنید. نکته مهم این است که XOR فقط زمانی TRUE می‌دهد که تعداد شرط‌های TRUE فرد باشد؛ یعنی ۱، ۳، ۵ و به همین ترتیب.

=XOR(A2>100, B2="فعال", C2="تایید شده")

مثال‌های ساده و پایه

مثال ۱: بررسی دقیقاً یکی از دو شرط

فرض کنید در سلول A2 نمره آزمون و در سلول B2 وضعیت پروژه دانشجو قرار دارد. می‌خواهیم بررسی کنیم آیا فقط یکی از این دو شرط برقرار است یا نه: نمره بالاتر از ۷۰ باشد یا پروژه تحویل شده باشد.

=XOR(A2>70, B2="تحویل شده")

اگر نمره دانشجو ۸۰ باشد و پروژه را تحویل نداده باشد، نتیجه TRUE است. اگر نمره بالای ۷۰ باشد و پروژه هم تحویل شده باشد، نتیجه FALSE می‌شود، چون هر دو شرط درست هستند.

مثال ۲: بررسی دو مقدار منطقی مستقیم

در این مثال دو مقدار منطقی مستقیماً به تابع داده شده‌اند.

=XOR(TRUE, FALSE)

نتیجه TRUE است، چون فقط یکی از مقدارها TRUE است.

مثال ۳: زمانی که هر دو شرط درست هستند

=XOR(TRUE, TRUE)

نتیجه FALSE است، چون هر دو شرط درست هستند و XOR برای دو شرط فقط وقتی TRUE می‌شود که یکی درست و دیگری نادرست باشد.

مثال ۴: استفاده از چند شرط

در این مثال سه شرط بررسی می‌شود. اگر تعداد شرط‌های درست فرد باشد، نتیجه TRUE خواهد بود.

=XOR(A2>10, B2>10, C2>10)

اگر فقط یکی از این سه مقدار بزرگ‌تر از ۱۰ باشد، نتیجه TRUE است. اگر هر سه بزرگ‌تر از ۱۰ باشند نیز نتیجه TRUE است، چون تعداد شرط‌های درست ۳ و فرد است.

مثال‌های کاربردی و واقعی

مثال ۱: ترکیب XOR با IF برای نمایش پیام

فرض کنید کارمند یا باید کارت ورود داشته باشد یا کد موقت؛ اما داشتن هر دو مورد همزمان مجاز نیست. در این حالت می‌توانیم از XOR همراه با IF استفاده کنیم.

=IF(XOR(A2="دارد", B2="دارد"), "مجاز", "نیاز به بررسی")

اگر فقط یکی از دو مورد وجود داشته باشد، خروجی «مجاز» است. اگر هیچ‌کدام یا هر دو وجود داشته باشند، خروجی «نیاز به بررسی» خواهد بود.

مثال ۲: ترکیب XOR با SUM برای کنترل مجموع فروش

فرض کنید اگر فروش حضوری انجام شده باشد، فروش آنلاین نباید ثبت شود و برعکس. در سلول A2 مبلغ فروش حضوری و در سلول B2 مبلغ فروش آنلاین قرار دارد.

=XOR(A2>0, B2>0)

اگر فقط یکی از دو مبلغ بزرگ‌تر از صفر باشد، نتیجه TRUE است. این فرمول برای کنترل ثبت درست نوع فروش کاربرد دارد.

مثال ۳: ترکیب XOR با AND

گاهی هر شرط خودش ترکیبی از چند شرط دیگر است. مثلاً مشتری یا باید عضو ویژه باشد و خرید بالای ۵۰۰ هزار تومان داشته باشد، یا کد تخفیف معتبر داشته باشد؛ اما نباید هر دو حالت همزمان برقرار باشد.

=XOR(AND(A2="ویژه", B2>500000), C2="معتبر")

در این فرمول، شرط اول با AND ساخته شده و سپس با شرط دوم توسط XOR مقایسه می‌شود.

مثال ۴: ترکیب XOR با OR

فرض کنید یک درخواست یا باید از سمت واحد فروش یا پشتیبانی ثبت شده باشد، یا باید توسط مدیر تایید شده باشد؛ اما اگر هر دو حالت همزمان درست باشند، نیاز به بررسی دارد.

=XOR(OR(A2="فروش", A2="پشتیبانی"), B2="تایید مدیر")

اگر فقط یکی از دو بخش اصلی فرمول درست باشد، خروجی TRUE خواهد بود.

مثال ۵: ترکیب XOR با COUNTIF

فرض کنید در محدوده A2:A10 نام افرادی وجود دارد که در یک رویداد ثبت‌نام کرده‌اند و در محدوده B2:B10 نام افرادی است که پرداخت کرده‌اند. می‌خواهیم بررسی کنیم آیا نام موجود در D2 فقط در یکی از این دو فهرست وجود دارد یا نه.

=XOR(COUNTIF(A2:A10, D2)>0, COUNTIF(B2:B10, D2)>0)

اگر نام فقط در یکی از فهرست‌ها باشد، نتیجه TRUE است. اگر در هر دو فهرست یا در هیچ‌کدام نباشد، نتیجه FALSE خواهد بود.

مثال ۶: ترکیب XOR با XLOOKUP

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

=XOR(ISNUMBER(XLOOKUP(D2, A2:A20, A2:A20, "")), ISNUMBER(XLOOKUP(D2, B2:B20, B2:B20, "")))

اگر کالا فقط در یکی از دو لیست پیدا شود، نتیجه TRUE خواهد بود. این نوع فرمول برای کنترل مغایرت بین دو لیست بسیار کاربردی است.

ترکیب تابع XOR با فرمول‌های دیگر

• ترکیب با IF برای نمایش نتیجه قابل فهم به جای TRUE و FALSE.

=IF(XOR(A2="بله", B2="بله"), "فقط یکی انتخاب شده", "انتخاب نامعتبر")

• ترکیب با AND برای ساخت شرط‌های چندبخشی و دقیق‌تر.

=XOR(AND(A2>100, B2="فعال"), C2="تایید شده")

• ترکیب با OR برای بررسی گروهی از شرط‌ها در برابر یک شرط دیگر.

=XOR(OR(A2="تهران", A2="کرج"), B2="ارسال فوری")

• ترکیب با COUNTIF برای بررسی وجود یک مقدار در یکی از دو فهرست.

=XOR(COUNTIF(A:A, D2)>0, COUNTIF(B:B, D2)>0)

• ترکیب با SUM برای کنترل اینکه فقط یکی از چند مقدار عددی ثبت شده باشد.

=XOR(A2>0, B2>0, C2>0)

• ترکیب با XLOOKUP برای بررسی وجود داده در دو منبع متفاوت.

=XOR(XLOOKUP(D2, A:A, A:A, "")"", XLOOKUP(D2, B:B, B:B, "")"")

خطاهای رایج و روش رفع آنها

۱. اشتباه گرفتن XOR با OR

بسیاری از کاربران فکر می‌کنند XOR دقیقاً مانند OR عمل می‌کند. در حالی که OR اگر حداقل یکی از شرط‌ها درست باشد TRUE می‌دهد، اما XOR زمانی TRUE می‌دهد که تعداد شرط‌های درست فرد باشد.

روش رفع: اگر می‌خواهید «حداقل یکی از شرط‌ها» درست باشد، از OR استفاده کنید. اگر می‌خواهید «فقط یکی از دو شرط» درست باشد، از XOR استفاده کنید.

=OR(A2="بله", B2="بله")

=XOR(A2="بله", B2="بله")

۲. انتظار نتیجه TRUE برای همه شرط‌های درست

در تابع XOR، اگر دو شرط داشته باشید و هر دو TRUE باشند، نتیجه FALSE می‌شود. این موضوع برای کاربران مبتدی کمی گیج‌کننده است.

روش رفع: منطق XOR را به خاطر بسپارید: در حالت دو شرطی، فقط یکی باید درست باشد.

=XOR(TRUE, TRUE)

۳. استفاده از متن به جای شرط منطقی

اگر در تابع XOR به جای شرط منطقی، متن‌هایی را وارد کنید که اکسل نتواند آن‌ها را به TRUE یا FALSE تبدیل کند، ممکن است خطا دریافت کنید یا نتیجه مورد انتظار ایجاد نشود.

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

=XOR(A2="فعال", B2="غیرفعال")

۴. فراموش کردن منطق تعداد فرد در چند شرط

وقتی تعداد شرط‌ها بیشتر از دو باشد، XOR فقط به «دقیقاً یکی» محدود نیست. اگر تعداد شرط‌های TRUE فرد باشد، خروجی TRUE می‌شود.

روش رفع: اگر واقعاً می‌خواهید دقیقاً یک شرط درست باشد، بهتر است از جمع شرط‌ها استفاده کنید.

=SUM(--(A2>0), --(B2>0), --(C2>0))=1

۵. استفاده در نسخه‌های قدیمی اکسل

تابع XOR در نسخه‌های قدیمی‌تر از Excel 2013 وجود ندارد. اگر فایل شما در نسخه‌های قدیمی باز شود، ممکن است خطای نام تابع نمایش داده شود.

روش رفع: برای سازگاری با نسخه‌های قدیمی، می‌توانید از ترکیب MOD و SUM استفاده کنید.

=MOD(SUM(--(A2>0), --(B2>0)), 2)=1

۶. خطا در ترکیب با XLOOKUP

اگر XLOOKUP مقداری را پیدا نکند و برای حالت پیدا نشدن مقدار جایگزین تعریف نکرده باشید، ممکن است خطا وارد تابع XOR شود.

روش رفع: در XLOOKUP آرگومان مقدار در صورت پیدا نشدن را مشخص کنید.

=XLOOKUP(D2, A2:A20, B2:B20, "یافت نشد")

نکات حرفه‌ای و ترفندهای مهم

• برای خوانایی بهتر، شرط‌ها را ساده و قابل فهم بنویسید. اگر فرمول خیلی طولانی شد، بهتر است از ستون‌های کمکی استفاده کنید.
• در حالت دو شرطی، XOR بهترین گزینه برای بررسی «فقط یکی درست باشد» است.
• در حالت چند شرطی، مراقب باشید؛ چون XOR با تعداد فرد شرط‌های TRUE نتیجه TRUE می‌دهد، نه الزاماً فقط یک شرط.
• اگر دقیقاً یک شرط باید درست باشد، استفاده از SUM و تبدیل TRUE/FALSE به عدد می‌تواند دقیق‌تر باشد.
• برای تبدیل TRUE و FALSE به ۱ و ۰ می‌توانید از دو علامت منفی استفاده کنید.

=SUM(--(A2="بله"), --(B2="بله"), --(C2="بله"))=1

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

=IF(XOR(A2>0, B2>0), "درست", "نیاز به اصلاح")

• در فایل‌های اشتراکی، قبل از استفاده از XOR مطمئن شوید کاربران از نسخه‌های جدید اکسل استفاده می‌کنند.
• در Excel 365 می‌توانید از توابع جدیدتر مانند LET برای خوانایی بیشتر فرمول‌های پیچیده استفاده کنید.

=LET(شرط۱, A2>0, شرط۲, B2>0, XOR(شرط۱, شرط۲))

تفاوت تابع XOR با توابع مشابه

تفاوت XOR با OR

تابع OR زمانی TRUE می‌شود که حداقل یکی از شرط‌ها درست باشد. اما XOR در حالت دو شرطی فقط وقتی TRUE می‌شود که یکی از شرط‌ها درست و دیگری نادرست باشد.

=OR(TRUE, TRUE)

=XOR(TRUE, TRUE)

فرمول اول TRUE می‌دهد، اما فرمول دوم FALSE می‌دهد.

تفاوت XOR با AND

تابع AND زمانی TRUE می‌شود که همه شرط‌ها درست باشند. اما XOR زمانی TRUE می‌شود که تعداد شرط‌های درست فرد باشد.

=AND(TRUE, TRUE)

=XOR(TRUE, TRUE)

AND در این حالت TRUE می‌دهد، ولی XOR نتیجه FALSE برمی‌گرداند.

تفاوت XOR با IF

تابع IF برای تصمیم‌گیری و نمایش خروجی بر اساس یک شرط استفاده می‌شود، اما XOR خودش یک تابع منطقی برای بررسی رابطه بین چند شرط است. معمولاً XOR را داخل IF قرار می‌دهیم تا نتیجه قابل فهم‌تری نمایش دهیم.

=IF(XOR(A2="بله", B2="بله"), "معتبر", "نامعتبر")

تفاوت XOR با COUNTIF

تابع COUNTIF تعداد سلول‌هایی را می‌شمارد که یک شرط خاص را دارند. اما XOR بررسی می‌کند که وضعیت منطقی شرط‌ها چگونه است. این دو تابع می‌توانند با هم ترکیب شوند تا وجود یک مقدار در فقط یکی از دو لیست بررسی شود.

=XOR(COUNTIF(A:A, D2)>0, COUNTIF(B:B, D2)>0)

سازگاری با نسخه‌های مختلف اکسل

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

• Excel 365: پشتیبانی می‌شود.
• Excel 2021: پشتیبانی می‌شود.
• Excel 2019: پشتیبانی می‌شود.
• Excel 2016: پشتیبانی می‌شود.
• Excel 2013: پشتیبانی می‌شود.
• Excel for Web: پشتیبانی می‌شود.
• Excel for Mac نسخه‌های جدید: پشتیبانی می‌شود.
• Excel 2010 و نسخه‌های قدیمی‌تر: تابع XOR به صورت مستقیم در دسترس نیست.

اگر لازم است فایل شما در نسخه‌های قدیمی‌تر اکسل هم کار کند، می‌توانید از فرمول جایگزین زیر استفاده کنید:

=MOD(SUM(--(A2>0), --(B2>0)), 2)=1

سؤالات پرتکرار درباره تابع XOR

تابع XOR دقیقاً چه کاری انجام می‌دهد؟

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

آیا XOR همان OR است؟

خیر. OR اگر حداقل یک شرط درست باشد TRUE می‌دهد، اما XOR در حالت دو شرطی اگر هر دو شرط درست باشند FALSE می‌دهد.

اگر سه شرط در XOR داشته باشیم و هر سه درست باشند، نتیجه چیست؟

نتیجه TRUE است، چون تعداد شرط‌های درست ۳ و فرد است.

چطور بررسی کنم دقیقاً یکی از چند شرط درست است؟

برای این کار بهتر است از SUM روی شرط‌ها استفاده کنید و بررسی کنید حاصل برابر ۱ باشد.

=SUM(--(A2="بله"), --(B2="بله"), --(C2="بله"))=1

آیا می‌توان XOR را با IF ترکیب کرد؟

بله، این یکی از رایج‌ترین کاربردهای XOR است؛ چون می‌توانید به جای TRUE و FALSE پیام دلخواه نمایش دهید.

=IF(XOR(A2>0, B2>0), "معتبر", "نامعتبر")

چرا فرمول XOR من FALSE شده در حالی که چند شرط درست هستند؟

اگر تعداد شرط‌های TRUE زوج باشد، XOR نتیجه FALSE می‌دهد. مثلاً دو شرط درست یا چهار شرط درست باعث خروجی FALSE می‌شود.

جمع‌بندی و پیشنهاد یادگیری بعدی

تابع XOR یکی از توابع منطقی مفید در اکسل است که برای بررسی حالت‌های انحصاری کاربرد دارد. اگر می‌خواهید مطمئن شوید فقط یکی از دو شرط برقرار است، XOR انتخاب بسیار مناسبی است. این تابع در کنترل فرم‌ها، بررسی مغایرت داده‌ها، اعتبارسنجی اطلاعات و ترکیب با توابعی مانند IF، AND، OR، COUNTIF و XLOOKUP بسیار کاربردی است.

برای یادگیری بهتر، پیشنهاد می‌شود بعد از XOR با توابع منطقی IF، AND و OR بیشتر تمرین کنید. همچنین اگر با لیست‌ها و جستجوی داده کار می‌کنید، یادگیری COUNTIF و XLOOKUP می‌تواند قدرت فرمول‌نویسی شما را چند برابر کند.