異なる単位付き文字列の集計

質問

表のある列に123mm、1.53m、100ft等のデータがランダムにコラムに入力されているとします。

ピボットテーブル等で各単位別に合計を表示させることは可能でしょうか？計10000mm、120290m、3280ftといったようにです。

単位を数値から切り離すというプロセスはしないという条件で考慮お願いいたします。

回答No.1

popuplt · ベストアンサー

>単位付き文字列の集計
>ピボットテーブル等で各単位別に合計を表示させることは可能でしょうか？
>単位を数値から切り離すというプロセスはしないという条件
123mmから単位[mm]を切り離しても[123]という文字列で、計算できるのは数値なので、ピボットテーブル等で合計は無理でしょうね。

計算式の１案ですが、集計したい単位を入力して
C1=m
C2=mm

配列数式
D1=SUM((RIGHT(範囲,LEN(C1))=C1)*(IF(NOT(ISERROR(VALUE(LEFT(範囲,LEN(範囲)-LEN(C1))))),VALUE(LEFT(範囲,LEN(範囲)-LEN(C1))),0)))
D2=[Copy]
範囲：計算する文字列（例：$A$1:$A$100)

後で見て判りにくい式を使うより、Excelの機能が使えるよう作業列で単位と数値を分けてから集計したほうが絶対いいと思います。

回答No.3

某HN クロメート(Chromate)（@CoalTar）

＞単位を数値から切り離すというプロセスはしない
マクロでも数式でも数値に変換できない文字列は計算できません

D2セル
=SUM(IF(ISNUMBER(SUBSTITUTE($A$2:$A$7,C2,"",1)*1),SUBSTITUTE($A$2:$A$7,C2,"",1)*1))
[Ctrl]+[Shift] ＋[Enter] で確定、配列数式です({}で囲まれる)

回答No.2

sige1701

回答が付かないようですので、お遊びで

　　A　　　B　　　　C　　　　D
1 123mm　　　　　　mm　　　　498
2 1.53m　　　　　　ft　　　　303
3 100ft　　　　　　m　　　　 4.62
4 124mm
5 1.54m
6 101ft
7 125mm
8 1.55m
9 102ft
10 126mm

C列に　単位入力
D1=SUM(IF(SUBSTITUTE($A$1:$A$10,C1,"")<>$A$1:$A$10,SUBSTITUTE($A$1:$A$10,C1,"")*1))
配列数式です　Ctrl+Shift+Enter

下にコピー

D3=SUM(IF((SUBSTITUTE($A$1:$A$10,C3,"")<>$A$1:$A$10)*ISNUMBER(LEFT(RIGHT($A$1:$A$10,2))*1),SUBSTITUTE($A$1:$A$10,C3,"")*1))
配列数式です　Ctrl+Shift+Enter

D3の単位mがD1の単位mmと文字が重複の場合考慮が必要になります

>単位を数値から切り離すというプロセスはしないという条件
単位を数値から切り離す方が簡単と思いますよ