エクセルで一つのセル内の特定の数字のみ足す方法

Question

添付画像のデータはシステムより抽出したものですが、前８桁が日付、後ろ３桁が出荷枚数となっています。このデータを利用して、日付毎の出荷枚数合計を出したいのですが、どの様な関数がありますでしょうか？
後ろ３桁は、数字が２桁までの場合はハイフンが入ります。３桁以上の数字はありません。
よろしくお願いいたします。

goomania · Accepted Answer

既に幾つかの回答が投稿されていますが、ご質問者のご希望である
>日付毎の出荷枚数合計を出したい
について具体的手順を示した回答がないように思います。

ご質問者のいう、「日付毎の出荷枚数合計を出したい」という意味が
システムから抽出したデータの隣(あるいは同じ行の適当な列)に表示したいということなら、添付画像のE2セルの数式

=SUMPRODUCT((LEFT($A$2:$A$7,8)=LEFT(A2,8))*ABS(REPLACE($A$2:$A$7,1,8,"")))

を日付毎の合計を表示したい列の見出し行の次行(つまり2行目)に記述して下方向へコピーすれば日付毎の出荷枚数合計が表示されます。
※上記数式の中の「$A$2:$A$7」はシステムから抽出したデータの範囲です。実際のデータ範囲に併せて調整願います。

この場合、添付画像のB列からD列は必ずしも必要でないことになります。ただし、システムから抽出したデータの日付が同じ場合は全て同じ数字が表示されます。

なお、上記数式はシステムから抽出したデータの"-"(ハイフン)の文字コードが"-"(マイナス)と同じであるという場合にのみ有効です。

システム抽出値のハイフンとマイナスの文字コードが異なる場合は、少し長い数式ですが、吹き出しの中に表示した、

=SUMPRODUCT((LEFT($A$2:$A$7,8)=LEFT(A4,8))*SUBSTITUTE(REPLACE($A$2:$A$7,1,8,""),"-",""))

を使用してください。

また、添付画像のG列、H列のように別表を用意して日付毎の合計を表示したいという場合は、システム抽出値を日付と出荷枚数に分離してから集計する方法が一般的だと考えられるので、B2セルに
=LEFT(A2,8)*1
を入れて下方向にコピー(表示形式は「日付」)。
C2セルに
=SUBSTITUTE(REPLACE(A2,1,8,""),"-","")*1
を入れて下方向へコピー。
※前述のように、システム抽出値のハイフンとマイナスの文字コードが同じ場合は、上記数式の代わりにD2セルの数式
=ABS(REPLACE(A2,1,8,""))
という数式が使えるので数式が短くで済みます。

別表ですからG列に日付を表示させる訳ですが、G2セルに

=IFERROR(AGGREGATE(15,6,$B$2:$B$7/(1-COUNTIF($G$1:G1,$B$2:$B$7)),ROW(A1)),"")

という数式を記述して下方向へコピーしています。
これで重複しない日付がG列に表示されます。
※上記数式の中の「$B$2:$B$7」はシステムから抽出したデータの範囲の行指定です。実際のデータ範囲に併せて調整願います。

さらにH2に

=SUMIF($B$2:$B$7,G2,$C$2:$C$7)

という数式を記述して下方向へコピーしています。
※上記数式の中の「$B$2:$B$7」「$C$2:$C$7」はシステムから抽出したデータの範囲の行指定です。実際のデータ範囲に併せて調整願います。
また、G列の日付が表示されていない部分へコピーすると0が表示されます。

これで、別表による日別出荷枚数の表示が完了します。

goomania · Answer

No.5です。

大変失礼しました。前回回答中のG2に記述する数式

=IFERROR(AGGREGATE(15,6,$B$2:$B$7/(1-COUNTIF($G$1:G1,$B$2:$B$7)),ROW(A1)),"")

に誤りがありました。正しくは

=IFERROR(AGGREGATE(15,6,$B$2:$B$7/(1-COUNTIF($G$1:G1,$B$2:$B$7)),ROW($A$1)),"")

となります。

なお、絶対参照に修正することで、値が固定されるため、

=IFERROR(AGGREGATE(15,6,$B$2:$B$7/(1-COUNTIF($G$1:G1,$B$2:$B$7)),1),"")

という数式でもOKです。

enunokokoro · Answer

日付ごとですよね。
この回答にある添付画像でもよいのなら、以下の設定で。

システムのデータを「元データ」としてA列にあるとします。
B1には「日付」を、C1には「出荷枚数」を配置。
B2セルには日付を抽出するために
　=LEFT(A2,8)*1
C2セルには、出荷枚数を数値地して抽出するために
　=ABS(MID(A2,9,3))
としてオートフィルで元データの横に表示します。

リボンの[データ]タブから[小計]を選択。
https://net-business888.com/excel-higoto-goukei/
[集計の設定]ダイアログで[グループの基準]を[日付]にして、
[集計の方法]を[合計]、[集計するフィールド]を[出荷枚数]に
して[ OK ]します。
これで日付ごとの集計ができるようになります。

fujillin · Answer

こんにちは

他の方の回答にもありますが、一旦、対象としたい数値部分だけを抜き出してから、合計するのが簡単です。
＞前８桁が日付、後ろ３桁が出荷枚数となっています。
後ろ3桁と言っても、場合分けが面倒そうなので、日付部分が必ず8桁としても良いのなら・・
（例示の日付が12月だけなので、01月のようにゼロ埋めされるのか不明）

「9文字目から後ろの文字でハイフンを除いたもの」

と考えてもよければ一律で処理ができます。
例えば、A列に元のデータが並んでいるとして、B1セルに
　=SUBSTITUTE(MID(A1,9,10),"-","")*1
の式を入力して、下方にフィルコピーすれば、目的の数値を得ることができます。
後は、合計すれば良いですね。

直接求めたいのなら、上記を応用して、
　=SUMPRODUCT(("0"&SUBSTITUTE(MID(A1:A30,9,9),"-",""))*1)
でも、求められると思います。

※ 上式では対象セル範囲を「A1:A30」としてありますが、この範囲内にご提示のフォーマットの文字列以外で9文字以上の文字列（＝おかしなデータ）が混在している場合にはエラーになります。
（空白セルが含まれるのは問題ありません。）

ほい3 · Answer

Excel関数で文字列を自在に操るワザを覚えて、
https://tenshoku.mynavi.jp/knowhow/shortcut/06/

「もしも、住所のセルの左から4番目が「県」ならば、左から4文字、それ以外は左から3文字取り出します。」
＝IF(MID(N2,4,1)=”県”,LEFT(N2,4),LEFT(N2,3))

これを参考に
=IF(MID(A2,9,1)="-",MID(A2,10,2),MID(A2,9,3))
当たりで行けそうです。

zircon3 · Answer

人が見ると文字列操作を年月日＋数値ですが、この場合、Excel上は数値ではなくただの文字列ですので文字列操作をするしかないでしょう。
例えば年月日が必ず間違いなくYY/MM/DDの8文字なら・・・

(1) 9文字目を参照し、これが-なら10文字目から文字列端までの文字を取り出し、これが1文字または2文字の数字あることを確認して数値変換する。
(2) 9文字目が1～9の数字なら9文字目から文字列端までの文字を取り出し、これが3文字の数字あることを確認して数値変換する。
(3) (1)、(2)で抽出した文字列が数字以外の文字を含んだ場合または、(1)でも(2)でもない文字列であった場合はエラーとする。

具体的にどういう関数で可能かはGoogleなどで「VBA　文字列長」、「VBA　文字列」、「VBA　数値変換」といったキーワードで検索するとVBAの解説をしているWebサイト等の該当ページがみつかるはずです。
ExcelのHELPも役立つかと思います。

参考まで。

エクセルで一つのセル内の特定の数字のみ足す方法

既に幾つかの回答が投稿されていますが、ご質問者のご希望である

No.5です。

日付ごとですよね。

こんにちは

Excel関数で文字列を自在に操るワザを覚えて、

人が見ると文字列操作を年月日＋数値ですが、この場合、Excel上は数値ではなくただの文字列ですので文字列操作をするしかないでしょう。

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング