Форматування нестандартного номера Excel - це одяг для даних у клітинах Excel. Ви можете одягати їх так, як вам хочеться. Все, що вам потрібно, це трохи знань про те, як працює нестандартний формат номера Excel.
За допомогою спеціального форматування чисел у Excel ви можете змінити спосіб відображення значень у клітинках, але в той же час зберегти початкове значення недоторканим.
Наприклад, я можу зробити так, щоб значення дати відображалося у різних форматах дати, тоді як базове значення (число, яке представляє дату) залишиться незмінним.
У цьому підручнику я висвітлю все про формат нестандартного числа в Excel, а також покажу вам кілька дійсно корисних прикладів, які можна використовувати у повсякденній роботі.
Тож почнемо!
Побудова формату користувацького формату Excel
Перш ніж я покажу вам її чудовість, дозвольте мені коротко пояснити, як форматування нестандартних чисел працює в Excel.
За задумом Excel може приймати в комірку такі чотири типи даних:
- Позитивне число
- від’ємне число
- 0
- Текстові рядки (це можуть включати рядки чистого тексту, а також буквено -цифрові рядки)
Для будь -якої клітинки на робочому аркуші в Excel можна вказати, як кожен із цих типів даних повинен відображатися у комірці.
Нижче наведено синтаксис, у якому можна вказати цей формат.
;;;
Зауважте, що всі вони розділені крапками з комою (;).
Все, що ви вводите в клітинку в Excel, потрапляє до будь -якої з цих чотирьох категорій, а отже, для неї можна вказати власний формат.
Якщо згадати лише:
- Один формат: Застосовується до всіх чотирьох розділів. Наприклад, якщо ви просто напишете Загальне, це буде застосовано до всіх чотирьох розділів.
- Два формати: Перший застосовується до додатних чисел і нулів, а другий - до від’ємних. Формат тексту за замовчуванням стає загальним.
- Три формати: Перший застосовується до додатних чисел, другий - до від’ємних, третій - до нуля, а текст зникає, оскільки для тексту нічого не вказано.
Якщо ви хочете дізнатися більше про форматування нестандартних чисел, я настійно рекомендую розділ Довідка Office.
Як це працює і де ввести ці формати, розглянемо далі в цьому посібнику. Наразі просто майте на увазі, що це синтаксис формату для будь -якої коміркиЯк працює нестандартний формат номера в Excel
Потім ви відкриваєте нову книгу Excel, за умовчанням усі клітинки у всіх аркушах цієї книги мають загальний формат, де:
- Позитивні числа відображаються як є (вирівняно ліворуч)
- Негативні числа відображаються зі знаком мінус (і вирівнюються ліворуч)
- Нуль відображається як 0 (і вирівнюється ліворуч)
- Текст відображається як є (вирівняно праворуч)
І якщо ви вводите дату, вона відображатиметься відповідно до ваших регіональних налаштувань (у форматах DD-MM-YYYY або MM-DD-YYYY).
Отже, за замовчуванням форматування номера комірки виглядає так:
Загальні; Загальні; Загальні; Загальні
Де кожен тип даних відформатований як Загальний (і дотримуйтесь правил, про які я згадував вище, залежно від того, чи це позитивне число, від’ємне число, 0 або текст)
Тепер налаштування за замовчуванням в порядку, але як змінити формат. Наприклад, якщо я хочу, щоб мої від’ємні числа відображалися червоним кольором або я хочу, щоб мої дати відображалися у певному форматі, як це зробити?
Використання випадаючого формату на стрічці
Деякі з часто використовуваних форматів чисел можна знайти на вкладці «Домашня сторінка» у групі «Число».
Це спадне меню, яке показує багато поширених форматів, які можна застосувати до комірки, просто вибравши комірку, а потім вибравши формат із цього списку.
Наприклад, якщо ви хочете перетворити числа у відсотки або перетворити дати у короткий формат дати або формат дати у довгій формі, то ви можете легко це зробити, скориставшись параметрами у випадаючому списку.
Використання діалогового вікна Форматування клітинок
Діалогові вікна форматування комірок, де ви отримуєте максимальну гнучкість у застосуванні форматів до клітинки в Excel.
У ньому вже є багато готових форматів, які ви можете використовувати, або якщо ви хочете створити власний формат власного номера, то ви також можете це зробити.
Ось як відкрити діалогове вікно Форматування клітинок;
- Натисніть вкладку Домашня сторінка
- У групі "Число" натисніть на піктограму запуску діалогового вікна (маленька нахилена стрілка в правому нижньому кутку групи).
Відкриється діалогове вікно Форматування клітинок. У ньому ви знайдете всі формати на вкладці "Номер" (разом із можливістю створення власних форматів комірок)
Ви також можете скористатися такою комбінацією клавіш, щоб відкрити діалогове вікно форматування комірок:
CONTROL + 1 (усі клавіші Control, а потім натисніть клавішу 1).
Якщо ви використовуєте Mac, ви можете використовувати CMD + 1
Тепер давайте мати пару практичних прикладів, коли ми створимо власні формати власних чисел.
Приклади використання користувацького форматування чисел у Excel
Тепер давайте наведемо пару практичних прикладів, коли ми створимо власні формати власних чисел.
Приховати значення клітинки (або сховати всі значення в діапазоні клітинок)
Іноді може виникнути необхідність приховати вміст комірки (не приховуючи рядка або стовпця, що містить клітинку).
Практичний випадок цього - коли ви створюєте інформаційну панель Excel, і на панелі інструментів ви використовуєте кілька значень, але не хочете, щоб користувачі бачили її. тож ви можете просто приховати ці значення
Припустимо, у вас є набір даних, як показано нижче, і ви хочете приховати всі числові значення:
Ось як це зробити за допомогою користувацького форматування в Excel:
- Виберіть клітинку або діапазон клітинок, для яких потрібно приховати вміст комірки
- Відкрийте діалогове вікно Форматування клітинок (комбінація клавіш Control + 1)
- На вкладці "Номер" натисніть "Спеціальне"
- Введіть у полі Тип такий формат:
;;;
Як це працює?
Формат чотирьох різних типів типів даних поділяється крапкою з комою у такому форматі:
;;;
У цій техніці ми видалили формат і вказали лише крапку з комою, що вказує на те, що ми не хочемо форматування клітинок. Це вказівка для Excel, щоб приховати будь -який вміст, що є у клітинці.
Зауважте, що хоча це приховує вміст комірки, воно все ще є. тому, якщо у вас є приховані цифри, ви все ще можете використовувати їх у розрахунках (що робить це чудовим прийомом для інформаційних панелей Excel та звітів).
Відображення негативних чисел червоним кольором
За замовчуванням від’ємні числа відображаються зі знаком мінус (або в дужках у деяких регіональних налаштуваннях).
Ви можете використовувати користувацьке форматування чисел, щоб змінити колір мінус -чисел і зробити їх червоними.
Припустимо, у вас є набір даних, як показано нижче, і ви хочете виділити всі негативні значення червоним кольором.
Нижче наведено кроки для цього.
- Виберіть діапазон клітинок, де потрібно відображати від’ємні числа червоним кольором
- Відкрийте діалогове вікно Форматування клітинок (комбінація клавіш Control + 1)
- На вкладці "Номер" натисніть "Спеціальне"
- Введіть у полі Тип такий формат:
Загальний; [Червоний] -Загальний
Наведений вище формат змусить клітини з негативними значеннями відображатися червоним кольором.
Як це працює?
Коли ви вказуєте лише два формати як формат користувацького числа, перший вважається для позитивних чисел і 0, а другий - для негативних чисел (наступне значення за замовчуванням стає загальним форматом).
У наведеному вище форматі ми вказали колір, який ми хочемо прийняти у квадратних дужках.
Отже, хоча негативні числа приймають загальний формат, вони відображаються червоним кольором зі знаком мінус.
Якщо ви хочете, щоб негативні числа зупинилися червоним кольором у дужках (замість знака мінус), ви можете використовувати такий формат:
Загальний; [Червоний] (Загальний)
Додати текст до чисел (наприклад, мільйони/мільярди)
Дивовижною особливістю форматування користувацьких чисел є те, що ви можете додати до числа префікс або текст суфікса, зберігаючи при цьому оригінальний номер у комірці.
Наприклад, я можу мати "100" як "100 мільйонів доларів", зберігаючи при цьому початкове значення у комірці, щоб я міг використовувати його у розрахунках.
Припустимо, у вас є набір даних, як показано нижче, і ви хочете показати ці цифри зі знаком долара та словом мільйон перед ним.
Нижче наведено кроки для цього.
- Виберіть набір даних, де потрібно додати текст до чисел
- Відкрийте діалогове вікно Форматування клітинок (комбінація клавіш Control + 1)
- На вкладці "Номер" натисніть "Спеціальне"
- Введіть у полі Тип такий формат:
Загальний долар "Мільйон"
Зауважте, що цей нестандартний формат чисел вплине лише на цифри. Якщо ви введете текст у тій самій комірці, він буде виглядати як є.
Як це працює?
У цьому прикладі до загального формату я додав слово мільйон у подвійних лапках. все, що я додаю у подвійні лапки до формату, відображається так, як є у комірці.
Я також додав знак долара перед загальним форматом, щоб усі мої цифри також показували знак долара перед номером.
Примітка: Якщо вам цікаво, чому знак долара не в подвійних лапках, а слово «мільйон» у подвійних лапках, це тому, що Excel розпізнає долар як символ, який часто використовується у форматуванні спеціальних чисел, і не вимагає від вас додати до нього подвійні лапки. Ви можете продовжити і додати подвійні лапки до знака долара, але коли ви закриєте діалогове вікно форматування комірок і відкриєте його знову, ви помітите, що Excel видалив його
Маскувати цифри та текст
За допомогою користувацького форматування чисел можна оцінити максимум дві умови, і на основі цих умов ви можете застосувати формат до комірки.
Наприклад, припустимо, що у вас є список балів для деяких студентів, ви хочете показати текст «Прохід» або «Невдача» на основі балів (де будь -який бал, менший за 35, повинен показувати «Невдалий», інакше він повинен показувати «Пройшов»).
Нижче наведено кроки для відображення балів як прохідних або невдалих замість значення у форматі користувацького числа:
- Виберіть набір даних, де у вас є бали
- Відкрийте діалогове вікно Форматування клітинок (комбінація клавіш Control + 1)
- На вкладці "Номер" натисніть "Спеціальне"
- Введіть у полі Тип такий формат:
[<35] "Fail"; "Pass"
Нижче описано, як будуть виглядати ваші дані після застосування вищевказаного формату.
Зауважте, що це не змінює значення в осередках. Клітинка продовжує мати оцінки в числових значеннях під час відображення "проходження" або "невдачі" залежно від значення комірки.
Як це працює?
У цьому прикладі умову потрібно вказати у квадратних дужках. форматування користувацького числа потім оцінює значення в комірці і застосовує відповідний формат.
Отже, якщо значення в комірці менше 35, то число приховується, і замість цього відображається текст «Невдало», додаючи всі інші випадки, «Прохід».
Аналогічно, якщо ви хочете перетворити діапазон клітинок, які мають 0 і 1, у TRUE та FALSE, ви можете скористатися наведеним нижче форматом:
[= 0] "FALSE"; [= 1] "TRUE"
Пам’ятайте, що у форматуванні спеціального номера можна використовувати лише дві умови. Якщо ви спробуєте використати більше двох умов, це відобразиться у вашому запиті, який повідомить вам, що він не може прийняти цей формат.
Приховати текст, але відобразити номери
Іноді, коли ви завантажуєте свої дані з баз даних, оскільки вони є порожніми, заповнюються такими значеннями, як Недоступно або NA або -
За допомогою спеціального форматування чисел ви можете приховати всі текстові значення, зберігаючи цифри такими, як вони є. це також гарантує, що ваші вихідні дані недоторкані (оскільки ви не видаляєте текстове значення, ви просто просто приховуєте його)
Припустимо, у вас є набір даних, як показано нижче, де ви хочете приховати всі текстові значення:
Нижче наведено кроки для цього.
- Виберіть набір даних, у якому у вас є дані
- Відкрийте діалогове вікно Форматування клітинок (комбінація клавіш Control + 1)
- На вкладці "Номер" натисніть "Спеціальне"
- Введіть у полі Тип такий формат:
Загальні; -Загальні; Загальні;
Зауважте, що я використовував загальний формат для чисел. Ви можете вибрати будь -який формат (наприклад, 0, 0. #, #0.0%). Також зверніть увагу, що перед другим загальним форматом є знак мінус (-), оскільки він представляє формат від'ємних чисел.
Як це працює?
У цьому прикладі, оскільки ми хотіли приховати всі текстові значення, ми просто не вказали його формат.
А оскільки ми вказали формат для інших трьох типів даних (позитивне число, від’ємне число та нуль), Excel зрозумів, що ми цілеспрямовано залишили формат текстового значення порожнім, і він не повинен нічого показувати, якщо у клітинці є текст рядок.
Відображення цифр у відсотках (%)
За допомогою користувацького форматування чисел ви також можете змінити цифри та відобразити їх у відсотках.
Припустимо, у вас є набір даних, як показано нижче, і ви хочете перетворити ці числа, щоб показати еквівалентне значення у відсотках:
Нижче наведено кроки для цього.
- Виберіть клітинки, де у вас є числа (які потрібно перетворити у відсотки)
- Відкрийте діалогове вікно Форматування клітинок (комбінація клавіш Control + 1)
- На вкладці "Номер" натисніть "Спеціальне"
- Введіть у полі Тип такий формат:
0%
Це змінить цифри на їх відсотки. Наприклад, вона змінюється від 0,11 до 11%.
Якщо все, що ви хочете зробити, - це перетворити число в його еквівалентне значення у відсотках, кращим способом цього буде використання параметра відсотка у діалоговому вікні форматування комірок.
Користувацьке форматування може бути корисним, коли ви хочете більше контролювати, як ви хочете показувати значення відсотка.
Наприклад, якщо ви хочете показати якийсь додатковий текст із відсотками, все, що вам потрібно, щоб показати негативні відсотки червоним кольором, тоді краще скористатися спеціальним форматуванням
Відображення цифр в різних одиницях (мільйони як мільярди або грами як кілограми)
Якщо ви працюєте з великими числами, можливо, ви захочете показати їх в іншому блоці. наприклад, якщо у вас є дані про продажі деяких компаній, ви можете показати продажі в мільйонах або мільярдах (зберігаючи при цьому первісну вартість продажу в комірках)
На щастя, ви можете легко це зробити за допомогою користувацького форматування номерів.
Припустимо, у вас є набір даних, як показано нижче, і ви хочете показати ці значення продажів у мільйонах.
Нижче наведено кроки для цього.
- Виділіть клітинки, де у вас є цифри
- Відкрийте діалогове вікно Форматування клітинок (комбінація клавіш Control + 1)
- На вкладці "Номер" натисніть "Спеціальне"
- Введіть у полі Тип такий формат:
0.0,,
Вищевикладене дасть вам результат, як показано нижче (значення в мільйонах однією цифрою після коми):
Як це працює?
У наведеному вище форматі нуль перед десятковим числом вказує на те, що Excel повинен показувати все число (саме так, як воно є у комірці), а 0 після десяткового вказує на те, що він повинен показувати лише одну значущу цифру після коми.
Отже, 0,0 завжди буде числом з однією цифрою після коми.
Коли ви додаєте кому після 0,0, вона видалить останні трицифрові числа. Наприклад, 123456 стане 123,4 (останні три цифри видаляються, а оскільки він повинен показувати 1 цифру після коми, він показує 4)
Якщо ви подумаєте про це, це подібно до поділу числа на 1000. Тому, якщо ви хочете показати число в одиниці «мільйони», вам потрібно додати 2 коми, щоб вони згладили останні 6 цифр.
А якщо ви також хочете показати слово мільйон або алфавіт М після цифри, скористайтеся наведеним нижче форматом
0,0 ,, "М"
