Cho em hỏi sử dụng hàm Averageif để đánh giá Vendor (6 người xem)

[mickey]

Các anh chị cho em hỏi: em có file excel dùng để đánh giá vendor như đính kèm. Trong đó:

- Sheet Evaluationsheet là sheet tổng hợp các vendor được đánh giá vào cuối quý.
- Sheet Claim no.1 và Claim no.2 là từng vụ khiếu nại có sử dụng vendor. Mỗi Claim chỉ sử dụng 1 vendor trong danh sách 20 vendor của Evaluationsheet. Số lượng Claim không biết trước được trong mỗi quý (đến cuối quý mới biết). Các tiêu chí đánh giá trong từng CLaim là giống với các tiêu chí trong sheet Evaluationsheet

Vậy nếu mình không dùng macro/VBA, chỉ sử dụng công thức, mấy anh chị chỉ giúp mình nên lập công thức như thế nào để khi người dùng tạo thêm claim, đồng thời đánh giá vendor thì sheet Evaluationsheet sẽ tự động tính trung bình cho từng tiêu chí. Em lấy ví dụ để dễ hiểu hơn

Ví dụ: Em tạo thêm Claim no.3 bằng cách copy Claim no.2, sau đó sửa lại:

- Tại sheet claim no.3 chọn Vendor 4 tại Dropdown List ô B2.
- Sau đó cho điểm từ cột E9 đến cột E16 (sheet claim no.3)
- Tại sheet Evaluationsheet sẽ tự động tính trung bình tại các tiêu chí từ Claim no.1, Claim no.2, Claim no.3 cho vendor 4. Nếu Claim no.1 và Claim no.2 hiện đang đánh giá cho vendor khác (1,2) nên số điểm trung bình từng tiêu chí của 3 claim 1,2,3 sẽ là số điểm của từng tiêu chí trong claim 3.

Em sử dụng Averageif mà hiện tại bị báo lỗi. Xin các cao nhân giúp đỡ. Em cảm ơn

 

[tieuthubuongbinh]

Các anh chị cho em hỏi: em có file excel dùng để đánh giá vendor như đính kèm. Trong đó:

- Sheet Evaluationsheet là sheet tổng hợp các vendor được đánh giá vào cuối quý.
- Sheet Claim no.1 và Claim no.2 là từng vụ khiếu nại có sử dụng vendor. Mỗi Claim chỉ sử dụng 1 vendor trong danh sách 20 vendor của Evaluationsheet. Số lượng Claim không biết trước được trong mỗi quý (đến cuối quý mới biết). Các tiêu chí đánh giá trong từng CLaim là giống với các tiêu chí trong sheet Evaluationsheet

Vậy nếu mình không dùng macro/VBA, chỉ sử dụng công thức, mấy anh chị chỉ giúp mình nên lập công thức như thế nào để khi người dùng tạo thêm claim, đồng thời đánh giá vendor thì sheet Evaluationsheet sẽ tự động tính trung bình cho từng tiêu chí. Em lấy ví dụ để dễ hiểu hơn

Ví dụ: Em tạo thêm Claim no.3 bằng cách copy Claim no.2, sau đó sửa lại:

- Tại sheet claim no.3 chọn Vendor 4 tại Dropdown List ô B2.
- Sau đó cho điểm từ cột E9 đến cột E16 (sheet claim no.3)
- Tại sheet Evaluationsheet sẽ tự động tính trung bình tại các tiêu chí từ Claim no.1, Claim no.2, Claim no.3 cho vendor 4. Nếu Claim no.1 và Claim no.2 hiện đang đánh giá cho vendor khác (1,2) nên số điểm trung bình từng tiêu chí của 3 claim 1,2,3 sẽ là số điểm của từng tiêu chí trong claim 3.

Em sử dụng Averageif mà hiện tại bị báo lỗi. Xin các cao nhân giúp đỡ. Em cảm ơn

Averageif mà băng qua các sheet là ko thể rồi, nên mình dùng các cột phụ nha. Bạn xem file nhé.
Cái list of sheets thì bạn đọc link trong bài #16 ở link này nhé, cứ máy móc làm theo tự nhiên ra được cái list này thôi ha.
File lưu dưới dạng binary nha, bạn mở file không được thì có thể do excel của bạn thấp hơn version file này nên ko hiểu được.

https://www.giaiphapexcel.com/diendan/threads/một-công-thức-để-lấy-dòng-cho-indirect.141530/

@dazkangel , anh @Phan Thế Hiệp có cách nào ko dùng cột phụ ko, cho em thỉnh giáo nè

 

[dazkangel]

Averageif mà băng qua các sheet là ko thể rồi, nên mình dùng các cột phụ nha. Bạn xem file nhé.
Cái list of sheets thì bạn đọc link trong bài #16 ở link này nhé, cứ máy móc làm theo tự nhiên ra được cái list này thôi ha.
File lưu dưới dạng binary nha, bạn mở file không được thì có thể do excel của bạn thấp hơn version file này nên ko hiểu được.

https://www.giaiphapexcel.com/diendan/threads/một-công-thức-để-lấy-dòng-cho-indirect.141530/

@dazkangel , anh @Phan Thế Hiệp có cách nào ko dùng cột phụ ko, cho em thỉnh giáo nè

Công thức cơ bản thì như sau, có thể nhấn Ctrl+Shift+Enter hoặc không:

=IFERROR(SUM(SUMIF(INDIRECT("'Claim no."&ROW($1:$4)&"'!B9:B16"),C$8,INDIRECT("'Claim no."&ROW($1:$4)&"'!E9:E16"))*COUNTIF($B12,T(INDIRECT("'Claim no."&ROW($1:$4)&"'!B2"))))/SUM(COUNTIF(INDIRECT("'Claim no."&ROW($1:$4)&"'!B2"),$B12)),"")

 

[tieuthubuongbinh]

Công thức cơ bản thì như sau, có thể nhấn Ctrl+Shift+Enter hoặc không:

=IFERROR(SUM(SUMIF(INDIRECT("'Claim no."&ROW($1:$4)&"'!B9:B16"),C$8,INDIRECT("'Claim no."&ROW($1:$4)&"'!E9:E16"))*COUNTIF($B12,T(INDIRECT("'Claim no."&ROW($1:$4)&"'!B2"))))/SUM(COUNTIF(INDIRECT("'Claim no."&ROW($1:$4)&"'!B2"),$B12)),"")

Mỗi lần sẽ gắn sheet vào thì đi sửa CT là ko tiện rồi. Vì thực tế phát sinh sẽ có thể có nhiều sheet claim lắm

 

[dazkangel]

Mỗi lần sẽ gắn sheet vào thì đi sửa CT là ko tiện rồi. Vì thực tế phát sinh sẽ có thể có nhiều sheet claim lắm

Thì sửa cái này:
ROW($1:$4) thành ROW($1:$100) hay 1000 gì đó là được mà, thực tế đã làm dữ liệu thì biết giới hạn là điều chỉnh, có nhiều cách để điều chỉnh công thức như đặt thành name thì chỉ cần sửa trong name là mọi thứ thay đổi theo.

 

[tieuthubuongbinh]

Công thức cơ bản thì như sau, có thể nhấn Ctrl+Shift+Enter hoặc không:

=IFERROR(SUM(SUMIF(INDIRECT("'Claim no."&ROW($1:$4)&"'!B9:B16"),C$8,INDIRECT("'Claim no."&ROW($1:$4)&"'!E9:E16"))*COUNTIF($B12,T(INDIRECT("'Claim no."&ROW($1:$4)&"'!B2"))))/SUM(COUNTIF(INDIRECT("'Claim no."&ROW($1:$4)&"'!B2"),$B12)),"")

Sao CT trả về dòng đầu tiên à, còn các dòng sau nó ko hiện ra kết quả

Và vì sao làm tròn được vậy dazkangel?

 

[dazkangel]

Sao CT trả về dòng đầu tiên à, còn các dòng sau nó ko hiện ra kết quả

Và vì sao làm tròn được vậy dazkangel?


View attachment 216493

Bạn xem file vậy:

 

[tieuthubuongbinh]

Bạn xem file vậy:

Vậy là phải CSE rồi, tại bài #4 dazkangel bảo ko bấm cũng được hihi
Nhưng mà muốn làm sẵn $1:$100 không có được. Vậy thì cũng bị hạn chế, vì mỗi lần gắn sheet mỗi lần sửa row cũng cực

 

[dazkangel]

Vậy là phải CSE rồi, tại bài #4 dazkangel bảo ko bấm cũng được hihi

Sở dĩ nói điều trên vì excel của mình không cần nhấn Ctrl+Shift+Enter mọi công thức đều ra kết quả.

 

[tieuthubuongbinh]

Sở dĩ nói điều trên vì excel của mình không cần nhấn Ctrl+Shift+Enter mọi công thức đều ra kết quả.

Version excel đã làm nên sự khác biệt này hả?

 

[dazkangel]

Version excel đã làm nên sự khác biệt này hả?

Có thể nói vậy, nhưng tốt nhất hạn chế bàn chuyện phiếm trên đây...

 

[tieuthubuongbinh]

- Tại sheet Evaluationsheet sẽ tự động tính trung bình tại các tiêu chí từ Claim no.1, Claim no.2, Claim no.3 cho vendor 4. Nếu Claim no.1 và Claim no.2 hiện đang đánh giá cho vendor khác (1,2) nên số điểm trung bình từng tiêu chí của 3 claim 1,2,3 sẽ là số điểm của từng tiêu chí trong claim 3.

Em sử dụng Averageif mà hiện tại bị báo lỗi. Xin các cao nhân giúp đỡ. Em cảm ơn

Mình gửi bạn 1 cách giảm bớt cột phụ dựa trên CT của @dazkangel (sheet Evaluationsheet C2)
Mỗi khi bạn thêm sheet vào thì tự N:N sẽ update (bạn cần kéo CT lại thì nó mới hiện ra, kiểu như refersh ấy dù trước đó đã để sẵn CT dòng đó)
Và mình đang đếm mảng cho indirect theo độ dài của N:N mà chỉ count "Claim no.*", nghĩa là nếu bạn insert thêm sheet đúng tên"Claim no." và số đơn liên tiếp (consecutive number) thì mới ra đúng kết quả. (vd: bạn copy claim no.2 thành claim no.2 (1) thì nó ko gắn sheet 2 (1) này vào CT, hoặc claim no. 7 mà trước đó ko có 6 thì cũng ko ra).

Đó là hạn chế hen.
Nên mình vẫn thấy cách 1 của mình là tối ưu nhất, vì bạn gắn sheet tên gì vào cũng ko sai. Nếu bạn thấy xấu report thì đem qua 1 sheet phụ rồi giấu đi.

PS: cám ơn dazkangel và anh Leo nhiều đã hỗ trợ TTBB giải đươc bài này hihi

 

[mickey]

Mình gửi bạn 1 cách giảm bớt cột phụ dựa trên CT của @dazkangel (sheet Evaluationsheet C2)
Mỗi khi bạn thêm sheet vào thì tự N:N sẽ update (bạn cần kéo CT lại thì nó mới hiện ra, kiểu như refersh ấy dù trước đó đã để sẵn CT dòng đó)
Và mình đang đếm mảng cho indirect theo độ dài của N:N mà chỉ count "Claim no.*", nghĩa là nếu bạn insert thêm sheet đúng tên"Claim no." và số đơn liên tiếp (consecutive number) thì mới ra đúng kết quả. (vd: bạn copy claim no.2 thành claim no.2 (1) thì nó ko gắn sheet 2 (1) này vào CT, hoặc claim no. 7 mà trước đó ko có 6 thì cũng ko ra).

Đó là hạn chế hen.
Nên mình vẫn thấy cách 1 của mình là tối ưu nhất, vì bạn gắn sheet tên gì vào cũng ko sai. Nếu bạn thấy xấu report thì đem qua 1 sheet phụ rồi giấu đi.

PS: cám ơn dazkangel và anh Leo nhiều đã hỗ trợ TTBB giải đươc bài này hihi

Dear các anh chị tieuthubuongbinh, dazkangel, Leo,

Em cảm ơn các anh chị rất nhiều. Phải nói là các anh chị hỗ trợ có tâm nhất quả đất. Đặc biệt là tieuthubuongbinh, thực sự số claim không liên tục ̣(vì không phải claim nào mình cũng dùng vendor) và số claim không phải là chỉ có số tự nhiên mà kèm theo các mẫu tự chữ cái đứng đầu. Xin lỗi, vì mình đã không nói rõ ngay từ đầu nhưng các anh chị cũng hỗ trợ được luôn bước này.

Mình ở Bình Thạnh nếu các anh chị có ghé ngang qua đây xin pm qua messenger fb (cùng địa chỉ email) để Mickey có dịp hậu tạ bằng chầu cafe thân mật.

Một lần nữa xin cảm ơn các cao nhân và diễn đàn bổ ích giaiphapexcel nhé.

 

[be_09]

..................
Mình ở Bình Thạnh nếu các anh chị có ghé ngang qua đây xin pm qua messenger fb (cùng địa chỉ email) để Mickey có dịp hậu tạ bằng chầu cafe thân mật.
...........................

- tieuthubuongbinh (ở quận 5).
- Còn chàng dazkangel (ở Bình Dương sao dưới Nick lại ghi Đồng Nai ta?).

 

[dazkangel]

Mình gửi bạn 1 cách giảm bớt cột phụ dựa trên CT của @dazkangel (sheet Evaluationsheet C2)
Mỗi khi bạn thêm sheet vào thì tự N:N sẽ update (bạn cần kéo CT lại thì nó mới hiện ra, kiểu như refersh ấy dù trước đó đã để sẵn CT dòng đó)
Và mình đang đếm mảng cho indirect theo độ dài của N:N mà chỉ count "Claim no.*", nghĩa là nếu bạn insert thêm sheet đúng tên"Claim no." và số đơn liên tiếp (consecutive number) thì mới ra đúng kết quả. (vd: bạn copy claim no.2 thành claim no.2 (1) thì nó ko gắn sheet 2 (1) này vào CT, hoặc claim no. 7 mà trước đó ko có 6 thì cũng ko ra).

Đó là hạn chế hen.
Nên mình vẫn thấy cách 1 của mình là tối ưu nhất, vì bạn gắn sheet tên gì vào cũng ko sai. Nếu bạn thấy xấu report thì đem qua 1 sheet phụ rồi giấu đi.

PS: cám ơn dazkangel và anh Leo nhiều đã hỗ trợ TTBB giải đươc bài này hihi

Bạn nên để công thức như sau để khỏi dùng cột phụ, vì bản thân name: sheetnames đã là 1 mảng đủ dữ liệu rồi:

=IFERROR(SUM(SUMIF(INDIRECT("'"&sheetnames&"'!B9:B16"),C$8,INDIRECT("'"&sheetnames&"'!E9:E16"))*COUNTIF($B12,T(INDIRECT("'"&sheetnames&"'!B2")))/SUM(COUNTIF(INDIRECT("'"&sheetnames&"'!B2"),$B12))),"")

- tieuthubuongbinh (ở quận 5).
- Còn chàng dazkangel (ở Bình Dương sao dưới Nick lại ghi Đồng Nai ta?).

Em ở Đồng Nai anh ơi

 

[mickey]

Bạn nên để công thức như sau để khỏi dùng cột phụ, vì bản thân name: sheetnames đã là 1 mảng đủ dữ liệu rồi:

=IFERROR(SUM(SUMIF(INDIRECT("'"&sheetnames&"'!B9:B16"),C$8,INDIRECT("'"&sheetnames&"'!E9:E16"))*COUNTIF($B12,T(INDIRECT("'"&sheetnames&"'!B2")))/SUM(COUNTIF(INDIRECT("'"&sheetnames&"'!B2"),$B12))),"")

Em ở Đồng Nai anh ơi

Cao thủ quá anh @dazkangel . Anh cho hỏi thêm với cách làm cũ, có cách nào công thức tự nhảy mà không cần phải refresh hoặc nhấn enter tại dòng công thức thì mới hiện kết quả không.

Cảm ơn anh.

 

[tieuthubuongbinh]

- tieuthubuongbinh (ở quận 5).
- Còn chàng dazkangel (ở Bình Dương sao dưới Nick lại ghi Đồng Nai ta?).

còn ở Q10 chú Bé ơi.

Em ở Đồng Nai anh ơi

Vậy TTBB sẽ phải gọi anh hay chú dazkangel bây giờ? hihi
hic... có chiêu bỏ sheetnames vô CT mà ko dạy TTBB, buồn bác quá. Mà thôi ko sao, TTBB chuyên học lóm haha

Anh cho hỏi thêm với cách làm cũ, có cách nào công thức tự nhảy mà không cần phải refresh hoặc nhấn enter tại dòng công thức thì mới hiện kết quả không.

Cảm ơn anh.

với CT mới nhất của bác Dazkangel thì sẽ tự nhảy và ko cần cột phụ đó bạn. Bạn dùng theo đi nhé.
Mình gửi file đã thử các thể loại tên đều ổn, vậy là xong rồi đó

 

[dazkangel]

còn ở Q10 chú Bé ơi.



Vậy TTBB sẽ phải gọi anh hay chú dazkangel bây giờ? hihi
hic... có chiêu bỏ sheetnames vô CT mà ko dạy TTBB, buồn bác quá. Mà thôi ko sao, TTBB chuyên học lóm haha



với CT mới nhất của bác Dazkangel thì sẽ tự nhảy và ko cần cột phụ đó bạn. Bạn dùng theo đi nhé.
Mình gửi file đã thử các thể loại tên đều ổn, vậy là xong rồi đó

Cái sheetnames mình không để ý, đến khi chủ bài kêu tên sheet không xác định được nên mới vào xem công thức đó và áp dụng vào công thức thôi bạn
Cứ xưng mình bạn là được, nhà anh Be09 gần nhà mình haha

 

[mickey]

còn ở Q10 chú Bé ơi.



Vậy TTBB sẽ phải gọi anh hay chú dazkangel bây giờ? hihi
hic... có chiêu bỏ sheetnames vô CT mà ko dạy TTBB, buồn bác quá. Mà thôi ko sao, TTBB chuyên học lóm haha



với CT mới nhất của bác Dazkangel thì sẽ tự nhảy và ko cần cột phụ đó bạn. Bạn dùng theo đi nhé.
Mình gửi file đã thử các thể loại tên đều ổn, vậy là xong rồi đó

Chị TTBB ơi, nảy thử em thấy ok rồi ( thanks anh @dazkangel, chị TTBB, anh Leo) nhưng tại lâu lâu em lại nhận được file excel mà công thức phải enter mới ra kết quả nên sẳn em hỏi luôn. Trước đây em có tìm hiểu thì được biết là chỉnh trong option của excel nhg trường hợp ví dụ của em mà anh Dazkagel giải có thể là 1 ngoại lệ nên em mới hỏi để biết nguyên nhân.

Em cảm ơn các anh chị và diễn đàn đã hỗ trợ giúp đỡ ngay cả những ngày cuối tuần nhóe.

 

[tieuthubuongbinh]

Chị TTBT ơi, nảy thử em thấy ok rồi ( Cảm ơn anh @dazkangel, chị TTBT, anh Leo) nhưng tại lâu lâu em lại nhận được file excel mà công thức phải enter mới ra kết quả nên sẳn em hỏi luôn. Trước đây em có tìm hiểu thì được biết là chỉnh trong option của excel nhg trường hợp ví dụ của em mà anh Dazkagel giải có thể là 1 ngoại lệ nên em mới hỏi để biết nguyên nhân.

Em cảm ơn các anh chị và diễn đàn đã hỗ trợ giúp đỡ ngay cả những ngày cuối tuần nhóe.

cái sheetnames nó hiện ngay tên nhưng khi mình copy sheet khác rồi mới đổi tên thì nó lại ko cập nhật tên mới được (vd: sheet 1, copy thành 1(1), thì khi đổi 2, nó cứ hiện 1(1), phải enter lại thì nó đúng, mình nghĩ là do hạn chế của macro 4. , vì dù sao cũng là macro nên nó cần bấm nút lại để refresh (đoán mò vậy thôi).

Còn việc file excel mà mỗi lần phải enter mới ra kết quả mới có thể do file đã tắt chế độ auto calculation và chuyển thành manual (giúp file bớt tự tính toán cho nhẹ file) nên bạn vô mục tô vàng, đổi chữ manual thành auto là xong.