Page 1 of 1
QUERY แล้วผลลัพธ์เป็นรูปสี่เหลี่ยมครับ และ สอบถามสูตรสำหรับทำ Report ครับผม
Posted: Tue Jul 06, 2021 6:16 pm
by tingpcj
ขอสอบถามครับ
ผมทำ QUERY แล้วผลลัพธ์เป็นรูปสี่เหลี่ยมครับ ทำอย่างไรถึงจะเป็นข้อมูลเหมือนอย่างที่ต้นฉบับก่อนที่จะรวมครับ
และ
ขอสอบถามสูตรเพื่อทำ Report 1 ครับผม
(เอกสารตามไฟล์แนบครับ)
ขอขอบคุณครับผม
Re: QUERY แล้วผลลัพธ์เป็นรูปสี่เหลี่ยมครับ และ สอบถามสูตรสำหรับทำ Report ครับผม
Posted: Tue Jul 06, 2021 7:02 pm
by snasui
ตรวจสอบและแก้ไขข้อมูลวันที่ไม่ให้เป็นค่าลบครับ
การที่วันที่เป็นค่าลบจะแสดงเครื่องหมาย # จนเเต็มความกว้างเซลล์ ดูภาพด้านล่างประกอบ
นอกจากนี้ วันที่ที่เป็นค.ศ.ไม่ควรมีค่าเป็นหลักแสน ลองคีย์
=Value(Now()) ลงในเซลล์ใด ๆ เพื่อสังเกตว่าค่าวันและเวลาในปัจจุบันเป็นเท่าใด เพื่อจะได้เทียบได้ว่าวันที่ไม่ควรจะเกินค่านี้ไปอย่างผิดสังเกตครับ
Re: QUERY แล้วผลลัพธ์เป็นรูปสี่เหลี่ยมครับ และ สอบถามสูตรสำหรับทำ Report ครับผม
Posted: Wed Jul 07, 2021 4:21 am
by tingpcj
ผมเปลี่ยนแปลงวันที่ได้แล้วครับ วิธีการแก้ไขคือ ผมไปปรับรูปแบบ ค.ศ.2021 ให้เป็น พ.ศ.2564 ใน Data ทั้ง 5 ไฟล์ แล้วทำการ Power QUERY อีกครั้งครับ ขอบคุณครับผม
- Untitled.jpg (121.38 KiB) Viewed 79 times
Re: QUERY แล้วผลลัพธ์เป็นรูปสี่เหลี่ยมครับ และ สอบถามสูตรสำหรับทำ Report ครับผม
Posted: Wed Jul 07, 2021 4:30 am
by tingpcj
เมื่อทำการแก้ไขรูปแบบวันที่แล้ว สามารถทำ Report 1 โดยการเขียนสูตรอย่างไรได้บ้างครับผม (ที่ Sheet Report 1)
ขอบคุณครับ
Re: QUERY แล้วผลลัพธ์เป็นรูปสี่เหลี่ยมครับ และ สอบถามสูตรสำหรับทำ Report ครับผม
Posted: Wed Jul 07, 2021 7:48 am
by snasui
ตัวอย่างสูตรตามด้านล่างครับ
- ที่ 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 > ลงด้านล่าง
- ที่ 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
ไม่ใช่วันที่และเวลาครับ
Re: QUERY แล้วผลลัพธ์เป็นรูปสี่เหลี่ยมครับ และ สอบถามสูตรสำหรับทำ Report ครับผม
Posted: Wed Jul 07, 2021 9:27 am
by tingpcj
Re: QUERY แล้วผลลัพธ์เป็นรูปสี่เหลี่ยมครับ และ สอบถามสูตรสำหรับทำ Report ครับผม
Posted: Wed Jul 07, 2021 9:49 am
by logic
แนบไฟล์เอ็กเซลล์มาให้เพื่อน ๆ ช่วยกันดูดีกว่าครับ
Re: QUERY แล้วผลลัพธ์เป็นรูปสี่เหลี่ยมครับ และ สอบถามสูตรสำหรับทำ Report ครับผม
Posted: Wed Jul 07, 2021 10:24 am
by tingpcj
Re: QUERY แล้วผลลัพธ์เป็นรูปสี่เหลี่ยมครับ และ สอบถามสูตรสำหรับทำ Report ครับผม
Posted: Wed Jul 07, 2021 10:27 am
by tingpcj
snasui wrote: Wed Jul 07, 2021 7:48 am
ตัวอย่างสูตรตามด้านล่างครับ
- ที่ 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 > ลงด้านล่าง
- ที่ 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
Re: QUERY แล้วผลลัพธ์เป็นรูปสี่เหลี่ยมครับ และ สอบถามสูตรสำหรับทำ Report ครับผม
Posted: Wed Jul 07, 2021 12:32 pm
by snasui
ชีต Total คือเวลาเดียวกันตามภาพครับ
Re: QUERY แล้วผลลัพธ์เป็นรูปสี่เหลี่ยมครับ และ สอบถามสูตรสำหรับทำ Report ครับผม
Posted: Wed Jul 07, 2021 1:18 pm
by tingpcj
Re: QUERY แล้วผลลัพธ์เป็นรูปสี่เหลี่ยมครับ และ สอบถามสูตรสำหรับทำ Report ครับผม
Posted: Thu Jul 08, 2021 12:23 am
by 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
Re: QUERY แล้วผลลัพธ์เป็นรูปสี่เหลี่ยมครับ และ สอบถามสูตรสำหรับทำ Report ครับผม
Posted: Fri Jul 09, 2021 12:02 pm
by tingpcj
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
ขอบคุณท่านมากๆครับผม ได้ความรู้มากมายครับ
