16
Excel 提供的 SVerweis 函数是一个多功能功能，用于搜索查找范围中的第一列。
Excel SVERWEIS——有针对性地使用，工作更干净利落
Excel 中的 SVERWEIS 是从大型表格中提取单个值时最常用的函数之一。为了确保操作可靠，仅使用正确的公式还不够，还需要注意数据的结构并仔细检查错误来源。
- 矩阵必须从左侧开始：SVERWEIS 仅搜索所选区域的第一个列。例如，如果客户编号位于 D 列，则搜索区域也必须从该列开始，即不是“A:F”，而是“D:F”。第一个列始终用作比较基准。如果忽略了这一点，就会得到错误的结果甚至没有结果。
- 使用表格对象更稳定地工作：将数据设置为“表格”格式（快捷键：Ctrl + T）后，Excel 会自动响应后续的扩展。即使您添加了新行，SVERWEIS 公式仍然有效。公式 =SVERWEIS(A2;Tabelle1;3;FALSCH) 能够准确访问更新后的数据——非常适合不断增长的列表。
- 使用 FALSCH 代替 WAHR——有针对性地控制搜索行为： 如果没有精确匹配，可能会得到不准确的结果。公式的第四位（即区域引用）几乎应始终设置为 FALSCH。只有这样，Excel 才会检查是否完全匹配。WAHR 仅适用于已排序的数据，而日常工作中很难确保数据完全排序。
- 检查并统一数据格式：文本格式的数字或数字格式的数字——两者看起来相同，但行为不同。如果 SVERWEIS 完全找不到匹配项，尽管该值在列表中存在，则几乎总是存在格式冲突。使用 =ISTTEXT(A2) 或 =ISTZAHL(A2) 可以快速找到问题所在。使用 =GLÄTTEN() 或 =WERT() 等工具可自动清理。
- 有针对性地使用绝对和相对单元格引用：复制公式时，单元格引用也会随之更改。对于矩阵数据，这可能会造成严重后果。因此，请使用 $ 符号（例如 $A$2:$D$100）来表示绝对单元格引用，以便将公式从上到下或横跨整个表格可靠地拖动，而不会“偏移”区域。
- 优雅地捕获错误： 没有人希望在系列信函或仪表板中看到“NV”。将 SVERWEIS 与 WENNFEHLER 结合使用，以生成替代输出：=WENNFEHLER(SVERWEIS(A2;Tabelle1;3;FALSCH); “未找到”)
- 这并不是为了美观，而是为了功能——例如在导出或自动化数据处理过程中。
- 了解 SVERWEIS 的限制：一旦列移动或表结构发生变化，SVERWEIS 就会返回错误的结果。反向搜索（从右到左）也不行。在这种情况下，建议使用 XVERWEIS 或 INDEX/VERGLEICH。
XVERWEIS、INDEX & Co.——控制力更强的替代方案
自 Microsoft 365 和 Excel 2021 以来，XVERWEIS 提供了一种更强大的替代方案，克服了 SVERWEIS 的许多弱点。INDEX/VERGLEICH 的组合也被认为非常灵活，特别是在 Excel 版本或要求发生变化时。
- XVERWEIS：无需绕道，直接反向搜索：最大的区别在于：XVERWEIS 还允许从搜索词左侧的列中返回结果。例如：=XVERWEIS(B2;Tabelle1[Name];Tabelle1[Kundennummer];“Kein Treffer”）。这样，即使表的结构不理想，也可以搜索表中的内容。SVERWEIS 在这里会失败。
- 列索引被省略——更少出错：在 XVERWEIS 中，您需要指定两个具体的单元格区域：搜索区域和返回区域。您无需像在 SVERWEIS 中那样考虑计数（第 3 列、第 4 列等）。这减少了错误，提高了可读性。
- 有针对性地控制错误消息：在 XVERWEIS 中，第四个参数“wenn_nicht_gefunden”取代了 WENNFEHLER 的绕道。易读性强、打字错误少、直接访问——该函数对空单元格或错误值的反应更敏捷。
- 控制搜索方向和比较行为：XVERWEIS 提供了两个附加参数： “比较模式”（0、-1、1）和“搜索模式”（1 从上到下、-1 从下到上）。这样就可以有针对性地搜索价格等级或重复条目，而这是 SVERWEIS 无法做到的。
- XVERWEIS 在移动设备和网页上的使用：在 Microsoft Excel Mobile 中，XVERWEIS 基本上可以正常使用，但复杂的动态公式会受到限制。在 Excel Online 中，只要单元格格式正确，SVERWEIS 和 XVERWEIS 都能稳定运行。在移动设备上，建议使用简单的结构，因为某些控件缺失。
- INDEX/VERGLEICH：多功能且与版本无关：INDEX 获取值，VERGLEICH 搜索合适的位置。公式示例：=INDEX(D2:D100;VERGLEICH(A2;B2:B100;0))
与 SVERWEIS 不同，当您重新排列列时，此组合不会中断。它适用于复杂的计算或 XVERWEIS 不可用时（例如 Excel 2016）。
处理大量数据的性能：与 SVERWEIS 相比，使用 INDEX/VERGLEICH 或 XVERWEIS 可以更快地完成处理数千条数据集的工作。这两种函数都能更高效地处理大量数据，特别是在动态仪表板或从第三方系统导出数据时。
实践示例——SVERWEIS 逐步操作
许多人每天都在使用 Excel，但在使用 SVERWEIS 时，经常会出现误解——尤其是当您不经常使用公式时。以下示例展示了如何通过清晰的结构和几个步骤创建一个有效的 SVERWEIS。目标是从客户列表中自动获取相应的电话号码。
- 构建源表：创建一个包含三列的表：“客户编号”、“姓名”和“电话号码”。每行包含一个数据记录。重要提示：包含要查找值的列（在本例中为客户编号）位于最左侧。SVERWEIS 始终只与搜索区域中的第一列进行比较。示例：“1002 Langer 0157 9876543”
- 设置第二个查询表：在右侧（或另一个工作表中）创建一个包含两列的小列表：“客户编号”和“电话号码”。在“客户编号”列中输入要搜索的值，例如“1002”。第二列暂时留空，稍后会自动填充。
- 正确构建 SVERWEIS：点击要显示结果的单元格，例如单元格 F2。现在输入以下公式：=SVERWEIS(E2;A2:C100;3;FALSCH)
- 该公式的含义是：在区域 A2 到 C100 的第一列中查找单元格 E2 的值（此处为“1002”）。然后返回同一行第三列（C）中的值。末尾的“FALSCH”确保只接受完全匹配的结果。
- 测试公式并检查结果：按回车键后，Excel 会自动在 F2 单元格中显示“0157 9876543”——即所查找客户的电话号码。更改 E2 中的值（例如更改为“1003”），F2 中的结果也会自动更改。该公式会根据输入值动态调整。
- 避免常见错误：检查搜索区域中的数字是否确实以数字格式显示。通常，“NV”错误的原因是 E2 中的值以数字格式显示，但在源表中以文本格式显示。这在视觉上几乎看不出来，但会带来技术上的后果。您可以使用“=ISTTEXT(A2)”进行检查。如有必要，可以使用“=WERT()”来更正单元格内容。
- 当使用多行时，固定单元格引用： 将公式向下拖动以查询更多客户，然后用美元符号固定区域：=SVERWEIS(E2;$A$2:$C$100;3;FALSCH)
这样，即使复制或向下拖动公式，搜索区域也不会改变。
捕获错误消息，改进输出
为了在“NV”的后面显示“未找到客户”这样的易懂消息，请在公式中添加以下内容：=WENNFEHLER(SVERWEIS(E2;$A$2:$C$100;3;FALSCH); “未找到客户”)
这样，表格看起来更整洁，用户可以直接看到没有结果。
