Excelでの集計、アウトラインについて

質問

仕事の資料作成で手詰まっています。

作業案件ごとに各作業班の作業日、受注金額、粗利益、粗利益率（％）のExcelデータがあります。

例；Ａ列・・・作業日
　　Ｂ１・・・作業班名称（１班）
　　Ｂ２・・・受注金額
　　Ｃ２・・・粗利益
　　Ｄ２・・・粗利益率（％）
　　Ｅ１・・・作業班名称（２班）
　　Ｅ２・・・受注金額
　　Ｆ２・・・粗利益
　　Ｇ２・・・粗利益率（％）

おのおの、２行目からデータが羅列されていく（同日に複数作業あり）入力されているのですが、ツールバーからの
集計作業では、日付ごとの集計まではできます。ただ、粗利益率までもがSUBTOTAL関数で出てしまいます。

日ごと集計、週間集計、月次集計とデータが欲しいのですが、どうやったらいいのかが分かりません。説明もいまいち
出来ていないかも知れないのですが・・・。どなたかいい方法がありましたら教えて下さい。ＶＢＡでできるなら
それは助かりますし、ファイルメーカーやＡＣＣＥＳＳの領域であれば再考しないといけないですよね。。。

どうぞ宜しく御願い致します！！！

回答No.1

saburo0 · ベストアンサー

このような感じで宜しいしょうか？
「ここから」「ここまで」の間の部分をメモ帳などのテキストエディタへコピー・貼り付けし、「Q1397511.slk」等の名前で保存し、エクセルで開いて確認して見てください。
ここには書きづらいので、こういう形でご了承ください。長くてすみません。
意図と違うものだったり、分からない事等御座いましたら補足願います。
----------ここから--------------
ID;PWXL;N;E
P;PGeneral
P;P0
P;P0.00
P;P#,##0
P;P#,##0.00
P;P#,##0;;\-#,##0
P;P#,##0;;[Red]\-#,##0
P;P#,##0.00;;\-#,##0.00
P;P#,##0.00;;[Red]\-#,##0.00
P;P"\"#,##0;;"\"\-#,##0
P;P"\"#,##0;;[Red]"\"\-#,##0
P;P"\"#,##0.00;;"\"\-#,##0.00
P;P"\"#,##0.00;;[Red]"\"\-#,##0.00
P;P0%
P;P0.00%
P;P0.00E+00
P;P##0.0E+0
P;P#\ ?/?
P;P#\ ??/??
P;Pyyyy/m/d
P;Pd\-mmm\-yy
P;Pd\-mmm
P;Pmmm\-yy
P;Ph:mm\ AM/PM
P;Ph:mm:ss\ AM/PM
P;Ph:mm
P;Ph:mm:ss
P;Pyyyy/m/d\ h:mm
P;Pmm:ss
P;Pmm:ss.0
P;P@
P;P[h]:mm:ss
P;P_ "\"* #,##0_ ;;_ "\"* \-#,##0_ ;;_ "\"* "-"_ ;;_ @_
P;P_ * #,##0_ ;;_ * \-#,##0_ ;;_ * "-"_ ;;_ @_
P;P_ "\"* #,##0.00_ ;;_ "\"* \-#,##0.00_ ;;_ "\"* "-"??_ ;;_ @_
P;P_ * #,##0.00_ ;;_ * \-#,##0.00_ ;;_ * "-"??_ ;;_ @_
P;P\$#,##0_);;\(\$#,##0\)
P;P\$#,##0_);;[Red]\(\$#,##0\)
P;P\$#,##0.00_);;\(\$#,##0.00\)
P;P\$#,##0.00_);;[Red]\(\$#,##0.00\)
P;Pm/d/yy
P;Pyyyy"年"m"月"d"日"
P;Ph"時"mm"分"
P;Ph"時"mm"分"ss"秒"
P;Prr\.m\.d
P;Prr"年"m"月"d"日"
P;Pyyyy"年"m"月"
P;Pm"月"d"日"
P;FＭＳ Ｐゴシック;M220
P;FＭＳ Ｐゴシック;M220
P;FＭＳ Ｐゴシック;M220
P;FＭＳ Ｐゴシック;M220
P;EＭＳ Ｐゴシック;M220
P;EＭＳ Ｐゴシック;M120
F;P0;DG0G8;M270
B;Y12;X29;D0 0 11 28
O;L;D;V0;K47;G100 0.001
F;W1 1 10
F;W2 2 17
F;W6 6 1
F;W10 10 1
F;W14 14 1
F;W18 18 1
F;W22 22 1
F;W26 26 1
F;W30 256 18
F;P19;FG0G;C1
F;P14;F%2G;C5
F;P14;F%2G;C27
F;P14;F%2G;C28
F;P14;F%2G;C29
C;Y1;X1;K"作業日"
C;X2;K"作業班名称（１班）"
C;X3;K"受注金額"
C;X4;K"粗利益"
C;X5;K"粗利益率"
C;X7;K"判定"
C;X11;K"受注金額計算"
C;X15;K"粗利益計算"
C;X19;K"受注金額表示"
C;X23;K"粗利益表示"
C;X27;K"粗利益率表示"
C;Y2;X7;K"日単位"
C;X8;K"週単位"
C;X9;K"月単位"
C;X11;K"日単位"
C;X12;K"週単位"
C;X13;K"月単位"
C;X15;K"日単位"
C;X16;K"週単位"
C;X17;K"月単位"
C;X19;K"日単位"
C;X20;K"週単位"
C;X21;K"月単位"
C;X23;K"日単位"
C;X24;K"週単位"
C;X25;K"月単位"
C;X27;K"日単位"
C;X28;K"週単位"
C;X29;K"月単位"
C;Y3;X1;K38413
C;X2;K"ABC"
C;X3;K8126
C;X4;K93
C;X5;K0.0114447452621216;ERC[-1]/RC[-2]
C;X7;K"20050302";ETEXT(RC[-6],"YYYYMMDD")
C;X8;K5487;EINT(RC[-7]/7)
C;X9;K"200503";ELEFT(RC[-2],6)
C;X11;K8126;EIF(RC[-4]=R[-1]C[-4],RC3+R[-1]C,RC3)
C;X12;K8126;EIF(RC[-4]=R[-1]C[-4],RC3+R[-1]C,RC3)
C;X13;K8126;EIF(RC[-4]=R[-1]C[-4],RC3+R[-1]C,RC3)
C;X15;K93;EIF(RC[-8]=R[-1]C[-8],RC4+R[-1]C,RC4)
C;X16;K93;EIF(RC[-8]=R[-1]C[-8],RC4+R[-1]C,RC4)
C;X17;K93;EIF(RC[-8]=R[-1]C[-8],RC4+R[-1]C,RC4)
C;X19;K"";EIF(R[+1]C[-12]=RC[-12],"",RC[-8])
C;X20;K"";EIF(R[+1]C[-12]=RC[-12],"",RC[-8])
C;X21;K"";EIF(R[+1]C[-12]=RC[-12],"",RC[-8])
C;X23;K"";EIF(R[+1]C[-16]=RC[-16],"",RC[-8])
C;X24;K"";EIF(R[+1]C[-16]=RC[-16],"",RC[-8])
C;X25;K"";EIF(R[+1]C[-16]=RC[-16],"",RC[-8])
C;X27;K"";EIF(RC[-8]="","",RC[-4]/RC[-8])
C;X28;K"";EIF(RC[-8]="","",RC[-4]/RC[-8])
C;X29;K"";EIF(RC[-8]="","",RC[-4]/RC[-8])
C;Y4;X1;K38413
C;X2;K"DDD"
C;X3;K350
C;X4;K72
C;X5;K0.205714285714286;ERC[-1]/RC[-2]
C;X7;K"20050302";ETEXT(RC[-6],"YYYYMMDD")
C;X8;K5487;EINT(RC[-7]/7)
C;X9;K"200503";ELEFT(RC[-2],6)
C;X11;K8476;EIF(RC[-4]=R[-1]C[-4],RC3+R[-1]C,RC3)
C;X12;K8476;EIF(RC[-4]=R[-1]C[-4],RC3+R[-1]C,RC3)
C;X13;K8476;EIF(RC[-4]=R[-1]C[-4],RC3+R[-1]C,RC3)
C;X15;K165;EIF(RC[-8]=R[-1]C[-8],RC4+R[-1]C,RC4)
C;X16;K165;EIF(RC[-8]=R[-1]C[-8],RC4+R[-1]C,RC4)
C;X17;K165;EIF(RC[-8]=R[-1]C[-8],RC4+R[-1]C,RC4)
C;X19;K8476;EIF(R[+1]C[-12]=RC[-12],"",RC[-8])
C;X20;K8476;EIF(R[+1]C[-12]=RC[-12],"",RC[-8])
C;X21;K"";EIF(R[+1]C[-12]=RC[-12],"",RC[-8])
C;X23;K165;EIF(R[+1]C[-16]=RC[-16],"",RC[-8])
C;X24;K165;EIF(R[+1]C[-16]=RC[-16],"",RC[-8])
C;X25;K"";EIF(R[+1]C[-16]=RC[-16],"",RC[-8])
C;X27;K0.019466729589429;EIF(RC[-8]="","",RC[-4]/RC[-8])
C;X28;K0.019466729589429;EIF(RC[-8]="","",RC[-4]/RC[-8])
C;X29;K"";EIF(RC[-8]="","",RC[-4]/RC[-8])
C;Y5;X1;K38416
C;X2;K"EEE"
C;X3;K1702
C;X4;K161
C;X5;K0.0945945945945946;ERC[-1]/RC[-2]
C;X7;K"20050305";ETEXT(RC[-6],"YYYYMMDD")
C;X8;K5488;EINT(RC[-7]/7)
C;X9;K"200503";ELEFT(RC[-2],6)
C;X11;K1702;EIF(RC[-4]=R[-1]C[-4],RC3+R[-1]C[+4],RC3)
C;X12;K1702;EIF(RC[-4]=R[-1]C[-4],RC3+R[-1]C[+4],RC3)
C;X13;K1867;EIF(RC[-4]=R[-1]C[-4],RC3+R[-1]C[+4],RC3)
C;X15;K161;EIF(RC[-8]=R[-1]C[-8],RC4+R[-1]C,RC4)
C;X16;K161;EIF(RC[-8]=R[-1]C[-8],RC4+R[-1]C,RC4)
C;X17;K326;EIF(RC[-8]=R[-1]C[-8],RC4+R[-1]C,RC4)
C;X19;K"";EIF(R[+1]C[-12]=RC[-12],"",RC[-8])
C;X20;K"";EIF(R[+1]C[-12]=RC[-12],"",RC[-8])
C;X21;K"";EIF(R[+1]C[-12]=RC[-12],"",RC[-8])
C;X23;K"";EIF(R[+1]C[-16]=RC[-16],"",RC[-8])
C;X24;K"";EIF(R[+1]C[-16]=RC[-16],"",RC[-8])
C;X25;K"";EIF(R[+1]C[-16]=RC[-16],"",RC[-8])
C;X27;K"";EIF(RC[-8]="","",RC[-4]/RC[-8])
C;X28;K"";EIF(RC[-8]="","",RC[-4]/RC[-8])
C;X29;K"";EIF(RC[-8]="","",RC[-4]/RC[-8])
C;Y6;X1;K38416
C;X2;K"RIS"
C;X3;K4049
C;X4;K70
C;X5;K0.0172882193134107;ERC[-1]/RC[-2]
C;X7;K"20050305";ETEXT(RC[-6],"YYYYMMDD")
C;X8;K5488;EINT(RC[-7]/7)
C;X9;K"200503";ELEFT(RC[-2],6)
C;X11;K5751;EIF(RC[-4]=R[-1]C[-4],RC3+R[-1]C,RC3)
C;X12;K5751;EIF(RC[-4]=R[-1]C[-4],RC3+R[-1]C,RC3)
C;X13;K5916;EIF(RC[-4]=R[-1]C[-4],RC3+R[-1]C,RC3)
C;X15;K231;EIF(RC[-8]=R[-1]C[-8],RC4+R[-1]C,RC4)
C;X16;K231;EIF(RC[-8]=R[-1]C[-8],RC4+R[-1]C,RC4)
C;X17;K396;EIF(RC[-8]=R[-1]C[-8],RC4+R[-1]C,RC4)
C;X19;K"";EIF(R[+1]C[-12]=RC[-12],"",RC[-8])
C;X20;K"";EIF(R[+1]C[-12]=RC[-12],"",RC[-8])
C;X21;K"";EIF(R[+1]C[-12]=RC[-12],"",RC[-8])
C;X23;K"";EIF(R[+1]C[-16]=RC[-16],"",RC[-8])
C;X24;K"";EIF(R[+1]C[-16]=RC[-16],"",RC[-8])
C;X25;K"";EIF(R[+1]C[-16]=RC[-16],"",RC[-8])
C;X27;K"";EIF(RC[-8]="","",RC[-4]/RC[-8])
C;X28;K"";EIF(RC[-8]="","",RC[-4]/RC[-8])
C;X29;K"";EIF(RC[-8]="","",RC[-4]/RC[-8])
C;Y7;X1;K38416
C;X2;K"FJEA"
C;X3;K6040
C;X4;K85
C;X5;K0.0140728476821192;ERC[-1]/RC[-2]
C;X7;K"20050305";ETEXT(RC[-6],"YYYYMMDD")
C;X8;K5488;EINT(RC[-7]/7)
C;X9;K"200503";ELEFT(RC[-2],6)
C;X11;K11791;EIF(RC[-4]=R[-1]C[-4],RC3+R[-1]C,RC3)
C;X12;K11791;EIF(RC[-4]=R[-1]C[-4],RC3+R[-1]C,RC3)
C;X13;K11956;EIF(RC[-4]=R[-1]C[-4],RC3+R[-1]C,RC3)
C;X15;K316;EIF(RC[-8]=R[-1]C[-8],RC4+R[-1]C,RC4)
C;X16;K316;EIF(RC[-8]=R[-1]C[-8],RC4+R[-1]C,RC4)
C;X17;K481;EIF(RC[-8]=R[-1]C[-8],RC4+R[-1]C,RC4)
C;X19;K11791;EIF(R[+1]C[-12]=RC[-12],"",RC[-8])
C;X20;K11791;EIF(R[+1]C[-12]=RC[-12],"",RC[-8])
C;X21;K"";EIF(R[+1]C[-12]=RC[-12],"",RC[-8])
C;X23;K316;EIF(R[+1]C[-16]=RC[-16],"",RC[-8])
C;X24;K316;EIF(R[+1]C[-16]=RC[-16],"",RC[-8])
C;X25;K"";EIF(R[+1]C[-16]=RC[-16],"",RC[-8])
C;X27;K0.0268001017725384;EIF(RC[-8]="","",RC[-4]/RC[-8])
C;X28;K0.0268001017725384;EIF(RC[-8]="","",RC[-4]/RC[-8])
C;X29;K"";EIF(RC[-8]="","",RC[-4]/RC[-8])
C;Y8;X1;K38423
C;X2;K"DJE"
C;X3;K6083
C;X4;K50
C;X5;K0.00821962847279303;ERC[-1]/RC[-2]
C;X7;K"20050312";ETEXT(RC[-6],"YYYYMMDD")
C;X8;K5489;EINT(RC[-7]/7)
C;X9;K"200503";ELEFT(RC[-2],6)
C;X11;K6083;EIF(RC[-4]=R[-1]C[-4],RC3+R[-1]C,RC3)
C;X12;K6083;EIF(RC[-4]=R[-1]C[-4],RC3+R[-1]C,RC3)
C;X13;K18039;EIF(RC[-4]=R[-1]C[-4],RC3+R[-1]C,RC3)
C;X15;K50;EIF(RC[-8]=R[-1]C[-8],RC4+R[-1]C,RC4)
C;X16;K50;EIF(RC[-8]=R[-1]C[-8],RC4+R[-1]C,RC4)
C;X17;K531;EIF(RC[-8]=R[-1]C[-8],RC4+R[-1]C,RC4)
C;X19;K6083;EIF(R[+1]C[-12]=RC[-12],"",RC[-8])
C;X20;K"";EIF(R[+1]C[-12]=RC[-12],"",RC[-8])
C;X21;K"";EIF(R[+1]C[-12]=RC[-12],"",RC[-8])
C;X23;K50;EIF(R[+1]C[-16]=RC[-16],"",RC[-8])
C;X24;K"";EIF(R[+1]C[-16]=RC[-16],"",RC[-8])
C;X25;K"";EIF(R[+1]C[-16]=RC[-16],"",RC[-8])
C;X27;K0.00821962847279303;EIF(RC[-8]="","",RC[-4]/RC[-8])
C;X28;K"";EIF(RC[-8]="","",RC[-4]/RC[-8])
C;X29;K"";EIF(RC[-8]="","",RC[-4]/RC[-8])
C;Y9;X1;K38424
C;X2;K"EJWW"
C;X3;K6201
C;X4;K33
C;X5;K0.00532172230285438;ERC[-1]/RC[-2]
C;X7;K"20050313";ETEXT(RC[-6],"YYYYMMDD")
C;X8;K5489;EINT(RC[-7]/7)
C;X9;K"200503";ELEFT(RC[-2],6)
C;X11;K6201;EIF(RC[-4]=R[-1]C[-4],RC3+R[-1]C,RC3)
C;X12;K12284;EIF(RC[-4]=R[-1]C[-4],RC3+R[-1]C,RC3)
C;X13;K24240;EIF(RC[-4]=R[-1]C[-4],RC3+R[-1]C,RC3)
C;X15;K33;EIF(RC[-8]=R[-1]C[-8],RC4+R[-1]C,RC4)
C;X16;K83;EIF(RC[-8]=R[-1]C[-8],RC4+R[-1]C,RC4)
C;X17;K564;EIF(RC[-8]=R[-1]C[-8],RC4+R[-1]C,RC4)
C;X19;K6201;EIF(R[+1]C[-12]=RC[-12],"",RC[-8])
C;X20;K12284;EIF(R[+1]C[-12]=RC[-12],"",RC[-8])
C;X21;K24240;EIF(R[+1]C[-12]=RC[-12],"",RC[-8])
C;X23;K33;EIF(R[+1]C[-16]=RC[-16],"",RC[-8])
C;X24;K83;EIF(R[+1]C[-16]=RC[-16],"",RC[-8])
C;X25;K564;EIF(R[+1]C[-16]=RC[-16],"",RC[-8])
C;X27;K0.00532172230285438;EIF(RC[-8]="","",RC[-4]/RC[-8])
C;X28;K0.00675675675675676;EIF(RC[-8]="","",RC[-4]/RC[-8])
C;X29;K0.0232673267326733;EIF(RC[-8]="","",RC[-4]/RC[-8])
C;Y10;X1;K38443
C;X2;K"DJAA"
C;X3;K1258
C;X4;K26
C;X5;K0.0206677265500795;ERC[-1]/RC[-2]
C;X7;K"20050401";ETEXT(RC[-6],"YYYYMMDD")
C;X8;K5491;EINT(RC[-7]/7)
C;X9;K"200504";ELEFT(RC[-2],6)
C;X11;K1258;EIF(RC[-4]=R[-1]C[-4],RC3+R[-1]C,RC3)
C;X12;K1258;EIF(RC[-4]=R[-1]C[-4],RC3+R[-1]C,RC3)
C;X13;K1258;EIF(RC[-4]=R[-1]C[-4],RC3+R[-1]C,RC3)
C;X15;K26;EIF(RC[-8]=R[-1]C[-8],RC4+R[-1]C,RC4)
C;X16;K26;EIF(RC[-8]=R[-1]C[-8],RC4+R[-1]C,RC4)
C;X17;K26;EIF(RC[-8]=R[-1]C[-8],RC4+R[-1]C,RC4)
C;X19;K1258;EIF(R[+1]C[-12]=RC[-12],"",RC[-8])
C;X20;K1258;EIF(R[+1]C[-12]=RC[-12],"",RC[-8])
C;X21;K"";EIF(R[+1]C[-12]=RC[-12],"",RC[-8])
C;X23;K26;EIF(R[+1]C[-16]=RC[-16],"",RC[-8])
C;X24;K26;EIF(R[+1]C[-16]=RC[-16],"",RC[-8])
C;X25;K"";EIF(R[+1]C[-16]=RC[-16],"",RC[-8])
C;X27;K0.0206677265500795;EIF(RC[-8]="","",RC[-4]/RC[-8])
C;X28;K0.0206677265500795;EIF(RC[-8]="","",RC[-4]/RC[-8])
C;X29;K"";EIF(RC[-8]="","",RC[-4]/RC[-8])
C;Y11;X1;K38451
C;X2;K"DAA"
C;X3;K1706
C;X4;K26
C;X5;K0.0152403282532239;ERC[-1]/RC[-2]
C;X7;K"20050409";ETEXT(RC[-6],"YYYYMMDD")
C;X8;K5493;EINT(RC[-7]/7)
C;X9;K"200504";ELEFT(RC[-2],6)
C;X11;K1706;EIF(RC[-4]=R[-1]C[-4],RC3+R[-1]C,RC3)
C;X12;K1706;EIF(RC[-4]=R[-1]C[-4],RC3+R[-1]C,RC3)
C;X13;K2964;EIF(RC[-4]=R[-1]C[-4],RC3+R[-1]C,RC3)
C;X15;K26;EIF(RC[-8]=R[-1]C[-8],RC4+R[-1]C,RC4)
C;X16;K26;EIF(RC[-8]=R[-1]C[-8],RC4+R[-1]C,RC4)
C;X17;K52;EIF(RC[-8]=R[-1]C[-8],RC4+R[-1]C,RC4)
C;X19;K1706;EIF(R[+1]C[-12]=RC[-12],"",RC[-8])
C;X20;K"";EIF(R[+1]C[-12]=RC[-12],"",RC[-8])
C;X21;K"";EIF(R[+1]C[-12]=RC[-12],"",RC[-8])
C;X23;K26;EIF(R[+1]C[-16]=RC[-16],"",RC[-8])
C;X24;K"";EIF(R[+1]C[-16]=RC[-16],"",RC[-8])
C;X25;K"";EIF(R[+1]C[-16]=RC[-16],"",RC[-8])
C;X27;K0.0152403282532239;EIF(RC[-8]="","",RC[-4]/RC[-8])
C;X28;K"";EIF(RC[-8]="","",RC[-4]/RC[-8])
C;X29;K"";EIF(RC[-8]="","",RC[-4]/RC[-8])
C;Y12;X1;K38452
C;X2;K"SSS"
C;X3;K6464
C;X4;K26
C;X5;K0.00402227722772277;ERC[-1]/RC[-2]
C;X7;K"20050410";ETEXT(RC[-6],"YYYYMMDD")
C;X8;K5493;EINT(RC[-7]/7)
C;X9;K"200504";ELEFT(RC[-2],6)
C;X11;K6464;EIF(RC[-4]=R[-1]C[-4],RC3+R[-1]C,RC3)
C;X12;K8170;EIF(RC[-4]=R[-1]C[-4],RC3+R[-1]C,RC3)
C;X13;K9428;EIF(RC[-4]=R[-1]C[-4],RC3+R[-1]C,RC3)
C;X15;K26;EIF(RC[-8]=R[-1]C[-8],RC4+R[-1]C,RC4)
C;X16;K52;EIF(RC[-8]=R[-1]C[-8],RC4+R[-1]C,RC4)
C;X17;K78;EIF(RC[-8]=R[-1]C[-8],RC4+R[-1]C,RC4)
C;X19;K6464;EIF(R[+1]C[-12]=RC[-12],"",RC[-8])
C;X20;K8170;EIF(R[+1]C[-12]=RC[-12],"",RC[-8])
C;X21;K9428;EIF(R[+1]C[-12]=RC[-12],"",RC[-8])
C;X23;K26;EIF(R[+1]C[-16]=RC[-16],"",RC[-8])
C;X24;K52;EIF(R[+1]C[-16]=RC[-16],"",RC[-8])
C;X25;K78;EIF(R[+1]C[-16]=RC[-16],"",RC[-8])
C;X27;K0.00402227722772277;EIF(RC[-8]="","",RC[-4]/RC[-8])
C;X28;K0.00636474908200734;EIF(RC[-8]="","",RC[-4]/RC[-8])
C;X29;K0.00827322868052609;EIF(RC[-8]="","",RC[-4]/RC[-8])
E
----------ここまで--------------

お礼

「.slk」のファイル、初めて見ました！とても勉強に
なりました。ありがとうございます。

職場で求められている書類として、週計は月～日で
欲しいようなのですが、Int関数を使って日付データを
シリアル値にして算出すると、どうも曜日がずれてしまい
週番号での週計、というのができませんでした。

ですが、それ以外の書式（数式等）はうまく活用する
事ができました。週計はWeekday関数でも無理みたい
ですね。。。ありがとうございました。また自分でも
「.slk」やExcel、VBAを勉強しなくてはと思います！