エクセルの数式相談。

質問

エクセルの数式を考えているのですが、どうしてもわからないため、質問させて頂きます。

ある部品をある設備を用いて作っているとします。ある部品は、12個を1セットとして梱包するとします同日、同設備、同じ元部品を使用して製造した場合に、同Lot.であるとします。

この時に、

A列にLot.　B列に製造日、C列に使用した設備番号、D列に“同日に同設備を用いて製造した何回目の製造か”が記されている表があります。（D列の数値が異なるのは、元部品が異なる場合です）

この表に対して、

わかりやすくするため、1列（E列）を開けて、F列にはLotの一部抜粋、G列にはF列に記載のLot.を製造した場合の1ダースにおさまらなかった余りの個数が記載してあります。

この時に、例えば I列に、F列のロットに対するG列の残り個数の生産が、同日の同設備を用いて製造した最後の製造かどうかを示すことはできないでしょうか？

一発で算出できる数式にこだわっているわけではありません。最後か、最後でないかが、出力されればよいのですが、if　や Vlookupを使用する気がしていますが、いまいちうまく考えられません。

何卒、よろしくお願いします。

回答No.5

kagakusuki

>概ねあっているようなのですが、“最後ではない”と出力されるところを一つ一つみてみると、実際には、“最後”なのに“最後ではない”と出力されている箇所があります。

　申し訳御座いません。

>この時に、例えば I列に、F列のロットに対するG列の残り個数の生産が、同日の同設備を用いて製造した最後の製造かどうかを示すことはできないでしょうか？

という条件の中の、「同設備を用いて」という条件を見落としておりました。

　ANo.1の関数は、どちらも

F列の「Lot.抜粋」欄に入力されているLot.番号が、A列において最初に現れる行を探し出し、「該当するLot.番号の右隣に入力されている日付」が、その行の処までに何回現れているのかという回数と、その日付がB列全体で何回現れているのかを比較して、2つの回数が等しければ、そのLot.番号が最初に現れる行より下には、その日付は入力されていない事になりますから、そのLot.番号はその日付における最後のロット番号と判定する。

というものですから、例え、同日において、その設備で最後に製造されたロットであっても、同日のもっと後に別の設備で製造が行われていた場合には、その日の最後に製造されたロットではないと判定してしまう事になります。（工場全体で同日最後のロットしか「最後」と判定されない）
　ですから、ANo.1の関数は、質問者様が御求めになられているものでは御座いませんでした。

　正しい関数の一例としましては、次の様なものとなります。

=IF(COUNTIF($A:$A,INDEX($F:$F,ROW())),"最後"&IF(COUNTIF(INDEX($C:$C,MATCH(INDEX($F:$F,ROW()),$A:$A,0)):INDEX($C:$C,MATCH(VLOOKUP(INDEX($F:$F,ROW()),$A:$C,2,FALSE),$B:$B)),VLOOKUP(INDEX($F:$F,ROW()),$A:$C,3,FALSE))>1,"ではない",""),"")

　これは、F列の「Lot.抜粋」欄に入力されているLot.番号が、A列において最初に現れる行と、「該当するLot.番号の右隣に入力されている日付」が最後に現れる行が、それぞれ何行目と何行目であるかを検出し、それら2つの行の範囲の中に、C列において、「該当するLot.番号を製造した設備の略号」が何回現れるのかをカウントし、その回数が1回のみの場合には「最後」と判定し、1回を上回っている場合には、「最後ではない」と判定する関数です。

　但し、日付のデータが、古い日付は必ず上に、新しい日付は必ず下になっている様に入力されていなければなりません。

　後、念のために確認しておきたいのですが、御使いになられておられるExcelのバージョンはExcel2007以降のものでしょうか、それとも、Excel2007よりも前のバージョンなのでしょうか？
　もし、Excel2007以降である場合には、もう少し簡単な関数にする事が可能ですので、どちらであるのかを御教え頂く訳には参りませんでしょうか？

　それから、同一のLot.番号が、表中に複数回現れる様な事は無いと考えて宜しいのでしょうか？
　もしも、同一のLot.番号が重複して現れる様な事がありますと、判定する事が出来なくなる恐れがあります。

　又、B列の日付データは、見かけ通り日付のみのデータであって、データの中身（シリアル値）には時刻データ（シリアル値における小数点以下の端数が存在するデータ）は付随してはいないと考えても宜しいのでしょうか？
　もし、日付データを数値（=1899年12月31日からの日数）に変換（セルの書式設定を数値にすると現れる数字）した際に、小数点以下の端数がある場合には、上記の関数は正しい結果を求める事は出来ません。

　又、もしも、元データの入力が、手入力によって行われていて、表示されている内容は、一見同じに見えていても、全角文字と半角文字が混じり合っていたり、文字列の前後に空欄がくっついていたりといった事もあり得るのでしょうか？
　もしも、その様な事があった場合には、上記の関数は正しい結果を求める事は出来ません。
　それはおそらく、他の関数等における方法でも同じだと思います。

回答No.4

K Kazz（@JazzCorp）

ちょっと、横着コイテマシタ、、、反省、、、
Ｉ４を訂正、
（正）
=IF($F4="","",IF($K4="","不明！",IF(COUNTIF($J4:$J$999,$K4)=1,"最後","最後でない")))
（誤）
=IF(F4="","",IF(K4="","不明！",IF(COUNTIF(J:J,K4)=1,"最後","最後でない")))

回答No.3

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

>ある部品をある設備を用いて作っているとします。ある部品は、12個を1セットとして梱包するとします同日、同設備、同じ元部品を使用して製造した場合に、同Lot.であるとします。
の説明がよくわかりません。表の中の何を説明しているのでしょうか？

画像もぼやけていてよくわかりませんが、説明と食い違っていませんか？
Lot.010が最終となるのでは？

製造日順に並んでいる かつ 1日は1000件まで かつ VLOOKUP関数は使えるとして

E4セル 当日の最後のセルがいくつ下まで続くかを考える
=MATCH(B4+0.5,B4:B1003)-1

F4セル その行が当日なら1、ちがうならその範囲(当日の最後のセル)までの その行と同じ設備の数を数える
=IF(E4=0,1,COUNTIF(C4:INDEX(C4:C1003,E4+1),C4))

G4セル
=IF(F4=1,"最後","最後ではない")

E4:G4セル範囲を下へオートフィル

後はVLOOKUP関数でどうぞ

回答No.2

K Kazz（@JazzCorp）

２列も使っちゃって、イインダロウカ？？
Ｉ４：
=IF(F4="","",IF(K4="","不明！",IF(COUNTIF(J:J,K4)=1,"最後","最後でない")))
Ｊ４：
=IF(COUNTA(B4:C4)<>2,"",B4&C4)
Ｋ４：
=IF(F4="","",VLOOKUP(F4,A:J,10,FALSE))
以下、適当にコピー

補足

２列でも、３列でも、使ってくださって構わないのですが、お教え頂いた数式を入力してみましたが、残念なから、目的としていることができておりません。もしよろしければ、再度、回答頂きたくよろしくお願いします。

回答No.1

kagakusuki

A列、B列、C列、F列、G列は手入力による元データと考えて、御質問はI列を自動表示させる事だけで宜しいのですね？
　それでしたら、まず、I4セルに次の関数を入力してから、I4セルをコピーして、I5以下に貼り付けて下さい。

=IF(COUNTIF($A:$A,INDEX($F:$F,ROW())),"最後"&IF(COUNTIF($B$3:INDEX($B:$B,MATCH(INDEX($F:$F,ROW()),$A:$A,0)),INDEX($B:$B,MATCH(INDEX($F:$F,ROW()),$A:$A,0)))=COUNTIF($B:$B,INDEX($B:$B,MATCH(INDEX($F:$F,ROW()),$A:$A,0))),"","ではない"),"")

或いは

=IF(ISNUMBER(1/(MATCH(INDEX($F:$F,ROW()),$A:$A,0)<=MATCH(9E+99,$B:$B))),"最後"&IF(COUNTIF(INDEX($B:$B,MATCH(INDEX($F:$F,ROW()),$A:$A,0)):INDEX($B:$B,MATCH(9E+99,$B:$B)),INDEX($B:$B,MATCH(INDEX($F:$F,ROW()),$A:$A,0)))>1,"ではない",""),"")

　因みに、D4以下には次の関数を入力されると良いと思います。

=IF(AND(ISNUMBER(1/DAY(INDEX($B:$B,ROW()))),INDEX($C:$C,ROW())<>""),COUNTIFS($B$3:INDEX($B:$B,ROW()),INDEX($B:$B,ROW()),$C$3:INDEX($C:$C,ROW()),INDEX($C:$C,ROW())),"")

補足

ご回答ありがとうございます。概ねあっているようなのですが、“最後ではない”と出力されるところを一つ一つみてみると、実際には、“最後”なのに“最後ではない”と出力されている箇所があります。

間違った出力がされてるところに、特に規則性をみつけられないので、なんとも連絡のしようがないのですが、どちらの関数式も同じ場所で、間違った出力がされています。

“最後ではない”の数は少なかったので、人力で確認してもいいのですが、“最後”の中に、実際は、“最後ではない”があると、困る状況です。もしまだ見ていただけていましたら、再度、回答頂けると助かります。