Excel för beslutsfattande

förutom formler är ett annat sätt att utföra matematiska beräkningar i Excel genom funktioner. Statistiska funktioner tillämpar en matematisk process på en grupp celler i ett kalkylblad. Till exempel används SUM-funktionen för att lägga till värdena i ett cellområde. En lista över vanliga statistiska funktioner visas i tabell 2.4. Funktioner är effektivare än formler när du tillämpar en matematisk process på en grupp celler. Om du använder en formel för att lägga till värdena i ett cellområde måste du lägga till varje cellplats i formeln en i taget. Detta kan vara mycket tidskrävande om du måste lägga till värdena på några hundra cellplatser. Men när du använder en funktion kan du Markera alla celler som innehåller värden som du vill summera i bara ett steg. Det här avsnittet visar en mängd olika statistiska funktioner som vi lägger till i arbetsboken för personlig Budget. Förutom att visa funktioner granskar detta avsnitt också procent av de totala beräkningarna och användningen av absoluta referenser.

Tabell 2.4 vanliga statistiska funktioner

funktionen summa används när du behöver beräkna summor för ett cellområde eller en grupp markerade celler i ett kalkylblad. När det gäller kalkylbladet för Budgetdetaljer använder vi SUM-funktionen för att beräkna totalerna i rad 12. Det är viktigt att notera att det finns flera metoder för att lägga till en funktion i ett kalkylblad, vilket kommer att demonstreras under resten av detta kapitel. Följande illustrerar hur en funktion kan läggas till i ett kalkylblad genom att skriva in den i en cellplats:

1. klicka på fliken budgetdetaljer för att öppna kalkylbladet.
2. klicka på cell C12.
3. Skriv ett likhetstecken=.
4. skriv funktionsnamnet summa.
5. Skriv en öppen parentes (.
6. klicka på cell C3 och dra ner till cell C11. Detta placerar intervallet C3: C11 i funktionen.
7. Skriv en avslutande parentes).
8. tryck på ENTER-tangenten. Funktionen beräknar summan för kolumnen månadsutgifter, vilket är $1 496.

figur 2.11 visar utseendet på SUM-funktionen som läggs till i kalkylbladet Budgetdetaljer innan du trycker på ENTER-tangenten.

som visas i Figur 2.11 lades SUM-funktionen till cell C12. Denna funktion behövs dock också för att beräkna summorna i kolumnerna årliga utgifter och LY-utgifter. Funktionen kan kopieras och klistras in i dessa cellplatser på grund av relativ referens. Relativ referens tjänar samma syfte för funktioner som för formler. Följande visar hur den totala raden är klar:

1. klicka på cell C12 i kalkylbladet Budgetdetaljer.
2. klicka på knappen Kopiera på fliken Hem i menyfliksområdet.
3. markera cellerna D12 och E12.
4. klicka på Klistra in på fliken Hem i menyfliksområdet. Detta klistrar in SUM-funktionen i cellerna D12 och E12 och beräknar summorna för dessa kolumner.
5. klicka på cell F11.
6. klicka på knappen Kopiera på fliken Hem i menyfliksområdet.
7. klicka på cell F12 och klicka sedan på Klistra in på fliken Start i menyfliksområdet. Eftersom vi nu har summor i rad 12 kan vi klistra in procentändringsformeln i den här raden.

figur 2.12 visar utmatningen av SUM-funktionen som lades till cellerna C12, D12 och E12. Dessutom kopierades procentändringsformeln och klistrades in i cell F12. Observera att denna version av budgeten planerar en minskning av utgifterna med 1, 7% jämfört med förra året.

absoluta referenser (beräkning procent av Totals)

datafil: fortsätt med CH2 Personlig Budget.

eftersom summor har lagts till i rad 12 i kalkylbladet för Budgetdetaljer kan en procent av den totala beräkningen läggas till i kolumn B med början i cell B3. Procentandelen av den totala beräkningen visar procentandelen för varje värde i kolumnen årliga utgifter med avseende på summan i cell D12. Efter att formeln har skapats måste du dock stänga av Excels relativa referensfunktion innan du kopierar och klistrar in formeln till resten av cellplatserna i kolumnen. Att stänga av Excels relativa referensfunktion uppnås genom en absolut referens. Följande steg förklarar hur detta görs:

1. klicka på cell B3 i kalkylbladet Budgetdetaljer.
2. Skriv ett likhetstecken=.
3. klicka på cell D3.
4. Skriv ett snedstreck framåt /.
5. klicka på cell D12.
6. tryck på ENTER-tangenten. Du kommer att se att hushållsverktyg representerar 16,7% av den årliga utgiftsbudgeten (se figur 2.14).

Figur 2.14 visar den färdiga formeln som beräknar den procentandel som hushållens årliga utgifter motsvarar den totala årliga utgifterna för budgeten (se cell B3). Normalt skulle vi kopiera denna formel och klistra in den i intervallet B4:B11. På grund av relativ referens kommer båda cellreferenserna dock att öka med en rad när formeln klistras in i cellerna under B3. Detta är bra för den första cellreferensen i formeln (D3) men inte för den andra cellreferensen (D12). Figur 2.15 illustrerar vad som händer om vi klistrar in formeln i intervallet B4:B12 i dess nuvarande tillstånd. Lägg märke till att Excel producerar felkoden #DIV/0. Det betyder att Excel försöker dela ett tal med noll, vilket är omöjligt. Om du tittar på formeln i cell B4 ser du att den första cellreferensen ändrades från D3 till D4. Det här är bra eftersom vi nu vill dela de årliga utgifterna för försäkring med de totala årliga utgifterna i cell D12. Excel har dock också ändrat D12-cellreferensen till D13. Eftersom cellplats D13 är tom producerar formeln felkoden #DIV / 0.

för att eliminera divide-by-zero-felet som visas i Figur 2.15 måste vi lägga till en absolut referens till cell D12 i formeln. En absolut referens förhindrar relativ referens från att ändra en cellreferens i en formel. Detta kallas också för att låsa en cell. Följande förklarar hur detta uppnås:

1. dubbelklicka på cell B3.
2. placera muspekaren framför D12 och klicka. Den blinkande markören ska vara framför D i cellreferensen D12.
3. tryck på F4-tangenten. Du kommer att se ett dollartecken ($) läggs till framför kolumnbokstaven D och radnumret 12. Du kan också skriva dollartecknen framför kolumnbokstaven och radnumret.
4. tryck på ENTER-tangenten.
5. klicka på cell B3.
6. klicka på knappen Kopiera på fliken Hem i menyfliksområdet.
7. markera intervallet B4:B11.
8. klicka på Klistra in på fliken Hem i menyfliksområdet.

figur 2.16 visar procent av den totala formeln med en absolut referens tillsatt till D12. Observera att i cell B4 förblir cellreferensen D12 istället för att ändras till D13 som visas i Figur 2.15. Du kommer också att se att procentsatserna beräknas i resten av cellerna i kolumnen, och divide-by-zero-felet elimineras nu.

RÄKNEFUNKTIONEN

datafil: fortsätt med CH2 Personlig Budget.

nästa funktion som vi lägger till i kalkylbladet för Budgetdetaljer är RÄKNEFUNKTIONEN. Funktionen räkna används för att bestämma hur många celler i ett område som innehåller en numerisk post. Funktionen räkna fungerar inte för att räkna text eller andra icke-numeriska poster. För kalkylbladet Budgetdetaljer använder vi funktionen räkna för att räkna antalet objekt som planeras i kolumnen årliga utgifter (kolumn D). Följande förklarar hur RÄKNARFUNKTIONEN läggs till i kalkylbladet med hjälp av funktionslistan:

1. klicka på cell D13 i kalkylbladet Budgetdetaljer.
2. Skriv ett likhetstecken=.
3. Skriv bokstaven C.
4. klicka på nedåtpilen i rullningsfältet i funktionslistan (se figur 2.17) och hitta ordräkningen.
5. dubbelklicka på antalet ord i funktionslistan.
6. markera intervallet D3:D11.
7. du kan skriva en avslutande parentes) och tryck sedan på ENTER-tangenten, eller tryck helt enkelt på ENTER-tangenten och Excel stänger funktionen för dig. Funktionen ger en utgång på 9 eftersom det finns 9 objekt planerade i kalkylbladet.

Figur 2.17 visar listrutan funktion som visas efter att ha slutfört steg 2 och 3 för funktionen räkna. Funktionslistan innehåller en alternativ metod för att lägga till en funktion i ett kalkylblad.

figur 2.18 visar utgången från RÄKNEFUNKTIONEN efter att ha tryckt på ENTER-tangenten. Funktionen räknar antalet celler i intervallet D3: D11 som innehåller Ett numeriskt värde. Resultatet av 9 indikerar att det finns 9 kategorier planerade för denna budget.

den genomsnittliga funktionen

nästa funktion som vi lägger till i kalkylbladet för Budgetdetaljer är den genomsnittliga funktionen. Denna funktion används för att beräkna det aritmetiska medelvärdet för en grupp siffror. För kalkylbladet Budgetdetaljer använder vi funktionen för att beräkna medelvärdet av värdena i kolumnen årliga utgifter. Vi lägger till detta i kalkylbladet med hjälp av funktionsbiblioteket. Följande steg förklarar hur detta uppnås:

1. klicka på cell D14 i kalkylbladet Budgetdetaljer.
2. klicka på fliken Formler i menyfliksområdet.
3. klicka på knappen Fler funktioner i Kommandogruppen funktionsbibliotek.
4. placera muspekaren över alternativet statistik i listrutan med alternativ.
5. klicka på det genomsnittliga funktionsnamnet i listan över funktioner som visas i menyn (se figur 2.19). Detta öppnar dialogrutan Funktionsargument.
6. klicka på knappen Dölj dialogrutan i dialogrutan Funktionsargument (se figur 2.20).
7. markera intervallet D3:D11.
8. klicka på knappen expandera Dialog i dialogrutan Funktionsargument (se figur 2.21). Du kan också trycka på ENTER-tangenten för att få samma resultat.
9. klicka på OK-knappen i dialogrutan Funktionsargument. Detta lägger till den genomsnittliga funktionen i kalkylbladet.

figur 2.19 illustrerar hur en funktion väljs från funktionsbiblioteket på fliken Formler i menyfliksområdet.

figur 2.20 visar dialogrutan Funktionsargument. Detta visas efter att en funktion har valts från funktionsbiblioteket. Knappen Dölj dialogruta används för att dölja dialogrutan så att ett cellområde kan markeras i kalkylbladet och sedan läggas till i funktionen.

figur 2.21 visar hur ett cellområde kan väljas från dialogrutan Funktionsargument när den har kollapsat.

Figur 2.22 visar dialogrutan Funktionsargument efter att cellområdet har definierats för den genomsnittliga funktionen. Dialogrutan visar resultatet av funktionen innan den läggs till i cellplatsen. Detta gör att du kan bedöma funktionsutgången för att avgöra om det är vettigt innan du lägger till det i kalkylbladet.

figur 2.23 visar den slutförda genomsnittliga funktionen i kalkylbladet Budgetdetaljer. Utgången från funktionen visar att vi i genomsnitt förväntar oss att spendera $1,994 för var och en av de kategorier som anges i kolumn A i budgeten. Denna genomsnittliga utgiftsberäkning per kategori kan användas som en indikator för att avgöra vilka kategorier som kostar mer eller mindre än de genomsnittliga budgeterade utgifterna.

Max-och min-funktionerna

datafil: fortsätt med CH2 Personlig Budget.

de sista två statistiska funktionerna som vi kommer att lägga till i kalkylbladet för Budgetdetaljer är MAX-och min-funktionerna. Dessa funktioner identifierar de högsta och lägsta värdena i ett cellområde. Följande steg förklarar hur du lägger till dessa funktioner i kalkylbladet för Budgetdetaljer:

1. klicka på cell D15 i kalkylbladet Budgetdetaljer.
2. Skriv ett likhetstecken=.
3. Skriv ordet MIN.
4. Skriv en öppen parentes (.
5. markera intervallet D3:D11.
6. Skriv en avslutande parentes ) och tryck på ENTER-tangenten, eller tryck helt enkelt på ENTER-tangenten och Excel stänger funktionen för dig. MIN-funktionen ger en utgång på $1,200, vilket är det lägsta värdet i kolumnen årliga utgifter (se figur 2.24).
7. klicka på cell D16.
8. Skriv ett likhetstecken=.
9. Skriv ordet MAX.
10. Skriv en öppen parentes (.
11. markera intervallet D3:D11.
12. Skriv en avslutande parentes ) och tryck på ENTER-tangenten, eller tryck helt enkelt på ENTER-tangenten och Excel stänger funktionen för dig. MAX-funktionen ger en effekt på $3,500. Detta är det högsta värdet i kolumnen årliga utgifter (se figur 2.25).

kopiera och klistra in formler(klistra in utan format)

datafil: fortsätt med CH2 Personlig Budget.

som visas i Figur 2.25 sammanfattar funktionerna COUNT, AVERAGE, MIN och MAX data i kolumnen årliga utgifter. Du kommer också att märka att det finns utrymme att kopiera och klistra in dessa funktioner under kolumnen Ly spendera. Detta gör att vi kan jämföra vad vi tillbringade förra året och vad vi planerar att spendera i år. Normalt skulle vi helt enkelt kopiera och klistra in dessa funktioner i intervallet E13:E16. Du kanske dock har märkt gränsen med dubbla linjer som användes runt omkretsen av intervallet B13: E16. Om vi använde kommandot vanlig klistra in skulle dubbellinjen på höger sida av intervallet E13:E16 ersättas med en enda rad. Därför kommer vi att använda en av Klistra in specialkommandon för att bara klistra in funktionerna utan någon av formateringsbehandlingarna. Detta uppnås genom följande steg:

1. markera intervallet D13: D16 i kalkylbladet Budgetdetaljer.
2. klicka på knappen Kopiera på fliken Hem i menyfliksområdet.
3. klicka på cell E13.
4. klicka på nedpilen under Klistra in-knappen på fliken Hem i menyfliksområdet.
5. klicka på alternativet formler i listrutan med knappar (se figur 2.26).

figur 2.26 visar listan med knappar som visas när du klickar på nedpilen under Klistra in-knappen på fliken Start i menyfliksområdet. En sak att notera om dessa alternativ är att du kan förhandsgranska dem innan du gör ett val genom att dra muspekaren över alternativen. Som visas i figuren, när muspekaren placeras över formlerna, kan du se hur funktionerna kommer att visas innan du gör ett val. Observera att gränsen med dubbla linjer inte ändras när det här alternativet förhandsgranskas. Det är därför detta val görs istället för det vanliga klistra in alternativet.