Difference between revisions of "Excel"
From Blue-IT.org Wiki
| Line 9: | Line 9: | ||
Koordinates in E10 and F10 like: "48,9283475" (with comma"). | Koordinates in E10 and F10 like: "48,9283475" (with comma"). | ||
| + | Zell formula: | ||
=HYPERLINK("http://www.osm.org/?mlat="&WECHSELN(E10;",";".")&"&mlon="&WECHSELN(F10;",";".")&" & zoom="&E10&"#map=16/"&WECHSELN(RUNDEN(E10;5);",";".")&"/"&WECHSELN(RUNDEN(F10;5);",";".");"Stelle bei OpenStreetMap") | =HYPERLINK("http://www.osm.org/?mlat="&WECHSELN(E10;",";".")&"&mlon="&WECHSELN(F10;",";".")&" & zoom="&E10&"#map=16/"&WECHSELN(RUNDEN(E10;5);",";".")&"/"&WECHSELN(RUNDEN(F10;5);",";".");"Stelle bei OpenStreetMap") | ||
| + | |||
| + | |||
| + | VBS: | ||
| + | ActiveCell.Formula = "=HYPERLINK(" & _ | ||
| + | """http://www.osm.org" & _ | ||
| + | "/?mlat=" & _ | ||
| + | """ & WECHSELN(E10,"","",""."")" & _ | ||
| + | " & ""/&mlon=" & _ | ||
| + | """ & WECHSELN(F10,"","",""."")" & _ | ||
| + | " & ""&zoom=""& E10 & ""#map=16/""" & _ | ||
| + | " & WECHSELN(RUNDEN(E10,5),"","",""."") & ""/""" & _ | ||
| + | " & WECHSELN(RUNDEN(F10,5),"","",""."")" & _ | ||
| + | ",""Stelle bei OpenStreetMap""" & _ | ||
| + | ")" | ||
[[Category:Excel]] | [[Category:Excel]] | ||
[[Category:Office 2010]] | [[Category:Office 2010]] | ||
Revision as of 11:12, 9 July 2014
Kalenderwoche
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(""0"",TRUNC((RC[-25]-DATE(YEAR(RC[-25]+4-WEEKDAY(RC[-25],2)),1,-9+WEEKDAY(RC[-25],3)))/7),"" / "",YEAR(RC[-25]))"
Open Street Map Hyperlink in Excel
Koordinates in E10 and F10 like: "48,9283475" (with comma").
Zell formula:
=HYPERLINK("http://www.osm.org/?mlat="&WECHSELN(E10;",";".")&"&mlon="&WECHSELN(F10;",";".")&" & zoom="&E10&"#map=16/"&WECHSELN(RUNDEN(E10;5);",";".")&"/"&WECHSELN(RUNDEN(F10;5);",";".");"Stelle bei OpenStreetMap")
VBS:
ActiveCell.Formula = "=HYPERLINK(" & _
"""http://www.osm.org" & _
"/?mlat=" & _
""" & WECHSELN(E10,"","",""."")" & _
" & ""/&mlon=" & _
""" & WECHSELN(F10,"","",""."")" & _
" & ""&zoom=""& E10 & ""#map=16/""" & _
" & WECHSELN(RUNDEN(E10,5),"","",""."") & ""/""" & _
" & WECHSELN(RUNDEN(F10,5),"","",""."")" & _
",""Stelle bei OpenStreetMap""" & _
")"