Приглашаем на вебинар-демонстрацию возможностей Мегаплана 31 января

Лучше один раз увидеть Мегаплан в деле. На вебинаре покажем возможности сервиса и расскажем, как он поможет вашей компании

Регистрируйтесьна наш вебинар >>

Реклама. ООО "МЕГАПЛАН". ИНН 7725681416.

#реклама

Читать полностью…

121 из 200!

Друзья, доброе утро, а давайте поднажмем немного, нам не хватает 79 бустов. Пожалуйста!

Обещаем, что сторис будем создавать только по делу (ну либо если придумаем очень хорошую шутку 🙈)

/channel/google_sheets?boost

❤️

Читать полностью…

UTM-метки. Извлекаем все параметры и их значения формулой

Справедливый комментарий к прошлому посту: удобнее получать отдельные параметры (source, medium, etc.), а не все метки разом.

Можно так:
1. Взять все из ссылки после знака вопроса (с помощью комбинации текстовых функций или INDEX+SPLIT или REGEXEXTRACT — в примере последний вариант). Получится хвост ссылки с метками, где параметры и их значения разделены знаком "равно" (=), а пары разделены амперсандом (&).

2. Разделить их на пары по амперсанду с помощью SPLIT .

3. Транспонировать, чтобы в итоговом результате было два столбца — с параметрами и значениями. Функция TRANSPOSE.

4. Разделить по знаку "равно" с помощью SPLIT. Засунуть ее в ArrayFormula, ибо иначе получим результат только для первой пары.

Есть идеи по сокращению и оптимизации формулы? Добро пожаловать в комменты!

=ArrayFormula(SPLIT(TRANSPOSE(SPLIT(REGEXEXTRACT(A2;"\?(\S+)");"&"));"="))

Читать полностью…

Достаём курс евро из Московской Биржи.

Друзья, привет, Михаил поделился ссылкой, по которой можно вытянуть текущий курс евро к рублю из MOEX:

https://iss.moex.com/iss/engines/currency/markets/selt/boards/CETS/securities/EUR_RUB__TOM.json?iss.only=securities,marketdata&securities.columns=PREVDATE,PREVPRICE&marketdata.columns=TIME,LAST

Что с этой ссылкой делать дальше? Доставать данные скриптом в Таблицу, например:

function eurRub() {
  const url = 'https://iss.moex.com/iss/engines/currency/markets/selt/boards/CETS/securities/EUR_RUB__TOM.json?iss.only=securities,marketdata&securities.columns=PREVDATE,PREVPRICE&marketdata.columns=TIME,LAST'
  
const r = JSON.parse(UrlFetchApp.fetch(url));
  const curr = r.marketdata.data[0][1] || r.securities.data[0][1];
  SpreadsheetApp.getActive()
    .getRange("лист5!a1")
    .setValue(curr);
};

Наш скрипт достанет либо цену последней сделки, либо, если её нет, например, не было торгов - последнюю актуальную цену.

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

Читать полностью…

Создаём QR-код в Таблице и Документе

Друзья, привет! В Таблицах QR-код создаётся формулой, которую нужно ввести в ячейку, как это сделать, мы рассказывали вот здесь: /channel/google_sheets/912

А чтобы добавить QR в Google Документ нужен скрипт, например тот, который будет ниже.

Что скрипт делает:
1) ищет в тексте документа "###"
2) находит и вставляет вместо "###" QR-код, созданный из ссылки t.me/google_sheets
3) изменяет размеры изображения на 75 и 75 пикселей
4) и добавляет к самой картинке исходную ссылку

function createQRCode() {
  var Doc = DocumentApp.getActiveDocument();
  var totalElements = Doc.getNumChildren();

  for (var j = 0; j < totalElements; ++j) {
    var element = Doc.getChild(j);
    el = element.getText();

    if (el == '###') {
      element.removeFromParent();

      var url = 't.me/google_sheets';
      var blob = UrlFetchApp.fetch("https://chart.googleapis.com/chart?cht=qr&amp;chs=300x300&amp;chl=" + encodeURIComponent(url)).getBlob();

      Doc.insertImage(j, blob)
        .setHeight(75).setWidth(75)
        .setLinkUrl(url)
    };
  };

PS Конечно, вы можете собрать ссылку из того, что есть в документе, например, искать с помощью регулярного выражения номер договора, соединять его с "ссылкой/" в коде и из этого формировать QR-код.

Документ со скриптом

Читать полностью…

Суммируем каждую N-ую ячейку

Допустим, надо суммировать только каждый N столбец.

Можно использовать такую формулу:

=SUMPRODUCT((MOD(COLUMN(диапазон);N)=0)*(диапазон))

Здесь мы проверяем, нулевой ли остаток при делении номера столбца на N — если нулевой, значит ,столбец нам нужен, и вся следующая конструкция:

(MOD(COLUMN(диапазон);N)=0)

будет в таком случае возвращать TRUE (1).
А для ненужных нам столбцов FALSE (0).
И получим мы на выходе массив {FALSE, TRUE, ...}
который умножим на наш диапазон. SUMPRODUCT потом все это дело просуммирует.

В нашем примере каждый четвертый столбец просуммируем так:

=SUMPRODUCT((MOD(COLUMN(C2:R2)-2;4)=0)*(C2:R2))

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

Читать полностью…

Превращаем дату-как-текст в дату

Для этого есть целая функция — 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

Регистрируйтесь, чтобы попасть на вебинар

#реклама

Читать полностью…

Друзья, нам осталось всего 17 голосов, поднажмём?

Читать полностью…

Эх, дороги..

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

Задача от классной гитарной школы Guitardo

1) есть первая точка (долгота и широта)
2) есть еще сто точек, также с долготой и широтой

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

Как решили

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

Таблица со скриптом

Читать полностью…

Вытаскиваем utm из ссылки (а точнее — все, что после знака вопроса)

Для этого можно воспользоваться следующим регулярным выражением:
\?(\S+)

Извлекаем выражение (круглые скобки) из не-пробелов, идущее после знака вопроса (\? = знак вопроса, а без слеша это был бы квантификатор (ноль или один символ)

Вся функция будет такая:
=REGEXEXTRACT(A2;"\?(\S+)")

Хорошее про регулярные выражения:
Компактная памятка про регулярки от Vitalich
Извлекаем числа, едим пончики
Волшебство "найти и заменить". Приводим mm-dd к dd-mm

Читать полностью…

🤯 Каждый день рутина — офис, таблицы, отчёты. Ещё и платят мало. В то же время 1С-разработчики работают на удалёнке и получают до 300 000 р.

Самое время стать одним из них!

Чтобы освоить 1С, не требуется опыт в IT и знания английского! На онлайн-курсе «Профессия 1С-разработчик» от Skillbox вы всего за 6 месяцев научитесь:

автоматизировать бизнес-процессы;
проектировать архитектуру баз данных;
разрабатывать конфигурации и системы учёта;
интегрировать «1С» с другими системами.

В конце курса вы получите свидетельство 1C и помощь в трудоустройстве.

Где вы сможете работать:

💰 1С используют более 12 000 компаний, среди которых Лукойл, Сибур и Газпром.
🌴 Есть удалёнка и фриланс.

Открываем бесплатный доступ к первым 5 модулям. Вы учитесь совершенно бесплатно и решаете, стоит ли продолжать: https://epic.st/Bl3-LS

☃️🎁Начните год с новых знаний! Новогодняя скидка до 60% и второй курс в подарок!


Реклама. ЧОУ ДПО «Образовательные технологии «Скилбокс (Коробка навыков)», ИНН: 9704088880

Читать полностью…

Интересуетесь бизнес-аналитикой? Хотите узнать подробнее о профессии и её перспективах? Получить практические навыки?

Если на все вопросы вы ответили «да», тогда стоит скорее записаться на мини-курс «Бизнес-аналитик за 4 дня: быстрый старт в профессии»: https://epic.st/kMP_PZ

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

🎁 Участников ждут крутые подарки!

— Год бесплатного изучения английского языка
— Персональная карьерная консультация
— Сертификат на скидку 10 000 рублей на любой курс
— 5 полезных чек-листов

Реклама. ЧОУ ДПО «Образовательные технологии «Скилбокс (Коробка навыков)», ИНН: 9704088880

#реклама

Читать полностью…

🟢В Системе Финансовый директор появился новый мастер-класс о работе с формулами в 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();
  };
};

Таблица с ёлкой

Друзья, с наступающим новым годом, берегите себя, встречаемся здесь же в 2024!

PS А еще у старожила нашего сообщества Каната сегодня день рождения, поздравляем! 🌟

Читать полностью…

Немного формульно-датового многоэтажного ада под конец года. Выдаем одной формулой все недели года в формате "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 (что ищем; в каком столбце ищем; из какого столбца забираем; [на что заменяем ошибку]; [ищем текст/число/текст с подстановочными символами] ; [ищем сверху или снизу])

— VLOOKUP ищет только в первом столбце таблицы, а XLOOKUP ссылается на отдельные столбцы (где ищем и откуда возвращаем данные) — ей все равно, какая структура данных. Соответственно, VLOOKUP слетит, если вставить столбец между первым и тем, откуда возвращаем данные (потому что номер возвращаемого столбца зашит как константа). А XLOOKUP, ссылающаяся на столбцы, будет работать при вставке новых столбцов. И может возвращать данные, которые левее, чем столбец для поиска.

— XLOOKUP по умолчанию ищет текст (точное совпадение), а VLOOKUP — ближайшее наименьшее число.

— В режиме поиска числа (пятый аргумент, равный единице или минус единице) XLOOKUP не требует сортировки данных и умеет искать и ближайшее наибольшее тоже;

— У XLOOKUP есть отдельный необязательный (четвертый) аргумент для замены ошибок (когда ничего не найдено) на другое значение. А в случае VLOOKUP для этого дела надо добавлять отдельную функцию IFNA.

— VLOOKUP умеет работать с символами подстановки (* и ?) по умолчанию, а XLOOKUP — нет. Чтобы использовать символы подстановки в XLOOKUP, нужно задать пятый аргумент match_mode равным 2 (по умолчанию 0 - точный поиск).

— VLOOKUP умеет только вертикально (столбцы), для горизонтального поиска используется HLOOKUP / ГПР. XLOOKUP может работать и со строками, и со столбцами.

— VLOOKUP всегда ищет сверху вниз (то есть при 2 и более совпадениях найдет первое), а XLOOKUP умеет и снизу вверх (то есть найдет последнее) - для этого задаем последний аргумент search_mode равным -1.

— В Excel XLOOKUP есть только в 2021 / 365, то есть при скачивании таблицы в формате XLSX функция не будет работать в 2019 и более ранних версиях Excel.

___
Подборка постов про VLOOKUP
Поиск последнего значения с помощью XLOOKUP

Читать полностью…