この SUMPRODUCT と MID の数式は具体的に何を行っているのでしょうか?

Question

H23Excel スプレッドシートに、、w67などのテキストエントリがあり、これらのP45数値部分、つまり、、、を合計したいと考えてい23ます67。45

私はその式を持っている

=SUMPRODUCT((0&MID($H2:$S2,1+ISERROR(LEFT($H2:$S2)+0),10))+0)

これは目的を果たしますが、何をするのかよくわかりません。完全に理解しないまま使用するのは嫌です。誰か式を説明してもらえませんか?

Answer 1

混乱を避けるために、単一セルの数式から始めます。

=SUMPRODUCT((0&MID($H2,1+ISERROR(LEFT($H2)+0),10))+0)

1. サンプルデータはすべて文字で始まりますが、ISERROR(LEFT($H2:$S2)+0)最初の文字として数字または文字を探し、1手紙の場合はLEFT関数デフォルトでは左端の1文字となり、TRUEは1そしてFALSEは0左端の文字がアルファベットの場合、数学演算で使用するとエラーが発生します（例：<キャラクター>+ 0)なので1に追加されます1の中に開始番号パラメータのMID機能そしてそれは2エラーが発生しない場合は0に追加されます1の中に開始番号パラメータ。
2. の10MID 関数が取得しようとする文字の最大数です。これより少ない文字数でも取得されます。
3. 数字が全くない場合は、SUMPRODUCT関数長さがゼロの文字列（基本的には空白）を数値に変換しようとすると、処理が遅くなります。これを防ぐために、0MID 操作から返される結果の前に付けられます。
4. MID演算の結果（および追加された0）は文字列であり、数値ではありません。すべてがうまくいけば、結果はすべて数字になるはずです。すべて数字の文字列は、数値を変えずに真の数値に変換できます。1または追加する0例えば、0に「123」本当の数字が得られます123。

内部操作はこれですべてです。これを複数の列に拡張し、SUMPRODUCT関数ラッパーとして。

=SUMPRODUCT((0&MID($H2:$S2,1+ISERROR(LEFT($H2:$S2)+0),10))+0)

列は絶対としてロックされ、行は相対のままなので、後続の行を下方向に入力します。