Google Таблицы

Google Таблицы pinned Deleted message

​​Рассчитываемое поле в сводных таблицах

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

Давайте рассмотрим пару примеров. На скриншоте под текстом, слева - наши исходные данные, справа - сводная, которая получится в итоге.

Для начала создадим сводную таблицу, в строки добавим "Менеджер".
В значения - рассчитываемое поле (оно прячется в "добавить").
Рядом с параметром "Суммировать по" выберите Другое.

Теперь:
1. Посчитаем среднюю стоимость метра по менеджеру, формулой будет:
=sum('Сумма')/sum('Кв. м.') (где 'Сумма' и 'Кв. м.' – столбцы исходных данных).

2. Посчитаем сумму оплаченных квартир у каждого менеджера:
=SUMIFS('данные'!C2:C7;'данные'!D2:D7;"да";'данные'!A2:A7;'Менеджер')

Названия полей из исходных данных указываются в апострофах. Таблица с примером

Субботние ответы на ваши вопросы

Друзья, всем привет, ответы—по ссылке.

—Добавляем новую строку скриптом и вставляем в нее текущую дату
—ДВССЫЛ в QUERY и выпадающий список с выбором месяца

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

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

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

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

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

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

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

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

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

Итак,
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)))

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

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

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

У этой задачи много спобосов решения: 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)

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

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

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

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

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

https://shagabutdinov.ru/answers_02062018/

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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