เลือช่วงวันที่เหลืออยู่มาแสดงตามช่วงวันที่กำหนด

[March201711]

อยากสอบถามว่าจะเลือกช่วงเวลาที่เหลืออยู่มาแสดงตามcolumn ที่กำหนดไว้ ตั้งแต่ column b4 ถึง b18 โดยดึงข้อมูลจาก column b25 ถึง n90 โดยดูจาก วันที่ remain อยู่ว่าเป้น fixing หรือ maturity ค่ะ และรวมค่ามาแสดงใน column d4 ถึง j18 ตามเอกสารที่แนบค่ะ

[parakorn]

ผมอ่านแล้วไม่เข้าใจเลยครับ ขอคำอธิบายเพิ่มเติมได้ไหมครับ
ผลลัพท์ที่ต้องการมีช่องไหนบ้างครับ B4 ถึง B18 ใช่ไหมครับ แล้วลองยกตัวอย่างวิธีคิดผลลัพท์ สัก2-3ตัวอย่างได้ไหมครับ
หรือผลลัพท์ที่ต้องการอยู่ในตาราง D4 ถึง J18 ครับ แล้วมีเงื่อนไขอย่างไรบ้าง ถึงจะไป Sum ช่วงนั้นๆ

[March201711]

แบบนี้ค่ะ อยากให้จับช่วงเวลาที่เหลืออยู่ใน column M3 "Remain" ว่าอยู่ใน Maturity หรือ Fixing แล้วมาแสดงตาม Column B ตั้งแต่ B4 ถึง B18 (High light สีเขียว) ค่ะ ส่วนคำตอบคือ อยู่ใน Column D4 ถึง J18 (High light สีฟ้า) เพราะใช้ตาดูว่าอยู่ในช่วงเวลาไหน ต้อง sum แบบบวกมือเองค่ะ

Column B4 ถึง B9 จะจับกับ Code ที่อยู่ใน Row25
Column B11 ถึง B18 จะจับกับ Code ที่อยู่ใน Row78
ส่วนจะหาอายุที่เหลือต้องดูว่า Remain ที่อยู่ ColumnM เป็น Maturity หรือ Fixing ในวันที่เหลืออยู่ตรงกับ Column J หรือ Column K
เช่น


Code 2105010 มี Remain เป็น Maturity ต้องไปดูที่ E25 อยู่เป็น code #2105010 แล้วไปดูต่อใน Column J เพราะเป็น Maturity จากนั้นใส่ช่วงวันที่เหลือใน D4 ว่า 1 ถึง 7 วัน รวมกันได้ยอด 20,000,000.00 มาใส่ในช่อง D4 ค่ะ ตามที่ high light สีเหลืองค่ะ

Code 2105010 มี Remain เป็น Maturity ต้องไปดูที่ E25 อยู่เป็น code #2105010 แล้วไปดูต่อใน Column J เพราะเป็น Maturity จากนั้นใส่ช่วงวันที่เหลือใน E4 ว่า 8 ถึง 31 วัน รวมกันได้ยอด 199,600,000.00 มาใส่ในช่อง E4 ค่ะ ตามที่ high light สีเหลืองค่ะ

Code 2105010 มี Remain เป็น Maturity ต้องไปดูที่ E25 อยู่เป็น code #2105010 แล้วไปดูต่อใน Column J เพราะเป็น Maturity จากนั้นใส่ช่วงวันที่เหลือใน F4 ว่า 32 ถึง 92 วัน รวมกันได้ยอด 199,600,000.00 มาใส่ในช่อง F4 ค่ะ ตามที่ high light สีเหลืองค่ะ
และไปเรื่อยๆ จนถึง J4 ที่มากกว่า 1096 วันค่ะ

[snasui]

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

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

สิ่งที่ต้องปรับในเบื้องต้นมีดังนี้

1. ข้อความใน D1:J1 ที่เป็นการระบุช่วงของวัน ให้ปรับเป็น 1:7, 8:31 ฯลฯ หรือ 1-7, 8-31 ฯลฯ หรือเขียนเป็นคนละบรรทัด เช่น 1 อยู่บรรทัดบนและ 7 อยู่บรรทัดล่าง เป็นต้น หากปรับเป็นเช่นนี้จะใช้สูตรได้ง่ายกว่าเขียนเป็นข้อความลักษณะเดิม
2. Code ใน B4:B18 จะต้องมีรูปแบบเหมือนกับ Code ใน E25:I25 และเหมือนกับ E78:K78, N78 หากจะนำหน้าด้วย # ก็ต้องใช้เหมือนกันทั้งหมดในทุก ๆ ตำแหน่งที่ Code นั้นปรากฎอยู่
3. ค่าใน M4:M18 จะต้องเหมือนกันกับค่าใน J25:K25, L78:M78 หากจะนำหน้าด้วย Remain ก็ต้องใช้เหมือนกันทั้่งหมดในทุกตำแหน่ง

[March201711]

ปรับไฟล์ใหม่ตามที่อาจารย์แนะนำแล้วค่ะ

[snasui]

ตัวอย่างสูตรที่ D4 ตามด้านล่างครับ

=IFERROR(SUMIFS(INDEX($E$26:$I$73,0,MATCH(--$B4,$E$25:$I$25,0)),INDEX($J$26:$K$73,0,MATCH($M4,$J$25:$K$25,0)),">="&LEFT(D$1,FIND("-",D$1)-1),INDEX($J$26:$K$73,0,MATCH($M4,$J$25:$K$25,0)),"<="&MID(D$1,FIND("-",D$1)+1,4)),0)

Enter > Copy ไปด้านขวาและลงด้านล่างถึง J9 > ปรับใช้กับช่วง D11:J18

[March201711]

ทำได้ช่วงที่ D4 ถึง J9 ได้แล้วค่ะ แต่พอปรับมาใช้ช่วง D11 ถึง J18 แล้ว ไม่ได้ยอดตรงตาม sheet Sum_OS_Date_1 สักcolumn เลยค่ะ ก็ปรับเปลี่ยนสูตรตามช่วง (high light สีเหลือง) ตั้งแต่D11 ถึง J18 แล้วนะค่ะ

[snasui]

ตรวจสอบคำตอบตัวอย่างใหม่ว่าตรงตามที่ควรจะเป็นหรือไม่ เช่น D11 คำตอบคือ 9,143,635.59 หรือไม่

หากพิจารณาจากโจทย์ คำตอบคือ 0 ไม่ใช่ 9,143,635.59 เพราะค่า Maturity ไม่ได้เป็นค่าตั้งแต่ 1-7 ครับ

[March201711]

อ๋อ ใช่ค่ะ อาจารย์ทำถูกแล้วค่ะ คำตอบใน sheet Sum_OS_Date_1 sheet ไม่ถูกค่ะ ขอบคุณที่อาจารย์ชี้แนะ อาจารย์ช่วยอธิบายสูตรได้ไหมคะ สูตรซับซ้อนมากเลยค่ะ ไม่เข้าใจ =IFERROR(SUMIFS(INDEX($E$26:$I$73,0,MATCH(--$B4,$E$25:$I$25,0)),INDEX($J$26:$K$73,0,MATCH($M4,$J$25:$K$25,0)),">="&LEFT(D$1,FIND("-",D$1)-1),INDEX($J$26:$K$73,0,MATCH($M4,$J$25:$K$25,0)),"<="&MID(D$1,FIND("-",D$1)+1,4)),0)
แล้วทำไมต้อง +1 เข้าไปด้วยคะ

[snasui]

ผมจะแปลให้เป็นแนวทางสำหรับในส่วนที่ดูว่าซับซ้อนวุ่นวายครับ

จากสูตร =IFERROR(SUMIFS(INDEX($E$26:$I$73,0,MATCH(--$B4,$E$25:$I$25,0)),INDEX($J$26:$K$73,0,MATCH($M4,$J$25:$K$25,0)),">="&LEFT(D$1,FIND("-",D$1)-1),INDEX($J$26:$K$73,0,MATCH($M4,$J$25:$K$25,0)),"<="&MID(D$1,FIND("-",D$1)+1,4)),0)

แปลว่าหากผลลัพธ์ของ SUMIFS(INDEX($E$26:$I$73,0,MATCH(--$B4,$E$25:$I$25,0)),INDEX($J$26:$K$73,0,MATCH($M4,$J$25:$K$25,0)),">="&LEFT(D$1,FIND("-",D$1)-1),INDEX($J$26:$K$73,0,MATCH($M4,$J$25:$K$25,0)),"<="&MID(D$1,FIND("-",D$1)+1,4)) เป็นค่าผิดพลาดให้แสดงค่า 0

จากสูตร SUMIFS(INDEX($E$26:$I$73,0,MATCH(--$B4,$E$25:$I$25,0)),INDEX($J$26:$K$73,0,MATCH($M4,$J$25:$K$25,0)),">="&LEFT(D$1,FIND("-",D$1)-1),INDEX($J$26:$K$73,0,MATCH($M4,$J$25:$K$25,0)),"<="&MID(D$1,FIND("-",D$1)+1,4))

แปลว่าให้รวมยอดของช่วง INDEX($E$26:$I$73,0,MATCH(--$B4,$E$25:$I$25,0)) โดยมีสองเงื่อนไขคือ ช่วงของ INDEX($J$26:$K$73,0,MATCH($M4,$J$25:$K$25,0)) จะต้องมีค่าเท่ากับ ">="&LEFT(D$1,FIND("-",D$1)-1) และ ช่วงของ INDEX($J$26:$K$73,0,MATCH($M4,$J$25:$K$25,0)) จะต้องมีค่าเท่ากับ "<="&MID(D$1,FIND("-",D$1)+1,4)

โดยที่ INDEX($E$26:$I$73,0,MATCH(--$B4,$E$25:$I$25,0)) คือช่วงข้อมูลในแนวคอลัมน์ แปลสูตรได้ว่า จากช่วง $E$26:$I$73 ให้นำคอลัมน์ที่เป็นผลลัพธ์ของ MATCH(--$B4,$E$25:$I$25,0) มาแสดง ซึ่ง MATCH(--$B4,$E$25:$I$25,0) หมายถึง ให้หาว่าค่าของ --$B4 อยู่ในลำดับที่เท่าไรของ $E$25:$I$25 หากผลลัพธ์จาก Match เป็น 2 ภาพรวมสูตรจะได้เป็น INDEX($E$26:$I$73,0,2) แปลว่าให้แสดงคอลัมน์ที่ 2 ของตาราง $E$26:$I$73 นั่นคือจะแสดงเป็น F2:F73 นั่นเอง

สูตร Index ในช่วงอื่น ๆ ก็จะแปลในลักษณะเดียวกัน ส่วนที่เหลือเป็นฟังก์ชั่นง่าย ๆ ที่ควรจะศึกษาให้เข้าใจ ใช้ให้เป็นเช่น Mid, Left, Sumifs, Find จะได้เข้าใจว่าทำไมต้อง -1 โดย Download ไฟล์ที่ผมแจกไว้ในกระทู้นี้ไปศึกษาแต่ละฟังก์ชั่นได้ตามสะดวกครับ viewtopic.php?f=9&t=13233

นอกจากนี้ควรฝึกประเมินสูตร โดยคลิกเซลล์ที่มีสูตร ลากคลุมส่วนทีต้องการดูคำตอบใน Formula Bar แล้วกดแป้น F9 หรือจะใช้การประเมินสูตรด้วยการเข้าเมนู Formula > Evaluate Formula > คลิก Evaluate ซ้ำ ๆ เพื่อแสดงการแปลสูตรไปทีละลำดับจะทำให้เข้าใจได้ง่ายขึ้นครับ

[March201711]

ขอบคุณอาจารย์มากค่ะ จะพยายามทำความเข้าใจและศึกษามากๆค่ะ แต่พอเจอปัญหาก็จะนึกไม่ออกค่ะ ว่าจะต้องปรับใช้กับสูตรไหน ปรับซ้อนสูตรอย่างไร พอปรับแล้วก้อติดerror ทุกที ต้องศึกษาจากเวปของอาจารย์ ชอบเวปนี้มากๆและเป็นประโยชน์กับทุกคนที่ศึกษาจริงๆค่ะ สุดยอดเลยค่ะ