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

Michael T. Kye

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


1999年 1月26日（火）第6回

社員名簿

　今日の課題は右のようなリストを作れっていうことだ。前回の続きで、データベースのようだ。別になんの変哲もない表で難しくも何ともない。

　しかし、データの入力方法が違うというのだ。レコード単位で入力して行くツールがあるのでそれを利用して入力すると楽だよ。ってことだそうだ。


　フォームによる
　　　　データ入力

フォームによるデータの入力
　どこかのセルをクリックしておいて、メニューから［データ(D) - フォーム(O)...］を選ぶと下のようなダイアログ ボックスが出るので、これを利用して入力する。
　しかし、入力用の表を見ると社員番号は連続になってるから、これはオートフィルを利用した方が良さそうなので、そうした。

　あとのデータは左のダイアログ ボックスを利用して入力するんだけど、次のフィールドに移る時にちょっとした注意がいる。
　入力を終わって次のフィールドに移る時、[Enter] を押してはだめなのだ。[Enter] を押すと、次のレコードに進んでしまう。

　次のフィールドへ　[Tab]
　前のフィールドへ　[Shift] + [Tab]
　次のレコードへ　[Enter] または [↓]
　前のレコードへ　[↑]

　入力方法に関しては、テキストにはごちゃごちゃ書いてあったけど、特別のことはない。ただ、日付の入力に関しては書いておこう。
  1. “入社”のフィールド（列） E を選択する
  2. 右クリックで［セルの書式設定(E)］を選び
  3. 日付の書式変更 このように設定する
　そうすると、上のリストのように 1999/01/26 ってふうに日付の桁が綺麗に揃う。

　このあと、条件検索とかソート（並べ替え）をやったけど、その辺は省略だ。


　INT と MOD
お年玉金種表
　次は関数の利用だ。まず、INT 関数と MOD 関数。これを利用して、おばあちゃんが孫へのお年玉の金種を計算しようとしてる。
　「まさかぁ」って思ってるだろ。^^; けど、判らんぞ。おぢいさんが人にシンセのこと教えたり、パソコン教室に通ったりする時代だからね。(爆)

　INT とか MOD っての、少しでもプログラミングをかじった人にはおなじみなんだけど、一般にはなじみがないかな？　けど、結構便利な関数だ。
　Excel での書式と具体的な働きを挙げておこう。

INT(数値)　　　MOD(数値, 除数)　　
　　INT(12.3)　→　12 　　MOD(9,3)　→　0
　　INT(12.9)　→　12 　　MOD(9,4)　→　1
　　INT(-2.5)　→　-3 　　MOD(17,10)→　7
　　INT(-1)　→　-1 　　MOD(12300,10000)→　2300
　　INT(12345/10)　→　1234 　　MOD(4,20)　→　4

　この中で、最後の MOD(4,20) だけど、数値（割られる数）が、除数（割る数）よりも小さい時は、数値（割られる数）そのものを返す。


　んじゃ、ぼつぼつ行くかっ！　ひろしくんの分を見ると 12,300円だ。「１万円が１枚に千円札が２枚、それに百円玉が３枚に決まってるじゃないか」なんてシラケたこと言わないのっ！ Excel で計算するところにいーところがあるんだから。(爆)

　 　１万円札の
　　枚数計算

　ひろしくんのからやってみよう。C3 のセルだ。
　で、この数式をひろふみくん以下のセル C4 〜 C8 に複写してやればいい。これで１万円札の枚数計算は終わりだ。あ、合計が残ってるけど、この計算は判るはずだ。一番上の 10000 を含めないようにね。(笑)


　 　５千円札の
　　枚数計算

　これがちょっと厄介だって思うのは僕だけだろうか？(笑)　サンプルは、やっぱりひろしくんの 12,300円だ。考え方としては 　って、こうなると思う。これを式になおせばいいんだ。
　答は =INT(MOD(B3,10000)/5000) になるんだけど、どうしてこうなるかじっくり考えてみよう。
  1. まず 12,300円を１万円札で払った残りの金額の計算だ
  2. =MOD(12300,10000) だ。セルの番号で書くと =MOD(B3,10000) となる
  3. どっかのセルに =MOD(B3,10000) と書いて実行して欲しい
  4. 答えは当然 2,300 になる。MOD(12300,10000) = MOD(B3,10000) = 2300 ってことだよね
  5. で、この 2,300円のために、５千円札が何枚必要かを計算してやればいい
  6. =INT(2300/5000) だよね？
  7. 4. のとおりだから、2300 を MOD(B3,10000) に置き換えてやればいい
  8. で、めでたく =INT(MOD(B3,10000)/5000) になる
  9. "(" の数と ")" の数とをちゃんと合わせるよ〜にっ
　当然のことだけど、スクールのテキストにはここまでバカ丁寧に書いてない。頭の悪い拙者に付き合ってくれてありがとう。(爆)


　 　千円札以下の
　　枚数計算

　次に千円札の枚数の計算だけど、 　って、考えればいい。え？「５千円札じゃなく１万円札だったじゃないか」って？(笑)　けど、この際、１万円じゃなく５千円札で払ったとしたら、って考える。「我々は、１万円札のない世界に拉致された」と考えるんだ。(爆)
　したら、自ずと =INT(MOD(B3,5000)/1000) と判るはずだ。五百円玉なんかも同じ。

　あったりまえの話だけど、ひろしくんのセルで作った数式は、ひろふみくん以下のセルに複写しといてください。



1999年 1月28日（木）第7回
　
なんかの得点表
　今日も関数が色々出て来た。IF 関数、標準偏差、最大、最少の関数だ。で、右のような表を作る。この表はなん表だろう？(笑)　一応、なんかの競技の得点表ってことにしておく。

　この表の中で手で打ち込むのは“氏名”、“得点”それに右下の説明みたいな部分だけだ。あとの“得点”から“予選通過”まで、それに右上の部分は関数を使って出してやる。

　IF 関数

　IF 関数ってのは「もし〜だったら、どうする。そうじゃなかったらこうする」ってふうに条件によって違った処理をする関数だ。
　「もし、そのパソコンが10万円以下だったら買う。そうじゃない時はやめる」とかね。

　 　Excel の
　　IF 関数

　上のを Excel の IF 関数を使って書くと、=IF(A1<=100000,"買う","やめる") ってなる。（A1 はパソコンだとする）

　つまり Excel の　IF 関数の基本形式は次のようになる

　条件式には下記の種類がある。（セル A1 と B1 とを比較する場合）

A1=B1　　A1 と B1 とは等しい
A1<>B1　　A1 と B1 とは等しくない
A1>B1　　A1 は B1 より大きい
A1>=B1　　A1 は B1 以上
A1<B1　　A1 は B1 より小さい
A1<=B1　　A1 は B1 以下

　IF 関数の基本形式は上に書いたように
　　　IF(条件式，条件に合った時の処理，合わなかった時の処理) だけど、合わなかった時の処理 として、IF 関数を書ける。

  1. 条件式1に合った場合は、条件に合った時の処理 を実行する
  2. 合わなかった場合は、条件式2 はどうかな？　と見に行く
  3. 条件式2に合った場合は、条件に合った時の処理 を実行する
  4. 合わなかった場合は、合わなかった時の処理 を実行する

　じゃ、上の表でやってみよう。セル E4 に次のように入力する。

　　　=IF(C4>=5000,"Ａ", IF(C4>=3000,"Ｂ", IF(C4>=2000,"Ｃ","Ｄ")))

　IF が三つもあるじゃないかって？(笑)　ま、いいから式の意味を考えてみよう。
  1. もし、C4 の値が 5000 以上なら Ａ と表示する
  2. （5000 未満で）3000 以上なら Ｂ と表示する
  3. （3000 未満で）2000 以上なら Ｃ と表示する
  4. それ以外なら Ｄ と表示する
　入力し終わったら、下の行も複写する。

　Ｆ列の○と×だけど、これは F4 に次の式を入れ、F5 から F13 にコピーすればいい。

　　　=IF(C4>=3000,"○","×")

　SUM, MAX, MIN,
　AVERAGE, STDEVP 関数

　Ｄ列の“偏差値”はとりあえず置いといて、今度は表の右上に注目しよう。
　別に説明はいらないと思う。で、標準偏差値だけど、STDEVP() って関数を使うそうだ。

　さて、最後に残ったのは得点表の“偏差値”の列だ。ここの数式は考えた。(笑)

　10*(得点-平均)/標準偏差値+50 だ。

　下線の部分はシートの右上のところの数字を利用するんだけど、この部分のセル番号は数式をコピーする時に変わってしまっては困るんで、絶対座標で指定してやる必要がある。
　従って、セル D4 には次の通りの式を入れればいい。

　　　=10*(C4-$I$4)/$I$5+50

　この式を D5 〜 D13 にコピーすれば出来上がりだ。ま、桁を調節したりセンタリングしたり、罫線を変更したりとか、見掛けの部分の仕上げをする必要はあるけどね。

　教室ではこのあと、これのグラフを作ったけど省略だ。



　次の課題でも色々な関数が出て来た。しかも、実用で使うための工夫とかが出て来て Excel らしくなって来たな、って感じだ。　

色々な関数の練習


　最終的に上の左側の表を作るのだが、右側の《部品マスター》にデータを入力してやる。その前に F2 の日付なんだけど、F2 には =TODAY() って関数が入ってる。
　これは表を開いた当日の日付だ。作成した日付じゃない。従って、こんな表に TODAY 関数を使うのは間違いだ。けど、練習のために使っておく…、ことになってる。(^^;)

　《部品マスター》への入力は特に問題ないはずだ。コード番号は連続してるからオートフィルが利用できるし…。

　
　VLOOKUP 関数

　マスターの入力が終わったらテストだ。コード番号を入れて部品名を出してみよう。で、C7（部品名の一番最初の行）に、とりあえず下のような式を入れてやる。

　　=IF(B7="","", VLOOKUP(B7,H7:J14,2))

　この式の意味は次の通りだ
  1. =IF(B7="","", VLOOKUP(B7,H7:J14,2))　もし、B7（コード）になにも入力されてなかったら
  2. =IF(B7="","", VLOOKUP(B7,H7:J14,2))　なにもしない
  3. =IF(B7="","", VLOOKUP(B7,H7:J14,2))　なんかが入力されてたら VLOOKUP 関数を実行する

  4. =IF(B7="","", VLOOKUP(B7,H7:J14,2))　B7 を H7 から J14 の範囲内から検索する
  5. =IF(B7="","", VLOOKUP(B7,H7:J14,2))　合致するデータがあれば、（その行の）２列目を表示する
　で、試しに B11 に "311001" って入れてみよう。 C11 に“スプリングＡ”って出るはずだ。出なければどっか違ってる。(笑)
　じゃ、今度は B11 に "511001" って入れてみる。ありゃま“ナット　Ａ”だってさ。(笑)　511001 なんてコードはないんだよね。ないコード入れて部品名が出てたりしちゃ困るわけだ。早速その対策をしてやらなきゃならない。

　その前に B11 に "511001" って入れた時に VLOOLUP がなにをやったかなんだけど、VLOOKUP は "511001" 以下で最大のをつかみ出したんだ。この場合は、311008 だ。

　で、=IF(B7="","", VLOOKUP(B7,H7:J14,2)) に対する対策なんだけど、要するに、マスターにないコードが入力された場合に、なにも表示しないようにすることにする。

　方法はいくつか考えられるけど、ここでは 　って条件を入れることにしよう。

　OR 関数と AND 関数

　　　OR(条件１，条件２，…，条件ｎ)

　上が OR 関数なんだけど、並べられた条件のうちのどれか一つの条件に合致すれば、
関数として、条件が満たされたと判断される（OR 関数は“真”＝ TRUE を返す）

　ついでに AND 関数についても書いておこう。

　　　AND(条件１，条件２，…，条件ｎ)

　AND 関数は、全ての条件に合致し時のみ、
関数として、条件が満たされたと判断される（AND 関数は“真”＝ TRUE を返す）

　じゃ、さっきの 　ってのを OR 関数で書いてみよう。　OR(B7<311001,B7>311008) だ。
　これで B7 が 311001未満、あるいは B7 が 311008 より大きい時、この IF 関数は「合わないよ〜」って、“偽”（FALSE）を返す。
　で、B7 が 311001 以上、あるいは B7 が 311008 より小さい時、この IF 関数は「合ったよ〜ん」って、“真”（TRUE）を返す。

　IF 関数と合わせて
　　　=IF(OR(B7<311001,B7>311008),なにもしない,なんかする))
　って書けばいいことになる。

　だから C7 のセルの式は

　　　=IF(B7="","", VLOOKUP(B7,H7:J14,2))
　　　　　　　↓
　　　=IF(OR(B7<311001,B7>311008),"",VLOOKUP(B7,H7:J14,2))

　と変更すればいい。

　これを B8 から B12 のセルに複写してやればいいんだけど、その前にすることがある。H7:J14 はセルの座標が変わってしまっては具合が悪いから、絶対座標に変更してやる必要がある。

　　　=IF(OR(B7<311001,B7>311008),"",VLOOKUP(B7,$H$7:$J$14,2))

　これでいい。こうしておいてて複写する。

　C7 のセルの数式は AND 関数を使って次のようにすることも出来る。

　　　=IF(AND(B7>=311001,B7<=311008),VLOOKUP(B7,$H$7:$J$14,2),"")

　次の“単価”の列だけど、これもさっきの“部品名”の列と同じようにやればいいのは判ると思う。部品マスターの３列目のデータを表示するように変更すれば、それでいい。C7 を D7 にコピーして、手で修正してから D8 〜 D12 にコピーするのが能率的だろう。

　　　=IF(OR(B7<311001,B7>311008),"",VLOOKUP(B7,$H$7:$J$14,3))


　E列、“数量”は手で入力するセルだから、そのままにしておいて、F列の“金額”だ。単価×数量だから簡単だよね。D7*E7 だ。
　これで正解は正解なんだけど、単価（D列）か数量(E列）にデータが入ってない時はエラーになったりするんで、その対策をする必要がある。

　　　=IF(OR(D7="",E7=""),"",D7*E7)

　これを D8 〜 D12 に複写する。あと、“合計”とか“在庫金額”が出るようにしてやれば完成だ。
　
　一応、これでかなり実用的なのが出来たんだけど、問題がある。間違えて数式の入ってるセルに数値を入れたりしたら、数式がなくなってしまう。［元に戻す］で戻してやればいいんだけど、何ともダサい。(笑)
　なんか工夫が出来そうに思うけど、今の段階ではそこまで習ってない。


・補足

　IF 関数、それに AND 関数と OR 関数は、プログラミングとかマクロ言語と付き合いのない人には、とってもややこしく見えるかも知れない。
　しかし、コンピュータに自動的になにかをさせようと思ったら、どうしても IF、AND、OR と付き合わなければならないのだ。同じ IF とかでも、言語（BASIC とかＣ言語とか）やアプリケーションによって文法（書式）が少しづつ違うけど、基本は同じだ。だから、ぜひ自分の物にして欲しいな。

　なお、AND と OR との具体的な使い方については別のところに書いたので、そちらを参照して欲しい。




1999年 1月30日（土）第8回

　ちょっと色々手間取っててページの更新が遅れてしまった。(笑)

　まず、28日に

間違えて数式の入ってるセルに数値を入れたりしたら、
数式がなくなってしまう。
って書いたら、某氏から

　　数式を入力したセルに保護をかけて防止するのが一般的な方法です。

　って、アドバイスを頂いた。試してみたけどうまく行かなかった。(^^;) セルとシートと両方保護しなければならないみたいなんだけど…。
　インストラクターにその辺のことを尋ねてみたら「あとで出て来ます」って話だった。だから、この件はその際に。

　
sample12
　本日最初の課題は右のようなのだ。主には INDEX 関数ってのの練習。
　セル C3 と C4 とに力士コードを入力すると、C5 に力士同士の対戦回数が表示されるってシカケになる。
　当然、コード表にない力士コードを入れたり、なにも入力しない時は、ヘンなのが出ないように工夫しなければならない。


　まずデータの入力だけど、力士名、それに対戦回数は同じのが同じ順番に並んでる。当然、コピー＆ペースト（貼り付け）だけど、縦と横の方向が違う。

行列を入れ替える
　Excel の場合、普通は縦方向に入力すると思う。今回も力士名と対戦回数は縦に入力した。で、これを横方向に複写するわけだ。
  1. 右クリックで［コピー］を選ぶところまでは同じ
  2. 複写しようとする一番左のセルをクリック
  3. ［形式を選択して貼り付け］を選択する
  4. ［□ 行列を入れ替える(E)］にチェックを入れ
  5. ［ＯＫ］をクリックする
　こういう手順になる。

　また、"***********" の部分だけど、
  1. "*" を１つ入力して
  2. ［セルの書式設定］ダイアログ ボックスを出す
  3. ［横方向］の“繰り返し”を選択する

　入力が無事に終わったら表の D3, D4 それに C5 に計算式を入力してやる。テキストには「INDEX 関数と今まで習った関数とで工夫しろ」って書いてあるぞ。

　INDEX 関数

　INDEX 関数の書式は

　　　INDEX(表の範囲，行位置，列位置，[領域番号]) 　　（[ ] 内省略可）

　こうなってる。今回は省略可能な“領域番号”は使用しない。今、上の表で

　　　INDEX(C12:H17,3,1)

　を実行すると、“32" って出るはずだ。

　ってことになる。

　なんか VLOOKUP 関数と似てる気がするんだけど、 　と言えるかな？

　さて、表の C5 のセルに INDEX 関数を使った式を入力だ。

　　　=INDEX(C12:H17,C3,C4)

　C2 に "3"、C4 に "1" と入力すると、上と同じように "32" って出る。

　しかし、範囲外のコード番号が指定された時などのことを考えなければならない。以前に出て来た IF 関数、それに OR 関数 または AND 関数の組み合わせる必要がある。今回は趣味の問題で AND 関数を使ってみた。

　　　=IF(AND(C3>=1,C3<=6,C4>=1,C4<=6,C3<>C4),INDEX(C12:H17,C3,C4),"")

  1. もし
  2. C3 の値が １以上で
  3. C3 の値が ６以下で
  4. C4 の値が １以上で
  5. C4 の値が ６以下で
  6. C3 の値と C4 の値が同じでない時
  7. INDEX 関数を実行する
  8. それ以外だったらなにもしない
　僕はこうしたけど、6. の C3<>C4 の条件はいらないだろう。なければ "*******" が表示されると思う。


　今度は表の D3, D4 の数式だ。D3 のセルに次の式を入れる。素直にやると次のようになる。

　　　=IF(AND(C3>=1,C3<=6),INDEX($G$3:$G$8, C3,1),"")

　けど、VLOOKUP 関数を使って、次のようにも書ける。

　　　=IF(AND(C3>=1,C3<=6),VLOOKUP(C3,$F$3:$G$8,2),"")

　これを D4 のセルにも複写する。


　ついでに、上の

　　　=IF(AND(C3>=1,C3<=6,C4>=1,C4<=6,C3<>C4),INDEX(C12:H17,C3,C4),"") は

　　　=IF(OR(D3="",D4=""),"",INDEX(C12:H17,C3,C4))

　と、スッキリ書く方がいいだろう。

　
社員名簿
　続いて、以前に作った右のリストにデータを追加した上で、レコードの抽出や複数キーでのデータの並べ替え、集計コマンド、リストのレベル別表示なんかをやったんだけど、大幅端折る。(笑)

　まず、データの追加は簡単だから、ここではやらないことにする。あ、教室では真面目にやったからね。(笑)


　データの抽出

　入会が 1970年７月以降のデータの抽出をする。
  1. シートの I1 に“入　社”、I2 に“>=1970/7/1 と入力する
  2. リストのどこかのセルをクリックしてアクティブにする
  3. ［データ(D) - フィルタ(F) - フィルタオプションの設定(A)］を選択する
  4. 抽出のフィルタオプション
  5. ［検索条件範囲(C):］のテキスト ボックス（白い部分）をクリックし I1 〜 I2 を範囲指定する
  6. 上の図のようになるので［ＯＫ］を押す。
抽出結果

　すると、右のようにデータが抽出される。元に戻すには［データ(D) - フィルタ(F) - すべて表示(S)］を選択する。


　この抽出にはちょっと注意する点があるので書いておく。

　このあと、OR 検索や AND 検索をいくつかやったけど、セルに書く書式だけ示しておく。

・AND 検索 ＝ 同一行に条件を入れる
入　社入　社
>=1970/07/01<=1998/03/31

入社が 1970年７月１日以降、1998年３月31日まで（の人）

入　社性別
>=1997/07/01女

入社が 1997年７月１日以降で、性別が女
つまり、1997年７月１日以降に入社した女性
若い女性のデータだけが欲しいのかも知れない(笑)


・OR 検索 ＝ 異なる行に条件を入れる
入　社性別
>=1997/07/01　
　女

入社が 1997年７月１日以降（の人）か、性別が女
つまり、入社が 1997年７月１日以降（の人）と、女性（全員）

入　社
>=1970/07/01
<=1998/03/31

入社が1970年７月１日以降か、1998年３月31日まで（の人）
これだと、全部のデータが出てしまう。条件なしと同じだ
単純な数字に置き換えて考えてみると判りやすい

　>3　　こう書いても同じ意味 →　　>=4　
　<8　　<=7　

４以上（∞まで）、または７以下（-∞まで）って意味になる

OR と AND とや "<" と ">" とを間違ったりすると、こういうことになる(笑)
笑いごっちゃなく、この間違いに気付かず半日ほどのたうちまわった経験がある(爆)

　AND と OR との使い方（機能）は下のように IF 関数と組み合わせて使う時でも、全く同じだ。

　　　=IF(AND(C3>=1,C3<=6),VLOOKUP(C3,$F$3:$G$8,2),"")

　　　=IF(OR(B7<311001,B7>11008),"",VLOOKUP(B7,$H$7:$J$14,3))



　複数キー指定の並べ替えをやったけど、これも［並べ替え］のダイアロ グボックスを見れば直感で判るから省略する。



　データの集計

　同じ表から社員の住所別の集計をやってみる。

　集計をする前に、必ずキーとなる項目を並べ替えてやる必要がある。この場合だと、 集計のダイアログボックス
　並べ替える。漢字の部分を並べ替えてもＪＩＳコード順に並ぶだけで、アイウエオ順に並ぶわけではないけど、データは整理される。

　並べ替えされたら集計だ。［データ(D) - 集計(B)］のダイアログ ボックスで左のように設定する。
　［集計するフィールド］はちょっと見にくいけど、“住所”にチェックが入ってる。

集計結果
　そうすると、右のように集計される。

　集計前の状態に戻すには［集計の設定］ダイアログ ボックスで［すべて削除(R)］（Excel95 では［リセット(R)］）を押す。“すべて削除”なんて、データが全部消えてしまいそうで、恐い気がするなぁ。(笑)


　アウトライン機能

階層レベルボタン アウトライン全体の特定レベルのデータを表示する
レベルバー レベル詳細行・列が表示されてることを示す
マイナスボタン クリックすると、レベルバーで示されてる
下位レベルを非表示にする
プラスボタン 非表示になってる行や列が表示される

　時間がなくなって来たので上のテーブルでごまかしておく。(笑)　ま、試してみれば判るから詳しい説明はいらないだろう。

　要するに、一番上の［１］［２］［３］で階層を選んで表示し、［−］と［＋］ボタンで詳細を隠したり表示させたりする。
　すべてを表示したい時は階層レベルボタンの［３］をクリックすればいい。集計前の状態に戻すには、上に書いたように［集計の設定］ダイアログ ボックスで［すべて削除(R)］（Excel95 では［リセット(R)］）を押す。




1999年 2月 2日（火）第9回

累計表
　今日の最初の課題は以前に作った３枚のシートからから１〜３月の累計表を作ることだった。こういうふうに、複数のシートにまたがった同一セルの合計を求めるのを“串刺し演算”と言うそうだ。

　累計表のフォーマットは各月の表と同じだから、表そのものは１月から３月までの実績表のどれかをコピーして作ればいいのは判る。

　串刺し演算

　串刺し演算の手順は下記の通りだ。
  1. １〜３月のどれかのシートをコピーして累計用のシートを作る
  2. 新しく出来た累計用シートの C4 〜 F9 の式と数値をクリアーする
    （右クリックから［数式と値のクリア(N)］）
  3. 累計用シートで C4 〜 F9 を選択し オートサム をクリックする
  4. １月実績のシート見出しをクリックし、
  5. [Shift] を押しながら３月実績のシート見出しをクリックする
  6. 今開いてる３月実績のシートの C4 のセルをクリックし、 オートサム をクリックする
　そうすると累計用のシートに演算結果が表示される。で、タイトルやシートの見出しを変更してやればＯＫ。


　
合計表の複合グラフ

　次は以前に作った合計表からのグラフの作成だ。この表からは以前にも面グラフを作ったけど、今度は右のような複合グラフを作る。
　中華料理店で１ヶ月に1700万円、日に567万円の売上があるのか、なんて全然責任持たないからね。(爆)

　グラフの作り方は Excel97 と 95 とでは、少し手順が違うので、97 でやってみる。


　複合グラフの作成

  1. グラフにする範囲（B3 〜 E10）を指定する
  2. グラフ ウィザード の［グラフの種類］から［ユーザ設定］のタブを選ぶ
  3. ［２軸上の折れ線と縦棒］を選び［次へ＞］
  4. 2/4 で “◎行(R)” にチェックを入れ［次へ＞］
  5. 3/4 の［タイトルとラベル］のタブで
    ［グラフ タイトル(T)］は“１〜３月度実績”
    ［Y/数値軸(V)］に“単位千円”と入れ、
  6. 同じ 3/4 の［目盛線］のタブを選び
    ［Y/数値軸］の “□目盛線” にチェックを入れて［次へ＞］
  7. 4/4 で “◎新しいシート(S):” にチェックを入れる
    ［完了］をクリックするとグラフ専用のシートにグラフが出来る
　出来上がったグラフは“合計”はともかく、“Ｅ店”と“Ｆ店”まで折れ線グラフになってるので、これを次の手順で棒グラフに変更してやる。
  1. 変更したいデータ系列（この場合“Ｅ店”か“Ｆ店”の折れ線グラフ）を選択する
  2. 右クリックのショートカットから［グラフの種類(T)...］を選択し
  3. “棒状” を選択し［完了］を押す。変更された棒グラフは様式が違うので
  4. “Ｅ店”または“Ｆ店”の棒グラフを右クリックして
    ［データ系列の書式設定(O)...］から［軸］のタブを選び“
    “◎主軸（下/左側）(P)” にチェックを入れ［ＯＫ］を押す
  5. “Ｅ店”“Ｆ店”のもう片方も同じ手順で棒グラフに変更する
　（Excel95で）もし、折れ線グラフが見にくい色だったら、［データ系列の書式設定(O)... - パターン］から“線”と“マーカー”の色を変更してやる。

　あと、タイトルなどの書式設定を整えてやり整えてやれば出来上がり。そうそう、上のグラフと同じようにするには、“データ系列の並び”を変更してやる必要がある。


　Excel95 では範囲指定したあと、［挿入(I).. - グラフ(H)］で “埋め込みグラフ”か、“新規グラフシート”かを選択してやる。
　また Excel95 では、“◎主軸（下/左側）(P)”ヘの変更は、ショートカットの［グラフの種類(T)...］の［オプション(O)］の［縦棒グループの書式設定］から出来る。



sample16
　次もグラフだけど、今度は絵入りのグラフだ。図形を挿入したグラフと言うらしい。まず、右のような表を作って、スタンダードなグラフを作る。

sample16 のとりあえずのグラフ

　表もグラフも簡単だから、ここまでは出来るはずだ。

　これに絵を入れて右のようなグラフを作る。図を入れるんだから、当然事前に図を用意しておく必要がある。僕の場合は、手持ちの画像ファイルに合わせて表とグラフを作ったんだけど。(爆)
　またこれも Excel95 と 97 とでは、ちょっと手順が違うので Excel97 での方法を書く。
  1. 左側のピアニストの棒グラフを選択する
  2. ショートカットから［データ系列の書式設定(O)...］を選択する
  3. ダイアログ ボックスの［パターン］の［塗りつぶし効果(I)...］をクリックする
  4. ［形式］で “◎引き伸ばし(S)”にチェックが入ってることを確かめて
    ［図］のタブの［図の選択］からあらかじめ作っておいたピアノの絵を選択する
  5. ベーシストのグラフにも同じようにして図形を挿入する
図形を挿入したグラフ

　とりあえず右のようなグラフが出来たんだけど、図の後ろが透明になってないのが気に入らない。(笑)
　.gif ファイルは使えなくて .jpg ファイルを使ったんだけど…。スクールでは、スクールがあらかじめ用意した .wmf って拡張子のファイルが使われていたが、確かにバックが透明のようになっていた。
　僕の持ってる画像ツールでも .wmf ファイルは出来るんだけど、透明のオプションはない。だから、この点については今後の研究課題とさせていただく。(爆)

　次にこのグラフの表示方法を変更する。手順は
  1. 左側のピアニストの図形グラフを選択する
  2. ショートカットから［データ系列の書式設定(O)...］を選択する
  3. ダイアログ ボックスの［パターン］の［塗りつぶし効果(I)...］をクリックする
  4. ［形式］で “◎引き伸ばし(S)”にチェックが入ってることを確かめて
    ［図］のタブで “◎拡大縮小して積み重ね(C)］”にチェックを入れ
    “図形を表す単位(U)”は "2" のままで［ＯＫ］を押す
  5. ベーシストのグラフにも同じようにして図形を挿入する

積み重ねグラフ


　図が適当でないのもあるけど、バックが透明じゃないので何ともダサい。(笑)

　スクールでは同じようなグラフをプリントアウトしたんだけど、印刷が始まるまでにずいぶん時間がかかった。
　かなり重い処理なんだろうな。




　次にやったのはピポットテーブル機能というヤツだ。これも以前に作った表を元にして作る。

　ピボットテーブルってのは、リストの中から特定のフィールドを取り出して、色んな角度から集計や分析を行うために使う。必要に応じて簡単に表示を切り換えられるのも便利だ。


　ピボットテーブルの作成

　手順は下記の通りだ。
  1. まず、読み込んだリストのセルをクリックしてアクティブにする
  2. ［データ(D) - ピボットテーブル レポート(P)...］を選択する
  3. 1/4 で“◎Excel のリスト/データベース(M)”にチェックが入ってることを確かめて
    ［次へ＞］を押す
  4. 2/4 で“範囲(R):”が $A$3:$G$19 であることを確かめて［次へ＞］を押す
  5. PivotWizard 3/4
    3/4 はとりあえず上のように設定する。右側のボタンを必要な箇所にドラッグしてやる。
    左の方の［性別］の下が見にくいけど“ページ(P)”と書いてある
  6. 4/4 では新しいワークシートにするかどうか聞いてくるが、ここでは新しいワークシートにすればいい。
    ここでは“行の総計”を出さないことにするので
    ［オプション(O)...］で“□ 行の総計(T)”のチェックを外して［ＯＫ」を押し、［完了］だ
　そうすると下のようなピボットテーブルが出来上がる。
とりあえず出来たピボットテーブル

　また、ピボットテーブル（Excel95 では、クエリー/ピボット）という
pivot table ツールバーが表示される。
　このツールバーを使ってピボットテーブルウィザードを呼び出したりとか、色々出来るんだけど今回は省略だ。(笑)


　じゃ、ちょっとピボットテーブルを試してみよう。ピボットテーブルの B1 の（すべて）の右にある ▼ をクリックすると男だけ、女だけに絞って表示される。

　
　今度はフィールドの入れ替えをやってみよう。
  1. シートをアクティブにする
  2. ページフィールドの［担当］をドラッグする
  3. そうするとマウスポインタはになる
  4. 列エリアに入るとポインタはになるが
  5. 行エリアではに変わる
  6. マウスポインタがの状態の時、ドロップする
sample1a
　そうすると、右のような結果になる。なお、エリア外にドラッグするとマウスポインタは になるが、この状態の時にドロップするとフィールドが削除されてしまう。

　上の要領で行フィールドと列フィールドの入れ替えが出来るので色々と試して欲しい。別に難しくないので直感で出来ると思う。




1999年 2月 4日（木）第10回

Excel 第一段階チェックテスト
　今日は10回目ということで、第１段階終了のチェックテストだ。Excel の講習は20回なので、時間的には半分終了したことになる。課題の方の消化具合は判らないが、どうやら飛び抜けて遅い方ではなさそうだ。

　さて、テストの課題は右のようなもの。基本的なことが頭に入ってれば別に難しくない。グレーのところは数値を入れずに計算式を入れて算出するのは当然だ。


・タイトル

　B1 に“１〜３月売上実績表”と書いて、フォントは“太字”で 16pt 程度にしてやればいい。
　表の中央に持ってくるには、B1 〜 H1 を選択しておいてツールバーの 範囲内で中央 をクリックすればＯＫ。


・枠の作成

　B3 〜 H1 を選択し
枠のメニューから枠の種類を選んで作ってやればいいんだけど、
一番最初に を選んでやるのがポイントだ。


・項目名

　フォントは太字にしてセル内で中央に配置してやる。３列目の文字は 12pt と少し大きめにしてある
水平方向の中央配置については説明はいらないと思うが、垂直方向の中央配置が隠してある。(笑)　ま、MS Word ほどヘンなところに隠してないので、すぐ発見できるけどね。
  1. 中央配置したい部分を選択しておいて
  2. メニューの［書式(O) - セル(E)...］
  3. ダイアログ ボックスの［配置］タブから“縦位置(V)”で“中央揃え”を選択する
　このダイアログ ボックスを見れば判るけど、ここで水平方向の中央揃えも出来る。


・計算式の入力

　数値の入力は問題ないからパスして、計算式の入力だ。

　９行目の縦の合計は単に C9 に オートサム をクリックして [Enter] を押せばいいから問題ない。あとはその数式をコピーしてやればいいんだけど、調子に乗って“達成率”の列にまで複写しないこと。(笑)

　 G4 の計算式だけど、オートサム をクリックしてやると、=SUM(C4:F4) になってしまうけど、括弧内を C4:E4 に変更する。あとは数式の複写だ。

　“達成率”の計算は、実績／目標のパーセントだけど、ここはまず H4 に =G4/F4 と入れて小数点表示にしてしまう。
　で、H5 〜 H9 に式を複写したあと、ツールバーの ［％］ をクリックした上で％の桁を調節してやればいい。


・印刷

　よけいなところを印刷しないように、範囲指定をする必要がある。
　タイトルから表全体（B1 から H9）を選択しておいて、
メニューから［ファイル(F) - 印刷範囲(T) - 印刷範囲の設定(S)］で、印刷する範囲を決めてやる。

　［ファイル(F) - 印刷プレビュー(V)］で確認して、サイズや印刷位置を確認する。気に入れなければ［ファイル(F) - ページ設定(U)］で変更する。



　ピボットテーブルでの
　　　グループ化
　
グループ化されたピボットテーブル
　さて、テストが終わったら実習の続きだ。前回作ったピボットテーブルのグループ化だ。実は、ピボットテーブルの元になった表が教材と自作のとでは大幅に違うので、教室でやったことの全部が出来ない。表から作りなおすのも面倒なので、ご勘弁乞う。

　右のピボットテーブルは月別にまとめて表示するようにグループ化したものだ。但し、フィールドを入れ替えて、“入社”のフィールドを列に、“住所”のフィールドを行に持って来てある。

　手順
  1. “入社”のフィールドをアクティブにしておく
  2. ピボットテーブルツールバーから ○ をクリックする
  3. グループ化のダイアログボックス
    　上のようなダイアログ ボックスが出るので、
    “単位(B)”で“月”が選ばれているのを確認して［ＯＫ］を押す
　そうすると、上のように日付ごとのアイテム（項目とか条項とか）が、月ごとにまとめられる。

　このあと、教室では新しいグループを作ってフィールドを追加してアイテム名を変更したりしたんだけど、元の表の都合で省略させてもらう。


　グループ化に関して若干補足説明をしておく。

　アイテムの種類によって、グループ化する方法がちょっと違う。
文字列
結合したいアイテムを選択して一つのフィールドを作る
日付・時刻
指定された期間ごとにグループ化できる
開始日と最終日は自動的に選択されるが、変更も可能
数値
10づつ、100づつなど、等分にまとめられたグループを設定する
最小値と最大値は自動的に選択されるが、変更も可能
　ま、やってみれば判るんだけど…。(笑)


　なお、ページフィールドでは、アイテムをグループ化出来ない。だから、ページフィールドのアイテムをグループ化したい場合は、一時的に列フィールドか行フィールドに移してからグループ化し、元へ戻すって方法をとる。


　
レポート

　次に作ったのが、右のようなレポートだ。カラーなんだけど、印刷のプレビューから画像ファイルを作ったんで白黒になっちゃった。(^^;)
　けど、こうして枠付きで表示すると、全体に左により過ぎだな…。(笑)

　で、今回のポイントは
　だいたいこんなところだ。

　Word で文書を作って、Excel の表を Word の方に持ってくることも出来るはずだけど、今回は文書のところも Excel で作ってしまうんだそうだ。

　文字列の表示

　まず、テキストの入力だ。Excel のワークシートに文字列を表示する方法は３種類ほどあるそうだ。
  1. レイアウトを考えながら複数のセルに文字列を割り付ける
  2. 一つのセルに文字列を書いてしまい、あとで範囲指定して配列しなおす
  3. テキスト ボックスを利用する

レポートの文章の部分

　右はレポートの冒頭の文章の部分だ。１行目から７行目まではセルに割り付ける方法だから、説明はいらないと思う。

　10行目からの文章は上記の 2. の方法で入力・配列してある。

  1. A10 のセルに全部の文字を入力する
  2. 割り付ける範囲を指定する（A10 〜 H12）
  3. ［編集(E) - フィル(I) - 文字の割り付け(J)］を選択する


１〜３月店別売上実績
　次は、右の部分だけど、実はこの表の部分は以前に作ったものだ。
　元の表はちょっと色がどぎついんでオートフォーマットでデザインを変えてあるけどね。(笑)

　別のブック（ファイル）から持ってくるんだから、当然、目的の表のあるファイルを開かなければならない。
　その上で貼り付けるんだけど、今回は単純なコピー＆ペーストじゃなく、カメラツールってのを使う。

　カメラツールカメラツール

　カメラツールってのは、ワークシート上の文字列（表を含む）を範囲して、別のセルに画像として貼り付ける（複写する）ツール（機能）だ。
　複写された画像は、移動したりサイズを変更することもかのだし、データは元のファイルと連結（ＯＬＥ）されてる。つまり、元のデータを変更すると、複写された方のデータも変更される。 　なお、貼り付けるセルは同じワークシートにあってもいいし、別のワークシートでもいい。

　で、このカメラツールだけど、標準ではツールバーにないみたいなんだ。だから、次のようにして持って来てやる。実は、このカメラツールをツールバーに持ってくる方法まで Excel95 と 97 じゃ違うんだ。(笑)

　まず Excel97 での方法を書く。
  1. ［表示(V) - ツールバー(T) - ユーザー設定(C)...］を選ぶ
  2. ダイアログ ボックスから［コマンド］タブを選ぶ
  3. “分類(G):” から “ツール” を選び
  4. “コマンド(D):” から “カメラツールカメラ” を見つけて、これをツールバーまでドラッグする
　教室で使った Excel95 では、
  1. ［表示(V) - ツールバー(T)］を選ぶ
  2. ［ツールバー］ダイアログ ボックスから［設定］ボタンをクリックする
  3. ［ユーザー設定］ダイアログ ボックスが表示されるので
    “分類(G)”から “ユーティリティ” を選択する
  4. “ボタン” に カメラツール があるので、これをツールバーまでドラッグする

　準備が出来たら、カメラツールのお出ましだ。
  1. 複写元となるセルの範囲を選択する
  2. カメラツール をクリックする
  3. 複写先のワークシートをアクティブにし、クリックする
  4. クリックした点が左上となって貼り付けられる
　ハンドル ■ が出るので、移動したりサイズを変更する方法は直感で判ると思う。カメラツールで複写した場合、移動やサイズ変更がセル幅に影響されないのが不思議なところだ。


　グラフの貼り付け

売上推移グラフ
　今度はグラフの貼り付けだ。これは前回作った複合グラフだ。さっきの表と同じブックにあるはず。

　実は、テキストをよく読まずに、グラフもカメラツールで貼り付けようとしたんだけど、どうもグラフはカメラに写らないみたいだ。(爆)

　それと、元のグラフを見ると、文字が小さくて貼り付けた時に見にくいみたいな気がした。で、貼り付ける前に元のグラフの文字を大きくしたんだけど、貼り付けたら文字が馬鹿デカくなってしまった。(笑)　よけいなことをせずに、そのまま素直に張りつけりゃ、ちゃんとなる。(笑)

　グラフ貼り付けの手順は次の通りだ。早い話、単純なコピー＆ペーストなんだけどね。(笑)
  1. グラフ全体が選択された状態にする。つまりグラフの周りにハンドル ■ がある状態にする
  2. コピーを選択する（拙者は [Ctrl] + [C] を押す）
  3. 複写先のワークシートをアクティヴにしておき、貼り付け（拙者は [Ctrl] + [V] ）をする
　あとは貼り付けられたグラフの位置や大きさを調節する。


　テキスト ボックス


　さっきから皿うどんが絶好調！とか、落書きしてあるのが気になった人もいるかも知れない。(笑)　これは Word にもあったテキスト ボックスってのを使ってる。
　テキスト ボックスはセルに関係なく、移動したりサイズを変更できたりする。また、書式や輪郭線など、装飾的な面も設定することも出来る。

　テキスト ボックスの作り方は次の通りだ。
  1. （図形描画）をクリックし （テキスト ボックス）をクリックする
  2. 入力したい位置までドラッグしてテキスト ボックスを作る
  3. 文字を入力する
    もし、文字が入り切らなくても
    あとでサイズの変更が出来るから、気にせずに入力すればいい
  4. フォントの変更はテキスト ボックスを右クリックして
    Excel97 の場合は［オートシェイプの書式設定(O)...］から出来る
    Excel95 では［オブジェクトの書式設定］の［フォント］タブでフォントを指定するのだが
    ［パターン］タブで “□影付き(O)” と “□角を丸くする(R)” にチェックを入れる
  5. Excel97 で角を丸くする場合は、［図形の調整(R)］から下のように設定する
    オートシェイプの変更メニュー
  6. 影付きにするには図形描画ツールから
    影付きにする このようにする
  7. 矢印は［図形描画ツール］ので引いてやればいいのは判ると思う

　あと、グラフの中の Ａ店の伸びに注目！ もテキスト ボックスで書いてるけど、こちらはフォントの指定をしたくらいで、輪郭線などは触ってない。






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