Nhờ giải thích các công thức phức tạp

[duongdv]

Nhờ các bác giải thích giúp ý nghĩa của việc đặt các name trong bài viết của ndu96081631 về phương pháp trích lọi duy nhất

DS =OFFSET($B$2,,,COUNTA($B$2:$B$1000),)
DK_1 =IF(MATCH(DS,DS,0)=ROW($1:$1000),ROW($1:$1000),"")
DK =IF(ISNA(DK_1),"",DK_1)
Các name này hoạt động như thế nào trong công thức

=IF(ROWS($1:1)>COUNT(DK),"",LOOKUP(SMALL(DK,ROWS($1:1)),DK,DS))
Các bạn giải thích giúp mình

 

[BNTT]

DS =OFFSET($B$2,,,COUNTA($B$2:$B$1000),)
Tạo ra một mảng bắt đầu từ ô B2, có một cột, còn số hàng thì bằng với tổng số ô chứa dữ liệu trong dãy B2:B1000

DK_1 =IF(MATCH(DS,DS,0)=ROW($1:$1000),ROW($1:$1000),"")
Lấy từng vị trí xuất hiện lần đầu tiên của mỗi phần tử trong mảng DS đi dò lần lượt với các số từ 1 đến 1000 (là các con số do hàm ROW trả về): lấy vị trí xuất hiện đầu tiên của phần tử thứ nhất trong mảng DS dò với số 1, lấy vị trí xuất hiện đầu tiên của phần tử thứ hai trong mảng DS dò với số 2, lấy vị trí xuất hiện đầu tiên trong mảng DS của phần tử thứ ba dò với số 3, và cứ thể... so sánh từng cặp một. Nếu cặp nào trùng nhau, thì lấy con số đang dùng để dò với vị trí vị trí xuất hiện đầu tiên của phần tử này ra, còn nếu chúng không trùng nhau, thì bỏ qua. Kết quả cuối cùng là ta có một mảng gồm có 1 cột và 1000 hàng, trong mảng này sẽ rải rác có những phần tử có giá trị rỗng và những giá trị có giá trị #NA, do số phần tử trong mảng DS có thể nhỏ hơn 1000.

DK =IF(ISNA(DK_1),"",DK_1)
Như đã nói ở trên, mảng DK_1 có thể có những phần tử có giá trị là #NA, là những giá trị không dùng để tính toán được, nên ta phải tạo thêm một mảng DK từ mảng DK_1: Mảng DK là một mảng có kích thước y như mảng DK_1 (1 cột, 1000 hàng), nhưng nếu một phần tử nào đó trong DK_1 có giá trị là #NA thì nó sẽ được gán cho một giá trị rỗng tương ứng trong mảng DK.

=IF(ROWS($1:1)>COUNT(DK),"",LOOKUP(SMALL(DK,ROWS($1:1)),DK,DS))
Nếu tổng số phần tử có giá trị khác rỗng trong mảng DK nhỏ hơn tổng số hàng tính từ hàng số 1 đến hàng chứa ô đang có công thức này, thì trả về giá trị rỗng, còn ngược lại, thì trả về phần tử trong mảng DS tại vị trí bằng với vị trí của phần tử nhỏ thứ k (với k là tổng số hàng tính từ hàng thứ 1 đến hàng chứa ô đang có công thức này) trong mảng DK.

 

[anhtuan1066]

DK_1 =IF(MATCH(DS,DS,0)=ROW($1:$1000),ROW($1:$1000),"")
Lấy từng vị trí xuất hiện lần đầu tiên của mỗi phần tử trong mảng DS đi dò lần lượt với các số từ 1 đến 1000 (là các con số do hàm ROW trả về): lấy vị trí xuất hiện đầu tiên của phần tử thứ nhất trong mảng DS dò với số 1, lấy vị trí xuất hiện đầu tiên của phần tử thứ hai trong mảng DS dò với số 2, lấy vị trí xuất hiện đầu tiên trong mảng DS của phần tử thứ ba dò với số 3, và cứ thể... so sánh từng cặp một. Nếu cặp nào trùng nhau, thì lấy con số đang dùng để dò với vị trí vị trí xuất hiện đầu tiên của phần tử này ra, còn nếu chúng không trùng nhau, thì bỏ qua. Kết quả cuối cùng là ta có một mảng gồm có 1 cột và 1000 hàng, trong mảng này sẽ rải rác có những phần tử có giá trị rỗng và những giá trị có giá trị #NA, do số phần tử trong mảng DS có thể nhỏ hơn 1000.

Chổ này giãi thích đơn giản là:
Nếu vị trí của 1 phần tử trong dảy đúng bằng kết quả MATCH của nó trong dảy thì đánh STT cho nó (số thứ tự đánh từ 1 đến hết dảy)... Bằng ngược lại thì ko làm gì cả (cho = rổng luôn)
Ví dụ: ở vị trí thứ 5 trong dảy có tên Nguyễn Văn 1, và vị trí thứ 9 cũng có tên Nguyên Văn 1... Vậy hàm MATCH("Nguyễn Văn 1,Dảy,0) ở cả 2 vị trí thứ 5 và 9 đều cho kết quả = 5 ===> Lấy em ở vị trí thứ 5 (bỏ em ở vị trí thứ 9)

 

[BNTT]

Chổ này giãi thích đơn giản là:
Nếu vị trí của 1 phần tử trong dảy đúng bằng kết quả MATCH của nó trong dảy thì đánh STT cho nó (số thứ tự đánh từ 1 đến hết dảy)... Bằng ngược lại thì ko làm gì cả (cho = rổng luôn)
Ví dụ: ở vị trí thứ 5 trong dảy có tên Nguyễn Văn 1, và vị trí thứ 9 cũng có tên Nguyên Văn 1... Vậy hàm MATCH("Nguyễn Văn 1,Dảy,0) ở cả 2 vị trí thứ 5 và 9 đều cho kết quả = 5 ===> Lấy em ở vị trí thứ 5 (bỏ em ở vị trí thứ 9)

Em sợ bạn ấy cũng chưa hiểu MATCH là gì... Nên mới giải thích vòng vo dài dòng như vậy. Cái "xuất hiện lần đầu tiên" là giải nghĩa cho cái tham số 0 ở trong hàm MATCH.
Phù.. nói thiệt, giải thích một công thức mệt gấp chục lần tạo ra một công thức ! Vì phải ráng mà nói sao cho người ta hiểu, chứ cho mình hiểu thì...

 

[dungli]

giải thích dùm công thức

Xin nhờ các bác giải thích giúp em hiểu rõ công thức này. Xin cám ơn trước !
tim=INDIRECT("B"&LARGE((LEFT(SHEET1!$C$3,1)=MID(SHEET1!$B$3:$B$19,3,1))*ROW(SHEET1!$B$3:$B$19),ROW(SHEET1!1:1)))

 

[hoangdanh282vn]

Xin nhờ các bác giải thích giúp em hiểu rõ công thức này. Xin cám ơn trước !
tim=INDIRECT("B"&LARGE((LEFT(SHEET1!$C$3,1)=MID(SHEET1!$B$3:$B$19,3,1))*ROW(SHEET1!$B$3:$B$19),ROW(SHEET1!1:1)))

Cái này thấy quen quen.

LEFT(SHEET1!$C$3,1) : Tách lấy ký tự đầu tiên trong chuỗi ở ô C3

MID(SHEET1!$B$3:$B$19,3,1) : Tách lấy ký tự thứ 3 của các chuỗi trong vùng B3:B19 -> tạo ra mảng 1 chiều là các ký tự thứ 3 của các chuỗi tương ứng.

LEFT(SHEET1!$C$3,1)=MID(SHEET1!$B$3:$B$19,3,1) : so sánh mảng vừa tìm được với ký tự thứ 3 trong chuỗi ở ô C3 -> tạo ra mảng TRUE FALE 1 chiều.

ROW(SHEET1!$B$3:$B$19) : lấy số chỉ dòng của các ô từ B3:B19

((LEFT(SHEET1!$C$3,1)=MID(SHEET1!$B$3:$B$19,3,1))*ROW(SHEET1!$B$3:$B$19) : tạo ra mảng số 1 chiều , bao gồm phần tử 0 ứng với FALSE, là số chỉ dòng ứng với phần tử TRUE trong mảngTRUE FALSE.

ROW(SHEET1!1:1) : tạo ra một số ứng với số chỉ dòng trong tham chiếu : 1:1->1, 2:2->2…..

LARGE((LEFT(SHEET1!$C$3,1)=MID(SHEET1!$B$3:$B$19,3,1))*ROW(SHEET1!$B$3:$B$19),ROW(SHEET1!1:1)) : tìm phần tử lớn thứ ROW(SHEET1!1:1) trong mảng số vừa tìm được ở trên.

INDIRECT("B"&LARGE((LEFT(SHEET1!$C$3,1)=MID(SHEET1!$B$3:$B$19,3,1))*ROW(SHEET1!$B$3:$B$19),ROW(SHEET1!1:1))) : trả về giá trị trong ô "B lớn thứ tìm được".