ひっそりとExcel関数のおさらい（表の縦・横を入れ替える）

今回は、表の縦・横を簡単に入れ替える方法を紹介します。

 目次

形式を選択して貼り付け（行列を入れ替える）

これが一番手っ取り早く、簡単です。
表をコピーし、貼り付けたい位置で、形式を選択して貼り付け →「行列を入れ替える」にチェック → ＯＫ
これだけです。

ただし、コピー元・コピー先は参照関係にはなっていないので、コピー元を変更したからといってコピー先が自動的に変更されるということはありません。むしろ、そのほうが都合いい！って場合には、この「形式を選択して貼り付け」が向いてます。

TRANSPOSE関数を使用する

=TRANSPOSE(配列)

 ※配列とは、元となる表を指します。表のセル範囲などで指定します。（これを配列引数という）

TRANSPOSE関数を使用して任意の範囲に、元となる表から縦・横を逆にしたクローンを生成します。

注意する点は、TRANSPOSE関数を適用する任意の範囲は、元となる表の行数・列数を逆にした範囲になるということです。

下の画像の元の表(B2:G8)は、7行・6列なので、クローンを生成する任意の範囲は、6行・7列にします。マウス等で選択中、左上に「6R×7C」のように表示されるので、わざわざ目で追って数える必要はありません。※Rは行、Cは列を表します

任意の範囲を選択できたら、数式バーに =TRANSPOSE(B2:G8) と入力し、Ctrl + Shift + Enter で確定します。

この、Ctrl + Shift + Enter で確定するという行為は、任意の範囲が一つの数式を共有することを意味します。このように確定された数式を、配列数式またはCSE数式（ Ctrl + Shift + Enter で確定するから）と呼び、数式が共有される範囲を配列範囲と呼びます。

数式バーには、配列数式であることを示すために、数式が中カッコで囲まれます。{=TRANSPOSE(B2:G8)}

これで、下の画像のように任意の範囲に縦・横 逆のクローンが生成されました。

TRANSPOSE関数で生成されたクローンは、元となる表を常に参照しているため、元の表に変更があればクローン側にも変更が反映されます。（ただし、書式は参照されないため、罫線や、文字の大きさなどは自分で設定する必要があります）

ひっそりとExcel関数のおさらい（乱数を発生させる）

今回は、エクセルで乱数を発生させる関数についてです。

 目次

RAND関数（0以上、1未満の乱数を生成する）

=RAND()
引数はありません。0～0.999999999999999の乱数を生成します。シート再計算（要するにシートに変化があった時）のたびに乱数が生成されます。

RANDBETWEEN関数（指定した範囲の整数の乱数を生成する）

=RANDBETWEEN(最小値,最大値)
最小値以上、最大値以下の整数の乱数を生成します。これも、シート再計算のたびに乱数が生成されます。

VBAで、RANDBETWEEN関数を使う

VBAで整数の乱数を生成するには、少しだけ工夫が必要です。
ズバっと、整数の乱数を生成するための関数はないので、Rnd関数（0以上、1未満の乱数を生成する） を応用して、例えば

Int((最大値 - 最小値 + 1) * Rnd + 最小値)

のようにする必要があります。

ん？なんのこっちゃ？

という人の為に解読を試みると･･････ 

の、ような感じになります。

うーん･･････なんかメンドくさいし、分かりにくいなぁと思う方は

VBAでも、WorksheetFunctionオブジェクトを介してExcel関数（の一部）を利用することができるので、シンプルに

WorksheetFunction.RandBetween(1, 9)

のように記述することもできます。
Randomizeによる、乱数系列の初期化もしなくてもいいようなので、楽チンです。

ぜひ、ご活用を。

ひっそりとExcel関数のおさらい（アラビア数字とローマ数字の相互変換）

アラビア数字をローマ数字に、ローマ数字をアラビア数字に変換する関数です。

ROMAN関数（アラビア数字をローマ数字に変換する）

=ROMAN(数値)
引数にアラビア数字の数値を指定します。

数値	返り値
1	I
2	II
3	III
4	IV
5	V
6	VI
7	VII
8	VIII
9	IX
10	X

ARABIC関数（ローマ数字をアラビア数字に変換する）

=ARABIC(文字列）
引数にローマ数字の文字列を指定します。=ARABIC("IX")のようにダブルクォーテーションで囲んで指定します。

文字列	返り値
I	1
II	2
III	3
IV	4
V	5
VI	6
VII	7
VIII	8
IX	9
X	10

何に使うか？うーん･･････。相互変換して遊ぶ（←ぇ）

ひっそりとExcel関数のおさらい（時間の切り上げ・切り捨て）

エクセルやVBAで、時間のデータを切り上げ・切り捨てするにはどうすればいいでしょうか？
うーん･･････。Round関数？DateAdd関数？
通常の数値と違い、年・月・日・時・分・秒 をプログラム上でどう扱うか悩む人は少なからずいると思います。（私もその一人です）
と、いっても時間を都合よく切り捨て・切り上げするための専用の関数はないので、何かしら別の関数で代用することになります。

時間データも元をたどれば、シリアル値という数値データであることを利用して、数値を切り上げ・切り捨てすることができる CEILING関数 と  FLOOR関数を使用しましょう。

目次

時間を切り上げる（CEILING関数）

=CEILING(数値,基準値)
CEILING関数は第1引数の数値を、第2引数に指定した基準値の倍数のうち一番近いものに切り上げします。
･･････と言葉で説明しても、とっても分かりにくいと思うので、簡単な例で示すと、
=CEILING(数値,5) であれば 

数値	返り値
1	5
2	5
3	5
4	5
5	5
6	10
7	10
8	10
9	10
10	10
11	15
12	15
13	15
14	15
15	15

というふうに、 5 きざみで切り上げされたものが、返り値として返ってきます。

では、ここから本題です。
この関数を使って、時間を切り上げるにはどうしたらいいでしょう？
時間はシリアル値で、24時間を 1 とし、それよりも細かい 時・分・秒 は小数点以下で表されます。12時間であれば 0.5 、1時間であれば 0.0416666666666667 というふうに。
なので、時間を1時間きざみで切り上げしたいのであれば、 =CEILING(ｼﾘｱﾙ値,0.0416666666666667) とすればいいことが分かります。
でも、この書き方はメンドウなので、 =CEILING(ｼﾘｱﾙ値,1/24) または、時間と認識できる文字列で =CEILING(ｼﾘｱﾙ値,"1:00") としましょう。
※以下、ｼﾘｱﾙ値は時間と表記します

5分きざみで切り上げする例
=CEILING(時間,5/(24*60))

時間	時間のシリアル値	返り値	しきい値［5/(24*60)のn倍］	n
0:11	0.007638888888888880	0:15	0.010416666666666700	3
0:12	0.008333333333333320	0:15	0.010416666666666700	3
0:13	0.009027777777777760	0:15	0.010416666666666700	3
0:14	0.009722222222222200	0:15	0.010416666666666700	3
0:15	0.010416666666666600	0:15	0.010416666666666700	3
0:16	0.011111111111111100	0:20	0.013888888888888900	4
0:17	0.011805555555555500	0:20	0.013888888888888900	4
0:18	0.012500000000000000	0:20	0.013888888888888900	4
0:19	0.013194444444444400	0:20	0.013888888888888900	4
0:20	0.013888888888888800	0:20	0.013888888888888900	4
0:21	0.014583333333333200	0:25	0.017361111111111100	5
0:22	0.015277777777777600	0:25	0.017361111111111100	5
0:23	0.015972222222222000	0:25	0.017361111111111100	5
0:24	0.016666666666666400	0:25	0.017361111111111100	5
0:25	0.017361111111110800	0:25	0.017361111111111100	5
0:26	0.018055555555555200	0:30	0.020833333333333300	6
0:27	0.018749999999999600	0:30	0.020833333333333300	6
0:28	0.019444444444444000	0:30	0.020833333333333300	6
0:29	0.020138888888888400	0:30	0.020833333333333300	6
0:30	0.020833333333332800	0:30	0.020833333333333300	6

こんなふうに、時間が、直近のしきい値を超えていなければ、直近のしきい値に切り上げされ、返り値となって返されます。なお、n（倍数）はユーザーが特に意識することはなく、関数が自動的に増減します。

時間を切り捨てる（FLOOR関数）

=FLOOR(数値,基準値)
FLOOR関数は第1引数の数値を、第2引数に指定した基準値の倍数のうち一番近いものに切り捨てします。

考え方は、CEILING関数と同じであり、「切り上げ」が「切り捨て」になっているだけなので省略します。

5分きざみで切り捨てする例
=FLOOR(時間,5/(24*60))

時間	時間のシリアル値	返り値	しきい値［5/(24*60)のn倍］	n
0:11	0.00763888888888888	0:10	0.00694444444444444	2
0:12	0.00833333333333332	0:10	0.00694444444444444	2
0:13	0.00902777777777776	0:10	0.00694444444444444	2
0:14	0.00972222222222220	0:10	0.00694444444444444	2
0:15	0.01041666666666660	0:10	0.01041666666666670	3
0:16	0.01111111111111110	0:15	0.01041666666666670	3
0:17	0.01180555555555550	0:15	0.01041666666666670	3
0:18	0.01250000000000000	0:15	0.01041666666666670	3
0:19	0.01319444444444440	0:15	0.01041666666666670	3
0:20	0.01388888888888880	0:15	0.01388888888888890	4
0:21	0.01458333333333320	0:20	0.01388888888888890	4
0:22	0.01527777777777760	0:20	0.01388888888888890	4
0:23	0.01597222222222200	0:20	0.01388888888888890	4
0:24	0.01666666666666640	0:20	0.01388888888888890	4
0:25	0.01736111111111080	0:20	0.01736111111111110	5
0:26	0.01805555555555520	0:25	0.01736111111111110	5
0:27	0.01874999999999960	0:25	0.01736111111111110	5
0:28	0.01944444444444400	0:25	0.01736111111111110	5
0:29	0.02013888888888840	0:25	0.01736111111111110	5
0:30	0.02083333333333280	0:25	0.01736111111111110	5

この表はCEILING関数の時と同じように見えますが（切り上げが、切り捨てになっただけ）、でも、この表はどこかがおかしいです。お気づきでしょうか？

FLOOR関数の不具合？

例えば、0:15 であれば、切り捨てられずに返り値が 0:15 となってほしいのですが、切り捨てられてしまっています。次の 0:20 、 0:25 、 0:30 も同じです。

なぜ、そうなるのか？
しきい値と、シリアル値を見比べると、しきい値のほうが僅かに大きいことが分かります。

なので、FLOOR関数さんは、「まだしきい値を超えてないので、切り捨てだね！」と判断して、一つ手前のしきい値で返り値を返してしまっているのです。
これは、厳密に言うとFLOOR関数の不具合ではなく、浮動小数点の演算誤差によって発生してしまっています。
FLOOR関数に限った話ではありません。

CEILING関数の場合にも、しきい値の誤差はありますが、切り上げ方向のため問題にならなかっただけです。

誤差があることはわかった。じゃあ、どうすればいいの？

方法はいくつかあるけど、例えば、第1引数が誤差分を確実に上回るようにしたらどうでしょう？

例：FLOOR(時間 + 1秒,5/(24*60))

1秒の箇所は、実際には 1/(24*60*60) や "00:00:01" と記述します。

VBAで使う際の注意点

VBAでも、ワークシート関数として CEILING関数 、 FLOOR関数 を使うことができます。ただし、第2引数を文字列 "0:05"とすることはできないようなので、下記のようにする必要があります。

• 直接数値で指定する：5/(24*60)
• 日付リテラルで指定する：#0:05#（自動的に #12:05:00 AM# のように変換されるが、意味は同じなので気にしなくてもいい）
• Date型の変数で指定する：事前に、変数 = "0:05" のように代入して引数に指定
• 関数で日付シリアル値に変換する：TimeValue("0:05")

セルを縦方向に結合

エクセルの標準ボタンには「横方向に結合」がありますが、「縦方向に結合」はありません。
どうしてないのかわかりませんが、個人的にその機能が欲しいので、ＶＢＡで作ってみます。

ググればありそうだけど、簡単そうなので、自分でやってみます。

Sub 縦方向に結合()
    Dim i As Integer

    For i = Selection.Column To Selection.Columns(Selection.Columns.Count).Column
        Range(Cells(Selection.Row, i), Cells(Selection.Rows(Selection.Rows.Count).Row, i)).Merge
    Next i

End Sub

※コードの使用方法

• SubからEnd Subまでをコピーし、標準モジュール等に貼り付けて使用して下さい。なお、マクロで実行した処理は「元に戻す」ことができません。実行前に一旦保存しやり直しのできる状態にしておいて下さい。標準モジュールにコードを貼り付けてマクロを使用する方法はこちら。

1. 縦方向に結合したい範囲を選択する
2. マクロを実行する

日付データ（シリアル値）をお好みの表示形式で文字列に変換する

今回は、日付データを一括で文字列に変換する簡単なマクロを作ったので紹介します。

まず、日付データを文字列として扱いたいのはどんな場合か？ について、軽く触れておきたいと思います。

このように、A列に日付データ、B列にコメントがあったとして

これを、C列に文字列結合 =A1&"は"&B1 すると

あれ？何だこの数値？(＠_＠;)

これは、日付データの実体はシリアル値だからであり、表に見えている 7月10日 などは、エクセルが私達に見せている仮の姿です。
表示形式を変えると、エクセルが 43291 のようなシリアル値に、表示形式ごとの仮面を被せて

7/10
2018/7/10
2018年7月
2018年7月10日
H30.7.10
平成30年7月10日
･･･等

のように、見えるようにしています。

このような日付データを、シリアル値ではなく、純粋な "7月10日" という文字列にしてから文字列結合を行うとうまくいきます。

では、日付データを文字列として扱うにはどうすればいいでしょう？
この場合、次のような方法が考えられると思います。

• A列の日付データを文字列に変換する
• TEXT関数を使って文字列結合する。（C列の式を =TEXT(A1,"m月d日")&"は"&B1 のようにする）

TEXT関数なんてチマチマ使ってられるか。バーロー！

という人のために、A列の日付データを一括で文字列に変換する という方法をマクロで提供しようと思います。（もちろんA列じゃなくてもOKです）
ただし、日付データを文字列に変換した場合、日付データとしての計算や判定ができなくなるので注意が必要です。

Sub 日付データを好きな書式で文字列に変換()
    Dim myRange As Range
    Dim n As Integer
    
    n = Application.InputBox(prompt:="書式を選んで下さい" & vbCrLf & vbCrLf & _
    "1:yyyy/m/d" & vbCrLf & "2:yyyy/m" & vbCrLf & "3:yy/m/d" & vbCrLf & _
    "4:m/d" & vbCrLf & "5:yyyy年m月d日" & vbCrLf & "6:yyyy年m月" & vbCrLf & _
    "7:yy年m月d日" & vbCrLf & "8:m月d日" & vbCrLf & "9:ggge年m月d日" & vbCrLf & _
    "10:ggge年m月" & vbCrLf & "11:ge年m月d日" & vbCrLf & "12:ge年m月" & vbCrLf & _
    "13:ge/m/d" & vbCrLf & "14:ge/m" & vbCrLf & vbCrLf & "(9～14は和暦です)" & _
    vbCrLf & vbCrLf, Type:=1)
        If TypeName(n) = "Boolean" Or n < 1 Or n > 14 Then
            Exit Sub
        End If
    
    For Each myRange In Selection.SpecialCells(xlCellTypeVisible)   '可視セルのみに処理を行う
        If myRange.Address = myRange.MergeArea(1).Address Then   '結合セルの場合は左上のセルのみ処理を行う
            Select Case n

            Case 1
                myRange.Value = "'" & Format(myRange.Value, "yyyy/m/d")
            Case 2
                myRange.Value = "'" & Format(myRange.Value, "yyyy/m")
            Case 3
                myRange.Value = "'" & Format(myRange.Value, "yy/m/d")
            Case 4
                myRange.Value = "'" & Format(myRange.Value, "m/d")
            Case 5
                myRange.Value = "'" & Format(myRange.Value, "yyyy""年""m""月""d""日""")
            Case 6
                myRange.Value = "'" & Format(myRange.Value, "yyyy""年""m""月""")
            Case 7
                myRange.Value = "'" & Format(myRange.Value, "yy""年""m""月""d""日""")
            Case 8
                myRange.Value = "'" & Format(myRange.Value, "m""月""d""日""")
            Case 9
                myRange.Value = "'" & Format(myRange.Value, "ggge""年""m""月""d""日""")
            Case 10
                myRange.Value = "'" & Format(myRange.Value, "ggge""年""m""月""")
            Case 11
                myRange.Value = "'" & Format(myRange.Value, "ge""年""m""月""d""日""")
            Case 12
                myRange.Value = "'" & Format(myRange.Value, "ge""年""m""月""")
            Case 13
                myRange.Value = "'" & Format(myRange.Value, "ge/m/d")
            Case 14
                myRange.Value = "'" & Format(myRange.Value, "ge/m")
                
            End Select
        End If
    Next myRange
End Sub

※コードの使用方法

1. SubからEnd Subまでをコピーし、標準モジュール等に貼り付けて使用して下さい。なお、マクロで実行した処理は「元に戻す」ことができません。実行前に一旦保存しやり直しのできる状態にしておいて下さい。標準モジュールにコードを貼り付けてマクロを使用する方法はこちら。
2. 文字列に変換したい日付データの入ったセルを選択します

   （変換前はシリアル値なので、右詰め表示です）

3. マクロを実行します
4. 表示されたInputBoxからお好きな書式を選んでOKします

   （変換後は文字列なので、左詰め表示です）

 そもそも書式記号が分からない！という人は、過去記事で触れていますので参考に。 

kouten0430.hatenablog.com

プルダウンで選択すると、シート内の指定セルにジャンプするイベントプロシージャ

今回は、プルダウンメニューから選んだ文字列と同じ文字列を含む、同一シート内のセルにジャンプする（画面の一番上に表示させる）イベントプロシージャを紹介します。

プルダウンメニューから文字列を選ぶ度に、同じ文字列を含むセルを

このように一番上に表示されるようにしたいと思います。

VBAでは、ある操作をトリガーとして自動的に実行されるプロシージャを「イベントプロシージャ」と呼びます。自動実行のトリガーとなる操作のことを「イベント」といい、今回は、プルダウンメニュー（が設置されたセル）の内容を変化させる度に、

シート内の指定範囲を検索し、検索に一致するセルを一番上に表示させる･･････。

というプログラムを自動実行させてみたいと思います。

ワークシートのセルがユーザー（または外部リンク）によって変更されたときに、プロシージャが自動的に実行されるようにするには、Worksheet_Change というイベントプロシージャを使用します。

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myRange As Range
    
    If Target.Address <> "$A$1" Then Exit Sub
    
    Set myRange = Range("A2:A1048576").Find(Target.Value, LookAt:=xlWhole)
    
    ActiveWindow.ScrollRow = myRange.Row
    
End Sub

※プログラムの解説

• Worksheet_Change という名前のイベントプロシージャを記述します。Worksheetというオブジェクトで、Change（変化）というイベントがあった時に自動実行されるプロシージャを示します。イベントプロシージャ名は、ユーザーが自由に変更することはできません。
• 引数の指定はイベントプロシージャごとに決まっており、Worksheet_Change では(ByVal Target As Range)とします。ワークシート内で変更があったセル(Range)が Target に格納されます。変数名の Target は任意ですが、引数の渡し方を指定する ByVal（※1）と、変数の型を指定する As Range は変更不可です。
• Ifで、変更があったセルのアドレスが$A$1（つまり、プルダウンメニューを設置したセル）以外であった場合は、プロシージャを終了し、何もしません。
• Findメソッドで、A2:A1048576（つまり、ジャンプ先の見出しが存在するセル範囲）を、プルダウンメニューで選択した文字列で検索し、一致したセル(Range)を返します。LookAt:=xlWhole の引数で、検索条件を完全一致にしています。
• ActiveWindow.ScrollRowで、検索に一致したセルのある行を上端に表示させます。ウィンドウ枠を固定している場合、 固定領域は対象外となります。

※コードの使用方法

• Private SubからEnd Subまでをコピーし、イベントが発生するオブジェクトモジュールに貼り付けて使用して下さい。つまり、book1のSheet1で発生するイベントに対してであれば、VBAProject(Book1) － Microsoft Excel Objects － Sheet1 モジュールに記述します。標準モジュールや、個人用マクロブックに記述しても実行されません。

  

同様の処理を、Workbook_SheetChangeというイベントプロシージャでも作成することができます。その場合は、ThisWorkbookモジュールにコードを記述します。Workbook_SheetChangeは、Workbookというオブジェクト（つまり、Book内の全てのシート）で、SheetChangeというイベントがあった時に自動実行されるプロシージャです。（Worksheet_Changeは一つのシートのみが対象）

※1
ByValは、引数が値渡しになります。参考に、省略またはByRefは、引数が参照渡しになります。ValはValue（値）の略で、RefはReference（参照）の略。
値渡しと参照渡しについては、thom (id:t-hom) さんのこちらの記事でわかりやすく解説されています。

thom.hateblo.jp