Часто буває, що потрібно підсумувати кожну другу, третю, четверту і т. Д. Осередок в електронній таблиці. Тепер, завдяки наступного трюку, це можна зробити.
В Excel не передбачено стандартної функції, яка може підсумувати кожну n-ю осередок або рядок. Однак можна виконати це завдання декількома різними способами. Всі ці підходи засновані на функціях СТРОКА (ROW) і ОСТАТ (MOD).
Помістіть функцію СТРОКА (ROW) в функцію ОСТАТ (MOD) (щоб передати числовий аргумент), розділіть на 2 (щоб підсумувати кожну другу осередок) і перевірте, чи не рівний результат нулю. Якщо це так, осередок підсумовується. Ці функції можна використовувати найрізноманітнішими способами - деякі забезпечать кращий результат, ніж інші. Наприклад, формула масиву для підсумовування кожної другої комірки в діапазоні $ А $ 1: $ А $ 100 могла б виглядати так: = SUM (IF (MOD (ROW ($ A $ 1: $ А $ 500); 2) = 0; $ А $ 1: $ А $ 500; 0)). в російській версії Excel = СУММ (ЯКЩО (ОСТАТ (СТРОКА ($ А $ 1: $ А $ 500); 2) = 0; $ А $ 1: $ А $ 500; 0)).
Оскільки це формула масиву, необхідно ввести її, натиснувши поєднання клавіш Ctrl + Shift + Enter, Excel додасть фігурні дужки, щоб вона виглядала так:. в російській версії Excel: Потрібно, щоб Excel сам додав фігурні дужки; якщо ви додасте їх самостійно, формула не буде працювати.
Хоча мета досягнута, такий спосіб негативно впливає на дизайн електронної таблиці. Це непотрібне застосування формули масиву. Щоб ще більше погіршити справи, в цю довгу формулу вкладена перераховується функція СТРОКА (ROW), що перетворює велику формулу також в перераховується. Це означає, що вона буде постійно перераховуватися, що б ви не зробили в робочій книзі. Це дуже поганий спосіб!
Ось інша формула, що представляє собою трохи кращий вибір: = SUMPRODUCT ((MOD (ROW ($ A $ 1: $ A $ 500); 2) = 0) * ($ A $ 1: $ A $ 500)). в російській версії Excel = СУММПРОИЗВ ((ОСТАТ (СТРОКА ($ А $ 1: $ А $ 500); 2) = 0) * ($ А $ 1: $ А $ 500)).
Потрібно, однак, пам'ятати, що ця формула поверне помилку # значить! (#VALUE!), Якщо будь-які осередки діапазону містять не числа, а текст. Ця формула, хоча і не є фактично формулою масиву, також уповільнює роботу Excel, якщо використовувати її занадто багато разів або якщо кожен раз вона посилається на великий діапазон.
На щастя, є кращий спосіб, який представляє собою не тільки більш ефективне, але і набагато більш гнучке рішення. Він вимагає застосування функції БДСУММ (DSUM). У цьому прикладі ми використовували діапазон А1: А500 як діапазон, в якому необхідно підсумувати кожну n-ю осередок.
У осередок Е1 введіть слово Criteria. У осередок Е2 введіть наступну формулу: = MOD (ROW (A2) - $ C $ 2-1; $ C $ 2) = 0. в російській версії Excel = ОСТАТ (СТРОКА (А2) - $ З $ 2-1; $ З $ 2) = 0. Виділіть клітинку С2 і виберіть команду Дані → Перевірка (Data → Validation).
В поле Тип даних (Allow) виберіть пункт Список (List), а в полі Джерело (Source) введіть 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. Переконайтеся, що увімкнено Архів допустимих значень (In-Cell), і клацніть на кнопці ОК. В осередку С1 введіть текст SUM every .... У будь-якому осередку, крім рядка 1, введіть наступну формулу: = DSUM ($ A: $ A; 1; $ E $ 1: $ E $ 2). в російській версії Excel = БДСУММ ($ А: $ А; 1; $ Е $ 1: $ Е $ 2).
В осередку безпосередньо над тією, де ви ввели функцію БДСУММ (DSUM), введіть текст = "Summing Every" $ З $ 2 CHOOSE ($ C $ 2; "st"; "nd"; "rd"; "th"; "th"; "th"; "th"; "th"; "th"; "th") "Cell". Тепер залишилося тільки вибрати потрібну кількість в комірці С2, а інше зробить функція БДСУММ (DSUM).
За допомогою функції БДСУММ (DSUM) можна підсумувати осередки через вказаний вами інтервал. Функція БДСУММ (DSUM) набагато ефективніше, ніж формула масиву або функція СУММПРОИЗВ (SUMPRODUCT). Хоча настройка займає трохи більше часу, це той випадок, коли важко в навчанні, легко в бою.