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

ما دو جدول داریم (مثلاً نسخه قدیم و جدید لیست قیمت) که باید مقایسه کنیم و به سرعت تفاوت ها را پیدا کنیم:

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

برای هر کار در اکسل، تقریباً همیشه بیش از یک راه حل وجود دارد (معمولاً 4-5). برای مشکل ما، بسیاری از رویکردهای مختلف را می توان استفاده کرد:

• تابع VPR (VLOOKUP) - نام محصولات را از لیست قیمت جدید در لیست قدیمی جستجو کنید و قیمت قدیمی را در کنار لیست جدید نمایش دهید و سپس تفاوت ها را مشاهده کنید.
• دو لیست را در یک لیست ادغام کنید و سپس یک جدول محوری بر اساس آن بسازید، جایی که تفاوت ها به وضوح قابل مشاهده خواهد بود.
• از افزونه Power Query برای اکسل استفاده کنید

بیایید همه آنها را به ترتیب در نظر بگیریم.

روش 1. مقایسه جداول با تابع VLOOKUP

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

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

آن دسته از محصولاتی که خطای #N/A در مورد آنها مشخص شد، در لیست قدیمی نیستند، یعنی اضافه شدند. تغییرات قیمت نیز به وضوح قابل مشاهده است.

مزایا این روش: ساده و واضح، همانطور که می گویند "کلاسیک ژانر". در هر نسخه اکسل کار می کند.

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

روش 2: مقایسه جداول با استفاده از pivot

بیایید جداول خود را یکی زیر دیگری کپی کنیم و یک ستون با نام لیست قیمت اضافه کنیم تا بعداً بفهمید کدام ردیف از کدام لیست:

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

همانطور که می بینید، جدول محوری به طور خودکار یک لیست کلی از تمام محصولات از لیست قیمت های قدیمی و جدید (بدون تکرار!) تولید می کند و محصولات را بر اساس حروف الفبا مرتب می کند. محصولات اضافه شده (قیمت قدیمی را ندارند)، محصولات حذف شده (قیمت جدید را ندارند) و تغییرات قیمت در صورت وجود را به وضوح مشاهده می کنید.

مجموع کل در چنین جدولی معنی ندارد و می توان آنها را در برگه غیرفعال کرد سازنده – مجموع کل – غیرفعال کردن سطرها و ستون ها (طراحی - مجموع بزرگ).

اگر قیمت‌ها تغییر کرد (اما نه مقدار کالا!)، کافی است خلاصه ایجاد شده را با کلیک راست روی آن به‌روزرسانی کنید. تازه کردن.

مزایا: این رویکرد یک مرتبه بزرگتر از VLOOKUP در جداول بزرگ است. 

منفی: باید داده ها را به صورت دستی در زیر یکدیگر کپی کنید و یک ستون با نام لیست قیمت اضافه کنید. اگر اندازه جداول تغییر کرد، باید همه چیز را دوباره انجام دهید.

روش 3: مقایسه جداول با Power Query

Power Query یک افزونه رایگان برای مایکروسافت اکسل است که به شما امکان می دهد داده ها را تقریباً از هر منبعی در اکسل بارگذاری کنید و سپس این داده ها را به هر طریق دلخواه تغییر دهید. در اکسل 2016، این افزونه قبلاً به طور پیش فرض در برگه تعبیه شده است داده ها (داده ها)، و برای اکسل 2010-2013 باید آن را به طور جداگانه از وب سایت مایکروسافت دانلود کنید و آن را نصب کنید - یک برگه جدید دریافت کنید پرس و جو برق.

قبل از بارگذاری لیست قیمت ها در Power Query، ابتدا باید آنها را به جداول هوشمند تبدیل کنید. برای انجام این کار، محدوده با داده را انتخاب کنید و ترکیب را روی صفحه کلید فشار دهید کلیدهای Ctrl+T یا زبانه روی روبان را انتخاب کنید صفحه اصلی – قالب بندی به صورت جدول (صفحه اصلی - قالب بندی به عنوان جدول). نام جداول ایجاد شده در برگه قابل اصلاح است سازنده (من استاندارد را ترک می کنم جدول 1 и جدول 2، که به طور پیش فرض بدست می آیند).

با استفاده از دکمه، قیمت قدیمی را در Power Query بارگیری کنید از جدول/محدوده (از جدول/محدوده) از زبانه داده ها (تاریخ) یا از زبانه پرس و جو برق (بسته به نسخه اکسل). پس از بارگذاری، با دستور از Power Query به اکسل برمی گردیم بستن و بارگیری – بستن و بارگیری در… (بستن و بارگذاری - بستن و بارگذاری برای…):

… و در پنجره ظاهر شده را انتخاب کنید فقط یک اتصال ایجاد کنید (فقط اتصال).

همین کار را با لیست قیمت جدید تکرار کنید. 

حالا بیایید یک پرس و جو سوم ایجاد کنیم که داده های دو مورد قبلی را ترکیب و مقایسه می کند. برای انجام این کار، در اکسل در تب را انتخاب کنید داده – دریافت داده – ترکیب درخواست ها – ترکیب (داده - دریافت داده - پرس و جوهای ادغام - ادغام) یا دکمه را فشار دهید ترکیب کردن (ادغام) برگ پرس و جو برق.

در پنجره join، جداول ما را در لیست های کشویی انتخاب کنید، ستون هایی را با نام کالاها در آنها انتخاب کنید و در پایین، روش Join را تنظیم کنید. خارجی کامل (بیرونی کامل):

پس از کلیک روی OK یک جدول سه ستونی باید ظاهر شود، جایی که در ستون سوم باید محتویات جداول تودرتو را با استفاده از فلش دوتایی در هدر گسترش دهید:

در نتیجه، ادغام داده ها را از هر دو جدول دریافت می کنیم:

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

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

باقی مانده است که روی آن کلیک کنید OK و با استفاده از همین دکمه گزارش حاصل را در اکسل آپلود کنید ببندید و دانلود کنید (بستن و بارگیری) برگ صفحه اصلی (خانه):

زیبایی

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

مزایا: شاید زیباترین و راحت ترین راه باشد. با میزهای بزرگ هوشمندانه کار می کند. هنگام تغییر اندازه جداول به ویرایش دستی نیاز ندارد.

منفی: برای نصب به افزونه Power Query (در Excel 2010-2013) یا Excel 2016 نیاز دارد. نام ستون ها در داده های منبع نباید تغییر کند، در غیر این صورت با خطای "ستون چنین و چنان یافت نشد!" دریافت خواهیم کرد. هنگام تلاش برای به روز رسانی پرس و جو

• نحوه جمع آوری داده ها از تمام فایل های اکسل در یک پوشه خاص با استفاده از Power Query
• نحوه یافتن تطابق بین دو لیست در اکسل
• ادغام دو لیست بدون تکرار