Відображення відсотків в Excel

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

У Microsoft Excel відображення значень у вигляді відсотків робиться дуже просто. Для цього потрібно виділити одну або кілька осередків і натиснути кнопку Percent Style (Процентний формат) в розділі Number (Число) вкладки Home (Головна):

Ще швидше це можна зробити, натиснувши поєднання клавіш Ctrl + Shift +%. Excel буде нагадувати Вам цю комбінацію кожного разу, коли Ви наводите курсор миші на кнопку Percent Style (Процентний формат).

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

Форматування існуючих значень як відсотків

Коли Ви застосовуєте процентний формат до осередків, які вже містять числові значення, Excel примножує ці значення на 100 і додає в кінці знак відсотка (%). З точки зору Excel, це правильно, так як 1% це по своїй суті одна сота частина.

Однак іноді це призводить до несподіваних результатів. Наприклад, якщо в осередку A1 записано число 20 і Ви застосуєте до цього вічка процентний формат, то в результаті Ви отримаєте 2000%, а не 20% як Ви, ймовірно, хотіли.

Як запобігти помилці:

  • Якщо в осередку Вашої таблиці записані числа в звичайному числовому форматі, а Вам потрібно перетворити їх в процентний, попередньо розділіть ці числа на 100. Наприклад, якщо Ваші вихідні дані записані в стовпці A, Ви можете в клітинку B2 ввести формулу = A2 / 100 і скопіювати її в усі необхідні комірки стовпчика B. Далі виділіть весь стовпець B і застосуєте до нього процентний формат. Результат повинен бути приблизно такий:Далі Ви можете замінити формули в стовпці B значеннями, потім скопіювати їх в стовпець A і видалити стовпець B, якщо він Вам більше не потрібний.
  • Якщо Вам потрібно перевести в формат відсотків тільки деякі з значень, Ви можете ввести їх вручну, для цього треба розділити число на 100 і записати його у вигляді десяткового дробу. Наприклад, щоб отримати значення 28% в осередку A2 (дивіться малюнок вище), введіть число 0.28, а потім застосуйте до неї процентний формат.

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

Ми побачили, як змінюється відображення вже існуючих даних в таблиці Microsoft Excel при зміні простого числового формату на процентний. Але що вийде, якщо спочатку застосувати до осередку процентний формат, а потім ввести в неї число вручну? Ось тут Excel може повести себе по-різному.

  • Будь-яке число, що дорівнює або більше 1 буде просто записано зі знаком%. Наприклад, число 2 буде записано як 2%; 20 – як 20%; 2,1 – як 2,1% і так далі.
  • Числа менше 1, записані без 0 зліва від коми, будуть помножені на 100. Наприклад, якщо Ви введете ,2 в клітинку з процентним форматуванням, то в результаті побачите значення 20%. Однак, якщо Ви наберете на клавіатурі 0,2 в тій же комірці, то значення буде записано як 0,2%.

Відображення чисел як відсотків відразу при введенні

Якщо Ви вводите в клітинку число 20% (зі знаком відсотка), Excel зрозуміє, що Ви хочете записати значення в процентах і автоматично змінить формат комірки.

Важливе зауваження!

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

Іншими словами, 20% зберігається як 0,2; 2% зберігається як 0,02 і так далі. Коли проводяться різні обчислення, Excel використовує саме ці значення, тобто десяткові дроби. Пам’ятайте про це, коли складаєте формули з посиланнями на комірки з відсотками.

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

  1. Клацніть по ній правою кнопкою миші і з контекстного меню виберіть Format Cells (Формат ячеек) або натисніть комбінацію Ctrl + 1.
  2. У діалоговому вікні Format Cells (Формат ячеек) погляньте в область Sample (Зразок) на вкладці Number (Число) в категорії General (Загальний).

Хитрощі при відображенні відсотків в Excel

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

1. Задайте відображення потрібної кількості десяткових знаків

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

Щоб побачити реальне, а не округлене значення, Вам потрібно збільшити число десяткових знаків, яке Excel повинен показати. Для цього:

  1. Відкрийте діалогове вікно Format Cells (Формат ячеек), скориставшись контекстним меню, або натисніть комбінацію клавіш Ctrl + 1.
  2. Оберіть категорію Percentage (Процентний) і встановіть число десяткових знаків, що відображаються в осередку, за своїм бажанням.
  3. Коли все буде готово – натисніть ОК, щоб зміни вступили в силу.

2. Виділіть негативні значення за допомогою форматування

Якщо Ви хочете, щоб негативні значення відображалися по-іншому, наприклад, червоним шрифтом, можете налаштувати особливий числовий формат. Відкрийте ще раз діалогове вікно Format Cells (Формат ячеек) і зайдіть на вкладку Number (Число). Оберіть категорію Custom (Все Формати) і введіть в поле Type (Тип) одну з наступних рядків:

  • 00%; [Red] -0.00% або 00%; [Червоний] -0,00% – виводити негативні процентні значення червоним кольором і показувати 2 знака після коми.
  • 0%; [Red] -0% або 0%; [Красний] -0% – виводити негативні процентні значення червоним кольором і не показувати значення після коми.

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

3. Форматування негативних значень відсотків в Excel за допомогою умовного форматування

У порівнянні з попереднім способом, умовне форматування в Excel це більш гнучкий метод, який дозволяє задати будь-який формат для осередку, яка містить від’ємне значення відсотка.

Найпростіший спосіб створити правило умовного форматування – зайти в меню Conditional formatting > Highlight cells rules > Less than (Умовне форматування > Правила виділення осередків > Менше …) і ввести 0 в поле Format cells that are LESS THAN (Форматувати осередки, які МЕНШЕ)

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

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

Залишайтеся з нами і приємного Вам читання!

Ссылка на основную публикацию