คีย์รหัสและจำนวน ให้แสดงผลรวมของจำนวน

[wisit]

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

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

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

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

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

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

[bank9597]

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

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

[wisit]

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

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

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

[bank9597]

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

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

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

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

[wisit]

ต้องขอโทษคุณ 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 ไหมครับ

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

[snasui]

แต่ปัญหาของผมคือ สมมุติเราก๊อปปี้ในเซลในคอลัมน์ 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

[wisit]

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

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

[snasui]

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

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

[wisit]

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

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

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

[snasui]

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

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

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

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

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

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

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

[snasui]

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

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

[wisit]

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

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

[bank9597]

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

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

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

ที่เซลล์ 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) คัดลอกลงมา

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

[wisit]

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

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

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

[bank9597]

ขอบคุณท่านอาจารย์และคุณ 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 เวอร์ชั่นเหล่านนี้ถูกพัฒนามาค่อนข้างดีมาก สามารถทำงานได้รวดเร็ว เก็บข้อมูลได้เยอะขึ้นครับ

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

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

[wisit]

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

[wisit]

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

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

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

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

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

[bank9597]

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

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

[wisit]

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

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


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

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

[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)

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