EXCEL TOOLS
Excel Add-ins ที่พัฒนาโดยคุณสันติพงศ์ ณสุย (MVP Excel 2010-2020) ด้วยภาษา C# เพื่อแก้ไขปัญหาไฟล์ใหญ่ คำนวณนาน ทำงานช้า จัดการข้อมูลต่าง ๆ ที่ทำงานประจำวันได้อย่างสะดวกรวดเร็ว สนใจคลิกไปดูได้ที่นี่ครับ => Excel Tools
Excel Add-ins ที่พัฒนาโดยคุณสันติพงศ์ ณสุย (MVP Excel 2010-2020) ด้วยภาษา C# เพื่อแก้ไขปัญหาไฟล์ใหญ่ คำนวณนาน ทำงานช้า จัดการข้อมูลต่าง ๆ ที่ทำงานประจำวันได้อย่างสะดวกรวดเร็ว สนใจคลิกไปดูได้ที่นี่ครับ => Excel Tools
[code]
และปิดด้วย [/code]
ตัวอย่างเช่น [code]dim r as range[/code]
เพื่อให้แตกต่างจากข้อความทั่วไป สะดวกในการอ่านและทดสอบ (คลิกเพื่อดูตัวอย่าง)Code: Select all
Public Function cost(list As String, piece As String, kind As String) As String
If list = "0" Then
cost = "0"
ElseIf list = "4/8*1.5*100_22,23mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H14").Value * Worksheets("Ref_Table").Range("I14").Value
ElseIf list = "4/8*1.5*100_22,23mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H14").Value * Worksheets("Ref_Table").Range("J14").Value
ElseIf list = "4/8*1.5*100_22,23mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H14").Value * Worksheets("Ref_Table").Range("K14").Value
ElseIf list = "4/8*1.75*100_22,23mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H15").Value * Worksheets("Ref_Table").Range("I15").Value
ElseIf list = "4/8*1.75*100_22,23mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H15").Value * Worksheets("Ref_Table").Range("J15").Value
ElseIf list = "4/8*1.75*100_22,23mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H15").Value * Worksheets("Ref_Table").Range("K15").Value
ElseIf list = "4/8*2*100_22,23mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H16").Value * Worksheets("Ref_Table").Range("I16").Value
ElseIf list = "4/8*2*100_22,23mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H16").Value * Worksheets("Ref_Table").Range("J16").Value
ElseIf list = "4/8*2*100_22,23mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H16").Value * Worksheets("Ref_Table").Range("K16").Value
ElseIf list = "4/8*2.5*100_22,23mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H17").Value * Worksheets("Ref_Table").Range("I17").Value
ElseIf list = "4/8*2.5*100_22,23mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H17").Value * Worksheets("Ref_Table").Range("J17").Value
ElseIf list = "4/8*2.5*100_22,23mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H17").Value * Worksheets("Ref_Table").Range("K17").Value
ElseIf list = "4/8*3*100_22,23mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H18").Value * Worksheets("Ref_Table").Range("I18").Value
ElseIf list = "4/8*3*100_22,23mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H18").Value * Worksheets("Ref_Table").Range("J18").Value
ElseIf list = "4/8*3*100_22,23mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H18").Value * Worksheets("Ref_Table").Range("K18").Value
ElseIf list = "4/8*4*100_22,23mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H19").Value * Worksheets("Ref_Table").Range("I19").Value
ElseIf list = "4/8*4*100_22,23mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H19").Value * Worksheets("Ref_Table").Range("J19").Value
ElseIf list = "4/8*4*100_22,23mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H19").Value * Worksheets("Ref_Table").Range("K19").Value
ElseIf list = "4/8*1.5*110_22,23mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H20").Value * Worksheets("Ref_Table").Range("I20").Value
ElseIf list = "4/8*1.5*110_22,23mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H20").Value * Worksheets("Ref_Table").Range("J20").Value
ElseIf list = "4/8*1.5*110_22,23mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H20").Value * Worksheets("Ref_Table").Range("K20").Value
ElseIf list = "4/8*1.75*110_22,23mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H21").Value * Worksheets("Ref_Table").Range("I21").Value
ElseIf list = "4/8*1.75*110_22,23mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H21").Value * Worksheets("Ref_Table").Range("J21").Value
ElseIf list = "4/8*1.75*110_22,23mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H21").Value * Worksheets("Ref_Table").Range("K21").Value
ElseIf list = "4/8*2*110_22,23mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H22").Value * Worksheets("Ref_Table").Range("I22").Value
ElseIf list = "4/8*2*110_22,23mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H22").Value * Worksheets("Ref_Table").Range("J22").Value
ElseIf list = "4/8*2*110_22,23mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H22").Value * Worksheets("Ref_Table").Range("K22").Value
ElseIf list = "4/8*2.5*110_22,23mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H23").Value * Worksheets("Ref_Table").Range("I23").Value
ElseIf list = "4/8*2.5*110_22,23mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H23").Value * Worksheets("Ref_Table").Range("J23").Value
ElseIf list = "4/8*2.5*110_22,23mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H23").Value * Worksheets("Ref_Table").Range("K23").Value
ElseIf list = "4/8*3*110_22,23mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H24").Value * Worksheets("Ref_Table").Range("I24").Value
ElseIf list = "4/8*3*110_22,23mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H24").Value * Worksheets("Ref_Table").Range("J24").Value
ElseIf list = "4/8*3*110_22,23mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H24").Value * Worksheets("Ref_Table").Range("K24").Value
ElseIf list = "4/8*4*110_22,23mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H25").Value * Worksheets("Ref_Table").Range("I25").Value
ElseIf list = "4/8*4*110_22,23mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H25").Value * Worksheets("Ref_Table").Range("J25").Value
ElseIf list = "4/8*4*110_22,23mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H25").Value * Worksheets("Ref_Table").Range("K25").Value
ElseIf list = "4/8*1.5*100_24mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H26").Value * Worksheets("Ref_Table").Range("I26").Value
ElseIf list = "4/8*1.5*100_24mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H26").Value * Worksheets("Ref_Table").Range("J26").Value
ElseIf list = "4/8*1.5*100_24mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H26").Value * Worksheets("Ref_Table").Range("K26").Value
ElseIf list = "4/8*1.75*100_24mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H27").Value * Worksheets("Ref_Table").Range("I27").Value
ElseIf list = "4/8*1.75*100_24mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H27").Value * Worksheets("Ref_Table").Range("J27").Value
ElseIf list = "4/8*1.75*100_24mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H27").Value * Worksheets("Ref_Table").Range("K27").Value
ElseIf list = "4/8*2*100_24mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H28").Value * Worksheets("Ref_Table").Range("I28").Value
ElseIf list = "4/8*2*100_24mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H28").Value * Worksheets("Ref_Table").Range("J28").Value
ElseIf list = "4/8*2*100_24mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H28").Value * Worksheets("Ref_Table").Range("K28").Value
ElseIf list = "4/8*2.5*100_24mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H29").Value * Worksheets("Ref_Table").Range("I29").Value
ElseIf list = "4/8*2.5*100_24mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H29").Value * Worksheets("Ref_Table").Range("J29").Value
ElseIf list = "4/8*2.5*100_24mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H29").Value * Worksheets("Ref_Table").Range("K29").Value
ElseIf list = "4/8*3*100_24mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H30").Value * Worksheets("Ref_Table").Range("I30").Value
ElseIf list = "4/8*3*100_24mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H30").Value * Worksheets("Ref_Table").Range("J30").Value
ElseIf list = "4/8*3*100_24mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H30").Value * Worksheets("Ref_Table").Range("K30").Value
ElseIf list = "4/8*4*100_24mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H31").Value * Worksheets("Ref_Table").Range("I31").Value
ElseIf list = "4/8*4*100_24mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H31").Value * Worksheets("Ref_Table").Range("J31").Value
ElseIf list = "4/8*4*100_24mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H31").Value * Worksheets("Ref_Table").Range("K31").Value
ElseIf list = "4/8*1.5*110_24mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H32").Value * Worksheets("Ref_Table").Range("I32").Value
ElseIf list = "4/8*1.5*110_24mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H32").Value * Worksheets("Ref_Table").Range("J32").Value
ElseIf list = "4/8*1.5*110_24mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H32").Value * Worksheets("Ref_Table").Range("K32").Value
ElseIf list = "4/8*1.75*110_24mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H33").Value * Worksheets("Ref_Table").Range("I33").Value
ElseIf list = "4/8*1.75*110_24mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H33").Value * Worksheets("Ref_Table").Range("J33").Value
ElseIf list = "4/8*1.75*110_24mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H33").Value * Worksheets("Ref_Table").Range("K33").Value
ElseIf list = "4/8*2*110_24mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H34").Value * Worksheets("Ref_Table").Range("I34").Value
ElseIf list = "4/8*2*110_24mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H34").Value * Worksheets("Ref_Table").Range("J34").Value
ElseIf list = "4/8*2*110_24mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H34").Value * Worksheets("Ref_Table").Range("K34").Value
ElseIf list = "4/8*2.5*110_24mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H35").Value * Worksheets("Ref_Table").Range("I35").Value
ElseIf list = "4/8*2.5*110_24mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H35").Value * Worksheets("Ref_Table").Range("J35").Value
ElseIf list = "4/8*2.5*110_24mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H35").Value * Worksheets("Ref_Table").Range("K35").Value
ElseIf list = "4/8*3*110_24mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H36").Value * Worksheets("Ref_Table").Range("I36").Value
ElseIf list = "4/8*3*110_24mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H36").Value * Worksheets("Ref_Table").Range("J36").Value
ElseIf list = "4/8*3*110_24mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H36").Value * Worksheets("Ref_Table").Range("K36").Value
ElseIf list = "4/8*4*110_24mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H37").Value * Worksheets("Ref_Table").Range("I37").Value
ElseIf list = "4/8*4*110_24mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H37").Value * Worksheets("Ref_Table").Range("J37").Value
ElseIf list = "4/8*4*110_24mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H37").Value * Worksheets("Ref_Table").Range("K37").Value
ElseIf list = "4/8*1.5*130_22,23mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H38").Value * Worksheets("Ref_Table").Range("I38").Value
ElseIf list = "4/8*1.5*130_22,23mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H38").Value * Worksheets("Ref_Table").Range("J38").Value
ElseIf list = "4/8*1.5*130_22,23mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H38").Value * Worksheets("Ref_Table").Range("K38").Value
ElseIf list = "4/8*1.75*130_22,23mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H39").Value * Worksheets("Ref_Table").Range("I39").Value
ElseIf list = "4/8*1.75*130_22,23mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H39").Value * Worksheets("Ref_Table").Range("J39").Value
ElseIf list = "4/8*1.75*130_22,23mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H39").Value * Worksheets("Ref_Table").Range("K39").Value
ElseIf list = "4/8*2*130_22,23mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H40").Value * Worksheets("Ref_Table").Range("I40").Value
ElseIf list = "4/8*2*130_22,23mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H40").Value * Worksheets("Ref_Table").Range("J40").Value
ElseIf list = "4/8*2*130_22,23mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H40").Value * Worksheets("Ref_Table").Range("K40").Value
ElseIf list = "4/8*2.5*130_22,23mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H41").Value * Worksheets("Ref_Table").Range("I41").Value
ElseIf list = "4/8*2.5*130_22,23mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H41").Value * Worksheets("Ref_Table").Range("J41").Value
ElseIf list = "4/8*2.5*130_22,23mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H41").Value * Worksheets("Ref_Table").Range("K41").Value
ElseIf list = "4/8*3*130_22,23mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H42").Value * Worksheets("Ref_Table").Range("I42").Value
ElseIf list = "4/8*3*130_22,23mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H42").Value * Worksheets("Ref_Table").Range("J42").Value
ElseIf list = "4/8*3*130_22,23mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H42").Value * Worksheets("Ref_Table").Range("K42").Value
ElseIf list = "4/8*4*130_22,23mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H43").Value * Worksheets("Ref_Table").Range("I43").Value
ElseIf list = "4/8*4*130_22,23mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H43").Value * Worksheets("Ref_Table").Range("J43").Value
ElseIf list = "4/8*4*130_22,23mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H43").Value * Worksheets("Ref_Table").Range("K43").Value
ElseIf list = "4/8*1.5*100_25mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H50").Value * Worksheets("Ref_Table").Range("I50").Value
ElseIf list = "4/8*1.5*100_25mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H50").Value * Worksheets("Ref_Table").Range("J50").Value
ElseIf list = "4/8*1.5*100_25mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H50").Value * Worksheets("Ref_Table").Range("K50").Value
ElseIf list = "4/8*1.75*100_25mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H51").Value * Worksheets("Ref_Table").Range("I51").Value
ElseIf list = "4/8*1.75*100_25mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H51").Value * Worksheets("Ref_Table").Range("J51").Value
ElseIf list = "4/8*1.75*100_25mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H51").Value * Worksheets("Ref_Table").Range("K51").Value
ElseIf list = "4/8*2*100_25mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H52").Value * Worksheets("Ref_Table").Range("I52").Value
ElseIf list = "4/8*2*100_25mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H52").Value * Worksheets("Ref_Table").Range("J52").Value
ElseIf list = "4/8*2*100_25mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H52").Value * Worksheets("Ref_Table").Range("K52").Value
ElseIf list = "4/8*2.5*100_25mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H53").Value * Worksheets("Ref_Table").Range("I53").Value
ElseIf list = "4/8*2.5*100_25mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H53").Value * Worksheets("Ref_Table").Range("J53").Value
ElseIf list = "4/8*2.5*100_25mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H53").Value * Worksheets("Ref_Table").Range("K53").Value
ElseIf list = "4/8*3*100_25mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H54").Value * Worksheets("Ref_Table").Range("I54").Value
ElseIf list = "4/8*3*100_25mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H54").Value * Worksheets("Ref_Table").Range("J54").Value
ElseIf list = "4/8*3*100_25mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H54").Value * Worksheets("Ref_Table").Range("K54").Value
ElseIf list = "4/8*4*100_25mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H55").Value * Worksheets("Ref_Table").Range("I55").Value
ElseIf list = "4/8*4*100_25mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H55").Value * Worksheets("Ref_Table").Range("J55").Value
ElseIf list = "4/8*4*100_25mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H55").Value * Worksheets("Ref_Table").Range("K55").Value
ElseIf list = "4/8*1.5*110_25mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H56").Value * Worksheets("Ref_Table").Range("I56").Value
ElseIf list = "4/8*1.5*110_25mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H56").Value * Worksheets("Ref_Table").Range("J56").Value
ElseIf list = "4/8*1.5*110_25mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H56").Value * Worksheets("Ref_Table").Range("K56").Value
ElseIf list = "4/8*1.75*110_25mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H57").Value * Worksheets("Ref_Table").Range("I57").Value
ElseIf list = "4/8*1.75*110_25mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H57").Value * Worksheets("Ref_Table").Range("J57").Value
ElseIf list = "4/8*1.75*110_25mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H57").Value * Worksheets("Ref_Table").Range("K57").Value
ElseIf list = "4/8*2*110_25mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H58").Value * Worksheets("Ref_Table").Range("I58").Value
ElseIf list = "4/8*2*110_25mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H58").Value * Worksheets("Ref_Table").Range("J58").Value
ElseIf list = "4/8*2*110_25mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H58").Value * Worksheets("Ref_Table").Range("K58").Value
ElseIf list = "4/8*2.5*110_25mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H59").Value * Worksheets("Ref_Table").Range("I59").Value
ElseIf list = "4/8*2.5*110_25mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H59").Value * Worksheets("Ref_Table").Range("J59").Value
ElseIf list = "4/8*2.5*110_25mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H59").Value * Worksheets("Ref_Table").Range("K59").Value
ElseIf list = "4/8*3*110_25mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H60").Value * Worksheets("Ref_Table").Range("I60").Value
ElseIf list = "4/8*3*110_25mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H60").Value * Worksheets("Ref_Table").Range("J60").Value
ElseIf list = "4/8*3*110_25mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H60").Value * Worksheets("Ref_Table").Range("K60").Value
ElseIf list = "4/8*4*110_25mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H61").Value * Worksheets("Ref_Table").Range("I61").Value
ElseIf list = "4/8*4*110_25mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H61").Value * Worksheets("Ref_Table").Range("J61").Value
ElseIf list = "4/8*4*110_25mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H61").Value * Worksheets("Ref_Table").Range("K61").Value
ElseIf list = "5/8*1.5*100_31mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H74").Value * Worksheets("Ref_Table").Range("I74").Value
ElseIf list = "5/8*1.5*100_31mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H74").Value * Worksheets("Ref_Table").Range("J74").Value
ElseIf list = "5/8*1.5*100_31mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H74").Value * Worksheets("Ref_Table").Range("K74").Value
ElseIf list = "5/8*1.75*100_31mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H75").Value * Worksheets("Ref_Table").Range("I75").Value
ElseIf list = "5/8*1.75*100_31mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H75").Value * Worksheets("Ref_Table").Range("J75").Value
ElseIf list = "5/8*1.75*100_31mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H75").Value * Worksheets("Ref_Table").Range("K75").Value
ElseIf list = "5/8*2*100_31mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H76").Value * Worksheets("Ref_Table").Range("I76").Value
ElseIf list = "5/8*2*100_31mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H76").Value * Worksheets("Ref_Table").Range("J76").Value
ElseIf list = "5/8*2*100_31mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H76").Value * Worksheets("Ref_Table").Range("K76").Value
ElseIf list = "5/8*2.5*100_31mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H77").Value * Worksheets("Ref_Table").Range("I77").Value
ElseIf list = "5/8*2.5*100_31mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H77").Value * Worksheets("Ref_Table").Range("J77").Value
ElseIf list = "5/8*2.5*100_31mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H77").Value * Worksheets("Ref_Table").Range("K77").Value
ElseIf list = "5/8*3*100_31mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H78").Value * Worksheets("Ref_Table").Range("I78").Value
ElseIf list = "5/8*3*100_31mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H78").Value * Worksheets("Ref_Table").Range("J78").Value
ElseIf list = "5/8*3*100_31mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H78").Value * Worksheets("Ref_Table").Range("K78").Value
ElseIf list = "5/8*4*100_31mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H79").Value * Worksheets("Ref_Table").Range("I79").Value
ElseIf list = "5/8*4*100_31mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H79").Value * Worksheets("Ref_Table").Range("J79").Value
ElseIf list = "5/8*4*100_31mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H79").Value * Worksheets("Ref_Table").Range("K79").Value
ElseIf list = "5/8*1.5*110_31mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H80").Value * Worksheets("Ref_Table").Range("I80").Value
ElseIf list = "5/8*1.5*110_31mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H80").Value * Worksheets("Ref_Table").Range("J80").Value
ElseIf list = "5/8*1.5*110_31mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H80").Value * Worksheets("Ref_Table").Range("K80").Value
ElseIf list = "5/8*1.75*110_31mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H81").Value * Worksheets("Ref_Table").Range("I81").Value
ElseIf list = "5/8*1.75*110_31mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H81").Value * Worksheets("Ref_Table").Range("J81").Value
ElseIf list = "5/8*1.75*110_31mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H81").Value * Worksheets("Ref_Table").Range("K81").Value
ElseIf list = "5/8*2*110_31mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H82").Value * Worksheets("Ref_Table").Range("I82").Value
ElseIf list = "5/8*2*110_31mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H82").Value * Worksheets("Ref_Table").Range("J82").Value
ElseIf list = "5/8*2*110_31mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H82").Value * Worksheets("Ref_Table").Range("K82").Value
ElseIf list = "5/8*2.5*110_31mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H83").Value * Worksheets("Ref_Table").Range("I83").Value
ElseIf list = "5/8*2.5*110_31mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H83").Value * Worksheets("Ref_Table").Range("J83").Value
ElseIf list = "5/8*2.5*110_31mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H83").Value * Worksheets("Ref_Table").Range("K83").Value
ElseIf list = "5/8*3*110_31mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H84").Value * Worksheets("Ref_Table").Range("I84").Value
ElseIf list = "5/8*3*110_31mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H84").Value * Worksheets("Ref_Table").Range("J84").Value
ElseIf list = "5/8*3*110_31mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H84").Value * Worksheets("Ref_Table").Range("K84").Value
ElseIf list = "5/8*4*110_31mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H85").Value * Worksheets("Ref_Table").Range("I85").Value
ElseIf list = "5/8*4*110_31mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H85").Value * Worksheets("Ref_Table").Range("J85").Value
ElseIf list = "5/8*4*110_31mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H85").Value * Worksheets("Ref_Table").Range("K85").Value
ElseIf list = "6/8*1.5*100_33mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H86").Value * Worksheets("Ref_Table").Range("I86").Value
ElseIf list = "6/8*1.5*100_33mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H86").Value * Worksheets("Ref_Table").Range("J86").Value
ElseIf list = "6/8*1.5*100_33mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H86").Value * Worksheets("Ref_Table").Range("K86").Value
ElseIf list = "6/8*1.75*100_33mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H87").Value * Worksheets("Ref_Table").Range("I87").Value
ElseIf list = "6/8*1.75*100_33mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H87").Value * Worksheets("Ref_Table").Range("J87").Value
ElseIf list = "6/8*1.75*100_33mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H87").Value * Worksheets("Ref_Table").Range("K87").Value
ElseIf list = "6/8*2*100_33mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H88").Value * Worksheets("Ref_Table").Range("I88").Value
ElseIf list = "6/8*2*100_33mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H88").Value * Worksheets("Ref_Table").Range("J88").Value
ElseIf list = "6/8*2*100_33mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H88").Value * Worksheets("Ref_Table").Range("K88").Value
ElseIf list = "6/8*2.5*100_33mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H89").Value * Worksheets("Ref_Table").Range("I89").Value
ElseIf list = "6/8*2.5*100_33mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H89").Value * Worksheets("Ref_Table").Range("J89").Value
ElseIf list = "6/8*2.5*100_33mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H89").Value * Worksheets("Ref_Table").Range("K89").Value
ElseIf list = "6/8*3*100_33mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H90").Value * Worksheets("Ref_Table").Range("I90").Value
ElseIf list = "6/8*3*100_33mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H90").Value * Worksheets("Ref_Table").Range("J90").Value
ElseIf list = "6/8*3*100_33mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H90").Value * Worksheets("Ref_Table").Range("K90").Value
ElseIf list = "6/8*4*100_33mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H91").Value * Worksheets("Ref_Table").Range("I91").Value
ElseIf list = "6/8*4*100_33mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H91").Value * Worksheets("Ref_Table").Range("J91").Value
ElseIf list = "6/8*4*100_33mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H91").Value * Worksheets("Ref_Table").Range("K91").Value
ElseIf list = "6/8*5*100_33mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H92").Value * Worksheets("Ref_Table").Range("I92").Value
ElseIf list = "6/8*5*100_33mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H92").Value * Worksheets("Ref_Table").Range("J92").Value
ElseIf list = "6/8*5*100_33mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H92").Value * Worksheets("Ref_Table").Range("K92").Value
ElseIf list = "6/8*1.5*110_33mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H93").Value * Worksheets("Ref_Table").Range("I93").Value
ElseIf list = "6/8*1.5*110_33mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H93").Value * Worksheets("Ref_Table").Range("J93").Value
ElseIf list = "6/8*1.5*110_33mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H93").Value * Worksheets("Ref_Table").Range("K93").Value
ElseIf list = "6/8*1.75*110_33mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H94").Value * Worksheets("Ref_Table").Range("I94").Value
ElseIf list = "6/8*1.75*110_33mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H94").Value * Worksheets("Ref_Table").Range("J94").Value
ElseIf list = "6/8*1.75*110_33mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H94").Value * Worksheets("Ref_Table").Range("K94").Value
ElseIf list = "6/8*2*110_33mm" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = Worksheets("Ref_Table").Range("H95").Value * Worksheets("Ref_Table").Range("I95").Value
ElseIf list = "6/8*2*110_33mm" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = Worksheets("Ref_Table").Range("H95").Value * Worksheets("Ref_Table").Range("J95").Value
ElseIf list = "6/8*2*110_33mm" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = Worksheets("Ref_Table").Range("H95").Value * Worksheets("Ref_Table").Range("K95").Value
ElseIf list = "0" And kind = "ÃÒ¤Ò»¡µÔ" Then
cost = 0
ElseIf list = "0" And kind = "ÃÒ¤Ò¾ÔàÈÉ" Then
cost = 0
ElseIf list = "0" And kind = "ÃÒ¤Ò«Ø»à»ÍÃì" Then
cost = 0
End If
End Function
คือผมต้องตัด code บางส่วนออกครับ เพราะเขาไม่ให้วาง code เกิน 60k ตัวอักษรครับ เลย run ได้snasui wrote: Code ด้านบนนี้ในเครื่องผมสามารถ Run ได้ ไม่ติดปัญหาใดครับ
พอดีวันนี้ผมได้ลองสร้างตารางขึ้นมาใหม่ตารางนึงครับsnasui wrote: สามารถใช้สูตรเข้ามาช่วยแทนการเขียนสูตรขึ้นมาเองได้ครับ
ตัวอย่างสูตรที่ชีต วันที่ 1 เซลล์ I6 คีย์
=IFERROR(ROUND(INDEX(Ref_Table!$H$2:$H$1000,MATCH($D6,Ref_Table!$E$2:$E$1000,0))*INDEX(Ref_Table!$I$2:$O$1000,MATCH($D6,Ref_Table!$E$2:$E$1000,0),MATCH($F5,Ref_Table!$I$1:$O$1,0)),2),0)
Enter > Copy ไปยังเซลล์ที่เกี่ยวข้อง
ลองตรวจสอบว่าสูตรถูกต้องตรงกับที่ต้องการหรือไม่ หากไม่ถูกต้องช่วยอธิบายมาด้วยว่าคำตอบเป็นค่าเท่าใด มีวิธีการคำนวณอย่างไรจึงได้ค่าเท่านั้น จะได้เข้าใจตรงกันครับ
Code: Select all
i =FIXED(VLOOKUP(L5,'vlookup sheet2'!$C$2:$D$553,2,FALSE) * VLOOKUP(L5,'vlookup sheet2'!$C$2:$E$553,3,FALSE),2)
=OFFSET(Sheet2!$C$2,MATCH(CHAR(255),Sheet2!$C:$C)-1,0,,3)
> OK > นำ Range Name มาใช้ในสูตร เช่น =VLOOKUP(L5,Tb,2,FALSE)*VLOOKUP(L5,Tb,3,FALSE)