Excel。横棒グラフの縦軸を反転して表を同じ順番にする
<横棒グラフ>
Excelでは、簡単に横棒グラフを作成することができます。
ただ、縦軸の並び順が、表と逆になっています。
縦軸を、どうやったら、表と同じ順番にすることができるのかという対応方法を紹介しています。
なお、お恥ずかしい限りですが、「軸の反転」を「軸の回転」といってしまっておりますので、ご了承いただけますと幸いです。
Excel・Word・PowerPoint・Accessのテクニックや研修ネタ・テキストを紹介しております。主に、講義先や現場レベルで質問があったものを中心に書いております。
Excelでは、簡単に横棒グラフを作成することができます。
ただ、縦軸の並び順が、表と逆になっています。
縦軸を、どうやったら、表と同じ順番にすることができるのかという対応方法を紹介しています。
なお、お恥ずかしい限りですが、「軸の反転」を「軸の回転」といってしまっておりますので、ご了承いただけますと幸いです。
お馴染みの「¥」マークですが、数値の先頭に表示されるため、桁数によって、一がバラバラで表示されます。
できたら、位置を揃えたいと思ったら、どのようにしたらいいのでしょうか。
セルの書式設定ダイアログボックスを表示するのもいいですが、ホームタブに用意されていますので、そちらの方法をご紹介します。
C2:C5を範囲選択します。
その中にある「会計」をクリックします。
すると、会計の表示形式が設定されました。
「¥」マークは数値の桁数に関係なく、揃っていることが確認できます。
ただし、「通貨スタイル」と「会計」で違う点は、数値が「負数」の時です。
Facebookページに書いた、Excelの豆知識(Trivia)です。
3月10日
Excel。
LENB関数
読み方: レンビー
分類: 文字列操作
LENB(文字列)
文字列のバイト数を返す
3月11日
Excel。
LET関数
読み方: レット
分類: 論理
LET(名前1,名前値1,計算または名前2,[名前値2,…)
計算結果を名前に割り当てます。
3月12日
Excel。
LINEST関数
読み方: ラインエスティー
読み方: ラインエスティメーション
分類: 統計
LINEST(既知のy,[既知のx],[定数],[補正])
重回帰直線の各係数を算出します
3月13日
Excel。
LN関数
読み方: ログ・ナチュラル
分類: 数学/三角
LN(数値)
オイラー数eとする数値の対数を算出します
3月14日
Excel。
LOG関数
読み方: ログ
分類: 数学/三角
LOG(数値,[底])
指定した数を底とする数値の対数を算出する
3月15日
Excel。
LOG10関数
読み方: ログテン
読み方: ベース・テン・ログ
分類: 数学/三角
LOG10(数値)
10を底とする数値の対数を算出する
3月16日
Excel。
LOGEST関数
読み方: ログイーエスティー
読み方: ログエスティメーション
分類: 統計
LOGEST(既知のy,[既知のx],[定数],[補正])
複数の独立変数の回帰指数曲線の係数を算出する
集合縦棒グラフの背面を平均値以上かそうでないかを、視覚的にわかるように、塗り分けをしたいのです。
ただ、図形で塗りつぶすのでは、綺麗にできません。
次のようなグラフを作りたいわけです。
Excelのグラフは、作りたいグラフを表見するためのデータが必要です。
平均値と塗り分けるための数値を含めた表を作ります。
D2の数式は、
=AVERAGE($C$2:$C$8)
D8まで、オートフィルで数式をコピーしています。
E列には、平均値からグラフのプロットエリアの上限を塗るための数値を算出しました。
販売金額の最高値が、1175なので、グラフのプロットエリアの上限を1200としました。
E2には、
=1200-D2
という数式を設定し、オートフィルで数式をコピーしています。
これで、表の準備が完了しましたので、グラフをつくっていきます。
B1:E8を範囲選択します。
挿入のグラフにある、「おすすめグラフ」をクリックします。
グラフの挿入ダイアログボックスが表示されます。
すべてのグラフタブにします。
販売金額を「集合縦棒」
平均値と1200-平均値は、ともに、第2軸として、「積み上げ面」に設定します。
設定後OKボタンをクリックします。
妙なグラフができましたが、気にせずに、設定作業を進めます。
左側の縦軸と右側の第2軸縦軸の最大値を1200に変更します。
書式タブをクリックして、「縦(値)軸」を選択したら、選択対象の書式設定をクリックします。
プロットエリアの上部に表示された「第2軸横軸」をクリックします。
軸の書式設定作業ウインドウの「軸のオプション」にある「軸位置」を目盛に変更します。
GESTEP関数
読み方: ジーイーステップ
分類: エンジニアリング
GESTEP(数値,[しきい値])
数値が境界値以上かを判定する
納品書・請求書で必須の自動更新する日付。
Excelでは、自動更新する日付を設定するには、TODAY関数をつかいます。
では、Wordでは、どのようにしたら、自動更新する日付を設定することができるのでしょうか。
平均値以上のデータは、どこなのかを、見てわかるようにしたいのですが、どのようにしたらいいのでしょうか。
問題になってくるのが、条件です。
ホームタブの条件付き書式にある上位/下位ルールにある、「平均より上」をつかうと、平均値より大きい値のセルを塗りつぶすことができます。
ただし、今回は「以上」ですし、データ全体ということですから「行全体」を塗りつぶしたいわけです。
よって、条件式を設定しなければなりません。
A2:C8を範囲選択します。
ホームタブの条件付き書式にある「新しいルール」をクリックします。
条件のボックスに、条件式を設定します。
=$C2>=AVERAGE($C$2:$C$8)
あとは、書式ボタンをクリックし、塗りつぶす色を設定したら、OKボタンをクリックすれば、平均以上のデータの行全体を塗りつぶすことができます。
行全体を対象にする場合には、「$C2」のように、列番号を固定した複合参照に設定することで対応することができます。
日々の売上データで、年間売上なら、YEAR関数をつかって年を算出してから年ごとに算出します。
また、月間なら、MONTH関数をつかって算出することができます。
では、日曜日から土曜日までの週間売上はどのようにしたらいいのでしょうか。
それが、WEEKNUM関数です。
D2に
=WEEKNUM(A2,1)
という数式を設定して、オートフィルで数式をコピーします。
すると、その日が第何週なのかを算出することができます。
また、WEEKNUM関数の2つ目の引数ですが、今回は日曜日から土曜日までを週としていますので、「1」と設定します。
週が算出できましたので、あとは週ごとに集計します。
G1にSUMIF関数をつかって週ごとの集計をすれば、週ごとの集計を算出することができます。
G1の数式は、
=SUMIF($D$2:$D$16,F1,$C$2:$C$16)
と設定して、オートフィルで数式をコピーしています。
Facebookページに書いた、Excelの豆知識(Trivia)です。
3月3日
Excel。
KURT関数
読み方: カート
分類: 統計
KURT(数値1,[数値2],…)
データセットの尖度(せんど)を算出します
3月4日
Excel。
LAMBDA関数
読み方: ラムダ
分類: 論理
LAMBDA(パラメータまたは計算,…)
カスタムの再利用可能な関数を作成し、それらを表示名で呼び出します
3月5日
Excel。
LARGE関数
読み方: ラージ
分類: 統計
LARGE(配列,順位)
指定した○番目に大きい値を算出します
3月6日
Excel。
LCM関数
読み方: エルシーエム
分類: 数学/三角
LCM(数値1,[数値2],…)
整数の最小公倍数を算出します
3月7日
Excel。
LEFT関数
読み方: レフト
分類: 文字列操作
LEFT(文字列,[文字数])
文字列の左端から文字を取り出す
3月8日
Excel。
LEFTB関数
読み方: レフトビー
分類: 文字列操作
LEFTB(文字列,[バイト数])
文字列の左端から指定バイト数の文字を返す
3月9日
Excel。
LEN関数
読み方: レン
分類: 文字列操作
LEN(文字列)
文字列の文字数を返す
テーブルの納品日フィールドの日付から同月の月末日を算出するには、どうしたらいいのでしょうか。
ExcelのEOMONTH関数のようにしたいわけです。
月末支払日: DateSerial(Year([納品日]),Month([納品日])+1,0)
演算式が長いので、ズーム機能をつかってみてもいいでしょう。
実行して確認してみましょう。
設定した演算フィールドのポイントは、月を+1して、日は「0(ゼロ)」とする点です。
翌月0日にすることで、前月末日にすることができます。
GEOMEAN関数
読み方: ジオミーン
分類: 統計
GEOMEAN(数値1,[数値2],…)
数値の相乗平均を算出する Geometricの略
Facebookページに書いた、Excelの豆知識(Trivia)です。
2月25日
Excel。
ISODD関数
読み方: イズオッド
分類: 情報
ISODD(数値)
対象が奇数の場合にTRUEを返す
2月26日
Excel。
ISOMITTED関数
読み方: イズオミテッド
分類: 情報
ISOMITTED(argument)
LAMBDAの値がないかどうかを確認し、TRUEまたはFALSEを返す
2月27日
Excel。
ISOWEEKNUM関数
読み方: アイエスオーウィークナム
読み方: アイエスオーウィークナンバー
分類: 日付時刻
ISOWEEKNUM(日付)
ISO週番号を算出する
2月28日
Excel。
ISPMT関数
読み方: アイエスピーエムティー
読み方: イズ・ペイメント
分類: 財務
ISPMT(利率,期,期間,現在価値)
元利均等返済における指定期間の利息を算出します Lotus1-2-3互換性維持
2月29日
Excel。
ISREF関数
読み方: イズリファレンス
分類: 情報
ISREF(テストの対象)
対象がセル参照の場合にTRUEを返す
3月1日
Excel。
ISTEXT関数
読み方: イズテキスト
分類: 情報
ISTEXT(テストの対象)
対象が文字列の場合にTRUEを返す
3月2日
Excel。
JIS関数
読み方: ジス
分類: 文字列操作
JIS(文字列)
半角文字を全角に変換する
複数列をつかった条件で並べ替えをしました。
その後、別の表へコピーする場合、作業的には、難しくはありませんが、面倒です。
そこで、SORTBY関数をつかうと、手早く別の表で並べ替えた状態で表示することができます。
次の表を用意しました。
処理したいことは、A1:D7の表を、クラス順の得点は降順に並べ替えた状態で別の表を作りたいわけです。
複数列の並べ替えなので、データタブの並べ替えをつかい、その後コピーすればいいわけです。
ただ、SORTBY関数をつかうと、並べ替えの作業も、コピーも、まとめて処理してくれます。
それでは、F1に次の数式を設定します。
=SORTBY(A2:D7,C2:C7,1,D2:D7,-1)
たった、この数式だけで、対応することができます。
オートフィルで数式をコピーする必要はありません。
スピル機能により、数式がコピーされます。
SORTBY関数の最初の引数は、「配列」です。
範囲なので、見出し行を除いた、「A2:D7」を設定します。
スピル機能のため、絶対参照は不要です。
2つ目の引数は、「基準配列1」です。
これは、最初の条件の並べ替え範囲のことを指しています。
クラス順にしたいので、「C2:C7」。絶対参照は不要です。
3つ目の引数は、「並べ替え順序1」です。
これは、「1」なら昇順。
「-1」なら降順の設定をおこないます。
クラス順なので、昇順ですから「1」と設定します。
2つ目と3つ目の引数を条件数分繰り返すことで、複数列で並べ替えを設定することができます。
なお、SORT関数という並べ替えをする関数もあります。
SORT関数でも複数列での並べ替えは、可能ですが、SORT関数は、一度に複数列での並べ替えができないので、SORT+SORT関数のネストにしなければ対応することができません。
GCD関数
読み方: ジーシーディー
読み方: グレーテスト・コモン・ディバイザー
分類: 数学/三角
GCD(数値1,[数値2],…)
整数の最大公約数を算出します
作業効率もUPする、知っていると便利なショートカットキー。
Ctrl+Alt+V
形式を選択して貼り付けダイアログボックスが表示されます。
Ctrl+Alt+=
拡大表示します。
Ctrl+Alt+-
縮小表示します。
Ctrl+Alt+F5
ブック内のすべてのデータを更新します。
Ctrl+Alt+F9
最終変更に関わらず開いているブックのすべてのワークシートを再計算
表を作成するたびに、外側は、太い外枠。
そして内側は、格子を設定するのは、単純作業ですが、面倒です。
Sub 罫線太い外枠と格子()
Range("b2").CurrentRegion.Borders.LineStyle = True
Range("b2").CurrentRegion.BorderAround Weight:=xlThick
End Sub
あとは、実行して確認します。
では、プログラム文を確認します。
格子を設定しているのが、1行目です。
Range("b2").CurrentRegion.Borders.LineStyle = True
CurrentRegionプロパティは、B2を始点として連続するセルを表と判断するプロパティです。
これで、表全体を範囲選択することができます。
Borders.LineStyle = True
Bordersは、罫線を意味します。
LineStyle = True は、格子を設定します。
ちなみに、LineStyle = False とすれば、格子を削除することができます。
つまり、「枠なし」にすることができます。
2行目が、外側の太い外枠です。
Range("b2").CurrentRegion.BorderAround Weight:=xlThick
BorderAroundメソッドは、外枠の設定を行います。
Weight:=xlThick で、線を太くする設定をしております。
Facebookページに書いた、Excelの豆知識(Trivia)です。
2月18日
Excel。
ISERROR関数
読み方: イズエラー
分類: 情報
ISERROR(テストの対象)
対象がエラー値の場合にTRUEを返す
2月19日
Excel。
ISEVEN関数
読み方: イズイーブン
分類: 情報
ISEVEN(数値)
対象が偶数の場合にTRUEを返す
2月20日
Excel。
ISFORMULA関数
読み方: イズフォーミュラー
分類: 情報
ISFORMULA(参照)
セルに数式が含まれている場合にTRUEを返す
2月21日
Excel。
ISLOGICAL関数
読み方: イズロジカル
分類: 情報
ISLOGICAL(テストの対象)
対象が論理値の場合にTRUEを返す
2月22日
Excel。
ISNA関数
読み方: イズエヌエー
分類: 情報
ISNA(テストの対象)
対象がエラー値の#N/Aの場合にTRUEを返す
2月23日
Excel。
ISNONTEXT関数
読み方: イズノンテキスト
分類: 情報
ISNONTEXT(テストの対象)
対象が文字列でない場合にTRUEを返す
2月24日
Excel。
ISNUMBER関数
読み方: イズナンバー
分類: 情報
ISNUMBER(テストの対象)
対象が数値の場合にTRUEを返す
Excel・Word・PowerPointでつかる、便利な機能は、いろいろあります。
今回は、写真を正円でトリミングするには、どのようにしたらいいのでしょうか。
トリミングに「図形に合わせてトリミング」というのがあります。
ただ、「正円」でトリミングしようとしても、「楕円」しか「図形」に用意されていません。
では、どのようにしたら、「正円」でトリミングをすることができるのでしょうか。
「図形に合わせてトリミング」だけでは、正円にすることができないので、「縦横比1:1」を合わせてつかうことで、正円でトリミングすることができます。
データが入力されると自動的に通し番号が入力できれば、ほんの少しかもしれませんが、入力作業も改善できるかもしれません。
では、どのようにしたら、データを入力したら、自動的に通し番号を入力できるのでしょうか。
今回は、SEQUENCE関数をつかった方法をご紹介していきます。
次の表を用意しました。
B列に利用者名を入力すると、A列に通し番号が自動的に入力するようにしたいわけです。
A2にSEQUENCE関数をつかった数式を設定します。
=SEQUENCE(COUNTA(B:B)-1)
あとは、スピル機能によって、ゴーストが発生しますので、オートフィルで数式をコピーする必要はありません。
B列にデータが入力されるたびに、自動的に、スピル機能によって、通し番号が自動的に入力してくれます。
数式を確認しておきましょう。
SEQUENCE関数は、「数列を返す」関数です。
最初の引数は、「行」です。
そこで、COUNTA関数でB列のデータに入力されている件数を算出させて、見出し行分を減算しますので、「-1」を追記ます。
これで、通し番号を入力することができます。
なんで、SEQUENCE関数をつかうのでしょうか。
それは、スピル機能に対応した関数だということ。
自動的に拡張してくれます。
次のような関数でも、同じような処理をすることができます。
=IF(F2<>"",ROW()-1,"")
ただ、この数式では、数式をコピーする必要があるということです。
今回は、新しい関数であるSEQUENCE関数をつかってみました。
ちょっとしたことで、どうして・なんでそうなるの。ということがExcelで発生します。
例えば、次のようなケース。
原因は、ホームタブにある「数値の形式」。
たまたまなのか、以前そのような書式の設定をしたままだったのか、理由はわかりませんが、「文字列」になっています。
これでは、数式も文字列で表示されてしまうというわけです。
「文字列」から「標準」に戻してから、再度、数式を設定することで、対応することができます。
GAUSS関数
読み方: ガウス
分類: 統計
GAUSS(x)
指定した標準偏差の範囲になる確率を算出します
計算結果が空白でも、元から空白でも、セルが空白にみえるなら、その空白セルの件数を算出するのが、COUNTBLANK関数です。
ところが、COUNTBLANK関数は条件がついてしまうと、対応することができません。
なぜかというと、COUNTBLANK関数の引数は、「範囲」だけしかないからです。
そして、この引数の「範囲」は、連続した範囲でないといけません。
つまり「,(カンマ)」で範囲を追加することもできません。
では、次のような表の場合、どのようにしたら、条件付きの空白セルの件数を算出することができるのでしょうか。
B列の上期下期が「上期」でC列の提出確認が「空白(空欄)」のセルの件数を算出するには、COUNTBLANK関数がつかえません。
そこで、COUNTIFS関数をつかえば、算出することができます。
E2に設定した数式は、
=COUNTIFS(B2:B9,"上期",C2:C9,"")
COUNTIFS関数は、複数条件で件数を算出することができる関数です。
なので、空白セルに条件を付けた場合は、COUNTIFS関数で対応することができます。
Facebookページに書いた、Excelの豆知識(Trivia)です。
2月11日
Excel。
INT関数
読み方: イント
読み方: インテジャー
分類: 数学/三角
INT(数値)
最も近い整数に切り下げる
2月12日
Excel。
INTERCEPT関数
読み方: インターセプト
分類: 統計
INTERCEPT(既知のy,既知のx)
回帰直線の切片を算出します
2月13日
Excel。
INTRATE関数
読み方: イントレート
分類: 財務
INTRATE(受渡日,満期日,投資額,償還価額,[基準])
満期に償還される証券の利率を算出します
2月14日
Excel。
IPMT関数
読み方: アイピーエムティー
読み方: インタレストペイメント
分類: 財務
IPMT(利率,期,期間,現在価値,[将来価値],[支払期日)]
元利均等返済における指定期間の利息を算出します
2月15日
Excel。
IRR関数
読み方: アイアールアール
分類: 財務
IRR(範囲,[推定値])
定期キャッシュフローに対する内部利益率を算出します
2月16日
Excel。
ISBLANK関数
読み方: イズブランク
分類: 情報
ISBLANK(テストの対象)
対象が空白セルの場合にTRUEを返す
2月17日
Excel。
ISERR関数
読み方: イズイーアールアール
読み方: イズエラー
分類: 情報
ISERR(テストの対象)
対象がエラー値の#N/A以外の場合にTRUEを返す
GAMMALN.PRECISE関数
読み方: ガンマログナチュラル・プリサイス
分類: 統計
GAMMALN.PRECISE(x)
ガンマ関数の値の自然対数を算出します
AかつBという条件をつくることができる「AND関数」ですが、欠点があります。
それが、ワイルドカードをつかった条件式には対応してくれないということです。
次の表をつかって説明します。
AND関数をつかってみたところ、思っているように算出されません。
C2に設定した数式は、
=IF(AND(A2="A*",B2="*横浜市*"),"○","")
AND関数とワイルドカードの組み合わせは、うまくいかないようです。
AND関数で、ワイルドカードをつかわない数式にすれば、算出することはできます。
ワイルドカードを使わない数式は、
=IF(AND(MID(A2,1,1)="A",MID(B2,5,3)="横浜市"),"○","")
ただ、MID関数をつかうので、可読性が下がってしまいます。
そこで、COUNTIFS関数をつかうことで、対応することができます。
C2の数式を次のように設定して、オートフィルで数式をコピーします。
=IF(COUNTIFS(A2,"A*",B2,"*横浜市*"),"○","")
数式を確認しておきましょう。
IF関数の引数であるCOUNTIFS関数から説明します。
COUNTIFS関数の最初の引数が、「検索条件範囲1」です。A2を設定します。
2番目の引数は、「検索条件1」。ここに「”A*”」とワイルドカードをつかっています。
「A*」とすれば、「Aで始まる」という意味です。
3番目以降は条件範囲と、条件の繰り返しです。
B2のあたいに、「*横浜市*」なのかを確認する条件式を設定します。
「*横浜市*」はB2に「横浜市という文字が含まれる」という意味です。
この2つの条件が合致したら「TRUE」、合致しない場合は「FALSE」と判定結果がでます。
IF関数をつかってTRUEならば「”○”」と表示します。
テータを指定した文字で連結するには、「TEXTJOIN関数」をつかえば対応することができます。
列の区切りを「,」(カンマ)で、行の区切りを「;」(セミコロン)、全体を「{ }」で囲む、「配列定数」にしたい場合には、TEXTJOIN関数では対応するのが大変です。
そこで、ARRAYTOTEXT関数をつかうことで、配列定数の形式にした文字列に変換できます。
A5に次の数式を設定しました。
=ARRAYTOTEXT(A2:B3,1)
これで、全体を{}で囲み、列の区切りは「,」で行の区切りを「;」の文字列にすることができました。
では、ARRAYTOTEXT関数を確認しておきましょう。
最初の引数は、「配列」です。
範囲なので、A2:B3と設定します。
2つ目の引数は、「書式」です。
0の簡潔を選択すると、すべてを「,」(カンマ)で区切ってくれます。
1の正確ならば、全体を{}で囲み、列の区切りは「,」で行の区切りを「;」の配列定数で区切ってくれます。
なお、このARRAYTOTEXT関数。
文字列に変換するので、日付は、シリアル値に変わってしまうので、注意が必要です。
日付や曜日を手早く入力する。
あるいは、数式をコピーするときに、つかっているオートフィル。
ただ、ドラッグしているだけではもったいない。
Ctrlキーを押しながらオートフィルをドラッグすると、連番が簡単に入力できます。
そして、日付は同じ日付を入力することができます。
日頃使っている数値。
桁区切りの記号は「,(カンマ)」で小数点は「.(ドット)」です。
これは、英語圏を中心とした場合で、フランスなど欧州などでは、逆で表示された数字を使っています。
つまり、桁区切りの記号が「.(ドット)」で小数点が「,(カンマ)」というわけです。
よって、そのままでは、計算で使おうとすると、文字型なので、#VALUE!というエラーが表示されてしまいます。
計算式としてつかうためには、通常の数字にしなければなりません。
そこで、NUMBERVALUE関数をつかって、文字型を数値に変更します。
B3に設定した数式は、
=NUMBERVALUE(B1,",",".")
これで、B1のフランス式の文字を数値として変換することができます。
では、設定した数式を確認しておきましょう。
最初の引数は、「文字列」。
B1を設定します。
フランス式で入力した場合数値として認識してくれません。
文字列になっています。
2つ目の引数は、「小数点記号」。
B1の文字列の中で、小数点でつかっている記号をExcelに教える必要があります。
小数点で使用しているのは「,」なので、「","」と設定します。
最後の引数は、「桁区切り記号」。
2つ目の引数と同じように、B1の文字列の中で、桁区切りでつかっている記号をExcelに教えるので「”.”」を設定します。
そもそも、NUMBERVALUE関数をつかわなければいけないのかというと、VALUE関数ではパーセントや通貨とみなせる文字列データを数値に変換できます。
ただし、「.」と「,」はVALUE関数に対応していないので、NUMBERVALUE関数を使う必要があるというわけです。
GAMMALN関数
読み方: ガンマログナチュラル
分類: 統計
GAMMALN(x)
ガンマ関数の値の自然対数を算出する