Как объединить несколько пар столбцов в одну таблицу (с нулями для отсутствующих значений)?
У меня есть много пар столбцов (данные масс-спектра), которые я хочу объединить в один массив с нулями для недостающих строк.
У меня есть что-то вроде этого:
... и я хочу иметь что-то вроде этого:
комбинированный массив
Я попробовал использовать сводный столбец, но это не сработало. Некоторое использование TOCOL может быть вариантом.
Ответы 3
Преобразование данных
=LET(data,A1:H11,na,0,
s,SEQUENCE(COLUMNS(data)/2,,,2),
sh,TAKE(data,1),
dh,HSTACK(TAKE(sh,,1),CHOOSECOLS(sh,s+1)),
sd,DROP(data,1),
drl,SORT(UNIQUE(TOCOL(CHOOSECOLS(sd,s),1))),
dd,DROP(REDUCE("",s,LAMBDA(rr,c,
HSTACK(rr,XLOOKUP(drl,CHOOSECOLS(sd,c),
CHOOSECOLS(sd,c+1),na)))),,1),
VSTACK(dh,HSTACK(drl,dd)))
Переменные
s - {1;3;5;7}
s+1 - {2;4;6;8}
sh - A1:H1
sd - A2:H11
dh - J1:N1
drl - J2:J17
dd - K2:N17
Это может означать, что разделителем списка является точка с запятой (;). Замените все запятые (,) на него (;).
К сожалению, у меня это не работает: LET(data,A1:H11,na,0, {1;3;5;7},SEQUENCE(COLUMNS(data)/2,,,2), A1:H1,TAKE (данные,1), J1:N1,HSTACK(TAKE(A1:H1,,1),ВЫБЕРИТЕ ECOLS(A1:H1,{2;4;6;8})), A2:H11,DROP(данные,1) , J2:J17,SORT(UNIQUE(TOCOL(ВЫБЕРИТЕ ЭКОЛОГИ(A2:H11,{1;3;5;7}),1))), K2:N17,DROP(REDUCE("",{1;3;5 ;7},LAMBDA(rr,c, HSTACK(rr,XLOOKUP(J2:J17,CHOOSECOLS(A2:H11,c), CHOOSECOLS(A2:H11,c+1),na)))),,1), VSTACK(J1:N1,HSTACK(J2:J17,K2:N17))) С точкой с запятой тоже не работает...
На точки с запятой необходимо заменить только запятые. «Переменные» показывают, что представляет собой каждая переменная.
Здравствуйте, я обнаружил, что в моем Excel также есть проблемы с буквами c и r. Итак, я использовал это, но все равно безрезультатно: =LET(data;A1:H11;na;0; {1;3;5;7};SEQUENCE(COLUMNS(data)/2;;;2); A1: H1;TAKE(данные;1); J1:N1;HSTACK(TAKE(A1:H1;;1);ВЫБЕРИТЬ(A1:H1;{2;4;6;8})); A2:H11;DROP(данные) ;1); J2:J17;SORT(UNIQUE(TOCOL(ВЫБЕРИТЕ ЭКОЛОГИ(A2:H11;{1;3;5;7});1))); K2:N17;DROP(REDUCE("";{1; 3;5;7};LAMBDA(yy;u; HSTACK(yy;XLOOKUP(J2:J17;CHOOSECOLS(A2:H11;u); CHOOSECOLS(A2:H11;u+1);na))));; 1); ВСТАК (J1: N1; HSTACK (J2: J17; K2: N17)))
Вот еще один способ использования MAKEARRAY() --> не использования тяжелых ресурсных функций для манипулирования данными.
• Формула, используемая в ячейке J1
=LET(
_Data, A1:H35,
_Header, TAKE(_Data,1),
_Rest, DROP(_Data,1),
_Uniq, UNIQUE(TOCOL(FILTER(_Rest,_Header=TAKE(_Header,,1)),1,1)),
_Output, MAKEARRAY(ROWS(_Uniq),MAX(SEQUENCE(,COLUMNS(_Rest)/2)),LAMBDA(r,c,
IFNA(VLOOKUP(INDEX(_Uniq,r,),CHOOSECOLS(_Rest,c*2-{1,0}),2,0),0))),
VSTACK(UNIQUE(_Header,1), HSTACK(_Uniq,_Output)))
Я обнаружил, что в моем Excel есть проблемы с буквами r и c, поэтому я использовал это: =LET( _Data; A1:H35; _Header; TAKE(_Data;1); _Rest; DROP(_Data;1); _Uniq; UNIQUE( TOCOL(FILTER(_Rest;_Header=TAKE(_Header;;1));1;1)); _Output;ROWS(_Uniq);MAX(SEQUENCE(;COLUMNS(_Rest)/2));LAMBDA( u;y; IFNA(VLOOKUP(INDEX(_Uniq;u;);CHOOSECOLS(_Rest;y*2-{1;0});2;0);0))); VSTACK(UNIQUE(_Header;1) ; HSTACK(_Uniq;_Output))) Однако результата нет: функция FILTER возвращает ошибку.
@lemann все данные решения работают с моей стороны, это из-за вашей версии Excel, которая не поддерживает.
Большое спасибо за Вашу помощь! Я постараюсь обновить свой Excel и попробовать ваши решения. Пожалуйста, сообщите мне, какую версию Excel вы использовали.
@ Леманн MS365. Вы также можете использовать веб-версию Excel, а также нажмите здесь
Ради интереса моя рекурсивная альтернатива должна быть быстрой:
Формула в J1:
=LET(r,A1:H35,h,A1:H1,n,UNIQUE(TOCOL(IFERROR(IFS(h>"0",r&"")*1,"Mass"))),x,LAMBDA(f,i,z,IF(i=COLUMNS(r),z,f(f,i+2,HSTACK(z,VLOOKUP(n,DROP(r,,i),2,0))))),IFNA(x(x,,n),))
Спасибо за помощь, но результата нет: выдает ошибку "недопустимое имя". Я использовал это: =LET(y;A1:H35;h;A1:H1;n;UNIQUE(TOCOL(IFERROR(IFS(h>"0";y&"")*1;"Mass")) );x;LAMBDA(f;i;u;IF(i=COLUMNS(y);u;f(f;i+2;HSTACK(u;VLOOKUP(n;DROP(y;; i);2;0)))));IFNA(x(x;;n);))
Он отлично работает для меня. Я использую тот же синтаксис и с точкой с запятой. @lemann. Какую версию Excel вы используете?
Microsoft Excel 2019 MSO (16.0.10406.20006) 32-разрядная версия
@lemann все данные решения работают с моей стороны, это из-за вашей версии Excel, которая не поддерживает.
Большое спасибо за Ваш ответ! Я попытался использовать вашу формулу с вашим MWE, но получил ошибку «С этой формулой возникла проблема». Я уверен, что мне нужно сделать что-то по-другому. Пожалуйста помоги!)))