EXCEL 年度別に振り分け、さらに特定の文字が何個あるか？

質問

A　　　B　
1　 出荷日　　船名
2　11/04/15　 A丸
3　11/06/22　　B丸
4　12/12/11　　A丸
5　12/04/19　　A丸
6 13/01/20 B丸
7　13/03/01　　B丸
・
・
・
1075
上の図のようにA1に「出荷日」、B1に「船名」というせるがあって、「出荷日」には11年くらいから現在まであり、「船名」はA丸・B丸という風に４３種類の船名が入っていて1075まで出荷日と船名が入っています。
で、質問ですが、上の図を下の図のように年度別（4/1～3/31）に振り分け、さらにその中に船名は何個あるか？という関数はあるのでしょうか？
　　 A　　　　 B　　　　 C
1　 船名　　１１年度　１２年度
2　 A丸 2 1
3　 B丸 1 2

この件を、昨日マイクロソフトへ電話で問い合わせしたのですが、関数を使うと長くなるのでという理由でフィルタでの振り分けしか教えてくれませんでした。
実際、関数は無理なのでしょうか？？

わかりにくい質問分ですが、頭のよい方どうかご教授願います。。。

回答No.6

mu2011 · ベストアンサー

NO3です。
（１）元データの出荷日セルを当初の日付表示に戻します。
（２）シート２の見出し行の年度セルをggge"年度"として、1999/4/1，2000/4/1・・と入力して下さい。
（３）Ｂ２の数式を次のように変更して下さい。
　　=SUMPRODUCT((Sheet1!$A$2:$A$1075>=B$1)*(Sheet1!$A$2:$A$1075<=DATE(YEAR(B$1)+1,3,31))*(Sheet1!$B$2:$B$1075=$A2))

お礼

出来ました!！
思ってた通りの表が出来、本当にありがとうございます。
大変、感謝・感激しています。

自分の会社では多くのEXCELを使い色々データを管理していますが、多くは電卓で計算し手入力をしているので間違いが多々あります。（本当に多い）
式さえ間違わなければ間違いがないはずなんですけど、その式が誰もわからず勉強しようともしないのでEXCELが泣いてますよね・・・。
この、式を応用して他のシートにも移植したいと思います。
本当にありがとうございました。

回答No.5

mu2011

NO3です。
照合条件が不一致となっていると考えられますので、次の点を教えてください。
元データ出荷日セルの書式の表示形式は文字列と想定していますが、如何でしょうか。
又、文字列の場合、入力は全角／半角のどちらでしょうか。

補足

平成11年4月20日と表示していたのを文字列に変換し現在36270という風な（シリアル値？）表示になっています。
入力は半角です。

お忙しい所お手間取らせまして申し訳ありません。

回答No.4

mu2011

NO3です。
年度はシート２の１行目の見出し行と想定しています。
B1セルにセルの書式設定（ユーザ定義で00"年度"）を行い、「11」入力し、右方向にコピー（ドラッグ）して下さい。
数字が変動しない場合、ｃｔｒｌキーを押下しながらドラッグして下さい。

補足

何度もすみません。
式をいれたのですが、すべて0になるですが・・・
式を見てもどこが悪いのかもわかりません。

回答No.3

mu2011

次の方法で如何でしょうか。
・元データをシート１、年度別をシート２とします。
・出荷日は和暦文字列と推定します。
（１）シート２のＡ列に船名を設定
　　船名が多い場合、元データびＢ列を選択→データ→フィルタオプション設定→重複するレコードは無視するにチェック→ＯＫ
　　選択されたセルをコピー＆貼り付けして下さい。
（２）シート２見出し行の年度セルの書式をユーザ定義で00"年度"
（３）シート２のＢ2セルに次の数式を設定して、縦横にコピーしてください。
　　　=SUMPRODUCT((SUBSTITUTE(Sheet1!$A$2:$A$1075,"/","")>=B$1&"0401")*(SUBSTITUTE(Sheet1!$A$2:$A$1075,"/","")<=B$1+1&"0331")*(Sheet1!$B$2:$B$1075=$A2))

補足

回答ありがとうございます。
試してみたのですが、（２）がわかりません。
Sheet2のA列は「船名」で、「年度」セルはどこになるのでしょうか？

回答No.2

driverII

頭の悪さには自信がありますが…

列を追加しても良いなら、船名の後に「出荷年度船名」という列を追加し、
=TEXT(MOD(YEAR(A2),100)+IF(MONTH(A2)<4,-1,0),"00")&B2

を C2 に入れ、フィルドラッグします。
あとはCOUNTIF(C2:C(最後),"11A丸")などで求められます。

補足

回答ありがとうございます。
親会社からのデータで列は追加できないのです・・・

回答No.1

p-applepie

年度を出す関数だけを追加して、ピボットテーブルが
一番楽ではないかと思うのですが・・。

D列に年度というフィールドを作り
=IF(MONTH(B2)>3,RIGHT(YEAR(B2),2)&"年度",RIGHT(YEAR(B2)-1,2)&"年度")

といれれば、年度の形式で表示出来ます。

ピボットテーブルの作り方は文章で説明しづらいのでリンクをご覧下さい。

http://hamachan.fun.cx/excel/piboto.html

この作り方で　
　行エリアに　船名
　列エリアに　年度
　データエリアに　出荷日
を入れればご希望の表が完成します。

補足

回答ありがとうございます
試してみたのですが、書かれている「D列に年度というフィールドを作り」というのは、質問文の下の図のD列にですか？

理解力がなく申し訳ありません。