┌──────────────────────────┐
   │【関数コレクションシリーズ】   ==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) ★