10-ый выпуск субботних ответов! (20 января 2018)

Спасибо создателям канала за отличный контент! Он мне помогает изучать таблицы вследствие чего работать более эффективно. Назрела такая проблема: есть столбец куда заказчики добавляют коды запчастей, вида 8bz033, 8ca024, pfw897-w28... Буквы в кодах должны быть исключительно латинские, иначе сайт их не распознает, нужно решение как автоматически заменять все кириллические буквы на их латинские аналоги: С кириллицей на латинскую C, А на A и т.д. Буду благодарен за помощь :)

Спасибо, мы очень рады, что наш контент помогает.

Сначала самое простое решение вашего вопроса: обернуть каждое значение во множество ПОДСТАВИТЬ (SUBSTITUTE), каждая вложенная формула будет заменять кириллическую букву на латинскую, а еще служить диапазоном данных для следующей формулы.

А теперь более продвинутое решение:

Если у вас есть таблица соответствия - в первом столбце кириллица, во втором латиница, то можно все решить формулой.

Она будет разбивать код на символы, искать соответствия для каждого через ВПР и заменять на латиницу, если найдет (а если не найдет - оставлять тот символ, что был) - и заново склеивать символы в код.

=Join("";arrayformula(ЕСЛИОШИБКА(ВПР(ПСТР(A2;{1\2\3\4\5\6};1);$D$2:$E$4;2;0);ПСТР(A2;{1\2\3\4\5\6};1))))

Она будет работать, если вы знаете максимальную длину кода.

Пример будет работать для кодов из 6 и более символов.

Если их максимум 8 - то надо заменить {1\2\3\4\5\6} на {1\2\3\4\5\6\7\8}.

ПСТР/MID разбивает текст на отдельные символы - от первого и до шестого (аргумент - массив с цифрами от 1 до 6 - это порядковый номер вырезаемого символа), затем каждый из них с помощью ВПР/VLOOKUP ищется в Таблице соответствия, а ЕСЛИ возникает ОШИБКА - то оставляется исходный символ с помощью той же ПСТР.

JOIN объединяет символы обратно в один код, разделитель - ничего (пустые кавычки, "" - это первый аргумент этой функции).

Файл с примером

Как можно упростить формулу в столбце С:C.

Цель: Есть список дат, нужно в столбце рядом с датой написать в какую неделю входит эта дата.

Вот так, например:

Дешифруем:

1) IF(WEEKDAY(A2;2)=1 - определяет, понедельнику ли соответствует дата в столбце А соответствующей строки

2) Если понедельнику, то берем эту дату, форматируем с помощью функции TEXT (dd - день, mm - месяц, yy - год), добавляем тире, с помощью амперсанда (&) добавляем эту же дату еще раз, прибавляя к ней 6 дней и форматируя.

Без функции TEXT было бы так:

3) При любых других днях недели, функция IF вернет составленную строку из предыдущей строки. То есть, там могут оказаться только строки составленные из даты с понедельником + 6 дней.

Таблицу можно посмотреть здесь.

Вопрос про формирование графика. Допустим, есть простая табличка с такими данными:

Дата Сумма + Сумма -

01.03.2017 1000 -200

04.03.2017 300

05.05.2017 -100

То есть каждый день отмечается + или - или оба.

Требуется сделать график баланса помесячно. То есть общая сумма за месяц а не по дням. Что-то вроде такого:

На сколько я понял потребуется делать промежуточные таблицы заполненные через query по месяцам и из них уже просто сделать обычный чарт? Быть может у Вас есть пример готовый? Спасибо.

Промежуточную таблицу нужно делать (верно, с помощью QUERY), если вы хотите строить график (диаграмму).

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

Таблица в E8 - это обычная функция QUERY, которая выдает баланс по месяцам:

=QUERY(A1:C;"select sum(B)-sum(C) group by month(A)")

А в ячейке E3 она вставлена как аргумент в функцию SPARKLINE, благодаря чему сразу без предварительного вывода данных в ячейке рисуется столбчатая диаграмма. 

=sparkline(QUERY(A1:C;"select sum(B)-sum(C) group by month(A)");{"charttype" \ "column"})

Файл с примером - по ссылке:

Доброго времени суток, появилась необходимость создания автоматических критериальных отчётов и в обычном excel задачу выполняла известная функция vlookup-2, которая позволяла по ряду критериев найти все значения удовлетворяющие значения и выдать по итогу отдельный перечень! Существует ли в google sheets подобные функции или правильно сказать написаны надстройки , так как код для функции на vba естественно не подходит к js!? Спасибо

Здравствуйте,

Если я вас правильно понял, то вам поможет функция FILTER. Одна формула выводит сразу весь подходящий под условия диапазон (улучшенный аналог формулы массива в MS Excel):

Если вам нужно взять ряд критериев (больше одного) с листа и использовать его для отбора в одном условии, то тут тоже поможет FILTER, этот кейс мы разбирали здесь.

Скажите, нет ли скрипта,который позволяет синхронизировать гугл-календарь с таблицами; например, выбор по трем столбцам: (1)менеджер проекта - Павел (соответственно календарь Павла), (2)название проекта и (3)дата реализации, т.е. когда подходит дата реализации, у Павла в календаре создается событие-напоминалка. Спасибо.

У нас такого скрипта нет, попробуйте поискать в интернете похожий и адаптировать, если не найдете - напишите нам: @namokonov, @renat_shagabutdinov, можем сделать на заказ.