以前と似た質問でエクセル関数=複数条件付関数です

質問

すいません、以前と同じ質問内容なのですが追加で教えてください。
シート1とシート2があります。横はA列～縦は1～があります。
シート1にはA1～D3までの範囲でA1～A3には野菜or空欄、B1～B3には番号、C1～C3には名称、D1～D3には自ら入力する判定結果(マルorバツ)が入ってます。
シート1
A　　 B　 　C　　 D　　　　　　　　　　
野菜　1　 なす 　　○
　　　2 　ねぎ　　 ○
野菜　3　 にら　　 ×
シート2
A B
1 なす

のようにシート1のA列で野菜と入力したかつD列で○の判定した番号と名称がのみがシート2のごうに自動でもっていけるようにしたいのです。
途中まで関数はくまれてます。追加でいれる関数が分かりませんので教えてください。
ちなみにシート2のA1には=IF(COUNTIF(Sheet1!$D$1:$D$100,"○")<ROW(A1)
,"",INDEX(Sheet1!B$1:B$100,SMALL(IF(Sheet1!$D$1:$D100="○",
ROW($A$1:$A$100)),ROW(A1))))
が入ってます。

回答No.4

kybo · ベストアンサー

>当てはまらない場合は空欄にしたいのですがどうしたらよろしいですか？

条件に当てはまらない場合とはどういう場合が不明ですが、
以下でどうでしょうか？

Excel2003までの場合、またはExcel2003まででも開く可能性がある場合：
=IF(ISNA(VLOOKUP(A1,Sheet1!B1:D3,2,FALSE)),"",VLOOKUP(A1,Sheet1!B1:D3,2,FALSE))

Excel2007以降のみの場合：
=IFERROR(VLOOKUP(A1,Sheet1!B1:D3,2,FALSE),"")

回答No.6

MackyNo1

A列が野菜、D列が○の2つの条件で抽出するなら、数式は複雑になりますが、条件部分などに配列計算をしない、以下のような数式のほうが計算負荷が少ないかもしれません（Ctrl＋Shift+Enterの操作の必要はありません）。

=INDEX(Sheet1!B:B,SMALL(INDEX(((Sheet1!$A$1:$A$100<>"野菜")+(Sheet1!$D$1:$D$100<>"○"))*1000+ROW($A$1:$A$100),),ROW(A1)))&""

データ数が多いなら、Aheet2のB列の式だけ上記の数式を入力し、以下のようにB列の値が空白なら空白を返す数式にしたほうが、さらに計算負荷を少なくすることができます。
（B列に上記の式で範囲を「Sheet1!C:C」に変更した数式を入力し、A列はそのセルの値を参照して配列計算を実行しない）

=IF(B1="","",INDEX(Sheet1!B:B,SMALL(INDEX(((Sheet1!$A$1:$A$100<>"野菜")+(Sheet1!$D$1:$D$100<>"○"))*1000+ROW($A$1:$A$100),),ROW(A1))))

回答No.5

KURUMITO

ご質問ではＡ列には必ずしも野菜が入力されていない場合もあるということですね。その場合には式もやや複雑になります。作業列を作って対応するのがよいでしょう。
シート1では2行目から下方にデータが入力されているとします。
Ｅ2セルには次の式を入力して下方にドラッグコピーします。

=IF(D2="","",IF(A2<>"",A2&D2&COUNTIF(E$1:E1,A2&D2&"*")+1,INDEX(A:A,MATCH("ー",A$1:A1))&D2&COUNTIF(E$1:E1,INDEX(A:A,MATCH("ー",A$1:A1))&D2&"*")+1))

*は半角英数モードでＳｈｉｆｔキーを押しながら「け」のキーを押します。
"ー"ではーが半角英数モードではいけません。一旦式を入力した上で最後にひらがなモードでーを入力するのがよいでしょう。

お求めの表はシートに表示させることにしてシート1でＡ列で野菜とか使っていますので魚などのケースもあるのでしょう。それらを考慮してＡ1セルに野菜と入力し、×の選択もできるようにＢ1セルには○を入力します。その上でＡ2セルには次の式を入力してＢ2セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(COUNTA($A$1:$B$1)<>2,"",IF(COUNTIF(Sheet1!$E:$E,$A$1&$B$1&ROW(A1))=0,"",INDEX(Sheet1!$B:$C,MATCH($A$1&$B$1&ROW(A1),Sheet1!$E:$E,0),COLUMN(A1))))

勿論該当のデータが無ければエラーではなく空白の表示になります。

回答No.3

kybo

>すいません、関数に含まれているコメジルシみたいな文字はなんでしょうか？

*はアスタリスクです。「かつ」や「AND」の条件なので*を使用しています。
通常は数式では「かける」のとき使用します。

=2*3

とセルに入れると6となります。

補足

分かりました、シート2のB列の名称がはいるとこなのですが、野菜かつ○なら名称がはいるのですが、条件に当てはまらなかった場合にはエラーがはいるのですが、当てはまらない場合は空欄にしたいのですがどうしたらよろしいですか？条件に当てはまった場合は教えていただいたとおり名称がきます。
何度もすいません。

回答No.2

keithin

ムズカシイ数式の応用ができないようなら，前回にお話しした猿でもできる方法で。

シート１のE1に
=IF(AND(A1="野菜",D1="○"),ROW(),"")
と記入してリストの下端まで埋めておき，

シート２のA1に
=IF(ROW(A1)>MAX(Sheet1!E:E),"",INDEX(Sheet1!B:B,SMALL(Sheet1!E:E,ROW(A1))))
シート２のB1に
=IF(ROW(A1)>MAX(Sheet1!E:E),"",INDEX(Sheet1!C:C,SMALL(Sheet1!E:E,ROW(A1))))
とそれぞれ記入して下向けにコピーして埋めておくとできます。

いずれの数式もCtrl+Shift+Enterはしない，ふつーのただの数式なので間違えないよう注意してください。
前回トラブっていた「実はセル結合してました」みたいな表でも，支障なく計算できます。

回答No.1

kybo

B列の番号に重複がないなら、以下でできます。

シート2、A1

=IF(SUMPRODUCT((Sheet1!$D$1:$D100="○")*(Sheet1!$A$1:$A100="野菜"))<ROW(Sheet1!A1),"",INDEX(Sheet1!B$1:B$100,SMALL(IF((Sheet1!$D$1:$D100="○")*(Sheet1!$A$1:$A100="野菜"),ROW(Sheet1!$A$1:$A$100)),ROW(Sheet1!A1))))

シート2、B1

=IF(SUMPRODUCT((Sheet1!$D$1:$D100="○")*(Sheet1!$A$1:$A100="野菜"))<ROW(Sheet1!A1),"",VLOOKUP(A1,Sheet1!B1:D3,2,FALSE))

補足

有り難うございます。すいません、関数に含まれているコメジルシみたいな文字はなんでしょうか？