Mong các cao nhân hỗ trợ giải đáp công thức mảng

[Mr. Duck]

Trong lập dự toán xây dựng có sử dụng công thức mảng dùng hàm Sum kết hợp Countif để tổng hợp dữ liệu rất hay ví dụ như sau:
SUM(COUNTIF(LK_TA!$8:$22;{""\2\3\4\5\6\7\8\9\10\11\12}&$D21)*{1\2\3\4\5\6\7\8\9\10\11\12})
Rất mong các cao nhân có thể chỉ giáo cách thức hoạt động của công thức mảng này như thế nào không ạ.
Xin gửi file kèm theo. Trân trọng cảm ơn mọi người!

 

[bebo021999]

Để tìm hiểu công thức ghép, nhất là công thức mảng (như COUNTIF trong ví dụ), bạn cần biết thủ thuật sau đây: trên thanh công thức, quét chọn công thức nào đó và nhấn F9, để xem kết quả
Xét CT tại ô F21, với D21 = "CN-22P"
CT của bạn có dạng:
=SUM(Array1 * Array2)

Trong đó:

Array 1: COUNTIF(LK_TA!$8:$22,{"",2,3,4,5,6,7,8,9,10,11,12}&$D21)
Nếu bạn quét chọn phần này trên thanh công thức thì kết quả là:{0,0,2,0,0,2,0,0,0,0,0,0} (Với số >0 nằm ở vị trí thứ 3 và thứ 6)
Có nghĩa là: trong vùng từ dòng 8:22: có 2 ô chứa chuỗi "3CN-22P" và 2 ô chứa chuỗi "6CN-22P"

Array2: {1,2,3,4,5,6,7,8,9,10,11,12}

Khi nhân 2 array với nhau:
{0,0,2,0,0,2,0,0,0,0,0,0}*{1,2,3,4,5,6,7,8,9,10,11,12}
={0,0,6,0,0,12,0,0,0,0,0,0}

Bỏ vào trong hàm SUM để cộng dồn thành 18
Và lưu ý cách dùng mảng trong COUNTIF:
=COUNTIF(vùng_data,ô) cho cách dùng bình thường (với ô riêng lẻ)
hoặc
=COUNTIF(vùng_data,vùng_cần_đếm)) cách dùng mảng cho vùng cần đếm. Trả về mảng kết quả với số phần tử bằng với số phần tử trong vùng cần đếm.

 

[Mr. Duck]

Array 1: COUNTIF(LK_TA!$8:$22,{"",2,3,4,5,6,7,8,9,10,11,12}&$D21)
Nếu bạn quét chọn phần này trên thanh công thức thì kết quả là:{0,0,2,0,0,2,0,0,0,0,0,0} (Với số >0 nằm ở vị trí thứ 3 và thứ 6)
Có nghĩa là: trong vùng từ dòng 8:22: có 2 ô chứa chuỗi "3CN-22P" và 2 ô chứa chuỗi "6CN-22P"

Array2: {1,2,3,4,5,6,7,8,9,10,11,12}

Khi nhân 2 array với nhau:
{0,0,2,0,0,2,0,0,0,0,0,0}*{1,2,3,4,5,6,7,8,9,10,11,12}
={0,0,6,0,0,12,0,0,0,0,0,0}

Rất cảm ơn bạn về câu trả lời rất chi tiết.
Có thể hiểu là trong khu vực từ dòng 8 đến 22, đếm số tổ hợp có thể có giữa mảng {1\2\3\4\5\6\7\8\9\10\11\12} với D21, xong nhân với chính mảng này. Sau đó lồng vào hàm sum để lấy tổng.
Tuy vậy, tại sao ở vị trí 1 lại là dấu "", cái này nó có qui định, qui tắc gì không (khả năng là để lồng các tổ hợp vào dấu nháy kép này rồi).
Bạn vui lòng cho biết thêm nếu có.

 

[VetMini]

"cao nhơn" không mấy người chịu khó đi vào bươi rác để giải thích cái đó đâu.
Cũng như bạn, nếu chịu khó làm dân ngu khu đen tự thử đi thử lại vài lần thì đã không cần phải hỏi ai.
Cái này không khó, chỉ do hỏi người khác thì dễ hơn thôi.

 

[cantl]

Rất cảm ơn bạn về câu trả lời rất chi tiết.
Có thể hiểu là trong khu vực từ dòng 8 đến 22, đếm số tổ hợp có thể có giữa mảng {1\2\3\4\5\6\7\8\9\10\11\12} với D21, xong nhân với chính mảng này. Sau đó lồng vào hàm sum để lấy tổng.
Tuy vậy, tại sao ở vị trí 1 lại là dấu "", cái này nó có qui định, qui tắc gì không (khả năng là để lồng các tổ hợp vào dấu nháy kép này rồi).
Bạn vui lòng cho biết thêm nếu có.

Tại sao F21 = 18 là đúng nhỉ?

Số 3 số 6 trước CN này nghĩa là gì nhỉ?
Ký hiệu, công dụng khác nhau mà cùng là 3, cùng là 6.

 

[bebo021999]

Rất cảm ơn bạn về câu trả lời rất chi tiết.
Có thể hiểu là trong khu vực từ dòng 8 đến 22, đếm số tổ hợp có thể có giữa mảng {1\2\3\4\5\6\7\8\9\10\11\12} với D21, xong nhân với chính mảng này. Sau đó lồng vào hàm sum để lấy tổng.
Tuy vậy, tại sao ở vị trí 1 lại là dấu "", cái này nó có qui định, qui tắc gì không (khả năng là để lồng các tổ hợp vào dấu nháy kép này rồi).
Bạn vui lòng cho biết thêm nếu có.

Tại sao thì bạn cứ bôi đen nó,nhấn F9 thì ra thôi
{"",2,3,4,5,6,7,8,9,10,11,12}&$D21
Kết qủa là
{"CN-22P","2CN-22P","3CN-22P","4CN-22P","5CN-22P","6CN-22P","7CN-22P","8CN-22P","9CN-22P","10CN-22P","11CN-22P","12CN-22P"}
Lần lượt nối chuỗi trong mảng với D21
""&D21 = "CN-22P"
2&D21 = "2CN-22P"
3&D21 = "3CN-22P"
...
12&D21 = "12CN-22P"
Sau đó đếm sự xuất hiện của chúng trong vùng 8:22
và lần lượt nhân kết quả với {1,2,3,...,12}
Cuối cùng là SUM tất cả lại.

 

[Mr. Duck]

Tại sao F21 = 18 là đúng nhỉ?

Số 3 số 6 trước CN này nghĩa là gì nhỉ?
Ký hiệu, công dụng khác nhau mà cùng là 3, cùng là 6.
View attachment 295838

Cái này nó chỉ là thuật ngữ chuyên ngành thôi. Nó là ký hiệu của chuỗi néo cấp 22kV loại polymer --> nghĩa là vị trí đó có 3 chuỗi néo, 6 chuỗi néo. Thế thôi ))

 

[bebo021999]

Tại sao F21 = 18 là đúng nhỉ?
Số 3 số 6 trước CN này nghĩa là gì nhỉ?
Ký hiệu, công dụng khác nhau mà cùng là 3, cùng là 6.

Mình không phải người trong ngành, nhưng căn cứ theo cách dùng công thức mình đoán như sau:
"CN-22P" là mã chính, còn con số từ 1-12 là số lượng cần dùng.
Vì "3CN-22P" sẽ nhân với số 3, "6CN-22P" sẽ nhân với 6...
trong vùng 8:22 có 2 cái "3CN-22P" và 2 cái "6CN-22P", nghĩa là cần dùng 2*3 + 2*6 = 18 cái "CN-22P"