Tính toán hàng hóa tàu dầu: Các hàm bảng tính

Các hàm bảng tính hầu hết là đơn giản nhưng áp dụng chúng trong bài toán tính hàng dầu lại có phần phức tạp. Chúng ta lần lượt tìm hiểu về chúng.

MATCH(SearchCriterion, LookupArray, Type)

Hàm này trả về chỉ số tương đối của một hạng mục mà khớp giá trị cho trước, trong một mảng dữ liệu là một hàng đơn hoặc một cột đơn, hoặc một phần của chúng.

SearchCriterion là giá trị mà được tìm kiếm.

LookupArray là mảng để tìm kiếm, có thể là một hàng đơn hoặc một cột đơn, hoặc một phần của một hàng đơn hoặc một cột đơn.

Type là kiểu tìm kiếm. Nếu nó bằng 1 hoặc không có mặt, mảng dữ liệu được coi như xếp theo thứ tự tăng dần. Nếu Type bằng -1, mảng dữ liệu được coi như xếp theo thứ tự giảm dần. Hàm trả về chỉ số của hạng mục cuối cùng mà giá trị tìm kiếm chạm tới hoặc vượt qua nó.

Nếu Type = 0, khớp chính xác sẽ được tìm. Chỉ trong trường hợp này tìm theo biểu thức chính quy hoặc kí tự đại diện mới được hỗ trợ. Bạn phải đảm bảo đã kích hoạt chức năng tương ứng bằng cách vào menu Tools -> Options -> LibreOffice Calc -> Calculate của LibreOffice Calc.

Ví dụ

MATCH(51,A:A)

Hàm tìm kiếm trên cột A với giá trị tìm kiếm là 51, dừng lại ô A5 có giá trị 50, trả về chỉ số của ô này là 5 trong ô kết quả C8. Chú ý là vị trí trả về là tương đối trong khoảng tìm kiếm. Nếu chúng ta chỉ tìm trên khoảng A2:A10 thì giá trị trả về sẽ là 4.

MATCH(“[0-9]{5}”,A:A,0)

Ví dụ trên tìm theo biểu thức chính quy, trả về chỉ số của ô đầu tiên chứa dữ liệu có 5 chữ số, là số 16000, có chỉ số là 5.

Hàm MATCH cho chúng ta chỉ số của một ô dữ liệu. Rồi chúng ta có ngay chỉ số của ô kế tiếp bằng cách cộng thêm 1. Giá trị của hai ô liền nhau trở thành hai biên mà giá trị đo độ vơi thực tế của két dầu lọt vào trong khoảng đó. Số đo độ vơi là đầu vào của thao tác tra bảng tự động để tìm ra thể tích hàng lỏng, và bảng dữ liệu trong hồ sơ tàu chỉ cho các giá trị biên do đó chúng ta tra đối với các giá trị biên sau đó tiến hành nội suy để tìm thể tích chính xác.

INDEX(Reference, Row, Column, Range)

Ngược lại với hàm MATCH, hàm INDEX trả về giá trị của ô dữ liệu dựa trên chỉ số đưa ra về hàng và cột. Nó cũng có thể trả về tham chiếu tới một (hoặc nhiều) khoảng dữ liệu (range) gồm nhiều ô.

Reference là tham chiếu tới khoảng dữ liệu cần xử lý, có thể vào trực tiếp hoặc chỉ ra tên khoảng. Nếu tham chiếu gồm nhiều khoảng thì bạn phải đóng chúng trong ngoặc đơn.

Row là chỉ số hàng tới khoảng tham chiếu. Nếu Row = 0 hoặc không có mặt, tất cả các hàng được trả về.

Column là chỉ số cột tới khoảng tham chiếu. Nếu Column = 0 hoặc không có mặt, tất cả các cột được trả về.

Range là chỉ số của khoảng con nếu Reference tham chiếu tới nhiều khoảng.

Chú ý:

  • Ba tham số cuối là tùy chọn
  • Đối số bỏ trống khác với đối số bị bỏ qua. Nếu bỏ trống đối số Row hoặc Column mà vẫn vào đối số phía sau chúng, bạn phải giữ dấu ngăn cách đối số. Ví dụ: INDEX(A1:B5,,1). Khi đó đối sỗ vẫn được coi là có mặt với giá trị mặc định (0). Đối số bị bỏ qua hoàn toàn nếu nó không có dấu ngăn cách đối số (dấu , hoặc ;) để xác định. Điều đó cho phép chương trình bảng tính đếm dấu ngăn cách để quyết định về các đối số vị trí.
  • Trường hợp nhiều hàng và / hoặc nhiều cột được trả về, nó không thể là một giá trị để gán tới một ô mà là một mảng với thao tác ấn tổ hợp phím Shift+ Ctrl+ Enter như mô tả chi tiết ở cuối bài.

Ví dụ

Ô C8 nhận giá trị của ô ở hàng thứ 5 của khoảng A1:A10, là 50.

Tham chiếu gồm hai khoảng con là A1:A10 và E1:E10, và ô C8 nhận kết quả từ khoảng con thứ hai.

VLOOKUP(SearchCriterion, Array, Index, Sorted)

Hàm tìm kiếm theo chiều dọc, nó kiểm tra giá trị cho trước xem có ở trong cột đầu tiên của mảng. Rồi hàm trả về giá trị trong cùng hàng của cột thứ Index. Nếu đối số Sorted được bỏ qua hoặc được vào là TRUE hoặc 1, nó cho rằng dữ liệu được sắp theo thứ tự tăng dần. Trong trường hợp này nếu chính xác SearchCriterion không được tìm thấy, giá trị cuối cùng mà nhỏ hơn SearchCriterion được coi là giá trị tìm được. Nếu Sorted được vào là FALSE hoặc 0, khớp chính xác phải được tìm thấy, nếu không kết quả là sai. Trong trường hợp này dữ liệu không cần phải sắp theo thứ tự tăng dần. Nói cách khác, nếu cột đầu tiên không được sắp theo thứ tự tăng dần, bạn cần vào FALSE hoặc 0 như đối số cho Sorted. VLOOKUP cũng hỗ trợ biểu thức chính quy.

SearchCriterion là giá trị tìm kiếm trong cột đầu tiên của mảng.

Array là tham chiếu bao gồm ít nhất hai cột.

Index là số của cột trong mảng Array mà chứa giá trị trả về. Cột đầu tiên là cột số 1.

Sorted là tham số tùy chọn dùng để xác định xem liệu cột đầu tiên của mảng có được sắp theo thứ tự tăng dần hay không, như đã mô tả bên trên.

Ví dụ

Giá trị tìm kiếm là 61, tìm trong mảng A1:E10, kết quả trả về trong cột thứ 5 (cột E). Cột đầu tiên (cột A) được sắp theo thứ tự tăng dần. Không có khớp chính xác được tìm thấy nhưng giá trị cuối cùng nhỏ hơn 61 là 60 nên hàng 6 được coi là chứa kết quả. Kết quả là ô ở hàng 6 cột E, có giá trị 1.05 được gán cho ô C8.

Hàm VLOOKUP chính là phương tiện tra bảng tự động trong chương trình.

TREND(DataY, DataX, NewDataX, LinearType)

Trả về các giá trị từ nội suy tuyến tính.

DataY đại diện cho mảng dữ liệu y.

DataX đại diện cho mảng dữ liệu x.

NewDataX đại diện cho mảng dữ liệu mới của x mà hàm cần trả về các giá trị y tương ứng.

LinearType quy định kiểu nội suy. Nếu LinearType khác 0 hoặc không có mặt trong đối số (mặc định), nội suy là tuyến tính thông thường, quan hệ giữa x và y là bậc nhất dạng y = ax +b. Nếu LinearType = 0, nội suy được tính toán qua điểm 0, hằng b được đặt tới 0 và quan hệ trở thành y = ax. Tên hàm TREND – xu hướng, là khái niệm lỏng gợi cho chúng ta nhu cầu cải tiến phương pháp. Đây là một chủ đề cần được bàn riêng, chẳng hạn các phương pháp toán học truyền thống như nội suy Lagrange đã trở nên lỗi thời so với ánh xạ được cung cấp bởi mạng nơron. Mạng nơron nhận DataX và DataY như mẫu luyện để tạo ánh xạ, rồi nó kết xuất các giá trị y mới cho các giá trị của NewDataX.

Tuy nhiên, phiên bản chương trình hiện tại ở đây chỉ đơn giản là nội suy giữa hai biên nên phương pháp không thành vấn đề.

Ba tham số cuối là tùy chọn. Ví dụ nếu DataX không có mặt thì nó là mảng { 1, 2, 3, …}.

Ví dụ

AND(LogicalValue1, LogicalValue2, …LogicalValue30)

Trong C/C++ và nhiều ngôn ngữ lập trình nói chung, nó là một phép toán logic, nhưng bảng tính xây dựng nó với vai trò của một hàm.

Trả về đúng nếu tất cả đối số là đúng (TRUE, một số khác 0 cũng có giá trị logic là TRUE), nếu không trả về sai (FALSE, số 0 có giá trị logic là FALSE). Đối số có thể là biểu thức logic hoặc mảng như A1:C5 mà chứa các giá trị logic.

LogicalValue1, LogicalValue2, …LogicalValue30 là các điều kiện để kiểm tra. Nếu một đối số là một khoảng dữ liệu, kết quả là đúng nếu tất cả các ô trong trong khoảng có giá trị đúng, nếu không là sai.

Ví dụ =AND(3 > 2, TRUE, 1 > 2) trả về sai.

Phép toán quan hệ <>

Mục này được đưa vào bài vì vấn đề kí hiệu. So sánh khác nhau trong C/C++ và nhiều ngôn ngữ lập trình khác sử dụng kí hiệu !=. Nhưng bảng tính sử dụng kí hiệu <>.

Ví dụ =AND(3 > 2, 1, B11 <> “”) trả về sai nếu ô B11 là rỗng.

IF(Test, ThenValue, OtherwiseValue)

Test là giá trị hay biểu thức logic.

ThenValue là giá trị trả về nếu Test là đúng.

OtherwiseValue là giá trị trả về nếu Test là sai.

Hai tham số cuối là tùy chọn. Nếu chúng được bỏ qua hàm trả về giá trị của Test. Nếu được để trống, chúng được thay bằng 0.

Ví dụ =IF(A1<>0,100/A1,0) trả về 100/(giá trị của ô A1) nếu giá trị của ô A1 khác 0, nếu không trả về 0.

ROUND(Number, Count)

Trả về Number được làm tròn tới Count chữ số thập phân với giá trị gần nhất. Nếu Count bị bỏ qua hoặc bằng 0, hàm trả về số nguyên gần nhất. Nếu Count là âm, hàm làm tròn tới 10, 100, 1000, vv gần nhất.

Ví dụ

=ROUND(2.346,2) trả về 2.35

=ROUND(2.346) trả về 2

=ROUND(12.346,-1) trả về 10

SUM(Number1, Number2, …Number30)

Trả về tổng của tất cả các đối số. Có thể lên tới 30 đối số từ Number1 tới Number30. Mỗi đối số có thể là một giá trị, một ô hay một khoảng các ô.

Ví dụ

Chúng ta xem xét một tình huống sử dụng hàm SUM nâng cao

Giả sử chúng ta có một bảng hóa đơn như trên. Có 7 ngày từ A1 tới A7. Tương ứng ở cột B là số lượng. Chúng ta muốn tính tổng số lượng nhưng chỉ đối với những ngày nhất định. Cột C có ô C1 là ngày bắt đầu 01-01-2018 và ô C2 là ngày kết thúc 01-02-2018. Chúng ta muốn tính tổng số lượng đối với các hóa đơn chỉ trong khoảng đó, tức là chỉ các hóa đơn trong tháng 1. Kết quả đặt trong ô C10. Vào công thức cho ô C10 là =SUM((A1:A7>=C1)*(A1:A7<C2)*(B1:B7)), sau đó ấn tổ hợp phím Shift+ Ctrl+ Enter (thay vì chỉ ấn Enter như thông thường), công thức trong thanh công thức sẽ được bổ sung đóng trong ngoặc nhọn. Kết quả là 19, là tổng số lượng của chỉ các ngày trong tháng 1, từ B1 tới B5.

Phân tích: Công thức thuộc dạng trên là công thức đặc biệt, gọi là công thức mảng. Ở đây trong phép nhân nó không nhân với một giá trị thông thường mà nhân với một mảng. Đối với công thức mảng chúng ta phải khẳng định công thức bằng tổ hợp phím Shift+ Ctrl+ Enter. Hai biểu thức quan hệ biểu diễn hai mảng các biểu thức logic riêng biệt theo từng hàng, giá trị logic rồi được chuyển thành giá trị số: TRUE trở thành 1, FALSE trở thành 0. Sau đó các mảng được nhân với nhau và được một mảng kết quả cùng kích thước. Những ngày mà điều kiện không thỏa sẽ cho giá trị 0 kéo theo tích bằng 0 đặt vào phần tử kết quả tương ứng, hệ quả là ngày đó không được tính. Tuy rằng kết quả của phép nhân là một mảng nhưng hàm SUM lại chấp nhận đối số là mảng nên chúng ta có một giá trị kết quả duy nhất mà có thể gán vào ô C10.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.