テーブルには、販売金額フィールドがあります。
作成タブのクエリデザインをつかって、クエリをつくります。
![=DMax("[販売金額]","T店舗別販売金額")](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiqYZK7hX5STljWM1F8XgLGwsfFTxn_oRb1PVclhb_PdNz4cbjaFb2dhmQZKFJKWgCS_KbbdTeo7knxfGdNcOn7sq3v_NYUl3UC1hqGGw5ZEjBypJZVe-QqWTEGCiYqp69EKQgBk9z8iHqJvU5X3tnTyf4F04CykSE-nWTkP_uEd27ZzYBNKp1zT7S71A4/s2598/002.jpg "=DMax(\"[販売金額]\",\"T店舗別販売金額\")")
販売金額フィールドの抽出条件に、
=DMax("[販売金額]","T店舗別販売金額")
を設定しました。
では、実行して確認してみます。
DMax関数をつかうことで、抽出できます。
実は、次の方法でも抽出することができます。
2にすれば、上位2位までのデータを抽出することができます。
テーブルに、販売金額のフィールドがあります。
クエリを使うわけですが、ポイントは、抽出条件をどのようにしたらいいのかということですね。
そこで、DAvg関数をつかうことで、対応することができます。
作成タブのクエリデザインでクエリをつくっていきましょう。
>=DAvg("[販売金額]","T店舗別販売金額")
と設定します。
これで、DAvg関数で平均値を求めることができるので、「>=」をつけることで、平均以上という条件をつくることができました。
では、実行して、データシートビューで確認しましょう。
Accessのクエリで「横浜市の人だけ抽出したい」のに、条件に「横浜市」と入れても何も出てこない…
そんな経験はありませんか？
実は住所のように、後ろに番地などが続くデータを抽出するには「ワイルドカード（*）」という特別な記号が必要です。
今回は、AccessだけでなくExcelでも役立つワイルドカードの基本と、クエリでの設定方法を分かりやすく解説します。
従業員を管理しているテーブルがあります。
都道府県別に何名従業員がいるのかを、確認したいのですが、どのようにしたらいいのでしょうか。
ExcelだとCOUNTIF関数をつかうことで対応できますが、Accessには、COUNTIF関数は用意されていません。
そこで、Accessではクエリの集計をつかって、求めることができます。
作成タブから、クエリデザインでクエリをつくっていきましょう。
クエリデザインタブの集計をクリックして、集計行をデザイングリッドに表示させます。
集計行に下記の設定をおこないます。
左側の都道府県フィールドには、グループ化
右側の都道府県フィールドには、カウント
では、実行して、データシートビューで確認してみましょう。
Accessは、ExcelのようなCOUNTIF関数はありません。
集計行のカウントをつかうことで、条件付きで件数を求めることができます。
Excelでは、値をABCで三分岐の判定をする場合には、IF+IF関数で対応することができます。
Accessもクエリで同じように判定することができます。
ただし、ExcelはIF関数ですが、AccessはIIf関数をネストで使用します。
用意したテーブルをもとに、80以上ならA。60以上ならBそれ以外はCと判定させます。
![判定: IIf([得点]>=80,"A",IIf([得点]>=60,"B","C"))](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg3g9UFxdROyep1ko3Cp_IpnCnvQnvGsUVKHDRqlMg_khiosdBhIzccmMHJD2Mw3oOb6ToOYUi2bHj2n8SBxTGxmFp5DK3iI0I5uOJ4QN_v_fIgVRSSKRbAACm52udX0cUw8NupiZ7dRsrfH4cjl2q15_tqf4onrLzWW0gofDkMfdq-0w4lfzam1B-LlnY/s2598/002.jpg "判定: IIf([得点]>=80,\"A\",IIf([得点]>=60,\"B\",\"C\"))")
判定の演算フィールドをつくります。
判定: IIf([得点]>=80,"A",IIf([得点]>=60,"B","C"))
Excelと同じで、偽の場合に、IIf関数をネストさせます。
これで、三分岐のABCで判定することができました。
次のテーブルを用意しました。
では、Accessでは、このような単一条件での判定をすることはできないのでしょうか。
Accessでは、IF関数ではなくて、IIF関数をつかうことで、Excelと同じように処理をすることができます。
作成タブのクエリデザインをつかって、クエリをつくっていきましょう。
判定: IIf([得点]>=80,"A","")
では、実行して確認をしてみましょう。
ExcelではIF関数ですが、AccessではIIf関数をつかうことで、単一条件での判定をすることができます。
Accessのクエリで「東京の人と神奈川の人、両方リストアップしたい」と思ったことはありませんか？
クエリデザイン画面の下にある「または」という行。
実はここを使えば、いくらでも条件を追加できるんです。
初心者が迷いやすい「抽出条件」の入力ルールと、保存した後に自動で書き換わってしまう「Accessの不思議な挙動」についても詳しく解説します。
ExcelにエクスポートしてMODE.SNGL関数やMODE.MULT関数をつかうことで、最頻値を求めることはできます。
Accessでは、求めることはできるのでしょうか。
次のテーブルを用意して説明していきます。
70が2つありますので、最頻値は70。
ということで、70が出れば正解というわけですね。
Accessのクエリでは、残念ながら1発で求めることはできません。
理由は、ExcelのMODE.SNGL関数やMODE.MULT関数のような関数がないからです。
今回は2段階クエリで求めていきましょう。
まずは、何件あるのかを求めるクエリをつくります。
一つは、グループ化します。
もう一つは、集計行をカウントにします。
並べ替えは降順
実行します。
まずは、このクエリを保存しておきましょう。
あとは、クエリデザインのTOPを1に設定すればOKです。
実行して確認してみましょう。
これで、最頻値を求めることができました。
Accessでテーブルを操作していると、レコードを削除した際にオートナンバー型が「永久欠番」になって困ったことはありませんか？
本来、データベースの重複を防ぐための重要な仕様ですが、データの整理やテストデータの作成時など、「途中の抜けを埋めてきれいな連番に振り直したい！」という場面はよくあります。
今回は、市販の書籍ではあまり詳しく解説されていない、オートナンバー型の番号を安全にリセット・復活させるリカバリー手順を分かりやすく解説します！
得点を管理しているテーブルがあります。
そこで、クエリで対応してみましょう。
作成タブのクエリデザインをつかいます。
平均・最大値・最小値・レンジ。それぞれの演算フィールドをつくります。
平均: Avg([得点])
最大値: Max([得点])
最小値: Min([得点])
レンジ: Max([得点])-Min([得点])
これらの演算フィールドで求めることができます。
平均はAvg関数。最大値と最小値は、Excelと同じMax関数とMin関数。
範囲のレンジは、最大値から最小値を引くことで、求めることができます。
Accessでも、求めることができるのですが、できないものもあります。
それは、「中央値」。
Excelの中央値を求めることができるMEDIAN関数のような関数がAccessには用意されておりません。
ちょっとした計算ならばExcelにエクスポートしなくても求めることができますが、Accessはデータベースソフト。
表計算ソフトのExcelには及ばないところはありますね。
まぁ、餅は餅屋といったところでしょうか。
Excelでは、合計などで、￥マークをつけることがあります。
Accessでは、どのようにしたらいいのでしょうか。
Accessのテーブルはあくまでも、データなので、見栄えとかは必要ではありませんが、どうしてもという場合には、クエリデザインをつかいます。
通貨スタイルを設定したいフィールドを選択したら、クエリデザインタブにある「プロパティシート」をクリックします。
プロパティシート作業ウィンドウが画面右側に表示されます。
では、データシートビューで確認してみましょう。
さて、Excelには、会計という表示形式があります。
会計は通貨と異なって
左側に通貨記号（￥）を固定配置
右側に数値を右揃えで配置
ゼロは “-”（ハイフン）
桁区切りあり
という設定です。
表示形式は、
_ ¥* #,##0_ ;_ ¥* -#,##0_ ;_ ¥* "-"_ ;_ @_
Accessで会計の表示をしたい場合はどうしたらいいのでしょうか。
結論は、「できません」
まず、書式で 会計 は用意されていません。
そもそも、Accessはデータベースアプリなので、見栄えとか、見やすさとは無縁です。
Excelは、セルの中に複数のレイアウト領域を持てますが、Accessは、データを構造化するものなので、フィールドには「値」しか持てません。
なので、できないわけです。
あえて、近づけるとしたら、次のようにしてみました。
![会計: "¥ " & Format([販売金額],"#,###")](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvGYqW9Waw-8iiX9H2uOtlLU3XbAT6Xxvi5Gfuy6FaWEK_eBnQhJ8CBA4zsKNxj4sjBzsfQ6hA-4jzqNf0IqtijHuxXodG2X0usn7_IkGAQoybSzGT1oYvH2M6zV7mTW1bt9qZbloJqRPHxiKNpir-tdwn_bXpamy8k-_Fg8RwIjl-CcclFVG8-0TQ1Gc/s2599/005.jpg "会計: \"¥ \" & Format([販売金額],\"#,###\")")
と演算フィールドをつくってみました。
では、実行してみましょう。
どうしてもやりたいようでしたら、Excelにエクスポートして￥マークをつけましょう。
区切りカンマを付けて表示したいとき、Excelならば、桁区切りカンマのボタンを押すとか、表示形式でカンマをつけて対応します。
では、Accessではどのようにしたらいいのでしょうか。
基本的に、データベースのデータとしては、表示形式はどうでもよく、あくまでも、見栄え・見やすさだけで、桁区切りカンマのつけるという考えになります。
まずテーブルです。
テーブルは「データの倉庫」であって、見た目を整える場所ではないからですね。
そこで、クエリをつかいます。
作成のクエリデザインでつくっていきましょう。
フィールドを設定します。
桁区切りカンマを設定しますので、設定したいフィールドを選択して、クエリデザインタブのプロパティシートをクリックします。
画面右側に、プロパティシート作業ウィンドウが表示されます。
書式から「標準」を選択します。
これで、桁区切りカンマは、つくことはつくのですが…
実行して、データシートビューで確認してみましょう。
標準だけでは、小数点第2位まで表示されてしまいます。
小数点はいらない場合は、次の設定を行う必要があります。
デザインビューに戻りましょう。
プロパティシートの小数点以下表示桁数を0にします。
改めて、実行して確認します。
これで、桁区切りカンマをつけることができました。
もしかしたら、Format関数でも桁区切りカンマをつけれるよと思うかもしれません。
Excelにも表示形式を設定するFORMAT関数をつかうことで、桁区切りカンマを設定することができます。
しかし、ちょっと問題があります。
Format関数を使った演算フィールドをつくりました。
関数: Format([販売金額],"#,##0")
という演算フィールドです。
実行して、データシートビューで確認します。
Format関数の結果は、文字列扱いになるので、左揃えになってしまうのが、欠点です。
そのため、あくまでも、見栄え・見やすさということであれば、プロパティシートで設定するほうがいいように思えます。
Excelデータがあります。
品川店のA定食と横浜店のB定食だけに抽出するには、どうしたらいいでしょうか。
Excelのオートフィルターでは対応できません。
店舗名は、品川店と横浜店。商品名は、A定食とB定食
結果は、思っているように抽出できません。
品川店はA定食のみにしたいのに、品川店のB定食も抽出されてしまっています。
あくまでも、オートフィルターは、列内のOR条件の優先順が高く、そのあとにAND条件をおこなうからです。
そのため、Excelでは、データタブのフィルターの詳細設定を使う必要があります。
ただ、詳細設定をおこなうには、条件行を作る必要があります。
1行目に、見出し行をつくりD2:F3に抽出条件をつくり、フィルターオプションの設定ダイアログボックスを表示して、抽出させる必要があります。
あるいは、店舗名と商品名を結合した、店舗名商品名という列をつくり、つくった列をつかって抽出させる方法もあります。
ただ、列を増設しますので、ファイルサイズは大きくなりますし、結局作業をしなければなりません。
では、Accessのクエリならどうなるのでしょうか。
ExcelデータをAccessにインポートしています。
挿入のクエリデザインをつかって、クエリをつくります。
販売日と店舗名と商品名の列をつかいます。
抽出条件には、品川店でA定食
「または」には、横浜店でB定食
と設定します。
では、実行してみましょう。
問題なく、品川店でA定食 と横浜店でB定食 だけを抽出することができました。
Accessは、Excelと異なった抽出ルールになっております。
場合によっては、Accessのほうが、抽出がスムーズになるかもしれません。
店舗名が品川店と横浜店の合計金額を求めたい場合、SUMIF関数一つだけではもとめることができません。
もし、SUMIF関数をつかう場合で、わかりやすくという前提ならば、次のような数式をつくります。
なんで、SUMIF関数が2つあるのかというと、2つめの引数の「検索条件」がOR条件に対応していないからです。
またOR関数をつかった数式に変えてみます。
=SUMIF(D2:D169,OR("品川店","横浜店"),I2:I169)
これだと、0になってしまいました。
原因は、先ほども紹介したように、検索条件にOR関数を使ったからです。
SUMPRODUCT関数をつかうなど、対応方法はありますが、算出するのはちょっと面倒です。
では、Accessのクエリだったらどうなるのでしょうか。
Accessのクエリデザインをつかって、演算フィールドをつくってみます。
![合計金額: Sum(IIf([店舗名] In ("品川店","横浜店"),[金額],0))](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvfKHXUuMuYHpOVgd-AGW7kXhz8WRWCkrqXnkfEC_f1Th5-UJpYk986aiX7nzyjrgIqeXVNmq8Xar347hi6qx1C2vYliJbrOSBgnBe4fza4W4qz2xw4HhlfokEddJxISOM7QIBe1MmHo90O-FNb2EMEauzZ0XqneN7zkLnn_b-xCmvmmm4A3iMeTQuWeI/s2612/003.jpg "合計金額: Sum(IIf([店舗名] In (\"品川店\",\"横浜店\"),[金額],0))")
テーブルは、Excelのでーたをインポートしています。
クエリデザインで演算フィールドをつくります。
そして、クエリデザインタブの集計ボタンをおして、集計行を表示します。
合計金額: Sum(IIf([店舗名] In ("品川店","横浜店"),[金額],0))
集計行には、演算 と設定します。
では実行してみましょう。
では、演算フィールドを確認しておきましょう。
合計金額: Sum(IIf([店舗名] In ("品川店","横浜店"),[金額],0))
Sum関数は合算する関数です。
何を合算するのかというと、IIf関数で合致しているものです。
IIf([店舗名] In ("品川店","横浜店"),[金額],0) は、
もし、店舗名が、品川店または横浜店だったら金額フィールドをつかって、そうでなければ0をつかうという条件式なので、品川店と横浜店ならば、金額を足すことができるというわけです。
単純ですが、OR条件の合計の場合、Accessのクエリという方法もアリなのかもしれません。