เพื่อมุ่งให้เกิดคุณภาพจากการอบรมสูงสุด Excel Expert Training ให้การอบรม Excel กลุ่มเล็กๆ ไม่เกิน 6 คนทุกคนสามารถเรียนรู้ Excel อย่างใกล้ชิด จะมาคนเดียวหรือมาเป็นกลุ่มแล้วนัดวันอบรมแบบส่วนตัวก็ได้ ผู้เข้าอบรมทุกคนสามารถติดตามเนื้อหาที่อบรมได้อย่างชัดเจนจากจอภาพด้านหน้าของตัวเอง
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 25

Thread: สูตร excel หรือ function ที่ใช้เกี่ยวกับการหาวันที่ต้องวางบิลกับลูกค้า

  1. #11
    วานิชย์
    Guest
    [quote]
    Quote Originally Posted by อรวีร์ View Post
    อรวีร์ขอเสนอเพิ่มอีกสูตรหนึ่งแทนที่ F19 เดิมเป็น . . .
    =(C19-WEEKDAY(C19+5)+7)+MOD(CEILING(DAY(C19-WEEKDAY(C19+5)+7),7)/7,2)*7

    แล้ว Copy ลงมาถึง F22
    ซึ่งสูตรใหม่นี้จะสั้นกว่าเดิม แต่ให้ผลลัพธ์เหมือนเดิม คือ ได้ผลเป็นจันทร์ที่ 2 หรือจันทร์ที่ 4
    ถ้าเกินจันทร์ที่ 4 ก็ขยับไปอีก 2 สัปดาห์(ซึ่งถือว่ายังไม่ถูกต้อง เพราะบางเดือนมีวันจันทร์ 5 วัน เช่นเดือน 6 ปี 2008)

    ถ้าจะทำให้สมบูรณ์ต้องแก้ไขอีกหน่อยค่ะ รอท่านอื่นช่วยแก้ไขเพิ่มเติม หรือจะมีสูตรใหม่อื่น . . .
    เพิ่มเติมสูตรใหม่เพื่อแก้ไขกรณีบางเดือนมีวันจันทร์ 5 วันครับ

    ดังนี้

    ที่ F19

    {=SMALL(IF(MOD(INT((DAY(C19-WEEKDAY(C19+5)+{7,14,21})-1)/7)+1,2)=0,C19-WEEKDAY(C19+5)+{7,14,21},9999999),1) }

    ช่วยทดสอบด้วยนะครับ

  2. #12
    gaka
    Guest
    อันนี้เป็นของน้องออฟฟิตเก่าครับ สูตรยาวกว่า (ยิ่งสั้นยิ่งดีสิ)

  3. #13
    อรวีร์
    Guest

    Smile

    ทดสอบสูตรของคุณวานิชย์แล้ว ได้ผลถูกต้องค่ะ



  4. #14
    ssr_thai
    Guest

    กรณี invoice หลังวันจันทร์ที่ 4 ของเดือน

    ปัญหานี้น่าสนุกมาก ขออนุญาติร่วมศึกษาด้วยคน (แก้ไขกรณีมี 5 สัปดาห์แล้วครับ)

  5. #15
    สมเกียรติ
    Guest
    สูตรคุณอรวีร์กับคุณวานิชย์ จะปรับตรงไหนครับให้เราสามารถเปลี่ยนจากวันจันทร์เป็นวันอื่นได้ ตรงเลข 5 ใช่ไหมเอ่ย

    อยากทราบแนวความคิดด้วยครับว่าใช้หลักการอย่างไร ทำไมถึงทำงานได้โดยไม่ต้องใช้ IF ซ้อนเข้าไป

    =(C19-WEEKDAY(C19+5)+7)+MOD(CEILING(DAY(C19-WEEKDAY(C19+5)+7),7)/7,2)*7

    {=SMALL(IF(MOD(INT((DAY(C19-WEEKDAY(C19+5)+{7,14,21})-1)/7)+1,2)=0,C19-WEEKDAY(C19+5)+{7,14,21},9999999),1) }

  6. #16
    สมเกียรติ
    Guest
    ขอยกปัญหาการปัดตัวเลขขึ้นไปหาค่าสูงในช่วง DataRange ที่กำหนดครับว่าจะใช้สูตรอะไรดี ยกตัวอย่างเช่น

    DataRange มีเลขเรียงจากน้อยไปมาก 0;7;14;21;28

    ถ้าเลขที่ต้องการปัดขึ้น คือ 14 ให้ตอบ 14 (ไม่ปัดเพราะตรง)
    ถ้าเลขที่ต้องการปัดขึ้น คือ 15 ให้ตอบ 21
    ถ้าเลขที่ต้องการปัดขึ้น คือ 20 ให้ตอบ 21
    ถ้าเลขที่ต้องการปัดขึ้น คือ 21 ให้ตอบ 21 (ไม่ปัดเพราะตรง)
    ถ้าเลขที่ต้องการปัดขึ้น คือ 25 ให้ตอบ 28

    ผมคิดออกตอนนี้แค่สูตรปัดลง ให้ใช้
    =Lookup(Number,DataRange)

    สูตรปัดขึ้นที่คิดได้ตอนนี้ได้แต่ใช้ Array อีกแล้ว ซึ่งไม่อยากใช้เลยเพราะคิดช้าและสูตรยาว
    {=Min(If(DataRange>=Number,DataRange))}

    ถ้าได้วิธีคิดการปัดขึ้นจะได้นำมาใช้ปัดไปหาวันถึงกำหนดได้ครับ

    ขอแนบแฟ้มที่คิดอีกวิธีมาด้วย วันนี้ไปสอนมากำลังมึนไปหมด ขอบคุณครับ

  7. #17
    อรวีร์
    Guest

    Smile

    ใช่ค่ะ ถ้าจะเปลี่ยนจากวันจันทร์เป็นวันอื่น ปรับตรง +5 โดยบวกด้วย (7-Weekdayของวันที่ต้องการ) หรือลบด้วย Weekdayของวันที่ต้องการ

    หรือเปลี่ยนจาก . . .
    =(C19-WEEKDAY(C19+5)+7)+MOD(CEILING(DAY(C19-WEEKDAY(C19+5)+7),7)/7,2)*7

    เป็น . . .
    =(C19-WEEKDAY(C19-D9)+7)+MOD(CEILING(DAY(C19-WEEKDAY(C19-D9)+7),7)/7,2)*7
    ถ้า D9 เก็บเลข 1=วันอาทิตย์, 2=วันจันทร์, . . .

    อรวีร์ของอธิบายเสริมบางเรื่องดังนี้ค่ะ
    เนื่องจากระบบวันที่ของ Excel ใช้การนับเป็นเลขจำนวนวันจากวันอ้างอิง(1/1/1900)
    ทำให้เราสามารถใช้การบวก,ลบ,คูณ,หาร และใช้ Function กลุ่ม Math.มากระทำกับวันที่ได้ ยกตัวอย่างบางสูตร เช่น

    =MOD( C19 - 1, 7 ) + 1 จะเท่ากับ . . .
    =Weekday( C19 )
    ____________________________

    =C19 - Day( C19 ) จะได้ผลเป็นวันที่สุดท้ายของเดือนก่อนวันที่ใน C19
    ____________________________

    =C19 - Weekday( C19 ) จะได้ผลลัพธ์เป็นวันเสาร์ที่ก่อนวันที่ใน C19
    =C19 - Weekday( C19 - 1 ) จะได้ผลลัพธ์เป็นวันอาทิตย์ที่ก่อนวันที่ใน C19
    =C19 - Weekday( C19 - 2 ) จะได้ผลลัพธ์เป็นวันจันทร์ที่ก่อนวันที่ใน C19
    . . . . . . .
    =C19 - Weekday( C19 - 6 ) จะได้ผลลัพธ์เป็นวันศุกร์ที่ก่อนวันที่ใน C19
    =C19 - Weekday( C19 - 7 ) จะได้ผลลัพธ์เป็นวันเสาร์ที่ก่อนวันที่ใน C19 ซึ่งเหมือน - 0
    ____________________________

    =C19 - Weekday( C19 - 2 ) +7 จะได้ผลลัพธ์เป็นวันจันทร์ที่ถัดจากวันที่ใน C19
    ____________________________

    วันที่ 1 - 7 ไม่ว่าจะเป็นวันอะไร จะถือเป็นวัน___แรกของเดือน
    วันที่ 8 -14 ไม่ว่าจะเป็นวันอะไร จะถือเป็นวัน___ที่สองของเดือน

    =Ceiling( Day(A1) , 7 )/7 ถ้า A1 เป็นวันจันทร์ที่ จะได้ผลลัพธ์เป็นตัวเลขที่บอกว่าเป็นวันจันทร์ที่เท่าไรของเดือน
    เช่นถ้า A1=26/05/2008 ซึ่งเป็นวันจันทร์ สูตร =Ceiling( Day(A1) , 7 )/7 จะได้เป็น 4 หมายความว่าวันที่ 26/05/2008 เป็นวันจันทร์ที่ 4 ของเดือน
    ____________________________

    คงต้องเรียนเชิญอาจารย์วานิชย์มาอธิบายต่อค่ะ

  8. #18
    วานิชย์
    Guest
    {=SMALL(IF(MOD(INT((DAY(C19-WEEKDAY(C19+$G$15)+{7,14,21})-1)/7)+1,2)=0,C19-WEEKDAY(C19+$G$15)+{7,14,21},9999999),1)}

    สรุปลำดับขั้นตอนของการสร้างสูตร

    1.หาวันจันทร์ถัดไปจากวันที่ระบุ 3 สัปดาห์

    2.วันจันทร์ที่ได้ 3 ลำดับนั้นเป็นวันจันทร์ที่เท่าใดในเดือนนั้น ๆ

    3.หาว่าวันจันทร์ใดคือวันจันทร์ ที่ 2 หรือ 4 ของเดือน

    4.นำวันจันทร์ที่ 2 หรือ 4 ที่ถัดไปเพียง 1 สัปดาห์ มาแสดง


    ----------------------------------------------------------------------
    อธิบายแต่ละขั้นตอน
    1.หาวันจันทร์ถัดไปจากวันที่ระบุ 3 สัปดาห์
    ขอเริ่มจากสูตรชั้นในสุดนะครับ

    C19-WEEKDAY(C19+5)+7 --->เพื่อหาวันจันทร์ลำดับถัดไปของวันที่ระบุ (C19) ซึ่งการหาวันในสัปดาห์นั้น คุณอรวีร์ได้อธิบายไว้ก่อนหน้านี้แล้วครับ

    หรือเพื่อให้ง่ายและสะดวกในการปรับวัน ก็สามารถเปลี่ยนไปใช้

    C19-WEEKDAY(C19-D9)+7 ก็ได้ครับ
    โดยที่ ถ้า D9 เก็บเลข 1=วันอาทิตย์, 2=วันจันทร์, ...

    ส่วนตัวเลข {7,14,21} ที่นำมาบวกต่อท้ายนั้นเพื่อหาวันจันทร์ในลำดับต่อไปอีก 3 สัปดาห์ถัดจากวันที่ระบุ

    ดังนั้น จะได้สูตรเป็น

    C19-WEEKDAY(C19+5)+{7,14,21} ----------------------------- (1)

    สรุป สูตร (1) นี้จะเป็นการหาวันจันทร์ที่ถัดไปจากวันที่ระบุอีก 3 จันทร์ (ซึ่งในเดือนหนึ่ง ๆ ถ้าหยิบวันจันทร์ที่ต่อกันมา 3 วัน จะต้องมีจันทร์ที่ 2 หรือ 4 อยู่ใน 3 จันทร์นี้แน่นอน)

    ----------------------------------------------------------------------
    2.วันจันทร์ที่ได้ 3 ลำดับนั้นเป็นวันจันทร์ที่เท่าใดในเดือนนั้น ๆ

    สูตรในการหาว่าวันที่ระบุนั้นอยู่ในสัปดาห์ใด ในเดือน (แบบนับ 7 วันเต็ม 7วันแรกคือสัปดาห์ที่ 1)

    ก็จะมีวิธีหาได้หลากหลายครับ อาทิเช่น

    =INT(DAY(วันที่-1)/7)+1 ---------------------------(2)

    =CEILING( DAY(วันที่), 7 )/7 --------->(2.1)-(คุณอรวีร์ใช้สูตรนี้)

    =ROUNDUP(DAY(วันที่)/7,0) ------------->(2.2)

    ดังนั้นในการหาว่าวันจันทร์ 3 ลำดับที่ได้จากสูตร (1) เป็นวันจันทร์ที่เท่าใดในเดือนนั้น ๆ ก็นำ สูตร (1) มาใช้ในสูตร (2) ดังนี้

    =INT(DAY(C19-WEEKDAY(C19+5)+{7,14,21} -1)/7)+1 ---------------------(2.3)

    (อาจจะนำสูตร (1) ไปใช้ใน ( 2.1) หรือใน (2.2) ก็ได้)

    ผลลัพธ์ที่ได้จากสูตรนี้ก็จะเป็นสัปดาห์ในเดือนนั้นและเดือนต่อไป(ถ้ามี) 3 ลำดับ
    เช่น
    ={5,1,2} ------------------ ถ้าวันที่ระบุตกอยู่ในสัปดาห์ที่ 4 หรือ 5

    ={4,1,2} ------------------ ถ้าวันที่ระบุตกอยู่ในสัปดาห์ที่ 3 หรือ 4

    ={2,3,4} ------------------ ถ้าวันที่ระบุตกอยู่ในสัปดาห์ที่ 1 หรือ 2

    เป็นต้น
    ----------------------------------------------------------------------
    3.หาว่าวันจันทร์ใดคือวันจันทร์ ที่ 2 หรือ 4 ของเดือน

    เพื่อหาวันจันทร์ที่ 2 หรือ 4 ของเดือน จะใช้ Function MOD ในการหา

    =MOD(สูตรที่ 2.3,2) ----------------- ใช้เลข 2 เป็น divisor เนื่องจาก สัปดาห์ที่ต้องการเป็นเลขคู่ ประกอบกับจำนวนสัปดาห์สูงสุดที่มีได้ไม่เกิน 5

    ดังนั้นเมื่อนำสูตรตั้งแต่ข้อ 2 ถึง 3 มาใช้ร่วมกันใน IF ก็จะได้เป็น

    =IF(ตรวจสอบว่าเป็นวันจันทร์ที่ 2 หรือ 4,วันจันทร์ที่ 2 หรือ 4,9999999) ----------- (3)
    หรือ
    =IF(MOD(INT((DAY(C19-WEEKDAY(C19+$G$15)+{7,14,21})-1)/7)+1,2)=0,C19-WEEKDAY(C19+$G$15)+{7,14,21},9999999)

    รูปแบบผลลัพธ์จากสูตรในขั้นตอนนี้จะเป็นแบบนี้

    ={39790,9999999,39804}

    ={9999999,39461,9999999}

    ={39804,9999999,9999999}

    ----------------------------------------------------------------------
    4.นำวันจันทร์ที่ 2 หรือ 4 ที่ถัดไปเพียง 1 สัปดาห์ มาแสดง

    ผลลัพธ์ที่ได้จาก สูตร 3 จะได้วันจันทร์ที่ 2 หรือ 4 แล้ว แต่รวมอยู่กับเลข 9999999 ใน Array

    ดังนั้นจึงต้องใช้ Function SMALL เพื่อนำวันจันทร์ที่ต้องการมาแสดง ซึ่งก็คือวันวางบิลที่ต้องการครับ

    {=SMALL(IF(MOD(INT((DAY(C19-WEEKDAY(C19+$G$15)+{7,14,21})-1)/7)+1,2)=0,C19-WEEKDAY(C19+$G$15)+{7,14,21},9999999),1)}

    -----------------------------------X-----------------------------------

    ขอบคุณคุณอรวีร์ที่ช่วยทดสอบสูตรและช่วยอธิบายนำครับ

  9. #19
    วานิชย์
    Guest
    Quote Originally Posted by สมเกียรติ View Post
    ขอยกปัญหาการปัดตัวเลขขึ้นไปหาค่าสูงในช่วง DataRange ที่กำหนดครับว่าจะใช้สูตรอะไรดี ยกตัวอย่างเช่น

    DataRange มีเลขเรียงจากน้อยไปมาก 0;7;14;21;28

    ถ้าเลขที่ต้องการปัดขึ้น คือ 14 ให้ตอบ 14 (ไม่ปัดเพราะตรง)
    ถ้าเลขที่ต้องการปัดขึ้น คือ 15 ให้ตอบ 21
    ถ้าเลขที่ต้องการปัดขึ้น คือ 20 ให้ตอบ 21
    ถ้าเลขที่ต้องการปัดขึ้น คือ 21 ให้ตอบ 21 (ไม่ปัดเพราะตรง)
    ถ้าเลขที่ต้องการปัดขึ้น คือ 25 ให้ตอบ 28

    ผมคิดออกตอนนี้แค่สูตรปัดลง ให้ใช้
    =Lookup(Number,DataRange)

    สูตรปัดขึ้นที่คิดได้ตอนนี้ได้แต่ใช้ Array อีกแล้ว ซึ่งไม่อยากใช้เลยเพราะคิดช้าและสูตรยาว
    {=Min(If(DataRange>=Number,DataRange))}

    ถ้าได้วิธีคิดการปัดขึ้นจะได้นำมาใช้ปัดไปหาวันถึงกำหนดได้ครับ
    กรณีที่ 1
    ถ้าข้อมูลใน DataRange เป็นข้อมูลที่มีช่วงห่าง(interval) เท่ากันทุกช่วงลำดับและเริ่มจาก 0 หรือเลขทวีคูณของ interval ก็สามารถใช้สูตร

    =CEILING(Number,interval)

    ได้ครับ

    ตามตัวอย่าง ถ้า DataRange มีเลขเรียงจากน้อยไปมาก 0;7;14;21;28
    ก็อาจจะใช้เป็น

    =CEILING(Number,7)

    ได้เลยครับ

    --------------------------------------------------------
    กรณีที่ 2
    ถ้าข้อมูลใน DataRange เป็นข้อมูลที่มีช่วงห่าง(interval) เท่ากันทุกช่วง ตัวแรกเริ่มจากตัวเลขใด ๆ จะใช้

    =LOOKUP(Number+interval-0.0001,DataRange)

    ------------------------------------------------------
    กรณีที่ 3
    แต่ถ้าข้อมูลใน DataRange เป็นข้อมูลใดๆ ตอนนี้ ยังหาสูตรอื่นไม่ได้นอกจาก

    {=Min(If(DataRange>=Number,DataRange))}

    ที่ อ.สมเกียรติ ได้ยกขึ้นมาแล้วครับ

    ที่เกือบจะได้ก็เป็นการใช้

    =INDEX(DataRange,MATCH(Number,DataRange)+1)

    แต่การใช้ INDEX+MATCH นี้ก็จะใช้ไม่ได้กับตัวเลขที่มากสุดและตัวเลขที่ตรงใน DataRange ครับ

  10. #20
    อรวีร์
    Guest

    Smile

    อรวีร์เอาสูตรของอาจารย์วานิชย์
    =INDEX(DataRange,MATCH(Number,DataRange)+1)
    มาแก้นิดเป็น . . .
    =INDEX(DataRange,MATCH(Number-0.0000001,DataRange)+1)

    ลบด้วยเลขน้อยๆก็เอามาจากอาจารย์วานิชย์เช่นกัน

    แต่ยังมีปัญหาที่ตัวแรกสุด เลยต้องแก้สูตรเป็น . . .
    =IF(Number<=DataRange , DataRange , INDEX(DataRange,MATCH(Number-0.0000001,DataRange)+1) )

Similar Threads

  1. Replies: 2
    Last Post: 20 Mar 2008, 15:34
  2. Replies: 4
    Last Post: 28 Jan 2008, 23:15
  3. excel เกิด error เวลา copy สูตร hyperlink
    By khomee in forum Excel Expert Forum Library 2012 - 2007
    Replies: 4
    Last Post: 14 Nov 2007, 14:29
  4. Replies: 2
    Last Post: 14 Aug 2007, 18:23

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •