Вважаємо відсоток від числа в Excel. Програма Microsoft Excel: додавання відсотка до числа
Під час обчислень іноді потрібно додати відсотки до конкретного числа. Наприклад, щоб дізнатися про поточні показники прибутку, що збільшився на певний відсоток порівняно з минулим місяцем, потрібно до величини прибутку минулого місяця додати цей відсоток. Існує і безліч інших прикладів, коли потрібно виконати таку дію. Давайте розберемося, як додати відсоток до програми Microsoft Excel.
Отже, якщо вам просто потрібно дізнатися, чому дорівнює число, після додавання до нього певного відсотка, то слід в будь-яку комірку листа, або в рядок формул, вбити вираз за наступним шаблоном: «=(число)+(число)*(величина_процента )%».
Припустимо, нам потрібно вирахувати, яке число вийде, якщо додати до 140 20 відсотків. Записуємо наступну формулу в будь-яку комірку, або рядок формул: «=140+140*20%».
Застосування формули для дій у таблиці
Тепер, давайте розберемося, як додати певний відсоток до даних, які вже знаходяться в таблиці.
Насамперед, вибираємо комірку, куди буде виводитися результат. Ставимо у ній знак «=». Далі, клацаємо по комірці, що містить дані, до яких слід додати відсоток. Ставимо знак "+". Знову клацаємо по осередку, що містить число, ставимо знак «*». Далі, набираємо на клавіатурі величину відсотка, який слід збільшити число. Не забуваймо після введення цієї величини ставити знак «%».
Клацаємо по кнопці ENTER на клавіатурі, після чого буде показано результат обчислення.
Якщо ви хочете поширити цю формулу на всі значення стовпця в таблиці, то просто станьте на правий нижній край осередку, де виведений результат. Курсор повинен перетворитися на хрестик. Тиснемо на ліву кнопку миші, і із затиснутою кнопкою «протягуємо» формулу вниз до самого кінця таблиці.
Як бачимо, результат множення чисел на певний відсоток виведено і для інших осередків у колонці.
Ми з'ясували, що додати відсоток до програми Microsoft Excel не так вже й складно. Тим не менш, багато користувачів не знають, як це зробити, і припускаються помилок. Наприклад, найпоширенішою помилкою є написання формули алгоритму «=(число)+(величина_процента)%», замість «=(число)+(число)*(величина_процента)%». Цей посібник повинен допомогти не допускати подібних помилок.
Можливо, після деякого часу ви співпрацювали відсотків в Excel. Або, можливо, що розрахунок відсотків не ваші навички обрані математичні. У цій статті ви побачите, як це може бути набагато простіше Якщо дозвольте Excel зробити всю роботу.
Розмноження цілого стовпця чисел на відсоткове значення
Розглянемо приклад таблиці, як показано на малюнку, де ми маємо кілька чисел, щоб помножити на 15%. Навіть якщо стовпець містить 100 і 1000 осередків з даними, Excel, як і раніше, може обробляти його за кілька кроків.
Ось як це зробити:
Порада:Можна також множення шпальти для віднімання відсоткового значення. Щоб відняти 15%, додайте знак мінус перед відсоток і віднімання відсотка від 1, використовуючи формулу = 1 -n% , в який n - Відсоток. Щоб відняти 15%, використовуйте як формулу = 1-15% .
Зміна суми на задану кількість відсотків
Припустимо, вам потрібно зменшити – чи визначить – ці щотижневі витрати на 25%. Щоб обчислити суму, використовуйте формулу для віднімання чи додавання відсотка.
У цьому прикладі ми стовпця B повинен містити в даний час, витрачене на суму набору і стовпець C містить відсоток за якими скоротите. Ось формулу можна ввести в осередку D2 для виконання цього завдання:
= B2 * (1-C2)
У цій формулі 1 збігається зі 100%. Значення у дужках обчислити по-перше, тому друг значення C2 віднімається від 1 до чекаємо 75%. Результат множиться B2, щоб отримати результат 56.25 для 1 тижня.
Щоб скопіювати формулу в осередку D2 вниз по стовпцю, двічі клацніть квадрат зелений квадратик в правому нижньому кутку осередок D2. Щоб отримати результати у всіх інших комірок без копіювання та вставки формули або вводити.
Щоб збільшитина 25%, просто замінити + вхід формула в комірці D2 увійдіть мінус (-):
= B2 * (1 + C2)
Ще раз двічі клацніть на маркері заповнення вниз.
У цьому уроці Ви познайомитеся з різними корисними формулами складання та віднімання дат в Excel. Наприклад, Ви дізнаєтеся, як відняти з однієї дати іншу, як додати до дати кілька днів, місяців чи років, тощо.
Якщо Ви вже проходили уроки з роботи з датами в Excel (наші чи будь-які інші уроки), то повинні знати формули для обчислення одиниць часу, таких як дні, тижні, місяці, рік.
Аналізуючи дати в будь-яких даних, часто потрібно виконувати над цими датами арифметичні дії. Ця стаття пояснить деякі формули складання та віднімання дат, які можуть виявитися для Вас корисними.
Як віднімати дати в Excel
Припустимо, що у Вас у осередках A2і B2містяться дати, і потрібно вирахувати одну дату з іншої, щоб дізнатися, скільки між ними днів. Як часто буває в Excel, цей результат можна отримати кількома способами.
Приклад 1. Безпосередньо віднімаємо одну дату з іншої
Думаю, Ви знаєте, що Excel зберігає дати у вигляді цілих чисел, починаючи з 1, що відповідає 1 січня 1900 р. Тому Ви можете просто арифметично відняти одне число від іншого:
Приклад 2. Віднімання дат за допомогою функції РОЗНДАТ
Якщо попередня формула здається Вам дуже простою, той же результат можна отримати більш витонченим способом за допомогою функції РОЗНДАТ(DATEDIF).
РОЗНДАТ(A2; B2; "d")
=DATEDIF(A2,B2,"d")
Наступний малюнок показує, що обидві формули повертають однаковий результат, за винятком ряду 4 де функція РОЗНДАТ(DATEDIF) повертає помилку #КІЛЬКІСТЬ!(#NUM!). Побачимо, чому це відбувається.
Коли Ви вичитаєте пізнішу дату (6 травня 2015) з більш ранньої (1 травня 2015), операція віднімання повертає негативне число. Однак синтаксис функції РОЗНДАТ(DATEDIF) не дозволяє, щоб початкова датабула більша кінцевої датиі, звичайно, повертає помилку.
Приклад 3. Віднімаємо дату з поточної дати
Щоб відняти певну дату з поточної дати, Ви можете використовувати будь-яку з описаних раніше формул. Просто замість сьогоднішньої дати використовуйте функцію СЬОГОДНІ(TODAY):
СЬОГОДНІ()-A2
=TODAY()-A2
РОЗНДАТ(A2;СЬОГОДНІ();"d")
=DATEDIF(A2,TODAY(),"d")
Як і в попередньому прикладі, формули добре працюють, коли поточна дата більша, ніж віднімається. В іншому випадку функція РОЗНДАТ(DATEDIF) повертає помилку.
Приклад 4. Віднімання дат за допомогою функції ДАТА
Якщо Ви волієте вводити дати безпосередньо у формулу, вказуйте їх за допомогою функції ДАТА(DATE), а потім відніміть одну дату з іншої.
Функція ДАТАмає наступний синтаксис: ДАТА( рік; місяць; день) .
Наприклад, наступна формула віднімає 15 травня 2015 року з 20 травня 2015 року та повертає різницю – 5 днів.
ДАТА(2015;5;20)-ДАТА(2015;5;15)
=DATE(2015,5,20)-DATE(2015,5,15)
Якщо потрібно підрахувати кількість місяців чи років між двома датамитоді функція РОЗНДАТ(DATEDIF) – єдине можливе рішення. У продовженні статті Ви знайдете кілька прикладів формул, які докладно розкривають цю функцію.
Тепер, коли Ви знаєте, як відняти одну дату з іншої, подивимося, як можна додати або відняти з дати певну кількість днів, місяців або років. Існує кілька функцій Excel. Що саме вибрати залежить від того, які одиниці часу потрібно додати чи відняти.
Як додати (відняти) дні до дати в Excel
Якщо у Вас є дата в осередку або список дат у стовпці, Ви можете додати до них (або відібрати) певну кількість днів, використовуючи відповідну арифметичну операцію.
Приклад 1. Додавання днів до дати в Excel
Загальна формула додавання певної кількості днів до дати має такий вигляд:
= Дата + N днів
Дату можна задати декількома способами:
- Посилання на комірку:
- Зверненням до функції ДАТА(DATE):
ДАТА(2015; 5; 6) +10
=DATE(2015,5,6)+10 - Зверненням до іншої функції. Наприклад, щоб додати кілька днів до поточної дати, скористайтеся функцією СЬОГОДНІ(TODAY):
СЬОГОДНІ()+10
=TODAY()+10
Наступний рисунок показує дію цих формул. На момент написання тексту поточною датою було 6 травня 2015 року.
Примітка:Результатом виконання цих формул є ціле число, що становить дату. Щоб показати його як дату, необхідно вибрати комірку (або комірки) та натиснути Ctrl+1. Відкриється діалогове вікно Формат осередків(Format Cells). На вкладці Число(Number) у списку цифрових форматів виберіть Дата(Date) і потім вкажіть потрібний формат. Більш детальний опис Ви знайдете у статті.
Приклад 2. Віднімання днів з дати в Excel
Щоб вирахувати певну кількість днів з дати, Вам знову потрібно використовувати звичайну арифметичну операцію. Єдина відмінність від попереднього прикладу – мінус замість плюса
= Дата - N днів
Ось кілька прикладів формул:
A2-10
= ДАТА (2015; 5; 6)-10
=СЬОГОДНІ()-10
Як додати (відняти) кілька тижнів до дати
Коли потрібно додати (відняти) кілька тижнів до певної дати, Ви можете скористатися тими самими формулами, що й раніше. Просто потрібно помножити кількість тижнів на 7:
- Додаємо N тижнівдо дати в Excel:
A2 + N тижнів * 7
Наприклад, щоб додати 3 тижні до дати в комірці А2, використовуйте таку формулу:
- Віднімаємо N тижнівз дати до Excel:
А2 - N тижнів * 7
Щоб відняти 2 тижні від сьогоднішньої дати, використовуйте цю формулу:
СЬОГОДНІ()-2*7
=TODAY()-2*7
Як додати (відняти) кілька місяців до дати в Excel
Щоб додати (або відібрати) певну кількість місяців до дати, потрібно використовувати функцію ДАТА(DATE) або ДАТАМЕС(EDATE), як показано нижче.
Приклад 1. Додаємо кілька місяців до дати за допомогою функції ДАТА
Якщо список дат знаходиться, наприклад, у стовпці A, вкажіть кількість місяців, які Ви хочете додати (позитивне число) або відібрати (негативне число) в якомусь осередку, скажімо, в C2.
Введіть у комірку B2вказану нижче формулу, клацніть по виділеному кутку комірки і протягніть його мишкою вниз по стовпцю Bдо останнього заповненого осередку в стовпці A. Формула з комірки B2буде скопійовано у всі осередки стовпця B.
ДАТА(РІК(A2);МІСЯЦЬ(A2)+$C$2;ДЕНЬ(A2))
=DATE(YEAR(A2),MONTH(A2)+$C$2,DAY(A2))
Подивимося, що робить ця формула. Логіка формули зрозуміла і очевидна. Функція ДАТА( рік; місяць; день) отримує такі аргументи:
- Рік з дати в осередку A2;
- Місяць з дати в осередку A2+ кількість місяців, вказана в осередку C2;
- День з дати в осередку A2;
Все просто! Якщо Ви введете в C2негативне число, формула відніме місяці, а чи не додасть.
Звичайно, ніщо не заважає Вам ввести мінус прямо у формулі, щоб відняти місяці:
ДАТА(РІК(A2);МІСЯЦЬ(A2)-$C$2;ДЕНЬ(A2))
=DATE(YEAR(A2),MONTH(A2)-$C$2,DAY(A2))
І, звичайно, ви можете вказати кількість місяців, що додаються або віднімаються, безпосередньо у формулі без посилання на комірку. Готові формули виглядатимуть приблизно так:
- Додати місяцідо дати:
ДАТА(РІК(A2);МІСЯЦЬ(A2)+2;ДЕНЬ(A2))
=DATE(YEAR(A2),MONTH(A2)+2,DAY(A2)) - Відняти місяціз дати:
ДАТА(РІК(A2);МІСЯЦЬ(A2)-2;ДЕНЬ(A2))
=DATE(YEAR(A2),MONTH(A2)-2,DAY(A2))
Приклад 2. Додаємо або віднімаємо місяці з дати за допомогою функції ДАТАМЕС
В Excel передбачена спеціальна функція, яка повертає дату, віддалену від заданої на певну кількість місяців тому або вперед, – це функція ДАТАМЕС(EDATE). Вона доступна в останніх версіях Excel 2007, 2010, 2013 та нової Excel 2016.
При використанні ДАТАМЕС(EDATE) Ви вказуєте два наступні аргументи:
- початкова дата - Дата, від якої відраховується кількість місяців.
- Місяці - Кількість місяців, які потрібно додати (позитивне число) або відібрати (негативне число).
Ці формули дадуть той самий результат, що й формули з функцією ДАТА(DATE) у попередньому прикладі:
При використанні функції ДАТАМЕС(EDATE) Початкову дату та кількість місяців можна вказувати безпосередньо у формулі. Дати можна задавати за допомогою функції ДАТА(DATE) або як результат виконання інших формул. Наприклад:
- Ця формула додає 10 місяців до 7 травня 2015 року.
ДАТАМЕС(ДАТА(2015;5;7);10)
=EDATE(DATE(2015,5,7),10) - Ця формула забирає 10 місяців від сьогоднішньої дати
ДАТАМЕС(СЬОГОДНІ();-10)
=EDATE(TODAY(),-10)
Примітка:Функція ДАТАМЕС(EDATE) повертає ціле число. Щоб подати його як дату, необхідно застосувати до комірки формат дати. Як це зробити, зазначено у статті Як змінити формат дати в Excel.
Як додати (відібрати) роки до дати в Excel
Додавання років до дат в Excel здійснюється так само, як додавання місяців. Вам необхідно знову використати функцію ДАТА(DATE), але цього разу потрібно вказати кількість років, які Ви бажаєте додати:
ДАТА(РІК( дата) + N років; МІСЯЦЬ( дата); ДЕНЬ( дата))
= DATE(YEAR( дата) + N років, MONTH ( дата), DAY( дата))
На листі Excel формули можуть виглядати так:
- Додаємо 5 роківдо дати, зазначеної в осередку A2:
ДАТА(РІК(A2)+5;МІСЯЦЬ(A2);ДЕНЬ(A2))
=DATE(YEAR(A2)+5,MONTH(A2),DAY(A2)) - Віднімаємо 5 роківз дати, зазначеної в осередку A2:
ДАТА(РІК(A2)-5;МІСЯЦЬ(A2);ДЕНЬ(A2))
=DATE(YEAR(A2)-5,MONTH(A2),DAY(A2))
Щоб отримати універсальну формулу, Ви можете ввести кількість років у комірку, а потім у формулі звернутися до цієї комірки. Позитивне число дозволить додати роки до дати, а негативне – відняти.
Додавання (віднімання) днів, місяців і років до дати
Якщо Ви уважно читали два попередні приклади, то, гадаю, здогадалися, як додати (або відняти) відразу роки, місяці та дні до дати в одній формулі. Так, за допомогою старої доброї функції ДАТА(DATA)!
- Для поповнення X років, Y місяців та Z днів:
ДАТА(РІК( дата) + X років; МІСЯЦЬ( дата) + Y місяців; ДЕНЬ( дата) + Z днів)
= DATE(YEAR( дата) + X років, MONTH ( дата) + Y місяців, DAY( дата) + Z днів) - Для віднімання X років, Y місяців та Z днів:
ДАТА(РІК( дата) - X років; МІСЯЦЬ( дата) - Y місяців; ДЕНЬ( дата) - Z днів)
= DATE(YEAR( дата) - X років, MONTH ( дата) - Y місяців, DAY( дата) - Z днів)
Наприклад, наступна формула додає 2 роки і 3 місяці, і віднімає 15 днів з дати в осередку A2:
ДАТА(РІК(A2)+2;МІСЯЦЬ(A2)+3;ДЕНЬ(A2)-15)
=DATE(YEAR(A2)+2,MONTH(A2)+3,DAY(A2)-15)
Стосовно нашого стовпця з датами, формула набуває наступного вигляду:
ДАТА(РІК(A2)+$C$2;МІСЯЦЬ(A2)+$D$2;ДЕНЬ(A2)+$E$2)
=DATE(YEAR(A2)+$C$2,MONTH(A2)+$D$2,DAY(A2)+$E$2)
Як складати та віднімати час в Excel
У Microsoft Excel складати та віднімати час можна за допомогою функції ЧАС(TIME). Вона дозволяє Вам поводитися з одиницями часу (годинами, хвилинами та секундами) так само, як і з роками, місяцями та днями у функції ДАТА(DATE).
- Додати часв Excel:
A2 + ЧАС( годинник; хвилини; секунди)
= A2 + TIME( годинник, хвилини, секунди) - Відняти часв Excel:
A2 - ЧАС( годинник; хвилини; секунди)
= A2 - TIME( годинник, хвилини, секунди)де A2– це осередок із часом, який потрібно змінити.
Наприклад, щоб додати 2 години 30 хвилин і 15 секунд до часу в осередку A2треба використовувати таку формулу:
A2+ЧАС(2;30;15)
=A2+TIME(2,30,15)
A2+ЧАС(2;30;-15)
=A2+TIME(2,30,-15)
Також Ви можете ввести потрібні значення в комірки листа та посилатися на них у формулі:
A2+ЧАС($C$2;$D$2;$E$2)
=A2+TIME($C$2,$D$2,$E$2)
Розраховувати відсотки та виконувати дії з ними найзручніше у програмі Excel у пакеті Microsoft Office, оскільки все, що потрібно – це вказати значення та потрібну формулу.
Відсоток – це сота частина цілого числа, що на папері позначається знаком % або десятковими дробами (70% = 0,70). Стандартний вираз для розрахунків відсотка – Ціле/Частина*100, але завдяки Excel вручну нічого рахувати не доведеться.
Як порахувати відсотки в Excel
Простота роботи з програмою полягає в тому, що користувачеві достатньо ввести значення цілого та його частини (або вибрати із введених раніше даних), і вказати принцип обчислення, а розрахунки Excel зробить самостійно. В Excel відсоток обчислюється так - Частина/Ціле = Відсоток, а множення на 100 відбувається автоматично, коли користувач вибирає процентний формат:
Для проведення обчислень візьмемо розрахунок виконання робочого плану:
Програма самостійно розрахує відсоток виконання плану кожного продукту.
Відсоток від числа
В Excel можна обчислити цифру, знаючи лише її частку: %*Частина = Ціле. Допустимо, необхідно обчислити, що становить 7% від 70. Для цього:
Якщо розрахунок йде під час роботи з таблицею, замість введення чисел треба давати посилання потрібні клітини. Варто бути уважним, при розрахунку формат має бути Загальним.
Відсоток від суми
Якщо ж дані розкидані по всій таблиці, необхідно використовувати формулу СУМІСЛІ- Вона складає значення, які відповідають заданим параметрам, у прикладі - задані продукти. Формула для прикладу виглядатиме так – «=СУМІСЛІ (діапазон критерій; діапазон додавання)/загальна сума»:
Отже вважається кожен параметр, тобто. продукт.
Розрахунок зміни у відсотках
Порівняння двох часток також можливе за допомогою Ексель. Для цього можна просто знайти значення та відняти їх (з більшого менше), а можна скористатися формулою приросту/зменшення. Якщо треба порівняти числа А та В, то формула виглядає так « (B-A)/A = різниця». Розглянемо приклад розрахунку в Excel:
- Розтягнути формулуна весь стовпець за допомогою маркера автозаповнення.
Якщо розрахункові показники розташовуються в одному стовпці для конкретного товару за тривалий період часу, то метод розрахунку зміниться:
Позитивні значення свідчить про приріст, а негативні – зменшення.
Розрахунок значення та загальної суми
Найчастіше, необхідно визначити підсумкову суму знаючи лише частку. У Excel це можна зробити двома способами. Розглянемо покупку ноутбука припустивши, що він коштує 950 доларів. Продавець каже, що це ціна без урахування податку на додану вартість, який становить 11%. Підсумкову націнку можна дізнатися, зробивши розрахунки Ексель:
Другий спосіб розрахунку розглянемо іншому прикладі. Допустимо, при покупці ноутбука за 400 доларів продавець каже, що ціна порахована з урахуванням 30% знижки. Дізнатися початкову ціну можна так:
Початкова вартість становитиме 571,43 доларів.
Як змінити значення на величину відсотка
Часто доводиться збільшувати або зменшувати підсумкове число на якусь його частку, наприклад, необхідно збільшити щомісячні витрати на 20%, 30% та 35%:
Програма визнає результат самостійно по всьому стовпчику, якщо протягнути вираз за допомогою маркера заповнення. Вираз для зменшення суми такий самий, тільки зі знаком мінус – « = Значення * (1-%)».
Операції з відсотками
З частками можна здійснювати такі самі операції, як і зі звичайними числами: додавання, віднімання, множення. Наприклад, Excel можна порахувати різницю в показниках між продажами компанії за допомогою команди AВS, яка повертає абсолютну величину числа:
Різниця між показником становитиме 23 %.
Можна також додати (або відняти) відсоток до числа - розглянемо дію на прикладі планування відпустки:
Програма самостійно здійснить обчислення та результати будуть 26000 рублів для відпускного тижня та 14000 рублів після відпустки відповідно.
Помноження числа на частку здійснювати в Ексель набагато простіше, ніж вручну, оскільки достатньо вказати необхідне значення та відсоток, а програма все визнає сама:
Усі суми можна швидко перерахувати, якщо розтягнути формулу весь стовпець F. 
Щоб відняти частку, необхідно вказати в осередку А1 цифру, а клітині А2 – відсоток. Розрахунки здійснити в клітині В1, вписавши формулу « = А1-(А1 * А2)». 
У цьому уроці Ви побачите, як за допомогою 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(Головна) у групі команд Номер(Кількість).
- За необхідності налаштуйте кількість знаків, що відображаються праворуч від коми.
- Готово!
Якщо для обчислення відсотків в Excel Ви будете використовувати будь-яку іншу формулу, загальна послідовність кроків залишиться такою самою.
У нашому прикладі стовпець D містить значення, які в процентах, яку частку від загальної кількості замовлень становлять вже доставлені замовлення. Усі значення округлені до цілих чисел.
Розрахунок відсотка від загальної суми в Excel
Насправді приклад, наведений , є окремий випадок розрахунку відсотка від загальної суми. Щоб краще зрозуміти цю тему, розглянемо ще кілька завдань. Ви побачите, як швидко зробити обчислення відсотка від загальної суми в Excel на прикладі різних наборів даних.
Приклад 1. Загальна сума порахована внизу таблиці у конкретному осередку
Дуже часто наприкінці великої таблиці з даними є осередок з підписом Підсумок, у якому обчислюється загальна сума. У цьому маємо завдання порахувати частку кожної частини щодо загальної суми. У такому разі формула розрахунку відсотка буде виглядати так само, як і в попередньому прикладі, з однією відмінністю - посилання на комірку в знаменнику дробу буде абсолютною (зі знаками $ перед ім'ям рядка та ім'ям стовпця).
Наприклад, якщо у Вас записані якісь значення в стовпці B, а їх результат у осередку B10, то формула обчислення відсотків буде така:
Підказка:Є два способи зробити посилання на комірку в знаменнику абсолютним: або ввести знак $ вручну, або виділити у рядку формул потрібне посилання на комірку та натиснути клавішу 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,"черени",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, що обчислює зміну у відсотках (приріст/зменшення)
(B-A)/A = Зміна у відсотках
Використовуючи цю формулу в роботі з реальними даними дуже важливо правильно визначити, яке значення поставити на місце A, а яке – на місце B.
Приклад:Вчора у вас було 80 яблук, а сьогодні у вас є 100 яблук. Це означає, що сьогодні у вас на 20 яблук більше, ніж було вчора, тобто ваш результат – приріст на 25%. Якщо ж учора яблук було 100, а сьогодні 80 – це зменшення на 20%.
Отже, наша формула в Excel працюватиме за такою схемою:
(Нове значення – Старе значення) / Старе значення = Зміна у відсотках
А тепер давайте подивимося, як ця формула працює в Excel на практиці.
Приклад 1. Розрахунок зміни у відсотках між двома стовпцями
Припустимо, що у стовпці B записані ціни минулого місяця (Last month), а стовпці C – ціни актуальні цього місяця (This month). У стовпець D внесемо таку формулу, щоб обчислити зміну ціни від минулого місяця до поточного у відсотках.
Ця формула обчислює відсоткову зміну (приріст або зменшення) ціни цього місяця (стовпець C) порівняно з попереднім (стовпець B).
Після того, як Ви запишите формулу в перший осередок і скопіюєте її у всі необхідні рядки, потягнувши за маркер автозаповнення, не забудьте встановити Відсотковий форматдля осередків із формулою. В результаті у Вас має вийти таблиця, подібна до зображеної на малюнку нижче. У прикладі позитивні дані, які показують приріст, відображаються стандартним чорним кольором, а негативні значення (зменшення у відсотках) виділені червоним кольором. Докладно про те, як настроїти таке форматування, читайте у цій статті.
Приклад 2. Розрахунок зміни у відсотках між рядками
У випадку, коли Ваші дані розташовані в одному стовпці, який відображає інформацію про продаж за тиждень або за місяць, зміну у відсотках можна розрахувати за такою формулою:
Тут C2 це перше значення, а C3 це наступне значення.
Примітка:Зверніть увагу, що при такому розташуванні даних у таблиці перший рядок з даними необхідно пропустити і записувати формулу з другого рядка. У нашому прикладі це буде осередок D3.
Після того, як Ви запишите формулу та скопіюєте її у всі необхідні рядки своєї таблиці, у Вас має вийти щось схоже на це:
Наприклад, ось так виглядатиме формула для розрахунку процентної зміни для кожного місяця порівняно з показником Січня(January):
Коли Ви копіюватимете свою формулу з одного осередку у всі інші, абсолютне посилання залишиться незмінним, у той час як відносне посилання (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%, щоб обчислити якусь частку від первісної ціни Вам потрібно заплатити:
Тепер нам потрібна формула, яка обчислить первісну ціну, тобто знайде те число, 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 кроків для вирішення цього завдання:
В обох формулах ми взяли 20% для прикладу, а Ви можете використовувати значення відсотків, яке Вам потрібно.
В результаті значення у стовпці B збільшаться на 20%.
У такий спосіб можна множити, ділити, складати чи віднімати з цілого стовпця з даними якийсь відсоток. Просто введіть потрібний відсоток у порожню комірку і зробіть кроки, описані вище.
Ці способи допоможуть Вам обчислити проценти в Excel. І навіть, якщо відсотки ніколи не були Вашим улюбленим розділом математики, володіючи цими формулами та прийомами, Ви змусите Excel виконати за Вас всю роботу.
На сьогодні все, дякую за увагу!
