MindMap Gallery Comprehensive list of commonly used excel functions
Excel workplace office, common functions for data analysis, classification analysis, users can complete data processing and calculation work more quickly and accurately, improving work efficiency!
Edited at 2024-04-18 17:30:24One Hundred Years of Solitude is the masterpiece of Gabriel Garcia Marquez. Reading this book begins with making sense of the characters' relationships, which are centered on the Buendía family and tells the story of the family's prosperity and decline, internal relationships and political struggles, self-mixing and rebirth over the course of a hundred years.
One Hundred Years of Solitude is the masterpiece of Gabriel Garcia Marquez. Reading this book begins with making sense of the characters' relationships, which are centered on the Buendía family and tells the story of the family's prosperity and decline, internal relationships and political struggles, self-mixing and rebirth over the course of a hundred years.
Project management is the process of applying specialized knowledge, skills, tools, and methods to project activities so that the project can achieve or exceed the set needs and expectations within the constraints of limited resources. This diagram provides a comprehensive overview of the 8 components of the project management process and can be used as a generic template for direct application.
One Hundred Years of Solitude is the masterpiece of Gabriel Garcia Marquez. Reading this book begins with making sense of the characters' relationships, which are centered on the Buendía family and tells the story of the family's prosperity and decline, internal relationships and political struggles, self-mixing and rebirth over the course of a hundred years.
One Hundred Years of Solitude is the masterpiece of Gabriel Garcia Marquez. Reading this book begins with making sense of the characters' relationships, which are centered on the Buendía family and tells the story of the family's prosperity and decline, internal relationships and political struggles, self-mixing and rebirth over the course of a hundred years.
Project management is the process of applying specialized knowledge, skills, tools, and methods to project activities so that the project can achieve or exceed the set needs and expectations within the constraints of limited resources. This diagram provides a comprehensive overview of the 8 components of the project management process and can be used as a generic template for direct application.
function
date time
shortcut key
ctrl shift ~ normal
ctrl shift # date
ctrl shift @ time
ctrl; current date
ctrl shift; current time
text conversion
timevalue
Text--->Time
datevalue
Text--->Date
date
Extract splicing
year year
month month
day day
date date
moon
datedif date difference
The third parameter: y,m,d,ym,md,yd
eomonth end of month date
Parameters: date, offset month
edit date offset
Offset month, day unchanged
week
weekday day of the week
Pay attention to the second parameter
weeknum week of the year
working day
workday
Example: Date after 3 working days
workday.intl
Customized, 1 means rest, 0 means work. Example: weekends: "0000011"
networkdays
Working days between two dates
networkdays.intl
Working days between two dates, custom working days
Calendar production
time
Extract splicing
hour
minute minute
second seconds
time time
Numeric functions
count
count counts numeric cells
counta counts non-null
countblank counts blanks
counta(area&"") counts all cells
countif
Single column does not count twice
The condition can be a reference
countifs
Do not repeat calculations in multiple columns
average
average
digital area average
averagea
Average of non-empty areas
averageif
Conditional average, can calculate the average of the last few digits
trimmean
Average after removing extreme values, pay attention to the second parameter
Sum
sum
Sum merged cells
sumproduct
Parameter array dimensions are the same
Non-numeric elements are treated as 0
sumif
conditional array
Calculate area if selection
Relative regional conditions
sumifs
wildcard
array
Reference area conditions
product class
product times
Note: The product area text is treated as 1
sumproduct
Arrays are multiplied first and then added
mmult
product of determinants
power
Abbreviation ^
sqrt square root
subtotal
Function
All the above modules support
advantage
All values can be calculated
It is possible to calculate only visible values
Hidden cells
Filter cells
digital operations
intercept
int
Round down
trunc
Truncated
rounding
round
<5 is discarded, >=5 is carried away from 0
roundup
All carry away from 0
rounddown
Same as trunc
Taking multiples
ceiling
Take the number upward according to the multiple of the parameter
floor
Take the number downward according to the multiple of the parameter
Take remainder
mod
The signs are the same, find the remainder
The signs are different, find the difference part
The result value is the same as the sign of the divisor
absolute value
abs
Base conversion
base
error value
NA generates wrong value
rank ranking
Multiple column rankings with brackets
Arabic to Roman conversion
arabic Rome-->Arab
roman Arabia-->Rome
positive and negative sign
Determine parity
iseven even
isodd odd
N function text to 0
text
String operation class
String concatenation
concat
concatenate
phonetic merge text, ignore numbers
String interception
left Taken from the left
right cut from right
mid intercepted from the middle
String query
len character length
lenb length in bytes
find
Returns character position, case sensitive findb bytes
search
Same as find, case-insensitive, wildcards can be used searchb bytes
Character replacement
replace
Replace a value, the parameter is 0 to insert characters
substitute
Find characters and replace
Numeric character conversion
char
Number conversion character
code
Convert characters to numbers
Character comparison
=
not case sensitive
exact
case sensitive
rept
repeat
T
Convert non-text to text
wildcard
? single character
* Multiple characters
~ represents wildcard character
String format class
English uppercase and lowercase
proper
The first letter of each word is capitalized, the rest are lowercase
upper
all caps
lower
all lowercase
Remove spaces
trim
Remove spaces from both ends of string
RMB USD
RMB
Digital conversion to RMB format
DOLLAR
Convert numbers to dollar format
text conversion
numbervalue
Text to Number
value
Convert text and date to numbers
clean
Clear non-printing characters, used when calculation cannot be done
numberstring
Convert numbers to Chinese characters
cell format
Content format
[>0] format; [<0] format; 0 format; text format
Numeric range variable
font color
[Color 10][>50]"Excellent";[Magenta][<20]"Poor";"Qualified";
[Black], [White], [Red], [Blue], [Green], [Magenta]
Color 1....Color 56
Placeholder
G/general format
conventional
0
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
special code
_
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
Chinese character format
[dbnum1]G/general format
1, 2, 3
date time
date
Example: yyyy-m-d
time
Example: AM/PM hh:mm:ss.0
TEXT function
The text function can directly take parameters in the above format
Find matches
vlookup
exact match
Parameter is 0
fuzzy matching
Parameter is 1
reverse match
2 columns
if({1,0},Column 1,Column 2)
multiple columns
choose({1,2,3...},Column 1,Column 2,Column 3...)
array
Available arrays for the first and third parameters
hlookup
Same as vlookup, horizontal
lookup
Two parameters
Requirements: The first column of the condition, the last column of the result
three parameters
The condition does not need to be in the first column
Ascending
Even if the lookup is not in ascending order, it will be treated as ascending order.
binary search
match
exact match
Parameter 0
fuzzy matching
Ascending order
Parameter 1
descending order
Parameter-1
dichotomy·
Fuzzy matching uses the dichotomy method, which is highly efficient.
If you find duplicate values in fuzzy matching, find the last position
index
three parameters
two-dimensional area
one dimensional area
four parameters
Add multi-region selection
choose
Equivalent to java's switch
Custom areas can be created
frequency
Distribution function
hyperlink
website link
files, folders
absolute address
relative address
local area network
Sharing computers with other colleagues
small
Minimum number extraction
large
Maximum number extraction
mode.mult
mode
number
text
Convert numbers using match
median
middle number
area function
offset
area shift
array
Represents multi-dimensional areas, calculated separately
address
Number-->Address
indirect
Address-->Area
rows
The number of rows occupied by the region
areas
The reference area contains the number of discontinuous areas
cell
first parameter
address
text address
row
OK
col
List
filename
file name
contents
content
Second parameter
Select the area, the default means the upper left cell of the mouse area
array
transpose transpose
munit
Generate oblique angle matrix
area crossover
Calculate intersection value with definition name
define name
Create from selected content
logic
xor
Judgment function
isformula is a formula
Whether isref refers to
isblank is blank
islogical is a logical value
Pivot table functions
getpivotdata
Format
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...
method
1. Ensure pivotdata is open in the analysis menu bar
2. Reference pivot table cells
3. Modify the text condition to a reference
advantage
The formula is simple and efficient
Reference pivot table data directly instead of the data source
Refresh data as pivot table refreshes
circular reference
process
1. Set up the iterator
2. Set switches and counters
3. Write formulas
Database functions
Example
DSUM(data range, column, condition...)
Features
Conditions that can be applied to cells
data area
Quote
Data only refers to column numbers starting from the first row
List
Support array
condition
Supports wildcards
No "=" means inclusion
"=" means equal to
Blank cells represent all data