Page 1 of 1
สูตรรวมที่มีเงื่อนไข
Posted: Wed May 20, 2015 11:28 am
by วังวู ช่ง
อยากรบกวนถามสูตรรวมที่มีเงื่อนไขครับ
ภาก=ตัวเลขทางหน้าของเลขบัญชี สองตัวเลขครับ
ต้องการเขียนสูตรตรงที่ D3, E3 และ F3 ครับ ให้รวมจำนวนเงินใน Column B ครับ
มีข้อมูลมาพร้อมนี้ครับ
ขอบคุณล่วงหน้าครับ
Re: สูตรรวมที่มีเงื่อนไข
Posted: Wed May 20, 2015 12:03 pm
by DhitiBank
ลองแบบนี้ครับ
ที่ D3 คีย์
=SUM(IF(LEFT($A$2:$A$147,2)=D$2&"",$B$2:$B$147))
Ctrl+Shift+Enter
แล้วคัดลอกไปทางขวาครับ
Re: สูตรรวมที่มีเงื่อนไข
Posted: Wed May 20, 2015 12:18 pm
by วังวู ช่ง
DhitiBank wrote:ลองแบบนี้ครับ
ที่ D3 คีย์
=SUM(IF(LEFT($A$2:$A$147,2)=D$2&"",$B$2:$B$147))
Ctrl+Shift+Enter
แล้วคัดลอกไปทางขวาครับ
ขอบคุณท่านมากครับ ผ่านมาผมใช้แบบนี้ครับ แต่ทำให้เครื่องทำงานช้ามากครับ ผมคิดว่าเป็นนำสูตรนี้เป็นสูตร array จึ่งทำให้เครื่องช้า หรือ ไงครับ
Re: สูตรรวมที่มีเงื่อนไข
Posted: Wed May 20, 2015 12:27 pm
by DhitiBank
เป็นสูตรอาร์เรย์ครับ โดยปกติหากมีแค่ 3 สูตรแค่นี้ไม่ควรทำให้เครื่องช้านะครับ นอกจากข้อมูลจะมีจำนวนมากจริงๆ หรือมีการใช้สูตรทำนองนี้เป็นจำนวนมาก
หากต้องการเปลี่ยนวิธี ลองส่งแบบฟอร์มที่ทำอยู่พร้อมตัวอย่างสักเล็กน้อยมาให้ดูได้ไหมครับ จะได้ช่วยหาวิธีที่ทำคำนวณได้เร็วขึ้น (หรือว่าที่ส่งมาด้านบนคือที่ใช้งานอยู่ตอนนี้?)
หรือลองปรับสูตรเป็นแบบนี้ครับ
=SUMPRODUCT(--(LEFT($A$2:$A$147,2)=D$2&""),$B$2:$B$147)
Enter
ผมไม่แน่ใจว่า ความเร็วในการคำนวณของสูตรนี้ กับสูตรก่อนหน้านี้จะแตกต่างกันอย่างไรนะครับ หากมีข้อมูลจำนวนมากๆ ก็ลองนำมาเทียบกันดูครับ
Re: สูตรรวมที่มีเงื่อนไข
Posted: Wed May 20, 2015 12:38 pm
by วังวู ช่ง
ข้อมูลจริงๆครับ ต่างเล็กหน้อยตามนี้ครับ เพราะผ่านมาผมทำแบบนี้เองเลยครับ
Re: สูตรรวมที่มีเงื่อนไข
Posted: Wed May 20, 2015 12:42 pm
by DhitiBank
ลองแบบนี้ครับ (คงต้องเอาคอลัมน์มาช่วย)
- 1. เซลล์ C1 พิมพ์ Criteria
2. C2 คีย์
=left(A2,2)+0
Enter แล้วคัดลอกลงจนถึงแถวสุดท้ายที่มีข้อมูล
3. เลือก C2 จนถึง Cxxx (xxx คือเซลล์สุดท้ายในคอลัมน์ C ที่มีข้อมูล) กดคัดลอก แล้ววางที่ C2 เลือกวางเฉพาะค่า
4. D3 คีย์
=SUMIF($C$2:$C$147,D$2,$B$2:$B$147)
Enter แล้วคัดลอกไปทางขวาครับ
ปรับตรงสีแดงให้ตรงกับข้อมูลจริงด้วยครับ
Re: สูตรรวมที่มีเงื่อนไข
Posted: Wed May 20, 2015 1:19 pm
by วังวู ช่ง
DhitiBank wrote:ลองแบบนี้ครับ (คงต้องเอาคอลัมน์มาช่วย)
- 1. เซลล์ C1 พิมพ์ Criteria
2. C2 คีย์
=left(A2,2)+0
Enter แล้วคัดลอกลงจนถึงแถวสุดท้ายที่มีข้อมูล
3. เลือก C2 จนถึง Cxxx (xxx คือเซลล์สุดท้ายในคอลัมน์ C ที่มีข้อมูล) กดคัดลอก แล้ววางที่ C2 เลือกวางเฉพาะค่า
4. D3 คีย์
=SUMIF($C$2:$C$147,D$2,$B$2:$B$147)
Enter แล้วคัดลอกไปทางขวาครับ
ปรับตรงสีแดงให้ตรงกับข้อมูลจริงด้วยครับ
ครับ ขอบคุณท่านมากครับที่ให้ความช่วยผมครับ
Re: สูตรรวมที่มีเงื่อนไข
Posted: Wed May 20, 2015 2:00 pm
by snasui
DhitiBank wrote:..ผมไม่แน่ใจว่า ความเร็วในการคำนวณของสูตรนี้ กับสูตรก่อนหน้านี้จะแตกต่างกันอย่างไรนะครับ หากมีข้อมูลจำนวนมากๆ ก็ลองนำมาเทียบกันดูครับ
Sumproduct คำนวณเร็วกว่า Sum แบบ Array ครับ
Re: สูตรรวมที่มีเงื่อนไข
Posted: Wed May 20, 2015 2:06 pm
by DhitiBank
snasui wrote:DhitiBank wrote:..ผมไม่แน่ใจว่า ความเร็วในการคำนวณของสูตรนี้ กับสูตรก่อนหน้านี้จะแตกต่างกันอย่างไรนะครับ หากมีข้อมูลจำนวนมากๆ ก็ลองนำมาเทียบกันดูครับ
Sumproduct คำนวณเร็วกว่า Sum แบบ Array ครับ
ขอบคุณครับอาจารย์
Re: สูตรรวมที่มีเงื่อนไข
Posted: Wed May 20, 2015 2:20 pm
by วังวู ช่ง
snasui wrote:DhitiBank wrote:..ผมไม่แน่ใจว่า ความเร็วในการคำนวณของสูตรนี้ กับสูตรก่อนหน้านี้จะแตกต่างกันอย่างไรนะครับ หากมีข้อมูลจำนวนมากๆ ก็ลองนำมาเทียบกันดูครับ
Sumproduct คำนวณเร็วกว่า Sum แบบ Array ครับ
ลืมไปแล้วท่านอาจารย์ ใช้ครับ ทำได้แล้วครับ
ที่
Code: Select all
D3=SUMPRODUCT(--(LEFT(รหัสบัญชี,2)=RIGHT(D$2,2)),จำนวนเงิน)
Enter แล้ว Copy ไปขวาเลยครับ หรือ อย่างไรอีกครับ ท่านอาจารย์
Re: สูตรรวมที่มีเงื่อนไข
Posted: Wed May 20, 2015 2:43 pm
by DhitiBank
จากไฟล์ล่าสุดที่แนบมา หากตัดตัวเลขออกมาในคอลัมน์ C แล้ว ให้ใช้สูตร Sumif เลยครับ เพราะการคำนวณจะเร็วกว่า Sumproduct (ถูกออกแบบมาเพื่อการนี้อยู่แล้ว) และในคอลัมน์ C หลังจากคัดลอกสูตรลงไปถึงแถวสุดท้ายแล้ว ให้คัดลอก C2:C... แล้ววางเฉพาะค่าที่ C2 เพื่อไม่ให้มีสูตรอยู่อีก ทั้งนี้เพื่อลดภาระในการคำนวณของโปรแกรมครับ
แต่ถ้าหากต้องการใช้ Sumproduct ก็ไม่จำเป็นต้องมีคอลัมน์C มาช่วยอีกครับ
Re: สูตรรวมที่มีเงื่อนไข
Posted: Wed May 20, 2015 3:03 pm
by วังวู ช่ง
ครับผม ความรู้ที่ดีมากครับ ขอบคุณสำหรับความรู้ดีๆของท่านครับ
Re: สูตรรวมที่มีเงื่อนไข
Posted: Wed May 20, 2015 3:09 pm
by วังวู ช่ง
รบกวนถามไปหน่อยครับ ในส่วนของ --RIGHT(D$2,2) และ RIGHT(D$2,2) มีความหมายอย่างไรครับ เพราะผมปรับจาก --RIGHT(D$2,2) มาเป็น RIGHT(D$2,2) ยังถูกเหมือนกันครับ
Re: สูตรรวมที่มีเงื่อนไข
Posted: Wed May 20, 2015 3:23 pm
by DhitiBank
อ๋อ
แตกต่างที่ "--" เป็นการกลับเครื่องหมายครับ คือบังคับให้ตัวเลขที่มีลักษณะเป็นข้อความ หรือตัวเลขที่ได้จากการตัดด้วยสูตรพวก Mid, Left, Right, ... ให้กลับมาเป็นตัวเลขจริงๆ
ที่ก่อนหน้านี้สูตรสามารถคำนวณได้เพราะคุณได้เอาไปเทียบกับตัวเลขที่เป็นข้อความเหมือนกันครับ (จะเห็นว่าได้จากการตัดด้วย Left เอามาเทียบกับเลขที่ตัดด้วย Right)
ลองค้นหาเพิ่มเติมด้วยคำว่า "กลับเครื่องหมาย" ในช่องค้นหาดูครับ จะได้เห็นตัวอย่างเพิ่มเติม
Re: สูตรรวมที่มีเงื่อนไข
Posted: Wed May 20, 2015 3:37 pm
by วังวู ช่ง
ครับ ขอบคุณครับ
Re: สูตรรวมที่มีเงื่อนไข
Posted: Fri May 22, 2015 3:31 pm
by วังวู ช่ง
รบกวนต่ออีกครับ
ความต้องการคือว่าอยากได้สูตรรวมในช่อง C ครับ เมื่อถึงตัวเลขที่ทางหน้ามีเลข 12 แต่ไม่รวมรหัสบัญชีที่เป็น 12009977 (สีเหลือง) ครับ
ให้รวมแต่ B2 ลงมา แต่ไม่รวมจำนวนเงินที่ทางหน้าของเลขบัญชีเป็น 13 ครับ มีสูตรอย่างไร ขอช่วยหน่อยครับ
ขอบคุณล่วงหน้าครับ
Re: สูตรรวมที่มีเงื่อนไข
Posted: Fri May 22, 2015 3:54 pm
by DhitiBank
ทดลองแบบนี้ครับ
ที่ C2 คีย์
=IF(AND(LEFT(A2,2)="12",A2<>12009977),SUMPRODUCT(--(LEFT($A$2:$A2,2)="12"),$B$2:$B2)-SUMPRODUCT(--($A$2:A2=12009977),$B$2:$B2),"")
Enter ครับ แล้วคัดลอกลงล่าง
Re: สูตรรวมที่มีเงื่อนไข
Posted: Mon May 25, 2015 1:33 pm
by วังวู ช่ง
DhitiBank wrote:ทดลองแบบนี้ครับ
ที่ C2 คีย์
=IF(AND(LEFT(A2,2)="12",A2<>12009977),SUMPRODUCT(--(LEFT($A$2:$A2,2)="12"),$B$2:$B2)-SUMPRODUCT(--($A$2:A2=12009977),$B$2:$B2),"")
Enter ครับ แล้วคัดลอกลงล่าง
ขอบคุณมากครับ