Преобразование данных размера файла Excel

Question

Преобразование данных размера файла Excel

Я часто получаю данные в Excel о размерах файлов, где значения могут включать в себя такие вещи, как:

      0
1K
850K
7.12K
19.2M
43.1G
1012G
28T
1.02P

(ок, последнее не так часто, но в конце концов это теоретическая возможность)

Мне часто нужно определить большие или маленькие выбросы, отсортировать их по размеру или использовать условное форматирование, чтобы увидеть, какие из них больше, чем окружающие.

Мои первые 2 попытки были:

      =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"T","000000000000"),"M","000000"),"K","000"),"G","000000000")
=IFERROR(LOG(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"T","000000000000"),"M","000000"),"K","000"),"G","000000000"),10),0)

(журнал эффективно преобразуется в «количество цифр», а iferror обрабатывает нулевой случай)

Хотя это и неточно с точки зрения соотношения 1000 против 1024, для меня это не имеет значения, поскольку меня интересует только обнаружение выбросов, а второй отлично подходит для моих целей, но он полностью терпит неудачу, когда присутствует десятичная точка.

В идеале я ищу что-то быстрое и хакерское, что я могу скопировать/вставить (или даже лучше ввести), потратив 5 минут на интерпретацию журналов или отчетов на предмет аномалий. Я никогда не собираюсь сохранять листы, которые использую, просто извлеките информацию, получить от нее информацию, а затем перейти к следующей работе.

Выделение красного цвета, если он содержит «G», и фиолетового, если он содержит «T», было бы для меня почти достаточно, но между 1G и 990G слишком большая разница.

Есть хорошие предложения?

Если бы он действительно был надежным с точки зрения поддержки кратных 1024 и мог обрабатывать МБ так же, как и М, это было бы здорово, но не обязательно для моих целей.

1

microsoft-excel worksheet-function size

Источник

Thingomy 08 апр '21 в 16:01

1 ответ

Другие вопросы по тегам microsoft-excel worksheet-function size

Engineer Toast 08 апр '21 в 18:01 2021-04-08 18:01 · Answer 1 · 2021-04-08 18:01

Основной ответ:

Есть более прямой способ сделать это, который также легче расширить для включения файлов большего размера (как если бы это действительно было необходимо, когда вы уже считаете в петабайтах). Если ваши данные начинаются с , формула будет такой:

      =IFERROR(VALUE(A1),VALUE(LEFT(A1,LEN(A1)-1)))*1000^IFERROR(SEARCH(RIGHT(A1),"KMGTPEZY"),0)

Есть две большие части этого:

      IFERROR(VALUE(A1),VALUE(LEFT(A1,LEN(A1)-1)))

Первый извлекает числовую часть строки, пытаясь выполнить прямое преобразование текста в число, и, если это не удается, он удаляет самый правый символ и повторяет попытку. Это будет работать до тех пор, пока ваш текст всегда представляет собой допустимое число, за которым следует 0 или 1 нечисловой символ.

      1000^IFERROR(SEARCH(RIGHT(A1),"KMGTPEZY"),0)

Вторая половина позволяет умножить полученное число на некоторую степень 10 на основе последнего символа. Это делается путем извлечения самого правого символаRIGHT(A1)и ищем его в спискеSEARCH(~,"KMGTPEZY"). Если он не может найти символ в этом списке, по умолчанию он равен нулю.IFERROR(~,0). Затем он возводит 1000 в эту степень.1000^~. Если самый правый символK, это будет1000^1 = 1,000. ДляG, это будет10^3 = 1,000,000,000. Если значение указано в байтах, то самый правый символ будет числом и, следовательно, его не будет в списке благодаряIFERROR, это будет1000^0 = 1.

1000 против 1024:

Для пояснения: приведенная выше формула основана на десятичной версии счета (K = килобайт = 1000 байт). Если вы работаете в двоичном формате (K = кибибайт = 1024 байта), вам нужно немного изменить вторую половину:

      =IFERROR(VALUE(A1),VALUE(LEFT(A1,LEN(A1)-1)))*1024^IFERROR(SEARCH(RIGHT(A1),"KMGTPEZY"),0)

Обработка «Б» в конце:

Что касается обработкиKB,MBи тому подобное, вы можете изменить ссылки наA1удалятьB. Вместо того, чтобы делать формулу еще длиннее, мы можем использовать функцию для сохраненияSUBSTITUTE()результат в виде одного символа для дальнейшего использования.

      =LET(s,SUBSTITUTE(A1,"B",""),IFERROR(VALUE(s),VALUE(LEFT(s,LEN(s)-1)))*1000^IFERROR(SEARCH(RIGHT(s),"KMGTPEZY"),0))

Если у вас нетLET()(я думаю, что это только для Office 365), тогда вы можете придерживаться действительно длинной версии ИЛИ (что я бы рекомендовал) создать вспомогательный столбец для удаления любого экземпляра B.

_{Длинная версия формулы:}

      =IFERROR(VALUE(SUBSTITUTE(A1,"B","")),VALUE(LEFT(SUBSTITUTE(A1,"B",""),LEN(SUBSTITUTE(A1,"B",""))-1)))*1000^(IFERROR(SEARCH(RIGHT(SUBSTITUTE(A1,"B","")),"KMGTPEZY"),0)

_{Версия вспомогательного столбца:}

      =IFERROR(VALUE(B1),VALUE(LEFT(B1,LEN(B1)-1)))*1000^(IFERROR(SEARCH(RIGHT(B1),"KMGTPEZY"),0)