ระบบติดตามการสั่งซื้อ Google Calendar และ Excel

กระบวนการทางธุรกิจจำนวนมาก (และแม้แต่ธุรกิจทั้งหมด) ในชีวิตนี้เกี่ยวข้องกับการปฏิบัติตามคำสั่งโดยนักแสดงจำนวนจำกัดภายในกำหนดเวลาที่กำหนด การวางแผนในกรณีดังกล่าวเกิดขึ้นอย่างที่พวกเขาพูดว่า "จากปฏิทิน" และมักจะมีความจำเป็นต้องถ่ายโอนเหตุการณ์ที่วางแผนไว้ในนั้น (คำสั่ง การประชุม การส่งมอบ) ไปยัง Microsoft Excel - สำหรับการวิเคราะห์เพิ่มเติมตามสูตร ตารางสาระสำคัญ แผนภูมิ เป็นต้น

แน่นอน ฉันต้องการใช้การถ่ายโอนดังกล่าวไม่ใช่โดยการคัดลอกแบบโง่ๆ (ซึ่งไม่ใช่เรื่องยาก) แต่ด้วยการอัปเดตข้อมูลอัตโนมัติ เพื่อให้การเปลี่ยนแปลงทั้งหมดที่ทำกับปฏิทินและคำสั่งซื้อใหม่ทันทีจะแสดงขึ้นในภายภาคหน้า เอ็กเซล คุณสามารถใช้การนำเข้าดังกล่าวได้ในเวลาไม่กี่นาทีโดยใช้ Add-in ของ Power Query ที่มีอยู่ใน Microsoft Excel โดยเริ่มจากเวอร์ชัน 2016 (สำหรับ Excel 2010-2013 สามารถดาวน์โหลดได้จากเว็บไซต์ Microsoft และติดตั้งแยกต่างหากจากลิงก์) .

สมมติว่าเราใช้ Google ปฏิทินฟรีสำหรับการวางแผน ซึ่งฉันสร้างปฏิทินแยกต่างหากเพื่อความสะดวก (ปุ่มที่มีเครื่องหมายบวกที่มุมล่างขวาถัดจาก ปฏิทินอื่น ๆ) โดยมีชื่อเรื่องว่า งาน. ที่นี่เราป้อนคำสั่งซื้อทั้งหมดที่ต้องทำให้เสร็จและจัดส่งให้กับลูกค้าตามที่อยู่:

คุณสามารถดูหรือแก้ไขรายละเอียดได้โดยดับเบิลคลิกที่คำสั่งซื้อ:

โปรดทราบว่า:

• ชื่องาน ผู้จัดการผู้ที่ปฏิบัติตามคำสั่งนี้ (เอเลน่า) และ จำนวนการสั่งซื้อ
• ชี้ให้เห็น ที่อยู่ การจัดส่ง
• บันทึกย่อประกอบด้วย (ในบรรทัดที่แยกจากกัน แต่ในลำดับใดก็ได้) พารามิเตอร์คำสั่งซื้อ: ประเภทการชำระเงิน จำนวนเงิน ชื่อลูกค้า ฯลฯ ในรูปแบบ พารามิเตอร์=ค่า.

เพื่อความชัดเจน คำสั่งของผู้จัดการแต่ละคนจะถูกเน้นด้วยสีของตนเอง แม้ว่าจะไม่จำเป็นก็ตาม

ขั้นตอนที่ 1 รับลิงก์ไปยัง Google ปฏิทิน

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

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

ขั้นตอนที่ 2 โหลดข้อมูลจากปฏิทินลงใน Power Query

ตอนนี้เปิด Excel และบนแท็บ ข้อมูล (ถ้าคุณมี Excel 2010-2013 ให้ไปที่แท็บ Power Query) เลือกคำสั่ง จากอินเทอร์เน็ต (ข้อมูล — จากอินเทอร์เน็ต). จากนั้นวางเส้นทางที่คัดลอกไปยังปฏิทินแล้วคลิกตกลง

iCal Power Query ไม่รู้จักรูปแบบ แต่สามารถช่วยได้ง่าย โดยพื้นฐานแล้ว iCal เป็นไฟล์ข้อความธรรมดาที่มีโคลอนเป็นตัวคั่น และภายในจะมีลักษณะดังนี้:

ดังนั้นคุณจึงสามารถคลิกขวาที่ไอคอนของไฟล์ที่ดาวน์โหลดมาและเลือกรูปแบบที่ใกล้เคียงที่สุดในความหมาย CSV – และข้อมูลของเราเกี่ยวกับคำสั่งซื้อทั้งหมดจะถูกโหลดลงในตัวแก้ไขแบบสอบถาม Power Query และแบ่งออกเป็นสองคอลัมน์ตามโคลอน:

ถ้าสังเกตดีๆ จะเห็นว่า

• ข้อมูลเกี่ยวกับแต่ละเหตุการณ์ (ลำดับ) ถูกจัดกลุ่มเป็นกลุ่มที่ขึ้นต้นด้วยคำว่า BEGIN และลงท้ายด้วย END
  
• วันที่เวลาเริ่มต้นและสิ้นสุดจะถูกเก็บไว้ในสตริงที่ชื่อ DTSTART และ DTEND
• ที่อยู่สำหรับจัดส่งคือ LOCATION
• บันทึกการสั่งซื้อ – ฟิลด์คำอธิบาย
• ชื่อเหตุการณ์ (ชื่อผู้จัดการและหมายเลขคำสั่งซื้อ) — ฟิลด์ SUMMARY

ยังคงดึงข้อมูลที่เป็นประโยชน์นี้และแปลงเป็นตารางที่สะดวก 

ขั้นตอนที่ 3 แปลงเป็นมุมมองปกติ

ในการดำเนินการนี้ ให้ดำเนินการตามขั้นตอนต่อไปนี้:

1. มาลบ 7 บรรทัดบนสุดที่เราไม่ต้องการกันก่อนคำสั่ง BEGIN แรก หน้าแรก — ลบแถว — ลบแถวบนสุด (หน้าแรก — ลบแถว — ลบแถวบนสุด).
2. กรองตามคอลัมน์ Column1 บรรทัดที่มีฟิลด์ที่เราต้องการ: DTSTART, DTEND, DESCRIPTION, LOCATION และ SUMMARY
3. บนแท็บขั้นสูง การเพิ่มคอลัมน์ เลือก คอลัมน์ดัชนี (เพิ่มคอลัมน์ — คอลัมน์ดัชนี)เพื่อเพิ่มคอลัมน์หมายเลขแถวลงในข้อมูลของเรา
4. ตรงแท็บนั่น การเพิ่มคอลัมน์ เลือกทีม คอลัมน์เงื่อนไข (เพิ่มคอลัมน์ — คอลัมน์เงื่อนไข) และที่จุดเริ่มต้นของแต่ละบล็อก (คำสั่ง) เราแสดงค่าของดัชนี:
5. เติมเซลล์ว่างในคอลัมน์ผลลัพธ์ ปิดกั้นโดยคลิกขวาที่ชื่อและเลือกคำสั่ง เติม-ลง (เติม - ลง).
6. ลบคอลัมน์ที่ไม่จำเป็น ดัชนี.
7. เลือกคอลัมน์ Column1 และทำการบิดข้อมูลจากคอลัมน์ Column2 โดยใช้คำสั่ง แปลง – คอลัมน์ Pivot (แปลง — คอลัมน์ Pivot). อย่าลืมเลือกในตัวเลือก อย่ารวมกัน (อย่ารวม)เพื่อไม่ให้ใช้ฟังก์ชันทางคณิตศาสตร์กับข้อมูล:
8. ในตารางสองมิติ (กากบาท) ที่เป็นผลลัพธ์ ให้ล้างแบ็กสแลชในคอลัมน์ที่อยู่ (คลิกขวาที่ส่วนหัวของคอลัมน์ – การแทนที่ค่า) และลบคอลัมน์ที่ไม่จำเป็นออก ปิดกั้น.
9. เพื่อเปลี่ยนเนื้อหาของคอลัมน์ ดีทีสตาร์ท и ดีเทนด์ ในแบบเต็มวันที่-เวลา ไฮไลต์พวกเขา เลือกบนแท็บ แปลง – วันที่ – เรียกใช้การวิเคราะห์ (แปลง — วันที่ — แยกวิเคราะห์). จากนั้นเราแก้ไขโค้ดในแถบสูตรโดยแทนที่ฟังก์ชัน วันที่.จาก on วันที่ เวลา จากเพื่อไม่ให้สูญเสียค่าเวลา:
10. จากนั้น โดยการคลิกขวาที่ส่วนหัว เราจะแยกคอลัมน์ DESCRIPTION ด้วยพารามิเตอร์การสั่งซื้อโดยตัวคั่น – symbol nแต่ในขณะเดียวกัน ในพารามิเตอร์ เราจะเลือกการแบ่งเป็นแถว ไม่ใช่ในคอลัมน์:
11. อีกครั้ง เราแบ่งคอลัมน์ผลลัพธ์ออกเป็นสองคอลัมน์แยกกัน – พารามิเตอร์และค่า แต่ด้วยเครื่องหมายเท่ากับ
12. การเลือกคอลัมน์ คำอธิบาย1 ทำการบิดตามที่เราทำก่อนหน้านี้ด้วยคำสั่ง แปลง – คอลัมน์ Pivot (แปลง — คอลัมน์ Pivot). คอลัมน์ค่าในกรณีนี้จะเป็นคอลัมน์ที่มีค่าพารามิเตอร์ − คำอธิบาย2  อย่าลืมเลือกฟังก์ชันในพารามิเตอร์ อย่ารวมกัน (อย่ารวม):
13. ยังคงตั้งค่ารูปแบบสำหรับคอลัมน์ทั้งหมดและเปลี่ยนชื่อตามต้องการ และคุณสามารถอัปโหลดผลลัพธ์กลับไปที่ Excel ด้วยคำสั่ง หน้าแรก — ปิดและโหลด — ปิดและโหลดใน… (หน้าแรก — ปิด&โหลด — ปิด&โหลดไปที่…)

และนี่คือรายการคำสั่งซื้อของเราที่โหลดเข้าสู่ Excel จาก Google ปฏิทิน:

ในอนาคต เมื่อเปลี่ยนแปลงหรือเพิ่มคำสั่งซื้อใหม่ลงในปฏิทิน การอัปเดตคำขอของเราด้วยคำสั่งเท่านั้นก็เพียงพอแล้ว ข้อมูล – รีเฟรชทั้งหมด (ข้อมูล — รีเฟรชทั้งหมด).

• ปฏิทินโรงงานใน Excel อัปเดตจากอินเทอร์เน็ตผ่าน Power Query
• การแปลงคอลัมน์เป็นตาราง
• สร้างฐานข้อมูลใน Excel