این wikiHow به شما آموزش می دهد که چگونه از قالب بندی شرطی برای مقایسه همه سلول های دو لیست جداگانه در یک صفحه گسترده Excel استفاده کنید و سلولهایی را که در هر دو لیست ظاهر می شوند علامت گذاری کنید. این ویژگی فقط در نسخه دسکتاپ Excel موجود است و برنامه تلفن همراه از آن پشتیبانی نمی کند.
مراحل
روش 1 از 2: استفاده از فرمول CountIf
مرحله 1. صفحه گسترده Excel را که می خواهید ویرایش کنید باز کنید
فایل صفحه گسترده را با لیست هایی که می خواهید مقایسه کنید پیدا کنید و روی فایل دوبار کلیک کنید تا در Microsoft Excel باز شود.
مرحله 2. اولین لیست خود را انتخاب کنید
روی اولین سلول موجود در اولین لیست خود کلیک کنید و موس خود را تا انتهای سلول پایین لیست بکشید تا کل محدوده داده را انتخاب کنید.
مرحله 3. روی زبانه Formulas در نوار نوار ابزار کلیک کنید
می توانید این برگه را در بالای نوار ابزار در بالای صفحه گسترده خود پیدا کنید. این ابزار فرمول شما را روی نوار نوار ابزار باز می کند.
مرحله 4. روی Define Name در نوار ابزار کلیک کنید
می توانید این گزینه را در وسط نوار "فرمول ها" پیدا کنید. این پنجره جدید باز می شود و به شما امکان می دهد لیست خود را نامگذاری کنید.
مرحله 5. List1 را در قسمت Name تایپ کنید
روی قسمت نوشتاری در بالای پنجره بازشو کلیک کنید و نام لیست را در اینجا وارد کنید.
- بعداً می توانید از این نام برای وارد کردن لیست خود در فرمول مقایسه استفاده کنید.
- متناوبا ، می توانید لیست خود را در اینجا نام متفاوتی بگذارید. به عنوان مثال ، اگر این لیستی از مکان ها است ، می توانید نام آن را "location1" یا "locationList" بگذارید.
مرحله 6. در پنجره بازشو روی OK کلیک کنید
این عمل شما را تأیید می کند و لیست شما را نامگذاری می کند.
مرحله 7. لیست دوم خود را به عنوان List2 نامگذاری کنید
همان مراحل فهرست اول را دنبال کنید و نامی را برای لیست دوم خود انتخاب کنید. این به شما امکان می دهد بعداً سریعاً از این لیست دوم در فرمول مقایسه خود استفاده کنید.
می توانید هر نامی که می خواهید به این لیست بدهید. اطمینان حاصل کنید که نامی را که به هر یک از لیستهای خود می دهید در اینجا به خاطر بسپارید یا یادداشت کنید
مرحله 8. اولین لیست خود را انتخاب کنید
روی اولین سلول موجود در اولین لیست کلیک کنید و برای انتخاب کل محدوده داده ، آن را به پایین بکشید.
قبل از شروع تنظیم قالب بندی مشروط خود ، مطمئن شوید اولین لیست شما انتخاب شده است
مرحله 9. روی زبانه Home در نوار ابزار کلیک کنید
این اولین برگه در گوشه سمت چپ بالای نوار نوار ابزار است. این ابزارهای گسترده صفحه گسترده شما را در نوار ابزار باز می کند.
مرحله 10. روی نوار ابزار روی Conditional Formatting کلیک کنید
این گزینه شبیه یک نماد صفحه گسترده کوچک است که برخی از سلول ها به رنگ قرمز و آبی مشخص شده اند. این یک منوی کشویی از همه گزینه های قالب بندی شما را باز می کند.
مرحله 11. روی New Rule در منوی کشویی کلیک کنید
این پنجره جدید باز می شود و به شما امکان می دهد یک قانون قالب بندی جدید برای محدوده انتخاب شده را به صورت دستی تنظیم کنید.
مرحله 12. گزینه "استفاده از فرمول برای تعیین اینکه کدام سلول ها باید قالب بندی شوند" را انتخاب کنید
این گزینه به شما امکان می دهد فرمول قالب بندی را به صورت دستی تایپ کنید تا دو لیست خود را مقایسه کنید.
- بر پنجره ها ، آن را در انتهای لیست قوانین در کادر "Select a Rule Type" پیدا خواهید کرد.
- بر مک ، انتخاب کنید کلاسیک در منوی کشویی "Style" در بالای پنجره بازشو. سپس ، این گزینه را در منوی کشویی دوم در زیر منوی Style پیدا کنید.
مرحله 13. روی فیلد فرمول در پنجره بازشو کلیک کنید
برای تنظیم یک قانون قالب بندی مشروط ، می توانید هر فرمول معتبر Excel را در اینجا وارد کنید.
مرحله 14 = countif (List2، A1) = 1 را در نوار فرمول وارد کنید
این فرمول دو لیست شما را اسکن می کند و تمام سلول های لیست اول شما را که در لیست دوم نیز وجود دارد علامت گذاری می کند.
- A1 را در فرمول با اولین سلول از اولین لیست خود جایگزین کنید.
- به عنوان مثال ، اگر اولین سلول از اولین لیست شما سلول D5 باشد ، فرمول شما شبیه = countif (List2 ، D5) = 1 خواهد بود.
- اگر نام دیگری به لیست دوم خود داده اید ، مطمئن شوید که List2 موجود در فرمول را با نام واقعی لیست خود جایگزین کرده اید.
- اگر می خواهید سلولهایی را علامت گذاری کنید ، فرمول را به = countif (List2 ، A1) = 0 تغییر دهید. انجام ندهید در لیست دوم ظاهر می شود
مرحله 15 = countif (List1، B1) = 1 را در نوار فرمول (اختیاری) وارد کنید
اگر می خواهید سلول های خود را پیدا کرده و علامت گذاری کنید دومین لیستی که در لیست اول ظاهر می شود ، از این فرمول به جای اولین مورد استفاده کنید.
List1 را با نام اولین لیست خود و B1 را با اولین سلول از لیست دوم خود جایگزین کنید
مرحله 16. یک قالب سفارشی برای علامت گذاری سلول ها (اختیاری) انتخاب کنید
برای علامت گذاری سلول هایی که فرمول شما پیدا می کند ، می توانید یک رنگ سفارشی برای پر کردن پس زمینه و سبک های مختلف فونت انتخاب کنید.
- بر پنجره ها ، کلیک کنید بر روی قالب بندی در پایین سمت راست پنجره بازشو کلیک کنید. می توانید یک رنگ پس زمینه را در برگه "Fill" و سبک های فونت را در برگه "Font" انتخاب کنید.
- بر مک ، یک قالب از پیش تعیین شده را در منوی کشویی "Format with" در پایین انتخاب کنید. همچنین می توانید انتخاب کنید قالب سفارشی در اینجا برای انتخاب دستی پر کردن پس زمینه و سبک های فونت.
مرحله 17. در پنجره بازشو روی OK کلیک کنید
با این کار فرمول مقایسه شما تأیید و اعمال می شود. تمام سلول های لیست اول شما که در لیست دوم نیز ظاهر می شوند با رنگ و فونت انتخابی شما مشخص می شوند.
- به عنوان مثال ، اگر رنگ قرمز روشن را با متن قرمز تیره انتخاب کنید ، همه سلول های تکراری در اولین لیست شما به این رنگ تبدیل می شوند.
- اگر از فرمول دوم بالا استفاده می کنید ، قالب بندی مشروط به جای سلول اول ، سلولهای تکراری موجود در لیست دوم شما را مشخص می کند.
روش 2 از 2: استفاده از فرمول VLookup
مرحله 1. صفحه گسترده Excel خود را باز کنید
فایل Excel را با لیست هایی که می خواهید مقایسه کنید پیدا کنید و روی نام یا نماد فایل دوبار کلیک کنید تا صفحه گسترده در Microsoft Excel باز شود.
مرحله 2. روی سلول خالی در کنار اولین مورد در لیست دوم خود کلیک کنید
لیست دوم خود را در صفحه گسترده بیابید و روی سلول خالی در کنار اولین مورد لیست در بالا کلیک کنید.
- می توانید فرمول VLookup خود را در اینجا وارد کنید.
- از طرف دیگر ، می توانید هر سلول خالی در صفحه گسترده خود را انتخاب کنید. این سلول تنها برای شما راحت تر خواهد بود که مقایسه خود را در کنار لیست دوم خود مشاهده کنید.
مرحله 3. نوع = vlookup (در سلول خالی
فرمول VLookup به شما این امکان را می دهد که همه موارد موجود در دو لیست جداگانه را با یکدیگر مقایسه کنید و ببینید آیا یک مقدار تکراری است یا مقدار جدید.
تا زمان تکمیل فرمول پرانتز فرمول را نبندید
مرحله 4. اولین مورد از لیست دوم خود را انتخاب کنید
بدون بستن پرانتز فرمول ، روی اولین مورد در لیست دوم خود کلیک کنید. این اولین سلول لیست دوم شما را در فرمول قرار می دهد.
مرحله 5. a ، کاما را در فرمول تایپ کنید
پس از انتخاب اولین سلول از لیست دوم ، یک کاما در فرمول تایپ کنید. بعداً می توانید محدوده مقایسه خود را انتخاب کنید.
مرحله 6. نگه دارید و کل اولین لیست خود را انتخاب کنید
این محدوده سلولی لیست اول شما را به قسمت دوم فرمول VLookup وارد می کند.
این به شما این امکان را می دهد که اولین لیست را برای مورد انتخاب شده از لیست دوم خود (اولین مورد در بالای لیست دوم) جستجو کنید و در صورت تکرار یا مقدار جدید بازگردانید
مرحله 7. یک کاما در فرمول تایپ کنید
این محدوده مقایسه را در فرمول شما قفل می کند.
مرحله 8. بعد از کاما فرمول 1 را وارد کنید
این عدد نشان دهنده شماره فهرست ستون شما است. از فرمول VLookup می خواهد ستون لیست واقعی را به جای ستون دیگری در کنار آن جستجو کند.
اگر می خواهید فرمول شما مقدار ستون را در کنار اولین لیست شما بازگرداند ، اینجا 2 را تایپ کنید
مرحله 9. یک کاما در فرمول تایپ کنید
با این کار شماره فهرست ستون (1) شما در فرمول VLookup قفل می شود.
مرحله 10. FALSE را در فرمول تایپ کنید
با این کار به دنبال مطابقت دقیق مورد جستجوی انتخاب شده (اولین مورد در بالای لیست دوم) به جای تطابق تقریبی جستجو می شود.
- به جای FALSE ممکن است از 0 استفاده کنید ، دقیقاً یکسان است.
- اگر می خواهید مطابقت تقریبی را جستجو کنید ، می توانید TRUE یا 1 را تایپ کنید.
مرحله 11. در انتها) فرمول را ببندید
اکنون می توانید فرمول خود را اجرا کنید و ببینید آیا مورد جستجوی انتخاب شده در لیست دوم شما یک مقدار تکراری یا جدید است.
به عنوان مثال ، اگر لیست دوم شما از B1 شروع شود و اولین لیست شما از سلول های A1 تا A5 باشد ، فرمول شما شبیه به = vlookup (B1 ، $ A $ 1: $ A $ 5 ، 1 ، false).
مرحله 12. Enter را فشار دهید یا . صفحه کلید خود را برگردانید.
این فرمول را اجرا می کند و اولین لیست خود را برای اولین مورد از لیست دوم خود جستجو می کند.
- اگر این مقدار تکراری است ، همان مقدار را دوباره در سلول فرمول مشاهده می کنید.
- اگر این مقدار جدید باشد ، می بینید " #N/A"اینجا چاپ شده
- به عنوان مثال ، اگر اولین لیست را برای "John" جستجو می کنید ، و اکنون "John" را در سلول فرمول مشاهده می کنید ، این مقدار تکرار است که در هر دو لیست ظاهر می شود. اگر "#N/A" را مشاهده می کنید ، این مقدار جدید در لیست دوم است.
مرحله 13. سلول فرمول خود را انتخاب کنید
پس از اجرای فرمول و مشاهده نتایج مورد اول ، روی سلول فرمول کلیک کنید تا آن را انتخاب کنید.
مرحله 14. روی نقطه سبز رنگ پایین و سمت راست سلول کلیک کرده و بکشید
با این کار سلول فرمول شما در امتداد لیست گسترش می یابد و فرمول را برای هر مورد لیست موجود در لیست دوم اعمال می کند.
- به این ترتیب می توانید همه موارد موجود در لیست دوم خود را با کل لیست اول خود مقایسه کنید.
- این اولین لیست شما را برای هر مورد در لیست دوم شما به صورت جداگانه جستجو می کند و نتیجه را در کنار هر سلول به طور جداگانه نشان می دهد.
- اگر می خواهید به جای "#N/A" یک نشانگر متفاوت برای مقادیر جدید ببینید ، از این فرمول استفاده کنید: = iferror (vlookup (B1 ، $ A $ 1: $ A $ 5 ، 1 ، false) ، "New Value"). با این کار "مقدار جدید" برای مقادیر جدید به جای "#N/A" چاپ می شود.
