Tạo một bảng tiền tệ Show
Gần danh sách nhân viên tạo như sau
Theo Lương USD viết công thức
H2 là tiền lương bằng đơn vị tiền tệ (ban đầu) Tên tôi là Penny Li, một kế toán thuế xuyên biên giới và tôi thường gặp các trường hợp trong công việc của mình khi tôi cần chuyển đổi ngoại tệ sang đô la Mỹ. Tôi đã quyết định xây dựng một công cụ chuyển đổi ngoại tệ trong Excel có khả năng tạo tỷ giá hối đoái trung bình trong một ngày cũng như tỷ giá hối đoái trung bình cho nhiều loại tiền tệ Khách quanMục tiêu của công cụ này là tự động tạo tỷ giá hối đoái trung bình hàng ngày và lịch sử cho 17 loại ngoại tệ, với đầu vào của người dùng cho loại tiền được chuyển đổi và ngày hoặc phạm vi ngày để tạo tỷ giá hối đoái Băng hìnhChi tiếtBước đầu tiên là tải tỷ giá hối đoái xuống sổ làm việc Excel. Dữ liệu lịch sử về tỷ giá hối đoái được cung cấp bởi ngân hàng Dự trữ Liên bang tại liên kết này. Dữ liệu được tải xuống và dán vào Excel với một nhóm cột cho mỗi loại tiền tệ như minh họa bên dưới Với tỷ giá hối đoái của lãi suất được lưu trữ trong Excel, chúng ta sẽ sử dụng các hàm tra cứu để dễ dàng lấy ra tỷ giá mong muốn trong một khoảng thời gian cụ thể Để trích xuất tỷ giá hối đoái có ý nghĩa hữu ích cho các tình huống cụ thể, chúng tôi sẽ thực hiện theo các bước bên dưới để thiết lập công cụ chuyển đổi • Liệt kê các loại tiền có sẵn Liệt kê các loại tiền tệ có sẵnCho dù trích xuất tỷ giá hối đoái vào một ngày hoặc tỷ giá hối đoái trung bình trong một phạm vi ngày từ dữ liệu được lưu trữ, điều quan trọng là phải có danh sách tất cả các loại tiền tệ có sẵn. Điều này sẽ hữu ích cho việc tham khảo từng ngoại tệ. Danh sách dưới đây là danh sách mà tôi thường sử dụng Thiết lập các ô nhập liệu cho ngày và số tiền tệĐể đánh dấu rõ ràng các ô đầu vào nơi người dùng cần nhập loại tiền họ muốn chuyển đổi và ngày họ muốn tỷ giá hối đoái được trích xuất, tôi đã áp dụng Kiểu ô đầu vào (Trang chủ > Kiểu ô > Đầu vào) như hình bên dưới Thiết lập chức năng CHOOSE với VLOOKUP để trích xuất tỷ giá hối đoái vào ngày được chọnDưới đây là một phần của hàm CHOOSE được sử dụng để trích xuất tỷ giá hối đoái, trong đó C3 là số tiền tệ mong muốn và C4 là ngày mong muốn =CHOOSE(C3,VLOOKUP(C4,'FX Rate'!A:B,2,FALSE), VLOOKUP(C4,'FX Rate'!D:E,2,FALSE),... Trong công thức này, hàm CHOOSE thực hiện hàm VLOOKUP tương ứng với số tiền tệ trong C3. Nếu số tiền tệ là 1, thì nó sẽ thực hiện VLOOKUP trên cột A. B trên bảng Tỷ giá hối đoái. Nếu số tiền tệ là 2, nó sẽ thực hiện VLOOKUP trên cột D. E, v.v. Ghi chú. Để biết thêm thông tin chi tiết về các chức năng được sử dụng, hãy xem bài đăng CHỌN và bài đăng VLOOKUP trước đây của chúng tôi Thiết lập các điều kiện chuyển đổi để nhân hoặc chia tỷ giá hối đoáiVì đồng Euro và Bảng Anh có giá trị cao hơn đồng đô la Mỹ và ngân hàng dự trữ liên bang lưu trữ đô la Úc và đô la New Zealand với giá trị nhỏ hơn một, nên chúng ta sẽ cần thiết lập một câu lệnh IF ngắn để đảm bảo độ chính xác khi sử dụng tỷ giá hối đoái Trong câu lệnh IF được thiết lập bên dưới, toán tử nhân sẽ được sử dụng khi người dùng đã chỉ định Bảng Anh, Euro, NZD hoặc đô la Úc làm đơn vị tiền tệ được chuyển đổi, trong khi toán tử chia sẽ được sử dụng cho tất cả những người khác =IF((OR(E3="GBP British Pound",E3="EUR Euro",E3="NZD New Zealand Dollar",E3="AUD Australian Dollar")),D10*C5,D10/C5) Sau đó, chúng tôi có thể sử dụng tỷ giá đã truy xuất để chuyển đổi báo cáo ngoại tệ thành báo cáo bằng đô la Mỹ. Ví dụ: trên báo cáo tài chính vào một ngày cụ thể, chẳng hạn như bảng cân đối kế toán Các biến thể khácỞ trên chỉ hữu ích để chuyển đổi số tiền liên quan đến một ngày sang đô la Mỹ, chẳng hạn như để sử dụng trong bảng cân đối kế toán. Tuy nhiên, công cụ này có thể được mở rộng để trích xuất tỷ giá hối đoái trung bình trong một phạm vi ngày nếu chúng ta thêm trường ngày thứ hai làm ngày kết thúc của một khoảng thời gian như được hiển thị bên dưới Để trích xuất tỷ giá hối đoái trung bình trong một khoảng thời gian, chúng ta sẽ cần thay thế hàm VLOOKUP được sử dụng ở trên bằng hàm AVERAGEIFS, đồng thời sử dụng các toán tử “nhỏ hơn hoặc bằng” và “lớn hơn hoặc bằng”. Dưới đây là hàm CHOOSE một phần sử dụng AVERAGEIFS thay vì VLOOKUP, trong đó C3 vẫn là số tiền tệ, C4 là ngày bắt đầu và C5 là ngày kết thúc =CHOOSE(C3,AVERAGEIFS('FX Rate'!B:B,'FX Rate'!A:A,">="&C4,'FX Rate'!A:A,"<="&C5), ... Điều này có thể hữu ích cho các báo cáo cho một khoảng thời gian đã kết thúc, chẳng hạn như báo cáo thu nhập Ghi chú. để biết thêm thông tin về hàm AVERAGEIFS, hãy xem các bài đăng này Sử dụng câu lệnh IF và xác thực dữ liệuChúng tôi có thể sử dụng tính năng xác thực dữ liệu để thay thế số ngoại tệ trong ví dụ trên bằng menu thả xuống có tên tiền tệ Để tạo danh sách thả xuống xác thực dữ liệu, hãy đi tới Dữ liệu > Xác thực dữ liệu Ghi chú. để biết thêm chi tiết về Xác thực dữ liệu, hãy xem các bài đăng này Nếu không có số tiền tệ, chúng ta có thể sử dụng các câu lệnh IF thay cho hàm CHOOSE, trong đó thay vì C3, chúng ta đánh vần tên đầy đủ của các loại tiền tệ =IF(C3="CNY Chinese Yuan",VLOOKUP(C4,'FX Rate'!A:B,2,FALSE),... Tương tự khi xác định tỷ giá hối đoái bình quân trong một thời kỳ =IF(C3="CNY Chinese Yuan",AVERAGEIFS('FX Rate'!B:B,'FX Rate'!A:A,">="&C4,'FX Rate'!A:A,"<="&C5),... Phần kết luậnBạn có thể sử dụng công cụ này để chuyển đổi số lượng ngoại tệ sang USD. Cho đến nay, tôi đã sử dụng công cụ này để chuyển đổi các mục trong bảng cân đối kế toán và báo cáo thu nhập, nhưng tôi rất muốn biết những trường hợp khác mà công cụ này có thể hữu ích. Vui lòng cho tôi biết bằng cách gửi bình luận bên dưới… cảm ơn |