- ベストアンサー
Excel:複数条件の値代入について
Excelで、複数条件をキーにした値の代入方法を教えて下さい。 言葉ではお伝えづらいので添付の表をご確認頂きたいと思います。 A表(sheet1)には各人員の、月毎の金額、合計値が入っています。 ここから別シートのB表(sheet2)に、値の入っている最終月を代入したいのですが、 条件としては3種類あり、 1.値が12月まで埋まっている場合は「12」を代入 2.値が途中まで埋まっている(その後は空欄)場合は、埋まっている最終月を代入 3.【合計】の欄に0が入っている場合は、ブランクを代入 このような条件になっております。 関数で「if」を繰り返していくと、【合計】含めて13回繰り返す事になるため、 何かすっきりとできる手法があれば、ご教授頂けると有り難いです。 宜しくお願い致します。
- rg6ms
- お礼率31% (6/19)
- その他MS Office製品
- 回答数8
- ありがとう数0
- みんなの回答 (8)
- 専門家の回答
質問者が選んだベストアンサー
シート2のA1セルには次の式を入力してB1セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。 =IF(ROW(A1)>COUNTA(Sheet1!$A$2:$A$1000),"",IF(COLUMN(A1)=1,Sheet1!$A2,IF(SUM(Sheet1!$C2:$N2)=0,"",INDEX(Sheet1!$C$1:$N$1,MATCH(10^10,Sheet1!$C2:$N2)))))
その他の回答 (7)
- MackyNo1
- ベストアンサー率53% (1521/2850)
質問通りの条件で、例示のように空白セルまでは数値が連続して入力されているなら以下のような数式がわかりよいかもしれません。 =IF(VLOOKUP(A2,Sheet1!A:B,2,0)=0,"",INDEX(Sheet1!$C$1:$N$1,COUNT(Sheet1!C2:N2)))
- kuma310min
- ベストアンサー率40% (212/518)
No.5 KURUMITOさんの、 MATCH(10^10,Sheet1!$C2:$N2) には脱帽しました。 条件1&2は、これだけで実現出来てしまいます。 この方法に一票。
- tom04
- ベストアンサー率49% (2537/5117)
No.2です! たびたびごめんなさい。 前回の数式は途中の列に空白セルがあっても対応できるように配列数式にしましたが、 C列から必ず列方向(右側)に数値でデータが埋まっていくのであれば 配列数式にする必要はありません。、 Sheet2のB2セルに =IF(COUNT(Sheet1!C2:N2),INDEX(Sheet1!C$1:N$1,,COUNT(Sheet1!C2:N2)),"") としてオートフィルで下へコピーしてみてください。 何度も失礼しました。m(_ _)m
- MSZ006
- ベストアンサー率38% (390/1011)
先の回答者様のご回答にOFFSET関数を加えて、 =IF(Sheet1!B2=0,"",OFFSET(Sheet1!$B$1,0,COUNT(Sheet1!C2:N2))) でどうでしょう。
- kuma310min
- ベストアンサー率40% (212/518)
シート2のB1に、 =IF(Sheet1!B2=0,"",COUNT(Sheet1!C2:N2)) →下へコピー でいいのではないでしょうか。 ただし、 ・1~3月空白、4~9月数値、10~12月空白 のような場合は、No.1さんのような回避策が必要になりますが。
補足
多数のご回答、ありがとうございます。 大変申し訳ありませんが、質問提示に不足がありました。 サンプルでは「1月~12月」の表記になっていますが、 実際の表は「1月~12月」であったり「4月~翌3月」 であったりする為、count関数は使用できない状態です。 (その為、あくまでC1~N1までの値を代入する事になります) 誤解を招く表記ですみませんでした。 もし他に手法があれば、ご教授頂けますでしょうか…?
- tom04
- ベストアンサー率49% (2537/5117)
こんにちは! 一例です。 Sheet2のA列はSheet1の名前順に入力済みだとします。 Sheet2のB1セルに =IF(COUNT(Sheet1!C2:N2),INDEX(Sheet1!C$1:N$1,,MAX(IF(Sheet1!C2:N2<>"",COLUMN(Sheet1!A1:L1)))),"") これは配列数式になってしまいますので、Shift+Ctrl+Enterで確定! この画面からコピー&ペーストする場合は 上記数式をコピー → B1セルを選択 → 数式バー内をクリック → 貼り付け そのまま(編集可能のまま)Shift+Ctrlキーを押しながらEnterキーで確定! 数式の前後に{ }マークが入り配列数式になります。 このB1セルをオートフィルで下へコピーしてみてください。m(_ _)m
空白セル数をカウントし、12から引けば結果が得られますが、この方法は仮に2月から始まる場合には、1月セルに空白セルがあると成立しませんので、1月セルには0を入力する必要があります。 sheet2のB2式 =IF(Sheet1!B2>0,12-COUNTBLANK(Sheet1!C2:N2),0)
関連するQ&A
- 複数の条件に値する1つの値を求めたい。
下記のような2つのSHEETがあり、【SHEET1】の2つの条件に値する数値を【SHEET2】より選ぶ関数を教えて下さい。 例えば、製品Aの品質○は【SHEET2】では3ですが、それを表す関数を 【SHEET1】の(在庫)?のところへ入れたいのです。 品質がその日によって変わるので、単にSHEETをリンクさせるのではなく、条件の付いた関数を入れたいのですが可能でしょうか? 宜しくお願い致します。 【SHEET1】 (製品) A B C D・・・ (品質) ○ × ○ ○ (在庫) ? ? ? ? 【SHEET2】 ○ × A 3 5 B 4 7 C 1 9
- ベストアンサー
- オフィス系ソフト
- Excel関数 「日付を入力したら値を返す」
仕事で支払管理表を作成しています。 請求先名・金額・金融機関・振込日などをデータとしています。 請求金額の合計・支払済金額の合計・残額の合計が自動計算されるようにしたいのですが、どうすればいいでしょうか? 私が考えたのは、IFを使って「振込日セルに日付が入力されたら、支払金額の値を返す」新しい列を作り(ここで支払い金額の合計を出す)、請求金額の合計-支払金額の合計=残額とすることです。 ただ、このIF式もどうすればいいのか…。「日付が入力されたら」というあいまいな条件を関数にできるのでしょうか? 「支払金額」という列をつくり、金額を手入力するのはやりたくありません…。 そのほかの手法でも構いませんので教えてください。 どうかよろしくお願いします。
- ベストアンサー
- Windows XP
- 【Excel】複数条件の合計
Excel2003を使用しています。 Sheet1のD7:D200に『あ』、G7:G200に2(数値)、と入力されているH列の合計をSUMPRODUCT 関数を使用して計算しました。 同じ条件で、範囲の最終行数をあるセルに取り出しておいて、INDIRECT 関数を組み合わせたのですが、うまくいきませんでした。 数式は、Sheet2に下記のように入力してみましたが、このような使い方は間違っているのでしょうか? =SUMPRODUCT((Sheet1!D7:INDIRECT("D"&F1)="あ")*(Sheet1!G7:INDIRECT("G"&F1)=2)*(Sheet1!H7:INDIRECT("H"&F1))) また、他にこんな方法があるというのがあれば、あわせて教えていただけると嬉しいです。 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- Excel 検索して返した値を条件で印を付けたい
以前、「Excelで同じ商品IDが登録されたデータの中から一致する値を返したい。」という質問を投稿しました。 htt◯s://okwave.jp/qa/q10160134.html ↑◯をpに変換して下さい。 そこから派生なのですが、重複するIDが登録されたデータの中から一致した値を条件に掛けて、検索して返した値>5%の場合◯印、検索して返した値>=10%の場合△印を付けるにはどうすればよいでしょうか? Excelシートの内容構成は添付画像の通りです IF関数にCOUNTIFS関数とSUMIFS関数をネストして、条件一致がゼロなら空欄、条件一致がカウント1以上(重複)の場合は”重複”と表示し、条件一致がカウント1ならSUMIFS関数で値を取り出すといった内容です。 以下の条件の場合、となりの列に印を入れたいです。 ・H3:12 物質A、B、Cのうち、5%を超えている場合は"◯" ・I3:I12 H列の"◯"が無い場合、物質AA、物質BBのうち、10%以上の場合は"△" 条件書式で数式で色付けすることは出来るのですが、関数などで印を付けようとすると数値が空欄でも印が入ってしまいます。 詳しい方居ましたら、ご教授下さい。 よろしくお願いします。
- ベストアンサー
- Excel(エクセル)
- EXCEL2003 別シートを参照する条件付書式のINDIRECTの使
EXCEL2003 別シートを参照する条件付書式のINDIRECTの使い方 いつもお世話になっております。 別シートを参照する条件付書式のINDIRECTの使い方について教えてください。 現在シートが2枚(表とLIST)あり表のH列がブランクではない時に I5:U6に条件付書式を入れたいと思っています。 シート構成は以下の通りです。 -------------------------------------------------------------- 設定したい条件(例:I4/4月度の氏名:「ああ」の場合) H5がブランクではない時 シート:表のI5の値(320)が シート:LISTのBK3より(設定された値/4月度の氏名:「ああ」は293)より 大きい場合に赤の太文字にしたい -------------------------------------------------------------- ブランクではない場合はH5<>"", LISTの値を見る(ああの4月の値を出す)数式は 以下の式を使ったことがあるのですが =SUMPRODUCT((LIST!$BF$3:$BF$147=F5)*(LIST!$BK$2:$BV$2=I4)*(LIST!$BK$3:$BV$147)) この組み合わせ方や別シートを参照するINDIRECTの使い方がわかりません。 設定したい条件を可能にするにはどのようにしたらいいのでしょうか? ご教示お願いいたします。 シート:表(I4:U4は4月:3月の意味です) F G H I ・・・ U 4 No. 氏名 区分 4 5 ・・・ 3 5 11 ああ AA 320 310 ・・・ 300 6 12 いい AB 295 200 ・・・ 280 シート:LIST(BF2:BV147で1ヶ月の制限の値が入った表です) BF BG BK BL・・・ BV 2 No. 氏名 4 5・・・ 3 3 11 ああ 293 263・・・290 4 12 いい 295 300・・・293
- ベストアンサー
- オフィス系ソフト
- エクセル:複数条件一致で値を代入する場合
複数条件で一致した場合、別のセルに他の値を代入したいです。 添付の図のE列には、「年月」と「商品名」が一致して「産地」が<青森>だった場合、 「年月」と「商品名」が一致した「産地」が<日本>の「売上げ数」を代入したいのですが、 やり方がわかりません。。。 ご教示いただけますと幸いです。
- ベストアンサー
- Excel(エクセル)
- EXCEL2003 複数の条件を満たす値の摘出
補充付きで、再度質問します。 行と列の交わるところの値を摘出したいのですが、条件が複数になってしまいます。 例えば 「1月のA社の入金は?」と言う具合です。表は下記のような感じです。 B16には、数式バーに入力されている関数が入っています。(B17・B18にも) A14の月を変更すると、15行目の請求額・入金額がその月に変更になるので、16~18行目にそれぞれの金額等を上記の表(5~7行目)から摘出して自動で表示させたいのです。 どんな計算式を使えばいいのか教えてください。 言いたい事がわかりずらいかもしれませんが、よろしくお願いします。
- ベストアンサー
- Windows系OS
- 複数の条件を満たした時に特定セルの値を返す関数
複数の条件を満たした時に【特定のセルの値】を返す関数を探しているのですが、自分が求めている方法が見つからず困っています。 大変お手数をおかけしますが、関数に詳しい方がおられれば、 ぜひご教授頂ければ幸いです。 例: 下記の画像のような表があります。 ※sheet2にも同じような表があります。(ただ、【●●●】の列は未記入の状態です) そこで教えて頂きたいのが、【A列】【B列】が同じ時に【●●●】列の値をsheet2の【●●●】 に返したいのですが、その関数を是非、教えていただけますでしょうか。 よろしくお願いいたします。
- 締切済み
- Windows系OS
- 【Excel】条件を満たした最下行の値を表示
こんにちは。 Excel2013を使用しています。 《Sheet1》 A B C ・・・・・・ N 1 1234 ○ 01/01 2,000 2 1234 ○ 01/30 5,000 3 2345 △ 01/05 1,000 4 3456 □ 01/20 3,000 5 3456 □ 01/25 4,000 6 1234 ○ 02/10 6,000 7 3456 □ 02/15 5,000 上記のような表がSheet1にある場合、A列が1234(数値)である最下行のN列の値をSheet2のA1セルに表示させたいのですが、関数で可能でしょうか? この場合はSheet2のA1セルに表示される値は6,000、同様にA列が2345(数値)である最下行のN列の値の場合は1,000です。 よろしくお願いします。
- ベストアンサー
- Excel(エクセル)
- エクセルで複数条件で値を検出する方法
質問です。 添付した上の表がメインで下の表が別シートにあります。 エクセルで別シートからメインの表に今日と一週間前の15時と18時の数値を検出したいと思ってますがうまく出来ません。 最初にvlookup関数を使用しましたが、今日と一週間前の列を参照までは出来ますが時間の指定が出来ず11時の値が検出されてしまいます。 indexとmatchを組み合わせれば出来るという質問応答があったので参考にしてみましたが うまく関数を作れず難航してます。 メインの表のD3、F3にどのような関数を入れればうまく別シートから条件にあった値を検出する事が出来るのでしょうか? よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
補足
多数の回答、再度御礼申し上げます。 色々と頂いた回答を試した結果、 「間にデータが抜けている場合でも正確に最終月が代入できる」 という事がわかった、KURUMITO様の提示式を採用させて頂きました。 本当に有り難うございました。