Merwyn fra England sendte dette problem ind.
Er der en enkelt formel i celle B2, som kan kopieres på tværs og ned for at oprette en multiplikationstabel?
Merwyns mål er at indtaste en enkelt formel i B2, som let kan kopieres til alle 144 celler for at oprette en multiplikationsreferencetabel.
Lad os angribe dette som en Excel-novice og se, hvilke faldgruber vi løber ind i. En indledende reaktion kan være at have formlen i B2 =A2*B1. Denne formel giver det korrekte resultat, men det er ikke egnet til Merwyns opgave.
Når du kopierer denne formel fra celle B2 til celle C2, bevæger de celler, der henvises til i formlen, også over en celle. Den formel, der =A2*B1nu blev, bliver =C1*B2. Dette er en funktion designet i Microsoft Excel og kaldes en relativ formelreference. Når du kopierer en formel, flytter cellehenvisningerne i formlen også et tilsvarende antal celler over og ned.
Der er tidspunkter, hvor du ikke ønsker, at Excel skal udvise denne adfærd, og den aktuelle sag er en af dem. For at forhindre Excel i at ændre referencen, mens du kopierer cellerne, skal du indsætte en "$" før den del af referencen, som du vil fryse. Eksempler:
- $ A1 fortæller Excel, at du altid vil henvise til kolonne A.
- B $ 1 fortæller Excel, at du altid vil henvise til række 1.
- $ B $ 1 fortæller Excel, at du altid vil henvise til celle B1.
At lære denne kode vil dramatisk reducere den tid, det tager at oprette regneark. I stedet for at skulle indtaste 144 formler, kan Merwyn bruge denne stenografi til at indtaste en enkelt formel og kopiere den til alle celler.
Når vi ser på Merwyns formel, =B1*A2indser vi, at "B1" -delen af udtrykket altid skal pege på et tal i række 1. Dette skal omskrives som "B $ 1". Afsnittet "A2" i formlen skal altid pege på et tal i kolonne A. Dette skal omskrives som "$ A2". Så den nye formel i celle B2 er =B$1*$A2.
Efter at have indtastet formlen i B2 kan jeg kopiere og indsætte den i det relevante område. Excel følger mine instruktioner, og efter at have lavet kopien finder jeg følgende formler:
- Celle M2 indeholder
=M$1*$A2 - Celle B13 indeholder
=B$1*$A13 - Celle M13 indeholder
=M$1*$A13
Hver af disse typer formler har et navn. Formler uden dollartegn kaldes relative formler. Formler, hvor både rækken og kolonnen er låst med et dollartegn, kaldes absolutte formler. Formler, hvor enten rækken eller kolonnen er låst med et dollartegn, kaldes blandede formler.
Der er en stenografisk metode til indtastning af dollartegn. Når du skriver en formel og afslutter en cellereference, kan du trykke på tasten for at skifte mellem de 4 referencetyper. Lad os sige, at du begyndte at skrive en formel, og at du skrev =100*G87.
- Hit F4, og din formel ændres til
=100*$G$87 - Hit F4 igen, og din formel ændres til
=100*G$87 - Hit F4 igen, og din formel ændres til
=100*$G87 - Klik på F4 igen, og din formel vender tilbage til originalen
=100*G87
Du kan holde pause under formelindtastningen ved hver cellereference for at ramme F4, indtil du får den rigtige referencetype. Tastetryk for at indtaste Merwyns formel ovenfor er =B1*A1.
En af mine foretrukne anvendelser af blandede formelhenvisninger dukker op, når jeg har en lang liste med poster i kolonne A. Når jeg vil have en hurtig og beskidt metode til at finde dubletter, indtaster jeg undertiden denne formel i celle B4 og kopierer den derefter ned:
=VLOOKUP(A4,$A$2:$A3,1,FALSE)
Bemærk, at 2. sigt i VLOOKUP-formlen bruger både en absolut ($ A $ 2) og en blandet ($ A3) reference til at beskrive opslagsområdet. På engelsk fortæller jeg Excel at søge altid fra celle $ A $ 2 til cellen i kolonne A lige over den aktuelle celle. Så snart Excel støder på en duplikatværdi, ændres resultatet af denne formel fra # N / A! til en værdi.
Med kreativ brug af $ i cellereferencer kan du sørge for, at en enkelt formel kan kopieres til mange celler med korrekte resultater.
