Cách chuyển đổi nhiều loại tiền tệ trong excel

Tạo một bảng tiền tệ

Column A        Column B Column C
    Currency    Rate    Rate in USD
    AUD         0.77    0.77          =B2
    JPY         112.32  0.008903134   =1/B3
    GBD         1.3     1.3
    EUR         1.14    1.14
    CAD         1.2979  =1/B6 (1/1.2979)

Gần danh sách nhân viên tạo như sau

           Column G       Column H
Employee    Currency      Salary    USD Salary
Mike        AUD           10000     7700     =H2*INDEX($C$2:$C$5,MATCH(G2,$A$2:$A$5,0))
Jacky       JPY           80000    712.2507

Theo Lương USD viết công thức

=H2*INDEX($C$2:$C$5,MATCH(G2,$A$2:$A$5,0))

H2 là tiền lương bằng đơn vị tiền tệ (ban đầu)
G2 là đơn vị tiền tệ
C2. C5 tỷ giá USD trong bảng Tiền tệ thay đổi nó để tương ứng với dữ liệu Tiền tệ của bạn
A2. A5 Thay đổi mã tiền tệ để tương ứng với bảng Tiền tệ của bạn

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 quan

Mụ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ình

Chi tiết

Bướ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
• Thiết lập các ô nhập cho số và ngày của loại tiền
• Thiết lập chức năng CHOOSE với VLOOKUP để trích xuất tỷ giá hối đoái
• Set up conversion conditions for multiplying or dividing exchange rates

Liệt kê các loại tiền tệ có sẵn

Cho 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ọn

Dướ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ái

Vì đồ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ệu

Chú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ận

Bạ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