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

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

Що таке відсоток

Майже кожен з нас розуміє, що таке відсотки і як їх нараховувати. Давайте повторимо це. Уявімо, що на склад надійшло 100 одиниць певного товару. Тут одна одиниця в даному випадку дорівнює одному відсотку. Якщо було імпортовано 200 одиниць товару, то один відсоток дорівнював би двом одиницям і так далі. Щоб отримати один відсоток, потрібно вихідну цифру розділити на сто. Ось де ви можете втекти від цього зараз.

Розрахунок відсотків від суми в Excel

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

Ви дізнаєтеся, як швидко визначити відсоток від суми значень за допомогою Excel.

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

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

= B2 / $ B $ 10

Розберемо цю формулу докладніше. Комірка B2 у цьому прикладі зміниться під час автоматичного заповнення. Тому його адреса має бути відносною. Але адреса комірки B10 повністю абсолютна. Це означає, що як адреса рядка, так і адреса стовпця не змінюються, коли ви перетягуєте формулу в інші клітинки.

Щоб перетворити посилання в абсолютне, необхідно натиснути F4 необхідну кількість разів або поставити знак долара зліва від адреси рядка та/або стовпця. 

У нашому випадку нам потрібно поставити два знаки долара, як показано в прикладі вище.

Ось зображення результату.

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

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

=SUMIF(діапазон, критерії, діапазон_суми)/загальна сума

Тут колонка А містить назви товарів, які разом утворюють асортимент. Стовпець B описує інформацію про діапазон підсумовування, який є загальною кількістю поставлених товарів. Умова записана в Е1, це назва товару, на яку орієнтується програма при визначенні відсотка.

Загалом формула виглядатиме так (враховуючи, що загальна сума буде визначена в комірці B10).

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

=СУММЕСЛИ(A2:A9;»cherries»;B2:B9)/$B$10

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

1. Кожен предмет поєднується між собою.
2. Потім отриманий результат ділиться на загальне значення.

Отже, формула, яка визначає результат для вишень і яблук, буде наступною:

=(СУММЕСЛИ(A2:A9;»cherries»;B2:B9)+СУММЕСЛИ(A2:A9;»apples»;B2:B9))/$B$10

Як обчислити відсоток від суми значень таблиці Excel

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

Додайте додатковий стовпець до наявної таблиці.

У клітинку C2 запишіть таку формулу.

= B2 / $ B $ 9

Як ми вже знаємо, знак долара робить посилання абсолютним. Тобто вона не змінюється залежно від того, куди формула копіюється або перетягується за допомогою ручки автозаповнення. Без використання абсолютного посилання неможливо скласти формулу, яка порівнюватиме одне значення з іншим конкретним значенням, оскільки при зсуві вниз формула автоматично стане такою:

=B3/$B$10

Нам потрібно переконатися, що перша адреса рухається, а друга ні.

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

Після застосування відсоткового формату ми отримуємо такий результат.

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

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

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

Тепер опишемо, як це працює, на реальному прикладі.

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

1. Розділіть кількість доставлених товарів на загальну кількість. Для цього просто введіть = C2 / B2 до рядка формул.
2. Далі ця функція копіюється в необхідну кількість рядків за допомогою маркера автозаповнення. Клітинкам присвоюється формат «Відсоток». Для цього натисніть відповідну кнопку в групі «Додому».
3. Якщо після коми забагато або замало чисел, ви можете змінити цей параметр.

Після цих нескладних маніпуляцій ми отримуємо відсоток у клітинці. У нашому випадку він зазначений у стовпці D.

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

Потрібного числа може не бути ні в одній з клітинок. Тоді його доведеться вводити у формулу вручну. Досить просто написати відповідне число замість потрібного аргументу.

= 20/150

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

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

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

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

1. Праворуч вкажіть товар.
   

   

2. Робимо так, щоб інформація в комірці E2 відображалася у відсотках. 
3. Застосовувати СУММЕСЛІ підсумувати помідори і визначити відсоток. 

Остаточна формула буде наступною.

=СУММЕСЛИ($A$2:$A$8;$E$1;$B$2:$B$8)/B9

Як працює ця формула

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

Синтаксис цієї функції дуже простий. Першим аргументом записується діапазон оцінки критеріїв. На другому місці записується умова, а на третьому – діапазон, який підсумовується. 

Необов'язковий аргумент. Якщо ви не вкажете його, Excel використовуватиме перший як третій.

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

У деяких життєвих ситуаціях звична структура витрат може змінюватися. Можливо, доведеться внести деякі зміни. 

Формула додавання певного відсотка до числа дуже проста.

=Значення*(1+%)

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

=A1*(1-20%)

Різниця між числами у відсотках в Excel

Формула для визначення різниці між клітинками або окремими числами у відсотках має такий синтаксис.

(BA)/A

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

Маленький приклад: скажімо, вам вчора на склад привезли 80 яблук, а сьогодні привезли аж 100.

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

Як знайти відсоток між двома числами з двох стовпців у Excel

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

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

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

Як буде виглядати формула в нашому випадку, добре видно на скріншоті.

Залишається зробити два простих кроки:

1. Встановити відсотковий формат.
2. Перетягніть формулу в усі інші клітинки.

Як помножити на відсотки в excel

Іноді вам може знадобитися помножити вміст комірок на певний відсоток в Excel. Для цього потрібно просто ввести стандартну арифметичну дію у вигляді номера комірки або числа, потім написати зірочку (*), потім написати відсоток і поставити знак %.

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

Як знайти відсоток між двома числами з 2 рядків в excel

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

Як розрахувати відсотки по кредиту в Excel

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

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

Ось проста техніка, як розрахувати ануїтетні платежі в Excel.

1. Створіть таблицю з вихідними даними.
2. Створіть платіжну таблицю. Поки точної інформації в ньому не буде.
3. Введіть формулу =ПЛТ($B$3/12; $B$4; $B$2) до першої клітини. Тут ми використовуємо абсолютні посилання.
   

   

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

У перший місяць залишок боргу буде таким же, як і сума кредиту. Далі, щоб його розрахувати, потрібно скористатися формулою =ЕСЛИ(D10>$B$4;0;E9-G9), по нашій табличці. 

Щоб розрахувати виплату відсотків, вам потрібно скористатися такою формулою: =E9*($B$3/12).

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