inst_yamamoto のすべての投稿

フィルターオプション

1つの列に対して複数(2つ以上)の条件を指定したいときにフィルタオプションを使います。

フィルターを駆使すればいろいろな抽出が可能なのですが、フィルタオプションを使うとどのようなフィルタをかけたが目に見える形で残ります。また、フィルターは元の表の行が折りたたまれて非表示になりますが、フィルタオプションは抽出結果を別の場所に表示することも可能です。

上のような表の場合見出し列にボタンがありフィルターの図柄になっています。また、列見出しが青色で番号が飛んでいますのでフィルターがかけられてるのが分かります。ただ、どのようなフィルターがかかっているかはこれを見る限りではわかりません。

フィルタオプションはフィルターをかけたい表と同じ見出し行を別の場所に作成し、その下にフィルターの条件を数式で入力します。

一般的にフィルターをかけたい表の上部にフィルタオプション用の見出しを作る場合が多いです。(横に作ると元の表が折りたたまれる場所によっては、フィルタオプション用の表も一緒に折りたたまれて見えなくなってしまいます。)

通常のオートフィルタ オプションでは、ANDとORが選択でき2つ指定できます。

フィルタオプションは横にAND、縦にORということで沢山の項目でフィルタをかけられます。

例えばみかんで単価が100円以上のものを抽出する場合はセルA2とセルB2に条件を入力します。

数式なので=イコールを入力してから改めて文字列として式を入力します。セルに表示されているのは=みかんと>100なのですが入力は=”みかん”と=”>100″の様にダブルクォーテーションで括って文字列として入力しています。

次にデータタブの並べ替えとフィルターの詳細設定をクリックします。フィルタオプションの設定のダイアログボックスが表示されます。この時元の表の中をクリックして選択しておくとリスト範囲が自動で選択されます。

。フィルタオプションの設定のダイアログボックスの検索条件範囲に先ほど作った見出しと数式のある範囲をドラッグして選択します。今回はセルA1からセルD2になります。

いづれも、自動で$マークの付いた絶対参照になります。OKボタンをクリックします。

これで商品名みかんで単価が100より大きいものが抽出されました。両方の条件を満たすAND条件です。

>100を1行下にするとどちらかの条件を満たすORになります。

セルB2の>100をセルB3にずらします。再度詳細設定をクリックし、検索条件範囲をセルA1からセルD3に変更しOKボタンをクリックします。

商品名はみかん、単価は100より大きいのどちらかの条件を満たしているものが抽出されました。検索範囲の選択は慎重に行ってください。条件が入っているのに範囲が選択されていないとか、空白行まで選択してしまうとすべての行が抽出されてしまうので注意してください。

2つの条件くらいなら普通のフィルターでもできますが、たくさんの項目でフィルターをかける場合や、フィルターの条件が見えているので便利です。

通常のフィルターでは元データが折りたたまれますし、フィルタオプションでも元のデータが折りたたまれますがフィルター オプションの設定から指定した範囲を選択すると別の場所に抽出結果が表示されます。(元データは折りたたまれません。)

元の表が折りたたまれませんのでフィルタオプションの見出しを横に作ります。並べ替えとフィルターの詳細設定をクリックします。検索条件範囲を指定しなおします。フィルタオプションの設定のダイアログの抽出先を指定した範囲を選択し、抽出範囲のボックスに抽出先の左上のセルを指定します。OKボタンをクリックします。(矢印は関係ありません)

これで元の表が折りたたまれることなく抽出条件が明示され、抽出データが別のセルに書き出されました。

グラフの書式のコピー

グラフにいろいろと書記を設定したグラフと同じ書式のグラフをまた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 システムツールのコントロールパネルをクリックします。

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

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

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

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