Таблицы Google конвертируют данные матрицы в реляционные строки

Данные моей электронной таблицы Google представлены в виде матрицы с «дырками». См. https://docs.google.com/spreadsheet/ccc?key=0Ati5T34RP9mEdGJfcUFzVDV4elZIbExkTGM4aktQT2c&usp=drive_web#gid=0

Я хотел бы создать еще один лист из приведенных выше данных, чтобы он выглядел так, как я сделал вручную в https://docs.google.com/spreadsheet/ccc?key=0Ati5T34RP9mEdGJfcUFzVDV4elZIbExkTGM4aktQT2c&usp=drive_web#gid=

Любые идеи?


google-sheets google-apps-script google-sheets-formula array-formulas google-sheets-query
person RudyF    schedule 22.10.2014    source источник
comment
2 вопроса: 1. Для этого есть решение на основе формулы, но оно также может быть достигнуто с помощью скрипта Google Apps; Вы предпочитали не использовать ГАЗ? 2. Ваша таблица находится в предыдущей версии Таблиц; возможно ли перейти на последнюю версию?   -  person AdamL    schedule 23.10.2014
comment
Я бы хотел запачкать руки ГАЗОМ. И, конечно же, переход на новейшую версию, безусловно, жизнеспособен.   -  person RudyF    schedule 24.10.2014

Ответы (2)


arrow_upward
2
arrow_downward

Это должно работать в последней версии Таблиц:

=ArrayFormula(QUERY(VLOOKUP(HLOOKUP('Table Chart'!A2,'Table Chart'!A2:A,INT((ROW('Table Chart'!A2:A)-ROW('Table Chart'!A2))/COLUMNS('Table Chart'!B1:G1))+1,0),{'Table Chart'!A2:G,IF(ROW('Table Chart'!A2:A),'Table Chart'!B1:G1)},{SIGN(ROW('Table Chart'!A2:A)),MOD(ROW('Table Chart'!A2:A)-ROW('Table Chart'!A2),COLUMNS('Table Chart'!B1:G1))+{2,2+COLUMNS('Table Chart'!B1:G1)}},0),"select Col1, Col3, Col2 where Col2 is not null",0))

Для полноты, это будет работать в предыдущей версии, но даты преобразуются в текстовые строки; их можно преобразовать в числовые значения, но тогда формула станет еще длиннее:

=ArrayFormula(REGEXREPLACE(TRANSPOSE(SPLIT(CONCATENATE(REPT('Table Chart'!A2:A&CHAR(9)&'Table Chart'!B1:G1&CHAR(9)&TEXT('Table Chart'!B2:G,"d-MMM-yyyy")&CHAR(10),LEN('Table Chart'!B2:G)>0)),CHAR(10))),"^(.+?)\t(.+?)\t(.+)$",{"$1","$2","$3"}))

Сценарий Google Apps предоставит вам решение «запустить один раз», которое будет лучше (IMO), чем формула, которая постоянно пересчитывает, хотя приведенные выше формулы могут быть вызваны, а затем просто преобразованы только в значения. Однако вам может потребоваться их динамическое обновление.

Если вам нужно решение GAS, возможно, добавьте тег google-apps-script, чтобы предложить другие ответы.

person AdamL    schedule 24.10.2014
comment
Большое спасибо. Решение предыдущей версии действительно работает, но я только что понял, что не упомянул, что искал сортировку по столбцу даты. Я должен иметь возможность настроить последнюю версию решения для этого, так что теперь осталось только преобразовать эту электронную таблицу ... что, я думаю, я могу сделать. - person RudyF; 24.10.2014
comment
Да, вы можете просто добавить заказ по столбцу 2 в предложение QUERY. - person AdamL; 24.10.2014
comment
Это действительно хорошо работает, но имеет несколько недостатков, о которых нужно знать. На листе «Таблица таблиц» должно быть много пустых строк для работы, кажется, требуется, чтобы общее количество строк на листе матрицы (включая пустые) было не менее (numCols * numRows) в матрице. Или, другими словами, если у вас есть матрица 50x50, когда вам нужно добавить не менее 2450 пустых строк после последней строки в листе матрицы. - person yzfr1; 23.06.2019

arrow_upward
1
arrow_downward

удалите диапазон A2: C и вставьте эту формулу в ячейку A2:

=ARRAYFORMULA(SORT(SPLIT(TRANSPOSE(SPLIT(TRIM(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF('Basic Matrix'!B2:H<>"", "♀"&
 SUBSTITUTE('Basic Matrix'!A2:A, "Indian Randonneurs", "IR")&"♂"&
 SUBSTITUTE('Basic Matrix'!B1:H1, "IR-", )&"♂"&'Basic Matrix'!B2:H, ))
 ,,999^99)),,999^99)), "♀")), "♂"), 3, 1))

0

(примечание: формат B: C для внутреннего использования с помощью кнопки 123 по мере необходимости)

person player0    schedule 24.06.2019