Loncat ke daftar isi utama

Bagaimana cara mengubah derajat desimal menjadi derajat menit detik di Excel?

Terkadang, Anda mungkin memiliki daftar data yang ditampilkan sebagai derajat desimal di lembar kerja, dan sekarang Anda perlu mengonversi derajat desimal ke format derajat, menit dan detik seperti yang diperlihatkan sebagai tangkapan layar berikut, bagaimana Anda bisa mendapatkan percakapan dengan cepat di Excel?

Ubah derajat desimal menjadi derajat, menit, detik dengan VBA

Ubah derajat, menit, detik ke derajat desimal dengan VBA

panah gelembung kanan biru Ubah derajat desimal menjadi derajat, menit, detik dengan VBA

Ikuti langkah-langkah di bawah ini untuk mengubah derajat desimal menjadi derajat, menit, dan detik dengan kode VBA.

1. Memegang ALT tombol dan tekan F11 pada keyboard untuk membuka a Microsoft Visual Basic untuk Aplikasi jendela.

2. klik Menyisipkan > Modul, dan salin VBA ke dalam modul.

VBA: Ubah derajat desimal menjadi derajat, menit dan detik

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. klik Run tombol atau tekan F5 untuk menjalankan VBA.

4. Sebuah dialog ditampilkan di layar, dan Anda dapat memilih sel yang ingin Anda ubah. Lihat tangkapan layar:

5. klik OK, kemudian data yang dipilih diubah menjadi derajat, menit dan detik. Lihat tangkapan layar:

jenis: Menggunakan kode VBA di atas akan kehilangan data asli Anda, jadi sebaiknya Anda menyalin data sebelum menjalankan kode.

panah gelembung kanan biru Ubah derajat, menit, detik ke derajat desimal dengan VBA

Terkadang, Anda mungkin ingin mengonversi data dalam format derajat / menit / detik ke derajat desimal, kode VBA berikut dapat membantu Anda menyelesaikannya dengan cepat.

1. Memegang ALTombol T dan tekan F11 pada keyboard untuk membuka jendela Microsoft Visual Basic for Application.

2. klik Menyisipkan > Modul, dan salin VBA ke dalam modul.

VBA: Ubah derajat, menit dan detik menjadi derajat desimal

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. Simpan kode dan tutup jendela, pilih sel kosong, misalnya, Sel A1, masukkan rumus ini = ConvertDecimal ("10 ° 27 '36" "") ("10 ° 27 '36" "" adalah derajat yang ingin Anda ubah menjadi derajat desimal, Anda dapat mengubahnya sesuai kebutuhan), lalu klik Enter tombol. Lihat tangkapan layar:

Artikel relatif

Comments (16)
No ratings yet. Be the first to rate!
Jon
Jon
This comment was minimized by the moderator on the site
WRONG calculation results... 29°30'13" 34°55'4" ---> 29.00083333 34.08333333
Reply
0
0
ValdrichF
ValdrichF
This comment was minimized by the moderator on the site
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
Reply
0
0
Sandeep
Sandeep
This comment was minimized by the moderator on the site
Works like a champ! Thanks for sharing!!!
Reply
0
0
Ritesh
Ritesh     Sandeep
This comment was minimized by the moderator on the site
Check your results. this code is wrong.
Reply
0
0
ahans
ahans
This comment was minimized by the moderator on the site
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.
Reply
0
0
Amjid afridi
Amjid afridi     ahans
This comment was minimized by the moderator on the site
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
Reply
0
0
Corny
Corny
This comment was minimized by the moderator on the site
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.
Reply
0
0
RAMON GOMEZ
RAMON GOMEZ     Corny
This comment was minimized by the moderator on the site
HI CORNY, NOT WORK FOR ME, EXCEL DO NOT ACCEPT THE FUNCTION..... WHATS WRONG? CAN YOU HELP ME?
Reply
0
0
Ernie
Ernie
This comment was minimized by the moderator on the site
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?
Reply
0
0
Ryan Smith
Ryan Smith
This comment was minimized by the moderator on the site
works great but how would i adapt the code to get more numbers for seconds?
Reply
0
0
Gregory
Gregory
This comment was minimized by the moderator on the site
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),"'")
Reply
0
0
Mou
Mou
This comment was minimized by the moderator on the site
Found this code very helpful. Great stuff!
Reply
0
0
Mort Wakeland
Mort Wakeland
This comment was minimized by the moderator on the site
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
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations