Cách Sử Dụng Các Hàm SUMPRODUCT, INDEX, MATCH Trong Excel Để Quản Lý Chi Phí Dự Án Xây Dựng

Hướng dẫn này HocThatNhanh sẽ giúp bạn nâng cao kỹ năng Excel của mình bằng cách áp dụng các hàm SUMPRODUCT, INDEX và MATCH để xử lý dữ liệu dạng mảng, lồng ghép các hàm, kiểm tra nhiều điều kiện và tổng hợp giá trị.

Công thức mảng sử dụng hàm SUMPRODUCT trong Excel:

=SUMPRODUCT((criteria_range1=criteria1)*(criteria_range2=criteria2)*sum_range)

Công thức lồng ghép INDEX và MATCH:

=INDEX(return_range, MATCH(1, (criteria_range1=criteria1)*(criteria_range2=criteria2), 0))

Giải thích ý nghĩa

Hàm SUMPRODUCT:

• SUMPRODUCT là hàm tính tổng các tích của các phạm vi hoặc mảng tương ứng.
• Cấu trúc: =SUMPRODUCT(array1, [array2], [array3], ...)
  • array1, array2, …: Các phạm vi hoặc mảng cần tính tích và tổng.

Hàm INDEX:

• INDEX trả về giá trị của một ô trong bảng hoặc phạm vi dựa trên số hàng và cột.
• Cấu trúc: =INDEX(array, row_num, [column_num])
  • array: Phạm vi hoặc bảng dữ liệu.
  • row_num: Số hàng của giá trị cần tìm.
  • [column_num]: Số cột của giá trị cần tìm (tùy chọn).

Hàm MATCH:

• MATCH tìm kiếm một giá trị trong một phạm vi và trả về vị trí tương đối của giá trị đó.
• Cấu trúc: =MATCH(lookup_value, lookup_array, [match_type])
  • lookup_value: Giá trị cần tìm.
  • lookup_array: Phạm vi tìm kiếm.
  • [match_type]: Loại khớp (0 – chính xác, 1 – nhỏ hơn hoặc bằng, -1 – lớn hơn hoặc bằng).

Tham gia nhóm Zalo

Hỏi đáp Excel VBA GoogleSheet cho Kỹ sư

Tham gia ngay

Trường hợp áp dụng các hàm SUMPRODUCT, INDEX và MATCH để xử lý dữ liệu mảng trong Excel

1. Tính tổng chi phí theo nhiều tiêu chí:

   • Khi bạn cần tổng hợp chi phí cho các hạng mục cụ thể trong dự án xây dựng, hàm SUMPRODUCT cho phép bạn tính tổng các giá trị dựa trên nhiều điều kiện mà không cần sử dụng các cột phụ.

2. Trích xuất giá trị dựa trên nhiều điều kiện:

   • Sử dụng hàm lồng ghép INDEX và MATCH để trích xuất giá trị từ bảng dữ liệu dựa trên nhiều tiêu chí. Điều này giúp bạn tìm kiếm thông tin một cách chi tiết và chính xác hơn.

3. Quản lý chi phí và tiến độ dự án:

   • Kết hợp các hàm nâng cao để kiểm tra và tổng hợp dữ liệu chi tiết về chi phí và tiến độ dự án, giúp bạn quản lý và ra quyết định chính xác hơn.

Ví dụ cụ thể sử dụng các hàm SUMPRODUCT, INDEX và MATCH

Giả sử bạn có bảng dữ liệu quản lý chi phí dự án xây dựng như sau:

Bạn muốn tính tổng chi phí cho các hạng mục đã hoàn thành trong Tháng 1 và Tháng 2, bạn có thể sử dụng công thức:

=SUMPRODUCT((E2:E6="Hoàn Thành")*(C2:C6="Tháng 1")*(D2:D6)) + SUMPRODUCT((E2:E6="Hoàn Thành")*(C2:C6="Tháng 2")*(D2:D6))

Bạn muốn trích xuất chi phí của hạng mục “Hoàn thiện kiến trúc” trong “Tháng 2”, bạn có thể sử dụng công thức:

=INDEX(D2:D6, MATCH(1, (B2:B6="Hoàn thiện kiến trúc")*(C2:C6="Tháng 2"), 0)) 

(Nếu bạn đang thiết lập dấu ngăn cách trong Excel là dấu chấm phẩy (;) thì công thức sẽ là:

=INDEX(D2:D6; MATCH(1; (B2:B6="Hoàn thiện kiến trúc")*(C2:C6="Tháng 2"); 0))

Kết luận

Đối với người dùng đã thành thạo Excel cơ bản, việc nắm vững và áp dụng các hàm này sẽ nâng cao kỹ năng quản lý dự án của bạn lên một tầm cao mới, giúp bạn xử lý dữ liệu phức tạp và ra quyết định chính xác.

Hi vọng bài viết và ví dụ về cách sử dụng các hàm nâng cao như SUMPRODUCT, INDEX, và MATCH trong Excel của HocThatNhanh có thể giúp bạn quản lý chi phí dự án nhanh và hiệu quả hơn.