โซลูชัน LuckyTemplates ระหว่างวันที่ชั่วโมงทำงาน

ในบล็อกของวันนี้ ฉันจะสาธิตวิธีการเข้าถึงความท้าทาย Start Stop ของ Access Analytic ซึ่งใช้ LuckyTemplates ชั่วโมงการทำงานทั้งหมดระหว่างวันที่จะถูกคำนวณ คุณสามารถดูวิดีโอทั้งหมดของบทช่วยสอนนี้ได้ที่ด้านล่างของบล็อกนี้

งานคือการคำนวณจำนวนชั่วโมงทั้งหมดสำหรับพนักงานในแต่ละเดือนและวันที่ ฉันแสดงวันที่เริ่มต้นและวันที่สิ้นสุดที่สามารถข้ามเดือนได้ และคำนึงถึงสถานะในการจัดการกับการพิมพ์ผิดที่อาจเกิดขึ้น 

สารบัญ

• ชุดข้อมูลสำหรับ LuckyTemplates ระหว่างวันที่ชั่วโมงทำงาน
• การกรองแถว
• การเพิ่มคอลัมน์ที่กำหนดเอง
• ชั่วโมงการทำงานระหว่างวันที่ LuckyTemplates: ตัวแปร
• ชั่วโมงการทำงานระหว่างวันที่ LuckyTemplates: การฉายภาพ
• ชั่วโมงการทำงานระหว่างวันที่ LuckyTemplates: ค่ารวม
• บทสรุป

ชุดข้อมูลสำหรับ LuckyTemplates ระหว่างวันที่ชั่วโมงทำงาน

ไปที่ตัวแก้ไข Power Query 

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

การกรองแถว

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

ใน หน้าต่าง Filter Rowsที่จะปรากฏขึ้น ให้เขียน"e" จาก นั้นคลิกตกลง

จะยังไม่มีอะไรเกิดขึ้นและไม่เป็นไรเพราะเราเขียนตัวพิมพ์เล็ก “e” และ “Exclude” ในข้อมูลของเราที่ขึ้นต้นด้วยตัวพิมพ์ใหญ่ “E”

เพื่อแก้ไข เราจะใช้พารามิเตอร์ตัวเลือกที่สามของText.StartsWith ในแถบสูตร ไปที่ส่วนท้ายของฟังก์ชัน Text.StartsWith เพิ่มเครื่องหมายจุลภาคและป้อนตัวเปรียบเทียบ OrdinalIgnoreCase ของเรา คลิกเครื่องหมายถูกและควรลบแถวที่แยกออก

แน่นอน เราอาจมีบางคนที่พิมพ์ "ยกเว้น" ผิดพลาดโดยไม่มี "E" ขึ้นต้น ในกรณีนั้น เราสามารถสร้างตรรกะเพิ่มเติมเพื่อให้เข้ากับสถานการณ์ได้ 

เราสามารถทดสอบว่าคำ นั้นไม่มี "X" หรือไม่โดยขยายฟังก์ชันของเราและใช้Text.Contains ในการทำเช่นนั้น ให้เพิ่ม หรือ Text.Contains ([ Status ], “x”, Comparer.OrdinalIgnoreCase

โปรดสังเกตว่าเมื่อเปรียบเทียบกับประโยคแรก เราแทนที่ "e" ด้วย "x" เราเพิกเฉยต่อกรณีและล้อมรอบชุดของวงเล็บรอบอนุประโยคทั้งสองของเรา คลิกเครื่องหมายถูกแล้วเราจะได้ตารางที่ไม่มีแถวที่มีสถานะไม่รวมหรือแยก

การเพิ่มคอลัมน์ที่กำหนดเอง

ตอนนี้เรามาที่ส่วนหลักของความท้าทายนี้กันเถอะ เราจะเพิ่มคอลัมน์ที่กำหนดเองโดยใช้ UI แล้วเปลี่ยนไปใช้ตัวแก้ไข 

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

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

มาสร้างตัวแปรและกำหนดคอลัมน์ที่มีอยู่ เริ่มด้วยการพิมพ์SDซึ่งจะเท่ากับวันที่เริ่มต้น เราสามารถทำได้โดยคลิกที่วันที่เริ่มต้นในคอลัมน์ทางด้านขวา

เราจะทำเช่นเดียวกันกับตัวแปรST, EDและETที่จะกำหนดให้กับStart Time, Stop DateและStop Timeตามลำดับ จากนั้นปิดบันทึกด้วยวงเล็บปิด คลิกตกลง

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

ชั่วโมงการทำงานระหว่างวันที่ LuckyTemplates: ตัวแปร

จากนั้นเราสามารถจัดรูปแบบตัวแปรของเราผ่านหน้าต่างAdvanced Editor นี่คือลักษณะของหน้าต่าง Advanced Editor 

จากนั้นให้ใส่ตัวแปรของเราในบรรทัดใหม่แยกกัน

ตอนนี้ เราสามารถขยายตรรกะที่เราต้องการ ดังนั้น แทนที่จะใช้บันทึก เราสามารถใช้นิพจน์ let ที่ซ้อนกันได้ที่นี่ โปรดทราบว่าทั้งสองอย่างนี้จะใช้ได้เพราะเรามีหลายตัวแปรหรือชื่อฟิลด์ที่มีค่าที่กำหนดให้กับพวกมัน

ขณะนี้ วันที่เริ่มต้นของเราเขียนเป็นค่าข้อความ และเราจำเป็นต้องแปลงให้เป็นรูปแบบวันที่ที่เหมาะสม ในการทำเช่นนั้น ให้ล้อมฟังก์ชันDate.FromTextไว้รอบๆ 

สำหรับเวลาเริ่มต้นซึ่งมีรูปแบบเป็นสตริงด้วย เราจำเป็นต้องแปลงเป็นตัวเลข จากนั้นเราสามารถหารด้วยร้อยแล้วปัดเศษทศนิยมออก 

ใช้ ฟังก์ชัน Number.Fromหาร ค่า วันที่เริ่มต้นด้วย100 จากนั้น ล้อมไว้ในฟังก์ชันถัดไปNumber.Roundและเพิ่ม, 0ที่ส่วนท้ายเพื่อปัดเศษโดยไม่มีจุดทศนิยม 

สำหรับวันที่สิ้นสุดและเวลาสิ้นสุดเราจะคัดลอกฟังก์ชันเดียวกันกับที่เรามีในวันที่เริ่มต้นและเวลาเริ่มต้นตามลำดับ และแทนที่ด้วยตัวแปรที่เหมาะสม

เมื่อตัวแปรได้รับการจัดรูปแบบอย่างถูกต้องแล้ว ให้สร้างฟิลด์อื่นในบันทึกของเราสำหรับลอจิกเพิ่มเติม สร้างบรรทัดใหม่โดยกดEnter 

เราจำเป็นต้องสร้างรายการวันที่ตั้งแต่วันแรกจนถึงวันที่สิ้นสุด เราจะเรียกวันที่เหล่านั้นว่า  LDและเราจะใช้ฟังก์ชันList.Dates

พารามิเตอร์แรกของฟังก์ชันนี้คือstart as dateซึ่งจะเป็นSD ของเรา จากนั้นต้องการให้นับเป็นตัวเลขหรือความแตกต่างระหว่างวันที่เริ่มต้น และวันที่สิ้นสุด เราสามารถรับสิ่งนั้นได้โดยใช้ ฟังก์ชัน Number.Fromตามด้วย ( ED – SD) + 1 โปรดทราบว่า "+1" จะไม่ปรากฏในภาพหน้าจอ ชุดถัดไป แต่ควรมี+1

พารามิเตอร์ที่สามเรียกว่าขั้นตอนเป็นระยะเวลาและเราต้องการเพิ่มขึ้นหนึ่งวัน เราสามารถรับสิ่งนั้นได้โดยใช้ระยะเวลาจาก (1) 

เราได้สร้างรายการที่มีวันที่ตั้งแต่วันที่เริ่มต้นวันแรกจนถึงวันที่หยุด สิ่งที่เราต้องการต่อไปคือการสร้างรายการเวลาที่สอดคล้องกัน 

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

เราต้องการให้เงื่อนไขบอกว่า “ถ้าวันที่เริ่มต้นตรงกับวันที่สิ้นสุด เวลาสิ้นสุดลบด้วยเวลาเริ่มต้น” ผลลัพธ์ควรอยู่ในรูปแบบรายการ เราจึงใช้ตัวเริ่มต้นรายการที่แสดงโดย{  }

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

ณ จุดนี้ เรายังไม่ได้นับรวมวันอื่นๆ ดังนั้นเราจะใช���เครื่องหมายและ (&) เพื่อต่อท้ายรายการต่างๆ สำหรับทั้งวัน ให้สร้างรายการที่มีค่า 24 ซึ่งแสดงถึง 24 ชั่วโมงที่เรามีในหนึ่งวัน เราจะใช้List.Repeatเพื่อสิ่งนั้น 

ด้วย ฟังก์ชัน List.Repeat ให้สร้างรายการที่ มี24 และทำซ้ำหลายครั้งโดยนับจำนวนวันในLD เพื่อให้บรรลุผลดังกล่าว ให้ใช้List.Count (LD)   แล้วลบ 2 เนื่องจากเรามีรายการแยกต่างหากสำหรับวันที่เริ่มต้น และเราจะสร้างรายการอื่นสำหรับเวลาสิ้นสุด 

โดยพื้นฐานแล้ว สิ่งนี้จะสร้างรายการโดยมีเพียง 24 ชั่วโมงในแต่ละวัน 

สำหรับเวลาสิ้นสุด เราสามารถต่อท้ายอีกครั้งเป็นรายการโดยใช้เครื่องหมายแอมเปอร์แซนด์ จากนั้นใช้ตัวเริ่มต้นรายการ เรามาเรียก ET กัน 

จนถึงตอนนี้ เรามีรายการใหญ่สองรายการ ได้แก่ รายการวันที่และรายการเวลา โดยมีความยาวเท่ากัน จากทั้งสองรายการ เราสามารถสร้างตารางเดียวได้ 

ในบรรทัดใหม่ ให้สร้างตัวแปรอื่นสำหรับตารางที่เราจะเรียกว่าt และใช้ฟังก์ชันTable.FromColumns ฟังก์ชันนี้ต้องการรายการเป็นรายการและเราจะใช้LD ของเรา 

LDของเรามีวันที่แยกจากวันที่เริ่มต้นจนถึงวันที่หยุด เราสามารถแปลงรายการของวันที่นั้นเป็นค่าวันที่สิ้นสุดของเดือนโดยผ่านLDและเรียกใช้ฟังก์ชันDate.EndOfMonth จากนั้นในบรรทัดถัดไป มารับLT ของเรา ด้วย 

กดEnterเพื่อสร้างบรรทัดใหม่และตั้งค่าตารางของเราเป็นประเภท table ตารางของเราจะมีสองคอลัมน์ คอลัมน์แรกเป็นคอลัมน์วันที่ ดังนั้น สร้าง คอลัมน์ Dateและเรียกใช้ฟังก์ชันDate.Type

คอลัมน์ที่สองจะเป็น คอลัมน์ ชั่วโมง ของเรา และจะเป็นจำนวนเต็ม ดังนั้น เราจะสร้าง คอลัมน์ Hrsและเรียกใช้Int8.Type

เรามีวงเล็บปิดเพื่อปิดเรกคอร์ดและวงเล็บปิดเพื่อปิดฟังก์ชันTable.AddColumn คลิกเสร็จสิ้นแล้วเราจะได้รับรายการบันทึกภายใต้คอลัมน์กำหนดเอง 

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

สังเกตว่าเรามีt Tableอยู่แถวสุดท้าย มาลองเจาะลึกหนึ่งในบันทึก คลิกขวาที่ผลลัพธ์แรกแล้วAdd as New Query 

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

แต่เราสนใจตารางผลลัพธ์สุดท้ายเป็นหลัก หากต้องการโฟกัสที่จุดนั้น ให้ลบคำค้นหาความช่วยเหลือโดยคลิกขวาที่Custom แล้วเลือกDelete ยืนยันโดยคลิกลบในหน้าต่างป๊อปอัป

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

ในคอลัมน์ Custom เราจะเห็นว่าแต่ละRecord  เปลี่ยนเป็นTable

ชั่วโมงการทำงานระหว่างวันที่ LuckyTemplates: การฉายภาพ

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

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

เราควรได้รับผลนี้

เราสามารถขยายตารางที่ซ้อนกันได้โดยคลิกที่ไอคอนลูกศร  ข้างCustom ยกเลิกการเลือกใช้ชื่อคอลัมน์เดิมเป็นคำนำหน้าแล้วคลิกตกลง

ตารางของเราควรมีลักษณะเช่นนี้

ชั่วโมงการทำงานระหว่างวันที่ LuckyTemplates: ค่ารวม

สิ่งที่ต้องทำตอนนี้คือการรวมค่าเหล่านี้ ในการทำเช่นนั้น เลือก คอลัมน์ ชื่อพนักงานและไปที่การแปลง 

ในหน้าต่างป๊อปอัป ให้เลือกHrsภายใต้คอลัมน์ Values ​​เนื่องจากเป็นสิ่งที่เราต้องการรวม จากนั้น เลือกSumภายใต้Aggregate Value Function จากนั้นคลิกตกลง

ผลลัพธ์สุดท้ายของเราจะมีลักษณะดังนี้ 

การสร้างตารางวันที่ใน LuckyTemplates วิธีที่เร็วที่สุด
การเปรียบเทียบเวลาที่เป็นไปได้สำหรับตารางวันที่ที่ไม่ได้มาตรฐานใน
ค่า DateTime ของ LuckyTemplates: วิธีตัดวินาที

บทสรุป

ในบล็อกนี้ คุณได้เห็นแนวทางหนึ่งในการท้าทาย Start Stop ของ Access Analytic คุณได้เรียนรู้วิธีคำนวณจำนวนชั่วโมงการทำงานของพนักงานแต่ละคนในหนึ่งเดือน และวิธีจัดการกับข้อผิดพลาดที่อาจเกิดขึ้นในข้อมูลของคุณ 

ด้วยเทคนิคนี้และ LuckyTemplates ชั่วโมงการทำงานระหว่างวันที่สามารถคำนวณได้อย่างง่ายดายในองค์กรของคุณเอง หรือเป็นวิธีปฏิบัติเพื่อเพิ่มพูนความรู้และทักษะ LuckyTemplates ของคุณให้ลึกซึ้งยิ่งขึ้น