Snasui Excel Forum

สวัสดีค่ะคุณคนควร
ตอนนี้คิดไม่ออกจะเริ่มอย่างไร คือมีงานอยู่ 2 ไฟล์
1.file ชื่อ File usage.xlsx เป็นข้อมูลดิบโหลดจากระบบ SAP
2. file ชื่อ Plan.xlsx เป็น Templete ที่สร้างเพื่อจัดการข้อมูลในรูปแบบ schedule plan
สิ่งที่ต้องทำคือ นำ Material ทั้งหมดมาใส่ใน templete แล้วดึงค่า material, plan และ ยอดของแต่ละวันที่ตามด้านบน (เซลล์สีแดง) มาใส่ใน Templete ทั้งหมด (ใส่มีแดงไว้)
พอจะใช้สูตรอะไรได้บ้างคะ ช่วยแนะนำด้วยค่ะ

ขอบคุณค่ะ
p_d

ขอข้อมูลเพิ่มเติมหน่อยครับ เพื่ออาจารย์จะได้ช่วยเหลือได้ง่ายขึ้น
1. ในไฟล์ File usage คอลัมน์ G กรองเฉพาะ type R ไว้ แสดงว่าต้องการดึงข้อมูลเฉพาะ Material Type R ใช่ไหมครับ?
2. ใน Template มีตารางลักษณะดังรูปด้านล่าง และเรียงต่อกันลงไปเรื่อยๆ เป็นชุดๆ อยากถามว่าชุดหนึ่งๆ จะมี Material แค่ตัวเดียวใช่ไหมครับ? เช่น ถ้าจากรูปบนนี้ก็...
จะมีชื่อ Material ในเซลล์ A9 (A10:A17 ว่าง)
จะมีชื่อ Plant ในเซลล์ C9 (C10:C17 ว่าง)

3. Material ที่มีข้อมูลทุกวันเป็น 0 ต้องดึงมาด้วยหรือไม่ครับ?
4. ข้อมูลที่ดึง ไม่จำเป็นต้องเรียงลำดับใช่ไหมครับ? หมายถึงว่า เรียงลำดับชื่อ plant หรือชื่อ material ตามตัวอักษร

ถ้าลองใส่ตัวอย่างมาให้ดูใน Template สัก 3-4 ตัวอย่าง จะดีมากเลยครับ

ข้อมูลเพิ่มเติมนะคะ
1. ใช่ค่ะกรองเฉพราะ type R
2.ชุดหนึ่งจะมี Material แค่ตัวเดียวค่ะ ตามที่คุณ DhitiBank เข้าใจเลยค่ะ
3. Material ที่เป็น 0 ให้แสดงด้วยค่ะ
4.ต้องการให้เรียงชือตาม material และ plant ตามลำดับค่ะ

แนบรูปเพิ่มเติมมาให้ดูเป็นตัวอย่างค่ะ

ขอบคุณค่ะ
p_d

ทดลองแบบนี้ครับ
1. เปิดไฟล์ทั้ง 2 ขึ้นมาด้วยกัน
2. ที่ไฟล์ Template "Plan.xlsx" ให้สร้างฟอร์มเผื่อเอาไว้มากๆ
3. ไฟล์ Plan.xlsx ชีท1
3.1 ที่ A9 คีย์
=IFERROR(INDEX('[File usage.xlsx]Sheet1'!$B$2:$B$200,SMALL(
IF('[File usage.xlsx]Sheet1'!$G$2:$G$200="R",ROW('[File usage.xlsx]Sheet1'!$G$2:$G$200)-ROW('[File usage.xlsx]Sheet1'!$G$2)+1),
INT((ROWS($A$9:A9)-1)/9)+1)),"")
กด Ctrl+Shift ค้างไว้ แล้วกด Enter

3.2 ที่ C9 คีย์
=IFERROR(INDEX('[File usage.xlsx]Sheet1'!$A$2:$A$200,SMALL(
IF('[File usage.xlsx]Sheet1'!$G$2:$G$200="R",ROW('[File usage.xlsx]Sheet1'!$G$2:$G$200)-ROW('[File usage.xlsx]Sheet1'!$G$2)+1),
INT((ROWS($A$9:A9)-1)/9)+1)),"")
กด Ctrl+Shift ค้างไว้ แล้วกด Enter

3.3 ที่ D15 คีย์
=IFERROR(INDEX('[File usage.xlsx]Sheet1'!$J$2:$AE$54,SMALL(
IF("R"='[File usage.xlsx]Sheet1'!$G$2:$G$200,ROW('[File usage.xlsx]Sheet1'!$G$2:$G$200)-1),
INT((ROWS(D$9:D15)-1)/9)+1),
MATCH(D$8&"",LEFT('[File usage.xlsx]Sheet1'!$J$1:$AE$1,LEN('[File usage.xlsx]Sheet1'!$J$1:$AE$1)-8),0)),0)
กด Ctrl+Shift ค้างไว้ แล้วกด Enter
แล้วคัดลอกไปทางขวาจนสุดตาราง

3.4 เอาเม้าส์คลิกที่เซลล์ B1 กด Ctrl+Shift+ลูกศรลง จะเลือกข้อมูลคอลัมน์ B ในตาราง
ไปที่ Data --> Filter จะปรากฎตัวกรองขึ้นตรงคำว่า "TIME" ให้กรองเอาเฉพาะ "AM : 8.00" และ "use" เท่านั้น

3.5 คัดลอกสูตรที่ A9 ไปวางไว้ในคอลัมน์ A ถัดลงไป ที่อยู่บรรทัดเดียวกับ AM.... ทำเช่นนี้กับสูตรในคอลัมน์ C

3.6 คัดลอกสูตรใน D15:AI15 ไปวางไว้ในแถวถัดลงไป ที่อยู่บรรทัดเดียวกับ use

ปล. หรืออาจทำทีละอย่างก็ได้ กรองเอาเฉพาะ AM... ก่อน คัดลอกสูตรเสร็จแล้วค่อยกรอง use เพื่อคัดลอกสูตรต่อ แต่ตอนจะวางสูตรอาจต้องกด Ctrl+g --> Special... --> visible cell only ก่อนนะครับ

ลองดูตามไฟล์แนบครับ

รบกวนอธิบายสูตรให้เข้าใจด้วยได้ไหมคะ และถ้าหากข้อมูลในไฟล์ File usage.xlsx เริ่มจากวันที่ 1 ของแต่ละเดือนต้องปรับสูตรตรงไหนบ้าง เพราะจากไฟล์ที่ยกตัวอย่างเริ่มจากวันที่ 10
ลองปรับสูตรแล้วแต่ตัวเลขไม่ตรงค่ะ....รบกวนด้วยนะคะ

ขอบคุณค่ะ
p_d

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

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

อ่อ ที่สำคัญคือ รูปแบบการพิมพ์วันที่ในข้อมูลดิบ ต้องเป็นรูปแบบเดิมนะครับคือ
d.mm.yyyy
หากเป็นรูปแบบอื่นนอกจากนี้ สูตรอาจผิดพลาด

สูตรด้านบนทั้ง 3 สูตรมีรูปแบบคล้ายกันครับ แต่สูตรสุดท้ายที่เอาไว้ดึงจำนวนจะมีอะไรเพิ่มมานิดหน่อย เลยจะอธิบายสูตรสุดท้ายเลยนะครับ
ขอแยกเป็นแบบนี้จะได้ดูสะดวก
(5) =IFERROR(.......(4)......,0)
(4) INDEX('[File usage.xlsx]Sheet1'!$J$2:$AE$54,....(2)....,....(3).....)
(2) SMALL(.....(1)...,INT((ROWS(D$9:D15)-1)/9)+1)
(1) IF("R"='[File usage.xlsx]Sheet1'!$G$2:$G$200,ROW('[File usage.xlsx]Sheet1'!$G$2:$G$200)-1)
(3) MATCH(D$8&"",LEFT('[File usage.xlsx]Sheet1'!$J$1:$AE$1,LEN('[File usage.xlsx]Sheet1'!$J$1:$AE$1)-8),0)

(1) IF จากเงื่อนไขสำคัญคือ เอาเฉพาะ type R จึงเอาคอลัมน์ G ในข้อมูลดิบมาตรวจก่อนว่าลำดับไหนเท่ากับ R บ้าง หากเท่าก็ให้แสดงด้วยลำดับจาก
ROW('[File usage.xlsx]Sheet1'!$G$2:$G$200)-1
ซึ่งหากคลุมช่วงนี้แล้วกด F9 ดู มันคือเลขลำดับครับ
{2;3;4;...;200}-1 = {1;2;...;199}
สมมติว่ามีแค่ G2 ตำแหน่งเดียวที่เป็น R สูตร IF ก็จะคำนวณได้ผลว่า
{1;False;False;...;False}
เพื่อเอาไปใช้ต่อในถัดไป

(2) Small(....,k) จะเลือกค่าที่น้อยที่สุดในข้อ (1) เป็นลำดับที่ k (เช่น k=1 ก็จะเลือกค่าน้อยสุดเป็นลำดับที่ 1) ใช้เพื่อเลือกลำดับข้อมูลอันแรก (ไล่จากด้านบนลงล่าง) ที่พบว่าเป็น Type R
ตรง k ซึ่งบอกว่าให้เลือกค่าน้อยสุดที่เท่าไรหามาจากสูตร
INT((ROWS(D$9:D15)-1)/9)+1
เพราะ template ของคุณมีความสูง 9 แถว ฉะนั้นหลักการคือเวลาคัดลอกสูตรลงไปด้านล่าง จะต้องทำให้ค่า k เพิ่มขึ้นทีละ 1 เมื่อคัดลอกลงไปทุกๆ 9 แถว งงไหมครับ ตัวอย่างเช่นหากคีย์ INT((rows(D$9:D9)-1)/9)+1 ในเซลล์ D9 แล้วคัดลอกสูตรลงไปถึงเซลล์ D26
ผลจากสูตร Rows() คือ {1;2;3;...;18} (คือที่แถว 9 จะได้ค่า 1 ที่แถว 26 จะได้ค่า 18)
ผลจากสูตร Rows()-1 คือ {0;1;2;...;17}
ผลจากสูตร (Rows()-1)/9 คือ {0;1/9;2/9;...;17/9}
ผลจากสูตร Int(Rows()-1)/9) คือ {0;0;0;...;1}
สูตร int จะปัดเศษทิ้งครับ เอาแค่จำนวนเต็ม นั่นหมายความว่าพอเอาไปบวกหนึ่งแล้ว 9 แถวแรกจะได้ 1 หมด อีก 9 แถวถัดมาก็ได้ 2 สูตรจะได้รู้ว่าเมื่อสูตรถูกคัดลอกไปอยู่ใน template ชุดไหนก็ต้องเอาข้อมูล material ชุดที่เท่านั้นมาแสดงครับ
สูตร Small ใช้เพื่อหาว่าต้องเอาข้อมูลแถวไหน ในข้อมูลดิบมาแสดง

(3) Match ใช้ตรวจวันที่ในหัว template กับวันที่ในข้อมูลดิบครับ โดยจากสูตร
LEFT('[File usage.xlsx]Sheet1'!$J$1:$AE$1,LEN('[File usage.xlsx]Sheet1'!$J$1:$AE$1)-8)
จะตัดเอาเฉพาะตัวเลข "วันที่" ในหัวตารางข้อมูลดิบ จากนั้น Match ก็จะมาเทียบกับเลข "วันที่" ในหัวตาราง template ว่าตรงกับคอลัมน์ที่เท่าไร
สูตร Match ใช้เพื่อหาว่าต้องเอาข้อมูลคอลัมน์ไหนในข้อมูลดิบมาแสดง

(4) Index(Array,x,[y]) หาก array มีหลายแถวหลายคอลัมน์ ต้องระบุทั้ง x และ y ให้ครบ สูตรนี้จะเอาข้อมูลใน array มาแสดงโดยเป็นข้อมูลแถวที่ x คอลัมน์ที่ y
ค่า x ได้จากข้อ (2)
ค่า y ได้จากข้อ (3) ครับ

(5) Iferror เอาไว้ดักค่าผิดพลาดครับ หากคัดลอกลงไปมากๆ จนเกินจำนวนที่ข้อมูลดิบจะมี สูตรจะแสดงค่าผิดพลาดเพราะหาข้อมูลมาแสดงไม่ได้แล้ว ก็ใช้สูตรนี้ดักไว้ให้แสดงค่า 0 ครับ

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

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

อ่อ ที่สำคัญคือ รูปแบบการพิมพ์วันที่ในข้อมูลดิบ ต้องเป็นรูปแบบเดิมนะครับคือ
d.mm.yyyy
หากเป็นรูปแบบอื่นนอกจากนี้ สูตรอาจผิดพลาด

ถูกต้องแล้วค่ะ ใส่วันที่ผิดรูปแบบจึงหาไม่เจอ...

ขอบคุณค่ะ
p_d

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

ขอบคุณมากค่ะ
p_d

มาช่วยเสริมครับ

จาก

DhitiBank wrote:(1) IF("R"='[File usage.xlsx]Sheet1'!$G$2:$G$200,ROW('[File usage.xlsx]Sheet1'!$G$2:$G$200)-1)

ควรปรับเป็น

IF("R"='[File usage.xlsx]Sheet1'!$G$2:$G$200,ROW('[File usage.xlsx]Sheet1'!$G$2:$G$200)-ROW('[File usage.xlsx]Sheet1'!$G$2)+1

ที่ควรเขียนเช่นนี้เพื่อความยืดหยุ่นกรณีที่ผู้ใช้งานมีการแทรกบรรทัดในไฟล์ต้นทาง สมมุติกรณีที่มีการแทรก 1 บรรทัด สูตรเดิมจะกลายเป็น

IF("R"='[File usage.xlsx]Sheet1'!$G$3:$G$201,ROW('[File usage.xlsx]Sheet1'!$G$3:$G$201)-1)

ซึ่งจะเกิดความผิดพลาดได้เพราะค่าลำดับไม่ได้เริ่มที่เลข 1 ในขณะที่สูตรใหม่หากมีการแทรก 1 บรรทัดจะกลายเป็น

IF("R"='[File usage.xlsx]Sheet1'!$G$3:$G$201,ROW('[File usage.xlsx]Sheet1'!$G$3:$G$201)-ROW('[File usage.xlsx]Sheet1'!$G$3)+1

ซึ่งจะเห็นว่ากรณีที่มีการแทรกบรรทัด ก็จะยังเริ่มด้วยลำดับที่ 1 ตลอดไป

จริงด้วย ลืมคิดไปเลย ขอบคุณอาจารย์มากครับ

ขอบพระคุณทั้งสองท่านที่ให้ความรู้เป็นอย่างดีด้วยค่ะ
p_d