今週見かけた気になるExcel記事

今週見かけた気になるExcel記事をリストアップ

・管理人のOfficeは2013のため影響はないけど、こんなのに遭遇してコード吹き飛んだら泣くな。 www.atmarkit.co.jp

・以前に百マス計算を作ってあげたらしばらく興味持ってやってくれてたんだよな。 www.atmarkit.co.jp

・今一番おもしろいExcel VBA関連のネタを提供してくれるt-homさん関連 thom.hateblo.jp

thom.hateblo.jp

thom.hateblo.jp

・実務ではよくあるCSV取込について

www.atmarkit.co.jp

・こちらも実務で使いみちがありそう

forest.watch.impress.co.jp

・IF関数とそれと組み合わせると便利な関数について

excel-master.net

・アンケートに活用すると集計が楽になりそう

excel-hack.com

・全然存在すら知らなかった関数。

ExcelのAGGREGATE関数の使い方|リストやデータベースの集計値を返す|エクセルハック

HELPの消し方

勝手にHELPが表示される

f:id:ExcelLover:20170902150414j:plain

ExcelのHELP、関数の引数を調べるのにとても便利でよく使います。
が、このHELPウィンドウ、呼び出してもいないのに(※正確には呼び出したつもりもない)勝手に表示されてイラッとくることも多々あります。
なんで出てくるかというと HELPを呼び出すショートカットキー「F1」をクリック しているからです。

HELPの消し方

F1で表示されたHELPを「×」をクリックして消すのは、恐らく今右手はキーボードの上にあると思われるので、マウスに手を伸ばさないといけないことになり手間やストレスが余計にかかっていまします。
ショートカットキーで消すのが早く・ストレスレスで消せます。

・ Alt + F4
・ Ctrl + F1(未確認ですがExcel2003まではAlt + F4ではなくこちら)
・ Alt + Space → c (Altを押しながらスペースを押して、メニュー表示されたら C )
f:id:ExcelLover:20170902150434j:plain

個人的にはF4が遠いので Alt + Space → c を使っています。
この2つのショートカットキーはHELP以外の、Excel以外のウィンドウも消せますので何かと重宝します。

※世の中にはHELPのショートカットキー自体を潰す方法もあるようですね

Excel HELP 無効 - Google 検索

月末日付の求め方・EOMONTH 関数

月末日付の求め方

請求書の締切日などを記載するために月末の日付を算出しないといけないことは多々あると思われます。
今までは次のような数式を使って求めていました。

= Date(2017,8+1,1)-1

f:id:ExcelLover:20170902141015j:plain

これは求めたい月末の翌日1日を作成してから1日マイナスして月末を求める方法です。

しかしExcelにはそれ専用の便利な関数が用意されていました。
それがEOMONTH 関数です。

EOMONTH 関数の使い方

EOMONTH(開始日、月)

開始日・・・必ず指定します。 起算日を表す日付を指定します。 日付は、DATE 関数を使って入力するか、他の数式または他の関数の結果を指定します。 たとえば、2008 年 5 月 23 日を入力する場合は、DATE(2008,5,23) を使用します。 日付を文字列として入力した場合、エラーが発生することがあります。

月・・・必ず指定します。 開始日から起算した月数を指定します。 “月” に正の数を指定すると起算日より後の日付を返し、負の数を指定すると起算日より前の日付を返します。
メモ 月に整数以外の値を指定すると、小数点以下は切り捨てられます。

たとえばEOMONTH(“2017/8/30",1)と入力すると2017年8月の1ヶ月後の月末、つまり2017年9月30日が表示されます。
「月」に0を入力すると「開始日」の月の月末、マイナスの数値を入力すると「月」の数字だけ遡った月の月末が表示されます。

EOMONTH(“2017/8/30",0)→2017/8/31
EOMONTH("2017/8/30",-1)→2017/7/31 f:id:ExcelLover:20170902141214j:plain

Range.Heightプロパティは読み取り専用?

Heightプロパティで設定できない

Range(“A1”).Height = 20
セルの高さを設定するために上記のようなコードを書いたところ

オブジェクトが必要です

というエラーが出て設定できない事態に遭遇しました。

Helpで調べたところ

Returns or sets a Variant value that represents the height, in points, of the range.

と記載があり、読み書き両方できるように読めます。

読み書き可能でRangeオブジェクトも指定しているのに何故エラーが?

Helpとオブジェクトブラウザで記述が異なる

ところがオブジェクトブラウザで調べてみると

 読み取り専用

の文字が。

実際設定できないところを見るとオブジェクトブラウザの方が正しいと思われるけど、WebHelpなんだから正しい方に修正しておいてほしいですね。
(※英語を正しく読めてない可能性も大)

RowHeightプロパティで読み書き可能

Heightで設定ができないとなるとどうやって設定するかというと

RowHeightプロパティ

Range(“A1”).RowHeight = 20

で高さ設定。

Debug.Print Range(“A1”).RowHeight

で高さを取得できます。

ピボットテーブルの範囲の広げ方

本日頂きましたご質問は 「ピボットテーブルを作った後で対象のシートに列を追加したけど、 追加した列がピボットテーブルに表示されない。 どうしたら表示される?」

範囲の広げ方

  • 1.一度ピボットテーブルを削除して再度作成する
  • 2.データソースの変更を行う
  • 3.元データをテーブル化しておいて、更新をかける

1の方法は一度削除して再作成しますので最新の状態が反映されます。設定をいじりすぎてわけがわからなくなった時も初期化されますので下手に悩むより省力化です。

2の方法は現状のピボットテーブルはそのままで範囲だけ変更できます。

ピボットテーブルの範囲内をどこでも良いので選択してから
分析 → データソースの変更

f:id:ExcelLover:20170821232902j:plain

元データの範囲が表示されるのでお好みの範囲を設定

f:id:ExcelLover:20170821232932j:plain

無事に追加した列が表示されました。

3の方法は前段階としてピボットテーブルを作成する前に元データをテーブルに変換してからピボットテーブルを作成しておく必要があります。

2の方法と同じようにピボットテーブルの範囲内をどこでも良いので選択してから 分析 → 更新

f:id:ExcelLover:20170821232954j:plain

以上で追加した列が反映されます。

ピボットテーブルで行項目をすべての行に表示する

ピボットテーブルを取り敢えずつくるとこのように表示されています。 f:id:ExcelLover:20170820112724j:plain

スッキリしていて見やすいのですが、ここから更に関数を使ってデータの抽出を行う場合、行ラベルがすべての行に表示されていない事がネックになります。

行ラベルをすべての行に表示する

このように表示されると関数でのデータ抽出が行いやすくなります。 f:id:ExcelLover:20170820112746j:plain

フィールドの設定

f:id:ExcelLover:20170820112821j:plain

「小計とフィルター」タブで「なし」を選択。 小計は関数で集計するときには邪魔なので外します。

f:id:ExcelLover:20170820112841j:plain

「レイアウトと印刷」タブで「アイテムのラベルを表示形式で表示する」を選択し 「アイテムのラベルを繰り返す」にチェックを入れる。

f:id:ExcelLover:20170820112858j:plain

※ファイル形式が「xls」だとグレーアウトしていてチェックを入れれません。
※リボンのピボットテーブル-デザインからなら変更できます。

関数で数式を文字列として検索する方法

こんな感じの表を頂きました。

f:id:ExcelLover:20170803203600j:plain

この表には期首残の項目が無く、期末残の数式の中に埋め込まれているという恐ろしい作りになっています。 期首残の数値が必要だったため数式から抜き出そうと思いMid関数と Find関数 で抜き出そうとしたのですが、 Find関数では数式の結果に対してのみ検索可能なため上手く行きませんでした。

Formulatext関数で文字列として認識

Formulatext関数を使うと関数の結果ではなく数式自体を返してくれます。

上記の例でいうと449という結果ではなく=1000-SUM(B3:M3)という文字列に対して検索をかけることが出来ます。

以下のように利用して目的を達成しました。

f:id:ExcelLover:20170803203617j:plain

=MID(FORMULATEXT(N3),2,FIND(“-”,FORMULATEXT(N3))-2)

※実際には = を置換して文字列にしてから普通にMid、Find関数だけで抜き出したんだけどね