Home / Office

Design Tab Edit Chart

Syntax: ISNA(value)

Returns TRUE if value is #N/A error, otherwise returns FALSE.

3. ISNUMBER function

Syntax: ISNUMBER(value)

Returns TRUE if value is a numeric value, otherwise returns FALSE.

Maybe you are interested!

4. ISTEXT function

Syntax: ISTEXT(value)

Returns TRUE if value is a string, otherwise returns FALSE.

2.2.3.7. DATABASE FUNCTIONS

Used to return a value from the database according to a certain condition.

To use database functions we need to have a criteria domain.

DSUM sum function:

Calculate the sum of a column ( Field ) of the Database area ( Database ) in rows that satisfy the conditions in the criteria area ( Criteria ).

Syntax:

DSUM(Database, Field, Criteria)

(This function is similar to the sumif function )

Example: Calculate the total salary of people with the position of TP and the number of children less than or equal to 2 (BT 13).


Figure 2.32 – Example illustrating the DSUM function

Note :

We can use the column number to calculate the sum instead of the field address or we can also use the column name string instead.

The formula could be:


DMAX function:

=DSUM(A4:H14,8,J4:K5)

Or :

=DSUM(A4:H14,“Salary”,J4:K5)

Calculate the largest value in a column ( Field ) of the Database area ( Database ) in rows that satisfy the conditions in the criteria area ( Criteria ).

Syntax:

DMAX(Database , Field , Criteria )

Example 1: Calculate the highest salary of people whose working day is 26.

Example 2: Calculate the highest salary of people with KT code B and the number of children less than or equal to 2




DMIN function:

Figure 2.33 - Example illustrating the DMAX function

Calculate the smallest value on a column ( Field ) of the Database area ( Database ) in rows that satisfy the conditions in the criteria area ( Criteria ).

Syntax:

DMIN(Database , Field , Criteria )

Example 1: Find the lowest salary of people with PP positions or people with less than 2 children

Example 2: Find the lowest salary of people with KT code A and working days

<27 .

( Perform similar to the above functions )

DAVERAGE function:

Calculate the average value on a column ( Field ) of the Database area ( Database ) in rows that satisfy the conditions in the criteria area ( Criteria ).

Syntax:

DAVERAGE(Database , Field , Criteria )

Example: Calculate the average salary of people who hold the position of employee and have more than 2 children.

DCOUNT function:

Count the number of numeric elements in a column ( Field ) of the Database area in rows that satisfy the conditions in the Criteria area .

Syntax:

DCOUNT(Database , Field , Criteria )

For example: How many employees have children greater than 2?

DCOUNTA function:

Count the number of non-blank elements in a column ( Field ) of the Database area in rows that satisfy the conditions in the Criteria area .

Syntax:

DCOUNTA(Database , Field , Criteria )

For example: How many people have the position of NV?

2.2.3.8. SEARCH AND REFERENCE FUNCTIONS

1. VLOOKUP function

Function : Finds the lookup_value in the leftmost column of the table_array according to the range_lookup search criteria , returns the corresponding value in the col_index_num column (if found).

Syntax:


VLOOKUP( lookup_value , Table _ array , col _ index _ num , range _ lookup )

- range_lookup = 1: Relative search, the list of lookup values ​​of the Table_array table must be sorted in ascending order. If not found, the largest value will be returned but smaller than lookup_value.

- range_lookup = 0: Find exactly, the list of lookup values ​​of the Table_array table does not need to be sorted. If the search is not found, the #N/A error will be returned.




2. HLOOKUP function

Figure 2.34 - Example illustrating the VLOOKUP function

- Function : Find lookup_value in the top row of table_array according to range_lookup search criteria , return corresponding value in row_index_num ( if found).

Syntax :​

HLOOKUP( lookup_value , Table _ array , row _ index _ num , range _ lookup )


The meaning of the arguments of the Hlookup function is similar to the Vlookup function.

For example:



3. MATCH function

Figure 2.35 - Example illustrating the HLOOKUP function

- Function : The function returns the position of lookup_value in the array.

lookup_array by match_type

- Syntax:


MATCH( lookup_value, lookup_array, match_type )


match_type = 1: Relative search, the list of lookup values ​​of the Table_array table must be sorted in ascending order. If not found, the position of the largest value but smaller than lookup_value will be returned.

match_type = 0: Find exactly, the list of searched values ​​of the Table_array table does not need to be sorted. If the search is not found, the #N/A error will be returned.

match_type = -1: Relative search, the list must be sorted by the lookup values ​​of the Table_array in descending order. If not found, the position of the smallest value but greater than lookup_value will be returned.

For example:


4. INDEX function

Function: returns the value in the cell in row_num , column_num in the array array .

Syntax:


INDEX( array , row _ num , column _ num )




2.2.4. Chart

Figure 2.36 - Example illustrating the INDEX function

Chart Concept

A chart is a visual representation of numbers and data, helping readers grasp information intuitively.

Data block

To create a chart, there must be at least 2 different numeric data cells. Usually, the rows or columns of numbers will be accompanied by a row or column of string data, these string cells are called the heading of the numeric data group (Category label).

Components of a chart

Chart Title: Title of the chart.

X title: X axis title.

Y title: Y axis title.

Category label: Digital data title.

Data series: The graph's representation.

Legend: Comparison data series.

Types of charts

− Column chart: is a vertical column chart that displays data that changes over time or compares between items. In Column, there are different types of charts such as: 2D column chart, 3D column chart, stacked column chart and 3D stacked column chart.

− Horizontal bar chart (Bar): is a horizontal column chart, similar to Column but organized vertically and with horizontal values.

− Line chart: is a line chart that can show trends over time with markers at each data value. In Line charts, there are many types of charts such as: line charts, marked line charts, stacked line charts, 3D line charts, etc.

− Pie chart: is a chart that represents data in percentage form.

− Area chart : is a chart used to show changes over time and draw attention to total value through a trend.

How to create a chart

− Select the data block you want to create a chart for.

− Select Insert select chart type in Charts group .

− Or double click on the arrow in the right corner of the Charts group to select the appropriate chart type.




Edit chart

Figure 2.37 – Insert chart dialog box

− Click on the chart you just created

− Select the Design Tab to change chart styles, layout, colors, change data... for the chart and move the chart to another Sheet in Excel.


Figure 2.38 – Design tab to edit chart


− Select the Layout Tab to manage inserting images and texts, labels, titles... for the chart.


Figure 2.39 - Layout tab to edit chart

− Select the Format Tab to edit the shape, font style and size of the chart.


Figure 2.40 – Format tab for chart formatting


Change chart type:

Select the chart to change

Select the Design Tab


Select the icon in the Type group

Select the chart type to change in the Change Chart Type dialog box


Figure 2.41 – Changing chart type


Edit title, notes for chart:

Select the chart to edit.

Select the Layout Tab.


Select the icon in the Labels group to edit the title for the chart.


Select the icon in the Labels group to edit the X,Y axis title


Select the icon in the Labels group to edit the note.

Comment


Agree Privacy Policy *