エクセルでこんな時どのような式を入れればよいのでしょう？

質問

エクセル２０００を使用しています。
同じシート内で下記のような表を作りたいのですがどのようにすれば良いのでしょう？
Ｄ、Ｅ列の数字は下表のＥ列に反映させ、ＣはＤに反映。Ｂ列に未という文字が入れば
その行は下の表に反映させないようにしたいのです。
但し後にＢ列の未は消える事もあります。さらに例で言えばオレンジの横の未が消えたら
下表のみかんとリンゴの間に入るようにしたいのです。
宜しくお願いします。

　　　　　　Ｂ　　　　　Ｃ　　　　　Ｄ　　　　　Ｅ
５　　　　　　　　　　みかん　　　　３０
６　　　　　未　　　　オレンジ　　　　　　　　　６０
７　　　　　　　　　　りんご　　　　５０
８　　　　　　　　　　もも　　　　　４０
９　　　　　　　　　　レモン　　　　　　　　　　８０
１０　　　　未　　　　バナナ　　　　９０
１１　　　　　　　　　パイン　　　　　　　　　　２０

　　　　　　　　　　　　Ｄ　　　　　　Ｅ
８０　　　　　　　　　みかん　　　　３０
８１　　　　　　　　　りんご　　　　５０
８２　　　　　　　　　もも　　　　　４０
８３　　　　　　　　　レモン　　　　８０
８４　　　　　　　　　パイン　　　　２０

回答No.7

zap35 · ベストアンサー

＃０３，０５です
Ｂ列に「未」以外の文字があっても抽出するようにしました

Ｄ８０
=IF(COUNTA($C$5:$C$70)-COUNTIF($B$5:$B$70,"未")>ROW()-80,INDEX($C$1:$C$70,SUMPRODUCT(LARGE(($B$5:$B$70<>"未")*($C$5:$C$70<>"")*ROW($B$5:$B$70),COUNTA($C$5:$C$70)-COUNTIF($B$5:$B$70,"未")-ROW()+80))),"")

Ｅ８０
=IF(COUNTA($C$5:$C$70)-COUNTIF($B$5:$B$70,"未")>ROW()-80,MAX(OFFSET($D$1,SUMPRODUCT(LARGE(($B$5:$B$70<>"未")*($C$5:$C$70<>"")*ROW($B$5:$B$70),COUNTA($C$5:$C$70)-COUNTIF($B$5:$B$70,"未")+80-ROW()))-1,0,1,2)),"")

お礼

御礼が遅くなりました事をお詫び致します。
重ねての御回答有難うございました。
大変助かりましたし又勉強になりました。

回答No.8

Wendy02

#4のWendy02です。

>しかしＥ８０～の数字が反映されません。

すでに解決しているようなのですし、出された条件とは違う条件のようなので、このままでは分かりません。こちらでは、Excel2000でも、完成しているので、たぶん何か違うのでしょうね。

私のほうは、そのままにしておきます。失礼しました。

お礼

御礼が遅くなりました事をお詫び致します。
いろいろと参考になりました。
この度は有難うございました。

回答No.6

noname#204879

B　 C　　　　　D　　E　 …　 Z
4　 印　項目　　　数1　数2　　　TMP
5　　　 みかん　　 30　　　　　　 1
6　 未　オレンジ　　　　60
7　　　 りんご　　 50　　　　　　 3
8　　　 もも　　　 40　　　　　　 4
9　　　 レモン　　　　　80　　　　5
10　未　バナナ　　 90
11　　　パイン　　　　　20　　　　7
12
13

　　　D　　 E
79　項目　　個
80　みかん　30
81　りんご　50
82　もも　　40
83　レモン　80
84　パイン　20
85
86

Z5: =IF(AND(B5="",C5<>""),ROW(A1),"") ← これを下方にズズーッと複写
D80: =IF(ISERROR(SMALL(Z$5:Z$78,ROW(A1))),"",INDEX($C$5:$E$78,SMALL(Z$5:Z$78,ROW(A1)),1))
E80: =IF(D80="","",VLOOKUP(D80,$C$5:$E$78,2,FALSE)+VLOOKUP(D80,$C$5:$E$78,3,FALSE))

お礼

有難うございました。
大変参考になりました。

回答No.5

zap35

＃０２です。式を訂正します。
Ｄ８０
=IF(COUNTA($C$5:$C$70)-COUNTA($B$5:$B$70)>ROW()-80,INDEX($C$1:$C$70,SUMPRODUCT(LARGE(($B$5:$B$70="")*($C$5:$C$70<>"")*ROW($B$5:$B$70),COUNTA($C$5:$C$70)-COUNTA($B$5:$B$70)-ROW()+80))),"")

Ｅ８０
=IF(COUNTA($C$5:$C$70)-COUNTA($B$5:$B$70)>ROW()-80,MAX(OFFSET($D$1,SUMPRODUCT(LARGE(($B$5:$B$70="")*($C$5:$C$70<>"")*ROW($B$5:$B$70),COUNTA($C$5:$C$70)-COUNTA($B$5:$B$70)+80-ROW()))-1,0,1,2)),"")

今度はデータ範囲は70行目までにしました。見比べれば変更点が分かると思います。

お礼

有難うございました。
私の望むようになりました。
しかし、質問なんですが上の表のＢ列が「空白」もしくは「何か入力」と
いう定義の元で反映させているのですよね？
もし「未」もしくは「空白ないし他の文字が入力」という分け方になると
さらにややこしくなるのでしょうか？

回答No.4

Wendy02

こんにちは。

元のデータの金額欄は、両方に入れると、多いほうしか出てきません。

以下のような式になります。

D80:～

=IF(ROW(A1)>(COUNTA($C$5:$C$50)-COUNTA($B$5:$B$50)),"",INDEX($B$1:$E$50,SMALL(INDEX(($B$5:$B$50<>"未")*($C$5:$C$50<>"")*ROW($C$5:$C$50),,),COUNTA($B$5:$B$50)+COUNTBLANK($C$5:$C$50)+ROW(A1)),2))

E80:～

=IF(C80="","",MAX(INDEX($D$1:$E$50,SMALL(INDEX(($B$5:$B$50<>"未")*($C$5:$C$50<>"")*ROW($C$5:$C$50),,),COUNTA($B$5:$B$50)+COUNTBLANK($C$5:$C$50)+ROW(A1)),)))

ところで、今まで、私がずっと手がけてきた方のご質問に良く似ています。だから、同じように扱いました。少し、参考になるかもしれません。同じものでマクロも出てきます。

その方のご質問の例に倣って、データは50行ぐらいだったので、こちらも50行にしました。ただ、その方は、別のシートに反映させています。何かの参考になりましたら、幸いです。

http://oshiete1.goo.ne.jp/kotaeru.php3?qid=2646287

http://oshiete1.goo.ne.jp/kotaeru.php3?qid=2624315

お礼

有難うございました。
しかしＥ８０～の数字が反映されません。
Ｄ８０～に関しては上手くいきました。
どこがおかしいのでしょうか？

回答No.3

zap35

＃０２さんと同じような疑問は多々感じますが、とりあえず式は作ってみました。

Ｄ８０の式は
=IF(COUNTA($C$5:$C$11)-COUNTA($B$5:$B$11)>ROW()-80,INDEX($C$1:$C$11,SUMPRODUCT(LARGE(($B$5:$B$11="")*ROW($B$5:$B$11),COUNTA($C$5:$C$11)-COUNTA($B$5:$B$11)-ROW()+80))),"")

Ｅ８０の式は
=IF(COUNTA($C$5:$C$11)-COUNTA($B$5:$B$11)>ROW()-80,MAX(OFFSET($D$1,SUMPRODUCT(LARGE(($B$5:$B$11="")*ROW($B$5:$B$11),COUNTA($C$5:$C$11)-COUNTA($B$5:$B$11)+80-ROW()))-1,0,1,2)),"")

それぞれ下方向にコピーしてください。なおデータ範囲は11行目までにしていますので実シートに合わせて変更が必要です

回答No.2

web2525

いくつかの補足をお願いします。

・上下の表は同一セル内に存在しますか？
・上表のＣ列の項目は常に一定？（項目が増えたり減ったりしますか？）
・また重複することはありますか？
・上表のＤ・Ｅ列は常にどちらか片方にデータが入っていますか？
（未入力であったり、両方に入る事はありますか？）
・マクロの利用は可能ですか？
・過去に関連した質問がありますか？

お礼

早々に御回答頂き有難うございました。

＞・上下の表は同一セル内に存在しますか？
同一セル内という意味が今ひとつ分からないのですが
同じシート上ではあるもののまったく違う表と認識して頂いて結構です。
別のシートにそれぞれあってもよいのですが便宜上同じシート上にあります。

＞・上表のＣ列の項目は常に一定？（項目が増えたり減ったりしますか？）
項目の列は増減する事はありません。

＞・また重複することはありますか？
内容が？って事でしょうか？
例で言えばみかんやりんごという意味でしょうか？
それならば重複する可能性は十分あります。

＞・上表のＤ・Ｅ列は常にどちらか片方にデータが入っていますか？
（未入力であったり、両方に入る事はありますか？）
必ず片方に入ります。空白や両方に入る事は有りません。

＞・マクロの利用は可能ですか？
他のシートにも式が入っているのと、マクロに関してはほとんど使用した事が
無いので出来れば式で対応したいです。

＞・過去に関連した質問がありますか？
似たような質問はあります。いずれにしてもほとんど素人ですので
ご配慮願えればと思います。

その他補足が必要なら申し付け下さい。

回答No.1

boy_boy

こんにちは。

左端の数字が行番号で、D・E列のどちらかにしか「値」が無ければ
作業列（仮にG列）が使える場合
G5セルに
=IF(B5<>"未",ROW(A1),"")
として下へコピー。

D80セルに
=IF(COUNT($G$5:$G$11)<ROW(A1),"",INDEX(C$5:C$11,SMALL($G$5:$G$11,ROW(A1))))

E80セルに
=IF(COUNT($G$5:$G$11)<ROW(B1),"",INDEX(D$5:D$11,SMALL($G$5:$G$11,ROW(B1)))+INDEX(E$5:E$11,SMALL($G$5:$G$11,ROW(B1))))
で下へコピー。
では駄目でしょうか。

お礼

早々に有難うございます。
作業列を使用する場合ですが実際にはＧ列は使用します。
まったく使用しない列を使用するという認識で宜しいのでしょうか？
又、作業列を使用しない場合はかなり長い式になってしまうのでしょうか？