PDA

Xem phiên bản đầy đủ : Thủ thuật Excel


thaygiaolang
12-09-2009, 17:10
Có lẽ Excel là một trong những phần mềm được GV sử dụng phổ biến. Nếu chỉ dùng Excel để lập các danh sách đơn giản để in ra thì chỉ cần những kiến thức cơ bản về Excel là đủ. Tuy nhiên, đôi khi GV cũng cần dùng Excel để làm 1 số công việc phức tạp hơn (như tính điểm, xếp loại,...). Khi đó, nếu có thêm một số kinh nghiệm nho nhỏ thì công việc sẽ trở nên dễ dàng hơn.

Do đó, mình lập ra chủ đề này nhằm chia sẻ một số những kinh nghiệm nho nhỏ mà mình tích lũy được trong quá trình ... học hỏi người khác. Những kinh nghiệm này thường không có trong các giáo trình Excel

Đánh số thứ tự

Trong các danh sách thì bao giờ cũng phải có cột số thứ tự. Để đánh số thứ tự thì có nhiều cách, cách nào cũng có những ưu nhược điểm của nó. Mình xin chia sẻ một số cách mà mình biết.


Giả sử ta cần đánh số thứ tự bắt đầu từ 1 từ ô A2 đến ô A11

Cách 1 : đây là cách mà các tài liệu Excel hướng dẫn.



Gõ số 1 (là số thứ tự đầu tiên) vào ô A2.
Click vào ô A2 để chọn lại nó.
Dùng chuột kéo góc dưới bên phải của ô để bôi đen hết vùng A2:A11.
Nhấn và giữ phím Ctrl rồi nhả nút chuột ra (nếu không nhấn và giữ phím Ctrl là thao tác Copy)


Ưu điểm : đơn giản, dễ thực hiện.
Nhược điểm : STT tạo ra có tính tĩnh. Khi ta xóa hàng, thêm hàng, thay đổi giá trị khởi đầu thì phải thao tác lại.

Cách 2 :



Gõ số 1 (là số thứ tự đầu tiên) vào ô A2.
Tại ô A3 gõ vào công thức =A2+1
Copy công thức này đến các ô còn lại.


Ưu điểm : khi cần thay đổi giá trị khởi đầu thì ta chỉ việc gõ vào ô A2; khi chèn thêm hàng thì chỉ cần copy công thức đến ô vừa chèn và ô ngay sau nó.
Nhược điểm : khi xóa 1 ô (bằng phím Delete) thì ô dưới nó sẽ trở lại giá trị 1. Còn nếu xóa cả hàng thì sẽ phát sinh lỗi “Ref!”

Cách 3 :



Tại ô A2 gõ vào công thức =ROW()-ROW(A$1)
Copy công thức này đến các ô còn lại.


Ưu điểm : khi chèn hay xóa hàng thì số thứ tự không thay đổi.
Nhược điểm : khi cần thay đổi giá trị khởi đầu thì phải viết lại công thức.

nhatphi
13-09-2009, 14:14
Bổ sung thêm một cách khác:
Cột A là thứ tự, cột B là họ tên ta nhập công thức sau:
- Ở cột A2 ta nhập công thức: if(B2="","",1)
- Ở cột A3 ta nhập công thức: if(B3="","",$A2+1) và sau đó coppy công thức cho các ô còn lại.
Ưu điểm: Khi nhập học và tên vào thì số thứ tự mới hiện ra.
Nhược điểm: Khi xóa phải coppy công thức lại.

nhatphi
13-09-2009, 14:20
Cách khác nữa:
Cột A là thứ tự, cột B là họ tên ta nhập công thức sau:
- Ở cột A9 ta nhập công thức: =IF(B9="","",MAX(A$8:A8)+1)
- Sau đó coppy công thức cho các ô còn lại.
Ưu điểm: Khi nhập học và tên vào thì số thứ tự mới hiện ra và khi xóa tên trong danh sách ta không cần coppy lại công thức
Nhược điểm: Khi chèn thêm tên phải coppy công thức lại.

thaygiaolang
13-09-2009, 16:07
Cách khác nữa:
- Ở cột A9 ta nhập công thức: =IF(B9="","",MAX(A$8:A8)+1)


Thầy xem lại giúp : nếu nhập ở ô A9 thì công thức phải là =IF(B9="","",MAX(A$1:A8)+1) mới đúng

nhatphi
13-09-2009, 16:11
Thầy xem lại giúp : nếu nhập ở ô A9 thì công thức phải là =IF(B9="","",MAX(A$1:A8)+1) mới đúng
Ở đây mình đánh số thứ tự bắt đầu từ ô A9.

thaygiaolang
17-09-2009, 13:53
Chuyển cột thành hàng

Giả sử ta có vùng dữ liệu dạng cột ở A2:A10, ta cần chuyển các giá trị ở vùng này sang dạng hàng, bắt đầu từ ô B1 (đến ô J1)

Cách 1 : đây là cách mà các tài liệu Excel thường hướng dẫn.



Bôi đen vùng A2:A10.
Vào menu Edit, chọn Copy (hoặc nhấn Ctrl+C, hoặc click nút Copy)
Chọn ô B1
Vào menu Edit, chọn Paste Special…
Trong hộp thoại Paste Special, đánh dấu chọn vào ô Transpose, rồi chọn OK.


Ưu điểm : đơn giản, dễ thực hiện.
Nhược điểm : dữ liệu tạo ra có tính tĩnh. Khi ta xóa hàng, thêm hàng, thay đổi giá trị khởi đầu thì phải thao tác lại.

Cách 2 :



Bôi đen vùng B1:J1
Gõ vào công thức =TRANSPOSE(A2:A10)
Nhấn tổ hợp phím Ctrl+Shift+Enter (để tạo công thức mảng).


Ưu điểm : khi thay đổi các giá trị trong vùng A2:A10 thì ở vùng B1:J1 sẽ thay đổi tương ứng.
Nhược điểm : khi muốn thêm hàng hay bớt hàng thì phải xóa công thức mảng (bôi đen hết vùng B1:J1 rồi Delete) rồi tạo lại.

Cách 3 :



Tại ô B1 gõ vào công thức =OFFSET($A1,COLUMN()-COLUMN($A1),0)
Copy công thức này đến các ô còn lại (đến J1).


Ưu điểm : tương tự như cách 2. Khi muốn thêm hàng thì chỉ copy công thức đến các cột tương ứng.
Nhược điểm : nếu là file lớn, có nhiều ô chứa công thức thì khi thêm những công thức này vào, tốc độ tính toán của Excel sẽ chậm đi đáng kể (đây chỉ là kinh nghiệm thực tế, mình cũng chưa biết vì sao).

thaygiaolang
21-09-2009, 08:26
Validation

Validation là một công cụ Excel cung cấp giúp người dùng có thể kiểm soát dữ liệu nhập vào trong 1 ô. Khi muốn dữ liệu nhập vào một ô phải thỏa mãn các yêu cầu nào đó thì ta có thể quy định Validation cho ô đó. Để cài đặt Validation cho 1 hoặc nhiều ô, ta chọn các ô đó, rồi vào menu Data -> Validation… Trong các giáo trình Excel, người ta thường hướng dẫn cài đặt Validation cho các yêu cầu thông thường. Ở đây, mình xin chia sẻ một số trường hợp Validation khác.

Giả sử ta cần đặt Validation vùng từ ô A1:A10

1) Không cho nhập chuỗi có dấu cách (khoảng trắng)

Trong mục Validation, ta chọn Custom rồi gõ vào ô Formula công thức : =IsERR(FIND(" ",A1))

Ta có thể thay dấu cách bằng 1 ký tự bất kỳ khác nếu không muốn cho nhập ký tự đó.

Nếu muốn ngăn nhập nhiều ký tự, ta có thể kết hợp nhiều công thức trên bằng phép toán AND.
Ví dụ : =IsERR(FIND(" ",A1))*IsERR(FIND(".",A1))*IsERR(FIND(",",A1))

2) Tránh nhập trùng

Giả sử ta cần nhập dữ liệu vào vùng A1:A10, và trong vùng này không cho phép có 2 ô giống nhau (ví dụ như vùng mã số HS chẳng hạn). Để ngăn ngừa việc nhập trùng, ta thiết lập Validation như sau :


Bôi đen vùng A1:A10
Vào Validation, chọn Custom rồi gõ vào ô Formula công thức =COUNTIF($A$1:$A$10,A1)=1


3) Nhập dữ liệu theo một dạng thức quy định

Ví dụ như cột mã số HS, mã số được quy định gồm 12 ký tự, 6 ký tự đầu là mã trường (ví dụ là 07EA34). Trong mục Formula ta dùng công thức =AND(LEFT(A1,6)="07EA34",LEN(A1)=12)

4) Validation dạng List

Người ta thường dùng Validation dạng List để người dùng chỉ nhập vào các giá trị được quy định trước trong List. Ví dụ như cột xếp loại hạnh kiểm, nếu để người dùng tự nhập thì sẽ có trường hợp cùng 1 loại nhưng lại được thể hiện bằng nhiều cách khác nhau (như Trung bình, TB, T.Bình,…), gây khó khăn trong việc thống kê.

Để thiết lập Validation dạng List, trong cửa sổ Data Validation, mục Allow ta chọn List, sau đó trong mục Source, ta gõ vào các giá trị quy định cách nhau bằng dấu phẩy.

Để được linh động hơn, người ta thường dùng 1 vùng trong bảng tính để chứa các giá trị. Khi đó trong mục Source, ta gõ địa chỉ của vùng này (nhớ dùng địa chỉ tuyệt đối). Ví dụ =$C$1:$C$10

Giả sử ta đã chọn vùng C1:C10 làm vùng chứa các giá trị cho List (chứa 10 giá trị). Thế nhưng bây giờ ta muốn thêm vào vài giá trị nữa. Như vậy ta phải sửa lại Source trong Validation. Do đó, người ta thường khai báo “trừ hao” trước. Ví dụ mới có 10 giá trị thôi, nhưng ta có thể khai báo 1 vùng C1:C20 (20 ô). Như vậy, khi cần thêm giá trị ta chỉ việc gõ thêm vào các ô tiếp theo.

Tuy nhiên, cách làm trên có 1 nhược điểm nho nhỏ là vì vùng C1:C20 sẽ có các ô trống do ta chưa dùng đến, nên trong List cũng sẽ có các dòng trống tương ứng. Tuy đây không phải là vấn đề gì nghiêm trọng, nhưng ta có thể khắc phục bằng cách đơn giản như sau : trong mục Source, thay vì chỉ gõ địa chỉ vùng, ta gõ vào công thức sau =OFFSET($C$1,,,COUNTA($C$1:$C$20))


Nhược điểm lớn nhất của Validation là nó chỉ kiểm soát dữ liệu được gõ vào ô. Nếu người dùng sử dụng thao tác Copy-Paste thì nó cũng bó tay, thậm chí là Validation của ô cũng bị xóa luôn.

thaygiaolang
23-09-2009, 17:54
Lọc dữ liệu


Lọc dữ liệu là ta lựa ra những dòng nào trong bảng thỏa 1 hay nhiều điều kiện nào đó và chỉ cho hiển thị các dòng đó mà thôi. Thông thường, khi cần lọc dữ liệu, ta có thể sử dụng các chứa năng trong menu Data -> Filter do Excel cung cấp. Tuy nhiên cách lọc này là cách làm tĩnh, khi ta thay đổi dữ liệu trong bảng thì ta phải thực hiện lại thao tác lọc.

Mình xin chia sẻ một số tình huống lọc dữ liệu động.

Việc lọc dữ liệu thường chia làm 2 bước :


Bước 1 : phân biệt những dòng dữ liệu thỏa và không thỏa điều kiện.
Bước 2 : hiển thị các dòng dữ liệu thỏa điều kiện.


Khi sử dụng các chức năng lọc do Excel cung cấp, Excel thực hiện bước 2 bằng cách dấu đi (hide) các dòng không thỏa điều kiện. Do đó, việc cập nhật dữ liệu sẽ khó thực hiện được. Khi lọc dữ liệu động, dữ liệu lọc ra sẽ được hiển thị tại 1 vùng riêng biệt và được cập nhật ngay khi vùng dữ liệu nguồn thay đổi.

Giả sử vùng chứa dữ liệu gốc là C2:C20, vùng chứa dữ liệu lọc là E2:E20. Việc lọc dữ liệu động cần phải dùng thêm 1 vùng dữ liệu trung gian B2:B20



Bước 1 : ở vùng trung gian B2:B20, ta gõ vào công thức =IF("Điều kiện lấy",MAX($B$1:B1)+1,"")
Bước 2 : ở vùng chứa dữ liệu lọc E2:E20, ta gõ vào công thức =IF(ROW(1:1)>MAX($B$2:$B$20),"",VLOOKUP(ROW(1:1),$B$2:$C$20,2))


Tùy theo yêu cầu mà ta thiết kế “Điều kiện lấy” thích hợp.

Một số ví dụ

Trong các ví dụ sau, mình chỉ nêu ra “Điều kiện lấy”. Khi sử dụng chỉ cần ráp vào công thức ở bước 1 trên.

1) Lọc các ô không rỗng : C2<>""

2) Lọc những người có họ Nguyễn trong danh sách : LEFT(C2,FIND(" ",C2)-1)="Nguyễn"

3) Lọc các người có tên "Dũng" trong danh sách (họ và tên chung 1 cột) :
RIGHT(C2,LEN(C2)-FIND("*",SUBSTITUTE(C2," ","*",LEN(C2)-LEN(SUBSTITUTE(C2," ","")))))="Dũng"

4) Lọc dữ liệu trùng, nếu có nhiều dòng giống nhau thì chỉ lấy 1 dòng : COUNTIF($C$2:C2,C2)=1

ducvuong58
13-03-2010, 21:03
Xin bổ sung thêm một cách như sau:

Giả sử danh sách bao gồm các cột mục:
Dòng tiêu đề
A2: "STT"; B2: "Họ và tên"; C2: "Ngày sinh"

Như vậy kể từ dòng (Row) thứ 3 sẽ là dòng nhập liệu của danh sách:
A3=if(B3="","",counta(B$3:B3))
đặt khung chọn vào A3 nắm nút kéo kéo xuống hết danh sách (hoặc dùng tổ hợp phím Ctrl+D để copy công thức xuống hết danh sách).

Các bạn thử xem!

thaygiaolang
18-04-2010, 13:14
Xếp hạng liên tục


Excel có cung cấp sẵn hàm Rank để xếp hạng. Tuy nhiên, hàm Rank lại xếp hạng theo 1 nguyên tắc khác với cách xếp hạng thông thường: ví dụ có 2 em đồng hạng nhất, thì em kế tiếp sẽ được xếp hạng 3 chứ không phải là hạng 2 như thông thường. Hàm Rank là hàm có sẵn trong Excel nên ta không thể chỉnh sửa gì được. Do đó, để có thể xếp theo ý muốn, ta phải tự xây dựng 1 công thức riêng. Ở đây, mình lấy ví dụ việc xếp hạng HS trong lớp.

Trước tiên, ta lập công thức để xếp hạng theo nguyên tắc của hàm Rank. Thực chất của nguyên tắc này là : giả sử có n HS có điểm lớn hơn em X thì em X sẽ xếp hạng (n+1). Như vậy, ta chỉ cần thực hiện việc đếm.

Giả sử ĐBQ của HS chứa trong vùng B1:B20, trong vùng xếp hạng C1:C20 ta dùng công thức sau :
=SUMPRODUCT((B$1:B$20>B1)*1)+1

Công thức trên sẽ đếm trong vùng B1:B20 có bao nhiêu giá trị lớn hơn B1, cộng thêm 1 để có hạng của HS.

Theo cách xếp loại thông thường ta cũng sẽ đếm các ô có giá trị lớn hơn ĐBQ của em đó, nhưng các ô có giá trị giống nhau thì ta chỉ đếm 1 lần.

Công thức để đếm các số giá trị khác nhau trong vùng là :
=SUMPRODUCT(1/COUNTIF(B$1:B$20,B$1:B$20)*1)

Kết hợp 2 công thức trên lại, ta có công thức cuối cùng :
=SUMPRODUCT((B$1:B$20>B1)*(1/COUNTIF(B$1:B$20,B$1:B$20)))+1

thaygiaolang
19-04-2010, 16:41
Tính tiền điện

Thời buổi điện tăng giá, các thầy cô có thể tự tính số tiền điện mình phải trả hàng tháng trước khi nhận được hóa đơn của điện lực.

Giả sử ô B16 ghi số KWh sử dụng trong tháng (bằng chỉ số mới trừ chỉ số cũ), có thể dùng công thức sau :

=(MIN(B16,50)*600+MAX(MIN(B16-50,50),0)*1004+MAX(MIN(B16-100,50),0)*1214+MAX(MIN(B16-150,50),0)*1594+MAX(MIN(B16-200,100),0)*1722+MAX(MIN(B16-300,100),0)*1844+MAX(B16-400,0)*1890)*110%Biểu giá điện mới nhất các thầy cô có thể xem tại đây (http://icon.com.vn/Home/News/tabid/55/TopicId/91/language/vi-VN/Default.aspx)

thaygiaolang
20-04-2010, 13:57
Xếp loại học lực theo quy chế 40

1) Loại Giỏi : chỉ có 1 nhóm điều kiện : AND(ĐBQ>=8,MAX(Van,Toan)>=8,MIN(DiemCacMon)>=6.5)


Điểm BQ từ 8 trở lên : ĐBQ >= 8
Có ít nhất 1 trong 2 môn Văn, Toán từ 8 trở lên : MAX(Van,Toan) >= 8
Không có môn học nào điểm trung bình dưới 6,5 : MIN(DiemCacMon) >= 6.5

2) Loại Khá : gồm 2 nhóm điều kiện :

* Nhóm đúng tiêu chuẩn : tương tự như loại giỏi : AND(ĐBQ>=6.5,MAX(Van,Toan)>=6.5,MIN(DiemCacMon)>=5)
* Nhóm được điều chỉnh : AND(ĐBQ>=8,MAX(Van,Toan)>=8,COUNTIF(DiemCacMon,"<6.5")<2,MIN(DiemCacMon)>=3.5)


Đáng lý xếp loại Giỏi : ĐBQ >= 8 và MAX(Van,Toan) >= 8
Do 1 môn (chỉ 1 môn) điểm thấp : COUNTIF(DiemCacMon,"<6.5") < 2
Môn này kéo xuống loại TB (chứ không phải là Yếu hay Kém) : MIN(DiemCacMon) >= 3.5

3) Loại TB và Yếu tương tự như loại Khá.

Ghép các phần trên lại, ta có công thức cuối cùng :

=IF(ĐBQ="-","-",
IF(AND(ĐBQ>=8,MAX(Van,Toan)>=8,MIN(DiemCacMon)>=6.5),"Giỏi",
IF(OR(AND(ĐBQ>=6.5,MAX(Van,Toan)>=6.5,MIN(DiemCacMon)>=5),AND(ĐBQ>=8,MAX(Van,Toan)>=8,COUNTIF(DiemCacMon,"<6.5")<2,MIN(DiemCacMon)>=3.5)),"Khá",
IF(OR(AND(ĐBQ>=5,MAX(Van,Toan)>=5,MIN(DiemCacMon)>=3.5),AND(ĐBQ>=8,MAX(Van,Toan)>=8,COUNTIF(DiemCacMon,"<5")<2)),"TB",
IF(OR(AND(ĐBQ>=3.5,MAX(Van,Toan)>=3.5,MIN(DiemCacMon)>=2),AND(ĐBQ>=6.5,MAX(Van,Toan)>=6.5,COUNTIF(DiemCacMon,"<5")<2)),"Yếu","Kém")))))

Nhờ các thầy cô kiểm tra giúp công thức trên có đúng không?

thaygiaolang
06-07-2010, 09:28
Ẩn dữ liệu trong ô

Trong Excel, đôi khi người ta cần dùng đến những kết quả trung gian trong tính toán. Những kết quả này người ta thường không muốn thể hiện trên bảng tính. Để dấu các kết quả trung gian này, thông thường người ta hay dùng cách dấu cột (hay hàng). Tuy nhiên, với cách làm này thì toàn bộ cột sẽ được dấu. Nếu ta chỉ muốn làm ẩn dữ liệu trong 1 số ô của cột thôi thì sao?

Có 1 cách đơn giản để làm điều này :


Chọn các ô muốn thực hiện.
Mở hộp thoại Format Cells, chọn tab Number.
Ở mục Category, chọn Custom.
Trong ô Type, gõ vào 3 ký tự chấm phẩy (ký tự ;) rồi OK.

Lưu ý : cách làm này chỉ giúp không hiển thị dữ liệu trong ô nhưng công thức trong ô vẫn hiện trên thanh công thức và ta vẫn có thể thay đổi hay xóa. Do đó nó không có tác dụng bảo vệ công thức hay bảo vệ bản quyền (không cho người khác xem công thức)