EXCELで会員管理（第２回）ブチョーの入力した変なデータを直すのが手間なんですが？
第１回 とにかく1000件のデータを入力しなくちゃいけなくなったのですが？
前回まで
いきなり1000件の会員情報をエクセルに入力しなければいけなくなったミカミ。株主総会というイベントを間近に控えた多忙な毎日の中で、可能な限り手間をかけずに済むように入力前の事前準備を行い、総務部で手分けして入力した結果、何とかデータ入力は終わったが・・・。
どう？会員データの入力は終わった？
ありがとうございました！おかげ様で、だいぶ予定より早く終わりました。みんな郵便番号辞書も知らなかったからビックリしてましたよ。ちょっと知っている、知らないの差ですけど、こんなに違うんですね。基礎が大事、準備が大事っていうのもよく分かりました。
でも、私達は終わったんですがブチョーのが・・・。オレも手伝うって言って、200枚くらいハガキもっていっちゃったんですよ。
一緒にやってもらえるのはありがたいんですけど、結局、半分くらいしか入力されていなくて、エクセル表の方もめちゃめちゃで、１個１個直すくらいなら、全部入力しなおした方が速いかもって話していたんです。
何度も私達でやりますって言ったんですけど・・・。あ、入力してない分は今入力してもらってます。
ちょっとブチョーのエクセルシート見せてもらえる？
やっぱり入力しなおした方が良いですかね？
ちゃんと説明して、メールにも書いたんですけど、全然読んでくれなかったのか、何一つ守ってくれなくって。
あ、でもこれなら何とかなるよ。ブチョーなりに規則性をもって入力しているみたいだ。これなら10分もあれば大丈夫かな。
どうしても複数人でデータ入力していると、エクセルみたいに自由に入力できちゃうものは人によって差がでちゃうから、これはある程度仕方ないよ。
ま、ブチョーのはある程度を超越しちゃっているけどね。こういったデータを一括して修正する、整形する方法を教えるね。
10分ですか。それは助かります！！
●このエントリの内容
問題点を整理しておこう
じゃあまず問題点を整理しておこうか。
これがブチョーが作ったエクセルシートです。原本は私が作って、入力していってもらいました。
まず不思議なのが、名前の文字と文字の間にスペースが入っているんです。チョーメンドウだと思うんですけど、何でこんな事するんですかね？あと、カナは全角でってお願いしたんですけど半角になっていました。
郵便番号は半角でお願いしたんですけど、全角になっているし、ハイフン（－）が長音（ー）になっているんです。むしろこれどうやって入力しているんですかね？〒の記号も入れないって事にしたんですが・・・。
そして住所もスペースが入っているし、長野県は県名が消されている感じなんですよ。どうやったらこうなるんでしょうか？
ああ、たぶんブチョーは、最初３桁入力して確定しちゃっているのかもね、「６８９」を入力したらENTERで確定して、ハイフン入力しているつもりが長音になっちゃう。続けて６８９－４５２５って続けて入力すれば良いだけなのにね。
だから郵便番号辞書で変換できなくて、住所手入力したんじゃないか？もしくは〒の記号まで入力して変換しようとしていたとか。
かもですね。でもおかしいなー。やり方は全部目の前で見てもらったんですけど。
あと入会日が上と同じ場合、〃を入力してくれちゃっているんですよ。
もう全部の項目がおかしいんですよ。せっかく説明してメールにも書いているんですけど、やる事が謎すぎます。
手書きの感覚なんだろうね。ブチョーとしては見やすいように気を使ったんじゃない？って思っておこうよ。それより修正修正。
見栄えを整えるためだけの余計なスペースを除去する
まず名前に入力されている余計なスペースを消そう。文字間は半角スペースで、姓名の間は全角スペースになっているね。
だから半角スペースだけ消せばOKってことだよね。こういう場合は置換を使うと良いんだ。
噂のチカンですね！
B列を選択します。B列全体でも構いませんし、B1をアクティブセルとし[CTRL]+[SHIFT]+↓で範囲指定しても大丈夫です。
範囲指定した状態で、[CTRL]+[H]を押します。検索と置換のダイアログが表示されます。今回は半角と全角を区別して置換を行うので、オプションをクリックして下さい。
検索する文字列に半角スペースを入力します。置換後の文字列には何も入力しません。（入力されていたら消して下さい）
半角と全角を区別するにチェックを入れて、全て置換をクリックします。
ああーホントだ。検索・置換で一発ですね！でも一気に修正されるのは、失敗したらチョット怖いですね。
失敗したら、[CTRL]+[Z]で元に戻せるよ。ゾンビのように黄泉帰るから[Z]って覚えると良いよ！
何か大きな修正をする場合は事前に上書き保存しよう。[CTRL]+[Z]で戻せないような最悪の事態が起きても、保存しないで終了すれば何もなかった事にできるからね。マクロを使って一括修正したような場合は元に戻せないんだ。
半角カナを全角カナに変換する
半角を全角にするには、JIS関数を使うと良いよ。いったん別の列にJIS関数を使ってカナを全角する。H列をC列に「値だけコピー」してあげればOKだよ。こういう場合は列全体でなく必要な範囲だけコピー＆貼付けするようにしてね。
必要な部分だけ一瞬で範囲指定するには基礎が大切だよ。マウスだけだと件数が多いと時間がかかるよ。もちろん範囲指定の基礎はしっかり習得しているよね！！
た、たぶん。大丈夫です！
ちなみに全角を半角に変換するには、ASC関数を使うんだ。
問題だらけの郵便番号を半角に統一しよう
郵便番号はこんな風に数式を組み立てると一発で修正できるよ。
えー、説明省略しすぎじゃないですか？もう少し詳しく教えて下さい。
手を抜いたんじゃなくて、うまく数式を組み立てれば一発で出来るよって事をまず知って欲しかったんだ。
関数がネストされている、関数の引数に別の関数が使われているような場合は、一番内側のものから見ていくとわかりやすいかな。
まずは、MID関数で郵便番号の2文字目から8文字抜き出す。これはわかるかな？
説明を聞けば・・・。ギリギリ。
それをASC関数で半角にしている。SUBSTITUTE関数は、半角長音を半角ハイフンに置換しているんだ。まとめると、郵便番号のセルに入力されている値を、2文字目から8文字抜き出して、それを半角にしてから、半角長音を半角ハイフンに変換する。という意味の数式になるんだ。
センセイ！関数は苦手で難しいです・・・。
じゃあ、郵便番号のセルに入力されている値を、2文字目から8文字抜き出して、それを半角にする。ここまでは関数でできるようになろう。長音をハイフンに変更するのは、最初に[CTRL]+[H]で置換してしまう。これならどう？
なるほどー。色々な方法でできるんですね。
住所の余計なスペースを取り除く
住所のスペースを消すなら、もう大丈夫です。全角スペースを空白（何も入力しない）に置換にすれば良いんですよね。
正解！ただ、ビル名とかがある場合、番地との間にスペースを入れるよね。これも消えちゃうかも知れないから気をつけてね。件数は多くないと思うから、手作業で再度スペースを入れてあげよう。
それならムリにスペース消さなくても良いんじゃないですか？
よく気づいたね。意味がなければやらなくても良いと思うよ。でも今回はちょっと理由があるんだ。それはまた後で説明するね。
都道府県名が入力されていないものを抽出する
長野県が入力されていないの探すの大変すぎます。
そういう場合は数式を使おう。１件１件目視だとどうしても見きれないからね。
都道府県名で４文字なのは、神奈川県と和歌山県だけで、それ以外は全て３文字なんだ。
だから４文字目が県、３文字目が都・道・府・県でなければ、都道府県名が入力されていない可能性が極めて高い。それはわかるかな？
はい。なるほど。そうですね。
それを数式にするとこうなる。
AND関数を使って全ての条件が満たされているかどうか調べます。
MID(E2,4,1)で住所の4文字目から1文字取り出して、その文字が「県」以外か判定する。続けてMID(E2,3,1)で住所の3文字目から1文字取り出して、その文字が「都」「道」「府」「県」以外か判定する。という数式になります。
TRUEが返ってくる場合、都道府県という文字が3文字目にも4文字目にも無いという意味になります。
こんな使い方もあるんですね・・・。奥が深いですね。
数式が難しい場合は、「県」「都」という文字が3文字目にあるかどうかだけでも判定してみよう。それだけでも目視でチェックする件数が激減するからね。全てを１００％できなくても、できる事だけでもやると良いよ。それでも楽になるからね。
上と同じ場合、手書き感覚で〃と入力されてしまった場合は？
最後に上と同じ場合に「〃」と入力されちゃった場合の対応だね。これは手順が多いけど、やっている事はシンプルだから何度かやってみると簡単に習得できるよ。空白セルに上と同じ値を入れたい！というのは結構あるんだ。
まず、入会日の範囲指定を行います。列全体ではなく必要な部分だけ指定します。（その後の処理があるので、列全体は指定しないで下さい。）
[CTRL]+[H]で置換を行います。〃を空白（何も入力しない）に置換にします。
続けて[CTRL]+[G]を押し、ジャンプのダイアログを表示させ、セル選択をクリックします。
空白セルを選択しOKをクリックします。
このように、選択していた範囲で空白のセルだけを選択できます。（列全体を指定すると全ての空白セルになってしまうので大失敗します）
数式を指定します。先頭のセルに代表する形で入力します。今回の場合は上のセルをクリックするだけです。通常は[ENTER]のみで確定しますが、ここでは[CTRL]+[ENTER]で確定します。これが一番重要です。[CTRL]+[ENTER]で選択した範囲に一括して入力する事ができます。
このように、上のセルの値が表示されます。数式のままだと並べ替えをすると全てがおかしくなるので、すぐに値のみに変換して下さい。
空白セルだけ選択したような連続しない範囲では、コピー＆貼付けができないので、値のみに変換する際は範囲指定をしなおして下さい。
ホントに10分でできましたね。ありがとうございます。ショートカットキー便利ですね。でも覚えられるかな・・・。
[CTRL]+[Z]の元に戻すは、ゾンビのように黄泉帰るの[Z]、[CTRL]+[H]の置換は、変態の[H]（なぜならチカンだから）、[CTRL]+[G]のジャンプは、欲張りで何でも自分のものにするジャイアンの[G]、みたいなイメージで覚えると良いよ。
何ですかソレ！でも印象に残りますね。
ショートカットキーって覚えづらいけど、これも10回くらい使ってみるとすぐに習得できるものだから、最初はムリにでも使ってみると良いよ。
やっとみんなで入力したデータも１つのシートに統合できました。
いよいよ集計ですね。関数苦手だから大変そうだけど頑張ります！
いや次は集計するための準備みたいな感じかな。集計するためのキーを作る。このキーの作り方で集計するための数式の難度が変わるからね。
最後に
全６回でお届けするエクセルで会員管理について。
第２回目は入力されているデータを一括して修正する、入力する方法の解説になります。
文字数の関係で、株式会社と（株）としなければならない、見積書発行の際には株式会社と社名の間にスペースを入れたい。多くの人が入力するデータは顧客名だけとっても統一が難しかったりします。誰が悪い訳ではなくても、そうなってしまうものです。
そういった場合でも自在に対応できるよう、データを集計しやすい形に修正するスキルがあると色々なシーンで役立ちます。
タグ：初心者・初級者
