​​Друзья, доброе утро! Сегодня рассматриваем функцию QUERY вложенную в QUERY.

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

С помощью первого QUERY мы умножим стоимость часа сотрудника на кол-во часов (по каждой строке). А с помощью второго полученный массив сгруппируем по проекту и сотруднику. Все действия уместим в одну формулу.

Статья со скриншотами в Medium.

P.S. В ближайшее время мы перенесем в Medium все наши статьи из Telegraph, который сейчас не у всех доступен.

​​В сегодняшних ответах мы расскажем,
- как найти кнопку поиска в таблицах
- как искать значение в интервалах в ПРОСМОТР(LOOKUP), не описывая каждое возможное положение
- а еще про связанные выпадающие списки и про Google Формы

Недавно в Таблицах прошло большое обновление. Наш сегодняшний пост - про него.

В статье в Medium детально разберем:
- группировку строк и столбцов (Ура, она появилась!)
- группировку в сводных таблицах
- флажки и как их использовать в функциях
- и покажем гифку с созданием макроса

​​ВПР по нескольким условиям.

Друзья, сегодня рассказываем про ВПР с мультиусловиями. По сути - это привычная вам функция ВПР, в которой нужно принудительно объединить и условия и диапазоны для поиска условий. В статье Medium - подробно и со скриншотами.

P.S. в конце статьи покажем более простую альтернативу

​​Друзья, сегодня у нас подробная статья в Medium, а в ней:

1. Импортируем турнирную таблицу РФПЛ из веб-страницы (=IMPORTHTML)
2. При импорте "на лету" оставим только нужные строки и столбцы (=QUERY)
3. Опубликуем результат как веб-страницу

​​Новая функция AVERAGE.WEIGHTED - средневзвешенное значение

Мы не отследили наверняка, но, кажется, функция AVERAGE.WEIGHTED добавилась совсем недавно, в рамках апрельских обновлений (о самых существенных мы писали здесь)

Теперь можно рассчитать средневзвешенное значение без промежуточных вычислений.
Например, чтобы получить средневзвешенную цену, раньше нужно было сначала получить стоимость (перемножить массивы цен и объема продаж с помощью СУММПРОИЗВ/SUMPRODUCT) и разделить на количество товаров (обычная СУММ/SUM):
=СУММПРОИЗВ(B2:B13;C2:C13)/СУММ(B2:B13)

А теперь достаточно воспользоваться одной функцией:
=AVERAGE.WEIGHTED(C2:C13;B2:B13)

​​Друзья, сегодня у нас два вопроса - один нам пришлось уточнить, а в другом случае получилось решить несложную, но любопытную задачу по аккуратному сцеплению текста в одной ячейке с переходами на новые строки . В качестве орудия пользовались замечательной функцией СМЕЩ, формирующей ссылку на динамический диапазон. Наш вестник.

​​Друзья, привет. Привет, друзья.

Тема, навеянная нашим чатом. Как запретить вводить формулы в ячейки?

Отвечаем:
Выделяете ячейки > "проверка данных" > "ваша формула" > =not(isformula(верхняя-левая ячейка выбранного диапазона) и "запрещать ввод данных". Все, теперь формулу не ввести. Чтобы разрешить ввод только формул - убираем not.

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

Друзья, сегодня делимся с вами еще одним роликом из онлайн-курса по Таблицам в SkillFactory.

"Условное форматирование и логические функции. Как выделять строки по одному или нескольким условиям"
https://www.youtube.com/watch?v=1O6SWDDsVZk

Коллеги, доброе утро. В сегодняшнем видео добавляем к изображению товара ссылку на него. И изображение и ссылку вставляем функциями (IMAGE + HYPERLINK).

​​Привет, друзья. Недавно в нашем чате обсуждался любопытный кейс: потребовалось разделить имя и фамилию, введенные в один столбец без пробела.

Давайте решать. Сначала достанем имя функцией REXEXEXTRACT (ф., извлекающая часть текста, соответствующую рег. выражению).

=REGEXEXTRACT(A2;"[А-Я][а-я]+")
Расшифровка: извлекаем имя, т.е. первую прописную букву - [А-Я] и любое кол-во строчных после - [а-я]+

=SUBSTITUTE(A2;B2;"") / =ПОДСТАВИТЬ(A2;B2;"")
И с помощью функции "=подставить" меняем в нашей строке имя, полученное выше на "" (пустоту). Остается фамилия.

​​Друзья, в сегодняшнем посте:

1. сделаем автоматическую сквозную нумерацию работ в смете

2. сделаем условное форматирование, чтобы строки с заголовками разделов заливались серым цветом автоматически

Добрый день, друзья. Сегодня два небольших ответа на ваши вопросы.
- Короткий скрипт, короткий будет автоматически переходить к последней строке при открытии файла (чтобы не пролистывать)
- FILTER по диапазону условий

Желаем всем хороших выходных!

https://shagabutdinov.ru/answers_02062018/

Друзья, доброе утро.

Сегодня отвечаем на вопрос из нашего чата: отберем уникальные поля из одного столбца по условию в другом столбце.

Отбирать будем разными способами: FILTER / QUERY / функция массива + IF.

​​Доброе утро.

Достаем из таблицы два поля: наибольшую дату и имя кассира из строчки с этой датой.

У этой задачи много спобосов решения: MINIFS или ПОИСКПОЗ + СМЕЩ (или ИНДЕКС) или ВПР, FILTER, QUERY...

Проще всего, на наш взгляд, воспользоваться функцией FILTER. Все, что нужно помнить о функции - первый диапазон в синтаксисе - диапазон вывода, дальше идут диапазоны условия и сами условия.

1) Если столбцы "дата" и "кассир" стоят рядом (J5:K7)
=FILTER(J5:K7; J5:J7 = MAX(J5:J7)

2) Столбцы не рядом, создаем массив и объединяем два нужных диапазона {\}
=FILTER({J11:J13 \ L11:L13};J11:J13=MAX(J11:J13))

P.S.
- чтобы вывести минимальную дату - меняем в условии MAX на MIN
- для второй наименьшей или третьей наибольшей даты - меняем условие на
=НАИМЕНЬШИЙ(J5:J7;2); =НАИБОЛЬШИЙ(J5:J7;3)

​​Друзья, привет. Сегодня у нас выпадающий список с "одноразовыми" значениями. После использования они будут исчезать из списка.

Итак,
1) в A4:A - все исходные варианты;
2) в B4:B - загружаем формулой то, что уже было выбрано;
3) в C4:4 - магия, оставляем разницу списков A4:A и B4:B, этот же диапазон используется для создания выпадающего списка.

Формулы,
2) B4: =FILTER(F4:F;F4:F<>"")
3) C4: =FILTER(A4:A7;ISNA(MATCH(A4:A7;B4:B;0)))

Таблица с примером.

​​Друзья, сводная таблица - лучше кучи формул.

Например, написав много разных формул вы сможете отобрать имена менеджеров, добавить к каждому его отдел, сумму продажу и дату последней сделки:

​​Но абсолютно такого же результата вам позволит добиться одна сводная таблица. Или в простых случаях - одна функция QUERY.

Таблица с примером

​​Друзья, публикуем памятку по работе с условиями в формулах FILTER, QUERY, SUMIF(и аналогах).

Теперь вы всегда будете знать, как отобрать по дате в QUERY, что писать в кавычках, а что нет и где нужен амперсанд (&).

Таблица с примером

P.S. А в этом посте мы писали о том, как отобрать по пустым ячейкам, по непустым, по ячейкам с ""