Az angliai Merwyn küldte ezt a problémát.
Van-e egyetlen képlet a B2 cellában, amelyet át és át lehet másolni a szorzótábla létrehozásához?
Merwyn célja, hogy egyetlen képletet adjon meg a B2-ben, amelyet könnyen át lehet másolni mind a 144 cellába, hogy létrehozzon egy szorzási referenciatáblát.
Támadjuk ezt Excel-kezdőként, és nézzük meg, milyen csapdákba ütközünk. Az egyik kezdeti reakció az lehet, hogy a képlet a B2-ben szerepel =A2*B1. Ez a képlet megfelelő eredményt ad, de nem alkalmas Merwyn hozzárendelésére.
Ha ezt a képletet átmásolja a B2 cellából a C2 cellába, akkor a képletben hivatkozott cellák is egy cellán mozognak. A képlet, ami =A2*B1most lett, válik =C1*B2. Ez a Microsoft Excelbe tervezett szolgáltatás, és relatív képletreferenciának hívják. Egy képlet másolása közben a képlet cellahivatkozásai is megfelelő számú cellát mozgatnak át és lefelé.
Van, amikor nem szeretné, hogy az Excel megjelenítse ezt a viselkedést, és a jelenlegi eset egy ilyen. Annak megakadályozására, hogy az Excel megváltoztassa a referenciát a cellák másolása közben, helyezzen be egy "$" -t a referencia azon része elé, amelyet be akar fagyasztani. Példák:
- Az $ A1 azt mondja az Excelnek, hogy mindig az A oszlopra kíván hivatkozni.
- A B $ 1 azt mondja az Excelnek, hogy mindig az 1. sorra kíván hivatkozni.
- A $ B $ 1 azt mondja az Excelnek, hogy mindig a B1 cellára akar hivatkozni.
Ennek a kódnak a megtanulása drámai módon csökkenti a munkalapok elkészítéséhez szükséges időt. Ahelyett, hogy 144 képletet kellene megadnia, Merwyn ezt a rövidítést felhasználhatja egyetlen képlet beírására és az összes cellába másolására.
Merwyn képletét tekintve =B1*A2rájövünk, hogy a kifejezés "B1" részének mindig egy számra kell mutatnia az 1. sorban. Ezt "B $ 1" néven kell átírni. A képlet "A2" szakaszának mindig egy számra kell mutatnia az A oszlopban. Ezt "$ A2" néven kell átírni. Tehát a B2 cellában az új képlet az =B$1*$A2.
Miután beírtam a képletet a B2-be, átmásolhatom és beilleszthetem a megfelelő tartományba. Az Excel követi az utasításokat, és a másolás elvégzése után a következő képleteket találom:
- Az M2 cella tartalmazza
=M$1*$A2 - A B13 sejt tartalmaz
=B$1*$A13 - Az M13 sejt tartalmazza
=M$1*$A13
Az ilyen típusú képletek mindegyikének van neve. A dollárjel nélküli képleteket relatív képleteknek nevezzük. Azokat a képleteket, amelyekben a sort és az oszlopot egyaránt dollárjel zárja, abszolút képleteknek nevezzük. Azokat a képleteket, amelyekben a sort vagy az oszlopot dollárjel zárja, vegyes képleteknek hívjuk.
Rövidített módszer van a dollárjelek bevitelére. Amikor képletet gépel és befejez egy cellahivatkozást, a 4 hivatkozástípus közötti váltáshoz nyomja meg a gombot. Tegyük fel, hogy elkezdett egy képletet gépelni, és beírta =100*G87.
- Hit F4 és a képlet változik
=100*$G$87 - Ismét nyomja meg az F4 billentyűt, és a képlete megváltozik
=100*G$87 - Ismét nyomja meg az F4 billentyűt, és a képlete megváltozik
=100*$G87 - Ismét nyomja meg az F4 billentyűt, és képlete visszatér az eredetire
=100*G87
Szüneteltetheti a képlet bevitelét az egyes cellahivatkozásoknál, hogy elérje az F4-et, amíg meg nem kapja a megfelelő referencia-típust. A Merwyn fenti képletének megadásához a billentyűleütések a =B1*A1.
A vegyes képletreferenciák egyik kedvenc felhasználása akkor merül fel, ha hosszú bejegyzésem van az A oszlopban. Ha gyors és piszkos módszert szeretnék találni az ismétlésekre, néha beírom ezt a képletet a B4 cellába, majd lemásolom:
=VLOOKUP(A4,$A$2:$A3,1,FALSE)
Ne feledje, hogy a VLOOKUP képlet 2. kifejezése abszolút ($ A $ 2) és vegyes ($ A3) hivatkozást is használ a keresési tartomány leírására. Magyarul azt mondom az Excel-nek, hogy mindig keressen a $ A $ 2 cellától az A oszlop cellájáig, közvetlenül az aktuális cella felett. Amint az Excel megismétli az ismétlődő értéket, a képlet eredménye # N / A értékre változik! értékre.
A $ cellahivatkozások kreatív felhasználásával biztos lehet abban, hogy egyetlen képletet helyes cellával sok cellába lehet másolni.
