مغایرت گیری در اکسل و استخراج سریع صورت مغایرت بانک
مغایرت گیری در اکسل برای استخراج مغایرت بانک و دفاتر یکی از مباحثی هست که بیشتر حسابداران و در بعضی مشاغل با آن سروکار دارند.فرض کنید شما یک لیست چندصدتایی و یا چندهزارتایی دارید که باید مقادیر تکرار یا غیرتکرار را از داخل آنها استخراج کنید.اگر بخواهید اینکار را به روش سنتی و با تیک زدن های کلافه کننده انجام دهید مسلما کاری زمانبر خواهد بود.با ما همراه باشید تا با چند روش ساده در اکسل این کار را در کمترین زمان ممکن انجام دهیم.
از بین روش های مختلفی که برای این کار وجود دارد استفاده از Conditional Formatting یکی از بهترین راه هاست.برای مثال ما دو لیست مبلغ داریم که یکی مربوط به تراکنش های بانک و دیگری مبالغ ثبت شده در دفتر می باشد.مطابق شکل زیر:
مغایرت گیری براساس مبلغ:
صورت مغایرت دفتر:
برای مغایرت گیری می خواهیم ببینیم کدام مبالغ دفتر در بانک وجود دارد بصورت هایلات نشان دهیم تا مغایرت ها مشخص شود.برای اینکار ابتدا محدوده G5:G13 را انتخاب کنید و مسیر زیر را بروید:
Home tab > Styles group > Conditional Formatting > New Rule > Use a formula to determine which cells to format
حال در کادر Format values where this formula is true فرمول زیر را بنویسید:
توضیح فرمول: کاری که فرمول بالا انجام میده این هست تابع COUNTIF میاد تو محدوده J5:J13 مقدار G5 را شمارش میکنه.اگر Conditional Formatting عددی مانند عدد G5 پیدا کنه اون سلول رو رنگی میکنه.
نکته: به نوع آدرس دهی مطلق و نسبی محدوده ها در فرمول دقت کنید.
سپس با زدن دکمه …Format فرمت یا استایل موردنظرتان را برای مبلغ های تکراری اعمال کنید.مثلا از تب Fill یک رنگی را انتخاب کنید.که من رنگ آبی را انتخاب کردم.مانند شکل زیر:
بعد از اعمال فرمت فوق همه پنجره ها را با زدن دکمه OK ببندید.نتیجه صورت مغایرت باید به شکل زیر باشد.
شکل فوق نشانگر این است که مبلغ سلول های آبی رنگ در ستون بانک نیز وجود دارد.
صورت مغایرت بانک:
حال اگر می خواهید مبلغ های بانک که در ستون دفتر نیز می باشد رنگی شده صورت مغایرت آن مشخص شود، دقیقا به روش بالا عمل می کنید.یعنی ابتدا محدوده J5:J13 را انتخاب کرده سپس در Conditional Formatting به روشی که در بالا گفته شد، فرمول زیر را در Format values where this formula is true جایگذاری کنید
نتیجه نهایی مغایرت گیری به شکل زیر خواهد بود :
مغایرت گیری براساس مبلغ و تاریخ:
خب ممکن است در صورت مغایرت شما مولفه تاریخ هم دخیل باشد.در این شرایط چگونه باید عمل کرد…؟
این وضعیت مشابه وضعیت فوق می باشد با این تفاوت که باید دو شرط را بررسی کنیم.پس به جای استفاده از تابع COUNTIF از تابع COUNTIFS استفاده می کنیم.
برای اینکه شرط های قبلی با شرط های جدید قاطی نشه ابتدا باید شرط های قبلی رو پاک کرد.برای اینکار به مسیر زیر برید و با انتخاب شرط های قبلی آنها رو پاک کنید:
Home tab > Styles group > Conditional Formatting > Manage Rules
مغایرت گیری دفاتر:
خب در این حالت برای مغایرت گیری دفتر باید بدانیم کدام مبلغ های ستون دفتر در ستون بانک با تاریخ های یکسان وجود دارد
برای اینکار ابتدا محدوده F5:G13 را انتخاب کرده و به مسیر زیر بروید
Home tab > Styles group > Conditional Formatting > New Rule > Use a formula to determine which cells to format
حال در کادر Format values where this formula is true فرمول زیر را بنویسید:
مغایرت گیری بانک:
برای مغایرت گیری بانک، درمورد ستون بانک هم به شکل فوق عمل میکنیم با این تفاوت که ابتدا محدوده I5:J13 را انتخاب و از فرمول زیر استفاده می کنیم:
که نتیجه نهایی مغایرت گیری با تغییر دادن داده ها به شکل زیر خواهد بود:
منبع:
درباره جاوید
عاشق اکسل هستم و بیش از 12 ساله دارم اکسل کار می کنم. در این مسیر آموزش ها و فایل های حرفه ای ارائه دادم و همچنان در این مسیر با علاقه پیش میرم چون امروزه اکسل به یک مهارت انکار نشدنی تبدیل شده
سایر نوشته های جاویدمطالب زیر را حتما مطالعه کنید
۴ گام تا شماره گذاری صفحه چاپ در اکسل با یک نکته عالی
ایجاد لیست شیت ها در ComboBox اکسل با دستورات VBA
در کمتر از ۱ دقیقه چک لیست کارهای روزانه در اکسل بساز
فایل اکسل محاسبه مالیات حقوق سال ۱۴۰۱ + آموزش ویدئویی فرمولنویسی مالیات
راست چین در اکسل و نحوه راست به چپ کردن شیت اکسل
برای چاپ اطلاعات داخل فرم از قبل پرینت شده در اکسل چکار کنیم؟
16 دیدگاه
به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.
سلام
من هم این فرمول رو زدم اصلا ایراد میگیره و خطا میده
دقیقا همین فرموله فقط داده ها بیشتره و به جای ۱۳، ۷۹۲۳ هست
سلام.من فرمول شما را counifs در اکسل جدید وارد میکنم…اجرا نمیشه به همین روش های گفته شده توسط شما…
دلیش چیه؟؟؟
ممنون اگر پاسخ دهید
سلام. وقت بخیر
فرمولی که نوشتید رو همینجا قرار بدید.
ی سوال دارم از شما اگر در یک روز ۳ یا ۴ عدد در دفاتر به صورت مبالغ تکراری بود اما در بانک فقط یک مورد بود به چه صورتی باید نوشته بشود که مغایرت گرفته شود – توو روش بالا فرض بر این بود که عدد تکراری نباشد
سلام. وقت بخیر
در موارد تجمعی کاربرد ندارد.
به ازای هر تراکنش باید متناظر اون تراکنش وجود داشته باشد.
از پست آموزشی تان بسیار سپاسگذارم خیلی مفید بود .
خواهش می کنم.
موفق باشید.
مطابق فایل ارسالی ۲ تا ستون داریم مثلا ستون یک بدهکار و ستون ۲ بستانکار ، می خواهیم فقط یک مورد از موارد مشابه و تکراری با کمک فرمت شرطی رنگ شود یعنی اگر از یک عدد در یک ستون ۳ مورد هم بود فقط یکی رنگ شود
با سلام.
مطابق فایل ارسالی ابتدا محدود $A$2:$A$1290 را انتخاب کرده و در کاندشینال فرمتینگ فرمول زیر رو بنویسید:
COUNTIF($A$2:$A2,$A2)=2=
با سلام مجدد ظاهرا بنده منظورم را خوب نرساندم
ما برای مغایرت گیری می خواهیم تمامی موارد مشابه در ۲ ستون رنگ شود البته اگر ۳ تا عدد ۱۰۰ داشتیم در ستون اول فقط یکی رنگی شود و اگر در ستون دوم هم ۲ تا عدد ۱۰۰ داشتیم فقط یکی رنگی شود تا عدد ۱۰۰ در ستون دوم با عدد ۱۰۰ در ستون اول تهاتر شود
یک ستون به جدولتون اضافه کنید طوریکه ستون A خالی باشه.ستون B برای بدهکار و ستون C برای بستانکار باشه.
در سلول A2 فرمول زیر رو بنویسید و به سمت پایین درگ کنید:
=IF(COUNTIF($B$1:B1,B2)=0,COUNTIF($C$2:C1290,B2),"")
در سلول D2 فرمول زیر رو بنویسید و به سمت پایین درگ کنید:
=IF(COUNTIF($C$1:C1,C2)=0,COUNTIF($B$2:B1290,C2),"")
حال محدوده B2:B1290 رو انتخاب و در کاندشینال فرمول زیر رو بنویسید:
=AND($A2>۰,$A2<>"")
حال محدوده C2:C1290 رو انتخاب و در کاندشینال فرمول زیر رو بنویسید:
=AND($D2>۰,$D2<>"")
با عرض سلام و تشکر از سایت خوبتون
حالا اگه تو ستون اول دو تا عدد ۲۰۰ داشته باشیم ولی تو ستون دوم یک عدد ۲۰۰ داشته باشیم و بخوایم مغایرت گیری انجام بدیم که یکی از عدد ۲۰۰ ستون اول رو رنگ کنه(چون در ستون دوم نیست)باید از چه فرمولی استفاده کنیم؟
سلام. وقت بخیر
باید از تابع Countif استفاده کنید.
با سلام
۲ تا ستون داریم مثلا ستون یک بدهکار و ستون ۲ بستانکار ، می خواهیم فقط یک مورد از موارد مشابه و تکراری با کمک فرمت شرطی رنگ شود یعنی اگر از یک عدد در یک ستون ۳ مورد هم بود فقط یکی رنگ شود
سلام. کارتون بیست. عالی عالی. دمتون گرم
خواهش میکنم.
موفق باشید…