Excelの操作方法を教えてください！頭文字2～8文字で特定の値に返す方法

Excelの操作方法が分からず困っています。

・1商品に個別のシリアルコードが採番されています（13桁）
・シリアルコードの頭文字によって、商品コードを判定することができます。
・しかし、判定される頭文字の文字数が、商品コードによって、2文字～8文字とばらばらです。
・このようなとき、どんな方法でシリアルNOと商品コードを紐づければいいのでしょうか？

シリアルNOと商品コード・シリアルNO頭文字の対照表を添付します。

どなたか、どうか教えてください！よろしくお願いします。

No.8 回答者： Belltreeriver 回答日時： 2022/07/21 09:56

D2でも問題ありません。

数式は私が回答したとおりなので、不具合はありません。
「0」が返るということはINDEX関数でS列（商品コード）を配列参照していますので、商品コードがS列にないということかなと思います。
私の検証結果では添付のとおり、D列に商品コードが表示されます。

この回答へのお礼

本当ですね！！
もう一度確認してやり直してみます！
何度もありがとうございます！！

お礼日時：2022/07/21 10:04

No.7 回答者： Belltreeriver 回答日時： 2022/07/21 09:25

№6です。

入力したセル位置と数式内容を、そのまま教えてください。

この回答へのお礼

D2に、
=IF(MAX(INDEX((LEFT(A2,LEN($T$2:$T$27))=$T$2:$T$27)*ROW($2:$27),0))=0,"シリアル№頭文字表該当なし",INDEX(S:S,MAX(INDEX((LEFT(A2,LEN($T$2:$T$27))=$T$2:$T$27)*ROW($2:$27),0))))
と入力しました。
B2に入力しなければならなかったのでしょうか？？！！

お礼日時：2022/07/21 09:29

No.6 回答者： Belltreeriver 回答日時： 2022/07/20 21:42

画像のシートレイアウトで

B2=IF(MAX(INDEX((LEFT(A2,LEN($T$2:$T$27))=$T$2:$T$27)*ROW($2:$27),0))=0,"シリアル№頭文字表該当なし",INDEX(S:S,MAX(INDEX((LEFT(A2,LEN($T$2:$T$27))=$T$2:$T$27)*ROW($2:$27),0))))

対応表に該当するものが無い場合は"シリアル№頭文字表該当なし"と表示します。適宜変更してください。

この回答へのお礼

アドバイスありがとうございます！！
ご指示いただいた式を入力してみたのですが、すべて結果が「０」になってしまって…
どうしてでしょうか…すみません、助けていただけるとありがたいです。

お礼日時：2022/07/21 09:12

No.5 回答者： tatsumaru77 回答日時： 2022/07/15 15:03

No4です。

以下のマクロを標準モジュールに登録してください。
シート名、セルのレイアウトの前提はNo4の通りです。

Option Explicit

Public Sub 商品コード設定()
Dim dicT As Object
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim maxrow1 As Long
Dim maxrow2 As Long
Dim wrow As Long
Dim minlen As Long
Dim maxlen As Long
Dim wkey As String
Dim key As String
Dim pos As Long
Set dicT = CreateObject("Scripting.Dictionary")
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
maxrow1 = sh1.Cells(Rows.Count, "A").End(xlUp).Row
maxrow2 = sh2.Cells(Rows.Count, "A").End(xlUp).Row
minlen = 999
maxlen = 0
For wrow = 2 To maxrow2
key = sh2.Cells(wrow, "B").Value
If Len(key) > maxlen Then maxlen = Len(key)
If Len(key) < minlen Then minlen = Len(key)
dicT(key) = sh2.Cells(wrow, "A").Value
Next
sh1.Range("B2:B" & maxrow1).ClearContents
For wrow = 2 To maxrow1
wkey = sh1.Cells(wrow, "A").Value
For pos = maxlen To minlen Step -1
key = Left(wkey, pos)
If dicT.exists(key) = True Then
sh1.Cells(wrow, "B").Value = dicT(key)
Exit For
End If
Next
Next
MsgBox ("完了")
End Sub

この回答へのお礼

ありがとうございます！早速試してみます！！
本当に感謝の気持ちでいっぱいです。

お礼日時：2022/07/15 17:56

No.4 回答者： tatsumaru77 回答日時： 2022/07/15 11:06

No3です。

補足ありがとうございました。
１．マクロでよいなら、提供可能です。
マクロでよいならその旨、補足ください。
マクロが不可の場合は、項番２以降の回答は不要です。

２．
＞A列のシリアルNoは、およそ何行ありますか？
→13桁です。
の回答ですが、データ件数についての質問です。
データ件数はおよそ何件でしょうか。
（あまり大量にあると、処理時間を考慮する必要があるための質問です）

３．シートのレイアウトですが、
Sheet1にA列（シリアルNO）、D列（商品コード）
Sheet2にA列（商品コード）、B列（シリアルNO頭文字）
でよろしいでしょうか。

この回答へのお礼

ありがとうございます！

１．マクロ、OKです！ありがたいです！

２．すみません、読み間違えました・・・大変失礼いたしました。
出荷の都度シートを作成する運用なのですが、少ないと10件程度、多いときは、300件ほどあるときもあります。

３．Sheet1にA列（シリアルNO）、B列（商品コード）　が希望です。
Sheet2にA列（商品コード）、B列（シリアルNO頭文字）　→こちらOKです！

お礼日時：2022/07/15 14:56

No.3 回答者： tatsumaru77 回答日時： 2022/07/15 09:21

補足要求です。

１．シリアルNO頭文字がVC316の場合、商品コードは
VC300SE黒、VC300SE白のどちらになるのでしょうか？
２．A列のシリアルNoは、およそ何行ありますか？
３．S列の商品コードは、およそ何行ありますか？
４．S列の商品コードとT列のシリアルNO頭文字は、
実際にS列とT列にあるのですか、それとも説明上、S列とT列に記述しているだけで、実際は他のシートのA列とB列とかにあるのでしょうか？

この回答へのお礼

ご質問ありがとうございます！
１．シリアルNO頭文字がVC316の場合、商品コードは
VC300SE黒、VC300SE白のどちらになるのでしょうか？

→すみません、こちら区別なく、VC300SE　です。失礼いたしました。

２．A列のシリアルNoは、およそ何行ありますか？

→13桁です。

３．S列の商品コードは、およそ何行ありますか？

→26行です。

４．S列の商品コードとT列のシリアルNO頭文字は、
実際にS列とT列にあるのですか、それとも説明上、S列とT列に記述しているだけで、実際は他のシートのA列とB列とかにあるのでしょうか？

→できれば別シートで管理したいと思っていますが、同一のシートでも運用上は問題ないです。

ご検討よろしくお願いいたします！
ありがとうございます！

お礼日時：2022/07/15 09:58

No.2 回答者： Moryouyou 回答日時： 2022/07/14 12:22

これはデータの正確度が影響します。

該当がない場合に近似値を割り当てよいのか？
ってところが課題です。

とりあえずは、
シリアルナンバー頭文字 商品コード
対照表を昇順で作成すれば、
VLOOKUPで検索することで、
文字列で一番近いものがヒットします。

しかし、既に一致しないデータが見受けられます。
(“VCT9”はないです。)
この場合、VCT8のT8がヒットしてしまいます。
そのあたりをどうするかですね。

No.1 回答者： fujillin 回答日時： 2022/07/14 12:21

こんにちは

ご提示の「シリアルNo頭文字」には重複があるので、それを区別することはできないと思われます。
以下は、この区別は無視しても良いという条件付きになりますが・・

考え方として、
T列の文字数の長いものから順に、A列の文字列の先頭から比較して、最初に一致するものを該当コードと判断すれば良いのではないでしょうか？


関数で行う場合は、S:T列をT列の文字数の長い順に並べ替えておいた方がやりやすいです。
S:T列が並べ替え不可の場合は、S：T列を参照して関数で並べ替えたものを別列に作成しておく方が簡単だと思います。

どうしてもそのままで行いたい場合は、ご提示のシートでD2セルに
=IFERROR(INDEX(S:S,MOD(AGGREGATE(14,6,(LEN(T$2:T$30)*1000+ROW(T$2:T$30))/(T$2:T$30<>"")/(LEFT(A2,LEN(T$2:T$30))=T$2:T$30),1),1000)),"")
を入力し、下方にフィルコピーとか。


VBAで行う場合でも考え方は同様ですが、メモリ内で処理できるので、必ずしもシート上で並べ替える必要はなくなります。