Excel 数式チートシート: 中級ガイド

これでExcel の基本をマスターできました。ありがとうございます! さて、あなたは物事をワンランク上げて、より高度なものに飛び込むことに決めましたよね? 便利な中級 Excel チートシートをあなたのためにまとめたので、あなたは幸運です!

この Microsoft Excel 中級チートシートは、数値分析、テキスト処理、複雑な日付と時刻のロジックのスキルをレベルアップする幅広いトピックをカバーしています。

Excel 中間チートシートをデスクトップ参照として使用すると、Microsoft Excel のより強力な数式と関数を深く掘り下げることができます。

では、必ず読み進めてください。また、以下のチートシートを印刷して保存してください。

始めましょう！

Excel-formulas-cheatsheet-intermediate-1ダウンロード

目次

• 中級の数学公式
  • 条件付きロジック
• 中間の統計公式
  • 1.ミナとマクサ
  • 2. カウンタとカウンティフ
• 財務分析用の Excel 式
  • 1. 製品の機能
  • 2. QUOTIENT 関数と LOG 関数
  • 投資シナリオに応じた機能
• Excel の日付と時刻の中級数式
  • 1. 中間月機能
  • 2. 中間週の機能
  • 3. 中間時間​​関数
• 条件代数および論理代数用の Excel 関数
  • 1.IF関数
  • 2. AND関数
  • 3. OR関数
  • 4. 機能ではありません
• セルの参照
  • 1. 間接関数
  • 2. INDEX機能
  • 3. オフセット機能
• 中間テキストの式
  • 1.TEXTJOIN関数
  • 2. REPLACE関数
  • 3.置換機能
• 中間検索式
• Microsoft Excelのエラーを処理するための式
  • 1.IFERROR関数
  • 2.IFNA関数
• 数式と関数のショートカット
• 中間のセルの書式設定
• 最終的な考え

中級の数学公式

初心者向けチートシートでは、SUM 関数などの最も一般的な初心者向け数学関数を取り上げました。中級ユーザーは、次のより複雑な関数に慣れる必要があります。

• ABS()は数値の絶対値を返します。

• SQRT()は数値の平方根を返します

• RAND() は0 から 1 までの乱数を提供します

条件付きロジック

中級ユーザーは、次の関数を使用して複数の条件に基づいて数学計算を実行する方法も知っておく必要があります。

• SUMIFS(合計範囲, 基準範囲1, 基準1, [….])

• COUNTIFS(基準範囲1, 基準1, [基準範囲2, 基準2], …)

• AVERAGEIFS(基準範囲1, 基準1, [基準範囲2, 基準2], …)

関数は指定された基準を評価し、TRUE または FALSE の論理値を生成します。

列 A、B、C に次の売上データがあるとします。

赤い T シャツの売上の合計を計算するには、SUMIFS 式を使用します。

=SUMIFS(C2:C6、A2:A6、「Tシャツ」、B2:B6、「レッド」)

赤い T シャツの行数をカウントするには、COUNTIFS 式を使用します。

=COUNTIFS(C2:C6, A2:A6, “Tシャツ”, B2:B6, “赤”)

赤い T シャツの平均売上を計算するには、AVERAGEIFS 式を使用します。

=AVERAGEIFS(C2:C6, A2:A6, “Tシャツ”, B2:B6, “レッド”)

中間の統計公式

Excel には、MIN や MAX などの基本的な関数のほかに、中級ユーザー向けの幅広い統計式が用意されています。最も役立つものをいくつか紹介します。

• ミナ

• マックスサ

• カウンタ

• カウンティフ

1.ミナとマクサ

より一般的に使用される MIN 関数と MAX 関数は、テキスト値を無視します。

代替の MINA 関数と MAXA 関数は、最高値または最低値を検索するときにテキストと数値の両方を考慮します。テキスト値はゼロであるかのように評価されます。

違いは下の図に示されています。最初の行は最小値 10 と評価されますが、2 番目の行はテキスト値が存在するため 0 と評価されます。

2. カウンタとカウンティフ

COUNTA 関数は、範囲内の空でないセルの数をカウントするために使用されます。

COUNTIF 関数は、特定の条件または基準を満たす範囲内のセルの数を数えるために使用されるため、あまり具体的ではありません。

これらの関数を使用して、列内の個別の値の数をカウントすることもできます。このビデオではその方法を示しています。

財務分析用の Excel 式

投資の予測などの財務分析を実行するときに使用したい公式がいくつかあります。

• PRODUCT(数値1, [数値2…])

• QUOTIENT(分子, 分母)

• LOG(数値, [基数])

1. 製品の機能

1,000 ドルの初期投資 (セル B1) の 5 年後 (セル B3) の年利 4% (セル B2) での将来価値を計算するとします。

PRODUCT 関数を使用して、次の式で将来の価値を計算します。

=B1 * 積(1 + B2)^B3

この式は、投資の将来価値を返します。

2. QUOTIENT 関数と LOG 関数

次のステップは、指定された金利で投資の価値が 2 倍になるまでに何年かかるかを計算することです。

QUOTIENT 関数を LOG 関数と組み合わせて使用​​すると、次のように計算されます。

=QUOTIENT(LOG(2) / LOG(1 + B2), 1)

この式はサンプル データでは 17 を返します。これは、年利 4% で投資の価値が 2 倍になるまでに 17 年かかることを示しています。

この図は、Excel ワークシート内の数式を示しています。

投資シナリオに応じた機能

これらは、特定の財務シナリオに適したいくつかの関数です。それらの 1 つはまさにあなたが必要とするものかもしれません:

• NPV – NPV (正味現在価値) 関数は、割引率に基づく一連の将来キャッシュ フローに基づいて投資の正味現在価値を計算します。

• ACCRINT – ACCRINT関数は、定期的に利息が支払われる証券の未払利息を計算します。これは、最後の支払日から特定の決済日までに証券に対して得られた利息を判断するのに役立ちます。

• INTRATE – INTRATE関数は、全額投資された証券の金利を計算します。

• PMT – PMT関数は、債務証券の支払総額を計算します。

• IRR – IRR関数は内部収益率を提供します。

• YIELD – YIELD関数は、金利、額面、満期に基づいて証券の利回りを提供します。

Excel の日付と時刻の中級数式

Excel の基本的な日付と時刻の関数には、現在の日付を表す NOW 関数と TODAY 関数が含まれます。中級ユーザーは、以下を使用して特定の日付からコンポーネントを抽出する方法も知っている必要があります。

• DAY(日付)

• 月(日付)

• 年(日付)

数式=MONTH(“2023 年 4 月 23 日”) は、 4 か月目の結果として 4 を返します。同様に、DAY 関数と YEAR 関数はそれぞれ 23 と 2023 を返します。

1. 中間月機能

中級ユーザーは、日付に月を加算または減算したり、月の終わりを見つけたりすることがあります。

• EDATE(開始日, 月数)

• EOMONTH(開始日, 月数)

たとえば、数式=EDATE(“23 April 2023”, 2) は2 か月後の日付を計算します。

数式=EOMONTH(23 April 2023”, 2) は、 2 か月後の月末を計算します。

結果は「2023 年 6 月 30 日」になります。これは、6 月が 30 日しかないことを考慮しています。3 か月を指定した場合、結果は「2023 年 7 月 31 日」になります。

2. 中間週の機能

SUM 関数と WEEKDAY 関数を組み合わせて使用​​すると、日付範囲内の全体の稼働日の数をカウントできます。

WEEKDAY 関数は、指定された日付の曜日を返します。2 番目のパラメータは番号付けシステムを決定し、デフォルトは日曜日から土曜日までです。

B1:B40 のセル範囲に一連の日付がある場合は、次の数式を使用して稼働日を計算します。

=SUM(–(WEEKDAY(B1:B40,2)>5))

3. 中間時間​​関数

YEARFRAC 関数は、2 つの日付の間の 1 年の分数を計算します。

TIMEVALUE 関数は、テキストとして表された時間を 1 日 24 時間の割合を表す 10 進数に変換するために使用されます。

結果は Excel のシリアル番号で、1 は 1 日 24 時間を表し、0.5 は 12 時間を表し、0.25 は 6 時間を表します。

セル A1 に「17:45」というテキスト形式の時刻があり、それを10 進数に変換するとします。これは式です:

=TIMEVALUE(A1)

値 0.74 は、1 日 24 時間のうち、6 時 15 分前の時点で経過した割合を表します。

に移動して、時間セルを時間形式からテキスト形式に変更します。わかりやすくするために、これをパーセンテージとして書式設定することもできます。

条件代数および論理代数用の Excel 関数

条件関数と論理関数は、Excel での意思決定に不可欠です。次の関数をより基本的な Excel の数式と組み合わせて、強力なロジックを作成できます。

1. IF(条件、真の場合の値、偽の場合の値):

2. AND(条件1, 条件2, …)

3. OR(条件1, 条件2, …)

4. NOT(条件1, 条件2, …)

IF 関数は条件を評価し、条件が true か false に応じて異なる値を返します。

AND、OR、NOT 関数を使用すると、どの条件が真であるかを確認し、それに応じて決定を下すことができます。

以前に使用した販売データに基づいた例をいくつか示します。

1.IF関数

品目の売上が 15 未満の場合は「売上低」というテキストを表示したいとします。売上が 15 を超える場合は、新しい列に「売上高」を表示するとします。

次の数式を使用して、他の行にコピーします。

=IF(C2<15, “売上が少ない”, “売上が多い”)

2. AND関数

少なくとも 15 枚の販売がある T シャツについて TRUE または FALSE を表示するとします。次の式を使用します。

=AND(A3=”Tシャツ”, C3>=15)

3. OR関数

複数の条件をテストし、いずれかの条件が満たされた場合は TRUE を返し、そうでない場合は FALSE を返すとします。その場合は、OR 関数を使用します。

たとえば、セル A1 と B1 にテストのスコアがあり、どちらかのスコアが 80 を超えているかどうかを知りたい場合は、 =OR(A1>80 , B1>80)を使用できます。どちらか (または両方) のスコアが 80 を超えている場合は TRUE を返し、両方のスコアが 80 以下である場合は FALSE を返します。

4. 機能ではありません

アイテムがパーカーではないかどうかを確認したいとします。次の式を使用します。

=NOT(A2=”パーカー”)

セルの参照

初心者は、$A$1 や B2 などの絶対参照および相対参照の使用にすぐに慣れるでしょう。中級ユーザーは、間接参照、インデックス、オフセットの使用に慣れる必要があります。

1. 間接(ref_text)

2. INDEX(範囲、行番号、列番号)

3. OFFSET(参照セル、行、列):

1. 間接関数

INDIRECT 関数は、テキストとして入力された指定されたセル参照の値を返します。セル B3 を参照するには、次の数式を使用します。

=INDIRECT(“B3”)

単純なセル参照を使用する場合に比べて、この関数により動的な参照が得られるという利点があります。これは、スプレッドシートの構造が変更された場合 (行が削除された場合など)、Excel が参照を自動的に更新することを意味します。

2. INDEX機能

INDEX 関数は、行番号と列番号に基づいて指定された範囲内のセルを参照するために使用されます。

6 行の表のセル B3 を参照するには、次の数式を使用します。

=INDEX(B1:B6, 3)

3. オフセット機能

OFFSET 関数は、参照セルから指定された行数と列数だけ離れたセルまたは範囲を返します。

A1 から横 2 セル、下に 1 セルのセル値を表示する場合は、次の数式を使用します。

=オフセット(A1, 2, 1)

実際に動作する数式は次のとおりです。

中間テキストの式

初心者は、文字列の左側から 1 つ以上の文字を抽出する LEFT などのテキスト関数を知っておく必要があります。パワー ユーザーは、次のような機能に精通している必要があります。

1. TEXTJOIN(区切り文字、ignore_empty、text1、[text2、…])

2. REPLACE(old_text, start_num, num_chars, new_text)

3. SUBSTITUTE(テキスト, 古いテキスト, 新しいテキスト, [インスタンス番号])

1.TEXTJOIN関数

TEXTJOIN 関数は、指定した区切り文字を使用してセルを連結します。空白セルを無視する場所を指定することもできます。

列 A の一部の項目のカンマ区切りリストを作成するには、次の式を使用します。

=TEXTJOIN(", ", TRUE, A2:A4)

2. REPLACE関数

replace 関数を使用すると、ターゲットに置き換える文字列の開始位置と長さを指定できます。

文字列の最初の 2 文字を「XX」に置き換えるには、次の式を使用します。

=TEXTJOIN(A2, 1, 2, “XX”)

3.置換機能

置換関数は、別のテキスト文字列内の指定されたテキスト文字列を新しいテキスト文字列に置き換えます。

「ジーンズ」という単語の出現を「レギンス」に置き換えるには、次の式を使用します。

=SUBSTITUTE(A3、「ジーンズ」、「レギンス」)

実際に動作する数式は次のとおりです。

中間検索式

VLOOKUP や MATCH などの一般的な検索関数については、初心者向けのチートシートで説明されています。

中級ユーザーは、CHOOSE ルックアップ関数に慣れる必要があります。

• CHOOSE(インデックス番号, 値1, [値2, …])

CHOOSE 関数は、指定されたインデックス番号に基づいて値のリストから値を返します。

T シャツのサイズの列に 1、2、または 3 というラベルが付けられているとします。同じ行に、それぞれ小、中、大のカテゴリを表示したいとします。次の式を使用します。

=CHOOSE(A1、「小」、「中」、「大」)

Microsoft Excelのエラーを処理するための式

初心者用チートシートには、Excel を使用するときに表示される典型的なエラー メッセージがリストされています。中級ユーザーは、数式を使用してエラーを適切に処理できる必要があります。

1. IFERROR(値, if_error)

2. IFNA(値, 値_if_na)

1.IFERROR関数

ゼロ除算エラーはよくあることですが、Excel のデフォルト表示は #DIV/0! すべてのユーザーにとって明確ではありません。

IFERROR 関数を使用して、「ゼロで割ることはできません」という標準エラーを次の式で置き換えます。

=IFERROR(A1/B1, “ゼロで割ることはできません”)

この図は、3 行目の生のエラーと 4 行目を処理する IFERROR 関数を示しています。

2.IFNA関数

Excel の IFNA 関数は、#N/A エラーを検出して処理するために使用されます。

このチートシートの別のセクションには、CHOOSE 関数が示されています。インデックス番号が整数でない場合、この関数は N/A エラーを返します。

T シャツのサイズが 1.5 と入力され、ユーザーはこれにより S と M の間のカテゴリが表示されると誤解したとします。代わりに、 #N/Aエラーが表示されます。

より役立つエラーを提供するには、次の式を使用します。

=IFNA(CHOOSE(A2, “低”, “中”, “高”), “無効なインデックス”)

数式と関数のショートカット

ワークシートをより効率的に操作できるキーボード ショートカットがいくつかあります。

• F2 : アクティブなセルを編集し、セルの内容の最後に挿入ポイントを置きます。

• F9 : 数式の選択した部分の結果を計算して表示します。

• Ctrl + Shift + Enter : 配列数式を入力します。

• Shift + F3 : [関数の挿入] ダイアログ ボックスを開きます。

• Esc : 数式の入力をキャンセルし、元のセルの内容に戻します。

これらの Excel ショートカットの一部は、言語やキーボード レイアウトによっては使用できない場合があります。

中間のセルの書式設定

財務分析では、数値、日付、通貨を含むセルを適切に書式設定することが重要です。セルの書式を設定するには、次の手順に従います。

1. 書式設定したいセルを選択します。

2. 選択したセルを右クリックし、「セルの書式設定」を選択します。

3. 適切なカテゴリ (数値、通貨、日付など) を選択し、希望の形式を適用します。

財務で考慮すべき一般的な書式設定の種類には次のようなものがあります。

• 通貨: $1,234.56

• 割合: 12.34%

• 会計: ($1,234.00)

• 日付: 2023 年 5 月 10 日

最終的な考え

このチートシートで紹介されているさまざまな数式や関数に慣れてくると、ますます複雑になるタスクやプロジェクトを処理できるようになります。

あらゆるスキルを習得するには練習が重要なので、日々の仕事でこれらの公式を試すことを恐れないでください。彼らはあなたの専門知識を初心者レベルから中級レベルに引き上げます。

これは、Excel の中間関数を使用して作業を簡素化し、分析を合理化し、データから洞察を得るのに役立つ機能の一例にすぎません。

本当の魔法は、チートシートにある関数を組み合わせて、独自のニーズに合わせて調整するときに起こります。だから、ここで立ち止まらないでください。探索を続け、実験を続ければ、Excel は単なるツールではなく、ゲームチェンジャーであることがわかります。

物事を次のレベルに引き上げたいと感じていますか? Excelの高度な数式のチートシートを確認してください。