Snasui Excel Forum

สวัสดีค่ะ....

รวบกวนสอบถามสูตรคำนวณ บวก-ลบ เวลาจากเวลาเริ่มต้นที่มีค่า
เช่น กรอก 08:00 ให้ค่าที่ได้อีกช่อง โชว์ 08:10 ค่ะ

และ สูตรเงื่อนไข การเลือกข้อมูล ตามไฟล์แนบค่ะ ขอบคุณค่ะ ^^

ช่วยกรอกตัวอย่างคำตอบที่ถูกต้องมาด้วยพร้อมอธิบายมาสักสองสามตัวอย่างว่า มีวิธีคิดอย่างไรจึงได้ค่าเท่านั้น จะได้เข้าใจตรงกันครับ

ตัวอย่างคำตอบที่ต้องการค่ะ
และวิธีคิดค่ะ

ขอบคุณค่ะ

ที่เขียนมาในไฟล์แนบนั้นไม่มีวิธีคิดของคอลัมน์ D ครับ

ในคอลัมน์ D ดูอย่างไรว่าต้องบวก 10 นาทีหรือต้องลบด้วย 10 นาที ครับ

ขอแนวคิดเป็นบวกอย่างเดียวก็ได้ค่ะ
พอดีมันมีข้อจำกัดหลายอย่าง...ขอบคุณค่ะ

oo-beer-oo wrote:ขอแนวคิดเป็นบวกอย่างเดียวก็ได้ค่ะ
พอดีมันมีข้อจำกัดหลายอย่าง...ขอบคุณค่ะ

เซลล์ D3 คีย์สูตร

=A3+"0:10:00"

Enter > Copy ลงด้านล่าง

เมื่อข้อมูลในคอลัม B แสดงตามคอลัม E F G แล้ว ให้ เลือก Number Tank ตามชีทที่2
โดยมีเงื่อนไข ถ้าแถวแรกเลือกแล้ว ห้ามเลือกซ้ำ เช่น B3 เลือก H3 แล้ว B4 จะไม่สามารถเลือก H3 ได้อีก จะต้องเลือกช่องถัดไป

จากด้านบนที่ผมยกมาอ่านแล้วไม่เข้าใจ ลองอธิบายมาใหม่ มีเงื่อนไขอะไรบ้างก็ควรอธิบายมาทั้งหมดครับ

ในคอลัม E F G ใช้ VLOOKUP ดึงข้อมูลมาจาก ชีท PL#1 PL#2 PL#3
เมื่อดึงมาแล้วจะรู้ว่า เบอร์อาหารแต่ละเบอร์อยู่ใน PL ไหนบ้าง คำตอบที่ต้องการคือ เมื่อรู้ว่าเบอร์อาหารอยู่ PL ไหนแล้ว ต้องการให้ เลือกว่าจะลง Number Tank ไหน โดย โดยมีเงื่อนไข ว่าถ้าเลือกแล้ว ห้ามเลือกซ้ำ เช่น E3 เลือก H แล้ว E4 จะไม่สามารถเลือก H ได้อีก แต่จะต้องเลือกช่องถัดไป ตามเงื่อนไขใน Sheet 2

คอลัม E เลือกได้เฉพาะ คอลัม H I J O R

คอลัม F เลือกได้เฉพาะ คอลัม J K L

คอลัม G เลือกได้เฉพาะ คอลัม I M N O P Q R S T



ขอบคุณค่ะ

เลือกได้หมายถึงให้กรอกเลข 1 ใช่หรือไม่ครับ หากใช่ จากคำอธิบายด้านล่าง

oo-beer-oo wrote:คอลัม E เลือกได้เฉพาะ คอลัม H I J O R

เซลล์ E9 จะต้องคีย์เลข 1 ในเซลล์ไหนครับ

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

ลบเครื่องหมายดอกจันที่ H2:T2 ทิ้งไป จากนั้น เซลล์ H3 คีย์สูตร

=CHOOSE(LOOKUP(2,1/($E3:$G3<>0),{1,2,3}),IF($E3=0,0,IF(COUNTIF(Sheet2!$B$3:$N$3,H$2),IF(SUMPRODUCT(--ISNUMBER(MATCH(Sheet2!$B$3:$N$3,$H$2:H$2,0)))=MOD((COUNTIF($E$3:$E3,"<>0")-1),5)+1,1,0),0)),IF($F3=0,0,IF(COUNTIF(Sheet2!$B$4:$N$4,H$2),IF(SUMPRODUCT(--ISNUMBER(MATCH(Sheet2!$B$4:$N$4,$H$2:H$2,0)))=MOD((COUNTIF($F$3:$F3,"<>0")-1),3)+1,1,0),0)),IF($G3=0,0,IF(COUNTIF(Sheet2!$B$5:$N$5,H$2),IF(SUMPRODUCT(--ISNUMBER(MATCH(Sheet2!$B$5:$N$5,$H$2:H$2,0)))=MOD((COUNTIF($G$3:$G3,"<>0")-1),9)+1,1,0),0)))

Enter > Copy ไปด้านขวาและลงด้านล่าง

จากที่อธิบายและตัวอย่างข้อมูลที่แนบมา

oo-beer-oo wrote:คอลัม E เลือกได้เฉพาะ คอลัม H I J O R

คอลัม F เลือกได้เฉพาะ คอลัม J K L

คอลัม G เลือกได้เฉพาะ คอลัม I M N O P Q R S T

E9 ไม่น่าจะใช่คอลัมน์ H ครับ

ขอบคุณน่ะค่ะ
จะลองไปทำดู แล้วจะมาบอกผลลัพธ์น่ะค่ะ

ขอโทษน่ะค่ะ คือลองนำมาใช้แล้ว แต่มันต้องนำมาปรับใช้ค่ะ แต่ไม่เข้าใจคำสั่งบางอย่าง เลยอยากรบกวนถามหน่อยค่ะ ขอบคุณมากๆค่ะ

ควรเขียนสูตรในส่วนที่สงสัยลงมาในฟอรัมครับ

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

จากฟังก์ชั่นที่ถามมา ศึกษาได้ตาม Link นี้ครับ สำหรับฟังก์ชั่นต่าง ๆ สามารถศึกษาได้จาก Link นี้ครับ Excel Functions

=CHOOSE(LOOKUP(2,1/($E3:$G3<>0),{1,2,3}),IF($E3=0,0,IF(COUNTIF(Sheet2!$B$3:$N$3,H$2),IF(SUMPRODUCT(--ISNUMBER(MATCH(Sheet2!$B$3:$N$3,$H$2:H$2,0)))=MOD((COUNTIF($E$3:$E3,"<>0")-1),5)+1,1,0),0)),IF($F3=0,0,IF(COUNTIF(Sheet2!$B$4:$N$4,H$2),IF(SUMPRODUCT(--ISNUMBER(MATCH(Sheet2!$B$4:$N$4,$H$2:H$2,0)))=MOD((COUNTIF($F$3:$F3,"<>0")-1),3)+1,1,0),0)),IF($G3=0,0,IF(COUNTIF(Sheet2!$B$5:$N$5,H$2),IF(SUMPRODUCT(--ISNUMBER(MATCH(Sheet2!$B$5:$N$5,$H$2:H$2,0)))=MOD((COUNTIF($G$3:$G3,"<>0")-1),9)+1,1,0),0)))

อยากให้ช่วยอธิบาย ที่มาที่ไปของสูตรที่เปนตัวหนังสือสีแดงหน่อยค่ะนี้หน่อย ขอบคุณค่ะ ^^

สูตรนั้นซับซ้อนและเข้าใจค่อนข้างยาก สืบเนืองมาจากโจทย์ยาก

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

จาก =CHOOSE(LOOKUP(2,1/($E3:$G3<>0),{1,2,3}),IF($E3=0,0,IF(COUNTIF(Sheet2!$B$3:$N$3,H$2),IF(SUMPRODUCT(--ISNUMBER(MATCH(Sheet2!$B$3:$N$3,$H$2:H$2,0)))=MOD((COUNTIF($E$3:$E3,"<>0")-1),5)+1,1,0),0)),IF($F3=0,0,IF(COUNTIF(Sheet2!$B$4:$N$4,H$2),IF(SUMPRODUCT(--ISNUMBER(MATCH(Sheet2!$B$4:$N$4,$H$2:H$2,0)))=MOD((COUNTIF($F$3:$F3,"<>0")-1),3)+1,1,0),0)),IF($G3=0,0,IF(COUNTIF(Sheet2!$B$5:$N$5,H$2),IF(SUMPRODUCT(--ISNUMBER(MATCH(Sheet2!$B$5:$N$5,$H$2:H$2,0)))=MOD((COUNTIF($G$3:$G3,"<>0")-1),9)+1,1,0),0)))

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

ซึ่งหมายถึง ให้ดูว่า LOOKUP(2,1/($E3:$G3<>0),{1,2,3}) ได้ค่าใด ซึ่งจะมีได้ 3 ค่าเท่านั้นคือ 1 หรือ 2 หรือ 3

หากได้ค่า 1 ให้คำนวณตามสูตร IF($E3=0,0,IF(COUNTIF(Sheet2!$B$3:$N$3,H$2),IF(SUMPRODUCT(--ISNUMBER(MATCH(Sheet2!$B$3:$N$3,$H$2:H$2,0)))=MOD((COUNTIF($E$3:$E3,"<>0")-1),5)+1,1,0),0))

หากได้ค่า 2 ให้คำนวณตามสูตร ,IF($F3=0,0,IF(COUNTIF(Sheet2!$B$4:$N$4,H$2),IF(SUMPRODUCT(--ISNUMBER(MATCH(Sheet2!$B$4:$N$4,$H$2:H$2,0)))=MOD((COUNTIF($F$3:$F3,"<>0")-1),3)+1,1,0),0))

หากได้ค่า 3 ให้คำนวณตามสูตร IF($G3=0,0,IF(COUNTIF(Sheet2!$B$5:$N$5,H$2),IF(SUMPRODUCT(--ISNUMBER(MATCH(Sheet2!$B$5:$N$5,$H$2:H$2,0)))=MOD((COUNTIF($G$3:$G3,"<>0")-1),9)+1,1,0),0))

จาก LOOKUP(2,1/($E3:$G3<>0),{1,2,3}) หมายถึง ให้หาค่า 2 จาก 1/($E3:$G3<>0) แล้วนำค่าที่ตรงกันใน Array {1,2,3} มาแสดง ศึกษาและทำความเข้าใจเพิ่มจากที่นี่ wordpress/last-cell/

จากตัวอย่างสูตรที่เป็นส่วนประกอบของ Choose ที่เป็น If เช่น IF($E3=0,0,IF(COUNTIF(Sheet2!$B$3:$N$3,H$2),IF(SUMPRODUCT(--ISNUMBER(MATCH(Sheet2!$B$3:$N$3,$H$2:H$2,0)))=MOD((COUNTIF($E$3:$E3,"<>0")-1),5)+1,1,0),0))

หมายถึง หาก $E3=0 เป็นจริง ให้แสดงค่า 0 หากไม่เป็นจริงให้แสดงผลลัพธ์ของ IF(COUNTIF(Sheet2!$B$3:$N$3,H$2),IF(SUMPRODUCT(--ISNUMBER(MATCH(Sheet2!$B$3:$N$3,$H$2:H$2,0)))=MOD((COUNTIF($E$3:$E3,"<>0")-1),5)+1,1,0),0)

จากสูตร IF(COUNTIF(Sheet2!$B$3:$N$3,H$2),IF(SUMPRODUCT(--ISNUMBER(MATCH(Sheet2!$B$3:$N$3,$H$2:H$2,0)))=MOD((COUNTIF($E$3:$E3,"<>0")-1),5)+1,1,0),0) หมายถึง หาก COUNTIF(Sheet2!$B$3:$N$3,H$2) เป็นจริงให้แสดงผลลัพธ์ของ IF(SUMPRODUCT(--ISNUMBER(MATCH(Sheet2!$B$3:$N$3,$H$2:H$2,0)))=MOD((COUNTIF($E$3:$E3,"<>0")-1),5)+1,1,0) หากไม่เป็นจริงให้แสดงค่า 0

จากสูตร IF(SUMPRODUCT(--ISNUMBER(MATCH(Sheet2!$B$3:$N$3,$H$2:H$2,0)))=MOD((COUNTIF($E$3:$E3,"<>0")-1),5)+1,1,0) หมายถึง หาก SUMPRODUCT(--ISNUMBER(MATCH(Sheet2!$B$3:$N$3,$H$2:H$2,0)))=MOD((COUNTIF($E$3:$E3,"<>0")-1),5)+1 เป็นจริง ให้แสดงค่า 1 หากไม่จริงให้แสดงค่า 0

จากสูตร SUMPRODUCT(--ISNUMBER(MATCH(Sheet2!$B$3:$N$3,$H$2:H$2,0))) หมายถึง ให้หาผลรวมของ --ISNUMBER(MATCH(Sheet2!$B$3:$N$3,$H$2:H$2,0)))

จากสูตร MOD((COUNTIF($E$3:$E3,"<>0")-1),5)+1 หมายถึง ให้หาเศษจากการหาร (COUNTIF($E$3:$E3,"<>0")-1) ด้วย 5 ได้ผลลัพธ์เท่าไรแล้วค่อยบวกด้วย 1

จากสูตร COUNTIF($E$3:$E3,"<>0")-1 หมายถึงให้นับว่า $E$3:$E3 มีค่าไม่เท่ากับ 0 กี่จำนวน ได้ผลลัพธ์เท่าใดให้หักออกด้วย 1

ลองค่อย ๆ ทำความเข้าใจ การอ่านสูตรให้อ่านจากด้านนอกเข้าด้านใน แต่การแทนค่าให้แทนค่าจากด้านในออกด้านนอกครับ

ขอบคุณมากค่ะ จะพยายามทำความเข้าใจน่ะค่ะ