メンバー

基本、スピンボタンは数値を入力する用のフォームになります。
ただ、使い方によっては文字をスピンボタンにより変化させることができます。

例えば、スピンボタンを使ってA～Eのアルファベットを切り替えたいと思います。
その場合は、まず、数値に対してA～Eのアルファベットを表示させる数式を作成します。

そのような数式を作成する場合はCHOOSE関数が使えます。

=CHOOSE(インデックス, 値1, [値2], [値3], [値4], …)
// インデックスに対応した値を返す

CHOOSE関数を使用して1～5の数値とA～Eの文字列を割り当てた例は以下になります。

=CHOOSE(A3,"A","B","C","D","E")
// セルA3が1の場合は"A"、2の場合は"B"、…、5の場合は"E"を返す

このようにできましたら、スピンボタンを作成していきます。
スピンボタンは[開発]タブの中の[挿入]、[スピンボタン]を選択することで作成できます。

好みの位置に作成できましたら、作成したスピンボタンを右クリックして[コントロールの書式設定]を開きます。

書式設定にて以下のように設定します。
今回の場合は1～5にアルファベットを割り当てているため、[最小値]と[最大値]を1と5にし、[変化の増分]を1にして数値を1刻みにします。
[リンクするセル]に関してはCHOOSE関数で参照している数値が入力されているセルを指定します。

以上で設定が完了です。
後はスピンボタンを押すだけで、セルA3の数値が変化し、セルB3の文字列が変化します。

数値の入力されたセルが邪魔な場合は、スピンボタンで隠すのも良いかもです。
作成したスピンボタンを選択する場合は、[Ctrl]キーを押しながらクリックします。
また、スピンボタンのサイズをセルの枠に合わせる場合は、[Alt]キーを押しながらサイズ調整すると、簡単にセルの枠に合わせて調整されます。

逆算して値を求める機能に「ゴールシーク」という機能があります。
それを更に進化させた機能が「ソルバー」になります。

まずは、ゴールシークの機能から簡単に確認します。

以下のような数式と目標人数が入力された表があります。
例えば、この表を用いて「合計金額を”100,000”にするには、Bプランの目標人数を何人にする必要があるのか？」
それを求めていきます。

合計金額を求める数式の入力されたセル「E6」を選択し、[データ]タブから[What-IF 分析]、[ゴールシーク]を選択します。

選択すると以下のような画面が表示されます。
この[数式入力セル]に選択したセルのアドレスが自動入力されるため、残りの[目標値]と[変化させるセル]を入力します。

入力後、[OK]で確定すると以下のようにBプランの人数を自動で調整してくれます。

※値を変化させながら収束させていく仕組みのため、場合によっては収束できないことがあります。

では次にソルバーです。
ゴールシークでは特定の１つのセルを変化させることしかできなかったのですが、ソルバーでは複数のセルを変化させることができます。

例えば、以下の表を元に、合計金額が”100,000”になるようなA～Cプランの人数を求めていきます。
また、人数を求める際に、「Cプランは10人まで」などの制約を設けることもできます。

まずは[開発]タブの中の[Excelアドイン]から[ソルバー アドイン]を追加します。

[ソルバー アドイン]を追加すると、[データ]タブの中に[ソルバー]が追加されます。
こちらを選択します。

選択後、必要な値を入力し[解決]で実行します。

【設定内容】
目標セル：$E$6 // 目標値の数式のセル
指定値：100000 // 10万円の利益を目標
変数セル：$D$3:$D$5 // A～Cプランの人数
制約：$D$5 <= 10 // Cプランは10人まで

実行すると以下のように求めることができます。
※ソルバーに関しても上手く求められないことがあります。

Aプランの場合は1,000円、Bプランの場合は1,500円、Cプランの場合は2,000円などとプランに対応した金額を表示させる際にSWITCH関数を覚えると便利です。
プランごとの金額表を作成し、VLOOKUP関数などで抽出するということも可能ですが、金額表を作成するまでもない場合に関しては、こちらの方法が便利になります。

SWITCH関数の使い方は以下になります。

=SWITCH(式, 値1, 結果1, [既定または値2, 結果2], [既定または値3, 結果3], …)
// 式の値が一致する結果を返す

実際にSWITCH関数を使用した例が以下になります。

=SWITCH(B3,"A",1000,"B",1500,"C",2000)

また、SWITCH関数には既定値を設定することができます。
既定値を設定する場合は、式の最後の引数に規定値を加えます。
規定値を0とした場合の式は以下になります。

=SWITCH(B3,"A",1000,"B",1500,"C",2000,0)

[補足] IF関数やIFS関数で実現する場合は以下のようになります。

=IF(B3="A",1000,IF(B3="B",1500,IF(B3="C",2000,0)))

=IFS(B6="A",1000,B6="B",1500,B6="C",2000,TRUE,0)
// IFS関数で既定値を設定する場合は、最後の条件式をTRUEにする

「複数行・複数列の表を１列の表にする方法」と、「１列の表を複数行・複数列の表にする方法」について解説しています。
※この記事の内容は、現時点(投稿日時点)では３６５でないと対応しておりません。

・複数行・複数列の表を１列の表にする方法

複数行・複数列の表を１列の表にするにはTOCOL関数を使います。

=TOCOL(配列)
// 配列を１列にして返す

以下の数式を入力するだけで、簡単に１列にすることができます。

=TOCOL(A3:C9)

この場合、列方向を優先した順番で抽出されています。
これを行方向を優先した順番にしたい場合は、TRANSPOSE関数と組み合わせると実現できます。

=TRANSPOSE(配列)
// 配列の行と列を交換した配列を返す

実際に以下のように組み合わせ活用できます。

=TOCOL(TRANSPOSE(A3:C9))

他に、１列ではなく１行にしたい場合は、TOROW関数が使えます。

=TOROW(配列)
// 配列を１行にして返す

以下のように活用できます。

=TOROW(A3:C9)

こちらもセットで覚えておくと良いです。

・１列の表を複数行・複数列の表にする方法

１列の表を複数行・複数列の表にするにはWRAPROWS関数を使います。

=WRAPROWS(1列もしくは1行の範囲, 列数, [端数に表示する文字列])
// 指定した範囲を指定した列数の配列で返す
// 指定した列数に満たさない行に関しては、[端数に表示する文字列]を表示する

使用した例が以下になります。

=WRAPROWS(B3:B22,3,"")

表示された範囲の右下の１マスに関して空白が表示されています。
この空白は引数[端数に表示する文字列]に設定した文字列が表示されます。
引数[端数に表示する文字列]を省略した場合はエラーが表示されます。

WRAPROWS関数は列数を指定して改行する関数ですが、逆に行数を指定するWRAPCOLS関数もあります。

=WRAPCOLS(1列もしくは1行の範囲, 列数, [端数に表示する文字列])
// 指定した範囲を指定した行数の配列で返す
// 指定した列数に満たさない行に関しては、[端数に表示する文字列]を表示する

以下のように活用できます。

=WRAPCOLS(B3:B22,3,"")

こちらもセットで覚えておくと良いです。

指定日以降でないと入力できない設定方法について、営業日を考慮しない方法と考慮する方法の２パターンを解説しています。

１.以下の表の対応予定日に関して、申込日から３日後以降でないと入力ができないような設定を行います。

入力制限する対象のセルを選択し、[データ]タブの中の[データの入力規則]を選択します。

[データの入力規則]の画面にて、[入力値の種類]を「ユーザー設定」にし、以下の数式を入力します。

C3>=B3+3

初めの「=」は「今から数式が始まります」というような意味になります。その後に、入力を許可する条件式を設定します。
こちらでは、「セルC3は、セルB3の３日後以降である」という条件式になります。
日付はシリアル値で管理されているため、このように「+」で計算することができます。

＞シリアル値とは

次に、[エラーメッセージ]に関しても以下のように設定します。
[スタイル]を「停止」にすることで、対象外の日付を入力できなくなります。
※デフォルト値は「停止」です。

上記の設定で確定することによって、申込日から３日後以降でないと入力ができなくなりました。

２.以下の表の対応予定日に関して、申込日から営業日を考慮した３日後以降でないと入力ができないような設定を行います。

同じように[データの入力規則]を開きます。
ここで設定する数式は先ほどとは異なり以下のようになります。

=C3>=WORKDAY.INTL(B3,3,1,E3:E100)

WORKDAY.INTL関数を用いて定休日(土日)と休日を除いた３日後の日付を求めています。
休日一覧表に関しては、データが追加されても反映されるように、「E3:E100」と１００行目まで考慮しています。
※休日の範囲内に文字列が含まれるとエラーになります。そのため、未入力は空白にする必要があります。

＞WORKDAY.INTL関数の使い方について

必要に応じて[エラーメッセージ]を設定し、確定することで適用されます。

以下の画像のように「特定の項目が重複しているデータ」を瞬時に削除する方法について解説しています。
※重複箇所が分かりやすいように黄色で塗りつぶしています。

まず初めに、表の中のセルを選択し、[データ]タブの中の[重複の削除]を選択します。

選択すると、以下のように表のデータの範囲が自動で選択され、[重複の削除]のウィンドウには項目名が表示されます。

※表に空白列や空白行がある場合は、正しい範囲が選択されないことがあります。

その場合は、予め対象の範囲を選択してから、[挿入の削除]を選択します。
この時に、項目名が表示されず列名が表示されることがありますが問題ございません。
項目名を表示したい場合は、項目名を含む全体を選択し、[先頭行をデータの見出しとして使用する]にチェックを入れます。

次に、重複を確認したい項目以外のチェックを外してOKで確定します。

このように簡単に重複した行を削除することができました。
重複データに関して、最も上にあるデータのみが残り、それ以外のデータが削除されます。

表に対し、毎回フィルターで絞り込む作業がある場合は、テーブルのスライサーを活用することで効率化できます。

まず初めに、テーブルの元となる表を選択します。
※表の中に空白行などがある場合は全体を選択します。

この状態で、[挿入]タブの中の[テーブル]を選択します。
ショートカットでは、「Ctrl + T」になります。

以下の画面でテーブルの範囲が問題ないことを確認したら、[OK]を選択します。
選択範囲の先頭行が項目名の場合は、「先頭行をテーブルの…」にチェックします。

作成したテーブルを選択すると、[テーブルデザイン]タブが表示されます。
必要に応じてテーブルの名前を変更します。

作成されたテーブルは、[名前ボックス]から簡単に選択できるようになります。

次に[テーブルデザイン]タブの中から[スライサーの挿入]を選択します。

表示された画面にて、絞り込み対象の項目をチェックし[OK]で確定します。

これで完成です。
後は、表示したい項目を選択するだけで絞り込みできます。
複数選択をする場合は、Ctrlキーを押しながら選択、もしくは、フィルターリセットの隣のアイコンを選択することで行えます。

以下の表の内容をもとに招待状を作成して印刷するという作業を自動化する方法について解説していきます。

まず初めに、できる限り数式を使って招待状を作成できるようにします。
今回の場合は、Noがキーになっているため、Noをもとに、他の氏名や性別が表示されるように以下のように作成します。

=OFFSET(招待者一覧!D2,C3,0) // フリガナ
=OFFSET(招待者一覧!C2,C3,0) // 氏名
=OFFSET(招待者一覧!E2,C3,0) // 性別

こちらではOFFSET関数を使っていますが、場合によってはVLOOKUP関数などでも対応できるかと思います。
今回の場合のOFFSET関数の使い方は以下になります。

=OFFSET(参照, 行数, 列数)
// 参照セルから指定の行数・列数移動した位置のセルの情報を返す

=OFFSET(招待者一覧!D2,C3,0) // フリガナ
// 招待者一覧シートのセルD2を基準にセルC3の数分下へ移動したセルの情報を返す

次に、VBAを使って、Noの値を変更しつつ、招待状シートを印刷します。

[開発]タブの中から[マクロ]を選択し、好みの名前でプログラムを作成します。

こちらでは以下のようなプログラムを作成しています。

Sub 自動印刷()
    
    Dim 開始No As Long: 開始No = 1
    Dim 終了No As Long: 終了No = 20
    
    Dim i As Long
    
    For i = 開始No To 終了No
        Worksheets("招待状").Range("C3").Value = i
        Worksheets("招待状").PrintOut
    Next i
    
End Sub

今回の場合、Noは１からの連番なので、招待状シートのセルC3の値を開始Noから終了Noまで順番に実行しています。
それぞれのNoをセルC3に設定した後に、招待状シートをPrintOutで印刷しています。

このPrintOutは設定されている印刷設定の内容をもとに実行されます。
そのため、こちらのプログラムを実行する前に、印刷設定にて、プリンターや余白などの設定を行う必要があります。

必要に応じてプログラムの一部を変更する必要がありますが、基本はこのようなアルゴリズムで自動化することができます。

▼サンプルファイル▼

特定の項目に関して、重複した入力を防ぐ方法について解説しています。
この方法を活用することで、IDなどの一意の値を設定する際に、誤って重複した値を入力して集計が正しくできなくなってしまうことを未然に防ぐことができます。

では、以下の表のB列に対して、重複した入力を禁止する設定を行います。

初めに、先頭を除いたデータ行の２行目（B4）から一番下まで選択します。

次に、[データ]タブの中の[データの入力規則]を選択します。

データの入力規則の[ユーザー設定]にて以下の数式を設定します。

=COUNTIF($B$3:B3,B4)=0

数式は、「セルB3からセルB3の範囲内でセルB4と同じ値の数が０個の場合」という意味になります。
初めのB3だけ「$B$3」と絶対参照にしているため、それ以外の参照に関して、セルB5に設定される数式は以下のように相対的に変化します。

=COUNTIF($B$3:B4,B5)=0

データの入力規則は、この条件式を満たしている時に関してのみ入力ができるといったものです。
必要に応じてエラーメッセージを設定すると、警告内容が分かりやすくなります。

※スタイルは入力を禁止するのか、警告のみにするのかなどの設定です。
　重複を禁止する場合は「停止」を指定します。

このような設定ができたら、[OK]で設定が完了します。
これで重複した値が入力された場合に警告を表示することができました。

[補足] データの入力規則の場合、外部のセルをコピーして値のみの貼り付けを行うと入力できてしまいます。
そのため、貼り付け禁止などとメモ（コメント）を追加するのも対策として良いかもです。

予め設定した定時と出勤時刻と退社時刻から、勤務時間（早朝勤務、通常勤務、残業）を求める方法について解説しています。

以下の表をもとに、黄色のセルの値を数式を使って求めていきます。

まず初めに早朝勤務時間から求めていきます。
早朝勤務時間を求めるにはIF関数を用います。

=IF(論理式, [値が真の場合], [値が偽の場合])
// 論理式の結果（真・偽）に応じて指定された値を返す

IF関数で、出社時刻が始業時刻よりも早い場合は、その差分を表示させます。

=IF(B6<$C$2,$C$2-B6,0)

※始業時刻に関しては、数式をコピーした際に参照がずれないように絶対参照（$）にしています。

一番下までコピーすることで簡単に早朝勤務時間を求めることができます。

次に残業時間を求めます。
残業時間は早朝勤務時間と同じ求め方で実現出来ます。

IF関数で、退社時刻が終業時刻よりも遅い場合は、その差分を表示させます。

=IF(C6>$C$3,C6-$C$3,0)

※終業時刻に関しては、数式をコピーした際に参照がずれないように絶対参照（$）にしています。

一番下までコピーすることで簡単に残業時間を求めることができます。

最後に通常勤務時間を求めます。
通常勤務時間は、退社時刻から出社時刻を引きて勤務時間を求め、さらに、早朝勤務時間と残業時間を引くことで求められます。

=C6-B6-G6-E6

一番下までコピーすることで簡単に通常勤務時間を求めることができます。

このようにして、それぞれの時間を求めることができます。

フィルターをキーボードのみで操作する方法について解説しています。
キーボードによる操作を覚えることで、効率的に操作できることがあります。

以下の表のフィルターをキーボードのみで操作していきます。

①操作したいフィルターのセルを選択

②[Altキー]+[カーソルキー（↓）]でフィルターの設定画面を開く

③[カーソルキー（↑・↓）]や[Tabキー]、[Shiftキー]+[Tabキー]で設定したい項目まで移動する

④フィルター条件を設定する

・スペースキー：チェックボックスのON・OFF
・カーソルキー（←・→）：グループの開閉

⑤[Enterキー]で確定（OKを押したい場合は、OKが青色になっている位置で[Enterキー]）

フィルターの解除方法に関しては以下の２種類のいずれかが使えます。

１.[Altキー]→[Aキー]→[Cキー]の順番に入力

２.表の中にカーソルを置き、[Ctrlキー]+[Shiftキー]+[Lキー]を２回押してフィルターを再設定

[Ctrlキー]+[Shiftキー]+[Lキー]は、フィルターの設定（解除）のショートカットになります。
フィルターを解除すると絞り込みが解除されるため、解除して再度設定するという流れです。