特定の文字列が含まれるセルを別表から参照したい

質問

Excel2010を利用しています。
ある表の中のセル内に特定の文字列が含まれるかどうかを、別表から参照するにはどういった関数の組み方がスマートでしょうか？

言葉ではわかりにくいと思いましたので、添付画像をご覧ください。

A1:E5 までのセルに銀行の通帳の履歴があります。
A7:B11には、摘要欄にどのキーワードが含まれている場合、どの種類になるかを記載しています。

この表のC13～C16までに、それぞれの年に税金と保険料をいくら払ったか、合計する式を入れたいです。

＊銀行の通帳には文字数の制限で末尾が切れたり、所得税のように頭に年度や期が入ったりすることがあるので、前方一致でも後方一致でもなく部分一致検索を希望します。

＊記載されている金額や適用は例です。

回答No.1

keithin · ベストアンサー

準備：
○Ａ２：Ａ５の年月日欄には、2013/12/2の形式で日付として日付を記入してあること
　セルの書式設定で2013.12.2としておくのは構いませんが、「数式バーで見たときに」年/月/日で記入されているという事です
　エクセルに導入したデータが「2013.12.2」形式で、セルに記入した時に（ご相談のサンプル画像とは違って）「左詰めで文字列として」記入されていた場合は、置換によって.を/にすべて置換して年/月/日形式に変換しておきます

○Ａ１３：Ａ１６には、2013のように数値で記入すること
　セルの書式設定の表示形式で0年のように設定して、2013年と表示しておくのは構いません

○Ｂ１３：Ｂ１６には「税金」のように、Ｂ８：Ｂ１１と同じ記載内容で記入しておくこと

手順：
Ｃ１３に
=SUM(SUMIFS($C$2:$C$5,$A$2:$A$5,">="&DATE(A13,1,1),$A$2:$A$5,"<="&DATE(A13,12,31),$B$2:$B$5,"*"&IF($B$8:$B$11=B13,$A$8:$A$11,"x")&"*"))
と記入、必ずコントロールキーとシフトキーを押しながらＥｎｔｅｒで入力、以下コピー。

お礼

出来ました！
ありがとうございます。

それにしても、配列数式と言うのですね。初めて知りました。
引き続き勉強しながらいろいろいじってみます。

回答No.2

kagakusuki

前提としてA2～A5に入力されている日付は、シリアル値で入力されていて、A13～A16に入力されている年は「4桁の数字＋年」の形式の文字列データであるものとします。

　まず、C13セルに次の関数（配列式では御座いません）を入力して下さい。

=IF(OR($A13="",$B13=""),"",IF(ISERROR(MATCH($B13,{"税金額合計","保険料合計"},0)/(RIGHT($A13,1)="年")/($A13&"1月1日")),"計算出来ません",SUMPRODUCT(($B$8:$B$11=INDEX({"税金","保険料"},MATCH($B13,{"税金額合計","保険料合計"},0)))*SUMIFS($C$2:$C$5,$A$2:$A$5,">="&$A13&"1月1日",$A$2:$A$5,"<"&($A13&"12月31日")+1,$B$2:$B$5,"*"&$A$8:$A$11&"*"))))

　次に、C13セルをコピーして、C14～C16の範囲に貼り付けて下さい。

　以上です。

お礼

ありがとうございます！
そうですよね、日付はシリアル値に変換するか何かしないとダメですよね。
預金通帳のデータのコピペですので、そこも何か考えておきます。