[[20181015105015]] 『INDEX関数について』(うみ) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『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


GobGobさま

ありがとうございます!

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.