エクセルにてあるセルに年式を打ち込むと、別シートにある顧客リストから、

質問

エクセルにてあるセルに年式を打ち込むと、別シートにある顧客リストから、月・日の区別なしに抽出し、左セルの地方名に準じて、さらに抽出されるような計算式を教えて頂けますでしょうか？

回答No.5

keithin · ベストアンサー

フム。。その式で#VALUEを出すのもなかなかムズカシイのですが，
では
B2に
=IF($A2="","",SUMPRODUCT((LEFT(リスト!$C$1:$C$1300,LEN($A2))=$A2)*(YEAR(0&リスト!$J$1:$J$1300)=B$1)))
としてみてください。

また一応念のため，リストシートの該当列に#VALUEなどのエラー値が計算されていないことを，よく確認しておいてください。

お礼

できました！
よく確認したら、Jの縦列の日付欄にひとつだけ「*」が入力されておりました。
「*」を削除して問題なく「keithin」さんの計算式で反映ができております。
ありがとうございました！　早速仕事で利用させて頂きます！

補足

=COUNTIF(リスト!$C$2:$C$1300,"*"&"東京都"&"*")

をB2に入れれば、ちゃんと数字が出ますが、
御指示頂きました計算式を入れても「VALUE!」と表示してしまいます。
リストシートのCとJの縦列内にてエラー表示を探しましたが、ありません。
少し気になったのは、範囲を狭めて

=IF($A2="","",SUMPRODUCT((LEFT(リスト!$C$1:$C$10,LEN($A2))=$A2)*(YEAR(0&リスト!$J$1:$J$10)=B$1)))

とした場合、「VALUE!」が「0」となりました。しかし1～10の範囲に東京都が含まれているのにカウントされていません。

回答No.6

merlionXX

No1～No3のmerlionXXです。
#VALUE!エラーがでましたか・・・。
念のため確認しますが、J列には日付が、文字列ではなくちゃんと日付形式ではいっているのですよね？

ためしに、J1に日付があるなら、=YEAR(J1)　としてちゃんと「年」が返るか確認してください。

回答No.4

KURUMITO

シート１のＣ１セルから下行に住所が、Ｄ１セルから下行に日付があるとします。
シート２ではＢ１セルに２００７のように入力し、Ａ２セルから下方には東京都などの文字が入力されるとします。
シート２のＢ２セルには次の式を入力して下方にオートフィルドラッグします。

=IF(OR(A2="",B$1=""),"",SUMPRODUCT((ISNUMBER(FIND(A2,Sheet1!C$1:C$1000)))*(YEAR(Sheet1!D$1:D$1000)=B$1)))

補足

御指示どおりにしましたが、「#VALUE!」と表示します。

回答No.3

merlionXX

同じシートでも別シートでもリストの範囲を参照している部分を変えればいいだけです。
仮にリストが別シート（Sheet2）にあり
住所がC列、日付がD列で1～25行までなら、B2の式は、このように変わるだけです。↓

=SUMPRODUCT((ISNUMBER(FIND(A2,Sheet2!$C$1:$C$25)))*(YEAR(Sheet2!$D$1:$D$25)=$B$1))

補足

御指示どおりにしましたが、「#VALUE!」と表示します。

シートの名前は「リスト」なので、下記の通りに修正しましたが、連動しないようです・・・。
=SUMPRODUCT((ISNUMBER(FIND(A4,リスト!$C$1:$C$1300)))*(YEAR(リスト!$J$1:$J$1300)=$B$1))

「リスト」シートのC1～C1300までは住所が記載されています。
「リスト」シートのJ1～J1300までは年式が記載されています。

回答No.2

merlionXX

No1です。
「C2セルは」は「B3セルは」　の間違いです。
B4セルは
=SUMPRODUCT((ISNUMBER(FIND(A4,$C$12:$C$36)))*(YEAR($D$12:$D$36)=$B$1))
です。

補足

早速のご回答ありがとうございます。
当方の添付画像はわかりやすくするために同じシート内で表を作成しましたが、
実際には顧客リストは「別シート」上にあります。
このような場合はどのような計算式になりますでしょうか？
お手数ですが、ご教授頂ければと存じます。

回答No.1

merlionXX

ご掲示のような形式だとすると
住所がC列、日付がD列ですね？
B1に2007、A2に東京都と入ってるとしてB2セルには
=SUMPRODUCT((ISNUMBER(FIND(A2,$C$12:$C$36)))*(YEAR($D$12:$D$36)=$B$1))
C2セルは
=SUMPRODUCT((ISNUMBER(FIND(A3,$C$12:$C$36)))*(YEAR($D$12:$D$36)=$B$1))

と入れてみてください。
リストは12行～36行までの例ですので実際の行にあわせて修正してください。