Excelの一覧を、条件をつけて関数を用いて別シート集計したいのです。

質問

Excelの一覧を、条件をつけて関数を用いて別シート集計したいのです。

A列（項目）　　　　B列　　　C列（カテゴリ）
○○○　　　　　　9/3　　　　aaa
××× 　　　　　　9/18 　　　bbb
. . .
. . .
. . .

上記のような一覧があるのですが、「カテゴリ別」「週ごと」の件数を出したいのです。
関数自体が簡単なIF文程度の知識しかないため、何から手をつけてよいのかサッパリです。
なお、B列に入っている日付は週の頭などの法則性はなく、
日次でのスケジュールが入っている状態です。

すみませんが、ご教授お願い致します。

回答No.5

KURUMITO · ベストアンサー

元のシートには手をつけたくないとのこと、何カ月もの集計になること、年がまたがった日付となるケースもあること、などを考えるなら作業用にシートを別に用意して対応するのがよいでしょう。
例えばシート1に元のデータがあるとします。Ａ，Ｂ，Ｃ列でそれぞれのデータが2行目から下方に有るとします。
作業用のシートとしてシート2を用意して、Ａ2セルには次の式を入力してＢ2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=Sheet1!B2

これでシート1のＢ列とＣ列のデータがシート2のＡ列およびＢ列に表示されます。
シート2では作業列をいくつか設けます。
Ｃ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNTIF(B$2:B2,B2)=1,MAX(C$1:C1)+1,"")

これでカテゴリの種類が違うものについて上から順に番号が付けられます。
次にＤ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",IF(ROW(A1)=1,WEEKNUM(A2),IF(AND(ROW(A1)>1,YEAR(A2)=YEAR(A1)),WEEKNUM(A2),IF(AND(ROW(A1)>1,YEAR(A2)<>YEAR(A1)),IF(WEEKDAY(DATE(YEAR(A2),1,1))=1,WEEKNUM(DATE(YEAR(A2)-1,12,31))+WEEKNUM(A2),WEEKNUM(DATE(YEAR(A2)-1,12,31))+WEEKNUM(A2)-1),""))))

これでＡ2セルの日付が最初に入力した年の1月1日から何週目に当たるかを求めています。あとから入力した年が次の年になった場合でも最初に入力した年からの何週目かを求める式になっています。
次にＥ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",(D2-MIN(D:D)+1)&B2)

これで最も古い日付の週を第1週として、その週とカテゴリを連結させたデータが表示されます。
次にお求めの表をシート2に表示させることにしても良いのですが、別のシート3に作成することにします。
シート3のＡ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(ROW(A1)>MAX(Sheet2!C:C),"",INDEX(Sheet2!B:B,MATCH(ROW(A1),Sheet2!C:C,0)))

Ａ列にはすべてのカテゴリが並ぶことになります。
次にＢ1セルには次の式を入力して右横方向にオートフィルドラッグします。

=IF(COLUMN(A1)>MAX(Sheet2!$D:$D)-MIN(Sheet2!$D:$D)+1,"","第 "&COLUMN(A1)&" 週")

これで1行目には入力されている日付で最も古い日の週を第1週として、最も新しい日付の週まで表示されます。
最後にＢ2セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。

=IF(OR(B$1="",$A2=""),"",COUNTIF(Sheet2!$E:$E,COLUMN(A1)&$A2))

これでお求めの表が完成です。
仮にシート1で、日付やカテゴリについて、新しいデータが追加されたりデータが修正されたりしても即座にシート3の表も変化することになります。また、入力の日付が翌年にまたがるような場合でも誤りなく対応することができます。勿論、日付の入力では9/10と入力すれば今年の9月10日として入力したことになりますね。2011年1月5日のような場合には2011/1/5のように年を付けて入力することが必要となりますね。
なお、作業シートであるシート2についてはシート見出しで右クリックし、「非表示」を選択することで通常での他人による誤った操作を防止することもできますし、また、シートの保護をすることで他人によるシート内容の変更ができないようにすることもできますね。

お礼

日付の部分にも言及して頂いて助かりました。
エクセルはまだまだ使いこなせませんが、なんとかがんばります。
ありがとうございました。

回答No.6

KURUMITO

回答の5です。
シート3で1行目には第何週が表示されますが、第何週が実際には何日からの週であるのか分からないのでは困るのではないでしょうか？
もしも、その週の始まりの日付、ここでは日曜日に当たりますがその日付を2行目に表示させるとしたら、今作られた表の2行目を選択して右クリックし「挿入」を選択します。
その後にＢ2セルには次の式を入力して右横方向にオートフィルドラッグします。

=IF(B1="","",DATE(2010,1,1)-WEEKDAY(DATE(2010,1,1))+1+(MIN(Sheet2!$D:$D)-1)*7+(COLUMN(A1)-1)*7)

セルの表示形式は日付にします。

回答No.4

tom04

No.2・3です！
たびたびごめんなさい。

補足の
＞・元シートは加工できません。（私以外の人が更新をかけるため）

の部分を見逃していました。

Sheet1にまったく手を加えられないのであれば、作業用の列をSheet2に設けても同様の結果が得られるはずです。

Sheet2の使っていない列の2行目に
=IF(COUNTIF(Sheet1!$C$2:Sheet1!C2,Sheet1!C2)=1,ROW(),"")

として、オートフィルで下へコピーします。

前回のSheet2のA3セルに入れる数式の範囲指定部分を、このSheet2の列にすればどうでしょうか？
仮にSheet2のH列を作業列としたのであれば
A3セルの数式は

=IF(COUNT(H:H)<ROW(A1),"",INDEX(Sheet1!C:C,SMALL(H:H,ROW(A1))))

となると思います。

どうも何度も失礼しました。m(__)m

お礼

何度も回答頂き本当にありがとうございました。
ベストアンサーに選べず申し訳ありません。

回答No.3

tom04

No.2です！
補足を読ませてもらいました。

希望はSheet1は作業用の列などは設けたくないということですが、
無理やりに数式を作成しても配列数式など、コンピュータに負担をかけるようになると思いますので、
今回の方法も作業用の列を設けています。
↓の画像ではE列にしていますが、他の人が見ることはない！という離れた列に1列作業列を設け
数式を入れてオートフィルでこれ以上データはない！
（Sheet2の数式がSheet1の1000行目まで対応できるようにしていますので、そのくらいまで）
コピーした後に、非表示にしてはどうでしょうか？

Sheet1の作業列E2セルに
=IF(COUNTIF($C$2:C2,C2)=1,ROW(),"")
という数式を入れ、オートフィルで下まで（1000行くらいまで）コピーした後に、この列を非表示にします。

次に、週ごとの集計だということなのですが、基準になる週をいつにするか？
という点でどうして良いか判らないので勝手に画像のような感じにしてみました。

Sheet2の1行目～2行目の間のデータ個数を表示するようにしています。
1行目は日曜日で2行目が土曜日になるはずです。

Sheet2のB1セルに
=MIN(Sheet1!$B:$B)-WEEKDAY(MIN(Sheet1!$B:$B))+1
B2セルに
=IF(B1="","",B1+6)

C1セルに
=IF(B1="","",IF(MAX(Sheet1!$B:$B)<B1+7,"",B1+7))
C2セルに
=IF(C1="","",C1+6)

として、C2・C3セルを範囲指定しC3セルのフィルハンドルで列（右）方向にオートフィルでコピーします。
空白になってもよいのでかなりの列までコピーしておいても構いません。

そして、Sheet2のA3セルに
=IF(COUNT(Sheet1!E:E)<ROW(A1),"",INDEX(Sheet1!C:C,SMALL(Sheet1!E:E,ROW(A1))))

B3セルに
=IF(OR($A3="",COUNTBLANK(B$1:B$2)),"",SUMPRODUCT((Sheet1!$C$1:$C$1000=$A3)*(Sheet1!$B$1:$B$1000>=B$1)*(Sheet1!$B$1:$B$1000<=B$2)))

としてこれもかなり右までオートフィルでコピー！

最後にA3～今コピーした最終列を範囲指定し、最終列のフィルハンドルで下へコピーすると
画像のように、1行目～2行目の日付間のデータ個数が表示されます。

Sheet1のデータが増えたり、変化してもSheet2に反映されるはずです。

この程度の方法しか思いつきませんが
参考になりますかね？m(__)m

回答No.2

tom04

こんばんは！
外していたらごめんなさい。
1ヶ月ごとの集計で良いのですかね？
一応そういうことだとしての一例です。

↓の画像のようにSheet1のデータをSheet2のようにまとめるようにしてみました。
Sheet1に作業用の列を2列設けています。

作業列D2セルに
=IF(COUNTBLANK(A2:C2),"","第"&(WEEKNUM(B2)-WEEKNUM(EOMONTH(B2,-1))+1&"週"))

としてその行の日付がその月の第何週目か？を表示させます。
尚、数式内にある「WEEKNUM関数とEOMONTH関数を使うに当たって、当方使用のExcel2003以前のバージョンでは
メニュー　→　ツール　→　アドイン　→　「分析ツール」にチェックを入れておく必要があります。
この「第○週」の表示は日曜日から土曜日までが同一週となります。

続いてE2セルに
=IF(OR(C2="",COUNTIF($C$2:C2,C2)<>1),"",ROW())

という数式をいれ、D2・E2セルを範囲指定し、D2セルのフィルハンドル（+）でダブルクリック、またはオートフィルで下へずぃ～～～！っとコピーします。

次にSheet2の数式ですが、1行目の　第1週～第5週　はあらかじめ入力されているものとします。

A2セルに
=IF(COUNT(Sheet1!E:E)<ROW(A1),"",INDEX(Sheet1!C:C,SMALL(Sheet1!E:E,ROW(A1))))

B2セルに
=IF($A2="","",SUMPRODUCT((Sheet1!$C$2:$C$1000=$A2)*(Sheet1!$D$2:$D$1000=B$1)))
として最終列のF2セルまでオートフィルでコピー

最後にA2～F2セルを範囲指定し、F2セルのフィルハンドルで下へオートフィルでコピーすると
画像のような感じになります。

以上、参考になればよいのですが、他に良い方法があったり
的外れだったら読み流してくださいね。m(__)m

補足

回答ありがとうございますm(__)m

的確な質問ができず申し訳ありません。
補足させて頂きますと、
・日付は数ヶ月にわたっています
・元シートは加工できません。（私以外の人が更新をかけるため）
・日付が変更になる場合があるので、それが集計先にも反映されるようにしたいのです。

後だしのようになってしまい申し訳ございません！！

回答No.1

aokii

ピボットテーブルを試してみて下さい。

補足

すみませんが、ピボットではなく関数を用いたいのです。