Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(指定なし : 指定なし)
シート参照を行う計算式のスマートな記述方法について
投稿日時: 17/08/13 14:38:52
投稿者: 茶釜

お世話になります。
 
1ヵ月分の日報データから任意の条件判定(〇or×)を行ない、一覧形式で表示したいのですが、
まず、シートは一覧表示させる"main"のほか、日報データを格納し当該日付に対応した"1","2","3"・・・があります。
mainシートには、以下の一覧表を準備しています。
 
  A    B              C              D
1      8月1日            8月2日           8月3日 ・・・・
2 北海道  =IF('1'$A10>100,"〇","×") =IF('2'$A10>100,"〇","×")
3 青森   =IF('1'$B50<660,"〇","×") =IF('2'$B50<660,"〇","×")
4 秋田
 ・
 ・
 
参照シートを'1'のように直接指定しているのですが、これを1行目に入力した日付部分のみを
参照して関連付けられるようにしたいので、どうかご教示のほどお願い致します。
現状、条件判断式を試行錯誤している最中なので、それに伴う修正作業を減らしたいのです。
どうか宜しくお願いします。

回答
投稿日時: 17/08/13 15:12:37
投稿者: Mike

こういうこと?
=IF('1'$A10>100,"〇","×") は =IF('1'!$A10>100,"〇","×") の間違いネ
                  ↓
=IF(INDIRECT("'"&DAY(B$1)&"'!A10")>100,"〇","×")

回答
投稿日時: 17/08/13 15:45:57
投稿者: WinArrow
投稿者のウェブサイトに移動

余計なお世話かもしれませんが、
INDIRECT関数は、揮発性関数といって、
 
どこかのセルが変わると(数式に関係しないセルでも・・という意味)
再計算が発生します。
INDIRECT関数を多用するとレスポンスに影響します。
 
念のため・・・

回答
投稿日時: 17/08/14 10:27:13
投稿者: Mike

WinArrow さんの引用:
INDIRECT関数は、揮発性関数といって、・・・再計算が発生します。
INDIRECT関数を多用するとレスポンスに影響します。
仰るとおりですね。
では、WinArrowさんがINDIRECT関数を使う/使わないを見極める判断基準を教えてください、参考までに。
絶対使わないとか?
ちなみに、私の場合は時間は掛かっても(その間、紅茶か珈琲でも)便利なモノは使う、使ってみて動作停止しない限り、です。

回答
投稿日時: 17/08/14 13:30:42
投稿者: WinArrow
投稿者のウェブサイトに移動

Mike さんの引用:

では、WinArrowさんがINDIRECT関数を使う/使わないを見極める判断基準を教えてください、参考までに。
絶対使わないとか?
ちなみに、私の場合は時間は掛かっても(その間、紅茶か珈琲でも)便利なモノは使う、使ってみて動作停止しない限り、です。

 
私も便利なものは使う主義です。
でも、使ってみて、耐えられないようならば、代案を考えます。
今回の場合(例示の範疇)、決して耐えられないような状況にはならないと思いますが、
余計なことかもしれませんが、何事も勉強ということな特していただければ・・・・
 
ところで話は変わりますが
横方向は、日付ですから、最大31列になると思いますが、
小の月の場合、29〜31に当たるセルの、数式はどのようになっているのでしょう?
単純に、DAY(B$1)のような関数だけで対応できるのでしょうか?
(存在しないシートを参照してしまうかもしれません)
 

回答
投稿日時: 17/08/14 13:50:07
投稿者: WinArrow
投稿者のウェブサイトに移動

>それに伴う修正作業を減らしたいのです。
 
どのような修正作業を想定しているのでしょう?
>それ
とは、何を指しているのでしょうか?
(試行錯誤がそれなんですか?)
 
シート名を'1'〜'31'というように定義してしまえば、
INDIRECT関数で変数化する必要はないですよね?
私は、むしろ、小の月を判断して、存在しないシート、または、参照しない方がよいシートを判断する
ことの方が重要と思います。
 
シートは、1〜31・・・・小の月を判断して、存在しないシート、または、参照しない方がよいシートを判断したいのでしたら、ISERROR関数を使えば回避できます。

回答
投稿日時: 17/08/14 13:52:18
投稿者: WinArrow
投稿者のウェブサイトに移動

文章の訂正
>何事も勉強ということな特していただければ・

何事も勉強ということを納得していただければ

回答
投稿日時: 17/08/14 14:17:22
投稿者: Mike

WinArrow さんの引用:
私も便利なものは使う主義です。
でも、使ってみて、耐えられないようならば、代案を考えます。
了解しました。私もそうすると思います。

回答
投稿日時: 17/08/14 14:31:55
投稿者: WinArrow
投稿者のウェブサイトに移動

質問者さんへ
 
例示は、8月だから1〜31のシートが存在しますよね・・・・
 
では、小の月の場合
1行目(日付)は、どのようになっているんでしょう?
その時、存在しない「日」のシートは?
ついでだから、2月の閏年の場合は?
 
など、いろいろ考えると
スマートではなくなってきます。
 
貴方の考えるスマートとは?

回答
投稿日時: 17/08/14 22:33:57
投稿者: WinArrow
投稿者のウェブサイトに移動

一寸アドバイス
 
1行目の日付に関する数式紹介
セルA1に、2017/2/1を入力
セルB1に
=IF(MONTH(DATE(YEAR($A$1),MONTH($A$1),COLUMN(A1)))<>MONTH($A$1),"",DATE(YEAR($A$1),MONTH($A$1),COLUMN(A1)))
セルB1の数式をセルC1〜AF1まで複写します。
※セルA1の月の先頭日を変更すれば、その月の日付が表示されます。
小の月の場合は、あり得ない日付に相当するセルは、空白文字が表示されます。
 
2行目以降の数式の中で
1行目セルの値が空白文字だったら〜〜という条件で分岐すればよいでしょう。
 
セルB2の例
=IF(B$1="","",IF(INDIRECT("'"&DAY(B$1)&"'!A10")>0,"×","●"))
 
 
 

投稿日時: 17/08/14 22:40:00
投稿者: 茶釜

短時間に沢山ご教示頂きありがとうございました。おかげさまで、イメージに近い仕上がりとなりました。厚く御礼申し上げます。
 
Mikeさんのご教示に従い、当初の目的は達せられました。
また、今回質問の例示は簡潔にしましたが、実際はデータ量が多く、 WinArrowさん(←一口馬主ですか?)ご指摘のとおりレスポンスへの影響は感じました。マシンの非力さもあるかもしれませんが、INDIRECT関数に置き換えた後にデータ検証したところ判断式に不備があり、その修正をかけてから処理が終わるまでに1セルあたり30〜40秒かかり、結構な時間ロスでした。
 
それから、以下のお尋ねですが、
>小の月の場合、29〜31に当たるセルの、数式はどのようになっているのでしょう?
これは無視です。まず他のDBからDLしたデータを日々"1","2"・・・のシートにコピーした上で参照して加工しているので、来月の2月なら29日以降のデータは存在せず実用上の問題は生じません。ISBLANKで非表示としています。
 
>どのような修正作業を想定しているのでしょう?
例えば、判断式を =IF('1'$A10>100,"〇","×") としていたところ、実際にデータを放り込んた結果から判断式に見直しが生じた場合、先頭列の数式を次列以降に全コピーした上で、'1'→'2'、'1'→'3'、・・・というように、参照日付部分の置換をしていました。(手順が拙いのは自覚していますが・・・)
 
>では、小の月の場合1行目(日付)は、どのようになっているんでしょう?
2月の場合、2017年2月1日〜29日です。シートに29日まで欄はあるので、上にも書いたとおりISBLANKで最後は空白です。
私(ど素人)の考えるスマートとは、なるべく汎用性を持たせるとか、修正の手間が少ない形態です。
参照元のデータや判断式が変わっても最小限の修正で対応できれば、他の人が別の案件に流用しやすいのではないか、それによって後々の生産性が上がればいいなと考えています。
おつきあい頂き、助かりました。本当にありがとうございました。

回答
投稿日時: 17/08/15 09:52:38
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:

1セルあたり30〜40秒かかり


引用:
まず他のDBからDLしたデータを日々"1","2"・・・のシートにコピーした上で参照して加工している・・・
 例えば、判断式を =IF('1'$A10>100,"〇","×") としていたところ、実際にデータを放り込んた結果から判断式に見直しが生じた場合、先頭列の数式を次列以降に全コピーした上で、'1'→'2'、'1'→'3'、・・・というように、参照日付部分の置換をしていました。(手順が拙いのは自覚していますが・・・)

1セルあたり30〜40秒は、掛かり過ぎです。
OS,Excelのバージョンの説明がないので、何とも言えませんが・・・
最初の説明以外にも配列を使った数式が存在するものと推測します。
 
上記から、考えて、日別のシートではなく、1つのシートに全部取り込むことを検討できないのでしょうか?データの中にも日付データが存在すると思いますので、INDEX関数などで対応できるのではないかと
勝手に推測します。
 

回答
投稿日時: 17/08/15 11:27:53
投稿者: WinArrow
投稿者のウェブサイトに移動

日別のシートのレイアウトと数式の意味などを掲示すると、
若しかしたら、アドバイスが貰えるかも?

回答
投稿日時: 17/08/15 12:43:31
投稿者: sy

茶釜 さんの引用:
>どのような修正作業を想定しているのでしょう?
例えば、判断式を =IF('1'$A10>100,"〇","×") としていたところ、実際にデータを放り込んた結果から判断式に見直しが生じた場合、先頭列の数式を次列以降に全コピーした上で、'1'→'2'、'1'→'3'、・・・というように、参照日付部分の置換をしていました。(手順が拙いのは自覚していますが・・・)
 
私(ど素人)の考えるスマートとは、なるべく汎用性を持たせるとか、修正の手間が少ない形態です。
参照元のデータや判断式が変わっても最小限の修正で対応できれば、他の人が別の案件に流用しやすいのではないか、それによって後々の生産性が上がればいいなと考えています。

こんにちわ。
 
質問者さんのような状況で、シート名の修正を行うなら私でも置換でやりますね。
シート名が1〜31の数値なら、1列1秒もあれば置換できますよね。
それに変更なんて作ってる時だけで、出来てしまえばもうする事は無い作業ですよね。
仕様変更なども日常的に起きる事では無いですし(日常的に起きるなら構造から見直すべきです)、
そう考えれば置換の操作に1列5秒くらいかかったとしても、そのファイルをこれから使用する上では大した手間ではないと思いませんか?
 
それよりもINDIRECT で何処か1か所変更する度に、毎回30〜40秒待たされるなんて本末転倒じゃないですか?
修正作業を行う回数よりも、今後日常に使用してデータを更新する回数の方が圧倒的に多いんじゃないですか?
 
修正作業は日常業務じゃないので、日常業務であるデータ更新時の効率を最優先で考えた方が良いですよ。

回答
投稿日時: 17/08/15 14:03:15
投稿者: Suzu

こんにちは。
 
WinArrowさんの回答

WinArrow さんの引用:
日別のシートのレイアウトと数式の意味などを掲示すると、
若しかしたら、アドバイスが貰えるかも?

の意図と同じだと思いますが・・
 
今参照させているのは、日毎のシートの特定セルの値ですよね。
紙ベースの書類を 印刷する為に用意したシートの特定セルの値を参照させているのですよね?
 
ではなくて、1行1レコード つまりデータベースとして入力ができないか。
必要なら、印刷用のフォーマット のシートの特定セルに
レコードを特定する為の 日付/地域 を入力する事で、 その指定した 日付/地域のレコードを
VLOOKUP関数等を使用し参照し印刷させる事が可能です。
 
或いは、帳票と同じフォーマットのフォームを用意し そのフォームに 入力し
VBAを使い、シートに流し込むとか。
 
そうすれば、ピボットテーブルとか、解析をする上ではかなり楽だと思います。

回答
投稿日時: 17/08/15 23:06:26
投稿者: んなっと

Excelのバージョンがわからないので、以下の回答にはまるで自信がありません。
 
●少し余裕をもって行をあけながら、1枚のシートにまとめておく方法。
 
  A  B  C  D
 2 1  9 27  7
 3 1 24 18 23
 4 1  3 25 24
 5 1 14 12 22
 6 1  1 27 11
 7 1       ┐
 8 1       |少し余裕をもって
 9 1       ┘
10 1      
11 2 12 27  1
12 2 19  5  1
13 2 18  4 14
14 2 12  1  9
15 2      
16 2      
17 2      
18 2      
19 2      
20 3 21 13 20
21 3 16 22  3
22 3 30 24 26
 
B2
=IF('1'!B2="","",'1'!B2)
右方向・→下方向・↓
 
B11[実際にはB100など]
=IF('2'!B2="","",'2'!B2)
右方向・→下方向・↓
 
B20[実際にはB200など]
=IF('3'!B2="","",'3'!B2)
右方向・→下方向・↓
 
 
●1枚のシートにまとめないで、現在の方法にこだわる場合...
数式作成用の新規シートを作成する方法も。
 
[1]新規シートを作成
→A1の表示形式を文字列に
→A1に基本形の数式を入れる 置換したい部分はア、イ、ウなど暫定の文字にしておく
 
              A  B              C              D
1 =IF('ア'!イ>ウ,"○","×")               
2   
3
4
5             
 
 
[2]C1から右,A2から下,B2から下に置換後の文字列
 
              A  B              C              D
1 =IF('ア'!イ>ウ,"○","×")                1              2
2            $A10 100                            
3            $B50 660                            
4            $C5  20                            
5            $D3  15                            
 
 
 
[3]C2
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$1,"ア",C$1),"イ",$A2),"ウ",$B2)
右方向・→下方向・↓
→正しく数式が作成されていることを確認
 
              A  B              C              D
1 =IF('ア'!イ>ウ,"○","×")                 1              2
2            $A10 100 =IF('1'!$A10>100,"○","×") =IF('2'!$A10>100,"○","×")
3            $B50 660 =IF('1'!$B50>660,"○","×") =IF('2'!$B50>660,"○","×")
4            $C5  20  =IF('1'!$C5>20,"○","×")  =IF('2'!$C5>20,"○","×")
5            $D3  15  =IF('1'!$D3>15,"○","×")  =IF('2'!$D3>15,"○","×")
 
 
[4]C2:E5をコピー
→メモ帳に貼り付けてコピーして、Excelに戻ってmainシートのB2に貼り付け
 (メモ帳を使わずに、形式を選択して貼り付け[値] → = を = に置換 という方法も)
 
    A  B  C  D
1     8/1 8/2 8/3
2 北海道  ×  ×  ×
3  青森  ×  ×  ×
4  秋田  ×  ×  ○
5  新潟  ○  ×  ×

トピックに返信