Excelの困りごとを解決するこのコラム。今回は、集計時の困りごとについてです。Excelに関する質問も募集中。抽選で、マンガで読むExcelの教科書をプレゼントします。詳細は記事のさいごに。
集計しづらい状態のデータを受け取った。対処法は？
売上などの数字の集計表を作ってほしいと頼まれることが多いんですけど、その集計のもとになるデータがめちゃくちゃでいつも大変なんです。
Excelで最もよく行われる作業が「数字の集計」です。しかし実務の現場からは「集計できない・集計しづらい」状態のデータから、数字を集計することが多くて困っている…といったご相談をよくいただきます。
「集計できない・集計しづらい」データとはどのようなデータなのでしょうか。そしてそのようなデータを扱うことになったときは、どのように対処すればよいのでしょうか。
まず、「集計できる・集計しやすい」データの条件とは
Excelで数字を集計するときはSUM関数やSUMIFS関数、ピボットテーブルといった機能がよく使われます。しかしこれらの関数や機能で数字の集計をスムーズに行うには、集計されるデータが次の条件を満たしている必要があります。
集計をスムーズに行う条件
■条件1
セルに入っている値が「計算可能」な「数値」である
■条件2
Excelで数字集計しやすい「データベース形式」になっている
「集計できない・集計しづらい」データは、この二つの条件に当てはめていくと対処法が見つかります。それでは一つずつ見ていきましょう。
条件1：セルに入っている値が「計算可能」な「数値」である
Excelで計算可能な値は「数値」と呼ばれます。
計算可能な数値とは
【1】単位などの「文字」が一緒に入力されていない
【2】入力されたセルの表示形式が「文字列」以外である
よくある「集計できない・集計しづらい」ケースを例に、まずは【1】から解説します。
■集計しづらいよくある例
単位も一緒に入力されてしまって、集計できない…。
セルに入っている値が「数値」でなければ四則演算の数式やSUM関数などで集計することができません。
例えばセルに、「100円」とか「100人」と言ったように、数値以外の文字を一緒に入力してしまうとそれは「数値」ではなく「文字列」というタイプのデータとなってしまい、数式で計算に使えない値になってしまうのです。このことから、「あとで数値計算に使われる数値を入力する際は、単位の文字を一緒に入力してはいけない」のです。
単位などの「文字列」を取り除く方法
数字と一緒に文字列が入力されている場合、その文字列を削除して数値に変換する方法は2つあります。
■「置換」機能を使う
Excelの「置換」機能は、選択しているセルにおいて、指定した文字を別の文字に置き換えることができるものです。
たとえばB列のすべてのセルから「円」という文字を削除したいとします。
（1）B列を選択する
（2）ショートカットCtrl＋Hを押して「検索と置換」機能を立ち上げる
（3）「検索する文字列」ボックスに「円」と入力、「置換後の文字列」ボックスは空白のまま…つまり何も入力しない状態で、「すべて置換」をクリック
これでB列にあるすべての「円」という文字を空白に置き換えることで、結果的に円という文字を削除できます。
■SUBSTITUTE関数を使う
SUBSTITUTE関数も「置換」の処理を行える関数です。毎日のルーティン作業となっている場合は、関数で自動化する仕組みを作ると楽です。
=SUBSTITUTE(B2,”円”,””)
このような式で、第一引数に指定したセルにおいて円という文字を削除することができます。
しかしこの関数で円を削除できても残った数字は「文字列」の状態のままになっているため、これをさらに数値に変換する処理を加える必要があります。それには「1を掛け算する」方法とVALUE関数を使う方法の2つがあります。
=SUBSTITUTE(B2,”円”,””)*1
=VALUE(SUBSTITUTE(B2,”円”,””))
どちらを使っても同じ結果になります。使い分けは好みの問題ですが、1を掛け算する方が式の見た目としてはシンプルでよいかもしれません。
■単位もつけて表示したい場合は
「単位もつけて表示したい」というケースがあれば、[セルの書式設定]の表示形式のユーザー定義を活用します。すると、見た目だけ一緒に任意の文字を表示することができるようになります。
■集計しづらいよくある例
SUM関数で『複数のセルを合計する式』を入力したのに、結果が0になる。単位などもついていないのに…。
数字が入力されているセルの表示形式が「文字列」になっていませんか。文字列は、SUM関数などで集計することができません。集計することができるのは、「数値」です。
この場合は、すでに数字が入力されているセルの表示形式を数値に変更するだけでは解決しません。表示形式を数値に変更した上で、再度同じ数字をそのセルに入力しなければ計算に使えるセルにはなりません。
しかし、そんな面倒なことはやってられません。ではどうするか。
入力されたセルの表示形式を「文字列」から「数値」にする方法
こたえは、「別の列にて数値に変換して、その列で計算する」ということになります。
文字列になってしまっている数字を数値に変換するには2つの方法があります。
■VALUE関数を使う
■1を掛け算する
たとえばA1セルに文字列化した数字が入っている場合、この値を数値に変換する式は
=VALUE(A1)
または
=A1*1
となります。どちらを使ってもいいです。
見た目は数字でも関数での計算に使えない文字列化した数字は、このように数値に変換することで計算につかえる値にすることができます。システムからダウンロードしたExcelなどでは頻発する問題です。そんな状況に遭遇した際にはぜひ思い出してください。
条件2：数字集計しやすい「データベース形式」になっている
数字の集計をスムーズに行うもう一つの条件は、集計されるデータが「データベース形式」であることがとても重要です。
多くの企業の現場ではそれは「システムやデータベースからExcelやCSVでエクスポート・ダウンロードしたそのままの状態のデータ」として入手可能なはずです。
もしも、データベース形式以外のデータをもとに集計をしなければならないときは、「データベース形式のデータがあるかどうか」を確認してみてください。
数字集計作業は必ずその材料として「データベース形式」のデータを用意する。これを「データベースファーストの原則」と呼んでいます。あらゆる数字の集計作業においてこの「データベースファーストの原則」が必須になります。
「データベース形式になっていないデータ」を集計するデメリット
データベース形式でないと、そもそも数字集計の定番ツールであるピボットテーブルで集計することができません。また、やはり数字集計の最重要関数であるSUMIFS関数での集計もデータベース形式のデータを参照して行うものです。
数字集計の対象となるデータがこのようなデータベース形式ではなく、誰かが独自に作ったような、データベース形式以外のデータをもとに集計をしなければならないとなると、これはとても厄介です。
「システムやデータベースからExcelやCSVでエクスポート・ダウンロードしたそのままの状態のデータ」…これを用意することからすべての数字集計作業が始まる…そんな手順を確立することが、作業の効率化において極めて重要な課題になります。
書籍プレゼントのお知らせ
抽選で書籍『マンガ たった1日で即戦力になるExcelの教科書』をプレゼント。
Excelの疑問や困りごとなども、ぜひ教えてください。お待ちしています。
受付は終了いたしました。
たくさんのご応募ありがとうございました。
■注意事項
・リクルートスタッフィングのご登録情報と応募時にご入力いただく【氏名】【スタッフNo.】【メールアドレス】【生年月日】の4点が一致していない場合は、抽選の対象外となります。ご登録情報のご確認は、MyPageよりお願いしております。
・当選者の発表は、賞品の発送をもって代えさせていただきます。賞品の発送は、締め切り後1ヶ月以内にお送りいたしますが、諸事情によりお時間をいただく場合もございます。ご了承ください。
・賞品は、MyPageにご登録の「住所」にお送りいたします。住所に変更がある場合は、締め切りの2024年10月28日（月）までに「登録情報の確認・変更」よりお手続きをお願いいたします。
・個人情報やご登録状況、抽選の結果、プレゼント発送状況に関するお問い合わせは、お答えいたしかねます。
▼Excelの関数や表・グラフの作成など、無料で学べるオンライン研修もご利用ください！
▼式を壊してしまいそう…の不安を解消する方法
