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

基本を一通り学習し、中級の内容に取り組みました。これで、Excel の高度な数式という大きな武器と格闘する準備が整いました。Excel には高度な計算を行うための高度な関数や数式が多数あるため、必要なものを正確に示したガイドがあると便利です。

この Excel 式のチートシートでは、高度な予測式、統計分析、データ操作関数、エラー処理などについて説明します。

このリファレンスでは、これらの高度な機能の使用方法についての知識を得ることができます。各数式には明確な説明、構文、および実践的な例が付いており、中級の Excel ユーザーが上級のパワー ユーザーになるのに役立ちます。

チートシートをダウンロードして印刷し、手元に保管してください。

さて、始めましょう。

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

まず、配列数式について説明します。

目次

• 配列数式
  • 1. ユニークな機能
  • 2. ソート機能
  • 3.フィルター機能
• Excel関数のランダム化
  • 1.RANDBETWEEN関数
  • 2. RANDARRAY関数
• Microsoft Excel の高度な予測式
  • 1.FORECAST.ETS関数
  • 2. トレンド機能
• 高度な統計公式
  • 1.パーセンタイル関数
  • 2. 四分位関数
• 高度なデータ分析と操作の公式
  • 1.トランスポーズ機能
  • 2. 周波数機能
  • 3.GETPIVOTDATA関数
• 高度なエラー処理
• 高度な検索式
  • 1.XLOOKUP関数
  • 2.XMATCH関数
• 最終的な考え

配列数式

初心者向けのチートシートでは、データを手動で並べ替えてフィルターする方法を説明します。上級ユーザーは、配列数式を使用してプログラムでこれを実行します。

配列数式を使用すると、複数のセルで同時に計算を実行できます。これらは 3 つの主要な機能です。

1. 個性的

2. 選別

3. フィルター

これらの関数の一部は、最新バージョンの Microsoft Excel でのみ使用できます。

1. ユニークな機能

UNIQUE 関数は範囲を受け入れ、一意の値のリストを返します。

衣料品の販売データがあるとします。列 A の一意の項目を見つけるには、次の式を使用します。

=ユニーク(A2:A6)

2. ソート機能

SORT 関数は、範囲の内容を並べ替えます。構文は次のとおりです。

SORT(配列, [ソートインデックス], [ソート順序], [列順])

• array : 並べ替える値の範囲。

• sort_index : ソートする列 (デフォルトでは 1)

• sort_order : 昇順の場合は 1 (デフォルト)、降順の場合は 2)。

• by_col : 列でソートする場合は TRUE (デフォルト)、行でソートする場合は FALSE。

最後の 3 つの引数はオプションであり、通常はデフォルト値が使用されます。

サンプル データを最初の列で並べ替えるには、次の式を使用します。

=ソート(A2:C6)

3.フィルター機能

FILTER 関数を使用すると、特定の条件で範囲をフィルターできます。構文は次のとおりです。

=FILTER(配列、インクルード、[if_empty])

• array : フィルタリングする範囲。

• include : どの値をフィルタリングするかを決定する条件。

• if_empty : フィルタリング基準を満たす値がない場合に何を返すかを指定します (デフォルトは「」)。

サンプル データの行をフィルターして、売上額が 15 ドルを超える部分のみを表示するとします。次の式を使用します。

=FILTER(A2:C6, C2:C6>15)

Excel関数のランダム化

中間チートでは、0 から 1 までの乱数を生成する RAND 関数の使用方法を示します。

Excel の上級ユーザーは、ランダム化関数を使用してサンプル データを迅速に生成する方法を知っています。

1.RANDBETWEEN関数

RANDBETWEEN 関数は、下位と上位の数値を 0 と 1 以外の値として指定できるため、RAND よりも柔軟性が高くなります。

1 ～ 100 の数値を含むデータを生成するには、セル A1 に次の数式を入力します。

=RANDBETWEEN(1, 100)

次に、セルを必要な数の行と列にコピーします。ランダム化された数値のグリッドを生成するには数秒かかります。

2. RANDARRAY関数

RANDBETWEEN 関数の手動コピーを避けたほうがよいと思われるかもしれません。超上級者向けには、最新バージョンの Microsoft Excel で新しい RANDARRAY 関数を使用できます。

構文は次のとおりです。

RANDARRAY([行], [列], [最小], [最大], [整数])

• rows : 行数

• columns : 列数

• min : 最小値

• max : 最大の数値

• 整数: デフォルトは TRUE、それ以外の場合は 10 進数を使用します。

6 行 9 列にわたって 1 ～ 100 の乱数を生成するには、次の式を使用します。

=RANDARRAY(6, 9, 1, 100, TRUE)

Microsoft Excel の高度な予測式

Excel の予測関数は、既存のデータ傾向に基づいて将来の値を予測するために使用されます。これらの機能は、データに基づいてパターンを特定し、傾向を予測するのに役立ちます。

1.FORECAST.ETS関数

Excel 2016 では、古い FORECAST 関数が一連の新しい関数に置き換えられました。

必要な特定の予測モデルに基づいて関数を選択します。たとえば、FORECAST.ETS 関数は指数平滑化アルゴリズムを使用します。

構文は次のとおりです。

FORECAST.ETS(ターゲット日付、値、タイムライン)

• target_date : 計算値が必要な日付。

• 値: 履歴データ。

• タイムライン: 日付の範囲

列 A に 1 月 1 日から 5 日までの日付があり、列 B に売上高があるとします。この数式は次の売上高を予測します。

=FORECAST.ETS(「2023 年 1 月 6 日」、B2:B6、A2:A6)

2. トレンド機能

TREND 関数は、最小二乗法に基づいて一連の値を予測します。配列を返します。構文は次のとおりです。

TREND(既知_y, [既知_x], [新規_x], [定数])

• known_y : y 値の範囲

• known_x : x 値の範囲

• new_x : 計算値の範囲

多くの場合、known_y はデータ ポイントであり、known_x は日付です。

前の例と同じデータを使用して、セル C2 に次の数式を入力できます。一連の値が生成されます。

=トレンド(B2:B6、A2:A6)

高度な統計公式

高度な統計関数には、パーセンタイルと四分位の計算が含まれます。一部の数学関数は下位互換性のために利用できますが、最新バージョンを使用することをお勧めします。

1.パーセンタイル関数

この関数は、特定の値を下回るデータ ポイントの割合を計算します。構文は次のとおりです。

PERCENTILE.INC(配列, k)

• 配列: セル範囲

• k : 0 から 1 までのパーセンタイル

列 B のデータの 70 パーセンタイルを計算するとします。次の式を使用します。

=PERCENTILE.INC(B2:B6, 0.7)

2. 四分位関数

この関数はパーセンタイル関数のバリエーションですが、データを分割するのに 4 分の 1 を使用します。構文は次のとおりです。

QUARTILE.INC(配列, クォート)

• 配列: データの範囲

• クォート: 25 パーセンタイルは 1、50 パーセンタイルは 2、75 パーセンタイルは 3、最大値は 4。

次の数式は、列 A のデータの最初の 4 分の 1 を計算します。

=QUARTILE.INC(A2:A5, 1)

高度なデータ分析と操作の公式

いくつかの高度な機能を使用して、データの形式を切り替えたり、度数分布を分析したり、ピボット テーブルからデータを抽出したりできます。

1. 転置

2. 周波数

3. ピボットデータの取得

1.トランスポーズ機能

行のデータを列に移動したり、その逆のデータを列に移動したりすることが必要な場合があります。これを手動で行うことも、代わりに TRANSPOSE 関数を使用することもできます。

セル A2、A3、A4 に「T シャツ」、「パーカー」、「ジーンズ」という項目があるとします。これらを列見出しにしたいとします。この関数は単一行の値を返します。

=転置(A2:A4)

2. 周波数機能

この関数は、データセット内の値の頻度分布を計算します。構文は次のとおりです。

FREQUENCY(データ配列, ビン配列)

• data_array : 値の範囲。

• bins_array : 使用する間隔。

列 B に売上データがあり、次の金額に基づいて値の度数分布を分析するとします。

• 20未満。

• 20から80まで。

• 80以上。

これは 3 つのビンを表し、次の式で計算できます。

=FREQUENCY(A2:A6, {20,80})

Excel の度数分布の詳細については、次のビデオをご覧ください。

3.GETPIVOTDATA関数

この機能を使用すると、ピボット テーブルから要約情報を抽出できます。構文は次のとおりです。

GETPIVOTDATA(データフィールド, ピボットテーブル, [フィールド1, 項目1], [フィールド2, 項目2], …)

• data_field : ピボット テーブルから取得するデータ フィールドまたは値。

• pivot_table : ピボット テーブルへの参照。

• field1、item1 など: フィルタリングするフィールドと項目のペア。

販売される商品の色に基づいたピボット テーブルがあるとします。赤色のアイテムの売上を抽出するには、次の式を使用します。

=GETPIVOTDATA(“売上”, A1, “色”, “赤”)

高度なエラー処理

最も基本的な Excel の数式でもエラーが発生する可能性があります。中級ユーザーは、ISERROR を使用してエラーを処理する方法を知っておく必要があります。上級ユーザーは、エラーを識別するための ERROR.TYPE 関数にも精通している必要があります。

ERROR.TYPE 関数は、セルまたは数式内の特定の種類のエラーを識別するのに役立ちます。

#N/A、#VALUE!、#REF! など、さまざまなエラー タイプに対応する数値を返します。

セル A1 にエラーがあり、そのエラーの種類を特定したいとします。次の式は、特定のエラーに対応する数値を返します。

=エラー.タイプ(A1)

これを複数の関数と組み合わせて、エラーの種類に応じて異なる対応を行うことができます。最も一般的なエラーとその値は次のとおりです。

1. ＃ヌル！(範囲内に共通のセルが見つかりません)

2. #DIV/0! (ゼロまたは空のセルによる除算)

3. ＃価値！(式内の不適切なデータ型または引数)

4. #REF! (参照セルが削除されているか、循環参照があります)

5. ＃名前？(Excelでは関数や範囲が認識されません)

6. #NUM! (無効な数値)

7. #N/A (値が見つかりません)

3 つの特定のエラー タイプを処理するとします。タイプに基づいて特定のエラー メッセージを表示するには、次の式を使用します。

=IF(ISERROR(C2), IF(ERROR.TYPE(C2)=2, “ゼロ除算エラー”, IF(ERROR.TYPE(C2)=3, “無効な値エラー”, IF(ERROR.TYPE(C2) )=7、「値が見つからないエラー」、「その他のエラー」)))、C2)

高度な検索式

初級者および中級者のチートシートでは、検索関数の選択を取り上げています。以下にいくつかの高度なオプションを示します。

1. XLOKUP

2. エックスマッチ

1.XLOOKUP関数

この検索関数を使用すると、範囲内の値を検索し、別の列または範囲から対応する値を返すことができます。

VLOOKUPのような単純な検索関数よりも汎用性が高くなります。構文は次のとおりです。

XLOOKUP(lookup_value, lookup_array, return_array, [match_mode], [search_mode], [if_not_found])

• lookup_value : 検索する値。

• lookup_array : ルックアップの範囲。

• return_array : 対応する値を表示する範囲。

• match_mode : 完全一致 (0)、次に小さい (1)、次に大きい (-1)、またはワイルドカード一致 (2)。

• search_mode : 上から下の場合は -1、下から上の場合は 1、二分探索の場合は 2。

• if_not_found : 一致するものが見つからなかった場合に返す値を設定します。

最初に出現した衣料品のデータ範囲を検索し、販売額を返したいとします。この数式は「Hoodie」というテキストを検索し、見つかった場合は隣接するセルの値を返します。

=XLOOKUP(「パーカー」, A2:A6, B2:B6)

2.XMATCH関数

この関数を使用すると、範囲または配列内で指定された値の位置を見つけることができます。構文は次のとおりです。

XMATCH(lookup_value, lookup_array, [match_type], [search_mode])

• lookup_value : 検索する値。

• lookup_array : 検索する範囲。

• match_type : 完全一致 (0)、次に小さい (-1)、次に大きい (1)。

• search_mode : 二分探索 (1) または線形探索 (2)。

列 B 内の範囲内で最初に出現する黄色の項目を検索するとします。次の式を使用します。

=XMATCH(“黄”, B2:B6, 0)

最終的な考え

このチートシートは、統計分析、検索式、データ操作テクニック、エラー処理戦略に至るまで、幅広い機能をカバーしています。

提供されている例と説明は、これらの高度な公式をわかりやすくするのに役立ち、経験が限られている人でもアクセスできるようにしています。

これらを Excel タスクに組み込み始めると、Excel スキルを上級レベルに引き上げることができます。

ただし、このチートシートは氷山の一角にすぎないことを覚えておいてください。創造力を発揮し、これらの公式を組み合わせて、独自の課題を解決するために調整すると、本当に素晴らしいことが起こります。Excel はキャンバスのようなもので、これらの数式はパレットです。さあ、傑作を描きましょう!