別シートのセルに入力された文字列を別シートで集計

質問

こんにちは。
求人募集を数社へ掲載しているのですが、採用となった人数から費用対効果をエクセルで測定したいと思っています。
・面接シートと求人掲載シートがあります。
・面接シートには応募された方の
名前｜掲載媒体名(略称)｜問合わせ日｜面接日｜採用・不採用
と言うセルがあり、
┝掲載媒体名には略称(OKウェブ⇒OK)で入力されます。
└採用の場合は「１」、不採用の場合は「２」で入力されます。
・求人掲載シートには掲載に関する
求人媒体名｜月額広告費｜問合わせ数｜面接数｜採用数｜不採用数｜費用対効果
と言うセルがあり、
┝掲載媒体名は総称(OKウェブ⇒OKウェブ)で入力されます。
└費用対効果は月額広告費÷採用数で算出します。

エクセルでやりたい事は、
・面接シートの略称された求人媒体名を求人掲載シートの総称求人媒体名として認識し、その列の問合わせ数、面接数、採用数、不採用数へ合計数値として認識させたいのです。
そして、最終的に各媒体ごとの採用数を求め、月額広告費の一人辺りに対する費用を算出したいのです。
因みに掲載求人媒体は全部で20程あります。

分かりにくいご説明で恐縮ですが、どなたかご教示の程宜しくお願い致します。

回答No.3

K Kazz（@JazzCorp） · ベストアンサー

（条件）
・求人掲載シートのキー（Ａ列）はユニーク（面接シートのＡ列も同じ形式）
・面接シートのＣ～Ｅ列を数値化するために、Ｆ～Ｉ列を使用する。
Ｃ列：空白でないものをＦ列に１でカウント
Ｄ列：空白でないものをＧ列に１でカウント
Ｅ列：採用（半角：1）をＨ列に１でカウント、不採用（半角：2）をＩ列に１でカウント
＜面接シート＞
Ｆ２に、
=IF($C2<>"",1,0)
Ｇ２に、
=IF($D2<>"",1,0)
Ｈ２に、
=IF($E2=1,1,0)
Ｉ２に、
=IF($E2=2,1,0)
＜求人掲載シート＞
Ｃ２に、
=SUMIF(面接!$B:$B,$A2,面接!F:F)
Ｄ２に，
=SUMIF(面接!$B:$B,$A2,面接!G:G)
Ｅ２に、
=SUMIF(面接!$B:$B,$A2,面接!H:H)
Ｆ２に、
=SUMIF(面接!$B:$B,$A2,面接!I:I)
Ｇ２に、
=IF($E2=0,"-",($B2/$E2))

以下、Ｃ～Ｆセットで適当にコピー

お礼

ご教示有難うございます！SUMPRODUCT関数を用いたら出来ました！
ご教示頂いた方法でも試してみます！

回答No.4

K Kazz（@JazzCorp）

掲載媒体名には略称と総称の扱いについて補足しておきます。
＞┝掲載媒体名には略称(OKウェブ⇒OK)で入力されます。
＞┝掲載媒体名は総称(OKウェブ⇒OKウェブ)で入力されます。
＞因みに掲載求人媒体は全部で20程あります。

略称の基準が判らない。
「あいまい検索」がしたい？、前方一致？、中間一致？
この程度の数なら、求人掲載シートには、略称、総称、両方入れて略称を検索キーにすべき。

お礼

ご教示有難うございます。SUMPRODUCT関数を用いたら出来ました！

回答No.2

KURUMITO

回答No1です。
先の回答でE3、F3セルへの入力の式は次のように変更してください。

採用数のE3セルには次の式を入力して下方にドラッグコピーします。

=IF($A3="","",COUNTIFS(面接!$B:$B,$A3,面接!$D:$D,">"&$A$1,面接!$D:$D,"<="&DATE(YEAR($A$1),MONTH($A$1)+1,0),面接!$E:$E,1))

不採用数のF3セルには次の式を入力して下方にドラッグコピーします。

=IF($A3="","",COUNTIFS(面接!$B:$B,$A3,面接!$D:$D,">"&$A$1,面接!$D:$D,"<="&DATE(YEAR($A$1),MONTH($A$1)+1,0),面接!$E:$E,2))

お礼

再度のご教示有難うございます！う～～～ん・・・うまく行かない・・・(>_<)

補足

色々とご教示頂き有難うございました！
SUMPRODUCT関数を用いたら出来ました！

回答No.1

KURUMITO

面接シートではB1セルが掲載媒体名、E1セルが採用・不採用のどの項目名になっているとします。
求人掲載シートでは月額広告費の項目もあるのである年月でのデータをまとめるとの考えでA1セルにはそのある年月を例えば2012年6月のように入力します。数式バー上では2012/6/1のように表示されます。
A列からG列の2行目にはお示しのような項目名があるとしてA３セルからの下方には求人媒体名（略称）が、B３セルから下方には月額広告費が入力されているとします。
問合せ数のC列でC3セルには次の式を入力して下方にドラッグコピーします。

=IF($A3="","",COUNTIFS(面接!$B:$B,$A3,面接!$C:$C,">"&$A$1,面接!$C:$C,"<="&DATE(YEAR($A$1),MONTH($A$1)+1,0)))

面接数ではD3セルに次の式を入力して下方にドラッグコピーします。

=IF($A3="","",COUNTIFS(面接!$B:$B,$A3,面接!$D:$D,">"&$A$1,面接!$D:$D,"<="&DATE(YEAR($A$1),MONTH($A$1)+1,0)))

採用数のE3セルには次の式を入力して下方にドラッグコピーします。

=IF($A3="","",COUNTIFS(面接!$B:$B,$A3,面接!$C:$C,">"&$A$1,面接!$C:$C,"<="&DATE(YEAR($A$1),MONTH($A$1)+1,0),面接!$E:$E,1))

不採用数のF3セルには次の式を入力して下方にドラッグコピーします。

=IF($A3="","",COUNTIFS(面接!$B:$B,$A3,面接!$C:$C,">"&$A$1,面接!$C:$C,"<="&DATE(YEAR($A$1),MONTH($A$1)+1,0),面接!$E:$E,2))

費用対効果のG3セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNT(B3,E3)<>2,"",IF(E3=0,"∞",B3/E3))

お礼

早速のご教示有難うございました！ご教示頂いた方法で行って見ましたがうまくいきません・・・(>_<)
頑張ってみます。