Snasui Excel Forum

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

เซลล์ 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 ทุกครั้ง

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

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

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

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

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