2023/09/26
【Excel】深夜退社を考慮した勤務時間の求め方

【Excel】深夜退社を考慮した勤務時間の求め方

以下のような出社時刻と退社時刻、休憩時間を管理した表があります。
この表のD列の勤務時間を求める方法について解説していきます。

時刻はシリアル値という数値で管理されています。

>シリアル値とは

そのため、時刻の差は引き算で求めることができます。
単純に勤務時間を求める式は以下のようになるかと思います。

勤務時間 = 退社時刻 - 出社時刻 - 休憩時間

こちらの計算式でD列を埋めてみます。

=B3-A3-C3

そのようにすることで、勤務時間を求めることができるのですが、退社時刻が深夜の場合は求めることができません。
深夜の時刻を翌日の値として入力している場合は、求めることができますが、直接「1:30」などと入力している場合は求めることができません。

求めることができない理由は、退社時刻より出社時刻の方が大きな数値となってしまい、「退社時刻 – 出社時刻 – 休憩時間」の計算結果がマイナスになってしまうためです。

以上の理由から、退社時刻よりも出社時刻の方が大きい場合は、特殊な計算をする必要があります。
特殊な計算とはいっても複雑なものではありません。

以下の条件を加えるだけで、深夜退社でも正しく計算することができるようになります。

『出社時刻が退社時刻よりも大きい場合は、計算結果に1を加え、それ以外は0を加える

シリアル値の概念を理解している場合は、上記の条件式のイメージがしやすいかと思います。
1日はシリアル値で1と表現され、時間は1をさらに細かくした値です。

出社時刻が退社時刻よりも大きい場合は、本来、退社時刻の方が大きくなければいけないため、深夜時刻が入力されていることが考えられます。
深夜時刻とは、正確には翌日の朝になります。

そのため、1を加えて、翌日の時間として計算させるという理屈になります。

3行目の数式に条件式を加えると以下のようになります。

// =IF(論理式, [値が真の場合], [値が偽の場合])
=B3-A3-C3+IF(A3>B3,1,0)

反映させると以下のように求めることができます。

時刻や日付の計算をする上では、シリアル値の仕組みから考えると、徐々に解決策が思い付くようになるかと思います。

2023/09/25
【Outlook・Word】定型のあいさつ文を入力

【Outlook・Word】定型のあいさつ文を入力

OutlookやWordでの文章作成において、以下のような「季節に合わせた定型文」を簡単に入力する機能が用意されています。

・寒露の候、貴社いよいよご清祥のこととお慶び申し上げます。平素は格別のお引き立てをいただき、厚く御礼申し上げます。
・霜寒の候、貴社いよいよご隆盛のこととお慶び申し上げます。平素は当店を御利用いただき御厚情のほど、心より御礼申し上げます。

こちらでは、Outlookの画面にて定型文の入力方法について解説していきますが、Wordでも同じ操作で入力することができます。

まず初めに、Outlookの場合は、メッセージの入力画面を開き、入力したい位置にカーソルを移動させます。

次に、[挿入]タブ内の[あいさつ文の挿入]を選択します。
※[起こし言葉]と[結び言葉]についても後半に解説しております。

[あいさつ文の挿入]を選択すると、以下の画面が表示されます。
こちらより、月を指定することで、季節に合った、定型文を簡単に入力することができます。

先ほどの画面で、[OK]を選択し確定すると、以下のように入力することができます。


[補足] 起こし言葉と結び言葉とは以下のような言葉を指します。

起こし言葉:本題に入る取っ掛かりとして用いられる言葉
結び言葉:本題の最後の締めに使われる言葉

[起こし言葉]を選択すると、以下の画面より[起こし言葉]を入力することができます。

[結び言葉]を選択すると、以下の画面より[結び言葉]を入力することができます。

あいさつ文を入力する際に、「失礼がないか、間違っていないか?」などと悩まれる方の場合、このような機能を活用してみるのも良いかと思います。

2023/09/22
【Excel】実は便利な拡張子(xltx)とは

【Excel】実は便利な拡張子(xltx)とは

Excelファイルを保存するとき、通常であれば「xlsx」やマクロ有効ブックであれば「xlsm」の拡張子で保存することが多いかと思います。
よく使うテンプレートのファイルなどでも、これらの拡張子で保存しているケースをよく見ます。

実は、テンプレートファイル専用の拡張子がExcelには用意されています。
その拡張子が「xltx」になります。※マクロ有効ブックの場合は「xltm」になります。

この拡張子で保存したファイルは、以下のように若干アイコンが通常とは異なります。

この「xltx」というテンプレートの形式で保存したファイルは、ダブルクリックで開くと、「新しいファイル」として開かれます。
そのため、誤って「Ctrl+S」などで保存してしまっても、上書き保存されることがなく、新規で保存されます。

テンプレートファイルを修正したい場合は、右クリックメニューから[開く]を選択するか、Excelの[ファイル]タブの[開く]から選択することで、上書きすることができる状態で開くことができます。

この拡張子での保存方法は以下の通りです。

1.[ファイル]タブから[名前を付けて保存]を選択し、[参照]を選択する

2.次の画面で拡張子を「xltx」にして保存する

※保存場所に関して、自動で開かれる「Officeのカスタムテンプレート」内に保存すると、Excelを開いたときの画面の[新規]から、いつでもテンプレートファイルが開けるようになります。

2023/09/21
【Excel】完全にランダムでチーム分け

【Excel】完全にランダムでチーム分け

以下のような名前一覧表をもとに、AチームからDチームにランダムでチーム分けする方法について解説していきます。

まずは、AチームからDチームに名前一覧表からリンクしている状態で必要な人数を設定していきます。
リンクしている状態とは「=B3」などと名前一覧表を参照している状態を指しています。

簡単な方法として、名前一覧表の全体を選択し、コピーします。
その後、Aチームの先頭を選択し、右クリックメニューから[リンク貼り付け]を選択します。

そのようにすると、1人1人の名前が名前一覧表をリンクしている状態で貼り付けられます。


後は、リンクされた名前一覧をもとに、各チームに必要な人数単位で分けていきます。
ドラッグして移動させることで、リンクを維持することができます。

各チーム単位でリンクされた名前を分けることができましたら、次は、参照元の名前一覧表の隣に乱数を用意します。
乱数はRAND関数を用いて用意することができます。

=RAND()

乱数の用意ができましたら、名前一覧表にフィルターを設定し、乱数の項目を軸に、昇順もしくは降順にすることで、ランダムでチーム分けすることができます。
フィルターは、表の中を選択し、「Ctrl + Shift + L」で簡単に設定することができます。

2023/09/20
【Excel】グラフに表示させるデータを瞬時に追加

【Excel】グラフに表示させるデータを瞬時に追加

以下のような各支店ごとの各月の売り上げをまとめた表があります。
1月から3月まではグラフに表示されていますが、後から4月のデータを追加した場合、グラフにも追加する必要があります。

データを追加する方法として、グラフを選択した後に表示される範囲から枠をドラッグして参照範囲を拡張するという方法があります。


では、次の場合はどうでしょうか。
3月の後に、一度集計列を追加し、その後に4月と5月のデータを追加しています。

このようになると、単純にドラッグでは拡張することができません。

ただ毎回、グラフを選択し、[グラフのデザイン]タブより[データの選択]をから修正するのは大変かと思います。
※離れている範囲は「,(カンマ)」区切りで指定します。

実は、簡単に離れている範囲でもグラフに追加する方法があります。


それは、「Ctrl+C」でコピーして、「Ctrl+V」で貼り付けるという方法です。

まず、追加したい範囲を選択し、「Ctrl+C」でコピーします。

後は、グラフを選択して、「Ctrl+V」で貼り付けるだけで完了です。

このような方法も覚えておくと、役立つことがあるかと思います。

2023/09/19
【ExcelVBA】表の最終行の行番号を取得する方法

【ExcelVBA】表の最終行の行番号を取得する方法

VBAで何かを開発する際に、表の最終行の行番号を取得することが結構あります。
そんな最終行の取得方法には色んな方法があります。

こちらでは、4パターンをお伝えし、それぞれのメリット・デメリットをまとめています。

最終行の取得方法は、以下のlastRow1からlastRow4などの方法があります。
これ以外にも色んな方法がありますが、今回はこちらの4パターンについて触れていきます。
※こちらのファイルは配布しています。

では、順番にメリット・デメリットをまとめていきます。

ExcelVBAレベル確認

・lastRow1

lastRow1 = Cells(Rows.Count, "A").End(xlUp).Row

こちらは、A列のシートの最後のセル「A1048576※最後の行番号はバージョンによって異なる」から「Ctrl+↑(カーソルキー)」で移動した先のセルの行番号を取得するという方法です。
「Ctrl+↑」で、次に値の入力されているセルまで移動することができます。

メリットは、必ず値の入力されている項目がある場合は、その項目の列を基準に正しい最終行の行番号を取得することができます。
表の間に空白行があったとしても、最終行を取得することができます。

デメリットは、必ず値の入力されている項目がない場合は、正しい最終行の行番号を取得することができないことです。
例えば、以下の表の場合、A列を基準に最終行の行番号を取得すると「11」になってしまいます。


・lastRow2

lastRow2 = Range("A1").End(xlDown).Row

こちらは、A列の先頭「A1」から「Ctrl+↓(カーソルキー)」で移動した先のセルの行番号を取得するという方法です。

メリットは、単純にコードを短く書くことができます。

デメリットは、データが0件の場合や、空白行がある場合に正しい行番号を取得することができません。
0件の場合は、「Ctrl+↓」ですと、次に値が入力されているセルが見つからないため、シートの最後の行番号が取得されてしまいます。


・lastRow3

lastRow3 = Range("A1").SpecialCells(xlLastCell).Row

こちらは、シート上で使用されたことがあるセル範囲の最後の行番号を取得するという方法です。
使用されたことがあるセル範囲は、セルA1を基準に「Ctrl + Shift + End」で選択される範囲になります。

メリットは、必ず値の入力されている項目がなくても、最終行の行番号を取得することができます。

デメリットは、”使用されたことがあるセル範囲”のため、データが削除された場合に、正しい最終行の行番号を取得することができません。


・lastRow4

lastRow4 = Range("A1").CurrentRegion.Rows.Count

こちらは、現時点で使用されているセル範囲から行数を取得するという方法です。
表の中を選択し、「Ctrl+A」で選択される範囲の行数を取得しています。

メリットは、lastRow3と同様に、必ず値の入力されている項目がなくても、最終行の行番号を取得することができます。

デメリットは、空白行がある場合には、正しい最終行の行番号を取得することができません。


それぞれにメリットとデメリットがあるため、それらを理解した上で、活用するのが良いです。
個人的には、lastRow1とlastRow4の方法がオススメになります。

▼サンプルファイル▼

2023/09/17
【便利】シート名を数式で取得

【便利】シート名を数式で取得



YouTubeで開く

マクロなどを使わずに、既存の関数のみでシート名を取得する方法について解説しています。

テンプレートシートを毎月コピーして、運用している場合などで、シート名に「年月」、そのシート内のセルに「年月」と、同じ値を入力していることもあるかと思います。
そのような時に、今回の方法を用いることで、シート名のみの変更で運用することができるようになります。

00:00 挨拶
00:32 CELL関数とは
03:06 シート情報の取得
12:33 完成
13:15 補足
13:40 まとめ

2023/09/08
【Excel】関数の使い方をすぐに確認する

【Excel】関数の使い方をすぐに確認する

他の方や、過去の自分自身が作成したExcelファイルには、見たことのない関数や機能を思い出せない関数が見つかることがあるかと思います。
そのような時に、瞬時に関数の使い方を確認する方法があります。

例えば、以下のNETWORKDAYS.INTL関数の使い方を確認したい場合、対象の関数上にカーソルを移動すると、関数名の書かれた以下のようなリンクが表示されます。
こちらを選択することで、[ヘルプ]が表示され、対象の関数の使い方を瞬時に確認することができます。

関数によっては、解説動画や使用例などが用意されているものもあります。

2023/09/07
【便利】好みの画像の上にグラフを作成

【便利】好みの画像の上にグラフを作成



YouTubeで開く

好みの画像の上にグラフを作成する方法について解説しています。

Excelには予め色んな形式のグラフが用意されていますが、その中に理想の形式がないこともあるかと思います。
そんな時、自分自身に合ったグラフを作成する際に、こちらの内容が参考になるかと思います。

00:00 挨拶
00:37 完成イメージ
00:57 準備
01:19 作成(グラフ)
08:30 作成(ラベル)
12:25 完成
13:11 まとめ

▼準備ファイル▼

2023/09/07
【Excel】英単語のスペルチェック機能

【Excel】英単語のスペルチェック機能

近代では海外との仕事も増え、英語での文章のやり取りをする方も多くなっているかと思います。
そこで、英語を入力する際に、入力した単語のスペルが正しいのかどうか不安になることもあるかと思います。

実は、Excelには入力した単語のスペルを自動でチェックする機能があります。

それは、[校閲]タブ内にある[スペルチェック]という機能になります。

こちらの機能は、[F7]キーでも実行することができます。
※ノートパソコンなど、小さめのキーボードの場合は、[Fn]キーを同時に押す必要があるものもあります。

まずは、スペルを確認したいセル上で、[スペルチェック]を実行します。

スペルにミスがないと判断された場合は、以下のウィンドウが表示されます。
必要に応じて、「はい」を選択し、先頭から順番にチェックすることができます。

実行すると、以下のように誤っている単語がある場合に、修正候補が表示されます。
修正が不要な場合は[無視]を選択し、修正が必要な場合は、修正候補から単語を選択し、[修正]を選択することで置換されます。

名詞などで辞書に登録されていない単語に関して、今後、警告を表示したくない場合は、辞書に追加することができます。
以下の画面にて、[辞書に追加]を選択することで、辞書に追加され警告が表示されなくなります。

辞書に登録した内容を修正したい場合は、上記の画面の左下の[オプション]、もしくは、[ファイル]タブから[オプション]を選択するなどとして、[Excelのオプション]を開きます。
そして、Excelのオプション内の[文章校正]内の[ユーザー辞書]を選択します。

以下の[ユーザー辞書]のウィンドウにて、対象の辞書を選択し、[単語の一覧を編集]より追加や削除することができます。

2023/09/06
【Excel】セルに分数を入力する

【Excel】セルに分数を入力する

「セルに分数を入力したら、日付になってしまった」

このような経験をされたことがある方、意外と多いかと思います。

表示形式によっては、入力値から自動で形式を推測して変換されてしまいます。
そのため、分数を入力する場合は、予め表示形式を[分数]にする必要があります。
※表示形式を変更せずに分数を入力する方法についても紹介しております。

[ホーム]タブの中より、表示形式を[分数]にします。

表示形式を設定した後に分数を入力すると、見た目は分数で、数式バーには数値が表示されるようになります。

表示形式を変更するのが面倒に感じる場合は、分数を入力する前に、「0(半角スペース)」を入力してから分数を入力することで、表示形式が分数に切り替わり、分数を直接入力することができます。

分数ではなく、手順番号のような意味合いで入力したい時は、表示形式を[文字列]にします。

文字列に関しても、表示形式を変更するのが面倒に感じる場合は、先頭に「’」を入力してから分数を入力することで日付形式に変換されることなく文字列として入力できます。
※こちらの入力方法の場合は、表示形式は変わらずに保持されます。

2023/09/05
【Word】自動で現在の日付や時刻を更新する

【Word】自動で現在の日付や時刻を更新する

Wordで作られた印刷する用のテンプレートファイルなどで、印刷日(発行日)を毎回入力している場合、実は自動で「現在の日付」を表示する機能があります。

まずは、日付を入力したい位置にカーソルを移動させます。

次に、[挿入]タブ内の[日付と時刻]を選択します。

次の画面が表示されましたら、[言語の選択]・[カレンダーの種類]を選択し、好みの[表示形式]を選択します。
その後、[自動的に更新する]にチェックを入れて[OK]で確定します。

こちらの設定をすると、以下のように、現在の日付が自動で入力されます。

また、こちらの日付には特殊な設定がされており、Wordで立ち上げた時の現在の日付が自動で表示されます。
意図的に更新したい場合は、右クリックの中の[フィールド更新]を選択することで更新できます。

文章の一部に特殊な設定がされているかどうかは、カーソルを合わせると簡単に確認することができます。
通常の文章は、以下のように文字列内を選択することができますが、特殊な設定がされている場合は、その適用範囲の全体が選択されます。