หน้าตาสูตรแบบผู้ดี เห็นแล้วจะได้ไม่ส่ายหัว

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

แทนที่จะสร้างสูตรซ้อนกันยาวเหยียดในเซลล์เดียว ควรหาทางสร้างสูตรที่ใช้คำนวณแต่ละขั้น กระจายลงไปในเซลล์หลายๆเซลล์จะช่วยทำให้เข้าใจว่าสูตรทำงานได้ยังไง สามารถไล่หาที่ไปที่มาได้ง่ายขึ้น แต่ถ้าไม่อยากกระจายเซลล์สูตร ต้องการสร้างสูตรซ้อนกันไว้ในเซลล์เดียวให้หาทางทำสูตรให้เป็นตามภาพนี้

ภาพแรกบนสุดเดิมทีสูตรที่สร้างไว้ติดกันแบบนี้

=IF($K15>=$D$17,$F$17,IF($K15>=$D$16,$F$16,IF($K15>=$D$15,$F$15,$F$14)))

สูตรล่างสุดล่ะ

=INDEX(SMALL(IF(ISNA(MATCH(WEEKDAY(ROW(INDIRECT(PushFrom&":"&PushFrom

+MaxDays))),WeekdayNum,0))*ISNA(MATCH(ROW(INDIRECT(PushFrom&":"&PushFrom

+MaxDays)),SpecialHoliday,0)),ROW(INDIRECT(PushFrom&":"&PushFrom+MaxDays))),

ROW(INDIRECT("1:"&MaxDays))),PushWrkDays,1)

จากแบบเดิมที่ติดกันเป็นพืด ควรจัดการเคาะวรรคแทรก หรืออยากจัดการขึ้นบรรทัดใหม่ให้คลิกลงไปหน้าส่วนนั้นแล้วกดปุ่ม ALT+Enter พร้อมกัน

ลูกศิษย์ตั้งชื่อให้ว่า สูตรแบบผู้ดี้ผู้ดี

ควรแยกแต่ละส่วนออกจากกันตามจังหวะของวงเล็บคู่ที่ตรงกันให้อยู่แนวเดียวกัน หรือแยกแต่ละสูตรให้ขึ้นบรรทัดใหม่ จากนั้นเคาะวรรคให้ห่างกันและมีแนวที่ตรงกันถ้าใช้คำนวณในลำดับเดียวกัน

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

ทางออกที่ดีกว่าคือหันไปสร้างสูตรด้วย VBA ทำเป็น Add-in แล้วเวลาใช้สูตรจะได้เหลือสูตรสั้นๆหน้าตาแบบสูตร VLookup XLookup นั่นแหละครับ

สูตรที่ซ้อนกัน มีชื่อเรียกว่า Mega Formula ซึ่งตอนที่สร้างขึ้นนั้นไม่มีใครหรอกที่สร้างเองได้เลยรวดเดียว แต่จะสร้างสูตรสั้นๆทีละขั้นให้ใช้งานได้ก่อนแบบที่ผมแนะนำให้กระจายกัน พอเห็นว่าสามารถใช้คำนวณร่วมกันได้แล้วจึงค่อยๆลอกสูตรมาวางซ้อนกันแล้วสูตรก็จะยาวขึ้นทีละน้อย ขอยกตัวอย่างง่ายๆให้ดูตามนี้

เซลล์ A1 สร้างสูตร =Sum(B11:B15)

เซลล์ A2 สร้างสูตร =Sum(G1:G34)

เซลล์ A3 สร้างสูตร =A1+A2

ให้ลอกสูตรใน A1 กับ A2 มาใส่ลงไปในสูตรเซลล์ A3 จะได้ =Sum(B11:B15)+Sum(G1:G34)

ถ้าคุณเจอสูตรที่ซ้อนกันยาวเหยียด ถ้ายังไม่เข้าใจที่ไปที่มาว่าสร้างขึ้นมาได้ยังไง แนะนำว่าอย่าลอกไปใช้เด็ดขาด ถ้าหัวหน้าอยากให้แก้ไขแล้วทำไม่ได้เองจะหน้าแตก

สูตรที่ซ้อนกันยาวมากๆนั้น เป็นสูตรที่มีไว้ชม มีไว้ดูว่าเขาเอาสูตรอะไรบ้างมาใช้งานร่วมกันแค่นั้นแหละครับ

=================================

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

.

ลอกมาให้ชมกัน

.

=LET(Wz,SORT(UNIQUE(A8:D17)),E,E8:E17,Ty,UNIQUE(C8:C17),Re,UNIQUE(D8:D17),Mx,C8:C17,Cx,F8:F17,Cd,INDEX(Ty,1),Yh,INDEX(Ty,2),data,TEXTJOIN(",",TRUE,FILTER(Cx,Mx=Cd)),data2,TEXTJOIN(",",TRUE,FILTER(Cx,Mx=Yh)),To,SUMIFS(E,Mx,Cd),Ti,SUMIFS(E,Mx,Yh),G,VSTACK(HSTACK(To,data),HSTACK(Ti,data2)),R,HSTACK(Wz,G),R)

.

=LET(Ty,UNIQUE(C8:C17),Re,UNIQUE(D8:D17),Mx,C8:C17,Cx,F8:F17,Cd,INDEX(Ty,1),Yh,INDEX(Ty,2),data,TEXTJOIN(",",TRUE,FILTER(Cx,Mx=Cd)),data2,TEXTJOIN(",",TRUE,FILTER(Cx,Mx=Yh)),To,SUMIFS(E8:E17,Mx,Cd),Ti,SUMIFS(E8:E17,Mx,Yh),VSTACK(HSTACK(Cd,INDEX(Re,1),To,data),HSTACK(Yh,INDEX(Re,2),Ti,data2)))

.

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

.

เรื่องที่คิดว่าไม่ง่ายที่จะตอบ ควรมาถามที่ fb กลุ่มคนรัก Excel ดีกว่าครับ ติดตามโพสต์ของตัวเองได้ง่าย