ステップ図解 Excel97関数ウラ技テクニック
|
|
Excel97の関数の驚異のパワーを引き出すには、複数の関数を組み合わせたり、数式や配列を組み合わせる必要があります。しかし、どの解説書もマニュアルチックなリファレンスものばかりで、物足りないと思った方も多いはず。
そこで本書ではExcel97の関数を本当に仕事に活用するための徹底的に役に立つ選りすぐったプロのウラ技ネタを集めてみました。 ※書籍中の例題データは「データ館」の部屋で入手できます。 |
●エラー値の表示を回避する･･････14 ■「#DIV/0!」のエラー表示を回避する仕組み■他のエラー値が表示されたときの対処法■不要なゼロ表示を消去するウラ技
●部署名から課名だけを取り出す･･････16 ■部署名から課名だけを取り出すメカニズム
●PHS番号を11桁の新番号にする･･････18
■PHS番号を現行の10桁から11桁の新番号に切り替えるメカニズム■携帯電話の番号を新番号に切り替えるには
●ボタン操作で運勢を占う･･････20
■おみくじシートのメカニズム
●英字氏名データの体裁を整える･･････24
■複数の空白文字が混在した英字氏名の体裁を整える仕組み
●名前からふりがなを半角で取り出す･･････26
■取り出したふりがなを半角カタカナで表示するメカニズム■元のふりがなを訂正するには
●領収書の金額を漢数字で表示する･･････28
■漢数字を「金××円也」形式で表示するメカニズム■まだある!漢数字で表示するウラ技
●単位換算して在庫数を問い合わせる･･････30
■単位換算の仕組み
●ドルとセントの金種表を作る･･････32
■数値から小数部のみを取り出すメカニズム
●単価に「円」と「銭」を付けて表示する･･････34
■単価に「円」と「銭」を付けて表示するメカニズム■数値をセルからはみだして表示させるウラ技
●文字列「123人」を数値に変換する･･････36
■数値付き文字列を数値に変換するメカニズム■まだある!「123人」などの文字列を数値に変換するウラ技
●予算に達しない達成率を赤で表示する･･････38
■値により表示色を変えるメカニズム
●日付を分解して文章に使う･･････42 ■日付を分解して文章に使う仕組み■「ただいま○時○分です」と表示するウラ技■シリアル値って何?
●西暦日付を和暦「年」だけで表示する･･････44 ■西暦を和暦に変換して表示する仕組み■日付の表示形式を設定するコツ
●土日の曜日を色付きで表示する･･････46
■曜日を色付きで表示させるメカニズム■まだある曜日を漢字で表示するウラ技
●日付の桁数を揃えて表示する･･････48
■日付を桁揃えで表示するメカニズム
●数値データを日付に変換する･･････50
■8桁の数字を日付に変換するメカニズム
●出社時刻を15分単位で切り上げる･･････52
■時間を15分単位で切り上げるメカニズム
●残業時間を求める･･････54
■残業時間を求める仕組み■24時間や60分を超える時間を表示するコツ
●勤務時間から給与を計算する･･････56
■「時間給×勤務時間」で支給額を求めるコツ■時間を分や秒だけで表示するウラ技
●期間内の実労働日数を調べる･･････58
■NETWORKDAYS関数の活用法■除外する日を直接指定する方法■20日後の納品予定日を求めるウラ技
●雑誌の発売日を調べる･･････60
■日付がその月の第何週であるかを調べる仕組み
●勤続年月を「○年○ヶ月」と表示する･･････62
■勤続年月を「○年○ヶ月」で表示する仕組み■現在の年齢を求めるウラ技■DATEDIF関数が見あたらない?
●締め日により入金日を切り替える･･････64
■締め日により入金予定日を切り替えるメカニズム■半年後の月初日を求めるウラ技
●月ごとにデータを集計する･･････68 ■月ごとに集計するメカニズム■まだある月ごとに集計するウラ技
●複数条件を満たすデータを集計する･･････70 ■複数の条件で集計する仕組みはこれだ!■データベース関数の検索条件を極める
●四半期の合計を集計する･･････72
■SUBTOTAL関数を使うメリット
●条件範囲に合うデータ数を求める･･････74
■条件範囲に合うデータ数を求める仕組みはこれだ!■検索条件を極めるコツ
●複雑な条件を満たすデータを数える･･････76
■DCOUNTA関数の用途■検索条件を指定するコツ
●同一データの連続回数を数える･･････78
■データの連続回数を数えるメカニズム
●上位30%のデータを平均する･･････80
■上位30%の平均値を求めるメカニズム
●行･列見出しからデータを抽出する･･････82
■行･列見出しからデータを抽出するメカニズム
●売上ベスト3を抽出する･･････84
■売上ベスト3を表示するメカニズム■ブービー賞を表示するワザ
●2つの表を1つにまとめる･･････86
■2つの表を1つにまとめる仕組みはこれだ!■別のブックの表をまとめるには■「#N/A」を「データなし」と表示するワザ
●同一データを素早く数える･･････88
■範囲内の同一データを数えるメカニズム■配列を使わずに同一データを数えるには
●複数シートを素早く合計する･･････90
■複数シートを合計するメカニズム■マウス操作のみで合計するワザ
●セル位置からデータを抽出する･･････92
■データを抽出するメカニズム
●抽出したデータのみを集計する･･････94
■SUBTOTAL関数を利用する理由
●追加データを自動的に集計する･･････96
■追加データを自動集計するメカニズム■他の方法で追加データを自動集計させるには
●2つの条件から順位を求める･･････100 ■2つの条件から順位を求めるメカニズム
●商品売上をABC分析する･･････102 ■これがABC分析だ!■ランクを表示する仕組みはこれだ!
●相対的な順位を百分率で表示する･･････104
■相対順位を百分率で表示する仕組みはこれだ!■合否ラインを求めるワザ
●移動平均で分析する･･････106
■これが移動平均だ!■n個ごとの平均を求めるメカニズム
●件数の最も多いデータを表示する･･････108
■MODE関数の用途■データの件数を表示するワザ
●標準偏差から偏差値を計算する･･････110
■偏差値を求める仕組みはこれだ!■母集団の標準偏差を推定するには
●検査データの度数分布を求める･･････112
■度数分布を求める仕組み■まだある度数分布を求めるワザ■度数分布を横方向に表示するワザ
●データの関連性を分析する･･････114
■CORREL関数の活用法■視覚的に相関関係を見極めるワザ
●回帰分析で売上を予測する･･････116
■FORECAST関数の用途■回帰直線方程式を求めるワザ
●2つの要素から値を予想する･･････118
■2つの条件から数値を予測するメカニズム■まだある回帰分析で数値を予測する関数
●売上伸び率の平均を計算する･･････120
■相乗平均を求める仕組みはこれだ!■相加平均の注意点
●例外データを除いて平均する･･････122
■例外データを除いた平均を求める仕組み■除外されるデータ数はいくつ?
●集計表から目的のデータを抽出する･･････124
■GETPIVOTDATA関数とは■引数を指定するコツ
●金種計算表を作成する･･････126
■金額から紙幣と硬貨の枚数を求めるメカニズム
●そもそも配列って何？･･････130 ■配列のメカニズム■配列のメリット■配列数式を入力する方法■配列定数って何？
●伝票の空白行の不要な「0」を消す･･････132 ■空白行に表示される不要な「0」を消す仕組み■列どうしで掛け算するときの注意点
●外貨の両替表を作成する･･････134
■作成した両替表の仕組み■構造の異なる配列どうしで演算するメカニズム
●総当たり戦の対戦表を瞬時に作成する･･････136
■総当たり戦の対戦表を作成するメカニズム■同一チームの対戦枠に斜線を引くには
●クイズの全問正解者の数を調べる･･････138
■応募者全体から全問正解者数を求める仕組み■配列どうしを結合するとこうなる!■設問ごとの正解者数を調べるには
●複数の条件からデータを絞り込む･･････140
■複数の条件からデータを絞り込む仕組み■配列で最低価格を求めるには
●勝敗の結果を「○△●」で表す･･････142
■勝敗結果をまとめて「○△●」と表示する仕組み■勝敗結果から通算成績を表示するには■勝敗結果から勝率を算出するには
●得点表から100点の有無を調べる･･････144
■検索範囲内から指定した値の有無を調べる仕組み■指定した得点の数を調べるには■平均点を素早く調べる方法
●1行おきの合計を求める･･････146
■1行ごとの合計を求めるメカニズム
●アンケート結果を集計する･･････148
■FREQUENCY関数って何?■集計する範囲を指定するコツ
●行列の行と列を入れ替えて表示する･･････150
■TRANSPOSE関数って何?
●手形割引料を簡単に求める･･････154 ■YEARFRAC関数の用途■YEARFRAC関数を活用するコツ
●割引金融債の利回りを一発で求める･･････156 ■YIELDDISCの用途
●社債の利回りを一発で求める･･････158
■YIELD関数の用途
●固定費と変動費を一発で分ける･･････160
■LINEST関数の用途■費用分解するメカニズム■費用分解をグラフにするウラ技■気温からビール販売量を予測するウラ技
●減価償却の月割り計算をする･･････164
■月割り計算のメカニズム■定額法による月割り計算をするには
●半年決算の減価償却をする･･････166
■半年決算の減価償却のコツ■定額法と定率法による減価償却の違いを知る
●品切れ防止在庫数を求める･･････168
■SQRT関数の用途
●ローン支払額の内訳を求める･･････170
■PMT関数とPPMT関数の用途■ローンの返済方法
●ボーナス併用の借入額を求める･･････172
■ボーナス併用のローン計算の仕組み
●繰上返済で利息を激減させる･･････174
■CUMPRINC関数とCUMIPMT関数の用途■住宅ローンの繰上返済とは
●繰上返済で毎月支払額を減らす･･････176
■返済額圧縮型での計算の仕組み
●投資の利益額を一発で求める･･････178
■NPV関数の用途■1期目から収入がある場合には■設備投資計算の用語を知る
●投資の利益率を一発で求める･･････180
■IRR関数の用途■「#NUM!」が表示されるときには
●投資の安全性を分析する･･････182
■投資の安全性を分析するメカニズム■投資の回収状況をグラフにするウラ技
●独自の関数を作成する･･････186 ■ユーザー定義関数を他のブックで使用するには
●消費税を求める関数を作る･･････188 ■消費税関数の利用方法■消費税関数の記述のコツ■消費税関数の改造方法■関数名を付けるコツ■VBA関数記述のコツ
●得意先によって販売単価を切り替える･･････192
■販売単価関数の利用方法■販売単価関数の記述内容■「Select〜Case」構文と「If〜Else」構文の使い分けのコツ
●有効期限を自動計算する･･････194
■有効期限関数の利用方法■有効期限関数の記述のコツ■自作の日付関数を使用するには
●株式会社を(株)に置換する･･････196
■社名縮小関数の利用方法■社名縮小関数の記述のコツ
●ファイル名を自動取得する関数を作る･･････198
■ファイル名関数の利用方法■ファイル名関数の記述のコツ■ワークシート情報を取得するには
●ファイルの日付情報を取得する･･････200
■更新日付関数の利用方法■更新日付関数の記述のコツ■ファイルサイズを取得するには
●残業時間を自動計算する･･････202
■残業時間関数の利用方法■残業時間関数の記述のコツ
●キャラクタで棒グラフを表示する･･････204
■キャラクタグラフ関数の利用方法■引数を省略可能にするウラ技■VBAで利用可能なワークシート関数
●郵便番号から住所データを入力する･･････206
■住所取得関数の利用方法■住所取得関数の記述のコツ
●自作関数をアドインにする･･････208
■作成したアドインを登録するには■通常のブックを一時的にアドインファイルにするウラ技
●表示制御に関するQ&A･･････40
●日付･時刻に関するQ&A･･････66
●データ集計に関するQ&A･･････98
●データ分析にまつわるQ&A･･････128
●配列に関するQ&A･･････152
●財務管理についてのQ&A･･････184
●自作関数に関するQ&A･･････212
前に戻る