57857
Работа в Google Таблицах. Кейсы, решения и угар. контакты: @namokonov @r_shagabutdinov @IT_sAdmin оглавление: goo.gl/HdS2qn заказ работы: teletype.in/@google_sheets/sheet_happens чат: @google_spreadsheets_chat
Суммируем каждую N-ую ячейку
Допустим, надо суммировать только каждый N столбец.
Можно использовать такую формулу:
=SUMPRODUCT((MOD(COLUMN(диапазон);N)=0)*(диапазон))
(MOD(COLUMN(диапазон);N)=0)
=SUMPRODUCT((MOD(COLUMN(C2:R2)-2;4)=0)*(C2:R2))
Превращаем дату-как-текст в дату
Для этого есть целая функция — DATEVALUE / ДАТАЗНАЧ.
Любой текст она не осилит (и не только такой вольный текст, как "Позавчера" или "первый день года", но даже "17 октябрь 2022", увы — см 4 строку на скриншоте). Но текст, максимально похожий на стандартные даты, превратит в настоящие. Если года в текстовой строке нет — будет использован текущий (см строку 6). Если есть время — останется только дата (строка 8).
Альтернатива — умножить дату на единицу.
Сегодня про то, как Google Таблицы нам строить и жить помогают
Личный чек-лист: очень простая, но мощная штука.
Как говорит Скотт Адамс, системы лучше целей. Да и не только он об этом говорит. Действительно, если уделять обучению или делу немного времени, но каждый день, результаты будут мощные. Фокусируйтесь не на большой цели, а на повседневной работе.
Завести чек-лист в Google Таблице проще некуда. Просто флажки с теми пунктами, которые нужно выполнять ежедневно — полчаса на изучение той или иной темы, ежедневные шаги или тренировки, работа над проектами, отказ от сладкого. Да, каждый день отмечать все не получится. Но сам настрой на это и наглядное представление того, как вы идете, помогает сосредоточиться и делать больше важного.
То, что уже на автопилоте — ежедневный бег, например, а у вас может быть что-то другое — уже можно не заносить, смысла нет. Важно то, что можно забыть сделать.
Традиционно напоминаем, что флажки можно включать и выключать пробелом.
Создавайте личную копию шаблона, настраивайте для себя и используйте! В шаблоне зеленым горит текущая дата, серым — выходные (давайте немного расслабляться в эти дни!):
https://docs.google.com/spreadsheets/d/11pAWMxaQfoQcr1exqwsmCha_Xxm7BUE_ind6QVvispU/copy
Табличное, мощное, лучшее за 2023 год
То, про что мы хотим вам напомнить из репертуара прошедшего года.
Оглавление Таблицы скриптами
t.me/google_sheets/1074
Достаем изображения из Google и Яндекса формулой
t.me/google_sheets/1091
И из Рамблера
t.me/google_sheets/1296
Новые функции в Google Sheets - статья Михаила Смирнова
telegra.ph/Novye-funkcii-v-Google-Sheets-2023-02-02-02-07
Собираем данные с разных листов в Excel и Google Таблицах (список листов - динамический)
teletype.in/@renat_shagabutdinov/lambdasheets
Главная статья импорта. Руководство по функции IMPORTRANGE
renat_shagabutdinov/importrange" rel="nofollow">https://teletype.in/@renat_shagabutdinov/importrange
Выводим все даты текущего месяца формулой
t.me/google_sheets/1113
Импорт данных из всех Google Таблиц в списке с помощью формул
teletype.in/@renat_shagabutdinov/IMPORT-LAMBDA
Телеграм + Google Таблицы, наши решения
t.me/google_sheets/1141
Запрашиваем из Таблиц ИНН и получаем название компании
t.me/google_sheets/1159
Вычисляемое поле в сводной: умножаем сумму на одно значение
t.me/google_sheets/1233
Случайная жеребьевка команд с условием
t.me/google_sheets/1250
Парсим Ютуб ⚡️
t.me/google_sheets/1285
ВПР-им с разных листов
t.me/google_sheets/1306
Схватка двух... ВПР / VLOOKUP vs ПРОСМОТРX / XLOOKUP
t.me/google_sheets/1315
Бусты
Друзья, всех с наступившим! А давайте попробуем собрать для нашего канала бусты?
Будем иногда подсвечивать некоторые посты с помощью сторис:
/channel/google_sheets?boost
erid: LjN8K6NHH
В честь предстоящего праздника делимся не лайфхаком, а возможностью выиграть полезные призы.
🎁 1 место. Курс «Excel Pro» от онлайн-школы Action Digital School. За 55 часов получите навыки, которые помогут ускорить работу и выделят вас среди конкурентов.
🎁 2–3 места. Сертификат на 10 000 рублей в интернет-магазин «Дарить Легко». Порадуете себя покупками на Ozon, в Золотом Яблоке, Tefal, Леонардо или в сотнях других магазинов.
🎁 4–5 места. Сертификат на 5 000 рублей в интернет-магазин «Дарить Легко».
🎁 6–10 места. Подписка Telegram Premium на три месяца, чтобы получить доступ к эксклюзивным функциям приложения.
Для участия необходимо:
1. Подписаться на Telegram-каналы @lifehackoftheday и @digitalschoola
2. Затем нажать кнопку 🎄УЧАСТВУЮ 🎄
Итоги подведем случайным образом при помощи бота 3 января в 19:00 по московскому времени.
Реклама. НОЧУ ОДПО "АКТИОН". ИНН 7706218347.
#реклама
Давайте продолжим тему ПРОСМОТРов. И поговорим о старой функции LOOKUP / ПРОСМОТР.
Функция по синтаксису похожа на новую XLOOKUP / ПРОСМОТРX, о которой мы писали выше. Но она как раз была давно — в Excel даже есть примечание, что функция LOOKUP / ПРОСМОТР остается для совместимости. И у нее есть минусы :она требует постоянной сортировки данных, не особо подходит для поиска текста.
Поэтому лучше использовать VLOOKUP, XLOOKUP или INDEX+MATCH. Во всяком случае, для объединения таблиц по текстовому ключу.
Но все же в некоторых экзотических случаях LOOKUP используют до сих пор. Например, для нечеткого текстового поиска, когда нужно находить в названиях какое-то слово и заменять все значения с этим слово на одно и то же типовое (исправлять разные наименования компаний или товаров, например) — такой пример есть в статье.
renat_shagabutdinov/wFymDX7fAN4" rel="nofollow">https://teletype.in/@renat_shagabutdinov/wFymDX7fAN4
Убираем пустые ячейки из столбца / диапазона элегантно
Друзья, а у нас ведь не только сложные формулы, но и простые.
Нужно убрать пустые ячейки из диапазона и превратить его в строку или в столбец?
На помощь придут новые функции:
=torow(диапазон; 1)
=tocol(диапазон; 1)
=filter(диапазон; один столбец из диапазона <>""), всё можно сделать проще.
Чтобы быть востребованным бухгалтером в 2024 году, мало загадать желание под бой курантов. Надежнее — осваивать новые навыки, изучать инструменты и следить за рынком.
В Telegram-канале Action Digital School делятся полезными советами для развития карьеры и разыгрывают курс «Excel Pro». На нем освоите навыки, которые выделят вас среди конкурентов и позволят претендовать на повышение зарплаты. Подпишитесь и примите участие в розыгрыше — вам повезет, ведь каждый участник получит скидку 20% на курсы онлайн-школы.
Подробности здесь.
Реклама. НОЧУ ОДПО "АКТИОН". ИНН 7706218347. erid: LjN8KJuLc
#реклама
Хотите использовать всю мощь Excel для работы с данными?
Приходите на бесплатный вебинар от karpovꓸcourses, где вы узнаете:
▪️ Как можно упростить работу с данными с помощью Excel
▪️ Зачем специалистам может понадобиться Excel, если есть такие инструменты, как Python и SQL
Вы также на практике разберётесь, как с помощью доступных инструментов можно проводить оценку тестов и выборок, строить простые прогнозы и подсвечивать важное в результатах.
А через несколько дней получите обратную связь от спикера вебинара о выполненном практическом задании.
Вебинар будет полезен:
▪️Тем, кто хочет стать аналитиком данных
▪️Начинающим аналитикам данных
▪️Практикующим специалистам, в работе которых необходим статистический анализ, но нет возможности использовать Python или иные инструменты помимо Excel
Вебинар проведёт: Ксения Колодницкая, Ad-hoc Analyst в «Звук», ex-аналитик данных в karpovꓸcourses
Встречаемся 20 декабря в 19:00
Регистрируйтесь, чтобы попасть на вебинар
#реклама
СКАЧАТЬ ВСЕ МАТЕРИАЛЫ
Первый онлайн-курс по переподготовке бухгалтеров в аналитиков данных
От создателей журнала и системы Главбух и высшей школы Главбуха. Решая домашние работы на основе бухгалтерских задач вы будете изучать аналитику данных.
Например, представьте, от вашего анализа зависит премирование целого отдела – на основании ваших расчетов статистической метрики будет оцениваться успешность работы коллег!
Наверняка, вы уже сталкивались с такими задачам как бухгалтер, теперь вы работаете с ними как аналитик данных.
Вы научитесь:
▪️ строить дашборды и использовать BI-инструменты
▪️ писать код на Python
▪️ работать с базами данных в SQL
По итогу обучения вы получите:
▪️ 5 проектов подтверждающих ваши новые навыки
▪️ помощь в трудоустройстве
▪️ диплом гособразца, подтверждающий квалификацию
Начните учиться бесплатно, а потом за 3 333 рублей в месяц.
Реклама. НОЧУ ОДПО "АКТИОН". ИНН 7706218347.
#реклама
Всегда мечтал быть айтишником? Освой новую профессию промт-инженера! Гарантия трудоустройства и низкая конкуренция!
Приходи на бесплатный вебинар и узнай как:
- Повысить свой доход на рабочем месте, создавая нейро-сотрудников
- Получить новую специальность и увеличить доход на 30-40%
- Создавать нейронки и продавать на заказ от 1 млн за проект
- Зарабатывать на фрилансе от 300 000 рублей
- Найти хобби, которое прокачает твой мозг и сделает жизнь ярче
Обо всем ты узнаешь на бесплатном вебинаре от AI University.
Вот ссылка, жми СЮДА
Реклама. ООО "ТЕРРА ЭЙАЙ". ИНН 9728019395. erid: LjN8KE3zp
#реклама
Любимый ВБ, формируем ссылки на изображения товара
Друзья, недавно у нас была задача по номенклатуре товара на Вайлдберрис создать ссылки на изображение этого товара.
Вот вам функция прямо из кода страницы WB, которую мы немного адаптировали (спасибо Роману) и теперь она может работать как пользовательская функция (смотрите скриншот) и в формуле массива.
Это нужно сохранить в редакторе скриптов:
function getImageUrl(nmid){
const basket={
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',
99999:'14'
}
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)))}
Предложение для пользователей системы "1С".
Отличная возможность поднять свою ценность в компании и увеличить оплату своего труда.
Полный курс, сертифицированный фирмой "1С", узнайте подробности по ссылке: https://epic.st/oJIAzN
Чем курс будет полезен:
– вы сможете дорабатывать конфигурацию под требования заказчика;
– вы научитесь составлять ТЗ, создавать отчёты и обрабатывать данные эффективнее, чем раньше;
– вы изучите полный функционал привычной системы "1С-Предприятие" и научитесь разрабатывать и изменять её;
– вы подготовитесь к сдаче экзамена "1С:Профессионал" и улучшите свои позиции на рынке труда, а также сможете найти работу программистом 1С.
Не упускайте возможность освоить востребованную профессию со скидкой!
☃️🎁Начните год с новых знаний! Новогодняя скидка до 60% и второй курс в подарок!
Реклама. ЧОУ ДПО «Образовательные технологии «Скилбокс (Коробка навыков)», ИНН: 9704088880
#реклама
Новый проект Актиона для бухгалтеров
Актион привносит новые технологии в профессию бухгалтера более 25 лет. В этом году компания пошла дальше и запустила уникальный проект Action Digital School. Это сервис онлайн-образования, который помогает опытным бухгалтерам освоить IT-профессии.
На канале Action Digital School эксперты школы регулярно делятся полезным контентом совершенно бесплатно. В дополнение к этому проходят прямые эфиры, которые позволяют поближе познакомиться с IT-профессиями и программами курсов.
Подпишитесь, чтобы узнавать фишки для работы бухгалтера и осваивать востребованные навыки.
Реклама. НОЧУ ОДПО "АКТИОН". ИНН 7706218347. erid: LjN8KPVPD
#реклама
🟢В Системе Финансовый директор появился новый мастер-класс о работе с формулами в Excel.
Эксперты собрали в 7 простых видеоинструкциях самое важное: от базовых правил до лайфхаков, которыми владеют единицы финансистов.
Пройдя мастер-класс, Вы узнаете, как работать с формулами в Excel: как их создавать, как очистить ячейки и сохранить формулы, как найти ячейки без формул, как скрыть и защитить формулы и так далее.
Закажите бесплатный доступ к мастер-классу по ссылке: https://bit.ly/47rpjkd
Реклама. Erid 2VtzqvYFvkm
🤔 Только 6% пользователей знают все фишки «Google Таблиц» и Excel.
Возможно, все функции таблиц знать не обязательно, но часть из них сделает вашу жизнь проще. Например, если вы:
🤓 Студент. В Excel и «Google Таблицах» можно использовать формулы, создавать диаграммы, настраивать макросы.
📈 Бухгалтер или экономист. Таблицы позволяют быстро создавать отчёты и фильтровать большие объёмы данных.
💻 Digital-специалист. Инструменты нужны, чтобы строить прогнозы, разрабатывать маркетинговые планы, сравнивать эффективность рекламных кампаний.
💰 Предприниматель. Вам таблицы помогут планировать продажи, считать рентабельность проектов, прогнозировать показатели роста бизнеса.
Освоить инструменты можно на онлайн-курсе «Excel + Google Таблицы с нуля до PRO» от Skillbox.
Новогодняя скидка до 60%! Второй курс в подарок при покупке!
Переходите по ссылке, чтобы узнать подробности: https://epic.st/yWhot9
Реклама. ЧОУ ДПО «Образовательные технологии «Скилбокс (Коробка навыков)», ИНН: 9704088880
Приглашаем на бесплатный мини-курс, где вы на практике научитесь получать и обрабатывать данные с помощью SQL. Освоите и полюбите язык, который пригодится программисту, продакт-менеджеру, BI-аналитику, data-инженеру или специалисту по Data Science.
Зарегистрируйтесь на мини-курс и получите 5 полезных статей по SQL и Excel: https://epic.st/STxDeh
Вас ждут:
— море полезной теории;
— практические работы для закрепления навыков;
— бонусы: скидка 10 000 рублей на любой курс Skillbox, бессрочный доступ к материалам, полезные чек-листы и год бесплатного изучения английского языка;
— персональная карьерная консультация.
Что будем делать:
— Писать запросы на языке SQL.
— Проводить аналитику для бизнеса.
— Разрабатывать автоматизированную отчётность в Excel.
— Обрабатывать данные в Power Query.
— Визуализировать показатели в Excel: будете создавать красивые графики, диаграммы и отчёты.
— Применять инструменты Excel для анализа данных.
Спикер — Мкртич Пудеян, специалист по анализу данных в «Газпромбанке». Сертифицированный SQL-разработчик от Microsoft, 8 лет работал специалистом по хранилищам данных в Tele2.
Оставьте заявку и получите доступ к мини-курсу прямо сейчас.
Реклама. ЧОУ ДПО «Образовательные технологии «Скилбокс (Коробка навыков)», ИНН: 9704088880
#реклама
erid: LjN8KYtiX
Помогаем вам заниматься развитием бизнеса, об остальном заботится Главбух ассистент.
Канал про:
— развитие бизнеса
— цифры и опыт
— мощные навыки руководителя
Присоединяйтесь тут
Реклама. ООО "ГЛАВБУХ АССИСТЕНТ". ИНН 9715362037.
#реклама
На новый год принято ставить елку, мы не будем отходить от этой традиции и установим на нашем канале нарядную Google-Табличную ель.
В ветках у неё формула, обеспечивающая для каждой ячейки свой цвет:
=INDEX({"🟠";"🟡";"🟢";"🔵";"🟣";"🟤"};RANDBETWEEN(1;6);1)
А зажжет ёлку скрипт, он при каждом открытии Таблицы сто раз, в цикле, вставит в ячейку A1 число, тем самым запуская пересчёт формул, в частности RANDBETWEEN:
function onOpen() {
for (var i = 0; i < 100; i++) {
SpreadsheetApp.getActiveSheet().getRange(1, 1).setValue(i);
SpreadsheetApp.flush();
};
};
Таблица с ёлкой
Немного формульно-датового многоэтажного ада под конец года. Выдаем одной формулой все недели года в формате "30 янв-5 фев"
В деле почти все функции Google Таблиц 🤠LET, LAMBDA, QUERY, SEQUENCE, NETWORKDAY.INTL, MIN, DATE, MONTH, YEAR, DAYS, WEEKNUM, WEEKDAY, BYROW, TRANSPOSE, CHOOSECOLS, TEXT, LEFT, REGEXREPLACE, IF, ARRAYFORMULA в тех или иных лютых комбинациях
Друзья, наверняка у вас есть отчеты и планы, где в заголовках идут недели. Если для вас привычен формат с первым и последним днем недели, забирайте формулы от нас и маэстро нашего чата (Михаил и Игорь, спасибо!) — они выдадут все недели года одной строкой в таком формате.
Три варианта в таблице по ссылке.
— в первом случае недели идут с первого понедельника, последняя неделя будет с первыми днями следующего года ("30 дек - 5 янв"). Выдаются недели за текущий год (можете поменять YEAR(TODAY()) на фиксированный год, если нужно)
— во втором случае недели тоже с первого понедельника, последняя неделя до 31 декабря ("30-31 дек"), год выбирается в ячейке
— в третьем случае все недели (включая ту, что до первого понедельника), последняя до 31 декабря, год в ячейке
С наступающим Новым годом! Пусть в этих самых отчетах у вас все будет хорошо в следующем году 🤠Спасибо, что читаете нас!
@zadavai_vopros_bot
Друзья, а мы обновили модель, которая рисует картинки в нашем ИИ-боте, теперь там dall-e-3.
Пользуйтесь, напоминаю, что в боте 10 бесплатных запросов каждый месяц, нужно больше – 500 рублей, их можно также оплатить через бота.
Про бота / Пишем ботом простые скрипты / Отвечаем ботом на отзывы / Работа с текстом / Как писать промпты!
Давайте продолжим тему ПРОСМОТРов. И поговорим о старой функции LOOKUP / ПРОСМОТР.
Функция по синтаксису похожа на новую XLOOKUP / ПРОСМОТРX, о которой мы писали выше. Но она как раз была давно — в Excel даже есть примечание, что функция LOOKUP / ПРОСМОТР остается для совместимости. И у нее есть минусы :она требует постоянной сортировки данных, не особо подходит для поиска текста.
Поэтому лучше использовать VLOOKUP, XLOOKUP или INDEX+MATCH. Во всяком случае, для объединения таблиц по текстовому ключу.
Но все же в некоторых экзотических случаях LOOKUP используют до сих пор. Например, для нечеткого текстового поиска, когда нужно находить в названиях какое-то слово и заменять все значения с этим слово на одно и то же типовое (исправлять разные наименования компаний или товаров, например) — такой пример есть renat_shagabutdinov/wFymDX7fAN4">в статье.
XLOOKUP — двойной, пожалуйста
Поиск по двум критериям (в строках и столбцах) — обычно это решается через сочетание INDEX / ИНДЕКС и MATCH / ПОИСКПОЗ.
Но ПРОСМОТРОМ (да не простым, а икс) тоже можно.
В нашем примере ищем в матрице оценку на основе двух оценок — профессиональных и поведенческих компетенций.
Сначала одной функцией получаем массив значений для профессиональной оценки (первого критерия) — у нас это C3:C6 (оранжевое) для первого сотрудника в списке.
Потом другой уже в этом массиве ищем значение, соответствующее второму критерию — поведенческой оценке (красное).
В итоге:
=ПРОСМОТРX(критерий1;где ищем критерий1; ПРОСМОТРX(критерий2;где ищем критерий2;двумерный массив))Таблица с примером Читать полностью…
Схватка двух... ВПР / VLOOKUP vs ПРОСМОТРX / XLOOKUP. Старая и новая функции для объединения таблиц (поиска текста и чисел)
=VLOOKUP(что ищем; таблица, в которой поиск идет в первом столбце; номер столбца, из которого забираем данные; [режим поиска])
=XLOOKUP (что ищем; в каком столбце ищем; из какого столбца забираем; [на что заменяем ошибку]; [ищем текст/число/текст с подстановочными символами] ; [ищем сверху или снизу])
Минус на минус дает число: превращаем текст в число для дальнейших вычислений
Если функция возвращает текст, то результат вычисления будет текстовым, даже если состоит только из цифр. Например, если мы извлекаем суммы из текстовой строки с помощью REGEXREPLACE (или последние/первые цифры с помощью функций RIGHT / LEFT), они не будут готовы к употреблению сразу — это будут текстовые значения. См. сумму в столбце B на скриншоте или в таблице по ссылке — там ноль, хотя внешне вроде бы числа извлеклись правильные.
Как превратить текст в число в Google Таблицах (и в Excel тоже)?
1. С помощью двух минусов. Два раза умножив текст на минус единицу, мы меняем тип данных, не меняя значения
=--REGEXEXTRACT(...)
=ЗНАЧЕН(REGEXEXTRACT(...))3. С помощью еще какой-нибудь математической операции, не меняющей значение, например, умножения на единицу
=REGEXEXTRACT(...)*1
=A2--B2
Читать полностью…
Что мы делаем в нашем патреон чате?
К примеру, взяли ссылку на крипто-API https://apilist.tronscanapi.com/api/account/wallet?address=TSTVYwFDp7SBfZk7Hrz3tucwQVASyJdwC7&asset_type=1
И написали пользовательскую функцию, которая обращается к этой ссылке, приводит результат к нормальному виду и вставляет данные прямо в Таблицу.
🌊 (здесь про наш патреон-чат, условия и какие темы мы в нём обсуждаем)
📌 В комментариях будет код из скриншота.
Данные с разных листов, на которых разная структура
Что делать в более тяжелом случае, когда данные на разных листах и еще разбросаны как попало (все в разных столбцах; нужный столбец то левее, то правее столбца с ключом для поиска)?
Тут сложнее. Можно решить эту задачу так: через ИНДЕКС+ПОИСКПОЗ (INDEX + MATCH) вместо ВПР (про эту комбинацию мы уже писали), чтобы все работало при любом порядке столбцов.
Через СМЕЩ / OFFSET будем формировать ссылку на столбец для поиска и на столбец с нужными данными. Находить их будем по заголовкам (заголовок будет находиться через ПОИСКПОЗ / MATCH, и это будет использоваться в функции СМЕЩ / OFFSET для смещения по столбцам, чтобы попасть на нужный).
Вот ингредиенты нашего коктейля:INDIRECT("'"&название листа&"'!диапазон") - ссылка на ячейку или диапазон на нужном листе, с которого тянем данныеMATCH(заголовок;INDIRECT("'"&название листа&"'!диапазон");0) - поиск нужного нам заголовка (столбца, из которого нужно тянуть данные)ROWS(INDIRECT("'"&название листа&"'!A:A") - число строк на листе, с которого нужно тащить данные)OFFSET(INDIRECT("'"&название листа&"'!A1"); 0;MATCH (ищем заголовок, как выше) - 1; число строк, как выше;1) - ссылка на диапазон на нужном листе шириной 1 столбец со всеми строками, с отступом от A1 до нужного нам заголовка.
А логика формулы в общем виде такая:=ИНДЕКС(СМЕЩ(который дает ссылку на диапазон на нужном листе в нужном столбце, с которого нужно тащить данные;Таблица с примером
ПОИСКПОЗ(ключ для поиска;СМЕЩ(который дает ссылку на диапазон на нужном листе в столбце, в котором находятся ключи для поиска, например, названия товаров);0))
ВПР-им с разных листов
Если вам нужно "подтягивать" данные с помощью ВПР / VLOOKUP с разных листов (например, на каждый город/месяц/склад у вас отдельный лист с данными), можно собрать ссылку с помощью INDIRECT / ДВССЫЛ.
Обычная ссылка на другой лист выглядит так:
='Москва'!A:B
Нам нужно подставлять внутри апострофов названия разных листов.
Сначала берем апостроф (в кавычках), потом к нему добавляем название листа, справа еще один апостроф, восклицательный знак и диапазон:
="'" & ячейка с названием листа & "'!диапазон"
Чтобы превратить полученную текстовую строку в ссылку, используем функцию INDIRECT - она ровно для этого и используется.
=INDIRECT("'" & ячейка с названием листа & "'!диапазон")
И отправляем это внутрь ВПР'а как второй аргумент:
=VLOOKUP(значение для поиска; INDIRECT("'" & ячейка с названием листа & "'!диапазон"
) ; номер столбца ; 0)
Ссылка на таблицу с примером
Отбираем по ошибке #N/A строки в QUERY и FILTER
Друзья, иногда в наших датасетах может быть ошибка #N/A (not available или значение не доступно).
Показываем, как отобрать строки или значения с ошибкой и без:
1. Для QUERY, отбираем строки с N/A:=QUERY(A1:A12;"where Col1 = '#N/A'")
2. Для QUERY, отбираем строки без N/A:=QUERY(A1:A12;"where Col1 != '#N/A'")
3. Чтобы отобрать N/A в FILTER:=FILTER(A1:A12;ISNA(A1:A12))
4. Ну и отбираем всё, кроме N/A в FILTER:=FILTER(A1:A12;NOT(ISNA(A1:A12)))