エクセルでＭ・Ｌ・ＬＬサイズの各数量をカウントしたいのですが

質問

どなたかお願いします。
エクセルで弁当店のオーダーを集計しています。
商品ごとにＭ・Ｌ・ＬＬサイズの各数量をカウントしたいのです。
単純にサイズごとに入力すれば済む事ですが、過去の経緯から
このような表記になっています。
――――――――――――――――――――――
焼肉ランチ　　５Ｌ　　　　　　（5個全てがLの意味）
唐揚ランチ　　７Ｌ３　　　　　（注文が7個でそのうちLは3個、残りはＭ）
ハンバーグ　　１０Ｌ２ＬＬ１　（注文が10個でそのうちLは2個LLは1個、残りはＭ）
――――――――――――――――――――――
※データは半角で入力してあります
※Ｍは通常サイズなので「Ｍ」の文字入力はしません

上記内容から商品ごとにサイズをカウントする方法は
ないでしょうか。宜しくお願いします。

回答No.10

mitarashi

A No.1です。既にrin01さんの、「1行でプログラム」みたいな、凄いのが出ておりますが、一応作成してみました。
テスト用データと結果は下記の通り
.......................A................B..........C..........D..........E
..1................品名..オーダー..........M..........L........LL
..2焼き肉ランチ................5..........5..........0..........0
..3唐揚げランチ............5L2..........3..........2..........0
..4....ハンバーグ.......5L2LL1..........2..........2...........1
..5....しゃけ弁当..............5L..........0..........5..........0
..6............のり弁............5LL..........0..........0..........5
プログラムは、逐次チェックで芸が無いですが、マクロ実行一発で済みます
Type lunchSize
M As Long
L As Long
LL As Long
End Type

Sub test()
Dim targetRange As Range
Dim i As Long
Dim returnVal As lunchSize
Dim targetCell As Range

Set targetRange = ActiveSheet.Range("a1").CurrentRegion
For i = 2 To targetRange.Rows.Count
Set targetCell = targetRange.Cells(i, 2)
returnVal = splitType(targetCell.Value)
With returnVal
targetCell.Offset(0, 1) = .M
targetCell.Offset(0, 2) = .L
targetCell.Offset(0, 3) = .LL
End With
Next i
End Sub

Private Function splitType(targetString As String) As lunchSize
Dim returnNo As Long

With splitType
returnNo = subMatchNo(targetString, "^(\d+)$")
If returnNo > 0 Then
.M = returnNo
.L = 0
.LL = 0
Exit Function
End If
returnNo = subMatchNo(targetString, "^(\d+)L$")
If returnNo > 0 Then
.M = 0
.L = returnNo
.LL = 0
Exit Function
End If
returnNo = subMatchNo(targetString, "^(\d+)LL$")
If returnNo > 0 Then
.M = 0
.L = 0
.LL = returnNo
Exit Function
End If
.L = subMatchNo(targetString, "L(\d+)")
.LL = subMatchNo(targetString, "LL(\d+)")
.M = subMatchNo(targetString, "^(\d+)\D+.*") - .L - .LL
End With
End Function

Private Function subMatchNo(targetString As String, patternString As String) As Variant
Dim regEX As Object
Dim Matches As Object
Dim match As Object
Dim subMatch As Object
Dim i As Long

Set regEX = CreateObject("VBScript.RegExp")
regEX.MultiLine = False
regEX.Pattern = patternString
regEX.ignorecase = False
regEX.Global = False
Set Matches = regEX.Execute(targetString)
i = 1
If Matches.Count > 0 Then
subMatchNo = Matches(0).submatches.Item(0)
Else
subMatchNo = 0
End If
Set Matches = Nothing
Set regEX = Nothing
End Function

回答No.9

rin01

おはようございま~す。。。♪

>「5L、5LL」の場合も可能であれば教えてください。
にも対応させました。。。
条件がダブってるので、式も長くなってしまいました。。。

私の表で、
(注文数)D2　(Mサイズ)E2の式は前回と同じです。。

(Lサイズ)F2セルは。。

=IF(B2="","",CHOOSE(COUNT(INDEX(0/(MID(B2,ROW($1:$20),1)="L"),))+1,0,IF(MID(B2,FIND("L",B2)+1,1)="",D2,LOOKUP(10^7,RIGHT(B2,ROW($1:$20))*1)),0,LOOKUP(10^7,LEFT(REPLACE(B2,1,FIND("L",B2),),ROW($1:$20))*1)))

(LLサイズ)G2セルは。。

=IF(B2="","",CHOOSE(COUNT(INDEX(0/(MID(B2,ROW($1:$20),1)="L"),))+1,0,0,IF(MID(B2,FIND("L",B2)+2,1)="",D2,LOOKUP(10^7,RIGHT(B2,ROW($1:$20))*1)),LOOKUP(10^7,RIGHT(B2,ROW($1:$20))*1),D2))

でした。。

。。。Ms.Rin～♪♪

回答No.8

rin01

ふたたび～Ms.Rinです。。。

スミマセン！！
オーダーの入力順が違ってましたネ!!。。。
L→LLの順ですね。。

★前の２つの回答は無視してください。。。

　　A　　　　　B　　　　　　　　C　　　　　D　　　　E　　　F　　　　G
1　品名　　　オーダー　　　　　　 　注文数　　M　　　L　　　LL
2焼肉ランチ　　5L4　　　　　　 　　　　5　　 　1　　　4　　　 0
3唐揚ランチ　　7LL3　　　　　　 　　　 7　　 　4　　　0　　 　3
4ハンバーグ　　20L12LL1　　　 　　　20　　　7　　　　12　　　1
5しゃけ弁当　　10　　　　　　　 　　　　10　　　10　　　0　　　0

D2セルへ
=IF(B2="","",LOOKUP(10^7,LEFT(B2,ROW($1:$20))*1))

E2セルへ
=IF(B2="","",D2-SUM(F2:G2))

F2セルへ
=IF(B2="","",IF((LEN($B2)-LEN(SUBSTITUTE($B2,"L",))=2)+(ISNUMBER(B2)),0,LOOKUP(10^7,LEFT(REPLACE(B2,1,FIND("L",B2),),ROW($1:$20))*1)))

G2セルへ
=IF(B2="","",IF(((LEN($B2)-LEN(SUBSTITUTE($B2,"L",)))=1)+(ISNUMBER(B2)),0,LOOKUP(10^7,RIGHT(B2,ROW($1:$20))*1)))

それぞれ下へコピー。。。

最終回答でした。。。

。。。Ms.Rin～♪♪

回答No.7

rin01

Ms.Rinです。。。

スミマセン！！
表が違ってました～。。。

　　A　　　　　B　　　　　　　　C　　　　　D　　　　E　　　F　　　　G
1　品名　　　オーダー　　　　　　 　注文数　　M　　　L　　　LL
2焼肉ランチ　　5L4　　　　　　 　　　　5　　 　1　　　4　　　 0
3唐揚ランチ　　7LL3　　　　　　 　　　 7　　 　4　　　0　　 　3
4ハンバーグ　　20LL12L1　　　 　　　20　　　7　　　　1　　　12
5しゃけ弁当　　10　　　　　　　 　　　　10　　　10　　　0　　　0

こちらが正しい表です。。。

補足

ありがとうございます。
なるほど、関数のみで出来るもんですね・・・。

「5L、5LL」の場合も可能であれば教えてください。
注文が5個で全て大盛りＬ（LL）サイズの意味です。
Lの後に数字が無い場合、注文全部がLの意味になる
約束で運営していますので。
5L5、5LL5と入力できない点が問題なのですが・・・。

宜しくお願いします。

回答No.6

rin01

こんばんは～♪

こんな表の場合です。。。

　　A　　　　　B　　　　　　　　C　　　　　D　　　　E　　　F　　　　G
1　品名　　　オーダー　　　　　　 　注文数　　M　　　LL　　　L
2焼肉ランチ　　5L4　　　　　　 　　　　5　　 　1　　　4　　　 0
3唐揚ランチ　　7LL3　　　　　　 　　　 7　　 　4　　　0　　 　3
4ハンバーグ　　20LL12L1　　　 　　　20　　　7　　　12　　　1
5しゃけ弁当　　10　　　　　　　 　　　　10　　　10　　　0　　　0

D2セルへ
=IF(B2="","",LOOKUP(10^7,LEFT(B2,ROW($1:$20))*1))

E2セルへ
=IF(B2="","",D2-SUM(F2:G2))

F2セルへ
=IF(B2="","",IF(((LEN($B2)-LEN(SUBSTITUTE($B2,"L",)))=2)+(ISNUMBER(B2)),0,LOOKUP(10^7,RIGHT(B2,ROW($1:$20))*1)))

G2セルへ
=IF(B2="","",IF((LEN($B2)-LEN(SUBSTITUTE($B2,"L",))=1)+(ISNUMBER(B2)),0,LOOKUP(10^7,LEFT(REPLACE(B2,1,FIND("LL",B2)+1,),ROW($1:$20))*1)))

それぞれ下へコピー。。。

ご参考にどうぞ。。。
。。。Ms.Rin～♪♪

回答No.5

mitarashi

A No.1です。もう寝ようと、入浴していて気がつきましたが、5Lの解釈がまだ違ってますね。条件分岐が複雑になるので、ユーザー定義関数では無理で、VBAでの処理を組まなければダメかもしれません。乗りかかった船で、もう少しやってみますので、全てのバリエーションを明確にしていただけますか。
ただの、５なんてのもありそうですね。

補足

遅い時間までありがとうございます。
ご推察の通り5Lの場合だけ「L1個・M4個」となってしまいます。
ただの「5」の場合はM5個となりOKでした。
――――――――――――――――――――――――――――
お客様の言い方が「M○○個、L○○個ちょうだい」
となる場合、全スタッフがスムーズに記録できるのですが
大概の場合は「弁当5個ちょうだい・・・あっ、2個大盛りね」
などとなってしまいます。
そこで長年の手順として
（1）まずは全ての数量を記録する、これを普通盛りサイズMと見なす。
（2）次に例外の大盛りL、特盛りLLを個数とともに右に書き加える
となっているのです。
――――――――――――――――――――――――――――
全てのバリエーションを記します。（セル入力値）

（5） ・・・・・M5個
（5L2） ・・・・M3個 L2個
（5L2LL1）・・・M2個 L2個 LL1個
（5L）・・・・・L5個
（5LL） ・・・・LL5個

※例えば5Lを5L5とすれば、注文が5個でそのうちLが5個の意味ですから
現状で問題なく値が得られます。しかし高齢のスタッフも多く手順変更
は最後にしたいと考えています。

よろしくお願いします。

回答No.4

mitarashi

A No.1です。
早とちりしておりました。下記でいかがでしょうか。
Function subMatchNo(targetString As String, patternString As String) As Variant
Dim regEX As Object
Dim Matches As Object
Dim match As Object

Set regEX = CreateObject("VBScript.RegExp")
regEX.MultiLine = False
regEX.Pattern = patternString
regEX.ignorecase = True
regEX.Global = False
On Error GoTo errorHandle
Set Matches = regEX.Execute(targetString)
If Matches(0).subMatches.Count > 0 Then
subMatchNo = Val(Matches(0).subMatches.Item(0))
If subMatchNo = 0 Then subMatchNo = 1
Else
subMatchNo = 0
End If
Set Matches = Nothing
Set regEX = Nothing
Exit Function
errorHandle:
subMatchNo = 0
End Function

......................A................B.........C.........D.........E
..1..................................................L.......LL.........M
..2...焼肉ランチ..............5L..........1.........0.........4
..3...唐揚ランチ............7L3.........3.........0.........4
..4...ハンバーグ......10L2LL1.........2..........1.........7
..5...............合計...........................6..........1........15

C2：=subMatchNo($B2,"L(\d*)")
D2：=subMatchno($B2,"LL(\d*)")
E2：=subMatchno($B2,"^(\d*)")-SUM(C2:D2)

回答No.3

Masa2072

ANo1さんと同様、ワークシート関数で行う方法はあるかもしれませんが、少なくとも私には難しい（やるとすると式が長くなりすぎる。）

私のサンプルは正規表現を使わない方法です。
ANo1さんの方がすっきりとしたサンプルですが、せっかく考えたので・・

Function GetSizeCount(ByVal OrderStr As String, ByVal OrderSize As String) As Integer
Dim L1, L2, L3, L, LL, M As Integer
Dim OStr, OSize As String

'念のためにセル入力値を大文字に変換
OStr = StrConv(OrderStr, vbUpperCase)
OSize = StrConv(OrderSize, vbUpperCase)

If OStr = "" Then
'注文情報が無い
GetSizeCount = 0
Exit Function
End If

If IsNumeric(OStr) Then
'数字のみの場合はMだけ
If OrderSize = "M" Or (OSize <> "L" And OSize <> "LL") Then
'要求サイズがM（またはL,LL以外）の場合は数量を返す
GetSizeCount = CInt(OStr)
Exit Function
Else
'要求サイズがL,LLの場合は0を返す
GetSizeCount = 0
End If
End If

L1 = InStr(OStr, "L") '１個目のLの位置
If L1 = 1 Then
'先頭がLで始まっている場合の対応
OStr = "1" & OStr
L1 = 2
End If
L2 = InStr(L1 + 1, OStr, "L") '２個目のLの位置
L3 = InStr(L2 + 1, OStr, "L") '３個目のLの位置

'Mサイズの数量は常に末尾なので最後のLの位置以降を数量とする
M = CInt(Mid(OStr, Application.WorksheetFunction.Max(L1, L2, L3) + 1, Len(OStr)))

If OSize = "M" Then
'要求のサイズがMならL、LLの個数取得はしない
GetSizeCount = M
Exit Function
End If

If L1 = 0 Then
'最初のLが無ければL,LLともに0とみなす
LL = 0
L = 0
ElseIf L1 = L2 - 1 Then
'最初の数字はLL（１番目のLと２番目Lが連続している、つまりLL）
LL = CInt(Left(OStr, L1 - 1))
If L3 = 0 Then
'３個目のLが無ければLは0とみなす
L = 0
Else
L = CInt(Mid(OStr, L2 + 1, L3 - L2 - 1))
End If
Else
'最初の数字はL（２番目のLが１番目と離れている）
L = CInt(Left(OStr, L1 - 1))
If L2 = 0 Then
'２個目のLが無ければLLは0とみなす
LL = 0
Else
LL = CInt(Mid(OStr, L1 + 1, L2 - L1 - 1))
End If
End If

'要求サイズによって該当する数量を返す
If OSize = "L" Then
GetSizeCount = L
ElseIf OSize = "LL" Then
GetSizeCount = LL
Else
GetSizeCount = M
End If
End Function

補足

ありがとうございます。問題なく動きました。
やっとここまで来たぞという感じです。
ANo.1さんに補足説明しましたが、質問の仕方が
悪かったようです。
再度、教えていただければ幸いです。

よろしくお願いします。

回答No.2

mitarashi

A No.1です。最後の式が違っていました。「お、回答が二つも」と期待させて申し訳ありません。推敲不足でした。
E2：=subMatchno($B2,"(\d*)$")

補足

ありがとうございます。問題なく動きました。
やっとここまで来たぞという感じです。
ANo.1さんに補足説明しましたが、質問の仕方が
悪かったようです。
再度、教えていただければ幸いです。

よろしくお願いします。

回答No.1

mitarashi

下記正規表現を利用した、ユーザー定義関数を用いる案です
データが沢山あると、重くて実用的ではないかもしれません。その場合は、VBAで処理する様に組んでやる必要があるかも。また、Mは一番最後にあるとしています。もっと軽い方法を使った回答が出てくると思いますが、ご参考まで。
Function subMatchNo(targetString As String, patternString As String) As Variant
Dim regEX As Object
Dim Matches As Object
Dim match As Object

Set regEX = CreateObject("VBScript.RegExp")
regEX.MultiLine = False
regEX.Pattern = patternString
regEX.ignorecase = False
regEX.Global = False
On Error GoTo errorHandle
Set Matches = regEX.Execute(targetString)
If Matches(0).subMatches.Count > 0 Then
subMatchNo = Val(Matches(0).subMatches.Item(0))
Else
subMatchNo = 0
End If
Set Matches = Nothing
Set regEX = Nothing
Exit Function
errorHandle:
subMatchNo = 0
End Function

実行例です
......................A................B....C....D....E
..1.............................................L..LL....M
..2...焼肉ランチ..............5L....5....0....0
..3...唐揚ランチ............7L3....7....0....3
..4...ハンバーグ......10L2LL1...10....2.....1
..5...............合計....................22....2....4

ユーザー定義関数の使用方法です（以下、下の行にコピー）
C2：=subMatchNo($B2,"(\d*)L")
D2：=subMatchNo($B2,"(\d*)LL")
E2：=subMatchNo($B2,"(\d)$")

参考URL：

http://officetanaka.net/excel/vba/tips/tips38.htm

補足

早速ありがとうございます。
問題なく動きましたが、私の質問が足りなかったようです。
申し訳ありません。

例えばハンバーグの「１０Ｌ２ＬＬ１」は
Ｍ＝3個　Ｌ＝2個　ＬＬ＝1個
の意味になります。
―――――――――――――――――――――――――――
ハンバーグ10個お願いしま～す！！　はいよ～
そのうちＬが2個で、ＬＬが1個で～す！
―――――――――――――――――――――――――――
こんな感じです。
宜しくお願いします。