エクセルの関数について教えて頂ければ😭
( ˘･з･)チェッ@君の💜にRev.🎵
E16
F16
G16
にそれぞれ、松、竹、梅を入力してます。
これらは左の表のような数字に該当するとします。この時
E16:G16の合計を、H16セルに関数で直接’’9’’でだす事可能ですかね？
E16～G16のセルの文字は変更がありえます。たとえば、松、松、梅、になったりもします。そのままなら、単にSUM(A17:C17)で良いのですが
つまり変更のある可能性をクリアできる関数でないとダメなのです
最初から数字入れられれば良いけど仕様の関係上、文字でないと意味がなく、数値を入力するセルを隣接せる入れる事も同様にできないので
現状は、SWITCH関数で別表に松→5以下、で数値に換算し、その合計を出し、合計を出したセルをH16に反映させる、という事してます。
この二段階を一発でクリアできる関数ありますかねー
COUNTIFとかSUMIFでは出せない（私の式の誤りかも）のですよねー
できそうで出来ない・・・ググッても辿り着かない
二段階にしてると修正するのが面倒くさいので、可能なら一発で・・・
35 件のコメント
コメントするには、ログインまたはメンバー登録（無料）が必要です。
エクセルの関数はAIに質問すると割と的確な答えがでてるくので、試してみてください。質問は何回もしました。
的確じゃーなかったのか😂
>> トッチン@寝不足 さん
全パターンが31日分あるので、相当な長さの式になりますねー┐(´-｀)┌
>> トッチン@寝不足 さん
いやーこれだと各セルの文字が変化した場合にゼロ扱いになりますかね
各セルは、松の場合も竹の場合も梅の場合もありますので
その時に修正不要で計算してくれる式があるのかなー🤔
＝SUM(SWITCH(E16,”松”,5,”梅”,3,”竹”,3,0),SWITCH(F16,”松”,5,”梅”,3,”竹”,3,0),SWITCH(E16,”松”,5,”梅”,3,”竹”,3,0))
これで合計出ましたー
ありがとうございました
こんなのはどうでしょうか？
HLOOKUPを使って、右の松、竹、梅に対応する値を参照してSUMで合計するものです。
これだと松竹梅という項目が変わらなければ、左表の数字が今後変わっても対応できます。
=SUM(HLOOKUP(E16,A16:C17,2),HLOOKUP(F16,A16:C17,2),HLOOKUP(G16,A16:C17,2))
こんにちは😊ちょっと違う式でも。手書きですみません😅
>> akapenguin さん
それ、良いですね😁>> akapenguin さん
これ良いですねーさっき自分が考えた式を入れたら、3000文字くらいになりましたよ😅
一回いれればコピペするだけなんで、特に問題はないですが
でもHLOOKUPが断然シンプルですねー
やってみよ
ありがとうございます(^_-)-☆
>> Y. Daemon@ポリアモラス さん
ありがとうございます(^_-)-☆LOOKUP系でイケんじゃないかとは思ってましたが、LOOKUP系は引数が多くて苦手なんですよねー😭
>> akapenguin さん
エクセルは2021ver.ですがHLOOKUP使えますよね？
>> akapenguin さん
やることは同じですが、私なら、空欄行のセル(例えばE18～G18)にHLOOKUPで値を参照し、H16は単純にSUMだけにします。具体的には次のようにします
[E18] =HLOOKUP(E16,$A$16:$C$17,2,FALSE)
[F18] =HLOOKUP(F16,$A$16:$C$17,2,FALSE)
[G18] =HLOOKUP(G16,$A$16:$C$17,2,FALSE)
[H16] =SUM(E18:G18)
こうすれば松竹梅の3つより多くても修正が楽になります
HLOOKUPのセルを見せたくなければ、18行目を非表示にすればよいでしょう
なお、XLOOKUPはVLOOKUPとHLOOKUPを統合して分かり易くしたものですが、古いEXCELでは使えません
私が使っているのはエクセル2013なので2021でもHLOOKUPは残っていると思いますよ。
PC-6001🌜つきあかり@♫•*¨*•♪さん
最初に一発でできないかとありましたのでまとめて式を書きましたが、メンテナンス性を考えたらその方がよさそうですね。
>> ( ˘･з･)チェッ@君の💜にRev.🎵 さん
AIに質問したのをスクショしたのですが、不完全なものだったのですね〜。失礼しました🥺
質問の内容の範囲だと。
=SUMIF(A16:C16,F16,A17:C17)+SUMIF(A16:C16,G16,A17:C17)+SUMIF(A16:C16,H16,A17:C17)
どこか勘違いしていたらすみません。
>> 雪だるま さん
これも、なかなかシンプルですねー試してみます
ありがとうございます(^_-)-☆
色々やってみましたが、自分で関数を作ってしまうと一番簡単に書けますね。(^_^;)=SumRange(B2:D3,F2:H3)
第1引数で、横方向のテーブルを指定、
第2引数で、加算する領域を指定、
以上。(￣^￣)ゞ
>> アッカリ〜ン@_@….,….,…😅 さん
第1引数で、横方向のテーブルを指定、ですが、B2:D2でもいいですね。結局、一致したセルに対して、Offset(1)で、1行下の値を参照しているだけなので。
そうすると、.Rows(1).Cellsとかは、不要になりますね。
>> アッカリ〜ン@_@….,….,…😅 さん
う～ん・・・プロの回答ですね～
ムズいです😂
ありがとうございます(^_-)-☆
因みに、例の勤務表は、99％できてますが、やればやるほど改良点が出てきて、無限ループ状態です（笑）
もう、この先は作成者の意見を聞いてから進めようかと思ってます。
面白いけどねー
テーブルの指定が面倒なら、関数内にテーブルの位置を書いてしまえば、もう、加算する範囲だけを指定すればいいだけになります。他にもテーブルがあったら、別名の関数を作ったほうが、間違えにくいかも。
あとは、ご自由にどうぞ。(^_^;)
>> ( ˘･з･)チェッ@君の💜にRev.🎵 さん
シンプルイズベストです。あとから変更しようとすると面倒なことになったりします。
勤務表とかであれば、簡単なのがよいかと。
式をコピーするときは、固定したい列や行に$マークを使えば簡単にできますよ。
VLOOKUPだとうまくいかなかったのですが、SUMIFだと、検索対象を範囲にすると、配列が出力されて、INDEX(xxx,0)を通すと、SUMで加算できました。テーブルが縦でも横でも、検索対象が２次元でもいけました。
まぁ、そこそこ式はごちゃごちゃしますけど。
>> アッカリ〜ン@_@….,….,…😅 さん
おおお！これが一番シンプル！
個人的にはHLOOKUPのやり方が好きですが、何しろ31人分の別枠が要るので。メンテナンスは楽かもそれないけど
てことで、雪だるまさんのSUMIFを使わせてもらってましたが。
何か同じパターンの繰り返しなんで、そこをまとめれんのかなーと思ってましたが、イケますね
さすがはプロ（なのか？）（笑）
>> ( ˘･з･)チェッ@君の💜にRev.🎵 さん
〉〉自己レスあー31人でなく20人分の間違い
>> いまげ@少し慣れてきた さん
ヘルプ見たら、XLOOKUPは、配列を返せると書いてありました。私は、Excel2019なので、PCアプリでは使えませんが‥
目的の値がないときに#N/Aになるのを無視すれば配列数式でもできそうなんですが、
=SUM(HLOOKUP(E16:G16,A16:C17,2,FALSE)) ← Shift + Ctrl + Enter
ではうまくいかない。なぜだ？
=SUMPRODUCT(COUNTIF(E16:G16,A16:C16),A17:C17)
これなら配列数式でも行けるかも。
sumifやcountifで配列を返せるんですねー。
マスター(A〜C)に無いデータ(E〜F)があっても#N/Aエラーが出ないのでテーブル結合が簡単に出来て超便利！！！
今度から私も使います。
普通にLOOKUP系の関数で配列を返して、IFERROR関数を付けるだけで行けますね。古いバージョンではダメなんでしょうか。
=SUM(IFERROR(LOOKUP(E16:G16,A16:C16,A17:C17),0))
VでもHでもないLOOKUP関数を初めて使いました。どう違うんだろう？
あ、完全一致検索ができないっぽい！これはダメですね。ではHLOOKUPで。
=SUM(IFERROR(HLOOKUP(E16:G16,A16:C17,2,FALSE),0))
私の環境では上手く動きます。なんでだろう。
あと、こちらで試したのはスペルが使える環境で配列数式を使ったので、そこかもしれません。