Power Query - це гарна, молода, нова функція в Excel. Пам’ятайте, що його не створила команда Excel. Команда Power Query вирішила, що в VisiCalc їх не зв’яже десятилітна давня помилка. Ось чому це може викликати у вас проблеми.
Для фону дати в Excel зберігаються як серійний номер. Якщо ви введете сьогоднішню дату - 30 березня 2018 року в Excel, вони покажуть вам певну форму дати, але Excel зберігає 43189. Це означає, що сьогодні 1 43189 днів з 1 січня 1900 року.
Це також означає, що, щоб дістатися до завтрашнього дня, ви могли б скористатися =F3+1. Щоб знайти кількість днів між датами, ви можете відняти одну дату від іншої. Це хороший спосіб дозволити обчислення дати.
Обережно
Це також означає, що ви не можете легко записати дати 1800-х років. Це погано для генеалогів або бухгалтерів у 150-річних компаніях.
Система була винайдена Даном Брикліном та Бобом Франкстоном, коли вони створили VisiCalc у 1978-1979 роках. Але Боб і Ден допустили одну помилку. Давайте повернемо нашу модель у минуле, у лютий та березень 1900 року. Дата, пов’язана із серійним номером 60, - 29 лютого 1900 року.
Високосні дні повертаються до Юлія Цезаря. Оскільки Землі потрібно 365,242189 днів, щоб обійти Сонце, наявність календаря на 365 днів означає, що пори року змінюватимуться на 24 дні кожного століття. Юлій Цезар створив план додавання високосного дня в кожному році, що ділиться на 4. Це було б ідеально, якби Земля обходила Сонце кожні 365,25 днів. Але ця крихітна різниця від .25 до .242189 означала, що протягом двох тисячоліть сезони все ще не були. Папа Григорій запропонував систему в 1582 р., Де було три правила:
- Правило 1: Рік, що ділиться на 4, буде високосним, за винятком:
- Правило 2: Рік, що ділиться на 100, не буде високосним, за винятком:
- Правило 3: Рік, що ділиться на 400, буде високосним.
Правила були запропоновані в 1582 р., Але були прийняті повільно. Японія не погодилася до 1873 р. Болгарія, Естонія, Росія, Греція та Туреччина перейшли з 1916 по 1927 р. Правило № 2 відбулося лише в 1700, 1800 і 1900 рр. Правило № 3 відбулося в 1600 та 2000 рр. Якщо ви читаєте це, ви, мабуть, були живі 29 лютого 2000 року, але ви, можливо, не усвідомлювали, що це було винятком із виняток з винятків. Але ще в 1978 році цього не було протягом 79 років, тому це не було широко відомо. VisiCalc допустив помилку, включивши 29 лютого 1900 року.
Насправді це не велика справа. Хто коли-небудь повертається назад, щоб перевірити, чи був 2 лютого 1900 р. Четвергом або середою (Excel каже, що це був четвер, але насправді це була середа). І хто буде відстежувати відкриту дебіторську заборгованість з початку 1900 року? Погодьмося, якщо ви виставили рахунок продавцю 15 лютого 1900 року, і він вам ще не заплатив, пора списати дебіторську заборгованість.
Для сумісності Мітч Капор запрограмував ту саму помилку на Lotus 1-2-3.
Стів Джобс, не бажаючи спеціально програмувати помилку, змусив годинник Macintosh запуститися 1 січня 1904 року.
У Microsoft Excel Excel повинен був бути сумісним із тодішнім лідером ринку Lotus 1-2-3, і неіснуючий 29 лютого 1900 року був введений в Excel і залишається там донині.
Але архітектори Power Query не є електронними таблицями. У них в офісі немає статуй Брикліна та Франкстона. Вони не знають цієї історії. Вони прийняли рішення, що їхніми датами буде кількість днів, що минули з 31 грудня 1899 року. Це змушує людей Power Query відчувати себе трохи вищими за людей Excel, оскільки будні, про які повідомляє Power Query за 60 днів на початку 1900 року, є більш правильними, ніж в Excel.
Це не про що турбуватися. Ніхто не має справи з датами з тих 60 днів.
Але тут є щось набагато більш поширене. Розглянемо цю таблицю відомих чисел.
Я попрошу вас "зробити неправильно" і випадково виконати наступні дії:
- Виберіть стовпець N
- Натисніть Ctrl + Shift + 3, щоб відформатувати стовпець як дату
-
Не помічайте, що ви робили будь-яку з цих речей.
На жаль, цифри випадково датуються - Використовуйте дані, отримуйте та перетворюйте, із таблиці або діапазону.
- Коли ви перейдете до Power Query, зверніть увагу на дати у стовпці цифр. У полі Застосовані кроки видаліть крок Змінений формат.
Коли дані повертаються до Excel, все вимикається на 1. Кола більше не існують. Хайнц має 56 сортів замість 57.
Я усвідомлюю, що це наша остання п’ятниця у Excel Lent. Я розумію, що це надуманий і неясний приклад. Які шанси хтось випадково відформатує стовпець із числами в діапазоні 1-60 як дати перед переходом до Power Query? Це здається низьким, але це сталося.
Power Query - це чудова функція. Я впевнений, архітектори вважали, що ніщо не могло піти не так, якби вони були розумнішими за тих людей, які в 1978 році помилились. Але чи спрацюють мільярди електронних таблиць, оскільки ми всі погоджуємось прийняти помилку, ви розриваєте крихітну дірку в тканині Excel.
Кожної п’ятниці я вивчаю помилку чи іншу рибну поведінку в Excel.
Думка дня в Excel
Я попросив своїх друзів Excel Master порадити їх щодо Excel. Сьогоднішня думка поміркувати:
"Ctrl + Shift + U перемикає висоту рядка формул"
Боб Умлас
