کوین باید فرمولی ایجاد کند که هر چهارمین سلول را در یک ردیف جمع کند. او می داند که می تواند از فرمولی مانند =A6+E6+I6+M6 و غیره استفاده کند، اما اگر ستون های زیادی در کاربرگ وجود داشته باشد، این کار دست و پا گیر می شود.
راه های مختلفی برای حل این مشکل وجود دارد. یکی از راه‌ها اضافه کردن اطلاعات اضافی به کاربرگ است تا مشخص شود کدام سلول‌ها باید در جمع گنجانده شوند. به عنوان مثال، در مثال شما علاقه مند به جمع کردن سلول های ردیف 6 کاربرگ هستید. اگر بتوانید برخی از شاخص‌ها را در ردیف 5 اضافه کنید، می‌توانید از آنها به عنوان "ماشه" در یک فرمول استفاده کنید. مثلاً بالای هر سلولی که می‌خواهید در جمع گنجانده شود، عدد 1 را قرار دهید (ستون‌های A، E، I، M و غیره). سپس، می توانید از فرمولی مانند زیر استفاده کنید:
=SUMPRODUCT(A5:X5, A6:X6)
این فرمول اساساً هر آنچه در ردیف 5 است در برابر ردیف 6 ضرب می کند و سپس نتایج را جمع می کند. از آنجایی که در ستون‌هایی که می‌خواهید جمع شوند فقط 1 وجود دارد، این‌ها تمام مواردی هستند که در جمع نهایی گنجانده شده‌اند.
اگر نمی‌خواهید یک ردیف نشانگر به کاربرگ خود اضافه کنید، باید راه‌حل‌های مختلفی را بررسی کنید. همچنان می توانید از تابع SUMPRODUCT در فرمولی مانند زیر استفاده کنید:
=SUMPRODUCT((MOD(COLUMN(6:6),4)=1)*(6:6))
این فرمول به تابع MOD برای برگرداندن باقیمانده یک تقسیم متکی است. در این مورد، چیزی که تقسیم می شود، تعداد ستون یک سلول بر مقدار 4 است. این باعث می شود که 0، 1، 2 یا 3 باقی بماند. هر چهارمین سلول در یک ردیف همان باقیمانده را خواهد داشت. بنابراین، ستون A (همچنین به عنوان ستون 1 شناخته می شود) دارای مقدار MOD 1 خواهد بود (1 تقسیم بر 4 برابر با 0 است، با 1 باقی مانده)، و همچنین ستون های E، I، M و غیره.
توجه داشته باشید که فرمول مقایسه می کند که آیا مقدار MOD 1 است یا خیر. اگر اینطور باشد، مقایسه True (1) را برمی گرداند. اگر اینطور نباشد، False (0) را برمی گرداند. سپس در برابر سلول در ردیف ششم ضرب می شود. در نهایت SUMPRODUCT همه این ضرب ها را جمع می کند و نتیجه دلخواه را می دهد.
در حالی که این فرمول مجموع هر سلول چهارم در ردیف ششم را ارائه می دهد، می توان آن را به راحتی تغییر داد تا مجموع هر سلول سوم، سلول پنجم یا هر فاصله ای که می خواهید ارائه شود. به سادگی عدد 4 را در تابع MOD به بازه مورد نظر تغییر دهید.
اگر می‌خواهید در هر «خوشه» چهار سلولی یک سلول متفاوت برای جمع‌بندی انتخاب کنید، تنها کاری که باید انجام دهید این است که مقدار مقایسه شده در تابع MOD را تغییر دهید. در این مثال، تنها سلول اول در هر خوشه چهارتایی MOD 1 (A، E، I، M و غیره) خواهد داشت. اگر در عوض بخواهید هر چهارمین خانه را که مثلاً با سلول C شروع می شود، جمع کنید، مقدار مقایسه را از 1 به 3 تغییر می دهید. چرا؟ زیرا C سومین سلول در خوشه است و مانند هر سلول چهارم (G، K، O و غیره) MOD 3 خواهد بود.
تنها مشکل این قانون کلی این است که اگر بخواهید سلول چهارم را در هر خوشه چهار سلولی جمع کنید. به عنوان مثال، ممکن است بخواهید سلول های D، H، L، P و غیره را جمع کنید. در این مورد، مقدار مقایسه ای که استفاده می شود 4 نخواهد بود زیرا هنگام انجام یک عملیات MOD که شامل تقسیم بر 4 است، هرگز باقیمانده 4 باقی نمی ماند. ، مقدار مقایسه مانند زیر 0 خواهد بود:
=SUMPRODUCT((MOD(COLUMN(6:6),4)=0)*(6:6))
اگر ترجیح می دهید با فرمول های آرایه کار کنید، می توانید از تغییرات کمی کوتاه تر در فرمول بالا استفاده کنید:
=SUM(IF(MOD(COLUMN(6:6),4)=1,6:6))
توجه داشته باشید که فرمول باید با فشردن کلیدهای Ctrl+Shift+Enter وارد شود. سپس در نوار فرمول با پرانتز ({ }) در اطراف فرمول ظاهر می شود. همان نکات اصلاحی مربوط به مقسوم‌کننده MOD و مقدار مقایسه در اینجا اعمال می‌شود که در مورد تابع SUMPRODUCT اعمال می‌شود.
هر دوی این رویکردهای فرمولی (SUMPRODUCT و فرمول آرایه) هر چهارمین خانه در کل ردیف را جمع می کنند. اگر در عوض می‌خواهید سلول‌هایی را که مجموع از آنها مشتق می‌شود به بخشی از ردیف محدود کنید، به سادگی 6:6 (هر دو نمونه) را با محدوده مناسب جایگزین کنید. بنابراین، اگر بخواهید فقط هر چهارمین سلول را در محدوده A6:Z6 جمع کنید، از آن محدوده در فرمول استفاده می کنید.
اگر جمع‌بندی زیادی را به این روش انجام می‌دهید، و آن را نه تنها برای محدوده‌های یک ردیف، بلکه در محدوده‌های یک ستون اعمال می‌کنید، ممکن است بخواهید یک تابع تعریف‌شده توسط کاربر برای انجام جمع‌سازی ایجاد کنید. تابع ساده زیر این کار را انجام می دهد:
Function SumEveryFourth(MyRange As Range)
Dim x As Integer
SumEveryFourth = 0
For x = 1 To MyRange.Cells.Count
If (x Mod 4) = 1 Then
SumEveryFourth = SumEveryFourth + MyRange.Cells(x).Value
End If
Next x
End Function
این تابع محدوده ارسال شده به آن را بررسی می کند و سپس هر سلول چهارم را که با اولین سلول در محدوده شروع می شود، جمع می کند. اگر ترجیح می دهید که مجموع هر سلول دوم در محدوده باشد، سپس مقدار مقایسه را در عبارت If تغییر دهید، همانطور که قبلا در این نکته بحث شد. (از آنجایی که عملیات Mod در این تابع استفاده می‌شود، و مانند تابع کاربرگ MOD عمل می‌کند، پس همان مقادیر مقایسه برای تعیین اینکه کدام سلول در هر خوشه باید جمع شود، وارد عمل می‌شود.)
تابع تعریف شده توسط کاربر بر روی سلول های یک ردیف یا سلول های یک ستون به خوبی کار می کند. همانطور که در اینجا نشان داده شده است، فقط باید مطمئن شوید که محدوده مورد نظر خود را پاس کرده اید:
=SumEveryFourth(C3:C57)
