| TOP PAGEへ | 会社案内 | データ館 | 達人募集 | 訂正情報 | 新刊案内 | 龍之介の部屋 | 読者の広場 | 譲ります |
アッと驚く達人の技
Excel関数
実践技＆ウラ技大全
[97/2000/2002対応]
|
|
Excelの持っているパワーを100%使いこなすには、「関数」を自在に使いこなす必要があります。しかし、関数はそれぞれ決められた書式で正確に記述しないと正しく機能しません。ある程度あっていれば近い答えを出してくれるというファジーさがないため、「知っている人しか使えない」という何とも融通のきかない一面があるのも事実です。 ※書籍中の例題データは「 データ館」の部屋で入手できます。 |
第１章 表示方法を加工する関数の実践･ウラ技 |
●部署名から課名だけを取り出す･･････16
■文字列を取り出すための引数の値が毎回変わる場合はFIND関数で調べろ!
■文字列の先頭から一部分を取り出すにはLEFT関数を使え!
●都道府県名を除いた住所を取り出す･･････18
■先頭の数文字分を除くデータを取り出すにはRIGHT関数を使え!
■複数条件式の1つを満たす場合にはIF関数の代わりにOR関数を使え!
●住所データの番地部分を半角文字で揃える･･････20
■英数カナ文字を半角で表示するにはASC関数を使え!
■文字列変換関数を使いこなせ!
●姓と名の間の空白文字を半角1文字に統一する･･････22
■全角と半角の文字が混在する場合はSUBSTITUTE関数で一方に揃えろ!
■空白文字をすべてカットするにはSUBSTITUTE関数を使え!
●「月日+連番」の形式の予約番号を作成する･･････24
■データを特定の形式で表示するときにはTEXT関数を使え!
●郵便番号に「−」を挿入する･･････26
■文字列を挿入するにはREPLACE関数で置換文字数を0にせよ!
■REPLACE関数とSUBSTITUTE関数の違いを理解せよ!
●カタカナをひらがなに変換して表示する･･････28
■カタカナをひらがなに変換するにはふりがなの設定を変更せよ!
●打率「0.325」を「3割2分5厘」と表示する･･････30
■数値を独自の単位で表示するときはTEXT関数を使え!
■セルの書式設定とTEXT関数の違いを理解せよ!
■「123.45」を「123＄45¢」と表示するには整数部と小数部を分けて変換せよ!
●請求書の金額を漢数字で表示する･･････32
■数値を漢数字で表示するにはNUMBERSTRING関数を使え!
■NUMBERSTRING関数で日付を漢数字にする場合は数値に変換せよ!
●「0」で割ったときのエラー値「#DIV/0!」を非表示にする･･････34
■「#DIV/0!」を非表示にするにはISERROR関数でエラーの有無を判定せよ!
■エラー値の意味を調べるには[！]マークを使え!
●伝票の空白行にある不要な「0」を消す･･････36
■不要な計算結果を表示させたくないときはデータの入力判定を行え!
■参照先が隣り合わせのときは配列数式で数式自体を合理化せよ!
●重複したデータの2番目以降に「★」印を付ける･･････38
■重複データの有無を調べるにはCOUNTIF関数を使え!
■重複データの数を調べるにはCOUNTIF関数を単独で使え!
●ゴルフのヤード表示をメートルに変換する･･････40
■異なる単位への換算にはCONVERT関数を使え!
■相互変換できる単位を調べるにはオンラインヘルプを利用せよ!
●絵文字を使って絵グラフを表現する･･････42
■絵グラフを作るときには一定単位ごとに表示させた文字列を絵文字にせよ!
■絵文字に対応する文字を調べるには「IMEパッド」を使え!
●1行おきに色を設定して表を縞模様にする･･････44
■奇数行は行数を2で割ったときの余りで見極めろ!
●生徒の氏名を順不同に並べ替えて座席表を作る･･････46
■乱数から重複しない番号を付けるときにはRANK関数を使え!
■縦方向に続く表を検索してデータを取り出すにはVLOOKUP関数を使え!
■RAND関数の値は変わりやすいので注意せよ!
第２章 日付･時刻を操作する関数の実践･ウラ技 |
●西暦と和暦を同時に表示する･･････52
■西暦と和暦を同時に表示する場合は2つの表示形式を並べて入力せよ!
■1900年より前の和暦は正しく表示されないことを忘れるな!
●水曜日だけを赤色で「定休日」と表示させる･･････54
■WEEKDAY関数は曜日判定の準備に使え!
●複数行の日付を揃えて表示させる･･････56
■日付を揃えるには十の位を半角スペースにして整えよ!
■西暦日付の桁を合わせるには「/0」をSUBSTITUTE関数で置き換えよ!
●月ごとの第3日曜日の日付を求める･･････58
■第○週目の日曜日の日付を求めるにはまず1日の曜日を算出せよ!
■日曜日以外の日付を求める場合は目的の曜日を計算に加えろ!
●生年月日と現在の日付で年齢を計算する･･････60
■経過期間を求める場合にはDATEDIF関数を使え!
■経過期間の単位は指数(パラメータ)で指定せよ!
●実労働日を求め給与を計算する･･････62
■平日の日数計算にはNETWORKDAYS関数を使え!
●稼働日数から平日の終了予定日を求める･･････64
■WORKDAY関数で終了日を求める場合には日数を1日分差し引け!
●購入日から月末5日前の支払日を求める･･････66
■月末から○日前の日付はEOMONTH関数を使って計算せよ!
●連続する平日の日付を一気に入力する･･････68
●土日にかかる支払日を金曜日に変更する･･････69
■土日の判定にはWEEKDAY関数の返り値を使え!
■土日の支払日を月曜に繰り下げるときは負の日数を差し引け!
●「午前○時○分○秒」の形式で時刻を表示する･･････71
■24時間表示を12時間表示にするときはHOUR関数で「時」を取り出せ!
■「午前○時○分」の桁を揃えるにはTEXT関数を利用せよ!
●時間を5分刻みになるように切り捨て表示する･･････73
■時間を特定の単位で切り捨てて表示するときにはFLOOR関数を使え!
■時間を特定の単位で切り上げて表示するときにはCEILING関数を利用せよ!
●24時間以上の時間を正しく表示する･･････75
■24時間以上の時間を表示させるにはセルの表示形式を変更せよ!
●○時間○分を数値に変換して給与を計算する･･････77
■時間を数値に変換するときには「1:0:0」という書式を使え!
●作業時間を通常･残業･深夜残業に分類して表示する･･････79
■時刻によって分類するにはMAX関数で時間を選択させよ!
■通常の勤務時間は残業時間を求めた後に計算せよ!
■遅刻や早退時間を引いて勤務時間を計算するには
●1月、2月、3月…を睦月、如月、弥生…にする･･････82
■該当するデータを表示させるにはデータベース関数を利用せよ!
■検索用のデータを用意したくないときは数式自体に入力せよ!
●日付をまたぐ作業時間を計算する･･････84
■日付をまたぐ時間は24時間プラスして計算せよ!
●時間の差異が負の場合に「−」付きで表示する･･････85
■「-」付きの時間の表示にはTEXT関数を使え!
第３章 データ集計を行う関数の実践･ウラ技 |
●指定した範囲の日付のデータを集計する･･････88
■SUMIF関数の検索条件に一定範囲を指定するには不等号を使え!
■指定期間の合計を求めるには開始以降の合計から終了日以降の合計を差し引け!
●毎日のデータを週ごとに集計する･･････90
■日付がその月の第何週目かを求めるには月の初日の曜日を調べよ!
■データを週ごとに合計するにはSUMIF関数を使え!
■週末(月末)を判定するには第何週かを表す数値の変化を調べよ!
●複数の条件を満たすデータを集計する･･････93
■DSUM関数の集計条件は表形式で設定せよ!
■DSUM関数を使うには表の形に注意せよ!
●複数条件を満たすデータ集計表を作成する･･････95
■条件を満たすデータを合計するには配列数式を使え!
■AND条件を指定するには「＊」演算子を使え!
●オートフィルタの抽出結果を使って集計する･･････97
■オートフィルタの結果を集計するにはSUBTOTAL関数を使え!
■SUBTOTAL関数で求める統計値は1番目の引数で指定せよ!
●小計行を含む表の合計と平均を求める･･････99
■集計範囲内の統計値を除いて集計するにはSUBTOTAL関数を使え!
●合計するデータの範囲を可変にする･･････101
■集計範囲を任意に変更可能にするにはOFFSET関数を使え!
●データを3行ずつ集計する･･････103
■データをn行ずつ集計するには集計範囲を相対参照で指定せよ!
■n行ごとのデータを取り出すには行番号をnで割った余りを利用せよ!
■6行ずつ集計するには集計範囲と行番号を割る値を変更せよ!
■表示位置が異なる表でn行ごとにデータを取り出すには余りの値を変更せよ!
●2行おきのデータを集計する･･････106
■データ範囲から条件に合うデータを合計するには配列数式を使え!
●同じ内容のセルの数を集計する･･････108
■内容が同じセルの数を求めるにはCOUNTIF関数を使え!
●同じデータの連続回数を数える･･････110
■データの連続回数を数えるには隣り合う2つのセルの内容を判定せよ!
●セル内に入力されたワークシート名を利用して集計する･･････112
■セルに入力された名前のワークシートを参照するにはINDIRECT関数を使え!
■異なるワークシートのセルを参照するにはセル番地にシート名を付加せよ!
●2つの表を1つに連結する･･････114
■2つの表を結合するにはVLOOKUP関数を使え!
■一致するデータがない場合を判定するにはISERROR関数を使え!
●2つの表を集計して1つにまとめる･･････116
■2つの表を1つに集計するにはSUMIF関数を使え!
■集計する2つの表の大きさが違う場合は集計範囲に注意せよ!
●行･列見出しからデータを取り出す･･････118
■行･列見出しの条件を満たすデータを取り出すにはMATCH関数とINDEX関数を使え!
■検索値を超えない最大値の位置を求めるにはMATCH関数の引数を変更せよ!
●金種表で紙幣･硬貨の枚数を集計する･･････120
■必要な紙幣･硬貨の枚数を求めるには紙幣･硬貨の金額で割り算した商を求めよ!
第４章 データ分析を行う関数の実践･ウラ技 |
●「売上金額」と「売上件数」の2つの条件から順位を求める･･････124
■2つの条件を元に順位を求めるには順位判定用の数値データを作成せよ!
●支店ごとに順位を求める･･････126
■グループごとに順位を求めるにはグループの条件をAND条件で指定せよ!
■数値の順位を求めるには自分より大きい数値の個数を数えよ!
●売上金額の多い順に担当者を抽出する･･････128
■重複しない順位を求めるにはRANK関数とCOUNTIF関数を組み合わせて使用せよ!
■取り出す順位を自動的に指定するにはROW関数を使用せよ!
●アンケートの回答数が多い上位3つの回答番号を調べる･･････130
■出現回数が2番目に多いデータを取り出すには配列数式を使え!
●来場者リストで重複している会社名を検索する･･････132
■重複しているデータを見つけるには同じデータの数を数えよ!
■条件を満たすデータに○印を表示するにはIF関数を使え!
●2つの表から不一致の会社名を検索する･･････134
■不一致のデータを調べるには一致するデータの数を求めよ!
●「0」を除いた売上個数の最小値を求める･･････136
■0を除く最小値はSMALL関数で「0のデータ数+1」番目に小さい値を求めよ!
●複数の条件を満たすデータ数を求める･･････138
■DCOUNTA関数の集計条件は表形式で設定せよ!
■DCOUNTA関数を使うには表の形に注意せよ!
●過去5年間の伸び率の平均を求める･･････140
■相乗平均を求めるにはGEOMEAN関数を使え!
■平均伸び率を求めるには相乗平均を使用せよ!
●「0」を除いた売上台数の平均値を求める･･････142
■「0」を除いた平均値を求めるにはCOUNTIF関数で「0」以外のデータ数を算出せよ!
●売上金額の上位30%の平均値を求める･･････144
■上位30%の平均値を計算するには上位30%にあたるセル範囲を求めよ!
■セル範囲の文字列を関数の引数に指定するにはINDIRECT関数を使え!
●売上金額の多い順に上位20%を判定する･･････146
■データの上位20%を求めるにはPERCENTRANK関数を使え!
■PERCENTRANK関数を使う際は重複データに注意せよ!
●売上台数の推移を移動平均で分析する･･････148
■移動平均を求めるにはn個ずつの平均値を計算せよ!
■変化の大きいデータの傾向をつかむには移動平均を利用せよ!
●商品の売上額をABC分析する･･････150
■ABC分析を行うにはIF関数をネスト(入れ子)で使え!
■重点商品を調べるにはABC分析を利用せよ!
●年齢層ごとに購入者数の度数分布を求める･･････152
■度数分布表を作成するには集計区間を区切るデータを入力せよ!
■FREQUENCY関数を入力するにはデータを表示するセル範囲を指定せよ!
●新店舗の売上金額を回帰分析で予測する･･････154
■データを回帰分析するにはFORECAST関数を使え!
■回帰分析を行うにはデータの相関関係に注意せよ!
■回帰分析の標準誤差を求めるにはSTEYX関数を使え!
●模擬試験の標準偏差と偏差値を求める･･････156
■標準偏差を求めるにはSTDEVP関数を使え!
■偏差値を求めるには標準偏差と平均値を計算せよ!
●集計表から目的のデータを抽出する･･････158
■ピボットテーブルからデータを抽出するにはGETPIVOTDATA関数を使え!
■抽出条件を指定するには見出しを半角スペースで連結して指定せよ!
第５章 金融･財務を管理する関数の実践･ウラ技 |
●株式の売買損益を年利に換算する･･････162
■株式投資の利回り計算にはINTRATE関数を使え!
●割引金融債の利回りを一発で求める･･････164
■割引金融債の利回りを求めるにはYIELDDISC関数を使え!
●社債の利回りを一発で求める･･････166
■社債の利回りを求めるにはYIELD関数を使え!
●手形割引料を一発で求める･･････168
■手形の割引料を求めるにはACCRINTM関数の引数[受領日]に「1」を加えろ!
●有価証券の未収利息を求める･･････170
■決算日直前の利払日を求めるにはCOUPPCD関数を使え!
■有価証券の未収利息を求めるにはACCRINTM関数を使え!
●減価償却費を求める･･････174
■減価償却のための月割り計算にはYEAR関数とMONTH関数を使え!
■DB関数の引数にはMOD関数やROUNDUP関数を使え!
■定額法の減価償却費を求めるにはSLN関数に当期使用月数の割合を掛けろ!
●黒字と赤字の分岐点を求める･･････178
■損益分岐点を求めるにはSLOPE関数とINTERCEPT関数を使え!
●品切れを防ぐ発注点を求める･･････180
■安全在庫量を求めるにはNORMSINV関数を使え!
■発注点を求めるにはリードタイムの平均販売量に安全在庫量を加えろ!
●預金の元利合計を求める･･････183
■預金の複利計算を行うにはFV関数を使え!
■利息計算するには利率と期間の時間的単位を合わせろ!
●積立金の満期額を一発で求める･･････185
■積立金の満期額を求めるにはFV関数を使え!
■積立金の目標満期額から積立額を求めるにはPMT関数を使え!
●借入金の支払額を求める･･････187
■事業資金の借入利息を求めるにはISPMT関数を使え!
■事業資金の借入の元金を求めるには借入額を支払回数で割れ!
●ローン支払額の内訳を求める･･････190
■元利均等返済の支払額を求めるにはPMT関数を使え!
●ボーナス併用の借入額と支払額を求める･･････192
■ボーナス払いの計算をするには時間的単位を半年にせよ!
■ボーナス時の支払額を求めるにはPMT関数を使え!
●10年固定型ローンの支払額を求める･･････196
■固定型が終了する際の借入残高はCUMPRINC関数で求めよ!
●住宅ローンの繰上返済の支払額を求める･･････199
■期間短縮型の繰上返済額を求めるにはCUMPRINC関数を使え!
■繰上返済の節約利息を求めるにはCUMIPMT関数を使え!
■支払額圧縮型の計算ではCUMPRINC関数を使って支払い済み元金を求めろ!
●投資の収益性を一発で求める･･････203
■投資の収益性を求めるにはNPV関数を使え!
■投資額が大きく異なる投資プランを比較するにはIRR関数を使え!
第６章 数学的な計算を行う関数の実践･ウラ技 |
●三角関数を使用してビルの高さを求める･･････208
■直角三角形の高さを求めるにはTAN関数を使え!
●緯度と経度を使って目的地への方角を調べる･･････210
■緯度と経度から方位角を求めるには三角関数を使用してβ1の値を求めよ!
■β1の値が真南を基準にしているときには補正して方向角を求めろ!
●指数関数を使用してドレミの音の周波数を求める･･････212
■数値のべき乗を求めるにはPOWER関数を使用せよ!
■関数を使わないべき乗の計算には「^」を使え!
●性能や価格を単純な数値で比較する･･････214
■割合を求めるときは各数値を最大公約数で割り算せよ!
●特定の単位で端数を切り捨てて料金を求める･･････216
■数値を特定の単位で切り捨てるにはFLOOR関数を使え!
●電波が到達可能な距離を平方根から求める･･････218
■SQRT関数の計算を行う前には引数を判定する処理を挿入せよ!
●余りを求めて閏年かどうかを判断する･･････220
■4年で割り切れる年でも閏年とならない場合があることに注意せよ!
■周期性のある数値の判定にはMOD関数を使え!
●モンテカルロ法で乱数の統計から円周率を実証する･･････222
■マイナスの乱数が必要なときは作成した乱数を差し引け!
■整数の乱数を作成するときはINT関数を数式に組み込め!
■座標までの距離を調べるには「三平方の定理」を応用せよ!
■円周率は円と正方形の面積の割合から計算せよ!
●数値の幅を３段階に分けてランク付けする･･････226
■数値のランク分けにはMATCH関数とINDEX関数を組み合わせて利用せよ!
●順列組み合わせの数からロトくじの当選確率を求める･･････228
■何通りの組み合わせができるかを調べるにはCOMBIN関数を使え!
■順列を求めるにはPERMUT関数を使え!
●惑星の半径から表面積を求める･･････230
■高い精度が必要な円周率の計算にはPI関数を使え!
●10進数のRGB値を16進数で表示する･･････232
■10進数のRGB値を表示するには各色に256を掛けて桁を繰り上げろ!
■10進数の値を16進数に変換するにはDEC2HEX関数を使え!
第７章 関数を自作する実践･ウラ技 |
●独自の計算式で関数を自作するには･･････236
●売上金から消費税を計算する関数を作る･･････239
■ユーザー定義関数には内容が分かりやすい名前を付けよ!
■消費税関数の利用方法
■消費税額を切り捨てるにはINT関数を利用せよ!
●購入金額によって割引率を変更する関数を作る･･････243
■優待金額関数の利用方法
■条件によって異なる値を求めるには「Select Case」で記述せよ!
●記念日を返す関数を作る･･････246
■記念日関数の利用方法
■記念日関数を毎年利用できるようにするにはTEXT関数を利用せよ!
●残業と深夜残業を算出する関数を作る･･････248
■残業時間関数の利用法
●冠汲株式会社･有限会社に変更させる関数を作る･･････250
■表示変更関数の利用方法
■冠汲変更するには文頭文末の文字列を探せ!
●色付きの文字をカウントする関数を作る･･････252
■色数関数の利用方法
■セルの内容を調べるには「Font」オブジェクトを利用せよ!
●ブック名と保存場所を表示する関数を作る･･････254
■保存先関数の利用方法
■ブックの情報を表示させるには「ActiveWorkbook.FullName」を記述せよ!
●入力した年の干支を返す関数を作る･･････256
■干支表示関数の利用方法
■年の干支を求めるには12(干支数)で割った余りを利用せよ!
●ユーザー定義関数をアドインに登録する･･････258
■作成した関数を別のブックで利用できるようにするにはアドイン登録せよ!
■Excel97ではアドインの保存先に注意せよ!
コラムページ |
●占い結果が変わるおみくじを作る･･････50
●Excelで万年カレンダーを作成しよう!･･････86
●ブック間でデータを集計する方法･･････122
●データを分析するにはグラフを活用せよ!･･････160
●近い未来の利益を予測する!･･････206
●三角関数のグラフで曲線を描いてみよう!･･････234
●1900年以前の年号を返す関数を作る･･････262
