Excel 好用公式、函數

Formulas

VLOOKUP：VLOOKUP is used to get the data for a lookup value in the leftmost column of a section of your spreadsheet called the table array on the base index_number (column number).

HLOOKUP：HLOOKUP is used to get the data for a lookup value in the top most row of a section of your spreadsheet called the table array on the base index_number (row number).

MATCH：This formula searches for a value in an array and returns the relative position of that item

INDEX：This formula gets a value from a table on the base of given row and column parameter.

INDIRECT：This formula returns the reference to a cell or range on the base of its string parameter.

OFFSET：This formula returns a reference to a range that is offset number of rows and columns.

CHOOSE：This formula gets a value from an array list on the based on given parameter.

ADDRESS：This formula returns a cell address in text format.

String/Text Functions

FIND：This formula returns a position of text or character in a text. This is a case sensitive.

LEN：This formula returns the number of characters (including space) of a text.

MID：This function returns a part of text or a character form a text on the base of parameter.

SUBSTITUTE：This formula replaces the fully or partially text with another text on the base parameter

TEXT：This formula returns a value converted to text with a specified format on the base of parameter.

TEXTJOIN：This formula is used to combine 2 or more texts together separated by a delimiter as given in the parameter.

TRIM：This formula removes the leading and trailing spaces from a text.

Date/Time Functions

DATE：This formula returns the create a date from given parameter of year, month and day.

DATEVALUE：This formula converts the text (should be in form of date) into a date.

EOMONTH：This formula calculates the last day of the month after adding a specified number of months to a date as given in the parameter.

NETWORKDAYS：This formula returns the number of work days between 2 dates, excluding Saturday and Sunday and holidays.

NOW：This formula returns the current system date and time

TODAY：This formula returns the current system date only.

Others

AVERAGEIF：This formula returns the average of all numbers in a range of cells, on the base of given criteria in the parameter.

COUNTIF：This formula returns the number of cells in a range on the base of given criteria in the parameter.

COUNTIFS：This formula returns the number of cells in a range on the base of multiple criteria in the parameter.

SUMIF：This formula returns the total all numbers in a range of cells on the base of given criteria in the parameter.

SUMIFS：This formula returns the total all numbers in a range of cells on the base of multiple criteria in the parameter.

SUMPRODUCT：This formula sum of multiplies the corresponding items in the arrays.

資料來源：https://www.pk-anexcelexpert.com
First Posted：2019/08/16