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

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

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

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

Якщо поставити формулу суми у G2

то отримаємо загальну виручку за першим відділом.

Зверніть увагу, що наявність тексту, а не числа, в осередку B1 ніяк не позначилося на підрахунках. Складаються лише числові значення, а символьні ігноруються.

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

Все сказане вище повною мірою стосується і роботи з рядками.

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

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

Важливо навчитися правильно вказати діапазон даних. Ось як це зробити, якщо підсумувати продажі за 1-й квартал:

Формула розрахунку виглядає так:

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

У формулі послідовно перераховуємо кілька діапазонів:

Звичайно, їх може бути не два, а набагато більше: до 255 штук.

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

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

Інакше кажучи, треба, рухаючись вниз, взяти кожну третю сходинку.

Це можна зробити двома способами.

Перший – найпростіший, "в лоб". Складаємо всі цифри необхідного відділу нормальної математичної операцією складання. Виглядає просто, але уявіть, якщо у вас статистика, припустимо, за 3 роки? Прийде обробити 36 чисел.

Другий спосіб – для більш «просунутих», але універсальний. Використовуємо функцію СУМ.

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

Як це працює? Нам потрібна 1-ша, 3-я, 6-я і т.д. позиції. За допомогою функції РЯДКУ() ми обчислюємо номер поточної позиції. І якщо залишок від розподілу на 3 дорівнюватиме нулю, то значення буде враховано в розрахунку. Інакше – ні.

Для такого лічильника ми будемо використовувати номери рядків. Але наше перше число знаходиться у другому рядку робочого аркуша Ексель.Оскільки треба починати з першої позиції і потім брати кожну третю, а починається діапазон з 2-го рядка, то до порядкового номеру її додаємо 1. Тоді у нас лічильник почне рахувати з цифри 3. Для цього і служить вираз РЯДОК(C2:C16)+1. Отримаємо 2+1=3, залишок від розподілу на 3 дорівнює нулю. Так ми візьмемо 1, 3, 6 і т.д. позиції.

Формула масиву означає, що Excel повинен послідовно перебрати всі комірки діапазону – починаючи з C2 до C16, і з кожною з них зробити описані вище операції.

Коли знаходимо продажі по Відділу 2, то змінимо вираз:

Нічого не додаємо, оскільки перше відповідне значення таки знаходиться в 3-й позиції.

Аналогічно для Відділу 3

Замість додавання 1 тепер віднімаємо 1, щоб відлік знову почався з 3. Тепер будемо брати кожну третю позицію, починаючи з 4-ї.

Ну і, звичайно, не забуваймо натискати CTRL+SHIFT+ENTER.

Примітка. Так само можна знайти суму кожного N-го стовпця в таблиці. Тільки замість функції РЯДКУ() потрібно буде використовувати СТІЛК().

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

У таблиці Excel записано щоденний виторг магазину за тривалий період часу. Необхідно розрахувати щотижневу виручку за кожну сімденку.

Використовуємо те, що СУММ() може складати значення у діапазоні даних, а й у масиві. Такий масив значень їй може надати функція ЗМІЩ.

Нагадаємо, що тут потрібно зазначити кілька аргументів:

1. Початкову точку. Зверніть увагу, що С2 ми запровадили як абсолютне посилання.

2. Скільки кроків вниз зробити

3. Скільки кроків праворуч зробити. Після цього потрапляємо до початкової (лівої верхньої) точки масиву.

4. Скільки значень взяти, знову рухаючись униз.

5.Скільки колонок буде у масиві. Потрапляємо до кінцевої (правої нижньої) точки масиву значень.

Отже, формула для 1-го тижня:

В даному випадку РЯДОК() – це як би наш лічильник тижнів. Відлік потрібно починати з 0, щоб почати дії прямо з комірки C2, нікуди вниз не переміщаючись. Для цього використовуємо РЯДОК()-2. Оскільки сама формула знаходиться в осередку F2, отримуємо в результаті 0. Початком відліку буде С2, а кінець його – на 5 значень нижче у тій самій колонці.

СУМ просто складе запропоновані їй п'ять значень.

Для 2-го тижня у F3 формулу просто копіюємо. РЯДКУ()-2 дасть тут результат 1, тому початок масиву буде 1 * 5 = 5, тобто на 5 значень вниз в комірці C7 і до С11. І так далі.

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

Завдання: Підсумувати 3 максимальні або 3 мінімальні значення.

Функція НАЙБІЛЬШИЙ повертає найбільше значення з переліку даних. Хитрість у цьому, що другий її аргумент показує, яке саме значення треба відновити: 1- найбільше, 2 – друге за величиною тощо. А якщо вказати – значить, потрібні три найбільші. Але при цьому не забувайте застосовувати формулу масиву та завершувати комбінацією клавіш CTRL+SHIFT+ENTER.

Аналогічно справи і з найменшими значеннями:

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

Щоб підрахувати цифри з однакової форми діапазону на декількох аркушах, можна записувати координати даних спеціальним синтаксисом, званим «3d-посилання».

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

Подивіться на цьому невеликому відео, як застосовуються 3-D формули.

Як бачите, у нас є 4 однакові таблиці. Стандартний метод знаходження виручки протягом місяця –

Як аргументи перераховуємо діапазони даних.

Ми вказуємо програмі, що необхідно зробити обчислення з осередками B2:B8, що знаходяться на аркушах тиждень1, тиждень2, тиждень3, тиждень4. Тут номер листа послідовно зростає на 1.

Важливе зауваження. Ви можете користуватися 3D-посиланнями та в інших випадках. Наприклад, формула СРЗНАЧ

знайде середнє значення одноденної виручки протягом місяця.

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

Є відомості про продаж товарів, для кожного з них виділено окрему колонку. Необхідно розрахувати продаж за обраним товаром. Інакше кажучи, нам в Екселі потрібно розрахувати суму стовпця, але спочатку потрібно лише знайти цей потрібний нам стовпець серед інших аналогічних.

Для пошуку та вибору конкретного товару використовуємо дуже популярну комбінацію функцій ІНДЕКС+ПОШУКПОЗ.

Розрахунок суми в G3 виконаємо так:

Отже, комбінація ІНДЕКС+ПОШУКПОЗ повинна повернути для подальших розрахунків набір чисел у вигляді вертикального масиву, який потім буде підсумовано.

Опишемо це докладніше.

Функція ПОШУКПОЗ знаходить у шапці найменувань таблиці B1:D1 потрібний продукт (банани) і повертає його порядковий номер (іншими словами, 2).

Потім ІНДЕКС вибирає із масиву значень B2:D21 відповідний номер стовпця (другий). Повернеться весь стовпчик даних з відповідним номером, оскільки номер рядка (перший параметр функції) вказаний рівним 0. На малюнку це буде С2:С21. Залишається тільки підрахувати всі значення у цій колонці.

В даному випадку, щоб уникнути помилок при записі назви товару, ми рекомендували б використовувати список, що випадає в F3, а значення для наповнення його брати з B1:D1.

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

Як у Екселе порахувати суму стовпця, якщо таких стовпців кілька, та й самі вони перебувають у різних таблицях?

Для отримання підсумків відразу за кількома таблицями також використовуємо функцію СУМ та структуровані посилання. Такі посилання з'являються під час створення в Excel «розумної» таблиці.

При створенні її Excel призначає ім'я самої таблиці та кожному заголовку стовпця у ній. Ці імена можна використовувати у виразах: вони можуть відображатися у вигляді підказок у рядку введення.

У нашому випадку це виглядає так:

Для створення «розумної» таблиці виділимо діапазон A1: B21 та на стрічці «Головна» вибираємо «Форматувати як таблицю».

Приємним бонусом тут є те, що «розумна» таблиця сама змінює свої розміри при додаванні до неї даних (або їх видаленні), посилання на неї коригувати не потрібно.

Також у нашому випадку не важливо, де саме розміщуються у вашому файлі Excel ці дані. Навіть не важливо, що вони знаходяться на різних аркушах – програма все одно знайде їх на ім'я.

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

І якщо буде внесено якісь зміни або додано цифри, то все перерахується автоматично.

Примітка: підсумковий рядок у таблиці має бути включений. Якщо ви відключите її, вираз поверне помилку #ПОСИЛКА.

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

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

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

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

Сподіваємося, що тепер знайти суму стовпця або окремих осередків вам буде набагато простіше.

8 простих способів як порахувати в Excel суму стовпця - Kompas.v.ua

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

Відео: Розрахунок відсотка

Отримати відсоток від загального

Приклад 1: Отримати відсоток заданого результату

Наприклад, у вас є деякі значення в стовпці B (B2: B4), а загальна кількість цих значень знаходиться в осередку B5, як показано нижче:

Тепер ви хочете отримати відсоток кожного значення від загальної суми, зробіть таке:

Крок 1: Використовуйте формулу, щоб отримати відсоток кожного значення від загального

1. Виберіть пусту комірку, тут C2, введіть формулу нижче, потім натисніть Enter ключ, щоб отримати перший відсоток:

Увага : щоб зробити знаменник абсолютним посиланням, або введіть знак долара ($) вручну, або клацніть посилання на комірку в рядку формул і натисніть F4 .

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

Крок 2: Відформатуйте результат у відсотках

Виберіть комірки результатів, потім натисніть Головна вкладка та перейдіть до Номер реєстрації група, виберіть Відсоток Стиль . (Або ви можете використовувати ярлики Shift + Ctrl + % для форматування осередків у відсотках.)

Тепер результати відображаються у процентному форматі.

Увага : Ви можете змінити десяткове число, натиснувши Збільшити десяткове число. або Зменшити десяткове число у групі «Номер» на вкладці «Головна».

Приклад 2: Отримати відсоток від невідомого результату

Наприклад, є таблиця, яка містить бали студентів. Тепер ви хочете отримати відсоток набраних очок 1 від кожної суми:

Крок 1: Використовуйте формулу, щоб отримати відсоток кожного значення від загального

1. Виберіть пусту комірку, тут D8, введіть формулу нижче, потім натисніть Enter ключ, щоб отримати перший відсоток:

Увага : Функція використовується для отримання суми чисел. SUM(B8:C8) отримує загальну кількість балів у Лізі.

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

Крок 2: Відформатуйте результат у відсотках

Виберіть комірки результатів, потім натисніть Головна вкладка та перейдіть до Номер реєстрації група, виберіть Відсоток Стиль . (Або ви можете використовувати ярлики Shift + Ctrl + % для форматування осередків у відсотках.)

Тепер результати відображаються у процентному форматі.

Увага : Ви можете змінити десяткове число, натиснувши Збільшити десяткове число.або Зменшити десяткове число у групі «Номер» на вкладці «Головна».

Отримати відсоток зміни між двома числами

Припустимо, дохід компанії A у 2022 році становить $3,000,000 2021 2,680,000, а дохід компанії A у 2022 році становить $2021 2021 XNUMX, який відсоток зміни між цими двома роками? Ви можете розрахувати різницю, віднімаючи новий дохід (XNUMX) з початкового доходу (XNUMX), а потім розділивши результат на початковий дохід (XNUMX).

Крок 1: Використовуйте формулу, щоб отримати відсоток кожного значення від загального

Виберіть пусту комірку, тут C9, введіть формулу нижче, потім натисніть Enter ключ, щоб отримати відсоток зміни:

Увага : Операція в дужках обчислюватиметься першою Осередок A9 містить стару ціну (2021 р.), осередок B9 містить більш нову ціну (2022 р.).

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

Крок 2: Відформатуйте результат у відсотках

Виберіть комірку результату, потім натисніть Головна вкладка та перейдіть до Номер реєстрації група, виберіть Відсоток Стиль . (Або ви можете використовувати ярлики Shift + Ctrl + % для форматування осередків у відсотках.)

Тепер результат відображається у процентному форматі.

  1. Якщо результат різниці позитивний, це, що останнє значення збільшилося проти першим. Якщо результат негативний, це, що останнє значення зменшилося проти першим.
  2. Ви можете змінити десяткове число, натиснувши Збільшити десяткове число або Зменшити десяткове число у групі «Номер» на вкладці «Головна».
  3. У формулі, якщо дільник (старше значення) дорівнює нулю або порожній, формула поверне # СПРАВ/0! значення помилки. Щоб уникнути цього, ви можете використати формулу:

Збільшення чи зменшення числа на відсоток

Приклад 1: Збільшення числа на відсоток

Припустимо, що торік ваша річна зарплата становила 320 000 20 доларів, а цього року ваша компанія вирішила збільшити вашу річну зарплату на XNUMX%. Скільки ви заробите цього року?

Виберіть пусту комірку, тут C17, введіть формулу нижче, потім натисніть Enter ключ:

Увага : Операція у дужках буде розрахована першою Комірка A17 містить вихідне число, комірка B17 містить відсоток збільшення.

Приклад 2: Зменшення числа на відсоток

Припустимо, що менеджер хоче зменшити витрати на рекламу у розмірі 10,000 25 доларів США на XNUMX% наступного місяця. Якою буде нова щомісячна вартість реклами?

Виберіть пусту комірку, тут C21, введіть формулу нижче, потім натисніть Enter ключ:

Увага : Операції у дужках будуть розраховані першими Комірка A21 містить вихідне число, комірка B21 містить відсоток зменшення.

Отримати підсумок за заданою сумою та відсотком

Припустимо, що продажна ціна ноутбука становить 120 доларів, що на 20% нижче за початкову ціну. Питання, яка первісна ціна цього ноутбука?

Виберіть пусту комірку, тут G9, введіть формулу нижче, потім натисніть Enter ключ, щоб отримати загальне число:

Увага : Операції у дужках будуть розраховані першими Осередок E9 містить ціну продажу, осередок F9 містить відсоток знижки.

Отримати суму за заданою сумою та відсотком

Якщо початкова ціна пальта складає 110 доларів, але вам необхідно заплатити додатково 12% податку з продажу, яку суму податку з продажу вам необхідно заплатити?

Виберіть пусту комірку, тут C13, введіть формулу нижче, потім натисніть Enter ключ:

8 простих способів як порахувати в Excel суму стовпця - Kompas.v.ua

У різних видах діяльності необхідно вміння рахувати відсотки. Розуміти, як вони «виходять». Торгові надбавки, ПДВ, знижки, дохідність вкладів, цінних паперів і навіть чайові – все це обчислюється у вигляді частини від цілого.

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

Робота з відсотками в Excel

Порахувати відсоток від числа, додати, відібрати відсотки на сучасному калькуляторі не важко. Головна умова – на клавіатурі має бути відповідний значок (%). А далі – справа техніки та уважності.

Наприклад, 25+5%. Щоб знайти значення виразу, потрібно набрати на калькуляторі цю послідовність цифр та знаків. Результат – 26,25. Великого розуму з такою технікою не потрібне.

Для складання формул в Excel пригадаємо шкільні ази:

Відсоток – сота частина цілого.

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

приклад. Привезли 30 одиниць товару. Першого дня продали 5 одиниць. Скільки відсотків товару продали?

5 – це частина. 30 – ціле. Підставляємо дані у формулу:

Щоб додати відсоток до Excel (25 + 5%), потрібно спочатку знайти 5% від 25. У школі становили пропорцію:

Після цього можна виконувати додавання.

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

Як порахувати відсоток від числа в Excel

Є кілька способів.

Адаптуємо до програми математичну формулу: (частина/ціле) * 100.

Подивіться уважно на рядок формул і результат. Підсумок вийшов правильний. Але ми не множили на 100 . Чому?

У програмі Excel змінюється формат осередків. Для С1 ми призначили "Процентний" формат. Він має на увазі множення значення на 100 і виведення на екран зі знаком %. За потреби можна встановити певну кількість цифр після коми.

Тепер обчислимо, скільки буде 5% від 25. Для цього вводимо в комірку формулу розрахунку: = (25 * 5) / 100. Результат:

Або: = (25/100) * 5. Результат буде той самий.

Розв'яжемо приклад іншим способом, задіявши знак % на клавіатурі:

Застосуємо отримані знання практично.

Відома вартість товару та ставка ПДВ (18%). Потрібно вирахувати суму ПДВ.

Помножимо вартість товару на 18%. "Розмножимо" формулу на весь стовпець. Для цього чіпляємо мишею правий нижній кут комірки і тягнемо вниз.

Відома сума ПДВ, ставка. Знайдемо вартість товару.

Формула розрахунку: = (B1 * 100) / 18. Результат:

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

Формула розрахунку залишається незмінною: частина / ціле * 100. Тільки в даному прикладі посилання на комірку в знаменнику дробу ми зробимо абсолютною. Використовуємо знак $ перед ім'ям рядка та ім'ям стовпця: $$7.

Як додати відсоток до числа

Завдання вирішується на дві дії:

  1. Знаходимо скільки становить відсоток від числа. Тут ми вирахували скільки буде 5% від 25.
  2. Додамо результат до числа.Приклад для ознайомлення: 25+5%.

А тут ми виконали власне додавання. Опустимо проміжну дію. Вихідні дані:

Ставка ПДВ – 18%. Нам потрібно знайти суму ПДВ та додати її до ціни товару. Формула: ціна + (ціна * 18%).

Не забуваємо про дужки! З їхньою допомогою встановлюємо порядок розрахунку.

Щоб відібрати відсоток від числа в Excel, слід виконати такий самий порядок дій. Тільки замість додавання виконуємо віднімання.

Як порахувати різницю у відсотках у Excel?

Наскільки змінилося значення між двома величинами у відсотках.

Спочатку абстрагуємося від Excel. Місяць тому до магазину привозили столи за ціною 100 карбованців за одиницю. Сьогодні закупівельна ціна – 150 рублів.

Різниця у відсотках = (нові дані – старі дані) / старі дані * 100%.

У прикладі закупівельна вартість одиниці товару збільшилася на 50%.

Порахуємо різницю у відсотках між даними у двох стовпцях:

Не забуваймо виставляти «Відсотковий» формат осередків.

Розрахуємо відсоткову зміну між рядками:

Формула така: (наступне значення – попереднє значення) / попереднє значення.

При такому розташуванні даних перший рядок пропускаємо!

Якщо потрібно порівняти дані за всі місяці зі січнем, наприклад, використовуємо абсолютне посилання на комірку з потрібним значенням (символ $).

Як зробити діаграму з відсотками

Перший варіант: зробити стовпець у таблиці з даними. Потім використовувати ці дані для побудови діаграми. Виділяємо комірки з відсотками та копіюємо – натискаємо «Вставка» – вибираємо тип діаграми – ОК.

Другий варіант: встановити формат підписів даних у вигляді частки. У травні – 22 робочі зміни. Потрібно порахувати у відсотках: скільки відпрацював кожен робітник. Складаємо таблицю, де перший стовпець – кількість робочих днів, другий – кількість вихідних.

Робимо кругову діаграму.Виділяємо дані у двох шпальтах – копіюємо – «Вставка» – діаграма – тип – ОК. Потім вставляємо дані. Клацаємо по них правою кнопкою миші – "Формат підписів даних".

Вибираємо "Долі". На вкладці "Число" – процентний формат. Виходить так:

Ми на цьому зупинимося. А Ви можете редагувати на свій смак: змінити колір, вигляд діаграми, зробити підкреслення тощо.

  • Створити таблицю
  • Форматування
  • Функції Excel
  • Формули та діапазони
  • Фільтр та сортування
  • Діаграми та графіки
  • Зведені таблиці
  • Друк документів
  • Бази даних та XML
  • Можливості Excel
  • Налаштування параметри
  • Уроки Excel
  • Макроси VBA
  • Завантажити приклади