メインコンテンツへスキップ

Excelでセルをテキストと数値で合計するにはどうすればよいですか?

たとえば、数値とテキストの文字列を含む値のリストがありますが、同じテキストに基づいて数値のみを合計したいので、次のスクリーンショットを見てください。 通常、リスト内の値をテキスト文字列と直接合計することはできません。ここでは、このタスクを処理するためのいくつかの式を紹介します。


数式を使用して、特定のテキストに基づいてテキストと数値を含むセルを合計します

配列数式を使用して、セル内の対応するテキスト文字列に基づいて数値を合計できます。次のようにしてください。

1. まず、列セルの相対数を合計するテキスト文字列を書き留めます。

2。 次に、空白のセルに以下の数式を入力します。この例では、セルE2に数式を入力して、を押します。 Ctrl + Shift + Enter キーを一緒に使用すると、特定のテキストに基づいてリストから合計値が返されます。 スクリーンショットを参照してください:

=SUM(IF(ISNUMBER(FIND(D2,$B$2:$B$9)),VALUE(LEFT($B$2:$B$9,FIND(D2,$B$2:$B$9)-1)),0))

Note:上記の式では、 D2 使用したい基準であり、 B2:B9 合計するセル範囲を示します。

3。 次に、数式セルを選択し、この数式を適用するセルまで塗りつぶしハンドルを下にドラッグして、結果を取得します。


便利な機能を備えた特定のテキストに基づいてテキストと数字を含むセルを合計する

上記の式がわかりにくい場合は、 Kutools for Excel 便利なツールを提供します- 同じテキストに基づく合計、この機能を使用すると、数式を覚えていなくても、数値やテキストが混在するセル内の合計結果を取得できます。

注:これらを適用するには 同じテキストに基づく合計 機能、まず、ダウンロードする必要があります Kutools for Excel、次に機能をすばやく簡単に適用します。

インストールした後 Kutools for Excel、次のようにしてください。

1。 結果を出力するセルをクリックして選択します。スクリーンショットを参照してください。

ヒント:循環参照を防ぐために、この数式をワークシートの最初の行に挿入しないでください。

2。 そして、 クツール > フォーミュラヘルパー > フォーミュラヘルパー、スクリーンショットを参照してください:

3。 の中に 数式ヘルパー ダイアログボックスで、次の操作を行ってください。

  • 選択 統計的 数式タイプ ドロップダウンリスト;
  • 式を選択してください リストボックスをクリックして選択します 同じテキストに基づく合計 オプション;
  • そして、 引数入力 セクションで、合計するテキストと数値を含むセルの範囲を選択します。 レンジ テキストボックスで、値を合計するテキストセルを選択します。 テキスト テキストボックス。
ヒント: の中に テキスト テキストボックスでは、数式を正しくドラッグするために、デフォルトの絶対セル参照を相対セル参照に変更する必要があります。

4。 次に、をクリックします Ok ボタンをクリックすると、最初の結果が計算され、数式を他のセルに塗りつぶすための塗りつぶしハンドルをドラッグします。スクリーンショットを参照してください。


ユーザー定義関数を使用して、テキストと数値を含むセルを合計し、テキストを無視します

次のデータがテキストと数値で混合されていて、各セルの数値のみを抽出して合計したい場合、Excelでこれをすばやく完了するにはどうすればよいでしょうか。

この問題に対処するために、 ユーザー定義関数、次のようにしてください。

1。 を押し続けます Alt + F11 キーを押して Microsoft Visual Basic forApplicationsウィンドウ.

2に設定します。 OK をクリックします。 インセット > モジュール、次のコードをに貼り付けます モジュールウィンドウ.

VBAコード:数値とテキストを含むセル内の数値のみを合計します

Function SumNumbers(rngS As Range, Optional strDelim As String = " ") As Double
'Updateby Extendoffice
    Dim xNums As Variant, lngNum As Long
    xNums = Split(rngS, strDelim)
    For lngNum = LBound(xNums) To UBound(xNums) Step 1
        SumNumbers = SumNumbers + Val(xNums(lngNum))
    Next lngNum
End Function

3。 次に、このコードを保存して閉じ、ワークシートに戻って、この数式を入力します = SumNumbers(A2) (A2 数値を合計するセルを示します)、数式を入力するセルまで塗りつぶしハンドルをドラッグすると、各セルの数値のみが加算されます。スクリーンショットを参照してください。


より相対的な記事:

  • 重複する行を組み合わせてExcelで値を合計する
  • Excelでは、次のスクリーンショットに示すように、重複するエントリを含むデータの範囲があり、重複するデータを結合して対応する値を別の列に合計する場合、常にこの問題が発生する可能性があります。 この問題をどのように解決できますか?
  • XNUMXつの列を乗算してから、Excelで合計します
  • Excelでは、ほとんどの人がXNUMXつの列を乗算してから合計するのに苦労するかもしれません。もちろん、各項目を乗算してから合計することもできますが、数百または数千の行を計算する必要がある場合、これは面倒です。 Excelには、強力な関数SUMPRODUCTがあります。これを使用すると、XNUMXつの列をすばやく乗算して、それらを合計できます。 この記事では、この機能の適用方法について説明します。
  • Excelの基準に基づいて上位3つまたはNの値を合計する
  • 通常、単純な数式を使用してセルの範囲から上位n個の値を合計できますが、いくつかの基準に基づいて上位n個の値を合計しようとしたことがあります。 この記事では、Excelで特定の基準を使用して上位n個の値を合計するための数式をいくつか紹介します。
  • Excelの基準に基づいて一意の値を合計する
  • たとえば、次のスクリーンショットに示すように、[名前]列に基づいて[注文]列の一意の値のみを合計するために、[名前]列と[注文]列を含むデータの範囲があります。 このタスクをExcelですばやく簡単に解決するにはどうすればよいですか?
  • Excelのフォントの色に基づいてセルをカウントまたは合計する
  • Excelでセルの数を取得したり、特定のフォントの色を含むすべてのセルを合計したりするにはどうすればよいですか? たとえば、次のスクリーンショットに示すように、ワークシートにさまざまなデータがあります。次に、赤、青、黒のフォントの色を持つセルをそれぞれカウントまたは合計します。 通常、このタスクに直接対処する方法はありません。ここでは、このジョブを解決するためのいくつかのトリックについて説明します。

最高のオフィス生産性向上ツール

人気の機能: 重複を検索、強調表示、または識別する   |  空白行を削除する   |  データを失わずに列またはセルを結合する   |   数式なしのラウンド ...
スーパールックアップ: 複数の基準の VLookup    複数の値の VLookup  |   複数のシートにわたる VLookup   |   ファジールックアップ ....
詳細ドロップダウン リスト: ドロップダウンリストを素早く作成する   |  依存関係のドロップダウン リスト   |  複数選択のドロップダウンリスト ....
列マネージャー: 特定の数の列を追加する  |  列の移動  |  Toggle 非表示列の表示ステータス  |  範囲と列の比較 ...
注目の機能: グリッドフォーカス   |  デザインビュー   |   ビッグフォーミュラバー    ワークブックとシートマネージャー   |  リソースライブラリ (自動テキスト)   |  日付ピッカー   |  ワークシートを組み合わせる   |  セルの暗号化/復号化    リストごとにメールを送信する   |  スーパーフィルター   |   特殊フィルター (太字/斜体/取り消し線をフィルター...) ...
上位 15 のツールセット12 テキスト 工具 (テキストを追加, 文字を削除する、...)   |   50+ チャート 種類 (ガントチャート、...)   |   40+ 実用的 (誕生日に基づいて年齢を計算する、...)   |   19 挿入 工具 (QRコードを挿入, パスから画像を挿入、...)   |   12 変換 工具 (数字から言葉へ, 通貨の換算、...)   |   7 マージ&スプリット 工具 (高度な結合行, 分割セル、...)   |   ... もっと

Kutools for Excel で Excel スキルを強化し、これまでにない効率を体験してください。 Kutools for Excelは、生産性を向上させ、時間を節約するための300以上の高度な機能を提供します。  最も必要な機能を入手するにはここをクリックしてください...

kteタブ201905


Officeタブは、タブ付きのインターフェイスをOfficeにもたらし、作​​業をはるかに簡単にします

  • Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
  • 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
  • 生産性を 50% 向上させ、毎日何百回もマウス クリックを減らすことができます!
Comments (50)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi,
I want to have a formula that count text. But what I want is that he count a criterium/ text like as 0,5 and not as 1. Like for example I have one column with T, TO and TM. T I want to count as 1 and TO/ TM as 0,50. I have tried several things, but can not solve it. Hopefully can someone help me out?
This comment was minimized by the moderator on the site
Hi, I am trying to check approximately 8 different types of leave. I am using conditional formatting to colour code letters and then calculate as numbers. It works well, I like it. I did not factor in a half day.

Can anyone tell me how to account for a half day, and still have Excel recognize the Alpha symbol.
This comment was minimized by the moderator on the site
Hi, how do I sum something like the following:
Column A
Row 1 2D
Row 2 2N
Row 3 1D+2N

I have no issues summing the Ds but when I try summing the Ns I would encounter a problem. The string 1D+2N is the culprit, so how do I go about this?
Any help is very much appreciated.

Thank you.
This comment was minimized by the moderator on the site
I must be doing something incorrect. When I try to use the SumNumbers() VB code nothing happens? excel does not recognize it as code? I am using a macro enabled document. Image attached.
https://ibb.co/6vRtD6N

[img]https://ibb.co/6vRtD6N[/img]
This comment was minimized by the moderator on the site
Hi, Michael,
I have tested the code based on the data you provied, it can work well.
This is User Defined Function, so, you needn't run the code, you just need to insert the formula SumNumbers(A1), and press Enter key to get the result. See screenshot:
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-sum-text-number.png

Please try, hope it can help you!
This comment was minimized by the moderator on the site
How to do this, but with decimal places? When using decimals, it returns 0...
This comment was minimized by the moderator on the site
HOW TO MULTIPLY COLUM 1 , 2 PCS , COLOUM 2 RATE 1 USD TOTAL AMOUNT ?
This comment was minimized by the moderator on the site
kindly reply to "I want to use this formula to sum only numbers in all columns like =SumNumbers(A1:G1) but it gives error VALUE. how it can be fixed?"
This comment was minimized by the moderator on the site
You need to use the VBA for that to work. the VBA code defines the SumNumbers as a function.
This comment was minimized by the moderator on the site
I have used VBA for that but it doesn't work. Can you plz take a look?
This comment was minimized by the moderator on the site
I have used VBA for that but it does not work. Can you take a look plz?
This comment was minimized by the moderator on the site
I want to use this formula to sum only numbers in all columns like =SumNumbers(A1:G1) but it gives error VALUE. how it can be fixed?
This comment was minimized by the moderator on the site
I am using this in excel 2016 but it doesn't seem to be working is there a work through? i keep getting a "
#name?" error
This comment was minimized by the moderator on the site
Hi,
I have a question. How to get the sum or the average of the column that has text inside that column. Please see attachment. Your answer would be greatly appreciated.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations