Merwyn från England skickade in detta problem.
Finns det en enda formel i cell B2 som kan kopieras över och ner för att skapa en multiplikationstabell?
Merwyns mål är att ange en enda formel i B2 som enkelt kan kopieras till alla 144 celler för att skapa en multiplikationsreferensbord.
Låt oss attackera detta som en nybörjare i Excel och se vilka fallgropar vi stöter på. En första reaktion kan vara att ha formeln i B2 =A2*B1. Denna formel ger rätt resultat, men det är inte lämpligt för Merwyns uppdrag.
När du kopierar den här formeln från cell B2 till cell C2, rör sig cellerna som refereras i formeln också över en cell. Formeln som =A2*B1nu blev blev =C1*B2. Detta är en funktion utformad i Microsoft Excel och kallas en relativ formelreferens. När du kopierar en formel flyttar cellreferenser i formeln också ett motsvarande antal celler över och ner.
Det finns tillfällen när du inte vill att Excel ska visa detta beteende och det aktuella fallet är en av dessa. För att förhindra att Excel ändrar referensen när du kopierar cellerna, sätt in en "$" före den del av referensen som du vill frysa. Exempel:
- $ A1 säger till Excel att du alltid vill hänvisa till kolumn A.
- B $ 1 säger till Excel att du alltid vill hänvisa till rad 1.
- $ B $ 1 säger till Excel att du alltid vill hänvisa till cell B1.
Att lära sig denna kod kommer att dramatiskt minska den tid som krävs för att bygga kalkylblad. I stället för att behöva ange 144 formler kan Merwyn använda denna stenografi för att ange en enda formel och kopiera den till alla celler.
Med tanke på Merwyns formel =B1*A2inser vi att "B1" -delen av uttrycket alltid ska peka på ett tal i rad 1. Detta bör skrivas om som "B $ 1". "A2" -avsnittet i formeln bör alltid peka på ett nummer i kolumn A. Detta bör skrivas om som "$ A2". Så den nya formeln i cell B2 är =B$1*$A2.
Efter att ha skrivit in formeln i B2 kan jag kopiera och klistra in den i lämpligt intervall. Excel följer mina instruktioner och efter att ha gjort kopian hittar jag följande formler:
- Cell M2 innehåller
=M$1*$A2 - Cell B13 innehåller
=B$1*$A13 - Cell M13 innehåller
=M$1*$A13
Var och en av dessa typer av formler har ett namn. Formler utan några dollartecken kallas relativa formler. Formler där både raden och kolumnen är låsta med ett dollartecken kallas absoluta formler. Formler där antingen raden eller kolumnen är låsta med ett dollartecken kallas blandade formler.
Det finns en stenografisk metod för att ange dollartecknen. När du skriver en formel och avslutar en cellreferens kan du trycka på knappen för att växla mellan de fyra referenstyperna. Låt oss säga att du började skriva en formel och att du skrev =100*G87.
- Hit F4 och din formel ändras till
=100*$G$87 - Tryck på F4 igen och din formel ändras till
=100*G$87 - Tryck på F4 igen och din formel ändras till
=100*$G87 - Klicka på F4 igen och din formel återgår till originalet
=100*G87
Du kan pausa under formelinmatningen vid varje cellreferens för att slå F4 tills du får rätt referens typ. Tangenttryckningarna för att ange Merwyns formel ovan är =B1*A1.
En av mina favoritanvändningar av blandade formelreferenser kommer upp när jag har en lång lista med poster i kolumn A. När jag vill ha en snabb och smutsig metod för att hitta dubbletter skriver jag ibland in den här formeln i cell B4 och kopierar sedan ner den:
=VLOOKUP(A4,$A$2:$A3,1,FALSE)
Observera att den andra termen i VLOOKUP-formeln använder både en absolut ($ A $ 2) och en blandad ($ A3) referens för att beskriva uppslagsområdet. På engelska ber jag Excel att alltid söka från cell $ A $ 2 till cellen i kolumn A strax ovanför den aktuella cellen. Så snart Excel stöter på ett duplikatvärde ändras resultatet av denna formel från # N / A! till ett värde.
Med kreativ användning av $ i cellreferenser kan du se till att en enda formel kan kopieras till många celler med rätt resultat.
