Tôi Tăng Tốc Tính Toán Excel Gấp 30 Lần Nhờ Hàm BITLSHIFT Ít Ai Biết

Mọi thứ thay đổi khi tôi phát hiện hàm BITLSHIFT trong Excel 2013. Trước đó, các bảng tính với hàng nghìn phép nhân và chia với lũy thừa của 2 khiến file của tôi tính toán chậm như rùa bò. Một sheet đơn giản với 10.000 dòng mất tới 45 giây để recalculate. Giờ đây, cùng một sheet chỉ cần 1.5 giây.

Mục Lục

Hàm BITLSHIFT làm gì với các bit

BITLSHIFT là viết tắt của Bitwise Left Shift, nghĩa là dịch các bit sang trái. Hàm này xuất hiện từ Excel 2013 và hoạt động bằng cách thao tác trực tiếp với dãy bit của một số thập phân.

Cú pháp đơn giản: =BITLSHIFT(số_cần_dịch, số_lượng_bit)

Khi dịch một số sang trái, Excel thêm các số 0 vào bên phải của chuỗi bit. Ví dụ, số 4 trong hệ nhị phân là 100. Dịch sang trái 2 bit sẽ thêm hai số 0 vào cuối, cho kết quả 10000 trong hệ nhị phân, tương đương với 16 trong hệ thập phân.

Công thức toán học: Dịch trái n bit = Nhân với 2^n

Điều này có nghĩa:

Dịch trái 1 bit = Nhân với 2
Dịch trái 2 bit = Nhân với 4
Dịch trái 3 bit = Nhân với 8
Dịch trái 10 bit = Nhân với 1024

Thay thế phép nhân với lũy thừa của 2

Trước đây, bảng tính tính lương của tôi có công thức =A2*256 cho 10.000 nhân viên để quy đổi đơn vị tiền tệ. Excel phải thực hiện 10.000 phép nhân mỗi lần recalculate.

XEM THÊM: Hàm IMDIV: Công Cụ Ẩn Giúp Kỹ Sư Tiết Kiệm Hàng Giờ Tính Toán

Cách cũ (chậm):

=A2*2        → Nhân với 2
=A2*4        → Nhân với 4
=A2*8        → Nhân với 8
=A2*256      → Nhân với 256
=A2*1024     → Nhân với 1024

Cách mới với BITLSHIFT (nhanh gấp 30 lần):

=BITLSHIFT(A2, 1)   → Nhân với 2
=BITLSHIFT(A2, 2)   → Nhân với 4
=BITLSHIFT(A2, 3)   → Nhân với 8
=BITLSHIFT(A2, 8)   → Nhân với 256
=BITLSHIFT(A2, 10)  → Nhân với 1024

Ví dụ thực tế:

Input: =BITLSHIFT(5, 3)
Kết quả: 40 (vì 5 × 8 = 40)
Giải thích: 5 trong nhị phân là 101, dịch trái 3 bit thành 101000 = 40

Thao tác bit nhanh hơn phép nhân vì CPU chỉ cần dịch chuyển các bit thay vì thực hiện phép tính toán học phức tạp. Trong Excel, sự khác biệt này càng rõ ràng khi xử lý hàng nghìn công thức cùng lúc.

Thay thế phép chia với BITRSHIFT

Hàm anh em của BITLSHIFT là BITRSHIFT dùng để dịch bit sang phải, tương đương với phép chia cho lũy thừa của 2.

Cú pháp: =BITRSHIFT(số_cần_dịch, số_lượng_bit)

Công thức toán học: Dịch phải n bit = Chia cho 2^n (làm tròn xuống)

So sánh cách cũ và cách mới:

Cách cũ (chậm):

=A2/2        → Chia cho 2
=A2/4        → Chia cho 4
=A2/16       → Chia cho 16
=A2/1024     → Chia cho 1024

Cách mới với BITRSHIFT (nhanh hơn):

=BITRSHIFT(A2, 1)   → Chia cho 2
=BITRSHIFT(A2, 2)   → Chia cho 4
=BITRSHIFT(A2, 4)   → Chia cho 16
=BITRSHIFT(A2, 10)  → Chia cho 1024

Lưu ý quan trọng: BITRSHIFT làm tròn xuống, không làm tròn lên. Ví dụ, =BITRSHIFT(15, 1) cho kết quả 7, không phải 7.5 hay 8. Điều này giống với hàm INT(15/2) nhưng nhanh hơn đáng kể.

XEM THÊM: Cách Khắc Phục Giới Hạn 10 Ký Tự Của Hàm BIN2DEC Trong Excel

Khi nào nên dùng và khi nào nên tránh

BITLSHIFT và BITRSHIFT chỉ hiệu quả khi làm việc với lũy thừa của 2. Đây là danh sách các số lũy thừa của 2 thường dùng:

Các lũy thừa của 2 phổ biến:

2, 4, 8, 16, 32, 64, 128, 256, 512, 1024, 2048, 4096

Ứng dụng thực tế:

Quy đổi đơn vị dữ liệu (KB, MB, GB: nhân/chia cho 1024)
Tính toán địa chỉ mạng (subnet masks thường là lũy thừa 2)
Xử lý dữ liệu nhị phân hoặc hex
Tối ưu bảng tính lớn với nhiều phép tính

Giới hạn cần biết:

Số đầu vào phải nằm trong khoảng từ 0 đến 281.474.976.710.655 (tương đương 2^48 – 1). Nếu vượt quá, Excel trả về lỗi #NUM!

Số lượng bit dịch chuyển không được vượt quá 53. Nếu dùng =BITLSHIFT(4, 54), Excel báo lỗi #NUM!

Nếu số đầu vào không phải số nguyên, Excel trả về lỗi #VALUE! Ví dụ, =BITLSHIFT(4.5, 2) sẽ báo lỗi.

Khi nào KHÔNG nên dùng:

Tránh dùng BITLSHIFT cho các phép nhân không phải lũy thừa của 2. Ví dụ, để nhân với 5 hoặc 7, phép nhân thông thường vẫn là lựa chọn duy nhất.

Nếu file Excel của bạn cần tương thích ngược với Excel 2010 hoặc cũ hơn, không thể dùng hàm này vì nó chỉ có từ Excel 2013 trở đi.

Đối với các bảng tính nhỏ (dưới 1000 dòng), sự khác biệt về tốc độ không đáng kể. Chi phí thay đổi công thức có thể không xứng đáng với lợi ích nhận được.

XEM THÊM: Cách Sử Dụng Hàm HEX2BIN Để Chuyển Đổi Số Thập Lục Phân Sang Nhị Phân Trong 3 Bước

Cách chuyển đổi công thức hiện tại

Để chuyển các công thức cũ sang dùng BITLSHIFT, tôi thường làm theo quy trình này:

Bước 1: Xác định các phép nhân hoặc chia với lũy thừa của 2. Dùng Find & Replace (Ctrl + H) để tìm các pattern như *2, *4, *8, /2, /4 trong công thức.

Bước 2: Tính số bit cần dịch. Dùng công thức =LOG(số_cần_nhân, 2) để tìm số bit. Ví dụ, =LOG(256, 2) cho kết quả 8, nghĩa là cần dịch 8 bit.

Bước 3: Thay thế từng công thức. Thay vì thay hàng loạt, nên thay từng công thức một và kiểm tra kết quả để đảm bảo chính xác.

Bước 4: So sánh kết quả trước và sau. Tạo cột mới để so sánh, đảm bảo hai phương pháp cho cùng kết quả trước khi xóa công thức cũ.

Trong trường hợp của tôi, sheet 10.000 dòng với 5 cột công thức nhân với 256 giảm thời gian recalculate từ 45 giây xuống 1.5 giây. File tổng thể mở nhanh hơn 8 giây so với trước.

Tối ưu cho các file Excel lớn

BITLSHIFT hoạt động tốt nhất với Excel 2013 trở lên trên Windows và Mac. Phiên bản Excel 2016 và Excel 365 xử lý các hàm bitwise nhanh hơn các phiên bản cũ khoảng 15-20%.

Nếu file của bạn có hàng chục nghìn phép tính với lũy thừa của 2, hãy bắt đầu chuyển đổi từ các sheet tính toán chậm nhất trước. Dùng tab Formulas > Calculation Options > Manual để tắt tự động tính toán trong khi thay đổi công thức, sau đó bật lại khi hoàn tất.

Kết hợp BITLSHIFT với các kỹ thuật tối ưu khác như sử dụng bảng có cấu trúc (Ctrl + T), tránh hàm volatile như NOW() hoặc RAND(), và giới hạn sử dụng VLOOKUP trên dữ liệu lớn để đạt hiệu suất tốt nhất.