Добрый день, друзья. Сегодня два небольших ответа на ваши вопросы.
-  Короткий скрипт, короткий будет автоматически переходить к последней строке при открытии файла (чтобы не пролистывать)
- 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. А в этом посте мы писали о том, как отобрать по пустым ячейкам, по непустым, по ячейкам с ""

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

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

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

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

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

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

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

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

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

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

​​Google Форма + Google Таблица для проведения тестирования.

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

​​Выделяем месяц из даты

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

1. Функция =TEXT(a1;"mmmm"), где a1—дата, вернет: Июня, Июля, Августа и т.д. Функция изменит только форматирование (отображение) вашей даты, вы увидите месяц и он будет в родительном падеже. Сама же дата останется прежней и эту ячейку вы сможете использовать в формулах.

2. Для извлечение месяца в любом падеже нам нужны будут INDEX и MONTH, дата при этом превратится в текстовую строку.

Внутри диапазона INDEX задаем массив с "правильными названиями":
=index({"ЯНВАРЬ"\"ФЕВРАЛЬ"\"МАРТ"\"АПРЕЛЬ"\"МАЙ"\"ИЮНЬ"\"ИЮЛЬ"\"АВГУСТ"\"СЕНТЯБРЬ"\"ОКТЯБРЬ"\"НОЯБРЬ"\"ДЕКАБРЬ"})

Вторым аргументом будет функция MONTH(дата), она возвращает порядковый номер месяца (1-12) и с помощью нее же мы вернем из массива нужный нам элемент, формула целиком:
=index({"ЯНВАРЬ"\"ФЕВРАЛЬ"\"МАРТ"\"АПРЕЛЬ"\"МАЙ"\"ИЮНЬ"\"ИЮЛЬ"\"АВГУСТ"\"СЕНТЯБРЬ"\"ОКТЯБРЬ"\"НОЯБРЬ"\"ДЕКАБРЬ"};month(A2))

​​Как проставить номера всех столбцов на листе?

Хочется видеть номера столбцов в таблице или же они нужны, чтобы легче прописать запрос в QUERY из другого файла (ведь, как мы неоднократно обсуждали, при запросе к другому  файлу или к нескольким диапазонам в QUERY нужно писать Col1, Col4, а не A, C)
Как быть?

Простая формула массива:
=ARRAYFORMULA(COLUMN(1:1))
=ARRAYFORMULA(СТОЛБЕЦ(1:1))

Вводите ее в A1 и во всех ячейках первой строки будут номера столбцов.
При этом можно удалять и добавлять столбцы - всегда автоматически будут номера во всех из них.
Конечно, такую же операцию можно и со строками провести (функция ROW/СТРОКА)

P.S. И два субботних ответа на ваши вопросы.

​​Условное форматирование. Выделяем дубликаты и не только.

Друзья, привет! Памятка с формулами условного форматирования, с помощью которых можно выделить:
—дубликаты
—дубликаты, с проверкой по нескольким столбцам
—дубликаты, начиная со второго, третьего и т.д

Для создания правила условного форматирования:
1) выделите диапазон
2) формат > условное форматирование
3) "добавить правило", "ваша формула" и вставьте соответствующую формулу из скриншота ниже

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

P.S. Чтобы формулы работали - переносите их со скриншота в точности, со всеми $

​​Простая CRM-система на Google Формах для постановки и контроля выполнения заданий

Друзья, привет! Наш читатель, Александр @aleprozorov поделился CRM-системой, которую он внедрил у себя на производстве.

Задания, статусы и комментарии создаются заполнением соответствующей Google Формы (например, Форма "Новое задание")

А Google Таблицы используются только для агрегации данных "Таблица Общая база", лист "общие задания" и для визуализации заданий для исполнителей, "Таблица Сотрудника".

Чтобы понять, как все работает — смотрите майнд-карту, которую мы сделали и читайте описание системы от Александра.

​​Google Форма с автоматическим добавлением вариантов ответа

Привет, друзья! В сегодняшней статье расскажем о том, как создать Google Форму для проведения опроса.

И Google Форму не простую, а с автоматическим добавлением вариантов ответа, введенных в “другое”.

Заодно покажем, как с помощью одной формулы в Таблице можно будет построить рейтинг ответов c сортировкой.

​​Скрипт, запрещающий редактирование строки при незаполненных ячейках строки выше

Друзья, привет! Сегодня вопрос из нашего чата: нашей читательнице Алене понадобился скрипт, который запрещает редактирование строки (и удаляет введенное), если выбранные ячейки в строке выше не заполнены.

Алена предложила свой вариант скрипта. Мы его немного модифицировали и выкладываем результат для вас.

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

function onEdit(e){
 var wsData = e.source.getActiveSheet();
 var celEdit = e.range;
 var intRow = celEdit.getRow();
 
 if(intRow > 2 && celEdit.getValue!=''){
   var row = wsData.getRange(intRow-1, 1, 1, wsData.getMaxColumns()).getValues()[0];
   var checkCols = [0, 2, 4]; // Какие ячейки проверяем, 0 = столбец A
   
   var checker = checkBuilder(checkCols);
   var isPass = row.filter(checker).length == 0;
   
   if(!isPass){
     SpreadsheetApp.getUi().alert('Вася, внеси все данные в обязательные для заполнения ячейки');
     celEdit.clearContent();
   }
 }
}

var checkBuilder = function(cols){
 return function(el, i){
   return cols.indexOf(i) > -1 && el == '';
 }
};

Алена — спасибо за отличную идею для поста!

​​Считаем, сколько раз символ встречается в строке

Привет, друзья! Наша цель—подсчитать, сколько раз в каждой ячейке диапазона встречается буква "а".

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

Дальше с помощью ДЛСТР() посчитаем количество символов в исходной строке и количество символов в той же строке, но без искомого символа. И вычтем одно из другого.


Итоговая формула:
=длстр(a4)-длстр(подставить(a4;"а";""))

P.S. Чтобы учесть прописные:
=длстр(a4)-длстр(подставить(строчн(a4);"а";""))

Таблица с примером | Оглавление канала

​​Выводим ряд чисел или дат. Одной формулой.

Привет, друзья! Сегодня мы покажем, как с помощью функции СТРОКА() / ROW() вывести ряд дат или чисел с любым шагом.

1. Числа от 5 до 20
=ArrayFormula(СТРОКА(A5:A20))

Разберем функцию по частям:
СТРОКА(A5:A20) возвращает массив {5, 6, 7 ... 20}.
ArrayFormula превращает формулу в функцию массива, без нее выведется только первое значение (5).

2. Текущая дата + 60 дней
=ArrayFormula(СТРОКА(A1:A60)+TODAY()-1)

Добавляем вчерашнюю дату today()-1 к массиву {1, 2, 3 .. 60}.

3. Числа от 1 до 10, шаг 0,5
=ArrayFormula(СТРОКА(A2:A20)*0,5)

Умножаем массив {2, 3, 4 … 20} на 0,5. Получается диапазон {1, 1.5 ... 10}.

Таблица с примером | Оглавление канала | Чат

​​Оглавление канала | Чат | Наша книга| Донаты
Заказы, консультации: @namokonov, @renat_shagabutdinov

Новые возможности Google Таблиц

В конце июля прошла конференция Google Next cloud, на которой в числе прочего анонсировали и новшества в Google Таблицах:

—5 миллионов ячеек в документе
—Срезы (Slicers)
—Улучшения графиков
—Сводные таблицы
—Интеграция с BigQuery и другими сервисами
—Еще новшества одной строкой

Предлагаем вам перевод статьи с описанием нововведений.