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

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への変更は行を追加したときにエクセルが自動的にやってくれる。
手作業で変更したのは除数を２から３にしただけ。

以上、今回はおしまい。