Text--->Time

Text--->Date

year year

month month

day day

date date

datedif date difference

eomonth end of month date

edit date offset

weekday day of the week

weeknum week of the year

workday

workday.intl

networkdays

networkdays.intl

hour

minute minute

second seconds

time time

Single column does not count twice

The condition can be a reference

Do not repeat calculations in multiple columns

digital area average

Average of non-empty areas

Conditional average, can calculate the average of the last few digits

Average after removing extreme values, pay attention to the second parameter

Sum merged cells

Parameter array dimensions are the same

Non-numeric elements are treated as 0

conditional array

Calculate area if selection

Relative regional conditions

wildcard

array

Reference area conditions

Note: The product area text is treated as 1

Arrays are multiplied first and then added

product of determinants

Abbreviation ^

All the above modules support

All values ​​can be calculated

It is possible to calculate only visible values

int

trunc

round

roundup

rounddown

ceiling

floor

mod

abs

base

NA generates wrong value

Multiple column rankings with brackets

arabic Rome-->Arab

roman Arabia-->Rome

iseven even

isodd odd

concat

concatenate

phonetic merge text, ignore numbers

left Taken from the left

right cut from right

mid intercepted from the middle

len character length

find

search

replace

substitute

char

code

=

exact

repeat

Convert non-text to text

? single character

* Multiple characters

~ represents wildcard character

proper

upper

lower

trim

RMB

DOLLAR

numbervalue

value

clean

numberstring

Numeric range variable

[Black], [White], [Red], [Blue], [Green], [Magenta]

Color 1....Color 56

conventional

Number placeholder, use 0 when the number is less than the code character

Numeric placeholder, only valid numbers are displayed, meaningless 0 values ​​are not displayed

Number placeholder. When the number is less than the code, a space is used to place it. It can be used for fraction display.

A text placeholder that represents the input text itself

Leave a space as wide as the following characters

Repeat for next character until column width is filled

Thousands separator, generally used in conjunction with "#" to simplify numbers

Format, force display.

Same as \, displays characters that cannot be displayed

Shrink the number 100 times

1, 2, 3

Example: yyyy-m-d

Example: AM/PM hh:mm:ss.0

Parameter is 0

Parameter is 1

2 columns

multiple columns

Available arrays for the first and third parameters

Requirements: The first column of the condition, the last column of the result

The condition does not need to be in the first column

Even if the lookup is not in ascending order, it will be treated as ascending order.

Parameter 0

Ascending order

descending order

Fuzzy matching uses the dichotomy method, which is highly efficient.

If you find duplicate values ​​in fuzzy matching, find the last position

two-dimensional area

one dimensional area

Add multi-region selection

absolute address

relative address

Sharing computers with other colleagues

number

text

Represents multi-dimensional areas, calculated separately

address

row

col

filename

contents

Select the area, the default means the upper left cell of the mouse area

Generate oblique angle matrix

isformula is a formula

Whether isref refers to

isblank is blank

islogical is a logical value

First parameter: What field is required?

Second parameter: In which pivot table to find

The third parameter: initially determine which category

The fourth parameter: specific category and condition

Other conditions...

1. Ensure pivotdata is open in the analysis menu bar

2. Reference pivot table cells

3. Modify the text condition to a reference

The formula is simple and efficient

Refresh data as pivot table refreshes