excel中如何按自己要求用公式排列各列的顺序

Question

源表各列的顺序不同，目的表列的顺序固定，如何从源表到目的表。如图要求补充。我目的的另一个描述，就是我在汇总表的某一列录入”一、二、三、四“四个值的任一个，比如”二“，那下边自动出现相应的表一的值”B1、B2、B3“
再补充如图

Answer 1

设计工作表不规范，随意来，给自己带来无尽的麻烦。

要对这样一些不规范的工作表进行合并，用Excel2013版本及更高版本自带的Power Query（简称PQ）数据分析工具才是合适的。

先将要合并的表格转换为Excel中的“表格”，快捷方法是：光标定位到数据区域中，按Ctrl+T，Excel自动扩充选择数据连续区域，并弹出转换为表格对话框，一般直接按“确定”即完成转换。如图，表1、表2已完成转换，对表3进行转换时截图。

转换的表格在名称管理器中可以看到分别给了表1、表2、表3名称，编辑可以修改名称，但不能像其他定义的名称一样修改定义的区域范围

2. 完成数据区域转换为表格，对文件进行一次保存后，就可通过PQ来导入合并数据了，数据选项卡——获取和转换数据——获取数据——自文件——从工作簿，找到保存的工作簿，双击或点“导入”，将文件数据导入到PQ中

3. PQ进入数据导入导航器，勾选“选择多项”后，再勾选要导入的数据表格（这里是表1、表2、表3），最点“编辑”，进入PQ数据编辑界面。

4. 在PQ数据编辑器中，可以数据进行多种处理操作，甚至编写公式完成需要的数据处理操作（属于高级内容了）。现在我们需要进行的处理是对3个表格的列调整顺序，分别选择3个表格，点上面的列标后右键，移动，选择“向左移动”、“向右移动”、“移动到开头”、“移动到末尾”，来调整3个表的列顺序到一致。如图是调整表1的“三”到“四”的前面。PQ编辑器，右侧记录了所有的操作步骤，可以再回去进行修改或删除某一步骤，或插入步骤。

5. 3个表都调整到列顺序一致后，就对3个表追加为一个新的查询（也可以任何一个表为基础，追加另外两个表的查询，但这里为明确起见，追加为一个新的查询，并修改查询名称为“合并表格”），追问查询时，选择选择一个表格，如表1，勾选“三个或更多表格”后，在列出的表格中选择表2、表3（可以通过按Shift选择+鼠标连续多个或按Ctrl+鼠标选择不连续的表格），点“添加”，添加到右边的列表中。在属性中修改查询的名称为“合并表格”。

6. 可以看到亲的查询中数据已满足了合并的要求。现在剩下的就把数据导入回Excel工作表中。这时点文件菜单中的“关闭并上载”，这里有两个选项——“关闭并上载”：上载到新的工作表中；“关闭并上载到”：上载到启动PQ时的工作表中或新的工作表中（与“关闭并上载”等价）。无论是上载还是上载到，都会有以“表”或“数据透视表”、“透视图”、“仅连接”四个不同的选项。从本问题来说，只要“表”即可。但这里实际操作时有一个问题。直接选择“表”上载时，仍只能上载到新的工作表中。通过实际，发现先选择“数据透视表”的方式上载到当前工作表中，再对查询中的“合并查询”选择“加载到”，再选择“表”，就会弹出“删除已有数据透视表”警示框，确定后，删除数据透视表，并可以“表”的方式上载查询后的数据了（下面的截图省去了中间先插入数据透视表的过程，直接是上载数据的结果）

直接上载为“表”时，现有工作表是灰色不可选择的

如上图所示，直接上载选择“表”时，现在工作表灰色不可选。而选择“数据透视表”时，可选择“现有工作表”。

7. 这样合并的表格不是简单的合并，而是与源表建立了链接的。源表中的数据修改（包括删除、添加、修改内容等），均可通过“刷新”来更新合并的表格。

如图，表1中修改了内容、表2中增加了行，表3中删除了行。保存后，合并后的工作表并没有变化，但光标在表格中任意单元格中右击后，选择“刷新”后，数据就会刷新到与源表一致。

Answer 2

如图，F3公式：=IFERROR(HLOOKUP(F$2,IF(COUNTA(F$2:F2)<4,$A$2:$D$5,IF(COUNTA(F$2:F2)<7,$A$10:$D$13,$A$18:$D$22)),IF(COUNTA(F$2:F2)>=7,ROW(A2)-6,IF(COUNTA(F$2:F2)>=4,ROW(A2)-3,ROW(A2))),0),"")

右拉到I3后整体下拉。

追问

不好意思，表一、表二、表三只是举个例，说明表的各列顺序都可能不一样。所以我要的公式仅仅是在汇总表F2单元格录入“一”时，下面F3:F100出现表一A3：A100的值，如果是录入的是"二",下面出现的是B列的值。

Answer 3

在F2中输入或复制粘贴下列公式

=HLOOKUP($F$1,$A$1:$D$11,ROW(A2),0)

或

=INDEX($A$2:$D$11,ROW(A1),MATCH($F$1,$A$1:$D$1,0))

下拉填充

本回答被提问者采纳

Answer 4

提问说是要用公式……
请问公式是依据什么来操作的，应该是需要单元格的定位操作的吧，所以必须要有表格的行列标号，没有数据结构和行列标号，谁能做出公式来？？？？
而且，你目前的信息数据，都不知道最终真实的基础数据意义是什么，有些事直接用真实的数据操作也许是很简单的一个事，但是你这样一个不明所以的一个举例，把简单的事情复杂化，真是没有办法给你回答了……
也许只能由你自己去研究想要的公式吧……追问

不复杂啊，就是我在新的一列标题行处录入相关标题，然后下边的数据自动匹配。
就比如一个表A列标题是姓名，B列标题是学历，在我c列标题行处录姓名或学历，C列其他单元格录入公式，当录入姓名时，下面出现的数据与A列一样，如果将姓名改成学历，下面相应出现与B列一样。
这样的结果是，我可以将表格调成我需要的列顺序。

追答

有了行列标号，公式就简单了，F2单元格输入公式：=INDEX($A$2:$D$11,ROW()-1,MATCH($F$1,$A$1:$D$1,0))，复制并下拉，即可。

Answer 5

用总表的（eg：一）做目标查找，然后顺序返还表一中（eg：一）的“一”列下的数值至最终空白行，然后再表二中查找，依次返回，再标三查找，依次返回。
用宏对表头进行循环，然后顺序输出返回值可能更方便简单些。