[[20191105000849]]　『データの抽出』（友紀）　ページの最後に飛ぶ

『データの抽出』（友紀）

Sheet1に下記のようなデータがあります。Sheet1のA1にB列の数字を
記入すると、Sheet2のB列からI列までの値を表示する方法は
ありますでしょうか。

例えばSheet1のA1の「1000」または「5000」と記入すると、Sheet2のは
下記のように表示したいのです。
（A1で記入した数字がB列で複数行のみ表示、同じ数字がとびとびには存在しません）

「3000」、「4000」のような1行だけは表示しないようにしたいのです。

実際にはB1～B8000行まで数字があります。

ご教授よろしくお願いいたします。

Sheet1

 A       B     C    D   E   F    G    H   I
1000   1000   A社   1   A  100  あ   ア   a
       1000	    2	B  200	い   イ   b
       1000	    3	C  300	う   ウ   c
       1000	    4	D  400	え   エ	  d
       2000   B社   5	E  500	お   オ	  e
       2000	    6	F  600	か   カ	  f
       3000   C社   7	G  700	き   キ	  g
       4000   D社   8	H  800	く   ク	  h
       5000   E社   9	I  900	け   ケ	  i
       5000	   10	J 1000	こ   コ   j
       5000	   11	K 1100	さ   サ	  k

「1000」の場合
Sheet2

	1000	A社	1	A	100	あ	ア	a
	1000		2	B	200	い	イ	b
	1000		3	C	300	う	ウ	c
	1000		4	D	400	え	エ	d

「5000」の場合
Sheet2

	5000	E社	9	I	900	け	ケ	i
	5000		10	J	1000	こ	コ	j
	5000		11	K	1100	さ	サ	k

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

Sheet1の一行目に見出しを追加したうえで、フィルタオプションを使って、
Sheet2に抽出したらどうでしょうか。
必要であればマクロにすることもできます。
数行のコードでできます。（マクロ記録がほぼそのまま使えます）

＞「3000」、「4000」のような1行だけは表示しないようにしたいのです。
特別扱いする理由が不明ですが、予め COUNTIFで判定すればよいと思われます。
（γ） 2019/11/05(火) 06:14

一案として関数案です。

Sheet2!A1: =IF(COUNTIF(Sheet1!$B$1:$B$11,Sheet1!$A$1)=1,"",IFERROR(INDEX(Sheet1!B$1:B$11,SMALL(IF(Sheet1!$B$1:$B$11=Sheet1!$A$1,ROW($A$1:$A$11),""),ROW(A1))),""))

「Ctrl + Shift + Enter」キーで式を入力します。
式の前後が「{}」で囲まれた配列数式になります。

I列までと下にコピーします。

Sheet2のB2以降に「0」と表示されますから、条件付き書式かゼロサプレス形式の
表示形式を設定してください。
式で対処すると式が長く（現状でも長いですが）なったり、抽出されたデータが
文字列になります。
抽出されたデータがすべて文字列でもいいということでしたら

Sheet2!A1: =IF(COUNTIF(Sheet1!$B$1:$B$11,Sheet1!$A$1)=1,"",IFERROR(INDEX(Sheet1!B$1:B$11,SMALL(IF(Sheet1!$B$1:$B$11=Sheet1!$A$1,ROW($A$1:$A$11),""),ROW(A1)))&"",""))

（メジロ） 2019/11/05(火) 10:01

 ＞同じ数字がとびとびには存在しません
 ということなら、配列数式なんか使わなくてもできますね。
 数式でいいのなら、ですけど。

 一応、確認
 1）最も多い数字で、Sheet1には何行ぐらいあるんですか？

 2）抽出するのは、実際もB列～I列の8列ですか？

 3）Sheet2はどのセルから抽出するんですか？　B1セル？

 とりあえず以上です
（笑） 2019/11/05(火) 11:40

γさま
メジロさま
笑さま

皆さまありがとうございます。
明日、頑張ってやります。

 1）最も多い数字で、Sheet1には何行ぐらいあるんですか？・・30行です
 2）抽出するのは、実際もB列～I列の8列ですか？・・・8列です
 3）Sheet2はどのセルから抽出するんですか？　B1セル？・・・B1セルです

（友紀） 2019/11/06(水) 00:29

 =IF(COUNTIF(Sheet1!$B:$B,Sheet1!$A$1)<ROW(A1),"",INDEX(Sheet1!B:B,MATCH(Sheet1!$A$1,Sheet1!$B:$B,0)+ROW(A1)-1))

 右、下へコピー。

 ※表示形式で0値表示なしに設定。
（GobGob） 2019/11/06(水) 07:47

 訂正

 =IF(OR(COUNTIF(Sheet1!$B:$B,Sheet1!$A$1)=1,COUNTIF(Sheet1!$B:$B,Sheet1!$A$1)<ROW(A1)),"",INDEX(Sheet1!B:B,MATCH(Sheet1!$A$1,Sheet1!$B:$B,0)+ROW(A1)-1))
（GobGob） 2019/11/06(水) 07:49

 最大30行×8列ぐらいなら数式でも。

   ↓ が前提条件です。
 ＞同じ数字がとびとびには存在しません

 作業用にセルを2つ使います。
 同じ検索を何度もすることを避けるため、です。

 どこでもいいですけど、Sheet2のA列が空いているとして、A1セルとA2セルで説明します。
 A列ではまずいのなら、J列以降のどこか空いた列で。

 ■作業セル（Sheet2）

 A1 =COUNTIF(Sheet1!B1:B8000,Sheet1!A1)
 A2 =IF(A1<2,"",MATCH(Sheet1!A1,Sheet1!B1:B8000,0))

 ■抽出（Sheet2）

 B1 =IF(OR($A$2="",$A$1<ROW(A1)),"",INDEX(Sheet1!B$1:B$8000,$A$2+ROW(A1)-1))
 I1セルまで右にコピー

 C1セルの表示形式～ユーザー定義　#
 C1以外の表示形式は「標準」、または数値を桁区切りにしたいのなら希望のものを設定

 B1:I1 を下にコピー（余裕を見て40行目ぐらいまで？）

 ※ROW(A1) の「A1」は、作業セルがA1セルだからではありません。
 　作業セルがどのセルであろうが、数式を入れるセルがどこであろうが「A1」を変更しないでください。

 以上です
（笑） 2019/11/06(水) 10:13

コメント返信：

[ 一覧(最新更新順) ]

[[20191105000849]] 『データの抽出』（友紀） ページの最後に飛ぶ

[[20191105000849]]　『データの抽出』（友紀）　ページの最後に飛ぶ