[[20230118110308]] 『この計算式を短くできますか?』(ヒカゲ) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『この計算式を短くできますか?』(ヒカゲ)

 セルF45の計算式が次のようになっています。

 =IF(COUNTIF(F14,"*-*")=1,SWITCH(LEN(F14),9,RIGHT(F14,4)-LEFT(F14,4),10,RIGHT(F14,5)-LEFT(F14,4),RIGHT(F14,5)-LEFT(F14,5)),IF(OR(COUNTIF(F14,{"*キャンセル*","*中止*","×","*に振*","希望*"})),0,SWITCH(LEN(F12),0,0,9,RIGHT(F12,4)-LEFT(F12,4),10,RIGHT(F12,5)-LEFT(F12,4),RIGHT(F12,5)-LEFT(F12,5))))
 +IF(COUNTIF(F19,"*-*")=1,SWITCH(LEN(F19),9,RIGHT(F9,4)-LEFT(F19,4),10,RIGHT(F19,5)-LEFT(F19,4),RIGHT(F19,5)-LEFT(F19,5)),IF(OR(COUNTIF(F19,{"*キャンセル*","*中止*","×","*に振*","希望*"})),0,SWITCH(LEN(F17),0,0,9,RIGHT(F17,4)-LEFT(F17,4),10,RIGHT(F17,5)-LEFT(F17,4),RIGHT(F17,5)-LEFT(F17,5))))
 +IF(COUNTIF(F24,"*-*")=1,SWITCH(LEN(F24),9,RIGHT(F9,4)-LEFT(F24,4),10,RIGHT(F24,5)-LEFT(F24,4),RIGHT(F24,5)-LEFT(F24,5)),IF(OR(COUNTIF(F24,{"*キャンセル*","*中止*","×","*に振*","希望*"})),0,SWITCH(LEN(F22),0,0,9,RIGHT(F22,4)-LEFT(F22,4),10,RIGHT(F22,5)-LEFT(F22,4),RIGHT(F22,5)-LEFT(F22,5))))
 +IF(COUNTIF(F29,"*-*")=1,SWITCH(LEN(F29),9,RIGHT(F9,4)-LEFT(F29,4),10,RIGHT(F29,5)-LEFT(F29,4),RIGHT(F29,5)-LEFT(F29,5)),IF(OR(COUNTIF(F29,{"*キャンセル*","*中止*","×","*に振*","希望*"})),0,SWITCH(LEN(F27),0,0,9,RIGHT(F27,4)-LEFT(F27,4),10,RIGHT(F27,5)-LEFT(F27,4),RIGHT(F27,5)-LEFT(F27,5))))
 +IF(COUNTIF(F34,"*-*")=1,SWITCH(LEN(F34),9,RIGHT(F9,4)-LEFT(F34,4),10,RIGHT(F34,5)-LEFT(F34,4),RIGHT(F34,5)-LEFT(F34,5)),IF(OR(COUNTIF(F34,{"*キャンセル*","*中止*","×","*に振*","希望*"})),0,SWITCH(LEN(F32),0,0,9,RIGHT(F32,4)-LEFT(F32,4),10,RIGHT(F32,5)-LEFT(F32,4),RIGHT(F32,5)-LEFT(F32,5))))
 +IF(COUNTIF(F39,"*-*")=1,SWITCH(LEN(F39),9,RIGHT(F9,4)-LEFT(F39,4),10,RIGHT(F39,5)-LEFT(F39,4),RIGHT(F39,5)-LEFT(F39,5)),IF(OR(COUNTIF(F39,{"*キャンセル*","*中止*","×","*に振*","希望*"})),0,SWITCH(LEN(F37),0,0,9,RIGHT(F37,4)-LEFT(F37,4),10,RIGHT(F37,5)-LEFT(F37,4),RIGHT(F37,5)-LEFT(F37,5))))
 +IF(COUNTIF(F44,"*-*")=1,SWITCH(LEN(F44),9,RIGHT(F9,4)-LEFT(F44,4),10,RIGHT(F44,5)-LEFT(F44,4),RIGHT(F44,5)-LEFT(F44,5)),IF(OR(COUNTIF(F44,{"*キャンセル*","*中止*","×","*に振*","希望*"})),0,SWITCH(LEN(F42),0,0,9,RIGHT(F42,4)-LEFT(F42,4),10,RIGHT(F42,5)-LEFT(F42,4),RIGHT(F42,5)-LEFT(F42,5))))

 計算式内で共通する文字列が非常に多いんですが、もっとコンパクトにできないものでしょうか?なおSWITCH関数はVBAを使っています。

< 使用 Excel:Excel2013、使用 OS:Windows10 >


>>計算式内で共通する文字列が非常に多いんですが、もっとコンパクトにできないものでしょうか?なおSWITCH関数はVBAを使っています。

何でしたら、全てVBAにすれば、数式は要りませんです。^^;
乱暴な発言でしたら、お許しを。
m(__)m
(隠居Z) 2023/01/18(水) 11:21:08


 コメントありがとうございます。
 VBAを使うしかないでしょうか?
 前々任者が作ったファイルなんですが、もう辞めてしまってVBA触れる人がいない状況です。
(ヒカゲ) 2023/01/18(水) 11:36:05

>> VBAを使うしかないでしょうか?
いえ
とんでもございません。只、私がVBA好きなだけです。
様々な方法が有ると存じます。私は数式は超苦手でして、
他の回答者様のお出ましを、引き続きお待ちくださいませ。
済みませんでした。
m(__)m
(隠居Z) 2023/01/18(水) 12:15:05

 数式だけだとアプローチの仕方が分からないので
 参照しているセルの値と
 求める結果
 をいくつかサンプル提示できませんか?
(稲葉) 2023/01/18(水) 12:22:48

  数式でやるにしても、VBAでやるにしても、どんな仕様が分からないととんでもないものが出来上がります。

  ヒカゲさん自身、どんな仕様(=やりたい条件)が分かっているんでしょうか?
  よく分からないが数式を読み解いて短くしてくれって言う事なんでしょうか?

  回答側は部外者ですからねぇ。
  具体的なサンプルもなければ、数式の意味の説明もないでは、ちょっと負担感が大なんですけど・・

  あと、ちょっと見なんですが、なぜ一行目だけ「F14」なのですか? 他の行は全て「F9」で統一されていますが・・

                            ↓
   =IF(COUNTIF(F14,"*-*")=1,SWITCH(LEN(F14),9,RIGHT(F14,4)-LEFT(F14,4),10・・
   +IF(COUNTIF(F19,"*-*")=1,SWITCH(LEN(F19),9,RIGHT(F9,4)-LEFT(F19,4),10・・
   +IF(COUNTIF(F24,"*-*")=1,SWITCH(LEN(F24),9,RIGHT(F9,4)-LEFT(F24,4),10・・

(半平太) 2023/01/18(水) 12:32:40


 コメントありがとうございます。
 これは従業員の勤務表になっていまして、F列の内容は次のようになっています。

  セルの内容            サンプル
 1タイトル              〇〇日程表
 2日付                  2023/1/10
 3曜日                  =F2(水曜日と表示)
 4氏名                  従業員A
 5勤務コード            801
 6勤務形態              A勤
 7備考1                 空欄
 8備考2                 空欄
 9勤務時間              9:00-15:30
 10顧客コード(1人目)  350
 11顧客名               B様
 12応対時間             9:30-10:30
 13応対内容             商談
 14応対時間(変更後)   9:00-10:00
 15顧客コード(2人目)  311
 16顧客名               C様
 17応対時間             10:15-11:15
 18応対内容             商談
 19応対時間(変更後)   空欄
 20顧客コード(3人目)  空欄
 21顧客名               空欄
 22応対時間             空欄
 23応対内容             空欄
 24応対時間(変更後)   空欄
 25顧客コード(4人目)  空欄
 26顧客名               空欄
 27応対時間             空欄
 28応対内容             空欄
 29応対時間(変更後)   空欄
 30顧客コード(5人目)  323
 31顧客名               D様
 32応対時間             12:00-13:30
 33応対内容             商談
 34応対時間(変更後)   12:00-13:00
 35顧客コード(6人目) 空欄
 36顧客名               空欄
 37応対時間             空欄
 38応対内容             空欄
 39応対時間(変更後)   空欄
 40顧客コード(7人目)  315
 41顧客名               E様
 42応対時間             14:30-15:30
 43応対内容             商談
 44応対時間(変更後)   空欄
 45応対時間合計         上の計算式(4:00と表示)

 F45で応対時間の合計を求めるんですが、応対時間に変更がある場合は変更後の時間で合計をします。

 >>なぜ一行目だけ「F14」なのですか? 他の行は全て「F9」で統一されていますが・・
 F9で統一している方が間違っていました。だいぶ前から間違ったまま使ってたみたいです(笑)
 正しい計算式は多分次のとおりです。

 =IF(COUNTIF(F14,"*-*")=1,SWITCH(LEN(F14),9,RIGHT(F14,4)-LEFT(F14,4),10,RIGHT(F14,5)-LEFT(F14,4),RIGHT(F14,5)-LEFT(F14,5)),IF(OR(COUNTIF(F14,{"*キャンセル*","*ショート*","×","*に振*","入院*"})),0,SWITCH(LEN(F12),0,0,9,RIGHT(F12,4)-LEFT(F12,4),10,RIGHT(F12,5)-LEFT(F12,4),RIGHT(F12,5)-LEFT(F12,5))))
 +IF(COUNTIF(F19,"*-*")=1,SWITCH(LEN(F19),9,RIGHT(F19,4)-LEFT(F19,4),10,RIGHT(F19,5)-LEFT(F19,4),RIGHT(F19,5)-LEFT(F19,5)),IF(OR(COUNTIF(F19,{"*キャンセル*","*ショート*","×","*に振*","入院*"})),0,SWITCH(LEN(F17),0,0,9,RIGHT(F17,4)-LEFT(F17,4),10,RIGHT(F17,5)-LEFT(F17,4),RIGHT(F17,5)-LEFT(F17,5))))
 +IF(COUNTIF(F24,"*-*")=1,SWITCH(LEN(F24),9,RIGHT(F24,4)-LEFT(F24,4),10,RIGHT(F24,5)-LEFT(F24,4),RIGHT(F24,5)-LEFT(F24,5)),IF(OR(COUNTIF(F24,{"*キャンセル*","*ショート*","×","*に振*","入院*"})),0,SWITCH(LEN(F22),0,0,9,RIGHT(F22,4)-LEFT(F22,4),10,RIGHT(F22,5)-LEFT(F22,4),RIGHT(F22,5)-LEFT(F22,5))))
 +IF(COUNTIF(F29,"*-*")=1,SWITCH(LEN(F29),9,RIGHT(F29,4)-LEFT(F29,4),10,RIGHT(F29,5)-LEFT(F29,4),RIGHT(F29,5)-LEFT(F29,5)),IF(OR(COUNTIF(F29,{"*キャンセル*","*ショート*","×","*に振*","入院*"})),0,SWITCH(LEN(F27),0,0,9,RIGHT(F27,4)-LEFT(F27,4),10,RIGHT(F27,5)-LEFT(F27,4),RIGHT(F27,5)-LEFT(F27,5))))
 +IF(COUNTIF(F34,"*-*")=1,SWITCH(LEN(F34),9,RIGHT(F34,4)-LEFT(F34,4),10,RIGHT(F34,5)-LEFT(F34,4),RIGHT(F34,5)-LEFT(F34,5)),IF(OR(COUNTIF(F34,{"*キャンセル*","*ショート*","×","*に振*","入院*"})),0,SWITCH(LEN(F32),0,0,9,RIGHT(F32,4)-LEFT(F32,4),10,RIGHT(F32,5)-LEFT(F32,4),RIGHT(F32,5)-LEFT(F32,5))))
 +IF(COUNTIF(F39,"*-*")=1,SWITCH(LEN(F39),9,RIGHT(F39,4)-LEFT(F39,4),10,RIGHT(F39,5)-LEFT(F39,4),RIGHT(F39,5)-LEFT(F39,5)),IF(OR(COUNTIF(F39,{"*キャンセル*","*ショート*","×","*に振*","入院*"})),0,SWITCH(LEN(F37),0,0,9,RIGHT(F37,4)-LEFT(F37,4),10,RIGHT(F37,5)-LEFT(F37,4),RIGHT(F37,5)-LEFT(F37,5))))
 +IF(COUNTIF(F44,"*-*")=1,SWITCH(LEN(F44),9,RIGHT(F44,4)-LEFT(F44,4),10,RIGHT(F44,5)-LEFT(F44,4),RIGHT(F44,5)-LEFT(F44,5)),IF(OR(COUNTIF(F44,{"*キャンセル*","*ショート*","×","*に振*","入院*"})),0,SWITCH(LEN(F42),0,0,9,RIGHT(F42,4)-LEFT(F42,4),10,RIGHT(F42,5)-LEFT(F42,4),RIGHT(F42,5)-LEFT(F42,5))))
(ヒカゲ) 2023/01/18(水) 15:08:00

 それと、応対時間(変更後)欄には変更後の時間を入力するのですが、「*キャンセル*」「*中止*」「*に振*」「希望*」と入力されていた場合は、その顧客の応対時間は合計に含めない、という計算式になっています。訂正した計算式は間違えて{"*キャンセル*","*ショート*","×","*に振*","入院*"}となっていますが無視してください。
(ヒカゲ) 2023/01/18(水) 15:15:42

 >だいぶ前から間違ったまま使ってたみたいです
 >正しい計算式は多分次のとおりです。
 どちらが本当なんでしょうね。
(?) 2023/01/18(水) 15:23:42

数式を短くしたい希望ですが、
何かお困りなんですかね?

長い数式が嫌、程度で、
自身のスキルがありません、というのであれば
そのまま使えばいいだけで。

自身で作り直したいのなら、
今の丸投げなアプローチはどうかな、と。

VBA関数のSWITCHのコードを提示して、
何してるんですか〜?なら分かりますが。
(tkit) 2023/01/18(水) 15:42:43


 困っていなければわざわざ質問したりしません。難しいようでしたら結構です。
(ヒカゲ) 2023/01/18(水) 16:11:20

 > 難しいようでしたら結構です。

 (隠居Z)さんの回答のように、
 VBAでやれば、スッキリです。
 合計時間を取得する関数にしてもいいでしょう。

 難しくありません。
(tkit) 2023/01/18(水) 17:01:17

 >困っていなければわざわざ質問したりしません。
 だから何で困るの。
 何で短くしたいの。

(VTG) 2023/01/18(水) 17:23:20


 時間かかるけど、回答しようと思ってるので、時間いただけないですか?
(稲葉) 2023/01/18(水) 17:48:10

 1.標準モジュールに下記コードを貼り付ける

 2. F45セルに下式を入力する(顧客コードの行から、最終行まで指定する)

    =timeWorked(F10:F44)

 '標準モジュールに貼り付けるVBA ↓

 Function timeWorked(r As Range)
     Dim Canc, i As Long, tm

     For i = 1 To r.Count Step 5
         With Application
             Canc = .Sum(.CountIf(r(i + 4, 1), Array("*キャンセル*", "*中止*", "*に振*", "*希望*")))
         End With

         If Canc = 0 Then
             If InStr(r(i + 4, 1), "-") Then
                 tm = Split(r(i + 4, 1), "-")
             ElseIf InStr(r(i + 2, 1), "-") Then
                 tm = Split(r(i + 2, 1), "-")
             End If

             If IsArray(tm) Then
                 timeWorked = timeWorked + TimeValue(tm(1)) - TimeValue(tm(0))
                 tm = Empty
             End If
         End If
     Next i
 End Function

(半平太) 2023/01/18(水) 17:50:46


 おおう、早い
 先生がいらっしゃったので検討やめますね
(稲葉) 2023/01/18(水) 18:24:57

 繰り返し使うパーツは名前定義にして、見かけ上短い数式にする案

 以下の名前定義は、F45セルを選択後、Ctrl+F3キー押下して出てくるダイアログから設定する。

  名前     参照範囲ボックスに入力する数式
  ALL           =F10:F44
  UpPos         =F8:F42
  ADJ           =IF(ISNUMBER(SEARCH("*-*",ALL)),ALL,UpPos)
  FIN           =FIND("-",ADJ)
  NGW           ={"キャンセル","中止","に振","希望"}
  対応合計      =SUM(IFERROR((MID(ADJ,FIN+1,15)-LEFT(ADJ,FIN-1))*NOT(MMULT(N(ISNUMBER(FIND(NGW,ALL))),{1;1;1;1}))*(MOD(ROW(ALL),5)=4),0))

 F45セルには、以下の数式を入力する
  =対応合計

(半平太) 2023/01/19(木) 16:31:07


 コメントありがとうございます。
 名前定義の方法、凄いですね。こういうやり方が知りたかったんですが、質問の仕方も分からずただ短くできないかと聞いてしまってすみません。MMULT関数というのも初めて知りました。
 サンプルにはF列の従業員Aしか記載していませんが、G列やH列には従業員Bや従業員Cがずーっと続いています。
 名前定義の方法で、このF45セルの数式をG45とかJ45セル(別の従業員のセル)にコピーしても使えるようにはならないでしょうか?
 私のスキルではできる事が一つもなく、丸投げで申し訳ありません。
(ヒカゲ) 2023/01/19(木) 17:16:42

 >私のスキルではできる事が一つもなく、
 名前定義の方法を提示されたんだからそれを応用してみる気がないの。
 いつまでたったもスキルアップしないですよ。
 面倒くさいから完成品を欲しいということですか。

(VTK) 2023/01/19(木) 17:25:50


 >名前定義の方法で、このF45セルの数式をG45とかJ45セル(別の従業員のセル)にコピーしても使えるようにはならないでしょうか?

 まだ回答をお待ちしていますのでよろしくお願いいたします。
(ヒカゲ) 2023/01/26(木) 13:36:04

ヒカゲさん、まずその方法を試されてみてはいかがでしょうか。
(お邪魔します) 2023/01/26(木) 15:42:39

 あれ? まだ続いてたんですか?

 >名前定義の方法で、このF45セルの数式をG45とかJ45セル
 >(別の従業員のセル)にコピーしても使えるようにはならないでしょうか?

 F45セルが旨くいっていて、その右方向にあるものも同じように求めたいのであれば、
 単に右にコピーするだけでいいハズです。(その様につくりましたので)

 つまり、それらのセルも =対応合計 だけで算出されます。

(半平太) 2023/01/26(木) 16:38:41


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.