数式設計支援改良版

前回の数式設計支援ツールに以前作成した数式分解ツールの改良版を一緒にして、１つのエクセルファイルにまとめてみた。

サンプルとダウンロードリンク

数式設計支援ツール

複雑な数式を組み立てていくのは一苦労。
なので、少しでも楽になるように数式の全体構成を確認しながら作成していけるように、ツールを作成した。
まあまあ使えるレベルになったので、ここで公開することにする。
番号の振り方が最初は複雑に感じると思うが、サンプル見れば分かると思う。

数式設計支援

番号、名称、説明、タイプ、値に情報を入力。
結果の式は式構成および式総合に表示される。

使い方

１．まずトップレベルから式構成を見ながら式（番号、名称、説明、タイプ、値）を記述する
２．名称どうしを四則演算や簡単な関数で繋げた式として記述する
３．複雑になる場合は参照として別式にする
４．その後、徐々に下のレベルの式を記述していく
５．値の欄に実際に式で使用する値を記述するのは、ある程度完成してからでよい
６．完成したら、トップレベルの式総合セルをコピーする（Ctrl+C）
７．実際に式を入れたいセルにカーソルを移動して、値の貼り付け（貼り付けオプションにあります）
８．先頭に"="を追加して完成

＊）行の高さ、列の幅は適当に調整してください。
＊）行のコピー追加や切り取り移動できるようにシート保護していません。
＊）行追加は式を記述していない行をコピーして、追加したい位置で挿入
＊）セルをクリアする場合は数式を削除してしまわないように注意。番号～値までをクリアする。
＊）式番号の検索範囲を番号の最大値で判断しているので、最後の行に10000とかの大きな番号をあらかじめ設定しておく

列の説明

番号：
整数ならば式
数値が同じならば後の要素を普通に繋げる（同じ式の構成要素）
小数の桁は関数のパラメータ、および式、関数の入れ子関係。
小数の数値が変わったら、カンマで繋げる
小数で桁が減った場合は変わった桁数に応じて閉じカッコを追加
空欄行で区切ると別の式扱い

タイプ：
rならば式の参照（値は式番号）
fならば関数。値の後ろに開きカッコを追加

値： 全て文字列扱い

式構成：
名称を連結
空欄の場合は値
参照の場合は[式番号]を追加

式総合：
参照元も含めた総合の式
値や参照元の式を連結（タイプが参照で値が空欄時は名称で代用）

スクリーンショット：

サンプルとダウンロードリンク

集計対象セルの切り取り移動で集計計算がずれる

問題：

表にデータを入力する途中、気がつくと入力行がずれていた。とか、いくつかデータを飛ばして入力していた。
なんてことは、よくある事。

例えば、下の赤の網掛け部分は本当は１行下に入力しなければならなかったような場合。

そんなとき、また入力し直すか？
面倒だな。
やはり、すでに入力されているセルを正しい位置に切り取り移動(Ctrl+X,Ctrl+V)したいところだろう。

ところが、これをやると

上のように集計計算がずれてしまう。

これは、計算対象セルの移動に対応して、エクセルが数式に使われているセル範囲を追従して自動的に変更するために起こることだ。

表の作成中は、非常に便利な機能なのだが、表の完成後には、こんな風に、やっかいなことも。

コピー貼り付けではこんなことは起こらないので、そっちで対応してもいいんだが、元の空欄にしたいセルの内容を削除するという、１ステップ余計に必要だし、間違いの元になることも。

できれば切り取り移動で済ませたい。

という訳で考えてみた。

対策：

問題は計算対象範囲のセルが移動した時に数式に使われているセル範囲が自動的に追従して変わるということ。

ということで、数式に計算対象のセルを直接使わないようにすることを考える。

上の例でいうと、SUM関数の中にC8:E8というような移動する可能性のあるセルを使わないようにする。

では、どうするのかというと、移動する可能性のない、基準セルからの位置で計算範囲をしてやれば良い。

実際の数式：

具体的にはOFFSET関数を使って、次のように範囲指定する。

F7の数式：

=SUM(OFFSET($B$2,ROW($B7)-ROW($B$2),1,1,COLUMN(F$2)-COLUMN($B$2)-1))

OFFSET関数のパラメータ

　基準セル：$B$2

　行オフセット：ROW($B7)-ROW($B$2)・・・基準行との差

　列オフセット：1・・・基準セルの隣

　範囲の高さ：1

　範囲の幅：COLUMN(F$2)-COLUMN($B$2)-1・・・基準列と”計”列との間に含まれる列数

＊）基準との差で行オフセットや幅を求めているのは、行、列の挿入・削除を考慮しての処理。

この状態で切り取り移動すると、

という具合に計算結果はずれなくなった。

これはこれでいいんだが、行の色はずれたままだな。

このように書式のずれは、これだけでは対応できない。

やはり書式には条件付書式。

　条件式　=ISEVEN(ROW(B3))・・・行番号が偶数ならば

　書式：　薄い青で塗りつぶし

　適用範囲　=$B$3:$F$12

こうすれば、

と、行の色も問題ない。

上の方法だと切り取られた７行目の条件付書式もなくなるのでダメでした。
たまたま７行目が塗りつぶしなしだったので見逃してしまっていた。
書式の問題は数式だけでは無理っぽいな。

ちなみに、左右の罫線はそれぞれ計算範囲外の隣の列のセルで書式指定してあるので、条件付書式で指定しなくても問題ない。

これで、入力ミス時の修正作業が、少し楽になるかも。

以上、今回はこれで解決。

範囲内の文字列を結合するには？

問題？

範囲内の文字列を結合と言っているのは、

みたいな表があって、

　　地名リスト：福岡県&熊本県&熊本市&佐賀県&佐賀市&長崎県&大分市

とセル内の文字列を結合して表示するような場合のことだ。

文字列の結合には、ご存知のように、&演算子、CONCATENATE関数が使える。
しかし、上のようにセパレータで区切ったり、数が多いと結構大変だ。

数値の場合、SUM関数のように範囲指定して演算をするような関数は用意されているので、範囲指定して、SUM関数等を使えばいいだけ。
しかし、文字列の場合、こんな風に範囲指定できる関数は用意されていないようだ。

文字列の場合、１個づつ&演算子やCONCATENATE関数で結合するしかないのか？
まあこれでも目的を果たせるならいいが、結合する範囲が変わったり、途中に行や列が挿入されたら？
という訳で、今回は範囲内の文字列の結合。

考え方：

文字列の結合には結局のところ&かCONCATENATEを使う必要がある。
途中にセルが挿入されても、数式のコピペで対応できるようにする。

ということで、採用したのが単純な方法。
隣のセルと結合した結果をそのまた隣と結合していくチェーン方式。

実際の数式：

計算用セルを横に追加する。（通常は非表示）

D4が最終結果となる。

D4の式：

=IF(OR(B4="",OFFSET(C4,-1,0,1,1)=""),"",$E$3)  &IF(OFFSET(C4,1,0,1,1)="",T(B4),B4&OFFSET(D4,1,0,1,1))

C4の式（結合対象範囲を判断するために使用）：
=ROW(C4)

D4式の解説
　基本処理：
　　(セパレータ$E$3)と(現在行のB列)と(下の行のD列)を結合。
　　文字列結合には&演算子を使用（CONCATENATE関数でもＯＫ）。

　前方にセパレータを追加しない条件：
　　B列が空欄または、一番上の行。

　最下行の処理：
　　下の行と結合しない。

*)上下のセルをC3やC5と直接指定せずに、OFFSET関数を使っているのは、途中に行を追加した場合に正しく直下や直上のセルを指定できるようにするため。
*)T関数は最下行のB列が空欄だった場合に0ではなく空欄("")とするために追加。
*)C列の上下は空欄にする。

これで完成。途中に行を挿入しても行コピーでＯＫだ。

さらに応用として、IFを追加して、条件にあう場合だけ結合というのもできるだろう。

今回のねた、範囲内の文字列を結合するなんていう場合は稀だとは思うが、たまたま別件で必要になったので考えてみた。

以上、終了。

数式分解ツール

複雑に入れ子になった数式を分解表示するツールを数式で作成したので公開してみる。
オレンジの箇所に数式を入力すると下に分解表示されます。
作成途中から、コレを使いながらコレを作りました。
使えるかも？

循環参照（反復計算）で累計計算

事例：

以前こんなことを聞かれた事がある。

「このセルに毎日の実績を入力すれば累計値がどんどん増えていくようにできませんかね～？」

”このセル”というのは一箇所。すなわち同じセルに入力するたびに累計値に加算されていくようにしたいらしかった。イメージ的にはこんな感じか。

当時の私の答えは、

「無理です。」

上のような数式を入力すると、こんなメッセージが現れて警告される。

循環参照、すなわち再帰的な計算はエクセルではできないということだ。

しかし今更ながら、エクセルのオプションにこんな項目があったのを思い出した。

最初に見た時に、すぐに循環参照のことを言っているんだろうなと思いながらも、なぜか今まで試さず、このこと自体を思考の隅っこに放置していた。

というわけで、やっと重い腰を上げて実験してみた。

実験：

まずこのオプション設定をこんな感じに変更。

「反復計算を行う」にチェックを入れて、「最大反復回数」を”１”に設定してみた。この「最大反復回数」というのはいわゆる無限ループを回避するための設定だろう。

変化の最大値についても同様に無限ループ回避のための設定だろうが、動作が想像できないので今はそのまま。

そして再び、この数式を。

こんどは警告メッセージは現れない。

そして、これが初期状態。

本日実績としてB3に最初から"100"が入力されていたのでC3の累計は"100"となっている。

そして、この状態からB3に数値を入力してみる。

例えば、"50"。

と、ここまで予想通りの動きだ。

問題点：

これで、最初の事例の件は解決だな。とりあえず保存しておこう。とセーブボタンをポチっとしたとたん・・・

ん？今なにか変わったような？

じつは、セーブしただけで、こんな風に。

見てのとおり、もう"50"増えて累計値が"200"になってしまった。

最初は意味がわからなかったが、これはエクセルが保存時に”再計算”されるために起こる現象だ。

数式が実行されるタイミングは、多くの人が通常”自動”に設定していると思う。この”自動”の中に”保存時”というのが入っているのだ。

これは問題だ。

このままでは、使えない。

なにか、策を考えねば。

解決策：

というわけで、ロック機能とリセット機能を付けてみた。

C3の数式：

=IF($D$2="計算実行",C3+B3,IF($D$2="リセット",0,C3))

使い方は、

最初にD2を”リセット”にして、C3を0クリア。

次に、”計算実行”にして、B3に数値入力。

入力終了後にD2を”ロック”にする。

あとはお好きな操作を。

これで、今回は終了・・・

でもない。

再び問題発生：

実は再計算処理が悪さをするシチュエーションがまだあったのだ。

それは、本日実績の数値をそのままの状態で、”ロック”から”計算実行”や”リセット”から”計算実行”に切り替えた場合だ。

このとき発生するのは、「本日実績が累計値に２回足されてしまう」という現象。

これは調査の結果、理由は不明だが、

ドロップダウンリストから選択した場合、なぜか再計算が２回発生してしまう。

ということが原因になっていた。

これからも分かるように、（考えてみれば当たり前だが）要は何らかの原因で再計算が発生するたびに累計値も再計算されるということ。

例えば、他の無関係のセルへの入力とか。

つまりほとんど何もできないっていうこと。

（もちろん再計算のタイミングを熟知していて、そのときに累計値に足されるセルの内容を0や空白にしておけばある程度は回避できると思うが・・・）

結論：

結局、結論は、

循環参照（反復計算）を使った、累計値の計算は、やはり、

「無理です。」（現実的には）

ということだ。

結論を覆して解決：

と、一旦は結論を出したが、もうちょっと粘ってみた。

その結果、循環参照（反復計算）を使った、累計値の計算を実装できた。

ある程度シチュエーションは限定されるが、今回の「本日実績」のように、累積をとる回数が数えられる場合には適用可能なやり方だ。

なお、このやり方でも、今回実装したロック機能はやはり必要。

そして、本日実績の項目が複数あったりして途中に再計算が入っても累計値には影響しない。

そのやり方は、今はまだ非公開。興味がある人は考えてみよう。

ヒントは、

”反復計算はやはり必要”

”操作者には見せないが、こっそり毎日の数値を保持している”

”累計値はそっちの累計をとる”

”もちろん数式だけで実現”

サンプルは作ったのでこちらのサンプルページの

「日々の実績値入力＆累計計算」

というところにあります。

これで本当におしまい。

分かりにくい互換性メッセージ

エクセルの便利な機能の１つに互換性チェックがある。
大抵は、どのセルに問題があるかというところまで教えてくれる。
ところが、そこまで特定してくれない場合もある。
こんな感じ。

メッセージを読む限り、あまり大したことはないようだが、気になったので調べてみた。

その結果、色の再現性だとわかった。（あくまでも今回の場合。）
「Excel97-2003」の色はRGBがそれぞれ1bitづつの計８色カラーだった（かな？）。

メッセージを読む限り、一番近い色に変換してくれるようなので、「Excel97-2003」でも問題なく開けると思われるが、ユーザに色で判断させているような場合には問題になるだろうな。

住所&郵便番号検索

郵便番号や住所の入力が面倒だったので、エクセル等のスプレッドシートに簡単にコピペできるように作りました。
javascriptで、googleドライブのspreadsheetのデータを検索することで実現してます。
そこそこ使えます。
なお、まだ全国カバーしていませんが、おいおい追加していきます。
2015/06/10 漢字検索等、いくつか機能追加。
2015/06/12 半角カナ検索を全角ひらがな検索へ変更

---

都道府県を選択し町域名の一部を全角ひらがな又は漢字で入力後、検索ボタンを押してください。

都道府県（郵便番号CSVファイルダウンロードリンク）リスト

都道府県（郵便番号CSVファイルダウンロードリンク）リスト

住所検索＋郵便番号自動入力を作成中に都道府県のリストが必要だったので、Webで探してみて、あるにはあった。しかし並び順が郵便番号ファイル名と合っていなかったので自分で作った＋アルファのリストを公開することにした。

都道府県のリストと日本郵便のサイトで公開されている郵便番号CSVファイルのURL
です。（baseUrl+fileNameで使います）
OneDriveからの埋め込みですが、そのままコピペでいけるハズ。

SCVファイルの種類は、
読み仮名データの促音・拗音を小書きで表記するもの
例：ホッカイドウ
というやつです。

めったに使わないが、いざ必要になった場合、全部一から作りたくない。
という人には便利かも。

いわゆる万年カレンダー（のサンプル）

OneDriveからの埋め込みです。
年と月のセルに入力できます。

Excel Onlineでシートコピーはどうやるの？

最近Excel Onlineも時々使うのだが、やはり色々と違いがある。
データ入力規則やユーザ定義表示形式については、すでに解っていたが、もっとよく使う機能がないことに気が付いた。
シートコピー、シート名のタブを右クリックして出てくるメニューにある、あれのことだが、Excel Onlineでは、ここにコピーの選択肢はない。

出来ないはずはないだろうと調べてはみたが、無理みたいだ。
オフィスサポートページによると、シートコピーするのには、

1.CTRL+SPACE,SHIFT+SPACEで全範囲選択
2.CTRL+Cでコピー
3.新規シート追加
4.CTRL+Vで貼り付け

または、一旦、通常のエクセルで開いてシートコピー

と、なんとも心苦しい説明がされていた。

なぜこれが実装されていないのかは全く持って不明。
通常のエクセルとの差別化？
違うな。
技術的な問題？
たぶんそんなことはないだろう。
謎だ。

ちなみにGoogle Spreadsheetは普通にできる。
（もっとも、こちらはOnlineしかないが）

それと、ちょっとしたTipsを
全行選択　：　CTRL+SPACE
全列選択　：　SHIFT+SPACE
なのだが、SHIFT+SPACEが機能しない現象に出くわして、
イラついたことはないだろうか？
自分はある。

そういう人は「半角/全角」キーを押すと幸せになれるかも。

Excel Onlineとデータ入力規則と日付表示形式

エクセルで作ったファイルをexcelOnlineで開いたら・・・うまく動かない。

どうも日付を比較する数式のところの動作がおかしい。
表示形式が違うので見た目には確かに違うが、内部的には同じなので、エクセルではちゃんと意図した動作をしている。しかし、Onlineでは意図したようには動いていない。

起こった現象：

青枠のセルに赤枠のリストから選択できるように、入力規則を設定する。そして、これらのセルには表示形式として上にあるようなユーザ定義の表示形式を設定する。
そして、青枠と緑枠のセルを比較して結果を表示する。
というような処理だ。
いくつかのパターンで実験してみたが、エクセルでは、すべて、上のように見た目は違うが青と緑は同じと判定された。

このファイルをOneDriveに保存してExcelOnlineで開くと、

上の図は開いた直後だ。一見、問題なさそうに見える。
数式バーにも表示されている形式ではなく、"2015/6/1"と表示されている。
ちなみに他のパターンの青枠もすべて"2015/6/1"だ。

ところが、ドロップダウンリストから改めて、今と同じ６月１日を選択し直すと、

判定はNGとなり、数式バーの表示も"平成27年06月01日(月)"と見た目と同じになってしまった。

面白いのは、さらに３番のパターン以外も試してみたところ、

と、問題ない場合もあることが分かった。
数式バーも"2015/6/1"と変わっていない。
なお、４番の青枠には"2015/6/1"と直接入力した。

以上の現象から問題点を確認すると、
ドロップダウンリストから選択したときに、エクセルでは日付シリアル値としてセルに入力されるがOnlineでは文字列となる場合がある。
というところだ。

原因推測：

ExcelOnlineでは入力規則で範囲をリストにしたときに、表示形式を適用した文字列データになって、元のデータが失われているのではないか？

確認：

表示形式を数値にして確認。まずはエクセルから。

ドロップダウンリストの表示は赤枠内の表示と同様に表示形式適用後のデータが表示されるが、実際に選択してセルに入力されると、青枠内の表示形式に従って、日付シリアル値がそのまま表示された。
１～３すべて同じだ。
確かにエクセルでは、元データはちゃんと保持されている。

一方Onlineでは、

ドロップダウンリストの表示はエクセルと同じだが、選択後のセルには日付シリアル値ではなく、リスト表示と同じ文字が入力されてしまった。
確かに、元データは失われているように見える。
しかし、３番のパターンではこのようになったが、１，２はまたちょっと違った。

こんな風に、ドロップダウンリストの表示とは関係なく、ちゃんと日付シリアル値が入力される。
これは、どう考えればいいのか？元データが残る場合もあるのか？
最初はまったく訳が分からなかったが、どうもエクセル（Onlineでも）の自動解釈で文字列が日付と判断されているようだ。
しかし、１番の”平成27年06月01日”なら解るが、２番の”６月１日”とか日付シリアル値に変換できないだろ。年が解らない。
と思ったが、一応、実際に入力して確認してみたところ・・・

これはエンターキーを押す前。





エンターキーを押すと、こうなる。

という具合に、（おそらく今年が）自動的に補完されるとは思わなかった。
次に一応、曜日が入るとやっぱりだめなのか、を確認。

エンターキーを押す前。

エンターキーを押すと・・・
やっぱりダメか。





結論：
１．入力規則のドロップダウンリストにリスト化されるのは、

　　　　エクセル→元データと表示形式適用後のデータ両方

　　　　Online  →表示形式を適用後のテキストデータのみ

２．年の情報はなくてもエクセルは補完してくれる。

今回の調査の発端になった現象を回避するには、
ドロップダウンリストには、エクセルが日付と解釈可能な表示形式を使う。
ということだな。

最後にいうのもなんだが、
実はExcelOnlineにはドロップダウンリスト（データの入力規則）もユーザ定義表示形式もない。
ただし、エクセルで作成したファイルを開いた場合には、ちゃんと機能した状態が再現されている。

というわけで、まあこんな現象もしょうがないのか。

以上。

シートを跨いだ集計

状況：

タイトルが解りづらいかもしれないが、要はこういう事。
月ごとの集計をする、１月、２月、３月といった名前のシートがあり、そして年間の集計をする集計という名前のシートで、各月シートの集計から年間集計を求めるような場合だ。

こんな感じ。
すなわち、集計シートのB4:G4に各月の集計結果を表示したい場合だ。

単純に、'４月'!G4、'５月'!G4と打ち込んでいってもいいが、かなり面倒な作業だ。
この例では大したことはないが、毎日の月集計ともなると・・・気が遠くなる・・・
ここはなんとか数式のコピーでできないか？

一応、直に打ち込んだ'４月'!G4をこのままコピーするとどうなるかを確認しておこう。
'４月'!G4をC4～G4にコピーすると、
'４月'!H4～'４月'!L4
となってしまうな。これじゃダメ。

ここで問題は、
１．'４月'!の部分はシートを判別するためのところなので、ここはコピーした時に変わってほしい。
２．G4の部分は各月のシートの同じ列を参照するので、行は下にコピーした時に変わってほしいが、列は変わってはダメ。
この２点だ。

つまり、
　'４月'!　→　列可変、行固定
　G4　→　列固定、行可変
になるような数式を考える。

考え方：

まず、式全体について、
各月のG4の値を参照してきたいので、
　INDIRECT(各月の集計セルを示す参照の文字列)
というふうに、INDIRECT関数を使うといいだろう。

そして、”各月の集計セルを指し示す参照の文字列”には
　CONCATENATE(各月シートを示す可変文字列,"!G",集計セルの行を示す可変文字列)
とCONCATENATE関数を使う。

そして、
各月シートを示す可変文字列
集計セルの行を示す可変文字列
この２つを数式で表すことを考える。

実際の式：

１．集計セルの行を示す可変文字列
　　　ROW('４月'!$G4)
　これじゃ４月固定にならないのか？
　まあそうだが、ここは各月のシートは同じ構造をしているという前提を付けると、これでいい。
　しかしここで問題が１つ。
　これだと月シートのG4より前に列が追加されたときに対応できない。そこで列も可変にするために、ROW,COLUMN,ADDRESSと３つの関数を組み合わせて、

　　ADDRESS(ROW('４月'!$G4),COLUMN('４月'!$G4),1,1)

とする。

ちなみに、上の式単独の戻り値は$G$4という文字列

またこれに伴い、CONCATENATE関数の引数の１つ"!G"を"!"に変更する。

２．各月シートを示す可変文字列
　表のヘッダー行に注目する。

これはシート名と同じだ。（まあ、普通はそう設計するだろうが。）
　と言うわけで、これを利用する。
　すなわち、B3～G3を参照すると、それぞれ４月～９月という文字列が返ってくるということだ。
　従って簡単に、
　　B$3

　これらを合わせて、

　　INDIRECT(CONCATENATE(B$3,"!",

　　　　ADDRESS(ROW('４月'!$G4),COLUMN('４月'!$G4),1,1)))

この式をB4に入力して、それを残りのセルにコピーすればＯＫ。

結果：

各月シートに行や列を追加してテストしてみたが、これもＯＫ。
これでだいぶ楽になったな。

以上、完了。

１行おきに集計したい

久々にネタが出来た。

状況：

出退勤表にありがちなシチュだと思うが、こんな感じの表を考えてみる。

１日の労働時間の集計をするには単純に

(退勤時間 - 出勤時間)の合計

な分けだが、それを数式で表すにはどうすればいい？

　１．集計用の列を追加して、その列で集計する。
　こんな感じ。

　２．なんとか１個のセル内で計算する。

１の方法でも集計処理自体は問題なく実現できる。
しかし、見た目を整えるのが面倒だ。
集計用の別シートを作成して、そこで処理すれば、見た目は何とかなるだろう。
しかし、この方法ではネタにならない。

というわけで、２を採用。

考え方：

(退勤時間 - 出勤時間)の合計

この式を

退勤時間の合計 - 出勤時間の合計

と展開する。

つまり、バックが白色の行の合計から、薄青の行の合計を引くことを考える。
合計を求めるならSUM関数だが、範囲指定をどうするか？

セルを１行おきに直接指定する。

まあ、人数追加（行追加）とかなければ、問題はない。
行数が多いと数式を記述するのが大変そうだが・・・

しかし、人数追加にも対応できて、記述も楽なやり方はないだろうか？

行追加に対応するためには、エクセル自身の補正機能？
（あの、行や列を追加した時に、数式の範囲を自動的に変えてくれる機能）
を使えるようにしたい。
そのためには、

　”B4:B11”

というような連続した範囲をSUM関数に指定する必要がある。

そして、その連続した範囲を２つの別々の範囲に分けて合計を求める。
ということを考えてみる。

実際の数式：

範囲を分けるやり方だが、
　配列数式で範囲の一部を０に置き換えて合計する方法
　 {=(SUM(IF(条件１,B4:B11,0))-SUM(IF(条件２,B4:B11,0)))*24}

が考えられる。（24を乗じているのは単位を時間にするため）
これには、出勤時間と退勤時間の行を判定する条件が必要になる。

さてどうするか？
これは行番号で判定できるだろう。
今回の例では、１行おきなので、行番号が偶数か奇数で範囲を分けられるので、

条件１：
　ISODD(ROW(B4:B11)-ROW(B$4))

条件２：
　ISEVEN(ROW(B4:B11)-ROW(B$4))

とすれば良いだろう。
（ここで、ROW(B$4))を引いているのは、表の上に行が追加された場合を想定しての処理。）
これでいいんだが、飛ばす行数が変わる場合も想定して、

条件１：
　MOD(ROW(B4:B11)-ROW(B$4),2)=1

条件２：
　MOD(ROW(B4:B11)-ROW(B$4),2)=0

こっちのほうがいいだろう。

結果：

これでＯＫ。

参考：

こんな変更があってもわずかな手間で修正できる。

B5:B16とB$5への変更は行を追加したときにエクセルが自動的にやってくれる。
手作業で変更したのは除数を２から３にしただけ。

以上、今回はおしまい。

ドロップダウンリストの矢印が消えた！

ある日突然、矢印が消えた。

入力規則でドロップダウンリストを設定したセルをセレクトすると横に表示されるやつだ。
ただし、いつも消えているわけじゃなくて、カーソルを合わせてマウスボタンを押している間は表示されているが、離すと消えるという、謎な現象だ。

調査：

ネットで調べたが、あまり有用な情報（ドロップダウンリストから選択するにチェックを入れましょうとか、オプション設定のすべてのオブジェクトを表示するにチェックを入れましょう。というのはもちろんあったが・・・）がなかったので、調査してみた。

まず、単純にブックをコピー(別名保存）してみたが変わらず。
次にすべてのシートを個別に新規ブックにコピーしてみたが、これも変わらず。
しかし、ここで、気になる点が。
シートを１つづつコピーしていったのだが、ある特定シートをコピーするまでは、正常動作だったのだ。

これで問題のあるシートが特定できたわけだ。
あとは、このシートのどこに問題があるのか？ということだ。

一番怪しいのは、このシートで最近変更した箇所だな。
図のリンクの貼り付けか・・・・

試しにこの図を削除してみたら・・・

当たり。

矢印が現れた。

念のため、新規ブックで同じ状況を作ったら再現したので、間違いないだろう。

原因：

なぜかは不明だが、リンクされた図の貼り付けがまずいらしい。
オプションのオブジェクト表示の切り替えで表示されたり消えたりすることからもわかるように、この矢印はオブジェクトの１つだろう。
その辺が関連しそうだが、これ以上はわからないな。

（ちなみに、ドロップダウンリストとリンクされた図が同じシートにある場合は、この現象は起こらない。また、リンクされた図の貼り付けをアンドゥ機能でなかったことにしても、矢印は消えたまま。
これはこれで謎だが。）

これで何が悪さをしているのかは判明したが、はたして対処方法は？

対策：

いまのところなし・・・

では困るので、色々と試行錯誤してみた結果、

ドロップダウンリストと同じシートでコピーすれば、どこにリンクされた図を貼り付けても問題ないことがわかった。
すなわち、

コピー元のセルを一旦ドロップダウンリストと同じシートで参照させて、そのセルをコピー元にして、必要なシートにリンクされた図を貼り付ける。

これで矢印が表示されるようになった。

図のリンク状態も問題ないようなので、正常に戻ったと見ていいだろう。

というような、原因も謎だが、対策も謎な結果になってしまった。

まあ、解決したからいいか。

追加

これで対策できたと思っていたら、また同じ現象が発生した・・・

というわけで、対策については、調査中。

土曜日がいっぱい

曜日ごとの集計処理を作成中に不思議な出来事に遭遇した。
最初は何が起こっているのか皆目見当もつかなかった。
まさかバグ？
いやいや、何かやり方が間違っているはず。

というわけで調査してみた。

１．起こった現象

下のような感じの、日々の売上を記録するような表があって、これから、曜日ごとの売上を分析するような数式を作っていたところ、

土曜日の平均(AVERAGE)だけが実際の数値と合わない。

というようなことが起こった。

なお、分析は週単位で区切って行った。

例えば、４月５日から３週間分、というように。

また、曜日の判定にはWEEKDAY関数を使っている。

現象を、もう少し詳しく説明すると、
ある数週間分の平均までは正常だが、それを越えると平均値がどんどん減り始める。

まとめると、
１．分析する範囲を週単位で増やして行くと、
２．土曜日だけ、
３．平均値が減ってい行く。
というわけだ。

２．原因推測

１．まず考えられるのは、分析する範囲を増やして行くと異常が起こることから、
範囲の設定
に問題がないかということ。

２．そしてもう一つは、土曜日だけに異常が見られることから、
日付の処理
に関して問題があるのではないかということ。

３．それから、平均値が減るということは、分子の合計が減っているか、分母の個数が増えているということだ。しかし、分析週数を増やして行く過程で起こっていることから考えてみれば、合計値が減っているとは考え難い。従って、何らかの原因で
個数が増えている
のではないか。

大きく分けるとこれら３つについて、考える必要があるだろう。


そういえば、週数を増やして行くと、まだ日付が入力されていないセルが分析範囲に入ってきている。この辺が何か怪しい。
しかし、土曜日以外の曜日については、分析週数を増やして行っても、異常は起きない。
よくわからないが、土曜日だけがおかしいということで、曜日の判定に使っているWEEKDAY関数の動作を調査した方がよさそうだ。

３．調査する内容

１．範囲の設定について
分析範囲はOFFSET関数を使って可変にしてあったので、これを直接指定にして確認してみた。
しかし結果は変わらず。相変わらず同じ現象が起こった。
従って、範囲の設定については問題ないだろう。

２．日付の処理について、
土曜日だけおかしいということで、WEEKDAY関数を中心に調査してみた。
特に気になるのは、

WEEKDAY関数で未入力セルを参照するとどうなるのか？

というところだ。

４．調査結果

で、その結果が下の表だ。

なるほど。
未入力セルは日付シリアル値に変換するとゼロ扱いなのか。

そこまでは解るとして、なぜWEEKDAY(0)が土曜日(7)になるのか？

そこで今度は日付シリアル値の入力されたセルをベースに再調査した。

なお、##...は負の値や大きすぎる値を日付表示にしようとすると、こう表示される。ということらしい。
そして、その場合、WEEKDAY関数は#NUM!!エラーを吐くようだ。

さて、肝心の土曜日について見てみると・・・
・・・これは・・・
シリアル値”１”に対応する”1900/1/1”は日曜日。
だから、その前日のシリアル値”０”に対応する”1900/1/0”は土曜日。
という理屈だろうか？

実際のところは不明だが、おそらくWEEKDAY関数の内部処理の都合だろう。
というのも、想像するに、WEEKDAY関数の中身はただ日付シリアル値を7で割った余りを計算しているだけじゃないかと思うからだ。

・・・まあ、例外処理とかまで考えると、そう単純じゃなさそうだが、当たらずとも遠からずと言ったところか。

で、結局原因は、
１．分析範囲を広げていくと、日付未入力のセルが含まれてしまう。
２．日付未入力のセルは日付シリアル値０とエクセルは認識する。
３．日付シリアル値０は土曜日。
４．土曜日の平均値を求めるときの分母だけが増える。
これにより、
１．分析する範囲を週単位で増やして行くと、
２．土曜日だけ、
３．平均値が減ってい行く。

という現象が発生していたわけだ。

５．対策

さて、これで原因はわかった。
あとは、これをどう防ぐかだ。

WEEKDAY関数に０を与えたときの動きはどうしようもないので、０を与えないようにする必要がある。
一方、未入力セルだった場合には、WEEKDAY関数に渡せる引数が何かあるかと言えば・・・
日付シリアル値の範囲外の値を渡して意図的にエラーを発生させるか・・・
いや、そもそもWEEKDAY関数を使わないようにした方が簡単だろう。

というわけで、ISBLANK関数を使ってIF関数を入れ子にして、未入力セルの処理を分けることで対応することにした。

改良前：
{=AVERAGE(IF(D10<>TEXT(WEEKDAY(分析期間),"aaa"),"",分析範囲))}

改良後：
{=AVERAGE(IF(ISBLANK(分析期間),"",IF(D10<>TEXT(WEEKDAY(分析期間),"aaa"),"",分析範囲)))}
※D10="土"
これで、問題は無事に解決された。

６．疑問点浮上

と、ここまで書いて、別の問題が隠れていたことに、今更ながら気が付いた。それは、
例え、未入力セルが土曜日だと判断されても、そのセルに対応する売上個数の列も未入力セルなので、AVERAGE関数の仕様としては、そのセルは無視されて、平均値計算には影響しないはずだ。
と、いうことだ。

これは、別の角度からの調査が必要だな。

７．追加調査

AVERAGE関数の範囲指定には、配列数式を使っているので、この部分を抜き出して、売上個数の隣のＣ列に表示させてみた。

Ｃ列の式でこっちが期待しているのは、
土曜日以外の売上を""に置き換えて、土曜日の売上はそのまま。
ということだが、調査結果は、その期待通りにはなっていないな。

土曜日と判断されているＡ１２，１３に対応するＢ１２，１３は未入力なので、Ｃ１２，１３も未入力状態を期待していたが、ここは０となっている。
まあ、考えてみればただの＝を使った参照でも未入力セルを参照すると０となるので、それと同じか。
どーも、こっちの方が主原因のような気がするな。

８．結論

色々と調査したが、今回の現象の原因は、
売上個数の未入力セルに対する対策が不足していた。
ということだろう。
”AVERAGE関数は未入力セルは無視する”
ということで、そこから先をあまり考えていなかったせいだろう。
実際には、未入力セルを参照したセルがAVERAGE関数に渡っていたのにだ。
一番の問題と思っていたWEEKDAY関数の動作の方はオマケみたいなもんだ。

しかし、結局対策としては、

{=AVERAGE(IF(ISBLANK(分析期間),"",IF(D10<>TEXT(WEEKDAY(分析期間),"aaa"),"",分析範囲)))}

でも問題はない。
この対策は、偶然にも、売上個数未入力の対策にもなっている。
日付未入力セルに対応する売上個数セルも未入力となっているからだ。

というわけで、何とか解決できたな。

名前の定義の注意

シートに問題が生じたので、シートを丸ごとコピーして、そこでテスト・確認する。

なんて言うシチュエーションはよくあると思う。

しかし、そのシートに名前の定義されているエリアがある場合、ちょっと注意が必要だ。

１．シートをコピーしたとき名前の定義はどうコピーされるのか？

テストのため、Sheet1に範囲１という名前を定義して、範囲１の最大値を表示するような場合を考えて、これで説明することにする。

まずSheet1の名前の定義は下のようになっている。

次にSheet1をコピーしてSheet1(2)を作成。

この時の名前の定義は下のようになる。

これを見て明らかなように、”範囲１”という名前の定義が２つになっている。違いは、参照範囲のシート名。それと、最初に定義したものは範囲が”ブック”になっているが、シートコピー時にエクセルが自動的に作成した定義の方は範囲が”Sheet1(2)”となっているところだ。

そして、このSheet1(2)のMAX関数が使っている”範囲１”は新しいSheet1(2)の”範囲１”だ。

２．どんな問題が起きるのか？

実は上の例では問題は起こらない。

Sheet1を削除した場合に上のようにゴミが残るのと、”範囲１”が他のシートから参照できない程度だ。しかし、この他のシートから参照できないというのがまずい。

というのも、問題が起こるのは、”範囲１”を別シートから参照していた場合だ。
例えば、範囲１がSheet1に、MAX関数がSheet2にあって、その後Sheet1をコピーしてSheet1(2)を作成した場合。

はたして、このMAX関数が参照している”範囲１”はどっちの”範囲１”だろうか？
当然、元からあったSheet1の”範囲１”の方なのだが、見た目ではわからないし、以外と盲点だ。

そして、コピーしたSheet1(2)は念のためのバックアップで、変更修正自体は元のSheet1の方に対して行う場合、例えば範囲１の5を10に変更するような場合は、

とMAX関数は元のSheet1の範囲１を参照しているので問題は起こらない（この時は・・・というのも、何か問題が発生して、バックアップのSheet1(2)を使う必要になったときには、後述する場合と同じことだから。）

しかし、元のSheet1をバックアップとして残し、Sheet1(2)の方に変更修正を加える場合には問題が起こる。

と、意図した結果にならない（まあ、間違って意図しているわけだが）。

しかし、この場合は、すぐに何かおかしいと気が付くので、まだ被害は少ない。

問題なのは、一目ではわからないような変更を行った場合だ。例えばMAXではなくてMIN関数だった場合、結果は変わらないので、問題に気づきにくい。

そして、Sheet1(2)の変更を確定して、オリジナルのSheet1を破棄して、Sheet1(2)をＳｈｅｅｔ１と変更してしまった場合。これは最悪だ。

当たり前と言えばそうだが、MAX関数で使用していた”範囲１”は元のSheet1と共に消えてなくなっているので、下のようにREFエラーとなってしまう。
しかも、新Sheet1で定義されている（コピー時にエクセルが自動的に定義した）”範囲１”の範囲はSheet1となっていて、MAX関数が記述してあるSheet2では使えない。

ならば、新Sheet1の”範囲１”の範囲をブックにしてやれば・・・・

しかし、これが変更不可ときている。

こうなると打つ手なし。もはや、”範囲１”を一旦削除して、新たに定義し直すしかないだろう。
トホホ・・・

３．対策はあるのか？

というわけで、名前の定義を使用しているシートをコピーする場合、というかシートのコピーはするな。
コピーするならファイルごとコピーする。
これが正解のような気がする。

そう言ってしまうと身もふたもないので、ちょっと考えてみる。
・・・・こういうのはどうだろう？

とういう具合に名前の定義の参照範囲をINDIRECT関数で指定するというのはどうだろうか。

この状態でSheet1をコピーして、Sheet2のB3の内容を”Sheet1 (2)!$A$1:$A$5”としてみたところ、

あれ？
どうやらINDIRECT関数がエラーを吐いているようだ。
どうも、スペースとカッコがまずいみたいなので、シート名を変えることにした。

よし。動作しているようだ。
（Sheet1とSheet12を区別するため、Sheet12では最大値を10と変えてある）

名前の定義を直接修正しなくても良い分、うん。こっちの方がいいな。
どっちにしろ、こういうことが起こることを考慮しておく必要はあるな。