- ベストアンサー
売上重複を避けるための質問
kagakusukiの回答
- kagakusuki
- ベストアンサー率51% (2610/5101)
回答No.2です。 祭日シートの祝日と振替休日の表を関数を使って自動的に作成する方法です。 但し、春分の日と秋分の日の日付に関しては、国立天文台が天体観測を行って得た地球の軌道や地軸の傾き等のデータを基にして、閣議によって決定されるものであり、それらの天体観測のデータは、多数の惑星と太陽の重力的な相互作用によって変動するため、その様な値を求める事は方程式を使って行う事は出来ず、Excelの関数を使って求めようとしましても、どうしても日付にずれが生じてしまう年が出て来ますので、春分の日とと秋分の日に関してだけは日本政府が発表した日付を手作業によって入力する必要があります。 それと、祝日の決め方が現在の方式になったのは2007年からであり、2006年以前には祝日の決め方が現在とは異なっていますので、以下の方法で祝日や振替休日を求める事が出来るのは、2007年以降の年に関してのみである事をお断わりしておきます。 まず、祭日シートのA列の書式の表示形式を[日付]にして下さい。（回答No.2の添付画像では、[ユーザー定義]のyyyy/m/d aaaにしております） 次に、祭日シートのD6セルに「元旦」と入力して下さい。 次に、祭日シートのA6セルに次の関数を入力して下さい。 AND(ISNUMBER((Sheet1!$C$2&”/1/1”)+0),Sheet1!$C$2>2006),DATE(Sheet1!$C$2,1,1),”“) 次に、祭日シートのD7セルに「正月」と入力して下さい。 次に、祭日シートのA7セルに次の関数を入力して下さい。 AND(ISNUMBER((Sheet1!$C$2&”/1/1”)+0),Sheet1!$C$2>2006),DATE(Sheet1!$C$2,1,2),”“) 次に、祭日シートのD8セルに「正月」と入力して下さい。 次に、祭日シートのA8セルに次の関数を入力して下さい。 AND(ISNUMBER((Sheet1!$C$2&”/1/1”)+0),Sheet1!$C$2>2006),DATE(Sheet1!$C$2,1,3),”“) 次に、祭日シートのD9セルに「振替」と入力して下さい。 次に、祭日シートのA9セルに次の関数を入力して下さい。 ISNUMBER(1/(WEEKDAY(A8)=1)),A8+1,”“) 次に、祭日シートのD10セルに「成人」と入力して下さい。 次に、祭日シートのA10セルに次の関数を入力して下さい。 AND(ISNUMBER((Sheet1!$C$2&”/1/1”)+0),Sheet1!$C$2>2006),DATE(Sheet1!$C$2,1,14-WEEKDAY(DATE(Sheet1!$C$2,1,0),3)),”“) 次に、祭日シートのD11セルに「建国」と入力して下さい。 次に、祭日シートのA11セルに次の関数を入力して下さい。 AND(ISNUMBER((Sheet1!$C$2&”/1/1”)+0),Sheet1!$C$2>2006),DATE(Sheet1!$C$2,2,11),”“) 次に、祭日シートのD12セルに「振替」と入力して下さい。 次に、祭日シートのA12セルに次の関数を入力して下さい。 ISNUMBER(1/(WEEKDAY(A11)=1)),A11+1,”“) 次に、祭日シートのD13セルに「春分」と入力して下さい。 次に、祭日シートのD14セルに「振替」と入力して下さい。 次に、祭日シートのA14セルに次の関数を入力して下さい。 ISNUMBER(1/(WEEKDAY(A13)=1)),A13+1,”“) 次に、祭日シートのD15セルに「昭和」と入力して下さい。 次に、祭日シートのA15セルに次の関数を入力して下さい。 AND(ISNUMBER((Sheet1!$C$2&”/1/1”)+0),Sheet1!$C$2>2006),DATE(Sheet1!$C$2,4,29),”“) 次に、祭日シートのD16セルに「振替」と入力して下さい。 次に、祭日シートのA16セルに次の関数を入力して下さい。 ISNUMBER(1/(WEEKDAY(A15)=1)),A15+1,”“) 次に、祭日シートのD17セルに「憲法」と入力して下さい。 次に、祭日シートのA17セルに次の関数を入力して下さい。 AND(ISNUMBER((Sheet1!$C$2&”/1/1”)+0),Sheet1!$C$2>2006),DATE(Sheet1!$C$2,5,3),”“) 次に、祭日シートのD18セルに「みどり」と入力して下さい。 次に、祭日シートのA18セルに次の関数を入力して下さい。 AND(ISNUMBER((Sheet1!$C$2&”/1/1”)+0),Sheet1!$C$2>2006),DATE(Sheet1!$C$2,5,4),”“) 次に、祭日シートのD19セルに「こども」と入力して下さい。 次に、祭日シートのA19セルに次の関数を入力して下さい。 最後に、祭日シートのA13セルに春分の日の日付を、祭日シートのA24セルに秋分の日の日付を、それぞれ手入力して下さい。 以上です。
関連するQ&A
- 祝日 土日を除いた合計の出し方
いつもお世話になります。 WINDOWS7 EXCELL2010です。 平日 土日 祝日の売り上げの合計を出したいのですが、 添付図の 「G19」で困りました。 この「G19」は祝日です。 祝の列の J19 に表示できるようにしたいのですがご指導いただけませんでしょうか。 他の方法または修正がありましたら併せてご教示ください。 参考 A6 =IF(ISNA(VLOOKUP(B6,祭日!$A$6:$D$53,4,FALSE)),"",VLOOKUP(B6,祭日!$A$6:$D$53,4,FALSE)) ※ ○は祝日です。 B6 =TEXT($D6,"yyyymmdd") G6 =IF(F6="","",SUMPRODUCT((WEEKDAY(D6,2)<6)*F6)) H6 =IF(F6="","",(WEEKDAY(D6)=7)*(COUNTIF(祭日!$A$6:$B$53,D6)=0)*F6) I6 =IF(F6="","",(WEEKDAY(D6)=1)*(COUNTIF(祭日!$A$6:$B$53,D6)=0)*F6) J6 わかりません
- ベストアンサー
- オフィス系ソフト
- エクセル 平日と土日祝の時間計算表示
下記のようなエクセルを作りたいのですが、 祝日の7月15日の土日祝残業が、平日の方に表示されてしまいます。 休日表は、別シートでＳｅｅｔ２のＢ２～Ｅ２１に作成しています。 Ａセルに、日付 Ｂセルに、始業時間 Ｃセルに、終業時間 Ｄセルに、休憩時間として、 Ｅセルに、実労時間 =C2-B2-D2 Ｆセルに、平日労働時間 =IF(OR(WEEKDAY(A2,2)>5,COUNTIF(ｓｈｅｅｔ2!B$2:E$21,A2)),"",(E2)) Ｇセルに、土日祝労働時間 =IF((F2<>""),"",(E2)) Ｈセルに、平日残業時間 =IF(OR(WEEKDAY(A2,2)>5,COUNTIF(ｓｈｅｅｔ2!$B$2:$E$2,A2)),"",(E2-TIME(8,0,0))) Ｉセルに、土日祝残業時間 =IF(OR(WEEKDAY(A2,2)<6,COUNTIF(ｓｈｅｅｔ2!$B$2:$E$2,A2)),"",(E2-TIME(8,0,0))) を入れてみたのですが・・・ 祝日の残業時間のところがうまく表示されません。 (WEEKDAY(A2,2)<6 から COUNTIF(ｓｈｅｅｔ2!$B$2:$E$2,A2) 除いた日を""にすれば よいのかなぁと試行錯誤しましたがうまくできませんでした。 そのような式を教えて頂けますか？ もしくは他に適切な式があったら教えてください。 宜しくお願い申し上げます。
- 締切済み
- その他(Windows)
- Excel VBA VLOOKUP の書き方
次の関数をVBAで書くにはどのように記述すればよいでしょうか？ =IF(ISNA(VLOOKUP(B2,Sheet2!A2:B9,2,FALSE)),"",VLOOKUP(B2,Sheet2!A2:B9,2FALSE)) よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- VLOOKUPのエラーについて
教えて下さい。 =IF(ISERROR(VLOOKUP($B3,Sheet2!$A$2:$D$35,4,FALSE)),"",VLOOKUP($B3,Sheet2!$A$2:$D$35,4,FALSE))+IF(ISERROR(VLOOKUP($B3,Sheet3!$A$2:$C$50,3,FALSE)),"",VLOOKUP($B3,Sheet3!$A$2:$C$50,3,FALSE)) という数式を使っています。 最初のIF文(Shee2）については有効なのですが、それならと、欲張って他のシートも参照できるように、＋の後にIF文（Sheet3）を追加したら#VALUE!が表示されます。 何がいけないのでしょうか？
- 締切済み
- オフィス系ソフト
- Excelの数式の簡素化について
=if(isna(VLOOKUP(R1C1,SOMERANGE,2,false)), "", VLOOKUP(R1C1,SOMERANGE,2,false)) =if(sum(SOMERANGE), sum(SOMERANGE), sum(OTHERRANGE)) のように、同じ式を数箇所に含む場合、無駄だなあと思いつつ、いつも仕方なく使ってますが、 もっと式を簡単にする方法はないでしょうか。 これらの式に対する具体例でも構いませんが、一般的にどんな方法が考えられますよ、 というアドバイスをいただけるととてもありがたいです。
- ベストアンサー
- オフィス系ソフト
- 日曜日&祝日と隔週の土曜日を除く日に”営業”
いつもお世話になります。 WIN7 EXCELL2010 です。 現在は土日及び祝日を除く日は”営業”と表示することは下記の数式(*1)で何とかできています。 しかしこの関数ですとすべての土曜日が”営業”と表示されます。 ご指導を仰ぎたいのは、 1 3週目の土曜日も”営業”になるような数式にしたいのですがご教示いただけませんか。 よろしくお願いします 参照図で言うと下記のようにしたいです。 B6 “営業” 1週目の土曜日 P6 “営業” 3週目の土曜日 *参照図にはないが参考に I6 “休日” 2週目の土曜日 *参照図にはないが参考に W6 “休日” 4週目の土曜日 *参照図にはないが参考に 条件付書式 B6 =AND(OR(ROUNDUP(DAY(B4)/7,0)=2,ROUNDUP(DAY(B4)/7,0)=4),WEEKDAY(B4)=7) B6 =IF(OR(WEEKDAY(B4)=7,WEEKDAY(B4)=1,AG3="○"),"","営業") * ↑この数式ですと B6 は休日になります。 参考に B2 =TEXT(B4,"yyyymmdd") B3 =IF(ISNA(VLOOKUP(B2,祭日,3,FALSE)),"",VLOOKUP(B2,祭日,3,FALSE)) 参照図を11月度にしたのは1 3週目の土曜日 休日が表示されているので他には特に意図はありません。
- ベストアンサー
- Excel（エクセル）
- 数式が入ったセルで値がないときは詰めたい
いつもお世話になります。 WINDOWS7 EXCELL2010です。 今回ご指導を戴きたいのは赤枠内です。 A Bは手入力します。 その結果、C D E Fの各列には下記の数式で表示されます。 例えば、 E5 E8 E9 の各列のように空白が出るので「見易く」 したいため詰めるのにどんな関数を入れればできますか。 添付画像で各列の説明です A 手入力 B 手入力 C =IF($B2="","",VLOOKUP($B2,一覧表!$A$2:$B$20,2,FALSE)) D =IF(COUNTIF($B$2:B2,B2)=1,ROW(B1),"") E =IF(K2="","",C2) F =IF(L2="","",COUNTIF($C$2:$C$19,"*"&C2&"*")) どうかよろしくお願いします。
- ベストアンサー
- その他MS Office製品
- エクセルの関数の直し方
現在、画像のような表を作成しておりますが、 1行目の前の行に5行挿入したいと考えております。 下の表からデータを製番・区分ごとに合計して上の表に表示されるようになっています。 5行挿入してタイトル等入れたいと思い、挿入してみると、 今まで下の表からの合計が上の表に表示されなくなってしまいます。 セル番号等確認はしてみたのですが、 どこがいけなくてうまく表示されないのかがわからなくて困っています。 どのように直したらいいかをご教示お願いいたします。 現在入っている関数は以下の通りです。 A2=IF(COUNT(Sheet1!$L$14:$L$38)<ROW(A1),"",INDEX(Sheet1!$B$14:$I$38,MATCH(SMALL(Sheet1!$L$14:$L$38,ROW(A1)),Sheet1!$L$14:$L$38,0),MATCH(A$1,Sheet1!$B$13:$I$13,0))) B2=IF(COUNT(Sheet1!$L$14:$L$38)<ROW(B1),"",INDEX(Sheet1!$B$14:$I$38,MATCH(SMALL(Sheet1!$L$14:$L$38,ROW(B1)),Sheet1!$L$14:$L$38,0),MATCH(B$1,Sheet1!$B$13:$I$13,0))) C2=IF(COUNT(Sheet1!$L$14:$L$38)<ROW(C1),"",INDEX(Sheet1!$B$14:$I$38,MATCH(SMALL(Sheet1!$L$14:$L$38,ROW(C1)),Sheet1!$L$14:$L$38,0),MATCH(C$1,Sheet1!$B$13:$I$13,0))) D2=IF(A2="","",SUMIFS(Sheet1!$G$14:$G$38,Sheet1!$B$14:$B$38,A2,Sheet1!$H$14:$H$38,B2)) E2=IF(C2="","",IF(ISERROR(VLOOKUP(C2,list!$S$3:$T$6,2,0)),"",VLOOKUP(C2,list!$S$3:$T$6,2,0))) H14=IF($E14="","",IF(ISERROR(VLOOKUP($E14,INDIRECT($J14),3,FALSE)),"",VLOOKUP($E14,INDIRECT($J14),3,FALSE))) I14=IF($E14="","",IF(ISERROR(VLOOKUP($E14,INDIRECT($J14),4,FALSE)),"",VLOOKUP($E14,INDIRECT($J14),4,FALSE))) J14=IF(ISBLANK(B14),"",IF(B14<="J121100144","旧","新")) K14=IF(AND(B14<>"",H14<>"-"),B14&"_"&H14,"") L14=IF(AND(K14<>"",COUNTIF(K$14:K14,K14)=1),COUNTIF($K$14:$K$38,"<"&K14)+1,"")
- ベストアンサー
- オフィス系ソフト
- エクセル VLOOKUP関数のエラー非表示について
次のような式がありますが答えが#DIV/0!になり困っています。 =IF($B28="","",($F28*2)/IF(ISNA(VLOOKUP($B28,形態!$B$5:$Z$200,7,FALSE)),"",VLOOKUP($B28,形態!$B$5:$Z$200,7,FALSE))) データ範囲のシートは分かれいます。 データ範囲の形態!B5:Z200の中に空白がある場合#DIV/0!になります。 #DIV/0!を非表示する方法を教えてください。
- ベストアンサー
- Excel（エクセル）