Як порахувати відсотки від числа 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 кроків для вирішення цього завдання:
- Внесемо дані, які потрібно збільшити або зменшити, в стовпець. У нашому прикладі це стовпець B.
- У порожній клітинці запишіть одну з формул:
Щоб збільшити на відсоток:
=1+20%
Щоб зменшити на відсоток:=1-20%
В обох формулах ми взяли 20% для прикладу, а Ви можете використовувати те значення відсотків, яке Вам потрібно.
- Виберіть клітинку з формулою (в нашому прикладі це C2) і скопіюйте її, натиснувши Ctrl + C .
- Виділіть діапазон комірок, в якому треба зробити зміну. Клацніть по ньому правою кнопкою миші і в контекстному меню виберіть пункт Paste Special (Спеціальна вставка).
- У діалоговому вікні 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/