Тази статия разкрива скрити тайни за форматирането на числа в Excel. Как можете да съхранявате числа в Excel, но да добавяте думи до числата. Прочетете за още страхотни съвети относно форматирането на числа.
Excel е невероятно да съхранява един номер и да представя друг номер. Изберете която и да е клетка и изберете Формат на валута. Excel добавя знак за долар и запетая и представя числото, закръглено до два знака след десетичната запетая. На фигурата по-долу клетка D2 всъщност съдържа 6,42452514. За щастие, вграденият формат на персонализирано число представя резултатите в лесен за четене формат.
Кодът за персонализиран номер в D2 е $ #, ## 0.00. В този код 0 са задължителни цифри. Всички # са незадължителни цифри.
Кодовете за форматиране обаче могат да бъдат много по-сложни. Кодът по-горе има един формат. Този формат се прилага към всяка стойност в клетката. Ако предоставите код с два формата, първият формат е за неотрицателни числа, а вторият формат е за отрицателни числа. Разделяте форматите с точка и запетая. Ако предоставите код с три формата, първият е за положителен, след това отрицателен, след това нулев. Ако предоставите код с четири формата, те се използват за положителни, отрицателни, нулеви и текстови.
Дори ако използвате вграден формат, можете да отидете на Форматиране на клетки, Число, Потребителски и да видите кода, използван за генериране на този формат. Ето кода за счетоводния формат:
За да създадете свой собствен персонализиран формат, отидете на Форматиране на клетки, Число, Персонализиран и въведете кода в полето Тип. Разгледайте примера в полето Пример, за да се уверите, че всичко изглежда правилно.
В следващия пример се използват три зони. Текстът в кавички се добавя към числовия формат, за да персонализирате съобщението.
Ако създадете зона за нула, но не поставите нищо там, ще скриете всички нулеви стойности. Следният код използва цветови кодове за положителни и отрицателни. Кодът завършва с точка и запетая, създавайки зона за нулеви стойности. Но тъй като зоната е празна, нулевите стойности не се показват.
Можете да удължите това, като направите всички зони празни. Код на персонализиран формат ;;; ще скрие стойности в дисплея и разпечатката. Все пак ще можете да видите стойностите в лентата с формули. Ако скриете стойности, като направите шрифта бял, ;;; ще останат скрити, дори ако хората променят цвета на запълване. Следващата фигура включва някои интересни трикове за форматиране.
В B2 и B3, ако поставите ** преди цифровия код, той ще се запълни вляво от номера със звездички, както биха направили старите машини за записване на чекове. Но няма нищо, което да казва, че трябва да използвате звездички. Каквото и да сложите след първата звездичка, се повтаря, за да запълни пространството. Ред 3 използва *! да повтаря удивителен знак.
В B4 и B5 всяка запетая, която поставите след крайната нула, ще раздели числото на 1000. Кодът 0, K показва цифри в хиляди, с K след това. Ако искате да покажете милиони, използвайте две запетаи. Кодът „M“ трябва да бъде поставен в кавички, тъй като M вече означава месеци.
В B6 поставете строго съобщение в четвъртата зона, за да предупредите всеки, който въвежда данни, че искате номер в клетката. Ако случайно въведат текст, съобщението се появява.
В B7 до B9 нормалните зони Положителни, Отрицателни и Нула се заменят от условия, които поставяте в квадратни скоби. Номерата под 70 са червени. Номерата над 90 са сини. Всичко останало е черно.
В B10 тези странни _ (символите в счетоводния формат казват на Excel да остави толкова място, колкото ще отнеме лявата скоба. Оказва се, че долната черта, последвана от всеки символ, ще остави толкова празно пространство, колкото този знак. В B10, кодът съдържа 4 нули. Но има различно количество пространство между всяка. Пространството между 1 и 2 е ширината на 2 WW знака. Пространството между 2 и 3 е ширината на N. Пространството между 3 и 4 е ширината на малка буква i.
Следващата фигура показва различни кодове за форматиране на дати.
Имайте предвид, че форматът mmmmm в ред 8 е полезен за създаване на етикети на диаграмата JFMAMJJASOND.
Благодаря на Дейв Бейлис, Брад Едгар, Майк Гирвин и @best_excel, че предложиха тази функция.
Гледам видео
- Форматирането на числата е фасада: Excel съхранява едно, показва ни друго.
- Например: използвайте намаляващи десетични числа, за да покажете закръглена версия на номера.
- Excel все още съхранява всички десетични знаци, но опростява дисплея.
- В падащото меню на началния раздел има 11 цифрови формата.
- Щракнете върху диалоговия панел за стартиране, за да стигнете до диалоговия прозорец Форматиране на клетки
- Днес говорим за кодовете за форматиране в групата Custom.
- Днес нямам всички познания тук, така че ако имате по-добри съвети, поставете ги в коментарите в YouTube.
- Нула в кода на формата казва, че Excel трябва да показва цифрата.
- Знакът # казва, че може да покаже цифрата, ако има достатъчно точност, но не е необходимо, когато няма.
- A? ще остави място за цифрата, за да запази десетичната запетая подредена.
- Може да има 1, 2, 3 или 4 формата, разделени с точка и запетая.
- Когато има 2 формата, първият е за нула и нагоре. Вторият е за отрицателен.
- Когато има 3 формата, третият ако е за нула.
- Четвъртият формат е за текст.
- Използване на числовия формат за показване на текст с число или за показване на текст вместо с номер.
- Посочване на цвят за зона.
- За да промените (Цвят 5), посетете опциите на Excel.
- * x, за да повторите x до числата
- , в края да се раздели на 1000
- ,, разделя на милиони
- Използване на условия в зоната
- _x ще остави място с размер "x"
- m mm mmm mmmm mmmmm
- d dd ddd dddd
- (h): mm
- Благодаря на Дейв Бейлис, Брад Едгар, Майк Гирвин и @best_excel, че предложиха тази функция.
Видео транскрипция
Научете Excel от подкаст, епизод 2044 - Форматиране като фасада!
Добре, подкастирам всичките си съвети в тази книга, щракнете върху „i“ в горния десен ъгъл, за да стигнете до плейлиста!
Днес говорим за персонализирано форматиране на числа, така че имам Количество, Приходи и показваме цял куп десетични знаци тук. И знаете ли, ако не искаме да играем всички тези десетични знаци и не искаме да използваме функцията ROUND, просто идвам тук и използвам Decrease Decimal. не променя номера, който всъщност се съхранява зад кулисите, а просто показва начина, по който Excel ни показва номера. Добре и така крада термина на Майк Гирвин, че използваме форматиране като фасада, нали, има цял куп десетични знаци, съхранявани в тази клетка, но ние използваме персонализирани числови формати, за да покажем това. И аз искам да отида отвъд 11-те формата тук и дори да щракна върху този стартер за диалогов прозорец, отвъд повечето неща, които са тук, и искам да говоря за Custom.Персонализираните цифрови формати ни дават света, нали?
И така, тук има цял куп персонализирани цифрови формати, които можете да въведете в това поле и резултатите се показват вляво. Така че просто Общи, получавате всички десетични знаци, но тук нулите преди десетичния знак принуждават нулите да се появяват. Така че, ако числото <100, ще получа водещо 0, понякога това е полезно за номера на части, нули след десетичните знаци принуждават числата да се появяват. Хаштагът или знакът за паунд (#) преди десетичната запетая не са задължителни, добре, така че може да е там, не е задължително да е там. Добре, в този случай имаме числото 6, но тъй като имаме пет нули, това ни дава водещи нули. Сега, ако трябва да VLOOKUP в част от текст, който има 00006, това няма да е съвпадение, но ако просто трябва да изглеждате правилно, ще работи. Добре, ето някои интересни неща тук,много използваме знаци за паунд или хаштагове, много „хеш-знаци“. В САЩ ние наричаме това знак за паунд и разбирам, че в Англия това не е знак за паунд, £ е вашата валута, която наричате хеш. Добре, извинявайте, аз го наричам цял живот цял живот и ми е трудно да започна да извиквам хеш, но ще работя върху това.
Добре, така че тук долу .000 казва, че ще покажете нулите независимо дали има прецизност или не и аз промених тези числа на 6.42 и 6.4. Сега това е интересно, това е, което никога не съм познавал, ако сложите? вместо 0, той казва, че ще покаже точността, ако е там, но ако не, не го показвайте, но не позволявайте на нещата да се стичат вдясно. Така че тук, нека променим тези числа, 6.42 и 6.4, добре, вижте тук, десетичните знаци не остават подредени. Ако искате десетичните знаци да останат подредени, използвайте въпросителните знаци, ако искате да принудите нулите да бъдат там, използвайте нулите.
Хей, докато минаваме тук, има МНОГО подробности в подробности, но аз нямам ъгъла на знанията тук. Ако видите, че пропускам някакъв страхотен трик за форматиране на числа, моля, оставете го в коментарите в YouTube. Добре сега, ето следващата концепция, можем да покажем 1, 2, 3 или 4 числови формата в полето Тип, ако показвате само 1, това е просто номерът, който се използва за всичко, добре. Но ако покажете 2 и те са разделени с точка и запетая, тогава първият е за числа> = 0, а вторият е за числа <0. Така че тук съм използвал два кода, +0; -0, и това означава, че отрицателните числа се показват с минус, но положителните числа се показват с плюс, но тъй като не съм посочил какво се случва за 0 , показва се и с плюс.
Така че можем да добавим 3-та зона: положителна, отрицателна, нула, добре, така че +0; -0; 0, добре, така че да обработва нули. Може дори да има 4-ти код на това какво да се прави, ако някой въведе текст. Добре сега, следваща концепция, в персонализиран формат на числа, в допълнение към нулите, можете да поставите текст в кавички! Добре, така че нека да разгледаме формата на персонализирания номер тук, за положителни числа, думите „Моля, възстановете“ и след това числото, за отрицателно „Кредитно салдо на„ номера и „(Не плащайте)“. А за 0 дори не показвам номера, а просто показвам „Без баланс“, добре. Така че всъщност можете да използвате това, за да замените числото, това е -1, 0 и 1, и това са формули, за да получите формулата е просто да копирате това надолу. Всички отрицателни числа ще казват „Loser!“ или „Надолу“ или думата „Минус“ и числото, така че поставяме -5, все още се казва „Губещ!“ или „Надолу“ или „Минус 5“.За 0 можем да кажем „Нула“ или да кажем „Плосък“ или да кажем „0“, а след това за положително число, нека го поставим в 55, „Победител!“, „Нагоре“ или „Положителен 55“, добре, така че всъщност можете да добавяте думи. И какво е готино, така че това са 55, 55, 55, ако бих могъл да обобщя това, те наистина са числа и това наистина ще работи, както обикновено SUM няма да работи с текст, но това всъщност ми дава отговор . И така, ако искате да запазите числа там, но да показвате думи в клетките, това е чудесен трик.не работи с текст, но това всъщност ми дава отговор. И така, ако искате да запазите числа там, но да показвате думи в клетките, това е чудесен трик.не работи с текст, но това всъщност ми дава отговор. И така, ако искате да запазите числа там, но да показвате думи в клетките, това е чудесен трик.
Добре сега, това са знаците, които могат да се използват в персонализиран формат на числа без кавички, така че ако случайно се нуждаете само от знак + или -, ето защо не е нужно да го поставяте в кавички. И има някои допълнения към това, като след като видях този списък, аз съм като „Добре, нека видим с какво можем да се объркаме.“ Всичко това са числата 1 2 3 4, така че ако използвах персонализиран формат на число на ((число> &, всичко това ще се покаже. Ако искам да разделя цифрите с тирета, това е персонализиран формат на числата, за да получа това да работи. И всичко това е реално, така че 7854, вижте всичко се променя, това са всички формули, слизащи тук. Така че мога да прецакам с персонализирания формат на числата и да сложа символи преди него, да сложа символи след него. не в списъка, за който знам, че работи е K, имаш право да поставиш K в края и това 'не е в кавички. Но М, тъй като М означава месеци или минути, ще трябва да го поставите в кавички или да сложите това M, за да се появи M в края.
Добре, следваща концепция, във всяка зона ви е позволено да посочите цвят, нали, така че тук горе положителните числа са зелени, а отрицателните числа са сини. Ctrl + 1, за да стигнете до Форматиране на клетки, преди персонализирания формат на числата, в първата зона, положителната зона, казах зелено, а за отрицателните числа казах синьо. Какъв цвят имаме право да използваме там? Връща се в дните на 8 цвята, така черен, зелен, бял, син, пурпурен, жълт, циан и червен. Но благодарение на Майк Александър ви е позволено да използвате 56 други цвята, Цвят 1, Цвят 2, трябва да е в (), като това, това са 56-те цвята. Ако трябва да промените тези, сега ставаме наистина дълбоки, сега влизаме в територията на дете Чарли! Опции на Excel, Запазване и след това щракнете върху Цветове, добре и харесайте, ако исках да променя този оранжев на нещо друго, може би лилавото, щракнете върху OK, щракнете върхущракнете OK и един от тях, не мога да разбера кой е, би се променил на този цвят. Още един страхотен трик, какво ще стане, ако искате да скриете всички числа? Така че имате някои тайни тук и не искате никой да ги вижда. Ctrl + 1, поставете персонализиран формат на числото, казвам „Не показвайте нищо за положително, нищо за отрицателно, нищо за 0“ щракнете върху OK и всичко е скрито. Разбира се, те все още могат да го видят тук в лентата с формули, така че не е перфектно, добре, ето някои други символи за форматиране на клиенти.нищо за отрицателно, нищо за 0 ”щракнете върху OK и всичко е скрито. Разбира се, те все още могат да го видят тук в лентата с формули, така че не е перфектно, добре, ето някои други символи за форматиране на клиенти.нищо за отрицателно, нищо за 0 ”щракнете върху OK и всичко е скрито. Разбира се, те все още могат да го видят тук в лентата с формули, така че не е перфектно, добре, ето някои други символи за форматиране на клиенти.
Звездичка казва „Показване на следващия знак като чек-писател“. Така че тези ** казват „Знаеш ли, запълни зоната до числото със звездички“, но можеш да промениш това, можеш да направиш този герой всичко, което пожелаеш! Така *! ще постави всички удивителни знаци там, нека променим това, Ctrl + 1, ще го променим на * X и ще получа всички X до този момент. Така че можете да създадете ефект на чек-писателя, но не е задължително да е със звездичка, можете да поставите там каквото искате. Поставете запетая в края, така че това е 123456, персонализиран формат на числото със запетая след нулата казва „Покажете това в хиляди, разделено на 1000“, аз поставям, K там за хиляди. Когато искате да го направите в милиони, така че има нашата 0.0 ,, всяка запетая казва „Покажи, но го раздели на още 1000“. Това се дели на милион,поставете „M“, за да покажете милиони. Знам, че някои хора използват ММ там, сестра ми Алис (?), Която работи за Procter & Gamble, „ММ“ трябва да е в кавички.
Сега тук, това е едно, при което налагаме число, ако някой въведе нещо, което не е число, съобщението в 4-та зона ще го замени. Така че, ако поставим числото 1, е чудесно, поставяме -1, чудесно е, ако поставим „Здравей”, той ни крещи: „Въведете число! Добре, отново, това са формули, които изтеглят каквото и да напиша в A към B. Можете да използвате условия, добре, така че положителните, отрицателните, нулевите, можете да ги замените. Така че ще кажа червено, ако е 90 и можете да имате само две условия и след това 3-та зона за неща, които не са 90, старо, старомодно условно форматиране. Друг, ето още един, това е просто лудост, поставя долна черта и тогава всеки герой оставя достатъчно място, за да се побере на този герой.Виждате това много с _ (за да поддържате положителните числа подредени с отрицателни числа, но наистина можете да прецакате с Excel. Така че W е най-широкият знак, така че това е поставянето на 1-ва цифра и след това достатъчно място за 2 W и след това 2-ра цифра и достатъчно място за N, и след това 3-та цифра и достатъчно място за I, и след това накрая, 4-та цифра. Това полезно ли е за нещо? Боже, не знам, но това е вид от wild, че можете да наложите различно разстояние между цифрите, като използвате долните черти.но е доста диво, че можете да налагате различни разстояния между цифрите, като използвате долните черти.но е доста диво, че можете да налагате различни разстояния между цифрите, като използвате долните черти.
Добре, дати, m / d / yy, получавате 2-цифрена година, разширявате до yyyy, получавате 4-цифрена година, поставяте mm, тя налага водещо 0 преди 7, dd налага водещо 0 преди 3 Ако искате да използвате някакъв старомоден формат за дата на кобалт, не поставяйте никакви /, а само всички знаци, ГГГГММДД. mmm изписва абревиатурата на месеца, mmmm изписва целия месец, mmmmm ви дава формата JASON. Защо го наричам формат JASON? Ако погледнете Wall Street Journal, финансовите им шортове винаги имат JFMAMJJASOND. Още през 1984 г. искам винилно копие на „Frankie & the Knockouts - Below the Belt“, за да отговоря на любопитния въпрос „Името на кой човек може да бъде изписано с помощта на съкращенията за месеца?“ И тъй като четях The Wall Street Journal в училище, знаех, че името е Джейсън, и спечелих това.Вероятно го раздадох при продажба на гараж за $ 50c, лошото ми. Добре, dd ви дава водещата цифра преди датата, ddd изписва съкращението, dddd ви дава цялото име на деня, името на делничния ден, ddddd не прави нищо. Можете да ги комбинирате, както искате. dddd изписва деня, вторник, поставете думата „the“ в кавички, самият ден от mmm, дава ви съкращението, добре, така че можете да ги комбинирате, както искате.
Време, едно странно нещо с времето, така че, h: mm или AM / PM, така че ако не поставите AM / PM, той се принуждава към 24-часово време, ако го направите, получавате 14:15. Ако имате hh, той принуждава водещата нула преди 2, дори можете да добавите .0 след секундите, за да получите милисекунди. Сега ето странното нещо, всъщност преди да стигна до странното нещо, не знам, нека първо направим странното. Нека направим = SUM на всички тези, така че всички те са 14 часа, 6 * 14 = 84, трябва да са 84 часа, но ако го форматирам като време, това казва 1:30 следобед, добре, и това е нещото. И така, тук имаме номер, тази сутрин на 10.10.2016 г. в 6 ч. Сутринта, и аз просто имам формули, които копират това надолу и мога да отменя, не забравяйте, това е форматиране като фасада. Мога да кажа „Хей, просто ми покажи годините, просто ми покажи месеца, просто ми покажи дните,просто ми покажете времевата част от това. " И тук, в тази СУММА, когато искаме време, казваме „Хей, игнорирай годините, месеците, дните, просто ми покажи времето.“ и какво всъщност е това, това са три дни + 1:30 следобед.
Така че, за да накарате това да се покаже правилно, или влизате във Време и търсите този, който казва 37 часа, или отивате в Custom, и ще видите, като поставите (h) казва „Покажете ни пълния брой часове . " Така че, ако трябва да видя часове и минути, (h): mm ще ми покаже, че всъщност това са 85,5 часа, виждам, отписвах 15-те минути, опитвайки се да вкарам 84-те в главата си, бях близо. Къде другаде можете да използвате квадратни скоби? Така че 6 ч. Сутринта, можете да покажете часовете, можете да покажете само минутите, 6 сутринта са 360 минути или само секундите, като използвате квадратните скоби. Добре, нека се върнем към един от форматите с числа, които имаме под формата на счетоводство, и да видим дали сега, след този наистина дълъг епизод, разбираме какво се случва.
И така, това казва, _ (казва „Оставете толкова място, колкото (в положителната стойност.“ „*“ Казва „Повтаряйте интервалите, докато стигнете до числата.“ #, ## 0 казва „Отиваме за да използвате разделителя за запетая, не поставяйте водещи знаци, ако нямаме точност, винаги поставяйте два знака след десетичната запетая. "И тогава _) казва" Оставете толкова място, колкото), и това е за положителните числа. " Добре сега, за отрицателните числа, това е наистина странно тук, преди $ те оставят толкова място, колкото (така че с други думи, те никога няма да покажат това. Отново "*", но тук те " сега ще поставите в (и). И след това за 0, оставете толкова място, колкото (и толкова място, колкото). Не поставяйте нищо, ние заместваме нулата с „-“, но след това оставете толкова място, колкото два знака след десетичната запетая, така чеще се подреди с това. И накрая в 4-та зона, ако въведат текст, оставете достатъчно място за (и а) и след това поставете текста.
Добре, това винаги беше просто глупост от мен, но има специфични значения за всички тези герои. Лудост е, че цялата тази информация, подкаст на стойност 18 минути, идва от 3 страници в тази книга, само си представете колко има във всичките 200 страници на книгата. Купете книгата, щракнете върху „i“ в горния десен ъгъл, ще я оцените и аз ще я оценя. Добре, дълъг, дълъг епизод: Форматирането на числа е фасада, Excel съхранява едно, а ни показва друго. Намаляването на десетичната и увеличаването на десетичните е чудесен пример за това, те все още съхраняват всички десетични знаци, но това опростява дисплея. Тук има 11 формата, но за да стигнете до тях, отидете в стартовия прозорец на диалоговия прозорец и след това можете да изберете нещо и след това да го промените в Custom, ако искате. Говорих за всички тези персонализирани кодове за форматиране на числа,ако имате по-добри съвети, моля, поставете ги в коментарите в YouTube.
Нула в кода на формата казва „Excel ТРЯБВА да показва цифрата, независимо дали има точност или не.“ Знак за хеш, знак за паунд казва, че може да покаже цифрата, ако има точност, но не е нужно. Въпросителният знак казва „Оставете място за цифрата, ако няма достатъчно точност.“ Може да има до 4 формата, разделени с точка и запетая, по този начин имаме различно число за положително, отрицателно, нулево и текст. 2 формата, първият е за отрицателен, вторият е за отрицателен, ако сте имали 3-ти формат, третият формат е за 0, четвъртият формат е за текст, ако е там. Добре, можем да покажем текст с число или да покажем текст вместо числото, „Победител“, „Губещ“ или „Нагоре“, „Плосък“, „Надолу“. Можете да посочите цвят за всяка зона, за да промените цветовите кодове, посетете Excel Options, Save, Colors.Поставете със звездичка всеки знак, за да повторите този знак преди цифрите, както би направил чекмайсторът. Поставете запетая в края, за да разделите на хиляда, две запетаи, за да разделите на милиони, 3 запетаи, разделени на милиарди и т.н. Можем да използваме условия в зоната и тогава долната черта X ще остави пространство с размер X. Добре, различните вариации на месеците, независимо дали сте поставили една цифра, две цифри, абревиатурата, изписвате я или само първата инициалка . Дни, една цифра, две цифри, съкратете или изречете, няма 5-ти. И накрая, за да получите пъти над 24 часа, трябва да поставите това h в квадратни скоби и други варианти на това.Можем да използваме условия в зоната и тогава долната черта X ще остави пространство с размер X. Добре, различните вариации на месеците, независимо дали сте поставили една цифра, две цифри, абревиатурата, изписвате я или само първата инициалка . Дни, една цифра, две цифри, съкратете или изречете, няма 5-ти. И накрая, за да получите пъти над 24 часа, трябва да поставите това h в квадратни скоби и други варианти на това.Можем да използваме условия в зоната и тогава долната черта X ще остави пространство с размер X. Добре, различните вариации на месеците, независимо дали сте поставили една цифра, две цифри, абревиатурата, изписвате я или само първата инициалка . Дни, една цифра, две цифри, съкратете или изречете, няма 5-ти. И накрая, за да получите пъти над 24 часа, трябва да поставите това h в квадратни скоби и други варианти на това.
Благодаря, че излезе до края! Ще се видим следващия път за поредното излъчване от!
Свали файл
Изтеглете примерния файл тук: Podcast2044.xlsm
