Mam liczby w komórkach w programie Excel. Chcę, aby liczby były sformatowane, aby jeśli miały miejsca po przecinku, pokazywały maksymalnie dwa, a jeśli nie mają miejsc po przecinku, nie pokazuje żadnych.

Na przykład.

• 15 należy sformatować jako 15, NIE 15.00
• 14.3453453 należy sformatować jako 14,35
• 12.1 należy sformatować jako 12.1
• 0 należy sformatować jako 0

Najbliższym niestandardowym kodem formatu, jaki wymyśliłem, jest 0.##. Niestety ta formatuje 15.00jako 15. (zwróć uwagę na dodatkowy punkt dziesiętny).

Aby dodatkowo skomplikować ten problem, arkusz kalkulacyjny jest wynikiem eksportu z SQL Server Reporting Services. Dlatego makra nie są możliwe. No cóż, wygląda na to, że 0.##to mój najlepszy zakład i mogą po prostu żyć z dodatkowym okresem.

Alternatywnie możesz rozwiązać problem „opcjonalnego” miejsca dziesiętnego, stosując następujące rozwiązanie:

Format liczbowy: General;[Red](General)

Spowoduje to dodanie odpowiednio miejsca dziesiętnego i wartości ułamkowej, a także sformatowanie liczb ujemnych w bardziej czytelny sposób.

Jeśli chodzi o pierwotne pytanie plakatu, nadal trzeba zaokrąglać / obcinać „dodatkowe” kropki dziesiętne za pomocą formuły. Jest to jednak prosta formuła, =ROUND(<value>,<desired decimal places>)która nie jest wyjątkowo droga obliczeniowo.

Przykłady:

2500 -> 2500
0.25 -> 0.25
-2500 -> (2500)
-0.25 -> (0.25)

Zastosuj formatowanie warunkowe dla liczb nie dziesiętnych.

Na przykład A1 jest komórką docelową.

1. Sformatuj A1 jako „###, ###. 00”. Zostanie to użyte dla liczby dziesiętnej.

2. Zdefiniuj formatowanie warunkowe dla liczb nie dziesiętnych.

   • Warunek: wartość komórki równa = TRUNC (A1).
   • Format: ###,###

Poniżej znajduje się wynik:

12       =>  12
14.231   => 14.23
15.00000 => 15
17.3     => 17.30

Niestandardowe formaty programu Excel mogą dostarczyć częściową odpowiedź

Niestandardowe formaty liczb w programie Excel są wprowadzane w tym formacie:

• format liczb dodatnich; format liczb ujemnych; format zerowy; format tekstowy

Jednym z formatów zbliżonych do twoich wymagań, ale pozostawiającym miejsca po przecinku dla liczb bez miejsc po przecinku jest:

• , ##. ??; (#, ##. ??); 0

Przykład:

• 15 jest wyświetlane jako 15.
• 14,3453453 jest wyświetlany jako 14,35
• 12.1 jest wyświetlane jako 12.1
• 0 jest wyświetlane jako 0

Natknąłem się na to niedawno w programie Excel 2007 i właśnie skończyło się na użyciu funkcji „TRUNC” w komórkach wartości:

  value  =TRUNC(B5,1)
      0      0
      5      5
    5.4    5.4
  65.43   65.4
765.432  765.4

Działało dokładnie tak, jak chciałem ...

Oto jedna metoda wykorzystująca formatowanie warunkowe.

1. Kliknij komórkę prawym przyciskiem myszy, wybierz „Formatuj komórkę”
2. Wybierz „Niestandardowy”
3. Wpisz „0. ####” (dodaj więcej lub mniej #, aby kontrolować maksymalną liczbę miejsc dziesiętnych)
4. Kliknij OK
5. Przy wciąż zaznaczonej komórce użyj „Formatowania warunkowego” (może to być element menu Format lub przycisk Strona główna w zależności od wersji programu Excel)

6. Dodaj nową regułę opartą na formule „= MOD (H32,1) = 0”

   edit - lepsza formuła to „= MOD (ROUND (H32,2), 1) = 0”, przy czym „2” oznacza żądaną liczbę miejsc po przecinku. poprzednia formuła pozostawia końcowy punkt dziesiętny, jeśli liczba zaokrągla liczbę całkowitą.

   Zamień H32 we wzorze na identyfikator swojej komórki, ale UPEWNIJ SIĘ, ŻE NIE MA ZNAKÓW $! (Brak znaków $ gwarantuje, że skopiujesz format do innych komórek)

7. Aby ustawić format tej reguły, wybierz kartę liczbową, wybierz „Niestandardowy”

8. Tym razem wprowadź formułę „0”
9. Kliknij OK tyle razy, aby wrócić do arkusza (różni się w zależności od wersji programu Excel)

Proszę bardzo, ciesz się odpowiedzialnie! Jeśli chcesz skopiować format do innych komórek, pamiętaj, że możesz skopiować komórkę i użyć opcji „Wklej specjalnie” z opcją „Format”.

Pamiętaj, że w Reporting Services możesz także napisać wyrażenie dotyczące formatowania liczb. W mojej sytuacji użyłem

iif(floor(Fields!numericFieldName.Value)=Fields!numericFieldName.Value,"0","0.##")

jako wyrażenie formatu liczbowego. W ten sposób utworzono plik Excel z dwoma formatami komórek, wybranymi przez to, czy wartość była liczbą całkowitą, czy nie.

Czy Ty / Twoi użytkownicy wprowadzacie wartości bezpośrednio w komórkach, czy też wypełniają je formuły lub makra?

Jeśli komórki nie są wypełniane bezpośrednio przez człowieka, to mógłby przechowywać obliczone wartości w ukrytym przedziale, a następnie wyświetlić tekst sformatowany do użytkownika z formułą tak:

=IF(ROUND(A1,2)=INT(A1),TEXT(A1,"0"),TEXT(A1,"0.0#"))

(gdzie „A1” oznacza odwołanie do komórki)

Formuła wyświetli następujące wartości:

 -------------------------
 |Original     |Formatted|
 |-------------+---------|
 |         15  |       15|
 | 14.3453453  |    14.35|
 |       12.1  |     12.1|
 |          0  |        0|
 |    -15.123  |   -15.12|
 |      1.004  |        1|
 -------------------------

NB: Wynik formuły jest ciągiem tekstowym, a nie liczbowym, więc:

• Wyjściowe ustawienie domyślne to wyrównanie do lewej.
• Nie można użyć danych wyjściowych w żadnych dalszych obliczeniach (zamiast tego należy użyć oryginalnej komórki, do której następuje odwołanie)

Sformatuj komórkę jako „Ogólne”, a następnie podczas sprawdzania poprawności danych ogranicz wartości do miejsc po przecinku

Nie musi to być takie skomplikowane - można to zrobić wyłącznie za pomocą formatowania warunkowego. Mam program Excel 2010, więc może nie działać we wcześniejszych wersjach.

1. Sformatuj komórki, aby były Ogólne. Liczba miejsc dziesiętnych dla niektórych liczb może być bardzo wysoka.
2. Podświetl komórkę i kliknij Formatowanie warunkowe. Kliknij Nowa reguła, a następnie „Użyj formuły”.
3. Użyj formuły = (B1-INT (B1))> 0 (gdzie B1 jest formatowaną komórką)
4. Kliknij przycisk Format i wybierz opcję Niestandardowy. Wpisz [= 0] 0;. ## w polu Typ.
5. Naciśnij Enter kilka razy.
6. Kliknij ponownie Formatowanie warunkowe, kliknij Zarządzaj regułami i edytuj właśnie utworzoną regułę. Zmień zakres w polu „Dotyczy”, aby objąć zakres, który chcesz uwzględnić.

Wszystko gotowe.

Moja odpowiedź usuwa przecinek dziesiętny ORAZ Usuwa wartość 0

w formatowaniu warunkowym napisz:

General;#;;@

To powinno załatwić sprawę.

Oprócz świetnej odpowiedzi Luke'a powyżej, tutaj jest wariant oparty na jego pracy. Nie chciałem mieć zera w komórce, jeśli nie było żadnej wartości. Pozostaw format komórki jako ogólny i użyj tej formuły:

=IF((A1=""), "",IF(ROUND(A1,2)=INT(A1),TEXT(A1,"0"),TEXT(A1,"0.0#")))

Ponownie, gdzie A1 jest komórką, do której następuje odwołanie.

Korzystanie z Roundfunkcji w celu zapewnienia znaczących liczb. Nie zmienia to jednak liczby wyświetlanych miejsc dziesiętnych. Wystarczy sformatować komórkę za pomocą generalformatu liczbowego.

ROUND(MyValue,Sig.figs - 1 - INT(LOG10(ABS(MyValue)))

B1=IF(ROUND(A1,2)=INT(A1),TEXT(A1,"0"),TEXT(A1,"0.##"))
C1=IF(ROUND(B1,2)=INT(B1),TEXT(B1,"0"),TEXT(B1,"0.##"))

Nigdy więcej sierocych miejsc po przecinku.

Wydaje się, że jest to najprostszy sposób wyświetlania dziesiętnego, gdy istnieją liczby ułamkowe - i braku dziesiętnego dla liczb całkowitych:

=TEXT(ROUND(Base_Number,Max_Decimal_Places),"general")

lub z odwołaniami do komórek:

=TEXT(ROUND(A1,2),"general")

Daje

15 = 15
9,23432234 = 9,23