Bài tập hàm Excel căn bản.

[Lê Duy Thương]

Không phức tạp, không có nghĩa gặp bài dạng trên không làm được, nhất là giờ có nhiều cách học, tìm tài liệu học

Thì như mình nói. càng ngày càng phát triển, excel cũng đã tiến hóa rất nhanh
Công thức của bài tập 6 câu 1 nếu là office 365 thì rất đơn giản với hàm =TOCOL($C$2:$G$16,3,FALSE) chỉ cần thay đổi cái tô đậm là xong.

 

[VetMini]

Dùng lookup 2 mảng gõ tay {1, 2, 3, 4} và {1, 5, 9, 13} mà gọi là tối ưu á? Rồi khi thêm số liệu thì làm gì?

Lookup không hẳn là hàm căn bản. MS ra nó để giải quyết một số trường hợp đặc thù.
Khi dạy về hàm này, điều quan trọng nhất là phải cho học sinh biết :
1. Nó là 1 trong 4 hàm mảng của Excel cổ điển.
2. Nó có khả năng bỏ qua trị lỗi (#error!). Vì tính chất này mà nó trở thành thuộc về nhóm hàm nguy hiểm.

Trong cách dùng thông thường (không kể mẹo) thì Lookup so với Index/Match thế nào?
- Bình thường, dùng Match hiệu quả hơn, và có thể dùng rộng rãi hơn. Bởi vì Match có thể dò chính xác. Lookup chỉ dò gần đúng, và vì thế mảng dò phải được sắp xếp.
- Nếu mảng dò hoặc mảng kết quả là một bài toán thì nên dùng Lookup hơn vì nó là hàm mảng chân chính. Nếu dùng Match thì phải CSE.

Ở công thức trên, đã lập mảng tra thì lập luôn mảng 2 chiều và dùng VLookup.
VLooup( trị dò, { 1, 1; 2, 5; 3, 9; 4, 13 }, 2, 1)
Thường thường bảng dò kiểu này người ta đặt mảng trong name.

Chỉ dùng Index, chỉ dùng If, Lookup, ... thì gọi là căn bản. Chỉ cần thêm chút tính toán số học.

Index hơi phức tạp đối với người không học qua tính chất mảng 1 chiều và mảng 2 chiều, 3 chiều.

 

[hichlehg]

Các hàm khác tôi nhớ hết, thậm chí là hàm offset rất khó nhớ nhưng nhiều năm tôi vẫn không quên. Chỉ có hàm lookup là tôi thua, vài tuần sau là chẳng nhớ gì hết. Đó chỉ thuần túy cá nhân thôi.

Hàm LOOKUP theo cấu trúc nó thì khó nhớ thật. Lúc trước em có ăn cắp của 1 bạn IT làm cho vợ với cấu trúc đơn giản, nên em chỉ sài như vậy cho đỡ đau đầu: =LOOKUP(2,1/(ĐK1=VÙNG ĐK1)/(ĐKn=VÙNG ĐKn),VÙNG LẤY KQ). 2,1 là gì em không biết mà cứ như vậy làm tới.

 

[TH2]

Hàm LOOKUP theo cấu trúc nó thì khó nhớ thật. Lúc trước em có ăn cắp của 1 bạn IT làm cho vợ với cấu trúc đơn giản, nên em chỉ sài như vậy cho đỡ đau đầu: =LOOKUP(2,1/(ĐK1=VÙNG ĐK1)/(ĐKn=VÙNG ĐKn),VÙNG LẤY KQ). 2,1 là gì em không biết mà cứ như vậy làm tới.

Muốn biết rõ thì đọc help của excel là hiểu, hoặc tìm kiếm các trang viết về nó, cùng với các lưu ý sử dụng
Còn không hiểu dùng đại, thì phải lưu ý là có thể rủi ro, hoặc chỉ đúng với các trường hợp cụ thể nào đó.
Và còn thây khó bỏ qua, thì chấp nhận rủi ro, niềm tin đặt vào người cho ta công thức với bài toán cụ thể khi đó mà thôi

 

[xuan.97]

Hàm LOOKUP theo cấu trúc nó thì khó nhớ thật. Lúc trước em có ăn cắp của 1 bạn IT làm cho vợ với cấu trúc đơn giản, nên em chỉ sài như vậy cho đỡ đau đầu: =LOOKUP(2,1/(ĐK1=VÙNG ĐK1)/(ĐKn=VÙNG ĐKn),VÙNG LẤY KQ). 2,1 là gì em không biết mà cứ như vậy làm tới.

Em cũng y chang bác luôn , cơ mà em chỉ dùng khi điều kiện cần tìm đó xuất hiện 1 lần thôi

 

[Lê Duy Thương]

Hàm LOOKUP theo cấu trúc nó thì khó nhớ thật. Lúc trước em có ăn cắp của 1 bạn IT làm cho vợ với cấu trúc đơn giản, nên em chỉ sài như vậy cho đỡ đau đầu: =LOOKUP(2,1/(ĐK1=VÙNG ĐK1)/(ĐKn=VÙNG ĐKn),VÙNG LẤY KQ). 2,1 là gì em không biết mà cứ như vậy làm tới.

nếu bạn học hàm này thì tìm tên những công thức đặc biệt trên GPE . đọc xong sẽ hiểu và áp dụng được

 

[VetMini]

Hàm LOOKUP theo cấu trúc nó thì khó nhớ thật. Lúc trước em có ăn cắp của 1 bạn IT làm cho vợ với cấu trúc đơn giản, nên em chỉ sài như vậy cho đỡ đau đầu: =LOOKUP(2,1/(ĐK1=VÙNG ĐK1)/(ĐKn=VÙNG ĐKn),VÙNG LẤY KQ). 2,1 là gì em không biết mà cứ như vậy làm tới.

Đó là MẸO; không phải cách sử dụng căn bản của hàm.
Mẹo này sử dụng khá nhiều tính chất của hàm mà dân chuyên sâu mới biết. Khi khám phá ra cách sử dụng này, người viết công thức có giải thích cặn kẽ. Về sau, những người khác chỉ biết copy và mách lại cho nên phần giải thích bị thất lạc.

 

[LmoovoenX]

Đó là MẸO; không phải cách sử dụng căn bản của hàm.
Mẹo này sử dụng khá nhiều tính chất của hàm mà dân chuyên sâu mới biết. Khi khám phá ra cách sử dụng này, người viết công thức có giải thích cặn kẽ. Về sau, những người khác chỉ biết copy và mách lại cho nên phần giải thích bị thất lạc.

Em thử giải thích hàm trên bác xem hộ xem đúng không.

1/(ĐK1=VÙNG ĐK1)/(ĐKn=VÙNG ĐKn)

Mảng duyệt theo nhiều điều kiện, nếu đáp ứng tất cả điều kiện mảng trả 1, nếu không mảng trả lỗi (chia cho 0).
Tham số 2, ứng với tính chất dò tìm gần đúng của lookup, nó sẽ lấy giá trị cuối cùng của mảng dò tìm có kết quả là 1.
Như vậy hàm trên sẽ lấy giá trị cuối cùng của vùng lấy kết quả, tương ứng với phù hợp với tất cả các điều kiện.
Thế nhưng mảng dò tìm đây chắc chỉ mảng 1 chiều. Nãy bác có nói mảng 2 chiều, chắc là 1 kiểu của bảng. Nhưng còn mảng 3 chiều là như thế nào, mong bác giải thích hộ.

 

[VetMini]

...
Tham số 2, ứng với tính chất dò tìm gần đúng của lookup, nó sẽ lấy giá trị cuối cùng của mảng dò tìm có kết quả là 1.
Như vậy hàm trên sẽ lấy giá trị cuối cùng của vùng lấy kết quả, tương ứng với phù hợp với tất cả các điều kiện.

Đó là chỗ lắc léo của mẹo. Giải thích hơi dài dòng. Người sử dụng cần biết đặc tính của phép dò gần đúng.
Theo nguyên tắc dò gần đúng trên mảng sắp xếp tăng đần, con toán sẽ dừng lại ở vị trí nó cho là lớn nhất và nhỏ hơn hay bằng trị cần tìm.
Ví dụ: tìm 3.2 trên mảng { 1, 2, 3, 4, 5 } thì các con số nhỏ hơn hay bằng 3.2 là 1,2,3 lớn nhất là 3. Lookup không so sánh 1,2,3 để lấy số lớn nhất. Nó cho rằng mảng đã sắp xếp cho nên cái bên phải (hoặc dưới cùng) là lớn nhất.
Khi dò mảng "mẹo" như bạn đưa ra ở trên, Lookup không thấy 1:0 mà nó chỉ thấy True:#DIV/0!
True thì nó có thể ép kiểu (lưu ý từ ép kiểu) thành 1, nhưng error thì nó không ép kiểu thành 0 như nhiều người tưởng lầm. - tôi đã từng nói hàm này có khả năng bỏ qua trị lỗi. Vậy là nó cứ dò ngược (phải qua trái, hoặc dưới lên trên) cho đến khi nó gặp một trị nhỏ hơn 2, tức là 1, tức là vị trí cuối cùng thỏa mãn các điều kiện.
Vì vậy, để hiểu công thức này, người dùng phải biết hai đặc tính của Lookup trên bảng dò:
- Khả năng bỏ qua trị lỗi. (xem chú thích)
- Tự cho rằng trị bên phải lớn hơn trị bên trái, ở dưới lớn hơn ở trên.

Chú thích Lookup chỉ bỏ qua trị lỗi chứ không bỏ qua trị 0 (nếu ép kiểu False thì ra 0)
Nếu ta tìm 2 trong mảng dò { 1, 0, #Div/0, 1, #Div/0 } thì nó cho rằng vị trí 4 (trị 1) chứa trị lớn nhất và nhỏ hơn 2
Nhưng nếu mảng dò là { 1, 0, #Div/0, 1, 0 } thì nó cho rằng vị trí 5 (trị 0) chứa trị lớn nhất và nhỏ hơn 2.

Thế nhưng mảng dò tìm đây chắc chỉ mảng 1 chiều. Nãy bác có nói mảng 2 chiều, chắc là 1 kiểu của bảng. Nhưng còn mảng 3 chiều là như thế nào, mong bác giải thích hộ.

Cái mảng ở bài #142 là mảng 2 chiều (4 dòng 2 cột)

Mảng 3 chiều thì gồm dòng/cột/vùng

 

[bebo021999]

Gần như nhất trí hết với bác, loại trừ cái này:

Khi dò mảng "mẹo" như bạn đưa ra ở trên, Lookup không thấy 1:0 mà nó chỉ thấy True:#DIV/0!

Không phải True:#DIV/0, mà là 1:#DIV/0
Khi mảng điều kiện:
ĐK1=VÙNG ĐK1
đứng 1 mình thì nó sẽ gồm true:false . Do vậy người ta mới nghĩ ra cách cho nó xuống mẫu số để ép nó thành 1:#DIV/0
Xét ví dụ:
Giả sử vùng DK có giá trị là {1,2,3,4,5}, vùng KQ có giá trị {"A","B","C","D","E"}
Với điều kiện là 3.2, muốn ra kết quả là "C"
Khi này biểu thức so sánh sẽ là:
VÙNG ĐK<=ĐK
trả về {TRUE,TRUE,TRUE,FALSE,FALSE}
Muốn tìm đến vị trí TRUE cuối cùng, ép TRUE về 1 và FALSE về #DIV/0 (nếu FALSE về 0 thì dãy này không thỏa điều kiện tăng dần)
1/(VÙNG ĐK<=ĐK) = {1,1,1,#DIV/0,#DIV/0)
Dãy này coi như tăng dần (chỉ có số 1, #DIV/0 bỏ qua)
và:
=LOOKUP(2,1/(VÙNG ĐK=ĐK),VÙNG LẤY KQ)
=LOOKUP(2,1/(TRUE,TRUE,TRUE,FALSE,FALSE),VÙNG LẤY KQ)
=LOOKUP(2,{1,1,1,#DIV/0,#DIV/0},{"A","B","C","D","E"})
="C"

 

[VetMini]

Gần như nhất trí hết với bác, loại trừ cái này:

Không phải True:#DIV/0, mà là 1:#DIV/0

Đúng, xin lỗi tôi nói lầm một chút.
Điều kiện được đặt trong một con toán chia 1/(điều kiện). True (thỏa) đã được ép kiểu thành 1 trước khi chia, và 1/1 = 1. False được ép kiểu thành 0 trước khii chia 1/0 = Div/0.

...
VÙNG ĐK<=ĐK
trả về {TRUE,TRUE,TRUE,FALSE,FALSE}
Muốn tìm đến vị trí TRUE cuối cùng, ép TRUE về 1 và FALSE về #DIV/0 (nếu FALSE về 0 thì dãy này không thỏa điều kiện tăng dần)
1/(VÙNG ĐK<=ĐK) = {1,1,1,#DIV/0,#DIV/0)
Dãy này coi như tăng dần (chỉ có số 1, #DIV/0 bỏ qua)

Cái này thì không hẳn đúng. "Điều kiện tăng dần" là vấn đề của người nạp mảng chứ không phải là vấn đề của Lookup.
Như tôi cố gắng giải thích ở trên, nó tự động coi như mảng là tăng dần. Ví dụ ở bài #149 tôi cố tình cho thấy nếu bạn đặt nhầm 0 bên phải (hoặc dưới) 1 thì nó coi như 0 ấy lớn nhất. Khi tìm trị 2 mà gặp 0 bên phải cngfbn thì Lookup coi như 0 ấy thỏa điều kiện:
1. số lớn nhất
2. nhỏ hơn hoặc bằng số cần tìm.

 

[LmoovoenX]

Vậy là nó cứ dò ngược (phải qua trái, hoặc dưới lên trên) cho đến khi nó gặp một trị nhỏ hơn 2, tức là 1, tức là vị trí cuối cùng thỏa mãn các điều kiện.
Vì vậy, để hiểu công thức này, người dùng phải biết hai đặc tính của Lookup trên bảng dò:
- Khả năng bỏ qua trị lỗi. (xem chú thích)
- Tự cho rằng trị bên phải lớn hơn trị bên trái, ở dưới lớn hơn ở trên.

Chú thích Lookup chỉ bỏ qua trị lỗi chứ không bỏ qua trị 0 (nếu ép kiểu False thì ra 0)
Nếu ta tìm 2 trong mảng dò { 1, 0, #Div/0, 1, #Div/0 } thì nó cho rằng vị trí 4 (trị 1) chứa trị lớn nhất và nhỏ hơn 2
Nhưng nếu mảng dò là { 1, 0, #Div/0, 1, 0 } thì nó cho rằng vị trí 5 (trị 0) chứa trị lớn nhất và nhỏ hơn 2.

Em thì cứ nghĩ là lookup vẫn dò từ trên xuống, lấy giá trị mà nó cho rằng vừa lớn nhất và nhỏ hơn hoặc bằng giá trị cần tìm, tương tự hàm Match tham số 1.

Ví dụ ở c3, dò tìm giá trị 2, kết quả trả về là 1.5, vì nó thấy 4 lớn hơn 2, nên nó sẽ lấy giá trị ngay trên giá trị 4, vì nó mặc định mảng đã được sắp xếp.
Nên có thể nói về mặt ý nghĩa, nó dò tìm giá trị lớn nhất, nhỏ hơn hoặc bằng giá trị cần tìm.

 

[cantl]

Em thì cứ nghĩ là lookup vẫn dò từ trên xuống, lấy giá trị mà nó cho rằng vừa lớn nhất và nhỏ hơn hoặc bằng giá trị cần tìm, tương tự hàm Match tham số 1.
View attachment 289220
Ví dụ ở c3, dò tìm giá trị 2, kết quả trả về là 1.5, vì nó thấy 4 lớn hơn 2, nên nó sẽ lấy giá trị ngay trên giá trị 4, vì nó mặc định mảng đã được sắp xếp.
Nên có thể nói về mặt ý nghĩa, nó dò tìm giá trị lớn nhất, nhỏ hơn hoặc bằng giá trị cần tìm.

Sau có xảy ra gì thì không được nói là: "À, mình test hàm trên điện thoại" nhé.

Hàm lookup thấy mọi người hay để số 2, chứ em toàn để số 1e9 thôi.

Bài đã được tự động gộp: 19/4/23

lookup vẫn dò từ trên xuống

Sai nè. Nó dò từ dưới lên đấy, cứ gặp thằng nào đầu tiên mà thỏa mãn "<= lookup_value" là nó lôi ra luôn.

 

[LmoovoenX]

Sau có xảy ra gì thì không được nói là: "À, mình test hàm trên điện thoại" nhé.

Hàm lookup thấy mọi người hay để số 2, chứ em toàn để số 1e9 thôi.

Bài đã được tự động gộp: 19/4/23

Sai nè. Nó dò từ dưới lên đấy, cứ gặp thằng nào đầu tiên mà thỏa mãn "<= lookup_value" là nó lôi ra luôn.

Đổi hộ vùng dữ liệu thành 1,9 thay vì 9,1 xem bạn.
À, vẫn ra 5, vì lookup tận 100000000, phải sửa là 6 mới được.

 

[cantl]

phải sửa là 6 mới được

cứ gặp thằng nào đầu tiên mà thỏa mãn "<= lookup_value" là nó lôi ra luôn

nhỉ nhỉ nhỉ nhỉ nhỉ.

 

[VetMini]

Em thì cứ nghĩ là lookup vẫn dò từ trên xuống, lấy giá trị mà nó cho rằng vừa lớn nhất và nhỏ hơn hoặc bằng giá trị cần tìm, tương tự hàm Match tham số 1.
...

...
Sai nè. Nó dò từ dưới lên đấy, cứ gặp thằng nào đầu tiên mà thỏa mãn "<= lookup_value" là nó lôi ra luôn.

Cả hai đều sai.
Dò từ trên xuống hay dưới lên làm sao hiệu quả được. Phép dò này trung bình là n/2, không khác gì dò chính xác.
Thuật toán thông dụng nhất để dò mảng tăng dần là phép dò nhị phân - từ khóa: binary search. Phép dò này có độ phức tạp là O(log n).
Đầu tiên nó so với trị lớn nhất (phần tử cuối), kế đó so với trị nhỏ nhất (phần tử đầu). Theo toán thì tùy ý muốn đầu nào trước cũng được, nhưng tôi tin là MS chọn đầu lớn trước.
Sau khi chắc là trị nằm trong khoảng hai đầu thì nó cắt đôi mảng, so với trị giữa, nếu trị giữa lớn hơn thì nó lấy khúc trước và [đệ quy] dò tiếp; ngược lại thì nó lấy khúc sau.

Ví dụ: tìm 4 trong { 1, 2, 9, 10, 11, 20, 100 }
1. 4 nhỏ hơn 100. Như vậy mục tiêu nằm đâu đó trước phần tử cuối.
2. 4 lớn hơn 1. Như vậy mục tiêu nằm đâu đó trong mảng.
3. Cắt ra 2 mảng { 1, 2, 9 } và { 11, 20, 100 }; điểm giữa là 10
4. 4 nhỏ hơn 10. Như vậy mục tiêu nằm trong mảng thứ nhất.
5. Tiếp tục dò sẽ được 4 nằm giữa 2 và 9. Như vậy 2 là mục tiêu.
Vì mảng nhỏ cho nên thấy các bước dài dòng. Mảng cỡ 1000 phần tử sẽ thấy hiệu quả của nó.

 

[cantl]

Ví dụ: tìm 4 trong { 1, 2, 9, 10, 11, 20, 100 }

1000 phần tử

Tình hình với 1000 phát ví dụ của bác thì em biểu diễn kỹ thuật cá nhân và ngộ ra rằng: hàm lookup này "BẮT BUỘC" người dùng "PHẢI" sắp xếp "TĂNG DẦN", sắp xếp linh tinh mà ra kết quả khả năng là do ăn ở tốt nên Office thương thôi.

Nếu không "TĂNG DẦN" thì hàm sẽ trả về giá trị theo sở thích và đam mê. Chỉ thay đổi vùng tham chiếu là nó nghịch ngợm ngay.
Có thể do trình độ lập trình Excel của Microsoft còn hạn chế nên gây ra lỗi này.

 

[LmoovoenX]

Cả hai đều sai.
Dò từ trên xuống hay dưới lên làm sao hiệu quả được. Phép dò này trung bình là n/2, không khác gì dò chính xác.
Thuật toán thông dụng nhất để dò mảng tăng dần là phép dò nhị phân - từ khóa: binary search. Phép dò này có độ phức tạp là O(log n).
Đầu tiên nó so với trị lớn nhất (phần tử cuối), kế đó so với trị nhỏ nhất (phần tử đầu). Theo toán thì tùy ý muốn đầu nào trước cũng được, nhưng tôi tin là MS chọn đầu lớn trước.
Sau khi chắc là trị nằm trong khoảng hai đầu thì nó cắt đôi mảng, so với trị giữa, nếu trị giữa lớn hơn thì nó lấy khúc trước và [đệ quy] dò tiếp; ngược lại thì nó lấy khúc sau.

Ví dụ: tìm 4 trong { 1, 2, 9, 10, 11, 20, 100 }
1. 4 nhỏ hơn 100. Như vậy mục tiêu nằm đâu đó trước phần tử cuối.
2. 4 lớn hơn 1. Như vậy mục tiêu nằm đâu đó trong mảng.
3. Cắt ra 2 mảng { 1, 2, 9 } và { 11, 20, 100 }; điểm giữa là 10
4. 4 nhỏ hơn 10. Như vậy mục tiêu nằm trong mảng thứ nhất.
5. Tiếp tục dò sẽ được 4 nằm giữa 2 và 9. Như vậy 2 là mục tiêu.
Vì mảng nhỏ cho nên thấy các bước dài dòng. Mảng cỡ 1000 phần tử sẽ thấy hiệu quả của nó.

Cái này là dò tìm nhị phân phải không bác. Em không học về cái này nên không biết.

Tình hình với 1000 phát ví dụ của bác thì em biểu diễn kỹ thuật cá nhân và ngộ ra rằng: hàm lookup này "BẮT BUỘC" người dùng "PHẢI" sắp xếp "TĂNG DẦN", sắp xếp linh tinh mà ra kết quả khả năng là do ăn ở tốt nên Office thương thôi.

Nếu không "TĂNG DẦN" thì hàm sẽ trả về giá trị theo sở thích và đam mê. Chỉ thay đổi vùng tham chiếu là nó nghịch ngợm ngay.
Có thể do trình độ lập trình Excel của Microsoft còn hạn chế nên gây ra lỗi này.

Nó mặc định là mảng tăng dần, nên thấy giá trị 20, mặc định là các giá trị sau lớn hơn 20 nên dò tìm 4 sẽ trả về giá trị 1.

 

[cantl]

dò tìm 4 sẽ trả về giá trị 1

Bạn làm nghề y mà đưa ra kết luận sớm quá. Với lại cách xử lý của bạn có vẻ gì đó khá là vội vàng. Hy vọng không ảnh hưởng đến nghề nhé.

Ý trên tớ có thể cho lookup dò tìm 4 trả về khác 1 đấy.

 

[ptm0412]

Bạn làm nghề y mà đưa ra kết luận sớm quá. Với lại cách xử lý của bạn có vẻ gì đó khá là vội vàng. Hy vọng không ảnh hưởng đến nghề nhé.

Ý trên tớ có thể cho lookup dò tìm 4 trả về khác 1 đấy.

Phát biểu linh tinh. Đưa cái dò tìm 4 ra kết quả khác 1 lên đây.