エクセルでの集計で悩んでます。

質問

エクセルでの集計で悩んでます。

（サンプル）
●番号・出荷先・入荷先がＢ列・Ｆ列・Ｈ列に次のように並んでいます。
　　　　Ｂ列　　Ｆ列　　Ｈ列
( 4行目)　番号　　出荷先　入荷先
( 5行目)　（空欄）　（空欄）　（空欄）
( 6行目)　１　　Ａ　　あ
( 7行目)　１　　Ａ　　あ
( 8行目)　１　　Ｂ　　あ
( 9行目)　２　　Ａ　　い
(10行目)　２　　Ａ　　あ
(11行目)　３　　Ｂ　　う
(12行目)　４　　Ｂ　　お
(13行目)　４　　Ｃ　　お
(14行目)　５　　Ａ　　あ
(15行目)　５　　Ａ　　あ
※全部で5,000行くらいあります。

（直送マスタ）
●Ｙ列～ＡＡ列に「直送マスタ」があります。
　　　　　　　Ｙ列　　　Ｚ列　　　ＡＡ列
(4行目)　　直送マスタ
(5行目)　　出荷先　入荷先　距離
(6行目)　　Ａ　　　　あ　　　　10
(7行目)　　Ａ　　　　い　　　　20
(8行目)　　Ａ　　　　う　　　　30
(9行目)　　Ｂ　　　　あ　　　　40
(10行目)　Ｂ　　　　い　　　　50
(11行目)　Ｂ　　　　う　　　　60
(12行目)　Ｃ　　　　あ　　　　70
(13行目)　Ｃ　　　　い　　　　80
(14行目)　Ｅ　　　　う　　　　90
※200行くらいあります。

（混載マスタ）
●ＡＢ列～ＡＨ列に「混載距離マスタ」を作成しています。
　　　　　　　ＡＢ列　　　ＡＣ列　　　ＡＤ列　　　ＡＥ列　　　ＡＦ列　　　ＡＧ列　　　ＡＨ列
(4行目)　　混載マスタ
(5行目)　　出荷先１　出荷先２　出荷先３　入荷先１　入荷先２　入荷先３　　距離
(6行目)　　Ａ　　　　　Ｂ　　　　　　　　　　　　あ　　　　　　　　　　　　　　　　　　　　100
(7行目)　　Ａ　　　　　Ｃ　　　　　　　　　　　　あ　　　　　　い　　　　　う　　　　　　 110
(8行目)　　Ａ　　　　　　　　　　　　　　　　　　あ　　　　　　い　　　　　　　　　　　 　120
(9行目)　　Ｂ　　　　　Ｃ　　　　　　　　　　　　お　　　　　　　　　　　　　　　　　　　 130
※100行くらいあります。
※１つの番号に対して「発場所」もしくは「着場所」が２つ以上あるものが混載です。

※番号は車輛の番号であり、(サンプル)の「3」番「5」番のように１箇所の出荷先に
対して入荷先も１箇所の場合は「直送」で「1」番「2」番「4」番は混載となります。

(1)こういった情報をもとに、Ｗ列に混載の合計距離を出したいと思っています。
（サンプル）の例で考えると、
セル「Ｗ６」に混載マスタを参照して「100」と表示
セル「Ｗ９」に混載マスタを参照して「120」と表示
セル「Ｗ１２」に混載マスタを参照して「130」と表示
といったようにＢ列の番号が変わる度にＷ列に混載の距離が出力されるような関数を設定したいのですが、
教えていただけないでしょうか？

(2)直送マスタは別の関数を設定して何とか表示できているのですが、
セル「Ｗ１１」に直送マスタを参照して「60」と表示
セル「Ｗ１４」に直送マスタを参照して「10」と表示
といったように、直送の距離も混載の距離と合わせて表示できるようなマクロを教えていただけないでしょうか。

回答No.3

KURUMITO · ベストアンサー

かなり難しい対応になりますね。
次のようにいくつかな作業列を使って対応することになります。
ＡＪ６セルには次の式を入力して下方にオートフィルドラッグします。

=IF($B6="","",IF($B6<>$B5,$F6&"/"&$H6,IF(AND(ISNUMBER(FIND($F6,AJ5)),ISNUMBER(FIND($H6,AJ5))),AJ5,IF(ISERROR(FIND($F6,AJ5)),LEFT(AJ5,FIND("/",AJ5)-1)&$F6&MID(AJ5,FIND("/",AJ5),20),IF(ISERROR(FIND($H6,AJ5)),AJ5&$H6,"")))))

ＡＫ6セルには次の式を入力して下方にオートフィルドラッグします。

=IF($B6<>$B7,$B6,"")

ＡＬ6セルには次の式を入力して下方にオートフィルドラッグします。

=IF($Y6="","",$Y6&"/"&$Z6)

ＡＭ6セルには次の式を入力して下方にオートフィルドラッグします。

=IF(AB6="","",AB6&AC6&AD6&"/"&AE6&AF6&AG6)

最後にＷ6セルには次の式を入力して下方にオートフィルドラッグします。

=IF(OR(COUNTIF(B$6:B6,B6)>1,COUNTIF(AK:AK,B6)=0),"",IF(COUNTIF(AL:AL,INDEX(AJ:AJ,MATCH(B6,AK:AK,0)))>0,INDEX(AA:AA,MATCH(INDEX(AJ:AJ,MATCH(B6,AK:AK,0)),AL:AL,0)),INDEX(AH:AH,MATCH(INDEX(AJ:AJ,MATCH(B6,AK:AK,0)),AM:AM,0))))

お示しのデータについて検証したところ番号2の場合には＃Ｎ/Aとエラーが表示されました。この原因は2号車の入荷先が上の行で”い”となっており、下の行で”あ"となっていることによるものです。出荷先や、入荷先については入力の順序を常に同じにしておくことが必要です。すなわち、最初は”あ”で次が”い”になうことが必要です。この順序を守らない場合にはいたずらに式が複雑になり関数での対応はあきらめたほうがよいでしょう。

お礼

毎回、Ｂ列・Ｆ列・Ｈ列を並び変えて表示させることにしました。
そしたら、何とか表示させることができました。

ＩＮＤＥＸ関数・ＭＡＴＣＨ関数のネストなど、今まで活用できなかった
機能を知ることができました。

ありがとうございました！

回答No.2

layy

少なくとも直送か混載かを区別するセルは求めておく。
６行目と１４行目は同じ入出荷でも違いますよね。これを同じ次元で検索できれば今やっている機能の応用で可能ではないでしょうか。

例えば
直送マスタをベースに、６行目に「Ａ」の「あ」の「直」で「10」、１５行目に追加で「Ａ」の「あ」の「混」で「100」という情報もあれば求めているものに近くなります。「Ｂ」の「あ」の「混」で「100」までは求め上で要らない前提です。このデータ群は違うものとして距離マスタとでもつける。逆に混載マスタをベースにしても同じ。

補足で
組み合わせはパターンコードを用意する。

内容によって検索場所や方法変えるのは面倒です。

参考になれば。

補足

＞少なくとも直送か混載かを区別するセルは求めておく。
これは出来ました。

＞６行目と１４行目は同じ入出荷でも違いますよね。
＞これを同じ次元で検索できれば今やっている機能の応用で可能ではないでしょうか。
なんとか、応用してできました。
ただ、複雑な関数になってしまって、自分でも本当にあっているのか微妙な感覚です。

回答No.1

nattocurry

回答ではなくてすみません。

ここまで複雑だと、関数だけでやるには、相当難しいと思います。
私には解りませんでした。

マクロでなら可能だと思います。
簡単にサンプルマクロを作れるほど簡単ではありませんが、作れることは確かです。

これを機に、マクロを勉強されてはいかがでしょうか？

補足

マクロは半年くらい前から学習しているのですが、ごく基本的な
機能を理解しただけなので、まだ使いこなせていません。

いいきっかけなので、もう一度マクロが使用できるやってみます！！