وبلاگ

مغایرت‌ گیری در اکسل و استخراج سریع صورت مغایرت بانک

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

از بین روش های مختلفی که برای این کار وجود دارد استفاده از Conditional Formatting یکی از بهترین راه هاست.برای مثال ما دو لیست مبلغ داریم که یکی مربوط به تراکنش های بانک و دیگری مبالغ ثبت شده در دفتر می باشد.مطابق شکل زیر:

تراکنش های حساب بانک و دفاتر
شکل شماره 1 – تراکنش های حساب بانک و دفاتر

مغایرت گیری براساس مبلغ:

صورت مغایرت دفتر:

برای مغایرت‌ گیری می خواهیم ببینیم کدام مبالغ دفتر در بانک وجود دارد بصورت هایلات نشان دهیم تا مغایرت ها مشخص شود.برای اینکار ابتدا محدوده 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($J$5:$J$13,$G5=

توضیح فرمول: کاری که فرمول بالا انجام میده این هست تابع COUNTIF میاد تو محدوده J5:J13 مقدار G5 را شمارش میکنه.اگر Conditional Formatting عددی مانند عدد G5 پیدا کنه اون سلول رو رنگی میکنه.

نکته: به نوع آدرس دهی مطلق و نسبی محدوده ها در فرمول دقت کنید.

مغایرت گیری با فرمت نویسی شرطی با ابزار  Conditional Formatting
شکل شماره 2 – فرمت نویسی شرطی با ابزار Conditional Formatting

سپس با زدن دکمه …Format فرمت یا استایل موردنظرتان را برای مبلغ های تکراری اعمال کنید.مثلا از تب Fill یک رنگی را انتخاب کنید.که من رنگ آبی را انتخاب کردم.مانند شکل زیر:

تنظیم رنگ صورت مغایرت در  Conditional Formatting
شکل شماره 3 – تنظیم رنگ مغایرت گیری در Conditional Formatting

بعد از اعمال فرمت فوق همه پنجره ها را با زدن دکمه OK ببندید.نتیجه صورت مغایرت باید به شکل زیر باشد.

هایلایت شدن تراکنش های مغایر در حساب دفاتر
شکل شماره 4 – هایلایت شدن تراکنش های مغایر در حساب دفاتر

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

صورت مغایرت بانک:

حال اگر می خواهید مبلغ های بانک که در ستون دفتر نیز می باشد رنگی شده صورت مغایرت آن مشخص شود، دقیقا به روش بالا عمل می کنید.یعنی ابتدا محدوده J5:J13 را انتخاب کرده سپس در Conditional Formatting به روشی که در بالا گفته شد، فرمول زیر را در Format values where this formula is true جایگذاری کنید

(COUNTIF($G$5:$G$13,$J5=

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

مقایسه مغایرت حساب بانک و دفاتر با ابزار Conditional Formatting
شکل شماره 5 – هایلایت شدن تراکنش های مغایر در حساب بانک و دفاتر

مغایرت گیری براساس مبلغ و تاریخ:

خب ممکن است در صورت مغایرت شما مولفه تاریخ هم دخیل باشد.در این شرایط چگونه باید عمل کرد…؟

این وضعیت مشابه وضعیت فوق می باشد با این تفاوت که باید دو شرط را بررسی کنیم.پس به جای استفاده از تابع COUNTIF از تابع COUNTIFS استفاده می کنیم.

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

Home tab > Styles group > Conditional Formatting > Manage Rules

استفاده از تابع Countif برای استخراج تراکنش های یکسان
شکل شماره 5 – استفاده از تابع Countif برای مغایرت گیری

مغایرت گیری دفاتر:

خب در این حالت برای مغایرت گیری دفتر باید بدانیم کدام مبلغ های ستون دفتر در ستون بانک با تاریخ های یکسان وجود دارد

صورت مغایرت براساس تاریخ و مبلغ
شکل شماره 6 – مغایرت گیری براساس تاریخ و مبلغ

برای اینکار ابتدا محدوده 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 فرمول زیر را بنویسید:

(COUNTIFS($I$5:$I$13,$F5,$J$5:$J$13,$G5=
هایلایت کردن تراکنش های مغایر براساس مبلغ و تاریخ
شکل شماره 7 – هایلایت کردن تراکنش های مغایر براساس مبلغ و تاریخ

مغایرت گیری بانک:

برای صورت مغایرت بانک، درمورد ستون بانک هم به شکل فوق عمل میکنیم با این تفاوت که ابتدا محدوده I5:J13 را انتخاب و از فرمول زیر استفاده می کنیم:

(COUNTIFS($F$5:$F$13,$I5,$G$5:$G$13,$J5=

که نتیجه نهایی مغایرت‌ گیری با تغییر دادن داده ها به شکل زیر خواهد بود:

نتیجه صورت مغایرت بانک و دفاتر با  Conditional Formatting
شکل شماره 8 – نتیجه صورت مغایرت بانکی با Conditional Formatting

اگر دوست دارید در عرض چند دقیقه مغایرت صورتحسابهای بانک و دفاتر رو در بیاری میتونید از نرم افزار مغایرت گیری تحت اکسل استفاده کنید.


منبع:

support.office.com

اشتراک گذاری:

مطالب زیر را حتما مطالعه کنید

29 دیدگاه

به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.

  • با سلام
    2 تا ستون داریم مثلا ستون یک بدهکار و ستون 2 بستانکار ، می خواهیم فقط یک مورد از موارد مشابه و تکراری با کمک فرمت شرطی رنگ شود یعنی اگر از یک عدد در یک ستون 3 مورد هم بود فقط یکی رنگ شود

  • مطابق فایل ارسالی 2 تا ستون داریم مثلا ستون یک بدهکار و ستون 2 بستانکار ، می خواهیم فقط یک مورد از موارد مشابه و تکراری با کمک فرمت شرطی رنگ شود یعنی اگر از یک عدد در یک ستون 3 مورد هم بود فقط یکی رنگ شود

    • با سلام.
      مطابق فایل ارسالی ابتدا محدود $A$2:$A$1290 را انتخاب کرده و در کاندشینال فرمتینگ فرمول زیر رو بنویسید:
      COUNTIF($A$2:$A2,$A2)=2=

      • با سلام مجدد ظاهرا بنده منظورم را خوب نرساندم
        ما برای مغایرت گیری می خواهیم تمامی موارد مشابه در 2 ستون رنگ شود البته اگر 3 تا عدد 100 داشتیم در ستون اول فقط یکی رنگی شود و اگر در ستون دوم هم 2 تا عدد 100 داشتیم فقط یکی رنگی شود تا عدد 100 در ستون دوم با عدد 100 در ستون اول تهاتر شود

        • یک ستون به جدولتون اضافه کنید طوریکه ستون 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>0,$A2<>"")

          حال محدوده C2:C1290 رو انتخاب و در کاندشینال فرمول زیر رو بنویسید:

          =AND($D2>0,$D2<>"")

          • اگه مثلا توی ستون بدهکارا 2 تا 200 داشته باشیم و توی ستون بستانکار 3 تا 200 داشته باشیم فقط یک 200 رو رنگی میکنه

  • سلام
    جهت اینکه بخواهیم مغایرت گیری کنیم وقتی فرمول countif رومینویسم وسلول رو انتخاب میکنیم اون چطور مثل مثال سایت g5چطور اخرش اضافه کنیم

    • سلام…

      برای راحتی کار ابتدا فرمول را داخل یکی از سلول ها بنویسید و سپس به ابزار Conditional Formatting منتقل کنید.برای تغییر نوع آدرس دهی بصورت نسبی یا مطلق می توانید از کلید F4 استفاده کنید.

  • سلام
    برای مغایرت بین ستون a, b اون اعدادی که مثل هم باشه رو میخوام پاک بشه ،لطفا راهنمایی کنید که چکونه این عملیات رو انجام بدم

  • با سلام وقت بخیر و سپاس بابت آموزش های خوبتون
    در یک فایل دو شیت موجود است یک شیت مربوط به اطلاعات بانک و یک شیت مربوط به اطلاعات سیستم حسابداری ، که باید باهم مثل مثال شما مغایرت گیری انجام شود
    با این تفاوت که در فایل سیستم حسابداری تاریخ ها با یکی دو روز تاخیر ثبت شده اند و باید برای مقایسه تاریخ از شیت بانک ، با تاریخ شیت سیستم حسابداری تا 3-4 روز بعد از تاریخ مورد نظر مقایسه صورت گیرد
    آیا امکانش هست؟؟؟؟
    با تشکر

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

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

  • سلام و وقت بخیر..
    ممنونم بابت آموزشهاتون..
    برای من مشکلی پیش آمده..
    برای مغایرت گیری فرومول و مراحل رو طبق آموزش شما انجام میدهم اما با ارور مواجه شده و نتیجه نمیگیرم ..
    مشکل کجاست؟
    ممنونم

  • سلام و وقت بخیر. برای مغایرت گیری بانک با این فرمولی ک نوشتین اینجا مشکل دارم =IF(COUNTIF($B$1:B1,B2)=0,COUNTIF($C$2:C1290,B2),””)
    خطا میده
    در واقع من میخوام مغایرت بانک رو بگیرم منتهی آموزشهای بالا در حالتیه که اعداد تکراری نباشه اما تو حالت معمولی قطعا از ی عدد چن بار در ستون ها تکرار شده.

  • سلام وقت بخیر
    بنده طبق فرمول شما میرم خطا میده ( عکس خطا هم واتس آپ براتون فرستادم )
    منتهی یه تغییر میدم داخل جدا کننده تابع ( , ) به جای این از ( ; ) استفاده میکنم خطا نمیده ولی جواب اشتباست
    لطفا راهنمایی کنید

  • سلام وقت بخیر
    وقتی طبق فرمول شما انجام میدم خطا میده
    زمانی که میخوام محدوده را انتخاب کنم و کلید (,) را میزنم خطا میده ولی این ( ; ) را میرنم خطا نمیده ولی جواب اشتباه بهم میده لطفا راهنمایییم کنید

  • سلام جناب مختاري
    چرا در فرمولهاي مربوطه در اين بخش علامت , با ; جابجا شده؟

    • سلام. وقت بخیر

      از کنترل پنل سیستمتون روی (,) تنظیم کنید.

  • سلام
    من دو ستون عدد دارم كه در يكي مثلا عدد27 هفت مورد و در ستون ديگه عدد27 دوازده بار تكرار شده مي خوام كه هفت تا از اين طرف و هفت تا از اون طرف رنگي بشه و پنج تاي اضافي اون طرف بدون رنگ باقي بمونه
    از اينكه صادقانه به سوالات جواب مي دين بينهايت ممنونم

  • سلام و خسته نباشید
    دو ستون اعداد دارم برای مغایرت گیری مثلا در ستون اول تعداد دوازده تا عدد27موجوده در ستون دوم تعداد هفت تا عدد27 می خوام هفتاشون از دو ستون رنگی بشه بقیه سفید بمونه تا مغایرتم رو پیدا کنم فرمولش چیه؟
    ایراد فرمول شما اینه که میاد تمام اعداد27رو رنگی می کنه!
    چند بار سوالم رو مطرح کردم ولی جواب نگرفتم ممنون میشم اگه جواب بدین

  • سلام
    کد زیر مشکلی به تنهایی نداره ولی وقتی از افزونه های فارسی استفاده می کنم نه تنها تاریخ شمسی رو نمی ندازه بلکه کلا از کار می افته
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range(“A4:A15″)) Is Nothing Then
    With Target(1, 2)
    .Value = Date & ” ” & Time
    .EntireColumn.AutoFit
    End With
    End If
    End Sub

دیدگاهتان را بنویسید