Thủ thuật Excel

[fcvnn]

Tôi biết đấy, cò gì bạn liên hệ với tôi nhé! :thank

 

[kebattai]

chào Bác VN geek:
em đã đọc bài tông hợp sô phát sinh của Bác nh­ung trong Use Label của em chỉ có:
Top row và left colum là sao Bác nhi ?

 

[lechi1]

Đánh dấu mũ trong exel

Gui ban HyperVN
Sao mình không làm được nhỉ ? Sau khi làm như bạn xong để con chuột ra chỗ khác là đâu lại hoàn đấy. Mình còn quên làm cái gì thế ?

 

[Bình_OverAC]

Excel 2000 có trò chơi đua xe , Excel 97 có trò bắn máy bay , nhưng làm thế nào để vào được những games này ? AI BIẾT CHỈ DÙM .

Vụ này được ketoan4mat đã biết được chưa vậy?
Biết rồi thì chỉ cho bà con cung biết với nhé.

 

[Tuanktcdcn]

Tập hợp phát sinh công nợ

Sheet DATA có các cột: NGAY, DVKH,NOTK, COTK, S_TIEN

Bạn có thể tính cho mình cái này nhé

Tính tổng S_TIEN bên nợ TK 131 của khách hàng "KH001" hoặc "KH002" và trong thời gian từ ngày 01/09/04 đến 10/09/04.

Rất mong nhận được câu trả lời!

 

[thanhnhan53]

=sum(if(ngay=<01/09/04,if(ngay<=10/09/04,if(dvkh="KH001",if(notk=131,s_tien,0))))))
Nói tóm lại đây là công thức mãng. Tính tổng nhiều điều kiện. Không hiểu Tuanktcdcn đưa ra câu hỏi này có ý gì nhỉ?

 

[Tuanktcdcn]

To Thanhnhan53: Bản chất của câu hỏi cũng chỉ là giao lưu và trao đổi và học tập thôi chứ không có ý gì khác cả. Bạn có thể hoàn chỉnh hơn công thức đó được chứ? Vì còn thiếu điều kiện hoặc "KH002".

Mình cũng muốn nhấn mạnh là nếu những bạn dùng SumIf sẽ không bao giờ làm được những sổ kế toán với những đối tượng theo nhiều điều kiện, chỉ đơn giản tính tổng S_TIEN trong thời gian từ ngày 01/09/04 đến 10/09/04 SumIf sẽ không làm được chứ không nói gì tới việc lập sổ tổng hợp với nhiều mã Tài khoản của những mã hàng, mã công nợ trong một khoảng thời gian mà điều này hết sức quan trọng.
Các bạn có thể dùng các hàm DATABASE (DSUM,DCOUNT,...) nhưng điểm yếu là với mỗi một mã lại phải có một vùng điều kiện. Tóm lại dùng chúng thì bạn khó copy tới các mã khác.

 

[handung107]

Theo mình nghĩ công thức mảng với các phép toán logic OR thí dụ dvkh="KH001" hoặc "KH002" thì công thức mảng có thể như sau :=sum((ngay=<01/09/04)*(ngay<=10/09/04)*if((dvkh="KH001")+(dvkh="KH002"),1,0)*(notk=131)*s_tien)
hoặc =sum((ngay=<01/09/04)*(ngay<=10/09/04)*if(mod((dvkh="KH001")+(dvkh="KH002"),2),1,0)*(notk=131)*s_tien). Toán tử MOD ở công thức trên trả về 0 nếu hai điều kiện (KH001, KH002) đều đúng hay đều sai. Nó chỉ trả về 1 khi nào một trong hai điều kiện là đúng.

 

[thanhnhan53]

Không phải là Sumif mà Sum với nhiều if, xin mời xem kỷ lại bài viết. Còn muốn thêm "KH002", thì cứ việc thêm 1 if nữa, hihihihi, có sau đâu,nói tóm lại dùng hàm TÍNH TỔNG VỚI NHIỀU ĐIỀU KIỆN. Mình đã đọc nhiều bài của Tuanktcdcn, rất khâm phục sự hiểu biết của bạn, thế mà bạn đưa ra một vấn đề mình nghĩ không phải là thắc mắc thật sư, mà là . . . hơi bị ngac nhiên tí thôi.

 

[handung107]

Không phải là Sumif mà Sum với nhiều if, xin mời xem kỷ lại bài viết. Còn muốn thêm "KH002", thì cứ việc thêm 1 if nữa, hihihihi, có sau đâu,nói tóm lại dùng hàm TÍNH TỔNG VỚI NHIỀU ĐIỀU KIỆN. Mình đã đọc nhiều bài của Tuanktcdcn, rất khâm phục sự hiểu biết của bạn, thế mà bạn đưa ra một vấn đề mình nghĩ không phải là thắc mắc thật sư, mà là . . . hơi bị ngac nhiên tí thôi.

Mình rất đồng ý kiến với thanhnhan53. Này nhé, bạn chỉ cần thêm 1If nữa là sai đấy nhé, vì lồng If như thế chỉ trong trường hợp AND thôi, còn OR là không đúng đâu. Công thức đó mình cũng xin sửa lại một chút : [=SUM((DAY(Ngay)>=1)*(DAY(Ngay)<=10)*(IF((dvkh="KH001")+(dvkh="KH002"),1,0))*(NoTK=131)*S_tien)].

 

[WhoamI]

Mình cũng muốn nhấn mạnh là nếu những bạn dùng SumIf sẽ không bao giờ làm được những sổ kế toán với những đối tượng theo nhiều điều kiện, chỉ đơn giản tính tổng S_TIEN trong thời gian từ ngày 01/09/04 đến 10/09/04 SumIf sẽ không làm được chứ không nói gì tới việc lập sổ tổng hợp với nhiều mã Tài khoản của những mã hàng, mã công nợ trong một khoảng thời gian mà điều này hết sức quan trọng.
Các bạn có thể dùng các hàm DATABASE (DSUM,DCOUNT,...) nhưng điểm yếu là với mỗi một mã lại phải có một vùng điều kiện. Tóm lại dùng chúng thì bạn khó copy tới các mã khác.

Đọc bài này làm W nhớ lại lúc mới bắt đầu làm kế toán W luôn phải dùng custom trong Autofilter kết hợp với Subtotal hoặc dùng Dsum và sumif để lập tổng hợp bảng cân đối số phát sinh, sổ cái, sổ chi tiết .....mất rất nhiều thao tác và thường xuyên bị lệch thót cả tim! (thế mà lúc ấy cứ tưởng mình đã siêu...... Excel rồi). Nhưng từ khi biết được Công thức mảng bằng cách thêm CTRL+Shifl+enter vào mỗi CT có nhiều điện kiện -từ một em kế toán ( khoá sau cùng trường mới vào làm ở Công ty em) đã giải quyết được tương đối lớn các công việc kế toán. Lúc đó W thấy thật xấu hổ vì đã không biết tận dụng cơ hội học hỏi có từ trước đó vì em ấy và W là cùng 1 thầy dạy.

Có thể với bác ThanhNhan thì CTM có thể không còn quan trọng nữa vì nếu ai xem file của bác cũng hiểu điều này và rất khâm phục ý tưởng từ table sang Form của bác, nó giải quyết được rất nhiều CTác thủ công cho công việc của KTThuế. W nói thế mong bác TN đừng hiểu sai ý của em nhé, vì em nghĩ nếu có những bạn ở vào trường hợp của em lúc này 2 năm trước thì sẽ rất ..rất cám ơn Thầy Tuân và Bác ThanhNhan đấy ạ!

Chúc mọi người có 1 ngày cuối tuần và trung thu vui vẻ!

To Bác ThanhNhan: Nếu bác có thời gian rỗi mong bác sang góp sức với những người yêu mến và tin tưởng Excel tại Excel fans club . W có 1 ý tưởng là nếu File của bác có thêm các "comment động" (em tạm gọi là thế nhé) với font chuẩn cho biết thêm chi tiết về mã mỗi khi nhập một mã hàng, mã KH..... và có thêm các macro tự động in các phiếu Thu Chi Nhập Xuất ... và một số hoàn thiện khác ..thì chương trình của bác là số 1 từ ý tưởng đến chương trình đấy ạ!
Vài dòng thiển nghĩ mong các bác đừng chê cười em nhé!

 

[handung107]

Không phải là Sumif mà Sum với nhiều if, xin mời xem kỷ lại bài viết. Còn muốn thêm "KH002", thì cứ việc thêm 1 if nữa, hihihihi, có sau đâu,nói tóm lại dùng hàm TÍNH TỔNG VỚI NHIỀU ĐIỀU KIỆN. Mình đã đọc nhiều bài của Tuanktcdcn, rất khâm phục sự hiểu biết của bạn, thế mà bạn đưa ra một vấn đề mình nghĩ không phải là thắc mắc thật sư, mà là . . . hơi bị ngac nhiên tí thôi.

Mình rất đồng ý kiến với thanhnhan53 về ý nghĩ đối với câu hỏi của tuan . Nhưng với công thức của bạn, bạn chỉ cần thêm 1 If nữa là sai đấy nhé, vì lồng If như thế chỉ trong trường hợp AND thôi, còn OR là không đúng đâu. Công thức đó mình cũng xin sửa lại một chút : [=SUM((DAY(Ngay)>=1)*(DAY(Ngay)<=10)*(IF((dvkh="KH001")+(dvkh="KH002"),1,0))*(NoTK=131)*S_tien)].

 

[handung107]

Có một công thức tính tổng nhiều điều kiện cũng rất hay, nhưng không phải là công thức mảng, vì không cần bấm tổ hợp phím Ctrl+Shift+Enter, đó là công thức SumProduct. Cụ thể ví dụ trên ta có thể dùng như sau : =SUMPRODUCT((DAY(Ngay)>=1)*(DAY(Ngay)<=10)*(dvkh="KH001")*(NoTK=131)*S_tien). Tuy vậy, với công thức này mình cũng không vận dụng được phép tính OR dvkh="KH002". Bạn nào biết xin trao đổi tiếp về công thức này

 

[handung107]

À, cuối cùng thì cũng ra rồi. Công thức như sau : =SUMPRODUCT((DAY(Ngay)>=1)*(DAY(Ngay)<=10)*((dvkh="KH001")+(dvkh="KH002"))*(NoTK=131)*S_tien)

 

[Tuanktcdcn]

Cảm ơn Thanhnhan53 đã có đánh giá tốt nhưng xin đừng hiểu sai ý I! Câu hỏi của I không phải là I không làm được mà muốn làm rõ cấu trúc của công thức mảng mà thôi. Những trao đổi của chúng ta trong phần này là rất quan trọng để mỗi người nâng cao hơn ý tưởng của mình.
Công thức mảng (CTM) cho ta thống kê theo nhiều điều kiện, nhưng lại không tuỳ ý dùng các hàm được. Bình thường trong hàm IF chúng ta dùng AND, OR để lồng nhau nhưng riêng CTM lại không cho phép như vậy. Và cuối cùng chúng ta phải dùng hai phép toán + (OR); * (AND) để giải quyết.
Câu hỏi của I đã được Thanhnhan53 và Hanhung107 giải quyết xong rất cảm ơn các bạn đã đóng góp, các bạn là hai trong những người làm cho WKT được sôi động và chất lượng hơn, mong các bạn tiếp tục phát huy.

Phân tích của I về CTM sẽ được viết trong EXCEL Fans mời các bạn sang đó trao đổi thêm.

 

[WhoamI]

Tiện đây các bác cho em hỏi luôn:
Em muốn xây dựng CT sao cho khi em nhập số tháng và số năm sẽ ra cho ra kết quả là ngày cuối tháng đó không trùng thứ bảy, chủ nhật. Nếu ngày cuối tháng đó là T7. CN thì lấy ngày Tsáu.

 

[handung107]

To WhoAmI, công thức đó như sau :=DATE(YEAR(Ngay),MONTH(Ngay)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(Ngay),MONTH(Ngay)+1,0),2)-5)), trong đó Ngay sẽ là một cell chứa ngày tháng năm bạn cần biết thí dụ : 06/09/04.

 

[handung107]

Công thức trên WhoamI có thể nhập thẳng tháng, năm vào hàm . Thí dụ hàm :=DATE(2004,9+1,0) sẽ trả về ngày cuối tháng 30/09/2004. Nếu bạn không cộng thêm 1 mà chỉ nhập tháng là 9, hàm sẽ trả về ngày cuối tháng 31/08/2004. Hàm WEEKDAY(DATE(2004,10,0),2) sẽ cho ta biết ngày cuối tháng là thứ mấy trong tuần. Weekday(serial_number, return_type) . Serial_number là Date(2004,10,0). Còn return_type là 1 nếu quy định chủ nhật là 1--> thứ bảy là 7, return_type là 2 nếu quy định thứ hai là 1--> chủ nhật là 7, return type là 0 nếu quy định thứ hai là 1-->thứ bảy là 6.
Nếu tôi chọn weekday(date(2004,10,0),2) thì khi ngày cuối tháng rơi vào thứ 6, hàm sẽ cho kết quả là 5. Hàm Max(0,weekday(date(2004,10,0),2)-5) sẽ cho kết quả là 0 nếu ngày cuối tháng rơi vào thứ 2 đến thứ 6 trong tuần và cho kết quả là 1 nếu là thứ bảy, 2 nếu là chủ nhật. Do đó nếu ta chọn lùi lại 1 hoặc 2 ngày nếu ngày cuối tháng rơi vào thứ bảy hay chủ nhật thì bạn chỉ cần làm phép trừ ngày cuối tháng cho 1 hoặc 2 nữa là xong. Khi ấy hàm sẽ là ::=DATE(2004,10,0)-(MAX(0,WEEKDAY(Date(2004,10,0,2) -5)).

 

[WhoamI]

To WhoAmI, công thức đó như sau :=DATE(YEAR(Ngay),MONTH(Ngay)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(Ngay),MONTH(Ngay)+1,0),2)-5)), trong đó Ngay sẽ là một cell chứa ngày tháng năm bạn cần biết thí dụ : 06/09/04.

Hay quá! thế mà em loay hoay mãi không ra. Nhưng khi em chạy thử công thức của bác thì vẫn bị dính tháng 11 cho ra kết quả là ngày 30/11/2004 (thứ bảy). Bác thử lại xem.(công thức của Bác chưa lọai trừ ngày T7)
Ý tưởng trên xuất phát từ việc em muốn Exc tự động tính ngày in sổ, ngày ghi sổ của sổ chi tiết, sổ cái, bút toán kết chuyển, các bút toán điều chỉnh vào cuối tháng, cuối năm khi số tháng thay đổi chứ không phải kích đúp vào cái system time rồi phải gõ lại ngày tháng năm bằng tay. Tiện lợi ra phết đấy ạ! Cám ơn bác handung107 nhiều !

 

[Tuanktcdcn]

Xin đưa ra cách 2 các bạn tham khảo.

DATE(Năm, Tháng, Ngày)
DATE(2004,02, 15) = 15 / 02 / 2004
DATE(2004,02, 0 ) = 0 / 02 / 2004 = 31/01/2004 .Là ngày cuối của Tháng -1

Vì vậy, nếu muốn trả về ngày cuối tháng 02 thì phải Tháng +1 (02+1). Thực chất là việc bù trừ (02 +1) - 1=02
DATE(2004,02+1, 0 ) = 0 / 03 / 2004 = 29/02/2004 (Tháng -1)

A2=10/01/2004
Ngày cuối của tháng Month(A2) là: DATE(Year(A2), Month(A2)+1, 0)

Để giải bài toán trên tư duy bắt đầy như sau:

=Ngày cuối tháng -1 (nếu thứ của ngày cuối tháng là T7 ) hoặc -2 (nếu thứ của ngày cuối tháng là CN)
T7-1= CN-2 = T6

WEEKDAY(Ngay) = Ngày trong tuần: T7 là 7, CN là 1


Diễn giải theo CT như sau:

=DATE(Year(A2), Month(A2)+1, 0)
+IF(WEEKDAY( DATE(Year(A2), Month(A2)+1, 0) )=7,-1,0)
+IF(WEEKDAY( DATE(Year(A2), Month(A2)+1, 0) )=1,-2,0)

(Ngày đầu tháng này = ngày cuối của tháng trước)