جمع اعداد غیر تکراری

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

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

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

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

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

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

تابع FREQUENCY

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

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

FREQUENCY(data_array,bins_array)

همانطور که در فرمول می بینید به جای data_array و bins_array ناحیه A2:A12 قرار گرفته است.

توجه داشته باشید اگر از  تابع FREQUENCY به تنهایی (بدون ترکیب با فرمول دیگری) استفاده می کنید باید آن را به صورت آرایه ای بنویسید.

روش ثبت فرمول آرایه ای

در این مثال ما تعداد 11 عدد را در ناحیه A2:A12 وارد کرده ایم.

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

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

جدول نتایج

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

داده ها  

  نتیجه تابع FREQUENCY

نتیجه تابع IF 

  نتیجه تابع SUM

توضیحات

152

1

152

2220

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

725

3

725

عدد 725 سه بار تکرار شده است

326

1

326

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

15

1

15

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

45

2

45

عدد 45 دوبار تکرار شده است

725

0

0

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

147

1

147

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

725

0

0

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

198

1

198

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

45

0

0

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

612

1

612

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

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

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

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

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

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

جمع اعداد غیر تکراری

جمع اعداد غیر تکراری

برای آشنایی با توابع آرایه ای این مقاله را مطالعه فرمایید