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

  • Dotyczy: Microsoft Excel

Objawy

Podczas obliczania arkusza niektóre komórki mogą wydawać się mieć zły wynik.

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ący się do zakresu komórek w arkuszu.

  • Wynik funkcji niestandardowej zależy od większej liczby komórek niż te, do których odnosi się bezpośrednio.

To zachowanie jest zamierzonym działaniem 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 funkcji niestandardowej. Jeśli wynik funkcji zależy od komórek, które nie są jawnie odwoływane przez funkcję, te komórki mogą nie zostać ponownie obliczone.

Rozwiązanie

Aby obejść ten problem, użyj dowolnej z poniższych metod.

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

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

Metoda 2. Ustawianie funkcji niestandardowej jako nietrwałej

Microsoft dostarcza przykłady programowania wyłącznie w celu zilustrowania i nie oferuje żadnej gwarancji, wyraźnej ani dorozumianej. w tym także, ale nie tylko, dorozumianych gwarancji zbywalności lub przydatności 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 wyjaśnić funkcjonalność określonej procedury, ale nie zmodyfikują tych przykładów, aby zapewnić dodatkowe funkcje lub procedury konstruowania w celu spełnienia określonych wymagań. Jeśli funkcja niestandardowa będzie niestabilna, ten problem nie wystąpi. Aby ustawić funkcję niestandardową jako nietrwałą, dodaj następujący wiersz kodu do funkcji.

Application.Volatile

Uwaga / Notatka

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

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

Naciśnij CTRL+ALT+F9, aby ponownie obliczyć wartości we wszystkich otwartych skoroszytach. W programie Microsoft Office Excel 2007 naciśnij 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:

  1. Zamknij i zapisz wszystkie otwarte skoroszyty, a następnie otwórz nowy skoroszyt.

  2. Uruchom Edytor Visual Basic (naciśnij ALT+F11).

  3. W menu Wstawianie kliknij pozycję Moduł.

  4. Wpisz następujący kod w arkuszu modułów:

    ' 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

  5. Naciśnij ALT+F11, aby powrócić do programu Excel.

  6. Wpisz Test, w komórce A2, a następnie naciśnij ENTER.

  7. Wpisz następującą formułę w komórce A10, a następnie naciśnij ENTER:

    =FindTextUp(A9)

    Formuła zwraca wartość 7.

  8. Wpisz inny test w komórce A5, a następnie naciśnij 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, tak aby oczekiwany wynik był obliczany.

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)
  • Last updated on