Excel公式可从单元格范围中获取值，按字母顺序排序并写为单个字符串 [英] Excel formula to take values from cell range, sort alphabetically and write as a single string

问题描述

我目前有一个翻译人员和他们会说的语言的列表:

I currently have a list of translators and the languages they can speak:

  |   A   |     B      |     C      |     D      |             F             |
  +-------+------------+------------+------------+---------------------------+
1 | Name  | Language 1 | Language 2 | Language 3 | Combined                  |
  +=======+============+============+============+===========================+
2 | John  | English    | Chinese    | Spanish    | English, Chinese, Spanish |
3 | Wendy | Chinese    | French     | English    | Chinese, French, English  |
4 | Peter | Spanish    | Chinese    | English    | Spanish, Chinese, English |

在F列中，我使用以下公式将每个人的所有语言组合在一起:

In column F I am using the following formula to combining all the languages for each person together:

=$B2&", "&$C2&", "&$D2

我正在数据透视表中使用此列来报告可以说相同语言组合的人.我的问题是，如果以不同的顺序输入语言(例如第2行和第4行)，则报告显示为不同的组合.我可以使用一个公式来提取这三种语言单元格，然后按字母顺序对它们进行排序，然后将其写成字符串吗?

I am using this column in a pivot table to report on people who can speak the same combination of languages. My problem is, if the languages are entered in a different order (e.g. row 2 and 4) the report shows up as a different combination. Is there a formula I can use to take the three language cells, sort them alphabetically and the write it out as a string?

希望这很清楚.

推荐答案

花了我一段时间，但我终于找到了公式解决方案.

Took me a while, but I finally figured out a formula solution.

将其放在单元格 E2

= CONCATENATE(INDEX(B2:D2,1，MATCH(SMALL(COUNTIF(B2:D2，<"& B2:D2)，1)，COUNTIF(B2:D2，<& B2:D2)，0))，"，，INDEX(B2:D2,1，MATCH(SMALL(COUNTIF(B2:D2，"<& B2:D2)，2)，COUNTIF(B2:D2，<"& B2:D2)，0))，，"，INDEX(B2:D2,1，MATCH(SMALL(COUNTIF(B2:D2，<"& B2:D2)，3)，COUNTIF(B2:D2，<"& B2:D2)，0))))

通过按 CTRL + SHIFT + ENTER

向下拖动公式.

这可以通过结合使用 COUNTIF()函数和 SMALL()函数将一个数组对三列进行排序来实现.然后我重复这3次，通过使用 SMALL()函数更改要显示的索引.很难理解，但是可以完成工作:)

This works by sorting the three columns with an array using the COUNTIF() function in conjunction with the SMALL() function. Then I repeat this 3 times, changing the index that I want to display by using the SMALL() function. It's a little hard to follow, but it gets the job done :)

以下是有关此类公式工作原理的更详细说明的链接:

Here's a link to a more detailed description of how a formula like this works:

http://www.get-digital-help.com/2009/03/27/sorting-text-cells-using-array-formula/

这篇关于Excel公式可从单元格范围中获取值，按字母顺序排序并写为单个字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！