仕入元帳と請求書の照合です。
SHEET1に仕入データ、SHEET2に請求データがあります。
(SHEET1)
科目…日付…注番…品名…数量…単価…金額…照合…税…税込金額
仕入…12…ああ…あか… 3…20…60… …3…63
仕入…15…いい…あお… 2…10…20… …1…21
仕入…18…うう…しろ… 1…20…20… …1…21
(SHEET2)
科目…日付…注番…品名…数量…単価…金額…照合…税…税込金額
請求…12…ああ…あか… 3…20…60… …3…63
請求…18…うう…しろ… 1…20…20… …1…21
請求…15…いい…あお… 2…10…20… …1…21
というように各SHEETには数千件のデータがランダムに表示されています。チェックポイント注番と金額で各SHEET同一の注番かつ金額があれば照合欄にaを、照合できないものは空白を表示させたいのです。
毎回データの量が違うため、マクロの記録を使えません。何か良い関数や、マクロがございましたらご教授お願い致します。
A 回答 (9件)
- 最新から表示
- 回答順に表示
No.8
- 回答日時:
NO7です。
>このダブりを解決する条件式はございますか?
この「解決」とはどのような事でしょうか。
はっきりしないご質問をし申し訳ございません。
1つの仕入(注番、金額)に対し1つの請求(注番、金額)を照合したいのです。しかし、現在の式では同じ注番・金額のものがシート1(仕入)で1つ、シート2(請求)で3つあった場合でもaをチェックしてしまうと思われます。これでは仕入値と請求値のチェックした合計が違ってきます。
できれば一度aを付けたものとの照合は避けたいのです。
うまくお伝えすることができずに済みません。宜しくお願いします。
No.7
- 回答日時:
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","")
大変お世話になっております。
早速ご回答拝見させていただき、実行してみました。
結果式自体に問題はなく合計は出せたのですが
シート1の1つの注番、金額に対しシート2にシート1と同じ注番、金額が2つ以上あった場合にもaが付くようになっています。
このダブりを解決する条件式はございますか?
宜しくお願いします。
No.6
- 回答日時:
NO5です。
>例えば品名の一部(セル内の前から10文字)をマッチング
(例)品名をD列でセルの先頭から10文字を照合しています。
=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は表記されていました。
恐れ入りますが再度ご回答お願いします。
No.5
- 回答日時:
NO4です。
キー条件を当初回答で進言しました日付等を追加しては如何でしょうか。
(例)B列を日付として照合キーを追加。
=IF(SUMPRODUCT((sheet2!$B$2:$B$10000=$B2)*(Sheet2!$C2:$C$10000=$C2)*(Sheet2!$J2:$J$10000=$J2)),"a","")
ありがとうございます。私自身も先述であったようにキーとなる列を増やしたのですが(日付)各シートチェックした合計が合いませんでした。
ご回答頂いた数式に加え、例えば品名の一部(セル内の前から10文字)のマッチングといった条件追加は可能なのでしょうか?
No.4
- 回答日時:
NO1です。
数式は、どちらのシートもC列を注番、J列を税込金額としていますので、実際の列番号と相違している場合は変更して下さい。
前回答に誤りがありましたので、訂正させて頂きます。
=IF(SUMPRODUCT((Sheet2!$C2:$C$10000=$C2)*(Sheet2!$J2:$J$10000=$J2)),"a","")
ご回答有難うございます。
早速実行いたしましたが両シートのaを付けた合計が合いません。
もしかしたらシート1の照合キーに対し、シート2に合致するものが2つ以上あった場合にもaが付けれれている可能性があります。
もしそうだとしたら解決策はございますか?
No.3
- 回答日時:
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","")
ご回答有難うございます。
早速実行してみたのですがシート1とシート2のチェックされたaの合計が合いません。
もしかしたらダブって照合しているのかもしれません。
シート1の照合キー1行に対しシート2には照合キーが2行以上あるものもaと表記されているのかもしれません。
何か対応策はございませんか?
No.2
- 回答日時:
例えば、シート1の照合列H2に=IF(SUMPRODUCT((sheet2!$C2:$C10000=$C2)*(sheet2!$J2:$J10000=$J2)),"a","")を入力し、下方向にコピー
又、シート2側も同様に数式のsheet2をsheet1に変更すればクロスで検証できます。
尚、照合キーとして日付もあった方が良いのではないでしょうか。
その場合、(sheet2!$B2:$B10000=$B2)を*で繋げて下さい。
早速のご回答有難うございます。が、恥ずかしながら私全くの初心者でニュアンス的に式は読めたのですが、実際mu2011さんの式を当てはめ検証することしか出来ませんでした。
さらに貼り付け→コピーをしたのですが両シート0が表記されただけで照合は出来ませんでした。
もう少しお力添えをしていただけないでしょうか?
No.1
- 回答日時:
この回答への補足
ご回答有難うございました。
参考urlを確認させていただいたのですが
全く応用できそうにありません。
己の勉強不足で申し訳ございませんが
もう少しだけお力添えをしていただけないでしょうか?
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) SUMIFSと日付変換 10 2023/04/16 15:38
- Excel(エクセル) エクセルの散布図で新たに入力した値のデータラベルが空欄になる現象 1 2022/04/26 09:31
- 格安スマホ・SIMフリースマホ 今 ahamoを使っていて 4898円以下になる携帯会社があれば乗り換えたいです 20GBと24時間 9 2022/09/27 07:43
- Word(ワード) エクセル→ワード差し込み印刷 1 2022/10/05 17:32
- Excel(エクセル) 【Excel質問】別シートにある複数の同型の表から、同じ行項目にある数字を集計する 4 2023/02/16 00:14
- Excel(エクセル) 隣り合っていないセルを まとめて税込表示したい 8 2022/09/25 14:32
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- 財務・会計・経理 請求金額について 5 2023/04/10 09:17
- Excel(エクセル) 【エクセル】元データからの引用 5 2022/04/18 10:22
- Excel(エクセル) Excel2016 行間を詰めたい&同じカテゴリなら上位2つだけを表示したい 5 2022/06/03 12:19
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
CSVファイルについて質問です。
-
エクセルの「入力規則」のプル...
-
VBAについての質問です
-
関数の説明
-
Excelについて
-
Excelにいついて質問です。
-
[オプション]の[アクセスビリテ...
-
一つのセルに(例えばA1)入力...
-
エクセルで特定のセルに入力が...
-
【マクロ】名前を保存する際に...
-
Excelの関数について教えてくだ...
-
UNIQUE関数が使えないバージョ...
-
エクセル共有したが、アクセス...
-
エクセルファイルがファイルの...
-
Excelで縦軸の書式を0:00形式の...
-
エクセル vbaについて教えてく...
-
【Excel】数字を3倍にし、なお...
-
エクセルVBA、別ブックへ転記す...
-
エクセルであるセルに数字を入...
-
エクセル マクロ チェックボックス
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報