エクセル

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

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

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

絶対値を計算する関数は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つ。「データの不備の把握・整理の順序決定」と「小まめなデータ保存」だ。クレンジング作業の設計はこの二つで構成される。

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

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

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

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

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

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

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

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

社内アンケートや調査アンケートの作成時にチェックボックス（レ点）を利用することがあるだろう。アンケートの内容が決まっているのに、いざエクセルに落とし込むと使い慣れていない機能を多く利用することになるので作成に一苦労する。そんな中でつくり方がわからないとよく問合せを受けるのがExcelのチェックボックスの作り方とその大きさの変更方法だ。他者に見せることが前提の資料であるため、その体裁を整える必要があるが特に使い方がわかりづらい。Webで調べるとマクロを編集してなど小難しいことばかりでてくる。簡単に体裁を整えたい方は頭を悩ませているはずだ。今回はチェックボックスの作成方法とその大きさの整え方について解説する。

なお、大きさの変更については後半に記述しているのでそちらが気になる方は前半の作成方法は読み飛ばしていただきたい。

複数回答可のアンケート項目に必要なチェックボックス（レ点）

アンケートを作成する際、その回答を簡単にするために選択式やチェックボックス式を利用する。特にチェックボックス式は複数回答を可能とする際に利用される。

エクセルの初期設定では画面に、チェックボックスを挿入するボタンがない。そのため、まずはチェックボックスを挿入するボタンを出現させる準備が必要となる。

①：作業画面最上部のセーブボタンの横になる▼ボタン（クイックアクセスツールバーのユーザー設定ボタン）をクリックする

②：出てきたメニューの「その他のコマンド(M)」をクリック

③：Excelのオプションというウインドウが出てくるので、左端の「クイックアクセスツールバー」をクリック

④：「コマンドの選択(C)」下のプルダウンリストをクリックするとメニューが出てくる

⑤：そのまま、「リボンにないコマンド」をクリック

⑥：出てきたメニュー中に「チェックボックス(ActiveXコントロール)」をクリックする。右隣の四角内に「チェックボックス(ActiveXコントロール)」という項目が出現したのを確認してから「OK」ボタンを押す

⑦：最初の▼ボタンの横にレボタンが出てくるのでそれをクリックすればチェックボックスの作成完了だ

チェックボックスの大きさの変更

ココで作ったチェックボックスは大きさを変更しても中身のボックス自体の大きさは変更されない。これが皆さんの悩みの種だ。以下では、３ステップで大きさが変更される方法を解説する。

①：チェックボックスを1セル分にあわせたら、チャックボックスで右クリックを押し、「コントロールの書式設定」をクリック。

②：「コントロールの書式設定」をプロパティタブに切り替え、「セルに合わせて移動やサイズを変更する」を設定、OK。

③：あとはセルの大きさを変更するだけ

体裁を整えて、回答率の高いアンケートを作成しよう

アンケートの回答率は答えやすさに直結する。今回説明したチェックボックスの作成やその大きさの変更はアンケートの答えやすさに関連する部分なのでしっかり抑えておきたい。
また、このような細部へのこだわりは必ず将来評価につながるのでしっかり、習得しておこう。

誰でも社会人になって1人は見たことがあるであろうエクセル魔人。マウスをほとんど使わず、すごいスピードでエクセルが出来上がっていくのを横で見たことはないだろうか。そんな魔人と一般人の大きな違いは、基本的なショートカットを使いこなしているかどうかだ。ここでは、エクセル操作が便利にすばやくなる、基本操作に関わるショートカットを9個紹介する。

Ctrl+やじるしでスピード移動

まず基本的なのが、このショートカットだ。マウスを使わずすばやく操作するには、必要なセルにすばやく移動する必要がある。そこで必須なのが、このショートカットだ。

Ctrl + やじるし：次の空白か記載されたセルまで移動

このショートカットは、やじるしの方向へ、何かにぶつかるまで移動する。空白セルからの移動であれば、何か記載されたセルまで移動する。記載されたセルからの移動であれば、次の空白に当たるまで移動する。ポケモンの氷の上での移動だと思えばいいだろう。
一部のプロは、Ctrl+やじるしで移動をする前提で、表を作る段階から空白の行を配置したりすることもある。
具体的な一例を紹介する。Ctrl+やじるしの欠点として空白のセルが存在するとそこで移動がとまってしまう。なので、エクセル魔人は基本的に空白セルをつくらない。しかし、空白セルはどうしてもできてしまう。そんなときに利用されるTipsだ。
解決策は簡単。表の周囲を「＊」で囲うことだ。コレにより空白セルが合ったとしてもCtrl + やじるしを連打し、最終行（列）に行ってしまっても一発で最終行（列）に戻ってくることができる。

Shift+やじるしで複数セル選択

Shift+やじるし：Shiftを押しっぱなしにして、複数セルを選択

Shiftを押している限り、やじるしで移動したセルを全て選択状態にできる。
これは、先ほどのショートカットと合わせて使用されることも多い。
Ctrl+Shift+やじるしで、表の中の1つの行や列をまとめて選択できる。

Ctrl+R、Ctrl+Dで隣のセルをコピー

Ctrl+スペース：今選択しているセルの列を全て選択
Shift+スペース：今選択しているセルの行を全て選択

列や行を一瞬で丸ごと選択するショートカットだ。
Shift+スペースの場合は、半角入力になっていないと機能しないので、要注意だ。
使い方は色々あるが、一番よく使うのは次に紹介するショートカットとの組み合わせだ。

Ctrl+プラス(+)、Ctrl+マイナス(-)で、行や列を追加・削除

Ctrl+プラス(+)：選択している行や列を追加
Ctrl+マイナス(-)：選択している行や列を削除

このショートカットで、行列の追加・削除がすぐにできる。なれると、マウスで選択して右クリックで追加するよりもよっぽど早くなる。
列や行を丸ごと選択していないと使えないのでそこは注意しよう。
また、通常のキーボードでは、shiftも一緒に押さないとプラス(+)を選択できない。

Ctrl+Shift+Lでフィルターを追加

Ctrl+Shift+L：フィルター追加

データの表を作っていると、よく使うのはフィルターだ。これもショートカットですぐに追加できる。
普通に使えば、選択しているセルの行を、範囲を自動的に選択してフィルター設定してくれる。フィルターを付ける範囲をしっかりしていしたければ、そのセルを複数選択してこのショートカットを使えばよい。
また、フィルターがシート内のどこかで設定されている状態でこのショートカットを使えば、フィルターを解除できる。

これであなたもエクセル魔人に！

今回、マウスなしでの操作用の基本的なショートカットを紹介した。
まとめると以下の通りだ。

①Ctrl + やじるし：次の空白か記載されたセルまで移動
②Shift+やじるし：Shiftを押しっぱなしにして、複数セルを選択
③Ctrl+R：１個左のセルをコピー
④Ctrl+D：１個上のセルをコピー
⑤Ctrl+スペース：今選択しているセルの列を全て選択
⑥Shift+スペース：今選択しているセルの行を全て選択
⑦Ctrl+プラス(+)：選択している行や列を追加
⑧Ctrl+マイナス(-)：選択している行や列を削除
⑨Ctrl+Shift+L：フィルター追加

もちろん、エクセルの便利なショートカットは他にもたくさんあるので、追って紹介していきたい。

SUBTOTAL関数は、知名度が意外と低いが、とても便利な関数だ。知っていても、2つのうちの１つの使い方しか知らない人も多い。SUBTOTAL関数を使えば、様々な操作を簡単に実施できるので、覚えて損はないだろう。

そもそもSUBTOTAL関数とは？

SUBTOTALは、データを集計する関数だ。
入力方法は、以下の通りだ。

=SUBTOTAL( 集計方法 , セル範囲 )

集計方法は、以下を指定できる。一番よく使うのは、SUM(合計)だろう。
1=AVERAGE
2=COUNT
3=COUNTA
4=MAX
5=MIN
6=PRODUCT
7=STDEV
8=STDEVP
9=SUM
10=VAR
11=VARP

そして、特徴として以下の2点がある。
①フィルターされたセルを計算しない
②SUBTOTAL関数同士を計算しない

それぞれ以下で解説していく。

SUBTOTALの使い方①　フィルターを排除した計算結果を簡単に出せる

SUBTOTAL関数の特性の１つは、フィルターしたセルを計算しないことだ。
つまり、属性が複数ある際、フィルターをちょっといじるだけで、すぐ合計などの集計値を出すことが可能だ。

例を見てみよう。

上記の通り、例えば「A社」でフィルターすると、自動的に今表示されているセルだけで合計を出してくれる。
SUMIF関数などを属性ごとに作るよりも、よっぽど楽だ。複数の属性があればあるほど、より便利だろう。

SUBTOTALの使い方②　簡単に小計を記載できる。

SUBTOTAL関数のもう1つの特性は、SUBTOTAL関数は他のSUBTOTAL関数の数値を無視して集計するということだ。
以下の図を見ればわかりやすいだろう。

以上の図では、「合計売上」と各「小計」にSUBTOTAL関数を使っている。
「合計売上」のSUBTOTAL関数の選択範囲は、D6:D15（D6～D15全て）だ。SUBTOTAL関数は他のSUBTOTAL関数を計算しないため、このシンプルな選択範囲で合計値を算出可能だ。
もしこれをSUM関数を利用して実行しようとすると、選択範囲を「D6:D8,D10:D12,D14」と選択しなければならない。これは選択するのも面倒であれば、ちょっと表が変わっただけでいちいち調整しなければならない。SUBTOTAL関数を使えば、これが簡単に設定できるのである。

SUBTOTAL関数の注意点

SUBTOTAL関数を使う上で、一点注意がある。それは、フィルターされたセルは計算しないが、通常、手動で非表示されたセルは計算に含まれることだ。
これは実は、手動で非表示されたセルも計算に含まないように設定することも可能だ。その場合、集計方法として始めに紹介した数値でなく、以下の数値を入れればよい。

集計方法（手動で非表示にしたセルを計算しない場合）
101=AVERAGE
102=COUNT
103=COUNTA
104=MAX
105=MIN
106=PRODUCT
107=STDEV
108=STDEVP
109=SUM
110=VAR
111=VARP

SUBTOTALで集計表の作成を簡単に！

今回は意外と便利な関数であるSUBTOTAL関数を紹介した。
SUBTOTAL関数は、以下の２つの特徴があるデータ集計の関数だ。
①フィルターされたセルを計算しない
②SUBTOTAL関数同士を計算しない
このように、便利な関数を1つずつ覚えていくのが、エクセルを使いこなすための近道だ。

コンサルティングファームと投資銀行に勤めてわかったことは、コンサルタントはエクセルワークが苦手ということだ。きれいな資料を作ってクライアントにプレゼンをしているコンサルタントたちは資料の見せ方において他のビジネスパーソンに比べて優位性があるものの、いざエクセルワークとなると素人同然になる。コレはエクセルをあくまで計算や管理表といった内部検討資料として利用しているためだ。おそらく、ビジネスパーソンの多くは同様の課題を抱えているだろう。本記事では、何に気をつければエクセレントなエクセルワークを実施できるか？といったことについて解説していくものとする。

Excel workの位置づけ

ほとんどの会社はMS-Officeを導入しており、その主要3ソフトである「Word」、「Power point」、「Excel」は業務の基本として浸透している。Excelは表計算ソフトなのでパワーポイント等の他の資料用のグラフを作成するために利用したり、数値分析を行うために利用されているので、実は最も表に出てきにくいツールといえる。

いざ、エクセルの見せ方や利用法についてスキルアップしようとしても、
・適切なテンプレートが見つからない
・表に出てくる他社資料がないので参考にできない
・進捗管理用のエクセルしかイメージがない
といった理由からエクセル資料のフォーマットが会社によってまちまちであったり、効率的な利用法が標準化されていない状況になっている。しかし、だからこそエクセルの基本原則を学ぶことは簡単に差別化できる最短ルートだと考えて欲しい。

エクセルを利用する際の基本スタンス：
「報告」と「共同作業」を意識

ココではエクセル作成における心得に触れていくものとする。業務全般について言えることだが、会社での業務は必ず「報告」と「共同作業」が含まれる。エクセルを利用した場合もこの点についてちゃんと意識をすべきだ。
冒頭で述べたとおりコンサルタントだったとしても、この辺をおろそかにしているケースが多い。これは「報告」や「共同作業」を意識していないメモ書きのようなエクセルが横行しているということだ。想定しているよりも自分の作成したエクセルを他人が見る機会は多い。自分が作成したエクセルを2週間まったく開かずに離れた後エクセルを見て欲しい。「報告」や「共同作業」を意識できていないエクセルだった場合、読解は非常に困難になっているはずだ。2週間後の自分も他人と捕らえるべきなのだ。
それでは「報告」や「共同作業」を意識したエクセルを作成するにはどうしたらよいのだろう。ファイルの利用者と閲覧者双方が見やすい/理解しやすい作り方を意識することが重要だ。そのためにはエクセルを論理的かつ見やすく作成する。報告者を意識してピラミッドストラクチャーに沿った形で報告し、ただの数値の羅列にならないように意識する。また、他の作業者にもわかるようにロジックツリーで展開したように構造的に表を作成することだ。ピラミッドストラクチャーやロジックツリーについては別の記事で紹介するのでそちらを参考にしてほしい。

いずれにせよ重要なことは「報告」や「共同作業」を意識して常に論理的かつ見やすくエクセルを作成することだ。

見やすい表を作るための基本原則

最後に見やすい表を作る他の基本原則を解説する。基本原則は２つのみで単純だ。

原則①：カラムの流れが論理的な流れになっている

人の視線はモノを見る時、決まった流れがある。パワーポイントの作成時に意識する流れと同様、表を作成する時も流れを意識する。表は左から右へ、上から下へ見ていくので、その流れを論理的な構成になるようにすべきだ。

原則②：不必要な情報は削除する

表を作成する際、アレもコレもと情報過多になってしまいがちだ。しかし、あくまで人が見ることが前提の資料。ノイズを極力削除するのが重要となる。数字や項目だけでなく、文字のサイズ・線・色づけ等についても不必要な情報を極力減らすのが見やすい表を作るコツだ。

エクセルの「報告」と「共同作業」における小技

基本原則は、解説の通りだ。他にもちょっとした小技はいくつかあるので紹介しよう。

むやみにセルの結合をしない

エクセルを作成していると構成の変更やセルの並び替えが発生したり、フィルターを使って一部の内容だけ見たり、といったことがあるだろう。セルを結合してしまうと、構成変更、並び替えやフィルターがうまくできなくなってしまう。ほとんどファイルをいじる可能性がなく、セルを結合しないと見栄えが悪い場合以外は、できるだけしない方がよい。
フィルターや昇順を使って確認される可能性のある表であれば、複数のセルの内容が同じでも、結合でなくそれぞれ同じ内容を記載した方が使い勝手がよい。
また、表頭などで1つの内容を複数セルにまたがって記載したい場合は、結合しないでもやり方がある。以下の通りだ。
対象の複数セルを右クリック⇒セルの書式設定⇒配置⇒横位置の中から「選択範囲内で中央」を選択

提出する際は、シートの左上のセルを選択した状態で保存する

報告で送ってもらったエクセルを開くと、ランダムな位置のセルが選択されていることはよくあるだろう。どんな印象を受けただろうか。作業中のような間隔を受けたり、どこから見たらいいかわからなかったりしないだろうか。
ほんの小技だが、各シートの左上のセルを選択した状態で保存しておけば、開いた時にしっかり完了したファイルである印象を与えることができる。

シートを整理する

シートの整理で気にするのは二つだ。
・名前を付ける。
・余計なシート（空シートなど）は消す。
特に、シート数が多くなる場合は必須だ。シート数が少なくても、完成度が高い印象を与えられる。

基本スタンスを徹底してライバルに差をつけろ

冒頭に述べたとおり、エクセルは基本が重要にも関わらず、その基本を徹底している人は少ない。基本を徹底するだけで他の人と差別化できるので非常に費用対効果の高いツールといえよう。基本スタンスとして「報告」や「共同作業」の他者を意識し、基本原則を守って見やすい表を作ってほしい。

エクセルを利用して資料を作って行く際、既存の行の間に新しい行を入れていきたいと思ったことははないだろうか？表計算というエクセルの基本からは離れているが、エクセルで報告資料を作成する業務などではよく遭遇する問題だ。その際、一行一行右クリックをして新しい行を挿入していく必要があるが、行が数百行あったり大量だとそれをやる気もうせる。そんな時に、簡単にそれも好きな行数だけ既存の行間に行挿入するテクニックを公開しよう。

まずは先頭列に番号を采番

今回のテクニックは下記図のような表の行と行の間に任意の数の空白行を挿入することが目的である。

下準備として先頭列に１．２．３．・・・・・と番号を采番する。采番は最初の2行に１．２．と記入した上で記入したセルを範囲指定。右下のかすかに四角くなっている部分を左クリックして下にスクロールすると簡単だ。

次は采番した列全体をコピー

采番をし終わったら、次は采番した範囲全体をコピーする。コピーをしたら挿入したい行数だけ下に貼り付けをしていく。もちろん、挿入する行は一行だけでなく、一度に複数挿入可能だ。

ココまで、完了すれば作業は終わったも同然だ。カンのいい人なら既に気づいているだろう。

仕上げの作業、フィルター・降順の整列

あとは、先頭行を選択しデータタブのフィルターを選択。

采番した列のタブ選択し、降順を選択。

行を規則的に挿入するには３ステップ！

簡単だろう。しかも、このテクニックのいいところは何行でも挿入可能なことだ。最後にテクニックの概要を確認して締めくくりとする。
①既存の表の先頭行に采番
②挿入したい数だけ采番をコピー
③フィルター・降順

エクセルは様々な機能を有しているので、その機能を組み合わせれば様々な作業を効率化することができる。自身のオリジナルの効率化を開発するのもエクセルワークの醍醐味のひとつである。

※本記事で使用したデータは疑似個人情報生成サービスを利用して作成したものとなっております。

部署ごとの売上順位など、エクセルで条件別の順位を出したいことが度々ある。通常RANK関数でランキングを出すが、複数条件を指定できるIF機能のついたRANKIF関数はエクセルには存在しない。しかし、別の関数をうまく活用することで、条件を指定をしたRANKIF関数のようなことは可能だ。その方法について解説する。

RANKIFは、COUNTIFを使えば代用できる

ランキングとは、そもそもその項目よりも大きい(もしくは小さい)項目がいくつあるか表示する機能と言ってもよい。
つまりCOUNTIFS関数を使えば、本来エクセルにないRANKIF関数のような操作は実施可能だ。

以下の通り関数を組めば、条件別のランキングが表示可能だ。
—————————————————————————————
【大きい順ランキング】
COUNTIFS（順位を出す数値の範囲 , “>”& 順位を出すセル , 条件範囲1 , 条件1 , ・・・）+1
【小さい順ランキング】
COUNTIFS（順位を出す数値の範囲 , “<“& 順位を出すセル , 条件範囲1 , 条件1 , ・・・）+1
—————————————————————————————

順位を出す数値の範囲：順位を出す数値と比較するセルの全体範囲（「売上」の列など）
順位を出すセル：順位の数を出したい数値（「100,000円」のセルなど）
条件範囲１：条件が記載されているセルの全体範囲（「地域」の列など）
条件１：順位を出したい条件（「北海道」のセルなど）

条件が複数あれば、条件範囲２, 条件２、のように追加していけばよい。
具体例を使って解説する。

COUNTIFを使った条件別ランキング表示を試してみる

下記のエクセルに沿って実際に試してみよう。

これは、とある会社の顧客別の売上リストとする。これから地域別のランキングを表示する。

この会社は、主に「東京」「神奈川」「埼玉」で活動しており、顧客ごとの地域はA列に記載されている。
D列は、全体のランキングだ。通常通り、RANK関数でこれは出せる。

E列に、地域別の売上の大きい順でのランキングを表示する。
先ほどの通り、その場合の関数は以下の通りだ。
COUNTIFS（順位を出す数値の範囲 , “>”& 順位を出す数値 , 条件範囲1 , 条件1 , ・・・）+1

これを今回のケースに当てはめると、以下のような入力となる。
COUNTIFS($C$2:$C$20 , “>”& C2 , $A$2:$A$20 , A2 )+1

順位を出す数値の数値の範囲は、売上の順位を出したいため、C列となる。
順位を出す数値は、C列の中の、順位を出す特定の企業の売上セルだ。
条件範囲１は、地域ごとの条件で順位を出したいため、A列となる。
条件１は、同様に、A列の中の、順位を出すと特定の企業の地域セルだ。

順位を出す数値の範囲と、条件範囲のセルに「$」を入れているのは、E列全体に関数をコピーした場合に、全体範囲がずれないためだ。
結果、以下の通りになる。

例えば、O社は全体では6位だが、埼玉内では1位だとわかる。

COUNTIFSでグループ別順位を計算する仕組み

COUNTIFS関数は、そもそも条件に合う項目がいくつあるかをカウントする関数だ。ここでは、条件の合うもので「対象のセルよりも大きい(もしくは少ない)セルの数」を出している。そのため、大きいか小さいかで”>”と”<“の記号だけ違う。
また、そのままでは、一位のセルが「0」と表示されてしまう。それよりも小さいセルが他に一つもないからだ。最後の「+1」はそのためにある。

ドンピシャな関数がなくても他の関数で代用

ありそうでないRANKIF関数も、その本質が、対象のセルよりも大きい(もしくは小さい)セルの数をカウントすることだとわかれば、COUNTIFSという別の関数で代用できる。
このような小技をいくつか知っていれば、エクセル作成のスピードもあがるだろう。