zunia.vn

Tuyển sinh 2024 dành cho Gen-Z

zunia.vn

» Công Nghệ Thông Tin

» Tin học văn phòng

Ứ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

Chia sẻ: Nguyễn Văn Thiêm | Ngày: | Loại File: DOC | Số trang:15

Báo xấu

265
lượt xem 71
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

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

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Ứ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

Ứ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ố . 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ả • #NUM! - tràn ô nhớ hoặc dùng tham số vô nghĩa, ví dụ: SQRT(-1) • #REF! - tham chiếu ô không có giá trị, hoặc ô không có trong bảng tính
Ứ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 • #VALUE! - kiểu đối số không đúng, ví dụ cộng trừ các dòng ký tự 2.1.4. Khái niệm về tham chiếu ô trong bảng tính EXCEL Các ô trong bảng tính có thể chứa con số hoặc ký tự hoặc công thức. Khi bạn muốn l ấy nội dung trong một ô thì bạn đã thực hiện việc tham chiếu tới ô đó. - Tham chiếu trong cùng Sheet: Ví dụ đang ở ô A1, muốn tham chiếu giá trị của ô D4: “=D4” - Tham chiếu khác Sheet: đang ở ô A1 của Sheet1, muốn lấy giá trị của ô B2 thuộc Sheet2: “=Sheet2!B2” - Tham chiếu khác File: muốn tham chiếu tới ô D13 thuộc Sheet1 của File “Solieu.xls”: “=[Solieu]Sheet1!$D$13” Cách đơn giản và chính xác nhất để tham chiếu đến một ô là di chuy ển đ ến b ảng tính chứa ô đó, rồi dùng chuột để nhấp vào ô mà bạn cần tham chiếu. - Tham chiếu tương đối: là tham chiếu mà khi di chuyển hoặc copy sang ô khác thì các địa chỉ tham chiếu trong ô sẽ thay đổi (địa chỉ tương đối). - Tham chiếu tuyệt đối: là tham chiếu mà khi di chuyển sang ô khác nhưng các địa chỉ tham chiếu trong ô vẫn giữ nguyên (địa chỉ tuyệt đối). - Có thể tham chiếu cả một vùng dữ liệu. Ví dụ: “=MAX(A1:D10)” 2.1.5. Địa chỉ tương đối, Địa chỉ tuyệt đối: • Tuyệt đối cả hàng và cột: $$. Ví dụ: $B$5 • Tương đối cột, tương đối hàng: . Ví dụ: B5 • Tuyệt đối cột, tương đối hàng: $. Ví dụ: $B5 • Tương đối cột, tuyệt đối hàng: $. Ví dụ: B$5 Ý nghĩa của các loại địa chỉ này được thể hiện trong phần sao chép, di chuyển dữ li ệu (mục k). 2.1.6. Định dạng bảng tính Vào Format/Cells (Ctrl+1), cửa sổ Format Cells hiện ra, trong đó các mục sau: a. Định dạng ký tự (Font) Để 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 Cells Merged Border • 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 UnMerge Cell Format Cell Merge Cell (Tool/Custumize/Command/Insert,….)
Ứ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 Giả sử cần gán vào ô E3 giá trị tuyệt đối của số trong ô B2, nhấn chuột vào ô E3 và gõ công thức sau: “=ABS(B2)”, sau khi ấn enter sẽ được kết quả là 2 d. Hàm tổng SUM Để tính tổng của các số trong vùng chọn B2:C2, gõ công thức sau: “= SUM(B2:C2)”, sau khi ấn enter sẽ được kết quả là 3. e. Hàm trung bình cộng AVERAGE Để tính trung bình cộng của các số trong vùng chọn B2:C2, gõ công thức sau: “=Average(B2:C2)”, sau khi ấn enter sẽ được kết quả là 1.5. f. Hàm giá trị lớn nhất MAX Để tính giá trị lớn nhất của các số trong vùng chọn B2:C2, gõ công thức sau: “=Max(B2,C2)”, hoặc “=Max(B2:C2)”, sau khi ấn enter sẽ được kết quả là 5. Max(2,5,10,20)=20 g. Hàm giá trị nhỏ nhất MIN (Tương tự hàm Max) h. Hàm IF - Cấu trúc của hàm IF: “=IF(,,)” (Ý nghĩa: nếu thoả mãn thì gán cho , ngược lại gán ). - Hàm IF lồng: “=IF(,,IF(,,))” (Ý nghĩa: Nếu thoả mãn thì nhận , ngược lại xét tiếp , nếu thoả mãn sẽ nhận , nếu không sẽ nhận ) Ví dụ: xét bảng dữ liệu ở mục a.
Ứ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 • “=IF(10=0,”Sai”,”Đúng”)”, kết quả sẽ là “Sai” • “=IF(B2=2,”ketqua=2”, IF(B2=-2,”ketqua=-2”,”abc”))”, kết quả : “ketqua=-2” i. Hàm OR, AND - Hàm OR(,,...) cho giá trị TRUE nếu một trong các điều kiện là đúng, ngược lại sẽ cho giá trị FALSE. Ví dụ: OR(B2=-2, C2=10) cho kết quả TRUE vì B2=-2 là đúng. - Hàm AND(,,...) cho giá trị TRUE nếu tất cả các điều kiện đều đúng, ngược lại sẽ cho giá trị FALSE. Ví dụ: AND(B2=-2, C2=6) cho kết quả FALSE. j. Hàm Round(,)) 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,H4
Ứ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) Ví dụ: ta có 1 cơ sở dữ liệu (CSDL) A1:C5 chứa các thông số về cường độ bê tông phụ thuộc vào mác bê tông. Trong bảng phía dưới, ta muốn điền các thông số về cường độ bê 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ế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)=2x2+3x+5 bằng 10, (tương đương với tìm nghiệm của phương trình 2x2+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. q2 Ví dụ: Tìm hc sao cho Eo = hc + = 40 (Biết q=61.7 m3/s/m; g=9.81; ϕ=0.95) 2 gϕ hc 2 2 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 lương của các trưởng phòng (TP)? - Cú pháp: SUMIF(range, criteria, sum_range) • 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ụ) • 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)
Ứ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 khác: = SUMIF(B3:B8,”=TP”,C3:C8) 2.2.5. Hàm PI() 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() Ví dụ : e10 = EXP(10) ⇒ 22026.466 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 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
Ứ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 L¬ng M· Ngh¹ch Chøc danh (*1000VND) A100 Trî gi¶ng 500 A110 Gi¶ngviªn 1000 A120 GV chÝnh 1500 A150 Phã GS 2000 A200 Gi¸o s 2500 GV Cao A500 cÊp 3000 • Bảng 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 • Bảng 3: L¬ng Chøc M· chøc Tªn chøc STT Hä vµ tªn Sinh n¨m M· ng¹ch (triÖu Giíi tÝnh Tuæi Ghi chó danh vô vô VND) 1 Phan Anh B×nh 1979 A200 TP N÷ 2 TrÇn Hïng Cêng 1984 A100 Nam 3 Vò Nam TiÕn 1945 A120 PK Nam 4 Chu §øc Qu©n 1950 A110 PP Nam 5 NguyÔn Mü H¹nh 1950 A500 N÷ Trong đó những người đến tuổi về hưu được ghi chú : “Nghỉ hưu“. Tiểu chuẩn nghỉ hưu: 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ết hàm nội suy đường quan hệ Q=f(Zhl)  Giả sử có bảng quan hệ Q_Zhl như sau: Zhl 500 502 504 506 508 510 512 514 516 518 520 Q (m3/s) 0 160 320 640 1600 2560 3840 5760 8000 11200 16000  Biểu đồ quan hệ có dạng: Biểu đồ quan hệ Q=f(Zhl) 525 Zhl(m) 520 515 510 505 500 495 0 5000 10000 15000 20000 Q (m3/s)  Yêu cầu tính toán nội suy tuyến tính Zhl 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)
Ứ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.3. Bài toán tính toán bể tiêu năng z i =0 1 2 ,1 hh hcd hc h''c Z do L1 L2 Lb 1 2 Đích của bài toán là tìm ra chiều sâu do sao cho thoả mãn điều kiện: hc” ≤ hb=do+hh+∆z (1) Ở đây sẽ sử dụng PP tính lặp để tìm doMin , tức bất đẳng thức (1) xảy ra dấu “=“, sau đó chọn giá trị do lớn hơn doMin . Các bước tính toán: • Bước 1: Giả thiết chiều sâu bể là do = hc” - hh =16.26 – 8.8 = 7.46m. • 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 Trong đó Eo là năng lượng cuối dốc (MC 1-1), được xác định theo công thức:
Ứ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 q2 Eo = hcd + 2 = 35.85 m (2) 2 gϕ 2 hcd 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ị hc mới sao cho: q2 Eo1 = hc + = = 43.31m (3) 2 gϕ 2 hc2 Kết quả được hc = 2.29 m. • Bước 5: Tính lại độ sâu liên hiệp theo công thức: hc � 8q 2 � hc " = � 1 + 3 − 1� 17.3 m. = 2� � ghc � � • Bước 6: Tính chiều sâu bể: hb=hh+d0+∆Z Trong đó ∆Z là độ dềnh mực nước ở ngưỡng bể tiêu năng, phụ thuộc vào vận tốc nước trong bể Vb: 2 q q2 αV Vb = = 3.42 m/s ; ∆ Z = − b = 2.75 σh"c 2 gϕ 2 hh 2 2g • Bước 7: Kiểm tra điều kiện hc”=hb • Nếu thoả mãn, bài toán kết thúc, tìm được giá trị d0Min. • Nếu không thoả mãn: Giả thiết lại do = hc” - hh - ∆z, với hc” và ∆z được lấy ở lần thử trướ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 được thực hiện đến khi hc”=hb, lúc đó do cũng hội tụ về doMin. Quá trình tính toán được thể hiện trong bảng sau: STT do E01 hc E hc” vb ∆Ζ hb 1 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 17.09 3.44 2.46 17.09 Vậy doMin = 5.83m. Lựa chọn do = 6m. Tính lại các đặc trưng như sau: do E01 hc E hc” vb ∆Ζ hb 6.00 41.85 2.33 = 41.85 17.11 3.43 2.46 17.26

CÓ THỂ BẠN MUỐN DOWNLOAD

THÔNG TIN

TRỢ GIÚP

HỖ TRỢ KHÁCH HÀNG

Theo dõi chúng tôi

Chịu trách nhiệm nội dung:

Nguyễn Công Hà - Giám đốc Công ty TNHH TÀI LIỆU TRỰC TUYẾN VI NA

LIÊN HỆ

Địa chỉ: P402, 54A Nơ Trang Long, Phường 14, Q.Bình Thạnh, TP.HCM

Hotline: 093 303 0098

Email: support@tailieu.vn

Giấy phép Mạng Xã Hội số: 670/GP-BTTTT cấp ngày 30/11/2015 Copyright © 2022-2032 TaiLieu.VN. All rights reserved.