作者別: it-yobi

Aならば9:00-14:00、Bならば14:00-20:00、Cならば9:00-20:00などとパターンが決まっている場合で、A~Cなどの文字を指定するだけで対象時間を色付けする仕組みの作成方法について解説していきます。

※こちらで使用したファイルは記事の最後にて配布しています。

条件付き書式の活用

では、以下の表を用いて解説していきます。

こちらの表は、セルB3~H3にA~Cの文字を指定することで、各列の対象時間を色付けする想定で用意されたものです。
A~Cに割り当てられた時間は以下の通りです。

A：9:00-14:00
B：14:00-20:00
C：9:00-20:00

まずは、「A」という文字が入力された場合に、「A」の対象時間（9:00-14:00）を色付けする設定から行います。
このような設定は「条件付き書式」を活用します。

初めに、表のAの対象時間を以下のように選択します。

次に、[ホーム]タブの中の[条件付き書式]より[新しいルール]を選択します。

条件付き書式の設定画面が表示されましたら、[ルールの種類]を[数式を使用して、書式設定するセルを決定]にし、[ルールの内容]には選択基準のセル（こちらの場合はセルB5）に対しての条件式を入力します。

基準のセルを色付けする条件は以下になります。

・基準の列の３行目のセルに「A」と入力されている場合

この内容を条件式で表現すると以下のような式になります。

=B3="A"
// 選択基準のセルがB5の場合

この式に関しては、他の選択しているセルにも相対参照で反映されます。
つまり、セルC5を色付けする条件式は「=C3=”A”」、セルC6を色付けする条件式は「=C4=”A”」になります。

ただ、「=C4=”A”」というように他の行を参照している条件式は正しくありません。
そのため、参照する行番号に関しては固定する必要があるので、以下のように行番号の隣に「$」を加えた条件式を入力します。

=B$3="A"

条件式の入力ができましたら、[書式]を選択し好みの書式を設定することで、「A」が入力されている列に対して自動で色付けすることができます。

「A」を入力すると以下のように反映されます。

「B」と「C」に関しても同様に設定します。

・B：セルA15~H26を選択 → 条件付き書式にて条件式「=B$3=”B”」を設定
・C：セルA5~H26を選択 → 条件付き書式にて条件式「=B$3=”C”」を設定

A~Cなどとパターンが予め決まっている場合は、今回の方法を活用すると便利です。

設定した条件の編集や削除は、[条件付き書式]の[ルールの管理]にて行えます。

▼サンプルファイル▼

何かしらのシステムから出力したファイルなどで、以下の表のように同じ属性の値が空白になっていることがあります。

この表の状態ですと、見やすいですがフィルター機能による操作が上手くできなかったり集計が大変だったりします。
そのため、こちらでは以下のように、空白を上のセルの値で埋める方法について解説していきます。

ジャンプ機能の活用

まずは、表の中の空白を含む対象の範囲を全選択し、[ホーム]タブの中の[検索と選択]から[条件を選択してジャンプ]を選択します。

選択すると表示される設定画面にて、[空白セル]を選択し[OK]を選択して確定します。

これだけで、選択した範囲内の空白セルのみを選択することができます。

選択している状態で、「=」と入力し、数式が入力されたセルの１つ上のセルを選択します。

数式の入力ができましたら、Ctrlキーを押しながらEnterキーを押して確定することで、空白のセルを埋めることができます。

数式を値に変換

以上の作業で空白を埋めることができましたが、このままでは一部のセルが数式のままです。
数式のままですと、並べ替え時に値が正しく表示されなくなってしまいます。
以下は、Noの項目を基準に降順にした場合です。

そのため、別の行のセルを参照する数式のセルに関しては値のみに変換する必要があります。
変換する方法は簡単です。
数式のセルを含む対象の範囲を選択し、Ctrlキーを押しながらCを押してコピーして、CtrlキーとShiftキーを押しながらVを押して値のみを貼り付けるだけです。

※「Ctrl + Shift + V」の貼り付けが出来ない場合は、右クリックのメニューから値のみを貼り付けることができます。

他にも、「Ctrl + Alt + V」を押して[形式を選択して貼り付け]の画面を表示し、Vを押して[値]を選択、Enterキーで確定することでも、値のみを貼り付けることができます。

以下の日付と曜日が表示されているセルに関して、実は日付のみしか入力されていません。
曜日は表示形式を活用して表示しています。

このように、表示形式を活用して日付と曜日を改行して表示する方法について解説していきます。

曜日を表示する

まずは日付が入力されているセルを用意し、表示形式の設定を表示します。
表示形式の設定画面は、[ホーム]タブの中の表示形式のリストより[その他の表示形式]を選択することで表示できます。
※以下では単体のセルを選択していますが、複数のセルに適用したい場合は複数のセルを選択します。

次の設定画面で[ユーザー定義]を選択し、種類と書かれた場所の下のテキストボックス内に書式記号を入力していきます。

まずは、日付形式の先頭に曜日を加えた書式記号を入力します。
書式記号は「aaayyyy/m/d」になります。
※「aaa」が曜日を表しています。

書式記号については以下の記事で解説しています。

＞表示形式の設定方法

曜日と日付を改行して表示する

次に、曜日を表す「aaa」と日付の年を表す「yyyy」の間にカーソルを移動し、Ctrlキーを押しながらJを押します。
「Ctrl + J」を行うことで、分かりづらいですが改行されます。

「a」の下あたりを確認すると、カーソルが点滅しているのが見えます。

この設定内容で確定することで、改行の設定を行うことができます。
確定後に関しても改行されない場合は、[ホーム]タブの中の[折り返して全体を表示する]を選択することで改行することができます。

以下のような分単位での表記を「h:mm」という時刻形式の表記に変換する方法について解説していきます。

分表記を時刻表記に変換

分表記を時刻表記に変換する場合は、TIME関数を活用することで、計算なしで簡単に変換することができます。
TIME関数の使い方は以下になります。

=TIME(時, 分, 秒)
// 指定した「時、分、秒」から時間を表すシリアル値を返す

シリアル値について不明な場合は、以下の記事が参考になります。
※シリアル値について理解していない状態でも読み進めることができます。

＞シリアル値とは

今回の場合は、分表記を時刻表記に変換するため、TIME関数の「分」の引数のみを以下のように指定します。

=TIME(0,B3,0)

上記の数式を入力することで、以下のように時刻形式で時間を取得することができます。

しかし、表示形式がAM/PM表記で勝手に設定されることがあります。
このような場合は、表示形式を「h:mm」形式に変更する必要があります。

表示形式を変更する場合は、対象のセルを選択している状態で、表示形式の設定の[その他の表示形式]を選択します。

次に、表示された設定画面にて[時刻]を選択し、「h:mm」形式の表記を選択します。
※「13:30」と書かれているものが「h:mm」の表記になります

対象の表記を選択して確定することで、表示形式を変更することができます。

残りのセルに関しては、コピーするだけで時刻表記に変換することができます。

注意点

TIME関数を活用することで「時、分、秒」からシリアル値に変換することができますが、TIME関数では２４時間を超える変換ができません。

例えば、「24:10」を分単位にした「1450」という数値を、TIME関数を用いて時刻形式に変換してみます。
そのようにすると、以下のように「24:10」ではなく「0:10」と表示されてしまいます。

※セルC3の表示形式は「[h]:mm」になっています。

＞[h]の表記について

このように、TIME関数では２４時間を超える場合に２４時間以上の時間が引かれた時間になってしまいます。
そのため、２４時間を超える可能性がある場合は、数式を用いて、以下のように計算する必要があります。

=B3/24/60

この計算についての詳しい解説は、以下の記事（動画）にて解説しています。

＞【ex39】複雑な計算なしで形式変換「○分⇔○時間○分」

以下の表を元に、年代および種別ごとの集計を行う方法について解説していきます。
また今回紹介する方法では、数式による計算が不要になります。

ピボットテーブルの作成

数式による計算なしで表を集計する場合は、「ピボットテーブル」が便利です。
まずは、対象の表からピボットテーブルを作成します。

対象の表の中を選択して、[挿入]タブから[ピボットテーブル]を選択し、以下の画面が表示されることを確認します。

表示された画面にて、表の範囲が正しいことを確認し、問題がなければ[OK]を選択することで、新しいシートにピボットテーブルが作成されます。

ピボットテーブルの設定

次に作成されたピボットテーブルの設定を行います。

こちらでは、年代および種別ごとの集計を行うため、[ピボットテーブルのフィールド]と書かれた設定画面にて、行のボックスに[年齢][種別]の順番で配置、値のボックスに[氏名]を配置します。
値に関しては、人数が求められればよいので、他の項目の個数での集計することができます。

以上の設定を行うことで、以下のような表が作成されます。

ただ、年齢および種別ごとの集計にはなっていますが、年代別にはなっていません。
そのため、年齢の項目を10歳間隔でグループ化する必要があります。

年齢の値を元にグループ化する場合は、年齢のいずれかの数値の上で右クリックし、[グループ化]を選択します。

選択後、表示されたグループ化の設定画面にて、グループ化する間隔を指定します。
こちらでは、[先頭の値]を「20」、[末尾の値]を「59」、[単位]を「10」にしています。

以上の手順で、以下のように年齢および種別ごとの集計表を作成することができます。

補足

必要に応じて表の見出しを変更すると、より見やすくなります。
[行ラベル]と書かれているセルに関しては直接変更することができ、[個数 / 氏名]と書かれている集計の見出しのセルに関しては、ダブルクリックした時に表示される[値フィールドの設定]画面の[名前の指定]にて変更することができます。

「セルを編集しようとクリックしたところ、選択がどこかに飛んで行ってしまった」
このような経験をされたことがある方は少なくないと思います。

この現象は不具合ではなく、Excelの機能になります。
この機能について解説していきます。

クリックで移動する機能

セルを選択すると緑色の枠でセルが囲まれます。
この枠線上にカーソルを移動すると、カーソルが十字矢印になります。

この十字矢印になる位置でダブルクリックすると、そのセルを基準に隣接するセルで値が入力されている端のセルまで選択が移動します。

緑色の枠線の上を選択すると上方向、下を選択すると下方向、左を選択すると左方向、右を選択すると右方向へ移動します。

選択されていないセルの枠の上の場合はカーソルが十字矢印になりませんが、そのような場合でも、セルの枠をダブルクリックすると同時に端のセルまで移動します。
その場合、セルの枠の少し下をダブルクリックすると上方向、少し上をダブルクリックすると下方向となります。

上記の画像のように、どこのセルを基準にダブルクリックされたかによって、その後の移動先が異なります。
左右の枠をダブルクリックした時に関しても同様です。

以上の理由で、セルの選択時に選択が移動することが確認できたかと思います。
そのため、特にサイズの小さいセルを編集する際は、いきなりセルをダブルクリックせずに、「セルをクリックし選択してからF2キーで編集する」、もしくは、「セルをクリックし選択してからカーソルが十字矢印でない位置でダブルクリックし編集する」というようにすると、意図せずに選択が端まで飛んで行ってしまうことがなくなります。