Advanced functions in Excel are tools for users to perform calculations, process data in tables. Each function in excel has its own tasks and functions that help perform different data processing functions. Taimienphi will guide advanced functions commonly used in excel such as Vlookup Function, DCOUNTA Function, DGET Function, Choose Function … to be able to serve his work well …
Excel is a spreadsheet program provided by Microsoft in the Office suite. Users will enter data into Excel in tabular form, using functions to calculate as well as process data. Each function will have its own functions, including advanced functions to handle more complex data. Taimienphi will introduce you to some advanced functions in Excel with specific examples.
Advanced functions in Excel
1. VLOOKUP function
Vlookup function is one of the most common advanced functions in excel with many applications, VLookup function is most used in finding and looking up data in a spreadsheet.
Syntax: VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
lookup_value: The value used to find
table_array: Value table for detection, using absolute value with $ sign
col_index: The order of columns to get data in the table of trace values
range_lookup: Search scope
To see a summary of different examples of the basic functions of the VLOOKUP function, please refer to how to use
2. Choose function
The Choose function is one of the advanced functions in excel that returns 1 value in a list of arguments. Choose function can be used on all versions of Excel 2016, 2013, 2010, 2007, 2003
Syntax : CHOOSE(index_num, value1, [value2], …)
Index_num : Location of the return value
Value : The values in the list, up to 255 values.
Example : Find your score ranked 2nd on the list of students with math scores
The result returned is 5 – is the value of the 2nd position in the list of selected cells
3. DCOUNTA function
The DCOUNTA function has the function of counting nonblank cells in a list or table with specified condition
DCOUNTA is an advanced function in excel that is commonly used to count nonblank cells in a list or table with specified condition
Syntax : DCOUNTA(database, field, criteria)
Database : The range of data includes columns, cells, lists, databases
Field : Specify the column in the function, column header
Criteria : The range of cells that contain conditions.
Example : Count the number of students who achieved Good Grades in the transcript, Grade column
The result returned is 2 – is the number of students who graded well on the transcript under the condition of Good in the Grade column. You use the command as in the example below.
4. DGET function
DGET function is one of the advanced Excel functions used a lot in the process of manipulating Excel, DGET function allows users to extract a value from a database
Syntax DGET(database, field, criteria)
Database : The range of data includes columns, cells, lists, databases
Field : Specify the column in the function, column header
Criteria : The range of cells containing conditions
Note: Error #VALUE: No value meets condition
Error #NUM: There are too many record matching conditions
Example: Get the value in the table with the condition that the total score is 12
The result returned is 12 – is the value in the table that satisfies the score condition 12 in the Total score column
5. DSUM function
DSUM function is an advanced excel function that sums 1 field or 1 column in the database with given conditions
Syntax DSUM(database, field, criteria)
Database : The range of data includes columns, cells, lists, databases
Field : Specify the column in the function, column header
Criteria : The range of cells containing conditions
Example: Calculate the total score of students with male gender
The result returned is 108 – is the sum of the scores of the male gender in the total score column
6. INDEX function
INDEX is an advanced function in Excel that returns arrays in excel, getting results as arrays that are the intersection between the values of cells and rows.
The INDEX function has 2 forms: array and reference
Array-type INDEX: (Array,Row_num,[Column_num])
INDEX(Reference,Row_num,[Column_num],[Area_num])
Reference: Reference area
Array: A range of cells or arrays
Row_num: Select the row in the array from which to enter a value
Column_num: Select the column in the array from which to enter a value
Area_num: Number of areas of the cell that will return the value
You can see more examples of the Index function and instructions for using each case by referring to how to use
Above are some advanced functions in excel with specific examples. These advanced functions are not only used independently but can also be combined with other conditional functions such as COUNTIF, IF, AND… to support you to calculate faster with complex calculations when processing data on Excel spreadsheets. You can also refer to
https://thuthuat.taimienphi.vn/cac-ham-nang-cao-trong-excel-co-vi-du-cu-the-22864n.aspx
In Excel there are many different calculation and statistical functions, there are both advanced functions and basic functions, using basic functions in Excel proficiently will help you learn advanced functions more easily, so these