Snasui Excel Forum

เรียนอาจารย์และผู้รู้ครับ

ผมมีปัญหาในการที่จะคำนวณสูตร โดยสูตรที่ผมคำนวณนั้น ถ้าเราคีย์รหัสในคอลัมน์ C (วอลเปเปอร์) กำหนดฟอร์แมทให้เป็น Text และคีย์จำนวนเงินในคอลัมน์ D กำหนดฟอร์แมทให้เป็น Number

ผมจะใช้สูตร SUMIF โดยให้รวมรหัสในคอลัมน์ C (วอลเปเปอร์) ของลูกค้าในแต่ละเจ้า โดยรวมในแต่ละบิล แต่ปัญหาของผมคือ เมื่อเราคีย์ข้อมูลแล้วเราก๊อปปี้ ซึ่งบางครั้งก๊อปปี้ผิดช่อง ทำให้เราคีย์จำนวนเงินในคอลัมน์ D จากกำหนดฟอร์แมทให้เป็น Number กลายเป็น Text เมื่อเราคีย์จำนวนเงินเข้าไป สูตรจะไม่คำนวณให้เนื่องจากเป็น Text

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

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

ผมได้แนบไฟล์ตัวอย่าง ซึ่งจริง ๆ แล้วมีรายชื่อลูกค้ามากกว่านี้ และมีรหัสสินค้ามากกว่านี้

ควรระบุถึงตำแหน่งเซลล์ที่ต้องการให้แสดงคำตอบและ แสดงตัวอย่างคำตอบมาให้ดูด้วยครับ

จะได้รวดเร็วในการคิดสูตรครับ

คุณ bank9597 ครับ ผมได้แนบไฟล์ และคำตอบที่ต้องการในไฟล์เรียบร้อยแล้วครับ

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

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

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

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

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

ผมไม่กล้าตอบในกรณีที่ยังไม่ทราบปัญหาที่ชัดเจนครับ

ต้องขอโทษคุณ bank9597 ด้วยครับ
คือ เมื่อผมคีย์รหัสสินค้าวอลเปเปอร์ในคอลัมน์ C3:C14991 และคีย์จำนวนเงินในคอลัมน์ D3:D14991 คีย์รหัสผ้าม่านในคอลัมน์ F3:F14991 และคีย์จำนวนเงินในคอลัมน์ G3:G14991 (กำหนดตามสูตรให้คำนวณถึงแถวที่ 14991 ครับ เพราะ จริง ๆ แล้วคีย์ข้อมูลไม่น่าจะถึง 14000 แต่เผื่อไว้ก่อนครับ) ดังตัวอย่างในไฟล์

เมื่อผมคีย์รายชื่อลูกค้าชื่อโอ๋ในคอลัมน์ A ส่วนบิลใบที่คอลัมน์ B ซึ่งอาจจะมี 1 ใบ 2 ใบ จนถึง 50 ใบ เป็นต้น โดยลูกค้าที่ชื่อโอ๋ ผมได้กำหนดอักษรย่อให้เป็นโอ (O) โดยในไฟล์เมื่อผมคีย์รหัส และจำนวนเงิน (ตามไฟล์ที่แนบ) ผมต้องการทราบว่าลูกค้าชื่อโอ๋ บิลที่ 1O รวมเป็นเงินเท่าไหร่ ซึ่งผมได้คำนวณโดยใช้สูตร SUMIF ทำให้ได้ผลลัพธ์ของลูกค้าชื่อโอ๋ บิลที่ 1O เป็นจำนวนเงิน 1,800 บาท

แต่ปัญหาของผมคือ สมมุติเราก๊อปปี้ในเซลในคอลัมน์ C3:C11 และ D3:D11 เพื่อนำไปวางไว้ในคอลัมน์ F3:F11 และ G3:G11 (ไฟล์จริงมีรหัสสินค้าและคอลัมน์มากกว่านี้ ซึ่งการคีย์รหัสสินค้าบางตัวใช้รหัสเหมือนกัน ซึ่งลูกค้าจะสั่งเหมือนกัน แต่จำนวนเงินอาจจะต่างกัน) แต่เราวางผิดคอลัมน์ โดยนำไปวางไว้ในคอลัมน์ G3:G11 และ H3:H11

ทำให้คอลัมน์ G3:G11 ซึ่งเป็นจำนวนเงินจากที่กำหนดดฟอร์แมทให้เป็น Number กลายเป็น Text เมื่อเราลบข้อมูลและคีย์จำนวนเงินเข้าไป สูตรจะไม่คำนวณในคอลัมน์ G3:G11 ให้เนื่องจากเป็น Text พอจะมีวิธีที่ทำให้คำนวณโดยไม่ต้องไปแก้ไขฟอร์แมทให้เป็น Number ไหมครับ

แต่อย่างได้สูตรที่ระบุข้างต้นมากกว่าครับ

wisit wrote:แต่ปัญหาของผมคือ สมมุติเราก๊อปปี้ในเซลในคอลัมน์ C3:C11 และ D3:D11 เพื่อนำไปวางไว้ในคอลัมน์ F3:F11 และ G3:G11 (ไฟล์จริงมีรหัสสินค้าและคอลัมน์มากกว่านี้ ซึ่งการคีย์รหัสสินค้าบางตัวใช้รหัสเหมือนกัน ซึ่งลูกค้าจะสั่งเหมือนกัน แต่จำนวนเงินอาจจะต่างกัน) แต่เราวางผิดคอลัมน์ โดยนำไปวางไว้ในคอลัมน์ G3:G11 และ H3:H11

ทำให้คอลัมน์ G3:G11 ซึ่งเป็นจำนวนเงินจากที่กำหนดดฟอร์แมทให้เป็น Number กลายเป็น Text เมื่อเราลบข้อมูลและคีย์จำนวนเงินเข้าไป สูตรจะไม่คำนวณในคอลัมน์ G3:G11 ให้เนื่องจากเป็น Text พอจะมีวิธีที่ทำให้คำนวณโดยไม่ต้องไปแก้ไขฟอร์แมทให้เป็น Number ไหมครับ

แต่อย่างได้สูตรที่ระบุข้างต้นมากกว่าครับ

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

ที่ X13 คีย์

=SUMPRODUCT(--($J$3:$J$12143=O3),$G$3:$G$12143+0)

Enter

ขอบคุณมากครับอาจารย์ ผมขอรบกวนอีกนิดครับ

คือ เราสามารถใช้สูตรไหนที่จะทำให้คิดจำนวนเงินออกมา โดยเราต้องคีย์รหัสสินค้า และจำนวนเงิน โปรแกรมถึงจะคำนวณจำนวนเงินออกมาให้ หากคีย์เฉพาะจำนวนเงินอย่างเดียว ไม่คีย์รหัสสินค้า สูตรจะไม่คำนวณจำนวนเงินให้

ใช้สูตร If เข้ามาช่วยตรวจสอบครับ เช่น

=If(A1<>"",Sumproduct(...),"")

อาจารย์ครับเมื่อผมให้สูตร

=IF($C$3:$C$12143<>"",SUMPRODUCT(--($J$3:$J$12143=O3),$D$3:$D$12143+0))

โปรแกรมคำนวณให้แต่เมื่อเราก๊อปปี้ลงด้านล่าง จะขึ้นคำว่า FALSE และเมื่อคีย์ค่าบิลเช่น 2N โปรแกรมก็จะไม่คำนวณจำนวนเงินให้ และถ้าไม่ได้คีย์รหัสสินค้า โปรแกรมก็ยังคำนวณจำนวนเงินให้อยู่ดีครับ

wisit wrote:อาจารย์ครับเมื่อผมให้สูตร

=IF($C$3:$C$12143<>"",SUMPRODUCT(--($J$3:$J$12143=O3),$D$3:$D$12143+0))

โปรแกรมคำนวณให้แต่เมื่อเราก๊อปปี้ลงด้านล่าง จะขึ้นคำว่า FALSE และเมื่อคีย์ค่าบิลเช่น 2N โปรแกรมก็จะไม่คำนวณจำนวนเงินให้ และถ้าไม่ได้คีย์รหัสสินค้า โปรแกรมก็ยังคำนวณจำนวนเงินให้อยู่ดีครับ

snasui wrote: ใช้สูตร If เข้ามาช่วยตรวจสอบครับ เช่น

=If(A1<>"",Sumproduct(...),"")

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

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

wisit wrote: =IF($C$3:$C$12143<>"",SUMPRODUCT(--($J$3:$J$12143=O3),$D$3:$D$12143+0))

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

อาจารย์ครับผมได้แนบไฟล์แล้วครับ และได้แก้ไขสูตรโดยอ้างเซลใดเซลหนึ่ง แต่ผมสงสัยว่าเราคีย์ในแต่ละบิลบางบิลก็มีมากกว่า 20 เซล บางบิลก็ประมาณ 10 เซล แต่ผมลองอ้างเซลใดเซลหนึ่ง โปรแกรมก็คำนวณได้จำนวนเงินตรงครับ แต่รหัสสินค้าผ้าม่านกลับคำนวณไม่ได้ครับ และถ้าไม่ใส่รหัสสินค้า โปรแกรมก็ยังคำนวณจำนวนเงินให้อยู่ดีครับ

สงสัยสูตรจะผิด แต่ผมไม่รู้จะแก้ไขอย่างไรดีครับ รบกวนอาจารย์ด้วยครับ

ไปทำธุระมา กลับมาเลยได้ช่วยนิดหน่อย หวังว่าคงทันเวลาครับ

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

ส่วนที่ติดปัญหาอยู่ ลองแกะสูตรนี้นำไปใช้ดูครับ

ที่เซลล์ W13 คีย์ =SUM(IF($J$3:$J$12143=O3,IF($C$3:$C$12143<>"",$D$3:$D$12143+0,""))) กด Ctrl+Shift+Enter คัดลอกลงมา
ที่เซลล์ X13 คีย์ =SUM(IF($J$3:$J$12143=O3,IF($F$3:$F$12143<>"",$G$3:$G$12143+0,""))) กด Ctrl+Shift+Enter คัดลอกลงมา

หรือ ปรับจากสูตรเดิม

ที่เซลล์ W13 คีย์ =SUMPRODUCT(--($J$3:$J$12143=O3),--($C$3:$C$12143<>""),$D$3:$D$12143+0) คัดลอกลงมา
ที่เซลล์ X13 คีย์ =SUMPRODUCT(--($J$3:$J$12143=O3),--($F$3:$F$12143<>""),$G$3:$G$12143+0) คัดลอกลงมา

ส่วนอื่นๆ ลองปรับสูตรไปใช้ดูครับ

ขอบคุณท่านอาจารย์และคุณ bank9597 มากครับ ทำได้แล้วครับ

มีเรื่องรบกวนถามนิดนึงครับ สูตร
=SUMPRODUCT(--($J$3:$J$12143=O3),--($C$3:$C$12143<>""),$D$3:$D$12143+0)

มีผลทำให้เครื่องทำงานช้าไหมครับ

wisit wrote:ขอบคุณท่านอาจารย์และคุณ bank9597 มากครับ ทำได้แล้วครับ

มีเรื่องรบกวนถามนิดนึงครับ สูตร
=SUMPRODUCT(--($J$3:$J$12143=O3),--($C$3:$C$12143<>""),$D$3:$D$12143+0)

มีผลทำให้เครื่องทำงานช้าไหมครับ

ผมเคยถามอาจารย์คนควนในคำถามประมาณนี้ คำตอบคือ อยู่ที่จำนวนของข้อมูล และสูตรที่ใช้งาน หากเป็นสูตรอาร์เรย์ เช่น
=SUM(IF($J$3:$J$12143=O3,IF($C$3:$C$12143<>"",$D$3:$D$12143+0,""))) ใช้กับข้อมูลปริมาณมากๆ ก็จะทำให้ช้า

ในกรณีใช้สูตร
=SUMPRODUCT(--($J$3:$J$12143=O3),--($C$3:$C$12143<>""),$D$3:$D$12143+0) ก็จะเร็วกว่าสูตรแรก แต่เนื่องจากสูตรต้องแปลง Text ให้เป็น number โดยการ +0 เข้าไป ก็จะทำให้ช้าลงไปอีกครับ


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

วิธีแก้คือการสร้าง rangname แบบไดนามิค จะช่วยได้เยอะ และก็ต้องมีวิธีการสร้าง rangeName อีกว่า สร้างอย่างไรจึงจะไม่ทำให้โปรแกรมทำงานช้า


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

ต่อมาเวอร์ชั่น ควรเป็นเวอร์ชั่นที่ใหม่ เช่น 2007-2010 เวอร์ชั่นเหล่านนี้ถูกพัฒนามาค่อนข้างดีมาก สามารถทำงานได้รวดเร็ว เก็บข้อมูลได้เยอะขึ้นครับ

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

รอดูท่านอื่นๆมาให้ความเห็นอีกครับ

ขอบคุณมากครับคุณ bank9597 สำหรับคำแนะนำที่ดี ๆ ทำให้ผมได้รับความรู้เพิ่มมากขึ้น ขอบคุณมากครับ

เรียนอาจารย์และคุณ bank9597

ผมได้ลองนำสูตร =SUMPRODUCT(--($J$3:$J$12143=O3),--($C$3:$C$12143<>""),$D$3:$D$12143+0)
ไปใช้กับงานจริงของผม (ตอนแรกลองในไฟล์ตัวอย่างใช้ได้)

ผมนำสูตรไปลองกับลูกค้าคนแรก (ลูกค้าอีก 18 คนยังไม่ได้แทนสูตรลงไป)ปรากฎว่าตอนแรกโปรแกรมมันก็รันลูกค้าคนแรกได้ตรงตามที่ต้องการ แต่พอทำสูตรจนครบทั้งหมด 19 รายชื่อ พอลองนำข้อมูลที่คีย์เมื่อเดือนที่แล้ว วางลงในโปรแกรมปรากฎว่าสูตรข้างบนนั้นมันขึ้นคำว่า #VALUE! เหมือนกับว่าเราใช้ Argument หรือ Operator ผิดรูปแบบ ทั้งที่ผมก็ระบุตำแหน่งถูกต้องตามสูตรข้างบน

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

รบกวนอาจารย์ คุณ bank9597 และผู้รู้ช่วยหาสูตรที่ไม่ทำให้โปรแกรมทำงานช้าหน่อยครับ (ถ้าไม่ได้ก็ไม่เป็นไรครับ )

ลองแนบไฟล์มาดูครับ

เนื่องจากไม่ใช้ VBA ผมจึงสามารถช่วยตอบให้ได้ โดยจะไม่ผิดกฏที่อาจารย์ได้กำหนดไว้ครับ

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

พอมีวิธีที่จะใช้สูตรอื่นที่ทำให้เครื่องทำงานได้เร็วไหมครับ ส่วนวิธีที่คุณ bank9597 แนะนำ คือ rangeName ผมไม่รู้ว่าคืออะไร


ส่วนตัวอยากได้สูตรที่ทำให้การคีย์ข้อมูลและการคำนวณเร็วขึ้น ซึ่งไฟล์ที่แนบมานั้นผมได้ลบไป 1 ชีท (ชีทที่ลบคือ ชีทที่รวมยอดของชีทแต่ละคน มารวมเป็นเงินทั้งหมดแค่นั้น ซึ่งผมคิดว่าไม่น่าจะมีผลต่อการทำงาน เพราะใช้แค่สูตร SUM อย่างเดียว แต่แค่ข้ามชีทเท่านั้น

รบกวนอาจารย์ คุณ bank9597 และผู้รู้ช่วยด้วยครับ

กฏข้อนึงที่คุณควรรู้ในการใช้สูตรคือ หากคุณกำหนดช่วงเซลล์ไว้เท่าไหร่ คุณก็ควรกำหนดไว้เท่านั้นทั้งหมดในสูตร ยกตัวอย่าง
=SUMPRODUCT(--($S$3:$S$12143=U3),--($F$3:$F$11909<>""),$G$3:$G$11909+0)
ควรปรับเป็น
=SUMPRODUCT(--($S$3:$S$11909=U3),--($F$3:$F$11909<>""),$G$3:$G$11909+0)

สูตรถึงจะจะคำนวนค่าออกมาได้