В Excel скрыта огромная сила. Каждый раз, когда мне кажется, что я исчерпал все его возможности, он незаметно открывает еще одну дверь, которую можно открыть с помощью одной-единственной функции.
На этот раз секрет прост и, возможно, непреднамерен, но тем не менее гениален. Всё действительно очень просто: Excel может генерировать URL-адреса, Excel может размечать любой неразборчивый текст, который вы ему отправляете в виде URL-адреса, и Excel может отображать изображение по этому URL-адресу. Объединив всё это, Excel становится легковесным интерфейсом для целого мира бесплатных API для работы с изображениями.
Быстрые ссылки
Функция IMAGE — это основной приём.
Используйте его в качестве API-клиента.
Начнём с примера. Предположим, вы хотите вставить QR-коды в свою электронную таблицу Excel для какого-либо мероприятия или чего-либо ещё. Не просто вставить их, а сгенерировать непосредственно в Excel. Проблема: в Excel нет встроенной функции для создания QR-кодов, но она и не нужна. Если сервис может сгенерировать изображение по URL-адресу, то... Excel перетаскивает его с помощью функции IMAGE()..
Единственная часть, которая дает людям право утвердить код, — это кодирование. Если вы его пропустите, это будет недопустимо. ENCODEURL()Ваши формулы будут работать, даже если в имени человека есть пробел, тире, эмодзи или что-либо, хотя бы отдаленно напоминающее человеческое. ENCODEURL() Именно это предотвращает взрыв всего происходящего.
Предположим, в вашей таблице указаны имена гостей и наличие у них билетов. Вам нужно, чтобы QR-код содержал имя гостя и информацию о том, оплатил ли он покупку. QRServer — это простой пример API для работы со статическими QR-изображениями. Формат URL-адреса:
https://api.qrserver.com/v1/create-qr-code/?size=200x200&data=[YOURDATA]&color=000000&bgcolor=FFFFFFВ E2Эта полная формула создает QR-код:
=IMAGE("https://api.qrserver.com/v1/create-qr-code/?size=200x200&data="&ENCODEURL("name="&A2&"; paid="&D2)&"&color=000000&bgcolor=FFFFFF")Проведите пальцем вниз; каждая строка получит свой QR-код. Отсканируйте один из них камерой телефона, и вы увидите содержимое. Если вы хотите, чтобы содержимое было более информативным при сканировании, не смешивайте значения. Присвойте им названия. Вы можете закодировать несколько полей следующим образом:
=IMAGE("https://api.qrserver.com/v1/create-qr-code/?size=200x200&data="&ENCODEURL("name="&A2&"; age="&B2&"; gender="&C2&"; paid="&D2))Дело не только в QR-кодах.
Создание значков в стиле GitHub в Excel
Как только вы начинаете использовать QR-коды, ваш разум сразу же спрашивает: что еще я могу создать в виде изображения? Вот тут-то и пригодится shields.io. Вы наверняка видели эти маленькие значки GitHub повсюду. Это просто изображения, созданные из URL-адресов. Если клетка A2 Если поле содержит имя, будет создан простой синий значок:
=IMAGE("https://img.shields.io/badge/"&ENCODEURL(A2)&"-blue.png?style=flat")А теперь самое интересное: Условное форматирование Внутри значка. Если значение D2 она TRUEСделайте его синим; и если это FALSEСделайте его красным:
=IMAGE("https://img.shields.io/badge/Guest-"&ENCODEURL(A2)&IF(D2,"-blue.png?style=flat","-red.png?style=flat"))Если вы хотите, чтобы на значке отображалась метка и значение (например, статус «оплачено/неоплачено»), вы можете сделать это следующим образом:
=IMAGE("https://img.shields.io/badge/"&ENCODEURL("Paid")&"-"&ENCODEURL(IF(D2,"Yes","No"))&".png?style=flat")Создайте диаграмму, которая обновляется автоматически.
На этот раз в виде фотографии.
Здесь трюк перестаёт быть просто трюком и начинает превращаться в панель мониторинга. QuickChart может создавать диаграммы по URL-адресу. Это означает, что вы можете создать конфигурацию диаграммы в Excel, а затем отобразить изображение непосредственно в ячейке.
Если клетки B2:D2 Если данные содержат числа, которые вы хотите отобразить на графике (например, итоговые значения за три недели), то будет создана простая столбчатая диаграмма:
=IMAGE("https://quickchart.io/chart?c="&ENCODEURL("{type:'bar',data:{labels:['W1','W2','W3'],datasets:[{label:'Totals',data:["&B2&","&C2&","&D2&"]}]}}"))Этот же шаблон можно адаптировать для создания диаграмм для каждого класса, каждого человека, каждого проекта или каждой вещи. Это хорошая альтернатива... Условные строки ExcelЕсли вам нужен больший контроль.
Та же самая схема «URL + CAM + DISPLAY ITEM» работает и в электронных таблицах Google Sheets, что делает ее более универсальной.
Актуальный прогноз погоды в вашей таблице Excel.
Не забудьте зонт.
Если вам нужно что-то более практичное, wttr.in может сгенерировать изображение погоды. Если ячейка A1 Если в описании указано местоположение (например, Чикаго), то это изображение отобразит график погоды в формате терминала:
=IMAGE("https://wttr.in/"&(A1)&".png?0_m_q_n")Фотографии профиля и заполнители
Когда вам нужно «что-то видимое» для каждого занятия
Не во всех электронных таблицах нужны QR-коды. Иногда достаточно простого, хорошо видимого столбца, чтобы таблица выглядела как полноценная система, а не просто таблица.
Если у вас есть адрес электронной почты в A2 Если вам нужен единый стиль аватара, Gravatar может его создать. Эта формула генерирует аватар в стиле IDICECON без необходимости загрузки каких-либо файлов:
=IMAGE("https://www.gravatar.com/avatar/"&LOWER(TEXTJOIN("",TRUE,DEC2HEX(CODE(MID(A2,SEQUENCE(LEN(A2)),1)),2)))&"?d=identicon&s=200")Вы даже можете создавать изображения для мемов прямо в Excel.
(естественно)
Как только вы поймете, что Excel может отображать изображения, созданные через API, вы начнете экспериментировать. Вот один забавный пример: Memegen создает изображения мемов из URL-адреса, где верхний и нижний текст являются частью пути.
если бы это было A2 Он содержит верхний текст и A3 В нём содержится текст, приведённый ниже, поэтому это работает:
=IMAGE("https://api.memegen.link/images/buzz/"&ENCODEURL(A2)&"/"&ENCODEURL(A3)&".jpg",2)Замените «buzz» на другое название шаблона, и стиль останется тем же.
Не вставляйте конфиденциальные данные в сторонние API для работы с изображениями. Предполагайте, что сервисы могут регистрировать запросы. Если вы делаете это в рабочих целях, получите разрешение. А если вы создаёте что-то, чем действительно увлечены, сначала протестируйте на фиктивных данных.
IMAGE و ENCODEURL По сути, они превращают Excel в генератор URL-адресов и редактор параметров, способный отображать результаты. Именно поэтому этот трюк кажется бесконечным. Как только вы его поймете, вы начнете видеть API повсюду (и, надеюсь, научитесь ими пользоваться).
Когда я показал это своим друзьям, первой фразой, которую я произнес, была та же самая, которую я всегда говорю, когда Excel придумывает новый трюк: «А вы знали, что это можно сделать в Excel?» Так… а вы знали? Десять лет спустя Excel снова преподнес мне сюрприз.
