엑셀 함수 정리IT information sea
Home – LIFE – Excel Function Summary 2023 (Microsoft Excel)
This is a summary of Excel functions.
Excel has become an essential tool in the modern office environment and is used in various fields such as spreadsheet organization and data statistics.
Among Excel’s useful functions, it seems impossible to leave out the function function when explaining the function that increases productivity the most.
A function is a function that allows you to simply use complex or long formulas in the form of short commands.
In this article, we briefly summarize the functions, which are useful functions of Excel, in a table.
The arithmetic function is the most basic and frequently used function.
These are functions that allow you to easily obtain numerical statistics such as the sum, average, maximum value, and minimum value of data.
Text functions are used to manipulate data in strings.
Date and time functions are used to manipulate date and time-related data.
Logical functions process data based on conditions set by the user.
Various operations can be processed, such as when a condition is met, when a condition is not met, when both conditions are met, or when only one condition is met.
Lookup and reference functions perform functions such as finding the value the user wants within a certain data range or returning the value at the search location.
Since lookup and reference functions are difficult to use, we will look into them in more detail through additional explanations and examples.
VLOOKUP is used to find the value the user wants within a given range.
For example, assume you have the following table:
The first table above is a table that sets out the salary for each position and year in the company.
The second table contains information about employees working at the company.
Suppose you need to find the salaries of employees in the second table by referring to the first table.
Then, we need to find the salary corresponding to the employee’s information in the salary table with the employee’s rank and annual leave information.
At this time, you can use the VLOOKUP function.
First, let’s find out Assistant Manager Kim Mlitschko’s salary.
1.=VLOOKUP(row value to find, specified range, column to find, match or not)
The row values listed in the salary table are the employee’s job title. Since we need to find Assistant Manager Kim Mㅁㅁ’s salary, Assistant Manager Kim Mㅁㅁ’s rank, “Assistant Manager,” is entered as the first argument value of the VLOOKUP function.
2.=VLOOKUP(row value to find, specified range, column to find, match or not)
Specifies the specified range to find the value. Since we need to find salary values, this will naturally be a salary table.
3.=VLOOKUP (row value to find, specified range, column to find, match or not)
Specifies the column to find. The columns in the salary table contain annual leave information.
Therefore, you only need to specify where the 2nd year information is located. The first column of the specified range contains the position information, and the 1st year starts from the second column. Therefore, you can specify it as the (annual leave + 1) column.
4.=VLOOKUP(row value to find, specified range, column to find, match or not)
The final match determines whether there is a perfect match. In general, 0 is entered because the value 0 is entered to find a perfect match.
Finally:
=VLOOKUP(position, designated range, annual leave+1, 0) → 310
For HLOOKUP, the rows and columns are reversed in the example above.
Retrieves values by column value and row number.
INDEX is a function that finds values within a specified range through a user-specified matrix number argument.
This function is simple to use.
=INDEX(A1:C5, 2,1)
If you use the function as above, it returns the value located in the 2nd row and 1st column within the specified range.
The MATCH function returns the position at which the value to be found is located within a specified range.
At this time, the range entered into MATCH must be a one-dimensional array arranged in a row, such as one column or one row.
We learned about Excel functions.
As long as you learn the above Excel function table well, you will be able to do most Excel tasks without difficulty.
More information on Excel functions can be found on the following Microsoft official page.
Excel functions (by category) – Microsoft Support
Use Excel more efficiently with Excel functions!
Comment
Save my name, email, and website in this browser for the next time I comment.
