このチュートリアルでは、Excelアレイの式とは何か、ワークシートに正しく入力する方法、および配列定數(shù)と配列機能を使用する方法を?qū)Wびます。

Excelの配列式は非常に強力なツールであり、マスターするのが最も難しいツールの1つです。単一の配列式は、複數(shù)の計算を?qū)g行し、數(shù)千の通常の式を置き換えることができます。それでも、ユーザーの90％がワークシートでアレイ関數(shù)を使用したことはありません。

実際、アレイ式は、學習する最も混亂するExcel機能の1つです。このチュートリアルの目的は、學習曲線を可能な限り簡単かつスムーズにすることです。

Excelの配列とは何ですか？

配列関數(shù)と式を開始する前に、「配列」という用語が何を意味するかを把握しましょう?；镜膜?、アレイはアイテムのコレクションです。アイテムはテキストまたは番號であり、単一の行または列、または複數(shù)の行と列に存在することができます。

たとえば、毎週の食料品リストをExcelアレイ形式にした場合、次のようになります。

{「牛乳」、「卵」、「バター」、「コーンフレーク」}

次に、セルA1からD1を選択すると、式バーに等記號（=）が先行する上記の配列を入力し、 Ctrlシフトを押すと、次の結(jié)果が得られます。

あなたが今したことは、1次元の水平アレイを作成することです。これまでのところ恐ろしいことはありませんよね？

Excelの配列式とは何ですか？

アレイ式と通常の式の違いは、アレイ式が1つだけでなくいくつかの値を処理することです。言い換えれば、Excelの配列式は、配列內(nèi)のすべての個々の値を評価し、式で表現(xiàn)された條件に従って1つまたは複數(shù)の項目で複數(shù)の計算を?qū)g行します。

アレイ式は複數(shù)の値を同時に処理できるだけでなく、一度に複數(shù)の値を返すこともできます。したがって、アレイ式によって返される結(jié)果も配列です。

Arrayフォーミュラは、Excel 2019、Excel 2016、Excel 2013、Excel 2010、Excel 2007以降のすべてのバージョンで利用できます。

そして今、あなたがあなたの最初のアレイ式を作成するのに適切な時期のようです。

Excelアレイ式の簡単な例

列Bにいくつかのアイテム、列Cに価格があり、すべての販売の総計を計算するとします。

もちろん、最初に=B2*C2と同じように単純なもので各行の小張區(qū)を計算することを妨げるものはありません。次にそれらの値を合計します。

ただし、アレイ式は、追加の列ではなくメモリに中間結(jié)果を保存することがExcelを獲得するため、それらの余分なキーストロークをspareしまない可能性があります。したがって、必要なのは、単一の配列式と2つのクイックステップです。

1. 空のセルを選択し、その中に次の式を入力してください。

   =SUM(B2:B6*C2:C6)

2. キーボードショートカットCTRLシフトを押して、アレイ式を完了します。

   これを行うと、Microsoft Excelは{Curly Braces}でフォーミュラを囲みます。これは、配列の式の視覚的な兆候です。

   式が行うことは、指定された配列の個々の行（セルB2からC6）の個々の行の値を掛け、サブトータルを一緒に追加し、総計を出力します。

   

この単純な例は、アレイ式がどれほど強力であるかを示しています。數(shù)百列のデータを使用するときは、1つのセルに1つの配列式を入力することで、どれだけの時間を節(jié)約できるかを考えてください。

なぜExcelで配列式を使用するのですか？

Excelアレイ式は、洗練された計算を?qū)g行し、複雑なタスクを?qū)g行するための最も便利なツールです。単一の配列式は、文字通り數(shù)百の通常の式を置き換えることができます。配列式は、次のようなタスクに非常に適しています。

• 特定の條件を満たす合計番號、たとえば、範囲內(nèi)の最大または最小値を合計します。
• この例で示されているように、他のすべての行、またはすべてのn^行または列を合計します。
• 指定された範囲のすべてまたは特定の文字の數(shù)をカウントします。これは、すべてのcharをカウントする配列式と、特定の文字をカウントする別の式を示します。

Excelに配列式を入力する方法（Ctrl Shift Enter）

すでにご存知のように、3キーCTRLシフトの組み合わせは、通常の式を配列式に変える魔法のタッチです。

Excelで配列式を入力するとき、留意すべき4つの重要なことがあります。

1. フォーミュラの入力が終了し、同時にCtrl Shift Enterを同時に押すと、Excelは{Curly Braces}間の式を自動的に囲みます。このようなセルを選択すると、フォーミュラバーにブレースが表示されます。
2. フォーミュラの周りにブレースを手動で入力することは機能しません。 CTRLシフトを押すとショートカットを入力して、配列式を完了する必要があります。
3. アレイ式を編集するたびに、ブレースが消え、Ctrl Shift Enterを押して変更を保存する必要があります。
4. Ctrl Shift Enterを押すのを忘れた場合、式は通常の式のように動作し、指定された配列の最初の値のみを処理します。

すべてのExcelアレイ式がctrlシフトを押す必要があるため、 CSE式と呼ばれることもあります。

F9キーを使用して、配列式の部分を評価します

Excelで配列式を使用する場合、セルに表示される最終結(jié)果を表示するために、アイテム（內(nèi)部配列）を計算および保存する方法を観察できます。これを行うには、関數(shù)の括弧內(nèi)で1つまたは複數(shù)の引數(shù)を選択し、 F9キーを押します。フォーミュラ評価モードを終了するには、 ESCキーを押します。

上記の例では、すべての製品のサブトータルを表示するには、B2：B6*C2：C6を選択し、 F9を押して、次の結(jié)果を取得します。

注記。 F9を押す前に式の一部を選択する必要があることに注意してください。そうしないと、F9キーは単に式を計算値に置き換えます。

Excelのシングルセルおよびマルチセルアレイ式

Excelアレイ式は、単一のセルまたは複數(shù)のセルで結(jié)果を返すことができます。さまざまなセルに入力された配列式は、マルチセル式と呼ばれます。単一のセルに存在する配列式は、単一セル式と呼ばれます。

マルチセルアレイを返すように設計されたExcelアレイ関數(shù)がいくつか存在します。たとえば、トレンド、周波數(shù)、ラインなどです。

合計、平均、集合體、最大、minなどのその他の機能は、 CtrlシフトEnterを使用して単一のセルに入力したときに配列式を計算できます。

次の例は、シングルセルおよびマルチセルアレイ式の使用方法を示しています。

例1。シングルセルアレイ式

2か月で販売されているアイテムの數(shù)をリストした2つの列があると、列BとCなど、最大販売の増加を見つけたいとします。

通常、追加の列、たとえば列Dを追加します。これは、 =C2-B2のような式を使用して各製品の販売変更を計算し、その追加列=MAX(D:D)の最大値を見つけます。

アレイ式では、メモリに中間結(jié)果を完全に保存するため、追加の列は必要ありません。したがって、次の式を入力するだけで、 ctrlシフトを押してください。

=MAX(C2:C6-B2:B6)

例2。Excelのマルチセルアレイ式

前の合計の例では、各販売から10％の稅金を支払う必要があり、各製品の稅額を1つの式で計算する必要があるとします。

空のセルの範囲を選択し、d2：d6をたとえば、フォーミュラバーに次の式を入力します。

=B2:B6 * C2:C6 * 0.1

Ctrl Shift Enterを押すと、Excelは選択した範囲の各セルに配列式のインスタンスを配置すると、次の結(jié)果が得られます。

例3。Multi-Cellアレイを返すためにExcel配列関數(shù)を使用する

すでに述べたように、Microsoft Excelは、マルチセルアレイで動作するように特別に設計された、いわゆる「アレイ関數(shù)」をいくつか提供しています。 Transposeはそのような機能の1つであり、上記のテーブル、つまり行を列に変換するためにそれを利用します。

1. 転置されたテーブルを出力する空の範囲のセルを選択します。行を列に変換しているため、ソーステーブルにそれぞれ列と行があるのと同じ數(shù)の行と列を選択してください。この例では、6列と4行を選択しています。
2. F2を押して編集モードに入ります。
3. 式を入力し、 Ctrl Shift Enterを押します。

私たちの例では、式は次のとおりです。

=TRANSPOSE($A$1:$D$6)

結(jié)果はこれに似ています。

これは、Excel 2019以前のCSEアレイ式としてTransposeを使用する方法です。ダイナミックアレイExcelでは、これは通常の式としても機能します。 Excelで転換する他の方法を?qū)Wぶには、このチュートリアル：Excelで列と行を切り替える方法をご覧ください。

マルチセルアレイ式の操作方法

Excelでマルチセルアレイ式を使用する場合は、これらのルールに従って正しい結(jié)果を得るようにしてください。

1. 式を入力する前に、結(jié)果を出力するセルの範囲を選択します。
2. マルチセルアレイ式を削除するには、それを含むすべてのセルを選択して削除を押すか、フォーミュラバーの式全體を選択し、削除を押してからCtrl Shift Enterを押します。
3. 個々のセルの內(nèi)容をアレイ式で編集および移動したり、マルチセルアレイ式から既存のセルを削除したり削除したりすることもできません。これを試してみると、Microsoft Excelは「配列の一部を変更することはできません」という警告を投げます。
4. アレイ式を縮小するには、つまり、より少ないセルに適用するには、最初に既存の式を削除してから新しい式を入力する必要があります。
5. アレイ式を拡張するには、IEをより多くのセルに適用し、現(xiàn)在の式と空のセルを含むすべてのセルを選択し、F2を押して編集モードに切り替え、式の參照を調(diào)整し、 CTRLシフトを押して更新します。
6. Excelテーブルでマルチセルアレイ式を使用することはできません。
7. フォーミュラで返された結(jié)果の配列と同じサイズのセルの範囲にマルチセルアレイ式を入力する必要があります。 Excelアレイフォーミュラが選択した範囲よりも大きい配列を生成すると、ワークシートには過剰な値が表示されません。フォーミュラによって返される配列が選択した範囲よりも小さい場合、＃n/aエラーが追加のセルに表示されます。

フォーミュラがさまざまな數(shù)の要素を持つ配列を返す場合は、この例で示すように、式で返される最大配列以上の範囲または最大配列以上に入力します。

Excelアレイ定數(shù)

Microsoft Excelでは、配列定數(shù)は単に靜的値のセットです。これらの値は、他のセルまたは値に式をコピーすると決して変わりません。

このチュートリアルの冒頭で、食料品リストから作成された配列定數(shù)の例をすでに見ました。それでは、他のアレイタイプが存在し、それらをどのように作成するかを見てみましょう。

3種類の配列定數(shù)が存在します。

1。水平アレイ定數(shù)

一定の水平配列は連続して存在します。一定の行アレイを作成するには、コンマで區(qū)切られた値を入力し、{1,2,3,4}など、ブレースに囲みます。

注記。配列を定數(shù)作成するときは、手動で開閉ブレースを入力する必要があります。

スプレッドシートに水平配列を入力するには、相當の數(shù)の空白セルの數(shù)={1,2,3,4}連続して選択し、式バーに式を入力し、 ctrlシフトを押します。結(jié)果はこれに似ています。

スクリーンショットでわかるように、Excelは別のブレースセットに配列定數(shù)をラップします。

2。垂直配列定數(shù)

垂直配列は列に定數(shù)存在します。たとえば、セミコロンでアイテムを區(qū)切る唯一の違いを持つ水平アレイと同じ方法で作成します。

={11; 22; 33; 44}

3。2次元配列定數(shù)

2次元配列を作成するには、各行をセミコロンとデータの各列でコンマで分離します。

={"a", "b", "c"; 1, 2, 3}

Excelアレイ定數(shù)を使用します

配列定數(shù)は、Excel配列式の礎石の1つです。次の情報とヒントは、最も効率的な方法でそれらを使用するのに役立つ場合があります。

1. 配列定數(shù)の要素

   配列定數(shù)には、數(shù)値、テキスト値、ブール値（真および偽）、およびエラー値が含まれ、コンマまたはセミコロンで區(qū)切られます。

   整數(shù)、小數(shù)、または科學的表記として數(shù)値を入力できます。テキスト値を使用する場合は、Excelフォーミュラのように、二重引用符（ "）に囲まれる必要があります。

   配列定數(shù)には、他の配列、セル?yún)⒄铡⒐爣?、日付、定義された名前、式、または機能を含めることはできません。

2. アレイ定數(shù)の命名

   配列を常に使いやすくするには、名前を付けます。

   • [式]タブに切り替え> [定義された名前]グループに切り替えて、 [名前を定義]をクリックします。または、 Ctrl F3を押して[新規(guī)]をクリックします。
   • 名前に名前を入力します
   • ボックスを參照するには、前述の等式記號（=）が付いたブレースに囲まれたアレイ定數(shù)のアイテムを入力します。例えば：

     ={"Su", "Mo", "Tu", "We", "Th", "Fr", "Sa"}

     

   • [OK]をクリックして名前付き配列を保存し、ウィンドウを閉じます。

   指定された配列定數(shù)をシートに入力するには、配列にアイテムがあるのと同じくらい多くのセルを列または列に選択し、=記號の前にあるフォーミュラバーに配列の名前を入力し、 Ctrl Shift Enterを押します。

   結(jié)果はこれに似ているはずです。

   

3. エラーの防止

   配列定數(shù)が正しく機能しない場合は、次の問題を確認してください。

   • 適切な文字である水平アレイ定數(shù)のコンマと垂直のセミコロンを使用して、配列の要素を一定に區(qū)切ります。
   • 配列定數(shù)內(nèi)のアイテムの數(shù)と正確に一致するセルの範囲を選択しました。より多くのセルを選択すると、各追加セルに＃n/aエラーが発生します。選択したセルを選択すると、配列の一部のみが挿入されます。

Excel式で配列定數(shù)を使用します

アレイ定數(shù)の概念に精通しているので、配列情報を使用して実際のタスクを解決する方法を見てみましょう。

例1。範囲內(nèi)の最大 /最小數(shù)値を合計

まず、合計するだけの數(shù)値を含む垂直配列定數(shù)を作成することから始めます。たとえば、範囲で3つの最小または最大數(shù)を追加する場合、配列定數(shù)は{1,2,3}です。

次に、大規(guī)模または小さな関數(shù)のいずれかを取り、最初のパラメーターにセルの全範囲を指定し、2番目のパラメーターに配列定數(shù)を含めます。最後に、このように合計関數(shù)に埋め込みました。

最大の3つの數(shù)字を合計します： =SUM(LARGE(range, {1,2,3}))

最小3の數(shù)字を合計します： =SUM(SMALL(range, {1,2,3}))

Array式を入力するため、 Ctrl Shift Enterを押すことを忘れないでください。次の結(jié)果が得られます。

同様の方法で、範囲內(nèi)のn平均nの最小値または最大値を計算できます。

上位3つの數(shù)値の平均： =AVERAGE(LARGE(range, {1,2,3}))

下部3の數(shù)値の平均： =AVERAGE(SMALL(range, {1,2,3}))

例2。複數(shù)の條件でセルをカウントするアレイ式

注文のリストがあり、特定の売り手が與えられた製品を販売した回數(shù)を知りたいとします。

最も簡単な方法は、複數(shù)の條件を持つcountifs式を使用することです。ただし、多くの製品を含めたい場合は、Countifs式が大きく大きくなる可能性があります。よりコンパクトにするために、Countifsを合計と一緒に使用し、1つまたは複數(shù)の引數(shù)に配列定數(shù)を含めることができます。

=SUM(COUNTIFS(range1, "criteria1", range2, {"criteria1", "criteria2"}))

実際の式は次のように見えるかもしれません：

=SUM(COUNTIFS(B2:B9, "sally", C2:C9, {"apples", "lemons"}))

目標はアプローチを?qū)g証することであるため、サンプルアレイは2つの要素のみで構(gòu)成されています。実際の配列式には、Excel 2019-2007（Excel 2003以降で1,024文字）で式の合計長さが8,192文字を超えない場合、ビジネスロジックに必要な數(shù)の要素を含めることができます。詳細については、配列式の制限をご覧ください。

そして、ここに、テーブル內(nèi)のすべての一致する値の合計を見つける高度な配列式の例があります：sumとvlookupは配列定數(shù)を使用します。

およびまたはExcelアレイ式のオペレーター

アレイオペレーターは、式に配列の処理方法を伝えます - 使用およびまたはロジック。

• 演算子は、乗算記號であるアスタリスク（*）です。すべての條件がTrueに評価された場合、ExcelにTrueを返すように指示します。
• またはオペレーターはプラス記號（）です。與えられた式の條件のいずれかがTrueに評価される場合、TRUEを返します。

アンドオペレーターの配列式

この例では、営業(yè)擔當者がマイクであり、製品がリンゴである販売の合計を見つけます。

=SUM((A2:A9="Mike") * (B2:B9="Apples") * (C2:C9))

または

=SUM(IF(((A2:A9="Mike") * (B2:B9="Apples")), (C2:C9)))

技術(shù)的には、この式は、同じ位置の3つの配列の要素を掛けます。最初の2つの配列は、A2：A9をMike "およびB2：B9と「Apples」と比較した結(jié)果であるTrue and False値で表されます。3番目の配列には、範囲C2：C9の販売數(shù)が含まれます。 3番目の配列の対応する要素は、たとえば1*1*c2 = 10）になります。{10; 0; 0; 0; 0; 0; 0}。

ORオペレーターを使用したExcelアレイ式

OR Operator（）を使用した次の配列式は、営業(yè)擔當者がMikeまたは製品であるすべての販売を追加します。

=SUM(IF(((A2:A9="Mike") (B2:B9="Apples")), (C2:C9)))

この式では、最初の2つの配列（テストする條件です）の要素を合計し、少なくとも1つの條件がtrueに評価された場合はtrue（> 0）を取得します。 FALSE（0）すべての條件がfalseに評価される場合。次に、添加の結(jié)果が0より大きいかどうかをチェックし、それがそうである場合、合計は3番目のアレイ（C2：C9）の対応する要素を追加します。

ヒント。 Excelの現(xiàn)代バージョンでは、この種のタスクに配列式を使用する必要はありません。シンプルなSumif式はそれらを完全に処理します。それにもかかわらず、アレイ式のおよびまたはまたは、より複雑なシナリオで役立つことが証明される可能性があります。

Excel Array式の二重統(tǒng)一演算子

Excelで配列式を使用したことがあるなら、ダブルダッシュ（ - ）を含むいくつかのものに出くわす可能性があり、それが何に使用されているのか疑問に思うかもしれません。

技術(shù)的には二重単位演算子または二重負と呼ばれるダブルダッシュは、いくつかの式によって返される非數(shù)量ブール値（true / false）を1および0に変換するために使用されます。

次の例では、物事が理解しやすくなることを願っています。列Aに日付のリストがあり、1年に関係なく、1月に何日が発生するかを知りたいとします。

次の式は治療に機能します。

=SUM(--(MONTH(A2:A10)=1))

これはExcelアレイ式であるため、 Ctrl Shift Enterを押して完了することを忘れないでください。

他の月に興味がある場合は、1を?qū)潖辘工霐?shù)字に置き換えます。たとえば、2つは2月、3つの手段3月などを表しています。式をより柔軟にするために、スクリーンショットで示されているように、いくつかのセルの月數(shù)を指定できます。

そして今、この配列の式がどのように機能するかを分析しましょう。月関數(shù)は、シリアル番號で表されるセルA2からA10の各日付の月を返し、配列{2; 1; 4; 2; 12; 1; 2; 2; 12; 1}を生成します。

その後、アレイの各要素は、この例のセルD1の値と比較されます。この比較の結(jié)果は、ブール値が真とfalseの配列です。覚えているように、アレイ式の特定の部分を選択してF9を押して、その部分が何に相當するかを確認できます。

最後に、これらのブール値を1と0に変換する必要があります。これは、合計関數(shù)が理解できます。そして、これは二重のネガティブな兆候が必要なものです。最初の単位は、それぞれ-1/0にtrue/falseを強制します。 2番目のものは値を無効にし、つまりサインを逆にし、それらを1と0に変換します。上記の式から二重単位を削除すると、機能しません。

この短いチュートリアルが、Excelアレイの式を習得するためのあなたの道で役立つことを証明していることを願っています。來週、高度なフォーミュラの例に焦點を當てることにより、Excelアレイを継続します。お楽しみに、お読みいただきありがとうございます！

以上がExcelの配列の式と機能 - 例とガイドラインの詳細內(nèi)容です。詳細については、PHP 中國語 Web サイトの他の関連記事を參照してください。