У меня есть значение в ячейке, которое в байтах. Но никто не может прочитать 728398112238. Я бы предпочел сказать 678.37GB
Чтобы написать формулу для ее форматирования относительно просто (здесь один: http://www.yonahruss.com/2007/02/format-excel-numbers-as-gb-mb-kb-b.html)
Но есть ли способ сделать это как "формат"? Я хотел бы иметь большое количество в ячейке, но отображать его как удобочитаемый формат.
Вы не можете делать вычисления в форматировании Excel. Вы можете использовать что-то вроде следующего, чтобы сделать приблизительную оценку:
[<500000]#,##0" B ";[<500000000]#,##0,," MB";#,##0,,," GB"
Вот то, что я использовал: -
[<1000000]0.00," KB";[<1000000000]0.00,," MB";0.00,,," GB"
Кажется, что он работает нормально.
Хотя условия формата Excel будут отображать только 1 из 3 условий, связанных с размером номера (они обозначают его как "положительный, отрицательный, ноль, текст", но я предпочитаю видеть его как: if isnumber и true; elseif isnumber и false; elseif number; elseif - текст)
так что лучшим ответом будет Дэвид, а также комментарий Grastveit для другого регионального формата.
Вот те, которые я использую в зависимости от отчетов, которые я делаю.
[<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"
Возьмите свой выбор!
Приведенный выше подход к форматированию работает, но только для трех уровней. Выше используются КБ, МБ и ГБ. Здесь я расширил его до шести. Щелкните правой кнопкой мыши по ячейкам и выберите "Формат ячеек". На вкладке Number выберите Custom. Затем в поле Type: введите следующее:
[<1000]##0.00" B";[<1000000]##0.00," KB";##0.00,," MB"
Затем выберите ОК. Это охватывает B, KB и MB. Затем, выбрав те же ячейки, нажмите Домашняя лента, Условное форматирование, Новое правило. Выберите Форматировать только те ячейки, которые содержат. Затем, ниже в описании правила, Форматируйте только ячейки с, Значение ячейки, большее или равное, 1000000000 (то есть 9 нулей). Затем нажмите на вкладку Формат, Число, Пользовательский и в поле Тип: введите следующее:
[<1000000000000]##0.00,,," GB";[<1000000000000000]##0.00,,,," TB";#,##0.00,,,,," PB"
Выберите ОК и ОК. Это условное форматирование вступит во владение, только если значение больше чем 1 000 000 000. И он позаботится о диапазонах GB, TB и 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
Все, что больше PB, будет отображаться как большее PB, например, 56 700 PB. Вы можете добавить другое условное форматирование для обработки еще больших значений, EB и так далее.
Незначительное изменение, чтобы заставить его работать в моем регионе, Европа (в качестве разделителя тысяч, запятая в виде десятичного разделителя):
[<1000000]#.##0,00" KB";[<1000000000]#.##0,00.." MB";#.##0,00..." GB"
Еще одна проблема при преобразовании данных (1000!= 1024), но она выполняет эту работу для меня.
Я не знаю, как заставить его показывать вам бинарные гигабайты (кратные 1024 * 1024 * 1024), но вы можете заставить его показывать десятичные гигабайты с использованием формата, например:
0.00,,,"Gb"
Выше формулы требуется знак минус в первой строке: "= IF (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""")))))))))))
И, еще одно решение, заключается в использовании инженерных обозначений. (Это похоже на научную запись, за исключением показателя степени, всегда кратного 3.) Щелкните правой кнопкой мыши ячейку (я) и выберите Формат ячеек. На вкладке Number выберите Custom. Затем в поле Type: введите следующее:
##0.00E+00
Затем нажмите ОК. Вместо K, M и т.д. У вас будут +3, +6 и т.д. Это будет работать для положительных и отрицательных чисел, а также для положительных и отрицательных показателей, -3 равно m, -6 равно u, так далее.
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
[< 1000] 0 "GB"; [ > 999] 0,0, "TB"
ИЛИ
[< 1000] 0 "GB"; [ >= 1000] 0,0, "TB"
Я использую CDH hadoop, и когда я экспортирую отчет Excel, у меня возникают две проблемы;
1) преобразовать дату Linux в дату Excel,
Для этого добавьте пустой столбец рядом со столбцом даты, скажем, верхняя строка - B4, вставьте ниже формулы и перетащите ЧЕРНЫЙ "+" до последнего дня в конце столбца. Затем скрыть исходный столбец
=(((B4/1000/60)/60)/24)+DATE(1970|1|1)+(-5/24)
2) Преобразовать размер диска из байтов в ТБ, ГБ и МБ.
лучшая формула для этого является
[>999999999999]# ##0.000,,,," TB";[>999999999]# ##0.000,,," GB";# ##0.000,," MB"
он даст вам значения с 3 десятичными знаками, просто отформатируйте ячейки → Custom и вставьте туда приведенный выше код
Это немного "грубой силы", но работает;)
=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")))))
После просмотра ответов здесь только улучшена эта формула, чтобы иметь десятичные знаки при больших значениях и обслуживать отрицательные значения.
=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""")))))))))))
Я подозреваю, что многие ответы здесь устарели, так как я не получил ожидаемый результат от данного ответа.
Если у вас есть значение в килобайтах, которое вы хотите отформатировать в соответствии с размером, вы можете попробовать следующее.
[<1000]#" KB ";[<1000000]#0,00 " MB";0,## " GB"
952 => 952 KB
1514 => 1.51 MB
5122323 => 5.12 GB
Для точного результата я бы лучше рассчитал его, но с использованием формата отображения.
Предполагая, что ячейка A1 имеет значение 29773945664927.
Подсчитайте количество запятых в ячейке B1.
= QUOTIENT (LEN (А1) -1, 3)
Разделите значение на 1024 ^ B1 в ячейке C1.
= A1/1024 ^ B1
Дисплей в ячейке D1.
= ПЕРЕКЛЮЧАТЕЛЬ (B1, 5, "PB", 4, "TB", 3, "GB", 2, "MB", 1, "KB", 0, "B")
Скрыть ячейку B1.