IT予備
業務効率化のコツが無料で学べる！Excelなどの解説&配布サイト
連番を入力するときにオートフィルを使って入力している方、多いと思います。
実は、このように連番になる値は自由に設定することができます。
その設定は「ユーザー設定リスト」というもので、設定すると以下のようなことが実現可能になります。
設定方法を解説していきます。
まず、[ファイル]タブを選択し、[オプション]を選択します。
Excelのオプションが表示されましたら、[詳細設定]を下の方を確認すると、「ユーザー設定リストの編集」というボタンが見つかります。
こちらにて好みのリストを追加することができます。
項目を直接入力して登録することもできますが、リストの内容を予めシート上に用意し、その範囲を選択してからオプションを開くと[インポート]で簡単に登録できます。
登録が完了すれば、設定したパソコンでExcelを使う場合は、自由に設定した内容を活用できます。
以下の表をもとに、スピンボタンを使って３行目に１行ずつ抽出する方法について解説していきます。
まずは、セルA3に番号を入力し、その番号に対応した行数のデータを抽出する仕組みを作っていきます。
セルA3が０の時は６行目のデータを抽出、１の時は７行目、２の時は８行目という感じに作成します。
そのような仕組みはOFFSET関数を使うと簡単に実現できます。
=OFFSET(参照, 行数, 列数, [高さ], [幅])
// [参照]の範囲を基準に行方向へ[行数]分移動し、列方向へ[列数]分移動したところの指定した[高さ]と[幅]のデータを抽出します。まずは、セルB3に入力する式を作成します。
=OFFSET(B6,$A$3,0)
// 数式をコピーして横に反映させたいので、セルA3に関しては絶対参照にしています。
// 引数の[高さ]と[幅]は今回は省略しています。
// セルB6を基準に、指定した行数（A3）移動したセルの情報を取得します。
後は横へコピーするだけで数式に関しては完成です。
続いて、[開発]タブの中の[挿入]よりスピンボタンを作成します。
作成後は右クリックで[コントロールの書式設定]を開き、以下のような設定をします。
この設定をすることで、スピンボタンによりセルA3の値を変化させることができるので、抽出する行を簡単に変更することができます。
※スピンボタンを移動させたい場合は、Ctrlキーを押しながら選択します。
[補足] スピンボタンは▲を押すと数値が加算され、▼を押すと数値が減算されます。
▼を押したときに加算したい場合は、まずスピンボタンの設定を以下のように修正します。
このように設定できましたら、セルA3に以下の数式を設定します。（スピンボタンを移動させています）
=30000-A2
// セルA2を加算するとセルA3の値が減算され、セルA2を減算すると加算されます。
スピンボタンの位置を調整すれば完成です。
※数式を先に設定してしまいますと、スピンボタンの設定時に上書きされてしまうので注意する必要があります。
▼サンプルファイル▼
今回は、特定の項目の値を一時的に”*****”などの好みの文字に置き換えて表示させる方法について解説しています。
例えば、以下のようなシートのパスワードの表示非表示を切り替えるチェックボックスを作成していきます。
まずは、チェックボックスの作成です。
[開発]タブの中の[挿入]からチェックボックスを選択し、好みの位置に作成します。
表示名などは好みの名前を設定します。
※作成後にクリックするとチェックボックスが動作します。編集したい場合は、Ctrlキーを押しながらクリックします。
作成後、チェックボックス上で右クリックし、[コントロールの書式設定]を開き、以下のように設定します。
これで、チェックボックスのチェックの有無に合わせて、セルA2にTRUEもしくはFALSEが表示されるようになります。
後は、セルA2の値がFALSEの時に、パスワードの表示を切り替えます。
この設定は条件付き書式で行います。
切替対象の範囲を左上（こちらでは上）を基準に選択します。
選択後、[ホーム]タブの中から、[条件付き書式]の[新しいルール]を選択します。
[書式ルールの編集]が表示されましたら、[数式を使用して、…]を選択し、以下のような数式を入力します。
=$A$2=FALSE
// "$A$2"は選択した基準のセルになります。今回の場合はセルA2なので絶対参照で"$A$2"と指定しています。
入力できましたら、次に書式を設定します。
[書式]を選択し、[表示形式]を以下のように設定します。
;;;"*******"
// 「;;;」で非表示にし、表示させたい文字を「"（ダブルクォーテーション）」で囲みます。
これで、条件付き書式を登録して完成です。
セルA2のTRUE・FALSEに関して非表示にしたい場合は、表示形式にて「;;;」と設定することで非表示にできます。
※値は実際に置き換わっているのではなく、表示上の設定になります。
そのため、該当するセルを選択すると、数式バーには中身の値が表示されてしまうので注意する必要があります。
▼サンプルファイル▼
ピボットテーブルで詳細データを瞬時に確認する方法について解説していきます。
まず初めに、以下のようにピボットテーブルを作成します。
（作成したファイルは配布しているので、動作確認の際にぜひご活用ください）
このように、各月の商品ごとの売り上げをまとめて表示させたとします。
この表の中に、日にち単位のデータは表示されていませんが、詳細が気になることがあるかと思います。
そんな時、気になる項目の[値]をダブルクリックします。
それだけで、新しいシートに、その項目のみの詳細を表示させたテーブルが作成されます。
※作成されたシートが不要になったら、削除して問題ございません。
[補足] ちなみに、月単位でシートを分割したい場合などは、ピボットテーブルを活用することで瞬時に作成できます。
分割したい項目をフィルターに追加します。
（こちらでは”月”を移動させています）
追加できましたら、ピボットテーブルを選択し、[ピボットテーブル分析]タブの中の[オプション]から[レポートフィルター ページの表示]を選択します。
フィルターに追加した「分割したい項目」を選択して[OK]を選択することで、瞬時に分割したシートが作成されます。
▼サンプルファイル▼
以下のようなシートを用意し、B列に”〇”が入力されている行を削除するマクロの作成方法について解説していきます。
まず初めに、B列に”〇”が簡単に入力できるようにドロップダウンリストを作成します。
対象の範囲を選択して、[データ]タブ内の[データの入力規則]にて下記のように設定します。
※入力値の種類を[リスト]にして、元の値に”〇”を入力しています。
これだけでドロップダウンリストの完成です。
では、本題のマクロを開発します。
[開発]タブの中の[マクロ]を選択し、”DeleteRow”という名前で作成します。
※名前は仮です。
プログラムを記述する画面が表示されましたら、以下の内容を記述します。
Sub DeleteRow()
Dim lastRow As Long
lastRow = Cells(Rows.Count, "B").End(xlUp).Row 'B列の最終行番号を取得
Dim i As Long
For i = lastRow To 3 Step -1 '最終行から3行目まで順番に繰り返す
If Cells(i, "B").Value <> "" Then 'i行目のB列の値が空白でない場合
Rows(i).Delete 'i行目の削除
End If
Next i
End Sub行を削除すると、行数がずれてしまうため、その対策として最終行から上に向かってB列が空白でないセルを探しています。
最後にボタンを作成します。
[開発]タブの中の[挿入]よりボタンを作成します。（フォームコントロール内のボタン）
作成すると[マクロの登録]が表示されるので、先ほど作成した”DeleteRow”を登録します。
※ボタンの作成後は、クリックすると実行されてしまいます。
Ctrlキーを押しながらクリックするとボタンを編集できます。
これで完成です。
▼サンプルファイル▼
セル内の改行は数式にも使えます。
セル内で文字を改行する場合は、改行したいタイミングで「Alt+Enter」を押すことで行えます。
※「Enter」だけですと、入力値の確定になってしまいます。
次の数式を確認します。
※細かな解説は割愛します。
=FILTER(B3:E12,(D3:D12="男")*(E3:E12>=DATEVALUE("1985/1/1")),"")
こちらの例では、かなり長いというわけではありませんが、「どこからどこまでがFILTER関数の第２引数なのか？」などが分かりにくいかと思います。
今回の例ではありませんが、IF関数の組み合わせなどでは特に分かりにくくなるかと思います。
そんな時は、次のように引数単位で改行すると直感的に分かりやすくなります。
※同じ引数でも、式が長い場合は分割すると分かりやすくなります。
改行しても自動で改行が削除されてしまうことがあります。ただ、それほど見やすさは変わらないかと思います。
（例１）
（例２）
[補足] 数式バーの高さは調整することができるので、数式バーで確認する際は高さを広げてから確認すると分かりやすいです。
以下の表に対して、商品ごとの売上の合計をスピルを使って求めていきます。
まずは、C列から重複を除いた商品一覧を取得します。
重複を除いた値を取得するにはUNIQUE関数を使えます。
=UNIQUE(C3:C10)※UNIQUE関数の他の引数についての解説は割愛します。
ただ、この式ですと、セルC3からセルC10までしか抽出することができません。
そのため大きめに範囲を設定する必要があります。
しかし、次のように大きめに設定すると、何も入力がされていないセルが「0」となり、その値まで表示されてしまいます。
また、設定したサイズよりも大きくなってしまった場合は、再度更新する必要があります。
そこで、範囲を自動で変更させる方法があります。
それはOFFSET関数とCOUNTA関数の組み合わせです。
OFFSET関数とCOUNTA関数の使い方は以下になります。
=OFFSET(参照, 行数, 列数, [高さ], [幅])
// 指定した範囲（参照）から、指定した行数・列数を移動したセルの指定した大きさ（[高さ], [幅]）の範囲を返す
=COUNTA(値1, [値2], [値3], …)
// 指定した範囲（値1~）の空白でないセルの数を返すこの２つを組み合わせて次のような式を作ります。
=OFFSET(C3,0,0,COUNTA(C:C)-1)
このようにして、データが存在する範囲のみを取得することができます。
OFFSET関数の第１引数では、「C3」を選択し、「C3」を基準に抽出しています。
第２引数と第３引数に関しては、指定した行数・列数の移動になりますが、今回は移動する必要がないので、０を設定しています。
第４引数の高さに関しては、存在するデータの数を設定したいので、COUNTA関数でC列全体の空白でないセルの数を取得し、項目名のセルの数が不要になるため、「-1」を行っています。
この数式をUNIQUE関数の引数にすることで可変のサイズで取得することができます。
続いては売上の合計を求めていきます。
条件に一致する合計を求めるにはSUMIF関数を使います。
SUMIF関数の使い方は以下になります。
=SUMIF(範囲, 検索条件, [合計範囲])
// 指定した条件に一致するセルの合計を返すこの検索条件を複数指定することでスピルを活用して一括で表示することができます。
また、スピルにより拡張された範囲は数式のセルに「#」を加えることで選択できます。
よって、次のように求めることができます。
=SUMIF(C:C,F3#,D:D)
範囲は列全体を指定しています。
セルG3に関しては、「C列の中でセルF3と一致する行のD列の合計」を表示しています。
セルG4に関しては、「C列の中でセルF4と一致する行のD列の合計」を表示しています。
他に関しても同様です。
これで、新しいデータが追加された場合に関しても自動で反映することができます。
フリガナを漢字の上に表示させたり、PHONETIC関数で別のセルに表示させたりする際に、フリガナが表示されないことがあります。
このフリガナはExcelに文字を入力するときに、漢字の変換前の値を記憶した内容になります。
そのため、外部からコピーしてきた値などにはフリガナが設定されません。
フリガナを設定するには、[ホーム]のフォント内にある[ふりがなの編集]から行う必要があります。
こちらを選択すると、MS-IMEが管理している標準的な日本語の読みが自動で設定されます。
※すでにフリガナが設定されている場合は、そのフリガナが入力されている状態で選択されます。
ただ、毎回「ふりがなの編集」を選択するのは大変かと思います。
そんな時に、次のショートカットを覚えておくと便利です。
Shift + Alt + ↑
このショートカットが「ふりがなの編集」と同じになります。
標準的な読みで良い場合は、『「Shift + Alt + ↑」を押して、「Enter」を押す』という流れで簡単に設定することができます。
[補足] 「ふりがなの設定」からフリガナの文字のフォントや種類を変更できます。
「グラフを作成したけど、項目の順番が逆になってしまう…」
このようなことが横棒グラフでは起こるかと思います。
この理由は、表のデータは上から下へ読み込まれますが、グラフのデータは原点から読み込まれていきます。
そのため、縦軸に関しては、下から上へという順番になってしまいます。
この向きを逆にする方法は次のようになります。
①逆にしたい軸をダブルクリック、もしくは、右クリックから書式設定を開きます。
②書式設定の中の、「最大項目」を選択し、「軸を反転する」にチェックを入れます。
i.「最大項目」で横軸の値の位置を一番上に移動させます。
ii.「軸を反転する」で縦軸全体を反転させます。
これだけで、軸の順番を逆にすることができます。
先月のシートと今月のシートの更新箇所の確認など、シート単位で比較する方法について解説していきます。
まずは、比較する際の数式について確認します。
以下のようなシート「A」と「B」の全ての内容を比較する場合、2つのシートとは別に比較結果を表示する用の「差分」というシートを用意します。
シート「A」とシート「B」のセルA1が一致しているか確認するには、差分シートに以下のような式を入力します。
=AND(A!A1=B!A1,EXACT(A!A1,B!A1))
各シートの参照（A!やB!など）に関しては、それぞれのシートのセルを選択することで自動で入力されます。
後はこの式を比較したいセルの範囲までコピーすると、一致していないセルがFALSEと表示されるようになります。
※自動色付けの解説は式の解説をした後に行います。
式の解説をします。
=AND(A!A1=B!A1,EXACT(A!A1,B!A1))①AND関数：引数の全てがTRUEの場合にTRUEを返す、それ以外はFALSEを返す
②〇=〇：2つの値が等しい場合にTRUEを返す、等しくない場合はFALSEを返す
③EXACT(〇,〇)：2つの文字列が等しい場合にTRUEを返す、等しくない場合はFALSEを返す
この内容を見ると、②もしくは③のみで問題がないように感じますが、②と③には以下のような違いがあります。
※「数値と文字列の比較」や「アルファベットの大文字と小文字の比較」に関して、一致していなくても片方の比較方法だとTRUEになってしまいます。
上記のようになるため、両方の比較を使い、両方が満たされた場合に関してのみTRUEとなるように以下のような式になりました。
=AND(A!A1=B!A1,EXACT(A!A1,B!A1))
ただ、TRUEもしくはFALSEの表現だけでは、確認が大変かと思います。
その際に、条件付き書式を使うと便利です。
色付けしたいシートに対し、色付けしたい範囲を選択します。
条件付き書式の設定は、相対参照を意識する必要があります。
そのため、選択したい範囲内の左上のセルを基準に選択すると設定が分かりやすいかと思います。
※こちらではセルA1を基準に選択しています。
選択できたら、[ホーム]→[条件付き書式]→[新しいルール]を開きます。
この中の「数式を使用して…」というところで先ほどの式を入力します。
※設定するセルは選択した基準になります。こちらの場合はセルA1を基準に選択しているため、セルA1を基準にした式を入力しています。
また、条件付き書式の「数式を使用して…」で設定する式は、この式の結果がTRUEの場合に関して書式を設定することになります。
そのため、以下の式のままですと、一致がTRUEになってしまうので、計算結果がFALSEの場合という式を追加します。
=AND(A!A1=B!A1,EXACT(A!A1,B!A1))
↓
=AND(A!A1=B!A1,EXACT(A!A1,B!A1))=FALSE
書式に関しては、好みの背景色を設定し「OK」で保存すると、不一致を色付けすることができます。
①DATEDIF関数
開始日と終了日の日数や月数、年数を計算する関数です。
=DATEDIF(開始日, 終了日, 単位)引数[単位]には以下の内容を設定します。
②DATESTRING関数
日付を和暦の文字列として返す関数です。
=DATESTRING(日付)文字列で返すため、計算には使えなくなってしまいます。
そのため、もし和暦表記にしたい場合は、表示形式の設定を推奨します。
③NUMBERSTRING関数
指定した数値を指定した漢数字に変換する関数です。
=NUMBERSTRING(数値, 書式番号)引数[書式番号]には１～３の数値を設定します。
１～３の違いは以下の通りです。
対象月（年月）の営業開始日と営業終了日を求める方法について解説していきます。
今回は指定日後の営業日を求めるWORKDAY.INTL関数を使います。
=WORKDAY.INTL(開始日, 日数, [週末], [祭日])WORKDAY.INTL関数は定休日や他の休日も設定することができます。
定休日は引数[週末]で設定できます。
引数[週末]は以下の番号を設定します。
※設定した曜日が定休日になります。
上記の表の中に、理想の定休日がない場合は、０と１でも表現することができます。
他の休日（不定期）は引数[祭日]に対象の日付の範囲を設定します。
この関数は、引数[開始日]から引数[日数]日後の営業を求める関数になります。
この関数と、次に必要な関数がDATE関数になります。
=DATE(年, 月, 日)DATE関数は指定した年月日から日付形式（シリアル値）に変換する関数です。
「=DATE(2023,1,1)」であれば、2023/1/1（シリアル値）になります。
また、月や日が存在しない場合は、自動で桁上がりや桁下がりします。
「=DATE(2023,2,0)」であれば、2023/1/31（シリアル値）になります。
「=DATE(2023,13,1)」であれば、2024/1/1（シリアル値）になります。
この関数を使い、営業開始日の場合は、前月末から1日後の営業日というように求めることができます。
営業終了日の場合は、翌月の1日から-1日後の営業日というように求めることができます。
※WORKDAY.INTL関数で求めた値は表示形式が自動で設定されないので、シリアル値が返されます。
必要に応じて表示形式を日付形式に変更します。
