月別アーカイブ: 2017年7月

重複データの削除

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

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

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

重複の削除のダイアログボックスが表示されます。スクロールで隠れていますが列の見出しが列挙されており、チェックが入っています。これらは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(グラフの開始位置)と同じなのですが、横軸を選択し、右クリックで軸の書式設定をクリックします。軸のオプションの軸位置をメモリの間から目盛に変更します。

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

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

表示されているデータをコピー (可視セルの選択)

元の表から不要な行や列を覗いたものを別の表にしたいことがあるかもしれません。元の表を変更してしまってよいなら不要な行や列を削除すればいいのですが、そうもいかないこともありますね。

フィルターで絞り込めるような状態だとそのままコピーペーストでうまくいきます。

フィルターをかけて支店の列で大阪支店のデータのみを抽出してから範囲選択しコピーのボタンをクリックします。

コピーした範囲が破線で囲まれていますが行が連続していないので行ごとに囲まれています。

別のシートに張り付けるとフィルターで抽出されたデータのみが貼り付けられました。

フィルターでうまく抽出できないデータだった場合はちょっと困ります。この表で仲のいい人だけ別の表にするとか、先輩だけをとか、先輩後輩列があったり、友達列があればなんとかなりますが。

このような場合、不要な行は非表示にすると思います。行列の非表示は5行5列のシートを作成を参照してみてください。

何らかの基準で行や列を非表示にして別の表にしたいデータのみ見えるようにしてあります。行番号や列番号がとびとびになっています。

このデータをコピーして貼り付けます。

コピーしたときの選択の枠が1つになっています。これを張り付けます。

D列は選択されていなかったので貼り付けられませんでしたが、行は非表示にした行も張り付けられてしまいました。

これは、ドラッグして選択した時点で非表示になっているデータも選択されていたということです。非表示行を含まないように選択できればうまくはりつけられますが、1行ずつ選択するのも面倒です。

いったん非表示行も含めて選択しておきます。次にホームタブの編集の検索と選択の右の下向き三角▼をクリックします。ジャンプをクリックします。

ジャンプのダイアログボックスが表示されます。セル選択のボタンをクリックします。

選択オプションの可視セルにチェックを入れてOKボタンをクリックします。

この状態でコピーします。すこしわかりづらいですが、行が連続していないところは少し白くなっており、個別に行が選択されていることが分かります。コピーボタンをクリックすると個別に選択の破線がつきます。

あとは、貼り付けるだけです。うまく張り付けられたら元の表の非表示行や列を表示しておきます。

 

フィルターオプション 数式

フィルターオプションフィルターオプション ワイルドカード等、とやりました。今回は検索条件に数式を使います。

これまでも=”=みかん”などと式を使ってきましたがこれらは比較演算子という分類になります。
= (等号) 左辺と右辺が等しい
> (より大記号) 左辺が右辺よりも大きい
< (より小記号) 左辺が右辺よりも小さい
>= (より大か等しい記号) 左辺が右辺以上である
<= (より小か等しい記号) 左辺が右辺以下である
<> (不等号) 左辺と右辺が等しくない
これらになります。IF関数の論理式に使われますね。

今回は比較演算子だけではなくほかの計算式でフィルターオプションの検索条件を作ります。

以下のような表で計が平均以上の商品をフィルターオプションで抽出したいと思います。

F列からI列までに検索用の見出し、L列からO列までは抽出用の見出しが用意されています。まず、計のの平均を元の表の最下行28行目に算出しておきます。

これでセルD81に計の平均が出ましたので抽出見出列 計の下に=”>=d81″でうまくいきそうです。

ところがこれがうまくいきません。セル参照がうまくいっていないようです。=”>=d81″のd81の代わりに481という数値ならうまくいきます。

セル参照が入っている場合やプラスやマイナス等の計算式もうまくいきません。

  • 比較演算子以外の計算式を使う場合はダブルクォーテーションで括らないこと
  • 検索用の見出しは元の表の見出しを使わないこと
  • 計算式はTRUEまたはFALSEを返すこと
  • 左辺は元の表の見出しを除く一番上の行を使うこと

というような約束事があります。

セルJ2に=(D2>=$D$28)と入力します。これはイコールの右辺がTRUEかFALSEを返す(TRUEとFALSEを参照してみてください)式になっています。元の表のD列に対して評価しますので初めのD2は相対参照になりD28は絶対参照になります。絶対参照と相対参照参照

更にセルJ1には見出しは特に必要ありませんが条件式とかにしておくことが多いようです。

もちろんこれで終了でもいいのですが28行目に平均値を出していますが、数式の中で平均値を使うことも可能です。

28行目の平均を削除します。セルJ2の数式の$d$28の代わりにAVERAGE関数を使い平均を計算します。
=(D2>=AVERAGE($D$2:$D$27))
引数は元の表のすべての行に対してなので絶対参照にしてください。

他の行についても計算式を使うならJ列に追記してください。

少しややこしくなりますが、ここでつかる計算式はTRUEかFALSEをかえせばいいのですから、複数の条件を指定することが可能です。

計の値が最大かまたは、最小のものを抽出する式を入力します。
=(OR(D2=MAX($D$2:$D$27),D2=MIN($D$2:$D$27)))

この式はOR(A,B)という形でAかBならTUREそうでなければFALSEを返します。Aの部分がMAX関数でセルD2:D27の範囲の最大値、Bの部分がMIN関数でセルD2:D27の範囲の最小値を表します。つまり、最大値か最小値と等しかったらTUREになりますので計の(セルD2)の最大値と最小値を抽出します。 MAX関数 MIN関数 AND関数 OR関数も参照してみてください。

このように複数の条件を1つの式にまとめると1つで、計の最大と最小が抽出されます。

オートサムボタンの横関数 COUNT関数 MAX関数 MIN関数

オートサムのボタンの右に下向き三角がありそこには合計、平均、数値の個数、最大値、最小値、その他の関数と6つあります。合計はSUM関数、平均はAVERAGE関数を参照してください。その他の関数はこれら5つ以外のすべてなので、数値の個数、最大値、最小値の3つの関数をご紹介します。

MAX関数(最大値)
一連の引数のうち、最大の数値を返します。
=MAX(数値 1, [数値 2], …)

MIN関数(最小値)
一連の引数のうち、最小の数値を返します。
=MIN(数値 1, [数値 2], …)

フィルターオプション ワイルドカード等

フィルターオプションよりもう少し細かい使い方をご紹介します。

今回は、抽出先を指定した範囲として常に元の表はそのままで抽出結果を別の場所にします。

以下のような表で商品名がみかんのデータをフィルターで抽出します。

セルF1に元の表の見出しと同じ商品名という見出しを入力します。見出しの下に=”=みかん”と入力します。

元の表のセルをクリックしデータタブの並べ替えとフィルター詳細設定をクリックします。フィルターオプションの設定が表示されますので検索条件範囲にセルF1からセルF2をドラッグして指定します。(自動で$マークの付いた絶対参照になります。)

抽出先の指定した範囲を選択しセルk1をドラッグで指定します。オプションの設定のOKボタンをクリックします。

元の表の見出しを含めて、商品名がみかんのものが抽出されます。

次に抽出先を少し変更します。セルk1とセルk2だけ残して抽出先のデータをいったん削除します。

この状態でサイト商品名がみかんのものだけ抽出します。フィルターオプションの設定で、抽出先を先ほどの2つの見出しセルK1からセルL1のセルをドラッグで選択します。OKボタンをクリックします。

指定した見出しのデータのみ抽出されます。抽出先の見出しの並び順は元の表通りでなくても大丈夫です。

通常のフィルターの場合同じ項目(列)で2つのAND条件が設定できました。例えば単価が100円以上200円未満だとかです。フィルターオプションは横にAND条件縦にOR条件になります。複数のANDの場合は、列見出しを複数作ります。

単価が100円以上、200円未満の場合は検索条件範囲に単価を2つ作ります。

セルF1には=”>=100″、セルG2には=”<200″と入力します。検索条件範囲セルF1からG2として、抽出先範囲はいったんデータを削除してから任意のセル(セルK1)を選択します。

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

単価のAND条件がうまくいきました。

文字列の抽出でワイルドカードを使う

フィルター テキストフィルターでも出来たように任意の文字、または、文字列を表す

* 任意の長さの文字(0文字以上)
? 任意の1文字

をワイルドカードと言ったりします。

コーヒーやチョコレートなどの伸ばす長音を含む商品だけを抽出する場合は

=”*ー*”

という風に指定します。*は0文字以上何文字でもいいので『ー』が商品名に含まれていれば該当します。

=”*ん”の場合は『ん』で終わっていれば抽出されます。『ん』一文字でも該当します。『みかん』は該当しますが、『こんにゃく』は『ん』で終わっていないので抽出されません。

商品名で「=”*に*”」とすれば何が抽出されるでしょうか?

『こんにゃく』と『にんにく』が抽出されました。『に』を含んでいればいいので今回の表にはありませんが、『にんじん』や『かに』なども抽出されます。

分かりやすいようにそれぞれ別個に指定しましたが、複数行組み合わせて商品名が『みかん』で価格が『100円以上』『200円未満』で数量が『5以上』か(ORで別の行に)商品名に『に』を含むもののような使い方ができます。