Mam wartość w komórce w bajtach. Ale nikt nie może odczytać 728398112238. Wolałbym, żeby to było 678,37 GB
Pisanie formuły w celu jej sformatowania jest stosunkowo łatwe (tutaj: http://www.yonahruss.com/2007/02/format-excel-numbers-as-gb-mb-kb-b.html )
Ale czy istnieje sposób, aby to zrobić jako „format”? Chciałbym mieć dużą liczbę w komórce, ale wyświetlając ją w formacie czytelnym dla człowieka.
Odpowiedzi:
Naprawdę nie możesz wykonywać obliczeń w funkcjach formatowania programu Excel. Możesz jednak użyć czegoś takiego jak poniżej, aby dokonać przybliżonej oceny:
[<500000]#,##0" B";[<500000000]#,##0,," MB";#,##0,,," GB"
Oto jeden, którego używałem: -
[<1000000]0.00," KB";[<1000000000]0.00,," MB";0.00,,," GB"
Wydaje się, że działa dobrze.
[<1000]#" B";[<1000000]0.00," KB";0.00,," MB"
Chociaż warunki formatu Excel będą wyświetlać tylko 1 z 3 warunków związanych z rozmiarem liczby (kodują go jako „dodatni; ujemny; zero; tekst”, ale wolę widzieć to jako: jeśli to liczba i prawda; elseif to liczba i fałsz; elseif liczba; elseif to tekst )
więc dla mnie najlepszą odpowiedzią jest komentarz Davida, a także komentarz Grastveita dotyczący innego formatu regionalnego.
Oto te, których używam w zależności od raportów, które robię.
[<1000000]#,##0.00," KB";[<1000000000]#,##0.00,," MB";#,##0.00,,," GB"
[>999999999999]#,##0.00,,,," TB";[>999999999]#,##0.00,,," GB";#.##0.00,," MB"
[<1000000]# ##0,00 " KB";[<1000000000]# ##0,00 " MB";# ##0,00 " GB"
[>999999999999]# ##0,00 " TB";[>999999999]# ##0,00 " GB";# ##0,00 " MB"
Wybieraj!
Powyższe podejście do formatowania działa, ale tylko na trzech poziomach. Powyższe używane KB, MB i GB. Tutaj rozszerzyłem go do sześciu. Kliknij komórkę (y) prawym przyciskiem myszy i wybierz Formatuj komórki. Na karcie Liczba wybierz Niestandardowe. Następnie w polu Typ: wpisz:
[<1000]##0.00" B";[<1000000]##0.00," KB";##0.00,," MB"
Następnie wybierz OK. Dotyczy to B, KB i MB. Następnie po zaznaczeniu tych samych komórek kliknij Wstążka Narzędzia główne, Formatowanie warunkowe, Nowa reguła. Wybierz opcję Formatuj tylko komórki zawierające. Następnie poniżej w opisie reguły Formatuj tylko komórki za pomocą, Wartość komórki, większa lub równa 1000000000 (to jest 9 zer). Następnie kliknij Format, kartę Liczba, Niestandardowe, aw polu Typ: wpisz:
[<1000000000000]##0.00,,," GB";[<1000000000000000]##0.00,,,," TB";#,##0.00,,,,," PB"
Wybierz OK i OK. To formatowanie warunkowe przejmie kontrolę tylko wtedy, gdy wartość jest większa niż 1 000 000 000. I zajmie się zakresami GB, TB i PB.
567.00 B
5.67 KB
56.70 KB
567.00 KB
5.67 MB
56.70 MB
567.00 MB
5.67 GB
56.70 GB
567.00 GB
5.67 TB
56.70 TB
567.00 TB
5.67 PB
56.70 PB
Cokolwiek większe niż PB pojawi się po prostu jako większe PB, np. 56 700 PB. Możesz dodać kolejne formatowanie warunkowe, aby obsłużyć jeszcze większe wartości, EB i tak dalej.
Nie wiem, jak pokazać gigabajty binarne (wielokrotności 1024 * 1024 * 1024), ale możesz sprawić, by pokazywał gigabajty dziesiętne, używając formatu takiego jak:
0.00,,,"Gb"
Powyższa formuła wymaga znaku minus w pierwszym wierszu: „= JEŻELI (A1 <-999500000000”
=IF(A1<-999500000000,TEXT(A1,"#,##.#0,,,"" TB"""),
IF(A1<-9995000000,TEXT(A1,"#,##.#0,,,"" GB"""),
IF(A1<-9995000,TEXT(A1,"#,##0,,"" MB"""),
IF(A1<-9995,TEXT(A1,"#,##0,"" KB"""),
IF(A1<-1000,TEXT(A1,"#,##0"" B """),
IF(A1<0,TEXT(A1,"#,##0"" B """),
IF(A1<1000,TEXT(A1,"#,##0"" B """),
IF(A1<999500,TEXT(A1,"#,##0,"" KB"""),
IF(A1<999500000,TEXT(A1,"#,##0,,"" MB"""),
IF(A1<999500000000,TEXT(A1,"#,##.#0,,,"" GB"""),
TEXT(A1,"#,##.#0,,,,"" TB""")))))))))))
Jeszcze innym rozwiązaniem jest użycie notacji inżynierskiej. (To jest jak notacja naukowa, ale wykładnik jest zawsze wielokrotnością 3.) Kliknij prawym przyciskiem myszy komórkę (y) i wybierz Formatuj komórki. Na karcie Liczba wybierz Niestandardowe. Następnie w polu Typ: wpisz:
##0.00E+00
Następnie kliknij OK. Zamiast K, M itd. Będziesz miał +3, +6 itd. To zadziała dla liczb dodatnich i ujemnych, a także dodatnich i ujemnych wykładników, -3 to m, -6 to u itd.
567.00E-06
5.67E-03
56.70E-03
567.00E-03
5.67E+00
56.70E+00
567.00E+00
5.67E+03
56.70E+03
567.00E+03
5.67E+06
Używam CDH hadoop i kiedy eksportuję raport Excela, mam dwa problemy;
1) przekonwertuj datę Linuksa na datę Excela.
W tym celu dodaj pustą kolumnę obok kolumny z datą, powiedzmy, że górny wiersz to B4, wklej poniższą formułę i przeciągnij CZARNY „+” aż do ostatniego dnia na końcu kolumna. Następnie ukryj oryginalną kolumnę
=(((B4/1000/60)/60)/24)+DATE(1970|1|1)+(-5/24)
2) Konwertuj rozmiar dysku z bajtów na TB, GB i MB
. Najlepsza formuła na to
[>999999999999]# ##0.000,,,," TB";[>999999999]# ##0.000,,," GB";# ##0.000,," MB"
da ci wartości z 3 miejscami po przecinku, po prostu sformatuj komórki -> Niestandardowe i wklej tam powyższy kod
To trochę "brutalna siła", ale działa;)
=IF(E4/1000<1;CONCATENATE(E4;" bps");IF(E4/1000<1000;CONCATENATE(ROUND(E4/1000;2);" kbps");IF(E4/1000000<1000;CONCATENATE(ROUND(E4/1000000;2);" mbps");IF(E4/1000000000<1000;CONCATENATE(ROUND(E4/1000000000;2);" gbps")))))
Wklej to obok swoich wartości (bajtów), a to automatycznie zmieni go na dowolny rozmiar wartości.
=IF(G10>=1099511627776,CONCATENATE(ROUND((G10/1024/1024/1024/1024),1)," TB"),IF(G10>=1073741824,CONCATENATE(ROUND((G10/1024/1024/1024),1)," GB"),IF(G10>=1048576,CONCATENATE(ROUND((G10/1024/1024),1)," MB"),IF(G10>=1024,CONCATENATE(ROUND((G10/1024),1)," KB"),IF(G10>=1,CONCATENATE((G10)," BYTES"),0)))))
Wszystkie odpowiedzi tutaj dostarczają wartości z potęgami 10. Oto format używający odpowiednich jednostek SI (wielokrotności 1024, tj. Mebibajtów, Gibibajtów i Tebibajtów):
[>1099511627776]#.##,,,," TiB";[>1073741824]#.##,,," GiB";0.##,," MiB"
Obsługuje MiB, GiB i TiB pokazujące dwa miejsca po przecinku.
Po zobaczeniu odpowiedzi tutaj właśnie ulepszyliśmy tę formułę, aby mieć miejsca dziesiętne na większych wartościach i uwzględniać wartości ujemne.
=IF(A1<999500000000,TEXT(A1,"#,##.#0,,,"" TB"""),
IF(A1<-9995000000,TEXT(A1,"#,##.#0,,,"" GB"""),
IF(A1<-9995000,TEXT(A1,"#,##0,,"" MB"""),
IF(A1<-9995,TEXT(A1,"#,##0,"" KB"""),
IF(A1<-1000,TEXT(A1,"#,##0"" B """),
IF(A1<0,TEXT(A1,"#,##0"" B """),
IF(A1<1000,TEXT(A1,"#,##0"" B """),
IF(A1<999500,TEXT(A1,"#,##0,"" KB"""),
IF(A1<999500000,TEXT(A1,"#,##0,,"" MB"""),
IF(A1<999500000000,TEXT(A1,"#,##.#0,,,"" GB"""),
TEXT(A1,"#,##.#0,,,,"" TB""")))))))))))
Podejrzewam, że wiele odpowiedzi tutaj jest nieaktualnych, ponieważ nie uzyskałem oczekiwanego wyniku z udzielonej odpowiedzi.
Jeśli masz wartość w KB, którą chcesz sformatować zgodnie z rozmiarem, możesz spróbować wykonać następujące czynności.
[<1000]#" KB ";[<1000000]#0,00 " MB";0,## " GB"
952 => 952 KB
1514 => 1.51 MB
5122323 => 5.12 GB
Aby uzyskać dokładny wynik, wolałbym go obliczyć, ale używając formatu wyświetlania.
Zakładając, że komórka A1 ma wartość 29773945664927.
Policz liczbę przecinków w komórce B1.
= ILOŚĆ (DŁ (A1) -1,3)
Podziel wartość przez 1024 ^ B1 w komórce C1.
= A1 / 1024 ^ B1
Wyświetlacz w komórce D1.
= PRZEŁĄCZ (B1, 5, „PB”, 4, „TB”, 3, „GB”, 2, „MB”, 1, „KB”, 0, „B”)
Ukryj komórkę B1.