Флажки в Excel: очередной повод сказать "а в Таблицах это есть давно!"
Вы вообще помните, когда появились флажки в Таблицах? Кажется, что были всегда :)
А в Excel — пару недель назад и пока только для бета-канала обновлений у подписчиков Microsoft 365.
Работают аналогично: пробелом можно включать и выключать, в ячейке хранится ИСТИНА / TRUE или ЛОЖЬ / FALSE.
Увы, при скачивании Таблицы на локальный диск в формате xlsx "табличные" флажки в Excel превратятся просто в логические значения, придется вставлять флажки поверх них. Аналогично при импорте Excel в Таблицы.
P.S. Речь именно про флажки в ячейках, так как в Excel всегда были доступны другие флажки, которые не размещаются в ячейке, а вставляются на отдельный слой поверх ячеек. Вставляются они на вкладке "Разработчик" (Developer), и в контекстном меню каждого флажка можно настроить связь с ячейкой (в которой будет отображаться ИСТИНА / ЛОЖЬ в зависимости от флажка)
Вы вообще помните, когда появились флажки в Таблицах? Кажется, что были всегда :)
А в Excel — пару недель назад и пока только для бета-канала обновлений у подписчиков Microsoft 365.
Работают аналогично: пробелом можно включать и выключать, в ячейке хранится ИСТИНА / TRUE или ЛОЖЬ / FALSE.
Увы, при скачивании Таблицы на локальный диск в формате xlsx "табличные" флажки в Excel превратятся просто в логические значения, придется вставлять флажки поверх них. Аналогично при импорте Excel в Таблицы.
P.S. Речь именно про флажки в ячейках, так как в Excel всегда были доступны другие флажки, которые не размещаются в ячейке, а вставляются на отдельный слой поверх ячеек. Вставляются они на вкладке "Разработчик" (Developer), и в контекстном меню каждого флажка можно настроить связь с ячейкой (в которой будет отображаться ИСТИНА / ЛОЖЬ в зависимости от флажка)
👍19💊5❤1👎1🤬1🤩1
@zadavai_vopros_bot
Сначала коротко новости - мы перевели нашего chatGPT бота на другой стек, а еще мы прикрутили оплату, месяц по прежнему стоит 500 рублей *
Про бота / Пишем ботом простые скрипты / Отвечаем ботом на отзывы / Работа с текстом
~~~
Немного базы
Что самое важное при работе с языковыми моделями? Промпт. То, что вы отправляете в нашего бота (или в чат open ai).
Представьте, что у вас есть текст и вы хотите достать из него названия городов.
Промпт будет примерно таким: После "текст: " будет текст, из него нужно извлечь названия городов США, их нужно написать по-английски и каждый город нужно взять в кавычки. В тексте города могут быть написаны с ошибками, ошибки нужно исправить. Текст: ".
Советы:
1) можете использовать капслог - по непровенным данным модель это воспринимает как приказ, который нужно выполнить
2) меняйте промпт пока не придете к нужному результату
3) пишите на английском, можете использовать google translate
Сначала коротко новости - мы перевели нашего chatGPT бота на другой стек, а еще мы прикрутили оплату, месяц по прежнему стоит 500 рублей *
* Но можно пользоваться и бесплатно, каждый месяц у вас по 10 запросов, пробуйте
Про бота / Пишем ботом простые скрипты / Отвечаем ботом на отзывы / Работа с текстом
~~~
Немного базы
Что самое важное при работе с языковыми моделями? Промпт. То, что вы отправляете в нашего бота (или в чат open ai).
Представьте, что у вас есть текст и вы хотите достать из него названия городов.
Промпт будет примерно таким: После "текст: " будет текст, из него нужно извлечь названия городов США, их нужно написать по-английски и каждый город нужно взять в кавычки. В тексте города могут быть написаны с ошибками, ошибки нужно исправить. Текст: ".
Советы:
1) можете использовать капслог - по непровенным данным модель это воспринимает как приказ, который нужно выполнить
2) меняйте промпт пока не придете к нужному результату
3) пишите на английском, можете использовать google translate
👍10❤6💩2🎄2💊2
Новые чипы в Таблицах: звездочки с оценками от 0 до 5
Вводим @Оценка (@ Rating)
или идем в меню Вставка (Insert) — Умные чипы (Smart chips) — Оценка (Rating)
или нажимаем Alt+I + A + 8
И получаем возможность выбирать звездочки в числе от 0 до 5 штук.
Гибкости, увы, немного: всегда будет серый фон, всегда от 0 до 5, всегда звездочки.
Бен Коллинз в своей рассылке предлагает менять фон всей ячейки на #e8eaed — это цвет чипа — чтобы хотя бы вся ячейка была залита.
еще можно поменять цвет шрифта на любой — это будет цвет звездочек.
Кстати, он же пишет, что звездочки не работают в формулах, но мы попробовали — все работает. В ячейках хранится соответствующее число и на эти ячейки можно ссылаться в формулах. Иначе было бы совсем грустно.
Вводим @Оценка (@ Rating)
или идем в меню Вставка (Insert) — Умные чипы (Smart chips) — Оценка (Rating)
или нажимаем Alt+I + A + 8
И получаем возможность выбирать звездочки в числе от 0 до 5 штук.
Гибкости, увы, немного: всегда будет серый фон, всегда от 0 до 5, всегда звездочки.
Бен Коллинз в своей рассылке предлагает менять фон всей ячейки на #e8eaed — это цвет чипа — чтобы хотя бы вся ячейка была залита.
еще можно поменять цвет шрифта на любой — это будет цвет звездочек.
Кстати, он же пишет, что звездочки не работают в формулах, но мы попробовали — все работает. В ячейках хранится соответствующее число и на эти ячейки можно ссылаться в формулах. Иначе было бы совсем грустно.
👍35🔥10❤3🍾3🎃2👎1😴1💊1
Выделяем сегодняшнюю дату и соседние с ней.
Если хотим как на картинке — сегодня с заливкой, вчера и завтра потемнее, а все остальное побледнее серым, то применяем два правила условного форматирования к столбцу с датами:
1 С первым все просто — форматировать ячейки, если "Дата" -> "Сегодня"
2 А второе правило будет с формулой. Формула, например, такая:
И ячейки, соответствующие второму правилу (а это даты, отстающие назад или вперед более чем на один день от ячейки), форматируем серым шрифтом (или как захотите по вкусу)
Если хотим как на картинке — сегодня с заливкой, вчера и завтра потемнее, а все остальное побледнее серым, то применяем два правила условного форматирования к столбцу с датами:
1 С первым все просто — форматировать ячейки, если "Дата" -> "Сегодня"
2 А второе правило будет с формулой. Формула, например, такая:
=ABS((TODAY()-первая ячейка диапазона))>1
И ячейки, соответствующие второму правилу (а это даты, отстающие назад или вперед более чем на один день от ячейки), форматируем серым шрифтом (или как захотите по вкусу)
👍33🔥15❤3💊3😢2🫡2🎉1💩1😭1
This media is not supported in your browser
VIEW IN TELEGRAM
"Скроем формулу с глаз"
Друзья, иногда нам нужно не показывать формулу в строке формул, для этого можно воспользоваться простым лайфхаком:
1) скопируйте формулу;
2) откройте любой текстовый редактор, к пример, блокнот, вставьте ее туда;
3) добавьте несколько переносов строк между "=" и телом формулы;
4) вставьте, то, что получилось в таблицу;
Формула останется на месте, конечно, но чтобы до нее добраться нужно будет немного постараться :)
Друзья, иногда нам нужно не показывать формулу в строке формул, для этого можно воспользоваться простым лайфхаком:
1) скопируйте формулу;
2) откройте любой текстовый редактор, к пример, блокнот, вставьте ее туда;
3) добавьте несколько переносов строк между "=" и телом формулы;
4) вставьте, то, что получилось в таблицу;
Формула останется на месте, конечно, но чтобы до нее добраться нужно будет немного постараться :)
🔥44👍20👎9❤2🤪2👾2💊1
This media is not supported in your browser
VIEW IN TELEGRAM
Парсим Ютуб ⚡️
И Ютуб – Google и Таблицы – Google. Что нам это даёт?
Например то, что в скриптах Таблиц есть обёртка для использования Ютуб API. Если вы хотите достать просмотры роликов, то вам необязательно парсить страницу с роликом влоб, просто откройте скрипты, в сервисах подключите YouTube и используйте код, который будет ниже. Кроме просмотров (stat.viewCount) функция выведет и название видео и лайки.
PS Расскажите в комментариях, интересна вам эта тема? Если да, то продолжим с парсингами других площадок (телеграм, инстаграм, твич, вк, дзен).
И Ютуб – Google и Таблицы – Google. Что нам это даёт?
Например то, что в скриптах Таблиц есть обёртка для использования Ютуб API. Если вы хотите достать просмотры роликов, то вам необязательно парсить страницу с роликом влоб, просто откройте скрипты, в сервисах подключите YouTube и используйте код, который будет ниже. Кроме просмотров (stat.viewCount) функция выведет и название видео и лайки.
function get_youtube() {
let video_url = "https://www.youtube.com/watch?v=ObthaxGP7gg";
console.log(platformId)
}
function youtube(url) {
let id = url.match(/[-\w]{11,}/)[0];
let item = YouTube.Videos.list('snippet,statistics', { 'id': id }).items[0]
let stat = item.statistics
let snippet = item.snippet
return ({ views: stat.viewCount, name: snippet.title, likes: stat.likeCount, dislikes: stat.dislikeCount })
};
PS Расскажите в комментариях, интересна вам эта тема? Если да, то продолжим с парсингами других площадок (телеграм, инстаграм, твич, вк, дзен).
👍106🔥40😘3👎2💊1👾1
Выводим формулой список всех рабочих дней — от заданной до сегодняшней (или не до сегодняшней, а до какой захотите)
Для этого:
1. вычислим число рабочих дней в периоде (функция ЧИСТРАБДНИ / NETWORKDAYS)
2. Засунем это число в функцию SEQUENCE и получим последовательность чисел от 1 до числа рабочих дней в периоде
3. отправим эту последовательность в функцию РАБДЕНЬ / WORKDAY — она возвращает дату, которая наступит по прошествии N рабочих дней от заданной. В нашем случае она выдаст много дат, по одной для каждого числа полученной на прошлом шаге последовательности. Формулу вводим сочетанием Ctrl+Shift+Enter, чтобы засунуть ее в функцию ArrayFormula.
Формула такая:
На скриншоте конечная дата задается функцией СЕГОДНЯ / TODAY — так что список будет обновляться каждый день.
Как выгрузить в Таблицу все официальные выходные / рабочие дни
Для этого:
1. вычислим число рабочих дней в периоде (функция ЧИСТРАБДНИ / NETWORKDAYS)
2. Засунем это число в функцию SEQUENCE и получим последовательность чисел от 1 до числа рабочих дней в периоде
3. отправим эту последовательность в функцию РАБДЕНЬ / WORKDAY — она возвращает дату, которая наступит по прошествии N рабочих дней от заданной. В нашем случае она выдаст много дат, по одной для каждого числа полученной на прошлом шаге последовательности. Формулу вводим сочетанием Ctrl+Shift+Enter, чтобы засунуть ее в функцию ArrayFormula.
Формула такая:
=ArrayFormula(WORKDAY(начальная дата-1; SEQUENCE(NETWORKDAYS(начальная дата ;конечная дата))))
На скриншоте конечная дата задается функцией СЕГОДНЯ / TODAY — так что список будет обновляться каждый день.
Как выгрузить в Таблицу все официальные выходные / рабочие дни
🔥25👍9❤1👎1💊1
Функция ТЕКСТ / TEXT: превращаем число в текстовое значение в заданном числовом формате
Эта чудо-функция возвращает текстовую строку со значением (первый аргумент), оформленным в заданном числовом формате (второй аргумент).
Для чего нужна?
Допустим, вы хотите "склеить" в одну текстовую строку текст и число.
Чтобы получить в таблице надпись вида "По состоянию на: 20.11.23" или "Сумма продаж: 20 500". То есть текст из фиксированной части и какого-то вычисления/функции, как-то суммы чисел или текущей даты.
Проблема в том, что если сделать это "в лоб" без функции ТЕКСТ / TEXT, форматирование потеряется. Число будет без разделителей разрядов, со всеми знаками после запятой; дата будет в виде числа ("По состоянию на: 45250") — потому что вот так даты хранятся в Таблицах (и Excel).
И функция ТЕКСТ позволяет это исправить — укажите нужный формат во втором аргументе, как если бы вводили его в пользовательском числовом формате.
Итак, для даты в нашем примере нужна будет такая формула:
Эта чудо-функция возвращает текстовую строку со значением (первый аргумент), оформленным в заданном числовом формате (второй аргумент).
Для чего нужна?
Допустим, вы хотите "склеить" в одну текстовую строку текст и число.
Чтобы получить в таблице надпись вида "По состоянию на: 20.11.23" или "Сумма продаж: 20 500". То есть текст из фиксированной части и какого-то вычисления/функции, как-то суммы чисел или текущей даты.
Проблема в том, что если сделать это "в лоб" без функции ТЕКСТ / TEXT, форматирование потеряется. Число будет без разделителей разрядов, со всеми знаками после запятой; дата будет в виде числа ("По состоянию на: 45250") — потому что вот так даты хранятся в Таблицах (и Excel).
И функция ТЕКСТ позволяет это исправить — укажите нужный формат во втором аргументе, как если бы вводили его в пользовательском числовом формате.
Итак, для даты в нашем примере нужна будет такая формула:
="По состоянию на: " & ТЕКСТ (дата; "DD.MM.YY DDD")Это формат вида 20.11.2023 Пн (с коротким днем недели — за него отвечает DDD). Подробнее про пользовательские числовые форматы можно посмотреть в видео.
🔥24👍14❤4👎1🎉1💊1
Forwarded from МИФ.Курсы
#таблицы
Реакции (эмодзи) в Google Документах
Если вам нужно проголосовать за идеи, проекты, продукты или что угодное еще в Google Документе — можно использовать эмодзи!
Еще они могут пригодиться для оценки текста, домашней работы или чего угодно еще.
Это просто: выделяем текст, нажимаем на смайл справа и выбираем эмодзи по вкусу.
Реакции (эмодзи) в Google Документах
Если вам нужно проголосовать за идеи, проекты, продукты или что угодное еще в Google Документе — можно использовать эмодзи!
Еще они могут пригодиться для оценки текста, домашней работы или чего угодно еще.
Это просто: выделяем текст, нажимаем на смайл справа и выбираем эмодзи по вкусу.
👍16🎄6❤5👎3🔥2😐1
Чат для патронов
Мы давно хотели попробовать и вот наконец решились. Мы создаём чатик, в котором можно будет задавать вопросы по нашим скриптам, по формулам и по прекрасным Google Таблицам в целом, подписка на чат будет стоить 1000 рублей в месяц.
Что даёт подписка
1. Взяли скрипт у нас на канале и не работает? – Поможем внедрить
2. Есть вопросы, за что отвечает та или иная строчка в скрипте? – Расскажем
3. Обсудим в спокойной обстановке формулы, как решить задачку, зачем нужны веб-приложения в Таблицах и кто такие телеграм боты
4. Ну и конечно подпиской вы поддержите нас и наш канал ❤️
Если вам стало интересно – напишите @namokonov
Мы давно хотели попробовать и вот наконец решились. Мы создаём чатик, в котором можно будет задавать вопросы по нашим скриптам, по формулам и по прекрасным Google Таблицам в целом, подписка на чат будет стоить 1000 рублей в месяц.
Что даёт подписка
1. Взяли скрипт у нас на канале и не работает? – Поможем внедрить
2. Есть вопросы, за что отвечает та или иная строчка в скрипте? – Расскажем
3. Обсудим в спокойной обстановке формулы, как решить задачку, зачем нужны веб-приложения в Таблицах и кто такие телеграм боты
4. Ну и конечно подпиской вы поддержите нас и наш канал ❤️
Если вам стало интересно – напишите @namokonov
👍28💩8❤6🔥4👎3❤🔥2🥰1👌1
Парсим картинки из Рамблера
Друзья, недавно перестали работать формулы с загрузкой картинок из Гугл и Яндекс (ссылки давать не будем, они же не работают).
В нашем чате предложили альтернативу - рамблер (а он всё еще работает, мы тоже удивились).
Формула:
PS Большое спасибо пользователю "Недвижимость Воронежа" из нашего чата!
Друзья, недавно перестали работать формулы с загрузкой картинок из Гугл и Яндекс (ссылки давать не будем, они же не работают).
В нашем чате предложили альтернативу - рамблер (а он всё еще работает, мы тоже удивились).
Формула:
=IMAGE(INDEX(IMPORTXML(HYPERLINK("https://images.rambler.ru/search?query="&B2);"//img/@src");3))
PS Большое спасибо пользователю "Недвижимость Воронежа" из нашего чата!
👍38👏8❤5👌1
Считаем количество ответов на форму... формулой
Вы проводите опрос и хотите быстро посмотреть статистику: сколько раз пользователи выбирали тот или иной вариант при ответе на каждый вопрос (конечно, такое не прокатит с открытыми вопросами, а только при выборе из списка вариантов)
Можно поступить так:
1. выведем список уникальных ответов (функция UNIQUE)
2. для каждого ответа посчитаем, сколько раз он встречается в столбце (COUNTIF / СЧЁТЕСЛИ)
3. Объединим в одну текстовую строку через дефис или другой разделитель:
4. Чтобы все было одной формулой, проделаем это через MAP — массивом значений для этой функции и будет список уникальных ответов. Для каждого из них мы будем считать, сколько раз он встречается, и склеивать число с текстом ответа.
Можно добавить проверку на пустоту через ISBLANK / ЕПУСТО, чтобы не выводить пустые ответы и ссылаться на открытый диапазон (если ждете новых ответов на форму).
Вы проводите опрос и хотите быстро посмотреть статистику: сколько раз пользователи выбирали тот или иной вариант при ответе на каждый вопрос (конечно, такое не прокатит с открытыми вопросами, а только при выборе из списка вариантов)
Можно поступить так:
1. выведем список уникальных ответов (функция UNIQUE)
2. для каждого ответа посчитаем, сколько раз он встречается в столбце (COUNTIF / СЧЁТЕСЛИ)
3. Объединим в одну текстовую строку через дефис или другой разделитель:
COUNTIF(диапазон;ответ)&" - "&ответ
4. Чтобы все было одной формулой, проделаем это через MAP — массивом значений для этой функции и будет список уникальных ответов. Для каждого из них мы будем считать, сколько раз он встречается, и склеивать число с текстом ответа.
Можно добавить проверку на пустоту через ISBLANK / ЕПУСТО, чтобы не выводить пустые ответы и ссылаться на открытый диапазон (если ждете новых ответов на форму).
=MAP(UNIQUE(диапазон с ответами);LAMBDA(ответ;IF(ISBLANK(ответ);"";COUNTIF(диапазон;ответ)&" - "&ответ)))
🔥30❤4👍4👎2
Именованные функции (Named functions)
Если в Excel можно создавать функции (без макросов) с помощью LAMBDA и диспетчера имен, то в Таблицах есть интерфейс для этого — Named functions (именованные функции) в меню "Данные".
Вашему вниманию очень короткое видео (3 минуты, ибо там все просто) про создание своих функций таким образом. С примером функции, обрабатывающей отдельные ячейки и функции, обрабатывающей диапазоны (для такого придется засунуть в именованную функцию LAMBDA и одну из вспомогательных функций, например, MAP).
https://www.youtube.com/watch?v=ASPf1LdDoDQ
Это один из 95 уроков курса "Драйв. Гугл Драйв" в МИФе. В курсе не только Таблицы, но на них упор, конечно❤️
Если в Excel можно создавать функции (без макросов) с помощью LAMBDA и диспетчера имен, то в Таблицах есть интерфейс для этого — Named functions (именованные функции) в меню "Данные".
Вашему вниманию очень короткое видео (3 минуты, ибо там все просто) про создание своих функций таким образом. С примером функции, обрабатывающей отдельные ячейки и функции, обрабатывающей диапазоны (для такого придется засунуть в именованную функцию LAMBDA и одну из вспомогательных функций, например, MAP).
https://www.youtube.com/watch?v=ASPf1LdDoDQ
Это один из 95 уроков курса "Драйв. Гугл Драйв" в МИФе. В курсе не только Таблицы, но на них упор, конечно❤️
YouTube
Урок 12.1 Бонус: именованные функции и LAMBDA
+НАЙТИ МИФ:
Наши книги: https://mif.to/vseknigi
Наши курсы: https://mif.to/vsekursy
ВКонтакте: https://vk.com/mifbooks
Telegram: https://www.tgoop.com/mifbooks
Наши книги: https://mif.to/vseknigi
Наши курсы: https://mif.to/vsekursy
ВКонтакте: https://vk.com/mifbooks
Telegram: https://www.tgoop.com/mifbooks
👍19❤6🔥6👎3
Уникальные пары значений
Нам нужны уникальные пары значений. Например, пары "Клиент" — "Продукт" (полный список клиентов и продуктов — кто что покупал)
Если столбцы рядом — то можно просто сослаться функцией UNIQUE на эти два столбца.
Если они не рядом — то предварительно выбрать их функцией CHOOSECOLS. Например, если нам нужны первый и третий столбцы:
Нам нужны уникальные пары значений. Например, пары "Клиент" — "Продукт" (полный список клиентов и продуктов — кто что покупал)
Если столбцы рядом — то можно просто сослаться функцией UNIQUE на эти два столбца.
Если они не рядом — то предварительно выбрать их функцией CHOOSECOLS. Например, если нам нужны первый и третий столбцы:
=UNIQUE(CHOOSECOLS(диапазон;1;3))
🔥42👍10❤4👎1
Отбираем по ошибке #N/A строки в QUERY и FILTER
Друзья, иногда в наших датасетах может быть ошибка #N/A (not available или значение не доступно).
Показываем, как отобрать строки или значения с ошибкой и без:
1. Для QUERY, отбираем строки с N/A:
2. Для QUERY, отбираем строки без N/A:
3. Чтобы отобрать N/A в FILTER:
4. Ну и отбираем всё, кроме N/A в FILTER:
Друзья, иногда в наших датасетах может быть ошибка #N/A (not available или значение не доступно).
Показываем, как отобрать строки или значения с ошибкой и без:
1. Для QUERY, отбираем строки с N/A:
=QUERY(A1:A12;"where Col1 = '#N/A'";0)
2. Для QUERY, отбираем строки без N/A:
=QUERY(A1:A12;"where Col1 != '#N/A'";0)
3. Чтобы отобрать N/A в FILTER:
=FILTER(A1:A12;ISNA(A1:A12))
4. Ну и отбираем всё, кроме N/A в FILTER:
=FILTER(A1:A12;NOT(ISNA(A1:A12)))
❤22👍16🔥4👎2👏2👌1
ВПР-им с разных листов
Если вам нужно "подтягивать" данные с помощью ВПР / VLOOKUP с разных листов (например, на каждый город/месяц/склад у вас отдельный лист с данными), можно собрать ссылку с помощью INDIRECT / ДВССЫЛ.
Обычная ссылка на другой лист выглядит так:
Нам нужно подставлять внутри апострофов названия разных листов.
Сначала берем апостроф (в кавычках), потом к нему добавляем название листа, справа еще один апостроф, восклицательный знак и диапазон:
Чтобы превратить полученную текстовую строку в ссылку, используем функцию INDIRECT - она ровно для этого и используется.
И отправляем это внутрь ВПР'а как второй аргумент:
Ссылка на таблицу с примером
Если вам нужно "подтягивать" данные с помощью ВПР / VLOOKUP с разных листов (например, на каждый город/месяц/склад у вас отдельный лист с данными), можно собрать ссылку с помощью INDIRECT / ДВССЫЛ.
Обычная ссылка на другой лист выглядит так:
='Москва'!A:B
Нам нужно подставлять внутри апострофов названия разных листов.
Сначала берем апостроф (в кавычках), потом к нему добавляем название листа, справа еще один апостроф, восклицательный знак и диапазон:
="'" & ячейка с названием листа & "'!диапазон"
Чтобы превратить полученную текстовую строку в ссылку, используем функцию INDIRECT - она ровно для этого и используется.
=INDIRECT("'" & ячейка с названием листа & "'!диапазон")
И отправляем это внутрь ВПР'а как второй аргумент:
=VLOOKUP(значение для поиска; INDIRECT("
) ; номер столбца ; 0)
Ссылка на таблицу с примером
👍39🔥19❤2👎1
Данные с разных листов, на которых разная структура
Что делать в более тяжелом случае, когда данные на разных листах и еще разбросаны как попало (все в разных столбцах; нужный столбец то левее, то правее столбца с ключом для поиска)?
Тут сложнее. Можно решить эту задачу так: через ИНДЕКС+ПОИСКПОЗ (INDEX + MATCH) вместо ВПР (про эту комбинацию мы уже писали), чтобы все работало при любом порядке столбцов.
Через СМЕЩ / OFFSET будем формировать ссылку на столбец для поиска и на столбец с нужными данными. Находить их будем по заголовкам (заголовок будет находиться через ПОИСКПОЗ / MATCH, и это будет использоваться в функции СМЕЩ / OFFSET для смещения по столбцам, чтобы попасть на нужный).
Вот ингредиенты нашего коктейля:
А логика формулы в общем виде такая:
Что делать в более тяжелом случае, когда данные на разных листах и еще разбросаны как попало (все в разных столбцах; нужный столбец то левее, то правее столбца с ключом для поиска)?
Тут сложнее. Можно решить эту задачу так: через ИНДЕКС+ПОИСКПОЗ (INDEX + MATCH) вместо ВПР (про эту комбинацию мы уже писали), чтобы все работало при любом порядке столбцов.
Через СМЕЩ / OFFSET будем формировать ссылку на столбец для поиска и на столбец с нужными данными. Находить их будем по заголовкам (заголовок будет находиться через ПОИСКПОЗ / MATCH, и это будет использоваться в функции СМЕЩ / OFFSET для смещения по столбцам, чтобы попасть на нужный).
Вот ингредиенты нашего коктейля:
INDIRECT("'"&название листа&"'!диапазон")
- ссылка на ячейку или диапазон на нужном листе, с которого тянем данныеMATCH(заголовок;INDIRECT("'"&название листа&"'!диапазон");0)
- поиск нужного нам заголовка (столбца, из которого нужно тянуть данные)ROWS(INDIRECT("'"&название листа&"'!A:A")
- число строк на листе, с которого нужно тащить данные)OFFSET(INDIRECT("'"&название листа&"'!A1"); 0;MATCH (ищем заголовок, как выше) - 1; число строк, как выше;1)
- ссылка на диапазон на нужном листе шириной 1 столбец со всеми строками, с отступом от A1 до нужного нам заголовка. А логика формулы в общем виде такая:
=ИНДЕКС(СМЕЩ(который дает ссылку на диапазон на нужном листе в нужном столбце, с которого нужно тащить данные;
ПОИСКПОЗ(ключ для поиска;СМЕЩ(который дает ссылку на диапазон на нужном листе в столбце, в котором находятся ключи для поиска, например, названия товаров);0))
Таблица с примеромTelegram
Google Таблицы
ВПР-им с разных листов
Если вам нужно "подтягивать" данные с помощью ВПР / VLOOKUP с разных листов (например, на каждый город/месяц/склад у вас отдельный лист с данными), можно собрать ссылку с помощью INDIRECT / ДВССЫЛ.
Обычная ссылка на другой лист выглядит…
Если вам нужно "подтягивать" данные с помощью ВПР / VLOOKUP с разных листов (например, на каждый город/месяц/склад у вас отдельный лист с данными), можно собрать ссылку с помощью INDIRECT / ДВССЫЛ.
Обычная ссылка на другой лист выглядит…
👍16🙉5👎1
Любимый ВБ, формируем ссылки на изображения товара
Друзья, недавно у нас была задача по номенклатуре товара на Вайлдберрис создать ссылки на изображение этого товара.
Вот вам функция прямо из кода страницы WB, которую мы немного адаптировали (спасибо Роману) и теперь она может работать как пользовательская функция (смотрите скриншот) и в формуле массива.
Это нужно сохранить в редакторе скриптов:
И запускаем это чудо на листе Таблицы (номенклатуры у нас в B2:B):
🧑💻 Кто желает – перепишите под обычную формулу в Таблице, авторам лучших вариантов +7 кармы в нашем чате :)
Таблица с примером
Друзья, недавно у нас была задача по номенклатуре товара на Вайлдберрис создать ссылки на изображение этого товара.
Вот вам функция прямо из кода страницы WB, которую мы немного адаптировали (спасибо Роману) и теперь она может работать как пользовательская функция (смотрите скриншот) и в формуле массива.
Это нужно сохранить в редакторе скриптов:
function getImageUrl(nmid){
const basketObj = {
143: '01',
287: '02',
431: '03',
719: '04',
1007: '05',
1061: '06',
1115: '07',
1169: '08',
1313: '09',
1601: '10',
1655: '11',
1919: '12',
2045: '13',
2189: '14',
2405: '15',
2621: '16',
2837: '17',
9999999: '18'
};
return `https://basket-${basket[Object.keys(basket)
.filter(v=>Math.floor(nmid/100000)<=v)[0]]}.wb.ru/vol${Math.floor(nmid/100000)}/part${Math.floor(nmid/1000)}/${nmid}/images/big/1.jpg`
}
function images(nmds) {
return nmds.map(n => getimageUrl(n));
}
И запускаем это чудо на листе Таблицы (номенклатуры у нас в B2:B):
={"IMG" ; ARRAYFORMULA( IMAGE( images(B2:B)))}
Таблица с примером
Please open Telegram to view this post
VIEW IN TELEGRAM
🔥35❤7👻2👍1👎1🤝1
Google Таблицы
Чат для патронов Мы давно хотели попробовать и вот наконец решились. Мы создаём чатик, в котором можно будет задавать вопросы по нашим скриптам, по формулам и по прекрасным Google Таблицам в целом, подписка на чат будет стоить 1000 рублей в месяц. Что даёт…
Что мы делаем в нашем патреон чате?
К примеру, взяли ссылку на крипто-API https://apilist.tronscanapi.com/api/account/wallet?address=TSTVYwFDp7SBfZk7Hrz3tucwQVASyJdwC7&asset_type=1
И написали пользовательскую функцию, которая обращается к этой ссылке, приводит результат к нормальному виду и вставляет данные прямо в Таблицу.
🌊 (здесь про наш патреон-чат, условия и какие темы мы в нём обсуждаем)
📌 В комментариях будет код из скриншота.
К примеру, взяли ссылку на крипто-API https://apilist.tronscanapi.com/api/account/wallet?address=TSTVYwFDp7SBfZk7Hrz3tucwQVASyJdwC7&asset_type=1
И написали пользовательскую функцию, которая обращается к этой ссылке, приводит результат к нормальному виду и вставляет данные прямо в Таблицу.
🌊 (здесь про наш патреон-чат, условия и какие темы мы в нём обсуждаем)
📌 В комментариях будет код из скриншота.
👍10❤2💩1👾1