カテゴリー別アーカイブ: マイクロソフトエクセル

重複データの削除

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

非常に便利です。

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

セルの最適幅

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

セルのデータ、書式、コメント、罫線など一気に削除

書式の設定されたセルのデータはデータだけを削除しても書式は保持されています。

下のエクセル画像のセルA1は現在空白ですが、文字サイズ、文字色、書式で日付が設定されています。試しに10000と入力してみます。

まだエンターキーで確定していませんが、すでに文字サイズが大きく、文字色が赤です。

エンターで入力データを確定すると10000と入力したのに1927年5月18日と表示されました。数式バーにも10000という数値は残っていません。10000は1900年1月1日から10000日目なんでしょうね。

通常の文字サイズで文字色も通常の黒、10000と入力したら、日付ではなく10000と表示されるようにするためには、設定された書式をひとつずつ戻す方法もありますが、何が設定されてるか全部調べるのも面倒ですし、それをいちいち初期値に戻すのも大変です。

ボタン1発で文字通りすべてクリアします。

セルA1には0.01と入力されているのに00.011111のような表示になっています。計算や文字色、背景パターン、コメントなど様々な書式が設定されています。

すべてクリアしたいセルを選択しホームタブの編集のクリアのすべてクリアをクリックします。

データ、書式設定、コメントなど文字通りすべてクリアされました。

データ(数式)ごと全部クリアしてよい場合とても便利です。これ以外にも、書式だけのクリアや、書式を残してデータ(数式)のクリアなどもあります。

数式以外のセルのデータを一括削除

セルのデータの削除方法についてです。

エクセルのセルに入力されているデータを削除したい場面はいくつもあると思います。しかし、数式が入力されているセルを削除してしまうと困ったことになることがあると思います。

自分がすべて作成したのならどこに数式が入っているか、どこのセルの値は消してもいいかは分かりやすいでしょうが、人が作ったエクセルシートはどこのデータを消してよいのかすぐには判断つきにくいと思います。

数式が入力されていないセルのデータだけを簡単に削除できれば便利だと思います。下の図は請求書からダウンロードできるエクセルブックです。

再利用しようと思ったときはどこを消せばよいでしょうか。日付や請求書番号、相手先のご住所や会社名などを、変更しますね。ここは文字列なので遠慮せずに削除して下さい。

表の部分は計算式が入っていますので慌てて上書きしてしまうと焦ってしまいます。この表の場合は文字列に見える商品名の列はVLOOKUP関数で商品番号から自動で表示していますので、商品名を消してしまいますと商品名を手入力しなければいけなくなります。

そうしますと、データを削除するときにいちいちセルを選択して数式バー数式が入っているかどうかを確認したのちにしかデータを削除できません。

数式が入っていないセルだけを選択する方法をご説明します。まず、データを消したいエリアを選択します。見出し列(商品番号や商品名)は文字列ですが、変更する必要はありませんね。小計や消費税、合計はおそらく計算式で自動計算と思われます。

変更したいデータのセルと、計算式の入っているセルをまとめて選択した状態です。ここから、計算式ではないセルだけを選択します。

ホームタブの編集の、検索と選択をクリックします。ジャンプをクリックします。

ジャンプのダイアログボックスが表示されます。

セル選択のボタンをクリックします。

定数にチェックを入れてOKボタンをクリックします。

これで数式以外のデータが入力されているセルが選択できましたのでDELETEキーで削除してください。

ダウンロードしていただけるサンプルには名前ボックスでクリアを選択していただければ商品番号の列と数量の列は自動で選択されます。

seikyusyo_6

色々な物の非表示

だからどうだということは全くありませんが、エクセルではいろいろなものを非表示にすることが可能です。

上の画像はエクセルです。一瞬何かわからないかもしれません。

  • リボンを非表示
  • 数式バーを非表示
  • 行見出し列見出しを非表示
  • 行と列を非表示
  • スクロールバーの非表示
  • 罫線を非表示
  • シート見出しを非表示

にしてあります。

 

リボンの非表示と数式バーの非表示はエクセルに対する設定ですので他のファイルに対しても有効です。(別の環境のエクセルでは非表示にしていないとなりません)

それ以外はファイル対する設定になります。

不要な行と列の非表示は5行5列のシートを作成を参照してください。Ctrlキー + Shiftキー + 下矢印キー (列の時は右矢印)がポイントです。

更にリボンを非表示にするを参照していただければリボン非表示にできます。ただし、これら以外の非表示はファイルタブのオプションから操作しますのでリボンの非表示は最後にしてください。

ファイルタブをクリックしオプションをクリックします。詳細設定をクリックします。

表示の下に数式バーを表示するがあるのチェックを外します。

次のブックで作業するときの表示設定の水平スクロールバーを表示する、垂直スクロールバーを表示する、シート見出しを表示するのチェックを外します。

次のシートで作業するときの表示設定で行列番号を表示する、枠線を表示するのチェックを外します。

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

以上で上のようないろいろなものを非表示にしたエクセルが完成です。

 

エクセル おすすめショートカット10個(本当の基本編)

エクセルを使うときに覚えておけば便利なキーボードショートカットを選んでみました。独断で選びましたがよろしくお願いします。すべてのエクセルのバージョンでは試していませんが、昔から使っているものばかりなのでwindows版のエクセルなら大丈夫だと思います。

ぜひとも覚えていただきたいショートカットトップ10

1位 元に戻す Ctrl + z
操作を失敗したときや、前の状態の方がよかった場合などに使ってください。クイックアクセスルールバーにあるボタンと同じです。

2位 やり直す Ctrl + y
元に戻すとペアで覚えてください。元に戻しすぎた場合やり直すでそのまえのじょうたいにもどります。

3位 コピー Ctrl + c
エクセルに限らずワードでも使えますし、コピーのボタンがないところでも使えることが多いです。いろいろなソフトでもCtrl + cがコピーに割り当てられているようです。

4位 貼り付け Ctrl + v
コピーしたものを張り付けます。コピーとワンセットで覚えてください。

5位 切り取り Ctrl + x
これもセットに入れておいてください。ちょうどキーが3つ並んでいますのでcopyのcを中心に3つです。

6位 すべて選択 CTRL+ a
すべてのセルを選択します。データをすべて削除するとかコピーするとかの場合便利です。

7位 直前のコマンドの繰り返し F4
直前と同じ操作をする際に使います。例えば離れたセルを同じ色で塗りつぶす場合などセルを選択しては塗りつぶしボタンをクリックするのは面倒ですね。そんな時、2回目以降はセルを選択してからF4キーをたたきます。使えないコマンドもあるようです。

8位 保存 Ctrl + s
何かの事情で強制終了してしまうと前回保存したところまで作業が戻ってしまいます。こまめに保存してください。新規保存時以外は上書き保存になります。

9位 書式設定 Ctrl + 1
右クリックからセルの書式設定でも構いませんが、マウスを持っていない場合便利です。

10位 右クリック Ctrl + F10 またはアプリケーションキー
キーボード操作中右クリックしたい場合、マウスを持ちなおさなければなりません。これを覚えておくと便利です。アプリケーションキーはキーボード右のCtrlキーの左隣にあるはずですが、ノートパソコンなどアプリケーションキーがないものもあります。

いかがでしたでしょうか。すでに全部知っているし使っているという方も多かったのではないでしょうか。7位の直前のコマンドの繰り返しと9位の書式設定以外はほとんどのアプリケーションで使えると思います。パソコンの共通ショートカットに近いものですね。

またの機会に別のショートカットもご紹介する予定です。

文字列なのに数値と認識される

わかりずらいタイトルですね。自分もあとで困る気がします。

よくあるのが1-2のような表示にしたいのでそのまま入力すると日付になってしまうという分です。

このまま表示してくればいいのですがEnterキーで確定したとたん日付の表示になってしまいます。

入力したのは1-2なんですが表示は1月2日となっています。さらに値も2017/1/2と日付になってしまっています。こうなってしまうと入力した1-2という値は保持されていません。

1-2と表示させたい場合は入力前に表示形式を文字列に変更してから1-2と入力してください。

ホームタブの数値の数値の書式から文字列を選択したのちに1-2と入力します。

文字のつもりで入力したら違う表示になってしまった場合はセルの書式を文字列にしてから入力しなおしてください。

1-2が1月2日と表示される以外にこのようになるパターンとしてはスラッシュ(/)を入力したら日付になるパターンなどもあります。

ハイフン、スラッシュ、コロンが日付、時間になるパターンですね。あとは午前、午後のAとMもあります。午前午後は0から12までの数値の後スペースAまたはPの形です。AとPは小文字でも構いません。

指数表現の数値e数値のパターンもあります。

 

商とあまり 負の数の除算

商とあまりを1つのセルに1発で表示する関数は残念ながらありません。

商とあまりを個別に計算して1つのセルに表示するしかないと思います。まずは、商の部分を計算してみます。

10割る3を計算すると答えは3.33333333という循環小数になります。今回は余りを出しますので小数点以下不要です。

使える関数としては
ROUNDDOWN関数、INT関数、TRUNC関数、QUOTIENT関数なんかが挙げられると思います。私の環境では、QUOTIENT関数がそのまま使えましたが開発タブを表示しないと、QUOTIENT関数は使えないかもしれません。

QUOTIENT関数は除算の商の整数部を返します。 商の余り (小数部) を切り捨てる場合に、この関数を使用します。
ROUNDDOWN関数は数値を指定された桁数で切り捨てます。
INT関数は指定された数値を最も近い整数に切り捨てます。
TRUNC関数は数値の小数部を切り捨てて、整数または指定した桁数に変換します。

ではエクセルで実際に比べてみたいと思います。

それぞれのセルには以下の数値、数式が入っています。

A2   10
B2     3
C2    =A2/B2
D2    =ROUNDDOWN(A2/B2,0)
E2    =INT(A2/B2)
F2    =TRUNC(A2/B2,0)
G2    =QUOTIENT(A2,B2)

ROUNDDOWN関数、INT関数、TRUNC関数、QUOTIENT関数いずれも同じ答えが出ています。(FLOOR関数忘れてましたね。時間計算3 FLOOR 関数四捨五入、切り上げ、切り捨て ROUND関数 ROUNDDOWN関数 ROUNDUP関数 TRUNC関数 INT関数

この表の割られる数(被除数)を9,8,7と減らしてみます。

INT関数だけが答えが違うところがあります。被除数がマイナスになってからほかの関数と答えが違ってきましたね。

では、商の部分はINT関数を使うかそれ以外の関数を使うかということで先にあまりに進みます。

MOD関数(エクセルでじゃんけんゲーム MOD関数)を使って余りを計算します。

H列にあまりを計算する数式
=MOD(A2,B2)
を入力しました。

余りが規則正しく繰り返されていますね。

これで答えが出たようです。検算しますね。2行目は10を3で割ったら何あまり何ですか。ということなので

10 ÷ 3 = 3あまり1

10割る3は3あまり1ですね。割る数の3と商である3を掛けて余りの1を足してあげれば10になりますよね。ほかの行、割られる数が負の場合を見てみましょう。被除数がマイナスの時はINT関数の答えはほかの関数の答えと違っていましたね。

被除数が-10の時を見てください。

-10 ÷ 3 =-3あまり2
-10 ÷ 3 =-4あまり2

の2通りの答えが出ているように思います。どちらが正しいのでしょうか

-10 ÷ 3 =-3あまり2 検算 3 × -3 +2 = -7
-10 ÷ 3 =-4あまり2 検算 3 × -4 +2 = -10

なので-10 ÷ 3 = は-4 あまり 2 となります。なんかしっくりこないような気がします。計算結果はあってますが、-10 の中に 3 がマイナス4回あるのか?ってとこですよね。日常の感覚から言えば-10 の中に 3 がマイナス3回あるので 3 × -3で-9になり、あと-1を足してあげればいいと思いますね。

-10 ÷ 3 =-3あまり-1 検算 3 × -3 -1 = -10

これが納得いきそうな答えに思えます。ということはMOD関数がおかいしいのでは?巻子のヘルプには以下のように書かれています。