Как через запрос query в консолидированной таблице google sheets добавить столбец с названием листа?

Question

[RKV762]

Есть набор однородных данных распределенных по листам, где название листа выступает городом, к которому относятся данные.

Пример: Лист 'Москва'
1. Модель, стоимость, дата, менеджер и прочая...
2. Модель, стоимость, дата, менеджер и прочая...

Аналогичным образом оформлены данные на листах 'СПБ', 'Новосибирск' и так далее.

Требовалось все это свести в один лист, что и проделано через запрос query:

=QUERY({'MSK'!A1:G;'SPB'!A2:G};"SELECT * WHERE(Col3 is not null) ORDER BY Col3")

Но, так как в самих данных названия городов отсутствовали, то они перемешались. Вижу несколько вариантов решения, но мне они не нравятся:
1) Добавить столбец в исходные данные вручную. Да, работает, но если после новые строки будут подгружаться автоматически, то таблица съедет, так как будут данные:
1. Москва, Модель, стоимость, дата, менеджер и прочая...
999. Модель, стоимость, дата, менеджер и прочая...
2) Создать промежуточный лист, где добавить столбец с городом, а в следующий столбец добавить запрос query, затем в конечный лист собирать данные уже с промежуточного. Решение выглядит громоздким, ощущение, что придумываю велосипед.

Отмечу: В видео по Power Query это делается буквально на автомате, листы собираются в один и столбец с названием листа появляется слева, после вся таблица выгружается кнопкой в эксель и готово, а вот как сделать это в онлайн таблицах не нашел.

Для наглядности, что имею в виду:


Я нашел как добавить к общим данным дополнительный столбец с месяцем и кварталом, quarter(Col3), (month(Col3)+1)*30
А вот какая функция добавит название листа, я не нашел. Еще одно примечание к этому решению: столбцы добавляются уже к объединенному диапазону, и если с датами это целесообразно, то в случае с названием листа остается вопрос, как функция поймет к какой строке в объединенных данных какой город добавлять?

Comments

[RKV762]

Вторая картинка в описании вопроса есть, но почему-то не отображается, дублирую тот вид к которому хотелось бы привести консолидированную таблицу:

Answer 1

[Alexey]

1. Делаете служебный лист c названиями всех имеющихся листов (городов) в Вашей таблице.
Можете сделать это вручную, можете скриптом, он достаточно простой.
Можно даже запускать такой скрипт при открытии таблицы автоматически, если предполагается изменение количества страниц.

2. Собираете данные со всех листов, но в первую колонку вставляете название листа.
Делаетеся это такой формулой:

=REDUCE({"Город"\"Продукт"\"Дата"\"Сумма"\"Менеджер"};
  tocol('Города'!A2:A;1);
  LAMBDA(acc;city;
    VSTACK(acc;
      BYROW(INDIRECT(city&"!A2:D");LAMBDA(_row;{city\_row})))
  )
)

Reduce - сводит воедино данные по страницам (городам в Вашем случае). lambda - составная часть этой функции.

TOCOL cо вторым параметром 1 используется в данном случае чтоб убрать пустые строки, так как количество городов неизвестно и мы используем открытый диапазон A2:A.

VSTACK - объединяет массивы. В данном случае - массив acc, результат предыдущих итераций reduce, с текущим.

BYROW - используется для построчной обработки данных. В данном случае - для формирования строки из названия текущего города (city из текущей итерации reduce) и каждой строки со страницы текущего города.

3. Эту формулу оборачиваете в нужный вам QUERY, где в том числе фильтруете пустые строки.

Вот ваш QUERY:


А вот так он будет выглядеть, когда в качестве "входящего" диапазона используется формула из п2.

=QUERY(
  REDUCE(...
  );
  "SELECT ..")

Comments

[RKV762]

Благодарю за ответ, Алексей!

Что-то приближенное к желаемому получилось (таблица соотнесла продажу грибов с Москвой и торта с СПБ), но не до конца, полагаю из-за неполного понимания совета. Посмотрите пожалуйста в специально созданную тестовую таблицу: https://docs.google.com/spreadsheets/d/1zRp7Esd7-F...

В частности мне не совсем понятно про "оборачивание", догадываюсь что нужно вложить одно в другое, но как именно это должно выглядеть?

[RKV762]

Вы великолепны, Алексей!

Отдельное спасибо за разъяснение работы формулы, для меня важно не просто решить задачу, но и главное понять, как именно это делается. У меня уйдет некоторое время, чтобы осознать решение в должной мере и поэкспериментировать в плане того какие еще задачи с его помощью можно решать и я благодарен Вам за это.

Кое-какие уроки я уже извлек, возможно, они будут полезны и тем, кто прочитает ответ:
1) Я пытался добавить новый столбец не в диапазон, а в вывод данных (как поступил с кварталом и месяцем, а возвращалась ошибка), теперь я вижу как надо и полагаю даже понимаю, почему так.
2) Как именно стоит записывать формулу (не в одну строку, особенно при наличии ряда значений).
3) Заметил и важную часть, что форматирование колонки с месяцем теперь задается в самом query. Действительно, ручное форматирование колонки было плохим решением, ведь как только данные достигли бы граничной строки, далее оно бы слетело со всеми вытекающими (заодно то же самое применил и к кварталу, он тоже у меня был прописан с буквами)