E∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
★ あなたの技術をぐ〜んと高めるマガジン ★
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞L
クイズで学ぼう!『エクセル』の小技 221
∞∞∞∞∞∞∞\(^o^)\∞∞∞(*_*);∞∞∞§^O^§∞∞∞∞∞∞∞∞∞∞
◆ 今日の問題 「セル範囲をセル番地で選択するには」
三重:先生、B4からH456 まで選択するにはどうしたらいいのですか?
藤原:おっと、いきなり数字で来たね。
三重:ハイ。普通にドラッグ、あるいは、B4のセルを選択して Shiftキー
を押しながら、H456をクリックしてもいいのですが、スクロールが
大変です。
佳代:そうね。そこまで大きいと範囲選択も命がけね(笑)
藤原:わかったよ。それじゃあ、今回は、直接セル番地を指定して範囲を
選択する方法を問題にしよう。
佳代:わかりました。セル範囲を直接セル番地で選択するにはどうしたら
いいでしょう。みなさんも一緒に考えてくださいね。
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
★ 解答を見る前に,少しだけ考えてね!
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
○ 登場人物紹介
・藤原清盛:塾の先生で,このマガジンの指導役。パソコン暦12年。
・大場佳代:藤原先生の生徒で,パソコン暦3年。
・山形三重:佳代の同級生で,パソコン暦2年半。藤原先生のいとこ。
※ このマガジンは,Microsoftのエクセル2000,2002 の Windows版
のみを扱っています。(たいていの場合、97でも使えます)
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
◆ 解 説 <難易度・・・中級>
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
藤原:それじゃあ、早速、結論を言おう。ジャンプを使うんだ。
三重:えっ、ジャンプですか?
佳代:そういえば、ジャンプに[セル選択]というボタンがありましたね。
藤原:そのとおり、そのボタンの上の「参照先」と書いてあるボックスに、
直接書き込めばいいんだ。
三重:なるほど、あそこですか?
藤原:とりあえず、やってごらん。
三重:ハイ。まずは、ジャンプのダイアログボックスを出します。えっと、
[編集]メニューの[ジャンプ]でいいですね。
佳代:ショートカットキーは、Ctrl+G よ。
藤原:そう、F5キーでもいい。よく使う人は覚えておいた方がいいね。
三重:ハイ。そして、下のほうの「参照先」のボックスに、直接書き込め
ばいいのですね。
藤原:そのとおり。通常は、アクティブセルが記されていて、黒くなって
いる。つまり、そのまま上書きできる状態になっているということ。
三重:それじゃあ、書き込みます。B4:H456 でいいですね。
藤原:そう。そして、注意してほしいのは、書き込んだ後、[セル選択]ボ
タンは、押さないこと。必ず、[OK]ボタンを押すこと。
佳代:[セル選択]ボタンは、これまで何回も勉強しました。特殊な選択を
するときに使うのですね。
藤原:そのとおり。その辺は、バックナンバーから探して勉強してほしい。
とにかく、直接セル番地を指定するときは、番地を記入して[OK]
ボタンを押せばいい。
三重:それじゃあ、[OK]ボタンを押します。あっ!見事選択されました。
藤原:セル番地がわかっていて、大きな範囲を選択したいときなどは、非
常に便利。キー操作に慣れている人は、小さい範囲でも便利だと思
うよ。
佳代:わかりました。みなさんも、是非、使ってみてください。それでは、
今日は、この辺で。
<まとめ> 今回の問題に対する手順のみを示します
○ セルB4からH456までを直接セル番地を入れて範囲選択する
ジャンプのダイアログボックスを出す([編集]−[ジャンプ])
(ショートカットキー:Ctrl+G、あるいは、F5)
「参照先」のボックスに、「B4:H456」と入力。
[OK]ボタンを押して完了。
E∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
★ あなたの技術をぐ〜んと高めるマガジン ★
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞L
クイズで学ぼう!『エクセル』の小技 222
∞∞∞∞∞∞∞\(^o^)\∞∞∞(*_*);∞∞∞§^O^§∞∞∞∞∞∞∞∞∞∞
◆ 今日の問題 「空白部分があってもきちんと計算させるには」
佳代:先生、今日は、読者からの質問から出題してください。
藤原:わかったよ。どんな問題?
佳代:ハイ、119号 でやった「個数を入れて累計や残りなどを自動的に入
れるには」で、空白部分を作るとエラーがでるので、それを回避で
きないかというものです。
三重:ああ、あれね。
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃1┃日付 │作業個数│累計 │残り数 │ ┃
┠─╂────┼────┼────┼────┼────┨
┃2┃9月1日│ 15│ 15│ 1985│ ┃
┠─╂────┼────┼────┼────┼────┨
┃3┃9月2日│ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃4┃9月3日│ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃5┃9月4日│ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃6┃9月5日│ │ │ │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
藤原:個数を入れて、累計や残りを出す表だったね。三重ちゃんは、結構、
苦労したんだったね。
三重:ハイ。苦労しました。
藤原:それに対する答えは「C2」のセルに「=IF(ISBLANK(B3),"",C2+B3)」
「D3」のセルには「=IF(ISBLANK(B3),"",D2-B3)」だったね。
三重:そうです。
佳代:しかし、これだと、9月2日を飛ばして、9月3日を入力すると、
エラーが出てしまいます。
藤原:うん。この表は、連続で入力することが前提になっているからね。
それじゃあ、今回は、まったく同じだけど、空白を作っても、エラ
ーが出ないように作ってみよう。
佳代:わかりました。それでは、問題です。上記のように、累計や残り個
数を計算する問題で、空白部分を作っても、きちんと計算されるよ
うにするには、どうしたらいいでしょう? みなさんも一緒に考え
てくださいね。
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
★ 解答を見る前に,少しだけ考えてね!
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
○ 登場人物紹介
・藤原清盛:塾の先生で,このマガジンの指導役。パソコン暦12年。
・大場佳代:藤原先生の生徒で,パソコン暦3年。
・山形三重:佳代の同級生で,パソコン暦2年半。藤原先生のいとこ。
※ このマガジンは,Microsoftのエクセル2000,2002 の Windows版
のみを扱っています。(たいていの場合、97でも使えます)
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
◆ 解 説 <難易度・・・中級>
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
藤原:それじゃあ、答えを言うよ。これは、式自体を変えなければならな
い。
三重:ISBLANK 関数は使わないのですか?
藤原:もちろん、使うよ。でも、最初は、使わないで考えてみよう。
佳代:式の立て方を変えるのですね。
藤原:そうだね。でも、119号では、とりあえず、ISBLANKでやれば〜とい
う感じだったから、式の方は、単純に作った。でも、こういう場合
の式の作り方を説明しよう。
三重:お願いします。
藤原:まずは、全体の個数を入れる欄を作ろう。
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃1┃日付 │作業個数│累計 │残り数 │個数 ┃
┠─╂────┼────┼────┼────┼────┨
┃2┃9月1日│ 15│ 15│ 1985│ 2000┃
┠─╂────┼────┼────┼────┼────┨
┃3┃9月2日│ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃4┃9月3日│ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃5┃9月4日│ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃6┃9月5日│ │ │ │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
三重:そうですね。あの時は、一行目は、手入力でした。元の個数の欄が
あった方がよかったですね。
藤原:そうだね。そして、今回は、B2をクリアして、改めて、C1とD1に式
を入れてみよう。
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃1┃日付 │作業個数│累計 │残り数 │個数 ┃
┠─╂────┼────┼────┼────┼────┨
┃2┃9月1日│ │ │=$E$2-C2│ 2000┃
┠─╂────┼────┼↑───┼────┼────┨
┃3┃9月2日│ │=SUM($B$2:B2) │ ┃
┠─╂────┼────┼────┼────┼────┨
┃4┃9月3日│ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃5┃9月4日│ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃6┃9月5日│ │ │ │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
佳代:なるほど、これならば、間を空けても大丈夫ですね。
藤原:そうなんだ。こういう風に式を作って、コピーすれば、B列に、空
白部分があっても問題なく計算できる。頭の部分だけ、絶対参照に
するのがコツだ。
三重:すると、これに、ISBLANK 関数をかぶせればいいのですね。
藤原:そういうこと。三重ちゃん、結論を言ってごらん。
三重:ハイ、C2のセルには、=IF(ISBLANK(B2),"",SUM($B$2:B2))です。そ
して、D2のセルには、=IF(ISBLANK(B2),"",$E$2-C2)です。
藤原:そうだね。後は、フィルハンドルでコピーすれば、B列に入力され
ない限り、余計な数字は表示されない。そして、飛び飛びに入れて
も大丈夫だ。
佳代:わかりました。式の立て方も、慎重にしなければなりませんね。み
なさんも、ときどき見直してみてください。それでは、今日は、こ
の辺で。
E∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
★ あなたの技術をぐ〜んと高めるマガジン ★
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞L
クイズで学ぼう!『エクセル』の小技 223
∞∞∞∞∞∞∞\(^o^)\∞∞∞(*_*);∞∞∞§^O^§∞∞∞∞∞∞∞∞∞∞
◆ 今日の問題 「一行おきに別の計算をするには」
三重:先生、また、変な質問を受けちゃって。
藤原:おう、どんな質問?
三重:ハイ、こんな感じの表を作っちゃったらしいのです。
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃1┃司商会 │杉並区 │ │合計 │ ┃
┠─╂────┼────┼────┼────┼────┨
┃2┃ 20│ 40│ 60│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃3┃西田商店│足立区 │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃4┃ 30│ 50│ 20│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃5┃田中機械│練馬区 │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃6┃ 40│ 50│ 40│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃7┃戸田製鋼│中央区 │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃8┃ 20│ 40│ 80│ │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
佳代:なるほど、合計欄が一行おきになっているのね。
三重:そうなのよ。そんなのフィルハンドルでコピーして、後で消せばい
いじゃんって言ったんだけど、何百行もあるからダメだって。
藤原:わかったよ。それじゃあ、今日は、これを問題にしよう。
佳代:わかりました。上の表のように、一行おきに式を入れるには、どう
したらいいでしょう? みなさんも一緒に考えてくださいね。
★★ 実際にやってみたい方は、まず、下のリストをコピーしてください。
司商会 杉並区
20 40 60
西田商店 足立区
30 50 20
田中機械 練馬区
40 50 40
戸田製鋼 中央区
20 40 80
★★ そして、A1のセルを選択して、貼り付けし、そのままの状態で、
[データ]−[区切り位置]をクリックし、ウィザードの最初は[次へ]
2番目で、「区切り文字」の「スペース」にチェックをつけてください。
そして、[完了]ボタンを押せば、上記の表のように綺麗に入るはずです。
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
★ 解答を見る前に,少しだけ考えてね!
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
○ 登場人物紹介
・藤原清盛:塾の先生で,このマガジンの指導役。パソコン暦12年。
・大場佳代:藤原先生の生徒で,パソコン暦3年。
・山形三重:佳代の同級生で,パソコン暦2年半。藤原先生のいとこ。
※ このマガジンは,Microsoftのエクセル2000,2002 の Windows版
のみを扱っています。(たいていの場合、97でも使えます)
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
◆ 解 説 <難易度・・・中級>
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
藤原:とりあえず、IF関数を使って、場合分けする。
三重:それは、わたしにもわかります。でも、どうやって分けたらいいの
かわかりません。
藤原:うん。これはね。割り算の余りをうまく使うんだ。
佳代:あっ、MOD関数ですね。
三重:えっ!そんなのあったっけ?
藤原:確かやったと思うけど。
佳代:調べてみます。あっ、2回もやっています。114号と172号です。
三重:あっ、思い出しました。しばらくやっていなかったので忘れていま
した。
藤原:そうだね。使わないと忘れてしまうよね。この関数で、割り算して、
余りを条件にすればいい。
佳代:2で割るのですね。
藤原:そう。元になる数字は、行番号でいい。
三重:ああ、それなら覚えています。ROW関数ですね。
藤原:おう、そちらは覚えていたね。
三重:ハイ。連続番号などで使っていますから。
藤原:ここまで言えば、君たちでもできるだろ。
佳代:ハイ、任せてください。えっと、D2のセルに、こんな感じで入れれ
ばいいのでは? と思います。
=IF(MOD(ROW(),2)=0,SUM(A2:C2),"")
藤原:よろしい正解だ。三重ちゃん、関数の意味を言ってごらん。
三重:ハイ、もし、行番号を2で割った余りがゼロだったら、A2からC2の
合計を出しなさい。そうでなければ空白にしなさいということです。
行番号を ゼロならば 空白にせよ
↓ ↓ ↓
=IF (MOD (ROW(), 2)=0, SUM(A2:C2), "")
↑ ↑ ↑ ↑
もし 割った余りが 2で 合計せよ
藤原:そのとおり。後は、フィルハンドルでコピーすればいい。
★ 完成図
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃1┃司商会 │杉並区 │ │合計 │ ┃
┠─╂────┼────┼────┼────┼────┨
┃2┃ 20│ 40│ 60│ 120│ ┃
┠─╂────┼────┼────┼────┼────┨
┃3┃西田商店│足立区 │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃4┃ 30│ 50│ 20│ 100│ ┃
┠─╂────┼────┼────┼────┼────┨
┃5┃田中機械│練馬区 │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃6┃ 40│ 50│ 40│ 130│ ┃
┠─╂────┼────┼────┼────┼────┨
┃7┃戸田製鋼│中央区 │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃8┃ 20│ 40│ 80│ 140│ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
藤原:できれば、こんな表を作らない方がいいけど、一行おきに、違う計
算をしたりするときも使える。これを元に、いろいろ応用してほし
いものだね。
佳代:そうですね。みなさんも、いろいろアイデアを出して使ってみてく
ださい。それでは、今日は、この辺で。
<まとめ> 今回の問題に対する手順のみを示します
セルD2を選択
=IF(MOD(ROW(),2)=0,SUM(A2:C2),"") と入力
フィルハンドルでコピー
<参 考>
○ IF関数 条件によって動作を変える関数
《 =IF(論理式,真の場合,偽の場合) 》
詳しくは http://www.pat.hi-ho.ne.jp/hirosilk/exb036.htm
○ MOD関数 割り算の余りを返す関数 《 =MOD(数値,除数) 》
詳しくは http://www.pat.hi-ho.ne.jp/hirosilk/exb111.htm#114
○ ROW関数 行番号を返す関数 《 =ROW() 》
詳しくは http://www.pat.hi-ho.ne.jp/hirosilk/exb156.htm
E∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
★ あなたの技術をぐ〜んと高めるマガジン ★
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞L
クイズで学ぼう!『エクセル』の小技 224
∞∞∞∞∞∞∞\(^o^)\∞∞∞(*_*);∞∞∞§^O^§∞∞∞∞∞∞∞∞∞∞
◆ 今日の問題 「品目別に現時点での合計を調べるには」
三重:先生、たまには、直接、問題を出してください。
藤原:わかったよ。それじゃあ、こんなのはどう?これまで勉強したもの
を組み合わせただけだけど。
┏━┳━━━━┯━━━━┯━━━━━┯━━━━━━━━┓
┃ ┃A │B │C │D ┃
┣━╋━━━━┿━━━━┿━━━━━┿━━━━━━━━┫
┃1┃品目 │売上げ │品目選択 │ ┃
┠─╂────┼────┼─────┼────────┨
┃2┃コーヒー│ 1200 │品目別合計│ ┃
┠─╂────┼────┼─────┼────────┨
┃3┃紅茶 │ 1000│ │ ┃
┠─╂────┼────┼─────┼────────┨
┃4┃紅茶 │ 1600│ │ ┃
┠─╂────┼────┼─────┼────────┨
┃5┃コーヒー│ 2400│ │ ┃
┠─╂────┼────┼─────┼────────┨
┃6┃ココア │ 1300│ │ ┃
┠─╂────┼────┼─────┼────────┨
┃7┃紅茶 │ 2200│ │ ┃
┠─╂────┼────┼─────┼────────┨
┃8┃ココア │ 1500│ │ ┃
┠─╂────┼────┼─────┼────────┨
┃9┃緑茶 │ 3200│ │ ┃
┗━┻━━━━┷━━━━┷━━━━━┷━━━━━━━━┛
佳代:つまり、品目選択のところに、コーヒーを入れると、コーヒーだけ
の合計が出るようにすればいいのですね。
藤原:そう。でも、それだけだと簡単すぎるから、A列、B列のデータは、
今後、どんどん増えていくという前提で作ることと、D1のセルでは、
コーヒーと紅茶、ココアだけは、リストから選べるようにし、その
他の品目は、直接入力して出すようにしてもらいたい。
佳代:わかりました。それでは、問題をまとめます。上記の表のD1のセル
に、A列の品目を入れると、D2のセルに、その品目だけ合計を出せ
るようにするにはどうしたらいいでしょう?ただし、A列、B列と
もに、今後、どんどんデータは増えていきます。また、D1のセルで
は、コーヒー、紅茶、ココアは、リストから選べるようにしてくだ
さい。
★★ 実際にやってみたい方は、まず、下のリストをコピーしてください。
品目 売上げ 品目選択
コーヒー 1200 品目別合計
紅茶 1000
紅茶 1600
コーヒー 2400
ココア 1300
紅茶 2200
ココア 1500
緑茶 3200
★★ そして、A1のセルを選択して、貼り付けし、そのままの状態で、
[データ]−[区切り位置]をクリックし、ウィザードの最初は[次へ]
2番目で、「区切り文字」の「スペース」にチェックをつけてください。
そして、[完了]ボタンを押せば、上記の表のように綺麗に入るはずです。
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
★ 解答を見る前に,少しだけ考えてね!
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
○ 登場人物紹介
・藤原清盛:塾の先生で,このマガジンの指導役。パソコン暦12年。
・大場佳代:藤原先生の生徒で,パソコン暦3年。
・山形三重:佳代の同級生で,パソコン暦2年半。藤原先生のいとこ。
※ このマガジンは,Microsoftのエクセル2000,2002 の Windows版
のみを扱っています。(たいていの場合、97でも使えます)
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
◆ 解 説 <難易度・・・中級>
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
三重:これまで、勉強した技を組み合わせればできますね。
藤原:そうだね。ただし、慎重にやってね。
佳代:わかりました。三重と二人で作ってみます。
三重:まずは、D1のセルをリストから選べるようにします。
佳代:やり方は、D1のセルを選択して[データ]メニューの[入力規則]
を選び、「設定」のタブを開きます。
三重:そして「条件の設定」の「入力値の種類」を「リスト」にします。
佳代:今回は、リストに設定する項目が3つしかありませんから、「元の
値」の欄に、直接「コーヒー,紅茶,ココア」と入力します。
藤原:おう、さすがだね。これまでは、実際にリストを作って、そこから
引っ張ってきたけど、今回は、直接指定だね。
三重:ハイ。データが少ないときは、その方が簡単です。しかし、このま
まだと、リスト以外のものを入力することはできません。
佳代:そこで、「エラーメッセージ」のタブを開き「無効なデータが出た
らエラーメッセージを表示する」のチェックを外します。
藤原:そうだったね。これは、比較的最近やった技だね。
三重:そうですね。これで、コーヒー、紅茶、ココアは、リストから選択
できるし、それ以外のものも直接入力できるようになりました。こ
れで、D1のセルは完了です。
藤原:うん、いいだろう。D1のセルを選択すると、右側に▼マークがつい
ていれば成功だ。
佳代:大丈夫です。▼マークを押すと、コーヒー、紅茶、ココアから選べ
るようになっています。
三重:それに、直接、緑茶などと入力することもできます。
藤原:よろしい。これで、完全に成功だ。
佳代:次に、D2に移ります。こちらは、「SUMIF関数」を使います。
三重:この関数は、条件にあったものだけを合計する関数です。公式は、
=SUMIF(範囲,検索条件,合計範囲)
です。
藤原:うん、今回は、この引数の部分をうまく作ってほしいね。
佳代:任せてください。まず、範囲は、これからずっとA列にデータが入
るのですから、A:A と設定します。検索条件は、D1のセルに入った
ものを参照にすればいいわけですから、D1 として、合計範囲は、
B列ですから、B:B です。
三重:まとめると、=SUMIF(A:A,D1,B:B)ということです。
┏━┳━━━━┯━━━━┯━━━━━┯━━━━━━━━┓
┃ ┃A │B │C │D ┃
┣━╋━━━━┿━━━━┿━━━━━┿━━━━━━━━┫
┃1┃品目 │売上げ │品目選択 │ ┃▼
┠─╂────┼────┼─────┼────────┨
┃2┃コーヒー│ 1200 │品目別合計│=SUMIF(A:A,D1,B:B)
┠─╂────┼────┼─────┼────────┨
┃3┃紅茶 │ 1000│ │ ┃
藤原:よろしい。さすがに間違えないね。
三重:もちろんです。任せてください!
佳代:これで、D1のセルを選択し、コーヒーを選ぶと
┏━┳━━━━┯━━━━┯━━━━━┯━━━━━━━━┓
┃ ┃A │B │C │D ┃
┣━╋━━━━┿━━━━┿━━━━━┿━━━━━━━━┫
┃1┃品目 │売上げ │品目選択 │コーヒー ┃▼
┠─╂────┼────┼─────┼────────┨
┃2┃コーヒー│ 1200 │品目別合計│ 3600┃
┠─╂────┼────┼─────┼────────┨
┃3┃紅茶 │ 1000│ │ ┃
こんな感じで、きちっと計算されます。もちろん、何を選んでも計
算できます。
藤原:一つ一つの技が、きちんとできていれば、難しくないよね。いろい
ろな技を組み合わせて、役に立つものを作ってほしいと思う。
佳代:わかりました。みなさんも、是非、組み合わせて役立ててください。
それでは、今日は、この辺で。
<まとめ> 今回の問題に対する手順のみを示します
「D1」のセルを選択
[データ]−[入力規則]の「設定」のタブを開く
「条件の設定」の「入力値の種類」を「リスト」にする
「元の値」に、 コーヒー,紅茶,ココア と入力(カンマ区切り)
「エラーメッセージ」のタブを開き「無効なデータが出たらエラー
メッセージを表示する」のチェックを外す
[OK]ボタンをクリック
「D2」のセルを選択
=SUMIF(A:A,D1,B:B) と入力し、Enterキーを押して完了
E∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
★ あなたの技術をぐ〜んと高めるマガジン ★
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞L
クイズで学ぼう!『エクセル』の小技 225
∞∞∞∞∞∞∞\(^o^)\∞∞∞(*_*);∞∞∞§^O^§∞∞∞∞∞∞∞∞∞∞
◆ 今日の問題 「条件によって選択用のリストを変更させるには」
三重:先生、前回の技で思いついたのですが、条件によって、出てくるリ
ストを変化させることはできますか?
藤原:おう、結構、高級なことを言うね。
佳代:えーっと、それって、同じセルで、条件によって違うリストが出る
ようにするってこと?
三重:そうよ。例えば、こんなやつ
┏━┳━━━━━┯━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━━┿━━━┿━━━━┿━━━━┿━━━━┫
┃1┃氏 名 │リーグ│チーム │ │ ┃
┠─╂─────┼───┼┌────┐───┼────┨
┃2┃阿部慎之助│セ ││巨人 │▼ │ ┃
┠─╂─────┼───┼┼ヤクルト┼───┼────┨
┃3┃松坂大輔 │パ ││中日 │ │ ┃
┠─╂─────┼───┼┼阪神 │───┼────┨
┃4┃井川 慶 │セ ││広島 │ │ ┃
┗━┻━━━━━┷━━━┷│横浜 │━━━┷━━━━┛
└────┘
┏━┳━━━━━┯━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━━┿━━━┿━━━━┿━━━━┿━━━━┫
┃1┃氏 名 │リーグ│チーム │ │ ┃
┠─╂─────┼───┼────┼────┼────┨
┃2┃阿部慎之助│セ │ │ │ ┃
┠─╂─────┼───┼────┼────┼────┨
┃3┃松坂大輔 │パ │┌─────▼ │ ┃
┠─╂─────┼───┼│西武 │──┼────┨
┃4┃井川 慶 │セ ││近鉄 │ │ ┃
┗━┻━━━━━┷━━━┷│ダイエー │━━┷━━━━┛
│ロッテ │
│日本ハム │
│オリックス│
└─────┘
佳代:つまり、C列のチームのところにリストを作るのね。
三重:そうなのよ。セ・リーグの選手の場合は、セ・リーグだけのリスト、
パ・リーグの選手には、パ・リーグだけのリストが出るようにした
いの。
藤原:わかったよ。それじゃあ、今日は、それを問題にしよう。
佳代:わかりました。それでは、問題です。C列のチーム名のところに、
B列に入っているリーグの名前に応じたチームのリストを出すには
どうしたらいいでしょう? みなさんも一緒に考えてくださいね。
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
★ 解答を見る前に,少しだけ考えてね!
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
○ 登場人物紹介
・藤原清盛:塾の先生で,このマガジンの指導役。パソコン暦12年。
・大場佳代:藤原先生の生徒で,パソコン暦3年。
・山形三重:佳代の同級生で,パソコン暦2年半。藤原先生のいとこ。
※ このマガジンは,Microsoftのエクセル2000,2002 の Windows版
のみを扱っています。(たいていの場合、97でも使えます)
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
◆ 解 説 <難易度・・・中級>
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
藤原:それじゃあ、おおまかな手順を言うよ。まずは、チームのリストを
他のシートに作る。そして、それぞれ名前をつける。次に、前回も
登場した、入力規則のリストを使うということだ。
三重:それは、わかっています。前にもやりました。
藤原:そうだね。何回かやっているよね。今回は、リーグ名に「セ」とあ
ったら、チーム名のところに、セ・リーグのリストが、「パ」とあ
ったら、パ・リーグのリストが出るようにするわけだよね。
三重:そうです。
佳代:B列の文字列を参照にしてリストを切り替えればいいのですね。
藤原:うん。できるかな?
三重:さっぱりわかりません。
藤原:実は、ずっと昔だけど、似たようなものをやったことがあるよ。
佳代:えっ、ありましたっけ?
藤原:あったよ。間接的な値を返す関数だ。
佳代:間接的?えーっと、間接、間接・・・あっ!思い出しました。
三重:えっ?どんな関数。
佳代:INDIRECT関数よ。
三重:インダイレクト?
佳代:ホラ、メチャクチャ、難しい問題があったじゃない。
藤原:そう。225号 を迎えた現在でも、一番難しい問題かもしれない。確
か、2年以上前にやった技だよ。
佳代:えっと、調べてみます・・・。あっ、ありました。46号と47号です。
2000年の12月です。
藤原:そのINDIRECT関数は、セルに入っているセル番地やセル範囲を参照
にして値を返すという関数だ。
三重:あっ、少し思い出してきました。確か、私の予定表に使ったはずで
す。
藤原:うん。そのためにやった技だからね。
佳代:すると、「セ」とか「パ」は、名前の定義をしてありますから、そ
のセル範囲を示してくれるということですね。
藤原:そういうこと。口で言うとわかりにくいから、具体的にやってみよ
う。
三重:ハイ、それでは、順を追ってやります。まずは、他のシートをあけ
て、リストを作ります。
【図1】 別のシートにリスト作る
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃1┃セリーグ│パリーグ│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃2┃巨人 │西武 │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃3┃ヤクルト│近鉄 │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃4┃中日 │ダイエー│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃5┃阪神 │ロッテ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃6┃広島 │日本ハム│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃7┃横浜 │オリックス │ │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
そして、A2からA7までを選択して、名前ボックスの上をクリックし
て「セ」と名前をつけます。同様に、B2からB7を選択して、名前ボ
ックスの上をクリックし「パ」と名前をつけます。
藤原:うん、いいね。それで、名前は付けられたわけだ。
三重:次に、元の表に戻って、C2のセルを選択します。
【図2】 元のシートに戻る
┏━┳━━━━━┯━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━━┿━━━┿━━━━┿━━━━┿━━━━┫
┃1┃氏 名 │リーグ│チーム │ │ ┃
┠─╂─────┼───┼────┼────┼────┨
┃2┃阿部慎之助│セ │ ←のセルを選択して ┃
┠─╂─────┼───┼────[データ]−[入力規則]
┃3┃松坂大輔 │パ │ │を選ぶ │ ┃
┠─╂─────┼───┼────┼────┼────┨
┃4┃井川 慶 │セ │ │ │ ┃
┗━┻━━━━━┷━━━┷━━━━┷━━━━┷━━━━┛
そして、[データ]メニューの[入力規則]を開き「設定」のタブ
から「入力値の種類」を「リスト」にします。
藤原:うん、それで、元の値のところに、=INDIRECT(B2) と入れてあげれ
ばいい。
佳代:なるほど、それならば、元の値が、B2に入っているセル範囲を指定
できるのですね。
藤原:そういうこと。それじゃあ、フィルハンドルでコピーしてから、松
坂大輔のところをクリックしてごらん。
┏━┳━━━━━┯━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━━┿━━━┿━━━━┿━━━━┿━━━━┫
┃1┃氏 名 │リーグ│チーム │ │ ┃
┠─╂─────┼───┼────┼────┼────┨
┃2┃阿部慎之助│セ │ │ │ ┃
┠─╂─────┼───┼────┼────┼────┨
┃3┃松坂大輔 │パ │┌─────▼ │ ┃
┠─╂─────┼───┼│西武 │──┼────┨
┃4┃井川 慶 │セ ││近鉄 │ │ ┃
┗━┻━━━━━┷━━━┷│ダイエー │━━┷━━━━┛
│ロッテ │
│日本ハム │
│オリックス│
└─────┘
三重:ハイ、ちゃんとパ・リーグのリストが出ます。
藤原:もちろん、リーグを書き換えれば、自動的にリストも変わる。これ
が、INDIRECT関数を使用したメリットだね。
佳代:わかりました。INDIRECT関数は、結構、応用範囲が広いようですね。
みなさんも、是非、試してみてください。それでは、今日は、この
辺で。
-----------------------------------------------------------------
<まとめ> 今回の問題に対する手順のみを示します
-----------------------------------------------------------------
他のシートに、セリーグ、パリーグのリストを作る(図1)
A2からA7までを選択して、名前ボックスの上をクリックし、選択状態
にして「セ」と入力し、Enterキー を2回押す
B2からB7までを選択して、名前ボックスの上をクリックし、選択状態
にして「パ」と入力し、Enterキー を2回押す
※ Enter キーは、必ず2回押してください。1回目は、文字を
確定するため、2回目は、名前を確定するためです。
元のシートに戻る(図2)
C2のセルを選択
[データ]メニューの[入力規則]を開き「設定」のタブから「入力
値の種類」を「リスト」にする
「元の値」のところに、=INDIRECT(B2)と入力して[OK]をクリック
C2のセルを選択して、フィルハンドルで下方向にコピーして完了
-----------------------------------------------------------------
・これで、C列のセルを選択すると、右側に▼ボタンが現れ、このボタ
ンをクリックすると、B列に「セ」が入っているときは、セ・リーグ
のリスト、「パ」が入っているときは、パ・リーグのリストが表示さ
れます。そこから、該当のチームをクリックして選んでください。
-----------------------------------------------------------------
<補足>
-----------------------------------------------------------------
・▼ボタンをクリックする変わりに、Alt+↓キーでもリストが出ます。
キーボードでやりたい方は、こちらでどうぞ。矢印キーで選択して、
Enter を押せば選択できます。
-----------------------------------------------------------------
<参考> 今回出てきた技に関連したものを示します
-----------------------------------------------------------------
・「入力規則」「他のシートのリストから選択」
http://www.pat.hi-ho.ne.jp/hirosilk/exb161.htm#164
・「INDIRECT関数」
http://www.pat.hi-ho.ne.jp/hirosilk/exb046.htm
★ エクセルの小技へ ★ | ★ TOPのページへ ★ | ★ 次へ(226〜230) ★ |