┌──────────────────────────┐
│【関数コレクションシリーズ】 ＝＝Number.021＝＝│
└──────────────────────────┘
◆ 今日の問題 「特定なものだけ合計する」
Ｑ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
下図のメーカー「アブジャ」の合計をC12に記しなさい。
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃Ａ │Ｂ │Ｃ │Ｄ │Ｅ ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃１┃日付 │商品名 │メーカー│金額 │ ┃
┠─╂────┼────┼────┼────┼────┨
┃２┃8/1 │ベッカー│トキオ │ 2400│ ┃
┠─╂────┼────┼────┼────┼────┨
┃３┃8/1 │ロナドー│アブジャ│ 3600│ ┃
┠─╂────┼────┼────┼────┼────┨
┃４┃8/1 │ジーダン│トキオ │ 1800│ ┃
┠─╂────┼────┼────┼────┼────┨
┃５┃8/1 │アジャ │アブジャ│ 2000│ ┃
┠─╂────┼────┼────┼────┼────┨
┃６┃8/1 │ドーハ │バツータ│ 2600│ ┃
┠─╂────┼────┼────┼────┼────┨
┃７┃8/1 │マイム │バツータ│ 2200│ ┃
┠─╂────┼────┼────┼────┼────┨
┃８┃8/2 │マイム │バツータ│ 2200│ ┃
┠─╂────┼────┼────┼────┼────┨
┃９┃8/2 │ベッカー│トキオ │ 2400│ ┃
┠─╂────┼────┼────┼────┼────┨
┃10┃8/2 │ロナドー│アブジャ│ 3600│ ┃
┠─╂────┼────┼────┼────┼────┨
┃11┃ │ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃12┃ アブジャ合計 │ │ │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
Ａ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
C12に =SUMIF(C2:C10,"アブジャ",D2:D10)と入力して、Enter
─────────────────────────────────
・今回は、基本問題です。これまでにも取り上げたことがあります。非
常にわかりやすく、実用的な関数ですので、しっかりマスターしてく
ださい。
・SUMIF関数は、=SUMIF(範囲，検索条件，合計範囲) で設定します。
範囲 検索する数値や文字列が含まれる範囲
検索条件 検索する数値や文字列（文字列の場合は、"" で囲む）
合計範囲 計算する数値が入っている範囲
つまり、SUMIF関数 は、条件付き合計式ということです。一つの表か
ら、特定のものだけ合計したいときに使います。
☆━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━★
・検索条件は、直接入れるのでなく、セル番地を入れておいて変化させ
ることもできます。上手に使ってください。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ＝＝Number.022＝＝│
└──────────────────────────┘
◆ 今日の問題 「部分的に一致するものだけ合計する」
Ｑ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
下の表から、北関東と南関東の合計だけを計算し、E12に関数を設定
しなさい。
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃Ａ │Ｂ │Ｃ │Ｄ │Ｅ ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃１┃日付 │商品名 │メーカー│支店名 │金額 ┃
┠─╂────┼────┼────┼────┼────┨
┃２┃8/1 │ベッカー│トキオ │北関東 │ 2400┃
┠─╂────┼────┼────┼────┼────┨
┃３┃8/1 │ロナドー│アブジャ│九州 │ 3600┃
┠─╂────┼────┼────┼────┼────┨
┃４┃8/1 │ジーダン│トキオ │南関東 │ 1800┃
┠─╂────┼────┼────┼────┼────┨
┃５┃8/1 │アジャ │アブジャ│四国 │ 2000┃
┠─╂────┼────┼────┼────┼────┨
┃６┃8/1 │ドーハ │バツータ│南関東 │ 2600┃
┠─╂────┼────┼────┼────┼────┨
┃７┃8/1 │マイム │バツータ│北関東 │ 2200┃
┠─╂────┼────┼────┼────┼────┨
┃８┃8/2 │マイム │バツータ│北関東 │ 2200┃
┠─╂────┼────┼────┼────┼────┨
┃９┃8/2 │ベッカー│トキオ │南関東 │ 2400┃
┠─╂────┼────┼────┼────┼────┨
┃10┃8/2 │ロナドー│アブジャ│東北 │ 3600┃
┠─╂────┼────┼────┼────┼────┨
┃11┃ │ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃12┃ │ │北関東・南関東合計│ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
Ａ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
E12のセルに =SUMIF(D2:D10,"*関東",E2:E10)と入力して、Enter
─────────────────────────────────
・単に、北関東だけの合計ならば、前回同様、SUMIF関数を使って
=SUMIF(D2:D10,"北関東",E2:E10)
とやれば、北関東の合計が出ます。南関東だけならば
=SUMIF(D2:D10,"南関東",E2:E10)
とやればいいのです。しかし、北関東、南関東の両方を計算したいと
きは、ワイルドカードの「*(アスタリスク)」を使います。
アスタリスクは、任意の数文字という意味ですから、北であれ南であ
れ、後ろに「関東」がつくものが対象になります。
☆━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━★
・今年は、アスタリスクというヒット曲が出たため、「＊」の知名度も
上がりましたね。SUMIF関数は、このアスタリスクを使うことにより、
利便性が高まります。是非使ってみてください。
・前回も説明しましたが、初めての方のために、補足しておきます。
SUMIF関数 =SUMIF(範囲，検索条件，合計範囲)
範囲 検索する数値や文字列が含まれる範囲
検索条件 検索する数値や文字列（文字列の場合は、"" で囲む）
合計範囲 計算する数値が入っている範囲
┌──────────────────────────┐
│【関数コレクションシリーズ】 ＝＝Number.023＝＝│
└──────────────────────────┘
◆ 今日の問題 「円書式にした文字列に変換する」
Ｑ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
下図の様に、１円単位を四捨五入して、頭に「￥」マークを付け、桁
区切りをした文字列にしたい。関数を設定せよ。
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃Ａ │Ｂ │Ｃ │Ｄ │Ｅ ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃１┃価格 │表示価格│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃２┃ 1244│\1,240 │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃３┃ 146│\150 │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃４┃ 562│\560 │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃５┃ 1488│\1,490 │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃６┃ 455│\460 │ │ │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
Ａ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
B2のセルに =YEN(A2,-1) と入力してEnter
フィルハンドルをダブルクリックしてコピー
─────────────────────────────────
・これは、あまり使われない関数ですが、場合によっては便利かもしれ
ません。結果は、数値ではなく文字列になることに注意してください。
・YEN関数は、=YEN(セル番地,桁数)で表します。桁数は、「0」ならば、
１円未満を四捨五入、「-1」ならば、10円未満を四捨五入します。
桁数の設定は、ROUND系の関数と同じです。小数点の位置を「0」と考
え、右(小数点以下)へ行くときは正の数、左へ行くときは負の数を使
ってください。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ＝＝Number.024＝＝│
└──────────────────────────┘
◆ 今日の問題 「順位を付ける」
Ｑ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
次のようなゴルフのスコアがある。順位をつけよ。ただし、順位は、
ネットの成績を基に付けるものとする(数字が小さいほうが優秀)
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃Ａ │Ｂ │Ｃ │Ｄ │Ｅ ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃１┃氏名 │グロス │ハンデ │ネット │順位 ┃
┠─╂────┼────┼────┼────┼────┨
┃２┃阿井上夫│ 96│ 12│ 84│ ┃
┠─╂────┼────┼────┼────┼────┨
┃３┃柿沼恵子│ 148│ 36│ 112│ ┃
┠─╂────┼────┼────┼────┼────┨
┃４┃指須清三│ 74│ 6│ 68│ ┃
┠─╂────┼────┼────┼────┼────┨
┃５┃太刀伝人│ 82│ 20│ 62│ ┃
┠─╂────┼────┼────┼────┼────┨
┃６┃何濡根之│ 77│ 0│ 77│ ┃
┠─╂────┼────┼────┼────┼────┨
┃７┃羽灯賦穂│ 188│ 36│ 152│ ┃
┠─╂────┼────┼────┼────┼────┨
┃８┃万美無免│ 102│ 18│ 84│ ┃
┠─╂────┼────┼────┼────┼────┨
┃９┃矢井優江│ 110│ 36│ 74│ ┃
┠─╂────┼────┼────┼────┼────┨
┃10┃羅利瑠流│ 72│ 8│ 64│ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
Ａ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
E2のセルに =RANK(D2,$D$2:$D$10,1) と入力
フィルハンドルをダブルクリック
─────────────────────────────────
・RANK関数は、順位を求める関数です。公式は、
=RANK(セル番地,範囲,順序)
で、順序は、「1」ならば昇順、「0」ならば降順になります。今回は、
スコアが少ないほうが上位になるので、昇順を選びます。
・大切なのは、範囲を「絶対参照」にすること。これを相対参照のまま
にしてしまうと、順位を求める範囲がずれてしまい、正しく計算され
ません。RANK関数を使うときは、範囲を絶対参照にすると強く覚えて
おいてください。
☆━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━★
・今回は、基本的な関数を見直してみました。順位を付けることはよく
あることで、ちょっとした大会などに役立ちます。
・尚、絶対参照と相対参照について、前に解説したものを再掲しておき
ますので、よくわからない方は参考にしてください。
-----------------------------------------------------------------
絶対参照とは、フィルハンドルでコピーしても値の変化しない参照方
法です。例えば、「$A$1」ように、$ マークをつけると、その部分は
変化しません。「$A1」や「A$1」のように片方だけ固定することもで
きます。
それに対して、通常使っているセル参照が、相対参照で、単に、A1の
ように表記します。これは、フィルハンドルでコピーすると、相対的
に変化します。
絶対参照を作りたいときは、直接 $マークをつけもいいのですが、と
りあえず、A1と入力した時点で、F4キーを押すと、自動的に絶対参照
になります。また、F4キーを続けて押すと、片方だけ固定など、いろ
いろなパターンが順番に現れますから、試してみてください。
F4キーは、関数の貼り付けボックスを使い、数式パレットを使ってい
るときも有効です。また、後から、その部分をクリックして、変更す
ることも可能ですので十分利用してください。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ＝＝Number.025＝＝│
└──────────────────────────┘
◆ 今日の問題 「重複をチェックする」
Ｑ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
品名が並べられている表がある。この表で、上から順番に重複してい
るものがある場合は●印を付けたい。関数を設定せよ。
ただし、下図のように、１つ目は無印で、２つ目以降のみに●を付け
るものとする。
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃Ａ │Ｂ │Ｃ │Ｄ │Ｅ ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃１┃品名 │ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃２┃みかん │ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃３┃りんご │ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃４┃レモン │ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃５┃みかん │● │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃６┃バナナ │ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃７┃キウイ │ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃８┃バナナ │● │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃９┃りんご │● │ │ │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
Ａ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
B1のセルに =IF(COUNTIF($A$1:A1,A1)>1,"●","") と入力
フィルハンドルでダブルクリック
─────────────────────────────────
・一つ目のものは、空白のままで、二つ目以降のものにはチェックをつ
けるという技です。先頭固定と COUNTIF関数をうまく使います。
・=IF(COUNTIF($A$1:A1,A1)>1,"●","")の意味は、一番上のセルからそ
のセルまでに、そのセルの値と同じものの数が、１つを超えていると
き(２つ以上)には●を、そうでないときは空白を返しなさいというこ
とです。
先頭を固定($A$1)してあるため、コピーすると下のように変化します。
B1のセル($A$1:A1,A1) → A1のセルの値がA1からA1までいくつ？
B2のセル($A$1:A2,A2) → A2のセルの値がA1からA2までいくつ？
B3のセル($A$1:A3,A3) → A3のセルの値がA1からA3までいくつ？
B4のセル($A$1:A4,A4) → A4のセルの値がA1からA4までいくつ？
B5のセル($A$1:A5,A5) → A5のセルの値がA1からA5までいくつ？
B6のセル($A$1:A6,A6) → A6のセルの値がA1からA6までいくつ？
・ ・
・ ・
・ ・
・このように、先頭を固定し後方をスライドさせる方法は、これまで、
何度か取り上げました。始点部分のセル番地を「絶対参照：相対参照」
の形にするだけでできます。あらゆるところで活躍する有効な技です。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ＝＝Number.026＝＝│
└──────────────────────────┘
◆ 今日の問題 「ど真ん中の数値を調べる」
Ｑ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
下図のように球速が書き込まれた表がある。この球速の中央値を知り
たい。D2のセルに関数を設定せよ。
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃Ａ │Ｂ │Ｃ │Ｄ │Ｅ ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃１┃球速 │ │中央値 │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃２┃ 132│ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃３┃ 150│ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃４┃ 146│ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃５┃ 128│ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃６┃ 155│ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃７┃ 138│ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃８┃ 144│ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃９┃ 136│ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃10┃ 148│ │ │ │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
Ａ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
D2のセルに、=MEDIAN(A2:A10) と入力して、Enter
─────────────────────────────────
・MEDIAN関数は、中央値を求める関数です。中央値とは、数値を小さい
順に並べた場合、その中央に位置する値のこと。平均ではないので注
意してください。
要は、真ん中の順位の数値と考えればいいでしょう。ただし、データ
が偶数の場合は、中央に属する２つの数値の平均になります。例えば、
データが、10個ならば、５位と６位の平均ということです。奇数の場
合は、必ず、ど真ん中の順位がありますので、その数値になります。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ＝＝Number.027＝＝│
└──────────────────────────┘
◆ 今日の問題 「早見表からデータを取り出す」
Ｑ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
下図のように、担任の名前が書いてある表がある。B9のセルに学年、
B10のセルにクラスの番号を入れると、B11に担任名が表示されるよう
にするにはどうしたらいいか？ B11に関数を入れよ。
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃Ａ │Ｂ │Ｃ │Ｄ │Ｅ ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃１┃組／学年│１年 │２年 │３年 │ ┃
┠─╂────┼────┼────┼────┼────┨
┃２┃１組 │西田 │川島 │桜井 │ ┃
┠─╂────┼────┼────┼────┼────┨
┃３┃２組 │篠原 │山田 │中島 │ ┃
┠─╂────┼────┼────┼────┼────┨
┃４┃３組 │大田 │西島 │星野 │ ┃
┠─╂────┼────┼────┼────┼────┨
┃５┃４組 │屋久島 │倉田 │大曽根 │ ┃
┠─╂────┼────┼────┼────┼────┨
┃６┃５組 │恩田 │小椋 │原田 │ ┃
┠─╂────┼────┼────┼────┼────┨
┃７┃６組 │筑比地 │吉田 │西山 │ ┃
┠─╂────┼────┼────┼────┼────┨
┃８┃ │ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃９┃学年 │ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃10┃クラス │ │ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃11┃担任 │ │ │ │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
Ａ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
B11のセルに =INDEX(B2:D7,B10,B9) と入力して、Enter
─────────────────────────────────
・INDEX関数は、=INDEX(セル範囲,行番号,列番号) で設定します。行番
号や列番号は、セル範囲内で数えます。今回は、セル範囲が「B2:D7」
ですので、B2のセルが行番号も列番号も「１」になります。
・ここを基準に、(行番号,列番号)で表すと、下のようになります。
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃Ａ │Ｂ │Ｃ │Ｄ │Ｅ ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃１┃組／学年│１年 │２年 │３年 │ ┃
┠─╂────┼────┼────┼────┼────┨
┃２┃１組 │ (1,1)│ (1,2)│ (1,3)│ ┃
┠─╂────┼────┼────┼────┼────┨
┃３┃２組 │ (2,1)│ (2,2)│ (2,3)│ ┃
┠─╂────┼────┼────┼────┼────┨
┃４┃３組 │ (3,1)│ (3,2)│ (3,3)│ ┃
┠─╂────┼────┼────┼────┼────┨
┃５┃４組 │ (4,1)│ (4,2)│ (4,3)│ ┃
┠─╂────┼────┼────┼────┼────┨
┃６┃５組 │ (5,1)│ (5,2)│ (5,3)│ ┃
┠─╂────┼────┼────┼────┼────┨
┃７┃６組 │ (6,1)│ (6,2)│ (6,3)│ ┃
〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜
ですから、２年の３組ならば、上の表で言うと、(3,2) になります。
しかし、このままでは間違いやすいので、うまくセル参照させる必要
があります。解答では、下のように工夫しています。
=INDEX(B2:D7,B10,B9)
↑ ↑
行 列
┠─╂────┼────┼────┼────┼────┨
┃９┃学年 │ 2│←列 │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃10┃クラス │ 3│←行 │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃11┃担任 │ 西島│ │ │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
この方法ならば、学年の番号、クラスの番号を入れるだけで、担任を
表示させることができます。構造をしっかり理解して作成してくださ
い。
☆━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━★
・このような早見表からデータを取り出す場合は、式を作った後、何度
か試し打ちをしてみましょう。正確に動作するか確認することが大切
です。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ＝＝Number.028＝＝│
└──────────────────────────┘
◆ 今日の問題 「選手名から順位を求める」
Ｑ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
下図のように、マラソンの順位表がある。E1に名前を入れたら、E2に
順位が入るように、E2に関数を入れよ。（図では、大平美紀を入れた
ら、順位の3 が出ています）
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃Ａ │Ｂ │Ｃ │Ｄ │Ｅ ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃１┃順位 │選手名 │TIME │選手名 │大平美紀┃
┠─╂────┼────┼────┼────┼────┨
┃２┃ 1│高橋愛子│ 2:21:44│順位 │ 3┃
┠─╂────┼────┼────┼────┼────┨
┃３┃ 2│野口瑞穂│ 2:22:16│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃４┃ 3│大平美紀│ 2:26:42│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃５┃ 4│渋井茶々│ 2:27:33│ │ ┃
〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜
┠─╂────┼────┼────┼────┼────┨
┃98┃ 97│中谷香里│ 2:46:12│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃99┃ 98│本田園子│ 2:48:30│ │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
Ａ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
E2のセルに =MATCH(E1,B2:B99,0) と入力してEnter
─────────────────────────────────
・MATCH関数 は、マッチしたものが、範囲の何番目にあるかを調べる関
数です。公式は、=MATCH(検査値,検査範囲,照合の型)で、今回のよう
に完全一致するものを調べる場合は、照合の型を「0」にします。
・これで、名前を入れると、それが範囲の何番目かがわかりますので、
簡単に順位を調べることができます。ちょっと人数の多い大会などに
使うことができます。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ＝＝Number.029＝＝│
└──────────────────────────┘
◆ 今日の問題 「千を超えるたびに●をつける」
Ｑ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
下図のように累計している表がある。Ｃ列の累計が、1000を超えるた
びに●印を付けたい。Ｄ列に関数を入れよ。
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃Ａ │Ｂ │Ｃ │Ｄ │Ｅ ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃１┃日付 │ポイント│累計 │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃２┃ 7月1日│ 580│ 580│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃３┃ 7月2日│ 390│ 970│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃４┃ 7月3日│ 560│ 1530│● ←100台から1000台に
┠─╂────┼────┼────┼────┼────┨
┃５┃ 7月4日│ 520│ 2050│● ←1000台から2000台に
┠─╂────┼────┼────┼────┼────┨
┃６┃ 7月5日│ 240│ 2290│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃７┃ 7月6日│ 320│ 2610│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃８┃ 7月7日│ 360│ 2970│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃９┃ 7月8日│ 480│ 3450│● ←2000台から3000台に
┠─╂────┼────┼────┼────┼────┨
┃10┃ 7月9日│ 360│ 3810│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃11┃ 7月10日│ 520│ 4330│● ←3000台から4000台に
┠─╂────┼────┼────┼────┼────┨
┃12┃ 7月11日│ 260│ 4590│ │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
Ａ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
D3のセルに =IF(ROUNDDOWN(C2,-3)<ROUNDDOWN(C3,-3),"●","") と
入力して、Enter
D3のセルを選択し、フィルハンドルでダブルクリック
─────────────────────────────────
・まともにやったのではできませんから、 ROUNDDOWN関数を使い、百の
位以下を切り捨てて千単位に丸め、その大小を比較します。それによ
り、上のセルより大きくなったときに働くようにするのです。
※ D2のセルではなく、D3のセルに入れるところに注意してください。
・この方法を使えば、いろいろ応用できます。万単位で丸めたいのなら、
-3の部分を-4にしてください。
☆━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━★
・ROUNDDOWN 関数については、前にも説明しました。もう一度掲載して
おきます。
・ROUNDDOWN 関数は、桁数を指定して切り捨てる関数です。
=ROUNDDOWN(セル番地,桁数)
切り捨て位置は、小数点位置を基準として、右方向(小数点以下)に正
の数、左方向に負の数を使います。
例えば、任意のセルに 1248.567 という数値が入っている場合、
=ROUNDDOWN(セル番地,2) 1248.56
=ROUNDDOWN(セル番地,1) 1248.5
=ROUNDDOWN(セル番地,0) 1248
=ROUNDDOWN(セル番地,-1) 1240
=ROUNDDOWN(セル番地,-2) 1200
=ROUNDDOWN(セル番地,-3) 1000
このように、切捨てされます。
┌──────────────────────────┐
│【関数コレクションシリーズ】 ＝＝Number.030＝＝│
└──────────────────────────┘
◆ 今日の問題 「スペースで区切られた氏名から名前だけ取り出す」
Ｑ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
姓と名が、スペースで区切られた表がある。下図のように、Ｂ列Ｃ列
に関数を設定して、二つのセルに分けよ。ただし、スペースは、どち
らのセルにも残らないようにすること。
┏━┳━━━━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃Ａ │Ｂ │Ｃ │Ｄ │Ｅ ┃
┣━╋━━━━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃１┃氏名 │姓 │名 │ │ ┃
┠─╂───────┼────┼────┼────┼────┨
┃２┃山田 太郎 │山田 │太郎 │ │ ┃
┠─╂───────┼────┼────┼────┼────┨
┃３┃佐々木 新次郎│佐々木 │新次郎 │ │ ┃
┠─╂───────┼────┼────┼────┼────┨
┃４┃西山 悟 │西山 │悟 │ │ ┃
┠─╂───────┼────┼────┼────┼────┨
┃５┃五藤 敏則 │五藤 │敏則 │ │ ┃
┠─╂───────┼────┼────┼────┼────┨
┃６┃勅使河原 昇 │勅使河原│昇 │ │ ┃
┗━┻━━━━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
Ａ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━☆
B2のセルに =MID(A2,1,FIND(" ",A2)-1) と入力
（=LEFT(A2,FIND(" ",A2)-1)でもＯＫ）
C2のセルに =MID(A2,FIND(" ",A2)+1,10) と入力
（=SUBSTITUTE(A2,B2&" ","")でもＯＫ）
B2、C2のセルを選択し、フィルハンドルをダブルクリック
─────────────────────────────────
・MID 関数は、=MID(セル番地,開始位置,取り出す文字数)で、文字列を
取り出します。「姓」の方は、開始位置が一番最初ですから、1 でい
いのですが、「名」の方は、開始位置が不定ですから工夫する必要が
あります。また、「姓」の方でも、取り出す文字数は不定ですから、
うまく設定しなければなりません。
・そこで、検索したい文字列(ここでは、スペース)が何番目に入ってい
るかを調べる関数、つまり、FIND関数を使います。
・FIND関数は、=FIND("検索文字列",セル番地)で設定します。例えば、
A2のセルならば、スペースは、3番目ですから、3が返されます。しか
し、「姓」の欄には、スペースは要りませんから、１を引きます。
山田 太郎 １を引くことによりスペースの前までの
↓ ↓ 文字数になる（ここでは、2）
=MID(A2,1,FIND(" ",A2)-1)
↑
スペースは3番目
言葉で表すと、A2のセルに入っている「山田 太郎」の１番目から、
２文字取り出しなさいということです。
・これが、佐々木さんならば、スペースの位置が、4 ですので、1 引い
て、3 になります。スペースの位置によって、取り出す文字数を自動
的に変化させることができます。
逆に「名」の方は、開始位置をFIND関数で調べます。これも、「姓」
のときと同じ考え方で、スペースの位置を見つけます。ポイントは、
姓の場合と逆に、１を足すこと。スペースの位置の次から開始するわ
けですから、わかりやすいでしょう。
・尚、取り出す文字数を「10」にしましたが、これは適当でかまいませ
ん。一番後ろですから、何文字取り出そうが、ないものは取り出せま
せんので、少し多めの数字を付けておけばいいと思います。
| ★ エクセルの小技へ ★ | ★ TOPのページへ ★ | ★ 次へ（031〜040） ★ |