Ви можете підсумувати стовпець одним клацанням миші, функцією AutoSum, функцією SUM, функцією фільтра та шляхом перетворення набору даних у таблицю.
Додавання стовпців або рядків чисел – це те, що більшість із нас має робити досить часто. Наприклад, якщо ви зберігаєте важливі дані, такі як записи про продажі або прайс-листи, в клітинках одного стовпця, ви можете швидко дізнатися загальну суму цього стовпця. Тому необхідно знати, як підсумувати стовпець в Excel.
Існує кілька способів підсумовувати або підсумовувати стовпець/рядок в Excel, включаючи, за допомогою одного клацання, функцію автоматичної суми, функцію SUM, функцію фільтра, функцію SUMIF, а також перетворення набору даних у таблицю. У цій статті ми побачимо різні методи додавання стовпця або рядка в Excel.
Підсумуйте стовпець одним кліком за допомогою рядка стану
Найпростіший і найшвидший спосіб обчислити загальне значення стовпця — клацнути букву стовпця з цифрами та перевірити рядок «Статус» унизу. У нижній частині вікна Excel є рядок стану, який відображає різну інформацію про робочий аркуш Excel, включаючи середнє значення, кількість та суму вибраних комірок.
Припустимо, у вас є таблиця даних, як показано нижче, і ви хочете знайти загальну суму цін у стовпці B.
Все, що вам потрібно зробити, це вибрати весь стовпець із числами, які ви хочете підсумувати (стовпець B), клацнувши літеру B у верхній частині стовпця та подивившись на рядок стану Excel (поруч із елементом керування масштабуванням).
Там ви побачите загальну кількість вибраних клітинок, а також середнє значення та значення підрахунку.
Ви також можете вибрати діапазон даних від B2 до B11 замість цілого стовпця та переглянути рядок стану, щоб дізнатися загальну суму. Ви також можете знайти загальну кількість чисел у рядку, вибравши рядок значень замість стовпця.
Перевага використання цього методу полягає в тому, що він автоматично ігнорує клітинки з текстовими значеннями і лише підсумовує числа. Як ви можете бачити вище, коли ми вибрали весь стовпець B, включаючи клітинку B1 з текстовою назвою (Ціна), це лише підсумовує числа в цьому стовпці.
SUM – стовпець із функцією AutoSum
Інший найшвидший спосіб підсумувати стовпець у Excel – це використовувати функцію автосумування. Автосума — це функція Microsoft Excel, яка дозволяє швидко додавати діапазон комірок (стовпчик або рядок), що містять числа/цілі/десяткові числа, за допомогою функції SUM.
На вкладках «Головна» та «Формула» стрічки Excel є кнопка команди «Автосума», яка при натисканні вставляє «функцію SUM» у вибрану клітинку.
Припустимо, що у вас є таблиця даних, як показано нижче, і ви хочете підсумувати числа в стовпці B. Виберіть порожню клітинку прямо під стовпцем або правий кінець рядка даних (щоб підсумувати рядок), який потрібно підсумувати.
Потім виберіть вкладку «Формула» та натисніть кнопку «Автосума» в групі «Бібліотека функцій».
Або перейдіть на вкладку «Головна» та натисніть кнопку «Автосума» в групі «Редагування».
У будь-якому випадку, як тільки ви натиснете кнопку, Excel автоматично вставить «=SUM()» у вибрану клітинку та виділить діапазон вашими числами (мурахи марширують по діапазону). Перевірте, чи вибраний діапазон правильний, і якщо це неправильний діапазон, ви можете змінити його, вибравши інший діапазон. І відповідно до цього параметри функції будуть автоматично налаштовуватися.
Потім просто натисніть Enter на клавіатурі, щоб побачити суму всього стовпця у вибраній клітинці.
Ви також можете викликати функцію AutoSum за допомогою комбінації клавіш.
Для цього виберіть клітинку, яка знаходиться трохи нижче останньої клітинки в стовпці, для якої потрібно загальну суму, і скористайтеся наведеним нижче ярликом:
Alt+= (Натисніть і утримуйте клавішу Alt і натисніть клавішу рівності =І він автоматично вставить функцію SUM і вибере діапазон для неї. Потім натисніть Enter, щоб підсумувати стовпець.
AutoSum дозволяє швидко підсумовувати стовпець або рядок одним клацанням миші або натисканням комбінації клавіш.
Однак функція AutoSum має певні обмеження: вона не виявить і не вибере правильний діапазон, якщо в діапазоні є будь-які порожні клітинки або будь-яка клітинка, яка має текстове значення.
Як ви можете бачити у наведеному вище прикладі, клітинка B6 порожня. І коли ми ввели функцію AutoSum в клітинку B12, вона вибирає лише 5 клітинок вище. Це тому, що функція сприймає, що клітинка B7 є кінцем даних, і повертає лише 5 клітинок загалом.
Щоб виправити це, вам потрібно змінити діапазон, клацнувши та перетягнувши мишею, або введіть правильні посилання на клітинки вручну, щоб виділити весь стовпець, і натисніть Enter. І ви отримаєте правильний результат.
Щоб уникнути цього, ви також можете ввести функцію SUM вручну для обчислення суми.
SUM стовпець, ввівши функцію SUM вручну
Хоча команда AutoSum швидка та проста у використанні, іноді вам може знадобитися ввести функцію SUM вручну, щоб обчислити суму стовпця або рядка в Excel. Особливо, якщо ви хочете додати лише деякі клітинки у вашому стовпці або якщо ваш стовпець містить будь-які порожні клітинки чи клітинки з текстовим значенням.
Крім того, якщо ви хочете показати значення суми в будь-якій з клітинок на аркуші, крім комірки під стовпцем або клітинки після рядка чисел, ви можете використовувати функцію SUM. За допомогою функції SUM ви можете обчислити суму чи загальну кількість комірок у будь-якому місці аркуша.
Синтаксис функції SUM:
=SUM(число1, [число2],...).номер 1(обов’язково) – перше числове значення, яке потрібно додати.номер 2(необов’язково) – це друге додаткове числове значення, яке потрібно додати.
Хоча число 1 є обов’язковим аргументом, ви можете підсумувати максимум 255 додаткових аргументів. Аргументами можуть бути числа, які потрібно додати, або посилання на клітинки до чисел.
Ще одна перевага використання функції SUM вручну полягає в тому, що ви додаєте числа в несуміжні клітинки стовпця або рядка, а також у кілька стовпців або рядків. Ось як ви використовуєте функцію SUM вручну:
Спочатку виділіть клітинку, де ви хочете побачити загальну суму стовпця або рядка в будь-якому місці на аркуші. Далі почніть свою формулу, ввівши =SUM( в камері.
Потім виберіть діапазон комірок із числами, які потрібно підсумувати, або введіть посилання на клітинки для діапазону, який потрібно підсумувати у формулі.
Ви можете клацнути та перетягнути мишею або утримувати клавішу Shift, а потім використовувати клавіші зі стрілками, щоб вибрати діапазон комірок. Якщо ви хочете ввести посилання на клітинку вручну, введіть посилання на клітинку першої клітинки діапазону, потім двокрапку, а потім посилання на клітинку останньої клітинки діапазону.
Після введення аргументів закрийте дужку та натисніть клавішу Enter, щоб отримати результат.
Як бачите, навіть якщо стовпець має порожню клітинку та текстове значення, функція дає вам суму всіх виділених комірок.
Підведення небезперервних комірок у стовпці
Замість підсумовування діапазону безперервних комірок ви також можете підсумовувати неперервні клітинки в стовпці. Щоб виділити несуміжні клітинки, утримуйте клавішу Ctrl і клацніть клітинки, які потрібно додати, або введіть посилання на клітинки вручну та розділіть їх за допомогою comms (,) у формулі.
Це відобразить суму лише виділених клітинок у стовпці.
Підсумування кількох стовпців
Якщо вам потрібна сума кількох стовпців, виділіть кілька стовпців за допомогою миші або введіть посилання на клітинку першого в діапазоні, потім двокрапку, а потім останнє посилання на клітинку діапазону для аргументів функції.
Після введення аргументів закрийте дужку та натисніть клавішу Enter, щоб побачити результат.
Підсумовування несуміжних стовпців
Ви також можете підсумовувати несуміжні стовпці за допомогою функції SUM. Ось як:
Виберіть будь-яку клітинку на аркуші, де потрібно відобразити загальну кількість несуміжних стовпців. Потім почніть формулу, ввівши функцію =SUM( в тій камері. Далі виберіть діапазон першого стовпця за допомогою миші або введіть посилання на діапазон вручну.
Потім додайте кому та виберіть наступний діапазон або введіть друге посилання на діапазон. Таким чином ви можете додати скільки завгодно діапазонів і відокремити кожен з них комою (,).
Після аргументів закрийте дужку та натисніть Enter, щоб отримати результат.
Підсумування стовпця за допомогою іменованого діапазону
Якщо у вас є великий аркуш даних і ви хочете швидко обчислити загальну кількість чисел у стовпці, ви можете використовувати іменовані діапазони у функції SUM, щоб знайти загальну суму. Коли ви створюєте іменовані діапазони, ви можете використовувати ці імена замість посилань на клітинки, що полегшує посилання на набори даних у Excel. У функції легко використовувати іменований діапазон замість прокручування сотень рядків вниз, щоб вибрати діапазон.
Ще одна гарна річ у використанні іменованого діапазону полягає в тому, що ви можете посилатися на набір даних (діапазон) в іншому аркуші в аргументі SUM і отримати значення суми в поточному аркуші.
Щоб використати іменований діапазон у формулі, спочатку його потрібно створити. Ось як ви створюєте та використовуєте іменований діапазон у функції SUM.
Спочатку виділіть діапазон комірок (без заголовків), для якого потрібно створити іменований діапазон. Потім перейдіть на вкладку «Формули» та натисніть кнопку «Визначити назву» в групі «Визначені імена».
У діалоговому вікні «Нове ім’я» вкажіть ім’я, яке потрібно дати вибраному діапазону, у полі «Ім’я:». У полі «Обсяг:» ви можете змінити область дії названого діапазону як усієї книги або окремого робочого аркуша. Область дії визначає, чи буде названий діапазон доступним для всієї книги чи лише для певного аркуша. Потім натисніть кнопку «ОК».
Ви також можете змінити посилання на діапазон у полі «Посилається на».
Крім того, ви також можете назвати діапазон, використовуючи поле «Назва». Для цього виберіть діапазон, перейдіть до поля «Назва» ліворуч від рядка формул (трохи над літерою A) і введіть назву, яку ви хочете призначити вибраному діапазону дат. Потім натисніть Enter.
Але коли ви створюєте іменований діапазон за допомогою поля Ім’я, воно автоматично встановлює область іменованого діапазону для всієї книги.
Тепер ви можете використовувати створений вами названий діапазон, щоб швидко знайти значення суми.
Для цього виділіть будь-яку порожню клітинку в книзі, де потрібно відобразити результат Суми. І введіть формулу SUM з названим діапазоном як її аргументи та натисніть Enter:
=SUM(ціни)
У наведеному вище прикладі формула на аркуші 4 посилається на стовпець «Ціни» на аркуші 2, щоб отримати суму стовпця.
Підсумуйте лише видимі клітинки в стовпці за допомогою функції SUBTOTAL
Якщо ви маєте відфільтровані клітинки або приховані клітинки в наборі даних або стовпці, використання функції SUM для підсумовування стовпця не ідеально. Оскільки функція SUM включає в свій обчислення відфільтровані або приховані клітинки.
Наведений нижче приклад показує, що відбувається, коли ви підсумовуєте стовпець із прихованими або відфільтрованими рядками:
У наведеній вище таблиці ми відфільтрували стовпець B за цінами, меншими за 100. В результаті ми отримали кілька відфільтрованих рядків. Ви можете помітити, що в таблиці є відфільтровані/приховані рядки за відсутніми номерами рядків.
Тепер, коли ви підсумовуєте видимі клітинки в стовпці B за допомогою функції SUM, ви повинні отримати «207» як значення суми, але замість цього відображається «964». Це тому, що функція SUM також враховує відфільтровані клітинки під час обчислення суми.
Ось чому ви не можете використовувати функцію SUM, якщо задіяні фільтровані або приховані клітинки.
Якщо ви не хочете, щоб відфільтровані/приховані клітинки включалися в обчислення під час підсумовування стовпця, і ви хочете лише підсумувати видимі клітинки, вам потрібно використовувати функцію SUBTOTAL.
Проміжний підсумок Функція
SUBTOTAL — це потужна вбудована функція в Excel, яка дозволяє виконувати різноманітні обчислення (СУМА, СЕРЕДНЄ, COUNT, MIN, VARIANCE та інші) для діапазону даних і повертає загальний або зведений результат стовпця. Ця функція лише підсумовує дані у видимих клітинках, ігноруючи відфільтровані чи приховані рядки. SUBTOTAL – це універсальна функція, яка може виконувати 11 різних функцій у видимих клітинках стовпця.
Синтаксис функції SUBTOTAL:
=ПІДГОТОВ (номер функції, посилання 1, [посилання2], ...)Аргументи:
номер_функції(вимагається)– Це номер функції, який визначає, яку функцію використовувати для обчислення загальної суми. Цей аргумент може приймати будь-яке значення від 1 до 11 або від 101 до 111. Тут нам потрібно підсумувати видимі клітинки, ігноруючи відфільтровані клітинки. Для цього нам потрібно використовувати «9».ref1(вимагається)– Перший іменований діапазон або посилання, які потрібно підвести.ref2(необов’язково) – Другий іменований діапазон або посилання, які потрібно підвести. Після першого посилання ви можете додати до 254 додаткових посилань.
Підсумування стовпця за допомогою функції SUBTOTAL
Якщо ви хочете підсумувати видимі клітинки та виключити відфільтровані чи приховані клітинки, виконайте такі дії, щоб використовувати функцію SUBTOTAL для підсумування стовпця:
Спочатку потрібно відфільтрувати таблицю. Для цього клацніть будь-яку клітинку у вашому наборі даних. Потім перейдіть на вкладку «Дані» та натисніть значок «Фільтр» (значок воронки).
Стрілки з’являться поруч із заголовками стовпців. Натисніть стрілку біля заголовка стовпця, за допомогою якого потрібно відфільтрувати таблицю. Потім виберіть параметр фільтра, який потрібно застосувати до своїх даних. У наведеному нижче прикладі ми хочемо відфільтрувати стовпець B з числами менше 100.
У діалоговому вікні «Спеціальний автофільтр» ми вводимо «100» і натискаємо «ОК».
Числа в стовпці фільтруються за значеннями менше 100.
Тепер виберіть клітинку, в якій потрібно відобразити значення суми, і почніть вводити функцію ПРОМІЖНИЙ ПІДСУМОК. Після того, як ви відкриєте функцію SUBTOTAL і введете дужку, ви побачите список функцій, які можна використовувати у формулі. Натисніть «9 – SUM» у списку або введіть «9» вручну як перший аргумент.
Потім виберіть діапазон комірок, який потрібно підсумувати, або введіть посилання на діапазон вручну та закрийте дужку. Потім натисніть Enter.
Тепер ви отримаєте суму (проміжну суму) лише видимих клітинок – «207»
Крім того, ви також можете вибрати діапазон (B2:B11) з числами, які потрібно додати, і натиснути «Автосума» на вкладці «Головна» або «Формули».
Він автоматично додасть функцію SUBTOTAL в кінець таблиці та підсумує результат.
Перетворіть свої дані в таблицю Excel, щоб отримати суму стовпця
Ще один простий спосіб, за допомогою якого ви можете підсумувати свій стовпець, — перетворити дані електронної таблиці в таблицю Excel. Перетворюючи дані в таблицю, ви можете не тільки підсумовувати свій стовпець, але й виконувати багато інших функцій або операцій зі своїм списком.
Якщо ваші дані ще не у форматі таблиці, вам потрібно перетворити їх у таблицю Excel. Ось як ви конвертуєте свої дані в таблицю Excel:
Спочатку виберіть будь-яку клітинку в наборі даних, яку потрібно перетворити на таблицю Excel. Потім перейдіть на вкладку «Вставка» та натисніть значок «Таблиця».
Або ви можете натиснути комбінацію клавіш Ctrl+T, щоб перетворити діапазон комірок у таблицю Excel.
У діалоговому вікні «Створення таблиці» підтвердьте діапазон і натисніть «ОК». Якщо у вашій таблиці є заголовки, залиште прапорець опцію «Моя таблиця має заголовки».
Це перетворить ваш набір даних у таблицю Excel.
Коли таблиця буде готова, виберіть будь-яку клітинку в таблиці. Потім перейдіть до вкладки «Дизайн», яка з’являється лише тоді, коли ви виберете клітинку в таблиці, і поставте прапорець «Усього рядків» у групі «Параметри стилю таблиці».
Після того, як ви позначите параметр «Усього рядків», новий рядок одразу з’явиться в кінці таблиці зі значеннями в кінці кожного стовпця (як показано нижче).
І коли ви клацнете клітинку в цьому новому рядку, ви побачите спадне меню поруч із цією клітинкою, з якої можна застосувати функцію, щоб отримати підсумок. Виберіть клітинку в останньому рядку (новому рядку) стовпця, який потрібно підсумувати, клацніть спадне меню поруч із нею та переконайтеся, що зі списку вибрано функцію «SUM».
Ви також можете змінити функцію на Average, Count, Min, Max та інші, щоб побачити їх відповідні значення в новому рядку.
Підсумуйте стовпець на основі критеріїв
Усі попередні методи показали вам, як обчислити підсумок для всього стовпця. Але що, якщо ви хочете підсумувати лише конкретні клітинки, які відповідають критеріям, а не всі клітинки. Тоді вам доведеться використовувати функцію SUMIF замість функції SUM.
Функція SUMIF шукає конкретну умову в діапазоні комірок (стовпці), а потім підсумовує значення, які відповідають заданій умові (або значення, що відповідають клітинкам, які відповідають умові). Ви можете підсумовувати значення на основі умов числа, умов тексту, умови дати, символів підстановки, а також на основі порожніх і непорожніх клітинок.
Синтаксис функції SUMIF:
=SUMIF(діапазон, критерії, [діапазон_суми])Аргументи/параметри:
діапазон– Діапазон клітинок, у якому ми шукаємо клітинки, які відповідають критеріям.критерії– Критерії, які визначають, які клітинки потрібно підсумувати. Критерієм може бути число, текстовий рядок, дата, посилання на клітинку, вираз, логічний оператор, символ підстановки, а також інші функції.діапазон_сум(необов'язково) – Це діапазон даних зі значеннями, які підсумовуються, якщо відповідний запис діапазону відповідає умові. Якщо цей аргумент не вказано, натомість підсумовується «діапазон».
Припустимо, у вас є набір даних нижче, який містить дані про продажі кожного представника з різних регіонів, і ви хочете лише підсумувати суму продажів з регіону «Південний».
Ви можете легко зробити це за допомогою такої формули:
=SUMIF(B2:B19,"Південь",C2:C19)Виберіть клітинку, в якій потрібно відобразити результат, і введіть цю формулу. Наведена вище формула SUMIF шукає значення «Південь» у стовпці B2:B19 і додає відповідну суму продажів у стовпці C2:C19. Потім відображається результат у клітинці E7.
Ви також можете посилатися на клітинку, яка містить текстову умову, замість того, щоб безпосередньо використовувати текст у аргументі критерію:
=СУМКА(B2:B19,E6,C2:C19)
Це воно.