데이터 분석을 수행하고 고객에게 프레젠테이션을 제공해야 하는 관리 컨설턴트의 경우 Microsoft 제품은 정말 놀라운 자원입니다. 그리고 의심의 여지 없이 관리 컨설턴트에게 가장 중요한 도구 중 하나는 Microsoft Excel입니다. . 이 기사에서는 최고의 Excel 경영 컨설턴트를 위한 기능 및 특징.
여기에서 연습 워크북을 다운로드할 수 있습니다.
상위 70가지 Excel 기능
이 섹션에서는 상위 70 Excel에서 데이터를 관리하고 표시하는 데 중요한 Excel 기능 . 카테고리별로 나눠서 일러스트를 하나씩 보여드리겠습니다.
DATE 기능(8가지 기능)
비즈니스 데이터 관리는 DATE부터 큰 이점을 얻을 수 있습니다. 기능 . 여기에서는 모든 DATE 기능.
DAY 기능
요일 기능 모노 인수 함수입니다. 날짜의 날짜를 반환합니다. 요일은 1 범위의 정수로 지정됩니다. 31까지 .
- 일반 구문
DAY(일련번호)
- 인수 설명
인수 | 요구사항 | 설명 |
---|---|---|
일련 번호 | 필수 | 찾고자 하는 날짜 |
DAY 함수의 예
이 예에서는 DAY 함수를 사용합니다. B5의 데이터 셀. 결과적으로 해당 월의 날짜를 나타내는 숫자 값을 얻습니다. 9월 13일 이후 13번째입니다. 날짜, 함수는 13을 반환합니다. . 따라서 DAY가 있는 필수 수식은 셀 C5의 함수 다음이 될 것입니다:
=DAY(B5)
- 그런 다음 Enter 키를 누릅니다. .
- 결과적으로 오늘의 숫자 값을 갖게 됩니다.
- 나머지 셀의 값을 가져오려면 커서를 내리세요.
MONTH 함수
이 함수는 하나의 인수만 사용합니다. 일련 번호로 표시되는 날짜의 월을 반환합니다. 월은 1(1월) 범위의 정수로 표시됩니다. 12(12월)까지 .
- 일반 구문
MONTH(일련번호)
- 인수 설명
인수 | 요구사항 | 설명 |
---|---|---|
일련 번호 | 필수 | 찾고자 하는 날짜. |
MONTH 함수의 예
이 예에서는 MONTH 함수를 사용합니다. B5의 데이터 셀. 결과적으로 특정 월의 해당 월을 나타내는 숫자 값을 얻게 됩니다. 9월 이후 9번째입니다. 날짜, 함수는 9를 반환합니다. . 따라서 MONTH가 포함된 필수 공식 C5 셀의 함수 다음이 될 것입니다:
=MONTH(B5)
- 그런 다음 Enter 키를 누릅니다. .
- 결과적으로 해당 월의 값을 얻을 수 있습니다.
- 나머지 값을 보려면 커서를 아래로 이동하세요.
YEAR 함수
이 함수는 단일 인수를 취합니다. 날짜에 해당하는 연도를 반환합니다. 연도는 1900 범위의 정수로 반환됩니다. 9999까지 .
- 일반 구문
YEAR(일련 번호)
- 인수 설명
인수 | 요구사항 | 설명 |
---|---|---|
일련 번호 | 필수 | 찾고자 하는 연도의 날짜입니다. |
YEAR 함수의 예
이 예에서는 DAY 함수를 사용합니다. B5의 데이터 셀. 결과적으로 특정 월의 날짜를 나타내는 숫자 값을 얻습니다. 9월 13일 이후 13번째입니다. 날짜, 함수는 13을 반환합니다. . 따라서 YEAR 함수가 있는 필수 수식은 셀 C5 다음이 될 것입니다:
=YEAR(B5)
- 그런 다음 Enter 키를 누릅니다. .
- 결과적으로 올해의 가치를 알 수 있습니다.
- 그런 다음 커서를 아래로 이동하여 나머지 값을 가져옵니다.
주간 기능
이 함수는 하나의 필수 인수와 하나의 선택적 인수를 취합니다. 날짜에 해당하는 요일을 반환합니다. 요일은 1 범위의 정수로 지정됩니다. (일요일 ) ~ 7 (토요일 ) (기본값).
- 일반 구문
WEEKDAY(일련번호,[반품_유형])
- 인수 설명
인수 | 요구사항 | 설명 |
---|---|---|
일련 번호 | 필수 | 찾고자 하는 날짜를 나타내는 일련 번호입니다. |
반환 유형 | 선택 사항 | 반환 값의 유형을 결정하는 숫자입니다. 이 숫자는 실제로 주 계산이 시작되는 요일을 의미합니다. 기본적으로 주 계산은 일요일부터 시작됩니다. |
WEEKDAY 함수의 예
이 예에서는 B5 WEEKDAY에 대한 인수로 셀 C5의 기능 셀. 이 함수는 오늘이 화요일이고 기본적으로 3번째이므로 3을 반환합니다. 요일. 따라서 WEEKDAY 함수와 함께 필요한 수식은 C5 셀 다음이 될 것입니다:
=WEEKDAY(B5)
- 그런 다음Enter 키를 누릅니다. .
- 결과적으로 요일 값을 얻습니다.
- 나머지 값을 가져오려면 커서를 마지막 데이터 셀로 이동하세요.
WEEKNUM 기능
이 함수는 하나의 필수 인수와 하나의 선택적 인수만 취합니다. 특정 날짜의 주 번호를 반환합니다.
- 일반 구문
WEEKNUM(일련번호,[반품_유형])
- 인수 설명
인수 | 요구사항 | 설명 |
---|---|---|
일련 번호 | 필수 | 주 내의 날짜 |
반환 유형 | 선택 사항 | 주의 첫째 요일을 설정하는 숫자. 1이 기본값입니다. |
이 경우 B5 셀의 날짜는 WEEKNUM 함수에 대한 유일한 인수가 됩니다. C5 셀. 함수는 38을 반환합니다. 날짜가 38일이므로 올해의 주. 따라서 셀 C5에 WEEKNUM 함수가 있는 필수 수식 다음이 될 것입니다:
=WEEKNUM(B5)
- 그런 다음 Enter 키를 누릅니다. .
- 결과적으로 인수에 대한 값을 얻게 됩니다.
NETWORKDAYS 기능
이 함수는 두 개의 필수 인수와 하나의 선택적 인수를 취합니다. 주말과 공휴일을 제외한 두 날짜 사이의 근무일 수를 반환합니다.
- 일반 구문
NETWORKDAYS(start_date, end_date, [휴일])
- 인수 설명
인수 | 요구사항 | 설명 |
---|---|---|
시작일 | 필수 | 시작 날짜가 되는 날 |
종료일 | 필수 | 종료일이 되는 날 |
공휴일 | 선택 사항 | 작업 달력에서 제외할 주 및 연방 공휴일 및 변동 공휴일과 같은 하나 이상의 날짜의 선택적 범위 |
이 경우 B5의 날짜는 및 C5 셀은 NETWORKDAY 함수에 대한 인수로 전달됩니다. C5 셀. 이 함수는 두 날짜 간의 차이를 반환합니다. 따라서 NETWORKDAY 함수에 필요한 수식은 셀 C5 다음이 될 것입니다:
=NETWORKDAYS(B5,C5)
- Enter 키를 누릅니다. .
- 결과적으로 주말을 제외한 순 근무일을 얻게 됩니다.
날짜 함수
이 함수는 세 개의 인수를 취합니다. 특정 날짜를 나타내는 일련 번호를 반환합니다.
- 일반 구문
DATE(년,월,일)
- 인수 설명
인수 | 요구사항 | 설명 |
---|---|---|
<강한>년 | 필수 | 연도 인수 값은 1-4자리일 수 있습니다. |
<강한>월 | 필수 | 1에서 12까지의 월로, 양수 또는 음수 정수로 표시됩니다. |
<강한>일 | 필수 | 1에서 31까지의 월은 양수 또는 음수 정수로 표시됩니다. |
이 예에서 B5, C5, 및 D5 셀은 DATE 함수의 인수로 사용된 후 결합하여 날짜를 반환합니다. .
따라서 DATE 함수와 함께 필요한 수식은 셀 E5 다음이 될 것입니다:
=DATE(B5,C5,D5)
- Enter 키를 누릅니다. 버튼.
- 결과적으로 원하는 값을 얻게 됩니다.
오늘 기능
이 함수는 현재 날짜의 일련 번호를 반환합니다. 논쟁의 여지가 없습니다.
- 일반 구문
오늘()
텍스트 기능(11가지 기능)
텍스트 기능 문자열로 작업하여 정보를 얻거나 멋진 보고서를 만들 수 있습니다. 이 섹션에서는 11의 발췌문을 보여줍니다. 가장 중요한 텍스트 기능입니다.
찾기 기능
이 함수는 하나의 필수 인수와 하나의 선택적 인수를 취합니다. find_text를 찾습니다. in_text 내의 문자열 find_text의 시작 위치 번호를 반환합니다. 두 번째 wthin_text의 첫 번째 문자에서 . Start_num 선택 사항입니다. in_text에 있는 숫자입니다. 검색을 시작하려는 인수. 찾기 기능 대소문자를 구분합니다.
- 일반 구문
FIND(find_text,in_text ,start_num)
- 인수 설명
인수 | 요구사항 | 설명 |
---|---|---|
찾기_텍스트 | 필수 | 찾고 있는 텍스트입니다. |
텍스트 내 | 필수 | 찾고 있는 텍스트가 포함된 텍스트입니다. |
시작 번호 | 선택 사항 | 검색을 시작할 문자를 정의합니다. |
이 예에서 FIND 함수 C5 셀은 3을 반환합니다. . “a” 이후 3위 “Adam”에 배치 . 따라서 FIND 함수와 함께 필요한 수식은 C5 셀 다음이 될 것입니다:
=FIND(“a”,B5)
- Enter 키를 누릅니다. .
- 결과적으로 필요한 값을 얻게 됩니다.
검색 기능
이 함수는 두 개의 필수 인수와 하나의 선택적 인수를 취합니다. find_text를 찾습니다. in_text 내의 문자열 find_text의 시작 위치 번호를 반환합니다. 두 번째 wthin_text의 첫 번째 문자에서 . Start_num 선택 사항입니다. in_text에 있는 숫자입니다. 검색을 시작하려는 인수. 검색 기능 대소문자를 구분하지 않습니다.
- 일반 구문
검색(find_text,in_text,start_num)
- 인수 설명
인수 | 요구사항 | 설명 |
---|---|---|
find_text | 필수 | 찾고 있는 텍스트입니다. |
텍스트 내 | 필수 | 검색하려는 텍스트가 포함된 텍스트입니다. |
start_num | 선택 사항 | 검색이 시작되는 문자를 정의합니다. |
이 예에서 SEARCH 기능 C5 셀은 1을 반환합니다. . “a” 이후 1위입니다. “Adam”에 배치 이 함수는 대소문자를 구분하지 않습니다. 따라서 SEARCH 기능을 사용하는 필수 수식은 C5 셀 다음이 될 것입니다:
=SEARCH(“a”,B5)
- Enter 키를 누릅니다. .
- 결과적으로 원하는 결과를 얻을 수 있습니다.
왼쪽 기능
이 함수는 왼쪽부터 시작하여 문자열에서 지정된 수의 문자를 반환합니다.
- 일반 구문
왼쪽(문자열, 길이 )
- 인수 설명
인수 | 요구사항 | 설명 |
---|---|---|
문자열 | 필수 | 가장 왼쪽 문자를 반환하는 문자열 표현식입니다. Null인 경우 문자열에 Null이 있습니다. 반환됩니다. |
길이 | 필수 | 반환할 문자 수를 나타내는 숫자 표현식입니다. 0인 경우 , 길이가 0인 문자열("")이 생성됩니다. 문자열의 문자 수가 해당 숫자보다 크거나 같으면 완전한 문자열이 반환됩니다. |
LEFT 함수 C5 셀은 '광고'를 반환합니다. . 2를 삽입할 것이기 때문에 “Adam”의 왼쪽에서 추출할 텍스트의 길이로 . 따라서 LEFT 함수와 함께 필요한 수식은 C5 셀 다음이 될 것입니다:
=LEFT(B5,2)
- 그런 다음 Enter 키를 누릅니다. .
- 결과적으로 원하는 결과를 얻습니다.
오른쪽 기능
이 함수는 문자열에서 오른쪽부터 지정된 수의 문자를 반환합니다.
- 일반 구문
오른쪽(문자열, 길이 )
- 인수 설명
인수 | 요구사항 | 설명 |
---|---|---|
문자열 | 필수 | 가장 오른쪽 문자를 반환하는 문자열 표현식입니다. 문자열에 Null이 있으면 Null이 반환됩니다. |
길이 | 필수 | It is an expression in numbers indicating the number of characters to return. If 0, a string with zero length (“”) is produced. The complete string is returned if the number of characters in the string is more than or equal to that number. |
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:
=RIGHT(B5,2)
- 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
Argument | Requirement | Explanation |
---|---|---|
text | Required | This is the text string from which you wish to extract the desired characters. |
start_num | Required | This is is the position where you want to extract the first character from the text. The text starts with start num 1, then continues with start num 2, etc. |
num_chars | Required | It indicates how many characters from the text you wish MID to return. |
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,
=MID(B5,1,3)
- 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
Argument | Requirement | Explanation |
---|---|---|
text | Required | This is the text which length you’re looking for.Spaces between the texts are considered as characters. |
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,
=LEN(B5)
- 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
Argument | Requirement | Explanation |
---|---|---|
text | Required | This is the text you want to convert in quotes, or a referenceto a cell that contains it. |
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,
=VALUE(B5)
- 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
Argument | Requirement | Explanation |
---|---|---|
value | Required | This is a number that you want to transform into text. |
format_text | Required | This is an expression that specifies the formatting you want to apply to the supplied value in text form. |
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,
=TEXT(B5,"dd/mm/yyyy ")
- 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
Argument | Requirement | Explanation |
---|---|---|
text | Required | The text from where you want to remove the spaces. |
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,
=TRIM(B5)
- 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
Argument | Requirement | Explanation |
---|---|---|
text | Required | This is any worksheet data that you want to clean up of non-printable characters. |
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,
=CHAR(7)& “A”
- 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,
=CLEAN(B5)
- 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
Argument | Requirement | Explanation |
---|---|---|
text1 | Required | This is the initial item to concatenate. The item could be a cell reference, a number, or a text value. |
text2, … | Optional | There are more text elements to join. You are allowed 255 items and 8,192 characters in total. |
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,
=CONCATENATE(B5,C5)
- 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
Argument | Requirement | Explanation |
---|---|---|
expression | Required | Expression is the value that will be compared to values value1 through value126, such as a number, date, or some text. |
ValueN | Required | We will compare ValueN against expression. |
ResultN | Required | When the matching valueN parameter matches the expression, ResultN is the value that will be returned. |
default | Optional | If no matches are discovered among the valueN and the expressions, the default value is what will be returned. |
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,
=SWITCH(B5,"Harry","Manager","Adam","Executive","Unknown")
- 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
Argument | Requirement | Explanation |
---|---|---|
logical_test | Required | The condition we would like to test. |
value_if_true | Required | The value that should be returned if logical test returns TRUE . |
value_if_false | Optional | The value that should be returned if logical test returns FALSE . |
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
Argument | Requirement | Explanation |
---|---|---|
value | Required | The argument that is examined for errors. |
value_if_error | Required | The value that should be returned if the formula results in an error. Evaluations are made for the following error types:#N/A , #VALUE! , #REF! , #DIV/0! , #NUM! , #NAME? , or #NULL! |
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,
=IFERROR(B5/B6,"No Result")
- 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,
=IF(AND(B5>B6,B7>B5,TRUE(),FALSE())
- 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,
=IF(OR(B5>B6,B7>B5,TRUE(),FALSE())
- 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,
=IF(NOT(B5>B6,B7>B5,TRUE(),FALSE())
- 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
Argument | Requirement | Explanation |
---|---|---|
index_num | Required | Specifies which value argument is selected. Index_num must be a number between 1 and 254 , or formula or reference to a cell containing a number between 1 and 254 . |
value1 | Required | This is a must-value. Based on this value the function matches the index number. |
value2, … | Optional | After the first value, all the values are optional. Based on index num , the CHOOSE function chooses a value or an action from a list of 1 to 254 value parameters. The arguments may be text, formulas, defined names, cell references, numbers, or other types of data. |
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,
=CHOOSE(C5, "Manager", "HR" ," Executive”)
- 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
Argument | Requirement | Explanation |
---|---|---|
reference | Required | This is the cell or a group of cells for which the row number is desired. |
The ROW function in the C5 cell will return 5 since the referenced cell B5 is in row 5 . So, the formula will be,
=ROW(B5)
- 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
Argument | Requirement | Explanation |
---|---|---|
reference | Required | This is the cell or a group of cells for which the column number is desired. |
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,
=COLUMN(B5)
- 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
Argument | Requirement | Explanation |
---|---|---|
array | Required | This is an array, an array formula, or a reference to a range of cells for which you want the number of rows. |
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,
=ROWS(B5:B8)
- 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
Argument | Requirement | Explanation |
---|---|---|
array | Required | This is an array, an array formula, or a reference to a range of cells for which you want the number columns. |
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,
=COLUMNS(B5:B8)
- 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
Argument | Requirement | Explanation |
---|---|---|
lookup_value | Required | You want to search up this value. The first column of the cell range that you supply in the table_array argument must contain the value you wish to look up. |
table_array | Required | The range of cells in which the VLOOKUP will look for the lookup_value and the return value. |
col_index_num | Required | the return value’s corresponding column number, which begins with 1 for table_array’s leftmost column. |
range_lookup | Optional | A logical value that tells the VLOOKUP function whether you want an exact match or a close match |
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,
=VLOOKUP(“Adam”,B5:C8,2)
- 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
Argument | Requirement | Explanation |
---|---|---|
lookup_value | Required | You want to search up this value. The first row of the cell range that you supply in the table_array argument must contain the value you wish to look up. |
table_array | Required | The range of cells in which the HLOOKUP will look for the lookup_value and the return value. |
row_index_num | Required | the return value’s corresponding row number, which begins with 1 for table_array’s leftmost column. |
range_lookup | Optional | A logical value that tells the HLOOKUP function whether you want an exact match or a close match |
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,
=HLOOKUP(“Adam”,B5:C8,2)
- 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
Argument | Requirement | Explanation |
---|---|---|
lookup_value | Required | A value in the first vector that LOOKUP looks for. Lookup value is a variable that can take the form of a number, text, logical value, name, or reference that points to a value. |
lookup_vector | Required | A set of cells with just one row or one column. Text, integers, or logical values can all be used as lookup_vector values. |
result_vector | Optional | A set of cells with just one row or column. The size of the lookup vector and the result vector arguments must match. It must be the same dimension. |
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,
=LOOKUP(“Adam”,B5:C8)
- 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
Argument | Requirement | Explanation |
---|---|---|
lookup_value | Required | The value that we want to match in lookup_array argument. |
lookup_array | Required | The set of cells being searched. |
match_type | Optional | The value 1, 0, or -1 . The lookup value and values in the lookup array are matched by Excel according to the match type argument. This argument’s default value is 1. |
The MATCH function will return 1 as “Adam” is the first element in the range B5:B8. So, the desired formula will be,
=MATCH(“Adam”,B5:B8)
- 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
Argument | Requirement | Explanation |
---|---|---|
array | Required | The range of cells or an array of constants where the function will operate. |
row_num | Required | In absent of the column_num , this must be present. It chooses which row in the array to return a value from. The column_num is necessary if row_num is left out. |
column_num | Optional | It chooses the array column from which to retrieve a value. The row_num is necessary if column_num is left out. |
The INDEX function will return “Jhon” in the D5 cell since it is in the second row of the B5:B8 범위. So, the formula will be,
=INDEX(B5:B8,2)
- 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
Argument | Requirement | Explanation |
---|---|---|
reference | Required | This is the reference that you want to use to determine the offset. The OFFSET function returns the #VALUE! error value if reference does not refer to a cell or range of neighboring cells. |
rows | Required | The number of rows that you wish the upper-left cell to point to, either up or down. |
cols | Required | The number of columns that you wish the upper-left cell to point to, either up or down. |
height | Optional | The desired height of the returned reference is expressed in rows. Height needs to be a positive integer. |
width | Optional | The desired width of the returned reference is expressed in columns. The width needs to be a positive integer. |
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,
=OFFSET(B5,2,1)
- 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
Argument | Requirement | Explanation |
---|---|---|
ref_text | Required | This is an A1-style reference, an R1C1-style reference, a name designated as a reference, or a reference to a cell as a text string are all examples of references to cells that contain references. The INDIRECT function returns the #REF! error value if the cell reference provided by ref text is invalid. |
a1 | Optional | This is a logical value describing the kind of reference that is present in the cell ref_text. |
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,
=INDIRECT(D8)
- Then, press Enter .
- As a result, we will get the value from the C8 cell.
GETPIVOTDATA Function
This function returns data stored in a PivotTable report.
- Generic Syntax
GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], …)
- Argument Description
Argument | Requirement | Explanation |
---|---|---|
data_field | Required | This is the name of the field in a PivotTable that has the information you want to access. It is necessary to quote this. |
pivot_table | Required | This is a reference to any PivotTable cell, range of cells, or designated range of cells. This data is needed to identify which PivotTable holds the data you’re looking for. |
field1, item1, field2, item2… | Optional | Field names and item names that define the data you want to obtain range from 1 to 126 in pairs. Any order is possible for the pairings. You must surround field names and names for objects other than dates and numbers in quotation marks. |
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,
=GETPIVOTDATA(“Revenue”,$B$4,”Month”,”January”)
- 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,
=RAND()*(C5-B5)+B5
- 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
Argument | Requirement | Explanation |
---|---|---|
bottom | Required | The smallest integer that the RANDBETWEEN function will return. |
top | Required | The largest integer that the RANDBETWEEN function will return. |
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,
=RANDBETWEEN(B5,C5)
- 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
Argument | Requirement | Explanation |
---|---|---|
number | Required | The real number that we want to round down to an integer. |
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,
=INT(B5)
- 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
Argument | Requirement | Explanation |
---|---|---|
number | Required | The number that we want to round. |
num_digits | Required | The number of digits to which we want to round the number argument. |
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,
=ROUND(B5,1)
- 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
Argument | Requirement | Explanation |
---|---|---|
number | Required | The numeric value that we want to round. |
significance | Required | The multiple to which we want to round. |
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,
=FLOOR(B5,1)
- 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
Argument | Requirement | Explanation |
---|---|---|
number | Required | The value that we want to round. |
significance | Required | The multiple to which we want to round. |
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,
=CEILING(B5,1)
- 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
Argument | Requirement | Explanation |
---|---|---|
number1 | Required | This is the first number that we want to add. |
[number2],…255 | Optional | These 256 numbers are optional and can be added at a time. |
It will simply return the sum of the numbers in the B5:B8 범위. So, the formula in the D5 cell will be,
=SUM(B5:B8)
- 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
Argument | Requirement | Explanation |
---|---|---|
range | Required | This is the selection of cells you want to use as criteria. Each range must include only numbers, names, arrays, or references containing numbers. Values that are blank or text are ignored. Dates in the default Excel format could be included in the selected range. |
criteria | Required | This is the condition based on which you will add the data. |
sum_range | Optional | If you want to add cells other than those listed in the range argument, you must add the actual cells. The cells that are given in the range parameter are added by Excel if the sum_range argument is not present. |
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,
=SUMIF(B5:B8,”0>”)
- 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
Argument | Requirement | Explanation |
---|---|---|
sum_range | Required | The set of cells to sum. |
criteria_range1 | Required | This is the range that is tested using Criteria1 . |
criteria1 | Required | It is the criteria that define which cells in Criteria_range1 will be added. |
criteria_range2, criteria2, … | Optional | These are the optional criteria. |
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,
=SUMIFS(B5:B8,”0>”)
- 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
Argument | Requirement | Explanation |
---|---|---|
number1 | Required | This is the first number or range that you want to multiply. |
number2, … | Optional | These are additional numbers or ranges that you want to multiply, up to a maximum of 255 arguments. |
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,
=PRODUCT(B5:B8)
- 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
Argument | Requirement | Explanation |
---|---|---|
number | Required | This is base number. It can be any real number. |
power | Required | This is the exponent to which the base number is raised. |
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,
=POWER(B5,C5)
- 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
Argument | Requirement | Explanation |
---|---|---|
number | Required | This is the number for which we will find the square root. |
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,
=SQRT(B5)
- 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
Argument | Requirement | Explanation |
---|---|---|
number | Required | This is the number for which we want to find the remainder. |
divisor | Required | This is the number by which we want to divide number. |
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,
=MOD(B5,C5)
- 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
Argument | Requirement | Explanation |
---|---|---|
function_num | Required | The range 1–11 or 101–111 designates the function to be applied for the subtotal. Rows that have been manually buried are included in rows 1–11 but not in rows 101–111; filtered-out cells are always excluded. |
ref1 | Required | This is the first named range or reference for which we want the subtotal.. |
ref2,… | Optional | This is the Named ranges or references 2 to 254 for which we want the subtotal. |
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,
=SUBTOTAL(1,B5:B8)
- 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
Argument | Requirement | Explanation |
---|---|---|
array1 | Required | This is the first array argument whose components we want to multiply and then add. |
[array2], [array3],… | Optional | These are the array arguments 2 to 255 whose components we want to multiply and then add. |
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,
=SUMPRODUCT(B5:B8,C5:C8)
- 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
Argument | Requirement | Explanation |
---|---|---|
value1 | Required | This is the first item, cell reference, or range within which we want to count numbers. |
value2, … | Optional | These are Up to 255 additional items, cell references, or ranges within which we want to count numbers. |
The COUNT function will return the number of cells in the range B5:B8 and will return 4 . So, the required formula will be,
=COUNT(B5:B8)
- 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
Argument | Requirement | Explanation |
---|---|---|
value1 | Required | This is the first argument representing the values that you want to count. |
value2, … | Optional | These are the additional arguments representing the values that we want to count, up to a maximum of 255 arguments. |
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,
=COUNTA(B5:B8)
- 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
Argument | Requirement | Explanation |
---|---|---|
range | Required | This is the set of cells you want to count |
criteria | Required | This is a number, expression, cell reference, or text string that determines which cells we will count. |
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,
=COUNTIF(B5:B8, “>0”)
- 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
Argument | Requirement | Explanation |
---|---|---|
criteria_range1 | Required | This is the first range in which to evaluate the related criteria. |
criteria1 | Required | This is the criteria in the form of a number, expression, cell reference, or text that define which cells we will count. |
criteria_range2, criteria2, … | Optional | These are the additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed. |
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,
=COUNTIFS(B5:B8, “>10”)
- 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
Argument | Requirement | Explanation |
---|---|---|
number1 | Required | This is the first number, cell reference, or range for which we want the average. |
number2, … | Optional | These are additional numbers, cell references or ranges for which we want the average, up to a maximum of 255. |
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,
=AVERAGE(B5:B8)
- 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
Argument | Requirement | Explanation |
---|---|---|
range | Required | This is a cell or cells to average, with or without names, arrays, or references containing numeric values. |
criteria | Required | This is the criteria of which cells are averaged, expressed as a number, expression, cell reference, or text. |
average_range | Optional | This is the actual set of cells to average. If omitted, range is used. |
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,
=AVERAGEIF(B5:B8, “>0”)
- 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
Argument | Requirement | Explanation |
---|---|---|
average_range | Required | This is a cell or cells to average, with or without names, arrays, or references containing numeric values. |
criteria_range1, criteria_range2, … | Required | Criteria_range1 is a must, the next criteria_ranges are optional. 1 to 127 ranges in which to evaluate the assigned criteria. |
criteria1, criteria2, … | Optional | Criteria1 is a must , the next criteria are optional |
The AVERAGEIFS function in the D5 cell will return the average of the values that are greater than zero from the B5:B8 범위. So, the formula will be,
=AVERAGEIFS(B5:B8,B5:B8, “>0”)
- 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
Argument | Requirement | Explanation |
---|---|---|
number1 | Required | Number1 is a must. |
number2, … | Optional | These subsequent numbers are optional. |
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,
=MIN(B5:B8)
- 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
Argument | Requirement | Explanation |
---|---|---|
number1 | Required | Number1 is a must tp get the maximum value. |
number2, … | Optional | These subsequent numbers are optional. |
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,
=MAX(B5:B8)
- 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
Argument | Requirement | Explanation |
---|---|---|
number1 | Required | Number1 is a must tp get the median value. |
number2, … | Optional | These additional numbers are optional. |
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,
=MEDIAN(B5:B8)
- 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
Argument | Requirement | Explanation |
---|---|---|
number1 | Required | The first argument representing a population sample. |
number2, … | Optional | Arguments in the range of 2 to 255 represent a sample of a population. |
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,
=STDEV(B5:B8)
- 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
Argument | Requirement | Explanation |
---|---|---|
number1 | Required | The first number argument represents a sample of a population. |
number2, … | Optional | Number arguments 2 to 255 represent a sample of a population. |
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
Argument | Requirement | Explanation |
---|---|---|
number | Required | This is the number whose rank we want to find. |
ref | Required | A set of, or a reference to, a list of numbers. Nonnumeric values in ref are not accepted. |
order | Optional | This is a number specifying how to rank the numbers. |
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,
=RANK(B5,$B$5:$B$8)
- 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
Argument | Requirement | Explanation |
---|---|---|
array | Required | This is the array or range of data for which we want to determine the k-th largest value. |
k | Required | This is the position of the largest value that the function will return. For example:the 2nd largest or the 3rd largest value. |
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,
=LARGE(B5:B8,2)
- 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
Argument | Requirement | Explanation |
---|---|---|
array | Required | This is the array or range of data for which we want to determine the k-th samllest value. |
k | Required | This is the position of the smallest value that the function will return. For example the 2nd smallest or the 3rd smallest value. |
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,
=SMALL(B5:B8,1)
- 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,
=EVEN(ROW())=ROW()
- Then, click on the Format button to open Format Cells dialogue box.
- Then, go to the Fill tab in the Format Cells dialogue box.
- 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.
=ODD(ROW())=ROW()
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 dialogue box.
- 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 from the available options.
- 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,
=RSQ(D5:D10,C5:C10)
- 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 boxes.
- 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.