数の書式コード

From LibreOffice Help
Jump to: navigation, search

Number format codes can consist of up to four sections separated by a semicolon (;).

  1. In a number format code with two sections, the first section applies to positive values and zero, and the second section applies to negative values.
  2. In a number format code with three sections, the first section applies to positive values, the second section to negative values, and the third section to the value zero.
  3. You can also assign conditions to the three sections, so that the format is only applied if a condition is met.
  4. Fourth section applies if the content is not a value, but some text. Content is represented by an at sign (@).

Decimal Places and Significant Digits

Use zero (0), the number sign (#) or the question mark (?) as placeholders in your number format code to represent numbers. The (#) only displays significant digits, while the (0) displays zeroes if there are fewer digits in the number than in the number format. The (?) works as the (#) but adds a space character to keep decimal alignment if there is a hidden non-significant zero.

Use question marks (?) to represent the number of digits to include in the numerator and the denominator of a fraction. Fractions that do not fit the pattern that you define are displayed as floating point numbers.

If a number contains more digits to the right of the decimal delimiter than there are placeholders in the format, the number is rounded accordingly. If a number contains more digits to the left of the decimal delimiter than there are placeholders in the format, the entire number is displayed. Use the following list as a guide for using placeholders when you create a number format code:

Placeholders Explanation
# Does not display extra zeros.
? Displays space characters instead of extra zeros.
0 (Zero) Displays extra zeros if the number has less places than zeros in the format.

Number Format Format Code
3456.78 as 3456.8 ####.#
9.9 as 9.900 #.000
13 as 13.0 and 1234.567 as 1234.57 #.0#
5.75 as 5 3/4 and 6.3 as 6 3/10 # ???/???
.5 as 0.5 0.##
.5 as 0.5   (with two extra spaces at the end) 0.???

Thousands Separator

Depending on your language setting, you can use a comma, a period or a blank as a thousands separator. You can also use the separator to reduce the size of the number that is displayed by a multiple of 1000 for each separator.

Number Format Format Code
15000 as 15,000 #,###
16000 as 16 #,

Including Text in Number Format Codes

Text and Numbers

To include text in a number format that is applied to a cell containing numbers, place a double quotation mark (") in front of and behind the text, or a backslash (\) before a single character. For example, enter #.# "meters" to display "3.5 meters" or #.# \m to display "3.5 m".

Text and Text

To include text in a number format that is applied to a cell that might contain text, enclose the text by double quotation marks (" "), and then add an at sign (@). For example, enter "Total for "@ to display "Total for December".

Spaces

To use a character to define the width of a space in a number format, type an underscore ( _ ) followed by the character. The width of the space varies according to the width of the character that you choose. For example, _M creates a wider space than _i.

To fill free space with a given character, use an asterisk (*) followed by this character. For instance:

*\0

will display integer value (0) preceded by as many as needed backslash characters (\) to fill column width. For accounting representation, you may left align currency symbol with a format similar to:

$_-* 0.--;$-* 0.--;$_-* -

Color

To set the color of a section of a number format code, insert one of the following color names in square brackets [ ]:

CYAN GREEN
BLACK BLUE
MAGENTA RED
WHITE YELLOW

Conditions

Conditional Brackets

You can define a number format so that it only applies when the condition that you specify is met. Conditions are enclosed by square brackets [ ].

You can use any combination of numbers and the <, <=, >, >=, = and <> operators.

For example, if you want to apply different colors to different temperature data, enter:

[BLUE][<0]#.0 "°C";[RED][>30]#.0 "°C";[BLACK]#.0 "°C"

All temperatures below zero are blue, temperatures between 0 and 30 °C are black, and temperatures higher than 30 °C are red.

Positive and Negative Numbers

To define a number format that adds a different text to a number depending on if the number is positive, negative, or equal to zero, use the following format:

"plus" 0;"minus" 0;"null" 0

Percentages, Scientific Notation and Fraction Representation

Percentages

To display numbers as percentages, add the percent sign (%) to the number format.

Scientific Notation

Scientific notation lets you write very large numbers or very small fractions in a compact form. For example, in scientific notation, 650000 is written as 6.5 x 10^5, and 0.000065 as 6.5 x 10^-5. In LibreOffice, these numbers are written as 6.5E+5 and 6.5E-5, respectively. To create a number format that displays numbers using scientific notation, enter a # or 0, and then one of the following codes E-, E+, e- or e+. If sign is omitted after E or e, it won't appear for positive value of exponent. To get engineering notation, enter 3 digits (0 or #) in the integer part. For instance: ###.##E+00

Fraction Representation

To represent a value as a fraction, format consists of two or three parts: integer optional part, numerator and denominator. Integer and numerator are separated by a blank or any quoted text. Numerator and denominator are separated by a slash character. Each part can consist of a combination of #, ? and 0 as placeholders.

Denominator is calculated to get the nearest value of the fraction with repsect to the number of placeholders. For example, PI value is represented as 3 16/113 with format:

# ?/???

Denominator value can also be forced to the value replacing placeholders. For example, to get PI value as a multiple of 1/16th (i.e. 50/16), use format:

?/16

Number Format Codes of Currency Formats

The default currency format for the cells in your spreadsheet is determined by the regional setting of your operating system. If you want, you can apply a custom currency symbol to a cell. For example, enter #,##0.00 € to display 4.50 € (Euros).

You can also specify the locale setting for the currency by entering the locale code for the country after the symbol. For example, [$€-407] represents Euros in Germany. To view the locale code for a country, select the country in the Language list on the Numbers tab of the Format Cells dialog.

Note.png 通貨の書式コードの形式は、[$xxx-nnn] です。ただし、xxx は通貨記号、nnn は国コードです。EUR (ユーロ) などの特殊な銀行業務用記号では、国コードを必要としません。通貨の書式は、言語 ボックスで選択した言語とは無関係に設定できます。

Date and Time Formats

Date Formats

To display days, months and years, use the following number format codes.

Warning.png すべての言語に対して、すべての書式コードが意味のある結果をもたらすわけではありません。
Format Format Code
Month as 3. M
Month as 03. MM
Month as Jan-Dec MMM
Month as January-December MMMM
First letter of Name of Month MMMMM
Day as 2 D
Day as 02 DD
Day as Sun-Sat NN or DDD
Day as Sunday to Saturday NNN or DDDD
Day followed by comma, as in "Sunday," NNNN
Year as 00-99 YY
Year as 1900-2078 YYYY
Calendar week WW
Quarterly as Q1 to Q4 Q
Quarterly as 1st quarter to 4th quarter QQ
Era on the Japanese Gengou calendar, single character (possible values are: M, T, S, H) G
Era, abbreviation GG
Era, full name GGG
Number of the year within an era, without a leading zero for single-digit years E
Number of the year within an era, with a leading zero for single-digit years EE or R
Era, full name and year RR or GGGEE

上に一覧された書式形式はLibreOffice の言語と対応します。しかし、LibreOffice のロケールを 別のロケールに変える必要がある時には、そのロケールで使われている書式形式を知っておく必要があります。

例えば、ソフトウェアのロケールを英語に設定していて、 年を4桁の形式にしたいときは書式形式を YYYY で設定します。ロケールをドイツ語に変えたときは、年の書式を JJJJ に変えます。次の表はローカライズ時の違いだけを一覧にしてあります。

ロケール 曜日 年代
英語 - en

一覧にないロケール

Y M D H N G
ドイツ語 - de J T
オランダ - nl J U
フランス語 - fr A J O
イタリア語 - it A G O X
ポルトガル語 - pt A O
スペイン語 - es A O
デンマーク語 - da T
ノルウェー語 - no, nb, nn T
スウェーデン語 - sv T
フィンランド語 - fi V K P T

Entering Dates

To enter a date in a cell, use the Gregorian calendar format. For example, in an English locale, enter 1/2/2002 for Jan 2, 2002.

All date formats are dependent on the locale that is set in Tools - Options' - Language settings - Languages'. For example, if your locale is set to 'Japanese', then the Gengou calendar is used. The default date format in LibreOffice uses the Gregorian Calendar.

To specify a calendar format that is independent of the locale, add a modifier in front of the date format. For example, to display a date using the Jewish calendar format in a non-Hebrew locale, enter: [~jewish]DD/MM/YYYY.

The specified calendar is exported to MS-Excel using extended LCID. Extended LCID can also be used in the format string. It will be converted to a calendar modifier if it is supported. See Extended LCID section below.

Modifier Calendar
[~buddhist] Thai Buddhist Calendar
[~gengou] Japanese Gengou Calendar
[~gregorian] Gregorian Calendar
[~hanja] or [~hanja_yoil] Korean Calendar
[~hijri] Arabic Islamic Calendar
[~jewish] Jewish Calendar
[~ROC] Republic Of China Calendar

When in Calc: If you perform a calculation that involves one or more cells using a date format, the result is formatted according to the following mappings:

When in Calc: {When in Calc: The Date&Time format displays the date and time that an entry was made to a cell with this format.

When in Calc: LibreOffice では、年数が 4 桁で示されます。したがって、99/1/1 と 01/1/1 では、その差は 2 年になります。年 (2 桁) では、2 桁の年数に 2000 を加算する年を指定できます。例えば、この設定で「30/1/1」以降の日付を指定した場合、「20/1/1」は「1920/1/1」ではなく「2020/1/1」と認識されます。

Note.png By default in LibreOffice, a date with the value "0" corresponds to Dec 30, 1899.

Time Formats

To display hours, minutes and seconds use the following number format codes.

Format Format Code
Hours as 0-23 H
Hours as 00-23 HH
Hours as 00 up to more than 23 [HH]
Minutes as 0-59 M
Minutes as 00-59 MM
Minutes as 00 up to more than 59 [MM]
Seconds as 0-59 S
Seconds as 00-59 SS
Seconds as 00 up to more than 59 [SS]
Note.png To display seconds as fractions, add the decimal delimiter to your number format code. For example, enter HH:MM:SS.00 to display the time as "01:02:03.45".

Minute time formats M and MM must be used in combination with hour or second time formats to avoid confusion with month date format.

If a time is entered in the form 02:03.45 or 01:02:03.45 or 25:01:02, the following formats are assigned if no other time format has been specified: MM:SS.00 or [HH]:MM:SS.00 or [HH]:MM:SS

Displaying Numbers Using Native Characters

NatNum modifiers

To display numbers using native number characters, use a [NatNum1], [NatNum2], ... [NatNum11] modifier at the beginning of a number format codes.

The [NatNum1] modifier always uses a one to one character mapping to convert numbers to a string that matches the native number format code of the corresponding locale. The other modifiers produce different results if they are used with different locales. A locale can be the language and the territory for which the format code is defined, or a modifier such as [$-yyy] that follows the native number modifier. In this case, yyy is the hexadecimal MS-LCID that is also used in currency format codes. For example, to display a number using Japanese short Kanji characters in an English US locale, use the following number format code:

[NatNum1][$-411]0

In the following list, the Microsoft Excel [DBNumX] modifier that corresponds to LibreOffice [NatNum] modifier is shown. If you want, you can use a [DBNumX] modifier instead of [NatNum] modifier for your locale. Whenever possible, LibreOffice internally maps [DBNumX] modifiers to [NatNumN] modifiers.

[NatNum] 修飾キーを使用して日付を表示させると、ほかの種類の数値とは別の効果を加えることができます。そのような効果は、「CAL: 」で示されます。たとえば、「CAL: 1/4/4」は、年を、[NatNum1] 修飾キーで表示し、日と月を [NatNum4] 修飾キーで表示することを示します。「CAL」を指定しないと、特定の修飾キーに対する日付書式はサポートされません。

[NatNum0]

Try to convert any native number string to ASCII Arabic digits. If already ASCII, it remains ASCII.

[NatNum1]

Transliterations Native Number Characters DBNumX Date Format
Chinese Chinese lower case characters CAL: 1/7/7 [DBNum1]
Japanese short Kanji characters [DBNum1] CAL: 1/4/4 [DBNum1]
Korean Korean lower case characters [DBNum1] CAL: 1/7/7 [DBNum1]
Hebrew Hebrew characters
Arabic Arabic-Indic characters
Thai Thai characters
Hindi Indic-Devanagari characters
Odia Odia (Oriya) characters
Marathi Indic-Devanagari characters
Bengali Bengali characters
Punjabi Punjabi (Gurmukhi) characters
Gujarati Gujarati characters
Tamil Tamil characters
Telugu Telugu characters
Kannada Kannada characters
Malayalam Malayalam characters
Lao Lao characters
Tibetan Tibetan characters
Burmese Burmese (Myanmar) characters
Khmer Khmer (Cambodian) characters
Mongolian Mongolian characters
Nepali Indic-Devanagari characters
Dzongkha Tibetan characters
Farsi East Arabic-Indic characters
Church Slavic Cyrillic characters

[NatNum2]

Transliterations Native Number Characters DBNumX Date Format
Chinese Chinese upper case characters CAL 2/8/8 [DBNum2]
Japanese traditional Kanji characters CAL 2/5/5 [DBNum2]
Korean Korean upper case characters [DBNum2] CAL 2/8/8 [DBNum2]
Hebrew Hebrew numbering

[NatNum3]

Transliterations Native Number Characters DBNumX Date Format
Chinese fullwidth Arabic digits CAL: 3/3/3 [DBNum3]
Japanese fullwidth Arabic digits CAL: 3/3/3 [DBNum3]
Korean fullwidth Arabic digits [DBNum3] CAL: 3/3/3 [DBNum3]

[NatNum4]

Transliterations Native Number Characters DBNumX Date Format
Chinese lower case text [DBNum1]
Japanese modern long Kanji text [DBNum2]
Korean formal lower case text

[NatNum5]

Transliterations Native Number Characters DBNumX Date Format
Chinese Chinese upper case text [DBNum2]
Japanese traditional long Kanji text [DBNum3]
Korean formal upper case text

[NatNum6]

Transliterations Native Number Characters DBNumX Date Format
Chinese fullwidth text [DBNum3]
Japanese fullwidth text
Korean fullwidth text

[NatNum7]

Transliterations Native Number Characters DBNumX Date Format
Chinese short lower case text
Japanese modern short Kanji text
Korean informal lower case text

[NatNum8]

Transliterations Native Number Characters DBNumX Date Format
Chinese short upper case text
Japanese traditional short Kanji text [DBNum4]
Korean informal upper case text

[NatNum9]

Transliterations Native Number Characters DBNumX Date Format
Korean Hangul characters

[NatNum10]

Transliterations Native Number Characters DBNumX Date Format
Korean formal Hangul text [DBNum4] CAL 9/11/11 [DBNum4]

[NatNum11]

Transliterations Native Number Characters DBNumX Date Format
Korean informal Hangul text

Extended LCID

If compatible, native numbering and calendar are exported to MS-Excel using extended LCID. Extended LCID can also be used in string format instead of NatNum modifier.

Extended LCID consists of 8 hexadecimal digits: [$-NNCCLLLL], with 2 first digits NN for native numerals, CC for calendar and LLLL for LCID code. For instance, [$-0D0741E] will be converted to [NatNum1][$-41E][~buddhist]: Thai numerals (0D) with Buddhist calendar (07) in Thai locale (041E).

Native Numerals

Two first digits NN represents native numerals:

NN Numeral Representation Compatible LCID
01 Arabic 1234567890 all
02 Eastern Arabic ١٢٣٤٥٦٧٨٩٠ 401

1401, 3c01, 0c01, 801, 2c01, 3401, 3001, 1001, 1801, 2001, 4001, 2801, 1c01, 3801, 2401

03 Persian ۱۲۳۴۵۶۷۸۹۰ 429
04 Devanagari १२३४५६७८९० 439

44E, 461, 861

05 Bengali ১২৩৪৫৬৭৮৯০ 445

845

06 Punjabi ੧੨੩੪੫੬੭੮੯੦ 446
07 Gujarati ૧૨૩૪૫૬૭૮૯૦ 447
08 Oriya ୧୨୩୪୫୬୭୮୯୦ 448
09 Tamil ௧௨௩௪௫௬௭௮௯0 449

849

0A Telugu ౧౨౩౪౫౬౭౮౯౦ 44A
0B Kannada ೧೨೩೪೫೬೭೮೯೦ 44B
0C Malayalam ൧൨൩൪൫൬൭൮൯൦ 44C
0D Thai ๑๒๓๔๕๖๗๘๙๐ 41E
0E Lao ໑໒໓໔໕໖໗໘໙໐ 454
0F Tibetan ༡༢༣༤༥༦༧༨༩༠ 851
10 Burmese ၁၂၃၄၅၆၇၈၉၀ 455
11 Tigrina ፩፪፫፬፭፮፯፰፱0 473

873

12 Khmer ១២៣៤៥៦៧៨៩០ 453
13 Mongolian ᠑᠒᠓᠔᠕᠖᠗᠘᠙᠐ C50

850

1B Japanese 一二三四五六七八九〇 411
1C (financial) 壱弐参四伍六七八九〇
1D (fullwidth Arabic) 1234567890
1E Chinese - simplified 一二三四五六七八九○ 804

1004, 7804

1F (financial) 壹贰叁肆伍陆柒捌玖零
20 (fullwidth Arabic) 1234567890
21 Chinese - traditional 一二三四五六七八九○ C04

1404

22 (financial) 壹貳參肆伍陸柒捌玖零
23 (fullwidth Arabic) 1234567890
24 Korean 一二三四五六七八九0 812
25 (financial) 壹貳參四伍六七八九零
26 (fullwidth Arabic) 1234567890
27 Korean - Hangul 일이삼사오육칠팔구영

Calendar

Two next digits CC are for calendar code. Each calendar is only valid for some LCID.

CC Calendar Example '(YYYY-MM-DD)' Supported LCID
00 Gregorian 2016-08-31 All
03 Gengou 28-08-31 411 (Japanese)
05 Unknown 4349-08-31 Unsupported
06 or 17 Hijri 1437-11-28 401 (Arabic - Saudi Arabia), 1401 (Arabic - Algeria), 3c01 (Arabic - Bahrain), 0c01 (Arabic - Egypt), 801 (Arabic - Iraq), 2c01 (Arabic - Jordan), 3401 (Arabic - Kuwait), 3001 (Arabic - Lebanon), 1001 (Arabic - Libya), 1801 (Arabic - Morocco), 2001 (Arabic - Oman), 4001 (Arabic - Qatar), 2801 (Arabic - Syria), 1c01 (Arabic - Tunisia), 3801 (Arabic - U.A.E.), 2401 (Arabic - Yemen) and 429 (Farsi)
07 Buddhist 2559-08-31 454 (Lao), 41E (Thai)
08 Jewish 5776-05-27 40D (Hebrew)
10 Indian 1938-06-09 Unsupported
0E, 0F, 11, 12 or 13 Unknown 2016-07-29 Unsupported
Unsupported Hanja 412 (Korean)
Unsupported ROC 0105-08-31 404 (Chinese - Taiwan)