Ứng dụng Toán học để rút gọn công thức trong Excel

[ptm0412]

Thực tế như ở cuối bài chú đã nói con liệt kê vẫn chưa đủ thì làm sao có thể liệt kê các hàm Boolean ra hết được? Hàm số dạng f(A,B,C,....) thì nhiều vô cùng tận, không cách nào liệt kê ra hết được.

Thực tế, chú Mỹ cứ đưa bất cứ công thức Boolean nào trong vòng 04 biến A,B,C,D đổ lại. Trong vòng 02 phút rưỡi sau khi lập xong bảng chân trị, con sẽ rút gọn được công thức ấy. Còn nhiều hơn 04 thì con chịu.

Không cần phức tạp đến mức đó. Vấn đề tôi muốn làm, là với những suy luận logic rút ra từ bảng "chân trị", ta phải áp dụng được vào thực tế để rút gọn công thức. Chứ thực tế đâu có chuyện f(A,B,C,D)=A*B*C*(A+D)

Với 2 điều kiện thì bảng trên tôi đã làm đủ cho công thức rồi. Với VBA thì thêm toán tử Xor nữa. Bây giờ chỉ còn vấn đề là áp dụng.

Thí dụ Đả Sầu thử rút gọn công thức sau:

=IF(AND(A1>=10;A1<=100);"Yes";"No")

Bảo đảm, sau khi Đả Sầu rút gọn bằng cộng trừ nhân chia xong, tôi còn có thể rút gọn 1 lần nữa.

Thí dụ 2:

=If(Or(A1="Đỏ",A1="Xanh",A1="Đen"),"Nấu chè", "Nấu xôi")

Nếu A1 chỉ có 1 giá trị trong 4 giá trị Đỏ, Trắng, Đen, Xanh, tôi sẽ rút gọn:

=If(A1="Trắng","Nấu xôi","Nấu chè")

Thí dụ 3:

Viết công thức cho 1 ô nào đó với điều kiện như sau:
- Ô A1 có 1 trong 4 giá trị Đỏ, Trắng, Đen, Xanh
- Ô A2 có 1 trong 3 giá trị Trắng, Đen, Xanh
- Quy ước: Trắng nấu xôi, Màu khác nấu chè

Yêu cầu:
Xét cả 2 ô, cho ra kết quả: "Chỉ có xôi", "Có cả xôi và chè", "Chỉ có chè"

Tóm lại: Cái mọi người cần là logic tính toán để làm ngắn gọn nhất có thể, trong thực tế sử dụng Excel.

Ghi chú:
Về việc sử dụng table trong bài viết, Đả Sầu xem bài này và tải addin về dùng:
http://www.giaiphapexcel.com/forum/showthread.php?13636-Copy-bảng-tính-vào-bài-viết/page2
Addin Table_GPE
Nhớ cám ơn Thầy Phạm Duy Long, Thành viên danh dự, Mod vĩnh viễn của GPE.

 

[VietDaSau]

Thí dụ Đả Sầu thử rút gọn công thức sau:

=IF(AND(A1>=10;A1<=100);"Yes";"No")

Bảo đảm, sau khi Đả Sầu rút gọn bằng cộng trừ nhân chia xong, tôi còn có thể rút gọn 1 lần nữa.

Công thức rút gọn của con là: =If((A1>=10)*(A1<=100);"Yes";"No"), hẻm rút hơn được nữa.

hí dụ 3:

Viết công thức cho 1 ô nào đó với điều kiện như sau:
- Ô A1 có 1 trong 4 giá trị Đỏ, Trắng, Đen, Xanh
- Ô A2 có 1 trong 3 giá trị Trắng, Đen, Xanh
- Quy ước: Trắng nấu xôi, Màu khác nấu chè

Yêu cầu:
Xét cả 2 ô, cho ra kết quả: "Chỉ có xôi", "Có cả xôi và chè", "Chỉ có chè"

Ô A2 có cho thêm Đâu đỏ hay không thì kết quả vẫn vậy, công thức là:
=IF(MOD((A1="Trắng")+(A2="Trắng"),2),"Có cả xôi và chè","Chỉ có "&IF(A1="Trắng","Xôi","Chè"))

 

[ptm0412]

Ban đầu:
Len(=IF(AND(A1>=10;A1<=100);"Yes";"No")) = 34

Sau khi "rút":
=Len(If((A1>=10)*(A1<=100);"Yes";"No")) = 33

Áp dụng phủ định (not), Len còn 31:

=IF(OR(A1<10;A1>100);"No";"Yes")

Chỉ là áp dụng câu tôi nói trong bài #19: nhận xét rằng Not (A And B) = Not(A) Or Not(B)
(Thí dụ 2 cũng là dùng not, coi như đã gợi ý rồi)

Tất nhiên 2 ký tự chả là nghĩa lý gì, nhưng đây là 1 thí dụ vô cùng nhỏ để áp dụng cho những trường hợp khác lớn hơn.

 

[VietDaSau]

Hì hì, không rành lắm mấy hàm trong Excel, nhưng sau khi ngâm kiú thêm ví dụ 03 thì thấy công thức này ngắn hơn
=CHOOSE((A1="Trắng")+(A2="Trắng")+1,"Chỉ có chè","Có cả xôi và chè","Chỉ có xôi")

 

[ptm0412]

Hì hì, không rành lắm mấy hàm trong Excel, nhưng sau khi ngâm kiú thêm ví dụ 03 thì thấy công thức này ngắn hơn
=CHOOSE((A1="Trắng")+(A2="Trắng")+1,"Chỉ có chè","Có cả xôi và chè","Chỉ có xôi")

Chính xác là tôi trông chờ công thức này. Thí dụ 3 là 1 cái bẫy xôi chè ngọt ngào. Không phải hễ thấy điều kiện thì dùng If hoặc cộng trừ kiểu toán học.
Nghĩa là không chết dí vào 1 cách suy luận duy nhất.

 

[yeudoi]

Về công thức thì tôi nghĩ thế này, việc rút gon công thức đa phần là tốt bởi vì nó giúp tối ưu tốc độ, nhưng có nhiều lúc rút gọn công thức vẫn chưa tối ưu được, vì vậy chúng ta cần hiểu hết và sử dụng dữ liệu nhiều để xét.

 

[VietDaSau]

Chính xác là tôi trông chờ công thức này. Thí dụ 3 là 1 cái bẫy xôi chè ngọt ngào. Không phải hễ thấy điều kiện thì dùng If hoặc cộng trừ kiểu toán học.
Nghĩa là không chết dí vào 1 cách suy luận duy nhất.

Quả là tuổi trẻ được kinh nghiệm đi trước của chú Mỹ thật là hay. Chú cháu mình tiếp tục thảo luận nhé, con nhờ chú cho con được mở rộng trí óc hơn chút nữa.
Đây là một công thức chú lập cho một member trên diễn đàn:
= INDEX($Z$5:$AE$7,IF(G8<=300,1,IF(G8<=400,2,3)),IF( E8<=1200,1,2)+IF(F8<=300,0,IF(F8<=400,2,4)))

Con đã máy móc rút gọn và được kết quả như thế này, chỉ ngắn hơn vài ký tụ nhưng rất rối rắm, kết quả dù đúng nhưng đọc vào cực khó hiểu
= INDEX($Z$5:$AE$7,((G8<=400)-2)*((G8<=300)-1)+1,2-(E8<=1200)+2*((F8<=400)-2)*((F8<=300)-1))
Chỉ được cái là rút hết if đi thôi.

Sau đó mới đọc lại một lần công thức chú lập sẵn để hiểu ý nghĩa, thì rút gọn được như thế này:
=INDEX($Z$5:$AE$7,1+(G8>300)+(G8>400),1+(E8>1200)+2*((F8>300)+(F8>400)))
Gọn và dễ hiểu hơn công thức rút gọn ở trên
Con tải file đính kèm lên, lần sau chú cháu mình thảo luận sâu hơn về vấn đề này nhé

 

[ptm0412]

Công thức 3 ngắn nhất, vì đã dùng đến phủ định (trong trường hợp thích hợp)
Tuy gọi là "dễ hiểu" hơn công thức 2, nhưng không dễ hiểu bằng công thức 1. Vì logic vốn trừu tượng, nhất là logic phủ định.

 

[MonaLisa7775]

Chính xác là tôi trông chờ công thức này. Thí dụ 3 là 1 cái bẫy xôi chè ngọt ngào. Không phải hễ thấy điều kiện thì dùng If hoặc cộng trừ kiểu toán học.
Nghĩa là không chết dí vào 1 cách suy luận duy nhất.

Em cũng thích ngọt ngào lém sư phụ à, nhưng em hong có sập bẫy. Em xài cái này nà:

=CHOOSE(CountIf(A1:A2,"Trắng")+1,"Chỉ có chè","Có cả xôi và chè","Chỉ có xôi")

Hehe, ngắn hơn mọi thứ "toán học boolean" trên đời. (Nói vậy chẳng qua là Monalisa hong giỏi toán thoai)

 

[VietDaSau]

Em cũng thích ngọt ngào lém sư phụ à, nhưng em hong có sập bẫy. Em xài cái này nà:

=CHOOSE(CountIf(A1:A2,"Trắng")+1,"Chỉ có chè","Có cả xôi và chè","Chỉ có xôi")

Hehe, ngắn hơn mọi thứ "toán học boolean" trên đời. (Nói vậy chẳng qua là Monalisa hong giỏi toán thoai)

Cảm ơn nàng Monalisa, các ví dụ em chọn trong này đi từ dễ đến khó, đơn giản đến phức tạp. Đây thật ra chỉ là vấn đề "tầm chương trích cú", làm mãi rồi quen thôi chứ không thể nói giỏi dở ở đây được chị hen. Vấn đề vẫn như chú Mỹ nói:

logic tính toán để làm ngắn gọn nhất có thể, trong thực tế sử dụng Excel.

Cảm ơn chị đã cho em biết thêm một cách làm ngắn gọn.

 

[Nguyễn Duy Tuân]

Để đảm bảo tối ưu về tốc độ tính toán nên dùng hàm IF lồng nhau, kết hợp tới các hàm OR, AND...Bần cùng lắm mới dùng phép cộng (+), phép nhân (*) logic.

Ví dụ: Nếu BT1 > BT2 và BT2 > BT3 thì trả về BT4.

Công thức là:
Cách 1:
=IF(BT1>BT2, IF(BT2>BT3, BT4, 0), 0)
BT2, BT3 chỉ phải tính toán nếu BT1>BT2, BT4 chỉ phải tính toán nếu BT2>BT3. Nếu BT1>BT2 = FALSE thì BT2,BT3,BT4 không phải tính toán nữa.

Cách 2:
=IF(AND(BT1>BT2, BT2>BT3), BT4, 0)
Tương tự như Cách 1

Cách 3:
=IF((BT1>BT2)*(BT2>BT3), BT4, 0)
Trong mọi trường hợp thì BT1,BT2,BT3 đều phải tính toán

Cách 4:
=(BT1>BT2)*(BT2>BT3)*BT4
Trong mọi trường hợp thì BT1,BT2,BT3,BT4 đều phải tính toán hết ==> Phương pháp ngắn gọn nhất nhưng lại tồi tệ nhất!

Con người tạo ra máy tính nên tư duy của nó cũng giống con người. Nên làm gì và làm khi có điều kiện nào.... Như một người cha nói với 2 người con rằng "nếu cái Hà bận thì thằng Tí phải đi đón bác". Không nhẽ lại để cả 2 đứa đi đón bác (trong trường hợp chỉ 1 trong hai đứa đi đón bác, còn đi đón bác và chở đồ nữa thì phải huy động cả hai rồi <--> "Multi-Threaded"/Xử lý đa luồng).

Tôi thường ưu tiên giải pháp về tốc độ->Trình bày dễ hiểu.

Làm việc với máy tính thì tư duy toán học cũng như các thuật toán thực sự rất cần thiết!

 

[VietDaSau]

Cảm ơn lời bài phân tích và góp ý của anh Tuân.
Rất lâu rồi em không đụng đến Excel, hồi đó xài Excel 97 thì một trong hai hàm sau báo lỗi:
=IF(A1>6.5,"Bảy",IF(A1>7.5,"Tám",IF(A1>8.5,"Chín",IF(A1>9.5,"Mười")))) (Công thức 1)
=IF(A1>9.5,"Mười",IF(A1>8.5,"Chín",IF(A1>7.5,"Tám",IF(A1>6.5,"Bảy")))) (Công thức 2)
Chả nhớ rõ là cái nào, nhưng vừa thử lại trên Excel, thì công thức 1 ra “Bảy” hoặc False, còn công thức 02 ra “Bảy”, “Tám”, “Chín”, “Mười” và False.
Như vậy đây là điểm khác biệt giữa Excel 97 và Excel 2003, em sẽ ghi nhận điều này.

Máy tính hơn con người ở chỗ nó nhanh, không biết chán và chính xác, nhưng dở hơn con người là không biết tư duy. Máy tính rất tuyệt vời ở chỗ tính toán, nhưng không nhận dạng vấn đề chính xác, ít nhất trong các trường hợp thảo luận vừa rồi là logic toán.

Hơn nữa topic của em đặt hơi sai, lý ra phải đặt là “Giải bài toán trên Excel như thế nào?” thì hợp lý hơn. Thôi thì lỡ rồi, ai quan tâm đến thì chủ đề nào cũng có ích với họ cả.
Ví dụ ta lấy bài toán: Tìm chữ số cuối cùng của biểu thức 2^n+3^n.
Một học sinh bình thường biết chút ít về Excel cũng có thể lập được công thức:
=Mod(2^n+3^n,10) (3)
Nhưng công thức trên có bất lợi là có lẽ n khó lòng mà vượt quá 300, vậy thì phải làm sao?
Có thể lập được một công thức thế này:
=choose(Mod(n,4)+1,7,5,3,5) (4)
Đến lúc này thì có thể mở rộng n lên đến vài tỷ, công thức (4) dù dài hơn nhưng trên thực tế sẽ thực hiện nhanh và n lớn hơn rất nhiều so với công thức (3)
Khi chưa biết đến hàm choose, công thức của em lập ra như thế này:
=mod(n,2)*5 + (1-Mod(n,2))*(7-2*Mod(n,4))

Nói chung rảnh rỗi quá thì tập suy luận một chút thôi, nói chung là tìm hiểu một chút về Số học, mà cách áp dụng hay nhất là thử giải các bài toán như vừa nói. Excel là phần mềm phổ biến mà máy nào cũng cài, nên đưa ra topic này để mở mang thêm được chút ít.

 

[ngocmaipretty]

Rất lâu rồi em không đụng đến Excel, hồi đó xài Excel 97 thì một trong hai hàm sau báo lỗi:
=IF(A1>6.5,"Bảy",IF(A1>7.5,"Tám",IF(A1>8.5,"Chín",IF(A1>9.5,"Mười")))) (Công thức 1)
=IF(A1>9.5,"Mười",IF(A1>8.5,"Chín",IF(A1>7.5," Tám",IF(A1>6.5,"Bảy")))) (Công thức 2)
Chả nhớ rõ là cái nào, nhưng vừa thử lại trên Excel, thì công thức 1 ra “Bảy” hoặc False, còn công thức 02 ra “Bảy”, “Tám”, “Chín”, “Mười” và False.
Như vậy đây là điểm khác biệt giữa Excel 97 và Excel 2003

Cả 2 công thức đều sai chú nhỏ ơi, chứ đâu phải Excel khác biệt?

Công thức 1:
- Loại trừ ngược: Loại từ dưới lên thì dấu phải là nhỏ hơn (<), A1 mà lớn hơn 6.5 (A1>6.5 = True) là lấy "Bảy" ngay từ đầu, đâu có test mấy cái If sau nữa? Vì mấy cái If sau là giá trị dành cho False của If ngoài cùng rồi.
- Cái If thứ 4 bị thiếu tham số thứ 3, vậy nếu A1<=6.5 (toàn bộ điều kiện của 4 If đều False), không có cái quỷ gì để gán, nó sẽ gán False chứ sao?

Công thức đúng là:

=IF(A1<6.5,"Nhỏ wá",IF(A1<7.5,"Bảy",IF(A1<8.5,"Tám",IF(A1<9.5, "Chín","Mười"))))

Công thức 2:

Sai y chang lỗi 2 của công thức 1: Không có tham số thứ 3 cho If thứ 4. Công thức đúng là:

=IF(A1>9.5,"Mười",IF(A1>8.5,"Chín",IF(A1>7.5," Tám",IF(A1>6.5,"Bảy","Thiếu cái này nè"))))

 

[ndu96081631]

Trong mọi trường hợp thì BT1,BT2,BT3,BT4 đều phải tính toán hết ==> Phương pháp ngắn gọn nhất nhưng lại tồi tệ nhất!

Con người tạo ra máy tính nên tư duy của nó cũng giống con người. Nên làm gì và làm khi có điều kiện nào.... Như một người cha nói với 2 người con rằng "nếu cái Hà bận thì thằng Tí phải đi đón bác". Không nhẽ lại để cả 2 đứa đi đón bác (trong trường hợp chỉ 1 trong hai đứa đi đón bác, còn đi đón bác và chở đồ nữa thì phải huy động cả hai rồi <--> "Multi-Threaded"/Xử lý đa luồng).

Tôi thường ưu tiên giải pháp về tốc độ->Trình bày dễ hiểu.

Làm việc với máy tính thì tư duy toán học cũng như các thuật toán thực sự rất cần thiết!

Lúc trước tôi cũng thích làm theo ý mình, nhưng sau này khi làm với những bài toán lớn mới thấy thì ra toàn bộ suy nghĩ trước đó là sai lầm
Tôi đã rút ra kết luận:
- Ngắn nhất không có nghĩa là nhanh nhất
- Cái gì MS viết sẵn thường tối ưu hơn cái do ta tự nghĩ ra
Vậy nên:
- Biết cách sử dụng công cụ hợp lý sẽ khiến cho máy tính đở nặng gánh hơn, từ đó mà tốc độ tính toán sẽ nhanh hơn
- Nếu dùng IF (hoặc 1 hàm có sẵn) thì MS khỏi cần phải tính toán phức tạp, cứ thế mà "xơi"
- Dùng các toán tử nhân chia gì gì đó... cuối cùng cũng ra kết quả nhưng ta vô tình đã bắt máy tính làm thêm 1 công đoạn nữa
Hic... Chả biết nói sao.. nhưng ai thích làm gì thì.. tùy ý vậy!
Ẹc... Ẹc...

 

[haonlh]

Tìm chữ số cuối cùng của biểu thức 2^n+3^n.

=Mod(2^n+3^n,10) (3)
=choose(Mod(n,4)+1,7,5,3,5) (4)

Chứng minh thế nào nhỉ?

 

[haonlh]

Chứng minh được rồi:
kiểm tra đuôi của 2^n với n =1,2,3,4,5 thì thấy tận cùng là 2, 4, 8, 6, 2
Tương tự đuôi của 3^n với n =1,2,3,4,5 thì thấy tận cùng là 3, 9, 7, 1, 3

sau 4 giá trị thì lặp lại, do đó tổng của chúng có tận cùng cũng tận cùng là 5, 3, 5, 7

Chú ý: các số như 4^n , 5^5 ... cũng có tận cùng tương ứng, chu kỳ có thể là 2 hoặc 4.

 

[ptm0412]

Chứng minh được rồi:
kiểm tra đuôi của 2^n với n =1,2,3,4,5 thì thấy tận cùng là 2, 4, 8, 6, 2
Tương tự đuôi của 3^n với n =1,2,3,4,5 thì thấy tận cùng là 3, 9, 7, 1, 3

sau 4 giá trị thì lặp lại, do đó tổng của chúng có tận cùng cũng tận cùng là 5, 3, 5, 7

Chú ý: các số như 4^n , 5^5 ... cũng có tận cùng tương ứng, chu kỳ có thể là 2 hoặc 4.

Như vậy mới là thống kê và kết luận, chưa gọi là chứng minh.

 

[haonlh]

1/ Xét tập N gồm các số nguyên dương, i, j, k, n thuộc tập N.
Xét hàm f(n) = 2^n mod 10

• f(n) là số có 1 chữ số
• f(n) là số chẵn
• 2^n không thể chia hết cho 10 nên f(n) khác 0

Vậy f(n) thuộc tập A = {2,4,6,8}
Thử trực tiếp f(1) = 2, f(2) = 4, f(3) = 8, f(4) = 6 nên f(n) nhận các giá trị 2,4,6,8 (với mọi n thuộc N)

2/ Với mọi k thuộc A, (k nhân 6) mod 10 = k (thử trực tiếp vì A có 4 phần tử). Chú ý rằng f(4) = 6

3/ Theo lý thuyết đồng dư, (a*b) mod n = ((a mod n) * (b mod n)) mod n.
Vì vậy f(n) là hàm tuần hoàn với chu kỳ bằng 4. Thật vậy
f(n+4) = 2^(n+4) mod 10 = (2^n * 2^4) mod 10 =
((2^n mod 10) * (2^4 mod 10)) mod 10 =
(f(n) * 6) mod 10 =f(n) (theo 2/).

Vậy f(n) lần lượt nhận các giá trị tuần hoàn 2, 4, 8, 6 (với n tăng dần từ 1)

4/Tương tự g(n) = 3^n mod 10 nhận các giá trị tuần hoàn là 3, 9, 7, 1.

5/ Cộng tương ứng, biết tổng 2 hàm tuần hoàn cùng chu kỳ thì cũng tuần hoàn cùng chu kỳ
thì F(n) = (2^n + 3^n) mod 10 (với n nguyên dương)
nhận các giá trị tuần hoàn là 5, 3, 5, 7. Chu kỳ bằng 4
Từ đó, n = 4k + i thì F(n) = F(i) với i>0, khi i = 0 thì F(4k) = F(4) = 7.

Đang sửa bài, bị trục trặc, phải gửi tạm, mà HYEN đã đọc. Cảm ơn.