Moje doświadczenia z Excelem, Accesem i programowaniem w VBA

Wpisy z tagiem: adres bezwzględny

niedziela, 17 czerwca 2012

O adresie względnym i bezwzględnym pisałm juz kiedyś:
Adres względny i bezwzględny
Tym razem spróbuję odpowiedzieć na pytanie - jak zamienić domyślnie ustawiony adres względny na bezwzględny. Oczywiście pomijając ręczne wpisywanie znaku $ do formuły.
W pojedyńczych przypadkach wystarczy wejść w edycję formuły i ustawić się na oznaczeniu komórki:

 adres względny i bezwzględny

Kolejny krok to naciśnięcie F4. I już. 

adres względny i bezwzględny 

Jeśli jednak w arkuszu formuły występują w wielu komórkach - będzie to pracochłonne.
W takim wypadku najlepszym wyjściem jest stworzenie procedury w VBA, która wykona to za nas. Najlepiej wykorzystać tu funkcję Application.ConvertFormula()

Na przykład tak:

Public Sub ZmienADRES
Dim Komorka As  Range
Dim C

  For Each Komorka In Range("A2:C14")      
                       'Obszar do zamiany formuł dostosowujemy do własnych potrzeb
    C = Left(Komorka.Formula, 1)
    If C = "=" Then
       Komorka.Formula = Application.ConvertFormula(Formula:=Komorka.Formula,    fromReferenceStyle:=xlA1, ToReferenceStyle:=xlA1,ToAbsolute:=True)
   Next Komorka
       End If
End Sub

Oczywiście, przedstawiony tu przykład można modyfikować w zależności od potrzeb. Kod prosty i krótki. I działa, choć zdaje się, że dopiero od wersji Excel 2007 - nie jestem jednak pewna.

 

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

 

 


poniedziałek, 26 kwietnia 2010

W swojej codziennej pracy często muszę szybko zrobić statystyki z pracujących w trybie wielodostępu plików na serwerze. Kilku różnych użytkowników edytujących poszczególne komórki, dodających kolejne wiersze do tabeli.
Najprostszym rozwiązaniem byłaby tabela przestawna, ale w plikach udotępnionych - nie działa, więc nie można jej tu wykorzystać. Jak sobie poradzić?
Ja na swoim komputerze zakładam nowy plik, kopiuję nagłówek, a w kolejnych wierszach - wstawiam łącza do pliku na serwerze.
Posłużę się tu już wcześniejszym przykładem katalogu książek. Jego pełna nazwa (wraz z ścieżką dostępu na moim komputerze) to:
E:\serwer\Katalog.xls

katalog

W moim pliku statystyk
(C:\Documents and Settings\Desktop\Statystyki_katalogu.xls)
pierwszy wiersz to również nagłówek, a kolejne komórki tworzę wpisując łącza do pliku Katalog.xls.
Na wszelki wypadek wyjaśniam, że najprościej to zrobić w ten sposób, że mając jednocześnie otwarte obydwa te pliki, w komórce A2 w pliku Statystyki_katalogu wpisuję znak równości, przechodzę do pliku Katalog, również do komórki A2 i wciskam Enter.
W komórce pojawi sie wartość 1, a w polu formuły pojawia się:
=[Katalog.xls]spis!$A$2
Wpisany adres jest adresem bezwzględnym. Skopiowanie formuły do kolejnej komórki - nie zmieni jej. Konieczne jest więc wycięcie znaków $
Po korekcie:
=[Katalog.xls]spis!A2
Skopiowana w ten sposób komórka A2 na całą tabelę - przeniesie odpowiednie wartości odowiednich komórek w odpowiednie miejsca.
Jest jeszcze jeden problem. Jeżeli jakaś komórka w pliku źródłowym będzie pusta - to w pliku statystyk pojawi się zero. Spowoduje to zaciemnienie obrazu tabeli, a w wielu przypadkach - zafałszowanie statystyk. Zero jest też wartością, niekiedy bardzo istotną.
Ja radzę sobie za pomocą funkcji Jeżeli...to  
=JEŻELI([Katalog.xls]spis!A2="";"";[Katalog.xls]spis!A2)
Jeżeli wartość komórki A2 (w pliku Katalog) jest pusta (oznaczamy to podwójnym cudzysłowem) - to wstaw "nic" (podwójny cudzysłów), w przeciwnym przypadku - wstaw wartość komórki A2.
I już.
Przy otwarciu pliku Statystyk będzie pojawiał się komunikat informujący o łączach i pytanie, czy zaktualizować ich wartość.

aktualizacja łączy

Plik statystyk jest gotowy do analizy.

czwartek, 11 lutego 2010

Każda komórka w arkuszu Excela ma swój adres. Na przykład pierwsza komórka (lewy górny róg), na przecięciu kolumny A i wiersza numer 1 ma swój adres A1. Zapis A1 jest to adres względny komórki, jej adres bezwzględny to $A$1. Oczywiście można to zapisać również w sposób mieszany:
$A1 - bezwzględne odwołanie do kolumny, względne do wiersza
A$1 - względne odwołanie do kolumny, bezwzględne do wiersza

Tyle teorii. Wyglada to malo ciekawie i od razu wywołuje pytanie: po co to wszystko? Cóż, jest to ważne i istotne. Najlepiej zobaczyć to na przykładzie.

W arkuszu mamy fragment tabeli. W wierszu 2 (pierwszy to nagłówek tabeli) , w komórce C2 wpisujemy formułę C2=A2/B2 - Liczbę dzielimy przez dzielnik i otrzymujemy wynik1. Podobna sytuacja występuje w komórce D2, tylko tym razem adres dzielnika jest zapisany w jako adres bezwzględny : D2=A2/$B$2. Oczywiście wynik działania jest identyczny.
Różnice zaczynją być widoczne w kolejnych wierszach. Jeżeli skopiujemy warości formuł z kolumn C i D - tam gdzie są adresy względne - automatycznie formuła zmieni adres dzielnika, tam gdzie bezwzględne - dzIelnikiem zawsze będzie komórka B2.  

   adres względny i bezwzględny

W efekcie otrzymamy tez różne wyniki :

adres względny 

W zależności więc od tego, czy przy kopiowaniu formuł chcemy lub nie na stałe odnieść się do jakiejś komórki - używamy adresów względnych lub bezwzględnych.
Excel domyślnie przy kopiowaniu używa adresów względnych. Gdy jednak będziemy wstawiać powiązania z danymi z innego skoroszytu - domyślnym ustawieniem będą adresy bezwględne.

 

 Czy są pytania?  

| < Listopad 2017 > |
Pn Wt Śr Cz Pt So N
    1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30      


Książki warte polecenia
zobacz szczegóły...


A tu oferta na dziś:





ministat liczniki.org



Napisz do mnie!