【原】Excel 函数公式 数据转置神来之笔辅助列

快速浏览

往期合集：【2023年3月】【2023年4月】【2023年5月】【2023年6月】

实用案例：|收费管理系统|中医诊所收费系统|日期控件|简单的收发存|电子发票管理助手|Excel表格拆分神器|

内容提要

• FREQUENCY函数取得项目出现次数
• INDEX/MATCH/COUNTIF结合取得不重复值、唯一值
• 借助辅助列实现数据查找匹配

大家好，我是冷水泡茶，今天在EXCELHOME论坛上看到一个网友求助贴：

他的原表是这样：

希望做成这样：

他是发在VBA版块，想用VBA解决，也有不少热心的网友给出代码，但我仔细研究了以后，觉得不用VBA，只用函数公式也能实现：

下面我们就一起来看一下制作过程吧！

设置表格格式

我们先把他的原表稍微整理一下：

1、D1单元格添加字段名称“项目”，D2以下单元格设置公式（他原来没有公式，看他的数据规则，是把B列和C列合并起来）：

D2=IF(C2="",B2,B2&","&C2)

2、据楼主所言，D列是他后加的，我们再加一个辅助列“点餐数":

E2=A2&"点餐" &COUNTIF($A$2:A2,A2)

3、添加几个名称：点餐数、客人号码、项目

设置取数公式

我们插入一张表，改名为“转置”

1、在A1单元格输入“客人号码”

2、在B1单元格输入公式，并向右拖动，直到出现空格为止（还可以再拖几格）：

=IF(COLUMN(A1)>MAX(FREQUENCY(客人号码,客人号码)),"","点餐"&COLUMN(A1))

（1）通过COLUMN函数产生数字序列

（2）通过FREQUENCY函数得出客人号码出现的次数，通过MAX函数取得最大次数，超过最大次数则单元格值为空。

（3）不超过最大次数，数字序列前面加“点餐”，形成表头

3、在A2单元格输入公式，并向下拖动，直到出现空格为止（还可以再拖几格）：

{=IFERROR(INDEX(客人号码,MATCH(0,COUNTIF($A$1:A2,客人号码),0)),"")}

这个公式我们在Excel 功能/公式函数/VBA/多种姿势处理重复值中分享过，有详细解析，这里就不啰嗦了。

4、在B2单元格输入公式，并向下、向右拖动，只到出现整行、整列均为空格为止（还可以再拖几格，与表头，A列的公式对应）：

=IFERROR(INDEX(项目,MATCH($A2&B$1,点餐数,0)),"")

这个公式很简单，查找关键字$A2&B$1在名称“点餐数”中的位置，并返回名称“项目”中对应位置的值。

5、有数据更新时，检查第一行最后有公式列是否为空，不为空要向右再拖一列，直到出现空格为止。检查A列最下面有公式行是否为空，不为空要向下拖动，直至出现空格为止。

6、相应把数据区域的公式对应填充，以确保没有数据遗漏。

正文完