[[20190418150543]]　『列で特定の連続セルを抽出』（あ）　ページの最後に飛ぶ

『列で特定の連続セルを抽出』（あ）

下記のような一列に連続した○◎◎◎○を見つけて最初の○に該当する数字を表示したいです。関数でどのうな式で表現できますでしょうか。下記の図です答えはと3と13になります。この羅列は2700程度連続しています。
1×
2×
3○
4◎
5◎
6◎
7○
8×
9×
10○
11×
12×
13○
14◎
15◎
16◎
17○
18×
19×
20×

＜使用 Excel：Excel2013、使用 OS：Windows8 ＞

 セルには数字も一緒に入力されているのか、数字は行番号でセルには入力されていないのかどちらだろうか？
（ねむねむ） 2019/04/18(木) 15:39

セルに入力しています、数字と記号は別の列に入力しています。
（あ） 2019/04/18(木) 15:41

 例えば数字はA列、記号がB列だとする。
 C列を作業列に使い、D列に数字を抜き出す。
 まず、C1セルに
 =IF(AND(INDEX(B1:B5={"○";"◎";"◎";"◎";"○"},0)),A1,"")
 と入力して下へフィルコピー。

（ねむねむ） 2019/04/18(木) 16:01

 次にD1セルに
 =IFERROR(SMALL(C:C,ROW(A1)),"")
 と入力してこれも下へフィルコピーしてみてくれ。
（ねむねむ） 2019/04/18(木) 16:01

すごすぎます！全然できなかったのにうまくいきました！ありがとうございます。
さらになのですが…

○◎◎◎○、○◎◎◎◎○、○◎◎◎◎◎○、○◎•••◎○というふうに○と○の間に◎が何個か入っても先ほどと同様に抽出する方法はございますでしょうか？例えば下記ですと3と13と21が答えになります。間の◎は最大6個までとなります。◎以外が入った場合は認識したくありません。
1×
2×
3○
4◎
5◎
6◎
7◎
8◎
9◎
10○
11×
12
13○
14◎
15◎
16◎
17○
18×
19◎
20×
21○
22◎
23◎
24◎
25◎
26◎
27◎
28○
29×
30○
（あ） 2019/04/18(木) 17:07

 C1セルに入れる式を
 =IF(AND(B1="○",B2<>"○",INDEX(B2:B$3000,MATCH(1,INDEX((B2:B$3000<>"◎")*1,0),0))="○"),A1,"")
 としてみてくれ。
 D1セルの式は前と同じで。
 なお、B$3000は行が2700くらいあるということでそれより大きい値にしている。
（ねむねむ） 2019/04/18(木) 17:29

できました！感動です！やはりプロにお伺いするのが一番ですね！
ねむねむさんご親切にありがとうございました。
（あ） 2019/04/18(木) 17:35

上記にさらに以下のような判定を加えたいです。何か方法はございますでしょうか。
まず、B列を追加します。
そして、C列で連続した○◎・・◎○を見つけて最初の○に該当する数字をD列に表示します。
次にE列では○◎・・◎○のうちそれぞれが対応するB列の数字のうち最大値が最小値の2倍以上であればD列の数字をそのまま表示、2倍未満なら非表示にします。←このE列を関数であわわせますでしょうか？

例えば、C4～C8では○◎◎◎○が続いているので対応する最大値は140、最小値は90となり、140/90<2で非表示とします。

A B C D E
1 40 ×
2 41 ×
3 40 ×
4 100 ○ 4 非表示
5 110 ◎
6 140 ◎
7 120 ◎
8 90 ○
9 40 ×
10 42 ×
11 100 ○ 11 11
12 200 ◎
13 400 ◎
14 180 ◎
15 95 ○
16 43 ×
17 41 ×
18 40 ×
19 40 ×
20 41 ×
21 42 ×
22 40 ×
23 100 ○ 23 23
24 280 ◎
25 500 ◎
26 700 ◎
27 480 ◎
28 300 ◎
29 95 ○
30 40 ×
31 95 ○ 31 非表示
32 125 ◎
33 130 ◎
34 100 ○
35 41 ×
36 41 ×
37 40 ×
38 42 ×
39 43 ×
40 40 ×

わかりにくくて申し訳ありません。
（k-mu ） 2019/04/22(月) 13:34

 D1セルに
 =IF(AND(C1="○",C2<>"○",INDEX(C2:C$3000,MATCH(1,INDEX((C2:C$3000<>"◎")*1,0),0))="○"),A1,"")
 E1セルに
 =IF(D1="","",IF(MAX(OFFSET(B1,,,MATCH(1,INDEX((C2:C$3000="○")*1,0),0)+1))>=MIN(OFFSET(B1,,,MATCH(1,INDEX((C2:C$3000="○")*1,0),0)+1))*2,D1,"非表示"))
 でどうだろうか？
（ねむねむ） 2019/04/22(月) 16:28

うまくいきました。
ねむねむさんありがとうございます。

（k-mu ） 2019/04/22(月) 17:28

40行目以降 8行おきにこの○◎×の並びが60回セット以上繰り返されるのですが、その際D列E列をコピーして、一定ごとに上記と同じ計算をさせるにはどうしたらよいでしょうか。あまりにも連続回数が多いので数式のコピーでなんとかしたいです。

（あ） 2019/04/23(火) 08:17

また、教えていただいたD1:=IF(AND(C1="○",C2<>"○",INDEX(C2:C$3000,MATCH(1,INDEX((C2:C$3000<>"◎")*1,0),0))="○"),A1,"")

 E1セル:=IF(D1="","",IF(MAX(OFFSET(B1,,,MATCH(1,INDEX((C2:C$3000="○")*1,0),0)+1))>=MIN(OFFSET(B1,,,MATCH(1,INDEX((C2:C$3000="○")*1,0),0)+1))*2,D1,"非表示"))
を大量にオートフィルすると容量オーバーでエクセルが落ちてしまい困っています。どうしたらよいでしょうか。

（k-mu ） 2019/04/23(火) 15:09

 >容量オーバーでエクセルが落ちてしまい困っています。
 こちら。
 うまくいくかはわからないが。
 >間の◎は最大6個までとなります。
 ということで式中の
 C2:C$3000
 を
 C2:C10
 としてみてはどうだろうか？

 あと、ハンドル名は統一してくれ。
（ねむねむ） 2019/04/23(火) 15:19

ねむねむさん
ご回答ありがとうございます。
随分ましになりましたが、それでもかなり重いようです。

ハンドル名不慣れなもので間違えてしまいすいません。
（k-mu ） 2019/04/23(火) 18:47

◎は最大6個といいましたが、実際には6個以上続く場合があります。判定では7個以上のものは何も表示したくないのですが、C2:C$3000をC2:C10とすると以下のようにエラー判定がでてしまいました。
D1:=IF(AND(C1="○",C2<>"○",INDEX(C2:C10,MATCH(1,INDEX((C2:C10<>"◎")*1,0),0))="○"),A1,"")の式で6個以上続くときエラーださずに非表示にするにはどうしたらよいでしょうか。
情報が後出しになって申し訳ありません。

A B C D
1 ×
2 ○ #N/A #N/A
3 ◎ #N/A #N/A
4 ◎ #N/A #N/A
5 ◎ #N/A #N/A
6 ◎ #N/A #N/A
7 ◎ #N/A #N/A
8 ◎ #N/A #N/A
9 ◎ #N/A #N/A
10 ◎ #N/A #N/A
11 ◎ #N/A #N/A
12 ◎ #N/A #N/A
13 ◎ #N/A #N/A
14 ◎
15 ◎
16 ◎
17 ◎
18 ◎
19 ◎
20 ◎
21 ◎
22 ◎
23 ○
24 ○ #N/A #N/A
25 ◎ #N/A #N/A
26 ◎ #N/A #N/A
27 ◎ #N/A #N/A
28 ◎ #N/A #N/A
29 ◎ #N/A #N/A
30 ◎ #N/A #N/A
31 ◎ #N/A #N/A
32 ◎ #N/A #N/A
33 ◎ #N/A #N/A
34 ◎ #N/A #N/A
35 ◎
36 ×

（k-mu ） 2019/04/24(水) 08:18

 最大で何個続くのだろうか？
（ねむねむ） 2019/04/24(水) 08:39

 おっと、7個以上は無視するのであれば上の質問は必要なかった。
 C列の式を
 =IFERROR(IF(AND(B2="○",B3<>"○",INDEX(B3:B11,MATCH(1,INDEX((B3:B11<>"◎")*1,0),0))="○"),A2,""),"")
 としてみてくれ。

（ねむねむ） 2019/04/24(水) 08:46

 あと、
 >C2:C$3000
 >を
 >C2:C10
 と書いたが、全式中の$3000を10とするようにしてくれ。

（ねむねむ） 2019/04/24(水) 08:48

誤判定がたくさん生じてしまいました。
◎を間にはさんでいない○○○○といったパターンの一番後ろの○もなぜか検出してしまうようになりました。○◎◎◎○といった間が◎のパターンの一番最初の○だけを検出したいのですがどうすればよいでしょうか。また、D列もエラーがでないように変更したいです。
（k-mu ） 2019/04/24(水) 09:23

 すまない。
 C2セルの式を挙げてしまっていた。
 C1セルだと
 =IFERROR(IF(AND(B1="○",B2<>"○",INDEX(B2:B10,MATCH(1,INDEX((B2:B10<>"◎")*1,0),0))="○"),A1,""),"")
 だった。
（ねむねむ） 2019/04/24(水) 09:53

 何度もすまない。
 7個以上は無視するには
 =IFERROR(IF(AND(B1="○",B2<>"○",INDEX(B2:B8,MATCH(1,INDEX((B2:B8<>"◎")*1,0),0))
="○"),A1,""),"") 
 にしてくれ。

（ねむねむ） 2019/04/24(水) 10:04

以下のように検知してほしいのは５だけなのですが、なぜか１７や１９までひろってしまいます。
どうすればよいでしょうか。
A B C
1 ×
2 ×
3 ×
4 ×
5 ○ 5
6 ◎
7 ◎
8 ◎
9 ○
10 ○
11 ○
12 ○
13 ○
14 ○
15 ○
16 ○
17 ○ 17
18 ×
19 ○ 19
20 ○
21 ○
22 ×
23 ×
24 ×

（k-mu ） 2019/04/24(水) 10:14

 実際のセル番地も上記通りなのだろうか？
 こちらで上記データをA1セルからに張り付けて
 =IFERROR(IF(AND(B1="○",B2<>"○",INDEX(B2:B8,MATCH(1,INDEX((B2:B8<>"◎")*1,0),0))"○"),A1,""),"") 
 の式を入れて試しても17,19は拾わないのだが。
（ねむねむ） 2019/04/24(水) 10:48

簡略化しておりました。
実際の一部のデータを抜粋しますと以下のようになります。
A、D、E列が上で示したA、B、C列に相当し、BとC列は記載がありますが数値は重要ではないので無視して頂いて大丈夫です。そして、62584行目のE列の数式は
=IFERROR(IF(AND(D62584="○",D62585<>"○",INDEX(D62584:D62590,MATCH(1,INDEX((D62584:D62590<>"◎")*1,0),0))="○"),A62584,""),"")と表記されています。

行番号 A B C D E
62581 2.942 2052 1342 ×
62582 2.95 4734 1303 ○
62583 2.958 6683 1416 ○
62584 2.967 5627 1835 ○ 2.967
62585 2.975 3365 2385 ×
62586 2.983 2194 2531 ×
62587 2.992 1735 2227 ×

（k-mu ） 2019/04/24(水) 11:04

 >=IFERROR(IF(AND(B1="○",B2<>"○",INDEX(B2:B8,MATCH(1,INDEX((B2:B8<>"◎")*1,0),0))"○"),A1,""),"") 
 この式をD列参照に変更して１行目に入力、そして下へフィルコピーした場合、62584行では
 =IFERROR(IF(AND(D62584="○",D62585<>"○",INDEX(D62585:D62591,MATCH(1,INDEX((D62585:D62591<>"◎")*1,0),0))="○"),A62584,""),"")
 となるはずだが？
（ねむねむ） 2019/04/24(水) 11:19

 1行目の式を
 =IFERROR(IF(AND(D1="○",D2<>"○",INDEX(D1:D7,MATCH(1,INDEX((D1:D7<>"◎")*1,0),0))="○"),A1,""),"")
 としてしまっていないか？
（ねむねむ） 2019/04/24(水) 11:20

うまくいきました。
セル範囲が違いました。
私の間違いです。すいません。
（k-mu） 2019/04/24(水) 12:45

コメント返信：

[ 一覧(最新更新順) ]

[[20190418150543]] 『列で特定の連続セルを抽出』（あ） ページの最後に飛ぶ

[[20190418150543]]　『列で特定の連続セルを抽出』（あ）　ページの最後に飛ぶ