■サイト内FAQ など■

　Excel豆知識のサイト内のことなどについての皆様からのご質問（や、個人的メモなど^^;）を
このコーナーでご紹介していこうと思います。

1. メニューに表示される項目が自分のパソコンと「Excel豆知識」の図で違いがあるのは？

2. セルに入っているのが「＝式」というだけで、左のセルの数式が表示されるのはなぜ？

　これには「4.0マクロ関数」というものを利用しています。これは古いバージョンのExcelで
使われていた関数のようで、現在のバージョンではヘルプにも載っていません。

こちらのMicrosoftのサイトから英文のヘルプをダウンロードすることは出来るようです。

また、「Excelファンクラブ」さんの過去ログでケインさんがこの中の「GET.CELL関数」のヘル
プを載せてくださっています。

「色の付いたセルを合計したい」などという質問には、よくこの関数を使って回答されているよ
うです。ただ、これはこのままセルに普通の関数のように入力しても役に立ちません。
「名前定義」を利用して使うことになります。

「エクセル技道場」さんのページにも解説が載っています。

詳しいことは上記の各サイトを参考にしていただくことにして、やり方を書いてみます。

　名前定義の式は相対参照になっているので、どこのセルに「＝式」と入れても、自セルの左の
セルの数式を表示します。

3. 「4月」から「3月」という並び順にするには？

　豆知識28でピボットテーブルで日付を「月」でグループ化することをご紹介しましたが、
年度で集計する場合は「4月」から「3月」という並びにする必要があります。

三つのやり方でその並び方にしてみたいと思います。

<グループ化を「年」と「月」にして年も表示させる方法>

ただ、年度の一覧の時にはこの「年」の表示は邪魔な時もあります。
なので、次は素直に「4月」から「3月」という並び順にするやり方です。

<項目を手で移動させる方法>
これが一番手っ取り早いかも。(^^ゞ

<「ユーザー設定リスト」で登録してしまう方法>

新規ブックでユーザー設定リストを表示させると、下図のように先ほど登録したものが反映
されています。なので、一旦登録すればいつでも使うことが出来ます。また、いらなくなっ
たらそのリストを選択して「削除」ボタンをクリックすればいいです。

上記は、自分だけの「連続データ」を作るやり方を、並べ替えに利用したものです。

本来の目的を利用する例を挙げれば、このユーザー設定リストで「北海道」から「九州」ま
での地方リストなどを作って、「北海道」とセルに入力し、オートフィルで地方を入力する
ことも出来ます。

4. ピボットテーブルで古いアイテム名がリストに出ないようにするには？

ピボットテーブルを作成した後にデータを変更した際、フィールドのドロップダウンリストに
変更前のアイテムが残ったままになることがあります。

下記のマイクロソフトのサポートの記事のやり方はVBAコードを利用したものですが、2007からのピボットテーブル
のオプションの「データ」タブにある「データソースから削除されたアイテムの保持」の「１フィールドに保持する
アイテム数」で「なし」を選択し、「OK」とした後、ピボットテーブルを更新する　と古いアイテムが削除され、
現在のデータソースにあるアイテムのみになります。（H26.4.9追記）
複数のピボットテーブルについて一度にやる場合はこちら

マイクロソフトのサポートの下記の記事のやり方で、その古いアイテムを削除してみたいと思
います。
[XL97] [ピボットテーブル フィールド] ダイアログ ボックスに無効なフィールド名が表示される


（１）下記の９月のデータでピボットテーブルを作成します。

このようなピボットテーブルを作りました。

（２）データを１０月分に変更します。

（３）ピボットテーブルの更新をすると、下図のようになります。

（４）ここで行フィールドのドロップダウンリストを表示させると、もうデータには存在しな
いはずの9月分の日付もリストに残っています。

この不要なアイテムをマイクロソフトの記事にあるコードを利用して削除します。

（５）コードの中で、「ピボットテーブルの名前」とピボットテーブルがある「シート名」が
必要になります。ピボットテーブルの名前は下図の「ピボットテーブル オプション」を表示さ
せるとわかります。自動的につけられた名前の場合、カタカナが半角になっているので注意し
てください。

（６）上記のコードを実行すると、下図のように古いアイテムが削除されます。

5. フィルタオプションで空白を抽出するには？

Excel豆知識42、43のフィルタオプションのところでは空白が含まれないデータで抽出を行う
例ばかりでしたので、Excel豆知識22のデータで空白を抽出してみようと思います。

下図のように「生命保険」という項目名の下のセルに「=」だけを入力し、抽出を行うと

「生命保険」が空白なデータが抽出されます。

この時下図のように「=""」として抽出を行うとうまくいきません。

でも、計算検索条件でやる場合は下図のように「=B7=""」としないとうまくいきません。
ちょっと納得がいかないのですが。^^;

次は3列のどこかが空白なデータを抽出してみます。
下図のように行をずらして条件を入れるとOR条件になります。

上記と同じことを計算検索条件でやると下図のようになります。

どちらで抽出しても同じように3列のどれかが空白なデータが抽出されます。

6. リスト範囲を行列とも可変にするには？

Excel豆知識30で「ピボットテーブルの参照範囲を可変にするには」として
=$A$2:INDEX($F:$F,COUNTA($A:$A)+1)
という数式に「リスト」などと名前をつけて利用するやり方をご紹介しました。

この時利用したデータの1行目が空白でしたので、このような式にしましたが、データが
A1セルを起点に始まる場合は
=$A$1:INDEX($F:$F,COUNTA($A:$A))
という数式を「参照範囲」に入力します。

上記で範囲が可変なのは行方向のみについてで、列方向はF列ということで固定されていますが
次の式にすると、行列どちらも可変になります。
=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))

下図はOFFSET関数のExcelのヘルプです。
OFFSET(基準,行数,列数,高さ,幅) の引数として
基準： A1セル
行数： 0 ←基準を起点に（行方向へシフトせずに。）
列数： 0 ←基準を起点に（列方向へシフトせずに。）
高さ： COUNTA($A:$A)　←A列のデータ数
幅  ： COUNTA($1:$1)　←1行目のデータ数
このように入力しているわけです。

ピボットテーブルのデータ範囲としてだけでなく、いろいろな場面でこの名前定義したリスト
範囲を使えます。
「サイト内FAQ」というよりは自分のための数式コピペ用メモです。(^^ゞ

7. ピボットテーブルの集計行のみに色をつけるには？

豆知識31のデータを少し変えた下図のようなデータから下のようなピボットテーブルを作ると
します。

ピボットテーブルの行の項目の左端にマウスをもっていくと、マウスポインタの形が右向き
矢印に変わります。

その状態でクリックすると、下図のようにその項目と同じ仲間の項目（この場合は「講座名」
の集計行）範囲が選択されます。

クリックを繰り返すと、下のように「パソコン教室」の集計だけ、とか「女性」の「パソコ
ン教室」の集計だけ、といったように選択される範囲がいろいろ変わって繰り返されます。

「講座名」の集計行が全部選択された状態で薄い緑色をつけてみます。

このようにするとだいぶ表が見やすくなります。

同じようにして、「性別」の集計行に別の色をつけてみます。

同様に「総計」行にも別の色をつけてみます。

次に列の項目にも同じようにしてみます。項目の上の枠線のところにマウスをもっていくと、
マウスカーソルが下向き矢印に変わります。そこでクリックを繰り返すと、行の項目の場合
と同じように、選択範囲がいろいろ変わって繰り返されます。

2005年と2006年どちらの2月とも選択された状態で「2月」に色をつけてみます。

こんどは元データを下図のように変更してピボットテーブルを更新してみます。

すると、下図のように集計行などに設定した書式は、ピボットテーブルを更新しても保持さ
れます。

<注意点>
この時、ピボットテーブルで右クリックして出るショートカットメニュー、またはピボットテーブルツール
バーのメニューの「選択」の「選択範囲を有効にする」のチェックがはずれていると、上記で説明している
矢印のマウスポインタにはなりません。（たぶん初期設定ではチェックは入っていると思います。）
この注意点はエクセルファンクラブのQ&Aラウンジへの書き込みを見て初めて知りました。^^

豆知識44にピボットテーブルの書式設定についてまとめましたのでご覧いただければと思います。

8. ピボットテーブルで日付のグループ化が出来ないのは？

豆知識31で日付のグループ化についてご紹介していますが、データ行をあらかじめ多く取っ
ていてデータ範囲に空白行が含まれる場合や、日付データ範囲に空白セルや文字列がある場
合にはグループ化をすることが出来ません。


豆知識31にある下図のようなデータからピボットテーブルを作るとします。

この時、下図のようにピボットテーブルウィザード2/3で設定する範囲は、下に空白行が含
まれないようにすることが肝心です。
（リストの作り方がきちんとしていれば、ウィザードの1/3で「完了」としてもExcelがきちんとその範囲
を認識してくれます。後でデータに追加があるためにあらかじめ範囲を大きく取っているとグループ化で
きません。そのような場合はExcel豆知識30でご紹介したピボットテーブルの参照範囲を可変にするやり方
で範囲設定をなさればいいと思います。）

「行」エリアに「受付日」を「列」エリアに「講座名」を「データ」エリアに「氏名」をドラッ
グし下図のようなピボットテーブルを作成します。次に行フィールドで「右クリック」して出て
くるショートカットメニューから「グループと詳細の表示」-「グループ化」をクリックします。

すると下図のようなグループ化のウィザードが出ますから、そこで「年」「月」などを選択し
「OK」とします。

下図のようにグループ化されます。

今度はグループ化されない状況を作ってみたいと思います。

・空白セルがある

ここでA5セルのデータをDeleteして「空白セル」にしてみます。

そのあとピボットテーブルを更新すると、下図のようにグループ化が解除されて「受付日」
のフィールドに「（空白）」というアイテムが表示されます。（空白のアイテムは一番下に表示
される場合もあります。）　そして、グループ化をしようとすると「選択対象をグループ化す
ることはできません。」というメッセージが出ます。

ここでA5セルに再び1/22の日付を入力してみます。

そのあとピボットテーブルを更新すると、空白のアイテムは表示されなくなりますが、相変
わらずグループ化はできません。

（この時1/22のアイテムは一番下に表示されるようです。並べ替えをすれば上にきます。）

グループ化できない理由は行フィールドのアイテムに「（空白）」が残っているためです。
これはデータを更新するだけでは消すことができません。4. ピボットテーブルで古いアイ
テム名がリストに出ないようにするには？でご紹介した方法で消すことができます。

または、一旦ピボットテーブルを削除して新たに作り直しをします。

下図のように「（空白）」のアイテムが消えました。

この状態にしてからピボットテーブルの更新をすると、今度は行フィールドでグループ化す
ることが出来るようになります。

・文字列のデータがある

今度はA8セルを文字列のデータに変えてみます。

そのあとピボットテーブルを更新すると、下図のようにグループ化が解除されて「受付日」
のフィールドに「1月31日」という文字列のアイテムが表示されます。

そして、グループ化をしようとすると「選択対象をグループ化することはできません。」
というメッセージが出ます。

このあとA8セルを日付データに戻してピボットテーブルの更新をしても、上記の空白セルの
場合と同様文字列のアイテムが残りますので、グループ化する場合はそれを削除してからと
いうことになります。


このように、日付データの範囲の中に空白データや文字列など日付と認識できないものが含
まれている場合、グループ化が出来ない状態になります。
グループ化が出来ないなあという場合、データをもう一度よく見直してみてください。きっ
と何か日付でないデータが見つかると思います。

9. カレンダーで1ヶ月の表示を毎月16日〜翌月15日までにするには？

豆知識10でダウンロード出来るカレンダー表を利用して、16日から15日のカレンダーを作っ
てみます。

B4セルには「=A1」としてありますから、A1セルを「16日」にします。
直接16日の日付を入れてしまってもいいですし、下図のようにシート名を利用した式で
=TEXT(SUBSTITUTE(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,31),"月","")&".16","ge.m.d")*1
日にちの部分を「.1」から「.16」に変えてもいいです。

次に、わかりやすいようにA4セルから下へ番号を振り、1日目から28日目までの表部分を選
択し、条件付書式の条件1（B4セルと月が違ったらフォントを白にする）を削除します。

すると下図のように月が違っても表示されるようになります。

次に29日以降の表部分を選択し、条件1の条件を、B4セルの日にちと同じかそれ以上だったら
=DAY($B32)>=DAY($B$4)
と、変えます。

すると、下図のように16日以降のフォントが白になります。

これで、出来上がりです。
シートをコピーして、シート名を次月に変更すれば、次月のカレンダー表になります。

10. ピボットテーブルの縦に並んだデータを横に並べるには？

ピボットテーブルのデータエリアへ複数のフィールドをドラッグすると、データが縦に並び
ますが、それではちょっと見難いので横に並べたいという場合の操作です。

「データ」となっているところをマウスで掴んで、右へドラッグします。

すると、下図のように横に並びます。

これはExcel豆知識26でご紹介していますが、下の方にあって目立たないのでここに取り上げてみました。

11. フィルタオプションで必要項目（フィールド）のみ抽出するには？

豆知識42、43のフィルタオプションのところでは、データにあるすべての項目を抽出すると
いう例ばかりでしたので、同じデータを使って、必要項目のみ、そして項目の順序をデータ
の配置とは変更して、抽出してみようと思います。

豆知識42のデータシートにある下図のデータから

下図のような抽出条件で

「抽出先」を「指定した範囲」にして、「抽出範囲」の欄にカーソルがある状態で抽出し
たい項目名のセル範囲を選択します。

すると下図のように「東森地区」の「久留米」に該当するデータの抽出したい項目だけが
抽出されます。
図でわかるように「日付」を右に置いても問題なく抽出できます。
項目名さえ一致していれば、項目の数も順序も必要に応じて決めることができます。

12. クロス表の形になっているアンケート結果のようなものをピボットテーブルで
集計するには？

下図のAのようにクロス表になっているアンケート結果を、ピボットテーブルでBのように
集計してみたいと思います。（問1から問7の設問で1から5までを選択するアンケート）

普通にピボットテーブルを作り始めると、下図のようなフィールドリストになり、Bの形の
集計をすることはできません。

そこで、クロス表を元データとして使える「複数のワークシート範囲」からのピボットテー
ブルを作ります。
詳しい操作は下記ページをご覧ください。
Excel豆知識40-1 ピボットテーブル（複数のワークシート範囲から）


表中のセルを選択した状態でピボットテーブルのウィザードを出し、「複数のワークシート
範囲」にチェックを入れ「次へ」。

すると通常の2/3のウィザードとは違う2a/3が出ますのでこれはとりあえずこのまま「次へ」。

次の2b/3の「範囲」のボックスにカーソルがある状態でA2:H14のデータ範囲を選択し、「追
加」ボタンをクリックすると「範囲一覧」のところにその範囲が表示されますので「完了」
とします。（アンケートが複数シートにある場合にはここで次々追加していけばいいです。今回は1枚の
シートで作業します。）

すると新しいシートに下図のようなピボットテーブルが出来ます。

そこで「行」を「列エリア」へ、「列」を「行エリア」へドラッグして移動します。

次に「値」を列エリアへドラッグします。

すると下図のようになります。

「行」「列」「値」といった項目名では不自然なので、「性別」「設問」「回答」と変え
てみました。また、データエリアが「合計/値」になっていますが、求めたいのは人数で
すので「合計/値」のセルで右クリックし、ショートカットメニューの「フィールドの設
定」で集計の方法を「データの個数」に変更します。

すると下図のような集計になります。

ここで、「問1」は該当する「回答」が1から5までありますので、欄が全部ありますが、
「問2」や「問3」のように1から5までの中に回答のない場合もあります。表示されないと
ちょっと面白くありませんので、該当するデータがなくても表示させるように設定します。

「回答」のセルを選択した状態で右クリック。「フィールドの設定」でをクリックすると
下図のウィザードが出ますので、その「データのないアイテムを表示する」にチェックを
入れ「OK」とします。

すると下図のように「問2」や「問3」も1から5まで表示されました。

見やすいように集計の部分を「選択」して、薄い緑にしようと思います。

これで、Bの形の集計が出来上がりました。

13. 外部データの取り込みで「このデータ ソースには、表示できるテーブルはありま
せん。」のエラーが出る時に。

豆知識33の(５)や、豆知識41の(3)でデータのあるエクセルファイルを選択して「OK」とし
た時、下図のように「このデータ ソースには、表示できるテーブルはありません。」と出
る場合があります。
そこであきらめずに、以下の操作をすることでテーブルを認識することが出来るようになる
と思いますので、試してみてください。

上図の「OK」をクリックすると、下図のように使用可能なテーブルが何もない状態になりま
す。そこで「キャンセル」をクリックします。

すると下図のように「Microsoft Query を使ってこのクエリの編集を続けますか？」と出ま
すので、「はい」をクリック。

すると、下図のような画面になりますので、「オプション」をクリック。

下図のように出ますので「再表示」をクリックし、「OK」をクリック。

すると、最初に選択したエクセルファイルのシート名が表示されますので、取り込みたいデ
ータのあるシートを選択して「追加」をクリック。

すると、下図のようにデータシートのフィールド名が表示されますので、「テーブルの追加」
は「閉じる」をクリック。

項目を選択して、マウスで掴んで下の白い大きなところへドラッグします。

すると下図のようになります。

豆知識41（外部データの取り込み（パラメータークエリの利用））の場合は、ここから
豆知識41(８)に続きます。
豆知識33（ピボットテーブル（外部データソースから））の場合は以下へ続きます。


「ファイル」-「Microsoft Office Excelにデータを返す」をクリック。

ピボットテーブルのウィザードの2/3に戻りますので、あとは豆知識33の(１１)からに続き
ます。

14. 外部データソースを使用してピボットテーブルを作成する際には、２４時間を超
   える時間の集計には注意が必要です。

Microsoftサポートの記事
Excel 2003 で、特定範囲の時間 または 日付データを含む外部データの取り込みを使用して
ピボットテーブルを作成すると、データの値が 24 時間分 または 1 日分増えて表示される

Excel 2007 で、特定範囲の時間 または 日付データを含む外部データ ソースを使用してピ
ボットテーブルを作成すると、データの値が 24 時間分 または 1 日分増えて表示される 

にあるように、外部データとの接続方法によっては、ピボットテーブルで２４時間を超える
時間の集計で１日分増えて集計されてしまう場合がありますので、注意が必要です。

サポートの記事にもありますように、外部データソースとの接続方法を変更することで、こ
の問題は回避できます。
Excel2003では、ピボットテーブルのウィザードで外部データを選択して作成すると正しい集
計ができますし、Excel2007では、この記事の「回避策」にある方法で接続すると正しく集計
をすることが出来ます。

また、元データの表示形式を「標準」として、日付として認識できないようにすると、上記
記事と同じ接続方法で作成したピボットテーブルでも正しく集計をすることが出来ます。
集計したあとで表示形式を時間に変更すれば大丈夫です。

15. 「そのピボットテーブルのフィールド名は正しくありません。・・・」のメッセ
ージが出た時には。

下図のように項目行に空欄があるデータでピボットテーブルを作成しようとすると
「そのピボットテーブルのフィールド名は正しくありません。ピボットテーブル レポ
ートを作成するには、ラベルの付いた列でリストとして編成されたデータを使用する必
要があります。ピボットテーブルのフィールド名を変更する場合は、フィールドの新し
い名前を入力する必要があります。」
というメッセージが出て、ピボットテーブルを作ることができません。

このメッセージが出た時には、項目行に空欄がないかチェックしてみてください。上図の
ような表ならすぐに気付きますが、大きなリストで画面をスクロールしないと項目行の全
体を見ることが出来ない場合などは、ちょっと気付きにくいかもしれませんので。^^;

その空欄に正しく項目を入力すれば、ピボットテーブルを作成することが出来ます。
なお、項目行に同じ項目名がある場合には２番めからは（２）（３）と、ピボットテーブルの
フィールド名の後に数字がつきます。

16. 2007でVBAで拡張子.xlsでブックを保存する時に。

Excel2003では、拡張子を「.xls」で保存する場合に、下のコードで大丈夫だったのですが、

Sub test1()
    Workbooks.Add
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\○○○.xls"
End Sub

Excel2007で、このコードで作成される拡張子「.xls」のブックは、偽物「.xls」になって
しまいエラーが出ます。
2007で開こうとすると、下図のエラーメッセージが出て、「はい」を選択すると開くことは
できます。

Excel2003で開こうとすると、下図のエラーメッセージが出て、開くことが出来ません。

なので、.SaveAsのオプションの FileFormat:=xlWorkbookNormalをコードに追加します。

Sub test2()
    Workbooks.Add
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\○○○.xls", FileFormat:=xlWorkbookNormal
End Sub

	
こうすれば、2007でコードを実行しても正しい「.xls」形式のファイルが出来上がります。

「サイト内FAQ」ではなく、自分用メモです。(^^ゞ 

17. Excel2013のアニメーションを無効にするには。

Excel2013を使ってみて、嫌だなあと思ったのが、セル選択をする時でも何でもアニメーショ
ンとかいうニュルッとした動きになることでした。

アニメーションを無効にしたいと思ってネットで検索をすると、オプションでは無効にする
場所がなくて、レジストリをいじる必要がある、ということでした。
でも、素人にはレジストリをいじるのは抵抗があります。^^；

なので、検索のキーワードの一部を「Excel2013」から「Office2013」に変えて、もう少し探
していたら、「コントロールパネル」の「システム」のところから「システムのプロパティ」
を選択して、そこの「詳細設定」-「パフォーマンス」の「設定」をクリックし、「Windows
内のアニメーション コントロールと要素」のチェックをオフにすると無効に出来ることがわ
かりました。
このチェックをはずしてみたら、ExcelだけでなくWordなどの動きもすっきりしました。^^

なかなか見つけられなかったのでここにメモしておきます。(^^ゞ 

18. Excel2013で印刷したバーコードを、バーコードリーダーで読み取ることが出来ない。

今までずっと、Excelで、JANコードを「Microsoft BarCode Control 9.0」を利用してバーコ
ード表示させたものを印刷して、バーコードリーダーで読み取っていましたが、Office2013
に変えたところ、なぜか読み取ることが出来なくなりました。
Office2013に入っているのは「Microsoft BarCode Control 15.0」です。
試しにAccessで同じバーコードを印刷してみたら、ちゃんとバーコードリーダーで読み取るこ
とが出来ました。
「Microsoft BarCode Control」は、もともとがAccessで利用することを想定したコントロー
ルだということですので、Excelでの印刷はあきらめて、ExcelのシートをAccessのリンクテ
ーブルにして、Accessのレポートで印刷をすることにしました。

この件について、Microsoftのサポート技術情報を公開していただきました。
Excel 2013 で印刷したバーコードを読み取れない

早く、Excelで印刷したものも読み取ることが出来るようになればいいなと思っています。

このコーナーがすっかり自分用メモになってしまっている。。^^；

19. ピボットテーブルの古いアイテムを表示しない設定を複数のピボットテーブルに
   対して一度に行うために。

こちらに書いたように、ピボットテーブルを作成した後にデータを変更した際、フィールド
のドロップダウンリストに残った古いアイテムを削除するのが、2007形式のピボットテーブ
ルになってから以前より簡単な操作で出来るようになりました。

でも、１つのブックに複数のピボットテーブルがある場合に、ピボットテーブルのオプショ
ンのデータタブにある「データソースから削除されたアイテムの保持」の「１フィールド
に保持するアイテム数」で「なし」を選択する、という設定は、ひとつひとつのピボット
テーブルに対して行う必要がありますので、少し面倒です。

なので、「アクティブになっているブックの中の複数のピボットテーブルに対して上記のオ
プションの設定を行い、その後、ブックの中にある全部のピボットテーブルを更新する」と
いう操作をVBAで行いたいと思います。
なお、下記のコードは、OtenkiAme（Tasan）さんの、「雨のち晴れ」サイトのこちらのコードを参考にさせて
いただきました。

Sub 古いアイテム削除用()
    Dim mySht As Worksheet
    Dim PvtTbl As PivotTable

    On Error Resume Next
    For Each mySht In ActiveWorkbook.Worksheets
        For Each PvtTbl In mySht.PivotTables
            PvtTbl.PivotCache.MissingItemsLimit = xlMissingItemsNone
        Next PvtTbl
    Next mySht
    On Error GoTo 0
    ActiveWorkbook.RefreshAll
End Sub