2015年3月29日日曜日

仮想マシンのスナップショットのオプション

VMwareの仮想マシンスナップショットだが、オプションが2つある。これらの違いを調べてみた。というかほとんど参考URLのKBに記載されているので、それの補足的な情報になる。



パワーオフ仮想マシンのスナップショット

後述するが、上記2つのオプションは、パワーオン状態でなければ実施できない。逆にパワーオフ状態の仮想マシンの場合は、ディスクのスナップショットが取得されるだけであり、ファイル名としては以下が作成される。

◆vSphere Clientのデータストアブラウザで確認した場合
<VM名>.vmdk   ←元の仮想ディスク
<VM名>-000001.vmdk   ←差分ファイル
<VM名>.vmsd   ←スナップショットのメタデータ
<VM名>-Snapshot<番号>.vmsn   ←メモリのスナップショット情報(28KB程度と非常に小さい)

データストアブラウザで見えるファイル名と実体はのファイル名は実は異なる。実体のファイル名は以下の通りになる。以降は実体のファイル名で記載する。

◆ESXで直接確認した場合
<VM名>-flat.vmdk   ←元の仮想ディスク
<VM名>-000001-delta.vmdk   ←差分ファイル
<VM名>.vmsd   ←スナップショットのメタデータ
<VM名>-Snapshot<番号>.vmsn   ←メモリのスナップショット(28KB程度と非常に小さい)

仮想マシンのメモリのスナップショット

その名の通り、仮想マシンのディスクだけではなく、メモリ情報もスナップショットを取る機能。メモリ情報なので、パワーオフの仮想マシンでは取得不可(オプションを選択できない)。

メモリ情報も含めて保存されているので、この機能を用いたスナップショットを復元した場合は、仮想マシンはパワーオンの状態で戻る(逆に言えばこのオプションを有効にしない場合のスナップショットの復元後はパワーオフで戻る)。

この処理はメモリのスナップショットを取得するため、割り当てメモリが大きい程時間を要し、ストレージのパフォーマンスにもよると思うが、メモリ6Gの仮想マシンで5分程度掛かった。

ファイルとしては以下ファイルが大きくなるのが違い。

<VM名>-Snapshot<番号>.vmsn   ←メモリのスナップショット(メモリと同程度(6GB))


静止ゲストファイルシステム(VMware Toolsのインストールが必要)

VMware Toolsが仮想マシンのOSのディスク静止点を確保してスナップショットを取得する機能。VMware Toolsの起動が条件となるため、パワーオフの仮想マシンでは取得不可(オプションを選択できない)。

この機能は、VMware ToolsがWindowsのVSS(ボリュームシャドウコピーサービス)やSyncドライバなる機能と連携して、ファイルシステムレベルでのデータの静止点を確保してからバックアップを取得するという機能となる。実際にWindowsの仮想マシンで本オプションを有効にしてスナップショットを取得すると、仮想マシンのアプリケーションイベントログに以下のようなVSS関連のログが出力されていた。

 ソース:ESENT
 説明:lsass (672) シャドウ コピー インスタンス 2 を開始しています。
    これは、完全シャドウ コピーとなります。

メモリのスナップショットに比べれば時間は短く、20秒程度でスナップショット取得は完了した。

データストア上のファイルとしては以下ファイルが生成される。VSSのマニフェストファイルということで、VSSのメタデータが内容に含まれるらしい。

<VM名>-vss_manifests<番号>.zip

PowerCLIでスナップショットを取得する

スナップショット取得はNew-Snapshotコマンドレットを使う。メモリのスナップショット取得は"-Memory"、静止ゲストファイルシステムの取得は"-Quiesce"(※静止という意味)で指定する。両方取りたければ以下の通り実行する。$vmsnapに結果を代入している理由は、後で消したりする操作を簡単にするため。

$vmsnap = New-Snapshot -VM "VM_Name" -Name "Temporary Snapshot" -Memory:$true -Quiesce:$true

蛇足だが、スナップショットを削除する場合は以下を実行。削除処理は確認プロンプトが表示されるので、"-Confirm:$false"で表示させないようにする。

Remove-Snapshot -Snapshot $vmsnap -Confirm:$false

参考URL

VMware ESX の仮想マシンのスナップショットについて (1033239)
http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1033239

vSphere PowerCLI Reference : New-Snapshot
https://www.vmware.com/support/developer/PowerCLI/PowerCLI51/html/New-Snapshot.html
2015年3月25日水曜日

OSのネットワーク(TCP)のタイムアウト値とディスクのタイムアウト値

OSのタイムアウト値を覚えておくと、障害設計や障害発生時に役立つ。例えば、ストレージのコントローラー障害でI/Oが1秒止まった場合の影響有無を考えた場合、ディスクタイムアウト値が1秒以上であれば、I/Oが再開されるので「影響無し」と判断することができたりする。

これはネットワークも同様に考えることができる。ということで、OSのネットワークとディスクのタイムアウト値を調べてみた。

ネットワーク(TCP)のタイムアウト値

Windowsの場合

再送回数に関しては、デフォルト5回との情報がMicorosoftのKBにあるが、5回ではなく3回との情報も多く存在し正しい情報がよくわからない状況。そこで、実際にWiresharkでパケットキャプチャしつつ、Telnetクライアント使ってSYNパケットの再送回数とタイムアウト時間(接続を諦める時間)を確認してみた。


タイムアウト時間の初期値は3秒、再送回数は2回のようだ。

つまりWindowsの場合、タイムアウトしてしまう最大の時間は、

 0秒(最初のSYN)
 +  3秒(1回)
 +  6秒(2回)
 + 12秒(タイムアウト)
 = 21秒

となる。

Linuxの場合

仕組みはWindowsと同じで初期値3秒でリトライ毎に時間が倍々になっていく。リトライ回数は以下で設定されている。

   /proc/sys/net/ipv4/tcp_syn_retries

このファイルをcatで見ると、デフォルトでは"5"になっているようだ。

つまりLinuxの場合、タイムアウトしてしまう最大の時間は、

 0秒(最初のSYN)
 +  3秒(1回)
 +  6秒(2回)
 + 12秒(3回)
 + 24秒(4回)
 + 48秒(5回)
 + 96秒(タイムアウト)
 = 189秒

となる。

ディスクのタイムアウト値

Windowsの場合

以下レジストリキーで設定されている。
HKEY_LOCAL_MACHINE¥System¥ CurrentControlSet¥Services¥Disk¥TimeOutValue
デフォルト:60秒 (Windows Server 2008 R2、Windows7、Windows 8.1環境で確認)
※レジストリキーの場所だが、"Disk"が"disk"の場合も有り



Linuxの場合

若干乱暴だが以下コマンドで調べることができるようだ。

   # cat /sys/block/sd*/device/timeout
   180

ディスクタイムアウトは180秒であることがわかる。これは後述するが、仮想マシンのVMware Toolsをインストールした際に変更されている。バージョン毎にデフォルト値は違うようだが、手元にあったCentOS 5.9 64bit (kernel:2.6.18-348)で確認した場合は、60秒であった。

仮想環境の場合

仮想環境では、1つのストレージに対し複数の仮想マシンのI/Oが発生するという特異な環境であることから、ストレージのパフォーマンスが安定しないことがある。そのため、VMware Toolsをインストールする際に、タイムアウト値が高めになるよう設定変更されるようだ。

変更される値は以下の通り。

   Windows:60秒 (デフォルトと変化無し)
   Linux:180秒


参考URL

TCP/IP の再送タイムアウトの最大値を変更する方法
https://support.microsoft.com/en-us/kb/170359/ja

ディスクが SAN データストアに配置されていると、Windows 仮想マシンのパフォーマンスが不安定になる (2080692)
http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=2080692

Linux 2.6 仮想マシンのディスク タイムアウト値を増やす (2076978)
http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=2076978




2015年3月19日木曜日

PDFのパスワードを解除して保存し直す方法

パスワード付きPDFっていちいち開くのが面倒だし、将来的にパスワードを紛失して開けなくなってしまうリスクがあるので、解除して保存することが多い。

基本的な方法としては、以下の流れで解除を実施する方法が多い。
1. PDFを開く
2. 印刷
3. 印刷データをPDFに変換するソフトに流す
3の「印刷データをPDFに変換するソフト」については、PrimoPDFやCubePDFといったフリーソフトが有名なようだが、PDFの内容によっては変換が上手く行かずエラーになってしまったり、容量が大きくなってしまったりすることがあるのが難点だった。

実際にCubePDFをインストールして、とある資料のパスワードを解除してPDFにしようとしたら、エラーが出て失敗してしまった。

そこで、Google Chromeの出番である。以下の流れで実施すると、画質・容量共に原本と遜色ないサイズにて出力することができた。
1. Google Chromeを開く
2. 該当のPDFをChromeへドラッグ&ドロップ
3. 印刷
4. 「PDF」に保存
PDF変換ソフトで上手く行かない場合は試してみると良いかも。

2015年3月14日土曜日

vmware.logが肥大化する不具合と確認方法 (VMware KB:2078823)

事の発端は以下のKBになる。最新パッチ当てれば直る不具合ではあるが、vmware.logに合計500文字程度のログが1秒に1回出力されるという不具合となる。

Virtual machine with multiple user login session fails with the error: GuestRpc: Channel X, conflict: guest application toolbox-dnd tried to register, but it is still registered on channel Y (2078823)
http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=2078823

この不具合自体は仮想マシンの動作に影響は無いようなので問題にはならないが、ログがそこそこ大きくて、500文字(Byte) x 3600秒 x 24時間 = 41MBが毎日vmware.logに出力されることになり、放っておくとデータストアの容量逼迫に繋がる可能性がある。

解決方法はKBに乗っているので割愛するとして、この事象の発生条件、確認方法などは調べたので記載する。

発生条件

vmtoolsdが3プロセス以上起動すると発生する。このvmtoolsdだが、OS起動時に1個プロセスが立ち上がり、さらにユーザーがOSのGUI画面にログインする毎に1個増えて、ログオフすると消える仕様のようである(Linuxのssh接続ではvmtoolsdは増えないようだ)。

GUI画面にログインするという条件から、特にWindowsで起きやすい。なぜかというと、通常Windows Serverでは、標準機能で2ユーザーの同時ログインを許可してしまうからである。なので、Windows Serverにログインした後、ログオフをきちんとしない(例えばRDPを「×」ボタンで閉じる)と、いつの間にか発生条件を満たしてしまい、そのまま長い期間放置してしまいログが肥大化してしまうことに繋がる。

確認方法

まずvmware.logの仕様だが以下の通り。ログを出力させないことや保存世代の変更はvmxファイルを修正すればできるようだ。
  • 仮想マシンのONのタイミングでローテートされる
  • デフォルトで6世代(vmware.log、vmware-xx.log x 5世代)保存

vmware.logは仮想マシンの保存ディレクトリ毎に作られるので、1つ1つデータストアブラウザで確認するのはとても手間なので、直接ESXi Shellにログインして、コマンドで確認すれば手っ取り早い。

単純なコマンドは以下の通り。
ls -lhS /vmfs/volumes/*/*/vmware*.log
データストアは /vmfs/volumes配下にUUID名のディレクトリが実体として存在するが、同ディレクトリにはシンボリックリンクでデータストア名ので名付けられたリンクも存在する。従って、上記コマンドで確認すると全てのログが2重で表示されて見づらくなる。

表示を絞りたければ以下の通りgrepすれば良い。

データストア名で表示

ls -lhSL /vmfs/volumes/*/*/vmware*.log | grep -v -e '/vmfs/volumes/.\{8\}-.*/'

-rw-r--r--    1 root     root     1006.2M Mar 11 07:14 /vmfs/volumes/DatastoreA1/VM1/vmware.log
-rw-r--r--    1 root     root      312.0M Mar 11 07:14 /vmfs/volumes/DatastoreA1/VM2/vmware.log
-rw-r--r--    1 root     root        6.8M Jan 15 09:10 /vmfs/volumes/DatastoreA1/VM3/vmware-8.log
-rw-r--r--    1 root     root        2.4M Jul  8  2014 /vmfs/volumes/Datastore01/VM4/vmware-27.log

UUIDで表示

ls -lhSL /vmfs/volumes/*/*/vmware*.log | grep -e '/vmfs/volumes/.\{8\}-.*/'
-rw-r--r--    1 root     root     1006.2M Mar 11 07:15 /vmfs/volumes/5243b476-8035a06c-582e-d89d6714f0b8/VM1/vmware.log
-rw-r--r--    1 root     root      312.1M Mar 11 07:14 /vmfs/volumes/5243b476-8035a06c-582e-d89d6714f0b8/VM2/vmware.log
-rw-r--r--    1 root     root        6.8M Jan 15 09:10 /vmfs/volumes/5243b476-8035a06c-582e-d89d6714f0b8/VM3/vmware-8.log
-rw-r--r--    1 root     root        2.4M Jul  8  2014 /vmfs/volumes/51d55cb3-dbe9bff0-0eeb-d89d6714e6d5/VM4/vmware-27.log

参考URL

VMware ESX/ESXi を操作するときのディスクの識別 (2078761)
http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=2078761

vmware.log のログ ローテーションとログ オプション (2077202)
http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=2077202
2015年3月12日木曜日

Tera TermでDCUIを使う方法

DCUI(Direct Console User Interface)はESXサーバーに直接ログインしなければ使えないと思っていたら、実はESXi Shellからも呼び出して使えるらしい。

参考URLに全て記載されているが、以下のコマンドで使える。直接ログインして使うDCUIの場合は「Alt + F1」でESXi Shellを呼び出すが、この場合は「Ctrl + C」なので注意。

# dcui

lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqk
x                                                                              x
x                                                                              x
x                                                                              x
x                                                                              x
x       VMware ESXi 5.1.0 (VMKernel Release Build 2000251)                     x
x                                                                              x
x       HP ProLiant DL360p Gen8                                                x
x                                                                              x
x       Intel(R) Xeon(R) CPU E5-2620 0 @ 2.00GHz                               x
x       32 GiB Memory                                                          x
x                                                                              x
x                                                                              x
x       Download tools to manage this host from:                               x
x       http://esx01/                                                          x
x       http://192.168.100.1/ (STATIC)                                         x
x                                                                              x
x                                                                              x
x                                                                              x
x                                                                              x
x                                                                              x
x                                                                              x
x <F2> Customize System/View Logs                      <F12> Shut Down/Restart x
mqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj

# <- Ctrl + Cで戻る

参考URL

Accessing Direct Console User Interface (DCUI) from an SSH session (2039638)
http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=2039638


2015年3月10日火曜日

Workgroup環境でのWindowsの時刻同期の設定方法

Linuxはntpd使って同期設定をきちんとしてあげれば高い精度で時刻を維持してくれるが、Windowsを使って、特にドメインに所属しないで使うWorkgroup環境の場合は、Windows Timeサービス(=W32Time)をデフォルトの設定で使うだけでは、イマイチな精度でしか同期されない。

Windows Timeサービスの仕様と精度を上げる方法について記載する。

Windows Timeサービス(W32Time)の仕様

まず、前提としてMicrosoftのKB939322から以下の文言を引用する。
ネットワーク上のノード間の W32Time サービスの精度は、保証およびサポートされません。W32Time サービスは、時間が重要となるアプリケーションのニーズを満たす完全な機能を備えた NTP ソリューションではありません。W32Time サービスは、主に次の手順を実行するように設計されています。

・Kerberos v5 認証プロトコルが正しく動作するようにします。
・クライアント コンピューターに緩やかな同期時刻を提供します。

W32Time サービスは、1 ~ 2 秒の範囲で同期時刻を確実に維持できません。このような許容誤差は、W32Time サービスの設計仕様に含まれていません。

ntpdの発想とは全く異なることがわかると思うが、簡単に言うと数秒のずれは平気で起きるし、Kerberos認証の時刻のずれの許容時間は5分なので±5分のずれは許容するということ。この発想を考えると、間違ってもWindows ServerをNTPの時刻参照先サーバーとして構築などしてはいけない。

また、Active Directory環境とWorkgroup環境で時刻同期の内容も異なるので、以下に違いを記載する。

Workgroup環境

「タスクスケジューラ」→「タスクスケジューラ ライブラリ」→「Micrsoft」→「Windows」→「Time Synchronization」に「SynchronizeTime」というタスクがあり、以下の通りとなっている(※Windows7の場合)。
・トリガー:2005/1/1以降毎週日曜日、1:00に起動
・操作:%windir%\system32\sc.exe start w32time task_started
・オプション:スケジュールされた時刻にタスクを開始できなかった場合、すぐにタスクを実行する
つまり、日曜1:00または、日曜1:00を過ぎた初回起動時に一度同期するがその後は次の日曜1:00まで同期しない。はっきり言って、Windows標準設定では時刻同期にやる気は無い。また、sc.exe start w32time task_startedの"task_started"の意味はw32timeのサービスを開始して同期が取れたら自動でサービスを停止する引数となり、同期を取ったらサービスは停止状態になる。

さらにもう1つ面倒な設定が存在する。それが「トリガーサービス」となる。トリガーサービスは、サーバー起動時に動作する機能で、その際のシステムの状態をトリガーにしてサービスの起動・停止を判断して実行する機能となる。w32tmの場合は以下の通り設定されている。
・ドメイン参加:w32tmサービス開始
・ドメイン非参加:w32tmサービスを停止
ドメイン非参加のWorkgroup環境では、トリガーサービスによりサービスが起動していても停止されてしまう。

Active Directory環境(概要)

今回はWorkgroup環境の説明がメインなので概要だけ。

Active Directoryでは、ドメインへのログイン認証にKerberos認証を使うためドメインコントローラーとメンバー間で時刻のズレを±5分に抑える必要がある。そのため、ドメインコントローラーとメンバー間では以下の通り同期が機能する。
1. ドメインコントローラーのPDCエミュレーターがドメインのタイムサーバーとなる
2. 他のドメインコントローラーはPDCエミュレーターのドメインコントローラーと同期する
3. メンバーはログイン先のドメインコントローラーと同期する
さらに同期間隔もWorkgroup環境のように週1回ということはなく、もっと短い間隔(1時間に1回)で行われるようだ。

Workgroup環境でも精度をそこそこ上げる方法

以下の観点で精度を上げる。
1. W32Timeサービスを常に開始状態にする
2. トリガーサービスを停止する
3. 同期間隔を短くする
4. (おまけ)時刻のズレを確認するコマンドを覚えておく
順番に対応しよう。

1. W32Timeサービスを常に起動させておく

「サービス」の画面で「Windows Time」を「手動」→「自動」または「自動 (遅延開始)」に変更する。

※なお、「自動 (遅延開始)」にすればトリガーサービスによるW32Timeサービス停止が処理された後に開始されるため、「2. トリガーサービスを停止する」を実施しなくても、サービスは開始状態で維持できる可能性があるが、タイミングの問題で上手くいかないこともあり得るので、オススメはできない

2. トリガーサービスを停止する

参考URL(http://support.microsoft.com/kb/2385818/ja)に記載の方法で停止する。
sc triggerinfo w32time delete   ←設定削除コマンド
sc qtriggerinfo w32time      ←確認コマンド
なお、タスクスケジューラの「SynchronizeTime」のタスクについては、W32Timeサービスが開始状態であれば、そもそも動作しない(サービスは既に起動している旨のメッセージが出るだけ)になるため、特に対応は不要となる。

3. 同期間隔を短くする

以下レジストリをいじる。
HKEY_LOCAL_MACHINE¥SYSTEM¥CurrentControlSet¥Services¥W32Time¥TimeProviders¥NtpClient\

SpecialPollInterval (REG_DWORD)
604800→3600 (10進数)
※604800秒(7日間)→3600秒(1時間)に変更
設定を確認するコマンドは以下の通り。
w32tm /query /configuration

4. (おまけ)時刻のズレを確認するコマンドを覚えておく

ntpdであればntpq -pのようなコマンドを覚えておくと同期状態が確認できて便利。
w32tm /monitor /computers:[タイムサーバー]   ←時刻のズレを確認するコマンド

C:\>w32tm /monitor /computers:192.168.100.100
192.168.100.100[192.168.247.100:100]:
    ICMP: 1ms 遅延
    NTP: -0.1615824s ローカル コンピュータの時刻からのオフセット        RefID: 'LOCL' [0x4C434F4C]
        階層: 1
前述のとおり、そもそもW32Timeに正確な時刻同期を求めるのも酷なので、とりあえず同期処理が成功していることを確認するのであれば、以下コマンドでもOK。
w32tm /query /status      ←前回の同期状況を確認するコマンド

閏インジケータ: 0 (警告なし)
階層: 2 (二次参照 - (S)NTP で同期)
精度: -6 (ティックごとに 15.625ms)
ルート遅延: 0.0312500s
ルート分散: 0.5493823s
参照 ID: 0xC0A8F73B (ソース IP:  192.168.100.100)
最終正常同期時刻: 2015/03/09 21:07:46ソース: 192.168.100.100,0x9
ポーリング間隔: 15 (32768s)

---

w32tm /query /status /verbose   ←詳細表示

閏インジケータ: 0 (警告なし)
階層: 2 (二次参照 - (S)NTP で同期)
精度: -6 (ティックごとに 15.625ms)
ルート遅延: 0.0312500s
ルート分散: 0.5493823s
参照 ID: 0xC0A8F73B (ソース IP:  192.168.100.100)
最終正常同期時刻: 2015/03/09 21:07:46ソース: 192.168.100.100,0x9
ポーリング間隔: 15 (32768s)

フェーズ オフセット: -0.0504310s
クロック レート: 0.0156252s
State Machine: 2 (同期)
タイム ソース フラグ: 0 (なし)
サーバーのロール: 0 (なし)
最終同期エラー: 0 (コマンドは正しく完了しました。)最終正常同期時刻からの時間: 2022.3559758s

参考URL

高精度の環境に向けた Windows タイム サービスの構成を目的とするサポート範囲
http://support.microsoft.com/kb/939322/ja

Windows Server 2008 の Windows タイム サービスとこれにより発生するインターネット通信
https://technet.microsoft.com/ja-jp/library/cc731790%28v=ws.10%29.aspx

Windows 7 および Windows Server 2008 R2 のスタンドアロン環境で Windows Time サービスが自動的に起動しない
http://support.microsoft.com/kb/2385818/ja

2015年3月7日土曜日

Excelで空行を飛ばして上にある数字+1する数式

タイトルだけみると何のことかわかりづらいが、要するに以下の様な動作をする数式となる。空行や文字列のセルは無視して、該当セルの直前にある数字を見つけて、その数字をインクリメントする数式で、飛び飛びで連番を作る必要がある場合に便利。


数式は以下の通り。長くてわかりづらいので、分解して説明する。
=INDEX(INDIRECT(ADDRESS(1,COLUMN(),4,1)):INDIRECT(ADDRESS(ROW()-1,COLUMN(),4,1)),MATCH(MAX(INDIRECT(ADDRESS(1,COLUMN(),4,1)):INDIRECT(ADDRESS(ROW()-1,COLUMN(),4,1)))+1,INDIRECT(ADDRESS(1,COLUMN(),4,1)):INDIRECT(ADDRESS(ROW()-1,COLUMN(),4,1)),1))+1

数式で使用している関数の説明

まず使っている数式の説明。

INDEX関数

INDEX(配列, 行番号, 列番号, 領域番号)
説明:配列で指定した場所から(行番号, 列番号)の位置にある情報を取り出す関数。例えば、図で示したExcelシートの場合、「=INDEX(A1:A4, 3, 1)」とすれば、A3にある数字「2」が返ってくる。

ROW関数

ROW(参照)
説明:ROW()とするとExcelの行番号を返す関数。

COLUMN関数

COLUMN(参照)
説明:COLUMN()とするとExcelの列番号を返す関数。

ADDRESS関数

ADDRESS(行番号, 列番号, 参照の型, 参照形式)
※「参照の型」は以下の通り。
 1:絶対参照(例:$A$1) (※省略時)
 2:行は絶対、列は相対(例:A$1)
 3:行は相対、列は絶対(例:$A1)
 4:相対(例:A1)
※「参照形式」は以下の通り。
 1:A1形式 (※省略時)
 0:R1C1形式
説明:行番号と列番号を指定すると、セルの場所を文字列として表示する関数。例えばA5のセルに「=ADDRESS(ROW()-1,COLUMN(),4,1)」と入れれば「A4」という文字列が返ってくる。

INDIRECT関数

INDIRECT(参照文字列, 参照形式)
※「参照形式」は以下の通り。
 1:A1形式 (※省略時)
 0:R1C1形式
説明:参照文字列にExcelの数式を入れると、文字列ではなく数式として処理する関数。

MATCH関数

MATCH(検査値, 検査範囲, 照合の型)
※照合の型
 1:検索値以下の最大の場所を返す(正しく使う場合、値は昇順で並べておく)
 0:検索値に等しい最初の場所を返す
 -1:検索値以上の最小の場所を返す(正しく使う場合、値は降順で並べておく)
説明:検査範囲から検査値の値を検索し、相対的な場所を返す関数。

数式を分解して説明

A14のセルに本数式を入れた場合で考えてみる。
=INDEX(INDIRECT(ADDRESS(1,COLUMN(),4,1)):INDIRECT(ADDRESS(ROW()-1,COLUMN(),4,1)),MATCH(MAX(INDIRECT(ADDRESS(1,COLUMN(),4,1)):INDIRECT(ADDRESS(ROW()-1,COLUMN(),4,1)))+1,INDIRECT(ADDRESS(1,COLUMN(),4,1)):INDIRECT(ADDRESS(ROW()-1,COLUMN(),4,1)),1))+1
まず、ADDRESS関数を変換してみる。
=INDEX(INDIRECT(A1):INDIRECT(A13),MATCH(MAX(INDIRECT(A1):INDIRECT(A13))+1,INDIRECT(A1):INDIRECT(A13),1))+1
INDIRECT関数にて文字列を数式として扱うよう変換する。ここでだいぶスッキリする。
=INDEX(A1:A13,MATCH(MAX(A1:A13)+1,A1:A13,1))+1
MAX関数を計算する。最大値はA7の"4"になるため、4+1=5になる。
=INDEX(A1:A13,MATCH(5,A1:A13,1))+1
MATCH関数を計算する。ここがミソで、検索値は最大値+1の"5"となり、検索範囲において一致する値は絶対に無いことになる。この場合、MATCH関数では「検索値以下の最大の行の位置」が返されるので、結果的に"10"行が返ってくることになる。
=INDEX(A1:A13,10)+1
INDEX関数を最後に計算して完了。A10に入っている"1"の値が取り出され+1されることになる。
=A10+1=1+1=2

もうちょっとシンプルなやつ

単純に連番にするだけなら、以下でもできる。単純な連番なので、途中で数字を1からリセットするような動きにはならないので注意。
=MAX(INDIRECT(ADDRESS(1,COLUMN(),4,1)):INDIRECT(ADDRESS(ROW()-1,COLUMN(),4,1)))+1

人気の投稿