┌──────────────────────────┐
│【関数コレクションシリーズ】 ==Number.031==│
└──────────────────────────┘
◆ 今日の問題 「条件付で平均する」
Q━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
下のように得点順に並べられた表がある。この表を使って、E2のセル
に東日本だけの平均を、E4のセルに西日本だけの平均を計算したい。
関数を設定せよ。ただし、必ず、AVERAGE関数を使うこと。
┏━┳━━━━━┯━━━┯━━━━┯━━━━┯━━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━━┿━━━┿━━━━┿━━━━┿━━━━━┫
┃1┃氏名 │得点 │所属 │ │東日本平均┃
┠─╂─────┼───┼────┼────┼─────┨
┃2┃大江真理 │ 92│東日本 │ │ ┃
┠─╂─────┼───┼────┼────┼─────┨
┃3┃山坂香織 │ 90│西日本 │ │西日本平均┃
┠─╂─────┼───┼────┼────┼─────┨
┃4┃新藤美智子│ 88│東日本 │ │ ┃
┠─╂─────┼───┼────┼────┼─────┨
┃5┃羽田順子 │ 86│東日本 │ │ ┃
┠─╂─────┼───┼────┼────┼─────┨
┃6┃西山美由紀│ 86│西日本 │ │ ┃
┠─╂─────┼───┼────┼────┼─────┨
┃7┃広橋彩夏 │ 84│西日本 │ │ ┃
┠─╂─────┼───┼────┼────┼─────┨
┃8┃石田百合子│ 80│東日本 │ │ ┃
┠─╂─────┼───┼────┼────┼─────┨
┃9┃幸元西絵 │ 78│東日本 │ │ ┃
┠─╂─────┼───┼────┼────┼─────┨
┃10┃真弓真由美│ 76│東日本 │ │ ┃
┠─╂─────┼───┼────┼────┼─────┨
┃11┃由利百合子│ 70│東日本 │ │ ┃
┠─╂─────┼───┼────┼────┼─────┨
┃12┃真田沙耶香│ 64│西日本 │ │ ┃
┗━┻━━━━━┷━━━┷━━━━┷━━━━┷━━━━━┛
A━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
E2のセルに =AVERAGE(IF(C2:C13="東日本",B2:B13)) と入力して、
Ctrl+Shift+Enter
E4のセルに =AVERAGE(IF(C2:C13="西日本",B2:B13)) と入力して、
Ctrl+Shift+Enter
─────────────────────────────────
・今回は、必ず AVERAGE関数を使うという条件ですので、配列を使いま
す。配列は、いろいろなパターンがあって、一言では言いにくいので
すが、総合的に言うと複数のデータを1つにまとめて計算できる機能
のことで、式を入れた後に、Ctrl+Shift+Enterを打つことによって
確定します。
・今回の場合は、IF関数との組み合わせで、C2:C13の中から東日本を見
つけて、それに対応する数値をB2:B13から取り出して平均するという
意味です。
・これまでにも、何度か取り上げましたので、もう一度、復習してみて
ください。配列が使えると、より幅広くエクセルを使うことができま
す。
↓↓ このあたりを参考にしてください
http://www.pat.hi-ho.ne.jp/hirosilk/exb251.htm#254
http://www.pat.hi-ho.ne.jp/hirosilk/fc011.htm#k015
┌──────────────────────────┐
│【関数コレクションシリーズ】 ==Number.032==│
└──────────────────────────┘
◆ 今日の問題 「支払日を表示する」
Q━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
下図のように、請求されたものをまとめた表がある。支払いは、25日
締めの翌月末払いとなっている。C列に請求日を入れたら、自動的に
D列に支払日が入るように、D列に関数を設定せよ。
┏━┳━━━━━┯━━━━━┯━━━━━┯━━━━━┓
┃ ┃A │B │C │D ┃
┣━╋━━━━━┿━━━━━┿━━━━━┿━━━━━┫
┃1┃請求者名 │金額 │請求日 │支払予定日┃
┠─╂─────┼─────┼─────┼─────┨
┃2┃丸一商事 │ 86,000│2005/6/18 │2005/7/31 ┃
┠─╂─────┼─────┼─────┼─────┨
┃3┃丸二商事 │ 6,600│2005/6/28 │2005/8/31 ┃
┠─╂─────┼─────┼─────┼─────┨
┃4┃丸三商事 │ 10,150│2005/7/1 │2005/8/31 ┃
┠─╂─────┼─────┼─────┼─────┨
┃5┃丸四商事 │ 32,000│2005/7/10 │2005/8/31 ┃
┠─╂─────┼─────┼─────┼─────┨
┃6┃丸五商事 │ 45,000│2005/7/26 │2005/9/30 ┃
┗━┻━━━━━┷━━━━━┷━━━━━┷━━━━━┛
A━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
D2のセルに =EOMONTH(C2,IF(DAY(C2)<=25,1,2)) と入力
フィルハンドルでコピー
─────────────────────────────────
・まず、EOMONTH関数 を使うには、分析ツールをオンにする必要があり
ます。[ツール]−[アドイン]で「分析ツール」にチェックが入っ
ているかどうか確認してください。もし、入っていない場合は、入れ
てください。
・また、結果が、38564 のようにシリアル値になってしまう場合があり
ます。そのときは、Ctrl+Shift+# を押して日付の表示形式にして
ください。([書式]−[セル]の「表示形式]で直してもよい)
・EOMONTH関数は、月末の日付を返す関数です。=EOMONTH(開始月,月数)
で、何ヵ月後、何ヶ月前の月末を取得できます。月数は、当月を「0」
とし、1ヵ月後は「1」、2ヵ月後は「2」という具合になります。ま
た、1ヶ月前は「-1」、2ヶ月前は「-2」という具合にマイナス表示
にします。
・今回は、請求日のセルを開始日にし、25日締めの翌月払いですから、
25日までは「1」を入れて1ヵ月後を、それ以外は、「2」を入れて、
2ヵ月後の月末を求めます。
そのため、IF関数を使い、月数のところを25以下の場合とそれ以外に
場合分けしています。注意してほしいのは、必ず DAY関数を使うこと。
DAY関数 は、=DAY(セル番地)で、日付の中から「日」だけを取り出す
関数です。この関数を使わないと、その月の「日」を判定することが
できません。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ==Number.033==│
└──────────────────────────┘
◆ 今日の問題 「氏名の先頭だけ大文字にする」
Q━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
下図のように、英字で入力されたリストがある。これを先頭文字だけ
大文字にするには?
┏━┳━━━━━━━━━┯━━━━━━━━━━┯━━━━┓
┃ ┃A │B │C ┃
┣━╋━━━━━━━━━┿━━━━━━━━━━┿━━━━┫
┃1┃NAME │ │ ┃
┠─╂─────────┼──────────┼────┨
┃2┃naoto tamura │ │ ┃
┠─╂─────────┼──────────┼────┨
┃3┃kensuke ueno │ │ ┃
┠─╂─────────┼──────────┼────┨
┃4┃kazuki yamamoto │ │ ┃
┠─╂─────────┼──────────┼────┨
┃5┃yuichiro kato │ │ ┃
┠─╂─────────┼──────────┼────┨
┃6┃ryuichi kobayashi │ │ ┃
┠─╂─────────┼──────────┼────┨
┃7┃wataru miyamoto │ │ ┃
┠─╂─────────┼──────────┼────┨
┃8┃ryo nakamura │ │ ┃
┠─╂─────────┼──────────┼────┨
┃9┃kazuya ikenaga │ │ ┃
┗━┻━━━━━━━━━┷━━━━━━━━━━┷━━━━┛
A━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
B2のセルに、=PROPER(A2)と入力
B1のセルを選択して、フィルハンドルをダブルクリック
【結果】
┏━┳━━━━━━━━━┯━━━━━━━━━┯━━━━┓
┃ ┃A │B │C ┃
┣━╋━━━━━━━━━┿━━━━━━━━━┿━━━━┫
┃1┃NAME │ │ ┃
┠─╂─────────┼─────────┼────┨
┃2┃naoto tamura │Naoto Tamura │ ┃
┠─╂─────────┼─────────┼────┨
┃3┃kensuke ueno │Kensuke Ueno │ ┃
┠─╂─────────┼─────────┼────┨
┃4┃kazuki yamamoto │Kazuki Yamamoto │ ┃
┠─╂─────────┼─────────┼────┨
┃5┃yuichiro kato │Yuichiro Kato │ ┃
┠─╂─────────┼─────────┼────┨
┃6┃ryuichi kobayashi │Ryuichi Kobayashi │ ┃
┠─╂─────────┼─────────┼────┨
┃7┃wataru miyamoto │Wataru Miyamoto │ ┃
┠─╂─────────┼─────────┼────┨
┃8┃ryo nakamura │Ryo Nakamura │ ┃
┠─╂─────────┼─────────┼────┨
┃9┃kazuya ikenaga │Kazuya Ikenaga │ ┃
┗━┻━━━━━━━━━┷━━━━━━━━━┷━━━━┛
─────────────────────────────────
・=PROPER(セル番地) で、単語の先頭文字のみ大文字にすることができ
ます。英文を入力する場合、全部小文字で入力してから加工するほう
が楽な場合が多いので、この技は役に立つでしょう。
・ちなみに、
大文字 → 小文字 =LOWER(セル番地)
小文字 → 大文字 =UPPER(セル番地)
です。この3つを覚えておくといいと思います。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ==Number.034==│
└──────────────────────────┘
◆ 今日の問題 「距離と速度から移動時間を求める」
Q━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
距離と速度から移動時間を求めたい。どうしたらいいか? 下の図の
C2のセルに関数を設定し、時刻の形式で表示するようにしなさい。
┏━┳━━━━━━┯━━━━━━┯━━━━━━┯━━━━━━┓
┃ ┃A │B │C │D ┃
┣━╋━━━━━━┿━━━━━━┿━━━━━━┿━━━━━━┫
┃1┃距離(km) │速度(時速) │時間 │ ┃
┠─╂──────┼──────┼──────┼──────┨
┃2┃ 60│ 40│ │ ┃
┗━┻━━━━━━┷━━━━━━┷━━━━━━┷━━━━━━┛
A━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
C2のセルに =A2/B2/24 と入力して、Enter
C2のセルを選択し、[書式]−[セル]の「表示形式」から「時刻」
の「00:00」型を選ぶ
┏━┳━━━━━━┯━━━━━━┯━━━━━━┯━━━━━━┓
┃ ┃A │B │C │D ┃
┣━╋━━━━━━┿━━━━━━┿━━━━━━┿━━━━━━┫
┃1┃距離(km) │速度(時速) │時間 │ ┃
┠─╂──────┼──────┼──────┼──────┨
┃2┃ 60│ 40│=A2/B2/24 │ ┃
┗━┻━━━━━━┷━━━━━━┷━━━━━━┷━━━━━━┛
─────────────────────────────────
・距離と速度から時間を求める公式は「時間=距離÷速さ」です。中学
の数学の定番ですね。単に、=A2/B2 とすれば、答えは、1.5 になり
ます。つまり、1.5 時間ということです。
それだけでも十分役に立ちますが、それを更に、時刻形式で表すには
少し工夫が要ります。そのために、24で割ります。
=A2/B2 ・・・・答えは、10進法・・・ここでは 1.5
=A2/B2/24 ・・・答えは、小数・・・・ここでは 0.0625
時刻は、60進法ですから、エクセルでは、シリアル値で計算されます。
シリアル値とは、1900年1月1日から、1日を1とした連続ナンバー
のことです。
1日が1ですから、時刻は、「0」から始まり「1」で終わります。つ
まり、「0:00」が「0」で、「24:00」が「1」ということです。時刻は、
すべて、この間の小数で表され、小数を使って計算されるのです。
1時間は、1/24時間(0.041667)、12時間は、12/24(0.5)となります。
つまり、10進法で表されたものを24で割れば、時刻のシリアル値が出
るのです。
・しかし、小数では、何時間何分かわかりませんから、時刻の表示形式
にしてわかりやすくします。
☆━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━★
*小技プラス*
今回のように、時刻形式にしたい場合は、セルの書式設定を使う代わ
りに便利な小技があります。
Ctrl+@ (標準形式を時刻形式にする技)
今回の問題で言うと、C2のセルに =A2/B2/24 と入力して、Enterキー
を押した後、C2のセルを選択して、Ctrl+@を打てば、時刻形式にな
ります。
☆━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━★
・今回の技は、車で出かけるときなど役に立ちますね。旅行のときなど、
シミュレーションしてみると面白いかもしれません。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ==Number.035==│
└──────────────────────────┘
◆ 今日の問題 「連続していない日付をカレンダーに入れる」
Q━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
図のように、A列、B列に、日付が連続していないデータがある。こ
れをD列、E列のような連続した日付の表に表示させるにはどうした
らいいか? E2からE12に関数を設定せよ。
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃1┃日付 │売上げ │ │日付 │売上げ ┃
┠─╂────┼────┼────┼────┼────┨
┃2┃ 12月1日│ 123000│ │ 12月1日│ 123000┃
┠─╂────┼────┼────┼────┼────┨
┃3┃ 12月3日│ 142000│ │ 12月2日│ ┃
┠─╂────┼────┼────┼────┼────┨
┃4┃ 12月5日│ 110000│ │ 12月3日│ 142000┃
┠─╂────┼────┼────┼────┼────┨
┃5┃ 12月6日│ 156000│ │ 12月4日│ ┃
┠─╂────┼────┼────┼────┼────┨
┃6┃ 12月8日│ 132000│ │ 12月5日│ 110000┃
┠─╂────┼────┼────┼────┼────┨
┃7┃12月11日│ 122000│ │ 12月6日│ 156000┃
┠─╂────┼────┼────┼────┼────┨
┃8┃ │ │ │ 12月7日│ ┃
┠─╂────┼────┼────┼────┼────┨
┃9┃ │ │ │ 12月8日│ 132000┃
┠─╂────┼────┼────┼────┼────┨
┃10┃ │ │ │ 12月9日│ ┃
┠─╂────┼────┼────┼────┼────┨
┃11┃ │ │ │12月10日│ ┃
┠─╂────┼────┼────┼────┼────┨
┃12┃ │ │ │12月11日│ 122000┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
A━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
E2のセルに、
=IF(ISERROR(VLOOKUP(D2,$A$2:$B$7,2,0)),"",VLOOKUP(D2,$A$2:$B$7,2,0))
と入力して、Enter
E2のセルを選択して、フィルハンドルをダブルクリック
─────────────────────────────────
・まず、VLOOKUP(D2,$A$2:$B$7,2,0) の部分を考えてみましょう。ここ
では、VLOOKUP 関数を使っています。この関数は、指定されたセル範
囲の「左端列」を基準として、それに対応する他の列のセルの内容を
返す関数です。公式は次のとおりです。
=VLOOKUP(検索値,範囲,列番号,検索の型)
ここでは、「検査値(D2)」が「範囲(左側の表)」の左端、つまり日付
と一致する「列番号(2)」、つまり売上げを「検索の型(0)=完全一致」
で表示しなさいということです。
・要は、左の表と同じ日付があったら、その売上げを表示しろという意
味です。 VLOOKUP関数については、これまでも何度か取り上げました
から、わかりにくい方は、あとがきにリンクを貼っておきますから、
そちらで確認ください。
・前半の IF(ISERROR(VLOOKUP(D2,$A$2:$B$7,2,0)),"" の部分は、も
しエラーだったら、空白にしなさいという意味です。
左側の表にない日付に対してはエラーが出てしまうので、この処理を
しておくと、無いものについては空白、有るものについては、対応す
る売上げが表示されます。
☆━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━★
・VLOOKUP 関数に慣れている人ならば、さほど難しくない問題ですが、
そうでない人にとっては、複雑に感じるでしょう。そこで、この関数
のバックナンバーを紹介しておきます。
http://www.pat.hi-ho.ne.jp/hirosilk/exb121.htm#122
一番、典型的な、VLOOKUP関数の説明です。
・今回は、それを日付に応用しました。そして、ポイントは、エラーが
出ないように処理したことです。
ISERROR(検査値) で、エラーのときは、TRUE が返されます。IF関数
を使う場合は、真の場合です。
ですから、もし、VLOOKUP 関数の内容がエラーだったら空白、そうで
ないときは、VLOOKUP 関数の内容を実行しなさいという意味になりま
す。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ==Number.036==│
└──────────────────────────┘
◆ 今日の問題 「常にその月の日数を示す」
Q━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
11月ならば「30」、12月ならば「31」というように、常に、その月の
日数を示すには?
A━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
任意のセルに =DAY(EOMONTH(TODAY(),0))
─────────────────────────────────
(※)EOMONTH関数は、「分析ツール」がアドインされていないと
使用できません。
[ツール]−[アドイン]で「分析ツール」にチェックを
入れてください。
─────────────────────────────────
・EOMONTH 関数は、指定した開始日から、指定した月数の月末の日付を
返す関数です。公式は、=EOMONTH(開始日,月)で、月は、当月を「0」
とし、1ヵ月後は「1」、2ヵ月後は「2」という具合に入れます。ま
た、前月は「-1」という具合に、マイナスで入れます。
・ここでは、当月の日数を表示させたいわけですから、月末の日付の日
の部分だけ取り出せばいいわけです。そのために、DAY 関数と組み合
わせます。
EOMONTH(TODAY(),0) 本日の日付の当月の月末の日付を表す
(11月中ならば、常に、11月30日)
DAY(EOMONTH(TODAY(),0)) その日付の日の部分だけ取り出す
(11月30日の「日」の部分、つまり30)
・毎月、その月の日数を必要とする計算をする場合、こうしておけば、
月が替わってもそのまま使えるので便利です。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ==Number.037==│
└──────────────────────────┘
◆ 今日の問題 「規則性のある文字列の一部だけ取り出す−1」
Q━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
下図のように、総務部経理課とか営業部営業推進課など文字数が違っ
ても、規則性のある文字列(ここでは、○○部○○課)の所属部だけ取
り出したい。B2からB6に関数を設定せよ。
┏━┳━━━━━━━━┯━━━━━━━┯━━━━━━━━┓
┃ ┃A │B │C ┃
┣━╋━━━━━━━━┿━━━━━━━┿━━━━━━━━┫
┃1┃部署名 │所属部 │ ┃
┠─╂────────┼───────┼────────┨
┃2┃総務部経理課 │総務部 │ ┃
┠─╂────────┼───────┼────────┨
┃3┃総務部庶務課 │総務部 │ ┃
┠─╂────────┼───────┼────────┨
┃4┃営業部営業推進課│営業部 │ ┃
┠─╂────────┼───────┼────────┨
┃5┃商品開発部開発課│商品開発部 │ ┃
┠─╂────────┼───────┼────────┨
┃6┃販売促進部企画課│販売促進部 │ ┃
┗━┻━━━━━━━━┷━━━━━━━┷━━━━━━━━┛
A━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
B2のセルに、=LEFT(A2,FIND("部",A2)) と入力
B2のセルを選択して、フィルハンドルをダブルクリック
─────────────────────────────────
・LEFT関数とFIND関数を組み合わせます。
LEFT関数は、=LEFT(文字列あるいはセル番地,文字数)で、左から指
定した文字数を取り出す関数です。
FIND関数は、=FIND(指定した文字,検索する文字列あるいはセル番地)
で、指定した文字が文字列の何番目にあるかを返す関数です。
つまり、FIND関数で「部」の位置が何番目にあるかを調べ、その数を
左から取り出したわけです。
「部」の位置 左から「部」まで
総務部経理課 3番目 総務部
総務部庶務課 3番目 総務部
営業部営業推進課 3番目 営業部
商品開発部開発課 5番目 商品開発部
販売促進部企画課 5番目 販売促進部
このように、共通の文字がある場合、この二つの関数を組み合わせる
ことにより文字列の一部分を取り出すことが出来ます。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ==Number.038==│
└──────────────────────────┘
◆ 今日の問題 「規則性のある文字列の一部だけ取り出す−2」
Q━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
前回と同じように、総務部経理課とか営業部営業推進課など文字数が
違っても、規則性のある文字列(ここでは、○○部○○課)がある。今
回は、その所属課だけ取り出したい。B2からB6に関数を設定せよ。
┏━┳━━━━━━━━┯━━━━━━━┯━━━━━━━━┓
┃ ┃A │B │C ┃
┣━╋━━━━━━━━┿━━━━━━━┿━━━━━━━━┫
┃1┃部署名 │所属課 │ ┃
┠─╂────────┼───────┼────────┨
┃2┃総務部経理課 │経理課 │ ┃
┠─╂────────┼───────┼────────┨
┃3┃総務部庶務課 │庶務課 │ ┃
┠─╂────────┼───────┼────────┨
┃4┃営業部営業推進課│営業推進課 │ ┃
┠─╂────────┼───────┼────────┨
┃5┃商品開発部開発課│開発課 │ ┃
┠─╂────────┼───────┼────────┨
┃6┃販売促進部企画課│企画課 │ ┃
┗━┻━━━━━━━━┷━━━━━━━┷━━━━━━━━┛
A━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
B2のセルに、
=MID(A2,FIND("部",A2)+1,FIND("課",A2)-FIND("部",A2))と入力
B2のセルを選択して、フィルハンドルをダブルクリック
─────────────────────────────────
・前回と似ていますが、今回は、先頭位置が文字列の途中にあります。
そして、その位置は一定ではありません。少し工夫する必要がありま
す。
・そこで、前回使った、FIND関数とMID関数を使います。
前回、FIND関数は、指定した文字が文字列の何番目にあるかを返す関
数だと述べました。繰り返しになりますが、もう一度記しておきます。
書式:=FIND(指定した文字,検索する文字列あるいはセル番地)
MID関数 は、文字列の開始位置から、指定した文字数を取り出す関数
で、公式は次のとおり。
書式:=MID(文字列あるいはセル番地,開始位置,取り出す文字数)
この「開始位置」をFIND関数でうまく取得します。
開始位置 「部」の次から・・・FIND("部",A2)+1
つまり、「部」が文字列の何番目にあるかをFINDで取得し、1を足せ
ばいいのです。すると、開始位置が決まるわけです。
次に「取り出す文字数」は
「課」の位置 − 「部」の位置
で求められます。
FIND("課",A2)−FIND("部",A2)
↓ ↓
「課」の位置 「部」の位置 取り出す文字数
総務部経理課 6番目 3番目 6-3=3
総務部庶務課 6番目 3番目 6-3=3
営業部営業推進課 8番目 3番目 8-3=5
商品開発部開発課 8番目 5番目 8-5=3
販売促進部企画課 8番目 5番目 8-5=3
・こうしておけば、課の後ろに文字が付いても、確実に、課名だけを取
り出すことができます。簡単な応用ですが、案外難しいかもしれませ
ん。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ==Number.039==│
└──────────────────────────┘
◆ 今日の問題 「組合せ数を調べる」
Q━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
正月旅行に12人で出かける予定がある。途中で、タクシーに3人ずつ
に分けて乗るつもりだが、その組み合わせは何通りあるか?下図のC3
のセルに関数を設定せよ。
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃1┃総人数 │1台人数│組合せ数│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃2┃ 12│ 3│ │ │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
A━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
C2のセルに =COMBIN(A2,B2) と入力
【結果】
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃1┃総人数 │1台人数│組合せ数│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃2┃ 12│ 3│ 220│ │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
─────────────────────────────────
・組み合わせを求めるには、COMBIN関数を使います。公式は、
=COMBIN(総数,抜き取り数)
簡単な関数ですが、便利です。部屋割りなどにも使えます。それにし
ても、 220通りもあるとはビックリしますね。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ==Number.040==│
└──────────────────────────┘
◆ 今日の問題 「数字を入れて計算方法を変える」
Q━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
同じデータ表を使って、合計、平均、最大値、最小値などを調べたい。
C2のセルにD列の数字を入れるだけで、その計算が出来るように、B8
に関数を設定せよ。
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃1┃氏名 │得点 │計算方法│ 9│合計 ┃
┠─╂────┼────┼────┼────┼────┨
┃2┃大場佳代│ 1200│ │ 1│平均 ┃
┠─╂────┼────┼────┼────┼────┨
┃3┃阿部沙織│ 1600│ │ 4│最大値 ┃
┠─╂────┼────┼────┼────┼────┨
┃4┃山形三重│ 1400│ │ 5│最小値 ┃
┠─╂────┼────┼────┼────┼────┨
┃5┃高橋由美│ 1300│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃6┃本間貝菜│ 1200│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃7┃惣田ヨネ│ 1100│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃8┃ │ │ │ │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
A━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
B8のセルに =SUBTOTAL(C2,B2:B7) と入力
─────────────────────────────────
・合計するには「SUM関数」平均するなら「AVERAGE関数」のように専用
の関数がありますが、SUBTOTAL関数を使うと、さまざまな計算が可能
になります。
・SUBTOTAL関数の公式は、=SUBTOTAL(種類,範囲)で、種類の欄に計算の
方法を番号で入れます。主なものは次のとおりです。
1 平均値(AVERAGE関数) 2 件数(COUNT関数)
3 件数(COUNTA関数) 4 最大値(MAX関数)
5 最小値(MIN関数) 9 合計値(SUM関数)
・このように設定しておけば、数字を入れ替えるだけでさまざまな計算
をすることが出来ます。SUBTOTAL関数は、非常に器用な関数ですので、
是非、覚えてほしいものです。
★ エクセルの小技へ ★ | ★ TOPのページへ ★ | ★ 次へ(041〜050) ★ |