Excel và những vấn đề bạn ít biết

[handung107]

Hàm N(value) ?

Bạn đã dùng hàm này bao giờ chưa ? Hàm này chuyển những số (dạng Text) thành giá trị (number), chuyển dạng Date thành dạng số (******), dạng Logic True/False thành dạng số 1/0, còn lại chuyển thành số 0
Như vậy, trong cấu trúc của hàm SUMPRODUCT, còn có thể sử dụng hàm N(value) như sau :
=SUMPRODUCT((N(array1=condition1),N(array2=conditional2),(array3))
cũng cho kết quả giống với các cấu trúc khác của hàm SUMPRODUCT này

 

[nmh_phocu]

To Handung107:
Mình có điều này muốn hỏi :
Mình cũng sử dụng Excel để làm công việc kế toán và mình cũng tạo ra được một cái tạm gọi là kế toán trên excel, nhưng có một việc mình chưa giải quyết được. Mình sử dụng một bảng nhập liệu, khoảng 10.000 lần nhập, xác định các công thức để đi đến các báo cáo, mình sử dụng rất nhiều hàm. Vấn đề xảy ra là dữ liệu nhiều thì máy tính chạy chậm, mình nghĩ là có phương pháp để giải quyết được vấn để này nhưng do trình độ còn kém nên không biết, bạn có thê cho mình một số phương pháp để giải quyết vấn đề này trên Excel được không?

 

[Đào Việt Cường]

Dear nmh_phocu,
-----------------
Mình xin góp ý nhé!
Chia hệ thống của bạn thành các module nhỏ, đừng làm tất cả trên 1 Workbook. Các tập tin Dữ liệu để riêng, tập tin xử lý tổng hợp để riêng - cái này giống như là các ứng dụng windows vậy. Hơn nữa, chắc bạn cũng tính đến dữ liệu của nhiều kỳ, bạn thấy rằng quản lý dữ liệu độc lập sẽ gọn hơn. Một trong các nguyên nhân gây ra chậm máy là MS Excel (MSE) luôn calculate tất cả các hàm mặc dù bạn không edit ô có chứa hàm. Điều này đặc biệt xảy ra nếu như bạn sử dụng các User defind function. Nếu bạn tổ chức thành các module riêng thì quá trình tính toán sẽ được hạn chế đáng kể, mặt khác nó thể hiện tính chuyên môn hơn.
Bạn cũng nên tận dụng chức năng Templete của MS Excel để thiết kế các báo cáo động theo mẫu sẵn như: Sổ cái tài khoản, sổ kế toán chi tiết... Với template, bạn tiết kiệm được phần lớn không gian lưu trữ dữ liệu vì không phải lưu trữ những báo cáo tĩnh!
Đôi điều góp ý cùng bạn, hi vọng sẽ được cùng bạn xây dựng hệ thống gọn nhẹ và hiệu quả!

 

[nmh_phocu]

Cảm ơn bạn mình đã hiểu được phần nào.
Minh xin trình bày kỹ một chút phương pháp minh làm.
Mình chia phần của mình ra làm ba phần mỗi phần là một file khác nhau:
Thứ nhất : Phần nhập liệu - tất cả các người dùng đều nhập dữ liệu trực tiếp vào bảng này, chính bảng này là bảng phải có số lượng dữ liệu nhiều nhất (10.000 lần nhập). phần này mình cho vào một file excel.
Phần thứ 2 : là phần báo cáo : bao gồm bảng cân đối, các bảng biểu tổng hợp ......( có thể cho vào nhiều file khác nhau nhưng nguồn dữ liệu là file nhập liệu ở trên) Mình dùng các hàm trong excel để làm chuyện này.
Vấn đế gặp phải :
- Khi muốn mở các báo cáo, mình phải mở file dữ liệu ra trước. ( dữ liệu sẽ rất nhiều )
- Khi sử dụng các chức năng trong file báo cáo ( lọc...) thì phải chờ máy tính tính toán lại rất lâu vì máy tính lại phải tính toán lại từ đầu
- Khi thay đổi dữ liệu dù rất nhỏ, máy tính cũng phải tính toán lại từ đầu.

Mình thấy bạn nói là có báo cáo động, thực sự là mục này mình không biết, bạn có thể chỉ dẫn để mình hiểu nó được không ( mình thực sự rất cần)
Xin trân trọng cảm ơn trước
-

 

[nmh_phocu]

Theo www.decisionmodels.com thì khi bạn tính tổng nhiều điều kiện :

1/ Sử dụng Conditional Sum Wizard : Đây là một add-in của Excel và là cách dễ dàng nhất : 744 giây trên máy laptop 500MHz

{=SUM(IF($A$4:$A$10003=$A4,IF($L$4:$L$10003="AA",$D$4:$D$10003),0))}

2/ Sử dụng công thức mảng :

{=SUM(($A$4:$A$10003=$A4)*($L$4:$L$10003="AA")*($D$4:$D$10003))}

Công thức này tốn khoảng 723 giây, vẫn còn quá chậm

3/Sử dụng hàm SUMPRODUCT :

=SUMPRODUCT(($A$4:$A$10003=$A4)*($L$4:$L$10003="AA")*($D$4:$D$10003))

Nhanh hơn công thức mảng, cần khoảng 711 giây

4/ Nếu bạn đặt dữ liệu tại một Sheet khác và thực hiện hàm SUMPRODUCT tại Sheet khác, kết quả tính toán sẽ giảm xuống còn 89 giây

=SUMPRODUCT((Data!$A$4:$A$10003=$A4)*(Data!$L$4:$L$10003="AA")*(Data!$D$4:$D$10003))

5/Sử dụng dãy động :

Bạn thử đặt tên cho các dãy Data!$A$4:$A$10003, Data!$L$4:$L$10003, Data!$D$4:$D$10003, và đây là các Dynamic Range, công thức tính toán sẽ giảm còn 0.673 giây.

Chẳng biết thế nào, vì tôi chưa kiểm chứng, bạn nào đã thử rồi xin cho biết, nhưng qua đây, chúng ta có một sự so sánh nhỏ cho các trường hợp để hiểu thêm vấn đề làm cách nào để Excel có thể tính toán nhanh hơn, phải không các bạn ?

Em thực sự quan tâm đến vấn đề số 5, sử dụng dãy động, làm ơn hướng dẫn em cái
Em cảm ơn nhiều nhiều

 

[Tran Chau]

Cho mình hỏi chút xíu
Có cách nào move 1 cột trong excel đến vị trí trước/sau đó 2 cột không ?
(vẫn đảm bảo không thay đổi công thức ở các ô trong cột đó sau khi di chuyển)

Trong ASAP,Pup... có lệnh di chuyển Formulas mà không làm thay đổi ref dựa vào ý cơ bản: Cells(1,1).Formulas=Cells(1,2).Formulas mà xào nấu,gia vị thêm

 

[Tran Chau]

...làm ba phần mỗi phần là một file khác nhau:
Thứ nhất : Phần nhập liệu - tất cả các người dùng đều nhập dữ liệu trực tiếp vào bảng này, chính bảng này là bảng phải có số lượng dữ liệu nhiều nhất (10.000 lần nhập). phần này mình cho vào một file excel.
Phần thứ 2 : là phần báo cáo : bao gồm bảng cân đối, các bảng biểu tổng hợp ......( có thể cho vào nhiều file khác nhau nhưng nguồn dữ liệu là file nhập liệu ở trên) Mình dùng các hàm trong excel để làm chuyện này.
Vấn đế gặp phải :
1/-Khi muốn mở các báo cáo, mình phải mở file dữ liệu ra trước. ( dữ liệu sẽ rất nhiều )
2/-Khi sử dụng các chức năng trong file báo cáo ( lọc...) thì phải chờ máy tính tính toán lại rất lâu vì máy tính lại phải tính toán lại từ đầu
3/-Khi thay đổi dữ liệu dù rất nhỏ, máy tính cũng phải tính toán lại từ đầu.

-

Theo I suy nghĩ ( chỉ suy nghĩ thôi !:wall: )
Về 1/-Dùng cách láy số liệu trong file dữ liệu mà không phải mở nó ra, đã có nói đâu đó trong Excel Club: dùng dạng công thức 3D, macro4, ADO,MS Query...
2/-Không dùng ( lọc...) nửa mà dùng vba, do..loop.. để scan + tính ra value ra các giá trị cần kiếm.Hoặc chứa công thức + address gốc vào chõ khác,trong khi lọc cần formula nào thì chép ngược lại
3/-Cungx gioongs như 2/- (chứa công thức + address gốc...) or dùng Range.Calculation

 

[Đào Việt Cường]

Mình chia phần của mình ra làm ba phần mỗi phần là một file khác nhau:
(...)

dear nmh_phocu,
---------------
Như vậy có thể coi việc tổ chức dữ liệu của bạn là đáp ứng yêu cầu của bạn. Vấn đề là làm sao để cải thiện tốc độ. Theo như mô tả thì có vẻ như dữ liệu của bạn rất nhiều. Cứ cho là hệ thống của bạn phải làm việc với đa số các hàm. Bạn có thể tham khảo rất nhiều bài viết liên quan đến vấn đề tối ưu. Ở đây tôi chỉ xin phép đề cập một số khía cạnh mà tôi được biết:
- Nếu bạn tổ chức cơ sở dữ liệu dạng phân tán (có nghĩa là chia ra nhiều workbook), bạn sẽ gặp vấn đề là làm sao các module chuẩn dùng chung và tham chiếu dễ dàng đến các đối tượng thuộc các workbook khác nhau. Tôi đưa tất cả các module vào một workbook. Workbook này có tác dụng như là màn hình điều khiển như: chọn in báo cáo, xem và hiệu chỉnh các tham số, tuỳ chọn, thiết lập (kỳ kế toán, thông tin về công ty và các thiết lập giao diện...). Tôi gọi workbook này là workbook nguồn. Nó làm tham chiếu (reference) cho các workbook khác (tập tin dữ liệu và báo cáo). Khi mở báo cáo, workbook nguồn cũng được mở theo (muốn cho người dùng không biết thì bạn cho nó Visible = False đi).
- Nếu bạn cảm thấy việc tham chiếu là bất tiện, cách này sẽ giúp bạn làm việc trong chế độ "offline":
Các workbook dữ liệu bạn cho nhập bình thường. Đối với mỗi workbook báo cáo, bạn bổ sung một sheet để import dữ liệu từ workbook nguồn. Theo tôi, công cụ phù hợp là Database Query. Bạn sẽ dùng Query này làm tham chiếu cho báo cáo hoặc các xử lý của bạn - muốn cập nhật lại chỉ cần refresh. Tốc độ sẽ cải thiện đáng kể. Nhược điểm của cách này là một lần nữa bạn phải lưu trữ lại những gì mà bạn đã lưu trữ ở tập tin dữ liệu.
- Việc tối ưu hoá các hàm và thủ tục luôn là phương pháp được khuyến khích. Bạn kiểm tra lại việc tổ chức cơ sở dữ liệu như vậy đã được tối ưu chưa. Kinh nghiệm cho thấy hiện tượng calculate xảy ra khi mà bảng tính chứa quá nhiều công thức mảng, công thức lặp hoặc lồng nhau,... Bạn sẽ cảm thấy hài lòng nếu như một ô nào đó sau mỗi lần edit vẫn không làm cho MSE calculate đến mức có thể cảm nhận được. Bằng không, hãy để Calculation = Manual và chỉ cho Automatic khi thực sự cần thiết.
Cuối cùng, bàn về "báo cáo động" thật ra nó không có gì xa lạ. Giải pháp của tôi là:
+ Nhờ AdvandceFilter lọc ra theo điều kiện của tôi muốn rồi đổ lên sheet TEMP
+ Hiệu chỉnh temp phù hợp với báo cáo được thiết kế sẵn trong template report
+ Đổ dữ liệu đã hiệu chỉnh lên templete report
-> thử nghiệm tiến trình đối với một sổ cái khoảng 5000 dòng mất không đến 1/2 giây, có thể chấp nhận được.
Chúc bạn thành công!

 

[nmh_phocu]

Cảm ơn Đào Việt Cường
Mình thấy chị Handung107 nói về cái dãy động, mình không biết là nếu áp dụng có thành công với mảng dữ liệu lớn như vậy?
Cố gắng giúp mình với

Và có thê thì Cường có thê dạy mình về dãy động với

 

[HongViet]

DL rất nhiều - Khi sử dụng các chức năng trong file báo cáo ( lọc...) thì phải chờ máy tính tính toán lại rất lâu vì máy tính lại phải tính toán lại từ đầu
- Khi thay đổi dữ liệu dù rất nhỏ, máy tính cũng phải tính toán lại từ đầu.

Trong Excel bạn nên trách dùng các hàm khả biến; các hàm này sẽ tính toàn lại tất thẩy khi có 1 dao động nhỏ trong 1 ô excel; Cách tránh thì đã có ghi trong quyển Notebook 100 thủ thuật nâng cao trong Excel; bạn tìm trong mục này (& ở các chương 1,6 gì đó!)
Một í nhỏ xin góp như sau: Biến tất cả ~ ô chứa ngày tháng năm thành mã ngày tháng năm VD thay vì 22/12/2005 là 5QQ (có nghĩa là dùng 3 kí tự gồm cả kí số cho tất cả các ngày trong thế kỉ này)
Chúc vui vẽ nhân dịp xuân về!

 

[nmh_phocu]

Cảm ơn tất cả các bạn, mình đã áp dụng các phương pháp được chỉ giáo, nhất là đặt tên cho mảng và hàm sumproduct và cảm thấy tuyệt vời, tính toán dữ liệu được cải thiện rất nhiều, một lần nữa xin trân trọng cảm ơn.
He he, mình nẩy ra ý kiến :
Mình rất phục các bác cùng VBA, mình cảm thấy cực hay, nhưng thú thật là không thể học nổi và mình chắc đa số các bác các anh các chú ở đây cũng chẳng học nổi, không học nổi suy ra phải dùng các phương pháp khác để tiếp cận vấn đề muốn giải quyết. Liệu có thể mở ra một chủ để " Excel không VBA" được không, mọi người đưa vấn đề lên và yêu cầu các người khác giải quyết mà không dùng VBA, ví dụ nhỏ là trường hợp của em vậy. Em nghĩ là số lượng người không biết gì về VBA rất nhiều nhiều và mọi người có thể nâng cao trình trước khi muốn gia nhập thế giới VBA.
Rửa tai lắng nghe ý kiến phản hồi

 

[mysterious_girl]

Liệu có thể mở ra một chủ để " Excel không VBA" được không, mọi người đưa vấn đề lên và yêu cầu các người khác giải quyết mà không dùng VBA, ví dụ nhỏ là trường hợp của em vậy. Em nghĩ là số lượng người không biết gì về VBA rất nhiều nhiều và mọi người có thể nâng cao trình trước khi muốn gia nhập thế giới VBA.
Rửa tai lắng nghe ý kiến phản hồi

Bạn ơi,
Box Excel và VBA là box nhấn mạnh việc vận dụng VBA trong excel. Các box còn lại:Excel tổng quát ,box Excel chuyên đề và box Excel và kế toán bạn có thể tìm hiểu về các hàm, công thức... của excel mà ko dùng VBA. Vậy thì lập ra box " Excel không VBA" là thừa bạn à.

Sắp tới EFC dự định sẽ mở lớp: Chỉ trong 5 buổi học, bạn sẽ có thể phát triển ứng dụng cho mình bằng VBA

 

[nmh_phocu]

Bạn ơi,
Box Excel và VBA là box nhấn mạnh việc vận dụng VBA trong excel. Các box còn lại:Excel tổng quát ,box Excel chuyên đề và box Excel và kế toán bạn có thể tìm hiểu về các hàm, công thức... của excel mà ko dùng VBA. Vậy thì lập ra box " Excel không VBA" là thừa bạn à.

Sắp tới EFC dự định sẽ mở lớp: Chỉ trong 5 buổi học, bạn sẽ có thể phát triển ứng dụng cho mình bằng VBA

Cảm ơn bác nghe, em đồng ý cái rụp
Em xin hỏi một chiêu nữa; đó là em muốn rằng khi em vào cái file excel của em, cái thanh phân ô, A,B,C...hàng ngang và các các thanh 1,2... phân ô hàng dọc, em chẳng biết nó là cái gì nữa sẽ tự động biến mất khi em vào cái file của em, hihi cố gắng không hướng dẫn em sử dụng bằng VBA nhé, nếu thực sự không có cách khác thì cho em luôn cái mà các bác làm sẵn rồi ý. Để bu nó nhà em không trách em ngu dốt về "ếch xeo"

 

[mysterious_girl]

Em xin hỏi một chiêu nữa; đó là em muốn rằng khi em vào cái file excel của em, cái thanh phân ô, A,B,C...hàng ngang và các các thanh 1,2... phân ô hàng dọc, em chẳng biết nó là cái gì nữa sẽ tự động biến mất khi em vào cái file của em, hihi cố gắng không hướng dẫn em sử dụng bằng VBA nhé, nếu thực sự không có cách khác thì cho em luôn cái mà các bác làm sẵn rồi ý.

Cái đó là row & column headers. Bạn vào tools/view và bỏ chọn check box :row & column headers, sau đó save file của bạn lại.Như vậy khi bạn mở lên sẽ ko thấy nữa.

Chúc bạn thành công và đừng câu bài nữa bạn nhé!!!

 

[handung107]

Nếu trên một sheet của bạn có những lỗi như [HASHTAG]#NAME[/HASHTAG]?, [HASHTAG]#REF[/HASHTAG]!, #N/A, #DIV/0..., và bạn chưa kịp xử lý những lỗi này, nhưng bạn vẫn muốn khi in, trang bảng tính của bạn không chứa lỗi, bạn có thể làm như sau :
- Vào Menu File / Page Setup
- Chọn thẻ Sheet - Print
-Tại ComboBox Cell error as, bạn có thể chọn : blank (khi in, những lỗi này sẽ không thấy) hay (--, khi in, những lỗi này hiển thị bằng 2 gạch ngắn).

 

[thanhlongcom]

Thường khi muốn sửa dữ liệu trực tiếp trên ô, bạn chỉ cần Double Click vào ô đó, con trỏ chuột sẽ hiện diện và giúp bạn thực hiện việc sửa chữa này.
Muốn vô hiệu hóa chưc năng này, bạn hãy chọn Tool/Options, chọn Edit. Trong mục Edit, bỏ chọn mục Edit directly in Cell.
Bây giờ, nếu bạn cho ẩn thanh Formula Bar, thì người khác sẽ gặp khó khăn trong việc chỉnh sửa dữ liệu đấy

Bác handung107 ah, tôi thấy cái việc chọn Edit directly in Cell này cũng có cái hay(VD: khi Ô Gốc là địa chỉ của 1 cell trong 1 sheet khác, khi double click vào ô công thức, nó sẽ chuyển trực tiếp sang cell nguồn - gần giống như 1 dạng link). Tuy nhiên, nó chỉ áp dụng được khi sử dụng trên cùng 1 máy tính, nếu chuyển sang máy tính khác, nó bị vô hiệu hóa vì máy tính khác không chọn chế độ Edit directly in Cell.

 

[LeHoan82]

Trích nguyên văn:
Nguyên văn bởi ketoan4mat
Đâu cần fải Double click mới chỉnh sửa dữ liệu được, đưa con trỏ vào ô cần chọn rồi bấm F2 thì sẽ chỉnh sửa được mà. Nên việc bỏ chọn mục Edit directly in Cell trong trường hợp này ko còn tác dụng.

Mình đã thử làm và mặc dù giấu thanh Formula nhưng khi nhấn F2 vẫn chỉnh sửa được dữ liệu

 

[conmeotobeo]

Cách tính tuổi học viên

Mình đã tính tuổi trên Excel, và đã cho kết quả như bên dưới(dùng hàm datedif) nhưng mình o hiểu sao có những người cùng năm sinh nhưng sinh nhật khác nhau lại tính tuổi khác nhau. Mình muốn biết dùng hàm nào để tính tuổi của những người cùng năm sẽ bằng tuổi nhau và tính theo thời điểm hiện tại là năm 2008.Giúp mình với!


STT Họ tên Ngày sinh Tuổi
1 Hồng Anh 12-08-84 23
2 Ngọc Lan 24-12-86 21
3 Thuỷ Sinh 14-11-83 24
4 Trần Ngọc 12-04-83 25
5 Hồng Nhung 12-03-87 21
6 Nhữ Đình Tạo 14-10-88 19
7 Cao Văn Phếng 24-05-76 31
8 Trần Tùng 06-12-83 24
9 Bùi Hiếu 12-09-78 29
10 Phạm Khanh 06-08-89 18
11 Nguyễn Hoà 18-10-89 18

 

[tran ngan ha]

bạn nào giỏi làm kế toán trên excel có thể cho mình tài liệu hướng dẫn làm kế toán excel ko? mình chỉ biết excel đơn giản chứ chưa biết áp dụng vào kế toán

 

[sieucoi]

cac bac oi! day em excel vơi! e chẳng biết tý ty nào.Em học hơi kém nên nếu mờ đi ra trung tâm,học đông thì em chẳng tiếp thu được tỵ nào.Mà về nhà thì hem có ai kèm cặp.Khổ đến mấy em cũng học.Em học kế toán nhưng chẳng bít gì về kế toán nốt nữa.huhu