8 простих способів як порахувати в Excel суму стовпця

8 простих способів обчислити суму стовпця в Excel Як швидко та легко розрахувати суму в Excel? Дуже часто нас цікавить сума по стовпцю чи рядку. Спробуйте різні способи знайти суму по стовпцям, використовуйте функцію СУМ або перетворіть свій діапазон на інтелектуальну таблицю для простих обчислень, додайте дані з кількох стовпців або навіть різних таблиць. Все це ми побачимо на прикладах.

Як підсумувати весь стовпець чи рядок.

Якщо ввести функцію вручну, в електронній таблиці Excel з'являться різні можливості розрахунку. У нашій таблиці записується щомісячний дохід у відділах. Якщо покласти формулу суми G2 = СУМ (B: B) тоді ми отримуємо загальну виручку по першому відділу. Зверніть увагу, що наявність тексту, а не числа в осередку B1, жодним чином не вплинула на підрахунок. Додаються лише числові значення, а символьні значення ігноруються.

Важлива нотатка! Якщо серед чисел виявиться дата, це серйозно позначиться на правильності розрахунків. Справа в тому, що дати зберігаються в Excel у вигляді чисел і починають відлік з 1900 року щодня. Тому будьте обережні, обчислюючи суму стовпця Excel і повністю використовуючи її у формулі.

Все сказане повною мірою стосується і роботи з рядками. Але складати стовпець цілком рідко. У більшості випадків область, з якою ми будемо працювати, потребує більш тонкого та точного визначення.

Підсумовуємо діапазон осередків.

важливо навчитися правильно вказувати діапазон даних.Ось як це зробити, якщо скласти продажі за перший квартал: Формула розрахунку виглядає так: = СУМ (B2: D4) Ви також можете застосувати його до кількох областей, які не перетинаються та розташовані у різних місцях вашої електронної таблиці. У формулі ми послідовно перераховуємо кілька діапазонів: = СУМ (B2: D4; B8: D10) Звичайно, може не два, а набагато більше: до 255 штук.

Як обчислити суму кожного N-го рядка.

У таблиці представлені показники, які повторюються з деякою періодичністю: продаж відділів. Вам потрібно порахувати загальний прибуток по кожному з них. Складність у тому, що цікаві для нас показники не близькі, а чергуються. Припустимо, щомісячно аналізуємо дані про продажі для трьох відділів. Продаж повинен визначатися для кожного відділу. Іншими словами, рухаючись вниз, потрібно брати один рядок із трьох. Це можна зробити двома способами. Перший - найпростіший, "передній". Складаємо всі номери потрібного відділу звичайною математичною операцією додавання. Звучить просто, але уявіть, що маєте статистику, скажімо, за 3 роки? Прийде обчислити 36 чисел… Другий спосіб — «просунутіший», але універсальний. Ми пишемо = СУМ (ЯКЩО (ВІДПОЧИНОК (РЯДОК (C2: C16) +1; 3) = 0; C2: C16))

Потім натисніть комбінацію клавіш CTRL+SHIFT+ENTER, оскільки використовується формула масиву. Excel сам додасть ліву та праву фігурні дужки.

Як це працює? Нам потрібен перший, третій, шостий і таке інше. За допомогою функції LINE() обчислюємо номер поточної позиції. І якщо залишок від розподілу на 3 дорівнює нулю, значення буде враховано при обчисленні. В іншому випадку немає. Для цього лічильника ми будемо використовувати номери рядків. Але наша перша цифра знаходиться у другому рядку аркуша Excel.Так як нам потрібно починати з першої позиції, а потім брати кожну третю, а діапазон починається з 2-го рядка, тому ми додаємо 1 до його порядкового номера, щоб наш лічильник почав відлік з числа 3. Для цього вираз LINE (C2: C16 ) +1 використовується. Отримуємо 2 + 1 = 3, залишок від розподілу на 3 дорівнює нулю. Отже, візьмемо перший, третій, шостий і таке інше. Формула масиву означає, що Excel повинен послідовно перебирати всі комірки в діапазоні від C2 до C16 і виконувати вищезазначені операції з кожною з них. Коли ми знайдемо продажу для відділу 2, ми змінимо вираз: = СУМ (ЯКЩО (ВІДПОЧИНОК (РЯДОК (C2: C16); 3) = 0; C2: C16)) Ми нічого не додаємо, так як перше збігається значення знаходиться на 3- й позиції. Аналогічно для Дивізіону 3 = СУМ (ЯКЩО (ВІДПОЧИНОК (РЯДОК (C2: C16) -1,3) = 0; C2: C16)) Замість додавання 1 ми тепер віднімаємо 1, так що рахунок починається з 3. Тепер ми візьмемо кожну третю позицію, починаючи з 4-ї. І, звичайно, не забудьте натиснути CTRL + SHIFT + ENTER. Примітка. Так само ви можете підсумувати кожен n-й стовпець у таблиці. Тільки замість функції РЯДКУ () вам потрібно буде використовувати стовпець().

Сума кожних N рядків.

В електронній таблиці Excel записується щоденний виторг магазину за тривалий період часу. Вам потрібно розрахувати тижневий прибуток для кожного дня тижня. Ми використовуємо той факт, що SUM () може додавати значення не тільки в діапазон даних, але і масив. Цей масив значень може бути наданий функцією ЗМІШЕННЯ. Пам'ятайте, що тут потрібно вказати кілька аргументів: 1. Відправна точка. Зверніть увагу, що ми запровадили C2 як абсолютне посилання. 2. Скільки сходів вниз необхідно зробити 3. Скільки кроків вправі зробити.Потім ми підходимо до початкової точки (зверху зліва) масиву. 4. Скільки значень взяти знову знижуючись. 5. Скільки стовпців буде у масиві. Ми приходимо до кінцевої точки (внизу праворуч) масиву значень. Отже, формула для першого тижня: = СУМ (Зміщення ($ C $ 2; (рядок () - 2) * 5,0,5,1)) У цьому випадку ROW () схожий на наш тижневий лічильник. Зворотний відлік повинен починатися з 0, щоб дії починалися безпосередньо з комірки C2 без руху вниз. Для цього ми використовуємо STRING() — 2. Оскільки сама формула знаходиться в осередку F2, результатом буде 0. Вихідною точкою буде C2, а її кінець матиме 5 менших значень у тому ж стовпці. СУМ просто додасть п'ять запропонованих значень. Для 2-го тижня F3 просто скопіюйте формулу. ROW() - 2 дасть тут результат 1, тому початок масиву буде 1 * 5 = 5, що складає 5 значень в осередках від C7 до C11. І т.п.

Як знайти суму найбільших (найменших) значень.

Мета: скласти 3 максимальні або 3 мінімальні значення. Функція НАЙБІЛЬШИЙ повертає найбільше значення у списку даних. Хитрість у цьому, що його другий аргумент показує, яке значення має бути повернуто: 1 — найбільше, 2 — друге найбільше тощо. І якщо ви вкажете <1; 2; 3>, то вам знадобляться три найбільші. Але не забудьте застосувати формулу масиву та закінчити за допомогою CTRL+SHIFT+ENTER. = СУМ (найбільший (B2: D13; <1; 2; 3>)) Аналогічна ситуація і з меншими значеннями: = СУМ (МАЛЕНЬКИЙ (B2: D13; <1; 2; 3>))

3-D сума, або працюємо з кількома аркушами робочої книги Excel.

Щоб підрахувати числа однієї і тієї ж форми діапазону на декількох аркушах, ви можете записати координати даних, використовуючи спеціальний синтаксис, що має назву «тривимірне посилання».Допустимо, у вас є таблиця з щотижневими даними на кожному аркуші вашої книги. Ви повинні зібрати все докупи і отримати сховище за один місяць. Для цього ми звернемося до чотирьох аркушів. Подивіться це коротке відео, щоб побачити, як використовуються тривимірні формули. Як бачите, у нас 4 однакові таблиці. Стандартний метод визначення щомісячного доходу = СУМ (тиждень1! B2: B8; тиждень2! B2: B8; тиждень3! B2: B8; тиждень4! B2: B8) Ми перераховуємо діапазони даних як аргументи. А ось і 3D-метод: = СУМ (тиждень1: тиждень4! B2: B8) Ми говоримо програмі виконувати обчислення з осередками B2: B8, знайденими на робочих аркушах week1, week2, week3, week4. Тут номер листа послідовно збільшується на 1. Важлива нотатка. Ви можете використовувати 3D-посилання та в інших випадках. Наприклад, вираз = СЕРЕДНІЙ (тиждень1! B2: B8; тиждень2! B2: B8; тиждень3! B2: B8; тиждень4! B2: B8) знайде середній денний дохід за місяць.

Пошук потрібного стовпця та розрахунок його суми.

Є інформація про продаж товарів, для кожного з них виділено окрему колонку. Вам необхідно розрахувати продаж обраного товару. Іншими словами, нам потрібно вирахувати суму стовпців в Excel, але спочатку нам просто потрібно знайти цей стовпець, який нам потрібен, серед інших схожих. Для пошуку та вибору конкретного продукту ми використовуємо дуже популярну комбінацію функцій ІНДЕКС + ПОШУК. Розраховуємо суму в G3 наступним чином: = СУМ (ІНДЕКС (B2: D21,0; ПОШУК (F3; B1: D1,0))) Потім комбінація ІНДЕКС + ПОШУК повинна повернути для подальших обчислень набір чисел у вигляді вертикального масиву, який потім буде підсумовано. Опишемо це докладніше.ПОШУКПОЗ знаходить бажаний продукт (банани) у заголовку назв таблиць B1: D1 і повертає його порядковий номер (тобто 2). Потім ІНДЕКС вибирає відповідний номер стовпця (другий) із масиву значень B2: D21. Повернеться весь стовпець даних з відповідним номером, оскільки номер рядка (перший параметр функції) заданий як 0. На малюнку це буде C2: C21. Залишилося лише порахувати всі значення у цьому стовпці. У цьому випадку, щоб уникнути помилок при реєстрації назви продукту, рекомендується використовувати список, що розкривається в F3, а значення для заповнення повинні бути взяті з B1: D1.

Сума стовпців із кількох таблиць.

Як порахувати суму стовпця в Excel, якщо таких стовпців кілька, і вони перебувають у різних таблицях? Щоб отримати результати відразу для кількох таблиць, ми також використовуємо функцію СУМ та структуровані посилання. Ці посилання з'являються під час створення розумної таблиці в Excel. Коли ви його створюєте, Excel дає ім'я таблиці і кожному заголовку стовпця, який вона містить. Ці імена можна використовувати у виразах — вони можуть відображатися як підказки в рядку введення. У нашому випадку це виглядає так:

Щоб створити інтелектуальну таблицю, виберіть діапазон A1: B21 та виберіть «Форматувати як таблицю» на стрічці «Головна». Приємною перевагою тут є те, що найрозумніша таблиця змінює свій розмір при додаванні (або видаленні) даних, вам не потрібно налаштовувати посилання на неї. Крім того, у нашому випадку немає значення, де саме ці дані знаходяться у вашому файлі Excel.Навіть не важливо, що вони на різних аркушах: програма все одно знайде їх на ім'я. Крім того, якщо використовувані таблиці містять рядок підсумків, ми перепишемо нашу формулу наступним чином: = СУМ (Таблиця2 [#Totals]; Table3 [#Totals]) А якщо будуть внесені зміни або додані числа, все буде автоматично перераховано. Примітка. Повинний бути включений підсумковий рядок у таблиці. Якщо ви вимкнете його, вираз поверне помилку #REF. Ще одне важливе зауваження. Незадовго до цього ми сказали, що функція СУМ повинна складати суму всіх значень у рядку або стовпці, навіть якщо вони приховані або фільтр значень не дозволить вам їх побачити. У нашому випадку, якщо до таблиці включено рядок підсумків, ви використовуватимете її для отримання суми лише видимих ​​осередків. Як ви можете бачити на цьому малюнку, якщо ви фільтруєте деякі значення, загальний обсяг продажів, розрахований за допомогою другого методу, зміниться. Якщо ви просто додаєте комірки і не використовуєте останній рядок інтелектуальної таблиці, фільтрація та приховування окремих елементів не змінює результату обчислень. Ми сподіваємося, що тепер вам стало набагато простіше підсумовувати області даних чи окремі осередки.

Як порахувати суму в Excel: докладна інструкція зі скріншотами

Розповіли про п'ять способів підсумовування значень в електронних таблицях. Показали, як підсумовувати осередки за одним чи декількома умовами.

Ілюстрація: Meery Mary для Skillbox Media

Розповідає просто про складні речі зі світу бізнесу та управління. До редактури — п'ять років у банку та три — в оцінці майна. Розбирається в Excel, фінансах та корпоративному житті.

Підсумовування значень осередків – базова функція електронних таблиць.Замість того, щоб вручну складати числа на калькуляторі, можна підсумовувати кілька десятків, сотень і тисяч значень лише за кілька секунд.

У Excel є п'ять основних способів обчислити суму: за допомогою арифметичної формули, за допомогою кнопки «Автосума» або використовуючи функції СУМ, СУМІСЛІ, СУМІСЛИМН. В інструкції покажемо на прикладах, як використовувати всі ці способи, а також розповімо, як провести підсумовування в Google Таблицях.

• Як порахувати суму за допомогою арифметичної формули
• Як підсумовувати значення осередків за допомогою кнопки «Автосума»
• Як користуватися функцією СУМ
• Як порахувати суму за однією умовою
• Як порахувати суму за кількома умовами
• Як порахувати суму в Google Таблицях
• Як дізнатися більше про роботу в Excel

Як порахувати суму в Excel за допомогою арифметичної формули

Припустимо, нам потрібно підсумувати два значення таблиці — комірки А1 і А2.

Виберемо порожню комірку, в якій хочемо отримати результат підсумовування, і натиснемо на знак рівності. Далі клікнемо по першому осередку, який потрібно підсумувати, — А1. Введемо оператор додавання + і клікнемо по другому осередку для підсумовування - А2.

Одночасно з цим у вибраному осередку та у рядку формул з'явиться формула: = А1 + А2. Натисніть клавішу Enter - Excel проведе розрахунок і покаже результат.

Цей спосіб найкраще використовувати, коли значень для підсумовування не дуже багато – не більше трьох. В інших випадках краще підійдуть інші способи, про які ми розповідатимемо нижче.Якщо користуватися ними, розрахунок буде набагато швидшим.

Якщо ви починаєте розбиратися, як працювати в електронних таблицях, прочитайте наш матеріал «Як створювати формули в Excel: інструкція для новачків». Там ми розповідали про принципи роботи із формулами.

Як порахувати суму в Excel за допомогою кнопки «Автосума»

Це найшвидший спосіб підсумовувати комірки одного рядка або одного стовпця. Розберемо з прикладу, як і працює.

Припустимо, нам потрібно підсумовувати ціни автомобілів у таблиці з продажем автосалону. Виберемо порожню комірку в стовпці «Ціна, руб.» і натисніть кнопку «Σ» на головній вкладці Excel. У меню виберемо «Сума».

Якщо у вашій версії Excel кнопки немає на головній вкладці, перейдіть у вкладку «Формули» і натисніть кнопку там.

У вибраній комірці з'явиться назва формули для розрахунку суми та посилання на діапазон, який Excel автоматично визначив для підсумовування, — усі комірки з цінами авто. За необхідності діапазон можна скоригувати вручну.

У рядку формул функція набула вигляду: fx=СУМ(E2:E39).

Excel самостійно прописав у рядку формул ім'я функції та діапазон для підсумовування
Скріншот: Excel / Skillbox Media

Натискаємо Enter — у вибраному осередку з'являється результат підсумовування.

Кнопка «Автосума» працює не лише зі стовпцями, а й з рядками.

Допустимо, ціни в каталозі розташовані горизонтально. Щоб підсумувати їх, оберемо порожню комірку у рядку «Ціни, руб.» і також виберемо в меню «Σ» → «Сума».Перевіримо діапазон, значення якого пропонує підсумовувати Excel, та натиснемо Enter. Готово!

Крім підсумовування, за допомогою кнопки «Σ» можна розрахувати середнє значення діапазону, підрахувати кількість значень у вибраному стовпці або рядку, а також показати максимальне та мінімальне значення в діапазоні.

Навчання у Skillbox для тих, хто хоче стати професіоналом у Excel

• Курс «Excel + „Google Таблиці“ з нуля до PRO»
• Курс «Excel для робочих та особистих завдань»
• Безкоштовний онлайн-інтенсив «Експрес-курс по Excel: освоюємо таблиці з нуля за 3 дні»
• Безкоштовні відеоуроки з Excel та «Google Таблицям»

Як порахувати суму в Excel за допомогою функції СУМ

СУМ - Функція Excel, яка проводить підсумовування значень осередків. Це класичний спосіб для розрахунку суми кількох значень.

У попередньому способі кнопка «Автосума» використовувала для розрахунку цю функцію: автоматично прописувала в рядку формул ім'я функції та діапазон аргументів .

У деяких випадках використовувати "Автосуму" незручно - наприклад, коли в розрахунок потрібно включити не всі комірки вибраного стовпця або рядки або підсумовувати відразу кілька діапазонів. Для цього краще користуватися функцією СУММ вручну. Розберемо з прикладу, як це зробити.

Допустимо, нам потрібно підсумовувати ціни автомобілів, розташовані на різних аркушах Excel. Виберемо порожню комірку і натиснемо на кнопку «Fx» ліворуч від рядка формул.

У правій частині екрана з'явиться вікно «Побудовник формул» із переліком доступних функцій. Знайдемо функцію СУММ і натисніть на неї двічі.

У вікні потрібно ввести аргументи функції - діапазони значень для підсумовування. Для цього поставимо курсор у рядок «Число1» і мишкою виділимо перший діапазон - у нашому випадку це діапазон з цінами авто з першого листа: Е2:Е20.

Далі переставимо курсор на рядок «Число2» і виділимо діапазон другого листа Excel. У будівнику формул він відобразиться у такому вигляді: 'Лист0 (2)'!E2:E20.

Якщо потрібно додати додаткові аргументи, натисніть «+» під рядком «Число2» побудовника формул.

Потім тиснемо "Готово" - функція проводить розрахунок і показує результат у комірці, яку ми вибрали на самому початку.

Одночасно з цим функція у рядку формул набуває вигляду: Fx=СУМ(E2:E20;'Лист0 (2)'! E2:E20).

Досвідчені користувачі Excel рідко запускають будівельник формул для підсумовування вони відразу прописують функцію в рядку формул. Для цього потрібно вибрати порожню комірку, поставити знак рівності - у цьому комірці або відразу в рядку формул - і ввести назву функції. Потім у дужках через ; потрібно перерахувати комірки для підсумовування або їх діапазони та натиснути Enter.

Як порахувати суму за однією умовою: функція СУМІСЛІ

СУМІСЛІ — функція Excel, за допомогою якої можна підсумовувати не всі значення вибраного діапазону, а лише ті, що відповідають одному заданому користувачем умові. Наприклад, лише автомобілі 2017 року випуску. Або лише чорні авто.

Припустимо, у нашій таблиці продажів автосалону потрібно підсумовувати лише дані про продажі менеджера Трігубова М. Ось як це зробити.

Виберемо порожню комірку і викличемо будівельник формул, як це робили в попередньому розділі. У списку функцій знайдемо функцію СУМІСЛІ - З'явиться вікно для введення аргументів.

Потрібно ввести три аргументи функції – діапазон, критерій та діапазон підсумовування.

• Діапазон — комірки таблиці, у яких Excel шукатиме критерій користувача. У нашому прикладі потрібні дані про продаж менеджера Тригубова, тому як діапазон для пошуку критерію вказуємо весь стовпець «Менеджер»: G2:G39.
• Критерій — це значення, яке Excel повинен знайти у вибраному діапазоні. У нашому прикладі це прізвище та ім'я менеджера — вказуємо їх у такому вигляді, як вони вказані в таблиці. І обов'язково укладаємо в лапки: "Тригуб М.".
• Діапазон підсумовування – діапазон осередків, які потрібно підсумовувати. У нашому випадку це осередки стовпця «Ціна, руб.»: E2:E39.

У результаті рядок формул набуває такого вигляду: Fx=СУМІСЛИ(G2:G39;"Тригубов М.";E2:E39).

Натискаємо «Готово» — функція переглядає вибрані діапазони та підсумовує значення лише тих осередків із цінами, навпроти яких стоїть «Тригуб М.». Результат з'являється в осередку, який ми вибирали на початку.

Як порахувати суму за кількома умовами: функція СУМІСЛІМН

СУМІСЛІМН — функція Excel, за допомогою якої можна підсумовувати не всі значення вибраного діапазону, а лише ті, що відповідають декільком умовам користувача. Тобто ця функція працює аналогічно СУМІСЛІ, але заданих умов може бути більше одного.

Додамо до умови з попереднього розділу друга умова – рік випуску автомобіля. Підсумовуємо ціни авто 2021 року випуску, які продав менеджер Тригуб М.

Виберемо порожню комірку, викличемо будівельник формул і знайдемо функцію «СУМІСЛІМН».

Спочатку з'явиться лише один рядок для введення аргументів - "Діапазон_підсумовування".Як і функції СУМІСЛІ, це діапазон осередків, значення яких потрібно підсумовувати. У нашому прикладі це все осередки стовпця «Ціна, руб.» - Додамо посилання на цей діапазон в рядок аргументу.

Далі двічі тиснемо "+" - з'являться рядки для введення аргументів першої умови: "Діапазон_умови1" та "Умова1".

Заповнюємо ці рядки першою умовою - потрібно підсумовувати ціни авто, проданих менеджером Трігубова М.:

• Діапазон умови 1 - весь стовпець "Менеджер": G2:G39.
• Умова 1 - "Тригуб М.".

Далі натиснемо «+» ще двічі — з'являться рядки для введення аргументів другої умови: «Діапазон_умови2» та «Умова2».

У нашому прикладі друга умова така: автомобілі, ціни яких потрібно підсумовувати, мають бути 2021 року випуску. Заповнимо аргументи:

• Діапазон умови 2 - весь стовпець «Рік випуску»: C2:C39.
• Умова 2 - "2021".

Натискаємо «Готово» і отримуємо результат — функція переглянула вибрані діапазони і підсумовувала ті значення з цінами, навпроти яких стоїть «Тригубов М.» та «2021».

Вводимо аргументи першої та другої умови функції СУМЕСЛІМН та отримуємо результат
Скріншот: Excel / Skillbox Media

Вводимо аргументи першої та другої умови функції СУМЕСЛІМН та отримуємо результат
Скріншот: Excel / Skillbox Media

Як порахувати суму в Google Таблицях? Всі описані вище способи, за винятком кнопки "Автосума", працюють і в "Google Таблицях". Відмінність у тому, що в Google Таблицях немає побудова формул, тому синтаксис функцій доведеться вводити самостійно, у рядку формул або в порожньому осередку. Коли користувач починає писати назву функції, під рядком формул з'являються підказки, які аргументи потрібно ввести, щоб вона спрацювала.

Як дізнатися більше про роботу в Excel

• У Excel багато функцій, які спрощують та прискорюють роботу з таблицями. У добірці даємо посилання на 15 статей та відео про інструменти Excel, необхідні у повсякденній роботі.
• У Skillbox є курс "Excel + "Google Таблиці" з нуля до PRO". Він підійде як новачкам, які хочуть навчитися працювати в Excel з нуля, так і впевненим користувачам, які хочуть покращити свої навички. На курсі вчать швидко робити складні розрахунки, візуалізувати дані, будувати прогнози, працювати із зовнішніми джерелами даних, створювати макроси та скрипти.
• Крім того, Skillbox дає безкоштовний доступ до запису онлайн-інтенсиву «Експрес-курс Excel: освоюємо таблиці з нуля за 3 дні». Він підходить для користувачів-початківців. На ньому можна навчитися створювати та оформляти листи, вводити дані, використовувати формули та функції для базових обчислень, налаштовувати формати користувача та створювати формули з абсолютними та відносними посиланнями.

Чи вивчаєте Excel? Перегляньте ці інструкції Skillbox Media.

• Як зробити ВПР в Excel
• Основи Excel: працюємо з списком, що випадає
• Як зробити сортування в Excel
• Як встановити фільтр і розширений фільтр в Excel
• Логічні функції Excel: для чого потрібні і як їх використовувати

Аргументи – значення, які функція використовує для виконання розрахунків.

Як у Екселі зробити суму стовпця

Якщо ви хочете підсумувати весь стовпець без подачі верхньої або нижньої межі, ви можете використовувати функцію СУММ з конкретним синтаксисом діапазону для всього стовпця.

Excel підтримує повний стовпець і повний рядок посилання, як це:

= СУМ (A: А) // сума всього стовпця A
= СУМ (3: 3) // сума всіх рядків 3

Ви можете побачити, як це працює самостійно, набравши "A: A", "3: 3" і т.д., в полі Ім'я (ліворуч від рядка формул) і ударяючи повернення - Excel вибиратиме весь стовпець або рядок.

Повні посилання стовпців і рядків є простим способом посилання на дані, які можуть змінюватися в розмірах, але ви повинні бути впевнені, що ви не випадково не включаєте додаткові дані. Наприклад, якщо ви використовуєте = СУМ (A: A), щоб підсумувати весь стовпець А і стовпець А також включає дату десь (у будь-якому місці), ця дата буде включена в суму.

Сума стовпців на основі суміжних критеріїв

= СУМПРОВИЗВ ( — (діапазон1 = критерії); діапазон2)

Підсумовуючи стовпці на основі критеріїв, зазначених у сусідніх стовпцях, можна використовувати формулу, що базується на функції СУММПРОИЗВ.

СУМПРОВИЗВ множить, потім підсумовує твори двох масивів: масив1 і масив2. Масив1 налаштований виступати як «фільтр», щоб пропустити ті значення, які задовольняють критеріям.

Масив1 використовує діапазон, який починається у першому стовпці, який містить значення, які мають пройти критерії. Ці «критерії цінності» перебувають у колонці ліворуч, та у безпосередній близькості до них «значення даних».

Критерії застосовуються як простий тест, який створює масив істинних і помилкових значень:

Цей шматочок формули «тестує» кожне значення в першому масиві за допомогою поставлених критеріїв, а потім використовує подвійне заперечення (-), що перетворює справжні і хибні значення в одиниці і нулі.Результат виглядає так:

Зверніть увагу, що одиниці відповідають колонкам 1,5 і 7, які відповідають критеріям «А».

Для масив2 всередині СУММПРОИЗВ, ми використовуємо діапазон, «зрушений» на один стовпець праворуч. Цей діапазон починається з першого стовпця, що містить значення суми, і закінчується останньою колонкою, яка містить значення суми.

Так, у прикладі формули J5, після того, як масиви були заселені, ми маємо:

Оскільки СУМПРОВИЗВ запрограмований спеціально, щоб ігнорувати помилки, що виникають в результаті множення значення тексту, остаточний масив виглядає так:

Єдині «вижили» значення множення є тими, які відповідають 1 всередині масив1. Ви можете думати про логіку в масив1 «фільтрації» значень масив2.

Сума кожного N-го стовпця

= СУМПРОВИЗВ ( — ( ЗАЛИШОК(СТІЛБЕЦЬ(ранг) — СТІЛКОВИЙ(перший.ранг) + 1 ; n) = 0 ); ранг)

Підсумовуючи кожен N-й стовпець, ви можете використовувати формулу, засновану на функціях СУММПРОИЗВ, МОД і СТІЛК.

= СУМПРОВИЗВ( — (ЗАСТАТ( СТУПЛЕНЬ( B5: J5 ) — СТІЛКУ( B5 ) + 1 ; К5 ) = 0 ); B5: J5 )

По суті, використання СУММПРОІЗВ підсумовує значення у рядку , які були «відфільтровані» , використовуючи логіку , засновану на ЗАЛИШОК. Ключ полягає в наступному:

ЗАЛИШОК (Стовпець (B5: J5) - стовпець (B5) + 1; К5) = 0

Цей фрагмент формули використовує функцію стовпця, щоб отримати набір чисел «відносних» стовпців для діапазону, який виглядає наступним чином:

Це йде в ЗАЛИШЕННЯ, так:

де K5 це значення N у кожному рядку. Функція ОСТАТ розраховує залишок для кожного номера стовпця, поділений на N. Так, наприклад, при N = 3, ОСТАТ розраховуватиме щось на кшталт цього:

Зауважте, що нулі з'являються в стовпці 3, 6, 9 і т.д. Формула використовує = 0, щоб перетворити значення ІСТИНА, якщо залишок дорівнює нулю, і БРЕХНЯ, якщо ні, ми використовуємо подвійне заперечення (-), що примушує ІСТИНА/БРЕХНЯ в одиниці та нулі. Це становить масив на кшталт цього:

Де перші в даний час вказують на «ступеня n значення». Це йде у СУМПРОВИЗВ як масив1, поряд з B5: J5, як масив2. СУМПРОВИЗВ потім робить свою справу, спочатку помножуючи, потім підсумовуючи твори масивів.

Тільки «вижили» цінності множення є тими, де масив1 містить 1. Таким чином, ви можете думати про логіку масив1 «фільтрації» значення масив2.

Підсумовувати будь-який інший стовпець

Якщо ви хочете підсумувати будь-який інший стовпець, просто адаптуйте цю формулу при необхідності, маючи на увазі, що формула автоматично надає 1 до першого стовпця в діапазоні. Підсумовуючи парні стовпці, використовуйте:

= СУМПРОВИЗВ ( — (ЗАЛИШОК ( СТІЛКА ( A1: Z1 ) — СТІЛКА ( A1 ) + 1 ; 2 ) = 0 ); A1: Z1 )

Підсумовуючи непарні стовпці, використовуйте:

= СУМПРОВИЗВ ( — ( ЗАЛИШОК ( СТІЛКА ( A1: Z1 ) — СТІЛКА ( A1 ) + 1 ; 2 ) = 1 ); A1: Z1 )

Сума останніх n стовпців

= СУМ ( ІНДЕКС ( дані ; 0 ; СТУПНИК ( дані ) - (n - 1 )) : ІНДЕКС ( дані ; 0 ; СТУПНИК ( дані )))

Підраховуючи останні n стовпці в таблиці даних (тобто останні 3 стовпці, останні 4 стовпці тощо), ви можете використовувати формулу, що базується на функції ІНДЕКС.

У наведеному прикладі формула К5:

де "дані" є іменований діапазон С5: H8.

Ключ до розуміння цієї формули є тим, що функція ІНДЕКС може бути використана для повернення посилання на цілий рядок і стовпець.

Щоб створити посилання на останні n стовпці в таблиці, ми ділимо посилання на дві частини, з'єднаних оператором діапазону. Для того, щоб отримати посилання на лівий стовпець, ми використовуємо:

ІНДЕКС ( дані ; 0 ; СТІЛК ( дані ) - ( К4 - 1 ))

Оскільки дані містить 6 стовпців і К4 містить 3, це спрощує:

Для того, щоб отримати посилання на правий стовпець у діапазоні, ми використовуємо:

Який розраховує посилання на стовпець 6 названого діапазону «дані», так як функція СТІЛК розраховує 6:

Разом ці дві функції ІНДЕКС розраховують посилання на стовпці з 4 по 6 даних (тобто F5: Н8), які можна звести в масив значень всередині функції СУМ:

Функція СУМ потім обчислює суму, 135.