配車実績の集計で２日以上悩んでいます。

質問

配車実績の集計で２日以上悩んでいます。

Ａ列からＯ列まで、「番号・出荷元・入荷先・重量・数量」などの配車情報が入っています。
また、「(1)Ｖ列～X列」「(2)Ｙ列～ＡＢ列」には「発場所・着場所・距離」の情報があります。
(1)直送と(2)混載で２つに分かれています。

この表の「Ｂ列 番号・Ｆ列 出荷元・Ｈ列 入荷先」の情報を元に、直送と混載に分けて
車両ごとの走行距離を集計したいと思っています。

４行目までは、「番号・出荷元」などの項目が並んでいて、５行目はオートフィルタの▼、
６行目からデータが始まっています。

「セルＱ６」に番号区分として「=COUNTIF($B$6:$B6,B6)」と入力
「セルＲ６」に出荷元区分として「=SUMPRODUCT(($B$6:$B6=B6)*($F$6:$F6=F6))」と入力
「セルＳ６」に料金仮入力として「{=SUM(IF(($V$6:$V$150=F6)*($W$6:$W$150=H6),$X$6:$X$150))}」を
配列関数で入力
「セルＴ６」には混載判断として「=IF(Q6=0,"",IF(Q6=R6,"","混載"))」
「セルＵ６」には「=IF( AND(Q6=1, R6=1), S6, "")」と入力

そして「セルＱ６からセルＵ６」をコピーして6,000行目まで貼り付け。
Ｕ列に表示された走行距離を見ています。

しかし、これだと、「Ｙ列～ＡＢ列」のマスタを利用できていません。
Ｔ列に表示された「混載」という文字を参考に上の方を見て、Ｂ列の値が同じものを探して
手入力で混載の場合の距離を入力しています。

混載の場合に「(1)Ｖ列～X列」ではなく「(2)Ｙ列～ＡＢ列」のマスタを使用するところが上手くいきません。
Ｂ列（とＨ列）が1つでＦ列が複数ある場合には混載で、Ｂ列・Ｆ列・Ｈ列がそれぞれ１つの場合は直送です。

何とかうまく表示させる方法はないでしょうか？
毎月手作業で確認しているので、非常に時間がかかりますし、ミスが心配です。

回答No.6

MackyNo1 · ベストアンサー

>1.「 ROW($A$1:$A$10) 」ですが、書式をコピーしても対象となるセルは変わらず、この部分の値はいつでも「1」にならないのか。

この部分は１つの値ではなく、１から１０の配列（すなわち{1;2;3;4;5;6;7;8;9;10}の配列定数）を返しています。

>　 Ｂ列の最大の行数が50の場合は、10を50にしたら良いのでしょうか？

B列の行数には関係なく、そのセルから下のセル10個分という意味ですので、B列の数字で連続して同じ数字が入力される最大数を指定します（同じ数字が20個連続するなら10を20に変更）。

>　 Ｂ列なので「ROW(B1:B50)」といった感じでしょうか。

この部分は1～10の配列を取得すれば良いのでどの列で指定しても同じです。
すなわち、「ROW($A$1:$A$10)」の代わりに「ROW($1:$10)」や「{1;2;3;4;5;6;7;8;9;10}」のような配列定数の指定でもOKです。

>　2.「 OFFSET(F6,0,0,10,1)&"",0 」の「＆""」はなぜ必要になるのでしょうか。
>　 MATCH 関数の検査値と検査範囲がいつでも同じ場合は１にならないのでしょうか？

MATCH関数は基本は検索値は1つのセル（または値）ですが、検索値を同じ範囲を対象範囲とした配列数式のMATCH関数は、1つ目のセルから順にその範囲内で検索値が最初に出てくる数字を求めています。
したがって、同じデータが２つ以上ある場合、2つ目以降の検索値は範囲内の1つ目のデータでヒットしますので、上から数えた数字は、その順番と一致しないことになります。
このとき、一致するデータだけを表示するように、表示したくない数値はSMALL関数で該当しないように十分に大きい数字となるようにした数式です。

配列数式のMATCH関数についてはA1:A10セルに重複のある適当な文字列を入力し、以下のような数式を入力して、数式バーでその数式部分をドラッグして、実際にどのようなデータを返しているか調べてみてください。

=MATCH(A1:A10,A1:A10,0)

この数字が１から１０に対応しているセルは、初めてのデータであり以下のような数式で「TRUE」を返します。

ちなみに、「&""」を付加しているのはデータ範囲に空白セルがある場合（上記の数式ならA10まで入力されていない場合）、#N/Aエラーを返すのを防ぐために付けています。

=MATCH(A1:A10,A1:A10,0)=ROW(A1:A10)

>　3.「50,000」が表示される場合はエラーなのかどうか。

該当しないデータなどで戻り値が「50,000」となる場合は、数式で表示しているセルよりも50000行下のセル（一般的には空白セル）を参照していますので、エクセル2003以前のバージョンで元データが1万５千行を超えるようなことがなければZ列やAB列の空白セルに対応することになります。

お礼

丁寧に教えていただき、ありがとうございました。

数式を改良して、何とかやりたいことが出来るようになりました。
これで、毎月約２日分の時間を減らすことが出来ると思います。

感謝しています。

回答No.5

jcctaira

実行ボタン等でマクロを動かして下さい。
注）・入荷先は番号の先頭を使用しています。
　　　複数の入荷先には対応していません。
　　・同一番号は続いて指定されているとしています。　　

Sub 合計距離取得()
　Dim 直送マッチ As Range
　Dim 混載マッチ As Range
　最終行 = Cells(Rows.Count, "B").End(xlUp).Row
　Range(Cells(6, "U"), Cells(最終行, "U")).ClearContents
　直送最終行 = Cells(Rows.Count, "V").End(xlUp).Row
　混載最終行 = Cells(Rows.Count, "Y").End(xlUp).Row
　行 = 6
　Do While 行 <= 最終行
　　　番号 = Cells(行, "B")
　　　番号先頭行 = 行
　　　Set 直送マッチ = マッチ範囲取得(Cells(行, "H"), Range(Cells(6, "W"), Cells(直送最終行, "W")))
　　　Set 混載マッチ = マッチ範囲取得(Cells(行, "H"), Range(Cells(6, "AB"), Cells(直送最終行, "AB")))
　　　Do While 番号 = Cells(行, "B")
　　　　　Set 直送マッチ = AND集合(直送マッチ, マッチ範囲取得(Cells(行, "F"), Range(Cells(6, "V"), Cells(直送最終行, "V"))))
　　　　　Set 混載マッチ = AND集合(混載マッチ, マッチ範囲取得(Cells(行, "F"), Range(Cells(6, "Y"), Cells(直送最終行, "AA"))))
　　　　　行 = 行 + 1
　　　Loop
　　　Select Case True
　　　　　Case Not (直送マッチ Is Nothing)
　　　　　　　Cells(番号先頭行, "U") = Cells(直送マッチ.Row, "X")
　　　　　Case 混載マッチ Is Nothing
　　　　　　　Cells(番号先頭行, "U") = "■"
　　　　　Case 混載マッチ.Rows.Count >= 2
　　　　　　　Cells(番号先頭行, "U") = "■"
　　　　　Case Else
　　　　　　　Cells(番号先頭行, "U") = Cells(混載マッチ.Row, "AC")
　　　End Select
　Loop
End Sub

Function マッチ範囲取得(検索文字 As String, 検索範囲 As Range) As Range
　Dim 範囲 As Range
　For Each 範囲 In 検索範囲
　　　If 範囲 = 検索文字 Then Set マッチ範囲取得 = OR集合(マッチ範囲取得, 範囲)
　Next
　If Not (マッチ範囲取得 Is Nothing) Then Set マッチ範囲取得 = マッチ範囲取得.EntireRow
End Function

Function OR集合(範囲1 As Range, 範囲2 As Range) As Range
　Select Case True
　　　Case 範囲1 Is Nothing: Set OR集合 = 範囲2
　　　Case 範囲2 Is Nothing: Set OR集合 = 範囲1
　　　Case Else: Set OR集合 = Union(範囲1, 範囲2)
　End Select
End Function

Function AND集合(範囲1 As Range, 範囲2 As Range) As Range
　Select Case True
　　　Case 範囲1 Is Nothing
　　　Case 範囲2 Is Nothing
　　　Case Else: Set AND集合 = Intersect(範囲1, 範囲2)
　End Select
End Function

お礼

考えていただいてありがとうございます！

しかし、実績を出したいところに■が出てきてしまいました。

回答No.4

MackyNo1

数式を分解して説明すると、

(OFFSET(F6,SMALL(IF((OFFSET(B6,0,0,10,1)=B6)*(MATCH(OFFSET(F6,0,0,10,1)&"",OFFSET(F6,0,0,10,1)&"",0)=ROW($A$1:$A$10)),ROW($A$1:$A$10),50000)-1,1),0)=$Y$6:$Y$60)
の部分は

(OFFSET(B6,0,0,10,1)=B6)
B6セル以下10個分のセルの値がB6と等しく、

(MATCH(OFFSET(F6,0,0,10,1)&"",OFFSET(F6,0,0,10,1)&"",0)=ROW($A$1:$A$10)
かつF6セルから10個分のセルの値（配列）を、同じ範囲で検索した時、１から10（ROW関数の返り値）に等しい（初めて出てくる値と同じ）なら

１から10（ROW関数の返り値）にそれ以外の場合は50000という大きい数字の配列を返す数式になっています。

その外側の　SMALL(IF((・・・・)),1)　の部分は、上記で得られた配列で最も小さい数字（すなわちF6セル以下で初めてF6セル以下の10個のセルが初めて出現する位置）が求められます。

OFFSET(F6,SMALL(IF((・・・・))-1,1,0)
F6セルから計算して上記の数値マイナス１した場所（Y列のデータに相当）のセルの値を求めています。

（上記の数式)=$Y$6:$Y$60)
1つ目のデータがY列のデータと等しい配列を求めています。

最後にSUMPRODUCT関数で、この条件を3つおよびAB列のデータの４つの配列を掛け算して、YからABセルですべての組み合わせが一致するデータの距離を探しています。

補足

回答を確認しました。
何度も何度もありがとうございます。
もう少し相手していただけると助かります。

回答でいくつか質問があります。

1.「 ROW($A$1:$A$10) 」ですが、書式をコピーしても対象となるセルは変わらず、この部分の値はいつでも「1」にならないのか。
　 Ｂ列の最大の行数が50の場合は、10を50にしたら良いのでしょうか？
　 Ｂ列なので「ROW(B1:B50)」といった感じでしょうか。

2.「 OFFSET(F6,0,0,10,1)&"",0 」の「＆""」はなぜ必要になるのでしょうか。
　 MATCH 関数の検査値と検査範囲がいつでも同じ場合は１にならないのでしょうか？

3.「50,000」が表示される場合はエラーなのかどうか。

お手数かけますが、この辺りを教えていただけると助かります。

回答No.3

MackyNo1

ご質問の趣旨は、混載の場合の条件に合致するAB列の数値を求めたいということですね。

B列の数字が連続10個までなら、たとえばU６セルに以下のような配列数式を入力してCtrl+Shift+Enterで確定して下方向にオートフィルすれば、混載の場合の数値を表示できます。

=IF(B6=B5,"",SUMPRODUCT((OFFSET(F6,SMALL(IF((OFFSET(B6,0,0,10,1)=B6)*(MATCH(OFFSET(F6,0,0,10,1)&"",OFFSET(F6,0,0,10,1)&"",0)=ROW($A$1:$A$10)),ROW($A$1:$A$10),50000)-1,1),0)=$Y$6:$Y$60)*(OFFSET(F6,SMALL(IF((OFFSET(B6,0,0,10,1)=B6)*(MATCH(OFFSET(F6,0,0,10,1)&"",OFFSET(F6,0,0,10,1)&"",0)=ROW($A$1:$A$10)),ROW($A$1:$A$10),50000)-1,2),0)&""=Z$6:$Z$60)*(OFFSET(F6,SMALL(IF((OFFSET(B6,0,0,10,1)=B6)*(MATCH(OFFSET(F6,0,0,10,1)&"",OFFSET(F6,0,0,10,1)&"",0)=ROW($A$1:$A$10)),ROW($A$1:$A$10),50000)-1,3),0)&""=$AA$6:$AA$60)*(H6=$AB$6:$AB$60)*$AC$6:$AC$60))

必要に応じて混載と直送データについてIF関数で適宜分岐してください（もし、混載と直送の条件がかぶらないのであれば、そのまま直送データを足し算すれば良いことになります）。

補足

ありがとうございます！
教えていただいた書式を入力したら求めたいものが表示されました！
あとは直送分と混載分を足したらうまく行きそうです！

ただ、ＲＯＷ関数・ＯＦＦＳＥＴ関数・ＭＡＴＣＨ関数・ＳＭＡＬＬ関数という
普段使用しない関数を調べて、解読しようと数時間試みましたが、数式が複雑すぎて理解できません。

・ROW($A$1:$A$10)で、Ａ１からＡ１０までを固定している意味
・ＳＵＭＰＲＯＤＵＣＴ関数の配列がどこで分かれているか
・ＲＯＷ関数の中に出てくる「50,000」という数字は50,000行目までのデータに対応できるということか
などなど、少し詳しく教えていただけないでしょうか？

ちなみにＢ列の数字の連続は最大で50個くらいあります。

回答No.2

jcctaira

すみません、画像が見にくいのと仕様が理解できていません。

よってAA列が「距離」と勘違いしました。

>(1)Ｂ列の番号が１つで、Ｆ列の出荷元が２つ（仮にf1・f2とおく）…　「混載」
>(2)Ｂ列の番号が１つで、Ｆ列の出荷元が１箇所の場合　　　　　　…　「直送」
f1・f2が具体的にどういう事か？混載、直送共に「Ｂ列の番号が１つで」等々理解ができません。

>マクロでも、うまく行かないでしょうか。
　VBAでユーザ関数を作ればできると思います。

画像が見えにくいのと、仕様（計算方法やどういう結果を求めるか）がわかりませんので、
画像ではなく、文字で具体例とどういう結果になれば良いかを説明して頂くと、回答できるかと
思います。

補足

>画像が見えにくいのと、仕様（計算方法やどういう結果を求めるか）がわかりませんので、
>画像ではなく、文字で具体例とどういう結果になれば良いかを説明して頂くと、回答できるかと
>思います。
考えていただいてありがとうございます。
下に説明していきます。

　　　　　　　Ｂ列　　Ｆ列　　Ｈ列
(4行目)　　番号　　出荷元　　入荷先
(5行目)
(6行目)　　１　　　　Ａ　　　　あ
(7行目)　　１　　　　Ａ　　　　あ
(8行目)　　１　　　　Ａ　　　　あ
(9行目)　　２　　　　Ｃ　　　　い
(10行目)　２　　　　Ｃ　　　　い
(11行目)　２　　　　Ｄ　　　　い
(12行目)　３　　　　Ｅ　　　　う
(13行目)　３　　　　Ｅ　　　　う
(14行目)　３　　　　Ｅ　　　　う
(15行目)　３　　　　Ｅ　　　　う
(16行目)　４　　　　Ａ　　　　い
(17行目)　４　　　　Ｂ　　　　い
(18行目)　４　　　　Ｂ　　　　い

上の場合、
番号「１」の便では出荷元「Ａ」から入荷先「あ」まで商品を出荷しています。
番号「３」の便では出荷元「Ｅ」から入荷先「う」まで商品を出荷しています。
・・・・・・番号１つに対して、出荷元が１つなので「直送」

番号「２」の便では出荷元「Ｃ」と出荷元「Ｄ」から入荷先「い」まで商品を出荷しています。
番号「４」の便では出荷元「Ａ」と出荷元「Ｂ」から入荷先「い」まで商品を出荷しています。
・・・・・・番号１つに対して、出荷元が２つなので「混載」
出荷元は最大３ヶ所になります。

●Ｖ列～Ｘ列に「直送距離マスタ」を作成しています。
　　　　　　　Ｖ列　　　Ｗ列　　　Ｘ列
(4行目)　　直送マスタ
(5行目)　　発場所　着場所　距離
(6行目)　　Ａ　　　　あ　　　　10
(7行目)　　Ａ　　　　い　　　　15
(8行目)　　Ａ　　　　う　　　　20
(9行目)　　Ｂ　　　　あ　　　　5
(10行目)　Ｂ　　　　い　　　　7
(11行目)　Ｂ　　　　う　　　　9
(12行目)　Ｃ　　　　あ　　　　12
(13行目)　Ｃ　　　　い　　　　16
(14行目)　Ｅ　　　　う　　　　20
※200行くらいあります。

●Ｙ列～ＡＣ列に「混載距離マスタ」を作成しています。
　　　　　　　Ｙ列　　　Ｚ列　　　ＡＡ列　　　ＡＢ列　　　ＡＣ列
(4行目)　　混載マスタ
(5行目)　　発場所１　発場所２　発場所３　着場所　　距離
(6行目)　　Ａ　　　　　Ｂ　　　　　　　　　　　　い　　　　　40
(7行目)　　Ａ　　　　　Ｃ　　　　　　　　　　　　あ　　　　　32
(8行目)　　Ｂ　　　　　Ｃ　　　　　　　　　　　　い　　　　　30
(9行目)　　Ｂ　　　　　Ｄ　　　　　Ｅ　　　　　　あ　　　　　50
(10行目)　Ｃ　　　　　Ｄ　　　　　　　　　　　　い　　　　　30
※50行くらいあります。

こういった情報をもとに、Ｕ列に合計距離を出したいと思っています。
行数はＢ列の番号が変わる位置に入れたいです。

上の例でいうと次の位置で考えています。
セル「Ｕ６」に直送マスタを参照して「10」と入力
セル「Ｕ９」に混載マスタを参照して「30」と入力
セル「Ｕ１２」に直送マスタを参照して「20」と入力
セル「Ｕ１６」に直送マスタを参照して「40」と入力
なお、マスタに出荷元と入荷先の組み合わせが無い場合は"●"など、
目立つようにしたいです。

こういった感じでいかがでしょうか？
＃本腰を入れて考え始めて３日目になりました。

回答No.1

jcctaira

セルS6の質問と解釈して回答致します。
※T列（混載判断）により、直送はＶ列～X列、混載はＹ列～ＡＢ列を使用します。

S6の式は以下の通りです（配列数式で {} は省略）。
=SUM((V$6:V$150=F6)*(W$6:W$150=H6)*(X$6:X$150)*(T6=""),(Y$6:Y$150=F6)*(Z$6:Z$150=H6)*(AA$6:AA$150)*(T6="混載"))

補足

「#VALUE!」のエラーが出てしまいます。

(1)Ｂ列の番号が１つで、Ｆ列の出荷元が２つ（仮にf1・f2とおく）
ある場合は、「混載」と判定されて、混載マスタを参照する。

f1・ｆ2の情報がＹ列・Ｚ列のどちらかにあって
Ｈ列の情報がＡＡ列にある場合の
ＡＢ列を表示させる。

(2)Ｂ列の番号が１つで、Ｆ列の出荷元が１箇所の場合は
「直送」と判定されて、直送マスタを使用。
Ｆ列の情報がＶ列に
Ｈ列の情報がＷ列にある場合の
Ｘ列を表示させる。

というような書式を設定したいです。

マクロでも、うまく行かないでしょうか。
・Ｂ列がＦ列が２箇所の場合の判定の仕方と
・混載の場合の出荷元の組み合わせが発場所１発場所２に含まれる場合の
混載の距離を出力するなどで難航しています。