Ứng dụng tin học trong thiết kế _ Sử dụng Excel trong các bài toán chuyên ngành

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 cho các công th c phía d i ta đ ọ ệ ng t ự ứ ướ ươ ể ượ ộ ố 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: ệ ố ị ỉ

ô E3 xu ng các ô phía d ệ ệ ướ ế i thì các đ a ch tham chi u ỉ ị

Th c hi n l nh Copy công th c tuy t đ i $B$8, $C$8, $D$8 s không thay đ i. ự ệ ố ứ ở ẽ ố ổ

 Bài1: T o b ng d li u có n i dung sau:

2.1.8. BÀI T P TH C HÀNH EXCEL-1 Ự Ậ

ữ ệ ạ ả ộ

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 ử ụ

Average() đ tính đi m trung bình chung c a t ng ng • S d ng hàm ử ụ ủ ừ ể ể ườ i (c t I). ộ

i (C t J) theo tiêu chí sau: ườ ộ

• S d ng hàm IF đ x p lo i h c l c c a t ng ng ử ụ ể ế

ạ ọ ự ủ ừ X p lo i ạ Trung bình Khá Gi iỏ Xu t s c ấ ắ ể ế Đi m trung bình <7 7‚ 8 8‚ 9 ‡ 9

t đi u ki n đi m trung bình thu c kho ng 7 ‚ 8 dùng hàm AND: • G i ý: đ vi ể ế ợ ề ệ ể ả ộ

 Bài 2: Th c hi n bài tính sau:

AND(H4>=7,H4<8)

a

+

= H H

ự ệ

0

2 . v 0 g 2

Ti n hành tính toán các giá tr Ho vào c t F theo công th c: ế ứ ộ ị

 Bài 3: T thi th c sau:

ng ng v i các tham s đ tính toán các công ự ế ế t k các ô s li u đ u vào t ố ệ ầ ươ ứ ố ể ớ

3

2

3

2

ứ

g

+

d

=

3

=

es= mQ

B

.2 Hg

Q

h

n

2/3 0

k

3

m +

ø Ø (cid:246) (cid:230) (cid:247) (cid:231) œ Œ ; ; ;… (cid:247) (cid:231)

(

A g

1 mm (

)2

Q B

1 g

h ) 1

d

d

- œ Œ ł Ł ß º

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. M T S HÀM NÂNG CAO

Ộ Ố

2.2.1. Các hàm tìm ki m ế

a. Hàm VLOOKUP

Cú pháp: =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

ụ ố ề ườ ứ

ng đ bê tông ph ộ i, ta mu n đi n các thông s v c ố ề ườ ụ ng đ bê ộ ề ả ộ ố

ơ ở ữ ệ (CSDL) A1:C5 ch a các thông s v c Ví d : ta có 1 c s d li u thu c vào mác bê tông. Trong b ng phía d ướ tông cho t ng c u ki n, m i c u ki n có m t mác khác nhau. S d ng hàm Vlookup: ộ ệ ỗ ấ ử ụ ừ ệ ấ

• Lookup_value = B8 (giá tr tra c u, là giá tr thu c c t đ u tiên c a CSDL) ộ ộ ầ ủ ứ ị ị

• Table_array = $A$2:$C$5 (CSDL ch a thông tin c n tra c u, CSDL này ph i đ ả ượ c ứ ứ ầ

trình bày theo c t đ ng – Vertical) ộ ứ

• Col_index_num = 2 ( c t th 2 c a CSDL, ch a thông tin v c ề ườ ứ ủ ứ ộ ng đ ch u nén Rn ị ộ

c a bê tông) ủ

• Range_lookup=0 (giá tr cho bi ứ ộ ế ầ ị

ế ế ế ầ ầ

ụ t m c đ tìm ki m là chính xác hay g n đúng, ví d : ế b ng 0 - tìm ki m chính xác, b ng 1 – tìm ki n g n đúng, True-tìm ki m g n đúng, ằ ằ False – tìm ki m chính xác,...) ế

b. Hàm HLOOKUP

- Cú pháp HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

- Ý nghĩa t ng t nh hàm Vlookup, đi m khác là CSDL c a hàm Hlookup đ c trình bày ươ ự ư ủ ể ượ

theo hàng ngang (Horizontal).

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. Hàm INDEX

Là hàm cho phép tra d li u theo hai chi u (hàng và c t) ữ ệ ề ộ

Cú pháp:

• INDEX(array, row_num, column_num)

Ví d : INDEX({1,2,3,5},0,2) =2 ụ

• 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 ộ ố

ị ủ ố ố ươ ứ ng đ ị ủ m t hàm s , yêu c u tìm giá tr c a đ i s t ầ 2+3x+5 b ng 10, (t ớ ằ ng ng. Ví d : ụ ủ ng v i tìm nghi m c a ệ ươ ươ

t tr ế ướ ị ng trình c giá tr c a ố 2x2+3x+5 =10). tìm giá tr x sao cho hàm s f(x)=2x ph ươ

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 ử ụ

• 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 ự

- Cách s d ng hàm Goal Seek: Menu Tools/ Goal Seek. ử ụ

ỉ ủ ứ ế ặ ộ ị

• Nh p giá tr c a hàm vào : To Value

ậ ti p.ế

• Nh p vào By Changing cell đ a ch c a ô ch a bi n s x (ho c dùng chu t) ỉ ủ

ị ủ ậ

ế ố ứ ặ ậ ộ ị

2

=

+

=

40

- 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. ườ ượ ứ ậ ể ả ụ ỹ ầ

3/s/m; g=9.81; j =0.95)

c sao cho

E o

h c

2

q hj g

2

2 c

Ví d : Tìm h (Bi t q=61.7 m ụ ế

K t qu đ c hc=2.39092 m. ả ượ ế

2.2.4. Hàm SUMIF

- Là hàm tính t ng có đi u ki n. Ví d : cho b ng l ề ụ ệ ả ổ ươ ổ ng c a 1 Công ty, yêu c u tính t ng ủ ầ

ng c a các tr ng phòng (TP)? l ươ ủ ưở

• Range: là t p h p các cell ch a các d li u c n đánh giá (Ví d : các ch c v ) ứ ụ

- Cú pháp: SUMIF(range, criteria, sum_range)

• Criteria: tiêu chu n tính toán (ví d : ch xét nh ng ng

ữ ệ ầ ụ ứ ậ ợ

•

ụ ữ ẩ ỉ ườ i có ch c v TP) ứ ụ

sum_range : đ a ch các cell ch a d li u c n tính t ng (ví d : l ng) ứ ữ ệ ầ ụ ươ ổ ị ỉ

 Cách khác: = SUMIF(B3:B8,”=TP”,C3:C8)

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.5. Hàm PI()

(cid:222) Là m t hàm s không đ i s , cho ra giá tr c a s pi: =PI() 3.14159..... ị ủ ố ố ố ố ộ

2.2.6. Hàm mũ EXP()

22026.466 Ví d : eụ 10 = EXP(10) (cid:222)

2.2.7. Các hàm l ng giác ượ

- Cú pháp chung: =(s ). Ví d =SIN(A2). ụ ố

Tên hàm Cú pháp

Sin = SIN(number)

Cos = COS(number)

Tang = TAN(number)

Arctang = ATAN(number)

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 ử ụ

 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

2.2.9. BÀI T P TH C HÀNH EXCEL-2 Ự Ậ

ơ ở ữ ệ ư ả ữ ệ ạ ả ạ ộ ớ

s n.ẵ

ạ

ườ ng kính t • 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 ạ ạ 22-28 dùng lo i ừ

ế ườ ng kính l n h n 28mm dùng lo i CIII. 10 đ n 20(mm) dùng lo i AII, đ ạ ộ lo i AI; đ ạ CII; đ ườ ng kính t ớ ừ ơ

• 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. ớ ừ

3 đ

• C t K: Tính kh i l ng thép, bi ng riêng c a thép g = 7800 kG/m ố ượ ế ố ượ ủ cượ

 Bài 2: S d ng các công th c sau đ tìm h

c và hc“ :

ộ nh p ậ ở Cell B9. Sau đó thay đ i giá tr ô B9 thành 7850. ổ t kh i l ị

2

2

=

+

=

+

ử ụ ứ ể

1

1

E 0

h c

'' h c

2

q hj g

2

h c 2

2 c

8 q 3 g h . c

� � � �

� � � �

- ;

 Bài 3 : T o b ng d li u sau và xu t ra đ th d ng XY thành hai đ

Bi =0.9. t ế E0=60 m, q=100 m2/s, j

j = 0.6

ng cong. ữ ệ ấ ạ ả ườ

X 0.52 1.15 1.88 2.78 3.96 5.53

Y 2.38 2.8 3.46 4.46 5.92 7.95

X 0.21 0.59 0.98 1.46 2.08 2.91

ồ ị ạ j = 0.8

 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

Y 2.89 3.31 4.08 5.25 6.95 9.36

ơ ở ữ ệ ề ữ ệ ộ ể ố ả ạ ả ở ả b ng

3.

• B ng 1: ả

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 ử ụ

M· Ngh¹ch Chøc danh

Trî gi¶ng A100 A110 Gi¶ngviªn A120 GV chÝnh A150 A200

L¬ng (*1000VND) 500 1000 1500 2000 2500

Phã GS Gi¸o s GV Cao cÊp

A500

3000

TK

TBM

PK

PP

TP

PBM GVCN Tr. Khoa Trëng phßng Phã khoa Phã phßng Trëng BM Phã BM GVCN

• B ng 2: ả M· chøc vô Tªn chøc vô • B ng 3: ả

STT

Hä vµ tªn

Sinh n¨m M· ng¹ch

Giíi tÝnh

Tuæi Ghi chó

Chøc danh

M· chøc vô

Tªn chøc vô

L¬ng (triÖu VND)

TP

N÷

1

Phan Anh B×nh

1979

A200

Nam

2

TrÇn Hïng Cêng

1984

A100

PK

Nam

3

Vò Nam TiÕn

1945

A120

PP

Nam

4

Chu §øc Qu©n

1950

A110

N÷

5

NguyÔn Mü H¹nh

1950

A500

i đ n tu i v h u đ ổ ề ư ượ ỉ ư c ghi chú : “Ngh h u“. Ti u chu n ngh h u: ỉ ư ể ẩ

Trong đó nh ng ng Nam đ n tu i 60, N đ n tu i 55. ườ ế ữ ế ữ ổ ế ổ

Chú ý: Tu i = năm hi n th i – năm sinh ; Năm hi n th i có th dùng hàm YEAR(today()) ệ ệ ể ờ ổ ờ

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.3.

NG D NG VBA Đ GI I M T S BÀI TOÁN CHUYÊN NGÀNH

Ứ

Ể Ả Ộ Ố

Ụ

2.3.1. Gi i thi u v Visual Basic Application (VBA) ớ ệ ề

2.3.2. Vi ng quan h Q=f(Z

hl)

 Gi

ế t hàm n i suy đ ộ ườ ệ

hl nh sau: ư

s có b ng quan h Q_Z ả ử ệ ả

160

320

 Bi u đ quan h có d ng:

500 502 504 506 640 0 508 1600 510 2560 512 3840 514 5760 516 8000 518 11200 520 16000 Zhl Q (m3/s)

ể ồ ệ ạ

Biểu đồ quan hệ Q=f(Zhl)

525

)

m

520

( l h Z

515

510

505

500

495

0

5000

10000

15000

20000

Q (m3/s)

 Yêu c u tính toán n i suy

hl theo Q. (Đi u ki n cho phép n i suy tuy n tính)

 Thu t toán: ậ

tuy n tính Z ầ ộ ế ề ệ ế ộ

• 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)

1 nào đó

• Gi s c n tìm giá tr Z t ả ử ầ ị ươ ứ ng ng v i giá tr Q ớ ị

1 trong m ng Q(i): ả

• Xác đ nh v trí c a Q ủ ị ị

1£ Q(1) (cid:222)

• N u Qế

Z = Z(1)

1‡ Q(11) (cid:222)

• N u Qế

Z = Z(11)

• N u Q(i) ế

£ Q(i+1), n i suy Z theo công th c: Q1 £ ứ ộ

=

+

-

[

+

Z

)i(Z

1

-

])i(Z)

i(Z

1 +

)i(QQ 1

)i(Q)

i(Q

-

hl đ

 Bài toán n i suy Q theo Z ộ

c th c hi n t ng t b ng cách đ i vai trò c a Q và Z cho ượ ệ ươ ự ự ằ ủ ổ

nhau.

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 ử ụ

z

i=

1

2

0,1

hh

hcd

hc

h''c

Z

do

L2

L1

L b

1

2

2.3.3. Bài toán tính toán b tiêu năng ể

o sao cho tho mãn đi u ki n:

Đích c a bài toán là tìm ra chi u sâu d ủ ề ề ệ ả

oMin , t c b t đ ng th c (1) x y ra d u “=“, sau đó

hc” £ hb=do+hh+D z (1)

ẽ ử ụ ể ặ ứ ấ ẳ ứ ả ấ

oMin .

o l n h n d ớ ơ

Ở đây s s d ng PP tính l p đ tìm d ch n giá tr d ọ ị

Các b c tính toán: ướ

o = hc” - hh =16.26 – 8.8 = 7.46m.

thi t chi u sâu b là d • B c 1: Gi ả ướ ế ề ể

• B c 2: Tính năng l ng t i cu i d c sau khi đào b (MC 2-2): ướ ượ ạ ố ố ể

Eo1 = Eo + do = 35.85+7.46=43.31m

ng cu i d c (MC 1-1), đ c xác đ nh theo công th c: Trong đó Eo là năng l ượ ố ố ượ ứ ị

2

=

+

E

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 ử ụ

o

h cd

2

q j

2

g

2 h cd

= 35.85 m (2)

là h s l u t c l y b ng 0,95 V i ớ j ằ

c m i sao cho:

2

=

+

=

E

ệ ố ư ố ấ • B c 4: Th d n đ xác đ nh giá tr h ử ầ ướ ể ị ị ớ

o 1

h c

2

q j

2

g

2 h c

= 43.31m (3)

c h ả ượ c = 2.29 m. ế

2

=

+

K t qu đ • B c 5: Tính l i đ sâu liên hi p theo công th c: ướ ạ ộ ứ ệ

"

1

1

h c

h c 2

8 q 3 gh c

� � � �

� � � �

- = 17.3 m.

ướ ề

• B c 6: Tính chi u sâu b : ể hb=hh+d0+D Z

ng ộ ề ự c ướ ở ưỡ ậ ố ng b tiêu năng, ph thu c vào v n t c ụ ể ộ

2

2

c trong b V Trong đó D Z là đ d nh m c n ể b: n ướ

=

V b

2

2

q "s h

c

c”=hb

a = - D Z = 3.42 m/s ; = 2.75 V b 2 g q j g 2 h h

• B c 7: Ki m tra đi u ki n h ể ướ ề ệ

0Min.

• N u tho mãn, bài toán k t thúc, tìm đ • N u không tho mãn: Gi

c giá tr d ế ượ ế ả

thi c l y ị ế ạ o = hc” - hh - D z, v i hớ c” và D z đ t l ả ả

i d c, sau đó ti n hành tính toán l i theo các b c trên. Quá trình tính l p đ ạ ướ ượ ấ ở ầ l n ặ ượ c

ế th tr ế ử ướ c”=hb, lúc đó do cũng h i t th c hi n đ n khi h ệ ế ự ộ ụ ề oMin. v d

c th hi n trong b ng sau: ượ ả

D Z

Quá trình tính toán đ E01 44.12 41.99 41.72 41.69 41.68 41.68 41.68

do 8.27 6.14 5.87 5.84 5.83 5.83 5.83

ể ệ hc 2.27 2.33 2.34 2.34 2.34 2.34 2.34

hc” 17.41 17.13 17.10 17.09 17.09 17.09 17.09

vb 3.38 3.43 3.44 3.44 3.44 3.44 3.44

2.47 2.46 2.46 2.46 2.46 2.46 2.46

hb 19.54 17.40 17.13 17.10 17.09 17.09 17.09

E = 44.12 = 41.99 = 41.72 = 41.69 = 41.68 = 41.68 = 41.68

oMin = 5.83m. L a ch n d

o = 6m.

STT 1 2 3 4 5 6 7

V y dậ ự ọ

Tính l ạ i các đ c tr ng nh sau: ư ư ặ

D Z E do E01 hc hc” vb hb

6.00 41.85 2.33 = 41.85 17.11 3.43 2.46 17.26