EXCEL fans

[Tuanktcdcn]

Đã lâu không post được gì lên đây mà chỉ đi lấy về. Đọc thấy một số bài của các thành viên nên cũng có đôi điều muốn nói có thể không theo chủ đề gì cả.
Nhớ lại cách đây khoảng 8 năm khi còn đang tập tễnh theo học một lớp "Tin văn phòng" với Excel 8 buổi. Với những hiểu biết thô sơ về Excel, I đã làm được một số bài tập và một số việc cho riêng mình. Qua hàm IF, I đã nhận thấy nó giải quyết rất nhiều vấn đề, I đã mong muốn tìm hiểu sâu hơn không chỉ dừng lại ở mức "căn bản" với Excel. Với mục đích đó I đã tìm một trung tâm và hỏi học Excel nâng cao, họ bảo, nâng cao như thế nào, lúc đó I không biết học cái gì và họ nói học lập trình trên Excel nhé! Lúc đó mới ngã ngửa ra còn bao nhiêu thứ chưa biết mà đã học lập trình, tưởng Excel chỉ làm văn phòng làm các bài toán CSDL cơ bản mà thôi. Thời đó quá sợ nên không thèm học nâng cao nữa mà tự học ở nhà. Cũng phải 2 năm sau khi đã mò mẫm tìm hiểu Excel kha khá mới thấy một menu MACRO thế là từ đây I đã có thêm một đam mê với Excel, mặc dù ngại lập trình nhưng tính hấp dẫn của nó làm I không cưỡng lại được. Cứ thế theo thời gian cùng với công việc phải làm I đã viết ra một số chương trình cho mình và bạn bè bằng VB, VFP, EXCEL. Đã tham khảo một số phần mềm của Việt nam và nước ngoài và nhận thấy để giải quyết một vấn đề đơn giản như lập một chứng từ cũng phải mất hàng trang câu lệnh trong khi với Excel trực quan dùng hàm, Format một loáng thế là xong cùng lắm thêm Macro để tự động hơn.
I không phải dân chuyên IT mà do công việc và sở thích mà đã tìm hiểu về vi tính để công việc của mình nhanh hơn và tốt hơn. Với tầm nhìn hạn chế nhưng I cũng xác định rằng xã hội đã phân công lao động, có người chuyên về việc này còn người kia sẽ chuyên về việc khác. Phần mềm cũng vậy VFP (Visual Foxpro), SQL Server,..Access, Excel,... sinh ra là để làm và xử lý dữ liệu. VB, VC, VJ,... thì lại chuyên về công việc khác như Game, xử lý hệ điều hành lạm dụng hơn là dùng cho CSDL. Không thể bảo EXCEL hay SQL Server làm Game đựợc nhưng nếu để nó làm đúng với vai trò của nó thì thật kính nể.
Với I công việc của I là liên quan tới CSDL nên trước mắt chỉ dùng EXCEL, VFP để làm mà thôi mặc dù biết SQL Server còn tốt hơn. Thế hệ EXCEL đã liên tục được Microsoft nâng cấp từ 97, 2000,2001,...2004, XP chắc phải có lý do nên với I rất yên tâm và tin tưởng.
Khi một chương trình được thiết kế phục vụ cho một công việc nào đó, xử lý hoàn chỉnh đầu vào->xử lý->đầu ra nó được gọi là phần mềm cho dù nó được viết trên ngôn ngữ gì, đó chính là khái niệm của Microsoft. Với tất cả các bạn khi thiết kế một file Excel xử lý hoàn chỉnh đầu vào-> xử lý-> đầu ra bạn có thể tự hào đó là một phần mềm của riêng mình. Còn nếu bạn có viết câu lệnh xử lý thì gọi là lập trình.
Với những bạn đang theo học Excel cứ tin tưởng đi nó còn làm rất nhiều việc cho chúng ta sau này. Sử dụng nó bạn sẽ thấy đam mê công việc hơn vì nó có tính sáng tạo. Nhiều người cho rằng EXCEL chỉ làm cho các DN có CSDL nhỏ và mức lập trình chỉ cũng cỡ nhỏ. Đúng vậy, nhưng chúng ta nên nhận rõ nhỏ như thế nào? lớn như thế nào? cái chính là do khả năng quy hoạch của mỗi người, nếu hợp lý mọi chuyện đều ổn.
Với EXCEL chúng ta sẽ còn nhiều ngạc nhiên hơn nữa, hy vọng EXCEL Fans là nơi minh chứng. Riêng với lập trình trong EXCEL với lời khai báo
Declare Function Tên hàm API Lib "File.DLL" () As ....
Giống như hàm IF trên Worksheet, sẽ còn nhiều điều giải quyết được và còn sảy ra nữa.
Rất vui khi Vongphuc đã tin tưởng đề nghị thực hiện Advance sợ không dảm nhiệm được, khả năng tới đâu sẽ viết tới đó.

 

[vongphuc]

Excel Fans Club - Excel number one

Đội quân nhạc nổi quốc ca...
... quảng trường trang nghiêm lặng nghe lời đồng chí chủ tịch nước :

Thay mặt Đảng..., thay mặt chính phủ..., và toàn thể đồng bào... trong cả nước

Tôi!
Trân trọng trao tặng

huân chương sao vàng
huân chương Hồ Chí Minh
huân chương độc lập
huân chương lao động hạng nhất​

Và

... huy hiệu diễn đàn webketoan.com​

(nếu có)

Cho đồng chí

OverAC​

Chỉ trong vài phút đồng hồ, đồng chí đã tự mình đưa ra được giải thuật cho bài toán chuyển số thành chữ, mở ra khả năng điều khiển con trỏ ở tầm vĩ mô, để kiểm soát vị trí dữ liệu trong bảng tính Excel

Đồng chí xứng đáng nhận danh hiệu cao quý

“ EXCEL NUMBER ONE ”​

Chúc đồng chí... và gia đình dồi dào sức khỏe, khỏe - để cống hiến, xây dựng, và bảo vệ Tổ quốc xã hội chủ nghĩa

Xin chúc mừng OverAC

 

[Tuanktcdcn]

Bài 1: Macro là gì?

Xin tắt ngang bài của Vongphuc nhé. Theo mong muốn của một số bạn muốn học Macro một cách đơn giản và cách tiếp cận cũng đơn giản nên I sẽ đưa ra từng bước học cụ thể. Dự định và trình tự của bạn vẫn thế nhé!

Theo trình tự các bài viết 1,2,3,... sẽ hướng dẫn cách viết một Macro từ đơn giản đến phức tạp.

1) Macro là gì?
Về bản chất Macro cho phép lưu lại một số thao tác của bạn trên Sheet với một cái tên xác định.
Ví dụ: Bạn sẽ lưu lại một loạt các thao tác: Format vùng dữ liệu về Font .VntimeH cỡ 14 dạng Bold (béo), màu xanh. Đặt tên hành động đó là FontTieude.
Sau này mỗi khi bạn cần format như trên chỉ cần chạy Macro có tên FontTieude. Bạn sẽ thấy công việc của bạn được giảm đi rất nhiều mà vẫn đạt được mục đích.

Tạo một Macro FontTieude :
B1) vào Menu Tools\Macro\Record New Macro... lúc đó sẽ xuất hiện một của sổ (Window) Record Macro. Ở mục Macro Name bạn gõ FontTieude. Sau khi chọn OK sẽ có hộp thoại "Stop Recording" (hình vuông), chỉ tắt nó khi công việc hoàn thành.
B2) Bạn thực hiện các thao tác để format như vi dụ đã nêu. Sau khi xong bạn chọn nút "Stop Recording" để kết thúc.

Như vậy bạn đã có một Macro có tên là FontTieude trên Workbook.
Chạy một Macro FontTieude :(Thực hiện lại thao tác)
+ Bạn chọn (select) vùng dữ liệu cần Format (vùng này nên có dữ liệu)
+ Nhấn ALT+F8 (Tools\Macro\Macro...)
Chọn Macro có tên là FontTieude sau đó chọn Run.

Kết quả bạn sẽ thấy trên vùng select.
Macro là thế đấy, bạn có thể tạo các Macro khác theo mục đích của bạn. Bản chất của một Macro là tập hợp của những dòng lện của VB chúng ta sẽ mổ bụng nó sau này.

Trước tiên cứ thuần thục Bài 1 bạn đã có thể tự tạo ra cho mình nhiều Macro và đã giúp được nhiều công việc.

Bài 2: Khái niệm về các thành phần cơ bản của Excel

 

[vongphuc]

Chào các bạn,

Gửi OverAC : thời gian sẽ giúp chúng ta hoàn thành thảo luận này. To WhoamI : bạn rất tự tin và bạn sẽ thực hiện được mong muốn của mình

Cam_To_80 : trí tuệ của cậu làm tôi nhớ đến người bạn hồi thơ ấu, sau này về hưu, tôi muốn đến chơi với cậu, xin được ngồi nghe cậu làm thơ nhé

Bác Tuanktcdcn : tôi không biết nói gì hơn, ta cùng nâng chén chúc nhau sức khỏe. Tôi rất tiếc chưa có hôm nào làm một game AOE với bác

Tạm biệt các bạn, tôi đành phải rời xa webketoan một vài hôm. Để có thể tiếp thu thêm những kiến thức mới, tôi phải dành thời gian chuẩn bị cho bản thân mình. Việc phân chia topic thành những chủ đề nhỏ là việc của anh erpvn. Mà thực ra thì đã có bài viết đâu mà chia ra chứ

Tôi cũng đang chờ đợi bài viết của anh hai2hai về lập trình Visual Basic, bài viết về Công thức mảng và Pivot Table của bác Tuanktcdcn, sản phẩm đóng gói hoàn chỉnh giải thuật chuyển đổi số thành chữ thì chỉ có anh erpvn mới làm nổi, ...

Chỉ vài nét nữa về VBA, là chúng ta đã có thể bước vào phần hấp dẫn nhất của thảo luận này. Ta cũng có thể phân chia theo chiều ngang, ví dụ những bài liên quan đến tiếng Việt vào một topic, nhưng như thế thì không bám sát vào bản chất của kế toán rồi, và sẽ không bao giờ kết thúc

Sau một thời gian nếu không ai có hướng phân tích nào khác, tôi sẽ bắt đầu với bài viết đơn giản : Excel - level 1, mong các bạn ta cùng hợp sức bằng những ý tưởng và mã nguồn chương trình cụ thể

Tôi xin chân thành cảm ơn anh hai2hai, anh nhp, chi handung107, anh VNgeek, bạn tranvanhung đã gửi tài liệu, ý tưởng, thuật toán và chương trình cụ thể đóng góp cho thảo luận này

Xin trân trọng mời các anh, chị, và các bạn cùng tham gia viết bài cho topic này

Bạn nào cần trao đổi thêm, xin vui lòng liên hệ với tôi qua địa chỉ email vongphuc@sg.netnam.vn , những ý tưởng dù là nhỏ nhất cũng có thể khơi nguồn cho sáng tạo

Chúc các bạn một buổi tối thứ 7 - êm đềm trong hạnh phúc. Hãy ...giữ gìn sức khỏe
Đêm nay Hà Nội không có mưa, không cần phải đem theo ...áo mưa

 

[erpvn]

Này Hugô, bạn đã lạc đường rồi đấy nheeeeeeeeeeeeeeé! nhấn số 4 cho các bác xem bản đồ đây.

Xin hãy đặt Excel về đúng với vị trí của nó và trả lại cho topic này sự hữu dụng của nó đi các bác.

Hãy nhớ rằng Excel chỉ là một Applications, tôi k nói là với Excel các bác k thể làm được một chương trình kế toán. Nhưng một chương trình đúng nghĩa các bác có biết như thế nào k? Nếu bác có một chiếc xe đạp dù có cố công trang hoàng có thể gắn thêm động cơ hoặc đống hộp hay như thế nào đấy thì cũng k thể thành chiếc xe hơi được, đúng k các bác? Chứ đừng nói tới là con Mẹt chẳng hạn. Excel cũng thế, bảo mật gì trong Excel cơ chứ? Nếu đi xe đạp thì phải chịu cảnh bụi đường nắng gió và nếu chẳng may gặp tai nạn thì phải vở đầu thôi (vì có ai đi xe đạp mà đội nón bảo hiểm bao giờ?). Nếu cái bác ngồi trong một con Mẹt thì làm gì chịu cảnh ấy, còn có máy lạnh phà phà nhạc du dương... và nếu xui gặp tai nạn thì vận... chết tốt các bác ạ? (Có điều là sẽ nhận được tiền bảo hiểm hehe...)

Thế nhưng xe đạp vẫn hữu dụng đấy chứ? Trong những phố nhỏ hẹp, di chuyển những khoản cách không xa thì xe đẹp cũng tuyệt rồi phải k? nó còn tốt cho sức khỏe nửa chứ? Trong những trường hợp khác thì phải dùng những phương tiện khác thôi ví như đi từ Sài Gòn ra Nha Trang có đi xe đẹp nổi k? có chăng chỉ là mấy tay đua xe đạp mà nếu có đạp ra đến nơi thử hỏi mất tay đó có mệt k? Còn nếu đi xế hộp thì vèo một cái là tới ngay... thế đấy còn những nơi xa hơn như Hà Nội hay trong khu vực rộng ra là global thì làm sao đây?

Excel cũng vậy các bác ạ, nó là một cái tool với nhưng chức năng tương đơn giản thôi nhưng cực kỳ hữu dụng.

Trong topic này các bác đừng quá xa đà vào lập trình một ít thì tốt nhưng quá nhiều là k nên (thích thì sao k qua bên Xưởng phần mềm nhỉ?). Hãy bàn về Excel thôi những chức năng có sẵn của nó là hay nhất vì đâu phải ai cũng biết tất cả đâu...

Sau khi bà con có được số vốn kha kha BQT sẽ mở cuộc thi tranh dành danh hiệu "Mr. No1 Excel" = Đệ nhất Excel hehe. Hình thức là mỗi người phải có một sản phẩm gởi cho bà con WKT bình chọn và Hội đồng kỷ thuật quyết định (giống như Sao mai điểm hẹn vậy đó hehe)

Nhứ thế dđề nghị bác tuan&vongphuc tiếp tục công việc dang dở của mình.

Thanks & Best Regards.

erpvn

 

[changtraithitran2000]

bac nao co chuong trinh quan ly hang NXT cua kho cho tui 1 ban voi. Toi cam on rat nhieu.
diachi;toiyeuhaiduong2000@yahoo.com

 

[Tuanktcdcn]

Hàm đếm ký tự chỉ định

Có bạn nào biết hàm đếm ký tự chỉ định không?
VD: Có chuỗi "TangThanhNa", nếu đếm ký tự "a" thì hàm trả về 3, "T" trả về 2.
Mong nhận được câu trả lời của các bạn!

 

[handung107]

Thày Tuanktcdcn lại ra đề bài nữa rồi. Nếu đếm cả "A" và "a" hàm sẽ là :=LEN(chuoi)-LEN(SUBSTITUTE(SUBSTITUTE(chuoi,"A",""),"a","")). Nếu chỉ đếm "A" thì hàm là : =LEN(chuoi)-LEN(SUBSTITUTE(chuoi,"A","") hay "a" thì hàm sẽ là :=LEN(chuoi)-LEN(SUBSTITUTE(chuoi,"a","") trong đó chuoi="TangThanhAn. Còn hàm nào khác mong thày chỉ giáo

 

[TTRA]

xin cho toi hoi mot ty: toi muon lap mot ban tinh gom 2 trang a4 trong excel dong dau la hoten,ngay thang nam,....; khi sang trang thu 2 toi khong muon viet lai dong dau thi phai lam the nao ho cac bac. xin cam on cac bac truoc

 

[WhoamI]

xin cho toi hoi mot ty: toi muon lap mot ban tinh gom 2 trang a4 trong excel dong dau la hoten,ngay thang nam,....; khi sang trang thu 2 toi khong muon viet lai dong dau thi phai lam the nao ho cac bac. xin cam on cac bac truoc

Ý bạn muốn hỏi là khi in ra bạn muốn các dòng đầu được in lại sang các trang tiếp theo hay là chỉ muốn các dòng đầu không bị cuốn khi di chuyển trong sheet?. Nếu là ý thứ hai thì bạn có thể xem lại các bài viết trước về Hide, group, Freeze panes...Còn là ý thứ nhất thì không có excel trên máy nên mình chẳng nhớ là như thế nào cả, chỉ biết là vào File trên thanh Menu, chọn Page setup khi cs Page setup hiện ra bạn vào Sheet......(gì gì đấy.... bạn nào nhớ thì chỉ cho bạn TTRA với!

 

[handung107]

Bạn vào View/Header and Footer, khi đó trang Page Setup sẽ hiện ra, vào Sheet, chọn Print titles, chọn Rows to repeat at top, rồi đánh chọn vùng tiêu đề bạn muốn lặp lại ở đầu mỗi trang mới. Bạn phải vào View mới chọn được chức năng này, nếu vào Print Preview / Page Setup thì nút chọn trên sẽ bị chìm, bạn không sử dụng được

 

[VIP Professional]

Cũng thú vị nhỉ, Bảng tính Excel sang Việt nam lại trở thành "chương trình Kế toán" hay một "công cụ doanh nghiệp" nhỉ?
Có lẽ nó là cái cuốc cái xẻng thì đúng là không thể thiếu được, nhưng nếu gọi là công cụ kế toán có lẽ là hơi bị quá trong thời đại tin học hiện nay đấy...

 

[WhoamI]

Cũng thú vị nhỉ, Bảng tính Excel sang Việt nam lại trở thành "chương trình Kế toán" hay một "công cụ doanh nghiệp" nhỉ?

Có lẽ nó là cái cuốc cái xẻng thì đúng là không thể thiếu được, nhưng nếu gọi là công cụ kế toán có lẽ là hơi bị quá trong thời đại tin học hiện nay đấy...

Theo như mình nghĩ có lẽ cũng không nên nói nhiều về các vấn đề này ở đây. Chỉ nên quan tâm rằng có nhiều bạn muốn mở rộng kiến thức về excel để ứng dụng tốt hơn trong công tác kế toán của mình và nên để cho Topic này về đúng vị trí của nó, . Nếu bạn VIP Professional có thiện chí muốn mở rộng hiểu biết cho các Tv ở đây mong bạn có thể chia sẻ những hiểu biết của bạn về excel hay về bất cứ một lĩnh vực nào khác cũng là một cơ hội cho mọi người được học hỏi! Còn nếu bạn muốn thay đổi quan niệm của mọi người về excel thì mình nghĩ bạn nên có 1 bài viết rõ ràng và thuyết phục hơn chứ như mình đọc ở trên câu trước đá câu sau chả hiểu mô tê chi cả!

 

[handung107]

Bạn à, tôi chưa bao giờ nghĩ Excel có thể trở thành một phần mềm kế toán, nhưng đối với tôi Excel quả là một công cụ tuyệt vời và càng khám phá, tôi càng yêu thích nó. Tôi rất trân trọng tất cả những đóng góp của các bạn để chúng ta cùng hiểu biết thêm về Excel. Tôi đã từng vào khá nhiều trang Web về Excel, và tôi thấy rằng người ta đã áp dụng được rất nhiều những ứng dụng của Excel cho nhiều công trình nghiên cứu về kinh tế, kỹ thuật cùng nhiều lãnh vực khác. Tôi cũng đã từng biết nhiều kế toán trưởng của nhiều công ty lớn như Công ty Bia Sài Gòn, Tổng Công Ty Thương Mại Sài Gòn, bên cạnh những phần mềm kế toán riêng, họ đã sử dụng thành thạo Excel rất tuyệt vời và họ đã làm được khá nhiều việc, tiết kiệm khá nhiều thì giờ, công sức nhờ sử dụng thành thạo Excel. Khi tôi yêu thích Webketoan vì ở đây có một diễn đàn khá phong phú về Excel, tôi thấy chúng ta nên bắt đầu từ những việc tưởng chừng như đơn giản nhất là nâng cao trình độ về Excel, đừng nên coi thường nó. Tôi rất cám ơn các bạn vongphuc, cam_to, tuankctdcn, thanhnhan, WhoamI...vì các bạn đã cho tôi ít nhiều tìm hiểu kỹ hơn về Excel, kế toán đối với tôi chỉ là nghiệp dư, tôi phục vụ cho ngành Y, nhưng chuyên mục tin học ứng dụng cũng như Excel Fan Club là những đề tài tôi yêu thích nhất

 

[Tuanktcdcn]

To handung107: I muốn hỏi để biết thêm đấy chứ không phải ra đề đâu. I biết bạn có kiến thức rất tốt về Excel nên muốn bạn post bài lên cho mọi người được biết thêm.

 

[handung107]

Mình chỉ làm đệ tử cho các bạn thôi. Các bạn cứ ra những đề tài cho mọi người thảo luận đi, mình biết được gì sẽ cố gắng đóng góp. Tiện đây, mình post ít công thức mảng, mình không biết giải thích sao cho dễ hiểu, bạn có thể làm cho công thức đơn giản hơn thì giúp mình vơi
1.Tính tổng của N số hạng thấp nhất trong dãy A1:A60
{=SUM(SMALL(A1:A60,ROW(INDIRECT("1:N"))))}
2.Tính tổng của N số hạng cao nhất trong dãy A1:A60
{=SUM(LARGE(A1:A60,ROW(INDIRECT("1:N"))))}
3.Tổng những số hạng ở những hàng cách nhau :
{=SUM((A1:A60)*(MOD(ROW(A1:A60)-ROW(A1);2)=0))}
4.Tổng những số hạng ở những hàng thứ 5, 10, 15 của dãy :
{=SUM((A1:A60)*(MOD(ROW(A1:A60)-ROW(A1);5)=0))}
5.Tổng những số chẵn trong dãy :
{=SUM(IF(MOD(A1:A60;2)=0;A1:A60;0))}
6. Tổng những số lẻ trong dãy :
{=SUM(IF(MOD(A1:A60;2)<>0;A1:A60;0))}
7.Tổng những số hạng dựa trên điều kiện AND :
{=SUM(IF((A1:A60>50)*(B1:B60="HH01");C1:C60;0))}
Cách 2 :
{=SUM((A1:A60>50)*(B1:B60="HH01")*C1:C60;0)}
8. Tổng những số hạng dựa trên điều kiện OR :
{=SUM((A1:A60>50)*((B1:B60="HH01")+(B1:B60="HH02"))*(C1:C60))}
Hẹn các bạn lần sau ta tìm thêm một số thí dụ về công thức mảng

 

[quocnghia]

To handung107. Mình làm kế toán trên Excell, mình muốn các kế toán viên nhập liệu sẽ tự động chạy về dữ liệu ở một máy tổng hợp. Có cách nào không ? giúp mình với.

 

[Bình_OverAC]

to handung107:
Quá tuyệt vời kiến thức về Excel cua bác rất đáng khâm phục. Có lẻ em sẽ mượn kiến thức đó của bác nhé.
Mình muốn hỏi có ai biết về "conditional sum" không nhĩ. Đây là một công cụ thật tuyệt vời nó sẽ giúp bạn lập công thức mãng mà không cần phải suy nghĩ nhiều. Em bắt gặp nó trong office XP, một add-in

 

[Tuanktcdcn]

Phep tinh voi nhieu dieu kien

Mình đã dùng Conditional Sum đúng là rất tuyệt nó đã gợi ý cho mình xây dụng được nhiều ý tưởng khác. "Conditional Sum" có trên các loại EXCEL bạn chỉ cần vào Tools\Add-In...chọn nó là xong. Công cụ này cho phép bạn tính tổng trên nhiều điều kiện ở dạng "và" (AND) theo cấu trúc công thức mảng (Formula Array).
Minhf xin chỉ ra điểm yếu của "Conditional Sum". Nó chỉ thực hiện cho bạn một phép tính duy nhất là tính tổng (Sum) còn trung bình (Average), đếm (Count) thì không. Trong cấu trúc điều kiện tính của các bạn đôi khi có điều kiện "hoặc" (OR) thì "Conditional Sum" không có. VD tính tổng số tiền bên Nợ TK "111" hoặc "112" hoặc "131" và của nhóm khách hàng "GR001", biết TK "131" được mở chi tiết cho nhiều đối tượng thuộc nhóm. Nếu chỉ đơn thuần dùng "Conditional Sum" thì ta không làm được mà phải "chế biến" thêm theo cách của Formula Array.
Từ những hạn chế của "Conditional Sum" mình đã viết ra một công cụ A-Tools để giải quyết công tác kế toán tổng hợp, ngoài chức năng chính là tính toán nó còn làm nhiều việc khác (theo hướng dẫn bạn sẽ thấy).

Từ kết quả làm ra của mỗi người hy vọng mõi người lại có những sản phẩm ưu điểm hơn!

 

[Tuanktcdcn]

Fomula Array-Công thức mảng trên EXCEL

Hôm nay mình sẽ phân tích về công thức mảng, giải thích các phép toán quan trọng của EXCEL và cũng là thực hiện lời hứa của TuanKTCDCN trên một chuyên mục của Webketoan. Để hiểu tốt bạn nên kết hợp vừa đọc vừa làm ngay trên máy.

To handung107 8 công thức tính của bạn mình giải thích kết hợp với phân tích về công thức mảng như sau:

1) Giải thích các hàm

+ Hàm SMALL(VUNG,P): Cho ra giá trị ở vị trí P trong VUNG theo thứ tự từ thấp đến cao mặc dù VUNG chưa được sắp xếp.
VD A1=4,A2=3,A3=5
SMALL(A1:A3,3) = 5 (Vì nếu A1:A3 được sắp xếp tăng dần thì 5 sẽ ở vị trí thứ 3)
SMALL(A1:A3,2) = 4 (Vì nếu A1:A3 được sắp xếp tăng dần thì 4 sẽ ở vị trí thứ 2)
SMALL(A1:A3,1) = 3 (Vì nếu A1:A3 được sắp xếp tăng dần thì 4 sẽ ở vị trí thứ 1)
+ Hàm LARGE(VUNG,P): Cho ra giá trị ở vị trí P trong VUNG theo thứ tự từ cao đến thấp mặc dù VUNG chưa được sắp xếp.
VD A1=4,A2=3,A3=5
LARGE(A1:A3,3) = 3 (Vì nếu A1:A3 được sắp xếp giảm dần thì 3 sẽ ở vị trí thứ 3)

+ Hàm INDIRECT(TextRef) cho ra giá trị kiểu dạng Text thành dạng tham chiếu
VD INDIRECT("A1")=A1
Giả sử A1=4
Nếu bạn dùng SUM("A1") kết quả là [HASHTAG]#VALUE[/HASHTAG]!. Lỗi vì sai kiểu, đối số của SUM phải là dạng số hoặc các tham chiếu.
SUM(A1) = 4 đúng vì đúng kiểu
SUM(INDIRECT("A1")) = 4 đúng vì INDIRECT("A1") =A1, lúc đó EXCEL hiểu là SUM(A1)=>đúng kiểu.
+ Hàm ROW([THAMCHIEU]) nếu không có THAMCHIEU ROW() = dòng con trỏ hiện thời, còn nếu có sẽ cho ra dòng của tham chiếu ROW(A5)=5, ROW(2:2)=2, ROW(5:5)=5.
+ Hàm MOD(SCHIA,SBCHIA). Trả về giá trị 0 khi phép chia là chẵn,<>0 nếu lẻ (ứng dụng để tìm năm nhuận).
VD: MOD(6,3)=0, MOD(7,3)=1, MOD(8,3)=2

2) Công thức mảng
Qua một ví dụ nhỏ bạn sẽ hiểu qua về Formula Array
Giả sử cột A là SLG, B là DGIA. Cần tính tổng giá trị (Tổng thành tiền)
A1=4;B1=2
A2=3;B2=3
A3=5;B3=2
=SUM(A1:A3*B1:B3) Nhấn CTRL+SHIFT+ENTER
{=SUM(A1:A3*B1:B3)}=27 (4*2+3*3+5*2=27)
Theo cách phân tích của Formula Array thì EXCEL sẽ chạy con trỏ lần lượt tự dòng đầu tiên trong vùng dữ liệu A1:B1 đến dòng cuối của vùng là A3:B3. Mỗi một dòng khi con trỏ chạy qua Excel thực hiện công thức A1*B1, đến dòng thứ 2 là (A1*B1)+(A2*B2) và đến dòng thứ 3 là (A1*B1)+(A2*B2)+(A3*B3). EXCEL sẽ thực hiện tính từng dòng trong CSDL theo công thức chỉ định và tích luỹ lại theo phép tổng (SUM) và ra kết quả.

Nếu bạn không dùng tổ hợp phím CTRL+SHIFT+ENTER thì gí trị ở cell sẽ là [HASHTAG]#VALUE[/HASHTAG]! vì sai kiểu.
Trên chính là phân tích về Fomula Array mà EXCEL thực hiện. Từ đây chúng ta sẽ tự lập cho mình những công thức tính có kết hợp nhiều diều kiện, trong khi bạn lập bạn nên có cách tư duy như: tính tổng vùng TIEN nếu như NGAY là Thứ 7, công thức sẽ là SUM(IF(WEEKDAY(NGAY)=7,TIEN,0)).
Với công thức trên EXCEL sẽ cộng số TIEN ở dòng tương ứng là Thứ 7, nếu không phải cộng 0. Bạn cứ hình dung trình tự khi EXCEL phân tích công thức trên từng dòng và tích luỹ lại theo phép toán tuỳ vào bạn dung hàm gì bạn sẽ hiểu. Trong VD tren là tổng vì dung hàm SUM bên ngoài.

3) Phép tính Logic
"Trái" so sánh với "Phải"
So sánh có thể là <,>,<>,=,>=,<=, Not
2>3=FALSE
3<5=TRUE

TRUE=1
FALSE=0
TRUE+TRUE+....=1
TRUE*FALSE*....=0
(2>3)+(3<5)=FALSE+TRUE=TRUE=1 TĐ OR((2>3),(3<5))
(2>3)*(3<5)=FALSE*TRUE=FALSE=0 TĐ AND((2>3),(3<5))


4) Giải thích các bài của Handung107
Các công thức trên đều là Formula Array
Bạn đọc kỹ 3 phần trên bạn sẽ dễ hiểu!

1.Tính tổng của N số hạng thấp nhất trong dãy A1:A60
{=SUM(SMALL(A1:A60,ROW(INDIRECT("1:N"))))}
Bạn xem lại 2 hàm SMALL và INDIRECT.

Giả sử cho N=3 có nghĩa là sẽ tính tổng của 3 số thấp nhất trong vung A1:A60
Khi EXCEL tính dòng 1 sẽ là (SMALL(A1:A60,ROW(INDIRECT("1:1"))))=Số thấp thứ 1
Khi EXCEL tính dòng 2 sẽ là: Số thấp thứ 1+(SMALL(A1:A60,ROW(INDIRECT("2:2"))))=(Số thấp thứ 1+Số thứ thứ 2)
Khi EXCEL tính dòng 3 sẽ là (Số thấp thứ 1+Số thứ thứ 2)+(SMALL(A1:A60,ROW(INDIRECT("3:3"))))=(Số thấp thứ 1+Số thứ thứ 2+Số thứ thứ 3)
Đó chính là tổng của 3 số hạng thấp nhất. Nếu là N EXCEL sẽ chạy từ dòng 1 đến dòng N và tính như trên.
(Số thấp thứ 1+Số thấp thứ thứ 3
Tại sao A1:A60 vẫn giữ nguyên? Vì đối số đầu của SMALL phải là vùng SMALL(VUNG,P)

2.Tính tổng của N số hạng cao nhất trong dãy A1:A60
{=SUM(LARGE(A1:A60,ROW(INDIRECT("1:N"))))}
Tương tự như 1) thay SMALL=LARGE =>ngược lại

3.Tổng những số hạng ở những hàng cách nhau :
{=SUM((A1:A60)*(MOD(ROW(A1:A60)-ROW(A1);2)=0))}
ROW(A1)=1 (đã giải thích)
Theo cách phân tích của Formula Array EXCEL thực hiện công thức từng dòng 1->60 như sau:
Dòng 1 là (A1)*(MOD(ROW(A1)-ROW(A1),2)=0) Kế quả= A1*0=0
Dòng 2 là (A2)*(MOD(ROW(A2)-ROW(A1),2)=0) Kế quả= A2*1=A2
Dòng 3 là (A3)*(MOD(ROW(A3)-ROW(A1),2)=0) Kế quả= A2*0=0
....
Dòng n là (An)*(MOD(ROW(n)-ROW(A1),2)=0) Kế quả= An*0=An (nếu N chẵn)
EXCEL tính tổng và tích luỹ mỗi dòng=0+A2+0+...+0/An
Câu 4,5,6 giải thích tương tự
Trong hàm MOD(SCHIA,SBCHIA) bạn thay SBCHIA thành 5 hay 0 sẽ có ýa nghĩa khacks nhau.

Câu 7,8 là phép tính có nhiều điều kiện (từ 2 ĐK trở lên) bạn vận dụng phép tính Logic
Lg1+Lg2+..+Lgn=OR(Lg1,Lg2,..,Lgn)=TRUE khi ít nhất 1 Lg =True
Lg1*Lg2*..*Lgn=AND(Lg1,Lg2,..,Lgn)=TRUE khi tất cả Lg =True

Trong Formula Array không cho phép bạn dùng tuỳ tiện AND, OR và NOT

7.Tổng những số hạng dựa trên điều kiện AND :
{=SUM(IF((A1:A60>50)*(B1:B60="HH01");C1:C60;0))}
Tính tổng các giá trị trong vùng C1:C60 nếu như giá trị dòng tương ứng của vùng A1:A60>50 và B1:B60="HH01"
Phân tích theo EXCEL
Dòng 1 là If((A1>50)*(B1="HH01"),C1,0))=C1 nếu (A1>50)*(B1="HH01")=TRUE, 0 nếu FALSE
Dòng 2 là If((A2>50)*(B2="HH01"),C2,0))=C2 ........
.....
Dòng 60 .......

Vì hàm bọc ngoài là SUM nên EXCEL sẽ tính tổng tích luỹ lại khi sang dòng mới.

Cách 2 :
{=SUM((A1:A60>50)*(B1:B60="HH01")*C1:C60;0)}

Dòng 1 là If((A1>50)*(B1="HH01")*C1,0))=1*C1 nếu (A1>50)*(B1="HH01")=TRUE, 0*C1 nếu FALSE
Dòng 2 là If((A2>50)*(B2="HH01"),C2,0))=C2 ........

8. Tổng những số hạng dựa trên điều kiện OR :
{=SUM((A1:A60>50)*((B1:B60="HH01")+(B1:B60="HH02"))*(C1:C60))}
Cách giải thích như câu 7

Trong công thức, phần ngăn cách đối số có máy là ( có máy là (,).
VD IF(Tien>100;"Thưởng";"Không")
Chuẩn phải là IF(Tien>100,"Thưởng","Không")
Các bạn vào Control Panel\Regional Setting\Number chỉnh List, thoát khỏi Excel , mở lại là OK

Chúc các bạn có thêm những sáng tạo để thêm sự hăng say công việc!