Ứng dụng tin học trong thiết kế Sử dụng Exel trong các bài toán chuyên ngành
CHƯƠNG 2. SỬ DỤNG EXCEL TRONG GIẢI CÁC B.TOÁN CHUYÊN
NGÀNH
Tài liệu tham khảo:
1. Tin học ứng dụng văn phòng – TS. Phùng Văn Ổn – NXB XD.
2. Tính toán kỹ thuật xây dựng trên EXCEL - TS. Nguyễn Viết Trung – NXB XD.
3. EXCEL toàn tập – Sách dịch – NXB Trẻ.
….
2.1. CÁC KIẾN THỨC CƠ BẢN
2.1.1. Giới thiệu chương trình
MS EXCEL là một phần mềm của hãng Microsoft, dùng để xử lý số liệu, được trình bày
dưới dạng các bảng tính. EXCEL không những đáp ứng được các công việc thuộc về quản
lý hành chính văn phòng mà còn là một công cụ hỗ trỡ đắc lực cho các nhà kỹ thuật, trong
đó có các kỹ sư xây dựng. Đặc biệt EXCEL kết hợp với bộ ngôn ngữ lập trình Visual Basic
for Aplication (VBA) cho phép giải nhiều bài toán kỹ thuật hóc búa.
2.1.2. Cấu trúc của một bảng tính EXCEL
- Sheet: Một file EXCEL (Book) gồm tối đa 255 bảng tính (Sheet1, Sheet2,….Sheet255),
muốn làm việc với sheet nào chỉ việc nhấn chuột vào tên của nó. Giữa các sheet có thể trao
đổi thông tin, liên kết thông tin lẫn nhau.
- Rows and Columns: Mỗi sheet có 65536 hàng (rows) đánh số từ 1÷65536; 255 cột
(columns) được ký hiệu bằng các chữ cái từ A÷IV.
- Cells: Giao của 1 Row và 1 Column được gọi là 1 cell, mỗi cell có 1 địa chỉ được ký hiệu
bằng chỉ số <cột><hàng>. Ví dụ: A1, W100, Sheet2!B5,…. Tại một thời điểm luôn có một
(hoặc một số) cell được chọn, để di chuyển sang cell khác có thể dùng chuột chọn trực tiếp
hoặc dùng các phím di chuyển trên bàn phím.
2.1.3. Lỗi trong Excel
Excel sẽ thông báo về 7 trường hợp lỗi như sau:
• # DIV/0! - Chia cho số 0
• #NAME? - chưa định nghĩa tên biến trong ô
• #N/A - không có trị số nào sẵn cho tình huống đang xét
• #NULL! - kết quả chẳng có gì cả

Để format kiểu font, kích cỡ chữ, màu sắc,…
b. Định dạng số, ngày tháng, thời gian (Number)
• Format số: Trong Category chọn Number. Decimal places: làm tròn bao nhiêu số sau
dấu phẩy.
• Các format khác: ngày (date), thời gian (time), ký tự (Text ),…
Ứng dụng tin học trong thiết kế Sử dụng Exel trong các bài toán chuyên ngành
c. Điều chỉnh vị trí của dữ liệu trong cell (Alignment)
• Horizontal: Căn vị trí của ký tự trong cell theo phương ngang .
• Vertical: Căn vị trí của ký tự trong cell theo phương đứng.
• Orientation: Góc quay của dòng text.
d. Tạo đường viền (Border)
Tạo các kiểu đường viên, kẻ ô của 1 ô Cell hoặc 1 vùng chọn.
e. Merge Cell and Unmerge Cell
• Chọn vùng D2:D3, vào Format/Cell/Alignment, chọn Merge Cells
• Cách 2: tạo biểu tượng trên thanh Tool Bar
(Tool/Custumize/Command/Insert,….)
Cells Merged
Format Cell
Merge Cell
UnMerge Cell
Border
Ứng dụng tin học trong thiết kế Sử dụng Exel trong các bài toán chuyên ngành
2.1.7. Một số hàm và phép toán đơn giản hay dùng
a. Các phép toán cộng, trừ, nhân, chia
• Nhập vào ô B2 giá trị -2, ô C2 giá trị 5 ;
• Nhập vào ô E2 công thức “=B2+C2”, sau khi Enter sẽ được kết quả là 3.
• Tương tự cho các phép toán - , * , /,…
b. Hàm mũ (^)
“=5^2” ⇒ 25; “=B2^3” ⇒ -8; “=4^0.5” ⇒ 2
c. Hàm giá trị tuyệt đối ABS

Round(1.253,0) = 1; Round(1.253,1)=1.3; Round(1.253,2)=1.25,
k. Lệnh Copy
l. Copy địa chỉ tương đối:
- Nhập công thức “=AVERAGE(B3:D3)” vào ô E3, Enter
- Chọn lại ô E3, chọn lệnh Copy (Ctrl+C, Edit/copy), chuyển chuột xuống ô E4, dùng lệnh
Paste (Ctrl+V) , tương tự cho các công thức phía dưới ta được như sau:
- Khi thực hiện lệnh Copy thì các địa chỉ tham chiếu tương đối sẽ thay đổi tương ứng với địa
chỉ paste.
m. Copy địa chỉ tuyệt đối:
Thực hiện lệnh Copy công thức ở ô E3 xuống các ô phía dưới thì các địa chỉ tham chiếu
tuyệt đối $B$8, $C$8, $D$8 sẽ không thay đổi.
2.1.8. BÀI TẬP THỰC HÀNH EXCEL-1
 Bài1: Tạo bảng dữ liệu có nội dung sau:
Ứng dụng tin học trong thiết kế Sử dụng Exel trong các bài toán chuyên ngành
• Sử dụng hàm Average() để tính điểm trung bình chung của từng người (cột I).
• Sử dụng hàm IF để xếp loại học lực của từng người (Cột J) theo tiêu chí sau:
Điểm trung bình Xếp loại
<7 Trung bình
7÷8
Khá
8÷9
Giỏi
≥9
Xuất sắc
• Gợi ý: để viết điều kiện điểm trung bình thuộc khoảng 7÷8 dùng hàm AND:
AND(H4>=7,H4<8)
 Bài 2: Thực hiện bài tính sau:
Tiến hành tính toán các giá trị Ho vào cột F theo công thức:
g
v

mm
m
hA
Q
d
d
γ
γ
;
2/3
0
.2 HgBmQ
n
εσ
=
;
3
2
1
gB
Q
h
d
k







• INDEX(reference,row_num,column_num,area_num)
Ví dụ: INDEX(B5:E9,2,2) = 1.07 ; INDEX(B5:E9,2,3) = 0.88
2.2.2. Hàm làm tròn số theo số cho trước ROUND()
- Cú pháp: ROUND(number,num_digits)
- Ví dụ: Round(3.145 , 2) = 3.15
2.2.3. Hàm “GOAL SEEK” để tìm nghiệm khi biết trước kết quả của hàm
- Khi ta biết trước giá trị của một hàm số, yêu cầu tìm giá trị của đối số tương ứng. Ví dụ:
tìm giá trị x sao cho hàm số f(x)=2x
2
+3x+5 bằng 10, (tương đương với tìm nghiệm của
phương trình 2x
2
+3x+5 =10).
Ứng dụng tin học trong thiết kế Sử dụng Exel trong các bài toán chuyên ngành
- Cách sử dụng hàm Goal Seek: Menu Tools/ Goal Seek.
• Nhập vào vào Set cell địa chỉ của ô chứa hàm f(x), hoặc dùng chuột tham chiếu trực
tiếp.
• Nhập giá trị của hàm vào : To Value
• Nhập vào By Changing cell địa chỉ của ô chứa biến số x (hoặc dùng chuột)
- Hàm GOAL SEEK thường được ứng dụng trong kỹ thuật để giải các bài toán đúng dần.
Ví dụ: Tìm h
c
sao cho
2
2 2
40
2
o c
c
q

Cotang Không định nghĩa
2.2.8. Biểu đồ trong EXCEL
Các bước tạo biểu đồ:
- Bước 1: Tạo cơ sở dữ liệu.
- Bước 2: Gọi Chart Wizard (Menu Insert / Chart ; hoặc dùng biểu tượng )
- Bước 3: chọn Chart type, có nhiều loại cho sẵn, tuy nhiên loại XY hay được sử dụng nhất.
- Bước 4: Chọn chart sub-type
- Bước 5: Tạo các Series, mỗi sery gồm tên seri, các giá trị X, các giá trị Y.
- Bước 5: Tạo các tính chất của biểu đồ: Title; Axes; Gridline,
- Bước 6: Format lại Chart
Ứng dụng tin học trong thiết kế Sử dụng Exel trong các bài toán chuyên ngành
2.2.9. BÀI TẬP THỰC HÀNH EXCEL-2
 Bài 1: Tạo các cơ sở dữ liệu có các nội dung như Bảng 1. Tạo bảng 2 với các dữ liệu cho
sẵn.
• Cột H: Tiến hành phân loại thép theo tiêu chí sau: Thép có đường kính <10 : dùng loại
loại AI; đường kính từ 10 đến 20(mm) dùng loại AII, đường kính từ 22-28 dùng loại
CII; đường kính lớn hơn 28mm dùng loại CIII.
• Cột I: Từ dữ liệu của cột H, dùng hàm tìm kiếm để tra ra cường độ thép tương ứng với
từng thanh thép.
• Cột K: Tính khối lượng thép, biết khối lượng riêng của thép g = 7800 kG/m
3
được nhập
ở Cell B9. Sau đó thay đổi giá trị ô B9 thành 7850.
 Bài 2: Sử dụng các công thức sau để tìm h
c
và h
c
“ :
2
0

=60 m, q=100 m
2
/s, ϕ =0.9.
 Bài 3 : Tạo bảng dữ liệu sau và xuất ra đồ thị dạng XY thành hai đường cong.
ϕ=0.6 ϕ=0.8
X Y X
Y
0.52 2.38 0.21 2.89
1.15 2.8 0.59 3.31
1.88 3.46 0.98 4.08
2.78 4.46 1.46 5.25
3.96 5.92 2.08 6.95
5.53 7.95 2.91 9.36
 Bài 4 : Tạo các cơ sở dữ liệu bảng 1 và bảng 2, điền dữ liệu vào các cột để trống ở bảng 3.
• Bảng 1:
M· Ngh¹ch Chøc danh
L¬ng
(*1000VND)
A100 Trî gi¶ng 500
ng dng tin hc trong thit k S dng Exel trong cỏc bi toỏn chuyờn ngnh
A110 Giảngviên 1000
A120 GV chính 1500
A150 Phó GS 2000
A200 Giáo s 2500
A500 GV Cao cấp 3000
Bng 2:
Mã chức vụ
TK TP PK PP TBM PBM GVCN
Tên chức vụ Tr. Khoa Trởng phòng Phó khoa Phó phòng Trởng BM Phó BM GVCN
Bng 3:

500 502 504 506 508 510 512 514 516 518 520
Q (m
3
/s)
0 160 320 640 1600 2560 3840 5760 8000 11200 16000
 Biểu đồ quan hệ có dạng:
 Yêu cầu tính toán nội suy tuyến tính Z
hl
theo Q. (Điều kiện cho phép nội suy tuyến tính)
 Thuật toán:
• Gán các giá trị Q và Zhl vào hai mảng: Q(i) và Z(i) sắp xếp theo thứ tự tăng dần:
Q(1)<Q(2)<Q(3)< ; Z(1)<Z(2)<Z(3)<
• Giả sử cần tìm giá trị Z tương ứng với giá trị Q
1
nào đó
• Xác định vị trí của Q
1
trong mảng Q(i):
• Nếu Q
1
≤Q(1) ⇒ Z = Z(1)
• Nếu Q
1
≥Q(11) ⇒ Z = Z(11)
• Nếu Q(i) ≤ Q
1
≤ Q(i+1), nội suy Z theo công thức:
[ ]
)i(Z)i(Z
)i(Q)i(Q

c
z
b
i
=
0
,
1
Đích của bài toán là tìm ra chiều sâu d
o
sao cho thoả mãn điều kiện:
h
c
” ≤ h
b
=d
o
+h
h
+∆z
(1)
Ở đây sẽ sử dụng PP tính lặp để tìm d
oMin
, tức bất đẳng thức (1) xảy ra dấu “=“, sau đó
chọn giá trị d
o
lớn hơn d
oMin
.
Các bước tính toán:

(2)
Với ϕ là hệ số lưu tốc lấy bằng 0,95
• Bước 4: Thử dần để xác định giá trị h
c
mới sao cho:
=+=
22
2
1
2
c
co
hg
q
hE
ϕ
= 43.31m
(3)
Kết quả được h
c
= 2.29 m.
• Bước 5: Tính lại độ sâu liên hiệp theo công thức:
2
3
8
" 1 1
2
c
c
c

= 3.42 m/s ;
g
V
hg
q
Z
b
h
2
2
2
2
2
2
α
ϕ
−=∆
= 2.75
• Bước 7: Kiểm tra điều kiện h
c
”=h
b
• Nếu thoả mãn, bài toán kết thúc, tìm được giá trị d
0Min
.
• Nếu không thoả mãn: Giả thiết lại d
o
= h
c
” - h

8.27 44.12 2.27
= 44.12
17.41 3.38 2.47 19.54
2
6.14 41.99 2.33
= 41.99
17.13 3.43 2.46 17.40
3
5.87 41.72 2.34
= 41.72
17.10 3.44 2.46 17.13
4
5.84 41.69 2.34
= 41.69
17.09 3.44 2.46 17.10
5
5.83 41.68 2.34
= 41.68
17.09 3.44 2.46 17.09
6
5.83
41.68 2.34
= 41.68
17.09
3.44 2.46
17.09
7
5.83
41.68 2.34
= 41.68