Difference between revisions of "Documentation/How Tos/Calc: Spreadsheet functions"

From Apache OpenOffice Wiki
Jump to: navigation, search
(Initial content)
 
m
 
(15 intermediate revisions by 5 users not shown)
Line 1: Line 1:
−
==List of Calc 'Spreadsheet' functions==
+
{{Documentation/CalcFunc SpreadsheetTOC
 +
|ShowPrevPage={{{ShowPrevPage}}}
 +
|PrevPage={{{PrevPage}}}
 +
|ShowNextPage={{{ShowNextPage}}}
 +
|NextPage={{{NextPage}}}
 +
|bookid=CalcFunctions
 +
|booktitle=<div style="padding: 8px; font-size: 140%; font-weight: bold; background-color: #9BC0F5;">[[Documentation/Reference/Calc_functions|CALC FUNCTIONS]]</div>
 +
|ShowParttitle=block
 +
|parttitle=[[Documentation/How_Tos/Calc:_Spreadsheet_functions|<div style="font-size: 140%;">Spreadsheet Functions]]
 +
|ShowPrevPart=block
 +
|PrevPart=Documentation/How_Tos/Calc:_Mumber_Conversion_functions
 +
|ShowNextPart=block
 +
|NextPart=Documentation/How_Tos/Calc:_Statistical_functions
 +
|toccontent= <div style="padding: 4px; font-size: 130%; font-weight: hidden; background-color:#DCE9FC;">FUNCTIONS</div>
 +
}}__NOTOC__
 +
 
 +
[[File:open.jpg]]
 +
 
 +
==<big>List of Calc 'Spreadsheet' functions</big>==
 
The so-called 'Spreadsheet' functions find values in tables, or cell references; they include '''LOOKUP''', '''SEARCH''', '''ADDRESS'''. They could thus also be described as 'Lookup' functions.
 
The so-called 'Spreadsheet' functions find values in tables, or cell references; they include '''LOOKUP''', '''SEARCH''', '''ADDRESS'''. They could thus also be described as 'Lookup' functions.
 +
  
 
{| border="0" cellpadding="0" cellspacing="10" align="left"
 
{| border="0" cellpadding="0" cellspacing="10" align="left"
 +
 +
|-valign="top"
 +
|colspan="2"|'''<big> Spreadsheet Lookup functions</big>'''
 +
 
|-valign="top"
 
|-valign="top"
 
|[[Documentation/How_Tos/Calc: ADDRESS function|'''ADDRESS''']]
 
|[[Documentation/How_Tos/Calc: ADDRESS function|'''ADDRESS''']]
Line 8: Line 31:
  
 
|-valign="top"
 
|-valign="top"
−
|[[Documentation/How_Tos/Calc: AREAS function|'''AREAS''']]
+
|[[Documentation/How_Tos/Calc: CHOOSE function|'''CHOOSE''']]
−
|returns the number of individual ranges in a multiple range.
+
|returns a value from a list, given an index number.
  
 
|-valign="top"
 
|-valign="top"
−
|[[Documentation/How_Tos/Calc: DDE function|'''DDE''']]
+
|[[Documentation/How_Tos/Calc: HLOOKUP function|'''HLOOKUP''']]
−
|returns information from other documents and applications, using the "DDE" protocol.
+
|returns a value from a table row, in the column found by lookup in the first row.
−
 
+
−
|-valign="top"
+
−
|[[Documentation/How_Tos/Calc: ERRORTYPE function|'''ERRORTYPE''']]
+
−
|returns the number corresponding to an error value.
+
  
 
|-valign="top"
 
|-valign="top"
 
|[[Documentation/How_Tos/Calc: INDEX function|'''INDEX''']]
 
|[[Documentation/How_Tos/Calc: INDEX function|'''INDEX''']]
−
|returns the contents of a cell, given row and column number.
+
|returns a value from a table, given a row and column number.
  
 
|-valign="top"
 
|-valign="top"
Line 28: Line 47:
  
 
|-valign="top"
 
|-valign="top"
−
|[[Documentation/How_Tos/Calc: INFO function|'''INFO''']]
+
|[[Documentation/How_Tos/Calc: LOOKUP function|'''LOOKUP''']]
−
|returns information about the current working environment.  
+
|returns result from one single-cell-wide table, corresponding to a lookup search in another.
  
 
|-valign="top"
 
|-valign="top"
−
|[[Documentation/How_Tos/Calc: COLUMN function|'''COLUMN''']]
+
|[[Documentation/How_Tos/Calc: MATCH function|'''MATCH''']]
−
|returns the column number, given a reference.
+
|returns the position of a search item in a single row or column table.
  
 
|-valign="top"
 
|-valign="top"
−
|[[Documentation/How_Tos/Calc: COLUMNS function|'''COLUMNS''']]
+
|[[Documentation/How_Tos/Calc: OFFSET function|'''OFFSET''']]
−
|returns the number of columns in a given reference.
+
|returns a modified reference, given a reference, an offset, and a desired size.
  
 
|-valign="top"
 
|-valign="top"
Line 43: Line 62:
 
|returns a value from a table column, in the row found by lookup in the first column.
 
|returns a value from a table column, in the row found by lookup in the first column.
  
−
|-valign="top"
 
−
|[[Documentation/How_Tos/Calc: SHEET function|'''SHEET''']]
 
−
|returns the sheet number, given a reference.
 
  
 
|-valign="top"
 
|-valign="top"
−
|[[Documentation/How_Tos/Calc: SHEETS function|'''SHEETS''']]
+
|colspan="2"|'''<big> Spreadsheet Information functions</big>'''
−
|returns the number of sheets, given a reference.
+
  
 
|-valign="top"
 
|-valign="top"
−
|[[Documentation/How_Tos/Calc: MATCH function|'''MATCH''']]
+
|[[Documentation/How_Tos/Calc: AREAS function|'''AREAS''']]
−
|returns the position in a single row or column table matching a search criterion.
+
|returns the number of areas in a given reference.
  
 
|-valign="top"
 
|-valign="top"
−
|[[Documentation/How_Tos/Calc: OFFSET function|'''OFFSET''']]
+
|[[Documentation/How_Tos/Calc: COLUMN function|'''COLUMN''']]
−
|returns the contents of a cell, given a reference and a desired offset from that reference.
+
|returns the column number(s), given a reference.
  
 
|-valign="top"
 
|-valign="top"
−
|[[Documentation/How_Tos/Calc: LOOKUP function|'''LOOKUP''']]
+
|[[Documentation/How_Tos/Calc: COLUMNS function|'''COLUMNS''']]
−
|returns result from one single-cell-wide table, corresponding to a lookup search in another.
+
|returns the number of columns in a given reference.
  
 
|-valign="top"
 
|-valign="top"
−
|[[Documentation/How_Tos/Calc: STYLE function|'''STYLE''']]
+
|[[Documentation/How_Tos/Calc: ERRORTYPE function|'''ERRORTYPE''']]
−
|applies a style to a cell (for example a colour).
+
|returns the number corresponding to an error value.
  
 
|-valign="top"
 
|-valign="top"
−
|[[Documentation/How_Tos/Calc: CHOOSE function|'''CHOOSE''']]
+
|[[Documentation/How_Tos/Calc: INFO function|'''INFO''']]
−
|returns a value from a list, given an index number.
+
|returns information about the current working environment.  
−
 
+
−
|-valign="top"
+
−
|[[Documentation/How_Tos/Calc: HLOOKUP function|'''HLOOKUP''']]
+
−
|returns a value from a table row, in the column found by lookup in the first row.
+
  
 
|-valign="top"
 
|-valign="top"
 
|[[Documentation/How_Tos/Calc: ROW function|'''ROW''']]
 
|[[Documentation/How_Tos/Calc: ROW function|'''ROW''']]
−
|returns the row number, given a reference.
+
|returns the row number(s), given a reference.
  
 
|-valign="top"
 
|-valign="top"
 
|[[Documentation/How_Tos/Calc: ROWS function|'''ROWS''']]
 
|[[Documentation/How_Tos/Calc: ROWS function|'''ROWS''']]
 
|returns the number of rows  in a given reference.
 
|returns the number of rows  in a given reference.
 +
 +
|-valign="top"
 +
|[[Documentation/How_Tos/Calc: SHEET function|'''SHEET''']]
 +
|returns the sheet number, given a reference.
 +
 +
|-valign="top"
 +
|[[Documentation/How_Tos/Calc: SHEETS function|'''SHEETS''']]
 +
|returns the number of sheets in a given reference.
 +
 +
 +
|-valign="top"
 +
|colspan="2"|'''<big>Other functions</big>'''
 +
 +
|-valign="top"
 +
|[[Documentation/How_Tos/Calc:  DDE function|'''DDE''']]
 +
|returns information from other documents and applications, using the "DDE" protocol.
  
 
|-valign="top"
 
|-valign="top"
 
|[[Documentation/How_Tos/Calc: HYPERLINK function|'''HYPERLINK''']]
 
|[[Documentation/How_Tos/Calc: HYPERLINK function|'''HYPERLINK''']]
 
|sets a cell to open a hyperlink (in another application) when clicked.
 
|sets a cell to open a hyperlink (in another application) when clicked.
 +
 +
|-valign="top"
 +
|[[Documentation/How_Tos/Calc: STYLE function|'''STYLE''']]
 +
|applies a style (for example a colour) to a cell.
 +
 
|}
 
|}
 
<br style="clear:both;" />
 
<br style="clear:both;" />
 
<!-- why on earth is this necessary? -->
 
<!-- why on earth is this necessary? -->
  
−
==See also==
+
{{SeeAlso|EN|
−
 
+
* [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]],
−
'''[[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]'''
+
* [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]]
 +
}}
 +
[[Category: Documentation/Reference/Calc]]

Latest revision as of 16:41, 1 February 2024

Spreadsheet Functions
FUNCTIONS
Spreadsheet Lookup functions
Spreadsheet Information functions
Other functions
[[{{{PrevPage}}}|< Previous Page

]]

[[{{{NextPage}}}|Next Page 
>]]



Open.jpg

List of Calc 'Spreadsheet' functions

The so-called 'Spreadsheet' functions find values in tables, or cell references; they include LOOKUP, SEARCH, ADDRESS. They could thus also be described as 'Lookup' functions.


Spreadsheet Lookup functions
ADDRESS returns a text representation of a cell reference, given row and column numbers.
CHOOSE returns a value from a list, given an index number.
HLOOKUP returns a value from a table row, in the column found by lookup in the first row.
INDEX returns a value from a table, given a row and column number.
INDIRECT returns a reference, given a text string.
LOOKUP returns result from one single-cell-wide table, corresponding to a lookup search in another.
MATCH returns the position of a search item in a single row or column table.
OFFSET returns a modified reference, given a reference, an offset, and a desired size.
VLOOKUP returns a value from a table column, in the row found by lookup in the first column.


Spreadsheet Information functions
AREAS returns the number of areas in a given reference.
COLUMN returns the column number(s), given a reference.
COLUMNS returns the number of columns in a given reference.
ERRORTYPE returns the number corresponding to an error value.
INFO returns information about the current working environment.
ROW returns the row number(s), given a reference.
ROWS returns the number of rows in a given reference.
SHEET returns the sheet number, given a reference.
SHEETS returns the number of sheets in a given reference.


Other functions
DDE returns information from other documents and applications, using the "DDE" protocol.
HYPERLINK sets a cell to open a hyperlink (in another application) when clicked.
STYLE applies a style (for example a colour) to a cell.



See Also
Retrieved from "https://wiki.openoffice.org/w/index.php?title=Documentation/How_Tos/Calc:_Spreadsheet_functions&oldid=259965"
Views
Personal tools
Navigation
Tools