اغلب کاربران اکسل یک وظیفه را برای مقایسه دو جداول یا لیست برای شناسایی تفاوت در آنها یا موارد گمشده دارند. هر کاربر با این کار به شیوه خود مقابله می کند، اما اغلب اوقات مقدار زیادی از زمان برای حل مسئله مشخص شده صرف می شود، زیرا همه رویکردهای این مشکل منطقی نیستند. در عین حال، الگوریتم های اثبات شده اثبات شده وجود دارد که اجازه می دهد لیست های مقایسه یا آرایه های جدول را در زمان نسبتا کوتاه با حداقل تلاش قابل توجهی داشته باشید. بیایید جزئیات این گزینه ها را در نظر بگیریم.
به همین ترتیب، می توانید داده ها را در جداول که در ورق های مختلف قرار دارند مقایسه کنید. اما در این مورد، مطلوب است که خطوط آنها شماره گذاری شده است. بقیه روش مقایسه تقریبا دقیقا همان چیزی است که در بالا شرح داده شده است، علاوه بر این واقعیت که هنگام ساخت فرمول، لازم است بین ورق ها تغییر کند. در مورد ما، بیان فرم زیر را دارد:
= b2 = list2! b2
به همین ترتیب، همانطور که مشاهده می کنیم، در مقابل مختصات داده ها، که در سایر ورق های دیگر قرار دارد، جایی که نتیجه مقایسه نمایش داده می شود، شماره ورق و علامت تعجب نشان داده شده است.
روش 2: انتخاب گروه های سلول
مقایسه را می توان با استفاده از ابزار جداسازی گروه های سلول انجام داد. با آن، شما همچنین می توانید لیست های هماهنگ و مرتب را مقایسه کنید. علاوه بر این، در این مورد، لیست ها باید در کنار یکدیگر در یک ورق قرار گیرند.
- آرایه های مقایسه را انتخاب کنید. به برگه "HOME" بروید بعد، روی نماد "Find and Select" کلیک کنید، که بر روی نوار در نوار ابزار ویرایش قرار دارد. فهرستی وجود دارد که در آن شما باید موقعیت "انتخاب گروه سلول ها را انتخاب کنید ...".
علاوه بر این، انتخاب گروه سلول ها را می توان به روش دیگری به دست آورد. این گزینه به ویژه برای کسانی که نسخه برنامه را قبل از اکسل 2007 دارند، مفید خواهد بود، زیرا روش از طریق دکمه "Find and Select" این برنامه ها را پشتیبانی نمی کند. آرایه های انتخابی که مایل به مقایسه هستند را انتخاب کنید و روی کلید F5 کلیک کنید.
- یک پنجره انتقال کوچک فعال شده است. روی دکمه "برجسته ..." کلیک کنید در گوشه پایین سمت چپ آن.
- پس از آن، هر دو گزینه فوق که شما انتخاب کرده اید، پنجره انتخاب گروه های سلولی راه اندازی شده است. سوئیچ را به موقعیت "برجسته در خط" نصب کنید. روی دکمه "OK" کلیک کنید.
- همانطور که می بینیم، پس از آن، مقادیر ناسازگار خطوط با رنگ های مختلف برجسته می شود. علاوه بر این، چگونه می توان از محتویات خط فرمول قضاوت کرد، این برنامه یک فعالیت فعال را تشکیل می دهد که در خطوط غیر هماهنگ نشان داده شده است.
روش 3: قالب بندی شرطی
شما می توانید با استفاده از روش قالب بندی شرطی مقایسه کنید. همانطور که در روش قبلی، مناطق مقایسه شده باید بر روی یک ورق کار اکسل تک باشد و با یکدیگر هماهنگ شوند.
- اول از همه، ما انتخاب می کنیم که منطقه ی جدولی به عنوان اصلی، و در چه چیزی به دنبال تفاوت است. آخرین بیایید در جدول دوم انجام دهیم. بنابراین، ما لیستی از کارگران را در آن تخصیص می دهیم. حرکت به برگه "صفحه اصلی"، بر روی دکمه "قالب بندی شرطی" کلیک کنید، که دارای یک مکان بر روی نوار در بلوک "سبک" است. از لیست کشویی، از طریق "مدیریت قوانین مدیریت" بروید.
- پنجره Manager Manager Manager فعال شده است. ما روی آن کلیک بر روی دکمه "Create Rule" کلیک میکنیم.
- در پنجره در حال اجرا، ما موقعیت "استفاده از فرمول" را انتخاب می کنیم. در فیلد "سلول فرمت"، یک فرمول حاوی آدرس های اول سلول های مقادیر ستون های مقادیر را بنویسید، از طریق علامت "برابر" جداگانه جدا شده است. درست قبل از این عبارت، این زمان علامت "=" را حفظ خواهد کرد. علاوه بر این، به تمام مختصات ستون در این فرمول، شما باید یک آدرس مطلق را اعمال کنید. برای انجام این کار، فرمول را با مکان نما تخصیص می دهیم و سه بار کلید F4 را کلیک می کنیم. همانطور که می بینیم، علامت دلار در نزدیکی تمام آدرس های ستون ظاهر شد، به این معنی که لینک ها را به مطلق تبدیل می کند. برای مورد خاص ما، فرمول فرم زیر را می گیرد:
= $ a2 $ d2
ما این عبارت را در قسمت فوق ثبت می کنیم. پس از آن، ما بر روی دکمه "فرمت ..." کلیک می کنیم.
- پنجره "فرمت سلول" فعال می شود. ما به برگه "پر کردن" می رویم. در اینجا در لیست رنگ ها انتخاب رنگ را متوقف کنید، که ما می خواهیم آن عناصر را که در آن داده ها مطابقت نداشته باشند، رنگ کنیم. روی دکمه "OK" کلیک کنید.
- بازگشت به پنجره قانون قالب بندی، روی دکمه OK کلیک کنید.
- پس از انتقال اتوماتیک به پنجره "مدیریت قوانین"، روی دکمه "OK" و در آن کلیک کنید.
- در حال حاضر در جدول دوم، عناصر که دارای داده هایی هستند که با مقادیر مربوطه از منطقه اول جدول متناقض هستند، در رنگ انتخاب شده برجسته می شوند.
راه دیگری برای اعمال قالب بندی شرطی برای انجام کار وجود دارد. مانند گزینه های قبلی، آن را به محل هر دو قسمت های هر دو در یک ورق نیاز دارد، اما در مقایسه با روش های قبلا شرح داده شده، وضعیت هماهنگ سازی یا داده های مرتب سازی اجباری نخواهد بود، که این گزینه را از قبلا شرح داده است.
- ما مجموعه ای از مناطق مورد نیاز برای مقایسه را تولید می کنیم.
- ما انتقال را به زبانه به نام "صفحه اصلی" انجام می دهیم. ما بر روی دکمه "قالب بندی شرطی" کلیک می کنیم. در لیست فعال، موقعیت "قوانین تخصیص سلول ها" را انتخاب کنید. در منوی بعدی، موقعیت "مقادیر تکراری" را ایجاد کنید.
- مقادیر تکراری راه اندازی می شود. اگر همه به درستی انجام می شود، در این پنجره تنها با کلیک بر روی دکمه "OK" باقی می ماند. اگر چه، اگر شما می خواهید، در زمینه مناسب این پنجره، شما می توانید رنگ دیگری از انتخاب را انتخاب کنید.
- پس از تولید اقدام مشخص شده، تمام عناصر تکراری در رنگ انتخاب شده برجسته می شوند. آن عناصری که هماهنگ نیستند، در رنگ اصلی خود رنگ می شوند (به طور پیش فرض سفید). بنابراین، شما می توانید بلافاصله بصری را ببینید تا ببینید چه تفاوت بین آرایه ها.
اگر می خواهید، شما می توانید، برعکس، عناصر متناقض را رنگ کنید، و شاخص هایی که مطابقت دارند، با پر کردن رنگ قبلی ترک کنید. در عین حال، الگوریتم عمل تقریبا یکسان است، اما در تنظیمات مقادیر تکراری در فیلد اول، به جای پارامتر "تکرار"، پارامتر "منحصر به فرد" را انتخاب کنید. پس از آن روی دکمه "OK" کلیک کنید.
بنابراین، دقیقا همان شاخص هایی است که هماهنگ نیستند.
درس: قالب بندی مشروط در اکسل
روش 4: فرمول جامع
همچنین داده ها را با کمک یک فرمول پیچیده مقایسه کنید، اساس آن عملکرد متر است. با استفاده از این ابزار، می توانید محاسبه کنید که هر عنصر از ستون انتخاب شده جدول دوم در ابتدا تکرار می شود.
شوراهای اپراتور به گروه آماری توابع اشاره می کنند. وظیفه آن این است که تعداد سلول ها را شمارش کنید، مقادیر که شرایط مشخص را برآورده می کنند. نحو این اپراتور این نوع را دارد:
= برنامه ها (محدوده؛ معیار)
این استدلال "محدوده" آدرس آرایه است که ارزش های همزمان را محاسبه می کند.
استدلال "معیارهای" وضعیت تصادف را مشخص می کند. در مورد ما، مختصات سلول های خاصی از منطقه اول جدول اول خواهد بود.
- ما اولین عنصر ستون اضافی را اختصاص می دهیم که در آن تعداد مسابقات محاسبه می شود. بعد، روی نماد "Insert Function" کلیک کنید.
- جادوگر توابع شروع می شود. به رده "آمار" بروید. پیدا کردن در لیست نام "Countecles". پس از انتخاب آن، روی دکمه "OK" کلیک کنید.
- پنجره آرگومان اپراتور شروع به کار کرد. همانطور که می بینیم، نام زمینه های این پنجره به نام استدلال ها مربوط می شود.
مکان نما را در قسمت "محدوده" نصب کنید. پس از آن، با نگه داشتن دکمه سمت چپ ماوس، تمام مقادیر ستون را با نام جدول دوم اختصاص دهید. همانطور که می بینید، مختصات بلافاصله به فیلد مشخص می شوند. اما برای اهداف ما، این آدرس باید توسط مطلق ساخته شود. برای انجام این کار، مختصات را در این زمینه برجسته کنید و روی کلید F4 کلیک کنید.
همانطور که می بینید، مرجع فرم مطلق را به دست آورد، که با حضور علائم دلار مشخص می شود.
سپس با نصب مکان نما به فیلد "معیار" بروید. با کلیک بر روی عنصر اول با آخرین نام در محدوده اول جدولی. در این مورد، پیوند پیوند را ترک کنید. پس از نمایش در این زمینه، می توانید بر روی دکمه "OK" کلیک کنید.
- نتیجه خروجی به عنصر ورق است. این برابر با شماره "1" است. این به این معنی است که در فهرست نام جدول دوم، نام "Grinev V. P."، که اولین بار در فهرست آرایه اول جدول است، یک بار رخ می دهد.
- حالا ما باید یک عبارت مشابه و برای تمام عناصر دیگر جدول اول ایجاد کنیم. برای انجام این کار، با استفاده از نشانگر پر کردن، با استفاده از نشانگر پر کردن، انجام خواهم داد، همانطور که قبلا انجام شده است. ما مکان نما را به قسمت پایین سمت راست عنصر برگ، که شامل عملکرد متر است، قرار داده و پس از تبدیل آن به نشانگر پر کردن، دکمه سمت چپ ماوس را فشار دهید و مکان نما را پایین بکشید.
- همانطور که می بینیم، این برنامه یک محاسبه مختصری را انجام داده است، مقایسه هر سلول از جدول اول با داده ها، که در محدوده جدول دوم قرار دارد. در چهار مورد، نتیجه "1" بود، و در دو مورد - "0". به این ترتیب، این برنامه نمی تواند دو مقدار را در جدول دوم پیدا کند که در آرایه اول جدول موجود است.
البته، این عبارت به منظور مقایسه شاخص های جدول، ممکن است در فرم موجود اعمال شود، اما ممکن است آن را بهبود بخشد.
ما ارزش هایی را که در جدول دوم وجود دارد، انجام خواهیم داد، اما در ابتدا در دسترس نیست، به عنوان یک لیست جداگانه نمایش داده می شود.
- اول از همه، ما فرمول ما را بازیافتیم، اما ما آن را یکی از استدلال های اپراتور خواهیم ساخت. برای انجام این کار، ما اولین سلول را برجسته می کنیم که اپراتور اپراتور است. در رشته فرمول در مقابل آن، ما بیان "اگر" بدون نقل قول و باز کردن براکت. علاوه بر این، به طوری که ما برای کار ساده تر بود، فرمول ها را در ردیف فرمول اختصاص داده و روی نماد "Insert Function" کلیک کنید.
- پنجره آرگومان تابع باز می شود اگر. همانطور که می بینید، اولین پنجره پنجره در حال حاضر با ارزیابی شورای اپراتور پر شده است. اما ما باید چیز دیگری را در این زمینه اضافه کنیم. ما نشانگر مکان نما و بیان موجود در حال حاضر اضافه "= 0" بدون نقل قول.
پس از آن، به میدان "معنی اگر حقیقت" بروید. در اینجا ما از یک تابع توزیع شده دیگر استفاده خواهیم کرد. کلمه "رشته" را بدون نقل قول وارد کنید، سپس براکت ها را باز کنید و مختصات سلول اول را با نام خانوادگی در جدول دوم نشان دهید، پس از آن ما براکت را بستیم. به طور خاص، در مورد ما در زمینه "معنی اگر درست"، عبارت زیر اتفاق افتاد:
ردیف (D2)
در حال حاضر String اپراتور توابع را گزارش می دهد اگر شماره خط که نام خانوادگی خاص واقع شده است، و در مورد زمانی که وضعیت مشخص شده در فیلد اول انجام می شود، عملکرد اگر این شماره نمایش داده شود. روی دکمه "OK" کلیک کنید.
- همانطور که می بینید، نتیجه اول به عنوان "دروغ" نمایش داده می شود. این به این معنی است که ارزش شرایط اپراتور را برآورده نمی کند. به عبارت دیگر، اولین نام خانوادگی در هر دو لیست وجود دارد.
- با استفاده از نشانگر پر کردن، که در حال حاضر آشنا است برای کپی بیان اپراتور اگر در کل ستون. همانطور که می بینیم، در دو موقعیت که در جدول دوم حضور دارند، اما نه در ابتدا، فرمول شماره های ردیف را رد می کند.
- ما از منطقه جداول به سمت راست عقب نشینی می کنیم و ستون شماره را به ترتیب پر می کنیم، از 1. از 1. تعداد اعداد باید تعداد ردیف ها را در جدول دوم کامپایلر مطابقت دهند. برای سرعت بخشیدن به روش شماره، شما همچنین می توانید از نشانگر پر کنید.
- پس از آن، ما اولین سلول را به سمت راست بلندگو با اعداد برجسته می کنیم و روی نماد "Insert Function" کلیک می کنیم.
- جادوگر باز می شود به رده "آمار" بروید و نام "کوچکترین" نام را تولید کنید. روی دکمه "OK" کلیک کنید.
- این تابع کوچکترین است، استدلال استدلال های آن افشا شده است، در نظر گرفته شده است تا کوچکترین ارزش مشخص شده را از بین ببرد.
در فیلد "آرایه"، باید مختصات طیف وسیعی از ستون اختیاری "تعداد مسابقات" را مشخص کنید، که ما قبلا با استفاده از عملکرد آن را تغییر دادیم. ما تمام لینک ها را مطلق می کنیم.
در فیلد "K"، نشان داده شده است که در حساب کوچکترین ارزش باید نمایش داده شود. در اینجا ما مختصات سلول اول ستون را با شماره گذاری نشان می دهیم که اخیرا اضافه شده است. آدرس را ترک کنید روی دکمه "OK" کلیک کنید.
- اپراتور نتیجه را نمایش می دهد - شماره 3 دقیقا کوچکترین تعداد خطوط متناوب آرایه های جدول است. با استفاده از نشانگر پر کردن، فرمول را به خود بینی کپی کنید.
- در حال حاضر، دانستن تعداد ردیف عناصر غیر قابل تشخیص، ما می توانیم به سلول و مقادیر آنها با استفاده از تابع شاخص وارد کنیم. اولین عنصر ورق حاوی فرمول را انتخاب کنید کوچکترین است. پس از آن، به فرمول های خط بروید و قبل از نام "کوچکترین" اضافه کردن نام "index" بدون نقل قول، بلافاصله براکت را باز کنید و یک نقطه را با کاما بگذارید (؛). سپس نام فرمول "index" را در خط تخصیص می دهیم و روی نماد "Paste Function" کلیک می کنیم.
- پس از آن، یک پنجره کوچک باز می شود، که در آن لازم است تعیین کنید که آیا نمای مرجع باید یک تابع شاخص داشته باشد یا طراحی شده برای کار با آرایه ها باشد. ما نیاز به یک گزینه دوم داریم این به طور پیش فرض نصب شده است، به طوری که در این پنجره به سادگی بر روی دکمه "OK" کلیک کنید.
- پنجره استدلال عملکرد شاخص را اجرا می کند. این اپراتور برای تولید یک مقدار که در یک آرایه خاص در خط مشخص شده طراحی شده است طراحی شده است.
همانطور که می بینید، فیلد "ردیف شماره" در حال حاضر با مقادیر معنی کوچکترین عملکرد پر شده است. از ارزش موجود وجود دارد، تفاوت بین شماره ورق اکسل و شماره گیری داخلی منطقه جدولی. همانطور که می بینیم، ما فقط کلاه بر روی مقادیر جدول داریم. این به این معنی است که تفاوت یک خط است. بنابراین، اضافه کردن "-1" بدون نقل قول در قسمت "ردیف شماره".
در فیلد "آرایه"، آدرس ارزش مقادیر جدول دوم را مشخص کنید. در عین حال، تمام مختصات مطلق را مطرح می کنند، یعنی، ما علامت دلار را قبل از روش توصیف شده توسط ما قرار می دهیم.
روی دکمه "OK" کلیک کنید.
- پس از خروج، نتیجه بر روی صفحه نمایش، عملکرد را با استفاده از نشانگر پر کردن به انتهای ستون کشش می دهد. همانطور که می بینیم، هر دو نام خانوادگی که در جدول دوم حضور دارند، اما در ابتدا در دسترس نیستند، در محدوده جداگانه حذف می شوند.
روش 5: مقایسه آرایه ها در کتاب های مختلف
هنگام مقایسه محدوده ها در کتاب های مختلف، می توانید از روش های بالا استفاده کنید، به استثنای آن گزینه هایی که در آن قرار دادن هر دو قسمت جدولی در یک ورق مورد نیاز است. شرایط اصلی روش مقایسه در این مورد، باز کردن پنجره های هر دو فایل در همان زمان است. برای نسخه های اکسل 2013 و بعدا، و همچنین برای نسخه های اکسل 2007، هیچ مشکلی با اجرای این شرایط وجود ندارد. اما در اکسل 2007 و اکسل 2010 به منظور باز کردن هر دو پنجره در همان زمان، دستکاری های اضافی مورد نیاز است. چگونه این کار را انجام دهیم در یک درس جداگانه می گوید.
درس: نحوه باز کردن اکسل در پنجره های مختلف
همانطور که می بینید، تعدادی از امکانات برای مقایسه جداول با یکدیگر وجود دارد. چه نوع گزینه ای برای استفاده بستگی دارد به جایی که داده های جدول نسبت به یکدیگر (در یک ورق، در کتاب های مختلف، در ورق های مختلف) واقع شده است، و همچنین از کاربر می خواهد این مقایسه را بر روی صفحه نمایش نمایش داده شود.