Snasui Excel Forum

รบกวนช่วย คิดสูตรการหาการนับค่าที่ติบลบต่อเนื่องที่มากที่สุด และรวมค่าติดลบต่อเนื่อง ด้วยครับ พอดีผมพยายามหาคำตอบแต่พบแบบใกล้เคียงคือหาค่า 0 ต่อเนื่อง แต่ไม่สามารถเปลี่ยนเป็นค่าที่ น้อยกว่า ศูนย์ได้ ครับ รบกวนทุกท่านด้วยครับ

ปรับสูตรเดิมนิดหน่อยครับ

M4 =MAX(FREQUENCY(IF(B2:L2<0,COLUMN(B2:L2)),IF(B2:L2>=0,COLUMN(B2:L2))))

ไม่ได้อะครับ

ลองแนบไฟล์ที่ว่าไม่ได้มาดูหน่อยครับ อ้อ...คำว่า "อ่ะ" ผิดกฎข้อ 1 นะครับ

ขอโทษขอรับ กระผมไม่ถี่ถ้วน

ผมแนวไฟล์มาให้พิจารณาแล้วครับ รบกวนอีกรอบขอรับ

เพราะยังไม่ได้กดแป้นแบบ array ครับ

คลิก m4 แล้วกดแป้น f2 ตามด้วย ctrl+shift และ enter จะได้คำตอบครับ

ได้แล้ว ขอบคุณมากขอรับ

แล้วถ้า จะรวมค่าที่ติดลบติดต่อกันละครับ ต้องแปลงสูตรอย่างไรบ้างครับ

หากหมายถึงข้อมูลมีค่าลบหลายช่วงและให้รวมค่าเฉพาะช่วงที่มีค่าลบที่ติดต่อกันมากที่สุดนั้นต้องอาศัย VBA ของเขียนมาเองก่อน ติดตรงไหนค่อยถามกันต่อครับ

งงกับ Frequency จริงๆ ครับ

สมมติข้อมูล B2:L2 คือ
1, 1, -2, -3, 1, 1, -4, -5, -6, 1, 1

ดูด้วยตาก็ได้คำตอบในใจคือ 3

ลองแกะสูตรทีละสูตรก็ได้ว่า
=MAX(FREQUENCY(IF(B2:L2<0,COLUMN(B2:L2)),IF(B2:L2>=0,COLUMN(B2:L2))))
=MAX([color=#FF0000]FREQUENCY({FALSE,FALSE,4,5,FALSE,FALSE,8,9,10,FALSE,FALSE},{2,3,FALSE,FALSE,6,7,FALSE,FALSE,FALSE,11,12})[/color])

แล้วก็ลากคลุม Frequency จากนั้นกด F9 แล้วก็ได้
=MAX([color=#FF0000]{0;0;2;0;3;0;0}[/color])

อยากรู้ว่า เรามาถึงจุดนี้ได้ยังไงเนี่ย... มันหาความถี่ยังไงถึงได้แบบนั้นครับ T_T

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

DhitiBank wrote:งงกับ Frequency จริงๆ ครับ

สมมติข้อมูล B2:L2 คือ
1, 1, -2, -3, 1, 1, -4, -5, -6, 1, 1

ดูด้วยตาก็ได้คำตอบในใจคือ 3

ลองแกะสูตรทีละสูตรก็ได้ว่า
=MAX(FREQUENCY(IF(B2:L2<0,COLUMN(B2:L2)),IF(B2:L2>=0,COLUMN(B2:L2))))
=MAX([color=#FF0000]FREQUENCY({FALSE,FALSE,4,5,FALSE,FALSE,8,9,10,FALSE,FALSE},{2,3,FALSE,FALSE,6,7,FALSE,FALSE,FALSE,11,12})[/color])

แล้วก็ลากคลุม Frequency จากนั้นกด F9 แล้วก็ได้
=MAX([color=#FF0000]{0;0;2;0;3;0;0}[/color])

อยากรู้ว่า เรามาถึงจุดนี้ได้ยังไงเนี่ย... มันหาความถี่ยังไงถึงได้แบบนั้นครับ T_T

การนับความถี่จะดูที่ตัว bins คือส่วนประกอบตัวหลังของ Frequency โดยไม่คำนึงค่า False ครับ

สำหรับส่วนประกอบหลังเมื่อไม่รวมค่า False จะเหลือเพียง {2,3,6,7,11,12} จำนวนสมาชิกของผลลัพธ์จะมากกว่า bins ไปอีก 1 จำนวน โดยมีหลักการคิดดังนี้

จาก Array แรกคือ {FALSE,FALSE,4,5,FALSE,FALSE,8,9,10,FALSE,FALSE} เทียบกับ {2,3,6,7,11,12}

1. มีค่าที่น้อยกว่าหรือเท่ากับ 2 กี่จำนวน
2. มีค่าที่มากกว่า 2 แต่น้อยกว่าหรือเท่ากับ 3 กี่จำนวน
3. มีค่าที่มากกว่า 3 แต่น้อยกว่าหรือเท่ากับ 6 กี่จำนวน
4. มีค่าที่มากกว่า 6 แต่น้อยกว่าหรือเท่ากับ 7 กี่จำนวน
5. มีค่าที่มากกว่า 7 แต่น้อยกว่าหรือเท่ากับ 11 กี่จำนวน
6. มีค่าที่มากกว่า 11 แต่น้อยกว่าหรือเท่ากับ 12 กี่จำนวน
7. มีค่าที่มากกว่า 12 กี่จำนวน

คำตอบจึงได้เป็น {0;0;2;0;3;0;0}

ขอบคุณอาจารย์มากๆ ครับ เข้าใจชัดเจนเลยครับ

ake9842 wrote:แล้วถ้า จะรวมค่าที่ติดลบติดต่อกันละครับ ต้องแปลงสูตรอย่างไรบ้างครับ

พออ่านคำอธิบาย ผมได้ไอเดียอย่างหนึ่ง ลองแบบนี้ครับ

สมมติว่าข้อมูลอยู่ใน B2:L2 เหมือนเดิมนะครับ

1. 1. ที่ B3 แสดงผลการนับช่วงที่เป็นลบที่ติดต่อกัน ณ คอลัมน์นั้นๆ คีย์
   =MAX(FREQUENCY(IF($A2:B2<0,COLUMN($A2:B2)),IF($B$2:$L$2>=0,COLUMN($B$2:$L$2))))
   กด Ctrl+Shift ค้าง แล้ว Enter --> คัดลอกไปถึง L3

• 2. ที่ M2 หาว่าช่วงที่เป็นลบติดต่อกันมากที่สุดนั้น ติดกันกี่คอลัมน์ คีย์
  =MAX(B3:L3)
  Enter

• 3. ที่ M3 หาว่าช่วงที่ติดลบติดต่อกันมากที่สุดอยู่คอลัมน์ไหนคีย์
  =MIN(IF(B3:L3=M2,COLUMN(B3:L3)-COLUMN(B3)+1))
  กด Ctrl+Shift ค้างแล้วกด Enter

• 4. ที่ M4 หาผลรวมของช่วงที่ติดลบติดต่อกันมากที่สุด คีย์
  =SUM(OFFSET(B2,0,M3-M2,1,M2))
  Enter

แต่ข้อจำกัดคือ หากมีช่วงติดลบที่ติดต่อกันมากที่สุดมากกว่า 1 ช่วง สูตรจะเลือกช่วงที่เจอก่อนมาหาผลรวมครับ

snasui wrote: หากหมายถึงข้อมูลมีค่าลบหลายช่วงและให้รวมค่าเฉพาะช่วงที่มีค่าลบที่ติดต่อกันมากที่สุดนั้นต้องอาศัย VBA ของเขียนมาเองก่อน ติดตรงไหนค่อยถามกันต่อครับ

อีกตัวอย่างสูตรสำหรับการรวมค่าที่ติดลบที่ติดต่อกันจำนวนมากที่สุด (โดยไม่สนใจว่ามูลค่ามากที่สุดหรือไม่) เป็นอันดับแรกครับ โดยที่ M4 เป็นสูตรสำหรับนับจำนวนที่ติดลบต่อเนื่องกันมากที่สุด

เซลล์ M5 คีย์

=SUM(OFFSET(B4,0,MATCH(M4,COUNTIF(OFFSET(B4,0,COLUMN(B4:L4)-COLUMN(B4),1,M4),"<0"),0)-1,1,M4))

Ctrl+Shift+Enter

ขอบคุณมากครับ ท่านอาจารย์ แต่ผมขอรบกวนอีกเรื่องหนึ่ง ถ้าเปลี่ยนจากแนวนอนเป็นแนวตั้ง สูตรจะเปลี่ยนแปลงไปอย่างไรบ้าง ผมลองฝึกอ่านสูตร ดูแล้วแต่ยังไม่แตกฉาน รบกวนอาจารย์อีกครั้งด้วยครับ

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

ขอโทษด้วยครับ ที่ไม่ได้แนบไฟล์มา ครั้วนี้แนบไฟล์มาใหม่แล้วครับ รบกวนด้วยครับ ผมยังไม่แตกฉานในการใช้สูตรต่างๆ
พอดีพยายามทดลองแล้วแต่ยังไม่ประสบความสำเร็จ รบกรวนอาจารย์ทุกท่านอีกครั้งครับ

ลองดูนะครับ

B15 =SUM(OFFSET(B6,MATCH(B14,COUNTIF(OFFSET(B6,ROW(B6:B13)-ROW(B6),0,B14),"<0"),0)-1,0,B14))

กด 3 แป้น คือ ctrl+shift+enter แป้นเพื่อทำเป็น array ครับ

ขอบคุณมากครับ ที่แนะนำและแบ่งปัน