我完全搞不懂为什么这个公式不能正常工作。为了提供背景故事,我试图随机化 DMS 网格坐标,例如:2339**01**N05005**51**W
。我只想随机化粗体的MID(5,2)
和MID(13,2)
,但我需要确保其他值(如果不是在0
和之间)发生变化60
。
我将想要的数字设置为随机向上或向下 5 位数字:
=AH3+(RANDBETWEEN(-5,5))
我执行检查公式(用于视觉和下一步),如果
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
现在从步骤 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 中的公式单元格,但没有成功。00
59
有人对此有什么想法吗? - 或者有更好的解决方案,关于如何0,59
在秒列中随机化 DMS 坐标,将分钟列四舍五入到 之间0,59
,最后将度列四舍五入到 之间?0,90
这LAT
就是我想做的。0,180
LON
答案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 值转换会使数学变得更加复杂。
- DMS 是一个字符串,首先需要将其拆分为纬度(LA)和经度(LO)
- LA/LO 字符串需要解析度、分、秒
- 度数字符串长度可以是 1-3 位数字
- 根据精度,秒字符串可以是 2-x 位数字(例如,埃菲尔铁塔 LA 是 29.1348 DMS 秒
- 需要解析 LA/LO 字符串的 N、S、E、W 值来确定它们是正数还是负数
- 虽然 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 可能有助于显示(可读性),或者满足其他应用程序的需要。