خواندن این مقاله حدود 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 با کلیک کردن انجام دهید داده ها(داده ها)> انتخاب و فیلتر کردن(مرتب سازی و فیلتر کردن)> پاک کردن(پاک کردن) همانطور که در تصویر زیر نشان داده شده است:

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

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

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

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

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

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

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

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

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

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

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

چند روش برای مقایسه مناطق جدول در Excel وجود دارد ، اما همه آنها را می توان به سه گروه بزرگ تقسیم کرد:

مقایسه لیست ها در همان صفحه.

مقایسه جداول واقع در ورق های مختلف.

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

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

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

روش 1: فرمول ساده

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

بنابراین ، ما دو جدول ساده با لیست کارمندان شرکت و حقوق آنها داریم. مقایسه لیست کارمندان و شناسایی ناسازگاری بین ستونهایی که در آنها اسامی قرار داده شده است ، ضروری است.

1. برای این منظور به یک ستون اضافی در صفحه نیاز داریم. در آنجا تابلویی وارد می کنیم «=» ... سپس بر روی اولین مورد کلیک می کنیم تا در لیست اول مقایسه شود. نماد را دوباره بگذارید «=» از صفحه کلید بعد ، روی سلول اول ستونی که در جدول دوم مقایسه می کنیم کلیک کنید. نتیجه بیان از نوع زیر است:

   

   اگرچه ، البته ، در هر حالت مختصات متفاوت خواهد بود ، اما ماهیت همان باقی خواهد ماند.

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



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

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



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



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



6. در پنجره جادوگران عملکرددر گروه اپراتورها "ریاضی"نام را برجسته کنید محصول... روی دکمه کلیک کنید "خوب".



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

   SUMPRODUCT (آرایه 1 ؛ آرایه 2 ؛ ...)

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

   مکان نما را در میدان قرار می دهیم "Array1"و محدوده داده مقایسه شده را در صفحه اول در صفحه انتخاب کنید. پس از آن ، در فیلد علامت را قرار می دهیم "نا برابر" (<> ) و محدوده مقایسه شده منطقه دوم را انتخاب کنید. بعد ، عبارت حاصل را درون پرانتز می پیچیم ، و قبل از آن دو علامت قرار می دهیم «-» ... در مورد ما ، عبارت زیر را بدست آوردیم:

   - (A2: A7<>D2: D7)

   روی دکمه کلیک کنید "خوب".



8. اپراتور نتیجه را محاسبه و نمایش می دهد. همانطور که مشاهده می کنید ، در مورد ما ، نتیجه برابر با عدد است "یک"یعنی اینکه به این معنی است که یک عدم تطابق در لیست های مقایسه شده وجود دارد. اگر لیست ها کاملاً یکسان باشند ، نتیجه برابر با عدد خواهد بود «0» .

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

B2 = ورق 2! B2

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

روش 2: گروه های سلول را انتخاب کنید

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

روش 3: قالب بندی شرطی

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

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

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

بنابراین ، دقیقاً آن شاخص هایی که با هم مطابقت ندارند برجسته می شوند.

روش 4: فرمول پیچیده

همچنین می توانید داده ها را با استفاده از یک فرمول پیچیده بر اساس عملکرد مقایسه کنید COUNTIF... با استفاده از این ابزار می توانید تعداد ستون های انتخاب شده جدول دوم را در جدول اول تکرار کنید.

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

COUNTIF (دامنه ، معیار)

بحث و جدل "دامنه"آدرس آرایه ای است که مقادیر تطبیق در آن محاسبه می شود.

بحث و جدل "معیار"یک شرط مطابقت را مشخص می کند. در مورد ما ، این مختصات سلولهای خاص را در منطقه جدول اول نشان می دهد.

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

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

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



2. پنجره آرگومان های عملکرد را باز می کند اگر یک... همانطور که مشاهده می کنید ، قسمت اول پنجره از قبل با مقدار عملگر پر شده است COUNTIF... اما باید چیز دیگری به این زمینه اضافه کنیم. مکان نما را در آنجا تنظیم کرده و به عبارت موجود اضافه می کنیم «=0» بدون نقل قول.

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

   ROW (D2)

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



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



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



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



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



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



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

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

   در زمینه "K"کوچکترین مقدار نمایش داده شده را نشان می دهد. در اینجا مختصات سلول اول ستون را با شماره گذاری نشان می دهیم که اخیراً اضافه کرده ایم. آدرس را نسبی بگذارید. روی دکمه کلیک کنید "خوب".



9. اپراتور نتیجه را - یک عدد 3 ... این کوچکترین شماره ردیف های ناسازگار در آرایه های جدول است. با استفاده از دسته پر کردن ، فرمول را در پایین آن کپی کنید.



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



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



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

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

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

    روی دکمه کلیک کنید "خوب".



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

روش 5: مقایسه آرایه ها در کتاب های مختلف

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

این مقاله پاسخ س toالات زیر را ارائه می دهد:

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

هنگام کار با مقدار زیادی اطلاعات ، ممکن است کاربر با چنین وظیفه ای مانند مقایسه دو منبع داده جدولی روبرو شود. هنگام ذخیره سازی داده ها در یک سیستم حسابداری واحد (به عنوان مثال سیستم های مبتنی بر 1C Enterprise ، سیستم هایی که از پایگاه داده SQL استفاده می کنند) ، می توان از قابلیت های تعبیه شده در سیستم یا DBMS برای مقایسه داده ها استفاده کرد. به عنوان یک قاعده ، برای این کافی است که یک برنامه نویس را درگیر کنید که برای پایگاه داده یا مکانیزم گزارش نرم افزار جستجو کند. کاربر باتجربه ای که مهارت نوشتن درخواست های 1C یا SQL را دارد ، می تواند یک پرس و جو را نیز مدیریت کند.

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

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

بیایید راه حل مشکل مقایسه جداول در اکسل را با استفاده از یک مثال در نظر بگیریم. ما دو جدول داریم که شامل لیست آپارتمان ها هستند. منابع تخلیه - 1C Enterprise (حسابداری ساخت و ساز) و یک جدول در Excel (حسابداری فروش). جداول به ترتیب در کتاب کار اکسل در صفحه اول و دوم قرار گرفته اند.

وظیفه ما مقایسه این لیست ها با آدرس است. جدول اول شامل تمام آپارتمانهای خانه است. جدول دوم فقط شامل آپارتمانهای فروخته شده و نام خریدار است. هدف نهایی نمایش نام خریدار در جدول اول برای هر آپارتمان (برای آن دسته از آپارتمان هایی که فروخته شده اند) است. این کار با این واقعیت پیچیده است که آدرس آپارتمان در هر جدول ساختاری است و از چندین زمینه تشکیل شده است: 1) آدرس ساختمان (خانه) ، 2) بخش (ورودی) ، 3) طبقه ، 4) شماره روی زمین (به عنوان مثال ، از 1 تا 4) ...

برای مقایسه دو جدول اکسل ، باید اطمینان حاصل کنیم که هر سطر در هر دو جدول توسط یک زمینه مشخص می شود ، نه چهار. با اتصال مقادیر چهار قسمت آدرس با تابع () Concatenate می توانید چنین فیلدی را بدست آورید. هدف از تابع Concatenate ترکیب چندین مقدار متن در یک خط است. مقادیر موجود در تابع از طریق نماد "؛" شمارش می شوند. مقادیر می تواند آدرس سلول یا متن دلخواه مشخص شده در نقل قول باشد.

مرحله 1بیایید یک ستون خالی "A" را در ابتدای جدول اول وارد کنیم و فرمول را در سلول این ستون مقابل خط اول با داده بنویسیم:
= CONCATENATE (B3؛ "-"؛ C3؛ "-"؛ D3؛ "-"؛ E3)
برای سهولت درک بصری ، بین مقادیر سلول ها برای ادغام ، نمادها را "-" تنظیم می کنیم.

گام 2.فرمول را در سلولهای زیر در ستون A کپی کنید.

مرحله 4برای مقایسه جداول اکسل بر اساس مقادیر ، از تابع () VLOOKUP استفاده کنید. هدف از تابع VLOOKUP یافتن مقداری در ستون سمت چپ جدول و برگرداندن مقدار سلول واقع در ستون مشخص شده همان ردیف است. اولین پارامتر مقدار مورد نظر است. پارامتر دوم جدولی است که مقدار در آن جستجو می شود. پارامتر سوم تعداد ستونی است که مقدار از آن در سطر پیدا شده بازگردانده می شود. پارامتر چهارم نوع جستجو است: false یک تطابق دقیق است ، درست یک تطبیق تقریبی است.از آنجا که اطلاعات خروجی باید در جدول اول قرار گیرد (در این جدول بود که باید نام خریداران اضافه شود) ، فرمول را در آن می نویسیم. بیایید یک فرمول در ستون آزاد در سمت راست جدول مقابل اولین ردیف داده ایجاد کنیم:
= VLOOKUP (A3؛ Sheet2! $ A 3 $: $ F $ 10؛ 6؛ FALSE)
هنگام کپی فرمول ها ، اکسل هوشمند به طور خودکار آدرس سلول را تغییر می دهد. در مورد ما ، مقدار مورد نظر برای هر سطر تغییر خواهد کرد: A3 ، A4 و غیره ، و آدرس جدولی که جستجو در آن انجام می شود باید بدون تغییر باشد. برای این کار سلولهای موجود در پارامتر آدرس جدول را با نمادهای "$" اصلاح کنید. به جای "Sheet2! A3: F10" ما "Sheet2! $ 3 $: $ F $ 10" را می سازیم.

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

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

کادر گفتگو به دو قسمت تقسیم شده است: برای جدول اول سمت چپ و برای جدول دوم سمت راست.

برای مقایسه جداول ، این مراحل را دنبال کنید:

• دامنه های جدول را مشخص کنید.
• اگر جدول شامل یک سرصفحه (ردیف هدر) است ، در محدوده انتخاب شده جداول ، یک کادر تأیید (کادر تأیید / کادر تأیید) تنظیم کنید.
• ستون های جداول چپ و راست را که با استفاده از آنها مقایسه انجام می شود ، انتخاب کنید (اگر محدوده جداول شامل سربرگ نباشد ، ستون ها شماره گذاری می شوند).
• نوع مقایسه را مشخص کنید.
• گزینه نمایش نتایج را انتخاب کنید.

مقایسه نوع جداول

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

ردیف هایی را از یک جدول پیدا کنید که در جدول دیگری وجود نداشته باشد

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

خطوط منطبق را پیدا کنید

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

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

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

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

مقایسه جداول در چهار ستون یا بیشتر

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

• یک ستون خالی در جداول ایجاد کنید.
• در ستون های جدید با استفاده از فرمول = جفتستون هایی را که می خواهید مقایسه کنید ادغام کنید.

به این ترتیب شما در نهایت با 1 ستون حاوی مقادیر چند ستون مواجه خواهید شد. خوب ، شما قبلاً می دانید که چگونه یک ستون را مطابقت دهید.