inst_yamamoto のすべての投稿

罫線

表の見栄えをアップするために罫線を引きましょう。

エクセルには初めから枠線が引かれていますが、プリントアウト時には印刷されません。

このように罫線を引いておけばプリントアウト時には印刷されますしエクセル上でも視認性が上がります。

印刷プレビュー

罫線の設定は簡単です。

罫線を引きたい範囲を選択します。

ホームタブのフォントの罫線の右の三角▼をクリックします。

格子をクリックします。

罫線が引かれました。

全体の枠の上と左が見えていませんが、上記の印刷プレビューで確認いただけるようにきちんと印刷されますので安心してください。

条件を指定してカウントするCOUNTIF関数

数値を含むセルの個数をカウントする場合はオートサムボタンの横関数 COUNT関数 MAX関数 MIN関数を参照してください。

今回はある条件を満たしたものだけをカウントするCOUNTIF関数のご紹介です。

例えばアンケート集計をエクセルで行いたい場合があるか無いかは分かりませんが例にしようと思います。手集計をしたものをエクセルで加工する場合があるかもしれませんが、今回は生データをエクセル上に入力してからエクセルで集計してみます。

5段階評価で、楽しかったか、役に立ったか、また来たいかの3項目についてアンケートを取りました。匿名アンケートなのでアンケート用紙の通し番号と5段階の数値をデータとして入力しました。

5が満足度が高く1が低いとしておきましょうか。

この表から各項目に5と評価したした人が何人いて4と評価した人が何人いてという風に、各項目に対する評価の人数を関数で引っ張り出したいですね。

このような集計表になるでしょうか。楽しかったかの項目に1の評価とした人の合計をセルG3に集計してあげればよいでしょう。このような場合にCOUNTIF関数を使います。

答えを出したいセルG2を選択しCOUNTIF関数を選択します。

COUNTIF関数
1つの検索条件に一致するセルの個数を返します。

COUNTIF(範囲,検索条件)

範囲は集計元のデータのある範囲です。今回は楽しかったかの項目で1と答えた人の数が欲しいので楽しかったかの項目のデータ範囲であるセルA3からセルA17となります。

検索条件は1と答えた人の1となりますのでセルF3となります。

=COUNTIF(B3:B17,F3)

という数式になりますが、あとでオートフィルで指揮をコピーした場合範囲のB3:B17がずれてしまうのでF4キーで絶対参照にしておきます。

=COUNTIF($B$3:$B$17,F3)

このような式にしておけばオートフィル後も正しい結果を表示してくれます。あとは同じことを役に立ったかのセルH3,また来たいかのセルI3ですればよいでしょう。

セルH3には
=COUNTIF($C$3:$C$17,F3)

セルI3には
=COUNTIF($D$3:$D$17,F3)

となります。これの数式の引数はよく似ていますね。範囲の列がB列からC、Dと変わっていますが行は3行目から17行目というのは変わっていません。

検索条件はすべてF3です。

絶対参照のつけ方をうまくすればセルG3 に入れた式1つをオートフィルすればすべての答えを出すことができます。絶対参照2(九九表作成)を参照してみてください。

セルG3に
=COUNTIF(B$3:B$17,$F3)

と入力しておきます。範囲のB$3:B$17はあとでオートフィルで縦横にコピーしたときに列番号は変化するが行番号は変化しないということです。

検索条件の$F3は同じように列のFは変化しないが行の3は変化するということです。

縦方向のオートフィルをします。

更にそのまま横方向にオートフィルを行います。

1つの数式を入力し、縦横にオートフィルをすることでうまくいきました。

F4キーを複数回叩くことで片方は絶対参照、もう片方は相対参照とすることでうまくいきました。(複合参照言うらしいです。)

グラフタイトルをセルの値と連動する

エクセルのグラフの表示をいろいろと変更するには元データを変えずにグラフの文字列を変更するを参照してみてください。

上記のリンクではグラフタイトルはダブルクリックで自由に変更できるとだけ書いてありますが、セルの値をそのまま使用することができます。あまりあちこちで紹介されいないようです。

まずグラフを作成します。

今回は棒グラフにしてみました。

通常グラフタイトルはグラフタイトルの場所をダブルクリックするとカーソルが出てきて文字を打ちなおすことができます。

今回はグラフタイトルをクリックして選択した状態にし、数式バーにイコール(=)を手入力します。その後グラフタイトルとして使用したいセルをクリックします。私の場合はシート見出しがSheet2 (3)だったので=’Sheet2 (3)’!$A$1:$M$1のような式が自動で入りました。この状態でEnterキーを叩いてください。

そうするとグラフタイトルはセル参照になりセルのあたりが変更されれば自動でグラフタイトルが変更されるようになりました。

ただご注意いただきたいのは、この状態ではダブルクリックによるグラフタイトルの変更ができません。

一旦グラフタイトルの式を何かの文字列の式に変更します。文字列をイコールの右に書きますのでダブルクォーテーションで括ってください。文字列の中身は何でもいいです。

エンターキーで確定後いったんどこかのセルをクリックして選択します。その後グラフタイトルをクリックすると式自体消えていますので文字列を打ち直すことができます。

あまり使わないですね。

切り取り線の作成

ワードには特に切り取り線の作成の機能があるわけではありませんが、時折質問があったり、サンプルを提示するとうまく同じように作成できなかったりと少し難しいようですのでご紹介しておきます。

切り取り線は図形(オートシェイプ)で作成されています。

挿入タブの図から図形をクリックし直線を選択します。

ドラッグして直線を書きます。歪まないようにするにはaltキーををしたままドラッグします。

描いた直線が選択されている状態で描画ツールの書式の図形のスタイルの図形の枠線をクリックします。実線/点線から適当な線(破線)をクリックします。

次に切り取りという文字をテキストボックス横書き、または正方形/長方形に書き込みます。

あとは、テキストボックスの枠線を消せば良いのでテキストボックスが選択されている状態で先ほどと同じ要領で図形の枠線をクリックし枠線なしをクリックします。

これで出来上がりです。

同じように図形の直線を使いますが、切り取りの文字を入力後中央揃えにして半分の破線を引いて、その破線をコピーペーストし反対側に移動する方法もあります。

図形(オートシェイプ)についてはワードで年賀状作成(文面)も参考にしてみてください。

重複データの削除

アクセスを見ておりますと、重複データで検索していただいてこちらに来てくださる方が多いようです。重複行の合算や削除 (フィルタ ピボットテーブル)で重複データの削除方法をご紹介しているのですが、ほかにも簡単な方法がありますのでご紹介します。

下の図は黄色と青の同じ背景色のデータは重複データです。まったく同じ内容となっています。もちろんわかりやすいように色を付けているだけなので重複データがどれかわからない状況だと思ってください。

表の中をクリックして選択します。データタブのデータツールの重複の削除をクリックします。

重複の削除のダイアログボックスが表示されます。スクロールで隠れていますが列の見出しが列挙されており、チェックが入っています。これらはAND条件で選択されていると思ってください。名前が同じでふりがなも同じでさらにアドレスも同じで更に…というようにチェックが入っている項目すべてが同じものを重複とみなすということです。

OKボタンをクリックします。

削除したデータ件数と残ったデータ件数を表示してくれています。OKボタンをクリックします。

後ろにある重複データが削除され自動で上に詰められました。

上の画像にもありますように一意である列が確定する場合、例えば同姓同名はあり得る。住所変更前と後のデータがあるかもしれない。でもメールアドレスは、絶対重複しないというデータであれば重複の削除のダイアログボックスでメールアドレスだけをチェックしておけばOKです。

削除のダイアログボックスで列の選択を調整すればいろいろと使えます。

簡単 データ移動(コピーも)

単純な移動はセルの値の移動を参照してください。

データを移動したいセルを選択します。複数セルでも構いません。選択したセルの周りの太い枠線の上にマウスポインターを合わせます。マウスポインターの形が上下の矢印になります。

その状態でドラッグするとデータ(数式、書式)の移動になります。

ドロップ(マウスのボタンを放します。)します。

CTRLキーを押したままドラッグするとコピーになります。

移動先にデータがあった場合は上書きされてしまいます。

上書きされてしまうといけない場合はいったん移動(コピー)されてくるデータ分を空けておくのがいいです。

でもこれはちょっと面倒です。移動(コピー)先のデータを消すことなく移動(コピー)したい場合は移動(コピー)先でShiftキーを押して消されてしまうデータの移動先を決定します。

緑の線が枠ではなくて一本の線になり、端が少し伸びてアルファベットのHのような形状になります。

列を挿入する場合は工の時の様に上下の端が横に伸びます。

非常に便利です。

ちなみにCtrl+Shifht+ドラッグでコピーしながら挿入も可能です。

元データを変えずにグラフの文字列を変更する

下のようなごく一般的な棒グラフがあります。グラフ内には

  1. グラフタイトル
  2. データラベル
  3. 縦軸(数値軸)
  4. 横軸(項目軸)
  5. 凡例

グラフタイトル

といくつもの文字が表示されています。1のグラフタイトルはダブルクリックで変更することが可能です。

データラベル

各棒の上(下にもある)に数値が表示されていますが、この棒の元データの値になります。もちろん元データを変更すれば自動で変更されますが、今回は元データを変更しない前提です。

変更したいデータラベルをクリックします。系列全体が選択されますのでサイトクリックします。データラベル1つだけが選択されました。更にクリックするとデータラベルの値が選択されますのでキーボードで上書きすることが可能です。

17.5とセルE2の値を表示している棒の上にとんでもない数値が表示されています。もちろん文字にも変更できます。一度変更すると元データの値が変更されてもデータラベルの表示は変更されません。

元データと連動させたい場合は再度そのデータラベルのみを選択して右クリックします。データラベルフィールドの挿入をクリックします。

値をクリックすると元データの値が入り元データと連動するようになります。ただ、入力した内容は自動では消えませんので値だけを残してうまく消してください。また、セルをクリックすればセルが選択できるようになりますので元データとは違うセルの値を参照して表示させることもできます。

縦軸

2の縦軸(数値軸)の値は元のグラフの値をもとにエクセルが自動で表示しています。軸の書式設定から縦軸の最大値や最小値などの変更が可能です。

また、表示形式を変更すれば後ろに単位を付けたりすることも可能です。

表示形式については表示形式 (数値)を参照してください。#は数値なのですが、#℃とすると0の時に0が表示されません。0℃としておけば0の時は0それ以外の数値は数値をそのまま表示してくれます。0″℃”と文字列の前後はダブルクォーテーションで括っておいた方がよかったかもしれません。

凡例

横軸を後に回しまして、先に凡例の変更方法をご紹介します。凡例の文字列の変更方法はグラフを選択した状態でグラフツールのデザインタブのデータのデータの選択をクリックします。

データソースの選択のダイアログボックスが表示されます。左側の凡例項目に凡例が列挙されています。表示を変更したいものをクリックし選択された状態(背景色がついている)で編集のボタンをクリックします。

系列名に
=Sheet1!$A$2
と数式が入っています。Sheet1!はこのグラフの元データはSheet1にあるということです。この画像のグラフもデータもSheet1にあります。これを消して手入力すれば自由に変更できます。

=”めちゃ熱い時の気温”
の様にイコールの後にダブルクォーテーションで文字列を括ってあげればうまくいきます。

OKボタンをクリックします。そのほかの凡例も変更しておきます。

凡例を任意の文字に変更することができました。この状態はグラフが選択されて元データも範囲選択されている状態ですが、A列の横軸の見出しだったところが選択されていません。このグラフの元データにはもう、セルA2からセルA4は含まれていません。ですからグラフの行列の切り替えをすると元データも表示されませんし、入力されたデータも表示されなくなります。

凡例を任意の文字に変更するときにセル参照も使えます。ただし、セル参照をを使うと行列の切り替え自体ができなくなるようです。

横軸

横軸は凡例と同じようにデータの選択から変更しますが、凡例の時の様に横軸の見出しの個数列挙されていますがどれを選んでも同じ1つの式になっています。また、直接文字列を指定することはできないようです。

横軸の表示を変更したい値をセルに入力しておきます。そして軸ラベルのダイアログボックスの軸ラベルの範囲に指定します。

横軸の値がセル参照になり、指定した通りセルH8からセルH11の値が表示されています。

凡例も横軸もセル参照に変更すると行列の入れ替えのボタンがグレーアウトして選択できない状態になってしまいます。

グラフ 降下線と高低線

折れ線グラフでどのマーカー(値がプロットされている点)がどの項目なのかわかりづらい時に、降下線というのを使ってマーカー部分と軸の関係をわかりやすくします。

下の折れ線グラフですが赤丸のデータは6月でしょうか、7月でしょうか8月でしょうか。正解は7月のデータなのですが、少し迷いませんでしたでしょうか。

降下線を引いておけば少しわかりやすくなります。

グラフを選択した状態でグラフツールのデザインのグラフのレイアウトのグラフ要素の追加をクリックします。線の降下線をクリックします。

グラフのマーカー部分から横軸に降下線が引かれどのデータが何かが分かりよくなりました。

降下線と似た感じのものに高低線があります。高低線は各項目の最大値から最小値を結びます。

下のような最高気温と最低気温の折れ線グラフですが、高低差の大きい月と小さい月があります。高低線を引くと少しわかりやすくなるかもしれません。

降下線と同様にグラフを選択した状態でグラフツールのデザインのグラフのレイアウトのグラフ要素の追加をクリックします。線の高低線をクリックします。

間を見るより線を見る方が少し見やすいかもしれません。

ちょっとイレギュラーなグラフになりますが高低線だけのグラフを作ってみます。

縦のガントチャートのような形ですね。先ほどの高低線のところまで見ていただいていたらもう作り方はお分かりかと思います。

簡単に説明しておきます。ふつうに折れ線グラフ(マーカーなし)を作り、データラベルを上と下に分けて表示します。

それぞれ、折れ線を選択して

データの系列の書式設定から塗りつぶしと線の線から線なしにして折れ線を2本とも見えなくします。その状態で高低線を引きます。

あとは線の太さと色を変更して出来上がりです。

実務で使うことはないかもしれませんがこんなのもできますよってことでお願いします。

セルの最適幅

2013年末からOffice ワード エクセルを始めていますが、今更ながら基本的な操作をご紹介していなかったりしています。もしかしたら何かのついでに触れているかもしれませんが。

今回はセルの最適幅についてです。

#####と表示されているセルがあります。これはセルの幅より長い桁数の数値が入力されている場合や、日付や時刻で負の数値が入力されている場合に表示されます。

セルの幅を広げれば解消されますのでセルの幅を広げます。

幅を変更したい列の見出しの右側にマウスポインターを移動します。列と列の境目にマウスポインターを合わせ、左右に引っ張るような矢印に変わったらドラッグします。

セルの幅が広がったらドラッグを中止します(マウスの左ボタンを放す)。

C列の####の表示はなくなり、数値が表示されました。列幅に数値が収まらない場合に####のような表示になりますが文字列の場合は表示が切れていてもわかりません。

また、表が縦長だったりして目視できる範囲外に大きな数値があった場合などドラッグでは都合が悪いこともあります。セルC1は『県内総生産』という文字が見えていますが、実際は『県内総生産(名目)単位:100万円』なので切れてしまっています。

マウスポインターをC列とD列の間に置き左右の矢印になったらドラッグではなくダブルクリックします。そうするとセル幅は最大の文字幅(数値の桁数)に合わせて広がってくれます。(セルの最適幅)

見出しの幅が広すぎて少し不格好です。セル幅に収まらない文字列の表示を参照してください。セルの書式設定の配置で『縮小して全体を表示する』か『折り返して全体を表示する』でも構いません。

複数の列に対して最適幅を設定する場合は、複数列を選択し複数選択した列の境目でマウスポインターが左右の矢印になったらダブルクリックします。

選択範囲内の列の境目で左右の矢印になるところだったら、どこでもいいのでダブルクリック。

すべての列が最大の文字幅(数値のけた)が切れない幅(最適幅)になりました。この時ダブルクリックではなくドラッグすると選択列すべてドラッグされた列幅と同じ幅になります。

グラフ 目盛り線 (折れ線グラフ)

折れ線グラフの様に縦と横に目盛りがある場合でも通常メモリの線は縦軸に対してのみ入っています。横軸の目盛り線を表示します。

グラフツールのデザインタブのグラフのレイアウトのグラフ用を追加の右の下向き三角▼をクリックします。

目盛り線の第1主縦軸をクリックします。

横軸に対する目盛り線が表示されました。今回のグラフは縦軸が数値なので縦軸に対してはさらに補助線が引けますが、横軸は項目なのでこれ以外細かい補助線は入れられません。

各月のマーカーが今表示した目盛り線のそれぞれ間にありますが、縦線の上に持っていきたい時は、横軸の書式設定を変更します。

これは折れ線グラフ3(グラフの開始位置)と同じなのですが、横軸を選択し、右クリックで軸の書式設定をクリックします。軸のオプションの軸位置をメモリの間から目盛に変更します。

目盛り線の下に項目名が表示されて、各マーカー位置が目盛の上に来ました。これを棒グラフでやると端が切れてしまいますので注意してください。

縦横の目盛り線が表示されたら補助線でできる矩形をどうも正方形にしたがるようですが、折れ線の見た目の傾斜が変わりますのでほどほどにしておいてください。