エクセルの関数、式教えてください。

質問

難しくてどうしてもわかりません。
無知で見よう見まねでやっております。

売上管理シートのE列 商品番号を入力すると
その商品番号の 入出庫表シートJ列にある価格を
売上管理シートのT列に自動で入力するには
どうしたらよいでしょうか???

売上管理シートのT列に色々やってみたのですが、
できません。どうしてもわかりません((+_+))

分かる方、教えてください!よろしくお願いいたします。

≪売上管理シート≫

　(E列)　　　　　　　・・・・省略　　　　　　(T列)
商品番号　　　品名　　　販売価格　　仕入価格
1111111　　　　スイカ　　　500円　　　　200円
22222　　　　　メロン　　　　1000円 　　 600円

≪入出庫表シート≫

　(D列)　　　　　・・・・省略　　　　　(J列)
商品コード　　　品名　　　　　　仕入価格　　　　　
1111111　　　　　スイカ　　　　　　500円　　　　　　
5555555 　　　　 メロン　　　　　　　300円

回答No.4

kagakusuki · ベストアンサー

回答No.2です。

>全部やっててみたものの、うまくいかず・・・

　申し訳御座いません。

=IF(COUNTIF(入出庫表シート!$J:$J,$E2),VLOOKUP($E2,入出庫表シート!$D:$J,7,FALSE),"")

の方は間違いで、正しくは

=IF(COUNTIF(入出庫表シート!$D:$D,$E2),VLOOKUP($E2,入出庫表シート!$D:$J,7,FALSE),"")

になります。
　尚、

=IF(ISERROR(1/(VLOOKUP($E2,入出庫表シート!$D:$J,7,FALSE)<>"")),"",VLOOKUP($E2,入出庫表シート!$D:$J,7,FALSE))

の方は間違いでは御座いません。

　それから、

>売上シートTに出るものと出ない場合があります。
>在庫シートに緑の▼エラーが出ているセルの商品コードのみ反映されるようです。
>書式は全部文字列になってるのですがまばらでエラーのセルとエラーなしのセルになってしまってます。

との事ですが、御質問文の中には「在庫シート」というシート名は登場していませんが、「在庫シート」とは一体どの様なシートの事で、御質問文にある「売上管理シート」や「入出庫表シート」のデータと、どの様に関わって来るのでしょうか？
　それとも、「在庫シート」というのは「入出庫表シート」と書こうとして間違っただけなのでしょうか？
　取り敢えず、「在庫シート」というのは「入出庫表シート」の事であるものと考える事にして回答致します。

　売上シートのT列に仕入価格が表示されるものとされないものが出て来るという事は、おそらく入出庫表シートに商品コードを入力した際に、セルの書式を文字列とする前に入力したコードと、書式を文字列とした後に入力したコードという、２種類のコードがあるからではないかと思います。
　例えば「1111111」の様な数字のみから成るコードの場合、セルの書式を文字列とする前に入力しますと、当然の事ながら、そのコードは数値データとして扱われます。
　そして、数値データとして入力された後から、セルの書式を文字列としたとしても、セルに入力されているデータは文字列データに変換される事は無く、数値データのままとなります。（セル上に左寄せで表示されていても数値なのです）
　一方、セルの書式を文字列にした後で、同じく「1111111」の様な数字のみから成るコードを入力した場合には、それは数値ではなく文字列データとして扱われます。
　或いは、入出庫表シートのD列に入力されている商品コードの値が、もしも、直接に手入力されたものではなく、何らかの関数によって、別のセルに入力されているデータを反映させたものである場合には、入出庫表シートのD列に入力されている関数内で参照している参照先のセルに入力されているデータが、字列データとなっているものと数値データとなっているものが混在しているという事も考えられます。（関数によっては、元々の参照先のデータが数値データであっても、文字列データに変換してしまう関数や、逆に、数字のみから成る文字列データを数値に変換してしまう様な関数もありますので注意して下さい）
　見かけは同じ「1111111」ではあっても、コンピュータは文字列の「1111111」と数値の「1111111」は別のデータであると判断します。
　ですから、入出庫表シートのD列に文字列データと数値データが混在している場合において、売上管理シートのE列に入力されているデータが（データを入力した際に、セルの書式設定が文字列となっていた事により）文字列データである場合には、入出庫表シートに存在している数値データのコードが反映される事はありませんし、逆に、売上管理シートのE列に入力されているデータが（データを入力した際に、セルの書式設定が文字列となってはいなかった事により）数値データが入力されていた場合には、入出庫表シートに存在している文字列データのコードが反映される事はありません。
　ですから、まずは、入出庫表シートのD列と売上管理シートのE列に入力するデータの形式を、数値データにするのか、それとも文字列データにするのか、どちらか一方に統一する必要があります。
　どちらにすれば良いのかは、商品コードの形式によって異なり、商品コードの中に数字のみから成り立っていて、尚且つ、文字列の先頭部分が「0」や「+」及び「-0」となっている様なものがある場合には、セルの書式設定を（入出庫表シートのD列と売上管理シートのE列の双方を共に）文字列とした方が良いと思いますし、文字列の先頭部分が「0」や「+」及び「-0」となっているコードが存在しない場合には、セルの書式設定を（入出庫表シートのD列と売上管理シートのE列の双方を共に）G/標準とした方が良いと思います。
　そのようにして、入出庫表シートのD列と売上管理シートのE列の双方においてセルの書式設定を統一し終えてから、再度、全ての商品コードを入力し直します。
　すると、入力し直した後の商品コードのデータが、全てセルの書式に応じたデータ形式に書き換えられますので、数値データと文字列データの違いによってデータが反映されなくなる事を避ける事が出来ます。
　尚、同じデータを入力し直す際には、データが入力済みのセルをダブルクリックしてからEnterキーを押すだけでOKです。
　又、入力済みのセルの数があまりに多過ぎて、１つ１つダブルクリックして行く事が困難である場合には、Excelの置換機能を利用して、入力済みのセル範囲を全て選択してから、
「1」を「1」に置換、
「2」を「2」に置換、
「3」を「3」に置換、
「4」を「4」に置換、
「5」を「5」に置換、
「6」を「6」に置換、
「7」を「7」に置換、
「8」を「8」に置換、
「9」を「9」に置換、
「0」を「0」に置換、
という操作を行いますと、複数のセルのデータを一括して入力し直す事が出来ます。

【参考URL】
　Word や Excel の "置換" 機能で簡単に変換したい
　　http://support.microsoft.com/kb/906326/ja

お礼

在庫シート」というのは「入出庫表シート」と書こうとして間違っただけ

そうです!!!

紛らわしくなり申し訳ございません
でした。

▼エラーの事、とても参考になりました。
補足くださった内容がその通りでした!!

>どちらか一方に統一する必要があります
ありがとうございます!!!

エラーに関しても本当に分かりやすい
ご説明いただいきお礼申し上げます。

Excelの置換機能もやってみます。

お二方とも、こんなに丁寧に教えて下さって
本当にありがとうございます。

補足

ありがとうございます!!!
めちゃくちゃ親切に本当に本当に
ありがとうございます。

URLもありがとうございます。

今からやってみます(T_T)

本当に感謝申し上げます!!

回答No.5

bunjii

>売上シートにも在庫シートにも　緑の▼エラーがすごく沢山、文字列ではなく、数値や標準にするとやはりT列が反映されなくなってしまいました。
アドバイスを聞き取る努力が必要です。
自分の言いたいことを言うだけでは解決になりません。

>このセルは文字列で保存されていますや、テキスト形式もしくはアポストロフィーになってます。と
商品番号が文字列と数値の混在になっているため検索できないものと検索できるものが発生しているようです。
取り敢えず、VLOOKUP関数で拾い出せる式を次のように提言します。

T3=IFERROR(VLOOKUP(E3,在庫表!D$2:J$10000,7,FALSE),IFERROR(VLOOKUP(E3&"",在庫表!D$2:J$10000,7,FALSE),""))

この式は在庫表シートの商品番号が数値であることを先に検索し、見つからないときは文字列の商品番号を検索します。
何れでも見つからないときは空欄になります。
条件としては売上管理シートの商品番号を数値で入力してください。

お礼

ひとまず、最初にしえていただいた式で完成いたしました。
文字列と数値の混在のためだとはまったく知らず
6年もこのシート使ってました(笑)

やっと完成しました。

無知の私にご親切に教えてくださって
本当にありがとうございました。

また、お願いします!(^^)!

回答No.3

bunjii

>在庫シートに緑の▼エラーが出ているセルの商品コードのみ反映されるようです。
元になる入出庫表シートに不備があるのではないでしょうか？
元データの不備をチェックする機能は含まれていません。

>書式は全部文字列になってるのですがまばらでエラーのセルとエラーなしのセルになってしまってます。
仕入価格が文字列であれば数値に置き換えないと計算できません。
元データの入出庫シートのデータを修正する必要があるでしょう。

>仕入価格は反映されるものの、隣のU列利益　=G6-T6+H6　V列の税項目がE列の商品番号を入力しないと、全部#VALUEになってしまい商品番号入力すると金額が反映します。
後出しの条件は考慮されていませんので動作のチェックは別に考えてください。
＃VALUEについては数でない値を計算対象にしたときの結果と思われます。

お礼

いろいろ勉強になりました。
エラーの件、無事解決いたしました
大変感謝しております。

=G6-T6+H6　V列◦#VALUE! エラーも、
>数でない値を計算対象にしたとき
と教えてくださったおかげで

なんとか検索して
=IF(ISERROR(G6-T6+H6),"",G6-T6+H6)

でエラーを回避できました!!!
応用して消費税部分の#VALUE! もついでにできました。
ありがとうございました。

補足

ご回答いただきありがとうございます。
結局色々やってみても解決されず
悪戦苦闘しております。。。

売上シートにも在庫シートにも　緑の▼エラー
がすごく沢山、文字列ではなく、数値や標準にするとやはり
T列が反映されなくなってしまいました。

このセルは文字列で保存されていますや、
テキスト形式もしくはアポストロフィーになってます。と

エラーの状態でなければ、T列が反映されないようです(>_<)
難しい。。。。

回答No.2

kagakusuki

カテゴリーがWindows 7となっていますので、質問者様が御使いになられているＥｘｃｅｌのバージョンは、おそらくＥｘｃｅｌ2007以降のものであると思われますが、万が一、Ｅｘｃｅｌ2007よりも前のバージョンであった場合も考えて、Ｅｘｃｅｌのバージョンに関係なく使用する事が出来る方法を御伝え致します。

　今仮に、売上管理シートのT列において、「仕入価格」と入力されているセルがT1セルであるものとしますと、まず、T2セルに次の様な関数を入力して下さい。

=IF(COUNTIF(入出庫表シート!$J:$J,$E2),VLOOKUP($E2,入出庫表シート!$D:$J,7,FALSE),"")

　或いは

=IF(ISERROR(1/(VLOOKUP($E2,入出庫表シート!$D:$J,7,FALSE)<>"")),"",VLOOKUP($E2,入出庫表シート!$D:$J,7,FALSE))

　でもOKです。
　そして、T2セルをコピーして、T3以下に貼り付けて下さい。

　これで、仕入価格が自動的に表示されます。

お礼

エラーの件、無事解決いたしました
お二人方に大変感謝しております<(_ _)>

優しく親切にわかりやすくて助かりました。

来年から、新しい売上シートでスタートできます!!

本当にありがとうございました。

補足

回答いただきありがとうございます。

全部やっててみたものの、うまくいかず・・・

何がいけないのか・・・・
なんとか、色々試行錯誤でやってみます(>_<)

回答No.1

bunjii

>売上管理シートのE列 商品番号を入力すると
>その商品番号の 入出庫表シートJ列にある価格を
>売上管理シートのT列に自動で入力するには
>どうしたらよいでしょうか???
「T列に自動入力」ではなく「T列へ抽出」と言うことではないでしょうか？
VLOOKUP関数で目的が達成できます。

=IFERROR(VLOOKUP(E2,入出庫表シート!$D$2:$J$10000,7,0),"")

外側のIFERROR関数は商品コードの該当がないときに空欄とするために使います。
品名も同様に抽出できます。

例示のデータは矛盾がありますので提示する場合は矛盾の無い状態にするよう心掛けてください。

お礼

出庫表シートの商品コードによって
売上シートTに出るものと出ない場合があります。
在庫シートに緑の▼エラーが出ているセルの商品コードのみ
反映されるようです。書式は全部文字列になってるのですが
まばらでエラーのセルとエラーなしのセルになってしまってます。

あと、教えていただいた
=IFERROR(VLOOKUP(E2,入出庫表シート!$D$2:$J$10000,7,0),"")

仕入価格は反映されるものの、隣のU列
利益　=G6-T6+H6　V列の税項目が
E列の商品番号を入力しないと、全部#VALUEになってしまい
商品番号入力すると金額が反映します。

とても、難しいです(>_<)

補足

回答いただきありがとうございます。
やっててみたものの、うまくいかず・・・

困惑しております((+_+))
表まで添付いただき、ありがとうございます。

なんとか、試行錯誤でやってみます・・・