Thủ...đọan excel

[AnhExcel]

Cập nhật công thức trong Excel 2000 trở lên

Trong Excel 2000 trở lên, bạn không cần sao chép hàng lọat công thức xuống các dòng bên dưới, khi các dòng bên dưới còn chưa có dữ liệu.
Sau khi bạn nhập dữ liệu cùng các công thức xong, bạn hãy nhấn Ctrl+D để sao chép các công thức xuống dòng kế tiếp. Bạn chỉ cần nhấn 3 lần như vậy để sao chép cho 3 dòng kế tiếp thôi. Bạn yên tâm, bây giờ khi bạn nhập dữ liệu xuống các hàng kế tiếp nữa, Excel sẽ thừa thông minh để tính sao chép tiếp tục các công thức ở hàng trên khi bạn nhập dữ liệu vào

In đường lưới Excel mà không phải tạo khung cho mỗi Cell
Ngòai cách dùng đặc tính Format/Cell/Border, để tạo khung viền cho các Cell, bạn cũng có thể đặt đường lưới in trên trang bảng tính bằng cách dùng Menu File / Page Setup. Nhấp chọn Tab Sheet, đánh dấu chọn ô Gridlines. (Trong một số phiên bản trước của Excel, ô này được chọn theo mặc định)
Bên cạnh đặc tính Gridlines, có 2 tùy chọn trên trang Sheet của cửa sổ Page Setup rất đáng được chú ý. Đó là Row and Column Headings cho phép in tiêu đề cột A, B, C...và số hiệu hàng 1, 2, 3...Đây là công cụ rất lý tuởng nếu bạn định in trang bảng tính làm tài liệu cho nhóm. Với tiêu đề cột và hàng này, bạn chỉ cần nói : "Chúng ta hãy chú ý ô D23", và mọi người trong nhóm sẽ nhanh chóng tìm thấy ngay vị trí đó

Chuyển giá trị Text thành công thức để tính tóan
Giả sử ta có các dữ liệu chuỗi như sau :
- Ô B1 : 1+2+3+4+5
- Ô B2 : 2*5
- Ô B3 : 10+2-4
- Ô B4 : 5*5
- Ô B5 : 8*6
Tại C1:C5, ta muốn hiện kết quả của phép tình này. Bạn có thể dùng hàm EVAL(B1), hay bạn đặt con trỏ tại C1, vào INsert/Name/Define. Bạn đặt tên công thức là KetQua. Tại Refers To, bạn nhập : =EVALUATE($B1), bạn cũng sẽ được kết quả là phép tình của các chuỗi số trên

Tổng của một dãy số liên tục
Giả sử bạn có một dãy số liên tục từ A1:A200 hay A1:W1, bạn sẽ tính tổng của dãy số này tại cell A201 và X1. Bạn chỉ cần giữ phím Alt và gõ dấu =, Excel sẽ tự động nhập công thức =SUM(A1:A200) và SUM(A1:W1) tại Cell A201 và Cell X1 giùm cho bạn.
Còn nếu bạn muốn có dấu $ để hiểu đó là giá tiền, bạn hãy thêm hàm DOLLAR vào, td : =DOLLAR(SUM(A1:A200)), bạn sẽ có kết quả $1500 chẳng hạn.
Excel còn hỗ trợ cho kế tóan một dạng Format / Cell nữa rất hay, Td nếu bạn muốn gạch dưới $1500 để nhấn mạnh, bạn hãy vào phần Underline, nhưng thay vì bạn chọn Single hay Double, bạn hãy chọn Single accounting và Double Accounting, các dạng đường kẻ dưới sẽ thóang và dễ nhìn hơn.

(Nguồn: *********)

 

[AnhExcel]

Chức năng Office Clipboard
Giả sử bạn đã có một DS được nhập liệu sẵn từ các Cell trong WorkSheet và bạn muốn Copy chúng vào một Comment hay một AutoShape như TextBox hay Callout..., chắc chắn bạn không thể sử dụng chức năng Edit / Copy hay Edit / Paste như bình thường được.
Bạn hãy thử làm như sau : Chọn vùng muốn Copy, rồi dùng Edit / Cut. Sau đó, bạn chọn Office Clipboard để dán vùng dữ liệu này vào TextBox hay Comment, bảo đảm bạn sẽ thực hiện được dễ dàng.

In trắng đen cho bảng tình có nhiều màu
Khi bảng tính của bạn có nhiều màu, có thể in bằng máy in trắng đen sẽ cho nhiều độ đen, xám đậm lạt khác nhau, nhưng bạn chỉ muốn các báo cáo có một màu đen duy nhất.
Bạn hãy vào File/Page Setup, chọn thẻ Sheet, tại mục Print, bạn đánh dấu kiểm vào Black and White. Bạn sẽ được một bản in đẹp như mong muốn

Điều khiển tính năng Zoom
Nếu bạn muốn điều khiển chức năng Zoom một cách nhanh chóng mà không cần vào Menu Zoom thì đây là một cách hiệu quả nhất.
Bạn giữ phím Ctrl và lăn nút Scroll của Mouse theo chiều kim đồng hồ, Zoom sẽ thu nhỏ lại từ từ cho đến khi chỉ còn 10%
Ngược lại, để phục hồi, bạn nhấn Ctrl và lăn Scoll theo chiều ngược lại. Mong các bạn sẽ hài lòng với tính năng này

Bạn đã thử hàm REPT chưa ?
Hàm REPT (lặp lại), gồm 2 đối số : Đối số thứ nhất là một chuỗi Text, và đối số thứ hai là số lần lập lại.
=REPT("text",n)
Td : =REPT("*",20) sẽ cho kết quả là có 20 dấu ********************
Tương tự, REPT("_",20) sẽ cho bạn một đường gạch liền nét dài 20 lần _
Ta có thể kết hợp hàm này cùng các Font Wingdings để trang trí cho bảng tính của chúng ta, vẽ các biểu đồ, đồ thị bằng cách cho chuỗi ký tự của chúng ta là những ô vuông nhỏ, kết hợp cùng các hàm khác nữa. Bạn thử dùng xem, cũng khá nhiều điều thú vị đấy

(Nguồn: *********)

 

[AnhExcel]

Khi một số không được xem là một số
Cách nhanh nhất để bạn chuyển các giá trị không phải số (Text) trở thành số (Number) là :
- Kích hoạt bất kỳ một ô trống nào đó trên bảng tính. Chọn Menu Edit/Copy
- Chọn vùng Text mà bạn muốn chuyển thành giá trị số. Tại hộp thoại Edit / Paste Special, chọn Add, nhấp OK
- Thủ tục này sẽ cộng thêm số 0 vào các ô, và Excel buộc phải xử lý các giá trị không phải số dưới dạng các giá trị thật sự


Sao chép của công thức
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

(Nguồn: *********)

 

[AnhExcel]

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é

Chuyển 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!

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

(Nguồn: *********)

 

[AnhExcel]

Làm tròn số với Round()
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

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

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ị #NUM

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à #NUM
=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

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

(Nguồn: *********)

 

[AnhExcel]

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


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 ?

(Nguồn: *********)

 

[AnhExcel]

Chuyển những số (dạng Text) thành giá trị (number),
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=conditi onal2),(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

(Nguồn: *********)

 

[thinhpn]

Ðề: Thủ...đọan excel

hay quá, cám ơn anh

 

[betrangxxx]

Ðề: Thủ...đọan excel

hiz chỉ dùm em hàm ABS là hàm gì ạh? dùng nó như thế nào ạh?
Em lấy ví dụ nha: =IF(OR(AND($C212="N",SUMIF(SOHIEUTK,$B212,SODDK)>0),AND($C212="C",SUMIF(SOHIEUTK,$B212,SODDK)<0)),ABS(SUMIF(SOHIEUTK,$B212,SODDK)),0)
Thx mí anh chị ^^

 

[xuongrongdat]

Ðề: Thủ...đọan excel

hiz chỉ dùm em hàm ABS là hàm gì ạh? dùng nó như thế nào ạh?
Em lấy ví dụ nha: =IF(OR(AND($C212="N",SUMIF(SOHIEUTK,$B212,SODDK)>0),AND($C212="C",SUMIF(SOHIEUTK,$B212,SODDK)<0)),ABS(SUMIF(SOHIEUTK,$B212,SODDK)),0)
Thx mí anh chị ^^

Hàm ABS là hàm lấy giá trị tuyệt đối đó bạn !

 

[thanhbinhdn]

Ðề: Thủ...đọan excel

Cho em hỏi tí!
em có 2 cột: cột A là Text, cột B là ngày tháng em muốn ghép 2 cột này lại với nhau thì làm sao?!
Cám ơn các bạn!

 

[lymap]

Ðề: Thủ...đọan excel

AnhExcel ơi cho e hỏi tí xíu: anh có bít làm khi mình nhấp vào ô thì sẽ hiện lên 1 ô như ô thông báo vậy. Nó giống như insert comment vậy đó nhưng k phải là insert comment.
-----------------------------------------------------------------------------------------
Cho e hỏi cái nữa : tiện ích của hàm DSUM là j? E thấy nhưng k biết dùng để làm j cả.

 

[beheo_wind]

Ðề: Thủ...đọan excel

thanks anh nhiều

 

[thuynguyen14]

Ðề: Thủ...đọan excel

Các bạn nào giỏi excel tìm dùm mình công thức để kết chuyển tổng số tiền từng tài khoản 621, 622 theo mã công trình. Mình dùng hàm sumif nhưng nó ra k chính xác. Thanks nhiều
A B C D
TKNO TKCO MA CT THANH TIEN
621 152 A 100.000,00
621 152 A 100.000,00
621 152 A 100.000,00
621 152 B 100.000,00
621 152 B 100.000,00
621 152 B 100.000,00
622 334 A 200.000,00
622 334 B 200.000,00
622 334 B 200.000,00
622 334 B 200.000,00

154 621 A ?
154 621 B ?
154 622 A

 

[maythu]

Ðề: Thủ...đọan excel

Mình bổ sung thêm cách này cho các bạn tham khảo nhé Hm ABS trong Excel, Hm tnh gi tr? tuy?t ??i