ネストされたクリエイティブキット内のすべての基本アセットを計算するには、直接配置されたアセットとキットの内容を分け、ネストの各階層を掛け合わせて、結果を合計します。この Mac 版 Excel の手順では、完全な所要数マトリックスと、アセットとキットを正確に照合する検索式を作成します。

ここでの課題は、キットにほかのキットが含まれる場合に、1つのキット、または指定した数量のキット一式に必要な各グラフィックの数を求めることです。以下のラベルと数量はすべて架空のものです。この計算ではグラフィックの使用数を数え、再利用可能な元ファイルの重複は除きません。

この手順に対応する Mac 版 Excel のバージョンは？

Mac 版 Microsoft 365 Excel、Mac 版 Excel 2024、または Mac 版 Excel 2021 を使用してください。Microsoft は、ここで使用するマトリックス関数と検索関数について、これらのエディションを対象として挙げています。また、Mac 版 Excel 2021 で動的配列が使えることも明記しています。この手順では、通常のワークシート範囲と、Enter キーで確定する数式を使用します。MMULT 関数、INDEX 関数、Mac 版 Excel 2021 の新機能。

動的配列数式は隣接するセルに自動で値を展開します。この動作をスピルと呼びます。各配列数式は指定された開始セルにだけ入力し、展開先の範囲は空けておいてください。スピル配列数式はテーブル内ではサポートされないと Microsoft が説明しているため、これらの数式は Excel テーブルの外に配置します。動的配列数式とスピル配列の動作。

計算例の算術は、小規模な決定論的 Python 計算で確認しました。数式を Excel デスクトップアプリケーションで実行したわけではありません。

直接配置するアセットとネストされたキットはどう記録する？

空白のワークシートから始めます。入力マトリックスを2つ作成します。1つは各キットに直接配置する基本グラフィック用、もう1つはほかのキットに直接含めるキット用です。これらの入力を分けることで、展開済みの数量を再度数えてしまうのを防げます。

直接配置する基本アセットのマトリックスを入力する

次のタブ区切りのブロックを A1 に貼り付けます。数値部分 B2:D5 には、アセットの行が4つ、キットの列が3つあります。

アセット	DotKit	StripeKit	StudioKit
Dot	2	1	0
Arc	1	0	2
Grid	0	3	1
Tile	0	1	0

各列は、1つのキットに直接含まれるグラフィックを表します。1つの DotKit には Dot が2個、Arc が1個、直接含まれます。1つの StripeKit には Dot が1個、Grid が3個、Tile が1個、直接含まれます。ネストされたキットは別に記録します。

存在しない構成要素には、数値のゼロを入力します。Microsoft によると、MMULT の入力には数値を含める必要があります。空白セルやテキストがあると #VALUE! が発生することがあります。関数に渡す数値範囲の外にラベルを置いてください。MMULT 関数。

キット内のキットのマトリックスを入力する

次のブロックを A7 に貼り付けます。行は構成キット、列は親キットを表します。数値のネストマトリックスは B8:D10 に配置します。

構成キット	DotKit	StripeKit	StudioKit
DotKit	0	2	1
StripeKit	0	0	3
StudioKit	0	0	0

このマトリックスは、次のレシピを表しています。

  • DotKit にはほかのキットは含まれません。
  • StripeKit には DotKit が2つ含まれます。
  • StudioKit には DotKit が1つ、StripeKit が3つ含まれます。

直接の内容だけを記録します。StudioKit に直接含まれる DotKit 1つは D8 に記録します。その3つの StripeKit に含まれる DotKit 6つは計算で求めます。D8 に7と入力すると、6つを二重に数えてしまいます。

B1:D1、B7:D7、A8:A10 では、DotKit、StripeKit、StudioKit の順序を必ずそろえてください。マトリックスの乗算では位置が対応するため、ラベルが一致していても順序が異なれば正しく計算できません。

この例では、数量は0以上の整数であり、レシピは最終的に基本アセットに至るものとします。ほかのキットを経由しても、キットが自分自身を含むことはできません。

完全な所要数マトリックスを計算するには？

以下の説明では、直接配置するアセットのマトリックスを B、ネストマトリックスを N と呼びます。これらは数学上の記号であり、ブック内で定義する名前ではありません。B に N を掛けると、キットの包含関係を1階層たどった分のアセットが求められます。その結果に N を掛けると、さらにもう1階層分が加わります。

この3つのキットでは、StudioKit の中に StripeKit があり、その中に DotKit がある経路が最も深くなります。そのため、完全なマトリックスは、直接のアセット、ネストを1階層たどったアセット、2階層たどったアセットを合計したものです。

完全な所要数 = B + BN + BN²

上付きの数字は、マトリックスの乗算を繰り返すことを表します。Excel では MMULT を使って計算します。範囲に通常の ^2 演算子を使うと、各要素が個別に2乗されます。

ネストの各階層における寄与分を計算する

この数式を G2 に入力してから Enter キーを押します。ネストを1階層たどった分のアセットが G2:I5 に展開されます。

=MMULT($B$2:$D$5,$B$8:$D$10)

次の数式を L2 に入力します。ネストを2階層たどった分のアセットが L2:N5 に展開されます。

=MMULT(G2:I5,$B$8:$D$10)

行列のサイズを見ると、乗算の順序が分かります。4行×3列のアセットマトリックスに3行×3列のネストマトリックスを掛けると、4行×3列のアセットマトリックスになります。Microsoft によると、最初の配列の列数は2番目の配列の行数と一致している必要があります。MMULT 関数。

中間結果を確認するには、G2:I5 の StudioKit 列に、アセット順の Dot、Arc、Grid、Tile に従って 5、1、9、3 が表示されていることを確認します。L2:N5 の StudioKit 列には、12、6、0、0 が表示されているはずです。

直接分とネスト分を合計する

DotKit、StripeKit、StudioKit を Q1:S1 に入力します。Dot、Arc、Grid、Tile を P2:P5 に入力します。次に、この数式を Q2 に入力します。

=$B$2:$D$5+G2:I5+L2:N5

完成したマトリックスは Q2:S5 に表示され、次のようになります。

基本アセット DotKit StripeKit StudioKit
Dot 2 5 17
Arc 1 2 9
Grid 0 3 10
Tile 0 1 3

StudioKit に必要な Dot の数を手作業で確認してみましょう。直接の Dot は0個、直接含まれる DotKit から2個、Dot がそれぞれ5個入った StripeKit 3つから15個です。合計は17個です。この確認では、共有サブキットを経由する経路も含め、同じアセットに至るすべての経路をたどります。

ネストの階層を見落としていないことを確認するには？

もっともらしい合計でも、展開が完了しているとは限りません。ネストマトリックス自体を使い、さらにもう1階層の包含関係があるか確認します。

この数式を G8 に入力して、2階層分のネストマトリックスを G8:I10 に計算します。

=MMULT($B$8:$D$10,$B$8:$D$10)

次に、この数式を L8 に入力して、3階層分のネストマトリックスを L8:N10 に計算します。

=MMULT(G8:I10,$B$8:$D$10)

このデータでは、G8:I10 にゼロ以外の値は1つだけあり、StudioKit 内の StripeKit を経由してたどった DotKit が6つです。L8:N10 の値はすべてゼロです。3階層の経路がないため、すでに含まれている2階層分を超える寄与はありません。

この終了条件はマトリックスモデルから導かれます。数量がすべて0以上なら、次のネストマトリックスがすべてゼロであることは、それより長いネスト経路もすべて存在しないことを意味します。これは、特定のアセットに次の寄与があるかだけを確認する方法より確実です。

異なるキットが k 個ある大きなブックで、包含サイクルがない場合、最長の経路は k−1 階層までです。アセット寄与マトリックスにネストマトリックスを繰り返し掛け、その結果を加算します。次のネスト累乗の値がすべてゼロになったところで終了します。上記の3項式はこの例では完全ですが、キットが増えて経路が深くなる場合は拡張が必要です。

キットを、単純なものからネストの深いものへ順に並べると確認しやすくなります。提示したネストマトリックスでは、ゼロ以外の値はすべて対角線より上にあります。対角成分が正の値なら、キットが直接自分自身を含むことを示します。より長い経路で元のキットに戻る場合は、この有限展開を使う前にレシピを修正してください。

特定のアセットとキットの数量を検索するには？

W1 に「アセット」、X1 に「キット」、Y1 に「数量」と入力します。W2 に Arc、X2 に StudioKit を入力します。Y2 には、次を入力します。

=INDEX($Q$2:$S$5,MATCH(W2,$P$2:$P$5,0),MATCH(X2,$Q$1:$S$1,0))

結果は 9 になるはずです。最初の MATCH がアセットの行を特定し、2番目がキットの列を特定します。INDEX がその交差する値を返します。Microsoft の説明によると、MATCH と 0 を使うと行と列を選択でき、ラベルを並べ替えなくても完全一致を指定できます。INDEX 関数、MATCH 関数。

各軸のラベルは重複しないようにしてください。完全一致する MATCH は、最初に一致した位置を返します。ラベルが見つからないと #N/A が返されます。そのエラーはゼロに置き換えず、名前を確認する間は表示したままにします。所要数マトリックスでは、ゼロにすでに意味があるためです。MATCH 関数。

複数のキットの合計数を計算するには？

DotKit、StripeKit、StudioKit を Q7:S7 に入力します。2、1、3 を Q8:S8 に入力します。これは個別に要求された数量で、DotKit が2つ、StripeKit が1つ、StudioKit が3つです。

U1 に「総使用数」と入力し、次の数式を U2 に入力します。

=MMULT($Q$2:$S$5,TRANSPOSE($Q$8:$S$8))

TRANSPOSE は、横方向に並んだ3キットの需要行を縦方向の列に変換します。その列にマトリックスを掛けると、基本アセットごとの合計が求められ、U2:U5 に展開されます。Microsoft によると、TRANSPOSE は行と列を入れ替えます。TRANSPOSE 関数。

基本アセット 計算 合計使用数
Dot 2×2 + 5×1 + 17×3 60
Arc 1×2 + 2×1 + 9×3 31
Grid 0×2 + 3×1 + 10×3 33
Tile 0×2 + 1×1 + 3×3 10

内部で必要になる DotKit や StripeKit を、この需要行に加算しないでください。これらの内容は完全なマトリックスにすでに含まれています。追加の需要欄には、別途要求されたキットだけを入力します。

結果がおかしいときは何を確認する？

数式を変更する前に、次の点を確認してください。

  1. 配列に #SPILL! が表示される： 展開先に既存の値や結合セルがないか確認します。妨げとなっている要素を移動するか、数式を別の場所に置きます。Microsoft はどちらの条件もスピルを妨げる要因として挙げています。#SPILL! エラーを修正する方法。
  2. 乗算に #VALUE! が表示される： 入力に空白、テキスト、見出しが含まれていないか、または範囲のサイズが一致しているか確認します。MMULT 関数。
  3. 合計が多すぎる： 直接入力のマトリックスにサブキットの展開済み内容が含まれていないか、または内部キットの数量をまとめて需要に加えていないか確認します。
  4. 合計が少なすぎる： 次のネスト累乗を確認します。値がゼロ以外なら、展開に寄与分をもう1階層追加する必要があるかもしれません。
  5. 数値は妥当に見えるが見出しが違う： 両方の入力マトリックスと出力見出しで、キットの順序を比較します。

ブックを拡張する場合は、影響を受ける入力範囲、補助計算、出力見出し、検索範囲、需要範囲をすべて更新してください。これらの数式ではセル番地を固定しているため、既存の入力の隣に新しいキットを入力しても、自動では計算に含まれません。

直接レシピを編集可能な入力として保ち、計算の横にネストの深さの確認を残し、構造を変更するたびにアセットへの経路を1つ完全にたどって確認してください。これらの確認を行えば、完成したマトリックスを使って、記録した同じレシピから個別のキットの検索と一括数量の計算ができます。

出典