英数半角と文字列の中から、文字列だけを対象にSUMPRODUCTをかけたい

質問

英数半角（7桁固定）と文字列の中から、文字列だけを対象にSUMPRODUCTをかけたい場合はどうすればよいのでしょうか？

一旦、別セルにOKウェブでおしえていただいた、SUBSTITUTEで文字列のみを抽出できたものの、最終的にそれらが合致しているかを確認する為のSUMPRODUCTをかけようとするとTRUEのはずがFALSEになります。
抽出したものは、セル内には実際は計算式が入っている為、計算できないなんてことありますか？
直接しかできないのでしょうか？

関数オンチですみませんが、宜しくお願いいたします。

回答No.1

noname#79209 · ベストアンサー

QNo.4058336
http://security.okwave.jp/qa4058336.html
の#3です。
> 抽出したものは、セル内には実際は計算式が入っている為、計算できないなんてことありますか？
> 直接しかできないのでしょうか？
そんなことはないでしょう。
000S58:あかさたな商事
020230:いきしちに会
を
=SUBSTITUTE(A1,LEFT(A1,7),"")
で
あかさたな商事
いきしちに会
としたものをどうSUMPRODUCT関数でやろうとしているのですか？

お礼

すみません。
関数が全くと言っていいほどわかっていないのでおかしな質問をしてしまっているようですね(^^;;
おかげさまで　=SUBSTITUTE(A1,LEFT(A1,7),"")で抽出はできました。
もう一度、最初からやり直したらうまくいきました。
ありがとうございました。

回答No.5

noname#79209

#1です。
ここは、SUMPRODUCTも、UBSTITUTEも、REPLACEもすべて忘れて、
「A1のデータが、B1～I1の中にあるかどうか」のチェックに集中しましょう。また、その際、#2さんへのコメントにある「空白セルは一致とみなす」も忘れましょう。
とにかく、「A1と同じものが、B1～I1に存在するかのチェック」に的を絞ります。
そして、「存在＝Trueを返す」と決めます。（これをキチンと決めておかないと混乱します）
「存在をチェックする」だけですから、COUNTIFがいいでしょう。
=(COUNTIF(B1:I1,A1)>0)
この数式で、存在すればTrueが返されます。
つまり、A1と同じものがあるセルを数えて、1以上なら「存在」です。

次に、「空白セルは一致とみなす」ですが、
多分これが数式を考える上で悪影響を及ぼしていると感じます。
質問者さんが提示した式から考えると、
B1～I1の中に1つでも空白があれば、存在チェックをするまでもなくTrueのようです。
このあたりで、論理式のドツボにハマっているようです。
１．B1～I1の中は数式。
２．それぞれのセル位置は特定の取引先と関連づけられている。
つまり、ある商品を扱っている取引先がB1～I1に表示されるようになっていて、
B1が「○○商事」、C1が「△△物産」というように固定されていて、
その取引先にその商品の扱いがあれば、その取引先名を表示、
扱いがなければ「""」が表示されるのでしょう。
もしそうなら、
「空白セルは一致とみなす」という妙な（曖昧な）考え方でなく、
「空白セルは検索対象から除外（無視）」というスタンスのほうが良いのでは？

お礼

お2人とも長い間、関数オンチの私に丁寧に教えてくださってありがとうございました。
おかげさまで、無事データ処理も完了しました。

良回答のポイント、どちらにも20ポイント差し上げたいのですが、
そういうわけにも行きませんので、書いてくださった順番にさせていただきます。

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

回答No.4

noname#176215

その表がどんな構成で 何を目的としたもので それぞれのセルに何が
入っているのかが 説明されていないので 何がなんだか分かりません。

以下予想です。

「000S58:あかさたな商事」のようなデータが B1:I1セル内に入って
いて A1セルの会社名が範囲内に「8」個あるかどうかを チェックし
たいのですか？　指定がない場合は TRUEということで？

=COUNTIF(B1:I1,"*"&A1)=8

違うようなら もう少し 最初から説明して下さい。

回答No.3

noname#176215

#2の者です。取り除いたデータで何をしようとしているのでしょう。
頭の７文字を取り除くだけなら「=REPLACE(A1,1,7,"")」で済みます。

「最終的にそれらが合致しているかを確認する」の意味が分かりません。

=COUNTIF(A1:A10,"*あいうえお商会")
もしくは
=COUNTIF(A1:A10,"*"&B1)

のようにして個数を数るのとは違いますか？
最終的になさりたいことを省略せずに説明して下さい。

お礼

ご面倒をおかけしております。
=REPLACEは、別セルですよね。
とりあえず、別セルで取り除くことはできました。

「最終的に合致しているか」は、ひとつの商品に対して、複数の取引先が存在するものがあり、同じ取引先名かをチェックするということです。
個数を数えるのではなく、TRUEかFALSEです。
=SUMPRODUCT((B1:I1=A1)+(B1:I1=""))=8
で合っていますか？
合致していないのに、TRUEが表示されたりします。
かといって、合致＝FALSEでもないようです。
参照セル(A1）が空白の場合、B1～I1それぞれが異なる社名が入っていても、TRUEとみなされますか？

ちゃんと説明できていますでしょうか？
本当にすみません。でも教えていただいて助かっています！

回答No.2

noname#176215

「SUMPRODUCT」は「積の和を返す関数」です。ですが他の用途に利用する
人が非常に多いですね。利用用途が広いので「SUMPRODUCTをかけたい」だ
けでは何をしたいのかさっぱり伝わりません。

SUMPRODUCTで どんな計算をしようとしているのか 数式と共に提示して
下さい。

> SUBSTITUTEで文字列のみを抽出できたものの
これをやるための作業セルがあるなら 計算に SUMPRODUCTを使う必要が
あるとは思えません。

お礼

「SUMPRODUCT」は「積の和を返す関数」なのですね。
色んな用途があることすらわかっていませんでした。すみません。
できれば、作業セルを新たに作らずに文字列のみを抽出できないかと思ったのですが、複雑でしょうか？

補足

6桁の半角英数：会社名　が入っているセルがあり、
そのうちの社名だけを対象に、9社が合致するか（空白セルは一致とみなす）をチェックしたかったのです。
作業セルを作らざるを得ないと思い、作業セルでSUBSTITUTEをして抽出しました。
しかし、あまりにも横に長くなりすぎていて、他の比較項目との参照がスクロールをしないと見れない為、元のセルのまま合致の確認をしたかったのです。（合致結果のセルは用意できます）