エクセルで文字の識別と文字を飛ばして足し算したい。

質問

以下のような表を作り、(1)、(2)の機能をつけたいのですがうまくいきません。
計算式の分かる方がいれば教えてください。

　　　A　　 　B　　 C　 　D
1　リンゴ　 90円　1個　 90円
2　バナナ　20円　2個　 40円
3　みかん　30円　3個　 90円
4　バナナ　20円　1個　 20円
5　みかん　30円　2個　 60円
6　　合計　　－　　9個　300円

(1)この表のAの列に「リンゴ」「みかん」「バナナ」を入力すると自動的にB列に価格が表示される。

(2)C列に「未定」などの文字を入れるとオートSUMが機能しなくなるので、これをC列に文字を入れると文字を入れたセルを飛ばして計算する。

よろしくお願いします。

回答No.3

zenjee · ベストアンサー

基本的な考え方は＃No.1 No.2 と同じですが、ご質問の文脈からもしかしたらVLOOKUPやISNUMBER関数などの具体的使用方法や、式構成の原理などをご存知ではないのではないかと存じ、大変失礼ですが能率的な作業方法も併せて説明させていただきます。

Sheet１が計算表だとするとまずSheet2に、A列が品名、B列がそれに対応する単価を入力した単価表を作ります。
A,B列のすべてを選択し、挿入→名前→定義をクリックします。名前の定義ダイアログボックスが現れますので、例えば「単価表」と名前を付け、下の参照範囲が「=Sheet2!$A:$B」となっていることを確認してＯＫをクリックします。

次に、作業能率を高めるため、Ａ列のみを選択し、同様に挿入→名前→定義をクリックします。名前の定義ダイアログボックスが現れますので、例えば「品名」と名前を付け、下の参照範囲が「=Sheet2!$A:$A」となっていることを確認してＯＫをクリックします。

次はsheet1です。1行目は見出し行として、A1～D1に品名、単価、数量、合計と入力します。
まず品名列の入力を容易にするため、A列全体を選択し、データ→入力規則をクリックします。データの入力規則というダイアログボックスが現れますので、入力値の種類（下の空欄の）右の▼をクリックし、「リスト」を選びます。
次に元の値欄に　
=品名
と入力するか、又は上のツールバーに「=ab」があればそれをクリックして、「品名」を選択、なければ、挿入→名前→引用をクリックして「品名」を選択します。

次にB2に入力する式です。
=IF(ISERROR(MATCH(A2,品名,0)),"",VLOOKUP(A2,単価表,2,FALSE))
なお、これは単価表（Sheet2）の品名にないものをA列に入力したときエラー値が出るので、それを防ぐために
IF(ISERROR(MATCH(A2,名前,0)),"",
を書き加えたもので、単価表（Sheet2）の品名にないものを入力することが絶対にない場合は、単に
=VLOOKUP(A2,単価表,2,FALSE)
だけで構いません。

次にD2に入力する式です。
=IF(B2="","",IF(ISNUMBER(C2),B2*C2,""))
と入力します。
これもB列が空欄になる場合があり得ることに備えて、 IF(B2="","",　を書き加えたもので、それがなければ
=IF(ISNUMBER(C2),B2*C2,"")
だけで構いません。

以上が終われば、B2とD2をそれぞれ下にプルダウンコピーします。
これで完成ですが、数量以外にC列に入力する文字列（例えば、未定、品切れなど）も前記の品名のようによく使う文字列を縦1列に並べた表を別に作っておき、名前を付け、品名同様、データ→入力規則 でプルダウン入力出来るようにしておくと便利でしょう。

なお、最後に一つだけ。No.2 imogasi さんが指摘されたように単位（個、円など）は表示形式で設定した方がよいと思います。

お礼

何の面識もない私にこんなに丁寧にご指導下さいましてありがとうございます。
これだけ書くのにきっとたくさんの時間を割いて下さったのだろうと想像し感謝の気持ちでいっぱいです。
本当にありがとうございます。

回答No.4

mshr1962

(1)A,B列だけの単価表を別に用意すればOK
その範囲がSheet2のA1:B100として
=IF(COUNTIF(Sheet2!$A$1:$A$100,A1),VLOOKUP(A1,Sheet2!$A$1:$B$100,2,FALSE),"")

(2)確かにオートサムは文字列の範囲の直前までしか範囲に自動設定しませんが
SUM関数自体は文字列を0として扱うので、範囲指定さえ手動ですれば大丈夫です。

お礼

オートサムとSUM関数ではそういう違いがあるのを知りませんでした、早速試して見ます。
　ご回答ありがとうございます。

回答No.2

imogasi

（１）Sheet２のA1:B5に
リンゴ 90
バナナ 20
みかん 30
バナナ 20
みかん 30
を作る。[
「円」は表示形式で円を添える。
Sheet1のB1に式
=IF(A1="","",VLOOKUP(A1,Sheet2!$A$1:$B$5,2,FALSE))
と入れてデータ入力行最下行まで複写する。
（２）はIF文でD1に
=IF(ISNUMBER(C1),B1*C1,"")
と入れて下方向に複写。
「個」は表示形式で設定して出すこと。

お礼

早速のご回答とアドバイスをありがとうございます。
私が1日悩んでも解決しなかった事を簡単に回答できるあなたはすごいと思います、きっとたくさん勉強されたのですね、私もimogasiさんのようにまではなれないにしてももっと勉強しようと思います。

回答No.1

134

(1)品名と単価の一覧表を作って、vlookup文などを使うと効果的です。

(2)　D1セルに
　=if(isnumber(c1)=true,b1*c1,"")
という関数を入力し、下にコピーする

お礼

早速のご回答ありがとうございます。
vlookupってなんだっけ、と思うくらい勉強不足の私でした。もう少ししっかり勉強してみようと思います。
（1）（2）ともに適切なご回答をありがとうございます。