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

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

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

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

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

Ви отримаєте навички, які дозволять вам вміло працювати з відсотками в Excel.

деморолик

Ви можете подивитися повне керівництво в деморолик вище або прочитати покроковий опис нижче. Для початку скачайте вихідні безкоштовні файли: Excel percentages worksheets. Ми будемо використовувати їх в процесі виконання вправ.

1. Введення даних в Excel

Вставте наступні дані (або відкрийте завантажений файл ” persentages.xlsx “, що знаходиться в початкових файлах цього керівництва). У ньому є лист Expenses (Витрати). Пізніше ми будемо використовувати лист Grades (Оцінки).

Дані листа з відсотками

2.Розрахуйте збільшення відсотка

Припустимо, що ви очікуєте збільшення витрат на 8% в наступному році і ви хочете бачити ці значення.

Перед тим як почати писати формули, корисно знати, що в Excel ви можете робити розрахунки використовуючи і знак відсотка (20%), і десяткові дроби (0.2 або просто .2). Символ відсотка для Excel це всього лише форматування.

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

Крок 1

В осередку A18 напишіть з 8% -ним зростанням. Оскільки в цьому осередку і текст, і число, Excel буде вважати, що вся осередок містить текст.

крок 2

Натисніть Tab , потім в осередку B18 напишіть наступну формулу: =B17 * 1.08

Або ви можете використовувати наступну формулу: =B17 * 108%

Отримана сума буде 71,675, як показано нижче:

Розрахунок збільшення відсотка в Excel

3. Розрахунок зменшення відсотка

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

Крок 1

В осередку A19 , напишіть з 8% зменшенням .

крок 2

Натисніть Tab , потім в осередку B19 напишіть наступну формулу: = B17 * .92

Або ви можете написати формулу по-іншому: =B17 * 92%

Результат буде 61,057.

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

крок 3

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

Формула для зростання на 8% в осередку B18 буде =B17 + B17 * 0.08

крок 4

Формула для зменшення на 8% в осередку B19 буде =B17 – B17 * 0.08

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

4. Розрахунок величини відсотка

Тепер перейдемо до формули розрахунку величини відсотка. Що якщо ви хочете знати скільки складають ці 8%? Щоб зробити це, помножте загальну суму в B17 на 8 відсотків.

Крок 1

В осередку A20 напишіть 8% від кінцевої суми.

крок 2

Натисніть Tab , і в комірці B20 напишіть наступну формулу: =B17 * 0.08

Або ви можете записати цю формулу таким чином: =B17 * 8%

Результатом буде 5,309.

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

5. Внесення змін без редагування формул

Якщо ви хочете змінювати відсоток без редагування формул, то необхідно помістити його в окрему клітинку. Почнемо з назв рядків.

Крок 1

В осередку A22 напишіть Зміни. Натисніть Enter.

крок 2

В осередку A23 напишіть Найбільша сума. Натисніть Enter.

крок 3

В осередку A24 напишіть Найменша сума. Натисніть Enter.

На аркуші це має виглядати наступним чином:

Лист Excel з додатковими рядками

Тепер додамо відсотки і формули праворуч від заголовків.

крок 4

В осередку B22 напишіть 8% . Натисніть Enter .

крок 5

В осередку B23 напишіть наступну формулу, яка розрахує підсумкову суму з додаванням 8%: =B17 + B17 * B22

крок 6

В осередку B24 напишіть наступну формулу, яка розрахує підсумкову суму за мінусом 8%: =B17 * (100% – B22)

Якщо в осередку B22 ви напишіть 8%, то Excel автоматично отформатирует цей осередок у вигляді відсотків. Якщо ж ви напишіть .08 або 0.08, Excel збереже цей осередок як є. Ви завжди можете змінити формат комірки на формат у вигляді відсотків, натиснувши на стрічці кнопку Percent Style:

Кнопка Percent Style і її дію

Порада: Ви можете відформатувати числа у вигляді відсотків за допомогою комбінації клавіш Ctrl + Shift +% для Windows або Command + Shift +% для Mac.

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

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

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

= (Нове значення – старе значення) / нове значення

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

Крок 1

Щоб розрахувати перша зміна розміру відсотка, внесемо в осередок C5 наступну формулу для його розрахунку: =(B5-B4)/B5

крок 2

Excel відобразить результат як дріб, тому натисніть кнопку Percent Style на стрічці (або використовуйте вказане вище поєднання клавіш), щоб відобразити число в вигляді відсотка.

Кнопка Percent Style для відображення у вигляді відсотків

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

крок 3

Наведіть мишку треба точкою в нижньому правому куті комірки, в якій стоїть значення -7%.

Точка для автозаповнення в Excel

крок 4

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

Якщо ви не знайомі з функцією автозаповнення, прочитайте про техніку 3 в моїй статті про техніках роботи з Excel:

Як використовувати Excel, 12 технік для просунутих користувачів

крок 5

Виберіть клітинку B3 (c заголовком “Сума”).

крок 6

Наведіть курсор мишки на точку для автозаповнення і перетягніть її на одну клітинку вправо, в осередок С3. . Так ви продубліруете заголовок разом з його форматуванням

крок 7

В осередку С3 замініть заголовок на % Зміни .

Всі зміни розмірів відсотка підраховані

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

Останньою технікою на цьому ліcте буде розрахунок відсотка від загальної суми за кожен місяць. Уявіть цей відсоток як кругову діаграму, в якій кожен місяць становить один сектор, а сума всіх секторів становить 100%.

Неважливо, розраховуєте ви його за допомогою Excel або на папері, буде використовуватися наступна проста операція ділення:

Значення одного елемента / загальна сума

. яку треба представити у вигляді відсотка.

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

Крок 1

Виберіть осередок C3 і виконайте автозаповнення одного осередку, до D3 .

крок 2

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

крок 3

В осередку D4 напишіть цю формулу, тільки поки не натискайте Enter (Введення) : =B4/B17

крок 4

Перед натисканням клавіші введення, нам треба переконатися, що при автозаповнення НЕ буде помилок. Оскільки ми будемо автоматично заповнювати колонки вниз, дільник (зараз це B17), не повинен змінюватися. Адже якщо він зміниться, то всі розрахунки з лютого по грудень виявляться невірними.

Переконайтеся, що текстовий курсор все ще в осередку перед дільником “B17”.

крок 5

Натисніть F4 (на Mac – Fn + F4 ).

Так ви вставите знак долара ($) перед значенням колонки і рядки, і дільник буде мати наступний вигляд: $B $17. $B означає що колонка В не зміниться на колонку С і т.д., а $17 означає що номер рядка 17 не зміниться на рядок $18 і т.д.

крок 6

Переконайтеся, що формула в результаті виглядає наступним чином: =B4/$B$17.

Формула для відсотка від загальної суми

крок 7

Тепер можна натиснути клавішу Enter (Введення) і виконати автозаповнення.

Натисніть Ctrl + Enter (на Mac – Command + ↩) щоб ввести формулу не рухаючи курсор миші в осередок знизу.

крок 8

Натисніть кнопку Percent Style на стрічці або використовуйте поєднання клавіш Ctrl + Shift +% ( Command + Shift +%).

крок 9

Наведіть покажчик курсору миші над точкою для автозаповнення в осередку D4 .

крок 10

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

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

Заповнений відсоток від загальної суми

8. Процентне ранжування

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

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

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

Подивіться на другу закладку листа: Grades (Оцінки).

Процентне ранжування в Excel – лист з оцінками

Тут список з 35 середніх балів, відсортованих по зростанню. Перше, що ми хочемо дізнатися, це процентне ранжування для кожного середнього бала . Для цього ми будемо використовувати функцію = PERCENTRANK.INC. “INC” в цій функції означає “включаючи”, оскільки вона буде включати перший і останній бали в списку. Якщо ви хочете виключити перший і останній бали масиву, то використовуйте функцію = PERCENTRANK.EXC.

У функції два обов’язкових аргументу і виглядає вона таким чином: =PERCENTRANK.INC(array, entry)

• array (масив) – це група осередків, які складають список (в нашому випадку B3: B37 )
• entry (значення) – це будь-яке значення або осередок в списку

Крок 1

Виберіть першу осередок в списку C3 .

крок 2

Напишіть наступну формулу, але не натискайте клавішу Enter (Введення) : =PERCENTRANK.INC(B3:B37

крок 3

Цей діапазон повинен бути незмінним, щоб ми могли застосувати автозаповнення всіх осередків зверху вниз.

Тому натисніть клавішу F4 (на Mac – Fn + F4 ), щоб додати знак долара до значень.

Формула повинна виглядати наступним чином: =PERCENTRANK.INC($B$3:$B$37

крок 4

У кожному осередку в колонці С, ми побачимо процентне ранжування відповідного значення в колонці В

Натисніть на осередок B3 і закрийте дужки.

Формула в підсумку така: =PERCENTRANK.INC($B$3:$B$37,B3)

Функція процентного ранжирування

Тепер ми можемо внести і відформатувати значення.

крок 5

Якщо необхідно, знову виберіть осередок С3 .

крок 6

Виконайте подвійне клацання мишею на точці автозаповнення комірки С3. Це автоматично заповнить осередку колонки нижче.

крок 7

На стрічці, натисніть кнопку Percent Style , щоб відформатувати колонку у вигляді відсотків. В результаті ви повинні побачити наступне:

Заповнена та відформатована функція процентного ранжирування

Таким чином, якщо у вас хороші оцінки і ваш середній бал 3.98, то ви знаходитеся в 94 мп роцентах.

9. Обчислення процентиля.

Використовуючи функції PERCENTILE, ви можете розрахувати проценти. За допомогою цієї функції ви можете ввести розмір відсотка і отримати значення з масиву, яке відповідає цьому відсотку. Якщо точного значення в діапазоні немає, то Excel покаже те значення, яке “має” бути в списку.

У яких випадках це необхідно? Наприклад, якщо ви подаєте заявку на університетську програму, яка приймає тільки тих студентів, які відносяться до діапазону від 60 го процента. І вам треба дізнатися, який середній бал відповідає 60%. Подивившись на список, ми побачимо, що 3.22 – це 59%, а 3.25 – це 62%. Використовуючи функцію = PERCENTILE.INC ми дізнаємося точну відповідь.

Формула будується так: = PERCENTILE.INC (array, percent rank)

• array (масив) – це діапазон комірок, які складають список (як в попередньому прикладі, B3: B37)
• rank (ранг) – це відсоток (або десяткова дріб від 0 до 1 включно)

Також як і для функції процентного ранжирування, ви можете використовувати функцію = PERCENTILE.EXC, яка виключає перше і останнє значення масиву, але в нашому випадку це не потрібно.

Крок 1

Виберіть під списком осередок В39 .

крок 2

Вставте в неї наступну формулу: =PERCENTILE.INC(B3:B37,60%)

Так як нам не треба застосовувати автозаповнення, немає потреби робити масив незмінним.

крок 3

На стрічці натисніть один раз клавішу Decrease Decimal (Зменшити розрядність), щоб округлити число до двох десяткових розрядів після коми.

В результаті в даному масиві 60 м перцентилем буде середній бал 3.23. Тепер ви знаєте які оцінки потрібні для того, щоб бути прийнятим на програму.

Результат функції обчислення процентиль

висновок

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

1. дужки
2. Показник ступеня
3. множення
4. розподіл
5. додавання
6. віднімання

Я запам’ятовую цей порядок за допомогою мнемонічної фрази: Сказав Степан: Розум ора, Давайте Словимо Вора.

Як розрахувати фору

ПДФО = (нараховано з/п – ПСП) х 18 %
ВЗ = нарахована з/п х 1,5%.

Приклад

Працівникові (одинока мати, яка виховує двох неповнолітніх дітей) нараховані за повний відпрацьований місяць 4800 грн. Вона повідомила в заяві роботодавцю про те, що має право на підвищену 150% -ву податкову соціальну пільгу та надала відповідні документи. Отже, для самотньої матері розрахункова межа для нарахування ПСП становитиме 5880,00 грн. (2940,00 Х на 2), а податкова соціальна пільга буде складає 3153,50 грн. (1576,50 Х 2)

Період: ___ січень 2020
Начислена зарплата: 4800,00 грн.

НСЛ на дітей: 150%

Кількість дітей: 2
ЄСВ: всі категоріі платників (22%)

Розрахувати

ПСП – 3153,00 грн (1576,50 Х 2 дітей), граничний розмір дохода для отримання ПСП – 5880,00 грн. (2940,00 Х на 2)

Формула

ПДФО: (4800,00 – 3153,00) х 18% = 1647,00 х 18% = 296,46 грн.
ВЗ: 4800,00 х 1,5% = 72,00
Заробітна плата “на руки” = Зарахована з / п – ВЗ – ПДФО = 4800,00 – 296,46 – 72,00 = 4431,54 грн.
Нараховано ЄСВ на зарплату 4800,00 х 22% (ставка ЄСВ) = 1056,00

Для нарахування і розрахунку індексації заробітної плати скористайтесь довідником

Онлайн калькулятор зарплати

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

Онлайн калькулятор зп Україна 2020

Зустрічайте унікальний сервіс від Uteka: розраховує розміри всіх податків, які потрібно утримати із зарплати працівника, і видає розмір заробітної плати, який працівник отримує «на руки».

Калькулятор автоматично утримує і нараховує:

• податок на доходи фізичних осіб (ПДФО);
• військовий збір (ВС);
• єдиний соціальний внесок (ЄСВ).

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

З його допомогою ви легко і дуже швидко зможете розрахувати заробітну плату своїх співробітників.

Розрахунок зарплати по окладу онлайн калькулятор

Для того, щоб розрахувати зарплату за допомогою калькулятора потрібно ввести період і оклад працівника, вибрати ПСП в разі її використання, а також розмір ставки ЄСВ. Наш калькулятор розрахує розміри всіх податків, які необхідно утримати із зарплати та розмір заробітної плати, який працівник отримає «на руки».

Зарплата за неповний місяць онлайн калькулятор

Не обов’язково вибирати період – місяць. Ви можете вибрати будь-який період і вказати будь-який розмір доходу.

Калькулятор зарплати на руки за місяць

Наведемо приклади розрахунку заробітної плати.

Оклад працівника за вересень 2020 року склав 5000 грн. ПСП до його заробітної плати не застосовується. Розрахуємо податки з зарплати і зарплату «на руки».

• Вводимо період зарплати: з 01.09.2020 по 30.09.2020 р.
• Вводимо оклад – 5000 грн.
• Натискаємо кнопку: «Розрахувати».
• Дивимося «Результати».

Граничний розмір зарплати для права на НСЛ: 2940,00 грн

Розмір НДФЛ: 5000,00 х 18% = 900,00 грн

Розмір ВС: 5000,00 х 1,5% = 75,00 грн

Розмір зарплати «на руки»: 5000,00 – 900,00 – 75,00 = 4025,00 грн

ЕСВ з фактичного доходу: 5000,00 х 22% = 1100,00 грн.

Працівниця має 2 дітей до 18 років. Оклад працівниці 4000 грн. До її зарплати застосовується ПСП.

• Вводимо період зарплати: з 01.09.2020 по 30.09.2020 г.
• Вводимо оклад – 4000 грн.
• Натискаємо на кнопку «ПСП на себе 100%» і «ПСП на дітей» – вводимо кількість дітей -2.
• Натискаємо кнопку: «Розрахувати».
• Дивимося «Результати».

Розмір НСЛ: 1051,00 х 2 = 2102,00 грн

Граничний розмір зарплати для права на НСЛ: 2940,00 х 2 = 5880,00 грн

Розмір НДФЛ: (4000,00 – 2102,00) х 18 % = 341,64 грн

Розмір ВС: 4000,00 х 1,5% = 60,00 грн

Розмір зарплати «на руки»: 4000,00 – 341,64 – 60,00 =3598,36 грн.

ЕСВ з фактичного доходу:4000,00 х 22% = 880,00 грн

ЕСВ з мінімальної зарплати: 5000,00 х 22% = 1100,00 грн.

Інші калькулятори

Калькулятор зарплат: мінімум часу і ризиків

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

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

Калькулятор заробітної плати UTEKA виконує миттєві розрахунки з урахуванням податків і різного окладу. Це позбавляє від довгих чисельних викладок і не забирає ресурси у стратегічних HR-завдань.

Що являє собою калькулятор заробітної плати

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

Основні функції інструменту включають:

1. Розрахунок зп онлайн на основі введених даних.
2. Автоматичне нарахування податків (ПДФО) і страхових внесків (ЄСВ).
3. Виведення інформації про повну заробітну плату та її чистий розмір (на руки) для кожного працівника.

Сервіс розраховує дані на підставі актуальної інформації про внески. Це дозволяє отримувати максимально правдивий результат.

Навіщо потрібен калькулятор зарплат HR-фахівцю

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

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

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

Основна перевага онлайн-калькулятора UTEKA — забезпечення конфіденційності даних компанії. На стороні вебресурсу не зберігається ніяка інформація про співробітників і параметри їхньої оплати праці. Користувач вводить усі необхідні дані в інтерфейс калькулятора, проводиться розрахунок, а після оновлення сторінки результати скидаються. Сайт не зберігає введені цифри після закриття сесії користувача.

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

Які завдання вирішує калькулятор заробітної плати

Згідно зі статистичними даними, використання онлайн-калькуляторів зарплат допомагає оптимізувати витрати на персонал у середньому на 10-15%. Кадровики відзначають, що використання онлайн-калькуляторів заробітної плати дає їм змогу економити щонайменше 2 години щотижня на рутинних розрахунках. Інструмент дозволяє автоматизувати безліч рутинних завдань кадровика.

Розрахунок зарплати новоприйнятих співробітників

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

Коригування зарплати при зміні статусу співробітника

Актуально при виході співробітниці з декрету, підвищенні/зниженні на посаді або переведенні на неповну зайнятість. За допомогою калькулятора ПДФО кадровик може спрогнозувати ефект цих змін на заробітну плату і податкові відрахування.

Аналіз витрат на персонал за підрозділами

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

Розрахунок премій/бонусів співробітникам

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

Зручний інструмент завжди під рукою

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