Когда вы в последний раз меняли свое мнение о том, что, по вашему мнению, было правдой?

Я говорю не о религиозных убеждениях, а о теме, в которой вы считаете себя экспертом. Для меня это произошло в 2013 году, когда я впервые обнаружил возможность таблиц в Microsoft Excel.

Если вы не хотите читать это мини-руководство по таблицам в Excel и Coda, я записал для вас видео ниже:

Что большинство людей делают в Excel

Как и многие новички в Excel, я думал, что Excel предназначен для хранения списков данных. Такие вещи, как списки продуктов, книги, которые вы хотите прочитать, и, возможно, личный бюджет. Все мы знаем, что сегодня Excel обладает сверхмощными возможностями с такими функциями, как PowerBI и Получить и преобразовать, но большинство более 100 миллионов пользователей Excel, вероятно, используют лишь часть возможностей и возможностей Excel. Мне не удалось найти статистику использования функций в Excel, но, просмотрев сотни файлов Excel за эти годы, я могу утверждать, что большинство людей используют Excel для одной цели: списков.

В списках нет ничего плохого. Они имеют смысл. Сетка в Excel обеспечивает прочную основу для списков.

Но как только я обнаружил такие функции, как VLOOKUP(), OFFSET(), INDEX() и MATCH(), я обнаружил, что больше не создаю автономные списки. Парадигма стала соединением списков вместе. По мере того, как мои знания Excel росли, я не мог видеть мира, в котором ваши списки данных были бы не связаны каким-либо образом. Если у вас есть список заказов продуктов, в какой-то момент вы можете захотеть узнать имя клиента, который сделал заказ, и это имя клиента, вероятно, будет взято из списка клиентов, и вы введете имя через VLOOKUP().

Это база данных или список?

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

Существует множество подобных приемов, чтобы ваш файл больше походил на приложение. Хаки для отображения отфильтрованных данных в нужном месте со случайными IF и IFERROR() формулами, добавленными в ваш файл. Хаки для ссылки на правильные столбцы для VLOOKUP() формул в случае добавления новых столбцов в ваш набор данных.

А потом я открыл для себя SQL.

Вместо ссылки на строку или столбец этот новый язык формул просто требует, чтобы вы знали имя столбца. Неважно, какой длины ваш список или сколько строк добавляется ежечасно или ежедневно; оператор SELECT извлечет все строки, соответствующие вашим критериям.

На мой взгляд, операторы SQL легче понять, но формулы Excel легче писать. Когда я создаю свои связанные списки в Excel, действительно ли я создаю мини-базу данных, а просто запрашиваю свои данные с помощью взломанных формул? Являются ли формулы Excel наиболее эффективным или правильным способом извлечения данных, или я достаточно знаком с формулами, которые кажутся правильными во взломанном решении? Это все еще вопрос, над которым я борюсь, и, надеюсь, приведенные ниже примеры продемонстрируют смену парадигмы, с которой, я думаю, многие пользователи Excel столкнутся, и то, как язык формул Coda бросил вызов моим убеждениям о языке формул Excel.

Простота ВПР ()

У вас есть два списка сотрудников и налоговых ставок ниже (меня вдохновил пример, который Джоэл Спольски использовал в своем учебнике по Excel, посмотрите его здесь):

Если вы хотите рассчитать столбец Total Tax $, вам необходимо знать ставку налога в%. Вашим первым инстинктом было бы написать формулу VLOOKUP() в ячейке F5 и перетащить эту формулу вниз в ячейку F14:

=VLOOKUP(E5,$B$21:$C$23,2,0)

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

Как я уже говорил в начале этого поста, таблицы в Excel действительно меняют правила обращения с данными. Таблицы появлялись в Excel 2007, но я редко видел, чтобы они использовались в различных файлах, над которыми я работал, работая в Google и работая внештатным консультантом. Вы, наверное, заметили, что списки сотрудников и налоговых ставок действительно представляют собой таблицы со стрелками раскрывающегося списка в строках заголовков и чередующимися цветами строк. Если вы напишете формулу VLOOKUP() в этой таблице, вы получите формулу, которая выглядит следующим образом:

=VLOOKUP([@Location],TaxRates[#All],2,0)

Когда вы перемещаете курсор в столбец E для ссылки на местоположение, вы фактически ссылаетесь на все имя столбца, подобное SQL. Точно так же вместо ссылки на $B$21:$C$23 вы ссылаетесь на таблицу TaxRates (обратите внимание, как когда я выбираю ячейку в этом диапазоне, имя таблицы TaxRates появляется в верхнем левом углу):

Еще одна ключевая особенность написания формул в таблицах заключается в том, что они автоматически заполняются до всех пустых ячеек ниже. Вот что происходит, когда вы пытаетесь записать новую формулу VLOOKUP() в таблицу «Сотрудники»:

Почему ссылка на столбец «Местоположение» как [@Location] и налоговые ставки как TaxRates[#All] важна? Эти ссылки теперь представляют собой объекты, на которые вы можете ссылаться в любом месте вашего файла Excel. Прошли те времена, когда вам нужно было знать точную ссылку на ячейку и / или диапазоны именования ячеек с уникальным именем. Еще одна похожая на SQL функция заключается в том, что вы можете добавлять строки в таблицу «Сотрудники» или «Ставки налогов», и эти строки автоматически становятся «частью» таблицы:

Если мы заглянем внутрь столбца Total Tax $, вы заметите, что он также содержит ссылки «@» на столбцы Salary и Tax Rate% вместо ссылок на отдельные ячейки:

Несколько вещей, которые мне не нравятся в этом решении:

  • Вам все равно нужно ввести номер столбца, который вы хотите добавить в свою основную таблицу (например, если в таблицу налоговых ставок добавляются новые столбцы, вам придется заменить цифру «2» на столбец с новой ставкой).
  • Автоматическое заполнение формул до пустых ячеек - это замечательно, но если вы случайно удалите отдельную ячейку в столбце «Ставка налога в%», вам придется снова скопировать и вставить формулу из другой ячейки, содержащей формулу таблицы.
  • Я думаю, что Excel поощряет вас вводить имена столбцов и таблиц, но привычным поведением большинства пользователей ВПР является перемещение курсора к столбцу или таблице поиска. Ввод ссылок на столбцы и таблицы требует, чтобы вы запомнили точный синтаксис использования символов «@» и «#» с различными скобками (хотя есть небольшая функция автозаполнения, которая поможет вам на этом пути):

Наконец, идеальное решение для создания масштабируемой формулы для связи двух таблиц должно использовать комбинацию INDEX() и MATCH(), которые я опишу в следующем разделе.

Множество способов поиска

Споры так же стара, как эволюция против креационизма (ладно, не так уж давно). Когда вы используете VLOOKUP(), а когда INDEX/MATCH? На эту тему есть бесчисленное множество сообщений в блогах. Мое простое правило таково: если у вас более 50 000 строк данных и вы заботитесь о производительности и скорости, используйте INDEX/MATCH.

В нашем простом примере «Сотрудники и налоговые ставки» VLOOKUP() делает то, что нам нужно, но это потому, что наши таблицы маленькие, а разница в производительности незначительна. Если бы это был список из миллионов сотрудников, у нас была бы проблема с VLOOKUP(). Итак, если нам нужно наиболее оптимизированное решение, давайте попробуем метод INDEX/MATCH:

Я даже не стал пытаться ввести ссылки на столбцы и таблицы, так как было бы сложно вспомнить, где использовать скобки, символы «@» и т. Д. Я просто сослался на столбцы и диапазоны ячеек как «старые» -fashioned »способом с помощью курсора. Итак, когда все сказано и сделано, наиболее оптимизированная формула для поиска налоговой ставки выглядит так:

=INDEX(TaxRates[Tax Rate],MATCH([@Location],TaxRates[City],0))

Несколько вещей, которые мне не нравятся в этом решении:

  • Это длинный AF.
  • INDEX/MATCH считается «продвинутой» формулой Excel, которую большинство пользователей Excel не будет использовать, поскольку она требует, чтобы вы вложили функцию MATCH() в функцию INDEX.
  • Обратите внимание на то, что в конце после TaxRates[City] стоит 0? Это странный флаг, который также поражает VLOOKUP() формулу, указывающую match_type, которую вы хотите использовать в Excel. Я никогда не видел реального сценария, в котором вы использовали бы match_type из true (например, «1»). Если вы не укажете 0, Excel найдет первое наиболее близкое совпадение в алфавитном порядке, что приведет к совершенно неверным результатам для налоговых ставок:

Когда связывать таблицы вместе

Хотя я считаю таблицы в Excel очень мощными, есть несколько проблем с удобством использования, которые делают их менее доступными для обычных пользователей Excel, которые связывают списки данных вместе с хорошими ссылками на "старые" VLOOKUP() и ячейки / диапазоны.

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

  • Нефиксированное количество строк, добавляемых в файл ежечасно, ежедневно, еженедельно
  • Добавление новых столбцов не влияет на функциональность VLOOKUP() (частично решено с помощью INDEX/MATCH)
  • Неспособность товарищей по команде удалить случайные ячейки в столбце, содержащем формулы

Если я использую файл только для личного использования, расширяемость функции таблиц становится менее интересной, поскольку я обычно использую Excel только для одноразового анализа данных. Если у вашей команды есть подписка на Office 365, в которой у вас есть все новейшие и лучшие функции общего доступа в Excel Online, таблицы могут быть правильным решением, поскольку ваша команда ставит сотрудничество на первое место.

Таблицы в коде

Пока я не открыл для себя Coda, мое «мировоззрение» о связывании таблиц воедино состояло из двух парадигм:

  • Использование VLOOKUP() и INDEX/MATCH
  • JOIN операторы в SQL

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

Давайте возьмем те же данные, с которыми мы работали, и посмотрим, как столбец «Ставка налога в%» будет отображаться с помощью таблиц в Coda:

Несколько замечаний на гифке выше:

  • Столбец Местоположение представляет собой формат столбца Поиск из таблицы, который создает раскрывающийся список всех городов, которые вы можете выбрать из таблицы налоговых ставок.
  • Когда вы пишете формулу в столбце «Ставка налога в процентах», происходит автозаполнение с именами столбцов, которые вы можете использовать TAB, чтобы автоматически заполнить формулу (без надоедливого «@» или скобок для ввода).
  • Поскольку столбец Location в нашей основной таблице является поиском по таблице Tax Rates, мы можем легко ссылаться на столбец Tax Rate из этой таблицы поиска, просто написав [Tax Rate] в нашей формуле.

Когда все сказано и сделано, формула, позволяющая оптимизировать налоговую ставку, выглядит так:

=Location.[Tax Rate]

Формула в Excel:

=INDEX(TaxRates[Tax Rate],MATCH([@Location],TaxRates[City],0))

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

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

Ваши столы, ваша команда

Макеты столов из открытого офиса 60-х годов эволюционировали, разве вам не следует использовать таблицы данных (сегодня просто полные шуток)?

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

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