スピル配列
- 本章で用いる実習用ファイル
Excelなどデータを処理する環境(処理系)は一つの変数(セルなど)で単一のデータだけで無く、複数のデータを同時に扱うデータ構造(配列)を持っている。Excelにおいては配列とはこれまで使ってきた複数セルを含む「範囲」だけではなく、複数のデータを1つのセルで扱うこともできる、そのようなデータ構造のことである。これまでの数式の結果や関数の戻り値は一つの値しか扱えなかった。だから例えば九九表の処理を行うには81個の数式が必要となる。これは効率が悪い。配列を使うと数式の結果や関数の戻り値でも複数のデータを扱える。
ただExcelの従来の配列は使い勝手はあまり良くなく、それほど普及はしなかった。「このままではExcelは駄目だ!」とMicrosoftが思ったかどうなのかはわからないが、これまでのExcelの配列の使い勝手の悪さを劇的に解消する新機能が登場した。新機能なので、使えるExcelのバージョンは限られている(Excel 2021以降)。Microsoft Office365でExcelを使っていれば、この新機能が使える(はず)。しかし旧来のバンドル版Officeを使っている人の中には使えない人もいる。ちなみに組織のPCはバージョンアップがそれほど頻繁にはできない(組織でシステムを運用するというのはそういうこと)関係上、新機能は使えない可能性が高い。
というわけで全員が確実に使える機能というわけではないが、しかしこの新機能、「筋」が非常に良い。確実に今後のスタンダードになる。やや先走り感はあるが、使える環境にある人(Microsoft Office365ユーザ)はしっかりものにしておこう。
スピル配列への道
- 相対参照とオートフィル
-
下記表で「金額」を求めるためには「個々の単価*個々の個数」からなる5つの数式が必要となる。オートフィルをつかえばセルC2に1つ数式を入力すればあとは自動的に入力させられるとは言っても、入力される数式は5つなのである。
A B C D 1 単価 個数 金額 2 150 3 =A2*B2 3 120 12 4 210 4 5 320 25 6 190 6 7 セル[C2]に入力されている数式を変更しても、残りの数式は元のままである。(オートフィルを用いて)改めて入力し直さなければならない。
- 静的配列(Static Array)
-
配列では全く考え方が変わる。「単価一式*個数一式」で5つの「金額」を一気に計算する。
A B C D 1 単価 個数 金額 2 150 3 =A2:A6*B2:B6 3 120 12 4 210 4 5 320 25 6 190 6 7 - セルC2:C6をアクティブセルにする
- 単価セルの範囲A2:A6×個数セルの範囲B2:B6の計算式を立てる
- Ctrl + Shift + Enterキーを押す
一つの数式で全ての計算ができた。
配列の中身 1 2 3 4 5 A2*B2
450A3*B3
1440A4*B4
840A5*B5
8000A6*B6
1140この5つの値がC2:C6に順に展開される。
これで入力されるのがExcelの(元からある)配列数式(Legacy Array Formulas)である。
基本的な考え方はエレガントなのだが、実用上はあまり美しくない。まず入力からしていけない。「Ctrl + Shift + Enterキーを押す」ってなに?さらにこれで入力される配列数式は意固地で柔軟性がとんと無い。Excelの従来からの配列は静的配列(Static Array)または固定長配列(Fixed-length Array)と呼ばれ、配列の長さ(配列の中に入っているデータの個数)を変えることができない。
例えばこの表から5行目にデータを増やしたくなったとする。「行の挿入」機能を用いて5行目に挿入してみよう。
というアラートが出る。「行の削除」機能を用いても同じ(長さを変えられない配列=固定長配列なのだから、当然なのだが)。このアラート、何かに付けて出てくる。アラートはいちいち「OK」しなければならない。ストレスMAXである。
筋は良いのだが柔軟性が欠けるのが問題。この配列の使い勝手の悪さを根本的にたたき直したのがスピル配列(Spilled Array)なのである。スピル配列は動的配列(Dynamic Array)・あるいは可変長配列(Variable-length Array)をExcelで実装した形態である。
従来の配列はデータの長さを変える(データを増減させる)ことができなかった。スピル配列はそれができる。おまけに入力も簡単、変なキーアサインを覚える必要が無い。スゴイ!素敵!カッコイイ!
- スピル配列(Spilled Array)(Excel2019×)
-
ここからの操作は使用しているExcelのバージョンによってできたりできなかったりする(できているように見えても、実は違う動きをしていたりする)。Office 365ユーザなら問題なく使えるはずだが、一応チェックしておこう。
A B C D 1 単価 個数 金額 2 150 3 =A2:A6*B2:B6 3 120 12 4 210 4 5 320 25 6 190 6 7 - セルC2をアクティブセルにする
- 単価セルの範囲A2:A6×個数セルの範囲B2:B6の計算式を立てる
- Enterキーを押す
Excel2021以降ならC2:C6に正しい計算結果が出力される。
C2に入力した数式は5個の数字を答えに持つ(数値配列)。
配列の中身 1 2 3 4 5 A2*B2
450A3*B3
1440A4*B4
840A5*B5
8000A6*B6
1140当然この5つの数値は一つのセル(C2)には入りきらない。そのこぼれた(Spilled)数値をそこから連なるセルに出力するのがスピル配列(Spilled Array)である。
この操作でC2にしか計算結果が出力されないとあなたのExcelはスピル配列には対応していない。Excel Onlineが対応しているのでそちらを用いる。
以後、スピル配列が使えることを前提で、話を進める。
データの増減
データの長さを増減してみる。例えば5行目で「行の挿入」を行ってみよう。
A B C D 1 単価 個数 金額 2 150 3 450 3 120 12 1440 4 210 4 840 5 0 6 320 25 8000 7 190 6 1140 8 セルA5,セルB5に適当な数値を入力すると、C5にはA5*B5の結果が出力される(従来の相対参照数式だとC5に新たに数式を入力しなくてはならない、オートフィルを用いるにせよ)。
A B C D 1 単価 個数 金額 2 150 3 450 3 120 12 1440 4 210 4 840 5 100 50 5000 6 320 25 8000 7 190 6 1140 8 絶対参照的な
従来の「絶対参照」にも対応する。
A B C D E F G 1 日付 曜日 時間 支給額 時給 2 10月11日 金 4.5 1000 3 10月12日 土 6 4 10月13日 日 3 5 10月14日 月 5.5 6 10月15日 火 3.5 7 10月16日 水 6 8 10月17日 木 1 9 日々の「支給額」は「時間」×「時給」である。
A B C D E F G 1 日付 曜日 時間 支給額 時給 2 10月11日 金 4.5 =C2:C8*F2 1000 3 10月12日 土 6 4 10月13日 日 3 5 10月14日 月 5.5 6 10月15日 火 3.5 7 10月16日 水 6 8 10月17日 木 1 9 数式をコピーしてセル位置を変えるわけではないので、時給F2への参照も不変である。つまり絶対参照にする必要が無い(しても構わない)。
あるいは式を全部絶対参照で立てることも可能である。むしろその方が正しいとも言える。時間はC2:C8に入力されているのであり、時給はF2に入力されていて、どこのセルから参照しようとそれは不変だからである。
A B C D E F G 1 日付 曜日 時間 支給額 時給 2 10月11日 金 4.5 =$C$2:$C$8*$F$2 1000 3 10月12日 土 6 4 10月13日 日 3 5 10月14日 月 5.5 6 10月15日 火 3.5 7 10月16日 水 6 8 10月17日 木 1 9 このように式を全部絶対参照で立てることができる、というのは大きな意味がある。なぜか?その説明は次に持ち越す(謎を残して…)。
配列の集計
個数分の合計金額を求めたいとする。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 商品 | 価格 | 個数 | |
| 2 | Camellia S9 | ¥65,000 | 3 | |
| 3 | Magnolia A5 | ¥24,000 | 2 | |
| 4 | juice P9 | ¥18,000 | 2 | |
| 5 | Ange E9 | ¥15,000 | 4 | |
| 6 | ||||
| 7 | 合計 | |||
| 8 |
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 商品 | 価格 | 個数 | |
| 2 | Camellia S9 | ¥65,000 | 3 | |
| 3 | Magnolia A5 | ¥24,000 | 2 | |
| 4 | juice P9 | ¥18,000 | 2 | |
| 5 | Ange E9 | ¥15,000 | 4 | |
| 6 | ||||
| 7 | 合計 | |||
| 8 |
B2:B5 * C2:C5で4つの数値
| 1 | 2 | 3 | 4 | B2*C2 195000 | B3*C3 48000 | B4*C4 36000 | B5*C5 60000 |
|---|---|---|---|
が配列として得られる。これまではその4つの数値を4つのセルに出力していたが、今回はこれをそのままSUM関数の引数にする。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 商品 | 価格 | 個数 | |
| 2 | Camellia S9 | ¥65,000 | 3 | |
| 3 | Magnolia A5 | ¥24,000 | 2 | |
| 4 | juice P9 | ¥18,000 | 2 | |
| 5 | Ange E9 | ¥15,000 | 4 | |
| 6 | ||||
| 7 | 合計 | =SUM(B2:B5*C2:C5) | ||
| 8 |
スピル機能が有効なときは単にEnterで確定すれば正しい結果が得られる(有効でない場合、従来の「配列」を用いるため、Ctrl + Shift + Enterで確定する)。
配列として保持された4つの数値{195000,48000,36000,60000}をSUMで合計した結果がセルB7に出力される。
- SUM(配列1 2 3 4)
-
1 2 3 4 195000 48000 36000 60000
- SUMやAVERAGEなど引数を集計する関数は配列(複数の値を保持)を一つの値にまとめる。
- 四則演算と同様、配列に含まれる値を一つ一つ処理し、複数の値を返す関数もある(ex. IF関数)。
課題
下表の
のセルに数式を立てて、スピル配列を用いて
を埋め、表を完成させよ(オートフィルは用いない)。
※スピル配列が使えないバージョンの場合、
が埋まらない。
課題:九九表(
形式)
セル[B2]のみに数式を立てて、九九表を完成させよ。
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | ||
| 2 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | ||
| 3 | 2 | 2 | 4 | 6 | 8 | 10 | 12 | 14 | 16 | 18 | |
| 4 | 3 | 3 | 6 | 9 | 12 | 15 | 18 | 21 | 24 | 27 | |
| 5 | 4 | 4 | 8 | 12 | 16 | 20 | 24 | 28 | 32 | 36 | |
| 6 | 5 | 5 | 10 | 15 | 20 | 25 | 30 | 35 | 40 | 45 | |
| 7 | 6 | 6 | 12 | 18 | 24 | 30 | 36 | 42 | 48 | 54 | |
| 8 | 7 | 7 | 14 | 21 | 28 | 35 | 42 | 49 | 56 | 63 | |
| 9 | 8 | 8 | 16 | 24 | 32 | 40 | 48 | 56 | 64 | 72 | |
| 10 | 9 | 9 | 18 | 27 | 36 | 45 | 54 | 63 | 72 | 81 | |
| 11 |
課題:家計費(形式)
1世帯あたり1年間の支出(全世帯)を示した表である。
各年ごとの品目別の構成比と2000年時に比して2019年の消費金額の増減率を求めよ。
※数式は3つしか入力してはならない(オートフィルも×)。
| A | B | C | D | E | F | F | |
|---|---|---|---|---|---|---|---|
| 1 | 品目 | 2000年(円) | 2000年構成比(%) | 2019年(円) | 2019年構成比(%) | 増減比 | |
| 2 | 食料費 | 973,680 | 965,536 | ||||
| 3 | 住居費 | 246,334 | 6.47% | 205,235 | 5.83% | -16.68% | |
| 4 | 光熱・水道費 | 259,546 | 6.82% | 263,408 | 7.48% | 1.49% | |
| 5 | 家具・家事用品費 | 139,148 | 3.65% | 140,598 | 3.99% | 1.04% | |
| 6 | 被服および履き物費 | 206,742 | 5.43% | 135,677 | 3.85% | -34.37% | |
| 7 | 保健医療費 | 137,732 | 3.62% | 168,124 | 4.78% | 22.07% | |
| 8 | 交通・通信費 | 438,748 | 11.52% | 525,768 | 14.93% | 19.83% | |
| 9 | 教育費 | 167,089 | 4.39% | 137,937 | 3.92% | -17.45% | |
| 10 | 教養娯楽費 | 403,055 | 10.58% | 368,150 | 10.46% | -8.66% | |
| 11 | その他消費支出 | 835,862 | 21.95% | 610,115 | 17.33% | -27.01% | |
| 12 |
課題:納品書(形式)
4つの商品を「個数」分購入し、消費税を加えた合計金額を求めよ。
※セル[B8]以外に数式を立ててはならない。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 商品 | 単価 | 個数 | |
| 2 | コレクション生写真 | ¥1,000 | 10 | |
| 3 | BDフォトスタンド | ¥3,600 | 2 | |
| 4 | デザインマスク | ¥1,200 | 3 | |
| 5 | DVDマガジン | ¥5,000 | 1 | |
| 6 | ||||
| 7 | 消費税率 | 10% | ||
| 8 | 合計 | |||
| 9 |
課題:ホテル宿泊料金(形式)
AHOホテルの宿泊料金は部屋の種類と宿泊日によって金額が決まる。すべての宿泊料金を求めよ。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 平日 | 休日 | 休日前日 | ||
| 2 | シングル | 7,000 | 8,400 | ||
| 3 | ツイン | 9,000 | 10,350 | 10,800 | |
| 4 | ダブル | 10,000 | 11,500 | 12,000 | |
| 5 | デラックスツイン | 25,000 | 28,750 | 30,000 | |
| 6 | |||||
| 7 | 休日 | 休日前日 | |||
| 8 | 割増率 | 15% | 20% | ||
| 9 |
課題:ツアー料金(形式)
キャメリアツアーズの河口湖ツアーでは基本料金とオプション料金を組み合わせてツアー料金が決まる。すべての料金を求めよ。
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | OP料金 | シングル | ツイン | スイート | ||
| 2 | 10,000 | 22,000 | 38,000 | |||
| 3 | 食事なし | 0 | 272,000 | 288,000 | ||
| 4 | 朝食付き | 12,000 | 272,0000 | 284,000 | 300,000 | |
| 5 | 全食事付き | 45,000 | 305,000 | 317,000 | 333,000 | |
| 6 | 観光付き | 35,000 | 295,000 | 307,000 | 323,000 | |
| 7 | 全食事・観光付き | 70,000 | 330,000 | 342,000 | 358,000 | |
| 8 | ||||||
| 9 | 基本料金 | 250,000 | ||||
| 10 | ||||||
課題:点数換算(形式)
満点が異なる試験結果を100点満点換算せよ。換算後の点数は小数点以下第1位まで表示せよ。
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | 素点 | 100点満点換算 | ||||||
| 2 | 氏名 | 国語 | 数学 | 英語 | 国語 | 数学 | 英語 | |
| 3 | 高橋 理子 | 180 | 121 | 69 | 80.7 | 76.7 | ||
| 4 | 新垣 希空 | 164 | 75 | 46 | 77.0 | 50.0 | 51.1 | |
| 5 | 亀井 安美 | 153 | 135 | 71 | 76.5 | 90.0 | 78.9 | |
| 6 | 道重 ゆめの | 132 | 145 | 55 | 66.0 | 96.7 | 61.1 | |
| 7 | 田中 樹々 | 159 | 127 | 38 | 79.5 | 84.7 | 42.2 | |
| 8 | 満点 | 200 | 150 | 90 | 100 | |||
| 9 | ||||||||