[[20141017164145]]　『月別で条件にあったもののみを抽出する関数』（あいあい）　ページの最後に飛ぶ

『月別で条件にあったもののみを抽出する関数』（あいあい）

下のような一覧があるのですが、月ごとの件数をだして、その月の中で条件に合ったものを抽出したいのですが、関数がうまくできないのでおしえてください。

番号　　日付　　　　区分　　．．．．．．．．．

 1　　2014/10/1　　 出張
 2　　2014/10/3　　 外出
 3　　2014/10/4　　 直帰
 4 　 2014/11/3　　 出張
 5　　2014/12/1　　 直帰
 6　　2014/12/2　 　直帰
 7　　2014/12/3　　 出張
・
・
・

 月別　　　件数　　　区分　　件数
2014/10　　 3　　　　出張　　 1
　　　　　　　　　　 外出　　 1
　　　　　　　　　　 直帰　　 1
2014/11　　 1　　　　出張　 　1
2014/12　　 3　　　　直帰　　 2
　　　　　　　　　　 出張　　 1

月別だけの抽出や区分別だけの抽出はできたのですが、
月別で区分別というのがうまくできません。
どなたか、教えていただけませんか。

＜使用 Excel：Excel2010、使用 OS：unknown ＞

 ピボットテーブルを利用されてはどうでしょう？
 元の表に2列追加して、（たとえばD/E列）
 D1=TEXT(A1,"yyyy/mm")　項目名を年月
 E1=COUNTIF(C:C,C1)　　　項目名を月件数

 ピボットテーブルを挿入して
 行ラベルに年月、月件数、区分の順番に入れる
 フィールドの設定でレイアウトをアイテムをラベル形式でに変更
 値に日付をいれて、データの個数

 これでどうですか？

（稲葉） 2014/10/18(土) 06:15

回答ありがとうございます。
説明不足で申し訳ありませんが、元の表は変更ができないことと、
ピボットテーブルですでに作成してみたのですが、
日付をグループ化ができないとダイアログがでてきてしまい、
こちらへ質問をしてみました。
（あいあい） 2014/10/20(月) 09:30

 横から失礼します。

 >元の表は変更ができないことと、

 作業用に元の表をコピーして使うことも出来ないのでしょうか？
（カリーニン） 2014/10/20(月) 09:36

 さらに横から失礼。

 >日付をグループ化ができない

 日付のセルが文字列？
 日付の列だけデータタブの「区切り位置」で3/3まで次へを押して3/3画面で「日付」にチェックを入れて「完了」させれば
日付データになってグループ化できるようになるけど、これも変更になるかな？

 そしたら以下のような表はできるよ？

 行ラベル		データの個数 / 区分
 10月		3
 　	外出	1
	出張	1
	直帰	1
 11月		1
	出張	1
 12月		3
	出張	1
	直帰	2
 総計		7

（1111） 2014/10/20(月) 09:42

（1111）さんの方法で試してみたのですが、日付をグループ化ができないと
ダイアログがでてきてしまいます。
なぜなのでしょうか．．．
（カリーニン）さんすみません。できれば元の表はそのままで作成できると
助かります。

（あいあい） 2014/10/21(火) 11:06

 あ、区切り位置をやった後、ピボットテーブルのフィールドリストのチェックを一回全部はずして、もう一度入れて。
 ピボットがセルの書式設定を勘違いしたままかもしれない。

（1111） 2014/10/21(火) 11:26

 ＞日付をグループ化ができない
 に関してですが

 元データの、日付の列に  日付以外のデータが混ざっていませんか？

 たとえば、ピボットテーブルの元データ範囲を
 A:C  の様に指定した場合  日付の列に空白セル（日付以外のデータ）が
 混ざるので、グループ化が出来なく成ります。
 　
 （HANA）

1111さんありがとうございます。でもダメでした。
HANAさんありがとうございます。空白行を削除してもうまくいきませんでした。
ざっと見ると、文字列らしきものはなく10/23のように表示されています。

ピボットは一旦あきらめて、自分なりに日付別、区分別の関数を作成したのですが、
月別で区分ごとに件数を出す関数がなかなかできなかったので下に貼ります。

例：Sheet1のE5:E2102に日付が入っています。下の関数で月ごとの数をだしました。　　
=COUNTIF(Sheet1!E5:E2102,">=2014/10/1")-COUNTIF(Sheet1!E5:E2102,">=2014/11/1")
完成例：2014年10月　3
　　　　2014年11月　1
　　　　2014年12月　3

次は下の関数で区分別件数をだしました。
=COUNTIF(Sheet1!$M$5:$M$2102,"出張")
完成例：出張　　3
　　　　外出　　1
　　　　直帰　　2

問題はここからです。
区分別の出張が2014年10月には何件あったかを数える関数ができません。
想像では、上記2つの関数をくっつけるような感じかと思ったのですが、
エラーになってしまいました。

完成はこのようになるといいのですが．．．
完成：2014年10月　出張　1
　　　・
　　　・
　　　・
　

（あいあい） 2014/10/23(木) 15:59

 条件が二つ以上のカウントなので、
 COUNTIFS関数ではどうですか？

 （HANA）

COUNTIFSで式を作ってみましたがエラーがでます。
COUNTIFSの後にどのように関数をつけたらよいのでしょうか。
（あいあい） 2014/10/23(木) 17:10

 あらら、うまく行きませんか？

 COUNTIFS関数は
COUNTIFS(条件範囲 1, 検索条件 1, 条件範囲 2, 検索条件 2, ...)
 なので
 COUNTIFS(Sheet1!E5:E2102,">=2014/10/1",Sheet1!E5:E2102,"<2014/11/1",Sheet1!$M$5:$M$2102,"出張")
 みたいな感じで。。。

 （HANA）

すみません、お返事が遅くなりました。

教えていただいたように、
COUNTIFS(Sheet1!E5:E2102,">=2014/10/1",Sheet1!E5:E2102,"<2014/11/1",Sheet1!$M$5:$M$2102,"出張")
を参考に入れてみたのですが、どうも結果の件数が多すぎてしまいます。
上の関数は私なりの解釈ですが、2014/10/1～2014/10/31までの中で出張が何件あるか？という考え方で
あっていますか？

前述の質問のように関数を分けた状態だと月件数と区分件数の結果は問題なかったです。
（あいあい） 2014/10/24(金) 15:05

 横から失礼します。
 HANAさんの式で問題なさそうなんですが
 =SUMPRODUCT((TEXT(Sheet1!E5:E2102,"YYYYMM")="201410")*(Sheet1!M5:M2102="出張"))
 だと正確な件数が出ますか？
（se_9） 2014/10/24(金) 15:13

お礼がおそくなってすみませんでした。無事に解決できました。ありがとうございました。
（あいあい） 2014/10/27(月) 09:37

コメント返信：

[ 一覧(最新更新順) ]

[[20141017164145]] 『月別で条件にあったもののみを抽出する関数』（あいあい） ページの最後に飛ぶ

[[20141017164145]]　『月別で条件にあったもののみを抽出する関数』（あいあい）　ページの最後に飛ぶ