住所シール

内容 †

内容
目的
入力イメージ(「名簿」シート)
- 「レ」のプルダウンについて補足
出力イメージ(「シール」シート)
Excelファイル
ソース?イメージ
- 2008年7月16日に思いついた対処
改版履歴

目的 †

名簿から任意の人を拾ってきて、住所シールにプリントするのに適する形に整形する。

入力イメージ(「名簿」シート) †

  	         名前　　　	郵便番号	住所
レ	1	ああ　あああ	111-1111	千葉県ｘｘ市ｘｘｘ1-1-11
	2	いい　いいい	111-1112	千葉県ｘｘ市ｘｘｘ1-1-12
レ	3	うう　ううう	111-1113	千葉県ｘｘ市ｘｘｘ1-1-13
レ	4	ええ　えええ	111-1114	千葉県ｘｘ市ｘｘｘ1-1-14
レ	5	おお　おお	111-1115	千葉県ｘｘ市ｘｘｘ1-1-15
	6	かか　かかか	111-1116	千葉県ｘｘ市ｘｘｘ1-1-16
     ・・・・・・・・・・・・・・
     ・・・・・・・・・・・・・・

使用する行のA列にレを入力。(プルダウンでレを選択。)

↑

「レ」のプルダウンについて補足 †

もちろん「♪レ　レ　レイク」にならってチェックを「レ」にしました。

というのは半分冗談なのだけど、ほんとのチェックボックスはマクロ組まないとできないようで、このような実装にしました。
マクロはやってもいいんだけど、開く時に「ウイルスあるかもだけどいい？」って聞かれるのがうざいし、とりあえず、なるべく普通の関数でるのを試してみたんですよ。

で、「名簿」シートのA列が「レ」になってるところを拾って「シール」シートに出力するようにしているのだけど、これが空白と「レ」を選ぶプルダウンメニューになっているので、
確かにチェックボックスと比べてアクションが多くてめんどくさい。
すばやく「レ」を変えるには、「レ」のセルをCtr+Cでコピーして、つけたいところにCtr+Vでコピーして行って、外したいところは空白を同じようにコピーしてペーストしていく、
とかかしら。これならそんなにストレスないと思う。
ただ、空白をコピーする時は、A列のプルダウンが備わっているセルをコピーするように気をつけないとプルダウン情報が消えてしまう。(まあ、消えてもいいのだけども。)

連続の場合は、「レ」のセルの右下の■をつかんでぴゅーって下に引っ張れば連続で「レ」になる。この場合はチェックボックスより便利。連続空白の場合も同じ。

↑

出力イメージ(「シール」シート) †

名簿シートの4の行にレがあれば、その行の住所を拾ってきて

〒 111-1114
　千葉県ｘｘ市ｘｘｘ1-1-14
　　　　　　　　　ええ　えええ 様

と出力する。
レのついている行、25行について、「シール」シートのF2～J6に5×5で出力する。

ちなみにOFFSET関数を使ってVLOOKUPの範囲を指定しているので、名簿が今後どんなに縦長になっても自動対応。

↑

Excelファイル †

住所シール.xls

↑

ソース?イメージ †

基本は

="〒 "&IF(VLOOKUP(A2,OFFSET(名簿!$A$2,0,0,COUNTA(名簿!$A:$A),4),3,FALSE)="","",VLOOKUP(A2,OFFSET(名簿!$A$2,0,0,COUNTA(名簿!$A:$A),4),3,FALSE))&CHAR(10)&"　"&IF(VLOOKUP(A2,OFFSET(名簿!$A$2,0,0,COUNTA(名簿!$A:$A),4),4,FALSE)="","",VLOOKUP(A2,OFFSET(名簿!$A$2,0,0,COUNTA(名簿!$A:$A),4),4,FALSE))&CHAR(10)&"　　　　　　　　　"&IF(VLOOKUP(A2,OFFSET(名簿!$A$2,0,0,COUNTA(名簿!$A:$A),4),2,FALSE)="","",VLOOKUP(A2,OFFSET(名簿!$A$2,0,0,COUNTA(名簿!$A:$A),4),2,FALSE))&" 様"

と書かれているだけ。

シールシートのA2～E6に書かれている数字と同じ行を名簿シートから拾ってきて、その行の住所をF2～J6に出力するようにしている。

↑

2008年7月16日に思いついた対処 †

シールシートのA2には

=VLOOKUP("レ",OFFSET(名簿!$A$2,0,0,COUNTA(名簿!$B:$B),2),2,FALSE)

A3には

=VLOOKUP("レ",OFFSET(名簿!$A$2,A2,0,COUNTA(名簿!$B:$B),2),2,FALSE)

と書かれている。
前の検索値を次のOFFSETの行ずらしの値に利用しているところがポイント。
これにより、A列でレが複数存在してもVLOOKUPを使用することが出来る。

↑

改版履歴 †

2008年7月13日深夜作成。
2008年7月14日深夜多少修正。
　・出力が1列だったのを5×5に。
　　　→関連してCOUNTA(名簿!A:A)をCOUNTA(名簿!$A:$A)に。
　・改ページプレビューを保存。
住所シール_070814.xls
2008年7月16日、頑張って考えて修正。
　・シールシートで使用する行数を指定するのではなく、名簿シートのチェックで指定するように変更。
　　→もう、マクロしかないかと思ったら関数で出来た！嬉しい！(このアイディアを思いついた!)
住所シール_080716.xls
2008年7月16日深夜バグ修正。
　「シール」シートA2～E6の
```
=VLOOKUP("レ",OFFSET(名簿!$A$2,0,0,COUNTA(名簿!$A:$A),2),2,FALSE)
=VLOOKUP("レ",OFFSET(名簿!$A$2,A2,0,COUNTA(名簿!$A:$A),2),2,FALSE)
```
等のCOUNTAをCOUNTA(名簿!$B:$B)に変更。
　→COUNTA(名簿!$A:$A)のままだと、「レ」の間が広く開いた時に、A列長が実際よりも短いと判断されてしまっていた。
住所シール.xls