مقایسه داده ها در گوگل شیت
یکی از مواردی که حتما در هنگام کار کردن با داده ها با اون مواجه می شیم مقایسه اطلاعات دو ستون یا دو شیت با هم دیگه در اکسل و گوگل شیت هست که نتیجه این مقایسه می تونه به پیدا کردن داده های مشابه یا داده های متفاوت با هم منجر بشه. این موضوع رو قبلا در اکسل و پاورکوئری بصورت کامل تشریح کردیم. حالا میخوایم در گوگل شیت هم حلش کنیم. این کار رو هم می تونیم مستقیما با فرمول نویسی و هم با استفاده از ابزار Conditional Formatting انجام بدیم. در این مقاله قصد داریم از هر دو روش مثال هایی رو ارائه کنیم.
روش اول: مقایسه داده ها با استفاده از فرمول نویسی
مثال ۱: این حالت رو با یک مثال خیلی ساده شروع می کنیم و اون هم مقایسه دو سلول با هم هست. در شکل زیر در دو ستون دو تا دستور غذایی نمایش داده شده که برای مقایسه هر سلول با سلول هم ردیفش در ستون مقابل به شکل زیر عمل می کنیم:
شکل ۱- مقایسه دو سلول با هم در گوگل شیت
همان طور که در شکل ۱ نمایش داده شده فرمولی که در این حالت استفاده کردیم خیلی ساده هست و خروجی اون یا TRUE هست (در صورت مساوی بودن داده های دو سلول) و یا FALSE (در صورت نامساوی بودن داده های دو سلول).
نکته کاربردی:
اگر بخواهیم اطلاعات یک سلول رو با اطلاعات یک سلول در فایل دیگه مقایسه کنیم از تابع IMPORTRANGE به صورت زیر استفاده می کنیم:
=A2= IMPORTRANGE (spreadsheet_url, range_string)
=A2=IMPORTRANGE (“https://docs.google.com/spreadsheets/d/1Hh2grfB6rp9OQ2yAIu3S5YF_CCFJGwyqPGveABlOZKg/edit”, “World Cup! C2“)
همان طور که در فرمول بالا مشخص هست آرگومان اول تابع IMPORTRANGE آدرس URL فایل گوگل شیت مورد نظر هست و آرگومان دوم آدرس سلول مربوطه در فایل گوگل شیت رو مشخص می کنه.
مثال ۲: در این مثال قصد داریم با استفاده از تابع IF اطلاعات دو تا ستون رو با هم مقایسه کنیم. شکل ۱ رو در نظر بگیرید نحوه استفاده از تابع IF به شکل زیر خواهد بود:
شکل ۲- مقایسه دو لیست با استفاده از تابع IF
در صورتی که داداه ها به زبان انگلیسی و با حروف کوچک و بزرگ نوشته شده باشه و در مقایسه ای که انجام میدیم بزرگ و کوچک بودن مهم باشه باید در آرگومان اول تابع IF (logical_expression) از تابع EXACT به صورت زیر استفاده کنیم:
=IF (EXACT (A2, B2),”Match”, “Differ”)
شکل ۳- مقایسه دو لیست با استفاده از تابع IF و تابع Exact
در صورتی که در شکل ۲ بخواهیم فقط سطرهای که داده های یکسان دارند رو مشخص کنیم فرمول رو به صورت زیر می نویسیم:
=IF (A2=B2, “Match”, “”)
دقت کنید که در این حالت به جای آرگومان سوم تابع IF (value_if_false) از دو تا کوتیشن استفاده کردیم که به معنی جای خالی هست.
در صورتی که در شکل ۲ بخواهیم فقط سطرهای که داده های ناهمسان دارند رو مشخص کنیم فرمول رو به صورت زیر می نویسیم:
=IF (A2=B2, “”, “Differ”)
همان طور که در فرمول بالا مشخص هست در این حالت در قسمت value_if_true از دو تا کوتیشن استفاده کردیم.
مثال ۳: در این مثال با استفاده از ترکیب تابع ArrayFormula و تابع IF داده های مشابه و متفاوت رو در ستون ها مشخص می کنیم. مزیتی که این روش فرمول نویسی داره این هست که دیگه لازم نیست فرمول رو در بقیه سلول ها کپی کنیم و فقط کافی فرمول رو در سلول اول بنویسیم و دکمه Enter رو بزنیم. در گوگل شیت برای عمل کردن آرایه بر خلاف اکسل که از ترکیب Ctrl+Shift+Enter استفاده می کنیم، کافیه فقط دکمه Enter رو بزنیم. نحوه ترکیب این دو تابع در شکل زیر نمایش داده شده:
شکل ۴- مقایسه دو لیست با استفاده از تابع IF و تابع ArrayFormula
در شکل ۴ به آرگومان اول تابع IF دقت کنید که به چه صورتی نوشته شده. چون فرمول رو به صورت آرایه ای نوشتیم در نتیجه آدرس دو ستونی که می خواهیم با هم مقایسه کنیم (A2:A6=B2:B6) رو در این قسمت به طور کامل آوردیم. در این فرمول هر سلول از ستون A با سلول متناظرش در ستون B مقایسه می شه و اگر مثل هم باشن عبارت “match” در ستون C نمایش داده میشه.
مثال ۴: فرض کنید مطابق شکل زیر دو جدول از اطلاعات قیمت محصولات مختلف در دو شیت داریم. حالا می خواهیم ستون های متناظر در دو جدول رو با هم مقایسه کنیم و اطلاعاتی که متفاوت هستند رو در یک شیت جداگانه نمایش بدیم.
شکل ۵- مقایسه مقایسه گوگل شیت داده ها – مقایسه دو جدول در دو شیت متفاوت
برای اینکار یک شیت جدید ایجاد می کنیم و فرمول رو به شکل زیر در شیت جدید وارد می کنیم:
=IF (Sheet3! A2<> Sheet4! A2, Sheet3! A2 & ” | ” & Sheet4! A2,””)
در فرمول بالا در قسمت value_if_true فرمول رو طوری نوشتیم که مقادیر متفاوت در کنار هم نمایش داده بشه و با یک خط عمودی از هم تفکیک بشن. برای اینکه تمام اطلاعات دو جدول رو با هم مقایسه کنیم فرمول نوشته شده در سلول A2 (در شیت جدید) رو به اندازه محدوده جدولی که در شکل ۵ نمایش داده شده کپی می کنیم که نتیجه در شکل زیر نمایش داده شده.
شکل ۶- نمایش مقادیر متفاوت در کنار هم در یک شیت جدید پس از مقایسه اطلاعات دو شیت
با توجه به شکل بالا، برای مثال در سلول B2 که از مقایسه سلول B2 در شیت ۳ و ۴ در شکل ۵ حاصل شده، چون اطلاعات سلولB2 در شیت های ۳ و ۴ با هم متفاوت هستند پس طبق منطق فرمول، اطلاعات این دو سلول با تفکیک یک خط عمودی نمایش داده شده.
در صورتی که شیت هایی که با هم مقایسه می کنیم در دو فایل مجزا باشند از الگوی زیر استفاده می کنیم:
=IF (Sheet1! A1<>IMPORTRANGE (“2nd_spreadsheet_url”,”Sheet1! A1″), Sheet1! A1&” | ” & IMPORTRANGE (“2nd_spreadsheet_url”,”Sheet1! A1″),””)
با توجه به الگوی بالا، در گوگل شیت هر جا بخواهیم به یک فایل دیگه ارجاع بدیم از تابع IMPORTRANGE برای نمایش آدرس فایل استفاده می کنیم.
تا این جای کار به مباحث مربوط به مقایسه اطلاعات در یک شیت و در شیت های مختلف برای یافتن داده های متفاوت و مشابه پرداختیم در ادامه این مقاله قصد داریم به مقایسه اطلاعات در شیت ها برای یافتن داده های مفقود (missing records) بپردازیم.
یافتن داده های مفقود در گوگل شیت
مثال ۱: فرض کنید مطابق شکل زیر دو لیست از اسامی شهرها داریم. حالا می خواهیم مشخص کنیم که کدام شهرها در لیست اول هستند اما در لیست دوم وجود ندارند. برای انجام اینکار مطابق شکل از فرمول زیر استفاده می کنیم:
=ISERROR (VLOOKUP (A2, $B: $B,1,0)
شکل ۷- نمایش داده هایی که در لیست ۱ موجودند و در لیست ۲ نیستند
مطابق شکل ۷ مواردی که با عبارت FALSE مشخص شدن نشان دهنده شهر هایی هستند که در هر دو لیست موجودند و مواردی که با عبارت TRUE مشخص شدن نشان دهنده شهرهایی هستن که فقط در لیست ۱ هستند.
نحوه عملکرد فرمول:
در فرمول بالا ابتدا با استفاده از تابع VLOOKUP هر کدوم از شهرهای لیست ۱ رو در ستون B (لیست ۲) جستجو می کنیم. در صورتی که شهر مربوطه در لیست ۲ باشه خروجی تابع VLOOKUP نام شهر خواهد بود و اگر موجود نباشه خروجی عبارت #N/A خواهد بود. در صورتی که خروجی تابع VLOOKUP عبارت خطا #N/A باشه خروجی تابع ISERROR عبارت TRUE خواهد بود که به معنی اینه که شهر لیست ۱ در لیست ۲ نیست در غیر این صورت خروجی تابع عبارت FALSE هست که به معنی اینه که شهر مربوطه در هر دو لیست وجود دارد.
نکته کاربردی:
در صورتی که بخواهیم فرمول بالا رو به صورت آرایه ای بنویسیم به شکل زیر خواهد بود:
=ArrayFormula (ISERROR (VLOOKUP (A2:A10, $B: $B,1,0)))
مثال۲: روش دیگه تعیین داده هایی که فقط در یک لیست موجود هستن، استفاده از ترکیب توابع COUNTIF و IF هست که در شکل زیر نحوه ترکیب این توابع نمایش داده شده. (مشابه این روش داخل خود اکسل رو در این مقاله میتونید ببینید):
شکل ۸- نمایش داده هایی که فقط در یک لیست موجودند با ترکیب توابع COUNTIF و IF
در فرمول بالا در قسمت logical_expression تابع IF از تابع COUNTIF استفاده شده که این تابع تعداد داده هایی که در ستون B با هر کدوم از سلول های ستون A برابر هستند رو برامون محاسبه میکنه. اگر خروجی تابع COUNTIF برابر صفر باشه یعنی شهر مربوطه فقط در لیست ۱ وجود داره و در نتیجه عبارت “Not found” نمایش داده می شه.
مثال ۳: یک روش دیگه برای حل مثال بالا استفاده از تابع MATCH برای بررسی انطباق شهرهای لیست ۱ با لیست ۲ هست که البته برای بدست آوردن نتیجه نهایی از ترکیب این تابع با توابع ISERROR و IF استفاده می کنیم که به صورت زیر خواهد بود:
شکل ۹- نمایش داده هایی که فقط در یک لیست موجودند با ترکیب توابع MATCH، ISERROR و IF
در فرمول بالا ابتدا با استفاده از تابع MATCH چک می کنیم که شهر مورد نظر در لیست ۱ در لیست ۲ وجود داره یا خیر. خروجی این تابع در صورتی که شهر مورد نظر در لیست ۲ موجود باشه یک عدد خواهد بود و در صورتی که موجود نباشه خطای #N/A هست. اگر خروجی تابع MATCH عبارت خطای #N/A باشه خروجی تابع ISERROR عبارت TRUE هست که در این صورت خروجی تابع IF عبارت Not Found خواهد بود و در غیر این صورت جای خالی نمایش داده میشه.(نحوه کار با توابع مدیریت خطا رو میتونید در این مقاله ملاحظه کنید)
روش دوم: مقایسه داده ها در گوگل شیت با استفاده از Conditional Formatting
یک روش دیگه برای مقایسه داده ها با هم و تعیین داده های همانند یا متفاوت استفاده از ابزارConditional Formatting هست که در ادامه با چند مثال توضیح خواهیم داد.
مثال ۱: فرض کنید دو لیست از اسامی شهر ها داریم. حالا می خواهیم اسامی شهرهایی که در هر دو لیست هستند رو در لیست ۱ با رنگ مشخصی نمایش بدیم. برای این کار مراحل زیر رو طی می کنیم:
- محدوده شامل نام شهر ها در لیست ۱ رو انتخاب می کنیم؛
- از تب Format گزینه ابزارConditional Formatting رو انتخاب می کنیم؛
- در پنجره Conditional Formatting فیلد ها رو به صورت زیر تکمیل می کنیم؛
شکل ۱۰- مقایسه مقایسه گوگل شیت داده ها – نمایش شهرهای مشترک با لیست ۲ در لیست ۱ با ابزار Conditional Formatting
همان طور که در شکل بالا مشخص هست در قسمت Apply to range محدوده داده ای که می خواهیم فرمت دهی کنیم (لیست ۱ – A2:A11) رو وارد کردیم و در قسمت Format rules گزینه Custom formula is رو انتخاب کردیم و فرمول رو در فیلد زیرین نوشتیم. در قسمت Formatting style هم فرمت مورد نظرمون رو (شامل رنگ یا فونت دلخواه) رو تعریف می کنیم.
نکات کاربردی:
- در صورتی که قرار باشه به تعداد سطرهای لیست مورد نظرمون اضافه بشه و بخواهیم فرمت تعریف شده برای هر سطری که به لیست ۱ اضافه می کنیم اعمال بشه، در این صورت در قسمتApply to range محدوده رو به صورت A2:A می نویسیم و فرمول نوشته شده رو هم به صورت زیر تغییر میدیم:
=AND (A2=C2, ISBLANK(A2) =FALSE)
در این حالت فرمت تعریف شده در صورتی اعمال میشه که هر دو شرط نوشته شده در تابع AND برقرار باشه یعنی سلول مربوطه در لیست ۱ با سلول متناظر در لیست ۲ برابر باشه و همچنین سلول مربوطه در لیست ۱ خالی نباشه. در واقع معنی عبارت ISBLANK(A2) =FALSE این هست که به طور مثال سلول A2 خالی نباشه.
- در صورتی که بخواهیم لیست ۱ رو با لیستی در شیت دیگه مقایسه کنیم فرمول نوشته شده در Conditional Formatting رو به صورت زیر تغییر می دیم:
=A2=INDIRECT (“Sheet2! C2:C”)
مثال ۲: دو لیست نمایش داده در مثال قبل رو در نظر بگیرید، حالا می خواهیم در هر دو لیست شهرهایی منحصر به فرد رو با رنگ مشخص کنیم یعنی فقط شهرهایی که در لیست ۱ و لیست ۲ هستند رو مشخص کنیم برای اینکار به صورت زیر عمل می کنیم:
- محدوده شهرهای لیست ۱ رو انتخاب می کنیم؛
- در قسمت Apply to range محدوده A2:A11 رو انتخاب می کنیم؛
- در قسمت Format rules فرمول زیر رو وارد می کنیم؛
=COUNTIF ($C$2: $C$11, $A2) =0
در فرمول بالا با استفاده از تابع COUNTIF تعداد سلول هایی که در محدوده لیست ۲ (($C$2: $C$11 با هر کدوم از سلول های لیست ۱ برابر هستند رو شمارش می کنه و اگر این تعداد برابر صفر باشه یعنی اینکه شهر مربوطه در لیست ۱ در لیست ۲ وجود نداره و در نتیجه سلول مربوط به اون شهر در لیست ۱ با فرمت تعریف شده نمایش داده می شه.
مراحل ۱ تا ۳ رو برای محدوده شهرهای لیست ۲ هم انجام می دیم با این تفاوت که در مرحله ۳ فرمول زیر رو وارد می کنیم:
=COUNTIF ($A$2: $A$11, $C2) =0
نتیجه استفاده از فرمول های بالا در شکل زیر نمایش داده شده:
شکل ۱۱- مقایسه مقایسه گوگل شیت داده ها – نمایش شهرهای منحصر به فرد در دو لیست با ابزار Conditional Formatting
مثال ۳: در صورتی که بخواهیم در مثال بالا شهرهایی که در هر دو لیست مشترک هستند رو به فرمت دلخواه نمایش بدیم مراحل ۱ تا ۳ رو مطابق مثال قبل طی می کنیم با این تفاوت که در مرحله۳، فرمول های زیر رو وارد می کنیم:
فرمول فرموت دهی شهرهای مشترک در لیست ۱:
=COUNTIF ($C$2: $C$11, $A2)>0
فرمول فرموت دهی شهرهای مشترک در لیست ۲:
=COUNTIF ($A$2: $A$11, $C2)>0
با توجه به اینکه شهرهای مشترک در هر دو لیست رو می خواهیم در این حالت باید نتیجه تابع COUNTIF بزرگتر از صفر باشه تا فرمت تعریف شده اعمال بشه.
شکل ۱۲- مقایسه مقایسه گوگل شیت داده ها – نمایش شهر های مشترک در دو لیست با ابزار Conditional Formatting
در این مقاله تلاش کردیم با ارائه مثال هایی روش های مختلف مقایسه داده ها در گوگل شیت رو بررسی کنیم و همان طور که گفتیم این روش ها رو در قالب دو روش کلی فرمول نویسی و استفاده از ابزار Conditional Formatting تقسیم بندی کردیم. اگر هنوز با نحوه شروع کار با گوگل شیت و ویژگی های این نرم افزار آشنا نیستید حتما مقاله مروری بر گوگل شیت رو نگاه بندازید.
