Microsoft2010
「不動産リスト」を解く
IF 関数 ・ ＶＬＯＯＫＵＰ関数 ・ ＣＨＯＯＳＥ関数 ・ ＬＥＦＴ関数 ・ ＭＯＤ関
数・ ＩＮＴ関数 ・ ＩＮＤＥＸ関数
2015/01/27 パソコン技能検定Ⅱ種試験Excel １級検定過去問題ここで使用する関数の種類 よく使われる関数として、「ＳＵＭ、ＩＦ，ＡＶＥＲＡＧＥ、ＡＮＤ，ＯＲ」などがありますが、そのほかにも、 今回次のような関数を単独で、また、組み合わせて使っていきます。 ＶＬＯＯＫＵＰ関数 ＣＨＯＯＳＥ関数 ＬＥＦＴ関数 ＩＮＴ関数 ＤＡＴＥＤＩＦ関数
１、Ｃ列「賃料」～Ｋ列「礼金」までを求める（ＶＬＯＯＫＵＰ関数）
・「ＶＬＯＯＫＵＰ」のＶとは、「縦方向(vertical)の検索」を意味します。これに対して横方向の検索は、 ・「HＬＯＯＫＵＰ」「横方向（horizontal）」があります。ここでは、「ＶＬＯＯＫＵＰ」を使っていきます。 「ＶＬＯＯＫＵＰ関数」の構文は次の通りです。 fx＝ＶＬＯＯＫＵＰ(検索値, 範囲, 列番号,検索の型)〔１〕資料「1kyu hudousan date」を例に「ＶＬＯＯＫＵＰ」関数で検索していきます。
①「問題 sheet」の問題をクリックして開きます。 ・A 列「物件番号」を検索値として、「C 列(賃料)～K 列(礼金)」までを検索していきます。 ② 検索先の Sheet「bukken]を開いてみます。 ・A 列の「物件番号」が問題リストの「物件番号」に対応して答えを表示していきます。 ③ では、実際に「ＶＬＯＯＫＵＰ関数」を実行していきましょう。 ・「問題 Sheet」を開き、セル【C４】(賃料)をクリックして選択 ・数式バーの「fx」をクリック
④ 「関数の分類」＝｢最近使用した関数｣または「すべて表示」をクリック ・「VLOOKUP」をクリックして → 「ＯＫ」をクリック ⑤ ｢関数の引数｣が表示 ・「検索値」にカーソルを置き、「sheet」の【A4】(物件番号)をクリック→「検索値」に「A4」が表 示。 ・「範囲」にカーソルを移動
⑥ Sheet「bukken」をクリックして開きます ・セル【Ａ3：Ｋ45】までを選択 ⑦ 「範囲」に選択した「bukkendata」がはいります。 ・列番号に「2」と入力 → 検索方法に「0」と入力 → 「OK」をクリック ※ 検索方法 1：検索値の近似値」を含めて検索する場合「TRUE」を指定または「1」と入力 検索方法 2：完全に一致するものだけを検索する場合「FALSE」を指定または「0」と入力
⑧ 「賃料」のセルに答えが表示 → 「オートフィル」で下へ向けコピーします。
⑨ 「賃料」の列が表示されました。
・同じ手順で「専有面積」から「礼金」まで答えを求めましょう。
２、「物件種目」を求める「CＨＯＯＳＥ関数」、「ＬＥＦＴ関数」 ・「ＣＨＯＯＳＥ関数」とは 値のリストから指定した値を取り出します。「インデックス」に指定した番号に基づいて、 最大 29 個の値の中から１つの値を選択します。 構文 fx＝ＣＨＯＯＳＥ(インデックス,値,値 2,・・・・・) ・「ＬＥＦＴ関数」とは 設定された範囲の先頭(左)から指定された数の文字を返します。 構文 fx＝ＬＥＦＴ（範囲または文字列）,文字数） ・「ＲＩＧＨＴ」関数とは 設定された範囲の右側から指定された数の文字を返します。 構文 fx＝ＲＩＧＨＴ（範囲または文字列）,文字数） 〔１〕資料「不動産リスト」２列目「物件種目」を次の条件で求めていきます。 物件番号 1000 の位 1 2 3 セルの表示 アパート マンション 貸家 ①「問題 sheet」を開きます。 ・Ｂ列「物件種目」の 1000 の位の値を元に、CHOOSE 関数を利用して、「物件種目」を表示します。 ・セル【B4】を選択 → 数式バーの「fx」をクリック ② 「関数の引数」ダイアログが表示 ・「関数名」から［CHOOSE］を選択 → 「ＯＫ」をクリック
③ 「ＣＨＯＯＳＥ関数」が表示 → 「インデックス」欄にカーソルがあることを確認。 ・「物件番号」の左の数字が検索の数値とする「ＬＥＦＴ関数」（ない場合は「その他の関数」）をク リックします。 ④ 「ＬＥＦＴ関数」の「関数の引数」ダイアログが表示 ・上の「文字列」にカーソルを置き、リストのセル【Ａ4】（物件番号）をクリック ・下の「文字列」にカーソルを置き、「1」（物件番号の左から１番目の意味）と入力 ⑦ 数式バー「CHOOSE」の右にカーソルを移動し、クリック
⑧「関数の引数」が「CHOOSE 関数」に戻りました。 ・「値１」に「アパート」、「値２」に「マンション」、「値３」に「貸家」と入力 →「ＯＫ」をクリッ ク。 物件番号 1000 の位 1 2 3 セルの表示 アパート マンション 貸家 ⑨ 【B4】に「貸家」と答えが表示されました → 「オートフィル」で下へ向けコピーします。
３、「築年数」を求める（ＤＡＴＥＤＩＦ関数） ・「ＤＡＴＥＤＩＦ関数」とは 開始年月日から終了年月日までの期間を年、月、日などの単位で求めることができる関数です。 Ｅｘｃｅｌ関数の一覧にはないので、直接手入力をする必要があります。 構文 ＝ＤＡＴＥＤＩＦ（開始年月日, 終了年月日,“Ｙ”）経過した年数 ＝ＤＡＴＥＤＩＦ（開始年月日, 終了年月日,“Ｍ”）経過した月数 ＝ＤＡＴＥＤＩＦ（開始年月日, 終了年月日,“Ｄ”）経過した日数 〔１〕資料「不動産リスト」のＬ列「築年数」を「ＤＡＴＥＤＩＦ関数」を利用して求めます。 ・「問題シート」を開いて、セル【L4】に答えを表示していきます。 ① 築年数から現在年月日までを求めます。 ・まず、セル【L2】に現代の年月日を「today 関数」で求めます → セル【L2】を選択 ・数式バーの「ｆｘ」をクリック ② 「関数の挿入」が表示 ・「関数の分類」＝｢最近使用した関数｣または「すべて表示」をクリック ・「ＴＯＤＡＹ」をクリックして → 「ＯＫ」をクリック ③ このようなダイアログが表示されますので、「ＯＫ」をクリック
④ セル【Ｌ2】に年月日が表示 → 答えを求めるセル【Ｌ４】を選択 ⑤ セル【L4】へ、手入力で「=DATEDIF(l4,$L$2,”Y”) と入力 → 「Enter」で確定。 ・年月日の部分をドラッグして「Ｆ4」キーを押し、［絶対参照］にします。 ・「L2」が ⇒「$L$2」になります。 ⑥ 「50」と答えが表示されました → 「オートフィル」で下へ向けコピーします。 ⑦ 築年数の列に答えが表示されました。
４、「不動産問題 Sheet」の「値引き」を「ＩＮＤＥＸ関数」、「INT 関数」、「ＩＦ関数」で求める 〔１〕 問題 :「徒歩」が 10 分以上の場合は値引き ・行番号(徒歩)の指定：10 未満の場合は 1、そうでない場合は 10 で割った数値＋１（分数） 「築年数」が 20 年以上の場合は値引き ・列番号(徒歩)の指定：20 未満の場合は 1、そうでない場合は 10 で割った数値（築年数）の交差し た部分を選択 ① 値引きの表を確認 → 「Sheet」の「nebiki」を開き、この表を元に「値引き」を求めます。 ② セル【M4】を選択 → 数式バーの「fx」をクリック ③「関数の挿入」ダイアログが表示 → ｢関数名｣の「ＩＮＤＥＸ」を選択 → 「ＯＫ」をクリック。 ④ 「引数の選択」ダイアログが表示 ・表が１つなので、そのまま(初期設定では「配列行番号列番号」に設定されている)「OK」をクリッ ク。
⑤ 「関数の引数」ダイアログが表示 ・「配列」欄にカーソルを移動し、「Sheet」に「nsbiki」をクリックして開きます。 ⑥ 「nebiki」のセル【C4:H6】をドラッグして選択します ・「配列欄」に Sheet の名前「nebiki」が表示。行番号の欄にカーソルを移動 ⑦ 数式バー左の▼をクリックし、「IF」を指定 →表示された一覧より「IF」を選択(ない場合は「その他 の関数」から指定します。）
⑧ 「関数の引数」ダイアログが表示 →セル【G4】を選択 →「＜10」を入力 →「真の場合」に「1」 を入力 ・「偽の場合」にカーソルを移動 ⑨ 数式バー左の▼をクリック →表示されたリストより（リストにない場合は、「その他の関数」から） 選択します。 ・「INT」クリックします
⑩「INT」の「関数の引数」が表示 ・ 数値欄に「（G4/10）+1」を入力（「G4」はセル【G4】をクリック） ⑫ 数式バー「IF」の右にカーソルを移動してクリック ⑬ 「偽の場合」 ・ 行番号の指定（徒歩）設定が終了したことを確認 → 列番号「築年数」の設定をします・ ・ 元のダイアログに戻ります → ・ カーソルを数式バーの「=INDEX」の右にクリックして移動
〔２〕「列番号」(築年数)の設定をしていきます。 ① 最初の[INDEX]のダイアログに戻りました。 「列番号」にカーソルを移動 → 数式バーの「IF」を選択 ② 「IF 関数」ダイアログが表示 ・「論理式」に築年数のセル【L4】を選択 「L4<20」(築年数が 20 年以下の意味)と入力 ・「真の場合」に「１」（値引きの意味） ・「偽の場合」にカーソルを移動 → 数式バー左▼から「INT」を選択
③ 「数値」に「（L4＜10）」と入力します。 ・カーソルを最初の「index」の右に移動します。
④ 「行番号」、「列番号」が設定されました→ 「OK」をクリック
表の完成
