Excel Forum

จากไฟล์แนบ อยากหาผมรวมเวลาการทำงานของทีม T1 และ T2 ว่าใช้เวลาดำเนินการเท่าไร

Column B = ทีมที่แก้ไขข้อมูลงานนั้นๆ
Column C = เวลาที่มีการแก้ไขข้อมูลงาน

จากไฟล์อยากหาเวลาการทำงานของ T2 โดย
Cell C2 = เวลาที่ทีม T1 เปิดใบงาน
Cell C3 = เวลาที่ทีม T1 แก้ไข Description
Cell C4 = เวลาที่ทีม T1 แก้ไข Case Owner >> ซึ่งหมายคว่าว่าส่งงานไปให้ T2
Cell C5 = เวลาที่ทีม T2 Acknowledge งานนั้น
Cell C6 = เวลาที่ทีม T2 แก้ไขงานเสร็จและส่งกลับไปยัง T1
Cell C7 = เวลาที่ทีม T1 ปิดใบงาน

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

รวมเวลาการออกใบงานจากทีม T1 เริ่มนับเวลาตั้งแต่
Edited By = T1
Field / Event = Created.
Field / Event = Closed.

รวมเวลาการแกไขใบงานจากทีม T2 เริ่มนับเวลาตั้งแต่
Edited By = T2
Field / Event = Action Owner , New Value = T2_Owner_Name
Field / Event = Case Owner New Value =T1

ตัวอย่างคำตอบแบบ Column M ดังไฟล์แนบค่ะ

เข้าใจว่าต้องการหาทั้งตารางในคอลัมน์ H:M

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

1. ที่ H2 คีย์
   =IF(ROWS(H$2:H2)>SUM(IF(FREQUENCY(MATCH($A$2:$A$16,$A$2:$A$16,0),ROW($A$2:$A$16)-ROW($A$2)+1),1))*4,"",IF(MOD((ROWS(H$2:H2)-1),4)+1=1,INDEX($A$2:$A$16,SMALL(IF(FREQUENCY(MATCH($A$2:$A$16,$A$2:$A$16,0),ROW($A$2:$A$16)-ROW($A$2)+1),ROW($A$2:$A$16)-ROW($A$2)+1),INT((ROWS(H$2:H2)-1)/4)+1)),""))
   Ctrl+Shift+Enter > Copy ลงด้านล่าง
2. ที่ I2 คีย์
   =IF(ROWS(H$2:H2)>SUM(IF(FREQUENCY(MATCH($A$2:$A$16,$A$2:$A$16,0),ROW($A$2:$A$16)-ROW($A$2)+1),1))*4,"",INDEX({"T1","T1","T2","T2"},MOD((ROWS(H$2:I2)-1),4)+1))
   Ctrl+Shift+Enter > Copy ลงด้านล่าง
3. ที่ K2 คีย์
   =IF(I2="","",INDEX({"Created.","Closed.","Action Owner","Case Owner"},MOD((ROWS(H$2:I2)-1),4)+1))
   Enter > Copy ลงด้านล่าง
4. ที่ J2 คีย์
   =IF(I2="","",LOOKUP(2,1/(($A$2:$A$16=LOOKUP(2,1/(H$2:H2<>""),H$2:H2))*($D$2:$D$16=K2)),$C$2:$C$16))
   Enter > Copy ลงด้านล่าง
5. ที่ L2 คีย์
   =IF(I2="","",LOOKUP(2,1/(($A$2:$A$16=LOOKUP(2,1/(H$2:H2<>""),H$2:H2))*($C$2:$C$16=J2)*($D$2:$D$16=K2)),$F$2:$F$16&""))
   Enter > Copy ลงด้านล่าง
6. ที่ M2 คีย์
   =IF(OR(K2="Closed.",K2="Case Owner"),TEXT(J2-J1,"d ""Day(s)"" h ""Hour(s)"" m ""Minute(s)"""),"")
   Enter > Copy ลงด้านล่าง

!

Note: Ctrl+Shift+Enter เป็นการสร้างสูตร Array โดยสามารถเลือกดำเนินการตามข้อ 1 หรือ 2 ด้านล่าง กรณีคีย์สูตรเอง เมื่อคีย์สูตรแล้ว แทนที่จะกด Enter ให้กดแป้น Ctrl และ Shift พร้อมกันค้างไว้แล้วกด Enter เพื่อสร้างเป็นสูตร Array กรณี Copy สูตรไปวางให้กดแป้น F2 เพื่อทำการ Edit Cell นั้นก่อน จากนั้นกดแป้น Ctrl และ Shift พร้อมกันค้างไว้แล้วตามด้วย Enter เพื่อสร้างเป็นสูตร Array หากกดแป้นถูกต้องสูตรนั้นจะมีเครื่องหมายปีกกาครอบ เช่น {=YourFormulas(...)} ปีกกานี้ไม่สามารถคีย์เข้าไปเอง จะต้องมาด้วยการกดแป้น Ctrl+Shift+Enter เท่านั้น ถ้ายังไม่เห็นปีกกาครอบสูตรแสดงว่ากดแป้นให้รับสูตรไม่ถูกต้อง การแก้ไขเปลี่ยนแปลงสูตร Array จะต้องกดแป้นให้รับสูตรด้วย Ctrl+Shift+Enter ทุกครั้ง

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

ตัวอย่างการส่งงานต่อ

Case Number CC171202174 Status Open

Column L = Action by Team (ทีมเจ้าของใบงาน)
Column M = Action Owner (คนที่แก้ไขใบงาน)
Column N = Case Created By (คนที่สร้างใบงานจะเป็นคนในทีม Action by Team)

Cell O2 = คนออกใบงาน ( Admin User ),
เวลา Column P, Cell P2 ( 25-12-2017 1:06:00 PM )

Cell O4 = ทีม (SO TMN) ส่งต่องานไปยังทีม Column S, Cell S4 ( ACM-TS )
เวลา Column P, Cell P4 ( 25-12-2017 2:07:00 PM )

Cell O5 = ทีม (ACM-TS ) รับใบงานเปรียน Status Column S, Cell S4 ( Acknowledge),
เวลา Column P, Cell P5 ( 25-12-2017 3:37:00 PM)

Cell O8 = ทีม (ACM-TS ) แก้ไขใบงานเสร็จส่งใบงานกลับ Column S, Cell S8 ( SO TMN )
เวลา Column P, Cell P8 (25-12-2017 3:50:00 PM)

** ใบงาน CC171202174 ยังไม่ถูกปิดซึ่งรอทีม SO TMN แล้วข้อมูลจะเพิ่มมาอีก 1 Row

เนื่องจากได้ปรับไปใช้งาน power query excel

จึงมีคำถามใหม่ดังนี้ค่ะ

สอบถามค่ะไม่ทราบว่าเราจะเขียนสูตร Column X ให้ออกมาดังรูปยังไงค่ะ

เนื่องจากข้อมูล Created. กับ Closed แต่ละวันจำนวน Row ไม่เท่ากันเลย

ปล. ไม่ทราบว่าสิ่งที่คิดใหม่นี้จะดีกว่าก่อนหน้าที่ถามไปหรือป่าว แนะนำด้วยนะค่ะ

ลองแนบไฟล์ตัวอย่าง Excel ตามภาพล่าสุดมาใหม่จะได้สะดวกในการทำความเข้าใจครับ

อธิบายเพิ่มเติมจากไฟล์แนบค่ะ Column T - Column W เป็น Column ที่ได้จาก power query

กรณีที่ 1 ไม่มีการส่งงานต่อ
ตัวอย่าง Case Numbe CC171202144
Admin User = ใบงาน Auto จาก ระบบ

Cell T2 คือทีมที่ ออกใบงาน Admin User , Cell U2 = วันที่ เปิดใบงาน Cell U2 (25-12-2017 12:50:00 AM)
Cell T4 ทีม SO TMN แก้ไขงานและเปลี่ยน Status จาก Open เป็น Closed เวลา Cell u4 ( 25-12-2017 10:54:00 AM)

กรณีที่ 2 มีการส่งงานให้ทีมที่ 2
ตัวอย่าง Case Number CC171202174
Admin User = ใบงาน Auto จาก ระบบ

Cell T144 คือทีมที่ ออกใบงาน Admin User , Cell U144 = วันที่ เปิดใบงาน
Cell O146 = ทีม SO TMN แก้ไข Case Owner เป็น ACM-TS (หมายถึงส่งงานไปยังทีม ACM-TS )
Cell O147 = ทีม ACM-TS รับทราบใบงานและเปลี่ยน Status จาก Open เป็น Acknowledge เวลา Cell w147 ( 25-12-2017 3:37:00 PM)
Cell O149 = ทีม ACM-TS แก้ไขงานเสร็จและเปลี่ยน Acknowledge เป็น Open เวลา Cell w149 ( 25-12-2017 3:50:00 PM )
Cell U152 = ทีม SO TMN เปลี่ยน Status จาก Open เป็น Closed

ดังนั้นนับเวลาของการรับงานจนถึงปิดงานของ
ทีม SO TMN ตั้งแต่เปิดใบงาน - ปิดใบงาน = U152 - U144 = X 152
ทีม ACM-TS ตั้งแต่ Status Acknowledge - ส่งงานกลับ = W149 - W147 = X149

สุดท้ายแล้ว Report ตัวนี้ ต้อแสดง ว่า
1.ทีมที่ออกใบงาน ออกกี่ใบทั้งหมดกี่ใบ แก้ได้เองกี่ใบ และส่งไปยังทีมที่ 2 กี่ใบ < บอกจำนวน
2.ผลรวมเวลาของทั้ง 2 ทีม

เพื่อจะดูว่าทีมไหนใช้เวลาในการแก้ปัญหานานค่ะ

ขอให้ค่อย ๆ ทำไปทีละเรื่อง ตัวอย่างสูตรตามด้านล่างครับ

1. ที่ Y1 คีย์หัวคอลัมน์เป็น UseFulStatus ที่ Y2 คีย์สูตรเพื่อหา Status ที่เป็น Created และ Closed
   =IF(ISNUMBER(SEARCH("Create",Q2)),"Created",IF(ISNUMBER(SEARCH("Close",S2)),"Closed",""))
   Enter > Copy ลงด้านล่าง
2. ที่ X2 คีย์สูตรเพื่อตำค่า Status ในข้อ 1 มาหักลบกันและแสดงผลลัพธ์เป็นวัน เวลา ที่ห่างกัน
   =IF(Y2="Closed",TEXT(U2-LOOKUP(2,1/((B$1:B1=B2)*(Y$1:Y1="Created")),U$1:U1),"D ""Days"" H ""Hours"" M ""Minutes"""),"")
   Enter > Copy ลงด้านล่าง

ประเด็นสำคัญกรณีมีการส่งต่อ จะต้องหาออกมาให้ได้ว่าจะให้ Status เป็น Created เมื่อใด หากกำหนดได้สูตรด้านบนจะใช้ได้ทุกกรณีตามที่แจ้งมาครับ

ที่ X2 ต้องแก้สูตรยังไงค่ะให้เป็น นาทีอย่าเดียว

เช่น

U4 - U2 = 604 นาที

( 25-12-2017 10:54:00 AM - 25-12-2017 12:50:00 AM ) = 604

เพราะ Step ต่อไปจะต้องทำ PivotTable เพื่อรวมค่าเวลาค่ะ

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

=IF(Y2="Closed",TEXT(U2-LOOKUP(2,1/((B$1:B1=B2)*(Y$1:Y1="Created")),U$1:U1)," [M] ""Minutes""")+0,"")

สามารถไช้งานได้แล้วค่ะ

ขอบคุณสำหรับความช่วยเหลือนะค่ะ