桁区切りスタイルの表示形式 2O2 参照 0 円 #,##0" 円 " 関数の基本 項 目 ど ん な 場 合 使 用 関 数 スペース 不要なスペースを削除する TRIM( トリム ) 関数 2A2 参照 文字 ( 英字 数字 カナ文字 ) 半角文字から全角文字に変換する JIS( ジス ) 関数

Transcription

1 < 関数関連 > 月日 曜日 の入力方法 < 見本 > < 入力方法 > 9/1 金 9/1 6 金金 9/2 土 9/1 と入力 9/2 7 土土 9/3 日 9/3 1 日日 9/4 月 関数 =weekday(f4,1) と 9/4 2 月 月 9/5 火 入力 9/5 3 火 火 9/6 水 9/6 4 水水 9/7 木 9/7 5 木木 9/8 金 9/9 土 9/10 日 関数 =text(f4,"aa") と入力 < 書式 条件付き書式 > セルの値が 次の値に等しい =" 土 " 書式 パターン 色選択 OK OK 数値の次に 人 を入れる場合 1 1 人 セルの書式設定 表示形式 ユーザー定義 G/ 標準 " 人 " 2 2 人 3 3 人 文字の次に 様 を入れる場合 様 セルの書式設定 表示形式 様 " 様 様 桁を揃える方法 セルの書式設定 表示形式 ユーザー定義 #,##o"" ,456 3,456 関数を使って表示する場合月曜日 月曜 =left(text(b7,"aaaa "),2) 月日を参照して曜日を求める場合 ( 月曜日の場合 月関数を使って表示させる ) ###### 月 =text(b21,"aaa ") =left(text(b21,"aaaa "),1) 関数を使って表示する場合平成 18 年 5 月 1 日現在 5 月現在 =text(today(),"m 月現在 ")

2 桁区切りスタイルの表示形式 2O2 参照 0 円 #,##0" 円 " 関数の基本 項 目 ど ん な 場 合 使 用 関 数 スペース 不要なスペースを削除する TRIM( トリム ) 関数 2A2 参照 文字 ( 英字 数字 カナ文字 ) 半角文字から全角文字に変換する JIS( ジス ) 関数 2A2 2C2 参照 文字 ( 英字 数字 カナ文字 ) 全角文字から半角文字に変換する ASC( アスキー ) 関数 2G2 参照 文字 ( 英字 ) すべて大文字に変換する UPPER( アッパー ) 関数すべて小文字に変換する LOWER( ロウワー ) 関数 文字 ( 英字 ) 先頭文字のみ大文字に変換 PROPER( プロパー ) 関数 文字 文字列の数字を数値に変換 VALUE( バリュー ) 関数 2B2 参照数値を書式設定して文字列に変換 TEXT( テキスト ) 関数 数値を指定した桁数で四捨五入する ROUND( ラウンド ) 関数 数値 数値を指定した桁数に切り上げる ROUNDUP( ラウンドアップ ) 関数 数値を指定した桁数に切り捨てる ROUNDDOWN( ラウンドダウン ) 関数 2A2 参照 空白セル 指定された範囲内の空白セル個数を返す COUNTBLANK 関数 2F2 参照 絶対値 数値の絶対値を返す ABS 関数 2H2 参照 超える範囲 数値を切り上げて基準値の倍数に最も近い値にする CEILING( セイリング ) 関数 2L2 参照 ( 例 ) (20 を基準とした場合 ) 超えない範囲 数値を切り捨てて基準値の倍数に最も近い値にする FLOOR( フロアー ) 関数 2K2 2L2 参照 ( 例 ) (20 を基準とした場合 ) 時刻文字列 文字列で表された時刻を小数に変換 TIMEVALUE( タイムバリュー ) 関数 2K2 参照 ROUND 関数の桁数 桁数 数値の位 例 2 少数第 2 位 少数第 1 位 整数表示 の位 の位 50 WEEKDAY 関数 2G2 参照種類 3 種類 1 日曜日 1 ~ 土曜日 7 2 月曜日 1 ~ 日曜日 7 3 月曜日 0 ~ 日曜日 6 DAY 関数 2I2 参照 DAY DATE(YEAR()),MONTH()+1,25),DATE(YEAR(),MONTH()+2,25) DATE 関数 2L2 参照 年月データを参照して月日を求める場合 西暦 2006 =DATE(E39,E40,E41) 2006/6/ 1 月 6 日 1 平均の計算 (AVERAGE 関数 ) =AVERAGE( セル番地 : セル番地 ) 最大 最小 (MAX MIN 関数 ) 1 MAX( マックス ) 関数 最大値の検出 =MAX( セル番地 : セル番地 )

3 2 MIN( ミニマム ) 関数 最小値の検出 =MIN( セル番地 : セル番地 ) データのカウント (COUNT COUNTA 関数 ) 1 COUNT 関数 数値データのカウント =COUNT( セル番地 : セル番地 ) 2 COUNTA( カウントエー ) 関数 数値 & 文字データのカウント =COUNTA( セル番地 : セル番地 ) データの四捨五入 切り上げ 切り捨て (ROUND ROUNDUP ROUNDDOWN 関数 ) 1 ROUND 関数 データの四捨五入 =ROUND( 数値またはセル番地, 桁数 ) 2 ROUNDUP 関数 データの切り上げ =ROUNDUP( 数値またはセル番地, 桁数 ) 3 ROUNDDOWN 関数 データの切り捨て =ROUNDDOWN( 数値またはセル番地, 桁数 ) 順位づけ (RANK LARGE SMALL 関数 ) 1 RANK 関数 数値の高い順 ( 降順 ) に順位付けをする =RANK( 順位付けるセル番地, 対象範囲, 順序 [0 または 1]) 対象範囲は絶対参照 (F4 キー ) とする [0] 降順 ( 大きい順 ) [1] 昇順 ( 小さい順 ) 2 LARGE 関数 n 番目に大きい値を探す =LARGE( 対象範囲, 順位 ) 順位はセル番地を指定する 3 SMALL 関数 n 番目に小さい値を探す =SMALL( 対象範囲, 順位 ) 順位はセル番地を指定する 条件の判定 (IF 関数 ) 1 条件の判定 =IF( 条件, 真の場合, 偽の場合 ) 2 条件の複合 =IF( 論理演算子 ( 条件, 条件, ) 真の場合, 偽の場合 ) 複合条件の論理演算子として AND 関数,OR 関数,NOT 関数 が使用できる AND 関数 条件をすべて満たしている場合 ( 例 :A と B が の場合 ) OR 関数 条件のうち 1 つでも満たしている場合 ( 例 :A または B が の場合 ) 3 ネスト =IF( 条件, 真の場合,(IF( 条件, 真の場合,, 偽の場合 ))) 桁数 数値の位 例 2 小数第 2 位 小数第 1 位 整数表示 の位 の位 50 式 入力方法 意味 例 >= 以上 100 以上 100,101 <= 以下 100 以下 100,99 > > 超える ~より大きい 100 を超える 101,102 < < 未満 満たない ~より小さい 100 未満 99,98

4 表データの検索 (VLOOKUP HLOOKUP INDEX 関数 ) 1 VLOOKUP 関数 表の中の一番左側のデータを 縦方向に検索する =VLOOKUP( 検索値, 範囲, 列番号, 検索の型 ) 範囲は絶対参照 (F4 キー ) とする 検索の型意味 FALSE または 0 検索値と完全に一致する値を検索の場合検索値と完全に一致しない値を検索の場合 TRUE または 1 ( 検索値未満の最大値を検索する場合 ) 範囲の左端列は 昇順 に並べ替えてから 2 HLOOKUP 関数 表の中の一番上の行のデータを 横方向に検索する =HLOOKUP( 検索値, 範囲, 行番号, 検索の型 ) 範囲は絶対参照 (F4 キー ) とする 検索の型意味 FALSE または 0 検索値と完全に一致する値を検索の場合検索値と完全に一致しない値を検索の場合 TRUE または 1 ( 検索値未満の最大値を検索する場合 ) 範囲の上端行は 昇順 に並べ替えてから 3 INDEX 関数 表の範囲中の 指定した行番号と列番号が交差する位置を検索する =INDEX( 配列, 行番号, 列番号 ) 配列は見出し部分を除く 全ての範囲を選択する 範囲は絶対参照 (F4 キー ) とする 4 MATCH( マッチ ) 関数 検査値と一致するデータが 上または横から数えて何番目にあるかを検索する =MATCH( 検査値, 検査範囲, 照合の型 ) 検査範囲は 1 行または 1 列のみの範囲しか選択できない 照合の型意味検索値以上の最小値 ( 大きい順 ) を検索 -1 検査値データは 降順 に並び替えてから 0 検索値と一致する値のみ検索検索値以下の最大値 ( 小さい順 ) を検索 1 検査値データは 昇順 に並び替えてから 通常は 0 で OK 名前ボックスに 名前 をつける場合 1 名前をつける範囲をドラック 2 名前ボックス欄をクリック 名前を入力 Enter キー 変更または削除する場合メニューバー 挿入 名前 定義 対象の名前を選択 削除 文字列の加工 (1)(LEFT RIGHT MID FIND 関数 ) 1 LEFT 関数 文字列の先頭 ( 左端 ) から 指定した文字分の文字を抜き出す =LEFT( 文字列またはセル番地, 文字数 ) 2 RIGHT 関数 文字列の末尾 ( 右端 ) から 指定した文字分の文字を抜き出す =RIGHT( 文字列またはセル番地, 文字数 ) 3 MID( ミッド ) 関数 文字列の任意の位置から 指定した文字分の文字を抜き出す =MID( 文字, 開始位置, 文字数 )

5 4 FIND 関数 対象となる文字列から 指定した文字列が何文字目にあるかを抜き出す =FIND( 検索文字列, 対象, 開始位置 ) 文字列の加工 (2)(UPPER LOWER JIS ASC PROPER 関数 ) 1 UPPER 関数 文字列に含まれる英字を すべて大文字に変換する =UPPER( 文字列 ) 2 LOWER( ロウワー ) 関数 文字列に含まれる英字を すべて小文字に変換する =LOWER( 文字列 ) 3 JIS 関数 半角の英数カナ文字を 全角の文字に変換する =JIS( 文字列 ) 4 ASC( アスキー ) 関数 全角の英数カナ文字を 半角の文字に変換する =ASC( 文字列 ) 5 PROPER( プロパー ) 関数 文字列中の英単語の先頭文字を大文字に 2 文字以降は小文字に変換する =PROPER( 文字列 ) 文字列の加工 (3)(VALUE LEN TEXT CONCATENATE 関数 ) 1 VALUE( バリュー ) 関数 文字列データを 数値データに変換する =VALUE( 文字列 ) 2 LEN 関数 文字列の文字数を計算する =LEN( 文字列 ) 3 TEXT 関数 数値を 書式設定した文字列に変換する =TEXT( 値, 表示形式 ) 種類書式記号結果表示西 yy 00~99 年暦 yyy y 1900~999 和暦 月 日 曜日 元号 年 g M T S H ( 例 ) gg 明 大 昭 平 ge H7 gg 明治 大正 昭和 平成 gge 平 7 e 1~99 ggg e 平成 7 年 ee 01~99 ggee 平成 07 年 m 1~12 mm 01~12 mmm Jan~Dec mmmm January~December mmmmm J~D d 1~31 dd 01~31 dd Sun~Sat ddd d Sunday~Saturday aa 日 ~ 土 aaa a 日曜日 ~ 土曜日 4 CONCATENATE( コンキャティネイト ) 関数 複数の文字列を 連結して 1 つの文字列にまとめる =CONCATENATE( 文字列 1, 文字列 2, )

6 数学 / 三角関数 (INT MOD ROUNDUP ROUNDDOWN SUMIF SUBTOTAL 関数 ) 1 INT( イント ) 関数 小数部を切り捨てて 整数の値にする =INT( 数値またはセル番地 ) 2 MOD( モッド ) 関数 除数で割った 余りを求める =MOD( 数値またはセル番地, 除数またはセル番地 ) 3 ROUNDUP 関数 データの切り上げ =ROUNDUP( 数値またはセル番地, 桁数 ) 4 ROUNDDOWN 関数 データの切り捨て =ROUNDDOWN( 数値またはセル番地, 桁数 ) 5 SUMIF 関数 ある条件のみを合計する =SUMIF( 範囲, 検索条件, 合計範囲 ) 範囲と合計範囲は 絶対参照 (F4 キー ) とする 6 SUBTOTAL 関数 リストまたはデータベースの数値を集計する =SUBTOTAL( 集計方法, 範囲 1, 範囲 2, ) No 集計方法 1AVERAGE 2COUNT 3COUNTA 4MAX 5MIN 6PRODUCT No 集計方法 7STDEV 8STDEVP 9SUM 10VAR 11VARP 統計関数 (COUNTIF LARGE SMALL 関数 ) 1 COUNTIF 関数 検索条件に一致する セルの個数をカウント =COUNTIF( 範囲, 検索条件 ) 範囲は 絶対参照 (F4 キー ) とする 検索条件を入力後は Tab キーを必ず押すこと 検索条件にセル番地を使用する場合は & を使う ( 例 ) セルG13 平均 374 以上 1 数値を使う場合 ">=374" 2セル番地を使う場合 ">="&G3 2 LARGE 関数 n 番目に大きい値を探す =LARGE( 対象範囲, 順位 ) 順位はセル番地を指定する 3 SMALL 関数 n 番目に小さい値を探す =SMALL( 対象範囲, 順位 ) 順位はセル番地を指定する 日付 / 時刻関数 (1)(TODAY YEAR MONTH DAY DATE 関数 ) 1 TODAY 関数 現在の日付 =TODAY()

7 2 YEAR 関数 日付に対応した年 =YEAR( シリアル値 ) 3 MONTH 関数 日付に対応した月 =MONTH( シリアル値 ) 4 DAY 関数 日付に対応した日 =DAY( シリアル値 ) 5 DATE 関数 指定した日付に対応した年 月 日 =DATE( 年, 月, 日 ) ( 例 )1 年後 シリアル値 + 1 ( 例 ) 翌月 シリアル値 + 1 ( 例 )5 日後 シリアル値 + 5 日付 / 時刻関数 (2)(NOW TIMEVALUE HOUR MINUTE SECOND 関数 ) 1 NOW 関数 現在の日付と時刻 =NOW() 2 TIMEVALUE( タイムバリュー ) 関数 時刻を小数に変換 =TIMEVALUE( 時刻文字列 ) 3 HOUR( アワー ) 関数 時刻に対応した時 =HOUR( シリアル値 ) 4 MINUTE( ミニット ) 関数 時刻に対応した分 =MINUTE( シリアル値 ) 5 SECOND( セカンド ) 関数 時刻に対応した秒 =SECOND( シリアル値 ) データベース関数 (DSUM DAVERAGE DMAX DMIN DCOUNT DCOUNTA DGET 関数 ) 1 DSUM 関数 リストまたはデータベース全体から フィールド ( 列 ) と Criteria( 条件 ) の合計値 =DSUM(Database, フィールド,Criteria) Criteria( 条件 ) は 行固定 (F4 キー ) とする ( 例 )M$4:M$5 型番 が O-381 で 担当者 が芹沢の 売上金額 の合計と 伝票枚数 型番 担当者 売上合計 O-381 芹沢 伝票枚数 日付 が 4 月 8 日から 4 月 10 日までの 売上金額 の合計日付日付売上合計 >=2005/4/ 8 <=2005/4/1 0 2 DAVERAGE 関数 リストまたはデータベース全体から フィールド ( 列 ) と Criteria( 条件 ) の平均値 =DAVERAGE(Database, フィールド, Criteria)

8 Criteria( 条件 ) は 行固定 (F4 キー ) とする ( 例 )M$4:M$5 3 DMAX 関数 リストまたはデータベース全体から フィールド ( 列 ) と Criteria( 条件 ) の最大値 =DMAX(Database, フィールド,Criteria) Criteria( 条件 ) は 行固定 (F4 キー ) とする ( 例 )M$4:M$5 4 DMIN 関数 リストまたはデータベース全体から フィールド ( 列 ) と Criteria( 条件 ) の最小値 =DMIN(Database, フィールド,Criteria) Criteria( 条件 ) は 行固定 (F4 キー ) とする ( 例 )M$4:M$5 5 DCOUNT 関数 リストまたはデータベース全体から フィールド ( 列 ) と Criteria( 条件 ) の数値の個数 =DCOUNT(Database, フィールド, Criteria) Criteria( 条件 ) は 行固定 (F4 キー ) とする ( 例 )M$4:M$5 型番 が O-381 で 担当者 が芹沢の 売上金額 の合計と 伝票枚数 型番 担当者 売上合計 O-381 芹沢 伝票枚数 売上金額 が 1 万を超える伝票枚数売上金額伝票枚数 >10000 型番 が T-106 か Q-663 の伝票枚数型番伝票枚数 T-106 Q DCOUNTA 関数 リストまたはデータベース全体から フィールド ( 列 ) と Criteria( 条件 ) の空白でないセルの個数 =DCOUNTA(Database, フィールド, Criteria) Criteria( 条件 ) は 行固定 (F4 キー ) とする ( 例 )M$4:M$5 7 DGET 関数 リストまたはデータベース全体から フィールド ( 列 ) と Criteria( 条件 ) の満たす値を 1 つ抽出 =DGET(Database, フィールド,Criteria) Criteria( 条件 ) は 絶対参照 (F4 キー ) とする ( 例 )$M$4:$M$5 伝票 No. が 1014 の売上金額と担当者伝票 No. 売上金額 1014 担当者 データベース機能 ( 抽出 ) < 抽出方法 > データ フィルタ フィルタオプションの設定 抽出先 指定した範囲

9 リスト範囲 検索条件範囲 抽出範囲 フィルターオプションの設定 1 抽出先 指定した範囲 を選択 2リスト範囲 データすべてを選択 ( 項目欄も含む ) Ctrl+ Shift+ + 3 検索条件範囲 ( 例 ) 担当者 が芹沢の場合 下記 1(A1) の表を選択 4 抽出範囲 セル A5 を選択 < 抽出に必要な条件 ( 検索条件範囲 )> 1. 担当者 が芹沢のデータ担当者芹沢 2. 売上金額 が1 万以上で 3 万未満のデータ 売上金額 売上金額 >=10000 < 型番 がS-281 P か Q-663 W のデータ型番 S-281 P Q-663 W 4. 型番 がQ-663 B か 担当者 が真崎のデータ 型番 Q-663B 担当者 真崎 5. 型番 の末尾が W のデータ 型番 *W <ワイルドカード > 1. * 何文字でも制限なし ( 例 )1 佐藤* 佐藤の後の文字は制限なく表示 ( 例 )2 * 佐藤 佐藤の前の文字は制限なく表示 2.? 1 文字のみ ( 例 )1 佐藤? 佐藤の後の 1 文字のみ表示 ( 例 )2? 佐藤 佐藤の前の 1 文字のみ表示 ( 例 )3 佐藤?? 佐藤の後の 2 文字のみ表示 ( 例 )4?? 佐藤 佐藤の前の 2 文字のみ表示 データベース機能 ( 集計 ) 1. 集計 作業を行う ( 型番 のみの合計金額を集計する場合 )

10 データの 型番 を昇順で並べ替える (1) 集計の設定 データ 集計 1 グループの基準 型番 を選択 2 集計の方法 合計 を選択 3 集計するフィールド 売上金額 を選択 4OK (2) アウトラインレベル 2 を選択 (3) 集計結果の必要データを別表にコピーする コピー元のドラッグ 編集 ジャンプ セル選択 可視セル OK コピー 形式を選択 値 OK 2. 集計 作業を行う ( 型番 と 担当者 の合計金額をを集計する場合 ) データの 型番 と 担当者 を昇順で並べ替える ( データ 並べ替え 型番 担当者 昇順 ) (1) 集計の設定 データ 集計 1 グループの基準 型番 を選択 2 集計の方法 合計 を選択 3 集計するフィールド 売上金額 を選択 4OK (2) 集計の設定 データ 集計 1 グループの基準 担当者 を選択 2 集計の方法 合計 を選択 3 集計するフィールド 売上金額 を選択 4 現在の集計表と置き換える のチェックをはずす ( 消す ) 5OK (3) アウトラインレベル 3 を選択 (3) 集計結果の必要データを別表にコピーする コピー元のドラッグ 編集 ジャンプ セル選択 可視セル OK コピー 形式を選択 値 OK 条件付き書式設定 書式 条件付き書式 1セルの値が 次の値に等しい 男 書式 2 数式が =c3=" 男 " 書式 3セルの値が 次の値に等しい =max($c$4:$h$12) 書式 書式設定 ( ユーザー定義 ) 参考結果平成 18 年 04 月 01 日 ggge" 年 "mm" 月 "dd" 日 " H ge.mm.dd 4 月 1 日付 m" 月 "d" 日付 "

11 H ge.mm.dd 4 月 1 日付 m" 月 "d" 日付 " 4 月分 m" 月 " 分 4/1( 土 )m/d(aaa) 4 月 1 日 ( 土曜日 )m" 月 "d" 日 "(aaaa) 2006/4/1(Saturday)yyyy/m/d(ddd) 4/1(Sat)m/d(ddd) 235,691,520#,##0 235,692#,###, または #,##o, 2,356 円 #,##0" 円 " または #,###" 円 " 10 冊 G/ 標準 " 冊 " または #" 冊 " 様 " 様 " 様