「不動産リスト」を解く

Transcription

1 Microsoft2010 不動産リスト を解く IF 関数 VLOOKUP 関数 CHOOSE 関数 LEFT 関数 MOD 関数 INT 関数 INDEX 関数 2015/01/27 パソコン技能検定 Ⅱ 種試験 Excel 1 級検定過去問題

2 ここで使用する関数の種類 よく使われる関数として SUM IF,AVERAGE AND,OR などがありますが そのほかにも 今回次のような関数を単独で また 組み合わせて使っていきます VLOOKUP 関数 CHOOSE 関数 LEFT 関数 INT 関数 DATEDIF 関数 1 C 列 賃料 ~K 列 礼金 までを求める (VLOOKUP 関数 ) VLOOKUP の V とは 縦方向 (vertical) の検索 を意味します これに対して横方向の検索は HLOOKUP 横方向 (horizontal) があります ここでは VLOOKUP を使っていきます VLOOKUP 関数 の構文は次の通りです fx=vlookup( 検索値, 範囲, 列番号, 検索の型 ) 1 資料 1kyu hudousan date を例に VLOOKUP 関数で検索していきます 1 問題 sheet の問題をクリックして開きます A 列 物件番号 を検索値として C 列 ( 賃料 )~K 列 ( 礼金 ) までを検索していきます 2 検索先の Sheet bukken] を開いてみます A 列の 物件番号 が問題リストの 物件番号 に対応して答えを表示していきます 3 では 実際に VLOOKUP 関数 を実行していきましょう 問題 Sheet を開き セル C4 ( 賃料 ) をクリックして選択 数式バーの fx をクリック 1

3 4 関数の分類 = 最近使用した関数 または すべて表示 をクリック VLOOKUP をクリックして OK をクリック 5 関数の引数 が表示 検索値 にカーソルを置き sheet の A4 ( 物件番号 ) をクリック 検索値 に A4 が表示 範囲 にカーソルを移動 2

4 6 Sheet bukken をクリックして開きます セル A3:K45 までを選択 7 範囲 に選択した bukkendata がはいります 列番号に 2 と入力 検索方法に 0 と入力 OK をクリック 検索方法 1: 検索値の近似値 を含めて検索する場合 TRUE を指定または 1 と入力検索方法 2: 完全に一致するものだけを検索する場合 FALSE を指定または 0 と入力 3

5 8 賃料 のセルに答えが表示 オートフィル で下へ向けコピーします 9 賃料 の列が表示されました 同じ手順で 専有面積 から 礼金 まで答えを求めましょう 10 求めた答えが表示された状態です 4

6 2 物件種目 を求める CHOOSE 関数 LEFT 関数 CHOOSE 関数 とは値のリストから指定した値を取り出します インデックス に指定した番号に基づいて 最大 29 個の値の中から 1 つの値を選択します 構文 fx=choose( インデックス, 値, 値 2, ) LEFT 関数 とは設定された範囲の先頭 ( 左 ) から指定された数の文字を返します 構文 fx=left( 範囲または文字列 ), 文字数 ) RIGHT 関数とは設定された範囲の右側から指定された数の文字を返します 構文 fx=right( 範囲または文字列 ), 文字数 ) 1 資料 不動産リスト 2 列目 物件種目 を次の条件で求めていきます 物件番号 1000 の位 セルの表示 アパート マンション 貸家 1 問題 sheet を開きます B 列 物件種目 の 1000 の位の値を元に CHOOSE 関数を利用して 物件種目 を表示します セル B4 を選択 数式バーの fx をクリック 2 関数の引数 ダイアログが表示 関数名 から [CHOOSE] を選択 OK をクリック 5

7 3 CHOOSE 関数 が表示 インデックス 欄にカーソルがあることを確認 物件番号 の左の数字が検索の数値とする LEFT 関数 ( ない場合は その他の関数 ) をクリックします 4 LEFT 関数 の 関数の引数 ダイアログが表示 上の 文字列 にカーソルを置き リストのセル A4 ( 物件番号 ) をクリック 下の 文字列 にカーソルを置き 1 ( 物件番号の左から 1 番目の意味 ) と入力 7 数式バー CHOOSE の右にカーソルを移動し クリック 6

8 8 関数の引数 が CHOOSE 関数 に戻りました 値 1 に アパート 値 2 に マンション 値 3 に 貸家 と入力 OK をクリッ ク 物件番号 1000 の位 セルの表示 アパート マンション 貸家 9 B4 に 貸家 と答えが表示されました オートフィル で下へ向けコピーします 7

9 3 築年数 を求める(DATEDIF 関数 ) DATEDIF 関数 とは開始年月日から終了年月日までの期間を年 月 日などの単位で求めることができる関数です Excel 関数の一覧にはないので 直接手入力をする必要があります 構文 =DATEDIF( 開始年月日, 終了年月日, Y ) 経過した年数 =DATEDIF( 開始年月日, 終了年月日, M ) 経過した月数 =DATEDIF( 開始年月日, 終了年月日, D ) 経過した日数 1 資料 不動産リスト の L 列 築年数 を DATEDIF 関数 を利用して求めます 問題シート を開いて セル L4 に答えを表示していきます 1 築年数から現在年月日までを求めます まず セル L2 に現代の年月日を today 関数 で求めます セル L2 を選択 数式バーの fx をクリック 2 関数の挿入 が表示 関数の分類 = 最近使用した関数 または すべて表示 をクリック TODAY をクリックして OK をクリック 3 このようなダイアログが表示されますので OK をクリック 8

10 4 セル L2 に年月日が表示 答えを求めるセル L4 を選択 5 セル L4 へ 手入力で =DATEDIF(l4,$L$2, Y ) と入力 Enter で確定 年月日の部分をドラッグして F4 キーを押し [ 絶対参照 ] にします L2 が $L$2 になります 6 50 と答えが表示されました オートフィル で下へ向けコピーします 7 築年数の列に答えが表示されました 9

11 4 不動産問題 Sheet の 値引き を INDEX 関数 INT 関数 IF 関数 で求める 1 問題 : 徒歩 が 10 分以上の場合は値引き 行番号 ( 徒歩 ) の指定 :10 未満の場合は 1 そうでない場合は 10 で割った数値 +1( 分数 ) 築年数 が 20 年以上の場合は値引き 列番号 ( 徒歩 ) の指定 :20 未満の場合は 1 そうでない場合は 10 で割った数値 ( 築年数 ) の交差した部分を選択 1 値引きの表を確認 Sheet の nebiki を開き この表を元に 値引き を求めます 2 セル M4 を選択 数式バーの fx をクリック 3 関数の挿入 ダイアログが表示 関数名 の INDEX を選択 OK をクリック 4 引数の選択 ダイアログが表示 表が 1 つなので そのまま ( 初期設定では 配列行番号列番号 に設定されている ) OK をクリック 10

12 5 関数の引数 ダイアログが表示 配列 欄にカーソルを移動し Sheet に nsbiki をクリックして開きます 6 nebiki のセル C4:H6 をドラッグして選択します 配列欄 に Sheet の名前 nebiki が表示 行番号の欄にカーソルを移動 7 数式バー左の をクリックし IF を指定 表示された一覧より IF を選択 ( ない場合は その他の関数 から指定します ) 11

13 8 関数の引数 ダイアログが表示 セル G4 を選択 <10 を入力 真の場合 に 1 を入力 偽の場合 にカーソルを移動 9 数式バー左の をクリック 表示されたリストより ( リストにない場合は その他の関数 から ) 選択します INT クリックします 12

14 10 INT の 関数の引数 が表示 数値欄に (G4/10)+1 を入力 ( G4 はセル G4 をクリック ) 12 数式バー IF の右にカーソルを移動してクリック 13 偽の場合 行番号の指定 ( 徒歩 ) 設定が終了したことを確認 列番号 築年数 の設定をします 元のダイアログに戻ります カーソルを数式バーの =INDEX の右にクリックして移動 13

15 2 列番号 ( 築年数 ) の設定をしていきます 1 最初の [INDEX] のダイアログに戻りました 列番号 にカーソルを移動 数式バーの IF を選択 2 IF 関数 ダイアログが表示 論理式 に築年数のセル L4 を選択 L4<20 ( 築年数が 20 年以下の意味 ) と入力 真の場合 に 1 ( 値引きの意味 ) 偽の場合 にカーソルを移動 数式バー左 から INT を選択 14

16 3 数値 に (L4<10) と入力します カーソルを最初の index の右に移動します 4 行番号 列番号 が設定されました OK をクリック 6 答えをオートコンプリートで下に向けてコピーします 15

17 表の完成 VLOOKUP 関数 IF 関数 CHOOSE 関数 LEFT 関数 MOD 関数 INT 関数 INDEX 関数 不動産リスト を解く Microsoft Excel