جمع اعداد غیر تکراری
فرض کنید ستونی از اعداد داشته باشیم و بعضی از مقادیر این ستون تکراری باشند و بخواهیم با یک فرمو ل این اعداد را طوری جمع کنیم که هر عدد فقط یک بار در محاسبه در نظر گرفته شود.
مثلاً اگر عددی دوبار یا سه بار در لیست تکرار شده باشد فقط یک بار در جمع منظور گردد.
برای این منظور از ترکیب سه تابع 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 محاسبه شده است.
شکل زیر را ببینید
جمع اعداد غیر تکراری
برای آشنایی با توابع آرایه ای این مقاله را مطالعه فرمایید
ثبت ديدگاه