IT予備
業務効率化のコツが無料で学べる！Excelなどの解説&配布サイト
「特定の曜日」と「特定の週」だけでも表示することができる万年カレンダーについて解説しています。
それぞれの日付が、「曜日」と「週」から計算されているため、行や列の削除をしても数式が壊れないようになっています。
そのため、行や列を削除しても、年月を切り替えるだけで自動で日付が切り替わります。
00:00 挨拶
00:25 完成イメージ
01:13 準備
01:51 作成（数式）
14:38 作成（レイアウト）
17:51 完成
18:47 まとめ
▼準備ファイル▼
Excelには数多くのテンプレートが用意されています。
そのテンプレートのクオリティはかなり高いので、使えるものは使いたいですね。
テンプレートを使用する方法は、Excelを立ち上げた画面から[新規]を選択します。
[新規]の画面内の検索ボックスにて、必要なテンプレートのタイトルを検索します。
その検索結果の中から、必要なテンプレートを選択することで、活用することができます。
テンプレートのファイルには複雑な設定がされていることもあるため、こちらをベースに大きなカスタマイズをすることは推奨しません。
仕組みを理解していない状態でカスタマイズすると、予期せぬ不具合に繋がる可能性が大きいためです。
基本、スピンボタンは数値を入力する用のフォームになります。
ただ、使い方によっては文字をスピンボタンにより変化させることができます。
例えば、スピンボタンを使って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にする
下記の図のように、表の中の一部の罫線を書式設定の画面を開かずに消す方法について解説していきます。
まず、罫線を削除したい範囲を選択します。
複数個所ある場合は、Ctrlキーを押しながら複数選択します。
こちらでは、下記のように選択しています。
この状態で、「Ctrl + Shift + ＼(バックスラッシュ)」を入力します。
※＼(バックスラッシュ)キーは／(スラッシュ)キーの右隣で、ひらがなの「ろ」が書かれているキーになります。
これだけで下記の図のように選択範囲の内側の罫線を一括で消すことができます。
また、一番外側の罫線に関しても消されるようになっています。
[補足] ちなみに、選択範囲の外枠の罫線を引きたい時は、「Ctrl + Shift + &」になります。
※選択範囲に対して、内側を含め全てに罫線を引く簡単なショートカットはありません。
ただ、少し複雑ですが、Altキーから順番に入力して好みの罫線を引くことができます。
(例)「Alt → H → B → A」と順番に入力した場合
自動で担当を割り当てる機能について解説しています。
それぞれの役割に人数を設定してプログラムを実行するだけで、ランダムに割り当てます。
「この役割はこの人には割り当てたくない」などの設定も可能です。
00:00 挨拶
00:17 完成イメージ
02:03 準備
02:28 実現方法
05:17 作成（担当自動割当）
19:21 作成（割当ボタン）
19:50 完成
21:47 プログラムの全体
29:01 まとめ
▼準備ファイル▼
今回は小技というよりかは、ExcelをGoogleスプレッドシートで操作する際の注意点などについて解説していきます。
題名通り、Googleドライブに配置したExcelファイルは、Web上でGoogleスプレッドシートを使って開くことができます。
ExcelファイルをGoogleスプレッドシートで開くことで、Googleスプレッドシートにしか存在していない関数を扱うことができます。
また、一部の関数(FILTER関数など)に関しては、ExcelとGoogleスプレッドシートでは使い方が若干異なるものも存在します。
では、ExcelファイルをGoogleスプレッドシートで開いて修正し、それをExcelで開き直したときにどのような弊害があるのかを確認していきます。
そこで、実際にExcelファイルをGoogleスプレッドシートで開き、Googleスプレッドシートにしか存在しない関数と、Excelにも存在する関数の２種類を入力していきます。
※ExcelファイルをGoogleスプレッドシートで開くと、ファイル名の隣に「.XLSX」と拡張子が表示されます。
実際に上記のファイルをExcelで開いた結果が以下になります。
見た目上は正しく計算結果が表示されているため、問題なく見えるのですが、入力されている数式を確認すると、想定とは異なる式になっているかと思います。
SUM関数など共通の関数は問題なく表示されていますが、QUERY関数などGoogleスプレッドシートにしか存在していない関数の場合は、IFERROR関数を使って抽出結果が表示されています。
このように疑似的に対応がされるのです。
少し特殊な文字に置かれている理由は、Googleスプレッドシートで開き直した際に、元の数式に自動で置換するためになります。
ただ、Excelで扱う場合は、IFERROR関数による疑似的な表現になるため、数式の参照元をいくら修正しても数式の結果は更新されません。
というような感じに、ExcelファイルをGoogleスプレッドシートで開くと上記のようなリスクがあるということを覚えておくことが大切です。
そのため、原則はExcelファイルはExcelで開くと決めておいた方が、数式が壊れたりしないので安全かと思います。
Officeスクリプトの変数と型について解説しています。
また、数値と文字列の演算方法から、変数を活用して実際のセルを操作する方法についても解説しています。
00:00 挨拶
00:19 変数の用意（変数宣言）
02:16 変数の型
05:33 数値の演算
08:49 文字列の演算
10:46 別の変数宣言
12:58 変数を活用してセルの操作
17:21 まとめ
「複数行・複数列の表を１列の表にする方法」と、「１列の表を複数行・複数列の表にする方法」について解説しています。
※この記事の内容は、現時点(投稿日時点)では３６５でないと対応しておりません。
・複数行・複数列の表を１列の表にする方法
複数行・複数列の表を１列の表にするには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,"")
こちらもセットで覚えておくと良いです。
TEXTJOIN関数を活用することによって、以下のように、セル範囲の値を「,」と「;」で文字結合することができます。
まずは、簡単な文字結合から確認していきます。
TEXTJOIN関数は以下のような使い方になります。
=TEXTJOIN(区切り文字, 空白のセルは無視, テキスト1, [テキスト2], [テキスト3], …)
// 区切り文字を指定して指定テキストを結合する引数[区切り文字]を直接指定する場合は、「”(ダブルクォーテーション)」で囲みます。
引数[空白のセルは無視]に関して、「TUREを指定した場合」と「FALSEを指定した場合」の違いは以下のようになります。
=TEXTJOIN("・",TRUE,C3:K3)
=TEXTJOIN("・",FALSE,C3:K3)
TRUEの場合は、空白セルが無視されるようになっています。
引数[テキスト]に関しては、離れているセルの場合は、「,(カンマ)」区切りで入力し、連続しているセルの場合は、範囲指定で入力できます。
複数の範囲がある場合の違いは以下のようになります。
複数の行列を含む範囲の場合は、列方向が優先して結合されます。
=TEXTJOIN("・",FALSE,B3:E5)
=TEXTJOIN("・",FALSE,B3:E3,B4:E4,B5:E5)
=TEXTJOIN("・",FALSE,B3:B5,C3:C5,D3:D5,E3:E5)
では、一定間隔で異なる区切り文字を設定する方法について解説していきます。
その方法は、引数[区切り文字]に関して配列で渡すという方法です。
例えば、以下のように「・」と「(空白)」を繰り返したい順番に「{}」の中で指定します。
区切り文字は全て「”」で囲む必要があります。
また、区切り文字と区切り文字は「,」で区切ります。
=TEXTJOIN({"・","・"," "},FALSE,C3:K3)
このように、指定の順番に異なる区切り文字で区切ることができました。
さらに応用すると以下のようにも使えます。
=TEXTJOIN({",",",",",",";"},FALSE,B3:E5)
若干分かりづらいですが、「,」という文字を３つと、「;」の計４つの区切り文字を「,」で区切って指定しています。
{",",",",",",";"} → {"," , "," , "," , ";"}
指定日以降でないと入力できない設定方法について、営業日を考慮しない方法と考慮する方法の２パターンを解説しています。
１.以下の表の対応予定日に関して、申込日から３日後以降でないと入力ができないような設定を行います。
入力制限する対象のセルを選択し、[データ]タブの中の[データの入力規則]を選択します。
[データの入力規則]の画面にて、[入力値の種類]を「ユーザー設定」にし、以下の数式を入力します。
C3>=B3+3初めの「=」は「今から数式が始まります」というような意味になります。その後に、入力を許可する条件式を設定します。
こちらでは、「セルC3は、セルB3の３日後以降である」という条件式になります。
日付はシリアル値で管理されているため、このように「+」で計算することができます。
次に、[エラーメッセージ]に関しても以下のように設定します。
[スタイル]を「停止」にすることで、対象外の日付を入力できなくなります。
※デフォルト値は「停止」です。
上記の設定で確定することによって、申込日から３日後以降でないと入力ができなくなりました。
２.以下の表の対応予定日に関して、申込日から営業日を考慮した３日後以降でないと入力ができないような設定を行います。
同じように[データの入力規則]を開きます。
ここで設定する数式は先ほどとは異なり以下のようになります。
=C3>=WORKDAY.INTL(B3,3,1,E3:E100)WORKDAY.INTL関数を用いて定休日(土日)と休日を除いた３日後の日付を求めています。
休日一覧表に関しては、データが追加されても反映されるように、「E3:E100」と１００行目まで考慮しています。
※休日の範囲内に文字列が含まれるとエラーになります。そのため、未入力は空白にする必要があります。
必要に応じて[エラーメッセージ]を設定し、確定することで適用されます。
