エクセルへの表化を効率よくやるには？

質問

今仕事でＤＭ発送しています。
リストを作るのに時間がかかって仕方ありません。
ＨＰのサイト　例えば、タウンページなどの
住所が載っているサイトから一気に
指定した場所へエクセルの表へコピペする方法は
ないでしょうか？
時間がかかって効率が悪くて仕方ありません。
よろしくお願い致します。

回答No.12

noname#118337 · ベストアンサー

>ＴＥＬ000-0000のＴＥＬっていう文字を消すことは可能でしょうか？

=CONCATENATE(A2,",",MID(A3,6,LEN(A3)),",",CONCATENATE(MID(A4,2,3),MID(A4,6,3)),",",MID(A4,11,LEN(A4)),",",A5)

A3のセルの文字をMIDを使って一部抜き出すことで可能です。
文字数でカウントしてますので、もしかしたらページによって"L 06-****"のように、TELの文字が一部残ったりすることもあるかもしれません。
その場合は、MID(A3,6,LEN(A3))の6を7にしたり5にしたりで変更してみてください★
慣れてきてよかったです(^-^)

お礼

すみません！すっかり遅くなりました！！
本とーーーーーーーーーーーーーーーーーーに
ありがとうございました！！！
これで作業が本当に楽になりました。
感謝してもし尽くせません！！！
本当にありがとうございました！！！！

回答No.11

noname#118337

>えっと郵便番号の「‐」を消すときに同時に〒マークも消したいです。自分でどうにか頑張ってみましたが、だめでした…

あと指示されたようにやってみたら、
いい感じにできたのですが、郵便番号と住所がつながっていました。この二つも違うセルにしたいのですが・・・

それだったら、SUBSTITUTEを使うのではなく、MID関数を利用する方法にしましょう

B2の式を下記のように設定してみてください
No.9の(10)です

=CONCATENATE(A2,",",A3,",",CONCATENATE(MID(A4,2,3),MID(A4,6,3)),",",MID(A4,11,LEN(A4)),",",A5)

CONCATENATE(MID(A4,2,3),MID(A4,6,3))は郵便番号・住所欄の2桁目から3文字抜き出したものと、6文字目から3文字抜き出したものを結合するという意味

MID(A4,11,LEN(A4))は郵便番号・住所欄の11桁目から最後の文字まで抜き出すということです。

この２つの間に","を入れることで、セルが分かれます

これでいけるはずですー。

お礼

毎回毎回ありがとうございます！
だいぶ完成に近づいてきました！！

・・・・が、
あともうひとつだけお願いしてもよろしいでしょうか？
本当にわがままで申し訳ありません。
ＴＥＬ000-0000のＴＥＬっていう文字を消すことは可能でしょうか？

宜しくお願い致します。

補足

だいぶというかそれ以外は本当に完璧です！！
しかもおかげさまで作業もだいぶ慣れてきました！
もう見なくても自分でできるようになってきて嬉しいです。
本当に慣れると早いですね！
もう何回お礼を言っても足りないくらいです！！！

あとひとつ宜しくお願い致します。

回答No.10

noname#118337

おはようございます！

＞Ｑ１　手順6の空白以外を選択することができなかったので、これは空白だけ消しました。（結局一緒？）

　結局一緒です。
　確認ですが、オートフィルタで、(空白以外のセル)を選択したら空白以外のが抽出されますよね？
　それで、抽出１行目～抽出最終行を選んで、コピーしてもだめですか？
　切り取りはダメ(Excelの仕様)だったと思うのですが、コピーはできたかと。
　やはりバージョンの違いでしょうか。
　Excelのバージョンは何ですが？
　メニューの[ヘルプ]→[バージョン情報]で確認できます

>Ｑ２　11でつまりました。特に行数分ドラッグするっていうのが、意味がわからず止まってしまいました・・・あと少しで完成するのに！！
B1に式が入っていて、B2～B5にその式をコピーしたいときの処理と同じです。
　つまり、B1を選択してセルの右下にカーソルを合わせたらカーソルが＋表示になると思います。この状態で左クリックし、B2～B5にドラッグすればB2～B5に式が入ります。

　これの応用編で、例えばB1,B2に異なる式がはいっており、B3にはB1,B4にはB2と同じ式を入れたい場合、B2とB3を選択後、セルの右下にカーソルを合わせて＋表示後、B3,B4にドラッグすれば希望通りになります。

　私のしているのは、B2～B5を選択後、セルの右下にカーソル合わせて＋表示後、最終行までドラッグしましょうということなのです。

　ドラッグっていう表現が分かりにくかったですね。確かこの動作のことを表す用語があったけど、忘れちゃいました。
　ごめんなさい(^-^;)

>Ｑ３　SUBSTITUTEはツールバーのどこに存在しているのですか？それがわかりません。それとも毎回自分で打ち込むのですか？

　ツールバーには存在しません。
　メニューの[挿入]→[関数]で、<関数の分類>の<文字列操作>で出てきます。バージョンが2003なら<関数の検索>で"置換"で検索すれば出てきます。

　この[挿入]→[関数]ですると、すごくいろんな関数が見れると思います。簡単な説明も出てくるので、その他役立つ関数も見つけれると思いますよ(^-^)

それと、コピー、ペースト、全て選択ですが、[Ctrl]ボタンと[c]ボタンを同時に押すとコピー、[Ctrl]ボタンと[v]ボタンを同時に押すとペースト、[Ctrl]ボタンと[a]ボタンを同時に押すと全て選択ができるので、慣れたら使うと作業が早くなりますよ★

お礼

ありがとうございます！
だんだんわかってきたような気がします！
それでもあともう一歩！って感じです。（泣）

えっと郵便番号の「‐」を消すときに同時に〒マークも消したいです。自分でどうにか頑張ってみましたが、だめでした…

あと指示されたようにやってみたら、
いい感じにできたのですが、郵便番号と住所がつながっていました。この二つも違うセルにしたいのですが・・・

前回質問した意味はわかりました。
ありがとうございました！！

回答No.9

noname#118337

iタウンページでしたら、いきなり,区切りのデータっていうのは難しそうですね。
しかも、ものによって、行数が異なるので、一筋縄にはいかなさそうですね。
A列、B列のような表形式でなっているページなら、できそうですが。。
でも、いろいろなNTTが出してるページなので、信頼性も高そうですもんね。

私だったらこうするっていうやり方を書きます。
例として、下のページで説明します。
http://itp.ne.jp/servlet/jp.ne.itp.sear.SGSSVWebDspCtrl?Gyoumu_cate=3&Media_cate=populer&cont_id=a00&svc=1201&navi=search&init_word=%90%7D%8F%91%8A%D9&init_addr=%91%E5%8D%E3%8E%73&proc_id=r1&focus_theme=Z431700
(1)必要な電話帳データを全て選択し、コピーする。
　(アジア図書館～大阪市立鶴見図書館の最終行まで)
(2)テキストエディタに貼り付ける
(3)テキストエディタのデータを全て選択＋コピーし、エクセルのSheet1に貼り付ける。
　(画像等余分なコピーを省くため)
(4)エクセルの１行目に１行挿入し、aと入力する。
(5)A1を選択し、オートフィルタを設定する
(6)空白行以外を選択する
(7)データが空白行以外のもののみになるので、それを全て選択し、Sheet2の２行目に貼り付ける。
(8)Sheet2の1行目にaと入力し、データを再びオートフィルタし、オプションを選択し、[httpから始まらない]と設定する。
(9)これをまた全て選択、コピーし、Sheet3に貼り付ける
★これで、全てのデータの行数が４行にそろったと思います

(10)A2からデータが始まっているので、B2に=CONCATENATE(A2,",",A3,",",SUBSTITUTE(A4,"-",""),",",A5)"と入力する
　★データが,区切りのデータになり、郵便番号は-が省かれている
(11)B2～B5を選択して、行数分ドラッグする。
　★これで、B2,B6,B10・・・のみに数式が入っているようになるはずです
(12)B1にbと入力し、再度オートフィルタを設定し、空白行以外で絞る
(13)絞ったデータを全て選択、コピーし、テキストファイルに貼り付ける
(14)(13)のテキストファイルをcsv形式で保存する

このやり方だと手順は多いけど、大量のデータを一度で編集することができます。
慣れたら(1)の時に、他のページもまとめてエクセルに貼り付けておけば、ＯＫだと思います

SUBSTITUTE使えませんか？
バージョンが違うのかもしれませんね。
だったら、ちょっと長いですが、SUBSTITUTE(・・・)のとこを
=CONCATENATE(LEFT(A4,4),MID(A4,6,LEN(A4)))にしてください。

置換が使えないので、必要なデータのみ抜き出して結合するっていうやり方です。

CONCATENATEは文字をつなげる
LEFTは文字列を左から？文字分抽出する。
　★ここだと、A4の文字列を左から4文字抜き出す(〒000まで)
MIDは文字列を指定数から指定数抜き出す
　★ここだと、A4も文字列と6つめからLEN(A4)分抜き出す(-以降の最後まで)
LENは文字列の長さ
　★ここだとA4の文字列の長さ
です。

ややこしく感じるかもしれませんが、慣れです！慣れ！

お礼

丁寧な説明ありがとうございました！
・・・にも関わらずつまってしまった自分が本当に情けないです。（泣）

Ｑ１　手順6の空白以外を選択することができなかったので、これは空白だけ消しました。（結局一緒？）空白以外を選択することはできましたが、その後の
アクション（コピーとか切り取り）をしようと思ってもできなかったからです。

Ｑ２　11でつまりました。特に行数分ドラッグするっていうのが、意味がわからず止まってしまいました・・・あと少しで完成するのに！！

Ｑ３　SUBSTITUTEはツールバーのどこに存在しているのですか？それがわかりません。それとも毎回自分で打ち込むのですか？

以上よろしくお願い致します。

回答No.8

noname#118337

NO.2です。
タウンページってiタウンページですか？？
http://itp.ne.jp/
どのＨＰか教えてくれたら、もう少し詳しくアドバイスできると思います。

郵便番号の-はSUBSTITUTE関数という置換の関数を使えば簡単にできます。
=SUBSTITUTE(B2,"-","")だったら、B2の文字列の-をなしにするということになります。
つまり、=SUBSTITUTE("333-333","-","+")だったら、333+333っていうことです。

お礼

ありがとうございます！！
そうです！iタウンページですね。
他にもリストアップするのに、いいサイトがあったらいいのですが、（何かご存知ですか？）
今のところiタウンページですね。

エクセルのSUBSTITUTE関数というのはどこにあるのでしょうか？お願いいたします。

回答No.7

noname#118337

No.2です。
私が書き込む前にかなり詳しい回答がされましたね(^-^)

いくつかもう少し簡単にできそうな方法が思いつくので、書き込みます。

まず一つ気になったのですが、No.1さんのやり方でExcelにエクスポート後した場合、表形式になっていませんか？
例えば、No.6さんのデータをお借りして説明すると、
A列,B列,C列,D列
○○会社,〒123-4567,沖縄県○○,01-2345-6789
という感じで。

なっているなら、この状態でcsv形式で保存すれば、手作業でカンマを入れる作業が省けます。

余談ですが、csv形式で保存した場合、ダブルクリックして開けばExcelで表示されます。
なので、余分な列があった場合、例えばC列の郵便番号がいらない場合は、ダブルクリックでExcelで表示し、C列を削除し、上書き保存すれば簡単にいらない列を削除することができます。
上書き保存時、「●●.csvにはCSVと互換性のない・・・」とインフォメーションダイアログが表示されると思いますが、[このまま保存する]の[はい]で大丈夫ですよ。

それと、これもNo.6さんのデータをお借りしての説明になりますが、0が消えてしまう場合、もちろん手作業で直してもいいのですがデータが多くて大変な場合、関数を利用する手もあります。

例えば、Excelで表示すると、01-2345-6789が1-2345-6789になってしまった場合。
このデータがD列の2行目にあるとしたら、E列2行目に[=CONCATENATE("0",D2)]という式を入れてみてください。
01-2345-6789になるはずです。
このE列2行目をデータ行数分コピーすれば、全ての行が0つきのデータになります。

このCONCATENATEは文字を連結するという関数で、CONCATENATE("A","B","C")ならABCという文字列が表示されるというものです。

関数があるままだと、その後扱いにくいこともあるので、関数を文字列に変更しましょう。
[=CONCATENATE("0",D2)]で01-・・・に表示されているデータを文字列の01-・・・に変換するというものです。

その方法は、E列全体をコピーし、メニューの[編集]→[形式を選択して貼り付け]を選択し、[貼り付け]項目で[値]を選択してOKボタンを押してください。

Excelの特性と、csvの特性を利用してデータ処理すれば簡単にできるようになると思います。
がんばってくださいね(^-^)

お礼

ありがとうございます！！

まず一つ気になったのですが、No.1さんのやり方でExcelにエクスポート後した場合、表形式になっていませんか？
例えば、No.6さんのデータをお借りして説明すると、
A列,B列,C列,D列
○○会社,〒123-4567,沖縄県○○,01-2345-6789
という感じで。

なってないんですー！（泣）
それがなってたら、一番楽なんですよねー。
どうやったらいいのでしょうか？
ちなみに今コピーしてるのはタウンページの住所録なんですが。

それで郵便番号の「-」ハイフンが消えたら、
もう言うことなしですんごい早くなりますね！！

回答No.6

crepon133

○○会社,〒123-4567,沖縄県○○,01-2345-6789
↑　この一行をエディターに貼り付けて名前を sample.csv で保存してみてください。
sample.csv で保存できないときは、
sample.txt で保存してからファイル名を sample.csv と書き替えます。
エクセルで開くとA1,B1,C1,D1に入っているはずです。

> あと電話番号を0で指定したら、
> 郵便番号の0にも反応してしまいました。
場合によっては住所の1にも反応してしまい、
とんでもないところにカンマが入ってしまうことがあります。

同じ数字がある場合の置換は難しいので、
1桁ではなく3桁や4桁など他の数字列とダブらない検索文字列にするか、
あきらめて、手動でカンマを入れてください。

お礼

何度も何度もありがとうございました！
使ううちに慣れてくるかと思います！
本当ありがとうございました！！

補足

申し訳ありません。
最後にもうひとつだけ。
ラベル化するのに「-」のみ邪魔で、
エクセル表内の　郵便番号000-000　の「-」のみ
を全て消したい場合に一気に全て消すことは
できますか？
今までひとつひとつ手動でやっていたので・・・

最後の最後に宜しくお願いいたします。

回答No.5

crepon133

エクセル→ファイル→名前を付けて保存→
ファイルの種類「CSV（カンマ区切り）（*.csv）」で保存　→ test.csv

test.csvをメモ帳で開く
○○会社〒123-4567沖縄県○○01-2345-6789

各項目を「,」（半角カンマ）で区切ってやればいいのです。
○○会社,〒123-4567,沖縄県○○,01-2345-6789
項目が空欄の場合は
○○会社,,沖縄県○○,01-2345-6789　のようになります

編集→置換→　検索する文字列：〒　／置換後の文字列：,〒　→すべて置換
○○会社,〒123-4567沖縄県○○01-2345-6789
編集→置換→　検索する文字列：沖縄県　／置換後の文字列：,沖縄県　→すべて置換
○○会社,〒123-4567,沖縄県○○01-2345-6789

エクセルで開いたとき、
電話番号など「0」から始まる文字列は、先頭の「0」が消えることがあります。
その時はファイルネームをtest.csvからtest.txtに変更。
エクセル→ファイル→開く→ファイルを指定→
ウィザード1/3：そのまま
ウィザード2/3：区切り文字「カンマ」
ウィザード3/3：全ての列のデータ形式を文字列に変更する
こうして読み込むと先頭の「0」が消えるのを防げませ。

お礼

丁寧な説明のおかげ半分できたと思います！
ありがとうございます！

でも、まだ質問があります。（申し訳ありません）
置換によってセルは区切られたのですが、
それをエクセルのＡ，Ｂ，Ｃ，のそれぞれの
セルに分けることは可能でしょうか？
今はセルは違っても同じＢの所に入ってしまっている
状態です。できたら

あと電話番号を0で指定したら、
郵便番号の0にも反応してしまいました。
これはどうしたらよいでしょうか？

宜しくお願いいたします。

よろしくお願いいたします。

回答No.4

crepon133

データをカンマ（,）で区切って並べたファイル形式で、中身は普通のテキストです。
原始的ですが汎用性が高く、表計算ソフトやデータベースソフト、
年賀状ソフトの住所録などにも読み込めます。

文字のみのファイル(テキストファイル)を作成・編集するためのソフトです。
文書の下書きをしたり、ホームページ用のHTMLファイルを作成・編集したり、
プログラムを作成したりするときに使います。
Windowsのメモ帳もテキストエディタの仲間です。

TeraPad
http://www.forest.impress.co.jp/lib/offc/document/txteditor/terapad.html

お礼

何度も本当にありがとうございます。
が、やってみましたが、ちょっとよくわかりませんでした。（涙）
とりあえず、TerePadはダウンロードしてみましたが・・
とりあえず、ＨＰ上の住所録をエクセルへ移すのには成功しました。（ＮＯ１の方の方法です。）
その後の置換？の仕方がやっぱりよくわかりません。
そのエクセルの表を保存して、それをメモパッドにまたコピペしてカンマをいれてやってみようとしましたが、だめでした・・・
もう一度教えて頂けますか？　宜しくお願いいたします。

回答No.3

crepon133

企業名、住所、XX、YY、
それぞれの項目の先頭文字の共通点を見つけてください。
たとえば住所の先頭が〒で始まっているとか、都道府県名で始まるとか、
電話番号の先頭に「（0」があるとか・・・

いくつかの共通点があるようなら、
エクセルファイルを名前を付けて保存で、
「,カンマ」区切りのCSVファイルで保存します。
カンマがエクセルではセルの区切りになります。
このファイルをテキストエディタで開き置換でカンマを入れます。

置換対象：〒　／　置換後：,〒
置換対象：東京都　／　置換後：,東京都
置換対象：(03　／　,(03
などなど・・・

無理して全てを置換しようとしないで、
テキストエディタで区切りたい所に「,」を入れてください。
エクセル上でカット＆コピーするより作業は早いと思います。

お礼

ありがとうございます。
早そうですね！私に実践できれば・・・（笑）

えっとＣＳＶファイルって何ですか？
そしてテキストエディタとはなんでしょうか？
本当質問ばっかりですみません！！

よろしくお願い致します！

回答No.2

noname#118337

エクセル開いて、サイトを全て選択してコピー、エクセルにペーストじゃダメってことですか？

だとしたら、指定ファイルに直接ＤＬは難しいかもしれませんが、テキストエディタなどを使ってマクロで編集後にエクセルにコピペする等したら早くできますよ。

それとか、ＶＢＡができて、固定サイトからのＤＬなら、コピペするとこまでは手作業でして、
書式等揃えるのをＶＢＡで書いてしまえば、
その後の作業はとっても楽です。

どういう状況でダメで時間がかかる等、もう少し詳しく教えてくれると詳しくアドバイスできるかと思います。

お礼

回答ありがとうございます！！
しかし私パソコンあまり詳しくないので、
マクロ？？ＶＢＡ？？といった感じでまったくわかりません。（泣）

まずサイトを全部コピペするといらない情報まで入ってきてしまいます。

解決したい点としては
(1)ＨＰ上の住所を一つ一つコピペするのが面倒。一気に自分のほしい情報だけ抜き取ることはできないのか？
(2)それをエクセルの表（企業名、住所などと分類されています。）に取り込むこと。
しかもできるのであれば、その分類された項目に分けることができると、もっと助かります。

ＮＯ１の方の方法でも大変助かったのですが、
データを取り込んだ後（取り込んだデータは全てＡに入ってしまいました）に、また分類された項目（Ｂ，Ｃ）に貼り付けなおさなきゃいけないので、結局またそこで一つ一つコピペすることになってしまいます。

以上わかっていただけましたでしょうか？
説明うまくできず申し訳ないですが、よろしくお願いいたします。