-
Home »
-
エクセル関数一覧表 »
-
スピルを利用した計算の例
作成:2020/2/22,
更新:2023/3/15
- スピルを使った配列を操作するような数式のことを動的配列数式と呼ぶようです。
一方、従来の配列数式は、[Ctr]+[Shift]+[Enter]キーで数式を確定することから、通称「CSE」と呼ばれているようです。
この辺のことは 動的配列数式と、こぼれた配列動作 をご覧ください。
- Excel for Office365 (Excel2016:サブスクリプション版) でスピルが利用できるようになりました
Office365は2020/4/22からは Microsoft365となっています。
ちなみに、このスピル機能はExcel2016(永続ライセンス版),Excel2019(永続ライセンス版)では使用できませんので、ご注意ください。
- なお、2021/10/5頃から市販されている Excel2021(永続ライセンス版)でも使用できるようです。
スピルを利用した計算の例 topへ
- セル範囲とセル範囲を掛け合わせた計算例です。
D3セルに =B3:B5*C3:C5 と入力しました。
スピルの機能によって、数式を入力したD4セルに加えて、D6セルまで計算結果が表示されます。
↓
その範囲 D4:D6は青色の枠で囲まれ、D4セルを選択すると、薄いグレーで数式が表示されます。
スピルの機能で作成されたセル範囲(D4:D5)では数式が薄いグレーで表示され、ゴーストと呼ぶようです。
- 従来の配列数式では、D3:D5セルを選択して、数式バーで =B3:B5*C3:C5 と入力しています。
↓
[Ctrl]+[Shift]+[Enter]キーで数式の入力を確定します。
つまり、上記のスピルの機能は数式を入力するセルをあらかじめ選択して、数式を入力せずに、勝手に必要なセル範囲に数式があふれて入力されるイメージかと思います。
- C10セルに =B10:B12*C9:E9 と入力しました。
C10:E12に計算結果が表示されました。
- 従来の配列数式では、C10:E12セルを選択して、=B10:B12*C9:E9 と入力して、[Ctrl]+[Shift]+[Enter]キーで数式の入力を確定します。
- 掛け算の九九がダイナミックに見えるかもしれません。
C14セルに =B14:B22*C13:K13 と入力しているだけです。
- 下図のように、D3セルにSpillを利用した数式を入力したところ、#SPILL! とエラーが表示されました。
- 原因は、Spill(スピル)機能で、数式が拡張(あふれる)されるセルにすでに値が入力されているときにエラーとなります。
上記の図では、D4セルに「12」と入力されているのがエラーの原因です。
#SPILL! エラーを解除するには、D4セルをクリアします。D4セルの値「12」を[Delete]キーでクリアします。
- C4セルに =RANK(A4:A8,A4:A8) と入力して、降順で順位を求めています。
- Spillが使えない場合は、配列数式を利用できます。
C4:C8を選択して、C4セルに =RANK(A4:A8,A4:A8) と入力して、[Ctrl]+[Shift]+[Enter]キーで数式の入力を確定します。
- F3セルに =UNIQUE(C3:C17) と入力して、UNIQUE関数を使って重複のない品種を取り出します。
- 販売数の合計をSUMIF関数で条件付きで合計を計算する例です。
G3セルには =SUMIF(C3:C17,F3#,D3:D17) と入力しています。
従来と異なるのは、引数の検索条件に F3# とセル範囲を指定しています。これはここでは F3:F5 と同じになります
(範囲のうしろにこぼれた範囲演算子 (#) を指定して、スピル範囲全体を参照することができます。)
スピルの機能によって、数式を入力したG3セルに加えて、G5セルまで計算結果が表示されます。
その範囲 G3:G5は青色の枠で囲まれ、G4、G5セルを選択すると、薄いグレーで数式が表示されます。
- ちなみに、従来は重複しない品種を「重複の削除」などの機能を使って書き出します。
数式の書き方は G4セルに =SUMIF(C$3:$C$17,F3,$D$3:$D$17) と入力します。
そして、G4セルをオートフィルで、G6セルまで数式をコピーします。
- または、配列数式では G4:G6セルを選択して、=SUMIF($C$3:$C$17,F3:F5,$D$3:$D$17) と入力して、[Ctrl]+[Shift]+[Enter]キーで数式の入力を確定します。
- F9セルには =UNIQUE(C3:C17) と入力して、重複のない品種を取り出します。
G8セルには =TRANSPOSE(UNIQUE(B3:B17)) として、UNIQUE関数で重複のない月を取り出し、TRANSPOSE関数で列方向へ展開します。
- SUMIFS関数を使った、複数条件での合計の計算例です。
G9セルに =SUMIFS(D3:D17,C3:C17,F9#,B3:B17,G8#) と入力しました。
条件1の F9# と条件2の G8# は範囲演算子 (#) を指定して、スピル範囲全体を参照しています。
計算結果が G10:I12 セルに表示されました。
- テーブルでスピルを使用すると、非常に便利になります。
データリストをテーブルに変換しています。テーブル名は テーブル1 となっています。
G9セルの数式は =SUMIFS(テーブル1[販売数],テーブル1[品種],F9#,テーブル1[月],G8#) とします。
- B18:D19に新たなデータを追加します。
クロス集計表に新たな品種「マンゴー」と月「4月」が自動で追加され、集計も自動で再計算されました。
- 従来の数式では G10セルに =SUMIFS($D$3:$D$17,$C$3:$C$17,$F10,$B$3:$B$17,G$9) と入力して、その数式G12セルまでコピーし、さらに、G10:G12セルを選択後、オートフィルでI10:I10セルまでコピーする必要があります。
- または、配列数式の場合は G10:I12セルを選択して、=SUMIFS($D$3:$D$17,$C$3:$C$17,F10:F12,$B$3:$B$17,G9:I9) と入力して、[Ctrl]+[Shift]+[Enter]キーで数式の入力を確定します。
- VLOOKUP関数を使った、複数条件での合計の計算例です。
C10セルに =VLOOKUP(B10:B12,B3:D5,2,FALSE) と入力しています。
- D10セル =VLOOKUP(B10:B12,B3:D5,3,FALSE) と入力しています。
- 従来の配列数式では C10:C12セルを選択して、=VLOOKUP(B10:B12,$B$3:$C$5,2,FALSE) と入力して、[Ctrl]+[Shift]+[Enter]キーで数式の入力を確定します。
XLOOKUP関数を使った場合
- XLOOKUP関数を使った場合
C10セルに数式 =XLOOKUP(B10:B12,B3:B5,C3:C5) を入力します。
D10セルの数式 =XLOOKUP(B10:B12,B3:B5,D3:D5) を入力します。
- C16セルの数式 =XLOOKUP(B16,$B$3:$B$5,$C$3:$D$5)
C16セルの数式を下方向へ、オートフィルでコピーします。または、C16:C18セルを選択して、[Ctrl]+[D]で数式をコピーします。
- COUNTIF関数を使った、カウントする計算例です。
G4セルに =COUNTIF($C$3:$C$17,F4:F6) と入力しています。
- 従来の配列数式では G4:G6セルを選択して、=COUNTIF($C$3:$C$17,F4:F6) と入力して、[Ctrl]+[Shift]+[Enter]キーで数式の入力を確定します。
- COUNTIFS関数を使った、複数条件でカウントする計算例です。
G10セルに =COUNTIFS($C$3:$C$17,F10:F12,$B$3:$B$17,G9:I9) と入力しています。
- 従来の配列数式では G10:I12セルを選択して、=COUNTIFS($C$3:$C$17,F10:F12,$B$3:$B$17,G9:I9) と入力して、[Ctrl]+[Shift]+[Enter]キーで数式の入力を確定します。
スポンサードリンク
よねさんのWordとExcelの小部屋 »
エクセル関数一覧表 » スピルを利用した計算の例
PageViewCounter
Since2006/2/27