067 安全に重複データを削除する～COUNTIF関数の応用１

　リストなどを管理する上で、データの重複はきれいにしておかねばなりません。
Ver.2007以降のExcelには「重複の削除」という機能が追加されましたが、これを使うのは避けましょう。実際、ある企業でこの機能を使ったところ、データ内でバグが発生し、無関係なデータまで消えてしまいました。安全な重複削除の方法の一つが、COUNTIF関数です。

　通常のCOUNTIF関数の使い方では、
1．B2セルに「=COUNTIF(A:A,A2)」と入力する

2．データ最下端行までドラッグコピーする

　A2セルの「A-194」は3件重複していることがわかりますが、重複を削除するためには、3件のうち2件を消し込むという作業をしなければなりません。しかし、この作業は一括ではできないのです。

　COUNTIF関数を少し工夫すると、該当データが何番目に出現したかをカウントし、一気に重複削除が可能になります。

1．B2セルに「=COUNTIF($A$2:A2,A2)」と入力する


2．データ最下端行までドラッグコピーする

3．オートフィルタで「2」以上の数を抽出、対象セルを削除する

　ポイントは、第一引数のセル範囲を指定するとき、始点にのみ絶対参照を付けること。下方向に数式をドラッグコピーすると、B3セルでは「=COUNTIF( $A $2:A3,A3)」、B4セルでは「=COUNTIF( $A $2:A4,A4)」と検索範囲が常に上のデータを見て広がっていくイメージ。そのため、該当データが2番目以降に登場した箇所がわかり、一括で削除できます。

1万人の業務効率を劇的に改善したExcel速技BEST100/PHP研究所

￥1,836

Amazon.co.jp

Excelによる文字の自動修正をさせない方法

Excelによる文字の自動修正をさせない方法

Excelには入力した文字を自動的に修正してしまう機能があります。

場合によっては便利ですが意図しない修正がされてしまうと非常に煩わしいものになってしまいます。

こうした機能のカスタマイズ方法を見てみましょう。

この自動修正機能は「オートコレクト」と呼ばれるものです。

「ファイル」タブ→オプション→「文章校正」→「オートコレクトのオプション」とクリックしていくと次の画面が出てきます。

「オートコレクト」タブで次のような修正を行うかどうかを、チェックの有無によって設定することができます。

・2文字目を小文字にする
英単語で先頭のみ大文字にするつもりが間違って2文字目も大文字で入力してしまったのを修正してくれる機能

・文の先頭文字を大文字にする
英文は通常、先頭文字は大文字で書くため、小文字で英単語を入力し始めた際に先頭文字を大文字に修正する機能。これは個人的には私は必要ないです。単語を一つ書くだけの場合など全部小文字で入力したいケースもありますので。

・曜日の先頭文字を大文字にする
これはあるといいと思います。曜日は通常、先頭文字は大文字で書くものなので、小文字で入力してしまったのを修正してくれるものです。

・Caps Lockキーの押し間違いを修正する
Caps Lockしている時にShiftキーをそもそも押すかという問題はあります。

・入力中に自動修正する
これは便利な場合とわずらわしい場合があります。どちらかというと評判が悪い機能です。

「hsi」と入力するつもりそう入力したのに、勝手に「his」と修正されてしまうというケースがあり、これは確かに腹立つでしょうね（笑）。「間違えたよね」って言われてるようなものですから（笑）。

こうしたイライラも、Excelのオプションを探すことで解除できることがたくさんあります。困った時は是非探してみてください。

たった1日で即戦力になるExcelの教科書/技術評論社

￥1,922

Amazon.co.jp

063 SUMIF関数で集計条件が複数ある場合どうするか

　担当者ごとの集計はSUMIF関数で行いましたが、担当者・商品別の集計を行うにはどうすればよいでしょうか？　SUMIF関数は、条件となる列を1列だけ見るのが特徴。ゆえに追加条件となる列を1列で作り、付け足せばよいのです。これらは俗に「作業列」や「計算セル」といわれます。「必要なデータは作る」。この発想ができると、Excel仕事はとてもラクに、そして快適になります。
　集計するための作業列を作ります。

・SUMIF関数を使う

１．G列に担当者と商品コードをつないだ作業列を作る
　G2セルに「=B2&C2」と入力し、データ最下端行までドラッグコピー


２．J2セルに「=SUMIF($G:$G,$I2&J$1,$E:$E)」と入力
　第二引数の担当者名には列のみ、商品コードには行のみ絶対参照を付けるのがポイント

３．式をO6セルまでコピーする

　作業列は、基本的に表の右側に追加していくこと。毎週・毎月のように同様の集計作業が発生するのであれば、図の表の場合、元データをA ～ F列に貼り付けるだけで作業が完了します。事前に運用方法を考えながら表を作ることで、ルーティンワークが1分もかからずに終えられるようになります。

　Ver.2007以降使えるようになったSUMIFS関数を使えば、作業列を作らずに集計することも可能です。

SUMIFS関数の書式は以下の通りです。

　第一引数　合計したい範囲
　第二引数　条件となる範囲1
　第三引数　条件１
　第四引数　条件となる範囲2
　第五引数　条件2　………

・SUMIFS関数を使う
J2セルに「=SUMIFS($E:$E,$B:$B,$I2,$C:$C,J$1)」と入力し、式をO6セルまでコピー

　SUMIFS関数では、最大127組まで条件を指定することができますが、集計条件が増えれば増えるほど、引数も増えてわかりづらくなるので、あまりおすすめしません。同様にSUMPRODUCT関数を用いた集計も可能ですが、非常に難解です。
　難解な表は、メンテナンスがしづらく仕事の引継ぎに支障をきたすことがあります。チームのスキルレベルを考え、適切な関数を選定することが大切です。

1万人の業務効率を劇的に改善したExcel速技BEST100/PHP研究所

￥1,836

Amazon.co.jp