คำนวณแบบหักลบค่ะ (ไม่รู้เรียกถูกหรือเปล่า)

[katai12]

ต้องการได้สูตรคำนวณ โดยการลบจำนวนออกค่ะ คือ
ซื้อ 300 จะได้เพิ่ม 10
ซื้อ 400 จะได้เพิ่ม 15
ซื้อ 800 จะได้เพิ่ม 35
ซื้อ 1200 จะได้เพิ่ม 55
ถ้าซื้อ 2000 (1200+800) จะได้ 35+55
หรือถ้าซื้อ 1650 (1200+300) จะได้ 55+10 แบบนี้ค่ะ
รบกวนผู้รู้ช่วยสอนหน่อยค่ะ ขอบคุณค่ะ

[snasui]

ทำตารางดังกล่าวขึ้นมาก่อนแล้วค่อยนำไปใช้ทีหลัง

คอลัมน์แรกคือ ซื้อ คอลัมน์ถัดมาคือ ได้เพิ่ม แสดงรายการเรียงกันไปจนครบทุกเงื่อนไข ทำเสร็จแล้วแนบไฟล์มาอีกทีครับ

[katai12]

แบบนี้ใช่มั้ยคะ

[snasui]

จากไฟล์ที่แนบมา

สมมุติค่าที่ต้องการตรวจสอบอยู่ที่ D1 ที่เซลล์ใด ๆ คีย์

=VLOOKUP(D1,$A$2:$B$5,2)

Enter จะได้คำตอบตามที่ต้องการครับ

[katai12]

ไม่ใช่ค่ะ คำตอบจะได้เป็น 90 ค่ะ

[snasui]

ต้องเขียน 2000 และ 90 เพิ่มเข้ามาอีก 1 บรรทัดแล้วขยายช่วงเซลล์ในสูตรให้ครอบคลุมบรรทัดที่เพิ่มเข้ามาครับ

หรือไม่เช่นนั้นก็ต้องบอกวิธีคิดมาอย่างละเอียดว่าเพิ่มในอัตราเท่าใด

หาก D1 เป็น 1,000,000 คำตอบที่ต้องการจะได้เป็นเท่าไร คำนวณอย่างไรจึงได้ค่าเท่านั้นครับ

[katai12]

วิธีคิดคือ
กรณีซื้อ 2000 ก็จะหักลบ 1200 ออก ซึ่งตรงนี้จะได้เพิ่ม 55 (B5)
ส่วนเศษที่เหลืออีก 800 ก็จะได้เพิ่ม อีก 35 (B4) รวมจะได้เพิ่มอีก 90
หรือถ้า ซื้อ 1900 วิธีคิดก็เช่นเดียวกัน คือ หักจำนวนมากสุดก่อน
1900-1200 ในส่วนของ 1200 จะได้เพิ่ม 55
ที่เหลือ 700-400 ในส่วนของ 400 จะได้เพิ่ม 15
ที่เหลืออกี 300 จะได้ 10 รวม ได้ 80 แบบนี้ค่ะ

[snasui]

เพื่อความเข้าใจที่เพิ่มขึ้น ถ้า D1 เป็น 500 มีวิธีคิดอย่างไรครับ

[katai12]

ถ้า D1 = 500
ก็จะได้ Step 300 ค่ะ คือได้เพิ่ม 10
ส่วน เศษ 200 ปัดทิ้งค่ะ เพราะไม่ได้ตามเงื่อนไขที่กำหนดค่ะ

[snasui]

ค่อย ๆ ถามตอบกันไปนะครับ

ในไฟล์แนบมีซื้อ 400 และได้เพิ่ม 15

จากกรณีที่ D1 มีค่าเป็น 500 ทำไมไม่คิดที่ Step 400 แล้วเหลือเศษ 100 ปัดทิ้งครับ

[katai12]

อ้อค่ะ ใช่ค่ะ เป็น step 400 แล้วปัดเศษ 100 ทิ้งค่ะ

[snasui]

ตัวอย่างสูตรครับ

เซลล์ E2 คีย์

=LOOKUP(9.9999999999999E+307,CHOOSE({1,2},VLOOKUP($D$1,$A$2:$B$5,2),SUM(OFFSET(B5,0,0,-MATCH($D$1,SUBTOTAL(9,OFFSET(A5,0,0,-(ROW($A$2:$A$5)-ROW($B$2)+1))))))))

Ctrl+Shift+Enter

!

Note: Ctrl+Shift+Enter หมายถึง กรณีคีย์สูตรเอง เมื่อคีย์สูตรแล้ว แทนที่จะกด Enter ให้กดแป้น Ctrl+Shift ค้างไว้แล้วกด Enter เพื่อสร้างเป็นสูตร Array กรณี Copy สูตรไปวางให้กดแป้น F2 เพื่อทำการ Edit Cell นั้นก่อน จากนั้นกดแป้น Ctrl+Shift ค้างไว้แล้วกด Enter เพื่อสร้างเป็นสูตร Array หากกดแป้นถูกต้องสูตรนั้นจะมีเครื่องหมายปีกกาครอบ เช่น {=YourFormulas(...)} ปีกกานี้คีย์เข้าไปเองไม่ได้ ถ้ายังไม่เห็นปีกกาครอบสูตรแสดงว่ากดแป้นให้รับสูตรไม่ถูกต้อง การแก้ไขเปลี่ยนแปลงสูตร Array จะต้องกดแป้นให้รับสูตรด้วย Ctrl+Shift+Enter ทุกครั้ง

[katai12]

copy สูตรไปวางแล้ว แต่คำตอบยังเป็น 55 อยู่เลยค่ะ คือ ถ้าซื้อ 2000 คำตอบตรง E2 จะต้องแสดง 90
แล้วลองคีย์จำนวนอื่นลงไป ก็จะได้คำตอบสูงสุดแค่ 55 ค่ะ ซึ่งจริง ๆ จะต้องคำนวณไปเรื่อย ๆ ค่ะ

[logic]

ที่ไม่ได้เพราะว่าไม่ได้ทำตามที่อาจารย์แจ้งไว้ใน note คุณลองอ่านอย่างละเอียดและทำตามนั้นครับ

[katai12]

อ๋อ ค่ะ ขอบคุณค่ะ
แต่พอทำตามแล้ว ทำไม 1500 ยังได้ แค่ 55 คะ
(ขอโทษนะคะ ที่ถามเยอะ เพราะไม่ค่อยเป็น excel ค่ะ)

[snasui]

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

[katai12]

ทำไม่เป็นค่ะ สูตร excel ก็ได้นิด ๆ หน่อย ๆ ค่ะ VBA ไม่รู้จักเลยค่ะ