どのような関数を使用すれば良いですか？

質問

どのような関数を使用すれば良いですか？

エクセルで
Ｆ列に行事名
Ｇ～Ｕ列に開催日を入力しました。

Ｗ列に日付が入っており、
Ｘ列にＧ～Ｕ列に打ち込んだ日付とあっている場合に
行事名が表示されるようにしたいと思っております。

複数の場合もＸ列に複数表示されるにはどうすればよいですか？
関数のみでお願い致します。

また、列は特に限りが無いので、
Ｙ、Ｚ、ＡＡなども空いている状態です。

どなたかご教授願います。

回答No.8

某HN クロメート(Chromate)（@CoalTar） · ベストアンサー

＞全く理屈がわからないですが理想どおりの結果になりました。
0. ツール - オプション - 計算方法 [レ]反復計算(回数100のまま)
1. Z1セル 行事の数として =COUNTA(F:F)
2. Z2セル =IF(Y2="",0,IF(AA2="END",Z2,Z2+1))
3. AA2セル =IF(Y2="","",IF(Z2<=Z1,"","END"))
4. X3セル
=IF($Y$2="","",IF($AA$2="END",TRIM(X3),
IF(ISNA(MATCH(W3,INDEX($G$3:$U$101,$Z$2,),0)),X3,
X3&" "&INDEX($F$3:$F$101,$Z$2))))
下へオートフィル
解説
0. Z2セルを変化させるための下準備
1. 終わりの範囲を設定
2. Y2セルに何か入ったら、1を加算してゆく
　終わりになったらそのまま表示
3. 行事数が最後まで入ったら"END"表示
4. 日付が書かれた範囲のY行目(Y2セルの値)を探しに入って
　 なかったらそのまま表示、あったら半角スペースと行事名を付け足す

プログラム的な方法なので数式で行うにはnattocurryさんの方法となります
改善案
Y1セル 1
Y2セル =INDEX($F$3:$F$101,Y1)
Y3セル =IF(ISNA(MATCH($W3,INDEX($G$3:$U$101,Y$1,),0)),"",Y$2)
日付の数分、下へオートフィル
Y列を行事の数分、右へオートフィル

X3セルは CONCATENATE関数(Excel2003までは引数は30まで)と
[Ctrl]押しつつクリックなどで数式を作る、
=CONCATENATE(Y3," ",Z3,・・・)&CONCATENATE(・・・)

お礼

反復計算というのは知りませんでした。

勉強になりました。
また、今後作成する時の参考にさせていただきます。
ありがとうございました。

回答No.7

某HN クロメート(Chromate)（@CoalTar）

ちょっとだけ間違いがあったので修正
行事数100の場合は 反復計算を101にしないとまずかったです。
ということで99個までと、数式がわかりやすいように少し修正。
結果も添付。
W3セル
=IF($Y$2="","",IF($AA$2="END",TRIM(X3),
IF(ISNA(MATCH(W3,INDEX($G$3:$U$101,$Z$2,),0)),X3,
X3&" "&INDEX($F$3:$F$101,$Z$2))))
下へオートフィル

回答No.6

某HN クロメート(Chromate)（@CoalTar）

行事数が100まででやってみました
0. ツール - オプション - 計算方法 [レ]反復計算(回数100のまま)
1. Z1セル 行事の数として =COUNTA(F:F)
2. Z2セル =IF(Y2="",0,IF(AA2="END",Z2,Z2+1))
3. AA2セル =IF(Y2="","",IF(Z2<=Z1,"","END"))
4. X3セル
=IF($Y$2="","",IF($AA$2="END",X3,
IF(ISNUMBER(MATCH(W3,INDEX($G$3:$U$102,$Z$2,),0)),
X3&" "&INDEX($F$3:$F$102,$Z$2),X3)))
下へオートフィル
Y1セルに何かの値を入れると出来上がりです。
追加・変更があった場合は Y2セルの値を消し(リセット)、もう一度値を入れてください。

お礼

おおお！

全く理屈がわからないですが理想どおりの結果になりました。

素晴らしいです。本当にありがとうございました。

回答No.5

nattocurry

＞そうなんです。自分もこの方法しか思いつかなかったのです。

だったら、そのことを書いた上で、もっと良い方法が無いかと質問するのが常識なんじゃないですか？

こっちは、あなたがまったく解らないものだと思って、わざわざ検証までしてから、回答しているんですよ。

どっちみち、関数を使うのであれば、少なくともX列のすべて(W列に日付を書いてある分だけ)のセルに数式を書き込まなければなりません。
関数(を使った数式)は、それが書かれたセルの内容を変えることしかできないからです。
他のセルの内容を能動的に変えることはできません。「他のセル」から参照されることによって受動的に「他のセル」の内容を変えることはできますが、結局「他のセル」にも数式が入っています。

数式の入っていないセルの表示内容を変えようとしたら、マクロを使うしかないのです。

回答No.4

nattocurry

マクロが組めればマクロのほうが楽なんですけど、関数のみということなので、多少強引になりますが、こんなのはどうでしょうか。

行事　１　２　３　・・・　となっている行が2行目だと仮定して回答します。

F3から下側に並んでいる行事名を、Y2から右側にも並べて書きます。

そして、Y3に
=IF(COUNTIF($G$3:$U$3,$W3)>0,Y$2,"")
と入力します。

入力したら、Y3をZ3から右側にもコピペします。

そのままだと、どの計算式にも$G$3:$U$3という範囲が入っていて不完全なので、Z3の計算式は、$G$4:$U$4、AA3の計算式は、$G$5:$U$5、AB3の計算式は、$G$6:$U$6、というように、１つずつ直します。ここがちょっと面倒ですけど、上手い方法が見つからなかったもので。

X3に、
=Y3&Z3&AA3&AB3&・・・・・&一番右のセル
と、Y3から右側すべてのセルの文字を結合させる数式を入力。

そのあと、X3から右側を範囲選択して、下方向にコピペするだけ。

イベント名が複数あるときに、このままだとイベント名がぴったりくっつくので、Y2より右側のイベント名の最後にスペースを足すと、イベント名の間が空きます。

Y列より右側が目障りなら、非表示にします。

お礼

ご回答ありがとうございました。
解決いたしました。

補足

そうなんです。自分もこの方法しか思いつかなかったのです。

実は行数がかなりありまして・・・。この方法だと大変かなと。

マクロなら楽なんですね、マクロがあまり解からないので
（記録させて繰り返させる消去などしか出来ず、VBAの変更が出来ない。）
簡単ならいいのですが。

回答No.3

KURUMITO

Ｘ1セルに次の式を入力して下方にオートフィルドラッグします。

=IF(COUNTIF(G1:U1,W1)>0,F1,"")

お礼

回答ありがとうございました。

解決いたしました。

補足

Ｗ列の日付に該当する行事名（Ｆ列）をＸ列に表示させたいのです。

＞複数の場合もＸ列に複数表示されるには
と、ありますが、何を表示するんでしょうか？

該当する日付が複数の場合、行事名も複数表示させたいのです。

この数式ですと、行が変わるたびに変わってしまいます。

要するにＷ列の日付をＧ～Ｕ全体から抽出し、
該当するＦ列の文字をＸ列に返したいのです。

解かり難くてスイマセンでした。

回答No.2

keithin

行事の日付の記入欄がG2:U20ぐらいあるとすると
W2の日付の隣のX2に
=IF(COUNTIF($G$2:$U$20,W2),INDEX(F:F,SUMPRODUCT(($G$2:$U$20=W2)*ROW($G$2:$U$20))),"")
と入れておく感じでしょうか。

お礼

回答ありがとうございました。
解決いたしました。

SUMPRODUCT関数の検索が勉強になりました。

補足

F:Fでやるんですね。
勉強になります。

教えて頂いたとおり入力し、下の行事が優先してしまうものの、解決しました。

ちなみに、複数表示させるのはやはり出来ないのでしょうか？
もしくは最初のCountifで2はY列に,3はZ列と入力する感じなのでしょうか？

その場合、Y列のINDEX関数以降はどのような感じなのでしょうか？
重ねての質問申し訳ありません。

回答No.1

HRI-Sankou

Ｘ１に右の式を入れて　=IF(COUNTIF(G1:U1,W1)>=1,F1,"")
下にコピーしてみて下さい。

複数の場合もＸ列に複数表示されるには
と、ありますが、何を表示するんでしょうか？

お礼

回答ありがとうございました。
解決いたしました。

補足

Ｗ列の日付に該当する行事名（Ｆ列）をＸ列に表示させたいのです。

＞複数の場合もＸ列に複数表示されるには
と、ありますが、何を表示するんでしょうか？

該当する日付が複数の場合、行事名も複数表示させたいのです。

この数式ですと、行が変わるたびに変わってしまいます。

要するにＷ列の日付をＧ～Ｕ全体から抽出し、
該当するＦ列の文字をＸ列に返したいのです。