[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『種類を数える方法【関数が入力されているセル】』(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
=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
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.