Эта статья раскрывает скрытые секреты форматирования чисел в 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 - это ширина двух WW-символов. Пространство между 2 и 3 - это ширина N. Пробел между 3 и 4 ширина строчной буквы i.
На следующем рисунке показаны различные коды форматирования даты.
Обратите внимание, что формат ммммм в строке 8 полезен для создания подписей диаграммы JFMAMJJASOND.
Спасибо Дэйву Бейлису, Брэду Эдгару, Майку Гирвину и @best_excel за предложение этой функции.
Смотреть видео
- Форматирование чисел - это фасад: Excel хранит одно, а показывает другое.
- Например: используйте уменьшающие десятичные дроби, чтобы показать округленную версию числа.
- Excel по-прежнему сохраняет все десятичные дроби, но упрощает отображение.
- В раскрывающемся списке на главной вкладке есть 11 числовых форматов.
- Щелкните панель запуска диалогового окна, чтобы перейти к диалоговому окну «Форматирование ячеек».
- Сегодня мы говорим о кодах форматирования в группе Custom.
- Сегодня у меня нет всех знаний, поэтому, если у вас есть лучшие советы, поместите их в комментарии на YouTube.
- Ноль в коде формата означает, что Excel должен отображать эту цифру.
- Знак # говорит о том, что они могут отображать цифру, если точность достаточна, но не обязаны, если ее нет.
- А? оставит место для цифры, чтобы десятичный разряд был выровнен.
- Может быть 1, 2, 3 или 4 формата, разделенных точкой с запятой.
- Когда есть 2 формата, первый для нуля и выше. Второй - отрицательный.
- Когда есть 3 формата, третий если на ноль.
- 4-й формат - для текста.
- Использование числового формата для отображения текста с числом или для отображения текста вместо числа.
- Указание цвета зоны.
- Чтобы изменить (Цвет 5), посетите Параметры Excel.
- * x, чтобы повторить x до цифр
- , в конце разделить на 1000
- ,, делится на миллионы
- Использование условий в зоне
- _x оставит пространство размером "x"
- м мм ммм мммм ммммм
- д дд ддд дддд
- (h): мм
- Спасибо Дэйву Бейлису, Брэду Эдгару, Майку Гирвину и @best_excel за предложение этой функции.
Стенограмма видео
Изучите Excel из подкаста, выпуск 2044 - Форматирование как фасад!
Хорошо, я подкастирую все свои советы из этой книги, нажмите «i» в правом верхнем углу, чтобы перейти к плейлисту!
Сегодня мы говорим о пользовательском форматировании чисел, поэтому у меня есть количество, доход, и мы отображаем здесь целую кучу десятичных знаков. И, знаете, если мы не хотим воспроизводить все эти десятичные дроби и не хотим использовать функцию ROUND, я просто подхожу сюда и использую Decrease Decimal. он не меняет число, которое фактически хранится за кулисами, а просто отображает то, как Excel показывает нам число. Хорошо, я украл термин Майка Гирвина, что мы используем форматирование как фасад, верно, в этой ячейке хранится целая куча десятичных чисел, но мы используем специальные числовые форматы для отображения этого. И я хочу выйти за рамки 11 форматов здесь, и даже щелкнуть эту диалоговую панель запуска, за пределами большинства вещей, которые здесь есть, и я хочу поговорить о Custom.Пользовательские числовые форматы открывают нам мир, понятно?
Итак, вот целый ряд настраиваемых числовых форматов, которые вы можете ввести в это поле, а результаты будут показаны слева. Итак, просто General, вы получаете все десятичные разряды, но здесь нули перед десятичным разрядом заставляют появляться нули. Так что, если число <100, я получу 0 в начале, иногда это полезно для номеров деталей, нули после десятичных знаков заставляют появляться числа. Хэштег или знак решетки (#) перед десятичной точкой не обязательны, хорошо, так что это может быть, но не обязательно. Хорошо, в этом случае у нас есть число 6, но поскольку у нас пять нулей, это дает нам ведущие нули. Теперь, если вам нужно выполнить VLOOKUP в разделе текста, который содержит 00006, это не будет совпадением, но если вам просто нужно посмотреть правильно, это сработает. Хорошо, здесь есть кое-что интересное,мы часто используем знаки решетки или хэштеги, много «хеш-знаков». В США мы называем это знаком фунта, и я понимаю, что в Англии это не знак фунта, £ - это ваша валюта, которую вы называете хешем. Хорошо, извините, я всю жизнь называл это знаком фунта, и мне трудно начать называть хеш, но я над этим поработаю.
Хорошо, здесь .000 говорит, что вы собираетесь отображать нули независимо от того, есть ли точность, и я изменил эти числа на 6,42 и 6,4. Это интересно, это то, о чем я никогда не знал, если вы поставите? вместо 0 он говорит, что будет отображать точность, если она есть, но если нет, не отображать ее, но не позволять вещам течь вправо. Хорошо, вот, давайте изменим эти числа, 6.42 и 6.4, хорошо, смотрите здесь, десятичные дроби не остаются выровненными. Если вы хотите, чтобы десятичные дроби оставались выровненными, используйте вопросительные знаки, если вы хотите, чтобы там были нули, используйте нули.
Теперь, когда мы проходим здесь, есть МНОГО деталей в мелочах, но я не знаю, что здесь есть. Если вы заметите, что я пропустил какой-нибудь классный трюк с форматированием чисел, оставьте его в комментариях на YouTube. Хорошо, теперь вот следующая концепция, мы можем отображать 1, 2, 3 или 4 числовых формата в поле Тип, если вы отображаете только 1, это просто число, которое используется для всего, хорошо. Но если вы отображаете 2, и они разделены точкой с запятой, то 1-й для чисел> = 0, а 2-й для чисел <0. Итак, прямо здесь я использовал два кода, +0; -0, и это означает, что отрицательные числа отображаются с минусом, а положительные числа отображаются с плюсом, но потому что я не указал, что происходит для 0 , он также отображается с плюсом.
Итак, мы можем добавить третью зону: положительную, отрицательную, нулевую, хорошо, поэтому +0; -0; 0, хорошо, чтобы обрабатывать нули. Может быть даже 4-й код, что делать, если кто-то вводит текст. Хорошо, теперь следующая концепция, в произвольном числовом формате, помимо нулей, вы можете заключать текст в кавычки! Хорошо, давайте посмотрим на пользовательский формат чисел здесь, для положительных чисел, слова «Пожалуйста, верните», а затем числа, для отрицательного «Кредитного баланса» числа и «(Не платить)». А для 0 я даже не показываю номер, я просто показываю «Нет баланса», хорошо. Таким образом, вы можете использовать это для замены числа, это -1, 0 и 1, и это формулы, чтобы получить формулу, просто скопируйте ее. Все отрицательные числа будут говорить «Неудачник!» или «Вниз», или слово «Минус» и число, поэтому мы добавляем -5, все равно говорят «Неудачник!» или «Вниз» или «Минус 5».Для 0 мы можем сказать «Ноль», сказать «Плоский» или сказать «0», а затем для положительного числа, давайте поместим его в 55, «Победитель!», «Вверх» или «Положительный 55», хорошо, так что вы можете добавлять слова. И что круто, так это 55, 55, 55, если бы я суммировал это, это действительно числа, и это действительно сработает, как обычно, СУММ не работает с текстом, но на самом деле это дает мне ответ . Итак, если вы хотите оставить числа там, но отображать слова в ячейках, это отличный трюк.Я не могу работать с текстом, но на самом деле это дает мне ответ. Итак, если вы хотите сохранить числа там, но отображать слова в ячейках, это отличный трюк.Я не могу работать с текстом, но на самом деле это дает мне ответ. Итак, если вы хотите оставить числа там, но отображать слова в ячейках, это отличный трюк.
Хорошо, теперь эти символы можно использовать в произвольном числовом формате без кавычек, поэтому, если вам понадобится просто + или -, поэтому вам не нужно заключать его в кавычки. И есть некоторые дополнения к этому, например, как только я увидел этот список, я сказал: «Хорошо, давайте посмотрим, с чем мы можем облажаться». Все это числа 1 2 3 4, поэтому, если я использовал собственный числовой формат ((number> &, все это будет отображаться. Если я хочу разделить цифры тире, это пользовательский формат чисел, чтобы получить это работает. И все они настоящие, так что 7854, смотрите, все меняется, это все формулы, идущие сюда. Так что я могу как бы прикрутить пользовательский числовой формат и поставить символы перед ним, поставить символы после него. нет в списке, который я знаю, что это работает, это K, вы можете поставить K в конце, и это 'не в кавычках. Но M, поскольку M означает месяцы или минуты, вам нужно будет заключить его в кавычки или поставить M, чтобы в конце появилось M.
Хорошо, следующая концепция, в каждой зоне вам разрешено указывать цвет, так, здесь положительные числа зеленые, а отрицательные числа синие. Ctrl + 1, чтобы перейти к формату ячеек, перед пользовательским числовым форматом в первой зоне, в положительной зоне, я сказал зеленый цвет, а для отрицательных чисел я сказал синий. Какой цвет нам разрешено там использовать? Восходит к тем временам, когда использовалось 8 цветов: черный, зеленый, белый, синий, пурпурный, желтый, голубой и красный. Но благодаря Майку Александру вам разрешено использовать 56 других цветов, Цвет 1, Цвет 2 должен быть в (), вот так, это 56 цветов. Если вам нужно их изменить, мы очень углубляемся, теперь мы переходим на территорию детей Чарли! Параметры Excel, Сохранить, а затем щелкните Цвета, хорошо и т.п., если я хочу изменить этот оранжевый цвет на что-то другое, может быть, на фиолетовый, щелкните ОК, щелкните ОК,нажмите ОК, и я не могу сказать, какой из них изменился бы на этот цвет. Еще один крутой трюк, а если вы хотите скрыть все числа? Итак, у вас есть несколько секретов, и вы не хотите, чтобы их кто-то видел. Ctrl + 1, введите произвольный числовой формат, я говорю: «Ничего не отображать для положительного, ничего для отрицательного, ничего для 0», нажмите «ОК», и все будет скрыто. Конечно, они все еще могут видеть это здесь, в строке формул, так что это не идеально, хорошо, вот некоторые другие символы форматирования клиента.ничего за отрицательный, ничего за 0 »нажмите ОК, и все будет скрыто. Конечно, они все еще могут видеть это здесь, в строке формул, так что это не идеально, хорошо, вот некоторые другие символы форматирования клиента.ничего за отрицательный, ничего за 0 »нажмите ОК, и все будет скрыто. Конечно, они все еще могут видеть это здесь, в строке формул, так что это не идеально, хорошо, вот некоторые другие символы форматирования клиента.
Звездочка говорит: «Отобразить следующий символ, как писатель чеков». Итак, эти ** говорят: «Знаете, заполните область до числа звездочками», но вы можете это изменить, вы можете сделать этого персонажа чем угодно! Так *! поместит туда все восклицательные знаки, давайте изменим это, Ctrl + 1, изменим его на * X, и я получу все X до этого момента. Таким образом, вы можете создать эффект чек-писателя, но не со звездочкой, вы можете поместить туда все, что захотите. Поставьте запятую в конце, так что это 123456, пользовательский формат чисел с запятой после нуля говорит: «Отобразите это в тысячах, разделенных на 1000», я поставил, K для тысяч. Если вы хотите сделать это в миллионах, то есть 0,0, каждая запятая говорит: «Показать, но разделить на еще одну 1000». Итак, это деление на миллион,поставьте «M», чтобы отобразить миллионы. Я знаю, что некоторые люди там используют ММ, моя сестра Алиса (?), Которая работает в Procter & Gamble, «ММ» должно быть в кавычках.
Теперь здесь мы вводим число, если кто-то вводит что-то не число, сообщение в 4-й зоне перезапишет его. Так что если мы введем цифру 1, это здорово, добавим -1, это здорово, если мы введем «Привет», он кричит нам: «Введите номер!». Хорошо, опять же, это формулы, которые переводят все, что я набираю, из A в B. Вы можете использовать условия, хорошо, так что положительное, отрицательное, ноль, вы можете это переопределить. Итак, я скажу красный, если это 90, и у вас может быть только два условия, а затем третья зона для вещей, которые не 90, старое, старомодное условное форматирование. Еще один, вот еще один, это просто безумие, ставится подчеркивание, а затем любой символ оставляет достаточно места для этого символа.Вы часто видите это с помощью _ (чтобы положительные числа были выровнены с отрицательными числами, но вы действительно можете ошибиться с Excel. Итак, W - самый широкий символ, так что это помещает 1-ю цифру, а затем достаточно места для 2 Вт , а затем 2-я цифра и достаточно места для N, затем 3-я цифра и достаточно места для I, а затем, наконец, 4-я цифра. Это полезно для чего-нибудь? Ну, я не знаю, но это мило of wild, что вы можете установить различный интервал между цифрами, используя символы подчеркивания.но это своего рода дикий, что вы можете установить различный интервал между цифрами, используя символы подчеркивания.но это своего рода дикий, что вы можете установить различный интервал между цифрами, используя символы подчеркивания.
Хорошо, даты, м / д / гг, вы получаете год с двумя цифрами, расширяете его до гггг, вы получаете год с четырьмя цифрами, ставите мм, он заставляет начальный 0 перед 7, dd заставляет начальный 0 перед 3 .Если вы хотите использовать какой-нибудь старомодный кобальтовый формат даты, не помещайте /, только все символы, ГГГГММДД. mmm обозначает аббревиатуру месяца, mmmm обозначает весь месяц, mmmmm дает вам формат JASON. Почему я называю это форматом JASON? Если вы посмотрите на Wall Street Journal, в их финансовых шортах всегда есть JFMAMJJASOND. Еще в 1984 году мне нужна виниловая копия книги «Фрэнки и нокауты - Ниже пояса», чтобы ответить на простой вопрос: «Имя какого человека можно написать, используя аббревиатуры месяца?» И поскольку я читал The Wall Street Journal в школе, я знал, что это имя Джейсон, и выиграл это.Я, наверное, отдал его на гаражной распродаже за 50 центов, плохо. Хорошо, dd дает вам первую цифру перед датой, ddd представляет аббревиатуру, dddd дает вам полное название дня, название дня недели, ddddd ничего не делает. Вы можете комбинировать их, как хотите. dddd обозначает день, вторник, помещает слово «the» в кавычки, сам день mmm дает вам аббревиатуру, хорошо, так что вы можете комбинировать их, как хотите.
Время, одна странная вещь со временем, так что, ч: мм или AM / PM, поэтому, если вы не поместите AM / PM, оно будет переведено в 24-часовое время, если вы это сделаете, тогда вы получите 14:15. Если у вас есть hh, он заставляет начальный ноль перед 2, вы даже можете добавить .0 после секунд, чтобы получить миллисекунды. А теперь вот причудливая вещь, на самом деле, прежде чем я перейду к причудливой вещи, я не знаю, давайте сначала сделаем причудливую вещь. Давайте сделаем = SUM всех этих, так что все это 14 часов, 6 * 14 = 84, это должно быть 84 часа, но если я отформатирую его как время, будет 1:30 днем, хорошо, и вот в чем дело. Итак, у нас есть номер здесь, сегодня утром 9.10.2016 в 6 часов утра, и у меня просто есть формулы, копирующие его, и я могу переопределить, помните, это форматирование как фасад. Я могу сказать: «Эй, покажи мне годы, просто покажи месяц, просто покажи мне дни,просто покажи мне временную часть этого ». И здесь, в этой СУММ, когда мы просим время, мы говорим: «Эй, не обращай внимания на годы, месяцы, дни, просто покажи мне время». и что это на самом деле, это три дня + 1:30 дня.
Итак, чтобы это отображалось правильно, вы либо заходите в Time и ищите тот, который говорит 37 часов, либо переходите в Custom, и вы увидите, что добавление (h) говорит: «Покажите нам полное количество часов. . » Так что, если мне нужно увидеть часы и минуты, (h): mm покажет мне, что на самом деле это 85,5 часов, я понимаю, я списывал 15 минут, пытаясь вбить 84 в свою голову, я был близок. Где еще можно использовать квадратные скобки? Итак, в 6 утра вы можете показывать часы, вы можете показывать только минуты, 6 утра - это 360 минут или только секунды, используя квадратные скобки. Хорошо, давайте вернемся к одному из числовых форматов, имеющихся в формате бухгалтерского учета, и посмотрим, понимаем ли мы теперь, после этого действительно длинного эпизода, что происходит.
Итак, что это говорит, _ (говорит: «Оставьте столько места, сколько (в положительном значении». «*» Говорит: «Повторяйте пробелы, пока не дойдете до чисел». #, ## 0 говорит: «Мы собираемся чтобы использовать разделитель запятую, не ставьте начальные символы, если у нас нет точности, всегда ставьте два десятичных знака ». А затем _) говорит:« Оставьте столько же места, сколько), и это для положительных чисел ». Хорошо, теперь для отрицательных чисел здесь действительно странно, до $ они оставляют столько же места, как (другими словами, они никогда не будут отображать это. Опять "*", но вот они ' Теперь мы собираемся вставить (и). А затем для 0 оставьте столько же места, сколько (и столько же, сколько). Ничего не помещайте, мы заменяем ноль на «-», но затем оставьте столько же места, сколько два десятичных знака, чтобы 'собираюсь присоединиться к этому. И, наконец, в 4-й зоне, если они вводят текст, оставьте достаточно места для (и a), а затем введите текст.
Хорошо, это всегда было просто чепухой, чем я, но у всех этих персонажей есть особые значения. Это безумие, что вся эта информация, 18-минутный подкаст, взята с 3-х страниц этой книги, только представьте, сколько всего этого на всех 200 страницах книги. Купите книгу, нажмите «i» в правом верхнем углу, вы оцените ее, и я буду признателен. Хорошо, длинный-длинный эпизод: форматирование чисел - это фасад, Excel хранит одно, а показывает другое. Уменьшение десятичной дроби и увеличение десятичной дроби - отличный пример этого, они по-прежнему хранят все десятичные дроби, но упрощают отображение. Здесь 11 форматов, но на самом деле, чтобы добраться до них, вы переходите в панель запуска диалогов, затем вы можете выбрать что-нибудь, а затем изменить это в Custom, если хотите. Говорили обо всех этих кодах форматирования произвольных чисел,если у вас есть лучшие советы, пожалуйста, оставьте их в комментариях на YouTube.
Ноль в коде формата означает, что «Excel ДОЛЖЕН отображать цифру, независимо от того, есть ли точность». Знак решетки, знак фунта означает, что они могут отображать цифру, если есть точность, но это не обязательно. Знак вопроса говорит: «Оставьте место для цифры, если недостаточно точности». Может быть до 4 форматов, разделенных точкой с запятой, таким образом, у нас есть разные числа для положительного, отрицательного, нуля и текста. 2 формата, 1-й для неотрицательного, 2-й для отрицательного, если у вас был 3-й формат, третий формат для 0, 4-й формат для текста, если он есть. Хорошо, мы можем отображать текст с числом или отображать текст вместо числа: «Победитель», «Проигравший» или «Вверх», «Плоский», «Вниз». Вы можете указать цвет для каждой зоны, чтобы изменить цветовые коды, перейдите в Параметры Excel, Сохранить, Цвета.Поставьте звездочку на любой символ, чтобы повторить этот символ перед числами, как это сделал бы чек-писатель. Поставьте запятую в конце, чтобы разделить на тысячу, две запятые, чтобы разделить на миллионы, 3 запятую, чтобы разделить на миллиарды, и так далее. Мы можем использовать условия в зоне, а затем подчеркивание X оставит пробел размером X. Хорошо, разные варианты месяцев, вводите ли вы одну цифру, две цифры, аббревиатуру, произносите это по буквам или просто первая буква . Дни, одна цифра, две цифры, сокращайте или пишите по буквам, пятой не бывает. И, наконец, чтобы получить время, превышающее 24 часа, вы должны заключить этот h в квадратные скобки и другие варианты этого.Мы можем использовать условия в зоне, а затем подчеркивание X оставит пробел размером X. Хорошо, разные варианты месяцев, вводите ли вы одну цифру, две цифры, аббревиатуру, произносите это по буквам или просто первая буква . Дни, одна цифра, две цифры, сокращайте или пишите по буквам, пятой не бывает. И, наконец, чтобы получить время, превышающее 24 часа, вы должны заключить этот h в квадратные скобки и другие варианты этого.Мы можем использовать условия в зоне, а затем подчеркивание X оставит пробел размером X. Хорошо, разные варианты месяцев, вводите ли вы одну цифру, две цифры, аббревиатуру, произносите это по буквам или просто первая буква . Дни, одна цифра, две цифры, сокращайте или пишите по буквам, пятой не бывает. И, наконец, чтобы получить время, превышающее 24 часа, вы должны заключить этот h в квадратные скобки и другие варианты этого.
Спасибо, что дожили до конца! Увидимся в следующий раз на другом сетевом трансляции от!
Скачать файл
Загрузите образец файла здесь: Podcast2044.xlsm
