Vì sao khi tăng lên giá trị lớn công thức MOD không còn đảm bảo (3 người xem)

[nguyenhongphong]

Trước đây bên em làm bảng tính top sản phẩm và top siêu thị.

Bạn huuthang_bd đã giải giúp em bài viết này

http://www.giaiphapexcel.com/forum/showthread.php?31003-C%C3%B3-th%E1%BB%83-t%E1%BA%A1o-name-c%E1%BB%A5c-b%E1%BB%99-cho-2-sheet&p=209407#post209407

Giờ cột lượng bán bên em chuyển qua đơn vị tính là ĐỒNG (không còn là THÙNG như lúc trước) có khi lên đến tiền tỷ. Lúc bấy giờ công thức tính top không còn đúng nữa. Thầy Cô và Anh Chị xem file đính kèm giúp em, mặc dù e đã chỉnh sửa công thức trong hàm mod nâng lên 10^2 hoặc 10^3 mà kết quả cũng không ra chính xác.

Xin mọi người giúp em. Em cảm ơn nhiều ạ.

 

[siwtom]

Trước đây bên em làm bảng tính top sản phẩm và top siêu thị.

Bạn huuthang_bdđã giải giúp em bài viết này

http://www.giaiphapexcel.com/forum/showthread.php?31003-C%C3%B3-th%E1%BB%83-t%E1%BA%A1o-name-c%E1%BB%A5c-b%E1%BB%99-cho-2-sheet&p=209407#post209407

Giờ cột lượng bán bên em chuyển qua đơn vị tính là ĐỒNG (không còn là THÙNG như lúc trước) có khi lên đến tiền tỷ. Lúc bấy giờ công thức tính top không còn đúng nữa. Thầy Cô và Anh Chị xem file đính kèm giúp em, mặc dù e đã chỉnh sửa công thức trong hàm mod nâng lên 10^2 hoặc 10^3 mà kết quả cũng không ra chính xác.

Xin mọi người giúp em. Em cảm ơn nhiều ạ.

Hàm MOD(number;divisor) sẽ trả về lỗi #NUM! trong trường hợp khi mà

divisor * 134217728 ≤ number

Trong trường hợp của bạn ta có divisor = 1, vậy lỗi khi 134 217 728 ≤ number

Khi bạn kinh doanh tiền tỷ thì rõ ràng đk trên thỏa nên có lỗi.

Cách khắc phục:
Thay MOD bằng

=number - divisor*INT(number/divisor)

------------------
Nếu để nguyên không sửa sheet Data thì có lẽ thế này:

Công thức cho B7

=INDEX(Data;ROUND((LARGE(OFFSET(Data;;9;;1);A7) - C7)*10^4;0);5)

Công thức cho F7

=INDEX(Data;ROUND((LARGE(OFFSET(Data;;10;;1);E7)-G7)*10^4;0);8)

Kéo xuống dưới

 

[nguyenhongphong]

Cảm ơn a siwtom. Phong đang kiểm tra lại, có vấn đề phát sinh nhờ anh hướng dẫn giúp Phong ạ.

 

[nguyenhongphong]

Thưa a siwtom ,các thầy và các bạn. E ứng dụng vào file em lại có lỗi #REF, trong file top.
E tìm được lý do như sau: phần thập phân sau khi nhân 10^(-4) nếu kết quả thập phân là 0.1 dò tên khách hàng thì báo lỗi REF. Còn kết quả thập phân 0.01 không báo lỗi #REF. Mong anh hướng dẫn giúp em. Do file dung lượng lớn nên e gởi qua link. A down giúp em nhé.

Rất cảm tất cả mọi người

 

[siwtom]

Thưa a siwtom ,các thầy và các bạn. E ứng dụng vào file em lại có lỗi #REF, trong file top.
E tìm được lý do như sau: phần thập phân sau khi nhân 10^(-4) nếu kết quả thập phân là 0.1 dò tên khách hàng thì báo lỗi REF. Còn kết quả thập phân 0.01 không báo lỗi #REF. Mong anh hướng dẫn giúp em. Do file dung lượng lớn nên e gởi qua link. A down giúp em nhé.

http://www.fshare.vn/file/KF2BRYK8JA/
http://www.fshare.vn/file/819GD47BCK/



Rất cảm tất cả mọi người

Trước hết tôi nói rõ là tôi chỉ sửa lại công thức ĐÃ ĐÚNG của bạn cho thích hợp với trường hợp dùng số lớn. Vì công thức do bạn huuthang_bd viết nên ắt phải đúng, và bạn cũng đã dùng. Vậy thì tôi tin tưởng và không kiểm tra lại.

Bây giờ gặp lỗi REF thì tôi xem lại công thức trong sheet Data. Công thức của bạn huuthang_bd đúng nhưng như bạn đã nói thì nó được dùng khi bạn có dữ liệu là THÙNG, tức dữ liệu nguyên. Bây giờ bạn bê công thức vào dữ liệu là ĐỒNG, tức dữ liệu không hẳn là nguyên nên công thức trong sheet Data không đúng nữa do vậy kết quả trong sheet TOP bị lỗi REF.

Tôi giải thích cho bạn hiểu công thức trong sheet Data

=IF(COUNTIF($C$5:C[COLOR=#ff0000]91[/COLOR];C[COLOR=#ff0000]91[/COLOR])=1;SUMPRODUCT(--(OFFSET(Data;;10;;1)=TOP!$B$1);--(OFFSET(Data;;9;;1)=TOP!$B$2)+(TOP!$B$2="");--(OFFSET(Data;;8;;1)=TOP!$B$3)+(TOP!$B$3="");--(OFFSET(Data;;2;;1)=C[COLOR=#ff0000]91[/COLOR]);OFFSET(Data;;6;;1))+(ROW()-ROW(Data)+1)*10^(-4);"")

Viết gọn lại thì là

=IF(COUNTIF($C$5:C[COLOR=#ff0000]91[/COLOR];C[COLOR=#ff0000]91[/COLOR])=1;xyz + (ROW()-ROW(Data)+1)*10^(-4);"")

Trong đó xyz là kết quả của SUMPRODUCT

1. Khi dữ liệu là THÙNG, là số nguyên thì xyz = SUMPRODUCT(...) là số nguyên, (ROW()-ROW(Data)+1) là chỉ số dòng trong mảng Data của dữ liệu đang xét. Dòng đang xét có chỉ số là 91 (đỏ đỏ) vậy (ROW()-ROW(Data)+1) = (ROW()-ROW($A$5:$AD$344)+1) = 91 - 5 + 1 = 87
=> (ROW()-ROW(Data)+1)*10^(-4) = 0,0087
=> xyz + (ROW()-ROW(Data)+1)*10^(-4) = xyz + 0,0087

Tóm lại chỉ số dòng của dữ liệu đang xét trong mảng (87) được ghi nhớ trong phần thập phân của ô L91 do xyz là số nguyên.

Sang bên sheet TOP thì phần thập phân 0,0087 được nhân với 10^4 để lấy lại chỉ số dòng 87 của dữ liệu trong mảng Data.

2. Bây giờ dữ liệu của bạn là ĐỒNG, tức không còn là số nguyên nữa. Với ô L91 như điểm 1 bạn có

SUMPRODUCT = xyz = 841167540,909091, (ROW()-ROW(Data)+1)*10^(-4) = 0,0087
=> SUMPRODUCT + (ROW()-ROW(Data)+1)*10^(-4) = xyz + 0,0087 = 841167540,917791
Sang bên sheet TOP khi lọc ra phần thập phân thì nó không là 0,0087 như ở điểm 1 mà nó là 0,917791
=>

B7 =INDEX(Data;ROUND((LARGE(OFFSET(Data;;11;;1);A7) - C7)*10^4;0);COLUMNS($A:C))
=INDEX(Data;ROUND((LARGE(OFFSET(Data;;11;;1);A7) - INT(LARGE(OFFSET(Data;;11;;1);A7)))*10^4;0);COLUMNS($A:C))
=INDEX($A$5:$AD$344;ROUND((841167540,917791 - 841167540)*10^4;0);3)
=INDEX($A$5:$AD$344;9178;3)
=> REF

Có lỗi do mảng của bạn chỉ có 340 dòng. Nếu bạn cho mảng "dư" ra tới vd. $A$5:$AD$10000 thì không còn lỗi nhưng kết quả sai. Vì kết quả của bạn phải nằm ở dòng 87 của mảng Data chứ không phải ở dòng 9178
------------
Kết luận: để dùng cho dữ liệu không là nguyên (ĐỒNG thay cho THÙNG) thì phải sửa lại công thức ở sheet Data.

Cách sửa mà vẫn giữ nguyên ý tưởng của bạn huuthang_bd, tức nhớ chỉ số dòng của dữ liệu trong phần thập phân, thì bạn phải cho biết số tiền ĐỒNG kia bạn định lấy tới mấy chữ số sau dấu phẩy. Tôi nghĩ là với tiền Việt Nam thì lấy tới 2 chữ số sau dấu phẩy. Tất nhiên trong ngân hàng người ta lấy nhiều chữ số hơn.

Giả sử bạn lấy tới 2 chữ số

Vậy thì công thức hiện giờ trong sheet Data

=IF(COUNTIF($C$5:C91;C91)=1;SUMPRODUCT(...) + (ROW()-ROW(Data)+1)*10^(-4);"")

Sửa thành

=IF(COUNTIF($C$5:C91;C91)=1;ROUND(SUMPRODUCT(...);2) + (ROW()-ROW(Data)+1)*10^(-6);"")

Tức công thức bây giờ cho L5 là

=IF(COUNTIF($C$5:C5;C5)=1;ROUND(SUMPRODUCT(--(OFFSET(Data;;10;;1)=TOP!$B$1);--(OFFSET(Data;;9;;1)=TOP!$B$2)+(TOP!$B$2="");--(OFFSET(Data;;8;;1)=TOP!$B$3)+(TOP!$B$3="");--(OFFSET(Data;;2;;1)=C5);OFFSET(Data;;6;;1));2)+(ROW()-ROW(Data)+1)*10^(-6);"")

Và trong sheet TOP công thức cho B6

=INDEX(Data;̣(LARGE(OFFSET(Data;;11;;1);A6) - C6)*10^6 MOD 10^4;COLUMNS($A:C))

---------------
Công thức trên chỉ đúng khi công thức cũ trong sheet Data là đúng. Tôi chỉ sửa trên nền tảng công thức cũ thôi.

Tốt nhất bạn nên nhờ bạn huuthang_bd sửa giúp công thức để dùng cho trường hợp dữ liệu không còn là số nguyên, tức ĐỒNG chứ không là THÙNG. Vì dù sao bạn huuthang_bd cũng đã nghiên cứu khi giúp bạn.

 

[nguyenhongphong]

Dạ, đúng ra e phải gọi anh là Thầy mới đúng ạ. Thầy hướng dẫn rất chi tiết cho e.

* 10^6 là để lấy lại chỉ số của dòng chứa thập phân ấy, nhưng công thức chỗ này e vẫn chưa hiểu vì sao có mod 10^4, vì sao trong mod không có divisor, vì sao giữa *10^6 và mod 10^4 không có dấu* vậy ạ?

=INDEX(Data;̣(LARGE(OFFSET(Data;;11;;1);A6) - C6)*10^6 MOD 10^4;COLUMNS($A:C))

Kiến thức excel của e còn rất hạn chế. Rất mong Thầy hướng dẫn thêm cho e.

Em rất cảm ơn Thầy và mọi người đã hướng dẫn e tận tình ạ.

 

[siwtom]

Dạ, đúng ra e phải gọi anh là Thầy mới đúng ạ. Thầy hướng dẫn rất chi tiết cho e.

* 10^6 là để lấy lại chỉ số của dòng chứa thập phân ấy, nhưng công thức chỗ này e vẫn chưa hiểu vì sao có mod 10^4, vì sao trong mod không có divisor, vì sao giữa *10^6 và mod 10^4 không có dấu* vậy ạ?

=INDEX(Data;̣(LARGE(OFFSET(Data;;11;;1);A6) - C6)*10^6 MOD 10^4;COLUMNS($A:C))

Kiến thức excel của e còn rất hạn chế. Rất mong Thầy hướng dẫn thêm cho e.

Em rất cảm ơn Thầy và mọi người đã hướng dẫn e tận tình ạ.

He he he. He he he. He he he.

Bạn mới phát hiện chỗ tôi nhầm. Còn vấn đề chưa nghĩ kỹ nữa.

1. Nhầm. Tôi lú lẫn nên viết theo kiểu trong VBA, tức a MOD b, còn trên sheet thì phải viết là MOD(a;b)

Vậy phải là

=INDEX(Data;MOD((LARGE(OFFSET(Data;;11;;1);A6) - C6)*10^6; 10^4);COLUMNS($A:C))

2. Chưa nghĩ kỹ.

Nếu SUMPRODUCT trả về vd. 2 tỷ thì xyz có 10 chữ số. Phần thập phân có 6 chữ số. Tổng cộng là 16 chữ số. Lúc đó chữ số cuối sẽ bị thay bằng 0 do Excel chỉ cho 15 chữ số. Vậy nếu dữ liệu là 2 000 000 000,25 và ở dòng 53 thì ta muốn có 2 000 000 000,250053 nhưng kết quả chỉ có 2 000 000 000,250050. Lúc này sang sheet TOP đọc ra ta sẽ có chỉ số dòng dữ liệu là 50 chứ không phải là 53, tức kết quả sẽ sai.

Vậy thì ta có cách giải quyết như sau:
A. Ta không lấy số tiền với 2 chữ số sau dấu phẩy mà làm tròn SUMPRODUCT thành số nguyên. Và chỉ số dòng sẽ nhớ trong phàn thập phân chỉ có 4 chữ số thay vì phần thập phân có 6 chữ số như bây giờ.

Tuy nhiên lúc này với số tiền là 200 tỷ thì xyz có 12 chữ số, và phần thập phân có 4 chữ số, tổng cộng 16 chữ số. Lại có trường hợp chữ số cuối bị thay bằng 0 dẫn tới kết quả sai như trên.

Vậy với cách giải quyết A thì số tiền lớn nhất chỉ cõ thể là 99 999 999 999 (11 chữ số), tức < 100 tỷ

Nếu số tiền có thể >= 100 tỷ thì phải dùng cách B

B. Ta không nhớ chỉ số dòng của dữ liệu ở phần thập phân nữa mà nhớ "ở đâu đó"

Bạn phải cân nhắc xem chọn cách nào thì mới có thể viết công thức mới.

 

[nguyenhongphong]

Kiến thức từ cơ bản đến nâng cao của các Thầy trên GPE thật uyên bác.

* Em thú thật.
1. E nhờ thầy mà e mới biết được thông tin "Excel chỉ cho 15 chữ số."
2. Thầy hướng dẫn để e hiểu được thông tin là lấy dòng 53 phần thập phân (,250053). Trước đây em cứ tưởng excel dò đến dòng có dãy số thập phân 250053.

Có 1 vấn đề xảy ra là khi áp dụng công thức vào. Thì kết quả trả về top không còn chính xác nữa ạ. Thầy xem link giúp em.

http://www.fshare.vn/file/5R282YXAJB/



Rất mong Thầy hướng dẫn thêm giúp em để e tường tận hơn.

* Thầy đã mở rộng thêm giới hạn cho bài toán lên đến 200 tỷ. Trước đây cũng từ lý suy nghĩ đơn giản của em trong ý 2 ở trên, mà e đã nghĩ là dùng dữ liệu ô chứa phần nguyên và thập phân để dò lại thông tin Top. Nhưng cũng không cho kết quả gì.

Đến bây giờ câu hỏi phần B của Thầy trong giới hạn hiểu biết của em. Em chỉ có thể nghĩ dùng hàm vlookup để dò dữ liệu phần nguyên và phần thập phân.

P/S: Lần đầu tiên e được 1 Thầy hướng dẫn em tận tình như Thầy. Các Thầy khác cũng rất tận tình, nhưng do khả năng excel của e còn kém quá nên chưa hiểu hết. Chúc Thầy và các Thầy khác luôn được nhiều sức khoẻ để chỉ dẫn, chia sẽ những kinh nghiệm, kiến thức vô cùng rộng lớn về excel mà em cũng như các bạn newbie còn rất hạn chế.

CẢM ƠN GPE.

 

[siwtom]

Kiến thức từ cơ bản đến nâng cao của các Thầy trên GPE thật uyên bác.

* Em thú thật.
1. E nhờ thầy mà e mới biết được thông tin "Excel chỉ cho 15 chữ số."
2. Thầy hướng dẫn để e hiểu được thông tin là lấy dòng 53 phần thập phân (,250053). Trước đây em cứ tưởng excel dò đến dòng có dãy số thập phân 250053.

Có 1 vấn đề xảy ra là khi áp dụng công thức vào. Thì kết quả trả về top không còn chính xác nữa ạ. Thầy xem link giúp em.

http://www.fshare.vn/file/5R282YXAJB/



Rất mong Thầy hướng dẫn thêm giúp em để e tường tận hơn.

* Thầy đã mở rộng thêm giới hạn cho bài toán lên đến 200 tỷ. Trước đây cũng từ lý suy nghĩ đơn giản của em trong ý 2 ở trên, mà e đã nghĩ là dùng dữ liệu ô chứa phần nguyên và thập phân để dò lại thông tin Top. Nhưng cũng không cho kết quả gì.

Đến bây giờ câu hỏi phần B của Thầy trong giới hạn hiểu biết của em. Em chỉ có thể nghĩ dùng hàm vlookup để dò dữ liệu phần nguyên và phần thập phân.

P/S: Lần đầu tiên e được 1 Thầy hướng dẫn em tận tình như Thầy. Các Thầy khác cũng rất tận tình, nhưng do khả năng excel của e còn kém quá nên chưa hiểu hết. Chúc Thầy và các Thầy khác luôn được nhiều sức khoẻ để chỉ dẫn, chia sẽ những kinh nghiệm, kiến thức vô cùng rộng lớn về excel mà em cũng như các bạn newbie còn rất hạn chế.

CẢM ƠN GPE.

Tôi muốn bạn chọn cách xử lý A (số tiền không bao giờ > 100 tỷ) hay B (số tiền có thể lớn > 100 tỷ).

Nhưng bạn viết nhiều mà cuối cùng cũng chả chọn gì cả.

Thôi đã thế thì tôi tự quyết định:

1. Ta chọn cách B.
2. Cột L của sheet Data chỉ chứa số tiền.
3. Chỉ số dòng của dữ liệu trong mảng Data (vd. 53) sẽ được nhớ trong 4 chữ số cuối của custPos - đọc tiếp
4. Ta làm cho tập tin có cấu trúc như ở tập tin SalesReportGPE.xlsx mà bạn gửi ở bài #4.

Thao tác
A.
Đặt name customer

=OFFSET(Data;;11;;1)

Đặt name custPos

=IFERROR(COUNTIF(customer;"<"&customer)*[COLOR=#ff0000]10^4[/COLOR]+ROW(INDIRECT("$1:$"&COUNTA(customer)));"")

Cũng có thể dùng hàm RANK. Phiên bản sử dụng hàm RANK

=IFERROR(RANK(customer;customer;1)*[COLOR=#ff0000]10^4[/COLOR]+ROW(INDIRECT("$1:$"&COUNTA(customer)));"")

B.

Cột L của sheet Data bây giờ chỉ là số tiền đơn thuần.

công thức cho L5

=IF(COUNTIF($C$5:C5;C5)=1;SUMPRODUCT(--(OFFSET(Data;;10;;1)=TOP!$B$1);--(OFFSET(Data;;9;;1)=TOP!$B$2)+(TOP!$B$2="");--(OFFSET(Data;;8;;1)=TOP!$B$3)+(TOP!$B$3="");--(OFFSET(Data;;2;;1)=C5);OFFSET(Data;;6;;1));"")

Công thức cho ô B6 bên sheet TOP

=INDEX(Data;MOD(LARGE(custPos;A6);[COLOR=#ff0000]10^4[/COLOR]);3)

Bên sheet TOP hiện thời tại ô C6 bạn có công thức

=INT(LARGE(OFFSET(Data;;11;;1);A6))

Đây chính xác là số tiền. Vậy chỉ cần

=LARGE(OFFSET(Data;;11;;1);A6)

hoặc 

=LARGE(customer;A6)

----------------
Trong tập tin SalesReportGPE.xlsx tôi không thấy bên sheet TOP có top 10 sản phẩm.
Nếu bạn muốn làm cả cho sản phẩm thì tôi đề nghị:
1.
Đặt name product

=OFFSET(Data;;12;;1)

Đặt name prodPos

=IFERROR(COUNTIF(product;"<"&product)*[COLOR=#ff0000]10^4[/COLOR]+ROW(INDIRECT("$1:$"&COUNTA(product)));"")

2. Trong công thức của M5 trong sheet Data xóa đoạn +(ROW()-ROW(Data)+1)*10^(-4)

3. Công thức cho ô đầu tiên bên sheet TOP cho sản phẩm bán nhiều nhất - tôi chọn G6, bạn tự sửa

=INDEX(Data;MOD(LARGE(prodPos;A6);[COLOR=#ff0000]10^4[/COLOR]);5)

----------
Với các công thức trên thì bảng dữ liệu (Data) không được phép có > 9999 dòng dữ liệu. Nếu có thể có > 9999 dòng dữ liệu thì thay tất cả các 10^4 thành 10^5.

Trong tập tin đính kèm tôi thiết lập Calculation thành Automatic. Bạn tự chỉnh lại nếu muốn.

Tập tin đính kèm
http://www.mediafire.com/download/lp878kqk8rs1o8k/SalesReportGPE.rar

 

[VetMini]

Xin lỗi, tự xoá bài vì đăng sai chỗ.

 

[nguyenhongphong]

Rất cảm ơn Thầy vì Thầy đã thức khuya để hướng dẫn giúp em bài này. Rất mong có dịp hậu tạ Thầy
Nếu được Thầy cho e xin HP của Thầy ạ. Số HP của em: 0944 12 66 44
Việc xoá đoạn:

+(ROW()-ROW(Data)+1)*10^(-4)

và add đoạn:

ROW(INDIRECT("$1:$"&COUNTA(product)))

Hai dòng này có ý nghĩa khác nhau như thế nào ạ?

Một lần nữa xin cảm ơn Thầy

 

[siwtom]

Rất cảm ơn Thầy vì Thầy đã thức khuya để hướng dẫn giúp em bài này. Rất mong có dịp hậu tạ Thầy

Việc xoá đoạn:

+(ROW()-ROW(Data)+1)*10^(-4)

và add đoạn:

ROW(INDIRECT("$1:$"&COUNTA(product)))

Hai dòng này có ý nghĩa khác nhau như thế nào ạ?

Một lần nữa xin cảm ơn Thầy

1. Về + (ROW()-ROW(Data)+1)*10^(-4)
Ở tập tin cũ thì các cột L và M không chỉ là tổng tiền đơn thuần, tức không chỉ là SUMPRODUCT. Ý tưởng của bạn huu_thang_bd là nhớ vào tổng THÙNG (bây giờ bạn dùng TIỀN thay THÙNG thì là tổng TIỀN) chỉ số dòng dữ liệu (ở phần thập phân). Bạn phân tích công thức thì thấy thôi.

Giả sử tổng THÙNG là xyz = SUMPRODUCT = 200, và dòng dữ liệu là dòng 51 của sheet Data
=> SUMPRODUCT(...) + (ROW()-ROW(Data)+1)*10^(-4) =
200 + (51 - ROW(A5:***344) + 1)*10^(-4) = 200 + (51 - 5 + 1)*10^(-4) = 200 + 47*10^(-4) = 200,0047
Tức dữ liệu ở dòng 51 của sheet Data sẽ ở dòng 47 của mảng Data. Chỉ số 47 sẽ được lấy lại trong công thức của sheet TOP.

Bây giờ do những phát sinh do bạn dùng TIỀN, tức số sẽ lớn không dùng được MOD, và số lớn cũng hạn chế dữ liệu phải < 100 tỷ, vậy tôi KHÔNG NHỚ chỉ số dòng trong cột L và M nữa. Tức trong L và M chỉ có số TIỀN đơn thuần. Chính vì thế mà phải xóa + (ROW()-ROW(Data)+1)*10^(-4)

2. Về ROW(INDIRECT("$1:$"&COUNTA(product)))
Chỉ số của dòng dữ liệu trong mảng Data không được nhớ trong phần thập phân của cột M nữa. Chỉ số đó bây giờ tôi nhớ trong 4 chữ số cuối của name prodPos

=IFERROR(COUNTIF(product;"<"&product)*10^4+ROW(INDIRECT("$1:$"&COUNTA(product)));"")

COUNTIF(product;"<"&product) trả về mảng mà ngoài các dòng trống thì tương ứng với dữ liệu trong mảng product thì trong mảng này là số các phần tử có trong mảng product mà nhỏ hơn nó. Vd. trong mảng product có 100 mặt hàng khác nhau mà mặt hàng lớn nhất ở dòng 20 của mảng product (cũng chính là dòng 20 của mảng Data) thì ở mảng COUNTIF(product;"<"&product) ở dòng 20 là con số 99 (có 99 mặt hàng "nhỏ hơn" mặt hàng ở dòng 20 của mảng product).
Chỉ số 20 ở trên được nhớ trong 4 chữ số cuối của prodPos vì:
COUNTIF(product;"<"&product)*10^4+ROW(INDIRECT("$1:$"&COUNTA(product))) =
COUNTIF(product;"<"&product)*10^4+ ROW(INDIRECT("$1:$"&344)) =
COUNTIF(product;"<"&product)*10^4+ ROW(INDIRECT("$1:$344")) =
COUNTIF(product;"<"&product)*10^4+ ROW($1:$344) =
99*10^4 + 20 (đang tính cho phần tử 20 của mảng prodPos) = 990020

Chỉ số được nhớ trong prodPos sẽ được lấy lại trong công thức ở sheet TOP

=INDEX(Data;MOD(LARGE(prodPos;A6);10^4);5)

Công thức ở trên là cho G6, tức sẽ tính ra cho mặt hàng "lớn nhất", tức mặt hàng ở dòng 20 của mảng product cũng chính là dòng 20 của mảng Data.

MOD(LARGE(prodPos;A6);10^4) =
MOD(LARGE(prodPos;1);10^4) =
MOD(990020;10^4) = 0020 = 20

=> INDEX(Data;MOD(LARGE(prodPos;A6);10^4);5) =
INDEX(Data;20;5) = tên mặt hàng "lớn nhất" (ở dòng 20 cột 5 của mảng Data)

Nếu được Thầy cho e xin HP của Thầy ạ. Số HP của em: 0944 12 66 44

http://www.giaiphapexcel.com/forum/...nhân-ngoài-giờ-với-tôi&p=499982#post499982

 

[nguyenhongphong]

Dạ vâng.

Thầy quả là người sâu sắc, tỉ mĩ và hiểu rõ trình độ của người hỏi. Thầy hướng dẫn đến chi tiết. Em thấy được hình ảnh của Thầy trong bài" Câu Chuyện Một Kiếm Sĩ) nói về ngài Banzo, ngài ấy chỉ dạy học trò tận tình như Thầy vậy.

Cảm ơn Thầy,
Cảm ơn các Thầy GPE

 

[nguyenhongphong]

Dạ thưa Thầy Siwtom,
Do file của bài này của e bị nhiễm virus, giờ em cần lại file này để tham khảo làm file khác tương tự nhưng không download được nữa - kể cả file của em trên Fshare (do hết hạn).
Thầy/anh/chị nào có lưu file này xin upload lại giúp em được không ạ.
Em chân thành cảm ơn ạ