[[20210212151200]] 『種類を数える方法【関数が入力されているセル】』(4年に1回命日) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『種類を数える方法【関数が入力されているセル】』(4年に1回命日)

質問失礼いたします。商品の種類を数える関数を知りたいです。今作成中なのは下の通りです。
SHEET1〜4 
   A列    B列    C列    D列   E列    F列    
1行目担当:〇岡 1回目   2      3    4   種類数合計     
2行目新規    TH機  GG機  AL機  KP機   4  
3行目増加   JI機       BN機  MA機   3
4行目解約   FF機                  1
5行目減少   IH機  VV機       SJ機   3

SHEET5
1回目 A列    B列    C列    D列   E列    F列    
1行目 担当者→〇岡    △谷   〇◎藤   □   種類数合計     
2行目 新規    TH機  JJ機  FF機  D機   4  
3行目 増加   JI機       YY機  YY機  2
4行目 解約   FF機                 1
5行目 減少   IH機  HC機       SJ機  3
SHEET1〜4に担当者ごとの契約数を入力し、SHEET1〜4の合計欄に
=SUMPRODUCT((B2:E2<>"")/COUNTIF(B2:E2,B2:E2&""))
といった内容の関数を各々の行に入力し種類数を数えました。
SHEET1〜4をSHEET5に担当者ごとの新規〜減少に並べ替えた内容をINDEX+MATCH関数を使い抽出しました。そしてその4名の担当者の1回目の契約ごとの種類をSHEET5の種類数欄に数える関数を入力しようと思うのですが、空欄に見えても関数が入っているからなのか、SHEET5の種類数合計欄にSHEET1〜4に使用した関数と同じ関数を入力すると全セル空欄で"ゼロ"種類となるべきところ、1種類となってしまいます。
他のシートからの取りまとめを行うシートなので、関数は外せません。
どうかお知恵をお貸しいただけませんでしょうか。

< 使用 Excel:Excel2016、使用 OS:Windows10 >


 >INDEX+MATCH関数を使い抽出しました。
 >空欄に見えても関数が入っているからなのか

 それがどんな式なのか、その式を提示してもらわないとわかりません。

 ひとつ考えられるのは、空白を「""」ではなく「" "」とかスペースにしてませんか?

 以上
(笑) 2021/02/12(金) 16:25

早速ありがとうございます。例えばB2セルに、

=INDEX(SHEET1!$B$2:$E$5,MATCH(SHEET5!$A2,SHEET1!$A$2:$A$5,0),MATCH(SHEET5!$G$1(←G1セルに"1回目"を指定しています),SHEET1!$B$1:$E$4,0))

という関数を入れました。他のセルにも同様の関数を入力し、商品の種類、他にも契約件数・金額を4名分抽出しています。

なお、例では新規・増加・解約・減少を1行ずつにしておりますが、実際は新規〜減少までの商品種類を3行ずつ設けております。必ずしも一つの回で3行全てが埋まるわけではありませんので、SHEET5に抽出結果を表示した際、空欄箇所は"0(ゼロ)"と表示されます。
その"0(ゼロ)"表示は、条件付き書式で【指定の値を含むセルだけを書式設定→セルの値→次の値に等しい→=0(ゼロ)→書式→フォント→白】とし、空欄の様に見せています。

ひとつ考えられるのは……とご指摘いただきました箇所は、「" "」ではなく、「""」となっていることを確認いたしました。

何卒よろしくお願いいたします。
(4年に1回命日) 2021/02/12(金) 17:08


 >抽出結果を表示した際、空欄箇所は"0(ゼロ)"と表示されます。

 つまり空白ではなく「0」を非表示にしてるだけでしょ?
 全部「0」だったら「1」になって当然ですよね(1種類「0」があるんだから)

 >=INDEX(SHEET1!$B$2:$E$5,MATCH(SHEET5!$A2,SHEET1!$A$2:$A$5,0),MATCH(SHEET5!$G$1(←G1セルに"1回目"を指定しています),SHEET1!$B$1:$E$4,0)) 

 最後の方の SHEET1!$B$1:$E$4、合ってますか?
 Sheet1!$B$1:$E$1 では?
             ~~~~
 だとして

 =INDEX(Sheet1!$B$2:$E$5,MATCH($A2,Sheet1!$A$2:$A$5,0),MATCH($G$1,Sheet1!$B$1:$E$1,0))&""

 上のように最後に &"" を付ける(Sheet5 というシート名は不要)
 こうすれば Sheet1 が空白でも「0」にはなりません(戻り値が文字列の場合)
 条件付き書式はクリアしてもかまいません。

 ただ、その SUMPRODUCT の式で正しい結果が返るかどうか(演算誤差が発生しないか)知りませんけど・・・

 以上
(笑) 2021/02/12(金) 17:37

 >ただ、その SUMPRODUCT の式で正しい結果が返るかどうか(演算誤差が発生しないか)知りませんけど・・・

  ↑ はスルーしてください。

 実際の範囲も B列〜E列なんですかね?
 とりあえず同じものが 27個並ぶことがなければ問題ないかも

 以上
(笑) 2021/02/13(土) 10:27

理解するのに時間がかかってしまいました。御礼が遅くなり申し訳ございません。
ありがとうございました。省略のためE列までにしましたが、実際はN列までです。
本当にありがとうございます!!
(4年に1回命日) 2021/02/26(金) 09:41

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.