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

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

範囲の広げ方

  • 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関数だけで抜き出したんだけどね

ユーザーフォームに動的にボタン等を追加する

今までユーザーフォームに配置するコマンドボタン等のコントロール類は ユーザーフォームの作成時に全てきっちり配置していました。

というかその方法しか知らなかった。

ところがコントロール類はユーザーフォームのイベントと連動して動的に、 しかも簡単に配置できる事を知ったのその備忘録。

まずはユーザーフォームを作成してコマンドボタンを1つ設置。

f:id:ExcelLover:20170730155019j:plain

このボタンをクリックする度にコマンドボタンを追加するようにしました。

f:id:ExcelLover:20170730155052j:plain

ユーザーフォームのコードは以下の通り。

Option Explicit
Dim myCol As Collection
Dim myCls As Class1

Private Sub UserForm_Initialize()
    Set myCol = New Collection
End Sub

Private Sub CommandButton0_Click()
    Dim myCmdBtn As MSForms.CommandButton
    
    Set myCmdBtn = UserForm1.Controls.Add("Forms.CommandButton.1", "CommandButton" & myCol.Count + 1, True)
    With myCmdBtn
        .Left = 10
        .Top = 10 + 20 * myCol.Count
        .Width = 150
        .Caption = "CommandButton" & myCol.Count + 1
    End With
    Set myCls = New Class1
    myCls.setBtn myCmdBtn
    myCol.Add myCls
End Sub

この内、動的にボタンを追加しているのは Set myCmdBtn = UserForm1.Controls.Add(“Forms.CommandButton.1”, “CommandButton” & myCol.Count + 1, True) の部分です。 追加したいフォームのコントロールコレクションに、追加したいコントロールを指定してやれば、フォームにコントロールが追加されます。

第1引数のコントロールを指定する文字列は以下の通り。

作成するコントロール 使用する ID
チェック ボックス (CheckBox) Forms.CheckBox.1
コンボ ボックス (ComboBox) Forms.ComboBox.1
コマンド ボタン (CommandButton) Forms.CommandButton.1
フレーム (Frame) Forms.Frame.1
イメージ (Image) Forms.Image.1
ラベル (Label) Forms.Label.1
リスト ボックス (ListBox) Forms.ListBox.1
マルチ ページ (MultiPage) Forms.MultiPage.1
オプション ボタン (OptionButton) Forms.OptionButton.1
スクロール バー (ScrollBar) Forms.ScrollBar.1
スピン ボタン (SpinButton) Forms.SpinButton.1
タブ ストリップ (TabStrip) Forms.TabStrip.1
テキスト ボックス (TextBox) Forms.TextBox.1
トグル ボタン (ToggleButton) Forms.ToggleButton.1

イベントの登録

動的に配置したボタンにはイベントが登録されていないので、このままではただの飾りになってしまう。

クラスを作成して以下のようなコードを登録

クラスを作成して以下のようなコードを登録
Option Explicit
Private WithEvents myCmdBtn As MSForms.CommandButton

Private Sub myCmdBtn_Click()
    MsgBox myCmdBtn.Caption
End Sub

Sub setBtn(Cmdbtn As MSForms.CommandButton)
    Set myCmdBtn = Cmdbtn
End Sub

witheventsキーワードを付けて変数を宣言すると、 変数の型でしているコントロールのイベントを認識することが出来ます。 この場合はCommandbuttonを指定しているのでコマンドボタンの各種イベントを 認識するクラスが作成されます。

参考記事

久しぶりに読み返してみたら、昔は読み飛ばしたであろう今回の内容を発見した。

かんたんプログラミング Excel2003 VBA コントロール・関数編

かんたんプログラミング Excel2003 VBA コントロール・関数編

イベントの共通化について。 thom.hateblo.jp

ピボットテーブルで項目が重複して表示される

本日のご質問。

ピボットテーブルで同じ項目が複数表示されます。
どうしたら1つにまとめられますか?

別れている理由わかりますか。
f:id:ExcelLover:20170510222910j:plain

先程の画像だとわかりにくいですけど、フォントをMS 明朝に変えたコチラだとどうでしょう。
f:id:ExcelLover:20170510222929j:plain

全角・半角で別文字扱い

答えが見出しにババーンと出ていますが、ピボットテーブルで集計する時は全角・半角で別の文字の扱いになります。
自分で入力したときにウッカリ全角・半角間違う時もあるでしょうし、他の人が作った資料からコピーすると表記がマチマチになってしまう可能性があります。

何らかの方法を使って表記揺れを統一して上げる必要があります。

「置換」機能で一括変換

まず1つ目は置換(ちかん)機能を使って該当文字列をどちらかの表記に合わせる方法です。
こちらは置換対象が少ない場合に使うといいです。
f:id:ExcelLover:20170510223058j:plain

置換したい列を選択してCtrl + H(Ctrlキーを押しながらHを押す)
今回は半角に統一しようと思うので
・検索する文字列に「A(全角のエー)」
・置換後の文字列に「A(半角のエー)」
を入力。
f:id:ExcelLover:20170510223148j:plain

これでAを半角に統一できました。

ASC関数で置換

もう1つの方法はASC関数を使って半角に統一します。

ASC(文字列)

引数文字列の中の全角文字を半角文字に置き換えます。

表とは別の場所にASC関数を入力。
入力後コピーして値で貼り付け。

全角・半角は統一しよう

ピボットテーブルでもそうですが、オートフィルターの絞込をする際も全角・半角は区別されてしまいます。
自分で入力する際は統一して入力するように。
また外部からのデータをインプットした結果、表記ゆれが せっかく集計するためにピボットテーブルを使ったのに、別れて表示されていたために横に足し算を入力しようとしていた質問者さんの苦悩を何とか救うことが出来ました。

VLookupの列指定

本日頂いたご質問。

こんな表で店Noを入力したときに、グループマスターから対応するグループコードを表示したい。

f:id:ExcelLover:20170506173005j:plain

このような場合に真っ先に思いつくのはVLOOKUP関数です。
しかし残念ながら今回の事例では使えません。

列指定は右側だけ

VLOOKUPの引数は以下のとおりです

VLOOKUP(検索値, 範囲, 列番号, [検索の型])

引数の中の列番号のHELPは以下のとおりです

列番号 必ず指定します。目的のデータが入力されている列を、範囲内の左端から数えた列数で指定します。列番号に 1 を指定すると、範囲の左端の列の値が返され、列番号に 2 を指定すると、範囲の左から 2 列目の値が返されます (3 列目以降も同様に指定します)。

列番号に次の値を指定すると、対応するエラーが返されます。
・1より小さい場合、#VALUE! エラー値が返されます。
・範囲の列数より大きい場合、#REF! エラー値が返されます。

VLOOKUP関数は引数範囲で指定した範囲の左端から、引数列数で指定した数だけ右側に移動した列から値を引っぱってきます。 またHELPにある通り列番号には1以上の数値を指定しなければいけないため、マイナスの値を入力して範囲の左側から値を引っぱって来ることが出来ません。

このような時の対処方法として以下のような方法があります。

表を組み替える f:id:ExcelLover:20170506173118j:plain

可能であれば、素直に表の列を並び替えるのが手っ取り早いです。
そもそもVLOOKUPで値をひっぱてくることが前提であれば、そのようにマスターテーブルを作りましょう。

INDEX関数とMATCH関数を使用する

表を組み替えずに対処する方法としてINDEX関数MATCH関数を使用する方法があります。
この方法だと検索した列より左側の値を引っぱって来ることが可能です。

INDEX(範囲、行番号, [列番号], [領域番号])

MATCH(検査値, 検査範囲, [照合の型])

INDEX関数は引数範囲に指定した範囲内で縦方向に引数行番号、横方向に引数列番号だけ移動した所にある値を表示する関数です。

MATCH関数は引数検査値の値が引数検査範囲ないのどの位置にあるのか相対的な場所を数字で返す関数です。
検査範囲を指定する時は1列or1行の範囲を指定してください。 f:id:ExcelLover:20170506173223j:plain

実際に入力する関数は以下のとおりです
INDEX(E4:E8,MATCH(A3,F4:F8),1)

関数をばらして処理内容を追ってみましょう。
まずMATCH関数部分。

引数検査値に指定した「5」が引数検査範囲のどの場所にあるのか探します。
この場合指定した場所は上から5番目にありますので「5」という答えが返ってきます。
f:id:ExcelLover:20170506173416j:plain

次にINDEX関数部分。
引数範囲に指定した範囲内で上から5番目(MATCH関数で算出)、横に1番目の値を返します。

基本はVLOOKUP関数で

INDEX関数とMATCH関数の組み合わせだとマスターテーブルを柔軟に作ることが出来るのはメリットですが、やはり関数が入れ子になりわかりにくくなります。
基本はVLOOKUP関数を使えるようにマスターテーブルを作るのがベターだと思います。

VBAで「元に戻す」方法

A1に”A”と入力
Enter
A2に”B”と入力
Enter
Ctrl+zで「元に戻す」を実行

記録されるマクロは以下のようになる。

    ActiveCell.FormulaR1C1 = "A"
    Range("A2").Select

「元に戻す」を実行すると、
「元に戻す」は記録されず、
直前の操作が記録から消える。

ちなみにマクロの記録は、リアルタイムでコードが生成されている。
なので上記の動作を記録するときにVBEを起動しておくと、
何か処理をするたびにコードが増えていく。
A2に”B”を入力というコードも一度は記録されるのが確認できる。

では記録されなかった「元に戻す」はどう書くかというと

Application.Undo

これで元に戻せるのだが1つ注意すべき点は

・直前の編集がマクロで変更を行ったものでないこと

例えばA1に”A”と入力した後で下記のコードを実行するとエラーが発生する

    ActiveCell.FormulaR1C1 = "B"
    Application.Undo

しかし同じようにA1に”A”と入力した後で下記のコード実行してもエラーは発生しない

    MsgBox "test"
    Application.Undo

msdnには下記のような表記がある

注釈
Undo メソッドは、マクロを実行する前にユーザーが実行した最後の操作を元に戻す場合にだけ使用できます。このメソッドは、マクロの先頭に記述する必要があります。Visual Basic で実行したコマンドは元に戻すことができません。
Application.Undo メソッド (Excel)

直前の操作がシートに何も影響を与えていなければ、必ずしも先頭にUndoメソッドを記載しなくても良いようだ。

Worksheet_Changeに仕込んで、望まない処理をされたときに戻すときに使ったりしている。