エクセル スピル。 Excel スピル(動的配列数式)の使い方とメリット・デメリット

【Excel】スピルで参照範囲を動的に設定する方法

こちらの記事では新しいExcelで追加になる計算方法、「スピル」について解説しました。 とてもインパクトのある機能でして、多くの皆さんにご覧いただき知っていたけて、とてもうれしかったです。 ただ、インパクトのある機能ではあるものの、どのようなケースで使えるか、具体的にどんな業務に使えるかということまではなかなか繋がりにくいと思います。 夢でまでスピルが出てくるほど考えていまして、そんな中で、これはスピルでしかできないという有効活用方法を見つけましたので、お知らせいたします。 具体的には計算式作成の時短・効率化に役立ちます。 時間と単価と基本料金の表 今回紹介するケースはこのような、貸し部屋の料金表のケースです。 ４つの表があります。 それぞれの表の左上は「基本料金」です。 短時間使ってもこの料金はかかります。 それぞれの横軸は「単価」、縦軸は「利用時間」となっています。 従来での効率的な計算プロセス 従来のExcelで最もこの表の黄色のセル範囲に計算する方法は、セルB2に次の計算式を作成します。 ということで、最初から計算式を作るか、上記の計算式を一度コピーしてその表にあわせて範囲を指定し直すか、そしてその改造した計算式を範囲にコピーします。 これが４つの表すべてで行う必要があります。 スピル使ったら？ スピルの場合は、セルB1に絶対参照を意識しない次の計算式を入力します。 後は、セルB2の計算式をセルをB7、G2、G7の各セルにコピーするだけです。 スピルでの位置関係が完全にコピーされます。 スピルでの問題点 それでもまだスピルでは問題があります。 今回はすべて同じ範囲の大きさの表でしたが、それぞれバラバラのケースもあるでしょう。 その場合では、残念ながらすべての範囲でスピル式を作成する必要があります。 ここ、OFFSET関数などでうまくできる方法があれば知りたいところです。 あらかじめ表の上にでも縦横の大きさが計算されるような関数を仕込んでおいて、そこをOFFSET関数で処理して範囲を決めてもいいのかもしれないです。 次のような計算式もアリかもしれないです。 もう少し検討が必要です。 もしその方法も使えない場合でも、スピルを使わない方法よりは使った方が効率化できると思います。 もっと応用 下のような表を作り、他の営業部のデータを作る時は、従来では、セルC3に計算式を作成し、セルC3からE4までの範囲にコピーして使い、そのシートをコピーし、中のパラメータを変更し、コピーしたシートの表範囲を切り取り、コピー前の表の6行目以降に貼り付ける、といった手順が最も早いかと思います。 スピルであれば、セルC3にスピル計算式を作成し、表全体を6行目以降に貼り付けてパラメータを変更するという流れだけで作業ができます。

次の

Excelテクニック and MS

の SPILL! エラーについて紹介します． SPILL! エラー にスピル機能が導入されたことにより， SPILL! エラーを見た人もいると思います． なお， Onlineでは スピル! と日本語で表示されるようです． （他，参考ページ: ） これはスピル機能に伴って導入された新しい種類のエラーで， 主にスピル先のセルに既に値が入っている時に発生するエラーです． 例 スピルが起こる範囲の一部であるB3セルに文字列が入っているため，エラーになっています． 本来スピルが起こる範囲が点線で囲まれていて，わかるようになっています． デスクトップ版では，エラーで出てくるメニューに「阻害しているセルを選択」という項目が表示されます． これを選択すると，スピルを阻害しているセルが選択されます． スピルを阻害しているセルの値を削除すると，即座にスピルが正常に行われます． その他の SPILL! エラーの原因 実は他にも SPILL! エラーが起こる原因があります． スピルのサイズが決まらない 再計算のたびに結果の配列のサイズが変わるなど，スピルのサイズが決まらない場合にも SPILL! エラーが発生します． この例では，乱数で返る配列のサイズが変わるので，エラーになっています． によると，通常RAND, RANDARRAY, RANDBETWEEN関数が関わる式でエラーが発生する可能性がありということです．（なお，OFFSET, INDIRECT, TODAY関数はだが計算のパスごとに異なる値は返さないので大丈夫とのこと） ワークシートの端を超えている スピルの範囲がワークシートの最大の行数・列数を超えていると SPILL! エラーになります． なお，テーブルを参照した結果がスピルすることはできます． これはむしろがウリにしている使い方です． メモリ不足 めったに無いと思いますが，メモリ不足になると SPILL! エラーになります． 指定したセル範囲を小さくして試してくださいということです． スピル範囲に結合セルがある スピルする範囲に結合セルがあると， SPILL! エラーになります． この例では，B3:C3セルが結合されているので，エラーになっています． エラーのメニューから「阻害しているセルを選択」を選んで，問題となっている結合セルにジャンプできます． まあ，やはり結合セルは様々な面倒ごとの元なので可能な限り避けるべきですね． CALC! エラー 動的配列数式に伴って， CALC! エラーというのも新しく登場したようです． 下記では，FILTER関数の結果が空の配列になっているため， CALC! エラーが返っています． 公式のドキュメントは，下記にあります． ただ，あげられていた3つの例のうち，2019年12月19日現在， Onlineでエラーが再現したのは空の配列だけだったので， CALC! エラーは無くなっていく方向なのかもしれません． ちなみに，他の例:• エラーになった まとめ スピルの SPILL!

次の

Excel スピル ``#SPILL!``エラー

2020. 17 Excel関数 ヒロユキ 【Excel】スピルで参照範囲を動的に設定する方法 Office365に新規に追加された機能「スピル」ですが、どのような用途で使っていますか？ 自分はXLOOKUPやXMATCHなどの汎用的に使える便利関数はもちろん使うし、FILTERを使用して重複削除、TRANSPOSEで縦横変換とかもやっています。 ただ勉強でやっている感が強く、別に今までの方法で良いという気もしています。 おそらく業務では、 フィルターやソートの代わりで使うことが多くなるかなというイメージです。 ピボットテーブルを使用しない場合、表をカスタマイズするには、元の表に対してフィルターやソートをかけてあげる必要がありました。 そしてフィルター条件に関数などを使えないため、高度な絞り込みは出来ませんでした。 あと縦 行 のフィルターは強いのですが、横 列 のフィルターにめちゃくちゃ弱い。 また、ソートをかけると元のデータの並びが完全に変わってしまうという欠点があります。 しかしスピルを使用することで、元のデータを保持したまま見たいデータだけを表示したり集計したりすることが出来ます。 スピルで表の終端を参照範囲とする スピルでフィルターを掛ける場合の式を考えてみます。 ちょっと前にも出した下記のフィルターの動作をスピルで表現してみます。 おすすめの方法は、 参照したい表に名前をつけてしまうということです。 まず、参照元の表を選択します。 そして、テーブルとして書式設定を選択します。 その後で書式を選んで、テーブルをクリックするとテーブルデザインタブが新しく出現します。 ここでテーブルの名前を変更することが出来ます。 仮にテーブル名を「table」とした場合、スピルを以下のように設定します。 また、 テーブル名 [カラム名]とすることで、その列にある値を配列で返してくれるので、これをFILTERの条件に使用しています。 テーブルの特性として、連続した範囲はすべてテーブルとみなしてくれるので、 行を追加すると自動で範囲が拡張されます。 これで元の表の大きさが変更になっても、自動でスピルの参照先に適用することが出来ます。 その他に使いそうなところだと… テーブル名 [ 見出し]とすることで、見出しの配列を得ることが出来ます。 これをスピルで作成した表の見出しにそのまま使えば、元の表の見出しが変更になっても自動で変更してくれます。 列のフィルターの列名を動的に取得する 上記例で「No」という列はシステム的な列なので、スピルで見る表上は特に必要ないとします。 その場合、横（列）のフィルターで消せるのがスピルの大きなメリットですね。 スピルの式を以下に設定します。

次の