Nigle2 (Автор тем)

Power Query. Сводная таблица (столбец сведения) с использованием нескольких столбцов значений

Пользователь

Сообщений: 5 Регистрация: 29.08.2023

16.04.2024 10:30:35

Добрый день!
Имеется простая (почти) таблица с несколькими сотнями тысяч строк. В таблице присутствует столбец "Месяц", который необходимо использовать как столбец сведения данных. Как сделать "сводную" по одному столбцу значений я понимаю. Но задача усложняется именно тем, что их несколько (может быть 2, а может быть >5).
Необходимо сделать сводную с учетом столбца сведения "Месяц" и в разрезе 3 столбцов значений (Выручка, Общий вес, Заказы)

Пока мое решение данной задачи выглядит несколько костыльно:
1) сделать 3 отдельных запроса и свести 3 отдельные таблицы по каждому из столбцов значений.
2) Выгрузить 3 запроса на один лист и получить "одну" таблицу

Минусы такого варианта:
- "одна" таблица - только визуально, т.к. эксель воспринимает их как 3 разные таблицы (т.е. отсутствуют фильтрация и прочие фишки УТ; или необходимость доработки - копирования значений в другой диапазон и создание новой умной таблицы)
- с учетом объема исходных данных есть риск ошибиться при сортировке этих самых исходных данных, т.е. получить несоответствие клиента его выручке/весу/заказам
- так же с учетом объема данных это "утяжеляет" файл и процесс работы с запросами.

Какое более элегантное решение может быть для работы со столбцом сведения и несколькими столбцами значений?
Пример файла во вложении.

Спасибо!

Перейти

Гугл таблицы. Макрос на вставку формулы из ячейки

Nigle2

Пользователь

Сообщений: 5 Регистрация: 29.08.2023

29.08.2023 11:18:11

Добрый день.
Есть массив данных из которого через QUERY мне необходимо выбирать n-ное кол-во столбцов по определенным условиям.
Условий может быть от 1 до 5

Цитата
=QUERY('Справочники'!A1:AD; "select A,B,C,D,E,P,Q,R,S,T,U,V,W,X,Y,Z,AA,AB,AC,AD where (E = 'Франчайзи' and D = 'Владивосток')")

где E = 'Франчайзи' and D = 'Владивосток' и есть 2 из 5 условий
Сама формула собирается в ячейке C9 "по кусочкам" с учетом выбранных условий (т.е. от слова QUERY до слова where - это "постоянная", а "Е=..." и далее подставляется по мере необходимости. Итого в ячейке C9 собрана формула значение которой соответствует формуле нужного запроса.

Необходим макрос, который будет копировать значение в ячейке C9 и вставлять его в ячейку A12 и активировать как формулу

При попытке записать макрос в ячейку А12 записывается конкретная формула, а не значение ячейки С9. Т.е. если я меняю условия, то в ячейке С9 меняется формула, но в макросе уже записана "старая" формула

Код

function Find() {
 var spreadsheet = SpreadsheetApp.getActive();
 spreadsheet.getRange('A12').activate();
 spreadsheet.getRange('C9').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
 spreadsheet.getCurrentCell().setFormula('=QUERY('Справочники'!A1:AD; "select A,B,C,D,E,P,Q,R,S,T,U,V,W,X,Y,Z,AA,AB,AC,AD where (E = 'Франчайзи' and D = 'Владивосток')")');
 };

Подскажите, как исправить код макроса, чтобы он вставлял измененное значение в А12?

Перейти