月別アーカイブ: 2017年6月

グラフの書式のコピー

グラフにいろいろと書記を設定したグラフと同じ書式のグラフをまた1から作成するのは非常に面倒ですね。どのような書式を設定したかも忘れてしまいます。まずグラフをコピーして、書式を張り付けます。

グラフのコピーはコピー貼り付けだけですので簡単ですがグラフのコピーを参照してみてください。

相変わらずのAKB48の指原さん推しの円グラフです。

円グラフを作成するときにセルA1からセルB7までを範囲選択し、できた円グラフに対して塗りつぶしやデータラベルなどを設定しました。

このグラフをコピーしてC列の45thの円グラフを作りたいです。グラフのコピーは普通に選択してコピーして、貼り付けたいセルを選択し貼り付けでコピーできます。

コピーしたグラフのデータをB列からC列に変更したいのですがグラフエリアを右クリックしたデータの選択のデータ ソースの選択からは45thが選択できません。

グラフのデータ範囲を選択しなおします。A列からC列まで選択すると左に41thと45thが選択できるようになるので45thだけ選択する方が汎用性が高いのですが、書式のコピー時にうまくいかないことがあるので、CTRLキーを使ってA列とC列を個別に選択します。ふつうにセルA2からセルA7まで選択します。次にCTRLキーを押したままセルC2からセルC7までを選択します。

2つの範囲がカンマ区切りで入力されています。

グラフのデータ範囲が変更されてグラフの内容も変更されたのですが、先ほどの書式が反映されていません。元のグラフから書式をコピーして貼り付けではうまくいきません。この辺りはエクセルのバージョンにより少し事情が違う場合があります。

まず元のグラフをコピーします。書式をコピーしたいグラフを選択し貼り付けボタンの下の下向き三角▼をクリックします。形式を選択して貼り付けをクリックします。

形式を選択して貼り付けのダイアログボックスが表示されますので書式をチェックして、OKボタンをクリックします。

これで同じ書式の年度違いの円グラフが作成できました。

グラフのコピー

グラフのコピー自体は簡単にできます。コピー元のグラフをクリックして選択しコピーボタンをクリックします。(右クリックからコピーでもCtrlキー+Cキーでも構いません。)

貼り付けたい場所(セル)を選択し、貼り付けのボタン(いずれかの貼り付け方法)をクリックします。

おそらく同じグラフは2つも必要ないと思われます。コピーで増やした方のグラフを加工してみます。現在最高気温と平均気温と最低気温の3系列となっていますが、最高気温だけの折れ線グラフに変更します。

グラフエリア内で右クリックします。メニューが表示されますのでデータの選択をクリックします。

データソースの選択のダイアログボックスが表示されます。今回は最高気温だけにしたいので左側の系列から最高気温だけチェックが入った状態にし、OKボタンをクリックします。

うまく最高気温だけの折れ線グラフになりました。縦軸最低気温に合わせて-10までついていますので調整したい場合は、縦軸の書式設定から変更してください。

現在、最高気温だけの折れ線グラフになっていますが、作成時(左のグラフをコピーしてある)に3系列ありましたので残りの2系列は非表示になっているだけですので再度右クリックからデータの選択をクリックすると、非表示の2系列を再表示することが可能です。

ところが全く見た目同じグラフなのですが、データソースの表示をした時にほかの系列が選択できないときがあります。

これはグラフ作成時、または系列の選択時にグラフのデータ範囲を変更した場合です。すこしわかりづらいですが、図を見比べていただくとグラフをクリックして選択したときの元の表の選択範囲が違います。3系列選択できる法のグラフは、表示が1系列でも元の表は3系列選択されています。ほかの2系列が選択できない方のグラフは、見出し行と最高気温しか選択されていません。

データソースの選択のダイアログボックスのグラフ データの範囲というところの値が違っています。

3系列選択できる方のグラフ データの範囲   =Sheet1!$A$1:$M$4
1系列しか選択出来ないグラフ データの範囲 =Sheet1!$A$1:$M$2

となっています。グラフデータの範囲はグラフ作成後も変更できます。データソースの選択のダイアログボックスを表示した直後の状態はグラフ データの範囲が選択されていますのでそのまま元の表をドラッグすれば変更可能です。(いつでもグラフ データの範囲の式をドラッグして選択すれば変更できます。)

3系列以外の降水量行を選択(ドラッグ)する降水量のグラフに変更することができます。

エクセルでリンク集 HYPERLINK関数

ホームページ上のリンクテキストをコピーペーストすると自動でハイパーリンクが挿入されますが、HYPERLINK関数をつかえば任意のリンク集が作成可能です。

リンクテキストを選択しコピーします。

エクセルに貼り付けます。

自動的にハイパーリンクが挿入されクリックでWebページが表示されます。

HYPERLINK関数

セルのデータ、書式、コメント、罫線など一気に削除

書式の設定されたセルのデータはデータだけを削除しても書式は保持されています。

下のエクセル画像のセルA1は現在空白ですが、文字サイズ、文字色、書式で日付が設定されています。試しに10000と入力してみます。

まだエンターキーで確定していませんが、すでに文字サイズが大きく、文字色が赤です。

エンターで入力データを確定すると10000と入力したのに1927年5月18日と表示されました。数式バーにも10000という数値は残っていません。10000は1900年1月1日から10000日目なんでしょうね。

通常の文字サイズで文字色も通常の黒、10000と入力したら、日付ではなく10000と表示されるようにするためには、設定された書式をひとつずつ戻す方法もありますが、何が設定されてるか全部調べるのも面倒ですし、それをいちいち初期値に戻すのも大変です。

ボタン1発で文字通りすべてクリアします。

セルA1には0.01と入力されているのに00.011111のような表示になっています。計算や文字色、背景パターン、コメントなど様々な書式が設定されています。

すべてクリアしたいセルを選択しホームタブの編集のクリアのすべてクリアをクリックします。

データ、書式設定、コメントなど文字通りすべてクリアされました。

データ(数式)ごと全部クリアしてよい場合とても便利です。これ以外にも、書式だけのクリアや、書式を残してデータ(数式)のクリアなどもあります。

横棒グラフ 系列を逆にする

簡単に横棒グラフを作ってみました。(表の中をクリックして挿入タブのグラフのおすすめグラフから集合横棒を選択します。グラフ1(棒グラフ作成)など参照してください)

表の並び順んと棒グラフの並び順が逆になっています。

これを表のワード、エクセル、Windows10の順に変更します。

縦軸をクリックして選択します。更に右クリックしてメニューを表示します。軸の書式設定をクリックします。

軸の書式設定が表示されます。軸のオプションの軸を反転するにチェックを入れます。

系列が反転します。この時に縦軸が右に来てしまった場合は、横軸を選択して軸の反転を行っていますので軸を反転するのチェックを外し、いったん元に戻してから縦軸を選択しなおし、軸を反転してください。

このままでは横軸が上に着たままですので横軸をクリックして選択します。

軸の書式設定のラベルのラベル位置から上端/右端を選択します。少し手数は掛かりますが、表と同じ順に系列を並べることができました。

数式以外のセルのデータを一括削除

セルのデータの削除方法についてです。

エクセルのセルに入力されているデータを削除したい場面はいくつもあると思います。しかし、数式が入力されているセルを削除してしまうと困ったことになることがあると思います。

自分がすべて作成したのならどこに数式が入っているか、どこのセルの値は消してもいいかは分かりやすいでしょうが、人が作ったエクセルシートはどこのデータを消してよいのかすぐには判断つきにくいと思います。

数式が入力されていないセルのデータだけを簡単に削除できれば便利だと思います。下の図は請求書からダウンロードできるエクセルブックです。

再利用しようと思ったときはどこを消せばよいでしょうか。日付や請求書番号、相手先のご住所や会社名などを、変更しますね。ここは文字列なので遠慮せずに削除して下さい。

表の部分は計算式が入っていますので慌てて上書きしてしまうと焦ってしまいます。この表の場合は文字列に見える商品名の列はVLOOKUP関数で商品番号から自動で表示していますので、商品名を消してしまいますと商品名を手入力しなければいけなくなります。

そうしますと、データを削除するときにいちいちセルを選択して数式バー数式が入っているかどうかを確認したのちにしかデータを削除できません。

数式が入っていないセルだけを選択する方法をご説明します。まず、データを消したいエリアを選択します。見出し列(商品番号や商品名)は文字列ですが、変更する必要はありませんね。小計や消費税、合計はおそらく計算式で自動計算と思われます。

変更したいデータのセルと、計算式の入っているセルをまとめて選択した状態です。ここから、計算式ではないセルだけを選択します。

ホームタブの編集の、検索と選択をクリックします。ジャンプをクリックします。

ジャンプのダイアログボックスが表示されます。

セル選択のボタンをクリックします。

定数にチェックを入れてOKボタンをクリックします。

これで数式以外のデータが入力されているセルが選択できましたのでDELETEキーで削除してください。

ダウンロードしていただけるサンプルには名前ボックスでクリアを選択していただければ商品番号の列と数量の列は自動で選択されます。

seikyusyo_6

B5サイズ 横書き 便箋

ワード便箋が人気のようです。便箋サンプルはA4横書きでしたので、B5横書き25行の便箋のサンプルをアップしておきます。

相変わらずヘッダーとフッターにサイトのコマーシャルが入っていますので何かと差し替えてください。

ヘッダー部分をダブルクリックしていただければヘッダー領域もフッター領域も編集可能になりますので、差し替えや削除などの作業を行ってください。

また、この状態で罫線の上をクリックするとすべての罫線が選択されます。(グループ化しています)描画ツールの書式タブの図形スタイル、図形の枠線から罫線の色、太さ、種類を変更してカスタマイズすることが可能です。

また、ページ罫線なども入れてみてはいかがでしょうか。

カスタマイズが完了したら左右の余白の上でダブルクリックしてください。通常の文字入力の状態になります。任意の名前で保存しておいてください。

参考までにダウンロードファイルは自己解凍圧縮ファイルにしていますのでダブルクリックで解凍してください。

罫線黒の実線の便箋
罫線黒の破線の便箋
罫線グレーの実線の便箋
罫線グレーの破線の便箋

顔上記4つ詰め合わせ自己解凍.exeファイル

勤務表 月別 時間給

日別月間 万年スケジュール DATE関数 MONTH関数制作時にすでに出来上がっていたのですが、なぜか公開していませんでした。なぜだったんでしょう。

少し見直しをして、公開いたします。ダウンロードできますが、よく動作を確認してくださいね。これは実際にに使い込んでいませんのでもしかしたら見落としがあるやもしれません。

終業時間から始業時間と休憩時間を引いて実労時間を算出しています。入力方法によっては実労時間が1分単位になりますので丸め時間を設定しています。

丸め時間が30分なら29分までは切り捨てです。59分までが30分労働となります。実労時間のHOUR部分と丸め後の分に時給をかけてその日の日当(小計)が計算されています。交通費欄に書かれた金額も加算されます。

変更部分

セルB2 :氏名
セルA3 :西暦
セルC3:月
セルG3:時間給
セルI3:丸め時間(60にしておけば1時間未満は59分でも0円)

これらのセルを設定すればあとは始業時間と終業時間、休憩時間、交通費を入力すれば37行目に累計されます。

月間150時間から180時間などの丸め後の時間が要る場合はL列に丸め後の時間を表示していますので数式を参考に作り替えてみてください。

簡単にシートの計算式などに触れておきます。

全体の表については日別月間 万年スケジュール DATE関数 MONTH関数と同じものです。F列の実労時間は引き算なのですが、終業時間から始業時間と休憩時間を引いていますので始業時間だけが入力された時点ではマイナス値になってしまいます。マイナス値が表示されるのは嫌なので表示形式 (数値)にあるようにマイナス値;ゼロ値;文字列は表示しないように、表示形式を

[h]:mm;;;

というように設定しています。正の数の時だけ時:分と表示されるようにしています。[]は24時間以上の場合時間が0に戻ってこずに、24:15とか26:50のような表示形式です。時間の計算 FLOOR関数を参照ください。

金額のセルは実労時間の時間部分に時給をかけたものに実労時間の分の部分を丸め時間で割った商に時間給をかけて丸め時間給(15分あたりとか30分あたりとか)をかけたものを足しています。端数が出ないようにTRUNC関数で切り捨てています。

=IF(F5>0,HOUR(F5)*$G$3+TRUNC(TRUNC(MINUTE(F5)/$I$3)*$G$3*$I$3/60))

$G$3は時給のセル番地、F5は実労時間のセル番地、なのでHOUR(F5)*$G$3が実労のじか時間部分だけに対する金額。

TRUNC(MINUTE(F5)/$I$3)が丸め時間単位が何回あったか(15分が3回とか)、$G$3*$I$3/60が丸め時間単位あたりの給与

TRUNC(TRUNC(MINUTE(F5)/$I$3)*$G$3*$I$3/60)が実労時間の分に対する給与額になります。1円未満が出ないようにTRUNC関数で切り捨てています。

されにその金額を計算するにあたっては労働時間が0より大きい時だけ計算するようにif文で囲んでいます。

計算用の途中経過の値のセルを作っていませんのでわかりずらいかもしれませんが、そう難しいことはやっていません。

くれぐれもご使用の際は何度も検証の上、自己責任でご利用ください。すでに1日24時間以上の実労時間の場合はうまくいかないことは分かっています。

勤務表のダウンロード

Windows10 コントロールパネルの開き方

最近いつもの調子でWindowd10でコントロールパネルを開こうとして開けなかったので記録に残す意味でも、ウィンドウズ10でのコントロールパネルの開き方を改めてご紹介します。

いつもの調子でという方法ですが、Windowd10のスタートボタンを右クリックして表示されるメニューにコントロールパネルがpというショートカットになっています。

スタートボタンを右クリックがウィンドウズキー + x で開けるので、windowskeyウィンドウズキー+xキー(二つのキーの同時押し)の後pキーを叩くとコントロールパネルが表示さていました。ところがこれが効かなくなっていました。

なんと、windowskeyウィンドウズキー+xキーのメニューにコントロールパネルがなくなっていました。pキーも効きません。

コルタナさんにお願いする。音声入力だとWeb検索になってしまいますので文字入力します。日本語で途中まで入力すると候補が表示されますのでクリックします。

またはWindows システムツールのコントロールパネルをクリックします。

頻繫に利用する場合は右クリックでスタートにピン止めするをクリックします。

スタートボタンのパネルに登録されます。タブレットになっている方は便利かもしれません。

もちろんコントロールパネルを開いている状態で、タスクバーのアイコンを右クリックしてタスクバーにピン止めする方法も使えます。

わずかですが、ショートカットキーが無効になったので不便になりました。

フィルター テキストフィルター

フィルター (データの抽出)でやったような性別によるフィルターなら、男、女、非公開、未選択など、あっても数種類なら選択肢からの抽出で十分です。(最近は性同一性障害への配慮から性別欄自体が消えつつありますのであまり適切な例ではないかもしれません)

ちょっと例題が無理やりですが、特定の文字を基準にフィルターをかけることが可能ですのでご紹介します。

このようなでたらめの住所録で名前の列でテキストフィルターをかけてみます。

セルA1の名前の右の三角をクリックしテキストフィルターをポイントします。指定の値に等しいとか等しくないとかのメニューが表示されます。

指定の値に等しいは完全一致、指定の値に等しくないは完全一致の裏ですね。

例えば『吉村 佳乃』で指定の値に等しい(ドロップダウンのと等しい)でフィルタをかけると完全に一致する『吉村 佳乃』さんだけが抽出されました。同姓同名(同じ文字列)の人がいればその人も抽出されます。

『吉村 佳乃』で指定の値に等しくないは『吉村 佳乃』以外の人全部が抽出されます。

指定の値で始まるで『吉村』と入力すると『吉村 佳乃』と『吉村 真一』が抽出されます。

指定の値で終わるで『乃』とすると乃で名前が終わる3名が抽出されました。

指定の値を含むは始まる、終わる以外にそれらをどこかに含んでいれば抽出対象になります。含まないはその裏ですね。

『村』を含むで抽出すると吉村さんも、高村さんも村上さんも抽出対象になります。とにかく『村』という文字さえ含まれていれば選択されます。

では、2文字目に村という文字が含まれている人だけを抽出するのどのようにすればよいでしょうか。

オートフィルターのオプションのダイアログの左下に書いてあります。

半角のはてな?を使えば?が1文字分になってくれます。オールマイティな文字だと考えるとわかりやすいかもしれません。

?村で始まるにすると、? はある時は田村の田、ある時は吉村の吉、またある時は梅村の梅の文字の代わりになります。〇村さんみたいに伏字で〇はどんな文字(1文字)でもいい世的な感じです。

吉村、高村、岡村、、田村浦などが抽出されます。村田は抽出されませんし、三田村も抽出されません。?は任意の1文字なので三田村の様に3文字目に村が来る人を抽出したいなら、??村と?を2回続けて入力するとうまくいきます。

*は任意の文字列となっており?とは少し違います。?は必ず1文字ですが、*は1文字とは限りません。何文字で*1つで表せます。

*村で始まるでフィルターをかけてみます。

*は任意の(長さの)文字列ですので吉村は該当します。三田村も該当します。少し驚かれるかもしれませんが、村上も該当します。

*は任意の文字列ということで空の文字も該当する。または任意の長さの文字として0文字も該当すると考えていただければ理解しやすいかもしれません。

フィルター 数値フィルターでもやりましたようにオートフィルターのオプションは2つ入力ボックスがあり間にANDとORのチェックボックスがありますので2つの条件で抽出することが可能です。

*村で始まる AND *子で終わるにすれば、□村 □子さんが抽出されます。□は任意の文字列(0文字も含む)ということで以下の図のような結果になります。村上翔子、三田村璃子、村奥育子など。

テキストフィルターをポイントしたときのメニューには表示がありませんが、以上と以下がドロップダウンのリストにあります。

これはCODE関数の戻り値を基準にそれ以上、それ以下の値に該当すると思われます。