実務スキル

エクセルを利用して分析や報告書を作成する際に意外と手間取る絶対値の計算方法について解説する。例えば、ビジネスシーンでは目標値との乖離を分析するなどの絶対値を計算上求めることがある。そんな時に利用する関数について解説する。
また、絶対値で平均値・最大値・最小値・合計などを出す方法についても解説するので必要があれば確認して欲しい。

なお、絶対値とは０からの距離のことを指し。マイナス（ー）を取った数値のことを言う。

絶対値を計算する関数はＡＢＳ

絶対値を計算する関数はABSというもので、絶対値の英訳であるAbsolute valueの頭文字3文字を取った関数となっている。

（括弧）内の引き数はセルだけではなく数値、数式でも可能だ。戻り値は（括弧）内の計算結果の絶対値が返ってくる。

絶対値を使った平均値・最大値・最小値はひと工夫必要

絶対値の取り扱い方法は大して難しくないがひと工夫必要だ。直感的に考えられる各関数の引数にＡＢＳ関数を呼び出すのみの以下の計算式では正しく計算されない。

これは、ＡＢＳ関数の引数が「セルの範囲」ではなく「セルの指定」を求めているためである。これを受けて絶対値を求める用の新しい列を計算する方法もあるが、今回はそれをせずに平均、最大値、最小値を計算するセルの計算式のみを計算する方法を解説しよう。

解決策は簡単で計算式を入力後「Shift」＋「Ctrl」＋「Enter」を押すことで同様の結果にもなる。中をみてみると誤った関数が｛｝で囲まれている。このショートカットをせずに｛｝で囲むだけでは処理は実行されないので気をつけて欲しい。ショートカットとしてこれはCSE数式というもので、範囲指定されている計算を個別に計算するという命令をする関数だ。これにより、エラーになっていた関数が計算可能になる。

目標値から乖離した店舗売上の計算例

以下では各店舗の売上が目標値からどれだけ乖離するかを検討する例を通して前章「絶対値を使った平均値・最大値・最小値はひと工夫必要」の詳細について解説する。

上記の表の通り、誤った関数で作成したものと正しい関数で計算したもので計算結果に差が出ることに注意して欲しい。例えば、最小値の計算では誤ったほうでは20となっており、最も小さい-20が絶対値化された値となっている。しかし、ココでほんとにほしい数値は０であるはずなので必ず正しい「Shift」＋「Ctrl」＋「Enter」を利用しよう。

まとめ

・絶対値の計算はＡＢＳ関数
・ＡＢＳ関数で平均値、最大値、最小値を返す際は「Shift」＋「Ctrl」＋「Enter」を押すこと！

この2点に注意して絶対値を利用した計算をおこなって欲しい。

前回の「【コツ徹底解説！】エクセル データ整理・クレンジングの早ワザと失敗例①｜作業設計」では、膨大なデータをエクセルで整理する際に気をつけなければならないことについて触れた。データの内容を確認し、どのようなデータへ整理していくかの設計図を作成し、上司と握ることが重要と述べた。今回は実際のデータ整理においてどのような方法（エクセルの帰納や関数）を利用してアウトプットデータを作成していくか述べるものとする。

なお、本シリーズで説明する内容は筆者が考える最短でデータ整理をするために利用する方法論と全機能である。複雑な関数や機能を利用することで欲しいデータを作ることは可能かもしれないが頭を使ったり、試した結果できなかったりすることを考慮すると本稿で紹介した手法のみでデータクレンジングをやり遂げる気持ちで業務を遂行して欲しい。

エクセルでデータ整理を行う際に利用する機能と関数

前回整理するデータに葉以下の3種類のデータがあることを紹介した。まず、データの項目の内容を確認する際とそれらのデータを整理する際に利用するエクセルの機能と関数を対応させて紹介する。
・データの項目の内容を確認
ー重複削除
－フィルタによる降順並び替え
－区切り位置の指定

・重複データ
ーフィルタを利用したフラグ付け
ー重複削除

・Nullデータ
ーフィルタを利用したＮｕｌｌデータの書き換え

・異型データ
－一括置換
ー全角カナを半角カナに変換するASC関数
－文字データを数値データにするVALUE 関数
－【参考】表示形式の変更が反映されない場合に利用するＶＢＡ

重複削除

重複削除は「データの項目の内容を確認」と「重複データ」の整理の際に利用するエクセルの機能だ。機能の内容は文字通り、選択した範囲から同一のデータを削除する機能だ。これにより、データ項目に重複したデータがどれくらいあるかを把握し、実際に重複しているデータを消しこんでいくことになる。

それでは具体的な利用方法について確認していく。なお、以下同様の整理手法についても同様であるが、作業する際は必ず元データを直接編集しないようにして欲しい。元データを残しておかないと、作業に失敗した際など、元に戻れなくなる恐れがある。

重複削除機能は、以上の様にデータタブのデータツール内にある。
項目をクリックすると以下のようなポップアップが出現する。

この画面ではデータが重複している場合に一緒に消すデータの列を選択する画面だ。データ項目の内容の確認の場合は対象となる列のみを選択してやればいいので重複確認をしたい項目のみチェックしてその他全てのチェックをはずす。重複データ削除の際はレコードを同時に削除するので重複をチェックしたい対象となるカラムの列を選択しておこう。

こうして重複データを削除していく。

フィルタによる降順並び替え

フィルタによる降順並び替えは、「データの項目の内容を確認」で利用する機能だ。その他の作業でも実施作業が反映されているか確認する際に利用できる。内容は、フィルターをかけてあいうえお順や数字順に並べ替える機能だ。不規則なデータを一定の規則によって並び替えることでデータの規則性を把握しやすくなる。

使い方は以下の通りだ。
整理するデータ全体を選択し、以下のボタンを押す。フィルターボタンはデータタブの並び替えとフィルター内に存在する。なお、データの項目名（カラム）を設定せずに空白にしているとフィルターを正しく設定できないので、メモ的なカラムだったとしても何かしらの名前をカラムに命名しよう。

フィルタボタンを押すと、下の図のような▽マークがカラムにつくので並び替えをしたいカラムの▽マークをクリック。
すると、ポップアップが出てきて降順を選択しよう。

区切り位置の指定

区切り位置の指定は「データの項目の内容を確認」の際にひとつのデータになっているが分解すれば使えるデータを整理する際に利用する。カンマ区切りのデータやIDなどの采番規則内に年齢が含まれていることがわかった場合などに利用する。例えば、「男性,44歳,東京都」・「22114898020170912」(最後の8桁が日付になっている)などの場合だ。

利用法は対象となるセルを選択し、データタブのデータツールにある区切り位置をクリックする。

どの後、どのような区切り方をするかの以下のような画面が出てくるので、データに応じて設定をするだけだ。なお、分割されたデータは右側のセルにずれていくので、右側にデータの入ったセルがないような状態にして利用しよう。

なお、左右のデータのみの抽出であれば、RIGHT関数やLEFT関数、MID関数を利用したすることで大体可能だ。
利用方法は
＝RIGHT(対象となるセル,抜き出す文字数)
だけで完了だ。

フィルタを利用したフラグ付け

フィルタを利用したフラグ付けは、「重複データ」で利用する機能だ。そもそもフラグ付けとは作業者が認識できる目印のようなもので、例えば「男」で「50歳以上」の人にフラグをつけて管理しやすくしようとする場合などに使える。名寄せの際などに同姓同名だが違う人を見分けるフラグとして利用するのも効果的だ。

使い方は前項のフィルタと同様のなのでフラグ付けをメインに解説する。フラグの付け方は簡単だ。
以下の図のように対象となるもの1,0などのフラグをつければよい。まずは手作業でやる場合。

フラグ用のカラムを作り、先ほどの手順でフィルターをかける。
その上で、例えば40代の人を抽出するのであれば、▽マークがカラムを選択し上記図真ん中の図ように40代にチェックを入れる。

その上で、フラグのセルに１を入れていく。この際、データ量が多い場合コピー＆ペーストを使いたいが気をつけて欲しい。単純にコピー＆ペーストをするの消えているはずのレコードにも１が入ってしまうので以下の手順をはさんでコピー＆ペーストを実行しよう。

もうひとつの方法として関数を使う方法がある。利用する関数は最も有名なIF関数だ。
IF関数で必要な条件を立てて”１”、”０”などのフラグを立てるのも良いだろう。

フィルタを利用したＮｕｌｌデータの書き換え

フィルタを利用したNullデータの書き換えは、「Nullデータ」を整理する際に利用する機能だ。Nullデータがあると集計作業で問題が発生しやすいのでN/Aなどを入れるようにしよう。

利用方法は前項で説明した「フィルタを利用したフラグ付け」のフラグと同様のやり方でフラグの代わりにＮ/Ａを記載すればよい。

一括置換

一括置換は「異型データ」の修正で利用する。機能は「マチガイ」→「ﾏﾁｶﾞｲ」などに置換することだ。Ctrl+Hで置換ポップアップの呼び出しが可能である。タブでは以下、ホームタブの編集の一番右側にある。

全角カナを半角カナに変換するASC関数

ＡＳＣ関数は「異型データ」の修正で利用する。機能は全角文字を半角文字に変換する。逆に半角文字を全角文字に変換するのはＪＩＳ関数だ。状況によって使い分けて欲しい。いずれの関数ももともと全角または半角のものはそのまま表示されるので、本関数を記載した列追加すればそのまま利用可能となる。

利用方法は
＝ASC(対象となるセル)
だけで完了だ。

文字データを数値データするVALUE 関数

VALUE 関数は「異型データ」の修正で利用する。機能は文字データを数値データに変換する。アクセスを知っている方であればより意識をしたことがあるであろうが、データには型というものが存在する。エクセル上で文字データとして認識されている”1”は、例えばＳＵＭ関数などで集計することが出来ない。データ型の詳細説明は省くが、この関数はデータ型を修正しカウント可能なものに変更する。

例えば、電話番号などは文字データとして認識されるケースが多く、データの抽入方法によってはＩＤなどの連番も文字データとしてカウントされてしまうことがあるので、それを数値として利用したい場合に本関数を利用する。
データ型は下記の表記を見れば確認可能だ。

利用方法は
＝VALUE（対象となるセル）
だけで完了だ。

【参考】表示形式の変更が反映されない場合に利用するＶＢＡ

なお、上記「文字データを数値データするVALUE 関数」で解説したデータ形式は以下のように変更することも可能だ。

しかし、データが量が非常に多い場合、データ型の表示は変わっているのにセル内部に反映されていないケースがある。その際、いちいちセルをクリックしなければ反映されない状況になってしまうのでデータ量が多い場合は実行性にかける。そんな時は以下のＶＢＡを試して欲しい。

方法は、開発タブにあるVisual Basicを押して以下の作業を図の通りに実施するのみだ。選択されている列を上から順にクリックするというVBAなので自動でクリック反映作業を実施してくれる。

以下、コードをコピペしてそのまま図の通りに貼り付けて実行しよう。
「
Sub reconfirm()
For Each e In Selection
e.Value = e.Value
Next
End Sub
」

知ってる方法でどう解決するかに注力しよう！

冒頭に解説したとおり、上記で紹介した方法がデータクレンジングを行うのに必要最低限かつ十分な手法だ。エクセルにはさまざまな機能があり大変便利であるが機能や関数の理解が不十分であると思わぬ作業手戻りにつながる。あたらしい方法を試すのものよいが知っている方法でどう解決するかに注力しよう。一方で、確かに新しい方法を使えるようになると作業設計の幅が広くなるのも事実だ。一回のデータクレンジングにつき新しい方法1個くらいを目安に習得していくのは悪くないかも知れない。

最後にまとめとして、必要最低限かつ十分な手法をおさらいする。
・データの項目の内容を確認
ー重複削除
－フィルタによる降順並び替え
－区切り位置の指定
・重複データ
ーフィルタを利用したフラグ付け
ー重複削除
・Nullデータ
ーフィルタを利用したＮｕｌｌデータの書き換え
・異型データ
－一括置換
ー全角カナを半角カナに変換するASC関数
－文字データを数値データにするVALUE 関数
－【参考】表示形式の変更が反映されない場合に利用するＶＢＡ

エクセルで膨大なデータを整理しなければならないことはないだろうか。ほとんどの場合は顧客データの整理などマーケティング関連の業務だ。データ整理（データクレンジング）のゴールはデータ分析であることが多いが、きちんとしたシステム設計をしていない限りかなり雑多なデータとして蓄積されているケースが多い。現場のオペレータがメモ代わりに使っていたり、記載方法の教育が徹底されていないからだ。おそらく、この記事を見ていただいている読者はとりあえず手をつけたものの途方もない作業に気が遠くなり簡単な方法がないか模索した結果であろう。

データクレンジング業務には手戻りが必ず発生し、結果としてかなりの時間がかかる。本シリーズで紹介する内容を読み、もう一度業務の設計を見直して、少しでも早くデータ整理の呪縛から開放されて欲しい。

なお、本シリーズで説明する内容は筆者が考える最短でデータ整理をするために利用する方法論と全機能である。複雑な関数や機能を利用することで欲しいデータを作ることは可能かもしれないが頭を使ったり、試した結果できなかったりすることを考慮すると本稿で紹介した手法のみでデータクレンジングをやり遂げる気持ちで業務を遂行して欲しい。

データ整理の流れをもう一度確認

データ整理の流れは以下の通りだ。

データ項目を確認し、アプトプットイメージを作成、データ整理実施となる。

後半のデータ整理作業の実施項目が多いので、そちらに目が行きがちだ。しかし、データ項目の確認とアプトプットイメージの作成は、手戻りの発生を最小限に抑えるために非常に重要な作業となるのでしっかり行うべきであることを頭に入れて欲しい。

①まずは対象となるデータ量の確認しよう

基本的に本記事を見ていただいている方はエクセルでデータ分析を実施しようとしているだろう。エクセルでは最大で1,048,576 行× 16,384 列（2018年時点）のデータを処理可能となっている。しかし、データの切り貼りや関数の入力、複数シートを利用することを前提にすると１万行程度までが限界であることを覚えておいて欲しい。

通常のデータでは1行（レコード）に対して10～20列項目（カラム）が存在する。それに加え、データを整理したり、分析用のフラグをつけたりするバッファーとして30行ほど追加で使うことを考えると50列程度。約1万行×50列程度でエクセルが重くなるのだ。重くなるというのは具体的には計算や保存に異常に時間がかかることを指す（1分以上）。これ以上時間がかかる場合データが破損したり、計算結果・保存の反映が適切に行われているか不安が残るため利用を避けるべきだ。

なお、これ以上の膨大な計算を実施する場合はエクセルでなくアクセスなどを用いるが、アクセスを用いた分析方法の紹介はまた、別の機会に紹介するものとする。

②データ整理の目的と整理するべきデータ項目をもう一度確認しよう

データ整理の目的は基本的に分析となるはずだ。分析をする際に必要となる整理すべきデータが何であるかをしっかり認識しよう。分析は○○×○○の掛け算で行われる。例えば、男女×購買金額などだ。膨大なデータをデータ項目（カラム）で抽出（ソート）し、集計する。そのため、整理するべきデータはある程度絞られるはずだ。

データベースによっては受付ＩＤと顧客ＩＤが別々に采番されていたりして、氏名などが重複する顧客が非常に多くなっているものもある。仮に分析の目的が顧客属性の分布の把握であった場合、受付ＩＤの整理は不必要な作業になるわけだ。

つまり、目的が分析であれば分析をするために必要なデータを想定し、そのデータがどういう形で表現されていれば十分か確認しよう。例えば、商品がどういう顧客に購入されているかが重要なのであれば、顧客の名前などは重要でなく、年齢や性別、家族構成などと商品がひもづいてさえいればよいのだ。

【参考】データの整理とはどういうことか？

そもそも、データ整理とどういった作業なのか？正しい形式のデータになっていれば整理はする必要はない。そのため、データとして正しい姿にするのがデータ整理といえよう。データとして正しくない姿のデータは大きく分けて3タイプ存在する。

正しいデータとは分析するために必要なデータの定義で各データが蓄積されている状態のことを指すのだ。そのため、前項で解説したように、分析をするために必要なデータを想定し、そのデータがどういう形で表現されていれば十分か確認する必要がある。

③クレンジング作業の設計

作業実施前の最後のプロセスとしてクレンジング作業の設計がある。設計をせずに、たぶん、こうすればよいだろうなという思い切りで進むと思わぬ落とし穴にはまることがある。これまでやった作業が無駄になり、場合によってはこれまでやった作業と同じ作業をするはめになることさえある。

例えば、名寄せ作業（同一人物をひとつのデータにまとめること）をすることに気を取られ、顧客ＩＤのデータ欠損に気づかなかったとする。顧客ＩＤをベースに名寄せ作業を開始したが、顧客ＩＤのデータ欠損に気づき顧客ＩＤの欠損数を調べたら8割が欠損していた。結局、性別と年齢と住所を元に名寄せ作業をするしかなくなった。こういった失敗例はよく存在する。

そのためにオススメしたい作業の進め方は2つ。「データの不備の把握・整理の順序決定」と「小まめなデータ保存」だ。クレンジング作業の設計はこの二つで構成される。

・データの不備の把握・整理の順序決定
②データ整理の目的と整理するべきデータ項目をもう一度確認しようで絞った整理する対象を決定した上で、それぞれのデータにどのような不備が存在しするか確認する。その上で、データの不備の修正作業がどのように他のデータに影響を及ぼすかを検討し、整理する順序を決定する。これにより基本的に失敗しないデータの整理方法が明らかになるはずだ。

なお、「データの不備の把握・整理の順序決定」は必ず資料化しよう。どんなに作業の設計をしたとしても新たな不備の形がでてくる可能性がある。システムの入力できちんとした設計がされていない限り完璧なデータにするのは非常に骨が折れる。「こういうデータ不備がメインだったので、こういう処理でデータを整理しました。」という資料があり、それを上司などと握れていれば、基本的に手戻り作業は発生しない。上司も完璧を求めることが生産性の観点から不要であることを認知することが出来るからだ。

・小まめなデータ保存
ひとつの資料を上書きしていく形で作業を進めるのはとても危険だ。想定していなかった大きな不備が発見されたり、データが重くなりファイルが壊れる可能性もある。出来れば作業ごとに新しいファイルを作成していくのが理想であるが、３作業ごとを目安に新しいファイルを作成していくルールで作業しても良いだろう。その際、何の作業をしたかわかるようにファイルを命名するとよりわかりやすい。

データクレンジングでは作業の設計が重要！

これまで解説したように、データ整理はデータの把握と作業の設計が肝だ。手戻りしたり、上司からつき返されたタイミングでせっかく作業した内容が無駄になる。きちんと作業の設計をして上司と作業手順を握ろう。

次回は【参考】データの整理とはどういうことか？で解説した欠損例に応じた具体的な作業方法について解説する。具体的な作業は以下のようなプロセスで進むだろう。

冒頭に述べたとおり、複雑な関数や機能を利用することは必ずしも最短・精緻なデータを作ることに適していない。本稿で説明した内容をしっかりした上で、次回説明する手法のみで作業を完了させることを心がけてほしい。

次回は「【コツ徹底解説！】エクセル データ整理・クレンジングの早ワザと失敗例②｜整理方法」で確認可能だ。