読者です 読者をやめる 読者になる 読者になる

実践で使うExcelとAccessの話

20年以上Excelを使ってきた筆者が綴ります

Excelの小技:元データシートとの連携を崩さずにシートをコピーする方法

今日は簡単で使える小技の紹介です。

ピボット集計、グラフ、関数などシート間で連携していて、これをコピーして新たなセットを作りたい。そんな時これを使うと、データ元のリンクを貼りなおさなくてもいいので大変便利です。

 

操作はあっけないほど簡単です。しかし知っているといないとでは、作業効率が違います。

 

例えばこういう場合

ここに2つのシートがあります。

 

⇒「データ」シート

f:id:yururimaaruku:20170311144010p:plain

f:id:yururimaaruku:20170311142531p:plain

 

⇒「グラフ」シート:データAシートを元に作成したグラフ

f:id:yururimaaruku:20170311144032p:plain

f:id:yururimaaruku:20170311142646p:plain

 

「このデータとグラフはそのままにして、セットでコピーしたい」場合、どう操作するのがよいでしょうか。

 

私の職場にデータとグラフを別々にコピーしている人がいました。

 

データシートをコピーしてデータ(2)シートを作った後、
グラフシートをコピーしてグラフ(2)シートを作る・・。

 

これだとグラフの元データを変更しなおさないといけません

間違いではありませんが、おすすめはしません。

 

 ▼具体的に言うと、ここでこうして元データを修正しないといけない。

f:id:yururimaaruku:20170311143042p:plain

「データ(2)」シートに変更。ちょっとしたことだけど面倒です。

f:id:yururimaaruku:20170311143104p:plain

 

セットでコピーする方法(おすすめ)

こういう時は、シートごとセットでコピーしましょう。

 

つまり「最初に2シートごと選択してコピー」するのです。

 

▼簡単ですが、やり方を説明します。

 

どちらかのシートを選択した状態で・・

f:id:yururimaaruku:20170311144010p:plain

「ctrl」キーを押しながらもう一つのシート名タブをクリックして複数シート選択します。

f:id:yururimaaruku:20170311144508p:plain

シート名の上で右クリック→「移動またはコピー」でシートをコピーします。

(もちろんctrlを押しながらドラッグでもコピーできます。ここでは画面どりが出来なかったので右クリックにしました)

f:id:yururimaaruku:20170311144609p:plain

 

分かりやすいように末尾へ移動しました。

f:id:yururimaaruku:20170311143810p:plain

シートが複製できました。

f:id:yururimaaruku:20170311144647p:plain

「セットでコピー」が良い理由

一言でいうとこれです!

「コピー先のデータシートを参照してくれる」

 

では、コピー先である「グラフ(2)」シートのグラフを右クリックして元データを確認してみましょう。

f:id:yururimaaruku:20170311144713p:plain

 

ほら、データソースがコピー先の「データ(2)」シートになっていますね

f:id:yururimaaruku:20170311144943p:plain

 

これで元のグラフセットを崩すことなく、心置きなくコピー先のデータを編集することができます。

 

ピボット集計や関数にも使える

f:id:yururimaaruku:20170311145720p:plain

ピボット集計では「データ」と「集計結果」でシートを分けるのがよいと以前ご説明しました。ということはシートが分かれているわけです。

 

「ちょっと違うパターンでも出して欲しい」

 

こんな時、もとのピボット集計はそのままに、新たなセットを作ります。シートをまたいでセル参照して関数を入力している時も同じです。

 

試してみてください。データ元もきちんと移動している、この便利さは分かる人には分かります!

 

私の周りでも意外と知らない方が多いです。操作自体は簡単ですが、考え方を身に着けておくと無駄な作業が省けます。ぜひご活用ください。

 

では、またお会いしましょう。

Excelの印刷設定の謎。同じフォントサイズなのに印刷すると大きさが違うのは・・。

f:id:yururimaaruku:20170304153318p:plain

昨日職場で「同じフォントサイズなのに文字サイズが違う」という相談を受けました。今回は2つのファイルの「ページ倍率」が異なっていたのが原因でした。他の人が作ったファイルを引き継いだりすると悩んでしまう方が多いようです。慣れるまでは迷いますよね。

 

印刷設定で知っておくとよいポイントがありますので、「Excelの印刷が苦手」という方はご参考にしてください。

 

分かりやすくするために大事なところだけ絞って書きます。

 

★Excel印刷は「余白」と「ページ倍率」がポイントです★

 

印刷が苦手な方はここを確認してみてください。

 

<目次>

 

余白の内側が1ページ

まず基本中の基本、用紙の余白を確認します。当たり前だけど大事なポイントです。紙のどの範囲を印刷に使うのかここで決めます。

 

▼ページレイアウト→余白→ユーザー設定の余白

f:id:yururimaaruku:20170304143209p:plain

画面の絵を見ると分かりやすいですね。

上下左右の余白の内側に印刷されます。これは1つのシート内で全ページ共通です。

 

余白の設定

余白とは紙の端から印刷領域までの幅です。「ページレイアウト」→「余白」→「ユーザー設定」の余白で「上下左右〇〇mm内側の枠内に印刷する」と指定できます。縦横はページタブで設定します。

f:id:yururimaaruku:20170304152020p:plain

  

ページ倍率を理解する

さて次です。さらに重要ですよ。

100%が規定のサイズ

100%が「規定のサイズ」で印刷される倍率です。

 

規定のサイズとは、例えば文字サイズなら12ポイントは約4.2mm、9ポイントは約3.17mmというように決まった一定の決まったサイズのことです。100%で作成すればその一定のサイズで印刷されます。

 

ExcelはWordのようページの画面イメージになっておらず慣れないと分かりにくいですよね。画面いっぱいどこまでも広く文書が作成できます。

 

基本的にページ設定で指定した大きさで機械的に左上から区切られ、収まらない分は次のページに自動的にはみ出して印刷されます

ページ倍率で全体が拡大・縮小して印刷される

さらに重要ポイントです。

 

ページ倍率とは「規定のサイズからどのくらい拡大縮小して印刷するか」

指定するものです。

 

設定は「サイズ」→「その他の用紙サイズ」で行います。

初期設定では100%です。

f:id:yururimaaruku:20170304143511p:plain

例えば余白がこのように設定されている時に・・

f:id:yururimaaruku:20170304143602p:plain

100%に設定するとこのように印刷されていたものが

f:id:yururimaaruku:20170304143645p:plain

同じ余白で50%に変更するだけで、このように印刷されるイメージです。

f:id:yururimaaruku:20170304143714p:plain

小さくなった分、文字サイズも小さくなり罫線も細くなります

 

同じフォントサイズなのに何故か体裁が異なるのは、ここが異なることが多いのです。文書を同じように作成したい時は、ページ倍率を揃えておくとやりやすくなります。まず確認する習慣をつけることをおすすめします。

 

ページ倍率と画面倍率は異なる

ここで混同されることが多いのが「画面倍率」です。ここで変わらないのかとよく聞かれますが、違います。

 

画面倍率は単に画面の倍率を指定するだけのもので、印刷には影響しません。

画面右下のこの部分です。マウスの中央ボタンをコロコロと回すとこの数字が変わり、画面が拡大・縮小されます。

 

f:id:yururimaaruku:20170304144204p:plain 

 

便利だけど要注意の改ページプレビュー

基本は列幅・行幅・文字サイズで調整する

基本的には100%で文書を作成して、ページに収まらない時は、列幅・行幅・文字サイズを調整することをおすすめします。その方がフォントサイズや罫線の太さが一定して規定の感覚が身に着けられるからです。

 

ある程度身に着いて、上のような話が理解できた上で、おすすめしたいのが次に説明する改ページプレビューです。手っ取り早くページ切り替え位置の調整ができるので、急ぐ時にはこれを使うのも一つの手です。

改ページプレビューの使い方

例えばこういう場合です。L列の右側と17行目の下の点線がページの切り替え位置を表しています。18行目がはみ出しています。本来であれば行間を少し縮めればおさまりますので、そうするのが良いのですが、今回はこのままの縦横バランスで一枚に収めたいとします。

f:id:yururimaaruku:20170304145021p:plain

表示メニューの「改ページプレビュー」をクリックします。

f:id:yururimaaruku:20170304145229p:plain

このようなメッセージが出たらチェックを入れてOKをクリックします。

f:id:yururimaaruku:20170304145315p:plain

画面が少し縮小されて青いラインが現れました。「1ページ」とページ番号が背景に入ります。これは印刷されません。

f:id:yururimaaruku:20170304145417p:plain

17行目の下の青い横ラインを下へドラッグします。

f:id:yururimaaruku:20170304145522p:plain

全体が1ページ内におさまりました。簡単ですね。

 

「表示メニュー」→「標準」で表示は元に戻ります。見にくい方は戻しておきましょう。

f:id:yururimaaruku:20170304154354p:plain

改ページプレビューの正体

改ページプレビューは何を意味するのでしょうか。ページレイアウト→サイズ→「その他の用紙サイズ」を確認してみましょう。

f:id:yururimaaruku:20170304145736p:plain

初期設定では100%だったページ倍率が、95%になっています。

f:id:yururimaaruku:20170304145857p:plain

 

つまり、改ページプレビューは「ページ倍率の自動調整機能」なのです。

 

自動調整機能にはもう一つ便利なものがあります。

ページ倍率の下にある「次のページ数に合わせて印刷」です

現在の印刷設定範囲を何ページにわたって印刷するかというものです。

例えば「横1×縦1」と設定すれば、全体を1ページに自動的に収めてくれます

 

縦横の比率は一定で拡大縮小してくれます。そのため、縦横の内どちらかはみ出す方があればそれが余白の内側に収まるように自動調整します。

 

ですので、倍率は縦横のうち、長い方に影響されます。

 

「横1×縦 空白」とすれば、「縦は何ページに渡ってもいいので、横幅がぴったりおさまる範囲に倍率を自動調整する」ように働きます。これはよく使います。

 

慣れるまで少し混乱する方が多いのは、このようにいくつかの要素が関わることに拠ります。

 

ここが理解できれば、かなりスッキリすると思います。

 

ちなみに100%の時は罫線が少し太くなるので、私は罫線の中でも一番細いこのラインをよく使います。

f:id:yururimaaruku:20170304151204p:plain

Excelのツールバーの罫線ボタンを使うともう一回り太いラインがデフォルトですが、こちらを外枠のラインに使うとちょうどいい事が多いです。

 

今日は、まずは基本の部分をご紹介しました。

印刷にはその他色々便利な方法がありますが、またの機会にご紹介します。

 

何かのお役に立てば幸いです。

 

ではまたお会いしましょう。

おすすめ行移動のはや技。右ボタンのドラッグ使っていますか?移動もコピーもクリックが半分以下で済みますよ。

f:id:yururimaaruku:20161204000611p:plain

意外と知らない人が多い右ボタンのドラッグについて今日はお話しますね。

 

「右ボタンのドラッグ」って言うと長いので、私は勝手に「右ドラッグ」って名前を付けています。この右ドラッグを使うとコピーや移動が非常に楽になります。やっていない人を見つけたら「ほら、ここは右ドラッグですよ」と説明してよく感心されます。

 

クリック数が半分以下で済みますのでおすすめです。

 

<目次>

  • 知らない人が多い右ボタンのドラッグ
    • 行の移動
    • セルの移動・コピーにも
  • 普通のコピー・移動もマウスがおすすめ
    • シートもファイルも図形にも
      • シート
      • ファイル
      • 図形(オートシェイプ)
続きを読む

乱れたデータをピボット集計を使って手っ取り早く整える方法

ピボットを使うとどんなデータが入っているか瞬時に把握できます。たぶん何よりも効率的でないかと思います。これを使ってデータを整える時に私がよくやる方法を紹介します。Excelをよく使う方でないと難しいかもしれませんね。イメージしながら読んでください。

 

「乱れたデータ」といっても色々なタイプがあります。今回は主に表記のゆれをなおすのに役立つ方法です。

 

簡単に言うと

1.ピボットを使ってグループ化する
2.データを変換する
3.元のデータに列を作って戻す
4.戻した列で再度ピボット集計

 

では具体的にやってみますね。

 

<目次> 

  • ピボット集計を使ってデータを整える
    • ピボットを使ってグループ化する
    • 集計用の項目をつくる
    • 元のデータに列を作って戻す
    • 戻した列で再度ピボット集計
  • ピボットを使うにあたって
    • 謎のデータの詳細を見る方法
    • 書式の融通がきかないけど上手くつきあう
    • getpivotdata関数のよしあし
続きを読む

Excelオートフィルター抽出技。色や曜日も抽出できます。設定解除の早業など。

f:id:yururimaaruku:20161104232900p:plain

こんばんは。今夜も引き続きオートフィルター技の話です。さて、みなさん「セルや文字の色で抽出」または日付しか入っていないデータから「曜日で抽出」はできますか?余裕な方は、さすが! 

 

自信のない方、ぜひご参考にしてください。オートフィルターは実践で非常によく使う「データ分析の基本」です。まずは基本からいきますね。

 

  • まずは基本の抽出おさらい 
    • 選択する列によって〇〇フィルターの〇〇の部分が変わる
    • テキストフィルター
    • 数値フィルター
    • 日付フィルター
      • と言いますが実は・・。
  • キーワード選択の早業
    • テキストフィルターの検索機能を使って絞り込む
    • ワークシート上で見えているデータで絞り込む
  • おもしろ抽出。ご存じですか?
    • 色を使ったフィルター
    • 条件付き書式と組み合わせるのもおすすめ
    • 曜日で抽出する方法
      • 曜日に変換した列を作る。おすすめTEXT関数。
    • OR条件で抽出する方法
      • 複数列をOR条件でで絞り込む
  • オートフィルター解除の早業
    • ctrl+shift+Lを同時に2回押す。
続きを読む

意外にスゴイExcelオートフィルター。結果をどう使うかがキーになります。

f:id:yururimaaruku:20161028230104p:plain

オートフィルターは簡単にデータの分析ができる基本の機能です。操作自体は簡単です。簡単だけど意外とすごいんです。甘く見てはいけません。

 

前回書いた「データを整える」「下ごしらえ」をするのにも非常に役立つツールです。自信がない方はぜひマスターされることをおすすめします。

 

そして、問題はフィルターを使って抽出した後、それをどう使うかです。

 

ここでは、「実践でよく使う方法」や「比較的新しい機能」(←こちらは次回)をピックアップしてご紹介します。2回に分けてご紹介します。

(長くなりますので細かい具体的なやり方は省略して、考え方をメインに書きます。)

 

  • オートフィルターとは
    • 強力な抽出機能
    • 行が非表示になっているだけ
  • 抽出したデータで何をするかが大事
    • フィルターで絞りながらデータに目印を付ける
    • データの把握
      • 入力されているデータの把握
      • 簡易の計算結果の把握
    • 計算する(subtotalの活用)
    • 取り出す
      • 行(レコード)をコピーする
    • 整える
      • 行(レコード)を削除する
  • 活用のキーは自作の「抽出・集計用フィールド」
    • なければ作る
      • すべてのセルを&でつなぐ
      • 集計したい分類に変換する
続きを読む

Excelデータベースの鉄則。分析はデータの下ごしらえで決まります

f:id:yururimaaruku:20161023074731p:plain

Excelでのデータ分析にあたり一番大事なのが「データの下ごしらえ」です。「下ごしらえ」とは「集計できる形にデータを整えること」です。実践の場では、教科書のようにデータは美しく整っていません。ほとんどの場合、はっきり言って「無茶苦茶」。または変なデータが混ざっています。これを整えられるかどうかが決め手で、非常に重要なスキルです

 

困った・・を経験しながら、ひとつひとつ身に着けていくしかないのですが、注目すべきことについて、私なりにピックアップしてみます。

  • その前に・・
    • Excelの機能
    • 分析の話から焦点を当てていきます
  • データを整える
    • 分析しやすいデータとは
    • テーブル機能はひとまず置いておきます
  • データベースの鉄則とは
    • 1行目にタイトル、2行目以降がデータ
    • タイトル(フィールド名)は重複不可
    • 1件1行
      • 1件1行にする方法
    • 空白行は不可
      • 空白行を削除する方法
      • 空白行を削除しない方法
    • 表記を揃える
      • 半角・全角を変換する関数
      • ひらがな・カタカナを変換する関数
      • 余分な空白を取る関数
    • セル結合しない
続きを読む