26.09.2022 13:53
Data_center_MIRAN 30 11000 Источник
Статья Хилла Уейна, автора почтовой рассылки «Компьютерные штучки», которая посвящена формальным методам и применению математики в программировании, истории и культуре программного обеспечения, философии и теории программирования, а также всяким экзотическим штукам и нишевым темам. В общем, рекомендуем.

В прошлом выпуске рассылки Уейн игрался с языком программирования J, который работает как арифметический калькулятор в консоли, и пошутил по ходу дела:

Я столько времени трачу на интерактивные вычисления, что даже не мог оторваться от этих функций… Может, мне просто хорошенько освоить Excel?

Пошутив про офисную программу, Хилл Уейн решил действительно посмотреть на неё поближе… и был поражён увиденным: «Это как найти сундук с сокровищами у себя на чердаке», — пишет он. И перечисляет некоторые новые функции современного Excel, о которых мало кто знает.

Вот кое-что из интересного.

В Excel теперь реальные возможности для программирования


Начнём с самой полезной функции, о которой, похоже, никто не знает. Вместо того чтобы повсюду использовать названия ячеек и диапазонов типа A15 и B1:D94, вы можете… дать им нормальные имена. Или пропустить хранение информации в ячейке — и просто напрямую присвоить значение. Почти как в настоящем языке программирования!


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

Обычно об этом не принято распространяться, но такой метод устраняет одну из главных проблем нечитаемости формул. Вместо формулы =A1*B1 можно написать =Ширина*Высота, как это делали деды.

О, и можете составлять кастомные формулы на свой вкус.

Это не шутка. В 2020 году Microsoft добавила в Excel функции LET и LAMBDA. Если присвоить формуле LAMBDA(arr,y, MAX(arr)^y) имя MAXEXP, то можно использовать MAXEXP в качестве обычной встроенной формулы. LET позволяет связывать в середине формулы новые имена, которые также могут ссылаться на предыдущие связывания. Очень простой пример: LET(x, 1, y, x+1, z, y+x) выдаёт 3. Примерно так же с LET работают все языки программирования. Конечно, синтаксис здесь немного странный, если вы не фанат S-выражений (sexp), но всё же. Полезная штука.

Заливка!


Предположим, у нас такая табличка.


Последний столбец — сложное преобразование текста

Нажимаем Ctrl+E:


Теперь в последних двух строках столбец заполнен по тому же образцу

Разве не здорово?!

Источники данных


Очень порадовала кнопка «Импорт из интернета»:



Она делает почти то, что вы ожидаете: вводите URL страницы — и оттуда HTML-таблица преобразуется в таблицу Excel.

Но есть нюанс (вот почему «почти»). Значения из интернета не просто копируются на лист. Excel сохраняет «запрос данных» по URL. Если изменить таблицу до неузнаваемости, то можно обновить запрос — и немедленно восстановить исходную таблицу вместе с любыми обновлениями из источника. То есть мы можем просто периодически (или автоматически) обновлять значения, которые подгружаются из интернета.

Это можно сделать практически с любым источником данных, включая XML, JSON и даже таблицы в PDF. В качестве эксперимента я импортировал в Excel старую налоговую декларацию в PDF — и он извлёк нужные данные.

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


Очищаем CSV от плохих данных с помощью воспроизводимых шагов

Я уже говорил, что можно переименовать и комментировать каждый шаг? Если это недостаточно круто, как вам такое — можно форкнуть любой запрос, чтобы у разных запросов были общие начальные шаги! Например, вытянуть из интернета кучу данных, очистить их — а затем разложить на отдельные таблицы 2021 и 2022. Затем, если требуется дополнительная очистка, то применяем её к форкнутому префиксу — и обе таблицы на выходе автоматически обновятся.

Excel — это по сути APL


APL — функциональный язык, оптимизированный для работы с массивами, предшественник Matlab — прим. пер.

Наверное, для меня это самое странное. В Excel есть своеобразные «транзитные» массивы: формула в одной ячейке выводит массив значений, которые транзитом переходят в другие ячейки. Например, у нас такая таблица:

A B
1 2
3 4
5 6

Затем пишем в C1 формулу =A1:A3 + B1:B3 — и получаем C1=3, C2=7, C3=11. Далее, если поместить в D1 формулу =C1^2, то просто получаем D1=9. Но если вы вместо этого написать C1#^2, то это применится к транзитному массиву C1 — и теперь у нас будет D1=9, D2=49, D3=121.

А если написать C1 = A1:A3 + TRANPOSE(B1:B3), то получится следующее:

C D E
3 5 7
5 7 9
7 9 11

Это открывает некоторые забавные возможности в стиле APL. Недавно у меня была такая таблица данных:

A  B
12	
15 x

В этом случае значение в столбце B показывает на нечётные значения, которые не делятся на два. Я хотел вычислить сумму всех значений после деления на два. «Правильный» способ будет такой:

=SUM(A:A*IF(ISBLANK(B:B), 1, 0.5))

Но можно сделать и в стиле APL, например, так:

SUM(A:A*(1-0.5*(B:B="x")))

В инсайдерской сборке есть несколько формул, которые ещё больше превращают Excel в нечто APL-подобное.

А вот игра «Жизнь» в Excel:

LET(x, SUM(OFFSET(cell#,-1,-1,3,3)), (x=3)+cell#*(x=4))



Разные крутости


  • Excel поставляется с решателем для логического программирования с учётом ограничений (constraint solver). Можно включить его в дополнениях программы (аддонах).
  • Можно напрямую перемещать или копировать листы между разными файлами Excel.
  • С помощью связанных типов данных можно вставить в ячейку позу йоги — и Excel скажет, насколько она сложная.
  • Есть кнопка для создания 3D-глобусных карт по вашим данным. Видимо, по ним можно водить экскурсии, хотя я не пробовал (и вряд ли попробую).
  • Есть кнопка, которая преобразует выборку данных в изображение.


В целом я рад, что потратил время на изучение Excel, потому что это очень интересный инструмент, который может оказаться полезным для меня. Даже удивительно, насколько он круче Google Sheets. Все обзоры в интернете говорят, что они примерно равны, но в Sheets даже нет приличных таблиц, не говоря уже о лямбдах или кнопке «Принять позу йоги» (здесь в словах автора чувствуется некая ирония — прим. пер.). Но зато Google Sheets бесплатный.

Вряд ли большинство пользователей, или даже «опытных пользователей», используют эти функции. То же самое верно и для разработчиков, большинство из которых на самом деле не используют все возможности своих редакторов. Однако такие штуки интересно исследовать!

В любом случае, любопытно познакомиться с этим инструментом, если вы или ваша компания оплатила подписку на Office 365.

Комментарии (30)


  1. Zamix80
    26.09.2022 14:00
    #24767856
    +1

    Ну да была как-то статья про игры на Excel


    1. sshikov
      27.09.2022 19:05
      #24771994

      Я начал с электронными таблицами с Quattro Pro, это MS DOS еще. Простую игру типа крестиков-ноликов можно было написать уже там.


  1. baldr
    26.09.2022 14:47
    #24768056
    +26

    Не разделяю восторг мистера Уэйна.

    Оно, конечно, эксель позволяет делать что угодно с данными и строить графики буквально парой кликов. В этом плане он, конечно, гораздо удобнее какого-нибудь pandas..

    Однако, мистеру Уэйну стоит попробовать открыть такой файлик от кого-нибудь, уже освоившего подобные глубинные трюки.

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

    =IFERROR(INDEX(t_050[[#All],[scheduled_qty]],MATCH(1,(t_050[[#All],[run_type]]="RTC")(t_050[[#All],[unit]]='SO Calculations'.J2)(t_050[[#All],[Time]]=C4),0),0),0)

    Здорово, правда? Это просто одна формула из небольшого документа. Не самое сложное из того что я встречаю, однако, сюрпризов хватает. У них MS Excel, у меня LibreOffice, так что все еще веселее.

    Именованные диапазоны - удобно, да... Такс, куда оно указывает?.. На внешний файл в Dropbox-папке у одного из пользователей? А здесь у нас макрос, который скачивает и парсит xml-файлик, но запустить все равно не получится, так как под Linux нужная DLL почему-то не находится.. А тут что у нас?. Какая прелесть - логин и пароль от базы данных в открытом виде!

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


    1. IvanSTV
      26.09.2022 15:51
      #24768296

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

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

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


      1. qyix7z
        26.09.2022 16:56
        #24768586

        Сложные формулы гораздо проще читать, когда видишь прямые адреса.
        Это Вам реально сложные не попадались. Когда формула на три-пять строк.
        В версии экселя 2003 и ранее без имен зачастую вообще невозможно было уместиться в ограничение на 1024 знаков в строке формул.


        1. IvanSTV
          26.09.2022 17:13
          #24768636
          -1

          Это Вам реально сложные не попадались. Когда формула на три-пять строк.

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

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


          1. qyix7z
            27.09.2022 06:37
            #24769934
            +1

            все время была проблема — выяснить, что буковки означают
            Осмысленные имена? Впрочем это проблема не только экселя, да и вообще не ПО, а того, кто дает эти имена.
            Или проблема в том, что неизвестно где это выяснять в экселе? Так вот же, слева от строки формул, где обычно адрес активной ячейки. Там конечно не все имена видны, но можно и сразу в Диспетчер имен, заодно понятно, что если имя видно только в диспетчере, то оно динамическое или не ссылается на диапазон
            Обновляется не быстро
            Это почему? Application.Volatile не слышали?
            но лучше, чем выискивать ошибку в четырех этажах
            Чем лучше? Там и там Вы читаете код. Оно конечно приятнее читать собственный код на VBA, но для меня разницы нет. Это все равно, что сказать "<питон, C> мне лучше <джавы, R> — и читается лучше и я умею там в обработчик ошибок, а в джаве нет" (подставьте в угловые скобки языки по вкусу).

            Мне вообще непонятно противопоставление: формулы vs имена vs пользовательские функции. Это три инструмента, которые надо применять там, где они удобнее. А лучше совмещать.


    1. vtal007
      27.09.2022 14:21
      #24771198

      У Вас в организации с бизнес-процессами все не так. Аналитики эту штуку рисуют потому что им дали ТЗ. вот когда они рисуют по ТЗ, давать ТЗ должны были и Вам, чтобы не разбираться в экселе
      С другой стороны, в экселе разобраться может почти каждый, кто с ним работал. А кто будет разбираться в пандасе-питоне? Пушкин? или в каждый отдел нанимать питонистов

      p.s. Взяли бы в отдел аналитики чела хотя бы после яндекс-практикума. Там (и не только там) учат и питону и пандасу. делали бы все на месте и сразу в одном отделе


      1. baldr
        27.09.2022 14:44
        #24771284

        Ну, у нас не такой большой штат для всего этого. Я, фактически, один эникею.

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

        Так-то конечно да - сначала требования, документы. Только этот xls-файлик, по сути, и есть требования.


    1. sshikov
      27.09.2022 19:15
      #24772016
      +1

      >Не разделяю восторг мистера Уэйна
      «Что может говорить лысый об искустве Герберта Фон Карояна, особенно если ему сразу заявить, что он лысый» (с) Жванецкий, по мотивам.

      На самом деле я с вами согласен. Мне пришлось переписывать 120 тысяч строк кода VBA на более пригодные для работы языки. Причем это было несколько файлов, некоторые из которых запускались/открывались, подключались к шине данных, и сосали данные из Reuters, к примеру. А потом считали доходность бондов, и сохраняли ее куда-то в папку. Ну примерно как у вас. Разбираться в этом ужасно неудобно. И да, пишется это людьми, которые не очень разбираются в промышленной разработке. Мы когда начали переписывать, так первое что я сказал, что нужно полностью разделить код от таблиц, положить его в VCS, и подгружать в таблицу при сборке. А таблицу с кодом сделать плагином, и подключать к таблицам с данными.

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


  1. v1000
    26.09.2022 15:13
    #24768148
    +11

    При этом недостаточно крут, чтобы адекватно загружать данные из CSV файлов. Особенно это странное желание все что хоть немного похоже на дату форматировать как дату.


    1. barbaris76
      26.09.2022 15:24
      #24768204
      +1

      Это не баг, а фича. Ссылку не дам, с телефона неудобно, но мамой клянусь, что на самом официальном сайте МС с самой официальной справкой так и написано - к сожалению, это неустранимая особенность работы ПО, поэтому можете попробовать воспользоваться некоторыми костылями (далее следует список возможных костылей, работающих чуть более, чем никак).


      1. Fartzilla
        26.09.2022 17:21
        #24768672
        +2

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


    1. wormball
      26.09.2022 20:46
      #24769246

      Вроде линуксовый csv2xls этим не страдает.


    1. yerbabuena
      26.09.2022 22:20
      #24769462
      +1

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


    1. FlashHaos
      26.09.2022 23:33
      #24769610
      +1

      Используйте PowerQuery, там все это нормально можно настроить. Формат ячеек - это боль, причем легаси боль. С ней надо уметь жить.


  1. k12th
    26.09.2022 16:17
    #24768394
    +2

    В гуглотаблицах тоже есть LAMBDA.


  1. qyix7z
    26.09.2022 16:34
    #24768470
    +6

    Я правильно понимаю, что перевод делал человек [не]знакомый с экселем примерно также как автор?
    Потому что я долго думал что же такое:

    В Excel есть своеобразные «транзитные» массивы
    И только уже по формулам вида SUM(A:A*(1-0.5*(B:B="x"))) понял, что речь идет о формулах массива. И ни слова о том, что их надо вводить тремя пальцами и их принято выделять фигурными скобками {SUM(A:A*(1-0.5*(B:B="x")))}, собственно как они в экселе и выглядят.

    В инсайдерской сборке есть несколько формул, которые ещё больше превращают Excel в нечто APL-подобное.
    Это у автора есть какая-то пиратская приватная сборка эксель не для всех с хитрыми формулами?

    С помощью связанных типов данных можно вставить в ячейку позу йоги — и Excel скажет, насколько она сложная.
    Можно это еще раз на русский перевести? Как говорится, ниасилил.

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


    1. baldr
      26.09.2022 16:38
      #24768496
      +2

      Да тут, кроме сомнительной ценности самой статьи и переводчик не сильно старался..

      Hillel Wayne
      Хилла Уейна

      Я бы перевел как "Хиллель Уэйн".


      1. qyix7z
        26.09.2022 16:43
        #24768512

        Ну с именами при переводе все очень неоднозначно, но в целом Вы правы.


    1. sshikov
      26.09.2022 19:15
      #24768984

      Да, причем имена он открыл примерно через сто лет после их появления…


      1. qyix7z
        27.09.2022 06:41
        #24769938

        Как и всё остальное. Веб-запросы и формулы массива — такие же динозавры.
        Или вот еще шикарное открытие:

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


    1. johnkimoo
      27.09.2022 10:31
      #24770424
      +1

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


    1. Al_Chemist
      29.09.2022 06:51
      #24775166

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


      1. qyix7z
        29.09.2022 08:11
        #24775452

        В последних — это в каких?
        У меня в 2016 всё еще требуется

        В ячейке А4 та же формула, что и в В4, но без трех пальцев.
        И где почитать, как эксель понимает, что надо относиться к формуле как к массиву? Потому что у меня здесь пример не удачный — без трех пальцев дает #ЗНАЧ!, а бывает, что просто разные числа, без ошибок.


  1. pav5000
    26.09.2022 16:49
    #24768550
    +2

    Даже удивительно, насколько он круче Google Sheets

    Мне кажется, это не совсем верное заявление. Они как раз примерно равны, как и говорят многие обзорщики. Пробежался по перечисленным в этой статье фичам, они все есть в Google Sheets

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


    1. vtal007
      27.09.2022 14:29
      #24771226

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

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


      1. sap058
        27.09.2022 21:12
        #24772222

        экселю не пофиг. в нем есть ограничения

        а про BigQuery в гугл таблицах слышали?

        до петабайта данных масштаб!


        1. vtal007
          27.09.2022 21:27
          #24772254
          +1

          ну может биг квери и есть, а вот скопировать из столбца в столбец для файла (который у меня на декстопе занимает 50мб) для гугл-таблиц непосильная задача. А эксель справляется с легкостью.
          читаю про BigQuery
          Экономичное, бессерверное, многооблачное хранилище данных для поддержки ваших инноваций на основе данных
          а причем тут гугл-таблицы вообще? Облачное решение использовать, чтобы перекинуть 50метров из столбца в столбец ? не, это не мой метод
          https://support.google.com/docs/answer/9702507?hl=RU

          Как начать работу с данными BigQuery в Google Таблицах
          При работе с функцией подключенных таблиц, которая представляет собой новый коннектор данных BigQuery, вы сможете просматривать, анализировать, представлять в наглядном виде и отправлять другим пользователям миллиарды строк данных из своих таблиц.
          то есть в гугл-таблицах просто коннектор к гугловскому облаку. Ну супер. а что толку, если гугл-таблица падает при работе даже с 50мб?


  1. Quark-Fusion
    29.09.2022 07:07
    #24775386
    +1

    Выросло поколение, которое является программистами, но не является «опытными пользователями ПК» :)
    Так-то всё это есть в документации к офисному продукту. И те, кому интересно разобраться с собственными инструментами её читают.
    Все эти возможности Excel уже доступны много лет, разве что чуток упростили с новыми функциями.

    При этом автор позиционирует себя как обладающим «extensive teaching experience», но такая элементарная вещь, как разобраться с базовыми интсрументами почему-то приходит в голову в последнюю очередь.