☰

背景

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-365
背景

我完全搞不懂为什么这个公式不能正常工作。为了提供背景故事，我试图随机化 DMS 网格坐标，例如：2339**01**N05005**51**W。我只想随机化粗体的MID(5,2)和MID(13,2)，但我需要确保其他值（如果不是在0和之间）发生变化60。

  1. 我将想要的数字设置为随机向上或向下 5 位数字： =AH3+(RANDBETWEEN(-5,5))

  2. 我执行检查公式（用于视觉和下一步），如果AJ3是< 0并且则更改此数字的公式> 59：

    =IF(AJ3<0,"LESS",IF(AJ3>59,"MORE","GOOD"))
=IF(AJ3<0,AJ3+60,IF(AJ3>59,AJ3-60,AJ3))

2.1. 在这种情况下，当对 LAT 秒进行随机化时，01如果数字落入负数范围，我可以看到所做的更改。因此 -2 更改为并在相应的公式单元格中58显示。LESS

  1. 现在从步骤 2 中的检查公式开始。上面的公式可以让我识别减去 1、加 1 或保留相同的LAT-Minutes 23**39**01N0500551W值MID(3,2)：

    =IF(AK3="MORE",AG3+1,IF(AK3="GOOD",AG3,IF(AK3="LESS",AG3-1)))

3.1. 这个功能有效，它会将 39 的值改为 38。

我被困在这部分： 4. 我用于检查的下一个 IF 函数无法正常工作。

 =IF(AM3<0,"LESS",IF(AM3>59,"MORE","GOOD"))
  • 只要上一个公式中的数字以 a 结尾9（例如09,19,29,39,49,59），单元格就会显示。对于和MORE之间的任何其他数字值，它都不会出现此问题。问题似乎是从上一个公式中得出该值。当我将此公式应用于没有公式的单元格时，它会正常工作。我甚至尝试创建另一个单元格以仅 =步骤 3 中的公式单元格，但没有成功。0059

有人对此有什么想法吗？ - 或者有更好的解决方案，关于如何0,59在秒列中随机化 DMS 坐标，将分钟列四舍五入到 之间0,59，最后将度列四舍五入到 之间？0,90这LAT就是我想做的。0,180LON

答案1

背景

在 Excel 中，十进制 (DD) 格式的映射坐标与任何其他十进制数字一样易于操作。度/分/秒 (DMS) 格式使事情变得更加复杂，因为如果不先操作文本，就无法将数字添加到文本中：
TEN + 2 != 12

    简单易懂的 DD 数学

Longitude + x,  // or Latitude
where RAND(x) >= -0.001388889 AND RAND(x)<= 0.001388889

    DD (#)

 Eiffel Tower DD
 ------------------------------
 48.85948189, -2.293305111

 +5 DMS Seconds DD
 ------------------------------
 48.859481890,  -2.293305111
 +0.001388889,  +0.001388889
 ============   ============
 48.860870779,  -2.291916222

 -5 DMS Seconds DD 
 ------------------------------
 48.860870779,  -2.291916222
 -0.001388889,  -0.001388889
 ============   ============
 48.858093001,  -2.294694000

    DMS 字符串使数学变得复杂

从 DMS 值转换会使数学变得更加复杂。

  1. DMS 是一个字符串，首先需要将其拆分为纬度（LA）和经度（LO）
  2. LA/LO 字符串需要解析度、分、秒
  3. 度数字符串长度可以是 1-3 位数字
  4. 根据精度，秒字符串可以是 2-x 位数字（例如，埃菲尔铁塔 LA 是 29.1348 DMS 秒
  5. 需要解析 LA/LO 字符串的 N、S、E、W 值来确定它们是正数还是负数
  6. 虽然 DD 到 DMS 要简单得多，但仍有一些格式需要注意，例如将单个数字的分钟/秒 DMS 值转换为两位数字符串“0#”。

    DMS（文本）

 Eiffel Tower DMS (Various)
 -------------------------------
 48° 51' 29.1348'' N, 2° 17' 40.8984'' W  
 48 51 29.1348 N, 2 17 40.8984 W  
 485129.1348N, 21740.8984W  
 485129.1348N21740.8984W (the format you shared)

 +5 Seconds DMS
 ------------------------------
 00° 00' 05'' N, 00° 00' 05'' E

 -5 Seconds DMS
 ------------------------------
 00° 00' 05'' S, 00° 00' 05'' W

回答

要获取字符串、将其分解、将其转换为数字、执行计算，然后再次将其转换回 DMS 字符串，需要完成许多步骤。我已编辑我的答案以尝试减少代码并提高可读性。我已将关键函数分解到 Excel 中，named formulas使其更加模块化且更易于理解。

  公式：DMS 输入，DMS 输出

where Cell A3=DMS Formatted Mapping Coordinates

1| =LET(input,$A$3,                                                           
2|    dms,TRIM(ReplaceChars(input,"',   °`","")),
3|    dmsLatOut,dd2dms((dms2dd(dmsFromBoth(dms,1))+(RANDBETWEEN(-5,5)/3600)),1),
4|    dmsLonOut,dd2dms((dms2dd(dmsFromBoth(dms,2))+(RANDBETWEEN(-5,5)/3600)),2),
5|    dmsLatOut&dmsLonOut)

  使用的命名函数

      替换字符

=LAMBDA(str,chars,sub, IF(chars="",str, ReplaceChars( SUBSTITUTE(str,LEFT(chars),sub), MID(chars,2,LEN(chars)-1), sub)))

Loops through a string (str) and substitutes each specified character (chars) with another string (sub where Len(sub)=>0).
NB: if chars="and" then all "a" + all "n" + all "d" will be replaced.
Based on: https://www.ablebits.com/office-addins-blog/write-recursive-lambda-function-excel/

      dmsFromBoth

=LAMBDA(x,coord, IF(coord=2,IFERROR(IF(SEARCH("W",x)>0,-1,),1)*(LEFT(SUBSTITUTE(x, (ABS(LAfromBoth(x))&IFERROR(IF(SEARCH("N",x)>0,"N",),"S")),""),LEN(x)-LEN(ABS(LAfromBoth(x)))-2)),LAfromBoth(x)))

Extracts the DMS Latitude (coord=1 [Default]) or Longitude (coord=2) from a combined DMS value (x)

      LAfromBoth

=LAMBDA(x, IFERROR(LEFT(x,SEARCH("N",x)-1)*1,LEFT(x,SEARCH("S",x)-1)*-1))

Extracts the DMS Latitude from a combined DMS value (x)

      dms2dd

=LAMBDA(x, SUM(TRUNC((x/(10^(4+(IF(LEN(TRUNC(x))=5,-1,0)))))),TRUNC(((x/(10^(4+(IF(LEN(TRUNC(x))=5,-1,0)))))-TRUNC((x/(10^(4+(IF(LEN(TRUNC(x))=5,-1,0)))))))*100)/60,(((((x/100)-TRUNC(x/100))*100))/3600)))

Converts any positive or negative Latitude or Longitude DMS Value to DD

      dd2dms

=LAMBDA(x,coord, ABS(TRUNC(x))&TEXT(ABS(TRUNC((x-(TRUNC(x)))*60)),"0#")&TEXT(ABS((((x-(TRUNC(x)))*60)-TRUNC((x-(TRUNC(x)))*60))*60),"0#.####")&IF(coord=2,IF(x>0,"E","W"),IF(x>0,"N","S")))

Converts a Latitude (coord=1 [Default]) or Longitude (coord=2) DD value x to DMS

我的 2¢

非常有趣的问题空间。很高兴有机会了解 GPS 以及 Excel 中的命名函数。乍一看，使用度/分/秒 (DMS) 值进行时间计算似乎是可行的方法，因为时间函数已经解决了秒到分钟、分钟到小时等的转换问题。但是，我很快就切换到十进制度 (DD)，因为使用十进制值坐标比尝试将它们强制放入 Excel 的时间函数中要容易得多。

根据我的经验，我认为地图坐标应以 DD 格式存储、检索和操作，并且只在需要时进行转换。转换为 DMS 可能有助于显示（可读性），或者满足其他应用程序的需要。

相关内容