Как суммировать столбец с ячейками, содержащими текст?

Question

У меня есть столбец, который содержит значения размера данных, с числами и изменяющимся текстом. Некоторые имеют значения, такие как «3,54 ТБ», а некоторые - "620 ГБ". В конечном итоге я хочу получить общий размер данных для данного столбца. Например, 3,54 ТБ + 620 ГБ = 4,16 ТБ.

Если бы я мог отправить сумму ячеек, содержащих "TB", в одну ячейку, суммировать ячейки, содержащие "GB", в другую ячейку - разделить эту ячейку GB на 1000, я мог бы легко добавить эти две ячейки.

Есть ли формулы для суммирования столбца ячеек, содержащих текст? Я не хочу скачивать плагин.

Кто-то спросил это здесь: Как я могу сложить столбец с ячейками, содержащими текст?

И залп обеспечил формулу ниже, но я возвращаю ошибку "слишком мало аргументов", когда я использую это ...

=SUM(NUMBERVALUE(MID(A1:A6000,SEARCH("p",A1:A6000)-1)))

Может кто-нибудь помочь?

Answer 1

Используйте это, чтобы перебрать ячейки и проверить окончание, преобразовав все это в TB и сложив их вместе:

=SUMPRODUCT(LEFT(A1:A2,FIND(" ",A1:A2&" ")-1)/1000^(MATCH(RIGHT(A1:A2,2),{"TB","GB","MB","KB"},0)-1)) & " TB"

При выполнении итерации LEFT(A1:A2,FIND(" ",A1:A2&" ")-1) возвращает числовую часть.

MATCH(RIGHT(A1:A2,2),{"TB","GB","MB","KB"},0)-1 возвращает 0,1,2,3 зависимости от того, где находится текстовая часть массив {"TB","GB","MB","KB"}

1000^ возвращает 1,1000,1000000,100000000000 зависимости от возврата MATCH.

Answer 2

Запишите эту формулу в ячейку C2 и заполните:

=IF(RIGHT(A2,2)="TB",SUM(IF(ISNUMBER(FIND(RIGHT(A2,2),$A$2:$A$3)),VALUE(LEFT($A$2:$A$3,FIND(RIGHT(A2,2),$A$2:$A$3)-1)),0)),IF(RIGHT(A2,2)="GB",SUM(IF(ISNUMBER(FIND(RIGHT(A2,2),$A$2:$A$3)),VALUE(LEFT($A$2:$A$3,FIND(RIGHT(A2,2),$A$2:$A$3)-1)),0))/1024))

NB. Я разделил вторую часть формулы на 1024, чтобы преобразовать ГБ в ТБ.

Напишите эту формулу в ячейке C5:

=ROUND(C2+C3,2)&" TB"

Примечание. При необходимости измените ссылки на ячейки в формуле.

Answer 3

Я уверен, что, возможно, есть и другие решения для этого, но это работает:=IF(RIGHT(A2,2)="TB",LEFT(A2,SEARCH(" ",A2,1))*1024^4,IF(RIGHT(A2,2)="GB",LEFT(A2,SEARCH(" ",A2,1))*1024^3)) Если в столбце также есть "МБ" и "КБ", вы необходимо добавить соответствующие заявления IF для их учета. После того, как вы суммировали свой столбец, вы можете разделить его на 1024 ^ x, чтобы вернуться к нужным вам единицам (т.е. 1024 ^ 4 для ТБ, 1024 ^ 3 для ГБ и т.д.)

Answer 4

Я не думаю, что есть простая формула для этого. Требуется несколько шагов:

1. Отделяйте числовые от текстовых единиц
2. Преобразовать единицы в множители
3. Объедините каждое число с его множителем
4. Подвести итоги
5. Выберите подходящую единицу для использования

Числа 2 и 5 являются хитрыми, поскольку они требуют знания единиц. В моем примере ниже я работаю с КБ, МБ, ГБ и ТБ и предполагаю, что они означают множители базы-10 (пусть KiB и т.д. Означают базу-2). Но вы также можете захотеть B, b, kb, KB, PB, EB и т.д., И для каждого устройства, которое вы хотите обработать, вам нужен дополнительный IF (), чтобы превратить его в множитель.