Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Excel 2016)
住所録抽出をしたい
投稿日時: 20/07/13 13:55:19
投稿者: tols777

いつもお世話になっております。
 
[Sheet1]
  A   B  C     D    E       F     G     H    I   J
1 姓  名  郵便番号 住所   会社郵便番号 会社住所 会社名   部署   役職 宛先種別 
2 〇〇 ◇◇ 111-1111 ○○都□□ 222-2222 ○○府□□ (株)△△ ◇◇事業部 主任 1
3 〇〇 ◇◇ 111-1112 ○○県□□ 222-2223 ○○県□□ (株)△△ ◇◇事業部 係長 2
4 〇〇 ◇◇ 111-1113 ○○都□□ 222-2224 ○○道□□ (株)△△ ◇◇事業部 課長 2
5 〇〇 ◇◇ 111-1114 ○○道□□                           1
6 〇〇 ◇◇ 111-1115 ○○府□□ 222-2226 ○○都□□ (株)△△ ◇◇事業部 主任 1
 
 
上記のようなデータ(CSVを開いたもの)があり、
やりたいこととしては「宛先種別」という情報が書かれた列を探して
"1"なら自宅住所(「郵便番号」「住所」)、
"2"なら会社住所(「会社郵便番号」「会社住所」「会社名」「部署」「役職」)の情報を別シートに表示したい。
 
↓最終表示イメージ
[Sheet2]
  A   B  C     D      E      F     G
1 〇〇 ◇◇ 111-1111 ○○都□□
2 〇〇 ◇◇ 222-2223 ○○県□□ (株)△△ ◇◇事業部 係長
3 〇〇 ◇◇ 222-2224 ○○道□□ (株)△△ ◇◇事業部 課長
4 〇〇 ◇◇ 111-1114 ○○道□□
5 〇〇 ◇◇ 111-1115 ○○府□□
 
VBAで小難しく考えていたのですが、関数だけでいけるのでは?と
思い直しお知恵をお貸し頂きたく投稿させて頂きました。
 
是非とも宜しくお願い致します。

回答
投稿日時: 20/07/13 15:52:14
投稿者: メジロ2

いろいろな方法がありますが、一案です。
 
自宅関連のシート
 
 A1: =IFERROR(INDEX(Sheet1!C$2:C$6,AGGREGATE(15,6,ROW($A$1:$A$5)/(Sheet1!$J$2:$J$6=1),ROW(A1))),"")
 
会社関連のシート
 
A1: =IFERROR(INDEX(Sheet1!E$2:E$6,AGGREGATE(15,6,ROW($A$1:$A$5)/(Sheet1!$J$2:$J$6=2),ROW(A1))),"")
 
両式を執拗数、右と下にコピーします。

回答
投稿日時: 20/07/13 16:58:52
投稿者: んなっと

Sheet2
 
   A   B     C      D      E      F   G
1 〇〇 ◇◇ 111-1111 ○○都□□               
2 〇〇 ◇◇ 222-2223 ○○県□□ (株)△△ ◇◇事業部 係長
3 〇〇 ◇◇ 222-2224 ○○道□□ (株)△△ ◇◇事業部 課長
4 〇〇 ◇◇ 111-1114 ○○道□□               
5 〇〇 ◇◇ 111-1115 ○○府□□               
 
C1
=IF(Sheet1!$J2=1,IF(COLUMN()>4,"",Sheet1!C2),Sheet1!E2)
右方向・→[G1まで] 下方向・↓

投稿日時: 20/07/13 17:13:45
投稿者: tols777

メジロ2 様
 
回答誠にありがとうございます。
 
教えていただいた内容で試させて頂きました。
以下のようにしたかったため、
 
[Sheet2]
  ↓姓 ↓名
  A   B  C     D      E      F     G
1 〇〇 ◇◇ 111-1111 ○○都□□
2 〇〇 ◇◇ 222-2223 ○○県□□ (株)△△ ◇◇事業部 係長
3 〇〇 ◇◇ 222-2224 ○○道□□ (株)△△ ◇◇事業部 課長
4 〇〇 ◇◇ 111-1114 ○○道□□
5 〇〇 ◇◇ 111-1115 ○○府□□
 
 
教えていただいたコードを
A1: =IFERROR(INDEX(Sheet1!C$2:C$6,AGGREGATE(15,6,ROW($A$1:$A$5)/(Sheet1!$J$2:$J$6=1),ROW(A1))),"")

A1: =IFERROR(INDEX(Sheet1!A$2:A$6,AGGREGATE(15,6,ROW($A$1:$A$5)/(Sheet1!$J$2:$J$6=1),ROW(A1))),"")
のようにさせて頂きコピペ致しました。
 
とても期待している近しいものになっておりました。ありがとうございます。
その上で今一度お知恵を拝借できればと思います。
 
1.「宛先種別」の列を探してその下の数字("1"or"2")によって判断したい。
 →例に書いたようなJ列に「宛先種別」が必ずあるわけではない為。
2.1つのシートに自宅住所宛と会社住所宛とを混在して表示したい。
3.自宅住所宛の場合、上記のE,F,G列には空白としたい。
4.元のCSVデータが1000行になっても表示できるようにしたい。
 
 
お手数ですが、宜しくお願い致します。[/quote]

投稿日時: 20/07/13 17:33:15
投稿者: tols777

んなっと さんの引用:
Sheet2
 
   A   B     C      D      E      F   G
1 〇〇 ◇◇ 111-1111 ○○都□□               
2 〇〇 ◇◇ 222-2223 ○○県□□ (株)△△ ◇◇事業部 係長
3 〇〇 ◇◇ 222-2224 ○○道□□ (株)△△ ◇◇事業部 課長
4 〇〇 ◇◇ 111-1114 ○○道□□               
5 〇〇 ◇◇ 111-1115 ○○府□□               
 
C1
=IF(Sheet1!$J2=1,IF(COLUMN()>4,"",Sheet1!C2),Sheet1!E2)
右方向・→[G1まで] 下方向・↓

 
んなっと 様
 
ありがとうございます。
おそらくメジロ2様と同様に上手に質問が出来ていなかったようで申し訳ございません。
Sheet2のA列にはSheet1の「姓」、Sheet2のB列にはSheet1の「名」が入ってきます。
 
んなっと様の数式を元に
A1
=IF(Sheet1!$J2=1,IF(COLUMN()>4,"",Sheet1!A2),Sheet1!A2)
B1
=IF(Sheet1!$J2=1,IF(COLUMN()>4,"",Sheet1!B2),Sheet1!B2)
のようにしましたが、よくよく考えれば
A1
=Sheet1!A2
B1
=Sheet1!B2
だけで良い感じですかね???
 
あと1点、重ねての追記になってしまいますが、
〇「宛先種別」の列を探してその下の数字("1"or"2")によって判断したい。
 
ここの部分に関して再度知恵を拝借できればと思います。
 
宜しくお願い致します。

回答
投稿日時: 20/07/13 17:38:37
投稿者: んなっと

2回目以降の書き込み、よく読んでいませんが...
 
=IF(INDEX(Sheet1!2:2,MATCH("宛先種別",Sheet1!$1:$1,0))=1,IF(COLUMN()>4,"",Sheet1!C2),Sheet1!E2)

投稿日時: 20/07/13 17:50:27
投稿者: tols777

んなっと さんの引用:
2回目以降の書き込み、よく読んでいませんが...
 
=IF(INDEX(Sheet1!2:2,MATCH("宛先種別",Sheet1!$1:$1,0))=1,IF(COLUMN()>4,"",Sheet1!C2),Sheet1!E2)

 
んなっと 様
 
再度、ありがとうございます。
期待する動作が出来ました!
 
それぞれの関数の意味に関してはこれから勉強させて頂きます。
 
またよろしくお願いいたします。