Niestandardowa funkcja użytkownika bez użycia VBA

Czy można utworzyć niestandardową funkcję użytkownika w programie Excel bez użycia VBA?
Mój problem polega na tym, że mam długi ciąg standardowych funkcji programu Excel połączonych razem, aby utworzyć jedną bardzo długą funkcję. Ta funkcja jest używana w 25 różnych arkuszach w moim skoroszycie. Jeśli muszę wprowadzić zmiany, chciałbym to zrobić tylko w jednym miejscu i automatycznie propagować zmiany na wszystkich arkuszach.

Na przykład prostym i trywialnym przypadkiem byłoby dodanie jednego do wyniku SUM (), tj. SUM (mySeries) +1 i nazwij to nową funkcją MYSUM ().

Waham się przed przetłumaczeniem długiej formuły na VBA ze względu na potencjalne błędy, które mogą się pojawić i dodatkową złożoność.

Tak, jest to możliwe, jeśli używasz nazwanych formuł programu Excel .

Załóżmy na przykład, że musisz obliczyć różnicę między sumami dwóch kolejnych kolumn ( A5: Axe - B5: Bx ) w różnych miejscach skoroszytu ( x jest ostatnim rzędem każdej kolumny):

Zatem w A11 definiujesz nazwę o nazwie diff (można użyć dowolnej nazwy) jako = Sum (A 5 USD: A10) - Sum (B 5 USD: B10) , zakładając, że dane zaczynają się w wierszu 5 aż do poprzedniego wiersza. Może to być dowolna komórka, nie tylko A11 , ale definicja zmienia się w ten sam sposób.

Niestety, Excel 2010 wstawia prefiksy absolutne ( $ ) i prefiksy arkusza roboczego, więc musisz usunąć prefiksy, ale zachować wykrzykniki i usunąć większość znaków $ .

Podczas przenoszenia formuł większość odniesień jest względna. Dlatego zawsze oblicza różnicę między bieżącą kolumną a następną, zaczynając od wiersza 5, aż do linii przed linią całkowitą.

Więc jeśli masz dane między C5 a D100 , po prostu umieść = Diff w C101 i oblicza Suma (C5: C100) - Suma (D5: D100) .

Oczywiście w nazwanych formułach można używać nazw lokalnych lub globalnych, tak jak wspomniano w pytaniu.

Możesz przeczytać więcej szczegółów w Nazwanych formułach .

Wiem, że nie powiedziałeś VBA, ale postępowanie w następujący sposób NIE wymaga ponownego pisania formuł, wiedzy o VBA ani utrzymywania formuł w VBA. Możesz to napisać raz i zapomnieć o tym.

Utwórz funkcję zdefiniowaną przez użytkownika, aby wyodrębnić formułę z komórki jako ciąg znaków:

Function GetFormula(Target As Range) As String
    GetFormula = Target.Formula
End Function

Utwórz inny, aby ocenić ciąg podobny do formuły:

Function Eval(Ref As String)
    Application.Volatile
    Eval = Evaluate(Ref)
End Function

Gdybyś miał swoją formułę główną w arkuszu1! A1, umieściłbyś to w każdej komórce, która musi jej użyć:

=eval(getformula(sheet1!a1))

Ten wątek jest nieco stary, ale natknąłem się na niego, szukając czegoś innego i pomyślałem, że podzielę się skoroszytem, ​​który utworzyłem, próbując osiągnąć dokładnie to samo: https://www.dropbox.com/s/gf5qrjj5q81tpke/ Title_Case_Named_Range_Function.xlsx? Dl = 1

W skrócie, MOŻESZ utworzyć UDF bez VBA (w pewnym sensie), przejmując jedną z wbudowanych formuł Excela z ciągiem wyjściowym o długości 0 i opracowując metodę interpretacji wstawionych danych za pomocą manipulacji ciągiem nazwanym, ale jest to poza tym nieefektywnym. ZOSTAŁEŚ OSTRZEŻONY! :)

METODA:

1. W przykładzie użyłem zarówno = REPT ([odwołanie do komórki lub tekst między cytatami tutaj], 0) i = TEKST ([odwołanie do komórki lub tekst między cytatami tutaj], ";;;") jako punkt początkowy może mieć jakąś formę wprowadzania danych przez użytkownika do wbudowanej formuły, która nie generowałaby żadnego widocznego tekstu w samej komórce.

2. Następnie stworzyłem Nazwany zakres, który byłby ekwiwalentem twojej nazwy UDF ( TCase jest podstawowym przykładem w arkuszu, ale jest tam również kilka innych odmian). Ten Nazwany Zakres faktycznie odwołuje się do całego szeregu innych ukrytych nazw w skoroszycie, aby wyodrębnić FormulaText z komórki, w której jest używany, a następnie zastosować do niego logikę, aby uzyskać końcowy wynik w postaci łańcucha. Warto zauważyć, że ta metoda może zwracać tylko wyniki tekstowe, a nie liczby (choć może zwracać liczbę jako tekst do konwersji w osobnej komórce).

3. Na własną rękę, = TCase generuje błąd, ponieważ nie ma odniesienia komórka / string na to interpretować (w przykładowym arkuszu, komunikat o błędzie jest tylko przypomnieniem formatu, które muszą być przestrzegane). Ale połączenie TCase z oczekiwaną formułą Excel o ciągach o długości 0 pozwala na odczyt danych wejściowych ze wzoru jako ciąg i zastosowanie do niej logiki. Na koniec formuła / wynik wyglądałby mniej więcej tak jak poniżej. Zauważ, że wstawiłem ciąg tekstowy bezpośrednio do poniższej formuły, ale w przykładowym arkuszu zobaczysz, że może on również interpretować odwołanie do pojedynczej komórki.

   FORMUŁA:
   =TCase&REPT("i want to convert this mIxEd sTRing into A TITLE cAsE string!!!",0)

   WYNIK:

   I Want to Convert This Mixed String Into a Title Case String!!!

W końcu nie zalecałbym stosowania tej metody w celach innych niż chorobliwa ciekawość, aby zobaczyć, do jakich limitów można wykorzystać program Excel. VBA jest znacznie prostszym i eleganckim rozwiązaniem UDF, ale przynajmniej dobrze się bawiłem podejmując wyzwanie.

UWAGA: Jeśli chcesz zobaczyć, jak to działa w pliku przykładowym, użyj opcji Oceń formułę ze wstążki Formuły i wprowadź / wyjdź z formuły w jednej z komórek, do których wstawiłem przykład. Ukryłem wszystkie bazowe Nazwane Zakresy, aby wszystko wyczyścić po zakończeniu, aby nie pojawiały się w Menedżerze nazw, chyba że odkryjesz je wszystkie.