Funkcja niestandardowa może nie obliczać oczekiwanej wartości w programie Excel

2025-05-26
Dotyczy: Microsoft Excel

Symptomy

Podczas obliczania arkusza może się wydawać, że niektóre komórki obliczyły nieprawidłową wartość.

Przyczyna

Ten problem występuje, gdy są spełnione następujące warunki:

Komórka w arkuszu zawiera funkcję niestandardową.
Funkcja niestandardowa zawiera co najmniej jeden argument odwołującego się do zakresu komórek w arkuszu.
Wynik funkcji niestandardowej zależy od większej liczby komórek niż odwołuje się bezpośrednio.

To zachowanie jest projektem programu Microsoft Excel. Gdy program Excel oblicza komórkę zawierającą funkcję niestandardową, oblicza ponownie wszystkie zakresy komórek, które są przekazywane jako argumenty do funkcji niestandardowej. Jeśli wynik funkcji zależy od komórek, które nie są jawnie określane przez funkcję, te komórki mogą nie zostać ponownie obliczone.

Obejście problemu

Aby obejść ten problem, użyj dowolnej z następujących metod.

Metoda 1. Zmodyfikuj funkcję tak, aby wszystkie odpowiednie zakresy komórek były przekazywane

Zmodyfikuj funkcję tak, aby akceptowała jako argumenty wszystkie komórki niezbędne do obliczenia wyniku funkcji.

Metoda 2. Utrwałość funkcji niestandardowej

Firma Microsoft podaje przykłady programowania tylko dla celów ilustracyjnych, nie udzielając żadnej rękojmi, wyrażonej wprost ani dorozumianej, w tym także, ale nie tylko, dorozumianej rękojmi co do przydatności handlowej lub do określonych celów. W tym artykule zakłada się, że czytelnik zna demonstrowany język programowania oraz narzędzia używane do tworzenia i debugowania procedur. Inżynierowie pomocy technicznej firmy Microsoft mogą pomóc w objaśnieniu funkcjonalności określonej procedury, ale nie zmodyfikują tych przykładów w celu zapewnienia dodatkowych funkcji lub procedur konstruowania w celu spełnienia określonych wymagań. Jeśli sprawisz, że funkcja niestandardowa stanie się nietrwała, ten problem nie wystąpi. Aby uczynić funkcję niestandardową nietrwałą, dodaj następujący wiersz kodu do funkcji.

Application.Volatile

Uwaga

Jeśli funkcja niestandardowa stanie się nietrwała, będzie obliczany ponownie za każdym razem, gdy wprowadzisz zmianę wartości lub ponownie obliczysz otwarty skoroszyt. Może to mieć wpływ na wydajność modelu arkusza.

Metoda 3. Wymuszanie ponownego obliczania wszystkich otwartych skoroszytów przez program Excel

Naciśnij klawisze CTRL+ALT+F9, aby ponownie obliczyć wartości we wszystkich otwartych skoroszytach. W programie Microsoft Office Excel 2007 naciśnij klawisze CTRL+ALT+SHIFT+F9, aby ponownie obliczyć wartości we wszystkich otwartych skoroszytach.

Więcej informacji

Przykład problemu

Aby zilustrować ten problem, wykonaj następujące kroki:

Zamknij i zapisz wszystkie otwarte skoroszyty, a następnie otwórz nowy skoroszyt.
Uruchom Redaktor Visual Basic (naciśnij klawisze ALT+F11).
W menu Insert (Wstaw) kliknij polecenie Module (Moduł).

Wpisz następujący kod w arkuszu modułu:

' This function counts the number of blank cells by starting from the cell 
' referred to by the rngStartCell argument and moving up the column.

Function FindTextUp(rngStartCell As Range) As Single
    Dim iIndex As Integer

For iIndex = 0 To 100
        If rngStartCell.Value <> "" Then
            FindTextUp = iIndex
            Exit Function
        Else
            Set rngStartCell = rngStartCell.Offset(-1, 0)
        End If
    Next iIndex
End Function

Naciśnij klawisze ALT+F11, aby powrócić do programu Excel.
Wpisz Test w komórce A2, a następnie naciśnij klawisz ENTER.
Wpisz następującą formułę w komórce A10, a następnie naciśnij klawisz ENTER:
```
=FindTextUp(A9)
```
Formuła zwraca wartość 7.
Wpisz inny test w komórce A5, a następnie naciśnij klawisz ENTER.

Formuła nadal zwraca wartość 7, gdy oczekiwana jest wartość 4. W tym przykładzie funkcja FindTextUp jawnie odwołuje się do komórki A9. Jednak funkcja może zależeć od komórek A1:A8, w zależności od danych wprowadzonych w arkuszu.

Jeśli zaimplementujesz metodę 1 w sekcji "Obejście" tego artykułu, funkcja obliczy oczekiwany wynik. Poniższy wiersz kodu ilustruje sposób modyfikowania funkcji w tym przykładzie, aby obliczyć oczekiwany wynik.

Function FindTextUp(rngStartCell As Range, rngOtherCells As Range) As Single

Teraz, jeśli zastąpisz wywołanie funkcji w kroku 7 następującym wywołaniem funkcji, funkcja zawsze zwróci oczekiwany wynik.

=FindTextUp(A9,A1:A8)

Udostępnij za pośrednictwem