配列 数式 エクセル。 相関表の意味とエクセルCOUNTIFS関数・配列数式を使った相関表の作り方 | Note&Board

ビデオ: 配列数式

配列 数式 エクセル

配列数式は通常ではできない高度な計算ができます。 使用する際は十分に注意が必要です。 配列数式には「単一セルの配列数式」と「複数セルの配列数式」の 2 種類があります。 どちらもセルの範囲に対して計算できるようになります。 単一セルの配列数式:計算結果を表示するセルを用意することなく、関数に直接セルの範囲を計算した結果を使用できます。 配列数式を使用したいときは主にこちらの使い方だと思います。 複数セルの配列数式:1 つの配列数式で、セルの範囲を計算した結果がそれぞれ行ごとの計算した結果になります。 これらの動作を解説するために、「配列」と「セルの範囲の計算」を先に説明します。 それを理解すると配列数式が理解できるようになります。 配列について 配列は値の集まりのことです。 一般的に二次元配列と呼びます。 値 配列にセル参照や関数や日付は入力できません。 入力できる値は文字列、数値、TRUE、FALSE、エラー値などです。 表示 配列をセルに入力すると、結果は 1 つ目の要素になります。 配列同士の計算は、配列の種類と要素数を同じにする必要があります。 関数の引数に渡す 「」のように単一の値を引数で受け取る関数に配列を渡せます。 「」のように複数の値を引数で受け取る関数に配列を渡せます。 結果は 6になります。 「」など一部の関数は配列を受け取れません。 その時はエラーになります。 セルの範囲同士の計算は同じ行または同じ列同士で計算する必要があります。 表示 計算結果が配列のようになると上記で説明しましたが、表示については配列と違います。 セルの範囲同士の計算と同じで、結果を表示するセルも同じ行または同じ列である必要があります。 行や列がずれているとエラーになります。 この動作がとても重要なので「セルの範囲の制約」という名前を説明しやすくするために付けます。 関数の引数に渡す 「」のように単一の値を引数で受け取る関数に「セルの範囲」を渡せます。 これは「セルの範囲の制約」を受けます。 「」のように複数の値を引数で受け取る関数に「セルの範囲の計算」を渡せます。 これは「セルの範囲の制約」を受けます。 まとめ このようにセルの範囲を計算するとその結果の表示には「セルの範囲の制約」を受けることになります。 配列数式を使用すると「セルの範囲の制約」を解除できます。 単一セルの配列数式を入力する 単一セルの配列数式を使用すると「セルの範囲の制約」を受けることなく、セルの範囲を計算した結果を単一のセルに表示できます。 編集する度に毎回入力する必要があります。 なぜ結果が 2000 になるのかというと、配列数式はセルの範囲を配列に変換しています。 セルの範囲の計算から配列の計算になるので「セルの範囲の制約」が無くなります。 この動作を配列数式は内部で行っています。 単一セルの配列数式を使うと次のことができるようになります。 セルの範囲の計算結果を使用できる。 単一の結果を返す関数をセルの範囲に対して使用できる• 関数に条件を付けられる。 詳しくは下記の「使用例」をご覧ください。 複数セルの配列数式を入力する 複数セルの配列数式は単一セルの配列数式と意味が異なります。 セルの範囲を配列に変換しないため「セルの範囲の制約」を受けたままです。 入力するには結果を表示するセルを範囲選択します。 編集する度に毎回入力する必要があります。 最初に範囲選択した全てのセルで同じ配列数式が入力されます。 絶対参照は必要ありません。 これは配列数式を使わないでも同じ結果を得ることができます。 配列数式にすることで次のようになります。 全てのセルで必ず同じ式になる。 セルを個別に編集できなくなる。 セルの間に行や列を追加できなくなる。 配列数式を入力しているセルは保護されます。 編集するには同じ配列数式を使用している全てのセルを範囲選択して Deleteキーで削除する必要があります。 このように新しいことができるようになるのではなく、セルを保護したり、式を統一する意味で使います。 なので無理をして複数セルの配列数式を使用する必要はありません。 使用例 単一セルの配列数式の使用例を紹介します。 セルの範囲の文字数を数える• 上位 3 位までの合計を求める• 下位 3 位までの合計を求める• 条件に一致する最大値を求める セルの範囲の文字数を数える 「」は単一のセルの文字数しか数えられませんが、配列数式を使うとセルの範囲の文字数を数えられます。 エクセルなら TRUE そうでないなら FALSE になります。 C3:C7の部分で判定した結果が TRUE のときはこの範囲の値を取得します。 FALSE のときは FALSE になります。 ただし FALSE は数値の 0 として扱われます。 負の値の最大値を取得したいときは、FALSE を返すところを -9999 などにする必要があります。 AND 条件に一致する最大値を求める 分類が「エクセル」かつバージョンが「2016」の中で最大の値を取得します。 AND 条件を指定する部分のみ違うのでそこを解説します。 この結果を掛けています。 論理値を数値として扱うときは TRUE が 1 で FALSE が 0 になります。 それ以外の組み合わせはすべて 0 FALSE になります。 入力時の注意として条件を で囲む必要があります。 OR 条件を指定する部分のみ違うのでそこを解説します。 この結果を足しています。 論理値を数値として扱うときは TRUE が 1 で FALSE が 0 になります。 また数値を論理値として扱うときは 0 が FALSEで、それ以外の数値はすべて TRUE になります。 それ以外の組み合わせはすべて 1 以上 TRUE になります。 入力時の注意として条件を で囲む必要があります。 条件1 かつ 条件2 または 条件3.

次の

エクセルExcel大事典 配列数式 配列定数 SUMIF COUNTIF SUMPRODUCT FREQUENCY 複数条件

配列 数式 エクセル

次に、MAX関数を使用して最大の進歩を見いだします。 配列式を使用する 列Dに範囲を格納する必要はありません。 Excelはこの範囲をそのメモリに格納できます。 Excelのメモリに格納されている範囲を 配列定数といいます。 私たちはすでに、次の式を使って最初の生徒の進路を見つけることができることを知っています。 最大の進展(圧倒されない)を見つけるために、MAX関数を追加し、C2をC2:C6とB2をB2:B6に置き換えます。 これらを自分で入力しないでください。 それらは数式を編集すると消えます。 説明:範囲(配列定数)は、範囲ではなくExcelのメモリーに格納されます。 配列定数は次のようになります。 F9キー 配列数式を扱うときは、これらの配列定数を自分で見ることができます。 式でC2:C6-B2:B6を選択します。 F9を押します。 それはよさそうだ。 垂直配列定数の要素はセミコロンで区切られています。 水平配列定数の要素はカンマで区切られます。

次の

相関表の意味とエクセルCOUNTIFS関数・配列数式を使った相関表の作り方 | Note&Board

配列 数式 エクセル

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2019 Excel 2016 Excel 2019 for Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 for Mac Excel for Mac 2011 Excel for iPad Excel for iPhone 配列数式は、配列内の1つ以上の項目に対して複数の計算を実行できる数式です。 配列は、値の行または列、または値の行と列の組み合わせとして考えることができます。 配列数式は、複数の結果、または1つの結果を返すことができます。 用の2018年9月の更新プログラムから、複数の結果を返すことができるすべての数式は、自動的に下方向に、または隣接するセルにスピルされます。 この動作の変更には、いくつかの新しいも伴います。 既存の関数または動的な配列関数のどちらを使用している場合でも、1つのセルに入力するだけで、 enterキーを押すことで確定する必要があります。 一般的に、 CSE数式と呼ばれています。 配列数式を使用すると、次のような複雑なタスクを実行できます。 サンプルデータセットをすばやく作成します。 セル範囲に含まれる文字数をカウントします。 範囲内の最小値や、上限と下限の間にある数値など、特定の条件を満たす数値だけを合計します。 指定した範囲の値のすべての n 番目の値を合計します。 次の例では、複数セルおよび単一セルの配列数式を作成する方法について説明します。 可能であれば、いくつかの動的配列関数、および dynamic と legacy の両方の配列として入力された既存の配列数式について例を示しています。 サンプルをダウンロードする します。 この演習では、複数セルの配列数式および単一セルの配列数式を使用して、売上金額を計算します。 まず、複数セルの数式を使用して、小計を求めます。 次に、単一セルの数式を使用して、総計を求めます。 複数セルの配列数式• Enterキーを押すと、結果が切り捨てられてセル H10: H19 に移動します。 スピル範囲内のセルを選択すると、スピル範囲が境界線で強調表示されます。 また、セル H10: H19 の数式が灰色表示されていることがあります。 これらは参照用のものです。 数式を調整する場合は、マスター数式が保存されているセル H10 を選択する必要があります。 この場合、Excel は配列の値 セル範囲 F10 から G19 を乗算し、SUM 関数を使用して合計を加算します。 この結果、売上の総計である 1,590,000 円が求められます。 この例から、配列数式がいかに便利であるかがわかります。 たとえば、1,000 行のデータがあるとします。 単一のセルに配列数式を作成すると、数式を 1,000 行分下にドラッグしなくても、そのデータの一部またはすべてを集計できます。 また、セル H20」の単一セルの数式は、複数セルの数式 セル H10 から H19 に完全に依存していないことに注意してください。 これは、配列数式を使用することのもう 1 つの利点である柔軟性です。 H20」の数式に影響を与えずに、列 H の他の数式を変更することができます。 また、結果の精度を検証するのに役立つため、このような個別の集計を作成することをお勧めします。 動的配列数式には、次のような利点もあります。 一貫性 H10 の任意のセルをクリックすると、同じ数式が表示されます。 この一貫性が、正確さの向上に役立ちます。 安全性: 複数セルの配列数式のコンポーネントを上書きすることはできません。 たとえば、セル H11 をクリックし、Delete キーを押します。 Excel では、配列の出力は変更されません。 この設定を変更するには、配列の左上のセル、またはセル H10 を選択する必要があります。 ファイルサイズを小さくする 多くの場合、いくつかの中間の数式の代わりに1つの配列数式を使うことができます。 たとえば、car の売上例では、1つの配列数式を使用して、列 E の結果が計算されます。 これは大きな問題ではありませんが、合計する行数が多すぎるとどうなりますか? そのため、大きな違いがあります。 効率性 データを 1 か所にまとめて保存すれば、ディスク領域を節約できます。 配列関数は、複雑な数式を作成するための効率的な方法です。 収まる 動的配列数式は、自動的に出力範囲にスピルされます。 ソースデータが Excel のテーブルに含まれている場合、データを追加または削除すると、動的配列数式のサイズが自動的に変更されます。 SPILL! 誤差 動的配列によってたこの値は、目的のスピル範囲が何らかの理由でブロックされていることを示します。 ボトルネックを解決すると、数式が自動的にスピルされます。 配列定数は、配列数式の構成要素です。 セミコロンを使用して項目を区切った場合は、垂直配列 列 が作成されます。 2次元配列を作成するには、各行の項目をカンマで区切って区切ります。 各行はセミコロンで区切ります。 水平定数、垂直定数、および 2 次元定数を作成する手順を次に示します。 を使用して、配列定数と手動で入力された配列定数を自動的に生成する例を紹介します。 水平定数を作成する 前の例のブックを使用するか、または新しいブックを作成します。 次の結果が表示されます。 次の結果が表示されます。 次の結果が表示されます。 ご覧のとおり、SEQUENCE オプションは、配列定数値を手動で入力するよりも大きな利点があります。 主に時間が節約されますが、手動入力によるエラーの削減にも役立ちます。 また、特にセミコロンはカンマ区切り文字と区別しにくい場合があるため、読みやすくなっています。 大きい数式の一部として配列定数を使う例を次に示します。 サンプルのブックで、数式ワークシート の定数に移動するか、新しいワークシートを作成します。 特定の番号を選択することについて特別なことはありません。 1-5 以外の項目を選択して区別することができます。 この数式は85を返します。 この時点で、格納された値に対応する定数の値を掛ける数式が実行されます。 これは、次の式に相当します。 保存されている配列を使用せずに、操作をすべてメモリ内で維持するには、次の配列定数に置き換えることができます。 数値は、整数、小数点形式、および指数形式で使用できます。 テキストを含める場合は、引用符 "text" で囲む必要があります。 配列定数には、別の配列、数式、または関数を含めることができません。 つまり、カンマまたはセミコロンで区切られた文字列または数値のみを含めることができます。 また、数値には、パーセント記号、ドル記号、カンマ、かっこを含めることができません。 配列定数を使うのに最適な方法の1つは、名前を付けることです。 名前付き定数は使いやすく、使用すると他の人の目には配列数式の複雑さの一部が見えなくなります。 配列定数に名前を付け、数式で使用するには、次の操作を行います。 [ 名前] ボックスに「第1四半期」と入力します。 [ 参照範囲] ボックスに次の定数を入力します 中かっこを手動で入力してください。 次の結果が表示されます。 財務諸表の作成に使用する場合と同じように、12か月のリストを表示する場合は、SEQUENCE 関数を使用して現在の年を1つにすることができます。 この関数の詳細については、表示されているのは月だけですが、他の計算で使用できる有効な日付があることを意味します。 これらの例については、「ブックの例」の「 名前付き配列定数」および「 クイックサンプルデータセット」ワークシートをご覧ください。 この場合、配列定数は1月から12月の配列定数を作成し、"mmm" 1 月、2月、3月など ます。 月全体の名前を表示する場合は、"mmmm" を使用します。 等号を入力しなかった場合、配列は文字列として扱われ、数式は期待どおりに動作しません。 最後に、関数、テキスト、数値の組み合わせを使用できることを覚えておいてください。 これは、どのような創造力が必要かによって異なります。 配列数式で配列定数を使用する方法を示す例をいくつか紹介します。 一部の例では、を使用して行を列に変換したり、その逆の変換を行ったりします。 配列内の2乗の要素の平方根を求めます。 このセクションでは、基本的な配列数式の例を示します。 既存の値から配列を作成する 次の例では、配列数式を使用して、既存の配列から新しい配列を作成する方法について説明します。 数値の配列を作成しているため、「180」と入力します。 D9: D11 で示されているのと同じ値で、3 x 6 のセルの配列が表示されます。 記号は、"こぼれていないと呼ばれ、入力しなくても、配列範囲全体を参照することができます。 既存の値から配列定数を作成する こぼれた配列数式の結果を取得して、それをその構成要素に変換できます。 セル D9 を選択し、 F2キーを押して編集モードに切り替えます。 次に、 F9キーを押してセル参照を値に変換します。 これで、Excel は配列定数に変換されます。 セル範囲の文字数を数える 次の例は、セル範囲内の文字数をカウントする方法を示しています。 これにはスペースも含まれます。 次に、SUM 関数によってそれらの値が加算され、結果 66 が表示されます。 平均文字数を取得したい場合は、次のように使用できます。 この式を、内側の要素から順番に詳しく確認してみましょう。 は、セル範囲 D2: D6 の各項目の長さを返します。 は、これらの項目の中で最大の値を計算します。 これは、セル D3 の最も長いテキスト文字列に対応しています。 ここからは、少し複雑になります。 は、最も長いテキスト文字列を含むセルのオフセット 相対的な位置 を計算します。 この計算を行うには、検査値、検査範囲、および照合の種類という 3 つの引数が必要です。 MATCH 関数は、指定された検査値を検査範囲で検索します。 この例の場合、検査値は、最も長い文字列です。 MAX LEN C9: C13 この文字列は、次の配列に含まれます。 LEN C9: C13 この例の match type 引数は0です。 一致の種類は、1、0、または-1 の値にすることができます。 1-検索値以下の最大値を返します。 0-検索値と厳密に等しい最初の値を返します。 -1-指定した検索値以上の最小値を返します。 照合の種類を指定しなかった場合は、1 が指定されたと見なされます。 最後に、 は次の引数を受け取ります。 配列とその配列内の行と列の番号を指定します。 セル範囲 C9: C13 は配列を提供し、MATCH 関数はセルアドレスを指定し、final 引数 1 は、値が配列の最初の列から取得されることを指定します。 最小のテキスト文字列の内容を取得するには、上の例の MAX をに置き換えます。 範囲内で値の小さい方から n 番目までを検索する 次の例は、セル範囲内で3つの最小値を検索する方法を示しています。 RANDARRAY は volatile 関数であるため、Excel で計算されるたびに新しいランダムな数値のセットが取得されます。 より多くの値を検索するには、SEQUENCE 関数の値を増やすか、定数に引数を追加します。 、 などの追加の関数をこの数式と組み合わせて使用することもできます。 次に例を示します。 範囲内で値の大きい方から n 番目までを検索する 範囲内の最大値を求めるには、SMALL 関数をに置き換えます。 次の例では、 関数と 関数も使用しています。 ROW 関数を使用すると、連続する整数の配列を作成できます。 たとえば、空白を選択して enter キーを押すと、次のようになります。 潜在的な問題を確認するために、配列数式が格納されている範囲の上 つまり、行 1 に行を挿入します。 行参照が調整され、数式によって2から11の整数が生成されるようになりました。 この問題を修正するには、次のように、INDIRECT 関数を数式に追加します。 行の挿入、または配列数式の移動を行っても、Excel によって文字列値が調整されることはありません。 この結果、ROW 関数は、常に目的の整数の配列を生成するようになります。 簡単に使い始めることができます。 間接関数は、一連のテキスト値を返します。 ROW 関数によって、3つのセルの列配列が生成されます。 LARGE 関数は、セル範囲 B9: B18 の値を使用し、ROW 関数によって返された各参照については3回評価されます。 より多くの値を検索するには、INDIRECT 関数により大きなセル範囲を追加します。 最後に、小さな例と同様に、この数式を SUM や AVERAGE などの他の関数と組み合わせて使用できます。 エラー値を含む範囲の合計を求める Excel の SUM 関数は、 VALUE などのエラー値を含む範囲を合計しようとしても機能しません。 内側の関数から順に説明すると、ISERROR 関数は、セル範囲 "データ" でエラーがないか検索します。 IF 関数は、指定された条件を評価した結果が TRUE の場合は特定の値を返し、評価した結果が FALSE の場合は別の値を返します。 この例の場合、すべてのエラー値については TRUE に評価されるため、空の文字列 "" が返され、範囲 データ の残りの値については FALSE に評価される つまり、エラー値を格納していない ため、値自体が返されます。 次に、SUM 関数が、フィルター処理された配列の合計を計算します。 範囲内のエラー値の個数を数える この例は前の数式と似ていますが、フィルター処理の代わりに、データという名前の範囲内のエラー値の数を返します。 次のように、IF 関数の 3 つ目の引数を省いて数式を簡略化しても、同じ結果が得られます。 この数式をさらに簡略化して、次のようにすることもできます。 条件に基づいて値を集計することが必要になる場合があります。 たとえば、次の配列数式では、Sales という名前の範囲に含まれる正の整数のみを合計します。 これは、上の例のセル E9: E24 を表します。 この数式で使用するセル範囲には、指定の数の行と列を含めることができます。 複数の条件を満たす値を合計することもできます。 たとえば、次の配列数式では 、0より大きく2500より小さい値が計算されます。 この配列数式では、MyData および YourData という名前の 2 つのセル範囲の値を比較し、この 2 つの範囲間で相違する値の数を返します。 2 つの範囲の内容が一致する場合は、0 が返されます。 この数式を使用するには、セル範囲が同じ大きさであり、同じ大きさである必要があります。 たとえば、MyData が5行2列の範囲の場合、データは5行5列である必要があります。 IF 関数が、配列に値 0 と値 1 を設定します 不一致の場合は 0 で、同一セルの場合は 1。 次に、SUM 関数が、配列内の値の合計を返します。 この数式は、次のように簡略化できます。 この配列数式は、"データ" という名前の単一列の範囲に含まれる最大値の行番号を返します。 対応するセルに範囲内の最大値が含まれている場合、配列に行番号が格納されます。 それ以外の場合は、配列に空の文字列 "" が格納されます。 MIN 関数は、この新しい配列を 2 番目の引数として使用して、最小値 "データ" の最大値の行番号に対応 を返します。 "データ" という範囲に同じ最大値が複数含まれている場合は、最初の値の行が返されます。 最大値の実際のセル住所を返すには、次の数式を使用します。 この演習では、複数セルの配列数式および単一セルの配列数式を使用して、売上金額を計算します。 まず、複数セルの数式を使用して、小計を求めます。 次に、単一セルの数式を使用して、総計を求めます。 複数セルの配列数式 下のテーブル全体をコピーし、空のワークシートのセル A1 に貼り付けます。 この処理は瞬時に行われ、各販売員について車種ごとの売上合計が E 列に表示されます。 単一セルの配列数式を作成する ブックのセル D13 に、次の数式を入力し、Ctrl キーと Shift キーを押しながら enter キーを押します。 この結果、売上の総計である 1,590,000 円が求められます。 この例から、配列数式がいかに便利であるかがわかります。 たとえば、1,000 行のデータがあるとします。 単一のセルに配列数式を作成すると、数式を 1,000 行分下にドラッグしなくても、そのデータの一部またはすべてを集計できます。 これは、配列数式を使用することのもう 1 つの利点である柔軟性です。 D13 の数式に影響を与えることなく、列 E の数式を変更したり、列をすべて削除したりすることができます。 配列数式には、次のような利点もあります。 一貫性 セル E2 以降、この列のどのセルをクリックしても、同じ数式が表示されます。 この一貫性が、正確さの向上に役立ちます。 安全性: 複数セルの配列数式のコンポーネントを上書きすることはできません。 たとえば、セル E3 をクリックし、 delキーを押します。 セル範囲全体 E2 ~ E11 を選択し、配列全体に対する数式を変更するか、または配列をそのままにしておく必要があります。 追加の安全措置として、Ctrl キーと Shift キーを押しながら enter キーを押して、数式が変更されていることを確認する必要があります。 ファイルサイズを小さくする 多くの場合、いくつかの中間の数式の代わりに1つの配列数式を使うことができます。 たとえば、ブックでは、1つの配列数式を使用して、列 E の結果が計算されます。 多くの場合、配列数式では標準の数式の構文を使用します。 主な違いは、配列数式を使用する場合は、 Ctrl キーと Shift キーを押しながら enter キーを押して数式を入力することです。 これで、配列数式が中かっこで囲まれます 中かっこを手動で入力した場合、数式が文字列に変換され、機能しません。 配列関数は、複雑な数式を作成するための効率的な方法です。 重要: 配列数式を入力する必要がある場合は、Ctrl キーと Shift キーを押しながら enter キーを押します。 これは、単一セルと複数セルの数式の両方に適用されます。 複数セルの数式を操作するときは、次の点にも注意する必要があります。 数式を入力する 前に、結果の格納先のセル範囲を選択します。 これは、複数セルの配列数式を作成する際に、セル E2 ~ E11 を選択したときに行いました。 配列数式の個々のセルの内容を変更することはできません。 実際に試すには、ブックのセル E3 を選択し、Del キーを押します。 配列の一部を変更することができないことを知らせるメッセージが表示されます。 配列数式全体を移動または削除できますが、配列数式の一部を移動または削除することはできません。 つまり、配列数式を縮小するには、既存の数式を削除してから、数式を入力し直す必要があります。 配列数式を削除するには、数式範囲全体 たとえば、 E2: E11 を選び、 deleteキーを押します。 複数セルの配列数式に対して空白セルを挿入したり、セルを削除したりすることはできません。 配列数式は優れていますが、次のような短所があります。 Ctrl キーと Shift キーを押しながら enterキーを押し忘れてしまってもかまいません。 経験豊富な Excel ユーザーでも忘れる可能性があります。 配列数式を入力または編集するときは、このキーの組み合わせを必ず押してください。 ブックの他のユーザーが数式を理解していない可能性があります。 実際には、ワークシートでは通常、配列数式は説明されません。 そのため、他のユーザーがブックを変更する必要がある場合は、配列数式を回避するか、必要に応じて配列数式がわかっていることを確認してください。 コンピューターの処理速度とメモリによっては、大きな配列数式を使用すると、計算速度が低下することがあります。 配列定数は、配列数式の構成要素です。 配列定数は配列数式の構成要素であるため、配列定数の前後に手動で中かっこを入力して定数を囲みます。 次に、 Ctrl キーと Shift キーを押しながら Enter キーを押して、数式全体を入力します。 カンマを使用して項目を区切った場合は、水平配列 行 が作成されます。 セミコロンを使用して項目を区切った場合は、垂直配列 列 が作成されます。 2 次元配列を作成するには、カンマを使用して各行の項目を区切り、さらに、セミコロンを使用して各行を区切ります。 2行の配列では、1行目は1、2、3、4、2番目の行は5、6、7、および8です。 4 と 5 の間の単一のセミコロンで 2 つの行が区切られています。 配列数式と同様、Excel に用意されているほとんどの組み込み関数で配列定数を使用できます。 後のセクションでは、各種の定数を作成する方法、およびこれらの定数を Excel の関数と組み合わせて使用する方法について説明します。 水平定数、垂直定数、および 2 次元定数を作成する手順を次に示します。 水平定数を作成する• 数式バーに次の数式を入力し、Ctrl キーと Shift キーを押しながら enter キーを押します。 次の結果が表示されます。 垂直定数を作成する• ブックで、5 行 1 列のセルを選択します。 数式バーに次の数式を入力し、Ctrl キーと Shift キーを押しながら enter キーを押します。 2 次元定数を作成する• ブックで、4 列 3 行のセルのブロックを選択します。 数式バーに次の数式を入力し、Ctrl キーと Shift キーを押しながら enter キーを押します。 数式で定数を使用する 定数を使用する簡単な例を見てみましょう。 サンプル ブックで、新しいワークシートを作成します。 セル A1 に 3 と入力し、B1 に 4、C1 に 5、D1 に 6、E1 に 7 と入力します。 セル A3 に次の数式を入力し、Ctrl キーと Shift キーを押しながら enter キーを押します。 セル A3 に値 85 が表示されます。 次のセクションでは、この数式がどのように機能するかを説明します。 上で使用した数式は、いくつかの部分で構成されます。 関数 2. 格納された配列 3. 演算子 4. 配列定数は Excel によって自動的に中かっこで囲まれないため、実際に入力する必要があります。 配列数式に定数を追加した後は、Ctrl キーと Shift キーを押しながら enter キーを押して、数式を入力することも覚えておいてください。 Excel では、かっこで囲まれた式の演算が最初に実行されるため、次に関与する要素は、ブックに格納した値 A1:E1 と演算子の 2 つです。 この時点で、格納された値に対応する定数の値を掛ける数式が実行されます。 これは、次の式に相当します。 格納された配列を使用せずに、演算全体をメモリ内で実行するには、格納された配列を次の配列定数で置き換えます。 数値には、整数、小数、および指数表現を使用できます。 文字列を使用する場合は、文字列を引用符 " で囲む必要があります。 配列定数には、別の配列、数式、または関数を含めることができません。 つまり、カンマまたはセミコロンで区切られた文字列または数値のみを含めることができます。 また、数値には、パーセント記号、ドル記号、カンマ、かっこを含めることができません。 配列定数を使うのに最適な方法の1つは、名前を付けることです。 名前付き定数は使いやすく、使用すると他の人の目には配列数式の複雑さの一部が見えなくなります。 配列定数に名前を付け、数式で使用するには、次の操作を行います。 [ 数式] タブの [ 定義された名前] で [ 名前の定義] をクリックします。 [ 名前の定義] ダイアログボックスが表示されます。 [ 名前] ボックスに、「 第 1 四半期」と入力します。 [ 参照範囲] ボックスに次の定数を入力します 中かっこを手動で入力してください。 [ OK] をクリックし、3 個の空白セルがある行を選択します。 次の数式を入力し、Ctrl キーと Shift キーを押しながら enterキーを押します。 名前付き定数を配列数式として使用する場合は、等号を入力することを忘れないでください。 等号を入力しなかった場合、配列は文字列として扱われ、数式は期待どおりに動作しません。 また、文字列と数値の組み合わせを使用できることも覚えておいてください。 配列数式で配列定数を使用する方法を示す例をいくつか紹介します。 一部の例では、を使用して行を列に変換したり、その逆の変換を行ったりします。 配列の各項目を乗算する• 新しいワークシートを作成し、4 列 3 行の空のセルのブロックを選択します。 次の数式を入力し、Ctrl キーと Shift キーを押しながら enterキーを押します。 4 列 3 行の空のセルのブロックを選択します。 次の配列数式を入力し、Ctrl キーと Shift キーを押しながら enter キーを押します。 1 列 5 行の空白セルを選択します。 次の数式を入力し、Ctrl キーと Shift キーを押しながら enterキーを押します。 1 次元の列を転置する• 1 行 5 列の空白セルを選択します。 次の数式を入力し、Ctrl キーと Shift キーを押しながら enterキーを押します。 2 次元定数を転置する• 3 列 4 行のセルのブロックを選択します。 次の定数を入力し、Ctrl キーと Shift キーを押しながら enterキーを押します。 このセクションでは、基本的な配列数式の例を示します。 既存の値から配列および配列定数を作成する 次の例では、配列数式を使用して、異なるワークシートのセル範囲間のリンクを作成する方法を示します。 同じ値セットから配列定数を作成する方法も示します。 既存の値から配列を作成する• Excel のワークシートで、セル C8:E10 を選び、次の数式を入力します。 数値の配列を作成しているからです。 配列数式を使用して、Ctrl キーと Shift キーを押しながら enterキーを押します。 これにより、セル範囲 C8: E10 にこの数値の配列が入力されます。 10 20 求める 40 50 60 70 80 90• C1 ~ E3 のセル範囲を選択します。 数式バーに次の数式を入力し、Ctrl キーと Shift キーを押しながら enter キーを押します。 既存の値から配列定数を作成する• セル C1: C3 が選択されている状態で、 F2キーを押して編集モードに切り替えます。 F9キーを押して、セル参照を値に変換します。 Excel により、値が配列定数に変換されます。 Ctrl キーと Shift キーを押しながら enterキーを押して、配列定数を配列数式として入力します。 セル範囲の文字数を数える セル範囲の文字数 スペースを含む を数える方法の例を次に示します。 このテーブル全体をコピーし、ワークシートのセル A1 に貼り付けます。 データ この文章は、 それぞれのセルに別れて入力されていますが、 すべてのセルに入力されている 文章を合わせて、 一つの文章にすることができます。 セル A8 を選択し、Ctrl キーと Shift キーを押しながら enter キーを押して、セル A2: A6 の文字の合計数を表示します 66。 セル A10 を選択し、Ctrl キーと Shift キーを押しながら enter キーを押して、セル A2: A6 セル A3 のうち、最も長いセルの内容を表示します。 次に、 SUM関数によってそれらの値が加算され、結果 66 が表示されます。 範囲内で値の小さい方から n 番目までを検索する 次の例では、セル範囲内で値の小さい方から 3 番目までを検索します。 セル A1: A11 にランダムな数値を入力します。 この一連のセルに、配列数式から返された結果を格納します。 次の数式を入力し、Ctrl キーと Shift キーを押しながら enterキーを押します。 さらに値を検索するには、定数に引数を追加します。 SUM、 AVERAGE などの追加の関数をこの数式と組み合わせて使用することもできます。 次に例を示します。 次の例では、 ROW 関数と INDIRECT 関数も使用しています。 セル D1 から D3 を選択します。 数式バーに次の数式を入力し、Ctrl キーと Shift キーを押しながら enter キーを押します。 ROW 関数を使用すると、連続する整数の配列を作成できます。 たとえば、演習用のブックでは、10個のセルからなる空の列を選択し、この配列数式を入力して、Ctrl キーと Shift キーを押しながら enter キーを押します。 潜在的な問題を確認するために、配列数式が格納されている範囲の上 つまり、行 1 に行を挿入します。 Excel によって行参照が調整され、2 ~ 11 の整数が生成されます。 この問題を修正するには、次のように、 INDIRECT 関数を数式に追加します。 行の挿入、または配列数式の移動を行っても、Excel によって文字列値が調整されることはありません。 この結果、 ROW 関数は、常に目的の整数の配列を生成するようになります。 ROW 関数によって、3つのセルからなる 列の配列が生成されます。 LARGE関数はセル範囲 A5: A14 の値を使用し、 ROW関数によって返された各参照については、3回評価されます。 3200、2700、および2000の値は、3つのセルの単票配列に返されます。 より多くの値を検索するには、 INDIRECT関数により大きなセル範囲を追加します。 この数式は、前の例と同様に、 SUMや AVERAGEなどの他の関数と共に使用できます。 セル範囲内で最も長い文字列を検索する 前のテキスト文字列の例に戻るには、空のセルに次の数式を入力し、Ctrl キーと Shift キーを押しながら enter キーを押します。 この式を、内側の要素から順番に詳しく確認してみましょう。 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関数はセルアドレスを指定し、final 引数 1 は、値が配列の最初の列から取得されることを指定します。 このセクションでは、高度な配列数式の例を示します。 この例では、エラーを含む、"データ" という名前の範囲の値を集計する方法を示します。 内側の関数から順に説明すると、 ISERROR 関数は、セル範囲 "データ" でエラーがないか検索します。 IF 関数は、指定された条件を評価した結果が TRUE の場合は特定の値を返し、評価した結果が FALSE の場合は別の値を返します。 この例の場合、すべてのエラー値については TRUE に評価されるため、空の文字列 "" が返され、範囲 データ の残りの値については FALSE に評価される つまり、エラー値を格納していない ため、値自体が返されます。 次に、 SUM 関数が、フィルター処理された配列の合計を計算します。 範囲内のエラー値の個数を数える この例は前の数式と似ていますが、"データ" という名前の範囲のエラー値をフィルター処理する代わりに、エラー値の数を返します。 次のように、 IF 関数の 3 つ目の引数を省いて数式を簡略化しても、同じ結果が得られます。 この数式をさらに簡略化して、次のようにすることもできます。 条件に基づいて値を合計する 条件に基づいて値を集計することが必要になる場合があります。 たとえば、次の配列数式では、"売上" という名前の範囲に含まれる正の整数だけを合計します。 この数式で使用するセル範囲には、指定の数の行と列を含めることができます。 複数の条件を満たす値を合計することもできます。 たとえば、次の配列数式では、0 より大きく、かつ、5 以下の値を計算します。 AND 関数と OR 関数は単一の結果 TRUE または FALSE を返しますが、配列数式では結果の配列が必要であるため、配列関数で AND 関数と OR 関数を直接使用することはできません。 この問題に対処するには、前の数式で示したロジックを使用します。 つまり、OR 条件または AND 条件を満たす値に対して、加算や乗算などの数学演算を実行します。 0 を除いた平均を計算する 範囲内の値から 0 を除いて平均を求める方法の例を次に示します。 この数式では、"売上" という名前のデータ範囲を使用しています。 2 つのセル範囲間で相違する値の個数を数える この配列数式では、MyData および YourData という名前の 2 つのセル範囲の値を比較し、この 2 つの範囲間で相違する値の数を返します。 2 つの範囲の内容が一致する場合は、0 が返されます。 この数式を使用するには、2 つのセル範囲が同じサイズで、同じ次元である必要があります たとえば、"データ1" が 3 行 5 列の範囲であれば、"データ2" も 3 行 5 列である必要があります。 IF 関数が、配列に値 0 と値 1 を設定します 不一致の場合は 0 で、同一セルの場合は 1。 次に、 SUM 関数が、配列内の値の合計を返します。 この数式は、次のように簡略化できます。 範囲内の最大値の場所を検索する この配列数式は、"データ" という名前の単一列の範囲に含まれる最大値の行番号を返します。 対応するセルに範囲内の最大値が含まれている場合、配列に行番号が格納されます。 それ以外の場合は、配列に空の文字列 "" が格納されます。 MIN 関数は、この新しい配列を 2 番目の引数として使用して、最小値 "データ" の最大値の行番号に対応 を返します。 "データ" という範囲に同じ最大値が複数含まれている場合は、最初の値の行が返されます。 最大値の実際のセル住所を返すには、次の数式を使用します。 ジョン Walkenbach は、以前の Excel MVP で書かれています。 補足説明 では、いつでも専門家に質問できます。 では、サポートを受けられます。 また、 では、新機能についての提案や改善案を送信することができます。 関連項目.

次の