売上や顧客情報といったデータの集計や管理に欠かせないExcel。
しかし、そのデータが大量になるとどうでしょうか。
「内容が重複している」というミスが起きやすくなるうえ、作業確認や修正に時間が取られがちになるものです。
最近では、作業効率化に向けた外部ツールが登場していますが、柔軟性が低いという懸念がぬぐえません。
そこで今回は、大量データ集計を効率化するための便利なExcelのワザをご紹介します。
月10時間、月額2.5万円からはじめよう。一人社長なら「タスカル」をチェック！
- 月額2.5万円～、業務量に応じて選べる３プラン
- 最短即日スタート・マニュアル不要
- 採用・教育コスト0円
重複したデータを削除する方法
大量の入力作業でついありがちなのが、すでに入力済みの情報をさらに入力してしまう重複入力です。
データに重複があると、
- 「DMを二重に送付してしまう」
- 「ファイルサイズが大きくなる」
- 「正しい分析ができない」
このようなデメリットが発生してしまいます。
重複していないかデータを目視で確認するとなるとデータ量に比例して膨大な時間がかかるだけでなく、
見落とすことも考えられます。そこで便利なのが、「重複の削除」機能です。
「重複の削除」機能
顧客リストを例にしてみましょう。
ここでは赤枠で囲っている山岡一尊さんが、重複しているデータです。
表のセルを選択し、データのタブから「重複の削除」をクリックします。
すると、何を削除するのか指定するための画面が出てきます。
今回は重複する行全体を削除したいので「すべて選択」を選びます。
そのままOKを押すと、先に入力されたデータを残して
重複して入力された山岡一尊さんのデータが削除されました。
目視と違って見落とすことがなく、確認&修正作業がほんの数秒しかかかりません。
重複したデータを確認する
ただし、重複の削除機能には困った点があります。
というのも、何が重複しているかがわからないのです。大量データ集計では困ってしまいます。
そこで、何のデータが重複しているのか調べたいときに便利なのが「COUNTIF関数」です。
COUNTIF関数は本来、エクセル内の条件にあてはまるセルを数えるための関数ですが、この関数の考えを応用すると複数回登場したデータがわかるということになります。
こちらは、COUNTIF関数を使って名前の登場回数をＪ列に登場させたものです。
重複している山岡一尊さんのデータだけ２と表示されているのがわかります。
では、複数回（＞1）登場した場合に（重複あり）、そうでない場合に空白（””）とさらに条件（IF）を追加して表示させてみましょう。
数式は次のようになります。
IF(COUNTIF(A:A,A2)>1,”重複あり”,””)
複数回登場している山岡一尊さんの行に「重複あり」と表示されました。
データの登場回数をすべて表示させるだけより、どこが重複しているかがグッとわかりやすくなりましたね。
COUNTIF関数はとても便利な関数です。
アンケート集計などにも役立つので、ぜひ活用してみてください。
表を使いまわす方法
業務予定表や勤怠管理表、請求書に見積書、売上の一覧表など、
日頃の業務でExcelを使っていると似たような表を作成する機会は多いのではないのでしょうか。
こういった場面では、表を使い回すことで作業の効率化を図りましょう。
以下に2通りの方法をご紹介します。
1.数式を生かす
こちらは、数値を削除して数式はそのまま生かす方法です。
次からの作業は数値を入力するだけ。それで表が完成します。
グッと作業時間が減らせることが想像できるでしょう。見積書や請求書などの作成時に活躍する方法です。
では、数値をどのように削除するのでしょうか。
Delete（デリート）キーで１つずつポチポチ消していく…？
まさか、そんなことはありません。まずは数値の削除の仕方から覚えましょう。
例として見積書のサンプルで見てみましょう。
ここの表では金額の欄に数式が入っています。
数量と単価だけを数値を消すには、右端にある「検索と選択」から「条件を選択してジャンプ」をクリックします。
すると、削除したい数量と単価のみ選択されます。
あとはDeleteキーを押せばワンクリックで数値だけが削除されます。
数値が削除されたことで関数は残したまま金額欄は自動的に空欄になります。
この表の場合、合計を算出するための計算式が入った合計金額も自動的に空欄になります。
この方法なら間違って数式を消してしまう心配がありません。
表データの再利用も簡単に行えるというわけです。
2.シートをコピーする
こちらは、売上一覧など月ごとにシートを分けて同じような表を作成する場合、
あるいは元データを複製して加工データを作りたい場合などに便利な方法です。
コピーしたいシートタブを選択したら、Ctrlキーを押しながら右にドラッグします。
書類マークのようなアイコンが現れたら手を放しましょう。
これでアイコンが出た場所にシートがコピーされます。
回数に制限はないので、必要な分だけドラッグでコピーできます。
他のファイルにコピーしたい場合は、シートタブを右クリックして「移動またはコピー」を選択します。
「移動先ブック」名からコピーしたいファイル名を選びましょう。
新規ファイルにコピーしたい場合、「新しいブック」を選びます。
ファイルを指定したら「コピーを作成する」にチェックを入れます。
これで他のファイルにシートがコピーされます。
「移動またはコピー」で同じファイル内にコピーすることもできますが、
先にご紹介した「ドラッグでコピー」する方が早く行えます。
ここまで代表的な２通りの方法をご紹介しましたが、どちらかにこだわる必要はありません。
場面によっては、2通りの方法を合わせることもあるでしょう。
3.一度で複数のシートに入力する
さてもう一つ、知っておくと便利な効率化のワザのご紹介です。
複数のシートに同じ内容の表またはデータの入力が必要な場合、どうすればよいのでしょうか。
無駄なく、一度で入力できる方法を知っておきましょう。
まず、必要な分だけシートを用意しましょう。
シートを用意できたら、Ctrlキーを押したままタブを選択していくとグループ化されます。
タブを見てみると、複数のシートが選択されているのがわかります。
タイトルバーのファイル名の横には[グループ]とも表示されます。
このグループ化された状態で入力していくと、他のシートにも同じ内容が同時に入力されていくというわけです。
これならコピペをする手間がなくなりますね。なお、タブのどれかをクリックすればグループ化は解除されます。
「2.シートをコピーする」で紹介した内容と似ていますが、状況に応じて効率の良い方法を選んでみてください。
まとめて置換する、一括入力する方法
たとえばデータの中に（株）と株式会社が混在しているなど語句を統一したい場合、
または同じ語句を一括入力したい場合にも効率良く処理できる方法があります。
Excelの大量データ集計において、ぜひこの方法は覚えておきたいものです。
1.まとめて置換する方法
語句を統一するということは、Excel的に考えるとまとめて置き換える（置換）ことです。
（株）を株式会社にしたい場合で見てみましょう。
「検索と置換」から置換を選択します。
「置換」のタブをクリックして「検索する文字列」に置き換える前の語句を
「置換後の文字列」に置き換えたい語句を入力したら「すべて置換」ボタンをクリックします。
このデータ上では2件の表記が置き換えられました。
とても便利な機能ですが、置換で対応できる語句は一種類のみです。
株式会社の表記は、㈱や㍿と入力されてしまうことがあります。
環境依存文字は文字化けしやすいことからも直しておきたいのですが一種類ずつ直すのも手間ですよね。
そんなときには「SUBSTITUTE関数」が便利です。
2.数種類ある表記の置換には関数が便利
まず、関数で置換するための行を追加します。
計算式は「=SUBSTITUTE(対象セル,検索文字列,置換文字列,置換対象)」で（置換対象はここでは省略します）
=SUBSTITUTE(B2,”（株）”,”株式会社”)となります。
文字列はダブルクォーテーション（”）で囲みます。
はじめのセルに関数を入れたらあとはドラッグで関数をコピー&ペーストできます。
しかし、この計算式では先ほどと同じ、一種類の用語が対象です。
複数表記があれば、その数だけ関数を追加します。
（株）も㈱も株式会社にしたい場合、計算式は次のようになります。
「=SUBSTITUTE(SUBSTITUTE(B2,”㈱”,”株式会社”),”（株）”,”株式会社”)」
関数の中に関数を入れる形になっているのですが、
㈱を先に置換してから（株）を置換するという指示になっています。
さらに㍿を追加したい場合、
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,”㍿”,”株式会社”),”㈱”,”株式会社”),”（株）”,”株式会社”)
という記述になります。
これですべての株式会社という表記になりました。
関数が苦手ならひとつずつ置換すると確実です。
3.一括入力する方法
ここでは、空白欄にまとめて入力する方法をご紹介します。
データ内に空欄が残っていると作業者以外にはなぜ空欄なのかわからないことがあります。
例として下の表の空欄に未集計という語句をまとめて入れてみましょう。
表のどこかセルを選択した状態でCtrl+Aで表が全選択されます。
その状態で「検索と選択」を選び、「条件を選択してジャンプ」とクリックします。
選択オプション画面が出てきたら「空白セル」を選択し、OKを押しましょう。
すると空白セルがすべて選択されます。
全選択の状態で未集計と入力し、Ctrl+Enterを押すとすべての欄に未集計と一括入力されました。
Ctrl+Enter自体、一括入力のショートカットキーですから覚えておくと便利です。
忙しい経営者にはExcel作業のアシスタント移行を
業務が稼働している以上、Excelデータは日々蓄積していくものです。
今回はExcelの大量データ集計について効率化のためのワザをご紹介しましたが、
「Excel作業をする時間そのものが惜しい」というような多忙な経営者の方には、Excel作業のアシスタント移行をおすすめします。
現在、オンラインアシスタントのサービスは国内で40以上あります。
タスカルもそのうちのひとつですが、
「初期費用がかからない」「月額2.5万円~の少額スタート」と
手軽に導入できるのが強みです。
細かく比較検討するための資料をご用意しましたので、ぜひご覧ください。
