Как стать автором
Обновить

Комментарии 85

Давайте выбросим MS Excel (далее с вашего позволения просто - "эксель") и начнем работать на Python

Это же всегда так, сначала "дед мороза не существует", потом оказывается "на ноль делить можно", ну а дальше по накатанной "забудьте чему вас учили в школе" :D

НЛО прилетело и опубликовало эту надпись здесь
Вы еще скажите, что квадратный корень из отрицательного числа — это выдумка… хе-хе…
Полноты ради — нам в ТФКП разрешили :) когда числовую прямую закольцевали, а из плоскости комплексных чисел сделали сферу.
Ну а то что 0*∞ стало неопределённостью (точнее, добавилось к существовавшей операции) — ну да, s/bug/feature/g.

Вот пока вы занимаетесь этим анализом один и никто к вам не лезет - вы можете хоть на чем угодно это делать. Отчеты выдаются, начальник доволен.

Но, предположим, вы увольняетесь. И вместо вас приходит другой человек, который должен быстро все после вас разгрести и делать то же самое. Сможет он это все понять на куче скриптов из VBA в куче Excel-файлов? Ну удачи ему.

Или, более оптимистичный сценарий - у вас работы по горло и вы просите помощника. Вам его дают и у вас встает вопрос об использовании этих файлов вдвоем - кто-то что-то поправил в расшаренном файлике, а другой это затер. Или не понял как это использовать.

Если не дают ничем пользоваться кроме Excel... Ну я бы не стал это терпеть, например..

// Кстати, я случайно поставил статье плюс, хотя хотел минус

Чем то, что вы описали отличается от обычного программинга или разработки отчетов? Главная экселька одна, она является запускателем отчетов. Несколько (5-7) людей работали одновременно открывая запускную эксельку и вытаскивая то, что им нужно. Никто не жаловался. Вы первый :-)
А по поводу терпеть — добавил анекдот выше. Я остался очень доволен тем, что удалось создать для компаний.

Версионирование кода? Нет, зачем, пусть лежит в xls-файле на шаре. (sarcasm)

А уж какой простор для новых требований...

Поступило требование формировать отчет и отправлять письмом? Сделаем на Excel, конечно же. (sarcasm)

Надо отправлять этот отчет каждый день в 6 утра? Напишем еще велосипед! (sarcasm)

Надо хранить отчет в базе и выдавать сторонним клиентам через API с фильтрами? О-о! Вот это простор для Excel! (sarcasm)

(все кейсы выше взяты из реальной жизни, изначально заказчик не упоминал о них при заказе отчетов)

Версионирование кода? Нет, зачем, пусть лежит в xls-файле на шаре. (sarcasm)

Ну вот и вы туда же. Ваш сарказм тут мимо. Значит все таки не умеете это все готовить. Экселька прекрасно загружает модули/код из текстовых файлов которые вы можете версионизировать.

У вас кейсы были из вашей жизни, я описал из своей. Много из вышеперечисленного решается так или иначе велосипедом под названием VBA. Вы просто не умеете это готовить

Экселька прекрасно загружает модули/код из текстовых файлов которые вы можете версионизировать.

Ок, согласен. А вы так делаете?

Много из вышеперечисленного решается так или иначе велосипедом под названием VBA

Ну понятно, можно и мультики рисовать в Excel. Можно? Можно. Нужно? Эмм....

Вы просто не умеете это готовить

Ой, все (с).

Экселька прекрасно загружает модули/код из текстовых файлов которые вы можете версионизировать.

А что делать с процедурами загрузки? Выносим за скобки? В общем, когда я увижу вашу библиотеку на github.com, тогда и продолжим разговор.

А, кстати, что насчет unit и всяких прочих тестов? CI/CD? Linux environment?

Как говорится - главное не размер, а умение пользоваться)

Я себе на связке Excel+MS SQL сделал свой гитхаб с версиями и автообновлением файлов)

Так что это совсем не проблема.

При этом весь бэкенд на sql, а фронт - на Excel.

Так же совсем не проблема отправлять письма из Экселя и даже звонить)

То есть как инструмент для модифицирования данных пользователем Эксель очень даже ничего. И главный плюс - скорость разработки.

Конечно он не панацея и технологии не стоят на месте, в отличии от VBA, но у меня тоже подгорает, когда кто то необоснованно пренебрежительно говорит об этом мегакомбайне )

  • xls файлы — это просто XML. Он прекрасно хранится в git.
  • Для формирования отчетов ничего лучше excel не придумать — делаем шаблон с нужными графиками и таблицами, макросом подставляем в него данные — и вот у вас красивый документ.
  • Отправить письмо — две строчки на VBA.
  • Каждый день в 6 утра — стадартный виндовый планировщик.

xls файлы — это просто XML. Он прекрасно хранится в git.

Сначала надо распаковать. После любого мелкого изменения в GUI в XLS будет столько правок, что фиг поймешь и найдешь (в GUI), что там на самом деле поменялось.

Для формирования отчетов ничего лучше excel не придумать — делаем шаблон с нужными графиками и таблицами,

LaTEX прозрачнее и мультиплатформеннее. Графики и диаграммы - ну перенесите визуальые стили из одного XLS-файла в другой... Если нужна защита от копирования, чтобы документ можно было посмотреть только на корпоративном ноутбуке (с опциональным запретом печати), находящемся в корпоративной сети, то для PDF решения есть. А для XLS?

макросом подставляем в него данные — и вот у вас красивый документ.

Документы с макросами не отрываю. Или в виртуальной машине.

Когда появился эксель, мне сказали: "выбрось свои awk и прочие юниксовые утилиты". Я их естественно не выбросил. Но зато подготовив данные можно загнать их в эксель и получить графики, с минимальными затратами времени. Всякому инструменту своё применение.

Однако на постоянной основе так делать отчёты, или что-либо другое, - это маразм. Чтобы не заниматься этим маразмом, нужно действительно либо кодить либо юзать BI, как это и практикуется.

Я согласен со всем Вами написанным!
НО! Душой я на стороне автора ;)
По жизни всё именно так, пока Excel ведёт создатель, всё как то работает, а вот потом приходит чувак, не фига не понимающий ни Excel, ни VBA, ни схему работы и начинает строить «супер правильное» приложение. По моему горькому опыту, вероятность, что то что он построит что то лучше, не очень высока и исключения только подчёркивают это правило.
Access + Excel еще круче.
Прикольно, но я тоже работал на АОН в свое время.
На Access написал приложение и базу данных для страхового брокера, а все отчеты Access переносил автоматически в Excel. Потом Access подключал вместо нативных таблиц к таблицам MS SQL.
Потом еще и Word пришлось подключить для генерации коммерческих предложений.
Словом я бы речь вел за MS Office, а не за один только Excel.

Словом я бы речь вел за MS Office, а не за один только Excel.

За умение использовать.

Счета (Invoices) я чудно сохранял в PDF из самого экселя. Ну и так же чудно добавлял сгенерированные файлы аттачем к письмам в Outlook. Так что да, пусть будет Office. Давно привык использовать SQL — поэтому даже выборку из экселевских таблиц для меня проще было сделать на нем. А так давно ковырял чужое приложение в Access + VBA

Нас в универе прокачивали на "табличных процессорах" (так это тогда в курсе называли) заставляя на "зачёт" писать игры в них: тетрис, змейка, жизнь...

но, для реальной задачи в жизни я все таки выбрал для автоматизации финотчетов веб-приложение с импортом в mysql + sql + php

и всюду вижу, что весь набор от MS этакий супер комбайн с огромным потенциалом, но этот потенциал никто не хочет пользовать. Как калькулятор пользуют. То ли не разъясняют назначение, то ли привычка с ворованных/халявных копий. Как фотошоп для просмотра картинок. В этом отношении, у Apple их офисный пакет лучше спозиционирован

Мне довелось много поработать и с экселем и с аксессом. Хорошие инструменты, но полные детских болячек, которые уже десятилетиями никто не собирается лечить.

Достаточно серьезные баги, которые я встречал в экселе и в аксессе 15 лет назад живы и сейчас. В аксессе "портятся" запросы, если таблица, на которую ссылается запрос залинкована из эксельки или другой мдбшки на сетевой папке, которая в данный момент недоступна. Т.е. упала сеть а ты в это время открыл запрос, он сломался. Сеть появилась снова, а запрос так и остался сломанным - круто же!

Ну или откройте в аксессе таблицу с миллионом записей, поставьте фильтр, чтобы осталось только 10 записей, а теперь отсортируйте эти 10 записей по возрастанию. Пока он сортирует можете пойти чайку попить.

А в Экселе я видел файл в котором больше 20 вкладок, на каждой вкладке сотни формул, они все ссылаются друг на друга, при этом все корректно сделано, ссылки все правильные, формулы правильные, но эксельку просто глючит от того, что она слишком большая и с какого то количества формул там в результате расчета начинается дичь.

А так действительно инструменты удобные, починили бы детские болезни, вообще было бы круто.

Т.е. упала сеть а ты в это время открыл запрос, он сломался. Сеть появилась снова, а запрос так и остался сломанным - круто же!

Справедливости ради, я могу написать код на десятке языков программирования, который поведет себя так же.

больше 20 вкладок, на каждой вкладке сотни формул, они все ссылаются друг на друга

А вот это да, реальная жесть. Особенно когда есть ссылки на внешние файлы и именованные области, тебе прислали (только один) файл по почте, а у тебя оказался только LibreOffice. Разбирайся как хочешь.

Достаточно серьезные баги, которые я встречал в экселе и в аксессе 15 лет назад живы и сейчас.

Старые баги — это предмет отдельного поста. Мне кажется либо M$ перевел баги в разряд фич, либо каждый раз хочет убить всю разработку VBA, но с каждым новым релизом офиса понимают что это будет выстрел себе в ногу
Я использовал Access для первичного анализа и обработки данных и у меня есть отработанный инструмент для этого. Но нынче приходится пользоваться чем то другим. Если с багами ещё как то можно бороться, то вот ошибки в доступе к новым типам данных типа bigint делают даже такое использование невозможным (на куче задач).

Имхо, Access намеренно кастрировали, чтобы не конкурировал с младшими версиями SQL Server. Например, ограничение на размер файла ну совсем смешное. SQL редактор невозможно убогий - даже подсветки нет. И у меня там иногда Copy/Paste переставал работать...

Кстати, редактор кода, с подсветкой и прочими плюшками, уже подтверждали. Если планы не поменялись, то функционал (и косяки, конечно) будет равен редактированию М-кода в powerquery.

В последнее время раздражало то, что для того, чтобы соединить несколько ячеек в одну строку, надо либо через "&" указывать ячейки, либо использовать СЦЕПИТЬ() где тоже нужно указывать каждую ячейку. Варианта функции, чтобы указать диапазон (как в СУММ()), нет. Пришлось писать самому.

Это еще один из плюсов, когда можно дописывать свои функции для использования в ячейках
А в Экселе я видел файл в котором больше 20 вкладок, на каждой вкладке сотни формул, они все ссылаются друг на друга, при этом все корректно сделано, ссылки все правильные, формулы правильные, но эксельку просто глючит от того, что она слишком большая и с какого то количества формул там в результате расчета начинается дичь.

Это у меня стандартная ситуация, excel действительно подглючивает на итеративных вычислениях. Но решается просто: отключается автоматический пересчет листов, хотя он у меня и так всегда отключен, и небольшой скрипт, который пересчитывает листы в нужной последовательности.

Мне довелось много поработать и с экселем и с аксессом. Хорошие инструменты, но полные детских болячек, которые уже десятилетиями никто не собирается лечить.

Особенно бесит, самопроизвольно упёртое преобразование в дату, того что датой не является.

А то, что Excell на русском не понимает формулы на английском и наоборот — делает бесполезным использование формул в международных компаниях.
А то, что Excell на русском не понимает формулы на английском и наоборот — делает бесполезным использование формул в международных компаниях.

если офис установлен русскоязычный, то формулы надо писать на русском. но открыв сохраненный файл в англоязычном офисе — они автоматически будут преобразованы к английскому варианту
Особенно бесит, самопроизвольно упёртое преобразование в дату, того что датой не является.

Да!

Особенно бесит, самопроизвольно упёртое преобразование в дату, того что датой не является.

Больше всего бесит, что они знают, что это больно, но принципиально выступают за дальнейшее причинение боли:

Microsoft Excel предварительно программируется, чтобы упростить ввод дат. Например, 12/2 изменяется на 2-дек. Это очень неприятно, если вы вводите что-то, что не хотите менять на дату. К сожалению, отключить эту возможность не получится. 

Чтобы акцессовские таблицы не глючили нужно выбирать данные через запрос, а уже в нем производить расчеты, сортировки, группировки...

Обрабатывая многомиллионные ежедневные транзакционные данные, нужно было посчитать Scheme Fee (микро платежи в пользу Visa/MasterCard начисляемые по различным параметрам как например — за каждые 1000 платежей по магнитной полоске.....


ойой как знакомо… я учавствовал в написании такой штуки на....1С!!! итогом было сокращение отдела из 5х человек… которые кстати тоже это всё считали в экселе с огромными vba скриптами
Вот кто бы что не говорил, а у 1с отличный инструмент — СКД, позволяет подобные отчеты раз в 10 быстрее создавать.
1С крайне недооценен в сфере 'большого ИТ'

Имеет право на жизнь в небольших конторах, которые не особо хотят вкладываться в софт. Но когда у вас появляется куча филиалов, сделайте уже нормальную информационную систему, куда будут попадать все необходимые данные и откуда все необходимые отчёты можно будет получить через SQL запросы. Правда есть тут небольшой косяк... Некоторые люди будут сидеть без работы, а значит их ставки можно сократить.

Питон удобнее, чем убогий древний VBA. Одна строчка в Питоне может сделать больше, чем строчка на VBA. Ну, например, list compehension — в VBA такого нету. Следовательно, производительность и удобство работы программиста на Питоне выше.


Также, при редактировании кода на Питон вы можете использовать нормальный редактор, а не встроенный в офис. Код на Питон удобно коммитить в репозиторий.


Код на Питоне может обрабатывать большие объемы данных, чем может поместиться в Excel-таблицу. Документация к Питону лучше.


Следовательно, лучше использовать Питон.


Единственное, Питон медленный, но если ваши данные можно потоково обработать с помощью numpy, то будут задействованы SIMD инструкции и производительность будет высокой.


В тех страховых и финансовых компаниях которых я работал, не существовало возможности установить дополнительно программное обеспечение на компьютер или тонкий клиент.

Эта работа предполагала ручную обработку данных, а вы зачем-то лезете с автоматизацией. Вы устроились на работу, где не приветствуется автоматизация, и пытаетесь этим как-то оправдать использование VBA. Если уж на то пошло, то на такой работе и макросы могли бы запретить — так как через них распространяются вирусы.

Код на Питоне может обрабатывать большие объемы данных, чем может поместиться в Excel-таблицу. Документация к Питону лучше.

Поясните мне пожалуйста зачем для презентации данных мне загружать даже более 10 тыс строк? Если мне нужно сделать транзакционную выгрузку данных, я буду использовать другие средства. Если мне нужно показать распределение покупок по типам карт, то все это легко уместится в даже в таблицу с тысячей записей. Расскажите мне пожалуйста какие преимущества я получу используя питон на примере видео выше

Эта работа предполагала ручную обработку данных, а вы зачем-то лезете с автоматизацией.

Перечитайте пожалуйста текст. Возможно вы поймете для чего была введена автоматизация при постоянно возрастающем объеме данных. Так же удивлен откуда вы знаете какую обработку предполагал анализ данных? :-)

Ну и не забывайте пункт 1 — далеко не везде есть возможность использовать другие программные средства

Одно время мучался с VBA, а потом открыл для себя Power Query, в котором много чего работает сразу из коробки - например собрать инфу из кучи файлов или подружить данные из разных источников. Excel решает)

НЛО прилетело и опубликовало эту надпись здесь
>VBA в текущем виде скоро перестанет поддерживаться
Такое лет 15 или даже 20 говорят, но пока не случилось.
Но может что-то знаете? Это действительно было бы крупной проблемой. Я полагаю, что число всякой малозаметной мелочевки (и даже не мелочевки) написанной в офисе куда больше в мире чем кажется молодежи

Ну, VBA вообще возглавляет список самых нелюбимых (? dreadful) языков программирования. И, хотя отказаться от него в ближайшие лет 10 вряд ли получится из-за большого количества скриптов во всех отраслях, но новых фич в нем я бы не советовал ждать. Cobol тоже еще не умер как бы, но...

Работаю с продуктами от OsiSoft и Rockwell Automation, их объектные модели поддерживают автоматизацию VBA (тут питонисты должны слиться).

Иногда приходится иметь дело с их PI-серверами, так пакет win32api дает возможность работать с ними из питона в windows. А можно и из Linux'а через jdbc.

НЛО прилетело и опубликовало эту надпись здесь

Мне, увы, эти радости недоступны. Сервера старые. Но и так нормально.

Лет 10 назад делали аналитическую систему, которая выгружала данные из PI с целью дальнейшего «верчения» в OLAP.
Даже тогда у PI был вполне сносный SQL драйвер, который позволял не просто выгрузить данные, но еще и применить встроенные функции PI к этим данным

Мне хватило раз по 20 наступить на пару моих любимых граблей с экселем:

  1. Берём вывод из MS SQL Management Studio, где есть даты и копи-пастим в эксель. Чу! Часть строчек выпадает из статистики потому что Студия добавила миллисекунды через точку и Эксель посчитал, что это теперь не дата, а строка. Не беда! Поменяем формат ячейки на строку... Ой - теперь у меня вместо дат какие-то красивые цифры, 4-с-чем-то-тысячи. Надо было сразу в Jupyter сделать pandas.read_clipboard() и не было бы проблем.

  2. Возьмём логи и попробуем вытащить подстроку из сообщений. Ой, НАЙТИ() в случае ненахождения подстроки выдаёт исключение! Не беда, обернём его в местный try-catch и тогда, после пары таких комбинаций, в нашей формуле уже никто и никогда не разберётся. Надо было тоже сразу делать pandas.read_clipboard(), а потом уже можно было str.extract с поиском по регуляркам...

Да, можно всё это делать в VBA, но можно же с тем же успехом использовать и Python? Последний в этом сравнении мне нравится больше, а тут как ни крути, начинается уже вкусовщина.

Эксель до сих пор использую для быстрой фильтрации по заголовку таблицы. Или для рисования графиков. Но какие-то преобразования данных уже давно делаю в питоне - там я получу результат за предсказуемое время, не попадая на неожиданности вроде формата дат или непривычной работы find/charindex/и т.п.

Берём вывод из MS SQL Management Studio, где есть даты и копи-пастим в эксель. Чу! Часть строчек выпадает из статистики потому что Студия добавила миллисекунды через точку и Эксель посчитал, что это теперь не дата, а строка.

думаю это стандартная проблема, которая появляется через Copy/Paste. Если запускать запросы напрямую с эксельки, то возвращаемый тип данных уже Date. Остается прочитать из XML файла желаемый формат презентации и вот уже хотим мы округлить десятичные до второго знака или вовсе убрать «копейки» не потревожив в итоге сумму по столбцу

Я тут ещё подумал: что ж меня так триггерит каждый раз с этих «апологий эекселя»? И, кажется, я понял: это обманутые ожидания.

Эксель говорит «дружище, давай к нам, я помогу перемолоть эту кучу данных всего парой кликов!» И ты радостно обращаешься к нему, ожидая, что через минуту у тебя уже будет готовый результат. Но через минуту ты ударяешься мизинцем об одну дырявую транзакцию: даты – не то, чем кажутся. Ничего, результат уже на горизонте – но вот новая проблема: у твоей любимой функции из обычных языков программирования не нашлось нужного аналога. Ничего, и это можно перебороть – пишем 3-этажную формулу, вытираем пот со лба – опять что-то работает не так, как ожидал.

Да, понятно, надо знать ограничения инструмента (=уметь им пользоваться). Но у меня глубокое впечатление, что эксель обещает сильно больше, чем потом может исполнить. Оттого и такая обида на этот инструмент.

Пытался донести этим постом, что нет смысла обрабатывать на экселе миллионы строк. Есть смысл вытаскивать в эксельку то, что нужно показать пользователю чтобы он не растерялся в результатах, а сразу видел куда смотреть. Для обработки больших данных есть совсем другие продукты. И этот пост не о них.

стандартная проблема, которая появляется через Copy/Paste.

Ну вот Pandas же как-то научился с ней справляться. Ни разу с ним таких проблем не было

«Ой, НАЙТИ() в случае ненахождения подстроки выдаёт исключение!»
=ЕСЛИ(ЕОШ(НАЙТИ(1;A1));"";1)

Ну, хоть опять портрет Альфа вставляй )

Какой копи-паст? Гражданин программист, Вы о чем?

"Данные" - "Получить данные" - "Из базы данных" - "Из базы данных SQL Server"

Не хотите подключаться к БД?

Сделайте выгрузку и загрузите данные оттуда, с помощью ПоверКвери (есть такая начиная с Эксель 2013). Проверьте форматы.

Не всё в порядке? Зайдите в редактирование запроса и на М-коде поправьте нужный шаг/столбец. Можете даже не думать про М-код - просто пользуйтесь "шагами и кнопочками".

По времени - на 15 сек больше чем копи-паст.

Может быть удивлю, но для любителей загружать в Эксель миллионЫ строк, без ПоверКвери не обойтись. В нем данные можно почистить, отфильтровать, транспонировать в пару кликов, без ненавистного ВБА и без Питона.

Потом, если всё равно строк больше ляма, выгрузить результат в Сводную Таблицу.

Эксель заточен под анализ БД и дашборды. Следующий шаг - ПоверБиАй, который... работает с тем же М-кодом.

Так что, думаю, если про Эксель не только разницу между "А1" и "$А$1", правильный результат можно получить чуть позже, чем моментально.

Какой копи-паст? Гражданин программист, Вы о чем?

Ну надо тогда законодательно копи-паст в эксель запретить)

Я не спорю, если я делаю какую-то витрину, к которой буду возвращаться - тогда и запрос в БД, и PowerQuery. Я же тут говорю про случай, когда хочется ad-hoc, на коленке, что-то быстро подсчитать. И в этом случае (как я писал выше) Эксель обещает справиться быстро, но на практике со всех сторон вылезают ньюансы.

Почитав комментарии видно, что каждый кулик своё болото хватит.

Могу сказать одно, это просто счастье, когда вы можете делать проект на том что хотите вы.

Ну как обычно — автор написал несколько раз что кроме офиса ничего нет, а ему
— а вот надо взять то то и то то и вообще…

Могу автора поддержать. Мне довелось съехать (опять так — вот она сила общего мнения. Я собираюсь похвалить эксель а сам написал слово «съехал») тоже к VBA на последнем месте работы
1) Офис есть везде -и этот факт крайне сложно чем-то перешибить
2) Куча народу привыкла к экселю и умеет с ним работать — если им дать форму заполняет за милую душу. Кстати говоря формы тоже быстро делаются. Не думаю, что кто то на чем угодно сможет с этим всем тягаться в разумное время (либо потребуется повторять эксель, либо делать что то иное и иметь всякие прелести разбирания — а почему тут так?)
3) Приходит снаружи куча добра именно что в экселях да вордах и наружу (сюрприз!) тоже требуется в экселях да вордах выдать.
4) Особых красивостей нет, ну да в моем случае бог с ними, по правде сказать такое ограничение только на пользу.
5) VBA не питон и т.д., много чего нет, но опять таки не бог весть что там нужно в подобных задачах
6) Внутри эксель сделан вполне эффективно и работает все довольно быстро (мы же не занимаемся прямым численным расчетом столкновения галактик). Я делал расчет всяких графиков ремонтов из нескольких десятков тысяч позиций с несколькими сотнями атрибутов и тому подобное — в общем это не такой уж малый объем, причем каждодневно собирались данные, обрабатывались, рассылались нескольким десяткам адресатов и прочее
7) По поводу багов есть такое, но если они известны то ничего страшного нет
8) Есть дополнительные расширения при желании сильно ускоряющие обработку больших массивов(Power XXX) — мне не потребовалось
9) не хватает конечно возможностей самой среды по рефакторингу и т.д. но ничего, нас спасает добрый человек разработавший RubberDuck
10) Конечно эксель мягок как пластилин в этом его сила и слабость

И в заключение хотя вот вначале мне показалось — ну как же так! эксель..., но по прошествии некоего времени могу сказать, что пожалуй если бы тогда я понимал эксель так как сейчас то при наличии выбора выбрал бы его и радовался бы

Офис есть везде -и этот факт крайне сложно чем-то перешибить

Ну, на самом деле, и не везде и не Офис. Кто-то переходит на Office365 и Microsoft сама старается его продвигать взамен, поскольку ей так удобнее деньги собирать.

А где-то переходят на OpenSource и, например, LibreOffice, который, хотя и хорошо поддерживает xlsx файлы, но не отлично и очень часто многие вещи он слегка делает не так (примеров не будет, но факт).

А если мы говорим как программисты (мы на профессиональном ресурсе как бы), то есть такие вещи как расширяемость проекта и интеграция с другими инструментами. Вот сделали вы какую-то логику внутри в Excel, но, внезапно, через какое-то время, вам нужно ее использовать еще и в другом инструменте (написанном на Java/Python/PHP или даже в другом Excel-файле). Можно нагородить кучу костылей через COM или развернуть логику на вызов всего из Excel или копипастом в другой проект перенести код, но все это будет слегка не очень. А если ваш проект развивается, то рано или поздно такие задачи встанут.

И если они встанут перед вами - вы как-то сможете втихую это поправить, наверное. А если это будет делать кто-то еще, то ваша недальновидность в выборе инструмента будет очевидна.

Вкратце мое мнение еще раз: логика внутри Excel - плохо и не очень расширяемо. Использовать Excel (xlsx) для формирования отчетов, графиков и представления пользователю - норм.

Будем исходить из реалий. А реалии в моем случае таковы, что есть только офис. И никакой моей «недальновидность в выборе инструмента» нет. Там где я работал, нет интернета, есть только офис, есть жесткая система безопасности и прочее. Еще раз — нет никакого выбора!
Есть офис и баста! Есть задачи которые надо сделать здесь и сейчас и ничего кроме офиса нет. И! оказывается можно вполне нормально сделать

Ну и попутно меня забавляет… куча скриптов на экселе… профессиональном ресурсе…

Если подходить к делу нормально, то и все будет нормально написано с долженствующими комментариями, описаниям и прочим.
Ну конкретно мой проект так и живет уже год без меня с почти без переделок. Занимается им человек, не программист, но в силу необходимости вынужден во все этом разбираться и ничего, все у него нормально

Действительно есть проблема потенциальная — импортозамещение. Ну что же когда придет час то и будем заниматься тем, что будет

Да, я понимаю о чем вы говорите. И, действительно, в условиях когда все зарегулировано и нет возможности даже файлик на флэшке принести - немного вариантов чтобы что-то сделать и на это повлиять не получится.

До сих пор у нас в стране, я уверен, есть места где файлами на дискетках обмениваются и на древнем FoxPro базы держат.

Иногда бывает из академического интереса можно попробовать реализовать какую-то идею в искуственных ограничениях и поведать об этом всему миру.

У автора же была возможность выбрать другой инструмент, но он этого не сделал и теперь учит других поступать так же. Я с этим не согласен и не хочу такое поощрять.

>Иногда бывает из академического интереса можно попробовать реализовать какую-то идею в искуственных ограничениях и поведать об этом всему миру.
Полистал, занятно конечно…

Но в моем случае совершенно никакой возможности заниматься таким нет (прежде всего потому, что не настолько я умный), мне бы свои систему для построения графиков ремонтов сделать… так, чтобы данные для них люди готовили максимально просто и это их не напрягало, что бы меня как можно меньше спрашивали про всякие неяcности те сорок человек, которым я рассылаю ежедневно данные по другой подобной системе. И желательно, что человек который останется со всем этим мог с эти всем разобраться
Вот такие очень приземленные задачи. И вот они все таки решаемы…
Да на горизонте маячит импортозамещение, офис возможно заменится скорее всего на мойофис (между прочим вполне неплохо и уж по моим пробам точно лучше либры), впрочем я уже там не работаю
У автора же была возможность выбрать другой инструмент, но он этого не сделал и теперь учит других поступать так же. Я с этим не согласен и не хочу такое поощрять.


Вы вероятно из тех людей кто «смотрит в книгу, видит фигу». И не поощряйте меня. :-)

В тех страховых и финансовых компаниях которых я работал, не существовало возможности установить дополнительно программное обеспечение на компьютер или тонкий клиент. Вообще. Совсем. На последнем месте было невозможно запустить даже cmd. Но везде и всегда был установлен нелюбимый многими M$ Офис.

1) Как Вы думаете, в чем отличие Офис 365 от Офис 2019 или 2016? Подсказать? В обновлениях "на лету". Не факт, что это нужно 90% пользователей.

Для справки: "клеточки" не становятся "линиями в косую", но добавляются новые функции. В 2019 году, произошел очередной качественный скачек, который работает в 365, но не попал (и не попадет) в 2019. Вы в курсе, про тип ссылок "А1#"? Не "А1" и не "$А$1". Речь не о самой ссылке, а о новом... принципе работы, что-ли, в Эксель.

Сравнивать ОпенОфис и Эксель может только тот, кто дальше ВПР() не осилил и тем же Вордом пользуется, как Блокнотом. Ещё раз: таких 90% пользователей.

2) Эксель предназначен для обработки данных из БД, в меньшей степени, для создания и ведения "легкой БД".

Результат можно оформить прям в Экселе, а можно подготовить для ПоверПойнт, ПоверБиАй, выгрузить в Ворд, текст, ЦСВ, ПДФ, для связки Сервак-ГуглТаблица-ГуглДатаСтудио, т.е. для ПРЕЗЕНТАЦИИ (вариант - первичных документов) или для загрузки в БД.

Где ещё может понадобиться логика из Эксель? В файле на соседнем ПК? Сделайте надстройку.

Вам нужно обрабатывать данные в Эксель и удобнее это реализовать на Питоне? Правда?

СУБД начального уровня, предназначенную для работы с Эксель гораздо проще реализовать в самом Эксель, Аксесс, Вам не кажется?

ВБА не совместим с плюсами и шарпами? Серьезно? А все остальные совместимы?

В "говнокоде на ВБА" сложнее разобраться, чем в "трушном" на Питоне, т.к у него "ниже входной порог", как писали выше? Ниже чего? Чьего-то достоинства?

ЗЫ: "xlsx" - не панацея. Rar("xls") - годнота )

У меня есть разовая (вернее ежепятилетняя) работа по обработке приблизительно 50 листов с 30-100 тыс строк и 30-50 столбцов циферок. При этом правила обработки этих данных грубо говоря не оговорены, т.е. составляются и правятся на ходу, есть особые случаи и исключения.

Пользуюсь Excel, о какой наглядности или удобстве можно говорить в питоне, это вообще не про то.

Понимаю автора и то что вы делали, примерно такими же задачами занимался последние 8 лет, да можно сделать достаточно стабильно, но все еще и зависит от компетенций человека который пишет код. Порог вхождения в VBA очень низкий, за счет этого компетенции и уровень знаний коллег оставляет желать лучшего. Здесь много написали о боли и страданий в VBA и Excel и я их конечно же поддерживаю, лично у меня после такого опыта возникла Идиосинкразия на Office продукты.

Под спойлером гифка из своего опыта. Задача — посчитать среднее значение по диапазону ячеек и округлить формулой Mround до 0.005
MROUND(AVERAGE(«your_range»),0.005)

Ну а Эксель дает разный результат в зависимости от того как отсортирован диапазон…

Чудеса Сортировки
image
Люблю такие примеры! Или точнее люблю размахивания такими примерами. О чем это говорит? О ремесленности современного мейнстримового программинга. Большинство программеров не очень то понимает, как вообще комп работает и воспринимает числа и операции с ними в компе весьма абстрактно. Оно наверное и неплохо. Но иногда вот прорывается в таких примерах. Конкретно в этом простеньком случае за за пару минут можно установить, что выражение для mround-a 1.7675000000000001 в другом случае 1.7674999999999998. И поскольку второй параметр в mround-е 0,005, то и получаем соответствующий результат. А чего же аргументы mround-у разные? Ведь числа то то сортируемые одинаковые! Одинаковые, верно, но порядок их сложения разный и тут то вступает в действие тот неприятный факт, что реальное устройство всегда имеет ограничения. Например переместительный закон не всегда точно выполняется и суммы вполне могут зависеть от порядка сложения. Весьма незначительно, но могут. В комбинации с установленной точностью округления это даст вот такой результат. Таких примером можно наклепать весьма много и разнообразных хоть в Экселе, хоть в любой другой компьютерной системе, особенно если известны алгоритмы реализации вычислений.
Можно конечно спросить а чего же разработчики эксела, эти жалкие, ничтожные люди не догадались, например хотя бы всегда в таких функциях как AVERAGE всегда предварительно сортировать аргументы? Ответов три:
1) Мы бы тогда были лишены удовольствия разбирать такой пример
2) Тогда бы банально имели всегда конкретную погрешность, а так такая погрешность в разных местах все таки немного компенсируется
3) Это дополнительные затраты времени

По большому счету это невелики проблемы. Ну и стоит помнить о смысле mround-a
Прошу прощения, но как так получается то?
Почему цифра разная? эксель округляет каждое число по отдельности а потом среднее высчитывает?
Подскажите алгоритм работы Экселя, пожалуйста

По мне, среднее до округления должно быть одинаковом при любом порядке
>По мне, среднее до округления должно быть одинаковом при любом порядке
Это так в математической теории, когда теория оперирует абстрактными математическим объектами, но при попытке воссоздания этих объектов в аппаратных устройствах мы натыкаемся на физические ограничения.
Например число 0,3 в десятичной системе невозможно представить без потери точности в двоичной. Да и в десятичной многие операции порождают бесконечные дроби
Если не касаться специализированных програм то в общем мы имеем дело с числами размещаемыми в нескольких байтах. В целом этого достаточно для большинства нужд
Но иногда вот проявляется как в вышеописанном примере
Впрочем там скорее наблюдается эффект разгона небольшой ошибки неправильным применением функции округления
Пример неудачного применения функции округления
Пример неудачного применения функции округления (с ходу не нашел как вставить картинку, поэтому напишу так)


Вернемся однако в вопросу о зависимости результат от порядка сложения
Я бы не хотел тут демонстрировать свой ум посредством Copy-Paste, поэтому предлагаю просто набрать в поисковике «потеря точности при изменении порядка суммирования» и при наличии желания прочитать что там пишут
По правде сказать нет большого смысла все это штудировать. Достаточно для практических целей просто понимать, что такая проблема есть, но и также пониматт, что для практических целей это все не так уж существенно. Куда важнее этих мелочей

Напомню, что функции округления меняют число. Еще раз — это куда важнее тонкостей суммирования. Такое ощущение, что многие люди не отличают функции округления от форматирования.
>Например число 0,3 в десятичной системе невозможно представить без потери точности в двоичной
Очень поразительно. Как тогда наша цивилизация существует? отправляет марсоходы, торгует фьчерсами?

>Пример неудачного применения функции округления
Что именно тут неудачно?
Я не спец в округлениях на 0,005 (в жизни до десятков всегда округляли, от 5 и выше в большую сторону), но в двух строчках — 2 разных числа. Логично, что при округлении можно получить 2 разных числа
Как все-таки порядок меняет сумму?

То есть у нас есть числа, если я правильно ловил на гифке, это
1,965
1,715
1,71
1,68

Если я правильно посчитал в экселе, сумма 7,07 И эта сумма не меняется при любой сортировке

Теперь мы хотим посчитать среднюю, делим на 4, в этом же экселе, получаю 1,7675
Ну а дальше как угодно можно округлять до каких угодно знаков

В какой момент в экселе происходит баг?

знаете, мы на работе премии рассчитывали в экселе. И ни на одной работе, а на многих считали. А тут Вы описываете баг, который ээ, типа от порядка сложения сумма разница. И это для вполне себе обычных цифр 3 знака после запятой?
Мне то яндекс (на запрос «потеря точности при изменении порядка суммирования) показывает примеры, где числа с дохрена знаков после запятой

вот цитата:

>После сложения количество значащих цифр равно 10. Число с одинарной точностью (float) позволяет хранить только 8 значащих цифр, то есть на самом деле число будет равно 1.2340056Е+03. Две значащие цифры потерялись в процессе сложения. Потеря точности здесь возникает из-за того, что при прибавлении к большому числу малых чисел результат сложения выходит за пределы точности при округлении. Для того чтобы уменьшить погрешность вычислений, нужно складывать числа в порядке возрастания их абсолютной величины. Таким образом можно минимизировать абсолютную величину промежуточной погрешности при каждом сложении.

И это понятно, мили-мили-мили-мили копейки могут теряться

Единственно, что может быть, что у TrSaver не 1,965, а 1,965 (и еще дохрена знаков, просто округленные в экселе)
И тогда да, при складывании этих „дохрена знаков после запятой“ каждое число округлялось отдельно и суммы будут разные. То есть это проблема именно „невидимого округления“ (неявного округления „дохрена знаков после запятой“) а не mround
> Как тогда наша цивилизация существует? отправляет марсоходы, торгует фьчерсами?
Ну вот… так и существует… В том то и сила нашего разумного вида, что можем достичь успеха с неиадельными инструментами…

>Если я правильно посчитал в экселе, сумма 7,07.
На бумажке тоже так будет
А теперь берем эксельный файл (вбиваем в него пример), меняем расширение например на rar или zip и смотрим его в чем то что позволяет обратиться к нему как контейнеру например FAR-ом
и что мы там видим?
А вот что
a7 — ячейка в которой суммировалось по возрастанию
b7 — ячейка в которой суммировалось по убыванию
1.965 — это ведь только на бумажке так
а внутри листа мы находим такое 1.9650000000000001

c r="A7">SUM(A2:A6)7.07"B7</i>">SUM(B2:B6)7.0699999999999994</

В общем при исследовании можно много чего найти

Но не переживайте — на ваших премиях это не отразится и марсоход сядет там где надо

>В какой момент в экселе происходит баг?
Это не баг… ну не баг же что мы состоим из атомов?
>1.965 — это ведь только на бумажке так
а внутри листа мы находим такое 1.9650000000000001

Ну то есть как я писал, скрытые знаки, которые не видны. Если поставить кол-во знаков после запятой в форматировании не 3, а 16, то увидим эту единичку

Проще говоря бага нет. Есть округления. округления, которые до mroud

Почему на наших премиях? у нас, расчетчиков премий, премии простые. Скорее уж на ваших :) где сложные KPI

Вспомнил, что бывают траблы, когда округляют процентные доли, а потом бац, и сумма не 100% :)
И что с этим делать — часто хз (это в социологии, когда считают %% ответов за каждый вариант вопросов)
Что же — попробуйте поставить… ну не 16, а… чего мелочиться 30!
Дык у меня нет этого примера. Это картинка TrSaver. И там только 1.965
А как же
>Если я правильно посчитал в экселе…

Может у вас и экселя нет?
Есть, могу показать
Я взял цифры с гифки. Там «1.965» и все. Никаких других данных о том, применялось ли округление, если там единичка в конце — нет

Если бы Tsaver выложил файл экселевский, я бы посмотрел «че да как и почему»
Да зачем вам какие-то выкладывания…
Берете эксельный файл да и вбиваете в него 1,965 и что там еще… Вы же это уже сделали… или не?
И все появится!
Что там у него… особый файл что ли…
Я же у себя повторил его пример и получимл те же самые результаты которые и описал

На самом деле (это уже второй слой) даже то что мы видим внутри файла это не более чем отражение размещения чисел в памяти.
Т.е. в файле могло и не быть этих 0000000001 и т.д.
Так что мир вон какой сложный
Подождите, чет я вообще не понимаю
У меня есть экселевский файл, я туда вбил 1,965
Откуда в нем возьмутся еще какие-то знаки после запятой?

Фар не нужен. достаточно переименовать в rar и разархивировать (это еще используют чтобы снять парольную защиту)
в файле sheet1.xml
1.9650000000000001

Не знаю что сказать
>Откуда в нем возьмутся еще какие-то знаки после запятой?
Потому, что компьютер использует двоичную систему счисления и 1,965 не может быть в ней точно отражен (принятые форматы представления чисел, если интересно то это стандарт IEEE 754-2008)
Можно найти калькулятор например тут numsys.ru и позабавиться с переводами чисел
например
1,965
переводится этим калькулятором в
1.11110111000010100011110101110000101000111101011100

а если обратно?
1.11110111000010100011110101110000101000111101011100
переведется в
1.964999999999999857891452847979962825775146484375

Ну если увеличивать число разрядов то эти числа будут все ближе и ближе друг к другу но между ними всегда будет существовать разница

А если не эти числа?
например 1,25
в двоичной чичтеме это 1.01
а обратно?
1,25

Как видим в этом случае в обоих системах счисления не возникает нехватки разрядов

Эксель все это конечно не вываливает на голову пользователей, что и хорошо
> Я взял цифры с гифки. Там «1.965» и все. Никаких других данных о том, применялось ли округление, если там единичка в конце — нет

Тут могут уже работать особенности вывода конкретных средств.

Я возьму опять Python для иллюстрации. В нём вывод по repr() — стандартный для показа результата операции в интерактивном режиме — работает так, что подбирает максимально короткое десятичное представление из тех, что переводятся в ту же двоичную форму. А вот форматный показ всякими %g действует так же, как в стандартах для C и C++ — чуть упрощая, это 6 цифр после запятой. Поэтому имеем:

>>> 0.1
0.1

что внутри — от нас оно скрыло.

>>> '%g' % 0.1
'0.1'
>>> '%.60g' % 0.1
'0.1000000000000000055511151231257827021181583404541015625'

По умолчанию деталей мало, но заставим его дать максимум подробностей. 60 это уже с сильным избытком, но пусть (хватило 55).

>>> 0.1000000000000000055511151231257827021181583404541015625
0.1

Всё равно результат скана свернулся в компактную форму.

>>> 0.1*3
0.30000000000000004
>>> '%g' % (0.1*3)
'0.3'
>>> '%.60g' % (0.1*3)
'0.3000000000000000444089209850062616169452667236328125'

Знаменитый пример. «Минимальная достаточная» форма — одна цифра после потока нулей. Полная — длиннее, но она не нужна.

Кстати, честное 0.3 на самом деле в полном виде выводится с недостатком:

>>> '%.g' % (0.3)
'0.3'
>>> '%.60g' % (0.3)
'0.299999999999999988897769753748434595763683319091796875'

Так вот к чему это всё: чтобы понять, что «цифры с гифки» значат, нужно узнать, как они выводятся, что там за форма. Может быть как минимум одно из следующих:
1. До операции хранится в текстовой форме. (Это Спарта Excel).
2. Хранится двоично, показ в минимальной достаточной форме. (Немного не верю потому, что последняя окончательно утвердилась только после 2000-го года, до этого AFAIK не было эффективного алгоритма её вычисления. Если кто знает иначе — опровергните, я не продрался через поиск.)
3. Показывается с дефолтным %g.
4. Показывается в чём-то типа %15g, с расчётом «просто все гарантированные цифры в double».

Каждый из этих случаев даст свою специфику.

Повторюсь, самое непонятное — почему они внутри считают в двоичке — для основного круга пользователей это выглядит откровенно неадекватным.
Всё это можно воспроизвести, например, на Python (вычисления в честном аппаратном double, вывод на показ — самое короткое представление, которое даёт внутреннее двоичное значение):

>>> 1.68+1.71+1.715+1.965
7.069999999999999
>>> 1.965+1.715+1.71+1.68
7.07
>>> (1.68+1.71+1.715+1.965)/4
1.7674999999999998
>>> (1.965+1.715+1.71+1.68)/4
1.7675


Ну а дальше mround() честно округляет по предписанным правилам.

Как по мне, проблема не в том, что происходит округление — оно так или иначе должно было происходить. Проблема в том, что Excel, как инструмент для экономической и финансовой сфер, должен был считать всю арифметику в десятичном режиме, а не в двоичном, как вшито в процессор. В этом случае ошибки бы не было. А данный пример на числа — просто один из наглядных примеров. Он сложнее, чем самый хрестоматийный 0.1 * 3 == 0.30000000000000004, но только чуть сложнее…

В дотнете есть тип decimal, в котором представление десятичное. В Java есть BigDecimal. В процессорах IBM (линии SystemZ и POWER) бывает аппаратная десятичная арифметика. У той же IBM есть General Decimal Arithmetic Specification, в которой собраны рецепты реализации десятичной плавучки как библиотеки. В общем, процесс идёт. Почему в Excel держатся за двоичку, хотя десятичная арифметика для него достаточно дёшева — ХЗ…

(Я добавил свой ответ в старую ветку, потому что показалось, что она слишком мутно прошла и некорректно закончилась, а коллега lks1965 ещё и как-то странно изъясняется всё время. Частичный повтор сказанного поэтому тут не помешает.)

Ну если уж очень хочется python, то есть решения которые добавляют его в excel.


Для запросов к данным есть PowerQuery, который позволяет делать многие вещи просто, быстро и без кода.

после Delphi/C#/Java/JavaScript я скачусь до VBA

Не обязательно отказываться от любимого языка, так как с офисом можно взаимодействовать не только через макросы, но и через надстройки. VSTO поддерживает C# и VisualBasic.Net, а Office Add-ins — Javascript/Typescript. С остальных языков можно взаимодействовать через COM.


VSTO и COM работают только на Windows. Office Add-ins — на всех платформах (Windows, Mac, Mobile, Web).

Политика компании — никаких внешних воздействий. Ни каких установок дополнительных программ. Доступ к реестру — закрыт. Зарегистрировать новые DLL — невозможно. Поначалу даже внешние программы как-то пытался запустить через VBA но потом и эту лавочку прикрыли. Запрос на установку чего-либо (SublimeText с моей лицензией) был отклонен. Финансовая структура. Если даже как-то можно по почте прислать DLL, то зарегистрировать ее в системе не получается. Как написал товарищ выше — Это счастье когда вы можете писать на чем хотите.

С другой стороны представьте ситуацию с библиотеками sp_Blitz из видео. Вы админ к которому обращаются за помощью диагностировать систему. Вы даете упаковку скриптов (sp_Blitz) и эксельку которую просите запустить и выслать результат на мыло. Все. Не нужно просить устанавливать Python или еще чего. Офис есть везде.

На экселе всегда делаются первые рабочие POC и MVP прототипы, потом они получают путевку в жизнь и дальше переводятся в нормальный BI, с данными в базе, ELT инструментами с трансформациями на sql (ну иногда добавляется питон, если у нас airflow) , и визуализацией в чем-то аля PowerBI или Tableau.

Зарегистрируйтесь на Хабре , чтобы оставить комментарий

Публикации

Истории