Keluarga rekayasa (engineering) di Excel terbaca seperti sudut termudah dari referensi fungsi. DEC2BIN mengubah angka menjadi string biner. HEX2DEC mengubahnya kembali. IMSUM menambahkan dua bilangan kompleks. Masing-masing terlihat seperti latihan pemformatan biasa. Padahal tidak. Di balik nama-nama ini terdapat pengodean komplemen dua sepuluh-bit (ten-bit two's complement) yang mungkin belum pernah disentuh lagi oleh sebagian besar pengembang sejak kelas arsitektur komputer, format bilangan kompleks yang hidup sepenuhnya di dalam string, dan operator bitwise yang akan meluap (overflow) secara diam-diam pada integer 64-bit jika Anda bergeser sebelum memeriksa. Mesin spreadsheet yang mereproduksi Excel secara persis tidak dapat membulatkan semua itu.
Fungsi-fungsi tersebut dibagi menjadi tiga kelompok, dan masing-masing kelompok menyembunyikan jebakan yang berbeda. Konversi basis adalah tentang angka negatif dan ambang batas per basis. Aritmatika kompleks adalah tentang penguraian dan pemformatan string. Operasi bitwise adalah tentang tetap berada di dalam batas-batas Int64. Artikel ini membahas setiap kelompok seperti yang diimplementasikan oleh HotXLS, dengan panggilan lembar kerja (worksheet) yang sebenarnya Anda tulis.
Konversi basis dan komplemen dua sepuluh-bit
Arah maju adalah bagian yang diharapkan semua orang. DEC2BIN(9) menghasilkan "1001", dan argumen kedua opsional memberikan pad-kiri (left-pads) pada hasil ke lebar tetap. Jebakannya adalah masukan negatif. Excel tidak menulis tanda minus. Ia mengodekan nilai tersebut sebagai string komplemen dua sepuluh digit dalam basis target, itulah sebabnya DEC2BIN(-5,10) mengembalikan "1111111011" alih-alih apa pun yang memiliki tanda. Argumen tempat (places) diabaikan begitu nilainya negatif, karena pengodean tersebut sudah dipatok pada sepuluh digit.
Sepuluh digit adalah anggaran tetap, dan anggaran tersebut menetapkan rentang yang dapat diwakili per basis. Dalam biner, besaran yang membalik ke bagian negatif adalah 512, dan modulus pembungkusnya adalah 1024, sehingga string biner bertanda hanya ketika panjangnya tepat sepuluh karakter dan nilainya setidaknya 512. Gagasan yang sama berskala dengan basisnya. Oktal menggunakan ambang batas setengah dari 2^29 dan modulus penuh dari 2^30. Heksadesimal menggunakan 2^39 dan 2^40. Pembaca HotXLS menerapkan aturan ini secara tepat: ia mengumpulkan digit, dan hanya ketika string selebar sepuluh karakter dan nilai yang terkumpul berada pada atau di atas ambang batas setengah, ia mengurangi modulus penuh untuk memulihkan nilai bertanda. String sembilan karakter selalu non-negatif, tidak peduli seberapa besar nilainya.
Pengode (encoder) adalah kebalikannya. Nilai non-negatif dikonversi digit demi digit dan secara opsional diberi pad-nol (zero-padded) ke lebar yang diminta, dan ditolak jika melebihi batas atas positif basis atau jika lebar yang diminta terlalu sempit untuk menampungnya. Nilai negatif pertama-tama dibawa ke dalam rentang dengan menambahkan modulus penuh, yang mengubahnya menjadi nilai yang representasi basisnya selalu sepuluh digit, dan kemudian digit tersebut dikeluarkan dengan nol di depan untuk mengisi lebar. Pemeriksaan rentang bersama yang tunggal, batas bawah dan atas simetris per basis, adalah apa yang menjaga DEC2BIN, DEC2OCT, dan DEC2HEX konsisten satu sama lain pada ujung-ujungnya.
Hal tersebut menyisakan konversi lintas basis, seperti HEX2BIN dan OCT2HEX yang mengubah basis tanpa melewati desimal dalam nama fungsi. Implementasinya tidak membawa rutinitas terpisah untuk setiap pasangan terurut. Ia mengurai string masukan menjadi nilai desimal bertanda menggunakan basis sumber, lalu memformat nilai desimal tersebut ke basis tujuan. Desimal adalah porosnya. Satu rutinitas penguraian (parse) dan satu rutinitas format, yang digabungkan, mencakup setiap kombinasi, dan karena kedua bagian berbagi konvensi bertanda sepuluh digit yang sama, nilai negatif bertahan dalam perjalanan dengan tandanya yang utuh.
Bilangan kompleks adalah string, jadi pekerjaannya adalah mengurai
Excel tidak memiliki tipe data kompleks. Nilai kompleks adalah string "a+bi", dan setiap fungsi dalam keluarga IM menerima string tersebut dan mengembalikan satu kembali. COMPLEX membangun string dari bagian riil dan imajiner. IMSUM, IMSUB, IMPRODUCT, dan IMDIV mengurai argumen mereka, melakukan aritmatika pada bagian numerik, dan memformat hasilnya kembali menjadi string. Pekerjaan numeriknya adalah aljabar sarjana. Kesulitannya sepenuhnya ada pada mengubah teks menjadi dua bilangan titik mengambang (floating-point) secara andal, dan di situlah parser internal membuktikan kegunaannya.
Dua detail dalam parser tersebut mudah salah diimplementasikan. Pertama adalah unit imajiner kosong. String "i" berarti satu kali i, bukan nol dan bukan kesalahan, jadi ketika koefisien di depan sufiks kosong atau tanda plus tunggal, parser harus membacanya sebagai nilai 1, dan tanda minus tunggal sebagai -1. Lewati itu dan IMSUM("i","i") tidak lagi menjadi 2i. Kedua adalah notasi ilmiah yang bertabrakan dengan tanda yang memisahkan bagian riil dan imajiner. Parser menemukan pemisah tersebut dengan memindai tanda plus atau minus, tetapi angka yang ditulis sebagai "1.5E-3" berisi tanda minus yang termasuk dalam eksponen. Pemindaian oleh karena itu menolak untuk memperlakukan tanda plus atau minus sebagai pemisah ketika karakter tepat sebelum karakter tersebut adalah e atau E. Tanpa pelindung tersebut, bagian riil akan robek menjadi dua bagian pada tanda eksponen dan penguraian akan gagal pada masukan yang sepenuhnya valid.
Sufiks itu sendiri dipertahankan alih-alih dinormalisasi. Excel menerima baik i maupun j, dan HotXLS mengingat yang mana yang digunakan masukan sehingga hasil yang diformat membawa huruf yang sama. Pemformatan kemudian menerapkan singkatan konvensional: bagian imajiner satu dicetak hanya sebagai sufiks saja, minus satu sebagai -i, bagian imajiner nol runtuh menjadi riil biasa, dan bagian riil nol menghilangkan 0+ di depan.
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;Fungsi kompleks transendental, di antaranya IMSQRT, IMEXP, IMLN, dan IMPOWER, tidak bekerja dalam koordinat persegi panjang (rectangular coordinates). Mereka mengonversi nilai yang diurai ke bentuk kutub (polar form), menerapkan operasi pada modulus dan argumen, dan mengonversi kembali. Akar kuadrat membagi dua argumen dan mengambil akar dari modulus. Pangkat mengalikan argumen dan menaikkan modulus. Melakukannya dengan cara lain berarti menurunkan kembali setiap identitas dalam bentuk persegi panjang, yang merupakan kode lebih banyak dan kurang stabil secara numerik di dekat pemotongan cabang (branch cuts).
Operator bitwise dan luapan (overflow) yang harus Anda periksa terlebih dahulu
Excel 2013 menambahkan BITAND, BITOR, BITXOR, BITLSHIFT, dan BITRSHIFT. Operand-operan tersebut dibatasi: masing-masing harus berupa integer non-negatif yang tidak lebih besar dari 2^48 minus 1, dan argumen pecahan atau negatif apa pun adalah kesalahan numerik. Batas tersebut cukup besar untuk mencakup rangkaian bendera realistis apa pun sambil tetap berada di dalam rentang double yang dapat diwakili secara tepat, yang penting karena Excel menyerahkan setiap argumen numerik sebagai nilai titik mengambang (floating-point).
Fungsi pergeseran (shift) membawa satu aturan pengurutan yang benar-benar berbahaya. Pergeseran kiri dapat menghasilkan nilai yang jauh lebih besar dari masukannya, dan jika Anda melakukan shl terlebih dahulu dan memeriksa hasilnya setelah itu, Anda telah meluap dari Int64 dan pengujian tersebut tidak berarti. Pemeriksaan harus dilakukan sebelum pergeseran. HotXLS membandingkan operand terhadap batas atas yang digeser ke kanan sebesar jumlah pergeseran, dan hanya jika operand tersebut pas, ia melakukan pergeseran kiri yang sebenarnya. Besaran pergeseran melebihi 53 bit ditolak secara langsung, dan pergeseran negatif hanya membalikkan arah, sehingga BITLSHIFT dengan jumlah negatif berperilaku sebagai pergeseran kanan. Prinsip ini digeneralisasi jauh melampaui satu fungsi ini: ketika pelindung ada untuk mencegah luapan, ia harus berjalan pada masukan, tidak pernah pada hasil yang dimaksudkan untuk dilindungi.
// 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)'); // 5Fungsi masa depan dan awalan nama _xlfn
Operator bitwise dan daftar panjang penambahan pasca-2007 lainnya berinteraksi dengan skema penamaan yang tidak ada hubungannya dengan apa yang mereka hitung dan memiliki segalanya untuk dilakukan dengan bagaimana Excel menyimpannya. Format lembar kerja biner asli menetapkan setiap fungsi bawaan ke slot numerik dalam tabel tetap. Fungsi yang ditemukan setelah tabel tersebut dibekukan tidak memiliki slot. Untuk menyimpan fungsi tersebut ke dalam berkas dan agar Excel modern mengenalinya, namanya ditulis dengan awalan _xlfn., sehingga BITAND disimpan sebagai _xlfn.BITAND di disk meskipun pengguna hanya mengetik BITAND.
Tangkapan (catch) dari aturan ini adalah bahwa ia tidak seragam. Beberapa fungsi yang lebih baru diberi slot tabel dan ditulis polos, sementara beberapa fungsi tersembunyi warisan juga ditulis tanpa awalan meskipun usianya sudah tua. HotXLS menyimpan daftar putih eksplisit tentang nama mana saja yang memerlukan awalan, menambahkannya saat menulis dan menghapusnya saat membaca, sehingga teks rumus yang Anda setel dan baca kembali selalu berupa nama bersih yang menghadap ke Excel. Anda menetapkan =BITLSHIFT(5,2), berkas menyimpan _xlfn.BITLSHIFT, dan nilainya kembali sebagai 20 terlepas dari itu. Awalan adalah detail penyimpanan yang tidak boleh bocor ke dalam rumus yang Anda kerjakan di kode.
Menyatukannya di lembar kerja
Permukaan publik untuk semua ini kecil. Buat TXLSXWorkbook, tambahkan lembar kerja, dan tulis rumus ke dalam sel melalui Cells[Row, Col].Formula lalu hitung ulang, atau evaluasi ekspresi secara langsung dengan metode Calculate lembar kerja, yang mengompilasi rumus terhadap lembar kerja tersebut dan mengembalikan Variant. Contoh-contoh di atas menggunakan Calculate karena menunjukkan hasil dari panggilan rekayasa tunggal tanpa keadaan lembar kerja di sekitarnya, tetapi fungsi yang sama dievaluasi secara identik di dalam rumus sel nyata ketika workbook dihitung ulang.
Pengodean adalah bagian yang harus diingat, bukan situs panggilannya. String biner bertanda hanya pada sepuluh digit dan hanya setelah melewati ambang batas setengah untuk basisnya. Bilangan kompleks adalah teks, koefisien imajiner kosong bernilai satu, dan parser melangkahi karakter e dari eksponen. Pergeseran kiri diperiksa sebelum ia digeser. Pahami empat fakta tersebut dengan benar dan keluarga rekayasa (engineering) berhenti menjadi sumber kejutan tanda-salah (off-by-a-sign).
Jika Anda menghubungkan matematika domain Anda sendiri ke dalam mesin yang sama, mekanisme mendaftarkan handler dan mengembalikan nilai dibahas dalam artikel kami tentang memperluas mesin rumus dengan fungsi kustom, dan ketika rumus tersebut harus menjangkau antar lembar kerja berdasarkan nama alih-alih berdasarkan alamat sel, panduan tentang nama yang ditentukan dan rumus lintas lembar kerja menunjukkan bagaimana referensi tersebut diselesaikan. Fungsi rekayasa yang dijelaskan di sini dikirim sebagai bagian dari komponen spreadsheet HotXLS untuk Delphi dan C++Builder, bersama dengan API pembacaan, penulisan, dan perhitungan yang dibahas di bagian lain di blog ini.