２つのファイルで大分類の項目が部分一致し小分類の項目が完全一致の数を出したい

質問

1つ目のエクセルファイルに

列・・・大分類の項目（例：テレビ修理）
行・・・小分類の項目（例：ねじ）

２つ目のエクセルファイルに
列・・・小分類や単価等の項目名
行・・・大分類の項目（例：テレビ修理（基本））や小分類の項目（例：ねじ）、単価（￥５０）と横に並んでいます。

それで、

「テレビ修理」・・・１つ目の大分類と２つ目の大分類の項目と一部一致している。
「小分類の項目」・・・１つ目の小分類と２つ目の小分類の名前は完全一致
していたら「単価」（￥５０）とセルに出したいのですが、関数の組み合わせ方がわかりません。

手入力しかないのでしょうか？知恵を貸してください。お願いします。

回答No.2

Nouble · ベストアンサー

「1つめのエクセルファイルの空欄を埋めたい」
ということでしょうかね？

１つめのファイルの表はこういう雰囲気なのでしょうか？

　　　　　Ａ 　　　　　Ｂ　　　　　　　Ｃ　　　　　　　　Ｄ
１０　　　　　　　テレビ修理　ビデオ修理　ラジオ修理　　…
１１　ネジＡ
１２　ナットＡ
１３　　　…

で
２つめのファイルの表はこういう雰囲気なのでしょうか？

　　　　Ａ 　　　　　Ｂ　　　　　　　Ｃ　　　　　　　　Ｄ
１　　　　　　　　ネジＡ　　　　ナットＡ　　　…
２　テレビ修理　５０　　　　　　３０　　　　　　　　
３　ビデオ修理　５０　　　　　　３０　　　　　　　
４　ラジオ修理　５０　　　　　　　３０
５　　　…

行と列が入れ替わってるだけならば
関数でも全く難しくないですね
何せ検索する場所を変えるだけですから

２つめのファイルの名を仮に「ファイル２」とします
双方のファイルが開いている状態で
１つめのファイルのＢ１１に
=OFFSET([ファイル２.xls]Sheet1!$A$1,LOOKUP(1,{1},MATCH(B$10,[ファイル２.xls]Sheet1!$A:$A,0))-1,LOOKUP(1,{1},MATCH($A11,[ファイル２.xls]Sheet1!$1:$1,0))-1,1,1)

と入れてみてください
問題ないはずです

その他の説明については
やっていることがほぼ同じですので
http://oshiete.coneco.net/kotaeru.php3?q=2570100
http://oshiete.coneco.net/kotaeru.php3?q=2569996
をご参照ください

なお双方ともファイルが開いていない状態で更新をかけたり
式を確定されるとエラーになりますのでご注意ください

補足

ありがとうございます。

表の雰囲気はその通りです。

ただ「テレビ修理」の記載がファイル１と２で微妙に違うんです。（コードで揃っていればやりやすいのですが・・・）

文字の置換をしてみます。一度試してみます。

回答No.1

imogasi

（１）VBAでやればなんと言うこともない。
（２）しかしエクセル関数では、部分一致はFIND関数しかなく、それは１セル内だけで考えるには良いが、表の複数セル中からどこにあるか探してくれるものではない。
MATCH関数、VLOOKUP関数は完全一致を探すもので役にたたない。
（３）VBA的になるが、列内範囲指定して、最初に部分一致した行を返すユーザー関数を作れば、本件できるかもしれない。
（４）ひょっとしたら配列数式の回答が出るかもしれない。
ーー
テレビ修理としてテレビ、修理はわかるがテレ、ビ修理、テレビ修
などがくる恐れはないのですか。
大分類をありえる語句にブレークダウンした表を作ることを考えたほうが得策では？

補足

ありがとうございます。
（１）VBAは思いつきませんでした。使えないので余計にｘｘｘ
（２）は私も色々探しましたが、「表の複数のセル」から探し当てる関数はないし、MATCHやVLOOKUPは完全一致のものなのであきらめました。
（３）参考にVBAを教えてくださいますでしょうか？
（４）配列数式のほうが使い勝手はいいかなと思いましたが、「集計をする」目的でないのと、本を読んでも「？？？」となってしまいました。

それと
テレビ修理としてテレビ、修理はわかるがテレ、ビ修理、テレビ修
などがくる恐れはないのですか。
＞それはありません。一くくりの言葉になっています。
大分類をありえる語句にブレークダウンした表を作ることを考えたほうが得策では？
＞大分類を一つずつ置換するか、別のセルに対応させるというのも手かなと思ったのですが、数が多いので挫折しました。