نحوه انجام معیار در اکسل مقایسه داده ها در اکسل در ورق های مختلف. تغییر رنگ یا برجسته کردن موارد تکراری

بگویید می خواهید نسخه های یک کتاب کار را مقایسه کنید ، یک کتاب کار را برای مشکلات یا ناسازگاری تجزیه و تحلیل کنید یا پیوندهای بین کتابهای کار یا صفحه های کار را ببینید اگر Microsoft Office 365 یا Office Professional Plus 2013 روی رایانه شما نصب شده باشد ، افزونه Spreadsheet Enquire در Excel موجود است.

برای انجام همه این کارها و موارد دیگر می توانید از دستورات موجود در برگه پرس و جو استفاده کنید. تب Enquire در نوار اکسل دارای دکمه هایی برای دستورات شرح داده شده در زیر است.

اگر شما دیدن نمی کنید جویا شدن در نوار اکسل ، به بخش روشن کردن افزونه Spreadsheet Enquire مراجعه کنید.

دو کتاب کار را با هم مقایسه کنید

مقایسه فایلها این دستور به شما امکان می دهد تفاوت های سلول به سلول بین دو کتاب کار را ببینید. برای اجرای این دستور باید دو کتاب کار در اکسل باز داشته باشید.

نتایج با توجه به نوع محتوا ، مانند مقادیر وارد شده ، فرمول ها ، دامنه های نامگذاری شده و قالب ها ، کدگذاری می شوند. حتی یک پنجره وجود دارد که می تواند کد کد VBA را خط به خط تغییر دهد. تفاوت بین سلول ها در یک طرح شبکه آسان برای خواندن نشان داده شده است ، مانند این:

مقایسه فایلها دستور برای مقایسه این دو پرونده از Microsoft Spreadsheet Compare استفاده می کند. در ویندوز 8 ، می توانید Spreadsheet Compare را در خارج از اکسل با کلیک کردن شروع کنید صفحه گسترده مقایسه کنید در برنامه ها صفحه نمایش در ویندوز 7 ، روی Windows کلیک کنید شروع کنید دکمه و سپس\u003e همه برنامه ها > Microsoft Office 2013 > Office 2013 Tools > صفحه گسترده مقایسه 2013.

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

یک کتاب کار را تجزیه و تحلیل کنید

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

پیوندهای کتاب کار را نشان دهید

کتابهای کاری که از طریق مراجع سلول به سایر کتابهای کار متصل می شوند ، گیج کننده می شوند. برای ایجاد یک نقشه تعاملی و گرافیکی از وابستگی های کتاب کار ایجاد شده توسط اتصالات (پیوندها) بین پرونده ها ، استفاده کنید. انواع پیوندها در نمودار می تواند شامل سایر کتابهای کار ، پایگاه داده های دسترسی ، پرونده های متنی ، صفحات HTML ، پایگاه داده SQL Server و سایر منابع داده باشد. در نمودار رابطه ، می توانید عناصر را انتخاب کرده و اطلاعات بیشتری در مورد آنها پیدا کنید و خطوط اتصال را بکشید تا شکل نمودار تغییر کند.

این نمودار کتاب کار فعلی در سمت چپ و ارتباطات بین آن و سایر کتابهای کار و منابع داده را نشان می دهد. همچنین سطح بیشتری از اتصالات کتاب کار را نشان می دهد ، و به شما تصویری از ریشه داده ها برای کتاب کار می دهد.

پیوندهای صفحه کار را نشان دهید

تعداد زیادی کاربرگ که به هم بستگی دارند ، دارید؟ برای ایجاد یک نقشه تعاملی و گرافیکی از ارتباطات (پیوندها) بین کاربرگها در همان کتاب کار و سایر کتابهای کار استفاده کنید. این به شما کمک می کند تا تصویری واضح تر از چگونگی وابستگی داده های شما به سلولهای مکانهای دیگر ارائه دهید.

این نمودار روابط بین کاربرگها را در چهار کتاب کار مختلف ، با وابستگی بین کاربرگها در همان کتاب کار و همچنین پیوندهای بین کاربرگها در کتابهای مختلف ، نشان می دهد. هنگامی که نشانگر خود را روی گره ای از نمودار مانند صفحه کاری با نام "West" در نمودار قرار می دهید ، یک بالون حاوی اطلاعات ظاهر می شود.

روابط سلولی را نشان دهید

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

این نمودار دو سطح ارتباط سلول برای سلول A10 را در Sheet5 در Book1.xlsx نشان می دهد. این سلول در کتاب کار دیگری ، Book2.xlsx ، به سلول C6 برگه 1 وابسته است. این سلول سابقه چندین سلول در سایر صفحات کاری موجود در همان پرونده است.

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

قالب بندی سلول اضافی را تمیز کنید

تا به حال یک کتاب کار را باز کرده و پیدا کرده اید که به آرامی بارگیری می شود یا عظیم شده است؟ ممکن است قالب در ردیفها یا ستونهایی که از آنها آگاه نیستید اعمال شده باشد قالب بندی سلول اضافی را تمیز کنید دستور برای حذف قالب بندی اضافی و کاهش اندازه پرونده این به شما کمک می کند تا از "نفخ صفحه گسترده" که سرعت اکسل را بهبود می بخشد جلوگیری کنید.

رمزهای عبور را مدیریت کنید

اگر از ویژگیهای Inquire برای تجزیه و تحلیل یا مقایسه کتابهای کاری محافظت شده با رمز عبور استفاده می کنید ، باید رمز کار کتاب را به لیست رمز ورود خود اضافه کنید تا Inquire بتواند نسخه ذخیره شده کار شما را باز کند. استفاده از رمزهای کار کتاب دستور در جویا شدن برای اضافه کردن گذرواژه ها ، در رایانه ذخیره می شود. این رمزهای عبور رمزگذاری شده و فقط توسط شما قابل دسترسی است.

خواندن این مقاله حدود 10 دقیقه طول می کشد. در 5 دقیقه بعدی ، می توانید به راحتی دو ستون را در اکسل مقایسه کنید و ببینید آیا در آنها نسخه های کپی وجود دارد ، آنها را حذف کنید یا آنها را با رنگ برجسته کنید. وقتش رسیده!

اکسل یک برنامه بسیار قدرتمند و بسیار جالب برای ایجاد و دستکاری مقادیر زیادی داده است. اگر چندین کتاب کار با داده (یا فقط یک جدول بزرگ) دارید ، احتمالاً می خواهید 2 ستون را مقایسه کنید ، مقادیر تکراری را پیدا کنید و سپس با آنها کاری انجام دهید ، به عنوان مثال محتویات را حذف ، برجسته یا پاک کنید. ... ستون ها می توانند در یک جدول باشند ، مجاور باشند یا مجاور نباشند ، می توانند در 2 صفحه مختلف یا حتی در کتاب های مختلف قرار بگیرند.

تصور کنید که ما 2 ستون از نام افراد داریم - 5 نام در یک ستون آ و 3 نام در یک ستون ب... مقایسه نام در این دو ستون و یافتن موارد تکراری ضروری است. همانطور که می فهمید ، این داده های ساختگی است که فقط به عنوان مثال در نظر گرفته شده است. در جداول واقعی ، ما با هزاران ، یا حتی ده ها هزار رکورد سر و کار داریم.

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

گزینه B: ستون ها در ورق های مختلف قرار دارند. به عنوان مثال ستون آ روی ورق ورق 2 و ستون آ روی ورق ورق 3.

اکسل 2013 ، 2010 و 2007 دارای یک ابزار داخلی است کپی را حذف کنید (کپی ها را حذف کنید) ، اما در چنین شرایطی ناتوان است ، زیرا نمی تواند داده ها را در 2 ستون مقایسه کند. علاوه بر این ، فقط می تواند موارد تکراری را حذف کند. گزینه های دیگری مانند برجسته سازی یا تغییر رنگ وجود ندارد. و نکته!

2 ستون را در Excel مقایسه کنید و سوابق تکراری را با استفاده از فرمول پیدا کنید

گزینه A: هر دو ستون در یک صفحه قرار دارند

سریع: اگر از میانبرهای صفحه کلید استفاده کنید ، در جداول بزرگ ، کپی کردن فرمول سریعتر خواهد بود. یک سلول را انتخاب کنید C1 و فشار دهید Ctrl + C (برای کپی فرمول در کلیپ بورد) ، سپس را فشار دهید Ctrl + Shift + End (برای انتخاب تمام سلولهای غیر خالی در ستون C) و در آخر فشار دهید Ctrl + V (برای درج فرمول در تمام سلولهای انتخاب شده).

گزینه B: دو ستون در ورق های مختلف قرار دارند (در کتاب های مختلف)

در حال پردازش موارد تکراری است

عالی است ، ورودی هایی را در ستون اول پیدا کردیم که در ستون دوم نیز وجود دارد. اکنون باید کاری با آنها انجام دهیم. مشاهده همه رکوردهای تکراری در یک جدول به صورت دستی ناکارآمد و زمانبر است. راه های بهتری وجود دارد.

فقط ردیف های تکراری را در ستون A نشان دهید

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

کلیک راست کرده و از منوی زمینه انتخاب کنید قرار دادن (درج):

برای ستون ها نام بگذارید ، به عنوان مثال ، " نام"و" تکراری؟"سپس برگه را باز کنید داده ها (داده) و فشار دهید فیلتر (فیلتر):

سپس بر روی پیکان کوچک خاکستری کنار "کلیک کنید تکراری؟"برای گسترش منوی فیلتر ؛ علامت همه موارد موجود در این لیست را بردارید ، به جز کپی کردن، و فشار دهید خوب.

این همه ، اکنون شما فقط آن عناصر ستون را مشاهده می کنید وکه در ستون کپی می شوند در... در جدول آموزش ما فقط دو سلول وجود دارد ، اما همانطور که می فهمید ، در عمل تعداد بیشتری از آنها وجود دارد.

برای نمایش مجدد تمام ردیف های یک ستون و، روی نماد فیلتر در ستون کلیک کنید درکه اکنون مانند یک قیف با یک پیکان کوچک به نظر می رسد و انتخاب کنید انتخاب همه (انتخاب همه). متناوباً ، می توانید همین کار را از طریق Feed با کلیک کردن انجام دهید داده ها (داده ها)\u003e انتخاب و فیلتر کردن (مرتب سازی و فیلتر کردن)\u003e پاک کردن (پاک کردن) همانطور که در تصویر زیر نشان داده شده است:

تغییر رنگ یا برجسته کردن موارد تکراری

اگر علائم " کپی کردن”برای اهداف شما کافی نیست و شما می خواهید سلولهای تکرار شده را با رنگ فونت متفاوت ، رنگ پر یا به روش دیگری علامت گذاری کنید ...

در این حالت ، کپی ها را مانند تصویر بالا فیلتر کنید ، تمام سلول های فیلتر شده را انتخاب کنید و کلیک کنید Ctrl + 1برای باز کردن گفتگو سلولها را قالب بندی کنید (قالب سلول). به عنوان مثال ، بیایید رنگ پر شدن سلولهای خطوط تکراری را به زرد روشن تغییر دهیم. البته می توانید رنگ پر را با ابزار تغییر دهید پر کنید (پر رنگ) روی برگه خانه (صفحه اصلی) اما مزیت کادر محاوره ای است سلولها را قالب بندی کنید (قالب سلول) به این معنی است که همه گزینه های قالب بندی می توانند به طور همزمان پیکربندی شوند.

اکنون قطعاً حتی یک سلول تکراری را از دست نخواهید داد:

حذف مقادیر تکراری از ستون اول

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

اگر 2 ستونی که مقایسه می کنید روی صفحات مختلف است، یعنی در جداول مختلف ، روی محدوده انتخاب شده کلیک راست کرده و از فهرست انتخاب کنید حذف ردیف (حذف خط):

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

اگر 2 ستون در یک صفحه باشد، نزدیک به هم (مجاور) یا نزدیک به هم (نه مجاور) ، روند حذف کپی ها کمی دشوارتر خواهد بود. ما نمی توانیم کل سطر را با مقادیر تکراری حذف کنیم ، زیرا این سلول ها را از ستون دوم نیز حذف می کند. بنابراین فقط ورودی های منحصر به فرد را در ستون بگذارید و، این کار را انجام دهید:

همانطور که مشاهده می کنید ، حذف نسخه های کپی از دو ستون در Excel با استفاده از فرمول ها کار چندان دشواری نیست.

سوال کاربر

سلام!

من یک وظیفه دارم و برای سومین روز است که مغزهایم را جمع می کنم - من نمی دانم چگونه آن را کامل کنم. 2 جدول وجود دارد (در هر جدول حدود 500-600 ردیف) ، شما باید یک ستون با نام یک محصول را از یک جدول بگیرید و آن را با نام یک محصول دیگر مقایسه کنید ، و اگر محصولات مطابقت دارد ، مقدار را از جدول 2 به جدول 1 کپی و جایگزین کنید. ، اما فکر می کنم مشکل را از عکس درک خواهید کرد ( تقریباً : برش عکس توسط سانسور ، هنوز اطلاعات شخصی).

پیشاپیش از شما متشکرم. آندری ، مسکو.

روز همگی بخیر!

آنچه توصیف کردید ، به وظایف نسبتاً پرطرفداری اشاره دارد که حل آنها با استفاده از اکسل نسبتاً آسان و سریع است. کافی است دو جدول خود را به داخل برنامه هدایت کرده و از عملکرد VLOOKUP استفاده کنید. در مورد کار او در زیر ...

نمونه ای از کار با عملکرد VLOOKUP

به عنوان مثال ، من دو قرص کوچک گرفتم ، آنها در تصویر زیر ارائه شده است. در جدول اول (ستون ها) الف ، ب - محصول و قیمت) هیچ اطلاعاتی در مورد ستون موجود نیست ب؛ در مرحله دوم ، هر دو ستون پر می شوند (محصول و قیمت). اکنون باید ستون های اول را در هر دو جدول بررسی کنید و در صورت یافتن تطبیق ، به طور خودکار قیمت را در صفحه اول کپی کنید. به نظر می رسد که کار ساده است ...

چگونه انجامش بدهیم...

نشانگر ماوس را در سلول قرار می دهیم B2 - یعنی در سلول اول ستون ، جایی که هیچ مقداری نداریم و فرمول را می نویسیم:

\u003d VLOOKUP (A2 ، $ E $ 1: $ F $ 7،2 $ ، نادرست)

A2 - مقدار ستون اول جدول اول (آنچه در ستون اول جدول دوم به دنبال آن خواهیم بود) ؛

$ E $ 1: $ F $ 7 - جدول دوم کاملاً انتخاب شده (که می خواهیم چیزی را در آن پیدا و کپی کنیم). به نماد "$" توجه کنید - لازم است تا سلولهای جدول دوم انتخاب شده هنگام کپی فرمول تغییر نکنند.

2 - تعداد ستونی که از آن مقدار کپی می شود (توجه داشته باشید که جدول دوم انتخاب شده ما فقط 2 ستون دارد. اگر دارای 3 ستون باشد ، می توان مقدار را از ستون 2 یا 3 کپی کرد).

دروغ گویی - ما به دنبال یک مسابقه دقیق هستیم (در غیر این صورت اولین مشابه تعویض می شود ، که واضح است که به درد ما نمی خورد).

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

برای تعیین قیمت برای نام های محصول دیگر ، فقط فرمول را به سلولهای دیگر بکشید (کپی کنید). مثال زیر را ببینید.

همانطور که مشاهده می کنید ، پس از آن ، ستون های اول جداول مقایسه می شوند: از ردیف هایی که مقادیر سلول با هم مطابقت دارند ، داده های مورد نیاز کپی و جایگزین می شوند. به طور کلی مشخص است که جداول می توانند بسیار بزرگتر باشند!

توجه داشته باشید : باید بگویم که عملکرد VLOOKUP از منابع کامپیوتر بسیار طاقت فرسا است. در بعضی موارد ، با داشتن یک سند بیش از حد بزرگ ، ممکن است مقایسه جداول مدت زیادی طول بکشد. در این موارد ، ارزش دارد که فرمولهای دیگر یا راه حلهای کاملاً متفاوتی را در نظر بگیرید (هر مورد جداگانه است).

این همه ، موفق باشید!

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

دو صفحه را در اکسل مقایسه کنید

این شرکت ممکن است بیش از صد کارمند داشته باشد ، برخی از آنها مرخص می شوند ، دیگران شغل پیدا می کنند ، دیگران به تعطیلات یا مرخصی استعلاجی می روند و ... در نتیجه ، مقایسه داده های حقوق ممکن است دشوار باشد. به عنوان مثال ، نام کارمندان دائماً در توالی دیگری قرار خواهد گرفت. چگونه می توان مقایسه دو جدول اکسل را در ورق های مختلف انجام داد؟

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

برای یافتن تغییرات در برگه های حقوق و دستمزد:

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

اصل مقایسه دو دامنه داده در Excel در ورق های مختلف:

در یک شرایط خاص ، عملکرد MATCH ضروری است. اولین استدلال آن شامل یک جفت مقدار است که باید در صفحه منبع ماه بعد ، یعنی "مارس" یافت شود. یک محدوده اسکن شده به عنوان ترکیبی از مقادیر محدوده مشخص شده توسط اسامی به جفت تعریف می شود. بنابراین ، مقایسه رشته در دو زمینه - نام خانوادگی و حقوق و دستمزد انجام می شود. برای موارد مطابقت یافته ، یک عدد بازگردانده می شود که در اصل برای اکسل صادق است. بنابراین ، شما باید از تابع \u003d NOT () استفاده کنید ، که به شما امکان می دهد مقدار TRUE را با FALSE جایگزین کنید. در غیر این صورت ، قالب بندی برای سلول هایی که مقادیر آنها مطابقت دارد اعمال می شود. برای هر جفت ارز پیدا نشده (یعنی یک عدم تطابق) و B2 و C2 $ در محدوده نام خانوادگی و حقوق ، MATCH خطایی را برمی گرداند. مقدار خطا مقدار بولی نیست. بنابراین ، ما از تابع IFERROR استفاده می کنیم که برای هر خطا یک مقدار منطقی اختصاص می دهد - TRUE. این امر تخصیص یک قالب جدید را فقط به سلولهای بدون تطبیق مقادیر حقوق و دستمزد نسبت به ماه بعد ، مارس ، ارتقا می دهد.

شاید همه افرادی که با داده ها در Excel کار می کنند با این س facedال روبرو شوند که چگونه می توان دو ستون را در اکسل برای همزمانی و اختلاف مقایسه کرد. راه های مختلفی برای این کار وجود دارد. بیایید نگاهی دقیق به هر یک از آنها بیندازیم.

نحوه مقایسه دو ستون در Excel ردیف به ردیف

هنگام مقایسه دو ستون با داده ها ، اغلب لازم است که داده ها را در هر ردیف جداگانه برای همزمانی یا اختلاف مقایسه کنید. با استفاده از تابع می توانیم این تحلیل را انجام دهیم. بیایید ببینیم که چگونه کار می کند در مثال های زیر.

مثال 1. نحوه مقایسه دو ستون برای مطابقت و تفاوت در یک ردیف

برای مقایسه داده ها در هر سطر از دو ستون در Excel ، یک فرمول ساده می نویسیم. فرمول باید در هر ردیف در ستون مجاور ، کنار جدولی که داده های اصلی در آن قرار دارد ، وارد شود. پس از ایجاد فرمولی برای اولین ردیف جدول ، می توانیم آن را در بقیه ردیف ها کشیده و کپی کنیم.

برای بررسی اینکه آیا دو ستون از یک ردیف دارای داده های یکسان هستند ، به فرمول زیر نیاز داریم:

\u003d IF (A2 \u003d B2 ، "مطابقت" ، "")

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

\u003d اگر (A2<>B2 ؛ "با هم مطابقت ندارند"؛ "")

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

\u003d IF (A2 \u003d B2 ، "مطابقت" ، "مطابقت ندارد")

\u003d اگر (A2<>B2 ؛ "با هم مطابقت ندارند"؛ "همخوانی داشتن")

یک مثال از یک نتیجه محاسبه ممکن است به صورت زیر باشد:

برای مقایسه داده ها در دو ستون از همان ردیف بزرگ با حساسیت ، از فرمول زیر استفاده کنید:

\u003d IF (مسابقه (A2 ، B2) ، "مطابقت" ، "منحصر به فرد")

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

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

• ردیف هایی با مقادیر یکسان را در تمام ستون های جدول پیدا کنید.
• ردیف هایی با مقادیر یکسان را در هر دو ستون جدول پیدا کنید.

مثال 1 چگونه می توان مسابقات را در یک ردیف در چند ستون جدول پیدا کرد

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

\u003d IF (AND (A2 \u003d B2؛ A2 \u003d C2)؛ "مطابقت"؛ "" ")

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

\u003d IF (COUNTIF ($ A2: $ C2؛ $ A2) \u003d 3؛ "مسابقات"؛ "")

در فرمول ، "5" تعداد ستونهای جدول است که فرمول را برای آنها ایجاد کرده ایم. اگر جدول شما بیشتر یا کمتر از ستون دارد ، این مقدار باید برابر با تعداد ستون ها باشد.

مثال 2. نحوه یافتن موارد منطبق در یک ردیف در هر دو ستون جدول

تصور کنید که وظیفه ما این است که از جدول دارای داده در چندین ستون ، آن سطرهایی را که داده ها همزمان یا در حداقل دو ستون تکرار می شوند ، شناسایی کنیم. توابع و کمک به ما در این زمینه. بیایید یک فرمول برای یک جدول متشکل از سه ستون داده بنویسیم:

\u003d IF (OR (A2 \u003d B2؛ B2 \u003d C2؛ A2 \u003d C2)؛ "مطابقت"؛ "")

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

\u003d IF (COUNTIF (B2: D2؛ A2) + COUNTIF (C2: D2؛ B2) + (C2 \u003d D2) \u003d 0؛ "رشته منحصر به فرد"؛ "رشته منحصر به فرد نیست")

\u003d IF (COUNTIF ($ B: $ B؛ $ A5) \u003d 0؛ "در ستون B هیچ تطبیقی \u200b\u200bوجود ندارد"؛ "در ستون B همخوانی وجود دارد")

این فرمول مقادیر موجود در ستون B را بررسی می کند تا ببیند آیا با داده های موجود در سلول های ستون A مطابقت دارند یا خیر.

اگر جدول شما از تعداد مشخصی ردیف تشکیل شده است ، می توانید یک محدوده روشن در فرمول تعیین کنید (به عنوان مثال ، B2 دلار: B10 دلار ) این فرمول را تسریع می کند.

نحوه مقایسه دو ستون در Excel برای مطابقت و برجسته کردن با رنگ

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

موارد منطبق را در چندین ستون در Excel پیدا و برجسته کنید

در مواردی که ما باید ستون ها را در چندین ستون پیدا کنیم ، برای این موارد ما نیاز داریم:

• ستون هایی را با داده هایی که می خواهید مطابقت ها را در آنها محاسبه کنید انتخاب کنید.
• در برگه "صفحه اصلی" در نوار ابزار ، روی مورد منوی "قالب بندی مشروط" -\u003e "قوانین انتخاب سلول" -\u003e "مقادیر تکراری" کلیک کنید ؛
• در کادر محاوره ای بازشو ، مورد "کپی" را در لیست کشویی سمت چپ انتخاب کنید ، در لیست کشویی سمت راست ، رنگ مقادیر تکراری را انتخاب کنید. روی دکمه "تأیید" کلیک کنید:
• پس از آن ، ستون انتخاب شده با رنگ مطابق برجسته می شود:

ردیفهای منطبق را در Excel پیدا کرده و برجسته کنید

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

جداول بالا حاوی همان داده ها هستند. تفاوت آنها این است که در مثال سمت چپ ، ما به دنبال سلولهای همسان هستیم و در سمت راست ، خطوط کامل تکرار شده داده را پیدا کردیم.

بیایید ببینیم چگونه می توان ردیف های منطبق را در جدول پیدا کرد:

• در سمت راست جدول با داده ، یک ستون کمکی ایجاد کنید ، در آن ، در مقابل هر ردیف با داده ، فرمولی قرار دهید که تمام مقادیر ردیف جدول را در یک سلول ترکیب کند:

\u003d A2 و B2 و C2 و D2

در ستون کمکی ، داده های جدول ترکیبی را مشاهده خواهید کرد:

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

• یک منطقه با داده را در ستون کمکی انتخاب کنید (در مثال ما ، این یک محدوده سلول است E2: E15 );
• در برگه "صفحه اصلی" در نوار ابزار ، روی مورد منوی "قالب بندی مشروط" -\u003e "قوانین انتخاب سلول" -\u003e "مقادیر تکراری" کلیک کنید ؛
• در کادر محاوره ای بازشو ، "کپی" را در لیست کشویی سمت چپ انتخاب کنید ، در لیست کشویی سمت راست ، انتخاب کنید که مقادیر تکراری چه رنگی برجسته شوند. روی دکمه "تأیید" کلیک کنید:
• پس از آن ، خطوط تکراری در ستون انتخاب شده برجسته می شوند: