Форматирането на потребителски номера в 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"
По -долу е как ще изглеждат вашите данни, след като сте приложили горния формат.
Обърнете внимание, че това не променя стойността в клетките. Клетката продължава да има резултатите в числови стойности, докато показва „Pass“ или „Fail“ в зависимост от стойността на клетката.
Как работи това?
В този пример условието трябва да бъде посочено в квадратните скоби. форматирането на персонализирано число след това оценява стойността в клетката и съответно прилага формата.
Така че, ако стойността в клетката е по -малка от 35, тогава номерът е скрит и вместо това се показва текстът „Fail“, като се добавят всички останали случаи, се показва „Pass“.
По същия начин, ако искате да конвертирате диапазон от клетки, които имат 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 ,, "M"
