我们有一个示例 销售数据集 包含 “产品”, “数量”、“单价” 和 “总销售额” 单元格中显示的列 B4:E18。我们希望使用 表格功能 和组合功能。
方法1 – 根据单元格值创建动态列表让我们构建一个基于给定标准的动态列表。我们将使用INDEX、MATCH、OFFSET、COUNTA、SMALL、IF、INDIRECT、COUNTIF 和 ROW 函数来获取“Product”名称其“总销售额”值超过“$50”。
步骤：
在单元格G5中>>输入下面给出的公式>>单击ENTER。=INDEX(OFFSET($B$5,0,0,COUNTA(B:B)-1,1),MATCH(SMALL(IF(OFFSET($E$5,0,0,COUNTA(E:E)-1,1)>=50,OFFSET($E$5,0,0,COUNTA(E:E)-1,1),""),ROW(A3:INDIRECT("A"&COUNTIF(E:E,">=50")))),OFFSET($E$5,0,0,COUNTA(E:E)-1,1),0),1)
OFFSET($E$5,0,0,COUNTA(E:E)-1,1) → 返回指定行和列中的一系列单元格。其中 $E$5 是参考参数，0 是行 >参数告诉函数从初始引用移动多少行$E$5 和0 代表列 参数，指定从起始点开始的列。 COUNTA(E:E)-1 是可选的高度参数，它计算区域中非空白单元格的数量，而1 是可选的宽度参数。
输出 → {64;69;40;128;142;180;114;110;66;68;78;123;82;272} COUNTIF(E:E,”>=50″) → 计算范围内满足给定条件的单元格数量。 E:E 单元格代表引用“总销售额”、“> 的范围参数” =50” 表示返回匹配值计数的条件参数。我们采用了整个 E 列 (E:E)，而不是 E5:E18 单元格来使列表动态化。
输出 → 13INDIRECT(“A”&COUNTIF(E:E,”>=50″)) → 返回由文本字符串指定的引用。 “A”&COUNTIF(E:E,”>=50″) 是 ref_text 参数。
输出 → 0ROW(A3:INDIRECT(“A”&COUNTIF(E:E,”>=50″))) → 返回行的序列号。
输出 → {3;4;5;6;7;8;9;10;11;12;13}IF(OFFSET($E$5,0,0,COUNTA(E:E)-1,1)>=50,OFFSET($E$5,0,0,COUNTA(E:E)-1,1 ),””) → 检查是否满足条件，如果TRUE则返回一个值，如果FALSE则返回另一个值。 $E$5,0,0,COUNTA(E:E)-1,1)>=50 是选择的逻辑_测试参数“总销售额”值高于“50 美元”。如果此条件成立 TRUE ，则函数返回 OFFSET($E$5,0,0,COUNTA(E:E)-1,1) (value_if_true 参数），否则返回“ ”（空白）（value_if_false 参数）。
输出 → {64;69;””;128;142;180;114;110;66;68;78;123;82;272 }小(IF(偏移($E$5,0,0,COUNTA(E:E)-1,1)>=50,偏移($E$5,0,0,COUNTA(E:E)-1 ,1),””),ROW(A3:INDIRECT(“A”&COUNTIF(E:E,”>=50″)))) → 变为
小({64;69;””;128;142;180;114;110;66;68;78;123;82;272},{3;4;5;6;7;8;9 ;10;11;12;13}) → 返回数据集中第 k 个最小值。这里，{64;69;””;128;142;180;114;110;66;68;78;123;82;272} 是数组 参数，而 {3;4;5;6;7;8;9;10;11;12;13} 是 k 参数。输出 → {68;69;78;82;110;114;123;128;142;180;272}匹配(小(IF(偏移($E$5,0,0,COUNTA(E:E)-1,1)>=50,偏移($E$5,0,0,COUNTA(E:E)) -1,1),””),ROW(A3:INDIRECT(“A”&COUNTIF(E:E,”>=50″)))),OFFSET($E$5,0,0,COUNTA(E:E) )-1,1),0) → 变成
匹配({68;69;78;82;110;114;123;128;142;180;272},{64;69;40;128;142;180;114;110;66;68; 78;123;82;272},0) → 返回数组中与给定值匹配的项目的相对位置。这里，{68;69;78;82;110;114;123;128;142;180;272} 是 lookup_value 参数而{64;69;40;128;142;180;114;110;66;68;78;123;82;272}代表lookup_array 值匹配的参数。 0 是可选的 match_type 参数，它指示完全匹配条件。输出 → {10;2;11;13;8;7;12;4;5;6;14}OFFSET($B$5,0,0,COUNTA(B:B)-1,1) → $B$5 是参考参数，0 是行参数，它告诉函数从初始引用移动多少行 $E$5 和0 代表列参数，指定从起始点开始的列。 COUNTA(B:B)-1 是可选的高度参数，1是可选的<宽度参数。
输出 → {“巧克力片”;“全麦”;“竹芋”;“大米”;“胡萝卜”;“谷物”;“燕麦片”葡萄干”;“什锦水果”;“椒盐卷饼”;“果冻”;“麸皮”;“生燕麦片”;“胡萝卜沙拉”;“肉”;0;“状况”}INDEX(OFFSET($B$5,0,0,COUNTA(B:B)-1,1),MATCH(小(IF(OFFSET($E$5,0,0,COUNTA(E:E)-)) 1,1)>=50,OFFSET($E$5,0,0,COUNTA(E:E)-1,1),””),ROW(A3:INDIRECT(“A”&COUNTIF(E:E,” >=50″)))),OFFSET($E$5,0,0,COUNTA(E:E)-1,1),0),1) → 变为
INDEX({“巧克力片”;“全麦”;“竹芋”;“大米”;“胡萝卜”;“麦片”;“燕麦葡萄干”;“什锦水果”;“椒盐卷饼”;“果冻”; ”麸皮”;“生燕麦片”;“胡萝卜沙拉”;“肉”;0;“状况”},{10;2;11;13;8;7;12;4;5;6;14},1 ) → 返回给定范围内行和列的交集处的值。 ({“巧克力片”;“全麦”;“竹芋”;“大米”;“胡萝卜”;“麦片”;“燕麦葡萄干”;“什锦水果”;“椒盐卷饼”;“果冻”;” Bran”;“Oatmeal Raw”;“Carrot Salad”;“Meat”;0;“Condition”} 是数组参数，它们是“产品”名称{10;2;11;13;8;7;12;4;5;6;14}是row_num 指示行位置的参数1是可选的column_num参数，它指向列位置。
方法 2 – 生成具有多个条件的动态列表
我们想要提取在“数量”中销售超过“80”单位的“产品”名称，并生成“总销售额” 大于“100 美元”。
步骤：
选择单元格G10>>输入以下表达式>>按ENTER。=FILTER(OFFSET($B$5,0,0,COUNTA(E:E)-1,1),OFFSET($C$5,0,0,COUNTA(C:C)-1,1)>=80,OFFSET($E$5,0,0,COUNTA(E:E)-1,1)>=100)OFFSET($C$5,0,0,COUNTA(C:C)-1,1)>=80 → 函数返回 TRUE 或 FALSE 对于“数量”值大于“80”。
输出 → {FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE} OFFSET($E$5,0,0,COUNTA(E:E)-1,1)>=100 → 函数返回 TRUE 或 FALSE“总销售额”大于“100 美元”。
输出 → {FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE} 过滤器(偏移量($B$5,0,0,COUNTA(E:E)-1,1),偏移量($C$5,0,0,COUNTA(C:C)-1,1)>= 80,OFFSET($E$5,0,0,COUNTA(E:E)-1,1)>=100) → 过滤范围或数组。 OFFSET($B$5,0,0,COUNTA(E:E)-1,1) 是数组参数， OFFSET($C$5,0,0,COUNTA(C:C)-1,1)>=80 是包含参数。 OFFSET($E$5,0,0,COUNTA(E:E)-1,1)>=100 是可选的 if_empty 参数。该函数返回符合这两个条件的“产品名称”。
输出 → {“谷物”;“椒盐卷饼”;“肉类”}
方法 3 – 利用辅助列的数据验证功能步骤：
选择范围B5:C13>>单击CTRL + T键创建Excel表格>>按确定 。
转到单元格 H6>> 使用UNIQUE 函数返回B6:B13范围内的唯一值。=UNIQUE(B6:B13)B6:B13 单元格代表“Item” 列。
选择单元格E6>>导航到数据选项卡>>点击数据验证选项。
这将打开数据验证窗口。
在允许字段中，选择列表选项>>在源字段中输入以下公式>>点击确定 。$H$6#H6 单元格指向“Helper Column” 中的“Item”，而 #（Hashtag ）标志表示溢出范围。
单击向下箭头>>从列表中选择“项目”，这里我们选择“汽车”。
转到单元格 I6>> 插入 FILTER 函数来过滤数组。显示与每个“项目”对应的“国家/地区”。=FILTER(C6:C13,B6:B13=E6)C6:C13 和 B6:B13 范围指向“Item”和“Country”列，而E6 单元格指的是从数据验证下拉列表中选择的“项目”。
输入以下公式，在 F6 单元格中插入另一个下拉列表。$I$6#单元格I6代表“帮助列”中的“国家/地区”和#（标签<）符号指的是溢出范围。
添加新数据会更新动态下拉列表，如下图所示。
方法 4 – 制作动态列表而不创建辅助列 步骤：
在单元格 B21>> 中输入以下公式，然后单击ENTER。=INDEX(B4:E18,SEQUENCE(5),SEQUENCE(1,4))索引(B4:E18,序列(5),序列(1,4))SEQUENCE(5) → 返回数字序列。 5 是行参数。
输出 → {1;2;3;4;5}SEQUENCE(1,4) → 这里，1是行参数，而4 > 是可选列参数。
输出 → {1;2;3;4}INDEX(B4:E18,SEQUENCE(5),SEQUENCE(1,4)) → 变为
INDEX(B4:E18,{1;2;3;4;5},{1;2;3;4}) → 返回给定行和列的交集处的值范围。 B4:E18 是表示数据集的数组参数，而{1;2;3;4;5} 是返回前 5 行的 row_num 参数，{1;2;3;4} 是可选的column_num参数，指示列的位置。
方法5 – 使用Excel表格和VLOOKUP函数步骤：
转到单元格E5>>将方程输入公式栏>>按ENTER>>拖动填充手柄 > 将公式复制到下面的单元格中的工具。=VLOOKUP(@C:C,$G$5:$H$13,2,FALSE)VLOOKUP(@C:C,$G$5:$H$13,2,FALSE) → @C:C ( lookup_value argument) 是从 $G$5:$H$13 (table_array argument) 数组映射的。 2（col_index_num 参数）表示查找值的列号。 FALSE（range_lookup 参数）指查找值的完全匹配。
输出 → PC
选择G4:H12数组>>按CTRL + T快捷键插入Excel表格>>按确定 。
添加新的“Item”及其“Item Code”，如下所示。
当我们向数据集中输入新数据时，Excel 会动态插入“项目代码”。