Thu thuat hay

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: [email protected]
ĐẶ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 (