植物、数量、価格をリストした表があります。どういうわけか、ほとんどの価格の先頭にスペースがあり、マークアップ乗数を適用しようとすると #VALUE エラーが発生する原因になっていると思われます。
トリム、トリム (クリーン)、コピー/貼り付け (特殊)、検索/置換を試しましたが、何も機能しません。最後には先頭にスペースのある数字が残っています。
何らかの書式設定が問題の原因になっていると考え、CSV にエクスポートしてから Excel に再度インポートしました。
一体何ですか? シートの一部をレビュー用に投稿する方法はありますか?
答え1
数字にはさまざまな種類のスペース (改行なしスペース) が含まれています。これらは通常、 ですCHAR(160)。Excel ではこれらを有効な文字と見なすため、TRIM でも CLEAN でもこれらを削除できません。ただし、これでは「数字」がテキスト文字列になり、乗算できなくなります。
そこで、SUBSTITUTE を使用してこれらの文字を削除し、念のため TRIM と CLEAN を追加します。
=TRIM(CLEAN(SUBSTITUTE(C2,CHAR(160),"")))*2.5
答え2
「改行不可スペースは処理されませんCLEAN()。これは、PDF のように、Web ページからデータを取得するときに非常に一般的であり、Web ページは視覚的なプレゼンテーションのみを対象としており、誰かがデータをスクレイピングしてさらに使用することは想定していません。しかし、特にデータの一般的な使用ではなく、限られたユーザーによる使用を想定しているデータ出力プログラムから大量に発生します。または、CSV 出力機能が作成された 28 年前には更新されませんでした。
文字通り、数字以外の部分が含まれない数値部分だけが必要なので、スプレッドシートでは問題を処理する方が簡単です。
値が負であることを示すインジケーターを保持する必要がある場合、それはより困難になります。または、これらのスペース以外の文字。または、私のように、出力が個別の数値である必要がある文字列を取得します。「90.02 45.55 .062」のような文字列ですが、抽出できるように保持されます。
ただし、必要なのは単一の文字列内の数字と、Excel によって数値とみなされる数字だけです。次の数式でそれを行います。
=VALUE(TEXTJOIN(,TRUE,IFERROR(MID(C1,SEQUENCE(1,LEN(C1)),1)*1,"")))
は、SEQUENCE()1 ずつ増加し、ターゲットの長さと同じ長さの一連の値を取得します。次に、それらを使用して、MID()ターゲット内の文字のマトリックスを出力する関数の開始位置を指定します。次に、マトリックス内の数字には機能する数学演算 ( *1) を実行しますが、数字 (0 ～ 9) 以外のものに対してはエラーが発生します。IFERROR()エラーが発生した場所 (ターゲットに数字がなかった場所) では、その文字列を "" で数字に変換します。TEXTJOIN()次に、そのマトリックスを単純な出力文字列に変換し、それらの "" 要素を無視します。これで、ターゲットに存在した数字だけの有用な文字列が得られます。これはテキストと見なされ、そのように表示されます。
現時点では、比較的新しいバージョンの Excel をお持ちの場合、この数式は役に立ちます。これは、Excel が、数値データのみを入力として要求または期待する関数に提示された場合、テキストとしてフォーマットされている数字の文字列 (フォーマットされているだけで、根本的に変更されているわけではありません) を、数字として認識するためです。したがって、「C1+1」でも、そのテキストを数値と見なし、エラーや 0+1=1 を返すのではなく、加算を実行します。しかし、すべての関数が出力をこのように扱うわけではありません。VLOOKUP()は、出力を何であれ (この場合はテキスト) 扱い、数値テーブルを検索しても一致するものが見つからないため、エラーが発生します。 そのため、私は上記をVALUE()終了時にラップして、結果が数値とみなされるようにし、他の誰かがそうしているように、結果を検索関数の入力として使用すると、機能するようになりました。
したがって、その関数を削除しても、マークアップ乗算は希望どおりに実行されます。
ただし、新しい関数がない場合SPILL、または特に Excel のかなり古いバージョンを使用している場合は、次の数式は基本的に Windows 版 Excel のすべてのバージョンで機能し、常に数値出力を生成します。
{=ROUND(NPV(-0.9,0,IFERROR(MID(L1,LEN(C1)-ROW(INDIRECT("C1:I"&LEN(C1)))+1,1)/100,"")),3)}
これはカスタマーサービス数式。通常の数式として使用すると、最後の 2 桁が削除されることがあります...出力は通貨としてフォーマットされます。(Excel は便利です...NPV()財務関数なので、通貨としてフォーマットする必要がありますよね?)
上記の式は「かなり昔のインターネット上のどこか」からのものです... 出典を明記できればよかったのですが、出典が思い出せません。親切なグルの方には申し訳ありません。
これを質問していただいて嬉しいです。なぜなら、私は多くの場所でそれを持っていて、どのようにSEQUENCE()助けることができるかを更新する必要があるからです。関数内の内部マトリックスから出力される便利な文字列への変更を置き換えることは言うまでもありませんTEXTJOIN()。それも現在利用可能になっているからです。すぐにメリットがあります。素晴らしいことです。