สมมติว่าคุณกำลังดำเนินโครงการหลายโครงการด้วยงบประมาณที่แตกต่างกัน และต้องการแสดงภาพต้นทุนของคุณสำหรับแต่ละโครงการ นั่นคือจากตารางแหล่งที่มานี้:
.. รับสิ่งนี้:
กล่าวอีกนัยหนึ่ง คุณต้องกระจายงบประมาณตามวันของแต่ละโปรเจ็กต์ และรับเวอร์ชันที่เรียบง่ายของแผนภูมิแกนต์ของโปรเจ็กต์ การทำสิ่งนี้ด้วยมือของคุณนั้นยาวนานและน่าเบื่อ แมโครนั้นยาก แต่ Power Query สำหรับ Excel ในสถานการณ์เช่นนี้แสดงให้เห็นถึงพลังของมันในทุกด้าน
Power Query เป็นส่วนเสริมจาก Microsoft ที่สามารถนำเข้าข้อมูลไปยัง Excel จากแหล่งเกือบทุกแหล่ง แล้วแปลงในรูปแบบต่างๆ มากมาย ใน Excel 2016 โปรแกรมเสริมนี้มีอยู่แล้วภายในตามค่าเริ่มต้น และสำหรับ Excel 2010-2013 สามารถดาวน์โหลดได้จากเว็บไซต์ Microsoft แล้วติดตั้งบนพีซีของคุณ
ขั้นแรก ให้เปลี่ยนตารางเดิมของเราให้เป็นตารางที่ "ฉลาด" โดยเลือกคำสั่ง จัดรูปแบบเป็นตาราง แถบ หน้าแรก (หน้าแรก — รูปแบบเป็นตาราง) หรือโดยการกดแป้นพิมพ์ลัด Ctrl+T :
จากนั้นไปที่แท็บ ข้อมูล (ถ้าคุณมี Excel 2016) หรือบนแท็บ Power Query (ถ้าคุณมี Excel 2010-2013 และคุณติดตั้ง Power Query เป็น Add-in แยกต่างหาก) แล้วคลิกปุ่ม จากตาราง / ช่วง :
ตารางอัจฉริยะของเราโหลดลงในตัวแก้ไขคิวรี Power Query โดยที่ขั้นตอนแรกคือการตั้งค่ารูปแบบตัวเลขสำหรับแต่ละคอลัมน์โดยใช้ดรอปดาวน์ในส่วนหัวของตาราง:
ในการคำนวณงบประมาณต่อวัน คุณต้องคำนวณระยะเวลาของแต่ละโครงการ ในการดำเนินการนี้ ให้เลือก (กดปุ่มค้างไว้ Ctrl) คอลัมน์ก่อน เสร็จสิ้นและจากนั้น เริ่มต้น และเลือกทีม เพิ่มคอลัมน์ – วันที่ – ลบวัน (เพิ่มคอลัมน์ — วันที่ — ลบวัน):
ผลลัพธ์ที่ได้คือ 1 น้อยกว่าที่จำเป็นเพราะเราควรเริ่มแต่ละโครงการในวันแรกในตอนเช้าและสิ้นสุดในวันสุดท้ายในตอนเย็น ดังนั้นให้เลือกคอลัมน์ผลลัพธ์และเพิ่มหน่วยโดยใช้คำสั่ง แปลง – มาตรฐาน – เพิ่ม (แปลง — มาตรฐาน — เพิ่ม):
ตอนนี้ มาเพิ่มคอลัมน์ที่เราคำนวณงบประมาณต่อวันกัน ในการทำเช่นนี้บนแท็บ เพิ่มคอลัมน์ ฉันไม่เล่น คอลัมน์ที่กำหนดเอง (คอลัมน์กำหนดเอง) และในหน้าต่างที่ปรากฏขึ้น ให้ป้อนชื่อฟิลด์ใหม่และสูตรการคำนวณโดยใช้ชื่อคอลัมน์จากรายการ:
ตอนนี้เป็นช่วงเวลาที่ละเอียดอ่อนที่สุด เราสร้างคอลัมน์จากการคำนวณอีกคอลัมน์หนึ่งพร้อมรายการวันที่ตั้งแต่ต้นจนจบด้วยขั้นตอน 1 วัน เมื่อต้องการทำเช่นนี้ ให้กดปุ่มอีกครั้ง คอลัมน์ที่กำหนดเอง (คอลัมน์กำหนดเอง) และใช้ภาษา M ของ Power Query ซึ่งเรียกว่า รายการวันที่:
ฟังก์ชั่นนี้มีสามอาร์กิวเมนต์:
- วันที่เริ่มต้น – ในกรณีของเรา มันถูกนำมาจากคอลัมน์ เริ่มต้น
- จำนวนวันที่ที่จะสร้าง – ในกรณีของเรา นี่คือจำนวนวันสำหรับแต่ละโครงการ ซึ่งเรานับก่อนหน้านี้ในคอลัมน์ การลบ
- ขั้นตอนเวลา - กำหนดโดยการออกแบบ #ระยะเวลา(1,0,0,0)ความหมายในภาษา M คือ หนึ่งวัน ศูนย์ชั่วโมง ศูนย์นาที ศูนย์วินาที
หลังจากคลิกที่ OK เราได้รับรายการ (รายการ) ของวันที่ซึ่งสามารถขยายเป็นบรรทัดใหม่โดยใช้ปุ่มในส่วนหัวของตาราง:
… และเราได้รับ:
เหลือเพียงการยุบตาราง โดยใช้วันที่ที่สร้างขึ้นเป็นชื่อสำหรับคอลัมน์ใหม่ ทีมงานมีหน้าที่รับผิดชอบในเรื่องนี้ คอลัมน์รายละเอียด (คอลัมน์หมุน) แถบ แปลง (แปลง):
หลังจากคลิกที่ OK เราได้ผลลัพธ์ที่ใกล้เคียงกับที่ต้องการมาก:
Null ในกรณีนี้คืออะนาล็อกของเซลล์ว่างใน Excel
มันยังคงลบคอลัมน์ที่ไม่จำเป็นและยกเลิกการโหลดตารางผลลัพธ์ถัดจากข้อมูลดั้งเดิมด้วยคำสั่ง ปิดและโหลด - ปิดและโหลดใน... (ปิด & โหลด — ปิด & โหลดไปที่…) แถบ หน้าแรก (บ้าน):
เราได้รับผลลัพธ์:
เพื่อความสวยงามยิ่งขึ้น คุณสามารถปรับแต่งรูปลักษณ์ของตารางอัจฉริยะที่เป็นผลลัพธ์บนแท็บได้ นวกรรมิก (ออกแบบ): ตั้งค่ารูปแบบสีเดียว ปิดใช้งานปุ่มตัวกรอง เปิดใช้งานผลรวม ฯลฯ นอกจากนี้ คุณสามารถเลือกตารางที่มีวันที่และเปิดใช้งานการเน้นตัวเลขโดยใช้การจัดรูปแบบตามเงื่อนไขบนแท็บ หน้าแรก — การจัดรูปแบบตามเงื่อนไข — เครื่องชั่งสี (หน้าแรก — การจัดรูปแบบตามเงื่อนไข — เครื่องชั่งสี):
และส่วนที่ดีที่สุดคือในอนาคต คุณสามารถแก้ไขงานเก่าหรือเพิ่มโครงการใหม่ลงในตารางต้นฉบับได้อย่างปลอดภัย จากนั้นอัปเดตตารางที่ถูกต้องด้วยวันที่ด้วยปุ่มเมาส์ขวา และ Power Query จะทำซ้ำการกระทำทั้งหมดที่เราทำโดยอัตโนมัติ .
Voila!
- แผนภูมิแกนต์ใน Excel โดยใช้การจัดรูปแบบตามเงื่อนไข
- ปฏิทินเป้าหมายของโครงการ
- การสร้างแถวที่ซ้ำกันด้วย Power Query