Snasui Excel Forum

DhitiBank wrote:ปล. ได้ความรู้เพิ่มมาจากกรณีนี้ว่า Weeknum ใช้แบบอาร์เรย์ไม่ได้

Weeknum สามารถใช้เป็น Array ได้ครับ ตัวอย่างสูตรการทำ Weeknum เป็น Array ตามด้านล่างครับ

=Sumproduct(Weeknum(Row(Indirect(a2&":"&Max(A:A))))

ขอบคุณครับอาจารย์ จบใน 1 กระบวนท่า

snasui wrote:

DhitiBank wrote:ปล. ได้ความรู้เพิ่มมาจากกรณีนี้ว่า Weeknum ใช้แบบอาร์เรย์ไม่ได้

Weeknum สามารถใช้เป็น Array ได้ครับ ตัวอย่างสูตรการทำ Weeknum เป็น Array ตามด้านล่างครับ

=Sumproduct(Weeknum(Row(Indirect(a2&":"&Max(A:A))))

อ๋อ ต้องใช้ผ่านตัวกลางแบบนี้เอง ขอบพระคุณครับ

snasui wrote: แถมอีกตัวอย่างสูตรครับ

ที่ C2 คีย์

=CHOOSE(COLUMNS($C2:C2),IF($A2>=DATE(YEAR(MAX($A$2:$A$262)),9,20),SUM($B$2:$B2)-SUM(C$1:C1),IF(WEEKDAY($A2)=6,SUM($B$2:$B2)-SUM(C$1:C1),"")),IF(MONTH($A2)<>MONTH($A3),SUM($B$2:$B2)-SUM(C$1:C1),""),IF($A2>=DATE(YEAR(MAX($A$2:$A$262)),9,20),SUM($B$2:$B2),""),IF(LOOKUP(MONTH($A2),{1,4,7,9})<>LOOKUP(MONTH($A3),{1,4,7,9}),SUM($B$2:$B2)-SUM(C$1:C1),""))

Enter > Copy ไปด้านขวาจนถึง F2 > Copy ลงด้านล่าง

ท่านอาจารย์ครับ แบบนี้เยี้ยมมากครับ ขอบคุณท่านมากๆครับ

DhitiBank wrote: C2 คีย์
=IF(AND(MONTH(A2)=9,DAY(A2)>19),SUMPRODUCT(--(DAY(A$2:A2)>19),--(MONTH(A$2:A2)=9),B$2:B2),IF(TEXT(A2,"[$- ]ddd")<>"Fri","",SUMPRODUCT(--(F$2:F2=F2),--(MONTH(A$2:A2)=MONTH(A2)),B$2:B2)))
Enter --> คัดลอกลงล่างครับ

รบกวนอีกครับ เมื่อนำไปใช้จิงแล้ว มีปัญหาอยู่ว่า ในสัปดาห์หนึ่งอาจมีการเคลื่อนไหวมากที่สุดครับ จะมีปัญหาตรงที่การรวมสัปดาห์ไม่ตรงกับความต้องการครับ จะแก้ไขสูตรบนนี้อย่างไรครับ

ส่วนไตรมาสนั้นก็อเหมือนกันครับ
- ไตรมาส หนึ่ง แต่ 1 ตุลาคม - 31 ธันวาคม ของปีที่หนึ่ง
- ไตรมาส สอง แต่ 1 มกราคม - 31 มีนาคม ของปีที่สอง
- ไตรมาส สาม แต่ 1 เมษายน - 30 มิถุนายน ของปีที่สอง
- ไตรมาส สี่ แต่ 1 กรกฎาคม - 30 กันยายน ของปีที่สอง
ดังนั้น ช่วงนั้นต้องสรุปเป็นไตรมาสครับ

ลองเขียนดูบ้างครับ

ลองปรับสูตรที่ C2 เป็น

=CHOOSE(COLUMNS($C2:C2),IF($G3=$G2,"",SUMIF($G$2:$G2,$G2,$B$2:$B2)),IF(MONTH($A2)=MONTH($A3),"",SUMPRODUCT(--(TEXT($A$2:$A2,"mmyy")=TEXT($A2,"mmyy")),$B$2:$B2)),IF(MONTH($A2)=MONTH($A3),"",IF(OR($A3="",LOOKUP(MONTH($A2),{1,4,7,11},{1,2,3,4})<>LOOKUP(MONTH($A3),{1,4,7,11},{1,2,3,4})),SUM($B$2:$B2)-SUM($E$1:$E1))),IF(AND($A2<>"",OR($A3="",DATE(YEAR(MAX($A:$A)),9,25)<$A3)),SUM($B$2:$B2),""))

Enter -> คัดลอกไปถึง F2 แล้วลงล่างครับ

คอลัมน์ G Weeknum ยังเอาไว้อยู่นะครับ

ขออภัยครับ ปรับสูตรนิดหน่อย พอดีย้อนไปอ่านหน้าแรกถึงจำเงื่อนไขต่างๆ ได้ครับ

C2
=CHOOSE(COLUMNS($C2:C2),IF(AND(MONTH($A2)=9,DAY($A2)>19),SUM($B$2:$B2)-SUMIF($A$2:$A2,"<="&DATE(YEAR(MAX($A:$A)),9,19),$B$2:$B2),IF(WEEKNUM($A2)<>WEEKNUM($A3),SUM($B$2:$B2)-SUM($C$1:$C1),"")),IF(OR(AND(DAY($A2)>19,MONTH($A2)=9),MONTH($A2)<>MONTH($A3)),SUMPRODUCT(--(TEXT($A$2:$A2,"mmyy")=TEXT($A2,"mmyy")),$B$2:$B2),""),IF(AND($A2<>"",OR($A3="",LOOKUP(MONTH($A2),{1,4,7,10})<>LOOKUP(MONTH($A3),{1,4,7,10}),AND(DAY($A2)>19,MONTH($A2)=9))),SUM($B$2:$B2)-SUMPRODUCT($B$2:$B2,--(LOOKUP(MONTH($A$2:$A2),{1,4,7,10})<>LOOKUP(MONTH($A2),{1,4,7,10}))),""),IF(AND($A2<>"",OR($A3="",DATE(YEAR(MAX($A:$A)),9,25)<$A3)),SUM($B$2:$B2),""))
Enter -> คัดลอกไปทางขวาและลงล่างครับ

จริงๆ แล้วสูตรไม่ยาว มันเป็นสูตรของแต่ละคอลัมน์ (สัปดาห์, เดือน, ไตรมาสต์, ปี) แค่เอามารวมกันในสูตร Choose เพื่อให้เลือกเวลาคัดลอกไปทางขวาครับ