Buzz
Excel is a spreadsheet program provided by Microsoft in the Office software suite. Users input data into Excel in table format, utilizing functions for calculations and data processing. Each function has distinct functionalities, and there are advanced functions to handle more complex data. Mytour introduces you to some advanced functions in Excel along with specific examples.
Explore advanced functions in Excel
1. VLOOKUP Function
VLOOKUP function is one of the commonly used advanced functions in Excel with versatile applications. It is primarily used for data lookup and retrieval in a spreadsheet.
Syntax: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
lookup_value: Value used for lookup
table_array: Table array to search, using absolute values with $ sign
col_index: Order of columns to retrieve data from the search table
range_lookup: Search range
Explore various examples of the basic functionalities of the VLOOKUP function by referring to how to use the VLOOKUP function in Excel.
2. Choose Function
Choose Function is an advanced Excel function that returns a value from a list of arguments. The Choose function can be used in all Excel versions: 2016, 2013, 2010, 2007, 2003.
Syntax: CHOOSE(index_num, value1, [value2], ...)
Index_num: Position of the value to return
Value: Values in the list, up to 255 values.
Example: Find the score of the person in the 2nd position in the list of math scores.
3. DCOUNTA Function
DCOUNTA function counts non-empty cells in a list or table with specified criteria.
DCOUNTA is an advanced Excel function commonly used to count non-empty cells in a list or table with specified criteria.
Syntax: DCOUNTA(database, field, criteria)
Database: Data range including columns, cells, lists, databases
Field: Specifies the column in the function, column header
Criteria: Range of cells containing conditions.
Example: Count the number of students with Excellent Ranking in the score table, in the Ranking column.
The result is 2 - the number of students with an Excellent ranking in the score table with the condition Excellent in the Ranking column. You can use the statement as in the example below.
4. DGET Function
The DGET function is one of the advanced Excel functions widely used in Excel operations. DGET allows users to extract a value from a database.
Syntax : DGET(database, field, criteria)
Database: Data range including columns, cells, lists, databases
Field: Specifies the column in the function, column header
Criteria: Range of cells containing conditions.
Note: Error #VALUE: No value satisfies the condition
Error #NUM: Too many records match the condition
Example: Retrieve the value in the table with the condition of a total score of 12
The result is 12 - the value in the table that satisfies the condition of a score of 12 in the Total Score column.
5. DSUM Function
DSUM Function is an advanced Excel function that calculates the sum of a field or column in a database with specified criteria.
Syntax : DSUM(database, field, criteria)
Database: Data range including columns, cells, lists, databases
Field: Specifies the column in the function, column header
Criteria: Range of cells containing conditions.
Example: Calculate the total score of students with the gender Male
The result is 108 - the total score of students with the Male gender in the Total Score column.
6. INDEX Function
INDEX is an advanced Excel function with the ability to return an array in Excel, retrieving results in the form of an array intersecting the value of a cell and a row.
INDEX has two forms: array and reference.
You can explore more examples of the INDEX function and usage instructions for each case by referring to how to use the INDEX function in Excel
In Excel, there are numerous calculation and statistical functions, including both advanced and basic functions. Mastering basic functions in Excel will make it easier for you to learn advanced functions. So, what are the basic functions in Excel? Explore the answer on Mytour for a comprehensive understanding.
