Documentation/Calc 関数/CELL

    From The Document Foundation Wiki
    This page is a translated version of the page Documentation/Calc Functions/CELL and the translation is 12% complete.
    Other languages:


    Function name:

    CELL

    Category:

    Information

    Summary:

    セルのアドレス、書式設定、または内容についての情報を返します。

    Syntax:

    CELL(情報の種類[; 対象範囲])

    Returns:

    要求された情報の性質に応じた型の値を返します。

    Arguments:

    情報の種類は、返される情報のタイプを決定するテキスト文字列、またはテキスト文字列を含むセルへの参照です。情報の種類は大文字と小文字の区別がされません。そして、次の許可されたオプションのいずれかに対応している必要があります "ADDRESS"、"COL"、"COLOR", "CONTENTS", "COORD", "FILENAME", "FORMAT", "PARENTHESES", "PREFIX"、"PROTECT"、"ROW"、"SHEET"、"TYPE"、または "WIDTH"。これらの各オプションの詳細は、次の表を参照してください。

    対象範囲は情報を返すセルへの参照を指定します。対象範囲を省略すると、数式が入力されているセルの情報が返されます。対象範囲には、次のいずれかを指定できます。

    • 単一のセルへの参照。
    • セル範囲への参照 ( D6:E12 など)。
    • 名前付き範囲の名前。
    • データベース範囲の名前。

    対象範囲で複数のセルが指定されている場合、CELL は、その範囲の左上のセルの情報を返します。

    Note pin.svg

    Note:
    次の 2 つの表の内容には、数値や日付の書式など、ロケールに依存する項目が含まれています。このページのすべての例は英語(米国)ロケールでテストされていて、他のロケール設定では動作が異なる場合があります。

    Description of valid Info Type argument strings 有効な情報の種類引数の文字列
    情報の種類 意味
    ADDRESS

    Returns the absolute address of the cell. For example:

    • With the formula located in cell A1, =CELL("address") returns $A$1.
    • =CELL("address"; D7) returns $D$7.
    • =CELL("address"; Sheet2.E7) returns $Sheet2.$E$7.
    • =CELL("address"; 'file:///C:/spreadsheets/test.ods'#$Sheet1.D10) returns 'file:///C:/spreadsheets/test.ods'#$Sheet1.D10.

    The above examples assume the default setting of Calc A1 for the Formula syntax menu in the Tools ▸ Options ▸ LibreOffice Calc ▸ Formula dialog (LibreOffice ▸ Preferences ▸ LibreOffice Calc ▸ Formula on macOS).

    COL

    Returns the column number of the cell (column A is 1, column B is 2, and so on). For example:

    • With the formula located in cell A1, =CELL("col") returns 1.
    • =CELL("col"; D7) returns 4.
    • =CELL("col"; Sheet2.E7) returns 5.
    COLOR

    Returns a value indicating whether negative values are formatted in color in the cell (1=formatted in color, 0=not formatted in color). For example:

    • The formula =CELL("color") returns 1 in a cell that has been formatted such that the Negative numbers red option has been selected on the Numbers tab of the Format ▸ Cells dialog.
    • The formula =CELL("color"; D7) returns 0 if cell D7 has been formatted such that the Negative numbers red option has been deselected on the Numbers tab of the Format ▸ Cells dialog.
    CONTENTS

    Returns the contents of the cell, without any formatting. For example:

    • Cell E2 contains the number -1234.567, with the cell formatted to show no decimal places and negative numbers as red (format code 0;[RED]-0). The formula =CELL("contents"; E2) returns -1234.567, without the formatting applied to cell E2.
    • Cell E3 contains the text "Hello world!", formatted with bold, italic, and underline. The formula =CELL("contents"; E3) returns " Hello world!", without the formatting applied to cell E3.
    • Cell E4 contains the date 2021-12-05. The formula =CELL("contents"; E4) returns the number 44535, the date-time serial number of 2021-12-05 (assuming that the start date for the internal conversion from days to numbers is set to the default of 1899-12-30).
    • Cell E5 contains the Boolean value TRUE. The formula =CELL("contents"; E5) returns 1.
    • If the formula =CELL("contents") is placed in a cell, then CELL reports a circular reference error (エラー:522).
    COORD

    Returns the complete cell address using a legacy notation, in which the sheet position is indicated by a letter (A indicates the spreadsheet at the left of the sheet tab area, B the next sheet to the right, and so on). For example:

    • With the formula located in cell A1 of the eighth sheet along, =CELL("coord") returns $H:$A$1.
    • With the formula located in the sixth sheet along, =CELL("coord"; D7) returns $F:$D$7.

    The "COORD" option for the Info Type argument is not a requirement of ODF 1.2.

    FILENAME

    Returns the file name and the name of the sheet in which the cell is located. For example:

    • With the formula in cell D7 of Sheet1 in the file C:/spreadsheets/test.ods, =CELL("filename") returns 'file:///C:/spreadsheets/test.ods'#$Sheet1.
    • In the same file, =CELL("filename"; $Sheet3.F5) would return 'file:///C:/spreadsheets/test.ods'#$Sheet3.
    • With the formula in cell D7 of Sheet1 in a new (unsaved) spreadsheet, =CELL("filename") returns "#$Sheet1.
    FORMAT Returns a character string that indicates the cell’s number format. The possible return values are defined in a separate table below.
    PARENTHESES

    Returns 1 if the cell's format code contains a left parenthesis and returns 0 otherwise. For example:

    • If the format code #,##0_);(#,##0) is applied to cell D3, then =CELL("parentheses"; D3) returns 1.
    PREFIX

    Returns a single character string indicating the alignment of text in the cell, coded as follows:

    • ' (apostrophe) = left aligned
    • " (quotation mark) = right aligned
    • ^ (caret) = centered
    • \ (backslash) = filled alignment

    For example:

    • If cell D3 is right-aligned, the formula =CELL("prefix"; D3) returns a quotation mark (").

    Returns the null character (Unicode U+0000) if the cell does not contain a string.

    PROTECT

    Returns the status of cell protection for the cell. 1 indicates that the cell is protected, while 0 indicates that the cell is not protected. For example:

    • If cell B1 is protected, then =CELL("protect"; B1) returns 1.

    Note that cell protection is only effective after the current sheet has been protected. Cell protection may be enabled while a sheet is not protected.

    ROW

    Returns the row number of the cell. For example:

    • With the formula located in cell A1, =CELL("row") returns 1.
    • =CELL("row"; D7) returns 7.
    • =CELL("row"; Sheet2.E12) returns 12.
    SHEET

    Returns the number of the referenced sheet, 1 indicates the sheet at the left of the sheet tab area, 2 the next sheet along to the right, and so on. For example:

    • With the formula located in cell A1 of the sheet Receipts (2nd from the left in the sheet tab area), =CELL("sheet") returns 2.
    • =CELL("sheet"; Accounts.G7) returns 3 in the case when the sheet Accounts is 3rd from the left in the sheet tab area.
    TYPE

    Returns the type of data in the cell. One of three possible single‑character strings – b (for blank) for an empty cell; l (for label) if the cell contains text; v (for value) otherwise. For example:

    • If cell A1 contains the text "Hello world!", then the formula =CELL("type"; A1) returns l.
    • If cell A2 contains the number 2, then the formula =CELL("type"; A2) returns v.
    • If cell A3 is empty, then the formula =CELL("type"; A3) returns b.

    The formula =CELL("type") always returns v.

    WIDTH

    Returns the width of the column in which the cell lies. The unit is the number of zero (0) characters that fit into the column based on the default font. For example:

    • Suppose the formula =CELL("width"; C2) currently returns the value 11.
    • Changing the width of column C will affect the result of the formula. If you increase the width of the column, then the value returned will increase. If you reduce the width of the column, then the value returned will decrease. You may need to use Data ▸ Calculate ▸ Recalculate Hard after changing the column width.


    Description of return values for the FORMAT Info Type argument
    Return value Meaning
    Numbers
    , (comma)

    Indicates that the cell is formatted to display numbers with thousands separators. The number of decimal places specified by the cell format is appended after the comma. For example:

    • With the number 123,456.7890 displayed in cell A3 (format code #,###.0000), the formula =CELL("format"; A3) returns ,4.
    F

    Indicates that the cell is formatted to display numbers without thousands separators. The number of decimal places specified by the cell format is appended after the letter F. For example:

    • With the number 123456.79 displayed in cell A4 (format code 0.00), the formula =CELL("format"; A4) returns F2.
    • With the number 123456.789 displayed in cell A5 (format code General), the formula =CELL("format"; A5) returns F0. Note that the Decimal places field on the Numbers tab of the Format Cells dialog is blank when the General format is selected.
    C

    Indicates that the cell is formatted to display numbers in a currency format. The number of decimal places specified by the cell format is appended after the letter C. For example:

    • With the value $2,345.68 displayed in cell A6 (format code [$$-409]#,##0.00;-[$$-409]#,##0.00), the formula =CELL("format"; A6) returns C2.
    S

    Indicates that the cell is formatted to display numbers in scientific (exponential) representation. The number of decimal places specified by the cell format is appended after the letter S. For example:

    • With the value 1.23E+45 displayed in cell A7 (format code 0.00E+00), the formula =CELL("format"; A7) returns S2.
    P

    Indicates that the cell is formatted to display percentages. The number of decimal places specified by the cell format is appended after the letter P. For example:

    • With the value 56.789% displayed in cell A8 (format code 0.000%), the formula =CELL("format"; A8) returns P3.
    Dates
    D1

    Date formats comprising day, month, and year values. For example:

    • With the date 12/8/21 displayed in cell A12 (format code M/D/YY), the formula =CELL("format"; A12) returns D1.
    D2

    Date formats comprising day and month (abbreviated alphabetical name) values. For example:

    • With the date Dec 08 displayed in cell A13 (format code MMM DD), the formula =CELL("format"; A13) returns D2.
    D3

    Date formats comprising month and year values. For example:

    • With the date 12/21 displayed in cell A14 (format code MM/YY), the formula =CELL("format"; A14) returns D3.
    D4

    Date and time formats comprising second, minute, hour, day, month, and year values (without AM/PM indicator). For example:

    • With the date/time 12/08/2021 20:25:46 displayed in cell A15 (format code MM/DD/YYYY HH:MM:SS), the formula =CELL("format"; A15) returns D4.
    D5

    Date format comprising day and month (numerical) values. For example:

    • With the date 12-08 displayed in cell A16 (format code MM-DD), the formula =CELL("format"; A16) returns D5.
    D6

    Time formats including second, minute, and hour values, with AM/PM indicator. For example:

    • With the time 08:33:35 PM displayed in cell A17 (format code HH:MM:SS AM/PM), the formula =CELL("format"; A17) returns D6.
    D7

    Time formats including minute and hour values, with AM/PM indicator. For example:

    • With the time 08:33 PM displayed in cell A18 (format code HH:MM AM/PM), the formula =CELL("format"; A18) returns D7.
    D8

    Time formats including second, minute, and hour values, with no AM/PM indicator. For example:

    • With the time 20:33:35 displayed in cell A19 (format code HH:MM:SS), the formula =CELL("format", A19) returns D8.
    D9

    Time formats including minute and hour values, with no AM/PM indicator. For example:

    • With the time 20:33 displayed in cell A20 (format code HH:MM), the formula =CELL("format"; A20) returns D9.
    G

    All other date and time formats. For example:

    • With the date/time 2021-12-08 20:33:35 displayed in cell A21 (format code YYYY-MM-DD HH:MM:SS), the formula =CELL("format"; A21) returns G.

    Additional notes A - (minus) is appended at the end of the returned string when the cell is formatted to display negative numbers in colour. For example:

    • With the value $12.34 displayed in cell A9 (format code [$$-409]#,##0.00;[RED]-[$$-409]#,##0.00), the formula =CELL("format"; A9) returns C2-.

    () (parentheses) are appended at the end of the returned string when the cell’s format code includes a left parenthesis. For example:

    • With the value (789) displayed in cell A10 (format code 0_);(0)), the formula =CELL("format"; A10) returns F0().
    • With the value 789.012 displayed in cell A11 (format code #,###.000_);[RED](#,###.000)), the formula =CELL("format"; A11) returns ,3-(). This complex format code shows negative values in red, surrounded by parentheses.
    • If Info Type does not correspond to one of the expected strings, then CELL reports an invalid argument error (エラー:502).
    • If Reference does not correspond to a valid reference, the CELL reports a #REF! error.
    • If Info Type is set to “CONTENTS” and Reference is omitted, then CELL reports a circular reference error (エラー:522).

    Additional details:

    None

    Examples:

    See examples within the tables in the #Arguments: section above.

    Related LibreOffice functions:

    None.

    ODF standard:

    Related (or similar) Excel functions:

    CELL