アッと驚く達人の技
Excel関数実践技＆ウラ技大全
[97/2000/2002/2003対応]
|
|
Excelが持っている表示機能・加工機能・集計機能などを自在に使いこなすには「関数」という機能を使う必要があります。しかし、関数は命令語やその書式を一文字も間違えずに記述しないと正しく動作してくれないという欠点もあります。 |
|
第1章 表示方法を加工する関数の実践･ウラ技 |
●製品の識別番号をバーコードに変換する･･････16
■バーコードを編集するときはコードの桁数を固定せよ!
■バーコードを印刷するときは16ポイント以上に設定せよ!
■入手したバーコード用フォントは「フォント」フォルダにインストールせよ!
●ゴルフコースのヤード表示をメートルに変換する･･････19
■異なる単位への換算には「CONVERT」関数を使え!
■相互変換できる単位を調べるにはオンラインヘルプを利用せよ!
●粗利率「25.8%」を「2割5分8厘」と表示する･･････21
■数値を独自の単位で表示するときは「TEXT」関数を使え!
■セルの書式設定と「TEXT」関数の違いを理解せよ!
■「123.45」を「123＄45¢」と表示するには整数部と小数部を分けて変換せよ!
●都道府県名を除いた住所を取り出す･･････23
■先頭の数文字分を除くデータを取り出すには「RIGHT」関数を使え!
■複数条件の1つを満たす場合には「IF」関数の代わりに「OR」関数を使え!
●同じセル内に郵便番号と住所を上下に表示させる･･････25
■正しく改行させるにはセル内での改行を許可する設定を行え!
●部署名から課名だけを取り出す･･････27
■文字列を取り出すための引数の値が毎回変わる場合は「FIND」関数で調べろ!
■文字列の先頭から一部分を取り出すには「LEFT」関数を使え!
●姓と名の間の空白文字を半角1文字に統一する･･････29
■全角と半角の文字が混在する場合は「SUBSTITUTE」関数で一方に揃えろ!
■空白文字をすべてカットするには「SUBSTITUTE」関数だけを使え!
●セル内で改行された文字列を1行で表示する･･････31
■セル内の改行を削除するには「CLEAN」関数を使え!
●「月日+連番」形式の受注番号を作成する･･････33
■データを特定の形式で表示するときは「TEXT」関数を使え!
●請求書の金額を「壱弐参」の漢数字で表示する･･････35
■数値を漢数字で表示するには「NUMBERSTRING」関数を使え!
■「NUMBERSTRING」関数で日付を漢数字に変換する場合は数値に変換せよ!
●基準点に満たない科目が1つでもあると赤で「不合格」と表示する･･････37
■条件に満たないセルの値が1つでもあるかどうかは「OR」関数で調べよ!
■入力値によって複数の書式に変化させたいときは条件付き書式を追加せよ!
●「0」で割ったときのエラー値「#DIV/0!」を非表示にする･･････40
■「#DIV/0!」を非表示にするには「ISERROR」関数でエラーの有無を判定せよ!
■エラー値の意味を調べるには[！]マークを使え!
●伝票の空白行にある不要な「0」を消す･･････42
■不要な「0」を表示させたくないときはデータの入力判定を行え!
■参照先が隣り合わせのときは配列数式で式全体を合理化せよ!
●セルの中にワークシート名を表示して表のタイトルにする･･････44
■ワークシート名を取り出すときは不要なパス名やブック名をカットせよ!
■新規ブックのときはいったんブックを保存してから実行せよ!
●1行おきに色を設定して表を縞模様にする･･････46
■奇数行は行数を2で割ったときの余りで見極めろ!
●2つのドロップダウンリストを連携させる ･･････48
■ドロップダウンリストに表示するデータにはあらかじめ名前を登録せよ!
■2つのドロップダウンリストを連携するには参照先を「INDIRECT」関数で指定せよ!
第2章 日付･時刻を操作する関数の実践･ウラ技 |
●記念日までの日数をカウントダウン表示する･･････54
■日数と文字列を組み合わせて表示するには「&」を利用せよ!
■「TODAY」関数を利用する際の注意点
●西暦と和暦を同時に表示する･･････56
■西暦と和暦を表示する場合は2つの表示形式を並べて入力せよ!
■年を和暦表示する際の注意点
●月曜日だけを赤色で「休館日」と表示させる･･････58
■「WEEKDAY」関数は曜日判定の準備に使え!
●同じ列の年月日の位置を揃えて表示させる･･････60
■日付を揃えるには十の位を半角スペースにして整えよ!
■記号で区切られた西暦の桁を合わせるには「/0」を「SUBSTITUTE」関数で置き換えよ!
●月ごとの第3水曜日の日付を求める･･････62
■第3水曜日を求めるには月の第1日目の曜日によって計算式を振り分けよ!
●入社年月日と現在の日付で勤続年月を求める･･････64
■「○年○ヵ月」と表示させるには2つの「DATEDIF」関数を連結せよ!
●勤務開始日と終了日から実労働日を求める･･････66
■平日の日数計算を求めるには「NETWORKDAYS」関数を利用せよ!
●契約日数から平日の契約終了日を求める･･････68
■「WORKDAY」関数で契約終了日を求めるには日数を1日分差し引け!
●月の最終営業日を求める･･････70
■月の最終営業日を求めるには「WORKDAY」関数で月の最終平日を判断させよ!
●購入日から平日の月末引き落とし日を計算する･･････72
■購入日に対する月末引き落とし日は締め日を基準に判別せよ!
●休憩時間を差し引いた勤務時間を計算する･･････74
■数値を時刻として計算する場合は「TIME」関数を利用せよ!
●出社･退社時間を5分切り上げ･切り捨てして勤務時間を求める･･････75
■時間を特定の単位に揃えるには「CEILING」関数と「FLOOR」関数を利用せよ!
●24時間以上の時間の合計を正しく表示する･･････77
■24時間以上の時間を表示させるにはセルの表示形式を変更せよ!
●○時間○分を数値に換算する･･････79
■時間を数値に変換するには「1:0:0」の書式を利用せよ!
●平日と土日の勤務時間を別々に求める･･････81
■条件別に数値を合計する場合には「SUMIF」関数を利用せよ!
●9:00前の出社時間を9:00に統一して勤務時間を計算する･･････83
■定時前の出社時間を切り上げるには「MAX」関数を利用せよ!
●作業時間を通常･残業･深夜残業に分類して表示する･･････84
■時刻によって分類するには「MAX」関数で時間を選択させよ!
■通常の勤務時間は残業時間を求めた後に計算せよ!
●日付をまたぐ作業時間を計算する･･････87
■日付をまたぐ時間は24時間プラスして計算せよ!
●現在の日時が受付時間を経過した場合に「受付終了」と表示させる･･････88
■現在の時刻を調べるには「TIME」関数と「NOW」関数を利用せよ!
■現在の日時を経過したかどうかを調べるには「OR」「AND」関数を利用せよ!
第3章 データ集計を行う関数の実践･ウラ技 |
●売上金額の累計を1日ごとに集計する･･････92
■累計を求めるには「SUM」関数の先頭の引数を絶対参照で指定せよ!
●単価×数量の総合計を一気に集計する･･････94
■「SUMPRODUCT」関数には同じサイズの配列を指定せよ!
●特定期間のデータのみを集計する･･････95
■特定期間の合計は2つの期間の差で求めよ!
●週ごとにデータを集計する･･････96
■週の始まりを月曜に変更するには「WEEKNUM」関数で指定せよ!
■週間合計は第1週目を基準に「SUMIF」関数で条件設定して求めよ!
●データを3行ずつ集計する･･････98
■n行ごとにデータを求めるには行番号をnで割った余りを利用せよ!
■エラー指摘のマークを解除するには[エラーを無視する(Ｉ)]を選択せよ!
●複数の条件を満たすデータを集計する･･････100
■複数条件で合計を求めるには「DSUM」関数を利用せよ!
●入力した項目と一致したデータを集計する･･････102
■条件の有無で結果を求めるには「COUNTIF」関数を利用せよ!
●会場名と期間を指定してデータを集計する･･････104
■「OFFSET」関数の基準セルを可変にするにはセル名を設定せよ!
■指定した期間の人数は2つの期間の差で求めよ!
●オートフィルタで絞り込んだデータを集計する･･････106
■オートフィルタで絞り込んだデータを集計するには「SUBTOTAL」関数を利用せよ!
●チェックしたデータだけを集計する･･････108
■チェックしたデータを集計するには「SUMIF」関数の検索条件に「TRUE」を指定せよ!
●複数のワークシートのデータを項目ごとに集計する･･････111
■ワークシート名は「INDIRECT」関数で指定せよ!
●2つの表を1つに連結する･･････114
■2つの表を1つに連結するには「VLOOKUP」関数を利用せよ!
■エラー値を非表示にするには「ISERROR」関数を利用せよ!
●同じデータの連続回数を数える･･････116
■連続回数を数えるには前後のセルの内容を比較せよ!
●重複した内容を除いて人数をカウントする･･････118
■重複したデータを調べるには「COUNTIF」関数を利用せよ!
■重複したデータを除いて集計するには「COUNTIF」関数で「1」を検索せよ!
●金種表で紙幣･硬貨の枚数を集計する･･････120
■1万円札の必要枚数を求めるには「QUOTIENT」関数を利用せよ!
■10000円以外の枚数は支払額から枚数×金額を引いた差額から計算せよ!
第4章 データ分析を行う関数の実践･ウラ技 |
●支店ごとに各社員の売上順位を求める･･････126
■支店ごとの順位を求めるには条件をANDで指定せよ!
■数値の順位を求めるには自分より大きな数値の個数をカウントせよ!
●目標契約数を達成した社員数を求める･･････128
■条件に一致するデータの個数を求めるには「COUNTIF」関数を使え!
●集計表から複数の条件を満たすデータ数を求める･･････130
■「DCOUNT」関数の集計条件は表形式で指定せよ!
●セールスが成功する確率を予測する･･････132
■「BINOMDIST」関数で目的の件数ごとの確率を求めるには「FALSE」を指定せよ!
■成功率は百分率で指定せよ!
●くじ引きが当たる確率を予測する･･････134
■標本の成功確率を求めるには「HYPGEOMDIST」関数を使え!
■「HYPGEOMDIST」関数を使う際は成功数の大きさに注意せよ!
●過去の数値の出現率から宝くじの当選番号を予測する･･････136
■出現回数が2番目に多いデータを取り出すには配列数式を使え!
●過去5年間の平均成長率を求める･･････138
■平均伸び率を求めるには「GEOMEAN」関数を使え!
●入社試験の合格者を判定する･･････140
■合否ラインを判定するには「PERCENTILE」関数を使え!
●テストの成績を相対評価で3段階に分ける･･････142
■値の相対的な順位を求めるには「PERCENTRANK」関数を使え!
●消費者の年齢層ごとの度数分布表を作成する･･････144
■度数分布表を作成するには「FREQUENCY」関数を使え!
●商品の売上高を予測する･･････146
■伸び率が直線的なデータの予測は「TREND」関数を使え!
■データが急上昇している場合の予測は「GROWTH」関数を使え!
●商品の仕入数を回帰分析で予測する･･････148
■回帰分析でデータを予測するには「FORECAST」関数を使え!
■回帰分析を行う際にはデータの相関係数を調べよ!
■回帰分析の標準誤差を求めるには「STEYX」関数を使え!
●期末テストの標準偏差と偏差値を求める･･････150
■標準偏差を求めるには「STDEVP」関数を使え!
■偏差値は標準偏差と平均値から求めよ!
第5章 金融･財務を管理する関数の実践･ウラ技 |
●得意先ごとの売掛金の未回収額を求める･･････154
■複数の条件に合うデータを合計するには「SUMPRODUCT」関数を使え!
●指定した月の手形決済額を求める･･････157
■指定した月の月初と月末の日付を求めるには「DATE」関数を使え!
●株式の売買損益と配当を年利に換算する･･････159
■株式投資の利回り計算には「INTRATE」関数を使え!
●割引金融債の利回りを求める･･････161
■割引金融債の利回りを求めるには「YIELDDISC」関数を使え!
●手形の割引料を一発で求める･･････163
■手形の割引料を求めるには「ACCRINTM」関数の引数[満期日]に「1」を加えよ!
●有価証券の未収利息を求める･･････165
■決算日直前の利払日を求めるには「COUPPCD」関数を使え!
■有価証券の未収利息を求めるには「ACCRINTM」関数を使え!
●減価償却費を求める･･････169
■減価償却のための月割り計算には「YEAR」関数と「MONTH」関数を使え!
■「DB」関数の引数には「MOD」関数や「ROUNDUP」関数を使え!
■定額法の減価償却費を求めるには「SLN」関数に当期使用月数の割合を掛けろ!
●税務申告用の減価償却費を求める･･････173
■税法の減価償却費を計算するには償却率表を使え!
●黒字と赤字の分岐点を求める･･････176
■損益分岐点を求めるには「SLOPE」関数と「INTERCEPT」関数を使え!
●品切れを防ぐ発注点を求める･･････178
■安全在庫量を求めるには「NORMSINV」関数を使え!
■発注点を求めるにはリードタイムの平均販売量に安全在庫量を加えろ!
●預金の元利合計を求める･･････181
■預金の複利計算を行うには「FV」関数を使え!
■利息計算を行うには利率と期間の時間的単位を合わせよ!
●積立金の満期額を一発で求める･･････183
■積立金の満期額を求めるには「FV」関数を使え!
■積立金の目標満期額から積立額を求めるには「PMT」関数を使え!
●借入金の支払額を求める･･････185
■事業資金の借入利息を求めるには「ISPMT」関数を使え!
■事業資金の借入の元金を求めるには借入額を支払回数で割れ!
●ローン支払額の内訳を求める･･････188
■元利均等返済の支払額を求めるには「PMT」関数を使え!
●ローンの支払回数を求める･･････190
■「NPER」関数で返済回数を求めるには引数[現在価値]に借入額を入力せよ!
●ボーナス併用ローンの借入可能額を求める･･････192
■ボーナス払いの計算を行うには時間的単位を半年にせよ!
●ボーナス併用ローンの支払額を求める･･････194
■ボーナス時の支払額を求めるには「PMT」関数を使え!
●段階金利型ローンの支払額を求める･･････196
■最初の固定期間以降の借入残高は「CUMPRINC」関数で求めよ!
●住宅ローンの繰上返済の支払額を求める･･････199
■期間短縮型の繰上返済額を求めるには「CUMPRINC」関数を使え!
■繰上返済の節約利息を求めるには「CUMIPMT」関数を使え!
■支払額圧縮型の計算では「CUMPRINC」関数を使って支払い済み元金を求めよ!
●投資の利益額と利益率を求める･･････203
■投資の収益性を求めるには「NPV」関数を使え!
■投資額が大きく異なる投資プランを比較するには「IRR」関数を使え!
第6章 数学的な計算を行う関数の実践･ウラ技 |
●売り上げの伸びを単純な数値で比較する･･････208
■割合を求めるには各数値を最大公約数で割り算せよ!
●三角関数を使用して鉄塔の高さを求める･･････210
■直角三角形の高さを求めるには「TAN」関数を使え!
●緯度と経度から目的地への方角を調べる･･････212
■緯度と経度から方位角を求めるには三角関数を使用して2つの地点の角度の値を求めよ!
■β1の値が真南を基準にしているときは補正して方位角を求めろ!
●「EXP」関数を使用して放射性物質の質量の変化を求める･･････214
■eを底とする数値のべき乗を求めるには「EXP」関数を使用せよ!
■底がeではない値のべき乗の計算には「POWER」関数を使え!
●50円以下の端数が出ないように割引価格を決める･･････216
■数値を特定の単位で切り捨てるには「FLOOR」関数を使え!
■目的に応じて「FLOOR」「CEILING」「ROUND」関数を使い分けよ!
●平方根を使用して音速を求める･･････218
■平方根を求めるには「SQRT」関数を使用せよ!
●余りを求めて干支を表示する･･････220
■周期性のある数値の判定には「MOD」関数を使用せよ!
■余りの値で参照するには「VLOOKUP」関数の指定範囲の左端に余りの列を用意せよ!
●モンテカルロ法で図形の面積を求める･･････223
■整数の乱数を生成させる場合は「INT」関数を数式に組み込め!
■負の乱数が必要なときは作成した乱数を差し引け!
■面積を求めるには図形の内側にある点の数を数えろ!
●テストの得点から成績を自動判定する･･････227
■成績区分の判定を行うには「MATCH」関数と「INDEX」関数を組み合わせて使用せよ!
●順列組み合わせでロイヤルストレートフラッシュの確率を求める･･････229
■何通りの組み合わせができるかを調べるには「COMBIN」関数を使え!
■順列を求めるには「PERMUT」関数を使え!
●地震のエネルギーの大きさからマグニチュードを計算する･･････231
■対数の計算には「LOG」関数を使え!
●積分で円周率を求める･･････232
■円周率を求めるには積分を利用せよ!
●順列を使用して自然対数の底を求める･･････235
■自然対数の底を求めるには順列を利用せよ!
●最高売り上げを記録した日付を表示する･･････237
■日付を表示するには値の列の右横に日付の列を作成せよ!
●紙幣と硬貨の数が最小になるように円をドルに交換する･･････239
■要素間の積を合計するには「SUMPRODUCT」関数を使用せよ!
第7章 関数を自作する実践･ウラ技 |
●関数を自作するためのユーザー定義関数について･･････244
●平方メートルを坪数に換算する関数を作る･･････247
■ユーザー定義関数には内容が分かりやすい名前を付けよ!
■「坪数」関数の利用方法
■端数を切り捨てるには「Int」関数を利用せよ!
●売上金額に対するランクを表示する･･････251
■「ランク」関数の利用方法
■条件によって異なる値を求めるには「Select Case」で記述せよ!
●曜日によって異なる文字列を表示する関数を作る･･････254
■「備考」関数の利用方法
■曜日を文字列で表示させる場合には「Format」関数を利用せよ!
●残業と深夜残業を算出する関数を作る･･････256
■「残業時間」関数の利用方法
●冠汲株式会社･有限会社に変更させる関数を作る･･････258
■「略称変更」関数の利用方法
■冠汲変更するには文頭文末の文字列を探せ!
●色付きの文字をカウントする関数を作る･･････260
■「合格」関数の利用方法
■セルの内容を調べるには「Font」オブジェクトを利用せよ!
●文字色を指定してカウントする関数を作る･･････262
■「色別数」関数の利用方法
■色別にセルをカウントさせる際の注意点
●数値を五捨六入する関数を作る･･････264
■「五捨六入」関数の利用方法
■数値を五捨六入するには「Round」関数を利用せよ!
●ユーザー定義関数をアドインに登録する･･････266
■作成した関数を別のブックで利用できるようにするにはアドイン登録せよ!
■Excel97ではアドインの保存先に注意せよ!
コラムページ |
●ランダムな数値を出せるスロットマシンを作ろう!･･････52
●Excelで万年スケジュール表を作成しよう!･･････90
●リスト作成機能で効率的にデータを集計しよう!･･････124
●パレート分析で重要な問題点を把握しよう!･･････152
●手形の割引料がもっとも少ない手形の組み合わせを求める!･･････206
●三角関数を使用して円や渦巻きを描画しよう!･･････242
●Excelに特殊機能を付加できるアドインソフト･･････270
