VLOOKUPとIFCOUNTの組み合わせについて

質問

初めての投稿です。

下記の様な場合、どの様な関数で値を返すことができますでしょうか。。。

【 シート１の列Aにあるデータを参照し、該当があった場合、シート１のM列にある「特定データの合計値」をシート２のE３に返す 】
※シート２のE3のセルにVLOOKUPとIFCOUNTの組み合わせの関数を入れる形になるかと・・・

ご存じの方がいらっしゃったら、是非ご教授頂けますでしょうか。

回答No.11

nekotaro3

SUMPRODUCT関数を使って集計する方法をご紹介します。

Sheet1A1:A1000とM1:M1000にデータがあるとします。A列には名前、M列には商品名があります。

名前が[吉田]で、商品名が[テレビ]のデータ件数を求める場合は、
=SUMPRODUCT((Sheet1!$A$1:$A$3000="吉田")*1,(Sheet1!$M$1:$M$3000="テレビ")*1)
と入力すれば抽出できます。

添付画像の右側のような集計表を作成します。
左端と上端の値を参照するように変更すると、
B2セルの関数は
=SUMPRODUCT((Sheet1!$A$1:$A$3000=$A2)*1,(Sheet1!$M$1:$M$3000=B$1)*1)
になります。

B2セルをコピーしてB3:B12にペーストすると、
Sheet1!$A$1:$A$3000
Sheet1!$M$1:$M$3000
行も列も絶対参照になっているので、そのままです。

$A2は行が相対参照なので、$A3,$A4,$A5…"林","長谷川","山村"…と変わります。
B$1は行が絶対参照なので、ペーストしても変わりません。

B2:B12の関数は、各人物の「テレビ」のデータ件数を求めることができます。

次にB2:B12をコピーして、C2:K12に貼り付けます。

Sheet1!$M$1:$M$3000=B$1
B$1は列が相対参照なので、C$1,D$1,E$1…"テレビ"、"パソコン"、"モニター"…と変わります。

こうすれば、コピー＆ペーストで集計表を作成することができます。

全部のデータを合計した値（集計表の右下の青いセル）は１０００になっています。
Sheet1のデータの最終行と同値になります。

Sheet1にデータを追加すれば、集計表の結果も変わります。

回答No.10

178-tall

> (【シート２】はあと回しとして) 【シート１】にて一例。

このあと始末を忘れてた…？

【シート１】から

B(ID)　 E(●●)　 F(■■)　 G(□□)

のブロックを「切り抜き」【シート２】に「貼り付け」てチョン！ … でした。

回答No.9

178-tall

>【シート１】

　　 A(ID)　M(評価)
-----------------
　1　 #1　　　●●
　2　 #1　　　■■
　3　 #1　　　□□
　4　 #4　　　●●
　5　 #5　　　□□
　6　 #3　　　□□

に対し、(【シート２】はあと回しとして)【シート１】にて一例。

　　 B(ID)　 E(●●)　 F(■■)　 G(□□)
----------------------------------------------
　1　 #1　　　 1　　　　　1　　　　 1

　　　　　　　　↑
　=COUNTIFS(($A$1:$A$6),$B1,($M$1:$M$6),"●●")
　　　　　　　　　　　　　↑
　　　　　　=COUNTIFS(($A$1:$A$6),$B1,($M$1:$M$6),"■■")
　　　　　　　　　　　　　　　　　　　↑
　　　　　　　　　=COUNTIFS(($A$1:$A$6),$B1,($M$1:$M$6),"□□")

　2　 #2　　　 0　　　　　0　　　　 0
　3　 #3　　　 0　　　　　0　　　　 1
　4　 #4　　　 1　　　　　0　　　　 0
　5　 #5　　　 0　　　　　0　　　　 1
　6　 #6　　　 0　　　　　0　　　　 0

回答No.8

178-tall

>【 シート１の列Aにあるデータを参照し、該当があった場合、シート１のM列にある「特定データの合計値」をシート２のE３に返す 】

ひとまず「シート２」は忘れて、「列Aにあるデータを参照し、該当が」あるか否か、の方を考えましょう。

最も単純なのは、該当する「ID」は指定したセル範囲に書きこむ場合。
これなら、COUNTIF で ”該当する「ID」かつ条件「●●」が true な「人数」を集計できそう。

こっちの方が難問なのかも…。

回答No.7

178-tall

>シート１のM列は数値ではなく、テキストになります。
>そのテキストが「●●」の個数の合計をカウントしたいのです。

　　　A　　　　M
-----------------
　1　 #1　　　●●
　2　 #2　　　■■
　3　 #3　　　□□
　4　 #4　　　●●
　…　…
　10　#10　　 ●●

それだけだのカウントなら、行範囲 (M1:M10) にて単一条件「●●」の場合、
=COUNTIF(M1:M10,"●●")
で結果を得られそう。

(相手はスプレッドシート、正確な「仕様」無しじゃ使える答が得られません)

補足

178-tallさん

ご指摘ありがとうござます。

おっしゃるとおり、正確な仕様なしでは無理ですね…大変失礼致しました。

【シート１】

　 A(社員ID) M(評価)
-----------------
　1　 #1　　　●●
　2　 #1　　　■■
　3　 #1　　　□□
　4　 #4　　　●●
　５　#5　　　□□
　6　 #3　　　□□
　…　…
　10　#1　　 ●●

上記のシートをシート１として、シート２に下記の様な表を作成し、
「シート１の社員IDを参照して、その社員の評価を評価種類別にカウント」したいのです。

【シート２】

　 Ｂ(社員ID) E(●●) 　 F(■■) G(□□)
----------------------------------------------
　1　 #1　　　 2　　　　　1　　　　 １
　2　 #2　　　 0　　　　　0　　　　 0
　3　 #3　　　 0　　　　　0　　　　 1
　4　 #4　　　 1　　　　　0　　　　 0
　4　 #5　　　 0　　　　　0　　　　 1
　…　…

＜補足＞
※随時入力、編集するシートはシート１となります
※シート１を入力、編集すると、シート２が自動更新される・・・と言う仕様にしたいです

回答No.6

shintaro-2

>シート１のM列は数値ではなく、テキストになります。
>そのテキストが「●●」の個数の合計をカウントしたいのです。

> 説明が拙くてごめんなさい・・・。

つたないと言うよりは、
単に足りないだけですけど

文字データの個数なら、
Dcountaを使います。

参考URL：

http://okwave.jp/answer/new?qid=9060691

回答No.5

shintaro-2

>sumifも試しましましたが、やはりそれでは解決しませんでした…。

どうやって駄目だったのかさっぱり伝わってきませんが？

例えば
sheet2のA２に検索条件があったとすれば
sheet2のB２に
=sumif(sheet1!a2:a100,a2,sheet2!m2:m100)
と入力すれば、シート２のA2の社員IDに対応するシート１のｍ列の値を合計してくれますが？

どんな元データで、どこにどんな式を入れたのですか？

参考URL：

http://pc.nikkeibp.co.jp/article/NPC/20070605/273650/

補足

＞例えば
＞sheet2のA２に検索条件があったとすれば
＞sheet2のB２に
＞=sumif(sheet1!a2:a100,a2,sheet2!m2:m100)
＞と入力すれば、シート２のA2の社員IDに対応するシート１のｍ列の値を合計してくれますが？

シート１のM列は数値ではなく、テキストになります。
そのテキストが「●●」の個数の合計をカウントしたいのです。

説明が拙くてごめんなさい・・・。

回答No.4

178-tall

”M列にある「特定データの合計値」”がみな同一値 (M) なら、
　COUNTIF ( ) の結果に M を乗算。

…じゃなければ、結構メンドウ。
　列 A の該当行のほかの列に「目印文字」を入れてる。
　M 列にて「目印文字」に該当する行だけを合計。
… が一案。

とりあえず、ドッチですか？

補足

178-tallさん

＞”M列にある「特定データの合計値」”がみな同一値 (M) なら、
＞COUNTIF ( ) の結果に M を乗算。

M列の値（テキスト）は同一値ではありません。。。
M列の値は数種類あります。

ちなみに、人材の情報を管理している表になります。

シート１のA列には、「人材を管理する為のID」があり、
そのIDを参照し、シート１のM列にある「●●」や「■■」を集計し、
その結果をシート２のE3（●●）、F3（■■）に返したい・・・と言うのが今回のご相談です。

回答No.3

msMike

IFCOUNT？
此処は Excel の部屋ですけど・・・

補足

msMikeさん

すみません。
COUNTIFと入力したつもりでした。

回答No.2

mshr1962

シート2!E3=SUMIF(シート1!A:A,"該当",シート1!M:M)

※１．"該当"の部分によっては、セル参照も可
※２．シート1!Aの条件が一致でなく範囲の場合は
シート2!E3=SUMIFS(シート1!M:M,シート1!A:A,">0",シート1!A:A,"<100")
のようになります。
※３．シート1!Aの条件が複数の場合は、SUMIFSの足し算で対応
シート2!E3=SUMIF(シート1!A:A,"条件1",シート1!M:M)+SUMIF(シート1!A:A,"条件2",シート1!M:M)

補足

mshr1962さん

ご教示頂いた内容を試しましたが、はやり解決出来ませんでした。
※私の読解力が弱いのだと思いますが…