E∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
★ あなたの技術をぐ〜んと高めるマガジン ★
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞L
クイズで学ぼう!『エクセル』の小技 151
∞∞∞∞∞∞∞\(^o^)\∞∞∞(*_*);∞∞∞§^O^§∞∞∞∞∞∞∞∞∞∞
◆ 今日の問題 「今日のランキングを表示させるには」
藤原:今日は、前回の技を利用して、ちょっと難しい問題を出そう。
三重:えっ!難しい問題ですか?
藤原:うん、自信のある人は、是非、自力で解いてもらいたい。
佳代:どんな問題ですか?
藤原:たとえば、売上などを毎日記録している人がいると思うけど、今日
の売上が今年の何位かを表示する方法だ。
佳代:えーっと、単に順位をつけるということではないのですね。
藤原:そう、たとえば、こんな表を作ってみる。佳代ちゃんは、ホームペ
ージのアクセス数を記録しているんだろ。
佳代:ハイ。
藤原:それならば、下のような表で考えてみよう。B列は、単純にアクセ
ス数を入力する。そして、前回の技を利用して、D1のセルに、今年
は何日目かを入れて、D2のセルに、今年何番目の順位かを自動的に
表示するようにしてほしい。
┏━━┳━━━━┯━━━━━┯━━━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━━╋━━━━┿━━━━━┿━━━━━━┿━━━━┿━━━━┫
┃ 1┃日付 │アクセス数│今日は │ │日目 ┃
┠──╂────┼─────┼──────┼────┼────┨
┃ 2┃1月1日│ │順位は │ │番目 ┃
┠──╂────┼─────┼──────┼────┼────┨
┃ 3┃1月2日│ │ │ │ ┃
┠──╂────┼─────┼──────┼────┼────┨
┃ 4┃1月3日│ │ │ │ ┃
┠──╂────┼─────┼──────┼────┼────┨
〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜
〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜
┠──╂────┼─────┼──────┼────┼────┨
┃ 366┃12月31日│ │ │ │ ┃
┗━━┻━━━━┷━━━━━┷━━━━━━┷━━━━┷━━━━┛
佳代:なるほど、わぁ〜、難しそうですね。みなさんもいっしょに考えて
くださいね。
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
★ 解答を見る前に,少しだけ考えてね!
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
○ 登場人物紹介
・藤原清盛:塾の先生で,このマガジンの指導役。パソコン暦10年。
・大場佳代:藤原先生の生徒で,パソコン暦1年。
・山形三重:佳代の同級生で,パソコン暦6ヶ月。藤原先生のいとこ。
※ このマガジンは,マイクロソフトのエクセル97,2000のWindows版
のみを扱っています。
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
◆ 解 説 <難易度・・・中級>
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
藤原:まず、D1のセルは 150号で勉強したとおり入れればいい。
佳代:=DATEDIF("2001/12/31",TODAY(),"d")ですね。
藤原:そう。これで、今年の何日目かを表示させることができる。問題は
次だ。単純に順位を出す方法は前に勉強したね。
三重:ハイ。RANK関数です。それならば、マスターしました。
藤原:うん、まず、この関数を知らなければできない。読者の方で、知ら
ない方は、53号を参考にしてほしい。
佳代:でも、それだけではできないのではないですか?
藤原:もちろん。今日のメインテーマは、本日のランキングを自動表示さ
せることだからね。
三重:いったい、どうやるのですか?とてもできそうにありません。
藤原:そうだよね。このメルマガでは、はじめての関数を使う。
佳代:えーっと、ずれに対応できる関数ですね。
藤原:そう。OFFSET関数だ。
三重:オフセット関数ですか?
藤原:うん、この関数は、基準を決めて、相対的に位置を指定できる便利
な関数だ。
三重:う〜ん、何か難しそうですね。公式はどうなっているのですか?
藤原:公式は、=OFFSET(基準値,行数,列数,高さ,幅)だ。
三重:????
藤原:簡単に説明すると、基準値はわかるよね。これは、どこかのセル番
地を入れればいい。
三重:ハイ。
藤原:行数は、その位置から、下へいくごとに、1,2,3・・・と下がる。
佳代:すると、列数は、右へ行くごとに、1,2,3となっていくわけですね。
藤原:そういうこと。上は、-1,-2,-3・・・。左も-1,-2,-3・・・。
三重:つまり、基準のセルから、番号をつけることで、位置を表すのです
ね。
藤原:そのとおり。
佳代:すると、そのあとの高さとか幅とかはどういう意味ですか?
藤原:こちらは、指定するセルの範囲だ。高さが「2」で幅が「1」ならば、
行数と列数で決まった位置から、縦2行1列のセルを指定するとい
うことだけど、今回は、一つのセルを指定すればいいわけだから、
高さ「1」幅「1」で指定すればいい。
佳代:う〜ん、わかりにくいですが、要するに、高さ「1」幅「1」という
ことは、セル一つということですね。
藤原:そう。あまり難しく考えなくていいよ。ほとんど、高さ「1」幅「1」
で使うからね。
三重:わかりました。すると、予定表が下にずれていくわけですから、行
数のところを変化させるようにすればいいわけですね。
┏━━┳━━━━┯━━━━━┯━━━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━━╋━━━━┿━━━━━┿━━━━━━┿━━━━┿━━━━┫
┃ 1┃日付 │アクセス数│今日は │ 16│日目 ┃
┠──╂────┼─────┼──────┼────┼────┨
┃ 2┃1月1日│ │順位は │ │番目 ┃
┠──╂────┼─────┼──────┼────┼────┨
┃ 3┃1月2日│ │ │ │ ┃
┠──╂────┼─────┼──────┼────┼────┨
┃ 4┃1月3日│ │ │ │ ┃
┠──╂────┼─────┼──────┼────┼────┨
〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜
〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜
┠──╂────┼─────┼──────┼────┼────┨
┃ 366┃12月31日│ │ │ │ ┃
┗━━┻━━━━┷━━━━━┷━━━━━━┷━━━━┷━━━━┛
藤原:そのとおり!だから、オフセットの部分は、OFFSET(B1,D1,0,1,1)
にすれば、B1のセルを基準に、D1、つまり何日目にはいる数字が、
行数になる。
佳代:列は、変わりませんから「0」でいいのですね。
藤原:そう、この問題は、あくまでも、同じ列で、何日目が表す数字にし
たがって、ずれていくようにしているわけだから「0」でいい。
三重:でも、これだけでは順位は出ませんよね。
藤原:そのとおり。だから、最初に言ったように、RANK関数を組み合わせ
る。
佳代:あっ!わたし、なんとなくわかりました。RANK関数の検索値に、今
のオフセットを入れればいいんじゃないですか?
藤原:正解。=RANK(OFFSET(B1,D1,0,1,1),$B$2:$B$366,0)
でOK。
三重:なるほど、これならば、その日を基準に、年間の順位が出ますね。
藤原:うん。RANK関数については、先ほども言ったように、よく知らない
人は、53号を参考にしてほしい。そして、ここでは、$
のついた絶
対参照になっているけど、この場合は、コピーする必要もないので、
$ なしでもOK。
佳代:先生、すると、D1のかわりに、D1の式を入れれば単独でも作れるの
ですか?
藤原:作れるよ。長くなるけど、=RANK(OFFSET(B1,DATEDIF("2001/12/31",
TODAY(),"d"),0,1,1),$B$2:$B$366,0)という具合にすれば、単独の
セルで実現することもできる。でも、一つ一つ理解するためにも、
最初は、セルをうまく使ったほうがいいと思うよ。
佳代:わかりました。毎日、何かを記録している人は、是非、試してみて
ください。いつでも、年間順位がリアルタイムにわかるのは、面白
いと思います。それでは、今日はこの辺で。
E∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
★ あなたの技術をぐ〜んと高めるマガジン ★
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞L
クイズで学ぼう!『エクセル』の小技 152
∞∞∞∞∞∞∞\(^o^)\∞∞∞(*_*);∞∞∞§^O^§∞∞∞∞∞∞∞∞∞∞
◆ 今日の問題 「模範解答を使って自己採点するには」
三重:先生、エクセルで自己採点することができますか?
藤原:自己採点?
三重:ハイ。模範解答はあるのですが、自分の解答と比べて、正しければ
○、違っていれば、×をつけたいんです。
佳代:ああ、英単語のテストなんかには有効ね。
藤原:なるほど、それは面白いね。それじゃあ、今日は、それを問題にし
よう。下の表のように、あらかじめ解答した後、模範解答をC列に
挿入したものとして考えよう。
┏━┳━━━━━━┯━━━━━━┯━━━━━━┯━━━━━━┓
┃ ┃A │B │C │D ┃
┣━╋━━━━━━┿━━━━━━┿━━━━━━┿━━━━━━┫
┃1┃意味 │解答 │模範解答 │採点 ┃
┠─╂──────┼──────┼──────┼──────┨
┃2┃1番目 │farst │first │× ┃
┠─╂──────┼──────┼──────┼──────┨
┃3┃2番目 │second │second │○ ┃
┠─╂──────┼──────┼──────┼──────┨
┃4┃3番目 │sard │third │× ┃
┠─╂──────┼──────┼──────┼──────┨
┃5┃4番目 │fourth │fourth │○ ┃
┠─╂──────┼──────┼──────┼──────┨
┃6┃5番目 │fiveth │fifth │× ┃
┗━┻━━━━━━┷━━━━━━┷━━━━━━┷━━━━━━┛
佳代:わかりました。それでは問題です。上の表で、B列とC列を比較し
て正しければ「○」間違っていれば「×」を表示するにはどうした
らいいでしょう。みなさんもいっしょに考えてくださいね。
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
★ 解答を見る前に,少しだけ考えてね!
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
○ 登場人物紹介
・藤原清盛:塾の先生で,このマガジンの指導役。パソコン暦10年。
・大場佳代:藤原先生の生徒で,パソコン暦1年。
・山形三重:佳代の同級生で,パソコン暦6ヶ月。藤原先生のいとこ。
※ このマガジンは,マイクロソフトのエクセル97,2000のWindows版
のみを扱っています。
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
◆ 解 説 <難易度・・・中級>
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
藤原:これは、比較的やさしいよ。
三重:やはり、関数を使うのですか。
藤原:そう。ふたつの文字列を比較して正しいかどうか判断する関数を使
う。
佳代:そんな関数があったのですか?
藤原:うん、EXACT関数だ。
三重:イグザクト関数ですか?
藤原:そうなんだ。公式は、=EXACT(文字列1,文字列2)
だ。正しければ、
TRUE が、間違っていれば FALSE が返される。
佳代:なるほど、文字列は、セル番地を指定すればいいわけですね。
藤原:そのとおり。しかし、これだけだと、D列に、TRUE
や FALSE が入
ってしまうから、例の手を使う。
佳代:わかりました。IF関数と組み合わせるのですね。
藤原:正解。そんなに難しくないだろ。
三重:ハイ。できるような気がします。
佳代:たぶん、D2のセルに =IF(EXACT(B2,C2),"○","×")
と入れればい
いのだと思います。
藤原:うん、正解だ。後はコピーして完了。
三重:この式の意味は、もし、B2のセルの内容とC2のセルの内容が同じだ
ったら○を、違っていたら×を表示しなさいってことですね。
藤原:そういうこと。わかりやすいだろ。
三重:ハイ、単語のテストの勉強などに最適です。
藤原:他にも、いろいろ使い道がありそうだね。何かをチェックするとき
に使えるから。ただ、注意してもらいたいのは、半角全角が違って
いても×が返されるし、大文字小文字が違っていても×になる。
佳代:書式が違っていても×ですか?
藤原:いや、書式の違いは区別しないから、それをチェックすることはで
きない。とにかく、データの中身をチェックするということだ。
佳代:わかりました。英単語のテストの練習だけでなく、似たようなもの、
間違いが発見しにくいもののチェックに使えそうです。みなさんも、
使えそうだなと思ったら、是非使ってみてください。それでは、今
日はこの辺で。
E∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
★ あなたの技術をぐ〜んと高めるマガジン ★
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞L
クイズで学ぼう!『エクセル』の小技 153
∞∞∞∞∞∞∞\(^o^)\∞∞∞(*_*);∞∞∞§^O^§∞∞∞∞∞∞∞∞∞∞
◆ 今日の問題 「不足分を正の数で表すには」
三重:先生、計算の結果、不足している部分はマイナスになりますよね。
藤原:そうだね。原則としては。
三重:でも、不足分とか、減った分を正の数で表したいときもあると思う
んです。
藤原:そうだよね。
三重:それは何とかならないのですか?
佳代:ああ、それなら簡単よ。
三重:あれ?佳代は知っているの?
佳代:まあね。
藤原:それじゃあ、せっかくだから、その簡単な関数を使って問題を出そ
う。こんなのはどうかな?
┏━┳━━━━┯━━━━┯━━━━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━┿━━━━┿━━━━━━━┿━━━━┿━━━━┫
┃1┃在庫数 │人数 │一人あたりの数│不足数 │ ┃
┠─╂────┼────┼───────┼────┼────┨
┃2┃ 56│ 3│ 2│ │ ┃
┠─╂────┼────┼───────┼────┼────┨
┃3┃ 84│ 20│ 6│ 36│ ┃
┠─╂────┼────┼───────┼────┼────┨
┃4┃ 36│ 12│ 5│ 24│ ┃
┗━┻━━━━┷━━━━┷━━━━━━━┷━━━━┷━━━━┛
佳代:不足していない場合は空白のままで、不足しているときに正の数で
入るようにするわけですね。
藤原:そういうこと。もちろん、人数と一人あたりの数をかけて在庫数か
ら引かなければならないよ。
佳代:わかりました。それでは問題です。上の表のD列のように、不足分
がある場合は、その数を正の数で、ない場合は空白にするような表
を作るにはどうしたらいいでしょう。みなさんも一緒に考えてくだ
さいね。
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
★ 解答を見る前に,少しだけ考えてね!
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
○ 登場人物紹介
・藤原清盛:塾の先生で,このマガジンの指導役。パソコン暦10年。
・大場佳代:藤原先生の生徒で,パソコン暦1年。
・山形三重:佳代の同級生で,パソコン暦6ヶ月。藤原先生のいとこ。
※ このマガジンは,マイクロソフトのエクセル97,2000のWindows版
のみを扱っています。
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
◆ 『MOUS上級試験』必勝講座がはじまります ◆
・このメルマガの作者である yosshy が、まぐまぐプレミアムより、
有料のメルマガを発行することになりました。ワード・エクセルの
MOUS上級試験講座です。秋に、上級試験の合格を目指している
方は是非どうぞ。詳しくは、巻末を見てください。
登録はこちら http://www.pat.hi-ho.ne.jp/hirosilk/premium.htm
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
◆ 解 説 <難易度・・・中級>
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
三重:う〜ん、単純にIF関数でできそうですが、わかりません。
藤原:そうだね。もちろん、IF関数は使うのだけれども、もう一つ関数を
知らないとキツイよね。
三重:もう一つの関数って何ですか?。
藤原:うん、絶対値を表す関数だ。
三重:あっ!なるほど、絶対値ですか・・・。確かに絶対値なら、マイナ
スのとき符号を取りますよね。
藤原:そう、数学で習ったよね。
佳代:ええ、絶対値は、距離のようなものを表しますから、マイナスでも
符号を取って表します。aの絶対値は、│a│です。
藤原:いいだろう。その絶対値を返す関数が、ABS関数だ。
三重:すると、=ABS(-1) と入力すると、「1」が返るということですか?
藤原:そのとおり。公式は単純で、=ABS(数値)だ。もちろん、括弧の中に
セル番地を入れて参照させてもいいし、数式を入れてもいい。
佳代:先生、答えを言ってもいいですか?
藤原:うん、いいよ。
佳代:ハイ。D2のセルに、=IF(A2>=B2*C2,"",ABS(A2-(B2*C2)))
って入れ
ればいいのだと思います。
藤原:おう、正解だ。
三重:なるほどなるほど、もし、A2の数値が、B2とC2をかけ合わせたもの
より大きいか等しければ空白って式を作ればいいのね。
藤原:そのとおり。A2の方が大きければ、不足分はないわけだからね。
三重:そして、単純に、A2-(B2*C2)と計算するとマイナスになるわけだか
ら、それをABS関数で絶対値にしているわけですね。
藤原:そういうこと。単に絶対値でやると、プラスもマイナスも区別でき
なくなる。そこで、IFを使って条件をつければいい。
佳代:わかりました。絶対値なんてめったに使わないかなと思っていまし
たが、マイナスに重きがあるものを扱うときは便利ですね。みなさ
んも、是非使ってみてください。それでは今日はこの辺で。
E∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
★ あなたの技術をぐ〜んと高めるマガジン ★
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞L
クイズで学ぼう!『エクセル』の小技 154
∞∞∞∞∞∞∞\(^o^)\∞∞∞(*_*);∞∞∞§^O^§∞∞∞∞∞∞∞∞∞∞
◆ 今日の問題 「掛け算した値を合計するには」
三重:先生、またまた父が難問を・・・。
藤原:えっ!またかい?
三重:そうなんです。前に教えた技が会社で好評だったので調子に乗って
いるんです。
佳代:三重も大変ねぇ・・・(笑)
三重:そうなのよ。なぜか、会社では、わたしがエクセルの天才少女とい
うことになっているんです。
藤原:天才少女ねぇ・・・(笑)。わかったよ。それじゃあ、三重ちゃんが
直接問題を出してよ。
三重:わかりました。下のように備品を購入する表があります。F列は、
普通に SUM関数と掛け算を使って計算しますが、6行目の課別の合
計を出すにはどうしたらいいでしょうか?
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E │F ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃1┃品目 │単価 │第1課 │第2課 │第3課 │合計 ┃
┠─╂────┼────┼────┼────┼────┼────┨
┃2┃鉛筆 │ 50│ 1│ │ │ 50┃
┠─╂────┼────┼────┼────┼────┼────┨
┃3┃消しゴム│ 40│ │
4│ │ 160┃
┠─╂────┼────┼────┼────┼────┼────┨
┃4┃ノート │ 80│ │
2│ │ 160┃
┠─╂────┼────┼────┼────┼────┼────┨
┃5┃クリップ│ 20│ │ │
3│ 60┃
┠─╂────┼────┼────┼────┼────┼────┨
┃6┃ 合 計 │ │ │ │
430┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
佳代:みなさんも一緒に考えてくださいね。
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
★ 解答を見る前に,少しだけ考えてね!
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
○ 登場人物紹介
・藤原清盛:塾の先生で,このマガジンの指導役。パソコン暦10年。
・大場佳代:藤原先生の生徒で,パソコン暦1年。
・山形三重:佳代の同級生で,パソコン暦6ヶ月。藤原先生のいとこ。
※ このマガジンは,マイクロソフトのエクセル97,2000のWindows版
のみを扱っています。
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
◆ 解 説 <難易度・・・中級>
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
佳代:う〜ん、確かに難問ねぇ〜。
三重:そうでしょ。数の合計を入れても計算できないし・・・。
藤原:そうだね。案外、難しいかもしれないね。でも、ある関数を知って
いると、すぐできるよ。
三重:ある関数?
藤原:うん、二つの列があって、お互いに対応しているセル同士を掛け合
わせて、尚且つ合計してくれる関数だ。
佳代:えっ!そんな便利な関数があるのですか?
藤原:うん。SUMPRODUCT関数だ。
三重:サムプロダクト関数・・・ですか?
藤原:そう。前に、PRODUCT関数ってやったよね。
佳代:ハイ。確か、掛け合わせる関数ですね。
藤原:そう、73号でやったから、忘れている人はもう一度見てほしい。
三重:なるほど、掛け合わせたものを合計するんだからSUMPRODUCTという
ことなのですね。
藤原:そういうこと。書式も簡単で、=SUMPRODUCT(セル範囲1,セル範囲2)
でいい。
佳代:すると、問題の表でいうと、C6のセルに、単価の列とC列を指定す
ればいいわけですね。
藤原:そう。=SUMPRODUCT(B2:B5,C2:C5)と入れればいい。でも、今回は、
D列やE列にも入れなければならないから、単価の列は、コピーし
たとき、ずれないように絶対参照にしておいたほうがいい。
佳代:そうですね。すると、=SUMPRODUCT($B$2:$B$5,C2:C5)って具合に、
単価の部分だけ絶対参照にして、列の部分は、相対参照のままに
すればいいわけですね。
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E │F ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃1┃品目 │単価 │第1課 │第2課 │第3課 │合計 ┃
┠─╂────┼────┼────┼────┼────┼────┨
┃2┃鉛筆 │ 50│ 1│ │ │ 50┃
┠─╂────┼────┼────┼────┼────┼────┨
┃3┃消しゴム│ 40│ │
4│ │ 160┃
┠─╂────┼────┼────┼────┼────┼────┨
┃4┃ノート │ 80│ │
2│ │ 160┃
┠─╂────┼────┼────┼────┼────┼────┨
┃5┃クリップ│ 20│ │ │
3│ 60┃
┠─╂────┼────┼────┼────┼────┼────┨
┃6┃ 合 計 │=SUMPRODUCT($B$2:$B$5,C2:C5)│
430┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
藤原:そういうこと、後は、フィルハンドルで、右にコピーしてやればい
い。
三重:わかりました。列同士を掛け算して、合計までしてくれるとは、本
当にありがたい関数ですね。
藤原:そうだね。念のために、F列の計算方法も説明してよ。
三重:ハイ。まず、F2を選択して =SUM(C2:E2)*B2
と入力します。そし
て、フィルハンドルで、F5までコピー。そして、F6を選択して、普
通に、=SUM(F2:F5)ってやればできます。
藤原:OK。これで、この表は完璧だね。
三重:ハイ。父も喜ぶと思います。でも、ますます調子に乗るのが恐いで
す(笑)。
藤原:ま、それだけ勉強になるんだからいいんじゃない?もっとも、不可
能なことを要求されると困るけどね。
佳代:そうですね。でも、勉強になりました。この技も結構使えそうです。
みなさんも是非使ってみてくださいね。それでは、今日はこの辺で。
◆ 補足説明
いちいち入力するのが面倒な人は
ツールバーの[関数の貼り付け]ボタンを押し、「すべてを表示]を選んで
SUMPRODUCTを選んでください。そして、配列1に、B2:B5と入れるか、ボ
ックスの右側のボタンをクリックして、マウスでドラッグしてください。
B2:B5が入ったら、F4を押し「絶対参照($マークがついたもの)」にして、
もう一度同じボタンを押します。そして、配列2も同様にやってみてく
ださい。ただし、配列2の方は、絶対参照にしないので、F4は押さない
でください。
あるいは、=SUMPRODUCT( と入力し、マウスで、B2からB5までドラッグし
て、F4を押し「絶対参照($マークがついたもの)」にして、カンマを打ち、
C2からC5をドラッグしてEnterを押しても大丈夫です。
F4を押しても、うまく絶対参照にならない場合は、F4を数回押してくだ
さい。押すごとに、$マークの付き方が変化します。
E∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
★ あなたの技術をぐ〜んと高めるマガジン ★
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞L
クイズで学ぼう!『エクセル』の小技 155
∞∞∞∞∞∞∞\(^o^)\∞∞∞(*_*);∞∞∞§^O^§∞∞∞∞∞∞∞∞∞∞
◆ 今日の問題 「組み合わせの数を計算するには」
三重:先生、たとえば10人で旅行にいくとしますよね。
藤原:うんうん。
三重:ホテルは、ツインなので、2人1組で部屋を取るのですが、その
組み合わせの数が知りたいんです。
佳代:組み合わせって、けっこうたくさんありそうね。
藤原:確かにね。高校の数学では順列とか組み合わせとか習うけど、エ
クセルでやれば、ものすごく簡単だよ。
三重:それじゃあ、それを今日の問題にしてください。
藤原:わかったよ。こんなのでいいかな?佳代ちゃん、問題を出して。
┏━┳━━━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃1┃人数 │ │ │ │ ┃
┠─╂──────┼────┼────┼────┼────┨
┃2┃一組の数 │ │ │ │ ┃
┠─╂──────┼────┼────┼────┼────┨
┃3┃組み合わせ数│ │ │ │ ┃
┗━┻━━━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
佳代:わかりました。それでは問題です。B1のセルに「人数」を、B2の
セルに「一組の数」を入れて、組み合わせの数がいくつになるか
をB3のセルに表すにはどうしたらいいでしょう?みなさんも一緒
に考えてくださいね。
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
★ 解答を見る前に,少しだけ考えてね!
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
○ 登場人物紹介
・藤原清盛:塾の先生で,このマガジンの指導役。パソコン暦10年。
・大場佳代:藤原先生の生徒で,パソコン暦1年。
・山形三重:佳代の同級生で,パソコン暦6ヶ月。藤原先生のいとこ。
※ このマガジンは,マイクロソフトのエクセル97,2000のWindows版
のみを扱っています。
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
◆ 解 説 <難易度・・・中級>
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
藤原:これは、本当に使い方が簡単な関数だよ。
三重:何という関数ですか?
藤原:うん、COMBIN関数だ。
三重:コンビネーション関数ですか!何かわかりやすいですね。
藤原:そう、COMBINATIONと書くと長いので、COMBINと短くしてある。
佳代:なるほど、確か、コンビネーションには、組合せという意味があり
ました。
藤原:そうだね。コンビなんて言葉もあるぐらいだから、わかりやすいだ
ろ。公式は、=COMBIN(総数,抜き取り数)だ。
三重:問題にあわせると、=COMBIN(人数,一組の数)ということでいいので
すね。
藤原:そういうこと。だから、セル番地を入れればいいわけだ。
┏━┳━━━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃1┃人数 │ │ │ │ ┃
┠─╂──────┼────┼────┼────┼────┨
┃2┃一組の数 │ │ │ │ ┃
┠─╂──────┼────┼────┼────┼────┨
┃3┃組み合わせ数│=COMBIN(B1,B2) │ │ ┃
┗━┻━━━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
三重:あれっ?先生、まだ人数を入力していないと、1が出てしまいます。
藤原:そうだね。気になる人は、例のやつを使って、表示しないようにす
ればいい。
三重:例のやつ?
佳代:ISBLANK関数よ。
三重:あっ、あれね。
藤原:もし、そうしたいのなら、B1かB2のどちらかが空白ならば空白を、
そうでなかったら、コンビネーション関数を計算しなさいという具
合に式を作ればいい。
佳代:任せてください。これで大丈夫ですよね。
=IF(OR(ISBLANK(B1),ISBLANK(B2)),"",COMBIN(B1,B2))
藤原:そのとおり。こうしておけば、両方に数字が入らない限り、何も表
示されない。復習になるけど、一応思い出してね。
佳代:わかりました。組合せの数を知りたいときには便利ですね。みなさ
んも是非試してみてください。それでは今日はこの辺で。
★ エクセルの小技へ ★ ★ TOPのページへ ★ ★ 次へ(156〜160) ★