TRƯỜNG ĐẠI HỌC BÁCH KHOA HÀ NỘI VIỆN ĐIỆN BỘ MÔN KỸ THUẬT ĐO VÀ TIN HỌC CÔNG NGHIỆP ĐỒ ÁN TỐT NGHIỆP (HỆ ĐẠI HỌC CHÍNH QUY) ĐỀ TÀI ỨNG DỤNG EXCEL TRONG PHÂN TÍCH VÀ XỬ LÍ SỐ LIỆU Sinh viên thực hiện PHÙNG VĂN TOÀN Lớp DKTDH 05 K59 MSSV 20144555 Giảng viên hướng dẫn TS NGUYỄN CÔNG PHƯƠNG HÀ NỘI, THÁNG 06 NĂM 2019 LỜI NÓI ĐẦU Trong suốt quá trình học tập tại trường Đại học Bách Khoa Hà Nội, em đã học hỏi được rất nhiều kiến thức và kinh nghiệm học tập từ phía thầy cô và bạn bè Những kinh nghi.
TỔNG QUAN VỀ PHÂN TÍCH DỮ LIỆU
Tầm quan trọng của phân tích dữ liệu
2.1 Những công cụ được sử dụng để phân tích dữ liệu hiện nay
Phần mềm phân tích số liệu đóng vai trò quan trọng đối với các nhà thống kê trong nhiều lĩnh vực khác nhau Hiện nay, có nhiều phần mềm chuyên dụng như SAS, SPSS, STATA và R, giúp hỗ trợ hiệu quả trong việc xử lý và phân tích dữ liệu thống kê.
Mỗi phần mềm có đặc trưng, điểm mạnh, điểm yếu riêng Vai trò của các phần mềm phân tích dữ liệu:
Phần mềm phân tích chỉ là công cụ hỗ trợ cho việc thực hiện các ước lượng, tính toán nhanh chóng trong quá trình phân tích dữ liệu.
Các phần mềm phân tích dữ liệu đều dựa trên các công thức toán học và thống kê chung, do đó, kết quả phân tích từ các phần mềm khác nhau thường tương đồng Nếu có sự khác biệt, chúng thường rất nhỏ.
Một số phần mềm phân tích dữ liệu phổ biến hiện nay:
SAS là một bộ chương trình mạnh mẽ được ưa chuộng bởi những người có trình độ cao nhờ khả năng lập trình linh hoạt Tuy nhiên, việc học SAS không hề đơn giản, đòi hỏi người dùng phải nghiên cứu chuyên sâu để có thể thao tác dữ liệu và thực hiện các phân tích hiệu quả Một thách thức lớn khi làm việc với SAS là khả năng phát hiện và sửa lỗi trong chương trình, điều này cần kỹ năng và kinh nghiệm nhất định.
– SAS rất mạnh trong lĩnh vực quản lý dữ liệu, cho phép người sử dụng thao tác dữ liệu hầu như với mọi cách có thể
SAS cho phép xử lý nhiều file dữ liệu đồng thời, giúp giảm bớt sự phức tạp trong việc chuẩn bị dữ liệu cho các nhiệm vụ phân tích yêu cầu làm việc với nhiều nguồn dữ liệu khác nhau.
SAS có khả năng quản lý các tệp dữ liệu lớn với tối đa 32.768 biến, và số lượng bản ghi có thể rất lớn, chỉ bị giới hạn bởi dung lượng của ổ cứng.
SAS nổi bật trong phân tích thống kê nhờ vào khả năng thực hiện các phương pháp như ANOVA, phân tích mô hình hỗn hợp và phân tích nhiều chiều, giúp người dùng hiểu rõ hơn về dữ liệu và mối quan hệ giữa các biến.
– SAS có các công cụ vẽ đồ thị mạnh nhất (SAS/Graph) so với hai bộ chương trình còn lại.
4 https://www.sas.com/en_us/home.html
PHẦN MỀM PHÂN TÍCH DỮ LIỆU VÀ EXCEL
Những công cụ được sử dụng để phân tích dữ liệu hiện nay
Phần mềm phân tích số liệu là công cụ thiết yếu cho các nhà thống kê trong nhiều lĩnh vực Hiện nay, có nhiều phần mềm chuyên dụng hỗ trợ việc xử lý và phân tích số liệu thống kê, tiêu biểu như SAS, SPSS, STATA và R.
Mỗi phần mềm có đặc trưng, điểm mạnh, điểm yếu riêng Vai trò của các phần mềm phân tích dữ liệu:
Phần mềm phân tích chỉ là công cụ hỗ trợ cho việc thực hiện các ước lượng, tính toán nhanh chóng trong quá trình phân tích dữ liệu.
Các phần mềm phân tích dữ liệu đều dựa trên các công thức toán học và thống kê chung, dẫn đến việc kết quả phân tích từ các phần mềm khác nhau thường tương đồng Nếu có sự khác biệt, nó thường rất nhỏ.
Một số phần mềm phân tích dữ liệu phổ biến hiện nay:
SAS là một phần mềm được ưa chuộng bởi những người có trình độ cao nhờ khả năng lập trình mạnh mẽ, tuy nhiên, việc học SAS không hề đơn giản và đòi hỏi nghiên cứu chuyên sâu Để sử dụng hiệu quả, người dùng cần viết chương trình để thao tác và phân tích dữ liệu Một thách thức lớn là khi chương trình gặp lỗi, người dùng cần có khả năng xác định và sửa chữa vấn đề đó.
– SAS rất mạnh trong lĩnh vực quản lý dữ liệu, cho phép người sử dụng thao tác dữ liệu hầu như với mọi cách có thể
SAS cho phép xử lý nhiều file dữ liệu đồng thời, giúp giảm thiểu sự phức tạp trong việc chuẩn bị dữ liệu cho các nhiệm vụ phân tích yêu cầu làm việc với nhiều nguồn dữ liệu.
SAS có khả năng quản lý các tệp dữ liệu lớn với tối đa 32.768 biến, và số lượng bản ghi có thể rất lớn, chỉ bị giới hạn bởi dung lượng ổ cứng.
SAS nổi bật trong phân tích thống kê nhờ vào khả năng thực hiện các phương pháp như ANOVA, phân tích mô hình hỗn hợp và phân tích nhiều chiều, mang lại sức mạnh vượt trội cho người dùng trong việc xử lý và phân tích dữ liệu.
– SAS có các công cụ vẽ đồ thị mạnh nhất (SAS/Graph) so với hai bộ chương trình còn lại.
4 https://www.sas.com/en_us/home.html
Việc học và hiểu cách quản lý dữ liệu trong SAS tốn nhiều thời gian, trong khi nhiều nhiệm vụ quản lý phức tạp có thể được thực hiện dễ dàng bằng những lệnh đơn giản trong SPSS và STATA.
– Yếu đối với hồi qui logistic kiểu thứ tự và kiểu phạm trù (vì các lệnh này là đặc biệt khó) và các phương pháp ước lượng mạnh.
– Nó cũng có hỗ trợ một ít cho phân tích dữ liệu theo lược đồ mẫu, nhưng lại hạn chế hơn so với STATA.
SPSS là phần mềm phân tích dữ liệu được ưa chuộng nhờ vào giao diện thân thiện và dễ sử dụng Người dùng có thể thực hiện phân tích bằng cách chọn các thủ tục và biến cần thiết thông qua menu thả xuống, giúp nhanh chóng nhận được kết quả trên màn hình Mặc dù SPSS cung cấp ngôn ngữ cú pháp cho những người muốn tìm hiểu sâu hơn, nhưng việc sử dụng cú pháp này có thể khá phức tạp và không trực quan cho người mới.
SPSS sở hữu một bộ soạn thảo dữ liệu tương tự như Excel, cho phép người dùng nhập liệu và mô tả các thuộc tính của dữ liệu một cách dễ dàng Chính vì vậy, SPSS được đánh giá là phần mềm thân thiện và dễ sử dụng cho người mới bắt đầu.
SPSS là công cụ mạnh mẽ trong lĩnh vực phân tích phương sai, cho phép thực hiện nhiều loại kiểm định tác động riêng biệt Ngoài ra, SPSS cũng hỗ trợ phân tích nhiều chiều, bao gồm các phương pháp như phân tích phương sai nhiều chiều, phân tích nhân tố và phân tích nhóm tổ.
SPSS sở hữu giao diện người dùng thân thiện, giúp người sử dụng dễ dàng tạo và chỉnh sửa đồ thị Sau khi tạo xong, người dùng có thể tùy chỉnh đồ thị theo ý muốn, đảm bảo chất lượng cao để có thể tích hợp vào các tài liệu như Word hoặc PowerPoint.
Một trong những điểm yếu lớn nhất của SPSS là khả năng xử lý các vấn đề ước lượng phức tạp, dẫn đến khó khăn trong việc xác định sai số cho các ước lượng này.
– SPSS cũng không hỗ trợ các công cụ phân tích dữ liệu theo lược đồ mẫu.
– SPSS không có công cụ quản lý dữ liệu thật mạnh
SPSS chỉ xử lý một file dữ liệu tại một thời điểm, điều này hạn chế khả năng phân tích khi cần làm việc với nhiều file dữ liệu đồng thời Mỗi file dữ liệu có thể chứa tối đa 4096 biến, trong khi số lượng bản ghi phụ thuộc vào dung lượng của đĩa cứng.
5 https://www.ibm.com/products/spss-statistics/pricing
– Ngôn ngữ cú pháp của SPSS phức tạp hơn so với STATA, nhưng lại có phần đơn giản hơn, ít mạnh hơn SAS.
2.1.3 Stata (Software for Statistics and Data Science) 6
STATA là một phần mềm thống kê được ưa chuộng bởi người mới bắt đầu và người dùng chuyên nghiệp nhờ vào tính dễ học và khả năng mạnh mẽ Phần mềm cho phép người dùng nhập lệnh trực tiếp, phù hợp với những ai mới làm quen, hoặc soạn thảo các chương trình bao gồm nhiều lệnh để thực hiện cùng một lúc Nếu có lỗi trong chương trình, người dùng cũng có thể dễ dàng nhận biết và sửa chữa.
– Cho phép thực hiện các thao tác phức tạp về dữ liệu một cách dễ dàng.
STATA nổi bật với sức mạnh trong phân tích hồi quy, cung cấp các công cụ dễ sử dụng cho việc thực hiện hồi quy và hồi quy logistic Những cải tiến mới đã giúp đơn giản hóa quá trình giải thích kết quả hồi quy logistic, trong khi hồi quy logistic thứ tự và hồi quy logistic phạm trù cũng được thực hiện một cách dễ dàng.
Giới thiệu về Excel và lí do sử dụng phần mềm Excel để phân tích dữ liệu
2.2.1 Giới thiệu chung về Excel
Phần mềm bảng tính, như Excel và Google Trang tính, là công cụ quan trọng hỗ trợ tính toán trong nhiều lĩnh vực như nghiên cứu khoa học, phân tích tài chính và kế toán – thống kê Bài viết này sẽ tập trung vào việc nghiên cứu ứng dụng của Excel trong các lĩnh vực này.
Microsoft Excel là một ứng dụng bảng tính mạnh mẽ thuộc bộ Microsoft Office, cho phép người dùng thực hiện các bảng tính chuyên nghiệp với định dạng tệp XLS Chương trình này bao gồm nhiều ô được tạo bởi các dòng và cột, giúp việc nhập dữ liệu và tính toán trở nên dễ dàng Excel phù hợp với nhiều đối tượng như doanh nhân, sinh viên và chuyên gia, hỗ trợ trong việc thu thập, phân tích dữ liệu, và dự đoán kinh tế Người dùng có thể áp dụng Excel để giải quyết các bài toán kinh tế, kế toán, tài chính và quản lý cơ sở dữ liệu trong học tập và công việc.
Trong suốt nhiều thập kỷ, Excel đã trở thành công cụ quan trọng cho việc xây dựng cơ sở dữ liệu và phân tích số liệu của hàng triệu doanh nghiệp toàn cầu Ngoài ra, chương trình này cũng được giảng dạy cho trẻ em trong trường học Với nhiều tính năng mới trong phiên bản cập nhật gần đây, Excel xứng đáng giữ vững vị trí là phần mềm xử lý dữ liệu hàng đầu thế giới.
2.2.2 Các thao tác cơ bản trong Excel
Thao tác ban đầu: Khởi động, trang bảng tính, thoát khỏi Excel, lưu giữ và đóng bảng tính. Nhập dữ liệu trong bảng tính:
+ Địa chỉ (Đặt tên cho các ô hoặc nhóm ô; đặt tên cho hằng, công thức)
+ Thao tác nhập dữ liệu, các kiểu dữ liệu nhập (kí tự, số, ngày tháng, công thức,…), nhập dữ liệu tự động cho dãy biến đổi đều.
Hiệu chỉnh và định dạng bảng tính:
+ Lựa chọn khối ô, hiệu chỉnh (cấu trúc bảng, dữ liệu trong ô)
+ Định dạng bảng tính (theo mẫu, dạng kí tự, dạng số, dạng lề,…)
Trong Excel, có nhiều hàm cơ bản như hàm số học, hàm thống kê, hàm ngày tháng, hàm ký tự, hàm logic, hàm điều kiện và các hàm tìm kiếm, giúp người dùng thực hiện các phép toán và phân tích dữ liệu hiệu quả Ngoài ra, Excel cũng hỗ trợ người dùng vẽ, hiệu chỉnh và định dạng đồ thị, tạo điều kiện thuận lợi cho việc trực quan hóa thông tin.
2.2.3 Phiên bản Excel trong Office 365 mới nhất hiện nay
Với phiên bản Excel mới nhất hiện nay, sử dụng office 365 với nhiều tính năng vượt trội so với các phiên bản office 2003, 2007, 2010, 2013, 2016 trước đây
Hàm mới này tương tự như hàm CONCATENATE nhưng có nhiều ưu điểm hơn Nó không chỉ ngắn gọn và dễ nhập hơn mà còn hỗ trợ cả tham chiếu dải ô bên cạnh tham chiếu ô đơn lẻ.
Người dùng thường cảm thấy mệt mỏi với việc sử dụng các hàm IF lồng nhau phức tạp, nhưng hàm IFS là giải pháp lý tưởng Hàm IFS cho phép kiểm tra các điều kiện theo thứ tự mà người dùng đã chỉ định, và trả về kết quả ngay khi một điều kiện được thỏa mãn Ngoài ra, người dùng có thể thêm yếu tố "bao gồm mọi trường hợp" để xử lý khi không có điều kiện nào được đáp ứng.
Hàm này trả về số lớn nhất trong một dải ô, đáp ứng một tiêu chí đơn lẻ hoặc nhiều tiêu chí. MINIFS
Hàm này giống với hàm MAXIFS nhưng trả về số nhỏ nhất trong một dải ô, đáp ứng một tiêu chí đơn lẻ hoặc nhiều tiêu chí
Hàm này đánh giá một biểu thức dựa trên danh sách giá trị theo thứ tự và trả về kết quả trùng khớp đầu tiên Nếu không tìm thấy kết quả nào trùng khớp, nó sẽ trả về yếu tố "khác".
Hàm này kết hợp văn bản từ nhiều dải ô và mỗi mục được phân tách bởi dấu tách mà người dùng chỉ định
Biểu đồ dạng bản đồ
Người dùng có khả năng tạo biểu đồ bản đồ để so sánh giá trị và thể hiện các danh mục cho nhiều khu vực địa lý khác nhau Tính năng này rất hữu ích khi dữ liệu của người dùng bao gồm các khu vực như quốc gia, tiểu bang, hạt hoặc mã bưu điện.
Hình 2.1 Biểu thị dân số của các quốc gia trên thế giới
Biểu đồ hình phễu là công cụ hữu ích để thể hiện giá trị qua các giai đoạn trong quy trình, chẳng hạn như số lượng khách hàng tiềm năng trong từng bước của quy trình bán hàng Thông thường, các giá trị sẽ giảm dần, tạo nên hình dáng giống như phễu, giúp người dùng dễ dàng nhận diện và phân tích hiệu quả bán hàng.
Hình 2.2 Biểu thị số khách mua hàng tiềm năng ở từng giai đoạn trong một quy trình bán hàng
Viết hoặc phác thảo ý tưởng bằng họa tiết bút chì mới (Như hình 2.3)
Hình 2.3 Bút chì số để viết/phác thảo ý tưởng bằng bút chì mới
Bộ bút linh động, có thể tùy chỉnh
Tạo bộ bút cá nhân hóa theo nhu cầu người dùng, Office sẽ lưu trữ bộ bút trong Word, Excel và PowerPoint trên tất cả các thiết bị Windows của người dùng.
Hình 2.4 Bộ bút linh động có thể tùy chỉnh trong Excel 365
Việc nhập phương trình toán học đã trở nên đơn giản hơn bao giờ hết Người dùng chỉ cần truy cập vào Chèn > Phương trình > Viết tay phương trình để thêm các phương trình phức tạp vào sổ làm việc Với thiết bị cảm ứng, người dùng có thể sử dụng ngón tay hoặc stylus để viết trực tiếp, và Excel sẽ tự động chuyển đổi thành văn bản Nếu không có thiết bị cảm ứng, người dùng vẫn có thể sử dụng chuột để nhập phương trình Ngoài ra, người dùng có thể dễ dàng xóa, chọn và chỉnh sửa nội dung đã viết bất cứ lúc nào.
Hình 2.5 Ứng dụng cách viết tay phương trình
Công cụ Chọn bằng dây trong tầm tay người dùng
Excel đã tích hợp tính năng Chọn Bằng dây, cho phép người dùng tự do chọn vùng cụ thể trong bản vẽ tay Bằng cách sử dụng công cụ này, người dùng có thể dễ dàng điều chỉnh các đối tượng theo ý muốn của mình.
Chuyển đổi hình vẽ tay sang hình dạng
Tab vẽ cho phép người dùng lựa chọn kiểu vẽ mực và thực hiện chú giải viết tay trên thiết bị cảm ứng Người dùng còn có thể chuyển đổi các chú giải viết tay thành hình dạng bằng cách chọn chúng và thực hiện chuyển đổi Điều này mang lại sự tự do cho người dùng trong việc vẽ tự do, đồng thời đảm bảo tính đồng nhất và tiêu chuẩn của hình dạng đồ họa.
Sử dụng bút Surface để chọn và thay đổi đối tượng
Trong Excel, người dùng có thể dễ dàng chọn một khu vực bằng bút bề mặt mà không cần sử dụng công cụ chọn trên ruy-băng Chỉ cần nhấn nút thùng dầu trên bút và vẽ để tạo vùng chọn Sau đó, người dùng có thể sử dụng bút để di chuyển, thay đổi kích cỡ hoặc xoay các đối tượng viết tay.
Các tính năng trợ năng được cải thiện
Các bản sửa lỗi một lần bấm cho các sự cố về trợ năng
Bộ kiểm tra Trợ năng đã được cải tiến đáng kể, cung cấp hỗ trợ cập nhật theo các tiêu chuẩn quốc tế và những đề xuất hữu ích, giúp tài liệu của người dùng trở nên dễ sử dụng hơn.
Hình 2.6 Biểu thị bộ kiểm tra Trợ năng trong Execl office 365 Âm thanh hữu ích giúp cải thiện trợ năng
Bật gợi ý bằng âm thanh để hướng dẫn người dùng khi người dùng làm việc (Như hình
Hình 2.7 Chức năng bật gợi ý bằng âm thanh
Chia sẻ dễ dàng hơn
Chèn liên kết gần đây
ỨNG DỤNG EXCEL TRONG PHÂN TÍCH VÀ XỬ LÍ DỮ LIỆU
Nhập liệu/ nhập dữ liệu tự động cho dãy biến đổi đều
Excel hỗ trợ nhiều loại dữ liệu như ký tự, số, ngày tháng, công thức, logic và lỗi Trong một bảng tính, mỗi ô chỉ chứa một kiểu dữ liệu duy nhất Một trong những ưu điểm nổi bật của Excel là khả năng tự động hoàn thành các đáp án tương đồng; từ lần nhập thứ hai trở đi, người dùng chỉ cần gõ chữ cái đầu và đáp án sẽ tự động hiện ra, giúp tiết kiệm thời gian nhập liệu Ngoài ra, Excel cho phép quản lý dữ liệu lớn với dung lượng lên tới 5 GB.
Định dạng bảng
Excel cho phép định dạng bảng một cách nhanh chóng và thuận tiện, hỗ trợ nhiều kiểu dữ liệu như ký tự, số và định dạng lề Với các thao tác cơ bản dễ sử dụng, người dùng có thể tạo bảng theo mục đích trình bày và biểu thị của mình.
Từ một bảng số liệu thô ban đầu sau khi đã phân tích, tính ra phần %
Hình 3.2 Số liệu thô ban đầu trước khi tạo bảng
Tách/ Gộp dữ liệu
Có thể kết hợp hai chỉ số Coef và 95% CI vào một cột duy nhất để làm cho bảng biểu trở nên gọn gàng và tiết kiệm diện tích Việc này có thể thực hiện dễ dàng chỉ bằng cách sử dụng hàm IF.
Bảng dữ liệu ban đầu:
Hình 3.4 Số liệu thô ban đầu trước khi gộp dữ liệu
Bảng dữ liệu sau khi sử dụng lệnh IF để gộp các biến (Như hình 3.5)
Sử dụng các hàm trong Excel
Sử dụng hàm Sum để tính tổng các đối tượng tham gia nghiên cứu trả lời khảo sát về trình độ học vấn của đối tượng (Như hình 3.6)
Hình 3.6 Sử dụng hàm SUM để tính tổng đối tượng tham gia nghiên cứu
Sử dụng hàm AVERAGE để tính giá trị trung bình thu nhập của các đối tượng tham gia nghiên cứu (Như hình 3.7)
Hình 3.7 Sử dụng hàm AVERAGE để tính giá trị trung bình thu nhập của các đối tượng tham gia nghiên cứu
Sử dụng lệnh Sort để sắp xếp dữ liệu theo độ tuổi từ thấp đến cao, giúp xác định người có độ tuổi thấp nhất và cao nhất Đồng thời, áp dụng hàm COUNT để tính tỷ lệ người tham gia nghiên cứu theo từng độ tuổi, từ đó xác định nhóm tuổi chiếm ưu thế nhất.
Hình 3.8 Sử dụng lệnh Sort để để sắp xếp tuổi từ thấp đến cao, thống kê được người có độ tuổi thấp nhất và cao nhất
Sau khi đã sử dụng lệnh sort (Như hình 3.9)
Hình 3.9 Sau khi sử dụng lệnh Sort để để sắp xếp tuổi từ thấp đến cao, thống kê được người có độ tuổi thấp nhất và cao nhất
Vẽ biểu đồ
Từ bảng dữ liệu có sẵn, người dùng có thể tạo ra nhiều loại biểu đồ khác nhau trong Excel Phần mềm này cung cấp tính năng gợi ý các loại biểu đồ phù hợp với dữ liệu, giúp người dùng lựa chọn một cách hợp lý tùy theo nhu cầu và vấn đề cụ thể.
Hình 3.10 Biểu đồ thể hiện các loại chấn thương của người bệnh liên quan đến chất lượng cuộc sống của những đối tượng
- Hiệu chỉnh các dạng biểu đồ dễ dàng, đơn giản trên cùng dữ liệu có sẵn (Như hình 3.11)
Hình 3.11 Biểu đồ thể hiện các loại chấn thương của người bệnh liên quan đến chất lượng cuộc sống của những đối tượng.
Dùng VBA để xử lý dữ liệu
VBA (Visual Basic for Applications) là ngôn ngữ lập trình được tích hợp trong Excel và các ứng dụng khác của Microsoft Office, cho phép người dùng tự động hóa và tùy chỉnh các tác vụ trong phần mềm.
Lập trình VBA trong Excel cho phép máy tính tự động hóa các thao tác mà người dùng muốn thực hiện, giúp tiết kiệm thời gian so với việc làm thủ công Để bắt đầu lập trình VBA trong Excel 365, người dùng cần vào File, chọn Options để mở cửa sổ Excel Options, sau đó chọn Customize và tích vào Developer trước khi nhấn OK.
Hình 3.12 Cửa sổ Excel Options c) Khi đó trên thanh công cụ cụ Excel sẽ xuất hiện thẻ Developer Chọn Developer
To access the Developer tab, right-click on VBAProject and select Insert followed by Module, as shown in Figure 3.14 This is where you will typically write your code in the inserted module.
Hình 3.14 Cửa sổ Visual Basic for Applications
Trong đồ án này ta sử dụng phiên bản VBA 7.1
3.6.1 Dùng VBA tính toán tìm công thức xấp xỉ của độ rọi lux và điện trở của quang trở
Bảng 3.1 cung cấp dữ liệu về điện trở và độ rọi của quang trở, từ đó chúng ta cần xác định mối quan hệ giữa hai yếu tố này Để thực hiện điều này, có thể sử dụng VBA để xử lý dữ liệu và tìm ra công thức xấp xỉ cho mối quan hệ giữa điện trở và độ rọi (lux) của quang trở.
Khi ánh sáng chiếu vào quang trở, điện trở của nó sẽ thay đổi; cụ thể, khi ánh sáng càng mạnh, điện trở càng giảm Tuy nhiên, mối quan hệ giữa điện trở và ánh sáng không phải là tuyến tính, mà thay đổi theo cấp số nhân.
Bảng 3.1 Giá trị điện trở của quang trở phụ thuộc vào ánh sáng r lux
Hình 3.15 Mối quan hệ giữa điện trở (R) và độ rọi (lux)
- Tuy nhiên dạng mỗi quan hệ giữa log(lux) và log(R ) có thể coi là tuyến tính có dạng(Như hình 3.16): log(lux) = m*log(R ) + b (3.1)
- Từ đó ta có thể xấp xỉ công thức mối quan hệ của độ rọi và điện trở là: lux = R m * 10 b (3.2)
Hình 3.16 Mối quan hệ giữa log(lux) và log(R )
Các bước phân tích dữ liệu độ rọi lux và điện trở của quang trở bao gồm:
- Nhập dữ liệu về điện trở tương ứng với độ rọi đo được vào bảng trong trang tính rawData
Chạy đoạn code VBA để thực hiện tính toán và tìm công thức xấp xỉ cho mối quan hệ giữa độ rọi và điện trở của quang trở lux Các bước tính toán bao gồm việc tính các giá trị log(lux) và log(r), sau đó xác định các tham số m và b trong công thức R = m * 10^b.
m = INDEX(LINEST(,),1).
Để tính toán giá trị b, sử dụng công thức b = INDEX(LINEST(,),2) Tiếp theo, áp dụng các tham số m và b đã tìm được để tính các giá trị lux và r Cuối cùng, xác định sai số giữa giá trị tính toán theo công thức và giá trị thực tế đã đo được.
Lưu đồ thuật toán phân tích mối quan hệ giữa độ rọi(lux) và điện trở(r ) được thể hiện như hình3.17.
Hình 3.17 Lưu đồ thuật toán
Thuật toán thực hiện các bước xử lý như sau:
- Đầu tiên ta nhập các giá trị tương ứng của điện trở(R ) và độ rọi(lux) của quang trở vào bảng trong Sheets rawData.
Sau khi hoàn tất việc nhập liệu, chúng ta sẽ thực hiện đoạn mã như trong PL1 Đầu tiên, cần xác định kích thước của bảng dữ liệu trong Sheets rawData, bao gồm số hàng và số cột Dựa trên kích thước này, chúng ta sẽ sao chép vùng dữ liệu cần xử lý từ Sheets rawData sang Sheets Lux_Analysis.
Sau khi sao chép dữ liệu vào Sheets Lux_Analysis, chúng ta tiến hành tính toán log10(lux) và log10(R) Việc sử dụng vòng lặp for giúp đơn giản hóa quá trình tính toán các giá trị này tương ứng với từng giá trị lux và R trong bảng dữ liệu.
- Tiếp theo ta sử dụng các giá trị log10(lux) và log10(R) tính toán được ở trên để đi tìm tham số m và b trong công thức xấp xỉ (3.2).
Với giá trị tìm được của m và b, chúng ta có thể tính toán giá trị của lux theo công thức (3.2) Do đây là giá trị tính toán xấp xỉ, nên cần tiến hành so sánh với giá trị thực tế Để thực hiện việc so sánh này, ta sẽ tính sai số tương đối.
- Cuối cùng sau tất cả các bước trên việc tính toán đã hoàn thành và đoạn code đã chạy xong.
Để tìm công thức xấp xỉ bằng cách xác định các tham số m và b, bạn có thể sử dụng đoạn mã VBA tại PL1 Để sao chép dữ liệu từ trang tính "rawData" sang trang tính "Lux_Analysis", hãy sử dụng lệnh sau:
Call CopySection("lux_analysis", "rawData", 1, 1, last_row1, last_col1, "lux_analysis",
Trong đoạn lệnh trên ta đã sử dụng một đoạn sub CopySection(PL2) với các tham số truyền vào theo thứ tự là:
- frmwkbook: tên WorkBooks nguồn nơi chứa dữ liệu cần sao chép (ở ví dụ này là lux_analysis).
- frmwksht: tên Sheets nguồn chứa dữ liệu cần sao chép trong WorkBooks nguồn (ở ví dụ này là rawData).
- r1: vị trí hàng ô đầu tiên của vùng cần sao chép trong Sheets nguồn (ở ví dụ này là 1).
- c1: vị trí cột ô đầu tiên của vùng cần sao chép trong Sheets nguồn (ở ví dụ này là 1).
- r2: vị trí hàng cuối cùng của vùng cần sao chép trong Sheets nguồn (ở ví dụ này là last_row1).
- c2: vị trí cột cuối cùng của vùng cần sao chép trong Sheets nguồn (ở ví dụ này là last_col1).
- towkbook: tên WorkBooks đích nơi dữ liệu được sao chép đến (ở ví dụ này là lux_analysis).
- towksht: tên Sheets đích nơi dữ liệu được sao chép đến (ở ví dụ này là lux_analysis).
- row: vị trí hàng ô đầu tiên do người dùng tự chọn để xác đinh vị trí chứa dữ liệu trong Sheets đích (ở ví dụ này là 1).
Col là vị trí cột đầu tiên mà người dùng chọn để xác định vị trí chứa dữ liệu trong Sheets đích (trong ví dụ này là 1) Để tính toán các giá trị, ta sử dụng đoạn code như ở PL3 để thêm công thức vào các ô tương ứng, với các tham số cần truyền vào.
- Sub AddFormula(wkbook, wksht, row, col, formula$)
- wkbook: tên WorkBooks cần tính toán.
- wksht: tên Sheets cần tính toán.
- row: vị trí hàng của ô được thêm công thức vào.
- col: vị trí cột của ô được thê công thức vào.
- formula$: biến chứa công thức cần thêm vào.
Cụ thể trong ví dụ này ta đã dùng các lệnh như sau để thêm công thức vào:
- Tính toán log10(lux) và log10(R): formula$ = "=log(" & "A" & Trim(Str(i)) & ")"
Call AddFormula("lux_analysis", "Lux_Analysis", i, 3, formula$) formula$ = "=log(" & "B" & Trim(Str(i)) & ")"
Call AddFormula("lux_analysis", "Lux_Analysis", i, 4, formula$)
- Tính toán giá các tham số m và b: formula$ = "=INDEX(LINEST(D2:D" & Trim(Str(last_row1)) & ",C2:C" & Trim(Str(last_row1)) & "),1)"
Call AddFormula("lux_analysis", "Lux_Analysis", 3, 7, formula$)
Call AddFormula("lux_analysis", "Lux_Analysis", 3, 9, formula$) formula$ = "=INDEX(LINEST(D2:D" & Trim(Str(last_row1)) & ",C2:C" & Trim(Str(last_row1)) & "),2)"
Call AddFormula("lux_analysis", "Lux_Analysis", 3, 8, formula$)
- Tính toán giá trị lux theo công thức xấp xỉ: formula$ = "^H3"
Call AddFormula("lux_analysis", "Lux_Analysis", 3, 10, formula$) For i = 2 To last_row1 formula$ = "=A" & Trim(Str(i)) & "^G3*J3"
Call AddFormula("lux_analysis", "Lux_Analysis", i, 5, formula$)
- Tính tóa sai số tương đối: formula$ = "s(E" & Trim(Str(i)) & "-B" & Trim(Str(i)) & ")/B" & Trim(Str(i)) & "*100"
Call AddFormula("lux_analysis", "Lux_Analysis", i, 6, formula$)
Ngoài ra trong ta còn sử dụng các hàm LastRow và LastCol để xác định phạm vi của dữ liệu trong một Sheets.
Hàm LastRow trong PL5 tìm kiếm các hàng từ dưới lên và trả về chỉ số của hàng đầu tiên chứa dữ liệu Giá trị này xác định hàng cuối cùng có dữ liệu trong bảng.
Hàm LastCol tại PL6 tìm kiếm các cột từ phải qua trái trên trang tính và trả về chỉ số của cột cuối cùng chứa dữ liệu Đoạn sub gop (wkbook, wksht, r1, c1, r2, c2) tại PL9 thực hiện việc gộp hai ô tại địa chỉ hàng r1, cột c1 và hàng r2, cột c2 Cuối cùng, đoạn sub VBColorCodes (wkbook, wksheet, ByVal vb_color_name As String, ByVal row) giúp xác định mã màu trong VBA.
As Integer, ByVal col As Integer) xẽ thực hiên việc tô màu nền những ô mong muốn(ở PL10).
Trong đó các tham số lần lượt là:
- vb_color_name: tên màu muốn tô(vbBlack, vbRed, vbGreen, vbYellow, vbBlue, vbMagenta, vbCyan, vbWhite).
- row: hàng của ô muốn tô
- col: cột của ô muốn tô Đoạn sub định dạng (Workbook, Worksheet, ByVal r1, ByVal c1, ByVal r2, ByVal c2) sẽ định dạng đường viền cho vùng được chọn (ở PL11).
Kết quả đạt được khi thực hiện với dữ liệu như trong Bảng 3.1 (Như trong hình 3.18)
Hình 3.18 Kết quả phân tích dữ liệu độ rọi lux và điện trở của quang trở
Các giá trị tìm được là:
Với các giá trị trên ta có công thức xấp xỉ mối quan hệ của độ rọi và điện trở là: lux = R-0.255493441 * 104.143133773 (3.3)
Dùng công thức 3.3 ta tính toán được giá trị độ rọi(lux ) theo điện trở(r ) như bảng 3.2
Gía trị lux tính theo công thức tìm được Sai số tương đối
Bảng 3.2 Giá tri lux tính theo công thức và sai số tương đối
Sai số tương đối giữa giá trị xấp xỉ tính theo công thức 3.3 và giá trị thực tế được tính toán trong Sheets Lux_Analysis (Như bảng 3.2).
Dựa vào bảng 3.2, chúng ta nhận thấy rằng sai số tương đối của công thức rất nhỏ, với hầu hết các sai số nằm dưới 4% Điều này chứng tỏ rằng việc tính toán theo công thức này là khá chính xác.
