สร้างตัวติดตามใบแจ้งหนี้หลายสกุลเงินใน Excel

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

ดาวน์โหลดไฟล์

ดาวน์โหลด ไฟล์ติดตามใบแจ้งหนี้ แล้วเปิดใน Excel มีตัวอย่างใบแจ้งหนี้ snapshot หลายสกุล คอลัมน์ USD และ audit trail

ทำความเข้าใจสี่ชีต

Invoice Tracker เก็บใบแจ้งหนี้และสูตร FX Snapshots เก็บข้อมูลตามวันที่ Audit Trail เก็บเหตุผลการตรึง และ Read Me อธิบายขั้นตอนและข้อจำกัด

เพิ่มใบแจ้งหนี้ในสกุลเงินเดิม

กรอก ID ลูกค้า วันที่ สกุลเงิน ยอดสุทธิ ภาษี วันที่อัตรา และสถานะในเซลล์สีน้ำเงิน เก็บ Net, Tax และ Gross เป็นสกุลเงินของใบแจ้งหนี้

เพิ่ม snapshot อัตราตามวันที่

เพิ่มข้อมูลย้อนหลังที่ใช้จริงและเก็บวันที่ สกุลเงิน source market_session data_updated_at และอัตรา อัตราหมายถึงหน่วยสกุลเงินต่อ 1 USD ใช้ Snapshot ID ที่ไม่ซ้ำ เช่น EUR-20260602-v1 ตรวจสอบว่า ID มีเพียงหนึ่งแถวและสกุลเงินของ snapshot ตรงกับใบแจ้งหนี้ หากแก้ไขให้เพิ่ม v2 และอย่าทับ v1 data_updated_at คือเวลาที่ข้อมูลย้อนหลังถูกอัปเดต ไม่ใช่ effective_at

{
  "result": "success",
  "base": "USD",
  "date": "2026-06-02",
  "source": "ecb_daily",
  "sources": { "EUR": "ecb_daily" },
  "market_session": "open",
  "timestamp": "2026-09-08T11:52:18Z",
  "data_updated_at": "2026-06-02T00:00:00Z",
  "is_forward_filled": false,
  "rates": { "EUR": 0.85844 },
  "notice": "Indicative rates, not for settlement."
}

ตรวจสอบคอลัมน์ USD จากสูตร

ไฟล์จะจับคู่วันที่และสกุลเงินกับ FX Snapshots แล้วหารยอดด้วย FX Rate / USD หากไม่มี snapshot คอลัมน์ USD จะว่าง สูตรครอบคลุมแถว100ของใบแจ้งหนี้และ snapshot; ขยายทั้งสองช่วงพร้อมกัน Posted เป็นเพียงป้ายกำกับ ไม่ได้ล็อกเซลล์หรือแช่สูตรอัตโนมัติ

=IF(OR(F6<0,G6<0,H6<0,K6="",L6="Missing snapshot ID",L6="Duplicate snapshot ID",L6="Currency mismatch",L6="Invalid rate"),"",F6/K6)

ตรึงค่าที่บันทึกแล้ว

หลังบันทึกใบแจ้งหนี้แล้วอย่าเปลี่ยน snapshot หากต้องแก้ไขให้เพิ่มแถวใหม่และบันทึกเหตุผลใน Audit Trail

เพิ่ม snapshot ใหม่ด้วย Power Query

สคริปต์ Power Query ดึงวันที่และสกุลเงินที่ระบุ อย่าใส่ API key ในไฟล์ที่แชร์ Power Query ให้ผลลัพธ์เก้าคอลัมน์ A:I โหลดลง staging ก่อน คัดลอกเฉพาะแถวข้อมูลไม่เอาหัวตาราง แล้ว Paste Special เป็น values ตั้งแต่แถว7 Refresh All แทนที่ผลลัพธ์ query ไม่ได้เพิ่มประวัติถาวร Windows ใช้ Data → Get Data → From Other Sources → Blank Query; Microsoft 365 Mac ที่รองรับใช้ Data → Get Data → Launch Power Query Editor เลือก Anonymous และใช้ server proxy หากต้องใช้คีย์

let
    SnapshotDate = "2026-06-02",
    Currency = Text.Upper(Text.Trim("EUR")),
    Revision = "v1",
    SelectedDate = Date.FromText(SnapshotDate, [Format = "yyyy-MM-dd", Culture = "en-US"]),
    RelativePath = "v1/" & Date.ToText(SelectedDate, "yyyy-MM-dd") & "/USD",
    Endpoint = "https://api.exchangerate.dev/" & RelativePath & "?symbols=" & Currency,
    Payload = Json.Document(Web.Contents("https://api.exchangerate.dev", [
        RelativePath = RelativePath,
        Query = [symbols = Currency],
        Timeout = #duration(0, 0, 0, 10)
    ])),
    Source = if Payload[base] <> "USD" or Payload[date] <> SnapshotDate
        then error "Unexpected response base or date" else Payload,
    RawRate = Record.Field(Source[rates], Currency),
    Rate = if not Value.Is(RawRate, type number) or RawRate <= 0
        then error "Expected a positive numeric rate" else RawRate,
    SnapshotId = Currency & "-" & Text.Replace(SnapshotDate, "-", "") & "-" & Revision,
    Notes = "Response timestamp=" & Source[timestamp] & "; is_forward_filled=" &
        (if Source[is_forward_filled] then "true" else "false"),
    Snapshot = #table(
        {"Snapshot ID", "Snapshot Date", "Currency", "Source", "Market Session", "Data Updated At (UTC)", "Rate / USD", "API Endpoint", "Notes"},
        {{SnapshotId, SelectedDate, Currency, Record.Field(Source[sources], Currency), Source[market_session], Source[data_updated_at], Rate, Endpoint, Notes}}
    )
in
    Snapshot

เก็บบันทึกการตรวจสอบ

บันทึกเวลาที่โพสต์ snapshot แหล่งข้อมูล/เซสชัน และเหตุผลที่ตรึงค่า เก็บ response เดิมหากกระบวนการต้องใช้หลักฐาน เก็บ timestamp และ is_forward_filled ใน Notes พร้อม Snapshot ID แหล่งข้อมูล session และเหตุผลการแก้ไข

ใช้ผลลัพธ์เพื่อรายงานเท่านั้น

นี่เป็นตัวอย่างเพื่อการรายงานและวิเคราะห์ ไม่ได้กำหนดภาษี บัญชี compliance หรือ settlement ตัวอย่างรองรับถึงแถว100 ขยายสูตรและ lookup พร้อมกัน