Як розрахувати відсотки в Excel – з прикладами формул

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

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

Сьогодні ви зможете більш ефективно виконувати різні операції з відсотками в Excel.

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

Слово «відсоток» має латинське походження. У цій мові є конструкція «per centum», що перекладається як «сто». Багато людей з уроків математики можуть згадати, які існують формули.

Відсоток – це частка числа 100. Щоб його отримати, потрібно число А розділити на число В і отримане число помножити на 100.

Насправді основна формула для визначення відсотків така:

(Номер деталі/Цілий номер)*100.

Припустимо, у вас є 20 мандаринів, і ви хочете подарувати на Новий рік 5 з них. Скільки це у відсотках? Після виконання простих операцій (=5/20*100) отримуємо 25%. Це основний спосіб обчислення відсотка від числа в звичайному житті.

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

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

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

Основний спосіб визначення відсотків в Excel

Частина/Всього = відсоток

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

А які практичні приклади визначення відсотка в Excel? Припустімо, ви продавець фруктів та інших продуктів харчування. У вас є документ, в якому вказана кількість речей, замовлених клієнтами. Цей список наведено в стовпці A, а кількість замовлень у стовпчику B. Деякі з них мають бути доставлені, і це число наведено у стовпчику C. Відповідно, у стовпчику D буде показано частку доставлених продуктів. Щоб його розрахувати, потрібно виконати такі дії:

1. Укажіть = C2 / B2 у комірку D2 і перемістіть його вниз, скопіювавши на необхідну кількість комірок.
2. Натисніть кнопку «Відсотковий формат» на вкладці «Головна» в розділі «Число».
3. Не забудьте збільшити кількість цифр після коми, якщо необхідно.

Це все.

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

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

Це робиться таким чином

Визначення частки від цілого значення

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

Випадок 1: ціле число знаходиться внизу таблиці в певній клітинці

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

Знак долара $ дає вам можливість прив’язати посилання до певної комірки. Тому вона залишиться незмінною, хоча формулу буде скопійовано в інше місце. Так, якщо в стовпці B вказано кілька показань, а їх сумарне значення записане в клітинку B10, важливо визначити відсоток за формулою: = B2 / $ B $ 10.

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

Якщо адреса написана в комірці $10 B$, у цьому випадку знаменник буде однаковим до рядка 9 таблиці нижче.

Рекомендація: щоб перетворити відносну адресу в абсолютну, ви повинні ввести в ній знак долара. Також можна клацнути по потрібному посиланню в рядку формул і натиснути кнопку F4.

Ось скріншот, на якому показано наш результат. Тут ми відформатували клітинку так, щоб відображалися дроби до сотих.

Приклад 2: частини цілого перераховані в різних рядках

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

Для простоти ось формула:

=SUMIF(діапазон значень, умова, діапазон підсумовування)/сума.

Оскільки в стовпці А містяться всі назви товарів, а в стовпці В вказано, скільки було зроблено покупок, а в клітинці E1 описується назва необхідного товару, а сума всіх замовлень - в клітинці B10, то формула матиме такий вигляд:

=SUMIF(A2:A9 ;E1; B2:B9) / $B$10.

Також користувач може прописати назву товару прямо в умові:

=SUMIF(A2:A9, «вишні», B2:B9) / $B$10.

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

=(SUMIF(A2:A9, «вишні», B2:B9) + SUMIF(A2:A9, «яблука», B2:B9)) / $B$10.

Як розрахувати ступінь корекції значення у відсотках в Excel

Методів розрахунку багато. Але, мабуть, найчастіше використовується формула для визначення зміни у відсотках. Щоб зрозуміти, наскільки збільшився або зменшився показник, існує формула:

Відсоткова зміна = (BA) / A.

Виконуючи фактичні розрахунки, важливо розуміти, яку змінну використовувати. Наприклад, місяць тому було 80 персиків, а зараз їх 100. Це означає, що зараз у вас на 20 персиків більше, ніж раніше. Підвищення склало 25 відсотків. Якщо до цього було 100 персиків, а зараз залишилося лише 80, то це говорить про зменшення кількості на 20 відсотків (так як 20 штук зі ста – це 20%).

Тому формула в Excel буде виглядати так: (Нове значення – старе значення) / старе значення.

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

Приклад 1: обчислення зміни значення між стовпцями

Припустимо, у стовпчику B вказані ціни за останній звітний період, а у стовпчику C – за поточний. Потім введіть таку формулу в клітинку C2, щоб дізнатися швидкість зміни значення:

= (C2-B2) / B2

Він вимірює ступінь збільшення або зменшення вартості продуктів, перелічених у стовпці A, порівняно з попереднім місяцем (стовпець B).

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

У цьому прикладі позитивні тенденції показані чорним кольором, а негативні – червоним.

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

Якщо є лише один стовпець чисел (наприклад, C, що містить щоденні та щотижневі продажі), ви зможете обчислити зміну ціни у відсотках за цією формулою:

= (S3-S2) / S2.

C2 — перша, а C3 — друга комірка.

примітки. Перший рядок потрібно пропустити, а в другу комірку вписати потрібну формулу. У наведеному прикладі це D1.

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

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

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

=(C3-$C$2)/$C$2.

Коли ви копіюєте комірку в інші комірки, абсолютна адреса не змінюється, поки відносна починає посилатися на C4, C5 тощо.

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

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

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

Припустімо, ви купуєте ноутбук за 950 доларів і повинні заплатити 11% податку на покупку. Скільки грошей доведеться заплатити в підсумку? Іншими словами, скільки буде 11% від 950 доларів?

Формула:

Ціле число * відсоток = частка.

Якщо припустити, що ціле знаходиться в комірці A2, а відсоток – у комірці B2, воно перетворюється на просту =A2*B2  У клітинці відображається значення $104,50.

Пам’ятайте, що коли ви записуєте значення, яке відображається зі знаком відсотка (%), Excel інтерпретує його як соту. Наприклад, 11% зчитується програмою як 0.11, і Excel використовує цю цифру в усіх розрахунках.

Іншими словами, формула =А2*11% аналог =А2*0,11. Звичайно, ви можете використовувати значення 0,11 замість відсотка безпосередньо у формулі, якщо це зручніше на даний момент.

Приклад 2: знаходження цілого за дробом і відсотком

Наприклад, друг запропонував вам свій старий комп’ютер за 400 доларів, що становить 30% від його вартості покупки, а вам потрібно знати, скільки коштує новий комп’ютер.

Спочатку потрібно визначити, скільки відсотків від початкової ціни коштує вживаний ноутбук.

Виявляється, його ціна становить 70 відсотків. Тепер потрібно знати формулу розрахунку первісної вартості. Тобто зрозуміти, з якого числа 70% буде 400. Формула така:

Частка загальної суми / відсоток = загальна вартість.

Якщо застосувати до реальних даних, це може мати одну з таких форм: =A2/B2 або =A2/0.7 або =A2/70%.

Як змінити число на деякий відсоток

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

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

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

Наприклад, у формулі =A1*(1+20%) значення клітинки A1 збільшується на п'яту частину.

Щоб зменшити число, скористайтеся формулою:

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

Так, формула = A1*(1-20%) зменшує значення в клітинці A1 на 20%.

У описаному прикладі, якщо A2 — це ваші поточні витрати, а B2 — це відсоток, на який ви повинні їх змінити, вам потрібно записати формули в клітинку C2:

1. Відсоткове збільшення:  =A2*(1+B2).
2. Зменшити у відсотках: =A2*(1-B2).

Як збільшити або зменшити всі значення цілого стовпця на відсоток

Як змінити всі значення в стовпці на відсотки?

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

1. Всі значення, які вимагають виправлення, введіть в певний стовпець. Наприклад, у колонці В.
2. У порожню клітинку запишіть одну з наступних формул (залежно від завдання):
   1. Збільшити: =1+20%
   2. Зменшення: =1-20%.

Природно, замість «20%» потрібно вказати необхідне значення.

1. Виберіть комірку, в якій записана формула (це C2 в прикладі, який ми описуємо), і скопіюйте, натиснувши комбінацію клавіш Ctrl + C.
2. Виберіть набір клітинок, які потрібно змінити, клацніть їх правою кнопкою миші та виберіть «Спеціальна вставка…» в англійській версії Excel або «Спеціальна вставка» в .

1. Далі з'явиться діалогове вікно, в якому потрібно вибрати параметр «Значення» (values), і задати операцію «Множення» (multiply). Далі натисніть кнопку «ОК».

І ось результат – всі значення в стовпці B збільшено на 20%.

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