To pytanie jest trochę stare, ale do tej pory nie wydaje się, aby miało na nie odpowiedź. Widziałem również podobne pytania na wielu stronach, ale nie znalazłem jeszcze żadnej odpowiedzi, która dotyczyłaby tylko wbudowanych funkcji Excela. Jednak rozwiązanie VBA jest dość łatwe. Nie musisz nawet wiedzieć, jak programować, aby to zrobić, ponieważ wymagana funkcja jest tak prosta.
Po otwarciu skoroszytu po prostu naciśnij klawisze Alt + F11 (aby otworzyć edytor VBA). Następnie kliknij pozycję menu Wstaw > Moduł
W nowym module VBA wprowadź następujące dane:
Public Function FMT$(ByVal Value, ByVal strFormat)
FMT = VBA.Format$(Value, strFormat)
End Function
To po prostu używa własnej Formatfunkcji VBA zamiast funkcji Excela TEXT. To jest dokładnie to, czego chcesz.
Ta nowa funkcja sformatuje dowolną datę (lub liczbę) zgodnie z podanym ciągiem formatu. Zinterpretuje ciąg formatu przy użyciu standardowej notacji ( en-US ), niezależnie od regionalnych ustawień systemu operacyjnego.
Aby użyć tego w skoroszycie, po prostu wpisz =FMT(A1, "yyyymmdd_hhss")zamiast =TEXT(A1, "yyyymmdd_hhss"). (Oczywiście możesz w razie potrzeby zmienić odwołanie do komórki i formatować ciąg.)
Nawiasem mówiąc, możesz nazwać funkcję, jak chcesz. Wybrałem, FMTponieważ był krótki i ponieważ funkcja może formatować zarówno liczby, jak i daty. Ale jeśli chcesz, możesz wybrać coś bardziej opisowego. Pamiętaj tylko, aby użyć w arkuszu tej samej nazwy, co w kodzie VBA.
Zauważ, że ciągi formatu VBA nie są dokładnie takie same jak ciągi formatu niestandardowego Excela (na przykład używaj „n” zamiast „m” dla minut), ale są one bardzo podobne. Poszukaj tutaj szczegółów lub wyszukaj w MSDN „Format Function (Visual Basic for Applications)”. Przewiń w dół, aby wyświetlić różne specyfikatory formatu daty.
Metoda 2
Innym podejściem, które również wykorzystuje VBA, ale może być łatwiejsze w utrzymaniu, jest:
- Zastosuj żądany format daty do komórki, używając normalnego okna dialogowego Format komórki. Ta komórka może znajdować się na ukrytym arkuszu, jeśli wolisz - nie musi być wyświetlana użytkownikowi końcowemu. Załóżmy, że zastosowałeś format
yyyymmdd\_hhssdo komórki $ A $ 1 (zwróć uwagę, że znak podkreślenia musi być poprzedzony znakiem jak pokazano).
- Dodaj
GetFormatfunkcję (pokazaną poniżej) do modułu VBA w swoim skoroszycie.
- Wejdź
=GetFormat($A$1, TRUE)do innej komórki (np. $ B $ 1 )
- Ta funkcja zwróci zlokalizowaną wersję ciągu formatu. Więc nawet jeśli pierwotnie sformatowany $ A $ 1 ze
yyyymmdd\_hhsspo otwarciu skoroszytu na komputerze przy użyciu języka francuskiego (na przykład), funkcja pokaże aaaammjj\_hhss.
- Teraz po prostu odwołaj się do drugiej komórki we wszystkich swoich
TEXTfunkcjach. Na przykład: =TEXT(F22, $B$1).
Oto kod VBA:
Public Function GetFormat$(Cell As Range, Optional ByVal UseLocal As Boolean)
If UseLocal Then
GetFormat = Cell.NumberFormatLocal
Else
GetFormat = Cell.NumberFormat
End If
End Function
Dzięki temu możemy „sprawdzić” pierwotnie sformatowaną komórkę (1 A $ 1 USD) w celu pobrania zlokalizowanego ciągu formatu. Ten ciąg będzie reprezentował ten sam format, który zastosowałeś, ale użyje poprawnych liter do interpretacji TEKSTU (na przykład „j” zamiast „d”), więc wyświetlana wartość dat będzie stała we wszystkich lokalizacjach. Jeśli chcesz używać tylko jednego formatu daty dla całego skoroszytu, musisz wykonać kroki 1-4 tylko raz. Następnie powtórz krok 5 (funkcja TEKST) we wszystkich komórkach, w których go obecnie używasz, ale zamiast na stałe kodować format, po prostu odwołaj się do komórki zawierającej zlokalizowany ciąg formatu ( $ B $ 1 w przykładowych instrukcjach) .
Zauważ, że drugi argument GetFormatmówi funkcji, czy zwrócić zlokalizowaną wersję (która zależy od ustawień regionalnych) czy wersję „standardową” (która zawsze opiera się na en-US).
Oto kilka zrzutów ekranu, które mogą to wyjaśnić.
- Na rysunku kolumna 1 zawiera kilka reprezentacji jednej daty z zastosowaniem różnych formatów.
- Pamiętaj, że wiersze 2 i 3 używają formatów daty „domyślnych” w programie Excel. (Są one oznaczone wiodącą gwiazdką w oknie dialogowym Format i wskazują, że należy użyć domyślnego formatu daty użytkownika.) Należy również pamiętać, że wiersz 5 używa nawiasów LCID, które wymuszają język używany w nazwach miesięcy i dni na angielski ( różne LCID mogą być użyte do określenia innych języków).
- Druga kolumna pokazuje wynik
GetFormat(Cell, FALSE)dla każdej komórki w kolumnie 1. (Przypomnij, że FALSEdla drugiego parametru powoduje, że funkcja zwraca formaty nie zlokalizowane ).
- Trzecia kolumna pokazuje, co
GetFormat(Cell, TRUE)zwraca każda komórka w kolumnie 2. (tj. Zlokalizowane formaty).
- Czwarta kolumna pokazuje wynik funkcji TEKST z wykorzystaniem oryginalnej, surowej wartości daty i zlokalizowanego wyniku
GetFormatponownego odtworzenia formatu pokazanego w kolumnie 1. Zwróć jednak uwagę, że w tej kolumnie nie zastosowano formatowania liczbowego. Wartości są bezpośrednim wynikiem funkcji TEKST.
Wyniki powyższego przypadku w języku angielskim (USA) nie są bardzo interesujące, ale można je porównać z następującymi wynikami uzyskanymi przez zmianę ustawień regionalnych mojego systemu operacyjnego na różne inne ustawienia regionalne. Co ważne, pamiętaj, że używając GetFormatw połączeniu z, TEXTjesteśmy w stanie zachować stały wynik dla formatów numerycznych (tych, które nie zawierają nazw dni lub miesięcy) we wszystkich lokalizacjach. A ograniczając język za pomocą LCID (jak w wierszu 5), możemy nawet zachować stały format, uwzględniając również nazwy dni i miesięcy.
Ta metoda działa w przypadku większości ustawień narodowych, należy jednak zauważyć, że skrypty używane do reprezentowania liczb hindusko-arabskich NIE są takie same we wszystkich lokalizacjach. Dlatego ustawienia regionalne, takie jak ustawienia nepalskie (Indie), spowodują, że formaty daty będą wyglądały inaczej niż daty w USA. Ale w rzeczywistości są one w tym samym „formacie” pod względem pozycji liczb - po prostu używają różnych symboli dla liczb.
