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

[handung107]

Chuyển đổi công thức thành Text

Bạn đã biết để nhập một số dưới dạng Text, bạn sẽ đặt dấu nháy đơn trước khi nhập số vào một Cell, nhưng bạn cũng có thể chuyển đổi công thức thành Text bằng cách tương tự như vậy : Bạn hãy thêm dấu apostrophe (') trước dấu =. Bạn hãy thử xem nhé

 

[DucThuan]

Khi bạn tạo ra một côn gthức và muốn sao chép nó sang một ô khác, Excel sẽ điều chỉnh các tham chiếu của bạn cho phù hợp. Đôi khi bạn muốn giữ chính xác công thức, thông thường, bạn sẽ tạo những tham chiếu tuyệt đối, nhưng không phải lúc nào cũng như ý muốn. Một phương pháp tốt hơn là chọn công thức và sao chép nó sang Clipboard dưới dạng Text.
-Nhấp đôi Cell có chứa công thức hay nhấn F2 để nhập vào chế độ Edit
- Rê chuột để chọn toàn bộ công thức. Bạn có thể rê chuột từ phải sang trái hay từ trái sang phải
-Nhấp nút Copy. Nhấn Enter để kết thúc
-Chọn Cell cần sao chép
-Nhấp nút Paste để dán công thức vào. Bạn có thể vào Edit / Office Clipboard, bạn sẽ thấy công thức vừa rồi nằm sẵn trong hộp này. Nếu bạn muốn tiếp tục việc sao chép, bạn chỉ cần chọn Cell và Click chọn công thức trong Office Clipboard mà thôi

Trời hay quá nhỉ, trước đến giờ em mò hoài mà chẳng ra. Cám ơn chị nhiều.

 

[adam_tran]

Chị Hạnh Dung muốn rành Excel hơn cả BillGate nữa, bái phục know:
Xin góp 1 chút kẻo ... nhận hoài mà chẳng chịu góp!
Nếu dữ liệu dạng text bạn muốn chuyển sang dạng số, bạn chỉ việc cộng thêm 0 là xong. Thí dụ cột A chứa 1 dãy số dạng text, bạn muốn chuyển qua dạng số, có 1 cách khác là bạn qua cột B = Cột A + 0.
Tương tự, cột bạn có X1, X2, M2, N3, M15, ...
Bạn muốn cộng các giá trị của M, thí dụ M = 2+15 = 17. Dễ dàng lập công thức mảng với hàm SUM, IF, LEFT, REPLACE hoặc SUBSTITUTE.
=SUM(IF(LEFT(A1:A10,1)="M",REPLACE(A1:A10,1,1,"")+0, 0))

Lệnh Paste Special cũng rất hay ở điều kiện Operation, cho phép cộng - trừ - nhân - chia giá trị trong Clipboard với giá trị hiện có của ô cần dán vào!

 

[handung107]

Bàn về các màu của Excel

Bạn có thể ngạc nhiên khi biết rằng Excel rất giới hạn các màu. Một Workbook lưu trữ một bảng màu gồm 56 màu. Do đó, Workbook của bạn chỉ có thể sử dụng các màu chứa trong bảng màu đó. Tuy nhiên, bạn có thể thay đổi bất kỳ hoặc tất cả màu của bảng màu đó. Để thực hiện, bạn làm như sau :
1/Chọn Tools / Options
2/Trong hộp thoại Options, nhấp Tab Color
3/Nhấp một màu mà bạn muốn thay đổi, sau đó, nhấp Modify để hiển thị hộp thoại Colors.
4/Chọn một màu mới và nhấp OK để quay về hộp thoại Options
5/Tiếp tục chỉnh sửa các màu hoặc nhấp Ok để thoát
Lưu ý : Việc chỉnh sửa một màu sẽ ảnh hưởng đến tất cả các ô đang sử dụng màu đó làm nền
Bạn cũng có thể dùng tab Color của hộp thoại Options để sao chép bảng màu từ một Workbook khác sang Workbook hoạt động

 

[handung107]

Sử dụng các phần giao dãy

Mặc dù vấn đề này đã được nhắc đến trong bài "Tên và nhãn trong công thức", nhưng tôi sẽ nói cụ thể hơn tại đây
Đây là khái niệm thú vị độc đáo của riêng Excel : các phần giao dãy. Một phần giao dãy tham chiếu sang các ô mà 2 dãy có chung. Excel sử dụng một toán tử giao - một khoảng trống - để xác định các tham chiếu phủ chồng trong 2 dãy.
Td : Ta có mảng A16. Nếu ta thiết lập một công thức như sau tại Cell B9

B9 = B1:B6 A33 , sẽ cho kết quả là giá trị trong Cell B3 - nghĩa là phần giao của 2 dãy B1:B6 và A33.

Ta đặt tên cho 2 dãy này là Product và East chẳng hạn, công thức trên sẽ là : =Product West

Tóm lại :

-Dấu hai chấm : Xác định 1 dãy. Td : A1:A6
-Dấu phẩy (,) : Xác định hợp của 2 dãy . Toán tử này kết hợp nhiều tham chiếu dãy thành một tham chiếu đơn. Td :=SUM(A22,A33) sẽ cho tổng của các số hạng trong 2 dãy trên
-Khoảng trống : Xác định phần giao của 2 dãy. Toán tử này tạo ra các ô chung cho 2 dãy. Td : =SUM(A33 B1:C6) sẽ cho tổng các Cell giao giữa 2 dãy này là Cell B3 và C3

 

[Minh Nguyen]

Hi.
có bạn nào cho hỏi: có cách (công thức) nào loại khoảng trống trong 1 dãy ký tự hay không?, vd: ha noi pho thành hanoipho.
(mình tìm trong công thức chỉ có công thức bỏ nhiều khoảng trống thành 1 khoảng trống)

Bạn có thể dùng theo các cách sau:
1. Dùng hàm SUBSTITUTE
2. Dùng lệnh REPLACE kí tự trống bằng không kí tự
Thử xem nhé

 

[handung107]

To viethuong : Tôi không hiểu bạn thực hiện thế nào, vì vấn đề này, không phải tôi khám phá ra đâu, có cả hàng chục cuốn sách nói như vậy và Microsoft cũng nói thế đấy. Còn riêng tôi, trước khi đưa thông tin lên cho các bạn, tôi đã kiểm tra rồi. Bạn bỏ dấu kiểm cho mục Edit directly in Cell, nghĩa là không sửa trực tiếp trên Cell được. Bạn có thể sửa ở chỗ khác như thanh Formula chẳng hạn. Nên nếu bạn vào Tool / Options, cho ẩn thanh công thức, ít nhất là bạn cũng phải tìm một chỗ khác để sửa dữ liệu, bạn không thể sửa tại Cell đó được. Bạn có thể Delete và nhập mới lại thôi

 

[handung107]

Trời, bạn định dùng cách này để bảo mật thì không ổn rồi, đây chỉ là những cái "khép" cửa hờ thôi, nó chỉ là cái lạ của Excel ít người để ý. Nói như bạn thì cũng đúng, nhưn gnếu chỉ sửa một ký tự ở giữa thí dụ :Anh Dung thành Anh Dũng thì hơi bị "phiền" đấy, bạn gõ Ctrl+Home để trở về đầu, gõ xong Duy và Delete Anh thì được, chứ sửa Dung thành Dũng e là hơi tốn thì giờ đấy

 

[penpal]

To handung107:
Mình đã áp dụng cách bạn hướng dẫn về Bản sao chính xác của công thức nhưng sao ko được. Khi mình thực hiện lệnh copy thì ngay lập tức ô mình vừa sao chép sẽ ko cho giá trị mà hiện nguyên công thức(?), mặc dù ô mình paste vẫn có giá trị đúng. Hơn nữa, làm sao để paste cho nhiều ô cùng một lúc, hình như cách copy chính xác này ko cho mình paste cho nhiều ô thì phải?
Mình vừa mới vào WKT và thật sự nó rất hữu ích, thanks a lots.

 

[handung107]

Hoặc khi bạn Copy thiếu mất dấu bằng (=), hoặc bạn Format Cell này dạng Text trước khi Paste công thức vào. Các bạn luôn luôn chú ý định dạng Cell trước khi áp dụng công thức nếu không muốn kết quả tính toán bị lỗi

 

[handung107]

Lâu rồi, chúng ta chưa có gì mới trong Topic này, phải không các bạn ? Hôm nay, chúng ta dạo qua một vòng với các hàm làm tròn số nhé.
1/Hàm ROUND, ROUNDUP, và ROUNDDOWN :

3 hàm này sẽ quen thuộc nhiều với các bạn. Hàm ROUND tôi không nói đến nữa, còn 2 hàm ROWNUP và ROUNDDOWN đúng với tên gọi của nó, nhưng khác với hàm ROUND, 2 hàm này chỉ làm tròn lên hoặc xuống mà thôi
TD : =ROUND(123.4567,-2) cho kết quả là 100 trong khi : =ROUNDUP(123.4567,-2) cho kết quả là 200 còn ROWNDOWN(123.4567,-2) cho kết quả là 100

=ROUND(123.4567,-1) cho kết quả là 120 trong khi : =ROUNDUP(123.4567,-1) cho kết quả là 130 còn ROWNDOWN(123.4567,-1) cho kết quả là 120

=ROUND(123.4567,0) cho kết quả là 123 trong khi : =ROUNDUP(123.4567,0) cho kết quả là 124 còn ROWNDOWN(123.4567,0) cho kết quả là 123

=ROUND(123.4567,1) cho kết quả là 123.5 trong khi : =ROUNDUP(123.4567,1) cho kết quả là 123.5 còn ROWNDOWN(123.4567,1) cho kết quả là 123.4

=ROUND(123.4567,2) cho kết quả là 123.46 trong khi : =ROUNDUP(123.4567,-2) cho kết quả là 123.46 còn ROWNDOWN(123.4567,2) cho kết quả là 123.45

 

[handung107]

2/ Làm tròn số với các hàm EVEN và ODD :

Bạn có thể sử dụng các hàm EVEN và ODD để thực hiện các phép tính làm tròn. Hàm EVEN làm tròn lên một số chẵn gần nhất, hàm ODD làm tròn lên một số tới số lẻ gần nhất. Do đó, các số âm sẽ được làm tròn xuống.

Cú pháp của hàm như sau :
=EVEN(number)
=ODD(number)

TD : =EVEN(23.4) cho kết quả là 24 trong khi ODD(23.4) cho kết quả là 25
=EVEN(2) cho kết quả là 2 trong khi ODD(2) cho kết quả là 3
=EVEN(-3) cho kết quả là -4 trong khi ODD(-3) cho kết quả là -3
=EVEN(-4) cho kết quả là -4 trong khi ODD(-4) cho kết quả là -5

 

[handung107]

3/Làm tròn số với các hàm FLOOR và CEILING :

Hàm FLOOR và CEILING có thể sử dụng để làm tròn lên hay tròn xuống tới một bội số xác định gần nhất

Cú pháp của hàm như sau :
=FLOOR(number,multiple)
=CEILING(number,multiple)

Trong cả 2 trường hợp, giá trị của cả 2 đối số đều phải là số và cùng dấu với nhau, nếu chúng khác dấu, Excel sẽ trả về giá trị [HASHTAG]#NUM[/HASHTAG]

TD : =FLOOR(23.4,0.5) cho kết quả là 23 vì bội số gần nhất của 0.5 với giá trị 23.4, và nhỏ hơn 23.4 chính là 23
=FLOOR(5,3) cho kết quả là 3 vì bội số gần nhất của 3 với giá trị 5 và nỏ hơn 5 chính là 3
=FLOOR(5,-1) cho kết quả là [HASHTAG]#NUM[/HASHTAG]
=FLOOR(5,1.5) cho kết quả là 4.5 vì bội số gần nhất của 1.5 với giá trị 5 và nhỏ hơn 5 chính là 4.5

=CEILING(23.4,0.5) cho kết quả là 23.5 vì bội số gần nhất của 0.5 với giá trị 23.4 và lớn hơn 23.4 chính là 23.5
=CEILING(5,3) cho kết quả là 6 vì bội số gần nhất của 3 với giá trị 5 và lớn hơn 5 chính là 6

 

[handung107]

4/ Làm tròn số với các hàm INT và TRUNC :

Hàm INT có chức năng làm tròn số xuống số nguyên gần nhất và có dạng :
=INT(number)
TD : =INT(100.01) cho kết quả là 100 nhưng =INT(100.999999) cũng có kết quả là 100 dù số này gần bằng 101

Hàm TRUNC : hàm này làm tròn số bằng cách cắt các số nằm ở bên phải dấu phẩy, cho dù số đó là âm hay dương. Đối số num_digits xác định số thứ bao nhiêu sau dấu phẩy sẽ bị cắt. Hàm có dạng sau :
=TRUNC(number,num_digits). Nếu đối số num_digits không xác định sẽ nhận giá trị mặc định bằng 0
TD : =TRUNC(13.978) cho kết quả là 13
Hàm TRUNC khác hàm ROUND là cắt bỏ những số đứng sau dấu phẩy mà không làm tròn

 

[handung107]

Excel xem ngày tháng là chuỗi số, nhưng là số gì ?

Thông thường, bạn sẽ chọn Cell chứa ngày tháng và Format nó trở thành dạng General hay Number. Nhưng có một cách nhanh hơn phải vào Menu Format là :
-Chọn bất kỳ Cell trống nào, nhấp nút Format Painter (hình cái chổi ) trên thanh Menu, sau đó nhấp vào Cell chứa ngày tháng
-Chọn nút Comma trên thanh công cụ Formatting, Excel sẽ áp dụng kiểu định dạng số có dấu ngăn cách cho Cell được chọn

 

[hai2hai]

Thông thường, bạn sẽ chọn Cell chứa ngày tháng và Format nó trở thành dạng General hay Number. Nhưng có một cách nhanh hơn phải vào Menu Format là :
-Chọn bất kỳ Cell trống nào, nhấp nút Format Painter (hình cái chổi ) trên thanh Menu, sau đó nhấp vào Cell chứa ngày tháng
-Chọn nút Comma trên thanh công cụ Formatting, Excel sẽ áp dụng kiểu định dạng số có dấu ngăn cách cho Cell được chọn

Ctrl + Shift + C: Copy Format của đoạn được Select vào Clipboard
Ctrl + Shift + V: Paste Format trong Clipboard vào vị trí hiện thời

Áp dùng cho cả Word, Excel, PPT, v.v.... (Tương đương với cách dùng "cái chổi vàng" của chị Dung)

 

[Bình_OverAC]

Ctrl+Shift+ C
Ctrl+Shift+ V
Em không sài được trong Excel bác hai2hai ơi! Không biết có bị cấn gì kô

 

[adam_tran]

Sử dụng phím Shift khi click một số button trên Toolbar, lệnh của Button sẽ "ngược lại" với lệnh mặc định.
Ví dụ, nhấn Shift+Save trở thành Open, Shift+Print trở thành Print Preview, Shift+Paste Value trở thành Paste Format, Shift+Delete Row trở thành Insert Row....
Cộng với Customize Toolbar thì việc sở dụng Excel sẻ thuận tiện hơn.
Nhấn giữ Alt khi sử dụng chuột, ta có thể di chuyển các Button trên toolbar bằng cách kéo và thả (drag and drop).

 

[handung107]

Bạn có biết ?

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 ?

 

[adam_tran]

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 ?

to chị Dung:
Đây là vấn đề em đặc biệt quan tâm. Có lẽ phải tìm hiểu xem tại sao. Trước mắt cũng có 1 vài giả thiết nhưng phải kiểm chứng xem đã.