Những công thức độc đáo (1 người xem)

[anhtuan1066]

Tình cờ tôi thu thập dc 1 số công thức có thể nói là độc đáo... Xin post lên đễ chia sẽ với mọi người:
1> Công thức lấy giá trị dạng Number nằm ở vị trí cuối cùng trong 1 cột
Giã sử cột A chứa dử liệu vừa số vừa chuổi, với công thức dưới đây ta sẽ lấy dc số nằm ở vị trị cuối cùng trong cột A này:

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))

2> Công thức lấy giá trị dạng Text nằm ở vị trí cuối cùng trong 1 cột:
Tương tự công thức trên, nhưng là lấy giá trị Text

=INDEX(A:A,MATCH(REPT("Z",255),A:A))

3> Công thức lấy giá trị nằm ở vị trí cuối cùng trong cột, bất kỳ dàng nào:

=LOOKUP(2,1/(1-ISBLANK(A1:A65535)),A1:A65535)

-----
Chú ý: công thức 1 sẽ cho kết quã #NA nếu cột A ko có số nào
Mời các cao thủ nếu có thu thập dc món nào tuyệt chiêu thì post lên ũng hộ topic này nhé!
Mến
ANH TUẤN

 

[anhtuan1066]

Thêm 1 công thức khá độc đáo nữa đây:
LẤY WEEKNUM cũa 1 cell ko cần dùng hàm WEEKNUM

=INT((A1-SUM(MOD(DATE(YEAR(A1-MOD(A1-2,7)+3),1,2),{1E+99,7})*{1,-1})+5)/7)

Mến
ANH TUẤN

 

[anhtuan1066]

Xếp hạng ko nhảy bậc (Ranking)

Đây là cách xếp hạng dùng RANK nhưng theo kiểu mới... (Hình như là cũa Soibien pháp hiện ra thì phải)

=SUM(1/(IF(RankRng<B2,COUNTIF(RankRng,RankRng),9.999999999E+307)))+1

Xem file đễ biết cách xếp hạng cụ thể
ANH TUẤN

 

[salam]

Em thêm một vài cách nữa.

Lấy dòng cuối có ký tự dạng text:

=MATCH("*";$A:$A;-1)

Lấy dòng đầu có ký tự dạng text:

=MATCH("*";$A:$A;0)

Lấy dòng cuối có ký tự dạng số:

=MATCH(1E+306;$A:$A)

 

[ndu96081631]

Công thức cuối cùng không chính xác vì 1E+306 chưa phải là số lớn nhất
Số lớn nhất chính là 9.99999999999999E+307
Thí nghiệm: Bạn tạo số trong cột A, gõ công thức vào cột B, ở dòng cuối cùng có dử liệu của cột A bạn gõ vào số: 9.99999999999999E+307 và xem kết quả của công thức sẽ biết liền

 

[BNTT]

Dành cho các bạn có tôn giáo là Công Giáo hoặc Tin Lành:

CÔNG THỨC TÍNH NGÀY LỄ PHỤC SINH:

= FLOOR(DAY(MINUTE(A1 / 38) / 2 + 56) & "/5/" & A1, 7) - 34​

Với A1 là năm cần tính. Ví dụ năm nay thì là 2008, và công thức trên cho ra kết quả là: 23/3/2008 (chính xác)
Nói thêm: Công thức này chỉ đúng trong khoảng thời gian từ 1900 đến 2078, và định dạng ngày tháng của ô chứa kết quả là dd/mm/yyyy


Bổ sung thêm: Dùng thực tế để chứng minh công thức trên là đúng:

Cách tính ngày lễ Phục Sinh của người Công giáo và Tin lành là như sau:

Đó là một ngày Chủ Nhật đầu tiên sau một ngày Rằm (ngày 14 âm lịch), và là ngày Rằm đầu tiên sau ngày Xuân Phân (19, 20 hoặc 21/3, tùy năm)​

Ví dụ năm nay: 2008
- Ngày xuân phân năm nay là ngày 20/3/2008, là thứ Năm, hôm đó theo âm lịch là ngày 13/2 năm Mậu Tý
- Ngày rằm (14/2 Mậu Tý) đầu tiên sau ngày 20/3 rơi vào thứ Sáu, 21/3/2008
Do đó, ngày Chủ Nhật đầu tiên sau một cái rằm đầu tiên sau ngày xuân phân, là Chủ Nhật 23/3/2008.

Nói như vậy, thì đây là chuyện Excel, chứ chẳng còn mang tính tôn giáo nữa, do đó, mời tất cả các bạn giải quyết tiếp dùm bài này: http://www.giaiphapexcel.com/forum/showthread.php?t=7548 (đang còn bỏ dở dang, chưa có đáp án...)

 

[Mainho]

Mình đã thử công thức trong file trên nhưng không áp dụng vào công việc của mình được. Mình upload file, nhờ các anh/ chị hướng dẫn tiếp nhé. Yêu cầu là phải xếp thứ hạng (tính cả đồng hạng) cho các tổ.
Cám ơn các anh/ chị

 

[Mainho]

Mình đã thử công thức trong file trên nhưng không áp dụng vào công việc của mình được. Mình upload file, nhờ các anh/ chị hướng dẫn tiếp nhé. Yêu cầu là phải xếp thứ hạng (tính cả đồng hạng) cho các tổ.
Cám ơn các anh/ chị

Không có ai giúp mình thế nhỉ

 

[TranThanhPhong]

Mình đã thử công thức trong file trên nhưng không áp dụng vào công việc của mình được. Mình upload file, nhờ các anh/ chị hướng dẫn tiếp nhé. Yêu cầu là phải xếp thứ hạng (tính cả đồng hạng) cho các tổ.
Cám ơn các anh/ chị

Trường hợp này bạn áp dụng như bài #3 của chủ đề này là làm được.

TTP

 

[ndu96081631]

Mình đã thử công thức trong file trên nhưng không áp dụng vào công việc của mình được. Mình upload file, nhờ các anh/ chị hướng dẫn tiếp nhé. Yêu cầu là phải xếp thứ hạng (tính cả đồng hạng) cho các tổ.
Cám ơn các anh/ chị

Chẳng hiểu tại sao bạn lại không áp dụng được!
Làm cho bạn đây!

 

[Mainho]

Chẳng hiểu tại sao bạn lại không áp dụng được!
Làm cho bạn đây!

Cám ơn bạn nhiều lắm, nhưng sao sau khi mình thêm hàm if vào thì công thức lại cho ra kết quả N/A vậy? Lọ mọ tìm lý do nãy giờ vẫ chưa ra
Và trong trường hợp mình muốn xếp thứ hạng từ thấp đến cao chứ không phải từ cao xuống thấp như trong file thì phải làm ntn?

 

[ndu96081631]

Cám ơn bạn nhiều lắm, nhưng sao sau khi mình thêm hàm if vào thì công thức lại cho ra kết quả N/A vậy? Lọ mọ tìm lý do nãy giờ vẫ chưa ra
Và trong trường hợp mình muốn xếp thứ hạng từ thấp đến cao chứ không phải từ cao xuống thấp như trong file thì phải làm ntn?

Công thức trong file là công thức mãng ---> Nếu bạn chỉnh sửa lại, nhớ Ctrl + Shift + Enter nha (Nếu chỉ Enter là không xong đâu)
Còn trục trặc thêm gì khác, bạn đưa file lên đi
--------------
Nói thêm: Trường hợp bạn muốn xếp hạng ngược lại, hãy thay hàm LARGE thành SMALL nhé

 

[nguoi vo hinh]

Chào các anh chị.
Các anh chị cho em hỏi với :
=SUM(1/(IF(RankRng<B2,COUNTIF(RankRng,RankRng),9.999999999E+307)))+1Các anh chị có thể nói cho em biết về các ý nghĩa của các hàm trong công thức này không ah?
Nhất là chỗ RankRng<B2 nó có tác dụng làm gì ?
Kêt quả trả về sẽ là cái gì ? Em suy nghĩ hoài mà vẫn không hiểu cái công thức này. Em xin cám ơn các anh chị! Chúc các anh chị cuối tuần vui vẻ.

 

[ndu96081631]

Chào các anh chị.
Các anh chị cho em hỏi với :
=SUM(1/(IF(RankRng<B2,COUNTIF(RankRng,RankRng),9.999999999E+307)))+1Các anh chị có thể nói cho em biết về các ý nghĩa của các hàm trong công thức này không ah?
Nhất là chỗ RankRng<B2 nó có tác dụng làm gì ?
Kêt quả trả về sẽ là cái gì ? Em suy nghĩ hoài mà vẫn không hiểu cái công thức này. Em xin cám ơn các anh chị! Chúc các anh chị cuối tuần vui vẻ.

Mở file của tác giả lên, Bấm Ctrl + F3 (hoặc vào menu Insert\Name\Define) sẽ biết RankRng là cái giống gì

 

[nguoi vo hinh]

Mở file của tác giả lên, Bấm Ctrl + F3 (hoặc vào menu Insert\Name\Define) sẽ biết RankRng là cái giống gì

Em làm kết quả ra rồi ah. Nhưng ý em muốn hỏi là tại sao lại so sánh RankRng<B2 (so sánh một mảng với một số). Dù sao cũng cám ơn anh nhiều .

 

[huuthang_bd]

Em làm kết quả ra rồi ah. Nhưng ý em muốn hỏi là tại sao lại so sánh RankRng<B2 (so sánh một mảng với một số). Dù sao cũng cám ơn anh nhiều .

Mảng RankRng so sánh với số B2 sẽ cho ra kết quả là một mảng. Các giá trị trong mảng kết quả được tính bằng cách lấy từng phần tử trong mảng RankRng so sánh với B2. Kết quả trả về là mảng có dạng {True,True,False,True,...}

 

[phihndhsp]

=LOOKUP(2,1/(1-ISBLANK($D7:$IV7)),$D7:$IV7)
gởi anh ndu, em vẫn không hiểu rõ về công thức trên lắm mong anh giải đáp dùm
em thử hàm
ISBLANK($D7:$IV7) khi em kéo xuống tất cả là giá trị FALSE
1-ISBLANK($D7:$IV7) khi em kéo xuống tất cả là 1
1/(1-ISBLANK($D7:$IV7)) khi em kéo xuống tất cả là 1
như vậy kết quả là đúng theo kết quả là
khi em chạy
= LOOKUP(2,1/(1-{FALSE,FALSE,FALSE,TRUE, TRUE, TRUE,....}),$D7:$IV7)
= LOOKUP(2,1/(1-{1,1,1,0,0,0,0....}),$D7:$IV7)
= LOOKUP(2,1/({1,1,1,0,0,0,0....}),$D7:$IV7)
= LOOKUP(2,1/({1,1,1,#DIV/0!,#DIV/0!,}),$D7:$IV7)
Và kết quả cuối cùng ra giá trị cuối cùng của ô cuối cùng

Mong anh giải thích dùm, xin cảm ơn

 

[vodoi2x]

=LOOKUP(2,1/(1-ISBLANK($D7:$IV7)),$D7:$IV7)
gởi anh ndu, em vẫn không hiểu rõ về công thức trên lắm mong anh giải đáp dùm
em thử hàm
= LOOKUP(2,1/({1,1,1,#DIV/0!,#DIV/0!,}),$D7:$IV7)
Và kết quả cuối cùng ra giá trị cuối cùng của ô cuối cùng

Mong anh giải thích dùm, xin cảm ơn

Trong khi chờ đợi anh ndu,
giải thích dùm vậy
bạn xem trong help của hàm lookup, sẽ thấy

If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value.

mà 1/x luôn <2 là cái chắc

 

[bebo021999]

=LOOKUP(2,1/(1-ISBLANK($D7:$IV7)),$D7:$IV7)
gởi anh ndu, em vẫn không hiểu rõ về công thức trên lắm mong anh giải đáp dùm
em thử hàm
ISBLANK($D7:$IV7) khi em kéo xuống tất cả là giá trị FALSE
1-ISBLANK($D7:$IV7) khi em kéo xuống tất cả là 1
1/(1-ISBLANK($D7:$IV7)) khi em kéo xuống tất cả là 1
như vậy kết quả là đúng theo kết quả là
khi em chạy
= LOOKUP(2,1/(1-{FALSE,FALSE,FALSE,TRUE, TRUE, TRUE,....}),$D7:$IV7)
= LOOKUP(2,1/(1-{1,1,1,0,0,0,0....}),$D7:$IV7)
= LOOKUP(2,1/({1,1,1,0,0,0,0....}),$D7:$IV7)
= LOOKUP(2,1/({1,1,1,#DIV/0!,#DIV/0!,}),$D7:$IV7)
Và kết quả cuối cùng ra giá trị cuối cùng của ô cuối cùng

Mong anh giải thích dùm, xin cảm ơn

Trong khi chờ anhtuan1066, mình muốn thảo luận 1 chút: bạn nói kéo xuống là sao, khi mảng của bạn là mảng ($D7:$IV7) nằm ngang?

Theo mình hiểu, các bước của bài toán này là,

• Bước 1: Quy các giá trị không rỗng trong vùng về giá trị 1

VD: Bạn có vùng A1: A6 chứa {"a","b"," ","c",8," ")

ISBLANK(A1:A6) = {FALSE;FALSE;TRUE;FALSE;FALSE;TRUE} = {0;0;1;0;0;1}
​

Quy về giá trị 1 :

1-ISBLANK(A1:A6) = {1;1;0;1;1;0}
​

Vô hiệu hóa giá trị 0 để dùng LOOKUP:

1/(1-ISBLANK(A1:A6)) = {1;1;#DIV/0!;1;1;#DIV/0!}

Ta đã tạo ra 1 vùng mới cùng kích thước, chứa những điều kiện để tham chiếu

​

• Bước 2: Sử dụng chức năng tìm gần đúng của LOOKUP:

So sánh 2 vùng:

{"a","b"," ", "c",8, " " }
{1;1;#DIV/0!;1;1;#DIV/0!}

​

Vị trí số 8 tương đương với giá trị 1 cuối cùng, làm sao để tham chiếu đến nó?
Ta biết trong LOOKUP, vécto điều kiện phải sort tăng dần, nếu điều kiện không thỏa nó sẽ tìm gần đúng từ dưới lên trên.

Do đó, công thức là

LOOKUP(2,{1;1;#DIV/0!;1;1;#DIV/0!},{"a","b"," ", "c",8, " " })
= LOOKUP(2,1/(1-ISBLANK(A1:A6)) ,A1:A6)

(Số 2 có thể thay bằng số bất kỳ >1)

(Lưu ý: Tất cả công thức, mảng, vùng ...ta có thể xem bằng cách bôi đen và nhấn F9.)

​

Chờ ý kiến thảo luận thêm của các ACE.
​

 

[nmhung49]

Nâng cấp lên mình tìm số cuối cùng trong vùng với công thức

=LOOKUP(1;1/--ISNUMBER(B1:H1);B1:H1)

Vậy mình thay thế vùng B1:H1 thành B1:H2 thì báo lỗi. Vậy tại sao như thể?? Mong các bạn chỉ giúp