FC2ブログ

簡易カレンダー

2014.09.28 21:36|Excel実例
092801.png



 薄い黄色の部分のみ入力すれば、あとは自動的に日付・曜日などが出てくる簡易カレンダーです。ここに出勤・休暇や時給計算を加えれば、パート・アルバイト用の賃金計算になるのですが、とりあえず簡易カレンダーのみにとどめました。祭日には対応していません。
 実際に入力するのは、「予定」以外の箇所です。

 開始日の欄には、年度・月度から求められる月の開始日を入力します。
 年度(B4)・月度(C4)が空白の時は空白を返すので、IF関数で OR(B4="",C4="") のTRUEの場合を「""」とします(ORは「または」の意)。そうでない場合には指定された日付に対応するシリアル値を返すDATE関数(DATE(年,月,日))を用いて、日付を返します。なので、D3には、=IF(OR(B4="",C4=""),"",DATE(B4,C4,1))と入力します。月の始まりは必ず1日ですので、DATE関数の「日」部分は「1」となります。
 終了日は、月によって28日(または29日)・30日・31日がありますので、「翌月1日から1を引いた日(DATE(年,月+1,1)-1)」と入力します。開始日と同じく、年度(B4)・月度(C4)が空白の時は空白を返すので、IF関数で OR(B4="",C4="") のTRUEの場合を「""」とします。F4には、=IF(OR(B4="",C4=""),"",DATE(B4,C4+1,1)-1) と入力します。
 なお、「~」の部分は、D4が空白のときは空白を返し、そうでない場合は「~」を返すようにしています(=IF(D4="","","~"))。
 また、開始日・入力日は、表示形式の「日付」を指定しています。

 フリガナ欄には、氏名(G4)に入力された文字のフリガナを返すようにします。フリガナを返すのは、PHONETIC関数を用います。
 G4が空白のときは空白を返すので、IF関数を用いて=IF(G4="","",PHONETIC(G4))と記述します。
 なお、G4のふりがなの設定を「ひらがな」にすると、H4に表示されるフリガナはひらがなになります。

 日付欄(B7:B37)は、表示形式をユーザー定義の「m"月"d"日"」としました(「9月1日」と表示されます)。B7は開始日なので「=D4」と記述します。B8には、「一つ前の日付が終了日(F4)以上であれば空白、そうでなければ一つ前の日付+1」という数式(=IF(B7>=$F$4,"",B7+1))を記述します(コピーするので、終了日を絶対参照にします)。フィルハンドルをドラッグ、もしくはダブルクリックで、B8の数式をB37までコピーします。

 曜日欄は、表示形式を指定して表示することもできますが、平日/休日欄で平日か休日かを区別するために、WEEKDAY関数CHOOSE関数を用いて表示します。

 WEEKDAY関数
 =WEEKDAY(シリアル値,種類)
 シリアル値には、日付の入力されているセルまたは日付を指定します。種類は1~3まであり、「1」は日曜日を「1」として返し、「2」は月曜日を「1」、「3」は火曜日を「1」として返します(月曜日が「0」)。何も指定しないと、自動的に日曜日を「1」として返します。

 CHOOSE関数
 =CHOOSE(インデックス,値1,値2,…)
インデックスでは、値で指定したリストの何番目を返すかを指定します。=CHOOSE(1,"あ","い","う","え","お")とすると、1番目の「あ」が返されます。
 WEEKDAY関数は曜日ではなく数字が返ってくるので、WEEKDAY関数の種類を「1」とした場合、日曜日を意味する「1」をさらにCHOOSE関数の値で「日曜日」と指定する必要があります。

 IF関数で、B7が空白のときは空白を返します。それ以外の場合は、WEEKDAY関数でB7の曜日の番号を返し、さらにその番号に対応する値をCHOOSE関数で返します。
 =IF(B7="","",CHOOSE(WEEKDAY(B7,1),"日曜日","月曜日","火曜日","水曜日","木曜日","金曜日","土曜日"))
 と記述します。

 平日/休日欄(D7)は、IF関数でB7またはC7が空白されば空白を返し、それ以外の場合は土曜日または日曜日であれば「休日」、それ以外は「平日」と返すようにします。=IF(OR(B7="",C7=""),"",IF(OR(C7="土曜日",C7="日曜日"),"休日","平日"))と記述します。
 数式をコピーし、平日/休日欄に条件付き書式を設定します。「セルの強調表示ルール」の「指定の値に等しい」で、「休日」に「濃い赤の文字、明るい赤の背景」を設定します。
092802.png

スポンサーサイト



Excelの関数・13 よく使う文字列操作の関数(基本編2)

2014.09.27 20:49|Excel関数
 TRIM関数
 文字列の先頭や末尾に挿入された空白(スペース)をすべて削除して返します。文字列内に空白が連続して含まれている場合には、空白を1つ残して、他の余分な空白は削除します。
 名簿などの氏名欄で、余計な空白を削除する場合に用います。なお、文字列内の半角スペースは半角のまま残ってしまいますので、下の例ではJIS関数と組み合わせて全角にしています。

0927.png


 FIND関数、SEARCH関数
 どちらも検索する文字列が何番目にあるのかを求める関数です。FIND関数が大文字・小文字を区別するのに対して、SEARCH関数は大文字・小文字を区別しないという違いがあります。また、SEARCH関数では、検索文字列にワイルドカード(?、*)が指定できます。
 今回はFIND関数を用いて、TRIM関数で返した氏名を、さらに姓・名に分けて取り出します。

 姓は =LEFT(文字列,FIND(" ",文字列)-1) で求められます。この式は、「 」(全角スペース)までより1字少ない文字列を左から返す、という意味になります。

092702.png


 名は =RIGHT(文字列,LEN(文字列)-FIND(" ",文字列)) で求めます。この式は、文字列から「 」(全角スペース)までの文字数分引いた文字列を、右から返す、という意味になります。

092703.png

Excelの関数・12 よく使う文字列操作の関数(基本編1)

2014.09.26 20:27|Excel関数
 住所録、名簿を作るときなどによく使われる関数です。
 ※ 実際にハガキ・DMなどで使用する場合(宛名やラベル名、Wordの差し込み文書、Accessのレポートなどで用いる場合)には、いったんコピーしてから「値」で貼り付ける必要があります(そのまま用いると、数式が挿入されてしまいます)

 ASC関数
 文字列をすべて半角にして返します。郵便番号などで数字に全角が使われている場合に用います。

 JIS関数
 こちらはASC関数とは逆に、文字列をすべて全角にして返します。
 名前などに半角カタカナが用いられている場合、この関数を用いて全角にします。

 UPPER関数、LOWER関数、PROPER関数
 UPPER関数は、文字列をすべて大文字にして返します。LOWERは逆で、文字列をすべて小文字にして返します。
 PROPER関数は、先頭の1文字を大文字にし、それ以降の文字を小文字にして返します。

 LEFT関数、MID関数、RIGHT関数
 それぞれ、「左から指定した文字数」の文字列、「指定した文字から指定した文字までの文字数」の文字列、「右から指定した文字数」の文字列を返します。
 たとえば、C3に「東京都世田谷区三軒茶屋」とあった場合、任意のセルに「=LEFT(C3,3)」と入力すると、「東京都」が返ってきます。「=MID(C3,4,4)」なら「世田谷区」、「=RIGHT(C3,4)」なら「三軒茶屋」が、それぞれ返されます。
 実際に都道府県名を抜き出したい場合には、「県」まで含めて4文字の県(神奈川県・和歌山県・鹿児島県)がありますので、IF関数で論理式を設定する必要があります。
 考え方としては、文字列の4文字目の1字が「県」である場合(TRUEが(MID(文字列,4,1)="県")は左から4文字目まで返し(LEFT(文字列,4))、それ以外(FALSE)は左から3文字目まで返す(LEFT(文字列,3))と考えます。
 そうすると、数式は =IF(MID(文字列,4,1)="県",LEFT(文字列,4),LEFT(文字列,3)) となります。
 これで、住所のうち、都道府県名だけを返すことができます。

092601.png


 都道府県以下の住所を返すには、さらに文字数を返す「LEN」関数を用います。

 LEN関数
 指定した文字列の、文字数を返します。
 住所のうち、都道府県名以下の住所を抜き出したい場合などに用います。
 例えば、上述のように都道府県名のみを取り出している場合、住所全体の文字列から都道府県名の文字数を引いた文字列を返すことができます。

092602.png


 =RIGHT(C3,LEN(C3)-LEN(D3)) で、「C3セルの文字数からD3セルの文字数を引いた文字数分のC3の文字列を、右から返す」という意味になります。
 RIGHT関数を使わずにMID関数を使って表示することもできます。この場合は、=MID(C3,LEN(D3)+1,LEN(C3)-LEN(D3)) となります。「C3セルの文字列のうち、D3セル(東京都)+1文字目(「世」)からC3の文字数引くD3の文字数分の文字を返す」という意味になります。RIGHT関数のほうが簡単だと思いますが、「MID関数を使って抜き出しなさい」という問題がMOSの模擬試験に出てきたので、一応 記しておきます。

 続きます。

Excelの便利な技・3 関数を使って計算しなくても、平均・データの個数・合計が分かる

2014.09.23 22:01|Excel便利技
 意外と知られていないのが、ステータスバー。数値を入力したセルの範囲を選択するだけで、ステータスバーの右側(表示モードの左側)に、平均・データの個数・合計が表示されています。
 データの入力途中、まだ関数を使わない段階で、平均・個数・合計を確認できて、なかなか便利です。

0923.png

Excelの関数・11 SUBTOTAL関数(集計行を表示する)

2014.09.20 19:13|Excel関数
 集計行を表示するには、「=SUBTOTAL(集計方法,セル範囲)」という関数を入力するのですが、もっと分かりやすい方法で集計行を表示する方法があります。
 まず、データを用意します(参照:統計局ホームページ http://www.stat.go.jp/)。

subtotal001.png


 [データ]タブの「アウトライン」グループから「小計」を選択します。
 「集計の設定」ダイアログボックスから、「グループの基準」、「集計の方法」、「集計するフィールド」を選択します。今回は、「地域区分」別に「総人口」を集計したいので、「グループの基準」は「地域区分」、「集計の方法」は「合計」、「集計するフィールド」は、平成24年推計人口が2つのセルにまたがっていて分かりづらいのですが、「総人口比」です。これで、地域区分別の人口集計を出すことができます。

subtotal002.png


 次に、「地域区分」ごとに「人口性比の平均」を出したいとします。グループの基準は「地域区分」、集計の方法は「平均」、「集計するフィールド」は、正しくは「人口性比」ですが、「平成24年推計人口」がかぶっていて間違ってしまっている「(列D)」です。
 なお、このとき、前に集計した分が消えてしまわないよう、「現在の小計をすべて置き換える」のチェックをオフにします。

subtotal003.png


 地域区分ごとに小計が出され、最終行に全体の平均と、総計が出ます。

subtotal004.png


 また、集計を実行すると自動的に行番号の左側にアウトライン記号が表示されます。アウトライン記号から、下位のグループの表示/非表示を設定できます。
 下位グループを非表示にしたほうが、グラフなどを作りやすいこともあります。

subtotal005.png

Excelの便利な技・2 条件付き書式

2014.09.17 19:29|Excel便利技
 ルール(条件)に基づいてセルに特定の書式を設定したり、数値の大小関係が視覚的にわかるように装飾する「条件付き書式」を設定します。
 ルールに基づいて、該当するセルの書式を変える「セルの強調表示ルール」、選択範囲内の数値の大小関係を比較して、バーで表示する「データバー」、同じく選択範囲内の数値の大小関係を段階的に色分けする「カラースケール」、選択範囲内の数値の大小関係をアイコンの図柄で表示する「アイコンセット」などがありますが、今回は指定した上位の項目・下位の項目の該当セルに特定の書式を設定する「上位/下位ルール」を設定します。

zyoukentsuki001.png


 「人口」の上位10県に「濃い赤の文字、明るい赤の背景」、下位10県に「濃い緑の文字、緑の背景」を設定します。余談ですが、「その他のルール」から、書式を設定する県の数(上位5位まで、など)や、設定する書式の情報を変えることができます。
 上位10県を設定したら、同じように下位10県を設定します。

zyoukentsuki002.png


 こうして見ると、やはり関東の首都圏に集中しているということがよく分かりますね……。

Excelでグラフを作る・3 スパークライン

2014.09.14 22:15|Excelグラフ
 「スパークライン」とは、「1つのセルに収まる小さなグラフ」のことです。[挿入]タブの「スパークライン」グループから選択・作成します。
 見本のデータは、「複合グラフ」でも使った、東京・那覇の平均気温。「年間推移」なので正しくは「折れ線グラフ」を使うべきですが、見やすいので縦棒グラフを使用しました。

spl001.png


 ダイアログボックスからデータ範囲を選択して完成。ちなみに、スパークラインはオートフィルで次の行にコピーできます。

spl002.png


 スパークラインは、データ範囲の中の最大値をセルの上端、最小値をセルの下端としてグラフ化されますので、関連する複数のスパークラインを作成するときは、最小値または最大値を固定します。
 最小値を揃える場合は、形式を揃えたいスパークラインを複数選択し、[スパークラインツール]タブ→[デザインタブ]の「グループ」グループ内「軸」から、「すべてのスパークラインで同じ値」を選ぶか、数値を指定したい場合には、「縦軸の最小値のオプション」→「ユーザー設定値」を選択し、最小値に設定したい数値(今回は「0.0」)を入力します。

spl003.png


 [スパークラインツール]タブ→[デザイン]タブの「表示」グループで、「頂点(山)」にチェックを入れると最大値のデータが、「頂点(谷)」にチェックを入れると最小値のデータが、それぞれ強調されます。

spl004.png


 余談ですが、入力したデータは、[ホーム]タブ→「クリップボードグループ」の「貼り付けオプション」の「行列を入れ替える」で貼り付けると、列と行が入れ替わった状態で貼り付けることができます。Excelの貼り付け機能は多彩な機能が備わっていて、「形式を選択して貼り付け」からは、入力規則や列幅、コメントなども貼り付けることができます。
 なお、行・列を反対にしてグラフを作ってしまった場合は、グラフを作ったあとで[グラフツール]タブの[デザイン]タブ→「データ」グループから、「行/列の切り替え」ができます。

Excelでグラフを作る・2 複合グラフ

2014.09.13 19:21|Excelグラフ
 複合グラフとは、大きな開きがあるデータ、単位の異なるデータを一つのグラフエリアに表示したい場合に用いるグラフです。一番分かりやすい例は、平均気温と降水量かな……と思いましたので、データを用意して、複合グラフを作成してみました。

hukugou001.png

 東京と那覇の平均気温と、那覇の平均降水量のグラフです。データは気象庁のホームページを参照しました(「各種データ・資料」→「過去の気象データ検索 」)。
 ちなみに、Excel2013では、[挿入]タブ→「グラフ」グループ内に「複合グラフの挿入」というボタンがすでに用意されていますが、Excel2010での作業、または「通常のグラフを作ってしまってから複合グラフに変える」ケースを想定して解説しています。
 グラフを作成するのは通常の手順と同じで、範囲を選択をして[挿入]タブの「グラフ」グループからグラフを作成します(この場合は「2-D縦棒」の「集合縦棒」)。
 その後で、種類を変更したいグラフを選んで、[グラフツール]タブ→[デザイン]タブ→「種類」グループの「グラフの種類の変更」を選択します。

hukugou002.png

 グラフの種類を選択し、種類を変更したいグラフを「第2軸」にします。

hukugou003.png

 Excel2010では、グラフの種類を変更したあと、変更したグラフの上で右クリックし、「データ系列の書式設定」→「系列のオプション」から「使用する軸」の「第2軸(上/右側)」にチェックを入れます。
 主軸と第2軸とで単位が異なるので、軸ラベルを挿入する([グラフツール]タブ→[デザイン]タブ→「グラフのレイアウト」グループ内「グラフ要素の追加」)と分かりやすいです。

Excelでグラフを作る・1 グラフの種類について

2014.09.11 22:43|Excelグラフ
 Excelでグラフを作成するのは、とても簡単です。なぜなら、グラフにしたい範囲を選択し、[挿入]タブの「グラフ」グループから、グラフの種類を選ぶだけだからです(Excel2013になると、「おすすめグラフ」という機能が付け加えられ、さらに機能性が上がりました)。作業自体はとても少ないのです。
 グラフの種類や機能が豊富で、むしろ、「どの種類のグラフを選んだらよいのか?」という点で迷うことが多いようです。
 グラフの種類の目安としては、一般的に以下のようになっています。

 棒グラフ:データの大小関係を示すのに用いる。縦軸にデータの量、横軸に日付などが入る
 折れ線グラフ:時間の経過による推移(データの増減)を表すのに用いる。縦軸にデータの量、横軸に時間などが入る
 円グラフ:割合、内訳などを示すのに用いる。すべてを合算すると100%となる
 積み上げグラフ:総数と内訳を示すのに用いる。数の違うもの同士の項目ごとの割合を比べる
 レーダーチャート:複数の特性間のバランスを示すのに用いる
 複合グラフ:単位の異なる複数の要素の関連性を示すのに用いる。基本的に、主軸は左側となる

 グラフを作るさいの範囲の選択にも迷いますが、円グラフなどは「合算が100%」なので、グラフを作成するさいは、元データの「集計(合計)」などは範囲に含まずに作成する必要があります。
 Excelでは基本的に、データを入力するさいには、「連続して増加するデータは行方向(下方向)に入力していく」ということを意識していくと、グラフ作成のさいにも縦軸をどの値に使うかなど、迷わなくてすむようになるかと思います。

Excelの便利な技・1 移動、コピーに関する便利技

2014.09.08 21:49|Excel便利技
 文字の移動、入れ替え
 文字が入力されているセルに、文字をドラッグして移動しようとすると「コピーまたは移動先のセルの内容を置き換えますか?」と警告が出てきます。
 そうではなく、セルの左右もしくは上下の文字を入れ替えたい場合は、Shiftキーを押しながらドラッグします。
 また、右クリックしながらドラッグすると、コピー・移動の種類を選択することができます。

 いろいろなコピー
 Excelにはさまざまな形式のコピー(厳密には貼り付け)方法があります。数式のコピー、書式のみのコピー、書式なしのコピー、罫線なしのコピーなどのほか、「形式を選択して貼り付け」では、入力規則、コメント、列幅のコピーなどもできます。
 面白いのが「演算」。たとえば「1000」と入力されたセルをコピーし、「12000」と入力されたセルに「演算」の「除算」を貼り付けると、「12」となるのです。コピー・貼り付けで計算まで行えるのです。これは、単位の違う数値同士を計算する場合などに、知っておくと意外と便利な機能です。
| 2014.09 |
- 1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 - - - -
カレンダー

08 | 2014/09 | 10
- 1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 - - - -

最新記事

カテゴリ

月別アーカイブ

検索フォーム

RSSリンクの表示

リンク

QRコード

QR

ページトップへ