配列 数式 エクセル。 条件付き書式に配列数式は使えるのか: ぱそらぼ(ぱぁと2)

Excel 配列数式とは

配列 数式 エクセル

配列数式とは、文字通り「配列」を数式の中に当てはめて結果を求める数式です。 配列は定数で指定することもできますし、セル範囲を指定することもできます。 配列数式を用いることで、本来ならば複数の作業セルを用いて求めなければならない計算結果を、一つの数式で求めることができます。 また、複数の条件に合致したデータの件数や合計値などを一つの数式で求めることも可能です。 早速、配列数式を利用した計算を見ていきましょう。 まず、このような表で配列数式を利用してみます。 ある月の社用車の燃料費の計算を行っています。 社用車はA車、B車、C車の3車でA車とB車はガソリン車です。 C車はディーゼル車なので軽油を使用します。 一回で入れるリッター数や月に給油する回数もそれぞれ違います。 作業セルを用いて計算する。 作業セルを用いれば、それぞれの燃料費の小計を算出し、合計することでその月の燃料費の合計が求められます。 SUMPRODUCT関数を用いて計算する。 この場合、単価が入力されている「B2:B4」とリッター数が入力されている「C2:C4」、回数が入力されている「D2:D4」がそれぞれ配列になります。 答えは当然、各行の小計の合計値と等しくなります。 配列数式を用いて計算する。 7500の数値が返ってきたことと思います。 なぜこうなるかというと、SUM関数が共通部分参照を行う関数だからです。 試しに、この式をF3、F4と移動すると、各行の小計を表示するはずです。 そしてF5に移動したとき、5行目には計算する数値がないため、 VALUE! のエラー値を返すはずです。 では、F5にまで移動させた後、F5セルを編集可能状態にします。 そして、F5セルに燃料費の合計値が表示されているはずです。 これが 配列数式です。 Enterだけでは配列数式として確定されませんので注意してください。 このとき、B2:B4、C2:C4、D2:D4、はそれぞれ配列として数式の中にもたれます。 あとはそれぞれの一次元配列の要素を順番に計算していきます。 上記の説明で便宜上、一次元配列と呼びましたが、配列定数は基本的に矩形の二次元配列になります。 上記の場合は3行1列の二次元配列です。 この中で、","は列の区切り、";"は行の区切りになります。 この例の場合では、3行3列の矩形範囲に1〜9のデータが格納された二次元配列になります。 つまり、東京在住でもなく男性でもない場合以外は全て会費を返すわけです。 これはIF関数の場合、条件式が0(FALSE)以外ならすべてTRUEと判断するからです。 1でも2でもTRUEと判定します。 人数を求める場合は工夫が必要です。 そこで、0(0以外)の条件をつけ再度、TRUE・FALSEの判定をしています。 最後に1を掛けているのは、先ほど述べたとおり、そのままだとTRUEはTRUEとして扱われ、数値としては扱われないため一度演算をしてやる必要があるからです。 AND条件(論理値の積) TRUE TRUE 1 TRUE FALSE 0 FALSE TRUE 0 FALSE FALSE 0 OR条件(論理値の和) TRUE TRUE 2 TRUE FALSE 1 FALSE TRUE 1 FALSE FALSE 0 このようになります。 最後に、配列数式を用いて数値範囲のデータ個数を求めてみましょう 数値範囲のデータ個数を求める関数に、FREQUENCY関数があります。 まずはFREQUENCY関数を用いて、年齢別の会員数を調べてみましょう。 どこでもいいので、3行1列のセル範囲を選択します。 このとき、29歳以下のデータ個数が1行目のセルに、30〜39歳のデータ個数が2行目のセルに、40歳以上のデータ個数が3行目のセルに表示されます。 (上述の29以下、30〜39、40以上の3つの返り値) 返される要素が3つなので当然、表示するセルも3つ必要になります。 そこで、3行1列のセル範囲に配列数式として結果を表示させたというわけです。 またFREQUENCY関数を使用しなくても、先ほどの複数条件指定の配列数式を用いることで、データ個数を求めることが可能です。 G1〜G3のセルに0,30,40の数値を、H1〜H3のセルに29,39,100の数値を入力します。 I1のセルに次の配列数式を入力します。 この配列数式は、年齢がG列の数値以上、H列の数値以下の場合、論理値の積として1を返し、SUM関数でその数を合計しています。 つまり、FREQUENCY関数同様に、ある数値からある数値の範囲までのデータ個数を計算しているわけです。 この場合さらに、数値以外の条件を追加してデータ個数を算出することも可能です。 以上、配列数式について解説してきました。 配列数式は必ずおぼえなければならないテクニックではありません。 配列数式を使用しなくても、同様の計算結果を作業セルや関数を用いて算出することは可能です。 しかし配列数式が使えれば、作業セルを使用して求めた計算結果を検証したり、表を変更することなく複雑な計算結果を算出できるなど、エクセルの使用範囲や用途が大きく広がります。 おぼえておいても、決して損なテクニックではないでしょう。 " からオブジェクト指向まで! 1.

次の

【Excel】エクセル配列数式とは?複数セルを1つの数式に!!

配列 数式 エクセル

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

次の

配列定数を配列数式の中で使う

配列 数式 エクセル

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 で書かれています。 補足説明 では、いつでも専門家に質問できます。 では、サポートを受けられます。 また、 では、新機能についての提案や改善案を送信することができます。 関連項目.

次の