[[20181009092810]] 『続き』(ごま塩) ページの最後に飛ぶ

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

 

『続き』(ごま塩)

[[20181005193537]]

以前質問したごま塩と申します。何度も失礼します。

以前、半平太さんに下の式を作って頂きました。本当に感謝しています。

(1) A14セル =IFERROR(AGGREGATE(15,6,$AW$1:$CA$1/($AW$3:$CA$3={"A";"C"}),1),"")

(2) A16セル =IF(A14="",""(AGGREGATE(15,6,$AW$1:$CA$1/(($AW$4:$CA$4={"B";"D";"E"})*($AW$1:$CA$1>=$A$14)),(ROW(A1)+2)/3)))

(3) A17セル =IFERROR(AGGREGATE(15,6,$AW$1:$CA$1/(($AW$3:$CA$3={"A";"C"})*($AW$1:$CA$1<=$A$14-WEEKDAY($A$14,2)+7)),(ROW(A4)+2)/3),"")

(4) E14セル =IF(A14="","",HLOOKUP(A14,$AW$1:$CA$3,3,FALSE))
(5) E16セル =IF(A16="","",HLOOKUP(A16,$AW$1:$CA$4,4,FALSE))

この数式を一週間の範囲に区切って、つまり、
一週目[AW〜]
二週目[BD〜]
三週目[BK〜]
四週目[BR〜]
という形にして使用していました。

行 _AV_ ___BB___ __BC___ _ BD___ _ BE___ __BF___ ___BG___

  1 日付 10月6日  10月7日  10月8日  10月9日  10月10日  10月11日
  2 曜日 土       日       月       火       水       木 
  3 開始 A                 A                A         C                         
  4 終了                   B        B                 E   

上の表の時、6日から8日、AからBとなるのですが、次の週、BDから始めると8日から8日のAからBになります。
8日から9日のAからBにできないでしょうか。
8日のBは前の週、6日から8日のA、Bになるのでここでまた8日のBが入るとおかしくなります。

それ以降もずれて、10日から9日のA、B。11日から11日のC、Eとなります。

同じ日のAからBという時もありますので、一度選択されたA、Bは使用できないということは可能でしょうか。
ご教授お願いします。

そしてこれはまた別件なのですが(すみません)、X列(X14:AA16セル結合を一ブロックで六個)にリストが入っています。
例えばX14セルのリストで休暇を選択すると14行と16行を条件付き書式で赤字にしたいのですが、X列がセル結合してるため14行目しか赤字にならないです。
14行から16行をまとめて赤字にできる方法も併せて教えてください。

本当にすみません、、、

< 使用 Excel:Excel2010、使用 OS:Windows7 >


 >次の週、BDから始める

 (1)1週目の「A14,A16,A17」に相当する数式は、
   どこのセルにどんな数式を入れているんですか?

 (2) 条件付き書式の条件式
         ↓
    =AND(INDEX(X:X,FLOOR(ROW()-2,3)+2)="休暇",MOD(ROW(),3))

(半平太) 2018/10/09(火) 12:23


ありがとうございます。

(1)1週目の「A14,A16,A17」に相当する数式は、

   どこのセルにどんな数式を入れているんですか?

 同じシートをコピーし、一周目〜五週目という名前をつけています。式は同じ場所です。

(ごま塩) 2018/10/09(火) 13:12


遅くなりました。
 (2) 条件付き書式の条件式
         ↓
    =AND(INDEX(X:X,FLOOR(ROW()-2,3)+2)="休暇",MOD(ROW(),3))

A14からAA31を選択して条件付き書式に入れて見たところ、X14のリストから休暇を選択するとA14とE14、A16のセルのみ赤字になりました。

言葉が足らずすみません。

AからXまでの範囲(セル結合含めるとAA)全てを赤字にしたいです。

(ごま塩) 2018/10/09(火) 13:29


 >同じシートをコピーし、

 2週目以降は別のシートですか・・

 (1) 2週目のシートには「どんなデータ」があって、「どんな数式」を入れたんですか?

       コピーしたと言っても、1週目と全く同じものじゃないですよね?

  (2) 条件付き書式の条件式 (Xの前に$マークを追加)
          ↓
     =AND(INDEX($X:$X,FLOOR(ROW()-2,3)+2)="休暇",MOD(ROW(),3))

(半平太) 2018/10/09(火) 13:53


範囲指定の$AW$1:$CA$1のAWの部分をそれぞれ

二週目[$BD$1:$CA$1]
三週目[$BK$1:$CA$1]
四週目[$BR$1:$CA$1]
五週目[$BY$1:$CA$1]

に変えています。他はデータも全て同じです。
(ごま塩) 2018/10/09(火) 14:05


遅くなりましたが、条件付き書式は色付け出来ました。ありがとうございます。
(ごま塩) 2018/10/09(火) 14:22

 2週目シートのA16セルへ下式を入力してください。

 =IF(A14="","",(AGGREGATE(15,6,$AW$1:$CA$1/($AW$4:$CA$4={"B";"D";"E"}),SUM(COUNTIFS($AW$1:$CA$1,"<="&$A14,$AW$3:$CA$3,{"A","C"})))))

 上記の数式は、データ範囲全体を見ていますので、範囲をAWからBDへ調整変更しないようにしてください。

(半平太) 2018/10/09(火) 15:30


ありがとうございます。

A16に入れてみましたが、変化がありません。
確認ですが、二週目のシートはA16以外は範囲はそのままBDから変えなくても大丈夫ですか。
いろいろとご迷惑おかけします。
(ごま塩) 2018/10/09(火) 15:58


 >二週目のシートはA16以外は範囲はそのままBDから変えなくても大丈夫ですか。 

 大丈夫です。

 念の為、二週目のシートの3セルの数式をアップします。 

 (1) A14セル =IFERROR(AGGREGATE(15,6,$BD$1:$CA$1/($BD$3:$CA$3={"A";"C"}),1),"")

 (2) A16セル =IF(A14="","",(AGGREGATE(15,6,$AW$1:$CA$1/($AW$4:$CA$4={"B";"D";"E"}),SUM(COUNTIFS($AW$1:$CA$1,"<="&$A14,$AW$3:$CA$3,{"A","C"})))))
 (3) A17セル =IFERROR(AGGREGATE(15,6,$BD$1:$CA$1/(($BD$3:$CA$3={"A";"C"})*($BD$1:$CA$1<=$A$14-WEEKDAY($A$14,2)+7)),(ROW(A4)+2)/3),"")

(半平太) 2018/10/09(火) 16:47


すみません、やはり変化がないです。

最初の数式でエラーが出るのは、週始めのABが同じ日に重なっている場合です。その週は全てずれてしまいます。
新しい数式を入れると、ずれている週はは変わらず、逆に正常な週もずれてしまいました。

シートのデザインに無理がありますかね・・・。

無理難題言ってすみません、、、
(ごま塩) 2018/10/09(火) 17:16


おはようございます。
あれからいろいろ考えましたが、やはり週始めに開始と終了が重なっている時の処理が思い浮かばないです。
同じ日に開始と終了があるパターンも存在しますので、それと区別をつける方法はありますか?

丸投げで申し訳ないですが、前の週で範囲に含まれたら次の週では範囲に入らない、ということは可能でしょうか。
よろしくお願いいたします。
(ごま塩) 2018/10/10(水) 10:06


 あれ? 私の方は問題ないことを確認しているんですが・・。

 簡単なテストなので、絶対か? と言われるとそんなに自信はないですけども。(下図ご参照)

 旨く行かないと言う具体的なデータをアップして貰えば、こちらで、もう一度テストしますけど。

 <2週目 テスト用データ>
  行 _AV_ ___AW___ ___AX___ ___AY___ ___AZ___ ___BA___ ___BB___ ___BC___ ___BD___ ___BE___ ___BF___ ___BG___ ___BH___ ___BI___ ___BJ___
   1 日付 10月1日  10月2日  10月3日  10月4日  10月5日  10月6日  10月7日  10月8日  10月9日  10月10日 10月11日 10月12日 10月13日 10月14日
   2 曜日 月       火       水       木       金       土       日       月       火       水       木       金       土       日      
   3 開始 a                          A                 C        a                 A                 C                 a                
   4 終了 b                                   b                 B                 d                 B        E                 b       

 <2週目 結果図>
  行 _____A_____ _B_ _C_ _D_ _E_
  14 2018/10/9               A  
  15                            
  16 2018/10/11              B  
  17 2018/10/11              C  
  18                            
  19 2018/10/12              E  
  20 2018/10/13              a  
  21                            
  22 2018/10/14              b  

(半平太) 2018/10/10(水) 10:17


  これ、忘れてないですね? 
   ↓
 > A16:A18の3つのセル(下の空白セルを含む)を一括選択して、丸ごと31行目までフィルコピー

(半平太) 2018/10/10(水) 10:20


ありがとうございます。
早急に確認します。ただ、現在パソコンに触れない状態ですので、返信は遅くなります。

(ごま塩) 2018/10/10(水) 11:16


お待たせしました。

二週目はこうなります。

行 _AV_ ___AW___ ___AX___ ___AY___ ___AZ___ ___BA___ ___BB___ ___BC___ ___BD___ ___BE___ ___BF___ ___BG___ ___BH___ ___BI___ ___BJ___

   1 日付 10月8日  10月9日  10月10日  10月11日  10月12日  10月13日  10月14日  10月15日 
   2 曜日 月       火        水       木       金        土       日       月    
   3 開始          A                          C               a                    
   4 終了 B                                   b          B                                     
 <2週目 結果図>
  行 _____A_____ _B_ _C_ _D_ _E_
  14 2018/10/9               A  
  15                            
  16 2018/10/8              B  
  17 2018/10/12              C  
  18                            
  19 2018/10/12              b  
  20 2018/10/14              a  
  21                            
  22 2018/10/13              b

(ごま塩) 2018/10/10(水) 13:53


 >他はデータも全て同じです。

 と言いましたよね?

 私はもう知らない。

(半平太) 2018/10/10(水) 14:22


すみません、私の説明不足で、ご迷惑おかけしました。

まず、この表ですが、勤務表でして、毎月内容が変わります。
全てデータは同じというのは、AWからの範囲はシート全て変わらないという認識で伝えていました。申し訳ありません。

データは何月何日のいつからいつまでという表がありまして、今までそれを個人ごと用紙に記入していましたが、それを表を張り付けるだけで自動入力できるようにしたい、というのが趣旨です。

先程のデータがはじめのと違ったのは、初めのデータは、こういうことをしたいという説明のために作ったもので、今回のは実際の勤務表から引用していました。横着して申し訳ないです。

本当にご迷惑おかけしました。
もしよろしければご協力お願いいたします。
(ごま塩) 2018/10/10(水) 16:12


 >先程のデータがはじめのと違ったのは、初めのデータは、こういうことをしたいという説明のために作ったもので、
 >今回のは実際の勤務表から引用していました。横着して申し訳ないです。 

 はじめと違うのは、何も問題ありません。
 テストは色々なケースのデータを使うので当然です。

 問題なのは、第二週のシートに第一週のデータが書いてない事です。

 初週からの全データが無かったら、「終了」が前の週からの繰り越し分なのか、
 今週発生した分なのか判定できないですよ。

 全部マクロでやった方がいいんじゃないですか。

 それなら初週からシートを全部見に行くのも
 そんなに抵抗ないですから(誰かが書いてくれますよ)。

(半平太) 2018/10/10(水) 17:15


〉問題なのは、第二週のシートに第一週のデータが書いてない事です。

申し訳ありませんでした。月曜から始めないといけないと思っていました。
例えば、金曜日から表を始めて、月曜から参照、前の週の終わりがあるかないかを判断、ということも可能でしょうか。
(ごま塩) 2018/10/10(水) 17:24


また新しく質問を作成します。
ありがとうございました。
先程(ようやく)気がつきましたが、先週のデータが入っていないと指摘された表はミスでした。スマホで入力しているため、横長の表が入らず二週目の部分だけ記載し、また列も二週目のBDに合わせるのを忘れていたため、あのような形の違うデータになりました。
今まで気がつかず申し訳ないです。
これまでありがとうございました。また機会があればよろしくお願いいたします。

(ごま塩) 2018/10/10(水) 19:19


半平太さん、申し訳ありません。もしよろしければ次のことについて回答をお願いしたいのですが。

(1)日付入力のA列はAからDの、開始、終了のE列はEからHの結合セル
(2)最初の書き込みで、
〉開始には2種類、種類は5種類あります。
と書きましたが、終了が5種類、の間違いです。

上の二つは数式に影響はないと(勝手に)思い込み、訂正をしませんでした(すみません・・・)
自分でも2項目を書き込んだ方の状態にして検証し、変化がないことを確認しましたが、あまり自信がなく・・・。

御手数お掛けしますが、上の二つの状態が半平太さんの数式に影響があるのか知りたいです。
(未だ半平太さんのテストした正常な状態を再現することができないです)

よろしくお願いいたします。
(ごま塩) 2018/10/12(金) 20:00


 >(未だ半平太さんのテストした正常な状態を再現することができないです) 
 済みません。一部ミスがありましたので、数式を見直しました。 m(__)m

 >上の二つの状態が半平太さんの数式に影響があるのか知りたいです。 
 影響はないです。 
 私も、5種類と言われたのに、3種類で数式を作ってしまったですが、訂正しなかったです。

 一週目
 (1) A14セル =IFERROR(AGGREGATE(15,6,$AW$1:$CA$1/($AW$3:$CA$3={"A";"C"}),1),"")
 (2) A16セル =IF(A14="","",(AGGREGATE(15,6,$AW$1:$CA$1/($AW$4:$CA$4={"B";"D";"E"}),SUM(COUNTIFS($AW$1:$CA$1,"<="&A14,$AW$3:$CA$3,{"A";"C"})))))
 (3) A17セル =IFERROR(AGGREGATE(15,6,$AW$1:$CA$1/(($AW$3:$CA$3={"A";"C"})*($AW$1:$CA$1<=$A$14-WEEKDAY($A$14,2)+7)),(ROW(A4)+2)/3),"")

 二週目
 (1) A14セル =IFERROR(AGGREGATE(15,6,$BD$1:$CA$1/($BD$3:$CA$3={"A";"C"}),1),"")
 (2) A16セル =IF(A14="","",(AGGREGATE(15,6,$AW$1:$CA$1/($AW$4:$CA$4={"B";"D";"E"}),SUM(COUNTIFS($AW$1:$CA$1,"<="&A14,$AW$3:$CA$3,{"A";"C"})))))
 (3) A17セル =IFERROR(AGGREGATE(15,6,$BD$1:$CA$1/(($BD$3:$CA$3={"A";"C"})*($BD$1:$CA$1<=$A$14-WEEKDAY($A$14,2)+7)),(ROW(A4)+2)/3),"")

 注1
 (1)の数式と(3)の数式は、AWがBD(新範囲)に変更されただけです。
 (2)の数式は、どちらのシートも全く同じものです。

 注2
 一週目のシートと二週目のシートの開始・終了データは、全く同じものとします。

 しかし、現実問題として、2週目のシートに1週目のデータをそっくり入れるというのは
 考えにくいですね。(人間はやりたがらない)

 なので、もし二週目のシートには一週目のデータを入れないとした場合は、
 マクロで対応する方がいいです。数式じゃ面倒です。

 「作成依頼すんな」なんてレスが入ったようですが、そんなの無視してください。
 やりたくなければスルーすりゃいいことです。

 まぁ、作成依頼に応じない人の方が多いのは事実ですが、老人大国ですからねぇ、
 暇な人 や 困っている人を助けたいと思う人は幾らでも居ます。

 回答者も色々な理由でレスを付けているんです。
 ただ、人間、感情の動物ですから、頼み方、持って行き方に左右される面も多分にあります。

 私なら本来マクロで対応しますが、如何せんここに至る過程がすったもんだ、し過ぎです。
 初めからきっちり説明を受けていれば、数式なんて作りませんよ。

 でも行きがかりで数式を作った。その上でそれを没にしてマクロなんて作る気起きません。
 幾ら暇でも、二度手間な事、無駄な事は好まないんです。

(半平太) 2018/10/12(金) 22:19


半平太さん、ありがとうございます。
都合があり返信は明日になると思います。
ご迷惑おかけしました。
(ごま塩) 2018/10/12(金) 22:37

おはようございます。

  行 _AV__AW__AX__AY__AZ__BA__BB__BC__BD__BE__BF__BG__BH__BI__BJ__BK
   1 日付 1   2  3  4   5  6  7   8  9  10  11 12  13 14 15
   2 曜日 月   火  水  木   金  土  日   月  火  水   木  金   土 日  月
   3 開始      A           C      A           C       A          C 
  4 終了 B                B  B               B    B              B                      

1週目

  行 _____A_____ _B_ _C_ _D_ _E_
  14 2018/10/2               A  
  15                            
  16 2018/10/1              B  
  17 2018/10/5              C  
  18                            
  19 2018/10/5              B 
  20 2018/10/7              A  
  21                            
  22 2018/10/6              B  

 2週目
  行 _____A_____ _B_ _C_ _D_ _E_
  14 2018/10/10               C  
  15                            
  16 2018/10/10              B  
  17 2018/10/12              A  
  18                            
  19 2018/10/11              B  

結果が上のようになります。終了の日付が開始の真下、空白なら一日前になります。
コピペではなく手動入力のため、式の打ち間違えの可能性があるので確認します。

(ごま塩) 2018/10/13(土) 08:49


 全データを見渡して「終了」が先に来るケースは想定していないです。

 そのテストデータで、1日の「B」が2日にあったらどう判断するんですか?
 先週分とするのか、当週分とするのか、その区別の仕方を先ず説明してください。

(半平太) 2018/10/13(土) 09:19


一週間で区切ろうが月で区切ろうが、前の週のデータがなければ判断できないのは当然ですね・・・。

上のテストデータの場合は、1日のBは先週の分となります。

1日のBを消去したところ、正常に作動しました。
前の週のデータを載せたところで同じように判断にはその前の週のデータが必要になる・・・。無限ループですね
(ごま塩) 2018/10/13(土) 09:41


 >無限ループですね

 開始に対応する「終了」が必ず表にある、と言う前提なら、
 「終了の数」-「開始の数」でその差が前週以前のものと判断はできます。

 その前提だと、今度は表のうしろ(次月のデータ)が気になります。

 いままでの話では、翌月分(1週間分でいいでしょうけど)は表に無いですよね?

(半平太) 2018/10/13(土) 10:06


次の週はないです。
一週間分だけ、つまりCH列まで伸ばして次週を入れれば大丈夫ですか?
(ごま塩) 2018/10/13(土) 10:21

 >一週間分だけ、つまりCH列まで伸ばして次週を入れれば大丈夫ですか?

 厳密に言うと、当月発生した「開始」に対応する「終了」が翌月に流れ込む場合、
 その「終了」が翌月第1週に存在する必要があります。

 ※翌月第1週に発生する「開始」データは要らない。邪魔なだけ。
   もし書いたら、それに対応する「終了」をチャンと書かなければない。
  そうしないと、「終了の数」-「開始の数」の差が前週以前のものと判断できなくなります。
  この理屈、分かりますよね?

(半平太) 2018/10/13(土) 10:38


間違っていたらすみません。
例えば上のテストデータの場合、月末はCE列、28日の日曜日に切って、29日月曜からを翌月の第一週分とする。
この認識で合っていますか?
(ごま塩) 2018/10/13(土) 10:52

 「月」にこだわってしまいましたが、考え方としては

  x曜日(正規は月曜日)から日曜日までで一区切りですから、それが何月に当たるかは問題じゃないです。

 「ある開始」に対応する「終了」が必ず存在することが必要と言うだけです。
  ※逆の「終了」に対応する「開始」は無くても対応できると言うことです。

 また、いちいち「終了」と書かなくても、
 「翌月に流れ込んだ終了は何個だよ」と言うデータ(通常「1」)をどっかに書いても簡単に対応できます。
 例えば、表の最終列の右隣に1と書く。それなら数式の修正は簡単ですし、表の修正もイージーでしょう。

 >月末はCE列、
 ちょっと引っかかったんですが、いままでだと10/31はCA列だったと思うんですけど、範囲が変わったんですか?
 ここがあやふやだと二度手間になるんで、嫌なんですけどねぇ・・

(半平太) 2018/10/13(土) 11:46


すみません。28日がCE列に当たるという意味です。分かりにくくてすみません。
(ごま塩) 2018/10/13(土) 11:50

 >28日がCE列に当たるという意味です。

 いえ、いままで表はCAまでと認識していたのですけども、
 実際のデータ範囲は「いままで」何だったんですか?

(半平太) 2018/10/13(土) 12:01


・・・すみません、完全に見間違えていました。28日はBXですね。
今まで通りCAまでです。
申し訳ありません。
(ごま塩) 2018/10/13(土) 12:04

 CB4に翌月繰越数を入れる案 (注:「翌月繰越終了数」と「前月から繰越終了数」は同じ意味になる)

  行  ___CA___  ____CB____
   1  10月31日  翌月終了数
   2  水                  
   3                      
   4                     1

 第1週目シートも、第2週目シートもA16セルはどちらも同じ

 A16セル =IF(A14="","",(AGGREGATE(15,6,$AW$1:$CA$1/($AW$4:$CA$4={"B";"D";"E";"F";"G"}),SUM(COUNTIFS($AW$1:$CA$1,"<="&A14,$AW$3:$CA$3,{"A";"C"}))+$CB$4)))

(半平太) 2018/10/13(土) 13:21


遅くなって申し訳ないです。希望通りに作動しました。
こんなに長く付き合わせて申し訳ありません。下手くそな説明を汲み取っていただき、本当に感謝してます。
ありがとうございました。。
(ごま塩) 2018/10/13(土) 15:14

コメント返信:

[ 一覧(最新更新順) ]


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