エクセル２０００で質問です。

質問

例のようにシート１のＥ列に数値が入力されるとシート２へ反映、Ｆ列に数値が
入力されるとシート３に反映されるようにしたいのです。
しかもシート１のＢ列に「＊」が入るとその行はまったく反映させないという
条件を付け加えたいのです。
さらに欲を言えば「＊」印はまれに入れたり消したりという操作が発生する
場合があります。後で消した場合もシート１の順序に基づいてシート２、３に
反映され最下位の行に反映させたくありません。
あるいは当初「＊」が入っていなかったにも関わらず後で「＊」が入った場合は
シート２、３に空白行を作るのでは無く上から詰めた状態で表示させたいのです。
出来ればマクロではなく式で対応したいです。
補足が必要なら申し付け下さい。

（シート１）
　　　　Ｂ　　　　　　Ｄ　　　　　　Ｅ　　　　　　Ｆ
６　　　　　　　　　東京　　　　　６００
７　　　　　　　　　大阪　　　　　　　　　　　　　７００
８　　　　　　　　　福岡　　　　　８００
９　　　＊　　　　　宮崎　　　　　　　　　　　　　３００
１０　　　　　　　　横浜　　　　　９００
１１　　　　　　　　横浜　　　　　４００　　　　　
１２　　　　　　　　新潟　　　　　　　　　　　　　５００
１３　　＊　　　　　東京　　　　　５５０
１４　　　　　　　　秋田　　　　　　　　　　　　　７５０

（シート２）
　　　　　　　Ｂ　　　　　　　Ｄ　　　　　　
４　　　　　６００　　　　　東京
５　　　　　８００　　　　　福岡
６　　　　　９００　　　　　横浜
７　　　　　４００　　　　　横浜

（シート３）
　　　　　　　Ｂ　　　　　　　Ｄ　　　　　　
４　　　　　７００　　　　　大阪
５　　　　　５００　　　　　新潟
６　　　　　７５０　　　　　秋田

回答No.2

Wendy02 · ベストアンサー

こんにちは。Wendy02です。

今年もよろしくね。私自身は、いつまで続けられるのか分かりませんが先が見えませんが、出来るところまで続けていきます。

ところで、このシート1には、タイ,韓国,中国などの除外項目はないのですか？
もし、前回の続きでしたら、このような数式になります。なお、除外項目がなくても、同様に出力できます。

http://oshiete1.goo.ne.jp/qa2619166.html

シート2　（補助列）

H4:～
=SMALL(INDEX(ISERROR(MATCH(Sheet1!$D$6:$D$100,{"大阪","宮崎","新潟","秋田","タイ","中国","香港","韓国"},0))*(Sheet1!$B$6:$B$100<>"*")*ROW($D$6:$D$100),,),SUMPRODUCT((INDEX(ISERROR(MATCH(Sheet1!$D$6:$D$100,{"大阪","宮崎","新潟","秋田","タイ","中国","香港","韓国"},0))*(Sheet1!$B$6:$B$100<>"*")*ROW($D$6:$D$100),,)=0)*1)+ROW(A1))

シート3　（補助列）

H4:～
=SMALL(INDEX(ISERROR(MATCH(Sheet1!$D$6:$D$100,{"東京","福岡","横浜","タイ","中国","香港","韓国"},0))*(Sheet1!$B$6:$B$100<>"*")*ROW($D$6:$D$100),,),SUMPRODUCT((INDEX(ISERROR(MATCH(Sheet1!$D$6:$D$100,{"東京","福岡","横浜","タイ","中国","香港","韓国"},0))*(Sheet1!$B$6:$B$100<>"*")*ROW($D$6:$D$100),,)=0)*1)+ROW(A1))

それぞれのシートの
（修正）

B4:～
=IF(OR($H4="",$H4>=COUNTA(Sheet1!$D$1:$D$100)+5),"",INDEX(Sheet1!$D$1:$F$100,$H4,2))

C4：～
=IF(OR($H4="",$H4>=COUNTA(Sheet1!$D$1:$D$100)+5),"",INDEX(Sheet1!$D$1:$F$100,$H4,1))

ところで、私は、前回、書きそびれたことですが、これは、「フィルタ・オプション」という機能の処理です。フィルタ・オプションは、上級までとは言いませんが、私自身も、以前、掲示板で少し教えていただきました。「フィルタ・オプション」を使いこなすというのは、少し、教えてもらわないと分かりません。関数の数式は壊れやすいし、修正しにくい欠点があります。もし、よろしかったら、紹介します。

それと、ちょっと、これとは関係ないのですが、あまり、同じワークシートに、こういう複雑な内容のものを作り込みすぎると、思わぬトラブルがありますので、バックアップ（Excelの備え付けの機能ではない方法）を自分なりにしておいたほうがよいと思います。

たぶん、次バージョン(Excel2007)ではそういうことはなくなるというような噂も聞きますが、人の行うことに必ずということはありませんので、用心しておいたほうがよいと思います。

お礼

御礼が大変遅くなり申し訳ありませんでした。
本年もどうぞ宜しくお願い致します。
お陰で上手くいきました。
有難うございました。
＞「フィルタ・オプション」
機械があれば是非御教授頂きたいと思います。

＞それと、ちょっと、これとは関係ないのですが、あまり、同じワークシートに、こういう複雑な内容のものを作り込みすぎると、思わぬトラブルがありますので、バックアップ（Excelの備え付けの機能ではない方法）を自分なりにしておいたほうがよいと思います。

おっしゃる通りだと思います。
バックアップは取るようにしていますし、式も保護して書き換えが
出来ないように自分なりにですが工夫しています。

また今年もお世話になると思いますがどうか宜しくお願い致します。

回答No.1

maron--5

◆Sheet2の
B4=IF(ROW(A1)>SUMPRODUCT((Sheet1!$B$6:$B$20<>"＊")*(Sheet1!$E$6:$E$20<>"")),"",INDEX(Sheet1!$E$1:$E$20,SMALL(INDEX(SUBSTITUTE((Sheet1!$B$6:$B$20<>"＊")*(Sheet1!$E$6:$E$20<>""),0,10^5)*ROW($6:$20),),ROW(A1))))

D4=IF(ROW(A1)>SUMPRODUCT((Sheet1!$B$6:$B$20<>"＊")*(Sheet1!$E$6:$E$20<>"")),"",INDEX(Sheet1!$D$1:$D$20,SMALL(INDEX(SUBSTITUTE((Sheet1!$B$6:$B$20<>"＊")*(Sheet1!$E$6:$E$20<>""),0,10^5)*ROW($6:$20),),ROW(A1))))

★共に、Enterで式を確定させて、下にコピー
★Sheet3の式は、上を参考にお作りください

お礼

御礼が大変遅くなり申し訳ありませんでした。
お陰で上手くいきました。
有難うございました。