امروز : شنبه , ۱۳ آذر , ۱۳۹۵

فرض کنید ستونی از اعداد داشته باشیم و بعضی از مقادیر این ستون تکراری باشند و بخواهیم با یک فرمو ل این اعداد را طوری جمع کنیم که هر عدد فقط یک بار در محاسبه در نظر گرفته شود. مثلاً اگر عددی دوبار یا سه بار در لیست تکرار شده باشد فقط یک بار در جمع منظور گردد.

برای این منظور از ترکیب سه تابع sum، IF،Frequncey استفاده می کنیم.

فرض کنید در ناحیه A2:A12 تعدادی عدد وارد کرده ایم. فرمول را به شکل زیر می نویسیم:

=SUM(IF(FREQUENCY(A2:A12,A2:A120)>0,A2:A12,0))

این فرمول اعداد ستون داده شده را جمع می کند به طوری که هر عدد بدون در نظر گرفتن اینکه چند بار تکرار شده باشد فقط یک بار در نتیجه جمع ظاهر می شود.

برای درک بهتر این فرمول ابتدا باید نتیجه تابع FREQUENCY را بررسی کنیم.

شکل کلی تابع FREQUENCY به این صورت است.

FREQUENCY(data_array,bins_array)

همانطور که در فرمول می بینید به جای data_array و bins_array ناحیه A2:A12 قرار گرفته است. توجه داشته باشید اگر از  تابع FREQUENCY به تنهایی (بدون ترکیب با فرمول دیگری) استفاده می کنید باید آن را به صورت آرایه ای بنویسید.

در این مثال ما تعداد ۱۱ عدد را در ناحیه A2:A12 وارد کرده ایم. اگر بخواهیم با استفاده از تابع FREQUENCY تعداد تکرار هر عدد را حساب کنیم باید ابتدا تعداد ۱۱ سلول را انتخاب نموده و سپس فرمول  (FREQUENCY(A2:A12,A2:A120 را در اولین سلول نوشته و کلیدهای ترکیبی ctrl+shift+enter را فشار دهیم تا فرمول به صورت آرایه ای ثبت شود.

تذکر: در فرمول های آرایه ای علامت های {    } در ابتدا و انتهای فرمول به صورت اتوماتیک نوشته می شوند.

در فرمول =SUM(IF(FREQUENCY(A2:A12,A2:A120)>0,A2:A12,0)) اگر تعداد تکرار در تابع FREQUENCY بیشتر از عدد صفر باشد آن عدد یک باردر محاسبه منظور می شود و اگر صفر بود یعنی تکرار آن عدد بیش از یک بار بوده و در جمع در نظر گرفته نمی شود.

داده ها  

  نتیجه تابع FREQUENCY

نتیجه تابع IF 

  نتیجه تابع SUM

توضیحات

۱۵۲

۱

۱۵۲

۲۲۲۰

عدد ۱۵۲ یک بار وارد شده است

۷۲۵

۳

۷۲۵

 

عدد ۷۲۵ سه بار تکرار شده است

۳۲۶

۱

۳۲۶

 

عدد ۳۲۶ یک بار وارد شده است

۱۵

۱

۱۵

 

عدد ۱۵ یک بار وارد شده است

۴۵

۲

۴۵

 

عدد ۴۵ دوبار تکرار شده است

۷۲۵

۰

۰

 

 این عدد تکراری می باشد فقط یک بار در جمع شرکت داده می شود

۱۴۷

۱

۱۴۷

 

عدد ۱۴۷ یک بار وارد شده است

۷۲۵

۰

۰

 

این عدد تکراری می باشد فقط یک بار در جمع شرکت داده می شود

۱۹۸

۱

۱۹۸

 

عدد ۱۹۸ یک بار وارد شده است

۴۵

۰

۰

 

این عدد تکراری می باشد فقط یک بار در جمع شرکت داده می شود

۶۱۲

۱

۶۱۲

 

عدد ۶۱۲ یک بار وارد شده است

 

در ستون A اعداد تکراری مشخص شده اند

فرمول ستون B به صورت آرایه ای ثبت شده است

در ستون C اگر مقدار ستون B از ۰ بیشتر شود عدد واقع در ستون A قرار میگیرد و در غیر این صورت نتیجه تابع IF عدد ۰ می باشد

در ستون D جمع نتیجه تابع IF محاسبه شده است.

شکل زیر را ببینید

 

 

  • نويسنده: احمد شهبازی
  • نوشته شده در تاريخ : یکشنبه , ۲۴ آبان , ۱۳۹۴
  • بازدید: 1247 بازدید

پاسخ دهید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *