<45788AD690942E786C7378>

Transcription

1 Excel : 関数講座 Excel 2010 対応 岩屋パソコン教室 岩屋パソコンクラブ 統計関数論理関数 File: 水泳大会 関数式入力 01 統計 論理関数 04 SUM MAX MIN AVERAGE COUNT RANK LARGE SMALL IF AND OR NOT COUNTIF 検索関数 File: 請求書 File: 運賃表 検索関数 06 VLOOKUP HLOOKUP INDEX 端数処理関数日付時刻関数 File: 売上予想 端数処理関数 10 日付時刻関数 12 INT ROUND ROUNDDOWN ROUNDUP DATE TODAY NOW DATEDIF 条件検索集計関数 File: 現金出納 条件検索集計関数 15 SUMIF SUM 2015/07/01

2 いわやパソコンクラブ

3 関数式の入力 (1/3) 関数 SUM() セル [A1]~[A5] に数値 1 ~ 5 が入力されていて セル [A6] にその合計値を計算 表示させる場合 : 関数を使用しないで数式入力するには右図の手順になります 関数を使用して合計結果を出すためには次の手順によります但し 合計するための関数が SUM( ) という名称であることを知っているとします 1 セル [A6] にキー入力により直接 "=SUM" と入力すると関数の候補群が表示されますが 2 そのまま "=SUM( " と入力し続けると関数 SUM に関する説明が表示されます 関数式の一般形式 = 関数名 ( 引数 1, 引数 2, ) 必須 カッコハシ メカッコトシ 3 続いて合計対照セル範囲 [A1:A5] をト ラック 選択して引数指定して 引数の形式 個数は使用関数に依存する 4 ")" 閉じて数式確定させると完了 目的に応じて関数を使用することで 数式入力を簡略化することができる これが関数使用の利点 平均値関数 AVERAGE( ) の使用例 関数を使用しない場合の数式例 ( 入力が手間 ) 関数を使用した場合の数式例 ( 入力が簡単 )

4 関数式の入力 (2/3) 関数 TODAY() 方法 (1 数式ハ ーから関数挿入ホ タンで関数式入力 ( 関数の種類 関数名を検索指定する ) 1 関数の挿入ホ タン [fx] をクリック あらかじめセル [B4] を選択しておきます 2 関数の挿入ウィサ ート 画面 関数名がわからない場合 : [ 関数の検索 ] ホ ックスに検索語を入力することで関数名を探すこともできる 3 関数の分類から 日付 / 時刻 指定 4 関数名から [TODAY] を選択 関数名をクリックすると関数名ホ ックス欄の下に該当する関数の働きを説明 表示する 5 関数の引数確認メッセーシ この関数では引数の必要なしというメッセーシ 表示 6 指定したセルに指定した関数が入力されて結果が表示される

5 関数式の入力 (3/3) 関数 DAY() 方法 (2 数式タフ の関数ライフ ラリ から関数式入力 ( 関数の種類 関数名を指定する ) 1 数式 / 関数ライフ ラリ /[ 日付 / 時刻 ] 選択 セル [B4] に表示されている日付から 日 のみを抽出表示させる関数 :DAY( ) を使用します ( 引数が一つ必要 ) 2 [ 日付 / 時刻 ] のリストから [DAY] を選択クリック あらかじめセル [B6] を選択しておきます 3 関数の引数指定画面表示 4 引数対照セルを選択クリック後 [OK] 5 入力セルに結果表示

6 統計 論理 File: 水泳大会 Data File 水泳大会.xls( 関数設定前 ) セル 設定 セル 設定 セル 設定 E3:E12 各種目の合計値 B17:E17 種目別上位 2 位タイム G3:G12 * タイムをすべてクリア : B13:E13 参加人員数 B18:E18 種目別上位 3 位タイム H3:H12 * タイムのどれかクリア : B14:E14 種目別平均値 B19:E19 種目別下位 2 位タイム I3:I12 * タイムのどれもクリアせず : B15:E15 種目別トップ値 F3:F12 総合タイム順位付け * タイム : 自由形 <30, 背泳ぎ <36, 平泳ぎ <40 B16:E16 種目別ワースト値 水泳大会.xls( 関数設定後 )

7 関数設定 File: 水泳大会 セル番地 関数 機能 E3:E12 SUM SUM( 範囲 ) 範囲で指定したセル内の数値データの合計を返す 集計 B13:E13 COUNT COUNT( 範囲 ) 範囲で指定したセル内にある数値データの件数を返す 統計 B14:E14 AVERAGE AVERAGE( 範囲 ) 範囲で指定したセル内にある数値データの平均値を返す 統計 B15:E15 MIN MIN( 範囲 ) 範囲で指定したセル内にある数値データの最小値を返す 統計 B16:E16 MAX MAX( 範囲 ) 範囲で指定したセル内にある数値データの最大値を返す 統計 B17:E17 SMALL SMALL( 範囲, 順位 ) 範囲で指定したセル内にある数値データの [ 順位 ] 番目に小さな値を返す 統計 B18:E18 SMALL SMALL( 範囲, 順位 ) 範囲で指定したセル内にある数値データの [ 順位 ] 番目に小さな値を返す 統計 B19:E19 LARGE LARGE( 範囲, 順位 ) 範囲で指定したセル内にある数値データの [ 順位 ] 番目に大きな値を返す 統計 F3:F12 RANK RANK( 数値, 範囲, 整数 ) 与えた [ 数値 ] が範囲で指定したセル内の数値のなかで何番目にあたるかを返す :[ 整数 ] が 0 の場合は降順 1 の場合は昇順の順位 統計 G3:G12 AND AND( 論理式 1, 論理式 2, ) 論理式のすべてが 真 の時に限り TRUE を返し それ以外は FALSE 論理 *IF 関数は頁 21を参照 H3:H12 OR OR( 論理式 1, 論理式 2, ) 論理式のどれかが 真 の時に TRUE を返し それ以外は FALSE 論理 *IF 関数は頁 21を参照 I3:I12 NOT NOT( 論理式 ) 論理式が 真 の時に FALSE を返し 偽 の時は TRUE 論理 *IF 関数は頁 21を参照 G13:I13 COUNTIF COUNTIF( 範囲, 検索条件 ) H13> " " 範囲で指定したセル内で検索条件を満たすデータの件計を返す I13> " " 統計

8 検索 File: 請求書.xls 設定前 セル C1 B5:B9 D5:D9 E2:E9 設定 得意先一覧表のコート 参照して名称を商品一覧表の品番参照して品名を商品一覧表の品番参照して単価を数式 :[ 数量 ]*[ 単価 ] セル E10 E11 E12 設定金額合計金額合計 *0.05 金額合計 *1.05 設定後 関数 : HLOOKUP VLOOKUP SUM

9 検索関数 : HLOOKUP VLOOKUP 形式 HLOOKUP( 検索値, 範囲, 行番号,[ 検索の型 :TRUE あるいは FALSE]) VLOOKUP( 検索値, 範囲, 列番号,[ 検索の型 :TRUE あるいは FALSE]) 検索の元になる表テ ータの構成によって HLOOKUP か VLOOKUP の使い分けをすることになる : 表テ ータ内のレコート 単位が列単位の場合は HLOOKUP( 検索値が水平方向に並ぶ ) 行番号 1 行番号 2 レコート 列 範囲 検索値行 : 表テ ータ内のレコート 単位が行単位の場合は VLOOKUP( 検索値が垂直方向に並ぶ ) レコート 行 検索値列 列番号 1 列番号 2 列番号 3 範囲 D5:D9 上端行 (HLOOKUP の場合 : 行番号 1) あるいは 左端列 (VLOOKUP の場合 : 列番号 1) にある値を参照することになる 行番号 あるいは 列番号 は戻り値として欲しいテ ータがある行あるいは列の番号を指定する 検索の型は指定しない限り TRUE を指定したとみなされる この場合は範囲内のテ ータは検索値によって昇順並べ替えされていなければならない ただし 指定した検索値に一致する値が表テ ータ内にない場合は 検索値を超えない近接値を検索値とみなしてこれに対応する戻り値を返す 検索の型として FALSE を指定した場合はテ ータを並べ替えさせる必要はないが 検索値が一致しない場合は 関数エラーとなる 関数設定 File: 請求書.xls セル番地 C1 関数機能 HLOOKUP HLOOKUP( 検索値, 範囲, 行番号,[ 検索の型 :TRUEあるいはFALSE]) B5:B9 D5:D9 VLOOKUP VLOOKUP( 検索値, 範囲, 列番号,[ 検索の型 :TRUEあるいはFALSE]) A 2 VLOOKUP VLOOKUP( 検索値, 範囲, 列番号,[ 検索の型 :TRUEあるいはFALSE])

10 検索 : INDEX 関数 File: 運賃表.xls 設定前 セル B17 A18 設定セル [B4:D12] の運賃表をもとに 入力されるセル [B15] の乗車駅コート とセル [B16] の降車コート から運賃を出力する ただし コート 入力が未入力の場合には 空白値を返す セル [B15] とセル [B16] に値が入力されて 料金表示が実行された場合 注釈表示をする 設定後

11 INDEX 関数のしくみ 10,390 行番号 :5 列番号 :2 形式 INDEX( 範囲, 行番号, 列番号 [, 領域番号 ] ) 引数として与えられた範囲の中から 引数として与えられた行番号と列番号の交差するセルの値を出力する 与えられた範囲が複数領域の場合には 領域の番号を指定する ( この例の場合 範囲は一つなので領域番号は指定せず ) 本例の説明 : IF( OR( B15="",B16=""),"",INDEX(B4:D12,B16,B15) ) IF 関数条件 : セル [B17] の内容 OR(): セル [B15] あるいはセル [B16] のどちらかでも一方が空白値 "" ならば 空白値 "" を返し セル [B15] とセル [B16] のどちらもが空白値 "" で INDEX(): ないならば INDEX 関数の出力値を返す IF( B17="",""," 料金は, 運賃と特急料金の合計です " ) IF 関数条件 : セル [A18] の内容セル [B17] の値が空白値 "" の場合は 空白値 "" を返し 違う場合は 注釈文 " 料金は, 運賃と特急料金の合計です " を出力する

12 端数処理関数 File: 売上予想.xls 設定前 セル D4:D8 E4:E8 F4:F8 B9:C9 E9:F9 B10:C10 E10:F10 設定 (10 月実績 -9 月実績 ) の値を9 月実績で割り % で小数点第 1 位表示 10 月実績に伸び率を掛けた値を整数化 切り捨て端数処理 11 月予想と単価を掛けた値を1000の位で切り捨て端数処理 各支店の合計を求める 各支店の平均値を整数化 四捨五入で端数処理 設定後 端数処理関数の特性 : INT( [ 数値 ] ) ROUND( [ 数値 ],[ 桁数 ] ) ROUNDDOWN( [ 数値 ],[ 桁数 ] ) ROUNDUP( [ 数値 ],[ 桁数 ] ) 機能 引数 :[ 数値 ]= 引数 :[ 数値 ]= [ 桁数 ]=1 [ 桁数 ]=0 [ 桁数 ]=-1 [ 桁数 ]=1 [ 桁数 ]=0 [ 桁数 ]=-1 INT ROUND 整数化切り捨て :* 四捨五入 : 絶対値処理 ROUNDDOWN 切り捨て : 絶対値処理 ROUNDUP 切り上げ : 絶対値処理 *:INT 関数の場合 引数としては [ 数値 ] だけになるので [ 桁数 ] を引数として指定する必要はない ただし [ 数値 ] をこえない整数を返すという特性があるので [ 数値 ] が負数の場合には 他の関数のように [ 数値 ] の絶対値に対する端数処理をした後で 正負の符号 を付けて出力値を返すというわけではないので おなじ切り捨て端数処理であっても ROUNDDOWN とは出力結果が異なる

13 関数設定 セル番地 関数 設定内容 D4:D8 E4:E8 数式 INT 書式を % 表示 小数点 1 桁表示に設定する 表示は自動的に四捨五入されるが 数値テ ータそのものは端数処理関数を使用していないので 変化していないことに注意 INT( [ 数値 ] ) [ 数値 ] の少数部分を切り捨てて 指定した数値をこえない整数を返す F4:F8 ROUNDDOWN ROUNDDOWN( [ 数値 ],[ 桁数 ] ) [ 数値 ] を指定した [ 桁数 ] で切り捨てる : 桁数 [-2] 100/[-1] 10/[0] 1/[1] 0.1/[2] 0.01 B9:C9 E9:F9 B10:C10 E10:F10 合計 :SUM ROUND 平均 :AVERAGE ROUND( [ 数値 ],[ 桁数 ] ) [ 数値 ] を指定した [ 桁数 ] で四捨五入 : 桁数 [-2] 100/[-1] 10/[0] 1/[1] 0.1/[2] 0.01 端数処理によるテ ータの不整合 : 数値テ ータを端数処理関数によって 更新した場合にどうしても避けられない問題としてテ ータの不整合ということがある : 本例の場合 セル [F9] の値に注目してみる 各支店の 11 月の売上予想額は単価と売上予想数量を掛け合わせたものを千円単位で切り捨て端数処理している これらの集計結果として SUM 関数による値を返している もし ここでセル [F9] の値として = ROUNDDOWN($F$2*E9,-3) を設定すると結果は以下のようになって数値にくい違いが生じる 端数処理の場合 (1). 求められているテ ータの特性を考えに入れて演算指定すること (2). その場合のテ ータの整合性を意識しておくこと が重要である F9 = SUM(F4:F8)

14 日付 時刻関数 :DATE TODAY NOW DATEDIF File: 年齢表.xls 設定前 セル F1 F2 F4:F13 G4:G13 設定コンヒ ュータの内蔵時計の日付を表示 : セルの書式設定により 日付表示 設定コンヒ ュータの内蔵時計の時刻を表示 : セルの書式設定により 時刻表示 設定入力済みの 年 月 日 の数値から日付シリアル値を返し これを書式設定 誕生日 と 今日の日付 から年齢を算出し これに" 歳 " という文字列を付加表示 設定後 使用する関数 : TODAY() NOW() 引数は指定せずに コンヒ ュータが内蔵している時計の日付をファイルを開いた時点に読み込みます 更新は [F9] キーで 引数は指定せずに コンヒ ュータが内蔵している時計の時刻をファイルを開いた時点に読み込みます 更新は [F9] キーで DATE() DATE( 年, 月, 日 ) 引数として数値で与えられた [ 年 ][ 月 ][ 日 ] から日付シリアル値を返すが 関数設定時 : シリアル値は セル書式が自動的に日付表示設定される DATEDIF() DATEDIF( 開始日, 終了日, 単位 ) [ 開始日 ] と [ 終了日 ] の差を指定された [ 単位 ]("Y","M","D" など ) で出力 "Y": 満年数 "M": 満月数 "D": 満日数

15 設定内容 セル 設 定 F1 F2 F4:F13 [Ctrl]+[;] キーで その都度入力することもできるが関数によって自動的に入力する方が便利 [Ctrl]+[:] キーで その都度入力することもできるが関数によって自動的に入力する方が便利書式 : ユーサ ー定義により ggee-mm-dd を指定セルの書式は : 自動的に日付になるが シリアル値を確認する場合は書式を標準に設定する G4:G13 DATEDIF() 関数で返される数値の後に & によって " 歳 " という文字列を付加してセルの書式を文字列として表示している (DATEDIF 関数は 本来は数値を返す ) 文字列になると書式が 左寄せになるので 見やすくするために書式の右寄せを設定している 関数に関する一般的な注意 : 関数が扱うテ ータの型 : タイフ 関数内の引数および 関数が出力する戻り値がどういう型のテ ータであるかということを念頭において関数の使用を考えることが ミスをより少なくするコツである おおざっぱに表現すれば セル内に格納すべきテ ータは 数値 か 文字列 だけ 関数 数式を入力されたセルの場合は結果として返す値が 数値 か 文字列 かということを考えなければならない 本例のセル [F4] とこれを参照して関数を設定している [G4] についてエラーの具体例を示す セル [F4] DATE ( C4,D4,E4) と設定したあとで セル書式の表示形式を ggee-mm-dd とユーサ ー定義するという手順で設定を行っているが ユーサ ー定義の手間を省くために TEXT(DATE ( C4,D4,E4),"ggee-mm-dd" という関数設定をしても表面的には 同様の表示結果が得られるが このセル [F4] を参照しているセル [G4] に次のようなエラーが発生する セル [G4] にはセル [F4] を引数にして DATEDIF(F4,$F$1,"Y")&" 歳 " という関数 数式が設定されている ここに使用されているDATEDIF() という関数では 引数 [F4] と [$F$1] が : 日付に対応するシリアル値 ( 数値 ) でなければならないというきまりがあるにもかかわらず 関数 TEXT() を使用してセル [F4] 内にテ ータを出力しているために テ ータ型が 文字列 になっている それが原因で #VALUE! というエラーが発生しているのである ちなみに セル [G4] についてもDATEDIF() という関数の出力値は 数値 であるが アンハ サント :& によって付加文字列 " 歳 " を結合させる数式を入力した段階で セル [G4] に返される値は 文字列 となる エラートラッフ はあらゆる場所にしくまれる 関数を多用して表現を多彩に設定すればする程 こういう罠が気付かぬうちに 出来上がってしまう

16 日付 時間の処理について Excel の日付 時間処理 : Excel というプログラムは 日付 時間を基本的には 数値 として扱う "1 日 " を "1" として扱い 1 時間を "1/24" として扱う (6 時間だと "6/24" "1/4" "0.25" ということになる ) 又 日付という視線から見ると 1900 年 1 月 1 日の 0:00 時刻を "1" として日付 時間を連続的 ( 日付シリアル値処理 ) に加算する (1900 年 1 月 1 日午前 6 時は "1900 年 1 月 1 日午前 0 時 "(1) + "6 時間経過 "(0.25) 数値表示させると "1.25" となる ) 確認例 > 数値 : 2.75 日付形式表示 ( 種類 /3/4/ 13:30) : 1900/1/2 18:00 日付入力について : セミコロン [Ctrl] + [;] キーによる日付の入力 セル表示 セル表示 2006/8/ /8/ /8/19 (8 月 19 日当日の表示 ) ( 翌日 8 月 20 日の表示 ) : 表示値は再入力しない限り変化しない 関数 = TODAY( ) による日付の出力数式ハ ーセル表示 =TODAY( ) 2006/8/ /8/20 : テ ータ更新 [F9] 再計算 ファイル開く度に変化する (8 月 19 日当日の表示 ) ( 翌日 8 月 20 日の表示 ) 日数差の計算について : [A1]:[C3] B 列日付とA 列日付の差をC 列に計算表示 (DATEDIF 関数 ) fxにはない A B C /5/5 2008/8/19 58 [C1] = DATEDIF(A1,B1,"y") : 日数差を年 ("y") 表示 /5/5 2008/8/ [C2] = DATEDIF(A2,B2,"m") : 日数差を月 ("m") 表示 /5/5 2008/8/19 21,291 [C3] = DATEDIF(A3,B3,"d") : 日数差を日 ("d") 表示 年齢計算 : 行 1で C1 = DATEDIF(A1,TODAY(),"y") として [A1] に誕生日を入力すれば当日 TODAY() の年齢を表示 応用 ) [A1] から [B1] へ 58 年 3ケ月と14 日経過というような表示をさせる場合にはセル [C1] に下のような関数式を入力する [C1] = DATEDIF(A1,B1,"y")&" 年 "&DATEDIF(A1,B1,"ym")&" ケ月と "&DATEDIF(A1,B1,"md")&" 日経過 " 時間入力について : コロン [Ctrl] + [:] キーによる時間の入力 セル表示 セル表示 11:50 11:50 11:50 ( 入力時点の時刻表示 ) ( 時間経過後の表示 ) : 表示値は再入力しない限り変化しない 関数 = NOW( ) による時間の出力 数式ハ ー セル表示 =NOW( ) 11:50 11:55 : テ ータ更新 [F9] 再計算 ファイル開く度に変化する ( 入力時点の時刻表示 ) (5 分経過後の表示 ) 時間差の計算について : [A1]:[C3] B 列時刻とA 列時刻の差をC 列に計算表示 [C1] = B1 - A1 : 2 行目 3 行目同様 A B C A B C 1 8:00 17:25 9:25 1 8:00 17:25 9:25 2 8:00 17:38 9:38 2 8:00 17:38 9:38 3 8:00 18:13 10:13 3 8:00 18:13 10:13 4 合計時間 5:16 4 合計時間 29:16 時給 800 給料計算 23,413 時給 800 給料計算 23,413 セル [C4] の書式設定 : 表示形式 セル [C4] の書式設定 : 表示形式 " 時刻 " " ユーサ ー定義 " ( 給料計算は29:16で算出 ) 種類 種類 "13:30" "[h]:mm" 実際はC 列の時間合計は29:16となるはず 左のような場合への対応策 だが 表示形式が標準の時刻表示設定の : 上のようにセル書式を " ユーサ ー定義 " により ままだと 24 時間単位でカットされ : 標準の "h:mm" から "[h]:mm" に設定変更 29:16 - (24の倍数) 5:16 と表示 すればカット処理のない積算時間を表示 実際は上例の左右ともにセル [C4] には 29:16" という時間値 ( 数値テ ータに換算すると "29:16/24:00" " ") が与えられている

17 条件検索 集計関数 SUMIF 現金出納.xls 基本シート構成 : 帳簿 科目 月計 年計 帳簿 : 帳簿形式シート ( 科目シートから VLOOKUP() 関数で科目出力 残高自動計算 ) 科目 : コート 対科目表 VLOOKUP() 用参照シート 月計 : 売上 仕入に限り月別合計 ( 関数 DSUM()) 年計 : < 本講習のテーマ> 科目ごとに年合計 ( 関数 SUMIF())

18 表引きの参照表示について :VLOOKUP( ) 関数の場合 下図は いわゆる現金出納帳をExcelで作成したものである 各項目にはいろんな設定を行っているが よく利用される関数 VLOOKUP( ) を使い コード表から " コード " を参照して対応する " 科目 " を自動入力するという方法である 項目 ( 列 ) 数が下のように比較的少ない場合には ウィンドウの並列表示機能の利用により たえず参照表を右ウィンドウに確認しながらデータ行を逐次入力していくことが出来る : 1 gensui.xlsの構造はシート1が現金出納帳の入力シートであり シート2にコード表をおいて VLOOKUP( ) で " コード " から " 科目 " を参照入力しているわけであるが 下図のような表引き表示状態にするには シート1 表示状態で表示 / ウィント ウ /[ 新しいウィント ウを開く ] という処理によりシート2を開いた後 表示 / ウィント ウ /[ 新しいウィント ウを開く ]/[ 整列 ]/[ 左右に並べて表示 ] とすればよい [ シート 1] [ シート 2: 現金出納コート ] ところがもし 項目 ( 列 ) 数が多い場合には 上のような表引き表示方式ではディスプレイの横幅内に表示しきれなくなって来る場合がある そんな場合の対策が下図の方式である 2 まず シート2のコード表範囲を選択し ホーム / クリッフ ホ ート /[ コヒ ー ] する 次にシート1に戻り ホーム / クリッフ ホ ート /[ 貼り付け ]/[ リンクされた図 ] として貼り付けを指定すると 下図のような 表引き 表示状態となる この場合 参照表は図形としてシート上に貼り付けられているので自由にドラッグ移動させることができる 但し テ ータ行が増えて入力画面がスクロールしたからといって 参照表までが自動的にスクロールしてくれるわけではない 上図 1 の場合は 入力画面がスクロールしても 右のウィント ウ表示は固定されたままなので ドラッグ移動の必要がない 以上 表引き表示についての2 方式 必要に応じて使い分けということになる 当然 2 方式併用も有り ということである 1[ ウィンドウの整列表示 ] 2[ 図形としてリンク貼り付け ]: ともに覚えておくと便利な機能

19 条件検索 集計関数 SUMIF File: 現金出納 Data File 現金出納.xls( 関数設定前 ) 現金出納.xls( 関数設定後 ) セル 設定 セル C2, C4:C6 収入科目毎の合計値 (SUMIF) C32 D8, D10:D29 支出科目毎の合計値 (SUMIF) D32 D31 支出科目 "200" の合計値 (SUMIF) D34 設定収入科目の総合計値 (SUM) 支出科目の総合計値 (SUM) 期末繰越金額

20 条件検索 集計関数 SUMIF File: 現金出納 セル番地 関数 機能 C2 SUMIF SUMIF( 範囲, 検索条件, 合計範囲 ) C4:C6 範囲で指定したセル内で検索条件を満たすデータの合計範囲セルの合計値を返す 集計 D8 SUMIF SUMIF( 範囲, 検索条件, 合計範囲 ) D10:D29 範囲で指定したセル内で検索条件を満たすデータの合計範囲セルの合計値を返す 集計 D31 SUMIF SUMIF( 範囲, 検索条件, 合計範囲 ) 範囲で指定したセル内で検索条件を満たすデータの合計範囲セルの合計値を返す 集計 C32 SUM SUM( 範囲 ) 範囲で指定したセル内の数値データの合計を返す 集計 D32 SUM SUM( 範囲 ) 範囲で指定したセル内の数値データの合計を返す 集計 D34 数式 = C32 - D32 数式の意味 > 収入総合計 - 支出総合計で期末繰越金額算出 数式 31 A31 SUMIF( ) について = SUMIF( 範囲, 検索条件, 合計範囲 ) 引数 範囲 は検索対象となるデータセル範囲 検索条件 は絞込み条件としてのデータ値 合計範囲 は合計すべきデータセル範囲となるが 範囲 と 合計範囲 は 1 対 1 対応していなければならない 又 検索条件は実際の " 値 " " セル番地 " " 条件式 " のどれをしても良い 条件式に演算子を使用する場合には "" で囲むことで指定することが出来る 下図に示した例は 日付が "05/03" 以降の販売額合計を [C13] に与えるものである [ 日付 ] データセル 範囲 を指定し 検索条件 セル [B13] に "05/03" を入力すれば販売額 合計範囲 から該当する行のデータ値を合計 : ここで 検索条件式に注目 日付が "05/03" 以降ということになると 検索条件に比較演算子を使用しなければならない その使い方が下図右 ">=" という文字列形式で演算子を入力し 文字列連結記号 & によって 条件値が置かれている [B13] を連結するという方法である " " を使用せずに & セル番地 を指定するのがミソ 関数の引数として検索条件に比較演算子を指定する場合のキマリとして頭に入れておくとよい

21 集計処理 テ ータ / アウトライン / [ 小計 ] (1/2) File: 現金出納 SUMIF 関数の便利さを比較確認するために 集計方法 : テ ータ / アウトライン /[ 小計 ] について解説します 集計方法テ ータ / アウトライン / [ 小計 ] 1 現金出納.xls を開き 2 テ ータ / 並べ替えとフィルター / [ 並べ替え ] アイコンをクリック 条件設定は上図のとおり 3 手順 2の処理によりテ ータ行は科目コート 月日という区分順に並べ替えられる 4 手順 3の後に メニュー [ テ ータ ] [ 集計 ] テ ータ / アウトライン /[ 小計 ] 設定内容ク ルーフ 基準 : [ 科目 ] 集計の方法 : [ 合計 ] 集計するフィールト : [ 収入 ] [ 支出 ] 設定完了して [OK] ホ タンをクリック * 集計状態を解除する場合は集計の設定画面で : [ すべて削除 ] ホ タンをクリック 5 手順 4 により 右図のような行集計表画面が表示される ク ルーフ [1] 総計表示ク ルーフ [2] 小計表示ク ルーフ [3] 精細テ ータ表示 右図は全てのク ルーフ が表示されている状態

22 集計処理 テ ータ / アウトライン / [ 小計 ] (2/2) [2] アイコン 6 手順 5 からク ルーフ 2( 小計表示 ) アイコンをクリックすると精細テ ータの表示が消え 科目集計表示になる File: 現金出納 7 この状態で集計表示領域をト ラック 選択しホーム / 編集 /[ 検索と選択 ]/ 条件を選択してシ ャンフ 8 選択オフ ション画面で [ 可視セル ] をチェック 9 手順 8 の処理で選択範囲がコヒ ー可能な状態になるので ホーム / クリッフ ホ ート / コヒ ー 10 手順 9 につづいて 貼り付け先のセルをクリック選択して ホーム / クリッフ ホ ート / 貼付け 集計状態を解除する場合は集計の設定画面で : テ ータ / アウトライン /[ 小計 ] [ すべて削除 ] ホ タンをクリック 11 集計行を解除した状態では : 同じ集計シート上に貼り付けた場合は集計行の行数だけ 差し引かれたセル位置にヘ ーストされる : 本例では 370 行目に貼り付けたものが 集計行の数 "12" だけ差し引かれた 358 行目にヘ ーストされることになる

23 IF 文と論理関数 数式 関数式を入力する場合によく使用される条件分岐関数 IF() についての説明 IF 関数の構造 = IF ( 1, 2, 3 ) 1 条件式 2 条件式に一致の時の出力値 3 条件式に不一致の時の出力値 1: 条件式 2:YES(TRUE 真 ) 3:NO(FALSE 偽 ) IF 文と論理関数 : IF 文の基本構造 IF ( 論理式, 論理式の判定結果がTRUEの場合の戻り値, 論理式の判定結果がFALSEの場合の戻り値 ) :TRUEとは判定結果が" 真 "(YES) :FALSEとは判定結果が" 偽 "(NO) 例 1) 論理式の検証 単純式 ( 数式内演算子 : =, >, <, <> 比較演算子 論理関数 (AND: 引数条件式を全て満たす OR: 引数条件式のどれかを満たす 論理関数 (NOT: 引数条件式を満たさない NOT(OR()):OR 内の引数条件を全て否定 A B =IF(A1=1,1,0) 2 2 Y =IF(A1=1,"Y","N") 戻り値が文字列の場合は " " で囲む 3 Y =IF(AND(A1=1,A2=2),"Y","N") 4 Y =IF(OR(A1=1,A2=2),"Y","N") 5 N =IF(NOT(OR(A1=1,A2=2)),"Y","N") N =IF(AND(A1<>1,A2<>2),"Y","N") 同じ意味 : 結果は同じ 例 2) A B =IF(A1=1,1,0) 2 2 N =IF(A1=1,"Y","N") 戻り値が文字列の場合は " " で囲む 3 N =IF(AND(A1=1,A2=2),"Y","N") 4 Y =IF(OR(A1=1,A2=2),"Y","N") 5 N =IF(NOT(OR(A1=1,A2=2)),"Y","N") N =IF(AND(A1<>1,A2<>2),"Y","N") 同じ意味 : 結果は同じ 例 3) A B =IF(A1=1,1,0) 2 0 N =IF(A1=1,"Y","N") 戻り値が文字列の場合は " " で囲む 3 N =IF(AND(A1=1,A2=2),"Y","N") 4 N =IF(OR(A1=1,A2=2),"Y","N") 5 Y =IF(NOT(OR(A1=1,A2=2)),"Y","N") Y =IF(AND(A1<>1,A2<>2),"Y","N") 同じ意味 : 結果は同じ

24 岩屋パソコン教室 Excel 関数講座 第 1 週補講 セルの 相対番地 と 絶対番地 Excelを使う方には当然の基本知識として確認しておいていただきたいセルの 相対番地 と 絶対番地 について第 1 週の授業内容を追いながら解説します RANK 関数の場合 : 評価されるべきセルの番地 ランク付けに使用するグループ全体の範囲 以上の 2 種類のセル番地を指定しなければならない セル [F3] にそれぞれを相対番地で指定してからセル [F12] までドラッグ複写すると評価されるべきセルは相対的に移動して問題無い ただし グループ範囲のセル [E3:E12] は複写によって変化してはならないのにセル [E12:E21] と変化した これでは正しいランク付けにはならない [ 対策 ] ドラッグ複写により相対的に変化してはならないセル範囲を絶対番地化することで 複写によるエラーを避けることができる [ 修正 ] セル [F3] の数式の内 範囲を指定しているセル範囲 [E3:E12] を編集画面からドラッグ選択し ファンクションキー [F4] を押す事で絶対番地化 ( 各々の番地頭に $ マークを付加する ) させる この後 セル [F3] をセル [F12] までドラッグ複写する [ 完了 ]

25 あとがき 本講習会では300 以上ある関数の中から実用性の高い関数 25 種について解説しています 更に学習したい方は Excel 関数ハント フ ックなどを購入して広大なExcel 関数の世界を覗いてみて下さい 本講習会で使用した関数の [ 名称 ] [ 働き ] は下記のとおりです [ 関数名 ] [ 働き ] [ 関数名 ] [ 働き ] [ 関数名 ] [ 働き ] SUM 合計値 COUNTIF 条件検索件数 SUMIF 条件検索合計 MAX 最大値 VLOOKUP 表引き :V MIN 最小値 HLOOKUP 表引き :H AVERAGE 平均値 COUNT 数値テ ータ件数 INDEX 索引簿検索 RANK 順位付け LARGE 順位検索 INT 整数処理 SMALL 順位検索 ROUND 四捨五入 ROUNDDOWN 切捨て IF 条件分岐 ROUNDUP 切上げ DATE 日付 AND 完全条件 TODAY 日付 : 現在日 OR 複合条件 NOW 時間 : 現在時間 NOT 否定条件 DATEDIF 差分 : 年 月 日 セル書式設定 表示形式 : ユーザー定義 表示 書式記号 表示 書式記号 表示 書式記号 を1234.6とする ####.# 1~12( 月 ) m H ( 元号 ) g 8.9を8.900とする # ~12( 月 ) mm 平 ( 元号 ) gg 123を000123とする Jan~Dec( 月 ) mmm 平成 ( 元号 ) ggg の 1~30( 日 ) d 00~99( 元号 ) ee???.??? 小数点の位置を揃える 01~30( 日 ) dd 00~23( 時 ) hh 12000を12,000とする #,### Sun~Sat( 日 ) ddd 00~59( 分 ) mm(hの後 ) 12000を12とする #, 日 ~ 土 ( 日 ) aaa 00~59( 秒 ) ss を12.2とする 0.0,, 日曜日 ~ 土曜日 ( 日 ) aaaa 25:02など累積時間 [h]:mm ハ ソコン教室提供 : 淡路市教育委員会 テキスト製作 : 淡路市商工会 講習会講師 スタッフ いわやハ ソコンクラフ がそれぞれ担当し すべて無報酬でこれを行うことで " 受講費 : 無料 " が実現したことをご報告しておきます

26 無断転載厳禁 いわやパソコンクラブ 岩屋公民館