Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(指定なし : Excel 2013)
複数条件に合致する行番号を取得する
投稿日時: 20/07/31 18:37:56
投稿者: ころぽち

データシートのA列:組織CD,B列:種別CDが入力された表があり、
抽出シートのA1:組織CD、B1:種別CDが入力される。
 

 
 1  A:所属     B:種別     抽出シート A1:100110111000   B1:70
 2 100110111000     51
 3 100110111000     71
 4 100110111000     75        この表から所属CDがA1「100110111000」で
 5 100110111000     76        種別CDがB1「70」以上のデータの初めて登場する
 6 100120121000     51        行番号を取得したい。
 7 100120121000     71
 8 100120121000     75
 9 100120121000     75
10 100120121000     76
11 100130131000     52
12 100130131000     71
13 100130131000     71
14 100140141000     54
15 100140141000     71
16 100140141000     71
17 100140141000     71
18 100150152000     71
19 100150152000     71
20 100150152000     71
21 100150152000     71
22 100150152000     71
23 100150153000     71
24 100150153000     75
 
組織コードを限定して、種別が値以上であるデータの初めて登場する
行番号を取得したいと思っています。
条件が一つであれば =MATCH(A$1,DATA!A:A,0)
で取得できるのですが、複数条件の場合、どうすればいいのか
わかりません。
よろしくお願いいたします。

回答
投稿日時: 20/07/31 19:24:38
投稿者: んなっと

=IFERROR(MATCH(1,INDEX((データ!A$1:A$500=A1)*(データ!B$1:B$500>=B1),),0),"")
 
 
最新のExcelなら
=IFERROR(XMATCH(1,(データ!A$1:A$500=A1)*(データ!B$1:B$500>=B1)),"")
とか
=IFERROR(MATCH(1,(データ!A$1:A$500=A1)*(データ!B$1:B$500>=B1),0),"")
かも。

回答
投稿日時: 20/08/01 20:46:40
投稿者: Mike

              Sheet抽出
    A    B  C   D        A    B  C   D
1 100110111000 70 行番号 種別   1 100140141000 70 行番号 種別
2           3  71   2           15  71
3           4  75   3           16  71
4           5  76   4           17  71
5                 
    A    B  C   D        A    B  C   D
1 100120121000 70 行番号 種別   1 100150152000 70 行番号 種別
2           7  71   2           18  71
3           8  75   3           19  71
4           9  75   4           20  71
5           10  76   5           21  71
6                 6           22  71
    A    B  C   D        A    B  C   D
1 100130131000 70 行番号 種別   1 100150153000 70 行番号 種別
2           12  71   2           23  71
3           13  71   3           25  75
4                 4
 
下記の手順は Excel 2019 の場合です。
Sheetデータに於いて、
1.セル A1:B24 選擇⇒Alt+MC⇒“上端行”のみにチェック入れ⇒[OK]
 
Sheet抽出に於いて、
2.セル C2 に式
 ̄ ̄=IFERROR(SMALL(IF((所属=A$1)*(種別>=B$1),ROW(種別)),ROW(A1)),"")
 ̄ ̄を入力
 ̄ ̄【御斷はり】上式は必ず配列數式として入力の事
3.セル D2 に式 =IF(C2="","",INDEX(種別,C2-1)) を入力
4.範圍 C2:D2 を下方にズズーッとオートフイル

回答
投稿日時: 20/08/02 20:57:40
投稿者: WinArrow
投稿者のウェブサイトに移動

条件に合致する行番号を求めて
その行番号をどのように使うのでしょうか?

投稿日時: 20/08/05 13:52:37
投稿者: ころぽち

WinArrow様
 
 ご回答ありがとうございます。
 
 行番号を求めて、他のシートから、その行番号のデータ(複数列)を引っ張ってきたいと
 思っています。
 種別70以上の表だけが作成できると思っています。

回答
投稿日時: 20/08/05 15:10:54
投稿者: WinArrow
投稿者のウェブサイトに移動

ころぽち さんの引用:
WinArrow様
 
 ご回答ありがとうございます。
 
 行番号を求めて、他のシートから、その行番号のデータ(複数列)を引っ張ってきたいと
 思っています。
 種別70以上の表だけが作成できると思っています。

 
それだったら、
『指定の「所属」の指定以上の「種別」の表を作成したい』
が、本当の質問ではないでしょうか?
 
「データ」タブの「詳細設定」(フィルタオプション)を使えばよいです。
 
手順
抽出シート
A1:所属
B1:種別
A2:100110111000・・・・抽出したい所属
B2:>=70 ・・・・70以上
 
この状態で
「詳細設定」をクリックして(フィルタオプション)ダイアログを表示します。
「指定した範囲」を選択
ボックスの上から
データシートのセル範囲
抽出シートのA1:B2
抽出シートの任意のセル(ex.H1)
「OK」
 
 
 
 
 

投稿日時: 20/08/06 18:34:25
投稿者: ころぽち

WinArrow様
 
ありがとうございます。
 
すみません、説明が足りなさ過ぎましたね。
 
実は、データシートでは、データがどんどん増えるのです。
半年にわたって、数十件から数百件ずつ増えていきます。
ですので、70以上のデータの最初の行番号を求めて、次の行番号を求めて・・・
と、データを引っ張って抽出シートに台帳を作ろうと思っています。
業務の性格上、並び替えはせず、下にデータを足していく形になります。
 
 
もしかして、もっとよい実現方法がありますでしょうか。
 
 
よろしくお願いいたします。

回答
投稿日時: 20/08/06 18:58:57
投稿者: んなっと

わざと回答を無視しているのですか?
 
       A       B  C
1 100110111000      70  
2       3 100110111000 71
3       4 100110111000 75
4       5 100110111000 76
 
A2
=IFERROR(AGGREGATE(15,6,ROW($2:$500)/(Data!A$2:A$500=A$1)/(Data!B$2:B$500>=B$1),ROW(A1)),"")
下方向・↓
B2
=IF($A2="","",INDEX(Data!A:A,$A2))
右方向・→下方向・↓

回答
投稿日時: 20/08/06 22:03:26
投稿者: WinArrow
投稿者のウェブサイトに移動

>もしかして、もっとよい実現方法がありますでしょうか
 
回答者からの回答は無視されているように思えます。
 
 
解決策が提案されているので、回答者からの回答をよく読んでください。
よく読んで、試してみる・・・・そして、疑問があったら、再度質問してください。
 
 
 

回答
投稿日時: 20/08/11 11:26:46
投稿者: xls-hashimoto

「んなっと」さんの解答が無視されている理由は分かりませんが、
似たような方法でC列に行番号を計算していく方法です。
A・B列はC列の行番号の値を表示させます。
 

100110111000	70	行番号
100110111000	71	3
100110111000	75	4
100110111000	76	5

 
C2
=IF($C1="","",IFERROR(MATCH(1,INDEX((OFFSET(Data!$A$1,SUM($C1),,500,1)=$A$1)*(OFFSET(Data!$B$1,SUM($C1),,500,1)>=$B$1),),0)+SUM($C1),""))
下方向・↓
 
C1に「行番号」と入力したために、その対応で式が長くなっています。
横長のこの表記は年のせいで読み解くのに苦労します。
そこで次の様に改行とインデントを加えます。
 
C2
=IF(
    $C1="",
    "",
 
    IFERROR(
            MATCH(
                  1,
                  INDEX(
                        (OFFSET(Data!$A$1,SUM($C1),,500,1)=$A$1)
                        *
                        (OFFSET(Data!$B$1,SUM($C1),,500,1)>=$B$1),
                       ),
                  0
                 )
            +
            SUM($C1),
            ""
           )
   )
下方向・↓
 
A2
=IF($C2="","",INDEX(Data!A:A,$C2))
右方向・→下方向・↓
 
これも改行とインデントを加えると次の様になります。
A2
=IF(
    $C2="",
    "",
 
    INDEX(Data!A:A,$C2)
   )
右方向・→下方向・↓
短い式は、読み解けるので改行とインデントは好みに応じて加えます。

投稿日時: 20/08/13 11:08:52
投稿者: ころぽち

んなっと様
Mike様
xls-hashimoto様
 
ご回答ありがとうございます。
わざと回答を無視しているわけではありません。
一度、試してから回答させていただこうとう思っているうちに
おやすみに入ってしまい、データとなるexcelファイルにアクセスできなく
なってしまいました。
 
ちゃんと回答のお礼はお伝えすべきでした。
貴重なお時間を私の質問に充ててくださってありがとうございます。
 
ファイルにアクセスできるようになって検証してからまた、ご回答させていただきます。
 
 
この度は大変失礼いたしました。
 
今後とも、よろしくお願いいたします。
 
 

回答
投稿日時: 20/08/15 09:01:58
投稿者: xls-hashimoto

配列数式を使わず条件の数だけ作業列を使う方法です。
 

100110111000	70	抽出行番号	所属同一行	70以上行
100110111000	71	3	2	
100110111000	75	4	3	3
100110111000	76	5	4	4
			5	5
				

1.D列で検索所属CDと同一の所属CDの存在行番号を計算します。
D2
=IF($D1="","",IFERROR(MATCH($A$1,OFFSET(Data!$A$1,SUM($D1),,ROWS($D:$D)-SUM($D1),1),0)+SUM($D1),""))
下方向・↓
 
この式に改行とインデントを加えると次の様になります。
D2
=IF(
    $D1="",
    "",
 
    IFERROR(
            MATCH(
                  $A$1,
                  OFFSET(Data!$A$1,SUM($D1),,ROWS($D:$D)-SUM($D1),1),
                  0
                 )
            +
            SUM($D1),
            ""
           )
   )
 
2.E列で検索種別CD以上の種別CDの存在行番号を計算します。
E2
=IF($D2="","",IF($B$1<=INDEX(Data!$B:$B,$D2),$D2,""))
下方向・↓
 
この式に改行とインデントを加えると次の様になります。
E2
=IF(
    $D2="",
    "",
 
    IF(
       $B$1<=INDEX(Data!$B:$B,$D2),
       $D2,
 
       ""
      )
   )
 
3.C列でE列で計算した行番号を順番に計算します。
C2
=IF($C1="","",IFERROR(SMALL($E:E,ROW()-1),""))
下方向・↓
 
この式に改行とインデントを加えると次の様になります。
C2
=IF(
    $C1="",
    "",
 
    IFERROR(
            SMALL($E:E,ROW()-1),
            ""
           )
   )
 
4.A列・B列はC列の行番号を使い計算します。
A2
 =IF($C2="","",INDEX(Data!A:A,$C2))
右方向・→下方向・↓
   
これも改行とインデントを加えると次の様になります。
A2
 =IF(
     $C2="",
     "",
   
     INDEX(Data!A:A,$C2)
    )
右方向・→下方向・↓

投稿日時: 20/08/20 22:20:03
投稿者: ころぽち

んなっと様
 
ご回答ありがとうございました。
遅くなりましたが、本日、検証いたしまして、こちらの希望通りの結果が得られることを確認しました。
 
今後は、他の方法も検証し、実際にこのExcel表を運用する担当者と相談して方向性を決めたいと
思います。
 
 
この度は、大変失礼をいたしまして申し訳ありませんでした。
 
今後とも、よろしくお願いいたします。
 
 

トピックに返信