[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『INDEX関数について』(うみ)
お世話になります。
受講一覧を使って、誰がどんな講座を受講したか
履歴を求めようとしています。
一覧J列 ⇒ 受講日
一覧A列 ⇒ 講座コード
一覧F列 ⇒ 個人コード
受講履歴シートの3行目に講座コード
D列に個人コードがあり
個人がどの講座を何年に受講しているかを表示させたいのですが
下記の関数ではエラーが出てしまいます。
=INDEX(一覧!$J$2:$J$3000,SUMPRODUCT(一覧!$A$2:$A$3000=受講履歴!U3)*(一覧!$F$3:$F$3000=受講履歴!D5),ROW(一覧!$J$2:$J$3000))
自分では穴が見つからず困っています。
宜しくお願いします。
< 使用 Excel:Excel2010、使用 OS:Windows10 >
F列のスタートだけが3行目だけど。
(GobGob) 2018/10/15(月) 11:06
ありがとうございます!
F2の誤りなので修正しましたがエラーは解除されませんでした。
ちなみにエラー値は#REF!です。
どの参照値が誤っているのかが情けないことに
分かりません・・
(うみ) 2018/10/15(月) 11:10
ROW関数の引数が2行目から3000行となっているのであれば 一覧!$J$2:$J$3000 は 一覧!$J$1:$J$3000 では? また、その式では例えば一覧の3行目、5行目が該当する場合、3+5の8行目の値を持ってくることになるが。 (ねむねむ) 2018/10/15(月) 11:15
>SUMPRODUCT(一覧!$A$2:$A$300
カッコがありません。
=INDEX(一覧!J:J,SUMPRODUCT((一覧!$A$2:$A$3000=U3)*(一覧!$F$2:$F$3000=D5),ROW(一覧!$J$2:$J$3000))) ~~~~ ~~ ↑ ↑ または =LOOKUP(1,0/((一覧!$A$2:$A$3000=U3)*(一覧!$F$2:$F$3000=D5)),一覧!$J$2:$J$3000)
受講履歴シートの数式ですから、シート名を付ける必要はありません。
以上 (笑) 2018/10/15(月) 11:25
J2⇒J1に修正するとエラーは消えましたが
おっしゃるように正確な値が反映しないようです。
試しに数式コピーをしてみると
全ての行に年数が表示されます。
正確には、その講座を受講した人のみ年数が入るはずなのに・・
いっそROW関数を外してみましたが
同じ結果が出ました。
宜しくお願いします
(うみ) 2018/10/15(月) 11:30
同じ口座コード、個人コードは複数あるのだろうか? もしそうであれば =INDEX(一覧!$J$1:$J$3000,SMALL(IF((一覧!$A$2:$A$3000=U3)*(一覧!$F$3:$F$3000=D5),ROW($2:$3000),""),ROW(A1))) と入力してShiftキーとCtrlキーを押しながらEnterキーで式を確定(確定後式が{}で囲まれればOK)、その後下へフィルコピーではどうか? (ねむねむ) 2018/10/15(月) 11:35
反応がありませんけど、どうなってるんですかね?
>ROW(一覧!$J$2:$J$3000)
↑ もシート名はなくてもオッケー
>=LOOKUP(1,0/((一覧!$A$2:$A$3000=U3)*(一覧!$F$2:$F$3000=D5)),一覧!$J$2:$J$3000)
該当するものがない場合のエラー処理を入れると
=IFERROR(LOOKUP(1,0/((一覧!$A$2:$A$3000=U3)*(一覧!$F$2:$F$3000=D5)),一覧!$J$2:$J$3000),"")
以上 (笑) 2018/10/15(月) 11:39
やはりうまく反映しませんでした・・
下記の式では#N/Aが出てしまいました。
=LOOKUP(1,0/((一覧!$A$2:$A$3000=U3)*(一覧!$F$2:$F$3000=D5)),一覧!$J$2:$J$3000)
関係のない年数が反映していますが、年数を返していますので
J列を見には行っているようです
(うみ) 2018/10/15(月) 11:40
=INDEX(一覧!$J$1:$J$3000,SMALL(IF((一覧!$A$2:$A$3000=U3)*(一覧!$F$3:$F$3000=D5),ROW($2:$3000),""),ROW(A1)))
確定後{}でくくられましたが、結果は#N/Aでした。
講座コードは重複しませんが、個人コードが一人で複数口座受講している場合は
重複します。
(うみ) 2018/10/15(月) 11:47
>講座コードは重複しませんが、個人コードが一人で複数口座受講している場合は >重複します。 であれば >(ねむねむ) 2018/10/15(月) 11:35 は無視してくれ。
(ねむねむ) 2018/10/15(月) 11:52
再度下記を試したところ
FALSE か TRUE 又は#N/Aのどちれかを表示するようになりました
=INDEX(一覧!J:J,SUMPRODUCT((一覧!$A$2:$A$3000=U3)*(一覧!$F$2:$F$3000=D5),ROW(一覧!$J$2:$J$3000)))
(うみ) 2018/10/15(月) 11:54
説明不足でご面倒をお掛けします。
宜しくお願いします
(うみ) 2018/10/15(月) 11:55
ちょっとテストしてもらいたい。 =COUNTIFS(一覧!$A$2:$A$3000,U3,一覧!$F$3:$F$3000,D5) とした場合に結果はなんと出るだろうか? (ねむねむ) 2018/10/15(月) 11:56
#VALUE!が表示されています。
(うみ) 2018/10/15(月) 11:59
>=COUNTIFS(一覧!$A$2:$A$3000,U3,一覧!$F$3:$F$3000,D5)
一覧!$F$2:$F$3000 では? ~~~~ 以上 (笑) 2018/10/15(月) 12:02
ありがとうございます。
F2に打ち直して試しましたが
同じく#VALUE!が返りました。
(うみ) 2018/10/15(月) 12:06
一覧の内容は下記のような感じです
講座コードは重複しませんとお伝えしましたが
個人コード同様、一つの講座に複数人受講すると、データ的には重複となります。
重要な説明が不適切で申し訳ありません
A列 F列 J列
講座コード 個人コード 受講日付
L1
L1
L1
L2
L2
L3
・
・
(うみ) 2018/10/15(月) 12:15
講座コードと個人コードの組み合わせが重複しているかどうか(講座コードがAで個人コードがBが複数件あるか)の問題なので 口座コード、個人コード単体では重複していても構わない。 (ねむねむ) 2018/10/15(月) 12:21
一覧を再確認しました。
懸念されている通り、
一人で同じ講座を複数回受講しているケースも存在しました。
この重複を解消すれば解決となるのでしょうか?
複数回の受講は一回とみなして、一覧を作り直そうと思います。
(うみ) 2018/10/15(月) 12:28
そうすれば笑さんの (笑) 2018/10/15(月) 11:39 での式で大丈夫なはずだ。 (ねむねむ) 2018/10/15(月) 12:37
期待通りの結果を得ることができました。
説明不足で皆さんの貴重なお時間を割いてしまい
申し訳ありませんでした。
でもこれで先に進めます。
ありがとうございました
(うみ) 2018/10/15(月) 13:41
結局 #N/A や #VALUE! の原因は何だったんですかね?
・一覧シートのJ列(受講日)は日付データ(数値) ・個人コードと講座コードの同じ組み合わせは重複しない
ということなら SUMIFS でもできますね(SUMPRODUCT だけでもできますけど) 該当するものがない場合は「0」になるので、表示形式で「0」は非表示にする。
>D5 >U3
↑ 行が違いますけど、合ってるんですか?
以上 (笑) 2018/10/15(月) 19:41
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.