Ответы на вопросы 16.12.2017

Здравствуйте, меня интересует, возможно ли использовать FILTER как аргумент функции IMPORTRANGE, т.е. отображать в одной таблице выборочные данные по значению в каком-то столбце, не открывая просматривающему весь диапазон, импортируемый из исходной таблицы

Да, можно. Только наоборот - IMPORTRANGE будет аргументом (по сути, диапазоном). И удобнее это сделать с QUERY, а не с FILTER. Детали ниже.

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

Если нужно одному человеку предоставить все данные только по "Бизнесу", создайте файл, дайте ему доступ на просмотр, а в файле используйте такую замысловатую функцию FILTER.

=FILTER(IMPORTRANGE(ссылка на файл;"A:D");IMPORTRANGE(;"B:B")="Бизнес")

с ссылкой выглядит так:

=FILTER(IMPORTRANGE("https://docs.google.com/spreadsheets/d/16aKQAIGtLKwQFjWyUGraKAVPQe6cJucYAHoIc-AEEc4";"A:D");IMPORTRANGE("https://docs.google.com/spreadsheets/d/16aKQAIGtLKwQFjWyUGraKAVPQe6cJucYAHoIc-AEEc4";"B:B")="Бизнес")

Результат будет без заголовков:

Конечно, можно их прицепить с помощью еще одного IMPORTRANGE, который будет уже третьим в этой страшной формуле. 

Но лучше воспользоваться функцией QUERY - выйдет элегантнее.

=QUERY(IMPORTRANGE(ссылка на файл;"A:D");"SELECT * WHERE Col2 = 'Бизнес'";1)

или с ссылкой:

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/16aKQAIGtLKwQFjWyUGraKAVPQe6cJucYAHoIc-AEEc4";"A:D");"SELECT * WHERE Col2 = 'Бизнес'";1)

Обратите внимание, что в QUERY с IMPORTRANGE в качестве аргумента столбцы обозначаются как ColN, а не как A/B/C/...

Результат:

Здравствуйте! Как суммировать значения ячеек в столбце, пропуская отдельные ячейки? На скриншоте нужно посчитать сумму для Вани и сумму для Пети на основании значения в первом столбце (имени)

Нужно воспользоваться функцией СУММЕСЛИ (SUMIF).

В вашем случае выглядеть она будет так:

=СУММЕСЛИ(диапазон с именами; имя; диапазон с числами)

пример, в котором мы подсчитываем сумму по двум тематикам (таблица та же, что в предыдущем вопросе):

Пожалуйста подскажите как посредством QUERY получить только значение без наименования аргумента. Пример QUERY :: =query(Tickets!A:AD;"select count(A) where S='closed' AND M LIKE '%"&B6&"%'";0) Сейчас, в той ячейке где query - выводится наименование атрибута, а ячейкой ниже значение. Выглядит это так: count 4532 Спасибо.

Первое, что приходит в голову - воспользоваться функцией ИНДЕКС (INDEX):

=ИНДЕКС(query(диапазон;запрос);2)

(третий аргумент QUERY - заголовки - здесь значения не имеет, его можно опустить)

В примере с той же нашей таблицей с книгами без ИНДЕКСа получается так:

А с ИНДЕКСом - то, что нужно:

Альтернативный вариант - убрать название у поля с помощью LABEL внутри запроса QUERY.

Эта кляуза позволяет переименовывать поля в QUERY. Можно задать и пустое имя - '' (два апострофа)

Пример:

=query(A:D;"select count(A) where B='Научпоп' AND D > 200 LABEL count(A) ''")

Есть поле {а;б;в;г;д;а;е;в;ж;з} как задать конкретный порядок сортировки например вверху {ж;г;а;а} (потому что эти данные не меняются и они нам заведомо известны) и под ними но в одной выдаче все остальное [А-Яа-я] {б;в;в;д;е;з} - как это сделать запросом QUERY? Приходила мысль сделать введением дополнительного столбца где {ж;г;а;а} = 0, остальное {б;в;в;д;е;з}=1 потом по данному столбцу сделать order by. Все что пришло в голову использовать IF или ВПР но они работают с ячейкой но не диапазоном.

Отличный вопрос! Предлагаем следующее решение. Сначала с помощью функции FILTER добавляйте значения в нужном вам порядке и объединяете всё в массив {;}. Формула будет такой:

={FILTER(A4:A13;A4:A13=G1);FILTER(A4:A13;A4:A13=F1);FILTER(A4:A13;A4:A13=E1)}

Далее, напишите формулу FILTER, которая выведет весь список из А4:А13 за исключением значений из E1:G1 (как создать такую формулу мы писали здесь)

И отсортируйте результат, добавив SORT. Формула целиком:

=SORT(FILTER(A4:A13;ISERROR(MATCH(A4:A13;E1:G1;0))))

Далее объединяете обе части формулы: