☰

编辑

tag-icon • tag-icon worksheet-function microsoft-excel-2010 gis
编辑

我是一家地籍测绘公司的 GIS 专家。

我正在运行 Office 2010，并尝试在 Excel 中构建一个函数，将 16 位包裹识别号 (PID) 中的区域、乡镇和范围派生到单独的列中。

PID 以调查乡镇的 3 位数字开头，后跟 2 位区号数字。但是，“调查乡镇”不应与 PLSS 的乡镇和范围混淆。但是，每个“调查乡镇”都位于 PLSS 中单个乡镇和范围方格的网格内。

每个测量乡镇由 9 张“地图”组成，这些地图来自旧时的米拉地图，每张地图包含 4 个部分。“测量乡镇”中有 36 个部分。9 张地图，每张 4 个部分，就是 36 个。

SO.  PID 0241800000001000 

Survey township: 02
Map sheet:       4
Section:         18

PLSS township: 04
PLSS range   : 06

到目前为止。我可以轻松收集部分编号。我现在正在研究乡镇和范围。我在 Sheet2 中创建了一个参考列表，列出了县内的每个地图表（011-209）。该县有 20 个调查乡镇。公式需要参考 Sheet 2 上的列表，如果调查乡镇 2 的 PID 的前 3 个字符是 021-029，则乡镇单元的输出应为乡镇 4 南部的 (04S)。调查乡镇范围 (021-029) 和 PLSS 乡镇 (04S) 输出是我的变量，我将根据县进行更改。

以下是我目前所掌握的信息：

=IF(LEFT(B2,3) = ("Sheet2!A1:A9"),"05"), IF(LEFT(B2,3) = ("Sheet2!B1:B9"), "04")

在第 2 页上，

  • A1:A9 引用调查乡镇（011-019）的地图表列表
  • B1:B9 引用调查乡镇 02（021-029）的地图表列表

B2 是 PID 位置。

输出一直告诉我“FALSE”或 !VALUE#。或者失败并出现错误。

答案1

除了赢得“最令人困惑的问题陈述”（请阅读@Scott 的评论），我认为您想将 PID/township 值与列表中的列表进行匹配Sheet2。

是IF(LEFT(B2,3) = ("Sheet2!A1:A9"),"05")错误的，但我可以看出您想要获得"04"或"05"。

不要将这些“组”放在单独的列中，而是尝试将 PID 的所有可能值放在列中A（仍然是Sheet2）。现在，将您想要的这些值的结果放在列中B。应该看起来像：

 A  |  B
----+----
011 | 04
012 | 04
013 | 04
014 | 04
015 | 04
016 | 04
017 | 04
018 | 04
019 | 04
020 | 
021 | 05
022 | 05
023 | 05
024 | 05
025 | 05
026 | 05
027 | 05
028 | 05
029 | 05

现在你有了一个查找表。以下公式应该可以得到你想要的结果：

=INDEX(Sheet2!$B$1:$B$19,MATCH(LEFT(B2,3),Sheet2!$A$1:$A$19,0))

是的，表格在这里可以成为你的好朋友（尤其是在Sheet2）。但同样，我没有表格的示例。很乐意提供更多帮助，但我们需要更多信息。

答案2

再次阅读描述，寻找我之前问题的答案，我得出结论，每个调查乡镇不仅属于 PLSS 中单个乡镇和范围方格的网格，而且除了命名之外，还与之匹配。因此，调查乡镇 02 的所有 9 张纸都对应于乡镇 04S 范围 06W（或 06E）的旧 9 张纸。由于您说当前县有 20 个调查乡镇，我创建了一个假设的国家“地图”，显示了 20 个调查乡镇在 PLSS 网格上的叠加。（由于您有地图，并且知道该地区，您可以调整我的示例以适应。）我让这张假设地图跨越基线和子午线，以便您看到它在那种情况下的应用。

这是我用于示例的“地图”。

在此处输入图片描述

将该地图转换为 Sheet2 的数据将生成以下工作表：

在此处输入图片描述

使用 PID 0241800000001000 的示例数据，我将其创建为 Sheet1：

在此处输入图片描述

单元格的公式B2为=LEFT(A2,2)，从PID中提取调查乡镇编号。

单元格的公式C2是=MID(A2,3,1)，从 PID 中提取地图表。

单元格的公式D2为=MID(A2,4,2)，从 PID 中提取部分编号。

使用最后一个公式作为指导，您应该能够提取 PID 中位置可预测的任何其他部分。我猜剩下的 10 位数字分为两组，每组 5 位，分别表示分区角以东和以北的分区。（我想，如果您所在的地方仍然使用该系统，或者甚至 5 位数字也能容纳英寸，那么 8,000 个 Gunter 链接会起作用。）

单元格的公式E2是=VLOOKUP(VALUE(B2),Sheet2!$A$2:$C$21,2)，查找Sheet2 的B2列中单元格的值（数字）A并返回同一行中第二列的值。笔记，这是需要注意的一点！Sheet2 的索引列中的值必须是数字，而不是文本。因此，它只能是 而不是“02” 2。这可能更好，因为当您输入数字时，Excel 会将它们存储为数字，而不是文本，除非您使用引号或显式单元格格式强制执行此问题。

单元格的公式F2为=VLOOKUP(VALUE(B2),Sheet2!$A$2:$C$21,3)，除了它是第三列之外，其工作原理与单元格中的公式相同E2。

修改 Sheet2 中的数据以及 Sheet1 的 E 和 F 列中对它的引用，应该允许您将此数据重新用于您所在州的任何县，只要它们使用与 PID 相同的系统即可。对于我误解的几点，我希望您能够调整此处给出的概念来纠正它们。

编辑

要对多个县或调查乡镇集使用相同的系统，请修改 Sheet2，在开头插入两列。这是您要用于调查乡镇集的县或其他名称的列表，以及用于查找 PLSS 数据的相应“范围”。此列表必须在添加其他集合时保持按字母顺序排列。每个县都有自己的三列集合。这些表不需要从左到右堆叠。它们可以按照最适合您的工作流程的方式排列。它们也不需要按任何顺序排列，只有列中的名称A必须按顺序排列。这是一个包含 3 个集合的示例，我仅对它们进行了着色，以便清楚地显示它们的分解方式。

在此处输入图片描述

这是这些集合的样本“地图”。我包括了一些重叠区域，因为我知道并非所有县界都会遵循方便绘制的 PLSS 边界线。因此，我猜测两个不同的测量乡镇可能对应于一个共同的 PLSS 乡镇/范围。

在此处输入图片描述

在第一个工作表中Sheet1，插入一个新列，即县名列A。示例如下所示。

在此处输入图片描述

此列中使用的名称必须与 的第一列中使用的名称完全匹配，Sheet2但可以按任意顺序使用。 的公式Sheet1需要根据两张表的变化进行调整，并引用县名。

单元格 C2 的公式是=LEFT(B2,2)。

单元格 D2 的公式是=MID(B2,3,1)。

单元格 E2 的公式是=MID(B2,4,2)。

单元格 F2 的公式为

`=VLOOKUP(VALUE(C2),INDIRECT("Sheet2!"&VLOOKUP(A2,Sheet2!$A$2:$B$100,2)),2)`.

单元格 G2 的公式为

`=VLOOKUP(VALUE(C2),INDIRECT("Sheet2!"&VLOOKUP(A2,Sheet2!$A$2:$B$100,2)),3)`.

在单元格中F2，G2公式包括范围Sheet2!$A$2:$B$100，其中100只是一个数字，用于表示许多县（实际上为 99）。例如，如果您所在的州有 120 个县，并且您需要覆盖所有县，请将 更改100为。121

“魔法”发生在最后两个单元格中，使用两个函数VLOOKUP和INDIRECT。内部函数使用列VLOOKUP中的县名来查找要使用的范围。这与其他字符串部分相结合以创建表中的完整引用。该函数获取该字符串并将其转换为外部函数可以使用的引用。Sheet1ASheet2Sheet2INDIRECTVLOOKUP

如果需要，您可以使用单独的工作表来覆盖不同的州。要使其工作，请将Sheet2单元格F2和G2中的引用更改为Sheet3，或新的工作表名称。您还可以重命名工作表，即使只有 2 个，也可以将其重命名为更好的名称。同样，您需要将引用更改Sheet2为新名称。（每行总共 4 个替换，F和中各两个G。）如果两个州有相同的县名，这没关系，因为将在该州工作表的列表中查找该名称，并引用其自己的表格，而不是另一个州的同名县。

我最初忘记提到的一点是，PID 的单元格格式Sheet1必须设置为文本，否则它将被理解为数字，Excel 将删除前导零，并且接下来 3 列中的字符串操作函数将失败，这会导致最后两列失败，甚至错误。

对于使用 Linux 的用户，需要额外注意的是，!工作表引用中的VLOOKUP需要改为句点，.，才能使其在 LibreOffice Calc 中正常工作，但其余部分保持原样。

相关内容