集計しづらい状態のデータを受け取った。リクルートスタッフィングが運営するオンラインマガジンらしさにこだわれば、らしい働き方が見えてくる。SITE2024-09-24集計しづらい状態のデータを受け取った。対処法は?らしく学ぶTweetExcelの困りごとを解決するこのコラム。今回は、集計時の困りごとについてです。Excelに関する質問も募集中。抽選で、マンガで読むExcelの教科書をプレゼントします。詳細は記事のさいごに。集計しづらい状態のデータを受け取った。対処法は?売上などの数字の集計表を作ってほしいと頼まれることが多いんですけど、その集計のもとになるデータがめちゃくちゃでいつも大変なんです。Excelで最もよく行われる作業が「数字の集計」です。実務の現場からは「集計できない・集計しづらい」状態のデータから、数字を集計することが多くて困っている...といったご相談をよくいただきます。「集計できない・集計しづらい」データとはどのようなデータなのでしょうか。そのようなデータを扱うことになったときは、どのように対処すればよいのでしょうか。「集計できる・集計しやすい」データの条件とはExcelで数字を集計するときはSUM関数やSUMIFS関数、ピボットテーブルといった機能がよく使われます。これらの関数や機能で数字の集計をスムーズに行うには、集計されるデータが次の条件を満たしている必要があります。■条件1セルに入っている値が「計算可能」な「数値」である■条件2Excelで数字集計しやすい「データベース形式」になっている「集計できない・集計しづらい」データは、この二つの条件に当てはめていくと対処法が見つかります。それでは一つずつ見ていきましょう。条件1:セルに入っている値が「計算可能」な「数値」であるExcelで計算可能な値は「数値」と呼ばれます。計算可能な数値とは【1】単位などの「文字」が一緒に入力されていない【2】入力されたセルの表示形式が「文字列」以外であるよくある「集計できない・集計しづらい」ケースを例に、まずは【1】から解説します。単位も一緒に入力されてしまって、集計できない...。セルに入っている値が「数値」でなければ四則演算の数式やSUM関数などで集計することができません。セルに、「100円」とか「100人」と言ったように、数値以外の文字を一緒に入力してしまうとそれは「数値」ではなく「文字列」というタイプのデータとなってしまい、数式で計算に使えない値になってしまうのです。このことから、「あとで数値計算に使われる数値を入力する際は、単位の文字を一緒に入力してはいけない」のです。使う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業務改善コンサルタント。Excel研修を毎週開催、高額の受講料にも関わらず、全国から受講者が参加し、常に2ケ月先まで満席状態が続く。問わず、Excelを駆使したあらゆる分野での業務改善の専門家として知られる。著書は『たった1日で即戦力になるExcelの教科書』『たった1秒で仕事が片づくExcel自動化の教科書』(技術評論社)など多数。Tweet関連記事2024-10-22Accessって、そもそも、なに?らしく学ぶ2024-10-01通勤時にちょうどいい英語レッスン『よろしくお願いします』って何ていう?らしく学ぶ2024-09-17クリック操作のみで写真を加工。WindowsのAI活用法らしく学ぶ通勤時にちょうどいい英語レッスン『よろ...クリック操作のみで写真を加工。Wind...RANKING記事ランキングAccessって、そもそも、なに?Wordの表作成や図の配置。「こんなはずじゃない!」をなくすテクニックとは通勤時にちょうどいい英語レッスン『よろしくお願いします』って何ていう?集計しづらい状態のデータを受け取った。Makerリクルートスタッフィングが運営するオンラインマガジンです。Maker」として働き方そのものを生み出せる企業になることを目指しています。