Gioi han 32.767 ky tu trong o Excel - Nhung rang buoc an sau bang tinh
Ban nhap mot van ban dai vao o Excel va bat ngo thay phan cuoi bi cat mat. Khong co thong bao loi, khong co canh bao - du lieu don gian la bien mat. Gioi han 32.767 ky tu cua mot o Excel la mot trong nhung rang buoc ma nhieu nguoi dung bang tinh khong biet den cho den khi gap su co. Bai viet nay phan tich chi tiet cac gioi han ky tu trong Excel, Google Sheets va cac cong cu bang tinh khac, dong thoi chi ra nhung tinh huong thuc te ma cac gioi han nay gay ra van de.
Gioi han ky tu trong o Excel - 32.767 den tu dau
Con so 32.767 khong phai ngau nhien. Day chinh la gia tri lon nhat cua kieu du lieu so nguyen 16-bit co dau (2^15 - 1). Khi Microsoft thiet ke dinh dang luu tru cua Excel, ho su dung 2 byte de luu do dai chuoi ky tu trong moi o, tao ra gioi han toi da 32.767 ky tu.
| Thanh phan | Gioi han ky tu | Ghi chu |
|---|---|---|
| Noi dung o (du lieu) | 32.767 ky tu | Bao gom ca ky tu khong hien thi |
| Hien thi trong o | 1.024 ky tu | Phan con lai van ton tai nhung khong hien thi |
| Cong thuc | 8.192 ky tu | Tinh ca tham chieu o va ham |
| Header/Footer | 255 ky tu | Moi phan (trai, giua, phai) |
| Ten sheet | 31 ky tu | Khong cho phep ky tu dac biet nhu [ ] : * ? / \ |
| Ten file | 218 ky tu | Bao gom duong dan day du |
Dieu dang chu y la su chenh lech giua gioi han luu tru (32.767) va gioi han hien thi (1.024). Mot o co the chua 32.767 ky tu du lieu, nhung chi 1.024 ky tu dau tien duoc hien thi truc tiep trong o. De xem toan bo noi dung, nguoi dung phai nhan vao o va doc trong thanh cong thuc. Day la nguyen nhan pho bien cua loi "du lieu bi mat" khi nguoi dung copy-paste van ban dai vao Excel.
Gioi han cong thuc 8.192 ky tu - Khi logic nghiep vu vuot qua suc chua
Gioi han 8.192 ky tu cho cong thuc nghe co ve rong rai, nhung trong thuc te, cac cong thuc phuc tap co the dat den gioi han nay nhanh hon du kien.
Mot cong thuc IF long nhau 7 cap voi cac dieu kien dai co the chiem 500-800 ky tu. Them cac ham VLOOKUP hoac INDEX-MATCH voi tham chieu den nhieu sheet, va cong thuc de dang vuot qua 2.000 ky tu. Trong cac file tai chinh doanh nghiep, cong thuc tinh gia phuc tap voi hang chuc dieu kien khong phai la hiem.
| Loai cong thuc | Do dai trung binh | Tinh huong vuot gioi han |
|---|---|---|
| IF don gian | 30-80 ky tu | Hiem khi gap van de |
| IF long nhau 5-7 cap | 300-800 ky tu | Bat dau can than |
| VLOOKUP + IF phuc tap | 500-2.000 ky tu | Can toi uu hoa |
| Cong thuc mang CSE | 200-1.500 ky tu | Phu thuoc do phuc tap |
| Cong thuc gia phuc tap | 2.000-6.000 ky tu | Nguy co cao |
Giai phap khi cong thuc qua dai la tach thanh nhieu o trung gian. Thay vi mot cong thuc khong lo, chia logic thanh cac buoc nho va dat moi buoc vao mot o rieng. Cach nay khong chi giai quyet gioi han ky tu ma con giup debug de dang hon - ban co the kiem tra ket qua trung gian cua tung buoc.
Google Sheets vs Excel - Khac biet ve gioi han ky tu
Google Sheets va Excel co nhieu diem tuong dong nhung gioi han ky tu khac nhau dang ke.
| Tieu chi | Excel (desktop) | Google Sheets | LibreOffice Calc |
|---|---|---|---|
| Ky tu toi da moi o | 32.767 | 50.000 | 32.767 |
| Hien thi trong o | 1.024 | 50.000 | 1.024 |
| Do dai cong thuc | 8.192 ky tu | 50.000 ky tu | 8.192 ky tu |
| So o toi da | ~17 ty (2^34) | 10 trieu | ~17 ty |
| Ten sheet | 31 ky tu | Khong gioi han ro rang | 31 ky tu |
Google Sheets cho phep 50.000 ky tu moi o - gap 1,5 lan Excel. Tuy nhien, Google Sheets gioi han tong so o trong mot file la 10 trieu, trong khi Excel cho phep khoang 17 ty o (1.048.576 hang x 16.384 cot). Moi cong cu co the manh rieng: Google Sheets uu viet ve noi dung moi o, Excel uu viet ve quy mo du lieu tong the.
Mot diem khac biet quan trong la Google Sheets hien thi toan bo noi dung o (len den 50.000 ky tu) truc tiep, trong khi Excel chi hien thi 1.024 ky tu dau. Dieu nay co nghia la khi chuyen du lieu tu Google Sheets sang Excel, van ban dai hon 32.767 ky tu se bi cat, va van ban tu 1.025 den 32.767 ky tu se khong hien thi day du trong o.
Van de cat du lieu khi nhap CSV - Cai bay cua 32.767
CSV (Comma-Separated Values) ban than khong co gioi han ky tu. Nhung khi mo file CSV bang Excel, gioi han 32.767 ky tu cua Excel duoc ap dung. Day la nguyen nhan pho bien cua mat du lieu khi nhap CSV.
Tinh huong dien hinh: mot he thong xuat bao cao duoi dang CSV voi cot "ghi chu" chua van ban dai. Khi nhan vien mo file CSV bang Excel de kiem tra, nhung ban ghi co ghi chu vuot 32.767 ky tu bi cat ma khong co canh bao. Neu nhan vien sau do luu file, du lieu bi cat vinh vien.
Cac bien phap phong tranh:
- Su dung Power Query de nhap CSV thay vi mo truc tiep - Power Query canh bao khi du lieu bi cat
- Kiem tra do dai ky tu cua cac truong van ban truoc khi xuat CSV tu co so du lieu
- Dat gioi han ky tu o tang ung dung (form nhap lieu) de dam bao du lieu khong vuot gioi han Excel
- Su dung dinh dang khac nhu JSON hoac Parquet cho du lieu co truong van ban dai
Van de nay dac biet nghiem trong trong cac he thong quan ly du lieu khach hang (CRM) va he thong ho tro ky thuat, noi truong "mo ta" hoac "lich su giao tiep" co the chua hang nghin ky tu. Nhu da phan tich trong Do dai VARCHAR trong co so du lieu, viec thiet ke gioi han ky tu can tinh den toan bo chuoi xu ly du lieu, tu nhap lieu den xuat bao cao.
Gioi han an cua Excel Online va Excel Mobile
Phien ban web va mobile cua Excel co them nhung gioi han rieng ma phien ban desktop khong co.
| Phien ban | Gioi han dac thu | Anh huong thuc te |
|---|---|---|
| Excel Online | Hien thi cham voi o > 8.000 ky tu | Trai nghiem nguoi dung giam |
| Excel Mobile (iOS) | Chinh sua kho khan voi o > 5.000 ky tu | Con tro nhay khong chinh xac |
| Excel Mobile (Android) | Tuong tu iOS | Them van de hien thi font |
| Excel trong Teams | Gioi han render khi nhieu nguoi chinh sua | Xung dot khi co-editing o dai |
Trong thoi dai lam viec tu xa, nhieu nguoi truy cap cung mot file Excel tu nhieu thiet bi khac nhau. Mot o chua 20.000 ky tu co the hoat dong tot tren desktop nhung gay ra van de tren mobile hoac web. Thiet ke bang tinh can tinh den moi truong truy cap da dang nhat cua nguoi dung.
Chien luoc thiet ke bang tinh voi y thuc ve gioi han ky tu
Hieu ro cac gioi han ky tu giup thiet ke bang tinh ben vung hon. Mot so nguyen tac thuc te:
Doi voi du lieu van ban dai, tach noi dung thanh nhieu o thay vi nhoi tat ca vao mot o. Vi du, thay vi mot o "dia chi day du" chua 200 ky tu, tach thanh cac o rieng cho thanh pho, quan, duong, so nha. Cach nay khong chi tranh gioi han ky tu ma con giup loc va sap xep du lieu de dang hon.
Doi voi cong thuc phuc tap, su dung Named Ranges va cac o trung gian. Mot cong thuc 3.000 ky tu co the duoc tach thanh 5 cong thuc 600 ky tu, moi cong thuc thuc hien mot buoc logic ro rang. Ket qua cuoi cung giong nhau nhung de bao tri hon nhieu.
Khi bang tinh bat dau dat den gioi han, do la tin hieu de chuyen sang cong cu phu hop hon. Du lieu vuot 10 trieu o nen chuyen sang co so du lieu. Logic nghiep vu vuot 8.192 ky tu cong thuc nen chuyen sang VBA hoac Python. Van ban dai hon 32.767 ky tu nen luu trong he thong quan ly tai lieu chuyen dung. Nhu da thao luan trong Ky thuat rut gon van ban, viec nhan biet khi nao can chuyen doi cong cu la ky nang quan trong trong quan ly du lieu.
Tai lieu tham khao ve ham Excel va thiet ke bang tinh co the tim tren Amazon.