愛しのエクセリーヌその３

Michael T. Kye
メールをお送り頂く場合は To: または本文に
“マイケル” の文字を入れてください
そうでないとスパム・メールとみなされて、受信されません


1999年 2月 6日（土）第11回

Excel の表とグラフを Word に持ってくる
　今日は以前のレッスンに関することを色々と聞いたりしてたし、途中で拙者の使ってるパソコンが調子悪くなったりしたのでいつもほど課題が進まなかった。
　進まなかった分、このページを書くのが楽だ。(笑)


　いよいよ Word との連携だ。内容は前回 Excel のみで作ったのとほとんど同じようなもの。ただし、文章の部分は Word で作って、表やグラフは Excel から持ってくる。
　たぶん、文書の作り方としては、こっちの方がオーソドックスだと思う。Excel で長い文章を書いて範囲指定して割り付けると、字下げが無視されてしまったりするから、やはり文章は Word で作る方がいいかも知れない。

　教室では Word の練習ではないからか、文章は既にタイプされてたし、Word でテキスト ボックスを作れ、とも書いてなかった。
　ま、テキストボックスの作り方は、両方のアプリで共通だけどね。


　Word への
　Excel の表の貼り付け

　Word の文書に Excel の表を貼り付けるんだから、両方のファイルを開かなければならないのは当然だ。どちらを先に開いてもいいんだけど、とりあえず Word で文章を入力することにしよう。
　文章の部分を書き終えたら、以前に作った表の入ってる Excel のブックを開き
  1. 目的のワークシートをアクティブにする
  2. 貼り付ける部分を範囲指定して“コピー”を選ぶ
  3. Word のタスクボタンを押して、さっき入力した文書を表示させる
  4. 貼り付ける部分をクリックする
  5. ［編集(E) - 形式を指定して貼り付け(S)...］を選択しダイアログ ボックスを出す
  6. ダイアログ ボックスから“Microsoft Excel ワークシート　オブジェクト”を選んで
    ［ＯＫ］を押す
  7. 貼り付けられた表を右クリックし、メニューから［オブジェクトの書式設定(O)...］を選ぶ
  8. ダイアログ ボックスから［文字列の折り返し］タブを選び“なし(N)”を選択して
    ［ＯＫ］を押す
  9. そうすると、回り込んでた文字がレイアウト枠の下に移動するので、表をドラッグして適当な位置に移動する

　Word への Excel の
　　グラフの貼り付け

　手順は表の貼り付けとほとんど変わらない。ただ上記の 6. のところで“図”を選ぶ。表が大きい場合は次のページに貼り付けられてしまうが、慌てなくていい。(笑)　サイズを少し小さくしてやればいいのだ。


　Word からの Excel の
　　表のフォーマット変更

　実は Word から（一太郎から、でもだけど） Excel の表やグラフの変更が出来るのだ。試しに表をオートフォーマットでデザイン変更してみよう
  1. Word の中の表をダブルクリックする
  2. すると、表は下のようになり、よく見るとあ〜ら不思議、メニューやツールバーが Excel のものに変わってる
    Word の中の Excel の表
  3. B3 〜 G10 までが既に選ばれてるので［書式(O) - オートフォーマット(A)...］から表の書式を変更してみる。今回は“標準”にした
  4. 変更が終わったら、表以外の文章の部分をクリックする。そうしたら、メニューなどが元の Word のものに戻る




　データベース関数


　次に出て来たのがデータベース関数だ。リストの中から条件に合ったレコードを選び出し、指定したフィールドの合計や平均などを求める関数だ。

　データベース関数は色々あるらしいが、主なものは次の通り。
DSUM()　条件に合った対象フィールドの数値の合計
DAVERAGE()　条件に合った対象フィールドの数値の平均
DMAX()　条件に合った対象フィールドの数値の最大値
DMIN()　条件に合った対象フィールドの数値の最小値
DCOUNT()　条件に合った対象フィールドの数値データの個数
DCOUNTA()　条件に合った対象フィールドの空白以外のデータの個数
　ま、頭の "D" を取った普通の関数から、機能そのものは連想できると思う。次に DSUM() を例にとって書式を説明してみよう。

DSUM(データベース範囲，フィールド，条件範囲)
データベース範囲　データベースを構成するセル範囲
フィールド　計算対象となるフィールド*
条件範囲　条件が入力されているセル範囲
* フィールドの書き方は次のいずれでもいい。
・セルの番号　D3
・フィールド名　"光熱費"
・左からの列位置　4
　で、課題は次のようなのだ。例によって以前に作ったデータベースを使う。以前にオートフィルタの練習をやった、あの表だ。
データベース関数を使っての計算

　まず、セル H5 に 　合計を求めるのだから、さっきの DSUM 関数がそのまま使える。
DSUM(データベース範囲，フィールド，条件範囲)
上の表に当てはめてみよう。
データベース範囲　A3:E14
フィールド　C3、または "商品Ａ" あるいは 3
条件範囲　G4:G5
　条件やフィールド名の文字列はデータベースの中の文字列と一致している必要がある。例えば、データベースの方で“日　付”と、まん中にスペースが入ってたりしたら、条件やフィールド名を書く時も“日　付”と、スペースを入れなければならない。

　じゃ、H5 に入れる DSUM 関数を書いてみよう。手で書いてもいいし、マウスで範囲指定したりクリックしたりしてもいい。

　　　=DSUM(A3:E14,C3,G4:G5)

　しかし、ほかの部分の計算もデータベース範囲など共通の部分が多いので、絶対座標にしておいて、違ってる部分だけど変更してやった方が楽だと思う。
　だから僕は下のように全部絶対座標にした。・・・ちょっとみにくいなぁ。(笑)

　　　=DSUM($A$3:$E$14,$C$3,$G$4:$G$5)

　次は I5 だが 　H5 のセルの式との違いは、合計か平均か、の違いだけだ。だから H5 の式を“形式を選択して貼り付け”してやればいい。で、DSUM を DAVERAGE に変更してやればすむ。

　　　=DAVERAGE($A$3:$E$14,$C$3,$G$4:$G$5)

　J5 の式は、“商品Ｃ” の “最大” だから

　　　=DMAX($A$3:$E$14,$E$3,$G$4:$G$5)

　K5 の式は、“得意先” の “回数” を見なければならないから

　　　=DCOUNTA($A$3:$E$14,$B$3,$G$4:$G$5)

　結果はデータ関数を使用した計算結果このようになるはずだ。


　もう一度課題を見よう。

　下の表は日付が 1998/12/21 以降であることを条件に検索することになってる。つまり、条件も自分で入れるのだ。

　 G H I J K
　7　 条件 合計 平均 最少 回数
　8　 日　付 　商品Ａ　 　商品Ａ　 　商品Ｃ　 販売回数
　9　 >=1998/12/21 　　　　
　Ｇ列にこのように入れればいい。ただ、“日　付” と、まん中にスペースを入れるのを忘れないように。で、Ｈ列からＫ列まで見ると、上とほとんどそっくりだ。これはコピー＆ペーストしない手はない。(笑)
　早速、H5 から K5 までを H9 から K9 に “形式を選択して貼り付け” して、数式を貼り付けてやればいい。そして“条件範囲”の部分だけ書き換えれば楽勝だ。

　H9 のセルを例にとると

　　　=DSUM($A$3:$E$14,$C$3,$G$4:$G$5)
　　　　　　　　　　　↓
　　　=DSUM($A$3:$E$14,$C$3,$G$8:$G$9)

　あと、I9 から K9 のセルも同じ要領だ。これで完成…、おっとハマるところだった。J9 のセルは DMAX を DMIN に書き換える必要がある。(笑)

　結果はデータベース関数を使った計算その２これでいい…、ハズだ。




1999年 2月12日（金）第12回

　今日はデータベース関数の続きからだ。
データベース関数を使っての計算

　上のデータベース（表）から
  1. 1998年12月25日以前の「山本商店」ヘの販売高の合計
  2. 「山本商店」または「ごとう屋」への販売回数
  3. 大阪屋に販売した最後の日
  4. 1998/12/20〜1998/12/25 までの「商品Ｂ」の平均販売高
  5. 1998/12/20〜1998/12/25 までの「商品Ａ」と「商品Ｂ」の販売高合計
　これらの計算を、適当に表の空いたことろを使って計算しろ、って問題だ。罫線とか体裁は気にしなくていい、って書いてある。けど、「気にしなくていい」とか書かれると、気にしたくなるもんだ。(爆)

　まず、1. を考えてみよう。 　この二つの条件を満足しなければならない。だから AND だ。
　表に書く場合は、以前にやったとおり、二つの条件を横に並べて書くことになってた。
得意先日　付
　山本商店　　<=1998/12/25
条件テーブル
　2. は“「山本商店」または「ごとう屋」へ”だから、異なる行に…。一つづつ問題をやって行く手もあるけど、僕は１から５までの問題の条件範囲のテーブルみたいなものを作ることにした。
  1. 1998年12月25日以前の「山本商店」ヘ…　は、G14:H15
  2. 「山本商店」または「ごとう屋」へ…　は、G14:G17
  3. 大阪屋に販売した…　は、G18:G19
  4. 1998/12/20〜1998/12/25 までの…　は、G20:H21
  5. 1998/12/20〜1998/12/25 までの…　だから、G20:H21
　ここまでできたらあとは、データベース関数の

　　　　Dxxx(データベース範囲，フィールド，条件範囲）

を、一つづつ埋めてやればいいだけだから、もう出来上がったも同然だ。

　条件範囲以外は前回と同様だから、
  1. データベース範囲：$A$3:$E$14
  2. フィールド
　なお、フィールドの書き方はこれ以外にもあることは、前回書いた。

問題１〜５の計算結果

　結果は左のようになるはずだ。計算式だけど書いておこう。
  1. =DSUM($A$3:$E$14,$C$3,G14:H15)
  2. =DCOUNTA($A$3:$E$14,$B$3,G14:G17)
  3. =DMAX($A$3:$E$14,$A$3,G18:G19)
  4. =DAVERAGE($A$3:$E$14,$D$3,G20:H21)
  5. =DSUM($A$3:$E$14,$C$3,G20:H21)+DSUM($A$3:$E$14,$D$3,G20:H21)
　なんか文字化けみたいだな。(笑)

　この問題、こうして書くとすらすら行ったような感じだけど、とんでもなく時間を食った。(笑)　式はあってるはずなのに、結果がテキストの解答と合わないのだ。(^^;)
　で、何度も何度も式を見直したりしてた。結局、データベースのほうの数値の打ち間違え（インプットミス）だったんだけどね。(爆)　よけいなことで時間を食ってしまった。

　それと、最後の問題なんだけど、教室ではちょっと違う計算方法でやってた。
  1. K25 のセルに =DSUM($A$3:$E$14,$C$3,G20:H21)
  2. K26 のセルに =DSUM($A$3:$E$14,$D$3,G20:H21)
  3. で、5. の答えを出すセルに =SUM(K25,K26)
　二つの式を別々に計算して、あとでそれを合計してた。(笑)　セルをバッファ変数のように使ったんだ。式が複雑になるのを嫌う、プログラマとしてのクセだね。(^^;)
　あ、別にこんなの真似する必要ない。表が汚くなるしね。インストラクターはなんにも言わなかったけど、Excel としては最初の式のほうが正解だと思う。




　データテーブル機能

　データテーブル機能とは、変数を用意しておいて、それでを行う機能だ。速度を変えると到着時間がどうなるか、とか、利率が変わると預金の受取額とか、ローンの支払い額がどうなるかとか、そういう計算が出来る。

　 A B C D
1 　 　　 　　　　 　　　　
2 　 　　 　　 　　
3 　 　　1 　　 　　
4 　 　　2 　　 　　
5 　 　　3 　　 　　
6 　 　　4 　　 　　
7 　 　　5 　　 　　
8 　 　 　　 　　

　まず試してみよう。左のような表を作る。Ｂ列に入力した数字が変数になるわけだ。

　次に C2 のセルに =A1*10 と、 D2 に =A1*100 と入力する。

　「え？　A1 って空っぽじゃない」って？　そう、ここは空っぽだ。この表では、変数のセルを直接指定できないんで、A1 を代入セルとして使うんだ。そう、ちょうどバッファ変数みたいな感じだね。
　代入セルは別に A1 じゃなくても空いたセルならどこでもいい。

　 A B C D
1 　 　　 　　　　 　　　　
2 　 　　 0 0
3 　 　　1 　　 　　
4 　 　　2 　　 　　
5 　 　　3 　　 　　
6 　 　　4 　　 　　
7 　 　　5 　　 　　

　そうすると、今は A1 が空っぽだから、右のように C2 と D2 の値は "0" になる。

　いよいよデータテーブルの作成だ。
  1. Ｂ列の変数と２行目の計算式を含む範囲、B2 から D7 までを選択する
  2. 次に、メニューから［データ(D) - テーブル(T)...］を選択すると
  3. テーブルのダイアログ ボックス
  4. 上のようなダイアログ ボックスが出るので
    ［列の代入セル(C):］にカーソルを移し、A1 のセルをクリックする
  5. ［ＯＫ］をクリックすると、右下のように計算結果が出る
データテーブル機能のテスト
　ま、結果は一目瞭然だけど、C2 のセルに A1*10 って式が入ってるから

　　　A1 に 1 が代入されて、C3 に 1×10
　　　A1 に 2 が代入されて、C4 に 2×10
　　　…
　D2 のセルに A1*100 って式が入ってるから

　　　A1 に 1 が代入されて、D3 に 1×100
　　　A1 に 2 が代入されて、D4 に 2×100
　　　…

　こういう具合になる。

　ちなみに C3 から D7 のセルの中身は、全部 {=TABLE(,A1)} ってなってるはずだ。


　では、このデータテーブル機能を使って、フロッピー１枚に入ったデータをモデムで伝送する場合の所要時間を計算してみる。
　伝送速度を変えると、伝送時間がどう変化するかシミュレートするのだ。伝送手順は非同期とする。
ISDN での伝送時間の計算
　とりあえず右のような表を作り、C4 に式を入れる。

  1. まず、単位を揃えてやる必要がある。1.44MB を bit になおす
  2. で、これを伝送速度（64,000）で割ればいいのだ
  3. 計算式は =(C2*1000000*(8+2)/C3) → =(C2*10000000/C3)
    数値を直接入れても計算結果は同じになるが、セル番号を入れる方が柔軟性がある
  4. 答えはISDN での伝送時間の結果こうなる

変数を入れてみよう
　これで C3 のセルの値を色々と変えると、それに対する伝送時間が計算できる。お終い、…なんだけど(笑)、さっきのデータテーブル機能を使って一覧表みたいにしてみる。

  1. Ｂ列の変数の入力はオートフィルが使える

  2. 結果は変数の右側のセル、Ｃ列に表示したいんだけど、
    問題は式をどこのセルに入れ、どのセルを代入セルとして使うかだ

  3. さっきはテストだから、"0" なんて意味のない数字を表示させたけど、そんなダサいことはしたくない
    じゃ、「式は C6 のセルかな？」って見当がつく

  4. 今度は代入セルだ。さっきと同じように A1 のセルかな？
    けど、A1 のセルだと、変数の初期値が "0" になって、最初の答えがおかしくなるはずだ
    …やっぱり変数の初期値は、どうしても 2400 でなければならない
    じゃ、代入セルは B6 だ。やってみよう

  5. 計算式はさっきの =(C2*10000000/C3) が使えるはずだから
    そのままコピーして、セル番号を変更してやればいいだろう
    C6 のセルに =($C$2*10000000/B6)

  6. B6 から C19 までのセルを選択して

  7. テーブルのダイアログ ボックス の［列の代入セル(C):］で B6 を指定してやる
伝送速度を変えた場合の伝送時間の変化表

　結果は右のようになった。たぶん合ってると思う。(笑)　なお、Ｅ列は、Ｃ列の“秒”を60で割って“分”表示にしてみた。

　この表を作ってみて、その昔、フロッピー１枚、ほぼ 1MB のデータを 1200bps の回線で送ろうと思ったけど、諦めたのを思い出した。
　テレホなんてのもなかった時代だからね。今だったら、テレホの時間でも ISDN でなら、５〜６分で送れるだろうな。

　で、教室での実習だけど、いくらパソコン教室だからってこんな例題は出ない。(笑)　もっと、実用的な（？）ビジネスむけのサンプルだった。

　ＦＶ関数ってのを使っての利息の計算だ。ＦＶ関数の使い方を書いておいた方がいいかな？　こういう計算、苦手なのは拙者だけかもしれないけど。(笑)

　　　FV（利率，回数，定期支払額［，現在価格，支払期日］）　　［ ］内 省略可

利率少数表示、あるいはパーセント表示のどちらでもいい
例． 5.5% または 0.055
回数支払回数の合計
定期支払い額毎回の支払額
現在価格現在の預金（投資）額
省略すると 0
支払期日期末の場合は 0
期首の場合は 1
省略すると 0
　まず、 　これで満期受取額を計算する。
　　満期受取額の計算式は =FV(0.023/12,3*12,10000,10000) になるが
　　セル番号で =FV($C$3/12,$C$4*12,C5,$C$6) とかにしてやる。

　こうしておいて定期積立額を 12,000円、14,000円 … 30,000円と変化させ、満期受取額がどうなるかシミュレートするものだった。




1999年 2月13日（土）第13回

　今日はほとんど進まなかった。(笑)　まず、課題をやってる最中に、セーブしようと思ったら、思わぬエラーメッセージが出てしまった。

ディスクがいっぱいです

　教習の最初に日に、生徒はそれぞれフロッピー・ディスクを渡され、それに出来上がった課題をセーブしゆくのだ。Word は終えたけど、Excel は20回のうちまだ 13回目だ。今ごろディスクがいっぱいになるハズがない…。

　「え？」と思って、とりあえずよけいなシートを削除してもう一度セーブしてみた。したら、今度はなんとかセーブできた。
　で、Ａ：ドライブのプロパティを見ると、なんと、あと数キロバイトしか残ってない。…ハズがなくても、いっぱいはいっぱいのようだ。

　じゃあ、ってんで今度はエクスプローラでＡ：ドライブの中身を見た。サイズ順に並べて妙な、大きすぎるファイルがないかと調べてみたんだ。あるにはあるけど、大きなファイルは Word のファイルだ。グラフの画像とかが入ってるから大きいんだろう。
　Excel のファイルはそれほど大きなのはない。ちょっと大きめのも、ちゃんとよけいなシートは削除してある…。整理できそうなのはない。

　ゴソゴソしてると、不振に思ったおね〜ちゃんがやってきた。「どうしたんですか？」って言うから、「フロッピーがいっぱいで…。ほらね」って見せたら納得して、新しいフロッピーを持ってきてくれることになった。ただ、あとで必要なファイルをコピーしてたらしく、しばらく待たされた。

　フロッピーを持ってきてくれたので、元のフロッピーにあった作りかけの課題をＨＤＤを経由して、新しいフロッピーにセーブしなおした。
　「いいんですけどね…、エクスプローラとか、その辺は次の『ＯＳ応用』の課題なんです」なんて笑ってる。(^^;) なんだ、『ＯＳ応用』ってオーバーな名前が付いてるけど、そんなことやるんかぁ、ってちょっとガッカリすると同時に安心した。(笑)


　複入力データテーブル


┏━━━変数Ｂ━━━━┓
┏
┃
変
数
Ａ
┃
┗		 計算式 変数１ 変数２ 変数３ 変数４ …
変数１ 計算結果
変数２
変数３
変数４
：

　今日も昨日の続きで、データテーブル機能だ。ただし、２種類の変数を使って複入力テーブルの計算表を作成する。
　複入力テーブルを作る時は、右のように、２種類の変数を表の一番左の列と一番上の列とに配置し、計算式は両方の変数が交差する左上のセルに入力する。
　また、当然だけど、代入セルも２つ必要になる。
the multiplication tables
　で、始めに右のような九九の表を作った。上の表と見較べると、B2 から B9 と、C1 から K1 の数字が変数で、B1 のセルに計算式が入ってるのが想像つく。
  1. 代入セルはどこでもいいけど
    とりあえず A1 と A2 にする
  2. B1 に =A1*A2 と、計算式を入力する
    （B1 は "0" と表示される）
  3. B1 〜 K10 を範囲指定する
  4. ［データ(D) - テーブル(T)...］を選択すると、ダイアログ ボックスが出る
    テーブルのダイアログ ボックス
  5. ［行の代入セル(R):］で A1 を
    ［列の代入セル(C):］にカーソルを移し、A2 のセルをクリックする
  6. それそれ $A$1，$A$2 となる
  7. ［ＯＫ］をクリックすると、上のような九九の表が出来上がる


　さて、いよいよ本日のメインディッシュだ。(笑)

　実は、新しいデジカメが欲しいんだけど金がない。だから、金を持ってそうな某女優に「５万円貸してくれ」と借金を申し込んだ。
　しかし、「おヘソがどうした」とか「ラーメンがどーたら」とか言って、貸してくれない。たぶん、某国旅行中にお金を使いすぎて持ってないんだろう。そうならそうとハッキリ言えばいいのに…。(笑)

借金計画
　しかし、世の中捨てたもんじゃない。信濃か府中の友人が貸してくれるという。10年返済で、年利 2.5% の固定金利。実に有利な条件だ。もしかしたら、夢を見てるのだろうか？(笑)

　でも、月々の返済額がどれくらいになるのか心配だから、計算してみることにした。また、もう少し返済期間を長くするとどうなるか、借入額を多くするとどうなるかの試算もすることにした。

　利率が一定の時、定額の定期支払を求めるには、ＰＭＴ関数ってのを使うそうだ。

　　　PMT(利率，回数，現在価値［，将来価値，支払期日］)　　［ ］内 省略可

利率少数表示、あるいはパーセント表示のどちらでもいい
例． 2.5% または 0.025
回数返済（投資）期間内の支払回数の合計
現在価値現在の借入（投資）額
将来価値投資の将来価値
借入金の時は 0
省略すると 0
支払期日期末の場合は 0
期首の場合は 1
省略すると 0
　まず、下のような表を作る。

借金返済計画、空の表

　別段難しくなさそうだ。B8 から B22 の列に返済年数を、C7 から L7 の行に借入金額を入れてゆけばいい。借入額は千円づつ増額した場合の金額を入れることになってる。
　で、計算式は当然、B7 に書く。あとは代入セルをどこにするかだけだ。

　早速、変数から入力することにした。B8 のセルから B22 のセルまで 11 からの数字を入れればいい。オートフィルだ。
　と、思ったけど、テキストには「B8 のセルには 11 を、B9 のセルには =B8+1 と入れ、B10 から B22 に B9 の式を複写しろ」って書いてある。この段階なのにいやに親切だ。
　B8 の数字を変えれば、あとの変数が自動的に変わるからだそうだ。にゃ〜るほど。

　じゃ、次は７行目の変数だ。これも妙に親切に「借入額の変数も セル C4 の値から 1,000円づつ増額した場合を想定して入力してください」って書いてある。…だからそうした。51,000, 52,000, 53,000 … ってうまく行った。

　今度は B7 に入れる式だ。ＰＭＴ関数を使うのは言うまでもない。

　PMT(利率，回数，現在価値) だから
　　=PMT(2.5%/12, 10*12, 50000) だ。これをセルに置き換えてやればいい。

　　　　=PMT(C2/12,C3*12,C4)

　やってみると B7 のセルに \-471 って答えが出る。このＰＭＴ関数は、借金の返済額の場合は赤のマイナスで表示されるしかけになってるそうだ。ま、このままでもいいんだけど、借入額に "-" を付けてやると見慣れた表示になる。つまり

　　　　=PMT(C2/12,C3*12,-C4) 　だ。

借金計画
　複入力テーブルを完成させなければならない。代入セルは…。やっぱり初期値の問題があるから、行の代入セルは C4、列の代入セルは C3 でいいはずだ。

　では、テーブルの作成だ。
  1. 選択範囲は下側の表全体でいいから、B7 から L22
  2. ［データ(D) - テーブル(T)...］を選択
  3. ダイアログ ボックスの指定は行と列との代入セルを指定するこれでいいはずだ
　答えはめでたく下のようになる。＼(^o^)／

借金返済計画表



　…実は、こうはすんなり行かなかった。(^^;) 上のデータテーブルを作って表を出したところ、デタラメの表が出来てしまったんだ。年とともに返済額が増えてしまったり…。
　計算式を見直してみたり、代入セルを変えてみたり、何度も何度もテキストを見直したり…。たぶん１時間近く格闘してたと思う。(笑)

　どうやら B7 の計算式や代入セルの問題じゃなさそうだ。行の変数＝借入額の変数がおかしいらしい。上にも書いたように、テキストには「借入額の変数もセル C4 の値から 1,000円づつ増額した場合を想定して入力してください」って書いてあった。
　　　　７行目の変数
　だから、拙者は C7 のセルに =C4+1000 って書いた。D7 のセルは =C7+1000、E7 以降のセルには D7 の計算式が複写してある。

　けど、よく考えたら行と列との代入セルを指定する{=TABLE(C4,C3)} ってふうに C4 のセルは代入セルに使ってたのだ。

　だから、C4 のセルの中身はどんどん大きくなる。それに連れて C7〜L7 の中身も。(笑)　ハマったっ！^^;;;　これじゃうまく行くわけがない。で、C7 のセルを =51000 と書き換えて解決した。お粗末っ！




1999年 2月18日（木）第14回

　魔の13回が終わって安心してたら、この日も呪われた。(爆)　途中でパソコンがハングアップしてしまい、[Ctrl]+[Alt]+[Del] での再起動もきかないありさま。たいてい、５分おきくらいにセーブするんだけど、この日はうっかりしてセーブしてなかったんで、データはパーだ。だいたい、ここのテキストには、「マメにセーブしろ」って書いてないんだよね。(笑)

　しかたないんで、インストラクターの許可を得て、電源断 → 再投入をやった。すると、なんと今度はセーフモードで立ち上がったのだ。(笑)　「このパソコン、ダメなんじゃないか？」って思ったけど、何度かやってるうちにちゃんと立ち上がった。あとのことは知らないぞ。(笑)　・・・次の日も教習だから、同じパソコンにあたったりして。(^^;)
　んな訳で今日も進んでない。って、なんだかこのところ言い訳だらけだ。(爆)


　Lotus の表の読込

ロータスのファイルを開く
　今日もデータテーブルだ。しかし、今回はロータス（Lotus 1-2-3）の表を読み込んでの作業だ。知ってれば別段、難しい作業じゃないんだけどね。

　ファイルを開く時、右のように "Lotus 1-2-3 ファイル" をクリックしてやる。したらロータスのファイルだけが表示されるので、その中から目的のファイルを選んでやる。ま、“すべてのファイル” にして、その中から選んでやってもいいけど、ややこしいだろうな。(*.*)

　で、出て来た表はセルの枠線がないから、
  1. ［ツール(T) - オプション(O)...］から
  2. ダイアログ ボックスを出して、［表示］タブをクリックする
  3. “□ 枠線(G)”にチェックを入れ
  4. ［ＯＫ］をクリックする
  5. ワークシートのすべてのセルを選択して
  6. フォントのサイズを 14pt から 11pt に変更する
　ま、教室ではこうしたんだけど、ロータスの表ならすべてこういうふうにするのかどうか知らない。ロータスの表のほうでの設定にもよるだろうけど、デフォルト設定されたロータスの表なら、普通はこの手順で Excel の標準になおしてやるのだろうな。

　
読み込んだリスト
　出て来たのは右のようなリストだ。ま、これは僕が適当に作ったもんだけどね。んと、これって商用データベースへのアクセス表ってことにしよう。
　顧客のＩＤ、アクセスポイント、利用したサービス、利用日、接続時間が並んでる。

　このリストから顧客別の利用時間とサービスごとの平均利用時間のデータテーブルを作成してやる。

データテーブル１
ＩＤ別接続時間表


データテーブル２
作業番号別平均接続時間／アクセス回数表

　名前ボックスの利用

　データテーブルを作る時は、いちいちデータベース範囲の指定をしてやった。これって、結構面倒くさいし、計算式も見にくくなる。
　しかし Excel には範囲名に名前を付けて、それを呼び出して利用する機能がある。
　じゃ、上の B2〜F31 の “アクセス一覧表” に "LIST" って範囲名を付けよう。
  1. B2 から F31 を範囲指定し
  2. 名前ボックスをクリックして、"LIST" って範囲名を入力する
  3. 計算式の中で範囲名を利用する場合は “名前ボックス” の ▼ をクリックして一覧表から選ぶ
  4. 範囲名の削除は、［挿入(I) - 名前(N) - 定義(D)］で出るダイアログ ボックスから出来る
　では、データテーブル１の作成だ。B34 からテーブルを作ることにしよう。Ｂ列にＩＤ番号を並べなければならない。

　データの抽出２

　要するに、B2 から B31のＩＤから全部のＩＤを重複しないように引っ張り出してやればいい。ＩＤ番号なんて読みにくいし、結構面倒な作業になる。
　こうした場合はフィルタ機能で、重複したデータを除いて抽出してやればいい。
  1. データの抽出は B34 にすることとし
  2. B34 にあらかじめ抽出したいフィールド名の“Ｉ　Ｄ”という文字を入力しておく
    そうしないと、全部のフィールド（列）が抽出されてしまう
  3. データベース内のセルをアクティヴにする
  4. ［データ(D) - フィルタ(F) -フォルダオプションの設定(A)...］を選択する
  5. ダイアログ ボックスはフィルタオプションの設定のように設定する
    “リストの範囲(L)” は自動的に入るが、“抽出範囲(T)” は 1. で決めた B34 のセルをクリックしてやると上のようになる
  6. ［ＯＫ］をクリックすると抽出されたＩＤデータこういうふうにデータが抽出される


　ここでデータベース関数だ。C34 のセルに下の書式の式を入れてやるんだけど、条件を書くセルをまだ決めてなかった

　Dxxx(データベース範囲，フィールド，条件範囲）

  1. 条件を書くセルは B44〜B45 にしてやろう
  2. ・・・B45 にどんな条件を入れるか、だ…
  3. 今回は“Ｉ　Ｄ”が変数になるから
    条件範囲のセルこの B45 のセルを代入セルにしてやる
  4. 従って、B45 には、なにも書かない
  5. この B44〜B45 にも名前を付けてしまう。名前は“ＩＤ条件”にしよう

　セル C34 と D34 に計算式を入れるんだけど、これを完成させよう。

  1. データテーブル１を見て欲しい。Ｃ列はＩＤ別の合計接続時間だ
  2. B34〜C34 のセルC34 の式は =DSUM($B$44:$B$45,$F$2,B44:B45) だ
    おっと待った(笑)　せっかく範囲に名前を付けたんだから、それを使ってすっきりと書こう
    DSUM(LIST,$F$2, ＩＤ条件)　せっかくだから、フィールド名も左からの番号にしよう
    Ｆ列はＢ列から、ひ・ふ・み・よ、５列目だ

  3. C34 の式は =DSUM(LIST,5,ＩＤ条件)　ずいぶんすっきりする

  4. D34 は “最終接続日” だから、式は =DMAX(LIST,4,ＩＤ条件) になる
  5. とりあえず出来上がったデータテーブル１とりあえず、こんな表が出来上がる

　じゃ、表を完成させよう。
  1. 日付がヘンなふうになってるけど、これは1900年１月１日からの日数になってるからで、表示を変更してやるとちゃんとなる。
  2. ［セルの書式設定］のダイアログ ボックスの［表示形式］タブで m"月"dd"日" に変更してやる

  3. 表示させたくない行を非表示にしてやることもできる 非表示にした行を再表示する時は
出来上がったデータテーブル１
　34行目を非表示にして体裁を整えてやったのが右の表（データテーブル）だ。


　これが出来れば、データテーブル２の方は問題なくできる。

  1. データの抽出は B47 にすることにし、“作業番号”と入れる
  2. 作業条件条件は C44〜45 に書くことにし
  3. 範囲の名前は“作業条件”にする
  4. C47 に =DAVERAGE(LIST,5,作業条件)
  5. D47 に =DCOUNT(LIST,1,作業条件) と計算式を入れる
出来上がったデータテーブル２
　46行目を書き足して、左のようなデータテーブルが完成した。これで47行目を非表示にしてやればいい。

　この日は、これっぽっちで終了。




1999年 2月19日（金）第15回

　パソコンの前に座るといきなりテストだ。ころっと忘れてたけど、15回目ということで第２段階終了のチェックテストだったのだ。
　教室ではテストのほうを先にやったけど、課題のほうが前回と関係あるから、そっちを先に書く。


　ＩＤ別・作業別リスト
　まず、前回のデータベースを使って右のような複入力テーブルを作る課題だ。

　テキストには、なんのデータテーブルなのか書いてないけど、どうやら「ＩＤ別・作業番号別の接続時間表」のようだ。

　一見して “ＩＤ” と “作業番号” が変数になってるのは判る。両方の変数とも、以前に作ったデータテーブルからコピーしてやれば良さそうだ。“ＩＤ” はデータテーブル１から、“作業番号” はデータテーブル２から持ってくればいい。ただ、“作業番号” の方は行と列とを入れ替える必要がある。

　式は B57 のセルに入ってるはずだけど、見えない。実はちょっとした仕掛がしてあるからで、これについてはあとで書く。
　ともかく式だ。接続時間の合計を求めるんだから DSUM(LIST，5，条件範囲) になる。“ＩＤ” と “作業番号” の条件なら以前に作った覚えがある。これがそのまま利用できそうだ。
　二つの条件これだ。範囲は B44:C45。

だから、B57 の式は =DSUM(LIST,5,B44:C45) となる。

　セルの非表示

　普通だと、B57 になにか数字が出るはずなんだけど、出ないのは［セルの書式設定］のダイアログ ボックス［表示形式］タブの“ユーザ定義”で ";;;" と指定して、非表示にしてあるからだ。


　ここまで来たら、出来たも同然だ。
  1. B57 〜 I65 を範囲指定する
  2. ［データ(D) - テーブル(T)...］を選択すると、ダイアログ ボックスが出る
    テーブルのダイアログ ボックス
  3. ［行の代入セル(R):］で C45 を
    ［列の代入セル(C):］にカーソルを移し、B45 のセルをクリックする
  4. ［ＯＫ］をクリックすると上のデータテーブル出来上がる


　これで終わりかと思ったら、次のページに「これと同じ表をピボットテーブルを使用して作ってください」って書いてあった。更にこのピボットテーブルから円グラフを作るらしい。(笑)
  1. “アクセス一覧表”のセルをクリックしてアクティブにする
  2. ［データ(D) - ピボットテーブル レポート(P)...］を選択する
  3. 1/4 で“◎Excel のリスト/データベース(M)”にチェックが入ってることを確かめて
    ［次へ＞］を押す
  4. 2/4 で“範囲(R):”が $B$2:$F$31 であることを確かめて［次へ＞］を押す
  5. PivotWiz で設定
    3/4 は上のように設定して［次へ＞］を押す
  6. 4/4 では、新しいワークシートにしてやろう。これで［完了］

出来上がったピボットテーブル



　円グラフ

PivotTable から作ったグラフ
　さて、グラフだ。右のようなのを作ることになってる。ポイントだけ書いておこう。

  1. 売上比率の大きい順に並ぶよう、ピボットテーブルのデータを並び替えておく
  2. 選択範囲はＩＤと総計とにする
    グラフを作る時の選択範囲は上のようにする

  3. “□ 凡例を表示する(S)” のチェックを外す
  4. “◎ ラベルとパーセンテージを表示する(A)” をチェックする
  5. グラフが出来上がってから
    切り離したい部分にハンドルを表示させて切り離す
    切り離したい部分を上の状態にして切り離す


Excel 第２段階終了テスト
　Excel の実習も15回目になったので、第２段階終了のテストだった。右のような表を作って簡単な処理をする。
  1. オートフィルタを使って“Ａ事業部”のデータだけ抜き出す
  2. 98年度の実績の小さい順に並べる
  3. 上記の処理をした表を元に３Ｄ棒グラフをグラフシートに作る
　これだけだ。
  1. オートフィルタ
    1. リストの中のどこかのセルをクリックする
    2. ［データ(D) - フィルタ(F) - オートフィルタ(F)］を選ぶと
      フィールド名のところが下のようになる
      レコード名の列
    3. “事業部” のセルの ▼ をクリックする
    4. プルダウン メニューから “Ａ” を選択する
    5. “Ａ” 事業部のデータ（レコード）だけが表示される
    6. （元に戻すには“事業部”のプルダウン メニューから“（すべて）”を選択する）

  2. 並べ替え
    1. リストの中のどこかのセルをクリックする
    2. ［データ(D) - 並べ替え(S)］を選ぶ
    3. “最優先されるキー” は “98年度”にし
    4. “◎ 昇順 (A)” にチェックを入れ、［ＯＫ］をクリックする

  3. 抽出・並べ替え終了
    Ａ事業部開発品の売上推移

  4. グラフの作成
    1. コードネームから、98年度までを選択する
      グラフを作るための選択範囲は上の通りだ
    2. グラフシートにグラフを作る場合、Excel95 では、いきなりをグラフ ウィザード 押してはダメだ
      ［挿入(I).. - グラフ(H)］で “新規グラフシート”を選択してやる

    3. あとは 3-D 棒状グラフを選択して［次へ＞］を押してゆけばいい

    　上のようなグラフが出来上がる。もちろんタイトルを入れたり、フォントを変更したりの工夫はいるけどね。






JA3AEBへ
総合メニューへ
古い記事を参照される方は
「なんだなんだ！」のトップへ