[[20190119152935]] 『表の1番上と下の値抽出』(くろごま) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『表の1番上と下の値抽出』(くろごま)

B列に並んでる数値の1番上と下の値を抽出する関数が知りたいです。

      A        B
1  くま   りんご
2  くま   みかん
3  くま   いちご

上記場合は、りんごといちごを抽出するような関数です。
因みに、3000行ほどあり、A列は300種類ほどあり種類によっては行数が変動します。

< 使用 Excel:Excel2013、使用 OS:Windows7 >


 B列が数値に見えないのですが・・・

 あとB列の一番最初と最後を出すのに、A列は必要なのですか?

 なんとなくだけど、
 A列の各種類の開始と終わりにある、B列の文字を出す

 ってことなのかと思います多が、どうですか?

 ちなみにB列だけの最後だとこんなんでできると思います
 =INDEX(B1:B5000,LARGE(INDEX((B1:B5000<>"")*ROW(B1:B5000),),1))

 最初はスタートが変わらなければ常に同じだと思うので、
 =B1
 でよいかと

(稲葉) 2019/01/19(土) 16:10


 ちょっと時間なくなったので、私の推測で正しければ以下の式でいかがですか?

 しばらく席外します。
 D1、E1は決め打ち
 C2=IFERROR(INDEX(A:A,AGGREGATE(15,6,INDEX((1/NOT($A$1:$A$100=$A$2:$A$101))*ROW($A$1:$A$100),),ROW(A1))),"")
 D2=VLOOKUP(C2,A:B,2,0)
 E2=INDEX(A:B,COUNTIF(A:A,C2)+MATCH(C2,A:A,0)-1,2)
 下方向にコピー

    |[A] |[B]     |[C] |[D]   |[E]   |[F]                                                                                                        
 [1]|くま|りんご  |    |最初  |最後  |                                                                                                           
 [2]|くま|みかん  |くま|りんご|いちご|C2=IFERROR(INDEX(A:A,AGGREGATE(15,6,INDEX((1/NOT($A$1:$A$100=$A$2:$A$101))*ROW($A$1:$A$100),),ROW(A1))),"")
 [3]|くま|いちご  |とり|麦    |豆    |D2=VLOOKUP(C2,A:B,2,0)                                                                                     
 [4]|とり|麦      |さる|バナナ|カキ  |E2=INDEX(A:B,COUNTIF(A:A,C2)+MATCH(C2,A:A,0)-1,2)                                                          
 [5]|とり|米      |    |      |      |                                                                                                           
 [6]|とり|豆      |    |      |      |                                                                                                           
 [7]|さる|バナナ  |    |      |      |                                                                                                           
 [8]|さる|アボカド|    |      |      |                                                                                                           
 [9]|さる|カキ    |    |      |      |                                                                                                           
(稲葉) 2019/01/19(土) 16:27

 きっともっとスマートな式があります。(凄い自信)(^^; せっかくなので、、、
                =IF(COUNTIF($A$1:A1,A1)=1,1,"")
                        =IF(A1<>A2,1,"")
                                 =IF(SUM(($C$1:$C$9=1)*1)<ROW(A1),"",INDEX($B$1:$B$9,SMALL(IF(($C$1:$C$9=1),ROW($A$1:$A$9)),ROW(A1))))
                                         =IF(SUM(($D$1:$D$9=1)*1)<ROW(A1),"",INDEX($B$1:$B$9,SMALL(IF(($D$1:$D$9=1),ROW($A$1:$A$9)),ROW(A1))))

 くま	りんご	1		りんご	いちご
 くま	みかん			もも	もも
 くま	いちご		1		
 りす	もも	1			
 りす	なし				
 りす	すいか				
 りす	シイタケ				
 りす	すいか				
 りす	もも		1		
(SoulMan) 2019/01/19(土) 16:32

 やりたいことが、A列の種類ごとに、B列の最初と最後の値を表示すること、だとして。

 稲葉さんのレイアウトで
 C列はそちらで入力する(種類は事前に分かってるんですよね?)

 D2は同じく VLOOKUPで =VLOOKUP(C2,$A$1:$B$3000,2,FALSE)

 E2 =LOOKUP(1,0/($A$1:$A$3000=C2),$B$1:$B$3000)

 A列の並びはランダムでも(同じ種類がかたまってなくても)オッケー

 参考まで
(笑) 2019/01/19(土) 17:04

皆様ありがとうございます。

稲葉様、ご指摘ありがとうございます。
確かに数値ではありませんね...大変失礼致しました。
また、説明不足で申し訳ありませんでした。
稲葉様のご見解通りの内容です(*^^*)
皆様にご教示頂きました内容で試させていただきます!!

因みにですが、今回質問させて頂きました関数で
C列のみに関数入力して最終行まで引っ張る事は可能でしょうか?

(くろごま) 2019/01/19(土) 18:51


 どう出力されればいいかわからないと、こたえようがないですよ
(稲葉) 2019/01/19(土) 19:03

稲葉様
C列に1番上と下の値を&で表示という意味でした。
    A    B        C
1 くま りんご  りんご&いちご
2 くま みかん
3 くま いちご
4 とり  麦        麦&豆
5 とり  米
6 とり  豆
本当に言葉足らずで申し訳ありません。
(くろごま) 2019/01/21(月) 11:56

 C1セルに
 =IF(COUNTIF(A$1:A1,A1)=1,B1&"&"&INDEX(B:B,MATCH(1,INDEX(0/(A:A=A1),0),1)),"")
 と入力して下へフィルコピーではどうか?
(ねむねむ) 2019/01/21(月) 12:01

 =IF(COUNTIF(A$1:A1,A1)=1,B1&"&"&LOOKUP(1,0/(A:A=A1),A:A),"")
 でもいいか。
(ねむねむ) 2019/01/21(月) 15:09

 すまない。
 =IF(COUNTIF(A$1:A1,A1)=1,B1&"&"&LOOKUP(1,0/(A:A=A1),A:A),"")
      ↓
 =IF(COUNTIF(A$1:A1,A1)=1,B1&"&"&LOOKUP(1,0/(A:A=A1),B:B),"")
 だった。
(ねむねむ) 2019/01/21(月) 15:20

 みなさんの式拝借して、条件分岐だけ変更

 =IF(MATCH(A1,A:A,0)=ROW(),B1&"&"&LOOKUP(1,0/(A:A=A1),B:B),"")
 COUNTIFとどっちのほうが負荷少ないんですかね?

(稲葉) 2019/01/21(月) 15:24


 ・A列種類塊が飛び飛びで発生しない。

 の、条件で

 C1 =IF(COUNTIF(A$1:A1,A1)=1,B1&"&"&INDEX(B1:B$6,COUNTIF(A1:A$6,A1)),"")
(GobGob) 2019/01/21(月) 15:33

おぉー!皆様ありがとうございます!
期待通りの結果が無事出来ました(*^^*)
自分の勉強にもなって助かりました
(くろごま) 2019/01/21(月) 18:47

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.