[[20231024111517]]　『検索窓を設けてデータを引っ張る』（ヤッホー）　ページの最後に飛ぶ

『検索窓を設けてデータを引っ張る』（ヤッホー）

すみませんが、
以下の内容ではうまく機能せず
ご教授のほどを、どうぞよろしくお願いいたします

「sheet1」に「sheet2」のデータを検索できるように、
検索窓と検索結果を置いています

	　検索窓									
	　A　　　B									
11

	　検索結果									
	　A　　　B　　　C　　　D　　　E　　　F　　　G　　　H　　　I									
13

Ａ列13行には以下の関数を入れて、ドラッグでI列まで引っ張っています
IF(COUNT(sheet2!$J:$J<ROW(sheet2!A1),"",INDEX(sheet2!A:A,SMALL
(sheet2!$J:$J,ROW(sheet2!A1))))

「sheet2」にデータを置いています

　A　　B　　C　　　D　　　E　　　F　　G　　　H　　　I　　　J
10/1　梨　　20　　山梨　　△FA　×L　○氏　女性　　31歳
10/1　林檎　20　　青森　　○FA　○L　○氏　男性　　30歳

Jには以下の関数を入れています
IF(sheet1!A$11&sheet1!B$11="","",IF(IF(AND(sheet1!A$11<>"",
sheet1!B$11<>""),COUNTIF(A4,"*"&sheet1!A$11&"*")*COUNTIF
(B4,"*"&sheet1!B$11&"*"),IF(sheet1!A$11<>"",
COUNTIF(A4,"*"&sheet1!A$11&"*"),COUNTIF(B4,"*"&sheet1!B$11&"*")))
,ROW(),""))

ここまではうまくいくのですが、
検索窓を3個にしようとしたとき、うまくいかないのです

「sheet1」検索窓を３つ

	　　検索窓									
	　A　　B　　C									
11

	　　検索結果									
	　A　　　B　　　C　　　D　　　E　　　F　　　G　　　H　　　I									
13

先程と同じ関数、
Ａ列13行には以下の関数を入れて、ドラッグでI列まで引っ張っています
IF(COUNT(sheet2!$J:$J<ROW(sheet2!A1),"",INDEX(sheet2!A:A,SMALL
(sheet2!$J:$J,ROW(sheet2!A1))))

「sheet2」にデータを置いています

先程と違う関数、
Jには以下の関数を入れています
IF(検索!A$10&検索!B$10&検索!C$10="","",IF(IF(AND(検索!A$10<>"",検索!B$10<>"",検索!C$10<>""),COUNTIF(B4,"*"&検索!A$10&"*")*COUNTIF(C4,"*"&検索!B$10&"*")*COUNTIF(D4,"*"&検索!C10&"*"),IF(検索!A$10<>"",COUNTIF(B4,"*"&検索!A$10&"*"),COUNTIF(C4,"*"&検索!B$10&"*"),COUNTIF(D4,"*"&検索!C$10&"*"))),ROW(),""))

しかし、上記でははうまくいかず
済みませんが教えていただければありがたく、
よろしくお願いいたします

＜使用 Excel：unknown、使用 OS：unknown ＞

 なにか、最初のA13セルの式からしてエラーになります。

 ・まず、日本語でやりたいことを説明して下さい。
 ・そして検索窓というのにこう入れたら、こういう結果を出したい、という説明をしてください。
 それがスタートだと思います。
 また、使用しているExcelのversionを明記してください。できることが全く違ってきますので。

（xyz） 2023/10/24(火) 11:58:26

ExcelとOSのバージョンは記入しましょう。Excelのバージョンで使える関数が違います。

計算式を書くのも良いですが、何がしたいかを書いたら、そのほうが早いような気がしますよ。
そうすれば、誰かが（現在のものよりもより）美しい式を提案してくれると思います（笑）

見たところ、検索キー１は日付、検索キー２は品名、検索キー３は県名でしょうかねえ。
（ゆたか） 2023/10/24(火) 12:04:48

わたしの回答はこちら

A13=IFERROR(INDEX(Sheet2!A:A,AGGREGATE(15,6,ROW($A$2:$A$100)/((Sheet2!$A$2:$A$100=$A$11)*(Sheet2!$B$2:$B$100=$B$11)*(Sheet2!$D$2:$D$100=$C$11)),ROW(A1))),"")

A13に上記の式を入れてI13まで右にコピーします。

Sheet2においては２行目から１００行目までデータがあると仮定しています。

Sheet2のJ列は使いません。

他の投稿からExcel2019と仮定しています。

（ゆたか） 2023/10/24(火) 12:32:21

書き忘れていましたが、上記のようにA13-I13まで式を入れた状態で、
A13-I13を下にコピーすると、条件に合う人が複数いても対応できます。
（ゆたか） 2023/10/24(火) 12:46:37

色々アドバイスをいただきありがとうございます
使用しているものは、
Windows10
エクセルのバージョン2019　となります

また、やりたいことですが
Sheet1の検索キーを使って、Sheet２のデータをSheet１のA列～I列の13行目に表したいのですが
うまくいかない状態となります

○Sheet1

検索窓

 　　　　A列　   B列　　 C列									
　　　　日付　　分類　　品名	
11行目

検索結果

 　　　　A列　　 B列　  C列　　 D列　　E列　　F列　　G列　　H列　　 I列									
　　　　日付　　分類　　品名　　県　　工場　　ライン　氏名　性別　  年齢	
13行目

○Sheet２(データ）

 　　　A列　 B列　　 C列　　D列　　E列　　  F列　　 G列　　H列　　I列									
2行目　10/1　林檎　　王林　 山梨　 △工場　×ライン　○氏　女性　 31歳									
3行目　10/1　林檎　　○○　 青森　 △工場　×ライン　○氏　女性　 32歳

ゆたかさんに、いただいた式をためしてみたのですが
やはりうまくいかず、、、
申し訳ありません

（ヤッホー） 2023/10/24(火) 15:01:10

それでは、A13に以下の式を入れて、右、下にコピーしてください。

=IFERROR(INDEX(Sheet2!A:A,AGGREGATE(15,6,ROW($A$2:$A$100)/((Sheet2!$A$2:$A$100=$A$11)*(Sheet2!$B$2:$B$100=$B$11)*(Sheet2!$C$2:$C$100=$C$11)),ROW(A1))),"")

検索キーとして日付、分類に加えて、県名ではなく品名に変えました。
（ゆたか） 2023/10/24(火) 15:58:02

１行で良ければ、下にコピーする必要はありません。
もし、２件が該当した場合は最初に見つけたレコード（一番上のデータ）が表示されます。
（ゆたか） 2023/10/24(火) 16:00:09

 Sheet2のデータにも見出しをつけて、"フィルタオプション"で抽出したらいかがですか？
 基本的な抽出用の道具をExcel君が提供してくれているのですから、何がなんでも関数でと
 こだわらないほうが良いのではないですか?　関数が一番簡単とも限りません。
（xyz） 2023/10/24(火) 16:20:02

皆さんいろいろとアドバイスを
いただきありがとうございます
ゆたかさんのアドバイスを
ためしてみましたが
やはりうまくず、検索しても（0→空白）となります

もう少し調べてみます

（ヤッホー） 2023/10/24(火) 16:37:09

もう一度、質問から読み直しました。
検索条件はANDにしていましたが、どうやらORのようですね。失礼しました。

では、改めまして。

検索シート名：Sheet1
検索キーの位置：A11,B11,C11
データシート名：Sheet2
データ範囲：Sheet2の2行目から

Sheet2のJには以下の式を入力して、下コピー

=IF(Sheet1!$A$11="",0,COUNTIF($A2,Sheet1!$A$11))+IF(Sheet1!$B$11="",0,COUNTIF($B2,"*"&Sheet1!$B$11&"*"))+IF(Sheet1!$C$11="",0,COUNTIF($C2,"*"&Sheet1!$C$11&"*"))

一致する検索キーの数を表示します。日付以外は検索ワードを含めば一致。

Sheet1のA13には以下の式を入力して、右コピー

=IFERROR(IF($A$11&$B$11&$C$11="","",INDEX(Sheet2!A:A,MATCH(COUNTIF($A$11:$C$11,"<>"),Sheet2!$J:$J,0))),"")

入力された検索キーの数と一致するレコードをSheet2のJから探します。
複数一致した場合は、一番上のレコードが表示されます。
（ゆたか） 2023/10/25(水) 12:00:31

＞Sheet2のJには以下の式を入力して、下コピー
J2に入力します。
（ゆたか） 2023/10/25(水) 12:02:13

コメント返信：

[ 一覧(最新更新順) ]

[[20231024111517]] 『検索窓を設けてデータを引っ張る』（ヤッホー） ページの最後に飛ぶ

[[20231024111517]]　『検索窓を設けてデータを引っ張る』（ヤッホー）　ページの最後に飛ぶ