空白の消し方

本日のご質問。 vlookup関数でちゃんと入力しているつもりなんだけど答えが正しく表示されません。

Vlookupが上手くいかない

f:id:ExcelLover:20171018215740j:plain
これは結論から言うと、参照するデータのコードの後ろに余白が入力されているため一致しなかったんですね。
別のシステムから出力されたデータをコピー&ペーストするとこういうことがあったりします。

ではどうすればVlookupで正しく答えが算出できるか

・Vlookupの引数にも余白を入力する
・参照するデータから余白を削除する

1つ目の方法でももちろん正しく答えが表示されますが、余白をいくつ入れればいいか毎回悩まないといけないので、2つ目の方法を使うほうが良いでしょう。

空白の消し方

空白の消し方と言ってもどうやればいいでしょうか?
ここでも2つの方法を紹介します。

・「置換」機能で空白を置き換える。
・関数で余分な空白を取り除く。

まずは置換で試してみます。

f:id:ExcelLover:20171018215906j:plain
空白を置き換えたいセルを選択して

Ctrl + H(コントロールキーを押しながらH)で置換機能を呼び出し

検索する文字列に今回の場合は「半角のスペース」を1つ入力。
置き換え後の文字列には何も入力せずそのまま。
「すべて置換」をクリック

これで全ての余白が無くなりました。

f:id:ExcelLover:20171018215959j:plain
しかしマスターデータを見てみると コードの前についていた「0」が全て消えています

場合によっては特に問題がないのかもしれませんが、あまりいい状態ではありません。

関数で空白を消す

次に関数で空白を取り除く方法を試してみます。
この目的で使えそうな関数は
・TRIM
・SUBSTITUTE
・REPLACE
などが考えられます。

今回は 「TRIM」 を関数を使ってみます。

TRIM 関数
各単語間のスペースは 1 つ残し、不要なスペースをすべて削除します。 TRIM 関数は、他のアプリケーションから読み込んだテキストに不要なスペースが含まれているときなどに使用します。

f:id:ExcelLover:20171018220049j:plain
一旦マスターテーブルの横の列で計算します。
引数に空白を削除したいセルを選択します。

この状態では空白が削除されているかわかりにくいし、
マスターテーブルの値が何も変わっていません。

f:id:ExcelLover:20171018220140j:plain
計算結果をコピーしてマスターテーブルに 値で貼り付け をします。

f:id:ExcelLover:20171018220158j:plain
これでvlookupが正しく動くようになりました。

外部システムから出力されたデータを再利用する時は、色々と書式の設定が必要になることが多いです。
色々な編集手段を持っていると効率的に変換ができるようになります。

今週見かけた気になる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」だとグレーアウトしていてチェックを入れれません。
※リボンのピボットテーブル-デザインからなら変更できます。