Note: The other languages of the website are Google-translated. Back to English
登入  \/ 
x
or
x
註冊  \/ 
x

or

如何在Excel中將十進制度轉換為分鐘分秒?

有時,工作表中可能有以十進制度顯示的數據列表,現在您需要將十進制度轉換為度,分鐘和秒格式,如以下屏幕截圖所示,如何在Excel中快速進行對話?

使用VBA將十進制度數轉換為度,分,秒

使用VBA將度,分,秒轉換為十進制度

Office選項卡在Office中啟用選項卡式編輯和瀏覽,並使您的工作更加輕鬆...
Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%
  • 重用任何東西: 將最常用或最複雜的公式,圖表等添加到您的收藏夾中,並在將來快速重用它們。
  • 超過20種文字功能: 從文本字符串中提取數字; 提取或刪除部分文字; 將數字和貨幣轉換為英文單詞。
  • 合併工具:將多個工作簿和工作表合二為一; 合併多個單元格/行/列,而不會丟失數據; 合併重複的行和總和。
  • 分割工具:根據價值將數據分割成多個工作表; 一本工作簿可轉換為多個Excel,PDF或CSV文件; 一列到多列。
  • 跳過粘貼 隱藏/過濾的行; 計數與求和 按背景色; 向多個收件人批量發送個性化電子郵件。
  • 超級過濾器: 創建高級過濾方案並應用於任何工作表; 分類 按星期,日期,頻率等 篩選 用粗體,公式,註釋...
  • 超過300種強大的功能; 適用於Office 2007-2019和365; 支持所有語言; 在您的企業或組織中輕鬆部署。

箭頭藍色右氣泡 使用VBA將十進制度數轉換為度,分,秒


請按照以下步驟使用VBA代碼將十進制度數轉換為度,分和秒。

1。 保持 ALT 按鈕並按下 F11 在鍵盤上打開一個 Microsoft Visual Basic應用程序 窗口。

2。 點擊 插入 > 模塊,然後將VBA複製到模塊中。

VBA:將十進制度轉換為度,分鐘和秒

Sub ConvertDegree()
'Update 20130815
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    num1 = Rng.Value
    num2 = (num1 - Int(num1)) * 60
    num3 = Format((num2 - Int(num2)) * 60, "00")
    Rng.Value = Int(num1) & "°" & Int(num2) & "'" & Int(num3) & "''"
Next
End Sub

3。 點擊 按鈕或按下 F5 運行VBA。

4.屏幕上顯示一個對話框,您可以選擇要轉換的單元格。 看截圖:

5。 點擊 OK,然後將所選數據轉換為度,分鐘和秒。 看截圖:

小技巧: :使用上面的VBA代碼將丟失原始數據,因此最好在運行代碼之前複製數據。


箭頭藍色右氣泡 使用VBA將度,分,秒轉換為十進制度

有時,您可能希望將以度/分/秒為單位的數據格式轉換為十進制度,下面的VBA代碼可以幫助您快速完成。

1。 保持 ALT按鈕並按 F11 在鍵盤上打開“ Microsoft Visual Basic for Application”窗口。

2。 點擊 插入 > 模塊,然後將VBA複製到模塊中。

VBA:將度,分和秒轉換為十進制度

Function ConvertDecimal(pInput As String) As Double
'Updateby20140227
Dim xDeg As Double
Dim xMin As Double
Dim xSec As Double
xDeg = Val(Left(pInput, InStr(1, pInput, "°") - 1))
xMin = Val(Mid(pInput, InStr(1, pInput, "°") + 2, _
             InStr(1, pInput, "'") - InStr(1, pInput, _
             "°") - 2)) / 60
xSec = Val(Mid(pInput, InStr(1, pInput, "'") + _
            2, Len(pInput) - InStr(1, pInput, "'") - 2)) _
            / 3600
ConvertDecimal = xDeg + xMin + xSec
End Function

3.保存代碼並關閉窗口,選擇一個空白單元格,例如Cell A1,輸入此公式 = ConvertDecimal(“ 10°27'36”“”) (“ 10°27'36”“”代表要轉換為十進制的度,您可以根據需要進行更改),然後單擊 Enter 按鈕。 查看屏幕截圖:


相關文章

Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Jon · 11 months ago
    WRONG calculation results... 29°30'13" 34°55'4" ---> 29.00083333 34.08333333
  • To post as a guest, your comment is unpublished.
    ValdrichF · 2 years ago
    The code has a assumes there is a space after ° and '. Change the 2's to 1's in the code to solve it.
    Function ConvertDecimal(pInput As String) As Double
    'Updateby20140227
    Dim xDeg As Double
    Dim xMin As Double
    Dim xSec As Double
    xDeg = Val(Left(pInput, InStr(1, pInput, "°") - 1))
    xMin = Val(Mid(pInput, InStr(1, pInput, "°") + 1, _
    InStr(1, pInput, "'") - InStr(1, pInput, _
    "°") - 1)) / 60
    xSec = Val(Mid(pInput, InStr(1, pInput, "'") + _
    1, Len(pInput) - InStr(1, pInput, "'") - 1)) _
    / 3600
    ConvertDecimal = xDeg + xMin + xSec
    End Function
  • To post as a guest, your comment is unpublished.
    Ritesh · 3 years ago
    @Sandeep Check your results. this code is wrong.
  • To post as a guest, your comment is unpublished.
    Sandeep · 3 years ago
    Works like a champ! Thanks for sharing!!!
  • To post as a guest, your comment is unpublished.
    Amjid afridi · 3 years ago
    @ahans step 1, Multiply numbers after decimal with 60 (0.5986740026*60)minutes=35.92044015 mean 35 mints
    spet 2, multiply the decimal after minute ie 0.9204401*60=55.2264
    so 317 degree 35 minuts 55 sec
  • To post as a guest, your comment is unpublished.
    RAMON GOMEZ · 3 years ago
    @Corny HI CORNY, NOT WORK FOR ME, EXCEL DO NOT ACCEPT THE FUNCTION..... WHATS WRONG? CAN YOU HELP ME?
  • To post as a guest, your comment is unpublished.
    ahans · 3 years ago
    please
    i want you help and write a computer program on paper to convert decimal of degree to degree minute and second.
    317.5986740026
    from ahans.
  • To post as a guest, your comment is unpublished.
    Corny · 3 years ago
    Not sure whats wrong with the code in the post from Ernie, but this is what I use to convert decimal degrees to DMS degrees in the spread sheet without using VB. The reference to the cell E33 is the cell containing the decimal degrees value.

    =CONCAT(FIXED(INT(B32), 0) , "°", FIXED(((B32 - INT(B32)) * 60), 0), "'", FIXED(((B32 - INT(B32) - INT(B32 - INT(B32))) * 3600), 0 ,TRUE), """)

    Good luck.
  • To post as a guest, your comment is unpublished.
    Ernie · 4 years ago
    Hello,
    I used the scrip to convert decimal to DMS however the code is wrong somewhere as it turned out 37.856908,-120.912469 to 37D 51M 25S, -121D 5M 15S
    This is way off....

    Anyone have a fix for this?
  • To post as a guest, your comment is unpublished.
    Ryan Smith · 4 years ago
    works great but how would i adapt the code to get more numbers for seconds?
  • To post as a guest, your comment is unpublished.
    Gregory · 4 years ago
    I want to plot the positions on a map and we not worry about N, S, E or W. I'm sure these could be added in with "if" statements.
    I have just used a very simple line of functions within the Excel spreadsheet.
    I want to go from Degrees (Decimal) to Degrees, Seconds (Decimal) Changing the negative values to positives as I know which hemispheres I am in. For me, cell E4 contained the target Lat or Long (Decimal).
    This is a bit easier than putting in some VBA code and can be enlarged to do DD,MM,SS

    =CONCATENATE(TRUNC(ABS(E4)),"°",FIXED((ABS(E4)-TRUNC(ABS(E4)))*60,2),"'")
  • To post as a guest, your comment is unpublished.
    Mou · 5 years ago
    Found this code very helpful. Great stuff!
  • To post as a guest, your comment is unpublished.
    Mort Wakeland · 5 years ago
    The most important thing to realize is that you cannot simply use superscript and small letter o for the degree symbol - this may be obvious to some, but not to me. I tried the superscript letter o and it did not work, I deleted the superscript letter o, and used Alt 0176 for the actual degree symbol and it worked!! I am confused as to why after the seconds there are extra sets of double quotes?
    36""") whereas in the subsequent comment if one simply uses =ConvertDecimal(A1) only a single set of quotes are used for seconds. Odd we can use single and double quotes and the thing works for minutes and seconds but superscript letter o does not?
    To check 27/60 = .45 smf 36/3600 = .01 add the two = .46 and add that to the degrees = 10.46 degrees. Hope this helps others? Mort
  • To post as a guest, your comment is unpublished.
    Levi · 6 years ago
    Really helpful.
    Thank you.
  • To post as a guest, your comment is unpublished.
    Gunnar · 6 years ago
    @Rajansinh Zala [quote name="Rajansinh Zala"]there is some error in code i tried to convert 26°10'55.416" using this code which results as 26.00139 which should be 26.18206 (Calculated & Verified.. please let me know if anyone knows the reason.[/quote]

    It seems the code expects a space after the ° and ' symbols.
    26° 10' 55.416" gives the correct answer.
    You can enter your input in a cell (eg A1) and get the result in another cell by using the formula =ConvertDecimal(A1). This is convenient if you have a many numbers to be converted.
  • To post as a guest, your comment is unpublished.
    Rajansinh Zala · 6 years ago
    there is some error in code i tried to convert 26°10'55.416" using this code which results as 26.00139 which should be 26.18206 (Calculated & Verified.. please let me know if anyone knows the reason.