inst_yamamoto のすべての投稿

オートサムボタンの横関数 COUNT関数 MAX関数 MIN関数

オートサムのボタンの右に下向き三角がありそこには合計、平均、数値の個数、最大値、最小値、その他の関数と6つあります。合計はSUM関数、平均はAVERAGE関数を参照してください。その他の関数はこれら5つ以外のすべてなので、数値の個数、最大値、最小値の3つの関数をご紹介します。

MAX関数(最大値)
一連の引数のうち、最大の数値を返します。
=MAX(数値 1, [数値 2], …)

MIN関数(最小値)
一連の引数のうち、最小の数値を返します。
=MIN(数値 1, [数値 2], …)

フィルターオプション ワイルドカード等

フィルターオプションよりもう少し細かい使い方をご紹介します。

今回は、抽出先を指定した範囲として常に元の表はそのままで抽出結果を別の場所にします。

以下のような表で商品名がみかんのデータをフィルターで抽出します。

セルF1に元の表の見出しと同じ商品名という見出しを入力します。見出しの下に=”=みかん”と入力します。

元の表のセルをクリックしデータタブの並べ替えとフィルター詳細設定をクリックします。フィルターオプションの設定が表示されますので検索条件範囲にセルF1からセルF2をドラッグして指定します。(自動で$マークの付いた絶対参照になります。)

抽出先の指定した範囲を選択しセルk1をドラッグで指定します。オプションの設定のOKボタンをクリックします。

元の表の見出しを含めて、商品名がみかんのものが抽出されます。

次に抽出先を少し変更します。セルk1とセルk2だけ残して抽出先のデータをいったん削除します。

この状態でサイト商品名がみかんのものだけ抽出します。フィルターオプションの設定で、抽出先を先ほどの2つの見出しセルK1からセルL1のセルをドラッグで選択します。OKボタンをクリックします。

指定した見出しのデータのみ抽出されます。抽出先の見出しの並び順は元の表通りでなくても大丈夫です。

通常のフィルターの場合同じ項目(列)で2つのAND条件が設定できました。例えば単価が100円以上200円未満だとかです。フィルターオプションは横にAND条件縦にOR条件になります。複数のANDの場合は、列見出しを複数作ります。

単価が100円以上、200円未満の場合は検索条件範囲に単価を2つ作ります。

セルF1には=”>=100″、セルG2には=”<200″と入力します。検索条件範囲セルF1からG2として、抽出先範囲はいったんデータを削除してから任意のセル(セルK1)を選択します。

OKボタンをクリックします。

単価のAND条件がうまくいきました。

文字列の抽出でワイルドカードを使う

フィルター テキストフィルターでも出来たように任意の文字、または、文字列を表す

* 任意の長さの文字(0文字以上)
? 任意の1文字

をワイルドカードと言ったりします。

コーヒーやチョコレートなどの伸ばす長音を含む商品だけを抽出する場合は

=”*ー*”

という風に指定します。*は0文字以上何文字でもいいので『ー』が商品名に含まれていれば該当します。

=”*ん”の場合は『ん』で終わっていれば抽出されます。『ん』一文字でも該当します。『みかん』は該当しますが、『こんにゃく』は『ん』で終わっていないので抽出されません。

商品名で「=”*に*”」とすれば何が抽出されるでしょうか?

『こんにゃく』と『にんにく』が抽出されました。『に』を含んでいればいいので今回の表にはありませんが、『にんじん』や『かに』なども抽出されます。

分かりやすいようにそれぞれ別個に指定しましたが、複数行組み合わせて商品名が『みかん』で価格が『100円以上』『200円未満』で数量が『5以上』か(ORで別の行に)商品名に『に』を含むもののような使い方ができます。

フィルターオプション

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ファイル