2023/01/06

sponsored link
目次
小手先のテクニック集
- 数式が使用されてるセルを目立たせる
- 数式を表示する
Ctrl+Shift+@ - 数式の参照先を確認する
[数式]→[参照元のトレース] - 数式のあるセルを着色する
Ctrl+G→[セル選択]→[数式]をチェックしてOKを押す
選択状態になるので着色する
- 数式を表示する
- セルの書式設定のショートカットキー
対象のセルを選択して、Ctrl+1を押す - 金額を桁区切り(カンマ区切り)にする
対象のセルを選択して、Ctrl+Shift+1を押す - セル内にグラフで表示する
データを選択し、[ホーム]タブ→[条件付き書式]→[データバー]
表を作らずデータを作る
集計の表は手作りだと変更に多大な労力を要します。
最初から表ではなく「データ」を作成し、表現は「ピボットテーブル」で行います。
そうすることで、想いのままに表現を最速で変更確認して見やすい形にすることが可能となります。
途中でデータの「テーブル」化を行います。このテーブルについてもメリットが一杯です。
ピポットテーブルのメリット:
- 最初からデータ形式で作っておけば入力出力がしやすくなる
ソフト乗り換えの負荷を減らし、データの追加もしやすくなる - すぐに枠化構造を柔軟に変更が可能である
→xxx別の集計をyyy別に、四半期別に等 - データの入力なので、表作成のように考えずにすむ
→元とするデータの入力順序は表現されるピポットテーブルとは関係ないので、入力しやすい順で構築すればよい - 余分な技術を節約することができる
- 串刺し集計
- フィルタ
- グラフ化
- 統合
- 関数
- SUMIF
- SUMIFS
- COUNTIF
- COUNTIFS
テーブルのメリット:
- 全行の関数の内容を賢く拾ってきてくれる
- 行追加がたやすい
- 最初から集計して表現させることも可能
- インテリセンスが働く
- 構造化参照が使える
- 範囲が追従する
ピポットテーブルの作成
- 「データ」を作成する
データのルール(構造):- 1行目は項目名(見出し)を入れる
- 1行で1つのデータ
- 1つのシートにすべてを入力する
- 上と同じデータでも入力する(日付等)
- 空白の行、列を作らない
- 同じ種類のものは同じ列に入れる
- データをテーブルに指定する
データから直接ピポットテーブルを作成することもできますが、「テーブル」に指定するという行為をワンクッション挟めば、行追加等で対象が範囲から漏れるといったトラブルを防くことができます。
テーブル化せず直接したピポットテーブルにした場合は都度、範囲選択のし直しが必要になります。
(列単位で範囲指定すれば、回避は可能です)
「Ctrl+Shift+:」(データの範囲を選択)→「Ctrl+T」を押します - テーブルをピポットテーブルに指定する
「挿入」→「ピボットテーブル」で「テーブルまたは範囲を選択」の「テーブル/範囲」で「テーブル1」を指定する。
ピボットテーブルの更新は手動で行う必要がある
ピボットテーブルはリアルタイムで反映されないため、データを追加削除修正等の変更をした場合は手動で更新する必要があります。
とりあえず方法を3つほど
- 「Alt+F5」を押す方法
- ピボットテーブル上で右クリックして「データの更新」を選択する方法
- マクロで行う方法
「Worksheets(“xxx”).PivotTables(“ピボットテーブル1”).PivotCache.Refresh」
よく使うExcel関数
関数 | 種類 | 説明 |
OR | 論理 | いずれかの引数がTRUEのときにTRUEを返します。 |
IF | 論理 | 値または数式が条件を満たしているかどうかを判定します。 |
IFERROR | 論理 | 数式の結果がエラーの場合は指定した値を返し、それ以外の場合は数式の結果を返します。 |
AND | 論理 | すべての引数がTRUEのときにTRUEを返します。 |
SUBSTITUTE | 文字列 | 文字列中の指定された文字を他の文字に置き換えます。 |
FIND | 文字列 | 指定した文字列が現れる位置を返す。MIDと連携させることが多い |
LEN | 文字列 | 文字列に含まれる文字数を返します。 |
LEFT | 文字列 | 文字列の先頭(左端)から指定された文字数の文字を返します。 |
RIGHT | 文字列 | 文字列の末尾(右端)から指定された文字数の文字を返します。 |
MID | 文字列 | 文字列の任意の位置から指定された文字数の文字を返します。 |
CONCATENATE | 文字列 | 複数の文字列を結合して1つの文字列にまとめます。 |
ASC | 文字列 | 全角(2バイト)の英数カナ文字を半角(1バイト)の文字に変換します。 |
JIS | 文字列 | 文字列内の半角(1バイト)の文字を全角(2バイト)の文字に変換します。 |
TEXT | 文字列 | 数値を書式設定した文字列に変換します。 |
TRIM | 文字列 | 文字列から余分なスペースを削除します。 |
VALUE | 文字列 | 文字列を数値に変換します。 |
PHONETIC | 文字列 | 文字列からふりがなを抽出します。 |
DATE | 日時 | 指定された日付に対応するシリアル値を返します。 |
DATEDIF | 日時 | 2つの日付間の日数、月数、年数を計算します。この |
YEAR | 日時 | シリアル値を年に変換します。 |
MONTH | 日時 | シリアル値を月に変換します。 |
DAY | 日時 | シリアル値を日付に変換します。 |
HOUR | 日時 | シリアル値を時刻に変換します。 |
MINUTE | 日時 | シリアル値を時刻の分に変換します。 |
EDATE | 日時 | 開始日から起算して、指定した月数だけ前または後の日付に対応するシリアル値を返します。 |
EOMONTH | 日時 | 開始日から起算して、指定した月数だけ前または後の月の最終日に対応するシリアル値を返します。 |
COUNT | 統計 | 引数リストの各項目に含まれる数値の個数を返します。 |
COUNTA | 統計 | 引数リストの各項目に含まれるデータの個数を返します。 |
COUNTIF | 統計 | 指定された範囲に含まれるセルのうち、検索条件に一致するセルの個数を返します。 |
MAX | 統計 | 引数リストに含まれる最大の数値を返します。 |
MIN | 統計 | 引数リストに含まれる最小の数値を返します。 |
AVERAGE | 統計 | 引数の平均値を返します。 |
SUM | 数学/三角 | 引数を合計します。 |
SUMIF | 数学/三角 | 指定された検索条件に一致するセルの値を合計します。 |
SUMIFS | 数学/三角 | セル範囲内で、複数の検索条件を満たすセルの値を合計します。 |
INT | 数学/三角 | 指定された数値を最も近い整数に切り捨てます。 |
ROUND | 数学/三角 | 数値を四捨五入して指定された桁数にします。 |
ROUNDUP | 数学/三角 | 数値を指定された桁数に切り上げます。 |
ROUNDDOWN | 数学/三角 | 数値を指定された桁数で切り捨てます。 |
NA | 情報 | エラー値#N/Aを返します。 |
CELL | 情報 | セルの書式、位置、内容についての情報を返します。 |
よく使うショートカットキー
内容 | キー |
ジャンプ | Ctrl G |
ハイパーリンク | Ctrl k |
ハイパーリンクから戻る | Alt ← |
ハイパーリンクから進む | Alt → |
取消線 | Ctrl 5 |
枠線追加 | Ctrl Shift 6 |
枠線削除 | Ctrl Shift _ |
コメント追加 | Shift F2 |
選択範囲でテーブル作成 | Ctrl t |
数式で表示させる | Ctrl Shift @ |
グループ化 | Alt Shift → |
グループ化の解除 | Alt Shift ← |
展開 | Alt Shift + |
折りたたむ | Alt Shift – |
アウトラインの表示/非表示を切替 | Ctrl 8 |
選択行の表示OFF | Ctrl 9 |
選択行の表示ON | Ctrl Shift 9 |
選択列の表示OFF | Ctrl 0 |
選択列の表示ON | Ctrl Shift 0 |
選択セルに同じ内容で確定 | Ctrl Enter |