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


(0 comments)


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.




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



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.




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.




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.


Currently unrated

Comments

There are currently no comments

New Comment

required

required (not published)

optional

required