Excel (一般機能)

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

 
(Windows 10 Pro : Excel 2010)
別シートのデータからを抽出
投稿日時: 19/04/02 01:17:04
投稿者: さーびす

[color=red]下記のデータをAシートからBシートへ抽出したいんですがどのような関数を使えばいいでしょうか?
 
【Aシート】
 
 ABC区分 / 部品番号 / 金額
  A     TSJ326    100
  B     TRR333 50
    A TSJ568 300
 
 
集計シート【Bシート】
 金額 / ABC区分/ 部品番号
 100   A   TSJ326 ← 区分が「A」で金額、部品番号も表示したいです。
                     *区分A抽出したいです。
 
説明不足かもしれませんが、ご教授よろしくお願いいたします。

回答
投稿日時: 19/04/02 08:53:03
投稿者: WinArrow
投稿者のウェブサイトに移動

こんな感じで・・・
 
部品番号取得の場合
 
=IFERROR(VLOOKUP(B2,INDEX(Sheet1!$A$2:$A$5,MATCH(B2,Sheet1!$A$2:$A$5,0)+ROW(Sheet1!A1)-1):Sheet1!$B$5,2,FALSE),"")

回答
投稿日時: 19/04/02 09:28:14
投稿者: んなっと

こんなのも。
 
●A シート
 
     A     B   C
1 ABC区分 部品番号 金額
2    A  TSJ326  100
3    B  TRR333  50
4    A  TSJ568  300
5    B  TRR334  550
6    A  TSJ569  800
7    B  TRR335 1050
8    A  TSJ570 1300
 
●B シート
 
   A    B     C
1 金額 ABC区分 部品番号
2  100    A  TSJ326
3  300    A  TSJ568
4  800    A  TSJ569
5 1300    A  TSJ570
 
A2
=IFERROR(INDEX(A!C:C,AGGREGATE(15,6,ROW($2:$500)/(A!$A$2:$A$500=$B$2),ROW(A1))),"")
下方向・↓
B3
=IF(C3="","",$B$2)
下方向・↓
C2
=IFERROR(INDEX(A!B:B,AGGREGATE(15,6,ROW($2:$500)/(A!$A$2:$A$500=$B$2),ROW(C1))),"")
下方向・↓

回答
投稿日時: 19/04/02 17:34:27
投稿者: Mike

ガイドマップ さんの引用:
*区分A抽出したいです。
ホントかなぁ〜?それを信じて・・・
SheetA             SheetB
  A    B   C        A   B    C
1 ABC区分 部品番号 金額     1 金額 ABC区分 部品番号
2 A    TSJ326  100     2 100 A    TSJ326
3 B    TRR333   50
4 A    TSJ568  300
SheetB において、
B2: =INDEX(SheetA!A:A,MAX(IF((SheetA!C:C=A2)*(SheetA!B:B=C2),ROW(SheetA!A:A))))
 ̄ ̄【お断り】上式は必
ず配列数式として入力のこと

回答
投稿日時: 19/04/02 23:07:27
投稿者: xls-hashimoto

作業列として「A」の存在行を計算して、必要なデータを表示する方法です。
 
【Aシート】

ABC区分	部品番号	金額 
A	TSJ326	100
B	TRR333	50
A	TSJ568	300
B	TRR334	550
A	TSJ569	800
B	TRR335	1050
A	TSJ570	1300

 
【Bシート】
存在行	金額 	ABC区分	部品番号
2	100	A	TSJ326
4	300	A	TSJ568
6	800	A	TSJ569
8	1300	A	TSJ570

 
A2
=IF(A1="","",IFERROR(MATCH($C$2,OFFSET(A!$A$1,SUM(A1),,COUNTA(A!$A:$A)-SUM(A1),1),0)+SUM(A1),""))
下にコピー
 
B2
=IF(A2="","",INDEX(A!C:C,A2))
下にコピー
 
C3
=IF(A3="","",INDEX(A!A:A,A3))
下にコピー
 
D2
=IF(A2="","",INDEX(A!B:B,A2))
下にコピー
 
そのデータが何行目にあるか、わかったほうが良い場合に使います。
C列の計算で、確かに「A」であることがわかります。
単純な計算なのでチェックが一目ですみます。

投稿日時: 19/04/03 00:13:36
投稿者: さーびす

Mike さんの引用:
ガイドマップ さんの引用:
*区分A抽出したいです。
ホントかなぁ〜?それを信じて・・・
SheetA             SheetB
  A    B   C        A   B    C
1 ABC区分 部品番号 金額     1 金額 ABC区分 部品番号
2 A    TSJ326  100     2 100 A    TSJ326
3 B    TRR333   50
4 A    TSJ568  300
SheetB において、
B2: =INDEX(SheetA!A:A,MAX(IF((SheetA!C:C=A2)*(SheetA!B:B=C2),ROW(SheetA!A:A))))
 ̄ ̄【お断り】上式は必
ず配列数式として入力のこと

 
Mike様
 
 ありがとうございます。
 金額、部品番号を表示させた場合は、どうしたらいいでしょうか?
 
 
ガイドマップ
 

回答
投稿日時: 19/04/03 11:04:19
投稿者: Mike

ヤッパリ違ってましたかぁ。読解力が貧弱なワ・タ・シ(*^.^*)
  
「*区分A抽出したい」SheetB のレイアウトを私なら此処の添付図のようにします。
区分の分かり切っている「A」を必要以上に登場させることもアンメーに、と思うから。
 
 ̄ ̄1.SheetA において、範囲 A1:C100 を選択 ⇒ Alt+MC ⇒“上端行”だけ
 ̄ ̄ ̄ ̄にチェック入れ ⇒ [OK]
 ̄ ̄2.SheetB において、
 ̄ ̄ ̄ ̄次式を入力したセル A4 を右隣および下方にズズーッとオートフィル
 ̄ ̄ ̄ ̄=IFERROR(INDEX(INDIRECT(A$3),SMALL(IF(ABC区分=$B$1,ROW(ABC区分)),ROW(A1))-1),"")
 ̄ ̄ ̄ ̄【お断り】上式は必ず配列数式として入力のこと(配列数式を嫌うなら、
 ̄ ̄ ̄ ̄此処の返信全部無視されたし)

SheetB
    A     B
1 ABC区分 A
2
3 金額  部品番号
4 100   TSJ326
5 300   TSJ568

投稿日時: 19/04/05 00:37:55
投稿者: さーびす

xls-hashimoto さんの引用:
作業列として「A」の存在行を計算して、必要なデータを表示する方法です。
 
【Aシート】
ABC区分	部品番号	金額 
A	TSJ326	100
B	TRR333	50
A	TSJ568	300
B	TRR334	550
A	TSJ569	800
B	TRR335	1050
A	TSJ570	1300

 
【Bシート】
存在行	金額 	ABC区分	部品番号
2	100	A	TSJ326
4	300	A	TSJ568
6	800	A	TSJ569
8	1300	A	TSJ570

 
A2
=IF(A1="","",IFERROR(MATCH($C$2,OFFSET(A!$A$1,SUM(A1),,COUNTA(A!$A:$A)-SUM(A1),1),0)+SUM(A1),""))
下にコピー
 
B2
=IF(A2="","",INDEX(A!C:C,A2))
下にコピー
 
C3
=IF(A3="","",INDEX(A!A:A,A3))
下にコピー
 
D2
=IF(A2="","",INDEX(A!B:B,A2))
下にコピー
 
そのデータが何行目にあるか、わかったほうが良い場合に使います。
C列の計算で、確かに「A」であることがわかります。
単純な計算なのでチェックが一目ですみます。

 
 
xls-hashimoto様
 ありがとうございました。
 試してみました、いろいろと勉強になりました。
 ありがとうございました。
 
ガイドマップ
 
 
 
 

投稿日時: 19/04/05 00:39:27
投稿者: さーびす

んなっと さんの引用:
こんなのも。
 
●A シート
 
     A     B   C
1 ABC区分 部品番号 金額
2    A  TSJ326  100
3    B  TRR333  50
4    A  TSJ568  300
5    B  TRR334  550
6    A  TSJ569  800
7    B  TRR335 1050
8    A  TSJ570 1300
 
●B シート
 
   A    B     C
1 金額 ABC区分 部品番号
2  100    A  TSJ326
3  300    A  TSJ568
4  800    A  TSJ569
5 1300    A  TSJ570
 
A2
=IFERROR(INDEX(A!C:C,AGGREGATE(15,6,ROW($2:$500)/(A!$A$2:$A$500=$B$2),ROW(A1))),"")
下方向・↓
B3
=IF(C3="","",$B$2)
下方向・↓
C2
=IFERROR(INDEX(A!B:B,AGGREGATE(15,6,ROW($2:$500)/(A!$A$2:$A$500=$B$2),ROW(C1))),"")
下方向・↓

 
 
んなっと様
 ありがとうございました。
 綺麗にデータがまとまってすごく助かりました。
 初めてみる関数もありとても勉強になりました。
 
ガイドマップ

投稿日時: 19/04/05 00:48:08
投稿者: さーびす

Mike さんの引用:
ヤッパリ違ってましたかぁ。読解力が貧弱なワ・タ・シ(*^.^*)
  
「*区分A抽出したい」SheetB のレイアウトを私なら此処の添付図のようにします。
区分の分かり切っている「A」を必要以上に登場させることもアンメーに、と思うから。
 
 ̄ ̄1.SheetA において、範囲 A1:C100 を選択 ⇒ Alt+MC ⇒“上端行”だけ
 ̄ ̄ ̄ ̄にチェック入れ ⇒ [OK]
 ̄ ̄2.SheetB において、
 ̄ ̄ ̄ ̄次式を入力したセル A4 を右隣および下方にズズーッとオートフィル
 ̄ ̄ ̄ ̄=IFERROR(INDEX(INDIRECT(A$3),SMALL(IF(ABC区分=$B$1,ROW(ABC区分)),ROW(A1))-1),"")
 ̄ ̄ ̄ ̄【お断り】上式は必ず配列数式として入力のこと(配列数式を嫌うなら、
 ̄ ̄ ̄ ̄此処の返信全部無視されたし)
SheetB
    A     B
1 ABC区分 A
2
3 金額  部品番号
4 100   TSJ326
5 300   TSJ568

 
Mike様
 
 回答ありがとうございました。
 お返事遅くなってしまい申し訳ありません。
 私の説明不足で大変申し訳ありませんが、最初にご教授いただきました。
 やり方で、下記のような表示ができたら教えていただけませんでしょうか
 金額、区分、部品番号も表示したいです。
 行ごとに該当するものがあれば表示できますでしょうか?
 
 
SheetA             SheetB
  A    B   C        A   B    C
1 ABC区分 部品番号 金額     1 金額 ABC区分 部品番号
2 A    TSJ326  100  →  2 100 A    TSJ326
3 B    TRR333   50  →  3 区分が違うので該当なしので、こちらは空欄表示 
4 A    TSJ568  300  →  4 30 A    TSJ568  
 
 
☆言葉足らずで申し訳ありません。
ガイドブック
 

回答
投稿日時: 19/04/05 09:17:20
投稿者: Mike

ガイドマップ さんの引用:
最初にご教授いただきましたやり方で、下記のような表示ができたら教えていただけませんでしょうか
 金額、区分、部品番号も表示したいです。
 行ごとに該当するものがあれば表示できますでしょうか?
「最初にご教授いただきましたやり方」ってのは「投稿日時: 19/04/02 17:34:27」のことですか?あんときゃ私「ホントかなぁ〜?」と嘆息したように、実は貴方の質問がナンセンスと思っていたのです。結果的に的外しになったわけで、ソレを繰り返す勇気はもうありません。

回答
投稿日時: 19/04/05 14:51:17
投稿者: Suzu

結局質問者さんがどうやりたいのか、質問者さんは厳密には理解できていません。
あくまでも、ある前提を予測して回答しています。
 
 

引用:
SheetA             SheetB
  A    B   C        A   B    C
1 ABC区分 部品番号 金額     1 金額 ABC区分 部品番号
2 A    TSJ326  100  →  2 100 A    TSJ326
3 B    TRR333   50  →  3 区分が違うので該当なしので、こちらは空欄表示
4 A    TSJ568  300  →  4 30 A    TSJ568

 
「A」だけのレコードを拾えば良いの?
「B」を拾う事もあるの? その時、Bのレコードを抽出するってどうやって指定するの?
 
式の中で、A または B を盛り込んでしって良いのであれば
A2 =IF(SheetA!A2="A",SheetA!C2,"")
B2 =IF(SheetA!A2="A","A","")
C2 =IF(SheetA!A2="A",SheetA!B2,"")
あとはオートフィルで良いのでは。

投稿日時: 19/04/11 23:15:26
投稿者: さーびす

Suzu さんの引用:
結局質問者さんがどうやりたいのか、質問者さんは厳密には理解できていません。
あくまでも、ある前提を予測して回答しています。
 
 
引用:
SheetA             SheetB
  A    B   C        A   B    C
1 ABC区分 部品番号 金額     1 金額 ABC区分 部品番号
2 A    TSJ326  100  →  2 100 A    TSJ326
3 B    TRR333   50  →  3 区分が違うので該当なしので、こちらは空欄表示
4 A    TSJ568  300  →  4 30 A    TSJ568

 
「A」だけのレコードを拾えば良いの?
「B」を拾う事もあるの? その時、Bのレコードを抽出するってどうやって指定するの?
 
式の中で、A または B を盛り込んでしって良いのであれば
A2 =IF(SheetA!A2="A",SheetA!C2,"")
B2 =IF(SheetA!A2="A","A","")
C2 =IF(SheetA!A2="A",SheetA!B2,"")
あとはオートフィルで良いのでは。

 
 
お返事遅くなり申し訳ございません。
ありがとうございました。
色々と勉強し直します。