Excel (一般機能)

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

 
(Windows 10 Home : Excel 2016)
指定した日付を一覧表に抽出したい
投稿日時: 23/12/03 05:19:35
投稿者: 忠犬

シート1にデータ、シート2に20行のデータが表示できる一覧表があります。
シート2のA1セル任意の日付を入力し、シート2のC1セルに1と入力したら、任意の日付に合致する1行目〜20行目をシート2の一覧表に表示させ、C1セルに2と入力したら21行目〜40行目までを表示するようにしたいです。
 
【シート1】
 A      B       C
1 受付日  会社名     送付日
2 5/1  あいう株式会社 5/10
3 5/1  かきく有限会社 5/11
・・・(5万行ぐらい)
 
【シート2】
   A     B     C
1 5/10         1
2 受付日   会社名   送付日
3 5/1  あいう株式会社 5/10
 
 
シート2のA3セルに
=if(($C$1-1)*20+row(A1)<=count(シート1!$A$1:$A50000,A1),index(シート1!$A:$A,small(シート1$A$1:$A50000,($C$1-1)*20+row(A1)),1),"")
 
と入力しましたが、シート1のC列に入力した5/10以外の日も表示されてしまいます。
あちこち調べてみましたが、よくわかっていないこともあって質問させていただきました。
 
どうかご教示ください。
 

回答
投稿日時: 23/12/03 10:22:17
投稿者: 半平太

シート2のA1はシート1のC列(送付日)を探索するものですよね?
※ご提示の数式は、A列(受付日)をチェックに行っていますが、
  そもそも間違いと解釈します。(サンプルの5/10は送付日なので)
 
データが多いので、配列数式(CSE数式)でやります。
 
1.A3セルに
  =IFERROR(INDEX(シート1!A1:A50000,AGGREGATE(15,6,ROW(Z1:Z50000)/(シート1!$C1:$C50000=$A$1),($C$1-1)*20+ROW(A1:A20)),0),"")
  と入力する。
 
2.A3セルを先頭にA22セルまでドラッグ選択し、
    F2キーを押下して、編集状態にしてから、Ctrl+Shift+Enterキー押下
 
3.上記2の処理直後の状態において、A22のセルの右下にマウスを近づけるとフィルハンドル(十字のマーク)が出ますので、
  それを掴んでC列までフィルドラッグ

回答
投稿日時: 23/12/03 10:23:56
投稿者: WinArrow

説明不足があるのではないでしょうか?
数式を分解してみると、
おかしなところが見えてきます。
>=count(シート1!$A$1:$A50000,A1)
で、どのような値になります?
 
抽出仕様を説明しましょう。
シート2のC1は、1〜20.21〜40・・・というような範囲を判別するものですか?
その範囲の中から、シート2のA1に該当するデータを抽出するのでしょうか?
 

投稿日時: 23/12/03 15:20:58
投稿者: 忠犬

>シート2のA1はシート1のC列(送付日)を探索するものですよね?
>※ご提示の数式は、A列(受付日)をチェックに行っていますが、
>そもそも間違いと解釈します。(サンプルの5/10は送付日なので)
 
半平太さま
 
はい、おっしゃるとおりです。
 
 
1.A3セルに
  =IFERROR(INDEX(シート1!A1:A50000,AGGREGATE(15,6,ROW(Z1:Z50000)/(シート1!$C1:$C50000=$A$1),($C$1-1)*20+ROW(A1:A20)),0),"")
  と入力する。
 
2.A3セルを先頭にA22セルまでドラッグ選択し、
    F2キーを押下して、編集状態にしてから、Ctrl+Shift+Enterキー押下
 
3.上記2の処理直後の状態において、A22のセルの右下にマウスを近づけるとフィルハンドル(十字のマーク)が出ますので、
  それを掴んでC列までフィルドラッグ
 
上記手順でやってみましたが何も表示されませんでした。
せっかくお考えいただいたのにすみません。

投稿日時: 23/12/03 15:28:59
投稿者: 忠犬

WinArrow様
 
>説明不足があるのではないでしょうか?
>数式を分解してみると、
>おかしなところが見えてきます。
>>=count(シート1!$A$1:$A50000,A1)
>で、どのような値になります?
 
1になります。
 
>抽出仕様を説明しましょう。
>シート2のC1は、1〜20.21〜40・・・というような範囲を判別するものですか?
>その範囲の中から、シート2のA1に該当するデータを抽出するのでしょうか?
 
すみません。はい、そのとおりです。
 
シート2のA1セルに送付日を入力し、シート1の送付日が合致するデータをすべて抽出したいのですが、
シート2のひな型の行が20行のため、例えば合致するデータが30行あるとすると、C1セルに「1」と入力
した場合、シート2に表示されるのが合致するデータの1〜20行目まで、「2」と入力すると
21行目〜30行目までが表示されるようにしたいのです。
説明下手ですみません。

回答
投稿日時: 23/12/03 16:14:43
投稿者: WinArrow

引用:
1になります。

 
おかしいですね?
 
こちらでは、
3
になりますが・・・・
 
=COUNTIF(シート1!C1:C5000,A1)
 
ならば
1
になります。

回答
投稿日時: 23/12/03 18:17:17
投稿者: WinArrow

シート2(受取側)が20行なんですよね?
シート1(元データ側)を20行で分割して検索する必要はないですよね?
 
どうも抽出仕様がよくわかりませんね。
1つの数式で20件を抽出したいということなのかな?
 

回答
投稿日時: 23/12/03 18:50:25
投稿者: WinArrow

シート2の20件という条件を除いた数式の例
 
シート2のA3セルに
 
=IF(COUNTIF(Sheet1!$C$2:$C$100,$A$1)<ROW($A$1),"",INDEX(Sheet1!A$2:A$100,SMALL(IF(Sheet1!$C$2:$C$100=$A$1,ROW($A$1:$A$20)),ROW(A1))))
数式入力後
[Shift]+[Ctrl]+[Enter]で入力を確定します。(配列数式にします)
B3〜c3へ複写します。

回答
投稿日時: 23/12/03 19:44:00
投稿者: WinArrow

あと
シート2の20件という条件
なんだけど、20件はどのような制約ですか?
 
もし、印刷件数という条件ならば、
改ページを設定すれば、対応できますよね。
 

投稿日時: 23/12/03 21:59:47
投稿者: 忠犬

WinArrow様
 
>シート2(受取側)が20行なんですよね?
 
はい。
 
>シート1(元データ側)を20行で分割して検索する必要はないですよね?
 
はい、そうです。

投稿日時: 23/12/03 22:12:52
投稿者: 忠犬

WinArrow様
 
>おかしいですね?
 
>こちらでは、
>3
>になりますが・・・・
 
あ!すみません、3になりました。

投稿日時: 23/12/03 22:26:30
投稿者: 忠犬

WinArrow様
 
>シート2の20件という条件を除いた数式の例
 
>シート2のA3セルに
 
>=IF(COUNTIF(Sheet1!$C$2:$C$100,$A$1)<ROW($A$1),"",INDEX(Sheet1!A$2:A$100,SMALL(IF(Sheet1!$C$2:$C$10>0=$A$1,ROW($A$1:$A$20)),ROW(A1))))
>数式入力後
>[Shift]+[Ctrl]+[Enter]で入力を確定します。(配列数式にします)
>B3〜c3へ複写します。
 
 
ありがとうございます。
20件という制約を除いた場合だと、いろいろ調べてみると
 
=IF(COUNTIF(Sheet1!C1:C50000,$A$1)<ROW(A1),"",INDEX(Sheet1!A1:A50000,MATCH($A$1,Sheet1!C1:C50000,0)+ROW(A1)-1))
 
↑上記でも表示できることはわかりました。
 
 
どうしても20件という制約をいれたいのは、50ページ以上にもなるケースが想定されまして、
ただでさえ重いのでひな型は1ページにしたく質問をさせていただきました。
難しいでしょうか…。

投稿日時: 23/12/03 22:46:23
投稿者: 忠犬

半平太 さんの引用:
シート2のA1はシート1のC列(送付日)を探索するものですよね?
※ご提示の数式は、A列(受付日)をチェックに行っていますが、
  そもそも間違いと解釈します。(サンプルの5/10は送付日なので)
 
データが多いので、配列数式(CSE数式)でやります。
 
1.A3セルに
  =IFERROR(INDEX(シート1!A1:A50000,AGGREGATE(15,6,ROW(Z1:Z50000)/(シート1!$C1:$C50000=$A$1),($C$1-1)*20+ROW(A1:A20)),0),"")
  と入力する。
 
2.A3セルを先頭にA22セルまでドラッグ選択し、
    F2キーを押下して、編集状態にしてから、Ctrl+Shift+Enterキー押下
 
3.上記2の処理直後の状態において、A22のセルの右下にマウスを近づけるとフィルハンドル(十字のマーク)が出ますので、
  それを掴んでC列までフィルドラッグ

 
半平太様
 
もう1回やってみたところ、
すみません!できました!ありがとうございました!