Snasui Excel Forum

Bo_ry wrote: Thu Jul 08, 2021 12:23 am เวลาติดลบเพราะ locale ของ Power Query
ข้อมูลใส่เป็นปี ค.ศ. ถูกต้องแล้ว

https://www.facebook.com/groups/ExcelSu ... 5130225474


Power Query จะแปลง data ตาม Locale ของ Power Query
ถ้า Set เป็น Thai ไว้
2021 จะถูกมองว่าเป็น ปี พ.ศ. 2021
เทียบเท่ากับ ปี ค.ศ 1478 ซึ่ง Power query มี พ.ศ นี้
แต่ใน Excel 1/1/1900 คือ 1
1478-1900 = -422 ปี ก็ประมาณ
-422*365 = -154030 วัน

Code: Select all

let
    Source = Folder.Files("D:\Login Logout ประจำเดือนกรกฎาคม 2564\DATA"),
    AddWorkbook = Table.AddColumn(Source, "WB", each Excel.Workbook([Content],true,true){0}[Data]),
    Combine = Table.Combine(AddWorkbook[WB]),
    Time = Table.TransformColumns(Combine, {"Total Login Time", each Time.From(Text.Trim(Text.Replace(Text.Select(_,{"0".."9",")"}),")",":"),":")),type time} )
in
    Time

snasui wrote: Wed Jul 07, 2021 12:32 pm ชีต Total คือเวลาเดียวกันตามภาพครับ

snasui wrote: Wed Jul 07, 2021 7:48 am ตัวอย่างสูตรตามด้านล่างครับ

1. ที่ A7 คีย์
   =IFERROR(INDEX(Total!B:B,AGGREGATE(15,6,ROW(Total!$B$2:$B$149)/(Total!$J$2:$J$149=$A$3),ROWS(A$7:A7))),"")
   Enter > Copy ไปด้านขวาถึง C7 > ลงด้านล่าง
2. ที่ D7 คีย์
   =IFERROR(LOOKUP(2,1/(Total!$J$2:$J$149=$A$3)/(Total!$B$2:$B$149=$A7)/(INT(INDEX(Total!$E$2:$G$149,0,MATCH($B$3,Total!$E$1:$G$1,0)))=D$6),INDEX(Total!$E$2:$I$149,0,MATCH($B$3,Total!$E$1:$I$1,0)))-D$6,"")
   Enter > Copy ไปด้านขวาและลงด้านล่าง > จัดรูปแบบผลลัพธ์ให้เป็น h:mm

สูตรตามข้อ 2 จะเป็นการตัดเอาเวลาด้านหลังมาแสดง เพราะเข้าใจว่าโจทย์ต้องการเช่นนั้น หากเซลล์ B3 เป็น Total Login Time จะไม่สามารถแสดงเวลาได้เพราะค่าต้นทางเป็น Text ไม่ใช่วันที่และเวลาครับ

1. ที่ A7 คีย์
   =IFERROR(INDEX(Total!B:B,AGGREGATE(15,6,ROW(Total!$B$2:$B$149)/(Total!$J$2:$J$149=$A$3),ROWS(A$7:A7))),"")
   Enter > Copy ไปด้านขวาถึง C7 > ลงด้านล่าง
2. ที่ D7 คีย์
   =IFERROR(LOOKUP(2,1/(Total!$J$2:$J$149=$A$3)/(Total!$B$2:$B$149=$A7)/(INT(INDEX(Total!$E$2:$G$149,0,MATCH($B$3,Total!$E$1:$G$1,0)))=D$6),INDEX(Total!$E$2:$I$149,0,MATCH($B$3,Total!$E$1:$I$1,0)))-D$6,"")
   Enter > Copy ไปด้านขวาและลงด้านล่าง > จัดรูปแบบผลลัพธ์ให้เป็น h:mm