- 締切済み
EXCELを使用したデータの照合
仕入元帳と請求書の照合です。 SHEET1に仕入データ、SHEET2に請求データがあります。 (SHEET1) 科目…日付…注番…品名…数量…単価…金額…照合…税…税込金額 仕入…１２…ああ…あか… ３…２０…６０… …３…６３ 仕入…１５…いい…あお… ２…１０…２０… …１…２１ 仕入…１８…うう…しろ… １…２０…２０… …１…２１ (SHEET2) 科目…日付…注番…品名…数量…単価…金額…照合…税…税込金額 請求…１２…ああ…あか… ３…２０…６０… …３…６３ 請求…１８…うう…しろ… １…２０…２０… …１…２１ 請求…１５…いい…あお… ２…１０…２０… …１…２１ というように各SHEETには数千件のデータがランダムに表示されています。チェックポイント注番と金額で各SHEET同一の注番かつ金額があれば照合欄にaを、照合できないものは空白を表示させたいのです。 毎回データの量が違うため、マクロの記録を使えません。何か良い関数や、マクロがございましたらご教授お願い致します。
- st_peco
- お礼率87% (14/16)
- オフィス系ソフト
- 回答数9
- ありがとう数7
- みんなの回答 （9）
- 専門家の回答
みんなの回答
- mu2011
- ベストアンサー率38% (1910/4994)
NO8です。 お役に立てず申し訳ありませんが、重複データの是非が疑問です。 今でもマナー違反（続きの質問）ですので、一度、データの整理（例えば、注番のユニーク性の確保等）を検討され、改めて質問されては如何でしょうか。
- mu2011
- ベストアンサー率38% (1910/4994)
NO7です。 >このダブりを解決する条件式はございますか？ この「解決」とはどのような事でしょうか。
お礼
はっきりしないご質問をし申し訳ございません。 １つの仕入（注番、金額）に対し１つの請求（注番、金額）を照合したいのです。しかし、現在の式では同じ注番・金額のものがシート１（仕入）で１つ、シート２（請求）で３つあった場合でもaをチェックしてしまうと思われます。これでは仕入値と請求値のチェックした合計が違ってきます。 できれば一度aを付けたものとの照合は避けたいのです。 うまくお伝えすることができずに済みません。宜しくお願いします。
- mu2011
- ベストアンサー率38% (1910/4994)
NO6です。 旅に出ていましたので回答が遅れましたことお詫びします。 数式に誤りがありましたので、以下の通り訂正致します。 =IF(SUMPRODUCT((LEFT(Sheet2!$D$2:$D$10000,10)=LEFT($D2,10))*(Sheet2!$C$2:$C$10000=$C2)*(Sheet2!$J$2:$J$10000=$J2)),"a","")
お礼
大変お世話になっております。 早速ご回答拝見させていただき、実行してみました。 結果式自体に問題はなく合計は出せたのですが シート１の１つの注番、金額に対しシート２にシート１と同じ注番、金額が２つ以上あった場合にもaが付くようになっています。 このダブりを解決する条件式はございますか？ 宜しくお願いします。
- mu2011
- ベストアンサー率38% (1910/4994)
NO5です。 >例えば品名の一部（セル内の前から１０文字）をマッチング （例）品名をＤ列でセルの先頭から１０文字を照合しています。 =IF(SUMPRODUCT((LEFT(Sheet2!$D$2:$D$10000,10)=LEFT($D2,10))*(Sheet2!$C2:$C$10000=$C2)*(Sheet2!$J2:$J$10000=$J2)),"a","")
お礼
本件、私にお付き合い頂き誠に感謝しています。 ご回答いただいたLEFT…の式を入力したのですが #N/Aと表記されてしまいます。 NO4で頂いたご回答では合計金額は合わずともaは表記されていました。 恐れ入りますが再度ご回答お願いします。
- mu2011
- ベストアンサー率38% (1910/4994)
NO4です。 キー条件を当初回答で進言しました日付等を追加しては如何でしょうか。 （例）Ｂ列を日付として照合キーを追加。 =IF(SUMPRODUCT((sheet2!$B$2:$B$10000=$B2)*(Sheet2!$C2:$C$10000=$C2)*(Sheet2!$J2:$J$10000=$J2)),"a","")
お礼
ありがとうございます。私自身も先述であったようにキーとなる列を増やしたのですが（日付）各シートチェックした合計が合いませんでした。 ご回答頂いた数式に加え、例えば品名の一部（セル内の前から１０文字）のマッチングといった条件追加は可能なのでしょうか？
- mu2011
- ベストアンサー率38% (1910/4994)
NO1です。 数式は、どちらのシートもＣ列を注番、Ｊ列を税込金額としていますので、実際の列番号と相違している場合は変更して下さい。 前回答に誤りがありましたので、訂正させて頂きます。 =IF(SUMPRODUCT((Sheet2!$C2:$C$10000=$C2)*(Sheet2!$J2:$J$10000=$J2)),"a","")
お礼
ご回答有難うございます。 早速実行いたしましたが両シートのaを付けた合計が合いません。 もしかしたらシート１の照合キーに対し、シート２に合致するものが２つ以上あった場合にもaが付けれれている可能性があります。 もしそうだとしたら解決策はございますか？
Sheet1!H2: =IF(SUMPRODUCT((C2=Sheet2!C$2:C$9000)*(G2=Sheet2!G$2:G$9000)),"a","") Sheet2!H2: =IF(SUMPRODUCT((C2=Sheet1!C$2:C$9000)*(G2=Sheet1!G$2:G$9000)),"a","")
お礼
ご回答有難うございます。 早速実行してみたのですがシート１とシート２のチェックされたaの合計が合いません。 もしかしたらダブって照合しているのかもしれません。 シート１の照合キー１行に対しシート２には照合キーが２行以上あるものもaと表記されているのかもしれません。 何か対応策はございませんか？
- mu2011
- ベストアンサー率38% (1910/4994)
例えば、シート１の照合列H2に=IF(SUMPRODUCT((sheet2!$C2:$C10000=$C2)*(sheet2!$J2:$J10000=$J2)),"a","")を入力し、下方向にコピー 又、シート２側も同様に数式のsheet2をsheet1に変更すればクロスで検証できます。 尚、照合キーとして日付もあった方が良いのではないでしょうか。 その場合、(sheet2!$B2:$B10000=$B2)を*で繋げて下さい。
お礼
早速のご回答有難うございます。が、恥ずかしながら私全くの初心者でニュアンス的に式は読めたのですが、実際mu2011さんの式を当てはめ検証することしか出来ませんでした。 さらに貼り付け→コピーをしたのですが両シート０が表記されただけで照合は出来ませんでした。 もう少しお力添えをしていただけないでしょうか？
- neko-tama
- ベストアンサー率29% (112/377)
同じデータを探し出して、何か値を出すのなら、vlookupでは？
補足
ご回答有難うございました。 参考urlを確認させていただいたのですが 全く応用できそうにありません。 己の勉強不足で申し訳ございませんが もう少しだけお力添えをしていただけないでしょうか？
関連するQ&A
- エクセルを用いた仕入データと請求データの照合（マッチング）
エクセルでの仕入データと請求データの照合です。 シート１には仕入データ、シート２には請求データがあります。 各シートの様式は以下です。 A行から 日付ー注番ー品名ー数量ー単価ー金額ー税ー税込金額ーチェック となっています。 仕入データと請求データの注番と金額が合えばチェックの行にaを付けます。 以前投稿した際に =IF(SUMPRODUCT((Sheet2!$B$2:$B$10000=$B2)*(Sheet2!$F$2:$F$10000=$F2)),"a","") の式を頂きましたが、 例えばシート１に注番0001・金額1,000の行が１行あり シート２に注番0001金額・1,000の行が２行あった場合でもチェックの列にaが入力されてしまうのです。 ＊この場合だと仕入1,000円、請求2,000円で仕入＝請求になりません。 このように一度照合したものに関しては同じ注番、同じ金額だとしてもチェックしないという条件の式をご回答頂きたくご質問させていただきました。 ご回答何卒宜しくお願い致します。
- 締切済み
- その他([技術者向] コンピューター)
- エクセルデータを照合したいのですが・・・
データの照合でかなり時間を使っているので、何とか効率を図りたくて、でも、知識なくわからないので、どなたか教えてください。 シート1 No. JANコード 数量 単価 金額 その他データ シート2 No. JANコード 数量 単価 金額 その他データ と、同じようなデータが別々の場所から抽出されます。その他データには、それぞれ少しずつ違う内容が入って、一行になっています。 No. JANコード 数量 単価 この4項目が一致したら、シート1とシート2のデータで一致するものを、 シート1の一致するシート2のデータを表示させたいのですが、そのようなことがそもそもできるのか、できるなら方法を教えていただきたいのですが・・・ よろしくお願いいたします。
- 締切済み
- 財務・会計・経理
- 異なるエクセルシートの照合
はじめての質問です。よろしくお願いします。 過去の事例でも同じような例があったと思いますが マクロは全く分からず、この設定にあったものを作成して頂けるなら幸いです。 （内容） 当方の仕入明細データと先方の売上明細データの照合です。かなり、双方の誤差が多く、現在手作業でやっていますが、時間がかかります。エクセルで誤差を 簡単に出せる方法を教えてください。 （設定） sheet1当方仕入 sheet2先方売上 Ａ列：品名、Ｄ列：重量、Ｅ列：単価、Ｆ列：金額 です。品名は双方の表記が異なるので照合対象からはずしてください。一致するものは表示せず、差があるものだけ表記し、差額金額を出して欲しいのです。 問題は、行数の合計がsheet1と2で異なることがあります。（仕入漏れ＆売上漏れ）
- 締切済み
- その他（インターネット・Webサービス）
- 2つのデータを照合したいです
お客様から送られてくるエクセルの注文データと、自社で管理している注残データを照合し、お客様の注文データにあって自社注残データにないもの＝新規注文データのみを別シートに抽出したいです。 これを行うにはマクロでいいのでしょうか？ エクセルのシート１にユーザー注文データ。 シート２に自社注残データ。 この状態でマクロ実行→新たにシート３が作られ、照合できない新規注文データのみを抽出。 ということをイメージしています。 シート１（ユーザー注文データ）のI列に型式、F列に数量が入っています。 シート２（自社注残データ）のI列に型式、J列に数量が入っています。 この型式・数量の2項目を照合したいのですが、ユーザーデータには型式の頭に「P （Pと半角スペース）」が全数ついています。 このP+スペース以降の型式が照合対象です。 型式・数量が一致しないものを新たにシート３を作成しそちらに抽出したいです。 データ数は大体100行程度。 シート１・２共に1・2行には項目タイトルが入っているので、3行目からデータが始まります。 自分で作成しようとVBAの本を買ってきて勉強中なのですが、超初心者なので、まだまだ時間がかかりそうです。 どなたかお知恵を貸していただければと思います。 よろしくお願いいたします。
- 締切済み
- オフィス系ソフト
- エクセルでのデータ照合
エクセルで出来るか教えてください。 Sheet1 [品目コード]、[数量(c/s)]、[端数（ｐ/ｃ）] [ABCD] [110] 、 [ 2] [EFGH] [ 9 ] 、 [ 3] [IJKL] [ 0] 、 [ 7] Sheet2 [品名KEY]、[総個数(C/S)]、[総端数（Ｐ/Ｃ）] [ABCD] [ 110 ] 、 [ 2] [EFGH] [ 8 ] 、 [ 3] [IJKL] [ 0 ] 、 [ 7] Sheet1とSheet2のデータ数量を照合して、数量に相違があるか簡単に調べる方法を 教えて下さい。 知識がないので行き詰っています。 知識が豊富なか方、助けてください。
- 締切済み
- オフィス系ソフト
- EXCELの２つのシートの照合処理マクロについて
マクロが得意でなく下記の処理が可能かご教授いただければと お願いします。 ・シートＡに自社の発注済みデータ ・シートＢに発注先から送られてきた受注データがあります。 シートＡの発注番号コードとシートＢの受注番号コードが一致するので これを基にそれぞれのシートにある数量、単価、金額を照合し 一致しないレコードのみシートＣにコピーさせる。 という流れなのですが、 ・それぞれのシートのコード、数量、単価、金額は別々の列にあります。 現在関数とオートフィルターで処理しているのですが効率が悪いため 改善するよう指示されて困っております。 よろしくお願いします。
- ベストアンサー
- Excel（エクセル）
- エクセルで仕入帳の作り方
はじめまして。エクセル初心者で初歩的な質問かもしれませんが教えてください。お願いします。 エクセルで1年分の仕入帳を作成しています。シートで1月から12月まで分けてそれぞれ品名、数量、単価、仕入金額の項目を作って金額を算出できるようにしているのですが、品名を入れたときに自動的に単価を単価の項目欄に表示されるようにしたいです。また、品名も全部で５０種類位あるのですが、それも選択するようなかたちで簡単に処理できれば尚良いなと思っています。 抽象的な質問で恐縮ですが宜しくお願いします。
- ベストアンサー
- オフィス系ソフト
- エクセルマクロ シート間の照合＿上書き
マクロ初心者です。（エクセル２００３使用） Sheet2の管理番号をSheet1の管理番号と照合し、同じであれば、数量など３項目を上書きするマクロを作ろうとしています。 （Sheet1：日々更新される元データ）全データ数約500件くらい Ａ列 ,Ｂ, Ｃ, Ｄ, ・・・ １行 管理番号,品名,注文数量,出荷数量,・・・ （Sheet2：上書きさせたいシート）全データ数約80件くらい Ｇ列 ,Ｈ, Ｉ Ｊ ９行 管理番号,品名,注文数量,出荷数量 ↑シート２にある管理番号をもとに数量などを照合＆上書きをしたいのです。 ■シート１も２も行数は日々変動します。 ■シート１で、まれに同じ管理番号が２つ存在することがありますが、取り出したい数量などのデータは、常に１番目に照合する管理番号です。 Sub シート間照合と上書き() Dim i As Integer a = Worksheets("sheet1").Range("a65536").End(xlUp).Row For i = 2 To a If Worksheets("sheet1").Range("A2") = Worksheets("sheet2").Range("G9") Then Worksheets("sheet1").Cells(1, i) = Worksheets("sheet2").Range("G9") Worksheets("sheet1").Cells(2, i) = Worksheets("sheet2").Range("H9") Worksheets("sheet1").Cells(3, i) = Worksheets("sheet2").Range("I9") While Cells(1, i) <> "" i = i + 1 Wend End If Next End Sub ■上記 模索しながらマクロを作ってみたのですが、エラーにはならないのですが（F8）、まったく動きませんでした。 すみませんが、お力をかしてください。 よろしくお願いいたします。
- ベストアンサー
- Visual Basic
- エクセルで入力用の場所に入力したら、入力内容が番号ごとに反映される方法を教えてください。
エクセルで入力用の場所に入力をしたら、その入力内容が番号ごとに別のシートに反映されるようにしたいのです。 反映される表は、『材料費の表』と『会計帳簿』です。 入力項目は 1.仕入先 2.月日 3.工番 4.材料区分 5.材質 6.種類 7.品名 8.寸法 9.数量 10.単位 11.重量 12.単価 13.仕入金額 14.支払金額 15.差引残高 16.備考 この16項目の中から 材料費の表には 1.仕入先 2.月日 3.工番 4.材料区分 5.材質 6.種類 7.品名 8.寸法 9.数量 10.単位 11.重量 12.単価 13.仕入金額 16.備考 の項目を転記させ、1工番につき1シート、工番ごとに振り分けたいんです。 ※工番25＝シート１ 工番26＝シート２ というように…。 工番は100番前後まであります。 会計帳簿には 1.仕入先 2.月日 7.品名 8.寸法 9.数量 10.単位 11.重量 12.単価 13.仕入金額 14.支払金額 15.差引残高 16.備考 の項目を転記させ、１仕入先につき1シート、仕入先ごとに振り分けたいんです。 仕入先の数は100前後です。 それぞれ1年分のデータを入力しないといけないので10000行はほしいのです…。 入力する内容がたくさんで、でも中身は同じなのでどうにか連動させて入力する手間を省きたいのです。 私はコンピューターのことが全然わからないので、どうしても人より時間がかかってしまいます…。 他にもやらなければならない仕事がたくさんあってどうしてもこれだけのために時間を割くことができないのです…。 いろんな方の質問を参考にやってみようと思ったのですが何が書いてあるのかちんぷんかんぷんで… お忙しいかとは思いますが、こんな私を助けてください。
- ベストアンサー
- オフィス系ソフト
- EXCEL重複データの抽出に関する質問
仕事で行き詰っています。 過去5年分くらいの仕入実績価格表(EXCEL)データから最新の価格のみを抽出する場合、どのような手順をふめば良いでしょうか？ ＜元データに入っている項目＞ (1)品名(2)仕入年月日(3)数量(4)単価 ＜抽出したいデータ＞ (1)品名(2)単価 品名が重複している場合は、一番直近に仕入れた単価を適用したいです。つまり一点一様の単価表を作成したいのです。 データは２０００件以上はあるかと思います ピボットにて品名と単価抽出はできるかと思うのですが、最新価格のみ抽出する方法がわかりません どなたかお知恵を貸していただけないでしょうか 詳細不足していましたら、ご指摘ください 宜しくお願いします
- 締切済み
- オフィス系ソフト
補足
了解しました。今までお付き合いいただき有難うございました。 一度内容を整理し再度投稿したいと思います。 本当に有難うございました。