اغلب اوقات ، کاربران اکسل با این کار روبرو می شوند که دو جدول یا لیست را برای شناسایی تفاوت ها یا موارد گمشده مقایسه کنند. هر کاربر به روش خود با این کار کنار می آید ، اما بیشتر اوقات زمان زیادی صرف حل این مسئله می شود ، زیرا همه رویکردهای این مسئله منطقی نیستند. همزمان ، چندین الگوریتم اقدام اثبات شده وجود دارد که به شما امکان مقایسه لیست ها یا آرایه های جدول را در مدت زمان نسبتاً کوتاه با حداقل هزینه تلاش. بیایید نگاهی دقیق به این گزینه ها بیندازیم.
چند روش برای مقایسه مناطق جدول در Excel وجود دارد ، اما همه آنها را می توان به سه گروه بزرگ تقسیم کرد:
بر اساس این طبقه بندی ، اول از همه ، روش های مقایسه انتخاب می شوند ، و همچنین اقدامات و الگوریتم های خاص برای کار تعیین می شود. به عنوان مثال ، هنگام مقایسه در کتابهای مختلف کار ، باید همزمان دو پرونده اکسل را باز کنید.
علاوه بر این ، باید گفت که مقایسه منطق های غذا خوری فقط وقتی منطقی باشد که ساختار مشابهی داشته باشند.
روش 1: فرمول ساده
ساده ترین راه مقایسه داده ها در دو جدول استفاده از یک فرمول ساده برابری است. اگر داده مطابقت داشته باشد ، نشانگر TRUE را برمی گرداند ، و در غیر اینصورت ، نادرست است. شما می توانید هم داده های عددی و هم متن را با هم مقایسه کنید. عیب این روش این است که فقط در صورت مرتب کردن یا مرتب سازی داده های جدول به صورت یکسان ، هماهنگ شده و تعداد خطوط یکسان ، می توان از آن استفاده کرد. بیایید ببینیم چگونه استفاده کنیم بدین ترتیب در عمل ، با استفاده از مثال دو جدول قرار داده شده در یک صفحه.
بنابراین ، ما دو جدول ساده با لیست کارمندان شرکت و حقوق آنها داریم. مقایسه لیست کارمندان و شناسایی ناسازگاری بین ستونهایی که در آنها اسامی قرار داده شده است ، ضروری است.
- برای این منظور به یک ستون اضافی در صفحه نیاز داریم. در آنجا تابلویی وارد می کنیم «=»
... سپس بر روی اولین مورد کلیک می کنیم تا در لیست اول مقایسه شود. نماد را دوباره بگذارید «=»
از صفحه کلید بعد ، ما بر روی سلول اول ستون کلیک می کنیم که در جدول دوم مقایسه می کنیم. نتیجه بیان از نوع زیر است:
اگرچه ، البته ، در هر مورد خاص ، مختصات متفاوت خواهد بود ، اما ماهیت همان باقی خواهد ماند.
- روی کلید کلیک کنید واردبرای به دست آوردن نتایج مقایسه همانطور که مشاهده می کنید ، هنگام مقایسه سلول های اول هر دو لیست ، برنامه نشانگر را نشان می دهد "درست است، واقعی"، که به معنی مطابقت داده است.
- حال باید عملیاتی مشابه با بقیه سلولهای هر دو جدول در ستونهایی که در حال مقایسه هستیم انجام دهیم. اما می توانید فرمول را کپی کنید ، که به طور قابل توجهی در وقت صرفه جویی می کند. این فاکتور به ویژه هنگام مقایسه لیست ها با تعداد زیادی رشته مهم است.
روش کپی ساده ترین کار با دسته پر است. نشانگر را در گوشه پایین سمت راست سلول که نشانگر را در آن قرار داده ایم ، نشان دهید "درست است، واقعی"... در این حالت ، باید به یک صلیب سیاه تبدیل شود. این نشانگر پر کردن است. دکمه سمت چپ ماوس را فشار دهید و مکان نما را به سمت پایین خطوط آرایه های جدول مقایسه شده بکشید.
- همانطور که مشاهده می کنید ، اکنون ستون اضافی تمام نتایج مقایسه داده ها را در دو ستون آرایه های جدول نمایش می دهد. در مورد ما ، داده ها فقط در یک خط مطابقت ندارند. هنگام مقایسه آنها ، فرمول نتیجه را می دهد "نادرست"... همانطور که مشاهده می کنید ، برای همه خطوط دیگر ، فرمول مقایسه شاخص را نشان می دهد "درست است، واقعی".
- علاوه بر این ، محاسبه تعداد عدم تطابق با استفاده از یک فرمول خاص امکان پذیر است. برای انجام این کار ، عنصر صفحه را انتخاب کنید که در آن نمایش داده می شود. سپس روی نماد کلیک می کنیم "درج عملکرد".
- در پنجره جادوگران عملکرد در گروه اپراتور "ریاضی" نام را برجسته کنید محصول... روی دکمه کلیک کنید "خوب".
- پنجره آرگومان های عملکرد فعال است محصول، وظیفه اصلی آنها محاسبه مجموع محصولات محدوده انتخاب شده است. اما از این عملکرد می توان برای اهداف ما نیز استفاده کرد. نحو آن کاملاً ساده است:
SUMPRODUCT (آرایه 1 ؛ آرایه 2 ؛ ...)
در کل ، آدرس های حداکثر 255 آرایه ای می توانند به عنوان آرگومان استفاده شوند. اما در مورد ما ، ما فقط از دو آرایه استفاده خواهیم کرد ، بعلاوه ، به عنوان یک استدلال.
مکان نما را در میدان قرار می دهیم "Array1" و محدوده داده مقایسه شده را در صفحه اول در صفحه انتخاب کنید. پس از آن ، علامت را در قسمت زمینه قرار دهید "نا برابر" (<> ) و محدوده مقایسه شده منطقه دوم را انتخاب کنید. بعد ، عبارت حاصل را در پرانتز می پیچیم ، در مقابل آن دو علامت قرار می دهیم «-» ... در مورد ما ، عبارت زیر را بدست آوردیم:
- (A2: A7<>D2: D7)
روی دکمه کلیک کنید "خوب".
- اپراتور نتیجه را محاسبه و نمایش می دهد. همانطور که مشاهده می کنید ، در مورد ما ، نتیجه برابر با عدد است "یک"یعنی اینکه به این معنی است که یک عدم تطابق در لیست های مقایسه شده وجود دارد. اگر لیست ها کاملاً یکسان باشند ، نتیجه برابر با عدد خواهد بود «0» .
به همین ترتیب می توانید داده ها را در جداول مقایسه کنید که در ورق های مختلف قرار دارند. اما در این حالت مطلوب است که خطوط موجود در آنها شماره گذاری شود. در غیر این صورت ، روش مقایسه تقریباً دقیقاً مشابه آنچه در بالا توضیح داده شد است ، به غیر از این واقعیت که هنگام وارد کردن فرمول ، باید بین ورق ها جابجا شوید. در مورد ما ، این عبارت به صورت زیر خواهد بود:
B2 \u003d Sheet2! B2
یعنی همانطور که مشاهده می کنید ، قبل از مختصات داده ها ، که در برگه های دیگر قرار دارند ، متفاوت از جایی که نتیجه مقایسه نمایش داده می شود ، شماره ورق و یک علامت تعجب نشان داده شده است.
روش 2: گروه های سلول را انتخاب کنید
مقایسه را می توان با استفاده از ابزار انتخاب گروه سلول انجام داد. همچنین به شما امکان مقایسه فقط لیست های هماهنگ و مرتب را می دهد. علاوه بر این ، در این حالت ، لیست ها باید در کنار یکدیگر و در همان صفحه قرار بگیرند.
روش 3: قالب بندی شرطی
مقایسه را می توان با استفاده از روش قالب بندی شرطی انجام داد. همانند روش قبلی ، مناطق مقایسه شده باید در همان صفحه کارایی Excel باشند و با یکدیگر هماهنگ شوند.
روش دیگری برای استفاده از قالب بندی مشروط برای انجام وظیفه موجود وجود دارد. مانند گزینه های قبلی ، به موقعیت هر دو منطقه مقایسه شده در یک صفحه نیاز دارد ، اما بر خلاف روش های توضیح داده شده قبلی ، شرط همگام سازی داده ها یا مرتب سازی مورد نیاز نخواهد بود ، که این گزینه را به طور مطلوب از گزینه های قبلی توصیف می کند.
در صورت تمایل ، می توانید عناصر ناسازگار با رنگ را از بین ببرید ، و شاخص هایی را که با پر کردن مطابقت دارند ، در همان رنگ بگذارید. در این حالت ، الگوریتم عملکردها عملاً یکسان است ، اما در پنجره تنظیمات به جای برجسته سازی پارامتر ، برای برجسته سازی مقادیر تکراری در قسمت اول ، "مکرر" پارامتر باید انتخاب شود "منحصر بفرد"... پس از آن ، بر روی دکمه کلیک کنید "خوب".
بنابراین ، دقیقاً آن شاخص هایی که با هم مطابقت ندارند برجسته می شوند.
روش 4: فرمول پیچیده
همچنین می توانید داده ها را با استفاده از یک فرمول پیچیده مبتنی بر عملکرد مقایسه کنید COUNTIF... با استفاده از این ابزار می توانید تعداد ستون های انتخاب شده جدول دوم را در جدول اول تکرار کنید.
اپراتور COUNTIF متعلق به گروه آماری توابع است. وظیفه آن شمردن تعداد سلولهایی است که مقادیر در آنها شرایط مشخص را برآورده می کند. نحو برای این عملگر به شرح زیر است:
COUNTIF (دامنه ، معیار)
بحث و جدل "دامنه" آدرس آرایه ای است که مقادیر تطبیق در آن محاسبه می شود.
بحث و جدل "معیار"یک شرط مطابقت را مشخص می کند. در مورد ما ، این مختصات سلولهای خاص را در منطقه جدول اول نشان می دهد.
البته ، برای مقایسه شاخص های جدولی می توان از این عبارت در شکل فعلی خود استفاده کرد ، اما امکان بهبود آن وجود دارد.
بیایید این کار را بکنیم تا مقادیری که در جدول دوم هستند ، اما در اولین جدول نیستند ، در یک لیست جداگانه نشان داده شوند.
- اول از همه ، بیایید فرمول خود را کمی اصلاح کنیم. COUNTIF، یعنی ، آن را به یکی از استدلال های اپراتور تبدیل کنید اگر... برای این کار اولین سلولی را که اپراتور در آن قرار دارد انتخاب کنید COUNTIF... در نوار فرمول ، قبل از آن ، عبارت را اضافه کنید "اگر" بدون نقل قول و پرانتز را باز کنید. بعلاوه ، برای سهولت کار ما ، مقدار را در نوار فرمول انتخاب کنید "اگر" و روی نماد کلیک کنید "درج عملکرد".
- پنجره آرگومان های عملکرد را باز می کند اگر... همانطور که مشاهده می کنید ، قسمت اول پنجره از قبل با مقدار عملگر پر شده است COUNTIF... اما باید چیز دیگری به این زمینه اضافه کنیم. مکان نما را در آنجا تنظیم کرده و به عبارت موجود اضافه می کنیم «=0»
بدون نقل قول.
پس از آن ، به میدان بروید "ارزش اگر درست باشد"... در اینجا ما از یک تابع تو در تو استفاده خواهیم کرد - خط... ما کلمه را وارد می کنیم "خط" بدون نقل قول ، سپس براکت ها را باز کرده و مختصات سلول اول را با نام خانوادگی در جدول دوم نشان دهید و سپس براکت ها را ببندید. به طور خاص در مورد ما ، در زمینه "ارزش اگر درست باشد" عبارت زیر را گرفت:
ROW (D2)
حالا اپراتور خط توابع را برقرار خواهد کرد اگر تعداد خطی که نام خانوادگی خاص در آن قرار دارد و در شرایطی که شرط مشخص شده در قسمت اول برآورده شود ، عملکرد اگر این عدد را به سلول منتقل می کند. روی دکمه کلیک کنید "خوب".
- همانطور که مشاهده می کنید ، اولین نتیجه به صورت نمایش داده می شود "نادرست"... این بدان معنی است که مقدار مطابق با شرایط اپراتور نیست اگر... یعنی اولین نام خانوادگی در هر دو لیست وجود دارد.
- با استفاده از نشانگر fill ، عبارت عملگر را به روش معمول کپی کنید اگر برای کل ستون همانطور که مشاهده می کنید ، برای دو موقعیتی که در جدول دوم وجود دارد ، اما در جدول اول نیست ، فرمول اعداد خط را نشان می دهد.
- از قسمت جدول به سمت راست عقب می رویم و ستون را به ترتیب و با شروع پر می کنیم 1 ... تعداد اعداد باید با تعداد ردیف های جدول مقایسه دوم مطابقت داشته باشد. برای سرعت بخشیدن به روند شماره گذاری می توانید از نشانگر پر کردن نیز استفاده کنید.
- پس از آن ، سلول اول را در سمت راست ستون با شماره انتخاب کرده و روی نماد کلیک کنید "درج عملکرد".
- باز می شود جادوگر عملکرد... به دسته بروید "آماری" و نام را انتخاب کنید "کمترین"... روی دکمه کلیک کنید "خوب".
- عملکرد کمترین، پنجره آرگومانهای آن باز شده است ، برای نمایش کمترین مقدار تعیین شده توسط تعداد است.
در زمینه "آرایه" مختصات محدوده ستون اضافی را مشخص کنید "تعداد همزمانی"که قبلاً با تابع تغییر شکل دادیم اگر... ما تمام پیوندها را مطلق می کنیم.
در زمینه "K" کوچکترین مقدار نمایش داده شده را نشان می دهد. در اینجا مختصات سلول اول ستون را با شماره گذاری نشان می دهیم ، که اخیراً اضافه کرده ایم. آدرس را نسبی بگذارید. روی دکمه کلیک کنید "خوب".
- اپراتور نتیجه را - یک عدد 3 ... این کوچکترین شماره ردیفهای تطبیق ناپذیر آرایه های جدول است. با استفاده از دسته پر کردن فرمول را در پایین آن کپی کنید.
- حال ، با دانستن شماره خط عناصر غیر همسان ، می توانیم با استفاده از تابع ، سلول و مقادیر آنها را وارد کنیم فهرست مطالب... اولین عنصر ورق حاوی فرمول را انتخاب کنید کمترین... پس از آن ، به نوار فرمول و قبل از نام بروید "کمترین" نام را اضافه کنید "فهرست مطالب" بدون نقل قول ، بلافاصله پرانتز را باز کنید و یک نقطه ویرگول قرار دهید ( ; ) سپس نام را در نوار فرمول انتخاب کنید "فهرست مطالب" و روی نماد کلیک کنید "درج عملکرد".
- پس از آن ، یک پنجره کوچک باز می شود که در آن شما باید تعیین کنید که آیا عملکرد باید فرم مرجع داشته باشد فهرست مطالب یا برای کار با آرایه ها طراحی شده است. ما به گزینه دوم نیاز داریم. به طور پیش فرض نصب شده است ، بنابراین در این پنجره فقط روی دکمه کلیک کنید "خوب".
- پنجره آرگومان های عملکرد شروع می شود فهرست مطالب. این اپراتور در نظر گرفته شده برای خروجی مقداری است که در یک آرایه خاص روی خط مشخص شده قرار دارد.
همانطور که می بینید ، زمینه "شماره خط" قبلاً با مقادیر تابع پر شده است کمترین... تفاوت بین شماره گذاری صفحه اکسل و شماره گذاری داخلی ناحیه جدول باید از مقداری که در آنجا وجود دارد کم شود. همانطور که مشاهده می کنید ، ما فقط یک هدر بالای مقادیر جدول داریم. این به این معنی است که تفاوت یک خط است. بنابراین ، ما در زمینه اضافه می کنیم "شماره خط" مقدار "-یک" بدون نقل قول.
در زمینه "آرایه" آدرس محدوده مقادیر جدول دوم را مشخص کنید. در این حالت ، همه مختصات را مطلق می کنیم ، یعنی علامت دلار را به روشی که قبلاً توصیف کردیم ، جلوی آنها می گذاریم.
روی دکمه کلیک کنید "خوب".
- پس از نمایش نتیجه روی صفحه ، عملکرد را با استفاده از نشانگر پر کردن به انتهای ستون پایین گسترش دهید. همانطور که مشاهده می کنید ، هر دو نام خانوادگی که در جدول دوم وجود دارد ، اما در جدول اول وجود ندارد ، در یک محدوده جداگانه نمایش داده می شوند.
روش 5: مقایسه آرایه ها در کتاب های مختلف
هنگام مقایسه دامنه ها در کتاب های مختلف کار ، می توانید از روش های ذکر شده در بالا استفاده کنید ، به جز مواردی که نیاز به قرار گرفتن هر دو جدول را در یک صفحه دارند. شرط اصلی برای انجام روش مقایسه در این مورد باز شدن همزمان پنجره های هر دو پرونده است. برای نسخه های اکسل 2013 به بعد ، و همچنین برای نسخه های قبل از اکسل 2007 ، این شرط وجود ندارد. اما در اکسل 2007 و اکسل 2010 ، برای باز کردن همزمان هر دو پنجره ، دستکاری های اضافی لازم است. نحوه انجام این کار در یک درس جداگانه شرح داده شده است.
همانطور که مشاهده می کنید ، تعدادی مقایسه جدول ها با یکدیگر وجود دارد. اینکه کدام گزینه استفاده شود به محل دقیق قرارگیری داده های جدولی نسبت به یکدیگر (در همان صفحه ، در کتاب های مختلف ، در ورق های مختلف) و همچنین به این موضوع بستگی دارد که کاربر می خواهد این مقایسه روی صفحه نمایش داده شود.
آیا نیاز به مقایسه دو فایل Microsoft Excel دارید؟ در اینجا دو مورد است راههای آسان برای انجام این.
دلایل زیادی وجود دارد که شما بخواهید یک سند اکسل را بگیرید و آن را با سند دیگری مقایسه کنید. این می تواند یک کار وقت گیر باشد
تمرکز زیادی می خواهد ، اما روش هایی وجود دارد که زندگی شما را آسان تر می کند.
آیا شما باید با دست از نزدیک نگاهی بیندازید یا می خواهید اکسل برخی از کارهای سنگین را انجام دهد
از طرف شما ، در اینجا دو روش آسان برای مقایسه چندین ورق وجود دارد.
نحوه مقایسه فایل های اکسل
اکسل به کاربران اجازه می دهد تا دو نسخه از یک سند را همزمان نشان دهند تا به سرعت بین آنها تمایز ایجاد کند:
- ابتدا کتابهای کاری را که می خواهید مقایسه کنید ، باز کنید.
- تغییر به نمای\u003e پنجره\u003e نمای جانبی.
فایل های اکسل را با چشم مقایسه کنید
ابتدا اکسل و همه کتابهایی را که می خواهید مقایسه کنید باز کنید. ما می توانیم از همان روش برای مقایسه ورق های یک سند استفاده کنیم
یا پرونده های کاملاً متفاوت.
اگر بیش از یک برگ از همان کتاب دریافت شده باشد ، باید آن را از قبل جدا کنید. برای انجام این کار به نمایش\u003e پنجره\u003e پنجره جدید.
با این کار صفحات منفرد به طور دائم تقسیم نمی شوند ، فقط نمونه جدیدی از سند شما باز می شوند.
این منو تمام جداولی را که در حال حاضر باز هستند لیست می کند. اگر فقط دو باز داشته باشید ، به طور خودکار انتخاب می شوند.
انتخاب خود را انجام داده و کلیک کنید خوب... مشاهده خواهید کرد که هر دو جدول بر روی صفحه نمایش ظاهر می شوند.
اگر راحت تر است ، می توانید استفاده کنید ترتیب همه چیز را بدهید دکمه برای تغییر بین تنظیمات عمودی و افقی.
یکی از گزینه های مهم برای آگاهی از آن این است پیمایش همزمان تعویض
فعال کردن این گزینه اطمینان می دهد که وقتی یک پنجره پیمایش می شود ، پنجره به صورت همگام حرکت می کند. این مهم است اگر شما با یک میز بزرگ کار می کنید.
و می خواهید یکی را در مقابل دیگری بررسی کنید. اگر به هر دلیلی دو برگه در یک راستا نبودند ، فقط فشار دهید تنظیم مجدد موقعیت پنجره.
فایل های اکسل را با استفاده از قالب بندی مشروط مقایسه کنید
در بسیاری از موارد ، بهترین روش مقایسه دو مورد است صفحات گسترده ممکن است فقط برای نمایش همزمان آنها روی صفحه باشد. با این حال ، می توان روند را تا حدودی خودکار کرد.
استفاده از قالب بندی مشروط
ما می توانیم اکسل را از نظر اختلاف بین دو صفحه بررسی کنیم. اگر تنها چیزی که باید پیدا کنید تفاوت بین یک نسخه و نسخه دیگر است ، این می تواند در وقت شما صرفه جویی کند.
برای این روش ، باید اطمینان حاصل کنیم که دو صفحه ای که با آنها کار می کنیم بخشی از یک کتاب کار هستند. برای این کار ، روی نام برگه ای که می خواهید منتقل کنید ، کلیک راست کرده و انتخاب کنید انتقال یا کپی کردن.
در اینجا می توانید از منوی کشویی استفاده کنید تا تصمیم بگیرید که کدام سند درج می شود.
تمام سلول هایی را که در صفحه کار پر شده اند ، جایی که می خواهید اختلافاتی برجسته شود ، انتخاب کنید. راه سریع برای انجام این کار کلیک بر روی سلول در گوشه بالا سمت چپ و سپس استفاده از میانبر است
Ctrl + Shift + End.
تغییر به صفحه اصلی\u003e سبک ها\u003e قالب بندی مشروط\u003e قانون جدید.
انتخاب کنید از فرمولی برای تعیین سلولهای قالب بندی استفاده کنید و موارد زیر را وارد کنید:
A1sheet_name! A1
فقط به یاد داشته باشید که "sheet_name" را برای هر نام دیگر برگ قرار دهید. این فرمول فقط زمانی اعتبار سنجی را انجام می دهد که سلول در یک صفحه دقیقاً با سلول مربوطه در صفحه دیگر مطابقت نداشته باشد و هر نمونه را پرچم گذاری کند.
نتایج را می توانید در بالا مشاهده کنید. تمام سلولهای حاوی تغییرات با رنگ قرمز مشخص شده اند و مقایسه سریع دو صفحه سریع و آسان است.
اجازه دهید اکسل کار سخت را انجام دهد
تکنیک بالا یک راه را نشان می دهد که می توانید به اکسل اجازه دهید برخی کارهای سنگین را انجام دهد. حتی اگر خوب توجه کنید ، این احتمال وجود دارد که اگر این کار را دستی انجام دهید ، تغییری را از دست بدهید. با قالب بندی مشروط ، می توانید مطمئن باشید که هیچ چیز به شبکه نمی خورد
اکسل در کارهای تکراری و دقیق خوب است. پس از دستیابی به توانایی های آن ، می توانید با استفاده از تکنیک هایی مانند قالب بندی مشروط و کمی هوشمندی ، در وقت و تلاش خود صرفه جویی کنید
آیا در زمینه مقایسه اسناد در اکسل توصیه ای دارید؟ یا آیا به فرایندهای این راهنما نیاز به کمک دارید؟ به هر حال ، چرا در بخش نظرات زیر به مکالمه نمی پیوندیم؟
ما دو جدول سفارش داریم که در یک صفحه کار کپی شده اند. لازم است داده های دو جدول در اکسل مقایسه شود و بررسی شود که در جدول اول کدام موقعیت ها وجود دارد ، اما در جدول دوم اینگونه نیست. مقایسه دستی هر سلول هیچ فایده ای ندارد.
دو ستون را برای مطابقت در Excel مقایسه کنید
چگونه می توان مقادیر دو ستون را در اکسل مقایسه کرد؟ برای حل این مشکل ، ما توصیه می کنیم از قالب بندی مشروط استفاده کنید ، که می تواند مواردی را که فقط در یک ستون هستند به سرعت برجسته کند. صفحه کار با جداول:
اولین قدم اختصاص نام به هر دو جدول است. این باعث می شود درک محدوده سلولی با یکدیگر مقایسه شود:
- ابزار FORMULAS - نامهای مشخص شده - اختصاص نام را انتخاب کنید.
- در پنجره ظاهر شده ، در قسمت "Name:" مقدار - Table_1 را وارد کنید.
- بر روی قسمت ورودی "Range:" کلیک چپ کرده و دامنه را انتخاب کنید: A2: A15. و روی تأیید کلیک کنید.
برای لیست دوم ، همین کار را انجام دهید ، اما یک نام اختصاص دهید - Table_2. و به ترتیب محدوده C2: C15 را نشان دهید.
مشاوره مفید! با استفاده از قسمت name می توان سریعتر نام محدوده ها را تعیین کرد. در سمت چپ نوار فرمول قرار دارد. فقط محدوده سلول ها را انتخاب کرده و در قسمت name ، نام مناسب محدوده را تایپ کرده و Enter را فشار دهید.
حال بیایید از قالب بندی مشروط برای مقایسه دو لیست در Excel استفاده کنیم. ما باید نتیجه زیر را بدست آوریم:
موقعیت هایی که در Table_1 هستند اما در Table_2 نیستند با سبز نشان داده می شوند. در همان زمان ، موقعیت های واقع در Table_2 ، اما در Table_1 وجود ندارد ، با رنگ آبی مشخص می شوند.
اصل مقایسه داده های دو ستون در اکسل
ما از تابع COUNTIF برای تعیین شرایط قالب بندی سلولهای ستون استفاده کردیم. که در این مثال این تابع بررسی می کند که چند بار مقدار آرگومان دوم (به عنوان مثال ، A2) در لیست آرگومان اول (به عنوان مثال ، جدول_2) رخ می دهد. اگر تعداد دفعات \u003d 0 باشد ، فرمول TRUE را برمی گرداند. در این حالت ، به سلول قالب سفارشی مشخص شده در گزینه های قالب بندی مشروط اختصاص داده می شود.
مرجع در آرگومان دوم نسبی است ، به این معنی که تمام سلولهای محدوده انتخاب شده به نوبت بررسی می شوند (به عنوان مثال ، A2: A15). به عنوان مثال ، برای مقایسه دو قیمت در اکسل ، حتی در ورق های مختلف. فرمول دوم به روشی مشابه کار می کند. همین اصل را می توان برای کارهای مختلف مختلف به کار برد.
به نظر می رسد یک کار ساده - مقایسه جداول. به عبارت دقیق تر ، مقایسه دو ستون جدول برای همزمانی یا اختلاف. منطقی است که پیشنهاد کنیم اکسل یک راه حل ایده آل برای حل مسئله است ، اما افسوس که من مقایسه ساده جداول در اکسل را پیدا نکردم ، به جز شاید برای "row1 \u003d row2" بدوی. در حقیقت ، برخی از پردازش رشته ها قبل از مقایسه لازم است ، زیرا ممکن است حاوی فضاهای اضافی ، علائم نگارشی و غیره باشد. در نتیجه ، تصمیم گرفته شد که یک ابزار مقایسه دو فایل متنی را خط به خط و خطوط پردازش را به انتخاب کاربر بنویسید ...
پردازش رشته ها از طریق پرونده ها به عنوان جهانی انتخاب شد. مهم نیست که کدام منبع داده باشد ، فقط یک لیست یا یک جدول اکسل است. معمولاً همه چیز را می توان در یک فایل متنی کپی کرد. بنابراین بیایید مستقیماً با برنامه شروع کنیم.
برنامه را بارگیری و از حالت فشرده خارج کنید. در نسخه اصلی خود ، شامل سه فایل "Compare.exe" است - خود برنامه. "List 1.txt" و "List 2.txt" پرونده های متنی خالی هستند. آنها جایی هستند که شما برای مقایسه باید رشته های خود را وارد کنید. راه اندازی:
به طور پیش فرض ، به نظر من ، تنظیمات مقایسه بهینه وجود دارد. پنجره ای با نمونه ای از نحوه مقایسه جدول فقط برای تنظیم تنظیمات وظایف شما و درک کلی از اتفاقات ایجاد شده است. داده های واقعی را در مثال مقایسه نکنید ، زیرا این پنجره ها بیش از 32KB متن ندارند ، بقیه بدون هشدار قطع می شوند. ممکن است نتایج اشتباهی بگیرید! این برنامه دارای یک نکته است و هنگامی که ماوس را نشان می دهید ، پنجره نشان داده می شود توضیح کوتاه تنظیم یا مورد.
بعد از اینکه با مثال مقایسه به اندازه کافی بازی کردید - داده های خود را برای مقایسه در پرونده های "List 1.txt" و "List 2.txt" کپی کنید و با تنظیمات قبلی انتخاب شده ، روی دکمه "File files" کلیک کنید. در هنگام پردازش پرونده ها ، دکمه "پردازش در حال انجام" با رنگ قرمز ظاهر می شود ، تا پایان این فرآیند صبر کنید. در انتها ، به پوشه همراه برنامه ، به مکانی که برنامه را شروع کرده اید نگاه کنید ، بسته به تنظیمات ، فایلهای مشخص شده در مثال مقایسه ظاهر می شوند. با هر مقایسه یا باز یا بسته شدن برنامه جدید ، همه پرونده ها به جز "List 1.txt" و "List 2.txt" حذف می شوند.
و کمی در مورد سرعت مقایسه. بیشتر مشکلات دنیای واقعی تقریباً بلافاصله حل می شوند. خوب ، تست های من به این شکل است (پردازنده مانند Intel Core برای سوکت LGA 775 با فرکانس 2 گیگاهرتز):
مقایسه 2 لیست 1 مگابایتی هر کدام (25 حرف در هر خط و 39 هزار خط در هر لیست) ، برای مقایسه بدیهی است که مقایسه هر خط از لیست 1 با تمام خطوط دیگر لازم است. در کل 1.521 میلیارد مقایسه رشته ای به ما می دهد. زمان اجرا حدود 20 ثانیه است. مصرف حافظه کمتر از 10 مگابایت.
مقایسه 2 لیست 10 مگابایتی هر کدام (25 حرف در هر خط و 390 هزار خط در هر لیست). این به ما 152.1 میلیارد مقایسه رشته می دهد. زمان سرب حدود یک ساعت است. در همان زمان ، این برنامه حدود 200 مگابایت از خود را می گیرد حافظه دسترسی تصادفی... اگرچه چنین اندازه هایی از قبل قلمرو پایگاه های اطلاعاتی هستند. در این برنامه ، من قبلاً از همه روش های مناسب برای افزایش سرعت استفاده کرده ام.
الگوریتم کار و پارامترهای مقایسه
الگوریتم کار به گونه ای است که با هر تنظیمات ، تمام کاراکترها از خطوط حذف می شوند ، به جز حروف لاتین و روسی ، اعداد و نشانه های یک نقطه و ویرگول. البته ، تمام فاصله های اضافی بین کلمات و فضاهای اطراف لبه های خط حذف می شوند.
خطوط منطبق را جستجو کنید و جستجوی خطوط مختلف - در اینجا همه چیز روشن است ، آیا جستجو برای خطوط منطبق یا متفاوت انجام خواهد شد. مسابقات در پرونده "Matches.txt" نوشته خواهد شد. هنگام جستجوی خطوط مختلف ، به ترتیب در دو پرونده "Mismatches 1.txt" و "Mismatches 2.txt" برای لیست های 1 و 2 نوشته می شوند. همچنین ، در قسمت مثال مقایسه ، دو پنجره به جای یک پنجره ظاهر می شود.
توجه! هنگام مقایسه لیست ها برای همزمانی ، یک ویژگی وجود دارد ، از آنجا که در هر دو لیست همان خطوط وجود دارد ، نتیجه شامل خطوطی از لیست 1. هنگام مقایسه برای موارد منطبق ، متن با قالب بندی منظم تری را در لیست 1 قرار دهید.
خطاهای طرح بندی صفحه کلید را برطرف کنید - Punto Switcher مطمئناً از اینجا دور است. این به خطایی در تایپ حروف مشابه در طرح اشتباه (C ، H ، P و غیره) اشاره دارد. به عنوان مثال "s" روسی و لاتین. آنها در همان کلید قرار دارند و اگر کلمه با "c" شروع شود ، می توانید حرف اول را در طرح انگلیسی تایپ کنید و سپس به روسی یا بالعکس بروید. الگوریتم جایگزینی به گونه ای است که اگر حروف روسی بیشتر از حروف انگلیسی در کلمه وجود داشته باشد ، حروف انگلیسی به حروف روسی تغییر می کنند و بالعکس.
اصلاح Yo (e) به E (e) - فقط همه "e" با "e" جایگزین می شود.
مقایسه حروف غیر حساس - همه حروف بزرگ می شوند.
مقایسه با رشته های منحصر به فرد - اگر پارامتر فعال باشد ، سپس لیست ها ابتدا از نظر خطوط منطبق بررسی می شوند. اگر یک رشته به عنوان مثال 5 بار تکرار شود ، یک نسخه از این رشته برای مقایسه در لیست باقی می ماند و شماره 4 به لیست "تکرارها" ارسال می شود. تکرارها برای هر لیست کلمات متفاوت است.
بدون این پارامتر ، رشته ها به صورت جفتی مقایسه می شوند. به عنوان مثال ، هنگام مقایسه برای خطوط تطبیق ، اگر 2 خط یکسان در لیست 1 وجود داشته باشد ، و در لیست 2 3 خط دیگر وجود دارد ، پس نتیجه فقط دو خط خواهد بود ، زیرا خط 3 جفتی پیدا نکرد که با آن مطابقت داشته باشد. اگر برای این مثال مقایسه ها را به رشته های غیر همسان تبدیل کنیم ، یک رشته از لیست 2-a در نتیجه رشته های غیر همسان قرار می گیرد ، زیرا با هیچ چیزی مطابقت ندارد
با استفاده از مقایسه رشته منحصر به فرد ، می توانید رشته های تکراری را در لیست پیدا کنید. برای انجام این کار ، می توانید برای مثال خطوط فقط پرونده "List 1.txt" را پر کرده و آن را با پرونده خالی "List 2.txt" مقایسه کنید و سپس در پرونده "RepeatСп1.txt" خطوط اتصال وجود دارد از لیست 1.
فقط برای ردیف های حاصل از نتیجه تکرار می شود - فقط در رابطه با مقایسه رشته منحصر به فرد کار می کند. بدون این پارامتر ، تمام خطوط تکراری در لیست های تکراری گنجانده شده اند. اگر فعال باشد ، فقط خطوط موجود در نتیجه در تکرارها گنجانده می شوند. تعداد خطهایی که در تکرارها گرفته می شوند یکسان است و برابر با تعداد تکرارهای لیست اولیه منهای 1 است.
نقطه ها و ویرگول ها را حذف کنید و همه فضاها را بردارید - فقط حذف شده و بس.
بگویید می خواهید نسخه های یک کتاب کار را مقایسه کنید ، یک کتاب کار را برای مشکلات یا ناسازگاری تجزیه و تحلیل کنید یا پیوندهای بین کتاب های کار یا صفحه های کار را ببینید. اگر 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 بتواند نسخه ذخیره شده کتاب کار شما را باز کند. استفاده از رمزهای کار کتاب کار دستور در جویا شدن برای اضافه کردن گذرواژه ها ، در رایانه ذخیره می شود. این رمزهای عبور رمزگذاری شده و فقط توسط شما قابل دسترسی است.