ฟังก์ชันและคุณลักษณะยอดนิยมของ Excel สำหรับที่ปรึกษาด้านการจัดการ

สำหรับที่ปรึกษาด้านการจัดการที่ต้องวิเคราะห์ข้อมูลและนำเสนอแก่ลูกค้า Microsoft ผลิตภัณฑ์เป็นทรัพยากรที่เหลือเชื่ออย่างแท้จริง และไม่ต้องสงสัยเลยว่า เครื่องมือที่สำคัญที่สุดอย่างหนึ่งสำหรับที่ปรึกษาด้านการจัดการคือ Microsoft Excel . ในบทความนี้ เราจะพูดถึง Excel . อันดับต้น ๆ ฟังก์ชันและฟีเจอร์สำหรับที่ปรึกษาด้านการจัดการ

ดาวน์โหลดแบบฝึกหัดได้จากที่นี่

ฟังก์ชัน Excel 70 อันดับแรก

ในส่วนนี้เราจะพูดถึงอันดับสูงสุด 70 ฟังก์ชัน excel ดังกล่าวที่มีความสำคัญในการจัดการและนำเสนอข้อมูลใน Excel . เราจะแบ่งพวกเขาออกเป็นหมวดหมู่และแสดงภาพประกอบทีละรายการ

ฟังก์ชันวันที่ (8 ฟังก์ชั่น)

การจัดการข้อมูลธุรกิจจะได้รับประโยชน์อย่างมากจาก วันที่ DATE ฟังก์ชัน . เราจะให้คำอธิบายสั้น ๆ ของ DATE . ทั้งหมด ฟังก์ชัน

ฟังก์ชันวัน

ฟังก์ชันวัน เป็นฟังก์ชันอาร์กิวเมนต์โมโน มันส่งกลับวันของวันที่ กำหนดวันเป็นจำนวนเต็มตั้งแต่ 1 ถึง 31 .

• ไวยากรณ์ทั่วไป

DAY(หมายเลขซีเรียล)

• คำอธิบายอาร์กิวเมนต์

ตัวอย่างฟังก์ชัน DAY

ในตัวอย่างนี้ เราจะใช้ฟังก์ชัน DAY เกี่ยวกับข้อมูลใน B5 เซลล์ เป็นผลให้ เราจะได้รับค่าตัวเลขที่แสดงถึงวันที่ของเดือนนั้น ๆ ตั้งแต่ 13 กันยายน คือ 13 วันของเดือนฟังก์ชันจะส่งคืน 13 . ดังนั้น สูตรที่ต้องการคือ DAY ฟังก์ชันในเซลล์ C5 จะเป็น:

• จากนั้น กด Enter .

• ดังนั้น เราจะมีค่าตัวเลขของวัน
• ลดเคอร์เซอร์ลงเพื่อรับค่าสำหรับเซลล์ที่เหลือ

ฟังก์ชันเดือน

ฟังก์ชันนี้รับอาร์กิวเมนต์เดียวเท่านั้น ส่งคืนเดือนของวันที่ที่แสดงด้วยหมายเลขซีเรียล กำหนดให้เดือนเป็นจำนวนเต็มตั้งแต่ 1 (มกราคม) ถึง 12 (ธันวาคม) .

• ไวยากรณ์ทั่วไป

MONTH(serial_number)

• คำอธิบายอาร์กิวเมนต์

ตัวอย่างฟังก์ชัน MONTH

ในตัวอย่างนี้ เราจะใช้ ฟังก์ชัน MONTH กับข้อมูลใน B5 เซลล์ ด้วยเหตุนี้ เราจะได้รับค่าตัวเลขที่แสดงเดือนของวันที่สำหรับเดือนนั้น ตั้งแต่ กันยายน เป็น ที่ 9 วันของเดือนฟังก์ชันจะคืนค่า 9 . ดังนั้น สูตรที่ต้องการด้วย เดือน ทำงานในเซลล์ C5 จะเป็น:

• จากนั้น กด Enter .

• เป็นผลให้คุณจะได้ค่าของเดือน
• เลื่อนเคอร์เซอร์ลงเพื่อรับค่าที่เหลือ

ฟังก์ชันปี

ฟังก์ชันนี้รับอาร์กิวเมนต์เดียว ส่งกลับปีที่ตรงกับวันที่ ปีจะถูกส่งกลับเป็นจำนวนเต็มตั้งแต่ 1900 ถึง 9999 .

• ไวยากรณ์ทั่วไป

ปี(หมายเลขซีเรียล)

• คำอธิบายอาร์กิวเมนต์

ตัวอย่างฟังก์ชัน YEAR

ในตัวอย่างนี้ เราจะใช้ฟังก์ชัน DAY เกี่ยวกับข้อมูลใน B5 เซลล์ ด้วยเหตุนี้ เราจะได้รับค่าตัวเลขที่แสดงวันที่ของเดือนนั้น ตั้งแต่ 13 กันยายน คือ 13 วันของเดือนฟังก์ชันจะส่งคืน 13 . ดังนั้น สูตรที่ต้องการด้วย ฟังก์ชัน YEAR ในเซลล์ C5 จะเป็น:

• จากนั้น กด Enter .

• ส่งผลให้คุณได้รับค่าสำหรับปี
• จากนั้น เลื่อนเคอร์เซอร์ลงเพื่อรับค่าที่เหลือ

ฟังก์ชันวันธรรมดา

ฟังก์ชันนี้รับอาร์กิวเมนต์ที่จำเป็นหนึ่งอาร์กิวเมนต์และอาร์กิวเมนต์ทางเลือกหนึ่งอาร์กิวเมนต์ ส่งคืนวันในสัปดาห์ที่ตรงกับวันที่ กำหนดวันเป็นจำนวนเต็มตั้งแต่ 1 (วันอาทิตย์ ) ถึง 7 (วันเสาร์ ) (โดยค่าเริ่มต้น)

• ไวยากรณ์ทั่วไป

WEEKDAY(serial_number,[return_type])

• คำอธิบายอาร์กิวเมนต์

ตัวอย่างฟังก์ชัน WEEKDAY

ในตัวอย่างนี้ เราจะแทรกวันที่ใน B5 เซลล์เป็นอาร์กิวเมนต์สำหรับ WEEKDAY ฟังก์ชันใน C5 เซลล์ ฟังก์ชันนี้จะคืนค่าเป็น 3 เนื่องจากเป็นวันอังคาร และโดยค่าเริ่มต้น ฟังก์ชันนี้จะกลับเป็น ที่ 3 วันของสัปดาห์. ดังนั้น สูตรที่ต้องการด้วยฟังก์ชัน WEEKDAY ในเซลล์ C5 จะเป็น:

• จากนั้น กด Enter .

• ดังนั้น เราจะได้ค่าวันธรรมดา
• เลื่อนเคอร์เซอร์ลงไปที่เซลล์ข้อมูลสุดท้ายเพื่อรับค่าที่เหลือ

ฟังก์ชัน WEEKNUM

ฟังก์ชันนี้รับอาร์กิวเมนต์ที่จำเป็นเพียงหนึ่งอาร์กิวเมนต์และอาร์กิวเมนต์ทางเลือกหนึ่งอาร์กิวเมนต์ ส่งคืนหมายเลขสัปดาห์ของวันที่ที่ระบุ

• ไวยากรณ์ทั่วไป

WEEKNUM(serial_number,[return_type])

• คำอธิบายอาร์กิวเมนต์

ในตัวอย่างนี้ วันที่ในเซลล์ B5 จะเป็นอาร์กิวเมนต์เดียวสำหรับ ฟังก์ชัน WEEKNUM ใน C5 เซลล์ ฟังก์ชันจะคืนค่า 38 เนื่องจากวันที่อยู่ใน 38 สัปดาห์ของปี ดังนั้น สูตรที่ต้องการด้วยฟังก์ชัน WEEKNUM ในเซลล์ C5 จะเป็น:

• หลังจากนั้น ให้กด Enter .

• ด้วยเหตุนี้ เราจะได้ค่าของอาร์กิวเมนต์

ฟังก์ชัน NETWORKDAYS

ฟังก์ชันนี้รับอาร์กิวเมนต์ที่จำเป็นสองอาร์กิวเมนต์และหนึ่งอาร์กิวเมนต์ที่ไม่บังคับ ส่งกลับจำนวนวันทำการระหว่างวันที่สองวันไม่รวมวันหยุดสุดสัปดาห์และวันหยุด

• ไวยากรณ์ทั่วไป

NETWORKDAYS(start_date, end_date, [วันหยุด])

• คำอธิบายอาร์กิวเมนต์

• As a result, we will get the net workdays excluding weekends.

DATE Function

This function takes three arguments. It returns the sequential serial number that represents a particular date.

• Generic Syntax

 DATE(year,month,day)

• Argument Description

In this example, the values in the B5, C5, and D5 cells will combine to return date after being used as the arguments of the DATE function .

So, the required formula with the DATE function in cell E5 will be:

• Press the Enter ปุ่ม.

• As a result, we will get the desired value.

TODAY Function

This function returns the serial number of the current date. It has no argument.

• Generic Syntax

TODAY()

TEXT Function (11 Functions)

Text functions let you work with strings to get information or create stunning reports. In this section, we will show excerpts of 11 of the most important text functions.

FIND Function

This function takes one required argument and one optional one. It locates find_text string within within_text and returns the number of the starting position of the find_text from the first character of the second wthin_text . Start_num is optional. It is the number in the within_text argument at which you want to start searching. FIND function is case-sensitive.

• Generic Syntax

FIND(find_text,within_text ,start_num)

• Argument Description

In this example, the FIND function in the C5 cell will return 3 . Since “a” is in 3rd place in “Adam” . So, the required formula with the FIND function in cell C5 will be:

• Hit Enter .

• As a result, we will get the required value.

SEARCH Function

This function takes two essential arguments and one optional one. It locates the find_text string within within_text and returns the number of the starting position of the find_text from the first character of the second wthin_text . Start_num is optional. It is the number in the within_text argument at which you want to start searching. The SEARCH function is not case-sensitive.

• Generic Syntax

SEARCH(find_text,within_text,start_num)

• Argument Description

In this example, the SEARCH function in the C5 cell will return 1 . Since “a” is in 1st place in “Adam” and this function is not case-sensitive. So, the required formula with the SEARCH function in cell C5 will be:

• Hit Enter .

• As a result, we will get the desired result.

LEFT Function

This function returns the specified number of characters from a string starting from the left side.

• Generic Syntax

Left( string, length )

• Argument Description

The LEFT function in the C5 cell will return “Ad” . Since we will insert 2 as the length of the text that will be extracted from the left side of “Adam” . So, the required formula with the LEFT function in cell C5 will be:

• Then, hit Enter .

• Consequently, we get the result we want.

RIGHT Function

This function returns the specified number of characters from a string from the right side.

• Generic Syntax

RIGHT( string, length )

• Argument Description

Example of RIGHT Function

The RIGHT function in the C5 cell will return “am”. Since we will insert 2 as the length of the text that will be extracted from the right side of “Adam” . So, the required formula with the RIGHT function in cell C5 will be:

• Then, hit Enter .

• Consequently, we will get a portion of the text from the right side.

MID Function

This function returns the specified number of characters from a string as specified by the start argument.

• Generic Syntax

MID(text,start_num,num_chars)

• Argument Description

Example of MID Function

The MID function in the C5 cell will return “Ada” . Since we will insert 3 as the length of the text that will be extracted from index 1 of “Adam” as the start number of the text is 1 . So, the required formula with the MID function in cell C5 will be:

• Firstly, choose the C5 cell and type the following formula,

• Then, hit Enter .

• Consequently, we will have the desired result.

LEN Function

This function returns the number of characters from a string.

• Generic Syntax

LEN(text)

• Argument Description

The LEN function in the C5 cell will return 4 as the text in the B5 cell has 4 ตัวอักษร So, the required formula in the C5 cell will be,

• Finally, hit Enter .

• Consequently, we will get the length of the text.

VALUE Function

This function converts the number saved as a text into a number.

• Generic Syntax

VALUE(text)

• Argument Description

In this example the VALUE function in the C5 cell will turn the value in the B5 cell that is saved as a text into a numeric type data. So, the required formula of the VALUE function in the C5 cell will be,

• Press Enter .

• As a result, we will get the value.

TEXT Function

This function converts a number into a string with a specific format.

• Generic Syntax

TEXT(value, format_text)

• Argument Description

In this instance, the TEXT function in the C5 cell will arrange the date value in the B5 cell as the format shown in the second argument.

So, the required formula for the TEXT function in the C5 cell will be,

• Then, hit Enter .

• As a result, we will get the data in a date format.

TRIM Function

This function removes all spaces from a text except for single spaces between words.

• Generic Syntax

TRIM(text)

• Argument Description

The TRIM function in the C5 cell will trim the extra spaces between the texts in the B5 cell. So the required formula in the C5 cell will be,

• Then, hit Enter .

• Consequently, we will get the desired outcome.

CLEAN Function

This function removes all nonprintable characters from text.

• Generic Syntax

CLEAN(text)

• Argument Description

The CLEAN function in the C5 cell will remove the non-printable character from the text in the B5 cell. So, the required formula in the C5 cell will be,

• Then, hit Enter .

• As a result, we will have a non-printable character before “A” .
• Next, select the C5 cell and write the following formula,

• Then, press Enter .

• Consequently, Excel will erase the non-printable character.

CONCATENATE Function

This function joins two or more strings into one string.

• Generic Syntax

CONCATENATE(text1, [text2], …)

• Argument Description

The CONCATENATE function in the D5 cell will merge the two texts from the B5 and C5 cells respectively. So, the required formula will be,

• Then, hit Enter .

• As a result, the texts will be merged.

LOGICAL Function (8 Functions)

Making logical comparisons between a value and what you anticipate is made possible by the Logical functions . In this section, we will talk about some of the essential logical functions.

TRUE Function

This function returns TRUE if a logical statement is true. It does not have any argument.

• Generic Syntax

TRUE()

FALSE Function

This function returns FALSE if a logical statement is false. It does not contain any argument.

• Generic Syntax

FALSE()

AND Function

This function returns TRUE if all of its arguments are true. It has no argument.

• Generic Syntax

AND()

OR Function

This function returns TRUE if any of its arguments are true. It does not encompass any argument.

• Generic Syntax

AND()

NOT Function

This function reverses the logic of its arguments. It has no argument.

• Generic Syntax

NOT()

SWITCH Function

This function evaluates a value against a list of values and returns the corresponding results. The optional default value will be returned if there is no match.

• Generic Syntax

SWITCH(expression, value1, result1, [default or value2, result2],…[default or value3, result3])

• Argument Description

The SWITCH function in the C5 cell will return “Executive” because the value in the B5 is “Adam” which is associated with “Executive”. So the formula in the C5 cell will be,

• Select the C5 cell and write the following formula,

• Then, hit the Enter ปุ่ม.

• As a result, we will get the output.

IF Function

This function has 3 arguments. If argument 1 is TRUE , then it does as argument 2 dictates. Otherwise, it does as argument 3 dictates.

• Generic Syntax

IF(logical_test, value_if_true, [value_if_false])

• Argument Description

IFERROR Function

This function returns value_if_erro r if the first argument is evaluated to an error. Otherwise, it returns the result of the first argument.

• Generic Syntax

IFERROR(value, value_if_error)

Argument Description

The IFERROR function in the D5 cell will return ” No Result” because the B5/B6 expression will return an error. So the formula will be,

• Then, hit Enter .

• As a result, we will have an error message.

Combining TRUE, FALSE, AND, and IF Functions

We can not use the  TRUE and FALSE functions independently. So we will use them in combination with the AND and IF functions ที่นี่.

ขั้นตอน:

• Select the D5 cell and write down the following formula,

• Then, hit Enter .

• As a result, we will get a logical result.

Combining TRUE, FALSE,OR, and IF Functions

We can not use the  TRUE and FALSE functions solo. So we will use them in combination with the OR and IF functions in this instance.

ขั้นตอน:

• Select the D5 cell and write the following formula down,

• Then, hit Enter .

• As a result, we will get an output.

Combining TRUE, FALSE, NOT, and IF Functions

We can not use the  TRUE and FALSE functions independently. So we will use them in combination with the NOT and IF functions in this example.

ขั้นตอน:

• Select the D5 cell and write down the following formula,

• Then, hit Enter .

• Consequently, we will get a logical answer.

LOOKUP and Reference Functions (13 Functions)

Lookup functions are frequently used when retrieving data, expanding data tables, or mapping records to categories. Here, we will discuss some of these functions.

CHOOSE Function

This function selects one of up to 254 values from the value list based on index_num .

• Generic Syntax

CHOOSE(index_num, value1, [value2], …)

• Argument Description

The CHOOSE function in the C5 cell will return “Manager” because the number 1 in the C5 cell is associated with “Manager.” So, the formula in that cell will be,

• Then, hit Enter .

• As a result, we will get our desired outcome.

ROW Function

This function returns the row number of the given cell reference.

• Generic Syntax

ROW([reference])

• Argument Description

The ROW function in the C5 cell will return 5 since the referenced cell B5 is in row 5 . So, the formula will be,

• Then, hit Enter .

• Consequently, we will get the desired outcome.

COLUMN Function

This function returns the column number of the given cell reference.

• Generic Syntax

COLUMN([reference])

• Argument Description

This function will return 2 in the C5 cell because the referenced cell is in the 2nd columns. ดังนั้น. the desired formula in the C5 cell will be,

• Then, hit Enter .

• Consequently, we will get the desired outcome.

ROWS Function

This function returns the number of rows in a reference of an array.

• Generic Syntax

ROWS(array)

• Argument Description

The ROWS function in the C5 cell will return 4 because the array under it has 4 rows. So, the formula in the C5 cell will be,

• Then, hit Enter .

• Consequently, we will get the desired outcome.

COLUMNS Function

This function returns the number of columns in a reference of an array.

• Generic Syntax

COLUMNS(array)

• Argument Description

The ROWS function in the C5 cell will return 1 because the array under it has 1 คอลัมน์. So, the formula in the C5 cell will be,

• Choose the C5 cell and write the following formula,

• Then, hit Enter .

• Consequently, we will get the desired outcome.

VLOOKUP Function

This function looks in the first column of an array and returns the value of offset_column.

• Generic Syntax

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

• Argument Description

The VLOOKUP function in the D5 cell will return 102 . Because the it is in the second column of the lookup range B5:C8 and beside the lookup value “Adam” . So the formula in the D5 cell will be,

• Then, press Enter .

• As a result, we will have our VLOOKUP value.

HLOOKUP Function

This function looks in the top row of an array and returns the value of offset_column .

• Generic Syntax

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

• Argument Description

The HLOOKUP function in the D5 cell will return “Jhon” . Because the it is in the second row of the lookup range B5:C8 and below the lookup value “Adam” . So the formula in the D5 cell will be,

• Then, press Enter .

• As a result, we will have our HLOOKUP value.

LOOKUP Function

This function looks at a single row or column and finds the value from the same position in a second row or column.

• Generic Syntax

LOOKUP(lookup_value, lookup_vector, [result_vector])

• Argument Description

The LOOKUP function in the D5 cell will return 102 . Because it matches the lookup  value “Adam” in the lookup range B5:C8 . So, the formula will be,

• Then, press Enter .

• As a result, we will have our lookup value.

MATCH Function

This function gets the position of an item in an array.

• Generic Syntax

MATCH(lookup_value, lookup_array, [match_type])

• Argument Description

The MATCH function will return 1 as “Adam” is the first element in the range B5:B8. So, the desired formula will be,

• Then, hit Enter .

• Consequently, we will have a match.

INDEX Function

This function returns the value of an element in a table or an array based on the row_num and column_num .

• Generic Syntax

INDEX(array, row_num, [column_num])

• Argument Description

The INDEX function will return “Jhon” in the D5 cell since it is in the second row of the B5:B8 range. So, the formula will be,

• Then, hit Enter .

• As a result, we will get the value of the indexed cell.

OFFSET Function

This function returns values in a cell or a range that is rows and columns away from a reference cell.

• Generic Syntax

OFFSET(reference, rows, cols, [height], [width])

• Argument Description

The OFFSET function in the D5 cell will return 101. Because it is the value of the cell that is two rows below and one column to the right of the referenced cell B5 . So, the desired formula in the D5 cell will be,

• Press Enter .

• Consequently, we will have an output.

INDIRECT Function

This function returns the reference specified by a text string.

• Generic Syntax

INDIRECT(ref_text, [a1])

• Argument Description

Example of INDIRECT Function

The INDIRECT function in the E5 cell will return 103 because the referenced D8 cell has C8 as its value and the value contained in the C8 cell is 103 . So, the required formula will be,

• Then, press Enter .

• As a result, we will get the value from the C8 เซลล์

GETPIVOTDATA Function

This function returns data stored in a PivotTable report.

• Generic Syntax

GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], …)

• Argument Description

This function will return 450 in the E5 cell because we will define the arguments accordingly. So, the required formula in the E5 cell is as follows,

• Press Enter .

• As a result, we will get an output.

MATH Function (15 functions)

Large companies typically have specialized teams that can analyze data and perform in-depth statistical analysis for consultants. However, basic math and statistics calculations require the use of Excel functions on a daily basis for management consultants.

RAND Function

This function returns an evenly distributed random real number greater than or equal to 0 and less than 1 . RAND()*(b-a)+a can return number between a and b . It does not contain any argument.

• Generic Syntax

RAND()

Example of RAND Function

The RAND function takes no argument. Here, we will modify the formula as follows to return a value in the D5 cell,

• Then, press Enter .

• As a result, we will get a random number.

RANDBETWEEN Function

This function generates a random integer number between the bottom number and the top number.

• Generic Syntax

RANDBETWEEN( bottom, top)

• Argument Description

The RANDBETWEEN function will return any random number in the D5 cell between the values in the B5 and C5 cells respectively. So, the desired formula will be,

• To start with, select the D5 cell and enter the following formula,

• Then, press Enter .

• Consequently, we will get a random number in between the numbers.

INT Function

This function rounds a number down to the nearest integer.

• Generic Syntax

INT(number)

• Argument Description

The INT function in the C5 cell will return 2 since it will only return the integer portion of the number. So, the required formula will be,

• After that, press Enter .

• As a result, we will get the desired outcome.

ROUND Function

This function rounds a number to a specified number of digits.

• Generic Syntax

ROUND(number, num_digits)

• Argument Description

The ROUND function in the C5 cell will round the 2.05 in the B5 cell to the nearest 1 and will return 2.1 . So the required formula will be,

• After that, press Enter .

• As a result, we will get the desired outcome.

FLOOR Function

This function rounds the numbers down, toward zero, to the nearest multiple of significance.

• Generic Syntax

FLOOR(number, significance)

• Argument Description

This function will return 2 in the C5 cell because it will round down the 2.05 in the B5 cell to 2 . So, the formula in the C5 cell will be,

• After that, press Enter .

• As a result, we will get the desired outcome.

CEILING Function

This function rounds the numbers up, away from zero, to the nearest multiple of significance.

• Generic Syntax

CEILING(number, significance)

• Argument Description

The CEILING function in the C5 cell will return 3 because it will round up the 2.05 in the B5 cell. So, the required formula will be,

• After that, press Enter .

• As a result, we will get the desired outcome.

SUM Function

This function adds together all the numbers given as arguments and returns the total number.

• Generic Syntax

SUM(number1,[number2],…)

• Argument Description

It will simply return the sum of the numbers in the B5:B8 range. So, the formula in the D5 cell will be,

• Then, hit Enter .

• As a result, we will get the sum of the specified cells.

SUMIF Function

This function adds together cells in range_sum if corresponding cells in range_evaluate meet the criteria. If range_sum is omitted, Excel will add cells in range_evaluat e .

• Generic Syntax

SUMIF(range, criteria, [sum_range])

• Argument Description

The SUMIF function in the D5 cell will return the sum of the numbers in the B5:B8 range which are greater than zero. So, the formula will be,

• Then, press Enter .

• Consequently, we will get the conditional summation.

SUMIFS Function

This function adds all cells meeting all of the specified criteria.

• Generic Syntax

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

• Argument Description

The SUMIFS function in the D5 cell will return the sum of numbers from the B5:B8 range which are greater than zero. So, the formula in the D5 cell will be,

• Then, press Enter .

• Consequently, we will get the conditional summation.

PRODUCT Function

This function multiplies all the numbers given as arguments and returns the product.

• Generic Syntax

PRODUCT(number1, [number2], …)

• Argument Description

Example of PRODUCT Function

The PRODUCT function in the D5 cell will return the product of the numbers in the range B5:B8 . So, the formula will be,

• Then, press Enter .

• Consequently, we will get the product.

POWER Function

This function returns the result of a number raised to a power.

• Generic Syntax

POWER(number, power)

• Argument Description

Example of POWER Function

The POWER function in the D5 cell will return 1. Because it will raise the power of 1 to 4 and the result is 1 . So, the required formula in the D5 cell will be,

• Then, press Enter .

• As a result, the base value will be raised to power.

SQRT Function

This function returns a positive square root.

• Generic Syntax

SQRT(number)

• Argument Description

Example of SQRT Function

The SQRT function in the C5 cell will simply return the square root of 4 that is in the B5 cell and return 2 . So, the formula for the C5 cell will be,

• Then, hit Enter .

• As a result, we will get the square root of the numbers.

MOD Function

This function returns the remainder after a number is divided by a divisor.

• Generic Syntax

MOD(number, divisor)

• Argument Description

Example of MOD Function

The MOD function in the D5 cell will simply return the reminder of the division between the B5 and C5 cell and it will be 1 . So, the required formula will be,

• Then, press Enter .

• As a result, we will get the reminder value.

SUBTOTAL Function

This function returns a subtotal in a list or database. The function_ numbe r includes 1-11 or 101-111 . It specifies the function to be used for the subtotal. 1-11 includes manually hidden rows while 101-111 excludes them. Filtered-out cells are always excluded.

• Generic Syntax

SUBTOTAL(function_num,ref1,[ref2],…)

• Argument Description

The SUBTOTAL function in the D5 cell will return the average of the numbers in the range of cell B5:B8 . So, the required formula in the D5 cell will be,

• Then, press Enter .

• Consequently, we will get the average of the selected range.

SUMPRODUCT Function

This function multiplies the corresponding items in given arrays and then returns the sum of the results.

• Generic Syntax

SUMPRODUCT(array1, [array2], [array3], …)

• Argument Description

The SUMPRODUCT function in the D5 cell will first multiply two adjacent cells( for example, B5 and C5 ) and finally sum the products of all the cells from C5 to C8 . So, The required formula in the D5 cell will be,

• Then, press Enter .

• As a result, we will get the product and then the sum of the values simultaneously.

STATISTICAL Function (15 functions)

Management consultants often need some statistical functions to analyze their data. In this section, we will talk about some of the statistical functions.

COUNT Function

This function counts the total number of cells that contain numbers.

• Generic Syntax

COUNT(value1, [value2], …)

• Argument Description

The COUNT function will return the number of cells in the range B5:B8 and will return 4 . So, the required formula will be,

• Then, press Enter .

• As a result, we will get the cell count.

COUNTA Function

This function counts the number of non-blank cells.

• Generic Syntax

COUNTA(value1, [value2], …)

• Argument Description

The COUNTA function in the D5 cell will return the number of nonempty cells in the range B5:B8 . So, the required formula will be,

• Then, press Enter .

• As a result, we will get the nonempty cell count.

COUNTIF Function

This function counts the number of cells within a range that meet the given criteria.

• Generic Syntax

COUNTIF(range, criteria)

• Argument Description

The COUNTIF function in the D5 cell will return the number of cells that have a value greater than zero in the range B5:B8 . So, the formula in the D5 cell will be,

• Then, press Enter .

• As a result, we will have conditional counting.

COUNTIFS Function

This function counts the number of cells within a range that meet multiple criteria.

• Generic Syntax

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

• Argument Description

Example of COUNTIFS Function

The COUNTIFS function in the D5 cell will return the number of cells that have a value greater than 10 in the range B5:B8 . So, the formula in the D5 cell will be,

• Then, press Enter .

• As a result, we will get the count.

AVERAGE Function

This function returns the average/mean of the arguments.

• Generic Syntax

AVERAGE(number1, [number2], …)

• Argument Description

The AVERAGE function in the D5 cell will simply return the average of the values on the range B5:B8 . So, the required formula will be,

• Then, press Enter .

• As a result, we will get the average value.

AVRAGEIF Function

This function gets the average of cells in range_ average if corresponding cells in range_evaluate meet criteria. If range_averag e is omitted, Exce l will take the average on cells in range_evaluate .

• Generic Syntax

AVERAGEIF(range, criteria, [average_range])

• Argument Description

The AVERAGEIF function will return the average of the values in the range B5:B8 which are greater than zero into the D5 cell. So, the formula in the D5 cell will be,

• Then, press Enter .

• Consequently, we will get a conditional average.

AVERAGEIFS Function

This function gets the average of cells meeting all of those criteria.

• Generic Syntax

AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

• Argument Description

The AVERAGEIFS function in the D5 cell will return the average of the values that are greater than zero from the B5:B8 range. So, the formula will be,

• Then, press Enter.

• Consequently, we will have a conditional average.

MIN Function

This function returns the minimum value in the arguments.

• Generic Syntax

MIN(number1, [number2], …)

• Argument Description

The MIN function in the D5 cell will return the minimum value in the range B5:B8 . So, the formula in the D5 cell will be,

• Then, press Enter.

• As a result, we will have the minimum value.

MAX Function

This function returns the maximum value in the arguments.

• Generic Syntax

MAX(number1, [number2], …)

• Argument Description

Example of MAX Function

The MAX function in the D5 cell will return the maximum value in the range B5:B8 . So, the formula in the D5 cell will be,

• Then, press Enter .

• As a result, we will have the maximum value.

MEDIAN Function

This function calculates the median of a sequence of numbers.

• Generic Syntax

MEDIAN(number1, [number2], …)

• Argument Description

The MEDIAN function in the D5 cell will return the median value of the cells in the range B5:B8. So, the formula in the D5 cell will be,

• Then, press Enter .

• As a result, we will have the median value.

STDEV Function

This function calculates standard deviation based on arguments.

• Generic Syntax

STDEV(number1,[number2],…)

• Argument Description

Example of STDEV Function

The STDEV function in the D5 cell will return the standard deviation value of the cells in the range B5:B8 . So, the formula in the D5 cell will be,

• Then, press Enter .

• As a result, we will have the standard deviation value.

VAR Function

This function estimates variance based on a sample.

• Generic Syntax

VAR(number1,[number2],…)

• Argument Description

Example of VAR Function

The VAR function in the D5 cell will return the variance value of the cells in the range B5:B8. So, the formula in the D5 cell will be,

=VAR(B5:B8)

• Then, press Enter .

• As a result, we will have the variance value.

RANK Function

This function returns the rank of a number in a list of numbers. Order is omitted by default. If it is 0 or omitted, Excel ranks number as if ref were a list sorted in descending order. If the order is any non-zero value, Excel ranks number as if ref were a list sorted in ascending order.

• Generic Syntax

RANK(number,ref,[order])

• Argument Description

Example of RANK Function

The RANK function in the D5 cell will rank the values in the cell range B5:B8 in an ascending order. So, the formula in the D5 cell will be,

• Hit Enter .

• As a result, we will get the ranks of the values.

LARGE Function

This function returns the k-th largest value in an array.

• Generic Syntax

LARGE(array, k)

• Argument Description

Example of LARGE Function

The LARGE function in the D5 cell will return the second largest value from the values in the range B5:B8 . So, the formula in the D5 cell will be,

• Then, press Enter .

• Consequently, we will get the second-largest value.

SMALL Function

This function returns the k-th smallest value in an array.

• Generic Syntax

SMALL(array, k)

• Argument Description

Example of SMALL Function

The SMALL function in the D5 cell will return the second smallest value from the values in the range B5:B8. So, the formula in the D5 cell will be,

• Then, press Enter .

• Consequently, we will get the smallest value.

Top 7 Features

Named ranges

You can make your formula simpler by using named ranges. You are not required to include a range reference. And readers can easily comprehend. Additionally, it enables you to automatically update formulas when data changes.

What Is Named Range?

Excel’s “Named Range ” feature allows you to call a group of cells by name rather than by range. A whole column, row, or even a few particular cells may be the subject. Once the named range has been defined, any operation on those cells can be carried out by calling the name of the named range.

We will follow the steps below to define a “Named Range.”

ขั้นตอน:

• Firstly, select the cells that we want to make a named range.
• Here, we select a range from D5 to D8 .
• Then, go to the Formulas  แท็บ
• From the Defined Names group of commands, select the drop-down Define Name .
• From the drop-down, select the command Define Name

• As a result, we will get a dialogue box named “New Name”
• Set a name in the Name .
• We can also see the selected range from the Refers to  กล่อง.
• Then, click OK .

• Finally, our selected range will be named according to our definition.
• Select the range containing the sales information in Column D to check our named range again.
• We will see that the range is named as we wanted.

Conditional Formatting

The useful feature of conditional formatting lets you highlight specific cells in a particular color. You can also use it to compare values, find discrepancies, find the smallest duplicate, and display basic icons. You can learn how to use this fantastic feature by looking at the two easy examples that follow.

Creating Alternating Bands

Here, we will create bands with alternate colors to visualize our data more aesthetically and clearly.

ขั้นตอน:

• Firstly, select the data range.
• Here, we will select the range B4:D8 .
• Secondly, go to the Home  แท็บ
• Then, click on Conditional Formatting .
• After that, from the drop-down list, select New Rule .

• Consequently, the New Formatting Rule dialogue box will appear on the screen.
• In the prompted New Formatting Rule dialogue box, select Use a formula to determine which cells to format  ตัวเลือก
• Then, type the following formula into Format values where this formula is true  box,

• Then, click on the Format button to open Format Cells กล่องโต้ตอบ

• Then, go to the Fill tab in the Format Cells กล่องโต้ตอบ
• Select the color that you want to have for your data cells.
• Finally, click OK .

• Consequently, you will find that your data set is colored in alternative bands.
• You can also use the following formula to format odd rows.

Finding Duplicate Values

In this instance, we will find duplicate values from our dataset using conditional formatting.

ขั้นตอน:

• To begin with, select the data range.
• In this case, we will select the range B4:D10 .
• After that, go to the Home  tab in the ribbon.
• Then, click on Conditional Formatting .
• Afterward, from the drop-down list, select New Rule .

• Consequently, the New Formatting Rule dialogue box will appear on the screen.
• In the prompted New Formatting Rule dialogue box, select Format only unique or duplicate value

• Then, select duplicate from the Format all ตัวเลือก
• Finally, click on the Format button to open Format Cells กล่องโต้ตอบ

• Next, open the Format Cells dialogue box and select the Fill
• Select the color that you want to have for your data cells.
• Finally, click OK .

• Consequently, we will find that the duplicate values are marked with the desired color.

Pivot Tables and Charts

You can analyze datasets quickly with pivot tables. Also, you can divide sales by region, average revenue per customer, country, and other metrics, for instance. You can use pivot tables to calculate, slice, and dice the data along any dimension. This is crucial because it can help you understand how the data is organized and where further investigation is required.

Pivot Table

We will use the pivot table to analyze data in the following steps.

ขั้นตอน:

• Firstly, go to the Insert  แท็บ
• Then, select the Pivot Table แท็บ
• From the drop-down, select From Table/Range .
• Consequently, a prompt will appear.

• From the prompt, first, select your data set as Table/Range .
• Then, check the Existing Worksheet  กล่อง.
• Set the location of the pivot table.
• Finally, click OK .

• As a result, you will have your Pivot table .
• Then, check the pivot table fields to get the entire data set.

• Next, you can calculate the average, minimum value, maximum value, etc from the pivot table.
• To calculate the average revenue, first, right-click on any of the revenue data.
• Then, select the Summarize Values By ตัวเลือก
• Finally, select Average จากตัวเลือกที่มี

• Consequently, you will have the average of the revenue values for each employee.

Pivot Chart

In this example, we will explore the Pivot Chart. Follow the subsequent steps to do that.

ขั้นตอน:

• To begin with, go to the Insert  แท็บ
• Then, choose the Pivot Chart  แท็บ
• From the drop-down menu, select the Pivot Chart
• Consequently, we will have a prompt on the screen.

• In the prompt, at first, choose your data set as Table/Range .
• Then, mark the Existing Worksheet  oval.
• Set the location of the pivot table.
• Finally, click OK .

• As a result, we will have our pivot chart with a pivot table.

RSQ

You can quickly check the correlation between two datasets using RSQ . RSQ can also be plotted on a scatter plot. We will show that in the following steps.

ขั้นตอน:

• Firstly, select the F5 cell and type the following formula,

• Then, hit Enter.

• As a result, we will have the R squared value for our data.

• After that, select the data in the range C5:D10 .
• Then, go to the Insert tab in the ribbon.
• From the Charts group, select a scatter plot for the data.
• As a result, we will have a scatter plot of the data.

• Then, right-click on any data point on the plot.
• From the options, select Add Trendline .
• Consequently, a menu bar will appear on the right.

• Then, from the Trendline Options , select Linear .
• Then check the “Display Equation on chart” and “Display R -squared value on chart”  กล่อง.

• As a result, you will have an R-squared value on the chart, the same as the value from the RSQ function .

Regression

Regression is most frequently used to predict outcomes and improve business operations. For instance, r egression can predict based on past behavior and other data how many products consumers will buy. Additionally, as a factory manager, you can create a model to determine the connection between cookie shelf life and oven temperature. We will show how to use regression in the following steps.

ขั้นตอน:

• Firstly, go to the Data  แท็บ
• Then, select Data Analysis .
• As a result, the Data Analysis prompt will appear.

• From the prompt, first, select Regression .
• Then, choose OK .
• The Regression dialogue box will appear on the screen.

• Firstly, select Input Y Range .
• Here, we will select the range (D5:D10 ) .
• Then, choose Input X Range .
• In our case, the range is (C5:C10 )
• Then, select the New Worksheet Ply  oval.
• After that, check the Residuals , Residual Plots, and Line Fit Plots  กล่อง.
• Finally, click OK .

• As a result, Excel will show a complete report of the data with regression plots.

Excel Solver

One of the most helpful features in Excel that can help you find the best solution to a problem is the Excel Solver . Excel Solver is an optimization tool that may be used to find ways to modify a model’s assumptions in order to obtain the desired result. It is a form of what-if analysis and is especially helpful when attempting to identify the “optimal” outcome in light of a number of assumptions.

VBA Programming

The most crucial feature of Excel is VBA programming. Management consultants can automate the printing of more than 100 files by using VBA to control the printer. Additionally, they can create slides using VBA so that slides update as the Excel source data does. Furthermore, VBA can be used to download online sources of data for analysis. In conclusion, learning VBA is a crucial skill that can help you become more productive and efficient.

บทสรุป

In this article, we have talked in detail about some of the key functions and features for management consultants of MS Excel . These functions and features will allow them to manage and interpret data properly.