Зведення способів додавання та підраховування даних Excel

Зведення способів додавання та підраховування даних Excel

Діалогове вікно "Надіслати до Microsoft Word"
Підсумовування (додавання значень) – невід’ємна складова аналізу даних незалежно від того, чи ви обчислюєте проміжний підсумок збуту в певному регіоні, чи підраховуєте підсумок для квитанцій за тиждень. Підсумувати дані в програмі Excel можна кількома способами.

Щоб допомогти вам зробити правильний вибір, ми надали в цій статті вичерпні відомості про всі способи, довідкову інформацію, а також посилання на статті з докладною інформацією.

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

У цій статті

Просте додавання й віднімання

Ви можете додавати й віднімати числа, натискаючи кнопку або застосовуючи функцію аркуша.

Додавання значень у клітинці за допомогою простої формули

Якщо вам просто потрібен швидкий результат, ви можете скористатися програмою Excel як міні-калькулятором. Для цього використовуйте у формулі арифметичний оператор + (знак "плюс"). Наприклад, якщо ввести в клітинці формулу =5+10, програма Excel відобразить результат 15.

Докладні відомості про використання арифметичних операторів у формулі див. в статті Використання програми Excel як калькулятора.

На початок сторінки

Віднімання значень у клітинці за допомогою простої формули

Для цього використовуйте арифметичний оператор - (знак "мінус"). Наприклад, формула =12-9 відобразить результат 3.

Докладні відомості про використання арифметичних операторів у формулі див. в статті Використання програми Excel як калькулятора.

На початок сторінки

Додавання значень у стовпці або рядку за допомогою кнопки

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

Змінення розміру таблиці

Докладні відомості про використання функції "Автосума" див. в статті Використання програми Excel як калькулятора.

На початок сторінки

Додавання значень у діапазоні за допомогою функції

Функція SUM корисна, коли вам потрібно додати чи відняти значення з різних діапазонів або ж поєднати числові значення з діапазонами чисел. Використовуйте функцію SUM, щоб додати всі аргументи, які ви вказуєте в дужках. Кожен аргумент може бути діапазоном, посиланням на клітинку або додатним чи від’ємним числовим значенням.

Щоб ввести просту формулу, введіть у клітинці =SUM і ліву дужку. Далі введіть одне або кілька чисел, посилань на клітинку чи діапазонів клітинок, розділивши їх комами. Потім введіть праву дужку та натисніть клавішу Enter, щоб відобразити результат. Ви також можете скористатися мишею, щоб виділити клітинки з даними, які потрібно підсумувати.

1

2

3

A

Відвідування

4823

12335

Наприклад, якщо використовуються дані в попередній таблиці, усі наведені нижче формули використовують функцію SUM, щоб повертати одне й те саме значення (17158):

  • =SUM(4823;12335)

  • =SUM(A2;A3)

  • =SUM(A2:A3)

  • =SUM(A2;12335)

На рисунку нижче показано формулу, яка використовує функцію SUM, щоб додати значення клітинки A2 та 12 335. Підказка під формулою містить поради щодо використання функції SUM.

Використання функції SUM для додавання клітинки та значення

Примітки : 

  • У програмі Excel немає функції SUBTRACT. Щоб відняти значення за допомогою функції, використовуйте від’ємні значення з функцією SUM. Наприклад, формула =SUM(30;A3;-15;-B6) додає 30 і значення в клітинці A3, віднімає 15, а потім віднімає значення в клітинці B6.

  • Ви можете включити як аргументи у функції SUM до 255 числових значень або посилань на клітинку чи діапазон у будь-якій комбінації.

Докладні відомості див. в статті Функція SUM.

На початок сторінки

Віднімання значень у діапазоні за допомогою функції

Використовуйте функцію SUM, щоб віднімати числа. Для цього вводьте числа, які потрібно відняти, як від’ємні числа у формулі.

1

2

3

A

Відвідування

29072

12335

Наприклад, якщо використовуються дані в попередній таблиці, усі наведені нижче формули використовують функцію SUM, щоб повертати одне й те саме значення (16737):

  • =SUM(29072;-12335)

  • =SUM(A2;-A3)

  • =SUM(A2;-12335)

  • =SUM(A2;(-1*(A3)))

На початок сторінки

Підсумування даних за допомогою групування та зведення

Використовуйте структуру або звіт зведеної таблиці, щоб групувати й сумувати дані.

Додавання значень у стовпці за допомогою структури

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

1

2

3

4

5

6

7

A

B

C

Регіон

Місяць

Продажі

Схід

Січ

18 000₴

Схід

Лют

23 000₴

Схід

Бер

19 000$

Захід

Січ

17 000$

Захід

Лют

27 000$

Захід

Бер

21 000$

На ілюстрації нижче показано структуру з проміжними підсумками, згрупованими за регіонами, і загальний підсумок.

Структура з проміжними та загальним підсумками

Використовуйте команду Проміжні підсумки (вкладка Дані, група Структура), щоб створювати структуру, проміжні підсумки та загальний підсумок.

Вихідні дані включали три рядки даних для східного регіону та три рядки даних для західного регіону (рядки 2–7). Зверніть увагу, що в результаті операції проміжних підсумків у рядок 5 вставлено дані "Схід усього": між останнім рядком даних "Схід" і першим рядком даних "Захід".

Якщо клацнути клітинку A4, а потім вибрати команду Проміжні підсумки, програма Excel створить структуру, вставить рядки для даних Схід усього, Захід усього та Загальний підсумок і введе ці підсумки в клітинки стовпця Збут.

Докладні відомості див. в цих статтях:

Створення структури списку даних на аркуші

Вставлення проміжних підсумків у список даних на аркуші

На початок сторінки

Додавання значень у списку або стовпці таблиці Excel за допомогою функції

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

Примітка : Функція SUBTOTAL може визначати, чи включати в підрахунок приховані рядки, і завжди ігнорує відфільтровані рядки.

Припустімо, наприклад, що вам потрібно обчислити проміжні й загальний підсумки для даних у клітинках C2–C7, але ви хочете ігнорувати приховані дані в рядках 3 та 6. Функція, яку ви використаєте, матиме такий вигляд:

= SUBTOTAL( 109;C2:C7)

Перший аргумент (109) указує, що ви хочете додати значення в діапазоні та ігнорувати приховані значення. Щоб включити приховані значення, використайте як перший аргумент число 9.

Докладні відомості див. в статтях Функція SUBTOTAL і Обчислення підсумків даних у таблиці Excel.

На початок сторінки

Підсумування даних і їх розгляд із різних точок зору

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

Щоб швидко створити зведену таблицю, виберіть клітинку в діапазоні даних або таблиці Excel, а потім на вкладці Вставлення в групі Таблиці натисніть кнопку Зведена таблиця.

Зверніть увагу: у прикладі нижче дані про збут складаються з багатьох рядків (насправді їх 40, але на рисунку показано лише частину). Крім того, дані не підсумовано та для них не вказано проміжні та загальний підсумки.

Невеликий триб "Настройки", що відображається замість елемента "Настроювання сайту".

Звіт зведеної таблиці, який ґрунтується на таких самих даних, містить проміжні та загальні підсумки, а також стисле зведення.

Математичний символ

Щоб створювати зведені таблиці та працювати з ними, може знадобитися попередня підготовка даних і вивчення деяких понять.

Докладні відомості, які допоможуть почати роботу, див. в таких статтях:

Підрахунок значень у звіті зведеної таблиці

Поля підсумків і проміжних підсумків у звіті зведеної таблиці

На початок сторінки

Підсумування даних на основі однієї або кількох умов

Використовуючи функції аркуша, ви можете застосовувати умови (також відомі як критерії), щоб додавати лише ті дані, які відповідають заданим умовам.

Додавання значень у діапазоні на основі однієї умови за допомогою однієї функції або поєднання функцій

Використовуйте функцію SUMIF або вкладіть функцію SUM у функцію IF.

Ви можете додавати числа на основі однієї умови за допомогою функції SUMIF або за допомогою поєднання функцій SUM та IF.

Наприклад, формула =SUMIF(A2:A6;">20") додає лише ті числа в діапазоні A2–A6, які більше 20.

Примітка : Наведена далі інформація носить дещо технічний характер, але вона безумовно варта уваги.

Якщо функції SUMIF немає, ви все одно можете отримати таку саму відповідь, поєднавши функції IF і SUM. Поєднання функцій таким чином також відоме як "вкладення", оскільки одна функція використовується в іншій.

Щоб створити формулу для імітації функції SUMIF, введіть формулу, яка обробляє діапазон A2–A6 як масив, тобто діапазон розглядається як єдиний об’єкт, що містить п’ять клітинок.

Ваша формула матиме такий вигляд: {=SUM(IF(A2:A6>20;A2:A6))}. Фігурні дужки навколо формули вказують, що це формула масиву. Щоб створити формулу масиву, потрібно виконати певні особливі дії: замість того, щоб самостійно вводити фігурні дужки, натисніть клавіші Ctrl+Shift+Enter, і тоді програма Excel візьме формулу =SUM(IF(A2:A6>20;A2:A6)) у дужки. Якщо ви самі введете фігурні дужки, формула масиву не створиться.

Спробуйте це!    

Скопіюйте дані з наведеної нижче таблиці на аркуш у клітинку A1. Вставивши ці дані, ви помітите, що в клітинці A10 з’явилася помилка #VALUE!. Це означає, що вам потрібно перетворити клітинку A10 на формулу масиву. Для цього натисніть клавішу F2, а потім – сполучення Ctrl+Shift+Enter. Програма Excel відобразить результат (65) у клітинках A8 і A10.

Копіювання прикладу даних на аркуші

  • Створіть пустий аркуш або книгу.

  • Виділіть приклад у розділі довідки.

    Примітка : Заголовки рядків і стовпців виділяти не потрібно.

    Виділення прикладу в довідці
    Виділення прикладу в довідці
  • Натисніть клавіші Ctrl+C.

  • На аркуші виділіть клітинку A1 і натисніть клавіші Ctrl+V.

  • Щоб переключатися між режимом перегляду результатів і режимом перегляду формул, які повертають результати, натискайте клавіші Ctrl+` (тупий наголос).

1

2

3

4

5

6

7

8

9

10

A

Значимість

18

29

36

11

16

Використання функції SUMIF

=SUMIF(A2:A6;">20")

Використання функцій SUM та IF

=SUM(IF(A2:A6>20;A2:A6))

Крім того, у статті бази знань XL: Використання функції SUM(IF()) замість CountBlank() наведено додаткові рекомендації щодо того, коли слід використовувати функцію SUMIF.

На початок сторінки

Додавання значень у стовпці на основі однієї або кількох умов за допомогою функції

Використовуйте функцію DSUM, щоб додавати числа в полі (стовпці) записів у списку або базі даних, які відповідають заданим умовам (також відомим як критерії).

Ви також можете використовувати функцію DSUM, коли у вас є список стовпців і вам простіше визначити умови в окремому діапазоні клітинок, ніж використовувати вкладену функцію.

Докладні відомості див. в статті Функція DSUM.

На початок сторінки

Додавання значень у діапазоні на основі кількох умов за допомогою майстра (стосується тільки Excel 2007)

Ви можете використовувати майстер підсумовування (Формули > Рішення > Умовна сума), щоб додавати значення на основі кількох умов.

Увага! : Майстер підсумовування доступний лише в Excel 2007 і попередніх версіях. Докладні відомості див. в статті Що сталося з надбудовою "Майстер підсумовування"?.

Якщо ця команда недоступна (група Рішення, можливо, просто не відображається), інсталюйте та завантажте надбудову "Пакет аналізу".

Завантаження надбудови "Пакет аналізу"

Натисніть кнопку Microsoft Office  рисунок на кнопці , потім – Параметри Excel, а потім виберіть категорію Надбудови.

  1. У списку Керування виберіть пункт Надбудови Excel і натисніть кнопку Перейти.

  2. У списку Наявні надбудови виберіть пункт Пакет аналізу й натисніть кнопку OK.

  3. Якщо майстер відображається в списку Неактивні надбудови програм, виберіть майстер і натисніть кнопку Перейти, щоб активувати його.

  4. Якщо необхідно, дотримуйтесь інструкцій програми інсталяції.

Щоб використовувати майстер підсумування, виділіть свій діапазон даних і виберіть команду Умовна сума (на вкладці Формули в групі Рішення). Виконайте кроки 1–4, щоб завершити роботу майстра. На ілюстрації нижче показано крок 2 майстра, у якому є одна умова: значення, які будуть сумуватися, мають бути більше 100.

Діалогове вікно "Опублікувати основну версію"

На початок сторінки

Додавання значень у діапазоні на основі кількох умов за допомогою однієї функції або поєднання функцій

Використовуйте функцію SUMIFS. Укажіть діапазон, який потрібно підсумувати, діапазон, який містить умови, і умови, які застосовуються до діапазону умов. Як варіант, ви можете вкласти функції SUM та IF.

Докладні відомості див. в статті Функція SUMIFS.

Крім того, докладні відомості доступні в таких статтях бази знань:

На початок сторінки

Підсумування значень квадрата й масиву

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

Обчислення суми добутку відповідних значень в одному або кількох масивах

Використовуйте функцію SUMPRODUCT. Наприклад, ви можете обчислити середню оцінку учня, використавши функцію SUMPRODUCT і поділивши цей результат на кількість кредитів, як показано на рисунку нижче.

Дві різні теми презентації

Формула =SUMPRODUCT(A2:A6;B2:B6)/SUM(B2:B6) помножує оцінку для кожного класу на його кількість кредитів, підсумовує ці добутки (61,3), ділить цю суму на загальну кількість кредитів (18) і визначає середню оцінку (3,41). Цей тип обчислення також відомий як обчислення середньозваженого значення.

Ви також можете використовувати функцію SUMPRODUCT у більш розширених математичних операціях, помножуючи кілька масивів.

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

Докладні відомості див. в статті Функція SUMPRODUCT.

Чотири функції, наведені нижче, зазвичай використовуються лише в розширених статистичних або складних математичних розрахунках, тому тут міститься їхній короткий опис. Для отримання докладних відомостей клацніть ім’я функції, щоб перейти до відповідного розділу довідки.

  • Функція SUMSQ  Повертає суму квадратів у списку чисел або значень у діапазоні. Наприклад, SUMSQ(2;3) повертає 13.

  • Функція SUMX2PY2  Додає суму квадратів відповідних значень у двох масивах.

  • Функція SUMX2MY2  Повертає суму різниць квадратів відповідних значень у двох масивах.

  • Функція SUMXMY2  Повертає суму квадратів різниць відповідних значень у двох масивах.

На початок сторінки

Особливі випадки (зростаючий підсумок, унікальні значення)

Ви можете використовувати функції аркуша, щоб створювати підсумки, які автоматично оновлюються, коли ви додаєте більше даних до діапазону чи таблиці, або додавати лише унікальні значення, які містяться в діапазоні чи таблиці.

Створення зростаючого підсумка за допомогою функції

Використовуйте функцію SUM. Наприклад, створіть на аркуші заголовки стовпців Депозити, Зняття коштів і Баланс.

  1. У першій клітинці під стовпцем Баланс введіть свій початковий баланс.

  2. У клітинці безпосередньо під балансом (у нашому випадку це C3) введіть формулу, яка додає значення зі стовпця "Депозити" в цьому ж рядку та віднімає значення зі стовпця "Зняття коштів" (=SUM(C2;A3;-B3).

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

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

    Копіювання прикладу

    • Створіть пустий аркуш або книгу.

    • Виділіть приклад у розділі довідки.

      Примітка : Заголовки рядків і стовпців виділяти не потрібно.

      Виділення прикладу в довідці

      Виділення прикладу в довідці

    • Натисніть клавіші Ctrl+C.

    • На аркуші виділіть клітинку A1 і натисніть клавіші Ctrl+V.

    • Щоб переключатися між режимом перегляду результатів і режимом перегляду формул, що повертають ці результати, натискайте клавіші Control + тупий наголос («`»). Також на вкладці Формули у групі Аудит формули ви можете натиснути кнопку Показати формули.

1

2

3

4

A

B

C

Депозити

Зняття коштів

Баланс

500

1000

625

=SUM(C2;A3;-B3)

1000

740

=SUM(C3;A4;-B4)

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

Докладні відомості див. в статті Обчислення поточного балансу.

На початок сторінки

Додавання унікальних значень у діапазоні за допомогою складеної формули

У стовпці введіть список значень із повторами, а потім використайте поєднання функцій SUM, IF і FREQUENCY, щоб додати лише ті унікальні значення, які містяться в цьому діапазоні.

На початок сторінки

Включення значень з інших аркушів або книг у формулі

Ви можете додати або відняти клітинки чи діапазони даних з інших аркушів або книг у формулі, включивши посилання на них. Щоб зробити посилання на клітинку чи діапазон на іншому аркуші або в іншій книзі, використовуйте вказівки в таблиці нижче.

Об’єкт посилання

Введіть

Приклади

Клітинка або діапазон на іншому аркуші в цій самій книзі

Ім’я аркуша зі знаком оклику, а потім посилання на клітинку або ім’я діапазону.

Аркуш2!B2:B4
Аркуш3!ПоказникиЗбуту

Клітинка або діапазон в іншій книзі, яку зараз відкрито

Ім’я файлу книги в дужках ([]) та ім’я аркуша зі знаком оклику, а потім посилання на клітинку або ім’я діапазону.

[МояКнига.xlsx]Аркуш1!A7

Клітинка або діапазон в іншій книзі, яку не відкрито

Повний шлях та ім’я файлу книги в дужках ([]), ім’я аркуша зі знаком оклику, а потім посилання на клітинку або ім’я діапазону. Якщо повний шлях містить символи пробілів, візьміть початок шляху та кінець імені аркуша в одинарні лапки (див. приклад).

['C:\My Documents\[МояКнига.xlsx]Аркуш1'!A2:A5

На початок сторінки

Підсумування та віднімання значень дати й часу

Ви можете використовувати функції дати й часу та навіть просте додавання або віднімання, щоб обчислювати проміжок часу, оцінювати дати завершення проектів тощо. У таблиці нижче наведено деякі формули, які обчислюють проміжки часу й астрономічні дні. Зверніть увагу: якщо в результаті віднімання дати або проміжку часу виходить від’ємне значення, програма Excel відображає символи ### у клітинці, яка містить формулу.

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

Копіювання прикладу

  • Створіть пустий аркуш або книгу.

  • Виділіть приклад у розділі довідки.

    Примітка : Заголовки рядків і стовпців виділяти не потрібно.

    Виділення прикладу в довідці

    Виділення прикладу в довідці

  • Натисніть клавіші Ctrl+C.

  • На аркуші виділіть клітинку A1 і натисніть клавіші Ctrl+V.

  • Щоб переключатися між режимом перегляду результатів і режимом перегляду формул, що повертають ці результати, натискайте клавіші Control + тупий наголос («`»). Також на вкладці Формули у групі Аудит формули ви можете натиснути кнопку Показати формули.

1

2

3

4

5

A

B

C

Час початку

Час завершення

Формула проміжку часу

Опис (результат)

11:55:24 AM

2:48:13 PM

=B2-A2

Відображає проміжок часу в годинах, хвилинах і секундах. У нашому випадку програма Excel відображає результат (2:52:49 AM) у форматі "AM", тому що різниця менше 12 годин. Якщо б різниця становила 12 годин або більше, програма Excel відобразила б формат "PM".

Дата початку

Дата завершення

Формула астрономічних днів

Опис (результат)

28.05.2008

03.06.2008

=B5-A5

Відображає кількість астрономічних днів як число (6).

Працюючи зі значеннями дати й часу, можна отримати неочікувані результати, тому будьте уважні.

На початок сторінки

Відображення підрахунків у рядку стану програми Excel

Щоб швидко знайти суму значень у виділених на аркуші клітинках, погляньте на рядок стану програми Excel (унизу вікна документа).

Якщо виділити одну або кілька клітинок, відомості про дані в них відобразяться в рядку стану програми Excel. Наприклад, якщо на аркуші виділено чотири клітинки зі значеннями 2 й 3, текстовий рядок (наприклад, "хмара") і значення 4, усі наведені нижче значення можуть відображатися в рядку стану одночасно: середнє значення, кількість, сума, мінімальне й максимальне значення. Клацніть правою кнопкою миші рядок стану, щоб відобразити або приховати всі чи деякі з цих значень. Ці значення показано на знімку екрана нижче.

Рядок стану з обчисленнями та кількостями виділених клітинок

На початок сторінки

Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×