Power Query - это самая горячая и молодая новая функция Excel. Помните, что он не был создан командой Excel. Команда Power Query решила, что они не будут связаны многолетней ошибкой в VisiCalc. Вот почему это может вызвать у вас проблемы.
В качестве фона даты в Excel хранятся в виде серийного номера. Если вы введете сегодняшнюю дату - 30 марта 2018 года в Excel, они покажут вам дату в какой-то форме, но Excel хранит 43189. Это означает, что сегодня 43189 дней с 1 января 1900 года.
Это также означает, что для перехода к завтрашнему свиданию вы можете использовать =F3+1. Чтобы найти количество дней между датами, вы можете вычесть одну дату из другой. Это хороший способ для расчета даты.
Осторожно
Это также означает, что вы не можете легко записать даты 1800-х годов. Это плохо для генеологов или бухгалтеров 150-летних компаний.
Система была изобретена Дэном Бриклином и Бобом Франкстоном, когда они создали VisiCalc в 1978–1979 годах. Но Боб и Дэн сделали одну ошибку. Давайте вернем нашу модель в прошлое, в февраль и март 1900 года. Дата, связанная с порядковым номером 60, - 29 февраля 1900 года.
Високосные дни восходят к Юлию Цезарю. Поскольку Земле требуется 365,242189 дней, чтобы обойти вокруг Солнца, наличие календаря из 365 дней означает, что времена года будут сдвигаться на 24 дня каждый век. Юлий Цезарь разработал план добавления високосных дней в каждый год, кратных 4. Это было бы идеально, если бы Земля вращалась вокруг Солнца каждые 365,25 дня. Но эта крохотная разница между 0,25 и 0,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 должен был быть совместим с тогдашним лидером рынка 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.
Я понимаю, что это наша последняя пятница Великого поста. Я понимаю, что это надуманный и неясный пример. Каковы шансы, что кто-то случайно отформатирует столбец чисел в диапазоне от 1 до 60 как даты перед переходом в Power Query? Это кажется низким, но это случилось.
Power Query - потрясающая функция. Я уверен, что архитекторы полагали, что ничего не могло бы пойти не так, если бы они были умнее тех людей в 1978 году, которые совершили ошибку. Но будут ли работать миллиарды электронных таблиц, потому что мы все согласны принять ошибку, вы проделываете крошечную дыру в ткани Excel.
Каждую пятницу я изучаю ошибку или другое подозрительное поведение в Excel.
Идея дня в Excel
Я попросил совета у моих друзей-мастеров Excel по поводу Excel. Сегодняшняя мысль задуматься:
"Ctrl + Shift + U изменяет высоту строки формул"
Боб Умлас
