Snasui Excel Forum

ขอคำแนะนำหน่อยค่ะ..

ตามไฟล์แนบ
สีเหลืองคือข้อมูลตัวอย่าง
สีฟ้า คือ เซลที่ต้องการคำตอบ

คำตอบที่ต้องการคือ ยอดขายรวมของพนักงานแต่ละคน ในปี และเดือนนั้น ๆ ตามช่องที่ระบุไว้ค่ะ

ไม่ทราบว่าจะต้องเขียนสูตรอย่างไรค่ะ

ที่ G3 คีย์
=SUMPRODUCT($C$4:$C$58,--(--($A$4:$A$58)=G$2),--($D$4:$D$58=$F3),--(--($B$4:$B$58)=55))
คัดลอกถึงคอลัมน์ I และคัดลอกลง

ที่ J3 คีย์
=SUMPRODUCT($C$4:$C$58,--(--($A$4:$A$58)=J$2),--($D$4:$D$58=$F3),--(--($B$4:$B$58)=INDEX({56,57},INT((COLUMNS($J3:J3)-1)/12)+1)))
คัดลอกไปทางขวาจนสุดตารางแล้วคัดลอกลงครับ

แล้วจัดความกว้างคอลัมน์ให้พอดีกับข้อมูลในเซลล์ครับ

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

้เรียน คุณ DhitiBank

ไม่ทราบว่าอธิบายสูตรให้หน่อยได้ไหมค่ะ

ถ้าอยากเขียนสูตรแบบนี้เป็นบ้างต้องทำอย่างไรค่ะ..
ขอคำแนะนำหน่อยค่ะ

... แล้วถ้าพนักงานขายมากกว่านี้ล่ะค่ะ ต้องแก้สูตรอย่างไรค่ะ

impim wrote: อธิบายสูตรให้หน่อยได้ไหมค่ะ

ได้ครับ สูตร Sumproduct นำมาใช้หาผลรวมกรณีมีหลายเงื่อนไขได้ (หรือถ้าจำไม่ผิดหากเป็น excel 2007 ขึ้นไปก็ใช้ Sumifs ได้ แต่จะไปเปิดใน 2003 แล้วจะ error ก็เลยแนะนำ sumproduct)
ลักษณะการทำงานคือ สูตรนี้จะนำเลขในช่วงแต่ละช่วง มาคูณในตำแหน่งเดียวกัน พอคูณเสร็จแล้วก็จะเอามาบวกกัน ผลบวกจะเป็นคำตอบ เช่น
=SUMPRODUCT({2,2,2},{0,1,2}) = SUMPRODUCT({2x0,2x1,2x2}) = SUMPRODUCT({0,2,4}) = (0+2+4) = 6

แบบนี้เลยเอามาใช้ประโยชน์ได้กรณีมีเงื่อนไขหลายๆ อย่าง เช่น จากสูตรด้านบน
=SUMPRODUCT($C$4:$C$58,--(--($A$4:$A$58)=G$2),--($D$4:$D$58=$F3),--(--($B$4:$B$58)=55))
เงื่อนไขของคุณคือ เดือน รหัสพนักงาน และ ปี

ที่ใส่สีไว้คือเงื่อนไขที่ต้องตรวจสอบครับ เช่น
--(--($A$4:$A$58)=G$2)
เอาเดือนในคอลัมน์ A มาตรวจว่าเท่ากับเลขเดือนใน G2 หรือไม่
เหตุที่ต้องมี "--" อยู่หน้าช่วงในคอลัมน์ A เนื่องจากในช่วงดังกล่าวมีทั้งตัวเลขในรูปแบบข้อความ และตัวเลขที่เป็นตัวเลขจริงๆ ดังนั้นจึงใส่ "--" ไว้ด้านหน้าเพื่อแปลงตัวเลขในรูปแบบข้อความ ให้กลายเป็นตัวเลขที่เอามาบวกลบคูณหารได้ครับ ("--" หมายถึง การคูณด้วย -1 สองครั้ง ลบลบคูณกันได้บวก) พอตรวจเสร็จก็จะได้ True, False สมมติว่า
--({True,False}) พอเจอ "--" ข้างหน้า True False ก็จะกลายเป็น 1 และ 0 นั้นคือ หลังจากเทียบแล้ว ตรงเงื่อนไขก็จะเป็น 1 ไม่ตรงก็ 0
เงื่อนไขทุกข้อจะถูกตรวจสอบในลักษณะนี้ หากมีข้อมูลแถวไหนตรงเงื่อนไขหมด ก็จะได้ 1 ในตำแหน่งเดียวกันทั้งหมด พอคูณกันก็จะได้ 1 ไม่ว่าเลขอะไรคูณกับ 1 ก็จะได้ตัวมันเอง นั่นคือยอดขายที่อยู่ในเงื่อนไขซ้ายสุดครับ

impim wrote: ถ้าอยากเขียนสูตรแบบนี้เป็นบ้างต้องทำอย่างไรค่ะ..
ขอคำแนะนำหน่อยค่ะ

ถ้าอยากเขียนได้ก็ต้องพยายามทำความเข้าใจสูตรครับ จากนั้นก็ลองเขียนดู ลองศึกษาเพิ่มเติมในบอร์ดนี้ก็ได้โดยค้นหาจากเมนูด้านบน (ใส่ชื่อสูตรลงไปเลยก็ได้ แล้วเลือกกระทู้ที่มีสูตรสั้นๆ ก่อนเพื่อจะเข้าใจง่าย) แล้วก็เทคนิคการแกะสูตรคือ
ลากคลุม --> F9 --> ESC
หรือ
เลือกเซลล์ที่มีสูตร --> Formula --> Evaluate formula
จะช่วยคุณได้ครับ

impim wrote:... แล้วถ้าพนักงานขายมากกว่านี้ล่ะค่ะ ต้องแก้สูตรอย่างไรค่ะ

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

อ่านคำอธิบายสูตรแล้ว ขอบอกตรง ๆ นะคะว่ายังงงอยู่ คงต้องใช้เวลาทำความเข้าใจสักหน่อยค่ะ
แต่ขอบคุณมากนะคะ

มีอีกสูตรหนึ่งซึ่งอยากได้คำอธิบายด้วยค่ะ..ได้สูตรจากอ.snasui ค่ะ แต่ถามไปนานมากแล้ว ซึ่งตอนแรกไม่กล้าขอคำอธิบายสูตรค่ะ
ไม่ทราบถามคุณ DhitiBank ได้หรือไม่ค่ะ หรือให้กลับไปที่คำถามนั้นค่ะ

ให้กลับไปถามที่คำถามนั้นครับ

มาช่วยเสริมในเชิงลึกครับ

DhitiBank wrote:("--" หมายถึง การคูณด้วย -1 สองครั้ง ลบลบคูณกันได้บวก)

=--True เป็นการกลับเครื่องหมายค่า True 2 ครั้ง และหากเป็น

=-True เป็นการกลับเครื่องหมายค่า True ครั้งเดียว

=--True ได้ค่าเป็น 1 เนื่องจาก True สามารถแปลงเป็น 1 ได้ หรือจะพูดว่า True = 1 ก็ย่อมได้ แต่การเท่ากันนั้นต้องผ่านการแปลง ตัวแปลงคือตัวดำเนินการใด ๆ เช่น +, -, *, / ไปกระทำกับ True เสียก่อน เช่น =True+0 ก็จะได้ 1 เช่นกัน

สำหรับ -- ที่ใส่ไว้ด้านหน้าแม้ความหมายหรือผลลัพธ์ที่ได้จะเหมือนกับการคูณด้วย -1 สองครั้ง แต่ในการคำนวณของ Excel ไม่ได้คำนวณเหมือนกัน การกลับเครื่องหมายจะทำงานได้เร็วกว่าครับ

=True+0 สามารถทำงานได้เร็วใกล้เคียงกับ =--True แต่ความเร็วในการสัมผัสคีย์บอร์ดอาจจะต่างกัน เพราะถ้าเทียบการสัมผัสแป้นพิมพ์ การคีย์ +0 กับการคีย์ -- นั้น การคีย์ -- จะเร็วกว่าเพราะเป็นการกดแค่แป้นเดียว

ขอบคุณ อ.snasui ค่ะ

เรียน คุณ DhitiBank ค่ะ

รบกวนช่วยแก้สูตรช่วงเซลสีเขียวให้หน่อยค่ะ .. คำตอบไม่ถูกค่ะ

เซลล์ H8 ปรับสูตรเป็นด้านล่างครับ

=SUMIFS($C$4:$C$58,$A$4:$A$58,$G8,$B$4:$B$58,LOOKUP(2,1/($F$8:$F8<>""),$F$8:$F8),$D$4:$D$58,H$7)

เรียน อ. snasui

ถ้าสูตรแบบเดิมใช้ไม่ได้หรือค่ะ ..

เนื่องจากเห็นว่าในปี 55 กับ 56 คำตอบถูกนี่ค่ะ

ผมไม่ได้ตรวจสอบว่าสูตรเดิมนั้นผิดพลาดตรงไหน อย่างไร

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

แต่หากต้องการใช้ Sumproduct สามารถใช้สูตรที่ H8 ตามด้านล่างครับ

=SUMPRODUCT(--($A$4:$A$58&""=$G8&""),--($B$4:$B$58&""=LOOKUP(2,1/($F$8:$F8<>""),$F$8:$F8)&""),--($D$4:$D$58=H$7),$C$4:$C$58)

ขอบพระคุณมากครับอาจารย์ ได้ความรู้ใหม่อีกแล้ว
ที่แท้ -- เป็นการกลับค่านี่เอง พิมพ์ง่ายด้วย แถมคำนวณไวอีก ยอดครับ
แล้วก็... ไม่รู้ว่าจะเรียกอะไรครับ แบบนี้ &""=blablabla&"" เพิ่งเคยเห็นครับ

DhitiBank wrote:แบบนี้ &""=blablabla&"" เพิ่งเคยเห็นครับ

เป็นการเชื่อมด้วยค่าว่างเข้าไปเพื่อทำให้เป็น Text เพื่อเพิ่มความกระชับ ลดการกลับเครื่องหมายหลาย ๆ รอบครับ

อ๋อ แบบนี้นี่เอง ขอบคุณมากครับ

เรียน อ.snasuiค่ะ

สูตร SUMIFS ที่อาจารย์เขียนให้ รบกวนอาจาย์ช่วยอธิบาย
$B$4:$B$58,LOOKUP(2,1/($F$8:$F8<>"")ให้หน่อยได้ไหมค่ะ
คือเคยเรียนแต่พื้นฐาน EXCEL ค่ะ

ส่วนสูตร SUMPRODUCT ขอใช้เวลาทำความเข้าใจก่อนนะคะ

Lookup ลักษณะนั้น ผมแจ้ง Link ให้ไปศึกษามาแล้วตามกระทู้ก่อนหน้านี้ ได้เข้าไปศึกษาแล้วยังครับ

ส่วน Sumifs ให้ศึกษาจาก Lnk นี้ครับ https://support.office.com/th-th/articl ... h-TH&ad=TH

ต้องศึกษา Sumifs ให้เข้าใจก่อนว่าใช้ทำอะไร แล้วค่อยไปศึกษา Lookup ติดแล้วค่อยถามกันอีกรอบครับ