カウントイフ関数の組み合わせ

質問

お世話になります。

棚の番号　　品番
００１　　　０１
００２　　　０２
００３　　　０３
００４　　　０２
・　　　　　・
・　　　　　・

このような表の場合、棚の番号ごとに品番がいくつあるか
数えたいのですがカウントイフ関数だけではダメで
２つくらいの関数を組み合わせなければならないと思います。
どうしたらいいでしょうか？

ご教授、よろしくお願いいたします。

回答No.5

kagakusuki · ベストアンサー

>棚番号はA１列全体（上から下へ）、品番はB２列全体（上から下へ）・・・ということでお願いします。

　「A１列全体（上から下へ）」というのは「A1以下」という意味で、「品番はB２列全体（上から下へ）」というのは「B2以下」という意味なのでしょうか？
　何故、棚番号と品番の行数が異なるのかは判りませんが、取り敢えず、品番が無い1行目は無視して考える事にしても宜しいでしょうか？

>→半角ハイフォンです。（念のため全角の場合も合わせて教えてください）

　という事ですので、

【半角ハイフォンのみの場合】
　まず、D列に

D1セルに　　　棚の番号
D2セルに　　　００１
D3セルに　　　００２
D4セルに　　　００３
D5セルに　　　００４
　　・　　　　　　・
　　・　　　　　　・
　　・　　　　　　・

という具合に、各棚の番号を入力して下さい。
　次に、Sheet1のE1セルに「品番」と入力して下さい。
　次に、Sheet2のA2セルに次の関数を入力して下さい。

=IF(OR(INDEX(Sheet1!$A:$A,ROW())="",INDEX(Sheet1!$B:$B,ROW())="",COUNTIF(B$1:B1,"="&ASC(INDEX(Sheet1!$A:$A,ROW()))&"゛"&LEFT(ASC(INDEX(Sheet1!$B:$B,ROW())),FIND("-",INDEX(Sheet1!$B:$B,ROW())&"-")-1)&"-*")>0),"",ASC(INDEX(Sheet1!$A:$A,ROW()))&"゛"&ASC(INDEX(Sheet1!$B:$B,ROW())))

　次に、Sheet2のA2セルをコピーして、Sheet2のA3以下に貼り付けて下さい。

　次に、Sheet1のE2セルに次の関数を入力して下さい。

=IF($D2="","",COUNTIF(Sheet2!$A:$A,"="&$D2&"゛*"))

　次に、Sheet1のE2セルをコピーして、Sheet1のE3以下に貼り付けて下さい。

【「-」、「‐」、「－」、「―」、「ー」、「ー」が混在している場合】
　Sheet2のA2セルに入力する関数を、

=IF(OR(INDEX(Sheet1!$A:$A,ROW())="",INDEX(Sheet1!$B:$B,ROW())="",COUNTIF(A$1:A1,"="&ASC(INDEX(Sheet1!$A:$A,ROW()))&"゛"&LEFT(ASC(INDEX(Sheet1!$B:$B,ROW())),MIN(FIND("-",INDEX(Sheet1!$B:$B,ROW())&"-"),FIND("‐",INDEX(Sheet1!$B:$B,ROW())&"‐"),FIND("－",INDEX(Sheet1!$B:$B,ROW())&"－"),FIND("―",INDEX(Sheet1!$B:$B,ROW())&"―"),FIND("ー",INDEX(Sheet1!$B:$B,ROW())&"ー"),FIND("ー",INDEX(Sheet1!$B:$B,ROW())&"ー"))-1)&"-*")>0),"",ASC(INDEX(Sheet1!$A:$A,ROW()))&"゛"&SUBSTITUTE(SUBSTITUTE(ASC(INDEX(Sheet1!$B:$B,ROW())),"‐","-"),"ー","-"))

に変更し、　次に、Sheet1のE2セルに入力する関数を、

=IF($D2="","",COUNTIF(Sheet2!$A:$A,"="&ASC($D2)&"゛*"))

に、それぞれ変更する他は、【半角ハイフォンのみの場合】と同じです。

回答No.4

kagakusuki

>実は、一つの品番で、品薄になると、同じものを 追加購入するので、追加購入する分を子番で管理しています。
>【品番】
>親番　　子番
>０１－０１（１回目）
>０１－０２（２回目）

　つまり、最初に仰っていた内容とは状況が異なるという事ですね。
　確認したいのですが、親番と子番は別々の列に入力されているのでしょうか、それとも、同一のセル内に、ハイフォンを挟んだ形で入力されているのでしょうか、
もし、後者である場合には、全角文字のハイフォンなのでしょうか、半角文字のハイフォンなのでしょうか、或いはまた別の文字なのでしょうか、
又、棚番号や品番は全て数字で構成されているのでしょうか、それとも、「0A2」等という様に、数字以外の文字が混ざる場合もあるのでしょうか？
　それによって、関数が多少変わってまいります。
　出来れば、棚番号や品番のデータ部分（項目名は除く）が、それぞれ何列の何行目から始まっているのかという事と、御使いのExcelのバージョンも御教え頂ければ幸いです。

お礼

本当にいろいろありがとうございます。
はじめからそのつもりだったのですが、説明力が足りず、申し訳ありません。
親番と、子番の間には「-」が入っていますが、「データ」→「区切り位置」機能を
使うことにより、親番だけにできます。

よって、品番については、親番だけにしておき、
棚番号ごとに同じ親番（品番）があるもの（ダブっているもの）が
いくつあるかを数えたいのです。
お忙しいところ大変申し訳ないのですが、回答、待っています！！
また、Excelバージョンは２０００です。よろしくお願いします。

念のため、以下にも回答しておきます。
＞親番と子番は別々の列に入力されているのでしょうか、それとも、同一のセル内に、ハイフォンを挟んだ形で入力されているのでしょうか
→親番・子番とも同一のセル内にハイフォンではさんだ形で入力されています。

＞全角文字のハイフォンなのでしょうか、半角文字のハイフォンなのでしょうか、或いはまた別の文字なのでしょうか、

→半角ハイフォンです。（念のため全角の場合も合わせて教えてください）

又、棚番号や品番は全て数字で構成されているのでしょうか、それとも、「0A2」等という様に、数字以外の文字が混ざる場合もあるのでしょうか？
→ハイフォン以外は全て数字です。

＞棚番号や品番のデータ部分（項目名は除く）が、それぞれ何列の何行目から始まっているのかという事と、
→エクセルなので、自由に構成は変更できると思います。
棚番号はA１列全体（上から下へ）、品番はB２列全体（上から下へ）・・・ということでお願いします。

回答No.3

kagakusuki

回答番号ANo.2です。

>（棚の番号と品番の双方が同一となっているデータが重複しません）

という事は、以下の2行目と4行目の様な

棚番号　　品番
００１　　　０２
００２　　　０１
００１　　　０２
００１　　　０１
　・　　　　　　・
　・　　　　　　・

内容が重複している行は存在しないと考えれば宜しいのでしょうか？
　それでしたら、例えば、棚番号００１棚には、棚番号の列に００１が現れた回数と同じ数だけ、異なる品番が置かれている事になりますから、棚番号ごとに、その棚番号が何回現れるのかをカウントすれば、棚の番号ごとの品番の数と同じ数になる筈です。
　ですから、ANo.2の前半部分で述べました様に、「棚の番号」と入力されているセルがA1セルであり、「品番」と入力されているセルがB1セルであるものとして、D列に各棚の番号を入力しておき、各棚に幾つ品番が存在しているのかをカウントした結果を、E列に表示させるものとした場合には、まず、D列に

D1セルに　　　棚の番号
D2セルに　　　００１
D3セルに　　　００２
D4セルに　　　００３
D5セルに　　　００４
　　・　　　　　　・
　　・　　　　　　・
　　・　　　　　　・

という具合に、各棚の番号を入力して下さい。
　次に、E1セルに「品番」と入力して下さい。
　次に、E2セルに次の関数を入力して下さい。

=IF($D2="","",COUNTIF($A:$A,$D2))

　次に、E2セルをコピーして、E3以下に貼り付けて下さい。

　以上です。

　尚、棚番号が入力されている列がA列では無かったり、棚番号の一覧を作成する列をD列以外の列にされたい場合には、実際のレイアウトに合わせて、適時、関数や入力するセルを修正して下さい。

お礼

大変、分かりづらく申し訳ありません。

実は、一つの品番で、品薄になると、同じものを
追加購入するので、追加購入する分を子番で管理しています。
【品番】
親番　　子番
０１－０１（１回目）
０１－０２（２回目）

※００１は同じ品番であっても２回同じものを購入したことが
　わかるようになります。

つまり、私が知りたかったのは、００１の棚に、品番が０１のものが
いくつあるかカウントしたいのです。
（在庫管理の観点で古いものから削減したいので）

どうしたらよいでしょうか？？

もう少しお付き合いよろしくお願いします。。

回答No.2

kagakusuki

今仮に、「棚の番号」と入力されているセルがSheet1のA1セルであり、「品番」と入力されているセルがSheet1のB1セルであるものとします。

>カウントイフ関数だけではダメで

　何故駄目なのでしょうか、棚の番号と品番の双方が同一となっているデータが重複して存在していないのであれば、
例えばD列に

D1セルに　　　棚の番号
D2セルに　　　００１
D3セルに　　　００２
D4セルに　　　００３
D5セルに　　　００４
　　・　　　　　　・
　　・　　　　　　・
　　・　　　　　　・

という具合に、各棚の番号を入力しておき、

E1セルに　　品番

と入力した上で、E2セルに次の関数を入力してから、E2セルにCOUNTIF関数で棚の番号が現れた回数をカウントする、次の様な関数

=IF($D2="","",COUNTIF($A:$A,$D2))

を入力してから、E2セルをコピーして、E3以下に貼り付ければ良いのではないでしょうか？

　それとも、棚の番号と品番の双方が同一となっているデータが重複して存在する場合もあるのでしょうか？
　その場合には、次の様にされると良いと思います。

　今仮に、Sheet2のA列を作業列として使用するものとします。
　まず、Sheet1の

D1セルに　　　棚の番号
D2セルに　　　００１
D3セルに　　　００２
D4セルに　　　００３
D5セルに　　　００４
　　・　　　　　　・
　　・　　　　　　・
　　・　　　　　　・

という具合に、各棚の番号を入力して下さい。
　次に、Sheet1のE1セルに「品番」と入力して下さい。
　次に、Sheet2のA2セルに次の関数を入力して下さい。

=IF(OR(INDEX(Sheet1!$A:$A,ROW())="",INDEX(Sheet1!$B:$B,ROW())="",COUNTIF(A$1:A1,"="&INDEX(Sheet1!$A:$A,ROW())&"゛"&INDEX(Sheet1!$B:$B,ROW()))>0),"",INDEX(Sheet1!$A:$A,ROW())&"゛"&INDEX(Sheet1!$B:$B,ROW()))

　次に、Sheet2のA2セルをコピーして、Sheet2のA3以下に貼り付けて下さい。

　次に、Sheet1のE2セルに次の関数を入力して下さい。

=IF($D2="","",COUNTIF(Sheet2!$A:$A,"="&$D2&"゛*"))

　次に、Sheet1のE2セルをコピーして、Sheet1のE3以下に貼り付けて下さい。

　以上です。

お礼

大変、親切にありがとうございます。

実は、棚の番号ごとにいくつの品番がのっかかっているか確認したく、
カウントイフ関数を使いたかったのです。

（棚の番号と品番の双方が同一となっているデータが重複しません）

この場合はどうしたらよいのでしょうか？？

棚の番号は５０くらい、品番が５０００くらいだとすると、
下記の例だと、棚番号が００１の場合、品番が２つありますと
カウントしたいのです。

棚番号　　　品番
００１　　　０２
００２　　　０１
００３　　　０２
００１　　　０１
・　　　　　　・
・　　　　　　・

すみません、もう少し教えてください。
どうぞよろしくお願いします。

回答No.1

utun01

いろいろ情報が足りなくて分からないのですが、
Excelの関数のことを言っておられるのであれば
こういった用件はVBAを使ったほうが簡単に実現できますよ。
シート関数のみでやると余分にデータシート用意する必要があったり
それを省くために複雑かつ冗長な処理の関数が必要であったりするので
これを機会にVBAを触って見ることをお勧めします。

また、この例の記述ではいまいちやりたいことがわからないのですが、
品番がプライマリキーになるようなイメージでしょうか？
その辺りも明確にご質問されると、的確な回答が得られるかもしれません。