Как перенести данные на другую вкладку в excel
Я ищу перенос таблицы на другую вкладку в Excel.
Моя первая таблица:
First_Name | Last_Name | Company | Number | Done (1 = yes / 0 = no)
-----------------------------------------------------
Allison | Dark ! McDonald | 2,00 | 1
John | Doe | Amazon | 10,00 | 1
Julian | Smith | Coca Cola | 3,00 | 0
Kathy | Johnson | Coca Cola | 20,00 | 1
Barbara | Brown | Tesco | 10,00 | 0
Alexander | Lee | Amazon | 4,00 | 0
Harry | Moore | Amazon | 8,00 | 0
Как я могу получить эти данные на другой вкладке, например:
Company | Number of names | Done | Sum of Numbers
------------------------------------------------------
Amazon | 3 | 1 on 3 | 22,00
Coca Cola | 2 | 1 on 2 | 23,00
McDonald | 1 | 1 on 1 | 2,00
Tesco | 1 | 0 on 1 | 10,00
Я искал формулу, но она также может быть в vba. Спасибо за вашу помощь.
Что вы пробовали? Для начала, для столбца A вы можете использовать UNIQUE, для столбца B COUNTIF, то же самое для C. И SUMIF для D. Или сводную таблицу, как говорит @MadMarc
Спасибо за эти советы. Я не могу использовать UNIQUE из-за моей старой версии. И да, я мог бы использовать сводную таблицу, но мне было интересно, как я могу сделать это без сводной таблицы, а, например, с формулой с ПРОСМОТР.
Ответы 3
В более старых версиях Excel, чтобы получить уникальный список, вы можете скопировать весь список компаний и использовать функцию Excel «Удалить дубликаты» или поместить формулу в A13 и перетащить ее вниз, пока она не станет пустой:
=IFERROR(INDEX($C$2:$C$8, MATCH(0,COUNTIF($A$12:A12, $C$2:$C$8), 0)),"")
Чтобы получить количество названий компаний, вставьте B13 и перетащите вниз:
=COUNTIF($C$2:$C$8,A13)
Для «Готово» в C13:
=COUNTIFS($C$2:$C$8,A13,$E$2:$E$8,1)&" of "&COUNTIF($C$2:$C$8,A13)
Для суммы чисел в D13:
=SUMIF($C$2:$C$8,A13,$D$2:$D$8)
Результат:
Адаптируйте диапазоны к вашим данным и новому назначению таблицы.
Предполагая отсутствие ограничений версии Excel в соответствии с тегами, перечисленными в вопросе. Это еще одно применение шаблона REDUCE/VSTACK, вы можете проверить мой ответ на следующий вопрос: как преобразовать таблицу в Excel из вертикальной в горизонтальную, но с разной длиной. Формула выводит весь результат, включая заголовок, и возвращает результат, отсортированный по названию компании. Наденьте G1 следующее:
=LET(in, A1:E8, h, TAKE(in,1), data, DROP(in,1), cmp, INDEX(data,,3),ux,
SORT(UNIQUE(cmp)), done, INDEX(data,,5), amnt, INDEX(data,,4),
header, {"Company","Number of Names","Done","Sum of Numbers"}, CALC, LAMBDA(x,
LET(a, cmp=x, b, SUM(N(a)), c, SUM((a)*(done=1)),
HSTACK(x, b, c&" on "&b,SUM(a*amnt)))), REDUCE(header, ux, LAMBDA(ac,x,
VSTACK(ac, CALC(x)))))
Примечание. Обновлена формула, глядя на @VBasic2008. Я понял, что компании можно отсортировать в первую очередь, что позволяет сократить формулу.
Вот результат:
Пользовательская функция LAMBDACALC выполняет вычисления для создания всей строки на каждой итерации REDUCE. Мы не можем использовать заголовок для инициализации аккумулятора (ac), потому что нам нужно сначала отсортировать результат.
Преобразование данных (формула Excel)
Улучшено (Изменить)
- После применения предложений Дэвида Леала в комментариях это выглядит так:
=LET(d,C2:E8,del," on ",dCols,3,
co,TAKE(d,,1),u,SORT(UNIQUE(co)),
HSTACK(u,MAKEARRAY(ROWS(u),dCols,LAMBDA(r,c,
LET(cc,MOD(c,dCols)+c-1,f,co=INDEX(u,r,1),
fr,ROWS(FILTER(co,f)),s,SUM(INDEX(d,,cc)*f),
IF(cc=1,fr,IF(cc=2,s,s&del&fr)))))))
Исходный
=LET(d,C2:E8,del," on ",
co,TAKE(d,,1),u,SORT(UNIQUE(co)),
HSTACK(u,MAKEARRAY(4,3,LAMBDA(r,c,
LET(cc,MOD(c,3)+c-1,f,co=INDEX(u,r,1),
IF(cc=1,ROWS(FILTER(co,f)),
LET(s,SUM(FILTER(INDEX(d,,cc),f)),
IF(cc=2,s,s&del&ROWS(FILTER(co,f))))))))))
Это интересный подход, позволяющий избежать использования REDUCE/VSTACK, что может быть неэффективным для очень больших данных. Мне нужно ознакомиться с альтернативой MAKEARRAY.
@DavidLeal Я стараюсь избегать этого, когда это возможно. Я не совсем уверен в ROWS(FILTER(co,f)). Было бы лучше поместить его во 2-й LET (он используется только для столбцов 1 и 3)?
Я думаю, что это возможно, ниже приведен более короткий подход с той же идеей: =LET(d,C2:E8,del," on ",co,TAKE(d,,1),u,SORT(UNIQUE(co)), HSTACK(u,MAKEARRAY(ROWS(u),3,LAMBDA(r,c, LET(cc,MOD(c,3)+c-1,f,co=INDEX(u,r,1), cnts, ROWS(FILTER(co, f)), s, SUM(INDEX(d,,cc)*f), IF(cc=1,cnts, IF(cc=2,s,s&del&cnts))))))) Я получаю тот же результат. Я использую ROWS(u) вместо 4
@DavidLeal Я знал, что необходимо вмешательство. Спасибо за предложения. 4 было действительно нелепой ошибкой, но избавиться от ФИЛЬТРА, это я должен запомнить. Я все это реализовал.
Это идеальный сценарий для использования сводной таблицы.