2002/2003/2007対応 Excelデータの集計・分析辞典

概 要

Excelでデータを効率よく整理し集計、分析するノウハウからピボットテーブルまでを紹介したExcel辞典です。データ入力の基本から、日付や時刻の自動入力や期間計算のための数式、入力内容の制限方法、エラーメッセージの表示、重複データの防止、複数のセルの入力された文字列の連結、任意のデータの検索、置換、書式設定、外部テキストの取り込み、テーブル作成、データの並び替え、レコードの抽出、複数のワークシートをまとめて編集する方法、テーブルの集計、さらに、集計や分析を行なうために必要なデータベース関数やピボットテーブルデータを利用した集計まで、豊富な事例を使って解説しています。サンプルのダウンロードサービス付き。

著者 長村知明
価格 本体1980円(税別)
ISBN 978-4-7980-2127-0
発売日 2008/11/22
判型 A5
色数 2色
ページ数 400
CD/DVD
対象読者 初級
シリーズ Office Dictionary Series
表紙イメージ
購入 アマゾンで購入する
楽天で購入する

※リンク先によっては、販売ページが用意されていないことがあります。あらかじめご了承ください。

新しいウィンドウで開く 書籍購入のご案内

サポート

サポート情報は以下からご参照下さい。

サポート情報へのリンク

目次

第1部 データの整理編

第1章 Excelデータベースの基本

1-1 データベースの作成

001 データ入力の基本

002 同じ列にあるデータを簡単に入力したい

事例1 オートコンプリート機能を利用して、同じ列にあるデータから候補を表示する

事例2 リストから選択して、同じ列にあるデータから候補を表示する

003 郵便番号から住所を自動入力したい

事例1 住所変換ウィザードで郵便番号に対応する住所を入力する

004 複数のセルにまとめて同じ数式を入力したい

事例1 複数のセルに累計を求める数式をまとめて入力する

005 簡単に連番を入力したい

事例1 すでに入力されているレコードに連番を入力する

1-2 数式の作成

006 日付と時刻を自動入力したい

事例1 NOW関数を使用して、今日の日付と時刻を自動入力する

事例2 TODAY関数を使用して、今日の日付を自動入力する

007 月末の日付を表示したい

事例1 関数を使用して、請求日に月末の日付を自動入力する

008 期間を計算したい

事例1 DATEDIF関数を使用して、入社日から今日までの期間を計算する

009 商品番号を入力すると商品名や単価が表示されるようにしたい

事例1 VLOOKUP関数を使用して、商品番号に対応する商品名や単価を表示する

010 数式を変更できないようにしたい

事例1 数式の入力されているセルとワークシートを保護する

1-3 表示

011 フィールド名を常に表示したい

事例1 ウィンドウ枠を固定して、スクロールしてもフィールド名が表示されるようにする

012 ふりがなを表示したい

事例1 入力されている漢字にふりがなを表示する

事例2 ふりがなの文字の種類を変更する

1-4 入力規則

013 入力できる内容を制限したい

事例1 指定した範囲以外の数値や文字数、日付などが入力できないようにする

014 規則に反するデータが入力されたときやセルを選択したときに

メッセージを表示したい

事例1 入力規則に違反するデータが入力されたときに任意のメッセージを表示する

事例2 データを入力しようとしたときに、任意のメッセージを表示する

015 入力するデータの種類に合わせて日本語の入力モードを変更したい

事例1 入力規則を利用して、日本語入力システムの入力モードを変更する

016 同じ列に重複するデータがないようにしたい

事例1 同じ列にすでに入力したデータを入力できないようにする

017 選択リストに表示した内容から入力したい

事例1 同じワークシートに入力されているデータをドロップダウンリストで入力する

事例2 入力されていないデータをドロップダウンリストで入力する

018 追加した項目を自動的に選択リストにも表示したい

事例1 ドロップダウンリストに表示するデータのセル範囲を拡張する

019 ほかのシートにある部署名をリストから入力したい

事例1 ほかのワークシートに入力されているデータを基にドロップダウンリストを作成する

020 「分類」によって、表示されるドロップダウンリストを変更したい

事例1 ドロップダウンリストに表示するデータを条件に合わせて変更する

第2章 データの加工

2-1 データの編集

021 データ編集の基本

022 計算結果を残したい

事例1 コピーと貼り付けを利用して、売上金額の計算結果を値にする

023 複数のセルに入力された文字列を連結したい

事例1 CONCATENATE関数を使用して、氏と名を1つのセルに表示する

024 任意のデータを検索したい

事例1 検索を利用して、任意の文字列を含むセルを探す

025 全角文字と半角文字を区別して検索したい

事例1 検索を利用して、全角文字で入力された文字列を探す

026 検索したデータを別のデータに置き換えたい

事例1 置換を利用して、任意のデータをほかのデータに置き換える

027 任意の文字列を含むセルの書式をまとめて変更したい

事例1 置換を利用して、セット品の書式をまとめて変更する

028 セル内の改行を解除したい

事例1 置換を利用して、セル内の改行を解除する

2-2 配置の変更

029 1つのセルに入力されているデータを複数列に分割したい

事例1 1つのセルに入力されている複数フィールドのデータを分割する

030 氏名を氏と名に分割したい

事例1 空白で区切られた氏と名を分割する

031 セルの一部のデータをほかのセルにコピーしたい

事例1 氏名フィールドのデータから氏だけをほかのセルにコピーする

032 表の縦横を入れ替えたい

事例1 縦横を入れ替えて表をコピーする

2-3 データの変換

033 文字の半角と全角を統一したい

事例1 関数を使用して、住所1フィールドを全角文字に統一する

034 カタカナをひらがなにしたい

事例1 フリガナフィールドのカタカナをひらがなに変換する

035 カタカナは全角に、英数字は半角にしたい

事例1 住所を全角カタカナと半角英数字に統一する

036 余計な空白文字をひとつにまとめたい

事例1 複数文字入力された空白文字を一文字にする

037 全角スペースをすべて削除したい

事例1 全角の空白文字をすべて削除する

038 入力されている数値を千単位で表示したい

事例1 表示形式を利用して、数値を千単位で表示する

039 住所から都道府県名を取り出したい

事例1 表示する文字数を関数で指定して、住所から都道府県を表示する

040 指定した文字まで文字列を取り出したい

事例1 「)」から「-」で区切られた市内局番を表示する

2-4 条件付き書式

041 重複するデータに書式を設定したい

事例1 重複する会社名の書式を設定する

042 任意のデータにアイコンを表示したい

事例1 在庫数に応じて異なるアイコンを表示する

043 条件に一致したレコードに書式を設定したい

事例1 条件付き書式を利用して、在庫数が10個以下のレコードのセルの色を変更する

044 条件付き書式が設定されたセルを検索したい

事例1 検索と置換を利用して、条件付き書式が設定されたセルを範囲選択する

2-5 外部データの活用

045 テキストファイルを取り込みたい

事例1 [テキストファイルウィザード]を利用して、CSVファイルを取り込む

046 Accessのテーブルやクエリを取り込みたい

事例1 外部データの取り込みを利用して、AccessのテーブルをExcelで使用する

047 ほかのブックのデータを利用したい

事例1 数式を利用して、ほかのブックのデータを表示する

048 ほかのブックとのリンク元を変更したい

事例1 リンクの編集を利用して、ほかのブックとのリンク元を変更する

事例2 ほかのブックのリンクを解除する

第3章 データの並べ替えと抽出

3-1 テーブルの作成

049 テーブル作成の基本

3-2 並べ替え

050 単一条件でレコードを並べ替えたい

事例1 ふりがなを条件にして、レコードを50音順に並べ替える

051 複数条件でレコードを並べ替えたい

事例1 [並べ替え]ダイアログボックスを利用して、3つ以上の条件でレコードを昇順

または降順に並べ替える

052 一部のレコードだけを並べ替えたい

事例1 1月に受注したレコードだけを並べ替える

053 オリジナルの順序に従って並べ替えたい

事例1 ユーザー設定リストを作成して、並べ替えの順序を指定する

054 英字の大文字小文字を区別したり、漢字を漢字コードで並べ替えたい

事例1 並べ替えのオプションを利用して、英字の大文字小文字を区別して並べ替える

事例2 並べ替えのオプションを利用して、漢字のコード順に並べ替える

055 数字と数値を正しく並べ替えたい

事例1 文字列となっている数値も数値として並べ替える

056 フォントの色をキーにしてレコードを並べ替えたい

事例1 色で並べ替えを利用して、フォントの色が赤色のレコードを上に移動する

事例2 色で並べ替えを利用して、フォントの色が「赤」「黄」「緑」の順に並べ替える

3-3 オートフィルタによる抽出

057 任意のデータと一致するレコードを抽出したい

事例1 オートフィルタで、商品コードを選択して抽出する

058 複数のフィールドに抽出条件を設定したい 187

事例1 オートフィルタを利用して、指定した部署と評価が一致するレコードを表示する

059 すべての抽出条件を解除したい

事例1 フィルタをまとめて解除して、すべてのレコードを表示する

060 上位のレコードを5位まで抽出したい

事例1 オートフィルタのトップテンを利用して、売上金額が多いレコードを5件表示する

061 空白セルのレコードを抽出したい

事例1 オートフィルタを利用して、出荷日が入力されていないレコードを表示する

062 任意の期間を含むレコードを抽出したい

事例1 オートフィルタを利用して、3月20日から30日に受注したレコードを表示する

063 任意のフィールドで平均より上のレコードを抽出したい

事例1 オートフィルタを利用して、売上金額が平均より大きい数値のレコードを表示する

064 任意のフォントの色が設定されているレコードを抽出したい

事例1 オートフィルタを利用して、在庫フィールドのフォントの色が赤色のレコード を表示する

065 任意の文字列を含むレコードを抽出したい

事例1 オートフィルタを利用して、商品番号に「PEN」を含む商品を表示する

事例2 ワイルドカードを利用して、抽出条件に文字数を指定する

事例3 商品名に「*」を含むレコードを抽出する

066 抽出結果のレコードを削除したい

事例1 オートフィルタで抽出したレコードを削除する

3-4 フィルタオプションの設定による抽出

067 抽出条件を詳細に設定したい

事例1 フィルタオプションの設定を利用して、詳細な条件に一致するレコードを抽出する

068 検索条件に数式を使用したい

事例1 フィルタオプションの設定を利用して、在庫数が全体の平均以上のレコードを表示する

069 任意の範囲内の数値を含むレコードを抽出したい

事例1 フィルタオプションの設定を利用して、在庫数が300~500個のレコードを表示する

070 抽出結果の表示位置を指定したい

事例1 フィルタオプションの設定を利用して、抽出先を指定する

071 重複するレコードを削除したい

事例1 重複の削除を利用して、会社名が重複するレコードを削除する

第2部 データの集計と分析編

第4章 データの集計

4-1 データの集計

072 データ集計の基本

4-2 ワークシート間の集計

073 複数のワークシートをまとめて編集したい

事例1 複数のワークシートに、新しい商品分類をまとめて追加する

074 複数のワークシートのデータを集計したい(1)

事例1 同じレイアウトで構成された各支店の売上を集計する

075 複数のワークシートのデータを集計したい(2)

事例1 統合を利用して、レイアウトの異なる各支店の売上を集計する

076 データの変更を統合した表に反映させたい

事例1 統合を利用して、参照元データの変更に対応する集計表を作成する

4-3 アウトラインの利用

077 小計行と総計行を追加したい

事例1 小計を利用して、顧客名ごとに数量と金額を集計する

事例2 小計が設定されている集計表に、受注日ごとの数量と金額の集計を追加する

078 集計表の明細行を非表示にしたい

事例1 アウトラインを利用して、詳細データを非表示する

079 集計表の小計で改ページしながら印刷したい

事例1 集計の設定を利用して、小計のグループごとに改ページする

080 集計表の小計行と総計行をほかのワークシートにコピーしたい

事例1 可視セルを利用して、小計行と総計行をほかのワークシートにコピーする

081 任意の行や列の表示や非表示を簡単に切り替えたい

事例1 グループ化を利用して、詳細データを一時的に非表示にする

4-4 テーブルの集計

082 テーブルの集計を表示したい

事例1 集計行を利用して、テーブルに数量と金額の合計を表示する

083 テーブルに数式の列を追加したい

事例1 テーブルを利用して、レコードごとの金額を表示する

084 条件に合うデータだけを集計したい

事例1 構造化参照を利用して、商品名ごとの金額を集計する

085 抽出したレコードを集計したい

事例1 SUBTOTAL関数を利用して、オートフィルタで抽出したレコードだけを対象

に集計する

086 空白のセルの個数を求めたい

事例1 COUNTBLANK関数を利用して、出荷日が入力されていないレコード件数を

求める

087 単価と数量だけで金額の総合計を求めたい

事例1 SUMPRODUCT関数を利用して、単価と数量から金額の総合計を求める

088 複数の条件を満たすレコードの合計を求めたい

事例1 数式を利用して、商品名と顧客名が一致する金額の合計を求める

第5章 データの分析

5-1 データ分析の基本

089 データ分析の基本

5-2 関数で行うデータ分析

090 分散を求めたい

事例1 VARP関数を使用して、分散を求める

091 標準偏差を求めたい

事例1 STDEVP関数を使用して、標準偏差を求める

092 偏差値を求めたい

事例1 数式を利用して、偏差値を求める

093 データの25%にあたる値を求めたい

事例1 QUARTILE関数を使用して、データの25%にあたる値を求める

094 任意の値の順位を百分率で表示したい

事例1 数式を利用して、順位を百分率で表示する

5-3 グラフで行うデータ分析

095 円グラフで構成比を調べたい

事例1 円グラフを利用して、商品区分別売上の構成比を確認する

096 レーダーチャートでバランスを調べたい

事例1 レーダーチャートを利用して、月ごとの売り上げバランスを確認する

097 単価と売上金額から売れ行きの傾向を調べたい

事例1 販売価格の変更による売り上げの傾向を確認する

5-4 分析ツールで行うデータ分析

098 1つの値の変化に対応する試算の表を作成したい

事例1 データテーブルを利用して、粗利率の変更に対応する試算の表を作成する

099 2つの値の変化に対応する試算の表を作成したい

事例1 データテーブルを利用して、粗利率と仕入れ値の変更に対応する試算の表を作成する

100 シミュレーションのケースを保存したい

事例1 シナリオを利用して、試算の表を作成する

101 目標値を得るために必要な値を求めたい(1)

事例1 ゴールシークを利用して、目標値を得るための逆算をする

102 目標値を得るために必要な値を求めたい(2)

事例1 ソルバーを利用して、目標値を得るための逆算をする

103 基本統計量を表示したい

事例1 平均や標準偏差、分散などの統計量を表示する

104 ヒストグラムを作成したい

事例1 度数分布を表すグラフを作成する

第6章 ピボットテーブルを利用した集計

6-1 ピボットテーブルの作成

105 ピボットテーブルの基本

106 金額を合計するピボットテーブルを作成したい

事例1 ピボットテーブルを利用して集計表を作成する

107 集計範囲を変更したい

事例1 ピボットテーブルの元データの範囲を変更する

6-2 集計データの編集

108 月別にまとめて集計したい

事例1 ピボットテーブルの集計を月別にする

事例2 グループ化するアイテムを選択する

109 空白セルに「0」を表示させたい

事例1 ピボットテーブルの集計結果の空欄に任意の文字を表示する

110 集計方法を変更したい

事例1 ピボットテーブルの集計方法を個数から合計に変更する

事例2 全体に対するアイテムの構成比を表示する

111 任意の集計フィールドを作成したい

事例1 集計結果をもとに計算するフィールドを作成する

112 複数のワークシートのセル範囲を集計したい

事例1 ピボットテーブルウィザードを利用して、複数のワークシートのセル範囲を集計する

6-3 ピボットテーブルのレイアウト

113 フィールドやアイテムの表示順序を入れ替えたい

事例1 ピボットテーブルの顧客名フィールドの順序を入れ替える

114 列幅が変更されないようにしたい

事例1 ピボットテーブルの列幅の自動調整を無効にする

115 データの表示形式を変更したい

事例1 ピボットテーブルの表示形式を通貨スタイルに変更する

116 小計の表示位置を変更したい

事例1 ピボットテーブルの小計の表示位置を明細の下にする

117 総計を非表示にしたい

事例1 ピボットテーブルの総計行を非表示にする

118 指定した範囲のデータを表示したい

事例1 受注金額の合計が50,000円以上の顧客を表示する

119 ピボットテーブルの表示形式を変更したい

事例1 ピボットテーブルを表形式に変更する

120 ピボットテーブルに書式を設定したい

事例1 ピボットテーブルスタイルを利用して、ピボットテーブルに書式を設定する

121 レイアウト変更をすぐに反映しないようにしたい

事例1 レイアウトの更新を保留して、変更をすぐに反映しないようにする

6-4 ピボットグラフの作成

122 ピボットグラフを作成したい

事例1 ピボットテーブルを元にグラフを作成する

123 グラフにするデータを選択したい

事例1 ピボットグラフフィルタウィンドウを利用して、売上金額の高い商品分類を抽出する

124 グラフの種類を変更したい

事例1 ピボットグラフを円グラフで表示する

125 ピボットグラフで詳細を非表示にしたい

事例1 ピボットグラフの詳細データを折りたたむ

第7章 データベース関数

7-1 データベース関数の入力

126 データベース関数の基本

127 条件を満たすデータの合計を求めたい

事例1 DSUM関数を使用して、任意の受注日と商品分類に一致する売上金額の合計を求める

128 条件を満たすデータの最大値を求めたい

事例1 DMAX関数を使用して、任意の受注日と商品分類に一致する売上金額の最大値を求める

129 条件を満たすセルの個数を求めたい

事例1 DCOUNT関数を使用して、任意の受注日と商品分類に一致するレコード件数を求める

130 条件を満たすデータの値を求めたい

事例1 DGET関数を使用して、任意の受注日と売上金額に一致するレコードの値を求める

7-2 データベース関数の活用

131 条件を満たすレコードの標準偏差を求めたい

事例1 DSTDEVP関数を使用して、任意の受注日と商品分類に一致するレコードの標準偏差を求める

132 条件を満たすレコードの分散を求めたい

事例1 DVAR関数を使用して、任意の受注日と商品分類に一致するレコードの分散を求める

ONE POINT目次

すぐ上のセルと同じデータを入力する

オートコンプリート機能を有効にする

リストに表示するデータを指定する

郵便番号変換ウィザードをインストールする

相対参照と絶対参照

オートフィル機能を利用できるようにする

NOW関数で時刻だけを表示する

保護されたシートで許可される操作

ワークシートの1行目や1列目をすばやく固定する

ほかのセルにふりがなを表示する

ふりがなのフォントやフォントサイズを変更する

すでに設定されている入力規則を変更する場合

無効なデータを入力不可にする

ほかのワークシートにあるデータを基にリストを作成している場

検索する文字列が入力されているワークシートがわからない場合

英数字の大文字と小文字を区別して検索する

書式を削除する

セルの内容を置き換えることを確認するメッセージが表示される

PHONETIC関数で表示されるふりがなをひらがなにする

入力されたデータを除算する

条件付き書式を削除する

条件付き書式の書式内容を削除する

リンクの自動更新を有効にする

並べ替え順序について

先頭行をデータとして並べ替える

すべてのレコードを表示する

すべてのレコードを表示する

抽出条件を2つ設定する

ワイルドカードを使用する

比較演算子を使用する

集計を削除する

グループ化を解除する

SUBTOTAL関数の集計方法

COUNTIF関数で空白のセルの個数を求める

SUM関数で積の和を求める

テーブルを利用している場合

テーブルを利用している場合

テーブルを利用している場合

戻り値に入力する数値

シナリオを削除する

フィールドの表示エリアを変更する

データ範囲を自動拡張する

数値データを区間ごとに集計する

エラー値の表示を変更する

フィルタを解除する

作成できないグラフの種類

ピボットグラフフィルタウィンドウを表示する

グラフの配色を表示する

詳細データを再表示する

詳細データを表示する

PR

秀和システム