QUERY แล้วผลลัพธ์เป็นรูปสี่เหลี่ยมครับ และ สอบถามสูตรสำหรับทำ Report ครับผม

[tingpcj]

ขอสอบถามครับ

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

และ

ขอสอบถามสูตรเพื่อทำ Report 1 ครับผม
(เอกสารตามไฟล์แนบครับ)
ขอขอบคุณครับผม

[snasui]

ตรวจสอบและแก้ไขข้อมูลวันที่ไม่ให้เป็นค่าลบครับ

การที่วันที่เป็นค่าลบจะแสดงเครื่องหมาย # จนเเต็มความกว้างเซลล์ ดูภาพด้านล่างประกอบ

นอกจากนี้ วันที่ที่เป็นค.ศ.ไม่ควรมีค่าเป็นหลักแสน ลองคีย์ =Value(Now()) ลงในเซลล์ใด ๆ เพื่อสังเกตว่าค่าวันและเวลาในปัจจุบันเป็นเท่าใด เพื่อจะได้เทียบได้ว่าวันที่ไม่ควรจะเกินค่านี้ไปอย่างผิดสังเกตครับ

[tingpcj]

ผมเปลี่ยนแปลงวันที่ได้แล้วครับ วิธีการแก้ไขคือ ผมไปปรับรูปแบบ ค.ศ.2021 ให้เป็น พ.ศ.2564 ใน Data ทั้ง 5 ไฟล์ แล้วทำการ Power QUERY อีกครั้งครับ ขอบคุณครับผม

Untitled.jpg (121.38 KiB) Viewed 74 times

[tingpcj]

เมื่อทำการแก้ไขรูปแบบวันที่แล้ว สามารถทำ Report 1 โดยการเขียนสูตรอย่างไรได้บ้างครับผม (ที่ Sheet Report 1)
ขอบคุณครับ

[snasui]

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

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 ไม่ใช่วันที่และเวลาครับ

[tingpcj]

สอบถามครับ ผมได้วางสูตรที่ 2 แล้วที่ช่อง D7 แล้วขยายสูตรไปตามรูปที่แนบครับ แต่เวลาของบรรทัดเดียวกัน เป็นเวลาเดียวกันหมดทั้งแถวเลยครับ ไม่เปลี่ยนตามวันที่ครับ รบกวนด้วยครับ ขอบคุณครับ

333.jpg (80.62 KiB) Viewed 62 times

[logic]

แนบไฟล์เอ็กเซลล์มาให้เพื่อน ๆ ช่วยกันดูดีกว่าครับ

[tingpcj]

ครับผม ไฟล์แนบครับ

Total&Report.xlsx

(67.72 KiB) Downloaded 1 time

[tingpcj]

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

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 ไม่ใช่วันที่และเวลาครับ

สอบถามครับ ผมได้วางสูตรที่ 2 แล้วที่ช่อง D7 แล้วขยายสูตรไปตามรูปที่แนบครับ แต่เวลาของบรรทัดเดียวกัน เป็นเวลาเดียวกันหมดทั้งแถวเลยครับ ไม่เปลี่ยนตามวันที่ครับ รบกวนด้วยครับ ขอบคุณครับ
333.jpg

Total&Report.xlsx

(67.72 KiB) Downloaded 2 times

[snasui]

ชีต Total คือเวลาเดียวกันตามภาพครับ

[tingpcj]

ชีต Total คือเวลาเดียวกันตามภาพครับ

ขอบคุณท่านอาจารย์ครับ ผมเองดู DATA ไม่ถี่ถ้วนเองครับ

ขอบคุณมากครับผม

[Bo_ry]

เวลาติดลบเพราะ 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

[tingpcj]

เวลาติดลบเพราะ 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

ขอบคุณท่านมากๆครับผม ได้ความรู้มากมายครับ