#google-sheets #google-sheets-formula #google-sheets-query
#google-таблицы #google-таблицы-формула #google-таблицы-запрос
Вопрос:
Предоставьте набор данных, подобный книге, с 3 столбцами:
Name,Type,Amount
Foo,A,-100
Bar,A,-200
Baz,A,-500
Foo,B1,200
Foo,A,-100
Bar,A,-300
Baz,B2,2000
Bar,A,-300
Если я хочу создать таблицу с такими заголовками:
Name,X,Y,Z
где Name — отдельный список Name в первой таблице,
где X — сумма значений из первой таблицы, где Name совпадают, Type равно A и Value отрицательно
где Y — сумма значений из первой таблицы, где Name совпадают s, Type начинается с B и Value является положительным
где Z находится Y / X
Как я могу это сделать? Прямо сейчас я реализовал это как 2 разных запроса для X и Y , но они повторяют Name столбец, и это невозможно объединить в Z , потому что эти два запроса не возвращают один и тот же набор, Names поскольку некоторые Names не имеют значений, соответствующих запросу для обоих X и Y .
Вот конкретный пример на листе, который я сделал: https://docs.google.com/spreadsheets/d/1qbwLqRncgo8pQNU4oAeYfEHr5o4L8EGB-Skhu869fGE/edit?usp=sharing
Комментарии:
1. Я могу помочь вам решить эту проблему без особых проблем, но абстрактно это сложно. Не могли бы вы вставить некоторые поддельные данные в новый лист и поделиться ими в виде редактируемой ссылки, чтобы я и другие могли продемонстрировать / протестировать решения?
2. Пожалуйста, добавьте пример желаемого результата, это тоже поможет.
3. Спасибо, просто добавил это в вопрос!
4. Пожалуйста, сделайте ваш лист общедоступным и доступным для редактирования для всех — мы должны иметь возможность обновлять его, поэтому убедитесь, что это всего лишь копия вашего листа и с конфиденциальными данными.
5. Упс, забыл это сделать. Теперь каждый должен иметь возможность редактировать.
Ответ №1:
Это может сработать для вас.
Я добавил вкладку к вашему листу, Лист1-GK. В ней я сначала вычисляю суммы, которые либо распределяются, либо инвестируются, для каждой строки ваших данных — смотрите формулы в ячейках D1 и E1. Затем я использую следующий запрос в G8, чтобы получить результат, который вы просили.
=query(A1:E12,"select A, sum(D), sum(E), sum(E)/sum(D) group by A label sum(D) ' Invested', sum(E) 'Distributed', sum(E)/sum(D) 'MoM'",1)
Единственная проблема заключается в том, что значение MoM возвращается как отрицательное. При необходимости это можно обработать с помощью другого столбца, чтобы принудительно ввести абсолютное значение. Я не уверен, есть ли какой-либо способ включить это в оператор запроса.
Дайте мне знать, если это полезно, или если вам нужно что-то другое.
