みなとみらい自由開発研究室

大好きな横浜から貴方のお役に立てるかもしれない情報を発信します!

EXCEL(エクセル)の秘宝

time 2017/06/05

EXCEL(エクセル)の秘宝

sponsored link

小手先のテクニック集

  1. 数式が使用されてるセルを目立たせる
    1. 数式を表示する
      Ctrl+Shift+@
    2. 数式の参照先を確認する
      [数式]→[参照元のトレース]
    3. 数式のあるセルを着色する
      Ctrl+G→[セル選択]→[数式]をチェックしてOKを押す
      選択状態になるので着色する
  2. セルの書式設定のショートカットキー
    対象のセルを選択して、Ctrl+1を押す
  3. 金額を桁区切り(カンマ区切り)にする
    対象のセルを選択して、Ctrl+Shift+1を押す
  4. セル内にグラフで表示する
    データを選択し、[ホーム]タブ→[条件付き書式]→[データバー]

表を作らずデータを作る

集計の表は手作りだと変更に多大な労力を要します。

最初から表ではなく「データ」を作成し、表現は「ピボットテーブル」で行います。
そうすることで、想いのままに表現を最速で変更確認して見やすい形にすることが可能となります。

途中でデータの「テーブル」化を行います。このテーブルについてもメリットが一杯です。

ピポットテーブルのメリット:

  • 最初からデータ形式で作っておけば入力出力がしやすくなる
    ソフト乗り換えの負荷を減らし、データの追加もしやすくなる
  • すぐに枠化構造を柔軟に変更が可能である
    →xxx別の集計をyyy別に、四半期別に等
  • データの入力なので、表作成のように考えずにすむ
    →元とするデータの入力順序は表現されるピポットテーブルとは関係ないので、入力しやすい順で構築すればよい
  • 余分な技術を節約することができる
    • 串刺し集計
    • フィルタ
    • グラフ化
    • 統合
    • 関数
      • SUMIF
      • SUMIFS
      • COUNTIF
      • COUNTIFS

テーブルのメリット:

  • 全行の関数の内容を賢く拾ってきてくれる
  • 行追加がたやすい
  • 最初から集計して表現させることも可能
  • インテリセンスが働く
  • 構造化参照が使える
  • 範囲が追従する

ピポットテーブルの作成

  1.  「データ」を作成する
    データのルール(構造):

    • 1行目は項目名(見出し)を入れる
    • 1行で1つのデータ
    • 1つのシートにすべてを入力する
    • 上と同じデータでも入力する(日付等)
    • 空白の行、列を作らない
    • 同じ種類のものは同じ列に入れる
  2. データをテーブルに指定する
    データから直接ピポットテーブルを作成することもできますが、「テーブル」に指定するという行為をワンクッション挟めば、行追加等で対象が範囲から漏れるといったトラブルを防くことができます。
    テーブル化せず直接したピポットテーブルにした場合は都度、範囲選択のし直しが必要になります。
    (列単位で範囲指定すれば、回避は可能です)
    「Ctrl+Shift+:」(データの範囲を選択)→「Ctrl+T」を押します
  3. テーブルをピポットテーブルに指定する
    「挿入」→「ピボットテーブル」で「テーブルまたは範囲を選択」の「テーブル/範囲」で「テーブル1」を指定する。

ピボットテーブルの更新は手動で行う必要がある

ピボットテーブルはリアルタイムで反映されないため、データを追加削除修正等の変更をした場合は手動で更新する必要があります。

とりあえず方法を3つほど

  1. 「Alt+F5」を押す方法
  2. ピボットテーブル上で右クリックして「データの更新」を選択する方法
  3. マクロで行う方法
    「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

sponsored link

down

コメントする




水耕栽培

みなとみらい自由開発研究室

ゆうろ

ゆうろ

はじめまして!室長のゆうろです! フリーランスのエンジニアとして活動しています。 サイトではシンプルライフ、地元情報、書評、パソコン、愛用品、ライフハック、ノウハウ等を掲載しようと思います。 お仕事も募集しています! eurobeat@b4u.yokohama ココナラにも参加しています! よろしくね! [詳細]



sponsored link