配列数式のガイドラインと例

配列数式のガイドラインと例

注: 最新のヘルプ コンテンツをできるだけ早く、お客様がお使いの言語で提供したいと考えております。このページは、自動翻訳によって翻訳されているため、文章校正のエラーや不正確な情報が含まれている可能性があります。私たちの目的は、このコンテンツがお客様の役に立つようにすることです。お客様にとって役立つ情報であったかどうかを、このページの下部でお知らせください。簡単に参照できるように、こちらに英語の記事があります。

配列数式は、配列内の1つ以上の項目に対して複数の計算を実行できる数式です。配列は、値の行または列、または値の行と列の組み合わせと考えることができます。配列数式では、複数の結果、または1つの結果を返すことができます。

Office 365の9月2018更新プログラムを使用すると、複数の結果を返すことができるすべての数式は、自動的にダウンするか、隣接するセルに分割されます。この動作の変更には、いくつかの新しい動的配列関数も付属しています。動的配列数式は、既存の関数または動的配列関数を使用しているかどうかにかかわらず、1つのセルに入力するだけで、 enterキーを押して確認する必要があります。従来の配列数式では、最初に出力範囲全体を選択してから、 Ctrl + Shift + enterキーを使用して数式を確認する必要があります。一般的には、 CSEの数式と呼ばれています。

配列数式を使用して、次のように複雑なタスクを実行できます。

  • サンプルデータセットをすばやく作成します。

  • セル範囲に含まれる文字数を数えます。

  • 特定の条件を満たす数値 (範囲内の最小値など)、または上下の境界の間にある数値のみを合計します。

  • 値の範囲内のすべての n 番目の値を合計します。

次の例では、複数のセルと単一セルの配列数式を作成する方法について説明します。可能であれば、動的配列関数の一部についての例と、dynamic とレガシーの両方の配列として入力された既存の配列数式が含まれています。

サンプルをダウンロードする

この記事のすべての配列数式の例を使用して、サンプルブックをダウンロードします。

この演習では、複数セルの配列数式および単一セルの配列数式を使用して、売上金額を計算します。まず、複数セルの数式を使用して、小計を求めます。次に、単一セルの数式を使用して、総計を求めます。

  • 複数セルの配列数式

    セル H10 = F10: F19 * G10: G19 での複数セルの配列関数単価によって販売された車の数を計算するには

  • ここでは、 F19: F19 * G10: G19と入力して、各販売員のクーペとセダンの総売上を計算しています。

    enterキーを押すと、結果がセル H10: H19 に流出します。流出範囲内の任意のセルを選択すると、境界線で流出範囲が強調表示されていることに注意してください。また、セル H10: H19 の数式が淡色表示されていることにも気付くことがあります。それらは参照用としてちょうどそこにあるので、数式を調整する場合は、マスター数式の対象となるセル H10 を選択する必要があります。

  • 単一セルの配列数式

    単一セルの配列数式 = SUM (F10: F19 * G10: G19) を使用して総計を計算する

    サンプルブックのセル H20 で、「 = SUM (F10: F19 * G10: G19)」と入力するか、コピーして貼り付け、enter キーを押します。

    この場合、Excel では、配列内の値 (セル範囲 F10 から G19) を乗算し、SUM 関数を使用して合計を加算します。その結果、売上の総計は $159万になります。

    この例では、この種類の数式を強力にする方法を示します。たとえば、1000行のデータがあるとします。1000行を使用して数式をドラッグする代わりに、1つのセルに配列数式を作成することで、そのデータの一部またはすべてを合計することができます。また、セル H20 の単一セルの数式は、複数のセルの数式 (セル H10 から H19 の数式) に完全に依存していないことに注意してください。これは、配列数式 (柔軟性) を使用する別の利点です。列 H の他の数式は、H20 の数式に影響を与えずに変更できます。また、結果の精度を検証するのに役立ちます。このように、独立した集計を行うことをお勧めします。

  • 動的配列数式には、次のような利点もあります。

    • 整合性   下のセルをクリックすると、同じ数式が表示されます。その一貫性は、より正確さを保証するのに役立ちます。

    • 安全   複数セルの配列数式の構成要素を上書きすることはできません。たとえば、[セル H11] をクリックし、del キーを押します。Excel では、配列の出力は変更されません。変更するには、配列内の左上のセル、またはセル H10 を選択する必要があります。

    • ファイルサイズを小さく   する多くの場合、複数の中間式ではなく1つの配列数式を使うことができます。たとえば、car sales の例では、1つの配列数式を使用して、列 E の結果を計算します。= F10 * G10、F11 * G11、F12 * G12 などの標準の数式を使用していた場合は、同じ結果を計算するために11個の異なる数式を使用しています。これは大したことではありませんが、合計数千行の場合はどうすればよいですか?次に、大きな違いが生じる可能性があります。

    • 効率性   配列関数を使うと、複雑な数式を効率的に作成することができます。配列数式 = SUM (F10: F19 * g10: G19) は、これと同じです: = SUM (F10 * g10、F11 * G11、F12 * G12、F13 * G13、F14 * G14、F15 * G15、F16 * G16、F17 * G17、F18 * G18、F19 * G19)。

    • こぼれる   動的配列数式は、自動的に出力範囲に流出します。ソースデータが Excel テーブルに含まれている場合、データを追加または削除すると、動的配列数式のサイズが自動的に変更されます。

    • #SPILL! エラー   動的配列は#SPILL! エラーを導入しました。これは、意図した流出範囲が何らかの理由でブロックされていることを示します。閉塞を解決すると、数式が自動的に流出します。

配列定数は、配列数式の構成要素です。配列定数を入力するには、項目のリストを入力し、次のように、手動でリストを中かっこ ({ }) で囲みます。

= {1, 2, 3, 4, 5}または= {"January", "2 月", "March"}

カンマを使用して項目を区切る場合は、水平方向の配列 (行) を作成します。セミコロンを使用して項目を区切る場合は、縦方向の配列 (列) を作成します。2次元配列を作成するには、各行の項目をコンマで区切り、各行をセミコロンで区切って指定します。

次の手順では、水平方向、垂直方向、2次元定数を作成する方法について説明します。配列定数を自動的に生成するシーケンス関数と、手動で入力した配列定数を使用して、例を示します。

  • 水平定数を作成する

    前の例のブックを使用するか、新しいブックを作成します。任意の空のセルを選択し、「 = SEQUENCE (1, 5)」と入力します。SEQUENCE 関数は、 = {1, 2, 3, 4, 5}と同じ5列の配列を1行ずつ作成します。次の結果が表示されます。

    = SEQUENCE (1, 5) または = {1, 2, 3, 4, 5} の水平配列定数を作成する

  • 垂直定数を作成する

    その下に余裕がある空白セルを選択し、 = SEQUENCE (5)、または= {1; 2; 3; 4; 5}と入力します。次の結果が表示されます。

    = SEQUENCE (5)、または = {1; 2; 3; 4; 5} の垂直配列定数を作成する

  • 2 次元定数を作成する

    右側とその下にある空白セルを選択し、「 = SEQUENCE (3, 4)」と入力します。次の結果が表示されます。

    = SEQUENCE (3, 4) を使用して、3行を4列の配列定数で作成します。

    または、= {1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12} と入力することもできますが、セミコロンとコンマをどこに置くかに注意する必要があります。

    おわかりのように、シーケンスオプションを使用すると、配列定数値を手動で入力することにより、大きな利点が得られます。主に、時間が節約されますが、手動入力によるエラーの削減にも役立ちます。また、特にセミコロンはコンマ区切り文字と区別するのが難しいため、読みやすくなります。

ここでは、配列定数をより大きな数式の一部として使用する例を示します。サンプルブックで、数式ワークシートの定数に移動するか、新しいワークシートを作成します。

セルの中には、 = SEQUENCE (1, 5, 3, 1)と入力しましたが、セル A9: H9 では、3、4、5、6、7と入力することもできます。その特定の番号の選択については特別なことはありません。1-5 以外のものを選択しただけです。

cell から e11 では、 = sum (H9 * SEQUENCE (1, 5))、または= sum (H9 * {1, 2, 3, 4, 5})を入力します。数式は85を返します。

数式で配列定数を使用します。この例では、= SUM (: H (* シーケンス (1, 5)) を使用しました。

SEQUENCE 関数は、配列定数 {1, 2, 3, 4, 5} に相当するものを構築します。Excel では、最初にかっこで囲まれた式に対する操作が実行されるため、次の2つの要素は、H9、および乗算演算子 (*) のセル値です。この時点で、格納されている配列の値は定数内の対応する値で乗算されます。これは、次のようになります。

= sum (* 1、E9 * 2、F9 * 3、G9 * 4、H9 * 5)、または= sum (3 * 1, 4 * 2, 5 * 3, 6 * 4, 7 * 5)

最後に、SUM 関数によって値が加算され、85が返されます。

格納された配列を使用して、操作を完全にメモリに保持しないようにするには、別の配列定数で置き換えることができます。

= sum (シーケンス (1, 5, 3, 1) * シーケンス (1, 5))、または= sum ({3, 4, 5, 6, 7} * {1, 2, 3, 4, 5})

配列定数で使用できる要素

  • 配列定数には、数値、文字列、論理値 (TRUE や FALSE など)、#N/a などのエラー値を含めることができます。数値は、整数、10進数、および指数形式で使用できます。テキストを含める場合は、引用符 ("text") で囲む必要があります。

  • 配列定数には、別の配列、数式、または関数を含めることができません。つまり、カンマまたはセミコロンで区切られた文字列または数値のみを含めることができます。{1,2,A1:D4}、{1,2,SUM(Q2:Z8)} などの数式を入力すると、警告メッセージが表示されます。また、数値には、パーセント記号、ドル記号、カンマ、かっこを含めることができません。

配列定数を使用するための最適な方法の1つに、名前を付けることがあります。名前付き定数は、より簡単に使うことができ、配列数式の複雑さの一部を他のユーザーから隠すことができます。配列定数に名前を付け、それを数式で使用するには、次の操作を行います。

[数式>定義された名前 > Name を定義する] に移動します。[名前] ボックスに「1」と入力します。[参照先] ボックスに、次の定数を入力します (中かっこを手動で入力することを忘れないでください)。

={"1 月","2 月","3 月"}

このダイアログボックスは次のようになります。

数式から名前付き配列定数を追加する > 定義された名前 > Name Manager > New

[ OK] をクリックし、3つの空白セルがある行を選択して、 = 1と入力します。

次の結果が表示されます。

= {"January", "二月", "March"} と定義されている1のように、数式内で名前付き配列定数を使用します。 = 1 のようにします。

結果を水平方向ではなく垂直方向に流出させたい場合は、 =転置(1)を使用できます。

財務諸表を作成するときに使用するように、12か月のリストを表示する場合は、SEQUENCE 関数を使って、現在の年の1つを基準にすることができます。この関数についてのきちんとしたことは、月のみが表示されているにもかかわらず、その背後に有効な日付があり、他の計算で使用できます。この例については、「ブックの例」の「名前付き配列定数クイックサンプルのデータセット」のワークシートを参照してください。

= TEXT (DATE (YEAR (TODAY ()), SEQUENCE (1, 12), 1), "mmm")

12か月の動的なリストを作成するために、テキスト、日付、YEAR、TODAY、シーケンス関数を組み合わせて使用する

これにより、 date 関数を使用して、現在の年に基づいて日付を作成します。シーケンスでは、1 ~ 12 月の配列定数が作成されます。その後、テキスト関数は、表示形式を "mmm" (Jan、feb、Mar など) に変換します。月などの完全な月の名前を表示したい場合は、"うーん" を使用します。

名前付き定数を配列数式として使用する場合は、1だけでなく、= 1 のように等号を入力してください。そうしないと、Excel は配列を文字列として解釈し、数式が期待どおりに動作しません。最後に、関数、テキスト、および数値の組み合わせを使うことができることに注意してください。これは、どのようにクリエイティブを取得したいかによって異なります。

次の例では、配列数式で使用する配列定数を配置できるいくつかの方法を示します。一部の例では、転置関数を使用して行を列に変換し、その逆にします。

  • 配列内の各項目を複数指定する

    入力= シーケンス (1, 12) * 2、または= {1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12} * 2

    (/) で除算したり、(+) で加算したり、(-) を使って減算したりすることもできます。

  • 配列の各項目を 2 乗する

    Enter = SEQUENCE (1, 12) ^ 2、または= {1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12} ^ 2

  • 配列内の2乗項目の平方根を求める

    = SQRT (シーケンス (1, 12) ^ 2)、または= SQRT ({1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12} ^ 2)を入力します。

  • 1 次元の行を転置する

    Enter = 転置 (シーケンス (1, 5))、または= 転置 ({1, 2, 3, 4, 5})

    入力したのは水平配列定数ですが、TRANSPOSE 関数によって、配列定数が列に変換されます。

  • 1 次元の列を転置する

    Enter = 転置 (シーケンス (5, 1))、または= 転置 ({1; 2; 3; 4; 5})

    入力したのは垂直配列定数ですが、TRANSPOSE 関数によって、配列定数が行に変換されます。

  • 2 次元定数を転置する

    Enter = 転置 (シーケンス (3, 4))、または= 転置 ({1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12})

    TRANSPOSE 関数によって、行が列に変換されます。

このセクションでは、基本的な配列数式の例を示します。

  • 既存の値から配列を作成する

    次の例では、配列数式を使用して既存の配列から新しい配列を作成する方法について説明します。

    Enter = SEQUENCE (3、6、10、10)、または= {10, 20, 30, 40, 50, 60; 70, 80, 90100110120; 130140150160170180}

    数値の配列を作成しているため、180を入力した後に「10」と入力する前に {(左中かっこ) を入力してください。

    次に、空白のセルに「 = I11 #」または「 = "= " を入力します。3 x 6 のセルの配列には、次のような値が表示されます。# 記号は、こぼれた範囲演算子と呼ばれ、それを入力するのではなく、配列範囲全体を参照する Excel's 方法です。

    指定した範囲の演算子 (#) を使用して既存の配列を参照する

  • 既存の値から配列定数を作成する

    流出した配列数式の結果を受け取り、それをその構成要素に変換することができます。セルを選択し、 F2キーを押して編集モードに切り替えます。次に、 F9キーを押してセル参照を値に変換します。これにより、Excel は配列定数に変換されます。enterキーを押すと、数式、= のようになります。これで、= {10、20、30、40、50、60、70、80、90} になります。

  • セル範囲の文字数を数える

    次の例は、セル範囲内の文字数をカウントする方法を示しています。これにはスペースが含まれます。

    範囲内の文字の合計数と、文字列を操作するためのその他の配列の個数を数える

    = SUM (LEN (C9:))

    この場合、 LEN 関数は、セル範囲内の各文字列の長さを返します。SUM 関数は、これらの値をまとめて追加し、結果を表示します (66)。平均文字数を取得したい場合は、次のように使用できます。

    = 平均 (LEN (C9:))

  • 範囲 C9 の最長セルの内容

    = INDEX (c9:13、MATCH (最大 (len (c9:12))、len (c9: 5)、0)、1)

    この数式は、データ範囲にセルの1つの列が含まれている場合にのみ機能します。

    ここでは、内部要素から外側に向かって、数式を詳しく見ていきましょう。LEN 関数は、セル範囲 D2: D6 の各項目の長さを返します。MAX 関数は、それらの項目間の最大値を計算します。これは、セル D3 にある最長のテキスト文字列に対応しています。

    ここでは、少し複雑なことを説明します。MATCH 関数は、最長のテキスト文字列を含むセルのオフセット (相対位置) を計算します。そのためには、ルックアップ値、ルックアップ配列、一致する型の3つの引数が必要です。MATCH 関数は、指定された参照値のルックアップ配列を検索します。この場合、参照値は最長のテキスト文字列になります。

    MAX (LEN (C9:)

    この文字列は、次の配列に含まれます。

    LEN (C9:)

    この場合の match type 引数は0です。一致の種類は、1、0、または-1 の値にすることができます。

    • 1-lookup val より小さいか等しい最大値を返します。

    • 0-ルックアップ値と完全に等しい最初の値を返します。

    • -1-指定された参照値以上の最小値を返します。

    • 一致の種類を省略すると、Excel は1と見なされます。

    最後に、 INDEX 関数は、配列、およびその配列内の行と列の番号を引数として受け取ります。セル範囲 C9:13 は、配列を提供し、MATCH 関数はセルのアドレスを提供し、最後の引数 (1) は、配列の最初の列から値が来ることを指定します。

    最小のテキスト文字列の内容を取得したい場合は、上の例の MAX をMINで置き換えます。

  • 範囲内で値の小さい方から n 番目までを検索する

    この例では、セルの範囲内で3つの最小値を検索する方法を示しています。セル B9: B18has のサンプルデータの配列は、: = INT (RANDARRAY(10, 1) * 100)で作成されています。RANDARRAY は揮発性関数であるため、Excel によって計算されるたびに新しいランダムな数値のセットが得られることに注意してください。

    n 番目に小さい値を検索するには、Excel の配列数式: = SMALL (B9 #、シーケンス ())

    入力= 小さい (b9 #, シーケンス (), = small (b9: B18, {1; 2; 3})

    この数式では、配列定数を使用して小さな関数を3回評価し、セル B9: B18 に含まれる配列内の最小の3つのメンバーを返します。その他の値を見つけるには、SEQUENCE 関数の値を増やしたり、定数に引数を追加したりできます。SUMAVERAGEなど、この数式で追加の関数を使用することもできます。例えば:

    = SUM (SMALL (B9 #、SEQUENCE ())

    = 平均 (小さい (B9 #、シーケンス ())

  • 範囲内で値の大きい方から n 番目までを検索する

    範囲内の最大値を検索するには、SMALL 関数を大関数に置き換えることができます。さらに、次の例では、関数と間接機能を使用しています。

    Enter = ラージ (b9 #、row ("1: 3"))、または= ラージ (b9: B18、row ("1: 3" )))

    この時点では、行と間接関数について少し知っていると役に立つことがあります。ROW 関数を使用して、連続する整数の配列を作成することができます。たとえば、空を選び、次のように入力します。

    = ROW (1:10)

    数式は、10個の連続する整数の列を作成します。潜在的な問題を確認するには、配列数式を含む範囲の上に行を挿入します (つまり、行1の上にあります)。Excel では行参照が調整され、数式は2から11までの整数を生成するようになりました。この問題を解決するには、次の式に間接関数を追加します。

    = ROW (間接 ("1:10"))

    間接関数は、テキスト文字列を引数として使用します (これは、範囲1:10 が引用符で囲まれているためです)。Excel では、行を挿入したり、配列数式を移動したりしても、テキスト値は調整されません。結果として、ROW 関数は常に必要な整数の配列を生成します。シーケンスを簡単に使うことができます。

    = SEQUENCE (10)

    前に使用した数式を調べてみましょう: = ラージ (B9 #, ROW ("1: 3")))-内側のかっこから開始して外側に向かっています。間接関数は、文字列値のセットを返します。この場合、値は 1 ~ 3 です。ROW 関数を指定すると、3セルの列配列が生成されます。ラージ関数は、セル範囲 B9: B18 の値を使用し、ROW 関数によって返される参照ごとに1回、3回評価されます。その他の値を検索する場合は、より大きなセル範囲を間接関数に追加します。最後に、小さな例と同様に、この数式を SUM や AVERAGE などの他の関数と共に使うことができます。

  • エラー値を含む範囲の合計を求める

    #VALUE などのエラー値を含む範囲を合計しようとしても、Excel の sum 関数は機能しません。または #N/aこの例では、エラーを含むデータという名前の範囲内の値を合計する方法を示します。

    配列を使ってエラーを処理します。たとえば、= sum (IF (ISERROR (data), "", data) は、エラーが含まれている場合でも、#VALUE! または #NA! のように、データという名前の範囲を合計します。

  • =SUM(IF(ISERROR(データ),"",データ))

    この数式では、元の値からエラー値を引いた値を格納した新しい配列が作成されます。内側の関数から順に説明すると、ISERROR 関数は、セル範囲 ("データ") でエラーがないか検索します。IF 関数は、指定された条件を評価した結果が TRUE の場合は特定の値を返し、評価した結果が FALSE の場合は別の値を返します。この例の場合、すべてのエラー値については TRUE に評価されるため、空の文字列 ("") が返され、範囲 (データ) の残りの値については FALSE に評価される (つまり、エラー値を格納していない) ため、値自体が返されます。次に、SUM 関数が、フィルター処理された配列の合計を計算します。

  • 範囲内のエラー値の個数を数える

    この例は前の数式と似ていますが、フィルターを適用するのではなく、データという名前の範囲内のエラー値の数を返します。

    =SUM(IF(ISERROR(データ),1,0))

    この数式では、エラーを含むセルの場合は値 1 を、エラーを含まないセルの場合は値 0 を格納している配列を作成します。次のように、IF 関数の 3 つ目の引数を省いて数式を簡略化しても、同じ結果が得られます。

    =SUM(IF(ISERROR(データ),1))

    この引数を指定しなかった場合、セルにエラー値が含まれていないと、IF 関数は FALSE を返します。この数式をさらに簡略化して、次のようにすることもできます。

    =SUM(IF(ISERROR(データ)*1))

    この形式が機能するのは、TRUE*1=1 で、FALSE*1=0 であるためです。

条件に基づいて値を合計する必要がある場合があります。

配列を使用して、特定の条件に基づいて計算することができます。= sum (IF (Sales>0, sales)) は、sales と呼ばれる範囲内の0より大きいすべての値を合計します。

たとえば、この配列数式は Sales という名前の範囲内の正の整数だけを合計します。上の例では、セル E9: E24 を表します。

=SUM(IF(売上>0,売上))

IF 関数は、正と false の値の配列を作成します。SUM 関数は、0 + 0 = 0 であるため、基本的に false 値を無視します。この数式で使用するセル範囲は、任意の数の行と列で構成できます。

複数の条件を満たす値を合計することもできます。たとえば、次の配列数式は、0より大きい値と2500よりも小さい数値を計算ます。

= SUM ((Sales>0) * (Sales<2500) * (売上))

範囲に数値以外のセルが含まれている場合、この数式はエラーを返します。

または、type または condition を使用する配列数式を作成することもできます。たとえば、0より大きい値を合計するか、または2500未満を加算することができます。

= SUM (IF ((Sales>0) + (Sales<2500), Sales))

配列数式で and や OR 関数を直接使うことはできません。これらの関数は、TRUE または FALSE のいずれかの結果を返すため、配列関数は結果の配列を必要とします。この問題を回避するには、前の数式に示されているロジックを使用します。つまり、or 条件を満たす値に加算や乗算などの算術演算を実行します。

範囲内の値から 0 を除いて平均を求める方法の例を次に示します。この数式では、"売上" という名前のデータ範囲を使用しています。

=AVERAGE(IF(売上<>0,売上))

IF 関数が、0 と等しくない値の配列を作成し、これらの値を AVERAGE 関数に渡します。

この配列数式は、MyData と YourData という2つのセル範囲内の値を比較し、両者の差の数を返します。2つの範囲の内容が同じである場合、数式は0を返します。この数式を使用するには、セル範囲を同じサイズと同じディメンションにする必要があります。たとえば、MyData が5列で3行の範囲である場合、YourData は5列ずつ3行である必要があります。

=SUM(IF(データ1=データ2,0,1))

この数式は、比較対象範囲と同じサイズの新しい配列を作成します。IF 関数が、配列に値 0 と値 1 を設定します (不一致の場合は 0 で、同一セルの場合は 1)。次に、SUM 関数が、配列内の値の合計を返します。

この数式は、次のように簡略化できます。

=SUM(1*(データ1<>データ2))

範囲内のエラー値の個数を数える数式と同様、この数式が機能するのは、TRUE*1=1 で、FALSE*1=0 であるためです。

この配列数式は、"データ" という名前の単一列の範囲に含まれる最大値の行番号を返します。

=MIN(IF(データ=MAX(データ),ROW(データ),""))

IF 関数が、"データ" という範囲に対応する新しい配列を作成します。対応するセルに範囲内の最大値が含まれている場合、配列に行番号が格納されます。それ以外の場合は、配列に空の文字列 ("") が格納されます。MIN 関数は、この新しい配列を 2 番目の引数として使用して、最小値 ("データ" の最大値の行番号に対応) を返します。"データ" という範囲に同じ最大値が複数含まれている場合は、最初の値の行が返されます。

最大値の実際のセル住所を返すには、次の数式を使用します。

=ADDRESS(MIN(IF(データ=MAX(データ),ROW(データ),"")),COLUMN(データ))

[データセット] ワークシートの違いについては、サンプルブックにも同様の例があります。

この演習では、複数セルの配列数式および単一セルの配列数式を使用して、売上金額を計算します。まず、複数セルの数式を使用して、小計を求めます。次に、単一セルの数式を使用して、総計を求めます。

  • 複数セルの配列数式

下の表全体をコピーし、空白のワークシートのセル A1 に貼り付けます。

売上高

Type

番号 完売

Unit 価格

合計 売上高

川井

セダン

5

33000

クーペ

4

37000

阿藤

セダン

6

24000

クーペ

8

21000

阿部

セダン

3

29000

クーペ

1

31000

山水

セダン

9

24000

クーペ

5

37000

安藤

セダン

6

33000

クーペ

8

31000

数式 (総計)

総計

'=SUM(C2:C11*D2:D11)

=SUM(C2:C11*D2:D11)

  1. 各販売員のクーペとセダンの売上合計を確認するには、[セル E2: から e11] を選択し、数式= C2: C11 * D2:を入力してから、 ctrl + Shift + enterキーを押します。

  2. すべての売上の総計を確認するには、[セル F11] を選択して、数式= SUM (C2: C11 * D2:)を入力し、ctrl キーとShift + enterを押します。

ctrl + Shift + enterキーを押すと、Excel では、数式が中かっこ ({}) で囲まれ、選択した範囲の各セルに数式のインスタンスが挿入されます。これは非常にすばやく行われるため、列 E に表示される内容は、各販売員の各車種の売上合計額です。[E2] を選択し、[E3]、[E4] の順に選択すると、同じ数式が表示されます。 {= C2: C11 * D2:のようになります。

E 列の合計を配列数式により計算

  • 単一セルの配列数式を作成する

ブックのセル D13 で、次の数式を入力し、ctrl キーとShift + enter を押します。

=SUM(C2:C11*D2:D11)

この場合、Excel では、配列内の値 (セル範囲 C2 ~ [-]) を乗算し、 SUM 関数を使用して合計を加算します。その結果、売上の総計は $159万になります。この例では、この種類の数式を強力にする方法を示します。たとえば、1000行のデータがあるとします。1000行を使用して数式をドラッグする代わりに、1つのセルに配列数式を作成することで、そのデータの一部またはすべてを合計することができます。

また、セル D13 の1つのセルの数式は、複数のセルの数式 (セル E2 からから e11 の数式) に完全に依存していないことに注意してください。これは、配列数式 (柔軟性) を使用する別の利点です。D13 の数式に影響を与えることなく、列 E の数式を変更したり、その列を完全に削除したりすることができます。

配列数式には、次のような利点もあります。

  • 一貫性:    セル E2 以降、この列のどのセルをクリックしても、同じ数式が表示されます。この一貫性が、正確さの向上に役立ちます。

  • 安全   複数セルの配列数式の構成要素を上書きすることはできません。たとえば、セル E3 をクリックし、 delキーを押します。セルの範囲全体 (E2 ~ から e11) を選択し、配列全体の数式を変更するか、配列をそのままにしておく必要があります。追加の安全対策として、 ctrl + Shift + enterキーを押して、数式の変更を確認する必要があります。

  • ファイル サイズの小型化:    多くの場合、複数の中間式の代わりに単一の配列数式を使用できます。たとえば、このブックでは、1 つの配列数式を使用して E 列の結果を計算しています。標準の数式 (=C2*D2、C3*D3、C4*D4 など) を使用して同じ結果を得るには、11 個の異なる数式を使用する必要があります。

一般的に、配列数式では標準の数式構文を使用します。すべての値は、等号 (=) で始まり、組み込みの Excel 関数のほとんどを配列数式で使用できます。主な違いは、配列数式を使用するときに、 ctrl + Shift + enterキーを押して数式を入力することです。この操作を行うと、Excel では、配列数式が中かっこで囲まれています。かっこを手動で入力すると、数式はテキスト文字列に変換され、動作しません。

配列関数を使うと、複雑な数式を効率的に作成することができます。配列数式= SUM (C2: C11 * d2:)は、これと同じです: = SUM (C2 * d2、C3 * D3、C4 * D4、C5 * D5、C6 * D6、C7 * D7、C8 * D8、C9 * の d5、C10 * D10、c11 *)

重要: 配列数式を入力する必要がある場合は、 ctrl + Shift + enterキーを押します。これは、単一セルと複数セルの両方の数式に適用されます。

複数セルの数式を操作するときは、次の点にも注意する必要があります。

  • 数式を入力するに、結果の格納先のセル範囲を選択します。これは、複数セルの配列数式を作成する際に、セル E2 ~ E11 を選択したときに行いました。

  • 配列数式の個々のセルの内容を変更することはできません。これを実際に試すには、ブックのセル E3 を選択し、Del キーを押します。配列の一部を変更することができないことを知らせるメッセージが表示されます。

  • 配列数式全体を移動または削除できますが、配列数式の一部を移動または削除することはできません。つまり、配列数式を縮小するには、既存の数式を削除してから、数式を入力し直す必要があります。

  • 配列数式を削除するには、数式の範囲全体を選択します (たとえば、 E2: から e11)、 delキーを押します。

  • 空白のセルを挿入したり、複数セルの配列数式からセルを削除したりすることはできません。

時には、配列数式を展開する必要がある場合があります。既存の配列範囲内の最初のセルを選択し、数式を拡張する範囲全体を選択するまで続けます。F2キーを押して数式を編集し、数式の範囲を調整したら、 ctrl + SHIFT + enterキーを押して数式を確定します。キーは、配列内の左上のセルを起点として、範囲全体を選択することです。左上のセルは、編集を行うものです。

配列数式は優れていますが、次のような短所があります。

  • ctrl + Shift + enterキーを押すことを忘れてしまうことがあります。これは、最も経験豊富な Excel ユーザーにも発生する可能性があります。配列数式を入力または編集するときは、必ずこのキーの組み合わせを押してください。

  • ブックの他のユーザーは、数式を理解していない可能性があります。実際には、配列数式は通常、ワークシートでは説明されません。したがって、他のユーザーがブックを変更する必要がある場合は、配列数式を使わないようにするか、配列数式を知っていることを確認し、必要に応じて変更する方法を理解しておく必要があります。

  • コンピューターの処理速度とメモリによっては、大きな配列数式を使用すると、計算速度が低下することがあります。

配列定数は、配列数式の構成要素です。配列定数を入力するには、項目のリストを入力し、次のように、手動でリストを中かっこ ({ }) で囲みます。

={1,2,3,4,5}

これで、配列数式を作成するときに、 ctrl + Shift + enterキーを押す必要があることがわかります。配列定数は配列数式の構成要素であるため、定数は、手動で入力してかっこで囲みます。次に、 Ctrl + Shift + enterキーを使用して、数式全体を入力します。

カンマを使用して項目を区切った場合は、水平配列 (行) が作成されます。セミコロンを使用して項目を区切った場合は、垂直配列 (列) が作成されます。2 次元配列を作成するには、カンマを使用して各行の項目を区切り、さらに、セミコロンを使用して各行を区切ります。

次に、1つの行の配列を示します。次に示すのは、1つの列 ({1; 2; 3; 4}) の配列です。次に、{1, 2, 3, 4; 5, 6, 7, 8} という2つの行と4つの列の配列を示します。2つの行配列では、最初の行が1、2、3、4で、2番目の行は5、6、7、8です。1つのセミコロンは、4 ~ 5 の間の2つの行を区切ります。

配列数式と同様、Excel に用意されているほとんどの組み込み関数で配列定数を使用できます。後のセクションでは、各種の定数を作成する方法、およびこれらの定数を Excel の関数と組み合わせて使用する方法について説明します。

水平定数、垂直定数、および 2 次元定数を作成する手順を次に示します。

水平定数を作成する

  1. 空白のワークシートで、セル A1 から E1 までを選択します。

  2. 数式バーに次の数式を入力し、 ctrl + Shift + enterキーを押します。

    = {1, 2, 3, 4, 5}

    この場合、左中かっこ ({}) を入力し、2番目のセットが Excel によって追加されます。

    次の結果が表示されます。

    数式の水平配列定数

垂直定数を作成する

  1. ブックで、5 行 1 列のセルを選択します。

  2. 数式バーに次の数式を入力し、 ctrl + Shift + enterキーを押します。

    ={1;2;3;4;5}

    次の結果が表示されます。

    配列数式の垂直配列定数

2 次元定数を作成する

  1. ブックで、4 列 3 行のセルのブロックを選択します。

  2. 数式バーに次の数式を入力し、 ctrl + Shift + enterキーを押します。

    ={1,2,3,4;5,6,7,8;9,10,11,12}

    次の結果が表示されます。

    配列数式の 2 次元配列定数

数式で定数を使用する

定数を使用する簡単な例を見てみましょう。

  1. サンプル ブックで、新しいワークシートを作成します。

  2. セル A1 に 3 と入力し、B1 に 4、C1 に 5、D1 に 6、E1 に 7 と入力します。

  3. セル A3 に次の数式を入力し、 ctrl + Shift + enterキーを押します。

    =SUM(A1:E1*{1,2,3,4,5})

    配列数式として入力したため、Excel によって定数全体が別の中かっこで囲まれます。

    配列定数を使用した配列数式

    セル A3 に値 85 が表示されます。

次のセクションでは、この数式がどのように機能するかを説明します。

上で使用した数式は、いくつかの部分で構成されます。

配列定数を使用した配列数式の構文

1. 関数

2. 格納された配列

3. 演算子

4. 配列定数

かっこ内の最後の要素は配列定数です: {1, 2, 3, 4, 5}。Excel では、配列定数が中かっこで囲まれていないことに注意してください。実際に入力します。配列数式に定数を追加した後で、 ctrl + Shift + enterキーを押して数式を入力することも忘れないでください。

Excel では、かっこで囲まれた式の演算が最初に実行されるため、次に関与する要素は、ブックに格納した値 (A1:E1) と演算子の 2 つです。この時点で、格納された値に対応する定数の値を掛ける数式が実行されます。これは、次の式に相当します。

=SUM(A1*1,B1*2,C1*3,D1*4,E1*5)

最後に、SUM 関数によって値が加算され、セル A3 に合計 85 が表示されます。

格納された配列を使用せずに、演算全体をメモリ内で実行するには、格納された配列を次の配列定数で置き換えます。

=SUM({3,4,5,6,7}*{1,2,3,4,5})

この操作を行うには、関数をコピーして、ブック内の空白セルを選択し、数式バーに数式を貼り付けてから、ctrl キーを押しながら「 shift ++」と入力します。配列数式を使用した前の演習と同じ結果が表示されます。

=SUM(A1:E1*{1,2,3,4,5}) を使用した場合と同じ結果が表示されます。

配列定数には、数値、文字列、論理値 (TRUE、FALSE など)、およびエラー値 (#N/A など) を格納できます。数値には、整数、小数、および指数表現を使用できます。文字列を使用する場合は、文字列を引用符 (") で囲む必要があります。

配列定数には、別の配列、数式、または関数を含めることができません。つまり、カンマまたはセミコロンで区切られた文字列または数値のみを含めることができます。{1,2,A1:D4}、{1,2,SUM(Q2:Z8)} などの数式を入力すると、警告メッセージが表示されます。また、数値には、パーセント記号、ドル記号、カンマ、かっこを含めることができません。

配列定数を使用する最善の方法の1つに、名前を付けることがあります。名前付き定数は、より簡単に使うことができ、配列数式の複雑さの一部を他のユーザーから隠すことができます。配列定数に名前を付け、それを数式で使用するには、次の操作を行います。

  1. [数式] タブの [定義された名前] グループで、[名前の定義] をクリックします。
    [名前の定義] ダイアログボックスが表示されます。

  2. [名前] ボックスに、「第 1 四半期」と入力します。

  3. [参照範囲] ボックスに次の定数を入力します (中かっこを手動で入力してください)。

    ={"1 月","2 月","3 月"}

    ダイアログ ボックスの定数は、次のようになります。

    [新しい名前] ダイアログ ボックスと数式

  4. [OK] をクリックし、3 個の空白セルがある行を選択します。

  5. 次の数式を入力し、 ctrl + Shift + enterキーを押します。

    =第 1 四半期

    次の結果が表示されます。

    数式として入力された名前付き配列

名前付き定数を配列数式として使用する場合は、等号を入力することを忘れないでください。等号を入力しなかった場合、配列は文字列として扱われ、数式は期待どおりに動作しません。また、文字列と数値の組み合わせを使用できることも覚えておいてください。

配列定数が機能しない場合は、次の問題がないか確認してください。

  • 一部の要素は、適切な文字で区切られない場合があります。コンマまたはセミコロンを省略した場合、または間違った場所に配置した場合、配列定数が正しく作成されないことがあります。または、警告メッセージを表示することがあります。

  • 選択したセルの範囲と、定数の要素の数が一致していない可能性があります。たとえば、6 行 1 列のセルを選択して 5 つのセル用の定数を使用しようとすると、空白セルに #N/A エラー値が表示されます。逆に、選択したセルの数が少なすぎる場合は、対応するセルのない値が省かれます。

次の例では、配列数式で使用する配列定数を配置できるいくつかの方法を示します。一部の例では、転置関数を使用して行を列に変換し、その逆にします。

配列の各項目を乗算する

  1. 新しいワークシートを作成し、4 列 3 行の空のセルのブロックを選択します。

  2. 次の数式を入力し、 ctrl + Shift + enterキーを押します。

    = {1、2、3、4、5、6、7、8、9、10、11、12} * 2

配列の各項目を 2 乗する

  1. 4 列 3 行の空のセルのブロックを選択します。

  2. 次の配列数式を入力し、 ctrl + Shift + enterキーを押します。

    ={1,2,3,4;5,6,7,8;9,10,11,12}*{1,2,3,4;5,6,7,8;9,10,11,12}

    または、キャレット演算子 (^) を使用する場合は、次の配列数式を入力します。

    ={1,2,3,4;5,6,7,8;9,10,11,12}^2

1 次元の行を転置する

  1. 1 列 5 行の空白セルを選択します。

  2. 次の数式を入力し、 ctrl + Shift + enterキーを押します。

    =TRANSPOSE({1,2,3,4,5})

    入力したのは水平配列定数ですが、TRANSPOSE 関数によって、配列定数が列に変換されます。

1 次元の列を転置する

  1. 1 行 5 列の空白セルを選択します。

  2. 次の数式を入力し、 ctrl + Shift + enterキーを押します。

    =TRANSPOSE({1;2;3;4;5})

入力したのは垂直配列定数ですが、TRANSPOSE 関数によって、配列定数が行に変換されます。

2 次元定数を転置する

  1. 3 列 4 行のセルのブロックを選択します。

  2. 次の定数を入力し、 ctrl + Shift + enterキーを押します。

    =TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12})

    TRANSPOSE 関数によって、行が列に変換されます。

このセクションでは、基本的な配列数式の例を示します。

既存の値から配列および配列定数を作成する

次の例では、配列数式を使用して、異なるワークシートのセル範囲間のリンクを作成する方法を示します。同じ値セットから配列定数を作成する方法も示します。

既存の値から配列を作成する

  1. Excel のワークシートで、セル C8:E10 を選び、次の数式を入力します。

    ={10,20,30;40,50,60;70,80,90}

    「10」を入力する前に「{」 (かっこ開き) を入力し、「90」を入力した後に「}」 (かっこ閉じ) を入力します。数値の配列を作成しているからです。

  2. ctrl + Shift + enterキーを押して、配列数式を使用して、セル範囲 C8: E10 内の数値の配列を入力します。ワークシートでは、C8 から E10 は次のようになります。

    10

    20

    30

    40

    50

    60

    70

    80

    90

  3. C1 ~ E3 のセル範囲を選択します。

  4. 数式バーに次の数式を入力し、 ctrl + Shift + enterキーを押します。

    =C8:E10

    セル C1 ~ E3 には、C8 から E10 に表示されている値と同じものが含まれています。

既存の値から配列定数を作成する

  1. セル C1: C3 が選択されている状態で、 F2キーを押して編集モードに切り替えます。

  2. セル参照を値に変換するには、 F9キーを押します。Excel では、値が配列定数に変換されます。これで、数式は= {10、20、30、40、50、60、70、80、90}になります。

  3. ctrl + Shift + enterキーを押して、配列定数を配列数式として入力します。

セル範囲の文字数を数える

セル範囲の文字数 (スペースを含む) を数える方法の例を次に示します。

  1. このテーブル全体をコピーし、ワークシートのセル A1 に貼り付けます。

    データ

    この文章は、

    それぞれのセルに別れて入力されていますが、

    すべてのセルに入力されている

    文章を合わせて、

    一つの文章にすることができます。

    A2:A6 の合計文字数

    =SUM(LEN(A2:A6))

    最も長いセル (A3) の内容

    =INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)

  2. セル A8 を選び、 ctrl + Shift + enterキーを押して、セル A2: A6 (66) の文字の合計数を確認します。

  3. セル A10 を選び、 ctrl + Shift + enterキーを押して、最も長いセル A2: A6 (セル A3) の内容を確認します。

セル A8 で次の数式を使用して、セル A2 ~ A6 の合計文字数 (66) をカウントします。

=SUM(LEN(A2:A6))

この場合、 LEN関数は、セル範囲内の各文字列の長さを返します。SUM関数は、これらの値をまとめて追加し、結果を表示します (66)。

範囲内のn 個の最小値を検索する

次の例では、セル範囲内で値の小さい方から 3 番目までを検索します。

  1. セル A1: A11 にランダムな数値を入力します。

  2. セル C1 から C3 までを選択します。この一連のセルは、配列数式によって返される結果を保持します。

  3. 次の数式を入力し、 ctrl + Shift + enterキーを押します。

    = SMALL (A1: A11, {1; 2; 3})

この数式では、配列定数を使用して小さな関数を3回評価し、セル A1: A10 に含まれている配列内の最小 (1)、第2の最小 (2)、および3番目に小さい (3) メンバーを返します。さらに値を検索するには、定数.SUMAVERAGEなど、この数式で追加の関数を使用することもできます。例えば:

= SUM (小 (A1: A10、{1, 2, 3})

= 平均 (小 (A1: A10, {1, 2, 3})

範囲内のn 個の最大値を検索する

範囲内に含まれる値のうち、大きい方の値を検索するには、SMALL 関数の代わりに LARGE 関数を使用します。次の例では、ROW 関数と INDIRECT 関数も使用しています。

  1. [セル D1 から D3] を選びます。

  2. 数式バーに次の数式を入力し、ctrl キーとShift + enter を押します。

    =LARGE(A1:A10,ROW(INDIRECT("1:3")))

この時点では、間接関数について少し知っていると役に立つことがあります。ROW関数を使用して、連続する整数の配列を作成することができます。たとえば、演習用ブック内の10個のセルの空の列を選択し、この配列数式を入力して、 ctrl + Shift + enterキーを押します。

=ROW(1:10)

これにより、10 個の連続する整数の列が作成されます。潜在的な問題を確認するために、配列数式が格納されている範囲の上 (つまり、行 1) に行を挿入します。Excel によって行参照が調整され、2 ~ 11 の整数が生成されます。この問題を修正するには、次のように、INDIRECT 関数を数式に追加します。

=ROW(INDIRECT("1:10"))

INDIRECT 関数は、引数として文字列を使用します (範囲 1:10 を二重引用符で囲むのはそのためです)。行の挿入、または配列数式の移動を行っても、Excel によって文字列値が調整されることはありません。この結果、ROW 関数は、常に目的の整数の配列を生成するようになります。

前に使用した数式を見てみましょう。 = ラージ (A5: A14、ROW ("1: 3" )))-内部かっこから開始して外側に向かっている:間接関数は、この場合は値1から3までの一連のテキスト値を返します。ROW関数を指定すると、3セルの円柱配列が生成されます。ラージ関数は、セル範囲 A5: A14 の値を使用し、 ROW関数によって返される参照ごとに1回、3回評価されます。3200、2700、および2000の値は、3セルの円柱配列に返されます。その他の値を検索する場合は、より大きなセル範囲を間接関数に追加します。

前の例と同様に、この数式をSUMAVERAGEなどの他の関数と共に使うことができます。

セル範囲内で最も長い文字列を検索する

前の文字列の例に戻るには、空のセルに次の数式を入力し、 ctrl + Shift + enterキーを押します。

=INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)

"セルの束" というテキストが表示されます。

ここでは、内部要素から外側に向かって、数式を詳しく見ていきましょう。LEN関数は、セル範囲 A2: A6 内の各項目の長さを返します。MAX関数は、それらの項目間の最大値を計算します。これは、セル A3 にある最長のテキスト文字列に対応しています。

ここからは、少し複雑になります。MATCH 関数は、最も長い文字列を格納しているセルのオフセット (相対位置) を計算します。この計算を行うには、検査値検査範囲、および照合の種類という 3 つの引数が必要です。MATCH 関数は、指定された検査値を検査範囲で検索します。この例の場合、検査値は、最も長い文字列です。

(MAX (LEN (A2: A6))

この文字列は、次の配列に含まれます。

LEN (A2: A6)

"照合の種類" 引数は 0 です。照合の種類は、1、0、または -1 の値で構成されます。1 を指定した場合、MATCH は、検査値以下で、最も大きな値を返します。0 を指定した場合、MATCH は、検査値と等しい最初の値を返します。-1 を指定した場合、MATCH は、指定した値以上で、最も小さい値を検索します。照合の種類を指定しなかった場合は、1 が指定されたと見なされます。

最後に、 INDEX関数は、配列、およびその配列内の行と列の番号を引数として受け取ります。セル範囲 A2: A6 は配列を提供し、 MATCH関数はセルのアドレスを提供し、最後の引数 (1) は配列の最初の列から値が来ることを指定します。

このセクションでは、高度な配列数式の例を示します。

エラー値を含む範囲の合計を求める

Excel の SUM 関数は、範囲内に #N/A などのエラー値が含まれている場合は機能しません。この例では、エラーを含む、"データ" という名前の範囲の値を集計する方法を示します。

=SUM(IF(ISERROR(データ),"",データ))

この数式では、元の値からエラー値を引いた値を格納した新しい配列が作成されます。内側の関数から順に説明すると、ISERROR 関数は、セル範囲 ("データ") でエラーがないか検索します。IF 関数は、指定された条件を評価した結果が TRUE の場合は特定の値を返し、評価した結果が FALSE の場合は別の値を返します。この例の場合、すべてのエラー値については TRUE に評価されるため、空の文字列 ("") が返され、範囲 (データ) の残りの値については FALSE に評価される (つまり、エラー値を格納していない) ため、値自体が返されます。次に、SUM 関数が、フィルター処理された配列の合計を計算します。

範囲内のエラー値の個数を数える

この例は前の数式と似ていますが、"データ" という名前の範囲のエラー値をフィルター処理する代わりに、エラー値の数を返します。

=SUM(IF(ISERROR(データ),1,0))

この数式では、エラーを含むセルの場合は値 1 を、エラーを含まないセルの場合は値 0 を格納している配列を作成します。次のように、IF 関数の 3 つ目の引数を省いて数式を簡略化しても、同じ結果が得られます。

=SUM(IF(ISERROR(データ),1))

この引数を指定しなかった場合、セルにエラー値が含まれていないと、IF 関数は FALSE を返します。この数式をさらに簡略化して、次のようにすることもできます。

=SUM(IF(ISERROR(データ)*1))

この形式が機能するのは、TRUE*1=1 で、FALSE*1=0 であるためです。

条件に基づいて値を合計する

条件に基づいて値を集計することが必要になる場合があります。たとえば、次の配列数式では、"売上" という名前の範囲に含まれる正の整数だけを合計します。

=SUM(IF(売上>0,売上))

IF 関数は、正の値と false 値の配列を作成します。0+0=0 であるため、SUM 関数は基本的に false 値を無視します。この数式で使用するセル範囲には、任意の数の行と列を含めることができます。

複数の条件を満たす値を合計することもできます。たとえば、次の配列数式では、0 より大きく、かつ、5 以下の値を計算します。

=SUM((売上>0)*(売上<=5)*(売上))

範囲に数値以外のセルが含まれている場合、この数式はエラーを返します。

OR 条件を使用する配列数式を作成することもできます。たとえば、5 未満の値と、15 より大きい値の合計を求めることができます。

=SUM(IF((売上<5)+(売上>15),売上))

IF 関数は、5 未満の値と、15 より大きい値をすべて検索し、これらの値を SUM 関数に渡します。

AND 関数と OR 関数は単一の結果 (TRUE または FALSE) を返しますが、配列数式では結果の配列が必要であるため、配列関数で AND 関数と OR 関数を直接使用することはできません。この問題に対処するには、前の数式で示したロジックを使用します。つまり、OR 条件または AND 条件を満たす値に対して、加算や乗算などの数学演算を実行します。

0 を除いた平均を計算する

範囲内の値から 0 を除いて平均を求める方法の例を次に示します。この数式では、"売上" という名前のデータ範囲を使用しています。

=AVERAGE(IF(売上<>0,売上))

IF 関数が、0 と等しくない値の配列を作成し、これらの値を AVERAGE 関数に渡します。

2 つのセル範囲間で相違する値の個数を数える

この配列数式では、"データ1" および "データ2" という名前の 2 つのセル範囲の値を比較し、この 2 つの範囲間で相違する値の数を返します。2 つの範囲の内容が一致する場合は、0 が返されます。この数式を使用するには、2 つのセル範囲が同じサイズで、同じ次元である必要があります (たとえば、"データ1" が 3 行 5 列の範囲であれば、"データ2" も 3 行 5 列である必要があります)。

=SUM(IF(データ1=データ2,0,1))

この数式は、比較対象範囲と同じサイズの新しい配列を作成します。IF 関数が、配列に値 0 と値 1 を設定します (不一致の場合は 0 で、同一セルの場合は 1)。次に、SUM 関数が、配列内の値の合計を返します。

この数式は、次のように簡略化できます。

=SUM(1*(データ1<>データ2))

範囲内のエラー値の個数を数える数式と同様、この数式が機能するのは、TRUE*1=1 で、FALSE*1=0 であるためです。

範囲内の最大値の場所を検索する

この配列数式は、"データ" という名前の単一列の範囲に含まれる最大値の行番号を返します。

=MIN(IF(データ=MAX(データ),ROW(データ),""))

IF 関数が、"データ" という範囲に対応する新しい配列を作成します。対応するセルに範囲内の最大値が含まれている場合、配列に行番号が格納されます。それ以外の場合は、配列に空の文字列 ("") が格納されます。MIN 関数は、この新しい配列を 2 番目の引数として使用して、最小値 ("データ" の最大値の行番号に対応) を返します。"データ" という範囲に同じ最大値が複数含まれている場合は、最初の値の行が返されます。

最大値の実際のセル住所を返すには、次の数式を使用します。

=ADDRESS(MIN(IF(データ=MAX(データ),ROW(データ),"")),COLUMN(データ))

確認

この記事の一部は、コリン・ウィルコックスによって書かれた一連の excel Power User 列に基づいていて、excel 2002 の数式、John Walkenbach によって書かれた本 (元の excel MVP) の章14と15から脚色されています。

補足説明

Excel Tech Community では、いつでも専門家に質問できます。Microsoft コミュニティでは、サポートを受けられます。また、Excel User Voice では、新機能についての提案や改善案を送信することができます。

関連項目

動的配列とスピル配列の動作

動的配列数式とレガシー CSE 配列数式

FILTER 関数

RANDARRAY 関数

SEQUENCE 関数

SINGLE 関数

SORT 関数

SORTBY 関数

UNIQUE 関数

Excel での #SPILL! エラー

数式の概要

Office のスキルを磨く
トレーニングの探索
新機能を最初に入手
Office Insider に参加する

この情報は役に立ちましたか?

ご意見をいただきありがとうございます。

フィードバックをお寄せいただき、ありがとうございます。Office サポートの担当者におつなぎいたします。

×