みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

エクセル2000で質問です。

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

(シート1)
    B      D      E      F
6         東京     600
7         大阪             700
8         福岡     800
9   *     宮崎             300
10        横浜     900
11        横浜     400     
12        新潟             500
13  *     東京     550
14        秋田             750

(シート2)
       B       D      
4     600     東京
5     800     福岡
6     900     横浜
7     400     横浜

(シート3)
       B       D      
4     700     大阪
5     500     新潟
6     750     秋田

投稿日時 - 2007-01-06 10:09:11

QNo.2646287

暇なときに回答ください

質問者が選んだベストアンサー

こんにちは。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)ではそういうことはなくなるというような噂も聞きますが、人の行うことに必ずということはありませんので、用心しておいたほうがよいと思います。

投稿日時 - 2007-01-06 11:59:41

お礼

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

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

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

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

投稿日時 - 2007-01-09 11:34:57

ANo.2

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(2)

ANo.1

◆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の式は、上を参考にお作りください

投稿日時 - 2007-01-06 10:32:36

お礼

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

投稿日時 - 2007-01-09 11:21:17

あなたにオススメの質問