Excelで在庫数チェックを行いたい

質問

まず現状ですが、Sheet1のA列に商品型番がa,b,c,d,eとあります。
B列に在庫数を入力します。
Sheet2にはA列に商品型番があるのですが、a,a-1,a-2（以下商品bも同じb-1,b-2）とあります。B列にそれぞれの数量があります。

そこで、在庫入力セルの数式なのですが、Sheet2のa,a-1,a-2の数量を抽出して合計し、その値を返すようにしたいと思っています。商品aはa,a-1,a-2の合計数を表示させるという形です。

分かりにくくて申し訳ないですが、御教授頂ければ幸いです。
宜しくお願い致します。

回答No.5

shiotan99 · ベストアンサー

No.1です。

失礼ですが、No.4さんはワイルドカードというものを理解されていないのではないかと思います‥

ところで、ちょっと確認ですが、No.1の補足は No.1の数式を試された上でのものだったのでしょうか？
No.1の数式ではうまくいかなかったけれど、No.3さんの数式だとうまくいったということですか？
No.1の補足にある型番例を見る限り、No.1の数式で何の問題もないはずで、かえって回答に対する自信が 「なし」から 「あり」に変わったぐらいです。
◆
*（ アスタリスク ）はワイルドカードというもののひとつで、SUMIF関数やEXCELに限らず、検索条件として広く使われるものです。
* は、0個以上の文字列を表しています。

ご質問に例に即して説明します。
3569C55 だけを検索条件にすると、完全に一致するものだけしか検索対象にはなりません。
3569C55* のように後ろにアスタリスクをつけると、＜3569C55で始まるすべての型番＞が対象になります。
3569C55 も 3569C55-1 も 3569C55-CAも、要は 3569C55のあとにつづく文字数に関係なく、3569C55 で始まるものすべてが対象になるのです。

*3569C55 のように前にアスタリスクをつけると、＜3569C55で終わるすべての型番＞が対象になります。
3569C55 や ABC3569C55 のようなものが対象になるということです。
この場合、3569C55-CA のようなものは 3569C55で終わっていないので対象にはなりません。

*3569C55* のように前後にアスタリスクをつけると、＜3569C55を含むすべての型番＞が対象になります。
3569C55 も、3569C55-CA も、ABC3569C55も対象になります。

SUMIF関数での使用例で言えば、
=SUMIF(A:A,"3569C55*",B:B)　
とか　
=SUMIF(A:A,"*3569C55*",B:B)

のようになります。

3569C55 が D2セルに入っていて、D2を検索条件にする場合は、
=SUMIF(A:A,D2&"*",B:B)
とか
=SUMIF(A:A,"*"&D2&"*",B:B)

のようになります。
◆
ですから、ご質問のケースでは＜3569C55で始まるすべての型番＞を検索条件にすればいいわけですから、後ろにだけアスタリスクをつければうまくいくはずで、No.3さんの数式ではOKだったが、No.1のではダメだったということはないと思うのです。
むしろ、前後にアスタリスクをつけるよりは、後ろにだけつける方がベターな気がします。

ついでに、A列が空白の場合を考慮して、No.1の数式は
=IF(A2="","",SUMIF(Sheet2!A:A,A2&"*",Sheet2!B:B))

とした方がいいかもしれません。

↓の参考URLの真ん中あたりにワイルドカードの説明が（ 少しだけですが ）あります。

参考URL：

http://www.eurus.dti.ne.jp/~yoneyama/Excel/kansu/sum.htm

お礼

丁寧なご回答誠に感謝いたします。
No1でもうまくいきました。
ただ、私の説明不足で詳細をお知らせしたかっただけなので、
誤解を招いてしまったならその点はお詫び申し上げます。

今回の件は、実際の在庫管理では後ろだけ＊で大丈夫でした。
ただ、今後の可能性にも対応できるよう前後＊ということは
覚えておきたいと思います。

本当にご丁寧に教えていただいて
助かりました。
また是非宜しくお願い致します。

回答No.4

imogasi

#2です。もちろんSUMIF関数が有用なのはわかります。
沢山の品番でうまくいきましたか。１つならうまくいくでしょう。
しかし難しいのは品番がサブ番号を除いても、多数ある場合（本例もそう）
です。
SUMIF（範囲，検索条件，合計範囲）の検索条件（品番）を、式の複写に都合よいように、並べて設定することです。第二引数をうまく変えるのが難しいのです。
A列の商品型番が２００種あれば、２００個のSUMIF関数を第２引数を変えてを手入力するは大変です。
だから関数でA列に出てくる品番を重複なく、もれなく、たとえばB列に
上から詰めてリストアップする必要がありますが、その関数式が難しいのです。
品番にサブコードが着いているのが、関数式を作成するのをさらに難しくします。
例データA２:A8
A列　　B列
a 1
a
b 2
c 3
a
b
c
B列のB2に
=IF(COUNTIF($A$2:A2,A2)=1,MAX($B$1:B1)+1,"")
下に＋ハンドルを引っ張ると、上記のようになります。
これを同一シートまたは別シートに、B列の１は第１行目に、２は第２行めに
集めれば（ｘ）、上記を達成できます。
すると
SUMIF（範囲，検索条件，合計範囲）の第２引数に使えます。
すると式の複写がうまくいきます。
（X）のやり方は、「imogasi方式」でOKWEBを検索してもらえれば、出てきます。
上記のことを、文章で理解してもらえるかどうか不安があり、＃２のピボットテーブルを進めた次第です。関数に拘らないように勧めます。

回答No.3

Wendy02

こんにちは。

＞aというものを抽出して合計できないかと思ったのですが

#1 さんの回答を少し変えればできると思います。

例:
Sheet1 の B2セルに
　=SUMIF(Sheet2!A:A,A2&"*",Sheet2!B:B)
　　　　　↓
=SUMIF(Sheet2!A:A,"*"&A2&"*",Sheet2!B:B)

A2:A

補足

ありがとうございます。
SUMIF関数の定義はSUMIF（範囲，検索条件，合計範囲）だと思うんですが、
今回の関数だとどのように考えればいいのでしょうか。
範囲：Sheet2のA:A（＝A列）
検索条件：A2の文字列と＊
合計範囲：Ｂ列
こんな解釈で合っておりますでしょうか？
ちなみに頂いた関数を入れてみて解決できました。
今後のために解釈だけ合っているか否か教えていただけないでしょうか。
お手数おかけしますが宜しくお願い致します。

回答No.2

imogasi

（１）まずVBAならできます
（２）商品型番の－の左側は一定文字数ですか、変化がありますじか
すぐ右のワーク列に
一定なら=left(a1,2)
不定なら=LEFT(A6,FIND("-",A6)-1)
のように入れて、枝番を除きます。
そしてピボットテーブルを使いますと
たとえば
商品型番 型番 計数
as-1 as 12
as-cd as 12
as-df as 3
ac-wer ac 4
ac-1 ac 5
asd-1 asd 2
asd-34 asd 1
のB：C列を対象にしてピボットテーブルを作ると
別シートに
合計 / 計数
型番 合計
ac 9
as 27
asd 3
総計 39
のような表が一発でできます。
関数よりこちらがすばやくて、良いと思いますが。

お礼

ご回答ありがとうございます。
ただピボットテーブルの知識がないもので、
どうしても関数で片付けられないかと・・・
もっと勉強して使い方を覚えてから
試してみたいと思います。
ありがとうございます。

回答No.1

shiotan99

こんにちは～

Sheet1の2行目から商品型番が入っているとすれば、

Sheet1 の B2セルに
=SUMIF(Sheet2!A:A,A2&"*",Sheet2!B:B)

と入れて、必要なだけ下にフィルコピー、ではダメでしょうか？

補足

早速のご回答ありがとうございます。
私の説明不足なのですが、型番はa-1,a-2と表記しておりますが、
ハイフン以下が多数なのもあれば、1つ、2つのものもあります。

最初考えたのはVLOOKUPで「a」を検索値としてハイフン以下が何であれ、aというものを抽出して合計できないかと思ったのですが。。。

型番例0125A88
0125A88-1
0125A88-2

3569C55-1
3569C55-2
3569C55-CA
3569C55-6H

といった感じです。