Power Query là tính năng mới, trẻ trung, hấp dẫn trong Excel. Hãy nhớ rằng nó không được tạo bởi nhóm Excel. Nhóm Power Query quyết định rằng họ sẽ không bị ràng buộc bởi một sai lầm hàng thập kỷ trong VisiCalc. Đây là lý do tại sao điều đó có thể gây ra vấn đề cho bạn.
Đối với nền, ngày tháng trong Excel được lưu trữ dưới dạng số sê-ri. Nếu bạn nhập ngày hôm nay - ngày 30 tháng 3 năm 2018 trong Excel, họ sẽ hiển thị cho bạn một số dạng ngày, nhưng Excel đang lưu trữ 43189. Điều đó có nghĩa là hôm nay là 43189 ngày kể từ ngày 1 tháng 1 năm 1900.
Nó cũng có nghĩa là để đến ngày mai, bạn có thể sử dụng =F3+1. Để tìm số ngày giữa các ngày, bạn có thể trừ ngày này cho ngày khác. Đó là một cách tốt để cho phép tính toán ngày tháng.
Thận trọng
Điều đó cũng có nghĩa là bạn không thể dễ dàng ghi lại ngày tháng từ những năm 1800. Điều này có hại cho các nhà di truyền học hoặc kế toán tại các công ty 150 năm tuổi.
Hệ thống này được Dan Bricklin và Bob Frankston phát minh khi họ tạo ra VisiCalc vào năm 1978-1979. Nhưng Bob và Dan đã mắc một sai lầm. Hãy quay ngược thời gian mô hình của chúng ta, đến tháng 2 và tháng 3 năm 1900. Ngày gắn với số sê-ri 60 là ngày 29 tháng 2 năm 1900.
Những ngày nhuận trở lại với Julius Caesar. Vì Trái đất mất 365,242189 ngày để quay quanh mặt trời, nên có lịch 365 ngày có nghĩa là các mùa sẽ thay đổi 24 ngày mỗi thế kỷ. Julius Caesar đã lập ra một kế hoạch thêm một ngày nhuận vào mỗi năm chia hết cho 4. Điều đó sẽ hoàn hảo nếu Trái đất quay quanh Mặt trời cứ 365,25 ngày một lần. Nhưng sự khác biệt rất nhỏ đó từ 0,25 đến 0,242189 có nghĩa là trong suốt hai thiên niên kỷ, các mùa vẫn tắt. Giáo hoàng Gregory đề xuất một hệ thống vào năm 1582 trong đó có ba quy tắc:
- Quy tắc 1: Một năm chia hết cho 4 sẽ là một năm nhuận, ngoại trừ:
- Quy tắc 2: Một năm chia hết cho 100 sẽ không phải là một năm nhuận, ngoại trừ:
- Quy tắc 3: Một năm chia hết cho 400 sẽ là một năm nhuận.
Các quy tắc được đề xuất vào năm 1582 nhưng chậm được thông qua. Nhật Bản đã không đồng ý cho đến năm 1873. Bulgaria, Estonia, Nga, Hy Lạp và Thổ Nhĩ Kỳ chuyển sang từ năm 1916-1927. Quy tắc số 2 chỉ xảy ra vào năm 1700, 1800 và 1900. Quy tắc số 3 xảy ra vào năm 1600 và 2000. Nếu bạn đang đọc điều này, bạn có thể còn sống vào ngày 29 tháng 2 năm 2000, nhưng bạn có thể không nhận ra rằng đó là một ngoại lệ đối với ngoại lệ cho một ngoại lệ. Nhưng trở lại năm 1978, điều này đã không xảy ra trong 79 năm, vì vậy nó không được biết đến rộng rãi. VisiCalc đã mắc sai lầm khi đưa vào ngày 29 tháng 2 năm 1900.
Nó không phải là một vấn đề lớn, thực sự. Ai đã từng quay lại xem ngày 2 tháng 2 năm 1900 là Thứ Năm hay Thứ Tư (Excel nói rằng đó là Thứ Năm, nhưng nó thực sự là Thứ Tư). Và ai sẽ theo dõi các khoản phải thu mở từ đầu năm 1900? Hãy đối mặt với nó, nếu bạn lập hóa đơn cho một nhà cung cấp vào ngày 15 tháng 2 năm 1900 và họ vẫn chưa thanh toán cho bạn, đã đến lúc xóa khoản phải thu.
Để tương thích, Mitch Kapor đã lập trình cùng một lỗi trong Lotus 1-2-3.
Steve Jobs, không muốn lập trình cố ý gây ra lỗi đã khiến đồng hồ Macintosh bắt đầu từ ngày 1 tháng 1 năm 1904.
Tại Microsoft, Excel phải tương thích với Lotus 1-2-3 dẫn đầu thị trường khi đó, và phiên bản không tồn tại ngày 29 tháng 2 năm 1900 đã được đưa vào Excel và vẫn tồn tại cho đến ngày nay.
Nhưng các kiến trúc sư Power Query không phải là những người làm bảng tính. Họ không có tượng của Bricklin và Frankston trong văn phòng của họ. Họ không biết lịch sử này. Họ đưa ra quyết định rằng ngày của họ sẽ là số ngày trôi qua kể từ ngày 31 tháng 12 năm 1899. Điều này khiến những người sử dụng Power Query cảm thấy cao hơn một chút so với những người sử dụng Excel vì các ngày trong tuần được Power Query báo cáo trong 60 ngày vào đầu năm 1900 chính xác hơn trong Excel.
Đây không phải là bất cứ điều gì đáng lo ngại. Không ai xử lý các ngày từ 60 ngày đó.
Nhưng đây là một cái gì đó phổ biến hơn nhiều. Hãy xem xét bảng các con số nổi tiếng này.
Tôi sẽ yêu cầu bạn "làm điều sai" và vô tình làm theo các bước sau:
- Chọn cột N
- Nhấn Ctrl + Shift + 3 để định dạng cột dưới dạng ngày tháng
-
Đừng nhận thấy rằng bạn đã làm một trong những điều này.
Rất tiếc - những con số vô tình là ngày tháng - Sử dụng Dữ liệu, Nhận & Chuyển đổi, Từ Bảng hoặc Phạm vi.
- Khi bạn truy cập Power Query, hãy để ý ngày tháng trong cột số. Trong hộp Các bước được Áp dụng, hãy xóa bước Định dạng đã Thay đổi.
Khi dữ liệu quay trở lại Excel, mọi thứ sẽ tắt bằng 1. Vòng kết nối không còn tồn tại. Heinz có 56 giống thay vì 57.
Tôi nhận ra đây là Thứ Sáu cuối cùng của chúng ta trong Mùa Chay Excel. Tôi nhận ra đây là một ví dụ ngụy tạo và khó hiểu. Tỷ lệ cược mà ai đó vô tình định dạng một cột số trong phạm vi 1-60 làm ngày trước khi chuyển đến Power Query là bao nhiêu? Nó có vẻ thấp, nhưng nó đã xảy ra.
Power Query là một tính năng tuyệt vời. Tôi chắc chắn rằng các kiến trúc sư đã nhận ra rằng không có gì có thể xảy ra sai sót nếu họ thông minh hơn những người đã mắc sai lầm vào năm 1978. Nhưng liệu hàng tỷ bảng tính có hoạt động không vì tất cả chúng ta đều đồng ý chấp nhận sai lầm, bạn đang xé một lỗ nhỏ trong cấu trúc của Excel.
Thứ sáu hàng tuần, tôi kiểm tra một lỗi hoặc các hành vi khó hiểu khác trong Excel.
Suy nghĩ của Excel trong ngày
Tôi đã hỏi những người bạn Excel Master của mình để được tư vấn về Excel. Hôm nay cần suy ngẫm:
"Ctrl + Shift + U chuyển đổi chiều cao của thanh công thức"
Bob Umlas
