今回は 配列式 や スピル についてです。
配列式 や スピル というと少し 「理解しにくい」 とか 「使いにくい」 という感じもありますが、 計算式をまとめて書くことができて全部のセルで式が全く同じなので、 とてもシンプルで間違いが起こりにくく、 セルの一部を修正することができなくなるので、 誤って表を壊してしまうこともなくなります。 このように、使い方によっては メリットも多くありますので、 是非使ってみることをおすすめします。 ぜひ最後までご覧ください。
納品書や請求書など「個数と単価を掛けて金額」を出すというような表はよく使いますよね。
販売した製品の製品名や単価および数量さえ入力すれば各製品の販売金額が自動で計算され、更に販売した全製品の合計金額も自動で求めて表示されるというような使い方は、エクセルを使ったことがある人なら必ず一度は作ったことがあるのではないでしょうか。
例えば単価の列と数量の列を行毎に掛け算して金額の列を作る、というような場合です。
A列から製品名、単価、数量、金額という順で列が構成されているときに、金額のセルに＝単価1*数量1という数式を書き、その下へ式をコピーをしていきます。
コピーは、数式を書いたセルで[Ctrl]+[C]し、コピー先で[Ctrl]+[V]するとできますね。
または数式を書いたセルとその下のセルを同時選択して[Ctrl]+[D]で一気にコピーもできます。
さらにもう一つ、数式を書いたセルの右下の角にある小さな四角をマウスで掴んで下に引っ張るっていうのもありますよね。
これで、表は完成となるわけです。
普通はこういう使い方が大半だと思いますし、問題なく使えるものです。
今回は、ここから更に一歩進んだ使い方をご紹介したいと思います。
配列式 で計算する
こうして コピペ で作成した各セルの数式は、それぞれの行毎に違う数式になっています。
二行目は=単価2*数量2、三行目は=単価3*数量3、、、という具合です。
せっかく作成した表ですが、どうしても1つのセルだけは違う計算にしたい！というニーズが発生した場合にそのセルだけを直接に値で上書きをしたり、計算式を変えたりして運用されることが発生してしまいます。
ここで「発生してしまいます」と書いたのは、個人で使っている分には その値を上書きしたり計算式を変えたりすること自体は問題ではないのですが、 複数の人が ファイルを共有 したり 将来自分で ファイルを再利用 したりした時に思わぬ問題が起こってしまったという事例が多く見受けられるためです。
再利用する場合、もし一部のセルを修正したことを忘れて 訂正せずに使ってしまうと、間違った結果の帳票が出来てしまいます。 場合によっては大変な事態にもなりかねません。
このような、大変な事態に陥る 前に 対策として次に紹介する 配列式 で数式を作成する方法が有効です。
一行毎の数式を作成してコピペする のではなく、単価の配列 と 数量の配列 を掛け算する計算式を書いて、金額の配列 を求めるという荒技です。
ここでいう 単価の配列 というのは 単価が入ったセルの列の範囲 で指定します。[単価1:単価5]のようにします。
まず数式を入力したい金額の列の範囲を選択します。
そして1つのセルに＝数量1:数量5*単価1:単価5と入力
普通なら[Enter]を押すところで[Ctrl]+[Shift]+[Enter]を同時に押します
そうすると一気に選択範囲に計算式がコピーされ結果が表示されます。
計算式は{=数量1:数量5 * 単価1:単価5}のように波括弧で囲まれた式で、どのセルにも同じ計算式が入力されています。
また数式には絶対参照の記述は必要ありません。これも、大きなメリットです。 絶対参照の記述は＄がいっぱい出てきて、見た目がゴジャゴジャになり、良く分からないことになりますから。そして、コピーでずれるということもありませんから、これも大きなメリットです。
このようにとても 便利 で 使いやす く、何よりも 数式が シンプル で 見やすく 分かりやすい のです。
この例ではそこまで複雑な式ではないのであまり実感が湧かないかもしれませんが、絶対参照などで＄表示が何個も入ってくるような場合では式が複雑になってしまい、うわーっ！となりますしミスの原因になってしまいます。
そして 配列式はどれか1つのセルを 部分的に修正することができません。ということは、部分的に修正したシートを再利用して失敗してしまう危険が無いのです。ちょくちょく修正する必要がある場合は、不便ですが、逆に考えると、これはエクセルシートを再利用する側からすると大きいメリットになります。 誤って数式を壊す危険が無い わけですからね。
ただこれは便利でもあり不便でもあります。行を増やしたいとか減らしたいといった配列が壊れる修正は出来ないのでそういう運用が頻繁に起こる表では不便かもしれません。
納品書や請求書のように様式が決まっていて行数も変わることがないというときには便利だと思います。
スピル で計算する
そして次に スピル です。office365ではこの配列式のような便利さに追加して[Ctrl]+[Shift]+[Enter]のような入力の不便さを排除したスピルという機能が追加されました。
入力方法がかなり強化されたので、シンプルな操作で便利な機能を享受できる優れものだと思います。
金額の列の一番上のセルを選んで＝単価1:単価5*数量1:数量5と入力し普通に[Enter]を押すと数式の枠が溢れて下に追加され結果が表示されます。
一部のセルだけを値を上書きするとスピルを入力した先頭のセルが#SPILL!と表示されますから、スピルも配列式同様で修正は出来ないということになります。
ただ残念なのは365からしか使えないということ。スピルの機能を使うと今まで絶対参照で対応していたことや、セルのコピーで作っていた計算の表やら検索の表やらが簡単に作れる様になります。
SUMIFS 関数のクロス集計表で 配列式 とスピルを比較する
先までの例では絶対参照の＄を使わないものでしたが、最後に絶対参照との比較をできる例でやってみます。配列式でもスピルでも絶対参照は使う必要がありません。
まず今までのように式のコピーを使った集計表です。
左側に売上のデータが有ります。その月に何が単価いくらで何個売れたかのリストになっています。
このリストから、製品別×売上月のクロス集計表をSUMIFS関数を使って右側に作成しています。
このクロス集計表の作り方はこちらの記事で紹介していますのでそちらを参照ください。
上部の数式入力の欄を見ていただければ分かりますが、かなり複雑な式になります。絶対参照の＄の付け方にかなり神経を使います。
当然セルの数式は全て違う計算式が入っています。もしどこかを特別に修正したときは、本人でも後では絶対にわかりません。
特に他の担当が書いた数式で絶対参照があるものは非常に頭を絞ります。
今度はスピルで書いた例です。
配列式でもスピルほどではないですが、シンプルな数式で作ることが出来ます。
配列数式で作る場合は、H2に数式を入力する前にH2:J6を範囲選択しておいてからスピルの時と同じ数式を入力し、[Enter]のところで[Ctrl]+[Shift]+[Enter]を押せば同じ結果が得られます。
配列数式を削除するときは、その範囲全体を範囲選択してから[Delete]を押せばできます。
一部を削除しようとしても、 「 配列の一部を変更することは出来ません 」 、となって出来ませんから注意してください。
以上です
コメント