Office
 Computer >> コンピューター >  >> ソフトウェア >> Office

経営コンサルタント向けのトップ Excel 関数と機能

データ分析を実施し、クライアントにプレゼンテーションを行う必要がある経営コンサルタントの場合、Microsoft 製品は本当に素晴らしいリソースです。 Microsoft Excel は、間違いなく経営コンサルタントにとって最も重要なツールの 1 つです。 .この記事では、トップ Excel について説明します。 経営コンサルタント向けの機能と機能。

ここから練習用ワークブックをダウンロードできます。

上位 70 の Excel 関数

このセクションでは、上位 70 について説明します Excel でデータを管理および表示する際に重要な Excel 関数 .カテゴリに分けてイラストを1つずつお見せします。

DATE 関数 (8 つの関数)

DATE は、ビジネス データの管理に大きなメリットをもたらします。 関数 .ここでは、すべての DATE について簡単に説明します。 関数。

DAY 関数

Day 関数 単引数関数です。日付の日を返します。日は 1 の範囲の整数として与えられます 31まで .

  • 汎用構文

DAY(serial_number)

  • 引数の説明
引数 要件 説明
シリアル番号 必須 探している日付

DAY 関数の例

この例では、DAY 関数を使用します B5 のデータについて 細胞。その結果、その特定の月の日付の日を示す数値が得られます。 9 月 13 日以降 13日です 日、関数は 13 を返します .したがって、DAY を使用した必要な数式 セル C5 の関数 になります:

=DAY(B5)
  • Enter キーを押します .

経営コンサルタント向けのトップ Excel 関数と機能

  • その結果、その日の数値が得られます。
  • カーソルを下げると、残りのセルの値が表示されます。

経営コンサルタント向けのトップ Excel 関数と機能

月関数

この関数は引数を 1 つだけ取ります。シリアル番号で表される日付の月を返します。月は 1 (1 月) の範囲の整数で指定します 12 月 12 日まで .

  • 汎用構文

MONTH(serial_number)

  • 引数の説明
引数 要件 説明
シリアル番号 必須 検索しようとしている月の日付。

MONTH 関数の例

この例では、MONTH 関数を使用します。 B5 のデータについて 細胞。その結果、その特定の月の日付の月を示す数値が取得されます。以降、9 月 9 番目です 日、関数は 9 を返します .したがって、を使用した必要な数式 セル C5 の関数 になります:

=MONTH(B5)
  • 次に、Enter を押します .

経営コンサルタント向けのトップ Excel 関数と機能

  • その結果、その月の価値が得られます。
  • カーソルを下に移動して、残りの値を取得します。

経営コンサルタント向けのトップ Excel 関数と機能

YEAR 関数

この関数は引数を 1 つ取ります。日付に対応する年を返します。年は 1900 の範囲の整数として返されます 9999 まで .

  • 汎用構文

YEAR(serial_number)

  • 引数の説明
引数 要件 説明
シリアル番号 必須 検索する年の日付。

YEAR 関数の例

この例では、DAY 関数を使用します B5 のデータについて 細胞。その結果、その特定の月の日付の日を示す数値が得られます。 9 月 13 日以降 13日です 日、関数は 13 を返します .したがって、YEAR 関数を使用した必要な数式 セル C5 内 になります:

=YEAR(B5)
  • 次に Enter を押します .

経営コンサルタント向けのトップ Excel 関数と機能

  • その結果、その年の値が得られます。
  • 次に、カーソルを下に移動して残りの値を取得します。

経営コンサルタント向けのトップ Excel 関数と機能

WEEKDAY 関数

この関数は、1 つの必須引数と 1 つのオプション引数を取ります。日付に対応する曜日を返します。日は 1 からの範囲の整数として与えられます (日曜日 ) から 7 (土曜日 ) (デフォルト)。

  • 汎用構文

WEEKDAY(serial_number,[return_type])

  • 引数の説明
引数 要件 説明
シリアル番号 必須 検索しようとしている日付を表す連番。
Return_type オプション 戻り値の型を決定する数値。この数字は、実際には週のカウントが開始される日を意味します。デフォルトでは、週のカウントは日曜日から始まります。

WEEKDAY 関数の例

この例では、B5 に日付を挿入します。 WEEKDAY の引数としてのセル C5 の機能 細胞。今日は火曜日で、デフォルトでは 3rd であるため、この関数は 3 を返します。 曜日。 WEEKDAY 関数を使用した必要な数式 セル C5 内 になります:

=WEEKDAY(B5)
  • 次に、Enter を押します .

経営コンサルタント向けのトップ Excel 関数と機能

  • したがって、平日の値を取得します。
  • カーソルを最後のデータ セルに移動して、残りの値を取得します。

経営コンサルタント向けのトップ Excel 関数と機能

WEEKNUM 関数

この関数は、必須の引数を 1 つとオプションの引数を 1 つだけ取ります。特定の日付の週番号を返します。

  • 汎用構文

WEEKNUM(serial_number,[return_type])

  • 引数の説明
引数 要件 説明
シリアル番号 必須 週内の日付。
Return_type オプション 週の最初の曜日を確立する数値。 1 がデフォルトです。
WEEKNUM 関数の例

この例では、B5 セルの日付が WEEKNUM 関数の唯一の引数になります。 C5 で 細胞。関数は 38 を返します 日付は38日なので 年の週。したがって、セル C5 の WEEKNUM 関数を使用した必要な数式 になります:

=WEEKNUM(B5)
  • その後、Enter を押します .

経営コンサルタント向けのトップ Excel 関数と機能

  • 結果として、引数の値を取得します。

経営コンサルタント向けのトップ Excel 関数と機能

NETWORKDAYS 関数

この関数は、2 つの必須引数と 1 つのオプション引数を取ります。週末と祝日を除く 2 つの日付間の稼働日数を返します。

  • 汎用構文

NETWORKDAYS(start_date, end_date, [休日])

  • 引数の説明
引数 要件 説明
開始日 必須 開始日となる日
End_date 必須 終了日となる日。
休日 オプション 営業カレンダーから除外される、州および連邦の休日、変動休日などの 1 つまたは複数の日付のオプションの範囲
NETWORDAY 関数の例

この場合、B5 の日付 C5 cells will be passed as the argument for the NETWORKDAY function in the C5 細胞。 The function will return the difference between the two dates. So, the required formula with the NETWORKDAY function in cell C5 will be:

=NETWORKDAYS(B5,C5)
  • Press Enter .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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
Argument Requirement Explanation
year Required The year argument’s value might be one to four digits.
month Required A month of the year, from 1 to 12, represented by a positive or negative integer.
day Required A month of the year, from 1 to 31, is represented by a positive or negative integer.
Example of DATE Function

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:

=DATE(B5,C5,D5)
  • Press the Enter ボタン。

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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 オプションです。 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
Argument Requirement Explanation
Find_text Required This is the text you’re looking for.
Within_text Required This is the text that contains the text you’re looking for.
Start_num Optional It defines the character at which the search should begin.
Example of FIND Function

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:

=FIND(“a”,B5)
  • Hit Enter .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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 オプションです。 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
Argument Requirement Explanation
find_text Required This is the text you’re searching for.
within_text Required This is the text that contains the text you’re searching for.
start_num Optional It defines the character at which the search begins.
Example of SEARCH Function

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:

=SEARCH(“a”,B5)
  • Hit Enter .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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
Argument Requirement Explanation
string Required String expression that returns the leftmost characters. If Null is present in the string, Null is returned.
length Required 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 LEFT Function

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:

=LEFT(B5,2)
  • Then, hit Enter .

経営コンサルタント向けのトップ Excel 関数と機能

  • Consequently, we get the result we want.

経営コンサルタント向けのトップ Excel 関数と機能

RIGHT Function

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

  • Generic Syntax

RIGHT( string, length )

  • Argument Description
Argument Requirement Explanation
string Required String expression that returns the rightmost characters. If Null is present in the string, Null is returned.
length Required 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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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 .

経営コンサルタント向けのトップ Excel 関数と機能

  • Consequently, we will have the desired result.

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of LEN Function

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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of VALUE Function

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 .

経営コンサルタント向けのトップ Excel 関数と機能

  • As a result, we will get the value.

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of TEXT Function

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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of TRIM Function

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

=TRIM(B5)
  • Then, hit Enter .

経営コンサルタント向けのトップ Excel 関数と機能

  • Consequently, we will get the desired outcome.

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of CLEAN Function

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

=CHAR(7)& “A”
  • Then, hit Enter .

経営コンサルタント向けのトップ Excel 関数と機能

  • 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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of CONCATENATE Function

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 .

経営コンサルタント向けのトップ Excel 関数と機能

  • As a result, the texts will be merged.

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of SWITCH Function

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 ボタン。

経営コンサルタント向けのトップ Excel 関数と機能

  • As a result, we will get the output.

経営コンサルタント向けのトップ Excel 関数と機能

IF Function

This function has 3 引数。 If argument 1 is TRUE , then it does as argument 2 指示します。 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!
Example of IFERRRO Function

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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

Combining TRUE, FALSE, AND, and IF Functions

We can not use the  TRUE and FALSE functions 独立して。 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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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 .

経営コンサルタント向けのトップ Excel 関数と機能

  • As a result, we will get an output.

経営コンサルタント向けのトップ Excel 関数と機能

Combining TRUE, FALSE, NOT, and IF Functions

We can not use the  TRUE and FALSE functions 独立して。 So we will use them in combination with the NOT and IF functions この例では。

手順:

  • Select the D5 cell and write down the following formula,
=IF(NOT(B5>B6,B7>B5,TRUE(),FALSE())
  • Then, hit Enter .

経営コンサルタント向けのトップ Excel 関数と機能

  • Consequently, we will get a logical answer.

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of CHOOSE Function

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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of ROW Function

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 .

経営コンサルタント向けのトップ Excel 関数と機能

  • Consequently, we will get the desired outcome.

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of COLUMN Function

This function will return 2 in the C5 cell because the referenced cell is in the 2nd 列。そう。 the desired formula in the C5 cell will be,

=COLUMN(B5)
  • Then, hit Enter .

経営コンサルタント向けのトップ Excel 関数と機能

  • Consequently, we will get the desired outcome.

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of ROWS Function

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

=ROWS(B5:B8)
  • Then, hit Enter .

経営コンサルタント向けのトップ Excel 関数と機能

  • Consequently, we will get the desired outcome.

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of COLUMNS Function

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 .

経営コンサルタント向けのトップ Excel 関数と機能

  • Consequently, we will get the desired outcome.

経営コンサルタント向けのトップ Excel 関数と機能

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
Example of VLOOKUP Function

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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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
Example of HLOOKUP Function

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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of LOOKUP Function

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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of MATCH Function

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 .

経営コンサルタント向けのトップ Excel 関数と機能

  • Consequently, we will have a match.

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of INDEX Function

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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of OFFSET Function

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 .

経営コンサルタント向けのトップ Excel 関数と機能

  • Consequently, we will have an output.

経営コンサルタント向けのトップ Excel 関数と機能

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 .

経営コンサルタント向けのトップ Excel 関数と機能

  • As a result, we will get the value from the C8 cell.

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of GETPIVOTDATA Function

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 .

経営コンサルタント向けのトップ Excel 関数と機能

  • As a result, we will get an output.

経営コンサルタント向けのトップ Excel 関数と機能

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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of RANDBETWEEN Function

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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of INT Function

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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of ROUND Function

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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of FLOOR Function

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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of CEILING Function

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

=CEILING(B5,1)
  • After that, press Enter .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of SUM Function

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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of SUMIF Function

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 .

経営コンサルタント向けのトップ Excel 関数と機能

  • Consequently, we will get the conditional summation.

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of SUMIFS Function

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 .

経営コンサルタント向けのトップ Excel 関数と機能

  • Consequently, we will get the conditional summation.

  経営コンサルタント向けのトップ Excel 関数と機能

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 .

経営コンサルタント向けのトップ Excel 関数と機能

  • Consequently, we will get the product.

経営コンサルタント向けのトップ Excel 関数と機能

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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of SUBTOTAL Function

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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of SUMPRODUCT Function

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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of COUNT Function

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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of COUNTA Function

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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of COUNTIF Function

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 .

経営コンサルタント向けのトップ Excel 関数と機能

  • As a result, we will have conditional counting.

経営コンサルタント向けのトップ Excel 関数と機能

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 .

経営コンサルタント向けのトップ Excel 関数と機能

  • As a result, we will get the count.

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of AVERAGE Function

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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of AVERAGEIF Function

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

=AVERAGEIF(B5:B8, “>0”)
  • Then, press Enter .

経営コンサルタント向けのトップ Excel 関数と機能

  • Consequently, we will get a conditional average.

経営コンサルタント向けのトップ Excel 関数と機能

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
Example of AVERAGEIFS Function

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.

経営コンサルタント向けのトップ Excel 関数と機能

  • Consequently, we will have a conditional average.

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of MIN Function

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.

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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.
Example of MEDIAN Function

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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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 .

経営コンサルタント向けのトップ Excel 関数と機能

  • Consequently, we will get the smallest value.

経営コンサルタント向けのトップ Excel 関数と機能

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 .

経営コンサルタント向けのトップ Excel 関数と機能

  • 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 .

経営コンサルタント向けのトップ Excel 関数と機能

  • 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.

経営コンサルタント向けのトップ Excel 関数と機能

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 .

経営コンサルタント向けのトップ Excel 関数と機能

  • 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 ダイアログ ボックス。

経営コンサルタント向けのトップ Excel 関数と機能

  • 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 .

経営コンサルタント向けのトップ Excel 関数と機能

  • 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()

経営コンサルタント向けのトップ Excel 関数と機能

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 .

経営コンサルタント向けのトップ Excel 関数と機能

  • 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 ダイアログ ボックス。

経営コンサルタント向けのトップ Excel 関数と機能

  • 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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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.

経営コンサルタント向けのトップ Excel 関数と機能

  • 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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

  • 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.

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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.

経営コンサルタント向けのトップ Excel 関数と機能

  • 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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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.

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

  • 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.

経営コンサルタント向けのトップ Excel 関数と機能

  • 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.

経営コンサルタント向けのトップ Excel 関数と機能

  • Then, from the Trendline Options , select Linear .
  • Then check the “Display Equation on chart” and “Display R -squared value on chart”  ボックス。

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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.

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

  • 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 .

経営コンサルタント向けのトップ Excel 関数と機能

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

経営コンサルタント向けのトップ Excel 関数と機能

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.


  1. Excel でデータベース関数を使用する方法 (例あり)

    この記事では、Excel でデータベース関数を使用する方法について説明します。 Excel には 12 のデータベース関数があります。この記事では、それらを 1 つずつ適用する方法を示します。次の図は、この記事の目的を強調しています。記事をざっと見て、残りの使い方を学んでください。 下のダウンロードボタンから練習用ワークブックをダウンロードできます。 Excel のデータベース関数の概要 Excel のデータベース関数を使用すると、特定のデータベースに対して合計、積、平均などの簡単な操作を実行できます。 Excel には 12 のデータベース関数があります。これらは、DSUM、DAVE

  2. フォトグラファー向けスタジオ管理ソフトウェア トップ 7

    誰でもスマートフォンで写真をクリックできますが、プロの写真になると、大きなファットレンズを備えたデジタル一眼レフなどのプロのものが必要です。また、これらのガジェットを扱うだけでは不十分な場合、写真家は、クライアントとの関係の維持、フィードバック調査の実施、プロジェクトと財務の処理、予約と電子メールの送信など、他のタスクを実行する必要があります。この記事では、可能な最高の写真スタジオ管理ソフトウェアに焦点を当てています写真家は自分の仕事に集中し、残りの面倒を見ることができます。 では、スタジオ管理ソフトウェアとは何ですか?さて、写真スタジオ管理ソフトウェアは、写真ビジネスを運営し、物