Как создать формулу, которая подсчитывает количество дубликатов, возвращает значение в выбранной мной ячейке, а затем удаляет дубликаты?

Question 1

Мое предложение касается только первой части (отсортированный список продуктов):

Вы можете поместить свой список в таблицу Excel, например, Table1и ссылаться на этот список как Table1[List].
В столбцах C, и Dвы получите список уникатов с их количеством.
Формула в C2: =UNIQUE(Table1[List])
Формула в D2: =COUNTIF(Table1[List],C2#)
В качестве следующего шага вы можете отсортировать эти два столбца, как показано в столбцах Fи G.
Формула в F2:=SORT(HSTACK(C2#,D2#),2,-1)

Answer

Мое предложение касается только первой части (отсортированный список продуктов):

Вы можете поместить свой список в таблицу Excel, например, Table1и ссылаться на этот список как Table1[List].
В столбцах C, и Dвы получите список уникатов с их количеством.
Формула в C2: =UNIQUE(Table1[List])
Формула в D2: =COUNTIF(Table1[List],C2#)
В качестве следующего шага вы можете отсортировать эти два столбца, как показано в столбцах Fи G.
Формула в F2:=SORT(HSTACK(C2#,D2#),2,-1)

Question 2

Если у вас есть и используются следующие данные, MS365то вы можете воспользоваться следующими методами:

Волокно	Кальций	Железо	Магний	Цинк	Медь	Марганец	Йод	Селен	Фтористый
фасоль по-флотски	молоко	обогащенные злаки	семена тыквы	говядина	краб	зародыши пшеницы	Печеный картофель	бразильский орех	черный чай
чечевица	йогурт	хлопья пшеничные отруби	миндаль	семена тыквы	Омар	сладкая картошка	молоко	семена подсолнечника	креветка
фасоль пинто	пармезан	крупа	шпинат	семена подсолнечника	шиитаке	листовая капуста	креветка	тунец	изюм
нут	швейцарский	гранола	черные бобы	чечевица	белые кнопки	горох	Турция	лосось	малина
лимская фасоль	творог	измельченная пшеница	лосось	Турция	портобеллас	кедровые орехи	фасоль по-флотски	Омар	вареная овсянка
авокадо	шпинат	рисовая каша	йогурт	яйца	креминис	фундук	яйца	креветка	пиво
семена тыквы	листовая капуста	говядина	молоко	шиитаке	сладкая картошка	пекан		свиная вырезка	красное вино
семена кунжута	зелень репы	Говяжий фарш	авокадо		зелень репы	овес		говядина	крепкий сидр
миндаль	брокколи	говяжья вырезка	бананы		Печеный картофель	лимская фасоль		ягненок	крупа
фисташки	спаржевая фасоль	филе буйвола			шпинат	нут		курица	суп из черной фасоли
пекан	фасоль по-флотски	наземный бизон			семена подсолнечника	фасоль по-флотски		Турция	Печеный картофель
семена подсолнечника	лосось	портерхаус			семена тыквы	чечевица		фасоль по-флотски	морковь
тыква желудевая	тыква желудевая	тушеная говядина мясо			бразильский орех	шпинат		фасоль пинто	шпинат
хаббардовская тыква	Омар	свиная вырезка			грецкие орехи	ананас		лимская фасоль	мускусная дыня

• Использование GROUPBY()функции, применимой только к MS365--> Office Insiders.

=LET(
     _Data, A3:J16,
     _SingleCol, TOCOL(_Data,1),
     GROUPBY(_SingleCol,_SingleCol,ROWS,,0,-2))

Или, используя BYROW()илиMMULT()

=LET(
     _Data, A3:J16,
     _SingleCol, TOCOL(_Data,1),
     _Uniq, UNIQUE(_SingleCol),
     _Counts, MMULT(N(_Uniq=TOROW(_SingleCol)),SEQUENCE(ROWS(_SingleCol))^0),
     HSTACK(_Uniq,_Counts))

=LET(
     _Data, A3:J16,
     _SingleCol, TOCOL(_Data,1),
     _Uniq, UNIQUE(_SingleCol),
     _Counts, BYROW(_Uniq,LAMBDA(x,SUM(--(x=_SingleCol)))),
     HSTACK(_Uniq,_Counts))

Все приведенные выше формулы дают количество подсчетов для каждой уникальной записи.
Использование TOCOL()функции для преобразования данных из нескольких диапазонов в массив из одного столбца, исключая дубликаты.
Метод 1--> В первом методе используется GROUPBY()функция, которая предназначена для group, aggregateи sortсогласно полям, которые мы указываем. Однако указанная функция находится в Office-Insidersследовательно, пока и если вы не включите опцию, это не будет работать и покажется как #NAME!ошибка.
Метод 2--> Также начинается с LET()функции, что облегчает чтение и сокращает использование избыточных диапазонов/формул.
Как и в первом методе, он сначала применяет TOCOL()функцию, затем использует UNIQUE()функцию для возврата уникальных значений и, наконец, использует MMULT()функцию для возврата количества, которое является матричным произведением двух заданных массивов.
Наконец, используем HSTACK()для объединения обоих возвращаемых массивов UNIQUE()и MMULT()функции для возврата желаемого результата.
Метод 3--> Начинается с тех же шагов, что и выше, за исключением того, что используется BYROW()функция для получения количества совпадений каждого элемента.

Однако если вы находите эти методы сложными, то вы также можете использовать простейший из них, показанный ниже:

Во-первых, используйте функцию TOCOL()& UNIQUE(), чтобы вернуть несколько диапазонов в один массив, исключая пустые и дубликаты. И поместите в ячейку, скажем, вL2

=UNIQUE(TOCOL(A3:J16,1))

Наконец, введите следующую функцию, чтобы получить подсчеты. Помните, что я взял диапазон данных, который A3:A16вам может понадобиться изменить в соответствии с вашим костюмом. Следующее не нужно заполнять, так как оно будет выливаться. Где L2формула, возвращаемая сверху, с использованием TOCOL()иUNIQUE()

=COUNTIFS(A3:J16,L2#)

Если вы все еще не готовы следовать вышеизложенному, то вы можете использовать , POWER QUERYкоторый доступен Excel 2010+с ,доступен в Windows Excel 2010+иExcel 365 (Windows or Mac)

Чтобы использовать Power Query, выполните следующие действия:

Сначала преобразуйте исходные диапазоны в таблицу и назовите ее соответствующим образом. Для этого примера я назвал ееTable1

Далее откройте пустой запрос из DataTab --> Get & Transform Data--> Get Data--> From Other Sources-->Blank Query

Вышеуказанное позволяет Power Queryоткрыть окно, теперь из HomeTab --> Advanced Editor--> и вставьте следующее, M-Codeудалив все, что вы видите, и нажмитеDone

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Value"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

Наконец, чтобы импортировать его обратно в Excel--> Нажмите Close & Loadили Close & Load To--> Первый нажатый элемент создаст файл New Sheetс требуемым выводом, а последний выведет окно с вопросом, куда поместить результат.

^{Примечание: Формула не может удалять дубликаты, вместо этого она извлекает значения, помещенные в ячейку, следуя определенным условиям. Как я показал выше. Вам в основном нужна функция UNIQUE(), TOCOL()& COUNTIF()или COUNTIFS()для получения желаемого результата}

С помощью Rough Listпростого размещения этой формулы в любой пустой ячейке, где Listуказано имя Table:

=LET(
     _Uniq, UNIQUE(List),
     _Output, SORT(HSTACK(_Uniq, COUNTIF(List,_Uniq)),2,-1),
     VSTACK({"Nutrients","Counts"},_Output))

Проще с GROUPBY()функцией, если применимо:

=GROUPBY(List,List,ROWS,,0,-2)

Answer