Họ các hàm kỹ thuật trong Excel trông giống như phần dễ nhất trong tham chiếu hàm. DEC2BIN chuyển một số thành một chuỗi nhị phân. HEX2DEC chuyển ngược lại. IMSUM cộng hai số phức. Mỗi cái trông giống như một bài tập định dạng. Nhưng thực tế không phải vậy. Đằng sau những cái tên này là một mã hóa bù hai mười bit (ten-bit two's complement) mà hầu hết các nhà phát triển đã không chạm tới kể từ lớp học kiến trúc máy tính, một định dạng số phức sống hoàn toàn bên trong các chuỗi, và các toán tử thao tác bit sẽ âm thầm tràn một số nguyên 64 bit nếu bạn dịch chuyển (shift) trước khi kiểm tra. Một công cụ bảng tính mô phỏng chính xác Excel không thể làm tròn bất kỳ điều nào trong số đó.
Các hàm được chia thành ba nhóm, và mỗi nhóm ẩn chứa một cái bẫy khác nhau. Chuyển đổi cơ số xoay quanh các số âm và các ngưỡng của từng cơ số. Số phức xoay quanh việc phân tích cú pháp và định dạng một chuỗi. Các thao tác bit là để duy trì bên trong giới hạn của Int64. Bài viết này đi qua từng nhóm như cách HotXLS triển khai nó, với các lệnh gọi bảng tính mà bạn thực sự sẽ viết.
Chuyển đổi cơ số và bù hai mười bit
Hướng thuận là phần mà mọi người mong đợi. DEC2BIN(9) cho ra "1001", và một đối số thứ hai tùy chọn sẽ đệm lề trái kết quả đến một độ rộng cố định. Cái bẫy là đầu vào âm. Excel không viết dấu trừ. Nó mã hóa giá trị dưới dạng một chuỗi bù hai mười chữ số trong cơ số đích, đó là lý do tại sao DEC2BIN(-5,10) trả về "1111111011" thay vì bất cứ thứ gì có dấu. Đối số vị trí (places) bị bỏ qua một khi giá trị là âm, bởi vì việc mã hóa đã được ghim cố định ở mười chữ số.
Mười chữ số là một mức ngân sách cố định, và ngân sách đó thiết lập phạm vi có thể biểu diễn trên mỗi cơ số. Trong hệ nhị phân, độ lớn chuyển sang nửa âm là 512, và mô-đun bao quanh (wrap modulus) là 1024, vì vậy một chuỗi nhị phân chỉ có dấu khi nó có độ dài chính xác là mười ký tự và giá trị của nó ít nhất là 512. Ý tưởng tương tự cũng áp dụng mở rộng với cơ số khác. Hệ bát phân sử dụng một ngưỡng một nửa là 2^29 và một mô-đun đầy đủ là 2^30. Hệ thập lục phân sử dụng 2^39 và 2^40. Trình đọc HotXLS áp dụng chính xác quy tắc này: nó tích lũy các chữ số, và chỉ khi chuỗi rộng mười ký tự và giá trị tích lũy nằm ở mức hoặc cao hơn ngưỡng một nửa thì nó mới trừ đi mô-đun đầy đủ để phục hồi giá trị có dấu. Một chuỗi chín ký tự luôn không âm, bất kể lớn thế nào.
Bộ mã hóa là hình ảnh phản chiếu. Một giá trị không âm được chuyển đổi từng chữ số và có thể được đệm số không tùy chọn theo độ rộng được yêu cầu, và nó bị từ chối nếu nó vượt quá trần dương của cơ số hoặc nếu độ rộng được yêu cầu quá hẹp để chứa nó. Một giá trị âm trước tiên được đưa vào phạm vi bằng cách cộng mô-đun đầy đủ, giúp chuyển nó thành một giá trị có biểu diễn cơ số luôn là mười chữ số, và sau đó các chữ số được phát ra với các số không dẫn đầu để điền đầy độ rộng. Việc kiểm tra phạm vi chung duy nhất, các biên dưới và trên đối xứng cho mỗi cơ số, là điều giữ cho DEC2BIN, DEC2OCT và DEC2HEX nhất quán với nhau tại các điểm biên của chúng.
That leaves the cross-base conversions, the ones such as HEX2BIN and OCT2HEX that change base without passing through decimal in the function name. The implementation does not carry a separate routine for every ordered pair. It parses the input string into a signed decimal value using the source base, then formats that decimal value into the destination base. Decimal is the pivot. One parse routine and one format routine, composed, cover every combination, and because both halves share the same ten-digit signed convention, a negative value survives the trip with its sign intact.
Số phức là chuỗi, vì vậy công việc là phân tích cú pháp
Excel không có kiểu dữ liệu số phức. Một giá trị phức là chuỗi "a+bi", và mọi hàm trong họ IM đều nhận những chuỗi đó vào và trả lại một chuỗi. COMPLEX xây dựng chuỗi từ một phần thực (real) và một phần ảo (imaginary). IMSUM, IMSUB, IMPRODUCT và IMDIV phân tích cú pháp các đối số của chúng, thực hiện số học trên các phần số, và định dạng kết quả trở lại thành một chuỗi. Công việc về số là đại số cơ bản. Khó khăn hoàn toàn nằm ở việc chuyển đổi văn bản thành hai số dấu phẩy động một cách đáng tin cậy, và đó là nơi trình phân tích cú pháp nội bộ chứng minh giá trị của mình.
Hai chi tiết trong trình phân tích cú pháp đó rất dễ làm sai. Đầu tiên là đơn vị ảo trần. Chuỗi "i" có nghĩa là một lần i, không phải là số không và không phải là một lỗi, vì vậy khi hệ số đứng trước hậu tố bị trống hoặc là một dấu cộng đơn độc, trình phân tích cú pháp phải đọc nó là giá trị 1, và một dấu trừ đơn độc là -1. Bỏ qua điều đó và IMSUM("i","i") sẽ không còn là 2i nữa. Thứ hai là ký hiệu khoa học (scientific notation) va chạm với dấu ngăn cách các phần thực và phần ảo. Trình phân tích cú pháp tìm thấy dấu ngăn cách đó bằng cách quét dấu cộng hoặc trừ, nhưng một số được viết là "1.5E-3" chứa một dấu trừ thuộc về số mũ (exponent). Do đó, việc quét từ chối xử lý dấu cộng hoặc trừ làm dấu ngăn cách khi ký tự ngay trước nó là e hoặc E. Không có biện pháp bảo vệ đó, phần thực sẽ bị xé làm đôi tại dấu số mũ và quá trình phân tích cú pháp sẽ thất bại trên một đầu vào hoàn toàn hợp lệ.
Bản thân hậu tố được bảo toàn thay vì chuẩn hóa. Excel chấp nhận cả i và j, và HotXLS ghi nhớ ký tự nào mà đầu vào đã sử dụng để kết quả định dạng mang cùng chữ cái đó. Định dạng sau đó áp dụng các cách viết tắt quy ước: một phần ảo bằng một sẽ in ra chỉ hậu tố, trừ một là -i, một phần ảo bằng không sẽ sụp đổ thành một phần thực phẳng, và một phần thực bằng không sẽ loại bỏ phần dẫn đầu 0+.
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Engineering');
// Negative input: a ten-bit two's complement, places argument ignored.
Sheet.Cells[1, 1].Value := Sheet.Calculate('=DEC2BIN(-5,10)'); // 1111111011
// Complex multiply on two "a+bi" strings.
Sheet.Cells[2, 1].Value := Sheet.Calculate('=IMPRODUCT("3+4i","1+2i")'); // -5+10i
finally
Book.Free;
end;
end;Các hàm phức siêu việt (transcendental complex functions), bao gồm IMSQRT, IMEXP, IMLN và IMPOWER, không hoạt động trong hệ tọa độ Descartes (rectangular coordinates). Chúng chuyển đổi giá trị đã phân tích cú pháp sang dạng cực (polar form), áp dụng phép toán trên mô-đun và đối số, và chuyển đổi ngược lại. Một căn bậc hai chia đôi đối số và lấy căn của mô-đun. Một lũy thừa nhân đối số và nâng mô-đun lên. Làm theo bất kỳ cách nào khác sẽ đồng nghĩa với việc rút ra lại từng đồng nhất thức dưới dạng Descartes, điều này vừa tốn nhiều mã hơn vừa kém ổn định về mặt số học gần các vết cắt nhánh (branch cuts).
Các toán tử thao tác bit và kiểm tra lỗi tràn trước khi thực hiện
Excel 2013 đã thêm vào BITAND, BITOR, BITXOR, BITLSHIFT và BITRSHIFT. Các toán hạng bị ràng buộc: mỗi toán hạng phải là một số nguyên không âm không lớn hơn 2^48 trừ 1, và bất kỳ đối số thập phân hoặc âm nào đều là một lỗi số học. Giới hạn đó đủ rộng để bao phủ bất kỳ tập hợp cờ thực tế nào trong khi vẫn nằm gọn bên trong phạm vi có thể biểu diễn chính xác của một số double, điều này rất quan trọng vì Excel truyền mọi đối số số học qua dưới dạng một giá trị dấu phẩy động.
Các hàm dịch chuyển mang theo một quy tắc về thứ tự thực sự gây rắc rối. Một phép dịch trái có thể tạo ra một giá trị lớn hơn nhiều so với đầu vào của nó, và nếu bạn thực hiện phép toán shl trước rồi kiểm tra kết quả sau thì bạn đã làm tràn Int64 và phép thử trở nên vô nghĩa. Việc kiểm tra phải diễn ra trước khi dịch chuyển. HotXLS so sánh toán hạng với mức trần được dịch phải một lượng bằng lượng dịch chuyển, và chỉ khi toán hạng vừa vặn thì nó mới thực hiện dịch trái thực tế. Một độ lớn dịch chuyển vượt quá 53 bit sẽ bị từ chối ngay lập tức, và một dịch chuyển âm chỉ đơn giản là đảo ngược hướng, do đó BITLSHIFT với số đếm âm hoạt động như một dịch chuyển phải. Nguyên lý này được khái quát hóa vượt xa khỏi một hàm đơn lẻ này: khi một chốt bảo vệ tồn tại để ngăn ngừa tràn, nó phải chạy trên các đầu vào, không bao giờ chạy trên kết quả mà nó có nhiệm vụ bảo vệ.
// Bitwise calls evaluate the same way through Calculate.
Sheet.Cells[3, 1].Value := Sheet.Calculate('=BITAND(13,11)'); // 9
Sheet.Cells[4, 1].Value := Sheet.Calculate('=BITLSHIFT(5,2)'); // 20
Sheet.Cells[5, 1].Value := Sheet.Calculate('=BITRSHIFT(40,3)'); // 5Các hàm tương lai và tiền tố tên _xlfn
Các toán tử thao tác bit và một danh sách dài các bổ sung sau năm 2007 tương tác với một cơ chế đặt tên không liên quan gì đến những gì chúng tính toán và liên quan hoàn toàn đến cách Excel lưu trữ chúng. Định dạng bảng tính nhị phân ban đầu đã gán cho mỗi hàm tích hợp sẵn một vị trí số trong một bảng cố định. Các hàm được phát minh sau khi bảng đó bị đóng băng không có vị trí nào. Để lưu một hàm như vậy vào một tệp và để Excel hiện đại nhận ra nó, tên được viết với tiền tố _xlfn., vì vậy BITAND được lưu trữ dưới dạng _xlfn.BITAND trên đĩa ngay cả khi người dùng chỉ bao giờ nhập BITAND.
Điều rắc rối là quy tắc này không đồng nhất. Một số hàm mới hơn đã được cấp các vị trí bảng và được viết trần, trong khi một vài hàm ẩn kế thừa cũng được viết không có tiền tố bất chấp tuổi đời của chúng. HotXLS giữ một danh sách trắng (whitelist) rõ ràng về việc tên nào cần tiền tố, thêm nó khi ghi và loại bỏ nó khi đọc, do đó văn bản công thức bạn đặt và đọc lại luôn là tên sạch hướng tới Excel. Bạn đặt =BITLSHIFT(5,2), tệp sẽ lưu _xlfn.BITLSHIFT, và giá trị trả về là 20 bất kể điều gì. Tiền tố là một chi tiết lưu trữ không bao giờ nên rò rỉ vào các công cụ công thức bạn làm việc trong mã.
Kết hợp lại với nhau trong một bảng tính
Bề mặt công khai cho tất cả những điều này là nhỏ gọn. Tạo một TXLSXWorkbook, thêm một bảng tính, và hoặc ghi một công thức vào một ô thông qua Cells[Row, Col].Formula và tính toán lại, hoặc đánh giá một biểu thức trực tiếp với phương thức Calculate của bảng tính, lệnh này biên dịch công thức đối chiếu với trang tính đó và trả về một Variant. Các ví dụ trên sử dụng Calculate vì nó hiển thị kết quả của một lệnh gọi kỹ thuật đơn lẻ mà không có trạng thái trang tính xung quanh, nhưng các hàm tương tự sẽ đánh giá giống hệt nhau bên trong các công thức ô thực tế khi bảng tính tính toán lại.
Mã hóa là phần cần lưu ý, không phải các vị trí gọi lệnh. Một chuỗi nhị phân chỉ có dấu ở mười chữ số và chỉ sau ngưỡng một nửa cho cơ số của nó. Một số phức là văn bản, một hệ số ảo trống là một, và trình phân tích cú pháp bước qua chữ e của một số mũ. Dịch trái được kiểm tra trước khi dịch chuyển. Nắm chắc bốn thực tế đó và họ hàm kỹ thuật sẽ không còn là nguồn gốc của những bất ngờ sai dấu nữa.
Nếu bạn đang kết nối toán học miền của riêng mình vào cùng một công cụ, cơ chế đăng ký trình xử lý và trả về giá trị được đề cập trong our article on extending the formula engine with custom functions, và khi những công thức đó phải tiếp cận các trang tính bằng tên thay vì bằng địa chỉ ô, the walkthrough on defined names and cross-sheet formulas shows how the references resolve. The engineering functions described here ship as part of the HotXLS spreadsheet component for Delphi and C++Builder, alongside the reading, writing, and calculation APIs covered elsewhere on this blog.