THƯ VIỆN ĐẠI HỌC NHA TRANG
M
660.60285
Ch 500 M
CHU VĂN MÂN
CHU VĂN man
TIN HỌC
1
RMK etas N tẩ SM HỌC
,n— — «»- -r „-É ,,| ,
ỈRƯỪUŨẸIÃI HỌC NHA TRANG
t h ư Ví I n
V U t ó Ậ
LỜI MỞ ĐẦU
Ngày nay, với sự tiến bộ của công nghệ thông tin và internet, đã cho phép người ta sử dụng các phương tiện tính toán cực kỳ mạnh để giải các bài toán hệ thống, các bài toán về quần thể sinh vật phức tạp cả về không gian và thời gian, đồng thời đòi hỏi việc truyền đạt kiến thức toán học thống kê phải được kết hợp với phương pháp tư duy, phương pháp tính toán bằng phương tiện mới.
Cống nghệ sinh học là một lĩnh vực khoa học trẻ, đa ngành, phát triển rất năng động và hết sức mạnh mẽ trong nửa cuối thế kỷ XX. Nếu như công nghệ thông tin và internet được xem là công nghệ của thế kỷ XX, thì rất nhiều ý kiến dự báo đều cho rằng, công nghệ sinh học trở thành công nghệ phát triển mạnh mẽ và năng động nhất của thế kỷ XXI.
Trong lĩnh vực khoa học và công nghệ sinh học, các cường quốc công nghiệp hàng đầu, do ưu tiên tập trung đầu tư từ rất sớm nên công nghệ sinh học của các quốc gia này'phát triển hết sức mạnh mẽ, vượt trội toàn diện, triệt để và bỏ rất xa các quốc gia đang phát triển. Như một hệ quả tất yếu, năng lực iưu trữ, xử lý và khai thác cơ sở dữ liệu nói chung, và dữ liệu về công nghệ sinh học nói riêng, cũng tập trung cao độ trong các ngân hàng dữ liệu thuộc ba trung tâm khoa học và công nghệ hàng đầu thế giới là: Mỹ, Cộng đồng Châu Âu và Nhật Bàn.
Trên nền tảng công nghệ thông tin và internet, cơ sở dữ liệu công nghệ sinh học và hợp tác trao đổi thông tin đã thực sự liên thông và ỉiên kết quy mô toàn cầu, góp phần tạo ra ưu thế ứng dụng to lớn của tin - sinh học
(Bio - informatics).
Cuốn sách này trang bị cho các nhà sinh học các công cụ có nội dung bao gồm:
- Tiếp cận và sử dụng phần mềm Microsoft excel trong nghiên cứu sinh học. - Thu thập số liệu, cách thu thập số liệu, sắp xếp hệ thống hoá số liệu đã thu được, tìm ra những tham số đặc trưng cho số liệu này bàng công cụ
Microsoft excel.
- Phân tích các quy luật biến thiên cùa các trị số quan trắc, xây dựng thành mô hình lý thuyết; so sánh các . tập hợp số liệu với nhau, xác định
hai hay nhiều tập hợp này có cùng bản chất hay không bằng công cụ
Microsoft excel.
- Lưu trữ và xác định mối liên hệ giữa nhiều hệ thống số liệu bằng công cụ Microsoft excel
- Giới thiệu về internet, cơ sở dữ liệu công nghệ sinh học trên internet và phân tích kết quả nghiên cứu theo chương ừình Tin - Sinh học.
Do sách được biên soạn lần đầu, nên khỏ tránh khỏi thiểu sót, tác giả mong được bạn đọc lượng thứ, góp ý kiến và xin được cám ơn các đồng nghiệp có tên trong tài liệu tham khảo đã trích dẫn.
Mọi ý kiến đóng góp xin gửi về Công ty c ổ phần Sách Đại học “ Dạy nghề, Nhà xuất bản Giáo dục Việt Nam, 25 Hàn Thuyên, Hà Nội. Điện thoại (04)38264974.
Xin trân ừọng cảm ơn.
TÁC GIẢ
Phần một
■xử LÝ SỐ LIỆU NGHIÊN cúu BẰNG PHẨN NIỂNI
EXCEL
Chương 1
GIỚI THIỆU VÈ MICROSOFT EXCEL
1. CÁC KHÁI NIỆM Cơ BẢN
Microsoft Excel là một phần mềm dùng để xử lý bảng tính, nó có thể
tạo ra các bảng tính điện tử với kích thước rất lớn, Trong bảng tính, người sử dụng (NSD) có thể thực hiện nhanh chóng các phép tính, sáp xếp và khai thác các cơ sở dữ liệu. Microsoft Excel được viết để sừ dụng trong môi trường Windows. Cách khởi động và thoát khỏi Excel cũng theo nguyên tắc chung như đối với các chương trình ứng dụng khác. Sau khi khởi động,
Excel sẽ hiển thị cửa sổ như trên hình 1.1.
1.1. Cửa sổ Microsoft Excel
Các thành phần của cửa sổ Microsoft Excel gồm:
- Thanh tiêu đề {Title Bar) với tên chương trình là Microsoft Excel. - Thanh thực đơn {Menu Bar).
- Thanh dụng cụ Toolbars có hai hàng chứa nút lệnh, hàng trên là
Standard Toolbar và hàng dưới Formatting Toolbar. Tên của các nút lệnh
sẽ hiện ra mỗi khi người sử dụng đưa con trỏ chuột đến nút lệnh. *
- Thanh công thức {Formula Bar) hay còn gọi là dòng nhập dữ liệu nằm dưới Toolbar.
- Vùng làm việc (Workbook) và bảng tính. - Danh sách các Module hiện tại.
- Dòng ưạng thái (nằm dưới cùng cùa màn hình).
Dòng thực đơn chứa tên cùa các nhóm lệnh chính và cơ bản nhất của
Excel. Thực đom chính của Excel bao gồm: File, Edit, View, Insert, Format, Tools, Data. Window, Help. Mỗi khi người sử dụng nhấn chuột trên tên của
một nhóm lệnh, một danh sách các lệnh sẽ mở ra để nguời sừ dụng chọn tiếp. Các lệnh cụ thể của thực đơn sẽ được mô tả lần lượt trong giáo trình.
1.2. Bảng tính (Sheet)
li’l U i l I B M M H I P M I I I I 'll 'HI!
Fie £dt View imert Fermat Iools Bata jiflndow Help
jiMiaM' .nMr u i~ẾịĩiU
zi gK?l
Hình 1.1. Cửa sổ Microsoft Excel
Bảng tính (Sheet) là phần chính của màn hình Excel. Bảng tính bao gồm các hàng (Row) và cột (Column). Chiều rộng mặc định của cột là 9 ký tự, người sử dụng có thể thay đồi đến 255 ký tự. Các hàng được đánh số từ 1 đến 65536, các cột được đánh theo A, B, c,..., z, AA,..., AZ, BA,..., IV (tối đa là 256 cột). Nơi giao nhau của hàng và cột gọi là ô (Ceỉỉ).
- Địa chỉ của ô bao gồm địa chỉ cột và hàng (Ví dụ: A 10, C12). Trong khi nhập dữ liệu, NSD thường phải sử dụng địa chỉ ô hoặc vừng để tham chiếu đến các ô hoặc vùng khác.
- Cách viết địa chỉ thông thường được hiểu là tương đối với ô hiện tại.
Vỉ dự. Trong bảng tính tại ô C3> NSD nhập công thức = A3 + B3 (theo
cách ghi địa chỉ tương đối).
- Đê chỉ địa chỉ tuyệt đối của ô, cần thêm ký tự $ đứng trước địa chỉ (ví
dụ: $D$12). Như vậy, nếu trong ô C3 có công thức = $A$3 + $B$3, đây là
- Trên thực tế, địa chi ô cũng có thể là hỗn hợp cả tuyệt đối và tương đôi, vỉ dụ như A$2; với loại địa chỉ này, khi thực hiện lệnh Copy thì chỉ có địa chỉ cột chạy, còn địa chỉ hàng sẽ cố định.
- Trong Excel, thường sử dụng khái niệm vùng dữ liệu trên bảng tính. Vùng là một miền chữ nhật trên màn hình chứa một số ô liên tục. Địa chỉ của vùng được ghi theo địa chỉ của hai ô đối diện (đỉnh ưên bên trái và đỉnh
dưới bên phải của hình chữ nhật) cách nhau bởi dấu (v/ dụ A2:F15).
1.3. Tệp hồ sơ (Workbook)
Trong Excel mỗi File dữ liệu bao gồm nhiều Module tập hop lại. Mỗi
Module này có thể là bảng tính (Sheets), bỉểu đồ {Chart), một hình vẽ hay là
một Module chứa các hàm thủ tục được viết bằng ngôn ngữ Visual Basic và là bộ phận của Workbook. Toàn bộ các Module này tập hợp trong một File được gọi là Workbook, số lượng các Module trong một Workbook không hạn chế. Giá trị mặc định cho số lượng bảng tính trong một Workbook là 3, NSD có thể thay đổi đến 255 bảng tính bàng lệnh insert/worksheet. Danh sách các bảng tính được liệt kê tại vị trí góc trái dưới màn hình, NSD có thể thực hiện các thao tác với các Module này như đặt tên, xoá, tạo mới. Trong giáo trình này sẽ trình bày chủ yếu các thao tác với Module dạng bảng tính ( Worksheet).
1.4. Dữ liệu (Data)
Dữ liệu được nhập từ bàn phím, nó được thể hiện đồng thời trên thanh công thức và trong ô hiện tại. Con trỏ nhấp nháy xuất hiện ngay trên thanh công thức để NSD thực hiện các thao tác hiệu chỉnh trực tiếp với số liệu và các ký tự của dữ liệu. Khi NSD nhập dữ liệu vào dòng nhập, Excel tự động nhận biết kiểu dữ liệu.
Excel phân biệt các loại dữ liệu chính sau đây:
- Dữ liệu dạng chữ: Gồm các ký tự từ A đến z và các dấu căn lề là \ ", A, \. Dạng mặc định của Excel là căn lề trái với ký tự ’ định sẵn đứng trươc các ký tự chữ. Quy ước của các dấu căn lề trong ô như sau: ’ là căn lề trá i," là căn lề phải, A là căn chính giữa và \ là lặp lại ký tự sau dấu này đến hết chiều rộng của ô. Việc căn lề cổ thể xử lý nhanh bàng các nút ỉệnh trên thanh Formatting Toolbar sau khi đó đánh dấu ô hoặc vùng.
- Dữ ỉiệu dạng số: Phải bắt đầu bằng các chữ số từ 0 đến 9 và các dấu
+ Dạng mặc định của chữ số là General và căn lề bên phải, muốn
thay đổi làm giống dữ liệu dạng chữ trên thanh công cụ. Nếu nhập toàn số nhưng muốn Excel hiểu là các ký tự chữ thì NSD phải bắt đầu bằng dấu
- Dữ liệu
dạng công
thức: Quy ước công thức phải được bắt đầu bằng dấu =. Khi nhập công thức vào một ô thì kết quả của công thức được gán cho ô đó và công thức sẽ được lưu giữ trên thanh công thức (mỗi khi ô kết quả được đánh dấu thì công thức gốc của nó được hiện ra trên thanh công thức). Các phép toán sừ dụng trong công thức bao gồm:- Các toán tử tính toán: + (cộng), - (trừ),/(chia), A (luỹ thừa), * (nhân). - Toán tử so sánh: = (bằng), o (không bằng), > (lớn hom), >= (lớn hơn hoặc bằng), < (nhỏ hơn), <= (nhỏ hơn hoặc bằng).
Vỉ dụ: Công thức = AI >= 20 cho kết quả: TRUE (đúng) hoặc FALSE
(sai).
- Toán tử liên kết: & (liên kết chuỗi ký tự chữ),
Vỉ dụ: Công thức = "Human" & "biology" cho kết quả: Humanbioỉogy.
Và các phép toán logic, các biểu thức, các hàm,... Khi thực hiện tính toán, các toán tử được ưu tiên theo thứ tự: biểu thức trong dấu ngoặc đơn (...), luỹ thừa, nhân, chia, cộng, trừ. Đối với dữ liệu dạng ngày, tháng, Excel quy ước thể hiện như sau:
2. CÁC THAO TÁC c ơ BÀN TRONG BÀNG TỈNH 2.1. Các lệnh di chuyển CO’ bản
Có thể di chuyển con trỏ nhấp nháy trong bảng tính bằng các mũi tên di
chuyển trên bàn phím í , i , hoặc NSD dùng kết hợp các phím đặc
biệt với các mũi tên:
Home, End + <—: v ề cột đầu tiên (cột A).
End + —►: v ề cột cuối cùng (cột IV).
PgUp, PgDn: Lên, xuống một ừang màn hình.
F5: Goto (Sau khi nhấn F5, nhập địa chỉ ô và nhấn ENTER).
+ m/d/y + d-m m m -yy + d-mmm + mmm-yy + m/d/y h:mm + h:mm:ss AM/PM + h:mm + h:mm:ss Ví dụ: 1/1/09 l-Jan -0 9 1-Jan Jan-09 1/1/09 15:00 1:00:00 PM 15:00 15:00:02
2.2. Đánh dấu ô, cột, hàng và khối dữ liệu
a) Đánh dấu ô
Có thể đánh dấu một ô, một số ô liền nhau trên một hàng (hoặc cột) hay nhiều ô không liền nhau như sau:
- Để đánh dấu một ô NSD nhấn nút chuột trái (gọi tắt là nhấn chuột) trên ô đó. Khi bị đánh dấu, ô sẽ được bao bởi một khung đen.
- Để đánh dấu một số ô liền nhau trên cùng một hàng (hoặc cột) NSD nhấn và rê chuột từ ô đầu tiên đến ô cuối cùng cần đánh dấu. Các ô được đánh dấu sẽ bị bôi màu.
- Để đánh dấu nhiều ô rcri rạc, NSD nhấn chuột trên ô đầu tiên định chọn, nhấn và giữ phím CTRL trong khi nhấn chuột tại các ô định chọn tiếp theo. Các ô được đánh dấu sẽ bị bôi màu.
b) Đảnh dấu cột
Trong Excel hàng trên cùng (gọi là hàng tiêu đề cùa bảng tính) gồm các ô đứng đầu của các cột. Các ô này được đánh dấu lần lượt bàng các chữ cái A, B, c , D,... theo thứ tự từ trái qua phải và được gọi là ô tiêu đề cột. Muốn đánh dấu cột, NSD phải nhấn chuột trên ô tiêu đề cột. NSD có thể đánh dấu một cột, một số cột liền nhau hoặc đánh dấu nhiều cột không liền nhau.
- Đẻ đánh dấu một cột, NSD nhấn chuột trên ô tiêu đề của cột đó. Khi bị đánh dấu, cột sẽ đổi màu (hình 1.2).
É Microsoft Excel ' Bookl
ffỉte £dlt ỵievv Insert Fflrmat loots Bata Window iietp 1
y X é .. u A 1 đ i M ' i m Í Ề Arial * 10 -■ i B I u [ Ẹ g m á g ị í % • Too i í ỉ i t C1 £ Ị A ị B « D r E Ị F T G 1 H 1 2 3 4 v r 6 ? : 8 9 10 11 12 13
- Để đánh dấu nhiều cột liền nhau, NSD nhấn và rê chuột từ ô tiêu đề của cột đầu tiên đến ô tiêu đề của cột cuối cùng rồi nhả chuột. Khi các cột bị đánh dấu chúng sẽ được đổi màu.
- Để đánh dấu nhiều cột không liền nhau, NSD nhấn chuột trên ô tiêu đề của cột thứ nhất, tiếp theo NSD nhấn và giữ phím CTRL trong khi nhấn chuột trên ô tiêu đề của các cột khác cần đánh dấu. Khi các cột bị đánh dấu chúng sẽ được đổi màu.
c) Đánh dẫu hàng
Trong Excel, cột đầu tiên bên trái (gọi là cột tiêu đề của bảng tính) gồm các ô đứng đầu của các hàng. Các ô này được đánh dấu lần lượt bàng các số 1, 2, 3,... theo thứ tự từ trên xuống, ta gọi chúng là ô tiêu đề hàng. Muốn đánh dấu hàng, NSD phải nhấn chuột trên ô tiêu đề của nó. NSD có thể đánh dấu một hàng, một số hàng liền nhau hoặc đánh dấu nhiều hàng không liền nhau.
- Để đánh dấu một hàng, NSD nhấn chuột trên ô tiêu đề cùa hàng đó. Khi bị đánh dấu, hàng sẽ được đổi màu (hình 1.3).
Microsoft Excel - Bookl 1
! 0 t i k 6 « yioo Insert Ffimiat Iooỉs Data Window Help
i J J ìA . i J J A \ 7 . Ỉ U ặ * '■£>' J , :: - ; A . ? \ ì i i i « 1 Arial * 10 »1 Ị /
... ...- ... _ J
G n u
Hình 1.3. Đánh dấu hàng, hàng đố đồi màu
- Để đánh dấu nhiều hàng liền nhau, NSD nhấn và rê chuột từ ô tiêu đề của hàng đầu tiên đến ô tiêu đề cùa hàng cuối cùng rồi nhả chuột. Khi các hàng bị đánh dấu chúng sẽ được đổi màu.
- Để đánh dấu nhiều hàng không liền nhau, NSD nhấn chuột trên ô tiêu đề của hàng thứ nhất, tiếp tục NSD nhấn và giữ phím CTRL trong khi nhấn chuột trên ô tiêu đề của các hàng khác cần đánh dấu. Khi các hàng bị đánh dâu chúng sẽ được đổi màu.
d) Đảnh dấu khối
Khối là một vùng chữ nhật trên bảng tính. Việc đánh dấu khối được tiến hành theo các bước:
- Đánh dấu ô ở một góc của khối.
- Nhấn và giữ phím SHIFT đồng thời sử dụng các phím mũi tên 4 - , Ú .
Để mở rộng khung bao quanh ô tới vị trí góc đối diện của khối, nhấn và giữ nút trái chuột đồng thời rê chuột tới vị trí góc đối diện của khối và thả chuột (hình 1.4). i " ____ 10 1
i i i S M S i
- ■ T T x : : : _ 12 13 14 IS.Hình 1.4. Đánh dáu khối, cả khối đó đổi màu
e) Các phương pháp đặc biệt
- Chọn một vùng liên tục: Đánh dấu ô góc trái trên cùa khối, nhấn và
giữ SHIFT, nhấn chuột tại ô đối diện ở góc phải của khối.
- Chọn các vùng rời rạc: Chọn vùng đầu, nhấn và giữ CTRL rồi nhấn và rờ chuột ở các vùng khác.
- Chọn toàn bộ bảng tính: nhấn tổ hợp phím CTRL + SHIFT + SPACEBAR hoặc nhấn chuột tại góc trái trên của bảng tính (là giao điểm của hàng tiêu đề và cột tiêu đề).
2.3. Nhập và hiệu chỉnh dữ liệu
a) Nhập đừ liệu vào bàng tỉnh
Dữ liệu được nhập vào các ô đã đánh dấu (ô được viền khung đen). Nhập dữ liệu vào bảng tính theo thứ tự:
- Nhấn chuột vào ô cần nhập để đánh dấu ô (nếu như trước đó ô chưa được đóng khung đánh dấu).
- Nhập dữ liệu từ bàn phím theo dạng thức quy định (xem mục 1.4). - Kết thúc nhập dữ liệu vào ô hiện tại bằng cách:
+ Nhấn phím ENTER, hoặc + Nhấn chuột vào ô khác, hoặc
+ Dựng các mũi tên di chuyển trên bàn phím để di chuyển ô hiện tại sang vị trí khác.
b) Xoá và hiệu chỉnh dữ liệu
Sau khi nhập dữ liệu vào bảng tính, NSD có thể xoá hoặc hiệu chỉnh chúng.
Xoá dữ liệu: Để xoá dữ liệu, NSD thực hiện các bước:
- Đánh dấu bằng cách bôi đen vùng dữ liệu muốn xoá (nhấn và rê chuột). - Nhấn phím DELETE từ bàn phím, hoặc thực hiện lệnh Edỉt/Cỉear, một khung chứa một số tuỳ chọn để chọn sẽ xuất hiện, trong đó NSD có thể xoá cả dữ liệu và định dạng (chọn Aỉĩ), chỉ xoá định dạng (chọn Format), hoặc chỉ xoá dữ liệu (chọn Content).
c) Sử dụng Fill Handle
Kỹ thuật điền dữ liệu bằng Fill Handle đom giản hơn so với việc sử dụng các lệnh từ dòng thực đom và tiết kiệm được thời gian. Fill Handle là một dấu vuông đen nhỏ ở góc dưới bên phải cùa ô hiện tại (ô được viền khung đánh dấu) như trên hình 1.5.
*
mmpmtmmm-
■
■
-li
wặ
1
3
— -- --- - ---.... ... . .. Hình 1.5. Công cụ Fill HandleThao tác với Fill Handle như sau: Đưa con trò chuột đến dấu hiệu của
Fill Handle, con trỏ chuột sể có hình đấu cộng đen thẫm, khi đó kéo rê con
2.4. Chèn, huỷ ô, hàng hoặc cột
a) Chèn thêm ô
Để chèm thêm ô vào bảng tính, NSD thực hiện các bước: - Đánh dấu vị trí ô noi NSD cần chèn thêm ô mới. - Thực hiện lệnh Insert/Celỉs, hộp thoại Insert xuất hiện.
Hình 1.6. Hộp thoại Insert
- Trong hộp thoại Insert (hình 1.6), NSD tiến hành lựa chọn một trong những phương án để dịch chuyển ô đánh dấu:
+ Shift Cells Right: Đẩy ô hiện tại sang phải. + Shift Cells Down: Đẩy ô hiện tại xuống dưới.
+ Entire Row: Chèn thêm một hàng ừổng vào vị trí hiện tại. + Entire Column: Chèn thêm một cột trống vào vị trí hiện tại. - Nhấn nút OK để kết thúc.
b) Chèn thêm hàng, cột
Các thao tác chèn thêm hàng, cột thực hiện như sau:
- Đánh dấu hàng (hoặc cột) nơi NSD muốn một hàng (một cột) mới sẽ xuất hiện.
- Thực hiện lệnh Insert/Rơw, nếu NSD muốn chèn thêm hàng; hoặc chọn lệnh Insert/Column, nếu NSD muốn chèn thêm cột.
Chủ ỷ:
- Thay vì phải đánh dấu cả hàng (hoặc cột), NSD chi cần đánh dấu một
ô của một hàng (hoặc cột).
- Nếu NSD chèn thêm hàng mới thì hàng hiện tại sẽ bị đẩy xuống dưới. - Nếu NSD chèn thêm cột thì cột hiện tại sỗ bị đẩy sang phải.
c) Huỷ bỏ ô, hàng hoặc cột
Để huỷ bỏ ô của bảng tính, NSD thực hiện các thao tác: - Đánh dấu đối tượng cần huỷ bỏ.
- Thực hiện lệnh Edit/Delỉete. Sau lệnh này:
+ Nếu đối tượng đã đánh dấu là một hàng thì hàng đó bị huỷ và các hàng dưới của nó được dịch lên.
+ Nếu đối tượng đã đánh dấu là một cột thì cột đó sẽ bị huỷ và các cột bên phải nó sẽ dịch chuyển và lấp đầy bảng tính.
+ Nếu đối tượng đã đánh dấu là một ô thì Excel hiển thị hộp thoại như hình 1.7. Trong hộp thoại Delete có các lựa chọn:
Shift Cells Left: huỷ bỏ ô hiện tại và đẩy ô bên phải sang bên trái. Shift Cells Up: huỷ bỏ ô hiện tại và đẩy ô phía dưới lên.
Hình 1.7. Hộp thoại Delete
Entire Row: Huỷ cả hàng hiện tại chứa ô có đánh dấu. Entire Column: Huỷ bỏ cả cột hiện tại chứa ô có lựa chọn.
- Sau khi lựa chọn các tham số, NSD nhấn nút OK để huỷ đối tượng có
đánh dấu.
3. Sử DỤNG BÀNG TÍNH VÀ HÀM GIẢI CÁC BÀI TOÁN SINH HỌC 3.1. Tính toán bằng công thức tự tạo
3.1.1. Nhập công thức vào ô chứa kết quả và sao chép công thức
Khi nhập công thức vào một ô và nhấn phím ENTER thì kết quả của phép tính sẽ được đặt vào trong ô đó. Giả sử có một bàng gồm các cột A, B, c , D, đã được điền đầy dữ liệu dạng số và NSD muốn trong các hàng của cột E sẽ là kết quả của các phép nhân giữa các số liệu tương ứng trong cột c
Bước thứ nhất, là tạo một số liệu trong một ô của cột E bằng công thức
nhân giữa hai ô tương ứng cùng hàng của cột A và cột c (các địa chỉ tham chiếu trong công thức này là địa chỉ tương đối).
Bước thứ hai, là sao chép công thức nhân đến các ô khác của cột E,
NSD hãy thực hiện các thao tác sau:
1) Trong một ô nào đó của cột E, vỉ dụ ô E l, NSD nhập công thức ” C1*A1.
2) Nhấn ENTER, kết quả của phép nhân giữa hai ô C1 và AI sẽ xuất hiện trong ô El.
3) Nhấn chuột trến ô E1 để đánh dấu rồi nhấn nút Copy để sao chép công thức.
4) Nhấn chuột trên một ô khác của cột E (ví dụ ồ E2) để đánh đấu rồi nhấn tiếp nút Paste trên thanh công cụ, kết quả phép nhân của hai ô A2 và C2 được điền vào ô E2.
5) Nhấn chuột trên ô tiếp theo để đánh dấu nó và nhấn nút Paste để chép tiếp kết quả. Thực hiện tương tự với các ô còn lại để kết thúc.
3.1.2. Tự động điền kết quả tính toán
Trong rất nhiều trường họp, dữ liệu của một dãy ô thuộc một cột (hoặc một hàng) là kết quả của một phép tính theo một công thức với tham số khác nhau.
Trong những trường hợp như vậy, việc sao chép công thức và chỉ điền kết quả vào bảng tính sẽ làm giảm thời gian nhập dữ liệu. NSD có thể sao chép công thức bằng cách tự động: Sử dụng lệnh từ dòng thực đơn hoặc dùng Fill Handle:
- Sử dụng lệnh từ dòng thực đơn: Sau bước 2 ở mục 3.1.1, NSD thực
hiện tiếp cảc thao tác dưới đây:
+ Rê chuột từ ô El qua các ô còn lại của cột E để xác định vùng điền dừ liệu.
+ Thực hiện lệnh Edit/FỉU/Down} Excel sẽ tự động điền kết quả của các phép tính vào các ô còn lại của cột E.
- Dùng Fill Handle: Để dùng Fill Handle cho ví dụ trên, sau bước 2 mục 3.1.1, NSD hãy nhấn chuột để đánh dấu ô E1 và rê Fill Handle đến hết những ô còn lại của cột E. Sau thao tác này Excel sẽ hoàn tất việc điền dữ liệu cho các ô còn lại.
3.1.3. Phương pháp Pointing
Đẻ thực hiện phép tính giữa hai hoặc nhiều ô, NSD có thể dùng phương pháp Pointing. Giả sử cần đặt kết quả của phép cộng giữa số liệu của ô A62 với ô C5 vào trong ô E5, NSD làm như sau:
- Trong ô E5 nhập dấu
- Nhấn chuột trên ô A62, khi đó Excel sẽ ghi địa chỉ của ô này vào công thức ở 5 E5.
- Nhập tiếp dấu "+" ưong ô E5.
- Nhấn chuột trên ô C5, Excel sẽ ghi tiếp địa chi của ô này vào công thức. - Nhấn ENTER để kết thúc.
Phương pháp Pointing được áp dụng khi NSD không nhớ địa chỉ của ô tham chiếu trong phép tính. Nó được dùng với các phép tính cộng, trừ, nhân,
chia và khi có nhiều ô tham chiếu.
3.1.4. Những thủ thuật sao chép đặc biệt
Excel có một thủ thuật đặc biệt ttong việc sắp xếp lại dữ liệu. Lệnh Edit/Paste Special cho phép thực hiện một số công việc như:
- Chọn kiểu sao chép: Chỉ sao chép giá trị, công thức, định dạng hay ghi chú.
- Lấy những dãy dữ liệu ban đầu và thực hiện tự động các phép tính với những ô ở nơi đến.
Dưới đây xẻt trên những vị dụ cụ thể. Trước hết, NSD chuẩn bị một bảng tính như sau:
- Trong ô AI nhập số 1, ô A2 nhập số 2, đưa dấu nhắc chuột về ô AI và rê đến ô A2. Dùng chức năng Fill Handle điền tiếp đến ô A7, được dãy số liệu 1, 2 ,3 ,4 , 5, 6, 7.
- Trong ô B2 nhập 2, ô C2 nhập 5.
- Dùng Fill Handle (chọn B2:C2 và rê Fill Handle đến G2) để tạo một dãy số tăng dần (2, 5, 8, 11, 14, 17).
- Trong ô B3 nhập 5, ô C3 nhập 9.
- Dùng Fill Handle để điền dãy số tăng đần đến G3: 5 ,9,13,17, 21,25. - Trong ô B4 nhập công thức = A2 * A3 và nhấn ENTER.
- Dùng Fill Handle để sao chép công thức đến những ô còn lại cho tới B7.
Kết quả của một số thao tác trên đây là một bảng tính với các số liệu như ưên hình 1.8.
Hình 1.8. Dữ liệu đẻ làm ví dụ cho các thủ thuật dán đặc bỉệt
a) Đản giá trị của kết quả tỉnh theo công thức
Excel cho phép sao chép một dãy công thức (công thức giống nhau
nhưng tham chiếu khác nhau) và chỉ dán những kết quả. Vỉ dụ, NSD chọn một dãy công thức mà kết quả là những giá trị số và sao chép những công thức này vào bộ nhớ đệm. Tiếp theo, NSD đánh dấu các ô nơi đến, sử dụng lệnh Edit/Paste Special và chọn Values trong hộp thoại rồi nhấn OK. Những ô đến sẽ chi chứa kết quả chứ không chứa công thức như ở ô ban đầu.
Chủ ỷ: Nếu ô có chứa công thức thì khi ô bị đánh dấu, trên thanh công
thức sẽ xuất hiện công thức đã tạo ra giá trị số ghi trong ô. Những ô không chứa công thức, khi bị đánh dấu thì trên thanh công thức xuất hiện giá trị số của ô đó. Trong bảng tạo ra bằng các thao tác ở trên (hình 1,8) các ô trong hàng 2 và 3 không chứa công thức, các ô trong hàng 4 chứa cả giá trị số và công thức. Để minh hoạ cho việc dán giá trị của kết quả, NSD hãy thực hiện các thao tác dưới đây:
- Trong bảng tính đã chuẩn bị (ví dụ hình 1.9), NSD đánh dấu các ô B4:B7 (bằng cách nhấn chuột trên ô B4 rồi nhấn SHIFT ừong khi nhấn trên các ô tiếp theo trong vùng này), sau đó nhấn tổ hợp phím CTRL + c để
Copy chúng.
- Nhấn chuột trên ô C4 để đánh dấu nó.
- Thực hiện lệnh Edit/Paste Special, sẽ xuất hiện hộp thoại như trên hình 1.9.
- Trong hộp thoại Paste Special NSD chọn Values và nhấn OK.
Kết quả trong dãy C4:C7 bây giờ là dãy số 6, 12, 20, 30 giống hệt như dãy B4:B7, chỉ khác một điều là những ô trong dãy mới không chứa công thức.
T H Ữ V ỉỂn I
Hình 1.9. Hộp thoại Paste Special
b) Thực hiện cảc phép tính
Excel cho phép sao chép một dãy các số (dãy có thể là các số nhập từ
bàn phím hoặc là dãy tạo ra từ các công thức) rồi cộng, trừ, nhân, chia các số này với một dãy khác. Để hiểu rõ hơn, NSD hãy thực hiện các thao tác sau (làm với số liệu ở hình 1,10):
- Chọn vùng AI :F1, có giá trị là: 2, 4, 6, 8, 10, 12. - Nhấn CTRL + c để sao chép chúng vào bộ nhớ đệm.
- Đánh dấu ô A2 cùa vùng A2:F2 (đang chứa các số 2, 12, 30, 56, 90, 132).
- Thực hiện lệnh Edit/Paste Special, chọn Divide trong khung
Operations của hộp thoại Paste Special và nhấn OK. Excel sẽ lấy những số
trong các ô đến (2, 12, 30, 56, 90, 132) và chia nó cho những ô trong bộ nhớ đệm (2,4, 6, 8, 10, 12).
Hình 1.10. Bảng số nguồn đẻ thực hiện phép tính bằng lệnh EdiưPaste special
Kết quả cùa phép chia hai dãy số trong dãy B2:F2 sẽ là giá trị thương số của các ô tương ứng: 1, 3, 5, 7,9, 11 như ở trên hình 1.11. Phương pháp này áp dụng cho các phép tính cộng, trừ, nhân hay chia một dãy số với một dãy số khác.
B E8 1 4! ^ 6 * 8! 10! 121 ' 1 SỆệ 1j ’ 3 j ' Z — c r I ' - J ' l 9i 11 ị ; ị m ; ị ___;____...j_ ... ! 1 $ * I ỉí ị ị
Hình 1.11. Kết quả chia hai dãy số bằng Paste Special 3.2. Các hàm cơ bản của Excel
Mỗi hàm là một công thức đã định trước, được sử dụng để tính toán hoặc thực hiện các thao tác trong bảng tính. NSD có thể dùng các hàm có sẵn của Excel hoặc tự tạo ra hàm của mình. Dưới đây chỉ nói đến các hàm có sẵn của Excel, thường được gọi là hàm bảng tính.
Dạng tổng quát của hàm trong Excel là:
= tên hàm (đối số) J
trong đó đối số nằm trong ngoặc đơn, có thể không có hoặc có một hay có
nhiều đối sổ và cách nhau bởi dấu Đối số có thể là hằng, công thức hay
biểu thức, tham chiếu tới ô hay là tới một công thức khác. Khi sử dụng các hàm trong hàm lồng nhau thì có tối đa là 7 mức lồng nhau, và không cần
dùng đấu ’ ở các hàm bị lồng bên trong hàm khác.
Chú ỷ: Không được có dấu cách trong ỉời gọi hàm.
Trong các mục tiếp theo giới thiệu các hàm toán học và hàm thống kê thường dùng trong Excel Các hàm được liệt kê theo thứ tự chữ cái.
3.2.1. Một số hàm thếng kê
Tên hàm Ý nghĩa
AVERAGE(ni, n2l...): Tính trung binh cộng của các số ni, n2l...
COUNT(DC); Đếm số các ô cỏ dữ liệu loại sổ trong vùng DC.
COUNTA(DC): Đếm số các ồ không rỗng trong vùng DO.
LARGE(DC, k): Phần tử lớn thứ k của vùng DC.
MAX(ni,n2,...): Giá trị lớn nhẩt cùa các số n-1, rì2,...
MEDIAN(m, n2l...): Cho số gần vởi giá trị trung bình của dãy sổ ni. ri2,...
MIN(m, n2,...): Giá trị nhỏ nhất của các số rvi, ri2,...
MODE(DC): Lấy giá trị hay gặp nhất trong vùng DC.
SMALL(DC, k):,... Phần tử nhỏ thứ k trong vùng DC....
Tên hàm Ỷ nghĩa CHl!NV(probabilỉty, df)
CONFIDENCE(anpha, standard_dev( size) HARMEAN (numbeii, number2,...)
STDEV (numberl, number2,...) STDEVP(number1, number2,...) TDIST (X , df, tail)
TINV (probability, df) VAR (numbert, numberê,...) VARP (numberl, number2t...)
Giá trị của hàm phân bổ xác suất X 2
Giá trị khoảng tin cậy của trung bỉnh tổng thẻ Trung bình điều hoà của một dãy số liệu Độ lệch chuẩn của mẫu
Độ lệch chuẩn cùa tồng thẻ Phân bổ t Student's
Giả trị đảo của phân bố t Student's Giá trị phương sai của mẫu Giá trị phương sai của tổng thể
3.2.2. Một sổ hàm toán học
Tên hàm Ý nghĩa
ABS(x) Giá trị tuyệt đối của sổ X.
ACOS(x) Hàm arccos của X.
ASIN(x) Hàm arcsỉn của X.
ATAN(x) Hàm arctang của X.
CEILING(x) Làm trỏn trên tới số nguyên gần nhất của X.
COS(x) Hàm COS của góc X (theo radian).
COSH(x) Hàm COS h y p e b o n cùa g ò c X.
EXP(x) Hàm mũ ex.
FLOOR(x, m) Làm tròn sổ X tới bội gần nhất của m; m > 0.
Vĩ dụ: FLOOR(728, 5) tà 725.
INT(X) Làm tròn dưới tới số nguyên gần nhẩt của X.
LN(X) Logarit tự nhiên của X (X > 0).
LOG(x, cs) Logarit cơ số cs của X (cs > 0, cs * 1), X > 0.
LOG1Ũ(x) Logarit cơ số 10 cùa X (x > 0).
MDETERM(DC) Hạng (rank) ma trận của vùng DC.
MINVERSE(DC) Ma trận ngược của DC.
MMULT(Mi, M2) Tích hai ma trận Ml, M2.
Tên hàm Ý nghĩa
Pl() Sổ Tí (không cỏ đối số).
RANDO Số ngẫu nhiên giữa 0 và 1 (không có đối sổ).
ROUND(x, n) Lảm tròn X vớ i độ c h ín h x á c đ ến n c h ữ sổ th ậ p p h â n .
SIGN(x) Hàm tỉm dẩu của một sổ (bằng 1, 0, -1, tương ứng với X là dương,
0, âm).
SIN(x) Hàm sin của X (theo radian).
SINH(x) Hàm sin hypebon của X (x là số thực).
SQRT(x) Hàm cản bậc hai của X (x>0).
SUM(m, n2,...) Tổng của các số n i,
TAN(x) Hàm tang của góc X (theo radian).
TANH(x) Hàm tan hypebon của X (x tà số thực).
TRUNC(X) C ắ t lấ y p h ầ n n g u yê n c ủ a số X.
3.2.3. Nhập hàm vào bảng tính
a) Nhập trực tiếp từ bàn phím
Giả sử cần tính tổng cùa các số trong vùng AI :A10 và đặt kết quả vào ô AI 1, NSD thực hiện như sau:
- Nhấn chuột trên ô AI 1 để xác định ô chứa kết quả.
- Nhập công thức = SƯM(A1:A10) và nhấn ENTER, Excel sẽ đira kết quả cùa phép cộng vào ô AI 1. Trong khi nhập công thức vào ô chứa kết quả thì đồng thời trên thanh công thức cũng xuất hiện công thức đó với con trỏ nhấp nháy. Nhờ con trỏ nhấp nháy này, NSD có thể chỉnh sửa lại các công thức trực tiếp trên thanh công thức.
b) Dùng Function Wizard
NSD có thể sử dụng Function Wizard để sao chép công thức từ hộp thoại Function Wizard dán vào ô chứa kết quả và vào thanh công thức, sau đó chỉnh sửa đối số. Để sử dụng Function Wizard, NSD thực hiện các thao tác:
- Đánh dấu ô chứa kết quả.
- Thực hiện lệnh Insert/Function từ dòng thực đom hoặc nhấn nút Function
Wizard (biểu tượng là fx) trên thanh công cụ, Excel hiển thị hộp thoại Function Wizard như trên hình 1.12.
- Trong khung Or select a category, NSD chọn AU và trong khung
select a function, NSD chọn SUM (hình 1.12).
- Insert Function V .
Sew difarabndlont
Type a bhef description «f«èatycu want tD do and twn 11^ -.a*)-, 1
dtek Go ___ _ ___ _ _ i _ „ a K te a a ptegory: [Ã* ScfcdBfijvttoa: SUBSTITUTE SUBTOTAL SUMff SUVROOLCT SUMSQ SU M xiw -SUH(— i
Addi «1 Ihe number* b a m geofedb.
H cbsntfm fuoagn 1. OK J 1 .. . O K * ; . 1 ' ! 1 1 i. L 1 - Function Arguments SUM *jrtwr2: [ ^ ] -- 8.Ĩ Adji «1 the nu*cr« h ■ rmge of <xk.
NMaberfc>iMberl,fij«ber%... are 1 to X r u i t e n ID M l. « ầ i n and text are Ignored In cdhi mckded if typed aa vgunentr
ftmM I**ưt • S.2
Metooo kneton 1 at I I C m ri 1 ■
Hình 1.12. Hộp thoại Function Wizard Hình 1.13. Hộp thoại Function Wizard khai báo địa chỉ ô của dữ liệu
- Nhấn nút OK, hộp thoại sẽ đóng lại, xuất hiện hộp thoại khai báo địa chỉ ô cùa dữ liệu; NSD tiến hành nhập tham số của công thức vào các khung rỗng, điền xong nhấn nút OK để kết thúc (hình 1.13).
3.3. ứng dụng Exce/để giải các bài toán biến động quần thể
- Sử dụng bảng tính Excel để giải các bài toán trong nghiên cứu sinh học bằng việc thiết lập các công thức hoặc sử dụng các hàm có sẵn. Trong giáo trình này gợi ý một số dạng bài toán sau đây:
a) Dạng bài toán biến động quần thể theo thời gian. b) Dạng bài toán biến động quần thể theo thế hệ. c) Dạng bài toán mô hình biến động quần thể.
Yêu cầu chung cùa việc giải các dạng bài toán trên bao gồm : 1) Phân tích được đặc điểm sinh học của bài toán.
2) Diễn giải các đặc điểm vừa phân tích ữên màn hình Ecxel theo cấu trúc hàng và cột.
3) Khai báo chỉ trên 3 hàng, trong đó:
- Hàng 1 là các đặc điểm của bài toán. - Hàng 2 khai báo số liệu ban đầu.
- Hàng 3 khai báo thuật giải của bài toán (các công thức kết hợp các hàm), dùng công cụ Fill Handle để giải.
Lưu ỷ: Số liệu ban đầu của bài toán cũng có thể là một vùng như các ví
dụ 2 sau đây. Vi dụ 1.
Để dự báo mật độ côn trùng gây hại trong nông nghiệp nhằm đề phòng và tránh thiệt hại một cách tích cực, người ta nghiên cứu các đặc điểm sinh học của loài côn trùng này và dựa vào đó tính toán khả năng tàng trưởng của quần thể trong một môi trường sinh thái nhất định. Từ kết quả dự báo đỏ, có thể đưa ra biện pháp phòng, trừ hiệu quả nhất. Bài toán dưới đây mô tả quá trình phát triển của một quần thể côn trùng nông nghiệp trong điều kiện môi trường sống ổn định.
Một quần thể côn trùng có tuổi trưởng thành là 4 tuần, số lượng ban đầu là 120 con trưởng thành tuần 1, ấu trùng là 4560 con, số trứng là 65030. Tỷ lệ nở cùa trứng là 0,63, tỷ lệ sống sót của ấu trùng là 0,24; tỷ lệ sống sót đến trưởng thành tuần 2 là 0,75, đến trưởng thành tuần 3 và 4 là 0,37. Mỗi con cái đẻ trung bình 230 trứng/tuần. Tỷ lệ con cái trong quần thể là 4/7. Hãy tính kích thước của quần thể côn trùng này sau 3 tháng. Giả sử rằng thòi gian sống của trứng và ấu trùng cũng là 1 tuần.
Các bước tỉnh toán như sau:
Phân tích đầu bài thấy rằng, đây là sinh vật mà vòng đời có biến thái, các giai đoạn biến thái hoặc phát triển có thời gian sống bằng nhau (1 tuần), do đỏ có thể lấy thế hệ để tính toán là 1 tuần. Sau 3 tháng, nghĩa là sau 12 tuần. Các số liệu cho ban đầu coi như là thế hệ khởi nguyên (thế hệ 0).
Tiêu đề hàng đầu tiên như sau:
- Tại cột A, ô AI ghi: số thế hệ (STH) - Ồ BI ghi: Trứng - Ô C1 g h i: Ấu trùng - Ô DI ghi: Trưởng thành 1 (TT1) - Ô E1 ghi: Trưởng thành 2 (TT2) - Ồ F1 ghi: Trưởng thành 3 (TT3) - Ồ GI ghi: Trưởng thành 4 (TT4) - ô HI g h i: Con cái.
Số liệu khởi nguyên được nhập vào hàng thứ hai:
Hàng thứ 3: • Tại ô A3 nhập công thức = A2 + 1 • Tại ô B3 nhập công thức = H2*230 • Tại ô C3 nhập công thức = B2*0.63 • Tại ô D3 nhập công thức = C2*0.24 • Tại ô E3 nhập công thức = D2*0.75 • Tại ô F3 nhập công thức = E2*0.37 • Tại ô G3 nhập công thức = F2*0.37
• Tại ô H3 thực hiện lệnh Copy từ ô H2 đến ô H3.
Bảng 1.1. Hình ảnh bảng tính khi khai báo 3 dòng đầu
A D ■ E G H 1 STH Trứng Ấu trùng TT1 TT2 TT3 TT4 Con cải 2 0 65030 4560 120 n 3 =A2+1 =H2*230 =B2*0.63 =C2*0,24 =D2*0.75 =E2*0.37 =F2*0.37 n (*) = sum(D2:G2)*4/7; (**) = sum(D3:G3)*4/7.
Kết quả tính toán được trình bày trên bảng 1.2.
Bảng 1.2. Kết quả tính toán kích thước quần thẻ côn trùng sau 3 thảng
A G H 1 STH Trứng Au trùng TT1 TT2 TT3 TT4 Con cái 2 0 65030 4560 120 69 3 1 15771 40969 1094 90 0 0 677 4 2 155664 9936 9833 821 33 0 6107 5 3 1404529 98038 2385 7374 304 12 5757 6 4 1324151 884853 23536 1788 2729 112 16095 7 5 3701787 834215 212365 17652 662 1010 132393 8 6 30450478 2332126 200212 159274 6531 245 209292 9 7 48137211 19183801 559710 150159 58931 2417 440695 10 8 1.014E+08 30326443 4604112 419783 55559 21805 2915005 11 . 9 6.705E+08 63856748 7278346 3453084 155320 20557 6232747 12 10 1.434E+09 4.224E+08 15325619 5458760 1277641 57468 12639708
13 11 2.907E+09 9.031 E+08 1.01E+08 11494215 2019741 472727 65919364
Lưu ý răng, độ rộng của cột mặc định là 9 ký tự, do đó ở các tuần của tháng thứ ba, số lượng cá thể của quần thể rất lớn, muốn thấy được con số trong mỗi ô nên biểu diễn dưới dạng nhân với 10a. Để thực hiện được điều này sử dụng lệnh Format/Cells.
Hộp thoại Format xuất hiện, chọn Number/Scientific, trong cửa sổ
Decimal places chọn 3 hoặc số lớn hơn tuỳ theo kích thước của ô cần Format lại giá trị (xem kết quả trong bảng 1.1). Hàng kết quả cuối cùng là
kích thước quần thể côn trùng đã cho ở các giai đoạn biến thái khác nhau sau 3 tháng.
Vi dụ 2.
Công tác dự báo dân số có ý nghĩa đặc biệt quan trong việc lập kế hoạch trung hạn cũng như dài hạn cho từng địa phương, khu vực hay một quốc gia. Để dự báo dân số sát với quần cư dân cụ thể, cần dựa vào các đặc điểm tự nhiên, xã hội vốn có của chính quần cư dân đó. Bài toán dưới đây là một ví dụ về các đặc điểm nội tại của một quần cư dân.
Kết quả điều tra dân số của địa phương A được cho trong bảng 1.3. Tính tỷ iệ gia tăng dân số của địa phương này theo hàm mũ pt = P0*eRT. Dùng giá trị R trung bình tính được theo số liệu đã cho dự báo dân số của địa phương này đến năm 2050.
Bảng 1.3. Kết quả điều tra dân số của địa phương A (đơn vị triệu người)
Năm 1979 1989 1990 1991 1992 1993 1994 1995 1996
Dân số 1,02 1,95 2,158 2,18 2,205 2,214 2,232 2,251 2,268
Các bước tính toán như sau:
- Bài toán này khác bài 1, các thế hệ không bỉến thái mà liên tiếp nhau, vì thế số cột được sừ dụng không nhiều.
- Chỉ tiêu gia tăng dân số tính cho từng năm rồi lấy giá trị trung bình từ 1979 đến 1996. Sử dụng R trung bình này để dự báo cho đến năm 2050.
- Từ công thức pt = p0 * eRT, biến đổi để tính R theo p. Trong Excel, hàm eA có dạng exp(A). Vậy công thức viết lại là pt = Po * exp(RT).
Lấy logarit cơ số e hai vế được lnPt = lnPo + RT Chuyển vế được
lnPt -ln P 0 T
Dưa vào số liệu đã cho tính R theo từng giai đoạn, giai đoạn đầu tiên là 10 năm liên tục từ 1979 đến 1989, các giai đoạn tiếp theo là một năm.
- Sau khi tính được R của từng giai đoạn, lấy giá trị trung bình của R bằng hàm A VERRAGE (nu n2,...). Từ công thức đã cho, tính dân số cho từng năm đến năm 2050. Các bước cụ thể như sau:
• Hàng 1 nhập tiêu đề: ô A I: Năm, B I: Dân số, C1: lnB, D I: R.
• Cột A nhập năm: A2: 1979, A3:1989, A4: 1990, A5: 1991.
• Dùng Fill Handle điền tiếp đến hàng 10 là năm 1996.
• Cột B từ B2 nhập số dân số theo bảng 1.3.
• Cột c từ C2 nhập công thức = ln(B2), dùng Fill Handle điền tiếp
đen CIO.
• Cột D, nhập công thức tính R như trên, dùng Fill Handle điền tiếp đen C9.
• Ồ DI 1, nhập công thức = Average(D2:D9) để được R trung bình. • Ô AI 1, nhập công thức = A10 + 1.
• Ô BI 1, nhập công thức = B10*exp(($D$l 1)*(A11 - A10)).
• Bôi đen hai ô từ ô AI 1 đến ô BI 1. • Dùng Fill Handle điền tiếp đến B64.
• Dự báo dân số của địa phương A cho đến năm 2050 theo R tính được là 9737966 người (bảng 1.4).
Bảng 1.4. Kết quả tỉnh toán của ví dụ 2
A B . C 1 Năm Dân số Lnb R 2 1979 1.02 0.0198026 0.064803 3 1989 1.95 0.6678294 0.101352 4 1990 2.158 0.7691819 0.010143 5 1991 2.18 0.7793249 0.011403 6 1992 2.205 0.7907275 0.004073 V. T : i 1993 2.214 0.7948008 0.008097 8 1994 2.232 0.802898 0.008477 i 1995 2.251 0.8113746 0.007524 10 1996 2.268 0.8188984 11 1997 2.330033 Rtb = 0.026984 12 1998 2.393762 13 1999 2.459235 14 2000 2.526498 »#• 64 2050 9.737966
Ví dụ 3.
Nghiên cứu cấu trúc động của các quần thể sinh vật trong một sinh cảnh nhất định để tìm hiểu cân bằng sinh thái là nhằm bảo tồn và phát triển một cách bền vững các hệ sinh thái tự nhiên trong sự phát triển kinh tế, xã hội của một địa phương, một quốc gia hay khu vực liên quốc gia. Sau đây là một ví dụ loại này.
Giả sử thả vào một khu rừng thí nghiệm số thỏ ban đầu là 1 200 con và 12 con chó sói, tỷ lệ đực cái là 1 : 1. Cứ một con chó sói hàng tháng ăn trung bình 15 con thỏ. Sau 4 tháng thỏ đẻ, mỗi lứa 4 con; sau 8 tháng sói đẻ, mỗi lứa 4 con. Giả sử thời gian bắt đầu thả là ngày đầu tháng, 4 tháng sau thỏ đẻ, 8 tháng sau sói đẻ. Sói và thỏ không bị chết vì các nguyên nhân khác.
Phân tích bài toán như sau :
- Các đặc điểm :
+ Thế hệ tính theo tháng. + Thỏ lớn đầu tháng.
+ Thỏ bị chết do sói ăn thịt trong tháng. + Thỏ con mới được đẻ ra trong tháng. + Sói lớn đầu tháng.
+ Sói con mới được đẻ ra trong tháng.
- Theo giả thiết, số lượng thỏ đầu tháng trước và đầu tháng sau chênh lệch nhau đúng bằng số thỏ chết trong tháng trước do sói ăn thịt cộng với số thỏ con sinh ra ở cuối tháng trước (khi thoả mãn điều kiện đúng 4 tháng).
- Số thỏ con được sinh ra lần đầu tiên ở cuối tháng thứ tư = số thỏ mẹ sống sót đến cuối tháng thứ tư * 6 con. Để xác định đúng bốn tháng (điều kiện ràng buộc để thỏ iớn đẻ) và mỗi tháng thỏ bị sói ăn thịt đúng 15 con (không kể thỏ cái hay đực, lớn hay bé) cho thấy số thế hệ của bài toán cẩn lấy theo tháng. Những phân tích này cũng tương tự cho sói. Vì cả sói và thỏ đều thả ngày đầu tháng, nên tháng thứ nhất là cũng là tháng có số liệu ban đầu đã cho. Trong tháng này đã có thò chết do sói ăn thịt.
- Trong Excel hàm lấy phần dư của phép chia có cú pháp như sau = mod(A, k); trong đó A là số bị chia, k là số chia, kết quả của hàm này hiển thị phần dư của phép chia A cho k. Đây là hàm có thể diễn tả được điều kiện "đúng bốn thảng" bằng cách lấy phần dư của phép chia (địa chỉ ô số tháng) cho 4 đổi với thỏ (hoặc cho 8 đối với sói). Nếu phần dư của phép chia này bàng 0, tức là phép chia không dư thi số tháng là số nguyên lần của 4 (hoặc của 8), ví dụ = mod(A2,4) = 0 thì A2 = n*4, n nguyên dương.
- Việc xuất hiện thỏ con (hoặc sói con) nghĩa là có sự sinh sản của thỏ mẹ (hoặc sói mẹ) khi thoả mãn điều kiện về thời gian mang thai (4 hoặc 8 tháng). Cú pháp này của ngôn ngữ thông thường có thể diễn giải bằng thuật toán khi dùng hàm ỉ f của Excel. Hàm này có cú pháp như sau:
= if (biểu thức điều kiện, biểu thửc X, biểu thức Ỵ)
Hàm ỉf hoạt động như sau:
+ Nếu biểu thức điều kiện đúng thì khi nhấn ENTER, hàm i f sẽ thực hiện biếu thức X.
+ Biểu thức điều kiện không đúng thì khi nhấn ENTER, hàm if sẽ thực hiện biểu thức Y.
Theo tính chất trên của hàm if, để có thỏ con xuất hiện hay là thỏ mẹ đẻ, ta gán các biểu thức của hàm bằng các điều kiện của bài toán như sau :
+ Biểu thức điều kiện : đủng 4 tháng:
+ Biểu thức X : có ihỏ con xuất hiện (thỏ lởn đẻ),
4- Biểu thức Y : không cổ thỏ con xuất hiện (thỏ lởn không đẻ) và gán
bằng không (ồ).
Hay diễn đạt lại như sau:
+ Biểu thức điều kiện : modịô số tháng, 4) - 0.
+ Biếu thức X : (thỏ lởn đầu thảng - thỏ chết trong tháng) *4 *0.5 + Biểu thức Y : 0.
Sử dụng hàm và công thức để diễn tả, ta có các khai báo trong bảng 1.5.
Bảng 1.5. Khai báo của bảng tính cho ví dụ 3
: A D E F
1 SỐ tháng Thỏ đầu tháng Thỏ Chết Thỏ con Sói lớn Sỏi con
2 1 1200 = E2*15 (*) 12 n
3 = A2 + 1 = B2 - C2 + D2 = E3*15 n = E2 + F2 r >
(*) = if(mod(A2,4) = 0,(B2-C2)*4/2,0), Copy xuống n , địa chỉ ô tương đối sẽ thay đổi (**) = if(mod(A2,8) = 0,E2*4/2,0), Copy xuống (*’), đja chì ô tương đối sẽ thay đổi.
Sau đó bôi đen toàn bộ hàng thứ 3 và sử dụng công cụ Fiỉ Hand kéo xuống một số hàng tiếp theo cho tới khi xuất hiện số lượng thỏ lớn đầu tháng bằng 0 hoặc "âm", thì đừng lại.
4. LIÊN KÉT Dữ LIỆU CÁC BẢNG TÍNH
Khi liên kết dữ liệu, người sử dụng có thể tạo ra một bảng tính tổng hợp (về một vấn đề tổng quát) từ những bảng tính đơn lẻ (ví dụ từ các bảng tính về những vấn đề nhỏ có liên quan). Bảng tổng hợp được tạo ra từ những công thức lấy dữ liệu từ các bảng tính con trong Workbook. Khi bảng tính tồng hợp đã hoàn chỉnh, mọi thay đổi sau đó trong các bàng tính con đều được phản ánh tự động vào bảng tính tổng hợp.
Trong liên kết dữ liệu thường sử dụng các thuật ngữ tham chiếu ba chiều, tài liệu gốc, tài liệu phụ thuộc:
- Tham chiếu ba chiều tới một bảng tính khác trong cùng Workbook. Những tham chiếu này sẽ được cập nhật tự động khi Excel tính toán lại
Workbook.
- Tài liệu gốc là tài liệu chứa các dữ liệu gốc.
- Tài liệu phụ thuộc là tài liệu được tính toán dựa trên các dữ liệu cùa tài liệu khác.
4.1. Tham chiếu ba chiều
Tham chiếu ba chiều từ nhiều bảng tính được tạo ra theo các bước như sau:
- Đánh dấu ô muốn nhập kết quả của công thức tính được tạo ra theo các bước như sau:
+ Nhập dấu để bắt đầu công thức trong ô chứa kết quả.
+ Nhấn chuột trên tên của bảng tính cần lấy dữ liệu gốc cho phép tính để hiển thị bảng tính đó trên màn hình rồi nhấn tiếp trên ô (của bảng tính này) mà người sử dụng muốn tham chiếu tới.
+ Nhấn nút ENTER để quay về bảng tính nơi có ô chứa kết quả của phép tính, lúc này dữ liệu tham chiếu đã xuất hiện trong ô chứa công thức, nó là dữ liệu động. Nói cách khác, khi tài liệu gốc thay đổi thì dữ liệu ở ô đến (ô chứa công thức) cũng thay đổi theo.
+ Nếu người sử dụng muốn điền tiếp công thức bằng một phép tham chiếu nữa thì hãy nhẩn chuột trên ô chứa kết quả, khi đỏ công thức của nó sẽ xuất hiện trên thanh công thức. Người sử dụng nhấn chuột trên thanh công thức để nhấn tiếp vào công thức. Thao tác tham chiếu thực hiện như trình bày ở trên.
Như vậy Excel nhập công thức theo dạng: = Sheet Name ĩ File Name. Phương pháp này là cách chung để nhập tham chiếu tới bảng tính khác. Trong tham chiếu ba chiều, tên bảng tính là tuyệt đối, tên ô (hoặc dãy) có thể là tương đối. Một phép cộng có tham chiếu đến ba bảng tính có thể có dạng như sau:
- Sheet 1 ỉ A I 5 + Sheet2ỈAỈ5 + Sheet3ỈAỈ5
- Khi người sử dụng thực hiện phép cộng những ô hoặc vùng có cùng
địa chỉ như nhau nhưng thuộc một dãy các bảng tính khác nhau (có thể đến hàng 100 bảng tính) thì nên sử dụng hàm SUM và thực hiện theo thứ tự dưới đây:
+ Sắp xếp các bảng tính sẽ tham chiếu đến thành một dãy liên tục. + Trong ô đến (ô chứa kết quả) nhập công thức = SƯMQ
+ Nhấn chuột trên tên bảng tính đầu tiên trong dãy rồi nhấn tiếp trên ô
tham chiếu. '
+ Nhấn giữ SHIFT và nhấn chuột trên tên bảng tính cuối cùng của dãy. + Nhấn ENTER, Excel sẽ tự động hoàn chỉnh công thức.
4.2. Liên kết dữ liệu giữa các Workbook
Việc liên kết dữ liệu trong bảng tính của nhiều Workbook có rất nhiều . tác dụng. Nhờ có tính năng này, người sừ dụng có thể tách các Workbook phức tạp thành những Workbook đơn giản để làm giảm không gian bộ nhớ cần thiết, tổ chức phân cấp các Workbook để dễ quản lý dữ liệu. Khi phân cấp như vậy, Workbook cấp 1 tham chiếu đến Workbook cấp 2, Workbook cấp 2 tham chiếu đến Workbook cấp 3, cứ như thế làm đơn giản cấu trúc và dễ dàng khai thác dữ liệu theo các cấp quản lý.
Việc liên kết giữa các
Workbook thực hiện cũng
giống như với các bảng tính, chỉ có điểm khác là phải mở
Workbook trước khi tham
chiếu tới bảng tính, và sử dụng lệnh Window từ dòng
thực đơn đê mở các Hình 1.14. Thông báo tài liệu có chứa liên kết
Workbook. Như vậy tham
chiếu tới những Workbook khác đã mở như sau:
Microsoft Excel m
Save changes in 'BookV?
Khi người sử dụng mở một Workbook có chứa liên kết tới các
Workbook khác sẽ có một thông báo xuất hiện như trên hình 1.14. Nếu
người sử dụng trả lời Yes, Excel sẽ tự động đi đến những Workbook có tham chiếu liên kết để tìm những dữ liệu mới nhất và cập nhật những công thức truy xuất tới dữ liệu đó.
4.3. Quản lý những liên kết của Workbook
Người sử dụng quản lý những liên kết giữa các Workbook để thực hiện các công việc:
- Cập nhật những Workbook phụ thuộc.
- Chuyển hướng liên kết từ Workbook này sang Workbook khác. - Mở Workbook gốc.
Để quản lý những liên kết giữa tài liệu gốc và tài liệu phụ thuộc, người sử dụng hãy thực hiện lệnh Edit/Link, Excel sẽ mở hộp thoại Links. Danh sách các liên kết có tài liệu hiện hành thể hiện trong khung Source File, người sử dụng chọn liên kết để làm việc bằng cách nhấn chuột trên liên kết, sau đó có thể chọn tiếp các lệnh khác:
- Nhấn nút Update để cập nhật những thông tin từ tài liệu gốc. - Nhấn nút Open Sour se để mở tài liệu gốc.
- Nhấn nút Change Source để chuyển liên kết các Workbook khác, khi đó Excel mở hộp Change Links giống như hộp thoại Open để tìm mở tàỉ liệu mới mà người sừ dụng muốn liên kết.
Chú ỷ: Cẩn thận khi đổi tên hay di chuyển các Workbook có liên kết.
Nếu muốn đổi tên một tài liệu gốc, người sừ dụng thực hiện hai điểm sau đây để đảm bảo rằng tất cả các tham chiếu phụ thuộc được cập nhật chính xác:
+ Kiểm tra để thấy rằng các Workbook phụ thuộc vào Workbook gốc đang được mở để Excel có thể điều chỉnh tự động một tham chiếu phụ thuộc với một Workbook đã được đổi tên.
+ Sử dụng lệnh Save As để đổi tên Workbook.
4.4. Liên kết bằng phương pháp dán đặc biệt
Khi sao chép và dán dữ liệu theo cách thông thường giữa các bảng tính thì không tạo được sự liên kết giữa chúng. Thay vào đó, người sử dụng có thể sừ dụng lệnh Paste Special để sao chép dữ liệu sang một bảng tính mới, bảng tính này sẽ liên kết ngược trở lại với bảng tính gốc.
Để sử dụng kỹ thuật sao chép đặc biệt này, người sử dụng thực hiện các thao tác sau đây:
- Chọn một dãy ô nguồn. - Thực hiện lệnh Edỉt/Copy.
- Di chuyển tới Workbook đến và nhấn chuột trên ô noi muốn chép dữ
liệu vào. Thực hiện lệnh Edit/Paste Special, sẽ xuất hiện hộp thoại như trên hình 1.15.
- Nhấn chuột trên nút Paste Link, dữ liệu sẽ được đặt vào ô đến kèm theo những liên kết tự động với tài liệu gốc.
4.5. Gỡ bỏ liên kết
Để loại bỏ những liên kết nhưng vẫn giữ nguyên kết quả (ở 5 đến), người sử dụng thực hiện những bước thao tác dưới đây:
- Đánh dấu ồ muốn gỡ bỏ liên kết và giữ nguyên kết quả.
- Thực hiện lệnh Edit/Copy. - Thực hiện lệnh Edỉt/Paste
Special, sẽ xuất hiện hộp thoại Paste Special, người sử dụng
nhấn nút Values rồi nhấn nút OK. Sau những thao tác này liên kết bị loại bỏ nhưng kết quả của liên kết trước đó thì vẫn được bảo đảm.
4.6. Liên kết dữ liệu giữa Excel và các ứng dụng khác
Đẻ thực hiện liên kết dữ liệu giữa Excel và những chương trình ứng dụng khác trong Windows, người sử dụng thực hiện các thao tác:
- Đánh dấu đối tượng nguồn (dữ liệu hoặc đồ thị liên kết) trong Excel.
- Thực hiện lệnh Edit/Copy, lúc này xuất hiện đường viền chuyển động
xung quanh đối tượng.
- Xác định đích đến cần liên kết và thực hiện lệnh Edit/Paste Spacỉal
Paste Special Paste
■
m
©Vafctottoa© Eormutes © M tsoept borders . Ọ frfcies Opolumnatfths
■-! © Format* ‘ ©Fapajteandnurtberfiormats
t -■ © garments 0 v*lup» áhd number formate
ị Operation . I, Ó O ỉrtte - ;*■ - ị f © Subtract ị Bskfcbfertw' .) Lfifff
1
...I Hình 1.15. Hộp thoại dán đặc biệt - Paste SpecialChú ý:
- Người sử dụng có thể sử dụng kỹ thuật rê chuột để kéo đối tượng từ
nguồn đến thả trên đích nếu như có điều kiện quan sát được cả hai cửa sổ trên màn hĩnh.
- Để hiệu chỉnh dữ liệu đã liên kết ở đích đến, người sử dụng hãy nhấn nút chuột trên dữ liệu đó, Excel sẽ tự động hiển thị lại dữ liệu gốc trên màn hình để chỉnh sửa.
4.7. Kết hợp số liệu trong các bảng (Consolidate Worksheet)
Kết hợp số liệu trong các bảng tính là tạo mối quan hệ nối kết dữ liệu dạng số của các bảng tính vào trong một bảng tính tổng hợp. Các bảng tính tham gia liên kết có thể thuộc cùng một Workbook hoặc từ những Workbook khác nhau.
a) Dùng lệnh Consolidate
Các thao tác tiến hành như sau (hình 1.16):
- Hiển thị các bảng tính cần liên kết trên màn hình (để thuận tiện cho việc thao tác).
- Xác định bảng tính và phạm vi chứa những dữ kiện liên kết (chỉ cần chọn toạ độ ô đầu trong bảng tính chứa dữ liệu liên kết).
- Thực hiện lệnh Data/Consoỉidate, Excel sẽ hiển thị hộp thoại
Consolidate.
- Xác định phạm vi vùng dữ liệu trong các bảng tính cần kết hợp bằng
một trong hai cách:
+ Rê chuột để đánh dấu. + Nhập toạ độ trong khung
Reference và nhấn chuột trên
nút Add, sẽ xuất hiện toạ độ của vùng dữ liệu cần liên kết trong khung All Reference.
- Xác định tiếp các vùng dữ liệu khác cần kết hợp bằng các thao tác như trên.
- Trong khung Use Hình ^ Hộp thoại kết hợp số liệu trong
+ Đánh dấu ô Left Column khi cần dùng cột dữ liệu đầu tiên ở bên trái những cột dữ liệu trong phạm vi vùng đã xác định để làm cột tiêu đề.
+ Đánh dấu ô Top Row khi cần dùng hàng dữ liệu đầu tiên ở bên trên những hàng dữ liệu ữong phạm vi vùng đã xác định để làm hàng tiêu đề.
- Trong khung Function, NSD hãy thay đổi dạng hàm cần tính toán trong việc kết hợp dữ liệu từ các bảng tính khác (ngầm định là SUM).
- Nhấn nút OK để kết thúc, Excel sẽ gửi kết quả vào vùng đã xác định ban đầu.
Chú ỷ:
- Vùng chứa dữ liệu cần liên kết có thể trong cùng một bảng tính, một
Workbook hoặc giữa các Workbook.
- Số vùng tối đa NSD có thể kết hợp là 255.
- Vùng chứa dữ liệu kết hợp có thể chứa hoặc không chứa cột, hàng tiêu đề.
Nhập địa chỉ vùng dữ liệu cần liên kết trong khung Reference theo nguyên tắc:
+ Tên hoặc toạ độ vùng nếu liên kết trong cùng bảng tính.
+ Tên bảng tính. Tên hoặc toạ độ vùng nếu là liên kết giữa các bảng tính trong cùng Workbook
+ [Tên Workbook] Tên bảng tính. Tên hoặc toạ độ vùng khi liên kết các
Workbook trong cùng thư mục.
+ Đường dẫn [Tên Workbook] Tên bảng tính. Tên hoặc toạ độ vùng khi liên kết giữa các Workbook trong những thư mục khác nhau.
- Chỉ tổng hợp các dữ liệu dạng số và chỉ có một hàm tương thích với tính năng này.
- Nút Browse để tìm kiếm các tập tin Workbook khác ừên đĩa.
- NSD có thể dùng các ký tự đại điện (*, ?) trong phần tệp tin khi khai báo trong khung Reference.
- Đánh dấu ô Create Links to Source Data khi cần tạo thêm sự liên kết với những dữ liệu nguồn trong bảng tính kết hợp.
b) Dàng lệnh Paste Special
Các thao tác liên kết khi dùng lệnh Paste special như sau:
