Merwyn z Anglii przysłał to zadanie.
Czy w komórce B2 jest jedna formuła, którą można skopiować w poprzek iw dół, aby utworzyć tabliczkę mnożenia?
Celem Merwyn jest wprowadzenie jednej formuły w B2, którą można łatwo skopiować do wszystkich 144 komórek, aby utworzyć tabelę odniesienia mnożenia.
Zaatakujmy to jako nowicjusz w Excelu i zobaczmy, jakie pułapki napotkamy. Pierwszą reakcją może być otrzymanie wzoru w B2 =A2*B1. Ta formuła daje poprawny wynik, ale nie nadaje się do przypisania Merwyn.
Podczas kopiowania tej formuły z komórki B2 do komórki C2 komórki, do których odwołuje się formuła, również przesuwają się po jednej komórce. Formuła, która była =A2*B1teraz, staje się =C1*B2. Jest to funkcja zaprojektowana w programie Microsoft Excel i nazywana odwołaniem do formuły względnej. Podczas kopiowania formuły odwołania do komórek w formule również przenoszą odpowiednią liczbę komórek w poprzek i w dół.
Są chwile, kiedy nie chcesz, aby program Excel wykazywał takie zachowanie i bieżący przypadek jest jednym z nich. Aby program Excel nie zmieniał odwołania podczas kopiowania komórek, wstaw „$” przed częścią odwołania, którą chcesz zablokować. Przykłady:
- $ A1 mówi programowi Excel, że zawsze chcesz odnosić się do kolumny A.
- B $ 1 mówi Excelowi, że zawsze chcesz odnosić się do wiersza 1.
- $ B $ 1 mówi programowi Excel, że zawsze chcesz odwoływać się do komórki B1.
Poznanie tego kodu znacznie skróci czas potrzebny do tworzenia arkuszy. Zamiast wpisywać 144 formuły, Merwyn może użyć tego skrótu, aby wprowadzić jedną formułę i skopiować ją do wszystkich komórek.
Patrząc na formułę Merwyna =B1*A2, zdajemy sobie sprawę, że część „B1” wyrażenia powinna zawsze wskazywać liczbę w wierszu 1. Należy to przepisać na „B $ 1”. Sekcja „A2” formuły powinna zawsze wskazywać liczbę w kolumnie A. Należy ją przepisać na „$ A2”. Tak więc nowa formuła w komórce B2 to =B$1*$A2.
Po wpisaniu wzoru w B2 mogę go skopiować i wkleić do odpowiedniego zakresu. Excel postępuje zgodnie z moimi instrukcjami i po skopiowaniu znajduję następujące formuły:
- Cell M2 zawiera
=M$1*$A2 - Komórka B13 zawiera
=B$1*$A13 - Komórka M13 zawiera
=M$1*$A13
Każdy z tych typów formuł ma swoją nazwę. Formuły bez znaków dolara nazywane są formułami względnymi. Formuły, w których zarówno wiersz, jak i kolumna są zablokowane znakiem dolara, nazywane są formułami absolutnymi. Formuły, w których wiersz lub kolumna są zablokowane znakiem dolara, nazywane są formułami mieszanymi.
Istnieje skrócona metoda wprowadzania znaków dolara. Podczas wpisywania formuły i kończenia odwołania do komórki możesz nacisnąć klawisz, aby przełączyć się między 4 typami odwołań. Załóżmy, że zacząłeś wpisywać formułę i wpisałeś =100*G87.
- Naciśnij F4, a twoja formuła zmieni się na
=100*$G$87 - Naciśnij ponownie F4, a twoja formuła zmieni się na
=100*G$87 - Naciśnij ponownie F4, a twoja formuła zmieni się na
=100*$G87 - Naciśnij ponownie F4, a twoja formuła powróci do oryginału
=100*G87
Możesz wstrzymać wprowadzanie formuły w każdym odwołaniu do komórki, aby nacisnąć klawisz F4, aż uzyskasz odpowiedni typ odwołania. Klawisze wprowadzające powyższą formułę Merwyn to =B1*A1.
Jedno z moich ulubionych zastosowań odwołań do formuł mieszanych pojawia się, gdy mam długą listę wpisów w kolumnie A.Gdy potrzebuję szybkiej i brudnej metody znajdowania duplikatów, czasami wprowadzam tę formułę w komórce B4, a następnie kopiuję ją:
=VLOOKUP(A4,$A$2:$A3,1,FALSE)
Zwróć uwagę, że drugi termin formuły WYSZUKAJ.PIONOWO używa zarówno odwołania bezwzględnego ($ A $ 2), jak i mieszanego ($ A3), aby opisać zakres wyszukiwania. W języku angielskim mówię programowi Excel, aby wyszukiwał zawsze od komórki $ A 2 $ do komórki w kolumnie A tuż nad bieżącą komórką. Gdy tylko Excel napotka zduplikowaną wartość, wynik tej formuły zmieni się z # N / A! do wartości.
Dzięki kreatywnemu wykorzystaniu znaku $ w odwołaniach do komórek możesz mieć pewność, że pojedynczą formułę można skopiować do wielu komórek z poprawnymi wynikami.
