Spis treści
Często użytkownicy stają przed koniecznością przypięcia komórki do formuły. Na przykład występuje w sytuacjach, w których chcesz skopiować formułę, ale aby łącze nie przesuwało się w górę iw dół o taką samą liczbę komórek, jak zostało skopiowane z oryginalnej lokalizacji.
W takim przypadku możesz naprawić odwołanie do komórki w programie Excel. Można to zrobić na kilka sposobów jednocześnie. Przyjrzyjmy się bliżej, jak osiągnąć ten cel.
Co to jest link do Excela
Arkusz składa się z komórek. Każdy z nich zawiera określone informacje. Inne komórki mogą go używać w obliczeniach. Ale jak rozumieją, skąd wziąć dane? Pomaga im tworzyć linki.
Każdy link oznacza komórkę z jedną literą i jedną cyfrą. Litera reprezentuje kolumnę, a liczba reprezentuje wiersz.
Istnieją trzy rodzaje linków: bezwzględne, względne i mieszane. Drugi jest ustawiony domyślnie. Odwołanie bezwzględne to takie, które ma stały adres zarówno kolumny, jak i kolumny. W związku z tym mieszany to taki, w którym ustalona jest oddzielna kolumna lub wiersz.
Metoda 1
Aby zapisać adresy kolumn i wierszy, wykonaj następujące kroki:
- Kliknij komórkę zawierającą formułę.
- Kliknij pasek formuły dla komórki, której potrzebujemy.
- Naciśnij F4.
W konsekwencji odwołanie do komórki zmieni się na bezwzględne. Można go rozpoznać po charakterystycznym znaku dolara. Na przykład, jeśli klikniesz komórkę B2, a następnie klikniesz F4, link będzie wyglądał tak: $B$2.
Co oznacza znak dolara przed częścią adresu na komórkę?
- Jeśli jest umieszczony przed literą, oznacza to, że odwołanie do kolumny pozostaje takie samo, bez względu na to, gdzie formuła została przeniesiona.
- Jeśli znak dolara znajduje się przed liczbą, oznacza to, że ciąg jest przypięty.
Metoda 2
Ta metoda jest prawie taka sama jak poprzednia, tylko musisz dwukrotnie nacisnąć F4. na przykład, jeśli mamy komórkę B2, to po tym stanie się ona B$2. W prostych słowach w ten sposób udało nam się naprawić linię. W takim przypadku zmieni się litera kolumny.
Jest to bardzo wygodne np. w tabelach, gdzie trzeba wyświetlić zawartość drugiej komórki od góry w dolnej komórce. Zamiast wielokrotnie powtarzać taką formułę, wystarczy naprawić wiersz i pozwolić, aby kolumna się zmieniła.
Metoda 3
Jest to zupełnie to samo, co poprzednia metoda, tylko musisz trzykrotnie nacisnąć klawisz F4. Wtedy tylko odwołanie do kolumny będzie bezwzględne, a wiersz pozostanie stały.
Metoda 4
Załóżmy, że mamy bezwzględne odniesienie do komórki, ale tutaj konieczne było uczynienie go względnym. Aby to zrobić, naciśnij klawisz F4 tyle razy, aby w łączu nie było żadnych znaków $. Wtedy stanie się względna, a kiedy przeniesiesz lub skopiujesz formułę, zmienią się zarówno adres kolumny, jak i adres wiersza.
Przypinanie komórek dla dużego zasięgu
Widzimy, że powyższe metody nie stanowią żadnej trudności do wykonania. Ale zadania są specyficzne. I na przykład co zrobić, jeśli mamy na raz kilkadziesiąt formuł, w których linki trzeba zamienić na bezwzględne.
Niestety standardowe metody Excela nie osiągną tego celu. Aby to zrobić, musisz użyć specjalnego dodatku o nazwie VBA-Excel. Zawiera wiele dodatkowych funkcji, które pozwalają znacznie szybciej wykonywać typowe zadania w programie Excel.
Zawiera ponad sto funkcji zdefiniowanych przez użytkownika i 25 różnych makr, a także jest regularnie aktualizowana. Pozwala usprawnić pracę niemal w każdym aspekcie:
- Komórki.
- Makro
- Funkcje różnych typów.
- Łącza i tablice.
W szczególności ten dodatek umożliwia jednoczesne naprawianie łączy w dużej liczbie formuł. Aby to zrobić, musisz wykonać następujące czynności:
- Wybierz zakres.
- Otwórz zakładkę VBA-Excel, która pojawi się po instalacji.
- Otwórz menu "Funkcje", w którym znajduje się opcja "Zablokuj formuły".
6 - Następnie pojawi się okno dialogowe, w którym należy określić wymagany parametr. Ten dodatek pozwala przypiąć kolumnę i kolumnę osobno, razem, a także usunąć już istniejące przypinanie z pakietem. Po wybraniu wymaganego parametru za pomocą odpowiedniego przycisku opcji, musisz potwierdzić swoje działania, klikając „OK”.
Przykład
Weźmy przykład, aby to było bardziej jasne. Załóżmy, że mamy informacje opisujące koszt towaru, jego całkowitą ilość i przychody ze sprzedaży. A my stajemy przed zadaniem sprawienia, aby stół, na podstawie ilości i kosztu, automatycznie określał, ile pieniędzy udało nam się zarobić bez odliczania strat.
W naszym przykładzie musisz w tym celu wprowadzić formułę =B2*C2. Jak widać, to całkiem proste. Bardzo łatwo jest użyć jej przykładu, aby opisać, jak poprawić adres komórki lub jej pojedynczą kolumnę lub wiersz.
Oczywiście w tym przykładzie możesz spróbować przeciągnąć formułę w dół za pomocą znacznika autouzupełniania, ale w tym przypadku komórki zostaną automatycznie zmienione. Czyli w komórce D3 będzie kolejna formuła, w której liczby zostaną zastąpione odpowiednio przez 3. Dalej, zgodnie ze schematem – D4 – formuła przyjmie postać = B4 * C4, D5 – podobnie, ale z numer 5 i tak dalej.
Jeśli jest to konieczne (w większości przypadków się okazuje), to nie ma problemów. Ale jeśli musisz naprawić formułę w jednej komórce, aby nie zmieniała się podczas przeciągania, będzie to nieco trudniejsze.
Załóżmy, że musimy określić dochód w dolarach. Umieśćmy to w komórce B7. Nabierzmy trochę nostalgii i podajmy koszt 35 rubli za dolara. W związku z tym, aby określić przychód w dolarach, konieczne jest podzielenie kwoty w rublach przez kurs dolara.
Oto jak to wygląda w naszym przykładzie.
Jeśli podobnie jak w poprzedniej wersji spróbujemy przepisać formułę, to nam się nie uda. Podobnie formuła zmieni się na odpowiednią. W naszym przykładzie będzie to tak: =E3*B8. Stąd widzimy. że pierwsza część formuły zamieniła się w E3 i stawiamy sobie to zadanie, ale nie musimy zmieniać drugiej części formuły na B8. Dlatego musimy zmienić odniesienie w absolutne. Możesz to zrobić bez naciskania klawisza F4, po prostu wstawiając znak dolara.
Po zamianie odniesienia do drugiej komórki na bezwzględną, została ona zabezpieczona przed zmianami. Teraz możesz bezpiecznie przeciągnąć go za pomocą uchwytu autouzupełniania. Wszystkie stałe dane pozostaną takie same, niezależnie od pozycji formuły, a niezatwierdzone dane będą się zmieniać elastycznie. We wszystkich komórkach przychód w rublach opisany w tym wierszu zostanie podzielony przez ten sam kurs dolara.
Sama formuła będzie wyglądać tak:
=D2/$B7$
Uwaga! Wskazaliśmy dwa znaki dolara. W ten sposób pokazujemy programowi, że należy naprawić zarówno kolumnę, jak i wiersz.
Odwołania do komórek w makrach
Makro to podprogram, który pozwala zautomatyzować działania. W przeciwieństwie do standardowej funkcjonalności programu Excel, makro umożliwia natychmiastowe ustawienie określonej komórki i wykonanie określonych czynności w zaledwie kilku wierszach kodu. Przydatne do przetwarzania wsadowego informacji, na przykład, jeśli nie ma możliwości zainstalowania dodatków (na przykład używany jest komputer firmowy, a nie osobisty).
Najpierw musisz zrozumieć, że kluczową koncepcją makra są obiekty, które mogą zawierać inne obiekty. Obiekt Workbooks odpowiada za książkę elektroniczną (czyli dokument). Zawiera obiekt Arkusze, który jest zbiorem wszystkich arkuszy otwartego dokumentu.
W związku z tym komórki są obiektem Cells. Zawiera wszystkie komórki danego arkusza.
Każdy obiekt jest kwalifikowany argumentami w nawiasach. W przypadku komórek są one przywoływane w tej kolejności. Numer wiersza jest podany jako pierwszy, a następnie numer kolumny lub litera (oba formaty są dopuszczalne).
Na przykład wiersz kodu zawierający odwołanie do komórki C5 wyglądałby tak:
Skoroszyty(„Książka2.xlsm”).Arkusze(„Lista2”).Komórki(5, 3)
Skoroszyty(„Książka2.xlsm”).Arkusze(„Lista2”).Komórki(5,„C”)
Możesz także uzyskać dostęp do komórki za pomocą obiektu Uporządkowane. Ogólnie rzecz biorąc, ma to na celu podanie odniesienia do zakresu (którego elementy, nawiasem mówiąc, mogą być również bezwzględne lub względne), ale możesz po prostu podać nazwę komórki w tym samym formacie, co w dokumencie Excel.
W takim przypadku linia będzie wyglądać tak.
Skoroszyty(„Książka2.xlsm”).Arkusze(„Lista2”).Zakres(„C5”)
Może się wydawać, że ta opcja jest wygodniejsza, ale zaletą dwóch pierwszych opcji jest to, że można użyć zmiennych w nawiasach i podać link, który nie jest już bezwzględny, ale coś w rodzaju relatywnego, który będzie zależał od wyników obliczenia.
Dzięki temu makra mogą być efektywnie wykorzystywane w programach. W rzeczywistości wszystkie odwołania do komórek lub zakresów będą tutaj bezwzględne i dlatego można je również naprawić. To prawda, że nie jest to takie wygodne. Użycie makr może być przydatne podczas pisania złożonych programów o dużej liczbie kroków w algorytmie. Ogólnie rzecz biorąc, standardowy sposób używania odwołań bezwzględnych lub względnych jest znacznie wygodniejszy.
wnioski
Dowiedzieliśmy się, czym jest odwołanie do komórki, jak działa i do czego służy. Zrozumieliśmy różnicę między odniesieniami bezwzględnymi i względnymi i ustaliliśmy, co należy zrobić, aby zmienić jeden typ na inny (prostymi słowami: naprawić adres lub odpiąć go). Dowiedzieliśmy się, jak możesz to zrobić od razu z dużą liczbą wartości. Teraz masz możliwość korzystania z tej funkcji we właściwych sytuacjach.