Latitud och longitud i Excel

Mats är sjökapten och seglar runt hela jorden med ett fraktfartyg. Ena månaden kan han vara i Indiska Oceanen och nästa i Stilla Havet. Han är Excelfantast och jobbar mycket med Excel när han är på bryggan. Fartyget har naturligtvis all topputrustning som finns för navigering, men Mats loggar intressanta positioner i Excel mest för sitt eget nöje och intresse.

Han undrar om man kan göra ett cellformat som automatiskt visar grader, minuter och sekunder för en position. Om han exempelvis skriver in 420329 som latitud ska det visas 42° 03’ 29’’ N och för longitud 235546 ska det visas 23° 55’ 46’’ E i cellen.

Det kan Mats göra med ett anpassat cellformat och dialogrutan Formatera celler (Tryck Ctrl + 1). I fältet typ skriver han in 00 (gradtecknet fås fram med Alt+0176) mellanslag, 00 mellanslag, en apostrof, mellanslag och slutligen två apostrofer. 00° 00’ 00’’

Longitud och latitud

Longitud och latitud-2

Resultatet för en position i Medelhavet. Han får skriva in N eller S (North or South) och i kolumn E, W eller E (West or East)

Latitude Longitude

 

Latituden utgår från ekvatorn som är 0° och varierar från 90° S till 90° N.
Longituden utgår från Greenwich och varierar från 180° W till 180° E.

 

 

 

Posted in Excel | Tagged , , | Leave a comment

Tulpaner i Excel

Tänk vad man kan göra med Excel. Rita fram tulpaner till exempel som japanen Tatsou Horiuchi gör. Han skapar dessutom en hel del andra fantastiska bilder som du kan kika på i länken nedan. Alla gjorda i Excel. Eftersom jag mest använder den vänstra hjärnhalvan (den logiska) när jag arbetar med Excel, kan det vara bra att även träna höger hjärnhalva. Jag ska rita de här tulpanerna vid ett tillfälle, men kommer nog att välja Word eller Powerpoint i stället. Ritdelen i Office delas ju av programmen och är exakt lika.

https://pasokonga.com

Bifogar bilden som en pdf så att du kan skriva ut den

Tulpaner

Tulpaner i Excel

Posted in Excel | Tagged | Leave a comment

Problem med tusentalsavgränsning

Kalle skickar in följande exempel och undrar hur han ska skriva en formel som visar tusentalsavgränsning på ett korrekt sätt. Han illustrerar problemet med följande uppställning och formel.

Problem med tusentalsavgränsning

 

Kalle har formaterat A kolumnen med talformatet tusentals-avgränsning.

 

Han har skrivit följande formel. Teckenkod(10) innebär en radbrytning i cellen, & et-tecknet slår ihop cellers innehåll och ” ” innebär ett mellanslag.

=B1&” ”&A1&” ”&C1&”.”&TECKENKOD(10)&B2&” ”&A2&” ”&C2&”.”&TECKENKOD(10)&B3&” ”&A3&” ”&C3&”.”

Problem med tusentalsavgränsning-2

Men då blir resultatet som bilden visar och tusentalsavgränsningen ignoreras.

 

Problem med tusentalsavgränsning-3

En lösning är då att använda funktionen Text som konverterar ett värde till text i ett specifikt talformat.

 

Koden för tusentalsavgränsning i Excel är # ## (nummertecken, hashmark). Och för att göra formeln mer lättläst använder jag funktionen Samman (tidigare Sammanfoga).

Posted in Excel | Tagged , , , | Leave a comment

Power Pivot överlägsen LetaRad

Om du arbetar med Pivottabeller i Excel är du förmodligen bekant med att sammanställa data från olika listor eller tabeller till ett kalkylblad, innan du skapar en pivottabell. En av de vanligaste funktionerna som då används är LetaRad, eller så används funktionerna Index och Passa i kombination. De är några av Excels effektiva sökfunktioner som nyttjas mycket bland användare.

Men i stället för att skriva mängder med formler i kalkylbladen kan du med fördel läsa in data till Power Pivot fönstret och där skapa relationer mellan de olika kalkylbladen. Har du gjort det några gånger kommer du att tycka det är mycket enklare och det är dessutom mycket snabbare.

Nedan visas två typiska listor med hundratals rader som ska sammanställas. De är utformade som tabeller (Ctrl + T) som jag gett namnen Data och Kategorier och de finns i två olika kalkylblad i samma arbetsbok.

Power Pivot överlägsen LetaRad

Power Pivot överlägsen LetaRad-2

 

 

 

 

 

 

Jag vill nu summera försäljningen per kategori och månad för ett helt år i en Pivottabell. Klicka in varsomhelst i den första tabellen, välj fliken Power Pivot och där ikonen Lägg till i datamodell. Gör på samma sätt med den andra tabellen.

Power Pivot överlägsen LetaRad-3

 

Power Pivot fönstret öppnas och uppgifterna läses in. Power Pivot påminner ju väldigt mycket om ”vanliga” Excel men har naturligtvis andra ikoner och verktyg.

Några skillnader är att du kan läsa in obegränsat antal rader med data och att formelskrivning är annorlunda.

 

 

Relationer skapas genom att identifiera vilka kolumner i de två tabellerna som kan kopplas ihop. Kopplar du ihop kolumnerna Produkt som finns i båda, kan du få fram vilka uppgifter som helst från de två tabellerna. Välj fliken Design, Skapa relation.

Power Pivot överlägsen LetaRad-4

 

Det blir en så kallad en-till många relation. En produkt i tabellen Kategorier kan förekomma många gånger i Datatabellen.

 

 

 

 

Datumkolumnen som innehåller alla transaktionsdatum blir opraktisk att visa i en Pivottabell. Så därför läggs en ny kolumn till som ska visa månadsnamnen i stället. Formeln i den nya kolumnen blir =format([Datum]; ”mmm”) och så ges den namnet Månad.

Power Pivot överlägsen LetaRad-5

 

 

 

 

Power Pivot överlägsen LetaRad-6

 

Datamodellen är nu klar för att läsas in till Excel. På Startfliken väljer du Pivottabell och att skapa den på ett nytt kalkylblad.

Bilden visar att det finns två tabeller och fältet Månad ligger som kolumner och fältet Kategori som rader.

 

 

Den färdiga Pivottabellen. Nästa gång en rapport behöver göras kan du bara klistra in ny data i de två tabellerna samt uppdatera Pivottabellen.

Power Pivot överlägsen LetaRad-7

Posted in Excel | Tagged , , , | Leave a comment

OneNote problematik

Jag har inte skrivit något om OneNote tidigare på denna blogg och skälet är att jag anser applikationen är ganska självinstruerande och enkel att använda. Det är ju trots allt bara ett digitalt anteckningsblock. Men de senaste åren har jag noterat att OneNote är en populär app och används flitigt av många användare. Stora organisationer som Göteborgs Stad och Malmö Stad använder den och ofta sparar man känsliga och sekretessbelagda uppgifter i OneNote filer. Det är då problematiken uppstår.

Microsoft har nämligen aviserat att man upphör med att utveckla skrivbordsversionerna och att One Note Online (molnversionen) och endast den blir tillgänglig för användare. Många användare har också frågat mig om man kan spara filerna i en annan mapp än standardmappen. Och det kan man genom att välja Arkiv, Nytt och nere till höger finns Skapa i en annan mapp.

OneNote problematik-2

 

Eftersom många använder OneNote för att lagra känsliga data, kan det vara bra att veta att det går att lösenordskydda ett avsnitt i OneNote. Högerklicka på avsnittet och välj Lösenordskydda det här avsnittet.

 

Posted in Office 365 | Tagged | Leave a comment

Matrisformler i Excel

Matrisformler har funnits länge i Excel, men det är inte så ofta man ser att de används. Tekniken är ju annorlunda än vanlig formelskrivning och det finns för och nackdelar med matrisformler. Formlerna kännetecknas av att de omges av klammerparenteser { } och att du måste trycka Ctrl + Skift + Enter för att slutföra inmatningen. Nedan visas ett enkelt exempel.

Matrisformler

 

Jag börjar med att markera området C5 till C10 där jag vill ha momsen. Skriver sedan = i cell C5, fortsätter med att markera in cellområdet B5 till B10 , multiplicerar med C2 och avslutar med att trycka Ctrl+Skift+Enter.

 

 

Matrisformler 2

 

Resultatet. Notera formeln {=B5:B10*C2} i formelfältet och att cell C7 är i fokus.

Det behövs alltså ingen absolut cellreferens i cell C2 och du inte behöver kopiera ner formeln, eftersom området var förmarkerat.

 

 

Det går att fördjupa sig ytterligare i matrisformler i Excel, men det kanske inte blir så viktigt längre. Microsoft har nämligen aviserat att man snart kommer att introducera dynamiska matrisformler i Excel 365. Observera endast i 365 versionen. Det innebär ett nytänk vad det gäller formelskrivning i Excel eftersom Excel då kommer att tillåta, att en formel i en cell kommer att fylla flera närliggande celler med ett resultat utan att de innehåller en formel. Det finns alla skäl till att återkomma till detta i bloggen.

 

Posted in Excel | Tagged , | Leave a comment

2019 års färg enligt Pantone

Pantone är ett företag som arbetar med färger och de lanserar i slutet av varje år en ny färg för kommande år. 2019 års färg kallas Living Coral och den kan du få fram i alla Officeprogram genom att t.ex. välja Teckenfärg, Fler färger och fliken Valfri.

2019 års färg Pantone

 

 

RGB-koden är 250, 114, 104

Behöver du färgen till HTML eller Hex är koden FA7268

 

 

 

2019 års färg Pantone-2

Posted in Powerpoint, Word | Tagged , , | Leave a comment

Regel mot skräppost i Outlook

Så här vid juletid drabbas många av stora mängder skräppost som droppar in i inkorgen. Flertalet spam innehåller dessutom länkar och bifogade filer, som om man är oförsiktig placerar virus och trojaner på hårddisken. Ett antivirusprogram är ett måste och de har ofta ett skräppostfilter som försöker sortera bort skräpet. Men ofta är det inte tillräckligt effektivt.

Då kan du sätta en regel i Outlook som tar bort kluddet. Det förutsätter att spammaren använder sig av samma domännamn, vilket inte alltid är fallet. På sistone har jag fått mycket skräppost där avsändaren använder domänadresserna @ryanair.com samt @nike.com. Hur spammarna nu kan använda så välkända varumärken till att sprida skräppost har jag ingen förklaring på, men så är fallet.

Regel mot skräppost

 

På Startfliken väljer du Regler, Skapa regel och i dialogen som följer väljer du längst ned till höger Avancerat. I Regelguidens Steg 1 bockar du för med specifika ord i avsändarens adress.

Nederst i Steg 2 klickar du på specifika ord.

 

 

 

Regel mot skräppost-2

 

I dialogrutan Söktext skriver du in avsändarnas adresser och klickar på Lägg till samt OK. Välj sedan Nästa i Regelguiden för att välja åtgärd.

 

Regel mot skräppost-3

 

 

Som åtgärd väljer du ta bort det. Kontrollera noga nederst så att du valt rätt. Och i nästa steg kan du välja undantag från regeln om du så önskar, annars välj Nästa.

 

 

 

Regel mot skräppost-4

 

I det sista steget slutför du regeln och väljer att Tillämpa regeln. Du har nu en mycket effektiv regel som tar bort mängder av skräppost och som sparar mycket tid och irritation.

 

 

Posted in Okategoriserade | Tagged | Leave a comment

3D-karta i Excel 365

I tidigare inlägg har jag beskrivit hur du kan infoga 3D-kartor i Excel. Analys- och presentationsverktyget har nu förbättrats i Excel 365 och här visas några nya exempel. I exemplet nedan har jag hämtat en databas som innehåller data om jordbävningar från hela världen och deras magnitud.

3D karta i Excel 365

 

Filen innehåller drygt 560 rader från år 2001 till 2017 och positionen bestäms av longitud och latitud.

 

Sedan väljs ikonen 3D-karta och Öppna 3D-Maps som finns på Infogafliken. I bilden nedan ligger fälten Longitud och Latitud i området Plats. Magnitude ligger i boxen Värde. Du använder samma teknik som när du skapar en pivottabell. Som standard visas värdena som staplar, men i detta exempel passar visualiseringen Värmekarta betydligt bättre.

3D karta i Excel 365-2

Bilden nedan visar tydligt koncentrationen av jordbävningar i den s.k. eldringen i västra Stilla Havet. Ju rödare värmekarta desto kraftigare jordbävning. Det går att rotera och zooma in globen till detaljnivå. Det är Microsofts söktjänst Bing som ritar värmekartan utifrån longitud och latitud, men det fungerar lika bra med stads- och ortnamn.

3D karta i Excel 365-3

3D karta i Excel 365-4

 

I Italien ligger koncentrationen av jordbävningar i landskapet Umbrien.

 

 

 

 

 

När jag höll utbildning på ett försäkringsbolag i Stockholm visade marknadschefen en värmekarta över deras kunder i Sverige, baserat på ortnamn.

3D karta i Excel 365-5

 

De visste redan att de allra flesta kunderna fanns i Sveriges större städer. Han zoomade in i detalj landskap för landskap och blev förbluffad när han undersökte delar av Småland. Han fann då en stor koncentration av kunder i mindre orter och de hade ingen aning om varför de var så väl representerade där.

 

Det går att skapa en video utifrån 3D-karta och i länken nedan finns en MP4-video med en roterande jord.

Posted in Excel, Office 365 | Tagged , , | Leave a comment

Extrahera data med dropdown-list

I bilden nedan finns en lista med data i kolumnerna A till C. Detta inlägg kommer att visa hur du skapar en dropdown-list i cell H2 för att extrahera data om respektive land. Resultatet kommer då att visas i kolumnerna J till L. Du kan göra samma sak betydligt snabbare med en pivottabell, men jag vill här visa hur man på ett fiffigt sätt arbetar med hjälpkolumner i Excel och sedan använder Excels kraftfulla funktioner för att få ett önskat resultat.

Extrahera data med dropdown list

Först markerar och kopierar du C kolumnen och klistrar in länderna i valfri kolumn t.ex. kolumn O. Du ska här ta bort dubbletterna som finns i listan. Välj fliken Data, Ta bort dubbletter. Listan består nu av unika länder. Placera markören i cell H2 och välj sedan Dataverifiering under fliken Data.

Extrahera data med dropdown list-2

 

Under Tillåt väljer du Lista. Klicka sedan in i fältet Källa och markera in de unika länderna, här i cellerna O2 till O9. Du har nu skapat en dropdown-list i cell H2.

 

Extrahera data med dropdown list-3

 

I nästa steg skapar du en hjälpkolumn med serienummer indexnummer för alla posterna. Se kolumn D Hjälpkolumn 1.

Cell E2 i Hjälpkolumn 2 innehåller följande formel: =OM(C2=$H$2;D2;””) vilket innebär att om t.ex. Indien väljs i dropdown-listen visas motsvarande siffra från kolumn D. Se bilden bredvid.

I hjälpkolumn 3 cell F2 finns följande formel =OMFEL(MINSTA($E$2:$E$26;D2);””). Omfel utför en felkontroll och funktionen Minsta returnerar det n:te minsta värdet i en datamängd, här i kolumn E.

 

Hjälpkolumnerna är nu klara och kan med fördel döljas. Slutligen väljer du var du vill placera din extraherade data. I bilden nedan placeras den med början i cell J2 och formeln är: =OMFEL(INDEX($A$2:$C$26;$F2;KOLUMNER($J$1:J1));””)

Extrahera data med dropdown list-4

Funktionen Index extraherar data utifrån radnumret $F2. Kolumner returnerar antal kolumner i ett cellområde och funktionen Omfel returnerar inget om det inte finns någon data.

 

Posted in Excel | Tagged , , | Leave a comment