Tóm lược
Để chuyển đổi một khoản chi phí trong một đơn vị thời gian (tức là hàng ngày, hàng tuần, hàng tháng, v.v.) sang các đơn vị thời gian khác, bạn có thể sử dụng công thức INDEX và MATCH hai chiều. Trong ví dụ được hiển thị, công thức trong E5 (được sao chép trên và xuống) là:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
trong đó dữ liệu (O5: S9), vunits (N5: N9) và hunits (O4: S4) là các dải được đặt tên, như được giải thích bên dưới.
Giải trình
Để chuyển đổi một khoản chi phí trong một đơn vị thời gian (tức là hàng ngày, hàng tuần, hàng tháng, v.v.) sang các đơn vị thời gian khác, bạn có thể sử dụng công thức INDEX và MATCH hai chiều. Trong ví dụ được hiển thị, công thức trong E5 (được sao chép trên và xuống) là:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Công thức này sử dụng bảng tra cứu với các phạm vi được đặt tên như hình dưới đây:
Dải ô được đặt tên: data (O5: S9), vunits (N5: N9) và hunits (O4: S4).
Giới thiệu
Mục đích là chuyển đổi một khoản chi phí trong một đơn vị thời gian thành một khoản chi phí tương đương trong các đơn vị thời gian khác. Ví dụ: nếu chúng tôi có chi phí hàng tháng là 30 đô la, chúng tôi muốn tính chi phí hàng năm là 360 đô la, chi phí hàng tuần là 7,50 đô la, v.v.
Giống như rất nhiều thử thách trong Excel, phần lớn phụ thuộc vào cách bạn tiếp cận vấn đề. Trước tiên, bạn có thể muốn xem xét một chuỗi các công thức IF lồng nhau. Điều này có thể được thực hiện, nhưng bạn sẽ kết thúc với một công thức dài và phức tạp.
Cách tiếp cận rõ ràng hơn là xây dựng một bảng tra cứu có chứa các yếu tố chuyển đổi cho tất cả các chuyển đổi có thể có, sau đó sử dụng công thức INDEX và MATCH hai chiều để truy xuất giá trị cần thiết cho một chuyển đổi nhất định. Khi bạn có giá trị, bạn có thể chỉ cần nhân với số tiền ban đầu.
Bảng chuyển đổi
Bảng chuyển đổi có các giá trị giống nhau cho cả nhãn dọc và nhãn ngang: hàng ngày, hàng tuần, hai tuần một lần, hàng tháng và hàng năm. Các đơn vị "từ" được liệt kê theo chiều dọc và các đơn vị "đến" được liệt kê theo chiều ngang. Đối với mục đích của ví dụ này, chúng tôi muốn đối sánh hàng trước, sau đó đến cột. Vì vậy, nếu chúng tôi muốn chuyển đổi chi phí hàng tháng thành chi phí hàng năm, chúng tôi đối sánh hàng "hàng tháng" và cột "hàng năm" và trả về 12.
Để tự điền bảng, chúng tôi sử dụng kết hợp các công thức và hằng số đơn giản:
Lưu ý: Tùy chỉnh các giá trị chuyển đổi để đáp ứng nhu cầu cụ thể của bạn. Nhập giá trị = 1/7 là một cách dễ dàng để tránh nhập các giá trị thập phân dài.
Công thức tra cứu
Vì chúng tôi cần xác định giá trị chuyển đổi dựa trên hai đầu vào, đơn vị thời gian "từ" và đơn vị thời gian "đến", chúng tôi cần công thức tra cứu hai chiều. INDEX và MATCH cung cấp một giải pháp tốt. Trong ví dụ được hiển thị, công thức trong E5 là:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Làm việc từ trong ra ngoài, hàm MATCH đầu tiên định vị hàng chính xác:
MATCH($D5,vunits,0) // find row, returns 4
Chúng tôi kéo đơn vị thời gian "từ" ban đầu từ cột D, mà chúng tôi sử dụng để tìm hàng bên phải trong phạm vi vunits đã đặt tên (N5: N9). Lưu ý $ D5 là một tham chiếu hỗn hợp với cột bị khóa, vì vậy công thức có thể được sao chép qua.
Hàm MATCH thứ hai định vị cột:
MATCH(F$4,hunits,0) // find column, returns 5
Ở đây, chúng tôi lấy giá trị tra cứu từ tiêu đề cột trong hàng 4 và sử dụng giá trị này để tìm cột "đến" bên phải trong phạm vi hunits được đặt tên (O4: S4). Một lần nữa, lưu ý F $ 4 là một tham chiếu hỗn hợp với hàng bị khóa, vì vậy công thức có thể được sao chép xuống.
Sau khi cả hai công thức MATCH đều trả về kết quả cho INDEX, chúng ta có:
=$C5*INDEX(data,4,5)
Mảng được cung cấp cho INDEX là dữ liệu dải ô được đặt tên , (O5: S9). Với hàng 4 và cột 5, INDEX trả về 12, vì vậy chúng tôi nhận được kết quả cuối cùng là 12000 như sau:
=$C5*INDEX(data,4,5) =1000*12 =12000
