エクセルで製品番号から生産日をチェックする関数

質問

Office(Excel)のビギナーです。
どうか皆様の知恵をお貸しください。

仕事で資料をつくる際に、製品の番号（製造番号）から生産日を確認するのですが、製造番号と生産日の表を見ながら、生産日でソートして手作業で入力していました。これではかなり労力がかかると思い、関数で判別式を作りたいと考えています。

例えば製品番号と生産日のリストがこれだとします。
AABB00001－AABB10000　：２０００年製
AABB10000－AABB99999　：２００５年製
ACBB00001－ACBB10000　：２００７年製
ACBB10000－ACBB99999　：２０１０年製

IF関数とLEFT,RIGHTを組み合わせてやってみましたが、種類が多くなるとごちゃごちゃになり、よく分からないエラー（１つのセルで使える関数のLIMITを超えたような）も出てうまくいきませんでした。
（例）=if(left(A1,4)="AABB",if(right(A1,5)<=10000,"2000","2005"),if(.................)

リストを参照して簡単にチェックができるような方法はありませんでしょうか？

説明が分かりにくくてすいません。
宜しくお願いいたします。

回答No.1

keithin · ベストアンサー

こんな具合に作ります。

添付図：
E2に調べたい番号を入れると
F2:
=IF(E2="","",VLOOKUP(E2,A:C,3))

実際のデータを入れてＡ～Ｃ列のデータを作ったら，必ず忘れずにＡ列の昇順でＡ：Ｃ列全体を並べ替えておいて（A→Z並べ替えボタンをぽちっとする）から計算します。

お礼

ありがとうございます。
いろんな方法がありますが、これが一番シンプルにできました。

回答No.4

kagakusuki

回答番号ANo.3です。

　それから、もし、御使用されているExcelのバージョンが、エクセル2007よりも前のバージョンである場合には、COUNTIFS関数やSUMIFS関数が使えませんので、Sheet2のB2セルに入力する関数は、次の様なものになります。

=IF(SUMPRODUCT((Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(10^5,Sheet1!$B:$B))=LEFT(A2,4))*(Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(10^5,Sheet1!$B:$B))<=REPLACE(A2,1,4,)*1)*(Sheet1!$C$1:INDEX(Sheet1!$C:$C,MATCH(10^5,Sheet1!$B:$B))>=REPLACE(A2,1,4,)*1))=0,"該当無し",INDEX(Sheet1!$D:$D,SUMPRODUCT(ROW(Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(1E+99,Sheet1!$B:$B)))*(Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(1E+99,Sheet1!$B:$B))=LEFT(A2,4))*(Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(1E+99,Sheet1!$B:$B))<=REPLACE(A2,1,4,)*1)*(Sheet1!$C$1:INDEX(Sheet1!$C:$C,MATCH(1E+99,Sheet1!$B:$B))>=REPLACE(A2,1,4,)*1))))

お礼

バージョンでの違いまで補足いただき、ありがとうございます。正直、出てくる関数が多すぎて私にはなかなか理解が難しいですが、此れを機に勉強したいと思います。

回答No.3

kagakusuki

まず、適当なシート（例えばSheet1）に、製造番号と生産日の関係を表す、次の様なリストを作成して下さい。

　　　　　　A列　　　　B列　　　　　C列　　　　　D列
1行目　　 記号　　先頭番号　　末尾番号　　生産日
2行目　　AABB　　　00001　　　10000　　　　2000
3行目　　AABB　　　10000　　　99999　　　　2005
4行目　　ACBB　　　00001　　　10000　　　　2007
5行目　　ACBB　　　10000　　　99999　　　　2010

　この時、B列とC列には、以下のような操作を行って、セルの書式設定を[ユーザー定義]の[00000]として下さい。

B列の最上部にある「B」と記されているマスにカーソールを合わせてから、マウスの左ボタンを押しっ放しにし、そのままカーソルを右隣にある「C」と記されているマスまで移動させてからボタンを放す
　　↓
選択範囲を示す黒い太枠の内側にカーソルを合わせてマウスを右クリック
　　↓
現れた選択肢の中から、[セルの書式設定]を選択してクリック
　　↓
現れた[セルの書式設定]ウィンドウの[表示形式]タグをクリック
　　↓
[分類]欄の中にある[ユーザー定義]を選択してクリック
　　↓
[種類]欄に

00000

と入力
　　↓
[セルの書式設定]ウィンドウの[OK]ボタンをクリック

　それから、D列の生産日のデーターは、

2000年製

の様な文字列データーではなく、

2000

の様な数値か、或いは

2000/1/1

の様な日付のデーターとして下さい。
　又、生産日のデーターは、西暦年を表す数字と、年月日まで記された日付のデーターを、混在させたりはせず、必ずどちらかに統一して下さい。

　次に、別のシート（例えばSheet2）の
A1セルに　　製造番号
B1セルに　　生産日
と入力して下さい。
　次に、Sheet1のリスト中の生産日のデーターが日付で入力されている場合には、以下の様な操作を行って、Sheet2のB2セルの書式設定を[日付]として下さい。（尚、生産日のデーターが日付ではなく、年数を表す整数値で入力されている場合には、セルの書式設定は変更せずに[標準]のままとして下さい）

Sheet2のB2セルセルにカーソルを合わせてマウスを右クリック
　　↓
現れた選択肢の中から、[セルの書式設定]を選択してクリック
　　↓
現れた[セルの書式設定]ウィンドウの[表示形式]タグをクリック
　　↓
[分類]欄の中にある[日付]を選択してクリック
　　↓
[ロケール]欄が現れない場合には次の操作に進み、現れる場合には、[ロケール]欄の中にある[日本語]を選択してクリックしてから、[カレンダーの種類]欄の中にある[西暦]を選択してクリック
　　↓
[種類]欄の中にある

2001/3/14

という様な形式で記されている日付をを選択してクリック
　　↓
[セルの書式設定]ウィンドウの[OK]ボタンをクリック

　そして、御使用されているExcelのバージョンが、エクセル2007以降のバージョンである場合には、Sheet2のB2セルに次の様な関数を入力して下さい。

=IF(COUNTIFS(Sheet1!$A:$A,LEFT(A2,4),Sheet1!$B:$B,"<="&REPLACE(A2,1,4,)*1,Sheet1!$C:$C,">="&REPLACE(A2,1,4,)*1)=1,SUMIFS(Sheet1!$D:$D,Sheet1!$A:$A,LEFT(A2,4),Sheet1!$B:$B,"<="&REPLACE(A2,1,4,)*1,Sheet1!$C:$C,">="&REPLACE(A2,1,4,)*1),"")

　後は、Sheet2のA2セルに製造番号を入力すると、Sheet2のB2セルに生産日が表示されます。

回答No.2

kmetu

たとえば
A列　　　　B列　　　　C列
AABB　　00001　　２０００年製
AABB　　09999　　２０００年製
AABB　　10000　　２００５年製
AABB　　99999　　２００５年製
ACBB　　00001　　２００７年製
ACBB　　09999　　２００７年製
ACBB　　10000　　２０１０年製
ACBB　　99999　　２０１０年製

という表を作成して(B列は文字列としてください)

F1に製品番号があるとした場合

=VLOOKUP(RIGHT(F1,5),INDIRECT("B" & MATCH(LEFT(F1,4),A:A,0) & ":C" & MATCH(LEFT(F1,4),A:A,0)+3),2)

で製造年が取り出せます。
+3
はAABBなど同一データの数-1
としてください。例の場合同一は4なので3にしています。

お礼

早速のご回答ありがとうございました。
INDIRECTやMATCHなどあまりなじみがない関数も勉強できました。