Телеграмм чат группы google_spreadsheets

попробую

22:22пожаловаться #1

EN

к предыдущему вопросу: добавил =ARRAYFORMULA(IF($C$5:C<>"";ARRAYFORMULA(INDEX('Цены'!$K$3:K;MATCH(C5:C&E5:E&D5:E;'Цены'!$C$3:C&'Цены'!$D$3:D&'Цены'!$G$3:G;0)));"")), теперь формула растягивается на всю таблицу но значение остается по перой строке из списка

index не работает в формуле массива

22:47пожаловаться #2

EN

какая цель-то, что нужно сделать? что хочется получить в итоге?

22:47пожаловаться #3

EN

QUERY в массиве.
Найдем максимальное значение по каждой строке.

Друзья, привет! Сегодня будем расширять границы вашего понимания функции QUERY.

Задача звучит так - есть ряд строк с разным количеством значений в каждой. Нужно - найти максимум по каждой строке (или минимум или среднее или сумму) и сделать это с помощью одной формулы.

Концептуально решение такое - мы разворачиваем диапазон (TRANSPOSE) и с помощью формулы создаем текст запроса для QUERY, он обращается к каждому столбцу диапазона, получает максимум. Дальше переворачиваем наш массив обратно, попутно убирая заголовки.

Подробно и по шагам решение на скриншоте →

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

Итоговая формула:
=QUERY(TRANSPOSE(QUERY(TRANSPOSE(A2:C5);"select "&
ARRAYFORMULA(JOIN(",";"max(Col"&row(A2:A5)-1&")"))));"select Col2")

P.S. Неоценимую помощь в поиске решения оказал @Volond (он же @SigmaSpola). Спасибо ему🤘

Вопрос:
есть запрос:
=QUERY('Цены'!$B$3:$N;"SELECT K WHERE C='"&C5&"' AND G='"&D5&"' AND D='"&E5&"'")
который вытаскивает цену материала по трем условиям из листа Цены. В ARRAYFORMULA загнать запрос нельзя, чтобы не протаскивать по всем ячейкам таблицы.
Нашел вариант формулы =ARRAYFORMULA(INDEX('Цены'!$K$3:K;MATCH(C5:C&E5:E&D5:D;'Цены'!$C$3:C&'Цены'!$D$3:D&'Цены'!$G$3:G;0)))
Но он так же выдает одно значение и не идет по всем строкам таблицы дальше автоматически.

Как все таки сделать, чтобы в таблице автоматом к записям происходил поиск цены?

query в привычном смысле тоже не работает в формуле массива, но существуют вот такие варианты t.me/google_sheets/336

Google Таблицы

22:48пожаловаться #4

EN

к предыдущему вопросу: добавил =ARRAYFORMULA(IF($C$5:C<>"";ARRAYFORMULA(INDEX('Цены'!$K$3:K;MATCH(C5:C&E5:E&D5:E;'Цены'!$C$3:C&'Цены'!$D$3:D&'Цены'!$G$3:G;0)));"")), теперь формула растягивается на всю таблицу но значение остается по перой строке из списка

Кстати, ремарка - не обязательно несколько раз писать arrayformula, достаточно один раз обернуть на верхнем уровне.

23:02пожаловаться #5

MG

Eugeny Namokonov

какая цель-то, что нужно сделать? что хочется получить в итоге?

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

23:19пожаловаться #6

MG

QUERY в массиве.
Найдем максимальное значение по каждой строке.

Друзья, привет! Сегодня будем расширять границы вашего понимания функции QUERY.

Задача звучит так - есть ряд строк с разным количеством значений в каждой. Нужно - найти максимум по каждой строке (или минимум или среднее или сумму) и сделать это с помощью одной формулы.

Концептуально решение такое - мы разворачиваем диапазон (TRANSPOSE) и с помощью формулы создаем текст запроса для QUERY, он обращается к каждому столбцу диапазона, получает максимум. Дальше переворачиваем наш массив обратно, попутно убирая заголовки.

Подробно и по шагам решение на скриншоте →

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

Итоговая формула:
=QUERY(TRANSPOSE(QUERY(TRANSPOSE(A2:C5);"select "&
ARRAYFORMULA(JOIN(",";"max(Col"&row(A2:A5)-1&")"))));"select Col2")

P.S. Неоценимую помощь в поиске решения оказал @Volond (он же @SigmaSpola). Спасибо ему🤘

Eugeny Namokonov

query в привычном смысле тоже не работает в формуле массива, но существуют вот такие варианты t.me/google_sheets/336

Google Таблицы

Интересный вариант но не мой случай

23:19пожаловаться #7

EN

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

query + формулы массива на столбцы, которые нужно обработать (и которые выводятся с помощью query)

23:20пожаловаться #8

MG

Eugeny Namokonov

query + формулы массива на столбцы, которые нужно обработать (и которые выводятся с помощью query)

Хм. Query использую для формирования основной Таблицы, а что такое формулы массива на столбцы?

23:23пожаловаться #9

MG

Грубо говоря у меня есть таблица спецификаций с материалами, вида:
#Спецификации/Артикул/поставщик/кол-во/объект
И есть таблица цен, вида
#спецификации/артикул/поставщик.

Количество записей в таблицах разное, тк в первой есть разбиение по количеству материала на разные объекты.

И вот мне нужна таблица вида
#Спецификации/артикул/поставщик/сумма(кол-во)/цена/стоимость

Первые четыре колонки формируются запросом, пятая (цена) к каждой позиции идёт запросом из другого листа по трём параметрам(спец-ция,артикул,поставщик)

Как мне сделать так, чтобы колонка Цена формировалась без растягивания формулы с запросом на диапазон ?

Alexandr Bubenok in Чат | Google Таблицы и скрипты

23:31пожаловаться #10

AB

@namokonov Веду к вам клиентов из Британии. Парень пишет магистерскую просит помощи по таблицам

Alexandr Bubenok in Чат | Google Таблицы и скрипты

23:35пожаловаться #11

AB

Просил поделиться чатиками про таблицы)

23:39пожаловаться #12

EN

Alexandr Bubenok

@namokonov Веду к вам клиентов из Британии. Парень пишет магистерскую просит помощи по таблицам

Привет! Отлично, отлично

23:53пожаловаться #13

EN

Грубо говоря у меня есть таблица спецификаций с материалами, вида:
#Спецификации/Артикул/поставщик/кол-во/объект
И есть таблица цен, вида
#спецификации/артикул/поставщик.

Количество записей в таблицах разное, тк в первой есть разбиение по количеству материала на разные объекты.

И вот мне нужна таблица вида
#Спецификации/артикул/поставщик/сумма(кол-во)/цена/стоимость

Первые четыре колонки формируются запросом, пятая (цена) к каждой позиции идёт запросом из другого листа по трём параметрам(спец-ция,артикул,поставщик)

Как мне сделать так, чтобы колонка Цена формировалась без растягивания формулы с запросом на диапазон ?

Я бы делал просто много формул sumifs. Тут можно сделать формулу массива с sumif через объединение всех трех колонок с условием, но скорее всего это будет ощутимо тормозить.

23:57пожаловаться #14

EN

вот так (вероятно будет тормозить)

23:59пожаловаться #15

2019 January 27

EN

Можете попробовать, в прочем, потом нам расскажете, будет ли нормально работать и сколько было строк в источнике.

00:00пожаловаться #16

EN

سیروس in Чат | Google Таблицы и скрипты

00:03пожаловаться #17

س

Hi guys, I am not Russian and cannot read the instructions of your group!
I have got an urgent question that I have to deal with that! Among you is there anyone can suggest me a graph which would be beneficial to present the information of this table?

سیروس in Чат | Google Таблицы и скрипты

00:10пожаловаться #18

س

Переслано от سیروس

Hey, is there any Excel experts here that could give me a suggestion how to convert this table to a graph? to make it more interesting and easier to follow

سیروس in Чат | Google Таблицы и скрипты

00:10пожаловаться #19

س

سیروس

Переслано от سیروس

Hey, is there any Excel experts here that could give me a suggestion how to convert this table to a graph? to make it more interesting and easier to follow

I am talking about this table, I wanna make a nice graph out of it! But it's hard for me as it has couple of groups if info, name of services which is basically and special, time and cost