[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『この計算式を短くできますか?』(ヒカゲ)
セル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 >
何でしたら、全てVBAにすれば、数式は要りませんです。^^;
乱暴な発言でしたら、お許しを。
m(__)m
(隠居Z) 2023/01/18(水) 11:21:08
コメントありがとうございます。 VBAを使うしかないでしょうか? 前々任者が作ったファイルなんですが、もう辞めてしまってVBA触れる人がいない状況です。 (ヒカゲ) 2023/01/18(水) 11:36: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
あれ? まだ続いてたんですか?
>名前定義の方法で、このF45セルの数式をG45とかJ45セル >(別の従業員のセル)にコピーしても使えるようにはならないでしょうか?
F45セルが旨くいっていて、その右方向にあるものも同じように求めたいのであれば、 単に右にコピーするだけでいいハズです。(その様につくりましたので)
つまり、それらのセルも =対応合計 だけで算出されます。
(半平太) 2023/01/26(木) 16:38:41
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.