Snasui Excel Forum

อยากรบกวนสอบถามสูตรเพื่อทำสรุปรายงานในSheet "Formula" โดยดึงข้อมูลจากSheet "CAR" และ Sheet "Sales"
ตามตัวอย่างในเอกสารแนบค่ะ

1.สูตรดึงข้อมูล ปีและรุ่น โดยมี 2 เงื่อนไขที่ต้องตรงกัน
2.สูตรเพื่อแสดงผลรวมยอดขายปี2014-2018 และ Averageราคา โดยมี 2 เงื่อนไขที่ต้องตรงกัน
3.สูตรเพื่อแสดงModelที่มีผลรวมยอดขายปี 2014-2018 มากที่สุด โดยมี 3 เงื่อนไขที่ต้องตรงกัน


ขอบคุณค่ะ

ตัวอย่างสูตรตามด้านล่างครับ

1. ที่ A4 คีย์
   =UNIQUE(FILTER(IF({1,0},CAR!A2:A14,CAR!C2:C14),(CAR!D2:D14=C2)*(CAR!E2:E14=B2)))
   Enter
2. ที่ D2 คีย์
   =SUMIFS(Sales!F2:F500,Sales!E2:E500,A2,Sales!D2:D500,C2,Sales!B2:B500,D1)
   Enter >
3. ที่ E2 คีย์
   =AVERAGEIFS(Sales!G2:G500,Sales!E2:E500,A2,Sales!D2:D500,C2,Sales!B2:B500,D1)
   Enter
4. ที่ F2 คีย์
   =SUMIFS(Sales!F2:F500,Sales!E2:E500,A2,Sales!D2:D500,C2,Sales!B2:B500,F1)
   Enter
5. ที่ G2 คีย์
   =AVERAGEIFS(Sales!G2:G500,Sales!E2:E500,A2,Sales!D2:D500,C2,Sales!B2:B500,F1)
   Enter
6. ที่ D3 คีย์
   =LET(x,SUMIFS(Sales!F2:F500,Sales!B2:B500,D1,Sales!D2:D500,C2,Sales!E2:E500,A2,Sales!C2:C500,UNIQUE(FILTER(Sales!C2:C500,Sales!B2:B500=D1))),y,UNIQUE(FILTER(Sales!C2:C500,Sales!B2:B500=D1)),FILTER(y,x=MAX(x)))
   Enter
7. ที่ F3 คีย์
   =LET(x,SUMIFS(Sales!F2:F500,Sales!B2:B500,F1,Sales!D2:D500,C2,Sales!E2:E500,A2,Sales!C2:C500,UNIQUE(FILTER(Sales!C2:C500,Sales!B2:B500=F1))),y,UNIQUE(FILTER(Sales!C2:C500,Sales!B2:B500=F1)),FILTER(y,x=MAX(x)))
   Enter

A4
=UNIQUE(FILTER(FILTER(CAR!A2:C14,(CAR!D2:D14=C2)*(CAR!E2:E14=B2)),{1,0,1}))

D2
=SUMIFS(Sales!$F$2:$F$500,Sales!$E$2:$E$500,$A2,Sales!$D$2:$D$500,$C2,Sales!$B$2:$B$500,D1)

E2
=AVERAGEIFS(Sales!$G$2:$G$500,Sales!$E$2:$E$500,$A2,Sales!$D$2:$D$500,$C2,Sales!$B$2:$B$500,D1)

Copy D2:E2 ไป F2

D3
=INDEX(UNIQUE(Sales!$C$2:$C$500),MATCH(1,FREQUENCY(0,1/(SUMIFS(Sales!$F$2:$F$500,Sales!$B$2:$B$500,D1,Sales!$D$2:$D$500,$C2,Sales!$E$2:$E$500,$A2,Sales!$C$2:$C$500,UNIQUE(Sales!$C$2:$C$500))+1)),))

Copy ไป F3

ขอบคุณมากนะคะ

ขอสอบถามเพิ่มเติมค่ะ กรณีที่ต้องการหาผลลัพธ์ ไม่รวม Model "TRADE MODEL" ต้องปรับสูตรเป็นอย่างไรคะ?

D2
=SUMIFS(Sales!$F$2:$F$500,Sales!$E$2:$E$500,$A2,Sales!$D$2:$D$500,$C2,Sales!$B$2:$B$500,D1)

E2
=AVERAGEIFS(Sales!$G$2:$G$500,Sales!$E$2:$E$500,$A2,Sales!$D$2:$D$500,$C2,Sales!$B$2:$B$500,D1)

D3
=INDEX(UNIQUE(Sales!$C$2:$C$500),MATCH(1,FREQUENCY(0,1/(SUMIFS(Sales!$F$2:$F$500,Sales!$B$2:$B$500,D1,Sales!$D$2:$D$500,$C2,Sales!$E$2:$E$500,$A2,Sales!$C$2:$C$500,UNIQUE(Sales!$C$2:$C$500))+1)),))

ตัวอย่างสูตรตามด้านล่างครับ

1. ที่ D2 คีย์
   =SUMIFS(Sales!F2:F500,Sales!E2:E500,A2,Sales!D2:D500,C2,Sales!B2:B500,D1,Sales!C2:C500,"<>TRADE MODEL")
   Enter
2. ที่ E2 คีย์
   =AVERAGEIFS(Sales!G2:G500,Sales!E2:E500,A2,Sales!D2:D500,C2,Sales!B2:B500,D1,Sales!C2:C500,"<>TRADE MODEL")
   Enter
3. ที่ D3 คีย์
   =LET(x,SUMIFS(Sales!F2:F500,Sales!B2:B500,D1,Sales!D2:D500,C2,Sales!E2:E500,A2,Sales!C2:C500,UNIQUE(FILTER(Sales!C2:C500,(Sales!B2:B500=D1)*(Sales!C2:C500<>"TRADE MODEL")))),y,UNIQUE(FILTER(Sales!C2:C500,(Sales!B2:B500=D1)*(Sales!C2:C500<>"TRADE MODEL"))),FILTER(y,x=MAX(x)))
   Enter

ขอบคุณมากๆค่ะ

มีคำถามเพิ่มเติมในเอกสารแนบ รบกวนด้วยค่ะ

ตัวอย่างสูตรตามด้านล่างครับ

1. ที่ H2 คีย์
   =SUM(FILTER(Sales!F2:F500,(Sales!E2:E500=A2)*(Sales!D2:D500=C2)*(ISNA(MATCH(Sales!B2:B500,D1:G1,0)))))
   Enter
2. ที่ I2 คีย์
   =AVERAGE(FILTER(Sales!G2:G500,(Sales!E2:E500=A2)*(Sales!D2:D500=C2)*(Sales!C2:C500<>"TRADE MODEL")*(ISNA(MATCH(Sales!B2:B500,D1:G1,0)))))
   Enter
3. ที่ H3 คีย์
   =LET(x,UNIQUE(FILTER(Sales!C2:C500,(Sales!C2:C500<>"TRADE MODEL")*ISNA(MATCH(Sales!B2:B500,D1:G1,0))*(Sales!D2:D500=C2)*(Sales!E2:E500=A2))),y,SUMIFS(Sales!F2:F500,Sales!D2:D500,C2,Sales!E2:E500,A2,Sales!C2:C500,x),FILTER(x,y=MAX(y)))
   Enter

ขอบคุณมากค่ะ

H2
=SUMIFS(Sales!F2:F500,Sales!E2:E500,A2,Sales!D2:D500,C2,Sales!B2:B500,"<>"&D1,Sales!B2:B500,"<>"&F1)

H3
=INDEX(SORT(CHOOSE({1,2},SUMIFS(Sales!F2:F500,Sales!E2:E500,A2,Sales!D2:D500,C2,Sales!B2:B500,"<>"&D1,Sales!B2:B500,"<>"&F1,Sales!C2:C500,"<>"&D3,Sales!C2:C500,UNIQUE(Sales!C2:C500)),UNIQUE(Sales!C2:C500)),1,-1),1,2)

I2
=AVERAGEIFS(Sales!G2:G500,Sales!E2:E500,A2,Sales!D2:D500,C2,Sales!C2:C500,H3)

ขอบคุณมากค่ะ