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

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

Уміння працювати з відсотками може виявитися корисним в самих різних сферах життя. Це допоможе Вам, прикинути суму чайових в ресторані, розрахувати комісійні, обчислити прибутковість будь-якого підприємства і ступінь особисто Вашого інтересу в цьому підприємстві. Скажіть чесно, Ви зрадієте, якщо Вам дадуть промокод на знижку 25% для покупки нової плазми? Звучить заманливо, правда ?! А скільки насправді Вам доведеться заплатити, порахувати зможете?

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

Як визначити відсотки – Базові знання про відсотки

Термін Відсоток (per cent) прийшов з Латини (per centum) і перекладався спочатку як  З СОТНІ . У школі Ви вивчали, що відсоток – це якась частина з 100 часткою цілого. Відсоток розраховується шляхом ділення, де в чисельнику дробу знаходиться шукана частина, а в знаменнику – ціле, і далі результат множиться на 100.

Основна формула для розрахунку відсотків виглядає так:

(Часть/Целое)*100=Проценты

Приклад: У Вас було 20 яблук, з них 5 Ви роздали своїм друзям. Яку частину своїх яблук у відсотках Ви віддали? Зробивши нескладні обчислення, отримаємо відповідь:

(5/20)*100 = 25%

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

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

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

Основна формула розрахунку відсотків в Excel

Основна формула розрахунку відсотка в Excel виглядає так:

Часть/Целое = Процент

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

А тепер подивимося, як розрахунок відсотків в Excel може допомогти в реальній роботі з даними. Припустимо, в стовпець В у Вас записано кілька замовлених виробів (Ordered), а в стовпець З внесені дані про кількість доставлених виробів (Delivered). Щоб обчислити, яка частка замовлень вже доставлена, виконаємо наступні дії:

  • Запишіть формулу = C2 / B2 в комірці D2 і скопіюйте її вниз на стільки рядків, скільки це необхідно, скориставшись маркером автозаповнення.
  • Натисніть команду Percent Style (Процентний формат), щоб відображати результати розподілу в форматі відсотків. Вона знаходиться на вкладці Home (Головна) в групі команд Number (Число).
  • При необхідності налаштуйте кількість відображуваних знаків праворуч від коми.
  • Готово!

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

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

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

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

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

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

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

Наприклад, якщо у Вас записані якісь значення в стовпці B, а їх підсумок у клітинці B10, то формула обчислення відсотків буде наступна:

=B2/$B$10

Для комірки B2 використовуємо відносне посилання, щоб вона змінювалася, коли ми скопіюємо формулу в інші комірки стовпчика B. Посилання на комірку в знаменнику повинна залишатися незмінною при копіюванні формули, тому ми записали її як $ B $ 10.

Підказка: Є два способи зробити посилання на комірку в знаменнику абсолютної: або ввести знак $ вручну, або виділити в рядку формул одне з посилань на осередок і натиснути клавішу F4 .

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

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

Приклад 2. Частини загальної суми знаходяться в декількох рядках

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

В цьому випадку використовуємо функцію SUMIF (СУММЕСЛИ). Ця функція дозволяє підсумувати тільки ті значення, які відповідають якомусь певному критерію, в нашому випадку – це заданий продукт. Отриманий результат використовуємо для обчислення відсотка від загальної суми.

=SUMIF(range,criteria,sum_range)/total
=СУММЕСЛИ(диапазон;критерий;диапазон_суммирования)/общая сумма

У нашому прикладі стовпець A містить назви продуктів (Product) – це діапазон . Стовпець B містить дані про кількість (Ordered) – це діапазон суммування. У осередок E1 вводимо наш критерій – назва продукту, за яким необхідно розрахувати відсоток. Загальна сума по всіх продуктах порахована в осередку B10. Робоча формула буде виглядати так:

=SUMIF(A2:A9,E1,B2:B9)/$B$10
=СУММЕСЛИ(A2:A9;E1;B2:B9)/$B$10

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

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

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

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

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

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

Формула Excel, що обчислює зміну у відсотках (приріст / зниження)

Щоб розрахувати процентну зміну між значеннями A і B , використовуйте наступну формулу:

(B-A)/A = Зміна в відсотках

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

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

Отже, наша формула в Excel буде працювати за такою схемою:

(Нове значення – Старе значення) / Старе значення = Зміна в відсотках

А тепер давайте подивимося, як ця формула працює в Excel на практиці.

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

Припустимо, що в стовпці B записані ціни минулого місяця (Last month), а в стовпці C – ціни актуальні в цьому місяці (This month). У стовпець D внесемо таку формулу, щоб обчислити зміну ціни від минулого місяця до поточного в процентах.

=(C2-B2)/B2

Ця формула обчислює процентну зміну (приріст або зменшення) ціни в цьому місяці (стовпець C) в порівнянні з попереднім (стовпець B).

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

Приклад 2. Розрахунок зміни в процентах між рядками

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

=(C3-C2)/C2

Тут C2 це перше значення, а C3 це наступне по порядку значення.

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

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

Якщо Вам потрібно розрахувати для кожної комірки зміну у відсотках в порівнянні із значенням однієї конкретної комірки, використовуйте абсолютне посилання на цей осередок зі знаком $ , наприклад, так $ C $ 2.

Наприклад, ось так буде виглядати формула для розрахунку процентної зміни для кожного місяця в порівнянні з показником січня (January):

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

Коли Ви будете копіювати свою формулу з одного осередку в усі інші, абсолютне посилання залишиться незмінним, в той час як відносне посилання (C3) буде змінюватися на C4, C5, C6 і так далі.

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

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

Приклад 1. Розрахунок значення за відомим відсотку і загальній сумі

Припустимо, Ви купуєте новий комп’ютер за $ 950, але до цієї ціни треба додати ще ПДВ в розмірі 11%. Питання – скільки Вам потрібно доплатити? Іншими словами, 11% від зазначеної вартості – це скільки в валюті?

Нам допоможе така формула:

Total * Percentage = Amount
Загальна сумма * Процент = Значення

Припустимо, що Загальна сума (Total) записана в осередку A2, а Відсотки (Percent) – в осередку B2. В цьому випадку наша формула буде виглядати досить просто = A2 * B2 і дасть результат $ 104.50 :

Важливо запам’ятати: Коли Ви вручну вводите числове значення в комірку таблиці і після нього знак%, Excel розуміє це як соті частки від введеного числа. Тобто, якщо з клавіатури ввести 11%, то фактично в осередку буде зберігатися значення 0,11 – саме це значення Excel буде використовувати, здійснюючи обчислення.

Іншими словами, формула = A2 * 11% еквівалентна формулі = A2 * 0,11 . Тобто в формулах Ви можете використовувати або десяткові значення, або значення зі знаком відсотка – як Вам зручніше.

Приклад 2. Розрахунок загальної суми по відомому відсотку і значенням

Припустимо, Ваш друг запропонував купити його старий комп’ютер за $ 400 і сказав, що це на 30% дешевше його повної вартості. Ви хочете дізнатися, скільки ж коштував цей комп’ютер спочатку?

Так як 30% – це зменшення ціни, то першим ділом віднімемо це значення від 100%, щоб обчислити яку частку від початкової ціни Вам потрібно заплатити:

100%-30% = 70%

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

Amount/Percentage = Total
Значение/Процент = Общая сумма

Для вирішення нашої задачі ми отримаємо таку форму:

=A2/B2або =A2/0,7або=A2/70%

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

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

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

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

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

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

=Значение*(1-%)

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

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

Збільшити на відсоток: =A2*(1+B2)
Зменшити на відсоток:=A2*(1-B2)

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

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

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

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

    Щоб збільшити на відсоток: =1+20%
    Щоб зменшити на відсоток:=1-20%

В обох формулах ми взяли 20% для прикладу, а Ви можете використовувати те значення відсотків, яке Вам потрібно.

  1. Виберіть клітинку з формулою (в нашому прикладі це C2) і скопіюйте її, натиснувши Ctrl + C .
  2. Виділіть діапазон комірок, в якому треба зробити зміну. Клацніть по ньому правою кнопкою миші і в контекстному меню виберіть пункт Paste Special (Спеціальна вставка).
  3. У діалоговому вікні Paste Special (Спеціальна вставка) в розділі Paste (Вставити) виберіть пункт Values (Значення), а в розділі Operation (Операція) пункт Multiply (Помножити). Потім натисніть ОК .

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

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

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

На сьогодні все, дякую за увагу!

Джерело: http://www.ablebits.com/office-addins-blog/2015/01/14/calculate-percentage-excel-formula/

Переклад: https://blogchain.com.ua/

Степан Лютий

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

Вам також сподобається...

Залишити відповідь

Ваша e-mail адреса не оприлюднюватиметься. Обов’язкові поля позначені *