Unlimited Powerpoint templates, graphics, videos & courses! Unlimited asset downloads! From $16.50/m
Advertisement
  1. Business
  2. Microsoft Excel
Business

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

by
Difficulty:IntermediateLength:MediumLanguages:

Ukrainian (українська мова) translation by Valya (you can also view the original English article)

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

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

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

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

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

деморолик

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

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

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

Excel percentages starting screen
Дані листа з відсотками

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

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

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

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

Крок 1

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

крок 2

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

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

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

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

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

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

Крок 1

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

крок 2

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

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

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

Finding 8 decrease
Розрахунок зменшення відсотка в 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.

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

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

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

Крок 1

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

крок 2

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

крок 3

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

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

Sheet with additional row titles
Лист 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 button and its effect
Кнопка Percent Style і її дію

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

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

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

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

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

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

Крок 1

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

крок 2

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

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

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

крок 3

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

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

крок 4

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

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

крок 5

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

крок 6

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

крок 7

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

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

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.

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

крок 7

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

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

крок 8

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

крок 9

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

крок 10

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

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

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

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

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

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

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

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

Percent ranking - second worksheet
Процентне ранжування в 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)

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

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

крок 5

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

крок 6

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

крок 7

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

Percentrank function result filled and formatted
Заповнена та відформатована функція процентного ранжирування

Таким чином, якщо у вас хороші оцінки і ваш середній бал 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. Тепер ви знаєте які оцінки потрібні для того, щоб бути прийнятим на програму.

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

висновок

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

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

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

Advertisement
Advertisement
Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.