CÁC THỦ THUẬT TRONG OFFICE (Phần 3)

Nguyễn Phương Thảo 525 - ĐƯỜNG ĐIỆN BIÊN - Thị xã YÊN BÁI
mailto: fthaoabc@yahoo.com

ĐẶT TÊN CÔNG THỨC TRONG EXCEL

a. Thống nhất khái niệm:

Trước khi viết phần này, tôi xin thống nhất một số khái niệm để chúng ta hiểu được ý đồ của bài viết.
Khi sử dụng Excel, ta sẽ thấy các ô trong Excel được chia làm 2 loại chính:
- Ô chủ động là ô nhập dữ liệu vào từ bàn phím.
- Ô bị động là ô ta thiết lập công thức để máy tính toán đưa ra kết quả. Khi sửa chữa dữ liệu trong các ô chủ động, thì kết quả trong các ô bị động sẽ tự sửa chữa theo.

Để thực hiện xử lý tự động trong Excel, người ta phải xây dựng bảng tính thành dạng CSDL (Cơ sở dữ liệu). Tức là thành bảng có cấu trúc các bản ghi giống nhau. Khi đó, ta chỉ việc làm mẫu cho Excel một trường hợp, thì những bản ghi tiếp theo máy sẽ tự tính theo cách của trường hợp mẫu.

Ví dụ bảng cấu trúc như sau không phải CSDL:

(Bạn muốn thành CSDL phải thêm một cột với tiêu đề "Phòng")

Để thống nhất, các bài viết của tôi về Excel đều xây dựng CSDL bắt đầu từ hàng thứ 4. Hàng thứ 5, tôi đánh công thức bằng tay. Từ hàng thứ 6 trở xuống tôi thực hiện Fill-Up. Nghĩa là đặt hộp sáng tại ô công thức đã đánh xong. Đặt trỏ chuột tại góc phải dưới của ô để biến thành dấu + màu đen, thì Dclick. (Nếu không Dclick được, bạn hãy Drag).

Những bài viết về sau của phần Excel, tôi không nhắc lại phần này nữa.

Ký hiệu trong bài viết: Click (Nháy đơn) ký hiệu là , Dclick (Nháy kép) ký hiệu là , Drag (Kéo) ký hiệu là . Nhìn qua ký hiệu, ta sẽ biết ngay cách thực hiện. Ký hiệu này tôi đã sử dụng từ hồi mới đi học và đã đề nghị Microsoft lấy làm ký hiệu chính thức của thao tác.

b. Đặt tên công thức:

Trong EXCEL có một điều rất hay, thế mà ta lại sử dụng chức năng này của nó rất mờ nhạt. Đó là đặt tên công thức. Một đoạn công thức ta sẽ đặt bằng một cái tên nào đó và sử dụng tên này giống như một biến trong các công thức bị động của Excel vậy. Khi đặt tên ta thấy mọi công thức kiểm tra thật dễ dàng và chính xác. Nó làm công thức ngắn gọn và đơn giản hơn. Tên công thức có thể dễ dàng truyền từ Sheet nọ sang sheet kia, dễ dàng nối kết dữ liệu với nhau, tự động cập nhật khi thay đổi. Ngoài ra tên công thức còn dùng khi in ấn, vẽ đồ thị... Excel cho ta nhiều kết quả hơn mong đợi rất nhiều. Bạn có tin không?

Ta hãy tìm hiểu từ đơn giản đến phức tạp. Đầu tiên là vùng dữ liệu và đặt tên vùng dữ liệu.

Khi xử lý dữ liệu liên quan tới nhiều ô sử dụng các hàm với vùng dữ liệu, thông thường thì ta viết theo quy tắc Góc trái trên: Góc phải dưới.

Ví dụ 1: Vùng C5: E5.
Ví dụ 2: Vùng $A$4:$H$20

Một vùng sử dụng nhiều lần có thể được đặt tên để dễ sử dụng.

Giả sử khi đặt tên vùng $A$4:$H$20 là VUNG_IN, thì thay việc viết công thức $A$4:$H$20, ta viết VUNG_IN.

Để đặt tên vùng Insert, Name, Define... hiện bảng giao tiếp

Đánh tên công thức cần đặt vào hộp Name In Workbook. Hộp Refer To để xác định công thức cần đặt tên.

Riêng tên vùng, có 2 loại vùng là vùng cố định và vùng tự động thay đổi khi ta thêm bớt bản ghi.

- Nếu đặt tên vùng cố định, đánh vào hộp ReferTo công thức xác nhận vùng Ví dụ: 'Sheet1'!$a$1:$H$300, rồi Add.

(Góc trái trên và góc phải dưới của vùng cố định không nhất thiết phải là địa chỉ tuyệt đối, tuỳ thuộc bạn áp dụng công thức đối với tên vùng).

Nếu bạn đặt tên vùng cố định bằng địa chỉ tương đối, bạn phải làm chủ được sự sai lệch địa chỉ khi copy công thức có tên vùng từ ô này sang ô khác.

Thông thường ta lại hay sử dụng tên vùng thay đổi. Nghĩa là nếu ta bổ sung thêm bản ghi, thì bản ghi tự động thêm vào tên vùng, khi đó hộp Refer to:

Sau khi đánh xong, ta Add. Và đóng hộp thoại lại.

Ví dụ: Cho CSDL: Họ tên (ở Cột B), Phường (C), Mệnh giá công trái (D) trên Sheet có tên là "Bảng mua công trái".
Ta có thể sử dụng cột Họ tên là cột căn cứ mở rộng tên vùng. Khi ta đánh thêm danh sách (Cột họ tên được cập nhật) thì vùng sẽ nới rộng thêm hàng họ tên đó.

*Đặt tên:
PHUONG = OFFSET('Bảng mua công trái'!$c$5;0;0;COUNTA($B:$B);1)
CONGTRAI = OFFSET('Bảng mua công trái'!$d$5;0;0;COUNTA($B:$B);1)

Khi đã đặt tên xong, ta sử dụng các hàm SUM, AVERAGE, MAX, MIN, COUNT, COUNTA, COUNTBLANK... ở bất cứ chỗ nào thuộc Workbook

Ví dụ: SUM(CONGTRAI) - Tính tổng vùng CONGTRAI. Nếu bổ sung danh sách, kết quả sẽ tự cập nhật. Tương tự các hàm kia.

Sau đây là ví dụ sử dụng nâng cao các hàm SUMIF, COUNTIF mà ta không thể ngờ tới:
Hàm SUMIF (Vùng dữ liệu; Điều kiện; Vùng cộng)
- Cộng các số trong vùng cộng nếu ô tương ứng trong vùng dữ liệu thoả mãn điều kiện.
- Nếu không đánh vùng cộng thì sẽ cộng trong vùng dữ liệu. Nếu có vùng cộng thì vùng dữ liệu và vùng cộng phải có số cột bằng nhau và độ cao vùng bằng nhau để có ô tương ứng giữa 2 vùng.

Chú ý: Điều kiện phải được đặt trong "".
COUNTIF(Vùng dữ liệu;"Điều kiện")
Đếm các ô trong vùng dữ liệu thỏa mãn điều kiện.

Ta sẽ thấy nhiều khi ta không cần sử dụng đến các hàm VLOOKUP rắc rối mà vẫn có thể thực hiện được mục đích.
Ví dụ 1:
Theo cách đặt trên. Tính tổng tiền mua công trái của phường Minh Tân:
= SUMIF(PHUONG;"Minh Tân";CONGTRAI)

Ví dụ 2:
Tính tổng tiền mua công trái của những người có mệnh giá hơn 500000
=SUMIF(CONGTRAI,">500000")
Do điều kiện phải đặt trong nháy kép, nên ta thấy dấu & rất lợi hại:

Ví dụ 3:
Tính tổng tiền mua công trái của những người mua nhỏ hơn giá trị nằm trong ô A3.
=SUMIF(CONGTRAI;"<"&$a$3)

Ví dụ 4:
Đếm số người mua công trái lớn hơn mệnh giá trung bình ?
=COUNTIF (CONGTRAI;">"&AVERAGE(CONGTRAI))

Trên đây là phần tên của vùng, nhưng ta đặt tên công thức thì có thể biến hoá khôn lường, độ dài của một công thức trong Excel không phải đánh dài vô tội vạ. Nếu đặt tên ta thấy công thức và thuật toán không những ngắn gọn và thật dễ hiểu. Ta đặt tên có khi bí mật cả công thức (!!!)

Ví dụ Cho CSDL tại Sheet "Văn"

Xác định công thức để tính điểm trung bình môn.
Ô M5 nếu đánh như sau thì khó hiểu:
=(SUM(C5:M5)+SUM(I5:M5)+SUM(M5))/(COUNTA(C5:M5)+COUNTA(I5:M5)+COUNTA(M5))
Đặt hộp sáng ở hàng 5, ta đặt tên :
TONG =SUM($C5:$M5)+SUM($I5:$M5)+SUM($M5)
DEM =COUNTA($C5:$M5)+COUNTA($I5:$M5)+COUNTA($M5)
TBM=TONG/DEM

Khi đó bất kỳ ở hàng nào chỉ việc đánh = TBM sẽ ra TBM của chính học sinh đó.
Xin hẹn ở bài viết sau.

Nguyễn Phương Thảo

PcLeHoan 1996 - 2002
Mirror : http://www.pclehoan.com
Mirror : http://www.lehoanpc.net
Mirror : http://www.ktlehoan.com