Спрашивай! (7 x суббота, 23 декабря 17)
Google Таблицы - это непростоДобрый вечер, можно ли работать с функцией query с диапазонами , которые имеют разную структуру, например на одном листе данные дата-фио-знач1 -знач2-знач3-знач4-итого1, на втором дата-фио-знач5-знач6-знач7-итого2, а вывести надо относительно фио и даты среднее по итого1 и итого2 отдельными столицами соответственно
Добрый день! Да, это вполне реально, если вы объедините две таблицы в один массив правильно,
Разберем объединение:
1) (фигурная скобка для создания массива) {
2) (первый диапазон для объединения) A1:B3
3) \
4) (следующий диапазон для объединения) G1:G3
5) (фигурная скобка, закрывающая массив) }
Формула целиком,
={A1:B3\G1:G3}
Тоже самое делаем со второй таблицей:
Обратите внимание, я добавил пустой диапазон O1:O6, чтобы после того, как мы объединим два массива наши столбцы Итого1 и Итого2 не пересекались.
Второй важным момент: кол-во строк в диапазонах, которые мы объединяем, должно быть одинаковым, иначе они не смогут объединится, поэтому мы берем везде диапазоны с 1 по 6 строку (или со 2-ой по 6-ю, шапка второй таблицы нам не нужна)
Третий момент: кол-во столбцов для горизонтального объединения (двух таблиц) тоже должно быть одинаковым и поэтому добавим в формулу первой таблицы пустой столбец, итого у нас получится по четыре столбца в каждой.
Все, теперь мы готовы объединить нужные нам столбцы в двух таблицах в один массив, итоговая формула будет такой,
={A1:B3\G1:H3 ; I1:J6\O1:O6\N1:N6}
(пробелами я отделил одну таблицу от другой. чтобы было визуально понятно, а ";" - символ для горизонтального объединения)
Теперь мы готовы использовать этот массив как диапазон для формулы QUERY, напомню то, что в случаях сборных диапазонов данных вместо имени столбца в запросе QUERY нужно писать ColN, где N - номер столбца.
И наконец (барабанная дробь), итоговая формула:
=QUERY({A1:B3\G1:H3; I2:J6\O2:O6\N2:N6};"select Col1, Col2, avg(Col3), avg(Col4) group by Col1, Col2")
Разумеется, формула будет работать и без дополнительных массивов (A6:C8, A10:D15, I9:L15 на скриншоте выше), их я оставил (в док-те они тоже есть), чтобы по им можно было проследить маршрут создания.
Документ с примером по ссылке. (файл-> создать копию, он сохранится на ваш диск и вы сможете его редактировать и попрактиковаться)
Ольга, немного практики и у вас все получится, хоть и выглядит всё страшновато. Будут вопросы, пишите @namokonov, помогу разобраться.
Как собрать данные с разных листов. Например, есть карточки учреждений. Один лист — одна карточка. Все карточки одинакового формата. Хочется лист со списком телефонов.
Надеемся, что правильно поняли вопрос.
Итак, есть много листов с однотипной карточкой, в которой есть ряд данных - телефон, электропочта и прочее:
Нам нужно забрать, допустим, две ячейки с каждого из листов на сводный. Желательно более-менее автоматизированно.
Это можно сделать с помощью функции ДВССЫЛ/INDIRECT.
Ее единственный аргумент - текст с адресом, по которому ей нужно пройти и забрать данные.
=ДВССЫЛ("Лист!A1") принесет данные из ячейки A1 с листа "Лист"
Главное - заранее занести в отдельный столбец на сводном листе названия всех листов, с которых будем забирать данные:
Функция выглядит так:
=ДВССЫЛ(A3&"!A2:B2")
=INDIRECT(A3&"!A2:B2")
где A3 - ячейка с названием листа, A2:B2 - тот диапазон, из которого нам нужны данные (в данном случае ФИО и телефон).
Такая функция сразу выдает диапазон из двух ячеек.
Остается вопрос - как быть, если листов в файле много - например, 70? Вбивать их руками на сводный лист?
Не стоит.
Мы сделали небольшой скрипт - пользовательскую функцию, которая выдает названия всех листов.
Вот код функции:
function shnames()
{
var ss = SpreadsheetApp.getActiveSpreadsheet()
var count = ss.getSheets().length
var shname = []
for(var i = 1; i<count;i++){
shname.push(ss.getSheets()[i].getSheetName())
}
return shname
}
В нашем примере она введена только в ячейку A2 и выдает массив из названий всех листов:
Как установить скрипт с функцией?
Смотрите нашу GIF-инструкцию по установке скрипта (В ней код другого скрипта, но принцип абсолютно аналогичный).